Creating a dialplan with MySQL (imbedded) Database

I already know how to create databases/tables in MySQL. Adding data en updating them is also no problem.

What i need to do is:
Create a dial plan within asterisk for a bank (not commercial). The caller needs to be able to listen to his current balance, after entering a userid and usercode (this way he/she accesses there respective accounts). They also need to be able to do transactions (send money. to make this simple, they will only be able to do this with other account holders within their bank). Where as the balance of both the sender and receiver must be automatically updated. The caller also needs to be able to request to hear his last 3 transactions.

these are the tables that i’ve made:
customer_info => id, first_name, last_name, birthday, (telephone_number)
account_info => id, usercode, account_number, balance
transactions => id, account_sender, account_receiver, ammount, (date)

customer_info.id=account_info.id

both account_sender and account_receiver will correspond with account_info:
transacties.account_sender=account_info.account_number (nieuw balance=balance-ammount)
transacties.account_receiver=account_info.account_number (nieuw balance=balance+ammount)

transaction.date : i want to the date to be added automatically when the caller does a transaction.

the table transaction will hold every transaction of every account holder. we can than make a temporary table to hold the (last 3) transaction of a specific account holder.

I (might) need help with the queries, but mostly i need help with writing the dial plan in asterisk.

ps. use of func_odbc is not allowed.

i saw an example of a dial plan:
part of the dial plan:

exten => 10000,n,MYSQL(Connect connid 192.168.10.13 invite invite feng)
exten => 10000,n,MYSQL(Query resultid ${connid} SELECT \firstname\, \lastname\ FROM og_contacts WHERE \w_phone_number\='${CALLERID(name)}' OR \w_phone_number2\='${CALLERID(name)}' LIMIT 1)
exten => 10000,n,MYSQL(Fetch fetchid ${resultid} name lastname)

but i don’t understand it fully

This is app_mysql and is deprecated but works with freepbx.

You are following an old tutorial. The use of the \ escape characters in queries was necessary in Asterisk versions long past, but will no longer work with current versions.

@lgaetz , @jersonjunior

could you give me an example of a dial plan where the caller can at least listen to his balance?

i think i’ll be able to take it from there. where necessary an explanation of the dial plan please

Things like this should be managed through AGI

1 Like

@jfinstrom

could you give an example and explanation?

https://www.google.com/search?q=asterisk+AGi+database&oq=asterisk+AGi+database&aqs=chrome..69i57j0l3.14014j0j4&client=ms-android-sprint-us&sourceid=chrome-mobile&espv=1&ie=UTF-8

thanks for the link.

but i was kinda hoping for a dial plan example.

http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/asterisk-DP-Basics.html

this is what i have so far:

[Bank_1]
exten => 2020,1,answer
exten => 2020,n,MYSQL(Connect connid ''ipadress'' ''username'' ''password'' ''database name'')
exten => 2020,n,Read(id, user_id,5,,,)
exten => 2020,n,Read(code, user_code,5,,,)
exten => 2020,n,MYSQL(Query resultid ${connid} SELECT * FROM 'account_info' WHERE 'id=${id}' and 'usercode=${code}' LIMIT 1)
exten => 2020,n,MYSQL(Fetch foundRow ${resultid} id, usercode, balance)
exten => 2020,n,Playback(chosen_account)
exten => 2020,n,SayAlpha(klanten_info.last_name)
exten => 2020,n,Playback(your_balance)
exten => 2020,n,SayNumber(account_info.balance)

i don’t know if i defined SayAlpha and SayNumber correctly. but obviously they need to “Say” contents from the database.

by the way: what does ‘foundRow’ do exactly?

Did you look at the AGI example James posted. You’re in for a world of pain if you try to keep using mysql on the dialplan, it’s just so limited.

i did look at the link that James send me, but i couldn’t see how the data in MySQL would get incorporated.

could you give me a written dial plan example with the use of AGI? the caller needs to be able to hear back his/her current balance. (that would be the balance that you input in the database)

You are in a boat with many fisherman. We are a friendly bunch who are willing to teach you to fish. If you Dont want to learn how to fish then it is best to hire a fisherman.

We are happy to give you direction and answer questions but no one is likely to write the code for you.

If you would like to have something developed, put together a full scope of work and people will be happy to quote out the cost. If you Dont want to pay someone then you need to get your hands dirty and (ab)use google.

The link I sent for asteriskdocs is basically the asterisk bible. It will show you through most tasks.

1 Like

I’ll bite and give you valid examples (in PHP):

Dialplan

[Blank_1]
exten => 2020,1,answer
exten => s,n,AGI(script.php)
exten => s,n,Hangup

Script (/var/lib/asterisk/agi-bin/script.php) [owned by Asterisk]

#!/usr/bin/php -q
<?php
// Bootstrap FreePBX but don't include any modules (so you won't get anything
// from the functions.inc.php files of all the modules.)
$restrict_mods = true;
if (!@include_once(getenv('FREEPBX_CONF') ? getenv('FREEPBX_CONF') : '/etc/freepbx.conf')) {
        include_once('/etc/asterisk/freepbx.conf');
}

// Connect to AGI:
//
require_once "phpagi.php";
$agi = new AGI();
$dbh = new PDO("mysql:dbname=databasename;host=ipaddress", "username", "password");
$id = $agi->get_data('user_id', null, 5);

I did the initial work for you. As I stated. This is much easier in a scripting language rather than asterisk, the examples above are PHP but it can be accomplished in python as well.

You can see all supported PHP AGI commands here: /var/lib/asterisk/agi-bin/phpagi.php

3 Likes

@jfinstrom

I understand your standpoint completely. At the time of my question i was really desperate for an answer. You referred a manual to me which i already had. But i am glad for your replies. I obviously have much to learn still and will brush up on my reading.

@tm1000

this being said: i am especially thankfull to andrew, who took the time to write a beginning of the dialplan that uses AGI. although i have to admit that i don’t really understand it fully yet. i will however study AGI extensively.

anyway…before i saw your last comments…i had already decided to stop whining and role up my sleeves.

i finally was able to write a working script. the caller can hear his/her balance after entering userid and usercode. after that the call hangs up. this will be my basis.

[Bank_1]
exten => 2020,1,Answer
exten => 2020,n,Playback(welcome)
exten => 2020,n,MYSQL(Connect connid localhost user password DBname)

exten => 2020,n,Read(userid,5)
exten => 2020,n,Playback(your_id)
exten => 2020,n,SayDigits(${userid})

exten => 2020,n,Read(code,5)
exten => 2020,n,Playback(your_code)
exten => 2020,n,SayDigits(${code})

exten => 2020,n,MYSQL(Query resultid ${connid} SELECT customer_info.id, customer_info.lastnaam, account_info.balance, account_info.usercode FROM customer_info, account_info WHERE customer_info.id=account_info.id AND (customer_info.id=${userid} AND account_info.usercode=${code}))
exten => 2020,n,MYSQL(Fetch fetchid ${resultid} id lastnaam balance usercode)

exten => 2020,n,Playback(chosen_account)
exten => 2020,n,SayAlpha(${lastnaam})

exten => 2020,n,Playback(your_balance)
exten => 2020,n,SayNumber(${balance})

exten => 2020,n,MYSQL(Clear ${resultid})
exten => 2020,n,MYSQL(Disconnect ${connid})
exten => 2020,n,Playback(vm-goodbye)
exten => 2020,n,hangup

exten => 2021,1,Playback(no_user)
exten => 2021,n,Goto(2020,4)

i also want to add the option that the call return to the beginning, when the caller enters an id and usercode that are not compatible (and so give no results in the query). furthermore i want to incorporate a text to speech engine, so i don’t have to use SayAlpha(only spells out the name instead of reading it full).

yes, it is a pain to work with MYSQL in the dialplan (with more complicated queries i know that i’ll regret working this way), but i understand the concept. i should be able to achieve the goals.

When i’ve programmed my dialplan fully, i’ll also post it here. After that i also will try this same dialplan with AGI and post it

@rod_nick Please note the reasons we point you to AGI.

  1. The MySQL function you are using has been deprecated, this means your dialplan above will break with a future Asterisk Update.
  2. MySQL in the dial plan is just ugly
  3. Every time you use MySQL in a dial plan a kitten dies…

Ok maybe not 3. But if you want this to be a lasting solution you should use AGI. Doing it your way will end up biting you in the butt later. Murphy’s law says that time will come at the absolute worst moment.

1 Like

Also of note. The example I provided hooks directly into FreePBX so you can query anything FreePBX knows.

@jfinstrom
@tm1000

okay. thanks for the comments

Leveraging the subject I wonder if with the VQ module plus is possible to forward a context with agi using the Post Hangup Destinations?

And if it is possible to use the argument c queue without applying the VQ plus?