Macro for balancing outgoing calls to GSM in Asterisk

Hi, Habr!

In the past topic (already in drafts) I promised to provide the habrasoobshchestvu with a valid macro for balancing outgoing calls via the N-th number of SIM cards. The subject, in fact, found, improved and tested. Plus - it guarantees that the SIM with the exceeded limit will not be used.

Given: FreeBSD 9, Asterisk 11.4, 2x Addpac GS1002 (all 4 SIM cards are used). 4 trunks for SIM cards are configured: 9001, 9002, 9003, 9004, respectively. On them a striped tariff with "unlimited" 300 minutes per month on each.
Task: If a limit is reached on a SIM card, do not use it when dialing. If everything is exhausted - to send a call through the provider (there is a bit cheaper than via GSM with charging)

1. We configure cdr saving in mysql

I see no reason to copy the manual here. I will add only, check the presence of the cdr_mysql.so module in your build.

2. Add a context with the macro to extensions.conf (for work, the app_mysql.so module is required - Simple Mysql Interface!)

The macro itself is commented in detail.
')

[macro-gsm] exten => s,1(start),Set(ARG2=$[${ARG2} + 1]) ;,    same => n,GotoIf($[${ARG2}>=9005]?sip:repeat) ;  4 ,  9005 -    same => n(repeat),MYSQL(Connect connid localhost asterisk password asterisk) ;    same => n,MYSQL(Query resultid ${connid} SELECT SUM(`billsec`) FROM `cdr` WHERE `dstchannel` LIKE '%${ARG2}%' AND `calldate` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-01') AND LAST_DAY(NOW()) + INTERVAL 1 DAY) ;  .  -           same => n,MYSQL(Fetch fetchid ${resultid} duration) ;    duration same => n,MYSQL(Clear ${resultid}) ;     same => n,MYSQL(Disconnect ${connid}) same => n,GotoIf($[${duration}<=18000]?call:start) ;      300 ,    ,  -   ,         same => n(call),Set(limit=$[18000 - ${duration}]) ;    ,      same => n,Dial(SIP/${ARG2}/${ARG1},,S(${limit})tTr) ; same => n,GotoIf($[${DIALSTATUS}=ANSWER]?finish:start) ;  -  ,   -   same => n(sip),Dial(SIP/ctm/${ARG1},,Ttr) ;   same => n(finish),Hangup ;  exten => h,1,MYSQL(Clear ${resultid}) ;      ,  . same => n,MYSQL(Disconnect ${connid}) 

An example call in dialplan: exten => _79XXXXXXXXX, 1, Macro (gsm, $ {EXTEN}, 9000)
9000: trunk number - 1

Macro tested and works. The obvious disadvantages are a non - optimized query, the names of the trunks are only digital. There is a desire to help - offer your options in the comments. Plus - it is effective)

Thanks to pfactum , PyroRed and Malamut for the inspiration.

UPD: Macro updated, thanks to WhiteD and ragus