Geodata for Germany queried from opengeodb

[Framercy]

Hello there,

in the last weeks I spend some time to query all German area and postal codes from the Opengeodb into seperate tables.

At this time I have the option to spend some more time to overwork the data and insert it into the tables vicidial_postal_code and vicidial_phone_codes.

Background is this: I want to define german state call times?

If I will insert the right data in the right format and delete the old german phone code entry - will this alone work?
Or has to be hard coded something else in the scripts to recognise the new data?

Best regards
Fram

[williamconley]

database changes alone should do the trick. but it's much simpler to do a single entry test. put in a fake postal code with a fake time zone ... and enter a new lead with that fake postal code and see if the system grabs the fake time zone.

remember to always post your vicidial version with build. (you got everything else very well, but that is a requirement ...)

[Framercy]

Thanks William for the quick response on that...

Then I will go ahead with this when my work is done with this German interface translation. My customers want this inbound lead search thing first to get rid of some of their back office tasks...

I started this topic because I wasn't so sure if it works so simple because I found some hard coded area code in one of the shipped scripts (vdc_script_notes.php) that irritated me.

Thanks
Fram

ps: There are no more signs left in my signature but I managed to add the svn version. Better?

[williamconley]

Vicidial 2.6b0.5

incomplete version: include "build" which is shown just after this on almost every admin page in Vicidial. LOL

many of the "extra" sample scripts have unusual coding. The Vicidial Group includes them to help create similar content. If you improve one (by removing hard-coded items and replacing them with db lookups ...) feel free to submit them in the Vicidial Issue Tracker to replace the original (or submit a DIFF against the old one so it may merely be updated).

[Framercy]

Hello,

got the postal codes complete and inserted into a copy of table vicidial_postal_codes.
A small test with ADMIN_adjust_GMTnow_on_leads.pl showed that the data seems to be complete and no codes are missing here.
The area codes are a little bit more difficult at the moment because a lot of area codes are missing in Opengeodb (What tests with ADMIN_adjust_GMTnow_on_leads.pl on my exported area codes showed either).

I found out that our

Bundesnetzagentur

hosts a list with German area codes

Ortsnetzbereichsverzeichnis (ONB-Verzeichnis)

for download.

Within this list lacks the state value, but a location key is given from witch the state value could be calculated with some effort.
And that's what I want to do next.

We have only one time zone here in Germany and therefore only little use of this zones.
What I want to do is defining state call times because we have some celebration days here that get celebrated only in some of our states. I want to block these states outbound calling on these local celebration days...

Best regards
Fram

[Framercy]

Hello,

I think I got it.

German postal- and area codes data seems to be complete and consistent. ADMIN_adjust_GMTnow_on_leads.pl showed that only leads with mobile numbers as their main number are missing the correct timezone after running in country and area code mode. What is totally correct since the german mobile prefixes are missing in my area codes.
The script run with --postal-code-gmt catched all of my leads with a correct postal code in it.

With this data I managed to update or query the state field in all of my leads to the correct value with one sql query...

I will put the data into 2 separate tables and upload the sql export to the issue tracker for the community.

Since German area codes have a length between 3 and 6 digits, a change in the table layout of vicidial_phone_codes will be necessary to insert the data.

A trailing '0' is added to the codes because that's the way the main numbers are stored in our system - the phone code 49 is leaved out while dialling therefore.
This way I had to made no major changes here. Shouldn't be so hard to remove the 0 with:

Code: Select all

UPDATE vicidial_phone_codes SET areacode = TRIM(LEADING '0' FROM areacode) WHERE ...

Best regards
Fram