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/xuri/excelize/v2"
	"path"
	"strings"
	"time"
)

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

	FenWeiExcelHistoryNameA = "动力煤产销存-66家"
	FenWeiExcelHistoryNameB = "动力煤产销存-100家"
	FenWeiExcelHistoryNameC = "焦煤产销存-53家"
	FenWeiExcelHistoryNameD = "焦煤产销存-88家"
	FenWeiExcelHistoryNameE = "样本焦化厂焦煤"

	FenWeiYangBenIndexNamePre    = "样本焦企炼焦煤库存可用天数"
	FenWeiYangBenIndexClassifyId = 5
)

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

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

// FenWeiFirstClassifyMap excel表名->一级分类ID
var FenWeiFirstClassifyMap = map[string]int{
	FenWeiExcelHistoryNameA: 1,
	FenWeiExcelHistoryNameB: 2,
	FenWeiExcelHistoryNameC: 3,
	FenWeiExcelHistoryNameD: 4,
	FenWeiExcelHistoryNameE: 5,
}

// 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, FenWeiExcelHistoryNameA) {
		indexes, e = AnalysisFenWeiHistoryExcelNormal(filePath, FenWeiExcelHistoryNameA)
	} else if strings.Contains(fileName, FenWeiExcelHistoryNameB) {
		indexes, e = AnalysisFenWeiHistoryExcelNormal(filePath, FenWeiExcelHistoryNameB)
	} else if strings.Contains(fileName, FenWeiExcelHistoryNameC) {
		indexes, e = AnalysisFenWeiHistoryExcelNormal(filePath, FenWeiExcelHistoryNameC)
	} else if strings.Contains(fileName, FenWeiExcelHistoryNameD) {
		indexes, e = AnalysisFenWeiHistoryExcelNormal(filePath, FenWeiExcelHistoryNameD)
	} else if strings.Contains(fileName, FenWeiExcelHistoryNameE) {
		indexes, e = AnalysisFenWeiExcelYangBen(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.FenweiTerminalCode
	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列-?列)
	for i, row := range rows {
		// 首行为标题, 忽略
		if i == 0 {
			continue
		}

		// 第二行-指标名称
		if i == 1 {
			for k, cell := range row {
				if k == 0 {
					continue
				}
				name := strings.TrimSpace(cell)
				if name == "" {
					break
				}
				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 := range row {
				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, 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
			}
			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
		}
	}

	for _, v := range indexColIndex {
		if v.IndexCode == "" {
			continue
		}
		indexes = append(indexes, 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()
				indexColIndex[k].ExcelDataMap[currDate] = fmt.Sprint(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
}

// 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, FenWeiExcelHistoryNameA) {
		indexes, e = AnalysisFenWeiNewExcelNormal(filePath, FenWeiExcelHistoryNameA)
	} else if strings.Contains(fileName, FenWeiExcelHistoryNameB) {
		indexes, e = AnalysisFenWeiNewExcelNormal(filePath, FenWeiExcelHistoryNameB)
	} else if strings.Contains(fileName, FenWeiExcelHistoryNameC) {
		indexes, e = AnalysisFenWeiNewExcelNormal(filePath, FenWeiExcelHistoryNameC)
	} else if strings.Contains(fileName, FenWeiExcelHistoryNameD) {
		indexes, e = AnalysisFenWeiNewExcelNormal(filePath, FenWeiExcelHistoryNameD)
	} else if strings.Contains(fileName, FenWeiExcelHistoryNameE) {
		indexes, e = AnalysisFenWeiExcelYangBen(filePath) // 样本焦化厂-增量数据和历史数据excel格式相同
	} 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.FenweiTerminalCode
	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
}

// TODO: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()))
		}
	}()

	return
}