MTP20_IF/models/wrTrade2.go

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

package models

import (
	"fmt"
	"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
}

// Wr2FactorType 仓单要素类型
type Wr2FactorType struct {
	WRFACTORTYPEID   int64  `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID" form:"wrfactortypeid"`       // 仓单要素类型ID(212+Unix秒时间戳(10位)+xxxxxx)
	WRFACTORTYPENAME string `json:"wrfactortypename"  xorm:"WRFACTORTYPENAME" form:"wrfactortypename"` // 仓单要素类型名称(选择项要素的名称合并显示，逗号分隔)
	DELIVERYGOODSID  int32  `json:"deliverygoodsid"  xorm:"DELIVERYGOODSID" form:"deliverygoodsid"`    // 品种ID
	WAREHOUSEID      int64  `json:"warehouseid"  xorm:"WAREHOUSEID" form:"warehouseid"`                // 仓库ID
	WRSTANDARDID     int64  `json:"wrstandardid"  xorm:"WRSTANDARDID" form:"wrstandardid"`             // 现货商品ID
	WRSTANDARDCODE   string `json:"wrstandardcode"  xorm:"WRSTANDARDCODE" form:"wrstandardcode"`       // 现货商品代码
	OPTIONCOMPARE    string `json:"optioncompare"  xorm:"OPTIONCOMPARE" form:"optioncompare"`          // 选择项比较串【{选择项ID}＋{冒号}+选择项值 } ，逗号分隔，头尾加逗号】-- 所有选择项拼接，用于比较

	WRSTANDARDNAME    string `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`       // 商品名称
	DELIVERYGOODSCODE string `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 品种代码
	DELIVERYGOODSNAME string `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 品种名称
	ENUMDICNAME       string `json:"enumdicname"`                                   // 单位名称
	WAREHOUSENAME     string `json:"warehousename"  xorm:"'WAREHOUSENAME'"`         // 仓库名称

	WRTYPENAME string `json:"wrtypename"` // 商品
}

func (r *Wr2FactorType) Calc() {
	r.WRTYPENAME = r.WRSTANDARDNAME + "-" + r.WRFACTORTYPENAME
}

// WrPosition 仓单贸易持仓汇总
type WrPosition struct {
	WRPOSITIONID    int64   `json:"wrpositionid"  xorm:"WRPOSITIONID" form:"wrpositionid"`          // 仓单头寸ID(201+Unix秒时间戳(10位)+xxxxxx)
	WRUSERID        int64   `json:"wruserid"  xorm:"WRUSERID" form:"userid"`                        // 持有人ID
	TOTALQTY        float64 `json:"totalqty"  xorm:"TOTALQTY" form:"totalqty"`                      // 总数量(库存数量)
	INCREASEQTY     float64 `json:"-"  xorm:"INCREASEQTY" form:"increaseqty"`                       // 今日增加数量
	DECREASEQTY     float64 `json:"-"  xorm:"DECREASEQTY" form:"decreaseqty"`                       // 今日减少数量
	FREEZEQTY       float64 `json:"freezeqty"  xorm:"FREEZEQTY" form:"freezeqty"`                   // 总冻结数量
	RELATEDQTY      float64 `json:"-"  xorm:"RELATEDQTY" form:"relatedqty"`                         // 关联数量
	MODIFYTIME      string  `json:"modifytime"  xorm:"MODIFYTIME" form:"modifytime"`                // 修改时间(过户时间)
	MODIFIERID      int64   `json:"-"  xorm:"MODIFIERID" form:"modifierid"`                         // 修改人
	TRADEWRQTY      float64 `json:"-"  xorm:"TRADEWRQTY" form:"tradewrqty"`                         // 订单仓单头寸总数量
	ACCOUNTID       int64   `json:"accountid"  xorm:"ACCOUNTID" form:"accountid"`                   // 帐户ID
	CREDITTOTALQTY  float64 `json:"credittotalqty"  xorm:"CREDITTOTALQTY" form:"credittotalqty"`    // 授信总数量(预售仓单：预售总量)
	CREDITCURQTY    float64 `json:"creditcurqty"  xorm:"CREDITCURQTY" form:"creditcurqty"`          // 授信当前数量(预售仓单：预售当前量)
	CREDITFREEZEQTY float64 `json:"creditfreezeqty"  xorm:"CREDITFREEZEQTY" form:"creditfreezeqty"` // 授信冻结数量(预售仓单: 当前数量)
	CREDITTRADEQTY  float64 `json:"credittradeqty"  xorm:"CREDITTRADEQTY" form:"credittradeqty"`    // 授信成交数量(预售仓单：成交总量)
	Wr2FactorType   `xorm:"extends"`

	ENALBEQTY       float64 `json:"enalbeqty"  xorm:"'ENALBEQTY'"`             // 可用数量
	CREDITENABLEQTY float64 `json:"creditenableqty"  xorm:"'CREDITENABLEQTY'"` // 授信可用数量(预售仓单: 可用数量)
	PLEDGEQTY       float64 `json:"pledgeqty"  xorm:"'-'"`                     // 质押数量
	BUYEXCUTEQTY    float64 `json:"buyexcuteqty"`                              // 采购履约中数量
	SELLEXCUTEQTY   float64 `json:"sellexcuteqty"`                             // 销售履约中数量
	WRPOSITIONNO    string  `json:"wrpositionno"`                              // 仓单编号

	QueryType int32 `json:"querytype" form:"querytype"` // 查询类型  1-现货汇总 2-库存汇总(订单汇总) 3-现货明细
}

func (r *WrPosition) calc() {
	r.ENALBEQTY = r.TOTALQTY - r.FREEZEQTY
	r.CREDITENABLEQTY = r.CREDITCURQTY - r.CREDITFREEZEQTY
	r.WRPOSITIONNO = fmt.Sprintf("%v", r.WRFACTORTYPEID)
	r.Wr2FactorType.Calc()
}

func (r *WrPosition) buildSql() string {
	if r.QueryType == 1 {
		// 现货汇总 品种 + 品类 + 其它要素, 根据品种汇总
		return r.buildSql1()
	} else if r.QueryType == 2 {
		// 库存汇总 品种 + 仓库 + 品类 + 其它要素, 即全维度
		return r.buildSql2()
	} else {
		// 现货明细 仓单编号 + 品种 + 仓库 + 仓单要素  即全维度 跟库存汇总一样
		return r.buildSql3()
	}
}

func (r *WrPosition) buildSql1() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.*," +
		"         w.wrstandardid," +
		"         w.warehouseid," +
		"         wd.wrstandardname," +
		"         wd.wrstandardname || '-' ||w.wrfactortypename as wrtypename" +
		"    from WRPOSITION t" +
		"    left join WRFACTORTYPE w" +
		"      on t.wrfactortypeid = w.wrfactortypeid" +
		"    left join wrstandard wd" +
		"      on w.wrstandardid = wd.wrstandardid" +
		"   where t.wruserid = %v)," +
		"hld as" +
		" (select k.deliverygoodsid," +
		"         k.wrtypename," +
		"         sum(k.TOTALQTY) TOTALQTY," +
		"         sum(k.INCREASEQTY) INCREASEQTY," +
		"         sum(k.DECREASEQTY) DECREASEQTY," +
		"         sum(k.FREEZEQTY) FREEZEQTY," +
		"         sum(k.RELATEDQTY) RELATEDQTY," +
		"         sum(k.TRADEWRQTY) TRADEWRQTY," +
		"         sum(k.CREDITTOTALQTY) CREDITTOTALQTY," +
		"         sum(k.CREDITCURQTY) CREDITCURQTY," +
		"         sum(k.CREDITFREEZEQTY) CREDITFREEZEQTY," +
		"         sum(k.CREDITTRADEQTY) CREDITTRADEQTY" +
		"    from tmp k" +
		"   where 1 = 1" +
		"   group by k.deliverygoodsid, k.wrstandardname, k.wrtypename)" +
		"select h.*, g.deliverygoodsname, g.deliverygoodscode, e.enumdicname" +
		"  from hld h" +
		"  left join deliverygoods g" +
		"    on h.deliverygoodsid = g.deliverygoodsid" +
		"  left join enumdicitem e" +
		"    on e.enumitemname = g.goodsunitid" +
		"   and e.enumdiccode = 'goodsunit'"
	sqlId.FormatParam(r.WRUSERID)
	return sqlId.String()
}

func (r *WrPosition) buildSql2() string {
	return r.buildSql3()
}

func (r *WrPosition) buildSql3() string {
	var sqlId utils.SQLVal = "SELECT t.WRPOSITIONID," +
		"       t.WRUSERID," +
		"       t.TOTALQTY," +
		"       t.INCREASEQTY," +
		"       t.DECREASEQTY," +
		"       t.FREEZEQTY," +
		"       t.RELATEDQTY," +
		"       to_char(t.MODIFYTIME, 'yyyy-mm-dd hh24:mi:ss') MODIFYTIME," +
		"       t.MODIFIERID," +
		"       t.TRADEWRQTY," +
		"       t.ACCOUNTID," +
		"       t.CREDITTOTALQTY," +
		"       t.CREDITCURQTY," +
		"       t.CREDITFREEZEQTY," +
		"       t.CREDITTRADEQTY," +
		"       w.WRFACTORTYPEID," +
		"       w.WRFACTORTYPENAME," +
		"       w.DELIVERYGOODSID," +
		"       w.WAREHOUSEID," +
		"       w.WRSTANDARDID," +
		"       w.WRSTANDARDCODE," +
		"       w.OPTIONCOMPARE," +
		"       dg.deliverygoodscode," +
		"       dg.deliverygoodsname," +
		"       wd.wrstandardcode," +
		"       wd.wrstandardname," +
		"       e.enumdicname" +
		"  FROM WRPOSITION t" +
		"  LEFT JOIN WRFACTORTYPE w" +
		"    on t.wrfactortypeid = w.wrfactortypeid" +
		"  LEFT JOIN DELIVERYGOODS dg" +
		"    on t.deliverygoodsid = dg.deliverygoodsid" +
		"  LEFT JOIN WRSTANDARD wd" +
		"    on w.wrstandardid = wd.wrstandardid" +
		"  left join enumdicitem e" +
		"    on e.enumitemname = dg.goodsunitid" +
		"   and e.enumdiccode = 'goodsunit'" +
		" WHERE 1 = 1"
	sqlId.And("t.WRUSERID", r.WRUSERID)
	return sqlId.String()
}

// GetDataEx 获取仓单贸易持仓汇总
func (r *WrPosition) GetDataEx() (interface{}, error) {
	sData := make([]WrPosition, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].QueryType = r.QueryType
	}
	return sData, err
}