Query a database from Asterisk Part 2

This simple example resembles a feature, which might be used by credit card companies, where a user may inquire about recent payments.


Here are the configurations:
------------------------------------------------

;Create the database "odbc_finance_bank"
;Create table "finance_bank_members" with the information below

+------------------+----------+---------------------+--------------------+
| account_number | pin_code | recent_payment_date | recent_paid_amount |
+------------------+----------+---------------------+--------------------+
| 5111300001 | 1234 | 2010-05-16 19:23:00 | 5003.12 |
| 5222300002 | 5678 | 2010-06-22 10:09:00 | 9800.00 |
+------------------+----------+---------------------+--------------------+


;Edit configuration files
------------------------------------------------
;cat /etc/odbcinst.ini

[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc3.so
Setup = /usr/lib/libodbcmyS.so
FileUsage = 1

------------------------------------------------
;cat /etc/odbc.ini

[astodbc]
Description = Retrieve names and extensions
Driver = MySQL
Server = localhost
Port = 3306
USER = dbuser
Password = dbpassword
Database = odbc_finance_bank
Option = 3

------------------------------------------------
;cat /etc/asterisk/res_odbc.conf

[getuserinfo]
enabled => yes
dsn => astodbc
username => dbuser
password => dbpassword
pre-connect => yes

------------------------------------------------
;cat /etc/asterisk/func_odbc.conf

[GETDATE]
dsn=getuserinfo
read=SELECT recent_payment_date FROM finance_bank_members WHERE account_number='${SQL_ESC(${ARG1})}' and pin_code='${SQL_ESC(${ARG2})}';

[GETAMOUNT]
dsn=getuserinfo
read=SELECT recent_paid_amount FROM finance_bank_members WHERE account_number='${SQL_ESC(${ARG1})}' and pin_code='${SQL_ESC(${ARG2})}';

------------------------------------------------
;cat /etc/asterisk/extensions_custom.conf

[from-internal-custom]
; Account Transactions

; answer incoming
exten => 1000,1,answer
exten => 1000,n,wait(1)

; enter account number
exten => 1000,n,Playback(after-the-tone)
exten => 1000,n,Playback(please-enter-your)
exten => 1000,n,Playback(digits/10)
exten => 1000,n,Playback(astcc-digit-account-number)
exten => 1000,n,Playback(beep)
exten => 1000,n(getacctnum),read(account_number,,10,,3,15)

;confirm account number
exten => 1000,n,Playback(you-entered)
exten => 1000,n,Playback(silence/1)
exten => 1000,n,SayDigits(${account_number})
exten => 1000,n,Playback(silence/1)
exten => 1000,n,Playback(if-this-is-correct)
exten => 1000,n,Playback(press-1)
exten => 1000,n(acctverify),read(account_verify,,1,,3,5)
exten => 1000,n,GotoIf($[${account_verify} = 1]?getpincode:goodbye)

; enter pin code
exten => 1000,n(getpincode),Playback(please-enter-your)
exten => 1000,n,read(pin_code,access-code,4,,3,10)

; hangup if no data
exten => 1000,n,Set(recentdate=${ODBC_GETDATE(${account_number},${pin_code})})
exten => 1000,n,GotoIf($["${recentdate}" = ""]?tryagain:)

; if data present, format date
; recentdate format: 1976-12-16 07:30:35
exten => 1000,n,Set(formatdate=${STRPTIME(${recentdate}|Asia/Manila|%Y-%m-%d %H:%M:%S})

exten => 1000,n,Playback(received)
exten => 1000,n,SayUnixTime(${formatdate})
exten => 1000,n,Set(recentamount=${ODBC_GETAMOUNT(${account_number},${pin_code})})
exten => 1000,n,SayNumber(${recentamount})
exten => 1000,n,Playback(digits/dollars)
exten => 1000,n,Set(centavo=${CUT(recentamount,.,2)})
exten => 1000,n,Playback(and)
exten => 1000,n,SayNumber(${centavo})
exten => 1000,n,Playback(cents)

exten => 1000,n,Playback(silence/3)
exten => 1000,n(goodbye),Playback(goodbye)
exten => 1000,n,hangup()

exten => 1000,n,Playback(silence/3)
exten => 1000,n(tryagain),Playback(login-fail)
exten => 1000,n,Playback(please-try-again)
exten => 1000,n,Playback(goodbye)
exten => 1000,n,hangup()