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" ) func getClassifySortMap() map[string]int { classifyMap := map[string]int{ "日度-商品猪出栏价": 1, "日度-商品猪标肥价差": 2, "日度-商品猪全国均价和宰量": 3, "日度-屠宰企业屠宰量": 4, "历史猪价": 5, "周度-商品猪出栏价": 6, "周度-体重": 7, "周度-屠宰厂宰前活猪重": 8, "周度-各体重段价差": 9, "二育成本": 10, "二育销量": 11, "育肥栏舍利用率": 12, "周度-养殖利润最新": 13, "周度-当期、预期成本": 14, "周度-育肥全价料出厂价": 15, "周度-成本计算附件": 16, "周度-毛白价差": 17, "周度-50公斤二元母猪价格": 18, "周度-规模场15公斤仔猪出栏价": 19, "仔猪、母猪": 20, "周度-河南屠宰白条成本": 21, "周度-淘汰母猪价格": 22, "周度-宰后结算价": 23, "周度-冻品库存": 24, "周度-冻品库存多样本": 25, "周度-鲜销率": 26, "周度-猪肉价（前三等级白条均价）": 27, "周度-猪肉产品价格": 28, "周度-屠宰企业日度屠宰量": 29, "周度-屠宰新2022.10.28": 30, "月度出栏完成率": 31, "月度计划出栏量": 32, "月度-淘汰母猪屠宰厂宰杀量": 33, "月度-猪料销量": 34, "月度-屠宰企业开工率": 35, "月度-屠宰厂公母比例": 36, "月度-生产指标（2021.5.7新增）": 37, "月度-生产指标2": 38, "月度-二元三元能繁比例": 39, "月度-能繁母猪存栏（2020年2月新增）": 40, "月度-小猪存栏（2020年5月新增）": 41, "月度-中猪存栏（2020年5月新增）": 42, "月度-大猪存栏（2020年5月新增）": 43, "月度-商品猪出栏量": 44, "月度-能繁母猪存栏量": 45, "月度-原种场二元后备母猪销量及出栏日龄": 46, "涌益样本测算": 47, "周度-养殖利润": 48, "周度-华东冻品价格": 49, "运费": 50, "国产冻品2-4号肉价格": 51, "小猪(50公斤以下)存栏": 52, "中猪(50-80公斤)存栏": 53, "大猪（80公斤以上）月度存栏": 54, "月度猪肉供应占比": 55, "历史出栏体重": 56, "重要部位冻品进口": 57, "各存栏规模": 58, "进口肉": 59, "猪料原料占比": 60, "MSY": 61, } return classifyMap } var YongyiWeeklySheetMap5 map[string]*models.YongyiSheet var YongyiWeeklySheetMap6 map[string]*models.YongyiSheet var YongyiWeeklySheetMap9 map[string]*models.YongyiSheet var YongyiBaseInfoMap map[string]*models.YongyiSheet func init() { YongyiWeeklySheetMap5 = map[string]*models.YongyiSheet{ "周度-50公斤二元母猪价格": {"50公斤二元母猪价格", "周度-50公斤二元母猪价格", "周度", "元/头"}, "周度-规模场15公斤仔猪出栏价": {"规模场15公斤仔猪出栏价", "周度-规模场15公斤仔猪出栏价", "周度", "元/头"}, "周度-宰后结算价": {"宰后结算价", "周度-宰后结算价", "周度", "元/公斤"}, "周度-冻品库存": {"冻品库存", "周度-冻品库存", "周度", "%"}, "周度-猪肉价（前三等级白条均价）": {"前三等级白条均价", "周度-猪肉价（前三等级白条均价）", "周度", "元/公斤"}, } YongyiWeeklySheetMap6 = map[string]*models.YongyiSheet{ "周度-冻品库存多样本": {"冻品库存多样本", "周度-冻品库存多样本", "周度", "%"}, "月度出栏完成率": {"出栏完成率", "月度出栏完成率", "旬度", "%"}, } YongyiWeeklySheetMap9 = map[string]*models.YongyiSheet{ "月度-小猪存栏（2020年5月新增）": {"50kg以下小猪存栏量", "月度-小猪存栏（2020年5月新增）", "月度", "头"}, "月度-中猪存栏（2020年5月新增）": {"中猪存栏量", "月度-中猪存栏（2020年5月新增）", "月度", "头"}, "月度-大猪存栏（2020年5月新增）": {"大猪存栏量", "月度-大猪存栏（2020年5月新增）", "月度", "头"}, } YongyiBaseInfoMap = map[string]*models.YongyiSheet{ "出栏价": {"商品猪出栏价", "日度-商品猪出栏价", "日度", "元/公斤"}, "标肥价差": {"", "日度-商品猪标肥价差", "日度", "元/公斤"}, "价格+宰量": {"", "日度-商品猪全国均价和宰量", "日度", "元/公斤"}, "屠宰企业日度屠宰量": {"商品猪日屠宰量", "日度-屠宰企业屠宰量", "日度", "头"}, "周度-商品猪出栏价": {"商品猪出栏价", "周度-商品猪出栏价", "周度", "元/公斤"}, "周度-体重": {"商品猪出栏体重", "周度-体重", "周度", "公斤"}, "周度-屠宰厂宰前活猪重": {"商品猪宰前活猪重", "周度-屠宰厂宰前活猪重", "周度", "公斤"}, "周度-各体重段价差": {"", "周度-各体重段价差", "周度", "元/斤"}, "周度-50公斤二元母猪价格": {"50公斤二元母猪价格", "周度-50公斤二元母猪价格", "周度", "元/头"}, "周度-规模场15公斤仔猪出栏价": {"规模场15公斤仔猪出栏价", "周度-规模场15公斤仔猪出栏价", "周度", "元/头"}, "周度-宰后结算价": {"宰后结算价", "周度-宰后结算价", "周度", "元/公斤"}, "周度-冻品库存": {"冻品库存", "周度-冻品库存", "周度", "%"}, "周度-冻品库存多样本": {"冻品库存多样本", "周度-冻品库存多样本", "周度", "%"}, "周度-猪肉价（前三等级白条均价）": {"前三等级白条均价", "周度-猪肉价（前三等级白条均价）", "周度", "元/公斤"}, "月度出栏完成率": {"出栏完成率", "月度出栏完成率", "旬度", "%"}, "月度计划出栏量": {"月度计划出栏量环比", "月度计划出栏量", "月度", "%"}, "月度-能繁母猪存栏（2020年2月新增）": {"能繁母猪存栏量", "月度-能繁母猪存栏（2020年2月新增）", "月度", "头"}, "月度-小猪存栏（2020年5月新增）": {"50kg以下小猪存栏量", "月度-小猪存栏（2020年5月新增）", "月度", "头"}, "月度-中猪存栏（2020年5月新增）": {"中猪存栏量", "月度-中猪存栏（2020年5月新增）", "月度", "头"}, "月度-大猪存栏（2020年5月新增）": {"大猪存栏量", "月度-大猪存栏（2020年5月新增）", "月度", "头"}, "月度-商品猪出栏量": {"商品猪出栏量", "月度-商品猪出栏量", "月度", "头"}, "历史猪价": {"历史猪价", "历史猪价", "月度", "元/公斤"}, "二育成本": {"", "二育成本", "周度", "kg"}, //单位有四种kg、无， 元/kg， 元 "二育销量": {"二育销量", "二育销量", "周度", "%"}, "育肥栏舍利用率": {"育肥栏舍利用率", "育肥栏舍利用率", "旬度", "%"}, "周度-养殖利润最新": {"出栏肥猪利润", "周度-养殖利润最新", "周度", "元/头"}, "周度-当期、预期成本": {"", "周度-当期、预期成本", "周度", "元/kg"}, "育肥全价料出厂价": {"育肥全价料出厂均价参考", "周度-育肥全价料出厂价", "周度", "元/吨"}, "周度-成本计算附件": {"", "周度-成本计算附件", "周度", "元/头"}, //多个单位：元/头，%，kg，元 "周度-毛白价差": {"毛白价差", "周度-毛白价差", "周度", "元/公斤"}, "仔猪、母猪": {"", "仔猪、母猪", "周度", "元/头"}, //多个前缀，仔猪价，二元母猪价 "周度-河南屠宰白条成本": {"河南屠宰白条成本", "周度-河南屠宰白条成本", "周度", "元/kg"}, //多个单位：kg，元/kg，元/头 "周度-淘汰母猪价格": {"淘汰母猪价格", "周度-淘汰母猪价格", "周度", "元/斤"}, "鲜销率": {"鲜销率", "周度-鲜销率", "周度", "%"}, "周度-猪肉产品价格": {"猪肉产品价格", "周度-猪肉产品价格", "周度", "元/公斤"}, "周度-屠宰企业日度屠宰量": {"屠宰企业日度屠宰量", "周度-屠宰企业日度屠宰量", "周度", "头"}, "周度-屠宰新2022.10.28": {"不同规模屠宰厂宰杀量", "周度-屠宰新2022.10.28", "周度", "头"}, "月度-淘汰母猪屠宰厂宰杀量": {"淘汰母猪屠宰厂宰杀量", "月度-淘汰母猪屠宰厂宰杀量", "月度", "头"}, "月度-猪料销量": {"饲料销量（环比）", "月度-猪料销量", "月度", "头"}, //todo 单位有问题 "月度-屠宰企业开工率": {"", "月度-屠宰企业开工率", "月度", "%"}, "月度-屠宰厂公母比例": {"屠宰厂", "月度-屠宰厂公母比例", "月度", "头"}, //多个单位：头，% "月度-生产指标（2021.5.7新增）": {"生产指标", "月度-生产指标（2021.5.7新增）", "月度", "头"}, //多个单位：头，% "月度-生产指标2": {"生产指标", "月度-生产指标2", "月度", "头"}, //多个单位：头，% "月度-二元三元能繁比例": {"能繁母猪存栏量", "月度-二元三元能繁比例", "月度", "%"}, //多个单位：头，% "月度-能繁母猪存栏量": {"月度能繁母猪存栏量", "月度-能繁母猪存栏量", "月度", "头"}, "月度-原种场二元后备母猪销量及出栏日龄": {"原种场二元后备母猪销量及出栏日龄", "月度-原种场二元后备母猪销量及出栏日龄", "月度", "头"}, "涌益样本测算": {"涌益样本测算", "涌益样本测算", "月度", "窝"}, //多个单位：头，%，窝，公斤，元/公斤 "周度-养殖利润": {"外购育肥", "周度-养殖利润", "周度", "元/头"}, "华东冻品价格": {"华东冻品价格", "周度-华东冻品价格", "周度", "元/公斤"}, "运费": {"9.6米拉猪车主流运费", "运费", "周度", "元/公里"}, "国产冻品2-4号肉价格": {"", "国产冻品2-4号肉价格", "月度", "元/吨"}, "月度-小猪（50公斤以下）存栏": {"小猪(50公斤以下)月度存栏", "小猪(50公斤以下)存栏", "月度", "头"}, "月度-中猪（50-80公斤）存栏": {"中猪(50-80公斤)月度存栏", "中猪(50-80公斤)存栏", "月度", "头"}, "月度-大猪（80公斤以上）月度存栏": {"大猪(80公斤以上)月度存栏", "大猪（80公斤以上）月度存栏", "月度", "头"}, "月度猪肉供应占比": {"月度猪肉供应占比", "月度猪肉供应占比", "月度", "%"}, "历史出栏体重": {"商品猪历史出栏体重", "历史出栏体重", "周度", "公斤"}, "重要部位冻品进口": {"重要部位冻品进口", "重要部位冻品进口", "月度", "-"}, "各存栏规模": {"半年度各规模存栏", "各存栏规模", "半年度", "%"}, "进口肉": {"", "进口肉", "月度", ""}, "猪料原料占比": {"猪料育肥全价料中各原料占比", "猪料原料占比", "月度", "%"}, "MSY": {"MSY", "MSY", "年度", "%"}, } } func GetBaseInfo(sheetName string) (classifyName string, classifySort int, frequency string, unit string, namePrefix string, namePrefixPingin string) { // 获取指标分类 info, ok := YongyiBaseInfoMap[sheetName] if !ok { return } classifyName = info.ClassifyName classifyMap := getClassifySortMap() classifySort, _ = classifyMap[classifyName] frequency = info.Frequency unit = info.Unit namePrefix = info.NamePrefix namePrefixPingin = "yyzx" + utils.GetFirstPingYin(namePrefix) return } func GetMergeCells(f *excelize.File, sheet string) (mergeCellMap map[int]map[int]string, err error) { mergedCells, err := f.GetMergeCells(sheet) if err != nil { fmt.Println(err) return } mergeCellMap = make(map[int]map[int]string) // 遍历所有合并单元格范围 for _, cellRange := range mergedCells { fmt.Println("Merged Cell Range:", cellRange) cellVal := cellRange.GetCellValue() // 解析合并单元格范围，例如 "A1:B2" startCell, endCell := cellRange.GetStartAxis(), cellRange.GetEndAxis() if err != nil { fmt.Println(err) continue } // 解析起始单元格的下标 startCol, startRow, err := excelize.CellNameToCoordinates(startCell) if err != nil { fmt.Println(err) continue } // 解析结束单元格的下标 endCol, endRow, err := excelize.CellNameToCoordinates(endCell) if err != nil { fmt.Println(err) continue } // 打印合并单元格的起始和结束下标 fmt.Printf("Start: Row %d, Col %d\n", startRow, startCol) fmt.Printf("End: Row %d, Col %d\n", endRow, endCol) //把合并的单元格整理成单个单元格，用于计算 for i := startRow; i <= endRow; i++ { cellMap, ok := mergeCellMap[i-1] if !ok { cellMap = make(map[int]string) } for j := startCol; j <= endCol; j++ { cellMap[j-1] = strings.TrimSpace(cellVal) } mergeCellMap[i-1] = cellMap } } return } // GetWeekly13IndexName 二育成本指标名称处理 func GetWeekly13IndexName(name, level string) string { switch { case strings.Contains(name, "二育主流采购体重段"): return fmt.Sprintf("二育主流%s采购体重段", level) case strings.Contains(name, "计划出栏体重"): return fmt.Sprintf("计划%s出栏体重", level) case strings.Contains(name, "料肉比"): return fmt.Sprintf("%s料肉比", level) case strings.Contains(name, "采购成本"): return fmt.Sprintf("%s采购成本", level) case strings.Contains(name, "运费+损耗"): return fmt.Sprintf("%s运费+损耗", level) case strings.Contains(name, "增重饲料成本"): return fmt.Sprintf("%s增重饲料成本", level) case strings.Contains(name, "二育成本"): return fmt.Sprintf("%s二育成本", level) } return name } // GetWeekly13IndexUnit 二育成本指标名称处理 func GetWeekly13IndexUnit(name string) string { switch { case strings.Contains(name, "二育主流采购体重段") || strings.Contains(name, "计划出栏体重"): return "kg" case strings.Contains(name, "料肉比"): return "无" case strings.Contains(name, "采购成本") || strings.Contains(name, "运费+损耗") || strings.Contains(name, "二育成本"): return "元/kg" case strings.Contains(name, "增重饲料成本"): return "元" } return name } func getSundayOfWeek(year int, week int) string { t := time.Date(year, 1, 1, 0, 0, 0, 0, time.Local) weekday := int(t.Weekday()) daysToFirstSunday := (7 - weekday) % 7 firstSunday := t.AddDate(0, 0, daysToFirstSunday) daysToDesiredSunday := (week - 1) * 7 return firstSunday.AddDate(0, 0, daysToDesiredSunday).Format(utils.FormatDate) } func isValueValid(value string) bool { if value == "" { return false } re := regexp.MustCompile(`^-?(\d+(\.\d+)?|\.\d+)(%)?$`) return re.MatchString(value) } func excelDateToTimeDateStr(excelDate int) string { baseDate := time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC) return baseDate.AddDate(0, 0, excelDate).Format(utils.FormatDate) } func getMergeCellValMap(f *excelize.File, sheetName string) (mergeIndexDataMap map[string]string, e error) { mergeIndexDataMap = make(map[string]string) merges, e := f.GetMergeCells(sheetName) if e != nil { return } for _, merge := range merges { xmin, ymin, e := excelize.CellNameToCoordinates(merge.GetStartAxis()) if e != nil { continue } xmax, ymax, e := excelize.CellNameToCoordinates(merge.GetEndAxis()) if e != nil { continue } for i := xmin; i <= xmax; i++ { for j := ymin; j <= ymax; j++ { mergeIndexDataMap[fmt.Sprintf("%d:%d", i, j)] = merge.GetCellValue() } } } return } func getCellValue(cell string) (val string, ok bool) { valueArr := strings.Split(cell, "-") switch len(valueArr) { case 1: if !isValueValid(valueArr[0]) { return } return valueArr[0], true case 2: val1, err := strconv.ParseFloat(valueArr[0], 64) if err != nil { return } val2, err := strconv.ParseFloat(valueArr[1], 64) if err != nil { return } mean := (val1 + val2) / 2 return fmt.Sprintf("%.2f", mean), true } return "", false } func LastDayOfMonth(year int, month time.Month) string { firstDay := time.Date(year, month, 1, 0, 0, 0, 0, time.Local) nextMonth := firstDay.AddDate(0, 1, 0) return nextMonth.Add(-time.Nanosecond * 1).Format(utils.FormatDate) }