MTP20_IF/models/ermcp3.go

/**
* @Author: zou.yingbin
* @Create  : 2021/4/14 11:11
* @Modify  : 2021/4/14 11:11
 */

package models

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

// ErmcpDeliveryGoods 现货品种(交割品种)
type ErmcpDeliveryGoods struct {
	DELIVERYGOODSID   int32  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种ID(SEQ_DELIVERYGOODS)
	DELIVERYGOODSCODE string `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
	GOODSUNITID       int32  `json:"goodsunitid"  xorm:"'GOODSUNITID'"`             // 现货品种单位ID
	DELIVERYGOODSTYPE int32  `json:"deliverygoodstype"  xorm:"'DELIVERYGOODSTYPE'"` // 现货品种类型: 1-整装不拆分 2-散装记录明细 3:整装拆分 4:散装不记录明细
	STANDARDQTY       int32  `json:"standardqty"  xorm:"'STANDARDQTY'"`             // 标准数量(库位数量) [标准品特有]
	STANDARDQTYRANGE  int32  `json:"standardqtyrange"  xorm:"'STANDARDQTYRANGE'"`   // 标准数量偏差范围 [标准品特有]
	AUDITFLAG         int32  `json:"auditflag"  xorm:"'AUDITFLAG'"`                 // 交割是否需要审核 - 0:不需要 1:需要审核   默认为0
	ISVALID           int32  `json:"isvalid"  xorm:"'ISVALID'"`                     // 是否有效 - 0:无效 1:有效
	AGREEUNIT         int32  `json:"agreeunit"  xorm:"'AGREEUNIT'"`                 // 合约单位[散货时默认为1， 整装时默认为标准数量]
	QTYDECIMALPLACE   int32  `json:"qtydecimalplace"  xorm:"'QTYDECIMALPLACE'"`     // 成交量小数位
	CATEGORYID        int32  `json:"categoryid"  xorm:"'CATEGORYID'"`               // 类别ID(SEQ_WRCATEGORY)
	DGSTATUS          int32  `json:"dgstatus"  xorm:"'DGSTATUS'"`                   // 品种状态 - 作废 - 0:未激活 1:正常
	AREAUSERID        int64  `json:"areauserid"  xorm:"'AREAUSERID'"`               // 所属机构

	EnumdicName string `json:"enumdicname"` // 现货品种单位名称
	ExcludeCfg  int32  `json:"-"`           // 排除已配置项 1-排除
}

func (r *ErmcpDeliveryGoods) calc() {
	r.EnumdicName = mtpcache.GetEnumDicitemName(r.GOODSUNITID)
}

func (r *ErmcpDeliveryGoods) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.DELIVERYGOODSID," +
		"       t.DELIVERYGOODSCODE," +
		"       t.DELIVERYGOODSNAME," +
		"       t.GOODSUNITID," +
		"       t.DELIVERYGOODSTYPE," +
		"       t.STANDARDQTY," +
		"       t.STANDARDQTYRANGE," +
		"       t.AUDITFLAG," +
		"       t.ISVALID," +
		"       t.ISSPLIT," +
		"       t.AGREEUNIT," +
		"       t.QTYDECIMALPLACE," +
		"       t.CATEGORYID," +
		"       t.DGSTATUS," +
		"       t.AREAUSERID" +
		"  FROM DELIVERYGOODS t" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.AREAUSERID", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	if r.ExcludeCfg == 1 {
		sqlId.Join(" and t.deliverygoodsid not in(select distinct deliverygoodsid from ermcp_bizgroupspotgoods)")
	}
	return sqlId.String()
}

// GetDataEx 获取现货商品(交割商品)
func (r *ErmcpDeliveryGoods) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// GetData
func (r *ErmcpDeliveryGoods) GetData() ([]ErmcpDeliveryGoods, error) {
	sData := make([]ErmcpDeliveryGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// ErmcpDGFactoryItem 品种选择项定义表
type ErmcpDGFactoryItem struct {
	DGFACTORYITEMID     int64  `json:"dgfactoryitemid"  xorm:"'DGFACTORYITEMID'"`         // 选择项ID(SEQ_DGFACTORYITEM)
	DELIVERYGOODSID     int32  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`         // 现货品种ID
	DGFACTORYITEMTYPEID int64  `json:"dgfactoryitemtypeid"  xorm:"'DGFACTORYITEMTYPEID'"` // 要素项类型
	DGFACTORYITEMVALUE  string `json:"dgfactoryitemvalue"  xorm:"'DGFACTORYITEMVALUE'"`   // 要素项值(类型为仓库时填写仓库名称)
	WAREHOUSEID         int64  `json:"warehouseid"  xorm:"'WAREHOUSEID'"`                 // 仓库ID(类型为仓库时填写)
	ISVALID             int32  `json:"isvalid"  xorm:"'ISVALID'"`                         // 是否有效 - 0:无效 1:有效
	ORDERINDEX          int32  `json:"orderindex"  xorm:"'ORDERINDEX'"`                   // 顺序
	AREAUSERID          int64  `json:"areauserid"  xorm:"'AREAUSERID'"`                   // 用户id
}

func (r *ErmcpDGFactoryItem) buildSql() string {
	var sqlId utils.SQLVal = "SELECT DGFACTORYITEMID," +
		"       t.DELIVERYGOODSID," +
		"       to_char(t.DGFACTORYITEMTYPEID) DGFACTORYITEMTYPEID," +
		"       t.DGFACTORYITEMVALUE," +
		"       to_char(t.WAREHOUSEID) WAREHOUSEID," +
		"       t.ISVALID," +
		"       t.ORDERINDEX," +
		"       g.areauserid" +
		"  FROM DGFACTORYITEM t" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		" WHERE t.ISVALID = 1"
	sqlId.AndEx("g.areauserid", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.DGFACTORYITEMID", r.DGFACTORYITEMID, r.DGFACTORYITEMID > 0)
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	sqlId.AndEx("t.DGFACTORYITEMTYPEID", r.DGFACTORYITEMTYPEID, r.DGFACTORYITEMTYPEID > 0)
	sqlId.AndEx("t.ORDERINDEX", r.ORDERINDEX, r.ORDERINDEX > 0)

	return sqlId.String()
}

// GetDataEx 获取品种选择项定义
func (r *ErmcpDGFactoryItem) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// GetData
func (r *ErmcpDGFactoryItem) GetData() (interface{}, error) {
	sData := make([]ErmcpDGFactoryItem, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	return sData, err
}

// Ermcp3Brand 品牌(从品种选择项定义表中查)
type Ermcp3Brand struct {
	BRANDID         int64  `json:"brandid"`         // 品牌id
	BRANDNAME       string `json:"brandname"`       // 品牌名称
	DELIVERYGOODSID int32  `json:"deliverygoodsid"` // 品种ID
	AREAUSERID      int64  `json:"areauserid"`      // 用户id
}

// GetDataEx
func (r *Ermcp3Brand) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// GetData 获取品牌数据
func (r *Ermcp3Brand) GetData() ([]Ermcp3Brand, error) {
	m := ErmcpDGFactoryItem{
		DELIVERYGOODSID:     r.DELIVERYGOODSID,
		DGFACTORYITEMTYPEID: 2,
		ORDERINDEX:          1,
	}
	sData := make([]Ermcp3Brand, 0)
	if d, err := m.GetDataEx(); err == nil {
		if dgLst, ok := d.([]ErmcpDGFactoryItem); ok {
			for _, val := range dgLst {
				sData = append(sData, Ermcp3Brand{BRANDID: val.DGFACTORYITEMID, AREAUSERID: val.AREAUSERID,
					BRANDNAME: val.DGFACTORYITEMVALUE, DELIVERYGOODSID: val.DELIVERYGOODSID})
			}
		}
	}
	return sData, nil
}

// ErmcpRelatedGoods 现货品种的关联交易商品
type ErmcpRelatedGoods struct {
	GoodsId         int    `json:"goodsid"  xorm:"'GoodsId'"`                 // 商品id
	GoodsCode       string `json:"goodscode"  xorm:"'GoodsCode'"`             // 商品代码
	GoodsName       string `json:"goodsname"  xorm:"'GoodsName'"`             // 商品名称
	DELIVERYGOODSID int32  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"` // 品种ID

	AREAUSERID int64 `json:"-"` // 所属机构
}

func (r *ErmcpRelatedGoods) buildSql() string {
	var sqlId utils.SQLVal = "select distinct t.deliverygoodsid, g.goodsid, g.goodscode, g.goodsname" +
		"  from deliverygoods t" +
		" inner join ERMS2_WRSConvertDetail w" +
		"    on t.deliverygoodsid = w.deliverygoodsid" +
		" inner join erms_middlegoods m" +
		"    on w.middlegoodsid = m.middlegoodsid" +
		" inner join ERMCP_GGConvertConfig gc" +
		"    on m.goodsgroupid = gc.destgoodsgroupid" +
		" inner join goods g" +
		"    on gc.srcgoodsgroupid = g.goodsgroupid" +
		" where 1 = 1"
	sqlId.AndEx("t.AREAUSERID", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	return sqlId.String()
}

// GetDataEx 获取现货关联交易商品
func (r *ErmcpRelatedGoods) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// GetData 获取现货关联交易商品
func (r *ErmcpRelatedGoods) GetData() ([]ErmcpRelatedGoods, error) {
	sData := make([]ErmcpRelatedGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	return sData, err
}

// ErmcpDeliveryGoodsDetail 现货商品详情
type ErmcpDeliveryGoodsDetail struct {
	Data      ErmcpDeliveryGoods  `json:"data"`      // 现货商品基本信息(交割品种)
	GmList    []ErmcpWrstandard   `json:"gmlist"`    // 品类列表(仓单标准)
	GbList    []Ermcp3Brand       `json:"gblist"`    // 品牌列表
	WrsList   []WRSConverTDetail  `json:"wrslist"`   // 套保信息列表
	GoodsList []ErmcpRelatedGoods `json:"goodslist"` // 关联交易商品列表

	QryTradeGoods int32 `json:"-"` // 是否查询关联交易商品 1-查询
}

func (r *ErmcpDeliveryGoodsDetail) addGmList(lst []ErmcpWrstandard) {
	for i := range lst {
		if lst[i].DELIVERYGOODSID == r.Data.DELIVERYGOODSID {
			r.GmList = append(r.GmList, lst[i])
		}
	}
}

func (r *ErmcpDeliveryGoodsDetail) addGbList(lst []Ermcp3Brand) {
	for i := range lst {
		if lst[i].DELIVERYGOODSID == r.Data.DELIVERYGOODSID {
			r.GbList = append(r.GbList, lst[i])
		}
	}
}

func (r *ErmcpDeliveryGoodsDetail) addWrsList(lst []WRSConverTDetail) {
	for i := range lst {
		if lst[i].DELIVERYGOODSID == r.Data.DELIVERYGOODSID {
			r.WrsList = append(r.WrsList, lst[i])
		}
	}
}

func (r *ErmcpDeliveryGoodsDetail) addGoodsList(lst []ErmcpRelatedGoods) {
	for i := range lst {
		if lst[i].DELIVERYGOODSID == r.Data.DELIVERYGOODSID {
			r.GoodsList = append(r.GoodsList, lst[i])
		}
	}
}

// GetDataEx
func (r *ErmcpDeliveryGoodsDetail) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpDeliveryGoodsDetail, 0)
	m := ErmcpDeliveryGoods{AREAUSERID: r.Data.AREAUSERID, DELIVERYGOODSID: r.Data.DELIVERYGOODSID}
	data, err := m.GetData()
	if err != nil {
		return sData, err
	}
	if data == nil || len(data) == 0 {
		return sData, nil
	}
	// 基本信息
	for i := range data {
		val := ErmcpDeliveryGoodsDetail{Data: data[i],
			GmList: make([]ErmcpWrstandard, 0), GbList: make([]Ermcp3Brand, 0),
			WrsList: make([]WRSConverTDetail, 0), GoodsList: make([]ErmcpRelatedGoods, 0),
		}
		sData = append(sData, val)
	}
	//品类/品牌/套保信息
	gm := ErmcpWrstandard{AREAUSERID: int(r.Data.AREAUSERID), DELIVERYGOODSID: r.Data.DELIVERYGOODSID, ISVALID: 1}
	gb := Ermcp3Brand{DELIVERYGOODSID: r.Data.DELIVERYGOODSID}
	WrsC := WRSConverTDetail{DELIVERYGOODSID: r.Data.DELIVERYGOODSID}
	gmLst, _ := gm.GetData()
	gbLst, _ := gb.GetData()
	wrsLst, _ := WrsC.GetData()
	var goodLst []ErmcpRelatedGoods
	if r.QryTradeGoods == 1 {
		goodsM := ErmcpRelatedGoods{AREAUSERID: r.Data.AREAUSERID, DELIVERYGOODSID: r.Data.DELIVERYGOODSID}
		goodLst, _ = goodsM.GetData()
	}

	for i := range sData {
		sData[i].addGmList(gmLst)
		sData[i].addGbList(gbLst)
		sData[i].addWrsList(wrsLst)
		sData[i].addGoodsList(goodLst)
	}

	return sData, nil
}

// Ermcp3Contract 现货合同
type Ermcp3Contract struct {
	SPOTCONTRACTID    string  `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'"`       // 现货合同ID(602+Unix秒时间戳(10位)+xxxxxx)
	CONTRACTNO        string  `json:"contractno"  xorm:"'CONTRACTNO'"`               // 现货合同编号
	CONTRACTTYPE      int32   `json:"contracttype"  xorm:"'CONTRACTTYPE'"`           // 现货合同类型 - 1:采购 -1:销售
	USERID            int64   `json:"userid"  xorm:"'USERID'"`                       // 所属机构ID
	BUYUSERID         int64   `json:"buyuserid"  xorm:"'BUYUSERID'"`                 // 采购方ID
	BUYUSERName       string  `json:"buyusername"  xorm:"'BUYUSERName'"`             // 采购方名称
	SELLUSERID        int64   `json:"selluserid"  xorm:"'SELLUSERID'"`               // 销售方ID
	SELLUSERNAME      string  `json:"sellusername"  xorm:"'SELLUSERNAME'"`           // 销售方名称
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种ID
	WRSTANDARDID      int32   `json:"wrstandardid"  xorm:"'wrstandardid'"`           // 品类ID
	WRSTANDARDNAME    string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`       // 品类名称
	WRSTANDARDCODE    string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`       // 品类代码
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
	PRODUCTTYPE       int32   `json:"producttype"  xorm:"'PRODUCTTYPE'"`             // 产品类型 - 1:标准仓单 2:等标 3:非标
	CONVERTFACTOR     float64 `json:"convertfactor"  xorm:"'CONVERTFACTOR'"`         // 标仓系数(品类)
	SPOTGOODSDESC     string  `json:"spotgoodsdesc"  xorm:"'SPOTGOODSDESC'"`         // 商品型号
	PRICETYPE         int32   `json:"pricetype"  xorm:"'PRICETYPE'"`                 // 定价类型 - 1:一口价 2:点价 3:暂定价
	QTY               float64 `json:"qty"  xorm:"'QTY'"`                             // 数量
	PRICE             float64 `json:"price"  xorm:"'PRICE'"`                         // 价格\暂定价 [1:一口价、3:暂定价]
	AMOUNT            float64 `json:"amount"  xorm:"'AMOUNT'"`                       // 金额 [1:一口价、3:暂定价]
	DELIVERYSTARTDATE string  `json:"deliverystartdate"  xorm:"'DELIVERYSTARTDATE'"` // 交收期(开始)
	DELIVERYENDDATE   string  `json:"deliveryenddate"  xorm:"'DELIVERYENDDATE'"`     // 交收期(结束)
	GOODSID           int32   `json:"goodsid"  xorm:"'GOODSID'"`                     // 点价合约ID - 0:为现货，其它为期货商品合约ID [2:点价 3:暂定价]
	GOODSCODE         string  `json:"goodscode"  xorm:"'GOODSCODE'"`                 // 点价合约代码
	GOODSNAME         string  `json:"goodsname"  xorm:"'GOODSNAME'"`                 // 点价商品名称
	PRICEMOVE         float64 `json:"pricemove"  xorm:"'PRICEMOVE'"`                 // 升贴水 [2:点价 3:暂定价]
	STARTDATE         string  `json:"startdate"  xorm:"'STARTDATE'"`                 // 点价开始日期 [2:点价 3:暂定价]
	ENDDATE           string  `json:"enddate"  xorm:"'ENDDATE'"`                     // 点价结束日期 [2:点价 3:暂定价]
	MARGIN            float64 `json:"margin"  xorm:"'MARGIN'"`                       // 当前保证金
	CONTRACTMARGIN    float64 `json:"contractmargin"  xorm:"'CONTRACTMARGIN'"`       // 合同保证金
	EnumdicName       string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`             // 单位名称
	CONTRACCTSTATUS   int32   `json:"contracctstatus"  xorm:"'CONTRACTSTATUS'"`      // 合同状态 - 0:未提交 1:待审核 2:执行中 3:正常完结 4:审核拒绝 5:异常完结 6:已撤回
	Remark            string  `json:"remark"  xorm:"'Remark'"`                       // 备注
	AUDITREMARK       string  `json:"auditremark"  xorm:"'AUDITREMARK'"`             // 审核意见
	AUDITTIME         string  `json:"audittime"  xorm:"'AUDITTIME'"`                 // 审核时间
	CREATETIME        string  `json:"createtime"  xorm:"'CREATETIME'"`               // 创建时间
	UPDATETIME        string  `json:"updatetime"  xorm:"'UPDATETIME'"`               // 更新时间
	SPOTGOODSBRANDID  int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`   // 现货品牌ID(DGFactoryItem表的ID)
	BRANDNAME         string  `json:"brandname"  xorm:"'brandname'"`                 // 品牌名称
	ATTACHMENT        string  `json:"attachment"  xorm:"'ATTACHMENT'"`               // 附件
	UNITID            int32   `json:"unitid"  xorm:"'UNITID'"`                       // 单位id(取品类上的单位id)
	TRADEUSERID       int64   `json:"tradeuserid"  xorm:"'TRADEUSERID'"`             // 交易员id
	SALEUSERID        int64   `json:"saleuserid"  xorm:"'SALEUSERID'"`               // 销售员id
	MERUSERID         int64   `json:"meruserid"  xorm:"'MERUSERID'"`                 // 业务员id
	ACCOUNTID         string  `json:"accountid"  xorm:"'ACCOUNTID'"`                 // 期货账户id
	BIZTYPE           int32   `json:"biztype"  xorm:"'BIZTYPE'"`                     // 业务类型 1-套保 2-套利

	SELLNICKNAME       string `json:"sellnickname"`       // 采购方昵称
	BUYNICKNAME        string `json:"buynickname"`        // 销售方昵称
	TRADEUSERLOGINCODE string `json:"tradeuserlogincode"` // 交易员登录代码
	SALEUSERLOGINCODE  string `json:"saleuserlogincode"`  // 销售员登录代码
	MERUSERLOGINCODE   string `json:"meruserlogincode"`   // 业务员登录代码

	// 筛选条件
	QryType   int32 `json:"-"` // 查询类型 1-未提交 2-待审核 3-履约中 4-已完成
	UserType  int32 `json:"-"` // 用户类型  2-管理员 7-企业成员
	OwnUserId int64 `json:"-"` // 登录用户id
}

func (r *Ermcp3Contract) getQryTypeStatus() string {
	var status string
	switch r.QryType {
	case 1: // 未提交
		status = "0,4,6"
	case 2: // 待审核
		status = "1"
	case 3: // 履约中
		status = "2"
	case 4: // 已完成
		status = "3,5"
	}
	return status
}

func (r *Ermcp3Contract) calc() {
	r.BUYUSERName, r.BUYNICKNAME = mtpcache.GetUserNameAndNickName(r.BUYUSERID)
	r.SELLUSERNAME, r.SELLNICKNAME = mtpcache.GetUserNameAndNickName(r.SELLUSERID)
	r.EnumdicName = mtpcache.GetEnumDicitemName(r.UNITID)
	r.TRADEUSERLOGINCODE = mtpcache.GetLoginCodeByUserId(r.TRADEUSERID)
	r.SALEUSERLOGINCODE = mtpcache.GetLoginCodeByUserId(r.SALEUSERID)
	r.MERUSERLOGINCODE = mtpcache.GetLoginCodeByUserId(r.MERUSERID)
}

func (r *Ermcp3Contract) buildSql() string {
	var sqlId utils.SQLVal = "SELECT to_char(t.SPOTCONTRACTID) SPOTCONTRACTID," +
		"       t.CONTRACTNO," +
		"       t.CONTRACTTYPE," +
		"       t.USERID," +
		"       t.BUYUSERID," +
		"       t.SELLUSERID," +
		"       t.DELIVERYGOODSID," +
		"       t.wrstandardid," +
		"       t.PRODUCTTYPE," +
		"       w.CONVERTFACTOR," +
		"       t.SPOTGOODSDESC," +
		"       t.PRICETYPE," +
		"       t.QTY," +
		"       t.PRICE," +
		"       t.AMOUNT," +
		"       to_char(t.DELIVERYSTARTDATE, 'yyyy-mm-dd hh24:mi:ss') DELIVERYSTARTDATE," +
		"       to_char(t.DELIVERYENDDATE, 'yyyy-mm-dd hh24:mi:ss') DELIVERYENDDATE," +
		"       t.GOODSID," +
		"       t.PRICEMOVE," +
		"       to_char(t.STARTDATE, 'yyyy-mm-dd hh24:mi:ss') STARTDATE," +
		"       to_char(t.ENDDATE, 'yyyy-mm-dd hh24:mi:ss') ENDDATE," +
		"       t.MARGIN," +
		"       t.contractmargin," +
		"       t.remark," +
		"       t.auditremark," +
		"       to_char(t.contractattachment) attachment," +
		"       t.contractstatus," +
		"       t.SPOTGOODSBRANDID," +
		"       to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') audittime," +
		"       to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       to_char(t.updatetime, 'yyyy-mm-dd hh24:mi:ss') updatetime," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g2.goodscode," +
		"       g2.goodsname," +
		"       gb.dgfactoryitemvalue brandname," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       t.tradeuserid," +
		"       t.saleuserid," +
		"       t.meruserid," +
		"       to_char(t.accountid) accountid," +
		"       t.biztype" +
		"  FROM ERMCP_SPOTCONTRACT t" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  left join goods g2" +
		"    on t.goodsid = g2.goodsid" +
		"  left join DGFACTORYITEM gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		" where 1 = 1"
	sqlId.AndEx("t.userid", r.USERID, r.USERID > 0)
	sqlId.AndEx("t.SPOTCONTRACTID", r.SPOTCONTRACTID, len(r.SPOTCONTRACTID) > 0)
	if r.UserType == 7 {
		// 企业成员
		sqlId.Join(fmt.Sprintf(" and %v in(t.saleuserid, t.tradeuserid, t.meruserid)", r.OwnUserId))
	}
	status := r.getQryTypeStatus()
	if len(status) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.contractstatus in (%v)", status))
	}

	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3Contract) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3Contract, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3SellBuyContract 采购/销售合同
type Ermcp3SellBuyContract struct {
	UserID             int64   `json:"userid"  xorm:"'UserID'"`                         // 机构ID
	SpotContractId     string  `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'"`         // 合同ID
	AccountName        string  `json:"accountname"  xorm:"'ACCOUNTNAME'"`               // 账户名称
	DeliveryGoodsId    int     `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`       // 现货商品ID
	DeliveryGoodsCode  string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`   // 现货品种代码
	DeliveryGoodsName  string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`   // 现货品种名称
	SpotGoodsdesc      string  `json:"spotgoodsdesc"  xorm:"'SPOTGOODSDESC'"`           // 商品型号(商品规格)
	GoodsId            int     `json:"goodsid"  xorm:"'GOODSID'"`                       // 点价商品ID
	GOODSNAME          string  `json:"goodsname"  xorm:"'GOODSNAME'"`                   // 点价商品名称
	Goodscode          string  `json:"goodscode"  xorm:"'GOODSCODE'"`                   // 点价商品代码
	Pricemove          float64 `json:"pricemove"  xorm:"'PRICEMOVE'"`                   // 升贴水
	Qty                float64 `json:"qty"  xorm:"'QTY'"`                               // 合同量
	UnpricedQty        float64 `json:"unpricedqty"  xorm:"'UNPRICEDQTY'"`               // 未定价量
	PricedQty          float64 `json:"pricedqty"  xorm:"'PRICEDQTY'"`                   // 已定价量
	UnsureQty          float64 `json:"unsureqty"  xorm:"'UNSUREQTY'"`                   // 未确定量
	PayAmount          float64 `json:"payamount"  xorm:"'PAYAMOUNT'"`                   // 已收付额(收款或付款)
	UnpayAmount        float64 `json:"unpayamount"`                                     // 应收付款额(应支付或应收款)
	PrePayAmount       float64 `json:"prepayamount"`                                    // 预收付额
	InvoiceAmount      float64 `json:"invoiceamount"  xorm:"'INVOICEAMOUNT'"`           // 已开票额
	DaikaiAmount       float64 `json:"daikaiamount"  xorm:"'DAIKAIAMOUNT'"`             // 应收(开)票额
	PreInvoiceAmount   float64 `json:"preinvoiceamount"`                                // 预收(开)票额
	StartDate          string  `json:"startdate"  xorm:"'STARTDATE'"`                   // 点价开始日
	EndDate            string  `json:"enddate"  xorm:"'ENDDATE'"`                       // 点价结束日
	DeliveryStartDate  string  `json:"deliverystartdate"  xorm:"'DELIVERYSTARTDATE'"`   // 交割开始日
	DeliveryendDate    string  `json:"deliveryenddate"  xorm:"'DELIVERYENDDATE'"`       // 交割结束日
	Convertfactor      float64 `json:"convertfactor"  xorm:"'CONVERTFACTOR'"`           // 标仓系数(品类)
	EnumdicName        string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`               // 单位名称
	Contracctstatus    uint    `json:"contracctstatus"  xorm:"'CONTRACTSTATUS'"`        // 合同状态- 0:未提交 1:待审核 2:执行中 3:正常完结 4:审核拒绝 5:异常完结 6:已撤回
	PriceType          int     `json:"pricetype"  xorm:"'PRICETYPE'"`                   // 定价类型 - 1:一口价 2:点价 3:暂定价
	ProductType        int     `json:"producttype"  xorm:"'PRODUCTTYPE'"`               // 产品类型 - 1:标准仓单 2:等标 3:非标
	Contracttype       int32   `json:"contracttype"  xorm:"'CONTRACTTYPE'"`             // 合同类型 1-采购, -1-销售
	Pricedamount       float64 `json:"pricedamount"  xorm:"'PRICEDAMOUNT'"`             // 已定价额
	PricedAvg          float64 `json:"pricedavg"  xorm:"'PRICEDAVG'"`                   // 已点均价
	Margin             float64 `json:"margin"  xorm:"'MARGIN'"`                         // 保证金
	CONTRACTMARGIN     float64 `json:"contractmargin"  xorm:"'CONTRACTMARGIN'"`         // 合同保证金
	Remark             string  `json:"remark"  xorm:"'Remark'"`                         // 备注
	ATTACHMENT         string  `json:"attachment"  xorm:"'ATTACHMENT'"`                 // 附件
	ReckonRealQty      float64 `json:"reckonrealqty"  xorm:"'ReckonRealQty'"`           // 已确定量
	ReckonOtherAmount  float64 `json:"reckonotheramount"  xorm:"'ReckonOtherAmount'"`   // 其它费用
	ReckonAdjustAmount float64 `json:"reckonadjustamount"  xorm:"'ReckonAdjustAmount'"` // 调整金额
	Price              float64 `json:"price"  xorm:"'Price'"`                           // 价格
	LoanAmount         float64 `json:"loanamount"  xorm:"'LoanAmount'"`                 // 贷款总额=已定价额+调整金额
	Contractno         string  `json:"contractno"  xorm:"'Contractno'"`                 // 合同编号
	TotalAmount        float64 `json:"totalamount"  xorm:"'-'"`                         // 合计总额
	ReckonedAmount     float64 `json:"reckonedamount"  xorm:"'ReckonedAmount'"`         // 实际已收付额(已确定额,已收付总额-已退款总额)
	BUYUSERID          int64   `json:"-"  xorm:"'BUYUSERID'"`                           // 采购方ID
	SELLUSERID         int64   `json:"-"  xorm:"'SELLUSERID'"`                          // 销售方ID
	AUDITTIME          string  `json:"audittime"  xorm:"'AUDITTIME'"`                   // 审核时间
	CREATETIME         string  `json:"createtime"  xorm:"'CREATETIME'"`                 // 创建时间
	SPOTGOODSBRANDID   int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`     // 现货品牌ID(DGFactoryItem表的ID)
	BRANDNAME          string  `json:"brandname"  xorm:"'brandname'"`                   // 品牌名称
	WRSTANDARDID       int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`             // 品类ID
	WRSTANDARDNAME     string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`         // 品类名称
	WRSTANDARDCODE     string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`         // 品类代码

	// 筛选条件
	QryType   int32 `json:"-"` // 查询类型 1-未提交 2-待审核 3-履约中 4-已完成
	UserType  int32 `json:"-"` // 用户类型  2-管理员 7-企业成员
	OwnUserId int64 `json:"-"` // 登录用户id
}

func (r *Ermcp3SellBuyContract) calc() {
	r.TotalAmount = r.LoanAmount + r.ReckonOtherAmount
	r.UnpayAmount = r.TotalAmount - r.ReckonedAmount
	r.PrePayAmount = r.ReckonedAmount - r.TotalAmount
	r.PreInvoiceAmount = r.InvoiceAmount - r.TotalAmount
	if r.Contracttype == 1 {
		//采购合同, 取销售方名称
		r.AccountName = mtpcache.GetUserNameByUserId(r.SELLUSERID)
	} else {
		//销售合同, 取采购方名称
		r.AccountName = mtpcache.GetUserNameByUserId(r.BUYUSERID)
	}
}

func (r *Ermcp3SellBuyContract) buildSql() string {
	var sqlId utils.SQLVal = "select t.contractno," +
		"       to_char(t.SpotContractId) SpotContractId," +
		"       t.deliverygoodsid," +
		"       t.spotgoodsdesc," +
		"       t.goodsid," +
		"       t.pricemove," +
		"       t.userid," +
		"       t.BUYUSERID," +
		"       t.SELLUSERID," +
		"       t.qty," +
		"       t.qty - t.pricedqty unpricedqty," +
		"       t.pricedqty," +
		"       (case" +
		"         when t.pricetype = 3 then" +
		"          t.qty - t.RECKONREALQTY" +
		"         else" +
		"          t.pricedqty - t.ReckonRealQty" +
		"       end) unsureqty," +
		"       t.reckonedamount PayAmount," +
		"       t.invoiceamount," +
		"       (t.pricedamount + t.RECKONADJUSTAMOUNT + t.RECKONOTHERAMOUNT -" +
		"       t.invoiceamount) as daikaiAmount," +
		"       to_char(t.startdate, 'yyyy-mm-dd hh24:mi:ss') startdate," +
		"       to_char(t.enddate, 'yyyy-mm-dd hh24:mi:ss') enddate," +
		"       to_char(t.deliverystartdate, 'yyyy-mm-dd hh24:mi:ss') deliverystartdate," +
		"       to_char(t.deliveryenddate, 'yyyy-mm-dd hh24:mi:ss') deliveryenddate," +
		"       w.convertfactor," +
		"       t.contractstatus," +
		"       t.pricetype," +
		"       t.producttype," +
		"       t.contracttype," +
		"       t.remark," +
		"       to_char(t.contractattachment) attachment," +
		"       t.margin," +
		"       t.contractmargin," +
		"       t.SPOTGOODSBRANDID," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       wr.wrstandardid," +
		"       wr.wrstandardname," +
		"       wr.wrstandardcode," +
		"       g2.goodscode," +
		"       g2.goodsname," +
		"       e.enumdicname," +
		"       t.contracttype," +
		"       t.pricedamount," +
		"       case" +
		"         when t.pricedqty > 0 then" +
		"          (t.pricedamount) / t.pricedqty" +
		"         else" +
		"          0" +
		"       end pricedAvg," +
		"       t.ReckonRealQty," +
		"       t.ReckonOtherAmount," +
		"       t.ReckonAdjustAmount," +
		"       t.Price," +
		"       t.ReckonedAmount," +
		"       to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') audittime," +
		"       to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       t.pricedamount + t.ReckonAdjustAmount as LoanAmount," +
		"       gb.dgfactoryitemvalue BRANDNAME" +
		"  from ermcp_spotcontract t" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  left join goods g2" +
		"    on t.goodsid = g2.goodsid" +
		"  left join wrstandard wr" +
		"    on t.wrstandardid = wr.wrstandardid" +
		"  left join DGFactoryItem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  left join enumdicitem e" +
		"    on wr.unitid = e.enumitemname" +
		"   and e.enumdiccode = 'goodsunit'" +
		" where 1 = 1"
	sqlId.AndEx("t.userid", r.UserID, r.UserID > 0)
	sqlId.AndEx("t.contracttype", r.Contracttype, r.Contracttype != 0)
	sqlId.AndEx("t.SpotContractId", r.SpotContractId, len(r.SpotContractId) > 0)
	if r.UserType == 7 {
		// 企业成员
		sqlId.Join(fmt.Sprintf(" and %v in(t.saleuserid, t.tradeuserid, t.meruserid)", r.OwnUserId))
	}
	var tmp = Ermcp3Contract{QryType: r.QryType}
	status := tmp.getQryTypeStatus()
	if len(status) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.contractstatus in (%v)", status))
	}
	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3SellBuyContract) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3SellBuyContract, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3ExposureDetail 敞口现货明细
type Ermcp3ExposureDetail struct {
	Createtime        string  `json:"createtime"  xorm:"'createtime'"`               // 时间
	Areauserid        int64   `json:"areauserid"  xorm:"'areauserid'"`               // 机构ID
	Logtype           int32   `json:"logtype"  xorm:"'logtype'"`                     // 类型 - 1:套保计划 2:现货合同
	Contracttype      int32   `json:"contracttype"  xorm:"'contracttype'"`           // 现货合同类型 - 1:采购 -1:销售
	Qty               float64 `json:"qty"  xorm:"'qty'"`                             // 数量
	RelateNo          string  `json:"relateNo"  xorm:"'relateNo'"`                   // 现货合同/套保计划编号
	Middlegoodsname   string  `json:"middlegoodsname"  xorm:"'middlegoodsname'"`     // 套保商品名称
	Middlegoodscode   string  `json:"middlegoodscode"  xorm:"'middlegoodscode'"`     // 套保商品代码
	MiddlegoodsId     int32   `json:"middlegoodsId"  xorm:"'middlegoodsId'"`         // 套保商品id
	Unitid            int32   `json:"-"  xorm:"'unitid'"`                            // 现货商品单位ID
	Enumdicname       string  `json:"enumdicname"  xorm:"'enumdicname'"`             // 现货商品单位名称
	ChangeQty         float64 `json:"changeQty"  xorm:"'changeQty'"`                 // 套保变动量
	Convertfactor     float64 `json:"convertfactor"  xorm:"'convertfactor'"`         // 标仓系数
	Convertratio      float64 `json:"convertratio"  xorm:"'convertratio'"`           // 套保系数
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种id
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
}

func (r *Ermcp3ExposureDetail) calc() {
	// 销售合同转换为负数
	if r.Logtype == 2 && r.Contracttype == -1 {
		r.ChangeQty *= -1
		r.Qty *= -1
	}

	// 采购计划 转换为负数
	if r.Logtype == 1 && r.Contracttype == 1 {
		r.ChangeQty *= -1
		r.Qty *= -1
	}
}

func (r *Ermcp3ExposureDetail) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select 2                as LogType," +
		"         s.spotcontractid as relatedid," +
		"         s.contractno     as relateNo," +
		"         s.qty" +
		"    from ermcp_spotcontract s" +
		"  union all" +
		"  select 1," +
		"         t.hedgeplanid as relateid," +
		"         t.hedgeplanno as relateNo," +
		"         t.planqty     as qty" +
		"    from ermcp_hedgeplan t)" +
		"select to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       t.middlegoodsid," +
		"       t.areauserid," +
		"       t.logtype," +
		"       t.contracttype," +
		"       t.qty / t.convertfactor / t.convertratio as qty," +
		"       t.convertfactor," +
		"       t.convertratio," +
		"       t.qty changeQty," +
		"       tmp.relateNo," +
		"       m.middlegoodsname," +
		"       m.middlegoodscode," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid unitid," +
		"       e.enumdicname" +
		"  from ermcp_spotexposurelog t" +
		"  left join erms_middlegoods m" +
		"    on t.middlegoodsid = m.middlegoodsid" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  left join enumdicitem e" +
		"    on g.goodsunitid = e.enumitemname" +
		"   and e.enumdiccode = 'goodsunit'" +
		"  left join tmp" +
		"    on t.logtype = tmp.LogType" +
		"   and t.relatedid = tmp.relatedid" +
		" where 1=1 and t.tradedate=to_char(sysdate, 'yyyymmdd')"
	sqlId.And("t.areauserid", r.Areauserid)
	sqlId.And("t.middlegoodsid", r.MiddlegoodsId)
	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3ExposureDetail) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Ermcp3ExposureDetail, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// Ermcp3AreaSpot 敞口现货头寸
type Ermcp3AreaSpot struct {
	AREAUSERID        int64   `json:"areauserid"  xorm:"'AREAUSERID'"`               // 所属机构
	ORIBUYPLANQTY     float64 `json:"-"  xorm:"'ORIBUYPLANQTY'"`                     // 期初采购计划数量
	ORIBUYPRICEDQTY   float64 `json:"-"  xorm:"'ORIBUYPRICEDQTY'"`                   // 期初采购合同已定价数量
	ORISELLPLANQTY    float64 `json:"-"  xorm:"'ORISELLPLANQTY'"`                    // 期初销售计划数量
	ORISELLPRICEDQTY  float64 `json:"-"  xorm:"'ORISELLPRICEDQTY'"`                  // 期初销售合同已定价数量
	BUYPLANQTY        float64 `json:"-"  xorm:"'BUYPLANQTY'"`                        // 采购计划数量
	BUYPRICEDQTY      float64 `json:"-"  xorm:"'BUYPRICEDQTY'"`                      // 采购合同已定价数量
	SELLPLANQTY       float64 `json:"-"  xorm:"'SELLPLANQTY'"`                       // 销售计划数量
	SELLPRICEDQTY     float64 `json:"-"  xorm:"'SELLPRICEDQTY'"`                     // 销售合同已定价数量
	TOTALSPOTQTY      float64 `json:"totalspotqty"  xorm:"'TOTALSPOTQTY'"`           // 当前数量(现货头寸总量) =  (销售计划数量 - 销售已定价数量)  - (采购计划数量 - 采购已定价数量)
	OriToalSpotQty    float64 `json:"oritoalspotqty"  xorm:"'OriToalSpotQty'"`       // 昨日数量
	IncreaseQty       float64 `json:"increaseqty"  xorm:"'IncreaseQty'"`             // 增加数量=销售计划数量+采购已定价数量
	DecreaseQty       float64 `json:"decreaseqty"  xorm:"'DecreaseQty'"`             // 减少数量=-(销售已定价数量+采购计划数量)
	UPDATETIME        string  `json:"updatetime"  xorm:"'UPDATETIME'"`               // 更新时间
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种id
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
}

func (r *Ermcp3AreaSpot) calc() {
	/*
		增加数量=今日销售计划数量+今日采购已定价数量
		减少数量=-(今日销售已定价数量+今日采购计划数量)

		其中，
		今日销售计划数量=期末销售计划数量-期初销售计划数量
		今日采购已定价数量=期末采购已定价数量-期初采购已定价数量
		今日销售已定价数量=期末销售已定价数量-期初销售已定价数量
		今日采购计划数量=期末采购计划数量-期初采购计划数量
	*/
	r.IncreaseQty = r.SELLPLANQTY - r.ORISELLPLANQTY + r.BUYPRICEDQTY - r.ORIBUYPRICEDQTY
	r.DecreaseQty = (r.SELLPRICEDQTY - r.ORISELLPRICEDQTY + r.BUYPLANQTY - r.ORIBUYPLANQTY) * -1
	r.OriToalSpotQty = (r.ORISELLPLANQTY - r.ORISELLPRICEDQTY) - (r.ORIBUYPLANQTY - r.ORIBUYPRICEDQTY)
}

func (r *Ermcp3AreaSpot) buildSql() string {
	var sqlId utils.SQLVal = "select t.AREAUSERID," +
		"       t.ORIBUYPLANQTY," +
		"       t.ORIBUYPRICEDQTY," +
		"       t.ORISELLPLANQTY," +
		"       t.ORISELLPRICEDQTY," +
		"       t.BUYPLANQTY," +
		"       t.BUYPRICEDQTY," +
		"       t.SELLPLANQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.TOTALSPOTQTY," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME" +
		"  from ermcp_areaspot t" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		" where 1 = 1"
	sqlId.And("t.areauserid", r.AREAUSERID)
	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3AreaSpot) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Ermcp3AreaSpot, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// Ermcp3AreaSpotDetail 敞口现货头寸明细
type Ermcp3AreaSpotDetail struct {
	Relatedid         string  `json:"relatedid"  xorm:"'relatedid'"`                 // 套保计划ID/现货合同ID
	Relatedno         string  `json:"relatedno"  xorm:"'relatedno'"`                 // 编号
	LogType           int32   `json:"logtype"  xorm:"'logType'"`                     // 记录类型 1-套保 2-现货合同
	Contracttype      int32   `json:"contracttype"  xorm:"'contracttype'"`           // 合同类型 1-采购 -1-销售
	Qty               float64 `json:"qty"  xorm:"'qty'"`                             // 数量
	Strtime           string  `json:"strtime"  xorm:"'strtime'"`                     // 时间
	Enumdicname       string  `json:"enumdicname"`                                   // 现货商品单位名称
	Recordname        string  `json:"recordname"`                                    // 类型名称
	CREATETIME        string  `json:"createtime"  xorm:"'CREATETIME'"`               // 创建时间
	Unitid            int32   `json:"-"  xorm:"'UNITID'"`                            // 单位ID
	UserId            int     `json:"-"`                                             // 所属用户ID
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种id
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
}

func (r *Ermcp3AreaSpotDetail) calc() {
	var logTypeName, contractTypeName string
	if r.LogType == 1 {
		logTypeName = "计划"
	} else {
		logTypeName = "合同"
	}

	if r.Contracttype == 1 {
		contractTypeName = "采购"
	} else {
		contractTypeName = "销售"
	}

	// 销售合同 数量转为负数
	if r.LogType == 2 && r.Contracttype == -1 {
		if r.Qty > 0 {
			r.Qty = r.Qty * -1
		}
	}

	// 采购计划 数量转为负数
	if r.LogType == 1 && r.Contracttype == 1 {
		if r.Qty > 0 {
			r.Qty = r.Qty * -1
		}
	}

	r.Recordname = contractTypeName + logTypeName
	r.Enumdicname = mtpcache.GetEnumDicitemName(r.Unitid)
}

func (r *Ermcp3AreaSpotDetail) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select to_char(t.hedgeplanid) relatedid," +
		"         t.hedgeplanno relatedno," +
		"         1 as logType," +
		"         t.contracttype" +
		"    from ermcp_hedgeplan t" +
		"   where t.areauserid = %v" +
		"     and t.deliverygoodsid = %v" +
		"  union all" +
		"  select to_char(t.spotcontractid)," +
		"         t.contractno," +
		"         2 as logType," +
		"         t.contracttype" +
		"    from ermcp_spotcontract t" +
		"   where t.userid = %v" +
		"     and t.deliverygoodsid = %v)" +
		"select t.relatedid," +
		"       tmp.relatedno," +
		"       t.LogType," +
		"       tmp.contracttype," +
		"       t.RealQty qty," +
		"       to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodsname," +
		"       g.deliverygoodscode," +
		"       g.goodsunitid unitid" +
		"  from ermcp_spotlog t" +
		" inner join tmp" +
		"    on t.LogType = tmp.logType" +
		"   and t.relatedid = tmp.relatedid" +
		"   and t.areauserid = %v" +
		"   and t.deliverygoodsid = %v" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		" where t.tradedate = to_char(sysdate, 'yyyymmdd')"
	sqlId.FormatParam(r.UserId, r.DELIVERYGOODSID, r.UserId, r.DELIVERYGOODSID, r.UserId, r.DELIVERYGOODSID)
	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3AreaSpotDetail) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Ermcp3AreaSpotDetail, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// Ermcp3Wrstandard 现货品类
type Ermcp3Wrstandard struct {
	WRSTANDARDID    int64  `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`       // 品类ID(SEQ_WRSTANDARD)
	WRSTANDARDCODE  string `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`   // 品类代码
	DELIVERYGOODSID int32  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"` // 现货品种ID
	UNITID          int32  `json:"unitid"  xorm:"'UNITID'"`                   // 品类单位ID
	MINIVALUE       int32  `json:"minivalue"  xorm:"'MINIVALUE'"`             // 最小变动值
	MINIVALUEDP     int32  `json:"minivaluedp"  xorm:"'MINIVALUEDP'"`         // 最小变动值小数位
	REALMINIVALUE   int32  `json:"realminivalue"  xorm:"'REALMINIVALUE'"`     // 实际最小变动值
	REALMINIVALUEDP int32  `json:"realminivaluedp"  xorm:"'REALMINIVALUEDP'"` // 实际最小变动值小数位
	CREATORID       int64  `json:"creatorid"  xorm:"'CREATORID'"`             // 创建人
	CREATETIME      string `json:"createtime"  xorm:"'CREATETIME'"`           // 创建时间
	WRSTANDARDNAME  string `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`   // 品类名称
	AREAUSERID      int    `json:"areauserid"  xorm:"'AREAUSERID'"`           // 所属机构
	EnumdicName     string `json:"enumdicname"`                               // 品类单位名称
	ISVALID         int32  `json:"isvalid"  xorm:"'ISVALID'"`                 // 是否有效 0-无效(停用) 1-有效(正常)
	UPDATETIME      string `json:"updatetime"  xorm:"'UPDATETIME'"`           // 更新时间
	REMARK          string `json:"remark"  xorm:"'REMARK'"`                   // 备注
}

func (r *Ermcp3Wrstandard) calc() {
	r.EnumdicName = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *Ermcp3Wrstandard) buildSql() string {
	str := "select WRSTANDARDID," +
		"       WRSTANDARDCODE," +
		"       DELIVERYGOODSID," +
		"       UNITID," +
		"       MINIVALUE," +
		"       MINIVALUEDP," +
		"       REALMINIVALUE," +
		"       REALMINIVALUEDP," +
		"       CREATORID," +
		"       to_char(CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       WRSTANDARDNAME," +
		"       ISVALID," +
		"       to_char(UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       REMARK," +
		"       AREAUSERID" +
		"  from wrstandard t" +
		" where t.AREAUSERID=%v"
	sqlId := fmt.Sprintf(str, r.AREAUSERID)
	if r.DELIVERYGOODSID > 0 {
		sqlId = sqlId + fmt.Sprintf(" and DELIVERYGOODSID=%v", r.DELIVERYGOODSID)
	}
	sqlId += " order by createtime desc"
	return sqlId
}

// GetDataEx
func (r *Ermcp3Wrstandard) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Ermcp3Wrstandard, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// Ermcp3AreaStockApply 出入库申请
type Ermcp3AreaStockApply 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
	SPOTGOODSBRANDID  int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`   // 现货品牌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'"`                 // 品牌名称
	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"`                                   // 现货商品单位名称
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种id
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称

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

func (r *Ermcp3AreaStockApply) 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 *Ermcp3AreaStockApply) buildSql() string {
	var sqlId utils.SQLVal = "SELECT to_char(t.INOUTAPPLYID) INOUTAPPLYID," +
		"       t.USERID," +
		"       t.INOUTTYPE," +
		"       t.WRSTANDARDID," +
		"       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," +
		"       g.goodsunitid unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype," +
		"       s.contractno," +
		"       s.contracttype," +
		"       s.buyuserid," +
		"       s.selluserid," +
		"       s.pricetype," +
		"       s.qty CONTRACTQTY," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname" +
		"  FROM ERMCP_AREAINOUTSTOCKAPPLY 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" +
		"  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 r.DELIVERYGOODSID > 0 {
		sqlId.And("t.DELIVERYGOODSID", r.DELIVERYGOODSID)
	}
	// 品类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 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()
}

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

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

func (r *Ermcp3AreaStockApplySum) buildSql() string {
	sqlId := "select a.*, gb.dgfactoryitemvalue brandname, w.wrstandardname" +
		"  from (SELECT t.INOUTTYPE," +
		"               t.wrstandardid," +
		"               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.wrstandardid," +
		"                  t.spotcontractid) a" +
		"  left join dgfactoryitem gb" +
		"    on a.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  left join wrstandard w" +
		"    on a.wrstandardid = w.wrstandardid"
	sqlId = fmt.Sprintf(sqlId, r.SPOTCONTRACTID)

	// 不关联合同id
	sqlId2 := "select a.*, gb.dgfactoryitemvalue brandname, w.wrstandardname" +
		"  from (SELECT t.INOUTTYPE," +
		"               t.wrstandardid," +
		"               t.SPOTGOODSBRANDID," +
		"               t.SPOTCONTRACTID," +
		"               sum(t.QTY) TOTALQTY" +
		"          FROM ERMCP_AREAINOUTSTOCKAPPLY t" +
		"         WHERE t.applystatus = 2" +
		"         group by t.inouttype," +
		"                  t.spotgoodsbrandid," +
		"                  t.wrstandardid," +
		"                  t.spotcontractid) a" +
		"  left join dgfactoryitem gb" +
		"    on a.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  left join wrstandard w" +
		"    on a.wrstandardid = w.wrstandardid"
	if len(r.SPOTCONTRACTID) == 0 {
		return sqlId2
	}
	return sqlId
}

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

// Ermcp3AreaStock 机构库存表
type Ermcp3AreaStock 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
	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'"`                     // 期末库存量(今日量)
	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
}

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

func (r *Ermcp3AreaStock) 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.SPOTGOODSBRANDID," +
		"       t.WAREHOUSEINFOID," +
		"       t.DELIVERYGOODSID," +
		"       t.ORISTOCK," +
		"       t.CURSTOCK," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid" +
		"  FROM ERMCP_AREASTOCK t" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN dgfactoryitem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemvalue" +
		"  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.Join(" order by t.WRSTANDARDID, t.UPDATETIME desc")
	return sqlId.String()
}

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

// Ermcp3AreaStockReport 库存报表
type Ermcp3AreaStockReport struct {
	USERID             int64   `json:"userid"  xorm:"'USERID'"`                         // 机构ID
	WRSTANDARDID       string  `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

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

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

func (r *Ermcp3AreaStockReport) 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.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 %v 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)
	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
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	// 品类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 len(r.WAREHOUSEINFOID) > 0 {
		sqlId.And("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID)
	}
	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
}