eta_api/services/data/excel/balance_table.go

package excel

import (
	"encoding/json"
	"errors"
	"eta/eta_api/models/data_manage"
	excelModel "eta/eta_api/models/data_manage/excel"
	"eta/eta_api/models/data_manage/excel/request"
	"eta/eta_api/utils"
	"fmt"
	"github.com/xuri/excelize/v2"
	"strconv"
	"time"
)

// 将表格信息转化成指标数据
func GetBalanceExcelData(excelDetail *excelModel.ExcelInfo, lang string) (newDataMap map[int]map[int]request.MixedTableCellDataReq, allRows, allCols int, err error, errMsg string) {
	var result request.MixedTableReq
	err = json.Unmarshal([]byte(excelDetail.Content), &result)
	if err != nil {
		err = errors.New("表格json转结构体失败，Err:" + err.Error())
		return
	}
	//todo 语言兼容
	newData, tmpErr, tmpErrMsg := GetMixedTableCellData(result, lang)
	if tmpErr != nil {
		errMsg = "获取失败"
		if tmpErrMsg != `` {
			errMsg = tmpErrMsg
		}
		err = errors.New("获取最新的数据失败，Err:" + tmpErr.Error())
		return
	}

	allRows = len(newData)
	allCols = 0
	newDataMap = make(map[int]map[int]request.MixedTableCellDataReq)
	for r, row := range newData {
		tmp := len(row)
		if tmp > allCols {
			allCols = tmp
		}
		colMap := make(map[int]request.MixedTableCellDataReq)
		for c, col := range row {
			colMap[c] = col
		}
		newDataMap[r] = colMap
	}
	return
}

func GetBalanceExcelChartList(excelInfo *excelModel.ExcelInfo, lang string) (list []*data_manage.ChartInfoView, mappingListMap map[int][]*excelModel.ExcelChartEdb, dataListMap map[int][]*data_manage.EdbDataList, err error, errMsg string) {
	newExcelDataMap, excelAllRows, excelAllCols, err, errMsg := GetBalanceExcelData(excelInfo, lang)
	if err != nil {
		return
	}
	dataListMap = make(map[int][]*data_manage.EdbDataList)
	// 相关联指标
	mappingListTmp, err := excelModel.GetExcelChartEdbMappingByExcelInfoId(excelInfo.ExcelInfoId)
	if err != nil {
		errMsg = "获取失败"
		err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", err.Error())
		return
	}
	mappingListMap = make(map[int][]*excelModel.ExcelChartEdb, 0)
	charInfoIds := make([]int, 0)
	for _, mapping := range mappingListTmp {
		mappingListMap[mapping.ChartInfoId] = append(mappingListMap[mapping.ChartInfoId], mapping)
		err, errMsg = GetBalanceExcelEdbData(mapping, newExcelDataMap, dataListMap, excelAllRows, excelAllCols)
		if err != nil {
			err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", err.Error())
			return
		}
	}
	for k, _ := range mappingListMap {
		charInfoIds = append(charInfoIds, k)
	}
	list = make([]*data_manage.ChartInfoView, 0)
	if len(charInfoIds) > 0 {
		chartInfoList, e := data_manage.GetChartInfoViewByIdList(charInfoIds)
		if e != nil {
			errMsg = "获取失败"
			err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", e.Error())
			return
		}
		list = chartInfoList
		/*for _, chartInfo := range chartInfoList {
			mappingList, ok := mappingListMap[chartInfo.ChartInfoId]
			if !ok {
				err = fmt.Errorf("未找到图表关联的指标信息")
				return
			}
			var chartInfoResp *data_manage.ChartInfoDetailResp
			chartInfoResp, err, errMsg = GetBalanceExcelChartDetail(chartInfo, mappingList, sysUser, dataListMap)
			if err != nil {
				return
			}
			list = append(list, chartInfoResp)
		}*/
	}
	return
}

// 将表格信息转化成指标数据
func GetBalanceExcelEdbData(excelEdbMappingItem *excelModel.ExcelChartEdb, newMixedTableCellDataListMap map[int]map[int]request.MixedTableCellDataReq, dataListMap map[int][]*data_manage.EdbDataList, allRows, allCols int) (err error, errMsg string) {
	var dateList, dataList []string
	// 日期序列
	{
		_, startColumnName, endColumnName, startNum, endNum, isAll, isRow, isColumn, tmpErr := GetSheetStr(excelEdbMappingItem.DateSequence)
		if tmpErr != nil {
			err = tmpErr
			return
		}

		startNum = startNum - 1
		endNum = endNum - 1
		// 选择行的数据
		if isRow {
			// 因为是选择一行的数据，所以开始行和结束行时一样的
			//endNum = startNum - 1

			// 开始列名、结束列
			var startColumn, endColumn int
			if isAll {
				// 结束列（其实也就是整列的个数）
				endColumn = allCols - 1
			} else {
				tmpStartColumn, tmpErr := excelize.ColumnNameToNumber(startColumnName)
				if tmpErr != nil {
					errMsg = "列名异常:" + startColumnName
					err = errors.New(errMsg)
					return
				}

				tmpEndColumn, tmpErr := excelize.ColumnNameToNumber(endColumnName)
				if tmpErr != nil {
					errMsg = "列名异常:" + endColumnName
					err = errors.New(errMsg)
					return
				}
				startColumn = tmpStartColumn - 1
				endColumn = tmpEndColumn - 1
			}

			// 最大列数，如果设置的超过了最大列数，那么结束列就是最大列数
			maxCol := allCols
			if endColumn > maxCol {
				endColumn = maxCol - 1
			}

			// 长度固定，避免一直申请内存空间
			dateList = make([]string, endColumn-startColumn+1)

			i := 0
			for currColumn := startColumn; currColumn <= endColumn; currColumn++ {
				currCell, ok := newMixedTableCellDataListMap[startNum][currColumn]
				if !ok {
					errMsg = fmt.Sprintf("第%d列,第%d行数据异常", currColumn, startNum)
					err = errors.New(errMsg)
					return
				}
				dateList[i] = currCell.ShowValue
				i++
			}

		} else if isColumn { // 选择列的数据
			if isAll {
				// 选择一整列的话，结束行得根据实际情况调整（其实也就是整个sheet有多少行）
				endNum = allRows - 1
			}

			startColumn, tmpErr := excelize.ColumnNameToNumber(startColumnName)
			if tmpErr != nil {
				errMsg = "列名异常:" + startColumnName
				err = errors.New(errMsg)
				return
			}
			startColumn = startColumn - 1

			// 最大行数，如果设置的超过了最大行数，那么结束行就是最大行数
			maxRow := allRows
			if endNum > maxRow {
				endNum = maxRow - 1
			}
			// 长度固定，避免一直申请内存空间
			dateList = make([]string, endNum-startNum+1)
			i := 0
			for currRow := startNum; currRow <= endNum; currRow++ {
				currCell, ok := newMixedTableCellDataListMap[currRow][startColumn]
				if !ok {
					errMsg = fmt.Sprintf("第%d列,第%d行数据异常", currRow, startColumn)
					err = errors.New(errMsg)
					return
				}
				//dateList = append(dateList, currCell.Value)
				dateList[i] = currCell.ShowValue
				i++
			}
		}

	}

	// 数据序列
	{
		_, startColumnName, endColumnName, startNum, endNum, isAll, isRow, isColumn, tmpErr := GetSheetStr(excelEdbMappingItem.DataSequence)
		if tmpErr != nil {
			err = tmpErr
			return
		}

		startNum = startNum - 1
		endNum = endNum - 1
		// 选择行的数据
		if isRow {
			// 开始列名、结束列
			var startColumn, endColumn int
			if isAll {
				// 结束列（其实也就是整列的个数）
				endColumn = allCols - 1
			} else {

				tmpStartColumn, tmpErr := excelize.ColumnNameToNumber(startColumnName)
				if tmpErr != nil {
					errMsg = "列名异常:" + startColumnName
					err = errors.New(errMsg)
					return
				}

				tmpEndColumn, tmpErr := excelize.ColumnNameToNumber(endColumnName)
				if tmpErr != nil {
					errMsg = "列名异常:" + endColumnName
					err = errors.New(errMsg)
					return
				}
				startColumn = tmpStartColumn - 1
				endColumn = tmpEndColumn - 1
			}

			// 最大列数，如果设置的超过了最大列数，那么结束列就是最大列数
			maxCol := allCols
			if endColumn > maxCol {
				endColumn = maxCol - 1
			}
			// 长度固定，避免一直申请内存空间
			dataList = make([]string, endColumn-startColumn+1)
			i := 0
			for currColumn := startColumn; currColumn <= endColumn; currColumn++ {
				currCell, ok := newMixedTableCellDataListMap[startNum][currColumn]
				if !ok {
					errMsg = fmt.Sprintf("第%d列,第%d行数据异常", startColumn, currColumn)
					err = errors.New(errMsg)
					return
				}
				//dataList = append(dataList, currCell.Value)
				dataList[i] = currCell.ShowValue
				i++
			}

		} else if isColumn { // 选择列的数据
			if isAll {
				// 选择一整列的话，结束行得根据实际情况调整（其实也就是整个sheet有多少行）
				endNum = allRows - 1
			}

			startColumn, tmpErr := excelize.ColumnNameToNumber(startColumnName)
			if tmpErr != nil {
				errMsg = "列名异常:" + startColumnName
				err = errors.New(errMsg)
				return
			}
			startColumn = startColumn - 1

			// 最大行数，如果设置的超过了最大行数，那么结束行就是最大行数
			maxRow := allRows
			if endNum > maxRow {
				endNum = maxRow - 1
			}

			// 长度固定，避免一直申请内存空间
			dataList = make([]string, endNum-startNum+1)
			i := 0
			for currRow := startNum; currRow <= endNum; currRow++ {
				currCell, ok := newMixedTableCellDataListMap[currRow][startColumn]
				if !ok {
					errMsg = fmt.Sprintf("第%d列,第%d行数据异常", currRow, startColumn)
					err = errors.New(errMsg)
					return
				}
				//dataList = append(dataList, currCell.Value)
				dataList[i] = currCell.ShowValue
				i++
			}
		}
	}

	//fmt.Println(dateList, dataList)
	//fmt.Println("日期序列结束")

	// 将excel中的日期、数据系列处理
	//newDateList, newDataList, err, errMsg := excel2.HandleEdbSequenceVal(dateList, dataList)
	newDateList, newDataList := dateList, dataList
	if err != nil {
		err = fmt.Errorf(" 处理日期和数据系列失败 %s", err.Error())
		return
	}
	newDataMap := make(map[int]float64, len(newDataList))
	for i, v := range newDataList {
		val, e := strconv.ParseFloat(v, 64)
		if e != nil {
			err = fmt.Errorf(" 处理日期和数据系列失败 %s", e.Error())
			return
		}
		newDataMap[i] = val
	}
	//组装成excelEdbData
	list := make([]*data_manage.EdbDataList, 0)

	for i, v := range newDateList {
		// todo 处理DataTimestamp
		dataTime, e := time.ParseInLocation(utils.FormatDate, v, time.Local)
		if e != nil {
			err = errors.New("time.Parse Err:" + err.Error())
			return
		}
		timestamp := dataTime.UnixNano() / 1e6
		tmp := &data_manage.EdbDataList{
			EdbDataId:     i,
			EdbInfoId:     excelEdbMappingItem.ExcelChartEdbId,
			DataTime:      v,
			DataTimestamp: timestamp,
			Value:         newDataMap[i],
		}
		list = append(list, tmp)
	}
	dataListMap[excelEdbMappingItem.ExcelChartEdbId] = list
	return
}