main.py 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227
  1. import pandas as pd
  2. import pymysql
  3. from LIB.BACKEND import DBManager, Log
  4. from apscheduler.schedulers.blocking import BlockingScheduler
  5. import datetime
  6. from LIB.MIDDLE.CellStateEstimation.Common.V1_0_1 import DBDownload
  7. from LIB.MIDDLE.CellStateEstimation.Common.V1_0_1 import log
  8. import CBMSBatInterShort
  9. import CBMSBatUniform
  10. import VoltStray
  11. import CBMSSafetyWarning
  12. #电池热安全预警核心算法函数......................................................................................................................
  13. def saftywarning_cal():
  14. global SNnums
  15. global df_warning_ram
  16. global df_warning_ram1
  17. global df_warning_ram2
  18. global df_warning_ram3
  19. global df_lfp_ram
  20. global df_lfp_ram1
  21. pd.set_option('display.width', 300) # 设置字符显示宽度
  22. pd.set_option('display.max_rows', None) # 设置显示最大行
  23. pd.set_option('display.max_columns', None) # 设置显示最大列,None为显示所有列
  24. # start=time.time()
  25. now_time=datetime.datetime.now()
  26. start_time=now_time-datetime.timedelta(hours=6)
  27. start_time1=now_time-datetime.timedelta(days=7)
  28. start_time2=now_time-datetime.timedelta(days=3)
  29. start_time3=now_time-datetime.timedelta(days=1)
  30. start_time=start_time.strftime('%Y-%m-%d %H:%M:%S')
  31. start_time1=start_time1.strftime('%Y-%m-%d %H:%M:%S')
  32. start_time2=start_time2.strftime('%Y-%m-%d %H:%M:%S')
  33. start_time3=start_time3.strftime('%Y-%m-%d %H:%M:%S')
  34. end_time=now_time.strftime('%Y-%m-%d %H:%M:%S')
  35. #数据库配置
  36. host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  37. port=3306
  38. user='qx_read'
  39. password='Qx@123456'
  40. #读取故障结果库中code==110且end_time='0000-00-00 00:00:00'...............................
  41. db='safety_platform'
  42. mysql = pymysql.connect (host=host, user=user, password=password, port=port, database=db)
  43. cursor = mysql.cursor()
  44. param='start_time,end_time,product_id,code,level,info,advice'
  45. tablename='all_fault_info'
  46. sql = "select %s from %s where code=110 and end_time='0000-00-00 00:00:00'" %(param,tablename)
  47. cursor.execute(sql)
  48. res = cursor.fetchall()
  49. df_fault_ram= pd.DataFrame(res,columns=param.split(','))
  50. cursor.close()
  51. mysql.close()
  52. for sn in SNnums:
  53. if 'PK500' in sn:
  54. celltype=1 #6040三元电芯
  55. elif 'PK502' in sn:
  56. celltype=2 #4840三元电芯
  57. elif 'K504B' in sn:
  58. celltype=99 #60ah林磷酸铁锂电芯
  59. elif 'MGMLXN750' in sn:
  60. celltype=3 #力信50ah三元电芯
  61. elif 'MGMCLN750' or 'UD' in sn:
  62. celltype=4 #CATL 50ah三元电芯
  63. else:
  64. print('SN:{},未找到对应电池类型!!!'.format(sn))
  65. continue
  66. # sys.exit()
  67. #读取原始数据库数据........................................................................................................................................................
  68. dbManager = DBManager.DBManager()
  69. df_data = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time, data_groups=['bms'])
  70. df_bms = df_data['bms']
  71. # print(df_bms)
  72. # df_bms.to_csv(r'D:\Platform\platform_python\data_analyze_platform\USER\spf\01qixiang\98Download\\'+sn+'_BMS_'+tim+'.csv',encoding='GB18030')
  73. #读取结果数据库数据........................................................................................................................................................
  74. db='qx_cas'
  75. mode=1
  76. DBRead=DBDownload.DBDownload(host, port, db, user, password,mode)
  77. with DBRead as DBRead:
  78. df_soh=DBRead.getdata('time_st,sn,soh,cellsoh', tablename='cellstateestimation_soh', sn=sn, timename='time_sp', st=start_time, sp=end_time)
  79. df_uniform=DBRead.getdata('time,sn,cellsoc_diff,cellvolt_diff,cellmin_num,cellmax_num,cellvolt_rank', tablename='cellstateestimation_uniform_socvoltdiff', sn=sn, timename='time', st=start_time1, sp=end_time)
  80. df_uniform.loc[0,'cellvolt_rank']=str([1]*20)
  81. df_voltsigma=pd.DataFrame()
  82. if not df_bms.empty:
  83. #ram处理...............................................................................................................
  84. df_warning_ram_sn=df_warning_ram[df_warning_ram['sn']==sn]
  85. df_warning_ram_sn1=df_warning_ram1[df_warning_ram1['sn']==sn]
  86. df_warning_ram_sn2=df_warning_ram2[df_warning_ram2['sn']==sn]
  87. df_warning_ram_sn3=df_warning_ram3[df_warning_ram3['sn']==sn]
  88. df_warning_ram_sn.reset_index(inplace=True,drop=True) #重置索引
  89. df_warning_ram_sn1.reset_index(inplace=True,drop=True) #重置索引
  90. df_warning_ram_sn2.reset_index(inplace=True,drop=True) #重置索引
  91. df_warning_ram_sn3.reset_index(inplace=True,drop=True) #重置索引
  92. if celltype>50 and (not df_lfp_ram.empty):
  93. df_lfp_ram_sn=df_lfp_ram[df_lfp_ram['sn']==sn]
  94. df_lfp_ram_sn.reset_index(inplace=True,drop=True) #重置索引
  95. else:
  96. df_lfp_ram_sn=pd.DataFrame()
  97. df_lfp_ram=pd.DataFrame(columns=df_bms.columns.tolist()+['sn'])
  98. if celltype>50 and (not df_lfp_ram1.empty):
  99. df_lfp_ram_sn1=df_lfp_ram1[df_lfp_ram1['sn']==sn]
  100. df_lfp_ram_sn1.reset_index(inplace=True,drop=True) #重置索引
  101. else:
  102. df_lfp_ram_sn1=pd.DataFrame()
  103. df_lfp_ram1=pd.DataFrame(columns=df_bms.columns.tolist()+['sn'])
  104. #内短路计算..................................................................................................................................................
  105. 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)
  106. df_short_res, df_ram_res, df_ram_res1, df_ram_res2, df_ram_res3, df_ram_res4=BatShort.intershort()
  107. if not df_short_res.empty:
  108. with open(r'D:\Platform\platform_python\data_analyze_platform\USER\spf\01qixiang\07BatSafetyWarning\内短路.txt','a') as file:
  109. file.write(str(df_short_res)+'\n')
  110. #静置电压排名..................................................................................................................................................
  111. BatUniform=CBMSBatUniform.BatUniform(sn,celltype,df_bms,df_uniform,df_ram_res3,df_lfp_ram_sn1)
  112. df_rank_res, df_ram_res3, df_ram_res5=BatUniform.batuniform()
  113. if not df_rank_res.empty:
  114. df_uniform=df_rank_res
  115. with open(r'D:\Platform\platform_python\data_analyze_platform\USER\spf\01qixiang\07BatSafetyWarning\电压排名.txt','a') as file:
  116. file.write(str(df_rank_res)+'\n')
  117. #电压离群.....................................................................................................................................................
  118. df_voltsigma=VoltStray.main(sn,df_bms,celltype)
  119. if not df_voltsigma.empty:
  120. with open(r'D:\Platform\platform_python\data_analyze_platform\USER\spf\01qixiang\07BatSafetyWarning\电压离群.txt','a') as file:
  121. file.write(str(df_voltsigma)+'\n')
  122. #ram处理................................................................................................................
  123. df_warning_ram=df_warning_ram.drop(df_warning_ram[df_warning_ram.sn==sn].index)
  124. df_warning_ram1=df_warning_ram1.drop(df_warning_ram1[df_warning_ram1.sn==sn].index)
  125. df_warning_ram2=df_warning_ram2.drop(df_warning_ram2[df_warning_ram2.sn==sn].index)
  126. df_warning_ram3=df_warning_ram3.drop(df_warning_ram3[df_warning_ram3.sn==sn].index)
  127. df_warning_ram=pd.concat([df_warning_ram,df_ram_res],ignore_index=True)
  128. df_warning_ram1=pd.concat([df_warning_ram1,df_ram_res1],ignore_index=True)
  129. df_warning_ram2=pd.concat([df_warning_ram2,df_ram_res2],ignore_index=True)
  130. df_warning_ram3=pd.concat([df_warning_ram3,df_ram_res3],ignore_index=True)
  131. if celltype>50:
  132. df_lfp_ram=df_lfp_ram.drop(df_lfp_ram[df_lfp_ram.sn==sn].index)
  133. df_lfp_ram=pd.concat([df_lfp_ram,df_ram_res4],ignore_index=True)
  134. df_lfp_ram1=df_lfp_ram1.drop(df_lfp_ram1[df_lfp_ram1.sn==sn].index)
  135. df_lfp_ram1=pd.concat([df_lfp_ram1,df_ram_res5],ignore_index=True)
  136. #电池热安全预警..............................................................................................................................................................
  137. #读取内短路、析锂和一致性结果数据库数据
  138. db='qx_cas'
  139. mode=2
  140. tablename1='cellstateestimation_intershort'
  141. tablename3='cellstateestimation_uniform_socvoltdiff'
  142. tablename4='outlier_voltchangeratio' #电压离群表单
  143. DBRead=DBDownload.DBDownload(host, port, db, user, password,mode)
  144. with DBRead as DBRead:
  145. df_short=DBRead.getdata('time_sp,sn,short_current', tablename=tablename1, sn=sn, timename='time_sp', st=start_time1, sp=end_time)
  146. df_uniform=DBRead.getdata('time,sn,cellsoc_diff,cellvolt_diff,cellmin_num,cellmax_num,cellvolt_rank', tablename=tablename3, sn=sn, timename='time', st=start_time2, sp=end_time)
  147. df_voltsigma=DBRead.getdata('time,sn,VolOl_Uni,VolChng_Uni', tablename=tablename4, sn=sn, timename='time', st=start_time3, sp=end_time)
  148. #获取sn的故障RAM
  149. df_fault_ram_sn=df_fault_ram[df_fault_ram['product_id']==sn]
  150. #热安全预警
  151. if df_fault_ram_sn.empty:
  152. BatWarning=CBMSSafetyWarning.SafetyWarning(sn,celltype,df_short,df_uniform,df_voltsigma,df_soh)
  153. df_warning_res=BatWarning.diag()
  154. #当前热失控故障写入数据库
  155. if not df_warning_res.empty:
  156. with open(r'D:\Platform\platform_python\data_analyze_platform\USER\spf\01qixiang\07BatSafetyWarning\热失控预警.txt','a',encoding="utf-8") as file:
  157. file.write(str(tuple(df_warning_res.iloc[-1]))+'\n')
  158. else:
  159. fault_time=datetime.datetime.strptime(df_fault_ram_sn.iloc[-1]['start_time'], '%Y-%m-%d %H:%M:%S')
  160. if (now_time-fault_time).total_seconds()>3*24*3600: #df_warning_end历史故障筛选并更改数据库故障结束时间
  161. df_fault_ram_sn['end_time']=end_time
  162. df_fault_ram_sn['Batpos']=1
  163. with open(r'D:\Platform\platform_python\data_analyze_platform\USER\spf\01qixiang\07BatSafetyWarning\热失控预警.txt','a',encoding="utf-8") as file:
  164. file.write(str(tuple(df_warning_res.iloc[-1]))+'\n')
  165. #...............................................主函数起定时作用.......................................................................................................................
  166. if __name__ == "__main__":
  167. excelpath=r'D:\Platform\platform_python\data_analyze_platform\USER\spf\01qixiang\sn-20210903.xlsx'
  168. SNdata_6060 = pd.read_excel(excelpath, sheet_name='科易6060')
  169. SNdata_6040 = pd.read_excel(excelpath, sheet_name='科易6040')
  170. SNdata_4840 = pd.read_excel(excelpath, sheet_name='科易4840')
  171. SNdata_L7255 = pd.read_excel(excelpath, sheet_name='格林美-力信7255')
  172. SNdata_C7255 = pd.read_excel(excelpath, sheet_name='格林美-CATL7255')
  173. SNdata_U7255 = pd.read_excel(excelpath, sheet_name='优旦7255')
  174. SNnums_6060=SNdata_6060['SN号'].tolist()
  175. SNnums_6040=SNdata_6040['SN号'].tolist()
  176. SNnums_4840=SNdata_4840['SN号'].tolist()
  177. SNnums_L7255=SNdata_L7255['SN号'].tolist()
  178. SNnums_C7255=SNdata_C7255['SN号'].tolist()
  179. SNnums_U7255=SNdata_U7255['SN号'].tolist()
  180. SNnums=SNnums_L7255 + SNnums_C7255 + SNnums_U7255 + SNnums_6040 + SNnums_4840 + SNnums_6060
  181. SNnums=['MGMCLN750N215I091']
  182. mylog=log.Mylog('log_warning.txt','error')
  183. mylog.logcfg()
  184. #............................模块运行前,先读取数据库中所有结束时间为0的数据,需要从数据库中读取...................................
  185. df_warning_ram=pd.DataFrame(columns=['sn','time','deltsoc','cellsoc'])
  186. df_warning_ram1=pd.DataFrame(columns=['sn','time1','deltsoc1'])
  187. df_warning_ram2=pd.DataFrame(columns=['sn','time2','deltAs2'])
  188. df_warning_ram3=pd.DataFrame(columns=['sn','time3','standingtime','standingtime1','standingtime2'])
  189. df_lfp_ram=pd.DataFrame()
  190. df_lfp_ram1=pd.DataFrame()
  191. #定时任务.......................................................................................................................................................................
  192. scheduler = BlockingScheduler()
  193. scheduler.add_job(saftywarning_cal, 'interval', hours=6)
  194. try:
  195. scheduler.start()
  196. except Exception as e:
  197. scheduler.shutdown()
  198. print(repr(e))
  199. mylog.logopt(e)