def push_asterisk_cdr(shell, table_name, db_engine, cursor, cursor_updated, switch, ipaddress): """ function to import and aggreagate Asterisk CDR """ cdr_source_type = CDR_SOURCE_TYPE.ASTERISK count_import = 0 # Each time the task is running we will only take 1000 records to import # This define the max speed of import, this limit could be changed if db_engine == 'mysql': cursor.execute("SELECT dst, UNIX_TIMESTAMP(calldate), clid, channel," "duration, billsec, disposition, accountcode, uniqueid," " %s FROM %s WHERE import_cdr=0 LIMIT 0, 1000" % (settings.ASTERISK_PRIMARY_KEY, table_name)) elif db_engine == 'pgsql': cursor.execute( "SELECT dst, extract(epoch FROM calldate), clid, channel," "duration, billsec, disposition, accountcode, uniqueid," " %s FROM %s WHERE import_cdr=0 LIMIT 1000" % (settings.ASTERISK_PRIMARY_KEY, table_name)) row = cursor.fetchone() # Store cdr in list to insert by bulk local_count_import = 0 batch_count = 0 acctid_list = '' while row is not None: destination_number = row[0] if not destination_number: # don't import CDR with no destination number continue acctid = row[9] callerid = row[2] try: m = re.search('"(.+?)" <(.+?)>', callerid) callerid_name = m.group(1) callerid_number = m.group(2) except: callerid_name = '' callerid_number = callerid channel = row[3] if not channel: channel = '' # Set empty string for channel in case is None duration = set_int_default(row[4], 0) billsec = set_int_default(row[5], 0) hangup_cause_id = translate_disposition(row[6]) accountcode = row[7] uniqueid = row[8] start_uepoch = datetime.fromtimestamp(int(row[1])) # Check Destination number if len(destination_number ) <= settings.INTERNAL_CALL or destination_number[:1].isalpha(): authorized = 1 country_id = 999 else: destination_data = verify_auth_dest_number(destination_number) authorized = destination_data['authorized'] country_id = destination_data['country_id'] # Option to get the direction from user_field direction = "unknown" try: # TODO: Code not valide as accountcode moved to his own model AccountCode # FYI, asterisk_fetcher.py is not used voipplan_id = UserProfile.objects.get( accountcode=accountcode).voipplan_id except: voipplan_id = False print_shell(shell, "No VoipPlan created for this user/accountcode") try: # TODO: Code not valide as accountcode moved to his own model AccountCode # FYI, asterisk_fetcher.py is not used user = UserProfile.objects.get(accountcode=accountcode).user except: # Cannot assign accountcode to an existing user # we will then assign to admin user = User.objects.filter(is_superuser=True)[0] call_rate = calculate_call_cost(voipplan_id, destination_number, billsec) buy_rate = call_rate['buy_rate'] buy_cost = call_rate['buy_cost'] sell_rate = call_rate['sell_rate'] sell_cost = call_rate['sell_cost'] # Sanitize callerid_number = sanitize_cdr_field(callerid_number) callerid_name = sanitize_cdr_field(callerid_name) destination_number = sanitize_cdr_field(destination_number) channel = sanitize_cdr_field(channel) cdr_json = {'channel': channel} dialcode = '' cdr = CDR(user=user, switch=switch, cdr_source_type=cdr_source_type, callid=uniqueid, caller_id_number=callerid_number, destination_number=destination_number, dialcode_id=dialcode, starting_date=start_uepoch, duration=duration, billsec=billsec, hangup_cause=hangup_cause_id, direction=direction, country_id=country_id, authorized=authorized, accountcode=accountcode, buy_rate=buy_rate, buy_cost=buy_cost, sell_rate=sell_rate, sell_cost=sell_cost, data=cdr_json) cdr.save() # Append cdr to bulk_cdr list count_import = count_import + 1 local_count_import = local_count_import + 1 batch_count = batch_count + 1 acctid_list += "%s, " % str(acctid) if batch_count == 100: acctid_list = acctid_list[:-2] # trim last comma (,) from string # Postgresql # select * from table_name where id in (1,2,3); update_cdr = "UPDATE %s SET import_cdr=1 WHERE %s in (%s)" % \ (table_name, settings.ASTERISK_PRIMARY_KEY, acctid_list) cursor_updated.execute(update_cdr) batch_count = 0 acctid_list = '' # Fetch a other record row = cursor.fetchone() if len(acctid_list) > 0: acctid_list = acctid_list[:-2] # trim last comma (,) from string # Postgresql # select * from table_name where id in (1,2,3); update_cdr = "UPDATE %s SET import_cdr=1 WHERE %s in (%s)" % \ (table_name, settings.ASTERISK_PRIMARY_KEY, acctid_list) cursor_updated.execute(update_cdr) return count_import
def import_cdr(shell=False, logger=False): """ Connect to the `import_cdr` Database and import the new CDRs """ count_imported = 0 log_print(logger, shell, "in func import_cdr...") if not check_connection_sql(): log_print(logger, shell, "check_connection_sql - Error Connection") return (False, "Error Connection") # Each time the task is running we will only take CDR_IMPORT_LIMIT records to import # This define the max speed of import, this limit could be changed new_CDRs = CDRImport.objects.using('import_cdr')\ .filter(imported=False)\ .order_by('-id')[:settings.CDR_IMPORT_LIMIT] (list_newcdr, list_cdrid) = ([], []) for call in new_CDRs: # Increment counter count_imported = count_imported + 1 # Get the dialcode dialcode = get_dialcode(call.destination_number, call.dialcode) switch_info = chk_ipaddress(call.switch) # Check Destination number if len( call.destination_number ) <= settings.INTERNAL_CALL or call.destination_number[:1].isalpha(): authorized = 1 country_id = None call_type = CALL_TYPE.INTERNAL else: # TODO: rename verify_auth_dest_number verify_auth_dest_number destination_data = verify_auth_dest_number(call.destination_number) authorized = destination_data['authorized'] country_id = destination_data['country_id'] call_type = CALL_TYPE.INTERNATIONAL # Sanitize direction if call.direction: direction = call.direction else: direction = CALL_DIRECTION.NOTDEFINED # Find the user for given accountcode try: user = AccountCode.objects.get(accountcode=call.accountcode).user except: # Cannot assign accountcode to an existing user, therefore we will assign to an Admin user = User.objects.filter(is_superuser=True)[0] try: user_profile = user.userprofile except: user_profile = UserProfile(user=user) user_profile.save() # Retrieve VoipPlan if user_profile: voipplan_id = user_profile.voipplan_id else: voipplan_id = False print_shell( shell, "VoipPlan doesn't exist for this user/accountcode (%s)" % call.accountcode) if call.buy_rate or call.buy_cost or call.sell_rate or call.sell_cost: buy_rate = call.buy_rate buy_cost = call.buy_cost sell_rate = call.sell_rate sell_cost = call.sell_cost elif voipplan_id: call_rate = calculate_call_cost(voipplan_id, call.destination_number, call.billsec) buy_rate = call_rate['buy_rate'] buy_cost = call_rate['buy_cost'] sell_rate = call_rate['sell_rate'] sell_cost = call_rate['sell_cost'] else: buy_rate = buy_cost = sell_rate = sell_cost = 0 hangup_cause_id = get_hangupcause_id(call.hangup_cause_id) log_print( logger, shell, "Create new CDR -> date:%s - dst:%s - duration:%s - hangup_cause:%s - sell_cost:%s" % (call.starting_date, call.destination_number, str( call.duration), str(hangup_cause_id), str(call.sell_cost))) # Create the new CDR newCDR = CDR( user=user, switch=switch_info['switch'], cdr_source_type=call.cdr_source_type, callid=call.callid, caller_id_number=call.caller_id_number, caller_id_name=call.caller_id_name, destination_number=call.destination_number, dialcode_id=dialcode, starting_date=call.starting_date, duration=call.duration, billsec=call.billsec, progresssec=call.progresssec, answersec=call.answersec, waitsec=call.waitsec, hangup_cause_id=hangup_cause_id, direction=direction, country_id=country_id, authorized=authorized, accountcode='' if call.accountcode is None else call.accountcode, buy_rate=buy_rate, buy_cost=buy_cost, sell_rate=sell_rate, sell_cost=sell_cost, call_type=call_type, data='' if call.extradata is None else call.extradata) list_newcdr.append(newCDR) list_cdrid.append(str(call.id)) if (count_imported % 100) == 0: bulk_create_cdrs(list_newcdr, list_cdrid) (list_newcdr, list_cdrid) = ([], []) # we exit the loop but we might still have some remaining CDRs to push if len(list_newcdr) > 0: bulk_create_cdrs(list_newcdr, list_cdrid) (list_newcdr, list_cdrid) = ([], []) log_print( logger, shell, 'TASK :: run_cdr_import -> func import_cdr count_imported:%d' % count_imported) return (True, count_imported)
def handle(self, *args, **options): no_of_record = 1 # default if options.get('number-cdr'): no_of_record = int(options.get('number-cdr', 1)) day_delta_int = 30 # default if options.get('delta-day'): day_delta_int = int(options.get('delta-day', 30)) arg_duration = False # default if options.get('duration'): arg_duration = int(options.get('duration'), -1) list_user = User.objects.all() user = list_user[0] list_switch = Switch.objects.all() switch = list_switch[0] # Pull a random accountcode list_acc = AccountCode.objects.all().order_by('?')[:1] if list_acc: acc = list_acc[0] else: acc = None for i in range(1, int(no_of_record) + 1): ( callid, answer_stamp, start_uepoch, caller_id, channel_name, destination_number, dialcode, hangup_cause, hangup_cause_q850, duration, billsec, end_stamp, cdr_source_type, authorized, country_id, direction, # accountcode, buy_rate, buy_cost, sell_rate, sell_cost ) = CDR.generate_fake_cdr(day_delta_int) if arg_duration and arg_duration >= 0: duration = arg_duration if i % 100 == 0: print '%d CDRs created...' % i print "CDR => date:%s, callid:%s, dur:%s, pn:%s, dc:%s, country:%s, hg_cause:%s" % \ (answer_stamp, callid, duration, destination_number, dialcode, country_id, hangup_cause) cdr_json = { 'channel_data': { 'state': 'CS_REPORTING', 'direction': 'inbound' }, 'variables': { 'direction': 'inbound', 'uuid': callid, 'session_id': '3', 'sip_network_ip': '192.168.1.21', 'sip_network_port': '60536', 'sip_authorized': 'true', 'sip_number_alias': '1000', 'sip_auth_username': '******', 'sip_auth_realm': '127.0.0.1', 'user_name': '1000', 'accountcode': '1000', 'channel_name': str(channel_name), 'sip_via_host': '192.168.1.21', 'sip_via_port': '60536', 'presence_id': '[email protected]', 'sip_use_codec_name': 'G722', 'sip_use_codec_rate': '8000', 'read_codec': 'G722', 'read_rate': '16000', 'write_codec': 'G722', 'write_rate': '16000', 'call_uuid': callid, 'remote_media_ip': '192.168.1.21', 'endpoint_disposition': 'ANSWER', 'current_application_data': '2000', 'current_application': 'delay_echo', 'sip_term_status': '200', 'proto_specific_hangup_cause': 'sip:200', 'sip_term_cause': '16', 'hangup_cause': str(hangup_cause), 'hangup_cause_q850': str(hangup_cause_q850), 'start_stamp': str(answer_stamp), 'profile_start_stamp': str(answer_stamp), 'answer_stamp': str(answer_stamp), 'end_stamp': str(end_stamp), 'start_epoch': '1327521953', 'start_uepoch': str(start_uepoch), 'answer_epoch': '1327521953', 'answer_uepoch': '1327521953952257', 'end_epoch': '1327521966', 'end_uepoch': '1327521966912241', 'last_app': 'delay_echo', 'last_arg': '2000', 'caller_id': str(caller_id), 'duration': str(duration), 'billsec': str(billsec), 'answersec': '0', 'waitsec': '0', 'mduration': '12960', 'billmsec': '12960', 'uduration': '12959984', 'billusec': '12959984', }, 'callflow': [{ 'dialplan': 'XML', 'caller_profile': { 'caller_id_name': str(caller_id), 'ani': str(caller_id), 'caller_id_number': str(caller_id), 'network_addr': '192.168.1.21', 'destination_number': str(destination_number), 'uuid': callid, 'chan_name': 'sofia/internal/[email protected]' } }] } cdr = CDR(user=user, switch=switch, cdr_source_type=cdr_source_type, callid=callid, caller_id_number=caller_id, destination_number=destination_number, dialcode_id=dialcode, starting_date=answer_stamp, duration=duration, billsec=billsec, hangup_cause=hangup_cause, direction=direction, country_id=country_id, authorized=authorized, accountcode=acc, data=cdr_json) cdr.save()