def add_timestamp(): db = DBConnection().db redis = MyRedis() e_sql = 'select id, tid, timestamp, lid from T_EVENT where timestamp = 0 order by id desc limit 3000000' #e_sql = 'select id, tid, timestamp, lid from T_EVENT where timestamp = 0 ' #e_sql = 'select id, tid, timestamp, lid from T_EVENT where timestamp = 0 limit 1000000' l_sql = 'select id, tid, timestamp from T_LOCATION where id = %s' print 'e_sql', e_sql event = db.query(e_sql) print 'len ', len(event) for i, e in enumerate(event): if not (i % 10000): print 'now, it is ', i time.sleep(2) print '-----i: %s, e: %s' % (i, e) lid = e.lid eid = e.id location = db.get(l_sql, lid) if location: gps_time = location.get('timestamp', 0) if gps_time: print 'update time', gps_time db.execute('UPDATE T_EVENT SET timestamp = %s WHERE id = %s', gps_time, eid)
class MGroupMixin(object): def __init__(self): self.mysql_db = DBConnection().db def retrieve_mixin(self, citylist=None): results = [] for city in citylist: infos = self.mysql_db.query("SELECT thp.province_id AS id, thp.province_name AS province_name," " thc.city_name AS city_name" " FROM T_HLR_CITY AS thc, T_HLR_PROVINCE AS thp" " WHERE thc.region_code = %s" " AND thc.province_id = thp.province_id", city) for info in infos: groups = self.mysql_db.query("SELECT txg.id, txg.xxt_id, txg.name," " txa.xxt_id AS agency_id, txa.name AS agency_name" " FROM T_XXT_GROUP AS txg LEFT JOIN T_XXT_AGENCY AS txa" " ON txa.xxt_id = txg.agency_id" " WHERE txg.city_id = %s", city) for group in groups: #total_parents = self.mysql_db.get("SELECT count(*) AS total" # " FROM T_XXT_USER AS txu" # " WHERE txu.group_id = %s", group.xxt_id) total_parents = self.mysql_db.get("call P_GROUP_TOTAL_PARENTS(%s)", group.xxt_id) #total_children = self.mysql_db.get("SELECT count(*) AS total" # " FROM T_XXT_TARGET AS txt" # " WHERE txt.group_id = %s", group.xxt_id) total_children = self.mysql_db.get("call P_GROUP_TOTAL_CHILDREN(%s)", group.xxt_id) if not group.agency_name: group.agency_name = u'暂无' res = {'_id': None, 'province_id': info.id, 'city_id': city, 'id': group.id, 'group_id': group.xxt_id, 'group_name': group.name, 'agency_id': group.agency_id, 'agency': group.agency_name, 'total_parents': total_parents.total, 'total_children': total_children.total, 'city': info.city_name, 'province': info.province_name} results.append(res) return results
def block_test(): db = DBConnection().db redis = MyRedis() #terminals = db.query("select * from T_TERMINAL_INFO where mobile = 14778741722 " ) terminals = db.query("select * from T_TERMINAL_INFO where mobile in (14778473468 ) " ) for terminal in terminals: tid = terminal.tid t = db.query("SELECT tid from T_TERMINAL_INFO where test !=0 and tid=%s", tid) if not t: continue #print 'clear', terminal db.execute("UPDATE T_TERMINAL_INFO SET test=0 WHERE tid = %s", tid) print 'tid: %s test is closed.' % tid sessionID_key = get_terminal_sessionID_key(tid) old_sessionid = redis.get(sessionID_key) if old_sessionid: redis.delete(sessionID_key) print "Termianl %s delete session in redis." % tid
class MOACB(object): def __init__(self): ConfHelper.load(options.conf) self.db = DBConnection().db def fetch_mo_sms(self): status = ErrorCode.SUCCESS result = None try: mos = self.db.query("SELECT id, msgid, mobile, content " " FROM T_SMS " " WHERE category = %s " " AND send_status = %s" " ORDER BY id ASC" " LIMIT 10", SMS.CATEGORY.MO, SMS.SENDSTATUS.PREPARING) for mo in mos: mobile = mo["mobile"] content = mo["content"] id = mo["id"] msgid = mo["msgid"] result = self.send_mo_to_acb(mobile, content, msgid) if result["status"] == ErrorCode.SUCCESS: if int(result["ret"]) == ErrorCode.SUCCESS: logging.info("SMS-->ACB success mobile = %s, content = %s", mobile, content) self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.SUCCESS, id) status = ErrorCode.SUCCESS elif int(result["ret"]) == ErrorCode.FAILED: logging.info("SMS-->ACB failure mobile = %s, content = %s", mobile, content) self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.FAILURE, id) status = ErrorCode.FAILURE else: #sms-->acb reponse error result status = ErrorCode.FAILURE else: # http response is None status = ErrorCode.FAILURE except Exception, msg: status = ErrorCode.FAILURE logging.exception("Fetch mo sms exception : %s", msg) finally:
def wash_location(): db = DBConnection().db redis = MyRedis() #sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where tid = '36E2400480' " sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO" #sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0" #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.get(key) if not location: #if True: print 'tid', tid location = db.get("SELECT timestamp, MAX(timestamp) as maxtime" " FROM T_LOCATION" " WHERE tid = %s" " AND type = 0" " AND latitude != 0", tid) if location: if location.timestamp != location.maxtime: print 'timestamp != maxtime, tid', tid location = db.get("SELECT * FROM T_LOCATION where timestamp = %s AND tid = %s limit 1", location.maxtime, tid) else: continue 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 'total hanlded count:', count print 'total not hanlded count:', cnt
def wash_location(): db = DBConnection().db redis = MyRedis() sql = "select tmobile, from_unixtime(add_time) as add_time, from_unixtime(del_time) as del_time from T_BIND_LOG where group_id in (239, 419)" sql_1 = "select tmobile, add_time, del_time from T_BIND_LOG where op_type = 1 and tmobile = %s" sql_2 = "select tmobile, add_time, del_time from T_BIND_LOG where op_type = 2 and tmobile = %s" #print 'sql', sql res = db.query(sql) #print 'len ', len(terminals) for i, t in enumerate(res): tmobile = t.tmobile add = db.query(sql_1, tmobile) add_num = len(add) unbind = db.query(sql_2, tmobile) del_num = len(unbind) print del_num, add_num, t.del_time , t.add_time if del_num > add_num: print del_num, add_num, t.del_time , t.add_time,
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 statistic(): db = DBConnection().db sql = 'select from_unixtime(timestamp), terminal_online, terminal_offline from T_STATISTIC where type=2 and timestamp between 1378051140 and 1380556741' t = db.query(sql) sum_on = 0 sum_off = 0 for i in t: print 'i', i on = i.terminal_online off = i.terminal_offline sum_on += on sum_off += off print 'sum_on: %s, sum_off: %s, sum: %s' % (sum_on, sum_off, sum_on+sum_off)
def execute(): db = DBConnection().db redis = MyRedis() terminals = db.query("SELECT tid, mobile FROM T_TERMINAL_INFO ") print "len ", len(terminals) for t in terminals: tid = t.tid print "tid", tid car = db.get("select * from T_CAR where tid = %s", tid) if car: print "has car " pass else: print "insert car"
def block_test(): db = DBConnection().db redis = MyRedis() #terminals = db.query("SELECT tid, mobile, group_id, cid FROM V_TERMINAL WHERE group_id != '-1'") terminals = db.query("SELECT tid, tmobile, group_id, cid FROM V_TERMINAL WHERE cid = '13600335550'") for terminal in terminals: tid = terminal.tid ttype = get_terminal_type_by_tid(tid) print 'ttype', ttype if ttype == 'zj200': t = db.query("SELECT tid from T_TERMINAL_INFO where test !=0 and tid=%s", tid) if not t: continue #print 'clear' db.execute("UPDATE T_TERMINAL_INFO SET test=0 WHERE tid = %s", tid) print 'tid: %s test is closed.' % tid sessionID_key = get_terminal_sessionID_key(tid) old_sessionid = redis.get(sessionID_key) if old_sessionid: redis.delete(sessionID_key) print "Termianl %s delete session in redis." % tid
def send_all(content): db = DBConnection().db terminals = db.query("SELECT * FROM V_TERMINAL WHERE cid in ( '15207610001' )") # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0") # terminals = db.query("SELECT mobile FROM T_TERMINAL_INFO where login=0") # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0") # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0 and offline_time>1408197915") # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0 and offline_time>1411452000") print "len: %s", len(terminals) for t in terminals: terminal = db.get("select login, tid, mobile from T_TERMINAL_INFO where tid = %s", t.tid) if terminal.login == 0: # offline print "--", terminal send(content, terminal.mobile)
def block_test(): db = DBConnection().db redis = MyRedis() sms = db.query("SELECT uid FROM T_SMS_OPTION") count = 0 for s in sms: uid = s.uid user = db.get("select * from T_USER where uid = %s", uid) if user: pass # print 'pass' else: db.execute("DELETE FROM T_SMS_OPTION WHERE uid = %s", uid) print "delete ....", uid count += 1 print "-------count: %s" % count
class MLocationMixin(object): def __init__(self): self.mysql_db = DBConnection().db def retrieve_mixin(self, city_list=None, start_time=None, end_time=None): results = [] d = datetime.date.fromtimestamp(start_time/1000) year = d.year month = d.month cities = city_info(city_list, self.mysql_db) for city in cities: groups = self.mysql_db.query("SELECT T_XXT_GROUP.name as group_name, T_XXT_GROUP.xxt_id" " FROM T_XXT_GROUP WHERE T_XXT_GROUP.city_id = %s", city.id) for group in groups: for item, category in [ #('custom', LOCATION.CATEGORY.CUSTOM), ('schedule', LOCATION.CATEGORY.SCHEDULE), ('realtime', LOCATION.CATEGORY.REALTIME)]: #data = self.mysql_db.get("SELECT count(T_LOCATION.id) as total" # " FROM T_LOCATION, T_XXT_TARGET, T_LBMP_TERMINAL" # " WHERE T_LOCATION.timestamp BETWEEN %s AND %s" # " AND T_LOCATION.category = %s" # " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" # " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" # " AND T_XXT_TARGET.group_id = %s", # start_time, end_time, category, group.xxt_id) data = self.mysql_db.get("call P_LOCATION(%s, %s, %s, %s)", start_time, end_time, category, group.xxt_id) group[item] = data.total res = {'_id': None, 'province_id': city.p_id, 'city_id': city.id, 'group_id': group.xxt_id, 'province': city.p_name, 'city': city.name, 'group_name': group.group_name, # 'custom': group.custom, 'schedule': group.schedule, 'realtime': group.realtime, 'year': year, 'month': month} results.append(res) return results
def block_test(): db = DBConnection().db redis = MyRedis() #terminals = db.query("select tid from T_TERMINAL_INFO where group_id= '438';") #terminals = db.query("select tid from T_TERMINAL_INFO where group_id= '438'") terminals = db.query("select id, tid, test from T_TERMINAL_INFO where mobile = '14778748087' ") for terminal in terminals: tid = terminal.tid #db.execute("UPDATE T_TERMINAL_INFO SET trace_para='60:0' WHERE tid = %s", # tid) #print 'tid: %s test is closed.' % tid sessionID_key = get_terminal_sessionID_key(tid) old_sessionid = redis.get(sessionID_key) if old_sessionid: redis.delete(sessionID_key) print "Termianl %s delete session in redis." % tid
def export_excel(): db = DBConnection().db online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;') offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;') wt = xlwt.Workbook() ws = wt.add_sheet(u'jia') sql = 'select id, mobile, owner_mobile, tid, begintime from T_TERMINAL_INFO' #sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO' #sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO limit 5' #sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status=1' #sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status=1 and mobile like '%%%%14778%%%%' " #sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where group_id = 310 " #sql = "select mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status != 0 and begintime between 1356969600 and 1388505599" #sql = "select mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status != 0 and begintime between 1356969600 and 1388505599" #sql = "select mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status != 0 and (mobile like '1477874%%' or mobile like '1477847%%' )" print 'sql', sql terminals = db.query(sql) print 'len ', len(terminals) for i, t in enumerate(terminals): print 't', t reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime)) ws.write(i,0,i) ws.write(i,1,t['tid']) ws.write(i,2,reg_time) #print 't', t #reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime)) #ws.write(i,0,t.owner_mobile) #ws.write(i,1,t.mobile) #ws.write(i,2,reg_time) #lst += "'" + mobile + "'," #print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';' #db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)" # " VALUES(NULL, %s)" # " ON DUPLICATE KEY" # " UPDATE mobile = values(mobile)", mobile) #content = ':SIM ' + umobile + ':' + mobile #print content #SMSHelper.send_to_terminal(mobile, content) #print '%s sucessfully.' % mobile wt.save('./jia_new.xls')
def block_test(): db = DBConnection().db redis = MyRedis() #terminals = db.query("select tid, move_val, static_val from T_TERMINAL_INFO where static_val =0 and move_val=0") terminals = db.query("select tid, move_val, static_val from T_TERMINAL_INFO where tid = '392240008A' ") #terminals = db.query("select tid, move_val, static_val from T_TERMINAL_INFO where id in (12923, 21792 )") #terminals = db.query("select tid, move_val, static_val from T_TERMINAL_INFO where static_val =60") for terminal in terminals: tid = terminal.tid #db.execute("UPDATE T_TERMINAL_INFO SET static_val=120,move_val=0 WHERE tid = %s", # tid) #db.execute("UPDATE T_TERMINAL_INFO SET static_val=0, move_val=60 WHERE tid = %s", # tid) print 'tid: %s parking is modified.' % tid sessionID_key = get_terminal_sessionID_key(tid) old_sessionid = redis.get(sessionID_key) if old_sessionid: redis.delete(sessionID_key) print "Termianl %s delete session in redis." % tid
def export_excel(): db = DBConnection().db online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;') offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;') wt = xlwt.Workbook() ws = wt.add_sheet(u'jia') sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status=1 and begintime<1396281600 order by begintime " terminals = db.query(sql) print 'len ', len(terminals) for i, t in enumerate(terminals): #terminal = db.get("select owner_mobile from T_TERMINAL_INFO where tid = %s", t.tid) #car= db.get("select cnum from T_CAR where tid = %s", t.tid) biz= db.get("select biz_type from T_BIZ_WHITELIST where mobile = %s limit 1", t.mobile) biz_type = 10 if biz: if biz['biz_type'] == 1: biz_type = 20 print 't', t reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime)) #reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime)) ws.write(i,0,t.owner_mobile) ws.write(i,1,t.mobile) ws.write(i,2,reg_time) ws.write(i,3,biz_type) #lst += "'" + mobile + "'," #print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';' #db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)" # " VALUES(NULL, %s)" # " ON DUPLICATE KEY" # " UPDATE mobile = values(mobile)", mobile) #content = ':SIM ' + umobile + ':' + mobile #print content #SMSHelper.send_to_terminal(mobile, content) #print '%s sucessfully.' % mobile wt.save('/tmp/jia_biz_type.xls')
def block_test(): db = DBConnection().db redis = MyRedis() #start_time = int(time.mktime(time.strptime("%s-%s-%s-%s-%s-%s"%(2014,8,26,0,0,0),"%Y-%m-%d-%H-%M-%S"))) #end_time = int(time.mktime(time.strptime("%s-%s-%s-%s-%s-%s"%(2014,8,27,18,0,0),"%Y-%m-%d-%H-%M-%S"))) #print time.localtime(start_time) #print time.localtime(end_time) tid = '3922400068' start_time = 1409222189 end_time = 1409223445 track = 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) distance = 0 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 print '---distance', distance, tid, start_time, end_time
def export_excel(): db = DBConnection().db online_style = xlwt.easyxf("font: colour_index green, bold off; align: wrap on, vert centre, horiz center;") offline_style = xlwt.easyxf("font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;") wt = xlwt.Workbook() ws = wt.add_sheet(u"jia") # sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL' # sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO limit 5' # sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status=1' # sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status=1 and mobile like '%%%%14778%%%%' " # sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where group_id = 310 " sql = "select tid, mobile from V_TERMINAL where cid = '13823936111'" terminals = db.query(sql) print "len ", len(terminals) for i, t in enumerate(terminals): terminal = db.get("select owner_mobile from T_TERMINAL_INFO where tid = %s", t.tid) car = db.get("select cnum from T_CAR where tid = %s", t.tid) print "t", t print "ter", terminal print "car", car # reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime)) ws.write(i, 0, terminal.owner_mobile) ws.write(i, 1, t.mobile) ws.write(i, 2, car.cnum) # lst += "'" + mobile + "'," # print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';' # db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)" # " VALUES(NULL, %s)" # " ON DUPLICATE KEY" # " UPDATE mobile = values(mobile)", mobile) # content = ':SIM ' + umobile + ':' + mobile # print content # SMSHelper.send_to_terminal(mobile, content) # print '%s sucessfully.' % mobile wt.save("/tmp/jia_13823936111_corp.xls")
def export_sms(): db = DBConnection().db online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;') offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;') wt = xlwt.Workbook() ws = wt.add_sheet(u'jia') sql = "select insert_time, mobile, content from T_SMS where insert_time > 1393603200000 limit 5000" #sql = "select insert_time, mobile, content from T_SMS where insert_time between 1391184000000 and 1393603200000 limit 5000" #sql = "select insert_time, mobile, content from T_SMS where insert_time > 1388505600000 limit 5000" #sql = "select insert_time, mobile, content from T_SMS where insert_time > 1388505600000" #sql = "select insert_time, mobile, content from T_SMS where content like '%%激活成功%%' " print 'sql', sql sms = db.query(sql) print 'len ', len(sms) for i, s in enumerate(sms): #print 's', s reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(s.insert_time/1000)) ws.write(i,0,str(s.mobile)) ws.write(i,1,reg_time) ws.write(i,2,s.content) wt.save('./sms_3.xls')
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
def batch_import(file_path): db = DBConnection().db online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;') offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;') wt = xlwt.Workbook() ws = wt.add_sheet(u'jia') wb = xlrd.open_workbook(file_path) sheet = wb.sheets()[0] lst = "" num = 0 #for i in range(1,sheet.nrows): for i in range(0,sheet.nrows): row = sheet.row_values(i) print 'roe', row #mobile = unicode(row[0])[0:-2] mobile = unicode(row[0])[0:11] # is recommend print 'mobile', mobile t = db.get("select id, mobile, login, tid, owner_mobile, from_unixtime(begintime) as begin_time," " from_unixtime(login_time) as login_time, domain from T_TERMINAL_INFO where mobile = %s and service_status=1", mobile) print 't', t if not t: label= u'未激活' b = db.query('select * from T_BIND_LOG where tmobile=%s', mobile) if b: label= u'已解绑' umobile='' print 'not: ', mobile else: label = u'已激活' print 't: ', t umobile=t.owner_mobile num += 1 print 'label', label ws.write(i,0,mobile) ws.write(i,1,umobile) if label == u'未激活': ws.write(i,2,label, offline_style) else: ws.write(i,2,label, online_style) if t: begin_time = t.begin_time login_time = t.login_time #reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime)) begintime = str(begin_time.year)+'-'+str(begin_time.month)+'-' +str(begin_time.day) +'-'+str(begin_time.hour)+':'+str(begin_time.minute)+':'+str(begin_time.second) logintime = str(login_time.year)+'-'+str(login_time.month)+'-'+ str(login_time.day) +'-'+str(login_time.hour)+':'+str(login_time.minute)+':'+str(login_time.second) ws.write(i,3,begintime) ws.write(i,4,logintime) #lst += "'" + mobile + "'," #print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';' #db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)" # " VALUES(NULL, %s)" # " ON DUPLICATE KEY" # " UPDATE mobile = values(mobile)", mobile) #content = ':SIM ' + umobile + ':' + mobile #print content #SMSHelper.send_to_terminal(mobile, content) #print '%s sucessfully.' % mobile print num wt.save('/tmp/jia_new.xls')
class MT(object): def __init__(self): ConfHelper.load(options.conf) self.db = DBConnection().db def fetch_mt_sms(self): status = ErrorCode.SUCCESS result = None try: mts = self.db.query("SELECT id, msgid, mobile, content, nosign" " FROM T_SMS" " WHERE category = %s" " AND send_status = %s" " ORDER BY id ASC" " LIMIT 50", SMS.CATEGORY.MT, SMS.SENDSTATUS.PREPARING) for mt in mts: mobile = mt["mobile"] content = mt["content"] msgid = mt["msgid"] id = mt["id"] if not mobile: logging.error("[SMS] Mobile is missing, drop it. mt: %s", mt) continue if True: #mt["nosign"]: send_status, result = self.send_mt_nosign(id, msgid, mobile, content) result = eval(result) if send_status["status"] == '200': if result["resultCode"] == "0": #if result["flag"] == "success": logging.info("SMS-->Gateway nosign message send successfully, mobile:%s, content:%s", mobile, content) status = ErrorCode.SUCCESS self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.SUCCESS, id) else: logging.error("SMS-->Gateway nosign message send failed, result:%s", result) status = ErrorCode.FAILED self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.FAILURE, id) else: logging.error("SMS-->Gateway nosign message failed, send_status:%s", send_status) status = ErrorCode.FAILED self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.FAILURE, id) else: result = self.send_mt(id, msgid, mobile, content) result = json_decode(result) if result["status"] == ErrorCode.SUCCESS: if result["ret"] == "100": logging.info("SMS-->Gateway success mobile = %s, content = %s, id = %s ", mobile, content, id) self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.SUCCESS, id) status = ErrorCode.SUCCESS else: if result["ret"] == "101": logging.error("SMS-->Gateway failure, errorcode = 101, mobile = %s, content = %s, id = %s ", mobile, content, id) elif result["ret"] == "104": logging.error("SMS-->Gateway content error, errorcode = 104, mobile = %s, content = %s, id = %s ", mobile, content, id) elif result["ret"] == "105": logging.error("SMS-->Gateway frequency too fast, errorcode = 105, mobile = %s, content = %s, id = %s ", mobile, content, id) elif result["ret"] == "106": logging.error("SMS-->Gateway number limited, errorcode = 106, mobile = %s, content = %s, id = %s ", mobile, content, id) else: logging.error("SMS-->Gateway other error, errorcode unknown, mobile = %s, content = %s, id = %s ", mobile, content, id) if result["ret"] != "105": self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.FAILURE, id) status = ErrorCode.FAILED else: # http response is None logging.info("SMS-->Gateway failed, mobile = %s, content = %s, id = %s ", mobile, content, id) status = ErrorCode.FAILED self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.FAILURE, id) except Exception, msg: status = ErrorCode.FAILED logging.exception("Fetch mt sms exception : %s", msg) finally:
class TerminalStatistic(object): def __init__(self): self.db = DBConnection().db self.redis = MyRedis() self.to_emails = ['*****@*****.**'] self.cc_emails = ['*****@*****.**','*****@*****.**'] #self.cc_emails = ['*****@*****.**','*****@*****.**','*****@*****.**'] def statistic_online_terminal(self, epoch_time): start_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) logging.info("[CELERY] %s statistic_online_terminal started.", start_time) try: corps = self.db.query("SELECT cid FROM T_CORP") for corp in corps: if corp: online_count = self.db.get("SELECT COUNT(tti.tid) AS num" " FROM T_CORP tc, T_GROUP tg, T_TERMINAL_INFO tti" " WHERE tc.cid = tg.corp_id" " AND tg.id = tti.group_id" " AND tti.service_status = 1" " AND tti.login != 0" " AND tc.cid = %s", corp.cid) offline_count = self.db.get("SELECT COUNT(tti.tid) AS num" " FROM T_CORP tc, T_GROUP tg, T_TERMINAL_INFO tti" " WHERE tc.cid = tg.corp_id" " AND tg.id = tti.group_id" " AND tti.service_status = 1" " AND tti.login = 0" " AND tc.cid = %s", corp.cid) if online_count: online_num = online_count.num else: online_num = 0 if offline_count: offline_num = offline_count.num else: offline_num = 0 self.db.execute("INSERT INTO T_ONLINE_STATISTIC(online_num, offline_num, time, cid) " " VALUES(%s, %s, %s, %s)", online_num, offline_num, int(epoch_time), corp.cid) convert_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(epoch_time)) logging.info("[CELERY] %s statistic_online_terminal finish.", convert_time) except Exception as e: logging.exception("[CHECKER] statistic_online_terminal failed, exception: %s", e.args) def statistic_user(self, epoch_time): try: start_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) logging.info("[CELERY] %s statistic_user started.", start_time) current_day = time.localtime(epoch_time) day_start_time, day_end_time = start_end_of_day(current_day.tm_year, current_day.tm_mon, current_day.tm_mday) month_start_time, month_end_time = start_end_of_month(current_day.tm_year, current_day.tm_mon) year_start_time, year_end_time = start_end_of_year(current_day.tm_year) logging.info("[CELERY] day_start_time: %s, day_end_time: %s, month_start_time: %s, month_end_time: %s, year_start_time: %s, year_end_time: %s.", day_start_time, day_end_time, month_start_time, month_end_time, year_start_time, year_end_time) in_terminal_add_day = 0 in_terminal_del_day = 0 in_terminal_add_month = 0 in_terminal_del_month = 0 in_terminal_add_year = 0 in_terminal_del_year = 0 e_terminal_add_day = 0 e_terminal_del_day = 0 e_terminal_add_month = 0 e_terminal_del_month = 0 e_terminal_add_year = 0 e_terminal_del_year = 0 def handle_dead_terminal(db, redis): """For the terminals to be removed, delete the associated info of it. @params: db, database """ terminals = db.query("select tid, mobile from T_TERMINAL_INFO where service_status = 2") logging.info("Handle the to be removed terminals, the count of terminals: %s", len(terminals)) for terminal in terminals: logging.info("Delete the to be removed terminal:%s", terminal.mobile) delete_terminal(terminal.tid, db, redis, del_user=True) def get_record_of_last_day(sta_time, sta_type, db): """Get record statisticted in last day. @params: sta_time, the statistic time sta_type, the statistic type, 0: individual; 1: enterprise, 2: all db, database """ ## BIG NOTE: the snippet only be invoked when statistic occurs first time #record = {} #record['terminal_add_month'] = 0 #record['terminal_del_month'] = 0 #record['terminal_add_year'] = 0 #record['terminal_del_year'] = 0 #return record end_of_last_day = sta_time - 1 record = db.get("SELECT terminal_add_month, terminal_add_year, terminal_del_month, terminal_del_year" " FROM T_STATISTIC" " WHERE timestamp = %s AND type = %s", end_of_last_day, sta_type) if not record: # it should never happen record = {} current_day = time.localtime(sta_time) if current_day.tm_mday == 1: # first day of a month, year.month.01, the month-data is unavaliable record['terminal_add_month'] = 0 record['terminal_del_month'] = 0 if current_day.tm_mon == 1: # first month of a year, 2014.01.01, the month-data and year-data are unvavliable record['terminal_add_year'] = 0 record['terminal_del_year'] = 0 return record def handle_in_terminal(tmobile, start_time, end_time, db): """Check the terminal is del or add. @params: tmobile, the mobile of terminal start_time, the start time of a day end_time, the end time of a day db, database """ add_num = 0 del_num = 0 add_count = db.get("SELECT COUNT(*) AS count FROM T_BIND_LOG" " WHERE tmobile = %s AND group_id = -1 AND op_type = %s AND add_time BETWEEN %s AND %s", tmobile, UWEB.OP_TYPE.ADD, start_time, end_time) del_count = db.get("SELECT COUNT(*) AS count FROM T_BIND_LOG" " WHERE tmobile = %s AND group_id = -1 AND op_type = %s AND del_time BETWEEN %s and %s", tmobile, UWEB.OP_TYPE.DEL, start_time, end_time) interval = add_count.count - del_count.count if interval == 0: # +-, -+ add_num = 0 del_num = 0 elif interval == 1: # +,+-+ add_num = 1 del_num = 0 elif interval == -1: # -, -+- add_num = 0 del_num = 1 else: #NOTE: it should never happen logging.error("Tmobile:%s, add_count: %s, del_count: %s", tmobile, add_count.count, del_count.count) return add_num, del_num def handle_en_terminal(tmobile, start_time, end_time, db): """Check the terminal is del or add. @params: tmobile, the mobile of terminal start_time, the start time of a day end_time, the end time of a day db, database """ add_num = 0 del_num = 0 add_count = db.get("SELECT COUNT(*) AS count FROM T_BIND_LOG" " WHERE tmobile = %s AND group_id != -1 AND op_type = %s AND add_time BETWEEN %s AND %s", tmobile, UWEB.OP_TYPE.ADD, start_time, end_time) del_count = db.get("SELECT COUNT(*) AS count FROM T_BIND_LOG" " WHERE tmobile = %s AND group_id != -1 AND op_type = %s AND del_time BETWEEN %s and %s", tmobile, UWEB.OP_TYPE.DEL, start_time, end_time) interval = add_count.count - del_count.count if interval == 0: # +-, -+ add_num = 0 del_num = 0 elif interval == 1: # +,+-+ add_num = 1 del_num = 0 elif interval == -1: # -, -+- add_num = 0 del_num = 1 else: #NOTE: it should never happen logging.error("Tmobile:%s, add_count: %s, del_count: %s", tmobile, add_count.count, del_count.count) return add_num, del_num # handle the dead terminal: handle_dead_terminal(self.db, self.redis) # for individual terminals = self.db.query("SELECT DISTINCT tmobile FROM T_BIND_LOG" " WHERE (tmobile LIKE '14778%%' OR tmobile LIKE '1847644%%')" " AND group_id = -1") tmobiles = [terminal.tmobile for terminal in terminals] for tmobile in tmobiles: add_num, del_num = handle_in_terminal(tmobile, day_start_time, day_end_time, self.db) in_terminal_add_day += add_num in_terminal_del_day += del_num record = get_record_of_last_day(day_start_time, UWEB.STATISTIC_USER_TYPE.INDIVIDUAL, self.db) logging.info("in_terminal_add_day: %s, in_terminal_del_day:%s", in_terminal_add_day, in_terminal_del_day) logging.info("record of last_day for individual: %s", record) in_terminal_add_month = record['terminal_add_month'] + in_terminal_add_day in_terminal_del_month = record['terminal_del_month'] + in_terminal_del_day in_terminal_add_year = record['terminal_add_year'] + in_terminal_add_day in_terminal_del_year = record['terminal_del_year'] + in_terminal_del_day # for enterprise terminals = self.db.query("SELECT DISTINCT tmobile FROM T_BIND_LOG" " WHERE (tmobile LIKE '14778%%' OR tmobile LIKE '1847644%%') " " AND group_id != -1") tmobiles = [terminal.tmobile for terminal in terminals] for tmobile in tmobiles: add_num, del_num = handle_en_terminal(tmobile, day_start_time, day_end_time, self.db) e_terminal_add_day += add_num e_terminal_del_day += del_num record = get_record_of_last_day(day_start_time, UWEB.STATISTIC_USER_TYPE.ENTERPRISE, self.db) logging.info("e_terminal_add_day: %s, e_terminal_del_day:%s", e_terminal_add_day, e_terminal_del_day) logging.info("record of last_day for enterprise: %s", record) e_terminal_add_month = record['terminal_add_month'] + e_terminal_add_day e_terminal_del_month = record['terminal_del_month'] + e_terminal_del_day e_terminal_add_year = record['terminal_add_year'] + e_terminal_add_day e_terminal_del_year = record['terminal_del_year'] + e_terminal_del_day sql_corp_add = ("SELECT COUNT(id) as num" " FROM T_CORP" " WHERE timestamp BETWEEN %s AND %s") sql_in_login = ("SELECT COUNT(distinct tll.uid) AS num" " FROM T_LOGIN_LOG as tll, T_TERMINAL_INFO as tti" " WHERE tll.uid = tti.owner_mobile " " AND (tti.mobile LIKE '14778%%' OR tti.mobile LIKE '1847644%%')" " AND tll.role =0 AND (tll.timestamp BETWEEN %s AND %s)") sql_en_login = ("SELECT COUNT(distinct uid) AS num" " FROM T_LOGIN_LOG" " WHERE role != 0 AND (timestamp BETWEEN %s AND %s)") sql_terminal_line_count = ("SELECT COUNT(tid) AS num" " FROM T_TERMINAL_INFO ") sql_in_active = ("SELECT COUNT(tmp.uid) AS num" " FROM" " (SELECT uid " " FROM T_LOGIN_LOG as tll, T_TERMINAL_INFO as tti" " WHERE tll.uid = tti.owner_mobile" " AND (tti.mobile LIKE '14778%%' OR tti.mobile LIKE '1847644%%') " " AND (tll.timestamp BETWEEN %s AND %s)" " AND tll.role = 0 " " GROUP BY tll.uid" " HAVING count(tll.id) >3) tmp") sql_en_active = ("SELECT COUNT(tmp.uid) AS num" " FROM" " (SELECT uid " " FROM T_LOGIN_LOG" " WHERE (timestamp BETWEEN %s AND %s)" " AND role != 0 " " GROUP BY uid" " HAVING count(id) >3) tmp") sql_kept = ("INSERT INTO T_STATISTIC(corp_add_day, corp_add_month, corp_add_year," " terminal_add_day, terminal_add_month, terminal_add_year," " terminal_del_day, terminal_del_month, terminal_del_year," " login_day, login_month, login_year, active, deactive," " terminal_online, terminal_offline," " terminal_individual, terminal_enterprise, timestamp, type)" " VALUES (%s,%s,%s," " %s, %s, %s," " %s, %s, %s," " %s, %s, %s, %s, %s," " %s, %s, %s, %s, %s, %s)" " ON DUPLICATE KEY" " UPDATE corp_add_day=values(corp_add_day)," " corp_add_month=values(corp_add_month), " " corp_add_year=values(corp_add_year)," " terminal_add_day=values(terminal_add_day)," " terminal_add_month=values(terminal_add_month)," " terminal_add_year=values(terminal_add_year)," " terminal_del_day=values(terminal_del_day)," " terminal_del_month=values(terminal_del_month)," " terminal_del_year=values(terminal_del_year)," " login_day=values(login_day)," " login_month=values(login_month)," " login_year=values(login_year)," " active=values(active)," " deactive=values(deactive)," " terminal_online=values(terminal_online)," " terminal_offline=values(terminal_offline)," " terminal_individual=values(terminal_individual)," " terminal_enterprise=values(terminal_enterprise)") in_login_day = self.db.get(sql_in_login, day_start_time, day_end_time ) in_login_month = self.db.get(sql_in_login, month_start_time, day_end_time) in_login_year = self.db.get(sql_in_login, year_start_time, day_end_time) in_active = self.db.get(sql_in_active, month_start_time, day_end_time) individuals = self.db.get("SELECT COUNT(tu.id) AS num" " FROM T_USER as tu, T_TERMINAL_INFO as tti" " WHERE tu.uid = tti.owner_mobile" " AND (tti.mobile LIKE '14778%%' OR tti.mobile LIKE '1847644%%')") in_deactive = DotDict(num=individuals.num-in_active.num) in_terminal_online_count = self.db.get(sql_terminal_line_count + " WHERE service_status=1 AND group_id=-1 AND login != 0 AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%')") in_terminal_offline_count = self.db.get(sql_terminal_line_count + " WHERE service_status=1 AND group_id=-1 AND login=0 AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%')") self.db.execute(sql_kept, 0, 0, 0,in_terminal_add_day, in_terminal_add_month, in_terminal_add_year, in_terminal_del_day, in_terminal_del_month, in_terminal_del_year, in_login_day.num, in_login_month.num, in_login_year.num, in_active.num, in_deactive.num, in_terminal_online_count.num, in_terminal_offline_count.num, 0, 0, day_end_time, UWEB.STATISTIC_USER_TYPE.INDIVIDUAL) #2: enterprise stattis e_corp_add_day = self.db.get(sql_corp_add, day_start_time, day_end_time ) e_corp_add_month = self.db.get(sql_corp_add, month_start_time, day_end_time ) e_corp_add_year = self.db.get(sql_corp_add, year_start_time, day_end_time ) e_login_day = self.db.get(sql_en_login, day_start_time, day_end_time ) e_login_month = self.db.get(sql_en_login, month_start_time, day_end_time ) e_login_year = self.db.get(sql_en_login, year_start_time, day_end_time ) e_active = self.db.get(sql_en_active, month_start_time, day_end_time) oper = self.db.get("SELECT count(id) as num" " FROM T_OPERATOR") enterprise = self.db.get("SELECT count(id) as num" " FROM T_CORP") e_deactive = DotDict(num=enterprise.num+oper.num-e_active.num) e_terminal_online_count = self.db.get(sql_terminal_line_count + " WHERE service_status=1 AND group_id != -1 AND login != 0 AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%') ") e_terminal_offline_count = self.db.get(sql_terminal_line_count + " WHERE service_status=1 AND group_id != -1 AND login = 0 AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%')") self.db.execute(sql_kept, e_corp_add_day.num, e_corp_add_month.num, e_corp_add_year.num, e_terminal_add_day, e_terminal_add_month, e_terminal_add_year, e_terminal_del_day, e_terminal_del_month, e_terminal_del_year, e_login_day.num, e_login_month.num, e_login_year.num, e_active.num, e_deactive.num, e_terminal_online_count.num, e_terminal_offline_count.num, 0, 0, day_end_time, UWEB.STATISTIC_USER_TYPE.ENTERPRISE) # 3 total statistic terminal_total_in = self.db.get("SELECT count(id) AS num FROM T_TERMINAL_INFO WHERE group_id = -1") terminal_total_en = self.db.get("SELECT count(id) AS num FROM T_TERMINAL_INFO WHERE group_id != -1") self.db.execute(sql_kept, e_corp_add_day.num, e_corp_add_month.num, e_corp_add_year.num, in_terminal_add_day+e_terminal_add_day, in_terminal_add_month+e_terminal_add_month, in_terminal_add_year+e_terminal_add_year, in_terminal_del_day+e_terminal_del_day, in_terminal_del_month+e_terminal_del_month, in_terminal_del_year+e_terminal_del_year, in_login_day.num+e_login_day.num, in_login_month.num+e_login_month.num, in_login_year.num+e_login_year.num, in_active.num+e_active.num, in_deactive.num+e_deactive.num, in_terminal_online_count.num+e_terminal_online_count.num, in_terminal_offline_count.num+e_terminal_offline_count.num, terminal_total_in.num, terminal_total_en.num, day_end_time, UWEB.STATISTIC_USER_TYPE.TOTAL) end_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) logging.info("[CELERY] %s statistic_user finished", end_time) except Exception as e: logging.exception("[CELERY] statistic_user terminal exception.") def statistic_offline_terminal(self, epoch_time): """Export data into excel file. """ start_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time())) logging.info("[CELERY] %s statistic_offline_terminal started.", start_time) current_day = time.localtime(epoch_time) day_start_time, day_end_time = start_end_of_day(current_day.tm_year, current_day.tm_mon, current_day.tm_mday) month_start_time, month_end_time = start_end_of_month(current_day.tm_year, current_day.tm_mon) year_start_time, year_end_time = start_end_of_year(current_day.tm_year) logging.info("[CELERY] day_start_time: %s, day_end_time: %s, month_start_time: %s, month_end_time: %s, year_start_time: %s, year_end_time: %s.", day_start_time, day_end_time, month_start_time, month_end_time, year_start_time, year_end_time) import xlwt import xlrd BASE_PATH = '/var/ydcws/reports/' # NOTE: chinese filename cannot download successfully, so here use # english filename. wish one day chinese words can work well OFFLINE_FILE_NAME = u"terminals_offline" #OFFLINE_FILE_NAME = u"离线用户统计表" cur_path = time.strftime("%Y%m%d",time.localtime(epoch_time) ) pre_path = time.strftime("%Y%m%d",time.localtime(epoch_time-60*60*24)) PRE_PATH = BASE_PATH + OFFLINE_FILE_NAME + '-' + pre_path + '.xls' CUR_PATH = BASE_PATH + OFFLINE_FILE_NAME + '-' + cur_path + '.xls' OFFLINE_HEADER = (u"车主号", u"终端号", u"电量", u"离线时间", u"累计离线时间", u"离线原因", u"唤醒指令下发频次", u"今日新增", u"当前状态", u"基站定位结果", u"备注") OFFLINE_DETAIL_HEADER = (u"用户类型", u"车主号", u"终端号", u"电量", u"离线时间", u"累计离线时间", u"离线原因", u"备注") # offline-terminals of this day cur_sql_cmd = ("SELECT id, owner_mobile as umobile, mobile as tmobile," " begintime, offline_time, pbat, remark" " FROM T_TERMINAL_INFO" " WHERE service_status = 1 AND login =0 " " AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%') " " AND (offline_time BETWEEN %s AND %s) ORDER BY pbat") # offline-terminals till now #terminals_offline_cmd = ("SELECT id, owner_mobile as umobile, mobile as tmobile," # " begintime, offline_time, pbat, remark, group_id" # " FROM T_TERMINAL_INFO" # " WHERE service_status = 1 AND login =0 " # " AND (mobile LIKE '14778%%' OR mobile LIKE '1847644%%') " # " ORDER BY group_id, pbat") terminals_offline_cmd = ("SELECT id, owner_mobile as umobile, mobile as tmobile," " begintime, offline_time, pbat, remark, group_id" " FROM T_TERMINAL_INFO" " WHERE service_status=1 AND login=0 " " ORDER BY group_id, pbat") terminal_sql_cmd = "SELECT login, remark, offline_time FROM T_TERMINAL_INFO WHERE mobile = %s LIMIT 1" cur_res = self.db.query(cur_sql_cmd, day_start_time, epoch_time) terminals_ofline = self.db.query(terminals_offline_cmd) tmobile_lst = [] for item in cur_res: tmobile_lst.append(item['tmobile']) item['offline_period'] = int(time.time()) - item['offline_time'] item['offline_cause'] = 2 if item['pbat'] < 5 else 1 item['sim_status'] = u'失败' if item['offline_cause'] == 1: # heart beat # check the sim status terminal_log = self.db.get("SELECT sim_status FROM T_BIND_LOG" " WHERE tmobile = %s LIMIT 1", item['tmobile']) if terminal_log.sim_status == 1: item['sim_status'] = u'成功' item['remark'] = safe_unicode(item['remark']) logging.info('[CELERY] the currentrecords to be dealed with, counts: %s, cur_res: %s', len(cur_res), cur_res) # NOTE: get last day's data pre_res = [] if not os.path.isfile(PRE_PATH): logging.info("[CELERY] pre_path: %s cannot be found.", PRE_PATH) else: wb=xlrd.open_workbook(PRE_PATH) sh=wb.sheet_by_name(u'离线汇总分析') for rownum in range(1,sh.nrows): # get records from the second row row = sh.row_values(rownum) if row[1] in tmobile_lst: continue if row[8] == u'在线': continue tmobile = row[1] terminal = self.db.get(terminal_sql_cmd, tmobile) current_status = u'离线' if not terminal: current_status = u'已解绑' row[8] = current_status else: if terminal['login'] !=0: current_status = u'在线' row[8] = current_status offline_period = int(time.time()) - terminal['offline_time'] row[4] = seconds_to_label(offline_period) d,m = divmod(offline_period,60*60) count = d+1 if m else d row[6] = count row[10] = safe_unicode(terminal['remark']) pre_res.append(row) logging.info('[CELERY] the previous records to be dealed with, count: %s, pre_res: %s', len(pre_res), pre_res) # some styles #date_style = xlwt.easyxf(num_format_str='YYYY-MM-DD HH:mm:ss') title_style = xlwt.easyxf('pattern: pattern solid, fore_colour ocean_blue; font: bold off; align: wrap on, vert centre, horiz center;' "borders: top double, bottom double, left double, right double;") abnormal_style = xlwt.easyxf('font: colour_index red, bold off; align: wrap on, vert centre, horiz center;') add_style = xlwt.easyxf('font: colour_index blue, bold off; align: wrap on, vert centre, horiz center;') powerlow_style = xlwt.easyxf('font: colour_index dark_yellow, bold off; align: wrap on, vert centre, horiz center;') online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;') offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;') center_style = xlwt.easyxf('align: wrap on, vert centre, horiz center;') wb = xlwt.Workbook() ws = wb.add_sheet(u'离线汇总分析') ws_detail = wb.add_sheet(u'离线明细') # sheet 1: 离线汇总分析 start_line = 0 for i, head in enumerate(OFFLINE_HEADER): ws.write(0, i, head, title_style) ws.col(0).width = 4000 # umobile ws.col(1).width = 4000 # tmobile ws.col(3).width = 4000 * 2 # offline_time ws.col(4).width = 4000 * 2 # offline_period ws.col(6).width = 4000 # lq count ws.col(9).width = 4000 # sim_status ws.col(10).width = 4000 * 4 # remark start_line += 1 results = cur_res for i, result in zip(range(start_line, len(results) + start_line), results): ws.write(i, 0, result['umobile'], center_style) ws.write(i, 1, result['tmobile'], center_style) ws.write(i, 2, str(result['pbat'])+'%', center_style) ws.write(i, 3, time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(result['offline_time'])), center_style) ws.write(i, 4, seconds_to_label(result['offline_period']), center_style) if result['offline_cause'] == 2: offline_cause = u'低电关机' ws.write(i, 5, offline_cause, powerlow_style) else: offline_cause = u'通讯异常' ws.write(i, 5, offline_cause, abnormal_style) if result['sim_status'] == u'成功': ws.write(i, 9, safe_unicode(result['sim_status']), online_style) else: ws.write(i, 9, safe_unicode(result['sim_status']), abnormal_style) d,m = divmod(result['offline_period'],60*60) count = d+1 if m else d ws.write(i, 6, count) terminal = self.db.get("SELECT remark FROM T_TERMINAL_INFO where id = %s", result['id']) ws.write(i, 7, u'新增', add_style) ws.write(i, 10, safe_unicode(terminal['remark']), center_style) start_line += 1 logging.info('[CELERY] current offline records, count: %s, tmobile_lst: %s', len(tmobile_lst), tmobile_lst) results = pre_res for i, result in zip(range(start_line, len(results) + start_line), results): #for j in range(len(OFFLINE_HEADER)): # ws.write(i, j, result[j]) #if result[1] in tmobile_lst: # continue ws.write(i, 0, result[0], center_style) ws.write(i, 1, result[1], center_style) ws.write(i, 2, result[2], center_style) ws.write(i, 3, result[3], center_style) ws.write(i, 4, result[4], center_style) if result[5] == u'低电关机': ws.write(i, 5, u'低电关机', powerlow_style) else: ws.write(i, 5, u'通讯异常', abnormal_style) ws.write(i, 6, result[6]) if result[8] == u'在线': ws.write(i, 8, u'在线', online_style) elif result[8] == u'已解绑': ws.write(i, 8, u'已解绑') else: pass #ws.write(i, 9, result[9], center_style) ws.write(i, 10, result[10], center_style) # sheet 2: 离线明细 start_line = 0 for i, head in enumerate(OFFLINE_DETAIL_HEADER): ws_detail.write(0, i, head, title_style) ws_detail.col(1).width = 4000 ws_detail.col(2).width = 4000 ws_detail.col(4).width = 4000 * 2 ws_detail.col(5).width = 4000 * 2 ws_detail.col(7).width = 4000 * 4 start_line += 1 results = terminals_ofline for i, result in zip(range(start_line, len(results) + start_line), results): # some modification if result['group_id'] == -1: result['user_type'] = UWEB.USER_TYPE.PERSON else: result['user_type'] = UWEB.USER_TYPE.CORP offline_period = int(time.time()) - result['offline_time'] result['offline_period'] = offline_period if offline_period > 0 else 0 result['offline_cause'] = 2 if result['pbat'] < 5 else 1 ws_detail.write(i, 0, u'个人用户' if result['user_type'] == UWEB.USER_TYPE.PERSON else u'集团用户') ws_detail.write(i, 1, result['umobile'], center_style) ws_detail.write(i, 2, result['tmobile'], center_style) ws_detail.write(i, 3, str(result['pbat'])+'%', center_style) ws_detail.write(i, 4, time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(result['offline_time'])), center_style) ws_detail.write(i, 5, seconds_to_label(result['offline_period'])) if result['offline_cause'] == 2: offline_cause = u'低电关机' ws_detail.write(i, 6, offline_cause, powerlow_style) else: offline_cause = u'通讯异常' ws_detail.write(i, 6, offline_cause, abnormal_style) #ws_detail.write(i, 6, u'低电关机' if result['offline_cause'] == 2 else u'通讯异常') terminal_offline = self.db.get("SELECT remark FROM T_TERMINAL_INFO where id = %s", result['id']) ws_detail.write(i, 7, safe_unicode(terminal_offline['remark'])) start_line += 1 wb.save(CUR_PATH) content = u'附件是 %s 的离线报表统计,请查收! 详情请看:%s ' % (cur_path,ConfHelper.ADMIN_CONF.url) subject = u'移动车卫士离线分析报告' + cur_path EmailHelper.send(self.to_emails, content, self.cc_emails, files=[CUR_PATH], subject=subject) logging.info("[CELERY] statistic_offline_terminal finished, cur_path: %s", CUR_PATH) def statistic_misc(self): """Handle the old data. #NOTE: Self-test & Deprecated """ ##self.db.execute("truncate T_SUBSCRIPTION_LOG") ##self.db.execute("truncate T_STATISTIC") #terminals = self.db.query("select id, tid, mobile, begintime, offline_time, group_id from T_TERMINAL_INFO") #for terminal in terminals: # # 1: record to T_SUBSCRIPTION # record_terminal_subscription(self.db, terminal['mobile'], terminal['group_id'], terminal['begintime'], terminal['begintime'],1) # # 2: modify the offline_time # if terminal['offline_time'] == 0: # self.db.execute("UPDATE T_TERMINAL_INFO set offline_time = %s where id = %s ", # terminal['begintime'], terminal['id']) #part 2: for new statistic, transfer data from T_TERMINAL to T_BIND_LOG #self.db.execute("TRUNCATE T_BIND_LOG") #terminals = self.db.query("SELECT id, tid, mobile, begintime, offline_time, group_id from T_TERMINAL_INFO where service_status = 1") #for terminal in terminals: # #1376755199, 2013.8.17; 1376668799, 2013.08.16 # #record_add_action(terminal.mobile, terminal.group_id, 1376668799, self.db) # record_add_action(terminal.mobile, terminal.group_id, 1376755199, self.db) # #record_add_action(terminal.mobile, terminal.group_id, int(time.time()), self.db) # part 3: handle terminals to be removed #terminals = self.db.query("SELECT id, tid, mobile, begintime, offline_time, group_id from T_TERMINAL_INFO where service_status = 2") #for terminal in terminals: # logging.info("Delete to be removed terminal:%s with no log in T_BIND_LOG.", terminal.mobile) # delete_terminal_no_record(terminal.tid, self.db, self.redis, del_user=True) pass
def send_all(content): db = DBConnection().db terminals = db.query("SELECT mobile FROM T_TERMINAL_INFO") for terminal in terminals: send(content, terminal.mobile)
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 MSmsMixin(object): def __init__(self): self.mysql_db = DBConnection().db def retrieve_mixin(self, city_list=None, start_time=None, end_time=None): # this is for parents #res = self.mysql_db.query("SELECT tsl.mobile, tsl.fetchtime, txu.name AS user_name," # " txg.xxt_id AS group_id, txg.name AS group_name," # " thc.region_code AS city_id, thc.city_name AS city," # " thp.province_id AS province_id, thp.province_name AS province" # " FROM T_SMS_LOG AS tsl, T_XXT_USER AS txu," # " T_XXT_GROUP AS txg, T_HLR_CITY AS thc," # " T_HLR_PROVINCE AS thp" # " WHERE tsl.fetchtime BETWEEN %s AND %s" # " AND tsl.mobile = txu.mobile" # " AND txu.group_id = txg.xxt_id" # " AND txg.city_id = thc.region_code" # " AND thc.province_id = thp.province_id", # start_time, end_time) results = [] cities = city_info(city_list, self.mysql_db) for city in cities: #res_parents = self.mysql_db.query("call P_SMS_PARENTS(%s, %s, %s)", # start_time, end_time, city.id) sms_pres_parent = self.mysql_db.query("SELECT DISTINCT txu.mobile, txu.name AS user_name, " " txg.xxt_id AS group_id, txg.name AS group_name," " thc.region_code AS city_id, thc.city_name AS city," " thp.province_id AS province_id, thp.province_name AS province" " FROM T_XXT_USER AS txu," " T_XXT_GROUP AS txg," " T_HLR_CITY AS thc," " T_HLR_PROVINCE AS thp" " WHERE txu.group_id = txg.xxt_id " " AND txg.city_id = thc.region_code" " AND thc.province_id = thp.province_id" " AND txg.city_id = %s", city.id) res_parents = [] for sms_pre in sms_pres_parent: sms_parents = self.mysql_db.query("SELECT mobile, fetchtime" " FROM T_SMS_LOG" " where fetchtime BETWEEN %s AND %s" " AND mobile = %s", start_time, end_time, sms_pre.mobile) for sms_parent in sms_parents: res_parent = DotDict({'mobile':sms_parent.mobile, 'fetchtime':sms_parent.fetchtime, 'user_name':sms_pre.user_name, 'group_id':sms_pre.group_id, 'group_name':sms_pre.group_name, 'city_id':sms_pre.city_id, 'city':sms_pre.city, 'province_id':sms_pre.province_id, 'province':sms_pre.province}) res_parents.append(res_parent) results.extend(res_parents) #this is for targets #res = self.mysql_db.query("SELECT tsl.mobile, tsl.fetchtime, txt.name AS user_name," # " txg.xxt_id AS group_id, txg.name AS group_name," # " thc.region_code AS city_id, thc.city_name AS city," # " thp.province_id AS province_id, thp.province_name AS province" # " FROM T_SMS_LOG AS tsl, T_XXT_TARGET AS txt," # " T_XXT_GROUP AS txg, T_HLR_CITY AS thc," # " T_HLR_PROVINCE AS thp" # " WHERE tsl.fetchtime BETWEEN %s AND %s" # " AND tsl.mobile = txt.mobile" # " AND txt.group_id = txg.xxt_id" # " AND txg.city_id = thc.region_code" # " AND thc.province_id = thp.province_id", # start_time, end_time) # res_targets = self.mysql_db.query("call P_SMS_TARGETS(%s, %s, %s)", # start_time, end_time, city.id) sms_pres_target = self.mysql_db.query("SELECT DISTINCT txt.mobile, txt.name AS user_name, " " txg.xxt_id AS group_id, txg.name AS group_name," " thc.region_code AS city_id, thc.city_name AS city," " thp.province_id AS province_id, thp.province_name AS province" " FROM T_XXT_TARGET AS txt," " T_XXT_GROUP AS txg," " T_HLR_CITY AS thc," " T_HLR_PROVINCE AS thp" " WHERE txt.group_id = txg.xxt_id " " AND txg.city_id = thc.region_code" " AND thc.province_id = thp.province_id" " AND txg.city_id = %s", city.id) res_targets = [] for sms_pre in sms_pres_target: sms_targets = self.mysql_db.query("SELECT mobile, fetchtime" " FROM T_SMS_LOG" " WHERE fetchtime BETWEEN %s AND %s" " AND mobile = %s", start_time, end_time, sms_pre.mobile) for sms_target in sms_targets: res_target = DotDict({'mobile':sms_target.mobile, 'fetchtime':sms_target.fetchtime, 'user_name':sms_pre.user_name, 'group_id':sms_pre.group_id, 'group_name':sms_pre.group_name, 'city_id':sms_pre.city_id, 'city':sms_pre.city, 'province_id':sms_pre.province_id, 'province':sms_pre.province}) res_targets.append(res_target) results.extend(res_targets) return results def distinct(self, results): """recount the result, provide for frontend""" res = [] mobiles = [r['mobile'] for r in results] d_mobiles = list(set([r['mobile'] for r in results])) for mobile in d_mobiles: for result in results: if mobile == result['mobile']: count = mobiles.count(mobile) r = {'province':result['province'], 'city':result['city'], 'mobile':mobile, 'count':count, 'group_name':result['group_name'], 'group_id':result['group_id'], 'user_name':result['user_name']} res.append(r) break return res
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 MDailyMixin(object): def __init__(self): self.mysql_db = DBConnection().db self.collection = self.mongodb_db.daily def retrieve_mixin(self, city_list=None, end_time=None): results = [] cities = city_info(city_list, self.mysql_db) #optype_status = (XXT.OPER_TYPE.CREATE, XXT.OPER_TYPE.RESUME, XXT.OPER_TYPE.UPDATE) for city in cities: #total_groups = self.mysql_db.get("SELECT count(*) as total" # " FROM T_XXT_GROUP" # " WHERE timestamp <= %s" # " AND city_id = %s", # end_time, city.id) total_groups = self.mysql_db.get("call P_DAILY_TOTAL_GROUPS(%s, %s)", end_time, city.id) #total_parents = self.mysql_db.get("SELECT count(T_XXT_USER.xxt_uid) as total" # " FROM T_XXT_USER, T_XXT_GROUP" # " WHERE T_XXT_USER.timestamp <= %s" # " AND optype IN %s" # " AND group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s", # end_time, tuple(optype_status), city.id) #NOTE: change utc to the format as 201201312359590000 end_time_parent = time.strftime("%Y%m%d%H%M%S9999", time.localtime(end_time/1000)) total_parents = self.mysql_db.get("call P_DAILY_TOTAL_PARENTS(%s, %s, %s)", end_time_parent, XXT.VALID.VALID, city.id) #total_children = self.mysql_db.get("SELECT count(T_XXT_TARGET.xxt_tid) as total" # " FROM T_XXT_TARGET, T_XXT_GROUP" # " WHERE T_XXT_TARGET.timestamp <= %s" # " AND optype IN %s" # " AND group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s", # end_time, tuple(optype_status), city.id) total_children = self.mysql_db.get("call P_DAILY_TOTAL_CHILDREN(%s, %s, %s)", end_time, XXT.VALID.VALID, city.id) #mo_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total" # " FROM T_SMS_LOG" # " WHERE T_SMS_LOG.category = %s" # " AND T_SMS_LOG.fetchtime <= %s" # " AND ((T_SMS_LOG.mobile in" # " (SELECT mobile FROM T_XXT_USER, T_XXT_GROUP" # " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s)) OR " # " (T_SMS_LOG.mobile in" # " (SELECT mobile FROM T_XXT_TARGET, T_XXT_GROUP" # " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s)))", # SMS.CATEGORY.RECEIVE, # end_time, city.id, city.id) #mo_sms = self.mysql_db.get("call P_DAILY_MO_SMS(%s, %s, %s)", # SMS.CATEGORY.RECEIVE, end_time, city.id) #NOTE: get mobiles for parents and children, then query sms_log through it mobile_parents = self.mysql_db.query("SELECT mobile" " FROM T_XXT_USER, T_XXT_GROUP" " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id" " AND T_XXT_GROUP.city_id = %s", city.id) mobile_children = self.mysql_db.query("SELECT mobile" " FROM T_XXT_TARGET, T_XXT_GROUP" " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" " AND T_XXT_GROUP.city_id = %s", city.id) mobiles=[] mobiles.extend(mobile_parents) mobiles.extend(mobile_children) mobiles = [int(mobile['mobile']) for mobile in mobiles] mo_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total" " FROM T_SMS_LOG" " WHERE T_SMS_LOG.category = %s" " AND fetchtime <= %s" " AND T_SMS_LOG.mobile IN %s", SMS.CATEGORY.RECEIVE, end_time, tuple(mobiles + DUMMY_IDS)) #mt_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total" # " FROM T_SMS_LOG" # " WHERE T_SMS_LOG.category = %s" # " AND T_SMS_LOG.fetchtime <= %s" # " AND ((T_SMS_LOG.mobile in" # " (SELECT mobile FROM T_XXT_USER, T_XXT_GROUP" # " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s)) OR " # " (T_SMS_LOG.mobile in" # " (SELECT mobile FROM T_XXT_TARGET, T_XXT_GROUP" # " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s)))", # SMS.CATEGORY.SEND, # end_time, city.id, city.id) # mt_sms and mo_sms call the same procedure #mt_sms = self.mysql_db.get("call P_DAILY_MO_SMS(%s, %s, %s)", # SMS.CATEGORY.SEND, end_time, city.id) mt_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total" " FROM T_SMS_LOG" " WHERE T_SMS_LOG.category = %s" " AND fetchtime <= %s" " AND T_SMS_LOG.mobile IN %s", SMS.CATEGORY.SEND, end_time, tuple(mobiles + DUMMY_IDS)) # customer, schedule and realtiem call the same procedure #custom = self.mysql_db.get("SELECT count(*) as total" # " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" # " WHERE category = %s" # " AND T_LOCATION.timestamp <= %s" # " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" # " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" # " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s", # LOCATION.CATEGORY.CUSTOM, end_time, city.id) #custom = self.mysql_db.get("call P_DAILY_CUSTOM(%s, %s, %s)", # LOCATION.CATEGORY.CUSTOM, end_time, city.id) #schedule = self.mysql_db.get("SELECT count(*) as total" # " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" # " WHERE category = %s" # " AND T_LOCATION.timestamp <= %s" # " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" # " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" # " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s", # LOCATION.CATEGORY.SCHEDULE, end_time, city.id) schedule = self.mysql_db.get("call P_DAILY_CUSTOM(%s, %s, %s)", LOCATION.CATEGORY.SCHEDULE, end_time, city.id) #realtime= self.mysql_db.get("SELECT count(*) as total" # " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" # " WHERE category = %s" # " AND T_LOCATION.timestamp <= %s" # " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" # " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" # " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s", # LOCATION.CATEGORY.REALTIME, end_time, city.id) realtime = self.mysql_db.get("call P_DAILY_CUSTOM(%s, %s, %s)", LOCATION.CATEGORY.REALTIME, end_time, city.id) #bound = self.mysql_db.get("SELECT count(*) as total" # " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" # " WHERE (category = %s OR category = %s)" # " AND T_LOCATION.timestamp <= %s" # " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" # " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" # " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s", # LOCATION.CATEGORY.REGION_ENTER, LOCATION.CATEGORY.REGION_OUT, # end_time, city.id) bound = self.mysql_db.get("call P_DAILY_BOUND(%s, %s, %s, %s)", LOCATION.CATEGORY.REGION_ENTER, LOCATION.CATEGORY.REGION_OUT, end_time, city.id) #power = self.mysql_db.get("SELECT count(*) as total" # " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" # " WHERE category = %s" # " AND T_LOCATION.timestamp <= %s" # " AND T_LOCATION.targesimt_id = T_LBMP_TERMINAL.sim" # " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" # " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s", # LOCATION.CATEGORY.POWERLOW, end_time, city.id) power = self.mysql_db.get("call P_DAILY_POWER(%s, %s, %s)", LOCATION.CATEGORY.POWERLOW, end_time, city.id) #sos = self.mysql_db.get("SELECT count(*) as total" # " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" # " WHERE category = %s" # " AND T_LOCATION.timestamp <= %s" # " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" # " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" # " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" # " AND T_XXT_GROUP.city_id = %s", # LOCATION.CATEGORY.EMERGENCY, end_time, city.id) sos = self.mysql_db.get("call P_DAILY_SOS(%s, %s, %s)", LOCATION.CATEGORY.EMERGENCY, end_time, city.id) # NOTE: query old_data from mongodb, then add the increment value in past # day: schedule, realtime, bound, sos, power old_daily = self.collection.find_one({'timestamp': end_time - 86400000}, {'_id':0}) #custom_total = DotDict(old_daily).custom if old_daily else 0 schedule_total = DotDict(old_daily).schedule if old_daily else 0 realtime_total = DotDict(old_daily).realtime if old_daily else 0 bound_total = DotDict(old_daily).bound if old_daily else 0 sos_total = DotDict(old_daily).sos if old_daily else 0 power_total = DotDict(old_daily).power if old_daily else 0 res = {'_id': None, 'province_id': city.p_id, 'city_id': city.id, 'province': city.p_name, 'city': city.name, 'total_groups': total_groups.total, 'total_parents': total_parents.total, 'total_children': total_children.total, 'mt_sms': mt_sms.total, 'mo_sms': mo_sms.total, # total = new + new + ... + new #'custom': custom.total + custom_total, 'schedule': schedule.total + schedule_total, 'realtime': realtime.total + realtime_total, 'bound': bound.total + bound_total, 'sos': sos.total + sos_total, 'power': power.total + power_total, 'timestamp': end_time} results.append(res) return results