MTP20_IF/models/ermcp3Report.go

/**
* @Author: zou.yingbin
* @Create  : 2021/4/16 18:22
* @Modify  : 2021/4/16 18:22
 */

package models

import (
	"fmt"
	"github.com/shopspring/decimal"
	"mtp2_if/db"
	"mtp2_if/mtpcache"
	"mtp2_if/utils"
	"strings"
)

// Ermcp3ReportOPLog 报表合同操作记录通用查询
type Ermcp3ReportOPLog struct {
	LOGID             string `json:"logid"  xorm:"'lOGID'"`                         // 流水ID(604+Unix秒时间戳(10位)+xxxxxx)
	BIZTYPE           int32  `json:"biztype"  xorm:"'BIZTYPE'"`                     // 业务类型 - 1:套保计划 2:现货合同
	OPERATELOGTYPE    int32  `json:"operatelogtype"  xorm:"'OPERATELOGTYPE'"`       // 操作流水类型 -
	RELATEDID         string `json:"relatedid"  xorm:"'RELATEDID'"`                 // 现货合同ID\套保计划
	LOGVALUE          string `json:"logvalue"  xorm:"'LOGVALUE'"`                   // 数值
	LOGDATETIME       string `json:"logdatetime"  xorm:"'LOGDATETIME'"`             // 流水日期(时间)
	TRADEDATE         string `json:"tradedate"  xorm:"'TRADEDATE'"`                 // 交易日(yyyyMMdd)
	APPLYID           int64  `json:"applyid"  xorm:"'APPLYID'"`                     // 操作人
	CONTRACTTYPE      int32  `json:"contracttype"  xorm:"'CONTRACTTYPE'"`           // 现货合同类型 - 1:采购 -1:销售
	USERID            int64  `json:"userid"  xorm:"'USERID'"`                       // 机构ID
	RELATEDNO         string `json:"relatedno"  xorm:"'RELATEDNO'"`                 // 合同编号
	UNITID            int32  `json:"-"  xorm:"'UNITID'"`                            // 现货商品单位id
	ENUMDICNAME       string `json:"enumdicName"`                                   // 单位名称
	OPTYPENAME        string `json:"optypename"`                                    // 流水类型名称
	LOGTYPENAME       string `json:"logtypename"`                                   // 合同类型(名称)
	APPLYNAME         string `json:"applyname"`                                     // 操作人名称
	BRANDNAME         string `json:"brandname"  xorm:"'brandname'"`                 // 品牌名称
	DELIVERYGOODSID   int32  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种id
	DELIVERYGOODSCODE string `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
	WRSTANDARDID      int32  `json:"wrstandardid"  xorm:"'wrstandardid'"`           // 品类id
	WRSTANDARDCODE    string `json:"wrstandardcode"  xorm:"'wrstandardcode'"`       // 品类代码
	WRSTANDARDNAME    string `json:"wrstandardname"  xorm:"'wrstandardname'"`       // 品类名称
	SPOTGOODSBRANDID  int32  `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`   // 品牌id
	LogTypeFilter     string `json:"-"`                                             // 查询日志类型, 逗号隔开(如 1,2,4)
}

// Calc 处理数据
func (r *Ermcp3ReportOPLog) Calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
	r.APPLYNAME = mtpcache.GetUserNameByUserId(r.APPLYID)
	if r.APPLYNAME == "" {
		// APPLYID 可能存的是loginId
		r.APPLYNAME = mtpcache.GetLoginCodeByLoginId(r.APPLYID)
	}

	//1:点价价格 2:点价数量 3:结算量 4:其它费用 5:追加保证金 6溢短金额 7:调整金额 8:付款金额 9:收款金额 10:退款金额
	//11:收票金额 12:开票金额 13:提交审核(合同) 14:审核通过(合同) 15:审核拒绝(合同) 16:合同撤回 17:提交审核(计划)
	//18:审核通过(计划) 19:审核拒绝(计划) 20:计划撤回 21:正常完结(合同) 22:异常终止(合同) 23:退还保证金
	//24:采购入库 25:销售出库 26:生产入库 27:生产出库
	// 数据库注释与返回值映身关系： 结算量->确定量, 收款->收款金额, 退款->退款金额, 付款->付款金额, 收票->收票金额, 开票->开票金额
	sDes := []string{"点价价格", "点价数量", "确定量", "其它费用", "追加保证金", "溢短金额", "调整金额", "付款", "收款", "退款",
		"收票", "开票", "提交审核(合同)", "审核通过(合同)", "审核拒绝(合同)", "合同撤回", "提交审核(计划)",
		"审核通过(计划)", "审核拒绝(计划)", "计划撤回", "正常完结(合同)", "异常终止(合同)", "退还保证金",
		"采购入库", "销售出库", "生产入库", "生产出库"}
	if r.OPERATELOGTYPE >= 1 && r.OPERATELOGTYPE <= 27 {
		r.OPTYPENAME = sDes[r.OPERATELOGTYPE-1]
	}

	// 收款款项:采购合同的退款、销售合同的收款应用正数显示，付款款项：采购合同的付款、销售合同的退款应用负数显示
	if r.CONTRACTTYPE == 1 {
		r.LOGTYPENAME = "采购"
		// 采购合同/退款 正数
		if r.OPERATELOGTYPE == 10 && len(r.LOGVALUE) > 0 {
			if r.LOGVALUE[0] == '-' {
				r.LOGVALUE = r.LOGVALUE[1:]
			}
		}
		// 采购合同/付款 负数
		if r.OPERATELOGTYPE == 8 && len(r.LOGVALUE) > 0 {
			if r.LOGVALUE[0] != '-' {
				r.LOGVALUE = "-" + r.LOGVALUE
			}
		}
	} else if r.CONTRACTTYPE == -1 {
		r.LOGTYPENAME = "销售"
		// 销售合同/收款 正数
		if r.OPERATELOGTYPE == 9 && len(r.LOGVALUE) > 0 {
			if r.LOGVALUE[0] == '-' {
				r.LOGVALUE = r.LOGVALUE[1:]
			}
		}
		// 销售合同/退款 负数
		if r.OPERATELOGTYPE == 10 && len(r.LOGVALUE) > 0 {
			if r.LOGVALUE[0] != '-' {
				r.LOGVALUE = "-" + r.LOGVALUE
			}
		}
	}

	// 去除多余的0,最后4个0
	if r.LOGVALUE[len(r.LOGVALUE)-4:] == "0000" {
		r.LOGVALUE = r.LOGVALUE[:len(r.LOGVALUE)-4]
	}

	if r.BIZTYPE == 1 {
		r.LOGTYPENAME += "计划"
	} else if r.BIZTYPE == 2 {
		r.LOGTYPENAME += "合同"
	}
}

func (r *Ermcp3ReportOPLog) buildSql() string {
	var sqlId utils.SQLVal = "SELECT to_char(t.LOGID) LOGID," +
		"       t.BIZTYPE," +
		"       t.OPERATELOGTYPE," +
		"       to_char(t.RELATEDID) RELATEDID," +
		"       t.LOGVALUE," +
		"       to_char(t.LOGDATETIME, 'yyyy-mm-dd hh24:mi:ss') LOGDATETIME," +
		"       t.TRADEDATE," +
		"       t.APPLYID," +
		"       t.CONTRACTTYPE," +
		"       t.USERID," +
		"       t.wrstandardid," +
		"       s.contractno relatedno," +
		"       s.spotgoodsbrandid," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       s.accountid," +
		"       w.wrstandardcode," +
		"       w.wrstandardname" +
		"  FROM ERMCP_CONTRACTOPERATELOG t" +
		" inner join ermcp_spotcontract s" +
		"    on t.RELATEDID = s.spotcontractid" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  left join wrstandard w on t.wrstandardid=w.wrstandardid" +
		"  left join dgfactoryitem gb" +
		"    on s.spotgoodsbrandid = gb.dgfactoryitemid" +
		" WHERE t.biztype = 2"
	// 筛选条件
	sqlId.And("t.userid", r.USERID)
	sqlId.And("t.TRADEDATE", r.TRADEDATE)
	sqlId.AndEx("t.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	sqlId.AndEx("t.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	sqlId.AndEx("s.spotgoodsbrandid", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0)
	if r.LogTypeFilter != "" {
		sqlId.Join(fmt.Sprintf(" and t.OPERATELOGTYPE in(%v)", r.LogTypeFilter))
	}
	return sqlId.String()
}

// GetDataEx 获取日志记录
func (r *Ermcp3ReportOPLog) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ReportOPLog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].Calc()
	}
	return sData, err
}

// Ermcp3ReckonAreaSpotSub 现货日报表(作废)
type Ermcp3ReckonAreaSpotSub struct {
	RECKONDATE            string  `json:"reckondate"  xorm:"'RECKONDATE'" form:"reckondate" binding:"required"` // 日照时期(yyyyMMdd)
	AREAUSERID            int64   `json:"areauserid"  xorm:"'AREAUSERID'" form:"userid" binding:"required"`     // 账户所属机构
	ACCOUNTID             int64   `json:"accountid"  xorm:"'ACCOUNTID'" form:"accountid"`                       // 期货账户ID
	WRFACTORTYPEID        int64   `json:"wrfactortypeid"  xorm:"'WRFACTORTYPEID'"`                              // 仓单要素类型ID(212+Unix秒时间戳(10位)+xxxxxx)
	DELIVERYGOODSID       int64   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'" form:"deliverygoodsid"`     // 现货品种ID
	WRSTANDARDID          int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'" form:"wrstandardid"`              // 现货品类ID
	SPOTGOODSBRANDID      int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'" form:"spotgoodsbrandid"`  // 现货品牌ID
	ORIBUYPRICEDQTY       float64 `json:"oribuypricedqty"  xorm:"'ORIBUYPRICEDQTY'"`                            // 期初采购合同已定价数量
	ORISELLPRICEDQTY      float64 `json:"orisellpricedqty"  xorm:"'ORISELLPRICEDQTY'"`                          // 期初销售合同已定价数量
	BUYPRICEDQTY          float64 `json:"buypricedqty"  xorm:"'BUYPRICEDQTY'"`                                  // 采购合同已定价数量
	SELLPRICEDQTY         float64 `json:"sellpricedqty"  xorm:"'SELLPRICEDQTY'"`                                // 销售合同已定价数量
	TOTALSPOTQTY          float64 `json:"totalspotqty"  xorm:"'TOTALSPOTQTY'"`                                  // 现货头寸总量 =  采购合同已定价数量 - 销售合同已定价数量
	UPDATETIME            string  `json:"updatetime"  xorm:"'UPDATETIME'"`                                      // 更新时间
	TODAYBUYRECKONQTY     float64 `json:"todaybuyreckonqty"  xorm:"'TODAYBUYRECKONQTY'"`                        // 今日采购合同已确定数量
	TODAYSELLRECKONQTY    float64 `json:"todaysellreckonqty"  xorm:"'TODAYSELLRECKONQTY'"`                      // 今日销售合同已确定数量
	ORIBUYPRICEDAMOUNT    float64 `json:"oribuypricedamount"  xorm:"'ORIBUYPRICEDAMOUNT'"`                      // 期初采购合同已定价金额
	ORISELLPRICEDAMOUNT   float64 `json:"orisellpricedamount"  xorm:"'ORISELLPRICEDAMOUNT'"`                    // 期初销售合同已定价金额
	BUYPRICEDAMOUNT       float64 `json:"buypricedamount"  xorm:"'BUYPRICEDAMOUNT'"`                            // 采购合同已定价金额
	SELLPRICEDAMOUNT      float64 `json:"sellpricedamount"  xorm:"'SELLPRICEDAMOUNT'"`                          // 销售合同已定价金额
	ORIBUYRECKONQTY       float64 `json:"oribuyreckonqty"  xorm:"'ORIBUYRECKONQTY'"`                            // 期初采购合同交收量
	ORISELLRECKONQTY      float64 `json:"orisellreckonqty"  xorm:"'ORISELLRECKONQTY'"`                          // 期初销售合同交收量
	ORIBUYCONTRACTINQTY   float64 `json:"oribuycontractinqty"  xorm:"'ORIBUYCONTRACTINQTY'"`                    // 期初采购合同入库量
	ORISELLCONTRACTOUTQTY float64 `json:"orisellcontractoutqty"  xorm:"'ORISELLCONTRACTOUTQTY'"`                // 期初销售合同出库量
	ORIPRODUCEINQTY       float64 `json:"oriproduceinqty"  xorm:"'ORIPRODUCEINQTY'"`                            // 期初生产入库量
	ORIPRODUCEOUTQTY      float64 `json:"oriproduceoutqty"  xorm:"'ORIPRODUCEOUTQTY'"`                          // 期初生产出库量
	BUYRECKONQTY          float64 `json:"buyreckonqty"  xorm:"'BUYRECKONQTY'"`                                  // 采购合同交收量
	SELLRECKONQTY         float64 `json:"sellreckonqty"  xorm:"'SELLRECKONQTY'"`                                // 销售合同交收量
	BUYCONTRACTINQTY      float64 `json:"buycontractinqty"  xorm:"'BUYCONTRACTINQTY'"`                          // 采购合同入库量
	SELLCONTRACTOUTQTY    float64 `json:"sellcontractoutqty"  xorm:"'SELLCONTRACTOUTQTY'"`                      // 销售合同出库量
	PRODUCEINQTY          float64 `json:"produceinqty"  xorm:"'PRODUCEINQTY'"`                                  // 生产入库量
	PRODUCEOUTQTY         float64 `json:"produceoutqty"  xorm:"'PRODUCEOUTQTY'"`                                // 生产出库量
	ENUMDICNAME           string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`                                    // 现货商品单位名称
	WRSTANDARDNAME        string  `json:"wrstandardname"  xorm:"'wrstandardname'"`                              // 品类名称
	WRSTANDARDCODE        string  `json:"wrstandardcode"  xorm:"'wrstandardcode'"`                              // 品类代码
	DELIVERYGOODSCODE     string  `json:"deliverygoodscode"  xorm:"'deliverygoodscode'"`                        // 现货商品代码
	DELIVERYGOODSNAME     string  `json:"deliverygoodsname"  xorm:"'deliverygoodsname'"`                        // 现货商品名称
	GOODSUNITID           int32   `json:"goodsunitid"  xorm:"'goodsunitid'"`                                    // 现货商品单位id
	BRANDNAME             string  `json:"brandname"  xorm:"'brandname'"`                                        // 品牌名称
	ACCOUNTNAME           string  `json:"accountname"  xorm:"'accountname'"`                                    // 所属机构名称
	BIZTYPE               int32   `json:"biztype"  xorm:"'BIZTYPE'"`                                            // 业务类型  1-套保 2-套利
	CURRENCYID            int32   `json:"currencyid"  xorm:"'CURRENCYID'"`                                      // 币种id

	CURRENCYNAME       string  `json:"currencyname"`       // 币种名称
	TOTALBUYPRICEDQTY  float64 `json:"totalbuypricedqty"`  // 采购定价量 = 期末 - 期初
	TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初
	BeginDate          string  `json:"-"`                  // 开始日期
	EndDate            string  `json:"-"`                  // 结束日期
}

func (r *Ermcp3ReckonAreaSpotSub) calc() {
	r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY
	r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
}

func (r *Ermcp3ReckonAreaSpotSub) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.ORIBUYRECKONQTY," +
		"       t.ORISELLRECKONQTY," +
		"       t.ORIBUYCONTRACTINQTY," +
		"       t.ORISELLCONTRACTOUTQTY," +
		"       t.ORIPRODUCEINQTY," +
		"       t.ORIPRODUCEOUTQTY," +
		"       t.BUYRECKONQTY," +
		"       t.SELLRECKONQTY," +
		"       t.BUYCONTRACTINQTY," +
		"       t.SELLCONTRACTOUTQTY," +
		"       t.PRODUCEINQTY," +
		"       t.PRODUCEOUTQTY," +
		"       t.Reckondate," +
		"       t.AREAUSERID," +
		"       t.ACCOUNTID," +
		"       t.WRFACTORTYPEID," +
		"       t.DELIVERYGOODSID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.ORIBUYPRICEDQTY," +
		"       t.ORISELLPRICEDQTY," +
		"       t.BUYPRICEDQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.TOTALSPOTQTY," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.TODAYBUYRECKONQTY," +
		"       t.TODAYSELLRECKONQTY," +
		"       t.ORIBUYPRICEDAMOUNT," +
		"       t.ORISELLPRICEDAMOUNT," +
		"       t.BUYPRICEDAMOUNT," +
		"       t.SELLPRICEDAMOUNT," +
		"       t.currencyid," +
		"       t.biztype," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid," +
		"       d.dgfactoryitemvalue brandname," +
		"       u.accountname," +
		"       e.enumdicname" +
		"  FROM RECKON_ERMCP_AREASPOTSUB t" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN DELIVERYGOODS g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  LEFT JOIN DGFACTORYITEM d" +
		"    on t.spotgoodsbrandid = d.dgfactoryitemid" +
		"  LEFT JOIN USERACCOUNT u" +
		"    on t.areauserid = u.userid" +
		"  LEFT JOIN ENUMDICITEM e" +
		"    on g.goodsunitid = e.enumitemname" +
		"   and e.enumdiccode = 'goodsunit'" +
		"   and e.enumitemstatus = 1" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	sqlId.AndEx("t.ACCOUNTID", r.ACCOUNTID, r.ACCOUNTID > 0)
	sqlId.AndEx("t.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	sqlId.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	sqlId.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0)

	if r.RECKONDATE != "" {
		sqlId.And("t.RECKONDATE", r.RECKONDATE)
	} else if r.BeginDate != "" && r.BeginDate == r.EndDate {
		sqlId.And("t.RECKONDATE", r.BeginDate)
	} else {
		if r.BeginDate != "" {
			sqlId.BiggerOrEq("t.RECKONDATE", r.BeginDate)
		}
		if r.EndDate != "" {
			sqlId.LessOrEq("t.RECKONDATE", r.EndDate)
		}
	}
	sqlId.Join(" order by t.RECKONDATE")

	return sqlId.String()
}

// GetDataEx 获取现货日报表
func (r *Ermcp3ReckonAreaSpotSub) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ReckonAreaSpotSub, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3ReportAreaSpotSub 现货分类月报表(作废)
type Ermcp3ReportAreaSpotSub struct {
	ORIBUYRECKONQTY       float64 `json:"oribuyreckonqty"  xorm:"'ORIBUYRECKONQTY'"`                           // 期初采购合同交收量【期初】
	ORISELLRECKONQTY      float64 `json:"orisellreckonqty"  xorm:"'ORISELLRECKONQTY'"`                         // 期初销售合同交收量【期初】
	ORIBUYCONTRACTINQTY   float64 `json:"oribuycontractinqty"  xorm:"'ORIBUYCONTRACTINQTY'"`                   // 期初采购合同入库量【期初】
	ORISELLCONTRACTOUTQTY float64 `json:"orisellcontractoutqty"  xorm:"'ORISELLCONTRACTOUTQTY'"`               // 期初销售合同出库量【期初】
	ORIPRODUCEINQTY       float64 `json:"oriproduceinqty"  xorm:"'ORIPRODUCEINQTY'"`                           // 期初生产入库量【期初】
	ORIPRODUCEOUTQTY      float64 `json:"oriproduceoutqty"  xorm:"'ORIPRODUCEOUTQTY'"`                         // 期初生产出库量【期初】
	BUYRECKONQTY          float64 `json:"buyreckonqty"  xorm:"'BUYRECKONQTY'"`                                 // 采购合同交收量【期末】
	SELLRECKONQTY         float64 `json:"sellreckonqty"  xorm:"'SELLRECKONQTY'"`                               // 销售合同交收量【期末】
	BUYCONTRACTINQTY      float64 `json:"buycontractinqty"  xorm:"'BUYCONTRACTINQTY'"`                         // 采购合同入库量【期末】
	SELLCONTRACTOUTQTY    float64 `json:"sellcontractoutqty"  xorm:"'SELLCONTRACTOUTQTY'"`                     // 销售合同出库量【期末】
	PRODUCEINQTY          float64 `json:"produceinqty"  xorm:"'PRODUCEINQTY'"`                                 // 生产入库量【期末】
	PRODUCEOUTQTY         float64 `json:"produceoutqty"  xorm:"'PRODUCEOUTQTY'"`                               // 生产出库量【期末】
	CYCLETIME             string  `json:"cycletime"  xorm:"'CYCLETIME'" form:"cycletime"`                      // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	CYCLETYPE             int32   `json:"cycletype"  xorm:"'CYCLETYPE'" form:"cycletype"`                      // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	AREAUSERID            int64   `json:"areauserid"  xorm:"'AREAUSERID'" form:"userid" binding:"required"`    // 账户所属机构
	ACCOUNTID             int64   `json:"accountid"  xorm:"'ACCOUNTID'" form:"accountid"`                      // 期货账户ID
	WRFACTORTYPEID        int64   `json:"wrfactortypeid"  xorm:"'WRFACTORTYPEID'"`                             // 仓单要素类型ID(212+Unix秒时间戳(10位)+xxxxxx)
	DELIVERYGOODSID       int64   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'" form:"deliverygoodsid"`    // 现货品种ID
	WRSTANDARDID          int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'" form:"wrstandardid"`             // 品类ID
	SPOTGOODSBRANDID      int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'" form:"spotgoodsbrandid"` // 现货品牌ID
	ORIBUYPRICEDQTY       float64 `json:"oribuypricedqty"  xorm:"'ORIBUYPRICEDQTY'"`                           // 期初采购合同已定价数量【期初】
	ORISELLPRICEDQTY      float64 `json:"orisellpricedqty"  xorm:"'ORISELLPRICEDQTY'"`                         // 期初销售合同已定价数量【期初】
	BUYPRICEDQTY          float64 `json:"buypricedqty"  xorm:"'BUYPRICEDQTY'"`                                 // 采购合同已定价数量【期末】
	SELLPRICEDQTY         float64 `json:"sellpricedqty"  xorm:"'SELLPRICEDQTY'"`                               // 销售合同已定价数量【期末】
	TOTALSPOTQTY          float64 `json:"totalspotqty"  xorm:"'TOTALSPOTQTY'"`                                 // 现货头寸总量 =  采购合同已定价数量 - 销售合同已定价数量【期末】
	UPDATETIME            string  `json:"updatetime"  xorm:"'UPDATETIME'"`                                     // 更新时间
	TODAYBUYRECKONQTY     float64 `json:"todaybuyreckonqty"  xorm:"'TODAYBUYRECKONQTY'"`                       // 今日采购合同已确定数量【汇总】
	TODAYSELLRECKONQTY    float64 `json:"todaysellreckonqty"  xorm:"'TODAYSELLRECKONQTY'"`                     // 今日销售合同已确定数量【汇总】
	ORIBUYPRICEDAMOUNT    float64 `json:"oribuypricedamount"  xorm:"'ORIBUYPRICEDAMOUNT'"`                     // 期初采购合同已定价金额【期初】
	ORISELLPRICEDAMOUNT   float64 `json:"orisellpricedamount"  xorm:"'ORISELLPRICEDAMOUNT'"`                   // 期初销售合同已定价金额【期初】
	BUYPRICEDAMOUNT       float64 `json:"buypricedamount"  xorm:"'BUYPRICEDAMOUNT'"`                           // 采购合同已定价金额【期末】
	SELLPRICEDAMOUNT      float64 `json:"sellpricedamount"  xorm:"'SELLPRICEDAMOUNT'"`                         // 销售合同已定价金额【期末】
	ENUMDICNAME           string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`                                   // 现货商品单位名称
	WRSTANDARDNAME        string  `json:"wrstandardname"  xorm:"'wrstandardname'"`                             // 品类名称
	WRSTANDARDCODE        string  `json:"wrstandardcode"  xorm:"'wrstandardcode'"`                             // 品类代码
	DELIVERYGOODSCODE     string  `json:"deliverygoodscode"  xorm:"'deliverygoodscode'"`                       // 现货商品代码
	DELIVERYGOODSNAME     string  `json:"deliverygoodsname"  xorm:"'deliverygoodsname'"`                       // 现货商品名称
	GOODSUNITID           int32   `json:"goodsunitid"  xorm:"'goodsunitid'"`                                   // 现货商品单位id
	BRANDNAME             string  `json:"brandname"  xorm:"'brandname'"`                                       // 品牌名称
	ACCOUNTNAME           string  `json:"accountname"  xorm:"'accountname'"`                                   // 所属机构名称
	BIZTYPE               int32   `json:"biztype"  xorm:"'BIZTYPE'"`                                           // 业务类型  1-套保 2-套利
	CURRENCYID            int32   `json:"currencyid"  xorm:"'CURRENCYID'"`                                     // 币种id

	CURRENCYNAME       string  `json:"currencyname"`       // 币种名称
	TOTALBUYPRICEDQTY  float64 `json:"totalbuypricedqty"`  // 采购定价量 = 期末 - 期初
	TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初
}

func (r *Ermcp3ReportAreaSpotSub) calc() {
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
	r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY
	r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY
}

func (r *Ermcp3ReportAreaSpotSub) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.ORIBUYRECKONQTY," +
		"       t.ORISELLRECKONQTY," +
		"       t.ORIBUYCONTRACTINQTY," +
		"       t.ORISELLCONTRACTOUTQTY," +
		"       t.ORIPRODUCEINQTY," +
		"       t.ORIPRODUCEOUTQTY," +
		"       t.BUYRECKONQTY," +
		"       t.SELLRECKONQTY," +
		"       t.BUYCONTRACTINQTY," +
		"       t.SELLCONTRACTOUTQTY," +
		"       t.PRODUCEINQTY," +
		"       t.PRODUCEOUTQTY," +
		"       t.CYCLETIME," +
		"       t.CYCLETYPE," +
		"       t.AREAUSERID," +
		"       t.ACCOUNTID," +
		"       t.WRFACTORTYPEID," +
		"       t.DELIVERYGOODSID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.ORIBUYPRICEDQTY," +
		"       t.ORISELLPRICEDQTY," +
		"       t.BUYPRICEDQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.TOTALSPOTQTY," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.TODAYBUYRECKONQTY," +
		"       t.TODAYSELLRECKONQTY," +
		"       t.ORIBUYPRICEDAMOUNT," +
		"       t.ORISELLPRICEDAMOUNT," +
		"       t.BUYPRICEDAMOUNT," +
		"       t.SELLPRICEDAMOUNT," +
		"       t.currencyid," +
		"       t.biztype," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid," +
		"       d.dgfactoryitemvalue brandname," +
		"       u.accountname," +
		"       e.enumdicname" +
		"  FROM REPORT_ERMCP_AREASPOTSUB t" +
		"  LEFT JOIN WRSTANDARD w on t.wrstandardid=w.wrstandardid" +
		"  LEFT JOIN DELIVERYGOODS g on t.deliverygoodsid=g.deliverygoodsid" +
		"  LEFT JOIN DGFACTORYITEM d on t.spotgoodsbrandid=d.dgfactoryitemid" +
		"  LEFT JOIN USERACCOUNT u on t.areauserid=u.userid" +
		"  LEFT JOIN ENUMDICITEM e on g.goodsunitid=e.enumitemname and e.enumdiccode='goodsunit' and e.enumitemstatus=1" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	sqlId.And("t.cycletype", r.CYCLETYPE)
	sqlId.And("t.cycletime", r.CYCLETIME)
	sqlId.AndEx("t.ACCOUNTID", r.ACCOUNTID, r.ACCOUNTID > 0)
	sqlId.AndEx("t.deliverygoodsid", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	sqlId.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	sqlId.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0)
	return sqlId.String()
}

// GetDataEx 获取现货分类报表
func (r *Ermcp3ReportAreaSpotSub) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ReportAreaSpotSub, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3ExpourseReport 敞口报表
type Ermcp3ExpourseReport struct {
	RECKONDATE            string  `json:"reckondate"  xorm:"'RECKONDATE'"`                       // 日照时期(yyyyMMdd)
	CYCLETIME             string  `json:"cycletime"  xorm:"'CYCLETIME'"`                         // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	CYCLETYPE             int32   `json:"cycletype"  xorm:"'CYCLETYPE'"`                         // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	ORITOTALSPOTQTY       float64 `json:"oritotalspotqty"  xorm:"'ORITOTALSPOTQTY'"`             // 期初现货头寸总量
	ORITOTALFUTUREQTY     float64 `json:"oritotalfutureqty"  xorm:"'ORITOTALFUTUREQTY'"`         // 期初期货头寸总量
	ORITOTALEXPOSURE      float64 `json:"oritotalexposure"  xorm:"'ORITOTALEXPOSURE'"`           // 期初实时总敞口
	ORINEEDHEDGEEXPOSOURE float64 `json:"orineedhedgeexposoure"  xorm:"'ORINEEDHEDGEEXPOSOURE'"` // 期初应套保敞口
	ORIHEDGEQTY           float64 `json:"orihedgeqty"  xorm:"'ORIHEDGEQTY'"`                     // 期初套保量
	ORIARBITRAGEQTY       float64 `json:"oriarbitrageqty"  xorm:"'ORIARBITRAGEQTY'"`             // 期初套利量
	ORINEEDHEDGEQTY       float64 `json:"orineedhedgeqty"  xorm:"'ORINEEDHEDGEQTY'"`             // 期初应套保量
	ORINEEDARBITRAGEQTY   float64 `json:"orineedarbitrageqty"  xorm:"'ORINEEDARBITRAGEQTY'"`     // 期初应套利量
	ORITOTALNEEDHEDGEQTY  float64 `json:"oritotalneedhedgeqty"  xorm:"'ORITOTALNEEDHEDGEQTY'"`   // 期初应套保总量
	MIDDLEGOODSID         int64   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`                 // 套保品种ID
	AREAUSERID            int64   `json:"areauserid"  xorm:"'AREAUSERID'"`                       // 所属机构\交易用户ID
	ACCOUNTID             int64   `json:"accountid"  xorm:"'ACCOUNTID'"`                         // 期货账户ID (作废, 默认为0)
	ORIBUYPLANQTY         float64 `json:"oribuyplanqty"  xorm:"'ORIBUYPLANQTY'"`                 // 期初采购计划数量
	ORIBUYPRICEDQTY       float64 `json:"oribuypricedqty"  xorm:"'ORIBUYPRICEDQTY'"`             // 期初采购合同已定价数量
	ORISELLPLANQTY        float64 `json:"orisellplanqty"  xorm:"'ORISELLPLANQTY'"`               // 期初销售计划数量
	ORISELLPRICEDQTY      float64 `json:"orisellpricedqty"  xorm:"'ORISELLPRICEDQTY'"`           // 期初销售合同已定价数量
	ORIBUYFUTUREQTY       float64 `json:"oribuyfutureqty"  xorm:"'ORIBUYFUTUREQTY'"`             // 期初买入期货数量
	ORISELLFUTUREQTY      float64 `json:"orisellfutureqty"  xorm:"'ORISELLFUTUREQTY'"`           // 期初卖出期货数量
	BUYPLANQTY            float64 `json:"buyplanqty"  xorm:"'BUYPLANQTY'"`                       // 采购计划数量
	BUYPRICEDQTY          float64 `json:"buypricedqty"  xorm:"'BUYPRICEDQTY'"`                   // 采购合同已定价数量
	SELLPLANQTY           float64 `json:"sellplanqty"  xorm:"'SELLPLANQTY'"`                     // 销售计划数量
	SELLPRICEDQTY         float64 `json:"sellpricedqty"  xorm:"'SELLPRICEDQTY'"`                 // 销售合同已定价数量
	BUYFUTUREQTY          float64 `json:"buyfutureqty"  xorm:"'BUYFUTUREQTY'"`                   // 买入期货数量
	SELLFUTUREQTY         float64 `json:"sellfutureqty"  xorm:"'SELLFUTUREQTY'"`                 // 卖出期货数量
	TOTALSPOTQTY          float64 `json:"totalspotqty"  xorm:"'TOTALSPOTQTY'"`                   // 现货头寸总量
	TOTALFUTUREQTY        float64 `json:"totalfutureqty"  xorm:"'TOTALFUTUREQTY'"`               // 期货头寸总量(期货总量)
	TOTALEXPOSURE         float64 `json:"totalexposure"  xorm:"'TOTALEXPOSURE'"`                 // 实时总敞口(总敞口)
	TOTALHEDGERATIO       float64 `json:"totalhedgeratio"  xorm:"'TOTALHEDGERATIO'"`             // 敞口比例
	TOTALNEEDHEDGEQTY     float64 `json:"totalneedhedgeqty"  xorm:"'TOTALNEEDHEDGEQTY'"`         // 应套保总量(现货应套保总量)
	NEEDHEDGEEXPOSOURE    float64 `json:"needhedgeexposoure"  xorm:"'NEEDHEDGEEXPOSOURE'"`       // 应套保敞口(套保敞口)
	NEEDHEDGERATIO        float64 `json:"needhedgeratio"  xorm:"'NEEDHEDGERATIO'"`               // 应套保敞口比例
	UPDATETIME            string  `json:"updatetime"  xorm:"'UPDATETIME'"`                       // 更新时间
	HEDGEQTY              float64 `json:"hedgeqty"  xorm:"'HEDGEQTY'"`                           // 套保量
	ARBITRAGEQTY          float64 `json:"arbitrageqty"  xorm:"'ARBITRAGEQTY'"`                   // 套利量
	NEEDHEDGEQTY          float64 `json:"needhedgeqty"  xorm:"'NEEDHEDGEQTY'"`                   // 应套保量
	NEEDARBITRAGEQTY      float64 `json:"needarbitrageqty"  xorm:"'NEEDARBITRAGEQTY'"`           // 应套利量
	MIDDLEGOODSNAME       string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`             // 套保商品名称
	MIDDLEGOODSCODE       string  `json:"middlgoodscode"  xorm:"'MIDDLEGOODSCODE'"`              // 套保商品代码
	UNITID                int32   `json:"unitid"  xorm:"'UNITID'"`                               // 单位id
	MGNEEDHEDGERATIO      float64 `json:"mgneedhedgeratio"  xorm:"'MGNEEDHEDGERATIO'"`           // 套保比例(套保品的)
	NEEDARBITRAGERATIO    float64 `json:"needarbitrageratio"  xorm:"'NEEDARBITRAGERATIO'"`       // 套利比例(套保品的)

	ENUMDICNAME string `json:"enumdicname"` // 单位名称
	ACCOUNTNAME string `json:"accountname"` // 机构名称

	DiffSpotQty  float64 `json:"diffspotqty"`  // 变动量(现货)
	DiffMgQtyA   float64 `json:"diffmgqtya"`   // 套保变动量
	DiffMgQtyB   float64 `json:"diffmgqtyb"`   // 套利变动量
	DiffFutuQty  float64 `json:"difffutuqty"`  // 变动量(期货)
	DiffQty      float64 `json:"diffqty"`      // 变动量(总敞口)
	DiffHedgeQty float64 `json:"diffhedgeqty"` // 变动量(现货应套保总量)
	DiffExposure float64 `json:"diffexposure"` // 变动量(套保敞口)

	QueryType int32  `json:"-"`         // 查询类型 1-报表
	BeginDate string `json:"begindate"` // 开始交易日
	EndDate   string `json:"enddate"`   // 结束交易日
}

func (r *Ermcp3ExpourseReport) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
	r.ACCOUNTNAME = mtpcache.GetUserNameByUserId(r.AREAUSERID)
	// 变动量(现货) = 现货头寸总量 - 期初现货头寸总量
	r.DiffSpotQty = r.TOTALSPOTQTY - r.ORITOTALSPOTQTY
	// 套保变动量 = 套保量 - 期初套保量
	r.DiffMgQtyA = r.HEDGEQTY - r.ORIHEDGEQTY
	// 套利变动量 = 套利量 - 期初套利量
	r.DiffMgQtyB = r.ARBITRAGEQTY - r.ORIARBITRAGEQTY
	// 变动量(期货) = (买入 - 买入期初) - (卖出 - 卖出期初)
	r.DiffFutuQty = (r.BUYFUTUREQTY - r.ORIBUYFUTUREQTY) - (r.SELLFUTUREQTY - r.ORISELLFUTUREQTY)
	// 变动量(敞口) = 实时敞口 - 期初实时敞口
	r.DiffQty = r.TOTALEXPOSURE - r.ORITOTALEXPOSURE
	// 变动量(现货应套保总量)
	r.DiffHedgeQty = r.TOTALNEEDHEDGEQTY - r.ORITOTALNEEDHEDGEQTY
	// 变动量(套保敞口)
	r.DiffExposure = r.NEEDHEDGEEXPOSOURE - r.ORINEEDHEDGEEXPOSOURE
}

func (r *Ermcp3ExpourseReport) buildSql() string {
	if r.CYCLETYPE == 0 {
		return r.buildSqlDay()
	}
	return r.buildSqlCycle()
}

func (r *Ermcp3ExpourseReport) buildSqlDay() string {
	var sqlId utils.SQLVal = "SELECT t.ORITOTALSPOTQTY," +
		"       t.ORITOTALFUTUREQTY," +
		"       t.ORITOTALEXPOSURE," +
		"       t.ORINEEDHEDGEEXPOSOURE," +
		"       t.ORIHEDGEQTY," +
		"       t.ORIARBITRAGEQTY," +
		"       t.ORINEEDHEDGEQTY," +
		"       t.ORINEEDARBITRAGEQTY," +
		"       t.ORITOTALNEEDHEDGEQTY," +
		"       t.RECKONDATE," +
		"       t.MIDDLEGOODSID," +
		"       t.AREAUSERID," +
		"       t.ACCOUNTID," +
		"       t.ORIBUYPLANQTY," +
		"       t.ORIBUYPRICEDQTY," +
		"       t.ORISELLPLANQTY," +
		"       t.ORISELLPRICEDQTY," +
		"       t.ORIBUYFUTUREQTY," +
		"       t.ORISELLFUTUREQTY," +
		"       t.BUYPLANQTY," +
		"       t.BUYPRICEDQTY," +
		"       t.SELLPLANQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.BUYFUTUREQTY," +
		"       t.SELLFUTUREQTY," +
		"       t.TOTALSPOTQTY," +
		"       t.TOTALFUTUREQTY," +
		"       t.TOTALEXPOSURE," +
		"       t.TOTALHEDGERATIO," +
		"       t.TOTALNEEDHEDGEQTY," +
		"       t.NEEDHEDGEEXPOSOURE," +
		"       t.NEEDHEDGERATIO," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.HEDGEQTY," +
		"       t.ARBITRAGEQTY," +
		"       t.NEEDHEDGEQTY," +
		"       t.NEEDARBITRAGEQTY," +
		"       g.middlegoodsname," +
		"       g.middlegoodscode," +
		"       g.goodsunitid unitid," +
		"       g.needhedgeratio MGNEEDHEDGERATIO," +
		"       g.needarbitrageratio" +
		"  FROM RECKON_ERMCP_AREAEXPOSURE t" +
		"  LEFT JOIN ERMS_MIDDLEGOODS g" +
		"    on t.middlegoodsid = g.middlegoodsid" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.AREAUSERID", r.AREAUSERID, r.AREAUSERID > 0)
	if r.QueryType == 1 {
		sqlId.And("t.RECKONDATE", r.RECKONDATE)
	} else if r.QueryType == 2 {
		sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	return sqlId.String()
}

func (r *Ermcp3ExpourseReport) buildSqlCycle() string {
	var sqlId utils.SQLVal = "SELECT t.cycletime," +
		"       t.cycletype," +
		"       t.ORITOTALSPOTQTY," +
		"       t.ORITOTALFUTUREQTY," +
		"       t.ORITOTALEXPOSURE," +
		"       t.ORINEEDHEDGEEXPOSOURE," +
		"       t.ORIHEDGEQTY," +
		"       t.ORIARBITRAGEQTY," +
		"       t.ORINEEDHEDGEQTY," +
		"       t.ORINEEDARBITRAGEQTY," +
		"       t.ORITOTALNEEDHEDGEQTY," +
		"       t.MIDDLEGOODSID," +
		"       t.AREAUSERID," +
		"       t.ACCOUNTID," +
		"       t.ORIBUYPLANQTY," +
		"       t.ORIBUYPRICEDQTY," +
		"       t.ORISELLPLANQTY," +
		"       t.ORISELLPRICEDQTY," +
		"       t.ORIBUYFUTUREQTY," +
		"       t.ORISELLFUTUREQTY," +
		"       t.BUYPLANQTY," +
		"       t.BUYPRICEDQTY," +
		"       t.SELLPLANQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.BUYFUTUREQTY," +
		"       t.SELLFUTUREQTY," +
		"       t.TOTALSPOTQTY," +
		"       t.TOTALFUTUREQTY," +
		"       t.TOTALEXPOSURE," +
		"       t.TOTALHEDGERATIO," +
		"       t.TOTALNEEDHEDGEQTY," +
		"       t.NEEDHEDGEEXPOSOURE," +
		"       t.NEEDHEDGERATIO," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.HEDGEQTY," +
		"       t.ARBITRAGEQTY," +
		"       t.NEEDHEDGEQTY," +
		"       t.NEEDARBITRAGEQTY," +
		"       g.middlegoodsname," +
		"       g.middlegoodscode," +
		"       g.goodsunitid unitid," +
		"       g.needhedgeratio MGNEEDHEDGERATIO," +
		"       g.needarbitrageratio" +
		"  FROM REPORT_ERMCP_AREAEXPOSURE t" +
		"  LEFT JOIN ERMS_MIDDLEGOODS g" +
		"    on t.middlegoodsid = g.middlegoodsid" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	sqlId.And("t.CYCLETYPE", r.CYCLETYPE)
	sqlId.And("t.CYCLETIME", r.CYCLETIME)
	return sqlId.String()
}

// GetDataEx 获取敞报表
func (r *Ermcp3ExpourseReport) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ExpourseReport, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].BeginDate = r.BeginDate
		sData[i].EndDate = r.EndDate
	}
	return sData, err
}

// Ermcp3ExposureContractDetail 敞口合同明细
type Ermcp3ExposureContractDetail struct {
	MIDDLEGOODSID      int32   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`           // 套保品种id
	DELIVERYGOODSID    int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`       // 现货商品id
	CONVERTRATIO       float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"`             // 折算系数(套保品种)
	USERID             int64   `json:"userid"  xorm:"'USERID'"`                         // 机构id
	SPOTCONTRACTID     string  `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'"`         // 合同id
	CONTRACTNO         string  `json:"contractno"  xorm:"'CONTRACTNO'"`                 // 合同编号
	CONTRACTTYPE       int32   `json:"contracttype"  xorm:"'CONTRACTTYPE'"`             // 现货合同类型 - 1:采购 -1:销售
	BIZTYPE            int32   `json:"biztype"  xorm:"'BIZTYPE'"`                       // 业务类型 1-套保 2-套利
	BUYUSERID          int64   `json:"buyuserid"  xorm:"'BUYUSERID'"`                   // 采购方id
	SELLUSERID         int64   `json:"selluserid"  xorm:"'SELLUSERID'"`                 // 销售方id
	QTY                float64 `json:"qty"  xorm:"'QTY'"`                               // 合同量
	PRICEDQTY          float64 `json:"pricedqty"  xorm:"'PRICEDQTY'"`                   // 已定价量
	WRSTANDARDID       int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`             // 品类id
	WRSTANDARDNAME     string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`         // 品类名称
	WRSTANDARDCODE     string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`         // 品类代码
	UNITID             int32   `json:"unitid"  xorm:"'UNITID'"`                         // 品类单位id
	MIDDLEGOODSNAME    string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`       // 套保品种名称
	MIDDLEGOODSCODE    string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"`       // 套保品种代码
	GOODSUNITID        int32   `json:"-"  xorm:"'GOODSUNITID'"`                         // 套保品种单位id
	TRADEDATE          string  `json:"tradedate"  xorm:"'tradedate'"`                   // 交易日
	NEEDHEDGERATIO     float64 `json:"needhedgeratio"  xorm:"'NEEDHEDGERATIO'"`         // 应套保比例
	NEEDARBITRAGERATIO float64 `json:"needarbitrageratio"  xorm:"'NEEDARBITRAGERATIO'"` // 应套利比例
	BRANDNAME          string  `json:"brandname"  xorm:"'BRANDNAME'"`                   // 品牌名称
	DELIVERYGOODSCODE  string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`   // 现货商品代码
	DELIVERYGOODSNAME  string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`   // 现货商品名称
	CONVERTFACTOR      float64 `json:"convertfactor"  xorm:"'CONVERTFACTOR'"`           // 标仓系数
	TODAYPRICEDQTY     float64 `json:"todaypricedqty"  xorm:"'TODAYPRICEDQTY'"`         // 今定价量

	EnumdicName      string  `json:"enumdicname"`      // 单位名称
	BUYUSERNAME      string  `json:"buyusername"`      // 采购方名称
	SELLUSERNAME     string  `json:"sellusername"`     // 销售方名称
	CurQty           float64 `json:"curqty"`           // 今定价量
	DiffQty          float64 `json:"diffqty"`          // 套保品种今变动量
	DiffHedgeQty     float64 `json:"diffhedgeqty"`     // 套保变动量
	DiffNeedHedgeQty float64 `json:"diffneedhedgeqty"` // 应套保变动量

	BeginDate string `json:"-"` // 开始交易日
	EndDate   string `json:"-"` // 结束交易日
}

func (r *Ermcp3ExposureContractDetail) calc() {
	r.CurQty = r.PRICEDQTY
	r.DiffQty = r.QTY * r.CONVERTRATIO
	r.DiffHedgeQty = r.TODAYPRICEDQTY * r.CONVERTRATIO * r.CONVERTFACTOR
	if r.BIZTYPE == 1 {
		r.DiffNeedHedgeQty = r.TODAYPRICEDQTY * r.CONVERTRATIO * r.CONVERTFACTOR * r.NEEDHEDGERATIO
	} else {
		r.DiffNeedHedgeQty = r.TODAYPRICEDQTY * r.CONVERTRATIO * r.CONVERTFACTOR * r.NEEDARBITRAGERATIO
	}
	r.EnumdicName = mtpcache.GetEnumDicitemName(r.UNITID)
	r.BUYUSERNAME = mtpcache.GetUserNameByUserId(r.BUYUSERID)
	r.SELLUSERNAME = mtpcache.GetUserNameByUserId(r.SELLUSERID)
}

func (r *Ermcp3ExposureContractDetail) buildSql() string {
	var sqlId utils.SQLVal = "select t.middlegoodsid," +
		"       t.deliverygoodsid," +
		"       t.convertratio," +
		"       s.userid," +
		"       to_char(s.spotcontractid) spotcontractid," +
		"       s.contractno," +
		"       s.contracttype," +
		"       s.biztype," +
		"       s.buyuserid," +
		"       s.selluserid," +
		"       s.qty," +
		"       s.pricedqty," +
		"       s.wrstandardid," +
		"       s.reckondate tradedate," +
		"       s.todaypricedqty," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       w.convertfactor," +
		"       mg.middlegoodsname," +
		"       mg.middlegoodscode," +
		"       mg.goodsunitid," +
		"       mg.needhedgeratio," +
		"       mg.needarbitrageratio," +
		"       dg.dgfactoryitemid brandid," +
		"       dg.dgfactoryitemvalue brandName," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname" +
		"  from erms2_wrsconvertdetail t" +
		" inner join reckon_ermcp_spotcontract s" +
		"    on t.deliverygoodsid = s.deliverygoodsid" +
		"  left join erms_middlegoods mg" +
		"    on t.middlegoodsid = mg.middlegoodsid" +
		"  left join wrstandard w" +
		"    on s.wrstandardid = w.wrstandardid" +
		"  left join dgfactoryitem dg" +
		"    on s.spotgoodsbrandid = dg.dgfactoryitemid" +
		"  left join deliverygoods g" +
		"    on s.deliverygoodsid = g.deliverygoodsid" +
		" where t.wrstandardid = 0" +
		"   and s.contractstatus in (2, 3)"
	sqlId.AndEx("s.reckondate", r.TRADEDATE, len(r.TRADEDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and s.reckondate >= '%v' and s.reckondate <= '%v'", r.BeginDate, r.EndDate))
	}
	sqlId.And("t.middlegoodsid", r.MIDDLEGOODSID)
	sqlId.Join(fmt.Sprintf(" and %v in(s.userid, s.tradeuserid)", r.USERID))
	return sqlId.String()
}

// GetDataEx 获取敞口合同明细
func (r *Ermcp3ExposureContractDetail) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ExposureContractDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3ExposureHedgeplanDetail 敞口套保计划明细
type Ermcp3ExposureHedgeplanDetail struct {
	MIDDLEGOODSID      int32   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`           // 套保品种id
	DELIVERYGOODSID    int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`       // 现货商品id
	CONVERTRATIO       float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"`             // 折算系数(套保品种)
	USERID             int64   `json:"userid"  xorm:"'USERID'"`                         // 机构id
	HEDGEPLANID        string  `json:"hedgeplanid"  xorm:"'HEDGEPLANID'"`               // 套保计划id
	HEDGEPLANNO        string  `json:"hedgeplanno"  xorm:"'HEDGEPLANNO'"`               // 套保计划名称
	BIZTYPE            int32   `json:"biztype"  xorm:"'BIZTYPE'"`                       // 业务类型(期现用途,业务类型就是期现用途) 1-套保 2-套利
	QTY                float64 `json:"qty"  xorm:"'QTY'"`                               // 合同量
	PRICEDQTY          float64 `json:"pricedqty"  xorm:"'PRICEDQTY'"`                   // 已定价量
	WRSTANDARDID       int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`             // 品类id
	WRSTANDARDNAME     string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`         // 品类名称
	WRSTANDARDCODE     string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`         // 品类代码
	UNITID             int32   `json:"unitid"  xorm:"'UNITID'"`                         // 品类单位id
	MIDDLEGOODSNAME    string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`       // 套保品种名称
	MIDDLEGOODSCODE    string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"`       // 套保品种代码
	GOODSUNITID        int32   `json:"-"  xorm:"'GOODSUNITID'"`                         // 套保品种单位id
	TRADEDATE          string  `json:"tradedate"  xorm:"'tradedate'"`                   // 交易日(登记日)
	NEEDHEDGERATIO     float64 `json:"needhedgeratio"  xorm:"'NEEDHEDGERATIO'"`         // 应套保比例
	NEEDARBITRAGERATIO float64 `json:"needarbitrageratio"  xorm:"'NEEDARBITRAGERATIO'"` // 应套利比例
	CONTRACTTYPE       int32   `json:"contracttype"  xorm:"'CONTRACTTYPE'"`             // 计划类型 - 1:采购 -1:销售
	HEDGEPLANSTATUS    int32   `json:"hedgeplanstatus"  xorm:"'HEDGEPLANSTATUS'"`       // 套保计划状态 -  0:未提交 1:待审核 2:执行中 3:正常完结 4:审核拒绝 5:异常完结 6:已撤回
	DELIVERYGOODSCODE  string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`   // 现货商品代码
	DELIVERYGOODSNAME  string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`   // 现货商吕名称
	DGUNITID           int32   `json:"dgunitid"  xorm:"'DGUNITID'"`                     // 现货商品单位id

	EnumdicName  string  `json:"enumdicname"`  // 单位名称(品类)
	MGUNITIDNAME string  `json:"mgunitidname"` // 单位名称(套保商品)
	DGUNITIDNAME string  `json:"dgunitidname"` // 单位名称(现货商品)
	CurQty       float64 `json:"-"`            // 今定价量
	DiffQty      float64 `json:"diffqty"`      // 套保品种今变动量
	ChangQty     float64 `json:"changqty"`     // 应套保总量变化量

	BeginDate string `json:"-"`
	EndDate   string `json:"-"`
}

func (r *Ermcp3ExposureHedgeplanDetail) calc() {
	r.EnumdicName = mtpcache.GetEnumDicitemName(r.UNITID)
	r.MGUNITIDNAME = mtpcache.GetEnumDicitemName(r.GOODSUNITID)
	r.DGUNITIDNAME = mtpcache.GetEnumDicitemName(r.DGUNITID)
	r.CurQty = r.QTY
	r.DiffQty = r.QTY * r.CONVERTRATIO
	r.ChangQty = r.QTY * r.NEEDHEDGERATIO * r.CONVERTRATIO
}

func (r *Ermcp3ExposureHedgeplanDetail) buildSql() string {
	var sqlId utils.SQLVal = "select t.middlegoodsid," +
		"       t.deliverygoodsid," +
		"       t.convertratio," +
		"       s.areauserid userid," +
		"       to_char(s.hedgeplanid) hedgeplanid," +
		"       s.hedgeplanno," +
		"       s.contracttype," +
		"       s.biztype," +
		"       s.planqty qty," +
		"       s.wrstandardid," +
		"       s.audittradedate tradedate," +
		"       s.hedgeplanstatus," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid," +
		"       mg.middlegoodsname," +
		"       mg.middlegoodscode," +
		"       mg.goodsunitid," +
		"       mg.needhedgeratio," +
		"       mg.needarbitrageratio" +
		"  from erms2_wrsconvertdetail t" +
		" inner join ermcp_hedgeplan s" +
		"    on t.deliverygoodsid = s.deliverygoodsid" +
		"  left join erms_middlegoods mg" +
		"    on t.middlegoodsid = mg.middlegoodsid" +
		"  left join wrstandard w" +
		"    on s.wrstandardid = w.wrstandardid" +
		"  left join deliverygoods g on s.deliverygoodsid=g.deliverygoodsid" +
		" where t.wrstandardid = 0" +
		"   and s.hedgeplanstatus in (2, 3)"
	sqlId.AndEx("s.audittradedate", r.TRADEDATE, len(r.TRADEDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and s.audittradedate >= '%v' and s.audittradedate <= '%v'", r.BeginDate, r.EndDate))
	}
	sqlId.And("t.middlegoodsid", r.MIDDLEGOODSID)
	sqlId.Join(fmt.Sprintf(" and %v in(s.areauserid, s.tradeuserid)", r.USERID))
	return sqlId.String()
}

// GetDataEx 获取敞口套保计划明细
func (r *Ermcp3ExposureHedgeplanDetail) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ExposureHedgeplanDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3AreaSpotPLReport 现货报表(日/月/周/季/年)
type Ermcp3AreaSpotPLReport struct {
	RECKONDATE            string  `json:"reckondate"  xorm:"RECKONDATE"`                       // 日照时期(yyyyMMdd)
	CYCLETIME             string  `json:"cycletime"  xorm:"'CYCLETIME'"`                       // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	CYCLETYPE             int32   `json:"cycletype"  xorm:"'CYCLETYPE'"`                       // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	AREAUSERID            int64   `json:"areauserid"  xorm:"AREAUSERID"`                       // 所属机构\交易用户ID
	ACCOUNTID             int64   `json:"accountid"  xorm:"ACCOUNTID"`                         // 期货账户ID (作废, 默认为0)
	WRFACTORTYPEID        int64   `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID"`               // 仓单要素类型ID(212+Unix秒时间戳(10位)+xxxxxx)
	CURRENCYID            int32   `json:"currencyid"  xorm:"CURRENCYID"`                       // 结算币种ID【原值】
	BIZTYPE               int32   `json:"biztype"  xorm:"BIZTYPE"`                             // 业务类型 - 1:套保 2:套利
	DELIVERYGOODSID       int64   `json:"deliverygoodsid"  xorm:"DELIVERYGOODSID"`             // 现货品种ID
	WRSTANDARDID          int64   `json:"wrstandardid"  xorm:"WRSTANDARDID"`                   // 现货品类ID
	SPOTGOODSBRANDID      int32   `json:"spotgoodsbrandid"  xorm:"SPOTGOODSBRANDID"`           // 现货品牌ID
	ORIBUYQTY             float64 `json:"oribuyqty"  xorm:"ORIBUYQTY"`                         // 期初采购总量
	ORIBUYAMOUNT          float64 `json:"oribuyamount"  xorm:"ORIBUYAMOUNT"`                   // 期初采购总额
	ORISELLQTY            float64 `json:"orisellqty"  xorm:"ORISELLQTY"`                       // 期初销售总量
	ORISELLAMOUNT         float64 `json:"orisellamount"  xorm:"ORISELLAMOUNT"`                 // 期初销售总额
	ORIQTY                float64 `json:"oriqty"  xorm:"ORIQTY"`                               // 期初量
	ORIAVERAGEPRICE       float64 `json:"oriaverageprice"  xorm:"ORIAVERAGEPRICE"`             // 期初均价
	ORIAMOUNT             float64 `json:"oriamount"  xorm:"ORIAMOUNT"`                         // 期初额
	TODAYBUYQTY           float64 `json:"todaybuyqty"  xorm:"TODAYBUYQTY"`                     // 今日采购量(今采购量)
	TODAYBUYAMOUNT        float64 `json:"todaybuyamount"  xorm:"TODAYBUYAMOUNT"`               // 今日采购额(今采购额)
	TODAYBUYAVERAGEPRICE  float64 `json:"todaybuyaverageprice"  xorm:"TODAYBUYAVERAGEPRICE"`   // 今日采购均价
	TODAYSELLQTY          float64 `json:"todaysellqty"  xorm:"TODAYSELLQTY"`                   // 今日销售量(今销售量)
	TODAYSELLAMOUNT       float64 `json:"todaysellamount"  xorm:"TODAYSELLAMOUNT"`             // 今日销售额(今销售额)
	TODAYSELLAVERAGEPRICE float64 `json:"todaysellaverageprice"  xorm:"TODAYSELLAVERAGEPRICE"` // 今日销售均价
	CURBUYQTY             float64 `json:"curbuyqty"  xorm:"CURBUYQTY"`                         // 期末采购总量
	CURBUYAMOUNT          float64 `json:"curbuyamount"  xorm:"CURBUYAMOUNT"`                   // 期末采购总额
	CURSELLQTY            float64 `json:"cursellqty"  xorm:"CURSELLQTY"`                       // 期末销售总量
	CURSELLAMOUNT         float64 `json:"cursellamount"  xorm:"CURSELLAMOUNT"`                 // 期末销售总额
	CURQTY                float64 `json:"curqty"  xorm:"CURQTY"`                               // 期末量
	CURAVERAGEPRICE       float64 `json:"curaverageprice"  xorm:"CURAVERAGEPRICE"`             // 期末均价
	CURAMOUNT             float64 `json:"curamount"  xorm:"CURAMOUNT"`                         // 期末额
	CURSPOTPRICE          float64 `json:"curspotprice"  xorm:"CURSPOTPRICE"`                   // 参考市价(最新价)
	CURMARKETVALUE        float64 `json:"curmarketvalue"  xorm:"CURMARKETVALUE"`               // 参考市值(期末市值)
	ACTUALPL              float64 `json:"actualpl"  xorm:"ACTUALPL"`                           // 现货损益
	FLOATPL               float64 `json:"floatpl"  xorm:"FLOATPL"`                             // 浮动损益
	UPDATETIME            string  `json:"updatetime"  xorm:"UPDATETIME"`                       // 更新时间
	TODAYINQTY            float64 `json:"todayinqty"  xorm:"TODAYINQTY"`                       // 今日入库量(今入库量)
	TODAYOUTQTY           float64 `json:"todayoutqty"  xorm:"TODAYOUTQTY"`                     // 今日出库量(今出库量)
	DELIVERYGOODSCODE     string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`       // 现货商品代码
	DELIVERYGOODSNAME     string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`       // 现货商品名称
	GOODSUNITID           int32   `json:"goodsunitid"  xorm:"'GOODSUNITID'"`                   // 现货商品单位id
	WRSTANDARDCODE        string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`             // 品类代码
	WRSTANDARDNAME        string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`             // 品类名称
	UNITID                int32   `json:"unitid"  xorm:"'UNITID'"`                             // 品类单位id
	BRANDNAME             string  `json:"brandname"  xorm:"'BRANDNAME'"`                       // 品牌名称

	EnumdicName  string `json:"enumdicname"`  // 现货商品单位名称
	UNITIDNAME   string `json:"unitidname"`   // 品类单位名称
	ACCOUNTNAME  string `json:"accountname"`  // 机构名称
	CURRENCYNAME string `json:"currencyname"` // 币种名称

	QueryType int32  `json:"-"`         // 查询类型 1-报表 2-报表明细
	BeginDate string `json:"begindate"` // 开始交易日
	EndDate   string `json:"enddate"`   // 结束交易日
}

func (r *Ermcp3AreaSpotPLReport) calc() {
	r.EnumdicName = mtpcache.GetEnumDicitemName(r.GOODSUNITID)
	r.UNITIDNAME = mtpcache.GetEnumDicitemName(r.UNITID)
	r.ACCOUNTNAME = mtpcache.GetUserNameByUserId(r.AREAUSERID)
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
	fCalcAvePrice := func(avePrice *float64, amount, qty float64) {
		if qty > 1e-10 {
			*avePrice = amount / qty
		} else if qty < -1e-10 {
			*avePrice = amount / qty
			*avePrice, _ = decimal.NewFromFloat(*avePrice).Round(3).Float64()
		} else {
			*avePrice = 0
		}
	}
	// 期初均价
	fCalcAvePrice(&r.ORIAVERAGEPRICE, r.ORIAMOUNT, r.ORIQTY)
	// 期末均价
	fCalcAvePrice(&r.CURAVERAGEPRICE, r.CURAMOUNT, r.CURQTY)
	// 今采购均价
	fCalcAvePrice(&r.TODAYBUYAVERAGEPRICE, r.TODAYBUYAMOUNT, r.TODAYBUYQTY)
	// 今销售均价
	fCalcAvePrice(&r.TODAYSELLAVERAGEPRICE, r.TODAYSELLAMOUNT, r.TODAYSELLQTY)
}

func (r *Ermcp3AreaSpotPLReport) buildSql() string {
	// 日报表
	if r.QueryType == 1 && r.CYCLETYPE == 0 {
		return r.buildSqlDay()
	}
	// 日报表明细
	if r.QueryType == 2 && r.CYCLETYPE == 0 {
		return r.buildSqlDayDetail()
	}
	return r.buildSqlCycle()
}

// buildSqlDay 现货日报表查询语句
func (r *Ermcp3AreaSpotPLReport) buildSqlDay() string {
	var sqlId utils.SQLVal = "select a.*," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid" +
		"  from (SELECT t.RECKONDATE," +
		"               t.AREAUSERID," +
		"               t.CURRENCYID," +
		"               t.DELIVERYGOODSID," +
		"               sum(t.ORIBUYQTY) ORIBUYQTY," +
		"               sum(t.ORIBUYAMOUNT) ORIBUYAMOUNT," +
		"               sum(t.ORISELLQTY) ORISELLQTY," +
		"               sum(t.ORISELLAMOUNT) ORISELLAMOUNT," +
		"               sum(t.ORIQTY) ORIQTY," +
		"               sum(t.ORIAMOUNT) ORIAMOUNT," +
		"               sum(t.TODAYBUYQTY) TODAYBUYQTY," +
		"               sum(t.TODAYBUYAMOUNT) TODAYBUYAMOUNT," +
		"               sum(t.TODAYSELLQTY) TODAYSELLQTY," +
		"               sum(t.TODAYSELLAMOUNT) TODAYSELLAMOUNT," +
		"               sum(t.CURBUYQTY) CURBUYQTY," +
		"               sum(t.CURBUYAMOUNT) CURBUYAMOUNT," +
		"               sum(t.CURSELLQTY) CURSELLQTY," +
		"               sum(t.CURSELLAMOUNT) CURSELLAMOUNT," +
		"               sum(t.CURQTY) CURQTY," +
		"               sum(t.CURAMOUNT) CURAMOUNT," +
		"               max(t.CURSPOTPRICE) CURSPOTPRICE," +
		"               sum(t.CURMARKETVALUE) CURMARKETVALUE," +
		"               sum(t.ACTUALPL) ACTUALPL," +
		"               sum(t.FLOATPL) FLOATPL," +
		"               sum(t.TODAYINQTY) TODAYINQTY," +
		"               sum(t.TODAYOUTQTY) TODAYOUTQTY" +
		"          FROM RECKON_ERMCP_AREASPOTPL t" +
		"         WHERE 1=1 %v" +
		"         GROUP BY t.RECKONDATE," +
		"                  t.AREAUSERID," +
		"                  t.CURRENCYID," +
		"                  t.DELIVERYGOODSID) a" +
		"  LEFT JOIN DELIVERYGOODS g" +
		"    on a.deliverygoodsid = g.deliverygoodsid"
	var sqlParam utils.SQLVal
	sqlParam.And("t.AREAUSERID", r.AREAUSERID)
	if r.QueryType == 1 {
		sqlParam.And("t.RECKONDATE", r.RECKONDATE)
	} else if r.QueryType == 2 {
		sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	sqlId.FormatParam(sqlParam.String())
	return sqlId.String()
}

func (r *Ermcp3AreaSpotPLReport) buildSqlDayDetail() string {
	var sqlId utils.SQLVal = "select a.*," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       w.unitid," +
		"       dg.dgfactoryitemvalue brandName" +
		"  from (SELECT t.RECKONDATE," +
		"               t.AREAUSERID," +
		"               t.CURRENCYID," +
		"               t.DELIVERYGOODSID," +
		"               t.WRSTANDARDID," +
		"               t.SPOTGOODSBRANDID," +
		"               sum(t.ORIBUYQTY) ORIBUYQTY," +
		"               sum(t.ORIBUYAMOUNT) ORIBUYAMOUNT," +
		"               sum(t.ORISELLQTY) ORISELLQTY," +
		"               sum(t.ORISELLAMOUNT) ORISELLAMOUNT," +
		"               sum(t.ORIQTY) ORIQTY," +
		"               sum(t.ORIAMOUNT) ORIAMOUNT," +
		"               sum(t.TODAYBUYQTY) TODAYBUYQTY," +
		"               sum(t.TODAYBUYAMOUNT) TODAYBUYAMOUNT," +
		"               sum(t.TODAYSELLQTY) TODAYSELLQTY," +
		"               sum(t.TODAYSELLAMOUNT) TODAYSELLAMOUNT," +
		"               sum(t.CURBUYQTY) CURBUYQTY," +
		"               sum(t.CURBUYAMOUNT) CURBUYAMOUNT," +
		"               sum(t.CURSELLQTY) CURSELLQTY," +
		"               sum(t.CURSELLAMOUNT) CURSELLAMOUNT," +
		"               sum(t.CURQTY) CURQTY," +
		"               sum(t.CURAMOUNT) CURAMOUNT," +
		"               max(t.CURSPOTPRICE) CURSPOTPRICE," +
		"               sum(t.CURMARKETVALUE) CURMARKETVALUE," +
		"               sum(t.ACTUALPL) ACTUALPL," +
		"               sum(t.FLOATPL) FLOATPL," +
		"               sum(t.TODAYINQTY) TODAYINQTY," +
		"               sum(t.TODAYOUTQTY) TODAYOUTQTY" +
		"          FROM RECKON_ERMCP_AREASPOTPL t" +
		"         WHERE 1 = 1 %v" +
		"         GROUP BY t.RECKONDATE," +
		"                  t.AREAUSERID," +
		"                  t.CURRENCYID," +
		"                  t.DELIVERYGOODSID," +
		"                  t.WRSTANDARDID," +
		"                  t.SPOTGOODSBRANDID) a" +
		"  LEFT JOIN DELIVERYGOODS g" +
		"    on a.deliverygoodsid = g.deliverygoodsid" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on a.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN DGFACTORYITEM dg" +
		"    on a.spotgoodsbrandid = dg.dgfactoryitemid"
	var sqlParam utils.SQLVal
	sqlParam.And("t.AREAUSERID", r.AREAUSERID)
	sqlParam.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	sqlParam.And("t.CURRENCYID", r.CURRENCYID)
	sqlParam.And("t.DELIVERYGOODSID", r.DELIVERYGOODSID)
	sqlId.FormatParam(sqlParam.String())
	return sqlId.String()
}

// buildSqlCycle 周期报表查询语句
func (r *Ermcp3AreaSpotPLReport) buildSqlCycle() string {
	var sqlId utils.SQLVal = "select a.*," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid" +
		"  from (SELECT t.cycletype," +
		"               t.cycletime," +
		"               t.AREAUSERID," +
		"               t.CURRENCYID," +
		"               t.DELIVERYGOODSID," +
		"               sum(t.ORIBUYQTY) ORIBUYQTY," +
		"               sum(t.ORIBUYAMOUNT) ORIBUYAMOUNT," +
		"               sum(t.ORISELLQTY) ORISELLQTY," +
		"               sum(t.ORISELLAMOUNT) ORISELLAMOUNT," +
		"               sum(t.ORIQTY) ORIQTY," +
		"               sum(t.ORIAMOUNT) ORIAMOUNT," +
		"               sum(t.TODAYBUYQTY) TODAYBUYQTY," +
		"               sum(t.TODAYBUYAMOUNT) TODAYBUYAMOUNT," +
		"               sum(t.TODAYSELLQTY) TODAYSELLQTY," +
		"               sum(t.TODAYSELLAMOUNT) TODAYSELLAMOUNT," +
		"               sum(t.CURBUYQTY) CURBUYQTY," +
		"               sum(t.CURBUYAMOUNT) CURBUYAMOUNT," +
		"               sum(t.CURSELLQTY) CURSELLQTY," +
		"               sum(t.CURSELLAMOUNT) CURSELLAMOUNT," +
		"               sum(t.CURQTY) CURQTY," +
		"               sum(t.CURAMOUNT) CURAMOUNT," +
		"               max(t.CURSPOTPRICE) CURSPOTPRICE," +
		"               sum(t.CURMARKETVALUE) CURMARKETVALUE," +
		"               sum(t.ACTUALPL) ACTUALPL," +
		"               sum(t.FLOATPL) FLOATPL," +
		"               sum(t.TODAYINQTY) TODAYINQTY," +
		"               sum(t.TODAYOUTQTY) TODAYOUTQTY" +
		"          FROM REPORT_ERMCP_AREASPOTPL t" +
		"         WHERE 1=1 %v" +
		"         GROUP BY t.AREAUSERID," +
		"                  t.cycletype," +
		"                  t.cycletime," +
		"                  t.CURRENCYID," +
		"                  t.DELIVERYGOODSID) a" +
		"  LEFT JOIN DELIVERYGOODS g" +
		"    on a.deliverygoodsid = g.deliverygoodsid"
	var sqlParam utils.SQLVal
	sqlParam.And("t.AREAUSERID", r.AREAUSERID)
	sqlParam.And("t.Cycletype", r.CYCLETYPE)
	sqlParam.And("t.CYCLETIME", r.CYCLETIME)
	sqlId.FormatParam(sqlParam.String())
	return sqlId.String()
}

// GetDataEx 获取现货报表(日/月/周/季/年)
func (r *Ermcp3AreaSpotPLReport) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3AreaSpotPLReport, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].BeginDate = r.BeginDate
		sData[i].EndDate = r.EndDate
	}
	return sData, err
}

// Ermcp3FinanceReport 财务报表
type Ermcp3FinanceReport struct {
	RECKONDATE             string  `json:"reckondate"  xorm:"RECKONDATE"`                         // 日照时期(yyyyMMdd)
	CYCLETIME              string  `json:"cycletime"  xorm:"CYCLETIME"`                           // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	CYCLETYPE              int32   `json:"cycletype"  xorm:"CYCLETYPE"`                           // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	SELLPREINVOICEDAMOUNT  float64 `json:"sellpreinvoicedamount"  xorm:"SELLPREINVOICEDAMOUNT"`   // 销售预开票额(预开票额)
	SELLUNINVOICEDAMOUNT   float64 `json:"selluninvoicedamount"  xorm:"SELLUNINVOICEDAMOUNT"`     // 销售应开票额(应开票额)
	TODAYRECEIVESUM        float64 `json:"todayreceivesum"  xorm:"TODAYRECEIVESUM"`               // 今收款合计
	TODAYPAYSUM            float64 `json:"todaypaysum"  xorm:"TODAYPAYSUM"`                       // 今付款合计
	UPDATETIME             string  `json:"updatetime"  xorm:"UPDATETIME"`                         // 更新时间
	AREAUSERID             int64   `json:"areauserid"  xorm:"AREAUSERID"`                         // 所属机构\交易用户ID
	CURRENCYID             int32   `json:"currencyid"  xorm:"CURRENCYID"`                         // 结算币种ID
	BIZTYPE                int32   `json:"biztype"  xorm:"BIZTYPE"`                               // 业务类型 - 1:套保 2:套利
	BUYTODAYSETTLEAMOUNT   float64 `json:"buytodaysettleamount"  xorm:"BUYTODAYSETTLEAMOUNT"`     // 采购今付款额(今付货款额)
	SELLTODAYSETTLEAMOUNT  float64 `json:"selltodaysettleamount"  xorm:"SELLTODAYSETTLEAMOUNT"`   // 销售今收款额(今收货款额)
	BUYTODAYREFUNDAMOUNT   float64 `json:"buytodayrefundamount"  xorm:"BUYTODAYREFUNDAMOUNT"`     // 采购今收退款额(今收退款额)
	SELLTODAYREFUNDAMOUNT  float64 `json:"selltodayrefundamount"  xorm:"SELLTODAYREFUNDAMOUNT"`   // 销售今付退款额(今付退款额)
	SELLTODAYINVOICEAMOUNT float64 `json:"selltodayinvoiceamount"  xorm:"SELLTODAYINVOICEAMOUNT"` // 销售今开票额(今开票额)
	BUYTODAYINVOICEAMOUNT  float64 `json:"buytodayinvoiceamount"  xorm:"BUYTODAYINVOICEAMOUNT"`   // 采购今收票额(今收票额)
	BUYPREPAIDAMOUNT       float64 `json:"buyprepaidamount"  xorm:"BUYPREPAIDAMOUNT"`             // 采购预付款额(预付货款额)
	BUYUNPAIDAMOUNT        float64 `json:"buyunpaidamount"  xorm:"BUYUNPAIDAMOUNT"`               // 采购应付款额(应付货款额)
	BUYPREINVOICEDAMOUNT   float64 `json:"buypreinvoicedamount"  xorm:"BUYPREINVOICEDAMOUNT"`     // 采购预收票额(预收票额)
	BUYUNINVOICEDAMOUNT    float64 `json:"buyuninvoicedamount"  xorm:"BUYUNINVOICEDAMOUNT"`       // 采购应收票额(应收票额)
	SELLPREPAIDAMOUNT      float64 `json:"sellprepaidamount"  xorm:"SELLPREPAIDAMOUNT"`           // 销售预收款额(预收货款额)
	SELLUNPAIDAMOUNT       float64 `json:"sellunpaidamount"  xorm:"SELLUNPAIDAMOUNT"`             // 销售应收款额(应收货款额)
	WRFACTORTYPEID         int64   `json:"wrfactortypeid"  xorm:"WRFACTORTYPEID"`                 // 仓单要素类型ID(212+Unix秒时间戳(10位)+xxxxxx)
	DELIVERYGOODSID        int64   `json:"deliverygoodsid"  xorm:"DELIVERYGOODSID"`               // 现货品种ID
	WRSTANDARDID           int64   `json:"wrstandardid"  xorm:"WRSTANDARDID"`                     // 现货商品ID
	SPOTGOODSBRANDID       int32   `json:"spotgoodsbrandid"  xorm:"SPOTGOODSBRANDID"`             // 现货品牌ID
	DELIVERYGOODSCODE      string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`         // 现货品种代码
	DELIVERYGOODSNAME      string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`         // 现货品种名称
	WRSTANDARDCODE         string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`               // 品类代码
	WRSTANDARDNAME         string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`               // 品类名称
	BRANDNAME              string  `json:"brandname"  xorm:"'BRANDNAME'"`                         // 品牌名称

	ACCOUNTNAME  string `json:"accountname"`  // 机构名称
	CURRENCYNAME string `json:"currencyname"` // 币种名称
	QueryType    int32  `json:"-"`            // 查询类型 1-报表 2-报表明细
	BeginDate    string `json:"begindate"`    // 开始交易日
	EndDate      string `json:"enddate"`      // 结束交易日

	SumFields string `json:"-"` // 维度字段
}

func (r *Ermcp3FinanceReport) calc() {
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
	r.ACCOUNTNAME = mtpcache.GetUserNameByUserId(r.AREAUSERID)
	f := func(v *float64) {
		if *v < 0 {
			*v = 0
		}
	}

	f(&r.BUYPREPAIDAMOUNT)
	f(&r.SELLPREINVOICEDAMOUNT)
	f(&r.BUYUNINVOICEDAMOUNT)
	f(&r.SELLPREPAIDAMOUNT)
	f(&r.BUYPREINVOICEDAMOUNT)
}

// getSumField 获取汇总维度字段
func (r *Ermcp3FinanceReport) getSumField() (fields []string) {
	fields = append(fields, "areauserid")
	fields = append(fields, "currencyid")
	if r.CYCLETYPE == 0 {
		// 日报表
		fields = append(fields, "reckondate")
	} else {
		// 周期报表
		fields = append(fields, "cycletype")
		fields = append(fields, "cycletime")
	}
	baseField := map[string]string{
		"1": "deliverygoodsid",
		"2": "wrstandardid",
		"3": "spotgoodsbrandid",
	}
	param := []string{}
	if len(r.SumFields) > 0 {
		param = strings.Split(r.SumFields, ",")
	}
	for k, v := range baseField {
		for _, p := range param {
			if k == p {
				fields = append(fields, v)
			}
		}
	}
	return
}

func (r *Ermcp3FinanceReport) getExFieldInfo() (strFiled string, strleftJoin string) {
	if len(r.SumFields) > 0 {
		param := strings.Split(r.SumFields, ",")
		fHas := func(v string) bool {
			for i := range param {
				if param[i] == v {
					return true
				}
			}
			return false
		}
		if fHas("1") {
			if len(strFiled) > 0 {
				strFiled += ","
			}
			strFiled += "g.deliverygoodscode, g.deliverygoodsname, g.goodsunitid"
			strleftJoin += " LEFT JOIN deliverygoods g on a.deliverygoodsid = g.deliverygoodsid"
		}
		if fHas("2") {
			if len(strFiled) > 0 {
				strFiled += ","
			}
			strFiled += "w.wrstandardname, w.wrstandardcode, w.unitid"
			strleftJoin += " LEFT JOIN WRSTANDARD w on a.wrstandardid = w.wrstandardid"
		}
		if fHas("3") {
			if len(strFiled) > 0 {
				strFiled += ","
			}
			strFiled += "gb.dgfactoryitemvalue brandname"
			strleftJoin += " LEFT JOIN dgfactoryitem gb on a.spotgoodsbrandid = gb.dgfactoryitemid"
		}
	}
	return
}

func (r *Ermcp3FinanceReport) buildSql() string {
	fields := r.getSumField()
	if r.CYCLETYPE == 0 {
		// 查日照报
		return r.buildSqlReckon(fields)
	} else {
		// 查报表表
		return r.buildSqlReport(fields)
	}
}

func (r *Ermcp3FinanceReport) buildSqlReckon(fields []string) string {
	var sqlId, sqlParam utils.SQLVal
	var strFileds string
	for _, v := range fields {
		if len(strFileds) > 0 {
			strFileds += ","
		}
		strFileds += "t." + v
	}
	sqlParam.And("t.AREAUSERID", r.AREAUSERID)
	sqlParam.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	sqlParam.AndEx("t.CURRENCYID", r.CURRENCYID, r.CURRENCYID > 0)

	exFileds, strleftJoin := r.getExFieldInfo()
	if len(exFileds) > 0 {
		// 有左联查额外信息(RECKON_ERMCP_AFINANCESUB明细表, 注意是不同的表)
		sqlId = "select a.*, %v" +
			"  from (SELECT %v," +
			"               sum(t.BUYTODAYSETTLEAMOUNT) BUYTODAYSETTLEAMOUNT," +
			"               sum(t.BUYTODAYREFUNDAMOUNT) BUYTODAYREFUNDAMOUNT," +
			"               sum(t.BUYPREPAIDAMOUNT) BUYPREPAIDAMOUNT," +
			"               sum(t.BUYUNPAIDAMOUNT) BUYUNPAIDAMOUNT," +
			"               sum(t.BUYTODAYINVOICEAMOUNT) BUYTODAYINVOICEAMOUNT," +
			"               sum(t.BUYPREINVOICEDAMOUNT) BUYPREINVOICEDAMOUNT," +
			"               sum(t.BUYUNINVOICEDAMOUNT) BUYUNINVOICEDAMOUNT," +
			"               sum(t.SELLTODAYSETTLEAMOUNT) SELLTODAYSETTLEAMOUNT," +
			"               sum(t.SELLTODAYREFUNDAMOUNT) SELLTODAYREFUNDAMOUNT," +
			"               sum(t.SELLPREPAIDAMOUNT) SELLPREPAIDAMOUNT," +
			"               sum(t.SELLUNPAIDAMOUNT) SELLUNPAIDAMOUNT," +
			"               sum(t.SELLTODAYINVOICEAMOUNT) SELLTODAYINVOICEAMOUNT," +
			"               sum(t.SELLPREINVOICEDAMOUNT) SELLPREINVOICEDAMOUNT," +
			"               sum(t.SELLUNINVOICEDAMOUNT) SELLUNINVOICEDAMOUNT," +
			"               sum(t.TODAYRECEIVESUM) TODAYRECEIVESUM," +
			"               sum(t.TODAYPAYSUM) TODAYPAYSUM" +
			"          FROM RECKON_ERMCP_AFINANCESUB t" +
			"         WHERE 1 = 1 %v" +
			"         GROUP BY %v) a %v"
		sqlId.FormatParam(exFileds, strFileds, sqlParam.String(), strFileds, strleftJoin)
	} else {
		sqlId = "SELECT %v," +
			"       sum(t.BUYTODAYSETTLEAMOUNT) BUYTODAYSETTLEAMOUNT," +
			"       sum(t.BUYTODAYREFUNDAMOUNT) BUYTODAYREFUNDAMOUNT," +
			"       sum(t.BUYPREPAIDAMOUNT) BUYPREPAIDAMOUNT," +
			"       sum(t.BUYUNPAIDAMOUNT) BUYUNPAIDAMOUNT," +
			"       sum(t.BUYTODAYINVOICEAMOUNT) BUYTODAYINVOICEAMOUNT," +
			"       sum(t.BUYPREINVOICEDAMOUNT) BUYPREINVOICEDAMOUNT," +
			"       sum(t.BUYUNINVOICEDAMOUNT) BUYUNINVOICEDAMOUNT," +
			"       sum(t.SELLTODAYSETTLEAMOUNT) SELLTODAYSETTLEAMOUNT," +
			"       sum(t.SELLTODAYREFUNDAMOUNT) SELLTODAYREFUNDAMOUNT," +
			"       sum(t.SELLPREPAIDAMOUNT) SELLPREPAIDAMOUNT," +
			"       sum(t.SELLUNPAIDAMOUNT) SELLUNPAIDAMOUNT," +
			"       sum(t.SELLTODAYINVOICEAMOUNT) SELLTODAYINVOICEAMOUNT," +
			"       sum(t.SELLPREINVOICEDAMOUNT) SELLPREINVOICEDAMOUNT," +
			"       sum(t.SELLUNINVOICEDAMOUNT) SELLUNINVOICEDAMOUNT," +
			"       sum(t.TODAYRECEIVESUM) TODAYRECEIVESUM," +
			"       sum(t.TODAYPAYSUM) TODAYPAYSUM" +
			"  FROM RECKON_ERMCP_AREAFINANCE t" +
			" WHERE 1 = 1 %v" +
			" GROUP BY %v"
		sqlId.FormatParam(strFileds, sqlParam.String(), strFileds)
	}
	return sqlId.String()
}

func (r *Ermcp3FinanceReport) buildSqlReport(fields []string) string {
	var sqlId, sqlParam utils.SQLVal
	var strFileds string
	for _, v := range fields {
		if len(strFileds) > 0 {
			strFileds += ","
		}
		strFileds += "t." + v
	}
	sqlParam.And("t.AREAUSERID", r.AREAUSERID)
	sqlParam.And("t.CYCLETYPE", r.CYCLETYPE)
	sqlParam.And("t.CYCLETIME", r.CYCLETIME)
	sqlParam.AndEx("t.CURRENCYID", r.CURRENCYID, r.CURRENCYID > 0)

	exFileds, strleftJoin := r.getExFieldInfo()
	if len(exFileds) > 0 {
		// 有左联查额外信息(REPORT_ERMCP_AFINANCESUB注意表名不同)
		sqlId = "select a.*, %v" +
			"  from (SELECT %v," +
			"               sum(t.BUYTODAYSETTLEAMOUNT) BUYTODAYSETTLEAMOUNT," +
			"               sum(t.BUYTODAYREFUNDAMOUNT) BUYTODAYREFUNDAMOUNT," +
			"               sum(t.BUYPREPAIDAMOUNT) BUYPREPAIDAMOUNT," +
			"               sum(t.BUYUNPAIDAMOUNT) BUYUNPAIDAMOUNT," +
			"               sum(t.BUYTODAYINVOICEAMOUNT) BUYTODAYINVOICEAMOUNT," +
			"               sum(t.BUYPREINVOICEDAMOUNT) BUYPREINVOICEDAMOUNT," +
			"               sum(t.BUYUNINVOICEDAMOUNT) BUYUNINVOICEDAMOUNT," +
			"               sum(t.SELLTODAYSETTLEAMOUNT) SELLTODAYSETTLEAMOUNT," +
			"               sum(t.SELLTODAYREFUNDAMOUNT) SELLTODAYREFUNDAMOUNT," +
			"               sum(t.SELLPREPAIDAMOUNT) SELLPREPAIDAMOUNT," +
			"               sum(t.SELLUNPAIDAMOUNT) SELLUNPAIDAMOUNT," +
			"               sum(t.SELLTODAYINVOICEAMOUNT) SELLTODAYINVOICEAMOUNT," +
			"               sum(t.SELLPREINVOICEDAMOUNT) SELLPREINVOICEDAMOUNT," +
			"               sum(t.SELLUNINVOICEDAMOUNT) SELLUNINVOICEDAMOUNT," +
			"               sum(t.TODAYRECEIVESUM) TODAYRECEIVESUM," +
			"               sum(t.TODAYPAYSUM) TODAYPAYSUM" +
			"          FROM REPORT_ERMCP_AFINANCESUB t" +
			"         WHERE 1 = 1 %v" +
			"         GROUP BY %v) a %v"
		sqlId.FormatParam(exFileds, strFileds, sqlParam.String(), strFileds, strleftJoin)
	} else {
		sqlId = "SELECT %v," +
			"       sum(t.BUYTODAYSETTLEAMOUNT) BUYTODAYSETTLEAMOUNT," +
			"       sum(t.BUYTODAYREFUNDAMOUNT) BUYTODAYREFUNDAMOUNT," +
			"       sum(t.BUYPREPAIDAMOUNT) BUYPREPAIDAMOUNT," +
			"       sum(t.BUYUNPAIDAMOUNT) BUYUNPAIDAMOUNT," +
			"       sum(t.BUYTODAYINVOICEAMOUNT) BUYTODAYINVOICEAMOUNT," +
			"       sum(t.BUYPREINVOICEDAMOUNT) BUYPREINVOICEDAMOUNT," +
			"       sum(t.BUYUNINVOICEDAMOUNT) BUYUNINVOICEDAMOUNT," +
			"       sum(t.SELLTODAYSETTLEAMOUNT) SELLTODAYSETTLEAMOUNT," +
			"       sum(t.SELLTODAYREFUNDAMOUNT) SELLTODAYREFUNDAMOUNT," +
			"       sum(t.SELLPREPAIDAMOUNT) SELLPREPAIDAMOUNT," +
			"       sum(t.SELLUNPAIDAMOUNT) SELLUNPAIDAMOUNT," +
			"       sum(t.SELLTODAYINVOICEAMOUNT) SELLTODAYINVOICEAMOUNT," +
			"       sum(t.SELLPREINVOICEDAMOUNT) SELLPREINVOICEDAMOUNT," +
			"       sum(t.SELLUNINVOICEDAMOUNT) SELLUNINVOICEDAMOUNT," +
			"       sum(t.TODAYRECEIVESUM) TODAYRECEIVESUM," +
			"       sum(t.TODAYPAYSUM) TODAYPAYSUM" +
			"  FROM REPORT_ERMCP_AREAFINANCE t" +
			" WHERE 1 = 1 %v" +
			" GROUP BY %v"
		sqlId.FormatParam(strFileds, sqlParam.String(), strFileds)
	}
	return sqlId.String()
}

// GetDataEx 获取财务报表
func (r *Ermcp3FinanceReport) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3FinanceReport, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].BeginDate = r.BeginDate
		sData[i].EndDate = r.EndDate
	}
	return sData, err
}

// Ermcp3AreaStockReportEx 库存报表
type Ermcp3AreaStockReportEx struct {
	RECKONDATE         string  `json:"reckondate"  xorm:"RECKONDATE"`                   // 日照时期(yyyyMMdd)
	CYCLETIME          string  `json:"cycletime"  xorm:"CYCLETIME"`                     // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	CYCLETYPE          int32   `json:"cycletype"  xorm:"CYCLETYPE"`                     // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	USERID             int64   `json:"userid"  xorm:"'USERID'"`                         // 机构ID
	WRSTANDARDID       int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`             // 品类ID
	SPOTGOODSBRANDID   int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`     // 现货品牌ID
	WAREHOUSEINFOID    string  `json:"warehouseinfoid"  xorm:"'WAREHOUSEINFOID'"`       // 仓库ID
	ORISTOCK           float64 `json:"oristock"  xorm:"'ORISTOCK'"`                     // 期初库存量
	CURSTOCK           float64 `json:"curstock"  xorm:"'CURSTOCK'"`                     // 期末库存量
	TODAYBUYINQTY      float64 `json:"todaybuyinqty"  xorm:"'TODAYBUYINQTY'"`           // 今采购入库量
	TODAYPRODUCEINQTY  float64 `json:"todayproduceinqty"  xorm:"'TODAYPRODUCEINQTY'"`   // 今内部入库量
	TODAYSELLOUTQTY    float64 `json:"todayselloutqty"  xorm:"'TODAYSELLOUTQTY'"`       // 今销售出库量
	TODAYPRODUCEOUTQTY float64 `json:"todayproduceoutqty"  xorm:"'TODAYPRODUCEOUTQTY'"` // 今内部出库量
	UPDATETIME         string  `json:"updatetime"  xorm:"'UPDATETIME'"`                 // 更新时间
	WRSTANDARDNAME     string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`         // 品类名称
	WRSTANDARDCODE     string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`         // 品类代码
	UNITID             int32   `json:"unitid"  xorm:"'UNITID'"`                         // 品类单位id
	BRANDNAME          string  `json:"brandname"  xorm:"'BRANDNAME'"`                   // 品牌名称
	WAREHOUSENAME      string  `json:"warehousename"  xorm:"'WAREHOUSENAME'"`           // 仓库名称
	WAREHOUSECODE      string  `json:"warehousecode"  xorm:"'WAREHOUSECODE'"`           // 仓库代码
	WAREHOUSETYPE      int32   `json:"warehousetype"  xorm:"'WAREHOUSETYPE'"`           // 仓库类型 - 1 厂库  2 自有库  3 合作库
	USERNAME           string  `json:"username"`                                        // 机构名称
	ENUMDICNAME        string  `json:"enumdicname"`                                     // 单位名称
	DELIVERYGOODSID    int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`       // 现货品种id
	DELIVERYGOODSCODE  string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`   // 现货品种代码
	DELIVERYGOODSNAME  string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`   // 现货品种名称
	GOODSUNITID        int32   `json:"goodsunitid"  xorm:"'GOODSUNITID'"`               // 现货单位id

	UNBUYINQTY   float64 `json:"unbuyinqty"`   // 采购未入库量(数据库未找到相关字段?)
	UNSELLOUTQTY float64 `json:"unselloutqty"` // 销售未出库量(数据库未找到相关字段?)
	DiffQty      float64 `json:"diffqty"`      // 库存变化量 = 期末 - 期初
	BeginDate    string  `json:"begindate"`    // 开始交易日
	EndDate      string  `json:"enddate"`      // 结束交易日
	SumFields    string  `json:"-"`            // 汇总维度(逗号隔开) 1-品种 2-品类 3-品牌 4-仓库
}

func (r *Ermcp3AreaStockReportEx) calc() {
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
	r.DiffQty = r.CURSTOCK - r.ORISTOCK
}

// getSumField 获取汇总维度字段
func (r *Ermcp3AreaStockReportEx) getSumField() (fields []string, bSum bool) {
	fields = append(fields, "userid")
	if r.CYCLETYPE == 0 {
		// 日报表
		fields = append(fields, "reckondate")
	} else {
		// 周期报表
		fields = append(fields, "cycletype")
		fields = append(fields, "cycletime")
	}
	baseField := map[string]string{
		"1": "deliverygoodsid",
		"2": "wrstandardid",
		"3": "spotgoodsbrandid",
		"4": "warehouseinfoid",
	}
	bSum = false
	param := []string{}
	if len(r.SumFields) > 0 {
		param = strings.Split(r.SumFields, ",")
	}
	if len(param) == 0 {
		// 全字段, 不需要汇总
		for _, v := range baseField {
			fields = append(fields, v)
		}
		bSum = false
	} else {
		bIncludeAll := true
		// 判断是否包含所有维度
		for k := range baseField {
			bExist := false
			for _, p := range param {
				if k == p {
					bExist = true
					break
				}
			}
			if !bExist {
				bIncludeAll = false
				break
			}
		}
		if bIncludeAll {
			// 包含了所有维度, 不需要汇总
			for _, v := range baseField {
				fields = append(fields, v)
			}
			bSum = false
		} else {
			tmp := map[string]string{}
			for _, p := range param {
				if v, ok := baseField[p]; ok {
					// 利用map特性去重, 先保存在一个临时map中
					tmp[p] = v
					bSum = true
				}
			}
			if bSum {
				for _, v := range tmp {
					fields = append(fields, v)
				}
			}
		}
	}
	return
}

func (r *Ermcp3AreaStockReportEx) getExFieldInfo() (strFiled string, strleftJoin string) {
	if len(r.SumFields) > 0 {
		param := strings.Split(r.SumFields, ",")
		fHas := func(v string) bool {
			for i := range param {
				if param[i] == v {
					return true
				}
			}
			return false
		}
		if fHas("1") {
			if len(strFiled) > 0 {
				strFiled += ","
			}
			strFiled += "g.deliverygoodscode, g.deliverygoodsname, g.goodsunitid"
			strleftJoin += " LEFT JOIN deliverygoods g on a.deliverygoodsid = g.deliverygoodsid"
		}
		if fHas("2") {
			if len(strFiled) > 0 {
				strFiled += ","
			}
			strFiled += "w.wrstandardname, w.wrstandardcode, w.unitid"
			strleftJoin += " LEFT JOIN WRSTANDARD w on a.wrstandardid = w.wrstandardid"
		}
		if fHas("3") {
			if len(strFiled) > 0 {
				strFiled += ","
			}
			strFiled += "gb.dgfactoryitemvalue brandname"
			strleftJoin += " LEFT JOIN dgfactoryitem gb on a.spotgoodsbrandid = gb.dgfactoryitemid"
		}
		if fHas("4") {
			if len(strFiled) > 0 {
				strFiled += ","
			}
			strFiled += "h.warehousecode, h.warehousename, h.warehousetype"
			strleftJoin += " LEFT JOIN WAREHOUSEINFO h on a.warehouseinfoid=h.autoid"
		}
	}
	return
}

func (r *Ermcp3AreaStockReportEx) buildSql() (sqlId string) {
	fields, bSum := r.getSumField()
	if r.CYCLETYPE == 0 {
		// 日报表
		if bSum {
			sqlId = r.buildSqlReckonSum(fields)
		} else {
			sqlId = r.buildSqlReckon()
		}
	} else {
		// 周期报表
		if bSum {
			sqlId = r.buildSqlReportSum(fields)
		} else {
			sqlId = r.buildSqlReport()
		}
	}
	return
}

// GetDataEx 获取库存报表
func (r *Ermcp3AreaStockReportEx) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3AreaStockReportEx, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].BeginDate = r.BeginDate
		sData[i].EndDate = r.EndDate
	}
	return sData, err
}

// buildSqlReckon 日照表
func (r *Ermcp3AreaStockReportEx) buildSqlReckon() string {
	var sqlId utils.SQLVal = "SELECT t.TODAYBUYINQTY," +
		"       t.TODAYPRODUCEINQTY," +
		"       t.reckondate," +
		"       t.TODAYSELLOUTQTY," +
		"       t.TODAYPRODUCEOUTQTY," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.USERID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.WAREHOUSEINFOID," +
		"       t.deliverygoodsid," +
		"       t.ORISTOCK," +
		"       t.CURSTOCK," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid" +
		"  FROM RECKON_ERMCP_AREASTOCK t" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN dgfactoryitem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  LEFT JOIN deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  LEFT JOIN WAREHOUSEINFO h" +
		"    on t.warehouseinfoid = h.autoid" +
		" WHERE 1 = 1"

	sqlId.And("t.USERID", r.USERID)
	sqlId.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	// 现货商品id
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	// 品类id
	sqlId.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	// 品牌id
	sqlId.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0)
	// 仓库id
	sqlId.AndEx("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID, len(r.WAREHOUSEINFOID) > 0)
	sqlId.Join(" order by t.reckondate")
	return sqlId.String()
}

// buildSqlReckonSum 日照表(汇总维度)
func (r *Ermcp3AreaStockReportEx) buildSqlReckonSum(fields []string) string {
	var sqlId utils.SQLVal = "SELECT a.*, %v" +
		"  from (SELECT %v," +
		"               sum(t.ORISTOCK) ORISTOCK," +
		"               sum(t.CURSTOCK) CURSTOCK," +
		"               sum(t.TODAYBUYINQTY) TODAYBUYINQTY," +
		"               sum(t.TODAYPRODUCEINQTY) TODAYPRODUCEINQTY," +
		"               sum(t.TODAYSELLOUTQTY) TODAYSELLOUTQTY," +
		"               sum(t.TODAYPRODUCEOUTQTY) TODAYPRODUCEOUTQTY" +
		"          FROM RECKON_ERMCP_AREASTOCK t" +
		"         WHERE 1 = 1 %v" +
		"         GROUP BY %v) a %v"

	var sqlParam utils.SQLVal
	sqlParam.And("t.USERID", r.USERID)
	sqlParam.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	// 现货商品id
	sqlParam.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	// 品类id
	sqlParam.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	// 品牌id
	sqlParam.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0)
	// 仓库id
	sqlParam.AndEx("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID, len(r.WAREHOUSEINFOID) > 0)
	var strFileds string
	for _, v := range fields {
		if len(strFileds) > 0 {
			strFileds += ","
		}
		strFileds += "t." + v
	}
	exFields, exLeftJoinTable := r.getExFieldInfo()
	sqlId.FormatParam(exFields, strFileds, sqlParam.String(), strFileds, exLeftJoinTable)
	sqlId.Join(" order by a.reckondate")
	return sqlId.String()
}

// buildSqlReport 报表表
func (r *Ermcp3AreaStockReportEx) buildSqlReport() string {
	var sqlId utils.SQLVal = "SELECT t.TODAYBUYINQTY," +
		"       t.TODAYPRODUCEINQTY," +
		"       t.cycletype," +
		"       t.cycletime," +
		"       t.USERID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.WAREHOUSEINFOID," +
		"       t.deliverygoodsid," +
		"       t.TODAYSELLOUTQTY," +
		"       t.TODAYPRODUCEOUTQTY," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.ORISTOCK," +
		"       t.CURSTOCK," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid" +
		"  FROM REPORT_ERMCP_AREASTOCK t" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN dgfactoryitem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  LEFT JOIN deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  LEFT JOIN WAREHOUSEINFO h" +
		"    on t.warehouseinfoid = h.autoid" +
		" WHERE 1 = 1"

	sqlId.And("t.USERID", r.USERID)
	sqlId.And("t.cycletype", r.CYCLETYPE)
	sqlId.And("t.cycletime", r.CYCLETIME)
	// 现货商品id
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	// 品类id
	sqlId.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	// 品牌id
	sqlId.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0)
	// 仓库id
	sqlId.AndEx("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID, len(r.WAREHOUSEINFOID) > 0)
	return sqlId.String()
}

// buildSqlReport 报表表(汇总维度)
func (r *Ermcp3AreaStockReportEx) buildSqlReportSum(fields []string) string {
	var sqlId utils.SQLVal = "SELECT a.*, %v" +
		"  from (SELECT %v," +
		"               sum(t.ORISTOCK) ORISTOCK," +
		"               sum(t.CURSTOCK) CURSTOCK," +
		"               sum(t.TODAYBUYINQTY) TODAYBUYINQTY," +
		"               sum(t.TODAYPRODUCEINQTY) TODAYPRODUCEINQTY," +
		"               sum(t.TODAYSELLOUTQTY) TODAYSELLOUTQTY," +
		"               sum(t.TODAYPRODUCEOUTQTY) TODAYPRODUCEOUTQTY" +
		"          FROM REPORT_ERMCP_AREASTOCK t" +
		"         WHERE 1 = 1 %v" +
		"         GROUP BY %v) a %v"
	var sqlParam utils.SQLVal
	sqlParam.And("t.USERID", r.USERID)
	sqlParam.And("t.cycletype", r.CYCLETYPE)
	sqlParam.And("t.cycletime", r.CYCLETIME)
	// 现货商品id
	sqlParam.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	// 品类id
	sqlParam.AndEx("t.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	// 品牌id
	sqlParam.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0)
	// 仓库id
	sqlParam.AndEx("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID, len(r.WAREHOUSEINFOID) > 0)
	var strFileds string
	for _, v := range fields {
		if len(strFileds) > 0 {
			strFileds += ","
		}
		strFileds += "t." + v
	}
	exFields, exLeftJoinTable := r.getExFieldInfo()
	sqlId.FormatParam(exFields, strFileds, sqlParam.String(), strFileds, exLeftJoinTable)
	return sqlId.String()
}

// Ermcp3ArealSumPL 损益汇总表
type Ermcp3ArealSumPL struct {
	RECKONDATE      string  `json:"reckondate"  xorm:"RECKONDATE"`             // 日照时期(yyyyMMdd)
	CYCLETIME       string  `json:"cycletime"  xorm:"'CYCLETIME'"`             // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	CYCLETYPE       int32   `json:"cycletype"  xorm:"'CYCLETYPE'"`             // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	AREAUSERID      int64   `json:"areauserid"  xorm:"AREAUSERID"`             // 所属机构\交易用户ID
	MIDDLEGOODSID   int64   `json:"middlegoodsid"  xorm:"MIDDLEGOODSID"`       // 套保品种ID(SEQ_ERMS_MIDDLEGOODS)
	CURRENCYID      int32   `json:"currencyid"  xorm:"CURRENCYID"`             // 结算币种ID
	SPOTACTUALPL    float64 `json:"spotactualpl"  xorm:"SPOTACTUALPL"`         // 现货损益
	SPOTFLOATPL     float64 `json:"spotfloatpl"  xorm:"SPOTFLOATPL"`           // 现货浮动损益
	FUTUREACTUALPL  float64 `json:"futureactualpl"  xorm:"FUTUREACTUALPL"`     // 期货损益
	FUTUREFLOATPL   float64 `json:"futurefloatpl"  xorm:"FUTUREFLOATPL"`       // 期货浮动损益
	SUMACTUALPL     float64 `json:"sumactualpl"  xorm:"SUMACTUALPL"`           // 实际损益 = (ActualPL + FutureActualPL)
	SUMPL           float64 `json:"sumpl"  xorm:"'SUMPL'"`                     // 汇总损益
	UPDATETIME      string  `json:"updatetime"  xorm:"UPDATETIME"`             // 更新时间
	MIDDLEGOODSNAME string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"` // 套保品种名称
	MIDDLEGOODSCODE string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"` // 套保品种代码
	GOODSUNITID     int32   `json:"goodsunitid"  xorm:"'GOODSUNITID'"`         // 套保品种单位id

	ACCOUNTNAME  string `json:"accountname"`  // 机构名称
	ENUMDICNAME  string `json:"enumdicname"`  // 单位名称(套保品种)
	CURRENCYNAME string `json:"currencyname"` // 币种名称
	QueryType    int32  `json:"-"`            // 查询类型 1-报表 2-报表明细
	BeginDate    string `json:"begindate"`    // 开始交易日
	EndDate      string `json:"enddate"`      // 结束交易日
}

func (r *Ermcp3ArealSumPL) calc() {
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODSUNITID)
	r.ACCOUNTNAME = mtpcache.GetUserNameByUserId(r.AREAUSERID)
}

func (r *Ermcp3ArealSumPL) buildSql() string {
	// 日报表
	if r.QueryType == 1 && r.CYCLETYPE == 0 {
		return r.buildSqlDay()
	}
	// 日报表明细
	if r.QueryType == 2 && r.CYCLETYPE == 0 {
		return r.buildSqlDayDetail()
	}
	return r.buildSqlCycle()
}

// buildSqlDay 损益汇总日报表(需要汇总维度)
func (r *Ermcp3ArealSumPL) buildSqlDay() string {
	var sqlId utils.SQLVal = "SELECT t.RECKONDATE," +
		"       t.AREAUSERID," +
		"       t.CURRENCYID," +
		"       sum(t.SPOTACTUALPL) SPOTACTUALPL," +
		"       sum(t.SPOTFLOATPL) SPOTFLOATPL," +
		"       sum(t.FUTUREACTUALPL) FUTUREACTUALPL," +
		"       sum(t.FUTUREFLOATPL) FUTUREFLOATPL," +
		"       sum(t.SUMACTUALPL) SUMACTUALPL," +
		"       sum(t.SUMPL) SUMPL" +
		"  FROM RECKON_ERMCP_AREASUMPL t" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	if r.QueryType == 1 {
		sqlId.And("t.RECKONDATE", r.RECKONDATE)
	} else if r.QueryType == 2 {
		sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	sqlId.Join(" GROUP BY t.RECKONDATE, t.AREAUSERID, t.CURRENCYID")
	return sqlId.String()
}

func (r *Ermcp3ArealSumPL) buildSqlDayDetail() string {
	var sqlId utils.SQLVal = "SELECT t.RECKONDATE," +
		"       t.AREAUSERID," +
		"       t.MIDDLEGOODSID," +
		"       t.CURRENCYID," +
		"       t.SPOTACTUALPL," +
		"       t.SPOTFLOATPL," +
		"       t.FUTUREACTUALPL," +
		"       t.FUTUREFLOATPL," +
		"       t.SUMACTUALPL," +
		"       t.SUMPL," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       g.middlegoodsname," +
		"       g.middlegoodscode," +
		"       g.goodsunitid" +
		"  FROM RECKON_ERMCP_AREASUMPL t" +
		"  LEFT JOIN ERMS_MIDDLEGOODS g on t.middlegoodsid=g.middlegoodsid" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	sqlId.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	sqlId.And("t.CURRENCYID", r.CURRENCYID)
	return sqlId.String()
}

func (r *Ermcp3ArealSumPL) buildSqlCycle() string {
	var sqlId utils.SQLVal = "SELECT t.AREAUSERID," +
		"       t.CYCLETIME," +
		"       t.CYCLETYPE," +
		"       t.CURRENCYID," +
		"       sum(t.SPOTACTUALPL) SPOTACTUALPL," +
		"       sum(t.SPOTFLOATPL) SPOTFLOATPL," +
		"       sum(t.FUTUREACTUALPL) FUTUREACTUALPL," +
		"       sum(t.FUTUREFLOATPL) FUTUREFLOATPL," +
		"       sum(t.SUMACTUALPL) SUMACTUALPL," +
		"       sum(t.SUMPL) SUMPL" +
		"  FROM REPORT_ERMCP_AREASUMPL t" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	sqlId.And("t.cycletype", r.CYCLETYPE)
	sqlId.And("t.cycletime", r.CYCLETIME)
	sqlId.Join(" GROUP BY t.AREAUSERID, t.CURRENCYID, t.CYCLETIME, t.CYCLETYPE")
	return sqlId.String()
}

// GetDataEx 获取损益汇总表
func (r *Ermcp3ArealSumPL) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ArealSumPL, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].BeginDate = r.BeginDate
		sData[i].EndDate = r.EndDate
	}
	return sData, err
}

// Ermcp3TaFutuReDataReport 期货报表
type Ermcp3TaFutuReDataReport struct {
	RECKONDATE             string  `json:"reckondate"  xorm:"RECKONDATE"`                         // 日照时期(yyyyMMdd)
	CYCLETIME              string  `json:"cycletime"  xorm:"CYCLETIME"`                           // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	CYCLETYPE              int32   `json:"cycletype"  xorm:"CYCLETYPE"`                           // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	ACCOUNTID              int64   `json:"accountid"  xorm:"ACCOUNTID"`                           // 交易账户
	GOODSID                int32   `json:"goodsid"  xorm:"GOODSID"`                               // 交易合约
	BUYORSELL              int32   `json:"buyorsell"  xorm:"BUYORSELL"`                           // 持仓方向
	CURRENCYID             int32   `json:"currencyid"  xorm:"CURRENCYID"`                         // 结算币种ID
	USERID                 int64   `json:"userid"  xorm:"USERID"`                                 // 账户所属用户ID
	RELATEDUSERID          int64   `json:"relateduserid"  xorm:"RELATEDUSERID"`                   // 账户关联用户ID
	GOODSGROUPID           int32   `json:"goodsgroupid"  xorm:"GOODSGROUPID"`                     // 交易品种
	ORIHOLDQTY             float64 `json:"oriholdqty"  xorm:"ORIHOLDQTY"`                         // 期初持仓量
	ORIOPENAMOUNT          float64 `json:"oriopenamount"  xorm:"ORIOPENAMOUNT"`                   // 期初开仓额
	ORIHOLDAMOUNT          float64 `json:"oriholdamount"  xorm:"ORIHOLDAMOUNT"`                   // 期初持仓额
	TODAYOPENQTY           float64 `json:"todayopenqty"  xorm:"TODAYOPENQTY"`                     // 今开仓量
	TODAYOPENAMOUNT        float64 `json:"todayopenamount"  xorm:"TODAYOPENAMOUNT"`               // 今开仓额
	TODAYCLOSEQTY          float64 `json:"todaycloseqty"  xorm:"TODAYCLOSEQTY"`                   // 今平仓量
	TODAYCLOSEAMOUNT       float64 `json:"todaycloseamount"  xorm:"TODAYCLOSEAMOUNT"`             // 今平仓额
	HOLDQTY                float64 `json:"holdqty"  xorm:"HOLDQTY"`                               // 期末持仓量
	OPENAMOUNT             float64 `json:"openamount"  xorm:"OPENAMOUNT"`                         // 期末开仓额
	HOLDAMOUNT             float64 `json:"holdamount"  xorm:"HOLDAMOUNT"`                         // 期末持仓额
	RECKONPRICE            float64 `json:"reckonprice"  xorm:"RECKONPRICE"`                       // 结算价
	RECKONHOLDAMOUNT       float64 `json:"reckonholdamount"  xorm:"RECKONHOLDAMOUNT"`             // 结算持仓额
	CHARGE                 float64 `json:"charge"  xorm:"CHARGE"`                                 // 手续费
	CLOSEPL                float64 `json:"closepl"  xorm:"CLOSEPL"`                               // 平仓损益
	RECKONPL               float64 `json:"reckonpl"  xorm:"RECKONPL"`                             // 结算损益
	LASTPRICE              float64 `json:"lastprice"  xorm:"LASTPRICE"`                           // 最新价
	LASTHOLDAMOUNT         float64 `json:"lastholdamount"  xorm:"LASTHOLDAMOUNT"`                 // 当前持仓额
	FLOATPL                float64 `json:"floatpl"  xorm:"FLOATPL"`                               // 浮动损益
	GOODSGROUPSPOTQTY      float64 `json:"goodsgroupspotqty"  xorm:"GOODSGROUPSPOTQTY"`           // 交易品种期末量 (= 期末持仓量 * 合约乘数)
	TODAYGOODSGROUPSPOTQTY float64 `json:"todaygoodsgroupspotqty"  xorm:"TODAYGOODSGROUPSPOTQTY"` // 交易品种变化量 (=（期末持仓量 - 期初持仓量）* 合约乘数)
	MIDDLEGOODSQTY         float64 `json:"middlegoodsqty"  xorm:"MIDDLEGOODSQTY"`                 // 套保品种期末量 (=交易品种期末量 * 期货品种折算系数)
	TODAYMIDDLEGOODSQTY    float64 `json:"todaymiddlegoodsqty"  xorm:"TODAYMIDDLEGOODSQTY"`       // 套保品种变化量 (= 交易品种变化量*期货品种折算系数)
	RECKONPL2              float64 `json:"reckonpl2"  xorm:"RECKONPL2"`                           // 结算逐笔盈亏
	OUTERGROUPCODE         string  `json:"outergroupcode"  xorm:"'OUTERGROUPCODE'"`               // 交易品种代码
	GOODSGROUPNAME         string  `json:"goodsgroupname"  xorm:"'GOODSGROUPNAME'"`               // 交易品种名称
	GOODSCODE              string  `json:"goodscode"  xorm:"'GOODSCODE'"`                         // 交易合约代码
	GOODSNAME              string  `json:"goodsname"  xorm:"'GOODSNAME'"`                         // 交易合约名称
	GOODUNITID             int32   `json:"goodunitid"  xorm:"'GOODUNITID'"`                       // 交易合约单位id
	ACCOUNTNAME            string  `json:"accountname"  xorm:"'ACCOUNTNAME'"`                     // 交易账号名称
	MIDDLEGOODSID          int32   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`                 // 套保品种id
	MIDDLEGOODSNAME        string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`             // 套保商品名称
	MIDDLEGOODSCODE        string  `json:"middlgoodscode"  xorm:"'MIDDLEGOODSCODE'"`              // 套保商品代码

	OPENAVGPRICE  float64 `json:"openavgprice"`  // 开仓均价
	CLOSEAVGPRICE float64 `json:"closeavgprice"` // 平仓均价
	ENUMDICNAME   string  `json:"enumdicname"`   // 单位名称
	CURRENCYNAME  string  `json:"currencyname"`  // 币种名称
	QueryType     int32   `json:"-"`             // 查询类型 1-报表 2-报表明细
	BeginDate     string  `json:"begindate"`     // 开始交易日
	EndDate       string  `json:"enddate"`       // 结束交易日
}

func (r *Ermcp3TaFutuReDataReport) calc() {
	if r.TODAYOPENQTY != 0 {
		r.OPENAVGPRICE = r.TODAYOPENAMOUNT / r.TODAYOPENQTY
	}

	if r.TODAYCLOSEQTY != 0 {
		r.CLOSEAVGPRICE = r.TODAYCLOSEAMOUNT / r.TODAYCLOSEQTY
	}
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID)
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
}

func (r *Ermcp3TaFutuReDataReport) userType() int32 {
	var userType int32 = 2
	areaUserId := mtpcache.GetAreaUserId(r.USERID, 0)
	if areaUserId == r.USERID {
		userType = 2
	} else {
		userType = 7
	}
	return userType
}

func (r *Ermcp3TaFutuReDataReport) buildSql() string {
	userType := r.userType()
	// 日报表
	if r.QueryType == 1 && r.CYCLETYPE == 0 {
		return r.buildSqlReckon(userType)
	}
	// 日报表明细
	if r.QueryType == 2 && r.CYCLETYPE == 0 {
		return r.buildSqlReckonDetail(userType)
	}
	return r.buildSqlReport(userType)
}

// buildSqlReckon 日照表
func (r *Ermcp3TaFutuReDataReport) buildSqlReckon(userType int32) string {
	var sqlId utils.SQLVal = "select a.*," +
		"       gp.outergroupcode," +
		"       gp.goodsgroupname," +
		"       g.goodscode," +
		"       g.goodsname," +
		"       g.goodunitid," +
		"       mg.middlegoodsid," +
		"       mg.middlegoodscode," +
		"       mg.middlegoodsname" +
		"  from (SELECT t.RECKONDATE," +
		"               t.GOODSID," +
		"               t.BUYORSELL," +
		"               t.CURRENCYID," +
		"               t.%v USERID," +
		"               t.GOODSGROUPID," +
		"               t.middlegoodsid," +
		"               sum(t.ORIHOLDQTY) ORIHOLDQTY," +
		"               sum(t.ORIOPENAMOUNT) ORIOPENAMOUNT," +
		"               sum(t.ORIHOLDAMOUNT) ORIHOLDAMOUNT," +
		"               sum(t.TODAYOPENQTY) TODAYOPENQTY," +
		"               sum(t.TODAYOPENAMOUNT) TODAYOPENAMOUNT," +
		"               sum(t.TODAYCLOSEQTY) TODAYCLOSEQTY," +
		"               sum(t.TODAYCLOSEAMOUNT) TODAYCLOSEAMOUNT," +
		"               sum(t.HOLDQTY) HOLDQTY," +
		"               sum(t.OPENAMOUNT) OPENAMOUNT," +
		"               sum(t.HOLDAMOUNT) HOLDAMOUNT," +
		"               sum(t.RECKONPRICE) RECKONPRICE," +
		"               sum(t.RECKONHOLDAMOUNT) RECKONHOLDAMOUNT," +
		"               sum(t.CHARGE) CHARGE," +
		"               sum(t.CLOSEPL) CLOSEPL," +
		"               sum(t.RECKONPL) RECKONPL," +
		"               max(t.LASTPRICE) LASTPRICE," +
		"               sum(t.LASTHOLDAMOUNT) LASTHOLDAMOUNT," +
		"               sum(t.FLOATPL) FLOATPL," +
		"               sum(t.GOODSGROUPSPOTQTY) GOODSGROUPSPOTQTY," +
		"               sum(t.TODAYGOODSGROUPSPOTQTY) TODAYGOODSGROUPSPOTQTY," +
		"               sum(t.MIDDLEGOODSQTY) MIDDLEGOODSQTY," +
		"               sum(t.TODAYMIDDLEGOODSQTY) TODAYMIDDLEGOODSQTY," +
		"               sum(t.Reckonpl2) RECKONPL2" +
		"          FROM RECKON_ERMCP_TAFUTUREDATA t" +
		"         WHERE 1 = 1 %v" +
		"         GROUP BY t.RECKONDATE," +
		"                  t.GOODSID," +
		"                  t.BUYORSELL," +
		"                  t.CURRENCYID," +
		"                  t.%v," +
		"                  t.middlegoodsid," +
		"                  t.GOODSGROUPID) a" +
		"  left join goodsgroup gp" +
		"    on a.goodsgroupid = gp.goodsgroupid" +
		"  left join goods g" +
		"    on a.goodsid = g.goodsid" +
		"  left join erms_middlegoods mg on a.middlegoodsid=mg.middlegoodsid"

	userId := "USERID"
	var sqlParam utils.SQLVal
	if userType == 2 {
		sqlParam.AndEx("t.userid", r.USERID, userType == 2)
		userId = "USERID"
	} else if userType == 7 {
		sqlParam.AndEx("t.relateduserid", r.USERID, userType == 7)
		userId = "RELATEDUSERID"
	}
	sqlParam.AndEx("t.reckondate", r.RECKONDATE, len(r.RECKONDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	sqlId.FormatParam(userId, sqlParam.String(), userId)
	return sqlId.String()
}

// buildSqlReckon 日照表明细
func (r *Ermcp3TaFutuReDataReport) buildSqlReckonDetail(userType int32) string {
	var sqlId utils.SQLVal = "select a.*," +
		"       gp.outergroupcode," +
		"       gp.goodsgroupname," +
		"       g.goodscode," +
		"       g.goodsname," +
		"       g.goodunitid," +
		"       ta.accountname," +
		"       mg.middlegoodsid," +
		"       mg.middlegoodscode," +
		"       mg.middlegoodsname" +
		"  from (SELECT t.reckondate," +
		"               t.GOODSID," +
		"               t.BUYORSELL," +
		"               t.CURRENCYID," +
		"               t.%v USERID," +
		"               t.accountid," +
		"               t.GOODSGROUPID," +
		"               t.middlegoodsid," +
		"               sum(t.ORIHOLDQTY) ORIHOLDQTY," +
		"               sum(t.ORIOPENAMOUNT) ORIOPENAMOUNT," +
		"               sum(t.ORIHOLDAMOUNT) ORIHOLDAMOUNT," +
		"               sum(t.TODAYOPENQTY) TODAYOPENQTY," +
		"               sum(t.TODAYOPENAMOUNT) TODAYOPENAMOUNT," +
		"               sum(t.TODAYCLOSEQTY) TODAYCLOSEQTY," +
		"               sum(t.TODAYCLOSEAMOUNT) TODAYCLOSEAMOUNT," +
		"               sum(t.HOLDQTY) HOLDQTY," +
		"               sum(t.OPENAMOUNT) OPENAMOUNT," +
		"               sum(t.HOLDAMOUNT) HOLDAMOUNT," +
		"               sum(t.RECKONPRICE) RECKONPRICE," +
		"               sum(t.RECKONHOLDAMOUNT) RECKONHOLDAMOUNT," +
		"               sum(t.CHARGE) CHARGE," +
		"               sum(t.CLOSEPL) CLOSEPL," +
		"               sum(t.RECKONPL) RECKONPL," +
		"               max(t.LASTPRICE) LASTPRICE," +
		"               sum(t.LASTHOLDAMOUNT) LASTHOLDAMOUNT," +
		"               sum(t.FLOATPL) FLOATPL," +
		"               sum(t.GOODSGROUPSPOTQTY) GOODSGROUPSPOTQTY," +
		"               sum(t.TODAYGOODSGROUPSPOTQTY) TODAYGOODSGROUPSPOTQTY," +
		"               sum(t.MIDDLEGOODSQTY) MIDDLEGOODSQTY," +
		"               sum(t.TODAYMIDDLEGOODSQTY) TODAYMIDDLEGOODSQTY," +
		"               sum(t.Reckonpl2) RECKONPL2" +
		"          FROM RECKON_ERMCP_TAFUTUREDATA t" +
		"         WHERE 1 = 1 %v" +
		"         GROUP BY t.reckondate," +
		"                  t.GOODSID," +
		"                  t.BUYORSELL," +
		"                  t.CURRENCYID," +
		"                  t.%v," +
		"                  t.accountid," +
		"                  t.middlegoodsid," +
		"                  t.GOODSGROUPID) a" +
		"  left join goodsgroup gp" +
		"    on a.goodsgroupid = gp.goodsgroupid" +
		"  left join goods g" +
		"    on a.goodsid = g.goodsid" +
		"  left join taaccount ta on a.accountid = ta.accountid" +
		"  left join erms_middlegoods mg on a.middlegoodsid=mg.middlegoodsid"

	userId := "USERID"
	var sqlParam utils.SQLVal
	if userType == 2 {
		sqlParam.AndEx("t.userid", r.USERID, userType == 2)
		userId = "USERID"
	} else if userType == 7 {
		sqlParam.AndEx("t.relateduserid", r.USERID, userType == 7)
		userId = "RELATEDUSERID"
	}
	sqlParam.And("t.CURRENCYID", r.CURRENCYID)
	sqlParam.And("t.GOODSGROUPID", r.GOODSGROUPID)
	sqlParam.And("t.GOODSID", r.GOODSID)
	sqlParam.And("t.BUYORSELL", r.BUYORSELL)

	sqlParam.AndEx("t.reckondate", r.RECKONDATE, len(r.RECKONDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlParam.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	sqlId.FormatParam(userId, sqlParam.String(), userId)
	return sqlId.String()
}

// buildSqlReckon 报表表
func (r *Ermcp3TaFutuReDataReport) buildSqlReport(userType int32) string {
	var sqlId utils.SQLVal = "select a.*," +
		"       gp.outergroupcode," +
		"       gp.goodsgroupname," +
		"       g.goodscode," +
		"       g.goodsname," +
		"       g.goodunitid," +
		"       mg.middlegoodsid," +
		"       mg.middlegoodscode," +
		"       mg.middlegoodsname" +
		"  from (SELECT t.cycletype," +
		"               t.cycletime," +
		"               t.GOODSID," +
		"               t.BUYORSELL," +
		"               t.CURRENCYID," +
		"               t.%v USERID," +
		"               t.GOODSGROUPID," +
		"               t.middlegoodsid," +
		"               sum(t.ORIHOLDQTY) ORIHOLDQTY," +
		"               sum(t.ORIOPENAMOUNT) ORIOPENAMOUNT," +
		"               sum(t.ORIHOLDAMOUNT) ORIHOLDAMOUNT," +
		"               sum(t.TODAYOPENQTY) TODAYOPENQTY," +
		"               sum(t.TODAYOPENAMOUNT) TODAYOPENAMOUNT," +
		"               sum(t.TODAYCLOSEQTY) TODAYCLOSEQTY," +
		"               sum(t.TODAYCLOSEAMOUNT) TODAYCLOSEAMOUNT," +
		"               sum(t.HOLDQTY) HOLDQTY," +
		"               sum(t.OPENAMOUNT) OPENAMOUNT," +
		"               sum(t.HOLDAMOUNT) HOLDAMOUNT," +
		"               sum(t.RECKONPRICE) RECKONPRICE," +
		"               sum(t.RECKONHOLDAMOUNT) RECKONHOLDAMOUNT," +
		"               sum(t.CHARGE) CHARGE," +
		"               sum(t.CLOSEPL) CLOSEPL," +
		"               sum(t.RECKONPL) RECKONPL," +
		"               max(t.LASTPRICE) LASTPRICE," +
		"               sum(t.LASTHOLDAMOUNT) LASTHOLDAMOUNT," +
		"               sum(t.FLOATPL) FLOATPL," +
		"               sum(t.GOODSGROUPSPOTQTY) GOODSGROUPSPOTQTY," +
		"               sum(t.TODAYGOODSGROUPSPOTQTY) TODAYGOODSGROUPSPOTQTY," +
		"               sum(t.MIDDLEGOODSQTY) MIDDLEGOODSQTY," +
		"               sum(t.TODAYMIDDLEGOODSQTY) TODAYMIDDLEGOODSQTY," +
		"               sum(t.Reckonpl2) RECKONPL2" +
		"          FROM REPORT_ERMCP_TAFUTUREDATA t" +
		"         WHERE 1 = 1 %v" +
		"         GROUP BY t.cycletype," +
		"                  t.cycletime," +
		"                  t.GOODSID," +
		"                  t.BUYORSELL," +
		"                  t.CURRENCYID," +
		"                  t.%v," +
		"                  t.middlegoodsid," +
		"                  t.GOODSGROUPID) a" +
		"  left join goodsgroup gp" +
		"    on a.goodsgroupid = gp.goodsgroupid" +
		"  left join goods g" +
		"    on a.goodsid = g.goodsid" +
		"  left join erms_middlegoods mg on a.middlegoodsid=mg.middlegoodsid"

	userId := "USERID"
	var sqlParam utils.SQLVal
	if userType == 2 {
		sqlParam.AndEx("t.userid", r.USERID, userType == 2)
		userId = "USERID"
	} else if userType == 7 {
		sqlParam.AndEx("t.relateduserid", r.USERID, userType == 7)
		userId = "RELATEDUSERID"
	}
	sqlParam.And("t.cycletype", r.CYCLETYPE)
	sqlParam.And("t.cycletime", r.CYCLETIME)
	sqlId.FormatParam(userId, sqlParam.String(), userId)
	return sqlId.String()
}

// GetDataEx 获取期货报表
func (r *Ermcp3TaFutuReDataReport) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3TaFutuReDataReport, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].BeginDate = r.BeginDate
		sData[i].EndDate = r.EndDate
	}
	return sData, err
}

// ReckonDayPosition 持仓头寸日照
type ReckonDayPosition struct {
	ACCOUNTID           int64   `json:"accountid"  xorm:"ACCOUNTID"`                     // 账号Id
	GOODSID             int32   `json:"goodsid"  xorm:"GOODSID"`                         // 商品Id
	RECKONDATE          string  `json:"reckondate"  xorm:"RECKONDATE"`                   // 日照日期(yyyyMMdd)
	BUYPOSITIONQTY      int32   `json:"buypositionqty"  xorm:"BUYPOSITIONQTY"`           // 买期初持仓数量
	BUYHOLDERAMOUNT     float64 `json:"buyholderamount"  xorm:"BUYHOLDERAMOUNT"`         // 买持仓期初总金额
	BUYCURPOSITIONQTY   int32   `json:"buycurpositionqty"  xorm:"BUYCURPOSITIONQTY"`     // 买当前持仓总数量
	BUYCURHOLDERAMOUNT  float64 `json:"buycurholderamount"  xorm:"BUYCURHOLDERAMOUNT"`   // 买当前持仓总金额
	BUYFROZENQTY        int32   `json:"buyfrozenqty"  xorm:"BUYFROZENQTY"`               // 买持仓冻结
	BUYOTHERFROZENQTY   int32   `json:"buyotherfrozenqty"  xorm:"BUYOTHERFROZENQTY"`     // 买持仓其他冻结(交割冻结)
	BUYOPENREQQTY       int32   `json:"buyopenreqqty"  xorm:"BUYOPENREQQTY"`             // 买开仓申请数量
	BUYRECKONPL         float64 `json:"buyreckonpl"  xorm:"BUYRECKONPL"`                 // 买结算盈亏
	BUYINTEREST         float64 `json:"buyinterest"  xorm:"BUYINTEREST"`                 // 买递延费(仓储费)
	BUYUSEDMARGIN       float64 `json:"buyusedmargin"  xorm:"BUYUSEDMARGIN"`             // 占用保证金
	BUYOPENTOTALQTY     int32   `json:"buyopentotalqty"  xorm:"BUYOPENTOTALQTY"`         // 今日买开仓总数量
	BUYCLOSETOTALQTY    int32   `json:"buyclosetotalqty"  xorm:"BUYCLOSETOTALQTY"`       // 今日买平仓总数量
	BUYCLOSETOTALPL     float64 `json:"buyclosetotalpl"  xorm:"BUYCLOSETOTALPL"`         // 买平仓总盈亏
	SELLPOSITIONQTY     int32   `json:"sellpositionqty"  xorm:"SELLPOSITIONQTY"`         // 卖期初持仓数量
	SELLHOLDERAMOUNT    float64 `json:"sellholderamount"  xorm:"SELLHOLDERAMOUNT"`       // 卖持仓期初总金额
	SELLCURPOSITIONQTY  int32   `json:"sellcurpositionqty"  xorm:"SELLCURPOSITIONQTY"`   // 卖当前持仓数量
	SELLCURHOLDERAMOUNT float64 `json:"sellcurholderamount"  xorm:"SELLCURHOLDERAMOUNT"` // 卖当前持仓总金额
	SELLFROZENQTY       int32   `json:"sellfrozenqty"  xorm:"SELLFROZENQTY"`             // 卖持仓冻结
	SELLOTHERFROZENQTY  int32   `json:"sellotherfrozenqty"  xorm:"SELLOTHERFROZENQTY"`   // 卖持仓其他冻结(交割冻结)
	SELLOPENREQQTY      int32   `json:"sellopenreqqty"  xorm:"SELLOPENREQQTY"`           // 卖开仓申请数量
	SELLRECKONPL        float64 `json:"sellreckonpl"  xorm:"SELLRECKONPL"`               // 卖结算盈亏
	SELLINTEREST        float64 `json:"sellinterest"  xorm:"SELLINTEREST"`               // 卖递延费
	SELLUSEDMARGIN      float64 `json:"sellusedmargin"  xorm:"SELLUSEDMARGIN"`           // 卖占用保证金-作废
	SELLOPENTOTALQTY    int32   `json:"sellopentotalqty"  xorm:"SELLOPENTOTALQTY"`       // 卖开仓总数量
	SELLCLOSETOTALQTY   int32   `json:"sellclosetotalqty"  xorm:"SELLCLOSETOTALQTY"`     // 卖平仓总数量
	SELLCLOSETOTALPL    float64 `json:"sellclosetotalpl"  xorm:"SELLCLOSETOTALPL"`       // 卖平仓总盈亏
	TRADEPROPERTY       int32   `json:"tradeproperty"  xorm:"TRADEPROPERTY"`             // 交易属性
	MARGINALGORITHM     int32   `json:"marginalgorithm"  xorm:"MARGINALGORITHM"`         // 保证金收取方式 - 1：比率 2：固定
	MARGINVALUE         float64 `json:"marginvalue"  xorm:"MARGINVALUE"`                 // 保证金设置值(投资管理系统-多LongMarginValue)
	BUYFEEALGORITHM     int32   `json:"buyfeealgorithm"  xorm:"BUYFEEALGORITHM"`         // 买递延费收取方式 - 1:比率  2:固定
	BUYMEMBERFEEVALUE   float64 `json:"buymemberfeevalue"  xorm:"BUYMEMBERFEEVALUE"`     // 买会员递延费设置值
	BUYEXCHAGEFEEVALUE  float64 `json:"buyexchagefeevalue"  xorm:"BUYEXCHAGEFEEVALUE"`   // 买交易所递延费设置值
	SELLFEEALGORITHM    int32   `json:"sellfeealgorithm"  xorm:"SELLFEEALGORITHM"`       // 卖递延费收取方式 - 1:比率  2:固定
	SELLMEMBERFEEVALUE  float64 `json:"sellmemberfeevalue"  xorm:"SELLMEMBERFEEVALUE"`   // 卖会员递延费设置值
	SELLEXCHAGEFEEVALUE float64 `json:"sellexchagefeevalue"  xorm:"SELLEXCHAGEFEEVALUE"` // 卖交易所递延费设置值
	INTEREST2           float64 `json:"interest2"  xorm:"INTEREST2"`                     // 过夜费
	ACCOUNTCURRENCYID   int32   `json:"accountcurrencyid"  xorm:"ACCOUNTCURRENCYID"`     // 账户币种ID
	GOODSCURRENCYID     int32   `json:"goodscurrencyid"  xorm:"GOODSCURRENCYID"`         // 商品币种ID
	CUREXCHANGERATE     float64 `json:"curexchangerate"  xorm:"CUREXCHANGERATE"`         // 当前汇率
	BUYRECKONPL2        float64 `json:"buyreckonpl2"  xorm:"BUYRECKONPL2"`               // 买结算盈亏(逐笔) - 根据持仓单日照表字段汇率
	SELLRECKONPL2       float64 `json:"sellreckonpl2"  xorm:"SELLRECKONPL2"`             // 结算盈亏(逐笔) - 根据持仓单日照表字段汇率
	BUYCURTDPOSITION    int32   `json:"buycurtdposition"  xorm:"BUYCURTDPOSITION"`       // 买期末今日头寸
	BUYFRETDPOSITION    int32   `json:"buyfretdposition"  xorm:"BUYFRETDPOSITION"`       // 买冻结今日头寸
	SELLCURTDPOSITION   int32   `json:"sellcurtdposition"  xorm:"SELLCURTDPOSITION"`     // 卖期末今日头寸
	SELLFRETDPOSITION   int32   `json:"sellfretdposition"  xorm:"SELLFRETDPOSITION"`     // 卖冻结今日头寸
	INTERESTCHARGE      float64 `json:"interestcharge"  xorm:"INTERESTCHARGE"`           // 利息(110)
	SHORTMARGINVALUE    float64 `json:"shortmarginvalue"  xorm:"SHORTMARGINVALUE"`       // 保证金设置值(投资管理系统-空)
	BUYDEFERCHARGE      float64 `json:"buydefercharge"  xorm:"BUYDEFERCHARGE"`           // 买递延费2(仓储费2)
	SELLDEFERCHARGE     float64 `json:"selldefercharge"  xorm:"SELLDEFERCHARGE"`         // 卖递延费2

	RELATEDUSERID      int64   `json:"relateduserid"  xorm:"'RELATEDUSERID'"`           // 关联用户id
	MIDDLEGOODSID      int32   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`           // 套保商品id
	MIDDLEGOODSCODE    string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"`       // 套保商品代码
	MIDDLEGOODSNAME    string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`       // 套保商品名称
	NEEDHEDGERATIO     float64 `json:"needhedgeratio"  xorm:"'NEEDHEDGERATIO'"`         // 应套保比例
	NEEDARBITRAGERATIO float64 `json:"needarbitrageratio"  xorm:"'NEEDARBITRAGERATIO'"` // 应套利比例
	GOODSGROUPID       int32   `json:"goodsgroupid"  xorm:"'GOODSGROUPID'"`             // 交易品种id
	CONVERTRATIO       float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"`             // 折算系数
	GOODSCODE          string  `json:"goodscode"  xorm:"'GOODSCODE'"`                   // 交易商品代码
	GOODSNAME          string  `json:"goodsname"  xorm:"'GOODSNAME'"`                   // 交易商品名称
	GOODUNITID         int32   `json:"goodunitid"  xorm:"'GOODUNITID'"`                 // 交易商品单位id
	AGREEUNIT          float64 `json:"agreeunit"  xorm:"'AGREEUNIT'"`                   // 合约乘数
	ENUMDICNAME        string  `json:"enumdicname"`                                     // 单位名称(交易商品)
	ACCOUNTNAME        string  `json:"accountname"  xorm:"'ACCOUNTNAME'"`               // 交易账户

	DiffBuyQty        float64 `json:"diffbuyqty"`        // 交易品种今变化量(买)
	DiffMgBuyQty      float64 `json:"diffmgbuyqty"`      // 套保品种今变化量(买)
	DiffNeedMgBuyQty  float64 `json:"diffneedmgbuyqty"`  // 应套保总量变化量(买)
	DiffSellQty       float64 `json:"diffsellqty"`       // 交易品种今变化量(卖)
	DiffMgSellQty     float64 `json:"diffmgsellqty"`     // 套保品种今变化量(卖)
	DiffNeedMgSellQty float64 `json:"diffneedmgsellqty"` // 应套保总量变化量(卖)

	BeginDate string `json:"begindate"` // 开始交易日
	EndDate   string `json:"enddate"`   // 结束交易日
}

func (r *ReckonDayPosition) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID)
	if r.BUYPOSITIONQTY > 0 || r.BUYCURPOSITIONQTY > 0 {
		r.DiffBuyQty = float64(r.BUYCURPOSITIONQTY) * r.AGREEUNIT
		r.DiffMgBuyQty = float64(r.BUYCURPOSITIONQTY) * r.AGREEUNIT * r.CONVERTRATIO
		r.DiffNeedMgBuyQty = float64(r.BUYCURPOSITIONQTY) * r.AGREEUNIT * r.CONVERTRATIO * r.NEEDHEDGERATIO
	}
	if r.SELLPOSITIONQTY > 0 || r.SELLCURPOSITIONQTY > 0 {
		r.DiffBuyQty = float64(r.SELLCURPOSITIONQTY) * r.AGREEUNIT
		r.DiffMgBuyQty = float64(r.SELLCURPOSITIONQTY) * r.AGREEUNIT * r.CONVERTRATIO
		r.DiffNeedMgBuyQty = float64(r.SELLCURPOSITIONQTY) * r.AGREEUNIT * r.CONVERTRATIO * r.NEEDHEDGERATIO
	}
}

func (r *ReckonDayPosition) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.middlegoodsid," +
		"         t.middlegoodscode," +
		"         t.middlegoodsname," +
		"         t.needhedgeratio," +
		"         t.needarbitrageratio," +
		"         t.goodsgroupid," +
		"         c.convertratio," +
		"         g.goodsid," +
		"         g.goodscode," +
		"         g.goodsname," +
		"         g.agreeunit," +
		"         g.goodunitid" +
		"    from erms_middlegoods t" +
		"   inner join ermcp_ggconvertconfig c" +
		"      on t.goodsgroupid = c.destgoodsgroupid" +
		"   inner join goods g" +
		"      on g.goodsgroupid = c.srcgoodsgroupid)" +
		"SELECT t.ACCOUNTID," +
		"       t.GOODSID," +
		"       t.RECKONDATE," +
		"       t.BUYPOSITIONQTY," +
		"       t.BUYHOLDERAMOUNT," +
		"       t.BUYCURPOSITIONQTY," +
		"       t.BUYCURHOLDERAMOUNT," +
		"       t.BUYFROZENQTY," +
		"       t.BUYOTHERFROZENQTY," +
		"       t.BUYOPENREQQTY," +
		"       t.BUYRECKONPL," +
		"       t.BUYINTEREST," +
		"       t.BUYUSEDMARGIN," +
		"       t.BUYOPENTOTALQTY," +
		"       t.BUYCLOSETOTALQTY," +
		"       t.BUYCLOSETOTALPL," +
		"       t.SELLPOSITIONQTY," +
		"       t.SELLHOLDERAMOUNT," +
		"       t.SELLCURPOSITIONQTY," +
		"       t.SELLCURHOLDERAMOUNT," +
		"       t.SELLFROZENQTY," +
		"       t.SELLOTHERFROZENQTY," +
		"       t.SELLOPENREQQTY," +
		"       t.SELLRECKONPL," +
		"       t.SELLINTEREST," +
		"       t.SELLUSEDMARGIN," +
		"       t.SELLOPENTOTALQTY," +
		"       t.SELLCLOSETOTALQTY," +
		"       t.SELLCLOSETOTALPL," +
		"       t.TRADEPROPERTY," +
		"       t.MARGINALGORITHM," +
		"       t.MARGINVALUE," +
		"       t.BUYFEEALGORITHM," +
		"       t.BUYMEMBERFEEVALUE," +
		"       t.BUYEXCHAGEFEEVALUE," +
		"       t.SELLFEEALGORITHM," +
		"       t.SELLMEMBERFEEVALUE," +
		"       t.SELLEXCHAGEFEEVALUE," +
		"       t.INTEREST2," +
		"       t.ACCOUNTCURRENCYID," +
		"       t.GOODSCURRENCYID," +
		"       t.CUREXCHANGERATE," +
		"       t.BUYRECKONPL2," +
		"       t.SELLRECKONPL2," +
		"       t.BUYCURTDPOSITION," +
		"       t.BUYFRETDPOSITION," +
		"       t.SELLCURTDPOSITION," +
		"       t.SELLFRETDPOSITION," +
		"       t.INTERESTCHARGE," +
		"       t.SHORTMARGINVALUE," +
		"       t.BUYDEFERCHARGE," +
		"       t.SELLDEFERCHARGE," +
		"       ta.relateduserid," +
		"       ta.accountname," +
		"       tmp.*" +
		"  FROM RECKON_DAYPOSITION t" +
		" INNER JOIN tmp" +
		"    on t.goodsid = tmp.goodsid" +
		" INNER JOIN TAACCOUNT ta" +
		"    on t.accountid = ta.accountid" +
		" WHERE 1 = 1" +
		"   and (t.buypositionqty > 0 or t.buycurpositionqty > 0 or t.SELLPOSITIONQTY > 0 or t.SELLCURPOSITIONQTY > 0)"
	sqlId.And("ta.RELATEDUSERID", r.RELATEDUSERID)
	sqlId.And("tmp.MIDDLEGOODSID", r.MIDDLEGOODSID)
	sqlId.AndEx("t.RECKONDATE", r.RECKONDATE, len(r.RECKONDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	return sqlId.String()
}

// GetDataEx 获取持仓头寸日照
func (r *ReckonDayPosition) GetDataEx() ([]ReckonDayPosition, error) {
	sData := make([]ReckonDayPosition, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// HISOUTTRADEPOSITION 外部头寸日照
type HISOUTTRADEPOSITION struct {
	HISTRADEDATE      string  `json:"histradedate"  xorm:"'HISTRADEDATE'"`         // 日期
	ISVALIDDATA       int32   `json:"isvaliddata"  xorm:"'ISVALIDDATA'"`           // 是否有效
	ACCOUNTID         int64   `json:"accountid"  xorm:"ACCOUNTID"`                 // 资金账号[外部母账户]
	HEDGEGOODSID      int32   `json:"hedgegoodsid"  xorm:"HEDGEGOODSID"`           // 对冲合约ID
	HEDGEACCOUNTCODE  string  `json:"hedgeaccountcode"  xorm:"HEDGEACCOUNTCODE"`   // 对冲账号
	TRADEDATE         string  `json:"tradedate"  xorm:"TRADEDATE"`                 // 交易日(yyyyMMdd)
	MARKETID          int32   `json:"marketid"  xorm:"MARKETID"`                   // 市场ID
	YDBUYPOSITION     int32   `json:"ydbuyposition"  xorm:"YDBUYPOSITION"`         // 期初买头寸
	CURBUYPOSITION    int32   `json:"curbuyposition"  xorm:"CURBUYPOSITION"`       // 期末买头寸
	CURYDBUYPOSITION  int32   `json:"curydbuyposition"  xorm:"CURYDBUYPOSITION"`   // 期末上日买头寸
	CURTDBUYPOSITION  int32   `json:"curtdbuyposition"  xorm:"CURTDBUYPOSITION"`   // 期末今日买头寸
	FREYDBUYPOSITION  int32   `json:"freydbuyposition"  xorm:"FREYDBUYPOSITION"`   // 冻结上日买头寸
	FRETDBUYPOSITION  int32   `json:"fretdbuyposition"  xorm:"FRETDBUYPOSITION"`   // 冻结今日买头寸
	YDSELLPOSITION    int32   `json:"ydsellposition"  xorm:"YDSELLPOSITION"`       // 期初卖头寸
	CURSELLPOSITION   int32   `json:"cursellposition"  xorm:"CURSELLPOSITION"`     // 期末卖头寸
	CURYDSELLPOSITION int32   `json:"curydsellposition"  xorm:"CURYDSELLPOSITION"` // 期末上日卖头寸
	CURTDSELLPOSITION int32   `json:"curtdsellposition"  xorm:"CURTDSELLPOSITION"` // 期末今日卖头寸
	FREYDSELLPOSITION int32   `json:"freydsellposition"  xorm:"FREYDSELLPOSITION"` // 冻结上日卖头寸
	FRETDSELLPOSITION int32   `json:"fretdsellposition"  xorm:"FRETDSELLPOSITION"` // 冻结今日卖头寸
	BUYOPENCOST       float64 `json:"buyopencost"  xorm:"BUYOPENCOST"`             // 买开仓成本
	BUYPOSITIONCOST   float64 `json:"buypositioncost"  xorm:"BUYPOSITIONCOST"`     // 买持仓成本
	BUYUSEMARGIN      float64 `json:"buyusemargin"  xorm:"BUYUSEMARGIN"`           // 买占用保证金
	SELLOPENCOST      float64 `json:"sellopencost"  xorm:"SELLOPENCOST"`           // 卖开仓成本
	SELLPOSITIONCOST  float64 `json:"sellpositioncost"  xorm:"SELLPOSITIONCOST"`   // 卖持仓成本
	SELLUSEMARGIN     float64 `json:"sellusemargin"  xorm:"SELLUSEMARGIN"`         // 卖占用保证金

	RELATEDUSERID      int64   `json:"relateduserid"  xorm:"'RELATEDUSERID'"`           // 关联用户id
	MIDDLEGOODSID      int32   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`           // 套保商品id
	MIDDLEGOODSCODE    string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"`       // 套保商品代码
	MIDDLEGOODSNAME    string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`       // 套保商品名称
	NEEDHEDGERATIO     float64 `json:"needhedgeratio"  xorm:"'NEEDHEDGERATIO'"`         // 应套保比例
	NEEDARBITRAGERATIO float64 `json:"needarbitrageratio"  xorm:"'NEEDARBITRAGERATIO'"` // 应套利比例
	GOODSGROUPID       int32   `json:"goodsgroupid"  xorm:"'GOODSGROUPID'"`             // 交易品种id
	CONVERTRATIO       float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"`             // 折算系数
	GOODSCODE          string  `json:"goodscode"  xorm:"'GOODSCODE'"`                   // 交易商品代码
	GOODSNAME          string  `json:"goodsname"  xorm:"'GOODSNAME'"`                   // 交易商品名称
	GOODUNITID         int32   `json:"goodunitid"  xorm:"'GOODUNITID'"`                 // 交易商品单位id
	AGREEUNIT          float64 `json:"agreeunit"  xorm:"'AGREEUNIT'"`                   // 合约乘数
	ENUMDICNAME        string  `json:"enumdicname"`                                     // 单位名称(交易商品)
	ACCOUNTNAME        string  `json:"accountname"  xorm:"'ACCOUNTNAME'"`               // 交易账户

	DiffBuyQty        float64 `json:"diffbuyqty"`        // 交易品种今变化量(买)
	DiffMgBuyQty      float64 `json:"diffmgbuyqty"`      // 套保品种今变化量(买)
	DiffNeedMgBuyQty  float64 `json:"diffneedmgbuyqty"`  // 应套保总量变化量(买)
	DiffSellQty       float64 `json:"diffsellqty"`       // 交易品种今变化量(卖)
	DiffMgSellQty     float64 `json:"diffmgsellqty"`     // 套保品种今变化量(卖)
	DiffNeedMgSellQty float64 `json:"diffneedmgsellqty"` // 应套保总量变化量(卖)

	BeginDate string `json:"begindate"` // 开始交易日
	EndDate   string `json:"enddate"`   // 结束交易日
}

func (r *HISOUTTRADEPOSITION) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID)
	if r.YDBUYPOSITION > 0 || r.CURBUYPOSITION > 0 {
		r.DiffBuyQty = float64(r.CURBUYPOSITION) * r.AGREEUNIT
		r.DiffMgBuyQty = float64(r.CURBUYPOSITION) * r.AGREEUNIT * r.CONVERTRATIO
		r.DiffNeedMgBuyQty = float64(r.CURBUYPOSITION) * r.AGREEUNIT * r.CONVERTRATIO * r.NEEDHEDGERATIO
	}
	if r.YDSELLPOSITION > 0 || r.CURSELLPOSITION > 0 {
		r.DiffBuyQty = float64(r.CURSELLPOSITION) * r.AGREEUNIT
		r.DiffMgBuyQty = float64(r.CURSELLPOSITION) * r.AGREEUNIT * r.CONVERTRATIO
		r.DiffNeedMgBuyQty = float64(r.CURSELLPOSITION) * r.AGREEUNIT * r.CONVERTRATIO * r.NEEDHEDGERATIO
	}
}

func (r *HISOUTTRADEPOSITION) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.middlegoodsid," +
		"         t.middlegoodscode," +
		"         t.middlegoodsname," +
		"         t.needhedgeratio," +
		"         t.needarbitrageratio," +
		"         t.goodsgroupid," +
		"         c.convertratio," +
		"         g.goodsid," +
		"         g.goodscode," +
		"         g.goodsname," +
		"         g.agreeunit," +
		"         g.goodunitid" +
		"    from erms_middlegoods t" +
		"   inner join ermcp_ggconvertconfig c" +
		"      on t.goodsgroupid = c.destgoodsgroupid" +
		"   inner join goods g" +
		"      on g.goodsgroupid = c.srcgoodsgroupid)" +
		"SELECT t.HISTRADEDATE," +
		"       t.ISVALIDDATA," +
		"       t.ACCOUNTID," +
		"       t.HEDGEGOODSID," +
		"       t.HEDGEACCOUNTCODE," +
		"       t.TRADEDATE," +
		"       t.MARKETID," +
		"       t.YDBUYPOSITION," +
		"       t.CURBUYPOSITION," +
		"       t.CURYDBUYPOSITION," +
		"       t.CURTDBUYPOSITION," +
		"       t.FREYDBUYPOSITION," +
		"       t.FRETDBUYPOSITION," +
		"       t.YDSELLPOSITION," +
		"       t.CURSELLPOSITION," +
		"       t.CURYDSELLPOSITION," +
		"       t.CURTDSELLPOSITION," +
		"       t.FREYDSELLPOSITION," +
		"       t.FRETDSELLPOSITION," +
		"       t.BUYOPENCOST," +
		"       t.BUYPOSITIONCOST," +
		"       t.BUYUSEMARGIN," +
		"       t.SELLOPENCOST," +
		"       t.SELLPOSITIONCOST," +
		"       t.SELLUSEMARGIN," +
		"       ta.relateduserid," +
		"       ta.accountname," +
		"       tmp.*" +
		"  FROM HIS_HEDGE_OUTTRADEPOSITION t" +
		" INNER JOIN tmp" +
		"    on t.HEDGEGOODSID = tmp.goodsid" +
		" INNER JOIN TAACCOUNT ta" +
		"    on t.accountid = ta.accountid" +
		" WHERE 1 = 1" +
		"   and (t.YDBUYPOSITION > 0 or t.CURBUYPOSITION > 0 or t.YDSELLPOSITION > 0 or t.CURSELLPOSITION > 0)"
	sqlId.And("ta.RELATEDUSERID", r.RELATEDUSERID)
	sqlId.And("tmp.MIDDLEGOODSID", r.MIDDLEGOODSID)
	sqlId.AndEx("t.HISTRADEDATE", r.HISTRADEDATE, len(r.HISTRADEDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.HISTRADEDATE >= '%v' and t.HISTRADEDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	return sqlId.String()
}

// GetDataEx 获取外部头寸日照
func (r *HISOUTTRADEPOSITION) GetDataEx() ([]HISOUTTRADEPOSITION, error) {
	sData := make([]HISOUTTRADEPOSITION, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3ExposureFutuDetail 敞口报表期货明细
type Ermcp3ExposureFutuDetail struct {
	ACCOUNTID       int64   `json:"accountid"`       // 交易账户id
	ACCOUNTNAME     string  `json:"accountname"`     // 交易账户
	GOODSID         int32   `json:"goodsid"`         // 商品id
	GOODSCODE       string  `json:"goodscode"`       // 商品代码
	GOODSNAME       string  `json:"goodsname"`       // 商品名称
	BUYORSELL       int32   `json:"buyorsell"`       // 方向 0-买 1-卖
	ORIHOLDQTY      float64 `json:"oriholdqty"`      // 期初持仓量
	HOLDQTY         float64 `json:"holdqty"`         // 期末持仓量
	TODAYOPENQTY    float64 `json:"todayopenqty"`    // 今开仓量
	TODAYCLOSEQTY   float64 `json:"todaycloseqty"`   // 今平仓量
	DiffTradeQty    float64 `json:"difftradeqty"`    // 交易品种今变化量
	DiffMgQty       float64 `json:"diffmgqty"`       // 套保品种今变化量
	DiffNeedQty     float64 `json:"diffneedqty"`     // 应套保总量变化量
	MIDDLEGOODSID   int32   `json:"middlegoodsid"`   // 套保商品id
	MIDDLEGOODSCODE string  `json:"middlegoodscode"` // 套保商品代码
	MIDDLEGOODSNAME string  `json:"middlegoodsname"` // 套保商品名称
	ENUMDICNAME     string  `json:"enumdicname"`     // 单位名称

	USERID     int64  `json:"-"`         // 用户id
	RECKONDATE string `json:"tradedate"` // 交易日
	BeginDate  string `json:"begindate"` // 开始交易日
	EndDate    string `json:"enddate"`   // 结束交易日
}

func (r *Ermcp3ExposureFutuDetail) fromOutPositionBuy(v *HISOUTTRADEPOSITION) {
	r.ACCOUNTID = v.ACCOUNTID
	r.ACCOUNTNAME = v.ACCOUNTNAME
	r.GOODSID = v.HEDGEGOODSID
	r.GOODSCODE = v.GOODSCODE
	r.GOODSNAME = v.GOODSNAME
	r.BUYORSELL = 0
	r.ORIHOLDQTY = float64(v.YDBUYPOSITION)
	r.HOLDQTY = float64(v.CURBUYPOSITION)
	r.TODAYOPENQTY = 0
	r.TODAYCLOSEQTY = 0
	r.DiffTradeQty = v.DiffBuyQty
	r.DiffMgQty = v.DiffMgBuyQty
	r.DiffNeedQty = v.DiffNeedMgBuyQty
	r.MIDDLEGOODSID = v.MIDDLEGOODSID
	r.MIDDLEGOODSCODE = v.MIDDLEGOODSCODE
	r.MIDDLEGOODSNAME = v.MIDDLEGOODSNAME
	r.ENUMDICNAME = v.ENUMDICNAME
	r.RECKONDATE = v.HISTRADEDATE
}

func (r *Ermcp3ExposureFutuDetail) fromOutPositionSell(v *HISOUTTRADEPOSITION) {
	r.ACCOUNTID = v.ACCOUNTID
	r.ACCOUNTNAME = v.ACCOUNTNAME
	r.GOODSID = v.HEDGEGOODSID
	r.GOODSCODE = v.GOODSCODE
	r.GOODSNAME = v.GOODSNAME
	r.BUYORSELL = 1
	r.ORIHOLDQTY = float64(v.YDSELLPOSITION)
	r.HOLDQTY = float64(v.CURSELLPOSITION)
	r.TODAYOPENQTY = 0
	r.TODAYCLOSEQTY = 0
	r.DiffTradeQty = v.DiffSellQty
	r.DiffMgQty = v.DiffMgSellQty
	r.DiffNeedQty = v.DiffNeedMgSellQty
	r.MIDDLEGOODSID = v.MIDDLEGOODSID
	r.MIDDLEGOODSCODE = v.MIDDLEGOODSCODE
	r.MIDDLEGOODSNAME = v.MIDDLEGOODSNAME
	r.ENUMDICNAME = v.ENUMDICNAME
	r.RECKONDATE = v.HISTRADEDATE
}

func (r *Ermcp3ExposureFutuDetail) fromTradePositionBuy(v *ReckonDayPosition) {
	r.ACCOUNTID = v.ACCOUNTID
	r.ACCOUNTNAME = v.ACCOUNTNAME
	r.GOODSID = v.GOODSID
	r.GOODSCODE = v.GOODSCODE
	r.GOODSNAME = v.GOODSNAME
	r.BUYORSELL = 0
	r.ORIHOLDQTY = float64(v.BUYPOSITIONQTY)
	r.HOLDQTY = float64(v.BUYCURPOSITIONQTY)
	r.TODAYOPENQTY = 0
	r.TODAYCLOSEQTY = 0
	r.DiffTradeQty = v.DiffBuyQty
	r.DiffMgQty = v.DiffMgBuyQty
	r.DiffNeedQty = v.DiffNeedMgBuyQty
	r.MIDDLEGOODSID = v.MIDDLEGOODSID
	r.MIDDLEGOODSCODE = v.MIDDLEGOODSCODE
	r.MIDDLEGOODSNAME = v.MIDDLEGOODSNAME
	r.ENUMDICNAME = v.ENUMDICNAME
	r.RECKONDATE = v.RECKONDATE
}

func (r *Ermcp3ExposureFutuDetail) fromTradePositionSell(v *ReckonDayPosition) {
	r.ACCOUNTID = v.ACCOUNTID
	r.ACCOUNTNAME = v.ACCOUNTNAME
	r.GOODSID = v.GOODSID
	r.GOODSCODE = v.GOODSCODE
	r.GOODSNAME = v.GOODSNAME
	r.BUYORSELL = 1
	r.ORIHOLDQTY = float64(v.SELLPOSITIONQTY)
	r.HOLDQTY = float64(v.SELLCURPOSITIONQTY)
	r.TODAYOPENQTY = 0
	r.TODAYCLOSEQTY = 0
	r.DiffTradeQty = v.DiffSellQty
	r.DiffMgQty = v.DiffMgSellQty
	r.DiffNeedQty = v.DiffNeedMgSellQty
	r.MIDDLEGOODSID = v.MIDDLEGOODSID
	r.MIDDLEGOODSCODE = v.MIDDLEGOODSCODE
	r.MIDDLEGOODSNAME = v.MIDDLEGOODSNAME
	r.ENUMDICNAME = v.ENUMDICNAME
	r.RECKONDATE = v.RECKONDATE
}

// GetDataEx 获取敞口报表期货明细
func (r *Ermcp3ExposureFutuDetail) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ExposureFutuDetail, 0)
	if mtpcache.IsAreaUserId(r.USERID) {
		// 查母账户头寸
		m := HISOUTTRADEPOSITION{RELATEDUSERID: r.USERID, HISTRADEDATE: r.RECKONDATE,
			BeginDate: r.BeginDate, EndDate: r.EndDate, MIDDLEGOODSID: r.MIDDLEGOODSID}
		d, _ := m.GetDataEx()
		for _, v := range d {
			if v.YDBUYPOSITION > 0 || v.CURBUYPOSITION > 0 {
				val := Ermcp3ExposureFutuDetail{}
				val.fromOutPositionBuy(&v)
				val.BeginDate = r.BeginDate
				val.EndDate = r.EndDate
				sData = append(sData, val)
			}
			if v.YDSELLPOSITION > 0 || v.CURSELLPOSITION > 0 {
				val := Ermcp3ExposureFutuDetail{}
				val.fromOutPositionSell(&v)
				val.BeginDate = r.BeginDate
				val.EndDate = r.EndDate
				sData = append(sData, val)
			}
		}
	} else {
		// 查子账户头寸
		m := ReckonDayPosition{RELATEDUSERID: r.USERID, RECKONDATE: r.RECKONDATE,
			BeginDate: r.BeginDate, EndDate: r.EndDate, MIDDLEGOODSID: r.MIDDLEGOODSID}
		d, _ := m.GetDataEx()
		for _, v := range d {
			if v.BUYPOSITIONQTY > 0 || v.BUYCURPOSITIONQTY > 0 {
				val := Ermcp3ExposureFutuDetail{}
				val.fromTradePositionBuy(&v)
				val.BeginDate = r.BeginDate
				val.EndDate = r.EndDate
				sData = append(sData, val)
			}
			if v.SELLPOSITIONQTY > 0 || v.SELLCURPOSITIONQTY > 0 {
				val := Ermcp3ExposureFutuDetail{}
				val.fromTradePositionSell(&v)
				val.BeginDate = r.BeginDate
				val.EndDate = r.EndDate
				sData = append(sData, val)
			}
		}
	}
	return sData, nil
}

// Ermcp3ExposureParamChLog 敞口报表参数调整明细
type Ermcp3ExposureParamChLog struct {
	LOGID            string  `json:"logid"  xorm:"LOGID"`                       // 流水ID(609+Unix秒时间戳(10位)+xxxxxx)
	MIDDLEGOODSID    int64   `json:"middlegoodsid"  xorm:"MIDDLEGOODSID"`       // 套保品种
	AREAUSERID       int64   `json:"areauserid"  xorm:"AREAUSERID"`             // 所属机构\交易用户ID
	ACCOUNTID        int64   `json:"accountid"  xorm:"ACCOUNTID"`               // 期货账户ID (作废, 默认为0)
	CHANGELOGTYPE    int32   `json:"changelogtype"  xorm:"CHANGELOGTYPE"`       // 变更流水类型 - 1:套保比率变更 2:套利比率变更
	PARAMBEFOREVALUE float64 `json:"parambeforevalue"  xorm:"PARAMBEFOREVALUE"` // 参数变更前(调整前参数值)
	PARAMAFTERVALUE  float64 `json:"paramaftervalue"  xorm:"PARAMAFTERVALUE"`   // 参数变更后(调整后参数值)
	CURVALUE         float64 `json:"curvalue"  xorm:"CURVALUE"`                 // 当前套保\套利量(套保总量|套利总量)
	BEFOREVALUE      float64 `json:"beforevalue"  xorm:"BEFOREVALUE"`           // 应套保\套利量变更前(调整前应套保总量)
	AFTERVALUE       float64 `json:"aftervalue"  xorm:"AFTERVALUE"`             // 应套保\套利量变更后(调整后应套保总量)
	LOGDATETIME      string  `json:"logdatetime"  xorm:"LOGDATETIME"`           // 流水日期
	TRADEDATE        string  `json:"tradedate"  xorm:"TRADEDATE"`               // 交易日(yyyyMMdd)
	MIDDLEGOODSCODE  string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"` // 套保品种代码
	MIDDLEGOODSNAME  string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"` // 套保品种名称

	DiffValue float64 `json:"diffvalue"` // 应套保总量变化量
	BeginDate string  `json:"begindate"` // 开始交易日
	EndDate   string  `json:"enddate"`   // 结束交易日
}

func (r *Ermcp3ExposureParamChLog) calc() {
	r.DiffValue = r.AFTERVALUE - r.BEFOREVALUE
}

func (r *Ermcp3ExposureParamChLog) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.LOGID," +
		"       t.MIDDLEGOODSID," +
		"       t.AREAUSERID," +
		"       t.ACCOUNTID," +
		"       t.CHANGELOGTYPE," +
		"       t.PARAMBEFOREVALUE," +
		"       t.PARAMAFTERVALUE," +
		"       t.CURVALUE," +
		"       t.BEFOREVALUE," +
		"       t.AFTERVALUE," +
		"       to_char(t.LOGDATETIME, 'yyyy-mm-dd hh24:mi:ss') LOGDATETIME," +
		"       t.TRADEDATE," +
		"       g.middlegoodscode," +
		"       g.middlegoodsname" +
		"  FROM ERMCP_EXPOSUREPARAMCHGLOG t" +
		"  LEFT JOIN ERMS_MIDDLEGOODS g on t.middlegoodsid=g.middlegoodsid" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	sqlId.AndEx("t.TRADEDATE", r.TRADEDATE, len(r.TRADEDATE) > 0)
	if len(r.BeginDate) > 0 && len(r.EndDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.TRADEDATE >= '%v' and t.TRADEDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	return sqlId.String()
}

// GetDataEx 获取敞口报表参数调整明细
func (r *Ermcp3ExposureParamChLog) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ExposureParamChLog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
		sData[i].BeginDate = r.BeginDate
		sData[i].EndDate = r.EndDate
	}
	return sData, err
}