Asterisk - limit total time usage of a trunk

Here's how you can limit the amount of time used on a particular trunk by looking up the time in SQL.
I tested this in the ISSABEL deployment of asterisk, but should work in any.

Once the time is exceeded on a trunk, the dialplan will skip to subsequent trunks.

(scroll to the bottom for advanced users for the completed code, the next bit I'm showing how to make the code change for users unfamiliar with dialplan changes)

HOW IT WORKS
The way this works is upon making a call that would use a particular trunk, the system does an SQL query on total outbound seconds used for the last 30 days.  It selects it by looking at the trunk name and adding up the values.
Then depending if you've reached your maximum or not, it will send the call to trunks accordingly.

(GUI) PBX -> Outbound Routes
Open the route you want to apply this "time limit" rule and verify at least these TWO conditions

1: Under "Trunk Sequence" you want the first route to be the one if the time limit is NOT exceeded
2: The next route is the one ff the time IS exceeded. (you can have as many more as you want)

Make sure those changes are saved

Now open up /etc/asterisk/extensions_additional.conf
Find your route in the dialplan. it will look SOMETHING like the one below. Yours maybe different heading and number of trunks, so just use mine as an example only, modify your file.
This is mine, and i have TWO Destinations

[outrt-2] ; 1000minlimitroute
include => outrt-2-custom
exten => _.,1,Macro(user-callerid,LIMIT,EXTERNAL,)
exten => _.,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})})
exten => _.,n,Set(_NODEST=)
exten => _.,n,Gosub(sub-record-check,s,1(out,${EXTEN},))
exten => _.,n,Macro(dialout-trunk,1,${EXTEN},,off)
exten => _.,n,Macro(dialout-trunk,2,${EXTEN},,off)
exten => _.,n,Macro(outisbusy,)
;--== end of [outrt-2] ==--;

Copy and paste the entire section

open up /etc/asterisk/extensions_override_issabelpbx.conf  (if you are using a different deployment "extensions_custom.conf" should work as well

Go to the bottom of the file, and PASTE it

Now we'll modify this file.

First we'll add the database lookup, insert this code. Change USERNAME and PASSWORD to be your database:
Change TRUNKNAME to be the trunk name you want to check against.
Change "30" to be the number of days you want to look back on
Change 60000 to be the number of seconds you want to limit to

exten => _.,n,MYSQL(Connect connid localhost USERNAME PASSWORD asteriskcdrdb)
exten => _.,n,MYSQL(Query resultid ${connid} SELECT if(sum(duration) < 60000, 1, 0) FROM cdr WHERE DATE(calldate) >= DATE(NOW()) - INTERVAL 30 DAY and dstchannel like '%TRUNKNAME%')
exten => _.,n,MYSQL(Fetch fetchid ${resultid} dynroute)
exten => _.,n,MYSQL(Clear ${resultid})
exten => _.,n,MYSQL(Disconnect ${connid})

Put it under the GOSUB statement in the base dialplan code:
It will look like this:

[outrt-2] ; 1000minlimitroute
include => outrt-2-custom
exten => _.,1,Macro(user-callerid,LIMIT,EXTERNAL,)
exten => _.,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})})
exten => _.,n,Set(_NODEST=)
exten => _.,n,Gosub(sub-record-check,s,1(out,${EXTEN},))

exten => _.,n,MYSQL(Connect connid localhost user password asteriskcdrdb)
exten => _.,n,MYSQL(Query resultid ${connid} SELECT if(sum(duration) < 60000, 1, 0) FROM cdr WHERE DATE(calldate) >= DATE(NOW()) - INTERVAL 30 DAY and dstchannel like '%trunkname%')
exten => _.,n,MYSQL(Fetch fetchid ${resultid} dynroute)
exten => _.,n,MYSQL(Clear ${resultid})
exten => _.,n,MYSQL(Disconnect ${connid})

Now, we'll modify the trunk to be dialed. Your code will look like something like this.
These are the trunks are called. In my example two trunks are called, and if they do not work, "outisbusy" is run which is "all circuits are busy" message. You'll notice in my example it has "..trunk, 1, $ ..." and followed by "... trunk, 2, $ ...".

exten => _.,n,Macro(dialout-trunk,1,${EXTEN},,off)
exten => _.,n,Macro(dialout-trunk,2,${EXTEN},,off)
exten => _.,n,Macro(outisbusy,)
;--== end of [outrt-2] ==--;

It IS possible that, although mine are in numeric order, yours may not be. So just make note of that when you see the modification example

Change the first line to look like this instead. Just note if your 1 or 2 values ​​are in a different order and adjust accordingly.

exten => _.,n,execif($["${dynroute}" = "0"]?macro(dialout-trunk,1,${EXTEN},,off))

This will tell the dialplan that IF the total time in SQL is NOT 1000 minutes (60000 seconds) then use the first trunk.
IF the dialplan is told by SQL IS more than 1000 minutes, the dialplan will execute the remaining trunk (s) in the order you have them.

Now your code will look something like this when completed:
username / password are you MYsql credentials
TRUNKNAME = trunk name in GUI (can be partial).
Change "30" to be the number of days you want to look back on
Change 60000 to be the number of seconds you want to limit to

[outrt-2] ; 1000minlimitroute
include => outrt-2-custom
exten => _.,1,Macro(user-callerid,LIMIT,EXTERNAL,)
exten => _.,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})})
exten => _.,n,Set(_NODEST=)
exten => _.,n,Gosub(sub-record-check,s,1(out,${EXTEN},))

exten => _.,n,MYSQL(Connect connid localhost username password asteriskcdrdb)
exten => _.,n,MYSQL(Query resultid ${connid} SELECT if(sum(duration) < 60000, 1, 0) FROM cdr WHERE DATE(calldate) >= DATE(NOW()) - INTERVAL 30 DAY and dstchannel like '%trunkname%')
exten => _.,n,MYSQL(Fetch fetchid ${resultid} dynroute)
exten => _.,n,MYSQL(Clear ${resultid})
exten => _.,n,MYSQL(Disconnect ${connid})

exten => _.,n,execif($["${dynroute}" = "0"]?macro(dialout-trunk,1,${EXTEN},,off))

exten => _.,n,Macro(dialout-trunk,2,${EXTEN},,off)   
exten => _.,n,Macro(outisbusy,)
;--== end of [outrt-2] ==--;

From the command line type
asterisk -rx 'core reload'

Test and try!

Please note that once this code is in place, you can't change the trunk order in the GUI for this outbound route. This code will over-ride the GUI for that. So if you DO need to make changes, you can make the change the GUI, but you will need to re-perform these custom modifications for it to work.

Anyone who is trying this, and you should only do this in a test server if you aren't familiar, but if you DO have problems and its messing up your PBX, in the "extensions_override_issabelpbx.conf" file. You can just erase these changes and save / reload the file and your pBX will revert to its original config for this rule.