Optimizing Asterisk Dialplan Listing with MySQL

Most companies now have IP-telephony, built on the basis of Asterisk (*). Recently I encountered the following, at first glance, banal task: there are about 50 employees, each of them has an internal (extension) number and a corporate worker. It is necessary that the employee was constantly in touch, because not everyone is in the office and they are not always there, and if the client does not get through, there will be trouble. The first thing that comes to mind is a dialplan of the following type:

exten => 3333.1, Dial (SIP / $ {EXTEN}, 20, tT); call additional
exten => 3333.2, Dial (SIP / trunk / 3809631234567,60, tT); if not the answer - we type on the mobile.

It seems everything is simple and beautiful. BUT! We have described only one employee, and there are 50 of them! And you also need to record each conversation, for example. The listing is already growing to indecent scales. And not only the listing, but also the number of errors that can be made in the process of drawing up a dialplan. The first thing that occurred to me was the good old MySQL. What can we realize here? Everything is simple, we hammer in base of employees with their extension and mobile numbers and, substituting them in variables, we call.

I picked up the asterisk from the AsteriskNOW distribution, which with already raised muscle and all the rest. It would be nice to create a database and a sign for our subscribers. My base is called aster_num and the table in it is numbers .
')

A little bit about the columns of our table:

id - employee's unique identifier
asterisk - extension numbers
mobile - mobile numbers
first_name - employee name
last_name - surname of employee
location - the address of the branch office

* connects to MySQL via an ODBC connector, the parameters of which are described in the /etc/odbcinst.ini file.


Set up the connection * to our database.


After the reboot, we check our connection. The * odbc show all command should show us something like the following:

Name: aster_num
DSN: aster_num
Last connection attempt: 1970-01-01 03:00:00
Pooled: No
Connected: Yes

So, everything works beautifully with us! Hooray! It remains not much at all: to fill in the table and write a dialplan. Suppose that we have an employee Ivan Ivanov, with an additional 3333 and a mobile 380631234567. And an employee Petr Petrov, with an additional 3444 and a mobile 380979876543. We enter them into our table numders .


Sketch the dialplan:


I will explain a little by the points, so:
exten => _3XXX, 1, Dial (SIP / $ {EXTEN}, 20, tT) - we call the extension, which is recorded in the $ {EXTEN} variable, within 20 seconds, with the possibility of transferring the call.
exten => _3XXX, 2, MySQL (Connect connid localhost xxx xxx aster_num) - initialize the connection to the employee database
exten => _3XXX, 3, MySQL (Query resultid $ {connid} SELECT mobile FROM numbers WHERE asterisk = $ {EXTEN}) - elementary query, to search for the employee's mobile number
exten => _3XXX, 4, MySQL (Fetch fetchid $ {resultid} mob_num) - substitute the mobile number into a variable
exten => _3XXX, 5, Dial (SIP / trunk / $ {mob_num}, 60, tT) - and call it!
exten => _3XXX, 6, MYSQL (Clear $ {resultid}) - clears the variable
exten => _3XXX, 7, MYSQL (Disconnect $ {connid}) - close the connection to the database.

Thus, only seven lines can describe calls with redirection and number transfer for any number of employees.

Agree that it will not be too convenient for adding a new employee to the database to open muscle and engage in boring inserts. For this, I wrote a simple admin panel that does an excellent job with this task. A detailed description of it will be in the next article. In the meantime, here is her screenshot:



I hope this publication will help someone. If you have any suggestions, questions or problems - write, do not hesitate.