MTP20_IF/models/ermcp3Report.go

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

package models

import (
	"fmt"
	"mtp2_if/db"
	"mtp2_if/mtpcache"
	"mtp2_if/utils"
)

// 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-日报表 2-周期报表 3-日报表(范围,[开始日期,结束日期])
	BeginDate string `json:"-"` // 开始交易日
	EndDate   string `json:"-"` // 结束交易日
}

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.QueryType == 1 || r.QueryType == 3 {
		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" +
		"  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 == 3 {
		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," +
		"       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()
	}
	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'"`   // 现货商品名称

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

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

func (r *Ermcp3ExposureContractDetail) calc() {
	r.CurQty = r.QTY
	r.DiffQty = r.QTY * r.CONVERTRATIO
	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.audittradedate tradedate," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       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 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.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.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:已撤回

	EnumdicName  string  `json:"enumdicname"`  // 单位名称(品类)
	MGUNITIDNAME string  `json:"mgunitidname"` // 单位名称(套保商品)
	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.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," +
		"       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" +
		" 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-周期报表 3-日报表(范围,[开始日期,结束日期])
	BeginDate string `json:"-"` // 开始交易日
	EndDate   string `json:"-"` // 结束交易日
}

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

func (r *Ermcp3AreaSpotPLReport) buildSql() string {
	if r.QueryType == 1 || r.QueryType == 3 {
		return r.buildSqlDay()
	} else if r.QueryType == 4 {
		return r.buildSqlDayDetail()
	}
	return r.buildSqlCycle()
}

// buildSqlDay 现货日报表查询语句
func (r *Ermcp3AreaSpotPLReport) buildSqlDay() string {
	var sqlId utils.SQLVal = "select a.*," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       w.unitid" +
		"  from (SELECT t.RECKONDATE," +
		"               t.AREAUSERID," +
		"               t.CURRENCYID," +
		"               t.DELIVERYGOODSID," +
		"               t.WRSTANDARDID," +
		"               sum(t.ORIBUYQTY) ORIBUYQTY," +
		"               sum(t.ORIBUYAMOUNT) ORIBUYAMOUNT," +
		"               sum(t.ORISELLQTY) ORISELLQTY," +
		"               sum(t.ORISELLAMOUNT) ORISELLAMOUNT," +
		"               sum(t.ORIQTY) ORIQTY," +
		"               sum(t.ORIAVERAGEPRICE) ORIAVERAGEPRICE," +
		"               sum(t.ORIAMOUNT) ORIAMOUNT," +
		"               sum(t.TODAYBUYQTY) TODAYBUYQTY," +
		"               sum(t.TODAYBUYAMOUNT) TODAYBUYAMOUNT," +
		"               sum(t.TODAYBUYAVERAGEPRICE) TODAYBUYAVERAGEPRICE," +
		"               sum(t.TODAYSELLQTY) TODAYSELLQTY," +
		"               sum(t.TODAYSELLAMOUNT) TODAYSELLAMOUNT," +
		"               sum(t.TODAYSELLAVERAGEPRICE) TODAYSELLAVERAGEPRICE," +
		"               sum(t.CURBUYQTY) CURBUYQTY," +
		"               sum(t.CURBUYAMOUNT) CURBUYAMOUNT," +
		"               sum(t.CURSELLQTY) CURSELLQTY," +
		"               sum(t.CURSELLAMOUNT) CURSELLAMOUNT," +
		"               sum(t.CURQTY) CURQTY," +
		"               sum(t.CURAVERAGEPRICE) CURAVERAGEPRICE," +
		"               sum(t.CURAMOUNT) CURAMOUNT," +
		"               sum(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) a" +
		"  LEFT JOIN DELIVERYGOODS g" +
		"    on a.deliverygoodsid = g.deliverygoodsid" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on a.wrstandardid = w.wrstandardid"
	var sqlParam utils.SQLVal
	sqlParam.And("t.AREAUSERID", r.AREAUSERID)
	if r.QueryType == 1 {
		sqlParam.And("t.RECKONDATE", r.RECKONDATE)
	} else if r.QueryType == 3 {
		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 t.RECKONDATE," +
		"       t.AREAUSERID," +
		"       t.ACCOUNTID," +
		"       t.WRFACTORTYPEID," +
		"       t.CURRENCYID," +
		"       t.BIZTYPE," +
		"       t.DELIVERYGOODSID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.ORIBUYQTY," +
		"       t.ORIBUYAMOUNT," +
		"       t.ORISELLQTY," +
		"       t.ORISELLAMOUNT," +
		"       t.ORIQTY," +
		"       t.ORIAVERAGEPRICE," +
		"       t.ORIAMOUNT," +
		"       t.TODAYBUYQTY," +
		"       t.TODAYBUYAMOUNT," +
		"       t.TODAYBUYAVERAGEPRICE," +
		"       t.TODAYSELLQTY," +
		"       t.TODAYSELLAMOUNT," +
		"       t.TODAYSELLAVERAGEPRICE," +
		"       t.CURBUYQTY," +
		"       t.CURBUYAMOUNT," +
		"       t.CURSELLQTY," +
		"       t.CURSELLAMOUNT," +
		"       t.CURQTY," +
		"       t.CURAVERAGEPRICE," +
		"       t.CURAMOUNT," +
		"       t.CURSPOTPRICE," +
		"       t.CURMARKETVALUE," +
		"       t.ACTUALPL," +
		"       t.FLOATPL," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.TODAYINQTY," +
		"       t.TODAYOUTQTY," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       w.unitid," +
		"       dg.dgfactoryitemvalue brandName" +
		"  FROM RECKON_ERMCP_AREASPOTPL t" +
		"  LEFT JOIN DELIVERYGOODS g on t.deliverygoodsid=g.deliverygoodsid" +
		"  LEFT JOIN WRSTANDARD w on t.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN DGFACTORYITEM dg on t.spotgoodsbrandid=dg.dgfactoryitemid" +
		" 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)
	sqlId.And("t.DELIVERYGOODSID", r.DELIVERYGOODSID)
	sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID)

	return sqlId.String()
}

// buildSqlCycle 周期报表查询语句
func (r *Ermcp3AreaSpotPLReport) buildSqlCycle() string {
	var sqlId utils.SQLVal = "select a.*," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       w.unitid" +
		"  from (SELECT t.cycletype," +
		"               t.cycletime," +
		"               t.AREAUSERID," +
		"               t.CURRENCYID," +
		"               t.DELIVERYGOODSID," +
		"               t.WRSTANDARDID," +
		"               sum(t.ORIBUYQTY) ORIBUYQTY," +
		"               sum(t.ORIBUYAMOUNT) ORIBUYAMOUNT," +
		"               sum(t.ORISELLQTY) ORISELLQTY," +
		"               sum(t.ORISELLAMOUNT) ORISELLAMOUNT," +
		"               sum(t.ORIQTY) ORIQTY," +
		"               sum(t.ORIAVERAGEPRICE) ORIAVERAGEPRICE," +
		"               sum(t.ORIAMOUNT) ORIAMOUNT," +
		"               sum(t.TODAYBUYQTY) TODAYBUYQTY," +
		"               sum(t.TODAYBUYAMOUNT) TODAYBUYAMOUNT," +
		"               sum(t.TODAYBUYAVERAGEPRICE) TODAYBUYAVERAGEPRICE," +
		"               sum(t.TODAYSELLQTY) TODAYSELLQTY," +
		"               sum(t.TODAYSELLAMOUNT) TODAYSELLAMOUNT," +
		"               sum(t.TODAYSELLAVERAGEPRICE) TODAYSELLAVERAGEPRICE," +
		"               sum(t.CURBUYQTY) CURBUYQTY," +
		"               sum(t.CURBUYAMOUNT) CURBUYAMOUNT," +
		"               sum(t.CURSELLQTY) CURSELLQTY," +
		"               sum(t.CURSELLAMOUNT) CURSELLAMOUNT," +
		"               sum(t.CURQTY) CURQTY," +
		"               sum(t.CURAVERAGEPRICE) CURAVERAGEPRICE," +
		"               sum(t.CURAMOUNT) CURAMOUNT," +
		"               sum(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," +
		"                  t.WRSTANDARDID) a" +
		"  LEFT JOIN DELIVERYGOODS g" +
		"    on a.deliverygoodsid = g.deliverygoodsid" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on a.wrstandardid = w.wrstandardid"
	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()
	}
	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'"`                         // 品牌名称

	CURRENCYNAME string `json:"currencyname"` // 币种名称
	QueryType    int32  `json:"-"`            // 查询类型 1-日报表 2-周期报表 3-日报表(范围,[开始日期,结束日期])
	BeginDate    string `json:"-"`            // 开始交易日
	EndDate      string `json:"-"`            // 结束交易日
}

func (r *Ermcp3FinanceReport) calc() {
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
}

func (r *Ermcp3FinanceReport) buildSql() string {
	if r.QueryType == 1 || r.QueryType == 3 {
		return r.buildSqlDay()
	} else if r.QueryType == 4 {
		return r.buildSqlDayDetail()
	}
	return r.buildSqlCycle()
}

// buildSqlDay 财务日报表查询语句
func (r *Ermcp3FinanceReport) buildSqlDay() string {
	var sqlId utils.SQLVal = "SELECT t.SELLPREINVOICEDAMOUNT," +
		"       t.SELLUNINVOICEDAMOUNT," +
		"       t.TODAYRECEIVESUM," +
		"       t.TODAYPAYSUM," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.RECKONDATE," +
		"       t.AREAUSERID," +
		"       t.CURRENCYID," +
		"       t.BIZTYPE," +
		"       t.BUYTODAYSETTLEAMOUNT," +
		"       t.BUYTODAYREFUNDAMOUNT," +
		"       t.BUYPREPAIDAMOUNT," +
		"       t.BUYUNPAIDAMOUNT," +
		"       t.BUYTODAYINVOICEAMOUNT," +
		"       t.BUYPREINVOICEDAMOUNT," +
		"       t.BUYUNINVOICEDAMOUNT," +
		"       t.SELLTODAYSETTLEAMOUNT," +
		"       t.SELLTODAYREFUNDAMOUNT," +
		"       t.SELLPREPAIDAMOUNT," +
		"       t.SELLUNPAIDAMOUNT," +
		"       t.SELLTODAYINVOICEAMOUNT" +
		"  FROM RECKON_ERMCP_AREAFINANCE t" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	if r.QueryType == 1 {
		sqlId.And("t.RECKONDATE", r.RECKONDATE)
	} else if r.QueryType == 3 {
		sqlId.Join(fmt.Sprintf(" and t.RECKONDATE >= '%v' and t.RECKONDATE <= '%v' ", r.BeginDate, r.EndDate))
	}
	return sqlId.String()
}

// buildSqlDayDetail 财务日报表明细查询语句
func (r *Ermcp3FinanceReport) buildSqlDayDetail() string {
	var sqlId utils.SQLVal = "SELECT t.RECKONDATE," +
		"       t.AREAUSERID," +
		"       t.CURRENCYID," +
		"       t.BIZTYPE," +
		"       t.WRFACTORTYPEID," +
		"       t.DELIVERYGOODSID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.BUYTODAYSETTLEAMOUNT," +
		"       t.BUYTODAYREFUNDAMOUNT," +
		"       t.BUYPREPAIDAMOUNT," +
		"       t.BUYUNPAIDAMOUNT," +
		"       t.BUYTODAYINVOICEAMOUNT," +
		"       t.BUYPREINVOICEDAMOUNT," +
		"       t.BUYUNINVOICEDAMOUNT," +
		"       t.SELLTODAYSETTLEAMOUNT," +
		"       t.SELLTODAYREFUNDAMOUNT," +
		"       t.SELLPREPAIDAMOUNT," +
		"       t.SELLUNPAIDAMOUNT," +
		"       t.SELLTODAYINVOICEAMOUNT," +
		"       t.SELLPREINVOICEDAMOUNT," +
		"       t.SELLUNINVOICEDAMOUNT," +
		"       t.TODAYRECEIVESUM," +
		"       t.TODAYPAYSUM," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       dg.dgfactoryitemvalue brandName" +
		"  FROM RECKON_ERMCP_AFINANCESUB t" +
		"  LEFT JOIN DELIVERYGOODS g on t.deliverygoodsid=g.deliverygoodsid" +
		"  LEFT JOIN WRSTANDARD w on t.wrstandardid=w.wrstandardid" +
		"  LEFT JOIN DGFACTORYITEM dg on t.spotgoodsbrandid=dg.dgfactoryitemid" +
		" 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()
}

// buildSqlCycle 财务周期报表查询语句
func (r *Ermcp3FinanceReport) buildSqlCycle() string {
	var sqlId utils.SQLVal = "SELECT t.SELLPREINVOICEDAMOUNT," +
		"       t.SELLUNINVOICEDAMOUNT," +
		"       t.TODAYRECEIVESUM," +
		"       t.TODAYPAYSUM," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.CYCLETIME," +
		"       t.CYCLETYPE," +
		"       t.AREAUSERID," +
		"       t.CURRENCYID," +
		"       t.BIZTYPE," +
		"       t.BUYTODAYSETTLEAMOUNT," +
		"       t.BUYTODAYREFUNDAMOUNT," +
		"       t.BUYPREPAIDAMOUNT," +
		"       t.BUYUNPAIDAMOUNT," +
		"       t.BUYTODAYINVOICEAMOUNT," +
		"       t.BUYPREINVOICEDAMOUNT," +
		"       t.BUYUNINVOICEDAMOUNT," +
		"       t.SELLTODAYSETTLEAMOUNT," +
		"       t.SELLTODAYREFUNDAMOUNT," +
		"       t.SELLPREPAIDAMOUNT," +
		"       t.SELLUNPAIDAMOUNT," +
		"       t.SELLTODAYINVOICEAMOUNT" +
		"  FROM REPORT_ERMCP_AREAFINANCE t" +
		" 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 *Ermcp3FinanceReport) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3FinanceReport, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3AreaStockReport 库存报表
type Ermcp3AreaStockReport 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"`      // 库存变化量 = 期末 - 期初
	QueryType    int32   `json:"-"`            // 查询类型 1-日报表 2-周期报表 3-日报表(范围,[开始日期,结束日期])
	BeginDate    string  `json:"-"`            // 开始交易日
	EndDate      string  `json:"-"`            // 结束交易日
}

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

func (r *Ermcp3AreaStockReport) buildSql() string {
	if r.QueryType == 1 || r.QueryType == 3 {
		return r.buildSqlDay()
	}
	return r.buildSqlCycle()
}

func (r *Ermcp3AreaStockReport) buildSqlDay() string {
	var sqlId utils.SQLVal = "SELECT a.*," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid" +
		"  from (SELECT t.RECKONDATE," +
		"               t.USERID," +
		"               t.DELIVERYGOODSID," +
		"               t.WRSTANDARDID," +
		"               t.SPOTGOODSBRANDID," +
		"               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 t.RECKONDATE," +
		"                  t.USERID," +
		"                  t.DELIVERYGOODSID," +
		"                  t.WRSTANDARDID," +
		"                  t.SPOTGOODSBRANDID) a" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on a.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN dgfactoryitem gb" +
		"    on a.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  LEFT JOIN deliverygoods g" +
		"    on a.deliverygoodsid = g.deliverygoodsid"

	var sqlParam utils.SQLVal
	sqlParam.And("t.USERID", r.USERID)
	if r.QueryType == 1 {
		sqlParam.And("t.RECKONDATE", r.RECKONDATE)
	} else if r.QueryType == 3 {
		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)
	sqlId.FormatParam(sqlParam.String())
	return sqlId.String()
}

func (r *Ermcp3AreaStockReport) buildSqlDayDetail() 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.ORISTOCK," +
		"       t.CURSTOCK," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype," +
		"       g.deliverygoodsid," +
		"       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)
	return sqlId.String()
}

func (r *Ermcp3AreaStockReport) buildSqlCycle() string {
	var sqlId utils.SQLVal = "SELECT a.*," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid" +
		"  from (SELECT t.cycletype," +
		"               t.cycletime," +
		"               t.USERID," +
		"               t.DELIVERYGOODSID," +
		"               t.WRSTANDARDID," +
		"               t.SPOTGOODSBRANDID," +
		"               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 t.cycletype," +
		"                  t.cycletime," +
		"                  t.USERID," +
		"                  t.DELIVERYGOODSID," +
		"                  t.WRSTANDARDID," +
		"                  t.SPOTGOODSBRANDID) a" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on a.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN dgfactoryitem gb" +
		"    on a.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  LEFT JOIN deliverygoods g" +
		"    on a.deliverygoodsid = g.deliverygoodsid"
	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)
	sqlId.FormatParam(sqlParam.String())
	return sqlId.String()
}

// GetDataEx 查询库存报表
func (r *Ermcp3AreaStockReport) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpAreaStockReport, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	if err == nil {
		for i := range sData {
			sData[i].calc()
		}
	}
	return sData, err
}

// 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"`         // 更新时间

	CURRENCYNAME string `json:"currencyname"` // 币种名称
	QueryType    int32  `json:"-"`            // 查询类型 1-日报表 2-周期报表 3-日报表(范围,[开始日期,结束日期])
	BeginDate    string `json:"-"`            // 开始交易日
	EndDate      string `json:"-"`            // 结束交易日
}

func (r *Ermcp3ArealSumPL) calc() {
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
}

func (r *Ermcp3ArealSumPL) buildSql() string {
	if r.QueryType == 1 || r.QueryType == 3 {
		return r.buildSqlDay()
	} else if r.QueryType == 4 {
		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 == 3 {
		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" +
		"  FROM RECKON_ERMCP_AREASUMPL t" +
		" 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()
	}
	return sData, err
}