eta_crawler/services/smm_shanghai.go

package services

import (
	"context"
	"encoding/json"
	"errors"
	"eta/eta_crawler/models"
	"eta/eta_crawler/services/sci99"
	"eta/eta_crawler/utils"
	"fmt"
	"io"
	"net/http"
	"strconv"
	"strings"
	"time"

	"github.com/beego/beego/v2/task"
	"github.com/xuri/excelize/v2"
)

type SmmRequest struct {
	Name      string
	IndexCode string
	Url       string
	Frequency string
	Unit      string
	Value     string
}

var nickelReqs = []SmmRequest{
	// {
	// 	Name:      "SMM 1#电解镍",
	// 	IndexCode: "",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/201102250239",
	// 	Frequency: "日度",
	// 	Unit:      "元/吨",
	// },
	{
		Name:      "1#金川镍",
		Url:       "https://hq.smm.cn/ajax/spot/history/201102250174",
		Frequency: "日度",
		Unit:      "元/吨",
	},
	{
		Name:      "1#进口镍",
		Url:       "https://hq.smm.cn/ajax/spot/history/201102250423",
		Frequency: "日度",
		Unit:      "元/吨",
	},
	{
		Name:      "镍豆",
		Url:       "https://hq.smm.cn/ajax/spot/history/202008270001",
		Frequency: "日度",
		Unit:      "元/吨",
	},
	// {
	// 	Name:      `印尼内贸红土镍矿1.2%(到厂价)`,
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202311230010",
	// 	Frequency: "周度",
	// 	Unit:      "美元/湿吨",
	// },
	// {
	// 	Name:      `印尼内贸红土镍矿1.6%(到厂价)`,
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202311230011",
	// 	Frequency: "周度",
	// 	Unit:      "美元/湿吨",
	// },
	{
		Name:      `菲律宾红土镍矿0.9%,Al≥7%(CIF)`,
		Url:       "https://hq.smm.cn/ajax/spot/history/201608170001",
		Frequency: "日度",
		Unit:      "美元/湿吨",
	},
	// {
	// 	Name:      `菲律宾红土镍矿0.9%,Al<7%(CIF) `, // 重名
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202109140006",
	// 	Frequency: "日度",
	// 	Unit:      "美元/湿吨",
	// },

	// {
	// 	Name:      `菲律宾红土镍矿1.3%(CIF)`,
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202109140001",
	// 	Frequency: "日度",
	// 	Unit:      "美元/湿吨",
	// },
	// {
	// 	Name:      `菲律宾红土镍矿1.4%(CIF)`,
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202109140003",
	// 	Frequency: "日度",
	// 	Unit:      "美元/湿吨",
	// },
	{
		Name:      `菲律宾红土镍矿1.5%(CIF)`,
		Url:       "https://hq.smm.cn/ajax/spot/history/201608170002",
		Frequency: "日度",
		Unit:      "美元/湿吨",
	},
	{
		Name:      `红土镍矿1.8%(CIF)`,
		Url:       "https://hq.smm.cn/ajax/spot/history/201608170003",
		Frequency: "日度",
		Unit:      "美元/湿吨",
	},

	{
		Name:      `菲律宾红土镍矿0.9%,Al≥7%(FOB)`,
		Url:       "https://hq.smm.cn/ajax/spot/history/201509220001",
		Frequency: "日度",
		Unit:      "美元/湿吨",
	},
	// {
	// 	Name:      `菲律宾红土镍矿0.9%,Al<7%(FOB)`, //重名
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202109140005",
	// 	Frequency: "日度",
	// 	Unit:      "美元/湿吨",
	// },
	// {
	// 	Name:      `菲律宾红土镍矿1.3%(FOB)`,
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202109140002",
	// 	Frequency: "日度",
	// 	Unit:      "美元/湿吨",
	// },
	// {
	// 	Name:      `菲律宾红土镍矿1.4%(FOB)`,
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202109140004",
	// 	Frequency: "日度",
	// 	Unit:      "美元/湿吨",
	// },
	{
		Name:      `菲律宾红土镍矿1.5%(FOB)`,
		Url:       "https://hq.smm.cn/ajax/spot/history/201509220002",
		Frequency: "日度",
		Unit:      "美元/湿吨",
	},
	{
		Name:      `红土镍矿1.8%(FOB)`,
		Url:       "https://hq.smm.cn/ajax/spot/history/201509220003",
		Frequency: "日度",
		Unit:      "美元/湿吨",
	},
	{
		Name:      `8-12%高镍生铁(出厂价)`,
		Url:       "https://hq.smm.cn/ajax/spot/history/201106150005",
		Frequency: "日度",
		Unit:      "元/镍点",
	},
	{
		Name:      `1.5-1.7%镍生铁(出厂价)`,
		Url:       "https://hq.smm.cn/ajax/spot/history/201106150007",
		Frequency: "日度",
		Unit:      "元/吨",
	},
}

var copperReqs = []SmmRequest{
	{
		Name:      "SMM 1#电解铜",
		Url:       "https://hq.smm.cn/ajax/spot/history/201102250376",
		Unit:      "元/吨",
		Frequency: "日度",
	},
	{
		Name:      "SMM 1#电解铜升贴水最大值",
		Url:       "https://hq.smm.cn/ajax/premium/history/201102250185",
		Value:     "highs",
		Frequency: "日度",
	},
	{
		Name:      "SMM 1#电解铜升贴水最小值",
		Url:       "https://hq.smm.cn/ajax/premium/history/201102250185",
		Value:     "low",
		Frequency: "日度",
	},
	// {
	// 	Name:      "SMM 广东1#电解铜",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/201912300001",
	// 	Unit:      "元/吨",
	// 	Frequency: "日度",
	// },
	{
		Name:      "SMM 广东1#电解铜升贴水最大值",
		Url:       "https://hq.smm.cn/ajax/premium/history/201912300002",
		Value:     "highs",
		Frequency: "日度",
	},
	{
		Name:      "SMM 广东1#电解铜升贴水最小值",
		Url:       "https://hq.smm.cn/ajax/premium/history/201912300002",
		Value:     "low",
		Frequency: "日度",
	},
	{
		Name:      "进口铜精矿指数(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201910240001",
		Unit:      "美元/吨",
		Frequency: "周度",
	},
	{
		Name:      "废铜:广东:1#光亮铜线",
		Url:       "https://hq.smm.cn/ajax/spot/history/201108090036",
		Unit:      "元/吨",
		Frequency: "日度",
	},
	{
		Name:      "电力行业用杆加工费(华东)-平均价",
		Url:       "https://hq.smm.cn/ajax/spot/history/201811010001",
		Unit:      "元/吨",
		Frequency: "日度",
	},
	{
		Name:      "电力行业用杆加工费(华南)-平均价",
		Url:       "https://hq.smm.cn/ajax/spot/history/202004080001",
		Unit:      "元/吨",
		Frequency: "日度",
	},
}

var zincReqs = []SmmRequest{
	{
		Name:      "SMM 0#锌锭",
		Url:       "https://hq.smm.cn/ajax/spot/history/201102250173",
		Unit:      "元/吨",
		Frequency: "日度",
	},
	{
		Name:      "SMM 1#锌锭",
		Url:       "https://hq.smm.cn/ajax/spot/history/201102250418",
		Unit:      "元/吨",
		Frequency: "日度",
	},
	// {
	// 	Name:      "SMM 0#锌锭溢价(广东)",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202010210003",
	// 	Unit:      "元/吨",
	// 	Frequency: "日度",
	// },
	{
		Name:      "SMM 0#锌锭(广东)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201102250231",
		Unit:      "元/吨",
		Frequency: "日度",
	},
	// {
	// 	Name:      "SMM 1#锌锭溢价(广东)",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202010210004",
	// 	Unit:      "元/吨",
	// 	Frequency: "日度",
	// },
	{
		Name:      "SMM 1#锌锭(广东)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201102250069",
		Unit:      "元/吨",
		Frequency: "日度",
	},
	// {
	// 	Name:      "SMM 0#锌锭溢价(天津)",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202010210001",
	// 	Unit:      "元/吨",
	// 	Frequency: "日度",
	// },
	{
		Name:      "SMM 1#锌锭(天津)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201102250556",
		Unit:      "元/吨",
		Frequency: "日度",
	},
	{
		Name:      "SMM 0#锌锭(天津)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201102250399",
		Unit:      "元/吨",
		Frequency: "日度",
	},
	// {
	// 	Name:      "SMM 1#锌锭溢价(天津)",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202010210002",
	// 	Unit:      "元/吨",
	// 	Frequency: "日度",
	// },
	// {
	// 	Name:      "SMM 0#锌锭溢价(宁波)",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202010210005",
	// 	Unit:      "元/吨",
	// 	Frequency: "日度",
	// },
	// {
	// 	Name:      "SMM 0#锌锭(宁波)",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/202004070006",
	// 	Unit:      "元/吨",
	// 	Frequency: "日度",
	// },
	// {
	// 	Name:      "Zn50国产TC(月)",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/201312030008",
	// 	Unit:      "元/金属吨",
	// 	Frequency: "月度",
	// },
	// {
	// 	Name:      "Zn50进口TC(月)",
	// 	Url:       "https://hq.smm.cn/ajax/spot/history/201312030009",
	// 	Unit:      "美元/千吨",
	// 	Frequency: "月度",
	// },
	{
		Name:      "Zn50国产TC(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/202004070002",
		Unit:      "元/金属吨",
		Frequency: "周度",
	},
	{
		Name:      "Zn50进口TC(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/202004070001",
		Unit:      "美元/千吨",
		Frequency: "周度",
	},
	{
		Name:      "Zn50内蒙古国产TC(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201504080006",
		Unit:      "元/金属吨",
		Frequency: "周度",
	},
	{
		Name:      "Zn50陕西国产TC(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201504080007",
		Unit:      "元/金属吨",
		Frequency: "周度",
	},
	{
		Name:      "Zn50甘肃国产TC(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/202108050001",
		Unit:      "元/金属吨",
		Frequency: "周度",
	},
	{
		Name:      "Zn50四川国产TC(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201504080008",
		Unit:      "元/金属吨",
		Frequency: "周度",
	},
	{
		Name:      "Zn50广西国产TC(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201504080009",
		Unit:      "元/金属吨",
		Frequency: "周度",
	},
	{
		Name:      "Zn50云南国产TC(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201504080010",
		Unit:      "元/金属吨",
		Frequency: "周度",
	},
	{
		Name:      "Zn50湖南国产TC(周)",
		Url:       "https://hq.smm.cn/ajax/spot/history/201504080011",
		Unit:      "元/金属吨",
		Frequency: "周度",
	},
}

type SmmResponse struct {
	Code int
	Data []*SmmData
	Msg  string
}

type SmmData struct {
	Highs           float64 `json:"highs"`
	Low             float64 `json:"low"`
	Average         float64 `json:"average"`
	VchangeRate     float64 `json:"vchange_rate"`
	LowShow         string  `json:"low_show"`
	HighShow        string  `json:"high_show"`
	AverageShow     string  `json:"average_show"`
	ProductId       string  `json:"product_id"`
	Vchange         float64 `json:"vchange"`
	RenewDate       string  `json:"renew_date"`
	ChangeValueShow string  `json:"change_value_show"`
	ChangeRateShow  string  `json:"change_rate_show"`
}

type EdbInfoData struct {
	ClassifyName string
	IndexName    string
	IndexCode    string
	Frequency    string
	Unit         string
	Value        string
	LastDate     time.Time
	OldDate      time.Time
	Data         map[string]float64
	SmmData
}

func SyncShangHaiSmm(cont context.Context) (err error) {
	upMonth := time.Now().AddDate(0, -1, 0).Format(utils.FormatDate)
	curDate := time.Now().Format(utils.FormatDate)
	reqList := make([]SmmRequest, 0)
	reqList = append(reqList, zincReqs...)
	reqList = append(reqList, copperReqs...)
	reqList = append(reqList, nickelReqs...)

	edbInfoList := make([]*EdbInfoData, 0)
	for _, v := range reqList {
		url := fmt.Sprintf("%s/%s/%s", v.Url, upMonth, curDate)
		req, e := http.NewRequest("GET", url, nil)
		if e != nil {
			fmt.Println(e)
			return
		}
		// 发送请求
		client := &http.Client{}
		resp, e := client.Do(req)
		if e != nil {
			fmt.Println(e)
			return
		}
		var respData SmmResponse
		body, er := io.ReadAll(resp.Body)
		if er != nil {
			fmt.Println(er)
			return er
		}
		if err = json.Unmarshal(body, &respData); err != nil {
			fmt.Println(err)
			return
		}
		if respData.Code != 0 {
			fmt.Printf("上海有色爬虫请求失败, 指标名称:%s, 指标地址:%s", v.Name, v.Url)
			continue
		}
		data := respData.Data
		edbInfoList = append(edbInfoList, &EdbInfoData{
			IndexName: v.Name,
			Frequency: v.Frequency,
			Unit:      v.Unit,
			Value:     v.Value,
			SmmData:   *data[len(data)-1],
		})
		utils.FileLog.Info("上海有色爬虫请求成功, 指标名称:%s, 指标数据:%+v", v.Name, data[len(data)-1])
	}

	urlStr := `shanghai_smm/refresh/list`
	postUrl := utils.EDB_LIB_URL + urlStr
	postData, err := json.Marshal(edbInfoList)
	if err != nil {
		utils.FileLog.Info("Marshal Err:" + err.Error())
		return
	}
	result, err := sci99.HttpPost(postUrl, string(postData), "application/json")
	if err != nil {
		utils.FileLog.Info("HttpPost Err:" + err.Error())
		return
	}
	resp := new(models.BaseResponse)
	err = json.Unmarshal(result, &resp)
	if err != nil {
		fmt.Println(err)
		utils.FileLog.Info("Unmarshal resp Err:" + err.Error())
		return
	}
	if resp.Ret != 200 {
		fmt.Println("上海有色爬虫更新失败")
		return
	}
	return
}

// 解析读取历史excel数据,将历史数据转化为Smm结构体
func ExcelToSmm(f *excelize.File, s string) (edbInfoList []*EdbInfoData) {
	cols, err := f.GetCols(s)
	if err != nil {
		fmt.Println(err)
		return
	}
	date := cols[0][4:]
	dateFormat := make([]string, 0)
	for _, v := range date {
		if v == "" {
			continue
		}
		var parseDate time.Time
		var err error
		// 表格的日期存在两种格式
		if strings.Contains(s, "锌") {
			parseDate, err = time.Parse(utils.FormatDate, v)
			if err != nil {
				parseDate, err = time.Parse("01-02-06", v)
			}
		} else {
			parseDate, err = time.Parse("01-02-06", v)
			if err != nil {
				parseDate, err = time.Parse(utils.FormatDate, v)
			}
		}
		if err != nil {
			fmt.Println(err)
			return
		}
		dateFormat = append(dateFormat, parseDate.Format(utils.FormatDate))
	}

	for i := 1; i < len(cols); i++ {
		tmpEdbInfo := new(EdbInfoData)
		tmpEdbInfo.IndexName = cols[i][0]
		tmpEdbInfo.IndexCode = cols[i][1]
		tmpEdbInfo.Frequency = cols[i][2]
		tmpEdbInfo.Unit = cols[i][3]
		tmpEdbInfo.Data = make(map[string]float64)
		tmpEdbInfo.OldDate = time.Now()
		for j := 4; j < len(cols[i]); j++ {
			floatVal, err := strconv.ParseFloat(cols[i][j], 64)
			if err != nil {
				fmt.Println("无效字符", cols[i][j])
				continue
			}
			tmpEdbInfo.Data[dateFormat[j-4]] = floatVal
			curTime, _ := time.Parse(utils.FormatDate, dateFormat[j-4])
			if curTime.After(tmpEdbInfo.LastDate) {
				tmpEdbInfo.LastDate = curTime
			}
			if curTime.Before(tmpEdbInfo.OldDate) {
				tmpEdbInfo.OldDate = curTime
			}
		}
		edbInfoList = append(edbInfoList, tmpEdbInfo)
	}
	return
}

func SyncOldExcel(ctx context.Context) error {
	fmt.Println("excel数据录入中")
	err := OldExcel()
	if err != nil {
		fmt.Println(err)
		utils.FileLog.Info(err.Error())
		return err
	}
	fmt.Println("excel数据录入成功")
	task.DeleteTask("shanghaiOldExcel")
	return nil
}

func OldExcel() (err error) {
	excelPath := utils.OLD_EXCEL_PATH_JR
	if excelPath == "" {
		fmt.Println("excel文件路径为空")
		return
	}
	f, err := excelize.OpenFile(excelPath)
	if err != nil {
		fmt.Print(err)
		return
	}
	var edbClassify = []string{`镍`, `铜`, `锌`, `锌(日度)`, `二期`}
	for _, v := range edbClassify {
		edbInfoList := ExcelToSmm(f, v)
		_, err = json.Marshal(edbInfoList)
		if err != nil {
			fmt.Println(err)
			return
		}
		fmt.Println("成功:", v)
		if len(edbInfoList) == 0 {
			continue
		}
		urlStr := `shanghai_smm/refresh/excel`
		postUrl := utils.EDB_LIB_URL + urlStr
		postData, er := json.Marshal(edbInfoList)
		if er != nil {
			err = er
			utils.FileLog.Info("Marshal Err:" + er.Error())
			return
		}
		result, er := sci99.HttpPost(postUrl, string(postData), "application/json")
		if er != nil {
			err = er
			utils.FileLog.Info("HttpPost Err:" + er.Error())
			return
		}
		resp := new(models.BaseResponse)
		err = json.Unmarshal(result, &resp)
		if err != nil {
			utils.FileLog.Info("Unmarshal resp Err:" + err.Error())
			return
		}
		if resp.Ret != 200 {
			err = errors.New(resp.ErrMsg)
			utils.FileLog.Info("上海有色网excel历史有色数据更新失败")
			return
		}
		time.Sleep(time.Second)
	}
	return
}