eta_data_analysis/services/base_from_fenwei.go

package services

import (
	"encoding/json"
	"eta/eta_data_analysis/models"
	"eta/eta_data_analysis/utils"
	"fmt"
	"github.com/shakinm/xlsReader/xls"
	"github.com/tealeg/xlsx"
	"github.com/xuri/excelize/v2"
	"path"
	"strings"
	"time"
)

const (
	FenweiIndexNamePrefix = "fwsj" // 指标名称前缀

	FenweiExcelNameA    = "动力煤产销存-66家"
	FenweiExcelNameB    = "动力煤产销存-100家"
	FenweiExcelNameC    = "焦煤产销存-53家"
	FenweiNewExcelNameC = "炼焦煤产销存分省-53家"
	FenweiExcelNameD    = "焦煤产销存-88家"
	FenweiNewExcelNameD = "炼焦煤产销存分省-88家"
	FenweiExcelNameE    = "样本焦化厂焦煤"

	FenweiYangBenIndexNamePre    = "样本焦企炼焦煤库存可用天数"
	FenweiYangBenIndexClassifyId = 5 // 样本焦企炼焦分类ID

	FenweiExcelNameF    = "焦炭产销存-61家"
	FenweiNewExcelNameF = "炼焦煤产销存分省-61家"
	FenweiExcelNameG    = "焦炭产销存-100家"
	FenweiNewExcelNameG = "炼焦炭产销存分省-100家"
	FenweiExcelNameH    = "样本钢厂焦炭"

	FenweiYangBenSteelCoalIndexNamePre    = "样本钢厂焦炭可用天数"
	FenweiYangBenSteelCoalIndexClassifyId = 35 // 样本焦企炼焦分类ID
)

var FenweiProvinces = []string{
	"内蒙古", "山西", "陕西", "三省合计", "临汾市", "临汾", "吕梁市", "吕梁", "长治市", "长治", "山西其他", "河北", "合计", "山东", "其他",
}

// FenweiProvinceClassifyMapping 指标名称省份->分类省份
var FenweiProvinceClassifyMapping = map[string]string{
	"内蒙古":  "内蒙古",
	"山西":   "山西",
	"陕西":   "陕西",
	"三省合计": "三省合计",
	"临汾市":  "临汾市",
	"吕梁市":  "吕梁市",
	"长治市":  "长治市",
	"临汾":   "临汾市",
	"吕梁":   "吕梁市",
	"长治":   "长治市",
	"山西其他": "山西其他",
	"河北":   "河北",
	"山东":   "山东",
	"合计":   "合计",
	"其他":   "其他",
}

// FenweiFirstClassifyMap excel表名->一级分类ID
var FenweiFirstClassifyMap = map[string]int{
	FenweiExcelNameA: 1,
	FenweiExcelNameB: 2,
	FenweiExcelNameC: 3,
	FenweiExcelNameD: 4,
	FenweiExcelNameE: 5,
	FenweiExcelNameF: 33,
	FenweiExcelNameG: 34,
	FenweiExcelNameH: 35,
}

// FenweiNewDlm66DiffIndexNameReplaceMapping 动力煤66家-指标名称不同的进行替换
var FenweiNewDlm66DiffIndexNameReplaceMapping = map[string]string{
	"价格平均上涨幅度（元/吨）": "煤矿价格平均上涨幅度",
	"库存库存量（万吨）":     "煤矿库存",
	"盈亏情况盈利煤矿数量":    "盈利煤矿数量",
	"盈亏情况平均利润（元/吨）": "煤矿平均利润",
	"盈亏情况亏损煤矿数量":    "亏损煤矿数量",
	"盈亏情况平均亏损（元/吨）": "煤矿平均亏损",
	"周度产量产量（万吨）":    "煤矿周度产量",
	"销售好":           "销售好煤矿数量",
	"销售一般":          "销售一般煤矿数量",
	"销售不好":          "销售不好煤矿数量",
	"销售关闭":          "关闭煤矿数量",
	"产能利用率产能利用率":    "煤矿产能利用率",
	"开工率开工率":        "煤矿算术平均开工率（含停产）",
	"价格平均下跌幅度":      "煤矿价格平均下跌幅度",
}

// FenweiNewDlm66IgnoreIndexNameMapping 动力煤66家-需要忽略的指标名称
var FenweiNewDlm66IgnoreIndexNameMapping = []string{
	"库存环比", "发运至港口平均利润", "周度产量环比", "开工率算数平均（不含停产矿）", "变化", "产能利用率变化", "开工率变化",
}

// FenweiNewDlm100DiffIndexNameReplaceMapping 动力煤100家-指标名称不同的进行替换
var FenweiNewDlm100DiffIndexNameReplaceMapping = map[string]string{
	"价格平均上涨幅度（元/吨）": "煤矿价格平均上涨幅度",
	"价格平均下跌幅度":      "煤矿价格平均下跌幅度",
	"库存库存量（万吨）":     "煤矿库存",
	"库存环比":          "煤矿库存环比",
	"盈亏情况盈利煤矿数量":    "盈利煤矿数量",
	"盈亏情况平均利润（元/吨）": "煤矿平均利润",
	"盈亏情况亏损煤矿数量":    "亏损煤矿数量",
	"盈亏情况平均亏损（元/吨）": "煤矿平均亏损",
	"发运至港口平均利润":     "煤炭发运至港口平均利润",
	"周度产量产量（万吨）":    "煤矿周度产量",
	"周度产量环比":        "煤矿周度产量环比",
	"产能利用率产能利用率":    "煤矿产能利用率",
	"产能利用率变化":       "煤矿产能利用率环比",
	"开工率开工率":        "煤矿平均开工率",
	"开工率变化":         "煤矿平均开工率环比",
	"销售好":           "销售好煤矿数量",
	"销售一般":          "销售一般煤矿数量",
	"销售不好":          "销售不好煤矿数量",
	"销售关闭":          "关闭煤矿数量",
}

// FenweiNewDlm100IgnoreIndexNameMapping 动力煤100家-需要忽略的指标名称
var FenweiNewDlm100IgnoreIndexNameMapping = make([]string, 0)

// FenweiNewJm53DiffIndexNameReplaceMapping 焦煤53家-指标名称不同的进行替换
var FenweiNewJm53DiffIndexNameReplaceMapping = map[string]string{
	"价格情况煤矿数量":       "煤矿数量",
	"价格情况上涨煤矿数量":     "上涨煤矿数量",
	"价格情况平均上涨（元/吨）":  "煤矿平均上涨幅度",
	"价格情况下跌煤矿数量":     "下跌煤矿数量",
	"价格情况平均下跌（元/吨）":  "煤矿平均下跌幅度",
	"价格情况持平煤矿数量":     "持平煤矿数量",
	"原煤库存情况库存总量（万吨）": "原煤库存",
	"精煤库存情况库存总量（万吨）": "精煤库存",
	"开工情况权重平均开工率":    "煤矿加权开工率",
	"开工情况算数平均开工率":    "煤矿算术平均开工率",
	"开工情况停产煤矿数量":     "停产煤矿数量",
	"开工情况本周原煤产量（万吨）": "本周原煤产量",
	"销售良好":     "销售好煤矿数量",
	"销售正常":     "销售一般煤矿数量",
	"销售不好":     "销售不好煤矿数量",
	"成本盈利煤矿数量": "盈利煤矿数量",
	"成本平均利润":   "煤矿平均利润",
	"成本亏损煤矿数量": "亏损煤矿数量",
	"成本平均亏损":   "煤矿平均亏损",
}

// FenweiNewJm53IgnoreIndexNameMapping 焦煤53家-需要忽略的指标名称
var FenweiNewJm53IgnoreIndexNameMapping = []string{
	"原煤库存情况环比（万吨）", "精煤库存情况环比（万吨）", "开工情况开工率变化", "开工情况停产煤矿数量变化", "开工情况环比产量变化",
}

// FenweiNewJm88DiffIndexNameReplaceMapping 焦煤88家-指标名称不同的进行替换
var FenweiNewJm88DiffIndexNameReplaceMapping = map[string]string{
	"价格情况煤企数量":       "煤企数量",
	"价格情况上涨煤企数量":     "上涨煤企数量",
	"价格情况平均上涨（元/吨）":  "煤企平均上涨幅度",
	"价格情况下跌煤矿数量":     "下跌煤企数量",
	"价格情况平均下跌（元/吨）":  "煤企平均下跌幅度",
	"价格情况持平煤企数量":     "持平煤企数量",
	"原煤库存情况库存总量（万吨）": "原煤库存",
	"原煤库存情况环比（万吨）":   "原煤库存环比",
	"精煤库存情况库存总量（万吨）": "精煤库存",
	"精煤库存情况环比（万吨）":   "精煤库存环比",
	"开工情况产能利用率":      "煤企产能利用率",
	"开工情况产能利用率变化":    "煤企产能利用率环比",
	"开工情况开工率":        "煤企开工率",
	"开工情况开工率变化":      "煤企开工率环比",
	"开工情况停产煤矿数量":     "停产煤矿数量",
	"开工情况停产煤矿数量变化":   "停产煤矿数量变化",
	"开工情况本周原煤产量（万吨）": "本周原煤产量",
	"开工情况环比产量变化":     "本周原煤产量变化",
	"本周精煤产量（万吨）":     "本周精煤产量",
	"环比产量变化":         "本周精煤产量变化",
	"良好":             "销售好煤企数量",
	"正常":             "销售一般煤企数量",
	"不好":             "销售不好煤企数量",
	"盈利煤企数量":         "盈利煤企数量",
	"商品煤平均利润":        "煤企平均利润",
	"亏损煤企数量":         "亏损煤企数量",
	"商品煤平均亏损":        "煤企平均亏损",
}

// FenweiNewJm88IgnoreIndexNameMapping 焦煤88家-需要忽略的指标名称
var FenweiNewJm88IgnoreIndexNameMapping = make([]string, 0)

// FenweiNewJt61DiffIndexNameReplaceMapping 焦炭61家-指标名称不同的进行替换
var FenweiNewJt61DiffIndexNameReplaceMapping = map[string]string{
	"价格情况焦化厂数量":       "焦化厂数量",
	"价格情况上涨煤矿数量":      "上涨煤矿数量",
	"价格情况平均上涨（元/吨）":   "价格平均上涨",
	"下跌焦化厂数量":         "价格下跌焦化厂数量",
	"价格情况平均下跌（元/吨）":   "价格平均下跌",
	"价格情况持平焦化厂数量":     "价格持平焦化厂数量",
	"原煤库存情况库存总量（万吨）":  "原煤库存",
	"焦炭库存情况库存总量（万吨）":  "精煤库存",
	"焦炭库存情况库存销售比（天数）": "库存销售比（天数）",
	"开工情况平均开工率（算数）":   "算数平均开工率",
	"开工情况开工率（加权）":     "加权开工率",
	"开工情况本周焦炭产量（万吨）":  "本周焦炭产量",
	"销售良好":     "销售良好焦炭数量",
	"销售正常":     "销售正常焦炭数量",
	"销售不好":     "销售不好焦炭数量",
	"成本盈利企业数量": "盈利企业数量",
	"成本平均利润":   "焦炭平均利润",
	"成本亏损企业数量": "亏损企业数量",
}

// FenweiNewJt61IgnoreIndexNameMapping 焦炭61家-需要忽略的指标名称
var FenweiNewJt61IgnoreIndexNameMapping = []string{
	"焦炭库存情况环比（万吨）", "焦炭库存情况环比（天数）", "开工情况开工率变化", "开工情况开工率变化（加权）", "开工情况环比产量变化",
}

// FenweiNewJt100DiffIndexNameReplaceMapping 焦炭100家-指标名称不同的进行替换
var FenweiNewJt100DiffIndexNameReplaceMapping = map[string]string{
	"价格情况焦化厂数量":       "焦化厂数量",
	"价格情况上涨煤矿数量":      "价格上涨煤矿数量",
	"价格情况平均上涨（元/吨）":   "价格平均上涨",
	"下跌焦化厂数量":         "价格下跌焦化厂数量",
	"价格情况平均下跌（元/吨）":   "价格平均下跌",
	"价格情况持平焦化厂数量":     "价格持平焦化厂数量",
	"原煤库存情况库存总量（万吨）":  "原煤库存",
	"焦炭库存情况库存总量（万吨）":  "精煤库存",
	"焦炭库存情况库存销售比（天数）": "库存销售比（天数）",
	"开工情况产能利用率":   "产能利用率",
	"开工情况本周焦炭产量（万吨）":  "本周焦炭产量",
	"销售良好":     "销售良好焦炭数量",
	"销售正常":     "销售正常焦炭数量",
	"销售不好":     "销售不好焦炭数量",
	"成本盈利企业数量": "盈利企业数量",
	"成本平均利润":   "焦炭平均利润",
	"成本亏损企业数量": "亏损企业数量",
}

// FenweiNewJt100IgnoreIndexNameMapping 焦炭100家-需要忽略的指标名称
var FenweiNewJt100IgnoreIndexNameMapping = []string{
	"焦炭库存情况环比（万吨）", "焦炭库存情况环比（天数）", "开工情况开工率变化", "开工情况开工率变化（加权）", "开工情况环比产量变化",
}

var FenweiNewDiffIndexNameReplaceMapping = map[string]map[string]string{
	FenweiExcelNameA: FenweiNewDlm66DiffIndexNameReplaceMapping,
	FenweiExcelNameB: FenweiNewDlm100DiffIndexNameReplaceMapping,
	FenweiExcelNameC: FenweiNewJm53DiffIndexNameReplaceMapping,
	FenweiExcelNameD: FenweiNewJm88DiffIndexNameReplaceMapping,
	FenweiExcelNameE: FenweiNewJt61DiffIndexNameReplaceMapping,
	FenweiExcelNameF: FenweiNewJt100DiffIndexNameReplaceMapping,
}

var FenweiNewIgnoreIndexNameMapping = map[string][]string{
	FenweiExcelNameA: FenweiNewDlm66IgnoreIndexNameMapping,
	FenweiExcelNameB: FenweiNewDlm100IgnoreIndexNameMapping,
	FenweiExcelNameC: FenweiNewJm53IgnoreIndexNameMapping,
	FenweiExcelNameD: FenweiNewJm88IgnoreIndexNameMapping,
	FenweiExcelNameE: FenweiNewJt61IgnoreIndexNameMapping,
	FenweiExcelNameF: FenweiNewJt100IgnoreIndexNameMapping,
}

var FenweiNewIndexNameSuffixMapping = map[string]string{
	FenweiExcelNameA: "/动力煤/66家样本",
	FenweiExcelNameB: "/动力煤/100家样本",
	FenweiExcelNameC: "/焦煤/53家样本",
	FenweiExcelNameD: "/焦煤/88家样本",
	FenweiExcelNameE: "/焦炭/61家样本",
	FenweiExcelNameF: "/焦炭/100家样本",
}

var FenweiNewExcelProvinceMapping = map[string]string{
	"内蒙古":    "内蒙古",
	"山西":     "山西",
	"陕西":     "陕西",
	"三省合计":   "三省合计",
	"山西-临汾市": "临汾",
	"山西-吕梁市": "吕梁",
	"山西-长治市": "长治",
	"山西-其他":  "山西其他",
	"山东":     "山东",
	"其他":     "其他",
	"合计":     "合计",
	"河北":     "河北",
}

// AnalysisFenweiHistoryExcel 解析汾渭历史Excel数据
func AnalysisFenweiHistoryExcel(filePath string) (err error) {
	defer func() {
		if err != nil {
			fmt.Printf("AnalysisFenweiExcel ErrMsg: %s\n", err.Error())
			utils.FileLog.Info(fmt.Sprintf("AnalysisFenweiExcel ErrMsg: %s", err.Error()))
		}
	}()

	fileName := path.Base(filePath)
	var indexes []*models.FenweiExcelIndex
	var e error
	if strings.Contains(fileName, FenweiExcelNameA) {
		indexes, e = AnalysisFenweiHistoryExcelNormal(filePath, FenweiExcelNameA)
	} else if strings.Contains(fileName, FenweiExcelNameB) {
		indexes, e = AnalysisFenweiHistoryExcelNormal(filePath, FenweiExcelNameB)
	} else if strings.Contains(fileName, FenweiExcelNameC) {
		indexes, e = AnalysisFenweiHistoryExcelNormal(filePath, FenweiExcelNameC)
	} else if strings.Contains(fileName, FenweiExcelNameD) {
		indexes, e = AnalysisFenweiHistoryExcelNormal(filePath, FenweiExcelNameD)
	} else if strings.Contains(fileName, FenweiExcelNameE) {
		indexes, e = AnalysisFenweiExcelYangBen(filePath)
	} else  if strings.Contains(fileName, FenweiExcelNameF) {
		indexes, e = AnalysisFenweiHistoryExcelNormal(filePath, FenweiExcelNameF)
	} else  if strings.Contains(fileName, FenweiExcelNameG) {
		indexes, e = AnalysisFenweiHistoryExcelNormal(filePath, FenweiExcelNameG)
	} else  if strings.Contains(fileName, FenweiExcelNameH) {
		indexes, e = AnalysisFenweiExcelYangBenSteelCoal(filePath)
	} else {
		utils.FileLog.Info(fmt.Sprintf("文件名有误, FileName: %s", fileName))
		return
	}
	if e != nil {
		utils.FileLog.Info(fmt.Sprintf("获取excel数据失败, FileName: %s, Err: %s", fileName, e))
		return
	}
	if len(indexes) == 0 {
		utils.FileLog.Info(fmt.Sprintf("fileName: %s, index empty", fileName))
		return
	}

	// 写入数据
	params := make(map[string]interface{})
	params["List"] = indexes
	params["TerminalCode"] = utils.TerminalCode
	result, e := PostEdbLib(params, utils.LIB_ROUTE_FENWEI_HANDLE)
	if e != nil {
		b, _ := json.Marshal(params)
		utils.FileLog.Info(fmt.Sprintf("fileName: %s, PostEdbLib err: %s, params: %s", fileName, e.Error(), string(b)))
		return
	}
	resp := new(models.BaseEdbLibResponse)
	if e := json.Unmarshal(result, &resp); e != nil {
		utils.FileLog.Info(fmt.Sprintf("fileName: %s, json.Unmarshal err: %s", fileName, e.Error()))
		return
	}
	if resp.Ret != 200 {
		utils.FileLog.Info(fmt.Sprintf("fileName: %s, Msg: %s, ErrMsg: %s", fileName, resp.Msg, resp.ErrMsg))
		return
	}
	return
}

// AnalysisFenweiHistoryExcelNormal 历史数据-动力煤产销存/焦煤产销存/焦炭产销存
func AnalysisFenweiHistoryExcelNormal(filePath, excelName string) (indexes []*models.FenweiExcelIndex, err error) {
	defer func() {
		if err != nil {
			fmt.Printf("AnalysisFenweiHistoryExcelNormal ErrMsg: %s\n", err.Error())
			utils.FileLog.Info(fmt.Sprintf("AnalysisFenweiHistoryExcelNormal ErrMsg: %s", err.Error()))
		}
	}()

	f, e := excelize.OpenFile(filePath)
	if e != nil {
		err = fmt.Errorf("open file err: %s", e.Error())
		return
	}
	defer func() {
		if e = f.Close(); e != nil {
			err = fmt.Errorf("f close err: %s", e.Error())
		}
	}()
	sheetName := f.GetSheetName(0)
	rows, e := f.GetRows(sheetName)
	if e != nil {
		err = fmt.Errorf("f GetRows err: %s", e.Error())
		return
	}

	// 获取指标分类
	firstId := FenweiFirstClassifyMap[excelName]
	if firstId <= 0 {
		err = fmt.Errorf("excel名称有误, 分类不匹配, excelName: %s", excelName)
		return
	}
	classifies, e := GetFenweiClassify()
	if e != nil {
		err = fmt.Errorf("获取指标分类失败, Err: %s", e.Error())
		return
	}
	secondClassifyMap := make(map[string]int)
	for _, v := range classifies {
		if v.ClassifyId != firstId {
			continue
		}
		for _, c := range v.Child {
			secondClassifyMap[c.ClassifyName] = c.ClassifyId
		}
	}

	indexes = make([]*models.FenweiExcelIndex, 0)
	indexColIndex := make(map[int]*models.FenweiExcelIndex) // 列对应的指标(B列-?列)
	maxCol := 0
	sortArr := make([]string, 0) // 排序用的
	for i, row := range rows {
		// 首行为标题, 忽略
		if i == 0 {
			continue
		}

		// 第二行-指标名称
		if i == 1 {
			for k, cell := range row {
				if k == 0 {
					maxCol += 1
					continue
				}
				name := strings.TrimSpace(cell)
				name = strings.ReplaceAll(name, " ", "")
				if name == "" {
					break
				}
				maxCol += 1
				if indexColIndex[k] == nil {
					indexColIndex[k] = new(models.FenweiExcelIndex)
					indexColIndex[k].ExcelDataMap = make(map[string]string)
					indexColIndex[k].TerminalCode = utils.TerminalCode
					indexColIndex[k].IndexName = name
				}
			}
			continue
		}

		// 第三行-指标ID、指标分类
		if i == 2 {
			for k := 0; k < maxCol; k++ {
				if k == 0 {
					continue
				}
				if indexColIndex[k] == nil {
					utils.FileLog.Info(fmt.Sprintf("第%d列无指标ID", k))
					break
				}
				// 例:内蒙古煤矿数量/动力煤/66家样本->fwsjneimenggumksldlm66jyb
				name := strings.ReplaceAll(indexColIndex[k].IndexName, "/", "")
				var middle, suffix, province string
				for _, p := range FenweiProvinces {
					// 此处山西其他可能会被错误的给鉴别为山西, 所以优先鉴别一下山西其他
					if strings.Contains(name, "山西其他") {
						province = "山西其他"
						middle = utils.GetFullPingYin("山西其他")
						name = strings.ReplaceAll(name, p, "")
						break
					}
					if strings.Contains(name, p) {
						province = p
						middle = utils.GetFullPingYin(p)
						name = strings.ReplaceAll(name, p, "")
						break
					}
				}
				if middle == "" {
					utils.FileLog.Info(fmt.Sprintf("指标名称转换编码有误, IndexName: %s", indexColIndex[k].IndexName))
					continue
				}
				classifyProvince := FenweiProvinceClassifyMapping[province]
				classifyId := secondClassifyMap[classifyProvince]
				if classifyId <= 0 {
					utils.FileLog.Info(fmt.Sprintf("指标名称分类不匹配, IndexName: %s", indexColIndex[k].IndexName))
					continue
				}

				suffix = utils.GetFirstPingYin(name)
				indexColIndex[k].IndexCode = fmt.Sprint(FenweiIndexNamePrefix, middle, suffix)
				indexColIndex[k].ClassifyId = classifyId
				sortArr = append(sortArr, indexColIndex[k].IndexCode)
			}
			continue
		}

		// 第四行-频度
		if i == 3 {
			for k, cell := range row {
				if k == 0 {
					continue
				}
				if indexColIndex[k] == nil {
					utils.FileLog.Info(fmt.Sprintf("第%d列无指标名称", k))
					break
				}
				frequency := strings.TrimSpace(cell)
				indexColIndex[k].Frequency = frequency
			}
			continue
		}

		// 第五行-单位
		if i == 4 {
			for k, cell := range row {
				if k == 0 {
					continue
				}
				if indexColIndex[k] == nil {
					utils.FileLog.Info(fmt.Sprintf("第%d列无指标单位", k))
					break
				}
				unit := strings.TrimSpace(cell)
				indexColIndex[k].Unit = unit
			}
			continue
		}

		// 数据行
		if i > 4 {
			var date string
			for k, cell := range row {
				if k == 0 {
					// 日期
					date = strings.TrimSpace(cell)
					_, e := time.Parse(utils.FormatDate, date)
					if e != nil {
						// 如果第一列不是日期那么结束本行的读取
						break
					}
					continue
				}
				// 日期数据
				if date == "" {
					utils.FileLog.Info(fmt.Sprintf("第%d行无数据日期", i))
					break
				}
				if indexColIndex[k] == nil {
					utils.FileLog.Info(fmt.Sprintf("第%d列无指标名称", k))
					break
				}
				val := strings.TrimSpace(cell)
				if val == "" {
					// 单元格数据为空, 则当前日期无数据
					continue
				}
				indexColIndex[k].ExcelDataMap[date] = val
			}
			continue
		}
	}

	codeIndexes := make(map[string]*models.FenweiExcelIndex)
	for _, v := range indexColIndex {
		if v.IndexCode == "" {
			continue
		}
		codeIndexes[v.IndexCode] = v
	}
	for _, v := range sortArr {
		if codeIndexes[v] != nil {
			indexes = append(indexes, codeIndexes[v])
		}
	}
	return
}

// AnalysisFenweiExcelYangBen 样本焦化厂
func AnalysisFenweiExcelYangBen(filePath string) (indexes []*models.FenweiExcelIndex, err error) {
	defer func() {
		if err != nil {
			fmt.Printf("AnalysisFenweiExcelYangBen ErrMsg: %s\n", err.Error())
			utils.FileLog.Info(fmt.Sprintf("AnalysisFenweiExcelYangBen ErrMsg: %s", err.Error()))
		}
	}()

	indexColIndex := make(map[int]*models.FenweiExcelIndex) // 列对应的指标(B列-E列)
	provinces := []string{"地区", "全国", "山西", "河北", "山东"}
	for k, v := range provinces {
		if k == 0 {
			continue
		}
		if indexColIndex[k] == nil {
			indexColIndex[k] = new(models.FenweiExcelIndex)
			indexColIndex[k].ExcelDataMap = make(map[string]string)
			indexColIndex[k].TerminalCode = utils.TerminalCode
			indexColIndex[k].ClassifyId = FenweiYangBenIndexClassifyId
			indexColIndex[k].Frequency = "周度"
			indexColIndex[k].Unit = "天"
			indexColIndex[k].IndexName = fmt.Sprintf("%s/%s", FenweiYangBenIndexNamePre, v)

			// 指标编码, 例:样本焦企炼焦煤库存可用天数/全国->fwsjybjqljmkckytsquanguo
			name := strings.ReplaceAll(indexColIndex[k].IndexName, "/", "")
			var middle, suffix string
			for _, p := range provinces {
				if strings.Contains(name, p) {
					suffix = utils.GetFullPingYin(p)
					name = strings.ReplaceAll(name, p, "")
					break
				}
			}
			if suffix == "" {
				utils.FileLog.Info(fmt.Sprintf("指标名称转换编码有误, IndexName: %s", indexColIndex[k].IndexName))
				continue
			}
			middle = utils.GetFirstPingYin(name)
			indexColIndex[k].IndexCode = fmt.Sprint(FenweiIndexNamePrefix, middle, suffix)
		}
	}

	maxCols := 5      // 每行只读取前5列即可
	startDateRow := 0 // 数据日期起始行(第一行), 后面依次加4行读取
	startDataRow := 2 // 数据值起始行(第三行), 后面依次加4行读取
	sumDateRow := 0   // 数据日期累加的行, 每加4次重置0
	sumDataRow := 0   // 数据值累加的行, 每加4次重置0
	currDate := ""    // 最近的一次日期
	stop := false     // 结束读取excel

	xlsFile, e := xls.OpenFile(filePath)
	if e != nil {
		err = fmt.Errorf("open xls file err: %s", e.Error())
		return
	}
	sheet, e := xlsFile.GetSheet(0)
	if e != nil {
		err = fmt.Errorf("GetSheet err: %s", e.Error())
		return
	}
	rows := sheet.GetRows()
	for i, row := range rows {
		sumDateRow += 1
		sumDataRow += 1

		// 第一个日期行/后续每4行取一次日期
		if i == startDateRow || sumDateRow == 4 {
			if stop {
				break
			}
			sumDateRow = 0

			for k, cell := range row.GetCols() {
				// 日期只取第一个单元格中的
				if k > 0 {
					break
				}
				strDates := strings.TrimSpace(cell.GetString())
				strDates = strings.ReplaceAll(strDates, "样本焦企炼焦煤库存", "")
				strDates = strings.ReplaceAll(strDates, "\x00", "")
				strDates = strings.ReplaceAll(strDates, "本焦企炼焦煤库存", "")
				strDateArr := strings.Split(strDates, "-")
				// 正常情况下, 长度不为2说明读取到了最后一行了
				if len(strDateArr) != 2 {
					utils.FileLog.Info(fmt.Sprintf("第%d行数据日期长度有误, 结束读取", i+1))
					stop = true
					break
				}
				t, e := time.Parse("2006.1.2", strDateArr[1])
				if e != nil {
					utils.FileLog.Info(fmt.Sprintf("第%d行数据日期有误, err: %s", i+1, e.Error()))
					break
				}
				currDate = t.Format(utils.FormatDate)
			}
		}

		// 第一个数据行/后续每4行取一次数据
		if i == startDataRow || sumDataRow == 4 {
			if currDate == "" {
				utils.FileLog.Info(fmt.Sprintf("第%d行数据对应日期有误", i+1))
				continue
			}
			sumDataRow = 0

			for k, cell := range row.GetCols() {
				if k >= maxCols {
					break
				}
				if k == 0 {
					continue
				}
				val := cell.GetFloat64()
				// 保留4位小数
				indexColIndex[k].ExcelDataMap[currDate] = fmt.Sprintf("%.4f", val)
			}
			// 重置日期
			currDate = ""
		}
	}

	indexes = make([]*models.FenweiExcelIndex, 0)
	for _, v := range indexColIndex {
		if v.IndexCode == "" {
			continue
		}
		indexes = append(indexes, v)
	}
	return
}

// GetFenweiClassify 获取汾渭数据分类
func GetFenweiClassify() (classifies []models.BaseFromFenweiClassifyItem, err error) {
	params := make(map[string]interface{})
	result, e := PostEdbLib(params, utils.LIB_ROUTE_FENWEI_CLASSIFY)
	if e != nil {
		b, _ := json.Marshal(params)
		err = fmt.Errorf("GetFenweiClassify, PostEdbLib err: %s, params: %s", e.Error(), string(b))
		return
	}
	resp := new(models.EdbLibFenweiClassifyResponse)
	if e = json.Unmarshal(result, &resp); e != nil {
		err = fmt.Errorf(fmt.Sprintf("GetFenweiClassify, json.Unmarshal err: %s", e.Error()))
		return
	}
	if resp.Ret != 200 {
		err = fmt.Errorf(fmt.Sprintf("GetFenweiClassify, Msg: %s, ErrMsg: %s", resp.Msg, resp.ErrMsg))
		return
	}
	classifies = resp.Data
	return
}

// GetFenweiIndexList 获取汾渭指标列表
func GetFenweiIndexList() (list []models.BaseFromFenweiIndex, err error) {
	params := make(map[string]interface{})
	result, e := PostEdbLib(params, utils.LIB_ROUTE_FENWEI_INDEX_LIST)
	if e != nil {
		b, _ := json.Marshal(params)
		err = fmt.Errorf("GetFenweiIndexList, PostEdbLib err: %s, params: %s", e.Error(), string(b))
		return
	}
	resp := new(models.EdbLibFenweiIndexListResponse)
	if e = json.Unmarshal(result, &resp); e != nil {
		err = fmt.Errorf(fmt.Sprintf("GetFenweiIndexList, json.Unmarshal err: %s", e.Error()))
		return
	}
	if resp.Ret != 200 {
		err = fmt.Errorf(fmt.Sprintf("GetFenweiIndexList, Msg: %s, ErrMsg: %s", resp.Msg, resp.ErrMsg))
		return
	}
	list = resp.Data
	return
}

// AnalysisFenweiNewExcel 解析汾渭增量Excel数据
func AnalysisFenweiNewExcel(filePath string) (err error) {
	defer func() {
		if err != nil {
			fmt.Printf("AnalysisFenweiNewExcel ErrMsg: %s\n", err.Error())
			utils.FileLog.Info(fmt.Sprintf("AnalysisFenweiNewExcel ErrMsg: %s", err.Error()))
		}
	}()

	fileName := path.Base(filePath)
	var indexes []*models.FenweiExcelIndex
	var e error
	if strings.Contains(fileName, FenweiExcelNameA) {
		indexes, e = AnalysisFenweiNewExcelNormal(filePath, FenweiExcelNameA)
	} else if strings.Contains(fileName, FenweiExcelNameB) {
		indexes, e = AnalysisFenweiNewExcelNormal(filePath, FenweiExcelNameB)
	} else if strings.Contains(fileName, FenweiNewExcelNameC) {
		indexes, e = AnalysisFenweiNewExcelNormal(filePath, FenweiExcelNameC)
	} else if strings.Contains(fileName, FenweiNewExcelNameD) {
		indexes, e = AnalysisFenweiNewExcelNormal(filePath, FenweiExcelNameD)
	} else if strings.Contains(fileName, FenweiExcelNameE) {
		indexes, e = AnalysisFenweiExcelYangBen(filePath) // 样本焦化厂-增量数据和历史数据excel格式相同
	} else if strings.Contains(fileName, FenweiNewExcelNameF) {
		indexes, e = AnalysisFenweiNewExcelNormal(filePath, FenweiExcelNameF)
	} else if strings.Contains(fileName, FenweiNewExcelNameG) {
		indexes, e = AnalysisFenweiNewExcelNormal(filePath, FenweiExcelNameG)
	} else if strings.Contains(fileName, FenweiExcelNameH) {// 样本钢厂焦炭-增量数据和历史数据excel格式相同
		indexes, e = AnalysisFenweiExcelYangBenSteelCoal(filePath)
	} else {
		utils.FileLog.Info(fmt.Sprintf("文件名有误, FileName: %s", fileName))
		return
	}
	if e != nil {
		utils.FileLog.Info(fmt.Sprintf("获取excel数据失败, FileName: %s, Err: %s", fileName, e))
		return
	}
	if len(indexes) == 0 {
		utils.FileLog.Info(fmt.Sprintf("fileName: %s, index empty", fileName))
		return
	}

	// 写入数据
	params := make(map[string]interface{})
	params["List"] = indexes
	params["TerminalCode"] = utils.TerminalCode
	result, e := PostEdbLib(params, utils.LIB_ROUTE_FENWEI_HANDLE)
	if e != nil {
		b, _ := json.Marshal(params)
		utils.FileLog.Info(fmt.Sprintf("fileName: %s, PostEdbLib err: %s, params: %s", fileName, e.Error(), string(b)))
		return
	}
	resp := new(models.BaseEdbLibResponse)
	if e := json.Unmarshal(result, &resp); e != nil {
		utils.FileLog.Info(fmt.Sprintf("fileName: %s, json.Unmarshal err: %s", fileName, e.Error()))
		return
	}
	if resp.Ret != 200 {
		utils.FileLog.Info(fmt.Sprintf("fileName: %s, Msg: %s, ErrMsg: %s", fileName, resp.Msg, resp.ErrMsg))
		return
	}
	return
}

// AnalysisFenweiNewExcelNormal 增量数据-动力煤/焦煤/焦炭
func AnalysisFenweiNewExcelNormal(filePath, excelName string) (indexes []*models.FenweiExcelIndex, err error) {
	defer func() {
		if err != nil {
			fmt.Printf("AnalysisFenweiNewExcelNormal ErrMsg: %s\n", err.Error())
			utils.FileLog.Info(fmt.Sprintf("AnalysisFenweiNewExcelNormal ErrMsg: %s", err.Error()))
		}
	}()

	xlsFile, e := xlsx.OpenFile(filePath)
	if e != nil {
		err = fmt.Errorf("OpenFile Err: %s", e.Error())
		return
	}

	// 获取存量指标, 增量数据只新增存量指标的数据
	originIndexes, e := GetFenweiIndexList()
	if e != nil {
		err = fmt.Errorf("GetFenweiIndexList err: %s", e.Error())
		return
	}
	indexMapping := make(map[string]models.BaseFromFenweiIndex)
	for _, v := range originIndexes {
		indexMapping[v.IndexCode] = v
	}

	diffNameReplace := FenweiNewDiffIndexNameReplaceMapping[excelName]
	ignoreIndexName := FenweiNewIgnoreIndexNameMapping[excelName]
	indexNameSuffix := FenweiNewIndexNameSuffixMapping[excelName]
	var dataRowMin, dataRowMax int // 数据起始行数
	if excelName == FenweiExcelNameA || excelName == FenweiExcelNameB {
		dataRowMin = 3
		dataRowMax = 6
	}
	if excelName == FenweiExcelNameC {
		dataRowMin = 3
		dataRowMax = 10
	}
	if excelName == FenweiExcelNameD {
		dataRowMin = 3
		dataRowMax = 13
	}

	var dataDate string
	secondRowNames := make(map[int]string) // 第二行-指标名称的上半部分
	thirdRowNames := make(map[int]string)  // 第三行-指标名称的另一部分
	indexes = make([]*models.FenweiExcelIndex, 0)
	indexColIndex := make(map[string]*models.FenweiExcelIndex) // 行-列对应的指标
	rowProvince := make(map[int]string)                        // 行对应的省份名
	for _, sheet := range xlsFile.Sheet {
		maxRow := sheet.MaxRow
		for i := 0; i < maxRow; i++ {
			cells := sheet.Row(i).Cells

			// 首行为数据日期
			if i == 0 {
				for _, cell := range cells {
					str := strings.TrimSpace(cell.String())
					if str == "" {
						continue
					}
					dateArr := strings.Split(str, "-")
					if len(dateArr) != 2 {
						err = fmt.Errorf("数据日期有误, len(dateArr): %d", len(dateArr))
						return
					}
					t, e := time.ParseInLocation("2006.1.2", dateArr[1], time.Local)
					if e != nil {
						t, e = time.ParseInLocation("2006.01.02", dateArr[1], time.Local)
						if e != nil {
							err = fmt.Errorf("数据日期有误, date: %s", dateArr[1])
							return
						}
					}
					dataDate = t.Format(utils.FormatDate)
					break
				}
			}

			// 第二行-指标名称上半部分
			if i == 1 {
				for k, cell := range cells {
					text := strings.TrimSpace(cell.String())
					if k > 0 && text != "" {
						// 纵向合并
						if cell.VMerge > 0 {
							thirdRowNames[k] = text
							continue
						}
						secondRowNames[k] = text
						// 横向合并
						if cell.HMerge > 0 {
							for j := 1; j <= cell.HMerge; j++ {
								if secondRowNames[k+j] != "" {
									continue
								}
								secondRowNames[k+j] = text
							}
						}
					}
				}
			}

			// 第三行-指标名称下半部分, 指标名称, 指标编码在这一步生成
			if i == 2 {
				for k, cell := range cells {
					text := strings.TrimSpace(cell.String())
					if k > 0 && text != "" {
						thirdRowNames[k] = text
					}
				}
			}

			// 数据行
			if i >= dataRowMin && i <= dataRowMax {
				for k, cell := range cells {
					text := strings.TrimSpace(cell.String())
					if text == "" {
						continue
					}
					// 第一个单元格为地区
					if k == 0 {
						if text == "合计" && (excelName == FenweiExcelNameA || excelName == FenweiExcelNameB) {
							text = "三省合计"
						}
						rowProvince[i] = FenweiNewExcelProvinceMapping[text]
						continue
					}
					rowsName := strings.TrimSpace(fmt.Sprint(secondRowNames[k], thirdRowNames[k]))
					rowsName = strings.ReplaceAll(rowsName, "\n", "")
					namePrefix := diffNameReplace[rowsName]
					if namePrefix == "" {
						namePrefix = rowsName
					}
					// 忽略部分名称
					if utils.InArrayByStr(ignoreIndexName, namePrefix) {
						continue
					}
					indexName := fmt.Sprintf("%s%s%s", rowProvince[i], namePrefix, indexNameSuffix)
					//fmt.Printf("k: %d, %s\n", k, indexName)
					name := strings.ReplaceAll(indexName, "/", "")
					var middle, suffix string
					for _, p := range FenweiProvinces {
						// 此处山西其他可能会被错误的给鉴别为山西, 所以优先鉴别一下山西其他
						if strings.Contains(name, "山西其他") {
							middle = utils.GetFullPingYin("山西其他")
							name = strings.ReplaceAll(name, p, "")
							break
						}
						if strings.Contains(name, p) {
							middle = utils.GetFullPingYin(p)
							name = strings.ReplaceAll(name, p, "")
							break
						}
					}
					if middle == "" {
						utils.FileLog.Info(fmt.Sprintf("指标名称转换编码有误, IndexName: %s", indexName))
						continue
					}
					suffix = utils.GetFirstPingYin(name)
					indexCode := fmt.Sprint(FenweiIndexNamePrefix, middle, suffix)

					// 根据转换的编码, 匹配存量指标, 无则忽略
					originIndex := indexMapping[indexCode]
					if originIndex.FenweiIndexId <= 0 {
						utils.FileLog.Info(fmt.Sprintf("无对应存量指标, IndexName: %s, IndexCode: %s", indexName, indexCode))
						continue
					}
					indexKey := fmt.Sprintf("%d-%d", i, k)
					if indexColIndex[indexKey] == nil {
						indexColIndex[indexKey] = new(models.FenweiExcelIndex)
						indexColIndex[indexKey].TerminalCode = utils.TerminalCode
						indexColIndex[indexKey].ClassifyId = originIndex.ClassifyId
						indexColIndex[indexKey].Frequency = originIndex.Frequency
						indexColIndex[indexKey].Unit = originIndex.Unit
						indexColIndex[indexKey].IndexName = indexName
						indexColIndex[indexKey].IndexCode = indexCode
						indexColIndex[indexKey].ExcelDataMap = make(map[string]string)
						indexColIndex[indexKey].ExcelDataMap[dataDate] = text
					}
				}
			}
		}

		// 只读取第一个sheet
		break
	}

	for _, v := range indexColIndex {
		if v.IndexCode == "" {
			continue
		}
		indexes = append(indexes, v)
	}
	return
}

// AnalysisFenweiExcelYangBen 样本钢厂焦炭
func AnalysisFenweiExcelYangBenSteelCoal(filePath string) (indexes []*models.FenweiExcelIndex, err error) {
	defer func() {
		if err != nil {
			fmt.Printf("AnalysisFenweiExcelYangBenSteel ErrMsg: %s\n", err.Error())
			utils.FileLog.Info(fmt.Sprintf("AnalysisFenweiExcelYangBenSteel ErrMsg: %s", err.Error()))
		}
	}()

	indexColIndex := make(map[int]*models.FenweiExcelIndex) // 列对应的指标(C列-D列)
	provinces := []string{"河北", "山东", "其他", "汇总"}
	for k, v := range provinces {
		//if k < 2 {
		//	continue
		//}
		if indexColIndex[k] == nil {
			indexColIndex[k] = new(models.FenweiExcelIndex)
			indexColIndex[k].ExcelDataMap = make(map[string]string)
			indexColIndex[k].TerminalCode = utils.TerminalCode
			indexColIndex[k].ClassifyId = FenweiYangBenSteelCoalIndexClassifyId
			indexColIndex[k].Frequency = "周度"
			indexColIndex[k].Unit = "天"
			indexColIndex[k].IndexName = fmt.Sprintf("%s/%s", FenweiYangBenSteelCoalIndexNamePre, v)

			// 指标编码, 例:样本焦企炼焦煤库存可用天数/全国->fwsjybjqljmkckytsquanguo
			name := strings.ReplaceAll(indexColIndex[k].IndexName, "/", "")
			var middle, suffix string
			for _, p := range provinces {
				if strings.Contains(name, p) {
					suffix = utils.GetFullPingYin(p)
					name = strings.ReplaceAll(name, p, "")
					break
				}
			}
			if suffix == "" {
				utils.FileLog.Info(fmt.Sprintf("指标名称转换编码有误, IndexName: %s", indexColIndex[k].IndexName))
				continue
			}
			middle = utils.GetFirstPingYin(name)
			indexColIndex[k].IndexCode = fmt.Sprint(FenweiIndexNamePrefix, middle, suffix)
		}
	}

	maxCols := 4      // 每行只读取前4列即可
	startDateRow := 0 // 数据日期起始行(第一行), 后面依次6行读取
	startDataRow := 3 // 数据值起始行(第四行), 后面依次加1行读取
	sumDateRow := 0   // 数据日期累加的行, 每加4次重置0
	sumDataRow := 0   // 数据值累加的行, 每加4次重置0
	currDate := ""    // 最近的一次日期
	stop := false     // 结束读取excel

	xlsxFile, e := xlsx.OpenFile(filePath)
	if e != nil {
		err = fmt.Errorf("open xls file err: %s", e.Error())
		return
	}
	sheet := xlsxFile.Sheets[0]
	//if e != nil {
	//	err = fmt.Errorf("GetSheet err: %s", e.Error())
	//	return
	//}
	rows := sheet.Rows
	for i, row := range rows {
		sumDateRow += 1
		sumDataRow += 1

		// 第一个日期行/后续每4行取一次日期
		if i == startDateRow || sumDateRow == 7 {
			if stop {
				break
			}
			sumDateRow = 0

			for k, cell := range row.Cells {
				// 日期只取第一个单元格中的
				if k > 0 {
					break
				}
				strDates := strings.TrimSpace(cell.String())
				strDates = strings.ReplaceAll(strDates, "\x00", "")
				strDateArr := strings.Split(strDates, "-")
				// 正常情况下, 长度不为2说明读取到了最后一行了
				if len(strDateArr) != 2 {
					utils.FileLog.Info(fmt.Sprintf("第%d行数据日期长度有误, 结束读取", i+1))
					stop = true
					break
				}
				t, e := time.Parse("2006.1.2", strDateArr[1])
				if e != nil {
					utils.FileLog.Info(fmt.Sprintf("第%d行数据日期有误, err: %s", i+1, e.Error()))
					break
				}
				currDate = t.Format(utils.FormatDate)
			}
		}

		// 第一个数据行/后续每4行取一次数据
		if i == startDataRow || sumDataRow > 3 {
			if currDate == "" {
				utils.FileLog.Info(fmt.Sprintf("第%d行数据对应日期有误", i+1))
				continue
			}

			for k, cell := range row.Cells {
				if k >= maxCols {
					break
				}
				if k == 2 {
					val,_ := cell.Float()
					// 保留四位小数
					provinceIndex := 0
					if sumDataRow > 3 {
						provinceIndex = sumDataRow - 4
					}
					indexColIndex[provinceIndex].ExcelDataMap[currDate] = fmt.Sprintf("%.4f", val)
					if sumDataRow == 7 {
						sumDataRow = 0
						// 重置日期
						currDate = ""
					}
					break
				}
			}

		}
	}

	indexes = make([]*models.FenweiExcelIndex, 0)
	for _, v := range indexColIndex {
		if v.IndexCode == "" {
			continue
		}
		indexes = append(indexes, v)
	}
	return
}