eta_index_lib/controllers/commodity_coal.go

package controllers

import (
	"encoding/json"
	"eta/eta_index_lib/models"
	"eta/eta_index_lib/utils"
	"fmt"
	"github.com/mozillazg/go-pinyin"
	"os"
	"strconv"
	"strings"
	"time"
)

// 煤炭网
type CoalMineDataController struct {
	BaseAuthController
}

// @Title 刷新数据
// @Description 刷新数据接口
// @Param	request	body models.CoalMineDataReq true "type json string"
// @Success 200 {object} models.EdbClassify
// @router /jsm/history [post]
func (this *CoalMineDataController) JsmHistory() {
	br := new(models.BaseResponse).Init()
	defer func() {
		this.Data["json"] = br
		this.ServeJSON()
	}()
	var req models.CoalMineDataReq

	err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
	if err != nil {
		br.Msg = "参数解析异常！"
		br.ErrMsg = "参数解析失败，Err:" + err.Error()
		return
	}
	//path := "/home/code/python/coal_mail/emailFile/沿海八省动力煤终端用户供耗存数据更新.xlsx"

	var mappingItems []*models.BaseFromCoalmineMapping
	var indexItems []*models.BaseFromCoalmineJsmIndex
	var codeMap = make(map[string]string)
	var indexMap = make(map[string]string)
	var nameMap = make(map[int]string)
	var coalInfoMap = make(map[string][]string)
	var dataTime string
	var unit string
	var province string
	var exchange string

	codeList, err := models.GetBaseFromCoalmineMapping()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭指标失败：", err)
		return
	}
	if len(codeList) > 0 {
		for _, v := range codeList {
			codeMap[v.IndexName] = v.IndexCode
		}
	}
	indexCompanyList, err := models.GetBaseFromCoalmineIndex()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭公司指标失败：", err)
		return
	}
	if len(indexCompanyList) > 0 {
		for _, v := range indexCompanyList {
			indexMap[v.IndexName+v.DataTime] = v.DealValue
		}
	}

	sheet := req.SheetData[0]

	//遍历行读取
	maxCol := sheet.MaxRow
	for i := 0; i < maxCol; i++ {
		if i == 0 {
			row := sheet.Rows[i]
			cells := row.Cells

			for k, cell := range cells {
				text := cell.Value
				if k > 0 {
					switch k {
					case 1:
						province = "山西"
						text = strings.TrimLeft(text, "山西")
					case 4:
						province = "内蒙古"
						text = strings.TrimLeft(text, "内蒙古")
					case 7:
						province = "陕西"
						text = strings.TrimLeft(text, "陕西")
					case 10:
						province = "442家"
						text = "产量"
					}
					if k != 1 && k != 4 && k != 7 && k != 10 {
						unit = "%"
					} else {
						unit = "万吨"
					}

					exchange = text
					text = province + text

					var item models.BaseFromCoalmineMapping
					item.IndexName = text
					nameMap[k] = item.IndexName
					coalInfoMap[item.IndexName] = append(coalInfoMap[item.IndexName], exchange, province, unit)
					//code
					exists := ContainsSpecialName(province)
					var code string
					if exists {
						abbr := trimProvinceName(text)
						provinceAbbr := trimProvinceName(province)
						//取处理后公司名首字母缩写
						a := pinyin.NewArgs()
						rows := pinyin.Pinyin(abbr, 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 = provinceAbbr + code + "jsm"
					} else {
						a := pinyin.NewArgs()
						rows := pinyin.Pinyin(text, 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 + "jsm"
					}
					item.CreateTime = time.Now()
					mappingItems = append(mappingItems, &item)
				}
			}
		}
		if i > 0 {
			row := sheet.Rows[i]
			cells := row.Cells
			for k, cell := range cells {
				var item models.BaseFromCoalmineJsmIndex
				if k == 0 {
					text := cell.Value
					parsedTime, err := time.Parse("01-02-06", text)
					if err != nil {
						fmt.Println("解析时间字符串出错:", err)
						return
					}
					dataTime = parsedTime.Format(utils.FormatDate)
				}
				if k > 0 {
					text := cell.Value
					item.IndexName = nameMap[k]
					item.IndexCode = codeMap[nameMap[k]]
					item.DealValue = text
					item.Source = "三省周度"
					item.DataTime = dataTime
					item.Frequency = "周度"
					item.ModifyTime = time.Now()
					item.CreateTime = time.Now()
					if len(coalInfoMap[item.IndexName]) == 3 {
						item.Exchange = coalInfoMap[item.IndexName][0]
						item.Province = coalInfoMap[item.IndexName][1]
						item.Unit = coalInfoMap[item.IndexName][2]
					}
					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.AddBaseFromCoalmineIndex(v)
			if err != nil {
				fmt.Println("添加数据错误", err)
			} else {
				fmt.Println("新增成功", newId)
			}
		} else {
			if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
				err = models.UpdateBaseFromCoalmineIndex(v)
				if err != nil {
					fmt.Println("修改数据错误错误", err)
					return
				}
			}
		}
	}

	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	return
}

// @Title 刷新数据
// @Description 刷新数据接口
// @Param	request	body models.CoalMineDataReq true "type json string"
// @Success 200 {object} models.EdbClassify
// @router /coastal/history [post]
func (this *CoalMineDataController) CoastalHistory() {
	br := new(models.BaseResponse).Init()
	defer func() {
		this.Data["json"] = br
		this.ServeJSON()
	}()
	var req models.CoalMineDataReq

	err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
	if err != nil {
		br.Msg = "参数解析异常！"
		br.ErrMsg = "参数解析失败，Err:" + err.Error()
		return
	}

	var mappingItems []*models.BaseFromCoalmineMapping
	var indexItems []*models.BaseFromCoalmineCoastalIndex
	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 unit string
	codeList, err := models.GetBaseFromCoalmineMapping()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭指标失败：", err)
		return
	}
	if len(codeList) > 0 {
		for _, v := range codeList {
			codeMap[v.IndexName] = v.IndexCode
		}
	}
	indexCompanyList, err := models.GetBaseFromCoalmineCoastalIndex()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭公司指标失败：", err)
		return
	}
	if len(indexCompanyList) > 0 {
		for _, v := range indexCompanyList {
			indexMap[v.IndexName+v.DataTime] = v.DealValue
		}
	}

	for ii, sheet := range req.SheetData {
		//遍历行读取
		maxCol := len(sheet.Rows)
		for i := 0; i < maxCol; i++ {
			if i == 0 {
				row := sheet.Rows[i]
				cells := row.Cells
				for k, cell := range cells {
					ignore := 1
					ignore2 := 10
					if ii == 3 {
						// 第四页供煤没有星期，所以只跳过第一行
						ignore = 0
						ignore2 = 9
					}
					if k > ignore && k < ignore2 {
						text := cell.Value
						text = strings.Replace(text, ":", "", -1)
						text = strings.Replace(text, "：", "", -1)
						if ii == 3 {
							text += "供煤"
						}
						if ii == 2 {
							text += "天数"
						}
						province := strings.Replace(text, "日耗", "", -1)
						province = strings.Replace(province, "库存", "", -1)
						province = strings.Replace(province, "可用天数", "", -1)
						province = strings.Replace(province, "供煤", "", -1)
						groupMap[k] = province
						var item models.BaseFromCoalmineMapping
						item.IndexName = text
						//合计命名
						nameMap[k] = item.IndexName
						//code
						exists := ContainsSpecialName(text)
						var code string
						if exists {
							abbr := trimProvinceName(text)
							//取处理后公司名首字母缩写
							a := pinyin.NewArgs()
							rows := pinyin.Pinyin(abbr, 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 + "coastal"
						} else {
							a := pinyin.NewArgs()
							rows := pinyin.Pinyin(item.IndexName, 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 + "coastal"
						}
						item.CreateTime = time.Now()
						mappingItems = append(mappingItems, &item)
					}
					if ii == 2 {
						unit = "天"
					} else {
						unit = "万吨"
					}
				}
			}
			if i > 0 {
				row := sheet.Rows[i]
				cells := row.Cells
				for k, cell := range cells {
					ingore := 10
					if ii == 3 {
						ingore = 9
					}
					if k < ingore {
						var item models.BaseFromCoalmineCoastalIndex
						if k == 0 {
							text := cell.Value
							if text == "" {
								continue
							}
							if len(text) < 6 {
								dataTime = ExcelDateToDate(text).Format(utils.FormatDate)
							} else {
								parsedTime, err := time.Parse("01-02-06", text)
								if err != nil {
									parsedTime, err = time.Parse("2006/01/02", text)
									if err != nil {
										fmt.Println("解析时间字符串出错:", err)
										return
									}
								}
								dataTime = parsedTime.Format(utils.FormatDate)
							}
						}
						ignore := 1
						if ii == 3 {
							// 第四页供煤没有星期，所以只跳过第一行
							ignore = 0
						}
						if k > ignore {
							text := cell.Value
							item.IndexName = nameMap[k]
							item.IndexCode = codeMap[nameMap[k]]
							item.DealValue = text
							item.GroupName = groupMap[k]
							item.Source = "沿海八省"
							item.DataTime = dataTime
							item.Frequency = "日度"
							item.ModifyTime = time.Now()
							item.CreateTime = time.Now()
							item.Unit = unit
							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.AddBaseFromCoalCoastalIndex(v)
			if err != nil {
				fmt.Println("添加数据错误", err)
			} else {
				fmt.Println("新增成功", newId)
			}
		} else {
			if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
				err = models.UpdateBaseFromCoalCoastalIndex(v)
				if err != nil {
					fmt.Println("修改数据错误错误", err)
					return
				}
			}
		}
	}
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	return
}

// @Title 刷新数据
// @Description 刷新数据接口
// @Param	request	body models.CoalMineDataReq true "type json string"
// @Success 200 {object} models.EdbClassify
// @router /inland/history [post]
func (this *CoalMineDataController) InlandHistory() {
	br := new(models.BaseResponse).Init()
	defer func() {
		this.Data["json"] = br
		this.ServeJSON()
	}()
	var req models.CoalMineDataReq

	err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
	if err != nil {
		br.Msg = "参数解析异常！"
		br.ErrMsg = "参数解析失败，Err:" + err.Error()
		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
	}
	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
	}
	if len(indexCompanyList) > 0 {
		for _, v := range indexCompanyList {
			indexMap[v.IndexName+v.DataTime] = v.DealValue
		}
	}

	for _, sheet := range req.SheetData {
		sheetName = sheet.Name
		fmt.Println("sheetName:", sheetName)
		var unit string
		sheetName = strings.TrimLeft(sheetName, "内陆")
		if sheetName == "可用天数" {
			unit = "天"
		} else {
			unit = "万吨"
		}
		//遍历行读取
		maxCol := sheet.MaxRow
		for i := 0; i < maxCol; i++ {
			if i == 0 {
				row := sheet.Rows[i]
				cells := row.Cells
				for k, cell := range cells {
					if k > 0 && k < 18 {
						text := cell.Value
						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.Rows[i]
				cells := row.Cells
				for k, cell := range cells {
					if k < 18 {
						var item models.BaseFromCoalmineInlandIndex
						if k == 0 {
							text := cell.Value
							if text == "" {
								continue
							}
							if len(text) < 6 {
								dataTime = ExcelDateToDate(text).Format(utils.FormatDate)
							} else {
								parsedTime, err := time.Parse("01-02-06", text)
								if err != nil {
									parsedTime, err = time.Parse("2006/01/02", text)
									if err != nil {
										fmt.Println("解析时间字符串出错:", err)
										return
									}
								}
								dataTime = parsedTime.Format(utils.FormatDate)
							}
						}
						if k > 0 {
							text := cell.Value
							item.IndexName = nameMap[k]
							item.IndexCode = codeMap[nameMap[k]]
							item.DealValue = text
							item.GroupName = groupMap[k]
							item.Source = "内陆十七省"
							item.DataTime = dataTime
							item.Unit = 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
				}
			}
		}
	}
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	return
}

// @Title 刷新数据
// @Description 刷新数据接口
// @Param	request	body models.CoalMineDataReq true "type json string"
// @Success 200 {object} models.EdbClassify
// @router /jsm [post]
func (this *CoalMineDataController) Jsm() {
	br := new(models.BaseResponse).Init()
	defer func() {
		this.Data["json"] = br
		this.ServeJSON()
	}()
	var req models.CoalMineDataReq

	err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
	if err != nil {
		br.Msg = "参数解析异常！"
		br.ErrMsg = "参数解析失败，Err:" + err.Error()
		return
	}
	//path := "/home/code/python/coal_mail/emailFile/沿海八省动力煤终端用户供耗存数据更新.xlsx"

	var province string
	var description string
	var exchange 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 indexCompanyMap = make(map[string]string)
	var items []*models.BaseFromCoalmineMapping
	var itemsCompany []*models.BaseFromCoalmineMapping
	var itemsIndex []*models.BaseFromCoalmineJsmIndex
	var itemsCompanyIndex []*models.BaseFromCoalmineCompanyIndex
	codeList, err := models.GetBaseFromCoalmineMapping()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭指标失败：", err)
		return
	}

	if len(codeList) > 0 {
		for _, v := range codeList {
			codeMap[v.IndexName] = v.IndexCode
		}
	}

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

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

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

	for _, sheet := range req.SheetData {
		//遍历行读取
		maxRow := sheet.MaxRow
		for i := 0; i < maxRow; i++ {
			//获取样本情况
			if i > 3 && i < 16 {
				row := sheet.Rows[i]
				cells := row.Cells
			Loop2:
				for k, cell := range cells {
					text := cell.Value
					if text != "" {
						if k == 1 {
							province = text
						}
						if k == 3 {
							description = text
						}
						if k == 4 {
							item := new(models.BaseFromCoalmineMapping)
							exchange = text

							exists := ContainsSpecialName(province)
							var strResult string
							if exists {
								abbr := TrimProvinceName(province)
								//取处理后公司名首字母缩写
								a := pinyin.NewArgs()
								rows := pinyin.Pinyin(exchange, a)
								for i := 0; i < len(rows); i++ {
									if len(rows[i]) != 0 {
										str := rows[i][0]
										pi := str[0:1]
										strResult += pi
									}
								}
								item.IndexCode = abbr + strResult + "jsm"
							} else {
								//取处理后公司名首字母缩写
								a := pinyin.NewArgs()
								rows := pinyin.Pinyin(province+exchange, a)
								for i := 0; i < len(rows); i++ {
									if len(rows[i]) != 0 {
										str := rows[i][0]
										pi := str[0:1]
										strResult += pi
									}
								}
								item.IndexCode = strResult + "jsm"
							}

							item.IndexName = province + exchange

							item.CreateTime = time.Now()
							items = append(items, item)
							break Loop2
						}
					}
				}
			}
			//获取日期
			if i == 3 {
				row := sheet.Rows[i]
				cells := row.Cells
				for k, cell := range cells {
					text := cell.Value
					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.Rows[i]
				cells := row.Cells
				for k, cell := range cells {
					text := cell.Value
					if k > 4 {
						item := new(models.BaseFromCoalmineJsmIndex)
						item.IndexName = province + exchange
						item.IndexCode = codeMap[item.IndexName]
						item.Exchange = exchange
						item.DealValue = text
						item.DataTime = dateMap[k]
						item.Exchange = exchange
						item.Description = description
						item.Province = province
						item.Source = "三省周度"
						if exchange == "产量" {
							item.Unit = "万吨"
						} else {
							item.Unit = "百分比"
						}
						item.Frequency = "周度"
						item.ModifyTime = time.Now()
						item.CreateTime = time.Now()
						itemsIndex = append(itemsIndex, item)
					}
				}
			}

			//获取公司指标名称
			if i > 17 {
				row := sheet.Rows[i]
				cells := row.Cells
			Loop3:
				for k, cell := range cells {
					text := cell.Value
					if text != "" {
						if k == 1 {
							province = text
						}
						if k == 2 {
							city = text
						}
						if k == 3 {
							companyName = text
						}
						if k == 4 {
							item := new(models.BaseFromCoalmineMapping)
							group = text
							trimName := TrimCompanyName(companyName)
							item.IndexName = trimName
							//取处理后公司名首字母缩写
							strResult := ""
							a := pinyin.NewArgs()
							rows := pinyin.Pinyin(trimName, a)
							for i := 0; i < len(rows); i++ {
								if len(rows[i]) != 0 {
									str := rows[i][0]
									pi := str[0:1]
									strResult += pi
								}
							}
							item.IndexCode = strResult + "company"
							item.CreateTime = time.Now()
							itemsCompany = append(itemsCompany, item)
							break Loop3
						}
					}
				}
			}

			//获取公司具体产量
			if i > 18 {
				row := sheet.Rows[i]
				cells := row.Cells
				for k, cell := range cells {
					text := cell.Value
					if k > 4 {
						item := new(models.BaseFromCoalmineCompanyIndex)
						companyName = TrimCompanyName(companyName)
						item.IndexName = companyName
						item.IndexCode = codeCompanyMap[item.IndexName]
						item.DealValue = text
						item.DataTime = dateMap[k]
						item.Province = province
						item.City = city

						//处理无类别名时的情况
						if group == "无" {
							item.GroupName = companyName
						} else {
							item.GroupName = group
						}

						item.Source = "三省企业"
						item.Unit = "万吨"
						item.Frequency = "周度"
						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.DealValue && 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.AddBaseFromCoalmineMapping(v)
			if err != nil {
				for i := 0; i < 10; i++ {
					v.IndexCode = v.IndexCode + strconv.Itoa(i)
					codeCompanyMap[v.IndexName] = v.IndexCode
					newId, err := models.AddBaseFromCoalmineMapping(v)
					if err != nil {
						fmt.Println("再次添加公司指标名称错误", err)
						fmt.Println(v.IndexName, v.IndexCode)
						continue
					} else {
						fmt.Println("新增公司成功", newId)
						break
					}
				}
			} else {
				fmt.Println("新增公司成功", newId)
			}
		}
	}
	fmt.Println("公司指标操作完成")

	for _, v := range itemsCompanyIndex {
		v.IndexCode = codeCompanyMap[v.IndexName]
		if indexCompanyMap[v.IndexName+v.DataTime] == "" && v.DealValue != "" {
			newId, err := models.AddBaseFromCoalmineCompanyIndex(v)
			if err != nil {
				fmt.Println("添加公司数据错误", err)
			} else {
				fmt.Println("新增公司数据成功", newId)
			}
		} else {
			if indexCompanyMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
				err = models.UpdateBaseFromCoalmineCompanyIndex(v)
				if err != nil {
					fmt.Println("修改数据错误错误", err)
				}
			}
		}

	}

	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	return
}

// @Title 刷新数据
// @Description 刷新数据接口
// @Param	request	body models.CoalMineDataReq true "type json string"
// @Success 200 {object} models.EdbClassify
// @router /coastal [post]
func (this *CoalMineDataController) Coastal() {
	br := new(models.BaseResponse).Init()
	defer func() {
		this.Data["json"] = br
		this.ServeJSON()
	}()
	var req models.CoalMineDataReq

	err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
	if err != nil {
		br.Msg = "参数解析异常！"
		br.ErrMsg = "参数解析失败，Err:" + err.Error()
		return
	}

	var mappingItems []*models.BaseFromCoalmineMapping
	var indexItems []*models.BaseFromCoalmineCoastalIndex
	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, unit string
	codeList, err := models.GetBaseFromCoalmineMapping()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭指标失败：", err)
		return
	}
	if len(codeList) > 0 {
		for _, v := range codeList {
			codeMap[v.IndexName] = v.IndexCode
		}
	}
	indexCompanyList, err := models.GetBaseFromCoalmineCoastalIndex()
	if err != nil && err.Error() != utils.ErrNoRow() {
		utils.FileLog.Info("获取煤炭公司指标失败：", err)
		return
	}
	if len(indexCompanyList) > 0 {
		for _, v := range indexCompanyList {
			indexMap[v.IndexName+v.DataTime] = v.DealValue
		}
	}

	for _, sheet := range req.SheetData {
		sheetName = sheet.Name
		//遍历行读取
		maxCol := sheet.MaxCol
		for i := 0; i < maxCol; i++ {
			if i == 0 {
				row := sheet.Rows[i]
				cells := row.Cells
				for k, cell := range cells {
					if k > 0 && k < 9 {
						text := cell.Value
						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 + "coastal"
						} 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 + "coastal"
						}
						item.CreateTime = time.Now()
						mappingItems = append(mappingItems, &item)
					}
					if k == 10 {
						unit = strings.Replace(cell.Value, "单位：", "", -1)
					}
				}
			}
			if i > 0 {
				var row models.Row
				if i < len(sheet.Rows) {
					row = sheet.Rows[i]
				}
				cells := row.Cells
				for k, cell := range cells {
					if k < 9 {
						var item models.BaseFromCoalmineCoastalIndex
						if k == 0 {
							text := cell.Value
							if text == "" {
								continue
							}
							if len(text) < 6 {
								dataTime = ExcelDateToDate(text).Format(utils.FormatDate)
							} else {
								parsedTime, err := time.Parse("01-02-06", text)
								if err != nil {
									parsedTime, err = time.Parse("2006/01/02", text)
									if err != nil {
										fmt.Println("解析时间字符串出错:", err)
										return
									}
								}
								dataTime = parsedTime.Format(utils.FormatDate)
							}
						}
						if k > 0 {
							text := cell.Value
							item.IndexName = nameMap[k]
							item.IndexCode = codeMap[nameMap[k]]
							item.DealValue = text
							item.GroupName = groupMap[k]
							item.Source = "沿海八省"
							item.DataTime = dataTime
							item.Frequency = "日度"
							item.ModifyTime = time.Now()
							item.CreateTime = time.Now()
						}
						item.Unit = unit
						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.AddBaseFromCoalCoastalIndex(v)
			if err != nil {
				fmt.Println("添加数据错误", err)
			} else {
				fmt.Println("新增成功", newId)
			}
		} else {
			if indexMap[v.IndexName+v.DataTime] != v.DealValue && v.DealValue != "" {
				err = models.UpdateBaseFromCoalCoastalIndex(v)
				if err != nil {
					fmt.Println("修改数据错误错误", err)
					return
				}
			}
		}
	}
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	return
}

// @Title 刷新数据
// @Description 刷新数据接口
// @Param	request	body models.CoalMineDataReq true "type json string"
// @Success 200 {object} models.EdbClassify
// @router /inland [post]
func (this *CoalMineDataController) Inland() {
	br := new(models.BaseResponse).Init()
	defer func() {
		this.Data["json"] = br
		this.ServeJSON()
	}()
	var req models.CoalMineDataReq

	err := json.Unmarshal(this.Ctx.Input.RequestBody, &req)
	if err != nil {
		br.Msg = "参数解析异常！"
		br.ErrMsg = "参数解析失败，Err:" + err.Error()
		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
	}
	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
	}
	if len(indexCompanyList) > 0 {
		for _, v := range indexCompanyList {
			indexMap[v.IndexName+v.DataTime] = v.DealValue
		}
	}

	for _, sheet := range req.SheetData {
		sheetName = sheet.Name
		fmt.Println("sheetName:", sheetName)
		var unit string
		if sheetName == "可用天数" {
			unit = "天"
		} else {
			unit = "万吨"
		}
		//遍历行读取
		maxCol := sheet.MaxCol
		for i := 0; i < maxCol; i++ {
			if i == 0 {
				row := sheet.Rows[i]
				cells := row.Cells
				for k, cell := range cells {
					if k > 0 && k < 18 {
						text := cell.Value
						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 {
				var row models.Row
				if i < len(sheet.Rows) {
					row = sheet.Rows[i]
				}
				cells := row.Cells
				for k, cell := range cells {
					if k < 18 {
						var item models.BaseFromCoalmineInlandIndex
						if k == 0 {
							text := cell.Value
							if text == "" {
								continue
							}
							if len(text) < 6 {
								dataTime = ExcelDateToDate(text).Format(utils.FormatDate)
							} else {
								parsedTime, err := time.Parse("01-02-06", text)
								if err != nil {
									parsedTime, err = time.Parse("2006/01/02", text)
									if err != nil {
										fmt.Println("解析时间字符串出错:", err)
										return
									}
								}
								dataTime = parsedTime.Format(utils.FormatDate)
							}
						}
						if k > 0 {
							text := cell.Value
							item.IndexName = nameMap[k]
							item.IndexCode = codeMap[nameMap[k]]
							item.DealValue = text
							item.GroupName = groupMap[k]
							item.Source = "内陆十七省"
							item.DataTime = dataTime
							item.Unit = 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
				}
			}
		}
	}
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	return
}
func ttrimProvinceName(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 ExcelDateToDate(excelDate string) time.Time {
	excelTime := time.Date(1899, time.December, 30, 0, 0, 0, 0, time.UTC)
	var days, _ = strconv.Atoi(excelDate)
	return excelTime.Add(time.Second * time.Duration(days*86400))
}

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
}

func TrimCompanyName(name string) string {
	name = strings.Replace(name, "有限", "", -1)
	name = strings.Replace(name, "股份", "", -1)
	name = strings.Replace(name, "责任", "", -1)
	name = strings.Replace(name, "公司", "", -1)
	name = strings.Replace(name, "（", "", -1)
	name = strings.Replace(name, "）", "", -1)
	return name
}

func ContainsSpecialName(name string) bool {
	return strings.Contains(name, "陕西") ||
		strings.Contains(name, "海南") ||
		strings.Contains(name, "河南") ||
		strings.Contains(name, "河北") ||
		strings.Contains(name, "澳门") ||
		strings.Contains(name, "内蒙古") ||
		strings.Contains(name, "黑龙江")
}

func TrimProvinceName(name string) string {
	name = strings.Replace(name, "陕西省", "sns", -1)
	name = strings.Replace(name, "陕西", "sn", -1)
	name = strings.Replace(name, "海南市", "hi", -1)
	name = strings.Replace(name, "海南", "hi", -1)
	name = strings.Replace(name, "河南省", "has", -1)
	name = strings.Replace(name, "河南", "ha", -1)
	name = strings.Replace(name, "河北省", "hes", -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 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, "海南可用", "hiky", -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
}