Build CID Route Table on FreePBX using Bash

My customer has 11 offices distributed over 11 states, for each of those states this customer uses Skype for business Client (endpoint) to make/receive calls.
They have a main toll free number which their clients call them on.
I would like to route the caller based on their state they are calling from to the office number.

I know this can be done via FreePBX because I have already done a test for 3 numbers however in order to go through the steps of uploading the database of NPA, States and creating routes based on these numbers (state codes) I would like to know the step by step procedure to do so.

I have asked in the FreePBX forum and they have given me the how to but it's not clear to me how to do so because I am fairly new to the batches and scripts on FreePBX bash. I would appreciate any help .

I am writing down call follow and how things are supposed to work.

My Customer Toll free Number = 888-XXX-XXX
Every one of their offices has a main number = 877-XXX-XX1/2/3-12

Assuming I called from Newyork with CID 203-XXX-XXX to the Main Toll Free 888-XXX-XXX the call in this case should be routed to NewYork's office number.
I already built the CSV file which has all codes/states abbreviations but need to know how to build routes based on this.

Thank you
LVL 24
Mohammed HamadaSenior IT ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nociSoftware EngineerCommented:
How does an entry need to be formatted and how is a record entered.
ie. How does one line of input look like (if there are multiple case please show ieach case) and how should it be transformed.
You may obfusciate the data, only the exact input & output syntax is needed.
Mohammed HamadaSenior IT ConsultantAuthor Commented:
Hi Noci,

I have got the commands, they are as following including the entries but it seems I am having an issue

Below is what I have been told to do on freepbx forum however, it seems there's some problem with the XLS! The order according to freepbx's database must be in the following format "database put <family> <key> <value>"

When I created a CSV file with NPA,State,State Code the commands below worked fine but it didn't show as expected in the result.

When I tried to import the CSV to the database with CSV data excluding , and adding spaces only between NPA,State and State code I got an error saying

Usage: database put <family> <key> <value>
       Adds or updates an entry in the Asterisk database for
       a given family, key, and value.

So it seems there's a problem with the command or with the csv file that I am using. I am attaching the file

just download at:-

http://media.juiceanalytics.com/downloads/area_codes_by_state.xls

convert by whatever means to .csv

Create a database key on NPA with value = state

while IFS=, read -r NPA STATENAME STATE; do echo "rasterisk -x 'database put NPA $NPA $STATE'"; done < area_codes_by_state.csv|bash

Create a database key on state with value = destination

for i in $(cat area_codes_by_state.csv |cut -d ',' -f3|sort -u);do echo "rasterisk -x 'database put STATE $i \${EXTEN}'";done|bash

update ${EXTEN} to the destination for any states you cover for example:-

rasterisk -x 'database put STATE 4566'

Then something like (pseudo code)

[custom-inbound-context]
Exten => _NXXNXXXXXX,1,DBGET state from CID
Exten => _NXXNXXXXXX,n.Goto(DBGETdestination from state

Open in new window

area_codes_by_state.csv
nociSoftware EngineerCommented:
I guess the problem is wit h the 3 or 4 column type.
In your CSV all spaces became , and Washington DC already has a ,

The First line from the CSV has to go...., hence grep -v "Code" needs to be added to the command strin.

The next statement will repair this: ( read one more field, if it is fill the previous one belongs to the middle...
And are  3 character statenames correct? ATL, HOU, CHI?
while IFS=, read -r NPA STATENAME STATE STATE4; do if [ "$STATE4" != "" ] ; then STATE=$STATE4; fi ; echo "rasterisk -x 'database put NPA $NPA $STATE'"; done < area_codes_by_state.csv | grep -v "Code"| bash 

Open in new window



The next loop can better be written as:  (appearantly the source has CR characters, or those are produced somewhere)
And the same 3/4 column issue so:  (code not correct yet....)
while IFS=, read -r NPA STATENAME STATE STATE4 ; do if [ "$STATE4" != "" ] ; then STATE=$STATE4; fi ; echo "$STATE" ; done < area_codes_by_state.csv | sort -u | while read STATE ; do "rasterisk -x 'database put STATE $STATE \${EXTEN}'" ; done |grep -v "Code" 

Open in new window


You will need an extra file. like  State_number.txt
ATL 91234
HOU 92345
DC 93456
etc.

Open in new window


while IFS=, read -r NPA STATENAME STATE STATE4 ; do if [ "$STATE4" != "" ] ; then STATE=$STATE4; fi ; echo "$STATE" ; done < area_codes_by_state.csv | sort -u | while read STATE ; do EXTEN=$( grep $STATE state_number.txt | read STATE EXTEN ; echo $EXTEN ) ; "rasterisk -x 'database put STATE $STATE ${EXTEN}'" ; done |grep -v "Code" | bash 

Open in new window


This is hard to read so here as a script.... (same code).
#!/bin/bash
while IFS=, read -r NPA STATENAME STATE STATE4
do 
   if [ "$STATE4" != "" ] 
   then 
      STATE=$STATE4; 
   fi 
   echo "rasterisk -x 'database put NPA $NPA $STATE'"
done < area_codes_by_state.csv | grep -v "Code" | bash

while IFS=, read -r NPA STATENAME STATE STATE4 
do 
   if [ "$STATE4" != "" ] 
   then 
      STATE=$STATE4
   fi
   echo "$STATE"
done < area_codes_by_state.csv | sort -u | grep -v "Code" | while read STATE 
do 
   STATE=$(echo "$STATE" | sed 's/\r//' );
   EXTEN=$( grep $STATE state_number.txt | read STATE EXTEN ; echo $EXTEN ) 
   echo "rasterisk -x 'database put STATE $STATE ${EXTEN}'" 
done | bash 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mohammed HamadaSenior IT ConsultantAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
freepbx

From novice to tech pro — start learning today.