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/models/system"
	"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
}

// AddBalanceStaticExcel 另存为静态表
func AddBalanceStaticExcel(oldExcelInfoId int, versionName string, sysUser *system.Admin, parentId int) (excelInfo *excelModel.ExcelInfo, err error, errMsg string, isSendEmail bool) {
	isSendEmail = true

	versionName = utils.TrimLRStr(versionName)

	// 获取原ETA表格信息
	oldExcelInfo, err := excelModel.GetExcelInfoById(oldExcelInfoId)
	if err != nil {
		errMsg = "获取ETA表格失败"
		return
	}

	// todo 操作权限校验
	{
		// 数据权限
		/*haveOperaAuth, tmpErr := data_manage_permission.CheckExcelPermissionByExcelInfoId(oldExcelInfo.ExcelInfoId, oldExcelInfo.ExcelClassifyId, oldExcelInfo.IsJoinPermission, sysUser.AdminId)
		if err != nil {
			errMsg = "获取ETA表格权限失败"
			err = errors.New("获取ETA表格权限失败,Err:" + tmpErr.Error())
			return
		}

		button := GetExcelInfoOpButton(sysUser, oldExcelInfo.SysUserId, oldExcelInfo.Source, haveOperaAuth)
		if !button.CopyButton {
			errMsg = "无操作权限"
			err = errors.New(errMsg)
			isSendEmail = false
			return
		}*/
	}

	// 检验分类下是否存在该表格名称
	{
		var condition string
		var pars []interface{}
		condition += " AND rel_excel_info_id=? "
		pars = append(pars, oldExcelInfoId)

		condition += " AND version_name=? "
		pars = append(pars, versionName)

		count, tmpErr := excelModel.GetExcelInfoCountByCondition(condition, pars)
		if tmpErr != nil {
			errMsg = "判断表格名称是否存在失败"
			err = tmpErr
			return
		}
		if count > 0 {
			errMsg = "表格名称已存在，请重新填写表格名称"
			err = errors.New(errMsg)
			isSendEmail = false
			return
		}
	}

	// 表格信息
	timestamp := strconv.FormatInt(time.Now().UnixNano(), 10)
	excelInfo = &excelModel.ExcelInfo{
		//ExcelInfoId:     0,
		ExcelName:          oldExcelInfo.ExcelName + "(" + versionName + ")",
		Source:             oldExcelInfo.Source,
		ExcelType:          oldExcelInfo.ExcelType,
		UniqueCode:         utils.MD5(utils.EXCEL_DATA_PREFIX + "_" + timestamp),
		ExcelClassifyId:    oldExcelInfo.ExcelClassifyId,
		SysUserId:          sysUser.AdminId,
		SysUserRealName:    sysUser.RealName,
		Content:            oldExcelInfo.Content,
		ExcelImage:         oldExcelInfo.ExcelImage,
		FileUrl:            oldExcelInfo.FileUrl,
		RelExcelInfoId:     oldExcelInfoId,
		VersionName:        versionName,
		UpdateUserId:       sysUser.AdminId,
		UpdateUserRealName: sysUser.AdminName,
		BalanceType:        1,
		Sort:               0,
		IsDelete:           0,
		ModifyTime:         time.Now(),
		CreateTime:         time.Now(),
	}

	// 获取excel与指标的关系表
	list, tmpErr := excelModel.GetAllExcelEdbMappingByExcelInfoId(excelInfo.ExcelInfoId)
	if tmpErr != nil {
		errMsg = "获取失败"
		err = tmpErr
		return
	}
	for k, v := range list {
		v.ExcelEdbMappingId = 0
		v.ExcelInfoId = 0
		list[k] = v
	}
	var childExcel *excelModel.ExcelInfo
	err = excelModel.AddExcelInfo(excelInfo, list, childExcel)
	if err != nil {
		errMsg = "保存失败"
		return
	}
	if parentId == 0 {
		//查询动态表所有的子表，并复制为静态表

	} else if parentId > 0 {
		// 如果复制的是动态表的子表，则同步复制关联的图表

	}
	return
}