def modify_terminal(): db = DBConnection().db redis = MyRedis() mobile='14778746907' #mobile='14778741845' #mobile='14778749172' #mobile='14778746786' #mobile='14778740942' #mobile='14778745985' #mobile='14778744628' #mobile='14778744861' #mobile='14778742261' #mobile='14778744473' #mobile='14778747112' #mobile='14778745219' #mobile='14778742290' #mobile='14778749137' #mobile='14778742587' #mobile='14778745073' #mobile='14778747467' #mobile='14778741340' #mobile='14778748943' #mobile='14778743681' sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where mobile= %s" terminals = db.query(sql, mobile) #print 'len ', len(terminals) count = 0 cnt = 0 no_loc = 0 for i, t in enumerate(terminals): tid = t.tid mobile = t.mobile owner_mobile = t.owner_mobile terminal_info_key = get_terminal_info_key(tid) terminal_info = redis.getvalue(terminal_info_key) if terminal_info: print 'umobile in redis:%s, umobile in db:%s' % (terminal_info['owner_mobile'], owner_mobile) if terminal_info['owner_mobile'] != owner_mobile: print 'mobile: %s, umobile in redis:%s, umobile in db:%s' % (mobile, terminal_info['owner_mobile'], owner_mobile) cnt = cnt + 1 terminal_info['owner_mobile'] = owner_mobile redis.setvalue(terminal_info_key, terminal_info) else: pass print 'count:', count print 'cnt:', cnt
def wash_location(): db = DBConnection().db redis = MyRedis() sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO " #print 'sql', sql terminals = db.query(sql) #print 'len ', len(terminals) count = 0 cnt = 0 for i, t in enumerate(terminals): tid = t.tid key = 'location:%s' % tid location = redis.getvalue(key) #print 'location', location, type(location) if location and int(location.locate_error) > 500: print '------1 tid', tid, location location['locate_error'] = 500 print '------2 tid', tid, location #mem_location = DotDict({'id':location.id, # 'latitude':location.latitude, # 'longitude':location.longitude, # 'type':location.type, # 'clatitude':location.clatitude, # 'clongitude':location.clongitude, # 'timestamp':location.timestamp, # 'name':location.name, # 'degree':float(location.degree), # 'speed':float(location.speed), # 'locate_error':location['locate_error']}) #redis.setvalue(key, mem_location, 86400*356*2) count = count +1 print 'handled tid:', tid print 'total hanlded count:', count print 'total not hanlded count:', cnt
class Test(object): def __init__(self): self.db = DBConnection().db self.redis = MyRedis() def test_session(self): tid = 'T123SIMULATOR' sessionID_key = get_terminal_sessionID_key(tid) old_sessionid = self.redis.get(sessionID_key) print 'session_id: %s' % old_sessionid def test_session(self): tid = 'T123SIMULATOR' sessionID_key = get_terminal_sessionID_key(tid) old_sessionid = self.redis.get(sessionID_key) print 'session_id: %s' % old_sessionid def test_mileage(self): tid = '361A000066' mileage_key = 'mileage:%s' % tid #mileage = self.redis.getvalue(mileage_key) # for hmy #mileage = dict(lat=80547804, # lon=408846024, # dis=530, # gps_time=1400733148) # for lp mileage = dict(lat=144089748, lon=418702032, dis=561, gps_time=1400732859) print 'mileage: %s' % mileage #mileage['dis'] = 530 self.redis.setvalue(mileage_key, mileage) def test_acc_status(self): #tid = '3489722632' tid = '1641486598' acc_status_info_key = 'acc_status_info:%s' % tid #acc_status_info_key = "acc_status_info:T123SIMULATOR" acc_status_info = self.redis.getvalue(acc_status_info_key) print 'tid: %s, acc_status_info_key: %s, acc_status_info: %s' % (tid, acc_status_info_key, acc_status_info) def test_alarm_info(self): tid = '1641486598' #tid = 'T123SIMULATOR' alarm_info_key = 'alarm_info:%s' % tid alarm_info = self.redis.getvalue(alarm_info_key) print 'tid: %s, alarm_info_key: %s, alarm_info: %s' % (tid, alarm_info_key, alarm_info) def test_terminal_info(self): #tid = 'T123SIMULATOR' tid = '18701639494' terminal_info_key = "terminal_info:%s" % tid #self.redis.delete(terminal_info_key) terminal_info = self.redis.getvalue(terminal_info_key) print 'tid: %s, terminal_info_key: %s, terminal_info: %s' % (tid, terminal_info_key, terminal_info) def test_location(self): #tid = 'T123SIMULATOR' tid = '369A400585' location_key = "location:%s" % tid #self.redis.delete(location_key) location = self.redis.getvalue(location_key) print 'tid: %s, location_key: %s, location: %s' % (tid, location_key, location) def test_expire(self): key = 'jia_expire' timestamp = int(time.mktime(time.strptime("%s-%s-%s-%s-%s-%s"%(2014,11,21,16,40,0), "%Y-%m-%d-%H-%M-%S"))) self.redis.set(key, '1') self.redis.expireat(key, timestamp)
class Test(): def __init__(self): self.db = DBConnection().db self.redis = MyRedis() def get_track(self, tid, start_time, end_time, cellid=False): """NOTE: Now, only return gps point. """ if cellid: track = self.db.query("SELECT id, latitude, longitude, clatitude," " clongitude, timestamp, name, type, speed, degree, locate_error" " FROM T_LOCATION" " WHERE tid = %s" " AND NOT (latitude = 0 OR longitude = 0)" " AND (timestamp BETWEEN %s AND %s)" " GROUP BY timestamp" " ORDER BY timestamp", tid, start_time, end_time) else: # gps, pvt track = self.db.query("SELECT id, latitude, longitude, clatitude," " clongitude, timestamp, name, type, speed, degree, locate_error" " FROM T_LOCATION" " WHERE tid = %s" " AND category = 1" " AND NOT (latitude = 0 OR longitude = 0)" " AND (timestamp BETWEEN %s AND %s)" " AND type = 0" " GROUP BY timestamp" " ORDER BY timestamp", tid, start_time, end_time) return track #def get_track_distance(self, track): # """Get distance of a section of track. # """ # distance = 0 # if not track: # pass # else: # start_point = None # for point in track: # if not start_point: # start_point = point # continue # else: # distance += get_distance(start_point["longitude"], start_point["latitude"], # point["longitude"], point["latitude"]) # start_point = point # return distance def handle_stop(self, tid, start_time, end_time): track = self.get_track(tid, start_time, end_time) print 'track', len(track) cnt = 0 for i, pvt in enumerate(track): #print 'i: %s, speed: %s, pvt: %s' % (i, pvt['speed'], pvt) stop_key = 'test_stop_redis:%s' % tid stop = self.redis.getvalue(stop_key) distance_key = 'test_distance_redis:%s' % tid distance = self.redis.get(distance_key) if not distance: distance = 0 last_pvt_key = 'test_last_pvt_redis:%s' % tid last_pvt = self.redis.getvalue(last_pvt_key) if last_pvt: tmp = get_distance(int(last_pvt["longitude"]), int(last_pvt["latitude"]), int(pvt["longitude"]), int(pvt["latitude"])) print 'tmp: %s, distance: %s' % (tmp, distance) distance = float(distance) + tmp print 'last distance: %s' % (distance) #print 'add distance', i, pvt['id'], tmp, distance self.redis.setvalue(distance_key, distance, time=EVENTER.STOP_EXPIRY) if pvt['speed'] > LIMIT.SPEED_LIMIT: # 5 is moving if stop: #NOTE: time_diff is too short, drop the point. if pvt["timestamp"] - stop['start_time'] < 60: # 60 seconds cnt += 1 _stop = self.db.get("select distance from T_STOP where lid =%s", stop['lid']) if _stop: tmp_dis = _stop['distance'] else: tmp_dis = 0 print 'tmp_dis', tmp_dis distance = float(distance) + tmp_dis print 'tmp_dis distance', distance self.db.execute("DELETE FROM T_STOP WHERE lid = %s", stop['lid']) self.redis.delete(stop_key) self.redis.setvalue(distance_key, distance, time=EVENTER.STOP_EXPIRY) logging.info("[EVENTER] Stop point is droped: %s", stop) else: # close a stop point cnt += 1 self.redis.delete(stop_key) self.db.execute("UPDATE T_STOP SET end_time = %s WHERE lid = %s", pvt["timestamp"], stop['lid']) logging.info("[EVENTER] Stop point is closed: %s", stop) else: pass else: # low speed, may stop if stop: stop['end_time'] = pvt["timestamp"] self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY) logging.info("[EVENTER] Stop point is updated: %s", stop) else: # NOTE: start stop. #NOTE: create a new stop point cnt += 1 lid=pvt['id'] stop = dict(lid=lid, tid=tid, start_time=pvt["timestamp"], end_time=0, pre_lon=pvt["longitude"], pre_lat=pvt["latitude"], distance=distance) self.db.execute("INSERT INTO T_STOP(lid, tid, start_time, distance) VALUES(%s, %s, %s, %s)", lid, tid, pvt["timestamp"], distance) self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY) self.redis.delete(distance_key) logging.info("[EVENTER] Stop point is created: %s", stop) last_pvt = pvt self.redis.setvalue(last_pvt_key, last_pvt, time=EVENTER.STOP_EXPIRY) print '---------------------- cnt', cnt def clear_stop(self, tid, start_time, end_time): self.db.execute("DELETE FROM T_STOP WHERE tid = %s and start_time between %s and %s", tid, start_time, end_time) stop_key = 'test_stop_redis:%s' % tid distance_key = 'test_distance_redis:%s' % tid last_pvt_key = 'test_last_pvt_redis:%s' % tid self.redis.delete(stop_key) self.redis.delete(distance_key) self.redis.delete(last_pvt_key)
def wash_location(): db = DBConnection().db redis = MyRedis() #NOTE: all offline terminal #sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0" #NOTE: all offline terminals who has login before. sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0 and login_time>0" #sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where tid = '354A000121'" #sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where mobile = '14778742419'" #sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where mobile = '14778749929'" #sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login_time>0" #print 'sql', sql terminals = db.query(sql) #print 'len ', len(terminals) count = 0 cnt = 0 no_loc = 0 for i, t in enumerate(terminals): tid = t.tid key = 'location:%s' % tid location = redis.getvalue(key) if not location: time.sleep(2) no_loc = no_loc + 1 print 'no location, tid', tid #continue ##return ##redis.delete(key) #NOTE: get latest point. location = db.get("SELECT timestamp, MAX(timestamp) as maxtime" " FROM T_LOCATION" " WHERE tid = %s" " AND type = 0" " AND latitude != 0", tid) if location and location['timestamp']: location = db.get("SELECT * FROM T_LOCATION where timestamp = %s AND tid = %s AND latitude != 0 limit 1", location.maxtime, tid) mem_location = DotDict({'id':location.id, 'latitude':location.latitude, 'longitude':location.longitude, 'type':location.type, 'clatitude':location.clatitude, 'clongitude':location.clongitude, 'timestamp':location.timestamp, 'name':location.name, 'degree':float(location.degree), 'speed':float(location.speed), 'locate_error':int(location.locate_error)}) redis.setvalue(key, mem_location, 86400*356*2) count = count +1 print 'handled tid:', tid else: cnt = cnt + 1 print '-------no_loc', no_loc print 'total hanlded count:', count print 'total not hanlded count:', cnt
class Test(object): def __init__(self): self.db = DBConnection().db self.redis = MyRedis() def get_track(self, tid, start_time, end_time, cellid=False): """NOTE: Now, only return gps point. """ if cellid: track = self.db.query("SELECT id, latitude, longitude, clatitude," " clongitude, timestamp, name, type, speed, degree, locate_error" " FROM T_LOCATION" " WHERE tid = %s" " AND NOT (latitude = 0 OR longitude = 0)" " AND (timestamp BETWEEN %s AND %s)" " GROUP BY timestamp" " ORDER BY timestamp", tid, start_time, end_time) else: # gps, pvt track = self.db.query("SELECT id, latitude, longitude, clatitude," " clongitude, timestamp, name, type, speed, degree, locate_error" " FROM T_LOCATION" " WHERE tid = %s" " AND category = 1" " AND NOT (latitude = 0 OR longitude = 0)" " AND (timestamp BETWEEN %s AND %s)" " AND type = 0" " GROUP BY timestamp" " ORDER BY timestamp", tid, start_time, end_time) return track def handle_stop(self, tid, start_time, end_time): track = self.get_track(tid, start_time, end_time) #print 'track, tid:%s, len: %s' % (tid, len(track)) cnt = 0 delete_ids = [] update_item = [] create_item = [] for i, pvt in enumerate(track): #print '------------i',i, pvt['id'] #print 'i: %s, speed: %s, pvt: %s' % (i, pvt['speed'], pvt) stop_key = 'test_stop_redis:%s' % tid stop = self.redis.getvalue(stop_key) distance_key = 'test_distance_redis:%s' % tid distance = self.redis.get(distance_key) if not distance: distance = 0 last_pvt_key = 'test_last_pvt_redis:%s' % tid last_pvt = self.redis.getvalue(last_pvt_key) #if i == 0: # print 'last_pvt', last_pvt if last_pvt: tmp = get_distance(int(last_pvt["longitude"]), int(last_pvt["latitude"]), int(pvt["longitude"]), int(pvt["latitude"])) #print 'tmp: %s, distance: %s' % (tmp, distance) distance = float(distance) + tmp #print 'last distance: %s' % (distance) self.redis.setvalue(distance_key, distance, time=EVENTER.STOP_EXPIRY) if pvt['speed'] > LIMIT.SPEED_LIMIT: # 5 is moving if stop: #NOTE: time_diff is too short, drop the point. if pvt["timestamp"] - stop['start_time'] < 60: # 60 seconds cnt += 1 _stop = self.db.get("SELECT distance FROM T_STOP WHERE lid =%s ", stop['lid']) if _stop: tmp_dis = _stop['distance'] else: tmp_dis = 0 #print 'tmp_dis', tmp_dis distance = float(distance) + tmp_dis #print 'tmp_dis distance', distance test_id = self.db.execute("DELETE FROM T_STOP WHERE lid = %s", stop['lid']) #print '---------delete id', test_id delete_ids.append(stop['lid']) self.redis.delete(stop_key) self.redis.setvalue(distance_key, distance, time=EVENTER.STOP_EXPIRY) logging.info("[EVENTER] Stop point is droped: %s", stop) else: # close a stop point cnt += 1 self.redis.delete(stop_key) self.db.execute("UPDATE T_STOP SET end_time = %s WHERE lid = %s", pvt["timestamp"], stop['lid']) update_item.append(dict(timestamp=pvt["timestamp"], lid=stop['lid'])) logging.info("[EVENTER] Stop point is closed: %s", stop) else: pass else: # low speed, may stop if stop: stop['end_time'] = pvt["timestamp"] self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY) logging.info("[EVENTER] Stop point is updated: %s", stop) else: # NOTE: start stop. #NOTE: create a new stop point cnt += 1 lid=pvt['id'] stop = dict(lid=lid, tid=tid, start_time=pvt["timestamp"], end_time=0, pre_lon=pvt["longitude"], pre_lat=pvt["latitude"], distance=distance) self.db.execute("INSERT INTO T_STOP(lid, tid, start_time, distance) VALUES(%s, %s, %s, %s)", lid, tid, pvt["timestamp"], distance) create_item.append(dict(distance=distance, tid=tid, timestamp=pvt["timestamp"], lid=lid)) self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY) self.redis.delete(distance_key) logging.info("[EVENTER] Stop point is created: %s", stop) last_pvt = pvt self.redis.setvalue(last_pvt_key, last_pvt, time=EVENTER.STOP_EXPIRY) #print '---------------------- cnt', cnt #BIG NOTE: never use it #if create_item: # #_start = time.time() # self.db.executemany("INSERT INTO T_STOP(lid, tid, start_time, distance) VALUES(%s, %s, %s, %s)", # [(item['lid'], item['tid'], item['timestamp'], item['distance']) for item in create_item]) # #_end = time.time() # #print 'create_item', create_item # #print 'time_diff', _end - _start ##handle db #if delete_ids: # #print 'delete_ids', delete_ids # self.db.executemany("DELETE FROM T_STOP WHERE lid = %s", # [(item) for item in delete_ids]) #if update_item: # #print 'update_item', update_item # self.db.executemany("UPDATE T_STOP SET end_time = %s WHERE lid = %s", # [(item['timestamp'], item['lid']) for item in update_item]) def clear_stop(self, tid): self.db.execute("DELETE FROM T_STOP WHERE tid = %s", tid) stop_key = 'test_stop_redis:%s' % tid distance_key = 'test_distance_redis:%s' % tid last_pvt_key = 'test_last_pvt_redis:%s' % tid self.redis.delete(stop_key) self.redis.delete(distance_key) self.redis.delete(last_pvt_key) def handle_stop_single(self, tid, start_time, end_time): #begin_time = time.localtime() self.clear_stop(tid) self.handle_stop(tid, start_time, end_time) #end_time = time.localtime() #print 'begin_time',begin_time #print 'end_time',end_time def handle_stop_groups(self, tids, start_time, end_time): if not tids: return for tid in tids: self.handle_stop_single(tid, start_time, end_time) logging.info("handle_stop_groups finished") def get_terminals(self): #terminals = self.db.query("SELECT * from T_TERMINAL_INFO" # " where tid = '35C2000067'" # " limit 50") #terminals = self.db.query("SELECT id, tid from T_TERMINAL_INFO where tid = '35C2000067'") terminals = self.db.query("SELECT id, tid from T_TERMINAL_INFO LIMIT 8000") return terminals
class SimulatorTerminal(object): def __init__(self): self.tid = 'B123SIMULATOR' self.tmobile = '15901258591' self.imsi = '888823615223538' self.imei = '888888900872209' self.login_mg = "[%s,,1,1.0.0,%s,T1,%s,15901258591,%s,%s,CLW,2,]" self.heartbeat_mg = "[%s,%s,1,1.0.0,%s,T2,23:9:95,1,0]" self.location_mg = "[%s,%s,1,1.0.0,%s,T3,1,E,113.25,N,22.564152,120.3,270.5,1,460:0:9876:3171,23:9:100,%s]" self.socket = socket.socket(socket.AF_INET, socket.SOCK_DGRAM) host = ConfHelper.GW_SERVER_CONF.host port = int(ConfHelper.GW_SERVER_CONF.port) self.socket.connect((host, port)) self.redis = MyRedis() self.alarm_key = 'simulator_alarm' self.packet_key = 'simulator_packet' self.packet_max_res_time = 5 # 5 seconds self.alarm_interval = 60 * 5 # 5 minutes self.mobiles = [13693675352, 18310505991, 13581731204] self.emails = ['*****@*****.**', '*****@*****.**', '*****@*****.**'] def alarm(self): """ #NOTE: Send alarm message if need. """ send_flag = self.redis.getvalue(self.alarm_key) if (not send_flag): content = SMSCode.SMS_GW_DELAY_REPORT % ConfHelper.UWEB_CONF.url_out for mobile in self.mobiles: SMSHelper.send(mobile, content) for email in self.emails: EmailHelper.send(email, content) logging.info("[CK] Notify S packet delay to administrator!") self.redis.setvalue(self.alarm_key, True, self.alarm_interval) def login(self): """Send packet of login(T1) to GATEWAY. """ t_time = int(time.time()) login_mg = self.login_mg % (t_time, self.tid, self.tmobile, self.imsi, self.imei) logging.info("[CK] Send login message:\n%s", login_mg) self.socket.send(login_mg) time.sleep(1) def login_response(self, packet_info): """Handle the response of login(T1). """ if packet_info[2] == "0": self.sessionID = packet_info[3] self.start_each_thread() logging.info("[CK] Login success!") else: logging.info("[CK] Login faild, login agin...") time.sleep(5) self.login() def heartbeat_response(self, packet_info): """Handle the response of heartbeat(T2). """ if packet_info[2] == "0": logging.info("[CK] Heartbeat send success!") else: logging.info("[CK] Login faild, login again.") time.sleep(5) self.login() def upload_response(self, packet_info): """Handle the response of position(T3). """ #NOTE: check the interval of T and S s_time = int(time.time()) t_time = self.redis.getvalue(self.packet_key) if (not t_time) or (s_time - t_time) > self.packet_max_res_time: logging.warn("[CK] Location's respnse, s_time:%s, t_time:%s, max_res_time:%s ", s_time, t_time, self.packet_max_res_time) self.alarm() if packet_info[2] == "0": logging.info("[CK] Location upload success!") else: logging.info("[CK] Location upload faild, login again.") time.sleep(5) self.login() def heartbeat(self): """Send packet of heartbeat(T2) to GATEWAY. """ time.sleep(10) logging.info("[CK] Simulator terminal heartbeat thread start.") while True: heartbeat_mg = self.heartbeat_mg % (int(time.time()), self.sessionID, self.tid) logging.info("[CK] Send heartbeat:\n%s", heartbeat_mg) self.socket.send(heartbeat_mg) time.sleep(300) def upload_position(self): """Send packet of position(T3) to GATEWAY. """ time.sleep(60) logging.info("[CK] Simulator terminal upload position thread start...") while True: t_time = int(time.time()) msg = self.location_mg % (t_time, self.sessionID, self.tid, t_time) logging.info("[CK] Upload location:\n%s", msg) #NOTE: keep the send action. the interval is 10 times the alarm_interval self.redis.setvalue(self.packet_key, t_time, self.alarm_interval*10) self.socket.send(msg) time.sleep(300) def start_each_thread(self): """Start the thread to send hearbeat(T2), position(T3) to GATEWAY. """ thread.start_new_thread(self.heartbeat, ()) thread.start_new_thread(self.upload_position, ()) def handle_recv(self, packet_info): """Get the response, and dispatch them to diffent method according to the type of Sx. """ type = packet_info[1] if type == GATEWAY.S_MESSAGE_TYPE.LOGIN: # S1 self.login_response(packet_info) elif type == GATEWAY.S_MESSAGE_TYPE.HEARTBEAT: #S2 self.heartbeat_response(packet_info) elif type == GATEWAY.S_MESSAGE_TYPE.POSITION: # S3 self.upload_response(packet_info) else: pass def udp_client(self): """Main method. workflow: login while True: receive response from GATEWAY handle response if login success: start hearet_beat start upload_location else: handle the response """ try: self.login() while True: infds, _, _ = select.select([self.socket], [], [], 1) if len(infds) > 0: dat = self.socket.recv(1024) logging.info("[CK] Recv data: %s", dat) packet_info = self.pase_packet(dat) self.handle_recv(packet_info) except KeyboardInterrupt: logging.error("Ctrl-C is pressed.") except Exception as e: logging.error("[CK] What's wrong, reconnect it.%s", e.args) finally: self.socket.close() def pase_packet(self, packet): """Parse the packet. """ packet_info = packet[1:][:-1].split(",") return packet_info