eta_api/services/data/excel/mixed_table.go

package excel

import (
	"encoding/json"
	"errors"
	"eta/eta_api/models/data_manage"
	"eta/eta_api/models/data_manage/excel/request"
	"eta/eta_api/services/data"
	"eta/eta_api/utils"
	"fmt"
	"github.com/shopspring/decimal"
	"github.com/yidane/formula"
	"sort"
	"strconv"
	"strings"
	"time"
)

// BaseCalculate
// @Description: 指标数据计算请求
type BaseCalculate struct {
	DataList      []*data_manage.EdbDataList
	Frequency     string `description:"需要转换的频度"`
	Formula       interface{}
	Calendar      string `description:"公历/农历"`
	MoveType      int    `description:"移动方式:1:领先(默认),2:滞后"`
	MoveFrequency string `description:"移动频度"`
	FromFrequency string `description:"来源的频度"`
	Source        int    `description:"1：累计值转月；2：累计值转季；3：同比值；4：同差值；5：N数值移动平均数计算；6：环比值；7：环差值；8：升频；9：降频；10：时间移位；11：超季节性；12：年化；13：累计值；14：累计值年初至今；15：指数修匀；16：日均值"`
}

// Cell
// @Description: 单元格位置
type Cell struct {
	Column   int                           `description:"行"`
	Row      int                           `description:"列"`
	CellInfo request.MixedTableCellDataReq `description:"对应的单元格信息"`
}

// GetMixedTableCellData 获取混合表格数据
func GetMixedTableCellData(mixedTableReq request.MixedTableReq, lang string) (newMixedTableCellDataList [][]request.MixedTableCellDataReq, err error, errMsg string) {
	cellRelationConf := mixedTableReq.CellRelation
	config := mixedTableReq.Data

	// 单元格关系配置x信息
	cellRelationConfMap := make(map[string]request.CellRelationConf)
	cellRelationConfList := make([]request.CellRelationConf, 0)
	if cellRelationConf != `` {
		err = json.Unmarshal([]byte(cellRelationConf), &cellRelationConfList)
		if err != nil {
			return
		}

		for _, v := range cellRelationConfList {
			cellRelationConfMap[v.Key] = v
		}
	}

	// 找出所有的关联指标id
	config, edbInfoIdList, _, err, errMsg := handleConfig(config)
	if err != nil {
		return
	}

	// 查询所有关联的指标信息
	edbInfoList, err := data_manage.GetEdbInfoByIdList(edbInfoIdList)
	if err != nil {
		return
	}

	// 指标信息map
	edbInfoMap := make(map[int]*data_manage.EdbInfo)
	// 日度指标数据map
	edbDayDataListMap := make(map[int]map[string]float64)
	// 月度指标数据map
	edbMonthDataListMap := make(map[int]map[string]string)
	// 日度指标数据map
	edbDataListMap := make(map[int][]*data_manage.EdbDataList)
	for _, edbInfo := range edbInfoList {
		edbInfoMap[edbInfo.EdbInfoId] = edbInfo

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

		dateValMap := make(map[string]float64)
		monthDateMap := make(map[string]string)
		for _, tmpData := range dataList {
			// 日度数据
			dateValMap[tmpData.DataTime] = tmpData.Value
			// 月度数据（取该月份的第一个数据）
			yearMonth := strings.Join(strings.Split(tmpData.DataTime, "-")[0:2], "-")
			if _, ok := monthDateMap[yearMonth]; !ok {
				// 存最早的时间
				monthDateMap[yearMonth] = tmpData.DataTime
			}
		}
		edbDayDataListMap[edbInfo.EdbInfoId] = dateValMap
		edbMonthDataListMap[edbInfo.EdbInfoId] = monthDateMap
		edbDataListMap[edbInfo.EdbInfoId] = dataList
	}

	// 单元格实际绑定的信息map
	cellDataRelationMap := make(map[string]request.MixedTableCellDataReq, 0)

	// 处理指定指标的日期
	for k, row := range config {
		for i, cell := range row {
			// 单元格是日期类型，且是日导入指标日期（指标库的最新日期）
			if cell.DataType == request.DateDT && cell.DataTimeType == request.EdbDateDT {
				// 指标id是在配置里面
				var edbDateConfig request.EdbDateConf
				err = json.Unmarshal([]byte(cell.Value), &edbDateConfig)
				if err != nil {
					return
				}
				if dataList, ok := edbDataListMap[edbDateConfig.EdbInfoId]; ok {
					// todo 获取配置信息，根据配置信息进行日期变换, 是否需要更新当前记录，将历史记录逐渐转换成新的记录
					var newDate string
					newDate, err = GetEdbDateByMoveForward(config[k][i].Value, dataList)
					if err != nil {
						return
					}
					newDate, err = HandleMixTableDateChange(newDate, config[k][i].Value)
					if err != nil {
						return
					}
					cell.ShowValue = newDate
					cell.DataTime = newDate
					config[k][i] = cell
				}
			}
			row[i] = cell

			cellDataRelationMap[cell.Uid] = cell
		}
		config[k] = row
	}

	// 指标计算的结果map
	edbSourceDataMap := make(map[string]data.BaseCalculateDataResp)

	// 单元格对应的key与他的值（只处理数据类型）
	cellKeyVal := make(map[string]float64)

	// 基础计算单元格的位置信息
	calculateCellMap := make(map[string]Cell)
	calculateChainList := make([]string, 0)
	dateCalculateList := make([]string, 0)
	showStyleList := make([]string, 0)

	// 处理单元格中的数据类型（除去基础计算，因为这个是依赖于其他）
	for k, row := range config {
		for i, cell := range row {
			switch cell.DataType {
			case request.EdbDT: // 指标类型
				if cell.Value == `` {
					if edbInfo, ok := edbInfoMap[cell.EdbInfoId]; ok {
						cell.ShowValue = edbInfo.EdbName
					}
				} else {
					cell.ShowValue = cell.Value
				}
			case request.InsertDataDT, request.PopInsertDataDT: // 数据类型
				// 数值先清空
				cell.ShowValue = ``
				//cell.Value = ``

				// 日期关系配置不存在，则默认最新数据
				if relationConf, ok := cellRelationConfMap[cell.Uid]; ok { //表示表格日期
					if relationConf.RelationDate.Key == `` {
						// 日期关系配置未绑定
						continue
					}
					// 配置
					relationCell, ok := cellDataRelationMap[relationConf.RelationDate.Key]
					if !ok {
						// 找不到对应日期的单元格
						continue
					}

					// 确实是找到了这个关联日期的单元格，那么通过日期重新获取数据值
					tmpDateValMap := make(map[string]float64)
					// 日度数据
					if dateValMap, ok := edbDayDataListMap[cell.EdbInfoId]; ok {
						tmpDateValMap = dateValMap
					}
					// todo 根据配置进行日期变换
					relationDate := relationCell.DataTime
					if strings.Contains(cell.Value, "{") {
						relationDate, err = HandleMixTableDateChange(relationDate, cell.Value)
						if err != nil {
							return
						}
					} else {
						cell.Value = ""
					}

					if val, ok2 := tmpDateValMap[relationDate]; ok2 {
						//cell.ShowValue = fmt.Sprint(val)
						cellKeyVal[cell.Uid] = val
						cell.ShowValue = utils.FormatMixTableDataShowValue(val)
					}
				} else {
					// 如果不是取得一个关联的日期，那么就是指定日期
					// 如果没有指定日期，则默认最新数据
					if cell.DataTime == `` {
						// 指标的最新日期
						if dateValList, ok := edbDataListMap[cell.EdbInfoId]; ok {
							tmpLenData := len(dateValList)
							if tmpLenData > 0 {
								//做期数前移动和日期变换
								if !strings.Contains(cell.Value, "{") {
									cell.Value = ""
								}
								var newDate string
								newDate, err = GetEdbDateByMoveForward(cell.Value, dateValList)
								if err != nil {
									return
								}
								newDate, err = HandleMixTableDateChange(newDate, cell.Value)
								if err != nil {
									return
								}
								var finalVal string
								for _, v := range dateValList {
									if v.DataTime == newDate {
										finalVal = utils.FormatMixTableDataShowValue(v.Value)
										cellKeyVal[cell.Uid] = v.Value
										break
									}
								}

								cell.ShowValue = finalVal
							}
						}
					} else {
						tmpDateList := strings.Split(cell.DataTime, "-")
						tmpDateValMap := make(map[string]float64)
						var newDate string
						if len(tmpDateList) == 2 {
							//月度数据
							if dateMap, ok1 := edbMonthDataListMap[cell.EdbInfoId]; ok1 {
								if d, ok2 := dateMap[cell.DataTime]; ok2 {
									newDate = d
								}
							}
						} else {
							// 日度数据
							newDate = cell.DataTime
						}
						// 日期变换后才能确定最后的时间
						//做期数前移动和日期变换
						if !strings.Contains(cell.Value, "{") {
							cell.Value = ""
						}

						newDate, err = HandleMixTableDateChange(newDate, cell.Value)
						if err != nil {
							return
						}

						if dateValMap, ok3 := edbDayDataListMap[cell.EdbInfoId]; ok3 {
							tmpDateValMap = dateValMap
							if val, ok2 := tmpDateValMap[cell.DataTime]; ok2 {
								//cell.ShowValue = fmt.Sprint(val)
								cellKeyVal[cell.Uid] = val
								cell.ShowValue = utils.FormatMixTableDataShowValue(val)
							}
						}
					}
				}
			case request.CustomTextDT: //自定义文本
				if cell.Value == `` {
					continue
				}
				// 处理看下能否转成float，如果可以的话，说明这个也是可以参与计算的
				tmpDeci, tmpErr := decimal.NewFromString(cell.Value)
				if tmpErr == nil {
					tmpVal, _ := tmpDeci.Float64()
					cellKeyVal[cell.Uid] = tmpVal
				}

			case request.FormulateCalculateDataDT: // 公式计算（A+B这种）
				calculateChainList = append(calculateChainList, cell.Uid)

			case request.InsertEdbCalculateDataDT: // 插入指标系统计算公式生成的值
				// 处理value的值
				if !strings.Contains(cell.Value, "EdbInfoId") && cell.EdbInfoId > 0 {
					cell.Value, _ = fixCalculateValueConfig(cell.Value, cell.EdbInfoId)
					row[i] = cell
				}
				// 日期
				var cellDateTime string
				// 日期关系配置不存在，则默认最新数据
				// 从绑定的单元格中获取数据的日期
				if relationConf, ok := cellRelationConfMap[cell.Uid]; ok {
					if relationConf.RelationDate.Key == `` {
						// 日期关系配置未绑定
						continue
					}
					// 配置
					relationCell, ok := cellDataRelationMap[relationConf.RelationDate.Key]
					if !ok {
						// 找不到对应日期的单元格
						continue
					}
					cellDateTime = relationCell.DataTime
				}

				var tmpDataMap map[string]float64

				key := utils.MD5(cell.Value)
				respItemData, ok := edbSourceDataMap[key]
				if !ok {
					// 对应的配置值
					var tmpConfig request.CalculateConf
					err = json.Unmarshal([]byte(cell.Value), &tmpConfig)
					if err != nil {
						return
					}

					tmpDataList, ok := edbDataListMap[tmpConfig.EdbInfoId]
					if !ok {
						continue
					}
					edbInfo, ok := edbInfoMap[tmpConfig.EdbInfoId]
					if !ok {
						continue
					}

					req2 := &BaseCalculate{
						DataList:      tmpDataList,
						Frequency:     tmpConfig.Frequency,
						Formula:       tmpConfig.Formula,
						Calendar:      tmpConfig.Calendar,
						MoveType:      tmpConfig.MoveType,
						MoveFrequency: tmpConfig.MoveFrequency,
						FromFrequency: edbInfo.Frequency,
						Source:        tmpConfig.Source,
					}

					// 调用指标库去更新
					reqJson, tmpErr := json.Marshal(req2)
					if tmpErr != nil {
						utils.FileLog.Error(fmt.Sprintf("计算失败1，配置信息;%s;错误原因：%s", cell.Value, tmpErr.Error()))
						err = tmpErr
						return
					}
					respItem, tmpErr := data.BaseCalculate(string(reqJson), lang)
					if tmpErr != nil {
						utils.FileLog.Error(fmt.Sprintf("计算失败2，配置信息;%s;错误原因：%s", cell.Value, tmpErr.Error()))
						err = tmpErr
						return
					}
					if respItem.Ret != 200 {
						utils.FileLog.Error(fmt.Sprintf("计算失败3，配置信息;%s;原因：%s;错误原因：%s", cell.Value, respItem.Msg, respItem.ErrMsg))
						continue
					}

					//tmpDataMap = respItem.Data.DataMap
					// 计算结果存一份，万一存在重复的计算方式，那么省的重新计算一下
					edbSourceDataMap[key] = respItem.Data
					respItemData = respItem.Data
				}
				lenDataList := len(respItemData.DateList)
				tmpDataMap = respItemData.DataMap
				if cellDateTime == `` && lenDataList > 0 {
					//判断是否需要做期数前移动
					cellDateTime = respItemData.DateList[lenDataList-1]
					cellDateTime, err = GetEdbDateByMoveForwardByDateList(cell.Value, respItemData.DateList)
					if err != nil {
						utils.FileLog.Error(fmt.Sprintf("日期前移失败，配置信息;%s", cell.Value))
						continue
					}
				}
				// 进行日期变换
				cellDateTime, err = HandleMixTableDateChange(cellDateTime, cell.Value)
				if err != nil {
					utils.FileLog.Error(fmt.Sprintf("日期变换失败，配置信息;%s， 日期：%s", cell.Value, cellDateTime))
					continue
				}
				val, ok := tmpDataMap[cellDateTime]
				if ok {
					cellKeyVal[cell.Uid] = val
					cell.ShowValue = utils.FormatMixTableDataShowValue(val)
				} else {
					cell.ShowValue = ""
				}

			case request.DateCalculateDataDT: //日期计算
				dateCalculateList = append(dateCalculateList, cell.Uid)
				// 遍历数组，根据公式进行计算，并将得到的结果放到对应的单元格中
			}
			row[i] = cell
		}
		config[k] = row
	}

	// 处理指定指标的日期
	for k, row := range config {
		for i, cell := range row {
			calculateCellMap[cell.Uid] = Cell{
				Column:   k,
				Row:      i,
				CellInfo: cell,
			}
			cell.ShowFormatValue = cell.ShowValue
			if cell.ShowStyle != `` {
				showStyleList = append(showStyleList, cell.Uid)
			}
			row[i] = cell
			cellDataRelationMap[cell.Uid] = cell
		}
		config[k] = row
	}

	// 公式链计算
	if len(calculateChainList) > 0 {
		for _, cellKey := range calculateChainList {
			// 查找这个单元格的位置，直接map找了，而不是遍历整个单元格
			cellPosition, ok := calculateCellMap[cellKey]
			if !ok {
				utils.FileLog.Error("找不到单元格位置：", cellKey)
				continue
			}

			cell := config[cellPosition.Column][cellPosition.Row]
			if cell.DataType != request.FormulateCalculateDataDT { // 判断公式计算（A+B这种）类型，不是的话也过滤了
				continue
			}

			val, has, tmpErr, tmpErrMsg := getCalculateValueByCell(calculateCellMap, cellKey, cellKeyVal)
			if tmpErr != nil {
				errMsg = tmpErrMsg
				err = tmpErr
				return
			}
			if !has {
				continue
			}

			cellKeyVal[cell.Uid] = val
			cell.ShowValue = utils.FormatMixTableDataShowValue(val)
			config[cellPosition.Column][cellPosition.Row] = cell

		}
	}

	// 日期计算
	config, err, errMsg = handlerDateCalculate(dateCalculateList, calculateCellMap, config)
	if err != nil {
		return
	}

	// 格式化展示
	config, err, errMsg = handleMixCellShowStyle(showStyleList, calculateCellMap, config)
	if err != nil {
		return
	}

	newMixedTableCellDataList = config

	return
}

// getCalculateValue 获取公式计算的结果
func getCalculateValueByCell(calculateCellMap map[string]Cell, key string, cellKeyValMap map[string]float64) (val float64, has bool, err error, errMsg string) {
	// 单元格的标签名
	val, ok := cellKeyValMap[key]
	if ok {
		has = true
		return
	}

	// 查找单元格数据
	cell, ok := calculateCellMap[key]
	if !ok {
		err = errors.New("查找单元格" + key + "的数据失败")
		return
	}

	colData := cell.CellInfo

	// 如果不是基础计算单元格,直接返回
	if colData.DataType != request.FormulateCalculateDataDT {
		return
	}

	// 如果是计算单元格

	tagList := make([]utils.CellPosition, 0)
	// 计算单元格relationCellList
	var relationCellList []request.RelationCell
	if colData.Extra == `` {
		err = errors.New(colData.Uid + "没有绑定关系")
		return
	}
	err = json.Unmarshal([]byte(colData.Extra), &relationCellList)
	if err != nil {
		return
	}

	for _, relation := range relationCellList {
		//relationCellTagName := strings.ToUpper(relation.Tag) + relation.Row
		tmpVal, _, tmpErr, tmpErrMsg := getCalculateValueByCell(calculateCellMap, relation.Key, cellKeyValMap)
		if tmpErr != nil {
			errMsg = tmpErrMsg
			err = tmpErr
			return
		}
		cellKeyValMap[relation.Key] = tmpVal

		rowInt, tmpErr := strconv.Atoi(relation.Row)
		if tmpErr != nil {
			err = tmpErr
			return
		}

		tagList = append(tagList, utils.CellPosition{
			Tag:   relation.Tag,
			Row:   rowInt,
			Value: tmpVal,
		})
	}

	// 计算
	val, errMsg, err = calculateByCellList(strings.ToUpper(colData.Value), tagList)
	if err != nil {
		return
	}
	// 重新赋值
	has = true
	cellKeyValMap[key] = val

	return
}

// handleConfig
// @Description: 处理混合表格配置
// @author: Roc
// @datetime2023-10-27 13:24:53
// @param configList [][]request.MixedTableCellDataReq
// @return newConfig [][]request.MixedTableCellDataReq
// @return edbInfoIdList []int
// @return dataEdbInfoIdList []int
// @return err error
// @return errMsg string
func handleConfig(configList [][]request.MixedTableCellDataReq) (newConfig [][]request.MixedTableCellDataReq, edbInfoIdList []int, dataEdbInfoIdList []int, err error, errMsg string) {
	edbInfoIdList = make([]int, 0)
	dataEdbInfoIdList = make([]int, 0)

	for ck, rowList := range configList {
		for rk, cell := range rowList {
			switch cell.DataType {
			case request.EdbDT: // 指标信息
				edbInfoIdList = append(edbInfoIdList, cell.EdbInfoId)
			case request.InsertDataDT, request.PopInsertDataDT: // 插值、弹框插值
				dataEdbInfoIdList = append(dataEdbInfoIdList, cell.EdbInfoId)
				edbInfoIdList = append(edbInfoIdList, cell.EdbInfoId)
			case request.InsertEdbCalculateDataDT: // 插入指标计算公式生成的值
				var config request.CalculateConf
				err = json.Unmarshal([]byte(cell.Value), &config)
				if err != nil {
					return
				}
				if config.EdbInfoId == 0 && cell.EdbInfoId > 0 {
					edbInfoIdList = append(edbInfoIdList, cell.EdbInfoId)
				} else {
					edbInfoIdList = append(edbInfoIdList, config.EdbInfoId)
				}
				dataEdbInfoIdList = append(dataEdbInfoIdList, cell.EdbInfoId)

			case request.DateDT: // 日期类型
				date, newVal, tmpErr, tmpErrMsg := handleDate(cell.DataTimeType, cell.Value)
				if tmpErr != nil {
					err = tmpErr
					errMsg = tmpErrMsg
					return
				}
				rowList[rk].DataTime = date
				rowList[rk].ShowValue = date
				rowList[rk].Value = newVal //兼容原有的历史数据中系统导入日期

				// 指标日期类型的单元格需要额外将指标id取出来
				if cell.DataTimeType == request.EdbDateDT {
					var config request.EdbDateConf
					err = json.Unmarshal([]byte(cell.Value), &config)
					if err != nil {
						return
					}
					edbInfoIdList = append(edbInfoIdList, config.EdbInfoId)
				}
			}
		}
		configList[ck] = rowList
	}

	newConfig = configList

	return
}

// HandleDate
// @Description: 日期处理
// @author: Roc
// @datetime2023-10-27 09:37:02
// @param dataTimeType int
// @param val string
// @return date string
// @return err error
// @return errMsg string
func HandleDate(dataTimeType int, val string) (date string, newVal string, err error, errMsg string) {
	return handleDate(dataTimeType, val)
}

// handleDate
// @Description: 日期处理
// @author: Roc
// @datetime2023-10-27 09:36:49
// @param dataTimeType int
// @param val string
// @return date string
// @return err error
// @return errMsg string
func handleDate(dataTimeType int, val string) (date string, newVal string, err error, errMsg string) {
	newVal = val
	if val == `` {
		errMsg = "错误的日期数据"
		err = errors.New(errMsg)
		return
	}

	switch dataTimeType {
	case request.CustomDateT: //手动输入日期
		/*if !strings.Contains(val, "{") {
			newVal, err, errMsg = handleOldCustomerDateT(val)
			if err != nil {
				return
			}
		}
		date, err = HandleMixTableDateChange("", newVal)
		if err != nil {
			return
		}*/
		date = val
		return
	case request.SystemDateT: // 系统日期
		date = time.Now().Format(utils.FormatDate)
		newVal, err, errMsg = handleOldSystemDateT(val)
		if err != nil {
			return
		}
		date, err = HandleMixTableDateChange(date, newVal)
		if err != nil {
			return
		}

	case request.EdbDateDT: // 导入指标日期（指标库的最新日期）
	default:
		errMsg = "错误的日期类型"
		err = errors.New(errMsg)
		return
	}

	return
}

func GetEdbDateByMoveForward(conf string, edbDataList []*data_manage.EdbDataList) (date string, err error) {
	dateList := make([]string, 0)
	for _, v := range edbDataList {
		dateList = append(dateList, v.DataTime)
	}

	date, err = GetEdbDateByMoveForwardByDateList(conf, dateList)
	return
}

func GetEdbDateByMoveForwardByDateList(conf string, dateList []string) (date string, err error) {
	moveForward := 0
	if conf != "" {
		var edbDateConf request.EdbDateChangeConf
		err = json.Unmarshal([]byte(conf), &edbDateConf)
		if err != nil {
			err = fmt.Errorf("日期变换配置json解析失败失败: %s", err.Error())
			return
		}
		moveForward = edbDateConf.MoveForward
	}
	// 根据日期进行排序
	index := len(dateList) - 1 - moveForward
	for k, v := range dateList {
		if k == index {
			date = v
			return
		}
	}
	return
}

// HandleMixTableDateChange 处理表格中的日期变换
func HandleMixTableDateChange(date, conf string) (newDate string, err error) {
	newDate = date
	if conf == "" {
		return
	}
	var edbDateConf request.EdbDateConf
	err = json.Unmarshal([]byte(conf), &edbDateConf)
	if err != nil {
		err = fmt.Errorf("日期变换配置json解析失败失败: %s, Err:%s", conf, err.Error())
		return
	}

	if newDate != "" {
		if len(edbDateConf.DateChange) > 0 {
			var dateTime time.Time
			dateTime, err = time.ParseInLocation(utils.FormatDate, newDate, time.Local)
			if err != nil {
				err = fmt.Errorf("日期解析失败: %s", err.Error())
				return
			}
			for _, v := range edbDateConf.DateChange {
				if v.ChangeType == 1 {
					dateTime = dateTime.AddDate(v.Year, v.Month, v.Day)
					newDate = dateTime.Format(utils.FormatDate)
				} else if v.ChangeType == 2 {
					newDate, err, _ = HandleSystemAppointDateT(dateTime, v.FrequencyDay, v.Frequency)
					if err != nil {
						return
					}
					dateTime, err = time.ParseInLocation(utils.FormatDate, newDate, time.Local)
					if err != nil {
						err = fmt.Errorf("日期解析失败: %s", err.Error())
						return
					}
				}
			}
		}
	}

	return
}

// handleOldSystemDateT
// @Description: 历史数据中的导入系统日期
// @author: Roc
// @datetime2023-10-27 09:36:21
// @param confStr string
// @return date string
// @return err error
// @return errMsg string
func handleOldSystemDateT(confStr string) (newConf string, err error, errMsg string) {
	newConf = confStr
	var config request.SystemDateConf
	err = json.Unmarshal([]byte(confStr), &config)
	if err != nil {
		return
	}

	newConfig := new(request.EdbDateConf)
	dateChange := new(request.EdbDateConfDateChange)

	dateChangeList := make([]*request.EdbDateConfDateChange, 0)

	switch config.Source {
	case request.SystemCurrDateT:
		return
	case request.SystemCalculateDateT:
		// todo 是否直接更新该excel记录，
		dateChange.Day = config.CalculateNum
		dateChange.ChangeType = 1
		dateChangeList = append(dateChangeList, dateChange)
		newConfig.DateChange = dateChangeList
		newConfByte, e := json.Marshal(newConfig)
		if e != nil {
			err = fmt.Errorf("日期计算额外配置，json序列化失败: %s", e.Error())
			return
		}
		newConf = string(newConfByte)
		return
	case request.SystemFrequencyDateT: // 处理系统日期相关的指定频率（所在周/旬/月/季/半年/年的最后/最早一天）
		dateChange.FrequencyDay = config.Day
		dateChange.Frequency = config.Frequency
		dateChange.ChangeType = 1
		dateChangeList = append(dateChangeList, dateChange)
		newConfig.DateChange = dateChangeList
		newConfByte, e := json.Marshal(newConfig)
		if e != nil {
			err = fmt.Errorf("日期计算额外配置，json序列化失败: %s", e.Error())
			return
		}
		newConf = string(newConfByte)
		return
	default:
		//errMsg = "错误的日期日期导入方式"
		//err = errors.New(fmt.Sprint("错误的日期日期导入方式:", config.Source))
		return
	}

	return
}

// handleSystemCalculateDateT
// @Description: 处理系统日期计算后的日期
// @author: Roc
// @datetime2023-10-27 09:31:22
// @param num int
// @param frequency string
// @return date string
// @return err error
// @return errMsg string
func handleSystemCalculateDateT(num int, frequency string) (date string, err error, errMsg string) {
	if err != nil {
		return
	}
	currDate := time.Now()
	switch frequency {
	case "", "日":
		date = currDate.AddDate(0, 0, num).Format(utils.FormatDate)
	default:
		errMsg = "错误的日期频度：" + frequency
		err = errors.New(errMsg)
		return
	}

	return
}

// handleSystemAppointDateT
// @Description: 处理系统日期相关的指定频率（所在周/旬/月/季/半年/年的最后/最早一天）
// @author: Roc
// @datetime2023-10-27 09:31:35
// @param Frequency string
// @param Day string
// @return date string
// @return err error
// @return errMsg string
func HandleSystemAppointDateT(currDate time.Time, appointDay, frequency string) (date string, err error, errMsg string) {
	//currDate := time.Now()
	switch frequency {
	case "本周":
		day := int(currDate.Weekday())
		if day == 0 { // 周日
			day = 7
		}
		num := 0
		switch appointDay {
		case "周一":
			num = 1
		case "周二":
			num = 2
		case "周三":
			num = 3
		case "周四":
			num = 4
		case "周五":
			num = 5
		case "周六":
			num = 6
		case "周日":
			num = 7
		}
		day = num - day
		date = currDate.AddDate(0, 0, day).Format(utils.FormatDate)
	case "本旬":
		day := currDate.Day()
		var tmpDate time.Time
		switch appointDay {
		case "第一天":
			if day <= 10 {
				tmpDate = time.Date(currDate.Year(), currDate.Month(), 1, 0, 0, 0, 0, currDate.Location())
			} else if day <= 20 {
				tmpDate = time.Date(currDate.Year(), currDate.Month(), 11, 0, 0, 0, 0, currDate.Location())
			} else {
				tmpDate = time.Date(currDate.Year(), currDate.Month(), 21, 0, 0, 0, 0, currDate.Location())
			}
		case "最后一天":
			if day <= 10 {
				tmpDate = time.Date(currDate.Year(), currDate.Month(), 10, 0, 0, 0, 0, currDate.Location())
			} else if day <= 20 {
				tmpDate = time.Date(currDate.Year(), currDate.Month(), 20, 0, 0, 0, 0, currDate.Location())
			} else {
				tmpDate = time.Date(currDate.Year(), currDate.Month()+1, 1, 0, 0, 0, 0, currDate.Location()).AddDate(0, 0, -1)
			}
		}
		date = tmpDate.Format(utils.FormatDate)
	case "本月":
		var tmpDate time.Time
		switch appointDay {
		case "第一天":
			tmpDate = time.Date(currDate.Year(), currDate.Month(), 1, 0, 0, 0, 0, currDate.Location())
		case "最后一天":
			tmpDate = time.Date(currDate.Year(), currDate.Month()+1, 1, 0, 0, 0, 0, currDate.Location()).AddDate(0, 0, -1)
		}
		date = tmpDate.Format(utils.FormatDate)
	case "本季":
		month := currDate.Month()
		var tmpDate time.Time
		switch appointDay {
		case "第一天":
			if month <= 3 {
				tmpDate = time.Date(currDate.Year(), 1, 1, 0, 0, 0, 0, currDate.Location())
			} else if month <= 6 {
				tmpDate = time.Date(currDate.Year(), 4, 1, 0, 0, 0, 0, currDate.Location())
			} else if month <= 9 {
				tmpDate = time.Date(currDate.Year(), 7, 1, 0, 0, 0, 0, currDate.Location())
			} else {
				tmpDate = time.Date(currDate.Year(), 10, 1, 0, 0, 0, 0, currDate.Location())
			}
		case "最后一天":
			if month <= 3 {
				tmpDate = time.Date(currDate.Year(), 3, 31, 0, 0, 0, 0, currDate.Location())
			} else if month <= 6 {
				tmpDate = time.Date(currDate.Year(), 6, 30, 0, 0, 0, 0, currDate.Location())
			} else if month <= 9 {
				tmpDate = time.Date(currDate.Year(), 9, 30, 0, 0, 0, 0, currDate.Location())
			} else {
				tmpDate = time.Date(currDate.Year(), 12, 31, 0, 0, 0, 0, currDate.Location())
			}
		}
		date = tmpDate.Format(utils.FormatDate)
	case "本半年":
		month := currDate.Month()
		var tmpDate time.Time
		switch appointDay {
		case "第一天":
			if month <= 6 {
				tmpDate = time.Date(currDate.Year(), 1, 1, 0, 0, 0, 0, currDate.Location())
			} else {
				tmpDate = time.Date(currDate.Year(), 7, 1, 0, 0, 0, 0, currDate.Location())
			}
		case "最后一天":
			if month <= 6 {
				tmpDate = time.Date(currDate.Year(), 6, 30, 0, 0, 0, 0, currDate.Location())
			} else {
				tmpDate = time.Date(currDate.Year(), 12, 31, 0, 0, 0, 0, currDate.Location())
			}
		}
		date = tmpDate.Format(utils.FormatDate)
	case "本年":
		var tmpDate time.Time
		switch appointDay {
		case "第一天":
			tmpDate = time.Date(currDate.Year(), 1, 1, 0, 0, 0, 0, currDate.Location())
		case "最后一天":
			tmpDate = time.Date(currDate.Year(), 12, 31, 0, 0, 0, 0, currDate.Location())
		}
		date = tmpDate.Format(utils.FormatDate)
	default:
		errMsg = "错误的日期频度：" + frequency
		err = errors.New(errMsg)
		return
	}

	return
}

// calculateByCellList
// @Description: 根据单元格来进行公式计算
// @author: Roc
// @datetime2023-11-14 16:17:38
// @param calculateFormula string
// @param tagList []utils.CellPosition
// @return calVal string
// @return errMsg string
// @return err error
func calculateByCellList(calculateFormula string, tagList []utils.CellPosition) (calVal float64, errMsg string, err error) {
	if calculateFormula == "" {
		errMsg = "公式异常"
		err = errors.New(errMsg)
		return
	}

	calculateFormula = strings.TrimPrefix(calculateFormula, "=")
	calculateFormula = strings.Replace(calculateFormula, "（", "(", -1)
	calculateFormula = strings.Replace(calculateFormula, "）", ")", -1)
	calculateFormula = strings.Replace(calculateFormula, "，", ",", -1)
	calculateFormula = strings.Replace(calculateFormula, "。", ".", -1)
	calculateFormula = strings.Replace(calculateFormula, "%", "*0.01", -1)

	rowList := make([]int, 0)
	rowListMap := make(map[int][]utils.CellPosition)
	for _, v := range tagList {
		tmpRowList, ok := rowListMap[v.Row]
		if !ok {
			rowList = append(rowList, v.Row)
			tmpRowList = make([]utils.CellPosition, 0)
		}
		tmpRowList = append(tmpRowList, v)
		rowListMap[v.Row] = tmpRowList
	}

	sort.Ints(rowList)

	list := make([]utils.CellPosition, 0)
	for _, row := range rowList {
		list = append(list, rowListMap[row]...)
	}

	formulaFormStr := utils.ReplaceFormulaByCellList(list, calculateFormula)
	//计算公式异常，那么就移除该指标
	if formulaFormStr == `` {
		errMsg = "公式异常"
		err = errors.New(errMsg)
		return
	}

	expression := formula.NewExpression(formulaFormStr)
	calResult, err := expression.Evaluate()
	if err != nil {
		errMsg = "计算失败"
		err = errors.New("计算失败：Err:" + err.Error() + ";formulaStr:" + formulaFormStr)
		// 分母为0的报错
		if strings.Contains(err.Error(), "divide by zero") {
			errMsg = "分母不能为0"
			err = errors.New("分母不能为空，计算公式：" + formulaFormStr)
		}
		return
	}
	// 如果计算结果是NAN，那么就提示报错
	if calResult.IsNan() {
		errMsg = "计算失败"
		err = errors.New("计算失败：计算结果是:NAN;formulaStr:" + formulaFormStr)
		return
	}
	calVal, err = calResult.Float64()
	if err != nil {
		return
	}

	// 转Decimal然后四舍五入
	calVal, _ = decimal.NewFromFloat(calVal).Round(4).Float64()

	return
}

// handlerDateCalculate 处理日期计算
func handlerDateCalculate(dateCalculateList []string, calculateCellMap map[string]Cell, oldConfig [][]request.MixedTableCellDataReq) (config [][]request.MixedTableCellDataReq, err error, errMsg string) {
	config = oldConfig
	if len(dateCalculateList) == 0 {
		return
	}
	if len(dateCalculateList) > 0 {
		for _, cellKey := range dateCalculateList {
			// 查找这个单元格的位置，直接map找了，而不是遍历整个单元格
			cellPosition, ok := calculateCellMap[cellKey]
			if !ok {
				utils.FileLog.Error("找不到单元格位置：", cellKey)
				continue
			}

			cell := config[cellPosition.Column][cellPosition.Row]
			if cell.DataType != request.DateCalculateDataDT { // 判断公式计算（A+B这种）类型，不是的话也过滤了
				continue
			}

			val, tmpErr, tmpErrMsg := DateCalculatePrepare(calculateCellMap, cell.Value)
			if tmpErr != nil {
				cell.ShowValue = ""
				config[cellPosition.Column][cellPosition.Row] = cell
				utils.FileLog.Error(fmt.Sprintf("%s 日期计算报错：Err：%s:%s", cellKey, tmpErr, tmpErrMsg))
				continue
			}

			cell.ShowValue = utils.FormatMixTableDataShowValue(val)
			config[cellPosition.Column][cellPosition.Row] = cell
		}
	}
	return
}

// DateCalculatePrepare 单个单元格的日期计算
func DateCalculatePrepare(calculateCellMap map[string]Cell, config string) (val float64, err error, errMsg string) {
	var edbDateConf request.MixDateCalculateConf
	err = json.Unmarshal([]byte(config), &edbDateConf)
	if err != nil {
		err = fmt.Errorf("日期计算配置json解析失败失败: %s, Err:%s", config, err.Error())
		return
	}
	if len(edbDateConf.RelationCellList) == 0 {
		err = fmt.Errorf("日期计算 未配置日期单元格失败: %s", config)
		return
	}
	valMap := make(map[string]int)
	for _, v := range edbDateConf.RelationCellList {
		// 查找单元格数据
		cell, ok := calculateCellMap[v.Uid]
		if !ok {
			err = fmt.Errorf("查找单元格:%s 的数据失败", v.Uid)
			return
		}
		colData := cell.CellInfo

		// 如果不是基础计算单元格,直接返回
		_, err = time.ParseInLocation(utils.FormatDate, colData.ShowValue, time.Local)
		if err != nil {
			err = fmt.Errorf("%s 的单元格非日期类型, Err: %s", colData.ShowValue, err.Error())
			return
		}
		// todo 把日期转换成excel里的天数
		realDiffDay := utils.GetDaysDiff1900(colData.ShowValue)

		valMap[strings.ToUpper(v.Tag)] = realDiffDay
	}

	// 计算
	val, errMsg, err = DateCalculateFormula(valMap, strings.ToUpper(edbDateConf.Formula))
	if err != nil {
		return
	}
	return
}

func DateCalculateFormula(valTagMap map[string]int, calculateFormula string) (calVal float64, errMsg string, err error) {
	if calculateFormula == "" {
		errMsg = "公式异常"
		err = errors.New(errMsg)
		return
	}

	calculateFormula = strings.TrimPrefix(calculateFormula, "=")
	calculateFormula = strings.Replace(calculateFormula, "（", "(", -1)
	calculateFormula = strings.Replace(calculateFormula, "）", ")", -1)
	calculateFormula = strings.Replace(calculateFormula, "，", ",", -1)
	calculateFormula = strings.Replace(calculateFormula, "。", ".", -1)
	calculateFormula = strings.Replace(calculateFormula, "%", "*0.01", -1)

	formulaFormStr := utils.ReplaceFormulaByTagMap(valTagMap, calculateFormula)

	if formulaFormStr == `` {
		errMsg = "公式异常"
		err = errors.New(errMsg)
		return
	}
	fmt.Println("公式：" + formulaFormStr)
	expression := formula.NewExpression(formulaFormStr)
	calResult, err := expression.Evaluate()
	if err != nil {
		errMsg = "公式错误,请重新填写"
		err = errors.New("计算失败：Err:" + err.Error() + ";formulaStr:" + formulaFormStr)
		// 分母为0的报错
		if strings.Contains(err.Error(), "divide by zero") {
			errMsg = "分母不能为0"
			err = errors.New("分母不能为空，计算公式：" + formulaFormStr)
		}
		return
	}
	// 如果计算结果是NAN，那么就提示报错
	if calResult.IsNan() {
		errMsg = "公式错误,请重新填写"
		err = errors.New("计算失败：计算结果是:NAN;formulaStr:" + formulaFormStr)
		return
	}
	calVal, err = calResult.Float64()
	if err != nil {
		return
	}

	// 转Decimal然后四舍五入
	calVal, _ = decimal.NewFromFloat(calVal).Round(4).Float64()

	return
}

// handleMixCellShowStyle 处理混合表格中，显示计算的逻辑
func handleMixCellShowStyle(showStyleList []string, calculateCellMap map[string]Cell, oldConfig [][]request.MixedTableCellDataReq) (config [][]request.MixedTableCellDataReq, err error, errMsg string) {
	config = oldConfig
	if len(showStyleList) == 0 {
		return
	}
	if len(showStyleList) > 0 {
		for _, cellKey := range showStyleList {
			// 查找这个单元格的位置，直接map找了，而不是遍历整个单元格
			cellPosition, ok := calculateCellMap[cellKey]
			if !ok {
				utils.FileLog.Error("找不到单元格位置：", cellKey)
				continue
			}

			cell := config[cellPosition.Column][cellPosition.Row]
			val := cell.ShowValue
			isPercent := false
			if strings.Contains(val, "%") {
				isPercent = true
				val = strings.Trim(val, "%")
			}
			_, e := strconv.ParseFloat(val, 64) // 将字符串转换成float类型
			if e != nil {                       // 如果没有错误发生则返回true，说明该字符串是一个合法的数字
				continue
			}
			var styleConf request.MixCellShowStyle
			err = json.Unmarshal([]byte(cell.ShowStyle), &styleConf)
			if err != nil {
				err = fmt.Errorf("日期计算配置json解析失败失败: %s, Err:%s", config, err.Error())
				return
			}
			if styleConf.Pn != 0 || styleConf.Nt != "" {
				val = changePointDecimalPlaces(val, styleConf.Pn, styleConf.Nt, isPercent)
				cell.ShowFormatValue = val
			} else {
				cell.ShowFormatValue = cell.ShowValue
			}
			config[cellPosition.Column][cellPosition.Row] = cell
		}
	}
	return
}

// changePointDecimalPlaces 小数点位数加减和百分比格式
func changePointDecimalPlaces(str string, changeNum int, numberType string, isPercent bool) (newStr string) {
	newStr = str
	var decimalPlaces int
	dotIndex := strings.Index(newStr, ".") // 查找小数点的位置
	if dotIndex == -1 {
		decimalPlaces = 0
	} else {
		decimalPlaces = len(newStr) - dotIndex - 1
	}
	// 把字符串转成浮点数
	val, _ := strconv.ParseFloat(str, 64)
	if isPercent {
		if numberType == "number" { //百分数转成小数
			val = val / 100
			if decimalPlaces > 2 {
				decimalPlaces += 2
			} else if decimalPlaces == 1 {
				decimalPlaces += 1
			} else if decimalPlaces == 0 {
				if len(str) == 1 {
					decimalPlaces = 2
				} else if len(str) == 2 {
					if str[1] == '0' {
						decimalPlaces = 1
					} else {
						decimalPlaces = 2
					}
				}
			}
			isPercent = false
		}
	} else {
		if numberType == "percent" {
			if decimalPlaces > 2 {
				decimalPlaces -= 2
			} else if decimalPlaces == 1 {
				decimalPlaces = 0
			}
			val = val * 100
		}
	}
	if decimalPlaces > 0 {
		val, _ = decimal.NewFromFloat(val).Round(int32(decimalPlaces)).Float64()
		newStr = strconv.FormatFloat(val, 'f', decimalPlaces, 64)
	} else {
		// 此处用%.f避免科学计数法, 从而导致后面出现很多位0
		newStr = fmt.Sprintf("%.f", val)
	}
	// 计算小数位数
	decimalPlaces = 0
	dotIndex = strings.Index(newStr, ".") // 查找小数点的位置
	if dotIndex == -1 {
		decimalPlaces = 0
	} else {
		decimalPlaces = len(newStr) - dotIndex - 1
	}
	decimalPlaces += changeNum

	if decimalPlaces < 0 {
		decimalPlaces = 0
	}
	val, _ = decimal.NewFromFloat(val).Round(int32(decimalPlaces)).Float64()
	newStr = strconv.FormatFloat(val, 'f', decimalPlaces, 64)
	if numberType == "percent" || isPercent {
		newStr += "%"
	}
	return
}

func fixCalculateValueConfig(conf string, edbInfoId int) (newConf string, err error) {
	newConf = conf
	if edbInfoId == 0 {
		return
	}
	var tmpConfig request.CalculateConf
	err = json.Unmarshal([]byte(conf), &tmpConfig)
	if err != nil {
		return
	}
	if tmpConfig.EdbInfoId == 0 {
		tmpConfig.EdbInfoId = edbInfoId
		newConfByte, _ := json.Marshal(tmpConfig)
		newConf = string(newConfByte)
		return
	}
	return
}