Custom AGI Script?

[RTOMikey]

Ok, one of my customers has a rather unique request, and I am not quite sure how to do it. He currently has a nortel system that handles his office lines and a toll-free for emergencies.

Since I have not yet quite convinced him to switch 100% to asterisk, I need some creativity. What he wants is to have the nortel answer his emergency line, froward the call to my Server with an specified inbounr route, that will in turn forward the emergency calls to a select group of cell phones. This Part I can handle.

But it gets a little hairy when he informs me that there are numerous customers that call the number expecting help and do not pay the support fees, so he wants me to create a way for the PIAF to accept the call, ask them to enter their access number, query a database to verify valid, then either pass the call to the ring group, or inform them they do not have a valid support account.

I assume the best way to do this would be to create some sort of agi script to query the database, and then send the call, or play a swift command? But I have no idea how to write agi scripts.

Any help, or other suggestions on how to accomplish this would be greatly appreciated

 

[RTOMikey]

Kind Of What I need, but with two problems. One, which I can talk him around is some of the companies have multiple lines requiring numberous entries in a DB for each possible callerid, and then the customers are restricted to those phones only, he would rather a prompt for an access code to allow progression, and I dont think smartroutes is capable of that

Second, these are going into an old Nortel Meridian system and forwarding, so the CallerID would always be the outbound line on THAT system instead of the caller

 

[rossiv]

If you don't have too many access numbers, you could direct the calls to an IVR. If the code matches, pass them to a destination. If not, play a sound and then hang up.

 

[RTOMikey]

nope, big computer consulting firm..... misc dest with this dialplan?




exten => 9856,1,MYSQL(Connect connid localhost root passw0rd accessrights)
exten => 9856,2,Read(ACCESSCODE,beep,1)
exten => 9856,3,MYSQL(Query resultid ${connid} SELECT\ accesscode\ from\ accesscodes\ where\ accesscode=${ACCESSCODE})

exten => 9856,4,(fetchrow),MYSQL(Fetch foundRow ${resultid} number)
exten => 9856,5,GotoIf($["${foundRow}" = "1"]?8)
exten => 9856,6,flite (“Sorry, the Access code you entered is invalid. Please Contact Extreme PC to purchase a valid support contract.”)
exten => 9856,7,Hangup
exten => 9856,8,flite(“Thank you, your access code has been verified. Please exten => 9856,9,wait while I connect your call.”)
exten => 9856,10,Goto(ext-group,780,1)

 

[RTOMikey]

ok, this is what I have narrowed it down to....
Database = xtremesupport
table - accesslist
field holding codes = code

When I dial in, it respomds correctly, but for some reason,l wether I enter a valid code or not, it jumps to line 10 (not valid)

any geniuses out there maybe have an idea where the error is?

 exten => s,1,Verbose
exten => s,2,MYSQL(Connect connid localhost root passw0rd xtremesupport)
exten => s,3,FLITE("Please Enter your Extreme PC PAID Support Access Code now followed by the Pound Key.")
exten => s,4,Read(ACCESSCODE,beep)
exten => s,5,MYSQL(Query resultid ${connid} SELECT\ code\ from\ accesslist\ where\ code=${ACCESSCODE})
exten => s,6,MYSQL(Fetch fetchid ${resultid} number)
exten => s,7,MYSQL(Clear ${resultid})
exten => s,8,MYSQL(Disconnect ${connid})
exten => s,9,GotoIf($[${fetchid} = 1]?12:10)
exten => s,10,Flite("Sorry, the Access code you entered is invalid. Please Contact Extreme PC to purchase a valid support contract.")
exten => s,11,Hangup
exten => s,12,Flite("Thank you, your access code has been verified. Please exten => 9856,9,wait while I connect your call.")
exten => s,13,Goto(ext-group,780,1)

 

[RTOMikey]

GOT IT!!!

For Anyone else with this need, and for reference, there are no escape strings needed in the SQL query (every post I found says they are) the final code, make sure you change to you commands and database structure is as follows:

[accessxtreme]
exten => s,1,Verbose
exten => s,2,MYSQL(Connect connid localhost root passw0rd xtremesupport)
exten => s,3,FLITE("Please Enter your Extreme PC PAID Support Access Code now followed by the Pound Key.")
exten => s,4,Read(ACCESSCODE,beep)
exten => s,5,MYSQL(Query resultid ${connid} SELECT code from accesslist where code=${ACCESSCODE})
exten => s,6,MYSQL(Fetch fetchid ${resultid} number)
exten => s,7,MYSQL(Clear ${resultid})
exten => s,8,MYSQL(Disconnect ${connid})
exten => s,9,GotoIf($["${fetchid}" = "1"]?12:10)
exten => s,10,Flite("Sorry, the Access code you entered is invalid. Please Contact Extreme PC to purchase a valid support contract.")
exten => s,11,Hangup
exten => s,12,Flite("Thank you, your access code has been verified. Please exten => 9856,9,wait while I connect your call.")
exten => s,13,Goto(ext-group,780,1)

 

[tm1000]

Just FYI. Incase anyone DOES want to use this.

The MYSQL command in asterisk is depreciated. They have moved to ODBC as has been pointed out a few times by ward.

 

[RTOMikey]

yeah, but I needed this fast, and didn't have time to learn odbc in an hour.... if anyone wants to tweak it I am all for it

 

[RTOMikey]

ok, Hammered out the ODBC, now it gets REALLY complicated, but hey, that's the way I roll, anyway, it is successfully pulling the system prompts and access codes from the DB, as well as the final Goto line... this had to be two different fields to make it cooperate Goto(${dest},${ext},1) cause if all in one field I end up with something like Goto(ext-group\,223\,1). Anyway, now that I got it all working, ${dest} set up with some to ext-group, and some to ext-local, my problem is the ext-local ONLY goes to my extension. Where do I designate if I want to send it to follow the same as in inbound call, (ie: follow me or voicemail, etc))