deploy_test.py 29 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557
  1. __author__ = 'lmstack'
  2. #coding=utf-8
  3. import os
  4. import datetime
  5. import pandas as pd
  6. from LIB.BACKEND import DBManager, Log
  7. from sqlalchemy import create_engine
  8. from sqlalchemy.orm import sessionmaker
  9. import time, datetime
  10. import dateutil.relativedelta
  11. import traceback
  12. from LIB.MIDDLE.CellStateEstimation.Common import log
  13. from LIB.MIDDLE.CellStateEstimation.Common.V1_0_1 import DBDownload as DBDownload
  14. import time, datetime
  15. from pandas.core.frame import DataFrame
  16. from apscheduler.schedulers.blocking import BlockingScheduler
  17. from LIB.MIDDLE.SaftyCenter.DataDiag_Static.DataStatistics import DataSta
  18. from LIB.MIDDLE.SaftyCenter.DataDiag_Static.SC_CtrlSafty import CtrlSafty
  19. from LIB.MIDDLE.SaftyCenter.DataDiag_Static.DiagDataMerge import DiagDataMerge
  20. from LIB.MIDDLE.SaftyCenter.DataDiag_Static.SC_SamplingSafty import SamplingSafty
  21. from LIB.MIDDLE.SaftyCenter.DataDiag_Static.SC_BMSUploadError import BMSReportError
  22. from LIB.MIDDLE.SaftyCenter.DataDiag_Static import CBMSBatDiag
  23. from LIB.MIDDLE.SaftyCenter.Common import DBDownload as DBDw
  24. from LIB.MIDDLE.CellStateEstimation.Common.V1_0_1 import BatParam as QX_BatteryParam
  25. from urllib import parse
  26. import pymysql
  27. #...................................电池包电芯安全诊断函数......................................................................................................................
  28. def diag_cal():
  29. task_on=1
  30. global SNnums
  31. # global df_Diag_Ram
  32. global start
  33. # 更新sn列表
  34. host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com'
  35. port=3306
  36. db='qixiang_oss'
  37. user='qx_algo_rw'
  38. password='qx@123456'
  39. conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)
  40. cursor = conn.cursor()
  41. cursor.execute("select sn, imei, add_time from app_device where status in (1,2,3)")
  42. res = cursor.fetchall()
  43. df_sn = pd.DataFrame(res, columns=['sn', 'imei', 'add_time'])
  44. df_sn = df_sn.reset_index(drop=True)
  45. cursor.close()
  46. conn.close();
  47. # 故障映射表
  48. host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  49. port=3306
  50. db='algo_dict'
  51. user='qx_algo_rw'
  52. password=parse.quote_plus('qx@123456')
  53. db_engine = create_engine(
  54. "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
  55. user, password, host, port, db
  56. ))
  57. errorcode_map = pd.read_sql("select * from faultcode_map", db_engine)
  58. db_engine.dispose()
  59. # 算法参数
  60. host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  61. port=3306
  62. db='safety_platform'
  63. user='qx_algo_rw'
  64. password=parse.quote_plus('qx@123456')
  65. db_engine = create_engine(
  66. "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
  67. user, password, host, port, db
  68. ))
  69. #............................模块运行前,先读取数据库中所有结束时间为0的数据,需要从数据库中读取................``
  70. # print("select start_time, end_time, product_id, code, level, info, advice, factory from {}".format(tablename))
  71. try:
  72. df_Diag_Ram=pd.read_sql("select start_time, end_time, product_id, code, level, info, advice, Batpos from all_fault_info_copy where end_time='0000-00-00 00:00:00' ", db_engine)
  73. finally:
  74. db_engine.dispose()
  75. # df_Diag_Ram=df_Diag_Ram.dropna(axis=1,how='any')
  76. # df_Diag_Ram=df_Diag_Ram.reset_index(drop=True)
  77. #读取结果数据库数据........................................................................................................................................................
  78. host1='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  79. port1=3306
  80. db1='qx_cas'
  81. user1='qx_algo_rw'
  82. password1='qx@123456'
  83. mode=1
  84. tablename1='cellstateestimation_soh'
  85. tablename2='cellstateestimation_uniform_socvoltdiff'
  86. DBRead=DBDownload.DBDownload(host1, port1, db1, user1, password1,mode)
  87. DBRead.connect()
  88. #读取电池当前运营状态.....................................................................................................................................................
  89. host2='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com'
  90. port2=3306
  91. db2='qixiang_manage'
  92. user2='qx_algo_rw'
  93. password2='qx@123456'
  94. mode=4
  95. tablename3='py_battery'
  96. DBReadOpe=DBDw.DBDownload(host2, port2, db2, user2, password2,mode)
  97. DBReadOpe.connect()
  98. host3='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  99. port3=3306
  100. db3='safety_platform'
  101. user3='qx_algo_rw'
  102. password3='qx@123456'
  103. db_engine = create_engine(
  104. "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
  105. user3, parse.quote_plus(password3), host3, port3, db3
  106. ))
  107. conn_platform = pymysql.connect(host=host3, port=port3, user=user3, password=password3, database=db3)
  108. cursor_platform = conn_platform.cursor()
  109. #..................................设置时间..........................................................
  110. start=time.time()
  111. end_time=datetime.datetime.now()
  112. start_time=end_time-datetime.timedelta(seconds=900)#15分钟跑一次,一次取15分钟数据
  113. start_time=start_time.strftime('%Y-%m-%d %H:%M:%S')
  114. end_time=end_time.strftime('%Y-%m-%d %H:%M:%S')
  115. logger.info(" diag_cal START!")
  116. for i in range(0, len(df_sn)):
  117. try:
  118. factory = "骑享"
  119. sn = df_sn.loc[i, 'sn']
  120. if df_sn.loc[i, 'imei'][5:9] == 'N640':
  121. celltype=1 #6040三元电芯
  122. FactoryType = 1
  123. elif df_sn.loc[i, 'imei'][5:9] == 'N440':
  124. celltype=2 #4840三元电芯
  125. FactoryType = 1
  126. elif df_sn.loc[i, 'imei'][5:9] == 'L660':
  127. celltype=99 # 6060锂电芯
  128. FactoryType = 1
  129. elif df_sn.loc[i, 'imei'][3:5] == 'LX' and df_sn.loc[i, 'imei'][5:9] == 'N750':
  130. celltype=3 #力信 50ah三元电芯
  131. FactoryType = 3
  132. elif df_sn.loc[i, 'imei'][3:5] == 'CL' and df_sn.loc[i, 'imei'][5:9] == 'N750':
  133. celltype=4 #CATL 50ah三元电芯
  134. FactoryType = 2
  135. elif 'TJMCL'in sn: # 金茂
  136. celltype=100 #CATL 50ah三元电芯
  137. factory = "金茂换电"
  138. FactoryType = 0
  139. else:
  140. logger.info("pid-{} celltype-{} SN: {} SKIP!".format(os.getpid(), "未知", sn))
  141. continue
  142. # sn = 'PK504B10100004349'
  143. # start_time = '2021-12-30 18:00:04'
  144. # end_time = '2021-12-30 18:15:04'
  145. logger.info("pid-{} celltype-{} SN: {} START!".format(os.getpid(), celltype, sn))
  146. # sys.exit()
  147. param=QX_BatteryParam.BatParam(celltype)
  148. #读取原始数据库数据........................................................................................................................................................
  149. dbManager = DBManager.DBManager()
  150. df_data = dbManager.get_data(sn=sn, start_time=start_time, end_time=end_time, data_groups=['bms'])
  151. df_bms = df_data['bms']
  152. df_bms=df_bms.dropna(subset=['总电流[A]'])
  153. df_bms=df_bms.reset_index(drop=True)
  154. # with DBRead as DBRead:
  155. df_soh=DBRead.getdata('time_st,sn,soh,cellsoh', tablename=tablename1, sn=sn, timename='time_sp', st=start_time, sp=end_time)
  156. df_uniform=DBRead.getdata('time,sn,cellsoc_diff,cellmin_num,cellmax_num', tablename=tablename2, sn=sn, timename='time', st=start_time, sp=end_time)
  157. # with DBReadOpe as DBReadOpe:
  158. df_OprtnSta=DBReadOpe.getdata('qrcode','status', tablename=tablename3, sn=sn, timename='', st='', sp='',factory='')#取最后一条运营状态
  159. #电池诊断.....................................................................................................................................................................
  160. CellFltInfo=DataFrame(columns=['start_time', 'end_time', 'product_id', 'code', 'level', 'info','advice'])
  161. df_Diag_Ram_sn = df_Diag_Ram.loc[df_Diag_Ram['product_id']==sn]#历史故障
  162. df_Diag_Ram_sn_else = df_Diag_Ram.loc[df_Diag_Ram['product_id']!=sn]#sn之外的故障
  163. CellFltInfo = df_Diag_Ram_sn.drop('Batpos',axis=1)
  164. #获取当前故障电池的历史故障信息↑↑↑↑↑↑↑↑↑↑↑.......................................................................................................................................
  165. if not df_bms.empty:
  166. df_Diag_Batdiag_update_xq=SamplingSafty.main(sn,param,df_bms,CellFltInfo)#采样安全
  167. df_Diag_Batdiag_update_xq2=CtrlSafty.main(sn,param,df_bms,df_Diag_Batdiag_update_xq)#控制安全
  168. # batDiag=CBMSBatDiag.BatDiag(sn,celltype,df_bms, df_soh, df_uniform, df_Diag_Batdiag_update_xq2)#鹏飞计算
  169. # df_Diag_Batdiag_update2=batDiag.diag()
  170. # df_Diag_Batdiag_update=BMSReportError.main(sn,df_bms,df_Diag_Batdiag_update2,FactoryType,errorcode_map)
  171. df_Diag_Batdiag_update=BMSReportError.main(sn,df_bms,df_Diag_Batdiag_update_xq2,FactoryType,errorcode_map)
  172. # df_Diag_Batdiag_update=batDiag.diag()
  173. # df_Diag_Batdiag_update=BatDiag.diag()
  174. else:
  175. df_Diag_Batdiag_update_xq=DataFrame(columns=['start_time','end_time','product_id','code','level','info','advice','Batpos'])
  176. df_Diag_Batdiag_update_xq2=DataFrame(columns=['start_time','end_time','product_id','code','level','info','advice','Batpos'])
  177. df_Diag_Batdiag_update2=DataFrame(columns=['start_time','end_time','product_id','code','level','info','advice','Batpos'])
  178. df_Diag_Batdiag_update=DataFrame(columns=['start_time','end_time','product_id','code','level','info','advice','Batpos'])
  179. df_Diag_Ram_add,df_Diag_Ram_Update=DiagDataMerge.DetaMerge(df_Diag_Ram_sn,df_Diag_Batdiag_update,df_OprtnSta,df_Diag_Ram_sn_else)
  180. # df_Diag_Ram.to_csv('df_Diag_Ram.csv')
  181. # df_Diag_Ram_add.to_csv('df_Diag_Ram_add.csv')
  182. # df_Diag_Ram_Update.to_csv('df_Diag_Ram_add.csv')
  183. if len(df_Diag_Ram_add) > 0:#新增故障
  184. df_Diag_Ram_add.columns = ['start_time', 'end_time', 'product_id', 'code', 'level', 'info','advice', 'Batpos']
  185. df_Diag_Ram_add['factory'] = factory
  186. df_Diag_Ram_add['add_time'] = datetime.datetime.now()
  187. df_Diag_Ram_add.to_sql("all_fault_info_copy",con=db_engine, if_exists="append",index=False)
  188. # df_Diag_Ram_add.to_csv(r'D:\Work\Code_write\data_analyze_platform\USER\01Screen_Problem\result_add.csv',index=False,encoding='GB18030')
  189. if len(df_Diag_Ram_Update) > 0:#更改故障
  190. df_Diag_Ram_Update = df_Diag_Ram_Update.reset_index(drop=True)
  191. try:
  192. for i in range(0,len(df_Diag_Ram_Update)):
  193. sql = '''
  194. update all_fault_info_copy set update_time='{}', end_time='{}', Batpos={} where product_id='{}' and code='{}' and start_time='{}'
  195. '''.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), df_Diag_Ram_Update.loc[i,'end_time'], df_Diag_Ram_Update.loc[i, 'Batpos'],
  196. df_Diag_Ram_Update.loc[i,'product_id'], df_Diag_Ram_Update.loc[i,'code'],
  197. df_Diag_Ram_Update.loc[i,'start_time'])
  198. cursor_platform.execute(sql)
  199. conn_platform.commit()
  200. except:
  201. logger.error(sql)
  202. logger.error(traceback.format_exc)
  203. logger.error(u"{} :{},{} 任务运行错误\n".format(sn,start_time,end_time), exc_info=True)
  204. # df_Diag_Ram_Update_change.to_csv(r'D:\Work\Code_write\data_analyze_platform\USER\01Screen_Problemm\result_change.csv',index=False,encoding='GB18030')
  205. end=time.time()
  206. logger.info("pid-{} celltype-{} SN: {} DONE!".format(os.getpid(), celltype, sn))
  207. except Exception as e :
  208. if (sn == 'MGMLXN750N21AN014' or sn == 'MGMLXN750N21B5011'):
  209. continue
  210. logger.error(traceback.format_exc)
  211. logger.error(str(e))
  212. logger.error(u"{} :{},{} 任务运行错误\n".format(sn,start_time,end_time), exc_info=True)
  213. DBReadOpe.close()
  214. DBRead.close()
  215. cursor_platform.close()
  216. conn_platform.close()
  217. db_engine.dispose()
  218. logger.info(" diag_cal Done!")
  219. task_on=0
  220. #.................................统计程序...............................
  221. def DaTa_Sta_Week_Task():
  222. task_on=1
  223. factory_info=['骑享','金茂换电']
  224. all_period_fault_info=DataFrame(columns=['factory','week','level1_count','level2_count','level3_count','level4_count','level5_count','solve_rate'])
  225. #............................获取数据................................
  226. host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  227. port=3306
  228. db='safety_platform'
  229. user='qx_algo_rw'
  230. password='qx@123456'
  231. mode=2
  232. tablename1='all_fault_info_copy'
  233. db_engine = create_engine(
  234. "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
  235. user, parse.quote_plus(password), host, port, db
  236. ))
  237. try:
  238. logger.info("DaTa_Sta_Week_Task START!")
  239. df_fltinfopt=DataFrame(columns=['product_id','level','code','start_time','end_time','batpos','factory'])
  240. DBRead=DBDw.DBDownload(host, port, db, user, password,mode)
  241. for i in range (0,len(factory_info)):
  242. with DBRead as DBRead:
  243. df_fltinfo=DBRead.getdata('product_id','level','code','start_time','end_time','batpos','factory',tablename=tablename1,factory='',sn='',timename='',st='',sp='')#dbdownload经过了改编
  244. df_fltinfopt=df_fltinfopt.append(df_fltinfo)
  245. df_fltinfopt=df_fltinfopt.reset_index(drop=True)
  246. for j in range(0,len(factory_info)):
  247. toweek='Week'+time.strftime('%W')
  248. df_fltinfo=df_fltinfopt[df_fltinfopt['factory']==factory_info[j]]
  249. #............................获取时间................................
  250. end_time=datetime.datetime.now()
  251. # end_time=datetime.datetime.strptime(end_time,'%Y-%m-%d')
  252. start_time_week=end_time-datetime.timedelta(days=7)
  253. start_time_week=start_time_week.strftime('%Y-%m-%d')
  254. end_time=end_time.strftime('%Y-%m-%d')
  255. FltAlarmInfo,Celltype=DataSta.SaftyWarningSta(df_fltinfo,start_time_week,end_time,factory_info[j])
  256. FaultLvlCount=DataSta.WeekInfoSta(df_fltinfo,start_time_week,end_time)
  257. for i in range(1,6):
  258. if not FaultLvlCount[FaultLvlCount['level']==i]['product_id'].empty:
  259. all_period_fault_info.loc[j,'level'+str(i)+'_count']=int(FaultLvlCount[FaultLvlCount['level']==i]['product_id'].values)
  260. else:
  261. all_period_fault_info.loc[j,'level'+str(i)+'_count']=int(0)
  262. all_period_fault_info.loc[j,'factory']=factory_info[j]
  263. all_period_fault_info.loc[j,'week']=toweek
  264. all_period_fault_info.loc[j,'solve_rate']=FltAlarmInfo.loc[0,'OprationManageRate']
  265. all_period_fault_info.fillna(0,inplace=False)
  266. if not all_period_fault_info.empty:
  267. all_period_fault_info.to_sql('all_period_fault_info', db_engine, if_exists='append', index=False)
  268. logger.info("DaTa_Sta_Week_Task DONE!")
  269. except Exception as e:
  270. logger.error(traceback.format_exc)
  271. logger.error(u" 任务运行错误\n".format(), exc_info=True)
  272. finally:
  273. db_engine.dispose()
  274. task_on=0
  275. # all_period_fault_info.to_csv('all_period_fault_info.csv')
  276. def DaTa_Sta_Minutes_Task():
  277. task_on=1
  278. factory_info=['骑享','金茂换电']
  279. #............................获取数据................................
  280. host='172.16.121.236'
  281. port=3306
  282. db='fastfun'
  283. user='readonly'
  284. password='Fast1234'
  285. mode=3
  286. tablename1='ff_battery_accum'
  287. tablename2='ff_location'
  288. DBRead=DBDw.DBDownload(host, port, db, user, password,mode)
  289. with DBRead as DBRead:
  290. df_last_accum=DBRead.getdata('devcode','dsg_phaccum','dsg_ahaccum',tablename=tablename1,factory='',sn='',timename='',st='',sp='')#dbdownload经过了改编
  291. df_last_pos=DBRead.getdata('devcode','latitude','longitude',tablename=tablename2,factory='',sn='',timename='',st='',sp='')#dbdownload经过了改编
  292. host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  293. port=3306
  294. db='qx_cas'
  295. user='qx_algo_rw'
  296. password='qx@123456'
  297. mode=3
  298. tablename2='bat_first_data_time'
  299. DBRead=DBDw.DBDownload(host, port, db, user, password,mode)
  300. with DBRead as DBRead:
  301. df_FirstDataTime=DBRead.getdata('sn','first_data_time',tablename=tablename2,factory='',sn='',timename='',st='',sp='')#dbdownload经过了改编
  302. host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  303. port=3306
  304. db='safety_platform'
  305. user='qx_algo_rw'
  306. password='qx@123456'
  307. mode=2
  308. tablename1='all_fault_info_copy'
  309. db_engine = create_engine(
  310. "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
  311. user, parse.quote_plus(password), host, port, db
  312. ))
  313. DBRead=DBDw.DBDownload(host, port, db, user, password,mode)
  314. #............................获取时间................................
  315. end_time=datetime.datetime.now()
  316. # end_time=datetime.datetime.strptime(end_time,'%Y-%m-%d')
  317. start_time=end_time-datetime.timedelta(days=1)
  318. start_time=start_time.strftime('%Y-%m-%d')
  319. end_time=end_time.strftime('%Y-%m-%d')
  320. #............................执行程序................................
  321. 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','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'])
  322. for j in range(0,len(factory_info)):
  323. with DBRead as DBRead:
  324. df_fltinfo=DBRead.getdata('product_id','level','code','start_time','batpos',tablename=tablename1,factory=factory_info[j],sn='',timename='',st='',sp='')#dbdownload经过了改编
  325. #............................执行程序................................
  326. FltAlarmInfo,Celltype=DataSta.SaftyWarningSta(df_fltinfo,start_time,end_time,factory_info[j])
  327. SatftyCount=DataSta.SftyWrngClsfy(df_fltinfo)
  328. MaxAccumAh,TotalAccumAh,MaxCycle,MaxRunningHour,TotalRunHour=DataSta.AccumInfo(df_last_accum,df_FirstDataTime,end_time,factory_info[j])
  329. all_location_info=DataSta.FltBatPosition(df_last_pos,df_fltinfo,factory_info[j])
  330. all_statistic_info.loc[j,'factory']=factory_info[j]
  331. all_statistic_info.loc[j,'total_alarm']=FltAlarmInfo.loc[0,'SftyPlt_Data_Total']
  332. all_statistic_info.loc[j,'alarm_total_today']=FltAlarmInfo.loc[0,'SftyPlt_Data_day']
  333. all_statistic_info.loc[j,'alarm_close_today']=FltAlarmInfo.loc[0,'CS_Warning_day_Finish_Count']
  334. all_statistic_info.loc[j,'alarm_not_close_today']=FltAlarmInfo.loc[0,'SftyPlt_Data_day']-FltAlarmInfo.loc[0,'CS_Warning_day_Finish_Count']
  335. all_statistic_info.loc[j,'alarm_uregent_total_today']=FltAlarmInfo.loc[0,'SftyPlt_EmgcyData_day']
  336. all_statistic_info.loc[j,'alarm_uregent_close_today']=FltAlarmInfo.loc[0,'SftyPlt_EmgcyData_day_Finish_Count']
  337. all_statistic_info.loc[j,'alarm_uregent_not_close_today']=FltAlarmInfo.loc[0,'SftyPlt_EmgcyData_day']-FltAlarmInfo.loc[0,'SftyPlt_EmgcyData_day_Finish_Count']
  338. all_statistic_info.loc[j,'run_time_total']=TotalRunHour
  339. all_statistic_info.loc[j,'dischrg_total']=TotalAccumAh
  340. all_statistic_info.loc[j,'odo_total']=0
  341. all_statistic_info.loc[j,'max_dischrg_one']=MaxAccumAh
  342. all_statistic_info.loc[j,'max_runtime_one']=MaxRunningHour
  343. all_statistic_info.loc[j,'max_cycle_one']=MaxCycle
  344. all_statistic_info.loc[j,'max_odo_one']=0
  345. all_statistic_info.loc[j,'alarm_close_total']=FltAlarmInfo.loc[0,'CS_Warning_Total_Finish_Count']
  346. all_statistic_info.loc[j,'alarm_total']=FltAlarmInfo.loc[0,'SftyPlt_Data_Total']
  347. CellType=Celltype.columns.tolist()
  348. CellType=','.join(CellType)
  349. all_statistic_info.loc[0,'cell_type']=str(CellType)
  350. CellTypeCount=Celltype.loc[0].tolist()
  351. CellTypeCount=[str(x) for x in CellTypeCount]
  352. CellTypeCount=','.join(CellTypeCount)
  353. all_statistic_info.loc[j,'cell_type_count']=str(CellTypeCount)
  354. all_statistic_info.loc[j,'cell_safety_risk_count']=SatftyCount.loc[0,'CellSaftyCount']
  355. all_statistic_info.loc[j,'data_safety_risk_count']=SatftyCount.loc[0,'DataSaftyCodeCount']
  356. all_statistic_info.loc[j,'status_safety_risk_count']=SatftyCount.loc[0,'StateSaftyCodeCount']
  357. all_statistic_info.loc[j,'hv_safety_risk_count']=SatftyCount.loc[0,'HvSaftyCodeCount']
  358. all_statistic_info.loc[j,'system_safety_risk_count']=SatftyCount.loc[0,'SysSaftyCodeCount']
  359. all_statistic_info.loc[j,'sample_safety_risk_count']=SatftyCount.loc[0,'SamplingSatyCount']
  360. all_statistic_info.loc[j,'controller_safety_risk_count']=SatftyCount.loc[0,'CtrlSaftyCodeCount']
  361. all_statistic_info.loc[j,'design_safety_risk_count']=SatftyCount.loc[0,'DsnSaftyCodeCount']
  362. # all_statistic_info.to_csv('all_statistic_info.csv')
  363. #
  364. try:
  365. logger.info("DaTa_Sta_Minutes_Task START!")
  366. # 更新统计信息
  367. conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)
  368. cursor = conn.cursor()
  369. sql = ''' update all_statistic_info set update_time='{}', total_alarm={}, alarm_total_today={}, alarm_close_today={}, alarm_not_close_today={},
  370. alarm_uregent_total_today={}, alarm_uregent_close_today={},alarm_uregent_not_close_today={},run_time_total={},dischrg_total={},
  371. odo_total={},max_dischrg_one={},max_runtime_one={},max_cycle_one={},max_odo_one={},alarm_close_total={},alarm_total={},cell_type='{}',
  372. cell_type_count='{}',cell_safety_risk_count={}, data_safety_risk_count={},status_safety_risk_count={},hv_safety_risk_count={},
  373. system_safety_risk_count={},sample_safety_risk_count={},controller_safety_risk_count={},design_safety_risk_count={}
  374. where factory='{}'
  375. '''.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), all_statistic_info.loc[j,'total_alarm'], all_statistic_info.loc[j,'alarm_total_today'],all_statistic_info.loc[j,'alarm_close_today'],
  376. all_statistic_info.loc[j,'alarm_not_close_today'],all_statistic_info.loc[j,'alarm_uregent_total_today'],all_statistic_info.loc[j,'alarm_uregent_close_today'],
  377. all_statistic_info.loc[j,'alarm_uregent_not_close_today'],all_statistic_info.loc[j,'run_time_total'],all_statistic_info.loc[j,'dischrg_total'],
  378. all_statistic_info.loc[j,'odo_total'],all_statistic_info.loc[j,'max_dischrg_one'],all_statistic_info.loc[j,'max_runtime_one'],
  379. all_statistic_info.loc[j,'max_cycle_one'],all_statistic_info.loc[j,'max_odo_one'],all_statistic_info.loc[j,'alarm_close_total'],
  380. all_statistic_info.loc[j,'alarm_total'],all_statistic_info.loc[j,'cell_type'],all_statistic_info.loc[j,'cell_type_count'],
  381. all_statistic_info.loc[j,'cell_safety_risk_count'],all_statistic_info.loc[j,'data_safety_risk_count'],all_statistic_info.loc[j,'status_safety_risk_count'],
  382. all_statistic_info.loc[j,'hv_safety_risk_count'],all_statistic_info.loc[j,'system_safety_risk_count'],all_statistic_info.loc[j,'sample_safety_risk_count'],
  383. all_statistic_info.loc[j,'controller_safety_risk_count'],all_statistic_info.loc[j,'design_safety_risk_count'],all_statistic_info.loc[j,'factory'])
  384. cursor.execute(sql)
  385. conn.commit()
  386. # 更新故障位置信息
  387. if not all_location_info.empty:
  388. sql = "delete from all_fault_location_info where factory='{}'".format(all_statistic_info.loc[j,'factory'])
  389. cursor.execute(sql)
  390. all_location_info.to_sql('all_fault_location_info', db_engine, if_exists='append', index=False)
  391. conn.commit()
  392. logger.info("DaTa_Sta_Minutes_Task DONE!")
  393. except:
  394. logger.error(traceback.format_exc)
  395. logger.error(u"{},{} 任务运行错误\n".format(start_time,end_time), exc_info=True)
  396. finally:
  397. conn.close();
  398. db_engine.dispose();
  399. task_on=0
  400. if __name__ == "__main__":
  401. # # 时间设置
  402. # now_time = datetime.datetime.now()
  403. # pre_time = now_time + dateutil.relativedelta.relativedelta(hours=-1)#上小时时间
  404. # end_time=datetime.datetime.strftime(now_time,"%Y-%m-%d %H:00:00")
  405. # start_time=datetime.datetime.strftime(pre_time,"%Y-%m-%d %H:00:00")
  406. # history_run_flag = False # 历史数据运行标志
  407. # # 更新sn列表
  408. # host='rm-bp10j10qy42bzy0q7.mysql.rds.aliyuncs.com'
  409. # port=3306
  410. # db='qixiang_oss'
  411. # user='qx_algo_rw'
  412. # password='qx@123456'
  413. # conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)
  414. # cursor = conn.cursor()
  415. # cursor.execute("select sn, imei, add_time from app_device where status in (1,2,3)")
  416. # res = cursor.fetchall()
  417. # df_sn = pd.DataFrame(res, columns=['sn', 'imei', 'add_time'])
  418. # df_sn = df_sn.reset_index(drop=True)
  419. # cursor.close()
  420. # conn.close();
  421. # df_sn['factory'] = '骑享'
  422. # db = 'qx_cas'
  423. # conn = pymysql.connect(host=host, port=port, user=user, password=password, database=db)
  424. # cursor = conn.cursor()
  425. # cursor.execute("select sn, factory from sn_list")
  426. # res = cursor.fetchall()
  427. # df_sn2 = pd.DataFrame(res, columns=['sn', 'factory'])
  428. # df_sn2['imei'] = ""
  429. # df_sn = pd.concat([df_sn, df_sn2], axis=0)
  430. # df_sn = df_sn.reset_index(drop=True)
  431. # # 数据库配置
  432. # host = 'rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  433. # port = 3306
  434. # user = 'qx_cas'
  435. # password = parse.quote_plus('Qx@123456')
  436. # database = 'qx_cas'
  437. # db_engine = create_engine(
  438. # "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
  439. # user, password, host, port, database
  440. # ))
  441. # DbSession = sessionmaker(bind=db_engine)
  442. # # 运行历史数据配置
  443. # df_first_data_time = pd.read_sql("select * from bat_first_data_time", db_engine)
  444. # 日志配置
  445. now_str = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()).replace(":","_")
  446. log_path = 'log_test/' + now_str
  447. if not os.path.exists(log_path):
  448. os.makedirs(log_path)
  449. log = Log.Mylog(log_name='saftyCenter_DataSta&Static_test', log_level = 'info')
  450. log.set_file_hl(file_name='{}/info.log'.format(log_path), log_level='info', size=1024* 1024 * 100)
  451. log.set_file_hl(file_name='{}/error.log'.format(log_path), log_level='error', size=1024* 1024 * 100)
  452. logger = log.get_logger()
  453. logger.info("pid is {}".format(os.getpid()))
  454. # 算法参数
  455. host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com'
  456. port=3306
  457. db='safety_platform'
  458. user='qx_algo_rw'
  459. password=parse.quote_plus('qx@123456')
  460. tablename='all_fault_info_copy'
  461. db_engine = create_engine(
  462. "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(
  463. user, password, host, port, db
  464. ))
  465. #............................模块运行前,先读取数据库中所有结束时间为0的数据,需要从数据库中读取................``
  466. # print("select start_time, end_time, product_id, code, level, info, advice, factory from {}".format(tablename))
  467. try:
  468. df_Diag_Ram=pd.read_sql("select start_time, end_time, product_id, code, level, info, advice, Batpos from all_fault_info_copy", db_engine)
  469. finally:
  470. db_engine.dispose()
  471. # result = result[['start_time', 'end_time', 'product_id', 'code', 'level', 'info', 'advice']]
  472. # df_Diag_Ram=result[result['end_time']=='0000-00-00 00:00:00']
  473. global task_on
  474. task_on=0
  475. diag_cal()
  476. # DaTa_Sta_Minutes_Task()
  477. # DaTa_Sta_Week_Task()
  478. #定时任务.......................................................................................................................................................................
  479. scheduler = BlockingScheduler()
  480. if task_on==0:
  481. scheduler.add_job(diag_cal, 'interval', seconds=900, id='diag_job')
  482. # scheduler.add_job(DaTa_Sta_Week_Task, 'interval',start_date='2021-11-15 08:00:00', days=7, id='Week_Task')
  483. # scheduler.add_job(DaTa_Sta_Minutes_Task, 'interval', seconds=180, id='Minutes_Task')
  484. try:
  485. scheduler.start()
  486. except Exception as e:
  487. scheduler.shutdown()
  488. logger.error(str(e))