So I only was able to find a post somewhat relevant to this with not much info. I wanted to use an already existing MySQL database through WHMCS to give amount due etc… Does anyone happen to have a guide or some good resources to tackle such a project?
Well, its not really that trivial to make this work…
Create a dialplan code to ask the users to input a numerical item to look up. PIN number for example.
First lets create some code to ask the customer for their pin number
open up /etc/asterisk/extensions_custom.conf
Go to the bottom of the file and paste this in the code below, but altering the following portions below that are in ALLCAPS that are specific to your situation)
[customerlookup]
exten => s,1,Answer()
exten => s,n,Playback(please-enter-your&pin_number)
exten => s,n,Read(pinnumber,then-press-pound,4,,1,10)
;MYSQL connection - adjust these values to reflect your database logon info
exten => s,n,MYSQL(Connect connid DATABASE_IP SQLUSER SQLPASSWORD DATABASENAME)
;Find the matching column of the pin number, and then pull data from the column you want
exten => s,n,MYSQL(Query resultid ${connid} select COLUMN_NAME_TO_PULL from TABLENAME where COLUMN_WITH_MATCHING_IVRDATA=${pinnumber})
;pull the matching data (IF any) and store it in the variable "COLUMN_NAME_TO_PULL"
exten => s,n,MYSQL(Fetch fetchid ${resultid} resultfound)
;cleanup connection to database
exten => s,n,MYSQL(Disconnect ${connid})
;If no matching data, system plays "hangup and try again", then it hangs up the phone.
exten => s,n,ExecIf($["${resultfound}"=""]?playback(hangup-try-again))
exten => s,n,ExecIf($["${resultfound}"=""]?HANGUP())
;if the resultfound contains data, the system says "THANK YOU" and then says the value in the variable
exten => s,n,PlayBack(auth-thankyou)
exten => s,n,SayDigits(${resultfound})
exten => s,n,Hangup()
Save those changes
EDIT:
create a “Custom Destination”
Create a destination called
" customerlookup "
and for “Custom Destination” put in
customerlookup,s,1
Save those changes.
Now, create a route or something to call that customer destination. Can use a ring group to point to it or an IVR prompt or inbound route.
Give it a run. System should ask you to input a value, then will query the database for that data and return the result.
Alright so I have everything where it is supposed to be and modified. Hopefully this doesn’t sound dumb, however, what are the steps to place a call to this script to test it?
Create a Custom Destination with a goto string of
customerlookup,s,1Yea my bad, I forgot to put in the step, I updated my post
The third part module dynroute can do the bulk (maybe even all) of what you’re looking for in the GUI.
It works! Thank you so much! I think I’ll have a good basis to start, appericate your time!
Awesome glad it worked out for you. I’ve got a customer that uses that code verbatim and do thousands of SQL hits a day with it.
@lgaetz is correct, if you install the dynroute module, you can do all of the code implementation through the GUI. You’ll use the code sequence above, but it will take care of a lot of the housekeeping of code implementation and you don’t have to edit .conf files if you aren’t comfortable doing that.
I’ll have to give it a try!
This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.