eta_api/services/data/excel/custom_analysis_edb.go

package excel

import (
	"encoding/json"
	"errors"
	"eta/eta_api/models/data_manage/excel"
	"eta/eta_api/services/data"
	excelServices "eta/eta_api/services/excel"
	"eta/eta_api/utils"
	"fmt"
	"github.com/araddon/dateparse"
	"github.com/shopspring/decimal"
	"github.com/tealeg/xlsx"
	"github.com/xuri/excelize/v2"
	"strings"
)

// GetCustomAnalysisExcelData 获取自定义分析的表格data数据
func GetCustomAnalysisExcelData(excelInfo *excel.ExcelInfo) (luckySheet excelServices.LuckySheet, err error, errMsg string) {
	// 查找当前excel的sheet列表
	sheetList, err := excel.GetAllSheetList(excelInfo.ExcelInfoId)
	if err != nil {
		errMsg = "保存失败"
		err = errors.New("查找当前excel的sheet列表失败，Err:" + err.Error())
		return
	}
	currSheetMap := make(map[string]string)
	for _, sheet := range sheetList {
		currSheetMap[sheet.SheetName] = sheet.SheetName
	}

	sheetCellDataMapList := make(map[int][]excelServices.LuckySheetCellData)

	// 通过excel的id获取各个sheet的单元格数据map
	{
		dataList, tmpErr := excel.GetAllSheetDataListByExcelInfoId(excelInfo.ExcelInfoId)
		if tmpErr != nil {
			err = tmpErr
			return
		}

		for _, cellData := range dataList {
			sheetDataList, ok := sheetCellDataMapList[cellData.ExcelSheetId]
			if !ok {
				sheetDataList = make([]excelServices.LuckySheetCellData, 0)
			}

			tmpSheetDataList := make([]excelServices.LuckySheetCellData, 0)
			err = json.Unmarshal([]byte(cellData.Data), &tmpSheetDataList)
			if err != nil {
				err = errors.New(fmt.Sprintf("解析data的配置失败,sheetId：%d，Err:%s", cellData.ExcelDataId, err.Error()))
				return
			}
			sheetCellDataMapList[cellData.ExcelSheetId] = append(sheetDataList, tmpSheetDataList...)
		}
	}

	// 转成luckySheet的数据格式
	luckySheet = excelServices.LuckySheet{
		SheetList: make([]excelServices.LuckySheetData, 0),
	}

	for _, sheet := range sheetList {
		var luckySheetDataConfig excelServices.LuckySheetDataConfig
		err = json.Unmarshal([]byte(sheet.Config), &luckySheetDataConfig)
		if err != nil {
			err = errors.New(fmt.Sprintf("解析sheet的配置失败,sheetId：%d，Err:%s", sheet.ExcelSheetId, err.Error()))
			return
		}
		tmpLuckySheetDataInfo := excelServices.LuckySheetData{
			Name:     sheet.SheetName,
			Index:    sheet.Sort,
			CellData: sheetCellDataMapList[sheet.ExcelSheetId],
			Config:   luckySheetDataConfig,
		}
		luckySheet.SheetList = append(luckySheet.SheetList, tmpLuckySheetDataInfo)
	}

	return
}

// GenerateExcelCustomAnalysisExcel 根据自定义分析的表格data数据生成excel
func GenerateExcelCustomAnalysisExcel(excelInfo *excel.ExcelInfo) (downloadFilePath string, err error, errMsg string) {
	luckySheet, err, errMsg := GetCustomAnalysisExcelData(excelInfo)
	if err != nil {
		return
	}

	downloadFilePath, err = luckySheet.ToExcel(false)

	return
}

// 数据集
type dataStruct struct {
	Value float64
	Ok    bool
}

// HandleEdbSequenceVal 处理日期集和数据集（获取可用的日期、数据集）
func HandleEdbSequenceVal(dateSequenceVal, dataSequenceVal []string) (newDateList []string, newDataList []float64, err error, errMsg string) {
	newDateList = make([]string, 0)
	newDataList = make([]float64, 0)

	dataList := make([]dataStruct, 0)
	{
		for _, v := range dataSequenceVal {
			// 如果没有数据集，那么就过滤
			if v == `` {
				// 如果开始插入数据了，那么就需要插入不存在值
				dataList = append(dataList, dataStruct{
					Value: 0,
					Ok:    false,
				})
				continue
			}

			v = strings.Replace(v, ",", "", -1)
			v = strings.Replace(v, "，", "", -1)
			// 过滤空格
			v = strings.Replace(v, " ", "", -1)
			v = strings.TrimSpace(v)

			var tmpVal float64
			if strings.Contains(v, "%") {
				// 百分比的数
				isPercentage, percentageValue := utils.IsPercentage(v)
				if !isPercentage {
					dataList = append(dataList, dataStruct{
						Value: 0,
						Ok:    false,
					})
					continue
				}
				tmpValDec, tmpErr := decimal.NewFromString(percentageValue)
				if tmpErr != nil {
					dataList = append(dataList, dataStruct{
						Value: 0,
						Ok:    false,
					})
					continue
				}
				tmpVal, _ = tmpValDec.Div(decimal.NewFromFloat(100)).Float64()
			} else {
				tmpValDec, tmpErr := decimal.NewFromString(v)
				if tmpErr != nil {
					dataList = append(dataList, dataStruct{
						Value: 0,
						Ok:    false,
					})
					continue
				}
				tmpVal, _ = tmpValDec.Float64()
			}
			dataList = append(dataList, dataStruct{
				Value: tmpVal,
				Ok:    true,
			})
		}
	}

	// 日期集
	dateList := make([]string, 0)
	{
		for _, v := range dateSequenceVal {
			// 如果没有数据集，那么就过滤
			if v == `` {
				// 如果开始插入数据了，那么就需要插入不存在值
				dateList = append(dateList, "")
				continue
			}

			// 过滤空格
			v = strings.Replace(v, " ", "", -1)
			t1, tmpErr := dateparse.ParseAny(v)
			if tmpErr != nil {
				dateList = append(dateList, "")
				continue
			}

			dateList = append(dateList, t1.Format(utils.FormatDate))
		}
	}

	lenData := len(dataList)
	lenDate := len(dateList)

	// 最小个数
	num := lenDate
	if num > lenData {
		num = lenData
	}

	for i := 0; i < num; i++ {
		date := dateList[i]
		tmpData := dataList[i]

		// 日期为空、数据为空
		if !tmpData.Ok || date == `` {
			continue
		}

		newDateList = append(newDateList, date)
		newDataList = append(newDataList, tmpData.Value)
	}

	return
}

// ResetCustomAnalysisData 数据重置的结构体
type ResetCustomAnalysisData struct {
	EdbInfoId int
	DateList  []string
	DataList  []float64
}

// Refresh  刷新表格关联的指标信息
func Refresh(excelInfo *excel.ExcelInfo, lang string) (err error, errMsg string, isSendEmail bool) {
	isSendEmail = true

	list, err := excel.GetAllExcelEdbMappingItemByExcelInfoId(excelInfo.ExcelInfoId)
	if err != nil {
		errMsg = "获取失败"
		err = errors.New("查找所有的mapping失败" + err.Error())
		return
	}

	// 没有关联指标，那么就退出吧
	if len(list) <= 0 {
		return
	}

	for k, v := range list {
		var tmpCalculateFormula excel.CalculateFormula
		err = json.Unmarshal([]byte(v.CalculateFormula), &tmpCalculateFormula)
		if err != nil {
			errMsg = "获取失败"
			err = errors.New(fmt.Sprintf("指标id：%d，公式转换失败，Err:%s", v.EdbInfoId, err.Error()))
			return
		}
		v.DateSequenceStr = tmpCalculateFormula.DateSequenceStr
		v.DataSequenceStr = tmpCalculateFormula.DataSequenceStr
		list[k] = v
	}

	luckySheet, err, errMsg := GetCustomAnalysisExcelData(excelInfo)
	if err != nil {
		err = errors.New(fmt.Sprintf("获取自定义分析Excel数据失败，Err：%s", err.Error()))
		return
	}

	// 获取excel表格数据
	xlsxFile, err := luckySheet.GetExcelData(false)
	if err != nil {
		err = errors.New(fmt.Sprintf("获取excel表格数据，Err：%s", err.Error()))
		return
	}

	//fmt.Println(xlsxFile)

	edbInfoIdList := make([]int, 0)

	for _, v := range list {
		edbInfoIdList = append(edbInfoIdList, v.EdbInfoId)

		// 获取对应的日期和数据列表
		relDateList, relDataList, tmpErr, tmpErrMsg := getDateAndDataList(v, xlsxFile)
		if tmpErr != nil {
			err = errors.New(fmt.Sprintf("《%s》获取对应的日期和数据列表，Err：%s", v.EdbName, tmpErr.Error()))
			errMsg = fmt.Sprintf("《%s》%s", v.EdbName, tmpErrMsg)
			return
		}
		if len(relDateList) <= 0 {
			errMsg = fmt.Sprintf("《%s》的日期序列为空", v.EdbName)
			err = errors.New(errMsg)
			return
		}
		if len(relDataList) <= 0 {
			errMsg = fmt.Sprintf("《%s》的数据序列为空", v.EdbName)
			err = errors.New(errMsg)
			return
		}
		//fmt.Println(v)
		req2 := &ResetCustomAnalysisData{
			EdbInfoId: v.EdbInfoId,
			DateList:  relDateList,
			DataList:  relDataList,
		}

		// 调用指标库去更新
		reqJson, tmpErr := json.Marshal(req2)
		if tmpErr != nil {
			err = errors.New(fmt.Sprintf("结构体转对象失败，Err：%s", tmpErr.Error()))
			return
		}
		respItem, tmpErr := data.ResetCustomAnalysisData(string(reqJson), lang)
		if tmpErr != nil {
			err = errors.New(fmt.Sprintf("调用指标库去更新，Err：%s", tmpErr.Error()))
			return
		}
		if respItem.Ret != 200 {
			errMsg = respItem.Msg
			err = errors.New(respItem.ErrMsg)
			return
		}
	}

	if len(edbInfoIdList) > 0 {
		err, _ = data.EdbInfoRefreshAllFromBaseV3(edbInfoIdList, false, true, true)
	}

	//xlsxFile.Sheet[]

	return
}

// getDateAndDataList
// @Description: 获取待刷新的日期和数据
// @author: Roc
// @datetime 2023-12-21 15:21:14
// @param excelEdbMappingItem *excel.ExcelEdbMappingItem
// @param xlsxFile *xlsx.File
// @return newDateList []string
// @return newDataList []float64
// @return err error
// @return errMsg string
func getDateAndDataList(excelEdbMappingItem *excel.ExcelEdbMappingItem, xlsxFile *xlsx.File) (newDateList []string, newDataList []float64, err error, errMsg string) {
	var dateList, dataList []string

	// 日期序列
	{
		sheetName, startColumnName, endColumnName, startNum, endNum, isAll, isRow, isColumn, tmpErr, tmpErrMsg := GetSheetStr(excelEdbMappingItem.DateSequenceStr)
		if tmpErr != nil {
			errMsg = tmpErrMsg
			err = tmpErr
			return
		}

		// 查找sheet页
		sheetInfo, ok := xlsxFile.Sheet[sheetName]
		if !ok {
			errMsg = "找不到" + sheetName
			err = errors.New(errMsg)
			return
		}

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

			// 开始列名、结束列
			var startColumn, endColumn int
			if isAll {
				// 结束列（其实也就是整列的个数）
				endColumn = len(sheetInfo.Cols) - 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 := len(sheetInfo.Cols)
			if endColumn > maxCol {
				endColumn = maxCol - 1
			}

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

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

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

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

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

	}

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

		// 查找sheet页
		sheetInfo, ok := xlsxFile.Sheet[sheetName]
		if !ok {
			errMsg = "找不到" + sheetName
			err = errors.New(errMsg)
			return
		}

		startNum = startNum - 1
		endNum = endNum - 1
		// 选择行的数据
		if isRow {
			// 开始列名、结束列
			var startColumn, endColumn int
			if isAll {
				// 结束列（其实也就是整列的个数）
				endColumn = len(sheetInfo.Cols) - 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 := len(sheetInfo.Cols)
			if endColumn > maxCol {
				endColumn = maxCol - 1
			}
			// 长度固定，避免一直申请内存空间
			dataList = make([]string, endColumn-startColumn+1)
			i := 0
			for currColumn := startColumn; currColumn <= endColumn; currColumn++ {
				currCell := sheetInfo.Cell(startNum, currColumn)
				if currCell == nil {
					errMsg = fmt.Sprintf("第%d列,第%d行数据异常", startColumn, startNum)
					err = errors.New(errMsg)
					return
				}
				//dataList = append(dataList, currCell.Value)
				dataList[i] = currCell.Value
				i++
			}

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

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

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

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

	}

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

	// 将excel中的日期、数据系列处理
	newDateList, newDataList, err, errMsg = HandleEdbSequenceVal(dateList, dataList)

	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 是否选择列数据
// @return err error 错误信息
// @return errMsg string 错误信息
func GetSheetStr(sequenceStr string) (sheetName, startColumnName, endColumnName string, startNum, endNum int, isAll, isRow, isColumn bool, err error, errMsg string) {
	// 找出sheetName
	tmpList := strings.Split(sequenceStr, "!")
	if len(tmpList) != 2 {
		errMsg = "错误的公式，查找sheet异常：" + sequenceStr
		err = errors.New(errMsg)
		return
	}

	sheetName = tmpList[0]

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

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

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

	if lenList != 3 && lenList != 2 {
		errMsg = "错误的公式：" + sequenceStr
		err = errors.New(errMsg)
		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 && errors.Is(tmpErr1, tmpErr2) {
		err = errors.New("错误的公式3：" + sequenceStr)
		return
	}

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

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

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

	return
}