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
}

// Ermcp3ReportAreaSpotPL 现货损益日/月表
type Ermcp3ReportAreaSpotPL struct {
	AREAUSERID            int64   `json:"areauserid"  xorm:"'AREAUSERID'"`                       // 所属机构
	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'"`                       // 更新时间
	WRSTANDARDCODE        string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`               // 品类代码
	WRSTANDARDNAME        string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`               // 品类名称
	WRUNITID              int32   `json:"unitid"  xorm:"'UNITID'"`                               // 品类单位id
	BRANDNAME             string  `json:"brandname"  xorm:"'BRANDNAME'"`                         // 品牌名称
	MODELNAME             string  `json:"modelname"  xorm:"'MODELNAME'"`                         // 品类名称
	GBUNITID              int32   `json:"gbunitid"  xorm:"'GBUNITID'"`                           // 现货单位id
	DELIVERYGOODSID       int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`             // 现货品种id
	DELIVERYGOODSCODE     string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`         // 现货品种代码
	DELIVERYGOODSNAME     string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`         // 现货品种名称

	ENUMDICNAME   string `json:"enumdicname"`   // 现货商品单位名称
	WRENUMDICNAME string `json:"gbenumdicname"` // 品类单位名称

	ReportType int32  `json:"-"` // 报表类型 1-日报表 2-月报表
	ReportDate string `json:"-"` // 格式 日报表(YYYYMMDD) 月报表(YYYYMM)
}

func (r *Ermcp3ReportAreaSpotPL) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GBUNITID)
	r.WRENUMDICNAME = mtpcache.GetEnumDicitemName(r.WRUNITID)
}

func (r *Ermcp3ReportAreaSpotPL) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.AREAUSERID," +
		"       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," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       w.unitid WRUNITID," +
		"       gb.dgfactoryitemvalue brandname," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid GBUNITID" +
		"  FROM %v t" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  left join dgfactoryitem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		" WHERE 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	if r.ReportType == 1 {
		// 日报表
		sqlId.FormatParam("RECKON_ERMCP_AREASPOTPL")
		sqlId.And("t.reckondate", r.ReportDate)
	} else {
		// 月报表
		sqlId.FormatParam("REPORT_ERMCP_AREASPOTPL")
		sqlId.And("t.cycletype", 1)
		sqlId.And("t.cycletime", r.ReportDate)
	}
	if r.WRSTANDARDID > 0 {
		sqlId.And("t.wrstandardid", r.WRSTANDARDID)
	}
	if r.SPOTGOODSBRANDID > 0 {
		sqlId.And("t.spotgoodsbrandid", r.SPOTGOODSBRANDID)
	}
	if r.DELIVERYGOODSID > 0 {
		sqlId.And("t.deliverygoodsid", r.DELIVERYGOODSID)
	}
	return sqlId.String()
}

// GetDataEx 获取现货损益日(月)报表
func (r *Ermcp3ReportAreaSpotPL) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ReportAreaSpotPL, 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:"areauserid" 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'"`                                    // 所属机构名称

	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
}

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," +
		"       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:"areauserid" 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'"`                                    // 所属机构名称

	TOTALBUYPRICEDQTY  float64 `json:"totalbuypricedqty"`  // 采购定价量 = 期末 - 期初
	TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初
}

func (r *Ermcp3ReportAreaSpotSub) calc() {
	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," +
		"       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
}