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