eta_crawler/services/commodity_coal_firm.go

package services

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

func FileCoalFirm() (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)
		}
	}()
	var xlFile *xlsx.File
	syear := time.Now().Year()
	var smonth string
	dateMonth := int(time.Now().Month())
	smonth = strconv.Itoa(dateMonth)
	//path := "static/file/firm.xlsx"
	path := "/home/code/python/coal_mail/emailFile/全国分企业煤炭产量旬度数据（%s年%s月）.xlsx"
	path = fmt.Sprintf(path,syear,smonth)
	exist,err := PathExists(path)
	if err != nil {
		fmt.Println(err)
		return
	}
	if exist == false {
		path = "/home/code/python/coal_mail/emailFile/全国分企业煤炭产量旬度数据（%s年%s月中旬）.xlsx"
		path = fmt.Sprintf(path,syear,smonth)
		exist,err = PathExists(path)
		if err != nil {
			fmt.Println(err)
			return
		}
	}else if exist == false {
		path = "/home/code/python/coal_mail/emailFile/全国分企业煤炭产量旬度数据（%s年%s月上旬）.xlsx"
		path = fmt.Sprintf(path,syear,smonth)
		exist,err = PathExists(path)
		if err != nil {
			fmt.Println(err)
			return
		}
	}else if exist == false {
		//本月三个文件都没有，去取上一个月的
		dateMonth = dateMonth - 1
		smonth = strconv.Itoa(dateMonth)
		path = "/home/code/python/coal_mail/emailFile/全国分企业煤炭产量旬度数据（%s年%s月）.xlsx"
		path = fmt.Sprintf(path,syear,smonth)
		exist,err = PathExists(path)
		if err != nil {
			fmt.Println(err)
			return
		}
	}


	path = fmt.Sprintf(path,syear,smonth)
	xlFile, err = xlsx.OpenFile(path)
	if err != nil {
		fmt.Println(err)
		return
	}

	var year, month, day string
	var yearMap = make(map[int]string)
	var monthMap = make(map[int]string)
	var dateMap = make(map[int]string)
	var mappingItems []*models.BaseFromCoalmineMapping
	var indexItems []*models.BaseFromCoalmineFirmIndex
	var codeMap = make(map[string]string)
	var indexMap = make(map[string]string)
	var groupName 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.GetBaseFromCoalmineFirmIndex()
	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 == 2 {
				row := sheet.Row(i)
				cells := row.Cells
				for k, cell := range cells {
					text := cell.String()
					if k > 0 && text != "" {
						year = text
						yearMap[k] = year
					}
				}
			}
			//获取月份
			if i == 3 {
				row := sheet.Row(i)
				cells := row.Cells
				for k, cell := range cells {
					text := cell.String()
					if k > 0 && text != "" {
						month = text
						monthMap[k] = month
					}
				}
			}
			//获取旬度
			if i == 4 {
				row := sheet.Row(i)
				cells := row.Cells
				for k, cell := range cells {
					text := cell.String()
					if k > 0 && text != "" {
						if yearMap[k] != "" {
							year = yearMap[k]
						}
						if monthMap[k] != "" {
							month = monthMap[k]
						}
						day = text
						dateMap[k] = year + month + day
					}
				}
			}
			//获取企业或地区名及信息
			if i > 4 {
				row := sheet.Row(i)
				cells := row.Cells
				var companyName string
				for k, cell := range cells {
					if k == 0 {
						companyName = cell.String()

						//省名
						if !strings.Contains(companyName, " ") {
							groupName = companyName
						}
						if groupName == "全国合计" {
							groupName = "全国"
						}
						//若有下面四个名称要拼上省名不然会重复
						if companyName == "  地方合计" || companyName == "    地方国有" ||
							companyName == "    地方乡镇" || companyName == "  国有重点" {
							companyName = groupName + companyName
						}
						companyName = strings.Replace(companyName, " ", "", -1)
						var item models.BaseFromCoalmineMapping
						//生成code
						exists := ContainsSpecialName(companyName)
						var code string
						if exists {
							abbr := trimProvinceName(companyName)
							//取处理后公司名首字母缩写
							a := pinyin.NewArgs()
							rows := pinyin.Pinyin(companyName[9:], 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[:2] + code + "firm"

						} else {
							a := pinyin.NewArgs()
							rows := pinyin.Pinyin(companyName, 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 + "firm"
						}
						item.IndexName = companyName
						item.CreateTime = time.Now()
						mappingItems = append(mappingItems, &item)
					} else {
						dealValue := cell.String()

						item := models.BaseFromCoalmineFirmIndex{
							IndexName:                   companyName,
							IndexCode:                   codeMap[companyName],
							DataTime:                    dateMap[k],
							DealValue:                   dealValue,
							GroupName:                   groupName,
							Source:                      "全国分企业",
							Unit:                        "万吨",
							Frequency:                   "旬度",
							CreateTime:                  time.Now(),
							ModifyTime:                  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.AddBaseFromCoalFirmIndex(v)
			if err != nil {
				fmt.Println("添加数据错误", err)
			} else {
				fmt.Println("新增成功", newId)
			}
		} else {
			if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
				err = models.UpdateBaseFromCoalFirmIndex(v)
				if err != nil {
					fmt.Println("修改数据错误错误", err)
					return
				}
			}
		}
	}
	return
}

func trimProvinceName(name string) string {
	name = strings.Replace(name, "陕西省", "sn", -1)
	name = strings.Replace(name, "陕西", "sn", -1)
	name = strings.Replace(name, "海南市", "hi", -1)
	name = strings.Replace(name, "海南", "hi", -1)
	name = strings.Replace(name, "河南省", "ha", -1)
	name = strings.Replace(name, "河南", "ha", -1)
	name = strings.Replace(name, "河北省", "he", -1)
	name = strings.Replace(name, "河北", "he", -1)
	name = strings.Replace(name, "澳门", "mo", -1)
	name = strings.Replace(name, "内蒙古自治区", "nm", -1)
	name = strings.Replace(name, "内蒙古", "nm", -1)
	name = strings.Replace(name, "黑龙江", "hl", -1)
	name = strings.Replace(name, "（", "", -1)
	name = strings.Replace(name, "）", "", -1)
	name = strings.Replace(name, "+", "", -1)
	return name
}


func PathExists(path string)(bool, error) {
	_, err := os.Stat(path)
	if err == nil { //文件或者目录存在
		return true, nil
	}
	if os.IsNotExist(err) {
		return false, nil
	}
	return false, err
}