Dynamic Clip Routing

Наведение входящих вызовов на звонившего внутреннего абонента

Внутренний абонент Asterisk вызывает внешнего. Когда внешний абонент перезванивает на городскую линию Asterisk, его вызов направляется непосредственно на звонившего внутреннего абонента
Для определения внутреннего номера звонившего абонента, используется стандартная БД CDR FreepBX - asteriskcdrdb.

[dynamic_did]
exten => _X.,1,Set(CHANNEL(language)=ru)
exten => _X.,n,Set(CALLID=${CALLERID(num):-11})
exten => _X.,n,MYSQL(Connect connidcdr localhost userdb passworddb asteriskcdrdb utf8)
;; All CALL
exten => _X.,n,MYSQL(Query resultidcdr ${connidcdr} SELECT * FROM cdr WHERE dst LIKE '%${CALLID}%' ORDER BY calldate DESC)
;; NOANSWER CALL ONLY
;;exten => _X.,n,MYSQL(Query resultidcdr ${connidcdr} SELECT * FROM cdr WHERE dst LIKE '%${CALLID}%' AND disposition LIKE 'NO ANSWER' ORDER BY calldate DESC)
;;
exten => _X.,n,MYSQL(Fetch fetchid ${resultidcdr} accid calldate clid src dst)
exten => _X.,n,Set(number=${src})
exten => _X.,n,NoOp(caller --> ${clid} callee --> ${dst})
exten => _X.,n,GOTOIF($["${dst}" = ""]?nodst:dst)
exten => _X.,n(dst),MYSQL(Clear ${resultidcdr})
exten => _X.,n,MYSQL(Disconnect ${connidcdr})
exten => _X.,n,Dial(SIP/${number},20,tT)
exten => _X.,n,GotoIf($["${DIALSTATUS}" = "BUSY"]?nodst)
exten => _X.,n,GotoIf($["${DIALSTATUS}" = "NOANSWER"]?nodst)
exten => _X.,n,GotoIf($["${DIALSTATUS}" = "FAILED"]?nodst)
exten => _X.,n(nodst),MYSQL(Clear ${resultidcdr})
exten => _X.,n,MYSQL(Disconnect ${connidcdr})
exten => _X.,n,Goto(from-trunk,${DID},1)
exten => h,1,hangup()

Выборка данных за последние 120 минут

 SELECT * FROM cdr WHERE dst LIKE '%${CALLID}%' AND calldate >= DATE_SUB(NOW(), INTERVAL 120 MINUTE) ORDER BY calldate DESC;

или за три часа:

 SELECT * FROM cdr WHERE dst LIKE '%${CALLID}%' AND calldate >= DATE_SUB( NOW(), INTERVAL 03 HOUR) ORDER BY calldate DESC;

Вывод консоли Asterisk при маршрутизации по cdr БД:

 -- Executing [3216111@from-samsung:1] Goto("SIP/samsung-00001694", "dynamic_did,3216111,1") in new stack
    -- Goto (dynamic_did,1111,1)
    -- Executing [3216111v@dynamic_did:1] Set("SIP/samsung-00001694", "CHANNEL(language)=ru") in new stack
    -- Executing [3216111@dynamic_did:2] Set("SIP/samsung-00001694", "CALLID=8129981138 ") in new stack
    -- Executing [3216111@dynamic_did:3] MYSQL("SIP/samsung-00001694", "Connect connidcdr localhost userdb passworddb asteriskcdrdb utf8") in new stack
    -- Executing [3216111@dynamic_did:4] MYSQL("SIP/samsung-00001694", "Query resultidcdr 11 SELECT * FROM cdr WHERE dst LIKE '%8129981138%' AND calldate >= DATE_SUB(NOW(), INTERVAL 03 HOUR)") in new stack
    -- Executing [3216111@dynamic_did:5] MYSQL("SIP/samsung-00001694", "Fetch fetchid 12 accid calldate clid src dst") in new stack
[2014-12-15 13:45:45] WARNING[6925][C-00000c60]: app_mysql.c:498 aMYSQL_fetch: ast_MYSQL_fetch: More fields (24) than variables (5)
    -- Executing [3216111@dynamic_did:6] Set("SIP/samsung-00001694", "number=1000") in new stack
    -- Executing [3216111@dynamic_did:7] NoOp("SIP/samsung-00001694", "кто звонил --> 1000 кому звонил --> 8129981138") in new stack
    -- Executing [3216111@dynamic_did:8] GotoIf("SIP/samsung-00001694", "0?nodst:dst") in new stack
    -- Goto (dynamic_did,1111,9)
    -- Executing [3216111@dynamic_did:9] MYSQL("SIP/samsung-00001694", "Clear 12") in new stack
    -- Executing [3216111@dynamic_did:10] MYSQL("SIP/samsung-00001694", "Disconnect 11") in new stack
    -- Executing [3216111@dynamic_did:11] Dial("SIP/samsung-00001694", "SIP/1000,20,tT") in new stack

Custom DB MySQL

[dynamic_route]
exten => _X.,1,Set(CHANNEL(language)=ru)
exten => _X.,n,Set(CALLID=${CALLERID(num):-7})
exten => _X.,n,MYSQL(Connect connidcdr localhost dbuser dbpassword dbname utf8)
exten => _X.,n,MYSQL(Query resultidcdr ${connidcdr} SELECT * FROM Callers WHERE CID LIKE '%${CALLID}%')
exten => _X.,n,MYSQL(Fetch fetchid ${resultidcdr} CID EXT)
exten => _X.,n,Set(number=${EXT})
exten => _X.,n,NoOp(caller --> ${CID} callee --> ${EXT})
exten => _X.,n,GotoIf($["${EXT}" = ""]?nodst:dst)
exten => _X.,n(dst),MYSQL(Clear ${resultidcdr})
exten => _X.,n,MYSQL(Disconnect ${connidcdr})
exten => _X.,n,Dial(SIP/${number},20,tT)
exten => _X.,n,GotoIf($["${DIALSTATUS}" = "BUSY"]?nodst)
exten => _X.,n,GotoIf($["${DIALSTATUS}" = "NOANSWER"]?nodst)
exten => _X.,n,GotoIf($["${DIALSTATUS}" = "FAILED"]?nodst)
exten => _X.,n(nodst),MYSQL(Clear ${resultidcdr})
exten => _X.,n,MYSQL(Disconnect ${connidcdr})
exten => _X.,n,Goto(from-trunk,${DID},1)
exten => h,1,hangup()

 mysql> create database CLIP;

 mysql> use CLIP;

 mysql> CREATE TABLE Callers (CID VARCHAR(20), EXT VARCHAR(4), NAME VARCHAR(35));

 mysql> INSERT Callers (CID,EXT,NAME) values (999000774,1000,'Test');

mysql> select * from Callers limit 100;
+-----------+------+------+
| CID       | EXT  | NAME |
+-----------+------+------+
| 999000774 | 1000 | Test |
+-----------+------+------+
2 rows in set (0.00 sec)

Asterisk Dialplan advanced (Диалплан расширенный)