def move_data(): db = DBConnection().db mobiles = ['18310505991', '13693675352', '13581731204'] message = "数据库T_LOCATION已经完全转移到T_LOCATION_NEW,请及确认表信息的正确性和完整性。" #max_row = 1000000000 max_row = 250000000 begin_time = time.gmtime(time.time()) for i in range(10000, max_row, 10000): sql = "INSERT INTO T_LOCATION_NEW" \ " SELECT * FROM T_LOCATION WHERE id <=%d AND id > %d -10000" \ " and (timestamp between 0 and 1448899200)" % (i, i) logging.info("exectue sql:%s", sql) n = db.execute(sql) #time.sleep(0.1) logging.info("last record row id =%s", n) break # if i = 250000000: if i == 240000000: for mobile in mobiles: SMSHelper.send(mobile, message) print "send", mobile end_time = time.gmtime(time.time()) L_bak = "alter table T_LOCATION rename to T_LOCATION_bak" NEW_L = "alter table T_LOCATION_NEW rename to T_LOCATION" for i in range(1, 5): time.sleep(1) logging.info("Will rename table neame after %d second", 5-i) db.execute(L_bak) db.execute(NEW_L) logging.info("exchange tables T_LOCATION and T_LOCATION_NEW is accomplished ") logging.info("Move table data begin_time:%s, end_time:%s", begin_time, end_time)
def batch_import(file_path): db = DBConnection().db wb = xlrd.open_workbook(file_path) sheet = wb.sheets()[0] lst = "" num = 0 for i in range(sheet.nrows): row = sheet.row_values(i) mobile = unicode(row[0]) mobile = mobile[0:11] t = db.get('select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = %s', mobile) if not t: num += 1 print 'not: ', mobile db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)" " VALUES(NULL, %s)" " ON DUPLICATE KEY" " UPDATE mobile = values(mobile)", mobile) else: print 't', t #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
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)
def add_whitelist(): db = DBConnection().db begin = 20000000000 num = 100 for mobile in xrange(begin, begin+num+1): print 'mobile', mobile db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)" " VALUES(NULL, %s)" " ON DUPLICATE KEY" " UPDATE mobile = values(mobile)", mobile)
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 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
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'") #terminals = db.query("SELECT tid, mobile, group_id FROM T_TERMINAL_INFO WHERE stop_interval != 0 limit 1000") print 'len ', len(terminals) for terminal in terminals: tid = terminal.tid db.execute("UPDATE T_TERMINAL_INFO SET stop_interval=0 WHERE tid = %s", tid) print 'tid: %s stop_interval 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 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
def insert_sms(): ConfHelper.load('../conf/global.conf') parse_command_line() db = DBConnection().db redis = MyRedis() msgid=str(int(time.time() * 1000))[-9:] mobile = '18310505991' insert_time = int(time.time() * 1000) category = 2 # SMS.CATEGORY.MT #send_status = -1 # SMS.SENDSTATUS.PREPARING send_status = 0 # SMS.SENDSTATUS.SUCCESS count = 3 for i in xrange(500): content= 'test sms' content = content + 'seq: %s' % i db.execute("INSERT INTO T_SMS(msgid, mobile, content, " " insert_time, category, send_status) " " VALUES(%s, %s, %s, %s, %s, %s)", msgid, mobile, content, insert_time, category, send_status)
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
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
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)
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 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 CheckTask(object): def __init__(self): self.db = DBConnection().db self.redis = MyRedis() def check_track_status(self): logging.info("[CELERY] checkertask check track status started.") try: terminals = self.db.query("SELECT tid FROM T_TERMINAL_INFO" " WHERE track = 1" " AND service_status = 1") for terminal in terminals: track_key = get_track_key(terminal.tid) track = self.redis.get(track_key) logging.info("[CK] track: %s, tid: %s", track, terminal.tid) if not track: self.db.execute("UPDATE T_TERMINAL_INFO" " SET track = 0" " WHERE tid = %s LIMIT 1", terminal.tid) sessionID_key = get_terminal_sessionID_key(terminal.tid) self.redis.delete(sessionID_key) logging.info("[CK] Turn off track of terminal: %s", terminal.tid) except Exception as e: logging.exception("[CELERY] Check track status exception.") def check_poweroff_timeout(self): logging.info("[CELERY] checkertask check poweroff timeout started.") try: terminals = self.db.query("SELECT tpt.tid, tpt.sms_flag, tpt.timestamp" " FROM T_POWEROFF_TIMEOUT as tpt, T_TERMINAL_INFO as tti" " WHERE tti.tid = tpt.tid" " AND tti.service_status = 1" " AND tti.login = %s" " AND tpt.sms_flag = %s" " AND tpt.timestamp < %s", GATEWAY.TERMINAL_LOGIN.OFFLINE, GATEWAY.POWEROFF_TIMEOUT_SMS.UNSEND, (time.time() - 2*60*60)) for terminal in terminals: terminal_info = QueryHelper.get_terminal_info(terminal.tid, self.db, self.redis) if int(terminal_info['pbat']) < 5: user = QueryHelper.get_user_by_tid(terminal.tid, self.db) sms = SMSCode.SMS_POWEROFF_TIMEOUT % terminal_info['alias'] SMSHelper.send(user.owner_mobile, sms) self.update_sms_flag(terminal.tid) logging.info("[CELERY] Send poweroff timeout sms to user:%s, tid:%s", user.owner_mobile, terminal.tid) except Exception as e: logging.exception("[CELERY] Check terminal poweroff timeout exception.") def update_sms_flag(self, tid): self.db.execute("UPDATE T_POWEROFF_TIMEOUT" " SET sms_flag = %s" " WHERE tid = %s", GATEWAY.POWEROFF_TIMEOUT_SMS.SEND, tid) def send_offline_remind_sms(self): logging.info("[CELERY] checkertask send offline remind sms started.") try: currenttime = int(time.time()) terminals = self.db.query("SELECT tid, alias, mobile, owner_mobile, offline_time" " FROM T_TERMINAL_INFO" " WHERE login = 0" " AND service_status = 1" " AND offline_time < %s", (currenttime - 24*60*60)) for terminal in terminals: sms_option = QueryHelper.get_sms_option_by_uid(terminal.owner_mobile, 'heartbeat_lost', self.db) if sms_option == UWEB.SMS_OPTION.SEND: ctime = get_terminal_time(currenttime) ctime = safe_unicode(ctime) alias = terminal['alias'] if terminal['alias'] else terminal['mobile'] sms = SMSCode.SMS_HEARTBEAT_LOST % (alias, ctime) SMSHelper.send(terminal.owner_mobile, sms) logging.info("[CELERY] Send offline remind sms to user:%s, tid:%s", terminal.owner_mobile, terminal.tid) logging.info("[CELERY] checkertask send offline remind sms finished.") except Exception as e: logging.exception("[CELERY] Check terminal poweroff timeout exception.") def check_charge_remind(self): logging.exception("[CELERY] checkertask charge remind started") try: terminals = self.db.query("SELECT tid, mobile, owner_mobile, begintime" " FROM T_TERMINAL_INFO " " WHERE service_status = 1") for terminal in terminals: begintime = int(terminal.begintime) begintime = time.strftime("%Y,%m,%d", time.localtime(begintime)).split(",") b_year = int(begintime[0]) b_month = int(begintime[1]) b_day = int(begintime[2]) currenttime = int(time.time()) currenttime = time.strftime("%Y,%m,%d", time.localtime(currenttime)).split(",") c_year = int(currenttime[0]) c_month = int(currenttime[1]) c_day = int(currenttime[2]) # get days of current month days = calendar.monthrange(c_year, c_month)[1] if b_year > c_year: continue elif b_year == c_year and b_month == c_month: # do not remind user on register month continue elif b_day < c_day: continue elif (b_day == c_day) or (b_day > days and c_day == days): # 1. equal day # 2. has no equal day on this month, send sms on the last day of this month # send charge remind sms if terminal.owner_mobile: content = SMSCode.SMS_CHARGE_REMIND % terminal.mobile SMSHelper.send(terminal.owner_mobile, content) logging.info("[CELERY] Send charge remind sms to user: %s", terminal.owner_mobile) except Exception as e: logging.exception("[CELERY] Check charge remind exception: %s", e.args) def mileage_notify(self): logging.info("[CELERY] checkertask mileage_notify started.") try: #NOTE: avoid sms is sent when the server is restart. _date = datetime.datetime.fromtimestamp(int(time.time())) if _date.hour not in (9,10): return #NOTE: get all terminals which may be notified, record them into T_MILEAGE_NOTIFICATION. terminals = self.db.query("SELECT tid, distance_notification, notify_count, left_days" " FROM T_MILEAGE_NOTIFICATION" " WHERE distance_notification != 0" " AND notify_count < 3") for terminal in terminals: terminal_info = QueryHelper.get_terminal_by_tid(terminal.tid, self.db) tid = terminal['tid'] owner_mobile = terminal_info['owner_mobile'] assist_mobile = terminal_info['assist_mobile'] #distance_current = terminal_info['distance_current'] t = self.db.get("SELECT distance_current FROM T_TERMINAL_INFO WHERE tid = %s", terminal.tid) distance_current = t['distance_current'] mobile = terminal_info['mobile'] notify_count = terminal['notify_count'] left_days = terminal['left_days'] distance_notification = terminal['distance_notification'] # NOTE: if distance_current is less than distance_notification, just skip it if distance_current < distance_notification: continue if left_days == 1: # it should be notified this day logging.info("[CELERY] Send mileage notification." " tid: %s, mobile: %s, owner_mobile: %s, assist_mobile: %s," " distance_notification: %s, distance_current: %s," " notify_count: %s, left_days: %s.", tid, mobile, owner_mobile, assist_mobile, distance_notification, distance_current, notify_count, left_days) self.db.execute("UPDATE T_MILEAGE_NOTIFICATION" " SET notify_count = %s," " left_days = %s," " notify_time = %s" " WHERE tid = %s", notify_count+1, 3, int(time.time()), tid) distance_current_ = int(round(distance_current/1000.0)) if owner_mobile: sms = SMSCode.SMS_NOTIFY % (distance_current_, terminal_info['alias']) SMSHelper.send(owner_mobile, sms) if assist_mobile: user = QueryHelper.get_user_by_mobile(owner_mobile, self.db) name = safe_unicode(user['name']) sms = SMSCode.SMS_NOTIFY_ASSIST % (mobile, owner_mobile, name, distance_current_) SMSHelper.send(assist_mobile, sms) elif left_days in (2, 3): # do not notify, just postpone one day logging.info("[CELERY] Do not send mileage notification this day," " just modify the left_days." " tid: %s, mobile: %s, owner_mobile: %s, assist_mobile: %s," " distance_notification: %s, distance_current: %s," " notify_count: %s, left_days: %s.", tid, mobile, owner_mobile, assist_mobile, distance_notification, distance_current, notify_count, left_days) self.db.execute("UPDATE T_MILEAGE_NOTIFICATION" " SET left_days = %s" " WHERE tid = %s", left_days-1, tid) else: #NOTE: It should never occur. logging.info("[CELERY] Invalid left_days: %s, mobile: %s.", left_days, mobile) except Exception as e: logging.exception("[CELERY] Mileage notification failed. Exception: %s.", e.args) def day_notify(self): logging.info("[CELERY] checkertask day_notify started.") try: #NOTE: avoid sms is sent when the server is restart. _date = datetime.datetime.fromtimestamp(int(time.time())) if _date.hour not in (9,10): return #NOTE: get all terminals which may be notified, record them into T_MILEAGE_NOTIFICATION. terminals = self.db.query("SELECT tid, day_notification, notify_count, left_days" " FROM T_DAY_NOTIFICATION" " WHERE day_notification != 0" " AND notify_count < 3") for terminal in terminals: if int(time.time()) < terminal['day_notification']: # it's not time to notify continue terminal_info = QueryHelper.get_terminal_by_tid(terminal.tid, self.db) tid = terminal['tid'] owner_mobile = terminal_info['owner_mobile'] assist_mobile = terminal_info['assist_mobile'] t = self.db.get("SELECT distance_current FROM T_TERMINAL_INFO WHERE tid = %s", terminal.tid) #distance_current = terminal_info['distance_current'] distance_current = t['distance_current'] mobile = terminal_info['mobile'] notify_count = terminal['notify_count'] left_days = terminal['left_days'] day_notification= terminal['day_notification'] if left_days == 1: # it should be notified this day logging.info("[CELERY] Send day notification." " tid: %s, mobile: %s, owner_mobile: %s, assist_mobile: %s," " day_notification: %s" " notify_count: %s, left_days: %s.", tid, mobile, owner_mobile, assist_mobile, day_notification, notify_count, left_days) self.db.execute("UPDATE T_DAY_NOTIFICATION" " SET notify_count = %s," " left_days = %s," " notify_time = %s" " WHERE tid = %s", notify_count+1, 3, int(time.time()), tid) if owner_mobile: sms = SMSCode.SMS_NOTIFY_DAY % (terminal_info['alias']) SMSHelper.send(owner_mobile, sms) if assist_mobile: user = QueryHelper.get_user_by_mobile(owner_mobile, self.db) name = safe_unicode(user['name']) sms = SMSCode.SMS_NOTIFY_ASSIST_DAY % (mobile, owner_mobile, name) SMSHelper.send(assist_mobile, sms) elif left_days in (2, 3): # do not notify, just postpone one day logging.info("[CELERY] Do not send day notification this day," " just modify the left_days." " tid: %s, mobile: %s, owner_mobile: %s, assist_mobile: %s," " day_notification: %s" " notify_count: %s, left_days: %s.", tid, mobile, owner_mobile, assist_mobile, day_notification, notify_count, left_days) self.db.execute("UPDATE T_DAY_NOTIFICATION" " SET left_days = %s" " WHERE tid = %s", left_days-1, tid) else: #NOTE: It should never occur. logging.info("[CELERY] Invalid left_days: %s, mobile: %s.", left_days, mobile) except Exception as e: logging.exception("[CELERY] Day notification failed. Exception: %s.", e.args)
class MT(object): def __init__(self, queue): ConfHelper.load(options.conf) self.db = DBConnection().db self.queue = queue def add_sms_to_queue(self): status = ErrorCode.SUCCESS 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"] nosign = mt["nosign"] packet = SMSComposer(mobile, content).result url = ConfHelper.SMS_CONF.mt_url sms = {"url":url, "packet":packet, "msgid":msgid, "mobile":mobile, "content":content, "id":id, "nosign":nosign} self.queue.put(sms) self.db.execute("UPDATE T_SMS" " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.SENDING, id) except Exception as e: status = ErrorCode.FAILED logging.exception("[SMS] add sms to queue exception: %s", e.args) finally: return status def send_sms(self, sms): try: status = ErrorCode.SUCCESS if True:# sms["nosign"]: #if sms["nosign"]: send_status, result = self.send_mt_nosign(sms["id"], sms["msgid"], sms["mobile"], sms["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", sms["mobile"], sms["content"]) status = ErrorCode.SUCCESS self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.SUCCESS, sms["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, sms["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 = HttpClient().send_http_post_request(sms['url'], sms['packet']) if result["status"] == ErrorCode.SUCCESS: parser_result = SMSParser(result["response"]) response_code = parser_result.response_code response_text = parser_result.response_text if response_code == "0": logging.info("[SMS] SMS-->Gateway success mobile = %s, content = %s, id= %s ", sms['mobile'], sms['content'], sms['id']) self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.SUCCESS, sms['id']) else: status = ErrorCode.FAILED if response_code == "5": logging.error("[SMS] SMS-->Gateway failure, gateway fault, errorcode = 5, mobile = %s, content = %s, id = %s ", sms['mobile'], sms['content'], sms['id']) else: logging.error("[SMS] SMS-->Gateway other error, errorcode = %s errortext = %s, mobile = %s, content = %s, id = %s ", response_code, response_text, sms['mobile'], sms['content'], sms['id']) self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.FAILURE, sms['id']) else: status = ErrorCode.FAILED self.db.execute("UPDATE T_SMS " " SET send_status = %s" " WHERE id = %s", SMS.SENDSTATUS.FAILURE, sms['id']) logging.error("[SMS] SMS execute send_http_post_request() failure, mobile = %s, content = %s, id = %s ", sms['mobile'], sms['content'], sms['id']) except UnicodeEncodeError as e: self.db.execute("UPDATE T_SMS" " SET send_status = %s, " " retry_status = %s " " WHERE id = %s", SMS.SENDSTATUS.FAILURE, SMS.RETRYSTATUS.YES, sms['id']) logging.exception("[SMS] Send sms encode exception : %s, msgid:%s, id:%s", e.args, sms['msgid'], sms['id']) except Exception as e: status = ErrorCode.FAILED logging.exception("[SMS] Send sms exception : %s", e.args) finally: return status def send_mt_nosign(self, id, msgid, mobile, content): url = 'http://120.197.89.173:8081/openapi/router' secret = '494e58f3a9808daea3bef94078563109' # system_para appKey = 'j1baerwhjp' sessionId = '' method = 'sms.service.send' v = '1.0' format = 'json' locale = '' sign = '' #upper() system_para_dict = dict(appKey=appKey, method=method, v=v, format=format, locale=locale, sessionId=sessionId, sign=sign) system_para_list = [] business_para_list = [] system_para_list = ["appKey" + appKey, "method" + method, "v" + v, "format" + format] if locale: system_para_list.append("locale"+locale) if sessionId: system_para_list.append("sessionId"+sessionId) #business_para phoneNumbers = mobile Content = content EntCode = '106571205329' ReportId = msgid isImmediately = True #lower business_para_list = ["phoneNumbers"+phoneNumbers, "Content"+Content, "EntCode"+EntCode, "ReportId"+str(ReportId), "isImmediately"+str(isImmediately)] business_para_dict = dict(phoneNumbers=phoneNumbers, Content=Content, EntCode=EntCode, ReportId=ReportId, isImmediately=isImmediately) parameters_list = self.get_parameters_list(system_para_list, business_para_list) sign = self.get_sign(secret, parameters_list) system_para_dict['sign'] = sign request_url = self.get_request_url(url, system_para_dict, business_para_dict) h = httplib2.Http() send_status, result = h.request(request_url) return send_status, result def get_parameters_list(self, system_para_list, business_para_list): parameters_list = [] parameters_list.extend(system_para_list) parameters_list.extend(business_para_list) parameters_list.sort() return parameters_list def get_sign(self, secret, parameters_list): parameters_str = ''.join(parameters_list) str = secret + parameters_str + secret sha1 = hashlib.sha1() sha1.update(str) mysign = sha1.hexdigest() sign = mysign.upper() return sign def get_request_url(self, url, system_para_dict, business_para_dict): request_url_list = [] request_url = url + "?" for k, v in system_para_dict.iteritems(): if v: s = k + "=" + str(v) request_url_list.append(s) for k, v in business_para_dict.iteritems(): if v: if k == "Content": content_dict = {'Content': v} v = urlencode(content_dict) request_url_list.append(v) continue s = k + "=" + str(v) request_url_list.append(s) request_url_list_str = '&'.join(request_url_list) request_url = request_url + request_url_list_str return request_url