ColdVerify_server/models/Device/DeviceData.go

package Device

import (
	"ColdVerify_server/conf"
	"ColdVerify_server/lib"
	"ColdVerify_server/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 "device_data" // 数据库名称   // ************** 替换 FormulaList **************
}

var redisCache_DeviceData cache.Cache
var redisCache_DeviceDataJPG 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)

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

	}

	redisCache_DeviceDataJPG, err = cache.NewCache("redis", configJPG)
	if err != nil || redisCache_DeviceDataJPG == nil {
		errMsg := "failed to init redis"
		logs.Println(errMsg, err)

	}
}

func DeviceData_Set(key string) (err error) {

	err = redisCache_DeviceData.Put(key, "", 2*time.Hour)
	if err != nil {
		logs.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 {
		logs.Error(lib.FuncName(), err)
		return
	}

	err = redisCache_DeviceData.Put(key, str, 24*time.Hour)
	if err != nil {
		logs.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
}

type DeviceCount struct {
	T_id string `json:"T_id"`
	T_sn string `json:"T_sn"`
	Num  int    `json:"Num"`
}

type DeviceDataJPG struct {
	State  int           `json:"State"` //1:生成中 2:已完成 3:失败
	Msg    string        `json:"Msg"`
	Url    string        `json:"Url"` //url
	Device []DeviceCount `json:"Device"`
}

// ---------------- Redis -------------------
// Redis_Set(m.T_sn,m) // Redis 更新缓存
func Redis_DeviceDataJPG_Set(key string, r DeviceDataJPG) (err error) {
	//json序列化
	str, err := json.Marshal(r)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return
	}
	err = redisCache_DeviceDataJPG.Put(key, str, 5*time.Minute)
	if err != nil {
		logs.Println("set key:", key, ",value:", str, err)
	}
	return
}

// if r,is :=Redis_Get(T_sn);is{
// return r,nil
// }
func Redis_DeviceDataJPG_Get(key string) (r DeviceDataJPG, is bool) {
	if redisCache_DeviceDataJPG.IsExist(key) {
		logs.Println("找到key:", key)
		v := redisCache_DeviceDataJPG.Get(key)
		json.Unmarshal(v.([]byte), &r)
		return r, true
	}
	logs.Println("没有 找到key:", key)
	return DeviceDataJPG{}, false
}
func Redis_DeviceDataJPG_Del(key string) (err error) {
	err = redisCache_DeviceDataJPG.Delete(key)
	return
}

// 创建数据库  Device.CREATE_DeviceData("")
func CREATE_DeviceData(SN string) bool {
	o := orm.NewOrm()

	sql := "CREATE TABLE IF NOT EXISTS `z_device_data_" + SN + "` ( " +
		"  `ID` int(11) NOT NULL AUTO_INCREMENT," +
		"	`t_id` int(11) NULL DEFAULT NULL," +
		"	`t_t` float(6, 1) NULL DEFAULT NULL," +
		"	`t_rh` float(6, 1) 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;"
	_, err := o.Raw(sql).Exec()
	if err != nil {
		return false
	}

	return true
}

// ---------------- 特殊方法 -------------------
// 获取 ById
//func Read_List_ById(id int) (r DeviceData) {
//	o := orm.NewOrm()
//
//	return r
//}
//
//// 添加
//func Add_DeviceData(SN string, t_id string, t_t string, t_rh string, t_time string) bool {
//	key := SN + "|" + t_id + "|" + t_time
//	//logs.Println(key)
//	if redisCache_DeviceData.IsExist(key) {
//		//println("找到key:",key)
//		return true
//	}
//	//println("没有 找到key:",key)
//
//	o := orm.NewOrm()
//	//  查看是否 有记录
//	var maps_z []orm2.ParamsList
//
//	sql := "SELECT COUNT(*) FROM z_devicedata_" + SN + " WHERE t_time = '" + t_time + "' AND t_id = " + t_id
//	//logs.Println(sql)
//	_, err := o.Raw(sql).ValuesList(&maps_z)
//
//	if err != nil {
//		logs.Error(lib.FuncName(),err)
//		return false
//	}
//	//logs.Println("maps_z[0][0]:",maps_z[0][0])
//	if lib.To_int(maps_z[0][0]) > 0 {
//		// 存在 写入 redis
//		DeviceData_Set(key)
//		return true
//	}
//	// 开始插入数据
//	sql = "INSERT INTO z_devicedata_" + SN + " (`t_id`, `t_t`, `t_rh`, `t_time`) VALUES (" + t_id + ", " + t_t + ", " + t_rh + ", '" + t_time + "')"
//	//  这里有时间优化  用于一次 prepare 多次 exec，以提高批量执行的速度
//	//logs.Println(sql)
//	res, err := o.Raw(sql).Exec()
//	if err != nil {
//		logs.Error(lib.FuncName(),err)
//		return false
//	}
//	res.RowsAffected()
//	// 存在 写入 redis
//	DeviceData_Set(key)
//	//logs.Println("mysql row affected nums: ", num)
//	return true
//}

type DeviceData_ struct {
	T_t    float32 `orm:"column(t_t);size(10);null"`            // 温度
	T_rh   float32 `orm:"column(t_rh);size(10);null"`           // 湿度
	T_time string  `orm:"column(t_time);type(timestamp);null;"` // 采集时间
}
type DeviceData_New struct {
	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_time time.Time `orm:"column(t_time);type(timestamp);null;"` // 采集时间
}

func Read_DeviceSensorData_ById_List(SN string, 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

	if len(SN) == 0 {
		return maps, 0
	}

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

	if len(sql_time) > 0 {
		sql_time = strings.TrimRight(sql_time, "AND ")
	}

	sql := "SELECT COUNT(*) FROM z_devicedata_" + SN
	if len(sql_time) > 0 {
		sql += " WHERE " + sql_time
	}
	logs.Println(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		return maps, 0
	}
	if len(maps_z) == 0 {
		return maps, 0
	}
	//logs.Println("maps_z；",maps_z[0][0])
	//sql = "SELECT t_id,t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_time  FROM z_devicedata_" + SN
	sql = "SELECT t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_time  FROM z_devicedata_" + SN
	if len(sql_time) > 0 {
		sql += " WHERE " + sql_time
	}
	sql += " ORDER BY t_time"
	if page_z != 9999 {
		sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(pagez)
	}

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

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

func Read_DeviceData_By_snid(SN string, T_id int, Time_start_ string, Time_end_ string, offset, limit int) ([]DeviceData_, int) {
	var maps []DeviceData_
	o := orm.NewOrm()

	if len(SN) == 0 {
		return maps, 0
	}

	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 t_id,t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%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 t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%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 = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(limit)
	logs.Println(sql)
	_, err := o.Raw(sql).QueryRows(&maps)
	if err != nil {
		logs.Println(lib.FuncName(), err)
		return maps, 0
	}

	return maps, 0
}

func Read_DeviceData_Count_By_snid(SN string, T_id int, Time_start_ string, Time_end_ string) int {
	if len(SN) == 0 {
		return 0
	}
	o := orm.NewOrm()
	var maps_z []orm2.ParamsList

	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(*) FROM z_devicedata_" + SN + " WHERE " + sql_time + " t_id = " + strconv.Itoa(T_id)
	logs.Println(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		return 0
	}
	if len(maps_z) == 0 {
		return 0
	}
	key, _ := strconv.Atoi(maps_z[0][0].(string))
	return key
}

func Read_DeviceSensorData_List(SN string, 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
	if len(SN) == 0 {
		return maps, 0
	}

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

	sql_time := ""

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

	sql := "SELECT COUNT(*) FROM z_devicedata_" + SN + " " + sql_time
	logs.Println(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		return maps, 0
	}
	if len(maps_z) == 0 {
		return maps, 0
	}
	//logs.Println("maps_z；",maps_z[0][0])
	//sql = "SELECT t_id,t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_time  FROM z_devicedata_" + SN + " " + sql_time + " ORDER BY t_time DESC "
	sql = "SELECT t_t,t_rh,DATE_FORMAT(t_time,'%Y-%m-%d %H:%i:%s') AS t_time  FROM z_devicedata_" + SN + " " + sql_time + " ORDER BY t_time DESC "
	if page_z != 9999 {
		sql = sql + " LIMIT " + strconv.Itoa(offset) + "," + strconv.Itoa(pagez)
	}

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

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

func Read_DeviceSensorData_List_z(SN string, Time_start_ string) int {
	if len(SN) == 0 {
		return 0
	}
	o := orm.NewOrm()
	var maps_z []orm2.ParamsList

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

	sql := "SELECT COUNT(*) FROM z_devicedata_" + SN + " " + sql_time
	logs.Println(sql)
	_, err := o.Raw(sql).ValuesList(&maps_z)
	if err != nil {
		logs.Println(lib.FuncName(), err)
		return 0
	}

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

// 获取最新数据
func Read_DeviceSensorData_ById_New(SN string, T_id int) (DeviceData_New, bool) {
	o := orm.NewOrm()
	var maps DeviceData_New
	if len(SN) == 0 {
		return maps, false
	}

	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
	}

	//logs.Println("maps_z；",maps_z[0][0])
	//sql := "SELECT t_id,t_t,t_rh,t_time  FROM z_devicedata_" + SN + " WHERE " + " t_id = " + strconv.Itoa(T_id) + " ORDER BY t_time DESC "
	sql := "SELECT t_t,t_rh,t_time  FROM z_devicedata_" + SN + " WHERE " + " t_id = " + strconv.Itoa(T_id) + " ORDER BY t_time DESC "
	sql = sql + " LIMIT 0,1 "

	logs.Println(sql)
	err := o.Raw(sql).QueryRow(&maps)
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return maps, false
	}
	logs.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], Time_start_, Time_end_, 0, 9999)
			maps = append(maps, r_maps...)
			maps_num = maps_num + r_maps_num

			logs.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 {
		logs.Println("总数据：", maps_num, " 导出")
		return maps, maps_num
	}
	logs.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
	if len(SN) == 0 {
		return maps_z
	}

	//sql = "SELECT t_name,t_t,t_rh,t_tl,t_tu,t_rhl,t_rhu,t_site,DATE_FORMAT(t_time,'%Y-%m-%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\");"
	logs.Println(sql)
	num, err := o.Raw(sql).ValuesList(&maps_z)
	if err == nil && num > 0 {
		logs.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
	if len(SN) == 0 {
		return maps_z
	}
	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-%m-%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\");"
	logs.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
	if len(SN) == 0 {
		return maps_z
	}
	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-%m-%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\");"
	logs.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
	if len(SN) == 0 {
		return maps_z
	}

	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-%m-%d %H:%i:%s') AS t_time FROM z_devicedata_" + SN + " WHERE " + sql_time + "   GROUP BY t_time  ORDER BY t_time DESC "
	logs.Println(sql)
	o.Raw(sql).ValuesList(&maps_z)
	return maps_z
}

func Read_DeviceData_T_Min_Max_Time_Min_Max(SN string, Time_start_ string, Time_end_ string) (minT, maxT float64, minTime, maxTime time.Time) {
	o := orm.NewOrm()
	if len(SN) == 0 {
		return
	}

	sql_condition := ""

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

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

	if len(sql_condition) > 0 {
		sql_condition = " WHERE " + strings.TrimLeft(sql_condition, " AND ")
	}
	//logs.Println("maps_z；",maps_z[0][0])
	sql := "SELECT MIN(t_t) AS min_t, MAX(t_t) AS max_t,MIN(t_time) AS min_time, MAX(t_time) AS max_time FROM z_devicedata_" + SN + sql_condition

	logs.Println(sql)
	err := o.Raw(sql).QueryRow(&minT, &maxT, &minTime, &maxTime)
	if err != nil {
		logs.Error(lib.FuncName(), err)
	}
	return

}