TBE: коды и названия стран и городов в CDR
Многие при изучении отчетов о звонках хотели бы видеть в поле назначения звонка не безмолвные цифры иногороднего телефонного номера, а название города, чтобы на вскидку прикинуть стоимость звонка и выявить самые популярные направления звонков в компании (например, чтобы выбить с провайдера льготы). В штатной системе анализа CDR дистрибутива Trixbox такой функции нет, однако ни что не мешает ее добавить.
Существует как минимум два варианта подстановки названий городов: “на лету” и в процессе построения отчета. Поиск и добавление информации “на лету” дополнительно нагружает сервер как минимум одним запросом в процессе прохождения звонка, но в последствии, при построении отчета, не требуется производить запросы для каждой строки CDR и нагрузка на сервер в целом будет разнесена во времени. В процессе поиска названий стран и городов автоматически решается и задача выделения кода города в набранном номере, что можно использовать для маршрутизации звонков по направлениям.
Поиск и подстановка названий стран и городов
Для того, чтобы извлекать откуда-то данные, нужны сами эти данные. К счастью сообщество asterisk-support.ru в теме Коды стран и городов извлекло и выложило все необходимые таблицы прямо в формате mysql. Для подстановки названий стран и городов можно использовать самую большую из найденных таблиц – destinations. Ее необходимо скачать и загрузить в MYSQL в базу данных asteriskcdrdb (файл представляет собой SQL запрос, создающий и заполняющий таблицу).
Поиск и заполнение полей аccountcode и userfield осуществляется командой mysql непосредственно в диалплане, но можно использовать func_odbc совместно c res_odbc:
;простой макрос поиска названия города и набора номера ;ARG1 – параметры для команды Dial ;ARG2 – номер, по которому производить поиск в БД ;ARG3 – имя исходящего контекста/направления [macro-simple-2-dial] exten => s,1,macro(seeknumber,${ARG2},${ARG3}) exten => s,2,dial(${ARG1}) ;макрос поиска названия. Реквизиты доступа к БД стандартные для Trixbox ;ARG1 – номер, по которому производить поиск в БД ;ARG2 – имя исходящего контекста/направления [macro-seeknumber];следующую инструкцию нужно уместить в одну строку exten => s,1,Set(sql=SELECT `name`,`prefix` FROM `destinations` WHERE `prefix` IN (’${ARG1:0:1}’,’${ARG1:0:2}’,’${ARG1:0:3}’,’${ARG1:0:4}’,’${ARG1:0:5}’, ‘${ARG1:0:6}’,’${ARG1:0:7}’) ORDER BY LENGTH (prefix) DESC) exten => s,n,MYSQL (Connect connid localhost asteriskuser amp109 asteriskcdrdb) exten => s,n,MYSQL (Query resultid ${connid} ${sql}) exten => s,n,MYSQL (Fetch foundRow ${resultid} name prefix) ; fetch row exten => s,n,MYSQL (Clear ${resultid}) exten => s,n,MYSQL (Disconnect ${connid}) exten => s,n,Set(CDR (userfield)=${name},${ARG2}) exten => s,n,Set(CDR (accountcode)=${prefix}) exten => s,n,Verbose (${ARG2}\,${prefix}\,${name}\, FROM:[${CALLERID (all)}] FOR:[${CDR (dst)}] ) ;пример вызова макроса [to-pstn] exten => _9XXXX.,1,macro(simple-dial,DAHDI/g4/${EXTEN:1},7${EXTEN:2}, to-pstn)
При звонке из контекста to-pstn например, в Уфу на номер 3472933713 в таблице CDR для этого звонка поле accountcode будет иметь значение “73472”, а поле userfield значение “Russia Ufa, to-pstn”. Сам запрос построен на примере тов. dimas: так как длинна значения в поле `prefix` разная для различных стран и направлений, то приходится искать по всем вариациям, например, ‘7’, ‘74’, ‘749’, ‘7495’, ‘74951’, и т.д. и выбирать результат с наиболее длинным префиксом.
Поиск и подстановка названий сотовых операторов
В использованной таблице destinations информация о сотовых направлениях в РФ неполная, однако зачастую необходимо знать, на какого оператора пошел вызов. Распространенный способ экономить на сотовых звонках – установить в шлюзы SIM карты с безлимитным тарифом по всем направлениям на сотовые вызовы. Однако такой подход экономически оправдан тогда, когда звонков на сотовые очень много и загрузка канала достаточно высокая, чтобы каждый канал был занят не менее 60..70% рабочего времени. В таких случаях шлюзы окупаются за 3..4 месяца после внедрения. В небольших же компаниях иная ситуация: звонков на мобильные мало, занятость канала низкая, безлимитный тариф за $200 не окупается за один месяц.
Решением может стать дифференциация шлюзов по операторам, для каждого оператора приобретается отдельный шлюз и SIM карта с безлимитным тарифом только на звонки внутри сети. Но основная проблема состоит в том, что у операторов сотовой связи нет привязки к префиксам (как коды городов). Операторам выдается диапазон номеров в рамках префикса. К примеру диапазон 901 разделен на 170 кусочков между 60 операторами сотовой связи. В этих условиях традиционные АТС остаются в безнадежном проигрыше, им попросту не хватит размера таблицы маршрутизации, но Asterisk такого недостатка лишен: можно на лету, в процессе обработки исходящего вызова делать запрос в БД на соответствие какому-либо диапазону набираемого номера и маршрутизировать вызов на SIM карту нужного оператора.
Для примера попробуем реализовать выборку названия и код оператора из базы данных, для чего понадобится две таблицы: коды и диапазоны номеров и названия операторов. Названия операторов в таблице имеют кириллическое написание, а Asterisk плохо работает с национальными кодировками. Чтобы наблюдать в CLI названия можно воспользоваться перекодированной в транслит таблицей. Макрос для обработки вызова может выглядеть так:
;макрос поиска названия. Реквизиты доступа к БД стандартные для Trixbox ;ARG1 - номер, по которому производить поиск в БД ;ARG2 - имя исходящего контекста/направления [macro-seekmobnumber];следующую инструкцию нужно уместить в одну строку exten => s,1,Set(sql=SELECT `name`, `id` FROM `mtt_operators_tr` WHERE `id`=(SELECT `operator` FROM `mtt_codes` WHERE (${ARG1:4:10} BETWEEN `from` AND `to`) AND `def`=${ARG1:1:3})) exten => s,n,MYSQL (Connect connid localhost asteriskuser amp109 asteriskcdrdb) exten => s,n,MYSQL (Query resultid ${connid} ${sql}) exten => s,n,MYSQL (Fetch foundRow ${resultid} name prefix) ; fetch row exten => s,n,MYSQL (Clear ${resultid}) exten => s,n,MYSQL (Disconnect ${connid}) exten => s,n,Set(CDR (userfield)=${name},${ARG2}) exten => s,n,Set(CDR (accountcode)=${prefix}) exten => s,n,Verbose (${ARG2}\,${prefix}\,${name}\, FROM:[${CALLERID (all)}] FOR:[${CDR (dst)}] )
Модифицировав этот макрос либо используя его части можно легко организовать продвинутую маршрутизацию звонков на сотовые телефоны через GSM шлюзы, для чего можно сделать простой переход в зависимости от значения переменной ${prefix}. Так например, переход на МТС должен осуществляться по коду “1”, на Билайн – по коду “4”.
Можно улучшить макрос, добавив в него поиск региона, в котором располагается мобильный телефон. Это позволит точно отсеять, к примеру, вызовы внутри сети от междугородных, или примерно оценить стоимость звонков. Для этого нужно добавить еще один запрос, на этот раз уже в таблицу mtt_regions. Как и раньше, эта таблица доступна в транслитерованном варианте mtt_regions_tr.
Измененный макрос выглядит так:
;макрос поиска названия региона и оператора. ;ARG1 - номер, по которому производить поиск в БД ;ARG2 - имя исходящего контекста/направления [macro-seekmobnumber] exten => s,1,noop ;здесь и далее длинные строки перенесены exten => s,n,MYSQL (Connect connid localhost asteriskuser amp109 asteriskcdrdb) exten => s,n,Set(sql=SELECT `operator`, `region` FROM `mtt_codes` WHERE (${ARG1:4:10} BETWEEN `from` AND `to`) AND `def`=${ARG1:1:3}) exten => s,n,MYSQL (Query resultid ${connid} ${sql}) exten => s,n,MYSQL (Fetch foundRow ${resultid} operator region) ; fetch row exten => s,n,Set(sql=SELECT `name` FROM `mtt_operators_tr` WHERE `id` = ${operator}) exten => s,n,Set(prefix=${operator}) exten => s,n,MYSQ L(Query resultid ${connid} ${sql}) exten => s,n,MYSQL (Fetch foundRow ${resultid} operator) ; fetch row exten => s,n,Set (sql=SELECT `name` FROM `mtt_regions_tr` WHERE `id` = ${region}) exten => s,n,MYSQL (Query resultid ${connid} ${sql}) exten => s,n,MYSQL (Fetch foundRow ${resultid} region) ; fetch row exten => s,n,MYSQL (Clear ${resultid}) exten => s,n,MYSQL (Disconnect ${connid}) exten => s,n,Set(CDR (userfield)=${region}, ${operator}, ${ARG2}) exten => s,n,Set(CDR (accountcode)=${prefix}) exten => s,n,Verbose (${ARG2}\, ${prefix}\, ${region}\, ${operator}\, FROM:[${CALLERID (all)}] FOR:[${CDR (dst)}] )
Все для пользователя или немного о Usability
Настройки штатной системы отчетности Trixbox не позволяет воспользоваться новыми функциями сразу, для этого нужно менять множество параметров непосредственно в файле /var/www/html/admin/cdr/cdr/call-log.php. Однако вывод с помощью встроенных функций длинных названий регионов и операторов ломает стройные ряды цифр. Для более удобного отображения было решено добавить свою обработку вывода страницы. При этом отображается поле accountcode и при наведении мыши выскакивает hint (см рис.) с названием региона, оператора и исходящего направления (содержимое userfield). Файл call-log.php доступен для скачивания.
Ссылки и файлы
Обсуждение темы: http://asterisk-support.ru/forum/topics/4177/
Таблица кодов стран и городов: http://lynks.ru/file_download/9/destinations.sql
Таблица DEF коды и диапазоны номеров: http://lynks.ru/file_download/10/mtt_codes.sql
Таблица DEF названия операторов http://lynks.ru/file_download/11/mtt_operators.sql
Таблица DEF названия операторов, транслит: http://lynks.ru/file_download/12/mtt_operators_tr.sql
Таблица DEF названия регионов: http://lynks.ru/file_download/13/mtt_regions.sql
Таблица DEF названия регионов, транслит: http://lynks.ru/file_download/14/mtt_regions_tr.sql
Измененный call-log.php: http://lynks.ru/file_download/15/call-log.php
- 15/08/09
- 4
TBE: коды и названия стран и городов в CDR 2009-08-16 01:30 / #
самый первый селект
exten => s,1,Set(sql=SELECT `name`,`prefix` FROM `destinations` WHERE `prefix` IN (’${ARG1:0:1}’,’${ARG1:0:2}’,’${ARG1:0:3}’,’${ARG1:0:4}’,’${ARG1:0:5}’, ‘${ARG1:0:6}’,’${ARG1:0:7}’) ORDER BY LENGTH (prefix) DESC)
можно сделать так
SELECT `name`,`prefix` FROM `destinations` WHERE ‘${ARG1}’ like concat(prefix,’%’) limit 1
или так хуже?
или медленее?
TBE: коды и названия стран и городов в CDR 2009-08-26 21:40 / #
Имхо будет медленней. LIKE очень медленно обрабатывается, так как происходит перебор по всей строке и так по всей таблице. Это обсуждалось на форуме, но ничто не мешает попробовать и сравнить...
TBE: коды и названия стран и городов в CDR 2009-08-31 20:27 / #
да действительно провел эксперементы с like медленее
[code]
mysql> SELECT `id`,`name`,`prefix` FROM `destinations` WHERE 841221234567
like concat(prefix,’%’) order by prefix desc limit 1;
———-————————+————+ | id | name | prefix |
———-————————+————+ | 23269 | VIETNAM MOBILE | 84122 |
———-————————+————+
1 row in set (0.08 sec)
mysql> SELECT `id`,`name`,`prefix` FROM `destinations` WHERE 93751234567
like concat(prefix,’%’) order by prefix desc limit 1;
———————————+————+ | id | name | prefix |
———————————+————+ | 3 | Afghanistan Cdma | 9375 |
———————————+————+
1 row in set (0.04 sec)
mysql> SELECT `id`,`name`,`prefix` FROM `destinations` WHERE `prefix` IN
(‘8’,‘84’,‘841’,‘8412’,‘84122’, ‘841221’,‘8412212’) ORDER BY LENGTH
DESC;
———-————————+————+ | id | name | prefix |
———-————————+————+ | 23269 | VIETNAM MOBILE | 84122 | | 9560 | Vietnam proper | 84 |
———-————————+————+
2 rows in set (0.00 sec)
mysql> SELECT `name`,`prefix` FROM `destinations` WHERE `prefix` IN
(‘9’,‘93’,‘937’,‘9375’,‘93751’, ‘937512’,‘9375123’) ORDER BY LENGTH
DESC;
——————————————+ | name | prefix |
——————————————+ | Afghanistan Cdma | 9375 | | Afghanistan Mobile | 937 | | Afghanistan proper | 93 |
——————————————+
3 rows in set (0.00 sec)
[/code]
TBE: коды и названия стран и городов в CDR 2009-08-31 23:30 / #
Спасибо за сравнение, познавательно! Выигрыш во времени выполнения запроса еще более актуален, когда много одновременных запросов. Например, когда сервер используется для маршрутизации направлений.