12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091 |
- 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
- 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
- building = "1101080259"
- def transfer_energy():
- print("同步%s项目数据" % (building))
- while True:
- project_id = "Pj" + building
- 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)
- #获取能耗数据
- 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]
- print(objectids)
- energy_cooling = "0"
- energy_heating = "0"
- energy_ac_terminal = "0"
- energy_light = "0"
- sum_data_value = "0"
- for i in datas:
- print(i)
- #总电耗
- 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"]
- 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()
- if __name__ == '__main__':
- transfer_energy()
|