eta_api/controllers/data_manage/excel/custom_analysis.go

package excel

import (
	"encoding/json"
	"eta/eta_api/controllers"
	"eta/eta_api/models"
	excelModel "eta/eta_api/models/data_manage/excel"
	"eta/eta_api/models/data_manage/excel/request"
	"eta/eta_api/models/data_manage/excel/response"
	"eta/eta_api/services/data/excel"
	"eta/eta_api/utils"
	"fmt"
	"strconv"
	"strings"
	"time"
)

// CustomAnalysisController 自定义分析
type CustomAnalysisController struct {
	controllers.BaseAuthController
}

// ExcelByName
// @Title 根据excel名称获取表格详情（基础信息+第一页初始化数据）
// @Description 根据excel名称获取表格详情（基础信息+第一页初始化数据）
// @Param   ExcelName   query   string  true       "搜索关键词"
// @Success 200 {object} response.ExcelListResp
// @router /excel_by_name [get]
func (c *CustomAnalysisController) ExcelByName() {
	br := new(models.BaseResponse).Init()
	defer func() {
		c.Data["json"] = br
		c.ServeJSON()
	}()
	sysUser := c.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录，SysUser Is Empty"
		br.Ret = 408
		return
	}

	excelName := c.GetString("ExcelName")
	if excelName == `` {
		br.Msg = "请选择表格"
		br.ErrMsg = "ExcelName未传"
		br.IsSendEmail = false
		return
	}

	resp := response.FindExcelInfoResp{}

	excelName = utils.TrimLRStr(excelName)
	// 获取数据详情
	excelDetail, err := excelModel.GetNoContentExcelInfoByName(excelName, utils.CUSTOM_ANALYSIS_TABLE)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			br.Ret = 200
			br.Success = true
			br.Msg = "获取成功"
			br.Data = resp
			return
		}
		br.Msg = "获取表格事变"
		br.ErrMsg = err.Error()
		return
	}

	resp.IsFind = true
	resp.ExcelInfo = excelDetail
	if excelDetail != nil {
		sheetList, err := excelModel.GetAllSheetItemList(excelDetail.ExcelInfoId)
		if err != nil {
			br.Msg = "获取sheet失败"
			br.ErrMsg = "获取sheet失败，err:" + err.Error()
			return
		}

		resp.SheetList = sheetList
	}

	//resp := response.ExcelListResp{
	//	Paging: page,
	//	List:   list,
	//}

	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	br.Data = resp
}

// Add
// @Title 新增表格接口
// @Description 新增表格接口
// @Param	request	body request.AddExcelInfoReq true "type json string"
// @Success 200 {object} response.AddExcelInfoResp
// @router /add [post]
func (c *CustomAnalysisController) Add() {
	br := new(models.BaseResponse).Init()
	defer func() {
		c.Data["json"] = br
		c.ServeJSON()
	}()

	sysUser := c.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录，SysUser Is Empty"
		br.Ret = 408
		return
	}
	deleteCache := true
	cacheKey := "CACHE_EXCEL_TABLE_INFO_ADD_" + strconv.Itoa(sysUser.AdminId)
	defer func() {
		if deleteCache {
			_ = utils.Rc.Delete(cacheKey)
		}
	}()
	if !utils.Rc.SetNX(cacheKey, 1, 30*time.Second) {
		deleteCache = false
		br.Msg = "系统处理中，请稍后重试！"
		br.ErrMsg = "系统处理中，请稍后重试！" + sysUser.RealName + ";data:" + string(c.Ctx.Input.RequestBody)
		return
	}
	var req request.AddExcelInfoReq
	err := json.Unmarshal(c.Ctx.Input.RequestBody, &req)
	if err != nil {
		br.Msg = "参数解析异常！"
		br.ErrMsg = "参数解析失败，Err:" + err.Error()
		return
	}
	req.ExcelName = strings.Trim(req.ExcelName, " ")
	if req.ExcelName == "" {
		br.Msg = "请填写表格名称！"
		br.IsSendEmail = false
		return
	}

	if req.ExcelClassifyId <= 0 {
		br.Msg = "分类参数错误！"
		br.IsSendEmail = false
		return
	}

	excelInfo, err, errMsg, isSendEmail := excel.AddCustomAnalysisTable(utils.TrimLRStr(req.ExcelName), req.Content, req.ExcelImage, req.ExcelClassifyId, sysUser)
	if err != nil {
		br.Msg = "保存失败"
		if errMsg != `` {
			br.Msg = errMsg
		}
		br.ErrMsg = "保存失败，Err:" + err.Error()
		br.IsSendEmail = isSendEmail
		return
	}

	// 更新excel下载地址(默认的EXCEL需要更新，自定义表格不需要更新）
	//if req.Source == 1 {
	//	go UpdateExcelInfoFileUrl(excelInfo)
	//}
	//
	resp := new(response.AddExcelInfoResp)
	resp.ExcelInfoId = excelInfo.ExcelInfoId
	resp.UniqueCode = excelInfo.UniqueCode

	// 生成excel文件
	go excel.UpdateExcelInfoFileUrl(excelInfo)

	//新增操作日志
	//{
	//	excelLog := &data_manage.ExcelInfoLog{
	//		//ExcelInfoLogId:  0,
	//		ExcelInfoId:     excelInfo.ExcelInfoId,
	//		ExcelName:       req.ExcelName,
	//		ExcelClassifyId: req.ExcelClassifyId,
	//		SysUserId:       sysUser.AdminId,
	//		SysUserRealName: sysUser.RealName,
	//		UniqueCode:      excelInfo.UniqueCode,
	//		CreateTime:      time.Now(),
	//		Content:         string(c.Ctx.Input.RequestBody),
	//		Status:          "新增表格",
	//		Method:          c.Ctx.Input.URI(),
	//	}
	//	go data_manage.AddExcelInfoLog(excelLog)
	//}

	br.Ret = 200
	br.Success = true
	br.Msg = "保存成功"
	br.Data = resp
	br.IsAddLog = false //数据量太大了，不写入日志吧
}

// Save
// @Title 保存表格接口
// @Description 保存表格接口
// @Param	request	body request.AddExcelInfoReq true "type json string"
// @Success 200 {object} response.AddExcelInfoResp
// @router /save [post]
func (c *CustomAnalysisController) Save() {
	br := new(models.BaseResponse).Init()
	defer func() {
		c.Data["json"] = br
		c.ServeJSON()
	}()

	sysUser := c.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录，SysUser Is Empty"
		br.Ret = 408
		return
	}
	deleteCache := true
	cacheKey := "CACHE_EXCEL_TABLE_INFO_ADD_" + strconv.Itoa(sysUser.AdminId)
	defer func() {
		if deleteCache {
			_ = utils.Rc.Delete(cacheKey)
		}
	}()
	if !utils.Rc.SetNX(cacheKey, 1, 30*time.Second) {
		deleteCache = false
		br.Msg = "系统处理中，请稍后重试！"
		br.ErrMsg = "系统处理中，请稍后重试！" + sysUser.RealName + ";data:" + string(c.Ctx.Input.RequestBody)
		return
	}
	var req request.SaveExcelInfoReq
	err := json.Unmarshal(c.Ctx.Input.RequestBody, &req)
	if err != nil {
		br.Msg = "参数解析异常！"
		br.ErrMsg = "参数解析失败，Err:" + err.Error()
		return
	}
	req.ExcelName = strings.Trim(req.ExcelName, " ")
	if req.ExcelName == "" {
		br.Msg = "请填写表格名称！"
		br.IsSendEmail = false
		return
	}

	if req.ExcelInfoId <= 0 {
		br.Msg = "请选择excel！"
		br.IsSendEmail = false
		return
	}

	if req.ExcelClassifyId <= 0 {
		br.Msg = "分类参数错误！"
		br.IsSendEmail = false
		return
	}

	excelInfo, err := excelModel.GetExcelInfoById(req.ExcelInfoId)
	if err != nil {
		br.Msg = "找不到该EXCEL！"
		br.ErrMsg = "找不到该EXCEL！err:" + err.Error()
		return
	}

	if excelInfo.Source != utils.CUSTOM_ANALYSIS_TABLE {
		br.Msg = "EXCEL异常！"
		br.IsSendEmail = false
		return
	}

	err, errMsg, isSendEmail := excel.SaveCustomAnalysisTable(excelInfo, utils.TrimLRStr(req.ExcelName), req.Content, req.ExcelImage, req.ExcelClassifyId, req.OpSheetList)
	if err != nil {
		br.Msg = "保存失败"
		if errMsg != `` {
			br.Msg = errMsg
		}
		br.ErrMsg = "保存失败，Err:" + err.Error()
		br.IsSendEmail = isSendEmail
		return
	}

	// 更新excel下载地址(默认的EXCEL需要更新，自定义表格不需要更新）
	//if req.Source == 1 {
	//	go UpdateExcelInfoFileUrl(excelInfo)
	//}
	//
	resp := new(response.AddExcelInfoResp)
	resp.ExcelInfoId = excelInfo.ExcelInfoId
	resp.UniqueCode = excelInfo.UniqueCode

	// 生成excel文件
	go excel.UpdateExcelInfoFileUrl(excelInfo)

	//新增操作日志
	//{
	//	excelLog := &data_manage.ExcelInfoLog{
	//		//ExcelInfoLogId:  0,
	//		ExcelInfoId:     excelInfo.ExcelInfoId,
	//		ExcelName:       req.ExcelName,
	//		ExcelClassifyId: req.ExcelClassifyId,
	//		SysUserId:       sysUser.AdminId,
	//		SysUserRealName: sysUser.RealName,
	//		UniqueCode:      excelInfo.UniqueCode,
	//		CreateTime:      time.Now(),
	//		Content:         string(c.Ctx.Input.RequestBody),
	//		Status:          "新增表格",
	//		Method:          c.Ctx.Input.URI(),
	//	}
	//	go data_manage.AddExcelInfoLog(excelLog)
	//}

	br.Ret = 200
	br.Success = true
	br.Msg = "保存成功"
	br.Data = resp
	br.IsAddLog = false //数据量太大了，不写入日志吧
}

// BaseExcelDetail
// @Title 根据excel名称获取表格详情（基础信息+第一页初始化数据）
// @Description 根据excel名称获取表格详情（基础信息+第一页初始化数据）
// @Param   UniqueCode   query   string  true       "excel唯一编码"
// @Success 200 {object} response.ExcelListResp
// @router /excel/base [get]
func (c *CustomAnalysisController) BaseExcelDetail() {
	br := new(models.BaseResponse).Init()
	defer func() {
		c.Data["json"] = br
		c.ServeJSON()
	}()
	sysUser := c.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录，SysUser Is Empty"
		br.Ret = 408
		return
	}

	uniqueCode := c.GetString("UniqueCode")
	if uniqueCode == `` {
		br.Msg = "请选择表格"
		br.ErrMsg = "UniqueCode未传"
		br.IsSendEmail = false
		return
	}

	resp := response.FindExcelInfoResp{}

	// 获取数据详情
	excelDetail, err := excelModel.GetNoContentExcelInfoByUniqueCode(uniqueCode)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			br.Ret = 200
			br.Success = true
			br.Msg = "获取成功"
			br.Data = resp
			return
		}
		br.Msg = "获取表格事变"
		br.ErrMsg = err.Error()
		return
	}

	resp.IsFind = true
	resp.ExcelInfo = excelDetail
	if excelDetail != nil {
		sheetList, err := excelModel.GetAllSheetItemList(excelDetail.ExcelInfoId)
		if err != nil {
			br.Msg = "获取sheet失败"
			br.ErrMsg = "获取sheet失败，err:" + err.Error()
			return
		}

		if len(sheetList) > 0 {
			sheetIdList := make([]int, 0)
			for _, v := range sheetList {
				sheetIdList = append(sheetIdList, v.ExcelSheetId)
			}
			// 获取所有sheet的第一页的数据
			sheetDataList, err := excelModel.GetSheetDataListBySheetIdListAndPage(sheetIdList, 1)
			if err != nil {
				br.Msg = "获取sheet中的数据失败"
				br.ErrMsg = "获取sheet中的数据失败，err:" + err.Error()
				return
			}

			sheetDataMap := make(map[int]*excelModel.ExcelSheetData)
			for _, v := range sheetDataList {
				sheetDataMap[v.ExcelSheetId] = v
			}

			for k, v := range sheetList {
				sheetData, ok := sheetDataMap[v.ExcelSheetId]
				if !ok {
					continue
				}
				v.Data = sheetData
				sheetList[k] = v
			}

		}

		// TODO 合并单元格信息、计算公式

		resp.SheetList = sheetList
	}

	//resp := response.ExcelListResp{
	//	Paging: page,
	//	List:   list,
	//}

	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	br.Data = resp
}

// ExcelDataList
// @Title 根据excel名称获取表格详情（基础信息+第一页初始化数据）
// @Description 根据excel名称获取表格详情（基础信息+第一页初始化数据）
// @Param   UniqueCode   query   string  true       "excel唯一编码"
// @Param   Page   query   int  true       "页码"
// @Success 200 {object} response.ExcelListResp
// @router /excel/data [get]
func (c *CustomAnalysisController) ExcelDataList() {
	br := new(models.BaseResponse).Init()
	defer func() {
		c.Data["json"] = br
		c.ServeJSON()
	}()
	sysUser := c.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录，SysUser Is Empty"
		br.Ret = 408
		return
	}

	uniqueCode := c.GetString("UniqueCode")
	if uniqueCode == `` {
		br.Msg = "请选择表格"
		br.ErrMsg = "UniqueCode未传"
		br.IsSendEmail = false
		return
	}

	page, _ := c.GetInt("Page")
	if page <= 0 {
		br.Msg = "页码异常"
		br.ErrMsg = "页码异常"
		br.IsSendEmail = false
		return
	}

	sheetList := make([]*excelModel.SheetItem, 0)
	// 获取数据详情
	excelDetail, err := excelModel.GetNoContentExcelInfoByUniqueCode(uniqueCode)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			br.Ret = 200
			br.Success = true
			br.Msg = "获取成功"
			br.Data = sheetList
			return
		}
		br.Msg = "获取表格事变"
		br.ErrMsg = err.Error()
		return
	}

	if excelDetail.Source != utils.CUSTOM_ANALYSIS_TABLE {
		br.Msg = "excel异常"
		br.ErrMsg = "excel异常"
		br.IsSendEmail = false
		return
	}

	if excelDetail != nil {
		sheetList, err = excelModel.GetAllNoConfigSheetItemList(excelDetail.ExcelInfoId)
		if err != nil {
			br.Msg = "获取sheet失败"
			br.ErrMsg = "获取sheet失败，err:" + err.Error()
			return
		}

		if len(sheetList) > 0 {
			sheetIdList := make([]int, 0)
			for _, v := range sheetList {
				sheetIdList = append(sheetIdList, v.ExcelSheetId)
			}
			// 获取所有sheet的第一页的数据
			sheetDataList, err := excelModel.GetSheetDataListBySheetIdListAndPage(sheetIdList, page)
			if err != nil {
				br.Msg = "获取sheet中的数据失败"
				br.ErrMsg = "获取sheet中的数据失败，err:" + err.Error()
				return
			}

			sheetDataMap := make(map[int]*excelModel.ExcelSheetData)
			for _, v := range sheetDataList {
				sheetDataMap[v.ExcelSheetId] = v
			}

			for k, v := range sheetList {
				sheetData, ok := sheetDataMap[v.ExcelSheetId]
				if !ok {
					continue
				}
				v.Data = sheetData
				sheetList[k] = v
			}

		}

		// TODO 合并单元格信息、计算公式

	}

	//resp := response.ExcelListResp{
	//	Paging: page,
	//	List:   list,
	//}

	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	br.Data = sheetList
}

// FixTableData ETA1.0.2 自定义分析（生成指标数据修复）
func FixTableData() {
	// 获取一级分类
	classifyList, err := excelModel.GetExcelClassifyByParentId(0, utils.EXCEL_DEFAULT)
	if err != nil && err.Error() != utils.ErrNoRow() {
		fmt.Println("数据修复失败，Err:" + err.Error())
		return
	}
	timeTableMap := make(map[int]int)
	mixTableMap := make(map[int]int)
	for _, v := range classifyList {
		// 时间序列表格
		classify := &excelModel.ExcelClassify{
			//ExcelClassifyId:   0,
			ExcelClassifyName: v.ExcelClassifyName,
			ParentId:          v.ParentId,
			Source:            utils.TIME_TABLE,
			SysUserId:         v.SysUserId,
			SysUserRealName:   v.SysUserRealName,
			Level:             v.Level,
			UniqueCode:        utils.MD5(fmt.Sprint(v.UniqueCode, "_", utils.TIME_TABLE)),
			Sort:              v.Sort,
			CreateTime:        time.Now(),
			ModifyTime:        time.Now(),
		}
		_, err = excelModel.AddExcelClassify(classify)
		timeTableMap[v.ExcelClassifyId] = classify.ExcelClassifyId

		// 混合表格
		classify2 := &excelModel.ExcelClassify{
			//ExcelClassifyId:   0,
			ExcelClassifyName: v.ExcelClassifyName,
			ParentId:          v.ParentId,
			Source:            utils.MIXED_TABLE,
			SysUserId:         v.SysUserId,
			SysUserRealName:   v.SysUserRealName,
			Level:             v.Level,
			UniqueCode:        utils.MD5(fmt.Sprint(v.UniqueCode, "_", utils.MIXED_TABLE)),
			Sort:              v.Sort,
			CreateTime:        time.Now(),
			ModifyTime:        time.Now(),
		}
		_, err = excelModel.AddExcelClassify(classify2)
		mixTableMap[v.ExcelClassifyId] = classify2.ExcelClassifyId
	}

	// 修改时间序列表
	{
		// 获取时间序列表
		timeTableExcelList, err := excelModel.GetNoContentExcelInfoAll(utils.TIME_TABLE, 0)
		if err != nil && err.Error() != utils.ErrNoRow() {
			fmt.Println("获取时间序列表列表失败，Err:" + err.Error())
			return
		}

		for _, v := range timeTableExcelList {
			classifyId, ok := timeTableMap[v.ExcelClassifyId]
			if !ok {
				continue
			}
			excelModel.UpdateExcelInfoClassifyId(classifyId, v.ExcelInfoId)
		}
	}

	// 修改混合序列表
	{
		// 获取时间序列表
		mixTableExcelList, err := excelModel.GetNoContentExcelInfoAll(utils.MIXED_TABLE, 0)
		if err != nil && err.Error() != utils.ErrNoRow() {
			fmt.Println("获取时间序列表列表失败，Err:" + err.Error())
			return
		}

		for _, v := range mixTableExcelList {
			classifyId, ok := mixTableMap[v.ExcelClassifyId]
			if !ok {
				continue
			}
			excelModel.UpdateExcelInfoClassifyId(classifyId, v.ExcelInfoId)
		}
	}

	fmt.Println("完成数据修复")
}

// FixTableDataMapping ETA1.0.2 自定义分析（修复excel与指标的关系）
func FixTableDataMapping() {

	// 修改时间序列表
	{
		// 获取时间序列表
		timeTableExcelList, err := excelModel.GetAllExcelInfoBySource(utils.TIME_TABLE)
		if err != nil && err.Error() != utils.ErrNoRow() {
			fmt.Println("获取时间序列表列表失败，Err:" + err.Error())
			return
		}

		for _, v := range timeTableExcelList {

			var tableData request.TableDataReq
			err = json.Unmarshal([]byte(v.Content), &tableData)
			if err != nil {
				fmt.Println(v.ExcelInfoId, "json转结构体失败，Err:"+err.Error())
				continue
			}
			if len(tableData.EdbInfoIdList) > 0 {
				excelEdbMappingList := make([]*excelModel.ExcelEdbMapping, 0)
				for _, edbInfoId := range tableData.EdbInfoIdList {
					excelEdbMappingList = append(excelEdbMappingList, &excelModel.ExcelEdbMapping{
						//ExcelEdbMappingId: 0,
						ExcelInfoId: v.ExcelInfoId,
						Source:      v.Source,
						EdbInfoId:   edbInfoId,
						CreateTime:  time.Now(),
						ModifyTime:  time.Now(),
					})
				}
				err = excelModel.AddExcelEdbMappingMulti(excelEdbMappingList)
				if err != nil {
					fmt.Println(v.ExcelInfoId, "自定义表格关系保存失败,Err:"+err.Error())
					continue
				}
			}

		}
	}

	//// 修改混合序列表
	//{
	//	// 获取时间序列表
	//	mixTableExcelList, err := excelModel.GetAllExcelInfoBySource(utils.MIXED_TABLE)
	//	if err != nil && err.Error() != utils.ErrNoRow() {
	//		fmt.Println("获取时间序列表列表失败，Err:" + err.Error())
	//		return
	//	}
	//
	//	for _, excelInfo := range mixTableExcelList {
	//		var result request.MixedTableReq
	//		err = json.Unmarshal([]byte(excelInfo.Content), &result)
	//		if err != nil {
	//			fmt.Println(excelInfo.ExcelInfoId, "修改混合序列表,json转结构体失败，Err:"+err.Error())
	//			continue
	//		}
	//		newResult, tmpErr := excel.GetMixedTableCellData(result.Data)
	//		if tmpErr != nil {
	//			fmt.Println(excelInfo.ExcelInfoId, "获取最新的数据失败，Err:"+err.Error())
	//			continue
	//		}
	//		edbInfoIdList := make([]int, 0)
	//		edbInfoIdMap := make(map[int]int)
	//		for _, tmpV := range newResult {
	//			for _, v := range tmpV {
	//				if v.EdbInfoId > 0 {
	//					if _, ok := edbInfoIdMap[v.EdbInfoId]; !ok {
	//						edbInfoIdMap[v.EdbInfoId] = v.EdbInfoId
	//						edbInfoIdList = append(edbInfoIdList, v.EdbInfoId)
	//					}
	//				}
	//			}
	//		}
	//
	//		if len(edbInfoIdList) > 0 {
	//			excelEdbMappingList := make([]*excelModel.ExcelEdbMapping, 0)
	//			for _, edbInfoId := range edbInfoIdList {
	//				excelEdbMappingList = append(excelEdbMappingList, &excelModel.ExcelEdbMapping{
	//					//ExcelEdbMappingId: 0,
	//					ExcelInfoId: excelInfo.ExcelInfoId,
	//					Source:      excelInfo.Source,
	//					EdbInfoId:   edbInfoId,
	//					CreateTime:  time.Now(),
	//					ModifyTime:  time.Now(),
	//				})
	//			}
	//			err = excelModel.AddExcelEdbMappingMulti(excelEdbMappingList)
	//			if err != nil {
	//				fmt.Println(excelInfo.ExcelInfoId, "混合表格关系保存失败,Err:"+err.Error())
	//				continue
	//			}
	//		}
	//
	//	}
	//
	//}

	fmt.Println("完成数据修复")
}