ColdP_server/models/Device/DeviceData.go

package Device

import (
	"ColdP_server/conf"
	"ColdP_server/controllers/lib"
	"ColdP_server/logs"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"github.com/astaxie/beego/cache"
	_ "github.com/astaxie/beego/cache/redis"
	"github.com/beego/beego/v2/client/orm"

	_ "github.com/go-sql-driver/mysql"
	"github.com/xuri/excelize/v2"
	"log"
	"math/rand"
	"strconv"
	"strings"
	"time"
)

// 建表
// var CREATEsql = "CREATE TABLE IF NOT EXISTS `z_device_data_" + SN + " ( " +
var CREATEsql = " ( " +
	"`t_id` int(6) NOT NULL," +
	"`t_sp` int(6) NOT NULL," +
	"`t_time` datetime NOT NULL," +
	"`t_t` double(6, 2) NULL DEFAULT NULL," +
	"`t_rh` double(6, 2) NULL DEFAULT NULL," +
	"`t_site` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL," +
	"`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP," +
	"PRIMARY KEY (`t_time`, `t_id`) USING BTREE" +
	") ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;"

type DeviceData_ struct {
	T_id        int       // 传感器id
	T_sp        int       // 传感器参数id
	T_t         float32   // 温度
	T_rh        float32   // 湿度
	T_site      string    // GPS
	T_time      time.Time // 采集时间
	Create_time time.Time //创建时间
}
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
}

// 模板
type DeviceData_R struct {
	T_sn        string  `json:"t_sn"`   //设备
	T_id        int     `json:"t_id"`   // 传感器id
	T_sp        int     `json:"t_sp"`   // 传感器参数id
	T_name      string  `json:"t_name"` // 传感器名称
	T_t         float32 `json:"t_t"`    // 温度
	T_rh        float32 `json:"t_rh"`   // 湿度
	T_site      string  `json:"t_site"` // GPS
	T_tl        float32 `json:"t_tl"`   // 温度下限
	T_tu        float32 `json:"t_tu"`   // 温度上限
	T_rhl       float32 `json:"t_rhl"`  // 湿度下限
	T_rhu       float32 `json:"t_rhu"`  // 湿度上限
	T_time      string  `json:"t_time"` // 采集时间
	Create_Time string  `json:"create_time"`
}

//func (t *DeviceData) TableName() string {
//	return "DeviceData" // 数据库名称   // ************** 替换 FormulaList **************
//}

var redis_DeviceData cache.Cache

func init() {

	config := fmt.Sprintf(`{"key":"%s","conn":"%s","dbNum":"%s","password":"%s"}`,
		"redis_DeviceData", conf.Redis_address, conf.Redis_dbNum, conf.Redis_password)
	fmt.Println(config)
	var err error
	redis_DeviceData, err = cache.NewCache("redis", config)
	if err != nil || redis_DeviceData == nil {
		errMsg := "failed to init redis"
		fmt.Println(errMsg, err)
	}
}

func DeviceData_ToDeviceData_R(r DeviceData_, maps map[int]DeviceSensorParameter_M) (t DeviceData_R) {
	t.T_id = r.T_id
	t.T_t = r.T_t
	t.T_rh = r.T_rh
	t.T_site = r.T_site
	t.T_time = r.T_time.Format("2006-01-02 15:04:05")
	t.Create_Time = r.Create_time.Format("2006-01-02 15:04:05")
	if sp, ok := maps[r.T_sp]; ok {
		t.T_sp = sp.T_sp
		t.T_name = sp.T_name
		t.T_tl = sp.T_Tlower
		t.T_tu = sp.T_Tupper
		t.T_rhl = sp.T_RHlower
		t.T_rhu = sp.T_RHupper
	}
	return
}

// ---------------- Redis -------------------
// Redis_Set(m.T_sn,m) // Redis 更新缓存
func RedisDeviceData_Set(key string, r DeviceData_R) (err error) {
	if redis_DeviceData.IsExist(key) {
		var t DeviceData_R
		v := redis_DeviceData.Get(key)
		json.Unmarshal(v.([]byte), &t)
		tTime, _ := time.Parse("2006-01-02 15:04:05", t.T_time)
		rTime, _ := time.Parse("2006-01-02 15:04:05", r.T_time)
		if tTime.Unix() > rTime.Unix() {
			// 储存的 是最新数据
			return
		}
	}

	//json序列化
	str, err := json.Marshal(r)
	if err != nil {
		fmt.Print(err)
		return
	}
	err = redis_DeviceData.Put(key, str, 1*time.Hour)
	if err != nil {
		fmt.Println("set key:", key, ",value:", str, err)
	}
	return
}

func RedisDeviceData_Get(key string) (r DeviceData_R, is bool) {
	if redis_DeviceData.IsExist(key) {
		v := redis_DeviceData.Get(key)
		json.Unmarshal(v.([]byte), &r)
		return r, true
	}
	return DeviceData_R{}, false
}
func RedisDeviceData(key string) (r DeviceData_, is bool) {
	if redis_DeviceData.IsExist(key) {
		v := redis_DeviceData.Get(key)
		err := json.Unmarshal(v.([]byte), &r)
		if err != nil {
			logs.Error(lib.FuncName(), err)
			return DeviceData_{}, false
		}

		return r, true
	}
	return DeviceData_{}, false
}

// 获取设备最新数据
func Read_DeviceData(T_sn string, T_id int) (t DeviceData_) {
	key := T_sn + "|" + strconv.Itoa(T_id)
	if t, is := RedisDeviceData(key); is {
		return t
	}
	o := orm.NewOrm()
	var maps DeviceData_
	sql := "SELECT t_id,t_sp,t_t,t_rh,t_site,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_time,t_time AS t_time1 FROM z_device_data_" + T_sn + " WHERE" + " t_id = " + strconv.Itoa(T_id) + " ORDER BY t_time1 DESC LIMIT 0,1"
	logs.Debug(sql)
	err := o.Raw(sql).QueryRow(&maps)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return t
	}
	return maps
}

// -------------------------------------------------------
// 创建数据库  Device.CREATE_DeviceData("")
func CREATE_DeviceData(SN string) bool {

	sql := "CREATE TABLE IF NOT EXISTS `z_device_data_" + SN + "` " + CREATEsql
	o := orm.NewOrm()
	_, err := o.Raw(sql).Exec()
	if err != nil {
		return false
	}
	return true
}

func DELETE_DeviceDatar(SN string) bool {
	timeStr := time.Now().Format("2006_01_02_15_04_05")
	//sql := "DROP TABLE z_device_data_" + SN
	sql := "ALTER TABLE z_device_data_" + SN + " RENAME TO " + "z_device_data_" + SN + "_dle_" + timeStr + ";"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Exec()
	if err != nil {
		return false
	}
	return true
}

// ---------------- 特殊方法 ------------------
type DeviceData_T struct {
	T_id   int       // ID
	T_t    float32   // 温度
	T_rh   float32   // 湿度
	T_Site string    // GPS
	T_time time.Time // 采集时间
}

// 添加
func Add_DeviceData(SN string, v DeviceData_T) bool {
	//if(conf.Test_server){
	//	fmt.Println("Add_DeviceData:",SN , t_name , t_id , t_t , t_rh , t_tl , t_tu , t_rhl , t_rhu , t_site , t_battery , t_time)
	//	return true
	//}

	key_time := SN + "|" + strconv.Itoa(v.T_id) + "|" + v.T_time.Format("2006-01-02 15:04:05")
	fmt.Println(key_time)
	if redis_DeviceData.IsExist(key_time) {
		//println("找到key:",key)
		return true
	}

	o := orm.NewOrm()

	// 检查 超过时间，查询 数据库
	if time.Now().Unix()-v.T_time.Unix() >= 60*40 {
		// 查看是否 有记录
		var maps_z []orm.ParamsList
		sql_c := "SELECT COUNT(ID) FROM z_device_data_" + SN + " WHERE t_time = '" + v.T_time.Format("2006-01-02 15:04:05") + "' AND t_id = " + strconv.Itoa(v.T_id)
		_, err := o.Raw(sql_c).ValuesList(&maps_z)
		if err != nil {
			return false
		}
		//fmt.Println("maps_z[0][0]:",maps_z[0][0])
		if lib.To_int(maps_z[0][0]) > 0 {
			return true
		}
	}

	// 开始插入数据
	sql := "INSERT INTO z_device_data_" + SN + " ( `t_id`, `t_t`, `t_rh`, `t_site`, `t_time`) " +
		"VALUES (" + strconv.Itoa(v.T_id) + ", " + lib.To_string(v.T_t) + ", " + lib.To_string(v.T_rh) + ", '" + v.T_Site + "'," + v.T_time.Format("2006-01-02 15:04:05") + "')"
	//  这里有时间优化  用于一次 prepare 多次 exec，以提高批量执行的速度
	fmt.Println(sql)
	res, err := o.Raw(sql).Exec()
	if err != nil {
		fmt.Println(err)
		return false
	}
	res.RowsAffected()

	return true
}

///---------------
//type DeviceData_ struct {
//	T_sn     string  `orm:"column(t_sn);size(256);null"`          // 标题
//	T_id      int     `orm:"column(t_id);size(10);null"`           // ID
//	T_t       float32 `orm:"column(t_t);size(10);null"`            // 温度
//	T_rh      float32 `orm:"column(t_rh);size(10);null"`           // 湿度
//	T_site    string  `orm:"column(t_site);type(timestamp);null;"` // GPS
//	T_time    string  `orm:"column(t_time);type(timestamp);null;"` // 采集时间
//}
//type DeviceData_New struct {
//	T_name    string    `orm:"column(t_name);size(256);null"`        // 标题
//	T_id      int       `orm:"column(t_id);size(10);null"`           // ID
//	T_t       float32   `orm:"column(t_t);size(10);null"`            // 温度
//	T_rh      float32   `orm:"column(t_rh);size(10);null"`           // 湿度
//	T_Tlower  float32   `orm:"column(t_tl);size(10);null"`           // 湿度
//	T_Tupper  float32   `orm:"column(t_tu);size(10);null"`           // 湿度
//	T_RHlower float32   `orm:"column(t_rhl);size(10);null"`          // 湿度
//	T_RHupper float32   `orm:"column(t_rhu);size(10);null"`          // 湿度
//	T_Site    string    `orm:"column(t_site);null;"`                 // GPS
//	T_Dattery int       `orm:"column(t_dattery);size(10);null"`      // 电量
//	T_time    time.Time `orm:"column(t_time);type(timestamp);null;"` // 采集时间
//}

func Read_DeviceData_ById_List(SN string, T_id int, Time_start_ string, Time_end_ string, page int, page_z int, SParamMap map[int]DeviceSensorParameter_M, sort string) ([]DeviceData_R, int) {
	o := orm.NewOrm()
	var maps []DeviceData_
	var maps_z []orm.ParamsList
	var r []DeviceData_R
	var offset int
	if page_z == 0 {
		page_z = conf.Page_size
	}

	if page <= 1 {
		offset = 0
	} else {
		offset = (page - 1) * page_z
	}
	sql_time := ""

	if len(Time_start_) > 1 {
		sql_time += " t_time >= '" + Time_start_ + "' AND "
	}

	if len(Time_end_) > 1 {
		sql_time += " t_time <= '" + Time_end_ + "' AND "
	}

	sql := "SELECT COUNT(t_id) FROM z_device_data_" + SN + " WHERE " + sql_time + " t_id = " + strconv.Itoa(T_id)
	fmt.Println(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		return r, 0
	}
	if len(maps_z) == 0 {
		return r, 0
	}
	//fmt.Println("maps_z；",maps_z[0][0])
	//t_tl,t_tu,t_rhl,t_rhu,
	sql = "SELECT t_id,t_sp,t_t,t_rh,t_site,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_time,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s') AS create_time  FROM z_device_data_" + SN + " WHERE " + sql_time + " t_id = " + strconv.Itoa(T_id) + " ORDER BY t_time  " + sort
	if page_z != 9999 {
		sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(page_z)
	}

	//fmt.Println(sql)
	_, err = o.Raw(sql).QueryRows(&maps)

	//value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
	key, _ := strconv.Atoi(maps_z[0][0].(string))
	fmt.Println("\n\n\n\n\n\n搜索结果：", maps)
	for _, v := range maps {
		r = append(r, DeviceData_ToDeviceData_R(v, SParamMap))
	}
	return r, key
}

func Read_SqlRaw(T_SQL string) []orm.Params {
	o := orm.NewOrm()
	var lists []orm.Params

	fmt.Println(T_SQL)
	o.Raw(T_SQL).Values(&lists)
	fmt.Println(T_SQL+":", len(lists))
	return lists
}

func Read_SqlRawL(T_SQL string, T_data []string) (string, []orm.Params) {
	o := orm.NewOrm()
	var lists []orm.Params
	fmt.Println(T_SQL)
	_, err := o.Raw(T_SQL, T_data).Values(&lists)
	if err != nil {

		return err.Error(), lists
	}

	return "", lists
}

func Read_DeviceData_By_T_snid_List(T_snid string, Time_start_ string, Time_end_ string, page int, page_z int, sort string) ([]DeviceData_R, int64) {
	T_snid_list := strings.Split(T_snid, "|")
	var maps []DeviceData_R
	var maps_num int64
	var offset, offset_z int

	for _, v := range T_snid_list {
		sn_id := strings.Split(v, ",")

		if len(sn_id) == 2 {
			SParamMap := Read_DeviceSensorParameter_Map_All(sn_id[0], lib.To_int(sn_id[1]))
			r_maps, r_maps_num := Read_DeviceData_ById_List(sn_id[0], lib.To_int(sn_id[1]), Time_start_, Time_end_, 0, 9999, SParamMap, sort)
			//for i, _ := range r_maps {
			//	r_maps[i].T_sn = sn_id[0]
			//}
			//添加一个sn设备标识
			for i := range r_maps {
				r_maps[i].T_sn = sn_id[0]
			}
			maps = append(maps, r_maps...)
			maps_num = maps_num + int64(r_maps_num)

			fmt.Println("加载数据：", sn_id[0], sn_id[1], r_maps_num)
		}
	}
	if page <= 1 {
		offset = 0
	} else {
		page -= 1
		offset = page * page_z
	}

	offset_z = offset + page_z
	if maps_num < int64(offset_z) {
		offset_z = int(maps_num)
	}

	if page_z == 9999 {
		fmt.Println("总数据：", maps_num, " 导出")
		return maps, maps_num
	}
	fmt.Println("总数据：", maps_num, " 截取", offset, offset+page_z)
	return maps[offset:offset_z], maps_num
}

// DeleteDeviceDataByDeviceDataRList 删除记录
func DeleteDeviceDataByDeviceDataRList(datas []DeviceData_R) {
	o := orm.NewOrm()
	for _, v := range datas {
		_, err := o.Raw(fmt.Sprintf("delete from z_device_data_%s where t_id = ? and t_t = ? and t_rh = ? and t_site = ? and create_time = ?", v.T_sn), v.T_id, v.T_t, v.T_rh, v.T_site, v.Create_Time).Exec()
		if err != nil {
			fmt.Println(err.Error())
		}
	}
}

func ReadDeviceSensorParameter(TSp int) (error, DeviceSensorParameter_M) {
	o := orm.NewOrm()
	var deviceParameter DeviceSensorParameter_M
	deviceSensorParameter := fmt.Sprintf("SELECT id,t_name,t__tlower,t__tupper,t__r_hlower,t__r_hupper FROM device_sensor_parameter WHERE  id='%v'", TSp)
	log.Println(deviceSensorParameter)
	err := o.Raw(deviceSensorParameter).QueryRow(&deviceParameter)
	if err != nil {
		log.Println(err.Error())
		return errors.New("设备不存在"), DeviceSensorParameter_M{}
	}
	return nil, deviceParameter
}

func DeleteDeviceDataByTime(sn, t_id, time_start, time_end string) (sql.Result, error) {
	o := orm.NewOrm()
	sprintf := fmt.Sprintf("delete from z_device_data_%s where t_id=%s and t_time >= '%s' and t_time <= '%s'", sn, t_id, time_start, time_end)
	log.Println(sprintf)
	count, err := o.Raw(sprintf).Exec()
	if err != nil {
		log.Println(err.Error())
		return count, err
	}
	warningsql := fmt.Sprintf("delete from warning where t_sn='%s' and t_id=%s and t__ut >= '%s' and t__ut <= '%s'", sn, t_id, time_start, time_end)
	log.Println(sprintf)
	count, err = o.Raw(warningsql).Exec()
	if err != nil {
		log.Println(err.Error())
		return count, err
	}
	startTime, err := time.Parse("2006-01-02 15:04:05", time_start)
	if err != nil {
		return nil, errors.New("时间格式错误")
	}
	endTime, err := time.Parse("2006-01-02 15:04:05", time_end)
	if err != nil {
		return nil, errors.New("时间格式错误")
	}
	//判断开始时间和结束时间是否属于同一个月
	if startTime.Month() == endTime.Month() && startTime.Year() == endTime.Year() {
		formatMonth := startTime.Format("01")
		tableName := "warning" + "_" + strconv.Itoa(startTime.Year()) + "_" + formatMonth
		sql := fmt.Sprintf("delete from %s where t_sn='%s' and t_id='%s' and t__ut >= '%s' and t__ut <= '%s'", tableName, sn, t_id, time_start, time_end)
		count, err := o.Raw(sql).Exec()
		if err != nil {
			log.Println(err.Error())
			return count, err
		}
	} else {
		// 不同的月份
		stas, ends, err := getMonthlyTimeRanges(time_start, time_end)
		if err != nil {
			return nil, errors.New("时间格式错误")
		}
		for i := 0; i < len(stas); i++ {
			fmt.Println(stas[i], ends[i])
			parse, err := time.Parse("2006-01-02 15:04:05", stas[i])
			if err != nil {
				return nil, errors.New("时间格式错误")
			}
			formatMonth := parse.Format("01")
			tableName := "warning" + "_" + strconv.Itoa(parse.Year()) + "_" + formatMonth
			sqls := fmt.Sprintf("delete from %s where t_sn='%s' and t_id=%s and t__ut >= '%s' and t__ut <= '%s'", tableName, sn, t_id, stas[i], ends[i])
			count, err := o.Raw(sqls).Exec()
			if err != nil {
				log.Println(err.Error())
				return count, err
			}
		}
	}

	return count, nil
}

func DeleteExceDeviceDataByTime(sn, t_id, time_start, time_end, tempUpper, tempUpperMin, tempUpperMax, tempLower, tempLowerMin, tempLowerMax, humidUpper, humidUpperMin, humidUpperMax, humidLower, humidLowerMin, humidLowerMax string) (sql.Result, error) {
	o := orm.NewOrm()

	// 初始化随机数种子
	rand.Seed(time.Now().UnixNano())

	//  删除异常数据
	sprintf := fmt.Sprintf("delete from z_device_data_%s where t_id=%s and t_time >= '%s' and t_time <= '%s' and DATE_FORMAT(t_time, '%%s') != '00'", sn, t_id, time_start, time_end)
	log.Println(sprintf)
	count, err := o.Raw(sprintf).Exec()
	if err != nil {
		log.Println(err.Error())
		return count, err
	}

	// 数据平滑处理：对超过8的数据进行平滑过渡，不超过8
	selectSql := fmt.Sprintf("select t_id, t_sp, t_t, t_rh, t_site, t_time from z_device_data_%s where t_id=%s and t_time >= '%s' and t_time <= '%s'", sn, t_id, time_start, time_end)
	log.Println(selectSql)

	var deviceDataList []DeviceData_
	_, err = o.Raw(selectSql).QueryRows(&deviceDataList)
	if err != nil {
		log.Println("读取数据失败:", err.Error())
		return count, err
	}

	// 将字符串参数转换为浮点数
	tempUpperFloat, _ := strconv.ParseFloat(tempUpper, 32)
	tempUpperMinFloat, _ := strconv.ParseFloat(tempUpperMin, 32)
	tempUpperMaxFloat, _ := strconv.ParseFloat(tempUpperMax, 32)
	tempLowerFloat, _ := strconv.ParseFloat(tempLower, 32)
	tempLowerMinFloat, _ := strconv.ParseFloat(tempLowerMin, 32)
	tempLowerMaxFloat, _ := strconv.ParseFloat(tempLowerMax, 32)
	humidUpperFloat, _ := strconv.ParseFloat(humidUpper, 32)
	humidUpperMinFloat, _ := strconv.ParseFloat(humidUpperMin, 32)
	humidUpperMaxFloat, _ := strconv.ParseFloat(humidUpperMax, 32)
	humidLowerFloat, _ := strconv.ParseFloat(humidLower, 32)
	humidLowerMinFloat, _ := strconv.ParseFloat(humidLowerMin, 32)
	humidLowerMaxFloat, _ := strconv.ParseFloat(humidLowerMax, 32)

	// 遍历数据并进行平滑处理
	for i, data := range deviceDataList {
		updated := false

		// 对温度进行平滑处理
		if data.T_t >= float32(tempUpperFloat) {
			// 生成指定范围内的随机数
			rangeSize := tempUpperMaxFloat - tempUpperMinFloat
			deviceDataList[i].T_t = float32(tempUpperMinFloat) + float32(rand.Float32()*float32(rangeSize))
			updated = true
		} else if data.T_t <= float32(tempLowerFloat) {
			// 生成指定范围内的随机数
			rangeSize := tempLowerMaxFloat - tempLowerMinFloat
			deviceDataList[i].T_t = float32(tempLowerMinFloat) + float32(rand.Float32()*float32(rangeSize))
			updated = true
		}

		// 对湿度进行平滑处理
		if data.T_rh >= float32(humidUpperFloat) {
			// 生成指定范围内的随机数
			rangeSize := humidUpperMaxFloat - humidUpperMinFloat
			deviceDataList[i].T_rh = float32(humidUpperMinFloat) + float32(rand.Float32()*float32(rangeSize))
			updated = true
		} else if data.T_rh <= float32(humidLowerFloat) {
			// 生成指定范围内的随机数
			rangeSize := humidLowerMaxFloat - humidLowerMinFloat
			deviceDataList[i].T_rh = float32(humidLowerMinFloat) + float32(rand.Float32()*float32(rangeSize))
			updated = true
		}

		// 如果数据有更新，则保存回数据库
		if updated {
			updateSql := fmt.Sprintf("update z_device_data_%s set t_t=?, t_rh=? where t_id=? and t_time=?", sn)
			_, err := o.Raw(updateSql, deviceDataList[i].T_t, deviceDataList[i].T_rh, data.T_id, data.T_time).Exec()
			if err != nil {
				log.Println("更新数据失败:", err.Error())
			}
		}
	}

	//  删除报警
	warningsql := fmt.Sprintf("delete from warning where t_sn='%s' and t_id=%s and t__ut >= '%s' and t__ut <= '%s'", sn, t_id, time_start, time_end)
	log.Println(sprintf)
	count, err = o.Raw(warningsql).Exec()
	if err != nil {
		log.Println(err.Error())
		return count, err
	}
	startTime, err := time.Parse("2006-01-02 15:04:05", time_start)
	if err != nil {
		return nil, errors.New("时间格式错误")
	}
	endTime, err := time.Parse("2006-01-02 15:04:05", time_end)
	if err != nil {
		return nil, errors.New("时间格式错误")
	}
	//判断开始时间和结束时间是否属于同一个月
	if startTime.Month() == endTime.Month() && startTime.Year() == endTime.Year() {
		formatMonth := startTime.Format("01")
		tableName := "warning" + "_" + strconv.Itoa(startTime.Year()) + "_" + formatMonth
		sql := fmt.Sprintf("delete from %s where t_sn='%s' and t_id='%s' and t__ut >= '%s' and t__ut <= '%s'", tableName, sn, t_id, time_start, time_end)
		count, err := o.Raw(sql).Exec()
		if err != nil {
			log.Println(err.Error())
			return count, err
		}
	} else {
		// 不同的月份
		stas, ends, err := getMonthlyTimeRanges(time_start, time_end)
		if err != nil {
			return nil, errors.New("时间格式错误")
		}
		for i := 0; i < len(stas); i++ {
			fmt.Println(stas[i], ends[i])
			parse, err := time.Parse("2006-01-02 15:04:05", stas[i])
			if err != nil {
				return nil, errors.New("时间格式错误")
			}
			formatMonth := parse.Format("01")
			tableName := "warning" + "_" + strconv.Itoa(parse.Year()) + "_" + formatMonth
			sqls := fmt.Sprintf("delete from %s where t_sn='%s' and t_id=%s and t__ut >= '%s' and t__ut <= '%s'", tableName, sn, t_id, time_start, time_end)
			count, err := o.Raw(sqls).Exec()
			if err != nil {
				log.Println(err.Error())
				return count, err
			}
		}
	}

	return count, nil
}

// DeleteDeviceDataByTimeWaring 删除对应报警时间范围
func DeleteDeviceDataByTimeWaring(sn, t_id, time_start, time_end string) (sql.Result, error) {
	o := orm.NewOrm()
	sprintf := fmt.Sprintf("delete from warning where t_sn='%s' and t_id=%s and t__ut >= '%s' and t__ut <= '%s'", sn, t_id, time_start, time_end)
	log.Println(sprintf)
	count, err := o.Raw(sprintf).Exec()
	if err != nil {
		log.Println(err.Error())
		return count, err
	}
	startTime, err := time.Parse("2006-01-02 15:04:05", time_start)
	if err != nil {
		return nil, errors.New("时间格式错误")
	}
	endTime, err := time.Parse("2006-01-02 15:04:05", time_end)
	if err != nil {
		return nil, errors.New("时间格式错误")
	}
	//判断开始时间和结束时间是否属于同一个月
	if startTime.Month() == endTime.Month() && startTime.Year() == endTime.Year() {
		tableName := "warning" + "_" + strconv.Itoa(startTime.Year()) + "_" + strconv.Itoa(int(startTime.Month()))
		sql := fmt.Sprintf("delete from %s where t_sn='%s' and t_id='%s' and t__ut >= '%s' and t__ut <= '%s'", tableName, sn, t_id, time_start, time_end)
		count, err := o.Raw(sql).Exec()
		if err != nil {
			log.Println(err.Error())
			return count, err
		}
	} else {
		// 不同的月份
		stas, ends, err := getMonthlyTimeRanges(time_start, time_end)
		if err != nil {
			return nil, errors.New("时间格式错误")
		}
		for i := 0; i < len(stas); i++ {
			fmt.Println(stas[i], ends[i])
			parse, err := time.Parse("2006-01-02 15:04:05", stas[i])
			if err != nil {
				return nil, errors.New("时间格式错误")
			}
			tableName := "warning" + "_" + strconv.Itoa(parse.Year()) + "_" + strconv.Itoa(int(parse.Month()))
			sqls := fmt.Sprintf("delete from %s where t_sn=%s and t_id='%s' and t__ut >= '%s' and t__ut <= '%s'", tableName, sn, t_id, stas[i], ends[i])
			count, err := o.Raw(sqls).Exec()
			if err != nil {
				log.Println(err.Error())
				return count, err
			}
		}
	}
	return nil, nil
}
func getMonthlyTimeRanges(startStr, endStr string) ([]string, []string, error) {
	startTime, err := time.Parse("2006-01-02 15:04:05", startStr)
	if err != nil {
		return nil, nil, err
	}

	endTime, err := time.Parse("2006-01-02 15:04:05", endStr)
	if err != nil {
		return nil, nil, err
	}

	var startMonths, endMonths []string
	currentTime := startTime

	for currentTime.Before(endTime) || currentTime.Equal(endTime) {
		monthStart := time.Date(currentTime.Year(), currentTime.Month(), 1, 0, 0, 0, 0, time.UTC)
		monthEnd := monthStart.AddDate(0, 1, -1)

		if monthStart.Before(startTime) {
			monthStart = startTime
		}
		if monthEnd.After(endTime) {
			monthEnd = endTime
		}

		startMonths = append(startMonths, monthStart.Format("2006-01-02 15:04:05"))
		endMonths = append(endMonths, monthEnd.Format("2006-01-02 15:04:05"))

		currentTime = monthEnd.AddDate(0, 1, 1)
	}

	return startMonths, endMonths, nil
}

// ImportDeviceData 批量导入
func ImportDeviceData(reader *excelize.File, sn string) string {
	sheetName := reader.GetSheetName(0)
	rows, err := reader.GetRows(sheetName)
	o := orm.NewOrm()
	if err != nil {
		panic(any(err.Error()))
	}
	values := make([]string, len(rows)-1)
	for i, row := range rows {
		if i == 0 {
			//跳过第一条头部描述
			continue
		}
		// 定义匹配的解析格式
		format := row[4]
		_, err := time.Parse("2006-01-02 15:04:05", row[4])
		if err != nil {
			layout := "1/2/06 15:04"
			parsedTime, _ := time.Parse(layout, row[4])
			year := parsedTime.Year()
			if year < 100 {
				year += 2000
			}
			parsedTime = time.Date(year, parsedTime.Month(), parsedTime.Day(), parsedTime.Hour(), parsedTime.Minute(), parsedTime.Second(), parsedTime.Nanosecond(), time.UTC)
			format = parsedTime.Format("2006-01-02 15:04:05")

		}
		isrepeat := Read_DeviceData_Execl_ISREPEAT(sn, format, row[0])
		t_sp := Read_device_sensor_parameter(sn, row[0])
		if t_sp == "" {
			sprintf := fmt.Sprintf("当前sn或探头参数ID不存在：%s,%s", sn, row[0])
			return sprintf
		}
		if isrepeat {
			//如果重复就用更新数据
			sql := fmt.Sprintf("UPDATE z_device_data_%s SET t_t=%s,t_rh=%s  WHERE t_time='%s' AND t_id=%s ", sn, row[1], row[2], format, row[0])
			_, err := o.Raw(sql).Exec()
			if err != nil {
				fmt.Println(err.Error())
				return "更新失败"
			}
		} else {
			// t_id,t_t,t_rh,t_time
			//传感器	传感器参数ID	温度	湿度	GPS	采集时间	创建时间
			parse, _ := time.Parse("2006-01-02 15:04:05", format)
			parse = parse.Add(time.Millisecond * 8000)
			parse.Format("2006-01-02 15:04:05")
			values[i-1] = fmt.Sprintf("(%s,%s,%s,%s,'','%s')", row[0], t_sp, row[1], row[2], format)
			sprintf := fmt.Sprintf("insert into z_device_data_%s  (t_id,t_sp,t_t,t_rh,t_site,t_time,create_time) values (%s,%s,%s,%s,'%s','%s','%s')", sn, row[0], t_sp, row[1], row[2], row[3], format, parse.Format("2006-01-02 15:04:05"))
			log.Println(sprintf)
			//执行SQL
			_, err := o.Raw(sprintf).Exec()
			if err != nil {
				fmt.Println("执行失败")
				return "请检查表格是否具有重复数据！！！"
			}
		}
	}
	//sqlStatement := fmt.Sprintf("insert into z_device_data_%s(t_id,t_sp,t_t,t_rh,t_site,t_time) values %s", sn, strings.Join(values, ","))
	//fmt.Println("执行批量导入SQL：", sqlStatement)
	return "导入成功"

}
func Read_DeviceData_Execl_ISREPEAT(sn, t_time, t_id string) bool {
	o := orm.NewOrm()
	var table struct {
		T_id   string
		T_time string
	}
	sql := fmt.Sprintf("select t_id,t_time from z_device_data_%s where t_time = '%s' and t_id=%s", sn, t_time, t_id)
	err := o.Raw(sql).QueryRow(&table)
	if err != nil {
		fmt.Println(err.Error())
		return false
	} else {
		if table.T_id != "" {
			return true
		}
	}
	return false
}

// ExportDeviceData 导出 未完成
/*func ExportDeviceData(sns []string, startTime, endTime string) *os.File {

	f, err := os.Create(fmt.Sprintf("[%s]导出数据.zip", time.Now().Format("2006-01-02")))
	if err != nil {
		fmt.Println(err.Error())
	}
	z := zip.NewWriter(f)

	o := orm.NewOrm()
	sqlStatement := "select * from z_device_data_%s where create_time between %s and %s"
	title := map[string]string{
		"A1": "传感器",
		"B1": "传感器参数ID",
		"C1": "温度",
		"D1": "湿度",
		"E1": "GPS",
		"F1": "采集时间",
		"G1": "创建时间",
	}
	for _, sn := range sns {
		datas := make([]DeviceData_, 0)
		sqlNew := fmt.Sprintf(sqlStatement, sn, startTime, endTime)
		o.Raw(sqlNew).QueryRows(&datas)

		//创建压缩包中的excel
		excelT, err := z.Create(fmt.Sprintf("%s.xlsx", sn))
		if err != nil {
			fmt.Println(err.Error())
		}

		//创建新的excel
		excelNew := excelize.NewFile()
		excelNew.NewSheet(sn)
		//设置表头
		for k, v := range title {
			excelNew.SetCellStr(sn, k, v)
		}
		//设置内容
		for i, v := range datas {

		}
	}
	return f

}
*/
// Update_DeviceSensorData 更新设备数据
func Update_DeviceSensorData(v DeviceData_R, fieldName, val string) error {
	o := orm.NewOrm()
	if fieldName == "t__tlower" {
		split := strings.Split(val, "|")
		i := strings.Split(split[0], "~")
		t__tlower := i[0]
		t__tupper := i[1]
		i2 := strings.Split(split[1], "~")
		t__r_hlower := i2[0]
		t__r_hupper := i2[1]
		sqlStatemt := fmt.Sprintf("update device_sensor_parameter set t__tlower =%s , t__tupper = %s , t__r_hlower = %s , t__r_hupper = %s where id =%d ", t__tlower, t__tupper, t__r_hlower, t__r_hupper, v.T_sp)
		//log.Println(sqlStatemt)
		_, err := o.Raw(sqlStatemt).Exec()
		if err != nil {
			return err
		}
		return nil
	} else {
		sqlStatemt := fmt.Sprintf("update z_device_data_%s set %s = ? where t_id = ? and t_t = ? and t_rh = ? and t_site = ? and create_time = ?", v.T_sn, fieldName)
		log.Println(sqlStatemt)
		log.Println("创建时间：", v.Create_Time)
		_, err := o.Raw(sqlStatemt, val, v.T_id, v.T_t, v.T_rh, v.T_site, v.Create_Time).Exec()
		if err != nil {
			return err
		}
		return nil
	}
}

// 复制添加数据
func Copy_DeviceData(T_sn, T_id string, T_Rh, T_T any, T_Site, CreateTime string) {
	T_sp := Read_device_sensor_parameter(T_sn, T_id)
	o := orm.NewOrm()
	sprintf := fmt.Sprintf("insert into z_device_data_%s(t_id,t_sp,t_time,t_t,t_rh,t_site,create_time) values (?,?,?,?,?,?,?)", T_sn)
	//log.Println(sprintf)
	_, err := o.Raw(sprintf, T_id, T_sp, CreateTime, T_T, T_Rh, T_Site, CreateTime).Exec()
	if err != nil {
		fmt.Println(err.Error())
	}
}
func Read_DeviceData_ById_Year_List(SN string) []orm.ParamsList {
	o := orm.NewOrm()

	var maps_z []orm.ParamsList

	//sql = "SELECT t_name,t_t,t_rh,t_tl,t_tu,t_rhl,t_rhu,t_site,DATE_FORMAT(t_time,'%Y-%c-%d %H:%i:%s') AS t_time  FROM z_device_data_"+SN+" WHERE "+sql_time+" t_id = "+ strconv.Itoa(T_id) +" ORDER BY t_time DESC "
	sql := "SELECT DATE_FORMAT(t_time,\"%m\") AS m ,DATE_FORMAT(t_time,\"%d\") AS d FROM z_device_data_" + SN + "  WHERE t_time > '" + strconv.Itoa(time.Now().Year()) + "-0-0 00:00:00' GROUP BY DATE_FORMAT(t_time,\"%m\");"
	fmt.Println(sql)
	num, err := o.Raw(sql).ValuesList(&maps_z)
	if err == nil && num > 0 {
		fmt.Println(maps_z[0][0]) // slene
	}
	//value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
	//key,_ := strconv.Atoi(maps_z[0][0].(string))
	return maps_z
} //
func Read_DeviceData_ById_Month_List(SN string) []orm.ParamsList {
	o := orm.NewOrm()

	var maps_z []orm.ParamsList
	currentTime := time.Now() //获取当前时间，类型是Go的时间类型Time

	time_x := currentTime.Format("2006-01") + "-00 00:00:00"

	//sql = "SELECT t_name,t_t,t_rh,t_tl,t_tu,t_rhl,t_rhu,t_site,DATE_FORMAT(t_time,'%Y-%c-%d %H:%i:%s') AS t_time  FROM z_device_data_"+SN+" WHERE "+sql_time+" t_id = "+ strconv.Itoa(T_id) +" ORDER BY t_time DESC "
	sql := "SELECT DATE_FORMAT(t_time,\"%d\") AS d FROM z_device_data_" + SN + "  WHERE t_time > '" + time_x + "' GROUP BY DATE_FORMAT(t_time,\"%d\");"
	fmt.Println(sql)
	o.Raw(sql).ValuesList(&maps_z)

	//value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
	//key,_ := strconv.Atoi(maps_z[0][0].(string))
	return maps_z
}
func Read_device_sensor_parameter(SN, t_id string) string {
	o := orm.NewOrm()
	var maps_z []orm.ParamsList

	//sql = "SELECT t_name,t_t,t_rh,t_tl,t_tu,t_rhl,t_rhu,t_site,DATE_FORMAT(t_time,'%Y-%c-%d %H:%i:%s') AS t_time  FROM z_device_data_"+SN+" WHERE "+sql_time+" t_id = "+ strconv.Itoa(T_id) +" ORDER BY t_time DESC "
	sql := "SELECT ID FROM device_sensor_parameter WHERE `t_sn` = '" + SN + "' AND `t_id` = '" + t_id + "' AND `t__state` = '1' ORDER BY `ID` DESC LIMIT 0,2;"
	fmt.Println(sql)
	list, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		return ""
	}
	if list == 0 {
		fmt.Println("没有数据")
		return ""
	} else {
		return maps_z[0][0].(string)
	}
	//value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
	//key,_ := strconv.Atoi(maps_z[0][0].(string))
}

func Read_DeviceData_ById_Day_List(SN string) []orm.ParamsList {
	o := orm.NewOrm()

	var maps_z []orm.ParamsList
	currentTime := time.Now() //获取当前时间，类型是Go的时间类型Time

	time_x := currentTime.Format("2006-01-02") + " 00:00:00"

	//sql = "SELECT t_name,t_t,t_rh,t_tl,t_tu,t_rhl,t_rhu,t_site,DATE_FORMAT(t_time,'%Y-%c-%d %H:%i:%s') AS t_time  FROM z_device_data_"+SN+" WHERE "+sql_time+" t_id = "+ strconv.Itoa(T_id) +" ORDER BY t_time DESC "
	sql := "SELECT DATE_FORMAT(t_time,\"%H\") AS m FROM z_device_data_" + SN + "  WHERE t_time > '" + time_x + "' GROUP BY DATE_FORMAT(t_time,\"%H\");"
	fmt.Println(sql)
	o.Raw(sql).ValuesList(&maps_z)

	//value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
	//key,_ := strconv.Atoi(maps_z[0][0].(string))
	return maps_z
}

func Read_DeviceData_List_GROUP_BY_t_time(SN string, Time_start_ string, Time_end_ string) []orm.ParamsList {
	o := orm.NewOrm()

	var maps_z []orm.ParamsList

	sql_time := ""
	if len(Time_start_) > 1 {
		sql_time += " t_time >= '" + Time_start_ + "' AND " + " t_time <= '" + Time_end_ + "' "
	}

	sql := "SELECT DATE_FORMAT(t_time,'%Y-%c-%d %H:%i:%s') AS t_time FROM z_device_data_" + SN + " WHERE " + sql_time + "   GROUP BY t_time  ORDER BY t_time DESC "
	fmt.Println(sql)
	o.Raw(sql).ValuesList(&maps_z)
	return maps_z
}

// GetCompanyBySn 根据sn查询公司名称
func GetCompanyBySn(sn string) (string, error) {
	o := orm.NewOrm()
	sqls := fmt.Sprintf("SELECT t_pid FROM `device` WHERE t_sn='%s'", sn)
	var TPid string
	err := o.Raw(sqls).QueryRow(&TPid)
	if err != nil {
		return "", err
	}
	sqlCompany := fmt.Sprintf("SELECT t_name FROM `company` WHERE id='%s'", TPid)
	var TName string
	err = o.Raw(sqlCompany).QueryRow(&TName)
	if err != nil {
		return "", err
	}
	TName = TName + "[" + TPid + "]"
	return TName, nil
}