eta_data_analysis/services/ruizide/supply_revision_analysis_processor.go

package ruizide

import (
	"encoding/json"
	"eta/eta_data_analysis/models"
	"eta/eta_data_analysis/utils"
	"fmt"
	"github.com/shopspring/decimal"
	"github.com/xuri/excelize/v2"
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"time"
)

const (
	CubeDashboards         = "cube dashboards"
	SupplyRevisionAnalysis = "Supply Revision Analysis"
	BeginRow               = 1
	Rows                   = 3
)

var (
	SupplyRevisionAnalysisConfigMap = map[string]SRAPConfig{
		"Chart 1": {
			Frequency:          "季度",
			Unit:               "千桶每天",
			IndexNameColSuffix: "Index",
			IndexNameColPrefix: "Index 7DMA",
			chartType:          "one",
			IndexRow:           2,
			DateRow:            1,
			DateType:           "quarter",
		},
		"Chart 2": {
			Frequency:          "季度",
			Unit:               "千桶每天",
			IndexNameColSuffix: "Index",
			IndexNameColPrefix: "Index 7DMA",
			chartType:          "two",
			NamePrevious:       "",
			NameCurrent:        "",
			CodePrevious:       "",
			CodeCurrent:        "",
			IndexRow:           2,
			DataPreviousRow:    4,
			DataCurrentRow:     5,
			DateRow:            1,
			DateType:           "quarter",
		},
		"Chart 3": {
			Frequency:          "年度",
			Unit:               "千桶每天",
			IndexNameColSuffix: "Index",
			IndexNameColPrefix: "Index 7DMA",
			chartType:          "one",
			IndexRow:           2,
			DateRow:            1,
			DateType:           "year",
		},
		"Chart 4": {
			Frequency:          "月度",
			Unit:               "千桶每天",
			IndexNameColSuffix: "Index",
			IndexNameColPrefix: "Index 7DMA",
			chartType:          "two",
			NamePrevious:       "",
			NameCurrent:        "",
			CodePrevious:       "",
			CodeCurrent:        "",
			IndexRow:           2,
			DataPreviousRow:    4,
			DataCurrentRow:     5,
			DateRow:            1,
			DateType:           "year",
		},
		"Chart 5": {
			Frequency:          "月度",
			Unit:               "千桶每天",
			IndexNameColSuffix: "Index",
			IndexNameColPrefix: "Index 7DMA",
			chartType:          "two",
			NamePrevious:       "",
			NameCurrent:        "",
			CodePrevious:       "",
			CodeCurrent:        "",
			IndexRow:           3,
			DataPreviousRow:    4,
			DataCurrentRow:     5,
			DateRow:            3,
			DateType:           "month",
		},
		"Chart 6": {
			Frequency:          "日度",
			Unit:               "千桶每天",
			IndexNameColSuffix: "Index",
			IndexNameColPrefix: "Index 7DMA",
			chartType:          "one",
			IndexRow:           3,
			DateRow:            1,
			DateType:           "month",
		},
	}
)

type SRAPConfig struct {
	Frequency          string
	Unit               string
	IndexNameColSuffix string
	IndexNameColPrefix string
	IndexRow           int
	DataPreviousRow    int
	DataCurrentRow     int
	DateRow            int
	DateType           string
	chartType          string
	NamePrevious       string
	NameCurrent        string
	CodePrevious       string
	CodeCurrent        string
}

type SupplyRevisionAnalysisProcessor struct {
}

func (p *SupplyRevisionAnalysisProcessor) Process(tableName string) (err error) {
	var fileName string
	// 解析表格
	fileName = tableName + "_" + utils.GetCurrentYearMonth() + ".xlsx"
	filePath := filepath.Join(excelDir, fileName)
	if _, err = os.Stat(filePath); os.IsNotExist(err) {
		utils.FileLog.Error("文件不存在: %v", err)
		return
	}
	// 打开 Excel 文件
	file, err := excelize.OpenFile(filePath)
	if err != nil {
		utils.FileLog.Error("无法打开 Excel 文件: %v", err)
		return
	}
	sheetNames := file.GetSheetList()
	classifyIds, err := dealClassify(CubeDashboards, []string{SupplyRevisionAnalysis})
	// 获取所有工作表
	if err != nil {
		utils.FileLog.Error("获取分类Id失败: %v", err)
		return
	}
	//按照sheet页来处理数据,一个sheet发送一次服务器
	for _, sheetName := range sheetNames {
		var indexMap = make(map[string]*models.IndexInfo)
		utils.FileLog.Info("读取工作表: %s\n", sheetName)
		// 获取工作表的最大行数
		dataRows, excelErr := file.GetRows(sheetName) // 直接获取所有行数据
		if excelErr != nil {
			utils.FileLog.Error("获取工作表数据时出错: %v", excelErr)
			continue
		}
		fmt.Printf("sheetName:%s,sheetConfig:%d", sheetName, len(dataRows))
		sheetConfig := SupplyRevisionAnalysisConfigMap[sheetName]
		// 匹配非数字字符
		classifyId := classifyIds[SupplyRevisionAnalysis]
		switch sheetConfig.chartType {
		case "one":
			indexMap, err = OneDataHandler(dataRows, sheetConfig, classifyId)
		case "two":
			indexMap, err = TwoDataHandler(dataRows, sheetConfig, classifyId)
		}
		utils.FileLog.Info("读取工作表完成:", sheetName)
		// 新增数据源指标数据
		if len(indexMap) > 0 {
			for _, index := range indexMap {
				pars := chunkIndexData(index, rzdBatchSize)
				for _, par := range pars {
					// 转换成json
					marshal, parseErr := json.Marshal(par)
					if parseErr != nil {
						utils.FileLog.Error("json.Marshal err: %v", parseErr)
						return parseErr
					}
					// 发送 HTTP POST 请求
					_, err = utils.HttpPostRequest(utils.EDB_LIB_URL+utils.ADD_BATCH_RZD_DATA, string(marshal), "application/json")
					if err != nil {
						utils.FileLog.Error("postEdbLib err: %v", err)
						return err
					}
				}
			}
		}
	}
	return
}
func OneDataHandler(dataRows [][]string, sheetConfig SRAPConfig, classifyId int) (indexMap map[string]*models.IndexInfo, err error) {
	for _, rowData := range dataRows[BeginRow:] {
		// step_2: 指标
		// 指标名称
		indexNameOne := sheetName + "/" + rowData[sheetConfig.IndexRow] + "/" + sheetConfig.IndexNameColOne
		indexNameTwo := sheetName + "/" + rowData[sheetConfig.IndexRow] + "/" + sheetConfig.IndexNameColTwo
		// 生成指标编码
		indexCodeOne := getIndexId(sheetName, strings.ReplaceAll(strings.ToLower(rowData[len(rowData)-3]), " ", ""), sheetConfig.IndexNameColOne)
		indexCodeTwo := getIndexId(sheetName, strings.ReplaceAll(strings.ToLower(rowData[len(rowData)-3]), " ", ""), sheetConfig.IndexNameColTwo)
		dataTime := rowData[sheetConfig.DateRow]
		date, _, convertErr := utils.ConvertDateFormat(dataTime)
		if convertErr != nil {
			utils.FileLog.Error(fmt.Sprintf("转换时间数据失败,index_code:%s,time_value:%s err:%v", indexCodeOne, dataTime, convertErr))
			continue
		}
		if rowData[sheetConfig.DataOneRow] != "" && re.MatchString(strings.TrimSpace(rowData[sheetConfig.DataOneRow])) {
			valueOne, parseErr := decimal.NewFromString(strings.ReplaceAll(rowData[sheetConfig.DataOneRow], ",", ""))
			if parseErr != nil {
				utils.FileLog.Error(fmt.Sprintf("转换data数据失败,index_code:%s,data_value:%s err:%v", indexCodeOne, rowData[sheetConfig.DataOneRow], err))
				continue
			} else {
				if index, ok := indexMap[indexCodeOne]; ok {
					if index.StartDate.After(date) {
						index.StartDate = date
					}
					if index.EndDate.Before(date) {
						index.EndDate = date
						index.LatestValue = valueOne
					}
					index.DataList = append(index.DataList, models.IndexData{
						DataTime: date,
						Value:    valueOne,
					})
				} else {
					indexMap[indexCodeOne] = &models.IndexInfo{
						IndexName:   indexNameOne,
						IndexCode:   indexCodeOne,
						Frequency:   sheetConfig.Frequency,
						Unit:        sheetConfig.Unit,
						StartDate:   date,
						EndDate:     date,
						LatestValue: valueOne,
						ClassifyId:  classifyId,
						DataList: []models.IndexData{
							{
								DataTime: date,
								Value:    valueOne,
							},
						},
					}
				}
			}
		}
		if rowData[sheetConfig.DataTwoRow] != "" && re.MatchString(strings.TrimSpace(rowData[sheetConfig.DataTwoRow])) {
			valueTwo, parseErr := decimal.NewFromString(strings.ReplaceAll(rowData[sheetConfig.DataTwoRow], ",", ""))
			if parseErr != nil {
				utils.FileLog.Error(fmt.Sprintf("转换data数据失败,index_code:%s,data_value:%s err:%v", indexCodeTwo, rowData[sheetConfig.DataTwoRow], err))
				continue
			} else {
				if index, ok := indexMap[indexCodeTwo]; ok {
					if index.StartDate.After(date) {
						index.StartDate = date
					}
					if index.EndDate.Before(date) {
						index.EndDate = date
						index.LatestValue = valueTwo
					}
					index.DataList = append(index.DataList, models.IndexData{
						DataTime: date,
						Value:    valueTwo,
					})
				} else {
					indexMap[indexCodeTwo] = &models.IndexInfo{
						IndexName:   indexNameTwo,
						IndexCode:   indexCodeTwo,
						Frequency:   sheetConfig.Frequency,
						Unit:        sheetConfig.Unit,
						StartDate:   date,
						EndDate:     date,
						LatestValue: valueTwo,
						ClassifyId:  classifyId,
						DataList: []models.IndexData{
							{
								DataTime: date,
								Value:    valueTwo,
							},
						},
					}
				}
			}
		}
	}
}
func TwoDataHandler(dataRows [][]string, sheetConfig SRAPConfig, classifyId int) (indexMap map[string]*models.IndexInfo, err error) {
	for _, rowData := range dataRows[BeginRow:] {
		// 匹配非数字字符
		re := regexp.MustCompile(`^\d*\.?\d*\s*$`)
		//处理数据不全的情况
		less := Rows - len(rowData)
		if less > 0 {
			for i := 0; i < less; i++ {
				rowData = append(rowData, "")
			}
		}
		// step_2: 指标
		// 指标名称
		indexNameCurrent := SupplyRevisionAnalysis + "/" + sheetConfig.IndexNameColPrefix + "/" + sheetConfig.NameCurrent
		indexNamePrevious := SupplyRevisionAnalysis + "/" + sheetConfig.IndexNameColPrefix + "/" + sheetConfig.NamePrevious

		// 生成指标编码
		indexCodeCurrent := getIndexId(fmt.Sprintf("%s %s", SupplyRevisionAnalysis, sheetConfig.IndexNameColPrefix), sheetConfig.CodeCurrent, "")
		indexCodePrevious := getIndexId(fmt.Sprintf("%s %s", SupplyRevisionAnalysis, sheetConfig.IndexNameColPrefix), sheetConfig.CodePrevious, "")

		dataTime := rowData[sheetConfig.DateRow]
		var date time.Time
		var convertErr error
		switch sheetConfig.DateType {
		case "quarter":
			date, _, convertErr = utils.ConvertDateFormatQuarter(dataTime)
		case "month":
			date, _, convertErr = utils.ConvertDateFormat(dataTime)
		case "year":
			date, _, convertErr = utils.ConvertDateFormat(dataTime)
		}
		if convertErr != nil {
			utils.FileLog.Error(fmt.Sprintf("转换时间数据失败,index_code:%s,time_value:%s err:%v", indexNameCurrent, dataTime, convertErr))
			continue
		}

		if rowData[sheetConfig.DataCurrentRow] != "" && re.MatchString(strings.TrimSpace(rowData[sheetConfig.DataCurrentRow])) {
			valueCurrent, parseErr := decimal.NewFromString(rowData[sheetConfig.DataCurrentRow])
			if parseErr != nil {
				utils.FileLog.Error(fmt.Sprintf("转换data数据失败,index_code:%s,data_value:%s err:%v", indexCodeCurrent, rowData[sheetConfig.DataCurrentRow], err))
				continue
			} else {
				if index, ok := indexMap[indexCodeCurrent]; ok {
					if index.StartDate.After(date) {
						index.StartDate = date
					}
					if index.EndDate.Before(date) {
						index.EndDate = date
						index.LatestValue = valueCurrent
					}
					index.DataList = append(index.DataList, models.IndexData{
						DataTime: date,
						Value:    valueCurrent,
					})
				} else {
					indexMap[indexCodeCurrent] = &models.IndexInfo{
						IndexName:   indexNameCurrent,
						IndexCode:   indexCodeCurrent,
						Frequency:   sheetConfig.Frequency,
						Unit:        sheetConfig.Unit,
						StartDate:   date,
						EndDate:     date,
						LatestValue: valueCurrent,
						ClassifyId:  classifyId,
						DataList: []models.IndexData{
							{
								DataTime: date,
								Value:    valueCurrent,
							},
						},
					}
				}
			}
		}
		if rowData[sheetConfig.DataPreviousRow] != "" && re.MatchString(strings.TrimSpace(rowData[sheetConfig.DataPreviousRow])) {
			valuePrevious, parseErr := decimal.NewFromString(rowData[sheetConfig.DataPreviousRow])
			if parseErr != nil {
				utils.FileLog.Error(fmt.Sprintf("转换data数据失败,index_code:%s,data_value:%s err:%v", indexCodePrevious, rowData[sheetConfig.DataPreviousRow], err))
				continue
			} else {
				if index, ok := indexMap[indexCodePrevious]; ok {
					if index.StartDate.After(date) {
						index.StartDate = date
					}
					if index.EndDate.Before(date) {
						index.EndDate = date
						index.LatestValue = valuePrevious
					}
					index.DataList = append(index.DataList, models.IndexData{
						DataTime: date,
						Value:    valuePrevious,
					})
				} else {
					indexMap[indexCodePrevious] = &models.IndexInfo{
						IndexName:   indexNamePrevious,
						IndexCode:   indexCodePrevious,
						Frequency:   sheetConfig.Frequency,
						Unit:        sheetConfig.Unit,
						StartDate:   date,
						EndDate:     date,
						LatestValue: valuePrevious,
						ClassifyId:  classifyId,
						DataList: []models.IndexData{
							{
								DataTime: date,
								Value:    valuePrevious,
							},
						},
					}
				}
			}
		}
	}
}