eta_crawler/services/commodity_coal_mine.go

package services

import (
	"fmt"
	"github.com/tealeg/xlsx"
	"hongze/hongze_data_crawler/models"
	"hongze/hongze_data_crawler/utils"
	"strings"
	"time"
)

func FileCoalJsm() (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 := "static/file/jsm.xlsx"
	xlFile, err := xlsx.OpenFile(path)
	if err != nil {
		return
	}

	var source string
	var province string
	var description string
	var exchange string
	var rank string
	var city string
	var companyName string
	var group string

	var dateMap = make(map[int]string)
	var codeMap = make(map[string]string)
	var indexMap = make(map[string]string)
	var codeCompanyMap = make(map[string]string)
	var items []*models.BaseFromCoalmineMapping
	var itemsCompany []*models.BaseFromCoalmineCompanyMapping
	var itemsIndex []*models.BaseFromCoalmineIndex
	var itemsCompanyIndex []*models.BaseFromCoalmineCompanyIndex
	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
		}
	}

	codeCompanyList, err := models.GetBaseFromCoalmineCompanyMapping()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭公司指标失败：", err)
		return err
	}
	if len(codeCompanyList) > 0 {
		for _, v := range codeCompanyList {
			codeCompanyMap[v.IndexName] = v.IndexCode
		}
	}

	indexCompanyList, err := models.GetBaseFromCoalmineIndex()
	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 _, sheet := range xlFile.Sheets {
		//遍历行读取
		maxRow := sheet.MaxRow
		for i := 0; i < maxRow; i++ {
			//获取制表来源
			if i == 1 {
				row := sheet.Row(i)
				cells := row.Cells
			Loop:
				for k, cell := range cells {
					text := cell.String()
					if k == 0 {
						source = text
						break Loop
					}
				}
				if source != "" {
					source = strings.Replace(source, "制表单位:", "", -1)
				}
			}
			//获取样本情况
			if i > 3 && i < 16 {
				row := sheet.Row(i)
				cells := row.Cells
			Loop2:
				for k, cell := range cells {
					text := cell.String()
					if text != "" {
						if k == 1 {
							province = text
						}
						if k == 3 {
							description = text
						}
						if k == 4 {
							item := new(models.BaseFromCoalmineMapping)
							exchange = text
							item.IndexName = source + "_" + province + "_" + exchange
							item.IndexCode = utils.MD5(item.IndexName)
							item.Exchange = exchange
							item.Description = description
							item.Province = province
							item.Source = source
							item.CreateTime = time.Now()
							items = append(items, item)
							break Loop2
						}
					}
				}
			}
			//获取日期
			if i == 3 {
				row := sheet.Row(i)
				cells := row.Cells
				for k, cell := range cells {
					text := cell.String()
					stamp, _ := time.ParseInLocation("01-02-06", text, time.Now().Location())
					if k > 4 {
						dateMap[k] = stamp.Format(utils.FormatDate)
					}
				}
			}
			//获取具体产量
			if i > 3 && i < 16 {
				row := sheet.Row(i)
				cells := row.Cells
				for k, cell := range cells {
					text := cell.String()
					if k > 4 {
						item := new(models.BaseFromCoalmineIndex)
						item.IndexName = source + "_" + province + "_" + exchange
						item.IndexCode = codeMap[item.IndexName]
						item.Exchange = exchange
						item.DealValue = text
						item.DataTime = dateMap[k]
						item.ModifyTime = time.Now()
						item.CreateTime = time.Now()
						itemsIndex = append(itemsIndex, item)
					}
				}
			}

			//获取公司指标名称
			if i > 17 {
				row := sheet.Row(i)
				cells := row.Cells
			Loop3:
				for k, cell := range cells {
					text := cell.String()
					if text != "" {
						if k == 0 {
							rank = text
						}
						if k == 1 {
							province = text
						}
						if k == 2 {
							city = text
						}
						if k == 3 {
							companyName = text
						}
						if k == 4 {
							item := new(models.BaseFromCoalmineCompanyMapping)
							group = text
							item.IndexName = source + "_" + province + "_" + city + "_" + companyName
							item.IndexCode = utils.MD5(item.IndexName)
							item.Rank = rank
							item.Province = province
							item.City = city
							item.CompanyName = companyName
							item.GroupName = group
							item.Source = source
							item.CreateTime = time.Now()
							itemsCompany = append(itemsCompany, item)
							break Loop3
						}
					}
				}
			}

			//获取公司具体产量
			if i > 18 {
				row := sheet.Row(i)
				cells := row.Cells
				for k, cell := range cells {
					text := cell.String()
					if k > 4 {
						item := new(models.BaseFromCoalmineCompanyIndex)
						item.IndexName = source + "_" + province + "_" + city + "_" + companyName
						item.IndexCode = codeCompanyMap[item.IndexName]
						item.DealValue = text
						item.DataTime = dateMap[k]
						item.ModifyTime = time.Now()
						item.CreateTime = time.Now()
						//fmt.Println(item)
						itemsCompanyIndex = append(itemsCompanyIndex, item)
					}
				}
			}
		}
	}

	//添加数据到数据库
	for _, v := range items {
		if codeMap[v.IndexName] == "" {
			codeMap[v.IndexName] = v.IndexCode
			newId, err := models.AddBaseFromCoalmineMapping(v)
			if err != nil {
				fmt.Println("添加指标名称错误")
			} else {
				fmt.Println("添加指标名称成功", newId)
			}
		}
	}
	fmt.Println("指标操作完成")

	for _, v := range itemsIndex {
		v.IndexCode = codeMap[v.IndexName]
		if indexMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
			newId, err := models.AddBaseFromCoalmineIndex(v)
			if err != nil {
				fmt.Println("添加数据错误", err)
			} else {
				fmt.Println("新增成功", newId)
			}
		} else {
			if indexMap[v.IndexName+v.DataTime] == v.IndexName+v.DataTime && indexMap[v.IndexName+v.DataTime] != v.DealValue {
				err = models.UpdateBaseFromCoalmineIndex(v)
				if err != nil {
					fmt.Println("修改数据错误错误", err)
					return
				}
			}
		}
	}
	fmt.Println("数据操作完成")

	for _, v := range itemsCompany {
		if codeCompanyMap[v.IndexName] == "" {
			codeCompanyMap[v.IndexName] = v.IndexCode
			newId, err := models.AddBaseFromCoalmineCompanyMapping(v)
			if err != nil {
				fmt.Println("添加公司指标名称错误", err)
			} else {
				fmt.Println("新增公司成功", newId)
			}
		}
	}
	fmt.Println("公司指标操作完成")

	//for _, v := range itemsCompanyIndex {
	//	newId, err := models.AddBaseFromCoalmineCompanyIndex(v)
	//	if err != nil {
	//		fmt.Println("添加公司数据错误", err)
	//return
	//	} else {
	//		fmt.Println("新增公司数据成功", newId)
	//	}
	//}
	return
}