ColdP_server/controllers/DataController.go

package controllers

import (
	"ColdP_server/conf"
	"ColdP_server/controllers/lib"
	"ColdP_server/models/Company"
	"ColdP_server/models/Device"
	"ColdP_server/models/Warning"
	"database/sql"
	"encoding/json"
	"fmt"
	beego "github.com/beego/beego/v2/server/web"
	"github.com/xuri/excelize/v2"
	"io"
	"io/ioutil"
	"log"
	"math"
	"strconv"
	"strings"
)

type DataController struct {
	beego.Controller
}

func (c *DataController) DataList_html() {
	b_, _ := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}
	// 验证登录
	b_, R_u := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}

	Name := c.GetString("Name")

	c.Data["Class_List"] = Company.Read_CompanyClass_All(R_u.T_pid, "")
	c.Data["Name"] = Name
	c.TplName = "Data/DataList.html"
}

// Device_Sensor_List 传感器列表
func (c *DataController) Device_Sensor_List() {
	b_, _ := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}
	// 验证登录
	b_, admin_r := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}

	type R_JSONS struct {
		//必须的大写开头
		DeviceSensor_lite []Device.DeviceSensor_
		Num               int
		Page              int
		Page_size         int
		Pages             []lib.Page_T
	}
	var r_jsons R_JSONS
	page, _ := c.GetInt("page")
	println(page)
	if page < 1 {
		page = 1
	}
	page_z, _ := c.GetInt("page_z")
	println(page_z)
	if page_z == 0 {
		page_z = conf.Page_size
	}

	T_sn := c.GetString("T_sn")
	T_Calss_id, _ := c.GetInt("T_class_id")
	T_name := c.GetString("T_name")
	SN_type := c.GetString("SN_type")

	//c.Data["Class_List"] = Device.Read_Class_All_1()

	var cnt int64
	fmt.Printf("当前登录用户的PID是：%d\n", admin_r.T_pid)
	num, _ := strconv.ParseInt(SN_type, 10, 64)
	r_jsons.DeviceSensor_lite, cnt = Device.Read_DeviceSensor_List_T_Class(admin_r.T_pid, T_Calss_id, T_sn, T_name, int(num), page, page_z)
	page_size := math.Ceil(float64(cnt) / float64(page_z))
	r_jsons.Page = int(page)
	r_jsons.Page_size = int(page_size)
	r_jsons.Pages = lib.Func_page(int64(page), int64(page_size))
	r_jsons.Num = int(cnt)

	c.Data["json"] = lib.JSONS{Code: 200, Msg: "ok!", Data: r_jsons}
	c.ServeJSON()
	return
}

// Device_Sensor_Data_More 列表 - 接口
func (c *DataController) Device_Sensor_Data_More() {

	b_, _ := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}

	page, _ := c.GetInt("page")
	page_z, _ := c.GetInt("page_z")
	println(page)
	if page < 1 {
		page = 1
	}
	if page_z < 1 {
		page_z = conf.Page_size
	}

	T_snid := c.GetString("T_snid")
	Time_start := c.GetString("Time_start")
	Time_end := c.GetString("Time_end")

	if len(T_snid) < 10 {
		c.Data["json"] = lib.JSONS{Code: 200, Msg: "ok!"}
		c.ServeJSON()
		return
	}
	sort := c.GetString("sort")
	//log.Println(sort, "排序==========================")
	var cnt int64
	var pageHelper = lib.PageHelper{}
	pageHelper.List, cnt = Device.Read_DeviceData_By_T_snid_List(T_snid, Time_start, Time_end, page, page_z, sort)
	pageSize := math.Ceil(float64(cnt) / float64(page_z))
	pageHelper.CurrentPage = page
	pageHelper.TotalPage = int(pageSize)
	pageHelper.TotalCount = int(cnt)
	pageHelper.NextPage = page < int(pageSize)
	pageHelper.PreviousPage = page > 1

	c.Data["json"] = lib.JSONS{Code: 200, Msg: "ok!", Data: pageHelper}
	c.ServeJSON()
	return
}

// Device_Sensor_List_Delete 删除设备数据
func (c *DataController) Device_Sensor_List_Delete() {
	b_, _ := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}
	var datas = make([]Device.DeviceData_R, 0)
	bytes, _ := ioutil.ReadAll(c.Ctx.Request.Body)
	fmt.Println("body获取得到数据：", string(bytes))
	json.Unmarshal(bytes, &datas)
	fmt.Println("jsonUnmarshal获取得到数据：", datas)
	for _, data := range datas {
		err, m := Device.ReadDeviceSensorParameter(data.T_sp)
		if err != nil {
			c.Data["json"] = lib.JSONS{500, "删除失败！", nil}
			c.ServeJSON()
			return
		}
		if data.T_t < m.T_Tlower || data.T_t > m.T_Tupper || data.T_rh < m.T_RHlower || data.T_rh > m.T_RHupper {
			log.Println("报警消息", data)
			err := Warning.DeleteWarning(data.T_sn, data.T_time, data.T_id)
			if err != nil {
				log.Println("删除报警消息失败", err)
				c.Data["json"] = lib.JSONS{500, "删除报警消息失败！", nil}
				c.ServeJSON()
				return
			}
		}
	}
	Device.DeleteDeviceDataByDeviceDataRList(datas)
	c.Data["json"] = lib.JSONS{200, "删除成功！", nil}
	c.ServeJSON()
	return
}

// Device_Sensor_List_Delete_Time 删除选择时间范围
func (c *DataController) Device_Sensor_List_Delete_Time() {
	b_, _ := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}
	type R_JSONS struct {
		T_snid     []string `json:"t_sn"`
		T_id       []string `json:"t_id"`
		Time_start string   `json:"time_start"`
		Time_end   string   `json:"time_end"`
	}
	all, _ := io.ReadAll(c.Ctx.Request.Body)
	var r_json R_JSONS
	err := json.Unmarshal(all, &r_json)
	if err != nil {
		return
	}
	var rows sql.Result
	var affected int64
	for i := 0; i < len(r_json.T_snid); i++ {
		rows, err = Device.DeleteDeviceDataByTime(r_json.T_snid[i], r_json.T_id[i], r_json.Time_start, r_json.Time_end)
		//rows, err = Device.DeleteDeviceDataByTimeWaring(r_json.T_snid[i], r_json.T_id[i], r_json.Time_start, r_json.Time_end)
		affected, _ = rows.RowsAffected()
		affected += affected
	}
	if err == nil {
		c.Data["json"] = lib.JSONS{Code: 200, Msg: "删除成功", Data: affected}
		c.ServeJSON()
		return
	}
	c.Data["json"] = lib.JSONS{Code: 201, Msg: "删除失败", Data: affected}
	c.ServeJSON()
}

// Device_Sensor_Update /Data/Device_Sensor_List_Update 更新设备数据
func (c *DataController) Device_Sensor_Update() {
	b_, _ := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}
	T_sn := c.GetString("t_sn")
	T_sp := c.GetString("t_sp")
	T_id, _ := c.GetInt("t_id")
	T_t, _ := c.GetFloat("t_t")
	T_rh, _ := c.GetFloat("t_rh")
	T_site := c.GetString("t_site")
	CreateTime := c.GetString("create_time")
	fieldName := c.GetString("type")
	val := c.GetString("value")
	i, _ := strconv.ParseInt(T_sp, 10, 64)
	data := Device.DeviceData_R{
		T_sp:        int(i),
		T_sn:        T_sn,
		T_id:        T_id,
		T_t:         float32(T_t),
		T_rh:        float32(T_rh),
		T_site:      T_site,
		Create_Time: CreateTime,
	}
	log.Println(T_sn, T_id, T_t, T_rh, T_site, fieldName, val, "修改值")
	log.Println(data, "修改值")
	err := Device.Update_DeviceSensorData(data, fieldName, val)
	if err != nil {
		c.Data["json"] = lib.JSONS{
			201, "更新失败", nil,
		}
		c.ServeJSON()
		return
	}
	c.Data["json"] = lib.JSONS{
		200, "更新成功", nil,
	}
	c.ServeJSON()
	return
}

// Device_Sensor_Record 数据记录
func (c *DataController) Device_Sensor_Record() {
	b_, _ := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}
	sn := c.GetString("sn")
	tId := c.GetString("tId")
	createTime := c.GetString("createTime")
	fmt.Println("时间：", createTime)
	list := Device.ReadDeviceOldBySnTid(sn, tId)
	c.Data["json"] = lib.JSONS{200, "操作成功", list}
	c.ServeJSON()
	return
}

// ImportData 数据导入 excel
func (c *DataController) ImportData() {
	b_, _ := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}

	file, header, err := c.GetFile("file")
	defer file.Close()

	if err != nil {
		panic(any(fmt.Sprintf("%s 获取上传文件错误", err.Error())))
	}
	fmt.Println("文件名称：", header.Filename)
	sn := strings.Split(header.Filename, ".")[0]
	fmt.Printf("sn：%s\n", sn)
	reader, err := excelize.OpenReader(file)
	if err != nil {
		panic(any(fmt.Sprintf("%s 打开上传文件excel错误", err.Error())))
	}
	data := Device.ImportDeviceData(reader, sn)
	c.Data["json"] = lib.JSONS{200, "操作成功", data}
	c.ServeJSON()
	return
}

// Device_Sensor_Data_Excel 导出传感器数据列表
func (c *DataController) Device_Sensor_Data_Excel() {
	b_, _ := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}
	T_snid := c.GetString("T_snid")
	Time_start := c.GetString("Time_start")
	Time_end := c.GetString("Time_end")

	if len(T_snid) < 10 {
		c.Data["json"] = lib.JSONS{Code: 200, Msg: "ok!"}
		c.ServeJSON()
		return
	}
	var DeviceSensor_data []Device.DeviceData_R

	DeviceSensor_data, _ = Device.Read_DeviceData_By_T_snid_List(T_snid, Time_start, Time_end, 0, 9999, "DESC")

	f := excelize.NewFile() // 设置单元格的值
	// 这里设置表头
	f.SetCellValue("Sheet1", "A1", "探头编号")
	f.SetCellValue("Sheet1", "B1", "温度")
	f.SetCellValue("Sheet1", "C1", "湿度℃")
	f.SetCellValue("Sheet1", "D1", "坐标")
	f.SetCellValue("Sheet1", "E1", "记录时间")
	// 设置列宽
	f.SetColWidth("Sheet1", "A", "A", 10)
	f.SetColWidth("Sheet1", "B", "B", 15)
	f.SetColWidth("Sheet1", "C", "D", 20)
	f.SetColWidth("Sheet1", "E", "E", 20)

	line := 1

	headStyleLower, _ := f.NewStyle(
		&excelize.Style{
			Font: &excelize.Font{Size: 11, Color: "#ff8585", Family: "arial"},
		})
	headStyleUpper, _ := f.NewStyle(
		&excelize.Style{
			Font: &excelize.Font{Size: 11, Color: "#ff8585", Family: "arial"},
		})
	// 定义时间格式
	// 循环写入数据
	for _, v := range DeviceSensor_data {
		line++

		f.SetCellValue("Sheet1", fmt.Sprintf("A%d", line), v.T_id)
		f.SetCellValue("Sheet1", fmt.Sprintf("B%d", line), v.T_t)

		f.SetCellValue("Sheet1", fmt.Sprintf("C%d", line), v.T_rh)
		//f.SetCellValue("Sheet1", fmt.Sprintf("E%d", line), strconv.FormatFloat(float64(v.T_tl), 'f', 2, 64)+"~"+strconv.FormatFloat(float64(v.T_tu), 'f', 2, 64))
		if v.T_t < v.T_tl || v.T_t > v.T_tu {
			f.SetCellStyle("Sheet1", fmt.Sprintf("A%d", line), fmt.Sprintf("G%d", line), headStyleLower)
		}

		f.SetCellValue("Sheet1", fmt.Sprintf("D%d", line), v.T_site)
		//f.SetCellValue("Sheet1", fmt.Sprintf("F%d", line), strconv.FormatFloat(float64(v.T_rhl), 'f', 2, 64)+"~"+strconv.FormatFloat(float64(v.T_rhu), 'f', 2, 64))
		if v.T_rh < v.T_rhl || v.T_rh > v.T_rhu {
			f.SetCellStyle("Sheet1", fmt.Sprintf("A%d", line), fmt.Sprintf("G%d", line), headStyleUpper)
		}
		f.SetCellValue("Sheet1", fmt.Sprintf("E%d", line), v.T_time)

	}
	//timeStr := time.Now().Format("20060102150405")
	filename := strings.Split(T_snid, ",")[0]
	// 保存文件
	if err := f.SaveAs("ofile/" + filename + ".xlsx"); err != nil {
		fmt.Println(err)
	}
	url := "/ofile/" + filename + ".xlsx"

	c.Data["json"] = lib.JSONS{Code: 200, Msg: "ok!", Data: url}
	c.ServeJSON()
	return
}

// GetCompanyBySn 根据SN查询对应公司
func (c *DataController) GetCompanyBySn() {
	b_, admin := lib.Verification(c.Ctx.GetCookie("User_tokey"), c.GetString("User_tokey"))
	if !b_ {
		c.Data["json"] = lib.JSONS{Code: 201, Msg: "User_tokey Err!"}
		c.ServeJSON()
		return
	}
	if admin.T_pid == 0 || admin.T_name == "bzdwgm" {
		sn := c.GetString("sn")
		company, err := Device.GetCompanyBySn(sn)
		if err != nil {
			c.Data["json"] = lib.JSONS{Code: 201, Msg: "查询失败"}
			c.ServeJSON()
			return
		}
		log.Println(admin.T_name)
		c.Data["json"] = lib.JSONS{Code: 200, Msg: "ok!", Data: company}
		c.ServeJSON()
		return
	}
	c.Data["json"] = lib.JSONS{Code: 403, Msg: "无权限"}
	c.ServeJSON()
}