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()
	}
}

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

// 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()
}

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

// 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
}

// WrOrderDetail 仓单贸易委托单
type WrOrderDetail struct {
	HASWR              int32   `json:"haswr"  xorm:"HASWR" form:"haswr"`                   // 是否有仓单-卖方 - 0:无仓单 1:有仓单
	WRTRADEORDERID     string  `json:"wrtradeorderid"  xorm:"WRTRADEORDERID"`              // 仓单贸易委托单ID(320+Unix秒时间戳(10位)+xxxxxx)
	TRADEDATE          string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`       // 交易日(yyyyMMdd)
	WRTRADETYPE        int32   `json:"wrtradetype"  xorm:"WRTRADETYPE" form:"wrtradetype"` // 仓单贸易类型 - 1:挂牌 2:摘牌 3:提货卖(文化中国) 4:提货买(文化中国)
	BUYORSELL          int32   `json:"buyorsell"  xorm:"BUYORSELL" form:"buyorsell"`       // 买卖 - 0:买 1:卖(挂牌类型 买为采购 卖为销售)
	MARKETID           int64   `json:"marketid"  xorm:"MARKETID" form:"marketid"`          // 市场ID
	USERID             int64   `json:"userid"  xorm:"USERID" form:"userid"`                // 用户ID
	WRFACTORTYPEID     int64   `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID"`              // 仓单要素类型ID/商品ID(77)
	ORDERQTY           float64 `json:"orderqty"  xorm:"ORDERQTY" form:"orderqty"`          // 委托数量
	TRADEQTY           float64 `json:"tradeqty"  xorm:"TRADEQTY" form:"tradeqty"`          // 成交数量
	CANCELQTY          float64 `json:"cancelqty"  xorm:"CANCELQTY" form:"cancelqty"`       // 撤销数量
	WRPRICETYPE        int32   `json:"wrpricetype"  xorm:"WRPRICETYPE" form:"wrpricetype"` // 价格方式 - 1:固定价 2-浮动价  -   [挂牌]
	FIXEDPRICE         float64 `json:"fixedprice"  xorm:"FIXEDPRICE" form:"fixedprice"`    // 固定价格    -   [挂牌]
	PRICEMOVE          float64 `json:"pricemove"  xorm:"PRICEMOVE" form:"pricemove"`       // 升贴水(浮动价时填写)    -   [挂牌]
	TRADEPRICE         float64 `json:"tradeprice"  xorm:"TRADEPRICE" form:"tradeprice"`    // 成交价格 - [摘牌] (浮动价  (（商品1价格*商品1价格系数+升贴水）* 商品1重量系数 + 商品2价格*商品2价格系数+商品2升贴水）* 商品2重量系数 ...)* 委托单价格系数 +　委托单升贴水)
	ORDERTIME          string  `json:"ordertime"  xorm:"ORDERTIME" form:"ordertime"`       // 委托时间(挂牌时间)
	WRTRADEORDERSTATUS int32   `json:"wrtradeorderstatus"  xorm:"WRTRADEORDERSTATUS"`      // 委托状态 - 1:委托请求 2:待冻结 3:委托成功 4:委托失败 5:配对成功 6:已撤 7:已成 8:成交失败 9:委托拒绝 10:挂牌部分成交 11:仓单生成失败 12:首付扣款失败 13:履约失败 14:撤单解冻贷款失败 15:部成部撤
	GOODSID            int32   `json:"goodsid"  xorm:"'GOODSID'"`                          // 期货商品id
	GOODSCODE          string  `json:"goodscode"  xorm:"'GOODSCODE'"`                      // 期货合约
	FUTUPRICEMOVE      float64 `json:"futupricemove"  xorm:"'FUTUPRICEMOVE'"`              // 期货升贴水
	DELIVERYMONTH      string  `json:"deliverymonth"  xorm:"'DELIVERYMONTH'"`              // 交收月
	Wr2FactorType      `xorm:"extends"`
	PageEx             `xorm:"extends"`

	FUTUSHOW string `json:"futushow"` // 期货合约/升贴水
}

func (r *WrOrderDetail) calc() {
	if r.GOODSCODE == "" {
		r.FUTUSHOW = "-"
	} else {
		r.FUTUSHOW = fmt.Sprintf("%v//%.2f", r.GOODSCODE, r.FUTUPRICEMOVE)
	}
}

func (r *WrOrderDetail) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.wrfactortypeid," +
		"         t.wrfactortypename," +
		"         t.wrstandardid," +
		"         t.deliverygoodsid," +
		"         g.deliverygoodscode," +
		"         g.deliverygoodsname," +
		"         w.wrstandardcode," +
		"         w.wrstandardname," +
		"         h.warehousecode," +
		"         h.warehousename," +
		"         e.enumdicname," +
		"         w.wrstandardname || '-' || t.wrfactortypename as wrtypename" +
		"    from wrfactortype t" +
		"    left join deliverygoods g" +
		"      on t.deliverygoodsid = g.deliverygoodsid" +
		"    left join wrstandard w" +
		"      on t.wrstandardid = w.wrstandardid" +
		"    left join warehouseinfo h" +
		"      on t.warehouseid = h.autoid" +
		"    left join enumdicitem e on w.unitid = e.enumitemname and e.enumdiccode='goodsunit')" +
		"select t.HASWR," +
		"       to_char(t.WRTRADEORDERID) WRTRADEORDERID," +
		"       t.TRADEDATE," +
		"       t.DELIVERYMONTH," +
		"       t.WRTRADETYPE," +
		"       t.BUYORSELL," +
		"       t.MARKETID," +
		"       t.USERID," +
		"       t.ORDERQTY," +
		"       t.TRADEQTY," +
		"       t.CANCELQTY," +
		"       t.WRPRICETYPE," +
		"       t.FIXEDPRICE," +
		"       t.PRICEMOVE," +
		"       t.TRADEPRICE," +
		"       to_char(t.ORDERTIME,'yyyy-mm-dd hh24:mi:ss') ORDERTIME," +
		"       t.WRTRADEORDERSTATUS," +
		"       c.GOODSID," +
		"       c.GOODSCODE," +
		"       c.pricemove FUTUPRICEMOVE," +
		"       k.*" +
		"  from wrtrade_orderdetail t" +
		"  left join wrtrade_goodsconfig c" +
		"    on t.wrtradeorderid = c.wrtradeorderid" +
		"  left join tmp k" +
		"    on t.wrfactortypeid = k.wrfactortypeid" +
		"  where t.WRTRADETYPE = 1 and t.isspecified=0"
	sqlId.And("t.USERID", r.USERID)
	sqlId.And("t.haswr", r.HASWR)
	sqlId.AndEx("t.marketid", r.MARKETID, r.MARKETID > 0)
	sqlId.Join(" order by t.ORDERTIME desc")
	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

// GetDataByPage 获取仓单贸易委托单
func (r *WrOrderDetail) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]WrOrderDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

// WrTradeDetail 仓单贸易成交明细
type WrTradeDetail struct {
	HASWR           int32   `json:"haswr"  xorm:"HASWR" form:"haswr"`                   // 是否有仓单-卖方 - 0:无仓单 1:有仓单
	WRTRADEDETAILID string  `json:"wrtradedetailid"  xorm:"WRTRADEDETAILID"`            // 仓单贸易成交单ID(321+Unix秒时间戳(10位)+xxxxxx)
	TRADEDATE       string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`       // 交易日(yyyyMMdd)
	WRTRADETYPE     int32   `json:"wrtradetype"  xorm:"WRTRADETYPE" form:"wrtradetype"` // 仓单贸易类型 - 1:挂牌 2:摘牌 3:提货卖(文化中国) 4:提货买(文化中国)
	BUYORSELL       int32   `json:"buyorsell"  xorm:"BUYORSELL" form:"buyorsell"`       // 买卖 - 0:买 1:卖(挂牌类型 买为采购 卖为销售)
	MARKETID        int64   `json:"marketid"  xorm:"MARKETID" form:"marketid"`          // 市场ID
	USERID          int64   `json:"userid"  xorm:"USERID" form:"userid"`                // 用户ID
	WRFACTORTYPEID  int64   `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID"`              // 仓单要素类型ID/商品ID(77)
	TRADEQTY        float64 `json:"tradeqty"  xorm:"TRADEQTY" form:"tradeqty"`          // 成交数量
	TRADEPRICE      float64 `json:"tradeprice"  xorm:"TRADEPRICE" form:"tradeprice"`    // 成交价格
	TRADETIME       string  `json:"tradetime"  xorm:"TRADETIME" form:"tradetime"`       // 成交时间
	DELIVERYMONTH   string  `json:"deliverymonth"  xorm:"'DELIVERYMONTH'"`              // 交收月
	ACCOUNTID       int64   `json:"accountid"  xorm:"ACCOUNTID"`                        // 资金账号
	MATCHACCOUNTID  int64   `json:"matchaccountid"  xorm:"MATCHACCOUNTID"`              // 对手账号
	MATCHUSERNAME   string  `json:"matchusername"  xorm:"'MATCHUSERNAME'"`              // 对手方
	Wr2FactorType   `xorm:"extends"`
	PageEx          `xorm:"extends"`
}

func (r *WrTradeDetail) calc() {

}

func (r *WrTradeDetail) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.wrfactortypeid," +
		"         t.wrfactortypename," +
		"         t.wrstandardid," +
		"         t.deliverygoodsid," +
		"         g.deliverygoodscode," +
		"         g.deliverygoodsname," +
		"         w.wrstandardcode," +
		"         w.wrstandardname," +
		"         h.warehousecode," +
		"         h.warehousename," +
		"         e.enumdicname," +
		"         w.wrstandardname || '-' || t.wrfactortypename as wrtypename" +
		"    from wrfactortype t" +
		"    left join deliverygoods g" +
		"      on t.deliverygoodsid = g.deliverygoodsid" +
		"    left join wrstandard w" +
		"      on t.wrstandardid = w.wrstandardid" +
		"    left join warehouseinfo h" +
		"      on t.warehouseid = h.autoid" +
		"    left join enumdicitem e on w.unitid = e.enumitemname and e.enumdiccode='goodsunit')" +
		"select t.HASWR," +
		"       to_char(t.WRTRADEDETAILID) WRTRADEDETAILID," +
		"       t.TRADEDATE," +
		"       t.DELIVERYMONTH," +
		"       t.WRTRADETYPE," +
		"       t.BUYORSELL," +
		"       t.MARKETID," +
		"       t.TRADEQTY," +
		"       t.TRADEPRICE," +
		"       to_char(t.TRADETIME,'yyyy-mm-dd hh24:mi:ss') TRADETIME," +
		"       u2.accountname matchusername," +
		"       ta1.userid," +
		"       k.*" +
		"  from wrtrade_tradedetail t" +
		"  left join tmp k" +
		"    on t.wrfactortypeid = k.wrfactortypeid" +
		"  left join taaccount ta1 on t.accountid=ta1.accountid" +
		"  left join taaccount ta2 on t.matchaccountid=ta2.accountid" +
		"  left join useraccount u2 on ta2.userid=u2.userid" +
		"  where 1=1"
	sqlId.And("ta1.userid", r.USERID)
	sqlId.And("t.haswr", r.HASWR)
	sqlId.AndEx("t.marketid", r.MARKETID, r.MARKETID > 0)
	sqlId.Join(" order by t.tradetime desc")
	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

// GetDataByPage 获取仓单贸易成交明细
func (r *WrTradeDetail) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]WrTradeDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}

// WrSpecialMatchOrder 指定对手委托
type WrSpecialMatchOrder struct {
	HASWR              int32   `json:"haswr"  xorm:"HASWR" form:"haswr"`                   // 是否有仓单-卖方 - 0:无仓单 1:有仓单
	WRTRADEORDERID     string  `json:"wrtradeorderid"  xorm:"WRTRADEORDERID"`              // 仓单贸易委托单ID(320+Unix秒时间戳(10位)+xxxxxx)
	TRADEDATE          string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`       // 交易日(yyyyMMdd)
	WRTRADETYPE        int32   `json:"wrtradetype"  xorm:"WRTRADETYPE" form:"wrtradetype"` // 仓单贸易类型 - 1:挂牌 2:摘牌 3:提货卖(文化中国) 4:提货买(文化中国)
	BUYORSELL          int32   `json:"buyorsell"  xorm:"BUYORSELL" form:"buyorsell"`       // 买卖 - 0:买 1:卖(挂牌类型 买为采购 卖为销售)
	MARKETID           int64   `json:"marketid"  xorm:"MARKETID" form:"marketid"`          // 市场ID
	USERID             int64   `json:"userid"  xorm:"USERID" form:"userid"`                // 用户ID
	WRFACTORTYPEID     int64   `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID"`              // 仓单要素类型ID/商品ID(77)
	ORDERQTY           float64 `json:"orderqty"  xorm:"ORDERQTY" form:"orderqty"`          // 委托数量
	TRADEQTY           float64 `json:"tradeqty"  xorm:"TRADEQTY" form:"tradeqty"`          // 成交数量
	CANCELQTY          float64 `json:"cancelqty"  xorm:"CANCELQTY" form:"cancelqty"`       // 撤销数量
	WRPRICETYPE        int32   `json:"wrpricetype"  xorm:"WRPRICETYPE" form:"wrpricetype"` // 价格方式 - 1:固定价 2-浮动价  -   [挂牌]
	FIXEDPRICE         float64 `json:"fixedprice"  xorm:"FIXEDPRICE" form:"fixedprice"`    // 固定价格(价格)
	PRICEMOVE          float64 `json:"pricemove"  xorm:"PRICEMOVE" form:"pricemove"`       // 升贴水(浮动价时填写)    -   [挂牌]
	TRADEPRICE         float64 `json:"tradeprice"  xorm:"TRADEPRICE" form:"tradeprice"`    // 成交价格
	ORDERTIME          string  `json:"ordertime"  xorm:"ORDERTIME" form:"ordertime"`       // 委托时间
	WRTRADEORDERSTATUS int32   `json:"wrtradeorderstatus"  xorm:"WRTRADEORDERSTATUS"`      // 委托状态 - 1:委托请求 2:待冻结 3:委托成功 4:委托失败 5:配对成功 6:已撤 7:已成 8:成交失败 9:委托拒绝 10:挂牌部分成交 11:仓单生成失败 12:首付扣款失败 13:履约失败 14:撤单解冻贷款失败 15:部成部撤
	USERNAME           string  `json:"username"  xorm:"'USERNAME'"`                        // 发起方
	MATCHUSERNAME      string  `json:"matchusername"  xorm:"'MATCHUSERNAME'"`              // 指定方
	MATCHUSERID        int64   `json:"matchuserid"  xorm:"'MATCHUSERID'"`                  // 指定方资金账号(字段名为userid，实际存的是accountid by:邓工)
	Wr2FactorType      `xorm:"extends"`
	PageEx             `xorm:"extends"`

	AMOUNT float64 `json:"amount"` // 金额 = 数量 * 价格
}

func (r *WrSpecialMatchOrder) calc() {
	r.AMOUNT = r.FIXEDPRICE * r.ORDERQTY
}

func (r *WrSpecialMatchOrder) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.wrfactortypeid," +
		"         t.wrfactortypename," +
		"         t.wrstandardid," +
		"         t.deliverygoodsid," +
		"         g.deliverygoodscode," +
		"         g.deliverygoodsname," +
		"         w.wrstandardcode," +
		"         w.wrstandardname," +
		"         h.warehousecode," +
		"         h.warehousename," +
		"         e.enumdicname," +
		"         w.wrstandardname || '-' || t.wrfactortypename as wrtypename" +
		"    from wrfactortype t" +
		"    left join deliverygoods g" +
		"      on t.deliverygoodsid = g.deliverygoodsid" +
		"    left join wrstandard w" +
		"      on t.wrstandardid = w.wrstandardid" +
		"    left join warehouseinfo h" +
		"      on t.warehouseid = h.autoid" +
		"    left join enumdicitem e" +
		"      on w.unitid = e.enumitemname" +
		"     and e.enumdiccode = 'goodsunit')" +
		"select t.HASWR," +
		"       to_char(t.WRTRADEORDERID) WRTRADEORDERID," +
		"       t.WRTRADETYPE," +
		"       t.BUYORSELL," +
		"       t.MARKETID," +
		"       t.USERID," +
		"       t.ORDERQTY," +
		"       t.TRADEQTY," +
		"       t.CANCELQTY," +
		"       t.WRPRICETYPE," +
		"       t.FIXEDPRICE," +
		"       t.PRICEMOVE," +
		"       t.TRADEPRICE," +
		"       to_char(t.ORDERTIME, 'yyyy-mm-dd hh24:mi:ss') ORDERTIME," +
		"       t.WRTRADEORDERSTATUS," +
		"       m.matchuserid," +
		"       u1.accountname username," +
		"       u2.accountname matchusername," +
		"       k.*" +
		"  from wrtrade_orderdetail t" +
		"  left join WRTrade_MatchDetail m on t.wrtradeorderid=m.wrtradeorderid" +
		"  left join useraccount u1 on t.userid=u1.userid" +
		"  left join taaccount ta2 on m.matchuserid=ta2.accountid" +
		"  left join useraccount u2 on ta2.userid=u2.userid" +
		"  left join tmp k" +
		"    on t.wrfactortypeid = k.wrfactortypeid" +
		" where t.WRTRADETYPE = 1" +
		"   and t.isspecified = 1" +
		"   and t.WRTRADEORDERSTATUS = 3"
	sqlId.And("t.USERID", r.USERID)
	sqlId.And("t.haswr", r.HASWR)
	sqlId.AndEx("t.marketid", r.MARKETID, r.MARKETID > 0)
	sqlId.Join(" order by t.ORDERTIME desc")
	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

// GetDataEx 获取指定对手委托
func (r *WrSpecialMatchOrder) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]WrSpecialMatchOrder, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	total := 0
	for i := range sData {
		sData[i].calc()
		total = sData[i].Total
	}
	return sData, err, r.Page, r.PageSize, total
}