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))