Cold_DataFiltering/service/eqiupmentMonitoring.go.temp

package service

import (
	"database/sql"
	"fmt"
	"math"
	"strconv"
	"strings"
	"time"
	"ydMonitoring/config"
	"ydMonitoring/databases"
	"ydMonitoring/util"
)

var (
	thresholdTemperature,
	thresholdHumidity float64
	// DateFormat 格式化时间字符串
	DateFormat = "2006-01-02 15:04:05"
	//EquipmentMonitoringTime uint64
	errRangeTime int
)

func init() {
	thresholdTemperature = config.Config.GetFloat64("monitoring.temperature")
	thresholdHumidity = config.Config.GetFloat64("monitoring.humidity")
	errRangeTime = config.Config.GetInt("monitoring.errRangeTime")
	//EquipmentMonitoringTime = config.Config.GetUint64("monitoring.equipmentMonitoringTime")

}

// EquipmentMonitoring
// 监控设备总函数
func EquipmentMonitoring() {
	db := databases.Db

	var (
		//sqlStatement string
		err  error
		rows *sql.Rows
	)

	//设备YD监控
	sqlStatement := "SELECT t_sn,t_dev_name FROM device WHERE (t__site = '' OR t__site IS NULL)  AND t__state = 1 AND t_monitor = 1"
	fmt.Printf("YD设备查询SQL：%s\n", sqlStatement)
	rows, err = db.Query(sqlStatement)
	if err != nil {
		fmt.Println("数据查询失败：", err.Error())
	}
	handlerData(rows)
}

// handlerData
// 处理数据库查询响应数据
func handlerData(rows *sql.Rows) {
	db := databases.Db

	//设备配置表，获取备份时间
	sql := "SELECT t_sn,t_save_time FROM device_parameter where ( t__state = 1 ) OR ( t__state = 2 and t__send_state = 1 ) order by ID asc"
	tSnRows, err := db.Query(sql)
	fmt.Printf("查询设备表设备备份时间SQL：%s\n", sql)
	if err != nil {
		fmt.Println("数据查询失败：", err.Error())
	}
	var tSnThresholdTimeMap = make(map[string]int)
	for tSnRows.Next() {
		var tSn string
		var saveTime int
		tSnRows.Scan(&tSn, &saveTime)
		tSnThresholdTimeMap[tSn] = saveTime
	}

	fmt.Printf("查询到%d条记录\n", len(tSnThresholdTimeMap))

	//获取所有tsn，并将其处理判断是否异常
	for rows.Next() {
		var tSn, tDevName string
		rows.Scan(&tSn, &tDevName)
		// z_devicedata_kf861551053476933
		tableName := fmt.Sprintf("z_device_data_%s", tSn)

		if tableName == "z_device_data_202335objgv2pz1" {
			fmt.Println()
		}

		//需要查出所有设备的t_id,对t_id对应的记录进行对比
		selectTId := fmt.Sprintf("SELECT t_id FROM %s GROUP BY t_id", tableName)
		fmt.Printf("查询%s表中t_id进行分类执行SQL：\n", tSn)
		tIdRows, err := db.Query(selectTId)
		if err != nil {
			fmt.Printf(err.Error())
			if strings.Contains(err.Error(), "Error 1146 (42S02): Table") {
				continue
			}
		}
		var tIds = make([]string, 0)
		for tIdRows.Next() {
			var tId string
			tIdRows.Scan(&tId)
			fmt.Println(tId)
			tIds = append(tIds, tId)
		}
		fmt.Printf("执行SQL后查询到%d条数据\n", len(tIds))

		//判断是否有时间间隔，对于表tsn来说
		thresholdTime, ok := tSnThresholdTimeMap[tSn]
		if !ok {
			util.SendWarning(tSn, -1, tDevName, "", fmt.Sprintf("[设备名称：%s|t_sn：%s]\t不存在配置时间", tDevName, tSn))
			continue
		}

		for _, tId := range tIds {
			//如果t_id为空则跳出当前循环
			if tId == "" {
				continue
			}

			t, _ := strconv.ParseInt(tId, 10, 64)
			id := int(t)

			sql := fmt.Sprintf("SELECT t_t,t_rh,create_time FROM %s where create_time >= DATE_SUB(NOW(),INTERVAL 1 DAY) AND t_id = %s ORDER BY create_time DESC LIMIT 10", tableName, tId)
			fmt.Printf("查询设备最近一天得前十条记录，执行SQL：%s\n", sql)
			tSnRows, err = db.Query(sql)
			if err != nil {
				util.SendWarning(tSn, -1, tDevName, "", fmt.Sprintf("[设备名称：%s|t_sn：%s]\t不存在配置时间", tDevName, tSn))
			}
			tTs := make([]float64, 0)
			tRhs := make([]float64, 0)
			createTimes := make([]string, 0)
			//获取sn上传信息
			for tSnRows.Next() {
				var (
					tT,
					tRh float64
					createTime string
				)
				tSnRows.Scan(&tT, &tRh, &createTime)
				tTs = append(tTs, tT)
				tRhs = append(tRhs, tRh)
				createTimes = append(createTimes, createTime)
			}

			fmt.Printf("查询到%d条记录\n", len(tTs))

			//如果不存在上传记录则跳出本次循环
			if len(createTimes) == 0 || len(tTs) == 0 || len(tRhs) == 0 {
				fmt.Printf("%s设备执行SQL后不存在记录，设备已停用\n", tableName)
				util.SendWarning(tSn, -1, tDevName, "", fmt.Sprintf("【设备名称：%s|t_sn：%s|t_id：%s】\t缺少上传记录，该设备下没有数据记录，时间间隔%d", tDevName, tSn, tId, thresholdTime))
				continue
			}

			//处理时间
			//情况1：查询出来的最近时间节点对于现在的时间，是否在其中还存在一个记录时间段
			currentTime := time.Now()
			firstTime, _ := time.Parse(DateFormat, createTimes[0])
			if int((currentTime.Unix()-firstTime.Unix())/1000) > thresholdTime {
				fmt.Printf("当前时间：%s 最近记录时间：%s，时间间隔：%d", currentTime.Format(DateFormat), createTimes[0], thresholdTime)
				util.SendWarning(tSn, -1, tDevName, "", fmt.Sprintf("【设备名称：%s|t_sn：%s|t_id：%s】\t缺少上传记录，对于当前时间%s，最近记录时间%s，时间间隔%d", tDevName, tSn, tId, currentTime.Format(DateFormat), createTimes[0], thresholdTime))
				continue
			}

			// 处理数据
			for i := 0; i < len(tTs); i++ {

				//情况2：设备记录时间段不在阈值内，存在上传时间误差
				if i == len(createTimes)-1 {
					break
				}
				previous, _ := time.Parse(DateFormat, createTimes[i])
				next, _ := time.Parse(DateFormat, createTimes[i+1])
				previous = previous.Add(time.Minute * time.Duration(-(thresholdTime / 60)))

				//判断时分是否一致，若不一致则存在误差
				if previous.Day() != next.Day() || previous.Hour() != next.Hour() || previous.Minute() != next.Minute() {
					//误差再1分钟内可接受
					if int(math.Abs((float64(previous.Unix()-next.Unix()))/1000)) > errRangeTime {
						fmt.Printf("当前%s，前条%s\n", next.Format(DateFormat), previous.Format(DateFormat))
						util.SendWarning(tSn, id, tDevName, "", fmt.Sprintf("【设备名称：%s|t_sn：%s|t_id：%s】\t数据上传与配置时间不符合，时间为：[%s]，[%s]，时间阀值为：%d秒", tDevName, tSn, tId, createTimes[i+1], createTimes[i], thresholdTime))
						continue
					}
				}
				//处理温度
				previousTemperature := tTs[i]
				nextTemperature := tTs[i+1]
				if math.Abs(previousTemperature-nextTemperature) > thresholdTemperature {
					util.SendWarning(tSn, id, tDevName, "", fmt.Sprintf("【设备名称：%s|t_sn：%s|t_id：%s】\t温度变化超出阈值警告，当前温度：%.2f 上一次温度：%.2f 阈值温度：%.2f，温差为：%.2f\t出现时间为：%s", tDevName, tSn, tId, nextTemperature, previousTemperature, thresholdTemperature, math.Abs(previousTemperature-nextTemperature), createTimes[i]))
					continue
				}

				//处理湿度
				previousHumidity := tRhs[i]
				nextHumidity := tRhs[i+1]
				if math.Abs(previousHumidity-nextHumidity) > thresholdHumidity {
					util.SendWarning(tSn, id, tDevName, "", fmt.Sprintf("【设备名称：%s|t_sn：%s|t_id：%s】\t湿度变化超出阈值警告，当前湿度：%.2f，前一次湿度：%.2f，温度阀值为：%.2f，湿度差：%.2f\t出现时间：%s", tDevName, tSn, tId, nextHumidity, previousHumidity, thresholdHumidity, math.Abs(previousTemperature-nextHumidity), createTimes[i]))
					continue
				}
			}
		}
	}

}