def resetBundleUsage():
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "UPDATE tarifica_bundle SET tarifica_bundle.usage = %s"
am.cursor.execute(sql, (0,))
am.db.commit()
return am.db.close()
def deleteAllUnconfiguredCalls():
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "DELETE FROM tarifica_unconfiguredcall"
am.cursor.execute(sql, ())
am.db.commit()
return am.db.close()
def deleteAllPinsetDailyDetail():
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "DELETE FROM tarifica_pinsetdailydetail"
am.cursor.execute(sql, ())
am.db.commit()
return am.db.close()
def deleteLastImportResults():
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "DELETE FROM tarifica_importresults"
am.cursor.execute(sql, ())
am.db.commit()
return am.db.close()
def deleteAllProviderDestinationDetail():
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "DELETE FROM tarifica_providerdestinationdetail"
am.cursor.execute(sql, ())
am.db.commit()
return am.db.close()
def saveImportResults(calls_saved, calls_not_saved):
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_importresults(calls_saved, calls_not_saved) \
VALUES(%s, %s)"
am.cursor.execute(sql, (calls_saved, calls_not_saved))
am.db.commit()
return am.db.close()
def saveImportFinishStatus():
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "UPDATE tarifica_userinformation \
SET tarifica_userinformation.is_first_import_finished = %s, \
tarifica_userinformation.test_run_in_progress = %s, \
tarifica_userinformation.processing_in_progress = %s"
am.cursor.execute(sql, (True, False, False))
am.db.commit()
return am.db.close()
def updatePinsetInformation():
#Get existing information:
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "SELECT * from tarifica_pinset"
am.cursor.execute(sql)
existing_pinsets = am.cursor.fetchall()
#Get current providers on asterisk's db
todays_pinsets = am.getPinsetInformation()
print todays_pinsets
new_pinsets = []
for today_u in todays_pinsets:
existing = False
for ex_u in existing_pinsets:
if today_u == ex_u['pinset_number']:
existing = True
if not existing:
#Didn't exist before, so we create one:
new_pinset = (today_u, )
new_pinsets.append(new_pinset)
print "New pinset", today_u, "found."
#Now, we update all previously existing pinsets and save the new ones
am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_pinset \
(pinset_number) VALUES(%s)"
totalRowsSaved = am.cursor.executemany(sql, new_pinsets)
am.db.commit()
print "----------------------------------------"
print totalRowsSaved, "new pinsets saved."
def updatePinsetInformation():
#Get existing information:
am = AsteriskMySQLManager()
am.connect('nextor_tarificador');
sql = "SELECT * from tarifica_pinset"
am.cursor.execute(sql)
existing_pinsets = am.cursor.fetchall()
#Get current providers on asterisk's db
todays_pinsets = am.getPinsetInformation()
print todays_pinsets
new_pinsets = []
for today_u in todays_pinsets:
existing = False
for ex_u in existing_pinsets:
if today_u == ex_u['pinset_number']:
existing = True
if not existing:
#Didn't exist before, so we create one:
new_pinset = (
today_u,
)
new_pinsets.append(new_pinset)
print "New pinset", today_u, "found."
#Now, we update all previously existing pinsets and save the new ones
am.connect('nextor_tarificador');
sql = "INSERT INTO tarifica_pinset \
(pinset_number) VALUES(%s)"
totalRowsSaved = am.cursor.executemany(sql, new_pinsets)
am.db.commit()
print "----------------------------------------"
print totalRowsSaved, "new pinsets saved."
def updateTrunkInformation():
#Get existing information:
am = AsteriskMySQLManager()
am.connect('nextor_tarificador')
sql = "SELECT * from tarifica_provider"
am.cursor.execute(sql)
existing_providers = am.cursor.fetchall()
#Get current providers on asterisk's db
todays_providers = am.getTrunkInformation()
new_providers = []
for today_p in todays_providers:
existing = False
for ex_p in existing_providers:
if today_p['trunkid'] == ex_p['asterisk_id']:
#Same provider, we just update its name:
if today_p['name'] != ex_p['asterisk_name']:
print "Provider", ex_p['name'], '(saved as', ex_p[
'asterisk_name'], ') has changed. New name is', today_p[
'name']
ex_p['asterisk_name'] = today_p['name']
existing = True
if not existing:
#Didn't exist before, so we create one:
new_provider = (
today_p['trunkid'],
today_p['name'],
today_p['name'],
today_p['tech'],
today_p['channelid'],
)
new_providers.append(new_provider)
print "New provider found:", today_p['name']
#Now, we update all previously existing providers and save the new ones
am.connect('nextor_tarificador')
for ex_p in existing_providers:
sql = "UPDATE tarifica_provider \
SET asterisk_name = %s \
WHERE id = %s"
am.cursor.execute(sql, (ex_p['asterisk_name'], ex_p['id']))
print "Provider", ex_p['name'], "updated."
sql = "INSERT INTO tarifica_provider \
(asterisk_id, asterisk_name, name, provider_tech, asterisk_channel_id) \
VALUES(%s, %s, %s, %s, %s)"
totalRowsSaved = am.cursor.executemany(sql, new_providers)
am.db.commit()
print "----------------------------------------"
print totalRowsSaved, "new providers 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."
def updateTrunkInformation():
#Get existing information:
am = AsteriskMySQLManager()
am.connect('nextor_tarificador');
sql = "SELECT * from tarifica_provider"
am.cursor.execute(sql)
existing_providers = am.cursor.fetchall()
#Get current providers on asterisk's db
todays_providers = am.getTrunkInformation()
new_providers = []
for today_p in todays_providers:
existing = False
for ex_p in existing_providers:
if today_p['trunkid'] == ex_p['asterisk_id']:
#Same provider, we just update its name:
if today_p['name'] != ex_p['asterisk_name']:
print "Provider", ex_p['name'],'(saved as',ex_p['asterisk_name'],') has changed. New name is',today_p['name']
ex_p['asterisk_name'] = today_p['name']
existing = True
if not existing:
#Didn't exist before, so we create one:
new_provider = (
today_p['trunkid'],
today_p['name'],
today_p['name'],
today_p['tech'],
today_p['channelid'],
)
new_providers.append(new_provider)
print "New provider found:", today_p['name']
#Now, we update all previously existing providers and save the new ones
am.connect('nextor_tarificador');
for ex_p in existing_providers:
sql = "UPDATE tarifica_provider \
SET asterisk_name = %s \
WHERE id = %s"
am.cursor.execute(sql, (ex_p['asterisk_name'], ex_p['id']))
print "Provider", ex_p['name'], "updated."
sql = "INSERT INTO tarifica_provider \
(asterisk_id, asterisk_name, name, provider_tech, asterisk_channel_id) \
VALUES(%s, %s, %s, %s, %s)"
totalRowsSaved = am.cursor.executemany(sql, new_providers)
am.db.commit()
print "----------------------------------------"
print totalRowsSaved, "new providers 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."
def __init__(self): self.am = AsteriskMySQLManager()
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
}
class Digester:
am = None
def __init__(self):
self.am = AsteriskMySQLManager()
def getUserDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_extension.id as extension_number, \
DATE(tarifica_call.date) as date \
FROM tarifica_call JOIN tarifica_extension \
ON tarifica_call.extension_number = tarifica_extension.extension_number \
WHERE date > %s AND date < %s \
GROUP BY extension_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
d = (
row['extension_number'],
row['total_calls'],
row['total_seconds'],
row['cost'],
row['date']
)
print d
callDetail.append(d)
return callDetail
def saveUserDailyDetail(self, day):
callData = self.getUserDailyDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_userdailydetail \
(extension_id, total_calls, total_seconds, cost, date) \
VALUES(%s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "User Daily Detail saved:", totalRowsSaved
def getUserDestinationDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_extension.id as extension_number, \
tarifica_call.destination_group_id as destination_group_id \
FROM tarifica_call \
LEFT JOIN tarifica_extension ON \
tarifica_call.extension_number = tarifica_extension.extension_number \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.extension_number, tarifica_call.destination_group_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((
row['extension_number'],
row['total_calls'],
row['total_seconds'],
row['cost'],
row['destination_group_id'],
row['date']
))
return callDetail
def saveUserDestinationDetail(self, day):
callData = self.getUserDestinationDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_userdestinationdetail \
(extension_id, total_calls, total_seconds, cost, destination_group_id, date) \
VALUES(%s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "User Destination Detail saved:", totalRowsSaved
def getUserDestinationNumberDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_call.dialed_number as dialed_number, \
tarifica_extension.id as extension_number, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.prefix as prefix \
FROM tarifica_call LEFT JOIN tarifica_extension \
ON tarifica_call.extension_number = tarifica_extension.extension_number \
LEFT JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.dialed_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((
row['extension_number'],
row['total_calls'],
row['total_seconds'],
row['cost'],
row['prefix'],
row['dialed_number'],
row['date']
))
return callDetail
def saveUserDestinationNumberDetail(self, day):
callData = self.getUserDestinationNumberDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_userdestinationnumberdetail \
(extension_id, total_calls, total_seconds, cost, prefix, number, date) \
VALUES(%s, %s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "User Destination Number Detail saved:", totalRowsSaved
# -----------------TRUNKS---------------------
def getProviderDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.provider_id as provider \
FROM tarifica_call JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_destinationgroup.provider_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((
row['provider'],
row['cost'],
row['total_calls'],
row['total_seconds'],
row['date']
))
return callDetail
def saveProviderDailyDetail(self, day):
callData = self.getProviderDailyDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_providerdailydetail \
(provider_id, cost, total_calls, total_seconds, date) \
VALUES(%s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Provider Daily Detail saved:", totalRowsSaved
def getProviderDestinationDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_call.destination_group_id as destination_group_id, \
tarifica_destinationgroup.provider_id as provider \
FROM tarifica_call JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.destination_group_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((
row['provider'],
row['cost'],
row['total_calls'],
row['total_seconds'],
row['destination_group_id'],
row['date']
))
return callDetail
def saveProviderDestinationDetail(self, day):
callData = self.getProviderDestinationDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_providerdestinationdetail \
(provider_id, cost, total_calls, total_seconds, destination_group_id, date) \
VALUES(%s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Provider Destination Detail saved:", totalRowsSaved
# -----------------PINSETS-----------------
def getPinsetDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_pinset.id as pinset_number, \
DATE(tarifica_call.date) as date \
FROM tarifica_call JOIN tarifica_pinset \
ON tarifica_call.pinset_number = tarifica_pinset.pinset_number \
WHERE date > %s AND date < %s \
GROUP BY pinset_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
d = (
row['pinset_number'],
row['total_calls'],
row['total_seconds'],
row['cost'],
row['date']
)
print d
callDetail.append(d)
return callDetail
def savePinsetDailyDetail(self, day):
callData = self.getPinsetDailyDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_pinsetdailydetail \
(pinset_id, total_calls, total_seconds, cost, date) \
VALUES(%s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Pinset Daily Detail saved:", totalRowsSaved
def getPinsetDestinationDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_pinset.id as pinset_number, \
tarifica_call.destination_group_id as destination_group_id \
FROM tarifica_call \
LEFT JOIN tarifica_pinset ON \
tarifica_call.pinset_number = tarifica_pinset.pinset_number \
WHERE date > %s AND date < %s AND tarifica_call.pinset_number IS NOT NULL\
GROUP BY tarifica_call.pinset_number, tarifica_call.destination_group_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((
row['pinset_number'],
row['total_calls'],
row['total_seconds'],
row['cost'],
row['destination_group_id'],
row['date']
))
return callDetail
def savePinsetDestinationDetail(self, day):
callData = self.getPinsetDestinationDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_pinsetdestinationdetail \
(pinset_id, total_calls, total_seconds, cost, destination_group_id, date) \
VALUES(%s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Pinset Destination Detail saved:", totalRowsSaved
def getPinsetDestinationNumberDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_call.dialed_number as dialed_number, \
tarifica_pinset.id as pinset_number, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.prefix as prefix \
FROM tarifica_call LEFT JOIN tarifica_pinset \
ON tarifica_call.pinset_number = tarifica_pinset.pinset_number \
LEFT JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.dialed_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((
row['pinset_number'],
row['total_calls'],
row['total_seconds'],
row['cost'],
row['prefix'],
row['dialed_number'],
row['date']
))
return callDetail
def savePinsetDestinationNumberDetail(self, day):
callData = self.getPinsetDestinationNumberDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_pinsetdestinationnumberdetail \
(pinset_id, total_calls, total_seconds, cost, prefix, number, date) \
VALUES(%s, %s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Pinset Destination Number Detail saved:", totalRowsSaved
class Digester:
am = None
def __init__(self):
self.am = AsteriskMySQLManager()
def getUserDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_extension.id as extension_number, \
DATE(tarifica_call.date) as date \
FROM tarifica_call JOIN tarifica_extension \
ON tarifica_call.extension_number = tarifica_extension.extension_number \
WHERE date > %s AND date < %s \
GROUP BY extension_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
d = (row['extension_number'], row['total_calls'],
row['total_seconds'], row['cost'], row['date'])
print d
callDetail.append(d)
return callDetail
def saveUserDailyDetail(self, day):
callData = self.getUserDailyDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_userdailydetail \
(extension_id, total_calls, total_seconds, cost, date) \
VALUES(%s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "User Daily Detail saved:", totalRowsSaved
def getUserDestinationDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_extension.id as extension_number, \
tarifica_call.destination_group_id as destination_group_id \
FROM tarifica_call \
LEFT JOIN tarifica_extension ON \
tarifica_call.extension_number = tarifica_extension.extension_number \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.extension_number, tarifica_call.destination_group_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((row['extension_number'], row['total_calls'],
row['total_seconds'], row['cost'],
row['destination_group_id'], row['date']))
return callDetail
def saveUserDestinationDetail(self, day):
callData = self.getUserDestinationDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_userdestinationdetail \
(extension_id, total_calls, total_seconds, cost, destination_group_id, date) \
VALUES(%s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "User Destination Detail saved:", totalRowsSaved
def getUserDestinationNumberDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_call.dialed_number as dialed_number, \
tarifica_extension.id as extension_number, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.prefix as prefix \
FROM tarifica_call LEFT JOIN tarifica_extension \
ON tarifica_call.extension_number = tarifica_extension.extension_number \
LEFT JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.dialed_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append(
(row['extension_number'], row['total_calls'],
row['total_seconds'], row['cost'], row['prefix'],
row['dialed_number'], row['date']))
return callDetail
def saveUserDestinationNumberDetail(self, day):
callData = self.getUserDestinationNumberDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_userdestinationnumberdetail \
(extension_id, total_calls, total_seconds, cost, prefix, number, date) \
VALUES(%s, %s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "User Destination Number Detail saved:", totalRowsSaved
# -----------------TRUNKS---------------------
def getProviderDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.provider_id as provider \
FROM tarifica_call JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_destinationgroup.provider_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append(
(row['provider'], row['cost'], row['total_calls'],
row['total_seconds'], row['date']))
return callDetail
def saveProviderDailyDetail(self, day):
callData = self.getProviderDailyDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_providerdailydetail \
(provider_id, cost, total_calls, total_seconds, date) \
VALUES(%s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Provider Daily Detail saved:", totalRowsSaved
def getProviderDestinationDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_call.destination_group_id as destination_group_id, \
tarifica_destinationgroup.provider_id as provider \
FROM tarifica_call JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.destination_group_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((row['provider'], row['cost'],
row['total_calls'], row['total_seconds'],
row['destination_group_id'], row['date']))
return callDetail
def saveProviderDestinationDetail(self, day):
callData = self.getProviderDestinationDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_providerdestinationdetail \
(provider_id, cost, total_calls, total_seconds, destination_group_id, date) \
VALUES(%s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Provider Destination Detail saved:", totalRowsSaved
# -----------------PINSETS-----------------
def getPinsetDailyDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_pinset.id as pinset_number, \
DATE(tarifica_call.date) as date \
FROM tarifica_call JOIN tarifica_pinset \
ON tarifica_call.pinset_number = tarifica_pinset.pinset_number \
WHERE date > %s AND date < %s \
GROUP BY pinset_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
d = (row['pinset_number'], row['total_calls'],
row['total_seconds'], row['cost'], row['date'])
print d
callDetail.append(d)
return callDetail
def savePinsetDailyDetail(self, day):
callData = self.getPinsetDailyDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_pinsetdailydetail \
(pinset_id, total_calls, total_seconds, cost, date) \
VALUES(%s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Pinset Daily Detail saved:", totalRowsSaved
def getPinsetDestinationDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
DATE(tarifica_call.date) as date, \
tarifica_pinset.id as pinset_number, \
tarifica_call.destination_group_id as destination_group_id \
FROM tarifica_call \
LEFT JOIN tarifica_pinset ON \
tarifica_call.pinset_number = tarifica_pinset.pinset_number \
WHERE date > %s AND date < %s AND tarifica_call.pinset_number IS NOT NULL\
GROUP BY tarifica_call.pinset_number, tarifica_call.destination_group_id"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append((row['pinset_number'], row['total_calls'],
row['total_seconds'], row['cost'],
row['destination_group_id'], row['date']))
return callDetail
def savePinsetDestinationDetail(self, day):
callData = self.getPinsetDestinationDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_pinsetdestinationdetail \
(pinset_id, total_calls, total_seconds, cost, destination_group_id, date) \
VALUES(%s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Pinset Destination Detail saved:", totalRowsSaved
def getPinsetDestinationNumberDetail(self, day):
callDetail = []
self.am.connect('nextor_tarificador')
sql = "SELECT SUM(tarifica_call.cost) as cost, \
SUM(tarifica_call.duration) as total_seconds, \
COUNT(tarifica_call.id) as total_calls, \
tarifica_call.dialed_number as dialed_number, \
tarifica_pinset.id as pinset_number, \
DATE(tarifica_call.date) as date, \
tarifica_destinationgroup.prefix as prefix \
FROM tarifica_call LEFT JOIN tarifica_pinset \
ON tarifica_call.pinset_number = tarifica_pinset.pinset_number \
LEFT JOIN tarifica_destinationgroup \
ON tarifica_call.destination_group_id = tarifica_destinationgroup.id \
WHERE date > %s AND date < %s \
GROUP BY tarifica_call.dialed_number"
self.am.cursor.execute(sql, (getStartOfDay(day), getEndOfDay(day)))
for row in self.am.cursor.fetchall():
callDetail.append(
(row['pinset_number'], row['total_calls'],
row['total_seconds'], row['cost'], row['prefix'],
row['dialed_number'], row['date']))
return callDetail
def savePinsetDestinationNumberDetail(self, day):
callData = self.getPinsetDestinationNumberDetail(day)
self.am.connect('nextor_tarificador')
sql = "INSERT INTO tarifica_pinsetdestinationnumberdetail \
(pinset_id, total_calls, total_seconds, cost, prefix, number, date) \
VALUES(%s, %s, %s, %s, %s, %s, %s)"
totalRowsSaved = self.am.cursor.executemany(sql, callData)
self.am.db.commit()
print "----------------------------------------"
print "Pinset Destination Number Detail saved:", totalRowsSaved
