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"
)

// GetBalanceExcelData 将表格信息转化成指标数据
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
	}
	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) {
	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
	}
	charInfoIds := make([]int, 0)
	mappingListMap = make(map[int][]*excelModel.ExcelChartEdb, 0)
	if excelInfo.BalanceType == 1 {
		for _, mapping := range mappingListTmp {
			mappingListMap[mapping.ChartInfoId] = append(mappingListMap[mapping.ChartInfoId], mapping)
		}
		//查询库里是否有值
		chartDataList, e := excelModel.GetExcelChartDataByExcelInfoId(excelInfo.ExcelInfoId)
		if e != nil {
			err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", e.Error())
			return
		}
		if len(chartDataList) > 0 {
			for _, v := range chartDataList {
				tmp := &data_manage.EdbDataList{
					EdbDataId:     v.ExcelChartDataId,
					EdbInfoId:     v.ExcelChartEdbId,
					DataTime:      v.DataTime,
					DataTimestamp: v.DataTimestamp,
					Value:         v.Value,
				}
				dataListMap[v.ExcelChartEdbId] = append(dataListMap[v.ExcelChartEdbId], tmp)
			}
		}
	} else {
		newExcelDataMap, excelAllRows, excelAllCols, e, msg := GetBalanceExcelData(excelInfo, lang)
		if e != nil {
			err = e
			errMsg = msg
			return
		}
		for _, mapping := range mappingListTmp {
			mappingListMap[mapping.ChartInfoId] = append(mappingListMap[mapping.ChartInfoId], mapping)
			er, ms := GetBalanceExcelEdbData(mapping, newExcelDataMap, dataListMap, excelAllRows, excelAllCols)
			if er != nil {
				utils.FileLog.Info(fmt.Sprintf(" 获取图表，指标信息失败 Err:%s, %s", er.Error(), ms))
				continue
			}
		}
	}

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

// GetBalanceExcelEdbData 将表格信息转化成指标数据
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, tmpErrMsg := GetSheetStr(excelEdbMappingItem.DateSequence)
		if tmpErr != nil {
			errMsg = tmpErrMsg
			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)
			dateList = make([]string, 0)
			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 = append(dateList, currCell.ShowValue)
				//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)
			fmt.Println("startNum:", startNum, "endNum:", endNum)
			dateList = make([]string, 0)
			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.ShowValue)
				//dateList[i] = currCell.ShowValue
				i++
			}
		}

	}

	// 数据序列
	{
		_, startColumnName, endColumnName, startNum, endNum, isAll, isRow, isColumn, tmpErr, tmpErrMsg := GetSheetStr(excelEdbMappingItem.DataSequence)
		if tmpErr != nil {
			errMsg = tmpErrMsg
			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)
			dataList = make([]string, 0)
			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.ShowValue)
				//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)
			dataList = make([]string, 0)
			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.ShowValue)
				//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 {
		if v != "" {
			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 {
		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:" + e.Error())
			return
		}
		timestamp := dataTime.UnixNano() / 1e6
		tmp := &data_manage.EdbDataList{
			EdbDataId:     i,
			EdbInfoId:     excelEdbMappingItem.ExcelChartEdbId,
			DataTime:      v,
			DataTimestamp: timestamp,
			Value:         val,
		}
		list = append(list, tmp)
	}
	dataListMap[excelEdbMappingItem.ExcelChartEdbId] = list
	return
}

// 根据chartInfoId获取单个图表信息
func GetBalanceExcelChartSingle(chartInfoId, ChartEdbId int, lang string) (mappingListTmp []*excelModel.ExcelChartEdb, dataListMap map[int][]*data_manage.EdbDataList, err error, errMsg string) {
	// 相关联指标
	if chartInfoId == 0 && ChartEdbId == 0 {
		err = fmt.Errorf(" 获取图表，指标信息失败 Err:chartInfoId和ChartEdbId不能同时为空")
		return
	}
	if chartInfoId == 0 {
		chartEdb, e := excelModel.GetExcelChartEdbById(ChartEdbId)
		if e != nil {
			err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", e.Error())
			return
		}
		chartInfoId = chartEdb.ChartInfoId
	}
	mappingListTmp, err = excelModel.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 := 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
	}
	dataListMap = make(map[int][]*data_manage.EdbDataList)
	if excelInfo.BalanceType == 1 {
		//查询库里是否有值
		chartDataList, e := excelModel.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 := &data_manage.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
}

// 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, lang)
	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
}

func SyncBalanceEdbData(excelInfo *excelModel.ExcelInfo, balanceTableData [][]request.MixedTableCellDataReq) (err error) {
	tmpMappingList, e := excelModel.GetExcelChartEdbMappingByExcelInfoId(excelInfo.ExcelInfoId)
	if e != nil {
		err = fmt.Errorf(" 获取图表，指标信息失败 Err:%s", err.Error())
		return
	}
	if len(tmpMappingList) == 0 {
		return
	}
	excelDataMap := make(map[int][]*data_manage.EdbDataList)
	excelEdbMap := make(map[int]*excelModel.ExcelChartEdb)
	excelAllRows := len(balanceTableData)
	excelAllCols := 0
	newExcelDataMap := make(map[int]map[int]request.MixedTableCellDataReq)
	for r, row := range balanceTableData {
		tmp := len(row)
		if tmp > excelAllCols {
			excelAllCols = tmp
		}
		colMap := make(map[int]request.MixedTableCellDataReq)
		for c, col := range row {
			colMap[c] = col
		}
		newExcelDataMap[r] = colMap
	}

	for _, mapping := range tmpMappingList {
		excelEdbMap[mapping.ExcelChartEdbId] = mapping
		er, msg := GetBalanceExcelEdbData(mapping, newExcelDataMap, excelDataMap, excelAllRows, excelAllCols)
		if er != nil {
			utils.FileLog.Info(fmt.Sprintf(" 获取图表，指标信息失败 Err:%s, %s", msg, er.Error()))
			continue
		}
	}
	// 批量更新图表数据
	err = excelModel.BatchUpdateChartEdbData(excelInfo.ExcelInfoId, excelEdbMap, excelDataMap)
	if err != nil {
		err = fmt.Errorf(" 批量更新图表数据失败 Err:%s", err.Error())
		return
	}
	return
}

// GetBalanceExcelInfoOpButton 获取ETA平衡表格的操作权限
func GetBalanceExcelInfoOpButton(sysUserId, parentSysUserId int, haveOperaAuth bool, parentExcelInfoId int) (button excelModel.ExcelInfoDetailButton) {
	// 如果没有数据权限，那么直接返回
	if !haveOperaAuth {
		return
	}
	//非管理员角色查看其他用户创建的表格，可刷新、另存为、下载表格；
	button.RefreshButton = true
	button.CopyButton = true
	button.DownloadButton = true

	if sysUserId == parentSysUserId {
		button.OpButton = true
		button.RefreshEdbButton = true
		button.OpWorkerButton = true
		button.DeleteButton = true
	} else {
		obj := new(excelModel.ExcelWorker)
		workerList, err := obj.GetByExcelInfoId(parentExcelInfoId)
		if err == nil {
			for _, v := range workerList {
				if v.SysUserId == sysUserId {
					button.OpButton = true
					button.RefreshEdbButton = true
					button.DeleteButton = true
					break
				}
			}
		}
	}
	return
}