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
