def start_new_session(self):
with transaction.manager:
DBSession.query(Measure).delete()
HLRDBSession.query(Sms).delete()
HLRDBSession.query(Subscriber).filter(Subscriber.extension != self.pf_phone_number).delete()
return True
def start_new_session(self):
with transaction.manager:
DBSession.query(Measure).delete()
HLRDBSession.query(Sms).delete()
HLRDBSession.query(Subscriber).filter(
Subscriber.extension != self.pf_phone_number).delete()
return True
def configuration_post(request):
for setting in ("imsiUpdate", "smsUpdate", "silentSms", "welcomeMessage", "replyMessage"):
obj = DBSession.query(Settings).filter_by(name=setting).one()
obj.value = request.POST.get(setting)
DBSession.add(obj)
return configuration_get(request)
def get_imsi_circles(request):
imsi = request.matchdict['imsi']
ts_begin = request.GET.get('timestamp_begin')
ts_end = request.GET.get('timestamp_end')
query = DBSession.query(
Measure.distance, Measure.gps_lat, Measure.gps_lon,
Measure.timestamp).filter(
(Measure.imsi == imsi) & (Measure.gps_lat != None)
& (Measure.gps_lon != None)
& (Measure.timestamp <= datetime.fromtimestamp(
float(ts_end) / 1000))).order_by(Measure.timestamp.asc())
if ts_begin is not None:
query = query.filter(Measure.timestamp >= datetime.fromtimestamp(
float(ts_begin) / 1000))
result = {'imsi': imsi, 'circles': []}
for circle in query.all():
result['circles'].append({
'center': [circle.gps_lat, circle.gps_lon],
'radius':
circle.distance,
'ts':
time.mktime(circle.timestamp.timetuple())
})
return result
def get_last_measure(self, imsi):
last_measures = DBSession.query(Measure).filter(
Measure.imsi == imsi).order_by(Measure.id.desc()).limit(1).all()
if len(last_measures) == 0:
return None
return last_measures[0]
def get_imsi_list(request):
result = []
messages = []
query = DBSession.query(
Measure.id,
Measure.imsi,
func.max(Measure.timestamp).label('last')
).group_by(Measure.imsi).all()
for measure in query:
dtime = datetime.now() - measure.last
result.append({
'id': measure.id,
'imsi': measure.imsi,
'last_lur': dtime.total_seconds() // 60
})
# Calculate message count for each IMSI
imsi_list = map(lambda item: item['imsi'], result);
query = HLRDBSession.query(
Subscriber.imsi,
func.count(Sms.id).label('sms_count')
).filter((
(Sms.src_addr == Subscriber.extension) |
(Sms.dest_addr == Subscriber.extension)) &
(Sms.protocol_id != 64) &
(Subscriber.imsi.in_(imsi_list))
).group_by(
Subscriber.imsi
).all()
for record in query:
messages.append({
'imsi': int(record.imsi),
'count': record.sms_count
})
if 'jtSorting' in request.GET:
sorting_params = request.GET['jtSorting'].split(' ')
sorting_field = sorting_params[0]
reverse = sorting_params[1] == 'DESC'
result.sort(key=lambda x: x[sorting_field], reverse=reverse)
try:
gps_status = all(request.xmlrpc.get_current_gps())
gps_status = 'yes' if gps_status else 'no'
except (socket.error, xmlrpclib.Error) as e:
gps_status = 'failed'
return {
'Result': 'OK',
'Records': result,
'Messages': messages,
'GpsStatus': gps_status
}
def get_formated_reply_message(self, **wargs):
wargs["ph_phone_number"] = self.pf_phone_number
reply_message_res = DBSession.query(Settings.value).filter(Settings.name == "replyMessage").all()
if len(reply_message_res) != 1:
self.logger.error("Settings table not have replyMessage value!")
return None
return reply_message_res[0][0].format(**wargs)
def configuration_post(request):
for setting in ('imsiUpdate', 'smsUpdate', 'silentSms', 'welcomeMessage',
'replyMessage'):
obj = DBSession.query(Settings).filter_by(name=setting).one()
obj.value = request.POST.get(setting)
DBSession.add(obj)
return configuration_get(request)
def configuration_get(request):
query = DBSession.query(Settings.name, Settings.value)
result = {}
for setting in query.all():
result[setting.name] = setting.value
return result
def configuration_get(request):
query = DBSession.query(Settings.name, Settings.value)
result = {}
for setting in query.all():
result[setting.name] = setting.value
return result
def get_formated_reply_message(self, **wargs):
wargs["ph_phone_number"] = self.pf_phone_number
reply_message_res = DBSession.query(
Settings.value).filter(Settings.name == "replyMessage").all()
if len(reply_message_res) != 1:
self.logger.error("Settings table not have replyMessage value!")
return None
return reply_message_res[0][0].format(**wargs)
def get_last_measure(self, imsi):
last_measures = DBSession.query(
Measure
).filter(
Measure.imsi == imsi
).order_by(
Measure.id.desc()
).limit(1).all()
if len(last_measures) == 0:
return None
return last_measures[0]
def post(self):
self.set_header('Content-Type', 'application/json')
try:
print self.request.body
data = json.loads(self.request.body)
except ValueError as e:
self.set_status(400)
return self.write(
json.dumps({"message": "username and password are required!"}))
username = data.get("username", None)
password = data.get("password", None)
if username and password:
session = DBSession()
try:
user = session.query(User).filter(
User.username == username).one()
except Exception as e:
logger.error("query db failed, %s" % e.message)
user = None
self.set_status(403)
self.write(
json.dumps({"message": "user %s not exist!" % username}))
finally:
session.close()
if user:
if hashlib.sha256(password).hexdigest() == user.password:
payload = {
'id':
user.id,
'username':
username,
'exp':
datetime.datetime.utcnow() +
datetime.timedelta(seconds=43200),
}
token = jwt.encode(payload, SECRET_KEY, algorithm='HS256')
response = {'token': token}
self.write(response)
else:
self.set_status(403)
self.write(
json.dumps({"message": 'authentication failed!'}))
else:
self.set_status(400)
self.write(
json.dumps({"message": 'username and password are required.'}))
def get_imsi_list(request):
result = []
messages = []
query = DBSession.query(Measure.id, Measure.imsi,
func.max(
Measure.timestamp).label('last')).group_by(
Measure.imsi).all()
for measure in query:
dtime = datetime.now() - measure.last
result.append({
'id': measure.id,
'imsi': measure.imsi,
'last_lur': dtime.total_seconds() // 60
})
# Calculate message count for each IMSI
imsi_list = map(lambda item: item['imsi'], result)
query = HLRDBSession.query(
Subscriber.imsi,
func.count(Sms.id).label(
'sms_count')).filter(((Sms.src_addr == Subscriber.extension)
| (Sms.dest_addr == Subscriber.extension))
& (Sms.protocol_id != 64)
& (Subscriber.imsi.in_(imsi_list))).group_by(
Subscriber.imsi).all()
for record in query:
messages.append({'imsi': int(record.imsi), 'count': record.sms_count})
if 'jtSorting' in request.GET:
sorting_params = request.GET['jtSorting'].split(' ')
sorting_field = sorting_params[0]
reverse = sorting_params[1] == 'DESC'
result.sort(key=lambda x: x[sorting_field], reverse=reverse)
try:
gps_status = all(request.xmlrpc.get_current_gps())
gps_status = 'yes' if gps_status else 'no'
except (socket.error, xmlrpclib.Error) as e:
gps_status = 'failed'
return {
'Result': 'OK',
'Records': result,
'Messages': messages,
'GpsStatus': gps_status
}
def prepare_ready_for_silent_sms(self):
while self.__stop_trackin_event.is_set() is False:
sub_sms = HLRDBSession.query(
Subscriber.imsi,
Sms.dest_addr,
Sms.sent,
func.max(Sms.created)
).select_from(
Subscriber
).outerjoin(
Sms,
(
(Sms.dest_addr == Subscriber.extension) and
(Sms.protocol_id == 64)
)
).group_by(
Sms.dest_addr
).filter(
Subscriber.extension != self.pf_phone_number
).all()
for (imsi, dest_addr, sent, created) in sub_sms:
if dest_addr is not None:
#if (sent is not None) or (self.__tracking_cicle_number == 0):
if (sent is not None):
self.__imis_reday_for_silent_sms_list.put(imsi)
self.logger.debug("Tracking. Put imsi {0} to queue for send silent sms!".format(imsi))
else:
self.logger.debug("Tracking. Don't put imsi {0} to queue for send silent sms - not answer previous one!".format(imsi))
else:
self.logger.debug("Tracking. Put imsi {0} to queue for send silent sms!".format(imsi))
self.__imis_reday_for_silent_sms_list.put(imsi)
silent_sms_interval = DBSession.query(Settings.value).filter(Settings.name == "silentSms").all()
if len(silent_sms_interval) != 1:
self.logger.error("Settings table not have silentSms value!")
silent_sms_interval = self.measure_update_period
else:
silent_sms_interval = int(silent_sms_interval[0][0]) / 1000
time.sleep(silent_sms_interval)
self.__tracking_cicle_number += 1
class TaskAsyncHandler(RequestHandler):
executor = ThreadPoolExecutor(THREAD_POOL_MAX)
def __init__(self, *args, **kwargs):
self.session = DBSession()
super(TaskAsyncHandler, self).__init__(*args, **kwargs)
@gen.coroutine
def get(self):
self.set_header('Content-Type', 'application/json')
req_id = self.get_argument('id')
if req_id:
results = yield self.get_data(req_id)
results
self.write(json.dumps(results))
else:
self.write("id are required!")
self.finish()
@run_on_executor
def get_data(self, req_id):
"""
执行ansible playbook
Args:
resource: ditto
playbook: ditto
variable_dict: playbook variable dict
"""
results_objs = self.session.query(Record).filter(
Record.req == str(req_id)).all()
results_list = [{
'submit_time':
obj.submit_time.strftime('"%Y-%m-%d %H:%M:%S'),
'states':
obj.states,
'step':
obj.step,
'msg':
obj.msg
} for obj in results_objs]
return results_list
class SSHPrivateKey(RequestHandler, CommonMiXin):
executor = ThreadPoolExecutor(THREAD_POOL_MAX)
def __init__(self, *args, **kwargs):
self.session = DBSession()
super(SSHPrivateKey, self).__init__(*args, **kwargs)
@gen.coroutine
def get(self, id):
"""
download ssh private key
"""
try:
try:
ssh_key = self.session.query(SSHKey).get(id)
private_file = os.path.join(ssh_key.cache_dir, 'id_rsa')
username = ssh_key.username
except Exception as e:
raise WorkFlowError("query database error, %s" % e.message)
self.set_header('Content-Type', 'application/force-download')
self.set_header(
'Content-Disposition',
'attachment; filename=%s-%s' % (username, 'id_rsa'))
with open(private_file, "rb") as f:
try:
while True:
_buffer = f.read(4096)
if _buffer:
self.write(_buffer)
else:
f.close()
self.finish()
return
except Exception as e:
raise HTTPError(500,
reason='read file failed: %s' % e.message)
except Exception as e:
logger.error(e.message)
self.my_response(400, e.message)
def prepare_ready_for_silent_sms(self):
while self.__stop_trackin_event.is_set() is False:
sub_sms = HLRDBSession.query(
Subscriber.imsi, Sms.dest_addr, Sms.sent,
func.max(Sms.created)).select_from(Subscriber).outerjoin(
Sms,
((Sms.dest_addr == Subscriber.extension) and
(Sms.protocol_id == 64))).group_by(Sms.dest_addr).filter(
Subscriber.extension != self.pf_phone_number).all()
for (imsi, dest_addr, sent, created) in sub_sms:
if dest_addr is not None:
#if (sent is not None) or (self.__tracking_cicle_number == 0):
if (sent is not None):
self.__imis_reday_for_silent_sms_list.put(imsi)
self.logger.debug(
"Tracking. Put imsi {0} to queue for send silent sms!"
.format(imsi))
else:
self.logger.debug(
"Tracking. Don't put imsi {0} to queue for send silent sms - not answer previous one!"
.format(imsi))
else:
self.logger.debug(
"Tracking. Put imsi {0} to queue for send silent sms!".
format(imsi))
self.__imis_reday_for_silent_sms_list.put(imsi)
silent_sms_interval = DBSession.query(
Settings.value).filter(Settings.name == "silentSms").all()
if len(silent_sms_interval) != 1:
self.logger.error("Settings table not have silentSms value!")
silent_sms_interval = self.measure_update_period
else:
silent_sms_interval = int(silent_sms_interval[0][0]) / 1000
time.sleep(silent_sms_interval)
self.__tracking_cicle_number += 1
def get_imsi_circles(request):
imsi = request.matchdict['imsi']
ts_begin = request.GET.get('timestamp_begin')
ts_end = request.GET.get('timestamp_end')
query = DBSession.query(
Measure.distance,
Measure.gps_lat,
Measure.gps_lon,
Measure.timestamp
).filter(
(Measure.imsi == imsi) &
(Measure.gps_lat != None) &
(Measure.gps_lon != None) &
(Measure.timestamp <= datetime.fromtimestamp(float(ts_end) / 1000))
).order_by(Measure.timestamp.asc())
if ts_begin is not None:
query = query.filter(
Measure.timestamp >= datetime.fromtimestamp(float(ts_begin) / 1000)
)
result = {
'imsi': imsi,
'circles': []
}
for circle in query.all():
result['circles'].append({
'center': [
circle.gps_lat,
circle.gps_lon
],
'radius': circle.distance,
'ts': time.mktime(circle.timestamp.timetuple())
})
return result
def consultas(request):
retorno = DBSession.query(LogModel).all()
json_string = json.dumps([ob.__dict__ for ob in retorno], default=datetime_handler)
return Response(json_body=json.loads(json_string))
logger = logging_utils.get_logger("main")
logger.info("Comm interface started! pid: {0}".format(os.getpid()))
# Init DB ================================================================
pf_db_conn_str = None
try:
pf_db_conn_str = configuration.get('app:main', 'sqlalchemy.pf.url')
except ConfigParser.Error as err:
logger.error("Identification People Finder DB fail: {0}".format(err.message))
sys.exit(1)
logger.info("PF db sqlite path: {0}".format(pf_db_conn_str))
try:
bind_session(pf_db_conn_str)
DBSession.query(Measure).count()
DBSession.query(Settings).count()
except:
logger.error("People finder DB connection err")
raise
hlr_db_conn_str = None
try:
hlr_db_conn_str = configuration.get('app:main', 'sqlalchemy.hlr.url')
except ConfigParser.Error as err:
logger.error("Identification HLR fail: {0}".format(err.message))
sys.exit(1)
logger.info("HLR db sqlite path: {0}".format(hlr_db_conn_str))
try:
bind_hlr_session(hlr_db_conn_str)
