MYSQL Command in TPV Dialplan

[IslandCabby]

Hi guys,

Thinking of using the MYSQL command with an extension to setup an automatic TPV recording system (Purpose would be to write variables to a TPV database which I'd then have the verification processor log into to confirm compliance). I can already pull the variables and use them to create a file, everything works ok (see below dialplan if anyone's been looking for something like this.) It appears I'd have to install the add-ons package from digium or a tarball in order to do this. Has anyone done this before with success or does it affect other components? (negatively). Does anyone have any suggestions on a better way of doing this?

VERSION: 2.4-361a
BUILD: 120221-1647
Asterisk Version: 1.4.39.2-vici built by abuild @ build07 on a x86_64 running CentOS

Dialplan:

;TPV IVR English
exten => 8091,1,Answer()
;Welcome Message
exten => 8091,n,Playback(prompt1112)
;Asks for AgentID (numeric ID used by client)
exten => 8091,n,Read(aid,prompt1113,4,noanswer,4)
;Asks for custphonenum (call comes in to extension from an agent, hence using this instead of CALLERID)
exten => 8091,n,Read(cusid,prompt1114,10,noanswer,4)
;Duct-tape way of waiting for a 1 before starting recording at the same time allowing agent to conf customer in before proceeding
exten => 8091,n,read(continue,prompt1115,1,noanswer,4)
;Instructions for customer regarding TPV process
exten => 8091,n,Playback(prompt1118)
exten => 8091,n,Wait,2
exten => 8091,n,Record(/yourrecordingdirectory/TPV${aid}${cusid}:wav)
;agent and customer advised confirmation number will be read;
exten => 8091,n,Playback(prompt1119)
exten => 8091,n,Playback(confirm-number-is)
exten => 8091,n,SayAlpha(TPV${aid}${cusid})
;exten => 8091,n,MYSQL(Connect connid localhost dbuser dbpass database)
;Understand that the connid/resultID variables are necessary so the connection will be closed and any info cleared however using an INSERT instead of SELECT command so unsure whether this applies
;exten => 8091,n,MYSQL(Query resultid ${connid} INSERT\ into\ TPV\ (recording,TPV_Status,TPV_Status_Reason)\ VALUES(TPV${aid}${cusid},Unverified,New))
;exten => 8091,n,MYSQL(Clear ${resultid})
;exten => 8091,n,MYSQL(Disconnect ${connid})
exten => 8091,n,Wait,2
;Numbers are read back twice
exten => 8091,n,Playback(prompt1120)
exten => 8091,n,SayAlpha(TPV${aid}${cusid})
exten => 8091,n,Playback(prompt1121)
exten => 8091,n,Hangup

[williamconley]

You would do much better to create an AGI script to perform these tasks, and merely pass the call to the AGI script based on a menu choice. Lots of sample versions (such as the agi did script ... ). The first part is always the same (security, get basic values regarding setup ...), then you can use all these same commands in a scripted language that already has database access due to grabbing the DB values from /etc/astguiclient.conf

Happy Hunting!

[IslandCabby]

I'm actually trying to move AWAY from heavy coding, has anyone sucessfully added the mysql addon only? I've looked at the documentation regarding doing it however it crashes on make clean and I want to make sure I'm not messing up vicibox. All I need is for the mysql command to be available in the dialplan. What happens is t hat the values are being keyed in by an agent vs being pulled from calldata