Cold_Api/models/Device/DeviceData.go

package Device

import (
	"Cold_Api/conf"
	"Cold_Api/controllers/lib"
	"encoding/json"
	"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, 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 // 采集时间
}

// 模板
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关闭
}

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

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
}

// -------------------------------------------------------
// 创建数据库  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){
	//	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()

	// 检查 超过时间，查询 数据库
	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
		}
		//fmt.Println("maps_z[0][0]:",maps_z[0][0])
		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，以提高批量执行的速度
	fmt.Println(sql)
	res, err := o.Raw(sql).Exec()
	if err != nil {
		logs.Error(lib.FuncName(), err)
		return false
	}
	res.RowsAffected()

	return true
}

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)
	fmt.Println(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
	}
	//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,t_time AS t_time1 FROM z_device_data_" + 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(page_z)
	}

	fmt.Println(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_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 {
		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(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 {
			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 {
			//	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 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 true
		}
		return false
	})

	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 {
		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_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) []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\");"
	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_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\");"
	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) []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 "
	fmt.Println(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"

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

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

	if len(maps) == 0 {
		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)

			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 offset > offset_z {
		offset = offset_z
	}

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

	fmt.Println(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)
}