Поддержание жизни номера

Суть статьи – написание скрипта который проверяет когда последний раз был совершён платный звонок из транка. И если это было больше 40 дней назад совершает платный вызов.

Для чего это надо? Я уже описывал создание транков на основе номеров мегафона с использованием услуги мультифон. Так вот, если не совершать какие-либо платные действия  номером в течении 60 дней оператор начинает списывать деньги со счёта, а в последствии блокирует номер. Что бы этого избежать мы будем совершать один платный звонок раз в 40 дней.

Сбор информации

Что нам для этого потребуется? Знание bash и запросов mysql. Естественно скрипт который я опубликую ниже будет работать с FreePBX и врядли заработает с чем-либо ещё.

Сперва нам нужно будет определить каким образом выловить исходящий звонок, заходим через ssh на mysql, команда выглядит как-то так:

mysql -u freepbxuser -pПАРОЛЬ

логин пароль для доступа к mysql прописан в /etc/asterisk/cdr_mysql.conf, после того как мы зашли смотрим какие у нас есть базы.

 show databases;

получаем что то вроде такого

+--------------------+
| Database |
+--------------------+
| information_schema |
| asterisk |
| asteriskcdrdb |
+--------------------+
3 rows in set (0.00 sec)

Нас интересует только база asteriskcdrdb, переходим в неё – use asteriskcdrdb; и смотрим какие таблицы внутри –  show tables;

+-------------------------+
| Tables_in_asteriskcdrdb |
+-------------------------+
| cdr |
| cel |
+-------------------------+
2 rows in set (0.00 sec)

Получаем две таблицы. Разобравшись немножко становится ясно что первая таблица cdr хранит только основную информацию о звонках, в то время как cel более подробную информацию по каждому звонку. Именно в cel указано с использованием какого транка прошёл звонок. Пробуем посмотреть в каком виде хранятся данные в обеих таблицах, сначала cdr – SELECT * FROM  asteriskcdrdb.cdr limit 5;

+---------------------+-----------------------------+-------------+---------+---------------+-----------------------+--------------------+------------+------------------------------+----------+---------+-------------+----------+-------------+---------------+-----------+-------+-----------------------------------------------------+-------------+-------------+---------------+---------------+----------+
| calldate | clid | src | dst | dcontext | channel | dstchannel | lastapp | lastdata | duration | billsec | disposition | amaflags | accountcode | uniqueid | userfield | did | recordingfile | cnum | cnam | outbound_cnum | outbound_cnam | dst_cnam |
+---------------------+-----------------------------+-------------+---------+---------------+-----------------------+--------------------+------------+------------------------------+----------+---------+-------------+----------+-------------+---------------+-----------+-------+-----------------------------------------------------+-------------+-------------+---------------+---------------+----------+
| 2014-12-07 04:34:43 | 100 | 100 | 2999831 | from-internal | SIP/100-00000000 | SIP/Linky-00000001 | Dial | SIP/Linky/78612999831,300,Tt | 22 | 18 | ANSWERED | 3 | | 1417937683.0 | | | | 100 | Gagarina | 78612999832 | | |
| 2014-12-07 04:35:10 | 100 | 100 | 2999832 | from-internal | SIP/100-00000002 | SIP/Linky-00000003 | Dial | SIP/Linky/78612999831,300,Tt | 15 | 5 | ANSWERED | 3 | | 1417937710.2 | | | | 100 | Gagarina | 78612999832 | | |
| 2014-12-07 04:39:00 | 100 | 100 | 2999832 | from-internal | SIP/100-00000006 | SIP/Linky-00000007 | Dial | SIP/Linky/78612999831,300,Tt | 5 | 0 | NO ANSWER | 3 | | 1417937940.6 | | | | 100 | Gagarina | 78612999832 | | |
| 2014-12-07 04:41:26 | "78612999832" <78612999832> | 78612999832 | 603 | ext-group | SIP/mega-128-00000008 | SIP/100-00000009 | Dial | SIP/100,55,TtrM(auto-blkvm) | 7 | 0 | NO ANSWER | 3 | | 1417938086.8 | | 09170 | rg-603-78612999832-20141207-044127-1417938086.8.wav | 78612999832 | 78612999832 | | | |
| 2014-12-07 04:42:01 | "Gagarina" <100> | 100 | 113 | ext-local | SIP/100-0000000a | SIP/Linky-0000000b | Congestion | 10 | 4 | 0 | NO ANSWER | 3 | | 1417938121.10 | | | exten-113-100-20141207-044201-1417938121.10.wav | 100 | Gagarina | | | |
+---------------------+-----------------------------+-------------+---------+---------------+-----------------------+--------------------+------------+------------------------------+----------+---------+-------------+----------+-------------+---------------+-----------+-------+-----------------------------------------------------+-------------+-------------+---------------+---------------+----------+
5 rows in set (0.00 sec)

Крайне сложно тут что-то понять. Но нас интересует соответствие полей в веб форме, соответствию полей в базе. Забегая вперёд скажу что сложно по cdr определить направление звонка, нас ведь только исходящие интересуют. Зато направление звонка сложно, но можно вычислить по таблице cel. По таблице cdr мы можем узнать был ли ответ в канале, и продолжительность звонка. Посмотреть что творится в таблице cel можно командой SELECT * FROM  asteriskcdrdb.cel limit 5;

Мне удалось выяснить что при исходящем звонке в поле cnam обязательно присутствует CID, в поле app – AppDial, ну и при ответе, а нас только платные звонки интересуют, в поле event – answer, так же в таблице cel за транк отвечает поле channame.

Таким образом мы можем по таблице cel сформировать звонки которые потом проверим на продолжительность по таблице cdr, связывают эти таблицы поле uniqueid в таблице cdr и поле linkedid в таблице cel.

таким образом нам нужно два запроса, первый по таблице cel второй по таблице cdr

SELECT distinct linkedid FROM  asteriskcdrdb.cel WHERE eventtime &gt; (NOW() - interval 40 day) AND channame like '%mega-290%' AND  eventtype like '%answer%' AND cid_name like '%CID%' AND appname like '%AppDial%' ;

этим запросом мы соберём все linkedid где встречается нужный нам транк, с полями как у исходящего звонка за последние 40 дней.

SELECT * FROM  asteriskcdrdb.cdr WHERE uniqueid like '1458972001.1901' AND duration &gt; 16;

– этот запрос выбирает записи с нужным нам uniqueid и продолжительностью звонка больше 16. Я поставил значение 16, потому что в среднем у меня астериск тратит 13 секунда на то что бы начать звонить. Не говоря уже про то что могут трубку не сразу взять итп, а в cdr записывается продолжительность не с момента соединения, а с момента отправки вызова. Предполагаю что идеальный случай вообще секунд 30 поставить, главное что бы тестовый звонок генерируемый скриптом занимал не меньше времени.

теперь нам надо написать bash скрипт, который если не находит нужные записи в mysql совершает звонок.

Скрипт

Для работы скрипта надо будет создать что-то в 5005, у меня там ivr с записью на 10 секунд, напоминаю что звонки менее 3х секунд не тарифицируются, совершать звонок лучше на номер мегафон, тогда будет более низкая цена.

Пару нюансов:

  • во-первых: добавим цикл для перебора транков, у меня их несколько;
  • во-вторых: этот скрипт не может выловить в таблице mysql звонок который сгенерирован скриптом, для этого я в callerID и запихнул уникальное значение.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
#!/bin/bash
 
 
#создаем цикл с транками, указываем имена транков через пробел
for trunk in mega-128 mega-ural-me mega-290 mega-ural-yubik
 do
 echo "первый цикл", $trunk
 #инициализируем пустую isempty
 isempty=''
 #получаем uniqueid из таблицы cel
 res=`mysql -u freepbxuser -pafe4f2d83267 -e "SELECT distinct linkedid FROM asteriskcdrdb.cel WHERE eventtime &gt; (NOW() - interval 40 day) AND channame like '%$trunk%' AND eventtype like '%answer%' AND cid_name like '%CID%' AND appname like '%AppDial%' ;"`
 # если res не пустое
 if [ -n "$res" ] 
 then
 #помещаем полученные значения в массив
 declare -a unique_ar="( $res )"
 #удаляем первый элемент массива (заголовок)
 unset unique_ar[0]
 echo $trunk ${#unique_ar[@]}
 
 
 #запускаем цикл что бы перебрать все значения
 for i in "${unique_ar[@]}"
 do
 echo "запущен второй цикл", $trunk
 #выбираем из таблицы cdr значения где продолжительность больше 30
 isempty=`mysql -u freepbxuser -pafe4f2d83267 -e "SELECT calldate,channel,duration FROM asteriskcdrdb.cdr where uniqueid like '$i' and duration &gt; 30 ;"`
 #проверяем пустая ли запись?
 if [ -n "$isempty" ]
 then
 #если запись не пустая, значит звонок за прошедшие 40 дней был, прерываем цикл
 echo "второй цикл прерван, значение", $isempty
 break
 fi
 
#завршаем цикл mysql
 done
 else
 echo $trunk, "первая проверка по mysql - пусто"
 fi
 #после выполнения цикла запускаем проверку, если переменная isempty пустая, значит надо проверить cdr на уникальный CID из нашего звонка
 if [ -z "$isempty" ]
 then
 echo "переменная empty пустая", $trunk
 # выбираем значения из таблицы cel с уникальным значением CID saBacraRAKekuJ3 которое мы указываем при звонке из скрипта и именем нашего транка
 res=`mysql -u freepbxuser -pafe4f2d83267 -e "SELECT distinct linkedid FROM asteriskcdrdb.cel wHERE eventtime &gt; (NOW() - interval 40 day) and channame like '%$trunk%' and cid_name like '%saBacraRAKekuJ3%';"`
 #помещаем полученные значения в массив
 declare -a unique_ar="( $res )"
 #удаляем первый элемент массива (заголовок)
 unset unique_ar[0]
 #запускаем цикл что бы перебрать все значения
 for i in "${unique_ar[@]}"
 do
 echo "запущен третий цикл", $trunk
 #выбираем из таблицы cdr значения где продолжительность больше 13
 isempty=`mysql -u freepbxuser -pafe4f2d83267 -e "SELECT calldate,channel,duration FROM asteriskcdrdb.cdr where uniqueid like '$i' and duration &gt; 13 ;"`
 #проверяем пустая ли запись?
 if [ -n "$isempty" ]
 then
 #если запись не пустая, значит звонок за прошедшие 40 дней был, прерываем цикл
 echo "третий цикл прерван, значение", $isempty
 break
 fi
 #завршаем цикл mysql
 done
 fi
 
 #после выполнения цикла запускаем проверку, если переменная isempty до сих пор пустая - совершаем звонок
 if [ -z "$isempty" ]
 then
 echo "совершаем звонок"
 # Уникальна¤ метка
 ts=$(date +%s%N)
 
 # Создаем call файла
 callname=/tmp/web-call.$ts.call
 
 # Call-файл Asterisk
 #sip/имя_транка/номер_телефона куда звоним
 #имя транка заменено на $trunk
 echo "Channel: SIP/$trunk/79282099831" &gt;&gt; $callname
 # CID уникальное значение
 echo "CallerID: "saBacraRAKekuJ3"&lt;5556&gt;" &gt;&gt; $callname
 echo "MaxRetries: 10" &gt;&gt; $callname
 echo "RetryTime: 60" &gt;&gt; $callname
 echo "WaitTIme: 30" &gt;&gt; $callname
 echo "Context: from-internal" &gt;&gt; $callname
 # с чем будем соединять на астериске
 echo "Extension: 5005" &gt;&gt; $callname
 echo "Priority: 1" &gt;&gt; $callname
 
 mv $callname /var/spool/asterisk/outgoing/
 
 fi
#завершаем цикл транков
done

Добавлю что номер 79282099831 у меня также завязан на астериск и на все звонки отвечает в канал, сам скрипт с помощью webmin я поставил в планировщик на ночь, когда работает голосовая почта, что бы мне сами звонки не мешали.