i am working on a customer reporting solution based on data provided by Asterisk Cdr.
i’ve a table in DB with almost 18500 Codes around the globe.
Asterisk is stories its cdr into mysql db with the help of adaptive odbc .
What i want is to match countrycode+area code with dialed number and display in front of dialed number.And offcourse depending on highest length match of prefix and dialed number.
for example , if i’ve codes in table like 44 for UK and 441 for Uk landline , i want to have 441123123 with UK Landline only
while results match both country having prefix 44 and 441.
i am trying in SQL from last 2 days but not getting an efficient way. if a raw query does so , on each time it has to filter 18500 codes which slows down everything.
So is it some better way from asterisk itself , or may be some AGI can strip the prefix and send it to database itself instead of running some AFTER CDR function ?
Any help will be highly appreciated