Simple IVR database interaction

I was in need of a simple app or utility to have drivers call into our system and enter in some information. Mainly it was unit number, odometer reading, and hours worked.

I asked around and did some reading so I thought I would post what I did to see if it was useful to anyone else.

First, add a misc. destination of what ever number (1234) you like and call it what ever you want (My Input App).

Next, add to your IVR an option to get to the misc destination. I used the same number (1234)

Then in your extensions_custom.conf file add this

[from-internal-custom]
exten => 1234,1,Answer
exten => 1234,2,Wait(1)
exten => 1234,3,Playback(custom/Welcome_message)
exten => 1234,4,Authenticate(1234) <---- Change this to a password you can give to the people calling in.
exten => 1234,5,Goto(MyCustomApp,s,1) <----- Change MyCustomApp to what ever you like that is the context your calling

[MyCustomApp]
exten => s,1,Set(TIMEOUT(digit)=7)
exten => s,2,Set(TIMEOUT(response)=10)
exten => s,3,Playback(custom/Message) <-------- Record a message telling the user what to enter
exten => s,4,Read(UNIT|beep|10||2|5) <ā€” Unit is the variable to hold your digits, beep is a a file that is played, 10 is max digits, 2 is attempts and 5 is time out.

exten => s,5,SayDigits(${UNIT}) <----- Repeat the number back to the user
exten => s,6,Playback(if-this-is-correct-press)
exten => s,7,Playback(digits/1)
exten => s,8,WaitExten()
exten => 1,1,Playback(custom/Message) <---- if they press 1 that means all is good and it goes here for them to enter the next bit of info if they press any other number it starts at the top again.
exten => 1,2,Read(ODO|beep|10||2|5)
exten => 1,3,SayDigits(${ODO})
exten => 1,4,Playback(if-this-is-correct-press)
exten => 1,5,Playback(digits/3) <------ if it is correct press 3 and then it moves on to the Query
exten => 1,6,WaitExten()
exten => 3,1,MYSQL(Connect connid 127.0.0.1 username password database) <-- This is your connect string, enter in your user name, password and the database that you will be sending the data to

exten => 3,2,MYSQL(Query r ${connid} Insert Into\ MyTable\ SET\ Unit_Number=${UNIT},Odometer_reading=${ODO},Date_d=Now()) <ā€” This appends the data to the table in the database.

exten => 3,3,MYSQL(Disconnect ${connid}) <---- Disconect from the database then hang up.
exten => 3,4,Playback(thank-you-cooperation)
exten => 3,5,Playback(goodbye)
exten => 3,6,Hangup
exten => t,1,Goto(s,3)
exten => i,1,Goto(s,3)
exten => o,1,Goto(s,3)
exten => h,1,Hangup

You also have to create a database that has the table or tables that you want to add data to. The MySQL command will take standard slq query data, so you can pull data if you want as well.

Yes this is very basic and my first attempt at any sort of dial plan or user interaction. Iā€™m sure many can offer some hints and tips but if it helps you to get started great.

My next attempt will do some verification. It will first check the unit number against another table to see if it is a valid unit number and then I will create a bit of logic to make sure that the odometer reading they entered is within a pre-defined range.

I know you can do this with a PHP script as well but I have know php knowledge and needed somthing quickly.

Rob

Hi,

Im trying to get your script working but i have some problems with it.
I added a misc that dials 1234 and i have a ivr with entries 1234.
In the extensions_custom.conf i put the following code:

exten => 1234, 1, Ansner
exten => 1234, 2, Wait(1)
exten => 1234, 3, Playback(en/goodbye)
exten => 1234, 4, Hangup

only the problem is when i call and in the ivr i input 1234 de call is hangup without playing the goodbye.

Can you maby help me?

It works but when i typed the password it says invalid password, but i typed 1234.

And it repeats, again and again, i am using X-lite Phone do you recommend another?

Can you show us your code?