Кто звонил? Или обратный звонок с сотового на asterisk

Часто попадали в ситуацию когда на мобильном обнаруживаешь пропущенный звонок с городского и при попытке перезвонить попадаешь на голосовое меню какой-нибудь фирмы и совершенно непонятно кто тебе звонил? Я часто, а также и клиенты нашей компании каждый день сталкиваются с этим… С этим решено было что-то делать. Все последующие действия актуальны для trixbox v2.8.0.4 (с некоторыми корректировками или, возможно, даже без них можно реализовать и на других системах)

Вся информация по совершенным звонкам хранится в CDR Report и соответственно базе mysql.
Алгоритм следующий:
Берем номер входящего звонка, делаем запрос к таблице cdr и получаем внутренний extension, который совершал последний вызов на данный номер, проговариваем номер extension'a, соединяем абонентов.

Зайдя в mysql на хосте asterisk мы сразу видим базу «asteriskcdrdb» с одной единственной табличкой «cdr» в которой хранятся записи такого вида


Из всех столбцов нам понадобятся:

• calldate — время звонка
• dst — набранный номер
• channel — внутренний номер с которого был совершен звонок

Поэкспериментировав с запросами получаем следующий:
SELECT `channel` FROM cdr WHERE `dst`='${CALLERID(number)}' ORDER BY `calldate` DESC LIMIT 1
Получаем поле channel из таблицы cdr где набранный номер совпадает с текущим callerid, сортируем по убыванию и получаем только последнюю запись, т.е. последний номер с которого был звонок.

Воспользуемся custom файлами asterisk'a. Добавляем в конец файла extensions_custom.conf следующий код. В данном примере используется extension 456.

[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)

1. Отвечаем на звонок
2. Подключаемся к БД пользователем root, паролем pass, к базе asteriskcdrdb
3. Выполняем запрос
4. Получаем результат пишем в var
5. Освобождаем память от запроса
6. Отключаемся от БД
7. В поле channel содержится не только номер канала, ненужное убирается sed'ом (Пример: SIP/160-0000000. Регулярным выражением убираем "/" и все что до него, а также "-" и все что после него), а также лишний перевод строки tr'ом
8. Индекс для цикла
9. Получаем длину внутреннего номера который нужно произнести
10. Пока индекс меньше длины номера
11. Произносим текущую цифру номера
12. Увеличиваем индекс на один
13. Заканчиваем цикл
14. Соединяем абонентов

По коду вроде все, теперь необходимо чтобы можно было использовать полноценно эту функцию в веб интерфейсе asterisk'a. Заходим в веб интерфейс trixbox.
PBX — PBX Settings — Tools — Custom Destinations и кликаем на Add Custom Destination. Заполняем два поля:
Custom Destination: custom-from-mobile,456,1 #где custom-from-mobile заголовок добавленного кода в custom_extensions.conf, 456 — номер extension'a, 1 — приоритет
Descritpion: по желанию к примеру 456.

Не забываем нажать на Submit Changes.
Правим голосовое меню IVR и вешаем к примеру на цифру «5» пункт Custom Destinations: с только что созданным 456.

Submit Changes. Apply Configuration Changes и Continue with reload. Дожидаемся применения изменений.

Теперь набрав номер нашей компании, у клиента есть всегда возможность нажав 5 в голосовом меню соединиться с последним позвонившим ему номером.

UPD1 ODBC. Бегло поковыряв файлики asterisk'a не увидел где конкретно cdr коннектится к mysql через odbc, чтобы использовать уже существующее соединение к базе. Как будет время поищу еще и добавлю обновления. На текущий момент дописал новый коннект к базе и использую его следующим образом.

Доустанавливаем необходимые пакеты
yum install mysql-connector-odbc Опять же может кто объяснит каким образом работал cdr без этого пакета (использует ли он odbc?), в системе его не было.

Далее правим файлик /etc/odbcinst.ini добавляя в конец файла

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

*Внимательно надо проследить пути и названия файлов, у всех могут быть разные в зависимости от версии odbc установленной в системе.

Аналогично правим /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

* Здесь заполняем в соответствие со своими настройками. В первой строке заполняем так называемый dsn произвольно и запоминаем.

Проверяем настройки. В терминале выполняем команду:

isql -v asterisk-connector

Если все правильно настроили в ответ должны получить что-то подобное:

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

Выходим командой «quit»

Добавляем в конец файла /etc/asterisk/res_odbc.conf

[asterisk2mysql]
enabled => yes
dsn => asterisk-connector ; взят из /etc/odbc.ini
pre-connect => yes

Добавляем в конец файла /etc/asterisk/func_odbc.conf

[FROMMOBILE]
dsn=asterisk2mysql ; взят из /etc/asterisk/res_odbc.conf    
readsql=SELECT channel FROM cdr WHERE dst=${ARG1} ORDER BY calldate DESC LIMIT 1 ; параметр ARG1 передается при вызове из диалплана

Ну и обновленный диалплан

[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)})} — строка запроса
FROMMOBILE берем из файла /etc/asterisk/func_odbc.conf
в скобочках через запятую можно передавать необходимые параметры для запроса, в самом запросе в файле /etc/asterisk/func_odbc.conf их получаем в переменных ARG1, ARG2 и т.д. в нашем примере передается только один параметр.
Плюсы: уменьшился код диалплана, заметен прирост в скорости работы.

UPD2. Идея из комментариев и состоит в следующем. Представим что один человек разговаривает с несколькими людьми в компании. И возникает ситуация когда есть один пропущенный и в этот момент ему звонит другой человек с которым он успешно ведет беседу. Закончив разговор и воспользовавшись нашей текущей функцией абонент попадет на последнего звонившего с которым он уже поговорил и не узнает кто ему звонил до этого. Чтобы исключить такие ситуации изменим немного запрос добавив дополнительные проверки.

При работе с MySQL из диалплана код будет следующим:

[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)

При работе с ODBC правим запрос в /etc/asterisk/func_odbc.conf

[FROMMOBILE]
dsn=asterisk2mysql ; взят из /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; 

Диалплан остается прежним как и в UPD1.

UPD3 Личное наблюдение Возникла очень хорошая ситуация. На одном asteiske несколько sip транков и соответственно несколько фирм. У каждой фирмы свой callerid. Если представить ситуацию что на один мобильный номер позвонили с трех фирм, то независимо на какой из городских номеров перезвонить попадем мы только на последнего звонящего и не факт что из той фирмы. Решение следующее. Добавить в выборку из базы дополнительный параметр проверки — с какого callerid звонили этому абоненту.
В запрос в обоих методах нужно добавить `src`='${FROM_DID}':

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