hz_crm_api/models/data_manage/edb_data_ys.go

/**
* @Author: jgl
* @Date: 2021/9/14 13:33
 */

package data_manage

import (
	"crypto/md5"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hz_crm_api/utils"
	"io/ioutil"
	"net/http"
	"net/url"
	"strconv"
	"strings"
	"time"
)

type BaseFromSmmDataSimple struct {
	SmmDataId          int `orm:"column(smm_data_id);pk"`
	BaseFromSmmIndexId int
	IndexCode          string
	DataTime           string
	Value              string
}

func GetEdbDataYsMaxAndMinDate(edbCode string) (min_date, max_date string, err error) {
	o := orm.NewOrmUsingDB("data")
	sql := ` SELECT MIN(data_time) AS min_date,MAX(data_time) AS max_date FROM edb_data_ys WHERE edb_code=? `
	err = o.Raw(sql, edbCode).QueryRow(&min_date, &max_date)
	return
}

// 有色
func GetEdbDataByYs(edbCode, startDate, endDate string) (searchItem *EdbInfoSearch, err error) {
	o := orm.NewOrmUsingDB("data")
	to, err := o.Begin()
	if err != nil {
		return
	}
	searchItem = new(EdbInfoSearch)
	searchItem.EdbCode = edbCode
	smmBaseDataAll, err := GetBaseFromSmmDataAllByIndexCode(edbCode)
	if err != nil && err.Error() != utils.ErrNoRow() {
		return
	}

	var isAdd bool
	addSql := ` INSERT INTO edb_data_ys(edb_info_id,edb_code,data_time,value,create_time,modify_time,status,data_timestamp) values `
	dataList := make([]*EdbInfoSearchData, 0)
	existMap := make(map[string]string)

	for _, sv := range smmBaseDataAll {
		eDate := sv.DataTime
		dataTime, err := time.Parse(utils.FormatDate, eDate)
		if err != nil {
			fmt.Println("time.Parse Err:" + eDate)
			return nil, err
		}
		timestamp := dataTime.UnixNano() / 1e6
		timeStr := fmt.Sprintf("%d", timestamp)
		if _, ok := existMap[eDate]; !ok {
			addSql += GetAddSql("0", edbCode, eDate, timeStr, sv.Value)
			isAdd = true
		}
		existMap[eDate] = sv.Value
	}
	if isAdd {
		addSql = strings.TrimRight(addSql, ",")
		utils.FileLog.Info("addSql:" + addSql)
		_, err = to.Raw(addSql).Exec()
		if err != nil {
			return searchItem, err
		}
	}
	if err != nil {
		_ = to.Rollback()
	} else {
		_ = to.Commit()
	}
	size := utils.EDB_DATA_LIMIT
	dataList, err = GetEdbDataAllByEdbCode(edbCode, utils.DATA_SOURCE_YS, size)
	if err != nil {
		utils.FileLogData.Info("GetEdbDataThsByCode Err:%s", err.Error())
		return searchItem, err
	}
	minDate, maxDate, err := GetEdbDataYsMaxAndMinDate(edbCode)
	if err != nil {
		return searchItem, err
	}
	searchItem.DataList = dataList
	searchItem.StartDate = minDate
	searchItem.EndDate = maxDate
	if searchItem.DataList == nil {
		searchItem.DataList = make([]*EdbInfoSearchData, 0)
	}
	return
}

// 全部刷新有色数据
func RefreshAllEdbDataByYs(edbInfoId, source int, edbCode, startDate, endDate string) (err error) {
	o := orm.NewOrmUsingDB("data")
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	if err != nil {
		return
	}
	edbInfoIdStr := strconv.Itoa(edbInfoId)
	//获取数据
	err = SyncSmmIndexDataBase(edbCode, startDate, endDate)
	if err != nil {
		err = errors.New("SyncSmmIndexDataBase Err:" + err.Error())
		return err
	}
	//获取已存在指标所有数据
	existDataList := make([]*EdbDataBase, 0)
	dataTableName := GetEdbDataTableName(source)
	sql := `SELECT * FROM %s WHERE edb_info_id=? `
	sql = fmt.Sprintf(sql, dataTableName)
	_, err = to.Raw(sql, edbInfoId).QueryRows(&existDataList)
	if err != nil {
		return err
	}
	existDataMap := make(map[string]string)
	for _, v := range existDataList {
		existDataMap[v.DataTime] = v.Value
	}

	smmDateList := make([]*BaseFromSmmDataSimple, 0)
	smmSql := ` SELECT * FROM base_from_smm_data WHERE index_code=? AND data_time>=? `
	_, err = to.Raw(smmSql, edbCode, startDate).QueryRows(&smmDateList)
	if err != nil {
		return err
	}

	addSql := ` INSERT INTO edb_data_ys(edb_info_id,edb_code,data_time,value,create_time,modify_time,status,data_timestamp) values `
	var isAdd bool
	for _, sv := range smmDateList {
		if existVal, ok := existDataMap[sv.DataTime]; !ok {
			dataTime, err := time.Parse(utils.FormatDate, sv.DataTime)
			if err != nil {
				return err
			}
			timestamp := dataTime.UnixNano() / 1e6
			timeStr := fmt.Sprintf("%d", timestamp)
			addSql += GetAddSql(edbInfoIdStr, edbCode, sv.DataTime, timeStr, sv.Value)
			isAdd = true
		} else {
			if existVal != sv.Value {
				sql := ` UPDATE %s SET value=?,modify_time=NOW() WHERE edb_info_id=? AND data_time=? `
				sql = fmt.Sprintf(sql, dataTableName)
				_, err = to.Raw(sql, sv.Value, edbInfoId, sv.DataTime).Exec()
				if err != nil {
					return err
				}
			}
		}
	}
	if isAdd {
		addSql = strings.TrimRight(addSql, ",")
		_, err = to.Raw(addSql).Exec()
		if err != nil {
			return err
		}
	}
	return
}

func SyncSmmIndexDataBase(edbCode, startDate, endDate string) (err error) {
	utils.FileLog.Info("start:" + time.Now().Format(utils.FormatDateTime))

	var smmCode string
	if strings.Contains(edbCode, "#") {
		smmCode = strings.Split(edbCode, "#")[0]
	} else {
		smmCode = edbCode
	}
	token, err := getToken("pqian@hzinsights.com", "hz123456")
	if err != nil {
		fmt.Println(err)
		return
	}
	baseSmmItem, err := GetBaseFromSmmBySmmCode(smmCode)
	if err != nil {
		fmt.Println(err)
		return
	}

	if baseSmmItem == nil {
		err = errors.New("GetBaseFromSmmBySmmCode Err:" + err.Error())
		return
	}

	smmIndexAll, err := GetBaseFromSmmIndexBySmmCode(smmCode)
	if err != nil {
		fmt.Println("GetBaseFromSmmIndex Err:" + err.Error())
		return
	}
	existIndexMap := make(map[string]*BaseFromSmmIndex)
	for _, item := range smmIndexAll {
		existIndexMap[item.IndexCode] = item
	}

	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 := smmCode + "#" + strconv.Itoa(indexKey)

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

		existDataMap := make(map[string]*BaseFromSmmData)
		for _, mv := range indexCodeMap {
			indexCode := mv
			dataAllList, err := GetBaseFromSmmDataAllByIndexCode(indexCode)
			if err != nil {
				err = errors.New("GetBaseFromSmmData Err:" + err.Error())
				return err
			}
			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 {
				err = errors.New("time.Parse Err:" + err.Error())
				return 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(BaseFromSmmData)
								dataItem.BaseFromSmmIndexId = smmIndexId
								dataItem.IndexCode = indexCode
								dataItem.DataTime = dataTime
								dataItem.Value = val
								dataItem.CreateTime = time.Now()
								dataItem.ModifyTime = time.Now()
								dataItem.DataTimestamp = timestamp
								_, err = AddBaseFromSmmData(dataItem)
								if err != nil && !strings.Contains(err.Error(), "idx_index_code_date") {
									fmt.Println("AddBaseFromSmmData Err:" + err.Error())
									err = errors.New("AddBaseFromSmmData Err:" + err.Error())
									return err
								}
							}
						}
					} else {
						if findData != nil && findData.Value != val { //修改
							if _, addOK := addExistDataMap[key]; !addOK {
								if val != "" && val != "-" {
									err = ModifyBaseFromSmmData(findData.SmmDataId, val)
									if err != nil {
										err = errors.New("ModifyBaseFromSmmData Err:" + err.Error())
										return err
									}
								}
							}
						}
					}
					addExistDataMap[key] = key
				}
			}
		}

		//修改数据开始，结束日期
		{
			indexList, err := GetBaseFromSmmIndexBySmmCode(smmCode)
			if err != nil {
				fmt.Println("GetBaseFromSmmIndexBySmmCode Err:" + err.Error())
			}
			for _, sv := range indexList {
				minDate, maxDate, err := GetBaseFromSmmMaxOrMinDate(sv.IndexCode)
				if err != nil {
					fmt.Println("GetEdbDataSmmMaxOrMinDate Err:" + err.Error())
				} else {
					err = ModifyBaseFromSmmMinDateAndMaxDate(sv.BaseFromSmmIndexId, minDate, maxDate)
					if err != nil {
						fmt.Println("ModifyBaseFromSmmMinDateAndMaxDate Err:" + err.Error())
					}
				}
			}
		}
	}
	return
}

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
}