sonar/data_push_sonar/push_status.py

# -*- coding: UTF-8 -*-
'''
@Project ：jh5sonar_newest 
@File    ：push_status.py
@Author  ：Fang
@Date    ：2024/8/14
'''
import configparser
import json
import os
import time
from datetime import datetime
import pymysql


# 数据格式化 fields 字段名，result 结果集
def format_data(fields, result):
    # 字段数组 ['id', 'name', 'password']
    field = []
    for i in fields:
        field.append(i[0])
    # 返回的数组集合 形式[{'id': 1, 'name': 'admin', 'password': '123456'}]
    res = []
    for iter in result:
        line_data = {}
        for index in range(0, len(field)):
            line_data[field[index]] = iter[index]
        res.append(line_data)
    return res


# ping声呐
def get_ping_result(ip_list):
    # ip_list: 列表：['192.168.1.51','192.168.1.52']
    # 返回值；0或1

    for ip in ip_list:
        # ping左目,-c:尝试次数，-w:等待时间(秒)
        result = os.popen('ping -c 1 -w 1 ' + ip)
        # ping通后，会有标志性字符串："1 received"，返回子串起始位置，否则返回-1
        r1 = result.read().find('1 received')
        # ping不通，返回0
        if r1 < 0:
            return str(0)
    # 都能ping通，返回1
    return str(1)


def get_message_from_db(config):
    message = {}
    sections = config.sections()

    print(path + "/push.conf")
    print(sections)

    if 'device_info' in sections:
        try:
            message['serial_number'] = config.get("device_info", "serial_number")
            message['device_name'] = config.get("device_info", "device_name")
            message['project_name'] = config.get("device_info", "project_name")
        except Exception as e:
            print(e)

    if 'db' in sections:
        try:
            db_host = config.get("db", "db_host")
            db_user = config.get("db", "db_user")
            db_pass = config.get("db", "db_pass")
            db_name = config.get("db", "db_name")
            # 打开数据库连接
            db = pymysql.connect(host=db_host, user=db_user, password=db_pass, database=db_name)

            print(db)

        except Exception as e:
            print(e)

        if 'data_rows' in sections:
            try:
                table_name = config.get("data_rows", "table_name")
                date_column_name = config.get("data_rows", "date_column_name")
                # 使用cursor()方法获取操作游标
                cursor = db.cursor()
                # SQL 查询语句
                # date = time.strftime('%Y-%m-%d')
                # sql = "SELECT count(*) FROM " + table_name + " WHERE DATE_FORMAT(" + date_column_name + " ,'%Y-%m-%d') = '" + date + "';"
                sql = "SELECT count(*) FROM " + table_name + " WHERE " + date_column_name + " >= ( NOW( ) - INTERVAL 24 HOUR );"
                try:
                    # 执行SQL语句
                    cursor.execute(sql)
                    # 获取所有记录列表
                    results = cursor.fetchall()
                    message['data_rows'] = results[0][0]
                except:
                    message['data_rows'] = 0
                    print("Error: unable to fetch data")
            except Exception as e:
                print(e)

        if 'ping' in sections:
            try:
                ip_list = config.get("ping", "ip_list")
                ip_list = ip_list.split(",")
                ping_result = get_ping_result(ip_list)
                message['ping'] = ping_result
            except Exception as e:
                print(e)
        if 'latest_data' in sections:
            try:
                table_name = config.get("latest_data", "table_name")
                column_name_list = config.get("latest_data", "column_name_list")
                column_order_by = config.get("latest_data", "order_by")
                # 使用cursor()方法获取操作游标
                cursor = db.cursor()
                # SQL 查询语句
                date = time.strftime('%Y-%m-%d')
                sql = "SELECT " + column_name_list + " FROM " + table_name + " order by " + column_order_by + " desc limit 1;"
                try:
                    # 执行SQL语句
                    cursor.execute(sql)
                    result = cursor.fetchall()
                    fields = cursor.description
                    res = format_data(fields, result)
                    message['latest_data'] = res[0]
                except:
                    print("Error: unable to fetch data")
            except Exception as e:
                print(e)


        if  message['ping'] == '1':
            message['last_offline_time'] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")


    message_json = json.dumps(message, default=str)
    return message_json


def save_message(db, message_json):
    cursor = db.cursor()
    message_json = json.loads(message_json)
    up_date = time.strftime('%Y-%m-%d')
    serial_number = message_json['serial_number']
    device_name = message_json['device_name']
    project_name = message_json['project_name']
    data_rows = message_json['data_rows']
    latest_data = json.dumps(message_json['latest_data'])
    device_status = message_json['ping']

    if message_json['ping'] == '1':
        # SQL 插入语句
        sql = "REPLACE INTO device_status (project_name, up_date, device_name, serial_number, data_rows, latest_data, device_status) VALUES ('%s','%s','%s', '%s', %s, '%s', %s)" % (
        project_name, up_date, device_name, serial_number, data_rows, latest_data, device_status)
    elif message_json['ping'] == '0':
        sql = f"SELECT last_offline_time FROM device_status " \
              f"WHERE project_name='{project_name}' and device_name='{device_name}' ORDER BY date_time DESC LIMIT 1;"
        cursor.execute(sql)
        result = cursor.fetchall()
        offline_time = result[0][0]
        if offline_time ==None:
            last_offline_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        else:
            last_offline_time = offline_time
        sql = "REPLACE INTO device_status (project_name, up_date, device_name, serial_number, data_rows, latest_data, device_status,last_offline_time) VALUES ('%s','%s','%s', '%s', %s, '%s', '%s', '%s')" % (
        project_name, up_date, device_name, serial_number, data_rows, latest_data, device_status,last_offline_time)


    print(sql)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except Exception as e:
        print(e)
    # 关闭数据库连接
    db.close()


if __name__ == '__main__':
    # db_host = "101.43.128.27"
    # db_user = "sencott"
    # db_pass = "sencott"

    db_host = "127.0.0.1"
    db_user = "root"
    db_pass = "123456"
    db_name = "sencott_device_adminer_db"

    # 打开数据库连接
    db = pymysql.connect(host=db_host, user=db_user, password=db_pass, database=db_name)
    path = os.path.abspath(os.path.dirname(__file__))
    config = configparser.ConfigParser()
    config.read(path + "/push.conf", encoding="utf-8")
    print(path + "/push.conf")
    print(config.sections())
    message_json = get_message_from_db(config)
    save_message(db, message_json)

    '''
    headers = {'Content-Type': 'application/json'}
    r = requests.post("http://localhost/sencott_device_adminer/saveMessage.php", data=message_json, headers=headers)
    print(r.text)
    '''