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'"` // 现货品种名称

	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:退还保证金
	// 数据库注释与返回值映身关系： 结算量->确定量, 收款->收款金额, 退款->退款金额, 付款->付款金额, 收票->收票金额, 开票->开票金额
	sDes := []string{"点价价格", "点价数量", "确定量", "其它费用", "追加保证金", "溢短金额", "调整金额", "付款", "收款", "退款",
		"收票", "开票", "提交审核(合同)", "审核通过(合同)", "审核拒绝(合同)", "合同撤回", "提交审核(计划)",
		"审核通过(计划)", "审核拒绝(计划)", "计划撤回", "正常完结(合同)", "异常终止(合同)", "退还保证金"}
	if r.OPERATELOGTYPE >= 1 && r.OPERATELOGTYPE <= 23 {
		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," +
		"       s.contractno relatedno," +
		"       s.spotgoodsbrandid," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid unitid," +
		"       gb.dgfactoryitemvalue brandname" +
		"  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 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)
	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
}

// Ermcp3ReportDaySpot 现货日报表
type Ermcp3ReportDaySpot struct {
	BUYPRICEDQTY       float64 `json:"-"  xorm:"'BUYPRICEDQTY'"`                        // 期末采购定价量
	SELLPRICEDQTY      float64 `json:"-"  xorm:"'SELLPRICEDQTY'"`                       // 期末销售定价量
	ORIBUYPRICEDQTY    float64 `json:"-"  xorm:"'ORIBUYPRICEDQTY'"`                     // 期初采购定价量
	ORISELLPRICEDQTY   float64 `json:"-"  xorm:"'ORISELLPRICEDQTY'"`                    // 期初销售定价量
	TODAYBUYRECKONQTY  float64 `json:"todaybuyreckonqty"  xorm:"'TODAYBUYRECKONQTY'"`   // 采购确定量
	TODAYSELLRECKONQTY float64 `json:"todaysellreckonqty"  xorm:"'TODAYSELLRECKONQTY'"` // 销售确定量
	RECKONDATE         string  `json:"reckondate"  xorm:"'RECKONDATE'"`                 // 日照时期(yyyyMMdd)
	AREAUSERID         int64   `json:"areauserid"  xorm:"'AREAUSERID'"`                 // 所属机构T
	UNITID             int32   `json:"-"  xorm:"'UNITID'"`                              // 现货商品单位id
	BUYINQTY           float64 `json:"buyinqty"  xorm:"'BUYINQTY'"`                     // 采购入库量
	SELLOUTQTY         float64 `json:"selloutqty"  xorm:"'SELLOUTQTY'"`                 // 销售出库量
	DELIVERYGOODSID    int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`       // 现货品种id
	DELIVERYGOODSCODE  string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`   // 现货品种代码
	DELIVERYGOODSNAME  string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`   // 现货品种名称

	ENUMDICNAME        string  `json:"enumdicname"`        // 现货商品单位名称
	TOTALBUYPRICEDQTY  float64 `json:"totalbuypricedqty"`  // 采购定价量 = 期末 - 期初
	TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初
	BeginDate          string  `json:"-"`                  // 开始日期
	EndDate            string  `json:"-"`                  // 结束日期
}

// Calc 数据处理
func (r *Ermcp3ReportDaySpot) Calc() {
	r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY
	r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *Ermcp3ReportDaySpot) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.userid," +
		"         t.deliverygoodsid," +
		"         t.reckondate," +
		"         sum(t.todaybuyinqty) todaybuyinqty," +
		"         sum(t.todayselloutqty) todayselloutqty" +
		"    from Reckon_ERMCP_AreaStock t" +
		"   group by t.userid, t.deliverygoodsid, t.reckondate)" +
		"select t.reckondate," +
		"       t.BUYPRICEDQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.Oribuypricedqty," +
		"       t.Orisellpricedqty," +
		"       t.TODAYBUYRECKONQTY," +
		"       t.TODAYSELLRECKONQTY," +
		"       t.RECKONDATE," +
		"       t.AREAUSERID," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid        unitid," +
		"       s.todaybuyinqty      buyinqty," +
		"       s.todayselloutqty    selloutqty" +
		"  from RECKON_ERMCP_AREASPOT t" +
		"  left join tmp s" +
		"    on t.reckondate = s.reckondate" +
		"   and t.areauserid = s.userid" +
		"   and t.deliverygoodsid = s.deliverygoodsid" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		" where 1 = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	if r.DELIVERYGOODSID > 0 {
		sqlId.And("t.DELIVERYGOODSID", r.DELIVERYGOODSID)
	}
	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 *Ermcp3ReportDaySpot) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ReportDaySpot, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].Calc()
	}
	return sData, err
}

// Ermcp3ReportMonSpot 现货月报表
type Ermcp3ReportMonSpot struct {
	CYCLETYPE          int32   `json:"cycletype"  xorm:"'cycletype'"`                   // 周期类型 - 1:月 2:季 3:年 4:周 5:全报表【原值】
	CYCLETIME          string  `json:"cycletime"  xorm:"'cycletime'"`                   // 周期时间 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYIW) 全(0)【原值】
	BUYPRICEDQTY       float64 `json:"-"  xorm:"'BUYPRICEDQTY'"`                        // 期末采购定价量
	SELLPRICEDQTY      float64 `json:"-"  xorm:"'SELLPRICEDQTY'"`                       // 期末销售定价量
	ORIBUYPRICEDQTY    float64 `json:"-"  xorm:"'ORIBUYPRICEDQTY'"`                     // 期初采购定价量
	ORISELLPRICEDQTY   float64 `json:"-"  xorm:"'ORISELLPRICEDQTY'"`                    // 期初销售定价量
	TODAYBUYRECKONQTY  float64 `json:"todaybuyreckonqty"  xorm:"'TODAYBUYRECKONQTY'"`   // 采购确定量
	TODAYSELLRECKONQTY float64 `json:"todaysellreckonqty"  xorm:"'TODAYSELLRECKONQTY'"` // 销售确定量
	AREAUSERID         int64   `json:"areauserid"  xorm:"'AREAUSERID'"`                 // 所属机构id
	UNITID             int32   `json:"-"  xorm:"'UNITID'"`                              // 现货商品单位id
	BUYINQTY           float64 `json:"buyinqty"  xorm:"'BUYINQTY'"`                     // 采购入库量
	SELLOUTQTY         float64 `json:"selloutqty"  xorm:"'SELLOUTQTY'"`                 // 销售出库量
	DELIVERYGOODSID    int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`       // 现货品种id
	DELIVERYGOODSCODE  string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`   // 现货品种代码
	DELIVERYGOODSNAME  string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`   // 现货品种名称

	ENUMDICNAME        string  `json:"enumdicname"`        // 现货商品单位名称
	TOTALBUYPRICEDQTY  float64 `json:"totalbuypricedqty"`  // 采购定价量 = 期末 - 期初
	TOTALSELLPRICEDQTY float64 `json:"totalsellpricedqty"` // 销售定价量 = 期末 - 期初
}

// Calc 数据处理
func (r *Ermcp3ReportMonSpot) Calc() {
	r.TOTALBUYPRICEDQTY = r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY
	r.TOTALSELLPRICEDQTY = r.SELLPRICEDQTY - r.ORISELLPRICEDQTY
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *Ermcp3ReportMonSpot) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.userid," +
		"         t.deliverygoodsid," +
		"         t.cycletime," +
		"         t.cycletype," +
		"         sum(t.todaybuyinqty) todaybuyinqty," +
		"         sum(t.todayselloutqty) todayselloutqty" +
		"    from Report_ERMCP_AreaStock t" +
		"   group by t.userid, t.deliverygoodsid, t.cycletime, t.cycletype)" +
		"select t.BUYPRICEDQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.Oribuypricedqty," +
		"       t.Orisellpricedqty," +
		"       t.TODAYBUYRECKONQTY," +
		"       t.TODAYSELLRECKONQTY," +
		"       t.cycletype," +
		"       t.cycletime," +
		"       t.AREAUSERID," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid        unitid," +
		"       s.todaybuyinqty      buyinqty," +
		"       s.todayselloutqty    selloutqty" +
		"  from Report_ERMCP_AreaSpot t" +
		"  left join tmp s" +
		"    on t.cycletime = s.cycletime" +
		"   and t.cycletype = s.cycletype" +
		"   and t.areauserid = s.userid" +
		"   and t.deliverygoodsid = s.deliverygoodsid" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		" where t.cycletype = 1"
	sqlId.And("t.AREAUSERID", r.AREAUSERID)
	sqlId.And("t.cycletime", r.CYCLETIME)
	return sqlId.String()
}

// GetDataEx 获取现货月报表
func (r *Ermcp3ReportMonSpot) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3ReportMonSpot, 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
}