data_analyze_platform/LIB/FRONTEND/SignalMonitor/main.py

#coding=utf-8
import os
import datetime
import pandas as pd
from LIB.BACKEND import DBManager, Log
from LIB.MIDDLE import SignalMonitor
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import time, datetime
import traceback

dbManager = DBManager.DBManager()
if __name__ == "__main__":
    try:
        # 数据库配置
        host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
        port = 3306
        user = 'qx_cas'
        password = 'Qx@123456'
        database = 'qx_cas'

        db_engine = create_engine(
            "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
                user, password, host, port, database
            ))

        db_engine = create_engine("mysql+pymysql://qx_cas:Qx@123456@rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com/qx_cas?charset=utf8")
        DbSession = sessionmaker(bind=db_engine)
        
        # 日志配置
        log = Log.Mylog(log_name='signal_monitor', log_level = 'info')
        log.set_file_hl(file_name='info.log', log_level='info')
        log.set_file_hl(file_name='error.log', log_level='error')
        logger = log.get_logger()

        logger.info("pid is + {}".format(os.getpid))

        # 读取sn列表
        df_sn = pd.read_csv('sn_list.csv')
        df_sn = df_sn.reset_index(drop=True)
        df_sn['StartTime'] = pd.to_datetime(df_sn['StartTime'])
        df_sn['EndTime'] = pd.to_datetime(df_sn['EndTime'])
        df_sn['ExitTime'] = pd.to_datetime(df_sn['ExitTime'])
        signalMonitor = SignalMonitor.SignalMonitor()
        
        cal_period = 24    # 计算间隔，单位h
        for i in range(0, len(df_sn['sn'])):    # 遍历SN号
            sn = [df_sn.loc[i,'sn']]
            if not (sn[0][0:2] == 'PK' or sn[0][0:2] == 'MG' or sn[0][0:2] == 'UD'):
                continue
            st = df_sn.loc[i, 'StartTime']
            if df_sn.loc[i, 'Service'] == 0:
                et = df_sn.loc[i, 'ExitTime']
            elif pd.isnull(df_sn.loc[i, 'EndTime']):
                otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
                et = pd.to_datetime(otherStyleTime)
            else:
                et = df_sn.loc[i, 'EndTime']
            df_last_state = pd.DataFrame(
                columns=['sn', 'current', 'Timestamp', 'PackState', 'LineState'])    # 每日最后BMS数据
            df_last_state_gps = pd.DataFrame(
                columns=['sn', 'Timestamp', 'PackState', 'LineState'])    # 每日最后GPS数据
            while st < et:
                df_res = pd.DataFrame(columns=[
                                    'sn', 'PackState', 'LineState', 'StartTime', 'EndTime', 'OfflineTime'])    # 初始化BMS信号统计数据
                df_res_gps = pd.DataFrame(columns=[
                                        'sn', 'PackState', 'LineState', 'StartTime', 'EndTime', 'OfflineTime'])    # 初始化GPS信号统计数据
                df_res, df_state, df_last_state = signalMonitor.get_bms_offline_stat(
                    sn, st, et, df_res, df_last_state, cal_period)    # 计算每日BMS信号统计数据
                df_res_gps, df_last_state_gps = signalMonitor.get_gps_offline_stat(
                    sn, st, et, df_state, df_res_gps, df_last_state_gps, cal_period)    # 计算每日GPS信号统计数据
            
                # 数据入库
                df_tosql = df_res_gps.copy()
                df_tosql.columns = ['sn', 'pack_state', 'line_state', 'start_time', 'end_time', 'offline_time']
                df_tosql.loc[df_tosql.index[-1:]].to_sql("gps_signal_monitor",con=db_engine, if_exists="append",index=False)

                df_tosql = df_res.copy()
                df_tosql.columns = ['sn', 'pack_state', 'line_state', 'start_time', 'end_time', 'offline_time']
                df_tosql.loc[df_res.index[-1:]].to_sql("bms_signal_monitor",con=db_engine, if_exists="append",index=False)
                st = st + datetime.timedelta(hours=cal_period)

            # 数据入库
            df_tosql = df_last_state.copy()
            df_tosql.columns = ['sn', 'current', 'time_stamp', 'pack_state', 'line_state']
            df_tosql.to_sql("bms_last_data_day",con=db_engine, if_exists="append",index=False)

            df_tosql = df_last_state.copy()
            df_tosql.columns = ['sn', 'time_stamp', 'pack_state', 'line_state']
            df_tosql.to_sql("gps_last_data_day",con=db_engine, if_exists="append",index=False)

            logger.info("{} {} Success!".format(sn, str(st)))
    except Exception as e:
        logger.error(traceback.format_exc)
        logger.error(u"任务运行错误", exc_info=True)