IVR-Base de datos.
En esta entrada vamos a probar el Elastix (RIP) en su versión
4, obviamente esta configuración es válida para Asterisk.
No explicare cómo funciona el dialplan, pero si tienen
alguna duda o comentario estoy más que a la orden.
Podremos ver como recoger datos de una llamada
activa, convertirlas en variables y consultar una base de datos.
Para la administración de base de datos, usaremos
MysqlWorkbench.
Escenario
Supongamos que tenemos un cliente que nos solicita un
sistema que pueda informar el saldo o balance de su cuenta con las siguientes condiciones, que el usuario debe ingresar su cédula, número de tarjeta y pin de la tarjeta.
Planificación
Necesitaremos una BD y una tabla que incluya los siguientes campos.
Columns:
id
|
mediumint(8) UN AI PK
|
nombre
|
varchar(255)
|
pin
|
varchar(255)
|
cedula
|
mediumint(9)
|
saldo
|
mediumint(9)
|
tarjeta
|
varchar(255)
|
En la que:
Id = lo usaremos para identificar nuestros registros
Nombre =
Nombre del “cliente”
Pin = pin de la tarjeta
Cedula = cedula del cliente
Saldo = al saldo o balance de la tarjeta
Tarjeta = número de la tarjeta
Crear base de
datos
Se utilizó el programa MySQL Workbench 6.3 para administrar
tablas y columnas, solamente se creó la base de datos directamente desde el
servidor.
MariaDB [(none)]> CREATE DATABASE ivr;
Crear tabla desde
el workbench
use ivr ;
CREATE
TABLE `consultasaldo` (
`id` mediumint(8) unsigned NOT NULL
auto_increment,
`nombre` varchar(255) default NULL,
`pin` varchar(255),
`cedula` mediumint,
`saldo` mediumint,
`tarjeta` varchar(255),
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
Una vez creada
podemos hacer un insert con estos datos ficticios
INSERT INTO
`consultasaldo` (`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES ("Xenos
Roberts","1952",1000000,80000,"5289189197495478"),("Guy
Frank","8389",999999,81024,"5436838962304701"),("Emerson
Holmes","4782",999998,82048,"5155538874222442"),("Yasir
Weaver","9586",999997,83072,"5186691021189338"),("Melvin
Cherry","4720",999996,84096,"5180135775111516"),("Barry
English","9178",999995,85120,"5349546772192739"),("Hunter
Richardson","9562",999994,86144,"5466555386389950"),("Axel
Manning","6411",999993,87168,"5359126912999017"),("Theodore
Hewitt","9652",999992,88192,"5595052758928809"),("Ryan
Mathews","4238",999991,89216,"5397617379840847");
INSERT INTO
`consultasaldo` (`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES
("Kadeem Wise","4424",999990,90240,"5262521203855678"),("Amir
Moore","7425",999989,91264,"5515504504631274"),("Zachery
Rush","4026",999988,92288,"5214467948709599"),("Owen
Acosta","9237",999987,93312,"5394424055023837"),("Thomas
Humphrey","1862",999986,94336,"5210261440457938"),("Brody
Price","4204",999985,95360,"5179830329339471"),("Carlos
Pate","8850",999984,96384,"5170245525180711"),("Hamish
May","8665",999983,97408,"5592949789982981"),("Kelly
Randolph","5642",999982,98432,"5246184054233767"),("Carson
Sanford","3767",999981,99456,"5444155212685106");
INSERT INTO
`consultasaldo` (`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES ("Gil
Floyd","6166",999980,100480,"5376146073834331"),("Magee
Blake","9055",999979,101504,"5598438636321506"),("Orlando
English","1618",999978,102528,"5238654392494206"),("Fitzgerald
Rowe","4091",999977,103552,"5248539462448588"),("Keane
Acevedo","6316",999976,104576,"5552578747064257"),("Drake
Marks","6416",999975,105600,"5138958716344026"),("Malik
Hopper","3878",999974,106624,"5107597067455580"),("Bernard
Guy","7858",999973,107648,"5261651118979486"),("Dustin
Hansen","6362",999972,108672,"5158442295882149"),("Luke
Lloyd","7472",999971,109696,"5498234418380278");
INSERT INTO
`consultasaldo` (`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES ("Len
Robles","3709",999970,110720,"5457028537674671"),("Jarrod
Morrow","9042",999969,111744,"5385908990305110"),("Edward
Clayton","2363",999968,112768,"5588240865228879"),("Gil
Vinson","7445",999967,113792,"5113413916883810"),("Timothy
Martinez","7872",999966,114816,"5298387836098591"),("Eagan
Mcguire","4716",999965,115840,"5218231580055778"),("Neville
Murphy","6624",999964,116864,"5293502129315628"),("Reese
Beach","4057",999963,117888,"5213316124149586"),("Gregory
Greer","1993",999962,118912,"5158806017106179"),("Luke
Gibbs","8196",999961,119936,"5161291984172709");
INSERT INTO
`consultasaldo` (`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES ("Beau
Roberts","1788",999960,120960,"5547320719416324"),("Plato
Holden","7419",999959,121984,"5148903468672206"),("Dolan
Morton","6123",999958,123008,"5304883078566933"),("Harlan
Chapman","4581",999957,124032,"5392890294932982"),("Arthur
Hodges","4999",999956,125056,"5501126047856287"),("Neville
Leach","5887",999955,126080,"5387833156909307"),("Arthur
Ramos","4812",999954,127104,"5466988998779856"),("Eric
Jennings","8802",999953,128128,"5317955419069796"),("Caleb
Hicks","3165",999952,129152,"5539285538412640"),("Dante
Glass","1240",999951,130176,"5137047782361246");
INSERT INTO
`consultasaldo` (`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES ("Hilel
Contreras","9898",999950,131200,"5321234985072723"),("Tate
Nichols","1693",999949,132224,"5225869336507328"),("Orlando
Charles","5089",999948,133248,"5472072920453484"),("Xanthus
Greene","8858",999947,134272,"5120783054550939"),("Blaze
Gallegos","2972",999946,135296,"5465844945581039"),("Preston
Jennings","3220",999945,136320,"5381391602559560"),("Ahmed
Finch","5834",999944,137344,"5295257867273780"),("Neville
Barrera","5245",999943,138368,"5498456936002205"),("Tate
Dodson","3479",999942,139392,"5104169348708402"),("Hedley
Fleming","8154",999941,140416,"5130550582437287");
INSERT INTO
`consultasaldo` (`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES ("Hu
Kerr","8086",999940,141440,"5324878619549493"),("Lewis
Donaldson","4945",999939,142464,"5531667004122332"),("Ferdinand
Odonnell","8314",999938,143488,"5418431798967081"),("Brennan
Spence","7270",999937,144512,"5208910056062648"),("Dean
Ratliff","6697",999936,145536,"5386174646777413"),("Hashim
Cunningham","7881",999935,146560,"5449221367896032"),("Louis
Palmer","9554",999934,147584,"5252682399768901"),("Russell
Sanders","1522",999933,148608,"5355587690683715"),("Coby
Moore","5374",999932,149632,"5513153357346275"),("Richard
Boyle","4631",999931,150656,"5584823801501097");
INSERT INTO
`consultasaldo` (`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES
("Jermaine
Norman","1492",999930,151680,"5250962061730419"),("Yasir
Pratt","3976",999929,152704,"5367704467340934"),("Duncan
Stevenson","9993",999928,153728,"5498576713215777"),("Alan
Parker","8230",999927,154752,"5286357556032645"),("Prescott
Santiago","6632",999926,155776,"5459145917092213"),("Theodore
Barnett","7244",999925,156800,"5228170144771736"),("Baxter
Lang","5922",999924,157824,"5213228067040567"),("Leroy
Patrick","8563",999923,158848,"5569396807196260"),("Blake
Padilla","5676",999922,159872,"5231912413239332"),("Arden
Perkins","7222",999921,160896,"5355901137710942");
INSERT INTO `consultasaldo`
(`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES ("Norman
Logan","1746",999920,161920,"5254213730901049"),("Igor
Gardner","2203",999919,162944,"5296290936831535"),("Logan
Cummings","7446",999918,163968,"5598098073500085"),("Ryder
Medina","8325",999917,164992,"5369581818531280"),("Coby
Sosa","9946",999916,166016,"5310646970421479"),("Cadman
Kirkland","9079",999915,167040,"5300397584369553"),("Ralph
Griffin","9271",999914,168064,"5293441997383360"),("Gabriel
George","4759",999913,169088,"5572479208274756"),("Upton
Higgins","6203",999912,170112,"5467542634862417"),("Kadeem
Heath","4309",999911,171136,"5402931362441106");
INSERT INTO `consultasaldo`
(`nombre`,`pin`,`cedula`,`saldo`,`tarjeta`) VALUES ("Justin
Ferguson","9363",999910,172160,"5517492761215734"),("Myles
Dudley","4270",999909,173184,"5510323009983741"),("Josiah
Humphrey","1292",999908,174208,"5104651344919638"),("Baxter
Grant","7666",999907,175232,"5380601965859352"),("Ryder
Hubbard","9050",999906,176256,"5582772516695755"),("Uriel
Lambert","4167",999905,177280,"5198751516173969"),("Rogan
Davidson","6776",999904,178304,"5477811976095813"),("Chaim
Bradley","1135",999903,179328,"5119414154117295"),("Joel
Figueroa","8721",999902,180352,"5320044477254684"),("Dustin
Lang","6048",999901,181376,"5410490727387280");
Ahora comprobamos que los datos se hayan insertado en la
tabla consulta de saldo
SELECT * FROM consultasaldo;
Deben ver datos similares a estos
Con esto tendremos preparada la base de datos para las
consultas desde nuestro dialplan.
Extensions_custom.conf
Ahora editamos el archivo
vim /etc/asterisk/extensions_custom.conf
Revisamos y pegamos es siguiente contenido
[local2]
exten => _*10,1,Goto(consultadb,s,1) ;cuando marquemos *10 saltara al contexto consultadb
[consultadb] ;contexto para la consulta
exten => _s,1,Answer
exten => _s,n,Set(loop=0)
exten => _s,n,Set(TIMEOUT(digit)=3)
exten => _s,n(start),agi(googletts.agi,"Gracias por llamar. Digite su numero de cedula despues del tono. Y presione el simbolo numeral",es,,1.25)
exten => _s,n,Set(loop=$[${loop}+1])
exten => _s,n(incrementoloop),NoOp(${loop})
exten => _s,n,Playback(beep)
exten => _s,n,Read(ceduladigitada,,20)
exten => _s,n,GotoIf($[${loop} > 2]?final)
exten => _s,n,Gotoif($["${ceduladigitada}" = ""]?start:ConfirmacionCed)
exten => _s,n(ConfirmacionCed),agi(googletts.agi,"Su numero de cedula es",es,,1.26)
exten => _s,n,SayDigits(${ceduladigitada},m)
exten => _s,n,agi(googletts.agi,"Digite 1, si es correcto, Digite 2 si es incorrecto",es,,1.26)
exten => _s,n,Background(silence/4)
exten => _s,n,WaitExten(4,)
exten => 2,1(ivrsel-2),Goto(consultadb,s,1)
exten => 1,1(ivrsel-1),Goto(Numerotarjeta,s,1)
exten => h,1(final),Hangup
[Numerotarjeta]
exten => _s,1,Answer
exten => _s,n,Set(loop=0)
exten => _s,n,Set(TIMEOUT(digit)=3)
exten => _s,n(start),agi(googletts.agi,"Digite su numero de tarjeta, despues del tono",es,,1.26)
exten => _s,n,Set(loop=$[${loop}+1])
exten => _s,n(incrementoloop),NoOp(${loop}
exten => _s,n,Playback(beep)
exten => _s,n,Read(numerodetarjeta,,30)
exten => _s,n,GotoIf($[${loop} > 2]?final)
exten => _s,n,Gotoif($["${numerodetarjeta}" = ""]?start:Confirmaciontarj)
exten => _s,n(Confirmaciontarj),agi(googletts.agi,"Su numero de tarjeta es",es,,1.26)
exten => _s,n,SayDigits(${numerodetarjeta},m)
exten => _s,n,agi(googletts.agi,"Digite 1, si es correcto, Digite 2 si es incorrecto",es,,1.26)
exten => _s,n,Background(silence/4)
exten => _s,n,WaitExten(4,)
exten => 2,1(ivrsel-2),Goto(Numerotarjeta,s,1)
exten => 1,1(ivrsel-1),Goto(pin,s,1)
exten => h,1(final),Hangup
[pin]
exten => _s,1,Answer
exten => _s,n,Set(loop=0)
exten => _s,n,Set(TIMEOUT(digit)=3)
exten => _s,n(start),agi(googletts.agi,"Digite el pin de su tarjeta despues del tono.",es,,1.26)
exten => _s,n,Set(loop=$[${loop}+1])
exten => _s,n(incrementoloop),NoOp(${loop}
exten => _s,n,Playback(beep)
exten => _s,n,Read(PIN,,4)
exten => _s,n,GotoIf($[${loop} > 2]?final)
exten => _s,n,Gotoif($["${numerodetarjeta}" = ""]?start:Confirmacionpin)
exten => _s,n(Confirmacionpin),agi(googletts.agi,"El pin de su tarjeta es:",es,,1.26)
exten => _s,n,SayDigits(${PIN},m)
exten => _s,n,agi(googletts.agi,"Digite 1, si es correcto, Digite 2 si es incorrecto",es,,1.26)
exten => _s,n,Background(silence/4)
exten => _s,n,WaitExten(4,)
exten => 2,1(ivrsel-2),Goto(pin,s,1)
exten => 1,1(ivrsel-1),Goto(conexionmysql,s,1)
exten => h,1(final),Hangup
[conexionmysql]
exten => _s,1,Answer
exten => _s,n,NoOp(Probando MySQL)
exten => _s,n,NoOp(Conectando con MySQL)
exten => _s,n,Mysql(connect conexion localhost root 123456 ivr)
exten => _s,n,NoOP(Resultado de conexion = ${conexion})
exten => _s,n,WaitExten(2,)
exten => _s,n,Mysql(query consulta ${conexion} SELECT * FROM consultasaldo WHERE cedula=${ceduladigitada} and pin=${PIN} and tarjeta = ${numerodetarjeta})
exten => _s,n,NoOP(Resultado de consulta = ${consulta})
exten => _s,n,NoOP(Asignando resultados a variables variable1 y variable2 )
exten => _s,n,Mysql(fetch asignacion ${consulta} variable1 variable2 variable3 variable4 variable5 variable6)
exten => _s,n,NoOP(Resultado de asignacion = ${asignacion})
exten => _s,n,NoOP(variable1 = ${variable1})
exten => _s,n,NoOP(variable2 = ${variable2})
exten => _s,n,NoOP(variable2 = ${variable3})
exten => _s,n,NoOP(variable2 = ${variable4})
exten => _s,n,NoOP(variable2 = ${variable5})
exten => _s,n,NoOP(Limpiando resultado de la consulta)
exten => _s,n,Mysql(clear ${consulta})
exten => _s,n,NoOP(Resultado de limpieza: consulta = ${consulta})
exten => _s,n,NoOP(Cerrando conexion)
exten => _s,n,Mysql(disconnect ${conexion})
exten => _s,n,GotoIf($["${variable1}" = ""]?nodataread,s,1:continuar)
exten => _s,n(continuar),agi(googletts.agi,"ID en base de datos es",es)
exten => _s,n,SayDigits(${variable1})
exten => _s,n,agi(googletts.agi,"Su nombre es ${variable2}",es,,1.26)
exten => _s,n,agi(googletts.agi,"su saldo es ${variable5}",es,,1.26)
exten => _s,n(final),Hangup()
[nodataread]
exten => _s,1,Answer
exten => _s,n(cedulanodigitada),agi(googletts.agi,"El valor no existe",es,,1.16)
exten => _s,n,Goto(consultadb,s,1)
Debemos hacer el include este mismo archivo
[from-internal-custom]
exten => 1234,1,Playback(demo-congrats) ; extensions can dial 1234
exten => 1234,2,Hangup()
exten => h,1,Hangup()
include => agentlogin
include => conferences
include => calendar-event
include => weather-wakeup
include => local2
include => consultadb
include => cedulainvalida
include => Numerotarjeta
include => pin
include => conexionmysql
include => nodataread
Guardamos el archivo y reiniciamos el dialplan
asterisk -rx "dialplan reload"
¡Y estamos listos (casi...)!
A falta de una voz glamurosa para las grabaciones, opte por usar el tts de google el cual obviamente se puede utilizar hasta que no pase lo contrario de forma gratuita, pueden seguir estos pasos para instalarlo;
http://zaf.github.io/asterisk-googletts/
¡Ahora si!
Solo nos queda probar el flujo de la llamada
Marcamos
*10
Escuchamos y seguimos las opciones, para entender el el dialplan pueden ir leyendo literalmente cada paso, también pueden ir viendo la consola del Asterisk para "debuguear"
Los valores ingresados deben coincidir con los de la base de datos, de lo contrario vuelven a la opción anterior.
El resultado debe de ser:
Con un audio (googletts.agi), id en base de datos.
Con un audio (googletts.agi), Nombre en la base de datos.
Con un audio (googletts.agi), Saldo en la base de datos.
En esta esta entrada utlizamos varias aplicaciones, con esto nos damos cuenta de lo potente que puede ser este recurso para una empresa.
Si les intereso el articulo, recomiendo aprender /leer mas sobre:
Aplicacion Goto
Aplicacion GotoIF
Aplicacion Mysql con mas de una opcion
Aplicacion AGI
Aplicacion NoOp
Espero les guste la entrada y los reto a tratar de simplificar este IVR.
Como siempre quedo atento a sus comentarios
gabofuentesm@gmail.com
Saludos,
