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 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 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 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 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 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."
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