jikong_backend_code/005_H570/jikong_script/update_maintenance_info.py

#!/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())