def updateUserInformation():
#Get existing information:
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "SELECT * from tarifica_extension"
am.cursor.execute(sql)
existing_users = am.cursor.fetchall()
#Get current providers on asterisk's db
todays_users = am.getUserInformation()
new_users = []
for today_u in todays_users:
existing = False
for ex_u in existing_users:
if today_u['extension'] == ex_u['extension_number']:
#Same user, we just update its name:
if today_u['name'] != ex_u['name']:
print "User", ex_u[
'name'], 'has changed. New name is', today_u['name']
ex_u['name'] = today_u['name']
existing = True
if not existing:
#Didn't exist before, so we create one:
new_user = (
today_u['extension'],
today_u['name'],
)
new_users.append(new_user)
print "New user", today_u['name'], "found with extension", today_u[
'extension']
#Now, we update all previously existing users and save the new ones
am.connect('nextor_tarificador')
for ex_u in existing_users:
sql = "UPDATE tarifica_extension \
SET name = %s \
WHERE id = %s"
am.cursor.execute(sql, (ex_u['name'], ex_u['id']))
print "Extension", ex_u['name'], "updated."
sql = "INSERT INTO tarifica_extension \
(extension_number, name) \
VALUES(%s, %s)"
totalRowsSaved = am.cursor.executemany(sql, new_users)
am.db.commit()
print "----------------------------------------"
print totalRowsSaved, "new extensions saved."
def updateUserInformation():
#Get existing information:
am = AsteriskMySQLManager()
am.connect('nextor_tarificador');
sql = "SELECT * from tarifica_extension"
am.cursor.execute(sql)
existing_users = am.cursor.fetchall()
#Get current providers on asterisk's db
todays_users = am.getUserInformation()
new_users = []
for today_u in todays_users:
existing = False
for ex_u in existing_users:
if today_u['extension'] == ex_u['extension_number']:
#Same user, we just update its name:
if today_u['name'] != ex_u['name']:
print "User", ex_u['name'],'has changed. New name is',today_u['name']
ex_u['name'] = today_u['name']
existing = True
if not existing:
#Didn't exist before, so we create one:
new_user = (
today_u['extension'],
today_u['name'],
)
new_users.append(new_user)
print "New user", today_u['name'], "found with extension", today_u['extension']
#Now, we update all previously existing users and save the new ones
am.connect('nextor_tarificador');
for ex_u in existing_users:
sql = "UPDATE tarifica_extension \
SET name = %s \
WHERE id = %s"
am.cursor.execute(sql, (ex_u['name'], ex_u['id']))
print "Extension", ex_u['name'], "updated."
sql = "INSERT INTO tarifica_extension \
(extension_number, name) \
VALUES(%s, %s)"
totalRowsSaved = am.cursor.executemany(sql, new_users)
am.db.commit()
print "----------------------------------------"
print totalRowsSaved, "new extensions saved."
class CallCostAssigner:
am = None
def __init__(self):
self.am = AsteriskMySQLManager()
def getAllBundlesFromDestinationGroup(self, destination_group_id):
self.am.connect('nextor_tarificador')
sql = "SELECT * from tarifica_bundle \
WHERE destination_group_id = %s \
ORDER BY priority ASC"
self.am.cursor.execute(sql, (destination_group_id,))
return self.am.cursor.fetchall()
def getBundlesFromProvider(self, provider_id):
self.am.connect('nextor_tarificador')
sql = "SELECT * from tarifica_bundle \
LEFT JOIN tarifica_destinationgroup \
ON tarifica_bundle.destination_group_id = tarifica_destinationgroup.id \
WHERE tarifica_destinationgroup.provider_id = %s"
self.am.cursor.execute(sql, (provider_id,))
return self.am.cursor.fetchall()
def getAllConfiguredProviders(self):
self.am.connect('nextor_tarificador')
sql = "SELECT * from tarifica_provider WHERE is_configured = %s"
self.am.cursor.execute(sql, (True,))
return self.am.cursor.fetchall()
def getAllDestinationGroupsFromProvider(self, provider_id):
self.am.connect('nextor_tarificador')
sql = "SELECT * from tarifica_destinationgroup JOIN tarifica_destinationname \
ON tarifica_destinationgroup.destination_name_id = tarifica_destinationname.id \
WHERE provider_id = %s \
ORDER BY CHAR_LENGTH(tarifica_destinationgroup.prefix) DESC"
self.am.cursor.execute(sql, (provider_id,))
return self.am.cursor.fetchall()
def getTariffMode(self, tariffMode_id):
self.am.connect('nextor_tarificador')
sql = "SELECT * from tarifica_tariffmode WHERE id = %s"
self.am.cursor.execute(sql, (tariffMode_id,))
return self.am.cursor.fetchone()
def getDailyAsteriskCalls(self, date, dryrun = False):
"""
Gets calls from specified date and saves them with their cost.
If dryrun is set to True, then the calls that could not be
assigned a cost are the only ones saved.
"""
# Primero revisamos si es el día de corte.
self.resetBundles(date)
print "Script running on day "+getStartOfDay(date)+"."
# Obtenemos las extensiones configuradas:
extensions = self.am.getUserInformation()
self.am.connect('asteriskcdrdb')
sql = "SELECT * from cdr WHERE callDate > %s AND callDate < %s AND lastapp = %s \
AND disposition = %s"
self.am.cursor.execute(sql,
(getStartOfDay(date), getEndOfDay(date), 'Dial', 'ANSWERED')
)
# Iteramos sobre las llamadas:
totalOutgoingCalls = 0
dailyCallDetail = []
unsavedDailyCallDetail = []
totalCallsFound = 0
for row in self.am.cursor.fetchall():
totalCallsFound += 1
outgoing = True
for ext in extensions:
if row['dst'] == ext['extension']:
outgoing = False
if outgoing:
print "Outgoing call found, assigning cost..."
totalOutgoingCalls += 1
callCostInfo = self.assignCost(row)
if callCostInfo['save']:
dailyCallDetail.append(callCostInfo['callInfo'])
else:
unsavedDailyCallDetail.append(callCostInfo['callInfo'])
print "----------------------------------------------------"
print "Total calls found:", totalCallsFound
print "----------------------------------------------------"
self.saveCalls(dailyCallDetail)
print "Total outgoing calls configured:", len(dailyCallDetail)
print "----------------------------------------------------"
self.saveUnconfiguredCalls(unsavedDailyCallDetail)
print "Total outgoing calls not configured:", len(unsavedDailyCallDetail)
return {
'total_calls_not_saved': len(unsavedDailyCallDetail),
'total_calls_saved': len(dailyCallDetail),
}
def resetBundles(self, date):
for provider in self.getAllConfiguredProviders():
# We get all bundles and check if they should be reset, based on their dates
for bundle in self.getBundlesFromProvider(provider['id']):
print "Date from provider reset check:",provider['period_end']
print "Date from bundle reset check:",date.day
if provider['period_end'] == date.day:
print "End date of provider", provider['name']
bundle['usage'] = 0
self.saveBundleUsage(bundle['id'], 0)
print "Bundle",bundle['name'],"reset."
#Checking that bundles have indeed been reset:
def saveBundleUsage(self, bundle_id, usage):
self.am.connect('nextor_tarificador')
sql = "UPDATE tarifica_bundle SET tarifica_bundle.usage = %s \
WHERE tarifica_bundle.id = %s"
self.am.cursor.execute(sql, (usage, bundle_id))
self.am.db.commit()
sql = "SELECT * FROM tarifica_bundle WHERE tarifica_bundle.id = %s"
self.am.cursor.execute(sql, (bundle_id,))
bundle = self.am.cursor.fetchone()
print "Bundle",bundle['name'],"usage is now",bundle['usage']
def saveCalls(self, calls):
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_call \
(dialed_number, extension_number, pinset_number, duration, cost, date, \
destination_group_id, provider_id, asterisk_unique_id) \
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)"
self.am.cursor.executemany(sql, calls)
return self.am.db.commit()
def saveUnconfiguredCalls(self, calls):
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_unconfiguredcall \
(dialed_number, extension_number, pinset_number, duration, provider, date, asterisk_unique_id) \
VALUES(%s, %s, %s, %s, %s, %s, %s)"
self.am.cursor.executemany(sql, calls)
return self.am.db.commit()
def assignCost(self, call):
#Obtenemos la informacion necesaria:
callInfoList = call['lastdata'].split('/')
cost = 0
provider_id = 0
destination_group_id = 0
save = False
dialedNoForProvider = call['dst']
separated = []
for a in callInfoList:
if len(a.split(',')) == 1:
#No fue, hay que separar con pipes
separated = separated + a.split('|')
else:
separated = separated + a.split(',')
try:
dialedNoForProvider = separated[2]
except IndexError:
print "No dialed number present! Skipping..."
print "Call from", call['src'],"with pinset", call['accountcode']
configuedProviders = self.getAllConfiguredProviders()
if len(configuedProviders) == 0:
print "No providers configured, ending..."
for prov in configuedProviders:
# Extraemos la troncal por la cual se fue la llamada:
try:
provider = callInfoList[1]
except IndexError:
print "No trunk information present, skipping..."
break
if provider.count(prov['asterisk_channel_id']) > 0:
print "Provider found:",prov['name']
provider_id = prov['id']
destinations = self.getAllDestinationGroupsFromProvider(prov['id'])
if len(destinations) == 0:
print "No destination groups configured: cannot proceed."
continue
costAssigned = False
for d in destinations:
if costAssigned:
break
try:
pos = dialedNoForProvider.index(d['prefix'])
print "Call prefix fits into destination group",d['name']
except ValueError, e:
pos = None
if pos is None or pos != 0:
continue
# Se encontro el prefijo!
numberDialed = dialedNoForProvider[pos + len(d['prefix']):]
# print "Number called according to trunk:",numberDialed
destination_group_id = d['id']
bundles = self.getAllBundlesFromDestinationGroup(d['id'])
appliedToBundle = False
seconds_left_to_count = call['billsec']
if len(bundles) > 0:
for b in bundles:
#Si ya se aplicó, salimos
if appliedToBundle:
print "Already has been applied to a bundle"
break
# La llamada debe estar dentro de un billing period del proveedor
# Si además en ese billing period hay un paquete que lo abarque,
# Entonces se aplica.
today = datetime.datetime.today()
provider_period_start = datetime.date(
year=today.year, month=today.month, day=prov['period_end']
)
if provider_period_start > call['calldate'].date():
#Entonces el periodo es el anterior:
provider_period_end = provider_period_start - relativedelta(days=1)
provider_period_start = provider_period_start - relativedelta(months=1)
else:
provider_period_end = provider_period_start + relativedelta(months=1)
provider_period_end = provider_period_end - relativedelta(days=1)
#Now that we have which billing period the call falls into, we check that
#Bundle should start before or the same day of provider's billing period
if b['start_date'] <= provider_period_start:
#Bundle should end after or the same day of provider's billing period
if b['end_date'] >= provider_period_end:
if b['usage'] is None:
b['usage'] = 0
#Si el bundle actual ya se agotó, seguimos
if b['usage'] == b['amount']:
print "Bundle",b['name'],"usage has reached its limit."
continue
#Si no, agregamos la llamada al uso del bundle
print "Billing with bundle",b['name']
usage = b['usage']
print "Usage before: ", usage
if self.getTariffMode(b['tariff_mode_id'])['name'] == 'Call':
usage += 1
appliedToBundle = True
else:
#Vemos cuantos minutos quedan:
call_minutes = ceil(call['billsec'] / 60)
minutes_available = b['amount'] - b['usage']
if minutes_available < call_minutes:
#No puede pasarse de los minutos disponibles
usage += minutes_available
#Ponemos que no fue completamente billeada:
appliedToBundle = False
#Revisamos cuanto queda por tarificar de la llamada
call['billsec'] = call['billsec'] - (call_minutes*60)
if call['billsec'] < 0:
call['billsec'] = 0
print "Call would overstep remaining minutes. Billed in bundle", minutes_available," minutes from",call_minutes
else:
#Todo bien, la guardamos normal:
usage += call_minutes
appliedToBundle = True
print "Usage after: ", usage
self.saveBundleUsage(b['id'], usage)
costAssigned = True
save = True
print "Call applied to bundle",b['name']
else:
print "End Date of Bundle is less than the end of billing period!"
else:
print "Start Date of Bundle is less than the end of billing period!"
if not appliedToBundle:
# Y no se aplicó a ninguno, por tanto
# calculamos el costo con la tarifa base:
print "Billing with base tariff..."
print "Base tariff bills by interval of",d['billing_interval'],"seconds."
print "Call Duration:",call['billsec']
intervals = ceil(call['billsec'] / d['billing_interval'])
print "Billed intervals:",intervals
per_second_tariff = float(d['minute_fee']) / 60
per_interval_tariff = per_second_tariff * d['billing_interval']
print "Tariff per interval:",per_interval_tariff
cost = ( intervals * per_interval_tariff ) + float(d['connection_fee'])
print "Calculated cost:", cost
save = True
costAssigned = True
if save:
print "Saving call..."
return {
'callInfo':
(
dialedNoForProvider,
call['src'],
call['accountcode'],
call['billsec'],
cost,
call['calldate'],
destination_group_id,
provider_id,
call['uniqueid']
),
'save': True
}
else:
print "Saving unconfigured call..."
return {
'callInfo':
(
dialedNoForProvider,
call['src'],
call['accountcode'],
call['billsec'],
callInfoList[1],
call['calldate'],
call['uniqueid']
),
'save': False
}
