jikong_backend_code/001_H511/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)
            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())