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
}

// 获取平衡表指标绑定详情
func GetBalanceExcelChartSimple(excelInfo *excelModel.ExcelInfo, chartInfoId int, lang string) (list []*data_manage.ChartInfoView, mappingListMap map[int][]*excelModel.ExcelChartEdb, dataListMap map[int][]*data_manage.EdbDataList, err error, errMsg string) {
	mappingListTmp, err := excelModel.GetExcelChartEdbMappingByChartInfoId(chartInfoId)
	if err != nil {
		errMsg = "获取失败"
		err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", err.Error())
		return
	}
	mappingList := make([]*excelModel.ExcelChartEdbView, 0)
	for _, v := range mappingListTmp {
		tmp :=
			&excelModel.ExcelChartEdbView{
				ExcelChartEdbId: v.ExcelChartEdbId,
				ExcelInfoId:     v.ExcelInfoId,
				ChartInfoId:     v.ChartInfoId,
				EdbCode:         v.EdbCode,
				EdbName:         v.EdbName,
				DateSequenceStr: v.DateSequence,
				DataSequenceStr: v.DataSequence,
				/*MaxData:         v.MaxData,
				MinData:         v.MinData,
				IsOrder:         v.IsOrder,
				IsAxis:          v.IsAxis,
				EdbInfoType:     v.EdbInfoType,
				LeadValue:       v.LeadValue,
				LeadUnit:        v.LeadUnit,*/
				FromTag: v.FromTag,
			}
		mappingList = append(mappingList, tmp)
	}

	return
}

func GetBalanceExcelChart(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 GetBalanceExcelChartSingle(chartInfo *data_manage.ChartInfoView, lang string) (mappingListTmp []*excelModel.ExcelChartEdb, dataListMap map[int][]*data_manage.EdbDataList, err error, errMsg string) {
	// 相关联指标
	mappingListTmp, err = excelModel.GetExcelChartEdbMappingByChartInfoId(chartInfo.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 := excelModel.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
	}
	// 获取图表详情
	newExcelDataMap, excelAllRows, excelAllCols, err, errMsg := GetBalanceExcelData(excelInfo, lang)
	if err != nil {
		return
	}
	dataListMap = make(map[int][]*data_manage.EdbDataList)

	for _, mapping := range mappingListTmp {
		err, errMsg = GetBalanceExcelEdbData(mapping, newExcelDataMap, dataListMap, excelAllRows, excelAllCols)
		if err != nil {
			err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", err.Error())
			return
		}
	}
	return
}

// TransferBalanceExcelContentToStatic 将平衡表动态表的内容转换为静态表的格式
func TransferBalanceExcelContentToStatic(excelDetail *excelModel.ExcelInfo, lang string) (newContent string, err error) {
	var mixedTableReq request.MixedTableReq
	err = json.Unmarshal([]byte(excelDetail.Content), &mixedTableReq)
	if err != nil {
		err = errors.New("表格json转结构体失败，Err:" + err.Error())
		return
	}

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

		for _, v := range cellRelationConfList {
			if v.Type == request.EdbDT || v.Type == request.InsertDataDT || v.Type == request.PopInsertDataDT || v.Type == request.InsertEdbCalculateDataDT || v.Type == request.DateCalculateDataDT {
			} else {
				cellRelationConfNewList = append(cellRelationConfNewList, v)
			}
		}
	}

	cellRelationConfByte, err := json.Marshal(cellRelationConfNewList)
	if err != nil {
		err = fmt.Errorf("cellRelationConf json转结构体失败，Err:%s", err.Error())
		return
	}
	cellRelationConf = string(cellRelationConfByte)

	newData, tmpErr, tmpErrMsg := GetMixedTableCellData(mixedTableReq, "")
	if tmpErr != nil {
		err = errors.New(tmpErrMsg + "获取最新的数据失败 ，Err:" + tmpErr.Error())
		return
	}
	for r, row := range newData {
		for c, cell := range row {
			// todo 系统日期类型，原先是不可编辑，转成静态表之后是否变成可编辑
			cell.EdbInfoId = 0
			if cell.DataType != request.FormulateCalculateDataDT { //除了公式计算，其他类型都转成自定义类型
				cell.DataType = request.CustomTextDT //除了计算公式的关联关系被保留，其余绑定关系都删除
				if _, ok := cellRelationConfMap[cell.Uid]; ok {
					delete(cellRelationConfMap, cell.Uid)
				}
			}
			if cell.DataTime != "" {
				cell.DataTimeType = request.CustomDateT
			}
			cell.Value = cell.ShowValue
			row[c] = cell
		}
		newData[r] = row
	}
	var newMixedTableReq request.MixedTableReq
	newMixedTableReq.CellRelation = cellRelationConf
	newMixedTableReq.Data = newData
	newMixedTableByte, err := json.Marshal(newMixedTableReq)
	if err != nil {
		err = fmt.Errorf("newData json转结构体失败，Err:%s", err.Error())
		return
	}
	newContent = string(newMixedTableByte)
	return
}