def apply_index(shell):
"""Apply index on cdr-stats mongodb collections"""
mongodb.cdr_common.ensure_index([
("start_uepoch", -1),
("caller_id_number", 1),
("destination_number", 1),
("duration", 1),
("billsec", 1),
("hangup_cause_id", 1)])
mongodb.daily_analytic.ensure_index([
("metadata.date", -1),
("metadata.switch_id", 1),
("metadata.country_id", 1),
("metadata.accountcode", 1)])
mongodb.monthly_analytic.ensure_index([
("metadata.date", -1),
("metadata.switch_id", 1),
("metadata.country_id", 1),
("metadata.accountcode", 1)])
print_shell(shell, "Index applied on collection")
return True
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 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 log_print(logger, shell, msg):
if logger:
logger.info(msg)
else:
print_shell(shell, msg)
sys.exit(1)
try:
if db_engine == 'mysql':
cursor.execute("SELECT VERSION() from %s WHERE import_cdr "
"IS NOT NULL LIMIT 0,1" % table_name)
elif db_engine == 'pgsql':
cursor.execute("SELECT VERSION() from %s WHERE import_cdr "
"IS NOT NULL LIMIT 1" % table_name)
cursor.fetchone()
except Exception, e:
# Add missing field to flag import
if db_engine == 'mysql':
cursor.execute("ALTER TABLE %s ADD import_cdr TINYINT NOT NULL "
"DEFAULT '0'" % table_name)
elif db_engine == 'pgsql':
cursor.execute("ALTER TABLE %s ADD import_cdr SMALLINT NOT NULL "
"DEFAULT '0'" % table_name)
cursor.execute("ALTER TABLE %s ADD INDEX (import_cdr)" % table_name)
count_import = push_asterisk_cdr(shell, table_name, db_engine, cursor,
cursor_updated, switch, ipaddress)
cursor_updated.close()
cursor.close()
connection.close()
print_shell(
shell, "Import on Switch(%s) - Record(s) imported:%d" %
(ipaddress, 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 log_print(logger, shell, msg):
if logger:
logger.info(msg)
else:
print_shell(shell, msg)
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:
voipplan_id = UserProfile.objects.get(accountcode=accountcode).voipplan_id
except:
voipplan_id = False
print_shell(shell, "No VoipPlan created for this user/accountcode")
try:
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
(e, ipaddress))
sys.exit(1)
try:
if db_engine == 'mysql':
cursor.execute("SELECT VERSION() from %s WHERE import_cdr "
"IS NOT NULL LIMIT 0,1" % table_name)
elif db_engine == 'pgsql':
cursor.execute("SELECT VERSION() from %s WHERE import_cdr "
"IS NOT NULL LIMIT 1" % table_name)
cursor.fetchone()
except Exception, e:
# Add missing field to flag import
if db_engine == 'mysql':
cursor.execute("ALTER TABLE %s ADD import_cdr TINYINT NOT NULL "
"DEFAULT '0'" % table_name)
elif db_engine == 'pgsql':
cursor.execute("ALTER TABLE %s ADD import_cdr SMALLINT NOT NULL "
"DEFAULT '0'" % table_name)
cursor.execute("ALTER TABLE %s ADD INDEX (import_cdr)" %
table_name)
count_import = push_asterisk_cdr(shell, table_name, db_engine, cursor, cursor_updated, switch, ipaddress)
cursor_updated.close()
cursor.close()
connection.close()
print_shell(shell, "Import on Switch(%s) - Record(s) imported:%d" %
(ipaddress, count_import))
