__author__ = 'lmstack' #coding=utf-8 import os import datetime import pandas as pd from LIB.BACKEND import DBManager, Log from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker import time, datetime import dateutil.relativedelta import traceback from LIB.MIDDLE.CellStateEstimation.Common import log from LIB.MIDDLE.SaftyCenter.Common import FeiShuData from LIB.MIDDLE.SaftyCenter.Common import DBDownload import time, datetime from pandas.core.frame import DataFrame from apscheduler.schedulers.blocking import BlockingScheduler from LIB.MIDDLE.SaftyCenter.DataSta.DataStatistics import DataSta from urllib import parse import pymysql from LIB.BACKEND import DBManager #............................主程序................................... def Week_Task(): all_period_fault_info=DataFrame(columns=['factory','week','level1_count','level2_count','level3_count','level4_count','level5_count','solve_rate']) #............................获取数据................................ toweek='Week'+time.strftime('%W') CS_Data=FeiShuData.getFeiShuDATA() host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port=3306 db='safety_platform' user='qx_read' password='Qx@123456' mode=2 tablename1='all_fault_info' db_engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( user, parse.quote_plus(password), host, port, db )) DBRead=DBDownload.DBDownload(host, port, db, user, password,mode) with DBRead as DBRead: df_fltinfo=DBRead.getdata('product_id','level','code','start_time',tablename=tablename1,factory='骑享',sn='',timename='',st='',sp='')#dbdownload经过了改编 #............................获取时间................................ end_time=datetime.datetime.now() # end_time=datetime.datetime.strptime(end_time,'%Y-%m-%d') start_time=end_time-datetime.timedelta(days=7) start_time=start_time.strftime('%Y-%m-%d') end_time=end_time.strftime('%Y-%m-%d') FltAlarmInfo,Celltype=DataSta.SaftyWarningSta(CS_Data,df_fltinfo,start_time,end_time) FaultLvlCount=DataSta.WeekInfoSta(df_fltinfo,start_time,end_time) lvl1=FaultLvlCount[FaultLvlCount['level']==1]['product_id'].values lvl2=FaultLvlCount[FaultLvlCount['level']==2]['product_id'].values lvl3=FaultLvlCount[FaultLvlCount['level']==3]['product_id'].values lvl4=FaultLvlCount[FaultLvlCount['level']==4]['product_id'].values lvl5=FaultLvlCount[FaultLvlCount['level']==5]['product_id'].values all_period_fault_info.loc[0,'factory']='骑享' all_period_fault_info.loc[0,'week']=toweek all_period_fault_info.loc[0,'level1_count']=lvl1 all_period_fault_info.loc[0,'level2_count']=lvl2 all_period_fault_info.loc[0,'level3_count']=lvl3 all_period_fault_info.loc[0,'level4_count']=lvl4 all_period_fault_info.loc[0,'level5_count']=lvl5 all_period_fault_info.loc[0,'solve_rate']=FltAlarmInfo.loc[0,'OprationManageRate'] all_period_fault_info.to_sql('all_period_fault_info', db_engine, if_exists='append', index=False) print(all_period_fault_info) def Minutes_Task(): #............................获取数据................................ host='172.16.121.236' port=3306 db='fastfun' user='readonly' password='Fast1234' mode=3 tablename1='ff_battery_accum' DBRead=DBDownload.DBDownload(host, port, db, user, password,mode) with DBRead as DBRead: df_last_accum=DBRead.getdata('devcode','dsg_phaccum','dsg_ahaccum',tablename=tablename1,factory='',sn='',timename='',st='',sp='')#dbdownload经过了改编 host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port=3306 db='qx_cas' user='qx_read' password='Qx@123456' mode=3 tablename2='bat_first_data_time' DBRead=DBDownload.DBDownload(host, port, db, user, password,mode) with DBRead as DBRead: df_FirstDataTime=DBRead.getdata('sn','first_data_time',tablename=tablename2,factory='',sn='',timename='',st='',sp='')#dbdownload经过了改编 CS_Data=FeiShuData.getFeiShuDATA() host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port=3306 db='safety_platform' user='qx_read' password='Qx@123456' mode=2 tablename1='all_fault_info' db_engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( user, parse.quote_plus(password), host, port, db )) DBRead=DBDownload.DBDownload(host, port, db, user, password,mode) with DBRead as DBRead: df_fltinfo=DBRead.getdata('product_id','level','code','start_time',tablename=tablename1,factory='骑享',sn='',timename='',st='',sp='')#dbdownload经过了改编 #............................获取时间................................ end_time=datetime.datetime.now() # end_time=datetime.datetime.strptime(end_time,'%Y-%m-%d') start_time=end_time-datetime.timedelta(days=1) start_time=start_time.strftime('%Y-%m-%d') end_time=end_time.strftime('%Y-%m-%d') #............................执行程序................................ FltAlarmInfo,Celltype=DataSta.SaftyWarningSta(CS_Data,df_fltinfo,start_time,end_time) SatftyCount=DataSta.SftyWrngClsfy(df_fltinfo) MaxAccumAh,TotalAccumAh,MaxCycle,MaxRunningHour,TotalRunHour=DataSta.AccumInfo(df_last_accum,df_FirstDataTime,end_time) all_statistic_info=DataFrame(columns=['factory','total_alarm','alarm_total_today','alarm_not_close_today','alarm_close_today','alarm_uregent_total_today','alarm_uregent_close_today','alarm_uregent_not_close_today','alarm_close_total','run_time_total','dischrg_total','odo_total','max_dischrg_one','max_runtime_one','max_cycle_one','max_odo_one','alarm_close_total','alarm_total','cell_type','cell_type_count','cell_safety_risk_count','data_safety_risk_count','status_safety_risk_count','hv_safety_risk_count','system_safety_risk_count','sample_safety_risk_count','controller_safety_risk_count','design_safety_risk_count']) all_statistic_info.loc[0,'factory']='骑享' all_statistic_info.loc[0,'total_alarm']=FltAlarmInfo.loc[0,'SftyPlt_Data_Total'] all_statistic_info.loc[0,'alarm_total_today']=FltAlarmInfo.loc[0,'SftyPlt_Data_day'] all_statistic_info.loc[0,'alarm_close_today']=FltAlarmInfo.loc[0,'CS_Warning_day_Finish_Count'] all_statistic_info.loc[0,'alarm_not_close_today']=FltAlarmInfo.loc[0,'SftyPlt_Data_day']-FltAlarmInfo.loc[0,'CS_Warning_day_Finish_Count'] all_statistic_info.loc[0,'alarm_uregent_total_today']=FltAlarmInfo.loc[0,'SftyPlt_EmgcyData_day'] all_statistic_info.loc[0,'alarm_uregent_close_today']=FltAlarmInfo.loc[0,'SftyPlt_EmgcyData_day_Finish_Count'] all_statistic_info.loc[0,'alarm_uregent_not_close_today']=FltAlarmInfo.loc[0,'SftyPlt_EmgcyData_day']-FltAlarmInfo.loc[0,'SftyPlt_EmgcyData_day_Finish_Count'] all_statistic_info.loc[0,'run_time_total']=TotalRunHour all_statistic_info.loc[0,'dischrg_total']=TotalAccumAh all_statistic_info.loc[0,'odo_total']=0 all_statistic_info.loc[0,'max_dischrg_one']=MaxAccumAh all_statistic_info.loc[0,'max_runtime_one']=MaxRunningHour all_statistic_info.loc[0,'max_cycle_one']=MaxCycle all_statistic_info.loc[0,'max_odo_one']=0 all_statistic_info.loc[0,'alarm_close_total']=FltAlarmInfo.loc[0,'CS_Warning_Total_Finish_Count'] all_statistic_info.loc[0,'alarm_total']=FltAlarmInfo.loc[0,'SftyPlt_Data_Total'] CellType=Celltype.columns.tolist() CellType=','.join(CellType) all_statistic_info.loc[0,'cell_type']=str(CellType) CellTypeCount=str(Celltype.loc[0].values) CellTypeCount=''.join(CellTypeCount) all_statistic_info.loc[0,'cell_type_count']=str(CellTypeCount) all_statistic_info.loc[0,'cell_safety_risk_count']=SatftyCount.loc[0,'CellSaftyCount'] all_statistic_info.loc[0,'data_safety_risk_count']=SatftyCount.loc[0,'DataSaftyCodeCount'] all_statistic_info.loc[0,'status_safety_risk_count']=SatftyCount.loc[0,'StateSaftyCodeCount'] all_statistic_info.loc[0,'hv_safety_risk_count']=SatftyCount.loc[0,'HvSaftyCodeCount'] all_statistic_info.loc[0,'system_safety_risk_count']=SatftyCount.loc[0,'SysSaftyCodeCount'] all_statistic_info.loc[0,'sample_safety_risk_count']=SatftyCount.loc[0,'SamplingSatyCount'] all_statistic_info.loc[0,'controller_safety_risk_count']=SatftyCount.loc[0,'CtrlSaftyCodeCount'] all_statistic_info.loc[0,'design_safety_risk_count']=SatftyCount.loc[0,'DsnSaftyCodeCount'] all_statistic_info.to_sql('all_statistic_info', db_engine, if_exists='append', index=False) print(all_statistic_info) if __name__ == "__main__": # # 时间设置 # now_time = datetime.datetime.now() # pre_time = now_time + dateutil.relativedelta.relativedelta(hours=-1)#上小时时间 # end_time=datetime.datetime.strftime(now_time,"%Y-%m-%d %H:00:00") # start_time=datetime.datetime.strftime(pre_time,"%Y-%m-%d %H:00:00") # history_run_flag = False # 历史数据运行标志 # # 更新sn列表 # host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com' # port=3306 # db='qixiang_oss' # user='qixiang_oss' # password='Qixiang2021' # conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db) # cursor = conn.cursor() # cursor.execute("select sn, imei, add_time from app_device") # res = cursor.fetchall() # df_sn = pd.DataFrame(res, columns=['sn', 'imei', 'add_time']) # df_sn = df_sn.reset_index(drop=True) # conn.close(); # # 数据库配置 # host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' # port = 3306 # user = 'qx_cas' # password = parse.quote_plus('Qx@123456') # database = 'qx_cas' # db_engine = create_engine( # "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( # user, password, host, port, database # )) # DbSession = sessionmaker(bind=db_engine) # # 运行历史数据配置 # df_first_data_time = pd.read_sql("select * from bat_first_data_time", db_engine) # 日志配置 now_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()).replace(":","_") log_path = 'log/' + now_str if not os.path.exists(log_path): os.makedirs(log_path) log = Log.Mylog(log_name='saftyCenter_DataSta', log_level = 'info') log.set_file_hl(file_name='{}/info.log'.format(log_path), log_level='info', size=1024* 1024 * 100) log.set_file_hl(file_name='{}/error.log'.format(log_path), log_level='error', size=1024* 1024 * 100) logger = log.get_logger() logger.info("pid is {}".format(os.getpid())) # # 算法参数 # host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' # port=3306 # db='safety_platform' # user='qx_read' # password=parse.quote_plus('Qx@123456') # db_res_engine = create_engine( # "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( # user, password, host, port, db # )) # dbManager = DBManager.DBManager() # charge_count = 0 # drive_count = 0 # stand_count = 0 # for sn in df_sn['sn'].tolist(): Minutes_Task() scheduler = BlockingScheduler() scheduler.add_job(Week_Task, 'interval', seconds=10, id='Week_Task') scheduler.add_job(Minutes_Task, 'interval', seconds=10, id='Hour_Task') try: logger.info("周期任务 START!") scheduler.start() # df_data = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time, data_groups=['bms', 'gps']) # df_bms = df_data['bms'] # df_gps = df_data['gps'] # charge_count, drive_count, stand_count = status_sta.status_sta(df_bms, df_gps, start_time, charge_count, drive_count, stand_count) # df_result = pd.DataFrame({'factory':['骑享'], 'time':[start_time], 'charge_count':[charge_count], 'drive_count':[drive_count], 'stand_count':stand_count}) # logger.info("pid-{} SN: {} DONE!".format(os.getpid(), sn)) except: logger.info("周期任务 ERROR!") scheduler.shutdown() logger.error(traceback.format_exc)