package trade_analysis import ( "eta/eta_index_lib/utils" "fmt" "github.com/beego/beego/v2/client/orm" "time" ) // 上期能源持仓榜单表 type TradePositionTop struct { Id uint64 `gorm:"primaryKey;column:id" json:"id"` ClassifyName string `gorm:"column:classify_name" json:"classify_name"` //分类名称 ClassifyType string `gorm:"column:classify_type" json:"classify_type"` //分类名称下的类型 DealShortName string `gorm:"column:deal_short_name" json:"deal_short_name"` //成交量公司简称 DealValue int `gorm:"column:deal_value" json:"deal_value"` //成交量 DealChange int `gorm:"column:deal_change" json:"deal_change"` //成交变化量 DataTime time.Time `gorm:"column:data_time" json:"data_time"` //数据日期 CreateTime time.Time `gorm:"column:create_time" json:"create_time"` //插入时间 ModifyTime time.Time `gorm:"column:modify_time" json:"modify_time"` //修改时间 DealType int `gorm:"column:deal_type" json:"deal_type"` //交易类型：1多单，2空单，3净多单，4净空单 SourceType int `gorm:"column:source_type" json:"source_type"` //数据来源，0是原始数据的值，1是由T+1日推算出的值，2是由T日的榜单数据推算出的值 Rank int `gorm:"column:rank" json:"rank"` //排名 } type TradeClassifyNameList struct { Exchange string `description:"交易所"` ExchangeEn string `description:"交易所英文"` Sort int `description:"排序字段" ` Num int `description:"品种数量"` DataTime string `description:"最新更新时间"` CurrDate string `description:"当前日期"` Items []TradeClassifyNameListItem `description:"子类"` } type TradeClassifyNameListSort []TradeClassifyNameList func (v TradeClassifyNameListSort) Len() int { return len(v) } func (v TradeClassifyNameListSort) Swap(i, j int) { v[i], v[j] = v[j], v[i] } func (v TradeClassifyNameListSort) Less(i, j int) bool { return v[i].Sort < v[j].Sort } type TradeClassifyNameListItemSort []TradeClassifyNameListItem func (v TradeClassifyNameListItemSort) Len() int { return len(v) } func (v TradeClassifyNameListItemSort) Swap(i, j int) { v[i], v[j] = v[j], v[i] } func (v TradeClassifyNameListItemSort) Less(i, j int) bool { return v[i].ClassifyName < v[j].ClassifyName } type TradeClassifyNameListItem struct { ClassifyName string `description:"交易分类"` Items []TradeClassifyNameListItemItem `description:"合约代码"` } type TradeClassifyNameListItemItemSort []TradeClassifyNameListItemItem func (v TradeClassifyNameListItemItemSort) Len() int { return len(v) } func (v TradeClassifyNameListItemItemSort) Swap(i, j int) { v[i], v[j] = v[j], v[i] } func (v TradeClassifyNameListItemItemSort) Less(i, j int) bool { return v[i].ClassifyType < v[j].ClassifyType } type TradeClassifyNameListItemItem struct { ClassifyType string `description:"分类名称下的类型"` } type TradeClassifyName struct { ClassifyName string //分类名称 ClassifyType string //分类名称下的类型 LatestDate string //分类下最晚日期 } // GetExchangeClassify 获取交易所分类列表 func GetExchangeClassify(exchange string) (list []TradeClassifyName, err error) { tableName := "base_from_trade_" + exchange + "_index" orderStr := "classify_name DESC, classify_type asc" if exchange == "zhengzhou" { orderStr = "classify_name asc" } sql := "SELECT classify_name, classify_type FROM " + tableName + " WHERE `rank` <=20 and `rank` > 0 GROUP BY classify_name, classify_type " sql += ` ORDER BY ` + orderStr o := orm.NewOrm() _, err = o.Raw(sql).QueryRows(&list) return } type LastTimeItem struct { CreateTime time.Time } // GetExchangeLastTime 获取交易所数据最晚的时间 func GetExchangeLastTime(exchange string) (item LastTimeItem, err error) { tableName := "base_from_trade_" + exchange + "_index" sql := `SELECT create_time FROM ` + tableName + ` ORDER BY create_time desc` o := orm.NewOrm() err = o.Raw(sql).QueryRow(&item) return } type GetPositionTopReq struct { Exchange string `json:"exchange" form:"exchange"` //交易所 ClassifyName string `json:"classify_name" form:"classify_name"` //分类名称 ClassifyType string `json:"classify_type" form:"classify_type"` //具体合约名称 DataTime string `json:"data_time" form:"data_time"` //请求日期，如果为空，则返回最新的榜单日期 } type GetPositionTopResp struct { BuyList GetPositionTopList `description:"多单列表"` SoldList GetPositionTopList `description:"空单列表"` CleanBuyList GetPositionTopList `description:"净多单列表"` CleanSoldList GetPositionTopList `description:"净空单列表"` DataTime string `description:"最新日期或者请求日期"` LastDataTime string `description:"最新日期"` } type GetPositionTopList struct { TotalDealValue int `description:"总计成交量"` TotalDealChange int `description:"校昨日变化"` List []GetPositionTopListItem `description:"榜单详情列表"` } type GetPositionTopListItem struct { DealShortName string `description:"成交量公司简称"` DealValue int `description:"成交量"` DealChange int `description:"成交变化量"` Rank int `description:"当前名次"` Rate string `description:"当前占比"` BeforeAllRate string `description:"排在前面的成交量总计占比（包含）"` BeforeAllValue int `description:"排在前面的成交量总计"` BeforeAllChange int `description:"排在前面的成交量增减总计"` } func GetTradePositionTop(exchange string, classifyName, classifyType, dataTime string) (list []TradePositionTop, err error) { tableName := "trade_position_" + exchange + "_top" sql := `SELECT * FROM ` + tableName + " WHERE classify_name=? and classify_type=? and data_time=? and `rank` <=20 and `rank` > 0 ORDER BY deal_value desc" o := orm.NewOrm() _, err = o.Raw(sql, classifyName, classifyType, dataTime).QueryRows(&list) return } type OriginTradeData struct { Rank int `description:"排名"` CompanyName string `description:"期货公司名称"` Val int `description:"持仓量"` ValChange int `description:"持仓增减"` DataTime time.Time `description:"数据日期"` ClassifyName string `description:"品种名称"` ClassifyType string `description:"合约代码"` ValType int `description:"数据类型: 1-多单; 2-空单"` } // GetTradeDataByClassifyAndCompany 根据品种和公司名称获取持仓数据 func GetTradeDataByClassifyAndCompany(exchange, classifyName string, contracts, companies []string) (items []*OriginTradeData, err error) { if exchange == "" { err = fmt.Errorf("数据表名称有误") return } if len(contracts) == 0 || len(companies) == 0 { return } condBuy := fmt.Sprintf(`classify_name = ? AND classify_type IN (%s)`, utils.GetOrmInReplace(len(contracts))) parsBuy := make([]interface{}, 0) parsBuy = append(parsBuy, classifyName, contracts) condSold := fmt.Sprintf(`classify_name = ? AND classify_type IN (%s)`, utils.GetOrmInReplace(len(contracts))) parsSold := make([]interface{}, 0) parsSold = append(parsSold, classifyName, contracts) // 是否含有TOP20 var hasTop bool var condCompanies []string for _, v := range companies { if v == TradeFuturesCompanyTop20 { hasTop = true continue } condCompanies = append(condCompanies, v) } if !hasTop { if len(condCompanies) == 0 { err = fmt.Errorf("查询条件-期货公司异常") return } condBuy += fmt.Sprintf(` AND buy_short_name IN (%s)`, utils.GetOrmInReplace(len(condCompanies))) parsBuy = append(parsBuy, condCompanies) condSold += fmt.Sprintf(` AND sold_short_name IN (%s)`, utils.GetOrmInReplace(len(condCompanies))) parsSold = append(parsSold, condCompanies) } else { // 这里rank=0或者999是因为大商所的数据并不只有999 if len(condCompanies) > 0 { condBuy += fmt.Sprintf(` AND (rank = 999 OR rank = 0 OR buy_short_name IN (%s))`, utils.GetOrmInReplace(len(condCompanies))) condSold += fmt.Sprintf(` AND (rank = 999 OR rank = 0 OR sold_short_name IN (%s))`, utils.GetOrmInReplace(len(condCompanies))) parsBuy = append(parsBuy, condCompanies) parsSold = append(parsSold, condCompanies) } else { condBuy += ` AND (rank = 999 OR rank = 0)` condSold += ` AND (rank = 999 OR rank = 0)` } } tableName := fmt.Sprintf("base_from_trade_%s_index", exchange) sql := `SELECT rank, buy_short_name AS company_name, buy_value AS val, buy_change AS val_change, classify_name, classify_type, data_time, 1 AS val_type FROM %s WHERE %s UNION ALL ( SELECT rank, sold_short_name, sold_value, sold_change, classify_name, classify_type, data_time, 2 AS val_type FROM %s WHERE %s )` sql = fmt.Sprintf(sql, tableName, condBuy, tableName, condSold) o := orm.NewOrm() _, err = o.Raw(sql, parsBuy, parsSold).QueryRows(&items) return } // GetTradeZhengzhouDataByClassifyAndCompany 郑商所-根据品种和公司名称获取持仓数据 func GetTradeZhengzhouDataByClassifyAndCompany(exchange string, contracts, companies []string) (items []*OriginTradeData, err error) { if exchange == "" { err = fmt.Errorf("数据表名称有误") return } if len(contracts) == 0 || len(companies) == 0 { return } condBuy := fmt.Sprintf(`classify_name IN (%s)`, utils.GetOrmInReplace(len(contracts))) parsBuy := make([]interface{}, 0) parsBuy = append(parsBuy, contracts) condSold := fmt.Sprintf(`classify_name IN (%s)`, utils.GetOrmInReplace(len(contracts))) parsSold := make([]interface{}, 0) parsSold = append(parsSold, contracts) // 是否含有TOP20 var hasTop bool var condCompanies []string for _, v := range companies { if v == TradeFuturesCompanyTop20 { hasTop = true continue } condCompanies = append(condCompanies, v) } if !hasTop { if len(condCompanies) == 0 { err = fmt.Errorf("查询条件-期货公司异常") return } condBuy += fmt.Sprintf(` AND buy_short_name IN (%s)`, utils.GetOrmInReplace(len(condCompanies))) parsBuy = append(parsBuy, condCompanies) condSold += fmt.Sprintf(` AND sold_short_name IN (%s)`, utils.GetOrmInReplace(len(condCompanies))) parsSold = append(parsSold, condCompanies) } else { if len(condCompanies) > 0 { condBuy += fmt.Sprintf(` AND (rank = 999 OR buy_short_name IN (%s))`, utils.GetOrmInReplace(len(condCompanies))) condSold += fmt.Sprintf(` AND (rank = 999 OR sold_short_name IN (%s))`, utils.GetOrmInReplace(len(condCompanies))) parsBuy = append(parsBuy, condCompanies) parsSold = append(parsSold, condCompanies) } else { condBuy += ` AND rank = 999` condSold += ` AND rank = 999` } } tableName := fmt.Sprintf("base_from_trade_%s_index", exchange) sql := `SELECT rank, buy_short_name AS company_name, buy_value AS val, buy_change AS val_change, classify_name AS classify_type, data_time, 1 AS val_type FROM %s WHERE %s UNION ALL ( SELECT rank, sold_short_name, sold_value, sold_change, classify_name AS classify_type, data_time, 2 AS val_type FROM %s WHERE %s )` sql = fmt.Sprintf(sql, tableName, condBuy, tableName, condSold) o := orm.NewOrm() _, err = o.Raw(sql, parsBuy, parsSold).QueryRows(&items) return } // ContractCompanyTradeData [合约-期货公司]持仓数据 type ContractCompanyTradeData struct { CompanyName string `description:"期货公司名称"` ClassifyType string `description:"合约代码"` StartDate time.Time `description:"数据开始日期"` EndDate time.Time `description:"数据结束日期"` DataList []*ContractCompanyTradeDataList `description:"数据序列"` } const ( TradeDataTypeNull = 0 // 无值 TradeDataTypeOrigin = 1 // 原始值 TradeDataTypeCalculate = 2 // 推算值 WarehouseBuyChartType = 1 // 多单图 WarehouseSoldChartType = 2 // 空单图 WarehousePureBuyChartType = 3 // 净多单图 WarehouseDefaultUnit = "手" WarehouseDefaultFrequency = "日度" GuangZhouTopCompanyAliasName = "日成交持仓排名" // 广期所TOP20对应的公司名称 GuangZhouSeatNameBuy = "持买单量" // 广期所指标名称中的多单名称 GuangZhouSeatNameSold = "持卖单量" // 广期所指标名称中的空单名称 GuangZhouTopSeatNameBuy = "持买单量总计" // 广期所指标名称中的TOP20多单名称 GuangZhouTopSeatNameSold = "持卖单量总计" // 广期所指标名称中的TOP20空单名称 ) const ( TradeExchangeZhengzhou = "zhengzhou" TradeExchangeGuangzhou = "guangzhou" ) var WarehouseTypeSuffixNames = map[int]string{ WarehouseBuyChartType: "席位多单", WarehouseSoldChartType: "席位空单", WarehousePureBuyChartType: "席位净多单", } // GuangzhouSeatNameValType 广期所数据名称对应的席位方向 var GuangzhouSeatNameValType = map[string]int{ GuangZhouSeatNameBuy: 1, GuangZhouSeatNameSold: 2, GuangZhouTopSeatNameBuy: 1, GuangZhouTopSeatNameSold: 2, } // ContractCompanyTradeDataList [合约-期货公司]持仓数据详情 type ContractCompanyTradeDataList struct { Date time.Time `description:"数据日期"` BuyVal int `description:"多单持仓量"` BuyValType int `description:"多单数据类型: 0-无值; 1-原始值; 2-推算值"` BuyChange int `description:"多单持仓增减"` BuyChangeType int `description:"多单持仓增减类型: 0-无值; 1-原始值; 2-推算值"` SoldVal int `description:"空单持仓量"` SoldValType int `description:"空单数据类型: 0-无值; 1-原始值; 2-推算值"` SoldChange int `description:"空单持仓增减"` SoldChangeType int `description:"空单持仓增减类型: 0-无值; 1-原始值; 2-推算值"` PureBuyVal int `description:"净多单持仓量"` PureBuyValType int `description:"净多单数据类型: 0-无值; 1-原始值; 2-推算值"` PureBuyChange int `description:"净多单持仓增减"` PureBuyChangeType int `description:"净多单持仓增减类型: 0-无值; 1-原始值; 2-推算值"` } // GetLastTradeDataByClassify 获取[合约]末位多空单数据 func GetLastTradeDataByClassify(exchange, classifyName string, contracts []string) (items []*OriginTradeData, err error) { if exchange == "" { err = fmt.Errorf("数据表名称有误") return } if len(contracts) == 0 { return } contractReplacer := utils.GetOrmInReplace(len(contracts)) tableName := fmt.Sprintf("base_from_trade_%s_index", exchange) sql := `SELECT tpt.rank, tpt.buy_short_name AS company_name, tpt.buy_value AS val, tpt.buy_change AS val_change, tpt.classify_name, tpt.classify_type, tpt.data_time, 1 AS val_type FROM %s tpt JOIN ( SELECT data_time, classify_type, MAX(rank) AS max_rank FROM %s WHERE classify_name = ? AND classify_type IN (%s) AND buy_short_name <> '' GROUP BY data_time, classify_type ) sub ON tpt.data_time = sub.data_time AND tpt.classify_type = sub.classify_type AND tpt.rank = sub.max_rank WHERE tpt.classify_name = ? AND tpt.classify_type IN (%s) UNION ALL ( SELECT tpt.rank, tpt.sold_short_name, tpt.sold_value, tpt.sold_change, tpt.classify_name, tpt.classify_type, tpt.data_time, 2 AS val_type FROM %s tpt JOIN ( SELECT data_time, classify_type, MAX(rank) AS max_rank FROM %s WHERE classify_name = ? AND classify_type IN (%s) AND sold_short_name <> '' GROUP BY data_time, classify_type ) sub ON tpt.data_time = sub.data_time AND tpt.classify_type = sub.classify_type AND tpt.rank = sub.max_rank WHERE tpt.classify_name = ? AND tpt.classify_type IN (%s) )` sql = fmt.Sprintf(sql, tableName, tableName, contractReplacer, contractReplacer, tableName, tableName, contractReplacer, contractReplacer) o := orm.NewOrm() _, err = o.Raw(sql, classifyName, contracts, classifyName, contracts, classifyName, contracts, classifyName, contracts).QueryRows(&items) return } // GetLastTradeZhengzhouDataByClassify 郑商所-获取[合约]末位多空单数据 func GetLastTradeZhengzhouDataByClassify(exchange string, contracts []string) (items []*OriginTradeData, err error) { if exchange == "" { err = fmt.Errorf("数据表名称有误") return } if len(contracts) == 0 { return } contractReplacer := utils.GetOrmInReplace(len(contracts)) tableName := fmt.Sprintf("base_from_trade_%s_index", exchange) sql := `SELECT tpt.rank, tpt.buy_short_name AS company_name, tpt.buy_value AS val, tpt.buy_change AS val_change, tpt.classify_name AS classify_type, tpt.data_time, 1 AS val_type FROM %s tpt JOIN ( SELECT data_time, classify_name, MAX(rank) AS max_rank FROM %s WHERE classify_name IN (%s) AND buy_short_name <> '' GROUP BY data_time, classify_name ) sub ON tpt.data_time = sub.data_time AND tpt.classify_name = sub.classify_name AND tpt.rank = sub.max_rank WHERE tpt.classify_name IN (%s) UNION ALL ( SELECT tpt.rank, tpt.sold_short_name, tpt.sold_value, tpt.sold_change, tpt.classify_name AS classify_type, tpt.data_time, 2 AS val_type FROM %s tpt JOIN ( SELECT data_time, classify_name, MAX(rank) AS max_rank FROM %s WHERE classify_name IN (%s) AND sold_short_name <> '' GROUP BY data_time, classify_name ) sub ON tpt.data_time = sub.data_time AND tpt.classify_name = sub.classify_name AND tpt.rank = sub.max_rank WHERE tpt.classify_name IN (%s) )` sql = fmt.Sprintf(sql, tableName, tableName, contractReplacer, contractReplacer, tableName, tableName, contractReplacer, contractReplacer) o := orm.NewOrm() _, err = o.Raw(sql, contracts, contracts, contracts, contracts).QueryRows(&items) return } type BaseFromTradeGuangzhouIndex struct { BaseFromTradeGuangzhouIndexId int `orm:"column(base_from_trade_guangzhou_index_id);pk"` BaseFromTradeGuangzhouClassifyId int `description:"分类id"` IndexCode string `description:"指标编码"` IndexName string `description:"指标名称"` Frequency string `description:"频率"` Unit string `description:"单位"` StartDate string `description:"开始日期"` EndDate string `description:"结束日期"` CreateTime time.Time `description:"创建日期"` ModifyTime time.Time `description:"修改日期"` } func GetBaseFromTradeGuangzhouIndexByClassifyId(classifyId int) (list []*BaseFromTradeGuangzhouIndex, err error) { o := orm.NewOrm() sql := `SELECT * FROM base_from_trade_guangzhou_index WHERE base_from_trade_guangzhou_classify_id = ?` _, err = o.Raw(sql, classifyId).QueryRows(&list) return } type BaseFromTradeGuangzhouData struct { BaseFromTradeGuangzhouDataId int `orm:"column(base_from_trade_guangzhou_data_id);pk"` BaseFromTradeGuangzhouIndexId int `description:"指标id"` IndexCode string `description:"指标编码"` DataTime time.Time `description:"数据日期"` Value float64 `description:"数据值"` QtySub float64 `description:"增减"` CreateTime time.Time `description:"创建日期"` ModifyTime time.Time `description:"修改日期"` } // GetBaseFromTradeGuangzhouDataByIndexIds 获取指标数据 func GetBaseFromTradeGuangzhouDataByIndexIds(indexIds []int) (list []*BaseFromTradeGuangzhouData, err error) { if len(indexIds) == 0 { return } o := orm.NewOrm() sql := fmt.Sprintf(`SELECT * FROM base_from_trade_guangzhou_data WHERE base_from_trade_guangzhou_index_id IN (%s) ORDER BY base_from_trade_guangzhou_index_id`, utils.GetOrmInReplace(len(indexIds))) _, err = o.Raw(sql, indexIds).QueryRows(&list) return } // GetBaseFromTradeGuangzhouMinDataByIndexIds 获取指标中的末位数据 func GetBaseFromTradeGuangzhouMinDataByIndexIds(indexIds []int) (list []*BaseFromTradeGuangzhouData, err error) { indexLen := len(indexIds) if indexLen == 0 { return } o := orm.NewOrm() sql := fmt.Sprintf(`SELECT t1.data_time, t1.min_value AS value FROM ( SELECT data_time, MIN(value) AS min_value FROM base_from_trade_guangzhou_data WHERE base_from_trade_guangzhou_index_id IN (%s) GROUP BY data_time ) t1 JOIN base_from_trade_guangzhou_data t2 ON t1.data_time = t2.data_time AND t1.min_value = t2.value AND t2.base_from_trade_guangzhou_index_id IN (%s) GROUP BY t1.data_time`, utils.GetOrmInReplace(indexLen), utils.GetOrmInReplace(indexLen)) _, err = o.Raw(sql, indexIds, indexIds).QueryRows(&list) return }