Individual daily limit for outgoing calls (restriction of paid directions)

In this article I want to tell how we solved a non-typical problem on FreePBX. By the definition of “not typical” I mean that it cannot be solved by standard means, without additional tools.

Background


There is a group of employees that is engaged in calling customers. In order to save on outgoing calls, different phone numbers are used for different directions. This can be easily solved with the help of templates (masks) of numbers in Outbound Routes. But part of the directions, for example, calls to mobile, remains to be paid. So that at the end of the month the company’s bill 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. Deny outgoing calls to certain destinations when the limit is exhausted. Upon reaching the threshold value:> 50%,> 90% and> 100% send a notification to the employee’s email. If an employee has not completely exhausted his daily limit during the day, the balance should go to the next day.

Getting started


First you need to determine which numbers we restrict dialing to. In our case, these are mobile operators of Kazakhstan. We find the corresponding Wikipedia article and try to create number templates (masks). Since FreePBX does not have the ability to use full-fledged regular expressions , we managed to pack 23 possible prefixes into 3 patterns:
  • 870 [5780-2] XXXXXXX
  • 877 [15-8] XXXXXXX
  • 8747XXXXXXX

Create the appropriate entries in Outbound Routes. In this example, we open the directions for extension 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 the configuration files each time, we find the blocks we need and move to the file:
/etc/asterisk/extensions_custom.conf
in which FreePBX does not crawl.

The block is as follows:

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 syntax of Asterisk’s configs, you can skip the previous two steps and create the blocks you need yourself.

Now you can delete the previously created Outbound Routes, the permissive rules we need are 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 to the mail, we need to store all this information somewhere. A better choice would be to use a database. Here we had two options:
  • use the existing asterisk database, and add the required fields to the users table;
  • create your database with tables of the desired structure.

The choice fell on option number 2, and it turned out something like 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 individual subscriber limit (in seconds), is set at a time;
  • mobile_limit contains the current limit for the current day, taking into account unspent minutes;
  • mobile_limit_flag determines what threshold the user has exceeded the limit ( 0 - <50% , 1 -> 50 and <90% , 2 -> 90% and <100% , 3 -> 100%);

Let's create Vasya Pupkin, with extension 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 crown) the script checks how much 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 appropriate one and an email message is sent;
  • if the subscriber is on the threshold of 3 (the limit is completely exhausted), a corresponding notification is sent by 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 form an XML file of the following form:

XML


Since we created three masks for accessing mobile numbers, there will be three permission blocks for each extension. In XML, we specify the line numbers of the beginning and end of each of these blocks. We comment on them using the following code:

Block Comment 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
Twitching on a schedule, for example, every 5 minutes. Bonus magnificent 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 mobile_limit_flag to the default value of 0 and uncomment 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 resolve possible disputes, write to the log data on the change of the limit threshold:

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 crooked solution to the problem. In the version of the script 2.0, we will dynamically generate permission blocks, which will allow us to use the system more flexibly with any changes.

Also popular now: