__author__ = 'lmstack' #coding=utf-8 import os import datetime import pandas as pd from LIB.BACKEND import DBManager from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker import datetime import traceback from LIB.MIDDLE.CellStateEstimation.BatSafetyWarning.V1_0_1 import CBMSSafetyWarning from LIB.MIDDLE.CellStateEstimation.BatSafetyWarning.V1_0_1 import CBMSBatInterShort from LIB.MIDDLE.CellStateEstimation.BatSafetyWarning.V1_0_1 import CBMSBatUniform # from LIB.MIDDLE.CellStateEstimation.BatSafetyWarning.V1_0_1 import VoltStray from urllib import parse import pymysql import logging import logging.handlers import re from apscheduler.schedulers.blocking import BlockingScheduler #...................................电池包电芯安全诊断函数...................................................................................................................... def saftywarning_cal(): global df_sn global df_warning_ram global df_warning_ram1 global df_warning_ram2 global df_warning_ram3 global df_lfp_ram global df_lfp_ram1 global df_chrgvolt_ram # 读取结果数据库 host2='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port2=3306 db2='safety_platform' user2='qx_algo_rw' password2='qx@123456' db_safety_platform_engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( user2, parse.quote_plus(password2), host2, port2, db2 )) conn_platform = pymysql.connect(host=host2, port=port2, user=user2, password=password2, database=db2) cursor_platform = conn_platform.cursor() host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port=3306 db='qx_cas' user='qx_algo_rw' password='qx@123456' db_qxcas_engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( user, parse.quote_plus(password), host, port, db )) conn_cas = pymysql.connect(host=host, port=port, user=user, password=password, database=db) cursor_cas = conn_cas.cursor() df_fault_ram=pd.read_sql("select start_time, end_time, product_id, code, level, info, advice from all_fault_info where end_time='{}' and code='{}'".format('0000-00-00 00:00:00','C490'), db_safety_platform_engine) # result = result[['start_time', 'end_time', 'product_id', 'code', 'level', 'info', 'advice']] # df_fault_ram=result[(result['end_time']=='0000-00-00 00:00:00') & (result['code']=='C490')] now_time=datetime.datetime.now() start_time=now_time-datetime.timedelta(hours=6) start_time1=now_time-datetime.timedelta(days=7) start_time2=now_time-datetime.timedelta(days=3) start_time3=now_time-datetime.timedelta(days=1) start_time=start_time.strftime('%Y-%m-%d %H:%M:%S') start_time1=start_time1.strftime('%Y-%m-%d %H:%M:%S') start_time2=start_time2.strftime('%Y-%m-%d %H:%M:%S') start_time3=start_time3.strftime('%Y-%m-%d %H:%M:%S') end_time=now_time.strftime('%Y-%m-%d %H:%M:%S') for i in range(0, len(df_sn)): factory = "骑享" try: sn = df_sn.loc[i, 'sn'] if df_sn.loc[i, 'imei'][5:9] == 'N640': celltype=1 #6040三元电芯 elif df_sn.loc[i, 'imei'][5:9] == 'N440': celltype=2 #4840三元电芯 elif df_sn.loc[i, 'imei'][5:9] == 'L660': celltype=99 # 6060锂电芯 elif df_sn.loc[i, 'imei'][3:5] == 'LX' and df_sn.loc[i, 'imei'][5:9] == 'N750': celltype=3 #力信 50ah三元电芯 elif df_sn.loc[i, 'imei'][3:5] == 'CL' and df_sn.loc[i, 'imei'][5:9] == 'N750': celltype=4 #CATL 50ah三元电芯 elif df_sn.loc[i, 'imei'][3:9] == 'CLL128': celltype=100 # 重卡 factory = "金茂换电" elif df_sn.loc[i, 'imei'][5:9] == 'L420': celltype=101 #20ah磷酸铁锂电芯 else: logger.info("pid-{} celltype-{} SN: {} SKIP!".format(os.getpid(), "未知", sn)) continue logger.info("pid-{} celltype-{} SN: {} START!".format(os.getpid(), celltype, sn)) # 读取soh数据 df_soh = pd.read_sql("select time_st,sn,soh,cellsoh from cellstateestimation_soh where sn = '{}' order by id desc limit 1".format(sn), db_qxcas_engine) df_uniform = pd.read_sql("select time,sn,cellsoc_diff,cellvolt_diff,cellmin_num,cellmax_num,cellvolt_rank from cellstateestimation_uniform_socvoltdiff where sn = '{}' order by id desc limit 1".format(sn), db_qxcas_engine) df_uniform.loc[0,'cellvolt_rank']=str([1]*20) # df_voltsigma=pd.DataFrame() #读取原始数据库数据................................................................................id........................................................................ dbManager = DBManager.DBManager() df_data = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time, data_groups=['bms']) df_bms = df_data['bms'] # print(df_bms) #电池诊断................................................................................................................................................................ if not df_bms.empty: #电池内短路计算................................................................................................................................................................ df_warning_ram_sn=df_warning_ram[df_warning_ram['sn']==sn] df_warning_ram_sn1=df_warning_ram1[df_warning_ram1['sn']==sn] df_warning_ram_sn2=df_warning_ram2[df_warning_ram2['sn']==sn] df_warning_ram_sn3=df_warning_ram3[df_warning_ram3['sn']==sn] df_chrgvolt_ram_sn=df_chrgvolt_ram[df_chrgvolt_ram['sn']==sn] df_warning_ram_sn.reset_index(inplace=True,drop=True) #重置索引 df_warning_ram_sn1.reset_index(inplace=True,drop=True) #重置索引 df_warning_ram_sn2.reset_index(inplace=True,drop=True) #重置索引 df_warning_ram_sn3.reset_index(inplace=True,drop=True) #重置索引 df_chrgvolt_ram_sn.reset_index(inplace=True,drop=True) if not df_lfp_ram.empty: df_lfp_ram_sn=df_lfp_ram[df_lfp_ram['sn']==sn] df_lfp_ram_sn.reset_index(inplace=True,drop=True) #重置索引 else: df_lfp_ram_sn=pd.DataFrame() df_lfp_ram=pd.DataFrame(columns=df_bms.columns.tolist()+['sn']) if celltype>50 and (not df_lfp_ram1.empty): df_lfp_ram_sn1=df_lfp_ram1[df_lfp_ram1['sn']==sn] df_lfp_ram_sn1.reset_index(inplace=True,drop=True) #重置索引 else: df_lfp_ram_sn1=pd.DataFrame() df_lfp_ram1=pd.DataFrame(columns=df_bms.columns.tolist()+['sn']) #内短路计算.................................................................................................................................................. BatShort=CBMSBatInterShort.BatInterShort(sn,celltype,df_bms,df_soh,df_warning_ram_sn,df_warning_ram_sn1,df_warning_ram_sn2,df_warning_ram_sn3,df_lfp_ram_sn,df_chrgvolt_ram_sn) df_short_res, df_ram_res, df_ram_res1, df_ram_res2, df_ram_res3, df_ram_res4, df_chrgvolt_ram_sn=BatShort.intershort() if not df_short_res.empty: df_short_res.columns = ['time_st', 'time_sp', 'sn', 'method', 'short_current', 'baltime'] df_short_res.to_sql("cellStateEstimation_interShort",con=db_qxcas_engine, if_exists="append",index=False) #静置电压排名.................................................................................................................................................. BatUniform=CBMSBatUniform.BatUniform(sn,celltype,df_bms,df_uniform,df_ram_res3,df_lfp_ram_sn1) df_rank_res, df_ram_res3, df_ram_res5=BatUniform.batuniform() if not df_rank_res.empty: df_uniform=df_rank_res df_rank_res.to_sql("cellstateestimation_uniform_socvoltdiff",con=db_qxcas_engine, if_exists="append",index=False) #电压离群..................................................................................................................................................... # df_voltsigma=VoltStray.main(sn,df_bms,celltype) # if not df_voltsigma.empty: # df_voltsigma.to_sql("outlier_voltchangeratio",con=db_qxcas_engine, if_exists="append",index=False) #内短路ram处理......................................................... df_warning_ram=df_warning_ram.drop(df_warning_ram[df_warning_ram.sn==sn].index) df_warning_ram1=df_warning_ram1.drop(df_warning_ram1[df_warning_ram1.sn==sn].index) df_warning_ram2=df_warning_ram2.drop(df_warning_ram2[df_warning_ram2.sn==sn].index) df_warning_ram3=df_warning_ram3.drop(df_warning_ram3[df_warning_ram3.sn==sn].index) df_chrgvolt_ram=df_chrgvolt_ram.drop(df_chrgvolt_ram[df_chrgvolt_ram.sn==sn].index) df_warning_ram=pd.concat([df_warning_ram,df_ram_res],ignore_index=True) df_warning_ram1=pd.concat([df_warning_ram1,df_ram_res1],ignore_index=True) df_warning_ram2=pd.concat([df_warning_ram2,df_ram_res2],ignore_index=True) df_warning_ram3=pd.concat([df_warning_ram3,df_ram_res3],ignore_index=True) df_chrgvolt_ram=pd.concat([df_chrgvolt_ram,df_chrgvolt_ram_sn],ignore_index=True) df_lfp_ram=df_lfp_ram.drop(df_lfp_ram[df_lfp_ram.sn==sn].index) df_lfp_ram=pd.concat([df_lfp_ram,df_ram_res4],ignore_index=True) if celltype>50: df_lfp_ram1=df_lfp_ram1.drop(df_lfp_ram1[df_lfp_ram1.sn==sn].index) df_lfp_ram1=pd.concat([df_lfp_ram1,df_ram_res5],ignore_index=True) #电池热安全预警.............................................................................................................................................................. #读取内短路、析锂和一致性结果数据库数据 df_short = pd.read_sql("select time_st,time_sp,sn,method,short_current from cellstateestimation_intershort where sn = '{}' and time_sp between '{}' and '{}'".format(sn,start_time1,end_time), db_qxcas_engine) # df_liplated = pd.read_sql("select time,sn,liplated,liplated_amount from mechanism_liplated where sn = '{}' and time between '{}' and '{}'".format(sn,start_time2,end_time), db_qxcas_engine) df_uniform = pd.read_sql("select time,sn,cellsoc_diff,cellvolt_diff,cellmin_num,cellmax_num,cellvolt_rank from cellstateestimation_uniform_socvoltdiff where sn = '{}' and time between '{}' and '{}'".format(sn,start_time2,end_time), db_qxcas_engine) # df_voltsigma = pd.read_sql("select time,sn,VolOl_Uni,VolChng_Uni from outlier_voltchangeratio where sn = '{}' and time between '{}' and '{}'".format(sn,start_time3,end_time), db_qxcas_engine) #获取sn的故障RAM df_fault_ram_sn=df_fault_ram[df_fault_ram['product_id']==sn] df_fault_ram_sn.reset_index(inplace=True, drop=True) #热安全预警 BatWarning=CBMSSafetyWarning.SafetyWarning(sn,celltype,df_short,df_uniform,df_soh,df_fault_ram_sn) df_res_new, df_res_chg=BatWarning.diag() #新增内短路故障写入数据库 if not df_res_new.empty: df_res_new['add_time'] = datetime.datetime.now() df_res_new['factory'] = factory df_res_new.to_sql("all_fault_info",con=db_safety_platform_engine, if_exists="append",index=False) conn_platform.commit() #内短路结束故障更新数据库 if not df_res_chg.empty: df_res_chg['Batpos']=1 cursor_platform.execute(''' update all_fault_info set update_time='{}',end_time='{}', Batpos={} where product_id='{}' and code='{}' and end_time='0000-00-00 00:00:00' '''.format(datetime.datetime.now(), df_res_chg.iloc[-1]['end_time'], df_res_chg.iloc[-1]['Batpos'],sn, 'C490')) conn_platform.commit() except Exception as e: logger.error(traceback.format_exc) logger.error(u"{} ：{},{} 任务运行错误\n".format(sn,start_time,end_time), exc_info=True) cursor_platform.close() conn_platform.close() cursor_cas.close() conn_cas.close() db_qxcas_engine.dispose() db_safety_platform_engine.dispose() logger.info("pid-{} Done!".format(os.getpid())) #...................................................主进程........................................................................................................... if __name__ == "__main__": # 日志 log_path = 'log/' if not os.path.exists(log_path): os.makedirs(log_path) logger = logging.getLogger("main") logger.setLevel(logging.DEBUG) # 根据日期滚动(每天产生1个文件) fh = logging.handlers.TimedRotatingFileHandler(filename='{}/main_info.log'.format(log_path), when="D", interval=1, backupCount=30, encoding="utf-8") formatter = logging.Formatter("%(asctime)s - %(name)s-%(levelname)s %(message)s") fh.suffix = "%Y-%m-%d_%H-%M-%S" fh.extMatch = re.compile(r"^\d{4}-\d{2}-\d{2}_\d{2}-\d{2}-\d{2}") fh.setFormatter(formatter) fh.setLevel(logging.INFO) logger.addHandler(fh) fh = logging.handlers.TimedRotatingFileHandler(filename='{}/main_error.log'.format(log_path), when="D", interval=1, backupCount=30, encoding="utf-8") formatter = logging.Formatter("%(asctime)s - %(name)s-%(levelname)s %(message)s") fh.suffix = "%Y-%m-%d_%H-%M-%S" fh.extMatch = re.compile(r"^\d{4}-\d{2}-\d{2}_\d{2}-\d{2}-\d{2}") fh.setFormatter(formatter) fh.setLevel(logging.ERROR) logger.addHandler(fh) logger.info("pid is {}".format(os.getpid())) # 时间设置 # now_time = datetime.datetime.now() # pre_time = now_time + dateutil.relativedelta.relativedelta(days=-1)# 前一日 # end_time=datetime.datetime.strftime(now_time,"%Y-%m-%d 00:00:00") # start_time=datetime.datetime.strftime(pre_time,"%Y-%m-%d 00: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 where status in (1,2,3)") 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) # 算法参数 host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com' port=3306 db='safety_platform' user='qx_read' password=parse.quote_plus('Qx@123456') tablename='all_fault_info' db_res_engine = create_engine( "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format( user, password, host, port, db )) #............................模块运行前，先读取数据库中所有结束时间为0的数据，需要从数据库中读取................ df_warning_ram=pd.DataFrame(columns=['sn','time','deltsoc','cellsoc']) df_warning_ram1=pd.DataFrame(columns=['sn','time1','deltsoc1']) df_warning_ram2=pd.DataFrame(columns=['sn','time2','deltAs2']) df_warning_ram3=pd.DataFrame(columns=['sn','time3','standingtime','standingtime1','standingtime2']) df_lfp_ram=pd.DataFrame() df_lfp_ram1=pd.DataFrame() df_chrgvolt_ram=pd.DataFrame(columns=['sn','time','deltvolt','packcrnt_avg','temp_avg']) # now_time='2021-10-15 00:00:00' # now_time=datetime.datetime.strptime(now_time,'%Y-%m-%d %H:%M:%S') saftywarning_cal() #定时任务....................................................................................................................................................................... scheduler = BlockingScheduler() scheduler.add_job(saftywarning_cal, 'interval', hours=6, id='saftywarning_cal') try: scheduler.start() except Exception as e: scheduler.shutdown() logger.error(str(e))