#!/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) this_time = time.time() if this_time - self.run_time > 60: self.run_time = this_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 = "SELECT device_name,serial_number FROM data_point_tbl WHERE unit=\'%s\';" % (i_unit) 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"])) # 更新维保信息 v_unit = "kWh" # 耗能单位 find_electric_device = "SELECT serial_number, device_name FROM data_point_tbl WHERE station_name=\'sct003\' AND unit=\'%s\';" % (v_unit) all_electric_device = self._operate_mysql.execute_sql(find_electric_device) for each_device in all_electric_device: v_serial_number = "c" + str(each_device['serial_number']) device_name = each_device['device_name'] if device_name != "60kpcs": table_name = "table_" + device_name # 日耗能 # [{'t': '2021-05-28', 'c159': 6.0}, {'t': '2021-05-27', 'c159': 4.2}] day_electric = "SELECT DATE_FORMAT(times, '%%Y-%%m-%%d') AS t, MAX(%s) AS %s FROM %s GROUP BY t ORDER BY t DESC LIMIT 2;" % (v_serial_number, v_serial_number, table_name) 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 = "SELECT DATE_FORMAT(times, '%%Y-%%m') AS t, MAX(%s) AS %s FROM %s GROUP BY t ORDER BY t DESC LIMIT 2;" % (v_serial_number, v_serial_number, table_name) 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 = "SELECT run_time FROM maintenance_information WHERE device_name=\'%s\';" % (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: 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 = "SELECT maintenance_cycle, last_maintenance_time FROM maintenance_information WHERE device_name=\'%s\';" % (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 = "UPDATE maintenance_information SET day_energy=%s, day_energy_percent=\'%s\', month_energy=%s, month_energy_percent=\'%s\', run_time=%s, waiting_days=%s WHERE device_name=\'%s\';" % ("%.2f" % day_energy, day_energy_percent, "%.2f" % month_energy, month_energy_percent, run_time, waiting_days, each) try: self._operate_mysql.execute_sql(update_electric) except: self._log.error("设备维保信息更新失败！") self._log.error(traceback.format_exc())