hongze_task/services/data_source_longzhong.go

package services

import (
	"encoding/json"
	"errors"
	"fmt"
	"github.com/tealeg/xlsx"
	"hongze/hongze_task/models"
	"hongze/hongze_task/utils"
	"net/url"
	"os"
	"path/filepath"
	"rdluck_tools/http"
	"strconv"
	"time"
)

//获取隆众信息表中指标
func GetLongZhongTargetInfoByExcel() {
	var err error
	defer func() {
		if err != nil {
			fmt.Println("err:" + err.Error())
			go utils.SendEmail("获取隆众指标信息失败"+time.Now().Format("2006-01-02 15:04:05"), ";Err:"+err.Error(), utils.EmailSendToUsers)
			return
		}
	}()

	excelPath := `./static/longzhong_0824.xlsx`
	file, err := xlsx.OpenFile(excelPath)
	if err != nil {
		fmt.Println("err:", err.Error())
		return
	}
	sheets := file.Sheets
	for k, v := range sheets {
		fmt.Println(k, v.Name)
		classify, err := models.GetEdbdataClassifyByClassifyName(v.Name)
		if err != nil {
			if err.Error() == utils.ErrNoRow() {
				err = errors.New("分类不存在：Err:" + err.Error())
			} else {
				err = err
			}
			return
		}
		if classify == nil {
			err = errors.New("分类不存在，请重新核对")
			return
		}

		classifyId := classify.ClassifyId
		classifyName := classify.ClassifyName
		fmt.Println(classifyId, classifyName)
		maxRow := v.MaxRow
		//maxCol := v.MaxCol
		fmt.Println(classifyName, maxRow)
		for i := 0; i < maxRow; i++ {
			row, err := v.Row(i)
			if err != nil {
				return
			}
			longzhongClassify := ""
			if i > 0 {
				longzhongClassifyNew := row.GetCell(0).String()
				if longzhongClassifyNew != "" {
					longzhongClassify = longzhongClassifyNew
				}
				secName := row.GetCell(1).Value
				frequency := row.GetCell(2).Value
				LongzhongId := row.GetCell(3).Value
				remark := row.GetCell(4).String()
				item := new(models.DataSourceLongzhong)
				item.ClassifyId = classifyId
				item.ClassifyName = classifyName
				item.SecName = secName
				item.Frequency = frequency
				item.LongzhongId = LongzhongId
				item.Remark = remark
				item.LongzhongClassify = longzhongClassify
				_, err = models.AddDataSourceLongzhong(item)
				if err != nil {
					fmt.Println("Err:", err.Error())
					return
				}
			}
		}
	}
}

func GetLzProductList()(err error) {
	utils.FileLog.Info("start GetLzProductList")
	defer func() {
		if err != nil {
			fmt.Println("Err:" + err.Error())
			go utils.SendEmail("获取隆众指标数据失败"+time.Now().Format("2006-01-02 15:04:05"), ";Err:"+err.Error(), utils.EmailSendToUsers)
		}
	}()
	postUrl := `http://info.oilchem.net/cust/interfacenew/getProductUnitListNew`
	username := "hzmd"
	password := "HZMD2020"
	timestamp := fmt.Sprintf("%d", time.Now().Unix())
	fmt.Println(timestamp)
	token := utils.MD5(username + timestamp + utils.MD5(password))
	fmt.Println("token:", token)

	param := url.Values{}
	param.Add("username", username)
	param.Add("password", password)
	param.Add("timestamp", timestamp)
	param.Add("token", token)
	utils.FileLog.Info("%s", param.Encode())
	body, err := http.Post(postUrl, param.Encode())
	if err != nil {
		fmt.Println("err:", err.Error())
		return
	}
	result := string(body)
	if result == "" {
		err = errors.New("返回内容为空")
		return
	}
	resp := new(models.LzProductInfoResp)
	err = json.Unmarshal(body, &resp)
	if err != nil {
		fmt.Println("json.Unmarshal Err:" + err.Error())
		return
	}
	if resp.Code != "1" {
		fmt.Println("获取信息失败")
		err = errors.New("Code:" + resp.Code + ";Msg:" + resp.Msg)
		return
	}
	fmt.Println("len")
	fmt.Println(len(resp.Data))
	for _, v := range resp.Data {
		fmt.Println(v.Unitid, v.ProName, v.ProUnitName)
		var classifyId int
		var classifyName string
		classify, err := models.GetEdbdataClassifyByClassifyName(v.ProName)
		if err != nil && err.Error() != utils.ErrNoRow() {
			fmt.Println("获取分类失败，Err:" + err.Error())
			return err
		}
		if classify == nil || (err != nil && err.Error() == utils.ErrNoRow()) {
			classifyItem := new(models.EdbdataClassify)
			classifyItem.ClassifyName = v.ProName
			classifyItem.ParentId = 4
			classifyItem.CreateTime = time.Now()
			newId, err := models.AddEdbdataClassify(classifyItem)
			if err != nil {
				fmt.Println("新增分类失败，Err:" + err.Error())
				return err
			}
			classifyId = int(newId)
			classifyName = v.ProName
		} else {
			classifyId = classify.ClassifyId
			classifyName = classify.ClassifyName
		}
		count, err := models.GetLongzhonginfoCount(classifyId, v.Unitid)
		if err != nil {
			fmt.Println("判断指标是否存在失败，Err:" + err.Error())
			return err
		}
		if count > 0 { //修改
			lzProduct := new(models.Longzhonginfo)
			lzProduct.SecName = v.ProUnitName
			lzProduct.Frequency = v.LzType
			lzProduct.ClassifyId = classifyId
			lzProduct.ClassifyName = classifyName
			lzProduct.Unit = v.Unit
			lzProduct.CreateTime = time.Now()
			if v.IsNormal == "" {
				v.IsNormal = "1"
			}
			lzProduct.IsNormal = v.IsNormal
			lzProduct.LastModifyDate = v.Maxdate
			lzProduct.Unitid = v.Unitid
			lzProduct.TempId = v.Tempid
			lzProduct.TempName = v.TempName
			lzProduct.ModifyTime = time.Now()
			err = models.ModifyLongzhonginfo(lzProduct)
			if err != nil {
				fmt.Println("修改失败，Err:" + err.Error())
				return err
			}
		} else { //新增
			maxId, err := models.GetLongzhonginfoMaxId()
			if err != nil {
				fmt.Println("获取编码失败,err:" + err.Error())
				return err
			}
			tradeCode := fmt.Sprintf("L%06d", maxId+1)
			lzProduct := new(models.Longzhonginfo)
			lzProduct.TradeCode = tradeCode
			lzProduct.SecName = v.ProUnitName
			lzProduct.Frequency = v.LzType
			lzProduct.ClassifyId = classifyId
			lzProduct.ClassifyName = classifyName
			lzProduct.Unit = v.Unit
			lzProduct.CreateTime = time.Now()
			if v.IsNormal == "" {
				v.IsNormal = "1"
			}
			lzProduct.IsNormal = v.IsNormal
			lzProduct.LastModifyDate = v.Maxdate
			lzProduct.Unitid = v.Unitid
			lzProduct.TempId = v.Tempid
			lzProduct.TempName = v.TempName
			lzProduct.ModifyTime = time.Now()
			_, err = models.AddLongzhonginfo(lzProduct)
			if err != nil {
				fmt.Println("新增指标信息失败")
				return err
			}
		}
	}
	return
}

func GetLzProductDetail()(err error) {
	utils.FileLog.Info("start GetLzProductDetail")
	defer func() {
		if err != nil {
			fmt.Println("Err:" + err.Error())
		}
	}()

	startDate := time.Now().AddDate(0, 0, -2).Format(utils.FormatDate)
	endDate := time.Now().AddDate(0, 0, 0).Format(utils.FormatDate)
	time.Sleep(5 * time.Second)
	pageSize := 30

	postUrl := `http://info.oilchem.net/cust/interfacenew/getProductUnitDetailNew`
	username := "hzmd"
	password := "HZMD2020"

	totalPage := 10
	for i := 1; i <= totalPage; i++ {
		fmt.Println("page:", i)
		timestamp := fmt.Sprintf("%d", time.Now().Unix())
		token := utils.MD5(username + timestamp + utils.MD5(password))

		param := url.Values{}
		param.Add("username", username)
		param.Add("password", password)
		param.Add("timestamp", timestamp)
		param.Add("token", token)
		//param.Add("id", "334")
		param.Add("startDate", startDate)
		param.Add("endDate", endDate)
		param.Add("pagesize", strconv.Itoa(pageSize))
		param.Add("page", strconv.Itoa(i))
		utils.FileLog.Info("%s", param.Encode())
		body, err := http.Post(postUrl, param.Encode())
		if err != nil {
			fmt.Println("err:", err.Error())
			return err
		}
		utils.FileLog.Info("%s", string(body))
		resp := new(models.LzProductInfoDetailResp)
		err = json.Unmarshal(body, &resp)
		if err != nil {
			fmt.Println("Unmarshal Err:" + err.Error())
			return err
		}
		if resp.Code != "1" {
			fmt.Println("获取信息失败")
			err = errors.New("Code:" + resp.Code + ";Msg:" + resp.Msg)
			return err
		}
		if i == totalPage && resp.Pagesize >= pageSize {
			totalPage = totalPage + 5
		}
		for k, v := range resp.Data {
			fmt.Println(k, v.UnitId, v.ProCnName)
			target, err := models.GetLongzhonginfoByUnitId(v.ProCnName, v.UnitId)
			if err != nil {
				if err.Error() != utils.ErrNoRow() {
					fmt.Println("获取target失败，err:" + err.Error())
					continue
				}
				fmt.Println("err:" + err.Error())
				return err
			}
			if target == nil {
				fmt.Println("target is empty:"+v.ProCnName, v.UnitId)
				continue
			}

			count, err := models.GetLongzhongdataCount(target.LongzhonginfoId, v.DataTime)
			if err != nil {
				fmt.Println("GetLongzhongdataCount Err:" + err.Error())
				return err
			}
			if count > 0 {
				lzdetail := new(models.Longzhongdata)
				lzdetail.CreateTime = time.Now()
				lzdetail.ModifyTime = time.Now()
				lzdetail.TradeCode = target.TradeCode
				lzdetail.LongzhonginfoId = target.LongzhonginfoId
				lzdetail.Dt = v.DataTime
				lzdetail.Close = v.UnitValue
				lzdetail.UnitDesc = v.UnitDesc
				lzdetail.UpdTime = v.UpdTime
				err = models.ModifyLongzhongdata(lzdetail)
				if err != nil {
					fmt.Println("ModifyLongzhongdata Err:" + err.Error())
					return err
				}
			} else {
				lzdetail := new(models.Longzhongdata)
				lzdetail.CreateTime = time.Now()
				lzdetail.ModifyTime = time.Now()
				lzdetail.TradeCode = target.TradeCode
				lzdetail.LongzhonginfoId = target.LongzhonginfoId
				lzdetail.Dt = v.DataTime
				lzdetail.Close = v.UnitValue
				lzdetail.UnitDesc = v.UnitDesc
				lzdetail.UpdTime = v.UpdTime
				err = models.AddLongzhongdata(lzdetail)
				if err != nil {
					fmt.Println("AddLongzhongdata Err:" + err.Error())
					return err
				}
			}
		}
		if resp.Pagesize < pageSize || len(resp.Data) == 0 {
			return err
		}
		time.Sleep(2 * time.Second)
	}
	return
}

func GetLzPrice() {
	fmt.Println("start")
	/*getUrl:=`https://openapi.oilchem.net/price/getProduct?password=HZMD2020&type=guoji&username=hzmd`
	result,err:=http.Get(getUrl)
	fmt.Println(string(result),err)
	utils.FileLog.Info("%s",string(result))*/

	getUrl := `https://openapi.oilchem.net/price/getPrice?password=HZMD2020&type=guoji&username=hzmd&inStartDate=2020-08-20&inEndDate=2020-08-26`
	result, err := http.Get(getUrl)
	fmt.Println(string(result), err)
	utils.FileLog.Info("%s", string(result))

	fmt.Println("end")
}

func LzExportExcel() {
	var err error
	defer func() {
		if err != nil {
			fmt.Println("err:" + err.Error())
		}
	}()
	parentId := 4
	classifyList, err := models.GetEdbdataClassifyByParentId(parentId)
	if err != nil {
		return
	}
	xlsxFile := xlsx.NewFile()
	if err != nil {
		fmt.Println("生成文件失败" + err.Error())
		return
	}
	dir, _ := os.Executable()
	exPath := filepath.Dir(dir)
	downLoadnFilePath := exPath + "/static/" + time.Now().Format(utils.FormatDateTimeUnSpace) + ".xlsx"
	fmt.Println("downLoadnFilePath")
	fmt.Println(downLoadnFilePath)
	for _, v := range classifyList {
		fmt.Println(v.ClassifyId, v.ClassifyName)
		//获取指标
		secNameList, err := models.GetLongzhonginfoByClassifyId(v.ClassifyId)
		if err != nil {
			fmt.Println("获取数据失败，Err:" + err.Error())
			return
		}
		if len(secNameList) <= 0 {
			continue
		}
		sheetNew, err := xlsxFile.AddSheet(v.ClassifyName)
		if err != nil {
			fmt.Println("新增Sheet失败", err.Error())
			return
		}
		//获取指标数据
		windRow := sheetNew.AddRow()
		secNameRow := sheetNew.AddRow()
		frequencyRow := sheetNew.AddRow()
		unitRow := sheetNew.AddRow()
		lastModifyDateRow := sheetNew.AddRow()
		//获取分类下指标最大数据量
		dataMax, err := models.GetLongzhongDataMaxCount(v.ClassifyId)
		if err != nil {
			fmt.Println("获取指标最大数据量失败", err.Error())
			return
		}
		for _, sv := range secNameList {
			fmt.Println("line 399:", v.ClassifyId, v.ClassifyName, sv.SecName, sv.Frequency)

			windRow.AddCell().SetValue("Wind")
			secNameRow.AddCell().SetValue("指标名称")
			frequencyRow.AddCell().SetValue("频率")
			unitRow.AddCell().SetValue("单位")
			lastModifyDateRow.AddCell().SetValue("更新时间")

			secNameRow.AddCell().SetValue(sv.SecName)
			frequencyRow.AddCell().SetValue(sv.Frequency)
			unitRow.AddCell().SetValue(sv.Unit)
			lastModifyDateRow.AddCell().SetValue(sv.LastModifyDate)

			windRow.AddCell()
			windRow.AddCell()
			secNameRow.AddCell()
			frequencyRow.AddCell()
			unitRow.AddCell()
			lastModifyDateRow.AddCell()

			//获取数据
			dataList, err := models.GetLongzhongDataById(sv.LongzhonginfoId)
			if err != nil {
				fmt.Println("GetLongzhongDataById Err:" + err.Error())
				return
			}
			if len(dataList) <= 0 {
				for n := 0; n < dataMax; n++ {
					rowIndex := 5 + n
					row, err := sheetNew.Row(rowIndex)
					if err != nil {
						fmt.Println("add row err:" + err.Error())
						return
					}
					row.AddCell()
					row.AddCell()
					row.AddCell()
				}
			} else {
				for rk, dv := range dataList {
					rowIndex := 5 + rk
					row, err := sheetNew.Row(rowIndex)
					if err != nil {
						fmt.Println("add row err:" + err.Error())
						return
					}
					row.AddCell().SetValue(dv.Dt)
					row.AddCell().SetFloat(dv.Close)
					row.AddCell()
				}
			}
		}
		err = xlsxFile.Save(downLoadnFilePath)
		if err != nil {
			return
		}
	}
}