def register():
click.echo("DPS PROCESS Initiated")
try:
con = connect()
cur = con.cursor()
dps_api_params = {"params_list": []}
params = {}
cur.execute(
"""SELECT imei, msisdn FROM duplication_list WHERE imei_status = true
AND export_status IS NOT TRUE """)
qry_dup = cur.fetchall()
if qry_dup:
for q in qry_dup:
params['contact_no'] = q[1]
cur.execute(
"""SELECT oem_serial_no, oem_brand, oem_model, oem_mac, oem_rat, oem_all_imeis
FROM oem_response
WHERE oem_imei = '{imei}'
""".format(imei=q[0]))
qry_oem = cur.fetchall()
for o in qry_oem:
params["serial_no"] = o[0]
params["brand"] = o[1]
params["model"] = o[2]
params["mac"] = o[3]
params["rat"] = o[4]
params["imei"] = o[5]
dps_api_params["params_list"].append(dict(params))
headers = {'content-type': 'application/json'}
for p in dps_api_params["params_list"]:
# print(type(p), p)
print(
"Registering device having IMEI(s) '{imei}' for MSISDN '{msisdn}' with DPS...."
.format(imei=p["imei"], msisdn=p['contact_no']))
result = requests.post(url=conf['dps_api'],
json=p,
headers=headers)
print(result.status_code, result.text, "\n")
# pprint.pprint(dps_api_params)
else:
click.echo("No genuine IMEI exists in DB !!!")
cur.execute(
"""UPDATE duplication_list SET export_status = TRUE WHERE imei_status = TRUE
AND export_status IS NOT TRUE """)
con.commit()
cur.close()
con.close()
except Exception as e:
app.logger.info("Error occurred creating Lists.")
app.logger.exception(e)
def compare():
"""Running comparison-algorithm to compare & evaluate user & oem responses."""
print("Running Comparison........")
con = connect()
cur = con.cursor()
cur.execute("""select distinct user_response.uid, user_response.user_imeis,
oem_response.oem_serial_no, oem_response.oem_all_imeis
from oem_response
INNER JOIN user_response
ON user_response.user_serial_no = oem_response.oem_serial_no
AND user_response.user_imeis @> oem_response.oem_all_imeis
AND user_response.user_imeis <@ oem_response.oem_all_imeis
AND oem_response.oem_all_imeis @> user_response.user_imeis
AND oem_response.oem_all_imeis <@ user_response.user_imeis
AND user_response.uid_status is NULL
""")
serial_nos = cur.fetchall()
for row in serial_nos:
c = set(row[3]) == set(row[1])
if c:
cur.execute("""UPDATE duplication_list SET imei_status = TRUE where uid = '{u}' """.format(u=row[0]))
cur.execute("""UPDATE user_response SET uid_status = TRUE where uid = '{u}' """.format(u=row[0]))
else:
cur.execute("""UPDATE duplication_list SET imei_status = FALSE where uid = '{u}' """.format(u=row[0]))
cur.execute("""UPDATE user_response SET uid_status = FALSE where uid = '{u}' """.format(u=row[0]))
cur.execute("""UPDATE duplication_list SET imei_status = FALSE
FROM user_response
WHERE user_response.uid = duplication_list.uid
AND duplication_list.imei_status IS NULL
AND user_response.user_serial_no IS NOT NULL;
""")
cur.execute("""UPDATE duplication_list SET imei_status = null
FROM oem_response
WHERE oem_response.oem_imei = duplication_list.imei
AND duplication_list.imei_status = FALSE
AND oem_response.oem_serial_no IS NULL;
""")
cur.execute("""UPDATE user_response SET uid_status = duplication_list.imei_status
FROM duplication_list
WHERE user_response.uid = duplication_list.uid
AND user_response.uid_status IS NULL
AND duplication_list.imei_status IS NOT NULL;
""")
con.commit()
cur.close()
con.close()
return
def imeis_loader():
"""method to load unique IMEIs for OEMs."""
con = connect()
cur = con.cursor()
cur.execute("""INSERT INTO oem_response(oem_imei, oem_tac)
SELECT DISTINCT imei, LEFT(imei, 8)
FROM test_list
ON CONFLICT (oem_imei)
DO NOTHING;
""")
cur.execute("""INSERT INTO duplication_list(imei, imsi, msisdn, mno, uid)
SELECT imei, imsi, msisdn, operator, uid
FROM test_list;
""")
cur.execute("""UPDATE duplication_list SET list_upload_date = '{lud}'
WHERE imei IS NOT NULL; """.format(lud=strftime("%Y-%m-%d %H:%M:%S")))
cur.execute("""INSERT INTO user_response(uid)
SELECT uid
FROM test_list
ON CONFLICT (uid)
DO NOTHING;
""")
cur.execute("""UPDATE oem_response SET gsma_brand = gsma_tac_data.manufacturer
FROM gsma_tac_data
WHERE oem_response.oem_tac = gsma_tac_data.tac
AND oem_response.oem_id IS NULL;
""")
cur.execute("""select gsma_brand from oem_response where oem_id is null; """)
t_brands = cur.fetchall()
if t_brands:
for t in t_brands:
brand = BrandsModifier.manufacturer_modifications(t)
cur.execute("""update oem_response set gsma_brand = '{b}' where gsma_brand = '{g}';""".format(b=brand,
g=t[0]))
cur.execute("""UPDATE oem_response SET oem_id = brand_mapping.oem_id
FROM brand_mapping
WHERE oem_response.gsma_brand = brand_mapping.brand_name
AND oem_response.oem_id IS NULL;
""")
con.commit()
cur.close()
con.close()
return
def generate():
"""Creating Black-List & Pairing-List from Duplication_list Table."""
try:
DOWNLOAD_PATH = conf['GDDS_Lists']
con = connect()
cur = con.cursor()
cur.execute("""SELECT imei FROM duplication_list WHERE imei_status IS FALSE """)
imei_list = set([l[0] for l in cur.fetchall()])
if imei_list:
black_list = "Black-List_" + strftime("%Y-%m-%d_%H-%M-%S") + '.csv'
bl_path = os.path.join(DOWNLOAD_PATH, black_list)
with open(bl_path, 'w') as bl:
bl.write('imei,reason\n')
for imei in imei_list:
bl.write(imei + ',duplicated\n')
# bl.write(",duplicated\n".join(imei_list))
bl.close()
pair_list = "Pair-List_" + strftime("%Y-%m-%d_%H-%M-%S") + '.csv'
pl_path = os.path.join(DOWNLOAD_PATH, pair_list)
if conf['pair_list_triplet']: params = "imei,imsi,msisdn"
else: params = "imei,imsi"
cur.execute("""SELECT {p} FROM duplication_list WHERE imei_status IS TRUE """.format(p=params))
pairs = cur.fetchall()
with open(pl_path, 'w') as file:
file.write(params + '\n')
for row in pairs:
if conf['pair_list_triplet']:
file.write(row[0] + ',' + row[1] + ',' + row[2] + '\n')
else:
file.write(row[0] + ',' + row[1] + '\n')
file.close()
print("Files successfully created")
return
except Exception as e:
app.logger.info(_("Error occurred creating Lists."))
app.logger.exception(e)
def send_sms(sms_to):
"""Sending SMS to all users in duplication_list Table."""
con = connect()
cur = con.cursor()
for mno in conf['mnos']:
if sms_to == "all":
cur.execute(
"""SELECT msisdn, uid, mno, sms_notification FROM duplication_list WHERE imei_status IS NULL AND
mno = '{mno}'; """.format(mno=mno))
elif sms_to == "unnotified":
cur.execute(
"""SELECT msisdn, uid, mno, sms_notification FROM duplication_list WHERE imei_status IS NULL AND
sms_notification IS NULL AND mno = '{mno}'; """.
format(mno=mno))
else:
print("invalid parameter \"{}\" for command \"send-request-sms\" ".
format(sms_to))
return
msisdns = cur.fetchall()
print(mno)
for m in msisdns:
msisdn = "0" + m[0][2:]
if m[2] in conf['mnos']:
MNO_SMSC = m[2]
else:
MNO_SMSC = conf['mnos'][0]
sms = conf['SMS_Text']['initial_sms'] + m[1] + conf['SMS_Text'][
'link_text']
sms_intimation(msisdn, sms, MNO_SMSC)
if m[3] is None:
cur.execute(
"""UPDATE duplication_list SET sms_notification = true where uid = '{}' """
.format(m[1]))
con.commit()
cur.close()
con.close()
return
def intimation(user_type):
"""Sending SMS to all users in duplication_list Table on the basis of their IMEI Status."""
con = connect()
cur = con.cursor()
if user_type == "duplicated":
cur.execute(
"""SELECT imei, msisdn, imei_status, mno FROM duplication_list WHERE imei_status IS FALSE; """
)
qry = cur.fetchall()
for q in qry:
sms = "Your IMEI '{imei}' is marked duplicated and will be blocked in next 24 hours".format(
imei=q[0])
kannel_sms(q[1], sms, q[3])
elif user_type == "genuine":
cur.execute(
"""SELECT imei, msisdn, imei_status, mno FROM duplication_list WHERE imei_status IS TRUE; """
)
qry = cur.fetchall()
for q in qry:
sms = "Your IMEI '{imei}' is declared Genuine and will be paired in next 24 hours".format(
imei=q[0])
kannel_sms(q[1], sms, q[3])
else:
print("invalid parameter \"{}\" for command \"send-intimation-sms\" ".
format(user_type))
con.commit()
cur.close()
con.close()
return
conf['db_username'], conf['db_password'], conf['db_host'], conf['db_name'])
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_POOL_SIZE'] = int(conf['pool_size'])
app.config['SQLALCHEMY_POOL_RECYCLE'] = int(conf['pool_recycle'])
app.config['SQLALCHEMY_MAX_OVERFLOW'] = int(conf['overflow_size'])
app.config['SQLALCHEMY_POOL_TIMEOUT'] = int(conf['pool_timeout'])
app.config['BABEL_DEFAULT_LOCALE'] = conf['supported_languages'][
'default_language']
app.config['SUPPORTED_LANGUAGES'] = conf['supported_languages']
db = SQLAlchemy()
db.init_app(app)
from gdds.app.api.common.db_connection import connect
pg_connect = connect()
from gdds.app.api.routes import *
@babel.localeselector
def get_locale():
return request.accept_languages.best_match(
app.config['SUPPORTED_LANGUAGES'])
@app.after_request
def add_no_cache(response):
"""Making sure no API responses are cached by setting headers on the response."""
response.cache_control.no_cache = True
response.cache_control.no_store = True
def list_processor(f_path, filename):
"""method to accept & process duplication List."""
try:
if file_allowed(filename):
filepath = os.path.join(f_path, filename)
tmp_dir = tempfile.mkdtemp()
temp_path = os.path.join(tmp_dir, filename)
try:
filetype = magic.from_file(filepath, mime=True)
if filename != '' and filetype == 'text/plain':
try:
with open(filepath, 'r') as newfile:
df = pd.read_csv(newfile, usecols=range(4), dtype=dict(imei=str, imsi=str,
msisdn=str, operator=str))
newfile.close()
except Exception as e:
if e:
newfile.close()
return "File content is not Correct", 403
total_rows, total_columns = df.shape
if df.columns[0] == 'imei' and df.columns[1] == 'imsi' and df.columns[2] == 'msisdn' and \
df.columns[3] == 'operator':
if not df.empty:
df['imei'] = df['imei'].str.strip()
df['imsi'] = df['imsi'].str.strip()
df['msisdn'] = df['msisdn'].str.strip()
df['operator'] = df['operator'].str.strip()
df1 = df[df.isnull().any(axis=1)] # to detect null values in any column
df2 = df.dropna() # to drop rows with one or more null values
df3 = df2[~(df2.msisdn.astype(str).str.match(conf['validation_regex']['msisdn']))]
df2 = df2[(df2.msisdn.astype(str).str.match(conf['validation_regex']['msisdn']))]
df4 = df2[~(df2.imei.astype(str).str.match(conf['validation_regex']['imei']))]
df2 = df2[(df2.imei.astype(str).str.match(conf['validation_regex']['imei']))]
df5 = df2[~(df2.imsi.astype(str).str.match(conf['validation_regex']['imsi']))]
df2 = df2[(df2.imsi.astype(str).str.match(conf['validation_regex']['imsi']))]
final_rows, final_columns = df2.shape
del_rec = (total_rows - final_rows)
df2['uid'] = [str(uuid.uuid4())[24:] for x in range(len(df2))] + \
df2['msisdn'].str[-6:]
df2.to_csv(temp_path, index=False, header=False)
lst_df = [df1, df3, df4, df5]
dfs = pd.concat(lst_df, ignore_index=False)
con = connect()
cur = con.cursor()
cur.execute(""" CREATE TABLE if not exists test_list (imei VARCHAR(50),
imsi VARCHAR(50),
msisdn VARCHAR(50),
operator VARCHAR(200),
uid VARCHAR(50),
t_time TIMESTAMP) """)
con.commit()
f = open(temp_path)
cur.copy_from(f, 'test_list', sep=",", columns=('imei', 'imsi', 'msisdn',
'operator', 'uid'))
con.commit()
f.close()
OemImeis.imeis_loader()
cur.execute(""" drop table if exists test_list;""")
con.commit()
cur.close()
con.close()
if del_rec != 0:
error_file = "Error-File_" + strftime("%Y-%m-%d_%H-%M-%S") + '.csv'
download_path = os.path.join(DOWNLOAD_FOLDER, error_file)
with open(download_path, 'w') as ff:
dfs.to_csv(download_path, index=False)
return "Duplication-List loaded successfully\nTotal_Records : {}\n" \
"Successful_Records : {}\nDeleted_Record : {}\n" \
"link to Error-File : {}".format(total_rows, final_rows, del_rec,
download_path)
else:
return "Duplication-List loaded successfully without errors", 200
else:
return "File is empty", 403
else:
return "File headers are incorrect", 403
else:
return "System only accepts csv/txt files", 403
finally:
rmtree(tmp_dir)
else:
return "Please select csv/txt file", 422
except Exception as e:
app.logger.info("Error occurred while processing duplication-list.")
app.logger.exception(e)
cur.close()
con.close()
return "Failed to process Duplication-List.", 422
def list_update(f_path, filename):
"""method to accept and process gsma_tac_data file and update the brand_mapping table"""
try:
if Miscellaneous.file_allowed(filename):
filepath = os.path.join(f_path, filename)
tmp_dir = tempfile.mkdtemp()
temp_path = os.path.join(tmp_dir, filename)
print("Reading file .................................")
filetype = magic.from_file(filepath, mime=True)
if filename != '' and filetype == 'text/plain':
try:
spaces = [
'', ' ', ' ', ' ', ' ', ' ', ' ',
' ', ' ', ' '
]
with open(filepath, 'r') as newfile:
df = pd.read_csv(newfile,
sep='|',
dtype=dict(TAC=str),
na_values=spaces,
keep_default_na=True)
newfile.close()
except Exception as e:
if e:
newfile.close()
return "File content is not Correct", 403
if not df.empty:
df['Manufacturer'] = df['Manufacturer'].str.strip()
df1 = df[df['Manufacturer'].isnull(
)] # to detect null values in any column
df3 = df[~(df.TAC.astype(str).str.
match(conf['validation_regex']['tac']))]
if df1.empty and df3.empty:
con = connect()
cur = con.cursor()
Miscellaneous.create_temp_table(cur)
df.to_csv(temp_path,
index=False,
header=False,
sep='|')
with open(temp_path, 'r') as f:
cur.copy_from(f, 'test_gsma_tac_data', sep='|')
f.close()
Miscellaneous.copy_temp_table(cur)
cur.execute(
"""drop table if exists test_gsma_tac_data; """
)
# Miscellaneous.brands_mapper(cur)
print(
"File processing started .............................."
)
cur.execute(
"""SELECT DISTINCT manufacturer FROM gsma_tac_data; """
)
t_manufacturer = cur.fetchall()
manufacturer = Miscellaneous.manufacturer_modifications(
t_manufacturer)
cur.execute(
"""SELECT brand_name FROM brand_mapping; """)
tmp_brands = cur.fetchall()
brands = [t[0] for t in tmp_brands]
t_brands = list(set(manufacturer) - set(brands))
new_brands = set(t_brands)
print(
"---------------------------------------------"
)
print("New Brands to insert :")
for b in new_brands:
cur.execute(
"""INSERT INTO brand_mapping(brand_name) VALUES ('{}'); """
.format(b))
print(b)
print(
"---------------------------------------------"
)
print("Total New Brands added : ", len(new_brands))
print(
"---------------------------------------------"
)
con.commit()
cur.close()
con.close()
return "GSMA TAC Database successfully updated", 200
else:
return "File contains Invalid TACs or Manufacturer names", 403
else:
return "GSMA-TAC File is empty", 403
else:
return "System only accepts csv/txt files", 403
else:
return "Please select csv/txt file", 422
except Exception as e:
app.logger.info("Error occurred while processing TAC-Data-File.")
app.logger.exception(e)
finally:
rmtree(tmp_dir)
return
def put():
"""method to check and upload a 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(8),
dtype={
"IMEI": str,
"Serial_no": str,
"Color": str,
"Brand": str,
"Model": str,
"RAT": str,
"MAC": str,
"Other_IMEIs": 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] == 'IMEI' and df.columns[1] == 'Serial_no' and df.columns[2] == 'Color' and \
df.columns[3] == 'Brand' and df.columns[4] == 'Model' and df.columns[5] == 'RAT' and \
df.columns[6] == 'MAC' and df.columns[7] == 'Other_IMEIs':
if not df.empty:
df['Serial_no'] = df['Serial_no'].str.strip()
df1 = df[df.isnull().any(
axis=1
)] # to detect null values in any column
# df2 = df.dropna() # to drop rows with one or more null values
df2 = df
df3 = df2[~(df2.IMEI.astype(str).str.match(
conf['validation_regex']['imei']))]
df2 = df2[(df2.IMEI.astype(str).str.match(
conf['validation_regex']['imei']))]
df4 = df2[~(df2.Serial_no.astype(str).str.
match(conf['validation_regex']
['serial_no']))]
df2 = df2[(df2.Serial_no.astype(str).str.match(
conf['validation_regex']['serial_no']))]
final_rows, final_columns = df2.shape
del_rec = (total_rows - final_rows)
df2.to_csv(filepath, index=False, header=False)
lst_df = [df1, 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_response (t_imei text, t_serial text,
t_color text, t_brand text, t_model text, t_rat text, t_mac text,
t_other_imeis text)""")
f = open(filename1)
cur.copy_from(f, 'test_response', sep=",")
cur.execute(
"""UPDATE oem_response SET oem_serial_no = test_response.t_serial,
oem_color = test_response.t_color, oem_brand = test_response.t_brand,
oem_model = test_response.t_model, oem_rat = test_response.t_rat,
oem_mac = test_response.t_mac,
oem_other_imeis = string_to_array(test_response.t_other_imeis, '|'),
oem_response_date = '{}'
FROM test_response
WHERE oem_imei = test_response.t_imei"""
.format(strftime("%Y-%m-%d %H:%M:%S")))
cur.execute(
"""UPDATE oem_response SET oem_all_imeis = array_append(oem_other_imeis,
oem_imei)
FROM test_response
WHERE oem_imei = test_response.t_imei """
)
cur.execute(
""" drop table if exists test_response; """
)
con.commit()
cur.close()
con.close()
f.close()
if del_rec != 0:
error_file = "Error-File" + 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":
_("Duplication-List 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 loaded successfully without errors"
), STATUS_CODES.get('OK'),
MIME_TYPES.get('JSON'))
else:
return custom_json_response(
_("File is empty"),
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'))
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:
app.logger.info("Error occurred while uploading a file")
app.logger.exception(e)
return custom_json_response(
_("Failed to upload a file."),
STATUS_CODES.get('SERVICE_UNAVAILABLE'),
MIME_TYPES.get('JSON'))