Cold_DataFiltering/service/datacaps.go

package service

import (
	"fmt"
	"github.com/xuri/excelize/v2"
	"log"
	"strconv"
	"time"
	"ydMonitoring/config"
	"ydMonitoring/databases"
	"ydMonitoring/util"
)

/*
*
DataCaps
*得到所有设备的SN并且通过SN得到表名
*z_device_data_sn
*查找该表内最新的一条数据信息并且查询该设数据上传时间，并且通过改时间
判断前5分钟内的数据是否缺失，如有缺失，直接推送短信告警，并记录在数据库里面（时间、客户名称、SN、数据缺失的时间、是否处理、处理人、处理时间）
*
*/
func DataCaps(davice []Device) {
	message := make([]string, 0)
	//文件名
	var row = 2
	filename := fmt.Sprintf("%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", "数据缺失开始时间")
	} else {
		//获取最后一行
		lastRow, err := getLastRow(file, "sheet1")
		if err != nil {
			log.Println("获取Excel文件失败:", err)
			return
		}
		row = lastRow + 1
	}

	sqllist := make([]string, 0)
	for _, v := range davice {
		log.Println(v.Tsn)
		//sql := fmt.Sprintf("select t_sn,t_id,t_pid,t_name from device_sensor where t_sn = %s")
		sql := fmt.Sprintf("select t_sn,t_id,t_pid,t_name from device_sensor where t_sn = %s", v.Tsn)
		sqllist = append(sqllist, sql)
	}
	deviceSensor := make([]DeviceSensor, 0)
	for _, sql := range sqllist {
		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})
			}
		}
	}
	//查询最新的两条数据以及保存时间，比较最近两条时间是否有超过保存时间
	for _, sensor := range deviceSensor {
		var t_time string
		var t_save_t string
		var companyname string
		var id string
		sql := fmt.Sprintf("SELECT t_time FROM z_device_data_%s ORDER BY t_time DESC LIMIT 1", sensor.Tsn)
		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)
		queryRow := databases.Db.QueryRow(sql)

		if queryRow != nil {
			queryRow.Scan(&t_time)
		}
		// 解析时间字符串为time.Time对象
		timeFormat := "2006-01-02 15:04:05" // 假设这是时间字符串的格式
		//fmt.Println("时间不能为空")
		if t_time != "" {
			t1, err := time.Parse(timeFormat, t_time)
			if err != nil {
				log.Println("解析时间失败:", err)
				continue
			}
			now := time.Now().Format(timeFormat)
			nows, _ := time.Parse(timeFormat, now)
			// 计算时间差
			timeDiff := nows.Sub(t1).Seconds()
			//timeDiff := t1.Sub(now).Seconds()
			fmt.Println("最后一条数据时间:", t_time)
			fmt.Println("系统当前时间:", now)
			//比较时间差
			fmt.Println("时间差:", timeDiff)
			fmt.Println("系统设置时间差:", t_save_t)

			// 获取今天的开始时间
			todayStart := time.Now().Truncate(24 * time.Hour)
			// 确保t1表示的时间为今天
			if t1.After(todayStart) || t1.Equal(todayStart) {
				float, _ := strconv.ParseFloat(t_save_t, 64)
				if timeDiff > float {
					//log.Printf("实际时间大于系统时间Sn:%s=>>%s=>>探头%s,上传时间%s", sensor.Tsn, companyname, sensor.TId, t_time[0])
					message = append(message, fmt.Sprintf("Sn:%s==>%s==>设备:%s==>探头%s==>数据缺失上传时间:%s \n", sensor.Tsn, companyname+"["+id+"]", sensor.TName, sensor.TId, t_time))
					// 将相关信息记录到Excel文件中
					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), t_time) // 数据缺失开始时间
					row++
				}
			}
			if err := file.SaveAs(filename); err != nil {
				log.Println("保存Excel文件失败:", err)
			}
		}
	}
	fmt.Println(message)
	lenght := len(message)
	phones := config.Config.GetStringSlice("phone")
	for _, phone := range phones {
		err := util.SendModel(phone, message, lenght)
		if err != nil {
			log.Println("发送失败:", err)
		}
	}
}

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