Cold_DataFiltering/service/test.go

package service

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

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"`
}

type DeviceLists struct {
	t_save_t   string  //系统设置间隔时间
	t_warn     string  // 报警间隔时间
	t_tlower   float64 // 低温下限
	t_tupper   float64 // 高温上限
	t_r_hlower float64 // 湿度下限
	t_r_hupper float64 // 湿度上限
	sn         string  // sn
}

func TestService() {
	sn := "2024479869314058"
	//var t_id string
	//t_id := "22"
	startTime := "2024-11-25 10:30:00"
	endTime := "2024-11-29 16:00:00"
	for i := 1; i <= 22; i++ {
		t_id := strconv.Itoa(i)
		//ablistdevices, ablists := Listdevices(sn, t_id, startTime, endTime)
		//abnormal(ablistdevices, ablists.t_save_t, ablists.t_warn, ablists.sn, ablists.t_tlower, ablists.t_tupper, ablists.t_r_hlower, ablists.t_r_hupper)
		listdevices, lists := Listdevices(sn, t_id, startTime, endTime)
		normal(listdevices, lists.t_save_t, lists.t_warn, lists.sn, lists.t_tlower, lists.t_tupper, lists.t_r_hlower, lists.t_r_hupper)
	}
}

func Listdevices(sn, t_id, startTime, endTime string) ([]DeviceData, DeviceLists) {
	var deviceslist DeviceLists
	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)
	deviceslist.sn = sn
	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)
	}
	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(&deviceslist.t_save_t, &deviceslist.t_warn)
	databases.Db.QueryRow(deviceSensorParameter).Scan(&deviceslist.t_tlower, &deviceslist.t_tupper, &deviceslist.t_r_hlower, &deviceslist.t_r_hupper)
	return devices, deviceslist
}

func normal(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)
	tx, err := databases.Db.Begin()
	if err != nil {
		log.Fatalf("开始事务失败: %v", err)
	}
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p)
		}
		if err != nil {
			tx.Rollback()
		} else {
			tx.Commit()
		}
	}()
	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()
		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++ {
				t := t1.Truncate(time.Minute)
				newTime := t.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 := tx.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 {
					log.Println("插入新时间点失败:", err)
					return
				}
				log.Println("sn:", sn, "补漏数据：", devi, " 插入时间点：", newTime.Format(timeFormat))
			}
			//rand.Seed(time.Now().UnixNano())
			//randomNumber := rand.Intn(59) + 1
			//add := t2.Add(time.Duration(randomNumber) * time.Second).Format(timeFormat)
			//format := t2.Format(timeFormat)
			//sprintf := fmt.Sprintf("UPDATE z_device_data_%s SET t_time = '%s' WHERE t_time = '%s'", sn, add, format)
			//fmt.Println(sprintf)
			//_, err = tx.Exec(sprintf)
			//if err != nil {
			//	log.Println("更新时间节点错误:", err)
			//	return
			//}
		}
	}
}

func abnormal(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"
	floatWarn, _ := strconv.ParseFloat(t_warn, 64)
	tx, err := databases.Db.Begin()
	if err != nil {
		log.Fatalf("开始事务失败: %v", err)
	}
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p)
		}
		if err != nil {
			tx.Rollback()
		} else {
			tx.Commit()
		}
	}()
	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)
			if timeDiff > floatWarn {
				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 := tx.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 {
					log.Println("插入新时间点失败:", err)
					return
				}
				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)
				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 := tx.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 {
					log.Println("插入新时间点失败:", err)
					return
				}
				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
}