Who called? Or a callback from a cell to asterisk

Often got into the situation when you find a missed call from a city on a mobile phone and when you try to call back, you get to the voice menu of some company and it is completely incomprehensible who called you? I often, as well as our clients, face this every day ... It was decided to do something about it. All subsequent actions are relevant for trixbox v2.8.0.4 (with some adjustments, or perhaps even without them, it can be implemented on other systems)

All information on calls made is stored in the CDR Report and, accordingly, the base of mysql.
The algorithm is as follows:
We take the number of the incoming call, make a request to the cdr table and get the internal extension, which made the last call to this number, we pronounce the extension number, we connect the subscribers.

Having logged in to mysql on the asterisk host, we immediately see the “asteriskcdrdb” database with a single “cdr” table in which records of this type are stored

')
Of all the columns we need:

calldate - call time
dst - dialed number
channel - internal number from which the call was made

Experimenting with the requests we get the following:
SELECT `channel` FROM cdr WHERE `dst`='${CALLERID(number)}' ORDER BY `calldate` DESC LIMIT 1
We get the channel field from the cdr table where the dialed number matches the current callerid, we sort in descending order and we get only the last record, i.e. the last number from which there was a call.

Use custom asterisk files. We add the following code to the end of the file extensions_custom.conf. In this example, extension 456 is used.

  [custom-from-mobile]
 exten => 456,1, Answer ()
 exten => 456, n, MYSQL (Connect connid localhost root pass asteriskcdrdb)
 exten => 456, n, MYSQL (Query resultid $ {connid} SELECT `channel` FROM cdr WHERE` dst` = '$ {CALLERID (number)}' ORDER BY `calldate` DESC LIMIT 1)
 exten => 456, n, MYSQL (Fetch fetchid $ {resultid} VAR)
 exten => 456, n, MYSQL (Clear $ {resultid})
 exten => 456, n, MYSQL (Disconnect $ {connid})
 exten => 456, n, Set (CHAN = $ {SHELL (echo $ {var} | tr -d '\ n' | sed -e 's /.*\/\(.*\)\--.*/ \ 1 / g ')})
 exten => 456, n, Set (i = 0)
 exten => 456, n, Set (COUNTER = $ {LEN ($ {CHAN})})
 exten => 456, n, While ($ [$ {i} <$ {COUNTER}])
 exten => 456, n, Playback (digitsru / $ {CHAN: $ {i}: 1})
 exten => 456, n, Set (i = $ [$ {i} + 1])
 exten => 456, n, EndWhile ()
 exten => 456, n, Goto (from-internal, $ {CHAN}, 1)

Answer the call
Connect to the database as root, with a password pass, to the asteriskcdrdb database
Execute the request
We receive result we write in var
Freeing memory from the request
Disconnect from the database
The channel field contains not only the channel number, the unnecessary is removed by sed (example: SIP / 160-0000000. With a regular expression we remove "/" and everything before it, as well as "-" and everything after it), as well as an extra line feed tr'om
Cycle Index
We get the length of the internal number to be pronounced
While the index is less than the length of the number
We pronounce the current digit of the number
Increase the index by one.
We finish the cycle
We connect subscribers

Code like everything, now it is necessary that you can fully use this function in the asterisk web interface. Go to the trixbox web interface.
PBX - PBX Settings - Tools - Custom Destinations and click on Add Custom Destination. Fill in two fields:
Custom Destination: custom-from-mobile, 456.1 # where custom-from-mobile is the header of the added code in custom_extensions.conf, 456 is the extension number, 1 is the priority
Descritpion: optional for example 456.

Do not forget to click on Submit Changes.
Rule the voice menu IVR and hang for example on the number "5" item Custom Destinations: with the newly created 456.

Submit Changes. Apply Configuration Changes and Continue with reload. We are waiting for the changes to apply.

Now dialing the number of our company, the client always has the opportunity by pressing 5 in the voice menu to connect with the last number he called.

UPD1 ODBC. Skimming the asterisk files, I didn’t see where exactly cdr connects to mysql via odbc to use an existing connection to the database. As time will look for more and add updates. At the moment, I added a new connection to the database and use it as follows.

We are installing the necessary packages.
yum install mysql-connector-odbc Again, can anyone explain how cdr worked without this package (does it use odbc?), it was not in the system.

Next, edit the file /etc/odbcinst.ini by adding to the end of the file

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

* Carefully follow the paths and file names, all may be different depending on the version of odbc installed in the system.

Similarly, the rules /etc/odbc.ini

 [asterisk-connector]
 Description = MySQL connection to 'asterisk' database
 Driver = MySQL
 Server = localhost
 Database = asteriskcdrdb
 UID = root
 PWD = pass
 Port = 3306
 SOCKET = / var / lib / mysql / mysql.sock

* Fill in here in accordance with their settings. In the first line we fill in the so-called dsn arbitrarily and memorize it.

Check the settings. In the terminal, execute the command:

  isql -v asterisk-connector

If everything is configured correctly in response should get something like this:

 + --------------------------------------- +
 |  Connected!  |
 |  |
 |  sql-statement |
 |  help [tablename] |
 |  quit |
 |  |
 + --------------------------------------- +

We leave with the command "quit"

Add to the end of the /etc/asterisk/res_odbc.conf file

 [asterisk2mysql]
 enabled => yes
 dsn => asterisk-connector;  taken from /etc/odbc.ini
 pre-connect => yes

Add to the end of the /etc/asterisk/func_odbc.conf file

 [FROMMOBILE]
 dsn = asterisk2mysql;  taken from /etc/asterisk/res_odbc.conf    
 readsql = SELECT channel FROM cdr WHERE dst = $ {ARG1} ORDER BY calldate DESC LIMIT 1;  ARG1 parameter is passed when calling from dialplan

Well, the updated dialplan

 [custom-from-mobile]
 exten => 456,1, Answer ()
 exten => 456, n, Set (CHAN = $ {SHELL (echo $ {ODBC_FROMMOBILE ($ {CALLERID (number)})} | tr-d "\ n" | sed-e 's /.* \ / \ ( . * \) \ -. * / \ 1 / g ')})
 exten => 456, n, Set (i = 0)
 exten => 456, n, Set (COUNTER = $ {LEN ($ {CHAN})})
 exten => 456, n, While ($ [$ {i} <$ {COUNTER}])
 exten => 456, n, Playback (digitsru / $ {CHAN: $ {i}: 1})
 exten => 456, n, Set (i = $ [$ {i} + 1])
 exten => 456, n, EndWhile ()
 exten => 456, n, Goto (from-internal, $ {CHAN}, 1)
 exten => 456, n, Hangup ()

$ {ODBC_FROMMOBILE ($ {CALLERID (number)})} - query string
FROMMOBILE taken from the file /etc/asterisk/func_odbc.conf
in parenthesis, you can pass the necessary parameters for the request, in the request itself in the /etc/asterisk/func_odbc.conf file we get them in the variables ARG1, ARG2, etc. in our example, only one parameter is passed.
Pros: the dialplan code has decreased, an increase in work speed is noticeable.

UPD2. The idea of the comments and is as follows. Imagine that one person is talking to several people in a company. And a situation arises when there is one missed and at this moment he is called by another person with whom he successfully leads a conversation. Having finished the conversation and using our current function, the subscriber will fall on the last caller with whom he has already spoken and will not know who called to him before. To exclude such situations, we will change the query a bit by adding additional checks.

When working with MySQL from dialplan, the code will be as follows:

 [custom-from-mobile]
 exten => 456,1, Answer ()
 exten => 456, n, MYSQL (Connect connid localhost root pass asteriskcdrdb)
 exten => 456, n, MYSQL (Query resultid $ {connid} SELECT `channel` FROM` cdr` WHERE `dst` = '$ {CALLERID (number)}' AND` disposition` = "NO ANSWER" OR `dst` = '$ {CALLERID (number)}' AND `disposition` =" ANSWERED "AND` billsec` <"4 ORDER BY` calldate` DESC LIMIT 1)
 exten => 456, n, MYSQL (Fetch fetchid $ {resultid} VAR)
 exten => 456, n, MYSQL (Clear $ {resultid})
 exten => 456, n, MYSQL (Disconnect $ {connid})
 exten => 456, n, Set (CHAN = $ {SHELL (echo $ {var} | tr -d '\ n' | sed -e 's /.*\/\(.*\)\--.*/ \ 1 / g ')})
 exten => 456, n, Set (i = 0)
 exten => 456, n, Set (COUNTER = $ {LEN ($ {CHAN})})
 exten => 456, n, While ($ [$ {i} <$ {COUNTER}])
 exten => 456, n, Playback (digitsru / $ {CHAN: $ {i}: 1})
 exten => 456, n, Set (i = $ [$ {i} + 1])
 exten => 456, n, EndWhile ()
 exten => 456, n, Goto (from-internal, $ {CHAN}, 1)

When working with ODBC, edit the request in /etc/asterisk/func_odbc.conf

 [FROMMOBILE]
 dsn = asterisk2mysql;  taken from /etc/asterisk/res_odbc.conf    
 readsql = SELECT channel FROM cdr WHERE dst = $ {ARG1} AND disposition = NO ANSWER OR dst = $ {ARG1} AND disposition = ANSWERED AND billsec <4 ORDER BY calldate DESC LIMIT 1;

Dialplan remains the same as in UPD1.

UPD3 Personal observation A very good situation has arisen. On one asteiske there are several sip trunks and, accordingly, several firms. Each company has its own callerid. If we imagine a situation that one mobile number was called from three companies, then regardless of which city number to call back, we only get on the last caller and not the fact that from that company. The solution is as follows. Add to the sample from the database an additional check parameter - from which callerid this subscriber was called.
In the query in both methods, add `src` = '$ {FROM_DID}':

  SELECT `channel` FROM cdr WHERE` dst` = '$ {CALLERID (number)}' AND `src` = '$ {FROM_DID}' ORDER BY` calldate` DESC LIMIT 1

Source: https://habr.com/ru/post/213669/

All Articles

Who called? Or a callback from a cell to asterisk

More articles: