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    float64 // 期初买入期货数量
	OriSellFutureQty   float64 // 期初卖出期货数量
	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 // 应套保敞口比例

	MiddleGoodsName       string  // 套保品种名称
	MiddleGoodsCode       string  // 套保品种代码
	MiddleGoodsHedgeRatio float64 // 应套保比例
	OriTotalSpotQty       float64 // 期初现货数量=(期初销售计划数量-期初销售合同已定价数量)-(期初采购计划数量-期初采购合同已定价数量)
	OriTotalFutuQty       float64 // 期初期货数量=期初买入期货数量-期初卖出期货数量
	DiffSpotQty           float64 // 今日变动量(现货) = 现货数量 - 期初现货数量
	DiffFutuQty           float64 // 今日变动量(期货) = (买入 - 买入期初) - (卖出 - 卖出期初)
}

// 计算相关字段
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.BuyFutureQty - r.OriBuyFutureQty) - (r.SellFutureQty - 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 = 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()
}

// 实时敞口数据: 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}
						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," +
		"         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," +
		"       tmp.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"

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

// 处理数据
func (r *ErmcpExposureDetailModel) Calc() {
	if r.Contracttype == -1{
		// 销售的变动量转换为负数
		r.ChangeQty *= -1
		r.Qty *= -1
	}
}

// 查询敞口现货明细
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
}

/// 现货头寸

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

// 组建查询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 hh: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"

	return fmt.Sprintf(str, r.UserId, r.WrstandardId, 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 = "销售"
		if r.Qty > 0 {
			// 销售的数量转为负数
			r.Qty = r.Qty * -1
		}
	}

	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
}

// 套保品种关联交易商品
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
}

// 获取套保商品关联的交易商品
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
}

// 获取用户头寸(子账户)
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
}

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

// 用户头寸(母账户)
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
}

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
}

// 获取对冲头寸(母账号头寸)
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
}

// 实时敞口\期货明细(头寸)
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"`                          // 期货品种系数(折算系数)
}

// 子账户相关计算(不一定用得到，现在说都是查母账号的)
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)
	}
}

// 母账户相关计算
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)
	}
}

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

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

	// 合并处理
	sData := make([]ErmcpExposurePostion, 0)
	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
}

// 敞口/期货头寸/期货明细
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
}

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" +
		" union all " +
		"select t.hedgegoodsid," +
		"       t.buyorsell," +
		"       tradeqty," +
		"       t.channelbuildtype," +
		"       to_char(t.tradetime, 'yyyy-mm-dd hh24:mi:ss') tradetime," +
		"       g.goodscode," +
		"       g.goodsname" +
		"  from his_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.isvalid = 1" +
		"   and t.hedgegoodsid = %v"
	sqlId = fmt.Sprintf(sqlId, r.AREAUSERID, r.HEDGEGOODSID, r.AREAUSERID, r.HEDGEGOODSID)
	return sqlId
}

// 获取持仓头寸明细(成交记录)
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
}