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): """ Overrirdes Dimension._matching_imeis_sql. :param conn: database connection :param app_config: dirbs config obj :param virt_imei_range_start: start of imei shard range :param virt_imei_range_end: end of imei shard range :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) association_list_shard = partition_utils.imei_shard_name(base_name='historic_device_association_list', virt_imei_range_start=virt_imei_range_start, virt_imei_range_end=virt_imei_range_end) classification_query = sql.SQL("""SELECT imei_norm FROM {network_imeis_shard} WHERE NOT EXISTS(SELECT imei_norm FROM {association_shard} WHERE imei_norm = {network_imeis_shard}.imei_norm AND end_date IS NULL)""").format( # noqa: Q449 network_imeis_shard=sql.Identifier(network_imeis_shard), association_shard=sql.Identifier(association_list_shard)) classification_query = classification_query.as_string(conn) if len(app_config.region_config.exempted_device_types) > 0: classification_query = filter_imei_list_sql_by_device_type(conn, app_config.region_config.exempted_device_types, classification_query) return classification_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)