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           time.Time `json:"signdate"  xorm:"'SIGNDATE'"`                                // 签订日期
	LastDate           time.Time `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"
}

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

	// 组织sql，插入现货合同申请信息
	sql := `INSERT INTO ERMS3_SPOTCONTRACTAPPLY(SPOTCONTRACTID,
			  TRADEDATE,
			  CONTRACTNO,
			  CONTRACTTYPE,
			  AREAUSERID,
			  ACCOUNTID,
			  CUSTOMERUSERID,
			  CUSTOMERACCOUNTID,
			  SIGNDATE,
			  LASTDATE,
			  CONTRACTATTACHMENT,
			  ORIMARGINPAYER,
			  ORIMARGIN,
			  REMARK,
			  DETAILJSON,
			  APPLYSTATUS,
			  APPLYSRC,
			  MARKETID,
			  CREATORID,
			  CREATETIME)
			VALUES(?,
				?,
				?,
				?,
				?,
				?,
				?,
				?,
				to_date(?, 'YYYY-MM-DD HH24:MI:SS'),
				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.LastDate,
		spotContractApply.ContractAttachment,
		spotContractApply.OriMarginPayer,
		spotContractApply.OriMargin,
		spotContractApply.Remark,
		spotContractApply.DetailJSON,
		spotContractApply.ApplyStatus,
		spotContractApply.ApplySrc,
		spotContractApply.MarketID,
		spotContractApply.CreatorID)

	return err
}

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
}