MTP20_IF/models/erms3.go

// Package models 211.3 风险管理系统2020_现货合同
package models

import (
	"mtp2_if/db"
	"time"
)

// Erms3Spotcontractdetail 合同标的明细表
type Erms3Spotcontractdetail struct {
	Spotdetailid          string    `json:"spotdetailid" xorm:"SPOTDETAILID"`                   // 标的明细ID(346+Unix秒时间戳(10位)+xxxxxx)
	Spotcontractid        string    `json:"spotcontractid" xorm:"SPOTCONTRACTID"`               // 现货合同ID
	Wrstandardid          int64     `json:"wrstandardid" xorm:"WRSTANDARDID"`                   // 仓单标准ID
	Deliverygoodsid       int64     `json:"deliverygoodsid" xorm:"DELIVERYGOODSID"`             // 现货品种ID
	Tradedate             string    `json:"tradedate" xorm:"TRADEDATE"`                         // 交易日(yyyyMMdd)
	Contracttype          int32     `json:"contracttype" xorm:"CONTRACTTYPE"`                   // 现货合同类型 - 1:采购 -1:销售
	Accountid             int64     `json:"accountid" xorm:"ACCOUNTID"`                         // 资金账户ID
	Customeraccountid     int64     `json:"customeraccountid" xorm:"CUSTOMERACCOUNTID"`         // 客户资金账户ID
	Producttype           int32     `json:"producttype" xorm:"PRODUCTTYPE"`                     // 产品类型 - 1:标准仓单 2:等标 3:非标
	Deliverygoodsdesc     string    `json:"deliverygoodsdesc" xorm:"DELIVERYGOODSDESC"`         // 产品规格 - 根据此字段生成仓单标准、仓单要素类型
	Warehouseid           int64     `json:"warehouseid" xorm:"WAREHOUSEID"`                     // 仓库ID
	Pointdesc             string    `json:"pointdesc" xorm:"POINTDESC"`                         // 点价描述
	Priceqty              float64   `json:"priceqty" xorm:"PRICEQTY"`                           // 指定价类总量
	Pointqty              float64   `json:"pointqty" xorm:"POINTQTY"`                           // 点价类总量
	Unpricedqty           float64   `json:"unpricedqty" xorm:"UNPRICEDQTY"`                     // 未确定量
	Unpricedrelatedqty    float64   `json:"unpricedrelatedqty" xorm:"UNPRICEDRELATEDQTY"`       // 未确定量关联量
	Unpricedavaliableqty  float64   `json:"unpricedavaliableqty" xorm:"UNPRICEDAVALIABLEQTY"`   // 未确定量可关联量
	Unpricedcancelledqty  float64   `json:"unpricedcancelledqty" xorm:"UNPRICEDCANCELLEDQTY"`   // 未确定量撤销量(非业务)
	Pricedqty             float64   `json:"pricedqty" xorm:"PRICEDQTY"`                         // 确定量
	Pricedamount          float64   `json:"pricedamount" xorm:"PRICEDAMOUNT"`                   // 确定金额
	Pricedrelatedqty      float64   `json:"pricedrelatedqty" xorm:"PRICEDRELATEDQTY"`           // 确定量关联量
	Pricedavaliableqty    float64   `json:"pricedavaliableqty" xorm:"PRICEDAVALIABLEQTY"`       // 确定量可关联量
	Pricedcancelledqty    float64   `json:"pricedcancelledqty" xorm:"PRICEDCANCELLEDQTY"`       // 确定量撤销量(非业务)
	Pricedcancelledamount float64   `json:"pricedcancelledamount" xorm:"PRICEDCANCELLEDAMOUNT"` // 确定量撤销金额(非业务)
	Deliveryqty           float64   `json:"deliveryqty" xorm:"DELIVERYQTY"`                     // 交收数量(非业务)
	Deliveryamount        float64   `json:"deliveryamount" xorm:"DELIVERYAMOUNT"`               // 交收金额(非业务)
	Deliveryovershortqty  float64   `json:"deliveryovershortqty" xorm:"DELIVERYOVERSHORTQTY"`   // 交收溢短数量(非业务)
	Deliveryactualamount  float64   `json:"deliveryactualamount" xorm:"DELIVERYACTUALAMOUNT"`   // 交收实际金额(非业务)
	Deliveryotheramount   float64   `json:"deliveryotheramount" xorm:"DELIVERYOTHERAMOUNT"`     // 交收其它费用(非业务)
	Curdeliveryqty        float64   `json:"curdeliveryqty" xorm:"CURDELIVERYQTY"`               // 剩余交收数量
	Curdeliveryamount     float64   `json:"curdeliveryamount" xorm:"CURDELIVERYAMOUNT"`         // 剩余交收金额
	Remark                string    `json:"remark" xorm:"REMARK"`                               // 备注
	Marketid              int64     `json:"marketid" xorm:"MARKETID"`                           // 市场ID
	Handlestatus          int32     `json:"handlestatus" xorm:"HANDLESTATUS"`                   // 处理状态
	Updatetime            time.Time `json:"updatetime" xorm:"UPDATETIME"`                       // 更新时间
	Reckonedamount        float64   `json:"reckonedamount" xorm:"RECKONEDAMOUNT"`               // 已结金额
	Invoicedamount        float64   `json:"invoicedamount" xorm:"INVOICEDAMOUNT"`               // 已开票金额
}

// TableName is ERMS3_SPOTCONTRACTDETAIL
func (Erms3Spotcontractdetail) TableName() string {
	return "ERMS3_SPOTCONTRACTDETAIL"
}

// Erms3SpotContractInfo 合同明细.
type Erms3SpotContractInfo struct {
	Erms3Spotcontractdetail `xorm:"extends"`
	Areauserid              int64     `json:"areauserid" xorm:"AREAUSERID"`                  // 所属机构
	Accountid               int64     `json:"accountid" xorm:"ACCOUNTID"`                    // 资金账户ID
	Customeruserid          int64     `json:"customeruserid" xorm:"CUSTOMERUSERID"`          // 客户ID
	Customeraccountid       int64     `json:"customeraccountid" xorm:"CUSTOMERACCOUNTID"`    // 客户资金账户ID
	Signdate                time.Time `json:"signdate" xorm:"SIGNDATE"`                      // 签订日期
	Closestatus             int32     `json:"closestatus" xorm:"CLOSESTATUS"`                // 完结状态 - 0:未完结 1:已完结
	Relatedbizid            string    `json:"relatedbizid" xorm:"RELATEDBIZID"`              // 关联业务ID
	Goodunit                string    `json:"goodunit" xorm:"'GOODUNIT'"`                    // 报价单位
	Wrstandardcode          string    `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`       // 仓单标准代码
	Wrstandardname          string    `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`       // 仓单标准名称
	Deliverygoodscode       string    `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 交割商品代码
	Deliverygoodsname       string    `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 交割商品名称
}

// Erms3SpotContractApply 现货合同申请表
type Erms3SpotContractApply struct {
	SpotContractID     int64     `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'" binging:"required"` // 现货合同ID(345+Unix秒时间戳(10位)+xxxxxx)
	TradeDate          string    `json:"tradedate"  xorm:"'TRADEDATE'"`                              // 交易日(yyyyMMdd)
	ContractNo         string    `json:"contractno"  xorm:"'CONTRACTNO'"`                            // 现货合同编号
	ContractType       int32     `json:"contracttype"  xorm:"'CONTRACTTYPE'"`                        // 现货合同类型 - 1:采购 -1:销售
	AreaUserID         int32     `json:"areauserid"  xorm:"'AREAUSERID'"`                            // 所属机构
	AccountID          int64     `json:"accountid"  xorm:"'ACCOUNTID'"`                              // 资金账户ID
	CustomerUserID     int32     `json:"customeruserid"  xorm:"'CUSTOMERUSERID'"`                    // 客户ID
	CustomerAccountID  int64     `json:"customeraccountid"  xorm:"'CUSTOMERACCOUNTID'"`              // 客户资金账户ID
	SignDate           string    `json:"signdate"  xorm:"'SIGNDATE'"`                                // 签订日期
	LastDate           string    `json:"lastdate"  xorm:"'LASTDATE'"`                                // 交货时间
	ContractAttachment string    `json:"contractattachment"  xorm:"'CONTRACTATTACHMENT'"`            // 合同附件
	OriMarginPayer     int32     `json:"orimarginpayer"  xorm:"'ORIMARGINPAYER'"`                    // 初始保证金支付方 -1:买方 2:卖方
	OriMargin          float64   `json:"orimargin"  xorm:"'ORIMARGIN'"`                              // 初始保证金
	Remark             string    `json:"remark"  xorm:"'REMARK'"`                                    // 备注
	DetailJSON         string    `json:"detailjson"  xorm:"'DETAILJSON'"`                            // 明细JSON
	ApplyStatus        int32     `json:"applystatus"  xorm:"'APPLYSTATUS'"`                          // 申请状态 - 0:未审核 1:审核通过 2:审核中 3:审核失败 4已撤销 5:审核拒绝
	ApplySrc           int32     `json:"applysrc"  xorm:"'APPLYSRC'"`                                // 申请来源 - 1:管理端 2:终端
	MarketID           int32     `json:"marketid"  xorm:"'MARKETID'"`                                // 市场ID
	CreatorID          int32     `json:"creatorid"  xorm:"'CREATORID'"`                              // 申请人
	CreateTime         time.Time `json:"createtime"  xorm:"'CREATETIME'"`                            // 申请时间
	AuditID            int32     `json:"auditid"  xorm:"'AUDITID'"`                                  // 审核人
	AuditTime          time.Time `json:"audittime"  xorm:"'AUDITTIME'"`                              // 审核时间
	AuditRemark        string    `json:"auditremark"  xorm:"'AUDITREMARK'"`                          // 审核备注
}

// TableName is ERMS3_SPOTCONTRACTAPPLY
func (Erms3SpotContractApply) TableName() string {
	return "ERMS3_SPOTCONTRACTAPPLY"
}

// Erms3Biztradedetail 业务关联单据明细表
type Erms3Biztradedetail struct {
	Biztradedetailid     int64     `json:"biztradedetailid" xorm:"BIZTRADEDETAILID"`         // 业务关联单据明细ID(352+Unix秒时间戳(10位)+xxxxxx)
	Tradedetailid        int64     `json:"tradedetailid" xorm:"TRADEDETAILID"`               // 单据明细ID
	Relatedbiztype       int32     `json:"relatedbiztype" xorm:"RELATEDBIZTYPE"`             // 关联业务类型 - 1:期现套利 2:仓单回购 3:现货贸易
	Relatedbizid         int64     `json:"relatedbizid" xorm:"RELATEDBIZID"`                 // 关联业务ID
	Marketid             int64     `json:"marketid" xorm:"MARKETID"`                         // 业务市场ID
	Spotdetailid         int64     `json:"spotdetailid" xorm:"SPOTDETAILID"`                 // 标的明细ID
	Spotcontractid       int64     `json:"spotcontractid" xorm:"SPOTCONTRACTID"`             // 现货合同ID
	Contracttype         int32     `json:"contracttype" xorm:"CONTRACTTYPE"`                 // 现货合同类型 - 1:采购 -1:销售
	Bizaccountid         int64     `json:"bizaccountid" xorm:"BIZACCOUNTID"`                 // 业务现货资金账户
	Contractaccountid    int64     `json:"contractaccountid" xorm:"CONTRACTACCOUNTID"`       // 现货合同资金账户
	Tradedate            string    `json:"tradedate" xorm:"TRADEDATE"`                       // 交易日(yyyyMMdd)
	Relatedqty           float64   `json:"relatedqty" xorm:"RELATEDQTY"`                     // 关联数量
	Relatedamount        float64   `json:"relatedamount" xorm:"RELATEDAMOUNT"`               // 关联金额
	Cancelledqty         float64   `json:"cancelledqty" xorm:"CANCELLEDQTY"`                 // 撤销量
	Cancelledamount      float64   `json:"cancelledamount" xorm:"CANCELLEDAMOUNT"`           // 撤销金额
	Deliveryqty          float64   `json:"deliveryqty" xorm:"DELIVERYQTY"`                   // 交收数量(业务)
	Deliveryamount       float64   `json:"deliveryamount" xorm:"DELIVERYAMOUNT"`             // 交收金额(业务) - 按比例计算
	Deliveryovershortqty float64   `json:"deliveryovershortqty" xorm:"DELIVERYOVERSHORTQTY"` // 交收溢短数量(业务)
	Deliveryactualamount float64   `json:"deliveryactualamount" xorm:"DELIVERYACTUALAMOUNT"` // 交收实际金额(业务)
	Deliveryotheramount  float64   `json:"deliveryotheramount" xorm:"DELIVERYOTHERAMOUNT"`   // 交收其它费用(业务)
	Closestatus          int32     `json:"closestatus" xorm:"CLOSESTATUS"`                   // 完结状态 - 0:未完结 1:已完结
	Closetradedate       string    `json:"closetradedate" xorm:"CLOSETRADEDATE"`             // 完结交易日(yyyyMMdd)
	Updatetime           time.Time `json:"updatetime" xorm:"UPDATETIME"`                     // 更新时间
	Handlestatus         int32     `json:"handlestatus" xorm:"HANDLESTATUS"`                 // 处理状态

}

// TableName is ERMS3_BIZTRADEDETAIL
func (m *Erms3Biztradedetail) TableName() string {
	return "ERMS3_BIZTRADEDETAIL"
}

// AddSpotContractApply 插入现货合同记录
func AddSpotContractApply(spotContractApply Erms3SpotContractApply) error {
	engine := db.GetEngine()

	// 组织sql，插入现货合同申请信息
	sql := `INSERT INTO ERMS3_SPOTCONTRACTAPPLY(SPOTCONTRACTID,
			  TRADEDATE,
			  CONTRACTNO,
			  CONTRACTTYPE,
			  AREAUSERID,
			  ACCOUNTID,
			  CUSTOMERUSERID,
			  CUSTOMERACCOUNTID,
			  SIGNDATE,
			  CONTRACTATTACHMENT,
			  ORIMARGIN,
			  REMARK,
			  DETAILJSON,
			  APPLYSTATUS,
			  APPLYSRC,
			  CREATORID,
			  CREATETIME)
			VALUES(?,
				?,
				?,
				?,
				?,
				?,
				?,
				?,
				to_date(?, 'YYYY-MM-DD HH24:MI:SS'),
				?,
				?,
				?,
				?,
				?,
				?,
				?,
				sysdate)`
	_, err := engine.Exec(sql,
		spotContractApply.SpotContractID,
		spotContractApply.TradeDate,
		spotContractApply.ContractNo,
		spotContractApply.ContractType,
		spotContractApply.AreaUserID,
		spotContractApply.AccountID,
		spotContractApply.CustomerUserID,
		spotContractApply.CustomerAccountID,
		spotContractApply.SignDate,
		spotContractApply.ContractAttachment,
		spotContractApply.OriMargin,
		spotContractApply.Remark,
		spotContractApply.DetailJSON,
		spotContractApply.ApplyStatus,
		spotContractApply.ApplySrc,
		spotContractApply.CreatorID)

	return err
}

// QueryErms3SpotContractInfo 查询现货合同信息
func QueryErms3SpotContractInfo(accountids []int64, contracttype, contractmode, status int32) ([]Erms3SpotContractInfo, error) {
	datas := make([]Erms3SpotContractInfo, 0)
	engine := db.GetEngine()
	s := engine.Table("ERMS3_SPOTCONTRACTDETAIL").
		Join("LEFT", "ERMS3_SPOTCONTRACT", "ERMS3_SPOTCONTRACTDETAIL.SPOTCONTRACTID = ERMS3_SPOTCONTRACT.SPOTCONTRACTID").
		Join("LEFT", "ERMS3_BIZTRADEDETAIL", "ERMS3_BIZTRADEDETAIL.SPOTDETAILID = ERMS3_SPOTCONTRACTDETAIL.SPOTDETAILID").
		Join("LEFT", "WRSTANDARD", "WRSTANDARD.WRSTANDARDID = ERMS3_SPOTCONTRACTDETAIL.WRSTANDARDID").
		Join("LEFT", "DELIVERYGOODS", "DELIVERYGOODS.DELIVERYGOODSID = WRSTANDARD.DELIVERYGOODSID").
		Join("LEFT", "ENUMDICITEM", "WRSTANDARD.UNITID = ENUMDICITEM.ENUMITEMNAME AND ENUMDICITEM.ENUMDICCODE = 'goodsunit'").
		Select(`to_char(ERMS3_SPOTCONTRACTDETAIL.SPOTCONTRACTID) SPOTCONTRACTID, ERMS3_SPOTCONTRACT.AREAUSERID, ERMS3_SPOTCONTRACT.ACCOUNTID, ERMS3_SPOTCONTRACT.CUSTOMERUSERID, 
ERMS3_SPOTCONTRACT.CUSTOMERACCOUNTID, ERMS3_SPOTCONTRACT.CLOSESTATUS, ERMS3_SPOTCONTRACT.SIGNDATE, ERMS3_SPOTCONTRACTDETAIL.*, WRSTANDARD.WRSTANDARDNAME, WRSTANDARD.WRSTANDARDCODE, DELIVERYGOODS.DELIVERYGOODSNAME, DELIVERYGOODS.DELIVERYGOODSCODE, 
to_char(ERMS3_BIZTRADEDETAIL.RELATEDBIZID) RELATEDBIZID, ENUMDICITEM.ENUMDICNAME GOODUNIT`).
		Where("ERMS3_SPOTCONTRACT.CONTRACTTYPE = ? AND ERMS3_SPOTCONTRACT.CONTRACTMODE = ? AND ERMS3_SPOTCONTRACT.CLOSESTATUS = ?", contracttype, contractmode, status).
		In("ERMS3_SPOTCONTRACT.ACCOUNTID", accountids).
		Desc("ERMS3_SPOTCONTRACTDETAIL.SPOTCONTRACTID")
	err := s.Find(&datas)
	return datas, err
}

// QueryBizTradeDetailByBizID 根据关联业务ID查询.
func QueryBizTradeDetailByBizID(relatedbizid []int64) ([]Erms3Biztradedetail, error) {
	data := make([]Erms3Biztradedetail, 0)
	engine := db.GetEngine()
	err := engine.In("RELATEDBIZID", relatedbizid).Find(&data)
	return data, err
}

// Erms3BizTradeInfo 汇总数据.
type Erms3BizTradeInfo struct {
	Relatedbizid int64   // 关联业务ID
	Buyqty       float64 // 采购量.
	Buyamount    float64 // 采购额.
	Sellqty      float64 // 销售量.
	Sellamount   float64 // 销售额.
}

// QueryBizTradeInfo 根据关联业务ID查询汇总信息.
func QueryBizTradeInfo(relatedbizid []int64) (map[int64]Erms3BizTradeInfo, error) {
	data, err := QueryBizTradeDetailByBizID(relatedbizid)
	if err != nil {
		return nil, err
	}

	infos := make(map[int64]Erms3BizTradeInfo)
	for i := range data {
		info := infos[data[i].Relatedbizid]
		if 1 == data[i].Contracttype {
			info.Buyqty += data[i].Relatedqty - data[i].Cancelledqty
			info.Buyamount += data[i].Relatedamount - data[i].Cancelledamount
		} else if -1 == data[i].Contracttype {
			info.Sellqty += data[i].Relatedqty - data[i].Cancelledqty
			info.Sellamount += data[i].Relatedamount - data[i].Cancelledamount
		}

		infos[data[i].Relatedbizid] = info
	}

	return infos, nil
}