eta_chart_lib/services/data/excel/balance_table.go

package excel

import (
	"encoding/json"
	"errors"
	"eta/eta_chart_lib/models"
	"eta/eta_chart_lib/models/data_manage/excel"
	"eta/eta_chart_lib/models/request"
	"eta/eta_chart_lib/utils"
	"fmt"
	"github.com/shopspring/decimal"
	"github.com/xuri/excelize/v2"
	"strconv"
	"strings"
	"time"
)

// 将表格信息转化成指标数据
func GetBalanceExcelData(excelDetail *excel.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
	}
	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
}

// 根据chartInfoId获取单个图表信息
func GetBalanceExcelChartSingle(chartInfoId, ChartEdbId int, lang string) (mappingListTmp []*excel.ExcelChartEdb, dataListMap map[int][]*models.EdbDataList, err error, errMsg string) {
	// 相关联指标
	if chartInfoId == 0 && ChartEdbId == 0 {
		err = fmt.Errorf(" 获取图表，指标信息失败 Err:chartInfoId和ChartEdbId不能同时为空")
		return
	}
	if chartInfoId == 0 {
		chartEdb, e := excel.GetExcelChartEdbById(ChartEdbId)
		if e != nil {
			err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", e.Error())
			return
		}
		chartInfoId = chartEdb.ChartInfoId
	}
	mappingListTmp, err = excel.GetExcelChartEdbMappingByChartInfoId(chartInfoId)
	if err != nil {
		errMsg = "获取失败"
		err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", err.Error())
		return
	}
	if len(mappingListTmp) <= 0 {
		errMsg = "获取失败"
		err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", err.Error())
		return
	}
	// 查询所有子表
	excelInfoId := mappingListTmp[0].ExcelInfoId
	excelInfo, err := excel.GetExcelInfoById(excelInfoId)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			errMsg = "表格不存在"
			err = fmt.Errorf(errMsg)
			return
		}
		errMsg = "查询子表失败"
		err = fmt.Errorf(" 查询子表失败图表，指标信息失败 Err:%s", err.Error())
		return
	}
	dataListMap = make(map[int][]*models.EdbDataList)
	if excelInfo.BalanceType == 1 {
		//查询库里是否有值
		chartDataList, e := excel.GetExcelChartDataByChartInfoId(chartInfoId)
		if e != nil {
			err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", e.Error())
			return
		}
		if len(chartDataList) == 0 {
			err = fmt.Errorf(" 获取图表，指标数据不存在")
			return
		}
		for _, v := range chartDataList {
			tmp := &models.EdbDataList{
				EdbDataId:     v.ExcelChartDataId,
				EdbInfoId:     v.ExcelChartEdbId,
				DataTime:      v.DataTime,
				DataTimestamp: v.DataTimestamp,
				Value:         v.Value,
			}
			dataListMap[v.ExcelChartEdbId] = append(dataListMap[v.ExcelChartEdbId], tmp)
		}
		return
	}

	// 获取图表详情
	newExcelDataMap, excelAllRows, excelAllCols, err, errMsg := GetBalanceExcelData(excelInfo, lang)
	if err != nil {
		return
	}

	for _, mapping := range mappingListTmp {
		er, msg := GetBalanceExcelEdbData(mapping, newExcelDataMap, dataListMap, excelAllRows, excelAllCols)
		if er != nil {
			utils.FileLog.Info(fmt.Sprintf(" 获取图表，指标信息失败 Err:%s, %s", er.Error(), msg))
			continue
		}
	}
	return
}

// 将表格信息转化成指标数据
func GetBalanceExcelEdbData(excelEdbMappingItem *excel.ExcelChartEdb, newMixedTableCellDataListMap map[int]map[int]request.MixedTableCellDataReq, dataListMap map[int][]*models.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

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

	for i, v := range newDateList {
		val, ok := newDataMap[i]
		if !ok {
			continue
		}
		// 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 := &models.EdbDataList{
			EdbDataId:     i,
			EdbInfoId:     excelEdbMappingItem.ExcelChartEdbId,
			DataTime:      v,
			DataTimestamp: timestamp,
			Value:         val,
		}
		list = append(list, tmp)
	}
	dataListMap[excelEdbMappingItem.ExcelChartEdbId] = list
	return
}

// GetSheetStr
// @return sheetName string 用户选择的sheet名称
// @return startColumnName string 用户选择的开始列名称
// @return endColumnName string 用户选择的结束列名称
// @return startNum int 用户选择的开始列单元格位置
// @return endNum int 用户选择的结束列单元格位置
// @return isAll bool 是否选择整行/列数据
// @return isRow bool 是否选择行数据
// @return isColumn bool 是否选择列数据
func GetSheetStr(sequenceStr string) (sheetName, startColumnName, endColumnName string, startNum, endNum int, isAll, isRow, isColumn bool, err error) {
	// 找出sheetName
	tmpList := strings.Split(sequenceStr, "!")
	if len(tmpList) != 2 {
		err = errors.New("错误的公式，查找sheet异常：" + sequenceStr)
		return
	}

	sheetName = tmpList[0]

	// 分离开始/结束单元格
	tmpList = strings.Split(tmpList[1], ":")
	if len(tmpList) != 2 {
		err = errors.New("错误的公式，查找开始/结束单元格异常：" + sequenceStr)
		return
	}

	startList := strings.Split(tmpList[0], "$")
	endList := strings.Split(tmpList[1], "$")

	lenList := len(startList)
	if lenList != len(endList) {
		err = errors.New("错误的公式，开始与结束单元格异常：" + sequenceStr)
		return
	}

	if lenList != 3 && lenList != 2 {
		err = errors.New("错误的公式：" + sequenceStr)
		return
	}

	startColumnName = startList[1]
	endColumnName = endList[1]

	// 长度为2的话，那说明是整行或整列
	if lenList == 2 {
		isAll = true

		startDeci, tmpErr1 := decimal.NewFromString(startList[1])
		endDeci, tmpErr2 := decimal.NewFromString(endList[1])

		if tmpErr1 == nil && tmpErr2 == nil {
			isRow = true // 正常转换的话，那么就是整行
			startNum = int(startDeci.IntPart())
			endNum = int(endDeci.IntPart())
			startColumnName = ``
			endColumnName = ``

			return
		}

		if tmpErr1 == nil || tmpErr2 == nil {
			err = errors.New("错误的公式2：" + sequenceStr)
			return
		}

		// 如果不能转成数字，那么就是整列
		isColumn = true

		return
	}

	// 确定行
	startDeci, tmpErr1 := decimal.NewFromString(startList[2])
	endDeci, tmpErr2 := decimal.NewFromString(endList[2])
	if tmpErr1 != nil && tmpErr1 != tmpErr2 {
		err = errors.New("错误的公式3：" + sequenceStr)
		return
	}

	startNum = int(startDeci.IntPart())
	endNum = int(endDeci.IntPart())

	if startColumnName != endColumnName && startNum != endNum {
		err = errors.New("选区不允许跨行或者跨列")
	}

	if startColumnName == endColumnName {
		isColumn = true // 列数据
	} else {
		isRow = true // 行数据
	}

	return
}