eta_data_analysis/services/base_from_yongyi_v2/base.go

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