Cold_DataFiltering/service/test.go

package service

import (
	"database/sql"
	"fmt"
	"log"
	"math/rand"
	"strconv"
	"time"
	"ydMonitoring/databases"
)

// DeviceData 两两比较，如果大，就插入一条
// 继续下一个两辆比较。
// 然后多次循环，直到没有需要插入的
type DeviceData struct {
	T_Id        string         `json:"t_id"`
	T_Sp        string         `json:"t_sp"`
	T_Time      string         `json:"t_time"`
	T_T         float64        `json:"t_t"`
	T_Rh        float64        `json:"t_rh"`
	T_Site      sql.NullString `json:"t_site"`
	Create_time string         `json:"create_time"`
}

func TestService() {
	var t_save_t string    //系统设置间隔时间
	var t_warn string      // 报警间隔时间
	var t_tlower float64   // 低温下限
	var t_tupper float64   // 高温上限
	var t_r_hlower float64 // 湿度下限
	var t_r_hupper float64 // 湿度上限
	sn := "2024332675927850"
	t_id := "1"
	startTime := "2024-09-30 00:00:00"
	endTime := "2024-10-09 10:11:00"
	sql := fmt.Sprintf("SELECT * FROM z_device_data_%s WHERE t_id=%s and  t_time  BETWEEN '%s' AND '%s';", sn, t_id, startTime, endTime)
	fmt.Println(sql)
	devices := make([]DeviceData, 0)
	rows, err := databases.Db.Query(sql)
	if err != nil {
		log.Fatalf("查询失败 %v", err)
	}
	defer rows.Close()
	// 遍历查询结果
	for rows.Next() {
		var device DeviceData
		err := rows.Scan(&device.T_Id, &device.T_Sp, &device.T_Time, &device.T_T, &device.T_Rh, &device.T_Site, &device.Create_time)
		if err != nil {
			log.Fatalf("扫描失败 %v", err)
		}
		devices = append(devices, device)
	}
	// 检查遍历过程中是否有错误发生
	if err := rows.Err(); err != nil {
		log.Fatalf("遍历错误: %v", err)
	}
	//fmt.Println(devices)
	sqls := fmt.Sprintf("SELECT t_save_t,t_warn FROM device_parameter WHERE t_sn='%s' ORDER BY update_time DESC LIMIT 1", sn)
	deviceSensorParameter := fmt.Sprintf("SELECT t__tlower,t__tupper,t__r_hlower,t__r_hupper FROM device_sensor_parameter WHERE  t_sn='%s' and t__state=1 and t_id = '%s' ORDER BY update_time DESC LIMIT 1", sn, t_id)
	databases.Db.QueryRow(sqls).Scan(&t_save_t, &t_warn)
	databases.Db.QueryRow(deviceSensorParameter).Scan(&t_tlower, &t_tupper, &t_r_hlower, &t_r_hupper)
	//fmt.Println("保存间隔：", t_save_t, "报警间隔：", t_warn, "温湿度上下限：", t_tlower, t_tupper, t_r_hlower, t_r_hupper)
	adjustData(devices, t_save_t, t_warn, sn, t_tlower, t_tupper, t_r_hlower, t_r_hupper)
}

func adjustData(devices []DeviceData, t_save_t, t_warn, sn string, t_tlower, t_tupper, t_r_hlower, t_r_hupper float64) {
	timeFormat := "2006-01-02 15:04:05"
	floatSave, _ := strconv.ParseFloat(t_save_t, 64) // 获取系统设置时间
	floatWarn, _ := strconv.ParseFloat(t_warn, 64)   // 获取系统报警时间
	for i := 0; i < len(devices)-1; i++ {
		t1, err := time.Parse(timeFormat, devices[i].T_Time)
		if err != nil {
			log.Println("解析时间失败:", err)
			continue
		}
		t2, err := time.Parse(timeFormat, devices[i+1].T_Time)
		if err != nil {
			log.Println("解析时间失败:", err)
			continue
		}
		timeDiff := t2.Sub(t1).Seconds()
		// 检查是否为报警数据
		isAlarm := devices[i].T_T < t_tlower || devices[i].T_T > t_tupper || devices[i].T_Rh < t_r_hlower || devices[i].T_Rh > t_r_hupper
		isNextAlarm := devices[i+1].T_T < t_tlower || devices[i+1].T_T > t_tupper || devices[i+1].T_Rh < t_r_hlower || devices[i+1].T_Rh > t_r_hupper
		// 处理报警数据与正常数据之间的间隔
		if isAlarm && !isNextAlarm {
			fmt.Println("处理报警数据与正常数据之间的间隔", devices[i].T_Time, devices[i+1].T_Time)
			//interval := floatWarn - 20
			//newTime := t1.Add(time.Duration(int(interval)) * time.Second)
			//fmt.Println("newTime:", newTime)
			newTime := t2.Add(time.Duration(floatSave * float64(time.Second) * -1))
			for timeDiff > floatSave {
				fmt.Printf("间隔时间大于系统设置时间：%s 和 %s\n", devices[i].T_Time, devices[i+1].T_Time)
				T_T, T_Rh := IsNotWarn(devices, t_tlower, t_tupper, t_r_hlower, t_r_hupper)
				devi := DeviceData{
					T_Id:        devices[i].T_Id,
					T_Sp:        devices[i].T_Sp,
					T_Time:      newTime.Format(timeFormat),
					T_T:         T_T,
					T_Rh:        T_Rh,
					T_Site:      devices[i].T_Site,
					Create_time: newTime.Format(timeFormat),
				}
				insertSql := fmt.Sprintf("INSERT INTO z_device_data_%s (t_id, t_sp, t_time, t_t, t_rh, t_site, create_time) VALUES (?, ?, ?, ?, ?, ?, ?)", sn)
				_, err := databases.Db.Exec(insertSql, devi.T_Id, devi.T_Sp, devi.T_Time, devi.T_T, devi.T_Rh, devi.T_Site.String, devi.Create_time)
				if err != nil {
					fmt.Println("插入新时间点失败:", err)
				}
				log.Println("sn:", sn, "连续报警补漏数据：", devi, " 插入时间点：", newTime.Format(timeFormat))
				//t, _ := time.Parse("2006-01-02 15:04:05", endTime)
				timeDiff = newTime.Sub(t1).Seconds()
				newTime = newTime.Add(time.Duration(floatSave * float64(time.Second) * -1))
			}
		}
		// 处理时间差大于系统设置时间的情况
		if timeDiff > floatSave {
			fmt.Printf("时间差大于系统设置时间：%s 和 %s\n", devices[i].T_Time, devices[i+1].T_Time)
			numInserts := int(timeDiff / floatSave)
			for j := 1; j <= numInserts; j++ {
				newTime := t1.Add(time.Duration(j*int(floatSave)) * time.Second)
				T_T, T_Rh := IsNotWarn(devices, t_tlower, t_tupper, t_r_hlower, t_r_hupper)
				devi := DeviceData{
					T_Id:        devices[i].T_Id,
					T_Sp:        devices[i].T_Sp,
					T_Time:      newTime.Format(timeFormat),
					T_T:         T_T,
					T_Rh:        T_Rh,
					T_Site:      devices[i].T_Site,
					Create_time: newTime.Format(timeFormat),
				}
				insertSql := fmt.Sprintf("INSERT INTO z_device_data_%s (t_id, t_sp, t_time, t_t, t_rh, t_site, create_time) VALUES (?, ?, ?, ?, ?, ?, ?)", sn)
				_, err := databases.Db.Exec(insertSql, devi.T_Id, devi.T_Sp, devi.T_Time, devi.T_T, devi.T_Rh, devi.T_Site.String, devi.Create_time)
				if err != nil {
					fmt.Println("插入新时间点失败:", err)
				}
				log.Println("sn:", sn, "补漏数据：", devi, " 插入时间点：", newTime.Format(timeFormat))
			}
		}
		// 处理连续报警数据
		if isAlarm && isNextAlarm {
			if timeDiff > floatWarn {
				fmt.Printf("连续报警时间差大于报警间隔时间：%s 和 %s\n", devices[i].T_Time, devices[i+1].T_Time)
				newTimeWarn := devices[i].T_Time
				parse, _ := time.Parse(timeFormat, newTimeWarn)
				newTime := parse.Add(time.Duration(int(floatWarn-rand.Float64()*10-0.2)) * time.Second)
				//newTime := t1.Add(time.Duration(int(timeDiff-rand.Float64()*10-0.2)) * time.Second)
				fmt.Println("报警新时间：", newTime.Format(timeFormat))
				T_T, T_Rh := IsNotWarn(devices, t_tlower, t_tupper, t_r_hlower, t_r_hupper)
				devi := DeviceData{
					T_Id:        devices[i].T_Id,
					T_Sp:        devices[i].T_Sp,
					T_Time:      newTime.Format(timeFormat),
					T_T:         T_T,
					T_Rh:        T_Rh,
					T_Site:      devices[i].T_Site,
					Create_time: newTime.Format(timeFormat),
				}
				insertSql := fmt.Sprintf("INSERT INTO z_device_data_%s (t_id, t_sp, t_time, t_t, t_rh, t_site, create_time) VALUES (?, ?, ?, ?, ?, ?, ?)", sn)
				_, err := databases.Db.Exec(insertSql, devi.T_Id, devi.T_Sp, devi.T_Time, devi.T_T, devi.T_Rh, devi.T_Site.String, devi.Create_time)
				if err != nil {
					fmt.Println("插入新时间点失败:", err)
				}
				log.Println("sn:", sn, "连续报警补漏数据：", devi, " 插入时间点：", newTime.Format(timeFormat))
			}
		}
	}
}
func IsNotWarn(deviceDatas []DeviceData, t_tlower, t_tupper, t_r_hlower, t_r_hupper float64) (T_T float64, T_Rh float64) {
	for i, _ := range deviceDatas {
		if !(deviceDatas[i].T_T < t_tlower || deviceDatas[i].T_T > t_tupper || deviceDatas[i].T_Rh < t_r_hlower || deviceDatas[i].T_Rh > t_r_hupper) {
			T_T = deviceDatas[i].T_T + rand.Float64()*0.4 - 0.2
			T_Rh = deviceDatas[i].T_Rh + rand.Float64()*0.4 - 0.2
			return T_T, T_Rh
		}
	}
	return 0, 0
}