MTP20_IF/models/wrTrade2.go

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

package models

import (
	"errors"
	"fmt"
	"mtp2_if/db"
	"mtp2_if/mtpcache"
	"mtp2_if/utils"
	"sort"
	"strconv"
	"strings"
	"time"
)

// 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'" form:"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'"`                             // 商品(商品名称+仓库要素名称 拼接)
	THUMURLS          string           `json:"thumurls"  xorm:"THUMURLS"`                                     // 缩略图片(1:1)(逗号分隔)
	PICTUREURLS       string           `json:"pictureurls"  xorm:"PICTUREURLS"`                               // 详情图片(逗号分隔)
	BANNERPICURL      string           `json:"bannerpicurl"  xorm:"BANNERPICURL"`                             // Banner图
	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'" form:"deliverymonth"`    // 交收月
	ENUMDICNAME       string           `json:"enumdicname" xorm:"'enumdicname'"`                              // 单位名称
	MINIVALUE         int32            `json:"minivalue"  xorm:"'MINIVALUE'"`                                 // 现货商品最小变动值
	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"`   // 品牌名称
	DGITEMNAME  string `json:"-" form:"dgitemname"`  // 筛选项
	BrandId     int    `json:"-" form:"brandid"`     // 品牌id
	YearsId     int    `json:"-" form:"yearsid"`     // 年份id
}

func (r *WrOrderQuote) calc() {
	r.WRGOODSNAME = strings.ReplaceAll(r.WRGOODSNAME, ",", "-")
}

func (r *WrOrderQuote) ftItemName() ([]string, bool) {
	if len(r.DGITEMNAME) < 2 {
		return nil, false
	}
	sItem := strings.Split(r.DGITEMNAME, ",")
	if len(sItem) > 0 {
		return sItem, true
	}
	return nil, false
}

// 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, a.deliverymonth" +
		"    from (select t.wrfactortypeid," +
		"                  t.marketid," +
		"                  t.buyorsell," +
		"                  t.orderqty - t.tradeqty as orderqty," +
		"                  t.fixedprice," +
		"                  nvl(t.deliverymonth,'0') deliverymonth," +
		"                  rank() over(partition by t.wrfactortypeid, nvl(t.deliverymonth,'0') order by t.fixedprice desc) cnt" +
		"             from wrtrade_orderdetail t" +
		"            where t.wrtradetype = 1 and t.isspecified=0 and t.buyorsell = 0 and t.wrtradeorderstatus in(3,7) %v" +
		"          ) a" +
		"   where a.cnt = 1" +
		"   group by a.wrfactortypeid, a.fixedprice, a.deliverymonth)," +
		"sellQuote as" +
		" (" +
		"  select to_char(a.wrfactortypeid) wrfactortypeid, a.fixedprice sellPrice, sum(a.orderqty) sellQty, a.deliverymonth" +
		"    from (select t.wrfactortypeid," +
		"                  t.marketid," +
		"                  t.buyorsell," +
		"                  t.orderqty - t.tradeqty as orderqty," +
		"                  t.fixedprice," +
		"                  nvl(t.deliverymonth,'0') deliverymonth," +
		"                  rank() over(partition by t.wrfactortypeid, nvl(t.deliverymonth,'0') order by t.fixedprice asc) cnt" +
		"             from wrtrade_orderdetail t" +
		"            where t.wrtradetype = 1 and t.isspecified=0 and t.buyorsell = 1 and t.wrtradeorderstatus in(3,7) %v " +
		"          ) a" +
		"   where a.cnt = 1" +
		"   group by a.wrfactortypeid, a.fixedprice, a.deliverymonth)," +
		"wrQuote as (" +
		"  select nvl(k1.wrfactortypeid, k2.wrfactortypeid) wrfactortypeid," +
		"         nvl(k1.deliverymonth, k2.deliverymonth) deliverymonth," +
		"         k1.buyPrice," +
		"         k1.buyQty," +
		"         k2.sellPrice," +
		"         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.minivalue," +
		"       wg.wrstandardname || w.wrfactortypename2 as wrgoodsname," +
		"       wg.bannerpicurl," +
		"       wg.pictureurls," +
		"       wg.thumurls," +
		"       e.enumdicname" +
		"  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" +
		"  left join enumdicitem e" +
		"    on e.enumitemname = wg.unitid" +
		"   and e.enumdiccode = 'goodsunit'" +
		"  where 1=1 %v " +
		"order by deliverygoodsname"
	var param, param2 utils.SQLVal
	param.AndEx("t.haswr", r.HasWr, r.HasWr >= 0)
	param.And("t.wrpricetype", r.WrPriceType)
	param.AndEx("t.marketid", r.MarketId, r.MarketId > 0)
	param.AndEx("t.wrfactortypeid", r.WRFACTORTYPEID, len(r.WRFACTORTYPEID) > 0)
	param.AndEx("t.deliverymonth", r.DELIVERYMONTH, len(r.DELIVERYMONTH) > 1)

	if d, ok := r.ftItemName(); ok {
		param2.AndLikes2("w.wrfactortypename", "w.wrfactortypename2", d...)
	}
	//指定品类id
	if r.WRSTANDARDID > 0 {
		param2.AndEx("w.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	} else {
		//品牌过滤
		if r.BrandId > 0 {
			param2.JoinFormat(" and w.WRSTANDARDID in(select WRSTANDARDID from WRStandardFactoryItem t "+
				"where dgfactoryitemtypeid = 100 and t.dgfactoryitemid=%d)", r.BrandId)
		}
		//年份过滤
		if r.YearsId > 0 {
			param2.JoinFormat(" and w.WRSTANDARDID in(select WRSTANDARDID from WRStandardFactoryItem t "+
				"where dgfactoryitemtypeid = 101 and t.dgfactoryitemid=%d)", r.YearsId)
		}
	}
	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," +
		"                 nvl(t.deliverymonth,'0') 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.isspecified=0 and t.wrtradeorderstatus in(3, 7)" +
		"             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," +
		"                 nvl(t.deliverymonth,'0') 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.isspecified=0 and t.wrtradeorderstatus in(3, 7)" +
		"             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 nvl(k1.deliverymonth,0) = nvl(k2.deliverymonth,0))" +
		"select k.*," +
		"       w.wrfactortypename," +
		"       w.deliverygoodsid," +
		"       w.wrstandardid," +
		"       w.warehouseid," +
		"       dg.deliverygoodsname," +
		"       dg.deliverygoodscode," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       wg.wrstandardname," +
		"       wg.wrstandardcode," +
		"       wg.minivalue," +
		"       wg.wrstandardname || w.wrfactortypename2 as wrgoodsname," +
		"       wg.bannerpicurl," +
		"       wg.pictureurls," +
		"       wg.thumurls," +
		"       e.enumdicname" +
		"  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" +
		"  left join enumdicitem e" +
		"    on e.enumitemname = wg.unitid" +
		"   and e.enumdiccode = 'goodsunit'" +
		" where 1 = 1 %v" +
		" order by deliverygoodsname"
	var param, param2 utils.SQLVal
	param.AndEx("t.haswr", r.HasWr, r.HasWr >= 0)
	param.And("t.wrpricetype", r.WrPriceType)
	param.AndEx("t.marketid", r.MarketId, r.MarketId > 0)
	param.AndEx("t.wrfactortypeid", r.WRFACTORTYPEID, len(r.WRFACTORTYPEID) > 0)
	param.AndEx("t.deliverymonth", r.DELIVERYMONTH, len(r.DELIVERYMONTH) > 1)

	if d, ok := r.ftItemName(); ok {
		param2.AndLikes("w.wrfactortypename", d...)
	}
	//指定品类id
	if r.WRSTANDARDID > 0 {
		param2.AndEx("w.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	} else {
		//品牌过滤
		if r.BrandId > 0 {
			param2.JoinFormat(" and w.WRSTANDARDID in(select WRSTANDARDID from WRStandardFactoryItem t "+
				"where dgfactoryitemtypeid = 100 and t.dgfactoryitemid=%d)", r.BrandId)
		}
		//年份过滤
		if r.YearsId > 0 {
			param2.JoinFormat(" and w.WRSTANDARDID in(select WRSTANDARDID from WRStandardFactoryItem t "+
				"where dgfactoryitemtypeid = 101 and t.dgfactoryitemid=%d)", r.YearsId)
		}
	}
	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()
}

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'"`                                // 购买方(销售方)
	ENUMDICNAME    string   `json:"enumdicname" xorm:"'enumdicname'"`                           // 单位名称
	MINIVALUE      int32    `json:"minivalue"  xorm:"'MINIVALUE'"`                              // 现货商品最小变动值
	DELIVERYMONTH  string   `json:"deliverymonth"  xorm:"'DELIVERYMONTH'" form:"deliverymonth"` // 交收月
	ORDERTIME      string   `json:"ordertime"  xorm:"'ORDERTIME'"`                              // 委托时间
	DELISTMINQTY   SFLOAT64 `json:"delistminqty"  xorm:"'DELISTMINQTY'"`                        // 起摘数量
	MarketId       int32    `json:"marketid"  xorm:"'MARKETID'" form:"marketid"`                // 市场id
	PageEx         `xorm:"extends"`

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

func (r *WrOrderQuoteDetail) calc() {
	r.USERNAME = EncryptByStar2(r.USERNAME)
}

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," +
		"       t.deliverymonth," +
		"       t.delistminqty," +
		"       t.marketid," +
		"       to_char(t.ordertime, 'yyyy-mm-dd hh24:mi:ss') ordertime," +
		"       u.accountname as username," +
		"       c.goodsid," +
		"       c.goodscode," +
		"       wd.minivalue," +
		"       e.enumdicname" +
		"  from WRTRADE_ORDERDETAIL t" +
		"  left join WRTRADE_GOODSCONFIG c" +
		"    on t.wrtradeorderid = c.wrtradeorderid" +
		"  left join Useraccount u" +
		"    on t.userid = u.userid" +
		"  left join wrfactortype w on t.wrfactortypeid=w.wrfactortypeid" +
		"  left join wrstandard wd on w.wrstandardid=wd.wrstandardid" +
		"  left join enumdicitem e on wd.unitid=e.enumitemname and e.enumdiccode='goodsunit'" +
		" where 1 = 1" +
		"   and t.wrtradetype = 1" +
		"   and t.isspecified = 0" +
		"   and t.wrtradeorderstatus in (3, 7)"
	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)
	sqlId.AndEx("t.deliverymonth", r.DELIVERYMONTH, len(r.DELIVERYMONTH) > 0)

	if r.BUYORSELL == 0 {
		if r.WrPriceType == 1 {
			sqlId.Join(" order by t.fixedprice desc, t.ordertime")
		} else {
			sqlId.Join(" order by t.pricemove desc, t.ordertime")
		}
	} else {
		if r.WrPriceType == 1 {
			sqlId.Join(" order by t.fixedprice asc, t.ordertime")
		} else {
			sqlId.Join(" order by t.pricemove asc, t.ordertime")
		}
	}
	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'"`         // 仓库名称
	MINIVALUE         int32  `json:"minivalue"  xorm:"'MINIVALUE'"`                 // 现货商品最小变动值

	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-库存汇总(订单汇总)
	HASWR      int32  `json:"haswr" form:"haswr"`         // 是否有仓单-卖方 - 0:无仓单 1:有仓单
	DGITEMNAME string `json:"-" form:"dgitemname"`        // 筛选项
}

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 1=1 %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, wd.minivalue" +
		"  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'"
	var param utils.SQLVal
	param.And("t.wruserid", r.WRUSERID)
	param.AndEx("w.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	param.AndEx("w.warehouseid", r.WAREHOUSEID, r.WAREHOUSEID > 0)
	param.AndEx("w.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	if d, ok := FtItemName(r.DGITEMNAME); ok {
		param.AndLikes("w.wrfactortypename", d...)
	}
	if r.HASWR == 0 {
		param.Join(" and t.CREDITTOTALQTY > 0")
	} else {
		param.Join(" and t.TOTALQTY > 0")
	}
	sqlId.FormatParam(param.String())
	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," +
		"       wd.minivalue," +
		"       h.warehousecode," +
		"       h.warehousename," +
		"       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 warehouseinfo h on w.warehouseid=h.autoid" +
		"  left join enumdicitem e" +
		"    on e.enumitemname = wd.unitid" +
		"   and e.enumdiccode = 'goodsunit'" +
		" WHERE 1 = 1"
	sqlId.And("t.WRUSERID", r.WRUSERID)
	if r.HASWR == 0 {
		sqlId.Join(" and t.CREDITTOTALQTY > 0")
	} else {
		sqlId.Join(" and t.TOTALQTY > 0")
	}
	sqlId.AndEx("w.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	sqlId.AndEx("w.warehouseid", r.WAREHOUSEID, r.WAREHOUSEID > 0)
	sqlId.AndEx("w.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	if d, ok := FtItemName(r.DGITEMNAME); ok {
		sqlId.AndLikes("w.wrfactortypename", d...)
	}
	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
		sData[i].HASWR = r.HASWR
	}
	// 查履约中的数量
	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:通道部成部撤  16:成交失败违约(荷兰式竞拍专用)  17:冻结PD成功  18:冻结PD失败  19:冻结能量成功  20:冻结能量失败  21:预约已报价  22:过期未付  23:优惠券处理中  24:仓单生成失败  25:首付扣款失败  26:履约失败  27:撤单解冻贷款失败
	GOODSID               int32    `json:"goodsid"  xorm:"'GOODSID'"`                             // 期货商品id
	GOODSCODE             string   `json:"goodscode"  xorm:"'GOODSCODE'"`                         // 期货合约
	GOODSNAME             string   `json:"goodsname"  xorm:"'GOODSNAME'"`                         // 商品名称
	FUTUPRICEMOVE         float64  `json:"futupricemove"  xorm:"'FUTUPRICEMOVE'"`                 // 期货升贴水
	DELIVERYMONTH         string   `json:"deliverymonth"  xorm:"'DELIVERYMONTH'"`                 // 交收月
	MARGINVALUE           SFLOAT64 `json:"marginvalue"  xorm:"'MARGINVALUE'"`                     // 保证金设置值(履约保证金)
	FREEZEMARGIN          SFLOAT64 `json:"freezemargin"  xorm:"'FREEZEMARGIN'"`                   // 冻结保证金(保证金)
	FREEZECHARGE          SFLOAT64 `json:"freezecharge"  xorm:"'FREEZECHARGE'"`                   // 冻结手续费
	DELISTMINQTY          int64    `json:"delistminqty"  xorm:"'DELISTMINQTY'"`                   // 起摘数量
	CANBARGAIN            int32    `json:"canbargain"  xorm:"'CANBARGAIN'"`                       // 是否可议价 - 0:不可 1:可
	CANPART               int32    `json:"canpart"  xorm:"'CANPART'"`                             // 是否允许部份摘牌 - 0:不允许(要求整单) 1:允许(可以不整单)
	ALLFRIENDSFLAG        int32    `json:"allfriendsflag"  xorm:"'ALLFRIENDSFLAG'"`               // 是否全好友可见 - 0:否 1:是
	ISSPECIFIED           int32    `json:"isspecified"  xorm:"'ISSPECIFIED'"`                     // 是否指定对手 - 0:不指定 1:指定    -   [挂牌]
	ACCOUNTID             int64    `json:"accountid"  xorm:"'ACCOUNTID'"`                         // 交易账号(资金账号)
	MATCHUSERID           int64    `json:"-"  xorm:"'MATCHUSERID'"`                               // 对手方id- 作废 可能存在多个对方id
	MATCHUSERNAME         string   `json:"matchusername"  xorm:"'MATCHUSERNAME'"`                 // 对手方名称(当ISSPECIFIED=1时,有值,指定朋友的名称)
	VALIDTYPE             int32    `json:"validtype"  xorm:"'VALIDTYPE'"`                         // 有效类型 - 1当日有效 2本周有效 3指定日期有效 4一直有效
	VALIDTIME             string   `json:"validtime"  xorm:"'VALIDTIME'"`                         // 挂牌有效期
	TANAME                string   `json:"taname"  xorm:"'TANAME'"`                               // 交易用户名称(资金账号名称)
	PERFORMANCETEMPLATEID int64    `json:"performancetemplateid"  xorm:"'PERFORMANCETEMPLATEID'"` // 履约模板id
	LADINGBILLID          string   `json:"ladingbillid"  xorm:"LADINGBILLID" form:"ladingbillid"` // 提单ID(208+Unix秒时间戳(10位)+xxxxxx)
	SUBNUM                int32    `json:"-"  xorm:"SUBNUM" form:"subnum"`                        // 提单子单号
	BANNERPICURL          string   `json:"bannerpicurl"  xorm:"BANNERPICURL"`                     // Banner图
	THUMURLS              string   `json:"thumurls"  xorm:"thumurls"`                             // 缩略图

	Wr2FactorType `xorm:"extends"`
	PageEx        `xorm:"extends"`

	FUTUSHOW string   `json:"futushow"` // 期货合约/升贴水
	AMOUNT   SFLOAT64 `json:"amount"`   // 挂牌金额 = 委托数量 * 固定价或升贴水

	BEGINDATE string `json:"begindate" form:"begindate"` // 开始交易日(yyyymmdd)
	ENDDATE   string `json:"enddate" form:"enddate"`     // 结束交易日(yyyymmdd)
	BCancel   int32  `json:"-" form:"bcancel"`           // 是否仅查可撤单据 1:可撤  注：此参数仅对当前记录有效, 且不含47模式市场
}

func (r *WrOrderDetail) calc() {
	if r.GOODSCODE == "" {
		r.FUTUSHOW = "-"
	} else {
		r.FUTUSHOW = fmt.Sprintf("%v/%.2f", r.GOODSNAME, r.PRICEMOVE)
	}
	r.Wr2FactorType.Calc()
	if r.WRPRICETYPE == 1 {
		r.AMOUNT.Set(r.ORDERQTY * r.FIXEDPRICE)
	} else {
		r.AMOUNT.Set(r.ORDERQTY * r.PRICEMOVE)
	}

	// 对手名字脱敏
	if len(r.MATCHUSERNAME) > 0 {
		var newStr string
		str := strings.Split(r.MATCHUSERNAME, ",")
		for i := range str {
			sItem := strings.Split(str[i], " ")
			if len(sItem) == 2 {
				sItem[1] = EncryptByStar(sItem[1])
				if len(newStr) > 0 {
					newStr += "、 "
				}
				newStr += sItem[0] + " " + sItem[1]
			}
		}
		r.MATCHUSERNAME = newStr
	}
	if r.SUBNUM > 0 {
		r.LADINGBILLID += fmt.Sprintf("-%v", r.SUBNUM)
	}
}

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,
         w.bannerpicurl,
         w.thumurls,
         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'),
mr as (
select q.wrtradeorderid, wm_concat(u.userid||' '||u.accountname) matchusername from wrtrade_matchdetail q 
left join useraccount u on q.matchuserid=u.userid
group by q.wrtradeorderid
)
select t.HASWR,
       t.validtype,
       to_char(t.validtime,'yyyy-mm-dd hh24:mi:ss') validtime,
       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,
       t.marginvalue,
       t.freezemargin,
       t.freezecharge,
       t.delistminqty,
       t.canbargain,
       t.canpart,
       t.allfriendsflag,
       t.isspecified,
       t.accountid,
       t.performancetemplateid,
       c.GOODSID,
       c.GOODSCODE,
       c.pricemove FUTUPRICEMOVE,
       g.goodsname,
       mr.matchusername,
       ta.accountname taname,
       to_char(b.ladingbillid) 	ladingbillid,
       b.subnum,
       k.*
  from wrtrade_orderdetail t
  left join wrtrade_goodsconfig c
    on t.wrtradeorderid = c.wrtradeorderid
  left join wrtrade_orderdetaillb b on t.wrtradeorderid=b.wrtradeorderid
  left join goods g
    on c.goodsid = g.goodsid
  left join tmp k
    on t.wrfactortypeid = k.wrfactortypeid
  left join mr
    on t.wrtradeorderid = mr.wrtradeorderid
  left join taaccount ta
    on t.accountid = ta.accountid
 where t.WRTRADEORDERSTATUS not in(2)
`
	sqlId.And("t.USERID", r.USERID)
	sqlId.And("t.haswr", r.HASWR)
	sqlId.AndEx("t.marketid", r.MARKETID, r.MARKETID > 0)
	sqlId.AndEx("t.wrtradetype", r.WRTRADETYPE, r.WRTRADETYPE > 0)

	if r.BCancel == 1 {
		// 只查委托状态=3 委托成功的单， 且不含47模式的
		sqlId.Join(" and t.WRTRADEORDERSTATUS=3 and t.marketid not in(select marketid from market where trademode = 47)")
	}

	sqlId.Join(" order by t.ORDERTIME desc")
	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

func (r *WrOrderDetail) buildSqlHis() 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'),
mr as (
select q.wrtradeorderid, wm_concat(u.userid||' '||u.accountname) matchusername from wrtrade_matchdetail q 
left join useraccount u on q.matchuserid=u.userid
group by q.wrtradeorderid
)
select t.HASWR,
       to_char(t.validtime,'yyyy-mm-dd hh24:mi:ss') validtime,
       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,
       t.marginvalue,
       t.freezemargin,
       t.delistminqty,
       t.canbargain,
       t.canpart,
       t.allfriendsflag,
       t.isspecified,
       t.accountid,
       t.performancetemplateid,
       c.GOODSID,
       c.GOODSCODE,
       c.pricemove FUTUPRICEMOVE,
       g.goodsname,
       mr.matchusername,
       ta.accountname taname,
       to_char(b.ladingbillid) 	ladingbillid,
       b.subnum,
       k.*
  from his_wrtrade_orderdetail t
  left join wrtrade_goodsconfig c
    on t.wrtradeorderid = c.wrtradeorderid
  left join wrtrade_orderdetaillb b on t.wrtradeorderid=b.wrtradeorderid
  left join goods g
    on c.goodsid = g.goodsid
  left join tmp k
    on t.wrfactortypeid = k.wrfactortypeid
  left join mr
    on t.wrtradeorderid = mr.wrtradeorderid
  left join taaccount ta
    on t.accountid = ta.accountid
 where t.WRTRADETYPE = 1 and t.WRTRADEORDERSTATUS not in(2) and t.isvaliddata=1
`
	sqlId.And("t.USERID", r.USERID)
	sqlId.And("t.haswr", r.HASWR)
	sqlId.AndEx("t.marketid", r.MARKETID, r.MARKETID > 0)
	// 格式 yyyymmdd
	if len(r.BEGINDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE >= '%v'", r.BEGINDATE)
	}

	if len(r.ENDDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE <= '%v'", r.ENDDATE)
	}
	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) {
	var sqlId string
	// 如果指定了开始日期或结束日期, 刚查历史
	if r.BEGINDATE != "" || r.ENDDATE != "" {
		sqlId = r.buildSqlHis()
	} else {
		sqlId = r.buildSql()
	}
	sData := make([]WrOrderDetail, 0)
	err := db.GetEngine().SQL(sqlId).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"`    // 成交价格
	CHARGEVALUE     float64 `json:"chargevalue"  xorm:"'CHARGEVALUE'"`                  // 手续费
	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'"`              // 对手方
	BANNERPICURL    string  `json:"bannerpicurl"  xorm:"BANNERPICURL"`                  // Banner图
	THUMURLS        string  `json:"thumurls"  xorm:"thumurls"`                          // 缩略图
	Wr2FactorType   `xorm:"extends"`
	PageEx          `xorm:"extends"`

	BEGINDATE string `json:"begindate" form:"begindate"` // 开始交易日(yyyymmdd)
	ENDDATE   string `json:"enddate" form:"enddate"`     // 结束交易日(yyyymmdd)
}

func (r *WrTradeDetail) calc() {
	r.Wr2FactorType.Calc()
	r.MATCHUSERNAME = EncryptByStar(r.MATCHUSERNAME)
}

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," +
		"         w.bannerpicurl," +
		"         w.thumurls," +
		"         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," +
		"       t.CHARGEVALUE," +
		"       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()
}

func (r *WrTradeDetail) buildSqlHis() 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," +
		"       t.CHARGEVALUE," +
		"       to_char(t.TRADETIME,'yyyy-mm-dd hh24:mi:ss') TRADETIME," +
		"       u2.accountname matchusername," +
		"       ta1.userid," +
		"       k.*" +
		"  from his_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 and t.isvaliddata=1"
	sqlId.And("ta1.userid", r.USERID)
	sqlId.And("t.haswr", r.HASWR)
	sqlId.AndEx("t.marketid", r.MARKETID, r.MARKETID > 0)
	// 格式 yyyymmdd
	if len(r.BEGINDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE >= '%v'", r.BEGINDATE)
	}

	if len(r.ENDDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE <= '%v'", r.ENDDATE)
	}
	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) {
	var sqlId string
	// 如果指定了开始日期或结束日期, 刚查历史
	if r.BEGINDATE != "" || r.ENDDATE != "" {
		sqlId = r.buildSqlHis()
	} else {
		sqlId = r.buildSql()
	}
	sData := make([]WrTradeDetail, 0)
	err := db.GetEngine().SQL(sqlId).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:邓工)
	ISSPECIFIED        int32   `json:"isspecified"  xorm:"'ISSPECIFIED'" form:"isspecified"` // 是否指定对手 - 0:不指定 1:指定好友(贸易圈) 2:指定对手    -   [挂牌]
	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.ISSPECIFIED," +
		"       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 in (1,2)" +
		"   and t.WRTRADEORDERSTATUS = 3"
	if r.ISSPECIFIED > 0 {
		sqlId.And("t.isspecified", r.ISSPECIFIED)
	} else {
		sqlId.Join(" and t.isspecified in (1,2)")
	}

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

// GetItemList 获取要素项列表
func (r WrFactorTypeInfo) GetItemList() ([]WrTypeItem, error) {
	d, err := r.GetData()
	return r.buildItemList(d), err
}

func (r *WrFactorTypeInfo) buildItemList(lst []WrFactorTypeInfo) []WrTypeItem {
	lstItem := make([]WrTypeItem, 0)
	if len(lst) > 0 {
		a := lst[0]
		lstItem = append(lstItem, WrTypeItem{NAME: "品种", VALUE: a.DELIVERYGOODSNAME})
		lstItem = append(lstItem, WrTypeItem{NAME: "商品", VALUE: a.WRSTANDARDNAME})
		for _, b := range lst {
			lstItem = append(lstItem, WrTypeItem{NAME: b.ITEMTYPENAME, VALUE: b.DGFACTORYITEMVALUE})
		}
	}
	return lstItem
}

//WrFactorTypeInfoEx 类型id
type WrFactorTypeInfoEx struct {
	WRFACTORTYPEID string `json:"wrfactortypeid"  xorm:"'WRFACTORTYPEID'" form:"wrfactortypeid"`
}

// GetDataEx 获取仓单要素选择项
func (r *WrFactorTypeInfoEx) GetDataEx() (interface{}, error) {
	m := WrFactorTypeInfo{WRFACTORTYPEID: r.WRFACTORTYPEID}
	return m.GetItemList()
}

// 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, 98:
		v := r.STEPVALUE * 100
		v.Round(8) // fix bug: 0.56 * 100 之后变为 0.560000000001, 把后面多余的1去掉
		r.STEPINFO = fmt.Sprintf("%v %v%%", r.STEPTYPENAME, v.string())
	case 92:
		r.STEPINFO = r.STEPTYPENAME
	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) buildSqlByWrstandard() 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()
}

func (r WrGoodsPerformanceStep) buildSqlByTmpId() string {
	var sqlId utils.SQLVal = "select t.*, s.steptypename" +
		"  from PerformanceStepTemplate t" +
		" inner join PerformanceStepType s" +
		"    on t.steptypeid = s.steptypeid" +
		" where t.templateid=%v" +
		" order by t.stepindex"
	sqlId.FormatParam(r.TEMPLATEID)
	return sqlId.String()
}

func (r WrGoodsPerformanceStep) buildSqlAll() string {
	var sqlId utils.SQLVal = "select t.*, s.steptypename" +
		"  from PerformanceStepTemplate t" +
		" inner join PerformanceStepType s" +
		"    on t.steptypeid = s.steptypeid" +
		" where 1=1" +
		" order by t.templateid, t.stepindex"
	return sqlId.String()
}

func (r *WrGoodsPerformanceStep) buildSql() string {
	if r.TEMPLATEID != 0 {
		return r.buildSqlByTmpId()
	} else if r.WRSTANDARDID > 0 {
		return r.buildSqlByWrstandard()
	} else {
		return r.buildSqlAll()
	}
}

// GetData 获取商品履约步骤
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"`                  // 履约规则
	TMPLATEID      int64                    `json:"-" form:"tmplateid"`       // 履约模板id
}

// 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 {
			v.LstItem = mWrType.buildItemList(sType)
			a := sType[0]

			// 获取履约规则
			mStep := WrGoodsPerformanceStep{}
			// 如果指定了履约模板id(-1是特殊的模板id:立即执行)
			if r.TMPLATEID > 0 || r.TMPLATEID == -1 {
				mStep.TEMPLATEID = r.TMPLATEID
			} else {
				mStep.MARKETID, mStep.WRSTANDARDID, mStep.HASWR = r.MARKETID, a.WRSTANDARDID, 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        string  `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                string  `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"`       // 当前步骤到期时间
	STARTTIME                string  `json:"starttime"  xorm:"'STARTTIME'"`                                        // 开始时间
	QTY                      float64 `json:"qty"  xorm:"QTY" form:"qty"`                                           // 履约数量
	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模板“
	ACTIVATEMONTH            string  `json:"activatemonth"  xorm:"'ACTIVATEMONTH'"`                                // 履约激活月 [无仓单的交易收月](yyyy-MM)
	Wr2FactorType            `xorm:"extends"`

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

	USERID       int64    `json:"userid" form:"userid" binding:"required"` // 用户id
	BUYORSELL    int32    `json:"buyorsell" form:"buyorsell"`              // 买卖方向 0-买 1-卖
	STATUS       string   `json:"-" form:"status"`                         // 状态筛选
	UNPAIDAMOUNT SFLOAT64 `json:"unpaidamount"  xorm:"'UNPAIDAMOUNT'"`     // 剩余款(待支付金额)
	BEGINDATE    string   `json:"begindate" form:"begindate"`              // 开始交易日(yyyymmdd)
	ENDDATE      string   `json:"enddate" form:"enddate"`                  // 结束交易日(yyyymmdd)
}

func (r *WrPerformancePlan) calc() {
	r.Wr2FactorType.Calc()
	r.UNPAIDAMOUNT = SFLOAT64(r.AMOUNT - r.BUYPAIDAMOUNT)
	r.SELLUSERNAME = EncryptByStar(r.SELLUSERNAME)
	r.BUYUSERNAME = EncryptByStar(r.BUYUSERNAME)
}

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,
       to_char(t.CURSTEPID) 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,
       t.activatemonth,
       u1.accountname buyusername,
       u2.accountname sellusername,
       s.steptypename CURSTEPNAME,
       s.steptypeid,
       to_char(k.starttime, 'yyyy-mm-dd hh24:mi:ss') starttime,
       tmp.*,
       case when m.trademode=17 and t.haswr=1 then '仓单贸易'
         when m.trademode=17 and t.haswr=0 then '仓单预售' else
           m.marketname end as typename
  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 performancestep k
    on t.curstepid = k.performancestepid
  left join performancesteptype s
    on k.steptypeid = s.steptypeid
  left join performanceplanwr f
    on t.performanceplanid = f.performanceplanid
  left join tmp
    on f.wrfactortypeid = tmp.wrfactortypeid
  left join market m on t.marketid=m.marketid
 where 1 = 1
`
	if r.BUYORSELL == 0 {
		sqlId.And("u1.userid", r.USERID)
	} else {
		sqlId.And("u2.userid", r.USERID)
	}
	sqlId.JoinEx(r.STATUS != "", fmt.Sprintf(" and t.PERFORMANCESTATUS in(%v)", r.STATUS))
	sqlId.Join(" order by t.CREATETIME desc")
	return sqlId.String()
}

func (r *WrPerformancePlan) buildSqlHis() 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,
       to_char(t.CURSTEPID) 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,
       t.activatemonth,
       u1.accountname buyusername,
       u2.accountname sellusername,
       s.steptypename CURSTEPNAME,
       s.steptypeid,
       to_char(k.starttime, 'yyyy-mm-dd hh24:mi:ss') starttime,
       tmp.*,
       case when m.trademode=17 and t.haswr=1 then '仓单贸易'
         when m.trademode=17 and t.haswr=0 then '仓单预售' else
           m.marketname end as typename
  from his_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 (select * from his_performancestep where isvaliddata=1) k
    on t.curstepid = k.performancestepid
  left join performancesteptype s
    on k.steptypeid = s.steptypeid
  left join performanceplanwr f
    on t.performanceplanid = f.performanceplanid
  left join tmp
    on f.wrfactortypeid = tmp.wrfactortypeid
  left join market m on t.marketid=m.marketid
 where 1 = 1 and t.isvaliddata=1
`
	if r.BUYORSELL == 0 {
		sqlId.And("u1.userid", r.USERID)
	} else {
		sqlId.And("u2.userid", r.USERID)
	}
	sqlId.JoinEx(r.STATUS != "", fmt.Sprintf(" and t.PERFORMANCESTATUS in(%v)", r.STATUS))
	// 格式 yyyymmdd
	if len(r.BEGINDATE) > 0 {
		sqlId.JoinFormat(" and t.HISTRADEDATE >= '%v'", r.BEGINDATE)
	}

	if len(r.ENDDATE) > 0 {
		sqlId.JoinFormat(" and t.HISTRADEDATE <= '%v'", r.ENDDATE)
	}
	sqlId.Join(" order by t.CREATETIME desc")
	return sqlId.String()
}

// GetDataEx 获取履约信息(履约计划)
func (r *WrPerformancePlan) GetDataEx() (interface{}, error) {
	var sqlId string
	// 如果指定了开始日期或结束日期, 刚查历史
	if r.BEGINDATE != "" || r.ENDDATE != "" {
		sqlId = r.buildSqlHis()
	} else {
		sqlId = r.buildSql()
	}
	sData := make([]WrPerformancePlan, 0)
	err := db.GetEngine().SQL(sqlId).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].BEGINDATE = r.BEGINDATE
		sData[i].ENDDATE = r.ENDDATE
	}
	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:"'-'"`                                          // 质押数量
	BANNERPICURL    string  `json:"bannerpicurl"  xorm:"BANNERPICURL"`                              // Banner图
	THUMURLS        string  `json:"thumurls"  xorm:"thumurls"`                                      // 缩略图
	Wr2FactorType   `xorm:"extends"`

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

	DGITEMNAME string `json:"-" form:"dgitemname"`
}

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," +
		"         w.bannerpicurl," +
		"         w.thumurls," +
		"         w.minivalue," +
		"         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)
	sqlId.AndInterEx("t.wrfactortypeid", r.WRFACTORTYPEID, r.WRFACTORTYPEID != "")
	if d, ok := FtItemName(r.DGITEMNAME); ok {
		sqlId.AndLikes("tmp.wrfactortypename", d...)
	}
	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)
	return sqlId.String()
}

// buildSql_KCHZ 库存汇总 履约中的数量
func (r *WrPerformanPlanSum) buildSql_KCHZ() string {
	// 跟buildSql_XHHZ()相比, group by 多了warehouseid字段
	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)
	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" form:"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:日利率 3:日固定值
	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:"loantag"`                 // 放款标识:  "放款快" 或者为空
	WRSTANDARDID   int64  `json:"-"`                       // 品类(商品)id
	BRANDID        int64  `json:"-"`                       // 品牌id
	WAREHOUSEID    int64  `json:"-"`                       // 仓库id
	WRFACTORTYPEID string `json:"-" form:"wrfactortypeid"` // 仓单要素ID
}

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

func (r *WrFAProductDetail) buildSqlByWrFactorTypeId() 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()
}

func (r *WrFAProductDetail) buildSqlByProductDetailId() string {
	var sqlId utils.SQLVal = "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" +
		"  from FinanceAreaProductDetail t" +
		"  left join FinanceAreaProduct p" +
		"    on t.productid = p.productid" +
		"  left join useraccount u" +
		"    on t.areauserid = u.userid" +
		"  where 1=1"
	sqlId.And("t.PRODUCTDETAILID", r.PRODUCTDETAILID)
	return sqlId.String()
}

func (r *WrFAProductDetail) buildSql() string {
	if r.PRODUCTDETAILID > 0 {
		return r.buildSqlByProductDetailId()
	} else {
		return r.buildSqlByWrFactorTypeId()
	}
}

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

// WrMarketTradeConfig 市场交易参数配置
type WrMarketTradeConfig struct {
	MARKETID                  int32   `json:"marketid"  xorm:"MARKETID" form:"marketid"`                                                    // 市场ID-仓单贸易、竞拍、预售
	BUYMARGINALGORITHM        int32   `json:"buymarginalgorithm"  xorm:"BUYMARGINALGORITHM" form:"buymarginalgorithm"`                      // 买方保证金方式 - 1：比率  2：固定
	BUYMARGINVALUE            float64 `json:"buymarginvalue"  xorm:"BUYMARGINVALUE" form:"buymarginvalue"`                                  // 买方保证金设置值
	SELLMARGINALGORITHM       int32   `json:"sellmarginalgorithm"  xorm:"SELLMARGINALGORITHM" form:"sellmarginalgorithm"`                   // 卖方保证金方式 - 1：比率  2：固定
	SELLMARGINVALUE           float64 `json:"sellmarginvalue"  xorm:"SELLMARGINVALUE" form:"sellmarginvalue"`                               // 卖方保证金设置值
	SELLMARGINVALUE2          float64 `json:"sellmarginvalue2"  xorm:"SELLMARGINVALUE2" form:"sellmarginvalue2"`                            // 卖方保证金设置值(无仓单)
	BUYCHARGEALGORITHM        int32   `json:"buychargealgorithm"  xorm:"BUYCHARGEALGORITHM" form:"buychargealgorithm"`                      // 买方手续费收取方式 1：比率  2：固定
	BUYCHARGEALGORITHMVALUE   float64 `json:"buychargealgorithmvalue"  xorm:"BUYCHARGEALGORITHMVALUE" form:"buychargealgorithmvalue"`       // 买方手续费设置值(交易所部分)
	BUYCHARGEALGORITHMVALUE2  float64 `json:"buychargealgorithmvalue2"  xorm:"BUYCHARGEALGORITHMVALUE2" form:"buychargealgorithmvalue2"`    // 买方手续费设置值(会员部分)
	SELLCHARGEALGORITHM       int32   `json:"sellchargealgorithm"  xorm:"SELLCHARGEALGORITHM" form:"sellchargealgorithm"`                   // 卖方手续费收取方式  1：比率  2：固定
	SELLCHARGEALGORITHMVALUE  float64 `json:"sellchargealgorithmvalue"  xorm:"SELLCHARGEALGORITHMVALUE" form:"sellchargealgorithmvalue"`    // 卖方手续费设置值(交易所部分)
	SELLCHARGEALGORITHMVALUE2 float64 `json:"sellchargealgorithmvalue2"  xorm:"SELLCHARGEALGORITHMVALUE2" form:"sellchargealgorithmvalue2"` // 卖方手续费设置值(会员部分)
	FAILEDCHARGEALGORITHM     int32   `json:"failedchargealgorithm"  xorm:"FAILEDCHARGEALGORITHM" form:"failedchargealgorithm"`             // 流拍手续费收取方式  1：比率  2：固定
	FAILEDCHARGEVALUE         float64 `json:"failedchargevalue"  xorm:"FAILEDCHARGEVALUE" form:"failedchargevalue"`                         // 流拍手续费设置值
	FAILEDCHARGEVALUE2        float64 `json:"failedchargevalue2"  xorm:"FAILEDCHARGEVALUE2" form:"failedchargevalue2"`                      // 流拍卖方手续费设置值
	BUYFIRSTMARGINALGORITHM   int32   `json:"buyfirstmarginalgorithm"  xorm:"BUYFIRSTMARGINALGORITHM" form:"buyfirstmarginalgorithm"`       // 买方出价保证金方式 - 1：比率  2：固定 - [竞拍]
	BUYFIRSTMARGINVALUE       float64 `json:"buyfirstmarginvalue"  xorm:"BUYFIRSTMARGINVALUE" form:"buyfirstmarginvalue"`                   // 买方出价保证金设置值 - [竞拍]
	PERFORMANCEWAY            int32   `json:"performanceway"  xorm:"PERFORMANCEWAY" form:"performanceway"`                                  // 交货模式 - 1:履约计划 2:分步履约 3:分步提货[文昌]
	PERFORMANCETEMPID         int64   `json:"performancetempid"  xorm:"PERFORMANCETEMPID" form:"performancetempid"`                         // 履约计划模板ID(有仓单) - 根据交货模式:1:履约 - 履约计划模板2:分步履约 - 履约计划模板3:分步提货 - 履约方式： 立即执行(-1)
	PERFORMANCETEMPID2        int64   `json:"performancetempid2"  xorm:"PERFORMANCETEMPID2" form:"performancetempid2"`                      // 履约计划模板ID(无仓单) - 根据交货模式:1:履约 - 履约计划模板2:分步履约 - 履约计划模板3:分步提货 - 履约方式： 立即执行
	BUYRELEASERATIO           float64 `json:"buyreleaseratio"  xorm:"BUYRELEASERATIO" form:"buyreleaseratio"`                               // 买方释放比率[2:分步履约]
	SELLRELEASERATIO          float64 `json:"sellreleaseratio"  xorm:"SELLRELEASERATIO" form:"sellreleaseratio"`                            // 卖方释放比率[2:分步履约]
	EXECUTEMODE               int32   `json:"executemode"  xorm:"EXECUTEMODE" form:"executemode"`                                           // 执行方式[2:分步履约] - 1:顺序执行 2:交叉执行
	EXECUTESIDE               int32   `json:"executeside"  xorm:"EXECUTESIDE" form:"executeside"`                                           // 执行方[2:分步履约] - 1:买方 2:卖方
	MINQTY                    float64 `json:"minqty"  xorm:"MINQTY" form:"minqty"`                                                          // 最小执行量[2:分步履约]
	HOLDDAYS                  int32   `json:"holddays"  xorm:"HOLDDAYS" form:"holddays"`                                                    // 合同期限[2:分步履约]
	MODIFIERID                int64   `json:"modifierid"  xorm:"MODIFIERID" form:"modifierid"`                                              // 修改人
	UPDATETIME                string  `json:"updatetime"  xorm:"UPDATETIME" form:"updatetime"`                                              // 更新时间
	BUYCHARGEALGORITHM2       int32   `json:"buychargealgorithm2"  xorm:"BUYCHARGEALGORITHM2" form:"buychargealgorithm2"`                   // 回购买方手续费收取方式 1：比率  2：固定 --77:现货商城
	BUYCHARGEALGORITHMVALUE3  float64 `json:"buychargealgorithmvalue3"  xorm:"BUYCHARGEALGORITHMVALUE3" form:"buychargealgorithmvalue3"`    // 回购买方手续费设置值(交易所部分) --77:现货商城
	BUYCHARGEALGORITHMVALUE4  float64 `json:"buychargealgorithmvalue4"  xorm:"BUYCHARGEALGORITHMVALUE4" form:"buychargealgorithmvalue4"`    // 回购买方手续费设置值(会员部分) --77:现货商城
	SELLCHARGEALGORITHM2      int32   `json:"sellchargealgorithm2"  xorm:"SELLCHARGEALGORITHM2" form:"sellchargealgorithm2"`                // 回购卖方手续费收取方式  1：比率  2：固定 --77:现货商城
	SELLCHARGEALGORITHMVALUE3 float64 `json:"sellchargealgorithmvalue3"  xorm:"SELLCHARGEALGORITHMVALUE3" form:"sellchargealgorithmvalue3"` // 回购卖方手续费设置值(交易所部分) --77:现货商城
	SELLCHARGEALGORITHMVALUE4 float64 `json:"sellchargealgorithmvalue4"  xorm:"SELLCHARGEALGORITHMVALUE4" form:"sellchargealgorithmvalue4"` // 回购卖方手续费设置值(会员部分) --77:现货商城
	BASISUPRATIO              float64 `json:"basisupratio"  xorm:"'BASISUPRATIO'"`                                                          // 浮动价基差上限比例(仓单贸易)
	BASISDOWNRATIO            float64 `json:"basisdownratio"  xorm:"'BASISDOWNRATIO'"`                                                      // 浮动价基差下限比例(仓单贸易)
	PERFORMANCETEMPIDS        string  `json:"performancetempids"  xorm:"PERFORMANCETEMPIDS"`                                                // 履约计划模板IDs(有仓单)- 根据交货模式, 多个逗号分隔:1:履约 - 履约计划模板2:分步履约 - 履约计划模板3:分步提货 - 履约方式： 立即执行(-1)
	PERFORMANCETEMPID2S       string  `json:"performancetempid2s"  xorm:"PERFORMANCETEMPID2S"`                                              // 履约计划模板IDs(无仓单) - 根据交货模式, 多个逗号分隔:1:履约 - 履约计划模板2:分步履约 - 履约计划模板3:分步提货 - 履约方式： 立即执行
	NOWRQUOTAFLAG             int     `json:"nowrquotaflag"  xorm:"'NOWRQUOTAFLAG'"`                                                        // 是否校验无仓单额度(仓单贸易) - 0:否 1:是
	LISTINGMODE               int     `json:"listingmode"  xorm:"'LISTINGMODE'"`                                                            // 挂牌方式 - 1:一口价 2:浮动价 3:贸易圈 4:协议指定
}

func (r *WrMarketTradeConfig) calc() {
	r.PERFORMANCETEMPIDS = strings.Trim(r.PERFORMANCETEMPIDS, ",")
	r.PERFORMANCETEMPID2S = strings.Trim(r.PERFORMANCETEMPID2S, ",")
}

func (r *WrMarketTradeConfig) buildSql() string {
	var sqlId utils.SQLVal = "select * from markettradeconfig t where 1=1"
	sqlId.AndEx("t.MARKETID", r.MARKETID, r.MARKETID > 0)
	return sqlId.String()
}

// GetDataEx 获取市场交易参数配置
func (r *WrMarketTradeConfig) GetDataEx() (interface{}, error) {
	sData := make([]WrMarketTradeConfig, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrFtWrstandard 品类筛选列表
type WrFtWrstandard struct {
	WRSTANDARDID   int64  `json:"wrstandardid"`   // 现货商品ID
	WRSTANDARDCODE string `json:"wrstandardcode"` // 现货商品代码
	WRSTANDARDNAME string `json:"wrstandardname"` // 商品名称
	MINIVALUE      int32  `json:"minivalue"`      // 最小变动值
	EnumdicName    string `json:"enumdicname"`    // 单位名称
}

// WrFtDeliveryGoods 品种筛选列表
type WrFtDeliveryGoods struct {
	DELIVERYGOODSID   int32            `json:"deliverygoodsid"`   // 品种ID
	DELIVERYGOODSCODE string           `json:"deliverygoodscode"` // 品种代码
	DELIVERYGOODSNAME string           `json:"deliverygoodsname"` // 品种名称
	WDLST             []WrFtWrstandard `json:"wdlst"`             // 商品列表
}

// GetDataEx 获取品种商品筛选列表
func (r *WrFtDeliveryGoods) GetDataEx() (interface{}, error) {
	sData := make([]WrFtDeliveryGoods, 0)
	mg := ErmcpDeliveryGoods{}
	sMgData, err := mg.GetData()
	if err == nil {
		mWd := Ermcp3Wrstandard{}
		if d, er := mWd.GetDataEx(); er == nil {
			if sMd, ok := d.([]Ermcp3Wrstandard); ok {
				for _, v := range sMgData {
					item := WrFtDeliveryGoods{
						DELIVERYGOODSID:   v.DELIVERYGOODSID,
						DELIVERYGOODSCODE: v.DELIVERYGOODSCODE,
						DELIVERYGOODSNAME: v.DELIVERYGOODSNAME,
						WDLST:             make([]WrFtWrstandard, 0),
					}
					for _, a := range sMd {
						if a.DELIVERYGOODSID == item.DELIVERYGOODSID && a.CANTRADE == 1 {
							val := WrFtWrstandard{
								WRSTANDARDID:   a.WRSTANDARDID,
								WRSTANDARDNAME: a.WRSTANDARDNAME,
								WRSTANDARDCODE: a.WRSTANDARDCODE,
								MINIVALUE:      a.MINIVALUE,
								EnumdicName:    a.EnumdicName,
							}
							item.WDLST = append(item.WDLST, val)
						}
					}
					// 有品类的品种才要, 终端不显示无品类的品种
					if len(item.WDLST) > 0 {
						sData = append(sData, item)
					}
				}
			}
		}
	}

	return sData, err
}

// WrStandardFactoryItem 现货商品要素定义项
type WrStandardFactoryItem struct {
	WRSTANDARDID        int64  `json:"-"  xorm:"'WRSTANDARDID'" form:"wrstandardid"`    // 品类
	DGFACTORYITEMTYPEID int64  `json:"-"  xorm:"'DGFACTORYITEMTYPEID'"`                 // 要素项类型id(1-999：预留为特殊类型 1:仓库 2:品牌)
	ITEMTYPENAME        string `json:"-"  xorm:"'ITEMTYPENAME'"`                        // 要素项类型名称
	DGFACTORYITEMID     int64  `json:"dgfactoryitemid"  xorm:"'DGFACTORYITEMID'"`       // 选择项id
	DGFACTORYITEMVALUE  string `json:"dgfactoryitemvalue"  xorm:"'DGFACTORYITEMVALUE'"` // 要素项值
	WAREHOUSEID         int64  `json:"warehouseid"  xorm:"'WAREHOUSEID'"`               // 仓库id

	WMCATFLAG int `json:"-"` // 合同相同项(如有多个仓库， 逗号隔开， 仓库1, 仓库2, 仓库3 ) 1-合并
}

func (r *WrStandardFactoryItem) calc() {

}

func (r *WrStandardFactoryItem) buildSql() string {
	var sqlId utils.SQLVal = "select t.wrstandardid," +
		"       dp.dgfactoryitemtypeid," +
		"       dp.itemtypename," +
		"       di.dgfactoryitemid," +
		"       di.warehouseid," +
		"       di.dgfactoryitemvalue" +
		"  from wrstandardfactoryitem t" +
		" inner join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		" inner join dgfactoryitemtype dp" +
		"    on t.dgfactoryitemtypeid = dp.dgfactoryitemtypeid" +
		"   and dp.deliverygoodsid = w.deliverygoodsid" +
		" inner join dgfactoryitem di" +
		"    on t.dgfactoryitemid = di.dgfactoryitemid" +
		"   and di.deliverygoodsid = w.deliverygoodsid" +
		" where dp.isvalid = 1" +
		"   and di.isvalid = 1"
	sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID)
	sqlId.Join(" order by t.dgfactoryitemtypeid, di.orderindex")
	// 合并同类项名称
	if r.WMCATFLAG == 1 {
		sqlId = `
select t.wrstandardid,
       dp.dgfactoryitemtypeid,
       dp.itemtypename,
       wm_concat(di.dgfactoryitemvalue) dgfactoryitemvalue
  from wrstandardfactoryitem t
 inner join wrstandard w
    on t.wrstandardid = w.wrstandardid
 inner join dgfactoryitemtype dp
    on t.dgfactoryitemtypeid = dp.dgfactoryitemtypeid
   and dp.deliverygoodsid = w.deliverygoodsid
 inner join dgfactoryitem di
    on t.dgfactoryitemid = di.dgfactoryitemid
   and di.deliverygoodsid = w.deliverygoodsid
 where dp.isvalid = 1
   and di.isvalid = 1
   and t.wrstandardid = %v
 group by t.wrstandardid, dp.dgfactoryitemtypeid, dp.itemtypename
`
		sqlId.FormatParam(r.WRSTANDARDID)
	}
	return sqlId.String()
}

// GetDataEx 获取现货商品要素定义项
func (r *WrStandardFactoryItem) GetDataEx() (interface{}, error) {
	sData := make([]WrStandardFactoryItem, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrStandardFactoryItemEx 现货商品要素定义项列表
type WrStandardFactoryItemEx struct {
	WRSTANDARDID        int64                   `json:"wrstandardid" form:"wrstandardid"` // 品类
	DGFACTORYITEMTYPEID int64                   `json:"dgfactoryitemtypeid"`              // 要素项类型id(1-999：预留为特殊类型 1:仓库 2:品牌)
	ITEMTYPENAME        string                  `json:"itemtypename"`                     // 要素项类型名称
	ItemLst             []WrStandardFactoryItem `json:"itemlst"`                          // 要素项列表
}

// GetDataEx 获取现货商品要素定义项列表
func (r *WrStandardFactoryItemEx) GetDataEx() (interface{}, error) {
	sData := make([]WrStandardFactoryItemEx, 0)
	m := WrStandardFactoryItem{WRSTANDARDID: r.WRSTANDARDID}
	if d, err := m.GetDataEx(); err == nil {
		if lst, ok := d.([]WrStandardFactoryItem); ok {
			mapLst := map[int64]WrStandardFactoryItemEx{}
			for _, v := range lst {
				if a, ok := mapLst[v.DGFACTORYITEMTYPEID]; ok {
					a.ItemLst = append(a.ItemLst, v)
					mapLst[v.DGFACTORYITEMTYPEID] = a
				} else {
					val := WrStandardFactoryItemEx{
						WRSTANDARDID:        v.WRSTANDARDID,
						DGFACTORYITEMTYPEID: v.DGFACTORYITEMTYPEID,
						ITEMTYPENAME:        v.ITEMTYPENAME,
						ItemLst:             make([]WrStandardFactoryItem, 0),
					}
					val.ItemLst = append(val.ItemLst, v)
					mapLst[v.DGFACTORYITEMTYPEID] = val
				}
			}
			for _, ex := range mapLst {
				sData = append(sData, ex)
			}
			sort.Slice(sData, func(i, j int) bool {
				return sData[i].DGFACTORYITEMTYPEID < sData[j].DGFACTORYITEMTYPEID
			})
		}
	}
	return sData, nil
}

// WrPerformancePlanStep 履约计划详情
type WrPerformancePlanStep struct {
	PERFORMANCESTEPID string  `json:"performancestepid"  xorm:"PERFORMANCESTEPID" form:"curstepid"` // 履约步骤ID(131+Unix秒时间戳(10位)+xxxxxx)
	PLANID            string  `json:"planid"  xorm:"PLANID" form:"planid"`                          // 所属履约计划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         float64 `json:"stepvalue"  xorm:"STEPVALUE" form:"stepvalue"`                 // 步骤值
	STEPAMOUNT        float64 `json:"stepamount"  xorm:"STEPAMOUNT" form:"stepamount"`              // 步骤金额
	REALAMOUNT        float64 `json:"realamount"  xorm:"REALAMOUNT" form:"realamount"`              // 实际完成金额
	REMAINDAYS        int32   `json:"remaindays"  xorm:"REMAINDAYS" form:"remaindays"`              // 剩余天数
	DELAYDAYS         int32   `json:"delaydays"  xorm:"DELAYDAYS" form:"delaydays"`                 // 延期申请天数
	ISAUTO            int32   `json:"isauto"  xorm:"ISAUTO" form:"isauto"`                          // 是否自动 - 0:不自动 1:自动
	ISLASTRECEIVESTEP int32   `json:"islastreceivestep"  xorm:"ISLASTRECEIVESTEP"`                  // 是否最后收款步骤 - 0:不是 1:是    （暂时不用，由服务自己判断是否为最后一步付款或收款)
	STEPSTATUS        int32   `json:"stepstatus"  xorm:"STEPSTATUS" form:"stepstatus"`              // 步骤状态 -  1:待开始 2:进行中 3:已完成 4:延期(进行中) 5:失败 6:自动完成 7:超时关闭
	HANDLESTATUS      int32   `json:"handlestatus"  xorm:"HANDLESTATUS" form:"handlestatus"`        // 处理状态 - 1:开始 2:结束 3:冻结请求 4:冻结返回成功 5:冻结返回失败 6:扣款请求 7:扣款返回成功 8:扣款返回失败 9:仓单转移请求 10:仓单转移返回成功 11:仓单转移返回失败 12:加钱请求 13:加钱返回成功 14:加钱返回失败 15:解冻仓单头寸请求 16:解冻仓单头寸返回成功 17:解冻仓单头寸返回失败 18:解冻库位请求 19:解冻库位返回成功 20:解冻库位返回失败
	STEPLANCHTYPE     int32   `json:"steplanchtype"  xorm:"STEPLANCHTYPE" form:"steplanchtype"`     // 步骤启动类型 - 1:系统自动 2:手动
	EXECUTESIDE       int32   `json:"executeside"  xorm:"EXECUTESIDE" form:"executeside"`           // 步骤执行方 - 1:买方 2:卖方
	STARTTIME         string  `json:"starttime"  xorm:"STARTTIME" form:"starttime"`                 // 开始日期
	ENDTIME           string  `json:"endtime"  xorm:"ENDTIME" form:"endtime"`                       // 结束日期
	REMARK            string  `json:"remark"  xorm:"REMARK" form:"remark"`                          // 错误备注
	STEPREMARK        string  `json:"stepremark"  xorm:"STEPREMARK" form:"stepremark"`              // 步骤备注
	STEPTYPENAME      string  `json:"steptypename"  xorm:"'STEPTYPENAME'"`                          // 步骤名称
	RELATEDORDERID    string  `json:"relatedorderid"  xorm:"'RELATEDORDERID'"`                      // 关联单号
	OVERDAYS          int32   `json:"overdays"  xorm:"'OVERDAYS'"`                                  // 超期天数 = 当前时间(数据库时间) - 结束日期
	Wr2FactorType     `xorm:"extends"`
}

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

func (r *WrPerformancePlanStep) 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 to_char(t.PERFORMANCESTEPID) PERFORMANCESTEPID," +
		"       to_char(t.PLANID) PLANID," +
		"       t.STEPINDEX," +
		"       t.STEPDAYS," +
		"       t.STEPTYPEID," +
		"       t.STEPVALUE," +
		"       t.STEPAMOUNT," +
		"       t.REALAMOUNT," +
		"       t.REMAINDAYS," +
		"       t.DELAYDAYS," +
		"       t.ISAUTO," +
		"       t.ISLASTRECEIVESTEP," +
		"       t.STEPSTATUS," +
		"       t.HANDLESTATUS," +
		"       t.STEPLANCHTYPE," +
		"       t.EXECUTESIDE," +
		"       to_char(t.STARTTIME, 'yyyy-mm-dd hh24:mi:ss') STARTTIME," +
		"       to_char(t.ENDTIME, 'yyyy-mm-dd hh24:mi:ss') ENDTIME," +
		"       nvl(trunc(sysdate) - trunc(t.endtime), 0) overdays," +
		"       t.REMARK," +
		"       t.STEPREMARK," +
		"       s.steptypename," +
		"       to_char(p.relatedorderid) relatedorderid," +
		"       k.*" +
		"  FROM PERFORMANCESTEP t" +
		"  LEFT JOIN PERFORMANCESTEPTYPE s on t.steptypeid=s.steptypeid" +
		"  LEFT JOIN PERFORMANCEPLAN p on t.planid=p.performanceplanid" +
		"  LEFT JOIN PERFORMANCEPLANWR pr on p.performanceplanid=pr.performanceplanid" +
		"  LEFT JOIN tmp k on pr.wrfactortypeid=k.wrfactortypeid" +
		" WHERE 1 = 1"
	sqlId.And("t.PLANID", r.PLANID)
	sqlId.AndEx("t.PERFORMANCESTEPID", r.PERFORMANCESTEPID, r.PERFORMANCESTEPID != "")
	sqlId.Join(" order by t.stepindex")
	return sqlId.String()
}

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

// WrFinanceBuyApply 融资购买申请表
type WrFinanceBuyApply struct {
	FINANCEAPPLYID     string  `json:"financeapplyid"  xorm:"FINANCEAPPLYID" form:"financeapplyid"`             // 融资申请单ID(320+Unix秒时间戳(10位)+xxxxxx)
	TRADEDATE          string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`                            // 交易日(yyyyMMdd)
	BUYORSELL          int32   `json:"buyorsell"  xorm:"BUYORSELL" form:"buyorsell"`                            // 买卖 - 0:买 1:卖
	MARKETID           int64   `json:"marketid"  xorm:"MARKETID" form:"marketid"`                               // 市场ID
	BUYUSERID          int64   `json:"buyuserid"  xorm:"BUYUSERID" form:"userid"`                               // 买方用户ID
	BUYACCOUNTID       int64   `json:"buyaccountid"  xorm:"BUYACCOUNTID" form:"buyaccountid"`                   // 买方资金账号
	SELLWRTRADEORDERID string  `json:"sellwrtradeorderid"  xorm:"SELLWRTRADEORDERID" form:"sellwrtradeorderid"` // 卖方委托单ID
	WRFACTORTYPEID     string  `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID" form:"wrfactortypeid"`             // 仓单要素类型ID
	ORDERQTY           float64 `json:"orderqty"  xorm:"ORDERQTY" form:"orderqty"`                               // 委托数量(商品数量)
	SELLUSERID         int64   `json:"selluserid"  xorm:"SELLUSERID" form:"selluserid"`                         // 卖方用户ID
	SELLACCOUNTID      int64   `json:"sellaccountid"  xorm:"SELLACCOUNTID" form:"sellaccountid"`                // 卖方资金账号
	FINANCEACCOUNTID   int64   `json:"financeaccountid"  xorm:"FINANCEACCOUNTID" form:"financeaccountid"`       // 融资方资金帐户ID
	FINANCEAREAUSERID  string  `json:"financeareauserid"  xorm:"FINANCEAREAUSERID" form:"financeareauserid"`    // 融资方用户ID
	ISAUTOLOAN         int32   `json:"isautoloan"  xorm:"ISAUTOLOAN" form:"isautoloan"`                         // 是否自动放款 - 0:手动 1:自动
	REMARK             string  `json:"remark"  xorm:"REMARK" form:"remark"`                                     // 备注
	APPLYSTATUS        int32   `json:"applystatus"  xorm:"APPLYSTATUS" form:"applystatus"`                      // 申请状态 - 1:待审核 2.审核中 3:审核通过 4:审核拒绝 5:审核失败 6:已撤销 7:自动审核 8:融资失败
	APPLYUSERID        int64   `json:"applyuserid"  xorm:"APPLYUSERID" form:"applyuserid"`                      // 申请人
	APPLYDATE          string  `json:"applydate"  xorm:"APPLYDATE" form:"applydate"`                            // 申请时间
	AUDITUSERID        int64   `json:"audituserid"  xorm:"AUDITUSERID" form:"audituserid"`                      // 审核人
	AUDITDATE          string  `json:"auditdate"  xorm:"AUDITDATE" form:"auditdate"`                            // 审核时间
	AUDITREMARK        string  `json:"auditremark"  xorm:"AUDITREMARK" form:"auditremark"`                      // 审核备注
	HANDLESTATUS       int32   `json:"handlestatus"  xorm:"HANDLESTATUS" form:"handlestatus"`                   // 处理状态
	HANDLEREMARK       string  `json:"handleremark"  xorm:"HANDLEREMARK" form:"handleremark"`                   // 处理备注
	BUYWRTRADEORDERID  string  `json:"buywrtradeorderid"  xorm:"BUYWRTRADEORDERID" form:"buywrtradeorderid"`    // 融资方买委托单ID
	SCFCONTRACTID      string  `json:"scfcontractid"  xorm:"SCFCONTRACTID" form:"scfcontractid"`                // 仓单回购合同ID
	FREEZEBUYMARGIN    float64 `json:"freezebuymargin"  xorm:"FREEZEBUYMARGIN" form:"freezebuymargin"`          // 冻结融资保证金(买方)
	WRPRICETYPE        int32   `json:"wrpricetype"  xorm:"WRPRICETYPE" form:"wrpricetype"`                      // 价格方式 - 1:固定价 2-浮动价  -   [挂牌单]
	TRADEPRICE         float64 `json:"tradeprice"  xorm:"TRADEPRICE" form:"tradeprice"`                         // 成交价格
	TRADEAMOUNT        float64 `json:"tradeamount"  xorm:"TRADEAMOUNT" form:"tradeamount"`                      // 成交金额(借款金额)
	DELIVERYMONTH      string  `json:"deliverymonth"  xorm:"DELIVERYMONTH" form:"deliverymonth"`                // 交收月(yyyy-MM) [无仓单HasWR =0]
	BUYUSERNAME        string  `json:"buyusername"  xorm:"'BUYUSERNAME'"`                                       // 买方名称
	FINANCENAME        string  `json:"financename"  xorm:"'FINANCENAME'"`                                       // 融出方名称
	FINANCETYPE        int32   `json:"financetype"  xorm:"'FINANCETYPE'"`                                       // 融资类型  1-融资回购

	PRODUCTDETAILID     int64   `json:"productdetailid"  xorm:"PRODUCTDETAILID" form:"productdetailid"` // 产品组合ID
	PRODUCTID           int64   `json:"productid"  xorm:"PRODUCTID"`                                    // 金融产品ID
	PRODUCTNAME         string  `json:"productname"  xorm:"'PRODUCTNAME'"`                              // 产品名称
	FINANCINGDAYS       int32   `json:"financingdays"  xorm:"FINANCINGDAYS"`                            // 天数(天)
	MARGINRATIO         float64 `json:"marginratio"  xorm:"MARGINRATIO"`                                // 保证金比率质押率 - [仓单质押]
	INTERESTRATEMODE    int32   `json:"interestratemode"  xorm:"INTERESTRATEMODE"`                      // 利率方式 - 1:年利率 2:日利率 3:日固定值
	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"`                // 结息方式值

	Wr2FactorType `xorm:"extends"`
}

func (r *WrFinanceBuyApply) calc() {
	r.FINANCENAME = EncryptByStar(r.FINANCENAME)
}

func (r *WrFinanceBuyApply) 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 to_char(t.FINANCEAPPLYID) FINANCEAPPLYID," +
		"       t.TRADEDATE," +
		"       t.BUYORSELL," +
		"       t.MARKETID," +
		"       t.BUYUSERID," +
		"       t.BUYACCOUNTID," +
		"       to_char(t.SELLWRTRADEORDERID) SELLWRTRADEORDERID," +
		"       t.ORDERQTY," +
		"       t.SELLUSERID," +
		"       t.SELLACCOUNTID," +
		"       t.PRODUCTDETAILID," +
		"       t.FINANCEACCOUNTID," +
		"       t.FINANCEAREAUSERID," +
		"       t.ISAUTOLOAN," +
		"       t.REMARK," +
		"       t.APPLYSTATUS," +
		"       t.APPLYUSERID," +
		"       to_char(t.APPLYDATE, 'yyyy-mm-dd hh24:mi:ss') APPLYDATE," +
		"       t.AUDITUSERID," +
		"       to_char(t.AUDITDATE, 'yyyy-mm-dd hh24:mi:ss') AUDITDATE," +
		"       t.AUDITREMARK," +
		"       t.HANDLESTATUS," +
		"       t.HANDLEREMARK," +
		"       to_char(t.BUYWRTRADEORDERID) BUYWRTRADEORDERID," +
		"       t.SCFCONTRACTID," +
		"       t.FREEZEBUYMARGIN," +
		"       t.WRPRICETYPE," +
		"       t.TRADEPRICE," +
		"       t.TRADEAMOUNT," +
		"       t.DELIVERYMONTH," +
		"       t.FINANCETYPE," +
		"       f.PRODUCTNAME," +
		"       t.FINANCINGDAYS," +
		"       t.MARGINRATIO," +
		"       t.INTERESTRATEMODE," +
		"       t.INTERESTRATE," +
		"       t.INTERESTMINLEN," +
		"       t.INTERESTSETTLEMODE," +
		"       t.INTERESTSETTLEVALUE," +
		"       u1.accountname BUYUSERNAME," +
		"       u2.accountname FINANCENAME," +
		"       tmp.*" +
		"  FROM WRTRADE_FINANCEBUYAPPLY t" +
		"  LEFT JOIN FINANCEAREAPRODUCTDETAIL p on t.productdetailid = p.productdetailid" +
		"  LEFT JOIN FinanceAreaProduct f on p.productid = f.productid" +
		"  LEFT JOIN tmp on t.wrfactortypeid=tmp.wrfactortypeid" +
		"  LEFT JOIN USERACCOUNT u1 on t.BUYUSERID = u1.userid" +
		"  LEFT JOIN USERACCOUNT u2 on t.FINANCEAREAUSERID = u2.userid" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.BUYUSERID", r.BUYUSERID, r.BUYUSERID > 0)
	sqlId.AndEx("t.FINANCEAPPLYID", r.FINANCEAPPLYID, r.FINANCEAPPLYID != "")
	return sqlId.String()
}

// GetDataEx 获取融资购买申请表
func (r *WrFinanceBuyApply) GetDataEx() (interface{}, error) {
	sData := make([]WrFinanceBuyApply, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrScfContract 仓单贸易融资合同
type WrScfContract struct {
	DEADLINE             string  `json:"deadline"  xorm:"DEADLINE"`                                      // 截止日期(yyyyMMdd) -> 借款到期时间
	MARKETID             int64   `json:"marketid"  xorm:"MARKETID" form:"marketid"`                      // 市场ID - 拆成三个独立模式后，根据市场ID处理
	SCFCONTRACTID        string  `json:"scfcontractid"  xorm:"SCFCONTRACTID" form:"scfcontractid"`       // SCF合同ID(310+Unix秒时间戳(10位)+xxxxxx)
	SCFCONTRACTTYPE      int32   `json:"scfcontracttype"  xorm:"SCFCONTRACTTYPE" form:"scfcontracttype"` // 合同类型 - 1:仓单回购 2:仓单质押 3:合作套保 4:资金贷款 5:融资单 6:融资回购
	LENDERUSERID         int64   `json:"lenderuserid"  xorm:"LENDERUSERID" form:"lenderuserid"`          // 贷款方用户ID -> 融出方用户ID
	BORROWERUSERID       int64   `json:"borroweruserid"  xorm:"BORROWERUSERID" form:"userid"`            // 借款用户ID
	LENDERAMOUNT         float64 `json:"lenderamount"  xorm:"LENDERAMOUNT"`                              // 贷款金额 -> 借款金额
	WRFACTORTYPEID       int64   `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID" form:"wrfactortypeid"`    // 仓单要素类型ID -   [仓单回购\仓单质押]
	WRQTY                float64 `json:"wrqty"  xorm:"WRQTY"`                                            // 仓单数量 - [仓单回购\仓单质押] -> 数量
	ISAUTOLOAN           int32   `json:"isautoloan"  xorm:"ISAUTOLOAN"`                                  // 是否自动放款 - 0:手动 1:自动 - [仓单质押]
	REMAINAMOUNT         float64 `json:"remainamount"  xorm:"REMAINAMOUNT"`                              // 合同剩余金额 -> 剩余金额
	INITMARGIN           float64 `json:"initmargin"  xorm:"INITMARGIN"`                                  // 保证金 - [仓单回购]
	REMAINWRPOSITIONQTY  float64 `json:"remainwrpositionqty"  xorm:"REMAINWRPOSITIONQTY"`                // 剩余仓单头寸数量 - [仓单回购\仓单质押] -> 剩余数量(待回购数量)
	BUYBACKWRPOSITIONQTY float64 `json:"buybackwrpositionqty"  xorm:"BUYBACKWRPOSITIONQTY"`              // 已回购仓单头寸数量 - [仓单回购\仓单质押] -> 已回购数量
	INTERESTAMOUNT       float64 `json:"interestamount"  xorm:"INTERESTAMOUNT"`                          // 计息金额=合同剩余金额 - 初始保证金 - 追缴保证金 \  合同剩余金额
	TOTALINTEREST        float64 `json:"totalinterest"  xorm:"TOTALINTEREST"`                            // 已计利息 -> 已计费用
	CLOSEINTEREST        float64 `json:"closeinterest"  xorm:"CLOSEINTEREST"`                            // 已结利息 -> 已结费用
	INTERESTDEBT         float64 `json:"interestdebt"  xorm:"INTERESTDEBT"`                              // 利息欠款(记录负数) -> 费用欠款
	STARTINTERESTDAY     string  `json:"startinterestday"  xorm:"STARTINTERESTDAY"`                      // 计息开始日(yyyyMMdd)  -> 开始计费日期
	LASTCLOSEINTERESTDAY string  `json:"lastcloseinterestday"  xorm:"LASTCLOSEINTERESTDAY"`              // 最近结息日(yyyyMMdd)
	SCFCONTRACTSTATUS    int32   `json:"scfcontractstatus"  xorm:"SCFCONTRACTSTATUS"`                    // 合同状态 - 1:待确认 2:已确认 3:确认拒绝 4:已支付保证金 5:已激活 6:已违约 7:到期结束 8:延期结束 9:违约结束 10:已注销 11:已关闭 99:未提交
	UPDATETIME           string  `json:"updatetime"  xorm:"UPDATETIME"`                                  // 更新时间
	REMARK               string  `json:"remark"  xorm:"REMARK"`                                          // 备注
	PRICEMOVE            float64 `json:"pricemove"  xorm:"PRICEMOVE"`                                    // 估值升贴水值
	CURRISKLEVEL         int32   `json:"currisklevel"  xorm:"CURRISKLEVEL"`                              // 风险级别 - 1:正常 2:追加货款 3:支付尾款

	PRODUCTDETAILID     int64   `json:"productdetailid"  xorm:"PRODUCTDETAILID" form:"productdetailid"` // 产品组合ID
	PRODUCTID           int64   `json:"productid"  xorm:"PRODUCTID"`                                    // 金融产品ID
	PRODUCTNAME         string  `json:"productname"  xorm:"'PRODUCTNAME'"`                              // 产品名称
	FINANCINGDAYS       int32   `json:"financingdays"  xorm:"FINANCINGDAYS"`                            // 天数(天)
	MARGINRATIO         float64 `json:"marginratio"  xorm:"MARGINRATIO"`                                // 保证金比率质押率 - [仓单质押]
	INTERESTRATEMODE    int32   `json:"interestratemode"  xorm:"INTERESTRATEMODE"`                      // 利率方式 - 1:年利率 2:日利率 3:日固定值
	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"`                // 结息方式值

	Wr2FactorType `xorm:"extends"`

	UNPAIDINTEREST SFLOAT64 `json:"unpaidinterest"` // 未结费用
	LENDERUSERNAME string   `json:"lenderusername"` // 融出方
}

func (r *WrScfContract) calc() {
	r.LENDERUSERNAME = mtpcache.GetUserNameByUserId(r.LENDERUSERID)
	r.LENDERUSERNAME = EncryptByStar(r.LENDERUSERNAME)
	r.UNPAIDINTEREST.Set(r.TOTALINTEREST - r.CLOSEINTEREST)
	r.UNPAIDINTEREST.Round(2)
}

func (r *WrScfContract) 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.scfcontractid) scfcontractid," +
		"       t.scfcontracttype," +
		"       t.marketid," +
		"       t.lenderuserid," +
		"       t.lenderamount," +
		"       s.remainamount," +
		"       s.initmargin," +
		"       t.wrqty," +
		"       s.buybackwrpositionqty," +
		"       s.remainwrpositionqty," +
		"       s.closeinterest," +
		"       s.totalinterest," +
		"       s.interestdebt," +
		"       s.startinterestday," +
		"       s.lastcloseinterestday," +
		"       t.deadline," +
		"       s.scfcontractstatus," +
		"       p.productdetailid," +
		"       p.productid," +
		"       f.productname," +
		"       p.FINANCINGDAYS," +
		"       p.MARGINRATIO," +
		"       p.INTERESTRATEMODE," +
		"       p.INTERESTRATE," +
		"       p.INTERESTMINLEN," +
		"       p.INTERESTSETTLEMODE," +
		"       p.INTERESTSETTLEVALUE," +
		"       tmp.*" +
		"  from scf_contract t" +
		"  left join scf_contractinfo s" +
		"    on t.scfcontractid = s.scfcontractid" +
		"  left join WRTrade_FinanceBuyApply a" +
		"    on t.scfcontractid = a.scfcontractid" +
		"  left join FINANCEAREAPRODUCTDETAIL p" +
		"    on a.productdetailid = p.productdetailid" +
		"  left join FINANCEAREAPRODUCT f" +
		"    on p.productid = f.productid" +
		"  left join tmp" +
		"    on t.wrfactortypeid = tmp.wrfactortypeid" +
		" where 1 = 1"
	sqlId.And("t.borroweruserid", r.BORROWERUSERID)
	sqlId.AndEx("t.scfcontractid", r.SCFCONTRACTID, r.SCFCONTRACTID != "")
	return sqlId.String()
}

// GetDataEx 获取仓单贸易融资合同
func (r *WrScfContract) GetDataEx() (interface{}, error) {
	sData := make([]WrScfContract, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrBuybackDetail 仓单回购明细
type WrBuybackDetail struct {
	SCFWRBUYBACKID       int64   `json:"scfwrbuybackid"  xorm:"SCFWRBUYBACKID" form:"scfwrbuybackid"` // SCF仓单回购明细ID(312+Unix秒时间戳(10位)+xxxxxx)
	SCFCONTRACTID        string  `json:"scfcontractid"  xorm:"SCFCONTRACTID" form:"scfcontractid"`    // SCF合同ID
	BUYBACKQTY           float64 `json:"buybackqty"  xorm:"BUYBACKQTY"`                               // 回购数量
	BUYBACKAMOUNT        float64 `json:"buybackamount"  xorm:"BUYBACKAMOUNT"`                         // 回购金额
	BUYBACKRELEASEMARGIN float64 `json:"buybackreleasemargin"  xorm:"BUYBACKRELEASEMARGIN"`           // 回购释放保证金
	BUYBACKTIME          string  `json:"buybacktime"  xorm:"BUYBACKTIME"`                             // 回购时间
	TRADEDATE            string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`                // 交易日(yyyyMMdd)
	RELATEDWRID          int64   `json:"relatedwrid"  xorm:"RELATEDWRID" form:"relatedwrid"`          // 关联仓单ID
	HANDLESTATUS         int32   `json:"handlestatus"  xorm:"HANDLESTATUS"`                           // 处理 -  1:待处理 2:处理成功 3:处理失败
	ERRORDESC            string  `json:"errordesc"  xorm:"ERRORDESC"`                                 // 错误描述
}

func (r *WrBuybackDetail) calc() {

}

func (r *WrBuybackDetail) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.SCFWRBUYBACKID," +
		"       t.SCFCONTRACTID," +
		"       t.BUYBACKQTY," +
		"       t.BUYBACKAMOUNT," +
		"       t.BUYBACKRELEASEMARGIN," +
		"       to_char(t.BUYBACKTIME, 'yyyy-mm-dd hh24:mi:ss') BUYBACKTIME," +
		"       t.TRADEDATE," +
		"       t.RELATEDWRID," +
		"       t.HANDLESTATUS," +
		"       t.ERRORDESC" +
		"  FROM SCF_WRBUYBACKDETAIL t" +
		" WHERE 1 = 1" +
		"   AND t.handlestatus = 2"
	sqlId.AndEx("scfcontractid", r.SCFCONTRACTID, r.SCFCONTRACTID != "")
	return sqlId.String()
}

// GetDataEx 获取仓单回购明细
func (r *WrBuybackDetail) GetDataEx() (interface{}, error) {
	sData := make([]WrBuybackDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrScfContractInterest 合同计息表(费用明细)
type WrScfContractInterest struct {
	SCFCONTRACTINTERESTID int64   `json:"scfcontractinterestid"  xorm:"SCFCONTRACTINTERESTID"`         // SCF合同计息ID(313+Unix秒时间戳(10位)+xxxxxx)
	SCFCONTRACTID         string  `json:"scfcontractid"  xorm:"SCFCONTRACTID" form:"scfcontractid"`    // SCF合同ID
	INTERESTDAY           string  `json:"interestday"  xorm:"INTERESTDAY"`                             // 计息日(yyyyMMdd)
	INTEREST              float64 `json:"interest"  xorm:"INTEREST"`                                   // 利息
	CLOSEINTEREST         float64 `json:"closeinterest"  xorm:"CLOSEINTEREST"`                         // 已结利息
	CLOSEINTERESTSTATUS   int32   `json:"closeintereststatus"  xorm:"CLOSEINTERESTSTATUS"`             // 结息状态 - 1:未结 2:已结
	UPDATETIME            string  `json:"updatetime"  xorm:"UPDATETIME"`                               // 更新时间
	CREATORID             int64   `json:"creatorid"  xorm:"CREATORID" form:"creatorid"`                // 操作人ID
	CLOSETIME             string  `json:"closetime"  xorm:"CLOSETIME"`                                 // 结息时间
	CLOSETRADEDATE        string  `json:"closetradedate"  xorm:"CLOSETRADEDATE" form:"closetradedate"` // 结息交易日
	INTERESTAMOUNT        float64 `json:"interestamount"  xorm:"INTERESTAMOUNT"`                       // 计息金额
	DAYRATE               float64 `json:"dayrate"  xorm:"DAYRATE"`                                     // 日利率(根据利率配置计算出的日利率值,年利率则除以360,保留5位小数)
}

func (r *WrScfContractInterest) calc() {

}

func (r *WrScfContractInterest) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.SCFCONTRACTINTERESTID," +
		"       t.SCFCONTRACTID," +
		"       t.INTERESTDAY," +
		"       t.INTEREST," +
		"       t.CLOSEINTEREST," +
		"       t.CLOSEINTERESTSTATUS," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.CREATORID," +
		"       to_char(t.CLOSETIME, 'yyyy-mm-dd hh24:mi:ss') CLOSETIME," +
		"       t.CLOSETRADEDATE," +
		"       t.INTERESTAMOUNT," +
		"       t.DAYRATE" +
		"  FROM SCF_CONTRACTINTEREST t" +
		" WHERE 1 = 1"
	sqlId.AndEx("scfcontractid", r.SCFCONTRACTID, r.SCFCONTRACTID != "")
	return sqlId.String()
}

// GetDataEx 获取描述
func (r *WrScfContractInterest) GetDataEx() (interface{}, error) {
	sData := make([]WrScfContractInterest, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrOutInApply 出入库申请(提货)
type WrOutInApply struct {
	APPLYID        string `json:"applyid"  xorm:"'APPLYID'"`               // 申请单id
	APPLYTYPE      int32  `json:"applytype"  xorm:"'APPLYTYPE'"`           // 申请类型 - 1:预约入库 2:预约出库 3:入库注册 4:出库注销
	USERID         int64  `json:"userid"  xorm:"'USERID'" form:"userid"`   // 用户id
	APPLYTIME      string `json:"applytime"  xorm:"'APPLYTIME'"`           // 申请时间
	APPLYSTATUS    int32  `json:"applystatus"  xorm:"'APPLYSTATUS'"`       // 申请状态 - 0:预约成功 1:待初审 2:初审通过 3:初审拒绝 4:初审失败 5复审通过 6:复审拒绝 7:复审失败 8:已撤销
	QTY            int64  `json:"qty"  xorm:"'QTY'"`                       // 数量
	EXPRESSNUM     string `json:"expressnum"  xorm:"'EXPRESSNUM'"`         // 物流单号
	EXPRESSCOMPANY string `json:"expresscompany"  xorm:"'EXPRESSCOMPANY'"` // 物流公司
	BANNERPICURL   string `json:"bannerpicurl"  xorm:"BANNERPICURL"`       // Banner图
	Wr2FactorType  `xorm:"extends"`

	BEGINDATE string `json:"begindate" form:"begindate"` // 开始交易日(yyyymmdd)
	ENDDATE   string `json:"enddate" form:"enddate"`     // 结束交易日(yyyymmdd)

	PageEx `xorm:"extends"` // 页码信息
}

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

func (r *WrOutInApply) 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," +
		"         w.bannerpicurl," +
		"         w.minivalue," +
		"         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.applyid) applyid," +
		"       t.applytype," +
		"       t.userid," +
		"       to_char(t.applytime, 'yyyy-mm-dd hh24:mi:ss') applytime," +
		"       decode(t.applytype, 2, t.applystatus, 4, t.applystatus2) applystatus," +
		"       decode(t.applytype, 2, t.inqty, 4, t.inrealqty) qty," +
		"       d.wrfactortypeid," +
		"       e.expressnum," +
		"       e.expresscompany," +
		"       k.*" +
		"  from WROutInApply t" +
		"  left join wroutindetail d" +
		"    on t.applyid = d.applyid" +
		"  left join wroutinexpress e" +
		"    on t.applyid = e.applyid" +
		"  left join tmp k" +
		"    on d.wrfactortypeid = k.wrfactortypeid" +
		" where t.applytype in (2, 4)"
	sqlId.AndEx("t.USERID", r.USERID, r.USERID > 0)
	// 格式 yyyymmdd
	if len(r.BEGINDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE >= '%v'", r.BEGINDATE)
	}

	if len(r.ENDDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE <= '%v'", r.ENDDATE)
	}
	sqlId.Join(" order by t.applytime desc")
	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

// GetDataEx 获取出入库申请(提货)
func (r *WrOutInApply) GetDataEx() (interface{}, error) {
	sData := make([]WrOutInApply, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// GetDataEx 获取出入库申请(提货) - 分页
func (r *WrOutInApply) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]WrOutInApply, 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
}

// WrDeliveryDetail 交收
type WrDeliveryDetail struct {
	DELIVERYID          string  `json:"deliveryid"  xorm:"'DELIVERYID'"`                 //交割单号(121+Unix秒时间戳(10位)+xxxxxx)
	DELIVERYQTY         float64 `json:"deliveryqty"  xorm:"DELIVERYQTY"`                 // 交收数量(点选数量)
	XDELIVERYCLOSEQTY   float64 `json:"xdeliverycloseqty"  xorm:"'XDELIVERYCLOSEQTY'"`   // 合约数量
	PDELIVERYCLOSEQTY   float64 `json:"pdeliverycloseqty"  xorm:"'PDELIVERYCLOSEQTY'"`   // p合约平仓数量
	DELIVERYTIME        string  `json:"deliverytime"  xorm:"DELIVERYTIME"`               // 交割时间
	DELIVERYAMOUNT      float64 `json:"deliveryamount"  xorm:"DELIVERYAMOUNT"`           // 货款金额(X总货款+P总货款)
	DELIVERYPRICEMOVE   float64 `json:"deliverypricemove"  xorm:"DELIVERYPRICEMOVE"`     // 升贴水(升贴水总额)
	DELIVERYTOTALAMOUNT float64 `json:"deliverytotalamount"  xorm:"DELIVERYTOTALAMOUNT"` // 交收金额
	P1GOODSREMAINAMOUNT float64 `json:"p1goodsremainamount"  xorm:"P1GOODSREMAINAMOUNT"` // 点价货款 ps:当PPRICEMODE=2时, 界面需显示"-"
	WRFACTORTYPEID      string  `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID"`           // 仓单要素类型ID
	DELIVERYSTATUS      int32   `json:"deliverystatus"  xorm:"DELIVERYSTATUS"`           // 状态 - 1:待成交 2:已成交 3:成交失败 4:履约完成
	BUYORSELL           int32   `json:"buyorsell"  xorm:"BUYORSELL"`                     // 方向 -  0:买   1:卖
	DELIVERYORDERID     string  `json:"deliveryorderid"  xorm:"DELIVERYORDERID"`         // 申报单号
	ACCOUNTID           int64   `json:"accountid"  xorm:"ACCOUNTID" form:"accountid"`    // 账号ID
	MATCHACCOUNTID      int64   `json:"matchaccountid"  xorm:"MATCHACCOUNTID"`           // 对手账号ID
	XGOODSID            int32   `json:"xgoodsid"  xorm:"XGOODSID" form:"xgoodsid"`       // x交易合约ID
	PGOODSID            int32   `json:"pgoodsid"  xorm:"'PGOODSID'"`                     // p点选合约id
	PPRICEMODE          int32   `json:"ppricemode"  xorm:"PPRICEMODE"`                   // 点价价格方式 - 1:商品价  2:固定值
	PDELIVERYPRICE      float64 `json:"pdeliveryprice"  xorm:"PDELIVERYPRICE"`           // 点价价格 ps:当PPRICEMODE=2时, 界面需显示"-"
	XGOODSCODE          string  `json:"xgoodscode"  xorm:"'XGOODSCODE'"`                 // 商品代码
	XGOODSNAME          string  `json:"xgoodsname"  xorm:"'XGOODSNAME'"`                 // 商品名称
	PGOODSCODE          string  `json:"pgoodscode"  xorm:"'PGOODSCODE'"`                 // 商品代码
	PGOODSNAME          string  `json:"pgoodsname"  xorm:"'PGOODSNAME'"`                 // 商品名称
	USERID              int64   `json:"userid"  xorm:"'USERID'" form:"userid"`           // 用户id
	USERNAME            string  `json:"username"  xorm:"'USERNAME'"`                     // 用户名称
	MATCHUSERNAME       string  `json:"matchusername"  xorm:"'MATCHUSERNAME'"`           // 对手方名称(仓单持有人)
	TRADEDATE           string  `json:"tradedate"  xorm:"'TRADEDATE'"`                   // 交易日(yyyymmdd)
	WRTYPENAME          string  `json:"wrtypename" xorm:"'WRTYPENAME'"`                  // 商品
	WAREHOUSENAME       string  `json:"warehousename"  xorm:"'WAREHOUSENAME'"`           // 仓库名称
	ENUMDICNAME         string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`               // 单位名称

	XGOODSAMOUNT SFLOAT64 `json:"xgoodsamount"` // 合约金额 = 货款金额(DELIVERYAMOUNT) - 点价货款(P1GOODSREMAINAMOUNT)
	AVERAGEPRICE SFLOAT64 `json:"averageprice"` // 交割均价 = 交割金额 / 交割数量

	BEGINDATE string `json:"begindate" form:"begindate"` // 开始交易日(yyyymmdd)
	ENDDATE   string `json:"enddate" form:"enddate"`     // 结束交易日(yyyymmdd)
}

func (r *WrDeliveryDetail) calc() {
	r.MATCHUSERNAME = EncryptByStar(r.MATCHUSERNAME)
	if r.WRTYPENAME != "" {
		r.WRTYPENAME = strings.ReplaceAll(r.WRTYPENAME, ",", "-")
	}
	r.XGOODSAMOUNT.Set(r.DELIVERYAMOUNT - r.P1GOODSREMAINAMOUNT)
	if r.DELIVERYQTY > 1e-8 {
		r.AVERAGEPRICE.Set(r.DELIVERYTOTALAMOUNT / r.DELIVERYQTY)
		r.AVERAGEPRICE.Round(3)
	}
}

func (r *WrDeliveryDetail) buildSql() string {
	var sqlId utils.SQLVal = `
select t.xgoodsid,
       t.buyorsell,
       to_char(t.deliveryid) deliveryid,
       t.deliveryqty,
       t.xdeliverycloseqty,
       t.pdeliverycloseqty,
       t.deliveryamount,
       t.pdeliveryprice,
       t.p1goodsremainamount,
       t.deliverypricemove,
       t.deliverytotalamount,
       to_char(t.deliverytime, 'yyyy-mm-dd hh24:mi:ss') deliverytime,
       t.deliverystatus,
       t.accountid,
       t.matchaccountid,
       t.tradedate,
       to_char(t.wrfactortypeid) wrfactortypeid,
       to_char(t.deliveryorderid) deliveryorderid,
       g.goodscode xgoodscode,
       g.goodsname xgoodsname,
       t.pgoodsid,
       g2.goodscode pgoodscode,
       g2.goodsname pgoodsname,
       ta.accountname taaccountname,
       u.userid,
       u.accountname username,
       u2.accountname matchusername,
       wd.wrstandardname || w.wrfactortypename2 as WRTYPENAME,
       h.warehousename,
       e.enumdicname
  from deliverydetail t
  left join goods g
    on t.xgoodsid = g.goodsid
  left join goods g2
    on t.pgoodsid = g2.goodsid
  left join taaccount ta
    on t.accountid = ta.accountid
  left join useraccount u
    on ta.userid = u.userid
  left join taaccount ta2
    on t.accountid = ta2.accountid
  left join useraccount u2
    on ta2.userid = u2.userid
  left join wrfactortype w
    on t.wrfactortypeid = w.wrfactortypeid
  left join wrstandard wd
    on w.wrstandardid = wd.wrstandardid
  left join warehouseinfo h on w.warehouseid=h.autoid
  left join enumdicitem e on wd.unitid=e.enumitemname and e.enumdiccode='goodsunit'
 where 1 = 1
`
	sqlId.AndEx("u.userid", r.USERID, r.USERID > 0)
	sqlId.AndEx("t.accountid", r.ACCOUNTID, r.ACCOUNTID > 0)
	return sqlId.String()
}

func (r *WrDeliveryDetail) buildSqlHis() string {
	var sqlId utils.SQLVal = `
select t.xgoodsid,
       t.buyorsell,
       to_char(t.deliveryid) deliveryid,
       t.deliveryqty,
       t.xdeliverycloseqty,
       t.pdeliverycloseqty,
       t.deliveryamount,
       t.pdeliveryprice,
       t.p1goodsremainamount,
       t.deliverypricemove,
       t.deliverytotalamount,
       to_char(t.deliverytime, 'yyyy-mm-dd hh24:mi:ss') deliverytime,
       t.deliverystatus,
       t.accountid,
       t.matchaccountid,
       t.tradedate,
       to_char(t.wrfactortypeid) wrfactortypeid,
       to_char(t.deliveryorderid) deliveryorderid,
       g.goodscode xgoodscode,
       g.goodsname xgoodsname,
       t.pgoodsid,
       g2.goodscode pgoodscode,
       g2.goodsname pgoodsname,
       ta.accountname taaccountname,
       u.userid,
       u.accountname username,
       u2.accountname matchusername,
       wd.wrstandardname || w.wrfactortypename2 as WRTYPENAME,
       h.warehousename,
       e.enumdicname
  from his_deliverydetail t
  left join goods g
    on t.xgoodsid = g.goodsid
  left join goods g2
    on t.pgoodsid = g2.goodsid
  left join taaccount ta
    on t.accountid = ta.accountid
  left join useraccount u
    on ta.userid = u.userid
  left join taaccount ta2
    on t.accountid = ta2.accountid
  left join useraccount u2
    on ta2.userid = u2.userid
  left join wrfactortype w
    on t.wrfactortypeid = w.wrfactortypeid
  left join wrstandard wd
    on w.wrstandardid = wd.wrstandardid
  left join warehouseinfo h on w.warehouseid=h.autoid
  left join enumdicitem e on wd.unitid=e.enumitemname and e.enumdiccode='goodsunit'
 where 1 = 1 and t.isvaliddata = 1
`
	sqlId.AndEx("u.userid", r.USERID, r.USERID > 0)
	sqlId.AndEx("t.accountid", r.ACCOUNTID, r.ACCOUNTID > 0)
	// 格式 yyyymmdd
	if len(r.BEGINDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE >= '%v'", r.BEGINDATE)
	}

	if len(r.ENDDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE <= '%v'", r.ENDDATE)
	}
	return sqlId.String()
}

// GetDataEx 获取交收
func (r *WrDeliveryDetail) GetDataEx() (interface{}, error) {
	var sqlId string
	// 如果指定了开始日期或结束日期, 刚查历史
	if r.BEGINDATE != "" || r.ENDDATE != "" {
		sqlId = r.buildSqlHis()
	} else {
		sqlId = r.buildSql()
	}
	sData := make([]WrDeliveryDetail, 0)
	err := db.GetEngine().SQL(sqlId).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrBsGoodsInfo 合约交易买卖大厅详情
type WrBsGoodsInfo struct {
	GOODSID int32                    `json:"goodsid"  xorm:"GOODSID" form:"goodsid"` // 交易合约ID
	LstItem []WrTypeItem             `json:"lstitem"`                                // 商品信息项
	LstStep []WrGoodsPerformanceStep `json:"lststep"`                                // 履约规则

	BUYORSELL int32 `json:"buyorsell" form:"buyorsell"` // 买卖方向 0-买 1-卖
}

// GetDataEx 获取描述
func (r *WrBsGoodsInfo) GetDataEx() (interface{}, error) {
	sData := make([]WrBsGoodsInfo, 0)
	// 履约关系
	mRelation := DeliveryRelation{GOODSID: r.GOODSID}
	if d, err := mRelation.GetDataEx(); err == nil {
		if sRelation, ok := d.([]DeliveryRelation); ok {
			val := WrBsGoodsInfo{GOODSID: r.GOODSID,
				LstItem: make([]WrTypeItem, 0),
				LstStep: make([]WrGoodsPerformanceStep, 0)}
			if len(sRelation) > 0 {
				vR := sRelation[0]

				val.LstItem = append(val.LstItem, WrTypeItem{NAME: "品种", VALUE: vR.DELIVERYGOODSNAME})
				val.LstItem = append(val.LstItem, WrTypeItem{NAME: "商品", VALUE: vR.WRSTANDARDNAME})

				mItem := WrStandardFactoryItem{WRSTANDARDID: int64(vR.WRSTANDARDID), WMCATFLAG: 1}
				if d2, err := mItem.GetDataEx(); err == nil {
					if lst, ok := d2.([]WrStandardFactoryItem); ok {
						for i := range lst {
							val.LstItem = append(val.LstItem,
								WrTypeItem{NAME: lst[i].ITEMTYPENAME, VALUE: lst[i].DGFACTORYITEMVALUE})
						}
					}
				}

				// 履约计划模板步骤
				tmpId := IFValue(r.BUYORSELL == 0, vR.BUYTEMPLATEID, vR.SELLTEMPLATEID).(int64)
				mStep := WrGoodsPerformanceStep{TEMPLATEID: tmpId}
				if d3, err := mStep.GetData(); err == nil {
					val.LstStep = d3
				}
			}
			sData = append(sData, val)
		}
	}
	return sData, nil
}

// WrTradeOrderDetail 合约交易买卖大厅
type WrTradeOrderDetail struct {
	ORDERID    string   `json:"orderid"  xorm:"'ORDERID'"`                      // 委托单号
	GOODSID    int32    `json:"goodsid"  xorm:"'GOODSID'" form:"goodsid"`       // 商品id
	BUYORSELL  int32    `json:"buyorsell"  xorm:"'BUYORSELL'" form:"buyorsell"` // 买卖方向 0-买 1-卖
	ORDERPRICE SFLOAT64 `json:"orderprice"  xorm:"'ORDERPRICE'"`                // 委托价格
	ORDERQTY   int64    `json:"orderqty"  xorm:"'ORDERQTY'"`                    // 委托数量
	USERID     int64    `json:"userid"  xorm:"'USERID'"`                        // 用户id
	USERNAME   string   `json:"username"  xorm:"'USERNAME'"`                    // 用户名称(已脱敏)
	PageEx     `xorm:"extends"`
}

func (r *WrTradeOrderDetail) calc() {
	r.USERNAME = EncryptByStar(r.USERNAME)
}

func (r *WrTradeOrderDetail) buildSql() string {
	var sqlId utils.SQLVal = "select to_char(t.orderid) orderid," +
		"       t.buyorsell," +
		"       t.goodsid," +
		"       t.orderprice," +
		"       t.orderqty - t.tradeqty orderqty," +
		"       u.userid," +
		"       u.accountname username," +
		"       t.orderstatus" +
		"  from trade_orderdetail t" +
		"  left join taaccount ta" +
		"    on t.accountid = ta.accountid" +
		"  left join useraccount u" +
		"    on ta.relateduserid = u.userid" +
		" where 1 = 1" +
		"   and t.orderstatus in(3,7)"
	sqlId.And("t.GOODSID", r.GOODSID)
	sqlId.And("t.BUYORSELL", r.BUYORSELL)

	if r.BUYORSELL == 0 {
		sqlId.Join(" order by t.orderprice desc, t.ordertime desc")
	} else {
		sqlId.Join(" order by t.orderprice, t.ordertime desc")
	}
	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

// GetDataByPage 获取合约交易买卖大厅
func (r *WrTradeOrderDetail) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]WrTradeOrderDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	if len(sData) > 0 {
		r.Total = sData[0].Total
	}
	return sData, err, r.Page, r.PageSize, r.Total
}

// WrAverageTradePrice 成交均价(历史走势)
type WrAverageTradePrice struct {
	TRADEDATE    string   `json:"tradedate"  xorm:"'TRADEDATE'"`       // 交易日yyyymm
	TRADEAMOUNT  SFLOAT64 `json:"-"  xorm:"'TRADEAMOUNT'"`             // 总成交额
	TRADEQTY     SFLOAT64 `json:"-"  xorm:"'TRADEQTY'"`                // 总成交量
	AVERAGEPRICE SFLOAT64 `json:"averageprice"  xorm:"'AVERAGEPRICE'"` // 成交均价
	PageEx       `xorm:"extends"`

	WRFACTORTYPEID int64 `json:"-" form:"wrfactortypeid"` // 仓单要素id
	HASWR          int32 `json:"-" form:"haswr"`          // 0-仓单预售 1-仓单贸易
}

func (r *WrAverageTradePrice) calc() {

}

func (r *WrAverageTradePrice) buildSql() string {
	var sqlId utils.SQLVal = "select t.tradedate," +
		"       sum(t.tradeamount) tradeamount," +
		"       sum(t.tradeqty) tradeqty," +
		"       round(sum(t.tradeamount) / sum(t.tradeqty), 2) as averagePrice" +
		"  from his_wrtrade_tradedetail t" +
		" where t.wrtradetype in (1, 2)" +
		"   and t.haswr = %v" +
		"   and t.wrfactortypeid = %v" +
		" group by t.tradedate" +
		" order by t.tradedate desc"
	sqlId.FormatParam(r.HASWR, r.WRFACTORTYPEID)
	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

// GetDataByPage 获取成交均价(历史走势)
func (r *WrAverageTradePrice) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]WrAverageTradePrice, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	// 进行反序, sql是日期倒序查的, 反序后变为日期从小到大
	if nLen := len(sData); nLen >= 2 {
		i, j := 0, nLen-1
		for i < j {
			sData[i], sData[j] = sData[j], sData[i]
			i++
			j--
		}
	}
	if len(sData) > 0 {
		r.Total = sData[0].Total
	}
	return sData, err, r.Page, r.PageSize, r.Total
}

// WrQuoteOrderMyq 贸易圈大厅
type WrQuoteOrderMyq 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'"`                                // 购买方(销售方)
	ENUMDICNAME    string   `json:"enumdicname" xorm:"'enumdicname'"`                           // 单位名称
	MINIVALUE      int32    `json:"minivalue"  xorm:"'MINIVALUE'"`                              // 现货商品最小变动值
	DELIVERYMONTH  string   `json:"deliverymonth"  xorm:"'DELIVERYMONTH'" form:"deliverymonth"` // 交收月
	ORDERTIME      string   `json:"ordertime"  xorm:"'ORDERTIME'"`                              // 委托时间
	DELISTMINQTY   SFLOAT64 `json:"delistminqty"  xorm:"'DELISTMINQTY'"`                        // 起摘数量

	CANBARGAIN            int32   `json:"canbargain"  xorm:"'CANBARGAIN'"`                                // 是否可议价 - 0:不可 1:可
	CANPART               int32   `json:"canpart"  xorm:"'CANPART'"`                                      // 是否允许部份摘牌 - 0:不允许 1:允许
	MARGINFLAG            int32   `json:"marginflag"  xorm:"'MARGINFLAG'"`                                // 挂牌是否指定保证金 - 0:未指定 1:指定
	MARGINVALUE           float64 `json:"marginvalue"  xorm:"'MARGINVALUE'"`                              // 保证金设置值
	VALIDTYPE             int32   `json:"validtype"  xorm:"'VALIDTYPE'"`                                  // 有效类型 - 1当日有效 2本周有效 3指定日期有效 4一直有效
	VALIDTIME             string  `json:"validtime"  xorm:"'VALIDTIME'"`                                  // 有效期限
	PERFORMANCETEMPLATEID int64   `json:"performancetemplateid"  xorm:"'PERFORMANCETEMPLATEID'"`          // 履约模板id
	GOODSCODE             string  `json:"goodscode"  xorm:"'GOODSCODE'"`                                  // 商品合约代码
	TEMPLATENAME          string  `json:"templatename"  xorm:"'TEMPLATENAME'"`                            // 履约模板名称
	WAREHOUSENAME         string  `json:"warehousename"  xorm:"'WAREHOUSENAME'"`                          // 仓库名称
	WRTYPENAME            string  `json:"wrtypename" xorm:"'WRTYPENAME'"`                                 // 商品
	WRSTANDARDID          int64   `json:"wrstandardid"  xorm:"WRSTANDARDID" form:"wrstandardid"`          // 现货商品ID
	DELIVERYGOODSID       int32   `json:"deliverygoodsid"  xorm:"DELIVERYGOODSID" form:"deliverygoodsid"` // 品种ID
	WRFACTORTYPEID        string  `json:"wrfactortypeid"  xorm:"'WRFACTORTYPEID'"`                        // 仓单要素id
	WRSTANDARDNAME        string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`                        // 商品名称
	WRSTANDARDCODE        string  `json:"wrstandardcode"  xorm:"WRSTANDARDCODE" form:"wrstandardcode"`    // 现货商品代码
	DELIVERYGOODSCODE     string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`                  // 品种代码
	DELIVERYGOODSNAME     string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`                  // 品种名称
	PageEx                `xorm:"extends"`

	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
	DGITEMNAME  string `json:"-" form:"dgitemname"`  // 筛选项
}

func (r *WrQuoteOrderMyq) calc() {
	r.WRTYPENAME = strings.ReplaceAll(r.WRTYPENAME, ",", "-")
	r.USERNAME = EncryptByStar(r.USERNAME)
}

func (r *WrQuoteOrderMyq) 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,
       t.deliverymonth,
       t.delistminqty,
       t.haswr,
       t.canbargain,
       t.canpart,
       t.marginflag,
       t.marginvalue,
       t.validtype,
       to_char(t.validtime, 'yyyy-mm-dd hh24:mi:ss') validtime,
       t.performancetemplateid,
       to_char(t.ordertime, 'yyyy-mm-dd hh24:mi:ss') ordertime,
       u.accountname as username,
       c.goodsid,
       c.goodscode,
       wd.minivalue,
       wd.wrstandardname,
       wd.wrstandardcode,
       g.deliverygoodscode,
       g.deliverygoodsname,
       e.enumdicname,
       wd.wrstandardname || w.wrfactortypename2 WRTYPENAME,
       w.wrstandardid,
       w.deliverygoodsid,
       s.templatename,
       h.warehousename
  from WRTRADE_ORDERDETAIL t
  left join WRTRADE_GOODSCONFIG c
    on t.wrtradeorderid = c.wrtradeorderid
  left join Useraccount u
    on t.userid = u.userid
  left join wrfactortype w
    on t.wrfactortypeid = w.wrfactortypeid
  left join wrstandard wd
    on w.wrstandardid = wd.wrstandardid
  left join performanceplantemplate s
    on t.performancetemplateid = s.autoid
  left join warehouseinfo h
    on w.warehouseid = h.autoid
  left join deliverygoods g on w.deliverygoodsid=g.deliverygoodsid
  left join enumdicitem e
    on wd.unitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 where 1 = 1
   and t.wrtradetype = 1
   and t.isspecified=1
   and t.wrtradeorderstatus in (3, 7)
   and (t.userid=%v or
   (t.allfriendsflag=1 and t.userid in(select userid from userfriend where frienduserid=%v)) 
   or (t.allfriendsflag=0 and %v in(select k.matchuserid from wrtrade_matchdetail k where k.wrtradeorderid=t.wrtradeorderid))
   )
`
	sqlId.FormatParam(r.USERID, r.USERID, r.USERID)
	sqlId.AndEx("t.haswr", r.HasWr, r.HasWr >= 0)
	r.WrPriceType = 1 // 查固定价
	sqlId.And("t.wrpricetype", r.WrPriceType)
	sqlId.AndEx("t.wrfactortypeid", r.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)
	sqlId.AndEx("t.deliverymonth", r.DELIVERYMONTH, len(r.DELIVERYMONTH) > 0)
	sqlId.AndEx("w.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	sqlId.AndEx("w.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	if d, ok := FtItemName(r.DGITEMNAME); ok {
		sqlId.AndLikes("w.wrfactortypename", d...)
	}

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

// GetDataByPage 获取贸易圈大厅
func (r *WrQuoteOrderMyq) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]WrQuoteOrderMyq, 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
}

// WrDeliveryAvalidHoldLB 合约交收可点选仓单
type WrDeliveryAvalidHoldLB struct {
	ACCOUNTID         int64   `json:"accountid"  xorm:"'ACCOUNTID'" form:"accountid"` // 资金账号
	GOODSID           int32   `json:"goodsid"  xorm:"'GOODSID'" form:"goodsid"`       // x合约商品id
	WRSTANDARDID      int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`            // 品类d
	UQTY              float64 `json:"-"  xorm:"'UQTY'"`                               // 用户持有数量
	QTY               float64 `json:"-"  xorm:"'QTY'"`                                // 用户仓单数量
	WRFACTORTYPEID    string  `json:"wrfactortypeid"  xorm:"'WRFACTORTYPEID'"`        // 仓单要素id
	LADINGBILLID      string  `json:"ladingbillid"  xorm:"'LADINGBILLID'"`            // 提单id
	SUBNUM            int64   `json:"subnum"  xorm:"'SUBNUM'"`                        // 提单子id
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`      // 品种id
	USERID            int64   `json:"userid"  xorm:"'USERID'"`                        // 用户id
	WRFACTORTYPENAME2 string  `json:"-"  xorm:"'WRFACTORTYPENAME2'"`                  // 仓单要素名称(不含仓库)
	WAREHOUSEID       int32   `json:"warehouseid"  xorm:"'WAREHOUSEID'"`              // 仓库id
	AVALIDQTY         float64 `json:"avalidqty"  xorm:"'AVALIDQTY'"`                  // 数量(可点选数量)
	WRSTANDARDNAME    string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`        // 品类名称
	WRSTANDARDCODE    string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`        // 品代码
	WAREHOUSECODE     string  `json:"warehousecode"  xorm:"'WAREHOUSECODE'"`          // 仓库代码
	WAREHOUSENAME     string  `json:"warehousename"  xorm:"'WAREHOUSENAME'"`          // 仓库名称
	XGOODSCODE        string  `json:"xgoodscode"  xorm:"'XGOODSCODE'"`                // x合约代码
	XGOODSNAME        string  `json:"xgoodsname"  xorm:"'XGOODSNAME'"`                // x合约名称
	PGOODSID          int32   `json:"pgoodsid"  xorm:"'PGOODSID'"`                    // p商品id(点价合约)
	PGOODSCODE        string  `json:"pgoodscode"  xorm:"'PGOODSCODE'"`                // p商品代码(点价合约)
	PGOODSNAME        string  `json:"pgoodsname"  xorm:"'PGOODSNAME'"`                // p商品名称(点价合约)
	USERNAME          string  `json:"username"  xorm:"'USERNAME'"`                    // 仓单持有人
	PRICEMOVE         float64 `json:"pricemove"  xorm:"'PRICEMOVE'"`                  // 升贴水
	WRTYPENAME        string  `json:"wrtypename"`                                     // 商品
	UNITID            int32   `json:"-"  xorm:"'UNITID'"`                             // 品类单位id

	ENUMDICNAME string `json:"enumdicname"` // 单位名称
}

func (r *WrDeliveryAvalidHoldLB) calc() {
	r.USERNAME = EncryptByStar(r.USERNAME)
	if r.WRTYPENAME == "" {
		if len(r.WRFACTORTYPENAME2) > 0 {
			r.WRTYPENAME = r.WRSTANDARDNAME + r.WRFACTORTYPENAME2
		} else {
			r.WRTYPENAME = r.WRSTANDARDNAME
		}
	}
	r.WRTYPENAME = strings.ReplaceAll(r.WRTYPENAME, ",", "-")
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *WrDeliveryAvalidHoldLB) buildSql() string {
	var sqlId utils.SQLVal = `
with tmp as
 (select k.wrfactortypeid, k.goodsid, sum(k.pricemove) pricemove
    from (select to_char(t.wrfactortypeid) wrfactortypeid,
                 b.dgfactoryitemtypeid,
                 b.itemtypename,
                 c.dgfactoryitemvalue,
                 t.optioncompare,
                 t.warehouseid,
                 t.wrstandardid,
                 c.dgfactoryitemid,
                 t.wrfactortypename,
                 t.wrfactortypename2,
                 r.goodsid,
                 nvl(r.premiumvalue, 0) pricemove
            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 deliveryrelationdetail r
              on c.dgfactoryitemid = r.dgfactoryitemid
             and b.dgfactoryitemtypeid = r.dgfactoryitemtypeid
           where 1 = 1) k
   group by k.wrfactortypeid, k.goodsid)
select a.*,
       case
         when a.uqty * g1.agreeunit * r.mindeliveryqty * (r.rratio2/nvl(r.rratio1,1)) < a.qty then
          a.uqty * g1.agreeunit * r.mindeliveryqty * (r.rratio2/nvl(r.rratio1,1))
         else
          a.qty
       end as avalidqty,
       wd.wrstandardname,
       wd.wrstandardcode,
       wd.unitid,
       h.warehousecode,
       h.warehousename,
       g1.goodscode xgoodscode,
       g1.goodsname xgoodsname,
       r.pgoodsid,
       g2.goodscode pgoodscode,
       g2.goodsname pgoodsname,
       u.accountname username,
       tmp.pricemove
  from (select t.accountid,
               t.goodsid,
               t.wrstandardid,
               t.inqty - t.cancelqty - t.deliveryqty - p.buycurpositionqty as uqty,
               hl.qty - hl.freezerqty qty,
               to_char(hl.wrfactortypeid) wrfactortypeid,
               to_char(hl.ladingbillid) ladingbillid,
               hl.subnum,
               hl.deliverygoodsid,
               hl.userid,
               w.wrfactortypename2,
               w.warehouseid
          from cptrade_usergoodsdata t
         inner join wrholdlb hl
            on t.accountid = hl.accountid
         inner join wrfactortype w
            on t.wrstandardid = w.wrstandardid
           and hl.wrfactortypeid = w.wrfactortypeid
         left join tradeposition p on t.accountid=p.accountid and t.goodsid=p.goodsid
           ) a
  left join wrstandard wd
    on a.wrstandardid = wd.wrstandardid
  left join warehouseinfo h
    on a.warehouseid = h.autoid
 inner join deliveryrelation r
    on a.goodsid = r.goodsid
   and a.wrstandardid = r.wrstandardid
  left join goods g1
    on a.goodsid = g1.goodsid
  left join goods g2
    on r.pgoodsid = g2.goodsid
  left join useraccount u
    on a.userid = u.userid
  left join tmp
    on a.wrfactortypeid = tmp.wrfactortypeid
   and a.goodsid = tmp.goodsid
 where a.uqty > 0
   and a.qty > 0
`
	// 过滤掉自己的仓单 and a.accountid !=
	sqlId.Join(fmt.Sprintf(" and a.accountid != %v", r.ACCOUNTID))
	sqlId.And("a.goodsid", r.GOODSID)
	return sqlId.String()
}

// GetDataEx 获取合约交收可点选仓单
func (r *WrDeliveryAvalidHoldLB) GetDataEx() (interface{}, error) {
	sData := make([]WrDeliveryAvalidHoldLB, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrUserFriend 我的朋友
type WrUserFriend struct {
	USERID       int64  `json:"userid"  xorm:"'USERID'" form:"userid"` // 用户id
	FRIENDUSERID int64  `json:"frienduserid"  xorm:"'FRIENDUSERID'"`   // 朋友id
	FRIENDNAME   string `json:"friendname"  xorm:"'FRIENDNAME'"`       // 朋友名称(已脱敏)
	ISFRIEND     int32  `json:"isfriend"  xorm:"'ISFRIEND'"`           // 是否好友 0-不是 1-是
	NEWUSERID    int64  `json:"-" form:"newuserid"`                    // 新朋友id
}

func (r *WrUserFriend) calc() {
	r.FRIENDNAME = EncryptByStar(r.FRIENDNAME)
}

func (r *WrUserFriend) buildSql() string {
	var sqlId utils.SQLVal
	if r.NEWUSERID > 0 {
		sqlId =
			"select t.userid frienduserid, t.accountname friendname, nvl(f.userid-f.userid+1,0) isfriend" +
				"  from useraccount t" +
				"  left join userfriend f on t.userid=f.frienduserid and f.userid=%v" +
				" where 1 = 1" +
				"   and t.userid = %v"
		sqlId.FormatParam(r.USERID, r.NEWUSERID)
	} else {
		sqlId = "select t.userid, t.frienduserid, u.accountname friendname, 1 as isfriend" +
			"  from UserFriend t" +
			"  left join useraccount u" +
			"    on t.frienduserid = u.userid" +
			" where 1 = 1"
		sqlId.And("t.userid", r.USERID)
	}

	return sqlId.String()
}

// GetDataEx 获取我的朋友
func (r *WrUserFriend) GetDataEx() (interface{}, error) {
	sData := make([]WrUserFriend, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrFriendApply 好友申请
type WrFriendApply struct {
	FRIENDAPPLYID string `json:"friendapplyid"  xorm:"FRIENDAPPLYID" form:"friendapplyid"` // 申请ID(SEQ_FRIENDAPPLY)
	USERID        int64  `json:"userid"  xorm:"USERID" form:"userid"`                      // 申请人用户ID
	FRIENDUSERID  string `json:"frienduserid"  xorm:"FRIENDUSERID" form:"frienduserid"`    // 好友用户ID
	APPLYSRC      int32  `json:"applysrc"  xorm:"APPLYSRC"`                                // 申请来源 - 1:管理端 2:终端
	APPLICANTID   int64  `json:"applicantid"  xorm:"APPLICANTID" form:"applicantid"`       // 申请人
	REMARK        string `json:"remark"  xorm:"REMARK"`                                    // 备注
	APPLYTIME     string `json:"applytime"  xorm:"APPLYTIME"`                              // 申请时间
	APPLYSTATUS   int32  `json:"applystatus"  xorm:"APPLYSTATUS"`                          // 申请状态 - 1:待审核 2:审核通过 3:审核拒绝 4:处理失败 5:已撤回
	AUDITORID     int64  `json:"auditorid"  xorm:"AUDITORID" form:"auditorid"`             // 审核人
	AUDITSRC      int32  `json:"auditsrc"  xorm:"AUDITSRC"`                                // 审核来源 - 1:管理端 2:终端
	AUDITTIME     string `json:"audittime"  xorm:"AUDITTIME"`                              // 审核时间
	AUDITREMARK   string `json:"auditremark"  xorm:"AUDITREMARK"`                          // 审核备注

	FRIENDNAME string `json:"friendname"  xorm:"'FRIENDNAME'"` // 好友名字(已脱敏)
	APPLYNAME  string `json:"applyname"  xorm:"'APPLYNAME'"`   // 申请人名称
	AUDITNAME  string `json:"auditname"  xorm:"'AUDITNAME'"`   // 审核人名称
}

func (r *WrFriendApply) calc() {
	r.FRIENDNAME = EncryptByStar(r.FRIENDNAME)
}

func (r *WrFriendApply) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select 2 as src," +
		"         loginid as usid," +
		"         nvl(to_char(accountname), to_char(logincode)) username" +
		"    from loginaccount" +
		"  union all" +
		"  select 1 as src, autoid as usid, rolename" +
		"    from systemmanagerrole)" +
		"select t.friendapplyid," +
		"       t.userid," +
		"       t.frienduserid," +
		"       t.applysrc," +
		"       t.applicantid," +
		"       t.remark," +
		"       to_char(t.applytime, 'yyyy-mm-dd hh24:mi:ss') applytime," +
		"       t.applystatus," +
		"       t.auditorid," +
		"       t.auditsrc," +
		"       to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') audittime," +
		"       t.auditremark," +
		"       u.accountname friendname," +
		"       k1.username as applyname," +
		"       k2.username as auditname" +
		"  from FriendApply t" +
		"  left join useraccount u" +
		"    on t.frienduserid = u.userid" +
		"  left join tmp k1" +
		"    on t.applicantid = k1.usid" +
		"   and t.applysrc = k1.src" +
		"  left join tmp k2" +
		"    on t.auditorid = k2.usid" +
		"   and t.auditsrc = k2.src" +
		"   where 1=1"
	sqlId.And("t.userid", r.USERID)
	return sqlId.String()
}

// GetDataEx 获取好友申请
func (r *WrFriendApply) GetDataEx() (interface{}, error) {
	sData := make([]WrFriendApply, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrPerformanceTemplate 履约模板
type WrPerformanceTemplate struct {
	AUTOID       int64  `json:"autoid"  xorm:"AUTOID" form:"autoid"`          // AutoID 模板id
	USERID       int64  `json:"userid"  xorm:"USERID" form:"userid"`          // 所属用户
	TEMPLATENAME string `json:"templatename"  xorm:"TEMPLATENAME"`            // 模板名称
	PAYMENTTYPE  int32  `json:"paymenttype"  xorm:"PAYMENTTYPE"`              // 支付方式 - 1:冻结 2:扣款
	CREATETIME   string `json:"createtime"  xorm:"CREATETIME"`                // 创建时间
	CREATORID    int64  `json:"creatorid"  xorm:"CREATORID" form:"creatorid"` // 创建人
	TEMPLATETYPE int32  `json:"templatetype"  xorm:"TEMPLATETYPE"`            // 模板类型 - 0:通用 1:交割 2:仓单贸易 3:预售集采 7:竞拍-竞价式 8:竞拍-大宗式 9:荷兰式--失效枚举：4:竞拍-降价式 (无仓单) 5:挂牌期权  6:竞拍-降价式
	TAKEMODE     int32  `json:"takemode"  xorm:"TAKEMODE"`                    // 提货方式 - 1:无 2:买方自提 3:卖方发货

	FTTMPTYPE  string `json:"-" form:"tmptype"`    // 模板类型筛选
	INCLUDEPUB int32  `json:"-" form:"includepub"` // 是否包含公共模板
	MARKETID   int32  `json:"-" form:"marketid"`   // 市场ID-从市场配置表中指定的模板id查
}

func (r *WrPerformanceTemplate) calc() {

}

func (r *WrPerformanceTemplate) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.USERID," +
		"       t.AUTOID," +
		"       t.TEMPLATENAME," +
		"       t.PAYMENTTYPE," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.CREATORID," +
		"       t.TEMPLATETYPE," +
		"       t.TAKEMODE" +
		"  FROM PERFORMANCEPLANTEMPLATE t" +
		" WHERE 1 = 1"
	if r.USERID > 0 {
		if r.INCLUDEPUB == 1 {
			// t.userid is null 意思是查公共模板
			sqlId.JoinFormat(" and (t.userid is null or t.userid = %v)", r.USERID)
		} else {
			sqlId.And("t.userid", r.USERID)
		}
	}
	sqlId.AndEx("t.autoid", r.AUTOID, r.AUTOID > 0)
	if r.FTTMPTYPE != "" {
		sqlId.JoinFormat(" and t.TEMPLATETYPE in(%v)", r.FTTMPTYPE)
	}
	if r.MARKETID > 0 {
		// 指定了市场, 从市场交易配置表中取履约模板id
		m := WrMarketTradeConfig{MARKETID: r.MARKETID}
		if d, err := m.GetDataEx(); err == nil {
			if v, ok := d.([]WrMarketTradeConfig); ok {
				a := v[0]
				var inStr string
				// 有仓单用 PERFORMANCETEMPIDS, 无仓单用PERFORMANCETEMPID2S,
				// 这2个只会有一个有值, 所以只需取其中一个有值的即可
				if len(a.PERFORMANCETEMPIDS) > 0 {
					inStr = a.PERFORMANCETEMPIDS
				} else if len(a.PERFORMANCETEMPID2S) > 0 {
					inStr = a.PERFORMANCETEMPID2S
				}
				if len(v) > 0 {
					sqlId.JoinFormat(" and t.autoid in(%v)", inStr)
				}
			}
		}
	}
	sqlId.Join(" order by t.AUTOID")
	return sqlId.String()
}

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

// WrPerformanceTemplateEx 履约模板(含步骤信息)
type WrPerformanceTemplateEx struct {
	WrPerformanceTemplate `form:"extends"`
	LstStep               []WrGoodsPerformanceStep // 步骤信息列表
}

func (r *WrPerformanceTemplateEx) addStep(lst []WrGoodsPerformanceStep) {
	for i := range lst {
		if lst[i].TEMPLATEID == r.AUTOID {
			r.LstStep = append(r.LstStep, lst[i])
		}
	}
}

func (r *WrPerformanceTemplateEx) GetDataEx() (interface{}, error) {
	sData := make([]WrPerformanceTemplateEx, 0)
	if d, err := r.WrPerformanceTemplate.GetDataEx(); err == nil {
		if sTmplate, ok := d.([]WrPerformanceTemplate); ok {
			if len(sTmplate) > 0 {
				// 查履约步骤
				ms := WrGoodsPerformanceStep{}
				if d2, err := ms.GetData(); err == nil {
					for i := range sTmplate {
						val := WrPerformanceTemplateEx{}
						val.WrPerformanceTemplate = sTmplate[i]
						val.LstStep = make([]WrGoodsPerformanceStep, 0)
						val.addStep(d2)
						sData = append(sData, val)
					}
				}
			}
		}
	}
	return sData, nil
}

// WRTradeBargainApply 议价单
type WRTradeBargainApply struct {
	WRBARGAINID    string   `json:"wrbargainid"  xorm:"WRBARGAINID" form:"wrbargainid"`          // 议价申请单ID(327+Unix秒时间戳(10位)+xxxxxx)
	WRTRADEORDERID string   `json:"wrtradeorderid"  xorm:"WRTRADEORDERID" form:"wrtradeorderid"` // 关联委托单ID
	TRADEDATE      string   `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`                // 交易日(yyyyMMdd)
	MARKETID       int64    `json:"marketid"  xorm:"MARKETID" form:"marketid"`                   // 市场ID
	USERID         int64    `json:"userid"  xorm:"USERID" form:"userid"`                         // 摘牌人用户ID(议价人ID)
	ACCOUNTID      int64    `json:"accountid"  xorm:"ACCOUNTID" form:"accountid"`                // 摘牌资金账号
	BUYORSELL      int32    `json:"buyorsell"  xorm:"BUYORSELL"`                                 // 买卖(挂牌类型) - 0:买 1:卖
	APPLYQTY       float64  `json:"applyqty"  xorm:"APPLYQTY"`                                   // 摘牌数量(议价数量)
	APPLYPRICE     string   `json:"applyprice"  xorm:"APPLYPRICE"`                               // 申请价格(议价价格)
	APPLYSTATUS    int32    `json:"applystatus"  xorm:"APPLYSTATUS"`                             // 申请状态(议价状态) - 1:待确认 2:已确认 3:已拒绝 4:已撤销 5:系统撤销 6:处理失败 7:确认中
	HANDLESTATUS   int32    `json:"handlestatus"  xorm:"HANDLESTATUS"`                           // 处理状态
	APPLYTIME      string   `json:"applytime"  xorm:"APPLYTIME"`                                 // 申请时间(议价时间)
	APPLYREMARK    string   `json:"applyremark"  xorm:"APPLYREMARK"`                             // 申请备注
	CONFIRMUSERID  int64    `json:"confirmuserid"  xorm:"CONFIRMUSERID" form:"confirmuserid"`    // 确认人
	CONFIRMTIME    string   `json:"confirmtime"  xorm:"CONFIRMTIME"`                             // 确认时间
	CONFIRMREMARK  string   `json:"confirmremark"  xorm:"CONFIRMREMARK"`                         // 确认备注
	REFPRICE       string   `json:"refprice"  xorm:"REFPRICE"`                                   // 参考价格
	RETCODE        int32    `json:"retcode"  xorm:"RETCODE"`                                     // 委托返回代码
	USERNAME       string   `json:"username"  xorm:"'USERNAME'"`                                 // 议价人名字(已脱敏)
	MATCHUSERID    int64    `json:"matchuserid"  xorm:"'MATCHUSERID'" form:"matchuserid"`        // 挂牌方用户id
	MATCHACCOUNTID int64    `json:"matchaccountid"  xorm:"'MATCHACCOUNTID'"`                     // 挂牌方资金账号
	MATCHUSERNAME  string   `json:"matchusername"  xorm:"'MATCHUSERNAME'"`                       // 挂牌方用户名称(已脱敏)
	ORDERQTY       int64    `json:"orderqty"  xorm:"'ORDERQTY'"`                                 // 挂牌数量
	FIXEDPRICE     SFLOAT64 `json:"fixedprice"  xorm:"'FIXEDPRICE'"`                             // 挂牌价格
	DELIVERYMONTH  string   `json:"deliverymonth"  xorm:"'DELIVERYMONTH'"`                       // 交收月
	MARGINVALUE    SFLOAT64 `json:"marginvalue"  xorm:"'MARGINVALUE'"`                           // 履约保证金比例
	VALIDTIME      string   `json:"validtime"  xorm:"'VALIDTIME'"`                               // 有效期限
	TEMPLATENAME   string   `json:"templatename"  xorm:"'TEMPLATENAME'"`                         // 履约方式(履约模板名称)
	Wr2FactorType  `xorm:"extends"`

	HASWR     int32  `json:"-" form:"haswr"`             // 0-仓单预售 1-仓单贸易
	BEGINDATE string `json:"begindate" form:"begindate"` // 开始交易日(yyyymmdd)
	ENDDATE   string `json:"enddate" form:"enddate"`     // 结束交易日(yyyymmdd)
}

func (r *WRTradeBargainApply) calc() {
	r.USERNAME = EncryptByStar(r.USERNAME)
	r.MATCHUSERNAME = EncryptByStar(r.MATCHUSERNAME)
}

func (r *WRTradeBargainApply) 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 to_char(t.WRBARGAINID) WRBARGAINID,
       to_char(t.WRTRADEORDERID) WRTRADEORDERID,
       t.TRADEDATE,
       t.MARKETID,
       t.USERID,
       t.ACCOUNTID,
       t.BUYORSELL,
       t.APPLYQTY,
       t.APPLYPRICE,
       t.APPLYSTATUS,
       t.HANDLESTATUS,
       to_char(t.APPLYTIME, 'yyyy-mm-dd hh24:mi:ss') APPLYTIME,
       t.APPLYREMARK,
       t.CONFIRMUSERID,
       to_char(t.CONFIRMTIME, 'yyyy-mm-dd hh24:mi:ss') CONFIRMTIME,
       t.CONFIRMREMARK,
       t.REFPRICE,
       t.RETCODE,
       t.matchuserid,
       t.matchaccountid,
       u.accountname USERNAME,
       u2.accountname matchusername,
       o.orderqty,
       o.fixedprice,
       o.deliverymonth,
       o.marginvalue,
       to_char(o.validtime, 'yyyy-mm-dd hh24:mi:ss') validtime,
       p.templatename,
       tmp.*
  FROM WRTRADE_BARGAINAPPLY t
  LEFT JOIN USERACCOUNT u on t.userid=u.userid
  LEFT JOIN USERACCOUNT u2 on t.matchuserid=u2.userid
  LEFT JOIN WRTRADE_ORDERDETAIL o on t.wrtradeorderid=o.wrtradeorderid
  LEFT JOIN tmp on o.wrfactortypeid=tmp.wrfactortypeid
  LEFT JOIN PERFORMANCEPLANTEMPLATE p on o.performancetemplateid=p.autoid
 WHERE 1 = 1 
`
	if r.USERID > 0 {
		if r.WRTRADEORDERID != "" {
			sqlId.And("t.userid", r.USERID)
			sqlId.AndInterEx("t.wrtradeorderid", r.WRTRADEORDERID, true)
		} else {
			sqlId.And("t.userid", r.USERID)
			sqlId.Join(" and t.APPLYTIME = (select max(APPLYTIME) from WRTRADE_BARGAINAPPLY where wrtradeorderid=t.wrtradeorderid)")
		}
	} else {
		sqlId.AndEx("t.WRTRADEORDERID", r.WRTRADEORDERID, r.WRTRADEORDERID != "")
	}

	sqlId.AndEx("t.matchuserid", r.MATCHUSERID, r.MATCHUSERID > 0)
	sqlId.And("o.haswr", r.HASWR)

	return sqlId.String()
}

// buildSqlHis 查历史
func (r *WRTradeBargainApply) buildSqlHis() 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 to_char(t.WRBARGAINID) WRBARGAINID,
       to_char(t.WRTRADEORDERID) WRTRADEORDERID,
       t.TRADEDATE,
       t.MARKETID,
       t.USERID,
       t.ACCOUNTID,
       t.BUYORSELL,
       t.APPLYQTY,
       t.APPLYPRICE,
       t.APPLYSTATUS,
       t.HANDLESTATUS,
       to_char(t.APPLYTIME, 'yyyy-mm-dd hh24:mi:ss') APPLYTIME,
       t.APPLYREMARK,
       t.CONFIRMUSERID,
       to_char(t.CONFIRMTIME, 'yyyy-mm-dd hh24:mi:ss') CONFIRMTIME,
       t.CONFIRMREMARK,
       t.REFPRICE,
       t.RETCODE,
       t.matchuserid,
       t.matchaccountid,
       u.accountname USERNAME,
       u2.accountname matchusername,
       o.orderqty,
       o.fixedprice,
       o.deliverymonth,
       o.marginvalue,
       to_char(o.validtime, 'yyyy-mm-dd hh24:mi:ss') validtime,
       p.templatename,
       tmp.*
  FROM HIS_WRTRADE_BARGAINAPPLY t
  LEFT JOIN USERACCOUNT u on t.userid=u.userid
  LEFT JOIN USERACCOUNT u2 on t.matchuserid=u2.userid
  LEFT JOIN HIS_WRTRADE_ORDERDETAIL o on t.wrtradeorderid=o.wrtradeorderid
  LEFT JOIN tmp on o.wrfactortypeid=tmp.wrfactortypeid
  LEFT JOIN PERFORMANCEPLANTEMPLATE p on o.performancetemplateid=p.autoid
 WHERE 1 = 1 and t.isvaliddata = 1
`
	sqlId.AndEx("t.userid", r.USERID, r.USERID > 0)
	sqlId.AndEx("t.matchuserid", r.MATCHUSERID, r.MATCHUSERID > 0)
	sqlId.AndEx("t.WRTRADEORDERID", r.WRTRADEORDERID, r.WRTRADEORDERID != "")
	sqlId.And("o.haswr", r.HASWR)

	// 格式 yyyymmdd
	if len(r.BEGINDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE >= '%v'", r.BEGINDATE)
	}

	if len(r.ENDDATE) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE <= '%v'", r.ENDDATE)
	}

	return sqlId.String()
}

// GetDataEx 获取议价单
func (r *WRTradeBargainApply) GetDataEx() (interface{}, error) {
	var sqlId string
	// 如果指定了开始日期或结束日期, 刚查历史
	if r.BEGINDATE != "" || r.ENDDATE != "" {
		sqlId = r.buildSqlHis()
	} else {
		sqlId = r.buildSql()
	}

	sData := make([]WRTradeBargainApply, 0)
	err := db.GetEngine().SQL(sqlId).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrPerformanceStepType 履约步骤枚举
type WrPerformanceStepType struct {
	STEPTYPEID   int32  `json:"steptypeid"  xorm:"STEPTYPEID" form:"steptypeid"` // 步骤类型ID - 1:买方支付 2:卖方收款 3:买方自提 4:卖方发货 5:买方确认货 6:卖方发票 7:买方确认票 8:仓单转移
	STEPTYPENAME string `json:"steptypename"  xorm:"STEPTYPENAME"`               // 步骤类型名称
	CANAUTO      int32  `json:"canauto"  xorm:"CANAUTO"`                         // 能否自动确认 -  0:不可自动确认 1:可自动确认  (可设置自动确认步骤: 1:买方支付 3:买方自提  5:买方确认货  7:买方确认票)
}

func (r *WrPerformanceStepType) calc() {

}

func (r *WrPerformanceStepType) buildSql() string {
	// id 90以上的不可选
	var sqlId utils.SQLVal = `
select t.* from PERFORMANCESTEPTYPE t where t.STEPTYPEID < 90 order by t.STEPTYPEID
`
	return sqlId.String()
}

// GetDataEx 获取履约步骤枚举
func (r *WrPerformanceStepType) GetDataEx() (interface{}, error) {
	sData := make([]WrPerformanceStepType, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrUserReceiptInfo 发票信息
type WrUserReceiptInfo struct {
	AUTOID         int64  `json:"autoid"  xorm:"AUTOID" form:"autoid"`                // AutoID
	USERID         int64  `json:"userid"  xorm:"USERID" form:"userid"`                // 用户ID
	RECEIPTTYPE    int32  `json:"receipttype"  xorm:"RECEIPTTYPE" form:"receipttype"` // 发票类型 - 1:个人 2:企业
	USERNAME       string `json:"username"  xorm:"USERNAME"`                          // 户名(个人姓名或企业名称)
	TAXPAYERID     string `json:"taxpayerid"  xorm:"TAXPAYERID" form:"taxpayerid"`    // 纳税人识别号
	CONTACTINFO    string `json:"contactinfo"  xorm:"CONTACTINFO"`                    // 联系方式
	RECEIPTBANK    string `json:"receiptbank"  xorm:"RECEIPTBANK"`                    // 发票开户行[发票类型:企业]
	RECEIPTACCOUNT string `json:"receiptaccount"  xorm:"RECEIPTACCOUNT"`              // 发票帐号[发票类型:企业]
	ADDRESS        string `json:"address"  xorm:"ADDRESS"`                            // 地址[发票类型:企业]
	IDNUM          string `json:"idnum"  xorm:"IDNUM"`                                // 身份证号码[发票类型:个人]
	ISDEFAULT      int32  `json:"isdefault"  xorm:"'ISDEFAULT'"`                      // 是否默认地址 - 0:否 1:是
}

func (r *WrUserReceiptInfo) calc() {

}

func (r *WrUserReceiptInfo) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT t.AUTOID,
       t.USERID,
       t.RECEIPTTYPE,
       t.USERNAME,
       t.TAXPAYERID,
       t.CONTACTINFO,
       t.RECEIPTBANK,
       t.RECEIPTACCOUNT,
       t.ADDRESS,
       t.IDNUM
  FROM USERRECEIPTINFO t
 WHERE 1 = 1
`
	sqlId.AndEx("t.USERID", r.USERID, r.USERID > 0)
	sqlId.AndEx("t.USERID", r.RECEIPTTYPE, r.RECEIPTTYPE > 0)
	return sqlId.String()
}

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

// WrSiteColumnDetail 资讯
type WrSiteColumnDetail struct {
	ID          int64            `json:"id"  xorm:"ID" form:"id"`                            // ID(SEQ_SITE_COLUMNDETAIL)
	COLUMNID    int64            `json:"-"  xorm:"COLUMNID" form:"columnid"`                 // 所属栏目
	USERID      int64            `json:"-"  xorm:"USERID" form:"userid"`                     // 用户ID(默认为0， 核心为为机构用户ID)
	TITLE       string           `json:"title"  xorm:"TITLE"`                                // 标题
	COVERIMAGE  string           `json:"coverimage"  xorm:"COVERIMAGE"`                      // 封面图片
	AUTHOR      string           `json:"author"  xorm:"AUTHOR"`                              // 作者
	BRIEF       string           `json:"brief"  xorm:"BRIEF"`                                // 简介
	CONTEXT     string           `json:"context"  xorm:"CONTEXT"`                            // 内容
	STATUS      int32            `json:"-"  xorm:"STATUS"`                                   // 状态 - 1:暂存 10:待审核 20:审核通过 30:审核拒绝
	ISTOP       int32            `json:"istop"  xorm:"ISTOP"`                                // 是否置顶 - 0:不置 1:置顶
	HITS        int64            `json:"-"  xorm:"HITS"`                                     // 点击数
	PUBLISHDATE string           `json:"publishdate"  xorm:"PUBLISHDATE" form:"publishdate"` // 发布日期
	CREAEDATE   string           `json:"creaedate"  xorm:"CREAEDATE" form:"creaedate"`       // 创建时间
	CREATORID   int64            `json:"-"  xorm:"CREATORID" form:"creatorid"`               // 创建人
	UPDATEDATE  string           `json:"updatedate"  xorm:"UPDATEDATE" form:"updatedate"`    // 修改时间
	MODIFIERID  int64            `json:"-"  xorm:"MODIFIERID" form:"modifierid"`             // 操作人
	ISSHOW      int32            `json:"-"  xorm:"ISSHOW"`                                   // 是否展示 - 0:不展示 1:展示
	PageEx      `xorm:"extends"` // 页码信息
}

func (r *WrSiteColumnDetail) calc() {

}

func (r *WrSiteColumnDetail) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT t.ID,
       t.COLUMNID,
       t.USERID,
       t.TITLE,
       t.COVERIMAGE,
       t.AUTHOR,
       t.BRIEF,
       t.CONTEXT,
       t.STATUS,
       t.ISTOP,
       t.HITS,
       to_char(t.PUBLISHDATE, 'yyyy-mm-dd hh24:mi:ss') PUBLISHDATE,
       to_char(t.CREAEDATE, 'yyyy-mm-dd hh24:mi:ss') CREAEDATE,
       t.CREATORID,
       to_char(t.UPDATEDATE, 'yyyy-mm-dd hh24:mi:ss') UPDATEDATE,
       t.MODIFIERID,
       t.ISSHOW
  FROM SITE_COLUMNDETAIL t
 WHERE 1 = 1 and t.isshow  = 1 and t.status = 20
 order by t.istop desc, t.updatedate desc
`
	sqlId.AndEx("t.COLUMNID", r.COLUMNID, r.COLUMNID > 0)
	sqlId.Page(r.Page, r.PageSize)
	return sqlId.String()
}

// GetDataByPage 获取资讯
func (r *WrSiteColumnDetail) GetDataByPage() (interface{}, error, int, int, int) {
	sData := make([]WrSiteColumnDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	if len(sData) > 0 {
		r.Total = sData[0].Total
	}
	return sData, err, r.Page, r.PageSize, r.Total
}

// WrDeliveryGoodsSection 走势 (分类)
type WrDeliveryGoodsSection struct {
	SECTIONID   int64  `json:"sectionid"  xorm:"SECTIONID" form:"sectionid"` // 版块ID (SEQ_DELIVERYGOODSSECTION)  - 自增100开始, 前面ID预留给SectionType = 1的版块1 - 云融指数
	SECTIONTYPE int32  `json:"sectiontype"  xorm:"SECTIONTYPE"`              // 版块类型 - 1:指数版块 2:普通版块
	SECTIONNAME string `json:"sectionname"  xorm:"SECTIONNAME"`              // 版块名称
	ORDERINDEX  int32  `json:"orderindex"  xorm:"ORDERINDEX"`                // 排序
	SHOWFLAG    int32  `json:"-"  xorm:"SHOWFLAG"`                           // 是否展示 - 0:不展示 1:展示
	CREATORSRC  int32  `json:"-"  xorm:"CREATORSRC"`                         // 申请来源 - 1:管理端 2:终端 3:交易
	CREATORID   int64  `json:"-"  xorm:"CREATORID" form:"creatorid"`         // 创建人
	CREATETIME  string `json:"-"  xorm:"CREATETIME"`                         // 创建时间
	IMAGEPATH   string `json:"imagepath"  xorm:"IMAGEPATH"`                  // 图片地址

	DGLst []WrDeliveryGoodsSectionConfig `json:"dglst"` // 板块商品列表
}

func (r *WrDeliveryGoodsSection) calc() {
	r.DGLst = make([]WrDeliveryGoodsSectionConfig, 0)
}

func (r *WrDeliveryGoodsSection) buildSql() string {
	var sqlId utils.SQLVal = `
select t.sectionid,
       t.sectiontype,
       t.sectionname,
       t.orderindex,
       t.showflag,
       t.creatorsrc,
       t.creatorid,
       to_char(t.createtime,'yyyy-mm-dd hh24:mi:ss') createtime,
       t.imagepath
  from DeliveryGoodsSection t
 where t.sectiontype = 2
   and t.showflag = 1
 order by t.orderindex
`
	return sqlId.String()
}

// GetDataEx 获取板块(分类)
func (r *WrDeliveryGoodsSection) GetDataEx() (interface{}, error) {
	sData := make([]WrDeliveryGoodsSection, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	// 获取商品
	m := WrDeliveryGoodsSectionConfig{}
	if d, err := m.GetDataEx(); err == nil {
		lst := d.([]WrDeliveryGoodsSectionConfig)
		for i := range sData {
			for _, v := range lst {
				if sData[i].SECTIONID == v.SECTIONID {
					sData[i].DGLst = append(sData[i].DGLst, v)
				}
			}
		}
	}
	return sData, err
}

// WrDeliveryGoodsSectionConfig 板块商品
type WrDeliveryGoodsSectionConfig struct {
	SECTIONID         int64  `json:"-"  xorm:"'SECTIONID'"`                         // 版块ID (SEQ_DELIVERYGOODSSECTION)
	DELIVERYGOODSID   int64  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种ID(SEQ_DELIVERYGOODS)
	ORDERINDEX        int32  `json:"orderindex"  xorm:"'ORDERINDEX'"`               // 排序
	CREATORSRC        int32  `json:"-"  xorm:"'CREATORSRC'"`                        // 申请来源 - 1:管理端 2:终端 3:交易
	CREATORID         int32  `json:"-"  xorm:"'CREATORID'"`                         // 创建人
	CREATEDATE        string `json:"-"  xorm:"'CREATEDATE'"`                        // 创建时间
	DELIVERYGOODSCODE string `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货商品代码
	DELIVERYGOODSNAME string `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` //	现货商品名称
}

func (r *WrDeliveryGoodsSectionConfig) calc() {

}

func (r *WrDeliveryGoodsSectionConfig) buildSql() string {
	var sqlId utils.SQLVal = `
select t.sectionid,
       t.deliverygoodsid,
       t.orderindex,
       t.creatorsrc,
       t.creatorid,
       to_char(t.createdate, 'yyyy-mm-dd hh24:mi:ss') createdate,
       g.deliverygoodscode,
       g.deliverygoodsname
  from DeliveryGoodsSectionConfig t
 inner join deliverygoods g
    on t.deliverygoodsid = g.deliverygoodsid
 inner join Deliverygoodssection s
    on t.sectionid = s.sectionid
 where 1 = 1
   and t.showflag = 1
   and s.showflag = 1
   and s.sectiontype = 2
 order by t.sectionid, t.orderindex
`
	return sqlId.String()
}

// GetDataEx 获取板块商品
func (r *WrDeliveryGoodsSectionConfig) GetDataEx() (interface{}, error) {
	sData := make([]WrDeliveryGoodsSectionConfig, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrSpotGroupTradeSum 指数
type WrSpotGroupTradeSum struct {
	RECKONDATE        string   `json:"reckondate"  xorm:"'RECKONDATE'"`               // 上个交易日(前1天交易日)
	LASTRECKONDATE    string   `json:"lastreckondate"  xorm:"'LASTRECKONDATE'"`       // 上上个交易日(前2天交易日)
	MARKETID          int64    `json:"-"  xorm:"'MARKETID'" form:"marketid"`          // 市场id
	TRADEQTY          float64  `json:"-"  xorm:"'TRADEQTY'"`                          // 成交量
	TRADEAMOUNT       float64  `json:"-"  xorm:"'TRADEAMOUNT'"`                       // 成交额
	LASTAVGPRICE      SFLOAT64 `json:"-"  xorm:"'LASTAVGPRICE'"`                      // 上上个交易日价格(前2天价格)
	TODAYAVGPRICE     SFLOAT64 `json:"-"  xorm:"'TODAYAVGPRICE'"`                     // 上个交易日价格(前1天价格)
	DELIVERYGOODSID   int64    `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种ID(SEQ_DELIVERYGOODS)
	DELIVERYGOODSCODE string   `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string   `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
	SECTIONID         int64    `json:"-"  xorm:"'SECTIONID'"`                         // 版块ID (SEQ_DELIVERYGOODSSECTION)
	PRETRADEDATE      string   `json:"-"  xorm:"'PRETRADEDATE'"`                      // 上个交易日(从marketid=0表中取的, 系统)

	LASTAVGPRICE2  string `json:"lastavgprice"`  // 上上个交易日价格(前2天价格)
	TODAYAVGPRICE2 string `json:"todayavgprice"` // 上个交易日价格(前1天价格)
	UPDOWN         string `json:"updown"`        // 涨跌
	UPDOWNRATIO    string `json:"updownratio"`   // 涨幅(已格式化, 直接显示即可)
}

func (r *WrSpotGroupTradeSum) calc() {
	if r.TODAYAVGPRICE < 1e-8 {
		// 如果今日为0, 则取上日的作为今日价
		r.TODAYAVGPRICE = r.LASTAVGPRICE
	}

	if r.LASTAVGPRICE < 1e-8 {
		r.LASTAVGPRICE2 = "-"
	} else {
		r.LASTAVGPRICE2 = r.LASTAVGPRICE.string()
	}

	if r.TODAYAVGPRICE < 1e-8 {
		r.TODAYAVGPRICE2 = "-"
	} else {
		r.TODAYAVGPRICE2 = r.TODAYAVGPRICE.string()
	}

	if r.TODAYAVGPRICE > 1e-8 && r.LASTAVGPRICE > 1e-8 {
		r.LASTAVGPRICE2 = r.LASTAVGPRICE.string()
		r.TODAYAVGPRICE2 = r.TODAYAVGPRICE.string()
		// 涨跌
		var diff SFLOAT64
		diff = r.TODAYAVGPRICE - r.LASTAVGPRICE
		r.UPDOWN = diff.Round(2).string()

		// 涨幅
		var k SFLOAT64
		k = diff * 100 / r.TODAYAVGPRICE
		k.Round(2)
		if k.Float64() > 1e-8 {
			r.UPDOWNRATIO = "+" + k.string() + `%`
		} else {
			r.UPDOWNRATIO = k.string() + `%`
		}
	} else {
		r.UPDOWN = "-"
		r.UPDOWNRATIO = "-"
	}
}

func (r *WrSpotGroupTradeSum) buildSql() string {
	var sqlId utils.SQLVal = `
with tmp as
 (select k.reckondate,
         k.marketid,
         k.tradeqty,
         k.tradeamount,
         k.lastreckondate,
         k.lastavgprice,
         k.todayavgprice,
         k.deliverygoodsid deliverygoodsid1
    from reckon_spotgrouptradesum k
   inner join marketrun m
      on k.reckondate = m.pretradedate
     and m.marketid = 0)
select tmp.*,
       t.sectionid,
       g.deliverygoodsid,
       g.deliverygoodscode,
       g.deliverygoodsname
  from DeliveryGoodsSectionConfig t
  left join tmp
    on t.deliverygoodsid = tmp.deliverygoodsid1
  left join deliverygoods g
    on t.deliverygoodsid = g.deliverygoodsid
 where t.sectionid = 1
   and t.showflag = 1
 order by t.orderindex
`
	return sqlId.String()
}

// GetDataEx 获取指数
func (r *WrSpotGroupTradeSum) GetDataEx() (interface{}, error) {
	sData := make([]WrSpotGroupTradeSum, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrSpotGroupTradeSumEx 指数(层级)
type WrSpotGroupTradeSumEx struct {
	RECKONDATE    string                `json:"reckondate"  xorm:"'RECKONDATE'"`       // 上个交易日(yyyymmdd)
	PRERECKONDATE string                `json:"prereckondate"  xorm:"'PRERECKONDATE'"` // 上上个交易日(yyymmdd)
	LST           []WrSpotGroupTradeSum `json:"lst"`                                   // 指数列表
}

func (r *WrSpotGroupTradeSumEx) calc() {
	r.LST = make([]WrSpotGroupTradeSum, 0)
}

func (r *WrSpotGroupTradeSumEx) buildSql() string {
	var sqlId utils.SQLVal = `
select t.pretradedate RECKONDATE,
        (select max(k.lastreckondate)
           from reckon_spotgrouptradesum k
          where k.reckondate = t.pretradedate) PRERECKONDATE
   from marketrun t
  where t.marketid = 0
`
	return sqlId.String()
}

// GetDataEx 获取指数(层级)
func (r *WrSpotGroupTradeSumEx) GetDataEx() (interface{}, error) {
	sData := make([]WrSpotGroupTradeSumEx, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	if err == nil && len(sData) > 0 {
		m := WrSpotGroupTradeSum{}
		if d, err := m.GetDataEx(); err == nil {
			sData[0].LST = d.([]WrSpotGroupTradeSum)
		}
	}

	return sData, err
}

// WrSpotGroupTradeSumDetail 折线图价格走势
type WrSpotGroupTradeSumDetail struct {
	RECKONDATE      string  `json:"reckondate"  xorm:"'RECKONDATE'"`                                  // 日期(yyymmdd, 如：20211031)
	DELIVERYGOODSID int64   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'" form:"deliverygoodsid"` // 现货商品id
	PRICE           float64 `json:"price"  xorm:"'PRICE'"`                                            // 价格

	LASTNUM int32 `json:"-" form:"lastnum"` // 最新多少条
}

func (r *WrSpotGroupTradeSumDetail) calc() {

}

func (r *WrSpotGroupTradeSumDetail) buildSql() string {
	var sqlId utils.SQLVal = `
select a.*
   from (select t.reckondate, t.deliverygoodsid, t.todayavgprice price, rownum cnt
           from reckon_spotgrouptradesum t
          where 1 = 1 and t.todayavgprice > 0
            and t.deliverygoodsid = %v
          order by t.reckondate desc) a
  where a.cnt <= %v
  order by a.reckondate
`
	if r.LASTNUM == 0 {
		r.LASTNUM = 30 // 如果没传值, 默认查最近30条
	}
	sqlId.FormatParam(r.DELIVERYGOODSID, r.LASTNUM)
	return sqlId.String()
}

// GetDataEx 获取折线图价格走势
func (r *WrSpotGroupTradeSumDetail) GetDataEx() (interface{}, error) {
	sData := make([]WrSpotGroupTradeSumDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrPreSaleInfo 新品发布
type WrPreSaleInfo struct {
	PRESALEAPPLYID     int64   `json:"presaleapplyid,string"  xorm:"PRESALEAPPLYID" form:"presaleapplyid"` // 预售申请ID(184+Unix秒时间戳(10位)+xxxxxx)
	SELLUSERID         int64   `json:"selluserid"  xorm:"SELLUSERID" form:"selluserid"`                    // 发行方用户ID
	SELLACCOUNTID      int64   `json:"sellaccountid"  xorm:"SELLACCOUNTID" form:"sellaccountid"`           // 发行方资金账户ID
	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
	WRFACTORTYPEID     int64   `json:"wrfactortypeid,string"  xorm:"WRFACTORTYPEID" form:"wrfactortypeid"` // 仓单要素类型ID - 根据现货商品\仓库生成
	WRSTANDARDNAME     string  `json:"wrstandardname"  xorm:"WRSTANDARDNAME"`                              // 现货商品名称
	UNITID             int32   `json:"unitid"  xorm:"UNITID" form:"unitid"`                                // 单位ID
	UNITPRICE          float64 `json:"unitprice"  xorm:"UNITPRICE"`                                        // 商品单价
	PRESALEQTY         float64 `json:"presaleqty"  xorm:"PRESALEQTY"`                                      // 预售总量
	BASEQTY            float64 `json:"baseqty"  xorm:"BASEQTY"`                                            // 中签基数
	MAXBUYQTY          float64 `json:"maxbuyqty"  xorm:"MAXBUYQTY"`                                        // 单人最大申购量
	MAXLUCKYQTY        float64 `json:"maxluckyqty"  xorm:"MAXLUCKYQTY"`                                    // 单人最大中签量
	STARTDATE          string  `json:"startdate"  xorm:"STARTDATE" form:"startdate"`                       // 预售开始日期
	ENDDATE            string  `json:"enddate"  xorm:"ENDDATE" form:"enddate"`                             // 预售结束日期
	TAKESTARTDATE      string  `json:"takestartdate"  xorm:"TAKESTARTDATE" form:"takestartdate"`           // 提货开始日期
	THUMURLS           string  `json:"thumurls"  xorm:"THUMURLS"`                                          // 缩略图片(1:1)(逗号分隔)
	PICTUREURLS        string  `json:"pictureurls"  xorm:"PICTUREURLS"`                                    // 详情图片(逗号分隔)
	BANNERPICURL       string  `json:"bannerpicurl"  xorm:"BANNERPICURL"`                                  // Banner图
	PRESALESTATUS      int32   `json:"presalestatus"  xorm:"PRESALESTATUS"`                                // 预售状态 - 1：未开始 2：进行中 3：已结束 4：已关闭
	LOTTERYFLAG        int32   `json:"lotteryflag"  xorm:"LOTTERYFLAG" form:"lotteryflag"`                 // 摇号标识 - 0:未摇号 1:已摇号
	LOTTERYQTY         float64 `json:"lotteryqty"  xorm:"LOTTERYQTY"`                                      // 摇号总量
	LUCKYQTY           float64 `json:"luckyqty"  xorm:"LUCKYQTY"`                                          // 已中签量
	PLACEQTY           float64 `json:"placeqty"  xorm:"PLACEQTY"`                                          // 已配售量
	MARKETID           int32   `json:"marketid"  xorm:"MARKETID" form:"marketid"`                          // 市场ID
	SELLWRTRADEORDERID string  `json:"sellwrtradeorderid"  xorm:"SELLWRTRADEORDERID"`                      // 发行方卖委托单ID
	CREATETIME         string  `json:"createtime"  xorm:"CREATETIME"`                                      // 创建时间
	TRADEDATE          string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`                       // 交易日
	WAREHOUSECODE      string  `json:"warehousecode"  xorm:"'WAREHOUSECODE'"`                              // 仓库代码(仓库简称)
	WAREHOUSENAME      string  `json:"warehousename"  xorm:"'WAREHOUSENAME'"`                              // 仓库名称
	USERNAME           string  `json:"username"  xorm:"'USERNAME'"`                                        // 发行方用户名称
	ENUMDICNAME        string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`                                  // 单位名称

	LastNum      int    `json:"-" form:"lastnum"`       // 最近多少条记录
	FilterStatus string `json:"-" form:"presalestatus"` // 预售状态(逗号隔开,如1,2,3)  1：未开始 2：进行中 3：已结束 4：已关闭
}

func (r *WrPreSaleInfo) calc() {

}

func (r *WrPreSaleInfo) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT to_char(t.PRESALEAPPLYID) PRESALEAPPLYID,
       t.SELLUSERID,
       t.SELLACCOUNTID,
       t.DELIVERYGOODSID,
       t.WAREHOUSEID,
       t.WRSTANDARDID,
       to_char(t.WRFACTORTYPEID) WRFACTORTYPEID,
       t.WRSTANDARDNAME,
       t.UNITID,
       t.UNITPRICE,
       t.PRESALEQTY,
       t.BASEQTY,
       t.MAXBUYQTY,
       t.MAXLUCKYQTY,
       to_char(t.STARTDATE, 'yyyy-mm-dd hh24:mi:ss') STARTDATE,
       to_char(t.ENDDATE, 'yyyy-mm-dd hh24:mi:ss') ENDDATE,
       to_char(t.TAKESTARTDATE, 'yyyy-mm-dd hh24:mi:ss') TAKESTARTDATE,
       t.THUMURLS,
       t.PICTUREURLS,
       t.BANNERPICURL,
       t.PRESALESTATUS,
       t.LOTTERYFLAG,
       t.LOTTERYQTY,
       t.LUCKYQTY,
       t.PLACEQTY,
       t.MARKETID,
       to_char(t.SELLWRTRADEORDERID) SELLWRTRADEORDERID,
       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME,
       t.TRADEDATE,
       h.warehousecode,
       h.warehousename,
       u.accountname username,
       e.enumdicname
  FROM WR_PRESALEINFO t
  LEFT JOIN WAREHOUSEINFO h on t.warehouseid=h.autoid
  LEFT JOIN USERACCOUNT u on t.selluserid=u.userid
  LEFT JOIN ENUMDICITEM e on t.unitid=e.enumitemname and e.enumdiccode='goodsunit'
 WHERE 1 = 1
`
	sqlId.AndEx("t.MARKETID", r.MARKETID, r.MARKETID > 0)
	sqlId.AndEx("t.LOTTERYFLAG", r.LOTTERYFLAG, r.LOTTERYFLAG >= 0)
	sqlId.JoinEx(r.FilterStatus != "", fmt.Sprintf(" and t.PRESALESTATUS in(%s)", r.FilterStatus))
	sqlId.Join(" order by t.createtime desc")

	if r.LastNum > 0 {
		sqlId.Page(1, r.LastNum)
	}

	return sqlId.String()
}

// GetDataEx 获取新品发布
func (r *WrPreSaleInfo) GetDataEx() (interface{}, error) {
	sData := make([]WrPreSaleInfo, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrReckonSpotGoodsTradeSum 价格走势
type WrReckonSpotGoodsTradeSum struct {
	RECKONDATE   string   `json:"reckondate"  xorm:"'RECKONDATE'"`                         // 日期 格式 yyyymmdd
	MARKETID     int      `json:"marketid"  xorm:"'MARKETID'" form:"marketid"`             // 市场ID
	WRSTANDARDID int      `json:"wrstandardid"  xorm:"'WRSTANDARDID'" form:"wrstandardid"` // 现货商品ID
	TRADEQTY     SFLOAT64 `json:"tradeqty"  xorm:"'TRADEQTY'"`                             // 日成交数量(当日单方向成交数量)
	TRADEAMOUNT  SFLOAT64 `json:"tradeamount"  xorm:"'TRADEAMOUNT'"`                       // 日成交金额(当日单方向成交金额)
	PRICE        string   `json:"price"`                                                   // 价格 = 日成交金额 / 日成交数量

	BEGINDATE string `json:"begindate" form:"begindate"` // 开始日期(结果含开始日期) yyyymmdd
	ENDDATE   string `json:"enddate" form:"enddate"`     // 结束日期(结果含结束日期) yyyymmdd
	LastNum   int    `json:"-" form:"lastnum"`           // 最近多少条记录

	ISTODAY bool `json:"istoday" form:"istoday"` // 是否当日
}

func (r *WrReckonSpotGoodsTradeSum) calc() {
	if r.TRADEQTY > 1e-10 {
		d := r.TRADEAMOUNT / r.TRADEQTY
		r.PRICE = d.Round(2).string()
	}
}

func (r *WrReckonSpotGoodsTradeSum) buildSql() string {
	var sqlId utils.SQLVal = `
select t.reckondate,
       t.marketid,
       t.wrstandardid,
       sum(t.tradeqty) tradeqty,
       sum(t.tradeamount) tradeamount
  from reckon_spotgoodstradesum t
 where 1 = 1 %v
 group by t.reckondate, t.marketid, t.wrstandardid
 order by t.reckondate desc
`
	var sqlParam utils.SQLVal
	sqlParam.And("t.marketid", r.MARKETID)
	sqlParam.And("t.wrstandardid", r.WRSTANDARDID)

	// 格式 yyyymmdd
	if len(r.BEGINDATE) > 0 {
		sqlParam.JoinFormat(" and t.reckondate >= '%v'", r.BEGINDATE)
	}

	if len(r.ENDDATE) > 0 {
		sqlParam.JoinFormat(" and t.reckondate <= '%v'", r.ENDDATE)
	}
	sqlId.FormatParam(sqlParam.String())
	sqlId.Page(1, r.LastNum)
	return sqlId.String()
}

// buildSqlByToday 今日走势数据
func (r *WrReckonSpotGoodsTradeSum) buildSqlByToday(today string) string {
	var sqlId utils.SQLVal = utils.SQLVal(fmt.Sprintf(`
	with tmp as 
	(select 
		to_char(t.TradeTime, 'HH24:MI:SS') RECKONDATE,
		to_char(t.TradeTime, 'yyyymmdd') TRADEDATE,
		t.marketid,
		w.wrstandardid,
		t.tradeqty,
		t.tradeamount
	from WRTrade_TradeDetail t
	left join WRFactorType w on w.WRFactorTypeID = t.WRFactorTypeID
	where t.marketid = %v and w.wrstandardid = %v 
	order by t.WRTradeDetailID desc)
	select distinct 
		t.RECKONDATE, 
		t.marketid, 
		t.wrstandardid,
		t.tradeqty,
		t.tradeamount
	from tmp t where t.TRADEDATE = '%v'
	`, r.MARKETID, r.WRSTANDARDID, today))

	sqlId.Page(1, r.LastNum)
	return sqlId.String()
}

// GetDataEx 获取价格走势
func (r *WrReckonSpotGoodsTradeSum) GetDataEx() (interface{}, error) {
	sql := r.buildSql()
	// 判断是否获取今日走势
	if r.MARKETID > 0 && r.ISTODAY {
		if marketRun, err := GetMarketRun(r.MARKETID); err == nil {
			sql = r.buildSqlByToday(marketRun.Tradedate)
		} else {
			return nil, errors.New("数据异常")
		}
	}

	sData := make([]WrReckonSpotGoodsTradeSum, 0)
	err := db.GetEngine().SQL(sql).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].BEGINDATE = r.BEGINDATE
		sData[i].ENDDATE = r.ENDDATE
		sData[i].ISTODAY = r.ISTODAY
	}
	return sData, err
}

// WrSearchUser 搜索用户
type WrSearchUser struct {
	USERNAME  string `json:"username"  xorm:"'USERNAME'" form:"username" binding:"required"` // 用户名称(已脱敏)
	USERID    int64  `json:"userid"  xorm:"'USERID'"`                                        // 用户ID
	USERNAME2 string `json:"username2"  xorm:"'-'"`                                          // 用户名称(未脱敏)
	USERTYPE  int    `json:"usertype"  xorm:"'USERTYPE'"`                                    // 账户类型 -  1:交易所 2:机构 3:会员子机构 4:经纪人 5:投资者 6:客户 7:企业成员(云平台)
}

func (r *WrSearchUser) calc() {
	r.USERNAME2 = r.USERNAME
	r.USERNAME = EncryptByStar(r.USERNAME)
}

func (r *WrSearchUser) buildSql() string {
	var sqlId utils.SQLVal = `
select t.userid, t.usertype, t.accountname username from useraccount t
where t.usertype in(2,6) and t.accountname like '%%%v%%'
`
	sqlId.FormatParam(r.USERNAME)
	return sqlId.String()
}

// GetDataEx 获取搜索用户
func (r *WrSearchUser) GetDataEx() (interface{}, error) {
	sData := make([]WrSearchUser, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// WrDraftUserInfo 实名认证信息
type WrDraftUserInfo struct {
	USERID                   int64   `json:"userid"  xorm:"USERID" form:"userid"`                       // 用户ID
	USERINFOTYPE             int32   `json:"userinfotype"  xorm:"USERINFOTYPE"`                         // 用户信息类型 - 1:个人  2:企业
	CUSTOMERNAME             string  `json:"customername"  xorm:"CUSTOMERNAME"`                         // 客户名称(企业名称)
	USERSTATUS               int32   `json:"userstatus"  xorm:"USERSTATUS" form:"userstatus"`           // 用户状态 - 1：正常 2：注销
	USERTYPE                 int32   `json:"usertype"  xorm:"USERTYPE"`                                 // 账户类型 -  1:交易所 2:机构 3:会员子机构 4:经纪人 5:投资者
	OPENMODE                 int32   `json:"openmode"  xorm:"OPENMODE"`                                 // 开户方式 -  1:管理端开户 2	:网上开户注册（会员官网） 3:微信开户 4:网页交易端注册 5:安卓手机端注册 6:苹果手机端注册 7:PC交易端注册
	CARDTYPEID               int32   `json:"cardtypeid"  xorm:"CARDTYPEID" form:"cardtypeid"`           // 证件类型ID
	CARDNUM                  string  `json:"cardnum"  xorm:"CARDNUM"`                                   // 证件号码(加密存储)
	HALFBODYPHOTOURL         string  `json:"halfbodyphotourl"  xorm:"HALFBODYPHOTOURL"`                 // 半身照地址
	CARDFRONTPHOTOURL        string  `json:"cardfrontphotourl"  xorm:"CARDFRONTPHOTOURL"`               // 证件正面图片地址
	CARDBACKPHOTOURL         string  `json:"cardbackphotourl"  xorm:"CARDBACKPHOTOURL"`                 // 证件背面图片地址
	BIZNATURE                int32   `json:"biznature"  xorm:"BIZNATURE"`                               // 企业性质( 企业) - 1:国有控股企业 2:集体控股企业 3:私人控股企业 4:港澳台商控股企业 5:外商控股企业 6:其它
	BIZSCOPE                 string  `json:"bizscope"  xorm:"BIZSCOPE"`                                 // 企业经营范围(企业)
	LEGALPERSONNAME          string  `json:"legalpersonname"  xorm:"LEGALPERSONNAME"`                   // 法人姓名(企业)
	CONTACTNAME              string  `json:"contactname"  xorm:"CONTACTNAME"`                           // 联系人
	SEX                      int32   `json:"sex"  xorm:"SEX"`                                           // 用户性别  0: 女  1: 男
	TELPHONE                 string  `json:"telphone"  xorm:"TELPHONE"`                                 // 联系电话(加密存储)
	MOBILE                   string  `json:"mobile"  xorm:"MOBILE"`                                     // 手机号码(加密存储)
	FAX                      string  `json:"fax"  xorm:"FAX"`                                           // 传真(加密存储)
	EMAIL                    string  `json:"email"  xorm:"EMAIL"`                                       // 邮件(加密存储)
	QQ                       string  `json:"qq"  xorm:"QQ"`                                             // QQ(加密存储
	WECHAT                   string  `json:"wechat"  xorm:"WECHAT"`                                     // 微信(加密存储)
	COUNTRYID                int32   `json:"countryid"  xorm:"COUNTRYID" form:"countryid"`              // 国家
	PROVINCEID               int32   `json:"provinceid"  xorm:"PROVINCEID" form:"provinceid"`           // 省
	CITYID                   int32   `json:"cityid"  xorm:"CITYID" form:"cityid"`                       // 市
	DISTRICTID               int32   `json:"districtid"  xorm:"DISTRICTID" form:"districtid"`           // 地区
	COMPANY                  string  `json:"company"  xorm:"COMPANY"`                                   // 公司(个人)
	ADDRESS                  string  `json:"address"  xorm:"ADDRESS"`                                   // 地址
	POSTALCODE               string  `json:"postalcode"  xorm:"POSTALCODE"`                             // 邮政编码
	CREATETIME               string  `json:"createtime"  xorm:"CREATETIME2"`                            // 创建时间
	CREATORID                int64   `json:"creatorid"  xorm:"CREATORID" form:"creatorid"`              // 创建人
	MODIFIERTIME             string  `json:"modifiertime"  xorm:"MODIFIERTIME2"`                        // 修改时间
	MODIFIERID               int64   `json:"modifierid"  xorm:"MODIFIERID" form:"modifierid"`           // 修改人
	REMARK                   string  `json:"remark"  xorm:"REMARK"`                                     // 备注
	WSKHINFO                 string  `json:"wskhinfo"  xorm:"WSKHINFO"`                                 // 开户申请信息(JSON)
	SIGNPDFURL               string  `json:"signpdfurl"  xorm:"SIGNPDFURL"`                             // 签约pdf文件
	LEGALCARDFRONTPHOTOURL   string  `json:"legalcardfrontphotourl"  xorm:"LEGALCARDFRONTPHOTOURL"`     // 法人身份证正面照地址
	LEGALCARDBACKPHOTOURL    string  `json:"legalcardbackphotourl"  xorm:"LEGALCARDBACKPHOTOURL"`       // 法人身份证背面照地址
	OTHERURL                 string  `json:"otherurl"  xorm:"OTHERURL"`                                 // 其它图片地址[使用分号分隔]
	HASENCRYPT               int32   `json:"hasencrypt"  xorm:"HASENCRYPT"`                             // 数据是否已加密 - 0:未加密 1:已加密
	BANKNAME                 string  `json:"bankname"  xorm:"BANKNAME"`                                 // 银行名称
	BANKACCOUNT              string  `json:"bankaccount"  xorm:"BANKACCOUNT"`                           // 银行帐号 (加密存储)
	BANKCARDFRONTPHOTOURL    string  `json:"bankcardfrontphotourl"  xorm:"BANKCARDFRONTPHOTOURL"`       // 银行卡正面照地址
	NEEDINVOICE              int32   `json:"needinvoice"  xorm:"NEEDINVOICE"`                           // 是否需要发票 - 0:不需要 1:需要
	BANKID                   string  `json:"bankid"  xorm:"BANKID" form:"bankid"`                       // 银行编码
	ATTACHMENT1              string  `json:"attachment1"  xorm:"ATTACHMENT1"`                           // 附件1
	ATTACHMENT2              string  `json:"attachment2"  xorm:"ATTACHMENT2"`                           // 附件2
	BANKACCOUNTNAME          string  `json:"bankaccountname"  xorm:"BANKACCOUNTNAME"`                   // 收款人名称
	HEADURL                  string  `json:"headurl"  xorm:"HEADURL"`                                   // 头像地址
	NICKNAME                 string  `json:"nickname"  xorm:"NICKNAME"`                                 // 昵称：默认为名称脱敏(张**) 或 手机号脱敏(139****9999)
	MOBILE2                  string  `json:"mobile2"  xorm:"MOBILE2"`                                   // 手机号码[明文-尚志]
	BIZTYPE                  int32   `json:"biztype"  xorm:"BIZTYPE"`                                   // 企业类型 - 1:进口/生产 2:销售 3:零售 4:运输 5:仓储
	PROXYSTATEMENTURL        string  `json:"proxystatementurl"  xorm:"PROXYSTATEMENTURL"`               // 授权委托书
	CONTACTCARDFRONTPHOTOURL string  `json:"contactcardfrontphotourl"  xorm:"CONTACTCARDFRONTPHOTOURL"` // 联系人证件正面图片地址
	CONTACTCARDBACKPHOTOURL  string  `json:"contactcardbackphotourl"  xorm:"CONTACTCARDBACKPHOTOURL"`   // 联系人证件背面图片地址
	ATTACHMENT3              string  `json:"attachment3"  xorm:"ATTACHMENT3"`                           // 附件3
	ATTACHMENT4              string  `json:"attachment4"  xorm:"ATTACHMENT4"`                           // 附件4
	ATTACHMENT5              string  `json:"attachment5"  xorm:"ATTACHMENT5"`                           // 附件5
	TAXPAYERNUM              string  `json:"taxpayernum"  xorm:"TAXPAYERNUM"`                           // 纳税人识别号
	BIRTHDAY                 string  `json:"birthday"  xorm:"BIRTHDAY2"`                                // 生日(个人:年月日)
	CREDITQUOTA              float64 `json:"creditquota"  xorm:"CREDITQUOTA"`                           // 授信额度(金瑞)
}

func (r *WrDraftUserInfo) calc() {
	r.CARDNUM = DecryptField(r.CARDNUM)
	r.TELPHONE = DecryptField(r.TELPHONE)       // 联系电话(加密存储)
	r.MOBILE = DecryptField(r.MOBILE)           // 手机号码(加密存储)
	r.FAX = DecryptField(r.FAX)                 // 传真(加密存储)
	r.EMAIL = DecryptField(r.EMAIL)             // 邮件(加密存储)
	r.QQ = DecryptField(r.QQ)                   // QQ(加密存储
	r.WECHAT = DecryptField(r.WECHAT)           // 微信(加密存储)
	r.BANKACCOUNT = DecryptField(r.BANKACCOUNT) // 银行帐号 (加密存储)
}

func (r *WrDraftUserInfo) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT to_char(t.BIRTHDAY, 'yyyy-mm-dd hh24:mi:ss') BIRTHDAY2,
       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME2,
       to_char(t.MODIFIERTIME, 'yyyy-mm-dd hh24:mi:ss') MODIFIERTIME2,
       t.*
  FROM DRAFT_USERINFO t
 WHERE 1 = 1
`
	sqlId.And("t.userid", r.USERID)
	sqlId.AndEx("t.userstatus", r.USERSTATUS, r.USERSTATUS != 0)
	return sqlId.String()
}

// GetDataEx 获取实名认证信息
func (r *WrDraftUserInfo) GetDataEx() (interface{}, error) {
	sData := make([]WrDraftUserInfo, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// DgFactoryItem 查询品牌和年份
type DgFactoryItem struct {
	DGFACTORYITEMID     int64  `json:"dgfactoryitemid"  xorm:"DGFACTORYITEMID"`         // 选择项ID(SEQ_DGFACTORYITEM)
	DGFACTORYITEMTYPEID int64  `json:"dgfactoryitemtypeid"  xorm:"DGFACTORYITEMTYPEID"` // 要素项类型
	DGFACTORYITEMVALUE  string `json:"dgfactoryitemvalue"  xorm:"DGFACTORYITEMVALUE"`   // 要素项值(类型为仓库时填写仓库名称)
	ORDERINDEX          int32  `json:"orderindex"  xorm:"ORDERINDEX"`                   // 顺序
}

func (r *DgFactoryItem) buildSql() string {
	var sqlId utils.SQLVal = `
select t.*
  from DGFactoryItem t
 where t.dgfactoryitemtypeid = %v
   and t.deliverygoodsid = 0
   and t.isvalid = 1
 order by t.orderindex
`
	sqlId.FormatParam(r.DGFACTORYITEMTYPEID)
	return sqlId.String()
}

// GetDataEx 获取查询品牌和年份
func (r *DgFactoryItem) GetDataEx() ([]DgFactoryItem, error) {
	sData := make([]DgFactoryItem, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	return sData, err
}

// WrBrandAndYears 查询品牌和年份
type WrBrandAndYears struct {
	BrandLst []DgFactoryItem `json:"brandlst"` // 品牌列表
	YearsLst []DgFactoryItem `json:"yearslst"` // 年份列表
}

// GetDataEx 获取查询品牌和年份
func (r *WrBrandAndYears) GetDataEx() (interface{}, error) {
	sData := make([]WrBrandAndYears, 0)
	v := DgFactoryItem{DGFACTORYITEMTYPEID: 100}
	brand, err := v.GetDataEx()
	if err != nil {
		return nil, err
	}
	v.DGFACTORYITEMTYPEID = 101
	years, err2 := v.GetDataEx()
	if err2 != nil {
		return nil, err
	}
	d := WrBrandAndYears{
		BrandLst: brand,
		YearsLst: years,
	}
	sData = append(sData, d)
	return sData, nil
}

// WrClientAdSpaceConfig 查询固定广告配置
type WrClientAdSpaceConfig struct {
	ADSPACEID   int32  `json:"adspaceid"  xorm:"ADSPACEID"`     // 广告位ID
	ADSPACETYPE int32  `json:"adspacetype"  xorm:"ADSPACETYPE"` // 广告位类型 - 1:固定广告位
	ADSPACENAME string `json:"adspacename"  xorm:"ADSPACENAME"` // 广告位名称
	RECSIZE     string `json:"recsize"  xorm:"RECSIZE"`         // 推荐尺寸
	IMAGEPATH   string `json:"imagepath"  xorm:"IMAGEPATH"`     // 图片
	TITLE       string `json:"title"  xorm:"TITLE"`             // 标题
	URLTYPE     int32  `json:"urltype"  xorm:"URLTYPE"`         // 链接类型 - 0:无 1:直接地址 2:商品ID 3:图片详情 4:App功能 5:微信小程序
	URLCONTENT  string `json:"urlcontent"  xorm:"URLCONTENT"`   // 链接(地址\商品ID\功能菜单Key\appid)
	CREATEDATE  string `json:"createdate"  xorm:"CREATEDATE"`   // 创建时间
	UPDATEDATE  string `json:"updatedate"  xorm:"UPDATEDATE"`   // 更新时间
	MODIFIERID  int64  `json:"modifierid"  xorm:"MODIFIERID"`   // 操作人
	AREAUSERID  int64  `json:"areauserid"  xorm:"AREAUSERID"`   // 所属机构ID
}

func (r *WrClientAdSpaceConfig) calc() {

}

func (r *WrClientAdSpaceConfig) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT t.ADSPACEID,
       t.ADSPACETYPE,
       t.ADSPACENAME,
       t.RECSIZE,
       t.IMAGEPATH,
       t.TITLE,
       t.URLTYPE,
       t.URLCONTENT,
       to_char(t.CREATEDATE, 'yyyy-mm-dd hh24:mi:ss') CREATEDATE,
       to_char(t.UPDATEDATE, 'yyyy-mm-dd hh24:mi:ss') UPDATEDATE,
       t.MODIFIERID,
       t.AREAUSERID
  FROM CLIENT_ADSPACECONFIG t
 WHERE t.adspacetype=1 
 order by t.updatedate desc
`
	return sqlId.String()
}

// GetDataEx 获取查询固定广告配置
func (r *WrClientAdSpaceConfig) GetDataEx() (interface{}, error) {
	sData := make([]WrClientAdSpaceConfig, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Xhcpsellbackapply 回收鉴别申请表
type Xhcpsellbackapply struct {
	SELLBACKAPPLYID string    `json:"sellbackapplyid"  xorm:"SELLBACKAPPLYID"`                // 回收申请ID - SEQ_XHCP_SELLBACKAPPLY
	PHONENUM        string    `json:"phonenum"  xorm:"PHONENUM"`                              // 联系电话
	GOODSSPEC       int32     `json:"goodsspec"  xorm:"GOODSSPEC"`                            // 商品规格 - 1:整件 2;整提 3:单罐 4:单盒
	EXPIRYTYPE      int32     `json:"expirytype"  xorm:"EXPIRYTYPE"`                          // 寄售有效期 - 1:3天 2:7天 3:15天 4:长期
	ISUNOPENED      int32     `json:"isunopened"  xorm:"ISUNOPENED"`                          // 是否原封 - 1:否 2:是
	ZPFLAG          int32     `json:"zpflag"  xorm:"ZPFLAG"`                                  // 皱皮类型[原封] - 1:无皱皮 2:小皱皮 3:皱皮
	MPFLAG          int32     `json:"mpflag"  xorm:"MPFLAG"`                                  // 磨皮类型[原封] - 1:无磨皮 2:小磨皮 3:磨皮
	HOLEFLAG        int32     `json:"holeflag"  xorm:"HOLEFLAG"`                              // 是否破洞[原封] - 1:无破洞 2:破洞
	OPENFLAG        int32     `json:"openflag"  xorm:"OPENFLAG"`                              // 是否非原封 - 1:无 2:皮张大、片张完整，无杂质、虫蛀、霉变、病变、烧皮。 3:片张较大、皮张基本完整，少量断片，少量病变、轻微虫蛀和烧皮。 4:皮张小、片张不完整，断片、碎片较多，有一定量杂质、病斑、虫蛀和烧皮。
	APPLYREMARK     string    `json:"applyremark"  xorm:"APPLYREMARK"`                        // 申请备注
	APPLYSRC        int32     `json:"applysrc"  xorm:"APPLYSRC"`                              // 申请来源 - 1:管理端 2:终端
	APPLYID         int64     `json:"applyid"  xorm:"APPLYID"`                                // 申请人
	APPLYTIME       time.Time `json:"applytime"  xorm:"APPLYTIME"`                            // 申请时间
	APPLYSTATUS     int32     `json:"applystatus"  xorm:"APPLYSTATUS"`                        // 申请状态 - 1:待确认 2:已处理
	AUDITREMARK     string    `json:"auditremark"  xorm:"AUDITREMARK"`                        // 审核备注
	AUDITSRC        int32     `json:"auditsrc"  xorm:"AUDITSRC"`                              // 审核来源 - 1:管理端 2:终端
	AUDITID         int64     `json:"-"  xorm:"AUDITID"`                                      // 审核人
	AUDITTIME       time.Time `json:"-"  xorm:"AUDITTIME"`                                    // 审核时间
	USERID          int64     `json:"userid"  xorm:"USERID" form:"userid" binding:"required"` // 所属用户ID
}

// TableName is XHCP_SELLBACKAPPLY
func (r *Xhcpsellbackapply) TableName() string {
	return "XHCP_SELLBACKAPPLY"
}

func (r *Xhcpsellbackapply) calc() {

}

func (r *Xhcpsellbackapply) buildSql() string {
	var sqlId utils.SQLVal = `
	SELECT 
		to_char(t.SELLBACKAPPLYID) SELLBACKAPPLYID,
       	t.PHONENUM,
       	t.GOODSSPEC,
       	t.EXPIRYTYPE,
       	t.ISUNOPENED,
       	t.ZPFLAG,
       	t.MPFLAG,
       	t.HOLEFLAG,
		t.OPENFLAG,
		t.APPLYREMARK,
		t.APPLYID,
		to_char(t.APPLYTIME, 'yyyy-mm-dd hh24:mi:ss') APPLYTIME,
		t.APPLYSTATUS,
		t.APPLYSTATUS,
		t.AUDITREMARK,
       	t.AUDITSRC,
       	t.AUDITID,
		t.USERID
	FROM XHCP_SELLBACKAPPLY t
	WHERE 1 = 1
	`
	sqlId.And("t.userid", r.USERID)

	return sqlId.String()
}

func (r *Xhcpsellbackapply) GetDataEx() (interface{}, error) {
	sData := make([]Xhcpsellbackapply, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// XhcpSellBackApplyEx 回收鉴别申请表
type XhcpSellBackApplyEx struct {
	SELLBACKAPPLYID int64  `json:"-"  xorm:"SELLBACKAPPLYID"`       // 回收申请ID - SEQ_XHCP_SELLBACKAPPLY
	PHONENUM        string `json:"phonenum"  xorm:"PHONENUM"`       // 联系电话
	GOODSSPEC       int32  `json:"goodsspec"  xorm:"GOODSSPEC"`     // 商品规格 - 1:整件 2;整提 3:单罐 4:单盒
	EXPIRYTYPE      int32  `json:"expirytype"  xorm:"EXPIRYTYPE"`   // 寄售有效期 - 1:3天 2:7天 3:15天 4:长期
	ISUNOPENED      int32  `json:"isunopened"  xorm:"ISUNOPENED"`   // 是否原封 - 1:否 2:是
	ZPFLAG          int32  `json:"zpflag"  xorm:"ZPFLAG"`           // 皱皮类型[原封] - 1:无皱皮 2:小皱皮 3:皱皮
	MPFLAG          int32  `json:"mpflag"  xorm:"MPFLAG"`           // 磨皮类型[原封] - 1:无磨皮 2:小磨皮 3:磨皮
	HOLEFLAG        int32  `json:"holeflag"  xorm:"HOLEFLAG"`       // 是否破洞[原封] - 1:无破洞 2:破洞
	OPENFLAG        int32  `json:"openflag"  xorm:"OPENFLAG"`       // 是否非原封 - 1:无 2:皮张大、片张完整，无杂质、虫蛀、霉变、病变、烧皮。 3:片张较大、皮张基本完整，少量断片，少量病变、轻微虫蛀和烧皮。 4:皮张小、片张不完整，断片、碎片较多，有一定量杂质、病斑、虫蛀和烧皮。
	APPLYREMARK     string `json:"applyremark"  xorm:"APPLYREMARK"` // 申请备注
	APPLYSRC        int32  `json:"-"  xorm:"APPLYSRC"`              // 申请来源 - 1:管理端 2:终端
	APPLYID         int64  `json:"-"  xorm:"APPLYID"`               // 申请人
	// APPLYTIME       time.Time `json:"-"  xorm:"APPLYTIME default(sysdate)"`                   // 申请时间
	APPLYSTATUS int32  `json:"-"  xorm:"APPLYSTATUS"`                                  // 申请状态 - 1:待确认 2:已处理
	AUDITREMARK string `json:"-"  xorm:"AUDITREMARK"`                                  // 审核备注
	AUDITSRC    int32  `json:"-"  xorm:"AUDITSRC"`                                     // 审核来源 - 1:管理端 2:终端
	USERID      int64  `json:"userid"  xorm:"USERID" form:"userid" binding:"required"` // 所属用户ID
}

// TableName is XHCP_SELLBACKAPPLY
func (r *XhcpSellBackApplyEx) TableName() string {
	return "XHCP_SELLBACKAPPLY"
}

func (r *XhcpSellBackApplyEx) Insert() error {
	engine := db.GetEngine()

	session := engine.NewSession()
	defer session.Close()

	// add Begin() before any action
	if err := session.Begin(); err != nil {
		return err
	}

	// 自增ID
	seqMap, err := session.QueryString("SELECT SEQ_XHCP_SELLBACKAPPLY.nextval SEQID FROM dual")
	if err != nil {
		return err
	}
	if len(seqMap) <= 0 {
		return errors.New("获取自增ID错误")
	}
	seqID, _ := strconv.Atoi(seqMap[0]["SEQID"])
	r.SELLBACKAPPLYID = int64(seqID)
	r.APPLYSRC = 2
	r.APPLYSTATUS = 1
	// 关于oracle的问题还是要处理的
	// r.APPLYTIME = time.Now()

	// 插入数据
	if _, err = session.Insert(r); err != nil {
		return err
	}

	// add Commit() after all actions
	return session.Commit()
}

// Wrtradetradequote 仓单贸易成交行情表
type Wrtradetradequote1 struct {
	WRTRADEDETAILID string    `json:"wrtradedetailid"  xorm:"WRTRADEDETAILID"`               // 仓单贸易成交单ID(321+Unix秒时间戳(10位)+xxxxxx)
	TRADEDATE       string    `json:"tradedate"  xorm:"TRADEDATE"`                           // 交易日(yyyyMMdd)
	MARKETID        int32     `json:"marketid"  xorm:"MARKETID"  form:"marketid"`            // 市场ID
	WRFACTORTYPEID  int64     `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID"`                 // 仓单要素类型ID
	DELIVERYGOODSID int32     `json:"deliverygoodsid"  xorm:"DELIVERYGOODSID"`               // 现货品种ID
	WRSTANDARDID    int32     `json:"wrstandardid"  xorm:"WRSTANDARDID" form:"wrstandardid"` // 现货商品ID
	BUYUSERID       int64     `json:"buyuserid"  xorm:"BUYUSERID"`                           // 买方用户ID
	BUYACCOUNTID    int64     `json:"buyaccountid"  xorm:"BUYACCOUNTID"`                     // 买方账号ID
	SELLUSERID      int64     `json:"selluserid"  xorm:"SELLUSERID"`                         // 卖方用户ID
	SELLACCOUNTID   int64     `json:"sellaccountid"  xorm:"SELLACCOUNTID"`                   // 卖方账号ID
	TRADETIME       time.Time `json:"tradetime"  xorm:"TRADETIME"`                           // 成交时间
	TRADEPRICE      float64   `json:"tradeprice"  xorm:"TRADEPRICE"`                         // 成交价格
	TRADEQTY        int64     `json:"tradeqty"  xorm:"TRADEQTY"`                             // 成交数量
	TRADEAMOUNT     float64   `json:"tradeamount"  xorm:"TRADEAMOUNT"`                       // 成交金额

	BEGINDATE string `json:"begindate" form:"begindate"` // 开始日期(结果含开始日期) yyyymmdd
	ENDDATE   string `json:"enddate" form:"enddate"`     // 结束日期(结果含结束日期) yyyymmdd
}

// TableName is WRTRADE_TRADEQUOTE
func (r *Wrtradetradequote1) TableName() string {
	return "WRTRADE_TRADEQUOTE"
}

func (r *Wrtradetradequote1) calc() {

}

func (r *Wrtradetradequote1) buildSql() string {
	var sqlId utils.SQLVal = `
	SELECT 
		to_char(t.WRTRADEDETAILID) WRTRADEDETAILID,
		t.TRADEDATE,
		t.MARKETID,
		t.WRFACTORTYPEID,
		t.DELIVERYGOODSID,
		t.WRSTANDARDID,
		t.BUYUSERID,
		t.BUYACCOUNTID,
		t.SELLUSERID,
		t.SELLACCOUNTID,
		t.TRADETIME,
		t.TRADEPRICE,
		t.TRADEQTY,
		t.TRADEAMOUNT
	FROM WRTRADE_TRADEQUOTE t
	WHERE 
		t.WRSTANDARDID = %v
	AND
		t.TRADETIME BETWEEN TO_DATE('%v' || ' 000000', 'yyyymmdd hh24:mi:ss') and TO_DATE('%v' || ' 235959', 'yyyymmdd hh24:mi:ss')
	`
	sqlId.FormatParam(r.WRSTANDARDID, r.BEGINDATE, r.ENDDATE)
	sqlId.AndEx("t.MARKETID", r.MARKETID, r.MARKETID > 0)

	return sqlId.String()
}

func (r *Wrtradetradequote1) GetDataEx() (interface{}, error) {
	sData := make([]Wrtradetradequote1, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}