Individual daily limit for outgoing calls (limitation of paid directions)
In this article I want to tell how we solved a non-typical task on FreePBX. By the definition of “non-typical,” I mean that it cannot be solved by standard means, without additional tools.
There is a group of employees, which is engaged in calling customers. In order to save on outgoing calls, different phone numbers are used for different directions. This is quietly solved with the help of patterns (masks) of numbers in Outbound Routes. But part of the directions, for example, calls to mobile, remains paid. So that at the end of the month the company’s account for telephone services does not exceed XXX $, it is necessary to strictly control and, if necessary, limit the corresponding directions of calls.
Set an individual daily limit for a group of managers. Bar outgoing calls to certain destinations when the limit is reached. When the threshold is reached:> 50%,> 90% and> 100%, send the corresponding notification to the employee's email. If the employee during the day has not fully exhausted his daily limit, the balance should be transferred to the next day.
First you need to determine which numbers we limit dialing. In our case, these are mobile operators in Kazakhstan. We find the corresponding article in Wikipedia and try to create templates (masks) of numbers. Since FreePBX does not have the ability to use full-fledged regular expressions , we managed to package the 23 possible prefixes into 3 templates:
Create corresponding entries in Outbound Routes. In this example, we open the directions for the internal number 2055:
')
We do this so that the corresponding rules are created in the configuration file:
Since when editing and applying settings in FreePBX, the system overwrites configuration files every time, we find the blocks we need and move to the file:
The block of the following type:
If you are friends with the Asterisk config file syntax, you can skip the two previous steps and create the blocks you need yourself.
Now we can delete the previously created Outbound Routes, we need the enabling rules now contained in extensions_custom.conf. Thus, we allowed employees to call in these areas. Further more.
Since the limit is individual, and we need to send notifications by mail, it is necessary to store all this information somewhere. The best choice would be to use the database. Here we had two options:
The choice fell on option number 2, and it turned out about the following:
Description of the main parameters:
Let's create Vasya Pupkin, with internal number 2055 already known to us.
We proceed to the formation of the main system, the logic is as follows:
To store the positions of the blocks of the corresponding internal numbers, we create the following XML file:
Since we have created three masks for accessing mobile numbers, there will be three permitting blocks for each extension. In XML, we specify the line numbers of the beginning and end of each of these blocks. We comment them with the following code:
And actually the main brains:
At the end of the working day, we add an unused limit:
We reset the mobile_limit_flag to the default value of 0 and unlock all the blocks:
To solve possible disputable situations, we write to the log data on the change of the threshold limit:
Here is acrooked solution to the problem. In version 2.0 of the script, we will dynamically form permitting blocks, which will allow more flexible use of the system, with any changes.
Prehistory
There is a group of employees, which is engaged in calling customers. In order to save on outgoing calls, different phone numbers are used for different directions. This is quietly solved with the help of patterns (masks) of numbers in Outbound Routes. But part of the directions, for example, calls to mobile, remains paid. So that at the end of the month the company’s account for telephone services does not exceed XXX $, it is necessary to strictly control and, if necessary, limit the corresponding directions of calls.
Task
Set an individual daily limit for a group of managers. Bar outgoing calls to certain destinations when the limit is reached. When the threshold is reached:> 50%,> 90% and> 100%, send the corresponding notification to the employee's email. If the employee during the day has not fully exhausted his daily limit, the balance should be transferred to the next day.
Getting Started
First you need to determine which numbers we limit dialing. In our case, these are mobile operators in Kazakhstan. We find the corresponding article in Wikipedia and try to create templates (masks) of numbers. Since FreePBX does not have the ability to use full-fledged regular expressions , we managed to package the 23 possible prefixes into 3 templates:
- 870 [5780-2] XXXXXXX
- 877 [15-8] XXXXXXX
- 8747XXXXXXX
Create corresponding entries in Outbound Routes. In this example, we open the directions for the internal number 2055:
')
We do this so that the corresponding rules are created in the configuration file:
/etc/asterisk/extensions_additional.conf Since when editing and applying settings in FreePBX, the system overwrites configuration files every time, we find the blocks we need and move to the file:
/etc/asterisk/extensions_custom.conf in which FreePBX does not climb.The block of the following type:
Outbound routes
exten => _877[15-8]XXXXXXX,1,Macro(user-callerid,LIMIT,EXTERNAL,) exten => _877[15-8]XXXXXXX/2055,1,Macro(user-callerid,LIMIT,EXTERNAL,) exten => _877[15-8]XXXXXXX/2055,n,ExecIf($[ "${CALLEE_ACCOUNCODE}" != "" ] ?Set(CDR(accountcode)=${CALLEE_ACCOUNCODE})) exten => _877[15-8]XXXXXXX/2055,n,Set(MOHCLASS=${IF($["${MOHCLASS}"=""]?default:${MOHCLASS})}) exten => _877[15-8]XXXXXXX/2055,n,ExecIf($["${KEEPCID}"!="TRUE" & ${LEN(${TRUNKCIDOVERRIDE})}=0]?Set(TRUNKCIDOVERRIDE=<7123456789>)) exten => _877[15-8]XXXXXXX/2055,n,Set(_NODEST=) exten => _877[15-8]XXXXXXX/2055,n,Gosub(sub-record-check,s,1(out,${EXTEN},)) exten => _877[15-8]XXXXXXX/2055,n,Macro(dialout-trunk,10,${EXTEN},,off) exten => _877[15-8]XXXXXXX/2055,n,Macro(outisbusy,) If you are friends with the Asterisk config file syntax, you can skip the two previous steps and create the blocks you need yourself.
Now we can delete the previously created Outbound Routes, we need the enabling rules now contained in extensions_custom.conf. Thus, we allowed employees to call in these areas. Further more.
Since the limit is individual, and we need to send notifications by mail, it is necessary to store all this information somewhere. The best choice would be to use the database. Here we had two options:
- use the existing asterisk database, and add the fields we need to the users table;
- create your own database with tables of the desired structure.
The choice fell on option number 2, and it turned out about the following:
SQL Create table
CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ' ', `extension` varchar(5) CHARACTER SET utf8 DEFAULT '000' COMMENT ' ', `mobile_limit_flag` int(11) DEFAULT '0' COMMENT ' ', `mobile_limit` int(11) DEFAULT '0' COMMENT ' ', `base_mobile_limit` int(11) DEFAULT NULL COMMENT ' ', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci Description of the main parameters:
- base_mobile_limit stores the subscriber's individual limit (in seconds), is set at a time;
- mobile_limit contains the current limit for the current day, taking into account unused minutes;
- mobile_limit_flag determines what threshold the user has reached for the limit to be exhausted ( 0 - <50% , 1 -> 50 and <90% , 2 -> 90% and <100% , 3 -> 100%);
Let's create Vasya Pupkin, with internal number 2055 already known to us.
We proceed to the formation of the main system, the logic is as follows:
- according to the schedule (by krone) the script checks how many each subscriber has spoken in the directions we need;
- if the subscriber has crossed the threshold of 0.1 or 2, the mobile_limit_flag parameter changes to the corresponding one and a message is sent to the email;
- if the subscriber is on the verge of 3 (the limit is fully exhausted), the corresponding notification is sent to the email, the corresponding block is commented in the configuration file, dialplan reload is performed.
To store the positions of the blocks of the corresponding internal numbers, we create the following XML file:
XML
<?xml version="1.0" encoding="UTF-8" ?> <bocks> <!-- BLOCKS START --> <block number="2055"> <element first="4" last="11"/> <element first="117" last="124"/> <element first="230" last="237"/> </block> <block number="2066"> <element first="14" last="21"/> <element first="127" last="134"/> <element first="240" last="247"/> </block> <block number="2077"> <element first="24" last="31"/> <element first="137" last="144"/> <element first="250" last="257"/> </block> <bocks> Since we have created three masks for accessing mobile numbers, there will be three permitting blocks for each extension. In XML, we specify the line numbers of the beginning and end of each of these blocks. We comment them with the following code:
Block commenting function
def commentBlocks(numb): import xml.etree.cElementTree as ET tree = ET.ElementTree(file='conf.xml') root = tree.getroot() f = open(r'extensions_custom.conf') lines = f.readlines() f.close() for elem in tree.iterfind('block[@number="'+numb+'"]/element'): lines[int(elem.get('first'))-2] = ";--\n" lines[int(elem.get('last'))] = "--;\n" f = open(r'extensions_custom.conf','w') f.writelines(lines) f.close() And actually the main brains:
Main script
Twitches on schedule, for example, every 5 minutes. Bonus gorgeous SQL query and divine code.
# import send_email import flags print ('###########START_MOBILE_LIMIT############') import pymysql mainconn = pymysql.connect(host='10.10.2.1', user='user', passwd='password', db='asteriskcdrdb', charset='utf8') maincur = mainconn.cursor() maincur.execute("""SELECT SUM(billsec) AS sec, src FROM cdr WHERE disposition = 'ANSWERED' AND (dst LIKE '8700%' OR dst LIKE '8701%' OR dst LIKE '8702%' OR dst LIKE '8705%' OR dst LIKE '8707%' OR dst LIKE '8708%' OR dst LIKE '8747%' OR dst LIKE '8771%' OR dst LIKE '8775%' OR dst LIKE '8776%' OR dst LIKE '8777%' OR dst LIKE '8778%') AND DATE(calldate) = DATE(CURDATE()) AND src in (2055,2066,2077) GROUP BY src;""") row = maincur.fetchone() print ('ROW COUNT: ' + str(self.maincur.rowcount)) while row is not None: #row[1] - #row[0] - # flags.UpdateUserCurrentLimit(str(row[1]), str(row[0])) per = row[0] * 100 / flags.checkUserLimit(row[1]) flag = flags.checkFlag(row[1]) # , manager_mail = send_email.getEmail(row[1]) # % print (row[1] + ' (' + str(round(per,0)) + '%): ' + str(row[0])) # if per >= 50 and per < 90: message = 'Nomer ' + row[1] + ', limit ischerpan na ' + str(round(per, 0)) + '%' if flag == 0: print ('go email to ' + send_email.getEmail(row[1])) send_email.send_message(manager_mail, message) flags.changeFlag(row[1], 1) flags.insertLog(row[1], per) print (message) elif per > 90 and per < 100: message = 'Nomer ' + row[1] + ', limit ischerpan na ' + str(round(per, 0)) + '%' if flag == 1: print ('go email to ' + send_email.getEmail(row[1])) send_email.send_message(manager_mail, message) flags.changeFlag(row[1], 2) flags.insertLog(row[1], per) print (message) elif per >= 100: message = 'Nomer ' + row[1] + ', limit polnostiu ischerpan' if flag != 3: print ('go email to ' + send_email.getEmail(row[1])) send_email.send_message(manager_mail, message) flags.changeFlag(row[1], 3) flags.insertLog(row[1], per) # flags.commentBlocks(str(row[1])) import subprocess # subprocess.call(['./dialplan_reload.sh']) print (message) row = maincur.fetchone() maincur.close() mainconn.close() print ('############END_MOBILE_LIMIT#############') At the end of the working day, we add an unused limit:
AddUnusedLimit
def AddUnusedLimit(ext): conn = pymysql.connect(host='10.10.2.2', user='user', passwd='password', db='crm', charset='utf8') cur = conn.cursor() cur.execute (""" UPDATE users SET mobile_limit=base_mobile_limit+mobile_limit WHERE extension=%s """, (ext)) conn.commit() print('changed', cur.rowcount) cur.close() conn.close() We reset the mobile_limit_flag to the default value of 0 and unlock all the blocks:
uncommentBlocks
def uncommentBlocks(): import xml.etree.cElementTree as ET tree = ET.ElementTree(file='conf.xml') root = tree.getroot() for elem in tree.iterfind('block/element'): first = int(elem.get('first')) last = int(elem.get('last')) lines[first-2] = "\n" lines[last] = "\n" f = open(r'/etc/asterisk/extensions_custom.conf') lines = f.readlines() f.close() ############################################ cur.execute (""" UPDATE users SET mobile_limit_flag=%s """, (0)) To solve possible disputable situations, we write to the log data on the change of the threshold limit:
LOG
# def insertLog(ext, per): import pymysql conn = pymysql.connect(host='10.10.2.1', user='user', passwd='password', db='crm', charset='utf8') cur = conn.cursor() cur.execute (""" INSERT INTO mobile_limit (extension, percent) VALUES (%s, %s) """, (ext, per)) conn.commit() print('insert', cur.rowcount) cur.close() conn.close() Here is a
Source: https://habr.com/ru/post/308086/
All Articles