eta_index_lib/models/future_good/future_good_edb_data.go

package future_good

import (
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"hongze/hongze_edb_lib/utils"
	"strconv"
	"strings"
	"time"
)

// FutureGoodEdbData 期货指标数据的表
type FutureGoodEdbData struct {
	FutureGoodEdbDataId int       `orm:"column(future_good_edb_data_id);pk"`
	FutureGoodEdbInfoId int       `description:"期货指标id"`
	FutureGoodEdbCode   string    `description:"期货指标code"`
	DataTime            time.Time `description:"数据日期"`
	TradeCode           string    `description:"证券代码"`
	Open                float64   `description:"开盘价"`
	High                float64   `description:"最高价"`
	Low                 float64   `description:"最低价"`
	Close               float64   `description:"收盘价"`
	Volume              float64   `description:"成交量"`
	Amt                 float64   `description:"成交额"`
	Oi                  float64   `description:"持仓量"`
	Settle              float64   `description:"结算价"`
	DataTimestamp       int64     `description:"数据日期时间戳"`
	ModifyTime          time.Time
	CreateTime          time.Time
}

// FutureGoodEdbDataItem 期货指标数据的表
type FutureGoodEdbDataItem struct {
	FutureGoodEdbDataId int     `orm:"column(future_good_edb_data_id);pk"`
	FutureGoodEdbInfoId int     `description:"期货指标id"`
	FutureGoodEdbCode   string  `description:"期货指标code"`
	DataTime            string  `description:"数据日期"`
	TradeCode           string  `description:"证券代码"`
	Open                float64 `description:"开盘价"`
	High                float64 `description:"最高价"`
	Low                 float64 `description:"最低价"`
	Close               float64 `description:"收盘价"`
	Volume              float64 `description:"成交量"`
	Amt                 float64 `description:"成交额"`
	Oi                  float64 `description:"持仓量"`
	Settle              float64 `description:"结算价"`
	DataTimestamp       int64   `description:"数据日期时间戳"`
	ModifyTime          time.Time
	CreateTime          time.Time
}

// GetFutureGoodEdbDataList 获取期货指标数据列表
func GetFutureGoodEdbDataList(condition string, pars []interface{}) (list []*FutureGoodEdbDataItem, err error) {
	o := orm.NewOrm()
	sql := `SELECT * FROM future_good_edb_data WHERE 1=1 `
	if condition != "" {
		sql += condition
	}
	sql += `ORDER BY data_time asc `
	_, err = o.Raw(sql, pars).QueryRows(&list)
	return
}

// AddEdbDataFromThs 添加同花顺商品指标数据
func AddEdbDataFromThs(futureGoodEdbInfoId int, edbCode string, item FutureGoodDataFromThs) (err error) {
	var errMsg string
	o := orm.NewOrm()
	defer func() {
		if err != nil {
			//go utils.SendEmail(utils.APP_NAME_CN+"【"+utils.RunMode+"】"+"失败提醒", " 同花顺数据获取失败:err:"+errMsg, utils.EmailSendToUsers)
			go alarm_msg.SendAlarmMsg("wind商品数据获取失败:err:"+errMsg, 3)
		}
	}()

	var isAdd bool
	addSql := ` INSERT INTO future_good_edb_data(future_good_edb_info_id,future_good_edb_code,data_time,trade_code,open,high,low,close,volume,amt,oi,settle,create_time,modify_time,data_timestamp) values `

	table := item.Tables
	dataLen := len(table.Time)
	for k := 0; k < dataLen; k++ {
		eDate := table.Time[k]
		dataTime, err := time.ParseInLocation(utils.FormatDate, eDate, time.Local)
		if err != nil {
			errMsg = " time.Parse :" + err.Error()
			return err
		}
		timestamp := dataTime.UnixNano() / 1e6
		timeStr := fmt.Sprintf("%d", timestamp)

		tradeCode := ``
		open := utils.SubFloatToString(table.Open[k], 20)
		high := utils.SubFloatToString(table.High[k], 20)
		low := utils.SubFloatToString(table.Low[k], 20)
		closeVal := utils.SubFloatToString(table.Close[k], 20)
		volume := utils.SubFloatToString(table.Volume[k], 20)
		amt := utils.SubFloatToString(table.Amount[k], 20)
		oi := utils.SubFloatToString(table.Ccl[k], 20)
		settle := utils.SubFloatToString(table.Settlement[k], 20)
		addSql += GetAddSql(strconv.Itoa(futureGoodEdbInfoId), edbCode, eDate, tradeCode, open, high, low, closeVal, volume, amt, oi, settle, timeStr)

		isAdd = true

	}

	if isAdd {
		addSql = strings.TrimRight(addSql, ",")
		_, err = o.Raw(addSql).Exec()
		if err != nil {
			errMsg = " tx.Exec Err :" + err.Error()
			return
		}
	}
	return
}

// RefreshFutureEdbEdbInfoReq 刷新商品指标请求
type RefreshFutureEdbEdbInfoReq struct {
	FutureGoodEdbInfoId int    `description:"指标ID"`
	FutureGoodEdbCode   string `description:"指标编码"`
	StartDate           string `description:"开始日期"`
}

// RefreshFutureGoodEdbDataFromThs 刷新wind期货指标数据
func RefreshFutureGoodEdbDataFromThs(futureGoodEdbInfoId int, edbCode, startDate string, item FutureGoodDataFromThs) (err error) {
	o := orm.NewOrm()
	to, err := o.Begin()
	if err != nil {
		return
	}
	defer func() {
		if err != nil {
			fmt.Println("RefreshAllCalculateTbz,Err:" + err.Error())
			_ = to.Rollback()
		} else {
			_ = to.Commit()
		}
	}()

	var existList []*FutureGoodEdbData
	// 获取指标中所有存在的数据值
	{
		var condition string
		var pars []interface{}

		condition += " AND future_good_edb_info_id=? "
		pars = append(pars, futureGoodEdbInfoId)

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

		sql := `SELECT * FROM future_good_edb_data WHERE 1=1 `
		if condition != "" {
			sql += condition
		}
		sql += `ORDER BY data_time asc `
		_, err = to.Raw(sql, pars).QueryRows(&existList)
	}

	existMap := make(map[string]*FutureGoodEdbData)
	for _, v := range existList {
		existMap[v.DataTime.Format(utils.FormatDate)] = v
	}
	addSql := ` INSERT INTO future_good_edb_data(future_good_edb_info_id,future_good_edb_code,data_time,trade_code,open,high,low,close,volume,amt,oi,settle,create_time,modify_time,data_timestamp) values `
	var isAdd bool
	addMap := make(map[string]string)

	table := item.Tables
	dataLen := len(table.Time)
	for k := 0; k < dataLen; k++ {
		eDate := table.Time[k]
		dataTime, tmpErr := time.ParseInLocation(utils.FormatDate, eDate, time.Local)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		timestamp := dataTime.UnixNano() / 1e6
		timeStr := fmt.Sprintf("%d", timestamp)

		tradeCode := ``
		open := utils.SubFloatToString(table.Open[k], 20)
		high := utils.SubFloatToString(table.High[k], 20)
		low := utils.SubFloatToString(table.Low[k], 20)
		closeVal := utils.SubFloatToString(table.Close[k], 20)
		volume := utils.SubFloatToString(table.Volume[k], 20)
		amt := utils.SubFloatToString(table.Amount[k], 20)
		oi := utils.SubFloatToString(table.Ccl[k], 20)
		settle := utils.SubFloatToString(table.Settlement[k], 20)

		if findItem, ok := existMap[eDate]; !ok {
			if _, existOk := addMap[eDate]; !existOk {
				addSql += GetAddSql(strconv.Itoa(futureGoodEdbInfoId), edbCode, eDate, tradeCode, open, high, low, closeVal, volume, amt, oi, settle, timeStr)

				addMap[eDate] = "1"
			}
			isAdd = true
		} else {
			if findItem != nil {
				updateCol := make([]string, 0)
				if findItem.TradeCode != tradeCode {
					findItem.TradeCode = tradeCode
					updateCol = append(updateCol, "TradeCode")
				}
				if utils.SubFloatToString(findItem.Open, 30) != open {
					findItem.Open = table.Open[k]
					updateCol = append(updateCol, "Open")
				}
				if utils.SubFloatToString(findItem.High, 30) != high {
					findItem.High = table.High[k]
					updateCol = append(updateCol, "High")
				}
				if utils.SubFloatToString(findItem.Low, 30) != low {
					findItem.Low = table.Low[k]
					updateCol = append(updateCol, "Low")
				}
				if utils.SubFloatToString(findItem.Close, 30) != closeVal {
					findItem.Close = table.Close[k]
					updateCol = append(updateCol, "Close")
				}
				if utils.SubFloatToString(findItem.Volume, 30) != volume {
					findItem.Volume = table.Volume[k]
					updateCol = append(updateCol, "Volume")
				}
				if utils.SubFloatToString(findItem.Amt, 30) != amt {
					findItem.Amt = table.Amount[k]
					updateCol = append(updateCol, "Amt")
				}
				if utils.SubFloatToString(findItem.Oi, 30) != oi {
					findItem.Oi = table.Ccl[k]
					updateCol = append(updateCol, "Oi")
				}
				if utils.SubFloatToString(findItem.Settle, 30) != settle {
					findItem.Settle = table.Settlement[k]
					updateCol = append(updateCol, "Settle")
				}

				if len(updateCol) > 0 {
					_, err = to.Update(findItem, updateCol...)
					if err != nil {
						return
					}
				}
			}
		}

	}

	if isAdd {
		addSql = strings.TrimRight(addSql, ",")
		_, err = to.Raw(addSql).Exec()
		if err != nil {
			fmt.Println("RefreshEdbDataFromWind add Err", err.Error())
			return
		}
	}

	return
}

func GetAddSql(futureGoodEdbInfoId, futureGoodEdbCode, dataTime, tradeCode, open, high, low, close, volume, amt, oi, settle, timestampStr string) (addSql string) {
	//future_good_edb_info_id,future_good_edb_code,data_time,trade_code,open,high,low,close,volume,amt,oi,settle,create_time,modify_time,data_timestamp
	nowStr := time.Now().Format(utils.FormatDateTime)
	//addSql += "("
	//addSql += futureGoodEdbInfoId + "," + "'" + futureGoodEdbCode + "'" + "," + "'" + dataTime + "'" + "," + value + "," + "'" + nowStr + "'" +
	//	"," + "'" + nowStr + "'"
	//addSql += "," + "'" + timestampStr + "'"
	//addSql += "),"
	addSql = fmt.Sprintf("(%s,'%s','%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,'%s','%s',%s),", futureGoodEdbInfoId, futureGoodEdbCode, dataTime, tradeCode, open, high, low, close, volume, amt, oi, settle, nowStr, nowStr, timestampStr)
	return
}

func GetFutureGoodEdbDataListByDate(futureGoodEdbInfoId int, startDate, endDate string) (list []*FutureGoodEdbData, err error) {
	var pars []interface{}
	sql := `SELECT * FROM future_good_edb_data WHERE 1=1 AND future_good_edb_info_id = ? `
	if startDate != "" {
		sql += ` AND data_time>=? `
		pars = append(pars, startDate)
	}
	if endDate != "" {
		sql += ` AND data_time<=? `
		pars = append(pars, endDate)
	}

	sql += ` ORDER BY data_time ASC `
	o := orm.NewOrm()
	_, err = o.Raw(sql, futureGoodEdbInfoId, pars).QueryRows(&list)
	return
}