eta_api/models/data_manage/trade_analysis/trade_analysis.go

package trade_analysis

import (
	"eta/eta_api/utils"
	"fmt"
	"github.com/beego/beego/v2/client/orm"
	"strings"
	"time"
)

const (
	TradeDataTypeNull      = 0 // 无值
	TradeDataTypeOrigin    = 1 // 原始值
	TradeDataTypeCalculate = 2 // 推算值

	WarehouseBuyChartType     = 1 // 多单图
	WarehouseSoldChartType    = 2 // 空单图
	WarehousePureBuyChartType = 3 // 净多单图

	WarehouseDefaultUnit      = "手"
	WarehouseDefaultFrequency = "日度"

	GuangZhouTopCompanyAliasName = "日成交持仓排名" // 广期所TOP20对应的公司名称
	GuangZhouSeatNameBuy         = "持买单量"    // 广期所指标名称中的多单名称
	GuangZhouSeatNameSold        = "持卖单量"    // 广期所指标名称中的空单名称
	GuangZhouTopSeatNameBuy      = "持买单量总计"  // 广期所指标名称中的TOP20多单名称
	GuangZhouTopSeatNameSold     = "持卖单量总计"  // 广期所指标名称中的TOP20空单名称
	GuangZhouTopSeatNameDeal     = "成交量总计"   // 广期所指标名称中的TOP20成交量名称
)

const (
	TradeExchangeDalian    = "dalian"
	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,
	GuangZhouTopSeatNameDeal: 3,
}

// 合约查询方式
var (
	ContractQueryTypeTop   = 1 // 主力合约
	ContractQueryTypeTop2  = 2 // 成交量前2
	ContractQueryTypeTop3  = 3 // 成交量前3
	ContractQueryTypeAll   = 4 // 所有合约(多个)
	ContractQueryTypeTotal = 5 // 合约加总(1个)
)

// 合约方向
var (
	ContractPositionBuy     = 1 // 多单
	ContractPositionSold    = 2 // 空单
	ContractPositionPureBuy = 3 // 净多单
)

// TradePositionTop 持仓榜单
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.NewOrmUsingDB("data")
	_, 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.NewOrmUsingDB("data")
	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.NewOrmUsingDB("data")
	_, 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 string, classifyNames, contracts, companies []string, startDate, endDate time.Time) (items []*OriginTradeData, err error) {
//	if exchange == "" {
//		err = fmt.Errorf("数据表名称有误")
//		return
//	}
//	if len(contracts) == 0 || len(classifyNames) == 0 || len(companies) == 0 {
//		return
//	}
//	condBuy := fmt.Sprintf(`classify_name IN (%s) AND classify_type IN (%s)`, utils.GetOrmInReplace(len(classifyNames)), utils.GetOrmInReplace(len(contracts)))
//	parsBuy := make([]interface{}, 0)
//	parsBuy = append(parsBuy, classifyNames, contracts)
//
//	condSold := fmt.Sprintf(`classify_name IN (%s) AND classify_type IN (%s)`, utils.GetOrmInReplace(len(classifyNames)), utils.GetOrmInReplace(len(contracts)))
//	parsSold := make([]interface{}, 0)
//	parsSold = append(parsSold, classifyNames, contracts)
//
//	if !startDate.IsZero() && !endDate.IsZero() {
//		st := startDate.Format(utils.FormatDate)
//		ed := endDate.Format(utils.FormatDate)
//		condBuy += fmt.Sprintf(` AND (data_time BETWEEN ? AND ?)`)
//		parsBuy = append(parsBuy, st, ed)
//		condSold += fmt.Sprintf(` AND (data_time BETWEEN ? AND ?)`)
//		parsSold = append(parsSold, st, ed)
//	}
//
//	// 是否含有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,
//			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.NewOrmUsingDB("data")
//	_, err = o.Raw(sql, parsBuy, parsSold).QueryRows(&items)
//	return
//}

// BaseFromTradeCommonIndex 郑商所/大商所/上期所/上期能源指标表通用字段
type BaseFromTradeCommonIndex struct {
	Rank          int       `description:"排名"`
	DealShortName string    `description:"成交量公司简称"`
	DealName      string    `description:"成交量指标名称"`
	DealCode      string    `description:"成交量指标编码"`
	DealValue     int       `description:"成交量"`
	DealChange    int       `description:"成交变化量"`
	BuyShortName  string    `description:"持买单量公司简称"`
	BuyName       string    `description:"持买单量指标名称"`
	BuyCode       string    `description:"持买单量指标编码"`
	BuyValue      int       `description:"持买单量"`
	BuyChange     int       `description:"持买单量变化量"`
	SoldShortName string    `description:"持卖单量公司简称"`
	SoldName      string    `description:"持卖单量指标名称"`
	SoldCode      string    `description:"持卖单量指标编码"`
	SoldValue     int       `description:"持卖单量"`
	SoldChange    int       `description:"持卖单变化量"`
	Frequency     string    `description:"频度"`
	ClassifyName  string    `description:"品种"`
	ClassifyType  string    `description:"合约"`
	CreateTime    time.Time `description:"创建时间"`
	ModifyTime    time.Time `description:"更新时间"`
	DataTime      time.Time `description:"数据日期"`
}

// GetTradeDataByContracts 根据合约获取持仓数据
func GetTradeDataByContracts(exchange string, classifyNames, contracts []string, startDate, endDate time.Time) (items []*BaseFromTradeCommonIndex, err error) {
	if exchange == "" {
		err = fmt.Errorf("数据表名称有误")
		return
	}
	var cond string
	var pars []interface{}
	if len(classifyNames) > 0 {
		cond += fmt.Sprintf(` AND classify_name IN (%s)`, utils.GetOrmInReplace(len(classifyNames)))
		pars = append(pars, classifyNames)
	}
	if len(contracts) > 0 {
		cond += fmt.Sprintf(` AND classify_type IN (%s)`, utils.GetOrmInReplace(len(contracts)))
		pars = append(pars, contracts)
	}
	if !startDate.IsZero() && !endDate.IsZero() {
		cond += ` AND (data_time BETWEEN ? AND ?)`
		pars = append(pars, startDate.Format(utils.FormatDate), endDate.Format(utils.FormatDate))
	}
	fields := []string{"rank", "buy_short_name", "buy_value", "buy_change", "sold_short_name", "sold_value", "sold_change", "classify_name", "classify_type", "data_time"}
	tableName := fmt.Sprintf("base_from_trade_%s_index", exchange)
	sql := fmt.Sprintf(`SELECT %s FROM %s WHERE 1=1 %s ORDER BY data_time DESC`, strings.Join(fields, ","), tableName, cond)
	_, err = orm.NewOrmUsingDB("data").Raw(sql, pars).QueryRows(&items)
	return
}

// GetZhengzhouTradeDataByContracts 郑商所-根据合约获取持仓数据
func GetZhengzhouTradeDataByContracts(classifyNames []string, startDate, endDate time.Time) (items []*BaseFromTradeCommonIndex, err error) {
	var cond string
	var pars []interface{}
	if len(classifyNames) > 0 {
		cond += fmt.Sprintf(` AND classify_name IN (%s)`, utils.GetOrmInReplace(len(classifyNames)))
		pars = append(pars, classifyNames)
	}
	if !startDate.IsZero() && !endDate.IsZero() {
		cond += ` AND (data_time BETWEEN ? AND ?)`
		pars = append(pars, startDate.Format(utils.FormatDate), endDate.Format(utils.FormatDate))
	}
	// ps.classify_name实为合约代码
	fields := []string{"rank", "buy_short_name", "buy_value", "buy_change", "sold_short_name", "sold_value", "sold_change", "classify_name AS classify_type", "data_time"}
	sql := fmt.Sprintf(`SELECT %s FROM base_from_trade_zhengzhou_index WHERE 1=1 %s ORDER BY data_time DESC`, strings.Join(fields, ","), cond)
	_, err = orm.NewOrmUsingDB("data").Raw(sql, pars).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.NewOrmUsingDB("data")
//	_, err = o.Raw(sql, parsBuy, parsSold).QueryRows(&items)
//	return
//}

// ContractCompanyTradeData [合约-期货公司]持仓数据
type ContractCompanyTradeData struct {
	Exchange     string                          `description:"交易所"`
	ClassifyName string                          `description:"品种"`
	ClassifyType string                          `description:"合约代码"`
	CompanyName  string                          `description:"期货公司名称"`
	IsTotal      bool                            `description:"是否为合约加总"`
	StartDate    time.Time                       `description:"数据开始日期"`
	EndDate      time.Time                       `description:"数据结束日期"`
	DataList     []*ContractCompanyTradeDataList `description:"数据序列"`
}

// 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 string, classifyNames, contracts []string) (items []*OriginTradeData, err error) {
//	if exchange == "" {
//		err = fmt.Errorf("数据表名称有误")
//		return
//	}
//	if len(classifyNames) == 0 || len(contracts) == 0 {
//		return
//	}
//	classifyReplacer := utils.GetOrmInReplace(len(classifyNames))
//	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 IN (%s) 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 IN (%s) 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 IN (%s) 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 IN (%s) AND tpt.classify_type IN (%s)
//		)`
//	sql = fmt.Sprintf(sql, tableName, tableName, classifyReplacer, contractReplacer, classifyReplacer, contractReplacer, tableName, tableName, classifyReplacer, contractReplacer, classifyReplacer, contractReplacer)
//	o := orm.NewOrmUsingDB("data")
//	_, err = o.Raw(sql, classifyNames, contracts, classifyNames, contracts, classifyNames, contracts, classifyNames, 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.NewOrmUsingDB("data")
//	_, 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, contractIds []int) (list []*BaseFromTradeGuangzhouIndex, err error) {
//	o := orm.NewOrmUsingDB("data")
//	cond := ` base_from_trade_guangzhou_classify_id = ?`
//	pars := make([]interface{}, 0)
//	pars = append(pars, classifyId)
//	if len(contractIds) > 0 {
//		cond += fmt.Sprintf(` AND base_from_trade_guangzhou_contract_id IN (%s)`, utils.GetOrmInReplace(len(contractIds)))
//		pars = append(pars, contractIds)
//	}
//	sql := fmt.Sprintf(`SELECT * FROM base_from_trade_guangzhou_index WHERE 1=1 %s `, cond)
//	_, err = o.Raw(sql, pars).QueryRows(&list)
//	return
//}

func GetBaseFromTradeGuangzhouIndex(classifyIds []int, contracts []string, indexKeyword string) (list []*BaseFromTradeGuangzhouIndex, err error) {
	o := orm.NewOrmUsingDB("data")
	cond := ``
	pars := make([]interface{}, 0)
	if len(classifyIds) > 0 {
		cond += fmt.Sprintf(` AND b.base_from_trade_guangzhou_classify_id IN (%s)`, utils.GetOrmInReplace(len(classifyIds)))
		pars = append(pars, classifyIds)
	}
	if len(contracts) > 0 {
		cond += fmt.Sprintf(` AND b.contract IN (%s)`, utils.GetOrmInReplace(len(contracts)))
		pars = append(pars, contracts)
	}
	if indexKeyword != "" {
		cond += fmt.Sprintf(` AND a.index_name LIKE ?`)
		pars = append(pars, indexKeyword)
	}
	sql := `SELECT a.* FROM base_from_trade_guangzhou_index AS a
		JOIN base_from_trade_guangzhou_contract AS b ON a.base_from_trade_guangzhou_contract_id = b.base_from_trade_guangzhou_contract_id
		WHERE 1=1 %s`
	sql = fmt.Sprintf(sql, cond)
	_, err = o.Raw(sql, pars).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, startDate, endDate time.Time) (list []*BaseFromTradeGuangzhouData, err error) {
	if len(indexIds) == 0 {
		return
	}
	cond := fmt.Sprintf(` AND base_from_trade_guangzhou_index_id IN (%s)`, utils.GetOrmInReplace(len(indexIds)))
	pars := make([]interface{}, 0)
	pars = append(pars, indexIds)
	if !startDate.IsZero() && !endDate.IsZero() {
		if startDate.Equal(endDate) {
			cond += ` AND data_time = ?`
			pars = append(pars, startDate.Format(utils.FormatDate))
		}
		if !startDate.Equal(endDate) {
			cond += ` AND (data_time BETWEEN ? AND ?)`
			pars = append(pars, startDate.Format(utils.FormatDate), endDate.Format(utils.FormatDate))
		}
	}
	sql := fmt.Sprintf(`SELECT * FROM base_from_trade_guangzhou_data WHERE 1=1 %s ORDER BY base_from_trade_guangzhou_index_id`, cond)
	_, err = orm.NewOrmUsingDB("data").Raw(sql, pars).QueryRows(&list)
	return
}

// GetBaseFromTradeGuangzhouMinDataByIndexIds 获取指标中的末位数据
//func GetBaseFromTradeGuangzhouMinDataByIndexIds(indexIds []int) (list []*BaseFromTradeGuangzhouData, err error) {
//	indexLen := len(indexIds)
//	if indexLen == 0 {
//		return
//	}
//	o := orm.NewOrmUsingDB("data")
//	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
//}

// ContractTopRankData TOP20合约排名数据
type ContractTopRankData struct {
	Exchange      string    `description:"交易所"`
	DealValue     int       `description:"成交量"`
	BuyValue      int       `description:"多单持仓量"`
	BuyChange     int       `description:"多单变化"`
	SoldValue     int       `description:"空单持仓量"`
	SoldChange    int       `description:"空单变化"`
	PureBuyValue  int       `description:"净多单持仓量"`
	PureBuyChange int       `description:"净多单变化"`
	ClassifyName  string    `description:"品种名称"`
	ClassifyType  string    `description:"合约代码"`
	DataTime      time.Time `description:"数据日期"`
}

// GetContractTopRankData 获取合约TOP20根据当日成交量排名
func GetContractTopRankData(exchange string, classifyNames []string, dataDate time.Time) (items []*ContractTopRankData, err error) {
	if exchange == "" {
		err = fmt.Errorf("数据表名称有误")
		return
	}
	if len(classifyNames) == 0 {
		return
	}
	tableName := fmt.Sprintf("base_from_trade_%s_index", exchange)
	// 大商所存在TOP20的rank=0
	queryRank := ` rank = 999`
	if exchange == TradeExchangeDalian {
		queryRank = ` (rank = 999 OR rank = 0)`
	}
	sql := `SELECT * FROM %s WHERE data_time = ? AND classify_name IN (%s) AND %s GROUP BY classify_type ORDER BY deal_value DESC`
	sql = fmt.Sprintf(sql, tableName, utils.GetOrmInReplace(len(classifyNames)), queryRank)
	_, err = orm.NewOrmUsingDB("data").Raw(sql, dataDate.Format(utils.FormatDate), classifyNames).QueryRows(&items)
	return
}

// GetZhengzhouContractTopRankData 郑商所-获取合约根据当日成交量排名
func GetZhengzhouContractTopRankData(classifyNames []string, dataDate time.Time) (items []*ContractTopRankData, err error) {
	if len(classifyNames) == 0 {
		return
	}
	sql := `SELECT * FROM base_from_trade_zhengzhou_index WHERE data_time = ? AND classify_name IN (%s) AND rank = 999 GROUP BY classify_name ORDER BY deal_value DESC`
	sql = fmt.Sprintf(sql, utils.GetOrmInReplace(len(classifyNames)))
	_, err = orm.NewOrmUsingDB("data").Raw(sql, dataDate.Format(utils.FormatDate), classifyNames).QueryRows(&items)
	return
}

// ContractCompanyTradeEdb [合约-期货公司]指标
type ContractCompanyTradeEdb struct {
	Exchange         string                         `description:"交易所"`
	ClassifyName     string                         `description:"品种"`
	ClassifyType     string                         `description:"合约代码"`
	CompanyName      string                         `description:"期货公司名称"`
	IsTotal          bool                           `description:"是否为合约加总"`
	ContractPosition int                            `description:"合约方向"`
	StartDate        time.Time                      `description:"数据开始日期"`
	EndDate          time.Time                      `description:"数据结束日期"`
	DataList         []*ContractCompanyTradeEdbData `description:"数据序列"`
}

// ContractCompanyTradeEdbData [合约-期货公司]指标数据
type ContractCompanyTradeEdbData struct {
	DataTime time.Time `description:"数据日期"`
	Val      int       `description:"数据值"`
}

// ContractCompanyTradeEdbData [合约-期货公司]指标数据
//type ContractCompanyTradeEdbData struct {
//	DataTime time.Time `description:"数据日期"`
//	Val      int       `description:"数据值"`
//}