def get(self, hash_):
"""Download the records and save it as excel.
"""
mem_key = self.get_memcache_key(hash_)
r = self.redis.getvalue(mem_key)
if r:
results, interval = r[0], r[1]
else:
self.render("errors/download.html")
return
wb = xlwt.Workbook()
ws = wb.add_sheet(OFFLINE_SHEET)
start_line = 0
for i, head in enumerate(OFFLINE_HEADER):
ws.write(0, i, head)
ws.col(1).width = 4000 * 4
ws.col(2).width = 4000
ws.col(3).width = 4000
ws.col(4).width = 4000
ws.col(6).width = 4000 * 2
ws.col(7).width = 4000 * 2
ws.col(8).width = 4000 * 2
ws.col(10).width = 4000 * 4
start_line += 1
for i, result in zip(range(start_line, len(results) + start_line), results):
ws.write(i, 0, u'个人用户' if result['user_type'] == UWEB.USER_TYPE.PERSON else u'集团用户')
ws.write(i, 1, result['corp_name'])
ws.write(i, 2, result['umobile'])
ws.write(i, 3, result['tmobile'])
ws.write(i, 4, result['tid'])
ws.write(i, 5, result['softversion'])
ws.write(i, 6, result['alias'])
ws.write(i, 7, str(result['pbat'])+'%')
ws.write(i, 8, time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(result['offline_time'])))
ws.write(i, 9, time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(result['login_time'])))
ws.write(i, 10, seconds_to_label(result['offline_period']))
ws.write(i, 11, 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.write(i, 12, safe_unicode(terminal_offline['remark']))
_tmp_file = StringIO()
wb.save(_tmp_file)
filename = self.generate_file_name(OFFLINE_FILE_NAME)
self.set_header('Content-Type', 'application/force-download')
self.set_header('Content-Disposition', 'attachment; filename=%s.xls' % (filename,))
# move the the begging.
_tmp_file.seek(0, SEEK_SET)
self.write(_tmp_file.read())
_tmp_file.close()
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 _on_finish(db):
self.db = db
if cellid_flag == 1:
# gps track and cellid track
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",
self.current_user.tid, start_time, end_time)
else:
# cellid_flag is None or 0, only gps track
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)"
" AND type = 0"
" GROUP BY timestamp"
" ORDER BY timestamp",
self.current_user.tid, start_time, end_time)
# check track point count
if track and len(track) > 500 and network_type == 0:
logging.info("[UWEB] The %s track points length is: %s, "
" and the newtork type is too low, so return error.",
tid, len(track))
self.write_ret(ErrorCode.TRACK_POINTS_TOO_MUCH)
self.finish()
return
# NOTE: if latlons are legal, but clatlons are illlegal, offset
# them and update them in db.
_start_time = time.time()
track = get_locations_with_clatlon(track, self.db)
_now_time = time.time()
if _now_time - _start_time > 3: # 3 seconds
logging.info("[UWEB] Track offset used time: %s s, tid: %s, cid: %s",
_now_time - _start_time, self.current_user.tid, self.current_user.cid)
# NOTE: filter point without valid clat and clon
_track = []
for t in track:
if t['clongitude'] and ['clatitude']:
_track.append(t)
else:
logging.info("[UWEB] Invalid point: %s, drop it, cid: %s",
t, self.current_user.cid)
track = _track
# add idle_points
# track1, track2, track3,...
# when the distance between two points is larger than 10 meters and the interval is less than 5 minutes,
# they are regarded as idle_points
idle_lst = []
idle_points = []
for i, item_start in enumerate(track):
is_idle = False
if i in idle_lst:
continue
for j in range(i + 1, len(track)):
item_end = track[j]
distance = get_distance(
item_start.clongitude, item_start.clatitude, item_end.clongitude, item_end.clatitude)
if distance >= UWEB.IDLE_DISTANCE:
break
else:
idle_time = item_end[
'timestamp'] - item_start['timestamp']
item_start['idle_time'] = idle_time
item_start['start_time'] = item_start['timestamp']
item_start['end_time'] = item_end['timestamp']
idle_lst.append(j)
is_idle = True
if is_idle and item_start['idle_time'] > UWEB.IDLE_INTERVAL:
idle_points.append(item_start)
# modify name & degere
for item in track:
item['degree'] = float(item['degree'])
if item.name is None:
item['name'] = ''
# organize and store the data to be downloaded
m = hashlib.md5()
m.update(self.request.body)
hash_ = m.hexdigest()
mem_key = self.KEY_TEMPLATE % (self.current_user.uid, hash_)
res = []
if idle_lst:
point_begin = dict(label=u'起点',
start_time=utc_to_date(
track[0]['timestamp']),
end_time='',
name=track[0]['name'])
point_idle = []
for idle_point in idle_points:
idle_label = seconds_to_label(idle_point['idle_time'])
label = u'停留' + idle_label
point = dict(label=label,
start_time=utc_to_date(
idle_point['start_time']),
end_time=utc_to_date(idle_point['end_time']),
name=idle_point['name'])
point_idle.append(point)
point_end = dict(label=u'终点',
start_time=utc_to_date(
track[-1]['timestamp']),
end_time='',
name=track[-1]['name'])
res.append(point_begin)
res.append(point_end)
res.extend(point_idle)
self.redis.setvalue(mem_key, res, time=UWEB.STATISTIC_INTERVAL)
logging.info(
"[UEB] Tid:%s track query, returned %s points.", self.current_user.tid, len(track))
self.write_ret(status,
dict_=DotDict(track=track,
idle_points=idle_points,
hash_=hash_))
self.finish()