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"
	"mtp2_if/utils"
)

// ErmcpRealExposureModel 实时敞口
type ErmcpRealExposureModel struct {
	MiddleGoodsID         uint32  `json:"MiddleGoodsID"  xorm:"'MiddleGoodsID'"`           // 套保品种
	AreaUserID            uint32  `json:"AreaUserID"  xorm:"'AreaUserID'"`                 // 所属机构
	OriBuyPlanQty         float64 `json:"OriBuyPlanQty"  xorm:"'OriBuyPlanQty'"`           // 期初采购计划数量
	OriBuyPricedQty       float64 `json:"OriBuyPricedQty"  xorm:"'OriBuyPricedQty'"`       // 期初采购合同已定价数量
	OriSellPlanQty        float64 `json:"OriSellPlanQty"  xorm:"'OriSellPlanQty'"`         // 期初销售计划数量
	OriSellPricedQty      float64 `json:"OriSellPricedQty"  xorm:"'OriSellPricedQty'"`     // 期初销售合同已定价数量
	OriBuyFutureQty       float64 `json:"OriBuyFutureQty"  xorm:"'OriBuyFutureQty'"`       // 期初买入期货数量
	OriSellFutureQty      float64 `json:"OriSellFutureQty"  xorm:"'OriSellFutureQty'"`     // 期初卖出期货数量
	BuyPlanQty            float64 `json:"BuyPlanQty"  xorm:"'BuyPlanQty'"`                 // 采购计划数量
	BuyPricedQty          float64 `json:"BuyPricedQty"  xorm:"'BuyPricedQty'"`             // 采购合同已定价数量
	SellPlanQty           float64 `json:"SellPlanQty"  xorm:"'SellPlanQty'"`               // 销售计划数量
	SellPricedQty         float64 `json:"SellPricedQty"  xorm:"'SellPricedQty'"`           // 销售合同已定价数量
	BuyFutureQty          float64 `json:"BuyFutureQty"  xorm:"'BuyFutureQty'"`             // 买入期货数量
	SellFutureQty         float64 `json:"SellFutureQty"  xorm:"'SellFutureQty'"`           // 卖出期货数量
	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'"`         // 应套保敞口比例
	GOODSUNITID           int32   `json:"-"  xorm:"'GOODSUNITID'"`                         // 品种单位ID
	MiddleGoodsName       string  `json:"MiddleGoodsName"  xorm:"'MiddleGoodsName'"`       // 套保品种名称
	MiddleGoodsCode       string  `json:"MiddleGoodsCode"  xorm:"'MiddleGoodsCode'"`       // 套保品种代码
	MiddleGoodsHedgeRatio float64 `json:"MiddleGoodsHedgeRatio"  xorm:"'needhedgeratio'"`  // 套保比例
	NEEDARBITRAGEQTY      float64 `json:"needarbitrageqty"  xorm:"'NEEDARBITRAGEQTY'"`     // 应套利量
	NEEDHEDGEQTY          float64 `json:"needhedgeqty"  xorm:"'NEEDHEDGEQTY'"`             // 应套保量
	HEDGEQTY              float64 `json:"hedgeqty"  xorm:"'hedgeqty'"`                     // 套保量
	ARBITRAGEQTY          float64 `json:"arbitrageqty"  xorm:"'arbitrageqty'"`             // 套利量
	NEEDARBITRAGERATIO    float64 `json:"needarbitrageratio"  xorm:"'needarbitrageratio'"` // 套利比例

	ORIHEDGEQTY           float64 `json:"orihedgeqty"  xorm:"'ORIHEDGEQTY'"`                     // 期初套保量
	ORIARBITRAGEQTY       float64 `json:"oriarbitrageqty"  xorm:"'ORIARBITRAGEQTY'"`             // 期初套利量
	ORITOTALNEEDHEDGEQTY  float64 `json:"oritotalneedhedgeqty"  xorm:"'ORITOTALNEEDHEDGEQTY'"`   // 期初现货应套保量
	ORITOTALFUTUREQTY     float64 `json:"oritotalfutureqty"  xorm:"'ORITOTALFUTUREQTY'"`         // 期初期货总量
	ORINEEDHEDGEEXPOSOURE float64 `json:"orineedhedgeexposoure"  xorm:"'ORINEEDHEDGEEXPOSOURE'"` // 期初套保敞口
	ORITOTALEXPOSURE      float64 `json:"oritotalexposure"  xorm:"'ORITOTALEXPOSURE'"`           // 期初总敞口

	ENUMDICNAME     string  // 单位名称
	OriTotalSpotQty float64 // 期初现货数量=(期初销售计划数量-期初销售合同已定价数量)-(期初采购计划数量-期初采购合同已定价数量)
	OriTotalFutuQty float64 // 期初期货数量=期初买入期货数量-期初卖出期货数量
	DiffSpotQty     float64 // 变动量(现货总量) = 现货数量 - 期初现货数量
	DiffFutuQty     float64 // 变动量(期货总量)

	DiffHedgeQty     float64 // 套保变动量
	DiffArbitrageQty float64 // 套利变动量
	DiffSpotHedgeQty float64 // 变动量(现货应套保总量)
	DiffExposoureQty float64 // 变动量(套保敞口)

}

// calc 计算相关字段
func (r *ErmcpRealExposureModel) calc() {
	r.OriTotalSpotQty = (r.OriSellPlanQty - r.OriSellPricedQty) - (r.OriBuyPlanQty - r.OriBuyPricedQty)
	r.OriTotalFutuQty = r.OriBuyFutureQty - r.OriSellFutureQty
	r.DiffSpotQty = r.TotalSpotQty - r.OriTotalSpotQty
	r.DiffFutuQty = r.TotalFutureQty - r.ORITOTALFUTUREQTY
	r.DiffHedgeQty = r.HEDGEQTY - r.ORIHEDGEQTY
	r.DiffArbitrageQty = r.ARBITRAGEQTY - r.ORIHEDGEQTY
	r.DiffSpotHedgeQty = r.TotalNeedHedgeQty - r.ORITOTALNEEDHEDGEQTY
	r.DiffExposoureQty = r.NeedHedgeExposoure - r.ORINEEDHEDGEEXPOSOURE

	if r.ENUMDICNAME == "" {
		r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODSUNITID)
	}
}

func (r *ErmcpRealExposureModel) buildSql() string {
	var sqlId utils.SQLVal = "select t.MiddleGoodsID," +
		"       t.AreaUserID," +
		"       t.OriBuyPlanQty," +
		"       t.OriBuyPricedQty," +
		"       t.OriSellPlanQty," +
		"       t.OriSellPricedQty," +
		"       t.OriBuyFutureQty," +
		"       t.OriSellFutureQty," +
		"       t.BuyPlanQty," +
		"       t.BuyPricedQty," +
		"       t.SellPlanQty," +
		"       t.SellPricedQty," +
		"       t.BuyFutureQty," +
		"       t.SellFutureQty," +
		"       t.TotalSpotQty," +
		"       t.TotalFutureQty," +
		"       t.TotalExposure," +
		"       t.TotalHedgeRatio," +
		"       t.TotalNeedHedgeQty," +
		"       t.NeedHedgeExposoure," +
		"       t.NeedHedgeRatio," +
		"       g.middlegoodsname," +
		"       g.middlegoodscode," +
		"       g.goodsunitid," +
		"       g.needhedgeratio," +
		"       t.needarbitrageqty," +
		"       t.needhedgeqty," +
		"       t.hedgeqty," +
		"       t.arbitrageqty," +
		"       t.orihedgeqty," +
		"       t.oriarbitrageqty," +
		"       t.oritotalneedhedgeqty," +
		"       t.oritotalfutureqty," +
		"       t.orineedhedgeexposoure," +
		"       t.oritotalexposure," +
		"       g.needarbitrageratio" +
		"  from ermcp_areaexposure t" +
		"  left join erms_middlegoods g" +
		"    on t.middlegoodsid = g.middlegoodsid" +
		" where 1 = 1"
	sqlId.And("t.AreaUserID", r.AreaUserID)
	return sqlId.String()
}

// ParseFromProto 从协议中获取数据
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 = float64(*v.OriBuyFutureQty)
	r.OriSellFutureQty = float64(*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()
}

// GetData 实时敞口数据: 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{MiddleGoodsName: sGoods[i].MIDDLEGOODSNAME,
							MiddleGoodsCode: sGoods[i].MIDDLEGOODSCODE, MiddleGoodsHedgeRatio: sGoods[i].NEEDHEDGERATIO,
							ENUMDICNAME: mtpcache.GetEnumDicitemName(sGoods[i].GOODSUNITID)}
						m.ParseFromProto(&data)
						sData = append(sData, m)
					}
				}
			}
		}

		return sData, nil
	}

	return nil, nil
}

// GetDataEx 从数据库中查询实时敞口
func (r *ErmcpRealExposureModel) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpRealExposureModel, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

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

// ErmcpExposureDetailModel 敞口现货明细结构
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," +
		"         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," +
		"       wc.wrstandardid," +
		"       t.qty / t.convertfactor / t.convertratio as qty," +
		"       t.convertfactor," +
		"       t.convertratio," +
		"       t.qty 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 and t.tradedate=to_char(sysdate, 'yyyymmdd')"

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

// Calc 处理数据
func (r *ErmcpExposureDetailModel) 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
	}
}

// GetData 查询敞口现货明细
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
	}
	for i := range sData {
		sData[i].Calc()
	}
	return sData, nil
}

/// 现货头寸

// AreaSpotModel 现货头寸数据
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'"`         // 更新时间
}

// calc 进行相关字段的值计算
func (r *AreaSpotModel) 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 *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)
}

// GetData 从数据库中查询现货头寸
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
}

// ErmcpAreaSpotDetailModel 现货头寸-现货明细
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"`                              // 类型名称
	CREATETIME     string  `json:"createtime"  xorm:"'CREATETIME'"`         // 创建时间
	Unitid         int32   `json:"-"  xorm:"'UNITID'"`                      // 单位ID
	UserId         int     `json:"-"`                                       // 所属用户ID
	WrstandardId   int32   `json:"-"`                                       // 现货商品ID
}

// buildSql 组建查询SQL(只查当前日期的)
func (r *ErmcpAreaSpotDetailModel) buildSql() string {
	str := "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.wrstandardid = %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.wrstandardid = %v)" +
		"select t.relatedid," +
		"       tmp.relatedno," +
		"       t.LogType," +
		"       tmp.contracttype," +
		"       t.RealQty qty," +
		"       to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid" +
		"  from ermcp_spotlog t" +
		" inner join tmp" +
		"    on t.LogType = tmp.logType" +
		"   and t.relatedid = tmp.relatedid" +
		"   and t.areauserid = %v" +
		"   and t.wrstandardid = %v" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  where t.tradedate=to_char(sysdate, 'yyyymmdd')"

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

// Calc 现货头寸-明细:数据加工处理
func (r *ErmcpAreaSpotDetailModel) 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)
}

// GetData 现货头寸-明细:从数据库中查询敞口现货头寸明细数据
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
}

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

// ErmcpHisExposure 历史/历史敞口
type ErmcpHisExposure struct {
	RECKONDATE         string  `json:"reckondate"  xorm:"'RECKONDATE'"`                 // 日照时期(yyyyMMdd)
	MIDDLEGOODSID      int32   `json:"-"  xorm:"'MIDDLEGOODSID'"`                       // 套保品种ID
	AREAUSERID         int32   `json:"-"  xorm:"'AREAUSERID'"`                          // 所属机构
	TOTALEXPOSURE      float64 `json:"totalexposure"  xorm:"'TOTALEXPOSURE'"`           // 总敞口
	NEEDHEDGEEXPOSOURE float64 `json:"needhedgeexposoure"  xorm:"'NEEDHEDGEEXPOSOURE'"` // 应套保敞口
	MIDDLEGOODSNAME    string  `json:"-"  xorm:"'MIDDLEGOODSNAME'"`                     // 套保品种名称
	MIDDLEGOODSCODE    string  `json:"-"  xorm:"'MIDDLEGOODSCODE'"`                     // 套保品种代码
	GOODSUNITID        int32   `json:"-"  xorm:"'GOODSUNITID'"`                         // 套保品种单位id

	LastNum int32 `json:"-"` // 查询条数
}

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

func (r *ErmcpHisExposure) buildSql() string {
	str := "select a.*, g.middlegoodsname, g.middlegoodscode, g.goodsunitid from (" +
		"select t.middlegoodsid," +
		"       t.areauserid," +
		"       t.totalexposure," +
		"       t.needhedgeexposoure," +
		"       t.reckondate" +
		"  from RECKON_ERMCP_AREAEXPOSURE t" +
		" inner join RECKON_ERMCP_AREAEXPOSURE t2" +
		"    on t.middlegoodsid = t2.middlegoodsid" +
		"   and t.reckondate <= t2.reckondate" +
		"   where t.areauserid=%v" +
		" group by t.middlegoodsid," +
		"          t.areauserid," +
		"          t.reckondate," +
		"          t.totalexposure," +
		"          t.needhedgeexposoure" +
		" having count(1) <= %v" +
		" order by t.middlegoodsid, t.reckondate desc" +
		")a left join erms_middlegoods g on a.middlegoodsid=g.middlegoodsid"
	if r.LastNum <= 0 {
		r.LastNum = 1000 //限制最多查1000条(每个品种)
	}
	return fmt.Sprintf(str, r.AREAUSERID, r.LastNum)
}

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

	// 按套保品种合并处理
	mData := make(map[int32]*[]ErmcpHisExposure)
	for i := range sData {
		if v, ok := mData[sData[i].MIDDLEGOODSID]; ok {
			*v = append(*v, sData[i])
		} else {
			d := make([]ErmcpHisExposure, 0)
			d = append(d, sData[i])
			mData[sData[i].MIDDLEGOODSID] = &d
		}
	}

	lst := make([]ErmcpHisExposureS, 0)
	for _, v := range mData {
		if len(*v) >= 1 {
			val := ErmcpHisExposureS{
				MIDDLEGOODSID:   (*v)[0].MIDDLEGOODSID,
				MIDDLEGOODSCODE: (*v)[0].MIDDLEGOODSCODE,
				MIDDLEGOODSNAME: (*v)[0].MIDDLEGOODSNAME,
				ENUMDICNAME:     mtpcache.GetEnumDicitemName((*v)[0].GOODSUNITID),
			}

			// 旧的敞口数据找不到对应的套保商品
			if val.MIDDLEGOODSCODE == "" {
				continue
			}

			for i := range *v {
				val.Data = append(val.Data, (*v)[i])
			}
			lst = append(lst, val)
		}
	}
	return lst, nil
}

// ErmcpTradeGoods 套保品种关联交易商品
type ErmcpTradeGoods struct {
	MIDDLEGOODSID    int32   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`       // 套保商品id
	MIDDLEGOODSNAME  string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`   // 套保商品名称
	MIDDLEGOODSCODE  string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"`   // 套保商品代码
	SRCGOODSGROUPID  int32   `json:"srcgoodsgroupid"  xorm:"'SRCGOODSGROUPID'"`   // 源期货品种id
	DESTGOODSGROUPID int32   `json:"destgoodsgroupid"  xorm:"'DESTGOODSGROUPID'"` // 目标期货品种id
	CONVERTRATIO     float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"`         // 折算系数
	GOODSID          int32   `json:"goodsid"  xorm:"pk 'GOODSID'"`                // 商品id
	GOODSCODE        string  `json:"goodscode"  xorm:"'GOODSCODE'"`               // 商品代码
	GOODSNAME        string  `json:"goodsname"  xorm:"'GOODSNAME'"`               // 商品名称
	GOODSGROUPID     int32   `json:"goodsgroupid"  xorm:"'GOODSGROUPID'"`         // 商品组id
	GOODSUNITID      int32   `json:"goodsunitid"  xorm:"'GOODSUNITID'"`           // 套保品种单位id
	AREAUSERID       int32   `json:"areauserid"  xorm:"'AREAUSERID'"`             // 所属机构id
	AGREEUNIT        float64 `json:"agreeunit"  xorm:"'agreeunit'"`               // 合约单位
}

func (r *ErmcpTradeGoods) buildSq() string {
	sqlId := "SELECT t.MIDDLEGOODSID," +
		"       t.MIDDLEGOODSNAME," +
		"       t.MIDDLEGOODSCODE," +
		"       t.GOODSUNITID," +
		"       t.AREAUSERID," +
		"       c.SRCGOODSGROUPID," +
		"       c.DESTGOODSGROUPID," +
		"       c.CONVERTRATIO," +
		"       g.GOODSID," +
		"       g.GOODSCODE," +
		"       g.GOODSNAME," +
		"       g.GOODSGROUPID," +
		"       g.AGREEUNIT" +
		"  FROM ERMS_MIDDLEGOODS t" +
		" INNER JOIN ERMCP_GGCONVERTCONFIG c" +
		"    ON t.GOODSGROUPID = c.DESTGOODSGROUPID" +
		" INNER JOIN GOODS g" +
		"    ON c.SRCGOODSGROUPID = g.GOODSGROUPID" +
		" WHERE t.AREAUSERID = %v" +
		"   AND t.MIDDLEGOODSID = %v"
	sqlId = fmt.Sprintf(sqlId, r.AREAUSERID, r.MIDDLEGOODSID)
	return sqlId
}

// GetData 获取套保商品关联的交易商品
func (r *ErmcpTradeGoods) GetData() (map[int32]*ErmcpTradeGoods, error) {
	mData := make(map[int32]*ErmcpTradeGoods, 0)
	err := db.GetEngine().SQL(r.buildSq()).Find(&mData)
	return mData, err
}

// ErmcpTradePosition 获取用户头寸(子账户)
type ErmcpTradePosition struct {
	USERID             int32 `json:"userid"  xorm:"'userid'"`                         // 用户id
	GOODSID            int32 `json:"goodsid"  xorm:"'goodsid'"`                       // 商品id
	BUYPOSITIONQTY     int64 `json:"buypositionqty"  xorm:"'Buypositionqty'"`         // 买期初持仓
	BUYCURPOSITIONQTY  int64 `json:"buycurpositionqty"  xorm:"'Buycurpositionqty'"`   // 买当前持仓
	SELLPOSITIONQTY    int64 `json:"sellpositionqty"  xorm:"'Sellpositionqty'"`       // 卖期初持仓
	SELLCURPOSITIONQTY int64 `json:"sellcurpositionqty"  xorm:"'Sellcurpositionqty'"` // 卖当前持仓
}

func (r *ErmcpTradePosition) buildSql() string {
	sqlId := "select a.relateduserid userid," +
		"       a.goodsid," +
		"       sum(a.buypositionqty) buypositionqty," +
		"       sum(a.buycurpositionqty) buycurpositionqty," +
		"       sum(a.sellpositionqty) sellpositionqty," +
		"       sum(a.sellcurpositionqty) sellcurpositionqty" +
		"  from (select ta.userid, t.*" +
		"          from tradeposition t" +
		"         inner join taaccount ta" +
		"            on t.accountid = ta.accountid" +
		"         where ta.relateduserid = %v and ismain=0" +
		"           ) a" +
		" group by a.userid, a.goodsid"
	sqlId = fmt.Sprintf(sqlId, r.USERID)
	return sqlId
}

// GetData 获取用户持仓头寸(子账户)
func (r *ErmcpTradePosition) GetData() ([]ErmcpTradePosition, error) {
	sData := make([]ErmcpTradePosition, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	return sData, err
}

// ErmcpHedgePosition 用户头寸(母账户)
type ErmcpHedgePosition struct {
	RELATEDUSERID     int32  `json:"relateduserid"  xorm:"'RELATEDUSERID'"`         // 关联用户id
	ACCOUNTID         int64  `json:"accountid"  xorm:"'ACCOUNTID'"`                 // 资金账号[外部母账户]
	HEDGEGOODSID      int32  `json:"hedgegoodsid"  xorm:"'HEDGEGOODSID'"`           // 对冲合约ID
	HEDGEACCOUNTCODE  string `json:"hedgeaccountcode"  xorm:"'HEDGEACCOUNTCODE'"`   // 对冲账号
	TRADEDATE         string `json:"tradedate"  xorm:"'TRADEDATE'"`                 // 交易日(yyyyMMdd)
	MARKETID          int32  `json:"marketid"  xorm:"'mARKETID'"`                   // 市场ID
	YDBUYPOSITION     int32  `json:"ydbuyposition"  xorm:"'YDBUYPOSITION'"`         // 期初买头寸
	CURBUYPOSITION    int32  `json:"curbuyposition"  xorm:"'CURBUYPOSITION'"`       // 期末买头寸
	CURYDBUYPOSITION  int32  `json:"curydbuyposition"  xorm:"'CURYDBUYPOSITION'"`   // 期末上日买头寸
	CURTDBUYPOSITION  int32  `json:"curtdbuyposition"  xorm:"'CURTDBUYPOSITION'"`   // 期末今日买头寸
	FREYDBUYPOSITION  int32  `json:"freydbuyposition"  xorm:"'FREYDBUYPOSITION'"`   // 冻结上日买头寸
	FRETDBUYPOSITION  int32  `json:"fretdbuyposition"  xorm:"'FRETDBUYPOSITION'"`   // 冻结今日买头寸
	YDSELLPOSITION    int32  `json:"ydsellposition"  xorm:"'YDSELLPOSITION'"`       // 期初卖头寸
	CURSELLPOSITION   int32  `json:"cursellposition"  xorm:"'CURSELLPOSITION'"`     // 期末卖头寸
	CURYDSELLPOSITION int32  `json:"curydsellposition"  xorm:"'CURYDSELLPOSITION'"` // 期末上日卖头寸
	CURTDSELLPOSITION int32  `json:"curtdsellposition"  xorm:"'CURTDSELLPOSITION'"` // 期末今日卖头寸
	FREYDSELLPOSITION int32  `json:"freydsellposition"  xorm:"'FREYDSELLPOSITION'"` // 冻结上日卖头寸
	FRETDSELLPOSITION int32  `json:"fretdsellposition"  xorm:"'FRETDSELLPOSITION'"` // 冻结今日卖头寸
	GOODSID           int32  `json:"goodsid"  xorm:"'GOODSID'"`                     // 商品id
	GOODSCODE         string `json:"goodscode"  xorm:"'GOODSCODE'"`                 // 商品代码
	GOODSNAME         string `json:"goodsname"  xorm:"'GOODSNAME'"`                 // 商品名称

	TotalYdQty  int32 `json:"totalydqty"`  // 昨日数量(净头寸) = 期初买头寸 - 期初卖头寸
	TotalCurQty int32 `json:"totalcurqty"` // 当前数量(净头寸) = 期末买头寸 - 期末卖头寸
	IncreaseQty int32 `json:"increaseqty"` // 增加数量 = 期末买头寸 - 期初买头寸
	DecreaseQty int32 `json:"decreaseqty"` // 减少数量 = (期末卖头寸 - 期初卖头寸)*-1
}

// Calc 计算母账号头寸相关数据
func (r *ErmcpHedgePosition) Calc() {
	r.TotalYdQty = r.YDBUYPOSITION - r.YDSELLPOSITION
	r.TotalCurQty = r.CURBUYPOSITION - r.CURSELLPOSITION
	r.IncreaseQty = r.CURBUYPOSITION - r.YDBUYPOSITION
	r.DecreaseQty = (r.CURSELLPOSITION - r.YDSELLPOSITION) * -1
}

func (r *ErmcpHedgePosition) buildSql() string {
	sqlId := "select ta.RELATEDUSERID," +
		"       t.ACCOUNTID," +
		"       t.HEDGEGOODSID," +
		"       t.HEDGEACCOUNTCODE," +
		"       t.TRADEDATE," +
		"       t.MARKETID," +
		"       t.YDBUYPOSITION," +
		"       t.CURBUYPOSITION," +
		"       t.CURYDBUYPOSITION," +
		"       t.CURTDBUYPOSITION," +
		"       t.FREYDBUYPOSITION," +
		"       t.FRETDBUYPOSITION," +
		"       t.YDSELLPOSITION," +
		"       t.CURSELLPOSITION," +
		"       t.CURYDSELLPOSITION," +
		"       t.CURTDSELLPOSITION," +
		"       t.FREYDSELLPOSITION," +
		"       t.FRETDSELLPOSITION," +
		"       g.goodsid," +
		"       g.goodscode," +
		"       g.goodsname" +
		"  from hedge_outtradeposition t" +
		" inner join taaccount ta" +
		"    on t.accountid = ta.accountid" +
		"   and ta.ismain = 1" +
		"   and ta.relateduserid = %v" +
		"  left join goods g" +
		"    on t.hedgegoodsid = g.goodsid"
	sqlId = fmt.Sprintf(sqlId, r.RELATEDUSERID)
	return sqlId
}

// GetData 获取对冲头寸(母账号头寸)
func (r *ErmcpHedgePosition) GetData() ([]ErmcpHedgePosition, error) {
	sData := make([]ErmcpHedgePosition, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].Calc()
	}
	return sData, err
}

// ErmcpExposurePostion 实时敞口\期货明细(头寸)
type ErmcpExposurePostion struct {
	AREAUSERID    int32   `json:"areauserid"  xorm:"'AREAUSERID'"`       // 所属机构id
	MIDDLEGOODSID int32   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"` // 套保商品id
	GOODSID       int32   `json:"goodsid"  xorm:"'GOODSID'"`             // 商品id
	GOODSCODE     string  `json:"goodscode"  xorm:"'GOODSCODE'"`         // 商品代码
	GOODSNAME     string  `json:"goodsname"  xorm:"'GOODSNAME'"`         // 商品名称
	YdQty         int64   `json:"ydqty"  xorm:"'YdQty'"`                 // 昨日持仓
	CurQty        int64   `json:"curqty"  xorm:"'CurQty'"`               // 当前持仓
	DiffQty       int64   `json:"diffqty"  xorm:"'DiffQty'"`             // 持仓变动量=当前持仓-昨日持仓
	DiffHedgeQty  float64 `json:"diffhedgeqty"  xorm:"'DiffHedgeQty'"`   // 套保品种变动量=持仓变动量*期货合约单位*期货品种系数
	AGREEUNIT     string  `json:"agreeunit"`                             // 合约单位
	CONVERTRATIO  float64 `json:"convertratio"`                          // 期货品种系数(折算系数)
}

// ParseFromPos 子账户相关计算(不一定用得到，现在说都是查母账号的)
func (r *ErmcpExposurePostion) ParseFromPos(val *ErmcpTradeGoods, data *ErmcpTradePosition) {
	r.AREAUSERID = val.AREAUSERID
	r.MIDDLEGOODSID = val.MIDDLEGOODSID
	r.GOODSID = val.MIDDLEGOODSID
	r.GOODSCODE = val.GOODSCODE
	r.GOODSNAME = val.GOODSNAME
	//相关计算
	r.YdQty = data.BUYPOSITIONQTY - data.SELLPOSITIONQTY
	r.CurQty = data.BUYCURPOSITIONQTY - data.SELLCURPOSITIONQTY
	r.DiffQty = r.CurQty - r.YdQty
	r.DiffHedgeQty = float64(r.DiffQty) * float64(val.AGREEUNIT) * val.CONVERTRATIO
	r.CONVERTRATIO = val.CONVERTRATIO
	if strName := mtpcache.GetEnumDicitemName(val.GOODSUNITID); len(strName) > 0 {
		r.AGREEUNIT = fmt.Sprintf("%v%v/手", val.AGREEUNIT, strName)
	}
}

// ParseFromHedgePos 母账户相关计算
func (r *ErmcpExposurePostion) ParseFromHedgePos(val *ErmcpTradeGoods, data *ErmcpHedgePosition) {
	r.AREAUSERID = val.AREAUSERID
	r.MIDDLEGOODSID = val.MIDDLEGOODSID
	r.GOODSID = val.MIDDLEGOODSID
	r.GOODSCODE = val.GOODSCODE
	r.GOODSNAME = val.GOODSNAME
	//相关计算
	r.YdQty = int64(data.YDBUYPOSITION - data.YDSELLPOSITION)
	r.CurQty = int64(data.CURBUYPOSITION - data.CURSELLPOSITION)
	r.DiffQty = r.CurQty - r.YdQty
	r.DiffHedgeQty = float64(r.DiffQty) * float64(val.AGREEUNIT) * val.CONVERTRATIO
	r.CONVERTRATIO = val.CONVERTRATIO
	if strName := mtpcache.GetEnumDicitemName(val.GOODSUNITID); len(strName) > 0 {
		r.AGREEUNIT = fmt.Sprintf("%v%v/手", val.AGREEUNIT, strName)
	}
}

// GetDataEx 获取敞口明细期货头寸
func (r *ErmcpExposurePostion) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpExposurePostion, 0)
	// 查询交易商品
	mg := ErmcpTradeGoods{AREAUSERID: r.AREAUSERID, MIDDLEGOODSID: r.MIDDLEGOODSID}
	sGoods, err := mg.GetData()
	if err != nil || sGoods == nil || len(sGoods) == 0 {
		return sData, err
	}

	// 查询头寸(母账号)
	mp := ErmcpHedgePosition{RELATEDUSERID: r.AREAUSERID}
	sPostion, err1 := mp.GetData()
	if err1 != nil || sPostion == nil || len(sPostion) == 0 {
		return sData, err1
	}

	// 合并处理
	for i := range sPostion {
		if val, ok := sGoods[sPostion[i].HEDGEGOODSID]; ok {
			d := ErmcpExposurePostion{}
			d.ParseFromHedgePos(val, &sPostion[i])
			sData = append(sData, d)
		}
	}
	return sData, nil
}

// ErmcpHedgePositionDetail 敞口/期货头寸/期货明细
type ErmcpHedgePositionDetail struct {
	HEDGEGOODSID     int32  `json:"hedgegoodsid"  xorm:"'HEDGEGOODSID'"`         // 商品id
	BUYORSELL        int32  `json:"buyorsell"  xorm:"'BUYORSELL'"`               // 买卖方向 0-买 1-卖
	TRADEQTY         int32  `json:"tradeqty"  xorm:"'TRADEQTY'"`                 // 数量(成交数量)
	CHANNELBUILDTYPE int32  `json:"channelbuildtype"  xorm:"'CHANNELBUILDTYPE'"` // 开平方向 1-建仓 2-平仓
	TRADETIME        string `json:"tradetime"  xorm:"'TRADETIME'"`               // 时间(成交时间)
	GOODSCODE        string `json:"goodscode"  xorm:"'GOODSCODE'"`               // 商品代码
	GOODSNAME        string `json:"goodsname"  xorm:"'GOODSNAME'"`               // 商品名称
	AREAUSERID       int32  `json:"-"`                                           // 所属机构id
}

// Calc 计算期货明细相关数据
func (r *ErmcpHedgePositionDetail) Calc() {
	if r.BUYORSELL == 1 && r.TRADEQTY > 0 {
		r.TRADEQTY = r.TRADEQTY * -1
	}
}

func (r *ErmcpHedgePositionDetail) buildSql() string {
	sqlId := "select t.hedgegoodsid," +
		"       t.buyorsell," +
		"       tradeqty," +
		"       t.channelbuildtype," +
		"       to_char(t.tradetime, 'yyyy-mm-dd hh24:mi:ss') tradetime," +
		"       g.goodscode," +
		"       g.goodsname" +
		"  from hedge_outtradedetail t" +
		" inner join taaccount ta" +
		"    on t.accountid = ta.accountid" +
		"   and ta.ismain = 1" +
		"   and ta.relateduserid = %v" +
		"  left join goods g" +
		"    on t.hedgegoodsid = g.goodsid" +
		" where t.hedgegoodsid = %v"
	sqlId = fmt.Sprintf(sqlId, r.AREAUSERID, r.HEDGEGOODSID)
	return sqlId
}

// GetDataEx 获取持仓头寸明细(成交记录)
func (r *ErmcpHedgePositionDetail) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpHedgePositionDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].Calc()
	}
	return sData, err
}