import logging
from configparser import ConfigParser
import pymysql.cursors
from pymysql.constants import CLIENT
import ping3
from datetime import datetime

# 读取配置文件
conf = ConfigParser()
conf.read("setting.ini", encoding='utf-8')
# log日志
logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')


# 数据库连接
def mysql():
    mydb = pymysql.connect(
        host=conf.get("mysql_settings", "host"),
        port=int(conf.get("mysql_settings", "port")),
        user=conf.get("mysql_settings", "user"),
        password=conf.get("mysql_settings", "password"),
        database='device_info',
        cursorclass=pymysql.cursors.DictCursor,
        client_flag=CLIENT.MULTI_STATEMENTS
    )
    return mydb


# 定时修改设备离线状态
def update_Off():
    mydb = mysql()
    mydb.connect()
    cursor = mydb.cursor()
    try:
        sql = "UPDATE device_info SET current_state =3 where DATE_SUB(NOW(), INTERVAL 2 HOUR) > normal_update_time and current_state = 1 and (device_class = 2004 OR device_class = 2005 OR device_class = 1001 OR device_class = 2001);"\
                  "UPDATE device_info SET current_state =3 where DATE_SUB(NOW(), INTERVAL 25 HOUR) > normal_update_time and current_state = 1 and (device_class = 2002 OR device_class = 2003);" \
                  "UPDATE device_info SET current_state =3 where DATE_SUB(NOW(), INTERVAL 2 HOUR) > normal_update_time and current_state = 1 and device_type = 1111;" \
                  "UPDATE device_info SET current_state =3 where DATE_SUB(NOW(), INTERVAL 9 MINUTE) > normal_update_time and current_state = 1 and device_class = 2006;"
        insert = cursor.execute(sql)
        mydb.commit()
    except Exception as e:
        logging.debug('update_Off_exception:%s'%e)
    finally:
        cursor.close()  # 关闭游标
        mydb.close()  # 再关闭数据库连接


# 视频设备更新在线时间
def camera_normal_update_time():
    mydb = mysql()
    mydb.connect()
    cursor = mydb.cursor()
    try:
        # 在线IP地址
        ips = []
        ips_on = []
        camera_ip_sql = "SELECT camera_ip FROM device_info WHERE device_classify = '1'"
        cursor.execute(camera_ip_sql)
        camera_ip_cur_data = cursor.fetchall()
        for camera_ip in camera_ip_cur_data:
            # ping
            response = ping3.ping(camera_ip['camera_ip'], timeout=1)
            # 如果通证明在线
            if response:
                ips_on.append(camera_ip['camera_ip'])
            else:
                ips.append(camera_ip['camera_ip'])
        if len(ips) > 0:
            # 创建日期
            creation_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            sql = "UPDATE device_info SET normal_update_time = '" + creation_date + "',current_state = 3  WHERE camera_ip IN (%s)" % ', '.join(['%s'] * len(ips))
            print(sql)
            insert = cursor.execute(sql,ips)
            print(insert)
            mydb.commit()
        if len(ips_on) > 0:
            # 创建日期
            creation_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            sql = "UPDATE device_info SET normal_update_time = '" + creation_date + "',current_state = 1  WHERE camera_ip IN (%s)" % ', '.join(['%s'] * len(ips_on))
            print(sql)
            insert = cursor.execute(sql,ips_on)
            print(insert)
            mydb.commit()
    except Exception as e:
        logging.debug('camera_normal_update_time:%s'%e)
    finally:
        cursor.close()  # 关闭游标
        mydb.close()  # 再关闭数据库连接