check_meetingid/main.py

import json
from MyUtils.Dingtalk import send_message_markdown
import datetime
import time
import pymysql
import pytz,os

SELECT_SQL = "SELECT sms.space_id, m2.id, m2. NAME FROM meeting m2 " \
             "LEFT JOIN space_meet_set sms ON sms.meeting_id = m2.id WHERE m2.id IN" \
             " ( SELECT id FROM meeting m WHERE classification = %s AND m.building_id = '%s' AND m. STATUS = 1 );"

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_id = "d89a6d4c73094f9f82eafb5d071e0934"

classifications = {"1":"公共会议室","0":"租区会议室"}

# # 读取配置文件信息
# with open("config.json", "r") as f:
#     data = json.load(f)
#     mysql = data["mysql"]
#     database = mysql["database"]
#     dingding = data["dingding"]

mysql = {
    "host": os.getenv("host"),
    "port": int(os.getenv("port")),
    "user": os.getenv("user"),
    "passwd": os.getenv("passwd"),
    "database": os.getenv("database")
}
dingding = os.getenv("dingding")

print("%s 等待开始检查..." % datetime_now())
while True:

    time_now = datetime.datetime.fromtimestamp(int(time.time()),
                                           pytz.timezone('Asia/Shanghai')).strftime('%H:%M:%S')
    # time_now = time.strftime("%H:%M:%S", time.localtime())  # 刷新
    am_set_time = "11:00:00"
    pm_set_time = "16:00:00"
    if time_now == am_set_time or time_now == pm_set_time:  # 此处设置每天定时的时间
        #钉钉推送消息
        messages = []

        # 连接mysql
        conn = pymysql.connect(**mysql)
        mysql_cur = conn.cursor()

        for classification in classifications:
            name = classifications[classification]
            print("%s,四川大厦，开始检查%s数据"%(datetime_now(),name))
            # 获取值
            mysql_cur.execute(SELECT_SQL%(classification,building_id))
            datas = mysql_cur.fetchall()
            if datas:
                null_sum = [i for i in datas if i[0] == None]
                if len(null_sum) != 0:
                    print("%s,四川大厦, %s: 总共%s个, 有%s个未绑定空间"%(datetime_now(),name,len(datas),len(null_sum)))
                    message = (" - **类型:** %s \n" + "- **总数量:** %s \n"+ " - **未绑定空间数量:**  <font color=#FF0000>%s</font> \n") % (name,len(datas),len(null_sum))
                    messages.append(message)
                    messages.append("___")
                else:
                    print("%s,四川大厦, 没有异常数据" % (datetime_now()))
            else:
                print("%s,四川大厦, 没有查询到%s数据"%(datetime_now(),name))

        # 关闭数据库
        mysql_cur.close()
        conn.close()
        if messages:
            print("%s 发送钉钉报警" % datetime_now())
            messages.append("<font color=#0080FF>详情查看MySQL数据库sagacloud_setup里的space_meet_set表</font>")
            messages_info = "\n".join(messages)
            # 如果有报警，发送钉钉
            title = "## **【四川大厦】会议室未绑定空间报警** "
            messages_info = title + "\n" + "___" + "\n" + messages_info
            send_message_markdown(title, messages_info, dingding)
        time.sleep(2)