Cold_DataFiltering/service/eqiupmentMonitoringV2.go

package service

import (
	"database/sql"
	"fmt"
	"strconv"
	"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")

}

type Device struct {
	Tsn       string      //sn
	TDevName  string      //设备名称
	TSaveTime int64       //设备保存时间间隔
	TSensors  [][2]string //设备探头
	TTasks    [][2]string //任务列表
	Tmodel    string
}
type DeviceSensor struct {
	Tsn    string //sn
	TId    string //探头
	TPid   string //设备公司id
	TName  string //设备名称
	Tmodel string //设备型号
}

type Data struct {
	Tt    float64
	Trh   float64
	TTime time.Time
}

var (
	thresholdTt,
	thresholdTrh float64
	recordTime int
)

func init() {
	recordTime = config.Config.GetInt("monitoring.equipmentHour")
}

// []string{"BX200GSE", "MD100", "MD200G", "BX100", "KF100", "MD300G"}
// EquipmentMonitoring2 监控设备v2
func EquipmentMonitoring2() {
	//1.获取所有设备表
	sqlStatement := "select t_sn,t_dev_name,t_model from device where t__state = 1 and t_monitor = 1 and t_online =1 or t_online_s = 1 and t_pid !=2 and (t_model = 'BX200GSE' OR t_model = 'MD100' OR t_model LIKE 'MD200G' OR t_model LIKE 'BX100' OR t_model LIKE 'KF100' OR t_model LIKE 'MD300G') "
	rows, _ := databases.Db.Query(sqlStatement)
	devices := make([]Device, 0)
	var sn, devName, model string
	for rows.Next() {
		rows.Scan(&sn, &devName, &model)
		devices = append(devices, Device{Tsn: sn, TDevName: devName, TSensors: [][2]string{}, TTasks: [][2]string{}, Tmodel: model})
	}

	//fmt.Println("查询得到设备：", devices)
	//fmt.Println("查询得到设备：", len(devices))
	//HandleTsn(devices)
	DataCaps(devices)
	//Forewarn(devices)
}

// 处理sn
func HandleTsn(devices []Device) {

	//查询所有设备探头
	var sensorId, sensorName string
	for i, v := range devices {
		sqlStatement := fmt.Sprintf("select t_id,t_name from device_sensor where t__state = 1 AND t_sn = '%s'", v.Tsn)
		rows, err := databases.Db.Query(sqlStatement)
		if err != nil {
			fmt.Println("查询", v.Tsn, "设备探头出错：", err.Error())
		}
		for rows.Next() {
			rows.Scan(&sensorId, &sensorName)
			// [设备名称,探头id,探头名称]
			v.TSensors = append(v.TSensors, [2]string{sensorId, sensorName})
		}
		devices[i] = v
	}
	fmt.Println("查询得到设备探头：", devices)

	//查询所有保存时间
	var tSaveT int64
	var devName any
	for i := 0; i < len(devices); i++ {
		sqlStatement := fmt.Sprintf("select t_name,t_save_t from device_parameter where t_sn = '%s' order by ID desc,update_time desc limit 1", devices[i].Tsn)
		row := databases.Db.QueryRow(sqlStatement)
		row.Scan(&devName, &tSaveT)
		devices[i].TSaveTime = tSaveT
		devices[i].TDevName = fmt.Sprintf("%v", devName)
		if tSaveT == 0 {
			util.SendWarning(devices[i].Tsn, -1, devices[i].TDevName, "", "设备保存时间未设置！")
			continue
		}
		fmt.Println("设备：", devices[i].Tsn, "保存时间：", devices[i].TSaveTime)
	}

	//查询是否存在执行计划，判断sn 是否为移动设备；
	var now = time.Now()
	var lastTowHours = now.Add(time.Hour * -time.Duration(recordTime))
	var start, end string
	for i, v := range devices {
		sqlStatement := fmt.Sprintf("select t__ut_start,t__ut_end from device_task where t_sn = '%s' and t__ut_end >= '%s'", v.Tsn, lastTowHours.Format(DateFormat))
		rows, err := databases.Db.Query(sqlStatement)

		if err != nil {
			fmt.Println(err)
		}

		//如果存在数据则表示是移动端
		for rows.Next() {
			rows.Scan(&start, &end)
			v.TTasks = append(v.TTasks, [2]string{start, end})
		}
		devices[i] = v
	}
	fmt.Println("查询得到设备任务：", devices)

	//公共的sql模板
	commonsStatement := "SELECT t_t,t_rh,DATE_FORMAT(t_time,'%%Y-%%m-%%d %%H:%%i:%%s') as t_time FROM z_device_data_%s WHERE t_id = '%s' AND t_time BETWEEN '%s' AND '%s' ORDER BY t_time DESC"

	var interval int64
	for _, v := range devices {

		//存在任务
		if len(v.TTasks) != 0 {
			for _, task := range v.TTasks {

				//计算时间差
				s, _ := time.Parse(DateFormat, task[0])
				e, _ := time.Parse(DateFormat, task[1])
				interval = e.Unix() - s.Unix()

				//循环查询各探头
				for _, sensor := range v.TSensors {
					sqlT := fmt.Sprintf(commonsStatement, v.Tsn, sensor[0], task[0], task[1])
					fmt.Println(sqlT)
					rows, err := databases.Db.Query(sqlT)
					if err != nil {
						fmt.Println("SOL查询错误！\t", err.Error())
					}
					handleDeviceData(v, sensor, interval, task[0], task[1], rows)
				}
			}
			continue
		}

		//不存在任务
		interval = now.Unix() - lastTowHours.Unix()
		for _, sensor := range v.TSensors {
			rows, err := databases.Db.Query(fmt.Sprintf(commonsStatement, v.Tsn, sensor[0], lastTowHours.Format(DateFormat), now.Format(DateFormat)))
			if err != nil {
				fmt.Println("SOL查询错误！\t", err.Error())
			}
			handleDeviceData(v, sensor, interval, lastTowHours.Format(DateFormat), now.Format(DateFormat), rows)
		}
	}
}

// 处理数据
func handleDeviceData(d Device, sensor [2]string, interval int64, start, end string, rows *sql.Rows) {

	var sensorId, _ = strconv.ParseInt(sensor[0], 10, 64)
	fmt.Println("设备sn：", d.Tsn, "-", sensorId, " 任务时间：", start, end)
	//读取数据
	datas := make([]Data, 0)
	var t, rh float64
	var tTime string
	for rows.Next() {
		rows.Scan(&t, &rh, &tTime)
		ttime, _ := time.Parse(DateFormat, tTime)
		datas = append(datas, Data{t, rh, ttime})
	}
	fmt.Println("查询得到设备", d.Tsn, "数据：", len(datas), "最少条数：", d.TSaveTime, interval/d.TSaveTime, "条\t设备保存时间：")
	//
	////时间内记录数小于应该保存的记录数，发出预警
	//if d.TSaveTime != 0 {
	//	if int64(len(datas)) < (interval / d.TSaveTime) {
	//		util.SendWarning(d.Tsn, int(sensorId), d.TDevName, sensor[1], fmt.Sprintf("设备[%s]探头[%d]   [%s~%s]超出保存时间范围，缺少数据[%d]条", d.Tsn, sensorId, start, end, interval/d.TSaveTime-int64(len(datas))))
	//	}
	//}

	//处理数据
	var current, next Data
	for i := 0; i < len(datas)-1; i++ {
		current = datas[i]
		next = datas[i+1]

		//不在保存时间范围内
		if d.TSaveTime != 0 {
			interval := current.TTime.Unix() - next.TTime.Unix() - d.TSaveTime
			if interval >= d.TSaveTime {
				util.SendWarning(d.Tsn, int(sensorId), d.TDevName, sensor[1], fmt.Sprintf("设备[%s]探头[%d]   [%s~%s]超出保存时间范围 [%d] 上传时间间隔[%d]，缺少数据[%d]条", d.Tsn, sensorId, next.TTime.Format(DateFormat), current.TTime.Format(DateFormat), interval, d.TSaveTime, interval/d.TSaveTime))
			}
		}

		//温度
		temperatureInterval := current.Tt - next.Tt
		if temperatureInterval > thresholdTemperature {
			util.SendWarning(d.Tsn, int(sensorId), d.TDevName, sensor[1], fmt.Sprintf("设备[%s]探头[%d]   [%s~%s]超出温度变化范围，变化差值[%.2f]", d.Tsn, sensorId, next.TTime.Format(DateFormat), current.TTime.Format(DateFormat), temperatureInterval))
		}

		//湿度
		humidityInterval := current.Trh - next.Trh
		if humidityInterval > thresholdHumidity {
			util.SendWarning(d.Tsn, int(sensorId), d.TDevName, sensor[1], fmt.Sprintf("设备[%s]探头[%d]   [%s~%s]超出湿度变化范围，变化差值[%.2f]", d.Tsn, sensorId, next.TTime.Format(DateFormat), current.TTime.Format(DateFormat), humidityInterval))
		}
	}
}