offline_alarm_sta.py 3.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354
  1. import numpy as np
  2. import pandas as pd
  3. import datetime,time
  4. from sqlalchemy import create_engine
  5. from sqlalchemy.orm import sessionmaker
  6. from urllib import parse
  7. import pymysql
  8. def offline_alarm_sta(df_sns, db_iotp_engine, db_qxcas_engine, conn, db_sp_engine):
  9. cursor = conn.cursor()
  10. sql = "select sn, DATE_FORMAT(add_time, '%Y-%m-%d %H:%i:%s') as time, level, last_time from offline_info where add_time >= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%Y-%m-%d 00:00:00')"+ \
  11. " and add_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 DAY), '%Y-%m-%d 23:59:59')"
  12. df_offlinealarm_lastday = pd.read_sql(sql, db_qxcas_engine)
  13. sn_offline = df_offlinealarm_lastday['sn'].tolist()
  14. sql = "select * from all_fault_info where end_time='{}' and code={}".format('0000-00-00 00:00:00', 59)
  15. df_all_alarm_info = pd.read_sql(sql, db_sp_engine)
  16. sn_allalarm = df_all_alarm_info['product_id'].tolist()
  17. # 遍历昨日报警和报警列表,如果 1:sn存在于昨日报警,但不存在与报警allinfo里,则添加一条新的报警记录到allinfo里;
  18. # 2:如果sn存在于allinfo但不存在于昨日报警,则记录故障为结束;
  19. # 3: 如果sn在两个表中都存在,但级别不同,则更新级别(离线暂时不考虑级别)。
  20. sn_new = list(set(sn_offline)-set(sn_allalarm))
  21. sn_close = list(set(sn_allalarm)-set(sn_offline))
  22. # sn_cross = list(set(sn_offline) & set(sn_allalarm))
  23. # 新增
  24. df_new = pd.DataFrame(columns=['add_time', 'product_id', 'start_time', 'end_time', 'code', 'level', 'info'])
  25. now = datetime.datetime.now()
  26. for sn in sn_new:
  27. df = df_offlinealarm_lastday[df_offlinealarm_lastday['sn']==sn]
  28. sql = "select * from ff_battery_status where devcode = '{}'".format(sn)
  29. df_on = pd.read_sql(sql, db_iotp_engine)
  30. if len(df_sns[df_sns['sn']==sn]) > 0:
  31. factory = df_sns[df_sns['sn']==sn]['factory'].values[0]
  32. df_new = df_new.append({'add_time':now, 'product_id':sn, 'factory':factory, 'start_time':pd.to_datetime(df_on['status_time'].values[0]), 'end_time':'0000-00-00 00:00:00', 'info':'离线报警','code':59, 'level':1}, ignore_index=True)
  33. if not df_new.empty:
  34. df_new.to_sql('all_fault_info', db_sp_engine, if_exists='append', index=False)
  35. # 修改
  36. now = datetime.datetime.now()
  37. for sn in sn_close:
  38. df = df_offlinealarm_lastday[df_offlinealarm_lastday['sn']==sn]
  39. df1 = df_all_alarm_info[df_all_alarm_info['product_id']==sn]
  40. sql = "select * from ff_battery_status where devcode = '{}'".format(sn)
  41. df_on = pd.read_sql(sql, db_iotp_engine)
  42. sql = ''' update all_fault_info set update_time='{}', end_time='{}' where product_id = '{}' and start_time = '{}' and code = {}'''.format \
  43. (now, pd.to_datetime(df_on['status_time'].values[0]), sn, pd.to_datetime(df1['start_time'].values[0]), df1['code'].values[0])
  44. cursor.execute(sql)
  45. conn.commit()