Beispiel #1
0
    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.")
Beispiel #2
0
    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)
Beispiel #3
0
    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)
Beispiel #4
0
    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)
Beispiel #5
0
    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)