MTP20_IF/models/ermcpExposure.go

/**
* @Author: zou.yingbin
* @Create  : 2021/1/18 9:26
* @Modify  : 2021/1/18 9:26
 */

package models

import (
	"fmt"
	"github.com/golang/protobuf/proto"
	"mtp2_if/db"
	"mtp2_if/mtpcache"
	"mtp2_if/pb"
	"mtp2_if/rediscli"
)

//实时敞口
type ErmcpRealExposureModel struct {
	MiddleGoodsID      uint32  // 套保品种
	AreaUserID         uint32  // 所属机构
	OriBuyPlanQty      float64 // 期初采购计划数量
	OriBuyPricedQty    float64 // 期初采购合同已定价数量
	OriSellPlanQty     float64 // 期初销售计划数量
	OriSellPricedQty   float64 // 期初销售合同已定价数量
	OriBuyFutureQty    uint64  // 期初买入期货数量
	OriSellFutureQty   uint64  // 期初卖出期货数量
	BuyPlanQty         float64 // 采购计划数量
	BuyPricedQty       float64 // 采购合同已定价数量
	SellPlanQty        float64 // 销售计划数量
	SellPricedQty      float64 // 销售合同已定价数量
	BuyFutureQty       float64 // 买入期货数量
	SellFutureQty      float64 // 卖出期货数量
	TotalSpotQty       float64 // 现货数量
	TotalFutureQty     float64 // 期货数量
	TotalExposure      float64 // 总敞口
	TotalHedgeRatio    float64 // 敞口比例
	TotalNeedHedgeQty  float64 // 期货应套保量
	NeedHedgeExposoure float64 // 应套保敞口
	NeedHedgeRatio     float64 // 应套保敞口比例
	//************以下需计算或非redis数据************//
	MiddleGoodsName       string  // 套保品种名称
	MiddleGoodsCode       string  // 套保品种代码
	MiddleGoodsHedgeRatio float64 // 应套保比例
	OriTotalSpotQty       float64 // 期初现货数量=(期初销售计划数量-期初销售合同已定价数量)-(期初采购计划数量-期初采购合同已定价数量)
	OriTotalFutuQty       uint64  // 期初期货数量=期初买入期货数量-期初卖出期货数量
}

// 计算相关字段
func (r *ErmcpRealExposureModel) calc() {
	r.OriTotalSpotQty = (r.OriSellPlanQty - r.OriSellPricedQty) - (r.OriBuyPlanQty - r.OriBuyPricedQty)
	r.OriTotalFutuQty = r.OriBuyFutureQty - r.OriSellFutureQty
}

func (r *ErmcpRealExposureModel) ParseFromProto(v *pb.ErmcpAreaExposure) {
	r.MiddleGoodsID = *v.MiddleGoodsID
	r.AreaUserID = *v.AreaUserID
	r.OriBuyPlanQty = *v.OriBuyPlanQty
	r.OriBuyPricedQty = *v.OriBuyPricedQty
	r.OriSellPlanQty = *v.OriSellPlanQty
	r.OriSellPricedQty = *v.OriSellPricedQty
	r.OriBuyFutureQty = *v.OriBuyFutureQty
	r.OriSellFutureQty = *v.OriSellFutureQty
	r.BuyPlanQty = *v.BuyPlanQty
	r.BuyPricedQty = *v.BuyPricedQty
	r.SellPlanQty = *v.SellPlanQty
	r.SellPricedQty = *v.SellPricedQty
	r.BuyFutureQty = *v.BuyFutureQty
	r.SellFutureQty = *v.SellFutureQty
	r.TotalSpotQty = *v.TotalSpotQty
	r.TotalFutureQty = *v.TotalFutureQty
	r.TotalExposure = *v.TotalExposure
	r.TotalHedgeRatio = *v.TotalHedgeRatio
	r.TotalNeedHedgeQty = *v.TotalNeedHedgeQty
	r.NeedHedgeExposoure = *v.NeedHedgeExposoure
	r.NeedHedgeRatio = *v.NeedHedgeRatio
	// 执行相关计算
	r.calc()
}

// 实时敞口数据: Redis数据 + 套保品种信息表
func (r *ErmcpRealExposureModel) GetData() ([]ErmcpRealExposureModel, error) {
	// 获取关联的套路商品
	if sGoods, err := mtpcache.GetMiddleGoodsByUserID(r.AreaUserID); err == nil {
		sData := make([]ErmcpRealExposureModel, 0)
		// 从Redis获取数据
		for i := range sGoods {
			key := fmt.Sprintf("ErmcpAreaExposure:%d_%d", sGoods[i].AREAUSERID, sGoods[i].MIDDLEGOODSID)
			if ret, err := rediscli.GetRedisClient().Get(key).Result(); err == nil {
				if len(ret) > 0 {
					var data pb.ErmcpAreaExposure
					if err := proto.Unmarshal([]byte(ret), &data); err == nil {
						var m ErmcpRealExposureModel
						m.ParseFromProto(&data)
						sData = append(sData, m)
					}
				}
			}
		}

		return sData, nil
	}

	return nil, nil
}

/*************敞口明细**************/

//敞口明细结构
type ErmcpExposureDetailModel struct {
	Createtime      string  `json:"createtime"  xorm:"'createtime'"`           // 时间
	Areauserid      uint32  `json:"areauserid"  xorm:"'areauserid'"`           // 机构ID
	Logtype         int32   `json:"logtype"  xorm:"'logtype'"`                 // 类型 - 1:套保计划 2:现货合同
	Contracttype    int32   `json:"contracttype"  xorm:"'contracttype'"`       // 现货合同类型 - 1:采购 -1:销售
	Wrstandardid    int32   `json:"wrstandardid"  xorm:"'wrstandardid'"`       // 现货商品ID
	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
	Wrstandardname  string  `json:"wrstandardname"  xorm:"'wrstandardname'"`   // 现货商品名称
	Wrstandardcode  string  `json:"wrstandardcode"  xorm:"'wrstandardcode'"`   // 现货商品代码
	Enumdicname     string  `json:"enumdicname"  xorm:"'enumdicname'"`         // 现货商品单位名称
	ChangeQty       float64 `json:"changeQty"  xorm:"'changeQty'"`             // 套保变动量
	Convertfactor   float64 `json:"convertfactor"  xorm:"'convertfactor'"`     // 标仓系数
	Convertratio    float64 `json:"convertratio"  xorm:"'convertratio'"`       // 套保系数
}

func (r *ErmcpExposureDetailModel) buildSql() string {
	str := "with tmp as" +
		" (select 2                as LogType," +
		"         s.spotcontractid as relatedid," +
		"         s.contractno     as relateNo" +
		"    from ermcp_spotcontract s" +
		"  union all" +
		"  select 1, t.hedgeplanid as relateid, t.hedgeplanno as relateNo" +
		"    from ermcp_hedgeplan t)" +
		"select to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       t.middlegoodsid," +
		"       t.areauserid," +
		"       t.logtype," +
		"       t.contracttype," +
		"       wc.wrstandardid," +
		"       t.qty," +
		"       t.convertfactor," +
		"       t.convertratio," +
		"       t.qty * t.convertfactor * t.convertratio as changeQty," +
		"       tmp.relateNo," +
		"       m.middlegoodsname," +
		"       m.middlegoodscode," +
		"       w.unitid," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       e.enumdicname" +
		"  from ermcp_spotexposurelog t" +
		"  left join erms2_wrsconvertdetail wc" +
		"    on t.wrstandardid = wc.wrstandardid" +
		"   and t.middlegoodsid = wc.middlegoodsid" +
		"  left join erms_middlegoods m" +
		"    on t.middlegoodsid = m.middlegoodsid" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  left join enumdicitem e" +
		"    on w.unitid = e.enumitemname" +
		"   and e.enumdiccode = 'goodsunit'" +
		"  left join tmp" +
		"    on t.logtype = tmp.LogType" +
		"   and t.relatedid = tmp.relatedid" +
		"  where t.middlegoodsid=%v and t.areauserid=%v"

	return fmt.Sprintf(str, r.MiddlegoodsId, r.Areauserid)
}

// 查询敞口现货明细
func (r *ErmcpExposureDetailModel) GetData() ([]ErmcpExposureDetailModel, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]ErmcpExposureDetailModel, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	return sData, nil
}

/// 现货头寸

// 现货头寸数据
type AreaSpotModel struct {
	WRSTANDARDID     int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`     // 现货商品ID
	WRSTANDARDNAME   string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"` // 现货品种
	WRSTANDARDCODE   string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"` // 现货品种代码
	AREAUSERID       int32   `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'"`         // 更新时间
}

// 进行相关字段的值计算
func (r *AreaSpotModel) calc() {
	r.IncreaseQty = r.SELLPLANQTY + r.BUYPRICEDQTY
	r.DecreaseQty = (r.SELLPRICEDQTY + r.BUYPLANQTY) * -1
	r.OriToalSpotQty = (r.ORISELLPLANQTY - r.ORISELLPRICEDQTY) - (r.ORIBUYPLANQTY - r.ORIBUYPRICEDQTY)
}

func (r *AreaSpotModel) buildSql() string {
	str := "select t.WRSTANDARDID," +
		"       w.WRSTANDARDNAME," +
		"       w.WRSTANDARDCODE," +
		"       t.AREAUSERID," +
		"       t.ORIBUYPLANQTY," +
		"       t.ORIBUYPRICEDQTY," +
		"       t.ORISELLPLANQTY," +
		"       t.ORISELLPRICEDQTY," +
		"       t.BUYPLANQTY," +
		"       t.BUYPRICEDQTY," +
		"       t.SELLPLANQTY," +
		"       t.SELLPRICEDQTY," +
		"       t.TOTALSPOTQTY," +
		"       to_char(t.UPDATETIME,'yyyy-mm-dd hh24:mi:ss') UPDATETIME" +
		"  from ermcp_areaspot t" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  where t.areauserid=%v"
	return fmt.Sprintf(str, r.AREAUSERID)
}

// 从数据库中查询现货头寸
func (r *AreaSpotModel) GetData() ([]AreaSpotModel, error) {
	e := db.GetEngine()
	sData := make([]AreaSpotModel, 0)
	if err := e.SQL(r.buildSql()).Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// 现货头寸-现货明细
type ErmcpAreaSpotDetailModel 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-销售
	Wrstandardname string  `json:"wrstandardname"  xorm:"'wrstandardname'"` // 现货商品名称
	Wrstandardcode string  `json:"wrstandardcode"  xorm:"'wrstandardcode'"` // 现货商品代码
	Qty            float64 `json:"qty"  xorm:"'qty'"`                       // 数量
	Strtime        string  `json:"strtime"  xorm:"'strtime'"`               // 时间
	Enumdicname    string  `json:"enumdicname"`                             // 现货商品单位名称
	Recordname     string  `json:"recordname"`                              // 类型名称
	Unitid         int32   `json:"-"  xorm:"'UNITID'"`                      // 单位ID
	UserId         int     `json:"-"`                                       // 所属用户ID
	WrstandardId   int32   `json:"-"`                                       // 现货商品ID
}

// 组建查询SQL
func (r *ErmcpAreaSpotDetailModel) buildSql() string {
	str := "select to_char(t.hedgeplanid) relatedid," +
		"       t.hedgeplanno relatedno," +
		"       1 as logType," +
		"       t.contracttype," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       t.planqty qty," +
		"        w.unitid," +
		"       to_char(t.createtime, 'yyyy-mm-dd hh:mi:ss') strtime" +
		"  from ermcp_hedgeplan t" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		" where t.hedgeplanstatus in (2, 3, 5)" +
		"   and t.areauserid = %v and t.wrstandardid = %v" +
		" union all " +
		"select to_char(t.spotcontractid)," +
		"       t.contractno," +
		"       1 as logType," +
		"       t.contracttype," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       t.qty," +
		"       w.unitid," +
		"       to_char(t.createtime, 'yyyy-mm-dd hh:mi:ss') strtime" +
		"  from ermcp_spotcontract t" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		" where t.contractstatus in (2, 3, 5)" +
		"   and t.userid = %v and t.wrstandardid = %v"

	return fmt.Sprintf(str, r.UserId, r.WrstandardId, r.UserId, r.WrstandardId)
}

// 现货头寸-明细:数据加工处理
func (r *ErmcpAreaSpotDetailModel) Calc() {
	var logTypeName, contractTypeName string
	if r.LogType == 1 {
		logTypeName = "计划"
	} else {
		logTypeName = "合同"
	}

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

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

// 现货头寸-明细:从数据库中查询敞口现货头寸明细数据
func (r *ErmcpAreaSpotDetailModel) GetData() ([]interface{}, error) {
	sData := make([]ErmcpAreaSpotDetailModel, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	iDatas := make([]interface{}, 0)
	for i := range sData {
		// 注意要传指针类型(&sData[i])，因为要接口转换
		iDatas = append(iDatas, &sData[i])
	}
	return iDatas, err
}

///////////////////////////

/*历史/历史敞口*/
type ErmcpHisExposure struct {
	RECKONDATE         string  `json:"reckondate"  xorm:"'RECKONDATE'"`                 // 日照时期(yyyyMMdd)
	MIDDLEGOODSID      int32   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`           // 套保品种ID
	AREAUSERID         int32   `json:"areauserid"  xorm:"'AREAUSERID'"`                 // 所属机构
	TOTALSPOTQTY       float64 `json:"totalspotqty"  xorm:"'TOTALSPOTQTY'"`             // 现货头寸总量
	TOTALFUTUREQTY     float64 `json:"totalfutureqty"  xorm:"'TOTALFUTUREQTY'"`         // 期货头寸总量
	TOTALEXPOSURE      float64 `json:"totalexposure"  xorm:"'TOTALEXPOSURE'"`           // 总敞口
	TOTALHEDGERATIO    float64 `json:"totalhedgeratio"  xorm:"'TOTALHEDGERATIO'"`       // 敞口比例
	TOTALNEEDHEDGEQTY  float64 `json:"totalneedhedgeqty"  xorm:"'TOTALNEEDHEDGEQTY'"`   // 应套保总量
	NEEDHEDGEEXPOSOURE float64 `json:"needhedgeexposourE"  xorm:"'NEEDHEDGEEXPOSOURE'"` // 应套保敞口
	NEEDHEDGERATIO     float64 `json:"needhedgeratio"  xorm:"'NEEDHEDGERATIO'"`         // 应套保敞口比例
	MIDDLEGOODSNAME    string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`       // 套保品种名称
	MIDDLEGOODSCODE    string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"`       // 套保品种代码
	LastNum            int32   `json:"-"`                                               // 查询条数
}

// 历史分品种嵌套结构
type ErmcpHisExposureS struct {
	MIDDLEGOODSID   int32              `json:"middlegoodsid"`   // 套保品种ID
	MIDDLEGOODSNAME string             `json:"middlegoodsname"` // 套保品种名称
	Data            []ErmcpHisExposure `json:"data"`            // 历史敞口
}

func (r *ErmcpHisExposure) buildSql() string {
	str := "select *" +
		"  from (select t.RECKONDATE," +
		"               t.MIDDLEGOODSID," +
		"               t.AREAUSERID," +
		"               t.TOTALSPOTQTY," +
		"               t.TOTALFUTUREQTY," +
		"               t.TOTALEXPOSURE," +
		"               t.TOTALHEDGERATIO," +
		"               t.TOTALNEEDHEDGEQTY," +
		"               t.NEEDHEDGEEXPOSOURE," +
		"               t.NEEDHEDGERATIO," +
		"               g.MIDDLEGOODSNAME," +
		"               g.MIDDLEGOODSCODE" +
		"          from Reckon_ERMCP_AreaExposure t" +
		"          left join erms_middlegoods g" +
		"            on t.middlegoodsid = g.middlegoodsid" +
		"         where t.areauserid = %v" +
		"         order by t.middlegoodsid, t.reckondate desc) a" +
		" where rownum <= %v"
	if r.LastNum <= 0 {
		r.LastNum = 100000 //限制最多查10W条
	}
	return fmt.Sprintf(str, r.AREAUSERID, r.LastNum)
}

// 获取历史敞口
func (r *ErmcpHisExposure) GetData() ([]ErmcpHisExposure, error) {
	e := db.GetEngine()
	sData := make([]ErmcpHisExposure, 0)
	if err := e.SQL(r.buildSql()).Find(&sData); err != nil {
		return nil, err
	}
	return sData, nil
}