persagy_transfer_energy/main.py

import datetime
import json
import time
from MyUtils.MysqlUtils import MysqlUtils
from MyUtils.DmprwdUtil import get_query_data
import pytz

INSERT_SQL = "replace into %s.%s(project_id,date,energy_cooling,energy_heating,energy_ac_terminal,energy_light,energy_others,create_time,update_time) values "
DELETE_SQL = "DELETE FROM `energy_week_day` WHERE `project_id` = '%s' AND `date` >= '%s' AND `date` < '%s' "
SELETE_COUNTLASTDATA_SQL = "SELECT count(*) FROM `energy_week_day` WHERE `project_id` = '%s' AND `date` >= '%s' AND `date` < '%s'"
SELETE_SUMLASTDATA_SQL = "SELECT SUM(energy_ac_terminal)+SUM(energy_heating)+SUM(energy_cooling)+SUM(energy_light)+sum(energy_others) as last_data FROM `energy_week_day` WHERE `project_id` = '%s' AND `date` >= '%s' AND `date` < '%s'"

with open("config.json", "r") as f:
    data = json.load(f)
    mysql = data["mysql"]
    my_database = mysql["database"]
    buildings = data["buildings"]

def datetime_now():
    # datetime_now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
    #容器时间
    # tz = pytz.timezone('Asia/Shanghai')  # 东八区
    datetime_now = datetime.datetime.fromtimestamp(int(time.time()),
                                    pytz.timezone('Asia/Shanghai')).strftime('%Y-%m-%d %H:%M:%S')
    return datetime_now


print("%s 等待下次执行..."%datetime_now())
while True:
    time_now = datetime.datetime.fromtimestamp(int(time.time()),
                                               pytz.timezone('Asia/Shanghai')).strftime('%H:%M:%S')
    am_set_time = "08:00:00"
    pm_set_time = "22:00:00"
    if time_now == am_set_time or time_now == pm_set_time:  # 此处设置每天定时的时间
        today = datetime.date.today().strftime("%Y%m%d") + "000000"
        yesterday = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y%m%d") + "000000"
        today_date = datetime.date.today().strftime("%Y%m%d")
        yesterday_date = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y%m%d")
        # #连接mysql
        MysqlUtil = MysqlUtils(**mysql)
        #循环项目查询
        for building in buildings:
            project_id = "Pj"+building["building"]
            groupcode = building["groupcode"]
            energyid = building["energyid"]
            rwdid = building["rwdid"]
            energy_total = rwdid["energy_total"]
            energy_light = rwdid["energy_light"]
            energy_ac_termial = rwdid["energy_ac_termial"]
            energy_cooling = rwdid["energy_cooling"]
            energy_heating = rwdid["energy_heating"]
            print("%s 开始查询%s项目数据"%(datetime_now(),project_id))
            # 获取能耗数据
            datas = get_query_data(yesterday, today,groupcode, project_id,energy_total,energy_light,energy_cooling,energy_heating,energy_ac_termial)
            if datas:
                # 删除昨天数据
                print("%s,开始删除%s项目%s的数据..." % (datetime_now(),project_id, yesterday))
                delete_sql = DELETE_SQL % (project_id, yesterday_date, today_date)
                MysqlUtil.update(delete_sql)
                objectids = [i["objectId"] for i in datas]
                print(objectids)
                sum_data_value_data = "0"
                energy_heating_data = "0"
                energy_ac_terminal_data = "0"
                energy_light_data = "0"
                energy_cooling_data = "0"
                for i in datas:
                    print(i)
                    # 总电耗
                    if i["objectId"] == energy_total:
                        sum_data_value_data = i["ipValue"]
                    # 冷
                    if i["objectId"] == energy_cooling:
                        energy_cooling_data = i["ipValue"]
                    # 热
                    if i["objectId"] == energy_heating:
                        energy_heating_data = i["ipValue"]
                    # 空调末端
                    if i["objectId"] == energy_ac_termial:
                        energy_ac_terminal_data = i["ipValue"]
                    # 照明
                    if i["objectId"] == energy_light:
                        energy_light_data = i["ipValue"]
                energy_other = float(sum_data_value_data) - float(energy_cooling_data) - float(energy_heating_data) - float(energy_light_data) - float(
                    energy_ac_terminal_data)
                sql = "('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
                    project_id, yesterday_date, energy_cooling_data, energy_heating_data, energy_ac_terminal_data, energy_light_data, energy_other,
                    datetime_now(), datetime_now())
                inser_sql = INSERT_SQL % (my_database, "energy_week_day") + sql
                print("%s,开始插入%s项目%s数据..." % (datetime_now(),project_id,yesterday))
                MysqlUtil.update(inser_sql)
            else:
                print("%s,没有查询到%s项目%s的数据..."%(datetime_now(),project_id,yesterday))

        MysqlUtil.close()
        print("%s 完成%s的数据插入"%(datetime_now(),yesterday))
        time.sleep(2)