labsop_mcs/backend/src/dashoo.cn/qatoolsweb/controllers/admin/datareport.go

package admin

import (
	"fmt"
	"strings"
	"time"

	"github.com/tealeg/xlsx"

	"dashoo.cn/labsop"
	. "dashoo.cn/qatoolsweb/controllers"
)

type DatareportController struct {
	BaseController
}

type datas struct {
	SNum             int
	Temperature      float64
	Humidity         float64
	Zbackup8         float64
	Co2              float64
	ElectricalPower  int64
	ElectricalSupply int64
	Signal           float64
	Voltage          float64
	BBVoltage        float64
	SensorMAC        string
	BBMac            string
	Zbackup4         int64   //sensor序列号
	SensorNo         int64   //序列号(blackbox)
	Zbackup3         int64   //移动信号
	Zbackup5         string  //服务器时间
	Zbackup2         string  //BBtime
	CSTime           string  //传输时间
	CJTime           string  //采集时间
	Zbackup10        float64 //真实温度
	Zbackup1         string  //请求原数据
}
type headname1 struct {
	SNum             string
	Temperature      string
	Humidity         string
	Zbackup8         string
	Co2              string
	ElectricalPower  string
	ElectricalSupply string
	Signal           string
	Voltage          string
	BBVoltage        string
	SensorMac        string
	BBMac            string
	Zbackup4         string
	SensorNo         string
	Zbackup3         string
	Zbackup5         string
	Zbackup2         string
	CSTime           string
	CJTime           string
	Zbackup10        string
	Zbackup1         string
	TimeRange        string
}

func (this *DatareportController) DataReport() {

	var channel, bbMac string
	if this.GetString("sensormac") != "" {
		channel = this.GetString("sensormac")
	} else {
		channel = this.GetString("c")
	}

	if this.GetString("blackboxmac") != "" {
		bbMac = this.GetString("blackboxmac")
	} else {
		bbMac = this.GetString("bbmac")
	}
	start := time.Now().Add(-4 * time.Hour).Format("2006-01-02 15:04:05")
	end := time.Now().Format("2006-01-02 15:04:05")

	if this.GetString("starttime") != "" {
		start = this.GetString("starttime")
	} else if this.GetString("start") != "" {
		start = this.GetString("start")
	}

	if this.GetString("endtime") != "" {
		end = this.GetString("endtime")
	} else if this.GetString("end") != "" {
		end = this.GetString("end")
	}
	t := ""
	if this.Ctx.Request.Method == "POST" || this.GetString("to") == "excel" || this.GetString("sensormac") != "" {
		if channel != "" {
			switch this.GetString("t") {
			case "3":

				starttime, _ := time.ParseInLocation("2006-1-2 15:4:5", start, time.Local)
				endtime, _ := time.ParseInLocation("2006-1-2 15:4:5", end, time.Local)

				client := labsop.GetLabSopClient(GetInfluxDBService(this.QADB))
				sql := fmt.Sprintf("select * from %v where time > %vs and time < %vs", "c"+channel, starttime.Unix()-1, endtime.Unix()+1)
				if bbMac != "" {
					sql = fmt.Sprintf("select * from %v where time > %vs and time < %vs and bbmac='%v'", "c"+channel, starttime.Unix()-1, endtime.Unix()+1, bbMac)
				}
				obj := new(labsop.DatapointLabSop)
				result := client.QueryLabSop(sql, obj)
				t = "3"
				liatcount := len(result)
				list := make([]labsop.DatapointLabSop, liatcount)
				for i, j := 0, liatcount; i < j; i++ {
					list[i] = result[j-i-1]
				}
				this.Data["data"] = list

			case "2":
				client := labsop.GetLabSopClient(GetInfluxDBService(this.QADB))
				t = "2"
				sql := fmt.Sprintf("select * from %v order by time desc limit 1 ", "c"+channel)
				if bbMac != "" {
					sql = fmt.Sprintf("select * from %v where bbmac='%v' order by time desc limit 1 ", "c"+channel, bbMac)
				}
				obj := new(labsop.DatapointLabSop)
				result := client.QueryLabSop(sql, obj)

				this.Data["data"] = result
			}

		}
	}
	this.Data["t"] = t
	this.Data["c"] = channel
	this.Data["bbmac"] = bbMac
	this.Data["start"] = start
	this.Data["end"] = end
	if this.GetString("to") == "excel" {
		this.TplName = "admin/datareport/excel.tpl" // 页面模板文件
	} else {

		this.TplName = "admin/datareport/index.tpl" // 页面模板文件
	}

}

func (this *DatareportController) DataExcel() {
	url := this.GetString("url")
	url = "http://" + this.Ctx.Request.Host + strings.Replace(url, " ", "%20", -1)
	upath := "/static/file/excel/"
	req := PageUrltoFile(url, upath, "blackboxfoodsafe.xls")
	this.Ctx.WriteString(req)
}

func (this *DatareportController) ExportData() {

	var channel, bbMac string
	if this.GetString("sensormac") != "" {
		channel = this.GetString("sensormac")
	} else {
		channel = this.GetString("c")
	}

	if this.GetString("blackboxmac") != "" {
		bbMac = this.GetString("blackboxmac")
	} else {
		bbMac = this.GetString("bbmac")
	}
	start := time.Now().Add(-4 * time.Hour).Format("2006-01-02 15:04:05")
	end := time.Now().Format("2006-01-02 15:04:05")

	if this.GetString("starttime") != "" {
		start = this.GetString("starttime")
	} else if this.GetString("start") != "" {
		start = this.GetString("start")
	}

	if this.GetString("endtime") != "" {
		end = this.GetString("endtime")
	} else if this.GetString("end") != "" {
		end = this.GetString("end")
	}
	t := ""
	if channel != "" {
		client := labsop.GetLabSopClient(GetInfluxDBService(this.QADB))
		switch this.GetString("t") {
		case "3":
			starttime, _ := time.ParseInLocation("2006-1-2 15:4:5", start, time.Local)
			endtime, _ := time.ParseInLocation("2006-1-2 15:4:5", end, time.Local)

			sql := fmt.Sprintf("select * from %v where time > %vs and time < %vs ", "c"+channel, starttime.Unix()-1, endtime.Unix()+1)
			if bbMac != "" {
				sql = fmt.Sprintf("select * from %v where time > %vs and time < %vs and bbmac='%v' ", "c"+channel, starttime.Unix()-1, endtime.Unix()+1, bbMac)
			}
			obj := new(labsop.DatapointLabSop)
			result := client.QueryLabSop(sql, obj)
			t = "3"
			this.Data["data"] = result
			f := xlsx.NewFile()
			this.SaveXlsx(result, f, channel, start+"至"+end)
			f.Save("static/file/excel/sensordatas.xlsx")

			this.Ctx.WriteString("static/file/excel/sensordatas.xlsx")

		case "2":
			sql := fmt.Sprintf("select * from %v order by time desc limit 1 ", "c"+channel)
			if bbMac != "" {
				sql = fmt.Sprintf("select * from %v where bbmac='%v' order by time desc limit 1 ", "c"+channel, bbMac)
			}
			obj := new(labsop.DatapointLabSop)
			result := client.QueryLabSop(sql, obj)
			t = "2"
			this.Data["data"] = result
			f := xlsx.NewFile()
			this.SaveXlsx(result, f, channel, start+"至"+end)
			f.Save("static/file/excel/sensordatas.xlsx")

			this.Ctx.WriteString("static/file/excel/sensordatas.xlsx")
		}

	}

	this.Data["t"] = t
	this.Data["c"] = channel
	this.Data["bbmac"] = bbMac
	this.Data["start"] = start
	this.Data["end"] = end

}

func (this *DatareportController) SaveXlsx(data []labsop.DatapointLabSop, f *xlsx.File, smac string, timerange string) {
	//	sheet, _ := f.AddSheet("SensorData")
	//	rowhead := sheet.AddRow()

	//	headsname := headname1{
	//		"序号",
	//		"温度/℃",
	//		"湿度/%",
	//		"氧气/%",
	//		"二氧化碳/‰",
	//		"电器功率/w",
	//		"电量/v",
	//		"信号强度",
	//		"Sensor电压",
	//		"BB电压",
	//		"SensorMac",
	//		"BBMAC",
	//		"Sensor序列号",
	//		"BB序列号",
	//		"移动信号",
	//		"服务器时间",
	//		"BB传输时间",
	//		"Sensor传输时间",
	//		"采集时间",
	//		"真实温度",
	//		"原数据",
	//		timerange,
	//	}
	//	rowhead.WriteStruct(&headsname, -1)
	//	num := 0
	//	for j, l := 0, len(data); j < l; j++ {
	//		num++
	//		row := sheet.AddRow()
	//		testStruct := datas{
	//			num,
	//			data[l-j-1].Temperature,
	//			data[l-j-1].Humidity,
	//			data[l-j-1].O2,
	//			data[l-j-1].Co2,
	//			//			data[l-j-1].ElectricalPower,
	//			//			data[l-j-1].ElectricalSupply,
	//			data[l-j-1].RSSI,
	//			data[l-j-1].Voltage,
	//			data[l-j-1].BBVoltage,
	//			smac,
	//			data[l-j-1].BBMac,
	//			data[l-j-1].SensorNo,
	//			data[l-j-1].BBNo,
	//			data[l-j-1].SimSignal,
	//			data[l-j-1].ServiceTime.Format("2006-01-02 15:04:05"),
	//			data[l-j-1].BBCSTime.Format("2006-01-02 15:04:05"),
	//			data[l-j-1].CSTime.Format("2006-01-02 15:04:05"),
	//			data[l-j-1].Time.Format("2006-01-02 15:04:05"),
	//			data[l-j-1].RealTemperature,
	//			data[l-j-1].RequestData,
	//		}
	//		row.WriteStruct(&testStruct, -1)
	//	}
	//	sheet.Cols[0].Width = 10 //设置时间单元格的宽度
	//	sheet.Cols[1].Width = 10
	//	sheet.Cols[2].Width = 10
	//	sheet.Cols[3].Width = 10
	//	sheet.Cols[4].Width = 10
	//	sheet.Cols[5].Width = 10
	//	sheet.Cols[6].Width = 10
	//	sheet.Cols[7].Width = 10
	//	sheet.Cols[8].Width = 10
	//	sheet.Cols[9].Width = 10
	//	sheet.Cols[10].Width = 15
	//	sheet.Cols[10].Width = 10
	//	sheet.Cols[11].Width = 10
	//	sheet.Cols[12].Width = 10
	//	sheet.Cols[13].Width = 20
	//	sheet.Cols[14].Width = 20
	//	sheet.Cols[15].Width = 20
	//	sheet.Cols[16].Width = 20
	//	sheet.Cols[17].Width = 10
	//	sheet.Cols[18].Width = 200
}