Cold_DataFiltering/service/datacaps.go

package service

import (
	"context"
	"fmt"
	"github.com/redis/go-redis/v9"
	"github.com/xuri/excelize/v2"
	"log"
	"strconv"
	"time"
	"ydMonitoring/config"
	"ydMonitoring/databases"
	"ydMonitoring/logger"
	"ydMonitoring/util"
)

/*
*
DataCaps
* 得到所有设备的SN并且通过SN得到表名
* z_device_data_sn
* 查找该表内最新的一条数据信息并且查询该设备数据上传时间，并且通过该时间
* 判断前5分钟内的数据是否缺失，如有缺失，直接推送短信告警，并记录在数据库里面（时间、客户名称、SN、数据缺失的时间、是否处理、处理人、处理时间）
* 新增：获取每个t_id的前10条数据进行两两比较，最后一条数据还需与当前时间进行比较判断在设定时间内是否有数据缺失
* 新增：每一个探头当天内只保存一次数据
*数据之间两两比较，如果时间差大于系统设置时间代表缺少数据，则发送微信告警
*
*/
var RedisClient *redis.Client

func init() {
	RedisClient = redis.NewClient(&redis.Options{
		Addr:     config.Config.GetString("redis.host"),
		Password: config.Config.GetString("redis.password"), // no password set
		DB:       0,                                         // use default DB
	})
	ping := RedisClient.Ping(context.Background())
	if ping.Err() != nil {
		log.Println("redis连接失败:", ping.Err())
		panic("redis连接失败")
	}
}

func DataCaps(devices []Device) {
	message := make([]string, 0)
	// 文件名
	var row = 2
	filename := fmt.Sprintf("./files/%s.xlsx", time.Now().Format("2006-01-02"))
	file, err := excelize.OpenFile(filename)
	if err != nil {
		file = excelize.NewFile()
		// 创建工作区
		sheet := "sheet1"
		file.NewSheet(sheet)
		// 设置列标题
		file.SetCellValue(sheet, "A1", "设备SN")
		file.SetCellValue(sheet, "B1", "公司名称")
		file.SetCellValue(sheet, "C1", "设备名称")
		file.SetCellValue(sheet, "D1", "探头")
		file.SetCellValue(sheet, "E1", "数据缺失开始时间")
		file.SetCellValue(sheet, "F1", "设备型号")
	} else {
		// 获取最后一行
		lastRow, err := getLastRow(file, "sheet1")
		if err != nil {
			logger.ErrorLogger.Println("获取Excel文件失败:", err)
			return
		}
		row = lastRow + 1
	}
	deviceSensor := make([]DeviceSensor, 0)
	for _, v := range devices {
		sql := fmt.Sprintf("select t_sn,t_id,t_pid,t_name from device_sensor where t_sn = %s", v.Tsn)
		query, _ := databases.Db.Query(sql)
		var sn, t_id, t_pid, t_name string
		if query != nil {
			for query.Next() {
				query.Scan(&sn, &t_id, &t_pid, &t_name)
				deviceSensor = append(deviceSensor, DeviceSensor{sn, t_id, t_pid, t_name, v.Tmodel})
			}
		}
	}
	for _, sensor := range deviceSensor {
		//var t_time string
		var t_save_t string
		var companyname string
		var id string
		b, _ := Ignore(sensor.Tsn)
		if b {
			logger.ErrorLogger.Println("该设备已忽略:", sensor.Tsn)
		} else {
			// 获取当前时间
			now := time.Now()
			// 获取昨天同一时间
			yesterday := now.AddDate(0, 0, -1)
			// 设置时间为昨天的 8:30
			yesterdayAtEightThirty := time.Date(yesterday.Year(), yesterday.Month(), yesterday.Day(), 8, 30, 0, 0, yesterday.Location()).Format("2006-01-02 15:04:05")
			// 构造今天早上8:30的时间
			eightThirtyToday := time.Date(now.Year(), now.Month(), now.Day(), 8, 30, 0, 0, now.Location()).Format("2006-01-02 15:04:05")

			sql := fmt.Sprintf("SELECT t_time FROM z_device_data_%s WHERE t_id = '%s' AND t_time >='%v' AND t_time<= '%v' ORDER BY t_time DESC", sensor.Tsn, sensor.TId, yesterdayAtEightThirty, eightThirtyToday)
			sqls := fmt.Sprintf("SELECT t_save_t FROM device_parameter WHERE t_sn='%s' ORDER BY update_time DESC LIMIT 1", sensor.Tsn)
			company := fmt.Sprintf("SELECT t_name,id FROM company WHERE id='%s'", sensor.TPid)
			databases.Db.QueryRow(sqls).Scan(&t_save_t)
			databases.Db.QueryRow(company).Scan(&companyname, &id)
			rows, err := databases.Db.Query(sql)
			if err != nil {
				logger.ErrorLogger.Println("查询数据失败:", err)
				continue
			}
			defer rows.Close()
			var times []string
			for rows.Next() {
				var t string
				rows.Scan(&t)
				times = append(times, t)
			}
			if len(times) >= 2 {
				// 数据之间两两比较，如果时间差大于系统设置时间代表缺少数据，则发送微信告警
				timeFormat := "2006-01-02 15:04:05"
				for i := 0; i < len(times)-1; i++ {
					t1, err := time.Parse(timeFormat, times[i])
					if err != nil {
						logger.ErrorLogger.Println("解析时间失败:", err)
						continue
					}
					t2, err := time.Parse(timeFormat, times[i+1])
					if err != nil {
						logger.ErrorLogger.Println("解析时间失败:", err)
						continue
					}
					timeDiff := t1.Sub(t2).Seconds()
					float, _ := strconv.ParseFloat(t_save_t, 64) //获取系统设置时间
					if timeDiff > float {
						fmt.Println("时间差:", timeDiff, "系统设置时间:", float, "设备型号：", sensor.Tmodel)
						//生成一个独立的key
						key := fmt.Sprintf("%s_%s_%s_%s", sensor.Tsn, companyname+"["+id+"]", sensor.TName, sensor.TId)
						//fmt.Println(key)
						nowday := time.Now()
						midnight := time.Date(nowday.Year(), nowday.Month(), nowday.Day()+1, 0, 0, 0, 0, nowday.Location())
						remainingTime := midnight.Sub(nowday)
						result, err := RedisClient.SetNX(context.Background(), key, 1, remainingTime).Result()
						if err != nil {
							logger.ErrorLogger.Println("redis写入失败:", err)
						} else if result {
							name := sensor.TName + "[" + sensor.Tmodel + "]"
							wx := util.WxStruct{
								Character: sensor.Tsn,
								Thing13:   companyname,
								Thing3:    name,
								Thing45:   sensor.TId,
								Time2:     times[i+1],
							}
							slice := config.Config.GetStringSlice("openID")
							for _, v := range slice {
								fmt.Println(v, wx)
								util.Send(v, wx)
							}
							message = append(message, fmt.Sprintf("Sn:%s==>%s==>设备:%s==>探头%s==>数据缺失上传时间:%s \n", sensor.Tsn, companyname+"["+id+"]", sensor.TName, sensor.TId, times[i]))
							// 将相关信息记录到Excel文件中
							if !dataExists(file, "sheet1", sensor.Tsn, companyname+"["+id+"]", sensor.TName, sensor.TId, times[i+1]) {
								file.SetCellValue("sheet1", fmt.Sprintf("A%d", row), sensor.Tsn)
								file.SetCellValue("sheet1", fmt.Sprintf("B%d", row), companyname+"["+id+"]")
								file.SetCellValue("sheet1", fmt.Sprintf("C%d", row), sensor.TName)
								file.SetCellValue("sheet1", fmt.Sprintf("D%d", row), sensor.TId)
								file.SetCellValue("sheet1", fmt.Sprintf("E%d", row), times[i+1])    // 数据缺失开始时间
								file.SetCellValue("sheet1", fmt.Sprintf("F%d", row), sensor.Tmodel) // 设备型号
								row++
							}

						}
					}
				}
			}
		}
	}
	fmt.Println(message, len(message))
	// 保存Excel文件
	if err := file.SaveAs(filename); err != nil {
		logger.ErrorLogger.Println("保存Excel文件失败:", err)
	} else {
		logger.InfoLogger.Println("Excel文件保存成功，路径：", filename)
	}
}

// 检查数据是否已存在
func dataExists(file *excelize.File, sheet string, sn, company, name, id, time string) bool {
	rows, err := file.GetRows(sheet)
	if err != nil {
		logger.ErrorLogger.Println("获取Excel文件行数据失败:", err)
		return false
	}
	for _, row := range rows[1:] { // 跳过表头
		if len(row) >= 5 && row[0] == sn && row[1] == company && row[2] == name && row[3] == id && row[4] == time {
			return true
		}
	}
	return false
}

// 获取最后一行的行号
func getLastRow(file *excelize.File, sheet string) (int, error) {
	rows, err := file.GetRows(sheet)
	if err != nil {
		return 0, err
	}
	return len(rows), nil
}

func Ignore(sn string) (bool, error) {
	//sql := "SELECT t_sn FROM ignore WHERE t_sn = ?"
	sql := fmt.Sprintf("SELECT t_sn FROM `ignore` WHERE t_sn = '%s'", sn)
	var tsn string
	err := databases.Db.QueryRow(sql).Scan(&tsn)
	if err != nil {
		return false, err // 其他数据库错误
	}
	return tsn == sn, nil
}