List Loader 3 Letter State Compatibility

[benomg]

I'm not much of a coder so I'm having trouble following new_listloader_superL.php to find a fix for getting states with 3 letters (like in Australia) to be recognised for the correct time zone. Currently the leads with 3 letter states are defaulting to the time zone for when states are unspecified. I'm trying to get them to convert to 2 letters (i.e. substr($state,1)) in new_listloader_superL.php as they are read for the first time (instead of when they are inserted to the 2 character state field in the db). Can someone familiar with the script please have a very quick look to see what can be done?

I've posted an issue, http://www.eflo.net/VICIDIALmantis/view.php?id=385 , but would really appreciate any suggestions to be able to fix myself.

It happens for at least Vicidial 2.0.2 and 2.2.1 (the only ones I've tried).

[mflorell]

All of the state fields are locked to 2 characters, and even in the vicidial_phone_codes table the Australian states are 2-letters:


| 61 | AUS | 2 | NS | +10 | Y | FSO-FSA | New South Wales & Capitol |
| 61 | AUS | 3 | VI | +10 | Y | FSO-FSA | Victoria |
| 61 | AUS | 5 | | +10 | Y | FSO-FSA | Prefix 5 nonexistant |
| 61 | AUS | 7 | QL | +10 | N | | Queensland |
| 61 | AUS | 36 | TA | +10 | Y | FSO-FSA | Tasmania |
| 61 | AUS | 80 | SA | +9.5 | Y | FSO-FSA | Southern Australia |
| 61 | AUS | 81 | SA | +9.5 | Y | FSO-FSA | Southern Australia |
| 61 | AUS | 82 | WA | +8 | N | | Western Australia |
| 61 | AUS | 83 | SA | +9.5 | Y | FSO-FSA | Southern Australia |
| 61 | AUS | 84 | WA | +8 | N | | Western Australia |
| 61 | AUS | 85 | SA | +9.5 | Y | FSO-FSA | Southern Australia |
| 61 | AUS | 86 | WA | +8 | N | | Western Australia |
| 61 | AUS | 88 | SA | +9.5 | Y | FSO-FSA | Southern Australia |
| 61 | AUS | 89 | WA | +8 | N | | Western Australia |
| 61 | AUS | 889 | NT | +9.5 | N | | Northern Territory |
| 61 | AUS | 4S | WA | +8 | N | | Mobile numbers Western Austral |
| 61 | AUS | 4S | NT | +9.5 | N | | Mobile numbers Northern Territ |
| 61 | AUS | 4S | SA | +9.5 | Y | FSO-FSA | Mobile numbers South Australia |
| 61 | AUS | 4S | TA | +10 | Y | FSO-FSA | Mobile numbers Tasmania |
| 61 | AUS | 4S | AC | +10 | Y | FSO-FSA | Mobile numbers Capitol |
| 61 | AUS | 4S | NS | +10 | Y | FSO-FSA | Mobile numbers New South Wales |
| 61 | AUS | 4S | | +10 | Y | FSO-FSA | Mobile numbers No State Given |
| 61 | AUS | 4S | QL | +10 | Y | | Mobile numbers Queensland |
| 61 | AUS | 4S | VI | +10 | Y | FSO-FSA | Mobile numbers Victoria |
|

[benomg]

Yes.

So when the state is checked against vicidial_phone_codes it doesn't seem to match. Our 2 letter states (NT, SA, WA) are entered with the correct time zone but our 3 letter states (NSW, QLD, VIC, ACT and I guess TAS but nobody calls them anyway) default to GMT+8.

[mflorell]

You would need to convert the 3-letter states into 2-letters before you import your leads into ViciDial.

[benomg]

I'll work it out. I know it's a simple fix, it's just hard to navigate the code. Those states are NEVER referred to by 2 letters.

Also, my vicidial_phone_codes is a little different to yours, for the better as mobile numbers without geographical locations should default to GMT+8.

country_code|country|areacode|state|GMT_offset|DST|DST_range|geographic_description
61|AUS|5||+8|N||Prefix 5 nonexistant
61|AUS|4S||+8|N||Mobile numbers No State Given
61|AUS|4S|AC|+10|Y|LSO-LSM|Mobile numbers Capitol
61|AUS|2|NS|+10|Y|LSO-LSM|New South Wales & Capitol
61|AUS|4S|NS|+10|Y|LSO-LSM|Mobile numbers New South Wales
61|AUS|889|NT|+9.5|N||Northern Territory
61|AUS|4S|NT|+9.5|N||Mobile numbers Northern Territ
61|AUS|7|QL|+10|N||Queensland
61|AUS|4S|QL|+10|N||Mobile numbers Queensland
61|AUS|80|SA|+9.5|Y|LSO-LSM|Southern Australia
61|AUS|81|SA|+9.5|Y|LSO-LSM|Southern Australia
61|AUS|83|SA|+9.5|Y|LSO-LSM|Southern Australia
61|AUS|85|SA|+9.5|Y|LSO-LSM|Southern Australia
61|AUS|88|SA|+9.5|Y|LSO-LSM|Southern Australia
61|AUS|4S|SA|+9.5|Y|LSO-LSM|Mobile numbers South Australia
61|AUS|36|TA|+10|Y|FSO-LSM|Tasmania
61|AUS|4S|TA|+10|Y|FSO-LSM|Mobile numbers Tasmania
61|AUS|3|VI|+10|Y|LSO-LSM|Victoria
61|AUS|4S|VI|+10|Y|LSO-LSM|Mobile numbers Victoria
61|AUS|82|WA|+8|N||Western Australia
61|AUS|84|WA|+8|N||Western Australia
61|AUS|86|WA|+8|N||Western Australia
61|AUS|89|WA|+8|N||Western Australia
61|AUS|4S|WA|+8|N||Mobile numbers Western Austral

[mflorell]

Please post your changes(changes only, not the whole list) to that ticket that you opened in the issue tracker.

Also, please list the proper abbreviations for the Australian states and we will see how we can make this work better.

[williamconley]

are any of these states duplicates if you merely "lop off" the last character?

and if there are duplicates, are they in different time zones?

if one of those answers is "no" you could just use a substring or "left" function in mysql during import and lose the rest of the characters (and make sure they are entered into the TZ tables accordingly).

otherwise, i'd experiment with changing the field to 3 char and see if you break anything.

[benomg]

I went straight for the "otherwise" as that seems like a more proper fix to me, as long as nothing is broken. So TZ table is varchar(4) for state already so I just updated it with the full state abbreviations and that allows everything to be picked up properly. I also changed vicidial_list.state and vicidial_hopper.state from char(2) to char(3) but it's not necessary.

So...

UPDATE vicidial_phone_codes SET state = 'ACT' WHERE state = 'AC' AND country_code = '61';
UPDATE vicidial_phone_codes SET state = 'NSW' WHERE state = 'NS' AND country_code = '61';
UPDATE vicidial_phone_codes SET state = 'TAS' WHERE state = 'TA' AND country_code = '61';
UPDATE vicidial_phone_codes SET state = 'VIC' WHERE state = 'VI' AND country_code = '61';

...works a treat.

I've done what I think is testing and cannot see any issues. If you want to hold off for at least a couple of weeks before changing anything, I'll put it through a good production trial and post any issues.

I've updated the mantis ticket with the above info and with the changes to the time zone for mobile numbers without a state specified.

[mflorell]

Don't you have to also change the "state" field in vicidial_list to be VARCHAR(3)?

[williamconley]

maybe it works during import (before hitting vicidial_list)

so updates won't work, but origin is up