eta_api/services/data/excel/excel_info.go

package excel

import (
	"encoding/json"
	"errors"
	"eta/eta_api/models"
	"eta/eta_api/models/data_manage"
	"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/models/system"
	"eta/eta_api/services/data"
	"eta/eta_api/services/data/data_manage_permission"
	"eta/eta_api/utils"
	"fmt"
	"sort"
	"strings"
	"time"

	"github.com/xuri/excelize/v2"
)

// GetExcelDetailInfoByExcelInfoId 根据表格id获取表格详情
func GetExcelDetailInfoByExcelInfoId(excelInfoId, sysUserId int, lang string) (excelDetail response.ExcelInfoDetail, errMsg string, err error) {
	errMsg = `获取失败`
	//获取eta表格信息
	excelInfo, err := excel.GetExcelInfoById(excelInfoId)
	if err != nil {
		err = errors.New("获取ETA表格信息失败，Err:" + err.Error())
		if err.Error() == utils.ErrNoRow() {
			errMsg = "ETA表格被删除，请刷新页面"
			err = errors.New("ETA表格被删除，请刷新页面，Err:" + err.Error())
		}
		return
	}

	return formatExcelInfo2Detail(excelInfo, sysUserId, lang)
}

// GetExcelDetailInfoByUnicode 根据表格编码获取表格详情
func GetExcelDetailInfoByUnicode(unicode string, sysUserId int, lang string) (excelDetail response.ExcelInfoDetail, errMsg string, err error) {
	errMsg = `获取失败`
	// 获取eta表格信息
	excelInfo, err := excel.GetExcelInfoByUnicode(unicode)
	if err != nil {
		err = errors.New("获取ETA表格信息失败，Err:" + err.Error())
		if err.Error() == utils.ErrNoRow() {
			errMsg = "ETA表格被删除，请刷新页面"
			err = errors.New("ETA表格被删除，请刷新页面，Err:" + err.Error())
		}
		return
	}

	return formatExcelInfo2Detail(excelInfo, sysUserId, lang)
}

func formatExcelInfo2Detail(excelInfo *excel.ExcelInfo, sysUserId int, lang string) (excelDetail response.ExcelInfoDetail, errMsg string, err error) {
	checkExcelInfo := excelInfo
	if excelInfo.Source == utils.BALANCE_TABLE {
		checkExcelInfoId := excelInfo.ExcelInfoId
		if excelInfo.BalanceType == 1 {
			checkExcelInfoId = excelInfo.RelExcelInfoId
		} else {
			if excelInfo.ParentId > 0 {
				checkExcelInfoId = excelInfo.ParentId
			}
		}
		if checkExcelInfoId != excelInfo.ExcelInfoId {
			checkExcelInfo, err = excel.GetExcelInfoById(checkExcelInfoId)
			if err != nil {
				errMsg = "获取平衡表格信息失败"
				err = errors.New("获取平衡表格信息失败，Err:" + err.Error())
				return
			}
		}
	}

	// 数据权限
	haveOperaAuth, err := data_manage_permission.CheckExcelPermissionByExcelInfoId(checkExcelInfo.ExcelInfoId, checkExcelInfo.ExcelClassifyId, checkExcelInfo.IsJoinPermission, sysUserId)
	if err != nil {
		err = errors.New("获取表格权限信息失败，Err" + err.Error())
		return
	}

	excelDetail = response.ExcelInfoDetail{
		ExcelInfoId:        excelInfo.ExcelInfoId,
		Source:             excelInfo.Source,
		ExcelType:          excelInfo.ExcelType,
		ExcelName:          excelInfo.ExcelName,
		UniqueCode:         excelInfo.UniqueCode,
		ExcelClassifyId:    excelInfo.ExcelClassifyId,
		SysUserId:          excelInfo.SysUserId,
		SysUserRealName:    excelInfo.SysUserRealName,
		Content:            excelInfo.Content,
		ExcelImage:         excelInfo.ExcelImage,
		FileUrl:            excelInfo.FileUrl,
		Sort:               excelInfo.Sort,
		IsDelete:           excelInfo.IsDelete,
		ModifyTime:         excelInfo.ModifyTime,
		CreateTime:         excelInfo.CreateTime,
		TableData:          nil,
		HaveOperaAuth:      haveOperaAuth,
		ParentId:           excelInfo.ParentId,
		BalanceType:        excelInfo.BalanceType,
		UpdateUserId:       excelInfo.UpdateUserId,
		UpdateUserRealName: excelInfo.UpdateUserRealName,
		RelExcelInfoId:     excelInfo.RelExcelInfoId,
		SourcesFrom:        excelInfo.SourcesFrom,
	}

	// 无权限，不需要返回数据
	if !haveOperaAuth {
		return
	}

	switch excelInfo.Source {
	case utils.TIME_TABLE: // 时间序列表格
		var tableDataConfig TableDataConfig
		err = json.Unmarshal([]byte(excelDetail.Content), &tableDataConfig)
		if err != nil {
			err = errors.New("表格json转结构体失败，Err:" + err.Error())
			return
		}
		result, tmpErr := GetDataByTableDataConfig(tableDataConfig)
		if tmpErr != nil {
			err = errors.New("获取最新的表格数据失败，Err:" + tmpErr.Error())
			return
		}

		if len(result.EdbInfoIdList) > 0 {
			classifyIdList := make([]int, 0)
			for _, v := range result.Data {
				classifyIdList = append(classifyIdList, v.ClassifyId)
			}

			classifyMap := make(map[int]*data_manage.EdbClassify)

			classifyList, tmpErr := data_manage.GetEdbClassifyByIdList(classifyIdList)
			if tmpErr != nil {
				err = errors.New("获取分类列表失败，Err:" + tmpErr.Error())
				return
			}

			for _, v := range classifyList {
				classifyMap[v.ClassifyId] = v
			}

			// 获取所有有权限的指标和分类
			permissionEdbIdList, permissionClassifyIdList, tmpErr := data_manage_permission.GetUserEdbAndClassifyPermissionList(sysUserId, 0, 0)
			if err != nil {
				err = errors.New("获取所有有权限的指标和分类失败，Err:" + tmpErr.Error())
				return
			}

			for i, v := range result.Data {
				if currClassify, ok := classifyMap[v.ClassifyId]; ok {
					result.Data[i].HaveOperaAuth = data_manage_permission.CheckEdbPermissionByPermissionIdList(v.IsJoinPermission, currClassify.IsJoinPermission, v.EdbInfoId, v.ClassifyId, permissionEdbIdList, permissionClassifyIdList)
				}
			}
		}

		excelDetail.TableData = result
	case utils.MIXED_TABLE, utils.BALANCE_TABLE: // 混合表格 平衡表
		var result request.MixedTableReq
		err = json.Unmarshal([]byte(excelDetail.Content), &result)
		if err != nil {
			err = errors.New("表格json转结构体失败，Err:" + err.Error())
			return
		}
		newData, tmpErr, tmpErrMsg := GetMixedTableCellData(result, lang)
		if tmpErr != nil {
			errMsg = "获取失败"
			if tmpErrMsg != `` {
				errMsg = tmpErrMsg
			}
			err = errors.New("获取最新的数据失败，Err:" + tmpErr.Error())
			return
		}
		result.Data = newData
		excelDetail.TableData = result
	}
	if excelDetail.Source == utils.BALANCE_TABLE {
		excelDetail.Button = GetBalanceExcelInfoOpButton(sysUserId, checkExcelInfo.SysUserId, excelDetail.HaveOperaAuth, checkExcelInfo.ExcelInfoId)
	}
	return
}

// GetExcelInfoOpButton 获取ETA表格的操作权限
func GetExcelInfoOpButton(sysUser *system.Admin, belongUserId, source int, haveOperaAuth bool) (button excel.ExcelInfoDetailButton) {
	// 如果没有数据权限，那么直接返回
	if !haveOperaAuth {
		return
	}
	//非管理员角色查看其他用户创建的表格，可刷新、另存为、下载表格；
	button.RefreshButton = true
	button.CopyButton = true
	button.DownloadButton = true

	// 1、本用户创建的表格，可编辑、刷新、另存为、下载、删除，删除需二次确认；
	// 2、管理员角色对所有表格有如上权限；
	// 3、在线excel所有人都能编辑
	if sysUser.RoleTypeCode == utils.ROLE_TYPE_CODE_ADMIN || sysUser.RoleTypeCode == utils.ROLE_TYPE_CODE_FICC_ADMIN || sysUser.AdminId == belongUserId || source == utils.EXCEL_DEFAULT {
		button.OpButton = true
		button.DeleteButton = true
	}

	// 自定义分析
	if source == utils.CUSTOM_ANALYSIS_TABLE {
		if sysUser.RoleTypeCode == utils.ROLE_TYPE_CODE_ADMIN || sysUser.RoleTypeCode == utils.ROLE_TYPE_CODE_FICC_ADMIN || sysUser.AdminId == belongUserId {
			button.OpEdbButton = true      // 生成、查看指标按钮
			button.RefreshEdbButton = true // 刷新指标按钮
		}
	}

	return
}

// GetFirstEdbDataList 获取第一列的数据
func GetFirstEdbDataList(edbInfo *data_manage.EdbInfo, num int, manualDateList []string) (resultDataList []request.ManualDataReq, err error) {
	var dataList []*data_manage.EdbDataList
	switch edbInfo.EdbInfoType {
	case 0:
		dataList, err = data_manage.GetEdbDataList(edbInfo.Source, edbInfo.SubSource, edbInfo.EdbInfoId, ``, ``)
	case 1:
		_, dataList, _, _, err, _ = data.GetPredictDataListByPredictEdbInfoId(edbInfo.EdbInfoId, ``, ``, false)
	default:
		err = errors.New(fmt.Sprint("获取失败,指标类型异常", edbInfo.EdbInfoType))
	}
	if err != nil {
		return
	}

	// 获取需要的期数
	lenData := len(dataList)
	if lenData <= 0 {
		return
	}

	tmpManualDateNum := 0 // 手工数据的期数
	lenManualDate := len(manualDateList)
	if lenManualDate > 0 {
		sortDateList := manualDateList
		baseDateList := utils.StrArr{}
		baseDateList = append(baseDateList, sortDateList...)
		sort.Sort(baseDateList)
		sortDateList = append([]string{}, baseDateList...)

		lastData := dataList[lenData-1]
		lastDataDate, tmpErr := time.ParseInLocation(utils.FormatDate, lastData.DataTime, time.Local)
		if tmpErr != nil {
			err = tmpErr
			return
		}

		// 遍历倒序后的日期，然后匹配在实际数据之后日期的个数
		for _, tmpDateStr := range sortDateList {
			tmpDate, tmpErr := time.ParseInLocation(utils.FormatDate, tmpDateStr, time.Local)
			if tmpErr != nil {
				err = tmpErr
				return
			}
			if tmpDate.After(lastDataDate) {
				tmpManualDateNum++
				continue
			}

			break
		}

	}

	// 需要的期数减去手工数据的期数，这才是A列指标需要的数据
	num = num - tmpManualDateNum
	if num > lenData {
		num = lenData
	}

	latestDateTime, _ := time.ParseInLocation(utils.FormatDate, edbInfo.LatestDate, time.Local)
	for i := 1; i <= num; i++ {
		dataTime, _ := time.ParseInLocation(utils.FormatDate, dataList[lenData-i].DataTime, time.Local)
		dataType := 1
		// 如果是预测指标，且当前值的日期，晚于实际日期，那么是预测值
		if edbInfo.EdbInfoType == 1 && dataTime.After(latestDateTime) {
			dataType = 5
		}

		resultDataList = append(resultDataList, request.ManualDataReq{
			DataType:     dataType,
			DataTime:     dataList[lenData-i].DataTime,
			ShowValue:    fmt.Sprint(dataList[lenData-i].Value),
			Value:        fmt.Sprint(dataList[lenData-i].Value),
			DataTimeType: 1,
		})
	}

	return
}

// PadFirstEdbDataList 补齐第一列的数据
func PadFirstEdbDataList(resultDataList []request.ManualDataReq, dateList []string, sortType string) []request.ManualDataReq {
	originDataNum := len(resultDataList)
	requsetDateNum := len(dateList)
	if originDataNum >= requsetDateNum {
		return resultDataList
	}
	padNum := requsetDateNum - originDataNum
	if sortType == "asc" {
		for i := 0; i < padNum; i++ {
			resultDataList = append(resultDataList, request.ManualDataReq{
				DataType:     0,
				DataTime:     dateList[originDataNum+i],
				ShowValue:    ``,
				Value:        ``,
				DataTimeType: 1,
			})
		}
	} else {
		var tmpDateList []request.ManualDataReq
		for i := padNum - 1; i <= 0; i-- {
			tmpDateList = append(tmpDateList, request.ManualDataReq{
				DataType:     0,
				DataTime:     dateList[originDataNum+i],
				ShowValue:    ``,
				Value:        ``,
				DataTimeType: 1,
			})
			resultDataList = append(tmpDateList, resultDataList...)
		}

	}
	return resultDataList
}

// GetOtherEdbDataList 获取其他列的数据
func GetOtherEdbDataList(edbInfo *data_manage.EdbInfo, dateList []string) (resultDataList []request.ManualDataReq, err error) {
	lenDate := len(dateList)
	if lenDate <= 0 {
		return
	}
	sortDateList := dateList
	baseDateList := utils.StrArr{}
	baseDateList = append(baseDateList, sortDateList...)
	sort.Sort(baseDateList)
	sortDateList = append([]string{}, baseDateList...)

	endDateTime, err := time.ParseInLocation(utils.FormatDate, sortDateList[0], time.Local)
	if err != nil {
		return
	}

	firstDateTime, err := time.ParseInLocation(utils.FormatDate, sortDateList[lenDate-1], time.Local)
	if err != nil {
		return
	}

	var dataList []*data_manage.EdbDataList
	switch edbInfo.EdbInfoType {
	case 0:
		dataList, err = data_manage.GetEdbDataList(edbInfo.Source, edbInfo.SubSource, edbInfo.EdbInfoId, ``, ``)
	case 1:
		_, dataList, _, _, err, _ = data.GetPredictDataListByPredictEdbInfoId(edbInfo.EdbInfoId, ``, ``, false)
	default:
		err = errors.New(fmt.Sprint("获取失败,指标类型异常", edbInfo.EdbInfoType))
	}
	if err != nil {
		return
	}

	// 获取日期内的数据（包含开始日期前一个日期，以及 结束日期后一个日期，目的为了做空日期时的 插值法兼容）
	baseDataList := make([]*data_manage.EdbDataList, 0)
	var lastData *data_manage.EdbDataList
	var isInsert bool
	for _, data := range dataList {
		tmpDate := data.DataTime
		tmpDateTime, tmpErr := time.ParseInLocation(utils.FormatDate, tmpDate, time.Local)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		if tmpDateTime.Before(firstDateTime) {
			lastData = data
			continue
		}

		// 如果是第一次写入数据
		if !isInsert && lastData != nil {
			baseDataList = append(baseDataList, lastData)
		}

		if tmpDateTime.After(endDateTime) {
			baseDataList = append(baseDataList, data)
			break
		}
		baseDataList = append(baseDataList, data)
		isInsert = true
	}

	// 实际数据的日期map
	realValMap := make(map[string]string)
	for _, v := range baseDataList {
		realValMap[v.DataTime] = v.DataTime
	}

	// 插值法处理
	handleDataMap := make(map[string]float64)
	err = data.HandleDataByLinearRegression(baseDataList, handleDataMap)
	if err != nil {
		return
	}

	latestDateTime, _ := time.ParseInLocation(utils.FormatDate, edbInfo.LatestDate, time.Local)

	// 对于不存在的数据做补充
	for _, date := range sortDateList {
		dataType := 1
		if _, ok := realValMap[date]; !ok {
			dataType = 2
		} else {
			dataTime, _ := time.ParseInLocation(utils.FormatDate, date, time.Local)
			// 如果是预测指标，且当前值的日期，晚于实际日期，那么是预测值
			if edbInfo.EdbInfoType == 1 && dataTime.After(latestDateTime) {
				dataType = 5
			}
		}
		var value, showValue string
		if tmpVal, ok := handleDataMap[date]; ok {
			value = fmt.Sprint(tmpVal)
			showValue = value
		} else {
			dataType = 3
		}
		resultDataList = append(resultDataList, request.ManualDataReq{
			DataType:  dataType,
			DataTime:  date,
			ShowValue: showValue,
			Value:     value,
		})
	}

	return
}

// GetFirstHistoryEdbDataList 获取指标的历史的数据
func GetFirstHistoryEdbDataList(edbInfo *data_manage.EdbInfo, num int, endDate string) (resultDataList []request.ManualDataReq, err error) {
	endDateTime, err := time.ParseInLocation(utils.FormatDate, endDate, time.Local)
	if err != nil {
		return
	}

	var dataList []*data_manage.EdbDataList
	switch edbInfo.EdbInfoType {
	case 0:
		dataList, err = data_manage.GetEdbDataList(edbInfo.Source, edbInfo.SubSource, edbInfo.EdbInfoId, ``, endDate)
	case 1:
		_, dataList, _, _, err, _ = data.GetPredictDataListByPredictEdbInfoId(edbInfo.EdbInfoId, ``, endDate, true)
	default:
		err = errors.New(fmt.Sprint("获取失败,指标类型异常", edbInfo.EdbInfoType))
	}
	if err != nil {
		return
	}

	// 获取需要的期数
	lenData := len(dataList)
	if lenData <= 0 {
		return
	}
	lastData := dataList[lenData-1]
	lastDataDateTime, err := time.ParseInLocation(utils.FormatDate, lastData.DataTime, time.Local)
	if err != nil {
		return
	}
	if endDateTime.Equal(lastDataDateTime) || lastDataDateTime.After(endDateTime) {
		dataList = dataList[:lenData-1]
		lenData = len(dataList)
	}
	if num > lenData {
		num = lenData
	}

	latestDateTime, _ := time.ParseInLocation(utils.FormatDate, edbInfo.LatestDate, time.Local)
	for i := 1; i <= num; i++ {
		dataTime, _ := time.ParseInLocation(utils.FormatDate, dataList[lenData-i].DataTime, time.Local)
		dataType := 1
		// 如果是预测指标，且当前值的日期，晚于实际日期，那么是预测值
		if edbInfo.EdbInfoType == 1 && dataTime.After(latestDateTime) {
			dataType = 5
		}

		resultDataList = append(resultDataList, request.ManualDataReq{
			DataType:  dataType,
			DataTime:  dataList[lenData-i].DataTime,
			ShowValue: fmt.Sprint(dataList[lenData-i].Value),
			Value:     fmt.Sprint(dataList[lenData-i].Value),
		})
	}

	return
}

// GetEdbIdsFromExcelCodes 获取表格中的指标IDs
func GetEdbIdsFromExcelCodes(excelCodes []string, sysUserId int, lang string) (edbIds []int, err error) {
	edbIds = make([]int, 0)
	edbIdExist := make(map[int]bool)
	for _, v := range excelCodes {
		// 表格详情
		detail, msg, e := GetExcelDetailInfoByUnicode(v, sysUserId, lang)
		if e != nil {
			err = fmt.Errorf("GetExcelDetailInfoByExcelInfoId err: %s, errMsg: %s", e.Error(), msg)
			return
		}

		// 自定义表格
		if detail.Source == utils.TIME_TABLE {
			jsonByte, e := json.Marshal(detail.TableData)
			if e != nil {
				err = fmt.Errorf("JSON格式化自定义表格数据失败, Err: %s", e.Error())
				return
			}
			var tableData request.TableDataReq
			if e = json.Unmarshal(jsonByte, &tableData); e != nil {
				err = fmt.Errorf("解析自定义表格数据失败, Err: %s", e.Error())
				return
			}
			for _, tv := range tableData.EdbInfoIdList {
				if edbIdExist[tv] {
					continue
				}
				edbIdExist[tv] = true
				edbIds = append(edbIds, tv)
			}
		}

		// 混合表格
		if detail.Source == utils.MIXED_TABLE {
			jsonByte, e := json.Marshal(detail.TableData)
			if e != nil {
				err = fmt.Errorf("JSON格式化混合表格数据失败, Err: %s", e.Error())
				return
			}
			var tableData request.MixedTableReq
			if e = json.Unmarshal(jsonByte, &tableData); e != nil {
				err = fmt.Errorf("解析混合表格数据失败, Err: %s", e.Error())
				return
			}
			if len(tableData.Data) > 0 {
				for _, td := range tableData.Data {
					for _, tv := range td {
						if tv.EdbInfoId > 0 && !edbIdExist[tv.EdbInfoId] {
							edbIdExist[tv.EdbInfoId] = true
							edbIds = append(edbIds, tv.EdbInfoId)
						}
					}
				}
			}
		}
	}

	return
}

// GetExcelEdbBatchRefreshKey 获取批量刷新表格指标缓存key
func GetExcelEdbBatchRefreshKey(source string, primaryId, subId int) string {
	if source == `` {
		return ``
	}

	return fmt.Sprint("batch_refresh_excel_edb:", source, ":", primaryId, ":", subId)
}

// GetEdbSourceByEdbInfoIdList 获取关联指标的来源
func GetEdbSourceByEdbInfoIdList(edbInfoIdList []int) (sourceNameList, sourceNameEnList []string, err error) {
	sourceNameList = make([]string, 0)
	sourceNameEnList = make([]string, 0)
	sourceMap := make(map[int]string)
	edbInfoList, tmpErr := data_manage.GetEdbInfoByIdList(edbInfoIdList)
	if tmpErr != nil {
		err = tmpErr
		return
	}
	for _, v := range edbInfoList {
		// 指标类型:1:基础指标,2:计算指标
		if v.EdbType == 2 {
			//sourceMap[0] = "弘则研究"
			baseEdbInfoArr, _, _ := data_manage.GetRefreshEdbInfoFromBase(v.EdbInfoId, v.Source)
			for _, baseEdbInfo := range baseEdbInfoArr {
				if baseEdbInfo.EdbInfoType == 0 { //普通指标才参与，预测指标不参与
					sourceMap[baseEdbInfo.Source] = baseEdbInfo.SourceName
				}
			}
		} else {
			sourceMap[v.Source] = v.SourceName
		}
	}

	for source, sourceName := range sourceMap {
		if utils.InArrayByInt([]int{utils.DATA_SOURCE_MANUAL, utils.DATA_SOURCE_MYSTEEL_CHEMICAL}, source) {
			continue
		}
		sourceNameList = append(sourceNameList, sourceName)

		sourceNameEn, ok := utils.DataSourceEnMap[source]
		if !ok {
			sourceNameEn = sourceName
		}
		sourceNameEnList = append(sourceNameEnList, sourceNameEn)
	}
	//sourceNameList = append(sourceNameList, utils.ChartDefaultNameCn)
	//sourceNameEnList = append(sourceNameEnList, utils.ChartDefaultNameEn)

	// 图表来源
	conf, e := models.GetBusinessConf()
	if e != nil {
		return
	}
	if conf[models.BusinessConfCompanyName] != "" {
		sourceNameList = append(sourceNameList, conf[models.BusinessConfCompanyName])
		sourceNameEnList = append(sourceNameEnList, conf[models.BusinessConfCompanyName])
	}
	return
}

// GetCustomAnalysisOpButton 获取自定义分析按钮权限
func GetCustomAnalysisOpButton(sysUser *system.Admin, belongUserId int, permissionType []int) (button excel.ExcelInfoDetailButton) {
	// 管理员/所属人所有权限
	if sysUser.RoleTypeCode == utils.ROLE_TYPE_CODE_ADMIN || sysUser.RoleTypeCode == utils.ROLE_TYPE_CODE_FICC_ADMIN || sysUser.AdminId == belongUserId {
		button.RefreshButton = true
		button.CopyButton = true
		button.DownloadButton = true

		button.OpEdbButton = true
		button.RefreshEdbButton = true
		button.OpButton = true
		button.DeleteButton = true
		return
	}

	// 非管理员-被分享人
	for _, v := range permissionType {
		if v == 1 {
			button.RefreshButton = true
			button.CopyButton = true
			button.DownloadButton = true
		}
		if v == 2 {
			button.OpEdbButton = true
			button.RefreshEdbButton = true
			button.OpButton = true
			button.CopyButton = true
			button.DownloadButton = true
		}
	}
	return
}

func parseExcelScopeCoord(scopeList []string) (scopeCoord string, err error) {
	if len(scopeList) == 2 {
		x1, y1, er := excelize.CellNameToCoordinates(scopeList[0])
		if er != nil {
			return "", er
		}
		x2, y2, er := excelize.CellNameToCoordinates(scopeList[1])
		if er != nil {
			return "", er
		}
		scopeCoord = fmt.Sprintf("%d,%d,%d,%d", x1, y1, x2, y2)
	}
	if len(scopeList) == 1 {
		x1, y1, er := excelize.CellNameToCoordinates(scopeList[0])
		if er != nil {
			return "", er
		}
		scopeCoord = fmt.Sprintf("%d,%d", x1, y1)
	}
	return
}

func ExcelRuleFormat(req *request.ExcelRuleMappingReq, lang string) (res *excel.ExcelInfoRuleMapping, err error) {
	res = new(excel.ExcelInfoRuleMapping)
	if req.RuleType == 5 {
		switch req.LeftValue {
		// 今天
		case "today":
			res.LeftValueShow = "今天"
		// 明天
		case "tomorrow":
			res.LeftValueShow = "明天"
		// 最近7天
		case "last7days":
			res.LeftValueShow = "最近7天"
		// 上周
		case "lastweek":
			res.LeftValueShow = "上周"
		// 本周
		case "thisweek":
			res.LeftValueShow = "本周"
		// 下周
		case "nextweek":
			res.LeftValueShow = "下周"
		// 上月
		case "lastmonth":
			res.LeftValueShow = "上月"
		// 本月
		case "thismonth":
			res.LeftValueShow = "本月"
		// 下月
		case "nextmonth":
			res.LeftValueShow = "下月"
		default:
			err = errors.New("发生日期规则错误")
			return
		}
	}
	// 格式化条件值
	switch req.LeftValueType {
	// 坐标
	case 2:
		x, y, err := excelize.CellNameToCoordinates(req.LeftValue)
		if err != nil {
			return nil, err
		}
		res.LeftValue = fmt.Sprintf("%d,%d", x, y)
	default:
		res.LeftValue = req.LeftValue
	}
	switch req.RightValueType {
	// 坐标
	case 2:
		x, y, err := excelize.CellNameToCoordinates(req.RightValue)
		if err != nil {
			return nil, err
		}
		res.RightValue = fmt.Sprintf("%d,%d", x, y)
		res.RightValueShow = req.RightValue
	default:
		res.RightValue = req.RightValue
		res.RightValueShow = req.RightValue
	}
	if res.LeftValueShow == "" {
		res.LeftValueShow = req.LeftValue
	}
	if res.RightValueShow == "" {
		res.RightValueShow = req.RightValue
	}
	res.RuleType = req.RuleType
	res.ExcelInfoId = req.ExcelInfoId
	res.ExcelInfoRuleMappingId = req.ExcelRuleMappingId
	res.LeftValueType = req.LeftValueType
	res.RightValueType = req.RightValueType
	res.FontColor = req.FontColor
	res.BackgroundColor = req.BackgroundColor
	res.Remark = req.Remark
	res.RemarkEn = req.Remark
	res.ScopeShow = req.Scope
	scopeList := strings.Split(req.Scope, ":")
	res.ScopeCoord, err = parseExcelScopeCoord(scopeList)
	return
}

func AddExcelRule(req *request.ExcelRuleMappingReq, lang string) (err error) {
	excelRule, err := ExcelRuleFormat(req, lang)
	if err != nil {
		return
	}
	if excelRule.ExcelInfoRuleMappingId != 0 {
		return errors.New("规则已存在")
	}
	excelRule.CreateTime = time.Now()
	_, err = excelRule.Insert()
	return
}

func ModifyExcelRule(req *request.ExcelRuleMappingReq, lang string) (err error) {
	excelInfo, err := excel.GetExcelRuleMappingById(req.ExcelRuleMappingId)
	if err != nil {
		return
	}
	editExcelInfo, err := ExcelRuleFormat(req, lang)
	if err != nil {
		return
	}
	var updateCols []string
	if excelInfo.LeftValue != editExcelInfo.LeftValue {
		updateCols = append(updateCols, "LeftValue")
		updateCols = append(updateCols, "LeftValueShow")
	}
	if excelInfo.LeftValueType != editExcelInfo.LeftValueType {
		updateCols = append(updateCols, "LeftValueType")
	}
	if excelInfo.RightValue != editExcelInfo.RightValue {
		updateCols = append(updateCols, "RightValue")
		updateCols = append(updateCols, "RightValueShow")
	}
	if excelInfo.RightValueType != editExcelInfo.RightValueType {
		updateCols = append(updateCols, "RightValueType")
	}
	if excelInfo.FontColor != editExcelInfo.FontColor {
		updateCols = append(updateCols, "FontColor")
	}
	if excelInfo.BackgroundColor != editExcelInfo.BackgroundColor {
		updateCols = append(updateCols, "BackgroundColor")
	}
	if excelInfo.Remark != editExcelInfo.Remark {
		updateCols = append(updateCols, "Remark")
	}
	if excelInfo.RemarkEn != editExcelInfo.RemarkEn {
		updateCols = append(updateCols, "RemarkEn")
	}
	if excelInfo.ScopeShow != editExcelInfo.ScopeShow {
		updateCols = append(updateCols, "ScopeCoord")
		updateCols = append(updateCols, "ScopeShow")
	}
	if len(updateCols) > 0 {
		err = editExcelInfo.Update(updateCols)
	}

	return
}

// GetExcelRuleList 获取规则列表
func GetExcelRuleList(excelInfoId int) (resp *response.ExcelRuleListResp, err error) {
	resp = new(response.ExcelRuleListResp)
	excelInfoList, err := excel.GetExcelRuleMappingByExcelInfoId(excelInfoId)
	if err != nil {
		return
	}
	for _, v := range excelInfoList {
		switch v.RuleType {
		// 大于
		case 1:
			v.LeftValueShow = "大于" + v.LeftValueShow
		// 小于
		case 2:
			v.LeftValueShow = "小于" + v.LeftValueShow
		// 介于
		case 3:
			v.LeftValueShow = "介于" + v.LeftValueShow + "到" + v.RightValueShow
		// 等于
		case 4:
			v.LeftValueShow = "等于" + v.LeftValueShow
		}
	}

	resp.List = excelInfoList
	return
}

// GetExcelRuleDetail 获取规则列表
func GetExcelRuleDetail(excelInfoMappingId int) (resp *excel.ExcelInfoRuleMappingView, err error) {
	resp = new(excel.ExcelInfoRuleMappingView)
	excelInfoDetail, err := excel.GetExcelRuleMappingById(excelInfoMappingId)
	if err != nil {
		return
	}
	resp = excelInfoDetail
	return
}