Optimizing Asterisk Dialplan Listing with MySQL

Most companies now have IP-telephony built on the basis of Asterisk (*). Recently I ran into the following seemingly banal task: there are about 50 employees, each of them has an internal (additional) number and a corporate worker. It is necessary that the employee be constantly in touch, since not everyone is and is not always in the office, and if the client does not get through, it will be a disaster. The first thing that comes to mind is a dialplan of the following form:

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 described only one employee, and there are 50 of them! And you also need to record every conversation, for example. Listing is already expanding to indecent proportions. And not only the listing, but also the number of errors that can be made in the process of compiling a dialplan. The first thing that occurred to me was the good old MySQL. What can we implement here? It's simple, we hammer the base of employees with their extension and mobile numbers and, substituting them into variables, we call.

I lifted an asterisk from the AsteriskNOW distribution, which with already raised muscle and everything else. It would be nice to create a base and a plate for our subscribers. My base is called aster_num and the table in it is numbers .

describe numbers;
+ --------------- + ---------------- + ------ + ----- + --- ------ + ------------------------ +
| Field | Type | Null | Key | Default | Extra |
+ --------------- + ----------------- + ------ + ----- + - ------- + ----------------------- +
| id | int (6) | NO | PRI | NULL | auto_increment |
| asterisk | varchar (20) | NO | | NULL | |
| mobile | varchar (20) | NO | | NULL | |
| first_name | varchar (20) | NO | | NULL | |
| last_name | varchar (20) | NO | | NULL | |
| location | varchar (20) | NO | | NULL | |
+ --------------- + ----------------- + ------- + ----- + - -------- + --------------------- +

A little about the columns of our table:

id -
asterisk employee unique identifier
- mobile extension numbers -
first_name mobile number -
last_name employee name - employee last name
location - office branch address

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

We will describe our connection in it:
[aster_num]
driver = MySQL
server = localhost
user = xxx
password = xxx
database = aster_num
Port = 3306

Set up connection * to our database.

/etc/asterisk/res_odbc.conf
[aster_num]
enabled => yes
dsn => aster_num
username => xxx
password => xxx
pooling => no
limit => 1
pre-connect => yes

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

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

total, everything works beautifully for us! Hurrah! There is not much left: fill in the table and write a dialplan. Suppose we have employee Ivan Ivanov, with extension 3333 and mobile 380631234567. And employee Petr Petrov, with extension 3444 and mobile 380979876543. We will add them to our numders table .

numders
+ ---- + ---------- + ------------------- + ------------- - + ---------------- + -------------- +
| id | asterisk | mobile | first_name | last_name | location |
+ ---- + ---------- + ------------------- + ------------- - + ---------------- + -------------- +
| 1 | 3333 | 380631234567 | Ivan | Ivanov | Kiev |
| 2 | 3444 | 380979876543 | Petr | Petrov | Kiev |


Sketch the dialplan:

extensions.conf
exten => _3XXX, 1, Dial (SIP / $ {EXTEN}, 20, tT)
exten => _3XXX, 2, MySQL (Connect connid localhost xxx xxx aster_num)
exten => _3XXX, 3, MySQL (Query resultid $ {connid } SELECT mobile FROM numbers WHERE asterisk = $ {EXTEN})
exten => _3XXX, 4, MySQL (Fetch fetchid $ {resultid} mob_num)
exten => _3XXX, 5, Dial (SIP / trunk / $ {mob_num}, 60, tT)
exten => _3XXX, 6, MYSQL (Clear $ {resultid})
exten => _3XXX, 6, MYSQL (Disconnect $ {connid})

I’ll explain a little about the points, so:
exten => _3XXX, 1, Dial (SIP / $ {EXTEN}, 20, tT) - we call the extension, which is recorded in the variable $ {EXTEN}, for 20 seconds, with the possibility of transferring a 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 employee’s mobile number
exten => _3XXX, 4, MySQL (Fetch fetchid $ {resultid} mob_num) - substitute the mobile number in the variable
exten => _3XXX, 5, Dial (SIP / trunk / $ {mob_num}, 60 , tT) - and call him!
exten => _3XXX, 6, MYSQL (Clear $ {resultid})- clear the variable
exten => _3XXX, 7, MYSQL (Disconnect $ {connid}) - close the connection to the database.

Thus, only seven lines can describe calls with call forwarding and translation for any number of employees.

Agree that it will not be very convenient for adding a new employee to the database to open the muscle and engage in boring insert'ami. To do this, I wrote a simple admin panel that perfectly copes with this task. A detailed description of it will be in the next article. In the meantime, here is her screenshot:

image

I hope this publication helps someone. If you have any suggestions, questions or problems - write, do not be shy.

Also popular now: