MTP20_IF/models/ermcpAreaStock.go

/**
* @Author: zou.yingbin
* @Create  : 2021/3/10 9:13
* @Modify  : 2021/3/10 9:13
 */

package models

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

// ErmcpAreaStockApply 出入库申请
type ErmcpAreaStockApply struct {
	INOUTAPPLYID     string  `json:"inoutapplyid"  xorm:"'INOUTAPPLYID'"`         // 申请ID(607+Unix秒时间戳(10位)+xxxxxx)
	USERID           int64   `json:"userid"  xorm:"'USERID'"`                     // 机构ID
	INOUTTYPE        int32   `json:"inouttype"  xorm:"'INOUTTYPE'"`               // 出入库类型 - 1:采购入库 2:销售出库 3:生产入库 4:生产出库
	WRSTANDARDID     string  `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`         // 现货商品ID
	SPOTGOODSMODELID int32   `json:"spotgoodsmodelid"  xorm:"'SPOTGOODSMODELID'"` // 现货型号ID
	SPOTGOODSBRANDID int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"` // 现货品牌ID
	DELIVERYGOODSID  int64   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`   // 现货品种ID
	SPOTCONTRACTID   string  `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'"`     // 关联现货合同ID
	WAREHOUSEINFOID  string  `json:"warehouseinfoid"  xorm:"'WAREHOUSEINFOID'"`   // 现货仓库ID
	QTY              float64 `json:"qty"  xorm:"'QTY'"`                           // 数量
	APPLYSTATUS      int32   `json:"applystatus"  xorm:"'APPLYSTATUS'"`           // 申请状态 - 1:待审核 2:审核通过 3:审核拒绝 4:处理失败 5:已撤回
	APPLYSRC         int32   `json:"applysrc"  xorm:"'APPLYSRC'"`                 // 申请来源 - 1:管理端 2:终端
	APPLYID          int64   `json:"applyid"  xorm:"'APPLYID'"`                   // 申请人
	APPLYREMARK      string  `json:"applyremark"  xorm:"'APPLYREMARK'"`           // 申请备注
	APPLYTIME        string  `json:"applytime"  xorm:"'APPLYTIME'"`               // 申请时间
	AUDITSRC         int32   `json:"auditsrc"  xorm:"'AUDITSRC'"`                 // 审核来源 - 1:管理端 2:终端
	AUDITID          int64   `json:"auditid"  xorm:"'AUDITID'"`                   // 审核人
	AUDITTIME        string  `json:"audittime"  xorm:"'AUDITTIME'"`               // 审核时间
	AUDITREMARK      string  `json:"auditremark"  xorm:"'AUDITREMARK'"`           // 审核备注
	AUDITTRADEDATE   string  `json:"audittradedate"  xorm:"'AUDITTRADEDATE'"`     // 审核交易日(yyyyMMdd)
	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'"`               // 品牌名称
	MODELNAME        string  `json:"modelname"  xorm:"'MODELNAME'"`               // 型号名称
	WAREHOUSENAME    string  `json:"warehousename"  xorm:"'WAREHOUSENAME'"`       // 仓库名称
	WAREHOUSECODE    string  `json:"warehousecode"  xorm:"'WAREHOUSECODE'"`       // 仓库代码
	WAREHOUSETYPE    int32   `json:"warehousetype"  xorm:"'WAREHOUSETYPE'"`       // 仓库类型 - 1 厂库  2 自有库  3 合作库
	CONTRACTNO       string  `json:"contractno"  xorm:"'CONTRACTNO'"`             // 合同编号
	CONTRACTTYPE     int32   `json:"contracttype"  xorm:"'CONTRACTTYPE'"`         // 现货合同类型 - 1:采购 -1:销售
	BUYUSERID        int64   `json:"buyuserid"  xorm:"'BUYUSERID'"`               // 采购方userid
	SELLUSERID       int64   `json:"selluserid"  xorm:"'SELLUSERID'"`             // 销售方userid
	PRICETYPE        int32   `json:"pricetype"  xorm:"'PRICETYPE'"`               // 定价类型 - 1:一口价 2:点价 3:暂定价
	CONTRACTQTY      float64 `json:"contractqty"  xorm:"'CONTRACTQTY'"`           // 合同量
	BUYUSERNAME      string  `json:"buyusername"`                                 // 采购方名称
	SELLUSERNAME     string  `json:"sellusername"`                                // 销售方名称
	APPLYNAME        string  `json:"applyname"`                                   // 申请人名称
	AUDITNAME        string  `json:"auditname"`                                   // 审核人名称
	ENUMDICNAME      string  `json:"enumdicname"`                                 // 现货商品单位名称

	FilterStatus string `json:"-"` // 查询条件, 申请状态, 逗号隔开
	FilterType   string `json:"-"` // 查询条件, 出入库类型, 逗号隔开
	BeginDate    string `json:"-"` // 开始日期
	EndDate      string `json:"-"` // 结束日期
}

func (r *ErmcpAreaStockApply) calc() {
	// 采购方名称
	r.BUYUSERNAME = mtpcache.GetUserNameByUserId(r.BUYUSERID)
	// 销售方名称
	r.SELLUSERNAME = mtpcache.GetUserNameByUserId(r.SELLUSERID)
	// 申请人名称
	if r.APPLYSRC == 1 {
		r.APPLYNAME = mtpcache.GetSystemmangerLoginCode(r.APPLYID)
	} else {
		r.APPLYNAME = mtpcache.GetLoginCodeByLoginId(r.APPLYID)
	}
	// 审核人名称
	if r.AUDITSRC == 1 {
		r.AUDITNAME = mtpcache.GetSystemmangerLoginCode(r.AUDITID)
	} else {
		r.AUDITNAME = mtpcache.GetLoginCodeByLoginId(r.AUDITID)
	}
	// 单位名称
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *ErmcpAreaStockApply) buildSql() string {
	var sqlId utils.SQLVal = "SELECT to_char(t.INOUTAPPLYID) INOUTAPPLYID," +
		"       t.USERID," +
		"       t.INOUTTYPE," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSMODELID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.DELIVERYGOODSID," +
		"       to_char(t.SPOTCONTRACTID) SPOTCONTRACTID," +
		"       to_char(t.WAREHOUSEINFOID) WAREHOUSEINFOID," +
		"       t.QTY," +
		"       t.APPLYSTATUS," +
		"       t.APPLYSRC," +
		"       t.APPLYID," +
		"       t.APPLYREMARK," +
		"       to_char(t.APPLYTIME, 'yyyy-mm-dd hh24:mi:ss') APPLYTIME," +
		"       t.AUDITSRC," +
		"       t.AUDITID," +
		"       to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," +
		"       t.AUDITREMARK," +
		"       t.AUDITTRADEDATE," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.brandname," +
		"       gm.modelname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype," +
		"       s.contractno," +
		"       s.contracttype," +
		"       s.buyuserid," +
		"       s.selluserid," +
		"       s.pricetype," +
		"       s.qty CONTRACTQTY" +
		"  FROM ERMCP_AREAINOUTSTOCKAPPLY t" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  left join spotgoodsbrand gb" +
		"    on t.spotgoodsbrandid = gb.brandid" +
		"  left join spotgoodsmodel gm" +
		"    on t.spotgoodsmodelid = gm.modelid" +
		"  left join warehouseinfo h" +
		"    on t.warehouseinfoid = h.autoid" +
		"  left join ermcp_spotcontract s" +
		"    on t.spotcontractid = s.spotcontractid" +
		" WHERE 1 = 1"
	// 用户id(必要条件)
	sqlId.And("t.USERID", r.USERID)
	// 合同id
	if len(r.SPOTCONTRACTID) > 0 {
		sqlId.And("t.SPOTCONTRACTID", r.SPOTCONTRACTID)
	}
	// 现货商品id
	if len(r.WRSTANDARDID) > 0 {
		sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID)
	}
	// 品牌id
	if r.SPOTGOODSBRANDID > 0 {
		sqlId.And("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID)
	}
	// 型号id
	if r.SPOTGOODSMODELID > 0 {
		sqlId.And("t.SPOTGOODSMODELID", r.SPOTGOODSMODELID)
	}
	// 仓库id
	if len(r.WAREHOUSEINFOID) > 0 {
		sqlId.And("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID)
	}
	// 日期范围
	if len(r.BeginDate) > 0 {
		if r.BeginDate == r.EndDate {
			sqlId.And("t.audittradedate", r.BeginDate)
		} else if r.EndDate > r.BeginDate {
			sqlId.BiggerOrEq("t.audittradedate", r.BeginDate)
			sqlId.LessOrEq("t.audittradedate", r.EndDate)
		}
	}
	// 出入库类型
	if len(r.FilterType) > 0 {
		if len(r.FilterType) == 1 {
			sqlId.And("t.INOUTTYPE", r.FilterType)
		} else {
			sqlId.Join(fmt.Sprintf(" and t.INOUTTYPE in(%v)", r.FilterType))
		}
	}
	// 出入库状态
	if len(r.FilterStatus) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.APPLYSTATUS in(%v)", r.FilterStatus))
	}
	// 排序
	sqlId.Join(" order by t.APPLYTIME desc")
	return sqlId.String()
}

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

// ErmcpAreaStockApplySum 合同出入库数量汇总
type ErmcpAreaStockApplySum struct {
	INOUTTYPE        int32   `json:"inouttype"  xorm:"'INOUTTYPE'"`               // 出入库类型 - 1:采购入库 2:销售出库 3:生产入库 4:生产出库
	SPOTGOODSMODELID int32   `json:"spotgoodsmodelid"  xorm:"'SPOTGOODSMODELID'"` // 型号id
	SPOTGOODSBRANDID int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"` // 品牌id
	BRANDNAME        string  `json:"brandname"  xorm:"'BRANDNAME'"`               // 品牌名称
	MODELNAME        string  `json:"modelname"  xorm:"'MODELNAME'"`               // 型号名称
	TOTALQTY         float64 `json:"totalqty"  xorm:"'TOTALQTY'"`                 // 总数量
	SPOTCONTRACTID   string  `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'"`     // 关联现货合同ID
}

func (r *ErmcpAreaStockApplySum) buildSql() string {
	sqlId := "select a.*, gb.brandname, gm.modelname" +
		"  from (SELECT t.INOUTTYPE," +
		"               t.SPOTGOODSMODELID," +
		"               t.SPOTGOODSBRANDID," +
		"               t.SPOTCONTRACTID," +
		"               sum(t.QTY) TOTALQTY" +
		"          FROM ERMCP_AREAINOUTSTOCKAPPLY t" +
		"         WHERE t.applystatus= 2 and t.spotcontractid = %v" +
		"         group by t.inouttype, t.spotgoodsbrandid, t.spotgoodsmodelid, t.spotcontractid) a" +
		"  left join spotgoodsbrand gb" +
		"    on a.spotgoodsbrandid = gb.brandid" +
		"  left join spotgoodsmodel gm" +
		"    on a.spotgoodsmodelid = gm.modelid"
	sqlId = fmt.Sprintf(sqlId, r.SPOTCONTRACTID)

	// 不关联合同id
	sqlId2 := "select a.*, gb.brandname, gm.modelname" +
		"  from (SELECT t.INOUTTYPE," +
		"               t.SPOTGOODSMODELID," +
		"               t.SPOTGOODSBRANDID," +
		"               t.SPOTCONTRACTID," +
		"               sum(t.QTY) TOTALQTY" +
		"          FROM ERMCP_AREAINOUTSTOCKAPPLY t" +
		"         WHERE t.applystatus= 2" +
		"         group by t.inouttype, t.spotgoodsbrandid, t.spotgoodsmodelid, t.SPOTCONTRACTID) a" +
		"  left join spotgoodsbrand gb" +
		"    on a.spotgoodsbrandid = gb.brandid" +
		"  left join spotgoodsmodel gm" +
		"    on a.spotgoodsmodelid = gm.modelid"
	if len(r.SPOTCONTRACTID) == 0 {
		return sqlId2
	}
	return sqlId
}

// GetDataEx 获取合同出入库汇总信息
func (r *ErmcpAreaStockApplySum) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpAreaStockApplySum, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	return sData, err
}

// ErmcpAreaStock 机构库存表
type ErmcpAreaStock struct {
	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'"`                 // 更新时间
	USERID             int64   `json:"userid"  xorm:"'USERID'"`                         // 机构ID
	WRSTANDARDID       string  `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`             // 现货商品ID
	SPOTGOODSMODELID   int32   `json:"spotgoodsmodelid"  xorm:"'SPOTGOODSMODELID'"`     // 现货型号ID
	SPOTGOODSBRANDID   int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`     // 现货品牌ID
	WAREHOUSEINFOID    string  `json:"warehouseinfoid"  xorm:"'WAREHOUSEINFOID'"`       // 仓库ID
	DELIVERYGOODSID    int64   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`       // 现货品种ID
	ORISTOCK           float64 `json:"oristock"  xorm:"'ORISTOCK'"`                     // 期初库存量(昨日量)
	CURSTOCK           float64 `json:"curstock"  xorm:"'CURSTOCK'"`                     // 期末库存量(今日量)
	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'"`                   // 品牌名称
	MODELNAME          string  `json:"modelname"  xorm:"'MODELNAME'"`                   // 型号名称
	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"`                                     // 单位名称
}

func (r *ErmcpAreaStock) calc() {
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *ErmcpAreaStock) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.TODAYBUYINQTY," +
		"       t.TODAYPRODUCEINQTY," +
		"       t.TODAYSELLOUTQTY," +
		"       t.TODAYPRODUCEOUTQTY," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.USERID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSMODELID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.WAREHOUSEINFOID," +
		"       t.DELIVERYGOODSID," +
		"       t.ORISTOCK," +
		"       t.CURSTOCK," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.brandname," +
		"       gm.modelname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype" +
		"  FROM ERMCP_AREASTOCK t" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN SPOTGOODSBRAND gb" +
		"    on t.spotgoodsbrandid = gb.brandid" +
		"  LEFT JOIN SPOTGOODSMODEL gm" +
		"    on t.spotgoodsmodelid = gm.modelid" +
		"  LEFT JOIN WAREHOUSEINFO h" +
		"    on t.warehouseinfoid = h.autoid" +
		" WHERE 1 = 1"
	sqlId.And("t.USERID", r.USERID)
	sqlId.Join(" order by t.WRSTANDARDID, t.UPDATETIME desc")
	return sqlId.String()
}

// GetDataEx 获取机构库存信息
func (r *ErmcpAreaStock) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpAreaStock, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	if err == nil {
		for i := range sData {
			sData[i].calc()
		}
	}
	return sData, err
}

// ErmcpAreaStockReport 库存报表
type ErmcpAreaStockReport struct {
	USERID             int64   `json:"userid"  xorm:"'USERID'"`                         // 机构ID
	WRSTANDARDID       string  `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`             // 现货商品ID
	SPOTGOODSMODELID   int32   `json:"spotgoodsmodelid"  xorm:"'SPOTGOODSMODELID'"`     // 现货型号ID
	SPOTGOODSBRANDID   int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`     // 现货品牌ID
	WAREHOUSEINFOID    string  `json:"warehouseinfoid"  xorm:"'WAREHOUSEINFOID'"`       // 仓库ID
	DELIVERYGOODSID    int64   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`       // 现货品种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'"`                   // 品牌名称
	MODELNAME          string  `json:"modelname"  xorm:"'MODELNAME'"`                   // 型号名称
	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"`                                     // 单位名称

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

func (r *ErmcpAreaStockReport) calc() {
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *ErmcpAreaStockReport) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.TODAYBUYINQTY," +
		"       t.TODAYPRODUCEINQTY," +
		"       t.TODAYSELLOUTQTY," +
		"       t.TODAYPRODUCEOUTQTY," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.USERID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSMODELID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.WAREHOUSEINFOID," +
		"       t.DELIVERYGOODSID," +
		"       t.ORISTOCK," +
		"       t.CURSTOCK," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.brandname," +
		"       gm.modelname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype" +
		"  FROM %v t" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN SPOTGOODSBRAND gb" +
		"    on t.spotgoodsbrandid = gb.brandid" +
		"  LEFT JOIN SPOTGOODSMODEL gm" +
		"    on t.spotgoodsmodelid = gm.modelid" +
		"  LEFT JOIN WAREHOUSEINFO h" +
		"    on t.warehouseinfoid = h.autoid" +
		" WHERE 1 = 1"

	sqlId.And("t.USERID", r.USERID)
	if r.ReportType == 1 {
		// 日报表
		sqlId.FormatParam("RECKON_ERMCP_AREASTOCK")
		sqlId.And("t.reckondate", r.ReportDate)
	} else {
		// 月报表
		sqlId.FormatParam("REPORT_ERMCP_AREASTOCK")
		sqlId.And("t.cycletype", 1)
		sqlId.And("t.cycletime", r.ReportDate)
	}
	// 现货商品id
	if len(r.WRSTANDARDID) > 0 {
		sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID)
	}
	// 品牌id
	if r.SPOTGOODSBRANDID > 0 {
		sqlId.And("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID)
	}
	// 型号id
	if r.SPOTGOODSMODELID > 0 {
		sqlId.And("t.SPOTGOODSMODELID", r.SPOTGOODSMODELID)
	}
	// 仓库id
	if len(r.WAREHOUSEINFOID) > 0 {
		sqlId.And("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID)
	}
	return sqlId.String()
}

// GetDataEx 查询库存报表
func (r *ErmcpAreaStockReport) 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
}