Problems with GTM OFFSET 0.00 in list

[Alfio]

Hi, i Have a vicidial server and Im having problems when I upload my leads, they got the default timezone 0.00 GMT OFFSET NOW (local time)

Everything is ok with the time zone, date, hwclock, server_updater table, etc.



But when i run this command i got this error:

/usr/share/astguiclient/ADMIN_adjust_GMTnow_on_leads.pl --debug --list-settings

-----DEBUGGING -----


----- USE LIST SETTINGS -----

STARTING TIME ZONE DAYLIGHT SAVING TIME CALCULATION SCRIPT



SEED TIME 1524280768 : 2018-04-20 23:19:28 LOCAL GMT OFFSET NOW: -5.00
- COUNTRY_AND_AREA_CODE lists found: 4 and list_id IN('1303','1304','1300','1305')
- POSTAL_CODE lists found: -1
- NANPA_PREFIX lists found: -1
- OWNER_TIME_ZONE_CODE lists found: -1
- Unique Country dial codes found: 2

RUNNING LOOP FOR COUNTRY CODE: 1
Area Code Updates: 0
Postal Updates: 0
NANPA Updates: 0
Time Zone Code Updates: 0

RUNNING LOOP FOR COUNTRY CODE:
Area Code Updates: 0
Postal Updates: 0
NANPA Updates: 0
Time Zone Code Updates: 0
DBI::db=HASH(0x103e710)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /usr/share/astguiclient/ADMIN_adjust_GMTnow_on_leads.pl line 1220.

GRAND TOTALS:
Postal Updates: 0
Area Code Updates: 0
NANPA Updates: 0
TIME ZONE Updates: 0

DONE
- process runtime (0 sec) (0 minutes)




In another server that everything is ok, i run the command and show no error




/usr/share/astguiclient/ADMIN_adjust_GMTnow_on_leads.pl --debug --list-settings

-----DEBUGGING -----


----- USE LIST SETTINGS -----

STARTING TIME ZONE DAYLIGHT SAVING TIME CALCULATION SCRIPT



SEED TIME 1524280355 : 2018-04-20 23:12:35 LOCAL GMT OFFSET NOW: -5.00
- COUNTRY_AND_AREA_CODE lists found: 5 and list_id IN('999','998','500','1303','1308')
- POSTAL_CODE lists found: -1
- NANPA_PREFIX lists found: -1
- OWNER_TIME_ZONE_CODE lists found: -1
- Unique Country dial codes found: 1

RUNNING LOOP FOR COUNTRY CODE: 1
Area Code Updates: 0
Postal Updates: 0
NANPA Updates: 0
Time Zone Code Updates: 0

GRAND TOTALS:
Postal Updates: 0
Area Code Updates: 0
NANPA Updates: 0
TIME ZONE Updates: 0

DONE
- process runtime (1 sec) (0.0166666666666667 minutes)



- ViciBox v.8.0.1
Asterisk 11.25.1-vici
VERSION: 2.14-669a
BUILD: 180411-1647
CPU i5, 8gb DDR, No digium or sangoma Hardware
I have Cepstral Installed.

In both vicidial servers im using the same list with the same values. The list has the following fields:

lista telefono nombre apellido Cuota Monto
1310 18492077601 Alfio Munoz 2 150000
1310 18292224232 Tito Cordero 4 12000

[williamconley]

Do they both have the same version of Vicidial?

Were these both fresh installs, or was one of them upgraded from a previous version?

Also show the code from ADMIN_adjust_GMTnow_on_leads.pl line 1220 (and relevant lines above it) to see why it's complaining about an improperly closed db request.

[Alfio]

Hi, they are the same vicibox version, fresh install but they have different astguiclient version.

The one that it is working:

- ViciBox v.8.0.1
Asterisk 11.25.1-vici
VERSION: 2.14-644a
BUILD: 171130-0036


/usr/share/astguiclient/ADMIN_adjust_GMTnow_on_leads.pl (line 1220)


}
##### STOP RUN LOOP FOR EACH TIME ZONE CODE RECORD #####
if($DB){print "Time Zone Code Updates: $tzcode_updated_count\n";}
$TOTALtzcode_updated_count = ($TOTALtzcode_updated_count + $tzcode_updated_count);



$d++;
}



$dbhA->disconnect(); ---------> line 1220

if($DB){print "\nGRAND TOTALS:\n";}
if($DB){print "Postal Updates: $TOTALpostal_updated_count\n";}
if($DB){print "Area Code Updates: $TOTALarea_updated_count\n";}
if($DB){print "NANPA Updates: $TOTALnanpa_updated_count\n";}
if($DB){print "TIME ZONE Updates: $TOTALtzcode_updated_count\n";}
if($DB){print "\nDONE\n";}
$secy = time(); $secz = ($secy - $secX); $minz = ($secz/60); # calculate script runtime so far
if ($q < 1) {print " - process runtime ($secz sec) ($minz minutes)\n";}

exit;

[mflorell]

Is the phone_code populated with anything in your leads? If not, then the timezone adjustment script won't do anything.

[Alfio]

Hi Matt, thanks for your answer, there is a way when I'm uploading my list to force gtm -4 the timezone? any field in the list or something like that?

Thanks in advanced.

[mflorell]

Yes, if you are using the Web-based lead loader, the "Phone Code Override" option allows you to select a phone code that will override for all of the leads you are loading.

[Alfio]

Hi again, we are in Dominican Republic and the Country phone code is the same for USA and Canada (1) i put (1) in the phone_code and matched when uploading the list, but the same results, try using area code with 809 and nothing.

GTM OFFSET 0.00 in list

[mflorell]

Run this query in mysql and post the results:

select count(*),phone_code,LEFT(phone_number,3) as phone from vicidial_list group by phone_code,phone order by phone_code,phone;

[Alfio]

Query:

Database changed
MariaDB [asterisk]> select count(*),phone_code,LEFT(phone_number,3) as phone from vicidial_list group by phone_code,phone order by phone_code,phone;
+----------+------------+-------+
| count(*) | phone_code | phone |
+----------+------------+-------+
| 1 | | |
| 7 | 1 | 727 |
| 12 | 1 | 829 |
| 6 | 1 | 849 |
| 6 | 809 | 829 |
| 3 | 809 | 849 |
+----------+------------+-------+
6 rows in set (0.00 sec)

[Alfio]

Hi, I solved the problem, I ran in the server :

/usr/share/astguiclient/ADMIN_area_code_populate.pl --purge-table –debug

And all the area code were populate, I uploaded a new list and selected 1-DOM from Phone Code Override, uploaded the new list, the list uploaded with gtm -4, later I run:

/usr/share/astguiclient/ADMIN_adjust_GMTnow_on_leads.pl --debug --list-settings

And no error was shown, and all the list fixed to gtm -4.

Thanks for your help.

[mflorell]

Thanks for posting your solution!