#!/usr/bin/env python # encoding: utf-8 """ @time: 2021/5/31 10:22 @desc: 更新设备维保信息（耗能占比和运行时间），粒度：5min """ import time from event_storage import EventStorage from datetime import datetime, timedelta from log import OutPutLog import traceback from dateutil.parser import parse class UpdateMaintenanceInfo: """更新设备的维保信息（每隔五分钟更新一次）""" def __init__(self): self._log = OutPutLog() self._operate_mysql = EventStorage() self.run_time = 0 def update_info(self): while 1: time.sleep(1) if time.time()-self.run_time > 60: self.run_time = time.time() now_time = datetime.now() today_time = now_time.strftime('%Y-%m-%d') month_time = now_time.strftime('%Y-%m') use_data = {} day_use = 0 # 所有设备的日耗能 month_use = 0 # 所有设备的月耗能 # 查询电流的serial_number current_threshold = 0.5 # 电流阈值，单位A i_unit = "A" i_serial_number = {} sql_i_serial_number = f"SELECT device_name,serial_number FROM data_point_tbl WHERE unit=\'{i_unit}\' AND `device_name` LIKE '%_electric%';" res_i_serial_number = self._operate_mysql.execute_sql(sql_i_serial_number) for each in res_i_serial_number: i_serial_number.setdefault(each["device_name"], []).append("c" + str(each["serial_number"])) # {'pg00-zfdj_electric': ['c142', 'c143', 'c144']} # 更新维保信息 v_unit = "kWh" # 耗能单位 find_electric_device = f"SELECT device_name, serial_number FROM data_point_tbl WHERE unit=\'{v_unit}\' AND `device_name` LIKE '%_electric%';" all_electric_device = self._operate_mysql.execute_sql(find_electric_device) for each_device in all_electric_device: # each_device = {'device_name': 'pg00-zfdj_electric', 'serial_number': 147} v_serial_number = "c" + str(each_device['serial_number']) # c147 device_name = each_device['device_name'] # pg00-zfdj_electric table_name = "table_" + device_name # table_pg00-zfdj_electric # 查询日耗能 # [{'t': '2021-05-28', 'c159': 6.0}, {'t': '2021-05-27', 'c159': 4.2}] day_electric = f"SELECT DATE_FORMAT(times, '%Y-%m-%d') AS t, MAX({v_serial_number}) AS {v_serial_number} FROM `{table_name}` GROUP BY t ORDER BY t DESC LIMIT 2;" day_electric_res = self._operate_mysql.execute_sql(day_electric) if len(day_electric_res) == 0: day_electric_use = 0 elif day_electric_res[0]['t'] != today_time: day_electric_use = 0 elif len(day_electric_res) == 2: day_electric_use = day_electric_res[0][v_serial_number] - day_electric_res[1][v_serial_number] elif len(day_electric_res) == 1: day_electric_use = day_electric_res[0][v_serial_number] if day_electric_use < 0: day_electric_use = 0 # 查询月耗能 month_electric = f"SELECT DATE_FORMAT(times, '%Y-%m') AS t, MAX({v_serial_number}) AS {v_serial_number} FROM `{table_name}` GROUP BY t ORDER BY t DESC LIMIT 2;" month_electric_res = self._operate_mysql.execute_sql(month_electric) if len(month_electric_res) == 0: month_electric_use = 0 elif month_electric_res[0]['t'] != month_time: month_electric_use = 0 elif len(month_electric_res) == 2: month_electric_use = month_electric_res[0][v_serial_number] - month_electric_res[1][v_serial_number] if month_electric_use < 0: month_electric_use = month_electric_res[0][v_serial_number] elif len(month_electric_res) == 1: month_electric_use = month_electric_res[0][v_serial_number] if month_electric_use < 0: month_electric_use = 0 # 查看设备历史运行时间 sql_begin_run_time = f"SELECT run_time FROM maintenance_information WHERE device_name=\'{device_name}\';" begin_run_time = self._operate_mysql.execute_sql(sql_begin_run_time) run_time = begin_run_time[0]['run_time'] ''' # 查看设备的开关状态 sql_device_status_num = "SELECT serial_number FROM data_point_tbl WHERE io_point_name=\'%s\';" % (device_name + "_status") res_device_status_num = self._operate_mysql.execute_sql(sql_device_status_num) device_status_num = "c" + str(res_device_status_num[0]['serial_number']) device_status = self._operate_mysql.memoryStorage.get_value([device_status_num]) # 0：开启， 1：关闭 {'c761':'0'} ''' # 查看电流的大小是否超过阈值（0.5A） i_status = 0 # 假设未超过 i_serial = i_serial_number[device_name] sql_i_value = "SELECT %s,%s,%s FROM `%s` ORDER BY times DESC LIMIT 1;" % (i_serial[0].replace('\'', ''), i_serial[1].replace('\'', ''), i_serial[2].replace('\'', ''), table_name) res_i_value = self._operate_mysql.execute_sql(sql_i_value) if len(res_i_value) == 1: for k in res_i_value[0]: if res_i_value[0][k] >= current_threshold: i_status = 1 # 判断设备是否在正常运行 # if device_status[device_status_num] == '0' and i_status == 1: if i_status == 1: run_time = run_time + 1 day_use = day_use + day_electric_use month_use = month_use + month_electric_use use_data[device_name] = [day_electric_use, month_electric_use, run_time] for each in use_data: data = use_data[each] day_energy = data[0] # 日耗能 month_energy = data[1] # 月耗能 run_time = data[2] # 运行时间 # 计算日耗能占比 if day_energy == 0: day_energy_percent = 0 else: day_energy_percent = day_energy / day_use day_energy_percent = str("%.2f" % (day_energy_percent*100)) + "%" # 计算月耗能占比 if month_energy == 0: month_energy_percent = 0 else: month_energy_percent = month_energy / month_use month_energy_percent = str("%.2f" % (month_energy_percent*100)) + "%" # 计算设备的待维保时间 maintenance_cycle_sql = f"SELECT `maintenance_cycle`, `last_maintenance_time` FROM `maintenance_information` WHERE device_name=\'{each}\';" maintenance_cycle_res = self._operate_mysql.execute_sql(maintenance_cycle_sql) maintenance_cycle = maintenance_cycle_res[0]['maintenance_cycle'] last_maintenance_time = maintenance_cycle_res[0]['last_maintenance_time'] next_maintenance_time = (last_maintenance_time + timedelta(days=maintenance_cycle)).strftime("%Y-%m-%d") waiting_days = (parse(next_maintenance_time) - parse(today_time)).days # 数据表更新 update_electric = f"UPDATE maintenance_information SET day_energy={'%.2f' % day_energy}, " \ f"day_energy_percent=\'{day_energy_percent}\', " \ f"month_energy={'%.2f' % month_energy}, " \ f"month_energy_percent=\'{month_energy_percent}\', " \ f"run_time={run_time}, waiting_days={waiting_days} WHERE device_name=\'{each}\';" try: self._operate_mysql.execute_sql(update_electric) except: self._log.error("设备维保信息更新失败！") self._log.error(traceback.format_exc())