Build CID Route Table on FreePBX using Bash

Mohammed Hamada
Mohammed Hamada used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
nociSoftware Engineer
Distinguished Expert 2018

Commented:
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 Consultant

Author

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
Software Engineer
Distinguished Expert 2018
Commented:
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

Mohammed HamadaSenior IT Consultant

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial