Asterisk Dialplan SQL integration with vTIGER

We use asterisk in our contact center using the Issabel distribution.
A caller in the IVR can enter their ticket number via the touch tone on their phone

Asterisk then looks up that ticket number in the vTiger CRM database, and when the call is delivered to an agent it pushes the URL to their contact center screen pop, but the URL is formatted to automatically show the ticket the caller is requesting.

1. The diaplan is to get the ticket number and query the database.
2. It asks for the caller to enter their ticket number and press # pound symbol.
3. Then it does a SQL look up in vTiger database to find that ticket number and then find its internal designation.

Here is the code in "extensions_custom.conf"

[getticket]

exten => s,1,Read(ticketnumber,then-press-pound,7,,1,10)

exten => s,2,MYSQL(Connect connid 10.1.100.217 asterisk password01 vtiger710)

;this will get us a ticket number

exten => s,3,MYSQL(Query resultid ${connid} SELECT ticketid FROM vtiger_troubletickets WHERE ticket_no = ${ticketnumber})

exten => s,4,MYSQL(Fetch fetchid ${resultid} vttnumber)

exten => s,5,MYSQL(Disconnect ${connid})

exten => s,6,NoOp(&& the value found is ${vttnumber} )

exten => s,7,goto(ext-queues,5000,1)

So
Line 1 (s,1) asks the caller to enter their ticket number

Line 2 (s,2) opens a connection to the SQL server, and connects to the database

Line 3 queries the database to search for the ticket number.  Ticket numbers that are displayed in Vtiger are different then the number that Vtiger uses in the database to keep track of them.  So this SQL statement finds the ticket number that the users would see, and translates it to the proper reference number that the database uses.

Line 4 Fetches the data that the SQL statement found and saves that number in variable "vttnumber"

Line 5 Closes the connection the database

Line 6 Echos the value found into the asterisk live log

Line 7 Sends this particular call to queue 5000


Now in the Issabel call_center system, we use URL pop to push that data to the customer screen using the external URL application and putting in the varilable "vttnumber" into the URL

http://10.1.100.217/vtigercrm/index.php?module=HelpDesk&view=Detail&record={vttnumber}&app=SUPPORT



Heres a more functional example:

User is asked to put a number into the IVR, sql call to a table to match that number

[macro-pincollection] exten => s,1,Answer() ;set a variable to prevent looping over and over in IVR, either by accident or malicious exten => s,n,Set(Attempts=1) ;throw a entry in the asterisk log for troubleshooting exten => s,n(begin),NooP(&&&&&&& Collecting PIN info for ${CALLERID(all)} attempt ${Attempts} &&&&&) ;ask the caller to entier their pin. exten => s,n,Playback(please-enter-your&pin_number) ;system listens for DTMF and records it in variable "pinnumber" ;the syntax at the end= 7=MAX DIGITS, 1=attempts if no data, 10=timeout. The ",," value i'm not using in this code example) exten => s,n,Read(pinnumber,then-press-pound,7,,1,10) ;system checks for blank value and asks again exten => s,n,ExecIf($["${pinnumber}"=""]?goto(macro-pincollection,s,1)) ;systems plays back the number they entered in to caller exten => s,n,Playback(you-entered) exten => s,n,SayDigits(${pinnumber}) ;system asks them to press 1 to accept or 2 to retry exten => s,n,Playback(if-this-is-correct-press) exten => s,n,SayDigits(1) exten => s,n,Playback(otherwise-press) exten => s,n,SayDigits(2) ;system listens for the value entered exten => s,n,Read(digi,,1) ;this section we test for invalid entries. On their 4 try we hangup. We dont want someone looping over and over again. exten => s,n,Set(Attempts=${MATH(${Attempts}+1,i)}) ;if they reach their 4th attempt, system plays some messages and hangsup. ;Change the "4" to a different value if you want to increase/decrease chances ;Change the "HANGUP to RETURN" if you want the system to go back to a different location as set in the CUSTOM DESTINATIONS section exten => s,n,ExecIf($["${Attempts}"="4"]?playback(sorry-youre-having-problems)) exten => s,n,ExecIf($["${Attempts}"="4"]?playback(hangup-try-again)) exten => s,n,ExecIf($["${Attempts}"="4"]?HANGUP()) ;if user presses 1 to confirm, system sends the call on to the SQL lookup exten => s,n,ExecIf($["${digi}"="1"]?goto(checkvalue)) ;if callers presses any other digit, system will re-ask them to enter in their number exten => s,n,goto(macro-pincollection,s,begin) ;now we call a SQL command to look up the data. Change LOCALHOST, USER PASSWORD and SQL_DATABSE_NAME to match what you use that you use exten => s,n(checkvalue),MYSQL(Connect connid localhost USERNAME PASSWORD SQL_DATABASE_NAME) ;query the database for the pinnumber entered in the IVR and select data from your TABLE and COLUMN that fits your database exten => s,n,MYSQL(Query resultid ${connid} select * from TABLE where COLUMN='${pinnumber}') ;fetch the result exten => s,n,MYSQL(Fetch fetchid ${resultid} number) ;close database connection exten => s,n,MYSQL(Disconnect ${connid}) ;this echos the result to the livelog what the asterisk system found. exten => s,n,NoOp(&& the value found is ${number}) exten => 1,n,GotoIf($[${pinnumber} = ${number}]?10:20) ;go hear if match exten => 1,10,NoOp(&&&&&MATCHING DATA!) exten => s,n,Hangup() ;go hear if NO match exten => 1,20,NoOp(&&&&& NO MATCHING DATA!) exten => s,n,Hangup()