MTP20_IF/models/wrTrade2.go

/**
* @Author: zou.yingbin
* @Create  : 2021/7/20 18:31
* @Modify  : 2021/7/20 18:31
 */

package models

import (
	"mtp2_if/db"
	"mtp2_if/utils"
)

// WrOrderQuote 买卖大厅行情
type WrOrderQuote struct {
	WRFACTORTYPEID    int64            `json:"wrfactortypeid"  xorm:"'WRFACTORTYPEID'" form:"wrfactortypeid"` // 仓单要素id
	BUYPRICE          float64          `json:"buyprice"  xorm:"'BUYPRICE'"`                                   // 买价
	BUYQTY            float64          `json:"buyqty"  xorm:"'BUYQTY'"`                                       // 买量
	SELLPRICE         float64          `json:"sellprice"  xorm:"'SELLPRICE'"`                                 // 卖价
	SELLQTY           float64          `json:"sellqty"  xorm:"'SELLQTY'"`                                     // 卖量
	WRFACTORTYPENAME  string           `json:"wrfactortypename"  xorm:"'WRFACTORTYPENAME'"`                   // 仓单要素类型名称(选择项要素的名称合并显示，逗号分隔)
	DELIVERYGOODSID   int32            `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`                     // 品种id
	WRSTANDARDID      int32            `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`                           // 商品id
	WAREHOUSEID       int32            `json:"warehouseid"  xorm:"'WAREHOUSEID'" form:"warehouseid"`          // 仓库id
	DELIVERYGOODSNAME string           `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`                 // 品种名称
	DELIVERYGOODSCODE string           `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`                 // 品种代码
	WAREHOUSENAME     string           `json:"warehousename"  xorm:"'WAREHOUSENAME'"`                         // 仓库名称
	WAREHOUSECODE     string           `json:"warehousecode"  xorm:"'WAREHOUSECODE'"`                         // 仓库代码
	WRSTANDARDNAME    string           `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'" form:"wrstandardname"` // 商品名称
	WRSTANDARDCODE    string           `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`                       // 商品代码
	WRGOODSNAME       string           `json:"wrgoodsname"  xorm:"'WRGOODSNAME'"`                             // 商品(商品名称+仓库要素名称 拼接)
	BUYPRICEMOVE      float64          `json:"buypricemove"  xorm:"'BUYPRICEMOVE'"`                           // 买升贴水(基差)
	SELLPRICEMOVE     float64          `json:"sellpricemove"  xorm:"'SELLPRICEMOVE'"`                         // 卖升贴水(基差)
	GOODSID           int32            `json:"goodsid"  xorm:"'GOODSID'"`                                     // 商品id(浮动价列表才有)
	GOODSCODE         string           `json:"goodscode"  xorm:"'GOODSCODE'"`                                 // 商品合约(浮动价列表才有)
	PageEx            `xorm:"extends"` // 页码信息

	HasWr       int32  `json:"-" form:"haswr"`       // 是否有仓单 0-没有 1-有
	WrPriceType int32  `json:"-" form:"wrpricetype"` // 价格方式 - 1:固定价 2-浮动价  -   [挂牌]
	MarketId    int32  `json:"-" form:"marketid"`    // 市场id
	BRANDNAME   string `json:"-" form:"brandname"`   // 品牌名称
}

func (r *WrOrderQuote) calc() {

}

// buildSqlFixedPrice 一口价
func (r *WrOrderQuote) buildSqlFixedPrice() string {
	var sqlId utils.SQLVal = "with buyQuote as" +
		" (" +
		"  select a.wrfactortypeid, a.fixedprice buyPrice, sum(a.orderqty) buyQty" +
		"    from (select t.wrfactortypeid," +
		"                  t.marketid," +
		"                  t.buyorsell," +
		"                  t.orderqty - t.tradeqty as orderqty," +
		"                  t.fixedprice," +
		"                  rank() over(partition by t.wrfactortypeid order by t.fixedprice desc) cnt" +
		"             from wrtrade_orderdetail t" +
		"            where t.wrtradetype = 1 and t.buyorsell = 0 and t.wrtradeorderstatus in(3,10) %v" +
		"          ) a" +
		"   where a.cnt = 1" +
		"   group by a.wrfactortypeid, a.fixedprice)," +
		"sellQuote as" +
		" (" +
		"  select a.wrfactortypeid, a.fixedprice sellPrice, sum(a.orderqty) sellQty" +
		"    from (select t.wrfactortypeid," +
		"                  t.marketid," +
		"                  t.buyorsell," +
		"                  t.orderqty - t.tradeqty as orderqty," +
		"                  t.fixedprice," +
		"                  rank() over(partition by t.wrfactortypeid order by t.fixedprice asc) cnt" +
		"             from wrtrade_orderdetail t" +
		"            where t.wrtradetype = 1 and t.buyorsell = 1 and t.wrtradeorderstatus in(3,10) %v " +
		"          ) a" +
		"   where a.cnt = 1" +
		"   group by a.wrfactortypeid, a.fixedprice)," +
		"wrQuote as (" +
		"  select nvl(k1.wrfactortypeid, k2.wrfactortypeid) wrfactortypeid," +
		"         k1.buyPrice," +
		"         k1.buyQty," +
		"         k2.sellPrice," +
		"         k2.sellQty" +
		"    from buyQuote k1" +
		"    full join sellQuote k2" +
		"      on k1.wrfactortypeid = k2.wrfactortypeid" +
		")" +
		"select k.*," +
		"       w.wrfactortypename," +
		"       w.deliverygoodsid," +
		"       w.wrstandardid," +
		"       w.warehouseid," +
		"       dg.deliverygoodsname," +
		"       dg.deliverygoodscode," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       wg.wrstandardname," +
		"       wg.wrstandardcode," +
		"       wg.wrstandardname || w.wrfactortypename as wrgoodsname" +
		"  from wrQuote k" +
		"  left join WRFactorType w" +
		"    on k.wrfactortypeid = w.wrfactortypeid" +
		"  left join deliverygoods dg" +
		"    on w.deliverygoodsid = dg.deliverygoodsid" +
		"  left join wrstandard wg" +
		"    on w.wrstandardid = wg.wrstandardid" +
		"  left join warehouseinfo h" +
		"    on w.warehouseid = h.autoid" +
		"  where 1=1 %v " +
		"order by deliverygoodsname"
	var param, param2 utils.SQLVal
	param.And("t.haswr", r.HasWr)
	param.And("t.wrpricetype", r.WrPriceType)
	param.AndEx("t.marketid", r.MarketId, r.MarketId > 0)

	param2.AndLike("w.wrfactortypename", r.WRSTANDARDNAME)
	param2.AndLike("w.wrfactortypename", r.BRANDNAME)
	param2.AndEx("w.warehouseid", r.WAREHOUSEID, r.WAREHOUSEID > 0)
	sqlId.FormatParam(param.String(), param.String(), param2.String())

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

// buildSqlPriceMove 浮动价
func (r *WrOrderQuote) buildSqlPriceMove() string {
	var sqlId utils.SQLVal = "with buyQuote as" +
		" (select a.wrfactortypeid," +
		"         a.pricemove buypricemove," +
		"         sum(a.orderqty) buyQty," +
		"         a.goodsid," +
		"         a.goodscode" +
		"    from (select t.wrfactortypeid," +
		"                 t.marketid," +
		"                 t.buyorsell," +
		"                 t.orderqty - t.tradeqty as orderqty," +
		"                 t.pricemove," +
		"                 t.wrtradeorderid," +
		"                 c.goodsid," +
		"                 c.goodscode," +
		"                 rank() over(partition by t.wrfactortypeid, c.goodsid order by t.pricemove desc) cnt" +
		"            from wrtrade_orderdetail t" +
		"            left join WRTrade_GoodsConfig c" +
		"              on t.wrtradeorderid = c.wrtradeorderid" +
		"           where t.wrtradetype = 1 and t.wrtradeorderstatus in(3,10)" +
		"             and t.buyorsell = 0 %v) a" +
		"   where a.cnt = 1" +
		"   group by a.wrfactortypeid, a.pricemove, a.goodsid, a.goodscode)," +
		"sellQuote as" +
		" (select a.wrfactortypeid," +
		"         a.pricemove sellpricemove," +
		"         a.goodsid," +
		"         a.goodscode," +
		"         sum(a.orderqty) sellQty" +
		"    from (select t.wrfactortypeid," +
		"                 t.marketid," +
		"                 t.buyorsell," +
		"                 t.orderqty - t.tradeqty as orderqty," +
		"                 t.pricemove," +
		"                 c.goodsid," +
		"                 c.goodscode," +
		"                 rank() over(partition by t.wrfactortypeid order by t.pricemove asc) cnt" +
		"            from wrtrade_orderdetail t" +
		"            left join WRTrade_GoodsConfig c" +
		"              on t.wrtradeorderid = c.wrtradeorderid" +
		"           where t.wrtradetype = 1 and t.wrtradeorderstatus in(3,10)" +
		"             and t.buyorsell = 1 %v) a" +
		"   where a.cnt = 1" +
		"   group by a.wrfactortypeid, a.pricemove, a.goodsid, a.goodscode)," +
		"wrQuote as" +
		" (select nvl(k1.wrfactortypeid, k2.wrfactortypeid) wrfactortypeid," +
		"         nvl(k1.goodsid, k2.goodsid) goodsid," +
		"         nvl(k1.goodscode, k2.goodscode) goodscode," +
		"         k1.buypricemove," +
		"         k1.buyQty," +
		"         k2.sellpricemove," +
		"         k2.sellQty" +
		"    from buyQuote k1" +
		"    full join sellQuote k2" +
		"      on k1.wrfactortypeid = k2.wrfactortypeid)" +
		"select k.*," +
		"       w.wrfactortypename," +
		"       w.deliverygoodsid," +
		"       w.wrstandardid," +
		"       w.warehouseid," +
		"       dg.deliverygoodsname," +
		"       dg.deliverygoodscode," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       wg.wrstandardname," +
		"       wg.wrstandardcode," +
		"       wg.wrstandardname || w.wrfactortypename as wrgoodsname" +
		"  from wrQuote k" +
		"  left join WRFactorType w" +
		"    on k.wrfactortypeid = w.wrfactortypeid" +
		"  left join deliverygoods dg" +
		"    on w.deliverygoodsid = dg.deliverygoodsid" +
		"  left join wrstandard wg" +
		"    on w.wrstandardid = wg.wrstandardid" +
		"  left join warehouseinfo h" +
		"    on w.warehouseid = h.autoid" +
		" where 1 = 1 %v" +
		" order by deliverygoodsname"
	var param, param2 utils.SQLVal
	param.And("t.haswr", r.HasWr)
	param.And("t.wrpricetype", r.WrPriceType)
	param.AndEx("t.marketid", r.MarketId, r.MarketId > 0)

	param2.AndLike("w.wrfactortypename", r.WRSTANDARDNAME)
	param2.AndLike("w.wrfactortypename", r.BRANDNAME)
	param2.AndEx("w.warehouseid", r.WAREHOUSEID, r.WAREHOUSEID > 0)
	sqlId.FormatParam(param.String(), param.String(), param2.String())

	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *WrOrderQuote) buildSql() string {
	if r.WrPriceType == 1 {
		return r.buildSqlFixedPrice()
	} else {
		return r.buildSqlPriceMove()
	}
}

// GetDataEx 获取买卖大厅行情
func (r *WrOrderQuote) GetDataEx() (interface{}, error) {
	sData := make([]WrOrderQuote, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrOrderQuoteDetail 行情大厅明细(买卖大厅)
type WrOrderQuoteDetail struct {
	WRTRADEORDERID string  `json:"wrtradeorderid"  xorm:"WRTRADEORDERID"`        // 仓单贸易委托单ID(320+Unix秒时间戳(10位)+xxxxxx)
	BUYORSELL      int32   `json:"buyorsell"  xorm:"BUYORSELL" form:"buyorsell"` // 买卖 - 0:买 1:卖
	USERID         int64   `json:"userid"  xorm:"USERID" form:"userid"`          // 购买方(销售方)用户ID
	QTY            float64 `json:"orderqty"  xorm:"ORDERQTY"`                    // 买(卖)量(=委托量-成交量)
	PRICE          float64 `json:"fixedprice"  xorm:"FIXEDPRICE"`                // 买(卖)价
	PRICEMOVE      float64 `json:"pricemove"  xorm:"PRICEMOVE"`                  // 买(卖)升贴水- 基差
	USERNAME       string  `json:"username"  xorm:"'USERNAME'"`                  // 购买方(销售方)
	PageEx         `xorm:"extends"`

	WRFACTORTYPEID int64 `json:"-" form:"wrfactortypeid"` // 仓单要素id
	HasWr          int32 `json:"-" form:"haswr"`          // 是否有仓单 0-没有 1-有
	WrPriceType    int32 `json:"-" form:"wrpricetype"`    // 价格方式 - 1:固定价 2-浮动价 - [挂牌]
	MarketId       int32 `json:"-" form:"marketid"`       // 市场id
	GOODSID        int32 `json:"-" form:"goodsid"`        // 商品id
}

func (r *WrOrderQuoteDetail) calc() {

}

func (r *WrOrderQuoteDetail) buildSql() string {
	var sqlId utils.SQLVal = "select t.wrtradeorderid," +
		"       t.wrfactortypeid," +
		"       t.userid," +
		"       t.orderqty - t.tradeqty as qty," +
		"       t.fixedprice as price," +
		"       t.pricemove," +
		"       t.wrpricetype," +
		"       t.buyorsell," +
		"       u.accountname as username," +
		"       c.goodsid," +
		"       c.goodscode" +
		"  from WRTRADE_ORDERDETAIL t" +
		"  left join WRTRADE_GOODSCONFIG c" +
		"    on t.wrtradeorderid = c.wrtradeorderid" +
		"  left join Useraccount u" +
		"    on t.userid = u.Accountname" +
		" where 1 = 1" +
		"   and t.wrtradetype = 1" +
		"   and t.wrtradeorderstatus in (3, 10)"
	sqlId.And("t.haswr", r.HasWr)
	sqlId.And("t.wrpricetype", r.WrPriceType)
	sqlId.And("t.wrfactortypeid", r.WRFACTORTYPEID)
	sqlId.And("c.goodsid", r.GOODSID)
	sqlId.And("t.buyorsell", r.BUYORSELL)
	sqlId.AndEx("t.marketid", r.MarketId, r.MarketId > 0)

	if r.BUYORSELL == 0 {
		if r.WrPriceType == 1 {
			sqlId.Join(" order by t.fixedprice desc")
		} else {
			sqlId.Join(" order by t.pricemove desc")
		}
	} else {
		if r.WrPriceType == 1 {
			sqlId.Join(" order by t.fixedprice asc")
		} else {
			sqlId.Join(" order by t.pricemove asc")
		}
	}
	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

// GetDataEx 获取行情大厅明细(买卖大厅)
func (r *WrOrderQuoteDetail) GetDataEx() (interface{}, error) {
	sData := make([]WrOrderQuoteDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}