Cold_Api/models/Device/DeviceData.go

package Device

import (
	"Cold_Api/conf"
	"Cold_Api/controllers/lib"
	"encoding/json"
	"errors"
	"fmt"
	"github.com/astaxie/beego/cache"
	_ "github.com/astaxie/beego/cache/redis"
	"github.com/astaxie/beego/logs"
	"github.com/beego/beego/v2/adapter/orm"
	orm2 "github.com/beego/beego/v2/client/orm"
	_ "github.com/go-sql-driver/mysql"
	"sort"
	"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, 1) NULL DEFAULT NULL," +
	"`t_rh` double(6, 1) 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 // 采集时间
}

// DeviceData_R1 辅助定位更新redis 结构体
type DeviceData_R1 struct {
	T_t    float32   // 温度
	T_rh   float32   // 湿度
	T_Site string    // GPS
	T_time time.Time // 采集时间
	T_sp   int       // 传感器参数ID
	T_tp   int       // 传感器参数ID
	//create_time
}

// 模板
type DeviceData_R struct {
	T_sn     string  // sn
	T_id     int     // 传感器id
	T_name   string  // 传感器名称
	T_t      float32 // 温度
	T_rh     float32 // 湿度
	T_site   string  // GPS
	T_tl     float32 // 温度下限
	T_tu     float32 // 温度上限
	T_rhl    float32 // 湿度下限
	T_rhu    float32 // 湿度上限
	T_time   string  // 采集时间
	T_sp     int     // 传感器参数id
	T_ist    int     // 温度   1开启   2关闭
	T_ish    int     // 湿度   1开启   2关闭
	T_free   int     // 空库
	T_remark string  // 备注
	Sorts    int     // 排序
}
type DeviceData_R2 struct {
	DeviceData_
	Sn     string `json:"sn"`
	T_name string `json:"t_name"` // 设备名称
	Time   string `json:"t_time"`
}

// 模板
type DeviceData_Docking struct {
	T_monitor  int     // 监控状态 0 未监控 1 监控
	T_online   int     // 在线状态 0 未启用  1 在线  2 离线
	T_online_s int     // 在线状态-备用  0 未启用  1 在线  2 离线
	T_sn       string  // sn
	T_id       int     // 传感器id
	T_name     string  // 传感器名称
	T_t        float32 // 温度
	T_rh       float32 // 湿度
	T_site     string  // GPS
	T_tl       float32 // 温度下限
	T_tu       float32 // 温度上限
	T_rhl      float32 // 湿度下限
	T_rhu      float32 // 湿度上限
	T_time     string  // 采集时间
	T_sp       int     // 传感器参数id
}

type DeviceData_Pdf struct {
	T_time string   // sn
	T_id1  *float32 // 传感器id1温度
	T_id2  *float32 // 传感器id2温度
}

type DeviceData_BackUp struct {
	T_sn   string
	T_id   int    // 传感器id
	T_name string // 传感器名称
	T_date string // 采集日期
	T_num  int    // 数据量
}

//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"
		logs.Error(errMsg, err)
		panic(errMsg)
	}
}

func DeviceData_ToDeviceData_R(d Device, r DeviceData_) (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.T_sp = r.T_sp
	sp := Read_DeviceSensorParameter_Map_Get(r.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
	t.T_free = sp.T_free
	if sp.T_free == 1 {
		t.T_remark = "空库"
	} else {
		t.T_remark = "-"
	}

	t.T_sn = d.T_sn
	t.T_ist = d.T_ist
	t.T_ish = d.T_ish

	return t
}

func DeviceDataToDeviceData_R2(d Device, sp DeviceSensorParameter_R, r DeviceData_) (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.T_sp = r.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

	// 设备信息
	t.T_ist = d.T_ist
	t.T_ish = d.T_ish

	return t
}

// ---------------- Redis -------------------
// Redis_Device_Set(m.T_sn,m) // Redis 更新缓存
func RedisDeviceData_Set(key string, r DeviceData_) (err error) {
	if redis_DeviceData.IsExist(key) {
		var t DeviceData_
		v := redis_DeviceData.Get(key)
		json.Unmarshal(v.([]byte), &t)
		if t.T_time.Unix() > r.T_time.Unix() {
			// 储存的 是最新数据
			return
		}
	}

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

// 辅助定位设置redis数据
func RedisDeviceData_SetAssistedPositioning(T_sn string, T_id int, r DeviceData_R1) (err error) {
	key := T_sn + "|" + strconv.Itoa(T_id)

	if redis_DeviceData.IsExist(key) {
		var t DeviceData_R1
		v := redis_DeviceData.Get(key)
		json.Unmarshal(v.([]byte), &t)
		// 防止时间溢出
		if time.Now().Unix() <= r.T_time.Unix() {
			r.T_time = time.Now()
		}

	}

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

func RedisDeviceData_Get(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
}

// 辅助定位获取redis数据
func AssistedPositioning_Get(T_sn string, T_id int) (r DeviceData_R1, is bool) {
	key := T_sn + "|" + strconv.Itoa(T_id)
	if redis_DeviceData.IsExist(key) {
		v := redis_DeviceData.Get(key)
		json.Unmarshal(v.([]byte), &r)
		return r, true
	}
	return DeviceData_R1{}, false
}

// -------------------------------------------------------
// 创建数据库  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 {
		logs.Error(lib.FuncName(), err)
		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 + ";"
	logs.Info("DELETE_DeviceDatar sql:", sql)
	o := orm.NewOrm()
	_, err := o.Raw(sql).Exec()
	if err != nil {
		logs.Error(lib.FuncName(), err)
		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){
	//	return true
	//}

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

	o := orm.NewOrm()

	// 检查 超过时间，查询 数据库
	logs.Info("Add_DeviceData 时间差s：", time.Now().Unix()-v.T_time.Unix())
	if time.Now().Unix()-v.T_time.Unix() >= 60*40 {
		// 查看是否 有记录
		var maps_z []orm2.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)
		logs.Info("检查 超过时间，查询 数据库 SQL:", sql_c)
		_, err := o.Raw(sql_c).ValuesList(&maps_z)
		if err != nil {
			logs.Info(err)
			return false
		}
		if lib.To_int(maps_z[0][0]) > 0 {
			logs.Info("存在记录，跳过！")
			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，以提高批量执行的速度
	logs.Debug(sql)
	res, err := o.Raw(sql).Exec()
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return false
	}
	res.RowsAffected()

	return true
}

// 辅助定位更新缓存
func Update_AssistedPositioning(T_sn, longitude, latitude, T_time string) error {
	o := orm.NewOrm()
	tableName := "z_device_data_" + T_sn
	var deviceData []DeviceData_
	tsn, i := Read_DeviceSensor_ByTsn(T_sn)
	if i == 0 {
		return errors.New("获取设备失败")
	}
	for _, v := range tsn {
		r, is := AssistedPositioning_Get(v.T_sn, v.T_id)
		if is {
			r.T_Site = longitude + "," + latitude
			err := RedisDeviceData_SetAssistedPositioning(v.T_sn, v.T_id, r)
			if err != nil {
				return err
			}
			sql := "SELECT * FROM " + tableName + " ORDER BY t_time DESC LIMIT 1"
			fmt.Println(sql)
			rows, err := o.Raw(sql).QueryRows(&deviceData)
			if err != nil {
				logs.Error(lib.FuncName(), err)
				return err
			}
			if rows > 0 {
				for _, data := range deviceData {
					format := data.T_time.Format("2006-01-02 15:04:05")
					sql = "UPDATE " + tableName + " SET t_site = '" + longitude + "," + latitude + "' WHERE t_id = " + strconv.Itoa(v.T_id) + " AND t_time = '" + format + "'"
					_, err := o.Raw(sql).Exec()
					if err != nil {
						logs.Error(lib.FuncName(), err)
						return err
					}
				}
			}
		}
	}
	return nil
}

func Read_DeviceData_ById_List(SN string, T_id int, Time_start_ string, Time_end_ string, page int, page_z int) ([]DeviceData_R, int) {
	o := orm.NewOrm()
	var maps []DeviceData_
	var maps_z []orm2.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)
	logs.Debug(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r, 0
	}
	if len(maps_z) == 0 {
		return r, 0
	}

	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_" + SN + " WHERE " + sql_time + " t_id = " + strconv.Itoa(T_id) + " ORDER BY t_time1 DESC,t_id DESC "
	if page_z != 9999 {
		sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(page_z)
	}

	logs.Debug(sql)
	_, err = o.Raw(sql).QueryRows(&maps)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r, 0
	}

	//value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
	key, _ := strconv.Atoi(maps_z[0][0].(string))
	device, err := Read_Device_ByT_sn(SN)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	for _, v := range maps {
		r = append(r, DeviceData_ToDeviceData_R(device, v))
	}

	return r, key
}
func Read_DeviceData_ById_List_OrderByTimeAes(SN string, T_id int, Time_start_ string, Time_end_ string, page int, page_z int) ([]DeviceData_R, int) {
	o := orm.NewOrm()
	var maps []DeviceData_
	var maps_z []orm2.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)
	logs.Debug(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r, 0
	}
	if len(maps_z) == 0 {
		return r, 0
	}

	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_" + SN + " WHERE " + sql_time + " t_id = " + strconv.Itoa(T_id) + " ORDER BY t_time1,t_id DESC "
	if page_z != 9999 {
		sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(page_z)
	}

	logs.Debug(sql)
	_, err = o.Raw(sql).QueryRows(&maps)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r, 0
	}

	//value, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", cnt), 64)
	key, _ := strconv.Atoi(maps_z[0][0].(string))
	device, err := Read_Device_ByT_sn(SN)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	for _, v := range maps {
		r = append(r, DeviceData_ToDeviceData_R(device, v))
	}

	return r, key
}

func Read_DeviceData_ByIds(SN string, ids []int, Time_start_ string, Time_end_ string, page int, page_z int) ([]DeviceData_R, int) {
	o := orm.NewOrm()
	var maps []DeviceData_
	var maps_z []orm2.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 in (" + lib.IntListToDotStr(ids) + ")"
	logs.Debug(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r, 0
	}
	if len(maps_z) == 0 {
		return r, 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,t_time AS t_time1 FROM z_device_data_" + SN + " WHERE " + sql_time + " t_id in (" + lib.IntListToDotStr(ids) + ") ORDER BY t_time1 DESC,t_id ASC "
	if page_z != 9999 {
		sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(page_z)
	}

	logs.Debug(sql)
	_, err = o.Raw(sql).QueryRows(&maps)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r, 0
	}

	key, _ := strconv.Atoi(maps_z[0][0].(string))
	device, err := Read_Device_ByT_sn(SN)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	for _, v := range maps {
		r = append(r, DeviceData_ToDeviceData_R(device, v))
	}

	return r, key
}
func Read_DeviceData_ByIds_ForPDF(SN string, ids []int, Time_start_ string, Time_end_ string) []DeviceData_Pdf {
	o := orm.NewOrm()
	var r []DeviceData_Pdf
	if len(ids) == 0 || len(ids) > 2 {
		return r
	}

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

	selectSql := ""
	for i, v := range ids {
		selectSql += fmt.Sprintf("MAX(CASE WHEN t_id = %d THEN t_t END) AS t_id%d,", v, i+1)
	}
	selectSql = strings.Trim(selectSql, ",")

	sql := "SELECT DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_time," + selectSql + " FROM z_device_data_" + SN + " WHERE " + sql_time + " t_id in (" + lib.IntListToDotStr(ids) + ") GROUP BY t_time ORDER BY t_time"

	logs.Debug(sql)
	_, err := o.Raw(sql).QueryRows(&r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return r
	}

	return r
}

func Read_SqlRawL(T_SQL string, T_data []string) (string, []orm2.Params) {
	o := orm.NewOrm()
	var lists []orm2.Params
	logs.Debug(T_SQL)
	_, err := o.Raw(T_SQL, T_data).Values(&lists)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		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) ([]DeviceData_R, int64) {
	T_snid_list := strings.Split(strings.Trim(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) == 3 {
			Read_DeviceSensorParameter_All_Map(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)
			for i, _ := range r_maps {
				atoi, _ := strconv.Atoi(sn_id[2])
				r_maps[i].Sorts = atoi
			}
			maps = append(maps, r_maps...)
			maps_num = maps_num + int64(r_maps_num)

			logs.Debug("加载数据：", sn_id[0], sn_id[1], r_maps_num)
		} else if len(sn_id) == 2 {
			Read_DeviceSensorParameter_All_Map(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)
			maps = append(maps, r_maps...)
			maps_num = maps_num + int64(r_maps_num)

			logs.Debug("加载数据：", sn_id[0], sn_id[1], r_maps_num)
		}
	}

	if maps_num == 0 {
		return maps, maps_num
	}

	// 倒序
	sort.Slice(maps, func(i, j int) bool {
		if maps[i].T_time == maps[j].T_time {
			return maps[i].Sorts < maps[j].Sorts
		}
		return maps[i].T_time > maps[j].T_time
	})
	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 offset > offset_z {
		offset = offset_z
	}

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

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

	var maps_z []orm2.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\");"
	logs.Debug(sql)
	num, err := o.Raw(sql).ValuesList(&maps_z)
	if err == nil && num > 0 {
		logs.Debug(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) []orm2.ParamsList {
	o := orm.NewOrm()

	var maps_z []orm2.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\");"
	logs.Debug(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_ById_Day_List(SN string) []orm2.ParamsList {
	o := orm.NewOrm()

	var maps_z []orm2.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\");"
	logs.Debug(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) []orm2.ParamsList {
	o := orm.NewOrm()

	var maps_z []orm2.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 "
	logs.Debug(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	return maps_z
}

func Read_DeviceData(T_sn string, T_id int) (t DeviceData_) {
	key := T_sn + "|" + strconv.Itoa(T_id)
	if t, is := RedisDeviceData_Get(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).QueryRows(&maps)

	if err != nil {
		logs.Error(lib.FuncName(), err)
		return t
	}

	if len(maps) == 0 {
		RedisDeviceData_Set(key, t)
		return t
	}

	RedisDeviceData_Set(key, maps[0])

	return maps[0]
}

// 获取数据备份
func Read_DeviceData_BackUp_List(T_snid string, Time_start_ string, Time_end_ string, page int, page_z int) ([]DeviceData_BackUp, int64) {
	T_snid_list := strings.Split(T_snid, "|")
	var maps []DeviceData_BackUp
	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 {

			r_maps, r_maps_num := Read_DeviceData_BackUp(sn_id[0], lib.To_int(sn_id[1]), Time_start_, Time_end_)

			maps = append(maps, r_maps...)
			maps_num = maps_num + int64(r_maps_num)

			logs.Debug("加载数据：", 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 offset > offset_z {
		offset = offset_z
	}

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

func Read_DeviceData_BackUp(T_sn string, T_id int, Time_start_ string, Time_end_ string) ([]DeviceData_BackUp, int) {
	o := orm.NewOrm()
	var maps []DeviceData_BackUp

	sql_time := ""

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

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

	sql := "SELECT t_id,date_format(t_time, '%Y-%m-%d') t_date, count(*) t_num FROM z_device_data_" + T_sn + " WHERE " + sql_time + " t_id = " + strconv.Itoa(T_id) + " GROUP BY t_date ORDER BY t_time DESC "

	logs.Debug(sql)
	_, err := o.Raw(sql).QueryRows(&maps)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return maps, 0
	}

	deviceSensor, _ := Read_DeviceSensor_ByT_sn(T_sn, T_id)

	for i := 0; i < len(maps); i++ {
		maps[i].T_sn = T_sn
		maps[i].T_name = deviceSensor.T_name
	}

	return maps, len(maps)
}

func Read_DeviceData_By_Time(T_sn string, T_id int, Time string) (t DeviceData_) {

	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) + " AND t_time <= '" + Time + "'" + " 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
}
func Read_DeviceData_Start_Time(T_sn string, T_id int, StartTime, EndTime string) (t []DeviceData_, count int64) {
	o := orm.NewOrm()
	var maps []DeviceData_
	var sql string
	if len(StartTime) > 0 && len(EndTime) > 0 {
		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) + " AND t_time >= '" + StartTime + "'" + " AND t_time <= '" + EndTime + "'" + " ORDER BY t_time1 DESC"
	} else if len(StartTime) > 0 && len(EndTime) == 0 {
		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) + " AND t_time >= '" + StartTime + "'" + " ORDER BY t_time1 DESC"
	}
	logs.Debug(sql)
	count, err := o.Raw(sql).QueryRows(&maps)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return t, 0
	}
	return maps, count
}

// 获取最新轨迹信息
func GetNewLocus(T_sn string, T_id int, list *[]DeviceData_) error {
	task, err := Read_DeviceTask_List_By_Condition(T_sn)
	if err != nil {
		logs.Error(lib.FuncName(), err.Error())
		return errors.New("设备未开启监控")
	}
	// 当最新设备状态为1时，证明设备正在启动中，查询大于开始时间的所有轨迹，设备状态为2时，查询开始时间到结束时间的轨迹
	if task.T_State == 1 {
		deviceData, count := Read_DeviceData_Start_Time(T_sn, T_id, task.T_Ut_start.Format("2006-01-02 15:04:05"), "")
		if count >= 0 {
			*list = appendUniqueDeviceData(*list, deviceData)
		} else {
			logs.Error(lib.FuncName(), err)
			return errors.New("获取设备轨迹失败")
		}

	} else if task.T_State == 2 {
		deviceData, count := Read_DeviceData_Start_Time(T_sn, T_id, task.T_Ut_start.Format("2006-01-02 15:04:05"), task.T_Ut_end.Format("2006-01-02 15:04:05"))
		if count >= 0 {
			*list = appendUniqueDeviceData(*list, deviceData)
		} else {
			logs.Error(lib.FuncName(), err)
			return errors.New("获取设备轨迹失败")
		}
	}
	return nil
}

// 去重并追加设备数据
func appendUniqueDeviceData(list []DeviceData_, newData []DeviceData_) []DeviceData_ {
	uniqueMap := make(map[time.Time]bool)
	for _, d := range list {
		uniqueMap[d.T_time] = true
	}
	for _, d := range newData {
		if !uniqueMap[d.T_time] {
			if len(d.T_site) > 0 && d.T_site != "0,0" {
				split := strings.Split(d.T_site, ",")
				defer func() {
					if r := recover(); r != nil {
						fmt.Println("数组下标越界:", r)
					}
				}()
				Lng := split[0]
				Lat := split[1]
				Lngs, _ := strconv.ParseFloat(Lng, 64)
				Lats, _ := strconv.ParseFloat(Lat, 64)
				mLng, mLat := lib.Wgs84ToGcj02(Lngs, Lats)
				d.T_site = fmt.Sprintf("%v,%v", mLng, mLat)
				list = append(list, d)
			}
		}
	}
	//排序
	sort.Slice(list, func(i, j int) bool {
		return list[i].T_time.After(list[j].T_time)
	})
	return list
}