Cold_Api/models/Device/DeviceData.go

package Device

import (
	"Cold_Api/conf"
	"Cold_Api/controllers/lib"
	"Cold_Api/logs"
	"encoding/json"
	"fmt"
	"github.com/astaxie/beego/cache"
	_ "github.com/astaxie/beego/cache/redis"
	"github.com/beego/beego/v2/adapter/orm"
	orm2 "github.com/beego/beego/v2/client/orm"
	_ "github.com/go-sql-driver/mysql"
	"strconv"
	"strings"
	"time"
)

// 模板
type DeviceData struct {
	Id int `orm:"column(ID);size(11);auto;pk"`

	T_id   string    `orm:"size(256);null"`        // 标题
	T_t    float32   `orm:"size(10);null"`         // 温度
	T_rh   float32   `orm:"size(10);null"`         // 湿度
	T_time time.Time `orm:"type(timestamp);null;"` // 采集时间

	CreateTime time.Time `orm:"column(create_time);type(timestamp);null;auto_now_add"` //auto_now_add 第一次保存时才设置时间 UpdateTime time.Time `orm:"column(update_time);type(timestamp);null;auto_now"`   //auto_now 每次 model 保存时都会对时间自动更新
}

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

var redisCache_DeviceData cache.Cache

func init() {

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

	}

}

func DeviceData_Set(key string) (err error) {

	err = redisCache_DeviceData.Put(key, "", 2*time.Hour)
	if err != nil {
		fmt.Println("set key:", key, err)
	}
	return
}

// ---------------- Redis -------------------
//Redis_Set(m.T_sn,m) // Redis 更新缓存
func RedisDeviceData_Set(key string, r DeviceData_New) (err error) {
	//json序列化
	str, err := json.Marshal(r)
	if err != nil {
		fmt.Print(err)
		return
	}

	err = redisCache_DeviceData.Put(key, str, 1*time.Hour)
	if err != nil {
		fmt.Println("set key:", key, ",value:", str, err)
	}
	return
}

//if r,is :=Redis_Get(T_sn);is{
//return r,nil
//}
func RedisDeviceData_Get(key string) (r DeviceData_New, is bool) {
	v := redisCache_DeviceData.Get(key)
	json.Unmarshal(v.([]byte), &r)
	return r, true
}
func RedisDeviceData_Get_(key string) (r DeviceData_New, is bool) {
	if redisCache_DeviceData.IsExist(key) {
		v := redisCache_DeviceData.Get(key)
		json.Unmarshal(v.([]byte), &r)
		return r, true
	}

	return DeviceData_New{}, false
}

func Redis_Set_DeviceData_Time(key string) (err error) {

	err = redisCache_DeviceData.Put(key, "", 40*time.Minute)
	if err != nil {
		fmt.Println("set key:", key, err)
	}
	return
}

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

	sql := "CREATE TABLE IF NOT EXISTS `Z_DeviceData_" + SN + "` ( " +
		"  `ID` int(11) NOT NULL AUTO_INCREMENT," +
		"	`t_name` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL," +
		"	`t_id` int(11) NULL DEFAULT NULL," +
		"	`t_t` double(6, 2) NULL DEFAULT NULL," +
		"	`t_rh` double(6, 2) NULL DEFAULT NULL," +
		"	`t_tl` double(6, 2) NULL DEFAULT NULL," +
		"	`t_tu` double(6, 2) NULL DEFAULT NULL," +
		"	`t_rhl` double(6, 2) NULL DEFAULT NULL," +
		"	`t_rhu` double(6, 2) NULL DEFAULT NULL," +
		"	`t_site` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL," +
		"	`t_battery` int(5) NULL DEFAULT NULL," +
		"	`t_time` datetime(0) NULL DEFAULT NULL," +
		"	`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0)," +
		"	PRIMARY KEY (`ID`) USING BTREE" +
		") ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;"
	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_DeviceData_" + SN
	sql := "ALTER TABLE Z_DeviceData_" + SN + " RENAME TO " + "Z_DeviceData_" + SN + "_dle_" + timeStr + ";"
	logs.Println("DELETE_DeviceDatar:", sql)
	o := orm.NewOrm()
	_, err := o.Raw(sql).Exec()
	if err != nil {
		return false
	}
	return true
}

// ---------------- 特殊方法 ------------------
type DeviceData_T struct {
	T_name    string    // 标题
	T_id      int       // ID
	T_t       float32   // 温度
	T_rh      float32   // 湿度
	T_Tlower  float32   // 湿度
	T_Tupper  float32   // 湿度
	T_RHlower float32   // 湿度
	T_RHupper float32   // 湿度
	T_Site    string    // GPS
	T_Dattery int       // 电量
	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 redisCache_DeviceData.IsExist(key_time) {
		//println("找到key:",key)
		return true
	}

	o := orm.NewOrm()

	// 检查 超过时间，查询 数据库
	logs.Println("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_DeviceData_" + SN + " WHERE t_time = '" + v.T_time.Format("2006-01-02 15:04:05") + "' AND t_id = " + strconv.Itoa(v.T_id)
		logs.Println("检查 超过时间，查询 数据库 SQL:", sql_c)
		_, err := o.Raw(sql_c).ValuesList(&maps_z)
		if err != nil {
			logs.Println(err)
			return false
		}
		//fmt.Println("maps_z[0][0]:",maps_z[0][0])
		if lib.To_int(maps_z[0][0]) > 0 {
			logs.Println("存在记录，跳过！")
			return true
		}
	}

	T_Tlower := strconv.FormatFloat(float64(v.T_Tlower), 'f', -1, 64)
	T_Tupper := strconv.FormatFloat(float64(v.T_Tupper), 'f', -1, 64)
	T_RHlower := strconv.FormatFloat(float64(v.T_RHlower), 'f', -1, 64)
	T_RHupper := strconv.FormatFloat(float64(v.T_RHupper), 'f', -1, 64)

	// 开始插入数据
	sql := "INSERT INTO Z_DeviceData_" + SN + " (`t_name`, `t_id`, `t_t`, `t_rh`, `t_tl`,`t_tu`,`t_rhl`,`t_rhu`, `t_site`,`t_battery`, `t_time`) " +
		"VALUES ('" + v.T_name + "'," + strconv.Itoa(v.T_id) + ", " + lib.To_string(v.T_t) + ", " + lib.To_string(v.T_rh) + "," + T_Tlower + "," + T_Tupper + "," + T_RHlower + "," + T_RHupper + ", '" + v.T_Site + "'," + strconv.Itoa(v.T_Dattery) + ", '" + 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()
	// 存在 写入 redis
	Redis_Set_DeviceData_Time(key_time)

	return true
}

///---------------
type DeviceData_ struct {
	T_name    string  `orm:"column(t_name);size(256);null"`        // 标题
	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_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);type(timestamp);null;"` //
	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;"`                 //
	T_Dattery int       `orm:"column(t_dattery);size(10);null"`      // 电量
	T_time    time.Time `orm:"column(t_time);type(timestamp);null;"` // 采集时间
}

//
func Read_DeviceSensorData_ById_List(SN string, T_id int, Time_start_ string, Time_end_ string, page int, page_z int) ([]DeviceData_, int) {
	o := orm.NewOrm()
	var maps []DeviceData_
	var maps_z []orm2.ParamsList
	pagez := page_z

	var offset int
	if page <= 1 {
		offset = 0
	} else {
		page -= 1
		offset = page * pagez
	}

	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(ID) FROM Z_DeviceData_" + SN + " WHERE " + sql_time + " t_id = " + strconv.Itoa(T_id)
	fmt.Println(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		return maps, 0
	}
	if len(maps_z) == 0 {
		return maps, 0
	}
	//fmt.Println("maps_z；",maps_z[0][0])
	sql = "SELECT t_name,t_id,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,t_time AS t_time1  FROM Z_DeviceData_" + SN + " WHERE " + sql_time + " t_id = " + strconv.Itoa(T_id) + " ORDER BY t_time1 DESC "
	if page_z != 9999 {
		sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(pagez)
	}

	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))
	return maps, key
}

//
func Read_SqlRaw(T_SQL string) []orm2.Params {
	o := orm.NewOrm()
	var lists []orm2.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, []orm2.Params) {
	o := orm.NewOrm()
	var lists []orm2.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_DeviceSensorData_ById_New(SN string, T_id int) (DeviceData_New, bool) {
	o := orm.NewOrm()
	var maps DeviceData_New

	key_data := SN + "|" + strconv.Itoa(T_id)
	if redisCache_DeviceData.IsExist(key_data) {
		r, _ := RedisDeviceData_Get(key_data)
		//println("Redis_Get  OK")
		return r, true
	}

	//fmt.Println("maps_z；",maps_z[0][0])
	sql := "SELECT t_name,t_t,t_rh,t_tl,t_tu,t_rhl,t_rhu,t_site,t_time  FROM Z_DeviceData_" + SN + " WHERE " + " t_id = " + strconv.Itoa(T_id) + " ORDER BY t_time DESC "
	sql = sql + " LIMIT 0,1 "

	fmt.Println(sql)
	err := o.Raw(sql).QueryRow(&maps)
	if err != nil {
		fmt.Println(err)
		return maps, false
	}
	fmt.Println("T_time:", maps.T_time)
	RedisDeviceData_Set(key_data, maps)
	return maps, true
}

func Read_DeviceSensorData_By_T_snid_List(T_snid string, Time_start_ string, Time_end_ string, page int, page_z int) ([]DeviceData_, int) {
	T_snid_list := strings.Split(T_snid, "|")
	var maps []DeviceData_
	var maps_num int
	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_DeviceSensorData_ById_List(sn_id[0], lib.To_int(sn_id[1]), Time_start_, Time_end_, 0, 9999)
			for i, _ := range r_maps {
				r_maps[i].T_sn = sn_id[0]
			}
			maps = append(maps, r_maps...)
			maps_num = maps_num + 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 < offset_z {
		offset_z = 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
}

func Read_V2_DeviceSensorData_By_T_snid_List(T_snid string, Time_start_ string, Time_end_ string, page int, page_z int) ([]DeviceData_, int64) {
	T_snid_list := strings.Split(T_snid, "|")
	var maps []DeviceData_
	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_DeviceSensorData_ById_List(sn_id[0], lib.To_int(sn_id[1]), Time_start_, Time_end_, 0, 9999)
			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
}

//
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_DeviceData_"+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_DeviceData_" + 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) []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_DeviceData_"+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_DeviceData_" + 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_DeviceSensorData_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_DeviceData_"+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_DeviceData_" + 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_DeviceSensorData_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_DeviceData_" + 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
}