eta_api/services/data/excel/excel_info.go

package excel

import (
	"encoding/json"
	"errors"
	"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/utils"
	"fmt"
	"github.com/shopspring/decimal"
	"github.com/yidane/formula"
	"sort"
	"strconv"
	"strings"
	"time"
)

// GetExcelDetailInfoByExcelInfoId 根据表格id获取表格详情
func GetExcelDetailInfoByExcelInfoId(excelInfoId int) (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)
}

// GetExcelDetailInfoByUnicode 根据表格编码获取表格详情
func GetExcelDetailInfoByUnicode(unicode 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)
}

func formatExcelInfo2Detail(excelInfo *excel.ExcelInfo) (excelDetail response.ExcelInfoDetail, errMsg string, err error) {
	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,
	}

	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
		}
		excelDetail.TableData = result
	case utils.MIXED_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)
		if tmpErr != nil {
			errMsg = "获取失败"
			if tmpErrMsg != `` {
				errMsg = tmpErrMsg
			}
			err = errors.New("获取最新的数据失败，Err:" + tmpErr.Error())
			return
		}
		result.Data = newData
		excelDetail.TableData = result
	}
	return
}

// GetExcelInfoOpButton 获取ETA表格的操作权限
func GetExcelInfoOpButton(sysUser *system.Admin, belongUserId, source int) (button response.ExcelInfoDetailButton) {
	//非管理员角色查看其他用户创建的表格，可刷新、另存为、下载表格；
	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
}

// 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
}

type TableDataConfig struct {
	EdbInfoIdList    []int                     `description:"指标id列表，从左至右，从上到下的顺序"`
	Sort             int                       `description:"日期排序，0：倒序，1：正序"`
	Data             []ManualData              `description:"数据列表"`
	Num              int                       `description:"实际数据需要列出来的期数"`
	RemoveDate       []string                  `description:"不展示的日期"`
	ManualDate       []string                  `description:"手动配置的日期（未来的日期）"`
	TableEdbInfoList []TableEdbInfo            `description:"表格内指标信息"`
	TextRowData      [][]request.ManualDataReq `description:"文本列表"`
}

type TableEdbInfo struct {
	EdbInfoId    int    `description:"指标ID"`
	Tag          string `description:"标签"`
	EdbName      string `description:"指标名称"`
	EdbAliasName string `description:"指标别名"`
	Frequency    string `description:"频度"`
	Unit         string `description:"单位"`
}

type ManualData struct {
	DataType            int                       `description:"数据类型，1：普通的，2：插值法，3：手动输入，4：公式计算"`
	DataTime            string                    `description:"所属日期"`
	DataTimeType        int                       `description:"日期类型，1：实际日期；2：未来日期"`
	ShowValue           string                    `description:"展示值"`
	Value               string                    `description:"实际值（计算公式）"`
	EdbInfoId           int                       `description:"指标id"`
	Tag                 string                    `description:"下标"`
	RelationEdbInfoList []request.RelationEdbInfo `description:"关联指标（计算公式中关联的指标，用于计算的时候去匹配）"`
}

// GetTableDataConfig 根据TableDataReq配置获取相关数据配置
func GetTableDataConfig(reqData request.TableDataReq) (tableDataConfig TableDataConfig, err error) {
	// 指标数据
	tableDataConfig.EdbInfoIdList = reqData.EdbInfoIdList
	tableDataConfig.Sort = reqData.Sort

	if len(reqData.Data) <= 0 {
		err = errors.New("数据不能为空")
		return
	}

	// 开始日期
	var startDate string
	// A列的指标id
	var firstEdbInfoId int
	// 手工操作的数据列
	manualDataList := make([]ManualData, 0)
	// 指标配置列表
	tableEdbInfoList := make([]TableEdbInfo, 0)

	// 第一列的日期map
	firstDateMap := make(map[string]string)
	manualDateMap := make(map[string]string)

	for _, v := range reqData.Data {
		// 指标信息
		tmpTableEdbInfo := TableEdbInfo{
			EdbInfoId:    v.EdbInfoId,
			Tag:          v.Tag,
			EdbName:      v.EdbName,
			EdbAliasName: v.EdbAliasName,
			Frequency:    v.Frequency,
			Unit:         v.Unit,
		}
		tableEdbInfoList = append(tableEdbInfoList, tmpTableEdbInfo)

		// 确定数据A列
		if v.Tag == "A" {
			firstEdbInfoId = v.EdbInfoId
			lenData := len(v.Data)
			if lenData <= 0 {
				err = errors.New("A列不能为空")
				return
			}
			index := 0
			if reqData.Sort == 1 {
				// 倒序
				index = lenData - 1
			}

			startDate = v.Data[index].DataTime

			// 存在的日期列表
			for _, data := range v.Data {
				firstDateMap[data.DataTime] = data.DataTime

				if data.DataTimeType == 2 {
					manualDateMap[data.DataTime] = data.DataTime
				}
			}
		}

		for _, data := range v.Data {
			if data.DataType == 3 || data.DataType == 4 {
				tmpManualData := ManualData{
					DataType:            data.DataType,
					DataTime:            data.DataTime,
					DataTimeType:        data.DataTimeType,
					ShowValue:           data.ShowValue,
					Value:               data.Value,
					EdbInfoId:           v.EdbInfoId,
					Tag:                 v.Tag,
					RelationEdbInfoList: data.RelationEdbInfoList,
				}
				if data.DataType == 4 {
					tmpManualData.ShowValue = ``
				}
				manualDataList = append(manualDataList, tmpManualData)
			}
		}
	}

	// 总共需要的期数
	num := len(reqData.Data[0].Data)

	removeDate := make([]string, 0)

	// 获取期数
	{
		firstDateTime, tmpErr := time.ParseInLocation(utils.FormatDate, startDate, time.Local)
		if tmpErr != nil {
			err = tmpErr
			return
		}

		edbInfo, tmpErr := data_manage.GetEdbInfoById(firstEdbInfoId)
		if tmpErr != nil {
			err = tmpErr
			return
		}

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

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

		// 筛选出需要删除的日期
		for _, tmpData := range baseDataList {
			//firstDateMap{}
			if _, ok := firstDateMap[tmpData.DataTime]; !ok {
				removeDate = append(removeDate, tmpData.DataTime)
			}
		}
	}

	tableDataConfig.Num = num
	tableDataConfig.RemoveDate = removeDate
	tableDataConfig.Data = manualDataList
	tableDataConfig.TableEdbInfoList = tableEdbInfoList
	tableDataConfig.TextRowData = reqData.TextRowData

	return
}

// GetDataByTableDataConfig 根据数据配置获取表格数据
func GetDataByTableDataConfig(tableDataConfig TableDataConfig) (resultResp request.TableDataReq, err error) {
	// 没有选择指标的情况下，直接返回吧
	if len(tableDataConfig.EdbInfoIdList) <= 0 {
		return
	}

	// 实际期数没有的情况下，直接返回吧
	if tableDataConfig.Num <= 0 {
		return
	}

	// 获取所有的指标信息
	edbInfoMap := make(map[int]*data_manage.EdbInfo)
	edbInfoIdList := make([]int, 0)
	// 标签与指标id的map
	tagEdbInfoIdMap := make(map[string]int)
	{
		for _, tableEdbInfo := range tableDataConfig.TableEdbInfoList {
			edbInfoIdList = append(edbInfoIdList, tableEdbInfo.EdbInfoId)
			tagEdbInfoIdMap[tableEdbInfo.Tag] = tableEdbInfo.EdbInfoId
		}
		edbInfoList, tmpErr := data_manage.GetEdbInfoByIdList(edbInfoIdList)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		for _, v := range edbInfoList {
			edbInfoMap[v.EdbInfoId] = v
		}
	}

	manualDateMap := make(map[string]string, 0)
	manualDateList := make([]string, 0)
	for _, v := range tableDataConfig.Data {
		if _, ok := manualDateMap[v.DataTime]; !ok {
			manualDateMap[v.DataTime] = v.DataTime
			manualDateList = append(manualDateList, v.DataTime)
		}
	}

	// 寻找A列的数据列表
	firstEdbInfo, ok := edbInfoMap[tableDataConfig.TableEdbInfoList[0].EdbInfoId]
	if !ok {
		err = errors.New("找不到A列指标")
		return
	}
	baseFirstEdbInfoDataList, err := GetFirstEdbDataList(firstEdbInfo, tableDataConfig.Num, manualDateList)
	if err != nil {
		return
	}
	// A列找不到数据，那么就直接返回吧
	if len(baseFirstEdbInfoDataList) <= 0 {
		return
	}

	firstEdbInfoDataList := make([]request.ManualDataReq, 0)
	if tableDataConfig.RemoveDate != nil && len(tableDataConfig.RemoveDate) > 0 {
		for _, v := range baseFirstEdbInfoDataList {
			if utils.InArrayByStr(tableDataConfig.RemoveDate, v.DataTime) {
				continue
			}
			firstEdbInfoDataList = append(firstEdbInfoDataList, v)
		}
	} else {
		firstEdbInfoDataList = baseFirstEdbInfoDataList
	}
	if len(firstEdbInfoDataList) <= 0 {
		return
	}
	// 实际数据的最后一天
	lastRealDateTime, err := time.ParseInLocation(utils.FormatDate, firstEdbInfoDataList[0].DataTime, time.Local)
	if err != nil {
		return
	}

	dateMap := make(map[string]string)
	dateList := make([]string, 0)
	edbInfoIdDateDataMap := make(map[int]map[string]request.ManualDataReq)
	firstDateDataMap := make(map[string]request.ManualDataReq)
	for _, v := range firstEdbInfoDataList {
		dateList = append(dateList, v.DataTime)
		dateMap[v.DataTime] = v.DataTime
		firstDateDataMap[v.DataTime] = v
	}

	// 将手工数据的日期填补进去（未来的日期，过去的就不管了）
	for _, manualData := range tableDataConfig.Data {
		if !utils.InArrayByStr(dateList, manualData.DataTime) {
			tmpDateTime, tmpErr := time.ParseInLocation(utils.FormatDate, manualData.DataTime, time.Local)
			if tmpErr != nil {
				err = tmpErr
				return
			}
			if tmpDateTime.After(lastRealDateTime) {
				dateList = append(dateList, manualData.DataTime)
			}
		}
	}

	edbInfoIdDateDataMap[firstEdbInfo.EdbInfoId] = firstDateDataMap

	for k, edbInfoId := range tableDataConfig.EdbInfoIdList {
		if k == 0 {
			continue
		}

		tmpEdbInfo, ok := edbInfoMap[edbInfoId]
		if !ok {
			err = errors.New("找不到A列指标")
			return
		}
		otherDataList, tmpErr := GetOtherEdbDataList(tmpEdbInfo, dateList)
		if tmpErr != nil {
			err = tmpErr
			return
		}

		tmpDateDataMap := make(map[string]request.ManualDataReq)
		for _, v := range otherDataList {
			tmpDateDataMap[v.DataTime] = v
		}
		edbInfoIdDateDataMap[tmpEdbInfo.EdbInfoId] = tmpDateDataMap
	}

	for _, v := range tableDataConfig.Data {
		tmpDate := v.DataTime
		if _, ok := dateMap[tmpDate]; !ok {
			dateMap[v.DataTime] = tmpDate
		}

		edbInfoIdDateData, ok := edbInfoIdDateDataMap[v.EdbInfoId]
		if !ok {
			edbInfoIdDateData = make(map[string]request.ManualDataReq)
		}

		// 判断是否存在该日期的数据（不存在，那么插入数据吧，存在就不管了）
		tmpManualData, ok := edbInfoIdDateData[tmpDate]
		if !ok {
			edbInfoIdDateData[tmpDate] = request.ManualDataReq{
				DataType:  v.DataType,
				DataTime:  v.DataTime,
				ShowValue: v.ShowValue,
				Value:     v.Value,
			}
		} else {
			if (tmpManualData.DataType == 3 || tmpManualData.DataType == 4) && tmpManualData.ShowValue == `` {
				tmpManualData.DataType = v.DataType
				tmpManualData.ShowValue = v.ShowValue
				tmpManualData.Value = v.Value
				tmpManualData.RelationEdbInfoList = v.RelationEdbInfoList

				edbInfoIdDateData[tmpDate] = tmpManualData
			}
		}
		edbInfoIdDateDataMap[v.EdbInfoId] = edbInfoIdDateData
	}

	// 获取数据的日期排序
	sortDateTimeList := make([]time.Time, 0)
	{
		sortDateList := dateList
		if tableDataConfig.Sort == 1 {
			baseDateList := utils.StrArr{}
			baseDateList = append(baseDateList, sortDateList...)
			sort.Sort(baseDateList)
			sortDateList = append([]string{}, baseDateList...)
		} else {
			sort.Strings(sortDateList)
		}
		for _, v := range sortDateList {
			tmpDateTime, tmpErr := time.ParseInLocation(utils.FormatDate, v, time.Local)
			if tmpErr != nil {
				err = tmpErr
				return
			}
			sortDateTimeList = append(sortDateTimeList, tmpDateTime)
		}
	}

	// 数据处理，处理成表格的数据格式
	tableDataMap, textRowListDataResp := handleTable(tagEdbInfoIdMap, lastRealDateTime, sortDateTimeList, edbInfoIdDateDataMap, tableDataConfig.Data, tableDataConfig.TextRowData)

	data := make([]request.EdbInfoData, 0)
	for _, tableEdbInfo := range tableDataConfig.TableEdbInfoList {
		tagEdbInfoIdMap[tableEdbInfo.Tag] = tableEdbInfo.EdbInfoId

		manualDataReqList := make([]request.ManualDataReq, 0)
		tmpEdbInfoData := request.EdbInfoData{
			EdbInfoId:    tableEdbInfo.EdbInfoId,
			Tag:          tableEdbInfo.Tag,
			EdbName:      tableEdbInfo.EdbName,
			EdbAliasName: tableEdbInfo.EdbAliasName,
			Frequency:    tableEdbInfo.Frequency,
			Unit:         tableEdbInfo.Unit,
			Data:         manualDataReqList,
		}

		edbInfo, ok := edbInfoMap[tableEdbInfo.EdbInfoId]
		if ok {
			tmpEdbInfoData.EdbName = edbInfo.EdbName
			tmpEdbInfoData.Frequency = edbInfo.Frequency
			tmpEdbInfoData.Unit = edbInfo.Unit
		}

		for index, dateTime := range sortDateTimeList {
			dataTimeType := 1
			if dateTime.After(lastRealDateTime) {
				dataTimeType = 2
			}
			tmpDateTimeStr := dateTime.Format(utils.FormatDate)

			rowData, ok := tableDataMap[index+1]
			if !ok {
				manualDataReqList = append(manualDataReqList, request.ManualDataReq{
					DataType:            3,
					DataTime:            tmpDateTimeStr,
					DataTimeType:        dataTimeType,
					ShowValue:           "",
					Value:               "",
					RelationEdbInfoList: nil,
				})
				continue
			}

			tmpData, ok := rowData[tableEdbInfo.Tag]
			if !ok {
				manualDataReqList = append(manualDataReqList, request.ManualDataReq{
					DataType:            3,
					DataTime:            tmpDateTimeStr,
					DataTimeType:        dataTimeType,
					ShowValue:           "",
					Value:               "",
					RelationEdbInfoList: nil,
				})
				continue
			}

			tmpData.DataTimeType = dataTimeType
			manualDataReqList = append(manualDataReqList, tmpData)
		}

		tmpEdbInfoData.Data = manualDataReqList

		data = append(data, tmpEdbInfoData)
	}

	// 处理一下数据格式
	for _, d := range data {
		for k2, d2 := range d.Data {
			// 可能有ShowValue非数值, 转换一下报错则continue
			vf, e := strconv.ParseFloat(d2.ShowValue, 64)
			if e != nil {
				continue
			}
			d.Data[k2].ShowValue = utils.FormatTableDataShowValue(vf)
		}
	}
	for _, d := range textRowListDataResp {
		for k2, d2 := range d {
			// 可能有ShowValue非数值, 转换一下报错则continue
			vf, e := strconv.ParseFloat(d2.ShowValue, 64)
			if e != nil {
				continue
			}
			d[k2].ShowValue = utils.FormatTableDataShowValue(vf)
		}
	}

	resultResp = request.TableDataReq{
		EdbInfoIdList: edbInfoIdList,
		Sort:          tableDataConfig.Sort,
		TextRowData:   textRowListDataResp,
		Data:          data,
	}

	return
}

// handleTable 表格数据处理
func handleTable(tagEdbInfoIdMap map[string]int, lastRealDateTime time.Time, sortDateTimeList []time.Time, edbInfoIdDateDataMap map[int]map[string]request.ManualDataReq, manualDataList []ManualData, textRowData [][]request.ManualDataReq) (tableDataMap map[int]map[string]request.ManualDataReq, textRowListDataResp [][]request.ManualDataReq) {
	tagList := make([]string, 0)
	for tag, _ := range tagEdbInfoIdMap {
		tagList = append(tagList, tag)
	}
	sort.Strings(tagList)

	tableDataMap = make(map[int]map[string]request.ManualDataReq) //行、列数据

	// 日期与行的关系
	dateIndexMap := make(map[string]int)

	for k, dateTime := range sortDateTimeList {
		rowDataMap := make(map[string]request.ManualDataReq)

		dataTimeType := 1
		if dateTime.After(lastRealDateTime) {
			dataTimeType = 2
		}

		tmpDateTimeStr := dateTime.Format(utils.FormatDate)
		dateIndexMap[tmpDateTimeStr] = k + 1

		for _, tag := range tagList {
			edbInfoId, ok := tagEdbInfoIdMap[tag]
			if !ok { // 没有找到该指标的映射关系，那么就用空串填补
				rowDataMap[tag] = request.ManualDataReq{
					DataType:            3,
					DataTime:            tmpDateTimeStr,
					DataTimeType:        dataTimeType,
					ShowValue:           "",
					Value:               "",
					RelationEdbInfoList: nil,
				}
				continue
			}

			// 获取指标的数据map
			dateDataMap, ok := edbInfoIdDateDataMap[edbInfoId]
			if !ok { // 没有找到该指标的数据，那么就用空串填补
				rowDataMap[tag] = request.ManualDataReq{
					DataType:            3,
					DataTime:            tmpDateTimeStr,
					DataTimeType:        dataTimeType,
					ShowValue:           "",
					Value:               "",
					RelationEdbInfoList: nil,
				}
				continue
			}

			// 获取指标该日期的数据
			tmpData, ok := dateDataMap[tmpDateTimeStr]
			if !ok { // 该指标没有找到对应日期的数据，那么就用空串填补
				rowDataMap[tag] = request.ManualDataReq{
					DataType:            3,
					DataTime:            tmpDateTimeStr,
					DataTimeType:        dataTimeType,
					ShowValue:           "",
					Value:               "",
					RelationEdbInfoList: nil,
				}
				continue
			}
			tmpData.DataTimeType = dataTimeType
			rowDataMap[tag] = tmpData
		}
		tableDataMap[k+1] = rowDataMap
	}

	// 替换手工设置的数据
	for _, manualData := range manualDataList {
		// 找不到该日期，说明这日期过期了，不处理
		index, ok := dateIndexMap[manualData.DataTime]
		if !ok {
			continue
		}

		// 获取对应行的数据
		rowDataMap, ok := tableDataMap[index]
		if !ok {
			continue
		}

		// 找到对应的单元格
		tmpData, ok := rowDataMap[manualData.Tag]
		if !ok {
			continue
		}

		// 如果该单元格实际有数据（包含预测值），或者插值法补充了数据的话，那么就不用手动填入的数据
		if utils.InArrayByInt([]int{1, 2, 5}, tmpData.DataType) {
			continue
		}

		// 手工填写的数字
		if tmpData.DataType == 3 {
			tmpData.ShowValue = manualData.ShowValue
			tmpData.Value = manualData.Value
			tableDataMap[index][manualData.Tag] = tmpData

			//edbInfoIdDateDataMap[manualData.EdbInfoId][manualData.DataTime] = tmpData
			continue
		}

		// 公式
		tmpData.DataType = manualData.DataType
		tmpData.ShowValue = ``
		tmpData.Value = manualData.Value
		tmpData.RelationEdbInfoList = manualData.RelationEdbInfoList
		tableDataMap[index][manualData.Tag] = tmpData

	}

	// 文本行的列表插入
	lenTableData := len(tableDataMap)

	// 文本行第一列的数据列表（可能多行）
	firstColTextRowList := make([]request.ManualDataReq, 0)
	// 参与计算的文本行列表数据
	tmpTextRowList := make([][]request.ManualDataReq, 0)
	for k, textRowList := range textRowData {
		// 判断列数是否匹配，不匹配的话那么过滤
		if len(tagList)+1 != len(textRowList) {
			continue
		}
		rowDataMap := make(map[string]request.ManualDataReq)
		tmpTextRow := make([]request.ManualDataReq, 0)
		for index, textRow := range textRowList {
			// 移除第一列，因为第一列是日期列
			if index == 0 {
				firstColTextRowList = append(firstColTextRowList, textRow)
				continue
			}
			rowDataMap[tagList[index-1]] = textRow
			tmpTextRow = append(tmpTextRow, textRow)
		}

		tableDataMap[lenTableData+k+1] = rowDataMap
		tmpTextRowList = append(tmpTextRowList, tmpTextRow)
	}

	// 参与计算的单元格
	calculateCellMap := make(map[string]string)

	// 计算手工填写的单元格
	for _, manualData := range manualDataList {
		// 找不到该日期，说明这日期过期了，不处理
		index, ok := dateIndexMap[manualData.DataTime]
		if !ok {
			continue
		}

		// 获取对应行的数据
		rowDataMap, ok := tableDataMap[index]
		if !ok {
			continue
		}

		// 找到对应的单元格
		colData, ok := rowDataMap[manualData.Tag]
		if !ok {
			continue
		}

		// 如果该单元格不是计算公式的单元格，那么直接退出当前循环即可
		if colData.DataType != 4 {
			continue
		}

		tagMap := make(map[string]float64)
		lenRelation := len(colData.RelationEdbInfoList)
		replaceNum := 0
		for _, relation := range colData.RelationEdbInfoList {
			relationCellTagName := strings.ToUpper(relation.Tag) + relation.Row
			valStr, tmpErr := getCalculateValue(tableDataMap, relation.Tag, relation.Row, calculateCellMap)
			if tmpErr != nil {
				continue
			}
			tmpValDecimal, tmpErr := decimal.NewFromString(valStr)
			if tmpErr != nil {
				continue
			}
			tagMap[relationCellTagName], _ = tmpValDecimal.Float64()
			replaceNum++
		}

		// 如果替换的数据与关联的不一致，那么就退出当前循环
		if lenRelation != replaceNum {
			continue
		}

		// 计算
		val, _, err := calculate(strings.ToUpper(colData.Value), tagMap)
		// 计算失败，退出循环
		if err != nil {
			continue
		}
		// 重新赋值
		colData.ShowValue = val
		tableDataMap[index][manualData.Tag] = colData

	}

	// 计算文本行的单元格
	for k, textRow := range tmpTextRowList {
		// 获取对应行的数据
		index := lenTableData + k + 1
		rowDataMap, ok := tableDataMap[index]
		if !ok {
			continue
		}

		for colIndex, _ := range textRow {
			currTag := tagList[colIndex]
			// 找到对应的单元格
			colData, ok := rowDataMap[currTag]
			if !ok {
				continue
			}

			// 如果该单元格不是计算公式的单元格，那么直接退出当前循环即可
			if colData.DataType != 4 {
				continue
			}

			tagMap := make(map[string]float64)
			lenRelation := len(colData.RelationEdbInfoList)
			replaceNum := 0
			for _, relation := range colData.RelationEdbInfoList {
				relationCellTagName := strings.ToUpper(relation.Tag) + relation.Row
				valStr, tmpErr := getCalculateValue(tableDataMap, relation.Tag, relation.Row, calculateCellMap)
				if tmpErr != nil {
					continue
				}
				tmpValDecimal, tmpErr := decimal.NewFromString(valStr)
				if tmpErr != nil {
					continue
				}
				tagMap[relationCellTagName], _ = tmpValDecimal.Float64()
				replaceNum++
			}

			// 如果替换的数据与关联的不一致，那么就退出当前循环
			if lenRelation != replaceNum {
				continue
			}

			// 计算
			val, _, err := calculate(strings.ToUpper(colData.Value), tagMap)
			// 计算失败，退出循环
			if err != nil {
				continue
			}
			// 重新赋值
			colData.ShowValue = val
			tableDataMap[index][currTag] = colData
		}

	}

	// 计算文本行第一列的数据值（多行）
	for k, colData := range firstColTextRowList {
		// 如果该单元格不是计算公式的单元格，那么直接退出当前循环即可
		if colData.DataType != 4 {
			continue
		}

		tagMap := make(map[string]float64)
		lenRelation := len(colData.RelationEdbInfoList)
		replaceNum := 0
		for _, relation := range colData.RelationEdbInfoList {
			relationCellTagName := strings.ToUpper(relation.Tag) + relation.Row
			valStr, tmpErr := getCalculateValue(tableDataMap, relation.Tag, relation.Row, calculateCellMap)
			if tmpErr != nil {
				continue
			}
			tmpValDecimal, tmpErr := decimal.NewFromString(valStr)
			if tmpErr != nil {
				continue
			}
			tagMap[relationCellTagName], _ = tmpValDecimal.Float64()
			replaceNum++
		}

		// 如果替换的数据与关联的不一致，那么就退出当前循环
		if lenRelation != replaceNum {
			continue
		}

		// 计算
		val, _, err := calculate(strings.ToUpper(colData.Value), tagMap)
		// 计算失败，退出循环
		if err != nil {
			continue
		}
		// 重新赋值
		colData.ShowValue = val
		firstColTextRowList[k] = colData
	}

	{
		// 文本行的数据处理返回
		textRowListDataResp = make([][]request.ManualDataReq, 0)
		newLenTableDataMap := len(tableDataMap)
		// 文本行的第一行所在的位置
		firstTextRow := lenTableData + 1
		for i := firstTextRow; i <= newLenTableDataMap; i++ {
			textRowDataResp := make([]request.ManualDataReq, 0)

			textRowDataResp = append(textRowDataResp, firstColTextRowList[i-firstTextRow])
			for _, tmpTag := range tagList {
				textRowDataResp = append(textRowDataResp, tableDataMap[i][tmpTag])
			}
			textRowListDataResp = append(textRowListDataResp, textRowDataResp)
		}

	}

	return
}

// getCalculateValue 获取公式计算的结果
func getCalculateValue(tableDataMap map[int]map[string]request.ManualDataReq, tag, row string, calculateCellMap map[string]string) (val string, err error) {
	rowInt, err := strconv.Atoi(row)
	if err != nil {
		return
	}

	// 单元格的标签名
	cellTagName := strings.ToUpper(tag) + row
	val, ok := calculateCellMap[cellTagName]
	if ok {
		return
	}

	// 查找行数据
	rowData, ok := tableDataMap[rowInt]
	if !ok {
		err = errors.New("查找" + row + "行的数据失败")
		return
	}

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

	// 如果不是计算单元格
	if colData.DataType != 4 {
		val = colData.ShowValue
		return
	}

	// 如果是计算单元格
	calculateCellMap[cellTagName] = ``

	tagMap := make(map[string]float64)
	for _, relation := range colData.RelationEdbInfoList {
		relationCellTagName := strings.ToUpper(relation.Tag) + relation.Row
		valStr, tmpErr := getCalculateValue(tableDataMap, relation.Tag, relation.Row, calculateCellMap)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		tmpValDecimal, tmpErr := decimal.NewFromString(valStr)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		tagMap[relationCellTagName], _ = tmpValDecimal.Float64()
	}

	// 计算
	val, _, err = calculate(strings.ToUpper(colData.Value), tagMap)
	if err != nil {
		return
	}
	// 重新赋值
	colData.ShowValue = val
	tableDataMap[rowInt][tag] = colData
	calculateCellMap[cellTagName] = val

	return
}

// calculate 公式计算
func calculate(calculateFormula string, TagMap map[string]float64) (calVal, 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.ReplaceFormula(TagMap, 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 = calResult.String()

	// 转Decimal然后四舍五入
	valDecimal, err := decimal.NewFromString(calVal)
	if err != nil {
		errMsg = "计算失败"
		err = errors.New("计算失败，结果转 Decimal 失败：Err:" + err.Error() + ";formulaStr:" + formulaFormStr)
		return
	}
	calVal = valDecimal.Round(4).String()

	return
}

// GetEdbIdsFromExcelCodes 获取表格中的指标IDs
func GetEdbIdsFromExcelCodes(excelCodes []string) (edbIds []int, err error) {
	edbIds = make([]int, 0)
	edbIdExist := make(map[int]bool)
	for _, v := range excelCodes {
		// 表格详情
		detail, msg, e := GetExcelDetailInfoByUnicode(v)
		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, reportId, chapterId int) string {
	if source == `` {
		return ``
	}

	return fmt.Sprint("batch_refresh_excel_edb:", source, ":", reportId, ":", chapterId)
}