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 post(self):
"""Provide statistics about terminal.
"""
status = ErrorCode.SUCCESS
try:
data = DotDict(json_decode(self.request.body))
logging.info("[UWEB] statistic request: %s, uid: %s, tid: %s",
data, self.current_user.uid, self.current_user.tid)
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
self.write_ret(status)
return
try:
page_size = UWEB.LIMIT.PAGE_SIZE_STATISTICS
page_number = int(data.pagenum)
page_count = int(data.pagecnt)
tid = data.tid
res = []
graphics = []
counts = []
label = u''
sql_cmd = ("SELECT longitude, latitude FROM T_LOCATION"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)"
" AND type = 0"
" ORDER BY timestamp asc")
last_cmd = ("SELECT timestamp FROM T_LOCATION"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)"
" AND type = 0"
" ORDER BY timestamp desc limit 1")
next_cmd = ("SELECT timestamp FROM T_LOCATION"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)"
" AND type = 0"
" ORDER BY timestamp asc limit 1")
dis_sum = Decimal()
current_time = int(time.time())
statistics_type = data.statistics_type
if statistics_type == UWEB.STATISTICS_TYPE.YEAR:
label = data.year + u'年'
start_time, end_time = start_end_of_year(year=data.year)
for month in range(1,12+1):
start_time_, end_time_ = start_end_of_month(year=data.year, month=str(month))
if start_time_ > current_time:
break
re = {}
re['name'] = str(month)
distance = Decimal()
points = self.db.query(sql_cmd, tid, start_time_, end_time_)
for i in range(len(points)-1):
if points[i].longitude and points[i].latitude and \
points[i+1].longitude and points[i+1].latitude:
dis = get_distance(points[i].longitude, points[i].latitude,
points[i+1].longitude, points[i+1].latitude)
distance += Decimal(str(dis))
# meter --> km
distance = '%0.1f' % (distance/1000,)
if float(distance) == 0:
distance = 0
graphics.append(float(distance))
dis_sum += Decimal(distance)
re['mileage'] = distance
res.append(re)
counts = [float(dis_sum),]
elif statistics_type == UWEB.STATISTICS_TYPE.MONTH:
label = data.year + u'年' + data.month + u'月'
start_time, end_time = start_end_of_month(year=data.year, month=data.month)
days = days_of_month(year=data.year, month=data.month)
distance = Decimal()
points_ = self.db.query(sql_cmd, tid, start_time, end_time)
for i in range(len(points_)-1):
if points_[i].longitude and points_[i].latitude and \
points_[i+1].longitude and points_[i+1].latitude:
dis = get_distance(points_[i].longitude, points_[i].latitude,
points_[i+1].longitude, points_[i+1].latitude)
dis=Decimal(str(dis))
distance += dis
distance = '%0.1f' % (distance/1000,)
dis_sum = distance
for day in range(1,days+1):
start_time_, end_time_ = start_end_of_day(year=data.year, month=data.month, day=str(day))
if start_time_ > current_time:
break
last_point = self.db.get(last_cmd, tid, start_time_-60*60*24, start_time_,)
next_point = self.db.get(next_cmd, tid, end_time_, end_time_+60*60*24)
start_time_ = last_point['timestamp'] if last_point else start_time_
end_time_ = next_point['timestamp'] if next_point else end_time_
re = {}
re['name'] = str(day)
distance = Decimal()
points = self.db.query(sql_cmd, tid, start_time_, end_time_)
for i in range(len(points)-1):
if points[i].longitude and points[i].latitude and \
points[i+1].longitude and points[i+1].latitude:
dis = get_distance(points[i].longitude, points[i].latitude,
points[i+1].longitude, points[i+1].latitude)
distance += Decimal(str(dis))
# meter --> km
distance = '%0.1f' % (distance/1000,)
if float(distance) == 0:
distance = 0
graphics.append(float(distance))
re['mileage'] = distance
res.append(re)
counts = [float(dis_sum),]
else:
logging.error("[UWEB] Error statistics type: %s", statistics_type)
if page_count == -1:
items_count = len(res)
d, m = divmod(items_count, page_size)
page_count = (d + 1) if m else d
# store resutl in redis
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(mem_key, (res, counts, label, statistics_type), time=UWEB.STATISTIC_INTERVAL)
res= res[page_number*page_size:(page_number+1)*page_size]
self.write_ret(status,
dict_=dict(res=res,
counts=counts,
graphics=graphics,
pagecnt=page_count,
hash_=hash_))
except Exception as e:
logging.exception("[UWEB] uid:%s, tid:%s statistic failed. Exception: %s",
self.current_user.uid, self.current_user.tid, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)
def post(self):
"""Provie some statistics for one terminals.
"""
status = ErrorCode.SUCCESS
try:
data = DotDict(json_decode(self.request.body))
logging.info("[UWEB] event single statistic request: %s, uid: %s, tid: %s",
data, self.current_user.uid, self.current_user.tid)
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
self.write_ret(status)
return
try:
page_size = UWEB.LIMIT.PAGE_SIZE_STATISTICS
page_number = int(data.pagenum)
page_count = int(data.pagecnt)
tid = data.tid
res = []
graphics = []
counts_dct= {}
counts = []
label = u''
sql_cmd = ("SELECT COUNT(*) as count FROM V_EVENT"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s) AND category != 5 ")
sql_cmd_category = sql_cmd + " AND category =%s"
current_time = int(time.time())
CATEGORY_DCT = DotDict(powerlow=EVENTER.CATEGORY.POWERLOW,
illegashake=EVENTER.CATEGORY.ILLEGALSHAKE,
illegalmove=EVENTER.CATEGORY.ILLEGALMOVE,
#sos=EVENTER.CATEGORY.EMERGENCY,
heartbeat_lost=EVENTER.CATEGORY.HEARTBEAT_LOST)
CATEGORY_KEY = ['illegalmove','illegashake', 'heartbeat_lost', 'powerlow' ]
for key in CATEGORY_KEY:
counts_dct[key] = 0
statistics_type = data.statistics_type
if statistics_type == UWEB.STATISTICS_TYPE.YEAR:
label = data.year + u'年'
start_time, end_time = start_end_of_year(year=data.year)
for month in range(1,12+1):
start_time_, end_time_ = start_end_of_month(year=data.year, month=str(month))
if start_time_ > current_time:
break
re = {}
re['name'] = str(month)
events = {}
for key in CATEGORY_KEY:
res_item = {}
item = self.db.get(sql_cmd_category, tid, start_time_, end_time_, CATEGORY_DCT[key])
res_item[key] = item.count
events.update(res_item)
#event = self.db.get(sql_cmd, tid, start_time_, end_time_)
#graphics.append(event.count)
re['events'] = events
res.append(re)
#for category in CATEGORY_DCT.itervalues():
# event_count = self.db.get(sql_cmd_category, tid, start_time, end_time, category)
# counts.append(event_count.count)
for r in res:
graphics.append(sum(r['events'].itervalues()))
event = r['events']
for key in CATEGORY_KEY:
counts_dct[key] += event[key]
for key in CATEGORY_KEY:
counts.append(counts_dct[key])
elif statistics_type == UWEB.STATISTICS_TYPE.MONTH:
label = data.year + u'年' + data.month + u'月'
start_time, end_time = start_end_of_month(year=data.year, month=data.month)
days = days_of_month(year=data.year, month=data.month)
for day in range(1,days+1):
start_time_, end_time_ = start_end_of_day(year=data.year, month=data.month, day=str(day))
if start_time_ > current_time:
break
re = {}
re['name'] = str(day)
events = {}
for key in CATEGORY_KEY:
res_item = {}
item = self.db.get(sql_cmd_category, tid, start_time_, end_time_, CATEGORY_DCT[key])
res_item[key] = item.count
events.update(res_item)
re['events'] = events
res.append(re)
for r in res:
graphics.append(sum(r['events'].itervalues()))
event = r['events']
for key in CATEGORY_KEY:
counts_dct[key] += event[key]
for key in CATEGORY_KEY:
counts.append(counts_dct[key])
else:
logging.error("[UWEB] Error statistics type: %s", statistics_type)
if page_count == -1:
items_count = len(res)
d, m = divmod(items_count, page_size)
page_count = (d + 1) if m else d
# store resutl in redis
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(mem_key, (res, counts, label), time=UWEB.STATISTIC_INTERVAL)
res= res[page_number*page_size:(page_number+1)*page_size]
self.write_ret(status,
dict_=dict(res=res,
counts=counts,
graphics=graphics,
pagecnt=page_count,
hash_=hash_))
except Exception as e:
logging.exception("[UWEB] event statistic, uid:%s, tid:%s failed. Exception: %s",
self.current_user.uid, self.current_user.tid, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)
def post(self):
"""Provide statistics about terminal.
"""
status = ErrorCode.SUCCESS
try:
data = DotDict(json_decode(self.request.body))
logging.info("[UWEB] Single statistic request: %s, uid: %s, tid: %s",
data, self.current_user.uid, self.current_user.tid)
except Exception as e:
status = ErrorCode.ILLEGAL_DATA_FORMAT
self.write_ret(status)
return
try:
page_size = UWEB.LIMIT.PAGE_SIZE_STATISTICS
page_number = int(data.pagenum)
page_count = int(data.pagecnt)
tid = data.tid
res = []
graphics = []
counts = []
label = u''
dis_sum = Decimal()
current_time = int(time.time())
statistics_type = data.statistics_type
if statistics_type == UWEB.STATISTICS_TYPE.YEAR:
label = data.year + u'年'
start_time, end_time = start_end_of_year(year=data.year)
for month in range(1, 12 + 1):
start_time_, end_time_ = start_end_of_month(
year=data.year, month=str(month))
if start_time_ > current_time:
break
re = {}
re['name'] = str(month)
distance = Decimal()
mileage_log = self.db.get("SELECT SUM(distance) AS distance "
" FROM T_MILEAGE_LOG"
" WHERE tid = %s"
" AND (timestamp BETWEEN %s AND %s)",
tid, start_time_, end_time_)
if mileage_log and mileage_log['distance']:
distance = '%0.1f' % (
Decimal(mileage_log['distance']) / 1000,)
else:
distance = 0
graphics.append(float(distance))
dis_sum += Decimal(distance)
re['mileage'] = float(distance)
res.append(re)
counts = [float(dis_sum), ]
elif statistics_type == UWEB.STATISTICS_TYPE.MONTH:
label = data.year + u'年' + data.month + u'月'
start_time, end_time = start_end_of_month(
year=data.year, month=data.month)
days = days_of_month(year=data.year, month=data.month)
distance = Decimal()
for day in range(1, days + 1):
start_time_, end_time_ = start_end_of_day(
year=data.year, month=data.month, day=str(day))
if start_time_ > current_time:
break
re = {}
re['name'] = str(day)
distance = Decimal()
# NOTE:TODO get mileage
mileage_log = self.db.get("SELECT distance FROM T_MILEAGE_LOG"
" WHERE tid = %s"
" AND timestamp = %s",
tid, end_time_)
distance = mileage_log['distance'] if mileage_log else 0
# meter --> km
distance = '%0.1f' % (Decimal(distance) / 1000,)
graphics.append(float(distance))
dis_sum += Decimal(distance)
re['mileage'] = float(distance)
res.append(re)
counts = [float(dis_sum), ]
else:
logging.error(
"[UWEB] Error statistics type: %s", statistics_type)
if page_count == -1:
items_count = len(res)
d, m = divmod(items_count, page_size)
page_count = (d + 1) if m else d
# store resutl in redis
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
self.redis.setvalue(
mem_key, (res, counts, label, statistics_type), time=UWEB.STATISTIC_INTERVAL)
res = res[page_number * page_size:(page_number + 1) * page_size]
self.write_ret(status,
dict_=dict(res=res,
counts=counts,
graphics=graphics,
pagecnt=page_count,
hash_=hash_))
except Exception as e:
logging.exception("[UWEB] Statistic single failed. uid:%s, tid:%s, Exception: %s",
self.current_user.uid, self.current_user.tid, e.args)
status = ErrorCode.SERVER_BUSY
self.write_ret(status)