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() }