eta_data_analysis/services/base_from_yongyi/daily.go

package base_from_yongyi

import (
	"eta/eta_data_analysis/models"
	"eta/eta_data_analysis/utils"
	"fmt"
	"github.com/tealeg/xlsx/v3"
	"strconv"
	"strings"
	"time"
)

// HandleYongyiExcelDaily1 日度-商品猪出栏价
func HandleYongyiExcelDaily1(sheet *xlsx.Sheet) (indexList []*models.YongyiExcelIndex, err error) {
	classifyName := "日度-商品猪出栏价"
	classifyMap := getClassifySortMap()
	classifySort, _ := classifyMap[classifyName]
	frequency := "日度"
	unit := "元/公斤"
	namePrefix := "商品猪出栏价"
	namePrefixPingin := "yyzx" + utils.GetFirstPingYin(namePrefix)
	//sheet := dailyPriceSheet
	// 遍历行读取
	indexList = make([]*models.YongyiExcelIndex, 0)
	dateMap := make(map[int]string)
	nameMap := make(map[int]string)
	maxRow := sheet.MaxRow
	maxCell := sheet.MaxCol
	sort := 0
	fmt.Println("最大行")
	fmt.Println(maxRow)
	stopFlag := false
	// 指标名称
	indexMap := make(map[string]*models.YongyiExcelIndex)
	for i := 0; i < maxRow; i++ {
		fmt.Printf("当前第%d行 \n", i)
		row, e := sheet.Row(i)
		if e != nil {
			err = fmt.Errorf("读取行失败")
			return
		}
		if stopFlag {
			break
		}

		if i == 0 { // 首行，表示时间
			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				text := cell.String()
				if k > 1 && text != "" {
					if cell.IsTime() {
						dateText, _ := cell.GetTime(false)
						text = dateText.Format(utils.FormatDate)
					}
					// 检查单元格是否为合并单元格
					if cell.HMerge > 0 {
						for j := 1; j <= cell.HMerge; j++ {
							dateMap[k+j] = text
						}
					}
					fmt.Printf("合并单元格开始列：%d \n", k)
					dateMap[k] = text
				}
			}
		} else if i == 1 { //表示表头
			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				text := cell.String()
				nameMap[k] = text
			}
		} else { //数据列

			province := ""
			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				text := cell.String()
				fmt.Printf("当前第%d列，内容为%s \n", k, text)
				if k == 0 {
					province = text
					if province == "" {
						stopFlag = true
						break
					}
					continue
				} else if k == 1 {
					continue
				} else {
					// 判断出不是字符的，则过滤
					if text == "" {
						continue
					}
					if strings.Contains(text, "%") {
						text = strings.Replace(text, "%", "", 1)
					}
					_, e := strconv.ParseFloat(text, 64)
					if e != nil {
						utils.FileLog.Info(fmt.Sprintf("sheet：%s 第%d行, 第%d列 strconv.ParseFloat Err:%s", sheet.Name, i, k, e))
						continue
					}
				}

				date, ok1 := dateMap[k]
				if !ok1 {
					utils.FileLog.Info(fmt.Sprintf("找不到对应的日期，第%d行，第%d列， text:%s", i, k, text))
					continue
				}
				name, ok2 := nameMap[k]
				if !ok2 {
					err = fmt.Errorf("找不到对应的列名，第%d行，第%d列", i, k)
					return
				}
				if name == "全国均价" {
					name = "全国"
				}
				fmt.Printf("当前第%d行第%d列, 当前省份%s \n", i, k, province)
				// 处理指标名称
				fullIndexName := fmt.Sprintf("%s/%s/%s", namePrefix, province, name)

				if name != "规模场" && name != "小散户" && name != "均价" {
					// 只处理以上三个类型，其余过滤
					continue
				}

				if fullIndexName == "商品猪出栏价/全国/均价" {
					continue
				}

				indexItem, okIndex := indexMap[fullIndexName]

				provincePingyin := utils.GetFullPingYin(province)
				namePingin := utils.GetFirstPingYin(name)
				fullIndexNamePingyin := fmt.Sprintf("%s%s%s", namePrefixPingin, provincePingyin, namePingin)

				if !okIndex {
					// 新增指标
					indexItem = new(models.YongyiExcelIndex)
					indexItem.IndexName = fullIndexName
					indexItem.ClassifyName = classifyName
					indexItem.ClassifySort = classifySort
					indexItem.IndexCode = fullIndexNamePingyin
					indexItem.Frequency = frequency
					indexItem.Sort = sort
					indexItem.Unit = unit
					indexItem.ExcelDataMap = make(map[string]string)
					sort++
				}
				fmt.Printf("indexItem%s", indexItem.IndexCode)

				indexItem.ExcelDataMap[date] = text
				indexMap[fullIndexName] = indexItem
				continue
			}
		}
	}
	for _, v := range indexMap {
		indexList = append(indexList, v)
	}
	return
}

// HandleYongyiExcelDaily2 日度-商品猪标肥价差
func HandleYongyiExcelDaily2(sheet *xlsx.Sheet) (indexList []*models.YongyiExcelIndex, err error) {
	classifyName := "日度-商品猪标肥价差"
	classifyMap := getClassifySortMap()
	classifySort, _ := classifyMap[classifyName]
	frequency := "日度"
	unit := "元/公斤"
	pingYinPrefix := "yyzx"
	// 遍历行读取
	indexList = make([]*models.YongyiExcelIndex, 0)
	dateMap := make(map[int]string)
	nameMap := make(map[int]string)
	maxRow := sheet.MaxRow
	maxCell := sheet.MaxCol
	sort := 0
	fmt.Println("最大行")
	fmt.Println(maxRow)
	stopFlag := false
	// 指标名称
	indexMap := make(map[string]*models.YongyiExcelIndex)
	for i := 0; i < maxRow; i++ {
		fmt.Printf("当前第%d行 \n", i)
		row, e := sheet.Row(i)
		if e != nil {
			err = fmt.Errorf("读取行失败")
			return
		}
		if stopFlag {
			break
		}
		if i == 0 { // 首行，表示时间

			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				text := cell.String()
				if text != "" {
					if cell.IsTime() {
						dateText, _ := cell.GetTime(false)
						text = dateText.Format(utils.FormatDate)
					}
					// 检查单元格是否为合并单元格
					if cell.HMerge > 0 {
						for j := 1; j <= cell.HMerge; j++ {
							dateMap[k+j] = text
						}
					}
					fmt.Printf("合并单元格开始列：%d \n", k)
					dateMap[k] = text
				}
			}
		} else if i == 1 { //表示表头
			// 处理 index指标表

			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				text := cell.String()
				nameMap[k] = text
			}
		} else { //数据列

			province := ""
			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				fmt.Printf("当前第%d列 \n", k)
				text := cell.String()
				if k == 0 {
					province = text
					if province == "" {
						stopFlag = true
						break
					}
					continue
				} else {
					// 判断出不是字符的，则过滤
					if text == "" {
						continue
					}
					if strings.Contains(text, "%") {
						text = strings.Replace(text, "%", "", 1)
					}
					_, e := strconv.ParseFloat(text, 64)
					if e != nil {
						utils.FileLog.Info(fmt.Sprintf("sheet：%s 第%d行, 第%d列 strconv.ParseFloat Err:%s", sheet.Name, i, k, e))
						continue
					}
				}

				date, ok1 := dateMap[k]
				if !ok1 {
					utils.FileLog.Info(fmt.Sprintf("找不到对应的日期，第%d行，第%d列， text:%s", i, k, text))
					continue
				}
				name, ok2 := nameMap[k]
				if !ok2 {
					err = fmt.Errorf("找不到对应的列名，第%d行，第%d列", i, k)
					return
				}
				if province == "全国均价" {
					province = "全国"
				}
				fmt.Printf("当前第%d行第%d列, 当前省份%s \n", i, k, province)
				// 处理指标名称

				// 过滤特殊的指标 放到对应的指标名称的下方

				if name != "市场标重猪" && name != "150公斤左右较标猪" && name != "175公斤左右较标猪" {
					// 只处理以上三个类型，其余过滤
					continue
				}

				if name == "150公斤左右较标猪" {
					name = "150公斤较标猪价差"
				} else if name == "175公斤左右较标猪" {
					name = "175公斤较标猪价差"
				} else if name == "市场标重猪" {
					name = "市场标重猪价"
				} else if name == "全国均价" {
					name = "全国"
				}
				fullIndexName := fmt.Sprintf("%s/%s", name, province)
				indexItem, okIndex := indexMap[fullIndexName]

				provincePingyin := utils.GetFullPingYin(province)
				namePrefixPingin := utils.GetFirstPingYin(name)
				fullIndexNamePingyin := fmt.Sprintf("%s%s%s", pingYinPrefix, namePrefixPingin, provincePingyin)

				if !okIndex {
					// 新增指标
					indexItem = new(models.YongyiExcelIndex)
					indexItem.IndexName = fullIndexName
					indexItem.ClassifyName = classifyName
					indexItem.ClassifySort = classifySort
					indexItem.IndexCode = fullIndexNamePingyin
					indexItem.Frequency = frequency
					indexItem.Sort = sort
					indexItem.Unit = unit
					indexItem.ExcelDataMap = make(map[string]string)
					sort++
				}
				fmt.Printf("indexItem%s", indexItem.IndexCode)

				indexItem.ExcelDataMap[date] = text
				indexMap[fullIndexName] = indexItem
				continue
			}
		}
	}
	for _, v := range indexMap {
		indexList = append(indexList, v)
	}
	return
}

// HandleYongyiExcelDaily3 日度-商品猪全国均价和宰量
func HandleYongyiExcelDaily3(sheet *xlsx.Sheet) (indexList []*models.YongyiExcelIndex, err error) {
	classifyName := "日度-商品猪全国均价和宰量"
	classifyMap := getClassifySortMap()
	classifySort, _ := classifyMap[classifyName]
	frequency := "日度"
	unit := ""
	// 遍历行读取
	indexList = make([]*models.YongyiExcelIndex, 0)
	nameMap := make(map[int]string)
	maxRow := sheet.MaxRow
	maxCell := sheet.MaxCol
	sort := 0
	fmt.Println("最大行")
	fmt.Println(maxRow)
	stopFlag := false
	// 指标名称
	indexMap := make(map[string]*models.YongyiExcelIndex)
	for i := 0; i < maxRow; i++ {
		if stopFlag {
			break
		}
		fmt.Printf("当前第%d行 \n", i)
		row, e := sheet.Row(i)
		if e != nil {
			err = fmt.Errorf("读取行失败")
			return
		}

		if i == 0 { // 首行，名称

			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				text := cell.String()
				nameMap[k] = text
			}
		} else { //数据列

			date := ""
			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				fmt.Printf("当前第%d列 \n", k)
				text := cell.String()
				if k == 0 {
					if cell.IsTime() {
						dateText, _ := cell.GetTime(false)
						text = dateText.Format(utils.FormatDate)
					}
					date = text
					if text == "" {
						stopFlag = true
						break
					}
					continue
				} else {
					// 判断出不是字符的，则过滤
					if text == "" {
						continue
					}
					if strings.Contains(text, "%") {
						text = strings.Replace(text, "%", "", 1)
					}
					_, e := strconv.ParseFloat(text, 64)
					if e != nil {
						utils.FileLog.Info(fmt.Sprintf("sheet：%s 第%d行, 第%d列 strconv.ParseFloat Err:%s", sheet.Name, i, k, e))
						continue
					}
				}

				name, ok2 := nameMap[k]
				if !ok2 {
					err = fmt.Errorf("找不到对应的列名，第%d行，第%d列", i, k)
					return
				}
				if name != "全国均价" && name != "日屠宰量" {
					// 只处理以上三个类型，其余过滤
					continue
				}
				var fullIndexNamePingyin string
				if name == "全国均价" {
					name = "商品猪出拦价/全国/均价"
					fullIndexNamePingyin = "yyzxspzcljquanguojj"
					unit = "元/公斤"
				} else if name == "日屠宰量" {
					name = "商品猪日屠宰量/全国"
					fullIndexNamePingyin = "yyzxspzrtzlquanguo"
					unit = "头/日"
				}
				fullIndexName := name
				indexItem, okIndex := indexMap[fullIndexName]

				if !okIndex {
					// 新增指标
					indexItem = new(models.YongyiExcelIndex)
					indexItem.IndexName = fullIndexName
					indexItem.ClassifyName = classifyName
					indexItem.ClassifySort = classifySort
					indexItem.IndexCode = fullIndexNamePingyin
					indexItem.Frequency = frequency
					indexItem.Sort = sort
					indexItem.Unit = unit
					indexItem.ExcelDataMap = make(map[string]string)
					sort++
				}
				fmt.Printf("indexItem%s", indexItem.IndexCode)

				indexItem.ExcelDataMap[date] = text
				indexMap[fullIndexName] = indexItem
			}
		}
	}
	for _, v := range indexMap {
		indexList = append(indexList, v)
	}
	return
}

// HandleYongyiExcelDaily4 日度-屠宰企业屠宰量
func HandleYongyiExcelDaily4(sheet *xlsx.Sheet) (indexList []*models.YongyiExcelIndex, err error) {
	classifyName := "日度-屠宰企业屠宰量"
	classifyMap := getClassifySortMap()
	classifySort, _ := classifyMap[classifyName]
	frequency := "日度"
	unit := "头/日"
	namePrefix := "商品猪日屠宰量"
	namePrefixPingin := "yyzx" + utils.GetFirstPingYin(namePrefix)
	// 遍历行读取
	indexList = make([]*models.YongyiExcelIndex, 0)
	dateMap := make(map[int]string)
	maxRow := sheet.MaxRow
	maxCell := sheet.MaxCol
	sort := 0
	fmt.Println("最大行")
	fmt.Println(maxRow)
	stopFlag := false
	// 指标名称
	indexMap := make(map[string]*models.YongyiExcelIndex)
	for i := 0; i < maxRow; i++ {
		fmt.Printf("当前第%d行 \n", i)
		row, e := sheet.Row(i)
		if e != nil {
			err = fmt.Errorf("读取行失败")
			return
		}
		if stopFlag {
			break
		}
		if i == 0 { // 首行，表示时间

			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				text := cell.String()
				if text != "" && text != "省份" {
					if cell.IsTime() {
						dateText, _ := cell.GetTime(false)
						text = dateText.Format(utils.FormatDate)
					} else {
						if strings.Contains(text, "（") {
							tmp := strings.Split(text, "（")
							text = tmp[0]
						}
						timeTmp, tErr := time.ParseInLocation(utils.FormatDate1, text, time.Local)
						if tErr != nil {
							err = fmt.Errorf("时间格式转换失败，Err:%s", tErr)
							return
						}
						text = timeTmp.Format(utils.FormatDate)
					}

					dateMap[k] = text
				}
			}
		} else { //数据列

			province := ""
			for k := 0; k < maxCell; k++ {
				cell := row.GetCell(k)
				fmt.Printf("当前第%d列 \n", k)
				text := cell.String()
				if k == 0 {
					province = text
					if province == "" {
						stopFlag = true
						break
					}
					continue
				} else {
					// 判断出不是字符的，则过滤
					if text == "" {
						continue
					}
					if strings.Contains(text, "%") {
						text = strings.Replace(text, "%", "", 1)
					}
					_, e := strconv.ParseFloat(text, 64)
					if e != nil {
						utils.FileLog.Info(fmt.Sprintf("sheet：%s 第%d行, 第%d列 strconv.ParseFloat Err:%s", sheet.Name, i, k, e))
						continue
					}
				}

				date, ok1 := dateMap[k]
				if !ok1 {
					utils.FileLog.Info(fmt.Sprintf("找不到对应的日期，第%d行，第%d列， text:%s", i, k, text))
					continue
				}
				if province == "合计（单位：头）" {
					continue
				}
				fmt.Printf("当前第%d行第%d列, 当前省份%s \n", i, k, province)
				// 处理指标名称

				// 过滤特殊的指标 放到对应的指标名称的下方
				fullIndexName := fmt.Sprintf("%s/%s", namePrefix, province)

				provincePingyin := utils.GetFullPingYin(province)
				fullIndexNamePingyin := fmt.Sprintf("%s%s", namePrefixPingin, provincePingyin)

				indexItem, okIndex := indexMap[fullIndexName]

				if !okIndex {
					// 新增指标
					indexItem = new(models.YongyiExcelIndex)
					indexItem.IndexName = fullIndexName
					indexItem.ClassifyName = classifyName
					indexItem.ClassifySort = classifySort
					indexItem.IndexCode = fullIndexNamePingyin
					indexItem.Frequency = frequency
					indexItem.Sort = sort
					indexItem.Unit = unit
					indexItem.ExcelDataMap = make(map[string]string)
					sort++
				}
				fmt.Printf("indexItem%s", indexItem.IndexCode)

				indexItem.ExcelDataMap[date] = text
				indexMap[fullIndexName] = indexItem
			}
		}
	}
	for _, v := range indexMap {
		indexList = append(indexList, v)
	}
	return
}

func getClassifySortMap() map[string]int {
	classifyMap := map[string]int{
		"日度-商品猪出栏价":            1,
		"日度-商品猪标肥价差":           2,
		"日度-商品猪全国均价和宰量":        3,
		"日度-屠宰企业屠宰量":           4,
		"周度-商品猪出栏价":            5,
		"周度-体重":                6,
		"周度-屠宰厂宰前活猪重":          7,
		"周度-各体重段价差":            8,
		"周度-50公斤二元母猪价格":        8,
		"周度-规模场15公斤仔猪出栏价":      10,
		"周度-宰后结算价":             11,
		"周度-冻品库存":              12,
		"周度-冻品库存多样本":           13,
		"周度-猪肉价（前三等级白条均价）":     14,
		"月度出栏完成率":              15,
		"月度计划出栏量":              16,
		"月度-能繁母猪存栏（2020年2月新增）": 17,
		"月度-小猪存栏（2020年5月新增）":   18,
		"月度-中猪存栏（2020年5月新增）":   19,
		"月度-大猪存栏（2020年5月新增）":   20,
		"月度-商品猪出栏量":            21,
	}
	return classifyMap
}