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()