__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磷酸铁锂电芯
elif df_sn.loc[i, 'imei'][5:9] == 'L264':
celltype=102 #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))