artem
14.05.2018
1582

Сбор статистики звонков по региональным направлениям.

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

Для начала нам необходима база регионов для городских и мобильных номеров, получим ее с сайта Россвязи:

mkdir /tmp/rossvyaz
cd /tmp/rossvyaz
 
wget http://www.rossvyaz.ru/docs/articles/Kody_ABC-3kh.csv
wget http://www.rossvyaz.ru/docs/articles/Kody_ABC-4kh.csv
wget http://www.rossvyaz.ru/docs/articles/Kody_ABC-8kh.csv
wget http://www.rossvyaz.ru/docs/articles/Kody_DEF-9kh.csv

Меняем кодировку файлов на UTF-8, меняем переносы строк на Unix-овые.

>for file in /tmp/rossvyaz/*; do new_file=${file%.csv}_UTF8.csv; iconv -f CP1251 -t UTF-8 “$file” >”$new_file”; dos2unix “$new_file”; sed -ri ‘s|[[:space:]]+;[[:space:]]+|;|g’ “$new_file”; rm -f “$file”; done
Также, выбрасываем из CSV-файлов 4-й столбец и всё, что дальше шестого столбца (т.е. должны остаться префикс, начало и конец диапазона, провайдер и регион).
for file in /tmp/rossvyaz/*; do sed -ri ‘s|^([^;]+);([^;]+);([^;]+);([^;]+);([^;]+);([^;]+).*$|1;2;3;5;6|’ “$file”; done

Создаём таблицу в MySQL для хранения диапазонов номеров. Если Asterisk пишет в базу данных MySQL CDR и/или CEL, то для создания таблицы можно использовать ту же базу данных.

CREATE TABLE `tel_codes` (
  `prefix` smallint(3) NOT NULL,
  `num_from` int(7) NOT NULL,
  `num_to` int(7) NOT NULL,
  `capacity` int(7) NOT NULL,
  `operator` varchar(255) NOT NULL DEFAULT “”,
  `region` varchar(255) NOT NULL DEFAULT “”,
  KEY `prefix` (`prefix`),
  KEY `num_from` (`num_from`),
  KEY `num_to` (`num_to`)
) DEFAULT CHARSET=utf8 ENGINE=InnoDB

Загружаем в созданную таблицу все подготовленные CSV-файлы:

LOAD DATA INFILE ‘/tmp/rossvyaz/Kody_ABC-3kh_UTF8.csv’ INTO TABLE `tel_codes`
FIELDS TERMINATED BY ‘;’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘n’
IGNORE 1 LINES
(`prefix`,`num_from`,`num_to`,`capacity`,`operator`,`region`);
(выполняем команду для каждого файла)

Если при импорте появляется ошибка доступа к файлу, можно скопировать файлы в корень файловой системы, поставить на них полные права всем и импортировать оттуда.

cp /tmp/rossvyaz/*.csv /
chown nobody. /*.csv
chmod 666 /*.csv

Настраиваем доступ для пользователя к данной таблице

GRANT ALL PRIVILEGES ON asteriskcdrdb.tel_codes TO regionsuser@localhost IDENTIFIED BY ‘regionspasswd’;
FLUSH PRIVILEGES;

Таблица регионов

Таблица готова, теперь необходимо создать скрипт сборки статистики.

Собирать будем за один месяц (при желании можно поменять) однако скрипт работает довольно долго для больших объемов, потому запускать его будем через крон в нерабочее время.

Скрипт писать будем на PHP:

Схему подключения к базе будем использовать из статей по web-инструментам (DND, CF…)

<?php
include(‘connectdb.php’);
 
$prefix = array(); //Массивы нужны для дальнейшей обработки и вывода данных.
$tel_num = array();
$billsec = array();
$region = array();
$count = array();
 

Далее указываем интервалы времени:

$date1=`date -d “1 month ago” +%Y-%m-%d`; //1я дата интервала
$date2=`date -d “0 month ago” +%Y-%m-%d`; //2я дата интервала
$daten=`date +%Y-%m-%d`; //Текущая дата

Именно в этом месте вместо 1 или 0 можно подставлять свои значения, либо получать их из переменных ($argv1, $argv2) ВАЖНО! Чтобы первая дата была меньше второй, иначе скрипт не отработает.

 Теперь необходимо создать файл в который и будем писать статистику.

Для разделения будем писать статистику в определенную папку с указанием даты. Для этого необходимо эту дату получить

$datey=`date -d “1 month ago” +%Y`;
$datem=`date -d “1 month ago” +%m`;

Открываем файл для записи, соответственно папку необходимо будет создать:

$mkdir=`mkdir /var/log/regions_log/$datey/$datem/ -p`;
$fp = fopen(“/var/log/regions_log/$datey/$datem/regions-$date1..$date2.csv”, ‘w’);
fputcsv($fp, array(‘Номер’, ‘Регион’, ‘Длительность’, ‘Количество’)); //Шапка таблицы

И осуществляем запрос в базу данных к таблице cdr на исходящие вызовы:

$request = bd_bridge(“asteriskcdrdb”,”SELECT dst, billsec FROM asteriskcdrdb.cdr where billsec>’0′ and dst REGEXP ‘^[0-9][0-9][0-9][0-9][0-9]+’ and dst like ‘_________%’ and calldate>'”.$date1.” 00:00:00′ AND calldate<‘”.$date2.” 23:59:59’ group by dst;”);

Маску для подбора исходящих номеров можете поменять по своему желанию.

Открываем цикл для обработки, разделения каждого номера на префикс и номер (для этого и нужны массивы заведенные выше).

while($row = mysqli_fetch_array($request)){
 
        $len=strlen($row[‘dst’]);
                if ($len==’11’) {
                $prefix[$i]=substr($row[‘dst’], 1, 3);
                $tel_num[$i]=substr($row[‘dst’], 4);
                $i++;
                } elseif ($len==’10’) {
                        $prefix[$i]=substr($row[‘dst’], 0, 3);
                                $tel_num[$i]=substr($row[‘dst’], 3);
                                $i++;
                }
        }

На данный момент реализовано распознавание только номеров из 11 и 10 символов (с 8 или 7 и без)

После заполнения данных массивов, открываем цикл для заполнения таблицы и определения регионов. (Самая ресурсоемкая часть).

for ($i=0;$i<count($prefix);$i++) {
                $num=$prefix[$i].$tel_num[$i];
$requestsec = bd_bridge(“asteriskcdrdb”,”SELECT SUM(billsec),count(*) FROM asteriskcdrdb.cdr where dst like ‘%”.$num.”%’ and billsec<>’0′ and calldate>='”.$date1.” 00:00:00′ AND calldate<='”.$date2.” 23:59:59′;”);
        $rowsec = mysqli_fetch_array($requestsec);
        $billsec[$i]=$rowsec[‘SUM(billsec)’];
        $count[$i]=$rowsec[‘count(*)’];
//Тут получаем длительность и количество вызовов по данному номеру.
 

Теперь определим регион номера:

$requestreg = bd_bridge(“asteriskcdrdb”,”SELECT region FROM regions.tel_codes WHERE prefix='”.$prefix[$i].”‘ AND num_from<='”.$tel_num[$i].”‘ AND num_to>='”.$tel_num[$i].”‘ LIMIT 1;”);
        $rowreg = mysqli_fetch_array($requestreg);
        $region[$i]=$rowreg[‘region’];

И внесем в таблицу:

fputcsv($fp, array($num, $region[$i], $billsec[$i], $count[$i]));

Теперь закрываем цикл и файл.

}fclose($fp);

Таблица

Для получения статистики по входящим звонкам изменить необходимо только первый запрос к cdr:

$request = bd_bridge(“asteriskcdrdb”,”SELECT cnum, billsec FROM asteriskcdrdb.cdr where billsec>’0′ and cnum REGEXP ‘^[0-9][0-9][0-9][0-9][0-9]+’ and cnum like ‘_________%’ and calldate>'”.$date1.” 00:00:00′ AND calldate<‘”.$date2.” 23:59:59’ group by cnum;”);
 

На данном этапе можно остановиться.

Однако каждый раз подключаться к АТС по ssh или ftp, для выгрузки статистики может быть неудобно, потому настроим веб-сервер для доступа к папке со статистикой:

Listen 5443
NameVirtualHost *:5443
<VirtualHost *:5443>
      DocumentRoot /var/www/html/regions
        <Directory /var/www/html/regions>
        Options +Indexes
        AllowOverride All
        Order deny,allow
        Deny from all
        Allow from all
        Satisfy all
        RewriteEngine off
        </Directory>
</VirtualHost>

И создадим символьную ссылку в корневой директории веб-сервера:

Cd /var/www/html/
Ln –s /var/log/regions_log

Соответственно директория должна принадлежать пользователю, под которым работает веб-сервер, в данном случае asterisk

Chown asterisk. /var/log/regions_log –R

И добавляем в конец скрипта сбора статистики следующую строчку:

$put=`chown asterisk. /var/log/regions_log/ -R`;
 

Теперь файлы статистики будут доступны через веб (главное не забыть разрешить доступ).

Доступ через web (Файл в корне был перенесен)

Доступ через web. (Файл в корне был перенесен)

На данном этапе можно закончить.

Рассмотрим возможность дополнения для входящей статистики:

Нам необходимо создать папку для статистики входящих вызовов, то есть добавить в путь /var/log/regions_log/ 2 подпапки – in и out,

В таком случае запись файла для исходящих будет выглядеть следующим образом:

$mkdir=`mkdir /var/log/regions_log/out/$datey/$datem/ -p`;
$fp = fopen(“/var/log/regions_log/out/$datey/$datem/regions-$date1..$date2.csv”, ‘w’);
fputcsv($fp, array(‘Номер’, ‘Регион’, ‘Длительность’, ‘Количество’)); //Шапка таблицы

А для исходящих:

$mkdir=`mkdir /var/log/regions_log/in/$datey/$datem/ -p`;
$fp = fopen(“/var/log/regions_log/in/$datey/$datem/regions-$date1..$date2.csv”, ‘w’);
fputcsv($fp, array(‘Номер’, ‘Регион’, ‘Длительность’, ‘Количество’)); //Шапка таблицы

Нежелательно сбор статистики для исходящей и входящей осуществлять в одном скрипте, это приведет к чрезмерной нагрузке, лучше создать два скрипта и запускать их с промежутком в 20-30 минут.

Полные скрипты с последними правками

Полные скрипты с последними правками

 

 

avatar
  Подписаться  
Уведомление о

Остались вопросы?

Я - Виталий Шелест, менеджер компании Voxlink. Хотите уточнить детали или готовы оставить заявку? Укажите номер телефона, я перезвоню в течение 3-х секунд.

VoIP оборудование

ближайшие курсы

ближайшие Вебинары

ONLINE

10 доводов в пользу Asterisk

Распространяется бесплатно.

Asterisk – программное обеспечение с открытым исходным кодом, распространяется по лицензии GPL. Следовательно, установив один раз Asterisk вам не придется дополнительно платить за новых абонентов, подключение новых транков, расширение функционала и прочие лицензии. Это приближает стоимость владения станцией к нулю.

Безопасен в использовании.

Любое программное обеспечение может стать объектом интереса злоумышленников, в том числе телефонная станция. Однако, сам Asterisk, а также операционная система, на которой он работает, дают множество инструментов защиты от любых атак. При грамотной настройке безопасности у злоумышленников нет никаких шансов попасть на станцию.

Надежен в эксплуатации.

Время работы серверов некоторых наших клиентов исчисляется годами. Это значит, что Asterisk работает несколько лет, ему не требуются никакие перезагрузки или принудительные отключения. А еще это говорит о том, что в районе отличная ситуация с электроэнергией, но это уже не заслуга Asterisk.

Гибкий в настройке.

Зачастую возможности Asterisk ограничивает только фантазия пользователя. Ни один конструктор шаблонов не сравнится с Asterisk по гибкости настройки. Это позволяет решать с помощью Asterisk любые бизнес задачи, даже те, в которых выбор в его пользу не кажется изначально очевидным.

Имеет огромный функционал.

Во многом именно Asterisk показал какой должна быть современная телефонная станция. За многие годы развития функциональность Asterisk расширилась, а все основные возможности по-прежнему доступны бесплатно сразу после установки.

Интегрируется с любыми системами.

То, что Asterisk не умеет сам, он позволяет реализовать за счет интеграции. Это могут быть интеграции с коммерческими телефонными станциями, CRM, ERP системами, биллингом, сервисами колл-трекинга, колл-бэка и модулями статистики и аналитики.

Позволяет телефонизировать офис за считанные часы.

В нашей практике были проекты, реализованные за один рабочий день. Это значит, что утром к нам обращался клиент, а уже через несколько часов он пользовался новой IP-АТС. Безусловно, такая скорость редкость, ведь АТС – инструмент зарабатывания денег для многих компаний и спешка во внедрении не уместна. Но в случае острой необходимости Asterisk готов к быстрому старту.

Отличная масштабируемость.

Очень утомительно постоянно возвращаться к одному и тому же вопросу. Такое часто бывает в случае некачественного исполнения работ или выбора заведомо неподходящего бизнес-решения. С Asterisk точно не будет такой проблемы! Телефонная станция, построенная на Asterisk может быть масштабируема до немыслимых размеров. Главное – правильно подобрать оборудование.

Повышает управляемость бизнеса.

Asterisk дает не просто набор полезных функций, он повышает управляемость организации, качества и комфортности управления, а также увеличивает прозрачность бизнеса для руководства. Достичь этого можно, например, за счет автоматизации отчетов, подключения бота в Telegram, санкционированного доступа к станции из любой точки мира.

Снижает расходы на связь.

Связь между внутренними абонентами IP-АТС бесплатна всегда, независимо от их географического расположения. Также к Asterisk можно подключить любых операторов телефонии, в том числе GSM сим-карты и настроить маршрутизацию вызовов по наиболее выгодному тарифу. Всё это позволяет экономить с первых минут пользования станцией.