Asterisk: определение региона по номеру телефона с использованием PHP и БД

Для реализации решения мы используем данные выписки из реестра российской системы и плана нумерации: https://rossvyaz.gov.ru/deyatelnost/resurs-numeracii/vypiska-iz-reestra-sistemy-i-plana-numeracii, PHP скрипт, который обрабатывает данные и заносит в БД. Скачиваем базу кодов:
wget -N https://rossvyaz.gov.ru/data/DEF-9xx.html
wget -N https://rossvyaz.gov.ru/data/ABC-8xx.html
wget -N https://rossvyaz.gov.ru/data/ABC-4xx.html
wget -N https://rossvyaz.gov.ru/data/ABC-3xx.html
Создаем пользователя и базу данных:
CREATE USER 'asterisk_reg_number'@'localhost' IDENTIFIED BY 'XXXXXXXX';
CREATE DATABASE IF NOT EXISTS `asterisk_reg_number_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL PRIVILEGES ON `asterisk_reg_number_db`.* TO 'asterisk_reg_number'@'localhost';
Подключаемся к базе под созданным пользователем и создаем таблицу num:
mysql -uasterisk_reg_number -p'XXXXXXXX' asterisk_reg_number_db
CREATE TABLE `num` (
`def` smallint(6) NOT NULL,
`start` int(11) NOT NULL,
`finish` int(11) NOT NULL,
`count` int(11) NOT NULL,
`operator` varchar(64) NOT NULL DEFAULT '',
`region` varchar(128) NOT NULL DEFAULT '',
`region_last` varchar(64) DEFAULT NULL,
`region_first` varchar(64) DEFAULT NULL,
KEY `a_b` (`def`,`start`),
KEY `a` (`def`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Перед подключением убедитесь что у вас в конфигурации my.cnf выставлен параметр log_bin_trust_function_creators = 1 иначе вы получите ошибку. Данный параметр можно выставить налету, но не забудьте прописать его в конфигурации my.cnf. Подключаемся под суперпользователем и создаем хранилку:
mysql -uroot -p`cat /root/.mysql`
SET GLOBAL log_bin_trust_function_creators=1;
USE asterisk_reg_number_db;

DROP PROCEDURE IF EXISTS `num_region`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `num_region`(n char(10)) RETURNS varchar(64) CHARSET utf8
BEGIN
    DECLARE l VARCHAR(3);
    DECLARE r VARCHAR(7);
    DECLARE tmp VARCHAR(64);
    DECLARE tmp2 VARCHAR(64);
    DECLARE i INT;
    SET l=LEFT(n, 3);
    SET r=RIGHT(n, 7);
    SELECT region_last INTO tmp FROM num where `def`=l and `start`<=r and `finish`>=r;
    IF (length(TRIM(tmp))=0 OR tmp like NULL) THEN
         SET tmp='---';
    END IF;
    RETURN tmp;
END//
DELIMITER ;
Скрипт PHP работает на версиях 7.X, немного изменен алгоритм преобразования CP1251 в UTF из оригинала Для работы скрипта требуется php-mbstring, php-mysqlnd. Скачиваем скрипт в ту же директорию, что и HTML файлы и запускаем:
wget https://raw.githubusercontent.com/sfnagg/asterisk_scripts/master/region_parser.php
php region_parser.php
На выводе скрипта будут обработанные файлы:
DEF-9xx.html
ABC-3xx.html
ABC-4xx.html
ABC-8xx.html
Проверим что в базе данных появились записи и они отображаются в читаемом виде:
> select * from num limit 5;
+-----+--------+--------+--------+---------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
| def | start  | finish | count  | operator                  | region                              | region_last                         | region_first                        |
+-----+--------+--------+--------+---------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
| 900 |      0 |  61999 |  62000 | ООО "Т2 Мобайл"           | Краснодарский край                  | Краснодарский край                  | Краснодарский край                  |
| 900 |  62000 |  62999 |   1000 | ООО "Т2 Мобайл"           | Ростовская обл.                     | Ростовская обл.                     | Ростовская обл.                     |
| 900 |  63000 |  99999 |  37000 | ООО "Т2 Мобайл"           | Краснодарский край                  | Краснодарский край                  | Краснодарский край                  |
| 900 | 100000 | 199999 | 100000 | ООО "Т2 Мобайл"           | Тверская обл.                       | Тверская обл.                       | Тверская обл.                       |
| 900 | 200000 | 299999 | 100000 | ООО "Т2 Мобайл"           | Челябинская обл.                    | Челябинская обл.                    | Челябинская обл.                    |
+-----+--------+--------+--------+---------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
5 rows in set (0.009 sec)
Проверяем что выборка работает
SELECT num_region('909XXXXXXX');
+----------------------------------------+
| num_region('909XXXXXXX')               |
+----------------------------------------+
| Калининградская обл.                   |
+----------------------------------------+
1 row in set (0.01 sec)
Прописываем настройки для использования в диалплане через ODBC:
/etc/asterisk/func_odbc.conf

[CALLREGION]
dsn=MySQL_asterisk_reg
readsql=SELECT num_region('${SQL_ESC(${ARG1})}');
/etc/asterisk/res_odbc.conf

[MySQL_asterisk_reg]
enabled => yes
dsn => asterisk_reg_num
max_connections => 100
pre-connect => yes
username => asterisk_reg_number
password => XXXXXXX
/etc/odbc.ini

[asterisk_reg_num]
Driver      = MySQL ODBC 8.0 Unicode Driver
Description = MySQL connection to 'asteriskcdrdb' database
Server      = localhost
UID         = asterisk_reg_number
PWD         = XXXXXXX
Port        = 3306
Database    = asterisk_reg_number_db
Option      = 3
Charset     = utf8
Использование в диалплане Asterisk:
NoOp(${ODBC_CALLREGION(${CALLERID(num):1})})
У вас может возникнуть проблема с отображением кириллицы из базы данных, когда при выборке вы получаете корректные значения, а в консоли АТС видите знаки вопроса "??????? ???". Нужно проверить что у вас выбрано в /etc/odbcinst.ini важно отметить что надо выбирать не "w" (UNICODE), а "a" (ANSI) для UTF8:
[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib64/libmyodbc8w.so
UsageCount=1

[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib64/libmyodbc8a.so
UsageCount=1
Следовательно в /etc/odbc.ini надо изменить драйвер на MySQL ODBC 8.0 ANSI Driver:
/etc/odbc.ini

[asterisk_reg_num]
Driver      = MySQL ODBC 8.0 ANSI Driver
Description = MySQL connection to 'asterisk_reg_number_db' database
Server      = localhost
UID         = asterisk_reg_number
PWD         = T4fSuNQwWx8$dB
Port        = 3306
Database    = asterisk_reg_number_db
Option      = 3
Charset     = utf8
Теперь проблем быть не должно, если они у вас остались, нужно проверить что БД настроена на UTF-8:
MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)
[mysqld]
init-connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci