package base_from_yongyi_v2 import ( "eta/eta_data_analysis/models" "eta/eta_data_analysis/utils" "fmt" "regexp" "strconv" "strings" "time" "github.com/xuri/excelize/v2" ) // HandleYongyiExcelWeekly41 月度-小猪(50斤以下)存栏 func HandleYongyiExcelWeekly41(f *excelize.File, sheetName string) (indexList []*models.YongyiExcelIndex, err error) { defer func() { if err != nil { utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly40 国产冻品2-4号肉价格 ErrMsg: %s", err.Error())) } }() cols, err := f.GetCols(sheetName) if err != nil { err = fmt.Errorf("f GetCols err: %s", err.Error()) return } classifyName, classifySort, frequency, unit, namePrefix, namePrefixPingin := GetBaseInfo(sheetName) sort := 0 var preIndexName string dateIndexList := make([]string, 0) indexInfoMap := make(map[string]*models.YongyiExcelIndex) indexNamePart2 := map[string]struct{}{"全国": {}, "华北": {}, "东北": {}, "华中": {}, "华东": {}, "华南": {}, "西南": {}} for i, col := range cols { if len(col) <= 3 { continue } if i == 0 && col[1] == "日期" { for _, cell := range col { tmpDateTime, err := time.Parse("2006年1月", cell) if err != nil { dateIndexList = append(dateIndexList, "") } else { dateTime := LastDayOfMonth(tmpDateTime.Year(), tmpDateTime.Month()) dateIndexList = append(dateIndexList, dateTime) } } } if len(dateIndexList) == 0 { continue } if col[1] != "" && col[1] != "日期" { var indexName, indexNameSuffix string if _, ok := indexNamePart2[col[1]]; ok { preIndexName = col[1] indexName = fmt.Sprintf("%s/%s", namePrefix, col[1]) indexNameSuffix = col[1] } else if col[1] == "环比" { indexName = fmt.Sprintf("%s(%s)/%s", namePrefix, col[1], preIndexName) indexNameSuffix = fmt.Sprintf("(%s)%s", col[1], preIndexName) } else { continue } for j, cell := range col { if !isValueValid(cell) { continue } if indexInfo, ok := indexInfoMap[indexName]; !ok { tmpYongyiIndex := new(models.YongyiExcelIndex) tmpYongyiIndex.ClassifyName = classifyName tmpYongyiIndex.ClassifySort = classifySort tmpYongyiIndex.Frequency = frequency tmpYongyiIndex.Unit = unit tmpYongyiIndex.IndexName = indexName tmpYongyiIndex.IndexCode = namePrefixPingin + utils.GetFirstPingYin(indexNameSuffix) tmpYongyiIndex.Sort = sort tmpYongyiIndex.ExcelDataMap = make(map[string]string) if string(cell[len(cell)-1]) == `%` { tmpYongyiIndex.Unit = `%` tmpYongyiIndex.ExcelDataMap[dateIndexList[j]] = cell[:len(cell)-1] } else { tmpYongyiIndex.ExcelDataMap[dateIndexList[j]] = cell } indexInfoMap[indexName] = tmpYongyiIndex indexList = append(indexList, tmpYongyiIndex) sort++ } else { if string(cell[len(cell)-1]) == `%` { indexInfo.ExcelDataMap[dateIndexList[j]] = cell[:len(cell)-1] } else { indexInfo.ExcelDataMap[dateIndexList[j]] = cell } } } } } utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly41 %s 更新指标数量:%d", sheetName, len(indexList))) return } // HandleYongyiExcelWeekly42 月度猪肉供应占比 func HandleYongyiExcelWeekly42(f *excelize.File, sheetName string) (indexList []*models.YongyiExcelIndex, err error) { defer func() { if err != nil { utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly40 月度猪肉供应占比 ErrMsg: %s", err.Error())) } }() rows, err := f.GetRows(sheetName) if err != nil { err = fmt.Errorf("f GetRows err: %s", err.Error()) return } classifyName, classifySort, frequency, unit, namePrefix, namePrefixPingin := GetBaseInfo(sheetName) re := regexp.MustCompile(`[+-]?[0-9]*\.?[0-9]+`) yearIndexMap := make(map[int]string) yongyiExcelIndex := new(models.YongyiExcelIndex) yongyiExcelIndex.ClassifyName = classifyName yongyiExcelIndex.ClassifySort = classifySort yongyiExcelIndex.Frequency = frequency yongyiExcelIndex.Unit = unit yongyiExcelIndex.IndexName = namePrefix yongyiExcelIndex.IndexCode = namePrefixPingin yongyiExcelIndex.Sort = 0 yongyiExcelIndex.ExcelDataMap = make(map[string]string) indexList = append(indexList, yongyiExcelIndex) for i, row := range rows { if i == 0 { // 记录年份索引 for j, cell := range row { if isValueValid(cell) { yearIndexMap[j] = cell } } } else { var month string for j, cell := range row { if j == 0 { if tmp := re.FindString(cell); tmp != "" { month = tmp continue } } if month == "" { break } tmpYear, err := strconv.Atoi(yearIndexMap[j]) if err != nil { continue } tmpMonth, err := strconv.Atoi(month) if err != nil { continue } dataTime := LastDayOfMonth(tmpYear, time.Month(tmpMonth)) if isValueValid(cell) { newCell := strings.ReplaceAll(cell, "%", "") yongyiExcelIndex.ExcelDataMap[dataTime] = newCell } } } } utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly42 %s 更新指标数量:%d", sheetName, len(indexList))) return } // HandleYongyiExcelWeekly43 历史出栏体重 func HandleYongyiExcelWeekly43(f *excelize.File, sheetName string) (indexList []*models.YongyiExcelIndex, err error) { defer func() { if err != nil { utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly43 %s ErrMsg: %s", sheetName, err.Error())) } }() rows, err := f.GetRows(sheetName) if err != nil { err = fmt.Errorf("f GetRows err: %s", err.Error()) return } re := regexp.MustCompile(`\d+`) classifyName, classifySort, frequency, unit, namePrefix, namePrefixPingin := GetBaseInfo(sheetName) yongyiExcelIndex := new(models.YongyiExcelIndex) yongyiExcelIndex.ClassifyName = classifyName yongyiExcelIndex.ClassifySort = classifySort yongyiExcelIndex.Frequency = frequency yongyiExcelIndex.Unit = unit yongyiExcelIndex.IndexName = namePrefix yongyiExcelIndex.IndexCode = namePrefixPingin yongyiExcelIndex.Sort = 0 yongyiExcelIndex.ExcelDataMap = make(map[string]string) indexList = append(indexList, yongyiExcelIndex) for i, row := range rows { if i == 0 { continue } if len(row) < 2 { continue } dateArr := re.FindAllString(row[0], -1) year, err := strconv.Atoi(dateArr[0]) if err != nil { continue } week, err := strconv.Atoi(dateArr[1]) if err != nil { continue } if !isValueValid(row[1]) { continue } date := getSundayOfWeek(year, week) yongyiExcelIndex.ExcelDataMap[date] = row[1] } utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly43 %s 更新指标数量:%d", sheetName, len(indexList))) return } // HandleYongyiExcelWeekly44 重要部位冻品进口 func HandleYongyiExcelWeekly44(f *excelize.File, sheetName string) (indexList []*models.YongyiExcelIndex, err error) { defer func() { if err != nil { utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly44 %s ErrMsg: %s", sheetName, err.Error())) } }() cols, err := f.GetCols(sheetName) if err != nil { err = fmt.Errorf("f GetCols err: %s", err.Error()) return } classifyName, classifySort, frequency, _, namePrefix, namePrefixPingin := GetBaseInfo(sheetName) sort := 0 re := regexp.MustCompile(`\d+`) dateIndexMap := make(map[int]string) for i, col := range cols { if i == 0 { for j, cell := range col { dateArr := re.FindAllString(cell, -1) if len(dateArr) != 2 { continue } year, err := strconv.Atoi(dateArr[0]) if err != nil { continue } month, err := strconv.Atoi(dateArr[1]) if err != nil { continue } dateIndexMap[j] = LastDayOfMonth(year, time.Month(month)) } } else { if len(col) < 2 { continue } if col[1] == "" { continue } indexName := fmt.Sprintf("%s/%s", namePrefix, col[1]) yongyiExcelIndex := new(models.YongyiExcelIndex) yongyiExcelIndex.ClassifyName = classifyName yongyiExcelIndex.ClassifySort = classifySort yongyiExcelIndex.Frequency = frequency yongyiExcelIndex.IndexName = indexName yongyiExcelIndex.IndexCode = namePrefixPingin + utils.GetFirstPingYin(col[1]) yongyiExcelIndex.Sort = sort yongyiExcelIndex.ExcelDataMap = make(map[string]string) if col[1] == "柜数" { yongyiExcelIndex.Unit = "个" } else if col[1] == "合计" { yongyiExcelIndex.Unit = "kg" } else { continue } for j, cell := range col { if j == 0 { continue } cell = strings.ReplaceAll(cell, ",", "") if !isValueValid(cell) { continue } yongyiExcelIndex.ExcelDataMap[dateIndexMap[j]] = cell } indexList = append(indexList, yongyiExcelIndex) sort++ } } utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly44 %s 更新指标数量:%d", sheetName, len(indexList))) return } // HandleYongyiExcelWeekly45 各存栏规模 func HandleYongyiExcelWeekly45(f *excelize.File, sheetName string) (indexList []*models.YongyiExcelIndex, err error) { defer func() { if err != nil { utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly45 %s ErrMsg: %s", sheetName, err.Error())) } }() rows, err := f.GetRows(sheetName) if err != nil { err = fmt.Errorf("f GetRows err: %s", err.Error()) return } classifyName, classifySort, frequency, unit, namePrefix, namePrefixPingin := GetBaseInfo(sheetName) sort := 0 re := regexp.MustCompile(`\d+`) indexNamePart3 := make([]string, 0) indexInfoMap := make(map[string]*models.YongyiExcelIndex) for i, row := range rows { if i == 0 { continue } if i == 1 { for j, cell := range row { if j >= 2 { indexNamePart3 = append(indexNamePart3, cell) } else { indexNamePart3 = append(indexNamePart3, "") } } } if len(row) == 0 { break } if i > 1 && len(row) > 2 { var date string for j, cell := range row { if j == 0 { dateArr := re.FindAllString(cell, -1) if len(dateArr) != 2 { break } year, err := strconv.Atoi(dateArr[0]) if err != nil { break } month, err := strconv.Atoi(dateArr[1]) if err != nil { break } date = LastDayOfMonth(year, time.Month(month)) } if !isValueValid(cell) { continue } indexName := fmt.Sprintf("%s/%s/%s", namePrefix, row[1], indexNamePart3[j]) if indexInfo, ok := indexInfoMap[indexName]; !ok { tmpYongyiIndex := new(models.YongyiExcelIndex) tmpYongyiIndex.ClassifyName = classifyName tmpYongyiIndex.ClassifySort = classifySort tmpYongyiIndex.Frequency = frequency tmpYongyiIndex.Unit = unit tmpYongyiIndex.IndexName = indexName tmpYongyiIndex.IndexCode = namePrefixPingin + utils.GetFirstPingYin(row[1]+indexNamePart3[j]) tmpYongyiIndex.Sort = sort tmpYongyiIndex.ExcelDataMap = make(map[string]string) newCell := strings.ReplaceAll(cell, "%", "") tmpYongyiIndex.ExcelDataMap[date] = newCell indexInfoMap[indexName] = tmpYongyiIndex indexList = append(indexList, tmpYongyiIndex) sort++ } else { newCell := strings.ReplaceAll(cell, "%", "") indexInfo.ExcelDataMap[date] = newCell } } } } utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly45 %s 更新指标数量:%d", sheetName, len(indexList))) return } // HandleYongyiExcelWeekly46 进口肉 func HandleYongyiExcelWeekly46(f *excelize.File, sheetName string) (indexList []*models.YongyiExcelIndex, err error) { defer func() { if err != nil { utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly46 %s ErrMsg: %s", sheetName, err.Error())) } }() cols, err := f.GetCols(sheetName) if err != nil { err = fmt.Errorf("f GetCols err: %s", err.Error()) return } classifyName, classifySort, frequency, _, _, namePrefixPingin := GetBaseInfo(sheetName) sort := 0 var indexName string re := regexp.MustCompile(`\d+`) regZr := regexp.MustCompile(`\([^()]*\)`) zrzuDateMap := make(map[int]string) // 猪肉及杂碎进口量 indexInfoMap := make(map[string]*models.YongyiExcelIndex) for _, col := range cols { if indexName == "" { indexName = col[1] } if col[2] == "" && col[3] == "" { indexName = "" continue } if indexName != "" && strings.Contains(indexName, "猪肉进口量") { indexName = "猪肉进口量" var year int if strings.Contains(col[2], "年") { year, err = strconv.Atoi(re.FindString(col[2])) if err != nil { continue } } for j, cell := range col { if !isValueValid(cell) { continue } monthArr := strings.Split(cols[0][j], "-") var month int switch len(monthArr) { case 2: month, err = strconv.Atoi(re.FindString(monthArr[1])) if err != nil { continue } case 1: month, err = strconv.Atoi(re.FindString(monthArr[0])) if err != nil { continue } } date := LastDayOfMonth(year, time.Month(month)) if date == "" { continue } if indexInfo, ok := indexInfoMap[indexName]; !ok { tmpYongyiIndex := new(models.YongyiExcelIndex) tmpYongyiIndex.ClassifyName = classifyName tmpYongyiIndex.ClassifySort = classifySort tmpYongyiIndex.Frequency = frequency tmpYongyiIndex.Unit = "万吨" tmpYongyiIndex.IndexName = indexName tmpYongyiIndex.IndexCode = namePrefixPingin + utils.GetFirstPingYin(indexName) tmpYongyiIndex.Sort = sort tmpYongyiIndex.ExcelDataMap = make(map[string]string) tmpYongyiIndex.ExcelDataMap[date] = cell indexInfoMap[indexName] = tmpYongyiIndex indexList = append(indexList, tmpYongyiIndex) sort++ } else { indexInfo.ExcelDataMap[date] = cell } } } else if indexName != "" && strings.Contains(indexName, "猪肉及杂碎进口量") { preName := "猪肉及杂碎进口量" if len(col) <= 3 { continue } colTitleName := col[2] if colTitleName == `` { continue } if colTitleName == `日期` { for j, cell := range col { if cell == "" || j <= 2 { continue } excelDate, err := strconv.ParseFloat(cell, 64) if err != nil { continue } tmpDate, err := excelize.ExcelDateToTime(excelDate, false) if err != nil { continue } LastDayDate := LastDayOfMonth(tmpDate.Year(), tmpDate.Month()) zrzuDateMap[j] = LastDayDate } } else { unit := regZr.FindString(colTitleName) if unit == "" { unit = "%" } else { unit = strings.ReplaceAll(unit, "(", "") unit = strings.ReplaceAll(unit, ")", "") } namePart := regZr.ReplaceAllString(colTitleName, "") tmpIndexName := fmt.Sprintf("%s/%s", preName, namePart) if indexInfo, ok := indexInfoMap[tmpIndexName]; !ok { yongyiIndex := new(models.YongyiExcelIndex) yongyiIndex.ClassifyName = classifyName yongyiIndex.ClassifySort = classifySort yongyiIndex.Frequency = frequency yongyiIndex.Unit = unit yongyiIndex.IndexName = tmpIndexName tmpIndexName = strings.ReplaceAll(tmpIndexName, "/", "") yongyiIndex.IndexCode = namePrefixPingin + utils.GetFirstPingYin(tmpIndexName) yongyiIndex.Sort = sort yongyiIndex.ExcelDataMap = make(map[string]string) for j, cell := range col { if j == 0 { continue } cell = strings.ReplaceAll(cell, ",", "") if unit == "%" { cell = strings.ReplaceAll(cell, "%", "") } if !isValueValid(cell) { continue } yongyiIndex.ExcelDataMap[zrzuDateMap[j]] = cell } indexInfoMap[yongyiIndex.IndexName] = yongyiIndex indexList = append(indexList, yongyiIndex) sort++ } else { for j, cell := range col { if j == 0 { continue } cell = strings.ReplaceAll(cell, ",", "") if unit == "%" { cell = strings.ReplaceAll(cell, "%", "") } if !isValueValid(cell) { continue } indexInfo.ExcelDataMap[zrzuDateMap[j]] = cell } } } } } utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly46 %s 更新指标数量:%d", sheetName, len(indexList))) return } // HandleYongyiExcelWeekly47 猪料原料占比 func HandleYongyiExcelWeekly47(f *excelize.File, sheetName string) (indexList []*models.YongyiExcelIndex, err error) { defer func() { if err != nil { utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly47 %s ErrMsg: %s", sheetName, err.Error())) } }() rows, err := f.GetRows(sheetName) if err != nil { err = fmt.Errorf("f GetRows err: %s", err.Error()) return } classifyName, classifySort, frequency, unit, namePrefix, namePrefixPingin := GetBaseInfo(sheetName) namePart2Map := map[string]int{ "玉米": 0, "小麦": 0, "稻谷、糙米": 0, "豆粕": 0, "棉粕": 0, "菜粕": 0, } regionMap := map[string]struct{}{ "东北": {}, "华北": {}, "华中": {}, "华南": {}, "西北": {}, "西南": {}, "全国": {}, } regionIndexMap := make(map[int]string) indexInfoMap := make(map[string]*models.YongyiExcelIndex) sort := 0 for i, row := range rows { if i == 0 { continue } if len(row) <= 1 { continue } var preName string for j, cell := range row { if _, ok := namePart2Map[cell]; ok { namePart2Map[cell] = j preName = cell continue } if _, ok := regionMap[cell]; ok { regionIndexMap[j] = preName + "/" + cell continue } if region, ok := regionIndexMap[j]; ok { if !isValueValid(cell) { continue } kind := strings.Split(region, "/")[0] dateIndex := namePart2Map[kind] excelDate, err := strconv.ParseFloat(row[dateIndex], 64) if err != nil { continue } curTime, err := excelize.ExcelDateToTime(excelDate, false) if err != nil { continue } dateTime := LastDayOfMonth(curTime.Year(), curTime.Month()) indexName := fmt.Sprintf("%s/%s", namePrefix, region) if indexInfo, ok := indexInfoMap[indexName]; !ok { yongyiIndex := new(models.YongyiExcelIndex) yongyiIndex.ClassifyName = classifyName yongyiIndex.ClassifySort = classifySort yongyiIndex.Frequency = frequency yongyiIndex.Unit = unit yongyiIndex.IndexName = indexName tmpSuffix := strings.ReplaceAll(region, "/", "") tmpSuffix = strings.ReplaceAll(tmpSuffix, "、", "") yongyiIndex.IndexCode = namePrefixPingin + utils.GetFirstPingYin(tmpSuffix) yongyiIndex.Sort = sort yongyiIndex.ExcelDataMap = make(map[string]string) cell = strings.ReplaceAll(cell, "%", "") yongyiIndex.ExcelDataMap[dateTime] = cell indexInfoMap[indexName] = yongyiIndex indexList = append(indexList, yongyiIndex) sort++ } else { cell = strings.ReplaceAll(cell, "%", "") indexInfo.ExcelDataMap[dateTime] = cell } } } } utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly47 %s 更新指标数量:%d", sheetName, len(indexList))) return } // HandleYongyiExcelWeekly48 MSY func HandleYongyiExcelWeekly48(f *excelize.File, sheetName string) (indexList []*models.YongyiExcelIndex, err error) { defer func() { if err != nil { utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly48 %s ErrMsg: %s", sheetName, err.Error())) } }() cols, err := f.GetCols(sheetName) if err != nil { err = fmt.Errorf("f GetRows err: %s", err.Error()) return } classifyName, classifySort, frequency, unit, namePrefix, namePrefixPingin := GetBaseInfo(sheetName) re := regexp.MustCompile(`\d+`) dateMap := make(map[int]string) for _, col := range cols { if len(col) <= 3 { continue } if col[2] == "时间" { for j, cell := range col { year := re.FindString(cell) if year == "" { continue } dateMap[j] = year + "-12-31" } } else if col[2] == "MSY" { yongyiIndex := new(models.YongyiExcelIndex) yongyiIndex.ClassifyName = classifyName yongyiIndex.ClassifySort = classifySort yongyiIndex.Frequency = frequency yongyiIndex.Unit = unit yongyiIndex.IndexName = namePrefix yongyiIndex.IndexCode = namePrefixPingin yongyiIndex.Sort = 0 yongyiIndex.ExcelDataMap = make(map[string]string) for j, cell := range col { if !isValueValid(cell) { continue } yongyiIndex.ExcelDataMap[dateMap[j]] = cell } indexList = append(indexList, yongyiIndex) } } utils.FileLog.Info(fmt.Sprintf("HandleYongyiExcelWeekly48 %s 更新指标数量:%d", sheetName, len(indexList))) return }