123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596 |
- 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"]
- dingding = data["dingding"]
- at_mobiles = data["at_mobiles"]
- 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)
- building = "1101080259"
- start_time = "20230712000000"
- end_time = "20230719000000"
- 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]
- print("同步%s项目数据"%(building))
- project_id = "Pj" + building
- 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")
- #
- print(today,yesterday)
- #获取能耗数据
- datas = get_query_data(yesterday,today)
- if datas:
- # 删除昨天数据
- print("%s,开始删除%s的数据..." % (datetime_now(), yesterday))
- delete_sql = DELETE_SQL % (project_id, yesterday_date, today_date)
- MysqlUtil.update(delete_sql)
- objectids = [i["objectId"] for i in datas]
- energy_cooling = "0"
- energy_heating = "0"
- energy_ac_terminal = "0"
- energy_light = "0"
- sum_data_value = "0"
- for i in datas:
- #总电耗
- if i["objectId"] == "Vo1101080259f2481ca3604644399f1dacb84e20adae":
- sum_data_value = i["ipValue"]
- #冷热源
- if i["objectId"] == "Vo1101080259e6dcf338d9be4bbf826f659b0b5a9ab2":
- energy_cooling = i["ipValue"]
- #空调末端
- if i["objectId"] == "Vo11010802590eaef68d3289452d86d89fbee721e6df":
- energy_ac_terminal = i["ipValue"]
- #照明
- if i["objectId"] == "Vo110108025953894df8d4ae4dbeb7c81ced7df3f83e":
- energy_light = i["ipValue"]
- print(sum_data_value,energy_cooling,energy_ac_terminal,energy_light)
- energy_other = float(sum_data_value) - float(energy_cooling) - float(energy_heating)- float(energy_light) - float(energy_ac_terminal)
- sql = "('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
- project_id, yesterday_date, energy_cooling, energy_heating, energy_ac_terminal, energy_light, energy_other,
- datetime_now(), datetime_now())
- inser_sql = INSERT_SQL % (my_database, "energy_week_day") + sql
- print("%s,开始插入数据..." % datetime_now())
- MysqlUtil.update(inser_sql)
- else:
- print("%s,没有查询到数据...")
- MysqlUtil.close()
|