def find_pair_details(kwargs):
"""method to find secondary-pairs associated with provided primary-pair"""
try:
msisdn_list = []
chk_primary = Pairing.query.filter(Pairing.msisdn == '{}'.format(kwargs['primary_msisdn']),
Pairing.is_primary == True,
Pairing.end_date == None).first()
# to check if request is made from primary-pair
if chk_primary:
chk_sec = Pairing.query.filter(Pairing.primary_id == '{}'.format(chk_primary.id),
Pairing.end_date == None).all()
if chk_sec:
for m in chk_sec:
msisdn_list.append(int(m.msisdn))
return msisdn_list
else:
return custom_text_response(_("No Pair is associated with %(pm)s", pm=kwargs['primary_msisdn']),
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
else:
return custom_text_response(_("%(pm)s is not registered as Primary-Pair", pm=kwargs['primary_msisdn']),
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def get(**kwargs):
"""method to populate Operators home page with MSISDNs"""
try:
mno_info = []
msisdn_list = []
chk_count = Pairing.query.filter(
Pairing.operator_name == '{}'.format(kwargs['operator']),
Pairing.imsi == None, Pairing.add_pair_status == True,
Pairing.end_date == None).distinct(Pairing.msisdn).count()
chk_imsi = Pairing.query.filter(Pairing.operator_name == '{}'.format(kwargs['operator']),
Pairing.imsi == None,
Pairing.add_pair_status == True,
Pairing.end_date == None).distinct(Pairing.msisdn). \
offset(kwargs['start']).limit(kwargs['limit']).all()
if chk_imsi:
for c in chk_imsi:
msisdn_list.append(c.msisdn)
for r in msisdn_list:
a = gen_req_id()
data = {"Req_id": a, "MSISDN": r}
mno_info.append(data)
if mno_info:
paginated_data = {
'start': kwargs['start'],
'limit': kwargs['limit'],
'count': chk_count,
'cases': mno_info
}
return custom_text_response(
paginated_data,
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('JSON'))
else:
return custom_json_response(
_("no record found"),
status=STATUS_CODES.get('NOT_FOUND'),
mimetype=MIME_TYPES.get('JSON'))
except Exception as e: # pragma: no cover
db.session.rollback()
finally:
db.session.close()
def first_pair_creation(kwargs):
"""method to create primary/first pair"""
try:
chk_pc = Pairing_Codes.query.filter(Pairing_Codes.pair_code == '{}'.format(kwargs['pair_code']),
Pairing_Codes.is_active == True).first()
if chk_pc:
chk_primary = Pairing.query.filter(Pairing.msisdn == '{}'.format(kwargs['sender_no'])) \
.filter(Pairing.is_primary == True) \
.filter(Pairing.end_date == None).first()
if not chk_primary:
chk_imei = Imei.query.filter(Imei.device_id == '{}'.format(chk_pc.device_id)).all()
for q in chk_imei:
first_add = Pairing(primary_id=0, # Inserting Primary-Pair in pairing table
msisdn=kwargs['sender_no'],
is_primary=True,
creation_date=strftime("%Y-%m-%d %H:%M:%S"),
operator_name=kwargs['operator'],
add_pair_status=True,
imei_id=q.id)
db.session.add(first_add)
db.session.flush()
chk_pc.is_active = False # de-activating pair-code in PairCodes Table
db.session.flush()
db.session.commit()
return custom_text_response(_("PairCode %(v1)s is valid and your pair will be added in next 24 to "
"48 hours", v1=kwargs['pair_code']),
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('TEXT'))
else:
return custom_text_response(_("MSISDN already exists as Primary-Pair"),
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
else:
return custom_text_response(_("Pair Code %(pc)s is not Valid", pc=kwargs['pair_code']),
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def put(**kwargs):
"""method to upload single IMSI"""
try:
chk_imsi = Pairing.query.filter(
Pairing.imsi == '{}'.format(kwargs['imsi']),
Pairing.end_date == None).first()
if chk_imsi:
return custom_json_response(
_("IMSI already exists"),
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('JSON'))
else:
chk_msisdn = Pairing.query.filter(
Pairing.operator_name == '{}'.format(kwargs['operator']),
Pairing.msisdn == '{}'.format(kwargs['msisdn']),
Pairing.imsi == None, Pairing.add_pair_status == True,
Pairing.end_date == None).all()
# checking criteria for MSISDN to get IMSI
if chk_msisdn:
for m in chk_msisdn:
m.imsi = kwargs['imsi']
m.updated_at = strftime("%Y-%m-%d %H:%M:%S")
m.change_type = 'add'
m.export_status = False
db.session.commit()
return custom_json_response(
_("IMSI added successfully"),
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('JSON'))
else:
return custom_json_response(
_("IMSI addition Failed"),
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('JSON'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def verify_pair_code(kwargs):
"""method to verify paircode"""
try:
# checking pair-code's validity
chk_pc = Pairing_Codes.query.filter(
Pairing_Codes.pair_code == '{}'.format(kwargs['pair_code']),
Pairing_Codes.is_active == True).first()
if chk_pc:
# verify that IMEI is related to that pair-code
chk_imei = Imei.query.filter(
Imei.imei == '{}'.format(kwargs['imei']),
Imei.device_id == '{}'.format(chk_pc.device_id)).all()
if chk_imei:
return custom_text_response(_(
"Pair-Code %(pc)s is active & associated with provided IMEI",
pc=kwargs['pair_code']),
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get(
'TEXT'))
else:
return custom_text_response(_(
"IMEI %(imei)s is not associated with Pair-Code %(paircode)s",
imei=kwargs['imei'],
paircode=kwargs['pair_code']),
status=STATUS_CODES.get(
'UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get(
'TEXT'))
else:
return custom_text_response(
_("Pair-Code %(pc)s is not valid", pc=kwargs['pair_code']),
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
except Exception as e: # pragma: no cover
db.session.rollback()
finally:
db.session.close()
def get(**kwargs):
"""method to accept & process Error-File."""
try:
if not os.path.isfile(kwargs['url']):
return custom_json_response(_("File not found"),
STATUS_CODES.get('NOT_FOUND'),
MIME_TYPES.get('JSON'))
else:
return send_file(kwargs['url'], as_attachment=True)
except Exception as e: # pragma: no cover
app.logger.info(_("Error occurred while retrieving File."))
app.logger.exception(e)
return custom_json_response(
_("Failed to retrieve Error File."),
STATUS_CODES.get('SERVICE_UNAVAILABLE'),
MIME_TYPES.get('JSON'))
def get():
"""method to create pair-codes"""
pc = gen_paircode()
return custom_paircode_response(
_("Pair-Code consists of 8 characters"),
pc,
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('JSON'))
def get(**kwargs):
"""method to download Bulk MSISDN File"""
try:
msisdn_list = []
DOWNLOAD_FOLDER = app.config['DPS_DOWNLOADS']
chk_imsi = Pairing.query.filter(Pairing.operator_name == '{}'.format(kwargs['operator'])) \
.filter(Pairing.imsi == None) \
.filter(Pairing.add_pair_status == True) \
.filter(Pairing.end_date == None).all()
# to check which MSISDNs require IMSI from MNO
if chk_imsi:
filename = "MSISDNs-List_" + kwargs[
'operator'] + '_' + strftime("%Y-%m-%d_%H-%M-%S") + '.csv'
download_path = os.path.join(DOWNLOAD_FOLDER, filename)
for c in chk_imsi:
msisdn_list.append(c.msisdn)
msisdn_list = set(msisdn_list)
msisdn_list = list(msisdn_list)
with open(download_path, 'w') as file:
file.write('MSISDN,IMSI\n')
file.write(",\n".join(msisdn_list))
file.close()
response = make_response(
send_file(download_path, as_attachment=True))
response.headers['Cache-Control'] = 'no-store'
return response
else:
return custom_json_response(
_("No File found"),
status=STATUS_CODES.get('NOT_FOUND'),
mimetype=MIME_TYPES.get('JSON'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def params_validation(text, kwargs):
"""method to validate the parameters send via SMS-Text"""
if len(text) > 2:
secondary_msisdn = text[2]
params = {
"primary_msisdn": kwargs['sender_no'],
"secondary_msisdn": secondary_msisdn
}
try:
Validations.validate_msisdn(secondary_msisdn)
except ValidationError:
return custom_text_response(
"Secondary MSISDN format is not correct",
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT')), None
else:
return None, params
else:
return "USSD does not contain Secondary MSISDN", None
def del_sngl_pair(kwargs):
"""method to delete/release a single pair"""
try:
rtn_msg = ""
chk_primary = Pairing.query.filter(
Pairing.msisdn == '{}'.format(kwargs['primary_msisdn']),
Pairing.is_primary == True, Pairing.end_date == None,
Pairing.msisdn != kwargs['secondary_msisdn']).all()
# checking primary and checking deletion request is not for Primary-Pair
if chk_primary:
for p in chk_primary:
num_exist = Pairing.query.filter(
Pairing.msisdn == '{}'.format(
kwargs['secondary_msisdn']),
Pairing.end_date == None,
Pairing.primary_id == p.id).first()
# checking whether, to-be-deleted MSISDN is paired with Primary, or not"
if num_exist:
num_exist.end_date = strftime("%Y-%m-%d")
if num_exist.imsi is not None and num_exist.add_pair_status \
and num_exist.change_type == 'add' and num_exist.export_status == True:
"""
Condition checks only those pairs be exported as "removed" in pair-list
which are added and already exported to DIRBS-CORE before removing
"""
num_exist.export_status = False
num_exist.change_type = 'remove'
elif num_exist.export_status is False and \
(num_exist.change_type is None or num_exist.change_type == 'add'):
""" Condition to avoid exporting this pair to DIRBS-CORE """
num_exist.export_status = None
num_exist.change_type = None
num_exist.old_imsi = None
elif num_exist.imsi is None and num_exist.export_status is None \
and num_exist.change_type is None and num_exist.old_imsi is not None:
"""
Condition for case where pair(s) is exported once and after that SIM-Change is requested
but before MNO provides new IMSI, Pair is deleted.
"""
num_exist.export_status = False
num_exist.change_type = "remove"
num_exist.imsi = num_exist.old_imsi
num_exist.old_imsi = None
db.session.commit()
rtn_msg = (_(
"Deletion request is successful. Pair will be removed in next 24 to 48 hours"
))
else:
rtn_msg = _(
"MSISDN %(sec)s is not Paired with the device",
sec=kwargs['secondary_msisdn'])
else:
rtn_msg = _(
"Request is not made by Primary-MSISDN or number-to-be-deleted belongs to primary pair"
)
return custom_text_response(rtn_msg,
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('TEXT'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def post(**kwargs):
"""method to create Secondary/Additional pairs confirmation"""
try:
rtn_msg = ""
cnfm_sms = False
chk_primary = Pairing.query.filter(Pairing.msisdn == '{}'.format(kwargs['primary_msisdn'])) \
.filter(Pairing.is_primary == True) \
.filter(Pairing.end_date == None).all()
if chk_primary:
for q in chk_primary:
if kwargs['confirm'] in ["NO", "no", "No"]:
othr_chks = Pairing.query.filter(
Pairing.msisdn == '{}'.format(
kwargs['secondary_msisdn']),
Pairing.is_primary == False,
Pairing.primary_id == '{}'.format(q.id),
Pairing.end_date == None,
Pairing.add_pair_status == False).first()
if othr_chks:
db.session.delete(othr_chks)
db.session.commit()
rtn_msg = _(
"Request of additional pair is rejected by %(sec)s",
sec=kwargs['secondary_msisdn'])
cnfm_sms = True
else:
rtn_msg = _(
"Confirmation of additional pair request is not done by valid MSISDN"
)
elif kwargs['confirm'] in ["YES", "Yes", "yes"]:
othr_chks = Pairing.query.filter(
db.and_(
Pairing.msisdn == '{}'.format(
kwargs['secondary_msisdn']),
Pairing.is_primary == False,
Pairing.primary_id == '{}'.format(q.id),
Pairing.end_date == None,
Pairing.add_pair_status == False)).first()
if othr_chks:
othr_chks.add_pair_status = True
othr_chks.operator_name = kwargs['operator']
othr_chks.updated_at = '{}'.format(
strftime("%Y-%m-%d %H:%M:%S"))
db.session.commit()
rtn_msg = _(
"Request of additional pair from %(prim)s is accepted by %(sec)s",
prim=kwargs['primary_msisdn'],
sec=kwargs['secondary_msisdn'])
cnfm_sms = True
else:
rtn_msg = _(
"Confirmation of additional pair request is not done by valid MSISDN"
)
else:
rtn_msg = _("Wrong Primary number mentioned in SMS")
if cnfm_sms:
""" ****************** Kannel-Block replaced with Jasmin ******************
chg_msisdn = '0' + kwargs['primary_msisdn'][2:]
payload = {'username': conf['kannel_username'], 'password': conf['kannel_password'],
'smsc': conf['kannel_smsc'], 'from': conf['kannel_shortcode'], 'to': chg_msisdn,
'text': rtn_msg}
requests.get(conf['kannel_sms'], params=payload)
"""
response = JasminAPIs.jasmin_sms(kwargs['secondary_msisdn'],
conf['kannel_shortcode'],
rtn_msg, kwargs['operator'])
cnfm_sms = False
return custom_text_response(rtn_msg,
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('TEXT'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def post(**kwargs):
"""method to create device registration request."""
try:
chk_owner_id = Owner.query.filter(
Owner.contact == '{}'.format(kwargs['contact_no'])).first()
if not chk_owner_id:
add_owner = Owner(contact=kwargs['contact_no'])
db.session.add(add_owner)
db.session.flush()
owner_id = add_owner.id
else:
owner_id = chk_owner_id.id
add_device = Devices(model=kwargs['model'],
brand=kwargs['brand'],
serial_no=kwargs['serial_no'].strip(),
mac=kwargs['mac'].strip(),
rat=kwargs['rat'].strip(','),
owner_id=owner_id)
db.session.add(add_device)
db.session.flush()
for i in kwargs['imei']:
add_imei = Imei(imei=i, device_id=add_device.id)
db.session.add(add_imei)
db.session.flush()
pair_code = gen_paircode()
add_paircode = Pairing_Codes(pair_code=pair_code,
is_active=True,
device_id=add_device.id)
db.session.add(add_paircode)
db.session.flush()
db.session.commit()
""" ****************** Kannel-Block replaced with Jasmin ******************
message = _("Device has been registered with Authority. Your Activation Pair-Code is %(pc)s",
pc=pair_code)
chg_msisdn = '0' + kwargs['contact_no'][2:]
payload = {'username': conf['kannel_username'], 'password': conf['kannel_password'],
'smsc': conf['kannel_smsc'], 'from': conf['kannel_shortcode'], 'to': chg_msisdn,
'text': message}
requests.get(conf['kannel_sms'], params=payload)
"""
message = _(
"Device has been registered with Authority. Your Activation Pair-Code is %(pc)s",
pc=pair_code)
response = JasminAPIs.jasmin_sms(kwargs['contact_no'],
conf['kannel_shortcode'], message)
return custom_paircode_response(
_("Device's information has been successfully loaded"),
pair_code,
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('JSON'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def sec_pair_creation(kwargs):
"""method to create additional/Secondary pair"""
try:
chk_primary = Pairing.query.filter(
db.and_(
Pairing.msisdn == '{}'.format(kwargs['primary_msisdn']),
Pairing.is_primary == True, Pairing.end_date == None,
Pairing.msisdn != kwargs['secondary_msisdn'])).all()
if chk_primary:
chk_1 = True
for q in chk_primary:
chk_sec = Pairing.query.filter(Pairing.primary_id == '{}'.format(q.id)) \
.filter(Pairing.end_date == None).all()
if chk_sec:
for r in chk_sec:
if r.msisdn != kwargs[
'secondary_msisdn']: # checking if MSISDN is already paired or not
chk_2 = True
if len(chk_sec) < conf[
'pair_limit']: # checking if pair-limit is exceeded or not
chk_3 = True
else:
chk_3 = False
return custom_text_response(
_("Pairing limit breached: remove any existing pair first"
),
status=STATUS_CODES.get(
'UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
else:
chk_2 = False
return custom_text_response(
_("MSISDN %(msisdn)s already paired with the device",
msisdn=kwargs['secondary_msisdn']),
status=STATUS_CODES.get(
'UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
if chk_1 and chk_2 and chk_3:
adding1 = Pairing(
primary_id=q.id,
msisdn=kwargs['secondary_msisdn'],
is_primary=False,
creation_date=strftime("%Y-%m-%d %H:%M:%S"),
add_pair_status=False,
imei_id=q.imei_id)
# adding secondary pair in case one or more secondary pairs already exists
db.session.add(adding1)
db.session.flush()
db.session.commit()
cnfm_sms = True
rtn_msg = _(
"Secondary pair is added by %(primary)s. Confirmation is awaited from %(sec)s",
primary=kwargs['primary_msisdn'],
sec=kwargs['secondary_msisdn'])
else:
adding2 = Pairing(
primary_id=q.id,
msisdn=kwargs['secondary_msisdn'],
is_primary=False,
creation_date=strftime("%Y-%m-%d %H:%M:%S"),
add_pair_status=False,
imei_id=q.imei_id
) # adding secondary pair for first time
db.session.add(adding2)
db.session.flush()
db.session.commit()
cnfm_sms = True
rtn_msg = _(
"Secondary pair is added by %(primary)s. Confirmation is awaited from %(sec)s",
primary=kwargs['primary_msisdn'],
sec=kwargs['secondary_msisdn'])
else:
chk_1 = False
return custom_text_response(_(
"Request not made by Primary-Pair or number-to-be-added is Primary number"
),
status=STATUS_CODES.get(
'UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
if cnfm_sms:
""" ****************** Kannel-Block replaced with Jasmin ******************
chg_msisdn = '0' + kwargs['secondary_msisdn'][2:]
message = "CONFIRM [{}]\nPlease reply with Yes/No space {}".format(kwargs['primary_msisdn'],
kwargs['primary_msisdn'])
payload = {'username': conf['kannel_username'], 'password': conf['kannel_password'],
'smsc': conf['kannel_smsc'], 'from': conf['kannel_shortcode'], 'to': chg_msisdn,
'text': message}
# requests.get(conf['kannel_sms'], params=payload)
"""
message = "CONFIRM [{}]\nPlease reply with Yes/No space {}".format(
kwargs['primary_msisdn'], kwargs['primary_msisdn'])
response = JasminAPIs.jasmin_sms(kwargs['secondary_msisdn'],
conf['kannel_shortcode'],
message)
cnfm_sms = False # pragma: no cover
return custom_text_response(rtn_msg,
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('TEXT'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def get(self):
kwargs = request.args
try:
USSDSchema().load(kwargs)
except ValidationError as e:
err = []
for v in e.messages.values():
err.append(v[0])
return custom_text_response(
err,
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
ussd_case = kwargs['case']
text = kwargs['msg_text'].split(',')
if ussd_case == "first_pair":
if len(text) > 2:
pair_code = text[2]
params = {
"pair_code": pair_code,
"sender_no": kwargs['sender_no'],
"operator": kwargs['operator']
}
try:
Validations.validate_paircode(pair_code)
except ValidationError as e:
return custom_text_response(
e.messages[0],
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
pp = FirstPair()
result = pp.first_pair_creation(params)
return result
else:
return "USSD does not contain pair-code"
elif ussd_case == "additional_pair":
rslt, params = self.params_validation(text, kwargs)
if rslt: return rslt
else:
ap = AdditionalPairs()
result = ap.sec_pair_creation(params)
return result
elif ussd_case == "del_single_pair":
rslt, params = self.params_validation(text, kwargs)
if rslt: return rslt
else:
dsp = ReleaseSinglePair()
result = dsp.del_sngl_pair(params)
return result
elif ussd_case == "del_all_pairs":
params = {"primary_msisdn": kwargs['sender_no']}
dap = ReleaseAllPairs()
result = dap.del_all_pairs(params)
return result
elif ussd_case == "sim_change":
params = {
"msisdn": kwargs['sender_no'],
"operator": kwargs['operator']
}
sc = SimChange()
result = sc.sim_change_mnp(params)
return result
elif ussd_case == "verify_pair":
if len(text) > 3:
pair_code, imei = text[2], text[3]
params = {"pair_code": pair_code, "imei": imei}
try:
VfyPaircodeSchema().load(params)
except ValidationError as e:
err = []
if e.messages.get("imei"):
err.append(e.messages.get("imei")[0])
if e.messages.get("pair_code"):
err.append(e.messages.get("pair_code")[0])
return custom_text_response(
err,
status=STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
mimetype=MIME_TYPES.get('TEXT'))
vps = VerifyPairCode()
result = vps.verify_pair_code(params)
return result
else:
return "USSD does not provide all the required information"
elif ussd_case == "find_pair":
primary_msisdn = {"primary_msisdn": kwargs['sender_no']}
fp = FindPairs()
result = fp.find_pair_details(primary_msisdn)
return result
return "Case not found"
def post(**kwargs):
"""method to upload bulk IMSIs File"""
try:
file = request.files.get('file')
if file and file_allowed(file.filename):
tmp_dir = tempfile.mkdtemp()
filename = secure_filename(file.filename)
filepath = os.path.join(tmp_dir, filename)
file.save(filepath)
try:
filetype = magic.from_file(filepath, mime=True)
if filename != '' or filetype == 'text/plain':
try:
with open(filepath, 'r') as newfile:
df = pd.read_csv(newfile,
usecols=range(2),
dtype={
"MSISDN": str,
"IMSI": str
})
newfile.close()
except Exception as e:
if e:
newfile.close()
return custom_json_response(
_("File content is not Correct"),
STATUS_CODES.get('FORBIDDEN'),
MIME_TYPES.get('JSON'))
total_rows, total_columns = df.shape
if df.columns[0] == 'MSISDN' or df.columns[1] == 'IMSI':
df['MSISDN'] = df['MSISDN'].str.strip(
) # removing white spaces from Column 'MSISDN'
df['IMSI'] = df['IMSI'].str.strip(
) # removing white spaces from Column 'IMSI'
df_dup = df[df.duplicated(['IMSI'], keep='first')]
# to check duplicated IMSIs
if df_dup.empty:
df2 = df[df.isnull().any(
axis=1
)] # to detect null values in any column
df1 = df.dropna(
) # to drop rows with one or more null values
df3 = df1[~(df1.MSISDN.astype(str).str.match(
conf['validation_regex']['msisdn']))]
df1 = df1[(df1.MSISDN.astype(str).str.match(
conf['validation_regex']['msisdn']))]
df4 = df1[~(df1.IMSI.astype(str).str.match(
conf['validation_regex']['imsi']))]
df1 = df1[(df1.IMSI.astype(str).str.match(
conf['validation_regex']['imsi']))]
final_rows, final_columns = df1.shape
del_rec = (total_rows - final_rows)
df1.to_csv(filepath, index=False)
lst_df = [df2, df3, df4]
dfs = pd.concat(lst_df, ignore_index=False)
con = connect()
filename1 = os.path.join(tmp_dir, filename)
cur = con.cursor()
cur.execute(
""" CREATE TABLE if not exists test_mno (t_msisdn text, t_imsi text)"""
)
# con.commit()
f = open(filename1)
cur.copy_from(f, 'test_mno', sep=",")
cur.execute(
""" update pairing set imsi = test_mno.t_imsi, change_type = 'add',
export_status = false, updated_at = date_trunc('second', NOW())
from test_mno
where pairing.msisdn = test_mno.t_msisdn and pairing.end_date is null
and pairing.add_pair_status = true and pairing.operator_name =
'{mno}'""".format(
mno=kwargs['operator']))
cur.execute(
""" drop table if exists test_mno; """)
con.commit()
cur.close()
con.close()
f.close()
if del_rec != 0:
error_file = "Error-Records_" + kwargs['operator'] + '_' \
+ strftime("%Y-%m-%d_%H-%M-%S") + '.csv'
download_path = os.path.join(
DOWNLOAD_FOLDER, error_file)
file.save(download_path)
dfs.to_csv(download_path, index=False)
rtn_msg = {
"msg": _("File loaded successfully"),
"Total_Records": total_rows,
"Successful_Records": final_rows,
"Deleted_Record": del_rec,
"link": download_path
}
return custom_text_response(
rtn_msg, STATUS_CODES.get('OK'),
MIME_TYPES.get('JSON'))
else:
return custom_json_response(
_("File uploaded successfully without errors"
), STATUS_CODES.get('OK'),
MIME_TYPES.get('JSON'))
else:
return custom_json_response(
_("File contains duplicated IMSIs"),
STATUS_CODES.get('FORBIDDEN'),
MIME_TYPES.get('JSON'))
else:
return custom_json_response(
_("File headers are incorrect"),
STATUS_CODES.get('FORBIDDEN'),
MIME_TYPES.get('JSON'))
else:
return custom_json_response(
_("System only accepts csv/txt files"),
STATUS_CODES.get('FORBIDDEN'),
MIME_TYPES.get('JSON')) # pragma: no cover
finally:
rmtree(tmp_dir)
else:
return custom_json_response(
_("Please select csv/txt file"),
STATUS_CODES.get('UNPROCESSABLE_ENTITY'),
MIME_TYPES.get('JSON'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def del_all_pairs(kwargs):
"""method to delete/release All-Pairs"""
try:
rtn_msg = ""
rel_all_cond = 3
chk_primary = Pairing.query.filter(db.and_(Pairing.msisdn == '{}'.format(kwargs['primary_msisdn']),
Pairing.is_primary == True)).all()
# checking if request is originated from primary-pair
if chk_primary:
for q1 in chk_primary:
if q1.end_date is None:
rel_all_cond = 1
q1.end_date = '{}'.format(strftime("%Y-%m-%d"))
if q1.imsi is not None and q1.add_pair_status and q1.change_type == 'add' \
and q1.export_status is True:
# Condition checks only those pairs be exported as "removed" in pair-list
# which are added and already exported to DIRBS-CORE before removing
q1.export_status = False
q1.change_type = 'remove'
elif q1.export_status is False and (q1.change_type is None or q1.change_type == 'add'):
""" Condition to avoid exporting this pair to DIRBS-CORE """
q1.export_status = None # pragma: no cover
q1.change_type = None # pragma: no cover
q1.old_imsi = None # pragma: no cover
elif q1.imsi is None and q1.export_status is None \
and q1.change_type is None and q1.old_imsi is not None:
"""
Condition for case where pair(s) is exported once and after that SIM-Change is requested
but before MNO provides new IMSI, Pair is deleted.
"""
q1.export_status = False
q1.change_type = "remove"
q1.imsi = q1.old_imsi
q1.old_imsi = None
db.session.commit()
chk_sec_pairs = Pairing.query.filter(Pairing.primary_id == q1.id).all()
# checking if secondary pairs exist under primary
if chk_sec_pairs:
for q2 in chk_sec_pairs:
if q2.imsi is not None and q2.add_pair_status and q2.change_type == 'add' \
and q2.export_status and q2.end_date is None:
"""
Condition checks only those pairs be exported as "removed" in pair-list
which are added and already exported to DIRBS-CORE before removing
"""
q2.export_status = False
q2.change_type = 'remove'
elif q2.export_status is False and (q2.change_type is None or q2.change_type == 'add'):
# Condition to avoid exporting this pair to DIRBS-CORE
q2.export_status = None # pragma: no cover
q2.change_type = None # pragma: no cover
q2.old_imsi = None # pragma: no cover
elif q2.imsi is None and q2.export_status is None \
and q2.change_type is None and q2.old_imsi is not None:
# Condition for case where pair(s) is exported once and after that SIM-Change is
# requested but before MNO provides new IMSI, Pair is deleted.
q2.export_status = False
q2.change_type = "remove"
q2.imsi = q2.old_imsi
q2.old_imsi = None
q2.end_date = '{}'.format(strftime("%Y-%m-%d"))
db.session.commit()
else:
rel_all_cond = 2
else:
rel_all_cond = 3
if rel_all_cond == 1:
paircode = gen_paircode() # generating new pair-code & assigning it to the particular mobile device
chk_dev_id = Imei.query.filter(Imei.id == q1.imei_id).first()
add_pc = Pairing_Codes(pair_code=paircode,
is_active=True,
device_id=chk_dev_id.device_id)
db.session.add(add_pc)
db.session.commit()
rtn_msg = _("Release All-Pairs request is registered. New Pair Code is %(pc)s", pc=paircode)
elif rel_all_cond == 2:
chk_dev_id = Imei.query.filter(Imei.id == q1.imei_id).first()
chk_paircode = Pairing_Codes.query.filter(Pairing_Codes.device_id == chk_dev_id.device_id,
Pairing_Codes.is_active == True).first()
rtn_msg = _("Your new Pair-Code is %(pc)s. Release-All request is already registered and will be implemented within 24-48 hours",
pc=chk_paircode.pair_code)
elif rel_all_cond == 3:
rtn_msg = _("Release-All request not made by Primary-MSISDN")
return custom_text_response(rtn_msg, status=STATUS_CODES.get('OK'), mimetype=MIME_TYPES.get('TEXT'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()
def get(**kwargs):
"""method to search device(s)"""
try:
cases = []
para_cnt = len(kwargs)
fst = False
qry = "select contact, brand, model, serial_no, string_agg(imei,',') as imei, mac, " \
"pair_code, is_active from test_view where "
if para_cnt > 2:
for p in kwargs:
if p == "imei" and not fst: # building the database query
qry = qry + """{} = '{}' """.format(p, (kwargs.get(p)))
fst = True
elif p == "imei" and fst:
qry = qry + """ and {} = '{}' """.format(
p, (kwargs.get(p)))
elif p == "serial_no" and not fst:
qry = qry + """{} = '{}' """.format(p, (kwargs.get(p)))
fst = True
elif p == "serial_no" and fst: # pragma: no cover
qry = qry + """ and {} = '{}' """.format(
p, (kwargs.get(p)))
elif p == "mac" and not fst:
qry = qry + """{} = '{}' """.format(p, (kwargs.get(p)))
fst = True
elif p == "mac" and fst: # pragma: no cover
qry = qry + """ and {} = '{}' """.format(
p, (kwargs.get(p)))
elif p == "contact" and not fst:
qry = qry + """{} = '{}' """.format(p, (kwargs.get(p)))
fst = True
elif p == "contact" and fst:
qry = qry + """ and {} = '{}' """.format(
p, (kwargs.get(p)))
tmp_qry = qry + " group by serial_no, mac,contact, brand, model, pair_code, is_active ;"
chk_rslt = db.engine.execute(tmp_qry)
qry_count = chk_rslt.fetchall()
qry = qry + " group by serial_no, mac,contact, brand, model, pair_code," \
" is_active Limit {} offset {} ;".format(kwargs['limit'], kwargs['start'])
rslt = db.session.execute(qry)
for rows in rslt:
cases.append(dict((a, b) for a, b in rows.items()))
if cases:
paginated_data = {
'start': kwargs['start'],
'limit': kwargs['limit'],
'count': len(qry_count),
'cases': cases
}
return custom_text_response(
paginated_data,
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('JSON'))
else:
data = {
"start": kwargs['start'],
"cases": cases,
"count": 0,
"limit": kwargs['limit']
}
return custom_text_response(
data,
status=STATUS_CODES.get('OK'),
mimetype=MIME_TYPES.get('JSON'))
else:
return custom_json_response(
_("Please select any search parameter"),
status=STATUS_CODES.get('NOT_FOUND'),
mimetype=MIME_TYPES.get('JSON'))
except Exception as e:
db.session.rollback() # pragma: no cover
finally:
db.session.close()