eta_crawler/services/commodity_coal_inland.go

package services

import (
	"fmt"
	"github.com/mozillazg/go-pinyin"
	"github.com/tealeg/xlsx"
	"hongze/hongze_data_crawler/models"
	"hongze/hongze_data_crawler/utils"
	"strconv"
	"time"
)

func FileCoalInland() (err error) {
	defer func() {
		if err != nil {
			fmt.Println("RefreshDataFromDalian Err:" + err.Error())
			go utils.SendEmail(utils.APPNAME+"【"+utils.RunMode+"】"+"失败提醒", "RefreshDataFromDalian ErrMsg:"+err.Error(), utils.EmailSendToUsers)
		}
	}()
	path := "/home/code/python/coal_mail/emailFile/内陆17省动力煤终端用户供耗存%s.xlsx"
	path = fmt.Sprintf(path,time.Now().Format(utils.FormatDateUnSpace))
	xlFile, err := xlsx.OpenFile(path)
	if err != nil {
		fmt.Println(err)
		return
	}

	var mappingItems []*models.BaseFromCoalmineMapping
	var indexItems []*models.BaseFromCoalmineInlandIndex
	var codeMap = make(map[string]string)
	var indexMap = make(map[string]string)
	var nameMap = make(map[int]string)
	var groupMap = make(map[int]string)
	var dataTime string
	var sheetName string
	codeList, err := models.GetBaseFromCoalmineMapping()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭指标失败：", err)
		return err
	}
	if len(codeList) > 0 {
		for _, v := range codeList {
			codeMap[v.IndexName] = v.IndexCode
		}
	}
	indexCompanyList, err := models.GetBaseFromCoalmineInlandIndex()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭公司指标失败：", err)
		return err
	}
	if len(indexCompanyList) > 0 {
		for _, v := range indexCompanyList {
			indexMap[v.IndexName+v.DataTime] = v.DealValue
		}
	}

	for s, sheet := range xlFile.Sheets {
		sheetName = sheet.Name
		//遍历行读取
		maxCol := sheet.MaxCol
		for i := 0; i < maxCol; i++ {
			if i == 0 {
				row := sheet.Row(i)
				cells := row.Cells
				for k, cell := range cells {
					if k > 0 && k < 18 {
						text := cell.String()
						groupMap[k] = text
						var item models.BaseFromCoalmineMapping
						//合计命名
						if k == 9 {
							item.IndexName = sheetName
						}
						item.IndexName = text + sheetName
						nameMap[k] = item.IndexName
						//code
						exists := ContainsSpecialName(text)
						var code string
						if exists {
							abbr := trimProvinceName(text)
							//取处理后公司名首字母缩写
							a := pinyin.NewArgs()
							rows := pinyin.Pinyin(sheetName, a)
							for i := 0; i < len(rows); i++ {
								if len(rows[i]) != 0 {
									str := rows[i][0]
									pi := str[0:1]
									code += pi
								}
							}
							item.IndexCode = abbr + code + "inland"
						} else {
							a := pinyin.NewArgs()
							rows := pinyin.Pinyin(text+sheetName, a)
							for i := 0; i < len(rows); i++ {
								if len(rows[i]) != 0 {
									str := rows[i][0]
									pi := str[0:1]
									code += pi
								}
							}
							item.IndexCode = code + "inland"
						}
						item.CreateTime = time.Now()
						mappingItems = append(mappingItems, &item)
					}
				}
			}
			if i > 0 {
				row := sheet.Row(i)
				cells := row.Cells
				for k, cell := range cells {
					if k < 18 {
						var item models.BaseFromCoalmineInlandIndex
						if k == 0 {
							if s == 0 {
								text := cell.String()
								t, _ := time.ParseInLocation("01-02-06", text, time.Local)
								dataTime = t.Format(utils.FormatDate)
							} else {
								text := cell.String()
								dataTime = ExcelDateToDate(text).Format(utils.FormatDate)
							}
						}
						if k > 0 {
							text := cell.String()
							item.IndexName = nameMap[k]
							item.IndexCode = codeMap[nameMap[k]]
							item.DealValue = text
							item.GroupName = groupMap[k]
							item.Source = "内陆十七省"
							item.DataTime = dataTime
							item.Unit = "万吨"
							item.Frequency = "日度"
							item.ModifyTime = time.Now()
							item.CreateTime = time.Now()
						}
						indexItems = append(indexItems, &item)
					}
				}
			}
		}
	}
	//添加数据到数据库
	for _, v := range mappingItems {
		if codeMap[v.IndexName] == "" {
			codeMap[v.IndexName] = v.IndexCode
			newId, err := models.AddBaseFromCoalmineMapping(v)
			if err != nil {
				for i := 0; i<10; i++ {
					v.IndexCode = v.IndexCode + strconv.Itoa(i)
					codeMap[v.IndexName] = v.IndexCode
					newId, err := models.AddBaseFromCoalmineMapping(v)
					if err != nil {
						fmt.Println("再次添加公司指标名称错误", err)
						continue
					} else {
						fmt.Println("新增公司成功", newId)
						break
					}
				}
			} else {
				fmt.Println("新增公司成功", newId)
			}
		}
	}
	fmt.Println("指标操作完成")

	//给indexItem中的code赋值并插入index表
	for _, v := range indexItems {
		v.IndexCode = codeMap[v.IndexName]
		if indexMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
			newId, err := models.AddBaseFromCoalInlandIndex(v)
			if err != nil {
				fmt.Println("添加数据错误", err)
			} else {
				fmt.Println("新增成功", newId)
			}
		} else {
			if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
				err = models.UpdateBaseFromCoalInlandIndex(v)
				if err != nil {
					fmt.Println("修改数据错误错误", err)
					return
				}
			}
		}
	}
	return
}