Back to the article Lynks - TBE: коды и названия стран и городов в CDR, IP телефония и телефоны, цифровые мини IP АТС и VoIP на основе Asterisk

TBE: коды и названия стран и городов в CDR

Многие при изучении отчетов о звонках хотели бы видеть в поле назначения звонка не безмолвные цифры иногороднего телефонного номера, а название города, чтобы на вскидку прикинуть стоимость звонка и выявить самые популярные направления звонков в компании (например, чтобы выбить с провайдера льготы). В штатной системе анализа CDR дистрибутива Trixbox такой функции нет, однако ни что не мешает ее добавить.


Измененный отчет CDR
Существует как минимум два варианта подстановки названий городов: “на лету” и в процессе построения отчета. Поиск и добавление информации “на лету” дополнительно нагружает сервер как минимум одним запросом в процессе прохождения звонка, но в последствии, при построении отчета, не требуется производить запросы для каждой строки 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
  • Оценка: 2.57/5, голосов: 662

Комментарии

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 / #

Спасибо за сравнение, познавательно! Выигрыш во времени выполнения запроса еще более актуален, когда много одновременных запросов. Например, когда сервер используется для маршрутизации направлений.

Оставить комментарий

Статьи

Корзина (0)

Корзина

Корзина пуста

Последние новости