123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167 |
- import json
- from MyUtils.Dingtalk import send_message_markdown
- import datetime
- import time
- import pymysql
- import pytz
- import os
- SELECT_BUILDING_SQL = "SELECT zj_building_id,sg_building_id FROM sagacloud_setup.zj_sync_config_building"
- SELECT_STATE_SQL = "SELECT room_id from sagacloud_setup.zj_t_building_rooms where `type` = 1"
- SELECT_FC_SQL = "SELECT building_id,room_name,room_id from zj_fc.t_building_rooms"
- SELECT_SPACE_SQL = "SELECT building_id,local_name FROM sagacloud_object.object_data_space"
- TRUNCATE_BUILDING_ROOMS_SQL = "truncate table sagacloud_setup.zj_sync_building_rooms"
- INSERT_BUILDING_ROOMS_SQL = "INSERT into sagacloud_setup.zj_sync_building_rooms(building_id,building_name,room_name,floor_id,floor_name,`state`,`type`,create_time) values"
- SELECT_DIFF_FC_SQL="SELECT building_name,floor_id,floor_name,`state`,`type` from zj_fc.t_building_rooms WHERE building_id='%s' and room_name='%s'"
- def datetime_now():
-
-
-
- datetime_now = datetime.datetime.fromtimestamp(int(time.time()),
- pytz.timezone('Asia/Shanghai')).strftime('%Y-%m-%d %H:%M:%S')
- return datetime_now
- def insert_mysql(sqls):
- print("%s 开始往mysql插入数据..." % datetime_now())
- for i in range(0, len(sqls), 1000):
- sqlranges = sqls[i:i + 1000]
- sqlranges = INSERT_BUILDING_ROOMS_SQL + ",".join(sqlranges)
- mysql_cur.execute(sqlranges)
- conn.commit()
- print("%s mysql数据插入成功,合计%s条..." % (datetime_now(), len(sqls)))
- mysql = {
- "host": os.getenv("host"),
- "port": os.getenv("port"),
- "user": os.getenv("user"),
- "passwd": os.getenv("passwd"),
- "database": os.getenv("database")
- }
- database = os.getenv("database")
- dingding = os.getenv("dingding")
- while True:
- print("%s 等待开始检查..."%datetime_now())
- time_now = datetime.datetime.fromtimestamp(int(time.time()),
- pytz.timezone('Asia/Shanghai')).strftime('%H:%M:%S')
-
- am_set_time = "05:00:00"
- if time_now == am_set_time:
-
- messages = []
-
- FC_NAMELIST_DICT = {}
-
- SPACE_NAMELIST_DICT={}
-
- conn = pymysql.connect(**mysql)
- mysql_cur = conn.cursor()
-
- mysql_cur.execute(SELECT_BUILDING_SQL)
- buildinglist = mysql_cur.fetchall()
-
- mysql_cur.execute(SELECT_STATE_SQL)
- roomid_list = mysql_cur.fetchall()
- roomid_list = [i[0] for i in roomid_list]
-
- print("%s 获取zj_fc空间名称..."%datetime_now())
- mysql_cur.execute(SELECT_FC_SQL)
- fc_namelist = mysql_cur.fetchall()
- for building in buildinglist:
- if building[0] and building[1] != None:
- for fc_name in fc_namelist:
-
- if fc_name[2] in roomid_list:
- if fc_name[0] == building[0]:
- FC_NAMELIST_DICT[building[1]+"_"+fc_name[1]] = building[0]+"_"+fc_name[1]
-
- else:
- message = (" - **类型:** 建筑关系未对应 \n" + " - **建筑ID:** <font color=#FF0000>%s(%s)</font> \n")% (building[0],building[1])
- print(message)
- messages.append(message)
- messages.append("___")
-
- print("%s 获取space空间名称..."%datetime_now())
- mysql_cur.execute(SELECT_SPACE_SQL)
- space_namelist = mysql_cur.fetchall()
- for space in space_namelist:
- SPACE_NAMELIST_DICT[space[0]+"_"+space[1]] = 1
-
-
-
- sqls = []
- print("%s 开始对比差异数据..."%datetime_now())
- diff_namelist = set(FC_NAMELIST_DICT) - set(SPACE_NAMELIST_DICT)
- if diff_namelist != set():
- for diff_name in diff_namelist:
- diff_building_rooms = FC_NAMELIST_DICT[diff_name]
- diff_building_id = str(diff_building_rooms).split("_")[0]
- diff_room_name = str(diff_building_rooms).split("_")[1]
- mysql_cur.execute(SELECT_DIFF_FC_SQL%(diff_building_id,diff_room_name))
- diff_data = mysql_cur.fetchall()[0]
- building_name = diff_data[0]
- floor_id = diff_data[1]
- floor_name = diff_data[2]
- state = diff_data[3]
- type = diff_data[4]
- sqlline = "('%s','%s','%s','%s','%s','%s','%s','%s')"%(diff_building_id,building_name,diff_room_name,floor_id,floor_name,state,type,datetime_now())
- sqls.append(sqlline)
-
-
-
- print("%s 差异数据共计%s条..."%(datetime_now(),len(diff_namelist)))
- message = (" - **类型:** 空间名称未对应 \n" + " - **数量:** <font color=#FF0000>%s</font> \n") % (len(diff_namelist))
- messages.append(message)
- messages.append("___")
-
- print("%s 清空MySQL表数据..."%datetime_now())
- mysql_cur.execute(TRUNCATE_BUILDING_ROOMS_SQL)
- conn.commit()
-
- insert_mysql(sqls)
-
- mysql_cur.close()
- conn.close()
- if messages:
- print("%s 发送钉钉报警"%datetime_now())
- messages.append("<font color=#0080FF>详情查看MySQL数据库sagacloud_setup里的zj_sync_building_rooms表</font>")
- messages_info = "\n".join(messages)
-
- title = "## **【之江实验室】空间名称报警** "
- messages_info = title + "\n" +"___"+"\n"+ messages_info
- send_message_markdown(title, messages_info, dingding)
|