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

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

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`;

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

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

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

Нам необходимо создать папку для статистики входящих вызовов, то есть добавить в путь /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 минут.