hongze_task/models/data_manage/edb_data_ys.go

/**
* @Author: jgl
* @Date: 2021/9/15 10:15
 */

package data_manage

import (
	"crypto/md5"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"hongze/hongze_task/utils"
	"io/ioutil"
	"net/http"
	"net/url"
	"rdluck_tools/orm"
	"strconv"
	"time"
)

type EdbDataYs struct {
	EdbDataId     int `orm:"column(edb_data_id);pk"`
	EdbInfoId     int
	EdbCode       string
	DataTime      string
	Value         float64
	Status        int
	CreateTime    time.Time
	ModifyTime    time.Time
	DataTimestamp int64
}

func AddEdbDataSmm(items []*EdbDataYs) (err error) {
	o := orm.NewOrm()
	o.Using("data")
	_, err = o.InsertMulti(1, items)
	return
}

func ModifyEdbDataSmm(edbCode, dataTime string, value float64) (err error) {
	o := orm.NewOrm()
	o.Using("data")
	sql := ` UPDATE edb_data_ys SET value=?,status=1,modify_time=NOW() WHERE edb_code=? AND data_time=? `
	_, err = o.Raw(sql, value, edbCode, dataTime).Exec()
	return
}

//刷新有色指标数据
func RefreshEdbDataByYs(edbInfoId int, edbCode, startDate, endDate string) (err error) {
	var condition string
	var pars []interface{}
	condition += " AND base_from_smm_index_id=? "
	pars = append(pars, edbInfoId)

	if startDate!="" {
		condition += " AND data_time>=? "
		pars = append(pars, startDate)
	}

	if endDate!="" {
		condition += " AND data_time<=? "
		pars = append(pars, endDate)
	}

	ysBaseDataAll, err := GetBaseFromSmmDataByCondition(condition, pars)
	if err != nil {
		err = errors.New("GetBaseFromSmmDataByCondition Err:" + err.Error())
		return
	}
	var existCondition string
	var existPars []interface{}
	existCondition += " AND edb_code=? "
	existPars = append(existPars, edbCode)

	existDataList, err := GetEdbDataListAll(existCondition, existPars, utils.DATA_SOURCE_YS, 1)
	if err != nil {
		err = errors.New("GetEdbDataListAll Err:" + err.Error())
		return err
	}

	existMap := make(map[string]float64)
	for _, ev := range existDataList {
		existMap[ev.DataTime] = ev.Value
	}
	ysList := make([]*EdbDataYs, 0)
	for _, dv := range ysBaseDataAll {
		if existItem, ok := existMap[dv.DataTime]; !ok { //新增
			dataDate, _ := time.Parse(utils.FormatDate, dv.DataTime)
			timestamp := dataDate.UnixNano() / 1e6
			item := new(EdbDataYs)
			item.EdbCode = edbCode
			item.EdbInfoId = edbInfoId
			item.CreateTime = time.Now()
			item.ModifyTime = time.Now()
			item.DataTime = dv.DataTime
			item.Value = dv.Value
			item.Status = 1
			item.DataTimestamp = timestamp
			ysList = append(ysList, item)
		} else {
			if dv.Value != existItem { //修改
				ModifyEdbDataSmm(edbCode, dv.DataTime, dv.Value)
			}
		}
	}
	if len(ysList) > 0 {
		err = AddEdbDataSmm(ysList)
		if err != nil {
			return
		}
	}
	return
}

/*

//有色基础指标同步
func SyncSmmIndexDataBase() {
	utils.FileLog.Info("start:" + time.Now().Format(utils.FormatDateTime))
	list, err := data_manage.GetBaseFromSmmList()
	if err != nil {
		fmt.Println("GetBaseFromSmmList Err:" + err.Error())
		return
	}
	token, err := getToken("pqian@hzinsights.com", "hz123456")
	if err != nil {
		fmt.Println(err)
		return
	}
	endDate := time.Now().Format(utils.FormatDate)
	smmIndexAll, err := data_manage.GetBaseFromSmmIndex()
	if err != nil {
		fmt.Println("GetBaseFromSmmIndex Err:" + err.Error())
		return
	}
	existIndexMap := make(map[string]*data_manage.BaseFromSmmIndex)
	for _, item := range smmIndexAll {
		existIndexMap[item.IndexCode] = item
	}

	for k, v := range list {
		edbCode := v.Interface
		var startDate string
		if v.EndDate == "" || v.EndDate == "0000-00-00" {
			startDate = v.ApiStartTime
		} else {
			if v.EndDate != "" {
				dateFormate, _ := time.Parse(utils.FormatDate, v.EndDate)
				var syncDay int
				if v.ApiUpdateType == "day" {
					syncDay = utils.DATA_REFRESH
				} else if v.ApiUpdateType == "month" {
					syncDay = utils.DATA_REFRESH * 30
				} else if v.ApiUpdateType == "week" {
					syncDay = utils.DATA_REFRESH * 7
				} else if v.ApiUpdateType == "year" {
					syncDay = utils.DATA_REFRESH * 365
				}
				startDate = dateFormate.AddDate(0, 0, -syncDay).Format(utils.FormatDate)
			}
		}

		ysItem, err := getApiData(token, edbCode, startDate, endDate)
		if err != nil {
			fmt.Println(err)
			return
		}
		if ysItem != nil && ysItem.Code == 200 {

			frequency := ysItem.Data.Frequency

			indexMap := make(map[string]int)
			smmIndexIdMap := make(map[int]int)
			indexCodeMap := make(map[int]string)

			indexKey := 0
			var isDateIndex int
			for fk, fv := range ysItem.Data.Field {
				if fv.IsDate == "1" {
					isDateIndex = fk
				} else {
					if !strings.Contains(fv.Name, "产品名称") &&
						!strings.Contains(fv.Name, "单位") &&
						!strings.Contains(fv.Name, "时间") &&
						!strings.Contains(fv.Name, "备注") {

						indexMap[fv.DBColName] = fk

						indexKey += 1
						indexCode := v.Interface + "#" + strconv.Itoa(indexKey)

						if findItem, ok := existIndexMap[indexCode]; !ok {
							ssmIndex := new(data_manage.BaseFromSmmIndex)
							ssmIndex.Interface = v.Interface
							ssmIndex.Name = v.Name
							ssmIndex.IndexCode = indexCode
							ssmIndex.IndexName = v.Name + "_" + fv.Name
							ssmIndex.Type1 = v.Type1
							ssmIndex.Type2 = v.Type2
							ssmIndex.Type3 = v.Type3
							ssmIndex.Frequency = frequency
							ssmIndex.Unit = fv.Unit
							ssmIndex.ApiStartTime = v.ApiStartTime
							ssmIndex.ApiUpdateTime = v.ApiUpdateTime
							ssmIndex.StartTime = v.StartTime
							ssmIndex.FinishTime = v.FinishTime
							ssmIndex.CreateTime = time.Now()
							ssmIndex.ModifyTime = time.Now()
							lastIndexId, err := data_manage.AddBaseFromSmmIndex(ssmIndex)
							if err != nil {
								return
							}
							smmIndexIdMap[fk] = int(lastIndexId)
							indexCodeMap[fk] = indexCode
						} else {
							smmIndexIdMap[fk] = findItem.BaseFromSmmIndexId
							indexCodeMap[fk] = findItem.IndexCode
						}
					}
				}
			}

			existDataMap := make(map[string]*data_manage.BaseFromSmmData)
			for _, mv := range indexCodeMap {
				indexCode := mv
				dataAllList, err := data_manage.GetBaseFromSmmDataAll(indexCode)
				if err != nil {
					fmt.Println("GetBaseFromSmmData Err:" + err.Error())
					return
				}
				for _, item := range dataAllList {
					key := item.IndexCode + item.DataTime
					existDataMap[key] = item
				}
			}

			addExistDataMap := make(map[string]string)

			for _, dv := range ysItem.Data.Content {
				var dataTime string
				dataTime = dv[isDateIndex]
				if strings.Contains(dataTime, "Q1") {
					dataTime = strings.Replace(dataTime, "Q1", "-01", -1)
					dataTime += "-31"
				}
				if strings.Contains(dataTime, "Q2") {
					dataTime = strings.Replace(dataTime, "Q2", "-06", -1)
					dataTime += "-30"
				}
				if strings.Contains(dataTime, "Q3") {
					dataTime = strings.Replace(dataTime, "Q3", "-09", -1)
					dataTime += "-30"
				}
				if strings.Contains(dataTime, "Q4") {
					dataTime = strings.Replace(dataTime, "Q4", "-12", -1)
					dataTime += "-31"
				}

				if strings.Contains(dataTime, "H1") {
					dataTime = strings.Replace(dataTime, "H1", "-06", -1)
					dataTime += "-30"
				}

				if strings.Contains(dataTime, "H2") {
					dataTime = strings.Replace(dataTime, "H2", "-12", -1)
					dataTime += "-31"
				}

				if frequency == "月" {
					monthDate, err := time.Parse("2006-01", dataTime)
					if err != nil {
						fmt.Println("time.Parse:" + err.Error())
					}
					lastTime := monthDate.AddDate(0, 1, -1)
					lastYear, lastMonth, lastDay := lastTime.Date()
					var lastDate string
					if int(lastMonth) < 10 {
						lastDate = strconv.Itoa(lastYear) + "-" + "0" + strconv.Itoa(int(lastMonth)) + "-" + strconv.Itoa(lastDay)
					} else {
						lastDate = strconv.Itoa(lastYear) + "-" + strconv.Itoa(int(lastMonth)) + "-" + strconv.Itoa(lastDay)
					}
					dataTime = lastDate
				} else if frequency == "年" {
					dataTime = dataTime + "-12-31"
				}
				saveDataTime, err := time.Parse(utils.FormatDate, dataTime)
				if err != nil {
					fmt.Println("saveDataTime parse err:" + err.Error() + " dataTime:" + dataTime)
					panic(err)
				}
				timestamp := saveDataTime.UnixNano() / 1e6
				//循环指标
				for _, v := range indexMap {

					indexCode := indexCodeMap[v]
					smmIndexId := smmIndexIdMap[v]
					dataVal := dv[v]

					if indexCode != "" {

						key := indexCode + dataTime
						val := strings.Replace(dataVal, ",", "", -1)
						if findData, dataOk := existDataMap[key]; !dataOk {
							if _, addOK := addExistDataMap[key]; !addOK {
								if val != "" && val != "-" {
									dataItem := new(data_manage.BaseFromSmmData)
									dataItem.BaseFromSmmIndexId = int(smmIndexId)
									dataItem.IndexCode = indexCode
									dataItem.DataTime = dataTime
									dataItem.Value = val
									dataItem.CreateTime = time.Now()
									dataItem.ModifyTime = time.Now()
									dataItem.DataTimestamp = timestamp
									_, err = data_manage.AddBaseFromSmmData(dataItem)
									if err != nil && !strings.Contains(err.Error(), "idx_index_code_date") {
										fmt.Println("AddBaseFromSmmData Err:" + err.Error())
										return
									}
								}
							}
						} else {
							if findData != nil && findData.Value != val { //修改
								if _, addOK := addExistDataMap[key]; !addOK {
									if val != "" && val != "-" {
										err = data_manage.ModifyBaseFromSmmData(findData.SmmDataId, val)
										if err != nil {
											fmt.Println("ModifyBaseFromSmmData Err:" + err.Error())
											return
										}
									}
								}
							}
						}
						addExistDataMap[key] = key
					}
				}
			}

			//修改数据开始，结束日期
			{
				indexList, err := data_manage.GetBaseFromSmmIndexByCode(v.Interface)
				if err != nil {
					fmt.Println("GetBaseFromSmmIndexByCode Err:" + err.Error())
				}
				for _, sv := range indexList {
					minDate, maxDate, err := data_manage.GetBaseFromSmmMaxOrMinDate(sv.IndexCode)
					if err != nil {
						fmt.Println("GetEdbDataSmmMaxOrMinDate Err:" + err.Error())
					} else {
						err = data_manage.ModifyBaseFromSmmMinDateAndMaxDate(sv.BaseFromSmmIndexId, v.BaseFromSmmId, minDate, maxDate)
						if err != nil {
							fmt.Println("ModifyBaseFromSmmMinDateAndMaxDate Err:" + err.Error())
						}
					}
				}
			}
		}
		fmt.Println(v.Interface, v.Name, k)
		time.Sleep(1 * time.Second)
	}
	utils.FileLog.Info("end:" + time.Now().Format(utils.FormatDateTime))
}
*/

const (
	dataUrl = "https://dataapi.smm.cn/GetData/" //data url (中文)
	//dataUrl = "https://dataapi.smm.cn/GetDataEn/" //data url (english edition)
	authUrl = "https://platform.smm.cn/usercenter/auth" // auth url (for all)
)

type TokenResp struct {
	Code int       `json:"Code"`
	Msg  string    `json:"Msg"`
	Data TokenData `json:"Data"`
}

type TokenData struct {
	Token string `json:"Token"`
}

//获取token
func getToken(userName string, password string) (string, error) {
	encryptAuth := md5.New()
	encryptAuth.Write([]byte(password)) //encrypt password with md5
	newPassword := hex.EncodeToString(encryptAuth.Sum(nil))

	resp, err := http.PostForm(authUrl, url.Values{"user_name": {userName}, "password": {newPassword}})
	if err != nil {
		return "", err
	}

	defer resp.Body.Close()

	body, err := ioutil.ReadAll(resp.Body)
	if err != nil {
		fmt.Println("reponse error", err)
		return "", err
	}

	var bodyJsonContent TokenResp

	if err = json.Unmarshal([]byte(body), &bodyJsonContent); err != nil {
		fmt.Println(err, "unmarsal failure")
		return "", err
	}

	var token string
	if bodyJsonContent.Code == 0 {
		token = bodyJsonContent.Data.Token
	}

	//print(token)
	return token, nil
}

//request response
type DataResp struct {
	Code int      `json:"Code"`
	Msg  string   `json:"Msg"`
	Data *ApiData `json:"Data"`
}

//api data response
type ApiData struct {
	Status  int        `json:"Status"` //0 no permission，1 ok
	Field   []ApiField `json:"Field"`
	Content [][]string `json:"Content"`
}

//api title
type ApiField struct {
	Unit       string `json:"Unit"`
	Info       string `json:"Info"`
	Name       string `json:"Name"`
	ColumnType string `json:"ColumnType"`
	ColIndex   uint   `json:"ColIndex"`
	IsDate     string `json:"IsDate"`
}

type YsResult struct {
	Code int64 `json:"Code"`
	Data struct {
		CompanyList []interface{} `json:"CompanyList"`
		Content     [][]string    `json:"Content"`
		Field       []struct {
			ColIndex   int64  `json:"ColIndex"`
			ColumnType string `json:"ColumnType"`
			Info       string `json:"Info"`
			IsDate     string `json:"IsDate"`
			Name       string `json:"Name"`
			Unit       string `json:"Unit"`
			DBColName  string `json:"db_col_name"`
		} `json:"Field"`
		CountPage   int64  `json:"count_page"`
		CurrentPage int64  `json:"current_page"`
		Frequency   string `json:"frequency"`
		Mindate     string `json:"mindate"`
		PageNum     int64  `json:"page_num"`
		Status      int64  `json:"status"`
		TotalNum    int64  `json:"total_num"`
	} `json:"Data"`
	Msg string `json:"Msg"`
}

/*
 * request data
 * sdatetime,edatetime ==>format:yyyy-mm-dd,
 * apiName ==> data.metal.com(for english)/data.smm.cn (for chinese)
 */
func getApiData(token string, apiName string, sdatetime string, edatetime string) (item *YsResult, err error) {
	reqUrl := dataUrl + apiName
	resp, err := http.PostForm(reqUrl, url.Values{"token": {token}, "sdatetime": {sdatetime}, "edatetime": {edatetime}})
	if err != nil {
		return nil, err
	}

	defer resp.Body.Close()
	body, err := ioutil.ReadAll(resp.Body)
	if err != nil {
		fmt.Println("response error")
		return nil, err
	}
	utils.FileLog.Info("ys result:" + string(body))
	dataJsonContent := new(YsResult)
	if err = json.Unmarshal([]byte(body), &dataJsonContent); err != nil {
		fmt.Println(err, "data unmarshal failure")
		return nil, err
	}

	if dataJsonContent.Code == 200 && len(dataJsonContent.Data.Content) > 0 {
		return dataJsonContent, nil
	} else {
		err = errors.New("code:" + strconv.Itoa(int(dataJsonContent.Code)) + "msg:" + dataJsonContent.Msg)
	}
	return nil, nil
}