persagy_transfer_energy/temp.py

import datetime
import json,pymysql
import os
import time
from MyUtils.MysqlUtils import MysqlUtils
from MyUtils.Dingtalk import send_message
from MyUtils.DmprwdUtil import get_query_data
from MyUtils.DateUtils import get_day
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


# #连接hbase
MysqlUtil = MysqlUtils(**mysql)



start_time = "20230724000000"
end_time = "20230726000000"
range_days = get_day(start_time,end_time)

for i in range_days:
    yesterday,today = i[0],i[1]
    yesterday_date = yesterday[0:8]
    today_date = today[0:8]
    time_now = datetime.datetime.fromtimestamp(int(time.time()),
                                               pytz.timezone('Asia/Shanghai')).strftime('%H:%M:%S')
    # 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")
    #

    # 循环项目查询
    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()