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"
	"strings"
)

// WrOrderQuote 买卖大厅行情
type WrOrderQuote struct {
	WRFACTORTYPEID    string           `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'"`                                 // 商品合约(浮动价列表才有)
	DELIVERYMONTH     string           `json:"deliverymonth"  xorm:"'DELIVERYMONTH'"`                         // 交收月
	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() {
	r.WRGOODSNAME = strings.ReplaceAll(r.WRGOODSNAME, ",", "-")
}

// buildSqlFixedPrice 一口价
func (r *WrOrderQuote) buildSqlFixedPrice() string {
	var sqlId utils.SQLVal = "with buyQuote as" +
		" (" +
		"  select to_char(a.wrfactortypeid) 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 to_char(a.wrfactortypeid) 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.wrfactortypename2 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)
	param2.AndEx("w.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 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 to_char(a.wrfactortypeid) wrfactortypeid," +
		"         a.pricemove buypricemove," +
		"         sum(a.orderqty) buyQty," +
		"         a.goodsid," +
		"         a.goodscode," +
		"         a.deliverymonth" +
		"    from (select t.wrfactortypeid," +
		"                 t.marketid," +
		"                 t.buyorsell," +
		"                 t.orderqty - t.tradeqty as orderqty," +
		"                 t.pricemove," +
		"                 t.wrtradeorderid," +
		"                 t.deliverymonth," +
		"                 c.goodsid," +
		"                 c.goodscode," +
		"                 rank() over(partition by t.wrfactortypeid, nvl(t.deliverymonth,'0'), 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, a.deliverymonth)," +
		"sellQuote as" +
		" (select to_char(a.wrfactortypeid) wrfactortypeid," +
		"         a.pricemove sellpricemove," +
		"         a.goodsid," +
		"         a.goodscode," +
		"         a.deliverymonth," +
		"         sum(a.orderqty) sellQty" +
		"    from (select t.wrfactortypeid," +
		"                 t.marketid," +
		"                 t.buyorsell," +
		"                 t.orderqty - t.tradeqty as orderqty," +
		"                 t.pricemove," +
		"                 t.deliverymonth," +
		"                 c.goodsid," +
		"                 c.goodscode," +
		"                 rank() over(partition by t.wrfactortypeid, nvl(t.deliverymonth,'0'), c.goodsid 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, a.deliverymonth)," +
		"wrQuote as" +
		" (select nvl(k1.wrfactortypeid, k2.wrfactortypeid) wrfactortypeid," +
		"         nvl(k1.goodsid, k2.goodsid) goodsid," +
		"         nvl(k1.goodscode, k2.goodscode) goodscode," +
		"         nvl(k1.deliverymonth, k2.deliverymonth) deliverymonth," +
		"         k1.buypricemove," +
		"         k1.buyQty," +
		"         k2.sellpricemove," +
		"         k2.sellQty" +
		"    from buyQuote k1" +
		"    full join sellQuote k2" +
		"      on k1.wrfactortypeid = k2.wrfactortypeid and k1.deliverymonth = k2.deliverymonth)" +
		"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.wrfactortypename2 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:"qty"`                         // 买(卖)量(=委托量-成交量)
	PRICE          float64 `json:"fixedprice"  xorm:"price"`                     // 买(卖)价
	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 to_char(t.wrtradeorderid) wrtradeorderid," +
		"       to_char(t.wrfactortypeid) 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.userid" +
		" 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.AndEx("c.goodsid", r.GOODSID, r.GOODSID > 0)
	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   string `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" xorm:"'enumdicname'"`              // 单位名称
	WAREHOUSENAME     string `json:"warehousename"  xorm:"'WAREHOUSENAME'"`         // 仓库名称

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

// Calc
func (r *Wr2FactorType) Calc() {
	if r.WRTYPENAME == "" {
		if len(r.WRFACTORTYPENAME) > 0 {
			r.WRTYPENAME = r.WRSTANDARDNAME + r.WRFACTORTYPENAME
		} else {
			r.WRTYPENAME = r.WRSTANDARDNAME
		}
	}
	r.WRTYPENAME = strings.ReplaceAll(r.WRTYPENAME, ",", "-")
}

// WrPosition 仓单贸易持仓汇总
type WrPosition struct {
	WRPOSITIONID    string  `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-库存汇总(订单汇总)
}

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) addPerformancePlanQty(sum WrPerformanPlanSum) {
	if r.DELIVERYGOODSID == sum.DELIVERYGOODSID &&
		r.WRSTANDARDID == sum.WRSTANDARDID &&
		r.WRTYPENAME == sum.WRTYPENAME {
		if r.QueryType == 1 || (r.QueryType == 2 && r.WAREHOUSEID == sum.WAREHOUSEID) {
			if sum.BUYORSELL == 0 {
				r.BUYEXCUTEQTY = sum.QTY
			} else {
				r.SELLEXCUTEQTY = sum.QTY
			}
		}
	}
}

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.wrfactortypename2 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," +
		"         k.wrstandardid," +
		"         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, k.wrstandardid)" +
		"select h.*, g.deliverygoodsname, g.deliverygoodscode, e.enumdicname" +
		"  from hld h" +
		"  left join deliverygoods g" +
		"    on h.deliverygoodsid = g.deliverygoodsid" +
		"  left join wrstandard wd" +
		"    on h.wrstandardid = wd.wrstandardid" +
		"  left join enumdicitem e" +
		"    on e.enumitemname = wd.unitid" +
		"   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 to_char(t.WRPOSITIONID) 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," +
		"       to_char(w.WRFACTORTYPEID) WRFACTORTYPEID," +
		"       w.WRFACTORTYPENAME2 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 = wd.unitid" +
		"   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
	}
	// 查履约中的数量
	if len(sData) > 0 {
		if r.QueryType == 1 || r.QueryType == 2 {
			m := WrPerformanPlanSum{QueryType: r.QueryType, USERID: r.WRUSERID}
			if d, err := m.GetData(); err == nil {
				for i := range sData {
					for _, sum := range d {
						sData[i].addPerformancePlanQty(sum)
					}
				}
			}
		}
	}

	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     string  `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)
	}
	r.Wr2FactorType.Calc()
}

func (r *WrOrderDetail) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select to_char(t.wrfactortypeid) wrfactortypeid," +
		"         t.wrfactortypename2 wrfactortypename," +
		"         t.wrstandardid," +
		"         t.deliverygoodsid," +
		"         g.deliverygoodscode," +
		"         g.deliverygoodsname," +
		"         w.wrstandardcode," +
		"         w.wrstandardname," +
		"         h.warehousecode," +
		"         h.warehousename," +
		"         e.enumdicname," +
		"         w.wrstandardname || t.wrfactortypename2 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() {
	r.Wr2FactorType.Calc()
}

func (r *WrTradeDetail) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select to_char(t.wrfactortypeid) wrfactortypeid," +
		"         t.wrfactortypename," +
		"         t.wrstandardid," +
		"         t.deliverygoodsid," +
		"         g.deliverygoodscode," +
		"         g.deliverygoodsname," +
		"         w.wrstandardcode," +
		"         w.wrstandardname," +
		"         h.warehousecode," +
		"         h.warehousename," +
		"         e.enumdicname," +
		"         w.wrstandardname || t.wrfactortypename2 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
	r.Wr2FactorType.Calc()
}

func (r *WrSpecialMatchOrder) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.wrfactortypeid," +
		"         t.wrfactortypename2 wrfactortypename," +
		"         t.wrstandardid," +
		"         t.deliverygoodsid," +
		"         g.deliverygoodscode," +
		"         g.deliverygoodsname," +
		"         w.wrstandardcode," +
		"         w.wrstandardname," +
		"         h.warehousecode," +
		"         h.warehousename," +
		"         e.enumdicname," +
		"         w.wrstandardname || t.wrfactortypename2 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
}

// WrFactorTypeInfo 仓单要素选择项
type WrFactorTypeInfo struct {
	WRFACTORTYPEID      string `json:"wrfactortypeid"  xorm:"'WRFACTORTYPEID'" form:"wrfactortypeid"` // 仓单要素ID
	DGFACTORYITEMTYPEID int64  `json:"dgfactoryitemtypeid"  xorm:"'DGFACTORYITEMTYPEID'"`             // 要素选择项ID (1-仓库 2-品牌)
	ITEMTYPENAME        string `json:"itemtypename"  xorm:"'ITEMTYPENAME'"`                           // 要素项名称
	DGFACTORYITEMVALUE  string `json:"dgfactoryitemvalue"  xorm:"'DGFACTORYITEMVALUE'"`               // 要素项值
	OPTIONCOMPARE       string `json:"optioncompare"  xorm:"'OPTIONCOMPARE'"`                         // 选择比较串
	WAREHOUSEID         int64  `json:"warehouseid"  xorm:"'WAREHOUSEID'"`                             // 仓库id
	DELIVERYGOODSNAME   string `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`                 // 品种名称
	DELIVERYGOODSCODE   string `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`                 // 品种代码
	WRSTANDARDCODE      string `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`                       // 品类代码
	WRSTANDARDNAME      string `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`                       // 品类名称
	WRSTANDARDID        int64  `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`                           // 品类id
	DGFACTORYITEMID     int64  `json:"dgfactoryitemid"  xorm:"'DGFACTORYITEMID'"`                     // 选择项id
	WRFACTORTYPENAME    string `json:"wrfactortypename"  xorm:"'WRFACTORTYPENAME'"`                   // 要素名称
	WRFACTORTYPENAME2   string `json:"wrfactortypename2"  xorm:"'WRFACTORTYPENAME2'"`                 // 要素名称(不含仓库)
}

func (r *WrFactorTypeInfo) calc() {

}

func (r *WrFactorTypeInfo) buildSql() string {
	var sqlId utils.SQLVal = "select to_char(t.wrfactortypeid) wrfactortypeid," +
		"       b.dgfactoryitemtypeid," +
		"       b.itemtypename," +
		"       c.dgfactoryitemvalue," +
		"       t.optioncompare," +
		"       t.warehouseid," +
		"       t.wrstandardid," +
		"       g.deliverygoodsname," +
		"       g.deliverygoodscode," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       c.dgfactoryitemid," +
		"       t.wrfactortypename," +
		"       t.wrfactortypename2" +
		"  from wrfactortype t" +
		" inner join wrfactortypeitem a" +
		"    on t.wrfactortypeid = a.wrfactortypeid" +
		" inner join dgfactoryitemtype b" +
		"    on t.deliverygoodsid = b.deliverygoodsid" +
		"   and a.dgfactoryitemtypeid = b.dgfactoryitemtypeid" +
		" left join dgfactoryitem c" +
		"    on a.dgfactoryitemid = c.dgfactoryitemid" +
		"   and b.deliverygoodsid = c.deliverygoodsid" +
		" left join deliverygoods g on t.deliverygoodsid=g.deliverygoodsid" +
		" left join wrstandard w on t.wrstandardid=w.wrstandardid" +
		" where 1 = 1"
	sqlId.And("t.wrfactortypeid", r.WRFACTORTYPEID)
	return sqlId.String()
}

// GetData 获取仓单要素选择项
func (r *WrFactorTypeInfo) GetData() ([]WrFactorTypeInfo, error) {
	sData := make([]WrFactorTypeInfo, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// GetDataEx 获取仓单要素选择项
func (r *WrFactorTypeInfo) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// WrGoodsPerformanceStep 商品履约步骤
type WrGoodsPerformanceStep struct {
	AUTOID       int64    `json:"autoid"  xorm:"AUTOID" form:"autoid"`             // AutoID
	TEMPLATEID   int64    `json:"templateid"  xorm:"TEMPLATEID" form:"templateid"` // 履约计划模板ID
	STEPINDEX    int32    `json:"stepindex"  xorm:"STEPINDEX" form:"stepindex"`    // 步骤序号
	STEPDAYS     int32    `json:"stepdays"  xorm:"STEPDAYS" form:"stepdays"`       // 距离上一步天数
	STEPTYPEID   int32    `json:"steptypeid"  xorm:"STEPTYPEID" form:"steptypeid"` // 履约步骤类型ID - 1:买方支付 2:卖方收款 3:买方自提 4:卖方发货 5:买方确认货 6:卖方发票 7:买方确认票 8:仓单转移 9:释放卖方冻结  10:货款溢短 11:生成合同[中江] 12:运费 90:确认支付 91. 确认放行 92买方支付(直接扣款)   用于-1模板“
	STEPVALUE    SFLOAT64 `json:"stepvalue"  xorm:"STEPVALUE" form:"stepvalue"`    // 步骤值
	ISAUTO       int32    `json:"isauto"  xorm:"ISAUTO" form:"isauto"`             // 是否自动 - 0:不自动 1:自动
	REMARK       string   `json:"remark"  xorm:"REMARK" form:"remark"`             // 备注
	STEPTYPENAME string   `json:"steptypename"  xorm:"'STEPTYPENAME'"`             // 步骤名称

	STEPINFO     string `json:"stepinfo"` // 步骤信息(步骤名称+步骤值)
	STEPDAYSINFO string `json:"stepdate"` // 天数信息(T+N)

	WRSTANDARDID int64 `json:"-" form:"wrstandardid"` // 商品id
	MARKETID     int64 `json:"-" form:"marketid"`     // 市场id
	HASWR        int32 `json:"-" form:"haswr"`        // 是否有仓单-卖方 - 0:无仓单 1:有仓单
}

func (r *WrGoodsPerformanceStep) calc() {
	switch r.STEPTYPEID {
	case 1, 2:
		r.STEPINFO = fmt.Sprintf("%v% v%%", r.STEPTYPENAME, r.STEPVALUE*100)
	default:
		if r.STEPVALUE == 0 {
			r.STEPINFO = r.STEPTYPENAME
		} else {
			r.STEPINFO = fmt.Sprintf("%v %v", r.STEPTYPENAME, r.STEPVALUE)
		}
	}
	r.STEPDAYSINFO = fmt.Sprintf("T+%v", r.STEPDAYS)
}

func (r *WrGoodsPerformanceStep) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select decode(%v, 1, t.performancetempid, t.performancetempid2) tempid" +
		"    from marketspotgoodsconfig t" +
		"   where rownum <= 1 %v)" +
		"select t.*, s.steptypename" +
		"  from PerformanceStepTemplate t" +
		" inner join PerformanceStepType s" +
		"    on t.steptypeid = s.steptypeid" +
		" inner join tmp" +
		"    on t.templateid = tmp.tempid" +
		" order by t.stepindex"
	var param utils.SQLVal
	param.And("t.wrstandardid", r.WRSTANDARDID)
	param.AndEx("t.marketid", r.MARKETID, r.MARKETID > 0)
	sqlId.FormatParam(r.HASWR, param.String())
	return sqlId.String()
}

// GetDataEx 获取商品履约步骤
func (r *WrGoodsPerformanceStep) GetData() ([]WrGoodsPerformanceStep, error) {
	sData := make([]WrGoodsPerformanceStep, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].MARKETID = r.MARKETID
		sData[i].WRSTANDARDID = r.WRSTANDARDID
	}
	return sData, err
}

// WrTypeItem 要素项
type WrTypeItem struct {
	NAME  string `json:"name"`  // 名称
	VALUE string `json:"value"` // 值
}

// WrGoodsInfo 仓单商品信息(仓单要素列展开+履约计划步骤)
type WrGoodsInfo struct {
	WRFACTORTYPEID string                   `json:"-" form:"wrfactortypeid"`  // 仓单要素ID
	MARKETID       int64                    `json:"marketid" form:"marketid"` // 市场id
	HASWR          int32                    `json:"haswr" form:"haswr"`       // 0:仓单预售 1:仓单贸易
	LstItem        []WrTypeItem             `json:"lstitem"`                  // 商品信息项
	LstStep        []WrGoodsPerformanceStep `json:"lststep"`                  // 履约规则
}

// GetDataEx 获取仓单商品信息(仓单要素列展开+履约计划步骤)
func (r *WrGoodsInfo) GetDataEx() (interface{}, error) {
	sData := make([]WrGoodsInfo, 0)
	mWrType := WrFactorTypeInfo{WRFACTORTYPEID: r.WRFACTORTYPEID}
	// 获取要素项
	if sType, err := mWrType.GetData(); err == nil {
		v := WrGoodsInfo{LstItem: make([]WrTypeItem, 0), LstStep: make([]WrGoodsPerformanceStep, 0)}
		if len(sType) > 0 {
			a := sType[0]
			v.LstItem = append(v.LstItem, WrTypeItem{NAME: "品种", VALUE: a.DELIVERYGOODSNAME})
			v.LstItem = append(v.LstItem, WrTypeItem{NAME: "品类", VALUE: a.WRSTANDARDNAME})

			for _, b := range sType {
				v.LstItem = append(v.LstItem, WrTypeItem{NAME: b.ITEMTYPENAME, VALUE: b.DGFACTORYITEMVALUE})
			}

			// 获取履约规则
			mStep := WrGoodsPerformanceStep{MARKETID: r.MARKETID, WRSTANDARDID: a.WRSTANDARDID, HASWR: r.HASWR}
			if sStep, err := mStep.GetData(); err == nil && len(sStep) > 0 {
				v.LstStep = append(v.LstStep, sStep...)
			}
		}

		v.MARKETID = r.MARKETID
		v.HASWR = r.HASWR
		v.WRFACTORTYPEID = r.WRFACTORTYPEID
		sData = append(sData, v)
	}
	return sData, nil
}

// WrPerformancePlan 履约信息(履约计划)
type WrPerformancePlan struct {
	PERFORMANCEPLANID        int64   `json:"performanceplanid"  xorm:"PERFORMANCEPLANID" form:"performanceplanid"` // 履约计划ID(130+yyMMddHHmmss+xxxx)
	PERFORMANCETYPE          int32   `json:"performancetype"  xorm:"PERFORMANCETYPE" form:"performancetype"`       // 履约类型 - 0:通用 1:交割 2:仓单贸易 3:预售集采 4:竞拍-降价式 (无仓单) 5:挂牌期权 6:竞拍-降价式 7:竞拍-竞价式 8:竞拍-大宗式 9:荷兰式 10:法币C2C 11:报价系统 12:挂牌系统
	RELATEDORDERID           string  `json:"relatedorderid"  xorm:"RELATEDORDERID" form:"relatedorderid"`          // 关联单号(履约类型的关联单号)
	AMOUNT                   float64 `json:"amount"  xorm:"AMOUNT" form:"amount"`                                  // 履约金额(总金额)
	PAYMENTTYPE              int32   `json:"paymenttype"  xorm:"PAYMENTTYPE" form:"paymenttype"`                   // 付款方式 - 1:冻结 2:扣款
	PERFORMANCESTATUS        int32   `json:"performancestatus"  xorm:"PERFORMANCESTATUS" form:"performancestatus"` // 履约状态 -  1:初始化 2:正常 3:处理错误 4:违约待处理 5:违约处理中 6:完成 7.违约已完成 8:释放冻结失败 9:超时待处理 10:超时关闭
	CURSTEPID                int64   `json:"curstepid"  xorm:"CURSTEPID" form:"curstepid"`                         // 当前步骤ID
	BUYACCOUNTID             int64   `json:"buyaccountid"  xorm:"BUYACCOUNTID" form:"buyaccountid"`                // 买方账号
	WRTRANSFERUSERID         int64   `json:"wrtransferuserid"  xorm:"WRTRANSFERUSERID" form:"wrtransferuserid"`    // 仓单受让用户
	BUYPAIDAMOUNT            float64 `json:"buypaidamount"  xorm:"BUYPAIDAMOUNT" form:"buypaidamount"`             // 买方已冻/已扣金额 (已付金额)
	SELLACCOUNTID            int64   `json:"sellaccountid"  xorm:"SELLACCOUNTID" form:"sellaccountid"`             // 卖方账号
	SELLRECEIVEDAMOUNT       float64 `json:"sellreceivedamount"  xorm:"SELLRECEIVEDAMOUNT"`                        // 卖方已收金额
	CREATETIME               string  `json:"createtime"  xorm:"CREATETIME" form:"createtime"`                      // 创建时间(开始时间)
	CREATORID                int64   `json:"creatorid"  xorm:"CREATORID" form:"creatorid"`                         // 创建人
	REMARK                   string  `json:"remark"  xorm:"REMARK" form:"remark"`                                  // 备注
	BUYERFREEZEAMOUNT        float64 `json:"buyerfreezeamount"  xorm:"BUYERFREEZEAMOUNT"`                          // 履约冻结(买履约)
	BUYERFREEZEAMOUNTREMAIN  float64 `json:"buyerfreezeamountremain"  xorm:"BUYERFREEZEAMOUNTREMAIN"`              // 履约冻结剩余(买履约)
	SELLERFREEZEAMOUNT       float64 `json:"sellerfreezeamount"  xorm:"SELLERFREEZEAMOUNT"`                        // 履约冻结(卖履约)
	SELLERFREEZEAMOUNTREMAIN float64 `json:"sellerfreezeamountremain"  xorm:"SELLERFREEZEAMOUNTREMAIN"`            // 履约冻结剩余(卖履约)
	OVERSHORTAMOUNT          float64 `json:"overshortamount"  xorm:"OVERSHORTAMOUNT" form:"overshortamount"`       // 溢短金额
	HASOVERSHORT             int32   `json:"hasovershort"  xorm:"HASOVERSHORT" form:"hasovershort"`                // 是否确认溢短 - 0:没有 1:买方确认 2:卖方确认
	SHIPREMARK               string  `json:"shipremark"  xorm:"SHIPREMARK" form:"shipremark"`                      // 发货备注[物流单号]
	MIDDLEUSERID             int64   `json:"middleuserid"  xorm:"MIDDLEUSERID" form:"middleuserid"`                // 中间商用户ID
	MIDDLEACCOUNTID          int64   `json:"middleaccountid"  xorm:"MIDDLEACCOUNTID" form:"middleaccountid"`       // 中间商资金账号ID
	HASWR                    int32   `json:"haswr"  xorm:"HASWR" form:"haswr"`                                     // 类型 0-仓单预售 1-仓单贸易
	OVERSHORTQTY             float64 `json:"overshortqty"  xorm:"OVERSHORTQTY" form:"overshortqty"`                // 溢短数量
	MARKETID                 int32   `json:"marketid"  xorm:"MARKETID" form:"marketid"`                            // 市场ID
	EXPRESSFEECONFIRMED      int32   `json:"expressfeeconfirmed"  xorm:"EXPRESSFEECONFIRMED"`                      // 是否确认运费 - 0:无 1:买方确认 2:卖方确认
	EXPRESSFEE               float64 `json:"expressfee"  xorm:"EXPRESSFEE" form:"expressfee"`                      // 运费
	CONTRACTID               int64   `json:"contractid"  xorm:"CONTRACTID" form:"contractid"`                      // 合同ID
	CURSTEPDEADLINE          string  `json:"curstepdeadline"  xorm:"CURSTEPDEADLINE" form:"curstepdeadline"`       // 当前步骤到期时间
	QTY                      float64 `json:"qty"  xorm:"QTY" form:"qty"`                                           // 履约数量
	Wr2FactorType            `xorm:"extends"`

	CURSTEPNAME  string `json:"curstepname"  xorm:"'CURSTEPNAME'"`   // 当前步骤名称
	BUYUSERNAME  string `json:"buyusername"  xorm:"'BUYUSERNAME'"`   // 买方名字
	SELLUSERNAME string `json:"sellusername"  xorm:"'SELLUSERNAME'"` // 卖方名字

	USERID    int64 `json:"userid" form:"userid" binding:"required"` // 用户id
	BUYORSELL int32 `json:"buyorsell" form:"buyorsell"`              // 买卖方向 0-买 1-卖
}

func (r *WrPerformancePlan) calc() {
	r.Wr2FactorType.Calc()
}

func (r *WrPerformancePlan) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select to_char(t.wrfactortypeid) wrfactortypeid," +
		"         t.wrfactortypename," +
		"         t.wrstandardid," +
		"         t.deliverygoodsid," +
		"         g.deliverygoodscode," +
		"         g.deliverygoodsname," +
		"         w.wrstandardcode," +
		"         w.wrstandardname," +
		"         h.warehousecode," +
		"         h.warehousename," +
		"         e.enumdicname," +
		"         w.wrstandardname || t.wrfactortypename2 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 to_char(t.PERFORMANCEPLANID) PERFORMANCEPLANID," +
		"       t.PERFORMANCETYPE," +
		"       to_char(t.RELATEDORDERID) RELATEDORDERID," +
		"       t.AMOUNT," +
		"       t.PAYMENTTYPE," +
		"       t.PERFORMANCESTATUS," +
		"       t.CURSTEPID," +
		"       t.BUYACCOUNTID," +
		"       t.WRTRANSFERUSERID," +
		"       t.BUYPAIDAMOUNT," +
		"       t.SELLACCOUNTID," +
		"       t.SELLRECEIVEDAMOUNT," +
		"       t.BUYTODAYAMOUNT," +
		"       t.SELLTODAYAMOUNT," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.CREATORID," +
		"       t.REMARK," +
		"       t.BUYERFREEZEAMOUNT," +
		"       t.BUYERFREEZEAMOUNTREMAIN," +
		"       t.SELLERFREEZEAMOUNT," +
		"       t.SELLERFREEZEAMOUNTREMAIN," +
		"       t.OVERSHORTAMOUNT," +
		"       t.HASOVERSHORT," +
		"       t.SHIPREMARK," +
		"       t.MIDDLEUSERID," +
		"       t.MIDDLEACCOUNTID," +
		"       t.HASWR," +
		"       t.BUYERINFO," +
		"       t.SELLERINFO," +
		"       t.OVERSHORTQTY," +
		"       t.MARKETID," +
		"       t.EXPRESSFEECONFIRMED," +
		"       t.EXPRESSFEE," +
		"       t.CONTRACTID," +
		"       to_char(t.CURSTEPDEADLINE, 'yyyy-mm-dd hh24:mi:ss') CURSTEPDEADLINE," +
		"       t.QTY," +
		"       u1.accountname buyusername," +
		"       u2.accountname sellusername," +
		"       s.steptypename CURSTEPNAME," +
		"       tmp.*" +
		"  from performanceplan t" +
		"  left join taaccount a1" +
		"    on t.buyaccountid = a1.accountid" +
		"  left join useraccount u1" +
		"    on a1.userid = u1.userid" +
		"  left join taaccount a2" +
		"    on t.sellaccountid = a2.accountid" +
		"  left join useraccount u2" +
		"    on a2.userid = u2.userid" +
		"  left join performancesteptype s" +
		"    on t.curstepid = s.steptypeid" +
		"  left join performanceplanwr p on t.performanceplanid = p.performanceplanid" +
		"  left join warehousereciept f on p.wrid= f.wrid" +
		"  left join tmp on f.wrfactortypeid=tmp.wrfactortypeid" +
		" where 1 = 1"
	if r.BUYORSELL == 0 {
		sqlId.And("u1.userid", r.USERID)
	} else {
		sqlId.And("u2.userid", r.USERID)
	}
	sqlId.Join(" order by t.CREATETIME desc")
	return sqlId.String()
}

// GetDataEx 获取履约信息(履约计划)
func (r *WrPerformancePlan) GetDataEx() (interface{}, error) {
	sData := make([]WrPerformancePlan, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrHoldLB 仓单持有记录
type WrHoldLB struct {
	LADINGBILLID    string  `json:"ladingbillid"  xorm:"LADINGBILLID" form:"ladingbillid"`          // 提单ID(208+Unix秒时间戳(10位)+xxxxxx)
	SUBNUM          int32   `json:"subnum"  xorm:"SUBNUM" form:"subnum"`                            // 提单子单号
	WRFACTORTYPEID  string  `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID" form:"wrfactortypeid"`    // 仓单要素类型ID
	DELIVERYGOODSID int32   `json:"deliverygoodsid"  xorm:"DELIVERYGOODSID" form:"deliverygoodsid"` // 品种ID
	QTY             float64 `json:"qty"  xorm:"QTY" form:"qty"`                                     // 数量
	FREEZERQTY      float64 `json:"freezerqty"  xorm:"FREEZERQTY" form:"freezerqty"`                // 冻结数量
	USERID          int64   `json:"userid"  xorm:"USERID" form:"userid"`                            // 持有人ID
	ACCOUNTID       int64   `json:"accountid"  xorm:"ACCOUNTID" form:"accountid"`                   // 持有人帐户ID
	CREATETIME      string  `json:"createtime"  xorm:"CREATETIME" form:"createtime"`                // 创建时间(过户时间)
	TRADEDATE       string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`                   // 交易日(yyyyMMdd)
	WRID            string  `json:"wrid"  xorm:"WRID" form:"wrid"`                                  // 仓单变更ID
	PARENTSUBNUM    int32   `json:"parentsubnum"  xorm:"PARENTSUBNUM" form:"parentsubnum"`          // 拆分父子单号
	PLEDGEQTY       float64 `json:"pledgeqty"  xorm:"'-'"`                                          // 质押数量
	Wr2FactorType   `xorm:"extends"`

	WRHOLDENO string  `json:"wrholdeno"` // 仓单编号
	ENABLEQTY float64 `json:"enableqty"` // 可用数量
}

func (r *WrHoldLB) calc() {
	r.WRHOLDENO = fmt.Sprintf("%v_%v", r.LADINGBILLID, r.SUBNUM)
	r.ENABLEQTY = r.QTY - r.FREEZERQTY
	r.Wr2FactorType.Calc()
}

func (r *WrHoldLB) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.wrfactortypeid," +
		"         t.wrfactortypename," +
		"         t.wrstandardid," +
		"         t.deliverygoodsid," +
		"         t.warehouseid," +
		"         g.deliverygoodscode," +
		"         g.deliverygoodsname," +
		"         w.wrstandardcode," +
		"         w.wrstandardname," +
		"         h.warehousecode," +
		"         h.warehousename," +
		"         e.enumdicname," +
		"         w.wrstandardname || t.wrfactortypename2 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 to_char(t.LADINGBILLID) LADINGBILLID," +
		"       t.SUBNUM," +
		"       to_char(t.WRFACTORTYPEID) WRFACTORTYPEID," +
		"       t.DELIVERYGOODSID," +
		"       t.QTY," +
		"       t.FREEZERQTY," +
		"       t.USERID," +
		"       t.ACCOUNTID," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.TRADEDATE," +
		"       t.WRID," +
		"       t.PARENTSUBNUM," +
		"       tmp.*" +
		"  FROM WRHOLDLB t" +
		" LEFT JOIN tmp on t.wrfactortypeid= tmp.wrfactortypeid" +
		" WHERE 1 = 1"
	sqlId.And("t.ACCOUNTID", r.ACCOUNTID)
	sqlId.AndEx("tmp.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	sqlId.AndEx("tmp.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	sqlId.AndEx("tmp.WAREHOUSEID", r.WAREHOUSEID, r.WAREHOUSEID > 0)
	return sqlId.String()
}

// GetDataEx 获取仓单持有记录
func (r *WrHoldLB) GetDataEx() (interface{}, error) {
	sData := make([]WrHoldLB, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrFilterItem 筛选框列表项
type WrFilterItem struct {
	ITEMTYPE int32  `json:"itemtype"  xorm:"'ITEMTYPE'"` // 类型 1-品种 2-品类(商品) 3-仓库
	ITEMNAME string `json:"itemname"  xorm:"'ITEMNAME'"` // 名称
	ITEMCODE string `json:"itemcode"  xorm:"'ITEMCODE'"` // 代码
	ITEMID   int64  `json:"itemid"  xorm:"'ITEMID'"`     // 项id

	IType string `json:"-" form:"itype"` // 类型 逗号隔开, 如 1,2
}

func (r *WrFilterItem) calc() {

}

func (r *WrFilterItem) buildSql() string {
	if r.IType == "" {
		r.IType = "1, 2, 3"
	}
	r.IType = strings.ReplaceAll(r.IType, " ", "")
	sType := strings.Split(r.IType, ",")
	sqls := make([]string, 0)
	for _, v := range sType {
		switch v {
		case "1":
			sqls = append(sqls, "select 1 itemtype, t.deliverygoodsid itemid, t.deliverygoodscode itemcode, to_char(t.deliverygoodsname) itemname from deliverygoods t")
		case "2":
			sqls = append(sqls, "select 2 itemtype, t.wrstandardid itemid, to_char(t.wrstandardcode) itemcode, to_char(t.wrstandardname) itemname from wrstandard t")
		case "3":
			sqls = append(sqls, "select 3 itemtype, t.autoid itemid, t.warehousecode itemcode, to_char(t.warehousename) itemname from warehouseinfo t")
		default:
		}
	}

	var sqlId string
	for _, v := range sqls {
		if len(sqlId) > 0 {
			sqlId += " union all " + v
		} else {
			sqlId = v
		}
	}
	sqlId += " order by itemtype, itemid"
	return sqlId
}

// GetDataEx 获取筛选框列表项
func (r *WrFilterItem) GetDataEx() (interface{}, error) {
	sData := make([]WrFilterItem, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrPerformanPlanSum 履约中数量
type WrPerformanPlanSum struct {
	USERID          int64   `json:"userid"  xorm:"'USERID'"`                   // 用户id
	BUYORSELL       int32   `json:"buyorsell"  xorm:"'BUYORSELL'"`             // 买卖方向
	QTY             float64 `json:"qty"  xorm:"'QTY'"`                         // 履约中的数量
	DELIVERYGOODSID int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"` // 品种id
	WRSTANDARDID    int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`       // 品类id
	WAREHOUSEID     int64   `json:"warehouseid"  xorm:"'WAREHOUSEID'"`         // 仓库id
	WRTYPENAME      string  `json:"wrtypename"  xorm:"'WRTYPENAME'"`           // 商品

	QueryType int32 `json:"querytype" form:"querytype"` // 查询类型  1-现货汇总 2-库存汇总
}

func (r *WrPerformanPlanSum) calc() {
	if len(r.WRTYPENAME) > 0 {
		r.WRTYPENAME = strings.ReplaceAll(r.WRTYPENAME, ",", "-")
	}
}

// buildSql_XHHZ 现货汇总 履约中的数量
func (r *WrPerformanPlanSum) buildSql_XHHZ() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.buyaccountid," +
		"         t.sellaccountid," +
		"         t.qty," +
		"         w.wrfactortypeid," +
		"         w.deliverygoodsid," +
		"         w.wrstandardid," +
		"         w.warehouseid," +
		"         wd.wrstandardname || w.wrfactortypename2 wrtypename," +
		"         u1.userid buyuserid," +
		"         u2.userid selluserid" +
		"    from performanceplan t" +
		"    left join performanceplanwr p" +
		"      on t.performanceplanid = p.performanceplanid" +
		"    left join warehousereciept f" +
		"      on p.wrid = f.wrid" +
		"    left join wrfactortype w" +
		"      on f.wrfactortypeid = w.wrfactortypeid" +
		"    left join wrstandard wd" +
		"      on w.wrstandardid = wd.wrstandardid" +
		"    left join taaccount ta1" +
		"      on t.buyaccountid = ta1.accountid" +
		"    left join useraccount u1" +
		"      on ta1.userid = u1.userid" +
		"    left join taaccount ta2" +
		"      on t.sellaccountid = ta2.accountid" +
		"    left join useraccount u2" +
		"      on ta2.userid = u2.userid" +
		"   where t.performancestatus in (2) and %v in(u1.userid, u2.userid))" +
		"select k.buyuserid userid," +
		"       0 as buyorsell," +
		"       k.deliverygoodsid," +
		"       k.wrstandardid," +
		"       k.wrtypename," +
		"       sum(k.qty) qty" +
		"  from tmp k" +
		" group by k.buyuserid, k.deliverygoodsid, k.wrstandardid, k.wrtypename" +
		"union all" +
		"select k.selluserid userid," +
		"       1 as buyorsell," +
		"       k.deliverygoodsid," +
		"       k.wrstandardid," +
		"       k.wrtypename," +
		"       sum(k.qty) qty" +
		"  from tmp k" +
		" group by k.selluserid, k.deliverygoodsid, k.wrstandardid, k.wrtypename"
	sqlId.FormatParam(r.USERID, r.USERID)
	return sqlId.String()
}

// buildSql_KCHZ 库存汇总 履约中的数量
func (r *WrPerformanPlanSum) buildSql_KCHZ() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.buyaccountid," +
		"         t.sellaccountid," +
		"         t.qty," +
		"         w.wrfactortypeid," +
		"         w.deliverygoodsid," +
		"         w.wrstandardid," +
		"         w.warehouseid," +
		"         wd.wrstandardname || w.wrfactortypename2 wrtypename," +
		"         u1.userid buyuserid," +
		"         u2.userid selluserid" +
		"    from performanceplan t" +
		"    left join performanceplanwr p" +
		"      on t.performanceplanid = p.performanceplanid" +
		"    left join warehousereciept f" +
		"      on p.wrid = f.wrid" +
		"    left join wrfactortype w" +
		"      on f.wrfactortypeid = w.wrfactortypeid" +
		"    left join wrstandard wd" +
		"      on w.wrstandardid = wd.wrstandardid" +
		"    left join taaccount ta1" +
		"      on t.buyaccountid = ta1.accountid" +
		"    left join useraccount u1" +
		"      on ta1.userid = u1.userid" +
		"    left join taaccount ta2" +
		"      on t.sellaccountid = ta2.accountid" +
		"    left join useraccount u2" +
		"      on ta2.userid = u2.userid" +
		"   where t.performancestatus in (2) and %v in(u1.userid, u2.userid)" +
		"select k.buyuserid userid," +
		"       0 as buyorsell," +
		"       k.deliverygoodsid," +
		"       k.wrstandardid," +
		"       k.warehouseid," +
		"       k.wrtypename," +
		"       sum(k.qty) qty" +
		"  from tmp k" +
		" group by k.buyuserid, k.deliverygoodsid, k.wrstandardid, k.warehouseid, k.wrtypename" +
		"union all" +
		"select k.selluserid userid," +
		"       1 as buyorsell," +
		"       k.deliverygoodsid," +
		"       k.wrstandardid," +
		"       k.warehouseid," +
		"       k.wrtypename," +
		"       sum(k.qty) qty" +
		"  from tmp k" +
		" group by k.selluserid, k.deliverygoodsid, k.wrstandardid, k.warehouseid, k.wrtypename"
	sqlId.FormatParam(r.USERID, r.USERID)
	return sqlId.String()
}

func (r *WrPerformanPlanSum) buildSql() string {
	if r.QueryType == 1 {
		return r.buildSql_XHHZ()
	} else {
		return r.buildSql_KCHZ()
	}
}

// GetData 获取履约中数量
func (r *WrPerformanPlanSum) GetData() ([]WrPerformanPlanSum, error) {
	sData := make([]WrPerformanPlanSum, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrFAProductDetail 融资产品列表
type WrFAProductDetail struct {
	PRODUCTDETAILID     int64   `json:"productdetailid"  xorm:"PRODUCTDETAILID"`         // 产品组合ID(SEQ_FINANCEAREAPRODUCTDETAIL)
	PRODUCTID           int64   `json:"productid"  xorm:"PRODUCTID"`                     // 金融产品ID
	AREAUSERID          int64   `json:"areauserid"  xorm:"AREAUSERID"`                   // 金融机构ID
	FINANCINGDAYS       int32   `json:"financingdays"  xorm:"FINANCINGDAYS"`             // 天数(天)
	MARGINRATIO         float64 `json:"marginratio"  xorm:"MARGINRATIO"`                 // 保证金比率质押率 - [仓单质押]
	INTERESTRATEMODE    int32   `json:"interestratemode"  xorm:"INTERESTRATEMODE"`       // 利率方式 - 1:年利率 2:日利率
	INTERESTRATE        float64 `json:"interestrate"  xorm:"INTERESTRATE"`               // 利率
	INTERESTMINLEN      int32   `json:"interestminlen"  xorm:"INTERESTMINLEN"`           // 最小起息天数
	INTERESTSETTLEMODE  int32   `json:"interestsettlemode"  xorm:"INTERESTSETTLEMODE"`   // 结息方式 - 1:按日结息  3:按月结息
	INTERESTSETTLEVALUE int32   `json:"interestsettlevalue"  xorm:"INTERESTSETTLEVALUE"` // 结息方式值
	UPDATETIME          string  `json:"updatetime"  xorm:"UPDATETIME"`                   // 更新时间
	ISAUTOLOAN          int32   `json:"isautoloan"  xorm:"'ISAUTOLOAN'"`                 // 是否自动放款 0-手动 1-自动 (当为自动放款时,显示"放款快")
	PRODUCTNAME         string  `json:"productname"  xorm:"'PRODUCTNAME'"`               // 产品名称
	USERNAME            string  `json:"username"  xorm:"'USERNAME'"`                     // 融出方
	ACCOUNTID           int64   `json:"accountid"  xorm:"'ACCOUNTID'"`                   // 资金账号

	LOANTAG string `json:"-"` // 放款标识:  "放款快" 或者为空

	WRSTANDARDID   int64  `json:"-"`                                    // 品类(商品)id
	BRANDID        int64  `json:"-"`                                    // 品牌id
	WAREHOUSEID    int64  `json:"-"`                                    // 仓库id
	WRFACTORTYPEID string `json:"wrfactortypeid" form:"wrfactortypeid"` // 仓单要素ID
}

func (r *WrFAProductDetail) calc() {
	r.LOANTAG = IFValue(r.ISAUTOLOAN == 1, "放款快", "").(string)
}

func (r *WrFAProductDetail) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select distinct f.productid, f.isautoloan" +
		"    from FinanceAreaProductLimit f" +
		"   where 1 = 1" +
		"     and f.wrstandardid = %v" +
		"     and (f.spotgoodsbrandid like '%%,%v,%%' or f.spotgoodsbrandid like '%%,0,%%')" +
		"     and (f.warehouseinfoid like '%%,%v,%%' or f.warehouseinfoid like '%%,0,%%'))" +
		"select t.PRODUCTDETAILID," +
		"       t.PRODUCTID," +
		"       t.AREAUSERID," +
		"       t.FINANCINGDAYS," +
		"       t.MARGINRATIO," +
		"       t.INTERESTRATEMODE," +
		"       t.INTERESTRATE," +
		"       t.INTERESTMINLEN," +
		"       t.INTERESTSETTLEMODE," +
		"       t.INTERESTSETTLEVALUE," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       p.productname," +
		"       p.accountid," +
		"       u.accountname username," +
		"       k.isautoloan" +
		"  from FinanceAreaProductDetail t" +
		"  left join FinanceAreaProduct p" +
		"    on t.productid = p.productid" +
		"  left join useraccount u" +
		"    on t.areauserid = u.userid" +
		" inner join tmp k" +
		"    on t.productid = k.productid"
	sqlId.FormatParam(r.WRSTANDARDID, r.BRANDID, r.WAREHOUSEID)
	return sqlId.String()
}

// GetDataEx 获取融资产品列表
func (r *WrFAProductDetail) GetDataEx() (interface{}, error) {
	sData := make([]WrFAProductDetail, 0)
	m := WrFactorTypeInfo{WRFACTORTYPEID: r.WRFACTORTYPEID}
	if d, err := m.GetData(); err == nil {
		var brandid int64 = 0
		for _, v := range d {
			if v.DGFACTORYITEMTYPEID == 2 {
				brandid = v.DGFACTORYITEMID
			}
		}
		if brandid > 0 && len(d) > 0 {
			r.BRANDID = brandid
			r.WRSTANDARDID = d[0].WRSTANDARDID
			r.WAREHOUSEID = d[0].WAREHOUSEID
			_ = db.GetEngine().SQL(r.buildSql()).Find(&sData)
			for i := range sData {
				sData[i].calc()
				sData[i].WRSTANDARDID = r.WRSTANDARDID
				sData[i].BRANDID = r.BRANDID
				sData[i].WAREHOUSEID = r.WAREHOUSEID
				sData[i].WRFACTORTYPEID = r.WRFACTORTYPEID
			}
		}
	}
	return sData, nil
}