def is_in_registration_list(db_conn, cursor, imei_norm: str) -> bool:
"""
Method to check if an IMEI exists in the Registration List.
TODO: use either of "db_conn, cursor" in the function.
Arguments:
db_conn: PostgreSQL database connection object
cursor: PostgreSQL connection cursor
imei_norm: normalized IMEI
Returns:
is_in_registration_list: Boolean
"""
cursor.execute(sql.SQL("""SELECT EXISTS(SELECT 1
FROM registration_list
WHERE imei_norm = %(imei_norm)s
AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)
AND {wl_status_filter}) AS in_registration_list""") # noqa: Q449
.format(wl_status_filter=registration_list_status_filter_sql()), {'imei_norm': imei_norm})
in_registration_list = cursor.fetchone().in_registration_list
exempted_device_types = current_app.config['DIRBS_CONFIG'].region_config.exempted_device_types
if not in_registration_list and len(exempted_device_types) > 0:
imei_sql = str(cursor.mogrify("""SELECT %s::TEXT AS imei_norm""", [imei_norm]), db_conn.encoding)
sql_query = filter_imei_list_sql_by_device_type(db_conn,
exempted_device_types,
imei_sql)
cursor.execute(sql_query)
# The IMEI is returned if it does not belong to an exempted device type.
# As the IMEI was not in registration list and is not exempted,
# the in_registration_list value would be set to False.
in_registration_list = cursor.fetchone() is None
return in_registration_list
def is_in_registration_list(db_conn, cursor, imei_norm):
"""
Method to check if an IMEI exists in the Registration List.
:param db_conn: database connection
:param cursor: database cursor
:param imei_norm: normalized imei
:return: bool
"""
cursor.execute(
sql.SQL("""SELECT EXISTS(SELECT 1
FROM registration_list
WHERE imei_norm = %(imei_norm)s
AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)
AND {wl_status_filter}) AS in_registration_list"""
).format(
wl_status_filter=registration_list_status_filter_sql()),
{'imei_norm': imei_norm})
in_registration_list = cursor.fetchone().in_registration_list
exempted_device_types = current_app.config[
'DIRBS_CONFIG'].region_config.exempted_device_types
if not in_registration_list and len(exempted_device_types) > 0:
imei_sql = str(
cursor.mogrify("""SELECT %s::TEXT AS imei_norm""", [imei_norm]),
db_conn.encoding)
sql_query = filter_imei_list_sql_by_device_type(
db_conn, exempted_device_types, imei_sql)
cursor.execute(sql_query)
# The IMEI is returned if it does not belong to an exempted device type.
# As the IMEI was not in registration list and is not exempted,
# the in_registration_list value would be set to False.
in_registration_list = cursor.fetchone() is None
return in_registration_list
def _matching_imeis_sql(self, conn, app_config, virt_imei_range_start, virt_imei_range_end, curr_date=None):
"""Overrides Dimension._matching_imeis_sql."""
network_imeis_shard = partition_utils.imei_shard_name(base_name='network_imeis',
virt_imei_range_start=virt_imei_range_start,
virt_imei_range_end=virt_imei_range_end)
registration_list_shard = partition_utils.imei_shard_name(base_name='historic_registration_list',
virt_imei_range_start=virt_imei_range_start,
virt_imei_range_end=virt_imei_range_end)
sql_query = sql.SQL("""SELECT imei_norm
FROM {network_imeis_shard}
WHERE NOT EXISTS(SELECT imei_norm
FROM {reg_list_shard}
WHERE imei_norm = {network_imeis_shard}.imei_norm
AND end_date IS NULL
AND {wl_status_filter})""") \
.format(network_imeis_shard=sql.Identifier(network_imeis_shard),
reg_list_shard=sql.Identifier(registration_list_shard),
wl_status_filter=registration_list_status_filter_sql())
sql_query = sql_query.as_string(conn)
if len(app_config.region_config.exempted_device_types) > 0:
sql_query = filter_imei_list_sql_by_device_type(conn, app_config.region_config.exempted_device_types,
sql_query)
return sql_query
def _matching_imeis_sql(self,
conn,
app_config,
virt_imei_range_start,
virt_imei_range_end,
curr_date=None):
"""
Overrides Dimension._matching_imeis_sql.
:param conn: database connection
:param app_config: dirbs config obj
:param virt_imei_range_start: virtual imei shard range start
:param virt_imei_range_end: virtual imei shard range end
:param curr_date: user defined current date
:return: SQL
"""
network_imeis_shard = partition_utils.imei_shard_name(
base_name='network_imeis',
virt_imei_range_start=virt_imei_range_start,
virt_imei_range_end=virt_imei_range_end)
registration_list_shard = partition_utils.imei_shard_name(
base_name='historic_registration_list',
virt_imei_range_start=virt_imei_range_start,
virt_imei_range_end=virt_imei_range_end)
sql_query = sql.SQL(
"""SELECT imei_norm
FROM {network_imeis_shard}
WHERE NOT EXISTS(SELECT imei_norm
FROM {reg_list_shard}
WHERE imei_norm = {network_imeis_shard}.imei_norm
AND end_date IS NULL
AND {wl_status_filter})"""
).format( # noqa: Q449
network_imeis_shard=sql.Identifier(network_imeis_shard),
reg_list_shard=sql.Identifier(registration_list_shard),
wl_status_filter=registration_list_status_filter_sql())
sql_query = sql_query.as_string(conn)
if len(app_config.region_config.exempted_device_types) > 0:
sql_query = filter_imei_list_sql_by_device_type(
conn, app_config.region_config.exempted_device_types,
sql_query)
return sql_query
def api(imei, include_seen_with=False, include_paired_with=False):
"""IMEI API common functionality."""
if len(imei) > 16:
abort(400, 'Bad IMEI format (too long)')
if re.match(r'^\d{14}', imei):
imei_norm = imei[:14]
else:
imei_norm = imei.upper()
tac = imei_norm[:8]
with get_db_connection() as db_conn, db_conn.cursor() as cursor:
cursor.execute(
'SELECT NOT EXISTS (SELECT * FROM gsma_data WHERE tac = %s) AS not_in_gsma',
[tac])
rt_gsma_not_found = cursor.fetchone()[0]
conditions = current_app.config['DIRBS_CONFIG'].conditions
condition_results = {
c.label: {
'blocking': c.blocking,
'result': False
}
for c in conditions
}
cursor.execute(
"""SELECT cond_name
FROM classification_state
WHERE imei_norm = %(imei_norm)s
AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)
AND end_date IS NULL""", {'imei_norm': imei_norm})
for res in cursor:
# Handle conditions no longer in the config
if res.cond_name in condition_results:
condition_results[res.cond_name]['result'] = True
resp = {
'imei_norm': imei_norm,
'classification_state': {
'blocking_conditions': {
k: v['result']
for k, v in condition_results.items() if v['blocking']
},
'informative_conditions': {
k: v['result']
for k, v in condition_results.items() if not v['blocking']
}
},
'realtime_checks': {
'invalid_imei':
False if re.match(r'^\d{14}$', imei_norm) else True,
'gsma_not_found': rt_gsma_not_found
}
}
# add a real-time check for the registration list
cursor.execute(
sql.SQL(
"""SELECT EXISTS(SELECT 1
FROM registration_list
WHERE imei_norm = %(imei_norm)s
AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)
AND {wl_status_filter}) AS in_registration_list"""
).format(wl_status_filter=registration_list_status_filter_sql()),
{'imei_norm': imei_norm})
in_registration_list = cursor.fetchone().in_registration_list
exempted_device_types = current_app.config[
'DIRBS_CONFIG'].region_config.exempted_device_types
if not in_registration_list and len(exempted_device_types) > 0:
imei_sql = str(
cursor.mogrify("""SELECT %s::TEXT AS imei_norm""",
[imei_norm]), db_conn.encoding)
sql_query = filter_imei_list_sql_by_device_type(
db_conn, exempted_device_types, imei_sql)
cursor.execute(sql_query)
# The IMEI is returned if it does not belong to an exempted device type.
# As the IMEI was not in registration list and is not exempted,
# the in_registration_list value would be set to False.
in_registration_list = cursor.fetchone() is None
resp['realtime_checks']['in_registration_list'] = in_registration_list
# add a real-time check for if IMEI was ever observed on the network
cursor.execute(
"""SELECT EXISTS(SELECT 1
FROM network_imeis
WHERE imei_norm = %(imei_norm)s
AND virt_imei_shard =
calc_virt_imei_shard(%(imei_norm)s)) AS ever_observed_on_network
""", {'imei_norm': imei_norm})
ever_observed_on_network = cursor.fetchone().ever_observed_on_network
resp['realtime_checks'][
'ever_observed_on_network'] = ever_observed_on_network
if include_seen_with:
cursor.execute(
"""SELECT DISTINCT imsi, msisdn
FROM monthly_network_triplets_country_no_null_imeis
WHERE imei_norm = %(imei_norm)s
AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)""",
{'imei_norm': imei_norm})
resp['seen_with'] = [{
'imsi': x.imsi,
'msisdn': x.msisdn
} for x in cursor]
cursor.execute(
"""SELECT EXISTS(SELECT 1
FROM pairing_list
WHERE imei_norm = %(imei_norm)s
AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s))""",
{'imei_norm': imei_norm})
resp['is_paired'] = [x.exists for x in cursor][0]
if include_paired_with:
cursor.execute(
"""SELECT imsi
FROM pairing_list
WHERE imei_norm = %(imei_norm)s
AND virt_imei_shard = calc_virt_imei_shard(%(imei_norm)s)""",
{'imei_norm': imei_norm})
resp['paired_with'] = [x.imsi for x in cursor]
return jsonify(IMEI().dump(resp).data)
