def get_local_msisdn(self, imsi): try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sq_hlr_cursor.execute( "SELECT extension FROM subscriber WHERE imsi=%(imsi)s AND lac > 0" % {'imsi': imsi}) connected = sq_hlr_cursor.fetchall() sq_hlr.close() if len(connected) <= 0: raise OsmoHlrError('imsi %s not found' % imsi) return connected[0] except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def get_all_5digits_inactive_since(self, days): try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sq_hlr_cursor.execute( "SELECT extension FROM subscriber WHERE length(extension) = 5 " "AND updated < date('now', '-%(days)s days')" % {'days': days}) inactive = sq_hlr_cursor.fetchall() sq_hlr.close() return inactive except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def get_imsi_from_msisdn(self, msisdn): try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sq_hlr_cursor.execute( 'SELECT extension,imsi from subscriber WHERE extension=?', [(msisdn)]) extension = sq_hlr_cursor.fetchone() if extension == None: raise NoDataException('Extension not found in the OsmoHLR') imsi = extension[1] except sqlite3.Error as e: raise OsmoHlrError('SQ_HLR error: %s' % e.args[0]) return str(imsi)
def get_all_expire(self): try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sq_hlr_cursor.execute( "SELECT extension,expire_lu FROM subscriber WHERE length(extension) = 11" ) subscribers = sq_hlr_cursor.fetchall() if subscribers == []: raise NoDataException('No subscribers found') else: sq_hlr.close() return subscribers except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def get_all_5digit_msisdns(self): try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sq_hlr_cursor.execute( "SELECT extension, imsi FROM subscriber WHERE length(extension) = 5" ) extensions = sq_hlr_cursor.fetchall() if extensions == []: raise NoDataException('No extensions found') else: sq_hlr.close() return extensions except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def get_all_inactive_roaming_msisdns(self, ignore_prefix): """Get all roaming msisdns (defined as length 11, unattached, from external prefix) , ignoring those beginning with the :ignore_prefix: """ try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sq_hlr_cursor.execute( "SELECT extension FROM subscriber WHERE length(extension) = 11 AND extension NOT LIKE '%s%%' AND lac = 0" % ignore_prefix) inactive = sq_hlr_cursor.fetchall() sq_hlr.close() return inactive except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def get_all_imeis(self): try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sql = 'SELECT DISTINCT Equipment.imei FROM Equipment ' sq_hlr_cursor.execute(sql) imeis = sq_hlr_cursor.fetchall() sq_hlr.close() if imeis == []: return [] if len(imeis) == 1: data = self.get_msisdn_from_imei(imeis[0][0]) return data else: return imeis except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def get_msisdn_from_imei(self, imei): try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sql = ('SELECT Equipment.imei, Subscriber.imsi, ' 'Subscriber.extension, Subscriber.updated ' 'FROM Equipment, EquipmentWatch, Subscriber ' 'WHERE EquipmentWatch.equipment_id=Equipment.id ' 'AND EquipmentWatch.subscriber_id=Subscriber.id ' 'AND Equipment.imei=? ' 'ORDER BY EquipmentWatch.updated DESC LIMIT 1;') print(sql) sq_hlr_cursor.execute(sql, [(imei)]) extensions = sq_hlr_cursor.fetchall() sq_hlr.close() return extensions except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def get_all_inactive_msisdns_since(self, days, ignore_prefix): """Get all msisdns that have been inactive for :days:, ignoring those beginning with the :ignore_prefix: """ try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sq_hlr_cursor.execute( "SELECT extension FROM subscriber WHERE (length(extension) = 5 OR extension NOT LIKE \"%(prefix)s%%\") AND updated < date('now', '-%(days)s days')" % { 'days': days, 'prefix': ignore_prefix }) inactive = sq_hlr_cursor.fetchall() sq_hlr.close() return inactive except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def get_all_inactive_roaming_msisdns_since(self, days, ignore_prefix): """Get all roaming msisdns (defined as length 11, unattached, from external prefix) that have been inactive for :days:, ignoring those beginning with the :ignore_prefix: """ try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() _sql = ( "SELECT extension FROM subscriber WHERE length(extension) = 11 AND extension NOT LIKE \"%(prefix)s%%\" AND lac = 0 AND updated < date('now', '-%(days)s days')" % { 'days': days, 'prefix': ignore_prefix }) sq_hlr_cursor.execute(_sql) inactive = sq_hlr_cursor.fetchall() sq_hlr.close() return inactive except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def get_matching_partial_imeis(self, partial_imei=''): try: sq_hlr = sqlite3.connect(self.hlr_db_path) sq_hlr_cursor = sq_hlr.cursor() sql = 'SELECT DISTINCT Equipment.imei FROM Equipment ' if partial_imei != '': sql += 'WHERE Equipment.imei LIKE ? ORDER BY Equipment.imei ASC' sq_hlr_cursor.execute(sql, [(partial_imei + '%')]) else: sq_hlr_cursor.execute(sql) imeis = sq_hlr_cursor.fetchall() sq_hlr.close() if imeis == []: return [] if len(imeis) == 1: data = self.get_msisdn_from_imei(imeis[0][0]) return data else: return imeis except sqlite3.Error as e: sq_hlr.close() raise OsmoHlrError('SQ_HLR error: %s' % e.args[0])
def _open_sqlite_connection(path): try: return sqlite3.connect(path) except sqlite3.Error as e: raise OsmoHlrError("SQ_HLR connect error: {}".format(e))