temp.py 4.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596
  1. import datetime
  2. import json,pymysql
  3. import os
  4. import time
  5. from MyUtils.MysqlUtils import MysqlUtils
  6. from MyUtils.Dingtalk import send_message
  7. from MyUtils.DmprwdUtil import get_query_data
  8. from MyUtils.DateUtils import get_day
  9. import pytz
  10. 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 "
  11. DELETE_SQL = "DELETE FROM `energy_week_day` WHERE `project_id` = '%s' AND `date` >= '%s' AND `date` < '%s' "
  12. SELETE_COUNTLASTDATA_SQL = "SELECT count(*) FROM `energy_week_day` WHERE `project_id` = '%s' AND `date` >= '%s' AND `date` < '%s'"
  13. 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'"
  14. with open("config.json", "r") as f:
  15. data = json.load(f)
  16. mysql = data["mysql"]
  17. my_database = mysql["database"]
  18. dingding = data["dingding"]
  19. at_mobiles = data["at_mobiles"]
  20. def datetime_now():
  21. # datetime_now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
  22. #容器时间
  23. # tz = pytz.timezone('Asia/Shanghai') # 东八区
  24. datetime_now = datetime.datetime.fromtimestamp(int(time.time()),
  25. pytz.timezone('Asia/Shanghai')).strftime('%Y-%m-%d %H:%M:%S')
  26. return datetime_now
  27. # #连接hbase
  28. MysqlUtil = MysqlUtils(**mysql)
  29. building = "1101080259"
  30. start_time = "20230712000000"
  31. end_time = "20230719000000"
  32. range_days = get_day(start_time,end_time)
  33. for i in range_days:
  34. yesterday,today = i[0],i[1]
  35. yesterday_date = yesterday[0:8]
  36. today_date = today[0:8]
  37. print("同步%s项目数据"%(building))
  38. project_id = "Pj" + building
  39. time_now = datetime.datetime.fromtimestamp(int(time.time()),
  40. pytz.timezone('Asia/Shanghai')).strftime('%H:%M:%S')
  41. # today = datetime.date.today().strftime("%Y%m%d")+"000000"
  42. # yesterday = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y%m%d")+"000000"
  43. # today_date = datetime.date.today().strftime("%Y%m%d")
  44. # yesterday_date = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y%m%d")
  45. #
  46. print(today,yesterday)
  47. #获取能耗数据
  48. datas = get_query_data(yesterday,today)
  49. if datas:
  50. # 删除昨天数据
  51. print("%s,开始删除%s的数据..." % (datetime_now(), yesterday))
  52. delete_sql = DELETE_SQL % (project_id, yesterday_date, today_date)
  53. MysqlUtil.update(delete_sql)
  54. objectids = [i["objectId"] for i in datas]
  55. energy_cooling = "0"
  56. energy_heating = "0"
  57. energy_ac_terminal = "0"
  58. energy_light = "0"
  59. sum_data_value = "0"
  60. for i in datas:
  61. #总电耗
  62. if i["objectId"] == "Vo1101080259f2481ca3604644399f1dacb84e20adae":
  63. sum_data_value = i["ipValue"]
  64. #冷热源
  65. if i["objectId"] == "Vo1101080259e6dcf338d9be4bbf826f659b0b5a9ab2":
  66. energy_cooling = i["ipValue"]
  67. #空调末端
  68. if i["objectId"] == "Vo11010802590eaef68d3289452d86d89fbee721e6df":
  69. energy_ac_terminal = i["ipValue"]
  70. #照明
  71. if i["objectId"] == "Vo110108025953894df8d4ae4dbeb7c81ced7df3f83e":
  72. energy_light = i["ipValue"]
  73. print(sum_data_value,energy_cooling,energy_ac_terminal,energy_light)
  74. energy_other = float(sum_data_value) - float(energy_cooling) - float(energy_heating)- float(energy_light) - float(energy_ac_terminal)
  75. sql = "('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
  76. project_id, yesterday_date, energy_cooling, energy_heating, energy_ac_terminal, energy_light, energy_other,
  77. datetime_now(), datetime_now())
  78. inser_sql = INSERT_SQL % (my_database, "energy_week_day") + sql
  79. print("%s,开始插入数据..." % datetime_now())
  80. MysqlUtil.update(inser_sql)
  81. else:
  82. print("%s,没有查询到数据...")
  83. MysqlUtil.close()