mysteel_watch/services/index_merge.go

package services

import (
	"encoding/json"
	"fmt"
	"hongze/mysteel_watch/global"
	"hongze/mysteel_watch/models/index"
	"hongze/mysteel_watch/utils"
	"strings"
	"sync"
	"time"

	"github.com/xuri/excelize/v2"
)

//合并指标
func Merge() {
	fmt.Println("merge start")
	/*
		年底，季度，存放在一个excel中
		月度30个指标存一个excel
		周度25个指标存放一个excel
		日度20个指标存一个excel
	*/
	//年度->38
	//IndexYearMerge()
	//季度->5
	//IndexSeasonMerge()
	//月度->86
	//周度->292
	//日度->114
	//merge_file_path
	fmt.Println("merge end")
}

//年度
func IndexYearMerge() {
	frequency := "年度"
	fileName := "year" + ".xlsx" //保存的文件名称
	yearIndexFilePath := utils.IndexMsergeSaveDir + fileName

	var err error

	indexObj := new(index.BaseFromMysteelChemicalIndex)
	yearList, err := indexObj.GetIndexByFrequency(frequency)
	if err != nil {
		fmt.Println("GetIndexByFrequency Err:" + err.Error())
		return
	}

	commentResult, err := GetIndexComment(yearIndexFilePath, yearList)
	if err != nil {
		fmt.Println("GetIndexComment Err:" + err.Error())
		return
	}
	if utils.FileIsExist(yearIndexFilePath) { //修改文件
		fileObj, err := excelize.OpenFile(yearIndexFilePath)
		if err != nil {
			fmt.Println("打开文件失败,Err:" + err.Error())
			return
		}

		fileObj.DeleteComment("Sheet1", "A1")
		fileObj.AddComment("Sheet1", "A1", commentResult)
		if err := fileObj.SaveAs(yearIndexFilePath); err != nil {
			fmt.Println("保存失败,Err:" + err.Error())
			fileObj.Close()
			return
		}
		fileObj.Close()
	} else { //新增文件
		templatePath := utils.IndexSaveDir + "index_template.xlsx"
		templateFile, err := excelize.OpenFile(templatePath)
		if err != nil {
			fmt.Println("打开文件失败,Err:" + err.Error())
			return
		}
		templateFile.DeleteComment("Sheet1", "A1")
		templateFile.AddComment("Sheet1", "A1", commentResult)
		if err := templateFile.SaveAs(yearIndexFilePath); err != nil {
			fmt.Println("保存失败,Err:" + err.Error())
			templateFile.Close()
			return
		}
		templateFile.Close()
	}
	//处理excel数据
}

//季度
func IndexSeasonMerge() {
	frequency := "季度"
	fileName := "season" + ".xlsx" //保存的文件名称
	seasonIndexFilePath := utils.IndexMsergeSaveDir + fileName

	var err error

	indexObj := new(index.BaseFromMysteelChemicalIndex)
	seasonList, err := indexObj.GetIndexByFrequency(frequency)
	if err != nil {
		fmt.Println("GetIndexByFrequency Err:" + err.Error())
		return
	}

	commentResult, err := GetIndexComment(seasonIndexFilePath, seasonList)
	if err != nil {
		fmt.Println("GetIndexComment Err:" + err.Error())
		return
	}
	if utils.FileIsExist(seasonIndexFilePath) { //修改文件
		fileObj, err := excelize.OpenFile(seasonIndexFilePath)
		if err != nil {
			fmt.Println("打开文件失败,Err:" + err.Error())
			return
		}

		fileObj.DeleteComment("Sheet1", "A1")
		fileObj.AddComment("Sheet1", "A1", commentResult)
		if err := fileObj.SaveAs(seasonIndexFilePath); err != nil {
			fmt.Println("保存失败,Err:" + err.Error())
			fileObj.Close()
			return
		}
		fileObj.Close()
	} else { //新增文件
		templatePath := utils.IndexSaveDir + "index_template.xlsx"
		templateFile, err := excelize.OpenFile(templatePath)
		if err != nil {
			fmt.Println("打开文件失败,Err:" + err.Error())
			return
		}
		templateFile.DeleteComment("Sheet1", "A1")
		templateFile.AddComment("Sheet1", "A1", commentResult)
		if err := templateFile.SaveAs(seasonIndexFilePath); err != nil {
			fmt.Println("保存失败,Err:" + err.Error())
			templateFile.Close()
			return
		}
		templateFile.Close()
	}
	//处理excel数据
}

func GetIndexComment(yearIndexFilePath string, list []*index.BaseFromMysteelChemicalIndex) (commentResult string, err error) {
	indexInfo := new(IndexObj)
	if utils.FileIsExist(yearIndexFilePath) { //文件存在
		fmt.Println("utils.FileIsExist")
		getCommentStr := GetComment(yearIndexFilePath)
		fmt.Println(getCommentStr)

		err = json.Unmarshal([]byte(getCommentStr), &indexInfo)
		if err != nil {
			fmt.Println("json.Unmarshal err:" + err.Error())
			return
		}
	} else { //文件不存在，
		indexInfo.BlankValue = "0"
		indexInfo.CanMark = true
		indexInfo.ChartLineType = "0"
		indexInfo.DateBlock = 0
		indexInfo.DateBlockCount = 1
		indexInfo.DateFormat = 0
		indexInfo.DateTimeTag = "637973605613980000"
		indexInfo.EndDate = ""
		indexInfo.ExportType = 0
		indexInfo.HasDescription = true
		indexInfo.HasEmptyRows = false
		indexInfo.HasFrequency = true
		indexInfo.HasIndexID = true
		indexInfo.HasLastDate = true
		indexInfo.HasSourceName = true
		indexInfo.HasTimeInterval = true
		indexInfo.HasUnit = true
		indexInfo.HasUpdateDate = true
		indexInfo.IsCreateChart = false
		indexInfo.IsDataSort = true
		indexInfo.IsNewSheet = false
		indexInfo.IsNewWorkbook = false
		indexInfo.Position = "A1"
		indexInfo.ShowBlankLines = false
		indexInfo.StartDate = ""
		indexInfo.Transpose = false
		indexInfo.UpdateMode = 1
		indexInfo.LookModel.IsLast = false
		indexInfo.LookModel.LookValue = 0
		indexInfo.LookModel.LookType = 0
		indexInfo.Ver = 3
	}
	modelsList := make([]IndexModels, 0)
	startDate := "1990-01-01"

	for k, v := range list {
		fmt.Println(k, v)
		item := new(IndexModels)
		item.DataFormat = 0
		if v.IndexName == "" {
			item.DataStartDate = startDate
		} else {
			item.DataStartDate = v.StartDate.Format(utils.FormatDate)
		}
		item.DefineName = ""
		item.DefineUnit = ""
		item.DisplayIndexCode = v.IndexCode
		item.IndexCode = v.IndexCode
		item.IndexFormula = v.IndexCode
		item.PointValue = 0
		item.UnionStart = ""
		modelsList = append(modelsList, *item)
	}

	indexInfo.Models = modelsList

	indexStr, err := json.Marshal(indexInfo)
	if err != nil {
		fmt.Println("json.Marshal err:" + err.Error())
		return
	}
	text := string(indexStr)
	text = strings.Trim(text, "{")

	commentMap := make(map[string]interface{})
	commentMap["author"] = "{"
	commentMap["text"] = text
	//commentMap["text"] = commentItem

	commentJson, err := json.Marshal(commentMap)
	if err != nil {
		fmt.Println("json.Marshal err:" + err.Error())
	}
	commentResult = string(commentJson)
	return
}

type IndexObj struct {
	BlankValue      string        `json:"BlankValue"`
	CanMark         bool          `json:"CanMark"`
	ChartLineType   string        `json:"ChartLineType"`
	DateBlock       int64         `json:"DateBlock"`
	DateBlockCount  int64         `json:"DateBlockCount"`
	DateFormat      int64         `json:"DateFormat"`
	DateTimeTag     string        `json:"DateTimeTag"`
	EndDate         string        `json:"EndDate"`
	ExportType      int64         `json:"ExportType"`
	HasDescription  bool          `json:"HasDescription"`
	HasEmptyRows    bool          `json:"HasEmptyRows"`
	HasFrequency    bool          `json:"HasFrequency"`
	HasIndexID      bool          `json:"HasIndexID"`
	HasLastDate     bool          `json:"HasLastDate"`
	HasSourceName   bool          `json:"HasSourceName"`
	HasTimeInterval bool          `json:"HasTimeInterval"`
	HasUnit         bool          `json:"HasUnit"`
	HasUpdateDate   bool          `json:"HasUpdateDate"`
	IsCreateChart   bool          `json:"IsCreateChart"`
	IsDataSort      bool          `json:"IsDataSort"`
	IsNewSheet      bool          `json:"IsNewSheet"`
	IsNewWorkbook   bool          `json:"IsNewWorkbook"`
	Models          []IndexModels `json:"Models"`
	Position        string        `json:"Position"`
	RangeData       string        `json:"RangeData"`
	ShowBlankLines  bool          `json:"ShowBlankLines"`
	StartDate       string        `json:"StartDate"`
	Transpose       bool          `json:"Transpose"`
	UpdateMode      int64         `json:"UpdateMode"`
	LookModel       struct {
		IsLast    bool  `json:"IsLast"`
		LookValue int64 `json:"LookValue"`
		LookType  int64 `json:"lookType"`
	} `json:"lookModel"`
	Ver int64 `json:"ver"`
}

type IndexModels struct {
	DataFormat       int64  `json:"DataFormat"`
	DataStartDate    string `json:"DataStartDate"`
	DefineName       string `json:"DefineName"`
	DefineUnit       string `json:"DefineUnit"`
	DisplayIndexCode string `json:"DisplayIndexCode"`
	IndexCode        string `json:"IndexCode"`
	IndexFormula     string `json:"IndexFormula"`
	PointValue       int64  `json:"PointValue"`
	UnionStart       string `json:"UnionStart"`
}

func DataAnalysis(filePath string) {

	runMode := "debug"
	//runMode = "release"

	fmt.Println("filePath:", filePath)
	time.Sleep(1 * time.Second)
	if !utils.FileIsExist(filePath) {
		fmt.Println("filePath is not exist:" + filePath)
		return
	}
	//读取文件内容
	global.LOG.Info("WatchFile:" + filePath)
	f, err := excelize.OpenFile(filePath)
	global.LOG.Info("OpenFile:" + filePath)
	if err != nil {
		fmt.Println("OpenFile:" + filePath + ",Err:" + err.Error())
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println("FileClose Err:" + err.Error())
			return
		}
	}()

	indexObj := new(index.BaseFromMysteelChemicalIndex)
	var wg = sync.WaitGroup{}
	wg.Add(1)
	go func() {
		sheetList := f.GetSheetList()
		for _, sv := range sheetList {
			rows, err := f.GetRows(sv)
			if err != nil {
				fmt.Println("f.GetRows:err:" + err.Error())
				return
			}
			var nameArr []string
			unitArr := make([]string, 0)
			sourceArr := make([]string, 0)
			codeArr := make([]string, 0)
			frequencyArr := make([]string, 0)
			dateArr := make([]string, 0)
			describeArr := make([]string, 0)
			endDateArr := make([]string, 0)
			updateArr := make([]string, 0)
			indexDateArr := make([]string, 0)

			dataMap := make(map[string][]string)

			var nameLen int
			for rk, row := range rows {
				if rk == 0 {
					continue
				}
				if rk == 1 {
					for ck, colCell := range row {
						if ck >= 1 {
							nameArr = append(nameArr, colCell)
						}
					}
					nameLen = len(nameArr)
					unitArr = make([]string, nameLen)
					sourceArr = make([]string, nameLen)
					codeArr = make([]string, nameLen)
					frequencyArr = make([]string, nameLen)
					dateArr = make([]string, nameLen)
					describeArr = make([]string, nameLen)
					endDateArr = make([]string, nameLen)
					updateArr = make([]string, nameLen)
				} else if rk == 2 {
					for ck, colCell := range row {
						if ck >= 1 {
							unitArr[ck-1] = colCell
						}
					}
				} else if rk == 3 {
					for ck, colCell := range row {
						if ck >= 1 {
							//sourceArr = append(sourceArr, colCell)
							sourceArr[ck-1] = colCell
						}
					}
				} else if rk == 4 {
					for ck, colCell := range row {
						if ck >= 1 {
							//codeArr = append(codeArr, colCell)
							codeArr[ck-1] = colCell
						}
					}
				} else if rk == 5 {
					for ck, colCell := range row {
						if ck >= 1 {
							//frequencyArr = append(frequencyArr, colCell)
							frequencyArr[ck-1] = colCell
						}
					}
				} else if rk == 6 {
					for ck, colCell := range row {
						if ck >= 1 {
							//dateArr = append(dateArr, colCell)
							dateArr[ck-1] = colCell
						}
					}
				} else if rk == 7 {
					for ck, colCell := range row {
						if ck >= 1 {
							//describeArr = append(describeArr, colCell)
							describeArr[ck-1] = colCell
						}
					}
				} else if rk == 8 {
					for ck, colCell := range row {
						if ck >= 1 {
							//endDateArr = append(endDateArr, colCell)
							endDateArr[ck-1] = colCell
						}
					}
				} else if rk == 9 {
					for ck, colCell := range row {
						if ck >= 1 {
							//updateArr = append(updateArr, colCell)
							updateArr[ck-1] = colCell
						}
					}
				} else {
					var date string
					dataArr := make([]string, nameLen)
					for ck, colCell := range row {
						if ck == 0 {
							date = colCell
							indexDateArr = append(indexDateArr, date)
						} else {
							if colCell != "" {
								dataArr[ck-1] = colCell
							}
						}
					}
					dataMap[date] = dataArr
				}
			}

			var indexId int64
			existDataMap := make(map[string]string)
			for k, v := range nameArr {
				indexName := v
				indexCode := codeArr[k]
				unit := unitArr[k]
				source := sourceArr[k]
				describe := describeArr[k]
				dateStr := dateArr[k]
				frequency := frequencyArr[k]
				//判断指标是否存在
				var isAdd int
				item, err := indexObj.GetIndexItem(runMode, indexCode)
				if err != nil {
					if err.Error() == "record not found" {
						isAdd = 1
					} else {
						isAdd = -1
						fmt.Println("GetIndexItem Err:" + err.Error())
						return
					}
				}
				if item != nil && item.BaseFromMysteelChemicalIndexId > 0 {
					fmt.Println("item:", item)
					isAdd = 2
				} else {
					isAdd = 1
				}

				var startDate, endDate string
				startEndArr := strings.Split(dateStr, "～")
				if len(startEndArr) >= 2 {
					startDate = startEndArr[0]
					endDate = startEndArr[1]
				}

				if !strings.Contains(frequency, "度") {
					frequency = frequency + "度"
				}

				indexItem := new(index.BaseFromMysteelChemicalIndex)
				if isAdd == 1 {
					indexItem.IndexCode = indexCode
					indexItem.IndexName = indexName
					indexItem.Unit = unit
					indexItem.Source = source
					indexItem.Describe = describe
					indexItem.StartDate, _ = time.ParseInLocation(utils.FormatDate, startDate, time.Local)
					indexItem.EndDate, _ = time.ParseInLocation(utils.FormatDate, endDate, time.Local)
					indexItem.Frequency = frequency
					indexItem.FilePath = filePath
					indexItem.MergeFilePath = filePath
					err = indexItem.Add(runMode)
					if err != nil {
						fmt.Println("indexItem.add err:" + err.Error())
						return
					}
					indexId = indexObj.BaseFromMysteelChemicalIndexId
				} else if isAdd == 2 {
					indexItem.IndexCode = indexCode
					indexItem.IndexName = indexName
					indexItem.Unit = unit
					indexItem.Source = source
					indexItem.Describe = describe
					indexItem.StartDate, _ = time.ParseInLocation(utils.FormatDate, startDate, time.Local)
					indexItem.EndDate, _ = time.ParseInLocation(utils.FormatDate, endDate, time.Local)
					indexItem.Frequency = frequency
					indexItem.FilePath = filePath
					indexItem.ModifyTime = time.Now()
					indexId = item.BaseFromMysteelChemicalIndexId
					//修改数据
					updateColsArr := make([]string, 0)
					updateColsArr = append(updateColsArr, "index_name")
					updateColsArr = append(updateColsArr, "unit")
					updateColsArr = append(updateColsArr, "source")
					updateColsArr = append(updateColsArr, "frequency")
					updateColsArr = append(updateColsArr, "start_date")
					updateColsArr = append(updateColsArr, "end_date")
					updateColsArr = append(updateColsArr, "describe")
					updateColsArr = append(updateColsArr, "end_date")
					updateColsArr = append(updateColsArr, "modify_time")
					updateColsArr = append(updateColsArr, "file_path")

					err = indexItem.Update(runMode, updateColsArr)
					if err != nil {
						fmt.Println("indexObj.Update err:" + err.Error())
						return
					}

					dataObj := new(index.BaseFromMysteelChemicalData)
					//获取已存在的所有数据
					dataList, err := dataObj.GetIndexDataList(runMode, indexCode)
					if err != nil {
						fmt.Println("GetIndexDataList Err:" + err.Error())
						return
					}
					fmt.Println("dataListLen:", len(dataList))
					for _, v := range dataList {
						dateStr := v.DataTime.Format(utils.FormatDate)
						existDataMap[dateStr] = v.Value
					}
				}

				dataList := make([]index.BaseFromMysteelChemicalData, 0)
				for _, dv := range indexDateArr {
					dataArr := dataMap[dv]
					dataVal := dataArr[k]
					updateDate := updateArr[k]
					if dataVal != "" {
						if _, ok := existDataMap[dv]; !ok {
							dateTime, err := time.ParseInLocation(utils.FormatDate, dv, time.Local)
							if err != nil {
								fmt.Println("time.ParseInLocation Err:" + err.Error())
								return
							}
							dataItem := new(index.BaseFromMysteelChemicalData)
							dataItem.BaseFromMysteelChemicalIndexId = indexId
							dataItem.IndexCode = indexCode
							dataItem.DataTime = dateTime
							dataItem.Value = dataVal
							dataItem.UpdateDate = updateDate
							dataItem.CreateTime = time.Now()
							dataItem.ModifyTime = time.Now()
							dataList = append(dataList, *dataItem)
						}
					}
				}
				if len(dataList) > 0 {
					dataObj := new(index.BaseFromMysteelChemicalData)
					err = dataObj.Add(runMode, dataList)
					if err != nil {
						fmt.Println("dataObj.Add() Err:" + err.Error())
						return
					}
				}
			}
		}
		wg.Done()
	}()
	wg.Wait()
}