def wash_location():
db = DBConnection().db
redis = MyRedis()
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where tid = '36E2400480' "
sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO"
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0"
#print 'sql', sql
terminals = db.query(sql)
print 'len ', len(terminals)
count = 0
cnt = 0
for i, t in enumerate(terminals):
tid = t.tid
key = 'location:%s' % tid
location = redis.get(key)
if not location:
#if True:
print 'tid', tid
location = db.get("SELECT timestamp, MAX(timestamp) as maxtime"
" FROM T_LOCATION"
" WHERE tid = %s"
" AND type = 0"
" AND latitude != 0",
tid)
if location:
if location.timestamp != location.maxtime:
print 'timestamp != maxtime, tid', tid
location = db.get("SELECT * FROM T_LOCATION where timestamp = %s AND tid = %s limit 1", location.maxtime, tid)
else:
continue
mem_location = DotDict({'id':location.id,
'latitude':location.latitude,
'longitude':location.longitude,
'type':location.type,
'clatitude':location.clatitude,
'clongitude':location.clongitude,
'timestamp':location.timestamp,
'name':location.name,
'degree':float(location.degree),
'speed':float(location.speed),
'locate_error':int(location.locate_error)})
redis.setvalue(key, mem_location, 86400*356*2)
count = count +1
print 'handled tid:', tid
else:
cnt = cnt + 1
print 'total hanlded count:', count
print 'total not hanlded count:', cnt
def 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)
class MGroupMixin(object):
def __init__(self):
self.mysql_db = DBConnection().db
def retrieve_mixin(self, citylist=None):
results = []
for city in citylist:
infos = self.mysql_db.query("SELECT thp.province_id AS id, thp.province_name AS province_name,"
" thc.city_name AS city_name"
" FROM T_HLR_CITY AS thc, T_HLR_PROVINCE AS thp"
" WHERE thc.region_code = %s"
" AND thc.province_id = thp.province_id", city)
for info in infos:
groups = self.mysql_db.query("SELECT txg.id, txg.xxt_id, txg.name,"
" txa.xxt_id AS agency_id, txa.name AS agency_name"
" FROM T_XXT_GROUP AS txg LEFT JOIN T_XXT_AGENCY AS txa"
" ON txa.xxt_id = txg.agency_id"
" WHERE txg.city_id = %s", city)
for group in groups:
#total_parents = self.mysql_db.get("SELECT count(*) AS total"
# " FROM T_XXT_USER AS txu"
# " WHERE txu.group_id = %s", group.xxt_id)
total_parents = self.mysql_db.get("call P_GROUP_TOTAL_PARENTS(%s)",
group.xxt_id)
#total_children = self.mysql_db.get("SELECT count(*) AS total"
# " FROM T_XXT_TARGET AS txt"
# " WHERE txt.group_id = %s", group.xxt_id)
total_children = self.mysql_db.get("call P_GROUP_TOTAL_CHILDREN(%s)",
group.xxt_id)
if not group.agency_name:
group.agency_name = u'暂无'
res = {'_id': None,
'province_id': info.id,
'city_id': city,
'id': group.id,
'group_id': group.xxt_id,
'group_name': group.name,
'agency_id': group.agency_id,
'agency': group.agency_name,
'total_parents': total_parents.total,
'total_children': total_children.total,
'city': info.city_name,
'province': info.province_name}
results.append(res)
return results
def export_excel():
db = DBConnection().db
online_style = xlwt.easyxf("font: colour_index green, bold off; align: wrap on, vert centre, horiz center;")
offline_style = xlwt.easyxf("font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;")
wt = xlwt.Workbook()
ws = wt.add_sheet(u"jia")
# sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL'
# sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO limit 5'
# sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status=1'
# sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status=1 and mobile like '%%%%14778%%%%' "
# sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where group_id = 310 "
sql = "select tid, mobile from V_TERMINAL where cid = '13823936111'"
terminals = db.query(sql)
print "len ", len(terminals)
for i, t in enumerate(terminals):
terminal = db.get("select owner_mobile from T_TERMINAL_INFO where tid = %s", t.tid)
car = db.get("select cnum from T_CAR where tid = %s", t.tid)
print "t", t
print "ter", terminal
print "car", car
# reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
ws.write(i, 0, terminal.owner_mobile)
ws.write(i, 1, t.mobile)
ws.write(i, 2, car.cnum)
# lst += "'" + mobile + "',"
# print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';'
# db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)"
# " VALUES(NULL, %s)"
# " ON DUPLICATE KEY"
# " UPDATE mobile = values(mobile)", mobile)
# content = ':SIM ' + umobile + ':' + mobile
# print content
# SMSHelper.send_to_terminal(mobile, content)
# print '%s sucessfully.' % mobile
wt.save("/tmp/jia_13823936111_corp.xls")
def execute():
db = DBConnection().db
redis = MyRedis()
terminals = db.query("SELECT tid, mobile FROM T_TERMINAL_INFO ")
print "len ", len(terminals)
for t in terminals:
tid = t.tid
print "tid", tid
car = db.get("select * from T_CAR where tid = %s", tid)
if car:
print "has car "
pass
else:
print "insert car"
def send_all(content):
db = DBConnection().db
terminals = db.query("SELECT * FROM V_TERMINAL WHERE cid in ( '15207610001' )")
# terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0")
# terminals = db.query("SELECT mobile FROM T_TERMINAL_INFO where login=0")
# terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0")
# terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0 and offline_time>1408197915")
# terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0 and offline_time>1411452000")
print "len: %s", len(terminals)
for t in terminals:
terminal = db.get("select login, tid, mobile from T_TERMINAL_INFO where tid = %s", t.tid)
if terminal.login == 0: # offline
print "--", terminal
send(content, terminal.mobile)
class CheckService(object):
def __init__(self):
self.db = DBConnection().db
self.tid = 'B123SIMULATOR'
self.mobiles = [15982463820, 15882081659]
self.emails = ['*****@*****.**', '*****@*****.**']
#self.emails = ['*****@*****.**', '*****@*****.**', '*****@*****.**']
#self.emails = ['*****@*****.**', '*****@*****.**', '*****@*****.**', '*****@*****.**']
def check_service(self):
try:
base_id = self.get_lid_by_tid(self.tid)
while True:
time.sleep(600) # 10 minutes
new_lid = self.get_lid_by_tid(self.tid)
logging.info("[CK] simulator terminal location base_id:%s, new_lid:%s",
base_id, new_lid)
if new_lid > base_id:
base_id = new_lid
else:
for mobile in self.mobiles:
sms = SMSCode.SMS_SERVICE_EXCEPTION_REPORT % ConfHelper.UWEB_CONF.url_out
SMSHelper.send(mobile, sms)
logging.info("[CK] Notify Administrator:%s By SMS, service exception!",
mobile)
for email in self.emails:
content = SMSCode.SMS_SERVICE_EXCEPTION_REPORT % ConfHelper.UWEB_CONF.url_out
EmailHelper.send(email, content)
logging.info("[CK] Notify Administrator:%s By EMAIL, service exception!",
email)
except KeyboardInterrupt:
logging.error("Ctrl-C is pressed.")
except Exception as e:
logging.exception("[CK] Check service failed. Exception: %s",
e.args)
def get_lid_by_tid(self, tid):
location = self.db.get("SELECT id"
" FROM T_LOCATION"
" WHERE tid = %s"
" ORDER BY id DESC LIMIT 1",
tid)
if location:
return location.id
else:
return 0
class MLocationMixin(object):
def __init__(self):
self.mysql_db = DBConnection().db
def retrieve_mixin(self, city_list=None, start_time=None, end_time=None):
results = []
d = datetime.date.fromtimestamp(start_time/1000)
year = d.year
month = d.month
cities = city_info(city_list, self.mysql_db)
for city in cities:
groups = self.mysql_db.query("SELECT T_XXT_GROUP.name as group_name, T_XXT_GROUP.xxt_id"
" FROM T_XXT_GROUP WHERE T_XXT_GROUP.city_id = %s",
city.id)
for group in groups:
for item, category in [ #('custom', LOCATION.CATEGORY.CUSTOM),
('schedule', LOCATION.CATEGORY.SCHEDULE),
('realtime', LOCATION.CATEGORY.REALTIME)]:
#data = self.mysql_db.get("SELECT count(T_LOCATION.id) as total"
# " FROM T_LOCATION, T_XXT_TARGET, T_LBMP_TERMINAL"
# " WHERE T_LOCATION.timestamp BETWEEN %s AND %s"
# " AND T_LOCATION.category = %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = %s",
# start_time, end_time, category, group.xxt_id)
data = self.mysql_db.get("call P_LOCATION(%s, %s, %s, %s)",
start_time, end_time, category, group.xxt_id)
group[item] = data.total
res = {'_id': None,
'province_id': city.p_id,
'city_id': city.id,
'group_id': group.xxt_id,
'province': city.p_name,
'city': city.name,
'group_name': group.group_name,
# 'custom': group.custom,
'schedule': group.schedule,
'realtime': group.realtime,
'year': year,
'month': month}
results.append(res)
return results
def block_test():
db = DBConnection().db
redis = MyRedis()
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 main():
ConfHelper.load('../conf/global.conf')
parse_command_line()
db = DBConnection().db
redis = MyRedis()
#tid = '35A60002B3'
tid = '36E24006A6'
location = db.get("SELECT id, speed, timestamp, category, name,"
" degree, type, latitude, longitude, clatitude, clongitude,"
" timestamp, locate_error"
" FROM T_LOCATION"
" WHERE tid = %s"
" AND type = 0"
" AND NOT (latitude = 0 AND longitude = 0)"
" ORDER BY timestamp DESC"
" LIMIT 1",
tid)
print 'location:', location
def export_excel():
db = DBConnection().db
online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;')
offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;')
wt = xlwt.Workbook()
ws = wt.add_sheet(u'jia')
sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO where service_status=1 and begintime<1396281600 order by begintime "
terminals = db.query(sql)
print 'len ', len(terminals)
for i, t in enumerate(terminals):
#terminal = db.get("select owner_mobile from T_TERMINAL_INFO where tid = %s", t.tid)
#car= db.get("select cnum from T_CAR where tid = %s", t.tid)
biz= db.get("select biz_type from T_BIZ_WHITELIST where mobile = %s limit 1", t.mobile)
biz_type = 10
if biz:
if biz['biz_type'] == 1:
biz_type = 20
print 't', t
reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
#reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
ws.write(i,0,t.owner_mobile)
ws.write(i,1,t.mobile)
ws.write(i,2,reg_time)
ws.write(i,3,biz_type)
#lst += "'" + mobile + "',"
#print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';'
#db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)"
# " VALUES(NULL, %s)"
# " ON DUPLICATE KEY"
# " UPDATE mobile = values(mobile)", mobile)
#content = ':SIM ' + umobile + ':' + mobile
#print content
#SMSHelper.send_to_terminal(mobile, content)
#print '%s sucessfully.' % mobile
wt.save('/tmp/jia_biz_type.xls')
def batch_import(file_path):
db = DBConnection().db
redis = MyRedis()
online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;')
offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;')
wt = xlwt.Workbook()
ws = wt.add_sheet(u'jia')
wb = xlrd.open_workbook(file_path)
sheet = wb.sheets()[0]
lst = ""
num = 0
for i in range(0,sheet.nrows):
row = sheet.row_values(i)
print 'row', row
mobile = unicode(row[0])
mobile = mobile[0:11]
#mobile = unicode(row[0])[0:-2]
print 'mobile', repr(mobile)
sql_t = 'select tid, test, login, service_status from T_TERMINAL_INFO where mobile =' + mobile
terminal = db.get(sql_t)
print 'terminal', terminal
tid = terminal.get('tid','') if terminal else ''
sql = 'UPDATE T_TERMINAL_INFO set test=0 where mobile =' + mobile
print 'sql', sql
#db.execute('UPDATE T_TERMINAL_INFO set test=0 where mobile = %s', int(mobile))
#db.execute(sql)
sessionID_key = get_terminal_sessionID_key(tid)
print 'sessionID_key', sessionID_key
old_sessionid = redis.get(sessionID_key)
if old_sessionid:
redis.delete(sessionID_key)
logging.info("[UWEB] Termianl %s delete session in redis.", tid)
#if i == 3:
# break
print num
class MSubscriberMixin(object):
def __init__(self):
self.mysql_db = DBConnection().db
def retrieve_mixin(self, citylist=None, start_time=None, end_time=None):
results = []
d = datetime.date.fromtimestamp(start_time/1000)
year = d.year
month = d.month
cities = city_info(citylist, self.mysql_db)
#optype_status = (XXT.OPER_TYPE.CREATE, XXT.OPER_TYPE.RESUME, XXT.OPER_TYPE.UPDATE)
for city in cities:
#new_groups = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_XXT_GROUP"
# " WHERE timestamp BETWEEN %s AND %s"
# " AND city_id = %s",
# start_time, end_time, city.id)
new_groups = self.mysql_db.get("call P_SUBSCRIBER_NEW_GROUPS(%s, %s, %s)",
start_time, end_time, city.id)
#new_parents = self.mysql_db.get("SELECT count(T_XXT_USER.xxt_uid) as total"
# " FROM T_XXT_USER, T_XXT_GROUP"
# " WHERE T_XXT_USER.timestamp BETWEEN %s AND %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# start_time, end_time, tuple(optype_status), city.id)
#NOTE: change utc to the format as 201201312359590000
#only do this way when dealing with timestamp of T_XXT_USER and T_XXT_SUBSCRIPTION_LOG
start_time_parent = time.strftime("%Y%m%d%H%M%S0000", time.localtime(start_time/1000))
end_time_parent = time.strftime("%Y%m%d%H%M%S9999", time.localtime(start_time/1000))
new_parents = self.mysql_db.get("call P_SUBSCRIBER_NEW_PARENTS(%s, %s, %s)",
start_time_parent, end_time_parent, city.id)
#new_children = self.mysql_db.get("SELECT count(T_XXT_TARGET.xxt_tid) as total"
# " FROM T_XXT_TARGET, T_XXT_GROUP"
# " WHERE T_XXT_TARGET.timestamp BETWEEN %s AND %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# start_time, end_time, tuple(optype_status), city.id)
new_children = self.mysql_db.get("call P_SUBSCRIBER_NEW_CHILDREN(%s, %s, %s)",
start_time, end_time, city.id)
#total_groups = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_XXT_GROUP"
# " WHERE timestamp <= %s"
# " AND city_id = %s",
# end_time, city.id)
total_groups = self.mysql_db.get("call P_SUBSCRIBER_TOTAL_GROUPS(%s, %s)",
end_time, city.id)
#total_parents = self.mysql_db.get("SELECT count(T_XXT_USER.xxt_uid) as total"
# " FROM T_XXT_USER, T_XXT_GROUP"
# " WHERE T_XXT_USER.timestamp <= %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# end_time, tuple(optype_status), city.id)
total_parents = self.mysql_db.get("call P_SUBSCRIBER_TOTAL_PARENTS(%s, %s)",
end_time_parent, city.id)
#total_children = self.mysql_db.get("SELECT count(T_XXT_TARGET.xxt_tid) as total"
# " FROM T_XXT_TARGET, T_XXT_GROUP"
# " WHERE T_XXT_TARGET.timestamp <= %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# end_time, tuple(optype_status), city.id)
total_children = self.mysql_db.get("call P_SUBSCRIBER_TOTAL_CHILDREN(%s, %s)",
end_time, city.id)
res = {'_id': None,
'province_id':city.p_id,
'city_id': city.id,
'province':city.p_name,
'city':city.name,
'new_groups': new_groups.total,
'new_parents': new_parents.total,
'new_children': new_children.total,
'total_groups': total_groups.total,
'total_parents': total_parents.total,
'total_children': total_children.total,
'year': year,
'month': month}
results.append(res)
return results
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 MMonthlyMixin(object):
def __init__(self):
self.mysql_db = DBConnection().db
def retrieve_mixin(self, city_list=None, start_time=None, end_time=None):
results = []
d = datetime.date.fromtimestamp(start_time/1000)
year = d.year
month = d.month
cities = city_info(city_list, self.mysql_db)
for city in cities:
#sql = "SELECT count(*) AS new_groups" + \
# " FROM T_XXT_GROUP" + \
# " WHERE timestamp BETWEEN %s AND %s" + \
# " AND city_id = %s"
#sql = sql % (start_time, end_time, city.id)
#new_groups = self.mysql_db.get(sql)
new_groups = self.mysql_db.get("call P_MONTHLY_NEW_GROUPS(%s, %s, %s)",
start_time, end_time, city.id)
#sql = "SELECT count(T_XXT_USER.xxt_uid) as total" + \
# " FROM T_XXT_USER, T_XXT_GROUP" + \
# " WHERE T_XXT_USER.timestamp BETWEEN %s AND %s" + \
# " AND optype IN (1,3,4)" + \
# " AND group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (start_time, end_time, XXT.VALID.VALID, city.id)
#new_parents = self.mysql_db.get(sql)
#NOTE: change utc to the format as 201201312359590000
#only do this way when dealing with time of T_XXT_USER and T_XXT_SUBSCRIPTION_LOG
start_time_parent = time.strftime("%Y%m%d%H%M%S0000", time.localtime(start_time/1000))
end_time_parent = time.strftime("%Y%m%d%H%M%S9999", time.localtime(start_time/1000))
new_parents = self.mysql_db.get("call P_MONTHLY_NEW_PARENTS(%s, %s, %s)",
start_time_parent, end_time_parent, city.id)
#sql = "SELECT count(T_XXT_TARGET.xxt_tid) as total" + \
# " FROM T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE T_XXT_TARGET.timestamp BETWEEN %s AND %s" + \
# " AND valid = %s" + \
# " AND group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (start_time, end_time, XXT.VALID.VALID, city.id)
#new_children = self.mysql_db.get(sql)
new_children = self.mysql_db.get("call P_MONTHLY_NEW_CHILDREN(%s, %s, %s)",
start_time, end_time, city.id)
#sql = "SELECT count(*) as total" + \
# " FROM T_XXT_GROUP" + \
# " WHERE timestamp <= %s" + \
# " AND city_id = %s"
#sql = sql % (end_time, city.id)
#total_groups = self.mysql_db.get(sql)
total_groups = self.mysql_db.get("call P_MONTHLY_TOTAL_GROUPS(%s, %s)",
end_time, city.id)
#sql = "SELECT count(T_XXT_USER.xxt_uid) as total" + \
# " FROM T_XXT_USER, T_XXT_GROUP" + \
# " WHERE T_XXT_USER.timestamp <= %s" + \
# " AND optype IN (1, 3, 4)" + \
# " AND group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (end_time, XXT.VALID.VALID, city.id)
#total_parents = self.mysql_db.get(sql)
total_parents = self.mysql_db.get("call P_MONTHLY_TOTAL_PARENTS(%s, %s)",
end_time_parent, city.id)
#sql = "SELECT count(T_XXT_TARGET.xxt_tid) as total" + \
# " FROM T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE T_XXT_TARGET.timestamp <= %s" + \
# " AND optyped IN (1, 3, 4)" + \
# " AND group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (end_time, XXT.VALID.VALID, city.id)
#total_children = self.mysql_db.get(sql)
total_children = self.mysql_db.get("call P_MONTHLY_TOTAL_CHILDREN(%s, %s)",
end_time, city.id)
#sql = "SELECT count(T_SMS_LOG.id) as total" + \
# " FROM T_SMS_LOG" + \
# " WHERE T_SMS_LOG.category = %s" + \
# " AND T_SMS_LOG.fetchtime BETWEEN %s AND %s" + \
# " AND ((T_SMS_LOG.mobile in" + \
# " (SELECT mobile FROM T_XXT_USER, T_XXT_GROUP" + \
# " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s)) OR " + \
# " (T_SMS_LOG.mobile in" + \
# " (SELECT mobile FROM T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s)))"
#sql = sql % (SMS.CATEGORY.RECEIVE, start_time, end_time, city.id, city.id)
#mo_sms = self.mysql_db.get(sql)
mobile_parents = self.mysql_db.query("SELECT mobile"
" FROM T_XXT_USER, T_XXT_GROUP"
" WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id"
" AND T_XXT_GROUP.city_id = %s",
city.id)
mobile_children = self.mysql_db.query("SELECT mobile"
" FROM T_XXT_TARGET, T_XXT_GROUP"
" WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
" AND T_XXT_GROUP.city_id = %s",
city.id)
mobiles=[]
mobiles.extend(mobile_parents)
mobiles.extend(mobile_children)
mobiles = [int(mobile['mobile']) for mobile in mobiles]
mo_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
" FROM T_SMS_LOG"
" WHERE T_SMS_LOG.category = %s"
" AND fetchtime BETWEEN %s AND %s"
" AND T_SMS_LOG.mobile IN %s",
SMS.CATEGORY.RECEIVE, start_time, end_time,
tuple(mobiles + DUMMY_IDS))
#mo_sms = self.mysql_db.get("call P_MONTHLY_MO_SMS(%s, %s, %s, %s)",
# SMS.CATEGORY.RECEIVE, start_time, end_time, city.id)
#sql = "SELECT count(T_SMS_LOG.id) as total" + \
# " FROM T_SMS_LOG" + \
# " WHERE T_SMS_LOG.category = %s" + \
# " AND T_SMS_LOG.fetchtime BETWEEN %s AND %s" + \
# " AND ((T_SMS_LOG.mobile in" + \
# " (SELECT mobile FROM T_XXT_USER, T_XXT_GROUP" + \
# " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s)) OR " + \
# " (T_SMS_LOG.mobile in" + \
# " (SELECT mobile FROM T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s)))"
#sql = sql % (SMS.CATEGORY.SEND, start_time, end_time, city.id, city.id)
#mt_sms = self.mysql_db.get(sql)
# mt_sms and mo_sms use the same procedure#
#mt_sms = self.mysql_db.get("call P_MONTHLY_MO_SMS(%s, %s, %s, %s)",
# SMS.CATEGORY.SEND, start_time, end_time, city.id)
mt_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
" FROM T_SMS_LOG"
" WHERE T_SMS_LOG.category = %s"
" AND fetchtime BETWEEN %s AND %s"
" AND T_SMS_LOG.mobile IN %s",
SMS.CATEGORY.SEND, start_time, end_time,
tuple(mobiles + DUMMY_IDS))
# In fact, total_sms = mo_sms + mt_sms
# there is of no necessity to query the database
#sql = "SELECT count(T_SMS_LOG.id) as total" + \
# " FROM T_SMS_LOG" + \
# " WHERE T_SMS_LOG.fetchtime BETWEEN %s AND %s" + \
# " AND ((T_SMS_LOG.mobile in" + \
# " (SELECT mobile FROM T_XXT_USER, T_XXT_GROUP" + \
# " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s)) OR " + \
# " (T_SMS_LOG.mobile in" + \
# " (SELECT mobile FROM T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s)))"
#sql = sql % (start_time, end_time, city.id, city.id)
#total_sms = self.mysql_db.get(sql)
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %s" + \
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" + \
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" + \
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (LOCATION.CATEGORY.CUSTOM, start_time, end_time, city.id)
#custom = self.mysql_db.get(sql)
#custom = self.mysql_db.get("call P_MONTHLY_CUSTOM(%s, %s, %s, %s)",
# LOCATION.CATEGORY.CUSTOM, start_time, end_time, city.id)
# custom, schedule and realtime use the same schedule
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %s" + \
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" + \
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" + \
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (LOCATION.CATEGORY.SCHEDULE, start_time, end_time, city.id)
#schedule = self.mysql_db.get(sql)
schedule = self.mysql_db.get("call P_MONTHLY_CUSTOM(%s, %s, %s, %s)",
LOCATION.CATEGORY.SCHEDULE, start_time, end_time, city.id)
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %s" + \
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" + \
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" + \
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (LOCATION.CATEGORY.REALTIME, start_time, end_time, city.id)
#realtime = self.mysql_db.get(sql)
realtime = self.mysql_db.get("call P_MONTHLY_CUSTOM(%s, %s, %s, %s)",
LOCATION.CATEGORY.REALTIME, start_time, end_time, city.id)
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE (category = %s OR category = %s)" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %s" + \
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" + \
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" + \
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (LOCATION.CATEGORY.REGION_ENTER,
# LOCATION.CATEGORY.REGION_OUT, start_time, end_time, city.id)
#bound = self.mysql_db.get(sql)
bound = self.mysql_db.get("call P_MONTHLY_BOUND(%s, %s, %s, %s, %s)",
LOCATION.CATEGORY.REGION_ENTER, LOCATION.CATEGORY.REGION_OUT, start_time, end_time, city.id)
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %s" + \
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" + \
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" + \
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (LOCATION.CATEGORY.POWERLOW, start_time, end_time, city.id)
#power = self.mysql_db.get(sql)
power = self.mysql_db.get("call P_MONTHLY_POWER(%s, %s, %s, %s)",
LOCATION.CATEGORY.POWERLOW, start_time, end_time, city.id)
#sql = "SELECT count(*) as total" + \
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP" + \
# " WHERE category = %s" + \
# " AND T_LOCATION.timestamp BETWEEN %s AND %s" + \
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim" + \
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile" + \
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id" + \
# " AND T_XXT_GROUP.city_id = %s"
#sql = sql % (LOCATION.CATEGORY.EMERGENCY, start_time, end_time, city.id)
#sos = self.mysql_db.get(sql)
sos = self.mysql_db.get("call P_MONTHLY_SOS(%s, %s, %s, %s)",
LOCATION.CATEGORY.EMERGENCY, start_time, end_time, city.id)
res = {'_id': None,
'province_id': city.p_id,
'city_id': city.id,
'province': city.p_name,
'city': city.name,
'new_groups': new_groups.total,
'new_parents': new_parents.total,
'new_children': new_children.total,
'total_groups': total_groups.total,
'total_parents': total_parents.total,
'total_children': total_children.total,
'total_sms': mt_sms.total + mo_sms.total,
'mt_sms': mt_sms.total,
'mo_sms': mo_sms.total,
#'custom': custom.total,
'schedule': schedule.total,
'realtime': realtime.total,
'bound': bound.total,
'sos': sos.total,
'power': power.total,
'year': year,
'month': month}
results.append(res)
return results
class Test():
def __init__(self):
self.db = DBConnection().db
self.redis = MyRedis()
def get_track(self, tid, start_time, end_time, cellid=False):
"""NOTE: Now, only return gps point.
"""
if cellid:
track = self.db.query("SELECT id, latitude, longitude, clatitude,"
" clongitude, timestamp, name, type, speed, degree, locate_error"
" FROM T_LOCATION"
" WHERE tid = %s"
" AND NOT (latitude = 0 OR longitude = 0)"
" AND (timestamp BETWEEN %s AND %s)"
" GROUP BY timestamp"
" ORDER BY timestamp",
tid, start_time, end_time)
else: # gps, pvt
track = self.db.query("SELECT id, latitude, longitude, clatitude,"
" clongitude, timestamp, name, type, speed, degree, locate_error"
" FROM T_LOCATION"
" WHERE tid = %s"
" AND category = 1"
" AND NOT (latitude = 0 OR longitude = 0)"
" AND (timestamp BETWEEN %s AND %s)"
" AND type = 0"
" GROUP BY timestamp"
" ORDER BY timestamp",
tid, start_time, end_time)
return track
#def get_track_distance(self, track):
# """Get distance of a section of track.
# """
# distance = 0
# if not track:
# pass
# else:
# start_point = None
# for point in track:
# if not start_point:
# start_point = point
# continue
# else:
# distance += get_distance(start_point["longitude"], start_point["latitude"],
# point["longitude"], point["latitude"])
# start_point = point
# return distance
def handle_stop(self, tid, start_time, end_time):
track = self.get_track(tid, start_time, end_time)
print 'track', len(track)
cnt = 0
for i, pvt in enumerate(track):
#print 'i: %s, speed: %s, pvt: %s' % (i, pvt['speed'], pvt)
stop_key = 'test_stop_redis:%s' % tid
stop = self.redis.getvalue(stop_key)
distance_key = 'test_distance_redis:%s' % tid
distance = self.redis.get(distance_key)
if not distance:
distance = 0
last_pvt_key = 'test_last_pvt_redis:%s' % tid
last_pvt = self.redis.getvalue(last_pvt_key)
if last_pvt:
tmp = get_distance(int(last_pvt["longitude"]), int(last_pvt["latitude"]),
int(pvt["longitude"]), int(pvt["latitude"]))
print 'tmp: %s, distance: %s' % (tmp, distance)
distance = float(distance) + tmp
print 'last distance: %s' % (distance)
#print 'add distance', i, pvt['id'], tmp, distance
self.redis.setvalue(distance_key, distance, time=EVENTER.STOP_EXPIRY)
if pvt['speed'] > LIMIT.SPEED_LIMIT: # 5 is moving
if stop: #NOTE: time_diff is too short, drop the point.
if pvt["timestamp"] - stop['start_time'] < 60: # 60 seconds
cnt += 1
_stop = self.db.get("select distance from T_STOP where lid =%s", stop['lid'])
if _stop:
tmp_dis = _stop['distance']
else:
tmp_dis = 0
print 'tmp_dis', tmp_dis
distance = float(distance) + tmp_dis
print 'tmp_dis distance', distance
self.db.execute("DELETE FROM T_STOP WHERE lid = %s",
stop['lid'])
self.redis.delete(stop_key)
self.redis.setvalue(distance_key, distance, time=EVENTER.STOP_EXPIRY)
logging.info("[EVENTER] Stop point is droped: %s", stop)
else: # close a stop point
cnt += 1
self.redis.delete(stop_key)
self.db.execute("UPDATE T_STOP SET end_time = %s WHERE lid = %s",
pvt["timestamp"], stop['lid'])
logging.info("[EVENTER] Stop point is closed: %s", stop)
else:
pass
else: # low speed, may stop
if stop:
stop['end_time'] = pvt["timestamp"]
self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY)
logging.info("[EVENTER] Stop point is updated: %s", stop)
else: # NOTE: start stop. #NOTE: create a new stop point
cnt += 1
lid=pvt['id']
stop = dict(lid=lid,
tid=tid,
start_time=pvt["timestamp"],
end_time=0,
pre_lon=pvt["longitude"],
pre_lat=pvt["latitude"],
distance=distance)
self.db.execute("INSERT INTO T_STOP(lid, tid, start_time, distance) VALUES(%s, %s, %s, %s)",
lid, tid, pvt["timestamp"], distance)
self.redis.setvalue(stop_key, stop, time=EVENTER.STOP_EXPIRY)
self.redis.delete(distance_key)
logging.info("[EVENTER] Stop point is created: %s", stop)
last_pvt = pvt
self.redis.setvalue(last_pvt_key, last_pvt, time=EVENTER.STOP_EXPIRY)
print '---------------------- cnt', cnt
def clear_stop(self, tid, start_time, end_time):
self.db.execute("DELETE FROM T_STOP WHERE tid = %s and start_time between %s and %s",
tid, start_time, end_time)
stop_key = 'test_stop_redis:%s' % tid
distance_key = 'test_distance_redis:%s' % tid
last_pvt_key = 'test_last_pvt_redis:%s' % tid
self.redis.delete(stop_key)
self.redis.delete(distance_key)
self.redis.delete(last_pvt_key)
def batch_import(file_path):
db = DBConnection().db
online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;')
offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;')
wt = xlwt.Workbook()
ws = wt.add_sheet(u'jia')
wb = xlrd.open_workbook(file_path)
sheet = wb.sheets()[0]
lst = ""
num = 0
#for i in range(1,sheet.nrows):
for i in range(0,sheet.nrows):
row = sheet.row_values(i)
print 'roe', row
#mobile = unicode(row[0])[0:-2]
mobile = unicode(row[0])[0:11] # is recommend
print 'mobile', mobile
t = db.get("select id, mobile, login, tid, owner_mobile, from_unixtime(begintime) as begin_time,"
" from_unixtime(login_time) as login_time, domain from T_TERMINAL_INFO where mobile = %s and service_status=1",
mobile)
print 't', t
if not t:
label= u'未激活'
b = db.query('select * from T_BIND_LOG where tmobile=%s', mobile)
if b:
label= u'已解绑'
umobile=''
print 'not: ', mobile
else:
label = u'已激活'
print 't: ', t
umobile=t.owner_mobile
num += 1
print 'label', label
ws.write(i,0,mobile)
ws.write(i,1,umobile)
if label == u'未激活':
ws.write(i,2,label, offline_style)
else:
ws.write(i,2,label, online_style)
if t:
begin_time = t.begin_time
login_time = t.login_time
#reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
begintime = str(begin_time.year)+'-'+str(begin_time.month)+'-' +str(begin_time.day) +'-'+str(begin_time.hour)+':'+str(begin_time.minute)+':'+str(begin_time.second)
logintime = str(login_time.year)+'-'+str(login_time.month)+'-'+ str(login_time.day) +'-'+str(login_time.hour)+':'+str(login_time.minute)+':'+str(login_time.second)
ws.write(i,3,begintime)
ws.write(i,4,logintime)
#lst += "'" + mobile + "',"
#print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';'
#db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)"
# " VALUES(NULL, %s)"
# " ON DUPLICATE KEY"
# " UPDATE mobile = values(mobile)", mobile)
#content = ':SIM ' + umobile + ':' + mobile
#print content
#SMSHelper.send_to_terminal(mobile, content)
#print '%s sucessfully.' % mobile
print num
wt.save('/tmp/jia_new.xls')
def execute(mobile):
db = DBConnection().db
redis = MyRedis()
terminal = db.get("SELECT tid, mobile FROM T_TERMINAL_INFO WHERE mobile = %s LIMIT 1", mobile)
if terminal:
delete_terminal(terminal.tid, db, redis, del_user=True)
def wash_location():
db = DBConnection().db
redis = MyRedis()
#NOTE: all offline terminal
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0"
#NOTE: all offline terminals who has login before.
sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0 and login_time>0"
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where tid = '354A000121'"
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where mobile = '14778742419'"
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where mobile = '14778749929'"
#sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login_time>0"
#print 'sql', sql
terminals = db.query(sql)
#print 'len ', len(terminals)
count = 0
cnt = 0
no_loc = 0
for i, t in enumerate(terminals):
tid = t.tid
key = 'location:%s' % tid
location = redis.getvalue(key)
if not location:
time.sleep(2)
no_loc = no_loc + 1
print 'no location, tid', tid
#continue
##return
##redis.delete(key)
#NOTE: get latest point.
location = db.get("SELECT timestamp, MAX(timestamp) as maxtime"
" FROM T_LOCATION"
" WHERE tid = %s"
" AND type = 0"
" AND latitude != 0",
tid)
if location and location['timestamp']:
location = db.get("SELECT * FROM T_LOCATION where timestamp = %s AND tid = %s AND latitude != 0 limit 1", location.maxtime, tid)
mem_location = DotDict({'id':location.id,
'latitude':location.latitude,
'longitude':location.longitude,
'type':location.type,
'clatitude':location.clatitude,
'clongitude':location.clongitude,
'timestamp':location.timestamp,
'name':location.name,
'degree':float(location.degree),
'speed':float(location.speed),
'locate_error':int(location.locate_error)})
redis.setvalue(key, mem_location, 86400*356*2)
count = count +1
print 'handled tid:', tid
else:
cnt = cnt + 1
print '-------no_loc', no_loc
print 'total hanlded count:', count
print 'total not hanlded count:', cnt
class 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 MDailyMixin(object):
def __init__(self):
self.mysql_db = DBConnection().db
self.collection = self.mongodb_db.daily
def retrieve_mixin(self, city_list=None, end_time=None):
results = []
cities = city_info(city_list, self.mysql_db)
#optype_status = (XXT.OPER_TYPE.CREATE, XXT.OPER_TYPE.RESUME, XXT.OPER_TYPE.UPDATE)
for city in cities:
#total_groups = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_XXT_GROUP"
# " WHERE timestamp <= %s"
# " AND city_id = %s",
# end_time, city.id)
total_groups = self.mysql_db.get("call P_DAILY_TOTAL_GROUPS(%s, %s)",
end_time, city.id)
#total_parents = self.mysql_db.get("SELECT count(T_XXT_USER.xxt_uid) as total"
# " FROM T_XXT_USER, T_XXT_GROUP"
# " WHERE T_XXT_USER.timestamp <= %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# end_time, tuple(optype_status), city.id)
#NOTE: change utc to the format as 201201312359590000
end_time_parent = time.strftime("%Y%m%d%H%M%S9999", time.localtime(end_time/1000))
total_parents = self.mysql_db.get("call P_DAILY_TOTAL_PARENTS(%s, %s, %s)",
end_time_parent, XXT.VALID.VALID, city.id)
#total_children = self.mysql_db.get("SELECT count(T_XXT_TARGET.xxt_tid) as total"
# " FROM T_XXT_TARGET, T_XXT_GROUP"
# " WHERE T_XXT_TARGET.timestamp <= %s"
# " AND optype IN %s"
# " AND group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# end_time, tuple(optype_status), city.id)
total_children = self.mysql_db.get("call P_DAILY_TOTAL_CHILDREN(%s, %s, %s)",
end_time, XXT.VALID.VALID, city.id)
#mo_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
# " FROM T_SMS_LOG"
# " WHERE T_SMS_LOG.category = %s"
# " AND T_SMS_LOG.fetchtime <= %s"
# " AND ((T_SMS_LOG.mobile in"
# " (SELECT mobile FROM T_XXT_USER, T_XXT_GROUP"
# " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s)) OR "
# " (T_SMS_LOG.mobile in"
# " (SELECT mobile FROM T_XXT_TARGET, T_XXT_GROUP"
# " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s)))",
# SMS.CATEGORY.RECEIVE,
# end_time, city.id, city.id)
#mo_sms = self.mysql_db.get("call P_DAILY_MO_SMS(%s, %s, %s)",
# SMS.CATEGORY.RECEIVE, end_time, city.id)
#NOTE: get mobiles for parents and children, then query sms_log through it
mobile_parents = self.mysql_db.query("SELECT mobile"
" FROM T_XXT_USER, T_XXT_GROUP"
" WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id"
" AND T_XXT_GROUP.city_id = %s",
city.id)
mobile_children = self.mysql_db.query("SELECT mobile"
" FROM T_XXT_TARGET, T_XXT_GROUP"
" WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
" AND T_XXT_GROUP.city_id = %s",
city.id)
mobiles=[]
mobiles.extend(mobile_parents)
mobiles.extend(mobile_children)
mobiles = [int(mobile['mobile']) for mobile in mobiles]
mo_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
" FROM T_SMS_LOG"
" WHERE T_SMS_LOG.category = %s"
" AND fetchtime <= %s"
" AND T_SMS_LOG.mobile IN %s",
SMS.CATEGORY.RECEIVE, end_time,
tuple(mobiles + DUMMY_IDS))
#mt_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
# " FROM T_SMS_LOG"
# " WHERE T_SMS_LOG.category = %s"
# " AND T_SMS_LOG.fetchtime <= %s"
# " AND ((T_SMS_LOG.mobile in"
# " (SELECT mobile FROM T_XXT_USER, T_XXT_GROUP"
# " WHERE T_XXT_USER.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s)) OR "
# " (T_SMS_LOG.mobile in"
# " (SELECT mobile FROM T_XXT_TARGET, T_XXT_GROUP"
# " WHERE T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s)))",
# SMS.CATEGORY.SEND,
# end_time, city.id, city.id)
# mt_sms and mo_sms call the same procedure
#mt_sms = self.mysql_db.get("call P_DAILY_MO_SMS(%s, %s, %s)",
# SMS.CATEGORY.SEND, end_time, city.id)
mt_sms = self.mysql_db.get("SELECT count(T_SMS_LOG.id) as total"
" FROM T_SMS_LOG"
" WHERE T_SMS_LOG.category = %s"
" AND fetchtime <= %s"
" AND T_SMS_LOG.mobile IN %s",
SMS.CATEGORY.SEND, end_time,
tuple(mobiles + DUMMY_IDS))
# customer, schedule and realtiem call the same procedure
#custom = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.CUSTOM, end_time, city.id)
#custom = self.mysql_db.get("call P_DAILY_CUSTOM(%s, %s, %s)",
# LOCATION.CATEGORY.CUSTOM, end_time, city.id)
#schedule = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.SCHEDULE, end_time, city.id)
schedule = self.mysql_db.get("call P_DAILY_CUSTOM(%s, %s, %s)",
LOCATION.CATEGORY.SCHEDULE, end_time, city.id)
#realtime= self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.REALTIME, end_time, city.id)
realtime = self.mysql_db.get("call P_DAILY_CUSTOM(%s, %s, %s)",
LOCATION.CATEGORY.REALTIME, end_time, city.id)
#bound = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE (category = %s OR category = %s)"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.REGION_ENTER, LOCATION.CATEGORY.REGION_OUT,
# end_time, city.id)
bound = self.mysql_db.get("call P_DAILY_BOUND(%s, %s, %s, %s)",
LOCATION.CATEGORY.REGION_ENTER, LOCATION.CATEGORY.REGION_OUT, end_time, city.id)
#power = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.targesimt_id = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.POWERLOW, end_time, city.id)
power = self.mysql_db.get("call P_DAILY_POWER(%s, %s, %s)",
LOCATION.CATEGORY.POWERLOW, end_time, city.id)
#sos = self.mysql_db.get("SELECT count(*) as total"
# " FROM T_LOCATION, T_LBMP_TERMINAL, T_XXT_TARGET, T_XXT_GROUP"
# " WHERE category = %s"
# " AND T_LOCATION.timestamp <= %s"
# " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
# " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
# " AND T_XXT_TARGET.group_id = T_XXT_GROUP.xxt_id"
# " AND T_XXT_GROUP.city_id = %s",
# LOCATION.CATEGORY.EMERGENCY, end_time, city.id)
sos = self.mysql_db.get("call P_DAILY_SOS(%s, %s, %s)",
LOCATION.CATEGORY.EMERGENCY, end_time, city.id)
# NOTE: query old_data from mongodb, then add the increment value in past
# day: schedule, realtime, bound, sos, power
old_daily = self.collection.find_one({'timestamp': end_time - 86400000}, {'_id':0})
#custom_total = DotDict(old_daily).custom if old_daily else 0
schedule_total = DotDict(old_daily).schedule if old_daily else 0
realtime_total = DotDict(old_daily).realtime if old_daily else 0
bound_total = DotDict(old_daily).bound if old_daily else 0
sos_total = DotDict(old_daily).sos if old_daily else 0
power_total = DotDict(old_daily).power if old_daily else 0
res = {'_id': None,
'province_id': city.p_id,
'city_id': city.id,
'province': city.p_name,
'city': city.name,
'total_groups': total_groups.total,
'total_parents': total_parents.total,
'total_children': total_children.total,
'mt_sms': mt_sms.total,
'mo_sms': mo_sms.total,
# total = new + new + ... + new
#'custom': custom.total + custom_total,
'schedule': schedule.total + schedule_total,
'realtime': realtime.total + realtime_total,
'bound': bound.total + bound_total,
'sos': sos.total + sos_total,
'power': power.total + power_total,
'timestamp': end_time}
results.append(res)
return results
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
def batch_import(file_path):
db = DBConnection().db
online_style = xlwt.easyxf("font: colour_index green, bold off; align: wrap on, vert centre, horiz center;")
offline_style = xlwt.easyxf("font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;")
wt = xlwt.Workbook()
ws = wt.add_sheet(u"polly")
wb = xlrd.open_workbook(file_path)
sheet = wb.sheets()[0]
lst = ""
num = 0
ws.write(0, 0, u"终端号码")
ws.write(0, 1, u"绑定时间")
# for i in range(1,sheet.nrows):
for i in range(0, sheet.nrows):
row = sheet.row_values(i)
print "roe", row
# mobile = unicode(row[0])[0:-2]
# mobile = unicode(row[0])[0:11] # is recommend
mobile = int(row[0])
print "mobile", mobile
sql = (
"SELECT tmobile,FROM_UNIXTIME(add_time) as add_time,FROM_UNIXTIME(del_time) as del_time from T_BIND_LOG where tmobile = '%s' HAVING MIN(FROM_UNIXTIME(add_time))"
% mobile
)
# print"sql:", sql
t = db.get(sql)
if not t:
i = i + 1
print "ttttttttttttt:", t
add_time = ""
del_time = ""
if t:
tmobile = t.tmobile
add_time = t.add_time
del_time = t.del_time
add_time = (
str(add_time.year)
+ "-"
+ str(add_time.month)
+ "-"
+ str(add_time.day)
+ "-"
+ str(add_time.hour)
+ ":"
+ str(add_time.minute)
+ ":"
+ str(add_time.second)
)
del_time = (
str(del_time.year)
+ "-"
+ str(del_time.month)
+ "-"
+ str(del_time.day)
+ "-"
+ str(del_time.hour)
+ ":"
+ str(del_time.minute)
+ ":"
+ str(del_time.second)
)
ws.write(i + 1, 0, mobile)
ws.write(i + 1, 1, add_time)
# lst += "'" + mobile + "',"
# print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';'
# db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)"
# " VALUES(NULL, %s)"
# " ON DUPLICATE KEY"
# " UPDATE mobile = values(mobile)", mobile)
# content = ':SIM ' + umobile + ':' + mobile
# print content
# SMSHelper.send_to_terminal(mobile, content)
# print '%s sucessfully.' % mobile
print num
print "iiiiiiiiiiiiiiiii", i
wt.save("/home/ydcws/polly_new.xls")