bileiqi/05.code/backend/controllers/excelController.go

/*
 * @Author: liling
 * @Date: 2022-08-13 14:38:13
 * @LastEditors: liling
 * @LastEditTime: 2022-08-13 14:38:15
 * @FilePath: \police_security\controllers\exelController.go
 * @Description:excel导入导出相关控制器
 *
 * Copyright (c) 2022 by liling/jujutong, All Rights Reserved.
 */
package controllers

import (
	"errors"
	"os"
	"path"
	"rtzh_elec_temperature/excel"
	"rtzh_elec_temperature/rtelec_app_public_lib/service"
	"rtzh_elec_temperature/tools"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/spf13/cast"

	"github.com/astaxie/beego/orm"
)

//Excel相关服务
type ExcelController struct {
	BaseController
}

func init() {
}

//公用数据导出方法
// @Summary      数据导出为Excel
// @Description  数据导出为Excel。接口将生成数据文件，并返回文件下载路径。当前支持:device_info、device_mp_info、history-data-opt(自定义日期段历史数据导出)、report-all（导出全部报表）、report-opt(各类型报表导出,需要device_id、reportid、reporttype[1|2|3|4]三个参数)、optimize_invalid_fillrule（导出无效数据填充规则，需要device_id和model_id参数）、optimize_invalid_fillrule（导出缺点数据填充规则，需要device_id和model_id参数）
// @Tags         api
// @Accept       x-www-form-urlencoded
// @Produce      json
// @Param        code   		query     string  true  "业务数据类别，由系统定义。当前支持:history-data-opt(自定义日期段历史数据导出)、report-all（导出全部报表）、report-opt(各类型报表导出,需要device_id、reportid、reporttype[1|2|3|4]三个参数)、optimize_invalid_fillrule（导出无效数据填充规则）、optimize_invalid_fillrule（导出缺点数据填充规则）"
// @Param        caption   		query     string  false  "数据标题/文件名称，将显示在excel中的第一行。"
// @Param        expcols   		query     string  false  "需要导出的字段名。未设置时按数据结果全部返回。"
// @Param        expcolnames   	query     string  false  "导出字段名对应的中文名称。与导出字段名一一对应"
// @Param        ...paras   	query     string  false  "根据业务查询需求，提供相应的参数"
// @Success      200    	{object}  ApiOK|ApiError   服务访问成功
// @Failure 	 401 		status  认证未通过，一般是未指定token或token已失效
// @Failure 	 500 		status  服务器｜后台发生错误
//	@router /expTableData [get]
func (c *ExcelController) ExpTableData() {
	code := c.GetString("code", "") //业务数据类别，由系统定义
	if code == "" {
		c.Data["json"] = c.ApiError("导出的业务数据类别不能为空")
		c.ServeJSON()
		return
	}
	expCaption := c.GetString("caption", "数据导出-"+time.Now().Format("20060102150405"))
	expcols := []string{}
	expcolnames := []string{}
	c1 := c.GetString("expcols")
	if c1 != "" {
		expcols = strings.Split(c1, ",")
	}
	c2 := c.GetString("expcolnames")
	if c2 != "" {
		expcolnames = strings.Split(c2, ",")
	}
	if len(expcols) != len(expcolnames) {
		c.Data["json"] = c.ApiError("设置了导出列，但列名与中文名数量不一致")
		c.ServeJSON()
		return
	}
	paras := c.Input()
	param := map[string]interface{}{"code": code}
	for k, _ := range paras {
		param[k] = paras.Get(k)
	}
	datalist := []orm.Params{}
	var err error
	switch code {
	case "history-data-opt":
		//自定义日期段历史数据导出
		hismgr := new(service.HistoryService)
		hismgr.Model.Deviceid = cast.ToInt32(param["deviceid"])
		hismgr.StartDate = cast.ToString(param["starttime"])
		hismgr.EndDate = cast.ToString(param["endtime"])
		if hismgr.Model.Deviceid == 0 {
			err = errors.New("设备编号不能为空")
		}
		if hismgr.StartDate == "" {
			err = errors.New("导出数据的开始日期不能为空")
		}
		if err == nil {
			result, _, err := hismgr.SearchHistory()
			if err == nil {
				datalist = result["tbody"].([]orm.Params)
			}
		}
		/*
			datatable := map[string]interface{}{"state": param["state"], "starttime": param["starttime"], "endtime": param["endtime"], "words": param["words"]}
			queryPara := map[string]interface{}{"datatable": datatable}
			queryReturn, err := bo.QueryGlobalsyslogList(queryPara)
			if err == nil {
				datalist = queryReturn["data"].([]orm.Params)
			}*/
		break
	case "report-opt":
		//导出指定类型报表
		obj := new(service.StatReportService)
		deviceid, _ := c.GetInt32("device_id")
		if deviceid == 0 {
			c.Data["json"] = c.ApiError("设备ID不能为空")
			c.ServeJSON()
			return
		}
		cols := new(service.DeviceService).DeviceMpInfo(deviceid)
		if len(cols) == 0 {
			c.Data["json"] = c.ApiError("该设备的未配置测点！")
			c.ServeJSON()
			return
		}
		expcols = []string{"acquisition_time"}
		expcolnames = []string{"采集时间"}
		tems := []string{}
		hums := []string{}
		vol := []string{}
		for _, r := range cols {
			an := tools.IsEmpty(r["attrname"])
			switch an[0:3] {
			case "Tem":
				tems = append(tems, an)
				break
			case "Hum":
				hums = append(hums, an)
				break
			case "Vol":
				vol = append(vol, an)
				break
			}
		}
		sort.Strings(tems)
		expcols = append(expcols, tems...)
		if len(hums) > 0 {
			sort.Strings(hums)
			expcols = append(expcols, hums...)
		}
		if len(vol) > 0 {
			sort.Strings(vol)
			expcols = append(expcols, vol...)
		}
		for _, r := range expcols {
			if r == "acquisition_time" {
				continue
			}
			expcolnames = append(expcolnames, tools.IsEmpty(cols[r]["mpname"]))
		}
		obj.UserInfo = c.GetCurrentUserInfo_rt()
		rtid, _ := c.GetInt64("reportid")
		typeid := c.GetString("reporttype")
		r, err := obj.ExpDataQuery(deviceid, rtid, typeid)
		if err == nil {
			datalist = r
		}
		break
	case "report-all":
		//导出全部报表
		obj := new(service.StatReportService)
		deviceid, _ := c.GetInt32("device_id")
		if deviceid == 0 {
			c.Data["json"] = c.ApiError("设备ID不能为空")
			c.ServeJSON()
			return
		}
		cols := new(service.DeviceService).DeviceMpInfo(deviceid)
		if len(cols) == 0 {
			c.Data["json"] = c.ApiError("该设备的未配置测点！")
			c.ServeJSON()
			return
		}
		expcols = []string{"acquisition_time"}
		expcolnames = []string{"采集时间"}
		tems := []string{}
		hums := []string{}
		vol := []string{}
		for _, r := range cols {
			an := tools.IsEmpty(r["attrname"])
			switch an[0:3] {
			case "Tem":
				tems = append(tems, an)
				break
			case "Hum":
				hums = append(hums, an)
				break
			case "Vol":
				vol = append(vol, an)
				break
			}
		}
		sort.Strings(tems)
		expcols = append(expcols, tems...)
		if len(hums) > 0 {
			sort.Strings(hums)
			expcols = append(expcols, hums...)
		}
		if len(vol) > 0 {
			sort.Strings(vol)
			expcols = append(expcols, vol...)
		}
		for _, r := range expcols {
			if r == "acquisition_time" {
				continue
			}
			expcolnames = append(expcolnames, tools.IsEmpty(cols[r]["mpname"]))
		}
		obj.UserInfo = c.GetCurrentUserInfo_rt()
		rtid, _ := c.GetInt64("reportid", 0)
		if rtid == 0 {
			rtid, _ = strconv.ParseInt(time.Now().Format("2006"), 10, 64)
		}
		r, err := obj.ExpDataQuery(deviceid, rtid, "1")
		if err == nil {
			datalist = r
		}
		break
	case "log-report":
		/*
			datatable := map[string]interface{}{"state": param["state"], "starttime": param["starttime"], "endtime": param["endtime"], "words": param["words"], "type": param["type"]}
			queryPara := map[string]interface{}{"datatable": datatable}
			queryReturn, err := bo.QueryReportlogList(queryPara)
			if err == nil {
				datalist = queryReturn["data"].([]orm.Params)
			}*/
		//上报日志导出
		break
	case "optimize_invalid_fillrule":
		dataOptSrv := new(service.DataoptimizeService)
		dataOptSrv.UserInfo = c.GetCurrentUserInfo_rt()
		modelid, _ := c.GetInt("model_id")
		deviceid, _ := c.GetInt("device_id")
		datalist, err = dataOptSrv.ExpInvalidFillRule(modelid, deviceid)
		expcols = []string{"modelname", "attrname", "devicename", "mpname", "minvalue", "maxvalue"}
		expcolnames = []string{"模型名称(必填)", "模型属性名称(必填)", "设备名称", "测点名称", "有效最小值(必填)", "有效最大值(必填)"}
		break
	case "optimize_deletion_fillrule":
		dataOptSrv := new(service.DataoptimizeService)
		dataOptSrv.UserInfo = c.GetCurrentUserInfo_rt()
		modelid, _ := c.GetInt("model_id")
		deviceid, _ := c.GetInt("device_id")
		expcols = []string{"modelname", "attrname", "devicename", "mpname", "fill"}
		expcolnames = []string{"模型名称(必填)", "模型属性名称(必填)", "设备名称", "测点名称", "填充测点列表(多个测点用逗号分隔)"}
		datalist, err = dataOptSrv.ExpDeletionFillRule(modelid, deviceid)
		break
	case "device_info":
		dataOptSrv := new(service.DeviceService)
		dataOptSrv.UserInfo = c.GetCurrentUserInfo_rt()
		expcols = []string{"region_name", "model_name", "devicename", "serial", "baudrate", "stopbit", "checkbit", "databit", "slaveid", "ip", "port", "mqtttopic", "eid", "dev_type_name"}
		expcolnames = []string{"区域名称(必填)", "模型名称(必填)", "设备名称(必填)", "串口号", "串口波特率", "串口停止位", "串口检测位", "串口数据位", "从设备ID", "IP地址", "端口号", "MQTT主题", "微功耗传感器ID", "设备类型(支持开关柜\\台区\\输电线路\\杆塔)"}
		datalist, err = dataOptSrv.Exp()
		break
	case "device_mp_info":
		deviceid, _ := c.GetInt32("device_id")
		if deviceid == 0 {
			err = errors.New("无效的设备ID")
		} else {
			dataOptSrv := new(service.DeviceService)
			dataOptSrv.UserInfo = c.GetCurrentUserInfo_rt()
			expcols = []string{"devicename", "mpname", "zonename", "positionname", "modelname", "attrname", "phase"}
			expcolnames = []string{"设备名称(必填)", "测点名称(必填)", "区域名称", "位置", "模型名称(必填)", "模型属性名称(必填)", "相位(支持A|B|C|N及空)"}
			datalist, err = dataOptSrv.ExpMp(deviceid)
		}
		break
	case "device_setting":
		deviceid, _ := c.GetInt("deviceid")
		ds := new(service.AlarmDeviceService)
		expcols = []string{"alarm_id", "deviceid", "mpid", "attrname", "mpname", "alarm_type", "alarm_level", "strategy_name", "upcycle", "threshold"}
		expcolnames = []string{"告警编号", "设备编号", "测点编号", "测点属性名称", "测点名称", "告警类型", "报警等级", "告警策略名称", "上升周期(绝对值告警可不填)", "阈值"}
		datalist, err = ds.ImportModelsDeviceSettingData(int64(deviceid))
		break
	case "device_setting_export":
		deviceid, _ := c.GetInt("deviceid")
		ds := new(service.AlarmDeviceService)
		expcols = []string{"deviceid", "mpid", "attrname", "mpname", "alarm_type", "upcycle", "threshold"}
		expcolnames = []string{"设备编号", "测点编号", "测点属性名称", "测点名称", "告警类型", "上升周期(绝对值告警可不填)", "阈值"}
		datalist, err = ds.ExportExcelDeviceSettingData(int64(deviceid))
		break
	case "alarm_info":
		alarm_level, _ := c.GetInt("alarm_level", 0)
		mpname := c.GetString("mpname")
		alarmDesc := c.GetString("alarmDesc")
		beginTime := c.GetString("beginTime")
		endTime := c.GetString("endTime")
		ds := new(service.AlarmInfoService)
		expcols = []string{"create_at", "devicename", "mpname", "alarm_desc", "is_reset", "alarm_level", "confirm"}
		expcolnames = []string{"时间", "设备名称", "告警测点", "告警内容", "是否复归", "告警等级", "是否确认"}
		datalist, err = ds.ExprotAlarmInfoList(mpname, alarm_level, alarmDesc, beginTime, endTime, 1, 0)
		break
	default:
		datalist = nil
		err = errors.New("未定义的导出数据类型：" + code)
		break
	}
	if err != nil {
		c.Data["json"] = c.ApiError(err.Error())
		c.ServeJSON()
		return
	}
	curPath, _ := os.Getwd() //当前绝对路径
	excel.InitExcel("exp_data_"+time.Now().Format("20060102150405"), curPath+string(os.PathSeparator)+"static"+string(os.PathSeparator)+"download")
	filename, errf := excel.ExportExcel(expCaption, expcols, expcolnames, datalist, true)
	if errf != nil {
		c.Data["json"] = c.ApiError(errf.Error())
	} else {
		c.Data["json"] = c.ApiOK("/static/download/" + filename)
	}
	c.ServeJSON()
}

//公用数据导入方法
// @Summary      将Excel数据导入到指定模块
// @Description  将Excel数据导入到指定模块
// @Tags         api
// @Accept       x-www-form-urlencoded
// @Produce      json
// @Param        code   		query     string  true  "业务数据类别，由系统定义。当前支持:device_info、device_mp_info、optimize_invalid_fillrule、optimize_deletion_fillrule"
// @Param        file   	query     file  true  "文件流参数名"
// @Param        ...paras   	query     string  false  "根据业务查询需求，提供相应的参数"
// @Success      200    	{object}  ApiOK|ApiError   服务访问成功
// @Failure 	 401 		status  认证未通过，一般是未指定token或token已失效
// @Failure 	 500 		status  服务器｜后台发生错误
//	@router /upload [post]
func (c *ExcelController) UploadExcelFile() {
	code := c.GetString("code", "") //业务数据类别，由系统定义
	if code == "" {
		c.Data["json"] = c.ResultError("导入的业务数据类别不能为空")
		c.ServeJSON()
		return
	}
	_, h, _ := c.GetFile("file")
	ext := path.Ext(h.Filename)
	if strings.Index(".csv,.xlsx", ext) == -1 {
		c.Data["json"] = c.WarpError("不支持的文件格式")
		c.ServeJSON()
		return
	}
	param := tools.ParseFormParam(c.Ctx.Request.Form)
	tmmpDir := os.TempDir() + string(os.PathSeparator) + h.Filename
	c.SaveToFile("file", tmmpDir)
	if ext == ".csv" {
		opt := map[string]string{"delimiter": c.GetString("delimiter", "\"")}
		opt["separator"] = c.GetString("separator", ",")
		csvData, err0 := excel.ImpCsv(tmmpDir, opt)
		os.Remove(tmmpDir)
		if err0 != nil {
			c.Data["json"] = c.WarpError(err0.Error())
			c.ServeJSON()
			return
		}
		param["datalist"] = csvData
	} else {
		dataList, err0 := excel.ImpExcel(tmmpDir)
		os.Remove(tmmpDir)
		if err0 != nil {
			c.Data["json"] = c.WarpError(err0.Error())
			c.ServeJSON()
			return
		}
		param["datalist"] = dataList
	}
	if len(param["datalist"].([]map[int]string)) == 0 {
		c.Data["json"] = c.WarpError("文件中没有可导入的数据，请检查后重试")
		c.ServeJSON()
		return
	}
	var err error
	var data interface{}
	//param为excel解析结果
	//以下为你的逻辑处理
	//....
	//siteDataObj := new(bo.SiteData)
	//data, err := siteDataObj.ExpData(GetCurrentUser(c.Ctx), param)
	switch code {
	case "optimize_invalid_fillrule":
		dataOptSrv := new(service.DataoptimizeService)
		dataOptSrv.UserInfo = c.GetCurrentUserInfo_rt()
		err = dataOptSrv.ImpInvalidExcel(param)
		break
	case "optimize_deletion_fillrule":
		dataOptSrv := new(service.DataoptimizeService)
		dataOptSrv.UserInfo = c.GetCurrentUserInfo_rt()
		err = dataOptSrv.ImpDeletionExcel(param)
		break
	case "device_setting":
		ds := new(service.AlarmDeviceService)
		// 将获取到的param导入到数据库中
		ds.UserInfo = c.GetCurrentUserInfo_rt()
		err = ds.ImportExcelDeviceSettingData(param)
		break
	case "device_info":
		devSrv := new(service.DeviceService) //
		devSrv.UserInfo = c.GetCurrentUserInfo_rt()
		err = devSrv.Imp(param)
		break
	case "device_mp_info":
		devSrv := new(service.DeviceService) //
		devSrv.UserInfo = c.GetCurrentUserInfo_rt()
		err = devSrv.ImpMp(param)
		break
	default:
		err = errors.New("未定义的导出业务类型code")
		break
	}
	if err != nil {
		c.Data["json"] = c.ApiError(err.Error())
	} else {
		c.Data["json"] = c.ApiOK(data)
	}
	c.ServeJSON()
}