MTP20_IF/models/ermcp3.go

/**
* @Author: zou.yingbin
* @Create  : 2021/4/14 11:11
* @Modify  : 2021/4/14 11:11
 */

package models

import (
	"encoding/json"
	"fmt"
	"mtp2_if/db"
	"mtp2_if/logger"
	"mtp2_if/mtpcache"
	"mtp2_if/utils"
	"strings"
)

// ErmcpDeliveryGoods 现货品种(交割品种)
type ErmcpDeliveryGoods struct {
	DELIVERYGOODSID   int32  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种ID(SEQ_DELIVERYGOODS)
	DELIVERYGOODSCODE string `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
	GOODSUNITID       int32  `json:"goodsunitid"  xorm:"'GOODSUNITID'"`             // 现货品种单位ID
	DELIVERYGOODSTYPE int32  `json:"deliverygoodstype"  xorm:"'DELIVERYGOODSTYPE'"` // 现货品种类型: 1-整装不拆分 2-散装记录明细 3:整装拆分 4:散装不记录明细
	STANDARDQTY       int32  `json:"standardqty"  xorm:"'STANDARDQTY'"`             // 标准数量(库位数量) [标准品特有]
	STANDARDQTYRANGE  int32  `json:"standardqtyrange"  xorm:"'STANDARDQTYRANGE'"`   // 标准数量偏差范围 [标准品特有]
	AUDITFLAG         int32  `json:"auditflag"  xorm:"'AUDITFLAG'"`                 // 交割是否需要审核 - 0:不需要 1:需要审核   默认为0
	ISVALID           int32  `json:"isvalid"  xorm:"'ISVALID'"`                     // 是否有效 - 0:无效 1:有效
	AGREEUNIT         int32  `json:"agreeunit"  xorm:"'AGREEUNIT'"`                 // 合约单位[散货时默认为1， 整装时默认为标准数量]
	QTYDECIMALPLACE   int32  `json:"qtydecimalplace"  xorm:"'QTYDECIMALPLACE'"`     // 成交量小数位
	CATEGORYID        int32  `json:"categoryid"  xorm:"'CATEGORYID'"`               // 类别ID(SEQ_WRCATEGORY)
	DGSTATUS          int32  `json:"dgstatus"  xorm:"'DGSTATUS'"`                   // 品种状态 - 作废 - 0:未激活 1:正常
	AREAUSERID        int64  `json:"areauserid"  xorm:"'AREAUSERID'"`               // 所属机构
	REMARK            string `json:"remark"  xorm:"'REMARK'"`                       // 备注

	EnumdicName string `json:"enumdicname"` // 现货品种单位名称
	ExcludeCfg  int32  `json:"-"`           // 排除已配置的业务类型商品 1-套保 2-套利
}

func (r *ErmcpDeliveryGoods) calc() {
	r.EnumdicName = mtpcache.GetEnumDicitemName(r.GOODSUNITID)
}

func (r *ErmcpDeliveryGoods) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.DELIVERYGOODSID," +
		"       t.DELIVERYGOODSCODE," +
		"       t.DELIVERYGOODSNAME," +
		"       t.GOODSUNITID," +
		"       t.DELIVERYGOODSTYPE," +
		"       t.STANDARDQTY," +
		"       t.STANDARDQTYRANGE," +
		"       t.AUDITFLAG," +
		"       t.ISVALID," +
		"       t.ISSPLIT," +
		"       t.AGREEUNIT," +
		"       t.QTYDECIMALPLACE," +
		"       t.CATEGORYID," +
		"       t.DGSTATUS," +
		"       t.REMARK," +
		"       t.AREAUSERID" +
		"  FROM DELIVERYGOODS t" +
		" WHERE 1 = 1"
	sqlId.AndEx("t.AREAUSERID", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	if r.ExcludeCfg > 0 {
		sqlId.Join(fmt.Sprintf(" and t.deliverygoodsid not in(select distinct deliverygoodsid from ermcp_bizgroupspotgoods where biztype = %v)", r.ExcludeCfg))
	}
	return sqlId.String()
}

// GetDataEx 获取现货商品(交割商品)
func (r *ErmcpDeliveryGoods) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// GetData
func (r *ErmcpDeliveryGoods) GetData() ([]ErmcpDeliveryGoods, error) {
	sData := make([]ErmcpDeliveryGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// ErmcpDGFactoryItem 品种选择项定义表
type ErmcpDGFactoryItem struct {
	DGFACTORYITEMID     int64  `json:"dgfactoryitemid"  xorm:"'DGFACTORYITEMID'"`         // 选择项ID(SEQ_DGFACTORYITEM)
	DELIVERYGOODSID     int32  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`         // 现货品种ID
	DGFACTORYITEMTYPEID int64  `json:"dgfactoryitemtypeid"  xorm:"'DGFACTORYITEMTYPEID'"` // 要素项类型
	DGFACTORYITEMVALUE  string `json:"dgfactoryitemvalue"  xorm:"'DGFACTORYITEMVALUE'"`   // 要素项值(类型为仓库时填写仓库名称)
	WAREHOUSEID         int64  `json:"warehouseid"  xorm:"'WAREHOUSEID'"`                 // 仓库ID(类型为仓库时填写)
	ISVALID             int32  `json:"isvalid"  xorm:"'ISVALID'"`                         // 是否有效 - 0:无效 1:有效
	ORDERINDEX          int32  `json:"orderindex"  xorm:"'ORDERINDEX'"`                   // 顺序
	AREAUSERID          int64  `json:"areauserid"  xorm:"'AREAUSERID'"`                   // 用户id
}

func (r *ErmcpDGFactoryItem) buildSql() string {
	var sqlId utils.SQLVal = "SELECT DGFACTORYITEMID," +
		"       t.DELIVERYGOODSID," +
		"       to_char(t.DGFACTORYITEMTYPEID) DGFACTORYITEMTYPEID," +
		"       t.DGFACTORYITEMVALUE," +
		"       to_char(t.WAREHOUSEID) WAREHOUSEID," +
		"       t.ISVALID," +
		"       t.ORDERINDEX," +
		"       g.areauserid" +
		"  FROM DGFACTORYITEM t" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		" WHERE t.ISVALID = 1"
	sqlId.AndEx("g.areauserid", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.DGFACTORYITEMID", r.DGFACTORYITEMID, r.DGFACTORYITEMID > 0)
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	sqlId.AndEx("t.DGFACTORYITEMTYPEID", r.DGFACTORYITEMTYPEID, r.DGFACTORYITEMTYPEID > 0)
	sqlId.AndEx("t.ORDERINDEX", r.ORDERINDEX, r.ORDERINDEX > 0)

	return sqlId.String()
}

// GetDataEx 获取品种选择项定义
func (r *ErmcpDGFactoryItem) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// GetData
func (r *ErmcpDGFactoryItem) GetData() (interface{}, error) {
	sData := make([]ErmcpDGFactoryItem, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	return sData, err
}

// Ermcp3Brand 品牌(从品种选择项定义表中查)
type Ermcp3Brand struct {
	BRANDID         int64  `json:"brandid"`         // 品牌id
	BRANDNAME       string `json:"brandname"`       // 品牌名称
	DELIVERYGOODSID int64  `json:"deliverygoodsid"` // 品种ID
	AREAUSERID      int64  `json:"areauserid"`      // 用户id
}

// GetDataEx
func (r *Ermcp3Brand) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// GetData 获取品牌数据
func (r *Ermcp3Brand) GetData() ([]Ermcp3Brand, error) {
	m := ErmcpDGFactoryItem{
		DELIVERYGOODSID:     int32(r.DELIVERYGOODSID),
		DGFACTORYITEMTYPEID: 2,
		ORDERINDEX:          1,
	}
	sData := make([]Ermcp3Brand, 0)
	if d, err := m.GetDataEx(); err == nil {
		if dgLst, ok := d.([]ErmcpDGFactoryItem); ok {
			for _, val := range dgLst {
				sData = append(sData, Ermcp3Brand{BRANDID: val.DGFACTORYITEMID, AREAUSERID: val.AREAUSERID,
					BRANDNAME: val.DGFACTORYITEMVALUE, DELIVERYGOODSID: int64(val.DELIVERYGOODSID)})
			}
		}
	}
	return sData, nil
}

// ErmcpRelatedGoods 现货品种的关联交易商品
type ErmcpRelatedGoods struct {
	GoodsId         int    `json:"goodsid"  xorm:"'GoodsId'"`                 // 商品id
	GoodsCode       string `json:"goodscode"  xorm:"'GoodsCode'"`             // 商品代码
	GoodsName       string `json:"goodsname"  xorm:"'GoodsName'"`             // 商品名称
	DELIVERYGOODSID int32  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"` // 品种ID

	AREAUSERID int64 `json:"-"` // 所属机构
}

func (r *ErmcpRelatedGoods) buildSql() string {
	var sqlId utils.SQLVal = "select distinct t.deliverygoodsid, g.goodsid, g.goodscode, g.goodsname" +
		"  from deliverygoods t" +
		" inner join ERMS2_WRSConvertDetail w" +
		"    on t.deliverygoodsid = w.deliverygoodsid" +
		" inner join erms_middlegoods m" +
		"    on w.middlegoodsid = m.middlegoodsid" +
		" inner join ERMCP_GGConvertConfig gc" +
		"    on m.goodsgroupid = gc.destgoodsgroupid" +
		" inner join goods g" +
		"    on gc.srcgoodsgroupid = g.goodsgroupid" +
		" where 1 = 1"
	sqlId.AndEx("t.AREAUSERID", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	return sqlId.String()
}

// GetDataEx 获取现货关联交易商品
func (r *ErmcpRelatedGoods) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// GetData 获取现货关联交易商品
func (r *ErmcpRelatedGoods) GetData() ([]ErmcpRelatedGoods, error) {
	sData := make([]ErmcpRelatedGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	return sData, err
}

// ErmcpDeliveryGoodsDetail2 现货商品详情(含转换系数)
type ErmcpDeliveryGoodsDetail2 struct {
	ErmcpDeliveryGoodsDetail
	CONVERTRATIO float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"` // 套保系数(折算系数)
}

// ErmcpDeliveryGoodsDetail 现货商品详情
type ErmcpDeliveryGoodsDetail struct {
	Data   ErmcpDeliveryGoods `json:"data"`   // 现货商品基本信息(交割品种)
	GmList []Ermcp3Wrstandard `json:"gmlist"` // 品类列表(仓单标准)
	GbList []Ermcp3Brand      `json:"gblist"` // 品牌列表
}

func (r *ErmcpDeliveryGoodsDetail) addGmList(lst []Ermcp3Wrstandard) {
	for i := range lst {
		if lst[i].DELIVERYGOODSID == r.Data.DELIVERYGOODSID && lst[i].ISVALID == 1 {
			r.GmList = append(r.GmList, lst[i])
		}
	}
}

func (r *ErmcpDeliveryGoodsDetail) addGbList(lst []Ermcp3Brand) {
	for i := range lst {
		if lst[i].DELIVERYGOODSID == int64(r.Data.DELIVERYGOODSID) {
			r.GbList = append(r.GbList, lst[i])
		}
	}
}

// GetData
func (r *ErmcpDeliveryGoodsDetail) GetData() ([]ErmcpDeliveryGoodsDetail, error) {
	sData := make([]ErmcpDeliveryGoodsDetail, 0)
	m := ErmcpDeliveryGoods{AREAUSERID: r.Data.AREAUSERID, DELIVERYGOODSID: r.Data.DELIVERYGOODSID}
	data, err := m.GetData()
	if err != nil {
		return sData, err
	}
	if data == nil || len(data) == 0 {
		return sData, nil
	}
	// 基本信息
	for i := range data {
		val := ErmcpDeliveryGoodsDetail{Data: data[i],
			GmList: make([]Ermcp3Wrstandard, 0), GbList: make([]Ermcp3Brand, 0),
		}
		sData = append(sData, val)
	}
	//品类/品牌/套保信息
	gm := Ermcp3Wrstandard{AREAUSERID: r.Data.AREAUSERID, DELIVERYGOODSID: r.Data.DELIVERYGOODSID, ISVALID: 1}
	gb := Ermcp3Brand{DELIVERYGOODSID: int64(r.Data.DELIVERYGOODSID)}
	gmLst, _ := gm.GetDataEx()
	gbLst, _ := gb.GetData()

	for i := range sData {
		sData[i].addGmList(gmLst.([]Ermcp3Wrstandard))
		sData[i].addGbList(gbLst)
	}

	return sData, nil
}

// GetDataEx
func (r *ErmcpDeliveryGoodsDetail) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// ErmcpDeliveryGoodsDetailEx 现货商品详情(含套保列表)
type ErmcpDeliveryGoodsDetailEx struct {
	ErmcpDeliveryGoodsDetail
	MgList []Ermcp3MiddleGoodsDetail2 `json:"mgList"` // 套保列表
}

// GetData 获取现货商品详情(含套保信息列表)
func (r *ErmcpDeliveryGoodsDetailEx) GetDataEx() (interface{}, error) {
	sData := make([]ErmcpDeliveryGoodsDetailEx, 0)
	mDg := r.ErmcpDeliveryGoodsDetail
	// 查现货商品列表
	if dgList, err := mDg.GetData(); err == nil {
		for i := range dgList {
			sData = append(sData, ErmcpDeliveryGoodsDetailEx{ErmcpDeliveryGoodsDetail: dgList[i],
				MgList: make([]Ermcp3MiddleGoodsDetail2, 0)})
		}
	}
	if len(sData) == 0 {
		return sData, nil
	}
	// 查转换关系
	mWc := WRSConverTDetail{}
	if wc, err := mWc.GetData(); err == nil {
		// 查套保商品列表
		mMg := Ermcp3MiddleGoodsDetail{}
		mMg.Mg.AREAUSERID = r.Data.AREAUSERID
		if mgLst, err := mMg.GetData(); err == nil {
			for i := range sData {
				sData[i].addMgList(mgLst, wc)
			}
		}
	}

	return sData, nil
}

// addMgList 添加关联的套保信息列表
func (r *ErmcpDeliveryGoodsDetailEx) addMgList(lst []Ermcp3MiddleGoodsDetail, wc []WRSConverTDetail) {
	for i := range wc {
		if r.Data.DELIVERYGOODSID == wc[i].DELIVERYGOODSID {
			for k := range lst {
				if int64(lst[k].Mg.MIDDLEGOODSID) == wc[i].MIDDLEGOODSID {
					val := Ermcp3MiddleGoodsDetail2{Ermcp3MiddleGoodsDetail: lst[k], CONVERTRATIO: wc[i].CONVERTRATIO}
					r.MgList = append(r.MgList, val)
				}
			}
		}
	}
}

// Ermcp3MiddleGoodsDetail2 套保品种详情(含转换系数)
type Ermcp3MiddleGoodsDetail2 struct {
	Ermcp3MiddleGoodsDetail
	CONVERTRATIO float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"` // 套保系数(折算系数)
}

// Ermcp3MiddleGoodsDetail 套保品种详情
type Ermcp3MiddleGoodsDetail struct {
	Mg     ErmcpMiddleGoodsModel `json:"mg"`     // 套保商品信息
	GPList []Ermcp3GoodsGroupEx  `json:"gplist"` // 关联商品组列表
}

// GetData 获取套保品种详情(不含现货列表)
func (r *Ermcp3MiddleGoodsDetail) GetData() ([]Ermcp3MiddleGoodsDetail, error) {
	sData := make([]Ermcp3MiddleGoodsDetail, 0)
	// 查基本信息
	mMg := ErmcpMiddleGoodsModel{AREAUSERID: r.Mg.AREAUSERID, MIDDLEGOODSID: r.Mg.MIDDLEGOODSID, ISVALID: 1}
	if mgLst, err := mMg.GetData2(); err == nil {
		for i := range mgLst {
			sData = append(sData, Ermcp3MiddleGoodsDetail{Mg: mgLst[i], GPList: make([]Ermcp3GoodsGroupEx, 0)})
		}
	}
	if len(sData) == 0 {
		return sData, nil
	}
	bExist := false
	// 查关联期货商品组
	mGp := Ermcp3MiddleGoodsRelateGroup{AREAUSERID: r.Mg.AREAUSERID}
	if gpLst, err := mGp.GetData(); err == nil {
		for i := range sData {
			sData[i].addGoodsGroup(gpLst)
		}
		bExist = len(gpLst) > 0
	}

	if bExist {
		mGoods := Ermcp3GoodsEx{AREAUSERID: r.Mg.AREAUSERID}
		if gLst, err := mGoods.GetData(); err == nil {
			for i := range sData {
				sData[i].addGoods(gLst)
			}
		}
	}

	return sData, nil
}

func (r *Ermcp3MiddleGoodsDetail) addGoodsGroup(gpLst []Ermcp3MiddleGoodsRelateGroup) {
	for i := range gpLst {
		if gpLst[i].MIDDLEGOODSID == r.Mg.MIDDLEGOODSID {
			bExist := false
			for _, v := range r.GPList {
				if v.GOODSGROUPID == gpLst[i].GOODSGROUPID {
					bExist = true
					break
				}
			}
			if !bExist {
				val := Ermcp3GoodsGroupEx{
					GOODSGROUPID:   gpLst[i].GOODSGROUPID,
					MIDDLEGOODSID:  gpLst[i].MIDDLEGOODSID,
					CONVERTRATIO:   gpLst[i].CONVERTRATIO,
					EXEXCHANGECODE: gpLst[i].EXEXCHANGECODE,
					GOODSGROUPNAME: gpLst[i].GOODSGROUPNAME,
					ENUMDICNAME:    gpLst[i].ENUMDICNAME,
					GOODSUNITID:    gpLst[i].GOODSUNITID,
					GList:          make([]Ermcp3Goods, 0),
				}
				r.GPList = append(r.GPList, val)
			}
		}
	}
}

func (r *Ermcp3MiddleGoodsDetail) addGoods(gpLst []Ermcp3GoodsEx) {
	for i := range gpLst {
		if gpLst[i].MIDDLEGOODSID == r.Mg.MIDDLEGOODSID {
			for k := range r.GPList {
				if r.GPList[k].GOODSGROUPID == gpLst[i].GOODSGROUPID {
					r.GPList[k].GList = append(r.GPList[k].GList, Ermcp3Goods{
						GOODSID:        gpLst[i].GOODSID,
						GOODSNAME:      gpLst[i].GOODSNAME,
						GOODSCODE:      gpLst[i].GOODSCODE,
						AGREEUNIT:      gpLst[i].AGREEUNIT,
						EXEXCHANGECODE: gpLst[i].EXEXCHANGECODE,
						ENUMDICNAME:    gpLst[i].ENUMDICNAME,
					})
				}
			}
		}
	}
}

// Ermcp3MiddleGoodsDetailEx 套保品种详情(含现货列表)
type Ermcp3MiddleGoodsDetailEx struct {
	Ermcp3MiddleGoodsDetail
	DgList []ErmcpDeliveryGoodsDetail2 `json:"dglist"` // 关联的现货品种列表
}

func (r *Ermcp3MiddleGoodsDetailEx) addDgList(dglst []ErmcpDeliveryGoodsDetail, wc []WRSConverTDetail) {
	for i := range wc {
		if wc[i].MIDDLEGOODSID == int64(r.Mg.MIDDLEGOODSID) {
			for _, detail := range dglst {
				if wc[i].DELIVERYGOODSID == detail.Data.DELIVERYGOODSID && detail.Data.ISVALID == 1 {
					val := ErmcpDeliveryGoodsDetail2{ErmcpDeliveryGoodsDetail: detail, CONVERTRATIO: wc[i].CONVERTRATIO}
					r.DgList = append(r.DgList, val)
				}
			}
		}
	}
}

// GetDataEx
func (r *Ermcp3MiddleGoodsDetailEx) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3MiddleGoodsDetailEx, 0)
	// 查套保品种基本信息
	m := r.Ermcp3MiddleGoodsDetail
	if mgLst, err := m.GetData(); err == nil {
		for i := range mgLst {
			sData = append(sData, Ermcp3MiddleGoodsDetailEx{Ermcp3MiddleGoodsDetail: mgLst[i],
				DgList: make([]ErmcpDeliveryGoodsDetail2, 0)})
		}
	}
	// 查转换关系
	mWc := WRSConverTDetail{}
	if wc, err := mWc.GetData(); err == nil {
		// 查现货商品列表
		mDg := ErmcpDeliveryGoodsDetail{}
		mDg.Data.AREAUSERID = r.Mg.AREAUSERID
		if dgLst, err := mDg.GetData(); err == nil {
			for i := range sData {
				sData[i].addDgList(dgLst, wc)
			}
		}
	}
	return sData, nil
}

// Ermcp3MiddleGoodsRelateGroup 套保商品关联的期货商品组
type Ermcp3MiddleGoodsRelateGroup struct {
	MIDDLEGOODSID  uint32  `json:"middlegoodsid"  xorm:"'middlegoodsid'"`   // 套保商品id
	GOODSGROUPID   int32   `json:"goodsgroupid"  xorm:"'goodsgroupid'"`     // 商品组id
	CONVERTRATIO   float64 `json:"convertratio"  xorm:"'convertratio'"`     // 折算系数(品种系数)
	EXEXCHANGECODE string  `json:"exexchangecode"  xorm:"'exexchangecode'"` // 交易所代码
	GOODSGROUPNAME string  `json:"goodsgroupname"  xorm:"'goodsgroupname'"` // 商品组名称
	GOODSUNITID    int32   `json:"goodsunitid"  xorm:"'GOODUNITID'"`        // 单位id
	ENUMDICNAME    string  `json:"enumdicname"`                             // 单位名称
	AREAUSERID     int64   `json:"-"`                                       // 所属机构id
}

func (r *Ermcp3MiddleGoodsRelateGroup) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODSUNITID)
}

func (r *Ermcp3MiddleGoodsRelateGroup) buildSql() string {
	var sqlId utils.SQLVal = "select mg.areauserid," +
		"       mg.middlegoodsid," +
		"       gc.convertratio," +
		"       ex.exexchangecode," +
		"       gp.goodsgroupname," +
		"       gp.goodunitid," +
		"       gp.goodsgroupid" +
		"  from erms_middlegoods mg" +
		" inner join ERMCP_GGConvertConfig gc" +
		"    on mg.goodsgroupid = gc.destgoodsgroupid" +
		" inner join goodsgroup gp" +
		"    on gc.srcgoodsgroupid = gp.goodsgroupid" +
		"  left join externalexchange ex" +
		"    on gp.exexchangeid = ex.autoid" +
		" where mg.isvalid = 1" +
		"   and gp.goodsgroupstatus = 1"
	sqlId.AndEx("mg.AREAUSERID", r.AREAUSERID, r.AREAUSERID > 0)
	return sqlId.String()
}

// GetData 套保品关联的期货商品组信息
func (r *Ermcp3MiddleGoodsRelateGroup) GetData() ([]Ermcp3MiddleGoodsRelateGroup, error) {
	sData := make([]Ermcp3MiddleGoodsRelateGroup, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// GetDataEx 套保品关联的期货商品组信息
func (r *Ermcp3MiddleGoodsRelateGroup) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// Ermcp3GoodsGroupEx 商品组(含商品列表)
type Ermcp3GoodsGroupEx struct {
	MIDDLEGOODSID  uint32        `json:"middlegoodsid"  xorm:"'middlegoodsid'"`   // 套保商品id
	GOODSGROUPID   int32         `json:"goodsgroupid"  xorm:"'goodsgroupid'"`     // 商品组id
	CONVERTRATIO   float64       `json:"convertratio"  xorm:"'convertratio'"`     // 折算系数(品种系数)
	EXEXCHANGECODE string        `json:"exexchangecode"  xorm:"'exexchangecode'"` // 交易所代码
	GOODSGROUPNAME string        `json:"goodsgroupname"  xorm:"'goodsgroupname'"` // 商品组名称
	GOODSUNITID    int32         `json:"goodsunitid"  xorm:"'GOODSUNITID'"`       // 单位id
	ENUMDICNAME    string        `json:"enumdicname"`                             // 单位名称
	GList          []Ermcp3Goods `json:"glist"`                                   // 合约列表
}

// Ermcp3Goods 商品信息
type Ermcp3Goods struct {
	GOODSID        int32   `json:"goodsid"  xorm:"'goodsid'"`               // 期货商品id
	GOODSCODE      string  `json:"goodscode"  xorm:"'goodscode'"`           // 期货商品代码
	GOODSNAME      string  `json:"goodsname"  xorm:"'goodsname'"`           // 期货商品名称
	AGREEUNIT      float32 `json:"agreeunit"  xorm:"'agreeunit'"`           // 合约单位(合约乘数)
	EXEXCHANGECODE string  `json:"exexchangecode"  xorm:"'exexchangecode'"` // 交易所代码
	ENUMDICNAME    string  `json:"enumdicname"`                             // 单位名称
}

// Ermcp3GoodsEx 套保品关联的期货商品(少量字段)
type Ermcp3GoodsEx struct {
	MIDDLEGOODSID  uint32  `json:"middlegoodsid"  xorm:"'middlegoodsid'"`   // 套保商品id
	GOODSID        int32   `json:"goodsid"  xorm:"'goodsid'"`               // 期货商品id
	GOODSCODE      string  `json:"goodscode"  xorm:"'goodscode'"`           // 期货商品代码
	GOODSNAME      string  `json:"goodsname"  xorm:"'goodsname'"`           // 期货商品名称
	GOODUNITID     int32   `json:"goodunitid"  xorm:"'goodunitid'"`         // 期货商品单位id
	CONVERTRATIO   float64 `json:"convertratio"  xorm:"'convertratio'"`     // 折算系数(品种系数)
	GOODSGROUPID   int32   `json:"goodsgroupid"  xorm:"'goodsgroupid'"`     // 商品组id
	EXEXCHANGECODE string  `json:"exexchangecode"  xorm:"'exexchangecode'"` // 交易所代码
	GOODSGROUPNAME string  `json:"goodsgroupname"  xorm:"'goodsgroupname'"` // 商品组名称
	GPUNITID       int32   `json:"gpunitid"  xorm:"'gpunitid'"`             // 商品组单位id
	AGREEUNIT      float32 `json:"agreeunit"  xorm:"'agreeunit'"`           // 合约单位(合约乘数)

	ENUMDICNAME   string `json:"enumdicname"` // 单位名称
	GPENUMDICNAME string `json:"enumdicname"` // 商品组单位名称
	AREAUSERID    int64  `json:"-"`           // 所属机构id
}

func (r *Ermcp3GoodsEx) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID)
	r.GPENUMDICNAME = mtpcache.GetEnumDicitemName(r.GPUNITID)
}

func (r *Ermcp3GoodsEx) buildSql() string {
	var sqlId utils.SQLVal = "select mg.areauserid," +
		"       mg.middlegoodsid," +
		"       g.goodsid," +
		"       g.goodscode," +
		"       g.goodsname," +
		"       g.goodunitid," +
		"       g.goodsgroupid," +
		"       g.agreeunit," +
		"       gc.convertratio," +
		"       ex.exexchangecode," +
		"       gp.goodsgroupname," +
		"       gp.goodunitid gpunitid" +
		"  from erms_middlegoods mg" +
		" inner join ERMCP_GGConvertConfig gc" +
		"    on mg.goodsgroupid = gc.destgoodsgroupid" +
		" inner join goodsgroup gp" +
		"    on gc.srcgoodsgroupid = gp.goodsgroupid" +
		" inner join goods g" +
		"    on gp.goodsgroupid = g.goodsgroupid" +
		"  left join externalexchange ex" +
		"    on gp.exexchangeid = ex.autoid" +
		" where g.goodsstatus not in(4,5,6) and mg.isvalid=1 and gp.goodsgroupstatus=1"
	sqlId.AndEx("mg.areauserid", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.Join(" order by mg.middlegoodsid")
	return sqlId.String()
}

// GetData
func (r *Ermcp3GoodsEx) GetData() ([]Ermcp3GoodsEx, error) {
	sData := make([]Ermcp3GoodsEx, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// GetDataEx
func (r *Ermcp3GoodsEx) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// Ermcp3Contract 现货合同
type Ermcp3Contract struct {
	SPOTCONTRACTID    string  `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'"`       // 现货合同ID(602+Unix秒时间戳(10位)+xxxxxx)
	CONTRACTNO        string  `json:"contractno"  xorm:"'CONTRACTNO'"`               // 现货合同编号
	CONTRACTTYPE      int32   `json:"contracttype"  xorm:"'CONTRACTTYPE'"`           // 现货合同类型 - 1:采购 -1:销售
	USERID            int64   `json:"userid"  xorm:"'USERID'"`                       // 所属机构ID
	BUYUSERID         int64   `json:"buyuserid"  xorm:"'BUYUSERID'"`                 // 采购方ID
	BUYUSERName       string  `json:"buyusername"  xorm:"'BUYUSERName'"`             // 采购方名称
	SELLUSERID        int64   `json:"selluserid"  xorm:"'SELLUSERID'"`               // 销售方ID
	SELLUSERNAME      string  `json:"sellusername"  xorm:"'SELLUSERNAME'"`           // 销售方名称
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种ID
	WRSTANDARDID      int32   `json:"wrstandardid"  xorm:"'wrstandardid'"`           // 品类ID
	WRSTANDARDNAME    string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`       // 品类名称
	WRSTANDARDCODE    string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`       // 品类代码
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
	PRODUCTTYPE       int32   `json:"producttype"  xorm:"'PRODUCTTYPE'"`             // 产品类型 - 1:标准仓单 2:等标 3:非标
	CONVERTFACTOR     float64 `json:"convertfactor"  xorm:"'CONVERTFACTOR'"`         // 标仓系数(品类)
	SPOTGOODSDESC     string  `json:"spotgoodsdesc"  xorm:"'SPOTGOODSDESC'"`         // 商品型号
	PRICETYPE         int32   `json:"pricetype"  xorm:"'PRICETYPE'"`                 // 定价类型 - 1:一口价 2:点价 3:暂定价
	QTY               float64 `json:"qty"  xorm:"'QTY'"`                             // 数量
	PRICE             float64 `json:"price"  xorm:"'PRICE'"`                         // 价格\暂定价 [1:一口价、3:暂定价]
	AMOUNT            float64 `json:"amount"  xorm:"'AMOUNT'"`                       // 金额 [1:一口价、3:暂定价]
	DELIVERYSTARTDATE string  `json:"deliverystartdate"  xorm:"'DELIVERYSTARTDATE'"` // 交收期(开始)
	DELIVERYENDDATE   string  `json:"deliveryenddate"  xorm:"'DELIVERYENDDATE'"`     // 交收期(结束)
	GOODSID           int32   `json:"goodsid"  xorm:"'GOODSID'"`                     // 点价合约ID - 0:为现货，其它为期货商品合约ID [2:点价 3:暂定价]
	GOODSCODE         string  `json:"goodscode"  xorm:"'GOODSCODE'"`                 // 点价合约代码
	GOODSNAME         string  `json:"goodsname"  xorm:"'GOODSNAME'"`                 // 点价商品名称
	PRICEMOVE         float64 `json:"pricemove"  xorm:"'PRICEMOVE'"`                 // 升贴水 [2:点价 3:暂定价]
	STARTDATE         string  `json:"startdate"  xorm:"'STARTDATE'"`                 // 点价开始日期 [2:点价 3:暂定价]
	ENDDATE           string  `json:"enddate"  xorm:"'ENDDATE'"`                     // 点价结束日期 [2:点价 3:暂定价]
	MARGIN            float64 `json:"margin"  xorm:"'MARGIN'"`                       // 当前保证金
	CONTRACTMARGIN    float64 `json:"contractmargin"  xorm:"'CONTRACTMARGIN'"`       // 合同保证金
	EnumdicName       string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`             // 单位名称
	CONTRACCTSTATUS   int32   `json:"contracctstatus"  xorm:"'CONTRACTSTATUS'"`      // 合同状态 - 0:未提交 1:待审核 2:执行中 3:正常完结 4:审核拒绝 5:异常完结 6:已撤回
	Remark            string  `json:"remark"  xorm:"'Remark'"`                       // 备注
	AUDITREMARK       string  `json:"auditremark"  xorm:"'AUDITREMARK'"`             // 审核意见
	AUDITTIME         string  `json:"audittime"  xorm:"'AUDITTIME'"`                 // 审核时间
	CREATETIME        string  `json:"createtime"  xorm:"'CREATETIME'"`               // 创建时间
	UPDATETIME        string  `json:"updatetime"  xorm:"'UPDATETIME'"`               // 更新时间
	SPOTGOODSBRANDID  int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`   // 现货品牌ID(DGFactoryItem表的ID)
	BRANDNAME         string  `json:"brandname"  xorm:"'brandname'"`                 // 品牌名称
	ATTACHMENT        string  `json:"attachment"  xorm:"'ATTACHMENT'"`               // 附件
	UNITID            int32   `json:"unitid"  xorm:"'UNITID'"`                       // 单位id(取品类上的单位id)
	TRADEUSERID       int64   `json:"tradeuserid"  xorm:"'TRADEUSERID'"`             // 交易员id
	SALEUSERID        int64   `json:"saleuserid"  xorm:"'SALEUSERID'"`               // 业务员id
	MERUSERID         int64   `json:"meruserid"  xorm:"'MERUSERID'"`                 // 跟单员id
	ACCOUNTID         string  `json:"accountid"  xorm:"'ACCOUNTID'"`                 // 期货账户id
	BIZTYPE           int32   `json:"biztype"  xorm:"'BIZTYPE'"`                     // 业务类型 1-套保 2-套利
	CURRENCYID        int32   `json:"currencyid"  xorm:"'CURRENCYID'"`               // 币种id
	SUBJECTID         string  `json:"subjectid"  xorm:"'SUBJECTID'"`                 // 交易主体ID
	SUBJECTNAME       string  `json:"subjectname"  xorm:"'SUBJECTNAME'"`             // 交易主体名称
	VATRATE           float64 `json:"vatrate"  xorm:"'VATRATE'"`                     // 增值税率

	SELLNICKNAME       string `json:"sellnickname"`       // 采购方昵称
	BUYNICKNAME        string `json:"buynickname"`        // 销售方昵称
	TRADEUSERLOGINCODE string `json:"tradeuserlogincode"` // 交易员登录代码
	SALEUSERLOGINCODE  string `json:"saleuserlogincode"`  // 业务员登录代码
	MERUSERLOGINCODE   string `json:"meruserlogincode"`   // 跟单员登录代码
	CURRENCYNAME       string `json:"currencyname"`       // 币种名称
	TRADEUSERNAME      string `json:"tradeusername"`      // 交易员名称
	SALEUSERNAME       string `json:"saleusername"`       // 业务员名称
	MERUSERNAME        string `json:"merusername"`        // 跟单员名称

	// 筛选条件
	QryType   int32 `json:"-"` // 查询类型 1-未提交 2-待审核 3-履约中 4-已完成
	UserType  int32 `json:"-"` // 用户类型  2-管理员 7-企业成员
	OwnUserId int64 `json:"-"` // 登录用户id
}

func (r *Ermcp3Contract) getQryTypeStatus() string {
	var status string
	switch r.QryType {
	case 1: // 未提交
		status = "0,4,6"
	case 2: // 待审核
		status = "1"
	case 3: // 履约中
		status = "2"
	case 4: // 已完成
		status = "3,5"
	}
	return status
}

func (r *Ermcp3Contract) calc() {
	r.BUYUSERName, r.BUYNICKNAME = mtpcache.GetUserNameAndNickName(r.BUYUSERID)
	r.SELLUSERNAME, r.SELLNICKNAME = mtpcache.GetUserNameAndNickName(r.SELLUSERID)
	r.EnumdicName = mtpcache.GetEnumDicitemName(r.UNITID)
	r.TRADEUSERLOGINCODE = mtpcache.GetLoginCodeByUserId(r.TRADEUSERID)
	r.SALEUSERLOGINCODE = mtpcache.GetLoginCodeByUserId(r.SALEUSERID)
	r.MERUSERLOGINCODE = mtpcache.GetLoginCodeByUserId(r.MERUSERID)
	r.TRADEUSERNAME = mtpcache.GetUserNameByUserId(r.TRADEUSERID)
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
	r.SALEUSERNAME = mtpcache.GetUserNameByUserId(r.SALEUSERID)
	r.MERUSERNAME = mtpcache.GetUserNameByUserId(r.MERUSERID)
}

func (r *Ermcp3Contract) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT to_char(t.SPOTCONTRACTID) SPOTCONTRACTID,
       t.CONTRACTNO,
       t.CONTRACTTYPE,
       t.USERID,
       t.BUYUSERID,
       t.SELLUSERID,
       t.DELIVERYGOODSID,
       t.wrstandardid,
       t.PRODUCTTYPE,
       w.CONVERTFACTOR,
       t.SPOTGOODSDESC,
       t.PRICETYPE,
       t.QTY,
       t.PRICE,
       t.AMOUNT,
       to_char(t.DELIVERYSTARTDATE, 'yyyy-mm-dd hh24:mi:ss') DELIVERYSTARTDATE,
       to_char(t.DELIVERYENDDATE, 'yyyy-mm-dd hh24:mi:ss') DELIVERYENDDATE,
       t.GOODSID,
       t.PRICEMOVE,
       to_char(t.STARTDATE, 'yyyy-mm-dd hh24:mi:ss') STARTDATE,
       to_char(t.ENDDATE, 'yyyy-mm-dd hh24:mi:ss') ENDDATE,
       t.MARGIN,
       t.contractmargin,
       t.remark,
       t.auditremark,
       to_char(t.contractattachment) attachment,
       t.contractstatus,
       t.SPOTGOODSBRANDID,
       to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') audittime,
       to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime,
       to_char(t.updatetime, 'yyyy-mm-dd hh24:mi:ss') updatetime,
       g.deliverygoodscode,
       g.deliverygoodsname,
       g2.goodscode,
       g2.goodsname,
       gb.dgfactoryitemvalue brandname,
       w.wrstandardname,
       w.wrstandardcode,
       w.vatrate,
       w.unitid,
       t.tradeuserid,
       t.saleuserid,
       t.meruserid,
       t.currencyid,
       to_char(t.accountid) accountid,
       t.biztype,
       to_char(t.subjectid) subjectid,
       b.subjectname
  FROM ERMCP_SPOTCONTRACT t
  left join ermcp_pa_areasubject b on t.subjectid=b.subjectid
  left join deliverygoods g
    on t.deliverygoodsid = g.deliverygoodsid
  left join goods g2
    on t.goodsid = g2.goodsid
  left join DGFACTORYITEM gb
    on t.spotgoodsbrandid = gb.dgfactoryitemid
  left join wrstandard w
    on t.wrstandardid = w.wrstandardid
  left join loginaccount l
    on t.applyid = l.loginid
 where 1 = 1
`
	if len(r.SPOTCONTRACTID) > 0 {
		// 如果指定了合同id, 合同id是key , 则不需要再判断其它条件
		sqlId.AndEx("t.SPOTCONTRACTID", r.SPOTCONTRACTID, len(r.SPOTCONTRACTID) > 0)
	} else {
		sqlId.AndEx("t.CONTRACTTYPE", r.CONTRACTTYPE, r.CONTRACTTYPE != 0)
		// 用户条件
		sqlId.Join(fmt.Sprintf(" and %v in(t.userid, t.saleuserid, t.tradeuserid, t.meruserid, l.userid)", r.OwnUserId))
		status := r.getQryTypeStatus()
		if len(status) > 0 {
			sqlId.Join(fmt.Sprintf(" and t.contractstatus in (%v)", status))
		}
	}

	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3Contract) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3Contract, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3SellBuyContract 采购/销售合同
type Ermcp3SellBuyContract struct {
	UserID             int64   `json:"userid"  xorm:"'UserID'"`                         // 机构ID
	SpotContractId     string  `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'"`         // 合同ID
	AccountName        string  `json:"accountname"  xorm:"'ACCOUNTNAME'"`               // 账户名称
	DeliveryGoodsId    int     `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`       // 现货商品ID
	DeliveryGoodsCode  string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`   // 现货品种代码
	DeliveryGoodsName  string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`   // 现货品种名称
	SpotGoodsdesc      string  `json:"spotgoodsdesc"  xorm:"'SPOTGOODSDESC'"`           // 商品型号(商品规格)
	GoodsId            int     `json:"goodsid"  xorm:"'GOODSID'"`                       // 点价商品ID
	GOODSNAME          string  `json:"goodsname"  xorm:"'GOODSNAME'"`                   // 点价商品名称
	Goodscode          string  `json:"goodscode"  xorm:"'GOODSCODE'"`                   // 点价商品代码
	Pricemove          float64 `json:"pricemove"  xorm:"'PRICEMOVE'"`                   // 升贴水
	Qty                float64 `json:"qty"  xorm:"'QTY'"`                               // 合同量
	UnpricedQty        float64 `json:"unpricedqty"  xorm:"'UNPRICEDQTY'"`               // 未定价量
	PricedQty          float64 `json:"pricedqty"  xorm:"'PRICEDQTY'"`                   // 已定价量
	UnsureQty          float64 `json:"unsureqty"  xorm:"'UNSUREQTY'"`                   // 未确定量
	PayAmount          float64 `json:"payamount"  xorm:"'PAYAMOUNT'"`                   // 已收付额(收款或付款)
	UnpayAmount        float64 `json:"unpayamount"`                                     // 应收付款额(应支付或应收款)
	PrePayAmount       float64 `json:"prepayamount"`                                    // 预收付额
	InvoiceAmount      float64 `json:"invoiceamount"  xorm:"'INVOICEAMOUNT'"`           // 已开票额
	DaikaiAmount       float64 `json:"daikaiamount"  xorm:"'DAIKAIAMOUNT'"`             // 应收(开)票额
	PreInvoiceAmount   float64 `json:"preinvoiceamount"`                                // 预收(开)票额
	StartDate          string  `json:"startdate"  xorm:"'STARTDATE'"`                   // 点价开始日
	EndDate            string  `json:"enddate"  xorm:"'ENDDATE'"`                       // 点价结束日
	DeliveryStartDate  string  `json:"deliverystartdate"  xorm:"'DELIVERYSTARTDATE'"`   // 交割开始日
	DeliveryendDate    string  `json:"deliveryenddate"  xorm:"'DELIVERYENDDATE'"`       // 交割结束日
	Convertfactor      float64 `json:"convertfactor"  xorm:"'CONVERTFACTOR'"`           // 标仓系数(品类)
	EnumdicName        string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`               // 单位名称
	Contracctstatus    uint    `json:"contracctstatus"  xorm:"'CONTRACTSTATUS'"`        // 合同状态- 0:未提交 1:待审核 2:执行中 3:正常完结 4:审核拒绝 5:异常完结 6:已撤回
	PriceType          int     `json:"pricetype"  xorm:"'PRICETYPE'"`                   // 定价类型 - 1:一口价 2:点价 3:暂定价
	ProductType        int     `json:"producttype"  xorm:"'PRODUCTTYPE'"`               // 产品类型 - 1:标准仓单 2:等标 3:非标
	Contracttype       int32   `json:"contracttype"  xorm:"'CONTRACTTYPE'"`             // 合同类型 1-采购, -1-销售
	Pricedamount       float64 `json:"pricedamount"  xorm:"'PRICEDAMOUNT'"`             // 已定价额
	PricedAvg          float64 `json:"pricedavg"  xorm:"'PRICEDAVG'"`                   // 已点均价
	Margin             float64 `json:"margin"  xorm:"'MARGIN'"`                         // 保证金
	CONTRACTMARGIN     float64 `json:"contractmargin"  xorm:"'CONTRACTMARGIN'"`         // 合同保证金
	Remark             string  `json:"remark"  xorm:"'Remark'"`                         // 备注
	ATTACHMENT         string  `json:"attachment"  xorm:"'ATTACHMENT'"`                 // 附件
	ReckonRealQty      float64 `json:"reckonrealqty"  xorm:"'ReckonRealQty'"`           // 已确定量
	ReckonOtherAmount  float64 `json:"reckonotheramount"  xorm:"'ReckonOtherAmount'"`   // 其它费用
	ReckonAdjustAmount float64 `json:"reckonadjustamount"  xorm:"'ReckonAdjustAmount'"` // 调整金额
	Price              float64 `json:"price"  xorm:"'Price'"`                           // 价格
	LoanAmount         float64 `json:"loanamount"  xorm:"'LoanAmount'"`                 // 贷款总额=已定价额+调整金额
	Contractno         string  `json:"contractno"  xorm:"'Contractno'"`                 // 合同编号
	TotalAmount        float64 `json:"totalamount"  xorm:"'-'"`                         // 合计总额
	ReckonedAmount     float64 `json:"reckonedamount"  xorm:"'ReckonedAmount'"`         // 实际已收付额(已确定额,已收付总额-已退款总额)
	BUYUSERID          int64   `json:"-"  xorm:"'BUYUSERID'"`                           // 采购方ID
	SELLUSERID         int64   `json:"-"  xorm:"'SELLUSERID'"`                          // 销售方ID
	AUDITTIME          string  `json:"audittime"  xorm:"'AUDITTIME'"`                   // 审核时间
	CREATETIME         string  `json:"createtime"  xorm:"'CREATETIME'"`                 // 创建时间
	SPOTGOODSBRANDID   int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`     // 现货品牌ID(DGFactoryItem表的ID)
	BRANDNAME          string  `json:"brandname"  xorm:"'brandname'"`                   // 品牌名称
	WRSTANDARDID       int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`             // 品类ID
	WRSTANDARDNAME     string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`         // 品类名称
	WRSTANDARDCODE     string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`         // 品类代码
	TRADEUSERID        int64   `json:"tradeuserid"  xorm:"'TRADEUSERID'"`               // 交易员id
	SALEUSERID         int64   `json:"saleuserid"  xorm:"'SALEUSERID'"`                 // 业务员id
	MERUSERID          int64   `json:"meruserid"  xorm:"'MERUSERID'"`                   // 跟单员id
	ACCOUNTID          string  `json:"accountid"  xorm:"'ACCOUNTID'"`                   // 期货账户id
	BIZTYPE            int32   `json:"biztype"  xorm:"'BIZTYPE'"`                       // 业务类型 1-套保 2-套利
	CURRENCYID         int32   `json:"currencyid"  xorm:"'CURRENCYID'"`                 // 币种id
	SUBJECTID          string  `json:"subjectid"  xorm:"'SUBJECTID'"`                   // 交易主体ID
	SUBJECTNAME        string  `json:"subjectname"  xorm:"'SUBJECTNAME'"`               // 交易主体名称
	VATRATE            float64 `json:"vatrate"  xorm:"'VATRATE'"`                       // 增值税率

	NICKNAME           string `json:"nickname"`           // 账户昵称
	TRADEUSERLOGINCODE string `json:"tradeuserlogincode"` // 交易员登录代码
	SALEUSERLOGINCODE  string `json:"saleuserlogincode"`  // 业务员登录代码
	MERUSERLOGINCODE   string `json:"meruserlogincode"`   // 跟单员登录代码
	CURRENCYNAME       string `json:"currencyname"`       // 币种名称
	TRADEUSERNAME      string `json:"tradeusername"`      // 交易员名称
	SALEUSERNAME       string `json:"saleusername"`       // 业务员名称
	MERUSERNAME        string `json:"merusername"`        // 跟单员名称

	// 筛选条件
	QryType   int32 `json:"-"` // 查询类型 1-未提交 2-待审核 3-履约中 4-已完成
	UserType  int32 `json:"-"` // 用户类型  2-管理员 7-企业成员
	OwnUserId int64 `json:"-"` // 登录用户id
}

func (r *Ermcp3SellBuyContract) calc() {
	r.TotalAmount = r.LoanAmount + r.ReckonOtherAmount
	// #95629 公式变更 modify:2021-06-16 09:54:08
	// 应付款额=货款总额-已付款额+已退款额
	// 应收款额=货款总额-已收款额+已退款额
	r.UnpayAmount = r.LoanAmount - r.ReckonedAmount
	// 预付款额=已付款额-已退款额-货款总额
	// 预收款额=已收款额-已退款额-货款总额
	r.PrePayAmount = r.ReckonedAmount - r.LoanAmount
	// 预收票额=已收票额-货款总额
	// 预开票额=已开票额-货款总额
	r.PreInvoiceAmount = r.InvoiceAmount - r.LoanAmount

	// 应收票额=货款总额-已收票额
	// 应开票额=货款总额-已开票额
	r.DaikaiAmount = r.LoanAmount - r.InvoiceAmount

	if r.Contracttype == 1 {
		//采购合同, 取销售方名称
		r.AccountName, r.NICKNAME = mtpcache.GetUserNameAndNickName(r.SELLUSERID)
	} else {
		//销售合同, 取采购方名称
		r.AccountName, r.NICKNAME = mtpcache.GetUserNameAndNickName(r.BUYUSERID)
	}

	r.TRADEUSERLOGINCODE = mtpcache.GetLoginCodeByUserId(r.TRADEUSERID)
	r.SALEUSERLOGINCODE = mtpcache.GetLoginCodeByUserId(r.SALEUSERID)
	r.MERUSERLOGINCODE = mtpcache.GetLoginCodeByUserId(r.MERUSERID)
	r.TRADEUSERNAME = mtpcache.GetUserNameByUserId(r.TRADEUSERID)
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
	r.SALEUSERNAME = mtpcache.GetUserNameByUserId(r.SALEUSERID)
	r.MERUSERNAME = mtpcache.GetUserNameByUserId(r.MERUSERID)
}

func (r *Ermcp3SellBuyContract) buildSql() string {
	var sqlId utils.SQLVal = `

select t.contractno,
       to_char(t.SpotContractId) SpotContractId,
       t.deliverygoodsid,
       t.spotgoodsdesc,
       t.goodsid,
       t.pricemove,
       t.userid,
       t.BUYUSERID,
       t.SELLUSERID,
       t.qty,
       t.qty - t.pricedqty unpricedqty,
       t.pricedqty,
       (case
         when t.pricetype = 3 then
          t.qty - t.RECKONREALQTY
         else
          t.pricedqty - nvl(t.ReckonRealQty, 0)
       end) unsureqty,
       t.reckonedamount PayAmount,
       t.invoiceamount,
       (t.pricedamount + t.RECKONADJUSTAMOUNT - t.invoiceamount) as daikaiAmount,
       to_char(t.startdate, 'yyyy-mm-dd hh24:mi:ss') startdate,
       to_char(t.enddate, 'yyyy-mm-dd hh24:mi:ss') enddate,
       to_char(t.deliverystartdate, 'yyyy-mm-dd hh24:mi:ss') deliverystartdate,
       to_char(t.deliveryenddate, 'yyyy-mm-dd hh24:mi:ss') deliveryenddate,
       wr.convertfactor,
       t.contractstatus,
       t.pricetype,
       t.producttype,
       t.contracttype,
       t.remark,
       to_char(t.contractattachment) attachment,
       t.margin,
       t.contractmargin,
       t.SPOTGOODSBRANDID,
       t.tradeuserid,
       t.saleuserid,
       t.meruserid,
       t.currencyid,
       t.biztype,
       g.deliverygoodscode,
       g.deliverygoodsname,
       wr.wrstandardid,
       wr.wrstandardname,
       wr.wrstandardcode, 
       wr.vatrate,
       g2.goodscode,
       g2.goodsname,
       e.enumdicname,
       t.contracttype,
       t.pricedamount,
       case
         when t.pricedqty > 0 then
          (t.pricedamount) / t.pricedqty
         else
          0
       end pricedAvg,
       t.ReckonRealQty,
       t.ReckonOtherAmount,
       t.ReckonAdjustAmount,
       t.Price,
       t.ReckonedAmount,
       to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') audittime,
       to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime,
       t.pricedamount + t.ReckonAdjustAmount as LoanAmount,
       gb.dgfactoryitemvalue BRANDNAME,
       to_char(t.subjectid) subjectid,
       b.subjectname
  from ermcp_spotcontract t
  left join ermcp_pa_areasubject b on t.subjectid=b.subjectid
  left join deliverygoods g
    on t.deliverygoodsid = g.deliverygoodsid
  left join goods g2
    on t.goodsid = g2.goodsid
  left join wrstandard wr
    on t.wrstandardid = wr.wrstandardid
  left join DGFactoryItem gb
    on t.spotgoodsbrandid = gb.dgfactoryitemid
  left join enumdicitem e
    on wr.unitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 where 1 = 1
`
	sqlId.AndEx("t.userid", r.UserID, r.UserID > 0)
	sqlId.AndEx("t.contracttype", r.Contracttype, r.Contracttype != 0)
	sqlId.AndEx("t.SpotContractId", r.SpotContractId, len(r.SpotContractId) > 0)
	if r.UserType == 7 {
		// 企业成员
		sqlId.Join(fmt.Sprintf(" and %v in(t.saleuserid, t.tradeuserid, t.meruserid)", r.OwnUserId))
	}

	var status string
	nQueryType := r.QryType
	if 1 == nQueryType {
		// 全部
		status = "2,3"
	} else if 2 == nQueryType {
		// 待点价
		status = "2"
	} else {
		// 履约
		status = "2"
	}

	if len(status) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.contractstatus in (%v)", status))
	}
	sqlId.JoinEx(r.QryType == 2, " and t.qty - t.pricedqty > 0 and t.pricetype !=1")
	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3SellBuyContract) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3SellBuyContract, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3ExposureDetail 敞口现货明细
type Ermcp3ExposureDetail struct {
	Createtime        string  `json:"createtime"  xorm:"'createtime'"`           // 时间
	Areauserid        int64   `json:"areauserid"  xorm:"'areauserid'"`           // 机构ID
	Logtype           int32   `json:"logtype"  xorm:"'logtype'"`                 // 类型 - 1:套保计划 2:现货合同
	Contracttype      int32   `json:"contracttype"  xorm:"'contracttype'"`       // 现货合同类型 - 1:采购 -1:销售
	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
	ChangeQty         float64 `json:"changeQty"  xorm:"'changeQty'"`             // 套保变动量
	Convertfactor     float64 `json:"convertfactor"  xorm:"'convertfactor'"`     // 标仓系数
	Convertratio      float64 `json:"convertratio"  xorm:"'convertratio'"`       // 折算系数
	DELIVERYGOODSID   int32   `json:"-"  xorm:"'DELIVERYGOODSID'"`               // 现货品种id
	DELIVERYGOODSCODE string  `json:"-"  xorm:"'DELIVERYGOODSCODE'"`             // 现货品种代码
	DELIVERYGOODSNAME string  `json:"-"  xorm:"'DELIVERYGOODSNAME'"`             // 现货品种名称
	MGUNITID          int32   `json:"mgunitid"  xorm:"'MGUNITID'"`               // 套保品种单位id
	MGUNITIDNAME      string  `json:"mgunitidname"`                              // 套保品种单位名称
	WRSTANDARDID      int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`       // 现货商品ID(SEQ_WRSTANDARD)
	WRSTANDARDCODE    string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`   // 现货商品代码
	WRSTANDARDNAME    string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`   // 现货商品名称
	UNITID            int32   `json:"unitid"  xorm:"'UNITID'"`                   // 现货商品单位id
	ENUMDICNAME       string  `json:"enumdicname"  xorm:"'enumdicname'"`         // 现货商品单位名称
}

func (r *Ermcp3ExposureDetail) 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
	}

	r.MGUNITIDNAME = mtpcache.GetEnumDicitemName(r.MGUNITID)
}

func (r *Ermcp3ExposureDetail) buildSql() string {
	var sqlId utils.SQLVal = `
select to_char(t.updatetime, 'yyyy-mm-dd hh24:mi:ss') createtime,
       s.userid as areauserid,
       2 as LogType,
       s.contracttype,
       t.todaypricedqty qty,
       to_char(s.spotcontractid) as relateid,
       s.contractno as relateNo,
       t.todaypricedqty * c.convertratio * (1 / (1 + nvl(w.vatrate, 0))) as changeQty,
       m.middlegoodsid,
       m.middlegoodsname,
       m.middlegoodscode,
       m.goodsunitid mgunitid,
       w.wrstandardid,
       w.wrstandardcode,
       w.wrstandardname,
       w.unitid,
       e.enumdicname,
       c.convertratio
  from ERMCP_SCMiddleGoods t
 inner join ermcp_spotcontract s
    on t.spotcontractid = s.spotcontractid
  left join erms_middlegoods m
    on t.middlegoodsid = m.middlegoodsid
  left join erms2_wrsconvertdetail c
    on t.middlegoodsid = c.middlegoodsid
   and t.wrstandardid = c.wrstandardid
  left join wrstandard w
    on t.wrstandardid = w.wrstandardid
  left join enumdicitem e
    on w.unitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
  left join erms2_wrsconvertdetail c
    on t.middlegoodsid = c.middlegoodsid
   and t.wrstandardid = c.wrstandardid
 where 1 = 1
`
	sqlId.And("s.userid", r.Areauserid)
	sqlId.And("t.middlegoodsid", r.MiddlegoodsId)
	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3ExposureDetail) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Ermcp3ExposureDetail, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// Ermcp3AreaSpot 敞口现货头寸
type Ermcp3AreaSpot struct {
	AREAUSERID       int64   `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'"`         // 更新时间
	CONVERTRATIO     float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"`     // 套保系数
	WRSTANDARDID     int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`     // 现货商品ID(SEQ_WRSTANDARD)
	WRSTANDARDCODE   string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"` // 现货商品代码
	WRSTANDARDNAME   string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"` // 现货商品名称
	UNITID           int32   `json:"unitid"  xorm:"'UNITID'"`                 // 现货商品单位id

	ENUMDICNAME string `json:"enumdicname"` // 单位名称
}

func (r *Ermcp3AreaSpot) 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)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *Ermcp3AreaSpot) buildSql() string {
	var sqlId utils.SQLVal = `
select t.AREAUSERID,
       t.ORIBUYPLANQTY,
       t.ORIBUYPRICEDQTY,
       t.ORISELLPLANQTY,
       t.ORISELLPRICEDQTY,
       t.BUYPLANQTY,
       t.BUYPRICEDQTY,
       t.SELLPLANQTY,
       t.SELLPRICEDQTY,
       t.hedgeqty,
       t.arbitrageqty,
       t.hedgeqty + t.arbitrageqty TOTALSPOTQTY,
       t.convertratio,
       w.wrstandardid,
       w.wrstandardcode,
       w.wrstandardname,
       w.unitid,
       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME
  from ermcp_areamiddlesum t
  left join wrstandard w
    on t.wrstandardid = w.wrstandardid
 where 1 = 1
`
	sqlId.And("t.areauserid", r.AREAUSERID)
	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3AreaSpot) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Ermcp3AreaSpot, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// Ermcp3AreaSpotDetail 敞口->现货头寸->现货明细
type Ermcp3AreaSpotDetail 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-销售
	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         int64   `json:"-"`                                       // 所属用户ID
	WRSTANDARDID   int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`     // 现货商品ID(SEQ_WRSTANDARD)
	WRSTANDARDCODE string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"` // 现货商品代码
	WRSTANDARDNAME string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"` // 现货商品名称
}

func (r *Ermcp3AreaSpotDetail) 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)
}

func (r *Ermcp3AreaSpotDetail) buildSql() string {
	var sqlId utils.SQLVal = `
select *
  from (select to_char(t.hedgeplanid) as relatedid,
               t.hedgeplanno relatedno,
               1 as LogType,
               t.contracttype,
               t.planqty qty,
               to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') createtime,
               w.wrstandardid,
               w.wrstandardcode,
               w.wrstandardname,
               w.unitid,
               t.areauserid as userid
          from ermcp_hedgeplan t
         inner join wrstandard w
            on t.wrstandardid = w.wrstandardid
         where t.hedgeplanstatus in (2, 3, 5)
           and t.tradedate = to_char(sysdate, 'yyyymmdd')
           and t.areauserid = %v
           and t.wrstandardid = %v
        union all
        select to_char(t.spotcontractid) as relatedid,
               t.contractno relatedno,
               2 as LogType,
               t.contracttype,
               t.qty,
               to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') createtime,
               w.wrstandardid,
               w.wrstandardcode,
               w.wrstandardname,
               w.unitid,
               t.userid
          from ermcp_spotcontract t
         inner join wrstandard w
            on t.wrstandardid = w.wrstandardid
         where t.userid = %v
           and t.wrstandardid = %v
           and t.contractstatus in (2, 3, 5)
           and t.tradedate = to_char(sysdate, 'yyyymmdd')) a
 order by a.createtime desc
`
	sqlId.FormatParam(r.UserId, r.WRSTANDARDID, r.UserId, r.WRSTANDARDID)
	return sqlId.String()
}

// GetDataEx
func (r *Ermcp3AreaSpotDetail) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Ermcp3AreaSpotDetail, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// Ermcp3Wrstandard 现货品类
type Ermcp3Wrstandard struct {
	WRSTANDARDID    int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`       // 品类ID(SEQ_WRSTANDARD)
	WRSTANDARDCODE  string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`   // 品类代码
	DELIVERYGOODSID int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"` // 现货品种ID
	UNITID          int32   `json:"unitid"  xorm:"'UNITID'"`                   // 品类单位ID
	MINIVALUE       int32   `json:"minivalue"  xorm:"'MINIVALUE'"`             // 最小变动值
	MINIVALUEDP     int32   `json:"minivaluedp"  xorm:"'MINIVALUEDP'"`         // 最小变动值小数位
	REALMINIVALUE   int32   `json:"realminivalue"  xorm:"'REALMINIVALUE'"`     // 实际最小变动值
	REALMINIVALUEDP int32   `json:"realminivaluedp"  xorm:"'REALMINIVALUEDP'"` // 实际最小变动值小数位
	CREATORID       int64   `json:"creatorid"  xorm:"'CREATORID'"`             // 创建人
	CREATETIME      string  `json:"createtime"  xorm:"'CREATETIME'"`           // 创建时间
	WRSTANDARDNAME  string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`   // 品类名称
	AREAUSERID      int64   `json:"areauserid"  xorm:"'AREAUSERID'"`           // 所属机构
	EnumdicName     string  `json:"enumdicname"`                               // 品类单位名称
	ISVALID         int32   `json:"isvalid"  xorm:"'ISVALID'"`                 // 是否有效 0-无效(停用) 1-有效(正常)
	UPDATETIME      string  `json:"updatetime"  xorm:"'UPDATETIME'"`           // 更新时间
	REMARK          string  `json:"remark"  xorm:"'REMARK'"`                   // 备注
	CONVERTFACTOR   float64 `json:"convertfactor"  xorm:"'CONVERTFACTOR'"`     // 标仓系数
	CANTRADE        int32   `json:"cantrade"  xorm:"'CANTRADE'"`               // 是否允许交易 - 0:不允许 1:允许 -1:未配置(数据库里没有记录)
}

func (r *Ermcp3Wrstandard) calc() {
	r.EnumdicName = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *Ermcp3Wrstandard) buildSql() string {
	sqlId := "select t.WRSTANDARDID," +
		"       t.WRSTANDARDCODE," +
		"       t.DELIVERYGOODSID," +
		"       t.UNITID," +
		"       t.MINIVALUE," +
		"       t.MINIVALUEDP," +
		"       t.REALMINIVALUE," +
		"       t.REALMINIVALUEDP," +
		"       t.CREATORID," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.WRSTANDARDNAME," +
		"       t.ISVALID," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.REMARK," +
		"       t.CONVERTFACTOR," +
		"       t.AREAUSERID," +
		"       nvl(m.cantrade,-1) cantrade" +
		"  from wrstandard t" +
		"  left join marketspotgoodsconfig m on t.wrstandardid = m.wrstandardid" +
		" where 1 = 1"
	if r.AREAUSERID > 0 {
		sqlId += fmt.Sprintf(" and t.AREAUSERID=%v", r.AREAUSERID)
	}
	if r.DELIVERYGOODSID > 0 {
		sqlId = sqlId + fmt.Sprintf(" and t.DELIVERYGOODSID=%v", r.DELIVERYGOODSID)
	}
	sqlId += " order by createtime desc"
	return sqlId
}

// GetDataEx
func (r *Ermcp3Wrstandard) GetDataEx() (interface{}, error) {
	e := db.GetEngine()
	s := e.SQL(r.buildSql())
	sData := make([]Ermcp3Wrstandard, 0)
	if err := s.Find(&sData); err != nil {
		return nil, err
	}
	for i := range sData {
		sData[i].calc()
	}
	return sData, nil
}

// Ermcp3AreaStockApply 出入库申请
type Ermcp3AreaStockApply struct {
	INOUTAPPLYID      string  `json:"inoutapplyid"  xorm:"'INOUTAPPLYID'"`           // 申请ID(607+Unix秒时间戳(10位)+xxxxxx)
	USERID            int64   `json:"userid"  xorm:"'USERID'"`                       // 机构ID
	INOUTTYPE         int32   `json:"inouttype"  xorm:"'INOUTTYPE'"`                 // 出入库类型 - 5:采购入库 6:销售出库 7:生产入库 8:生产出库
	WRSTANDARDID      int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`           // 品类ID
	SPOTGOODSBRANDID  int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`   // 现货品牌ID
	SPOTCONTRACTID    string  `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'"`       // 关联现货合同ID
	WAREHOUSEINFOID   string  `json:"warehouseinfoid"  xorm:"'WAREHOUSEINFOID'"`     // 现货仓库ID
	APPLYSTATUS       int32   `json:"applystatus"  xorm:"'APPLYSTATUS'"`             // 申请状态 - 1:待审核 2:审核通过 3:审核拒绝 4:处理失败 5:已撤回
	APPLYSRC          int32   `json:"applysrc"  xorm:"'APPLYSRC'"`                   // 申请来源 - 1:管理端 2:终端
	APPLYID           int64   `json:"applyid"  xorm:"'APPLYID'"`                     // 申请人
	APPLYREMARK       string  `json:"applyremark"  xorm:"'APPLYREMARK'"`             // 申请备注
	APPLYTIME         string  `json:"applytime"  xorm:"'APPLYTIME'"`                 // 申请时间
	AUDITSRC          int32   `json:"auditsrc"  xorm:"'AUDITSRC'"`                   // 审核来源 - 1:管理端 2:终端
	AUDITID           int64   `json:"auditid"  xorm:"'AUDITID'"`                     // 审核人
	AUDITTIME         string  `json:"audittime"  xorm:"'AUDITTIME'"`                 // 审核时间
	AUDITREMARK       string  `json:"auditremark"  xorm:"'AUDITREMARK'"`             // 审核备注
	AUDITTRADEDATE    string  `json:"audittradedate"  xorm:"'AUDITTRADEDATE'"`       // 审核交易日(yyyyMMdd)
	WRSTANDARDNAME    string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`       // 品类名称
	WRSTANDARDCODE    string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`       // 品类代码
	UNITID            int32   `json:"unitid"  xorm:"'UNITID'"`                       // 单位id
	BRANDNAME         string  `json:"brandname"  xorm:"'BRANDNAME'"`                 // 品牌名称
	WAREHOUSENAME     string  `json:"warehousename"  xorm:"'WAREHOUSENAME'"`         // 仓库名称
	WAREHOUSECODE     string  `json:"warehousecode"  xorm:"'WAREHOUSECODE'"`         // 仓库代码
	WAREHOUSETYPE     int32   `json:"warehousetype"  xorm:"'WAREHOUSETYPE'"`         // 仓库类型 - 1 厂库  2 自有库  3 合作库
	CONTRACTNO        string  `json:"contractno"  xorm:"'CONTRACTNO'"`               // 合同编号
	CONTRACTTYPE      int32   `json:"contracttype"  xorm:"'CONTRACTTYPE'"`           // 现货合同类型 - 1:采购 -1:销售
	BUYUSERID         int64   `json:"buyuserid"  xorm:"'BUYUSERID'"`                 // 采购方userid
	SELLUSERID        int64   `json:"selluserid"  xorm:"'SELLUSERID'"`               // 销售方userid
	PRICETYPE         int32   `json:"pricetype"  xorm:"'PRICETYPE'"`                 // 定价类型 - 1:一口价 2:点价 3:暂定价
	CONTRACTQTY       float64 `json:"contractqty"  xorm:"'CONTRACTQTY'"`             // 合同量
	DETAILJSON        string  `json:"detailjson"  xorm:"'DETAILJSON'"`               // 明细数据
	BUYUSERNAME       string  `json:"buyusername"`                                   // 采购方名称
	SELLUSERNAME      string  `json:"sellusername"`                                  // 销售方名称
	APPLYNAME         string  `json:"applyname"`                                     // 申请人名称
	AUDITNAME         string  `json:"auditname"`                                     // 审核人名称
	ENUMDICNAME       string  `json:"enumdicname"`                                   // 现货商品单位名称
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货品种id
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货品种名称
	BUYNICKNAME       string  `json:"buynickname"`                                   // 采购方昵称
	SELLNICKNAME      string  `json:"sellnickname"`                                  // 销售方昵称
	QTY               float64 `json:"qty"`                                           // 数量

	FilterStatus string `json:"-"` // 查询条件, 申请状态, 逗号隔开
	FilterType   string `json:"-"` // 查询条件, 出入库类型, 逗号隔开
	BeginDate    string `json:"-"` // 开始日期
	EndDate      string `json:"-"` // 结束日期
	UserType     int32  `json:"-"` // 用户类型
}

func (r *Ermcp3AreaStockApply) calc() {
	// 采购方名称
	r.BUYUSERNAME, r.BUYNICKNAME = mtpcache.GetUserNameAndNickName(r.BUYUSERID)
	// 销售方名称
	r.SELLUSERNAME, r.SELLNICKNAME = mtpcache.GetUserNameAndNickName(r.SELLUSERID)
	// 申请人名称
	if r.APPLYSRC == 1 {
		r.APPLYNAME = mtpcache.GetSystemmangerLoginCode(r.APPLYID)
	} else {
		r.APPLYNAME = mtpcache.GetLoginCodeByLoginId(r.APPLYID)
	}
	// 审核人名称
	if r.AUDITSRC == 1 {
		r.AUDITNAME = mtpcache.GetSystemmangerLoginCode(r.AUDITID)
	} else {
		r.AUDITNAME = mtpcache.GetLoginCodeByLoginId(r.AUDITID)
	}
	// 单位名称
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)

	if len(r.DETAILJSON) > 0 {
		// 解析qty字段
		req := struct {
			QTY float64 `json:"InOutQty"` // 数量
		}{}
		if json.Unmarshal([]byte(r.DETAILJSON), &req) == nil {
			r.QTY = req.QTY
		}
	}
}

func (r *Ermcp3AreaStockApply) buildSql() string {
	var sqlId utils.SQLVal = "SELECT to_char(t.operateapplyid) INOUTAPPLYID," +
		"       t.USERID," +
		"       t.operateapplytype INOUTTYPE," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.DELIVERYGOODSID," +
		"       to_char(t.relatedid) SPOTCONTRACTID," +
		"       to_char(t.WAREHOUSEINFOID) WAREHOUSEINFOID," +
		"       to_char(t.detailjson) detailjson," +
		"       t.APPLYSTATUS," +
		"       t.APPLYSRC," +
		"       t.APPLYID," +
		"       t.remark APPLYREMARK," +
		"       to_char(t.APPLYTIME, 'yyyy-mm-dd hh24:mi:ss') APPLYTIME," +
		"       t.AUDITSRC," +
		"       t.AUDITID," +
		"       to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," +
		"       t.AUDITREMARK," +
		"       t.AUDITTRADEDATE," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       g.goodsunitid unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype," +
		"       s.contractno," +
		"       s.contracttype," +
		"       s.buyuserid," +
		"       s.selluserid," +
		"       s.pricetype," +
		"       s.qty CONTRACTQTY," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname" +
		"  FROM ermcp_contractoperateapply t" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  left join dgfactoryitem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  left join warehouseinfo h" +
		"    on t.warehouseinfoid = h.autoid" +
		"  left join ermcp_spotcontract s" +
		"    on t.relatedid = s.spotcontractid" +
		" WHERE t.operateapplytype in(5,6,7,8)"

	areaUserId := mtpcache.GetAreaUserId(r.USERID, r.UserType)
	if areaUserId == r.USERID {
		// 机构用户
		sqlId.And("t.USERID", r.USERID)
	} else {
		if r.INOUTTYPE == 7 || r.INOUTTYPE == 8 {
			// 生产出入库, 没有相关合同
			sqlId.And("t.USERID", r.USERID)
		} else {
			sqlId.Join(fmt.Sprintf(" and %v in(s.saleuserid, s.tradeuserid, s.meruserid)", r.USERID))
		}
	}
	// 合同id
	sqlId.AndInterEx("t.RELATEDID", r.SPOTCONTRACTID, len(r.SPOTCONTRACTID) > 0)

	// 现货品种id
	if r.DELIVERYGOODSID > 0 {
		sqlId.And("t.DELIVERYGOODSID", r.DELIVERYGOODSID)
	}
	// 品类id
	if r.WRSTANDARDID > 0 {
		sqlId.And("t.WRSTANDARDID", r.WRSTANDARDID)
	}
	// 品牌id
	if r.SPOTGOODSBRANDID > 0 {
		sqlId.And("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID)
	}
	// 仓库id
	sqlId.AndInterEx("t.WAREHOUSEINFOID", r.WAREHOUSEINFOID, len(r.WAREHOUSEINFOID) > 0)
	// 日期范围
	if len(r.BeginDate) > 0 {
		if r.BeginDate == r.EndDate {
			sqlId.And("t.tradedate", r.BeginDate)
		} else if r.EndDate > r.BeginDate {
			sqlId.BiggerOrEq("t.tradedate", r.BeginDate)
			sqlId.LessOrEq("t.tradedate", r.EndDate)
		}
	}
	// 出入库类型
	if len(r.FilterType) > 0 {

		// 原INOUTTYPE 1,2,3,4 转为 新表 5,6,7,8
		r.FilterType = strings.ReplaceAll(r.FilterType, "1", "5")
		r.FilterType = strings.ReplaceAll(r.FilterType, "2", "6")
		r.FilterType = strings.ReplaceAll(r.FilterType, "3", "7")
		r.FilterType = strings.ReplaceAll(r.FilterType, "4", "8")

		if len(r.FilterType) == 1 {
			sqlId.AndInterEx("t.OPERATEAPPLYTYPE", r.FilterType, true)
		} else {
			sqlId.Join(fmt.Sprintf(" and t.OPERATEAPPLYTYPE in(%v)", r.FilterType))
		}
	}
	// 出入库状态
	if len(r.FilterStatus) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.APPLYSTATUS in(%v)", r.FilterStatus))
	}
	// 排序
	sqlId.Join(" order by t.APPLYTIME desc")
	return sqlId.String()
}

// GetDataEx 查询库存申请(记录)
func (r *Ermcp3AreaStockApply) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3AreaStockApply, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	if err == nil {
		for i := range sData {
			sData[i].calc()
		}
	}
	return sData, err
}

// Ermcp3AreaStockApplySum 合同出入库数量汇总
type Ermcp3AreaStockApplySum struct {
	INOUTTYPE        int32   `json:"inouttype"  xorm:"'INOUTTYPE'"`               // 出入库类型 - 5:采购入库 6:销售出库 7:生产入库 8:生产出库
	WRSTANDARDID     int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`         // 品类id
	SPOTGOODSBRANDID int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"` // 品牌id
	BRANDNAME        string  `json:"brandname"  xorm:"'BRANDNAME'"`               // 品牌名称
	WRSTANDARDNAME   string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`     // 品类名称
	TOTALQTY         float64 `json:"totalqty"  xorm:"'TOTALQTY'"`                 // 总数量
	SPOTCONTRACTID   string  `json:"spotcontractid"  xorm:"'relatedid'"`          // 关联现货合同ID
	DETAILJSON       string  `json:"-"  xorm:"'DETAILJSON'"`                      // 出入库数量
	Key              string  `json:"-"`                                           // 汇总主键
}

// Calc
func (r *Ermcp3AreaStockApplySum) Calc() {
	if len(r.DETAILJSON) > 0 {
		// 解析qty字段
		req := struct {
			QTY float64 `json:"InOutQty"` // 数量
		}{}
		if json.Unmarshal([]byte(r.DETAILJSON), &req) == nil {
			r.TOTALQTY = req.QTY
		}
	}

	r.Key = fmt.Sprintf("%v_%v_%v", r.INOUTTYPE, r.WRSTANDARDID, r.SPOTGOODSBRANDID)
}

func (r *Ermcp3AreaStockApplySum) buildSql() string {
	var sqlId utils.SQLVal = "select t.detailjson," +
		"       t.operateapplytype    INOUTTYPE," +
		"       t.wrstandardid," +
		"       t.relatedid," +
		"       t.spotgoodsbrandid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       w.wrstandardcode," +
		"       w.wrstandardname" +
		"  from ermcp_contractoperateapply t" +
		"  left join dgfactoryitem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		" where t.applystatus = 2 and t.operateapplytype in (5, 6)"
	sqlId.And("t.relatedid", r.SPOTCONTRACTID)
	return sqlId.String()
}

// GetDataEx 获取合同出入库汇总信息
func (r *Ermcp3AreaStockApplySum) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3AreaStockApplySum, 0)
	if err := db.GetEngine().SQL(r.buildSql()).Find(&sData); err == nil {
		mSum := map[string]Ermcp3AreaStockApplySum{}
		for i := range sData {
			sData[i].Calc()
			// 按品牌+品类+出入库类型 汇总
			if d, ok := mSum[sData[i].Key]; ok {
				d.TOTALQTY += sData[i].TOTALQTY
				mSum[sData[i].Key] = d
			} else {
				mSum[sData[i].Key] = sData[i]
			}
		}
		if len(mSum) > 0 {
			sData = make([]Ermcp3AreaStockApplySum, 0)
			for _, v := range mSum {
				sData = append(sData, v)
			}
		}
	}

	return sData, nil
}

// Ermcp3AreaStock 机构库存表
type Ermcp3AreaStock struct {
	TODAYBUYINQTY      float64 `json:"todaybuyinqty"  xorm:"'TODAYBUYINQTY'"`                    // 今日采购入库量
	TODAYPRODUCEINQTY  float64 `json:"todayproduceinqty"  xorm:"'TODAYPRODUCEINQTY'"`            // 今日生产入库量
	TODAYSELLOUTQTY    float64 `json:"todayselloutqty"  xorm:"'TODAYSELLOUTQTY'"`                // 今日销售出库量
	TODAYPRODUCEOUTQTY float64 `json:"todayproduceoutqty"  xorm:"'TODAYPRODUCEOUTQTY'"`          // 今日生产出库量
	UPDATETIME         string  `json:"updatetime"  xorm:"'UPDATETIME'"`                          // 更新时间
	USERID             int64   `json:"userid"  xorm:"'USERID'" form:"userid" binding:"required"` // 机构ID
	WRSTANDARDID       string  `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`                      // 品类ID
	SPOTGOODSBRANDID   int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`              // 现货品牌ID
	WAREHOUSEINFOID    string  `json:"warehouseinfoid"  xorm:"'WAREHOUSEINFOID'"`                // 仓库ID
	ORISTOCK           float64 `json:"oristock"  xorm:"'ORISTOCK'"`                              // 期初库存量(昨日量)
	CURSTOCK           float64 `json:"curstock"  xorm:"'CURSTOCK'"`                              // 期末库存量(今日量)
	WRSTANDARDNAME     string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`                  // 品类名称
	WRSTANDARDCODE     string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`                  // 品类代码
	UNITID             int32   `json:"unitid"  xorm:"'UNITID'"`                                  // 单位id
	BRANDNAME          string  `json:"brandname"  xorm:"'BRANDNAME'"`                            // 品牌名称
	WAREHOUSENAME      string  `json:"warehousename"  xorm:"'WAREHOUSENAME'"`                    // 仓库名称
	WAREHOUSECODE      string  `json:"warehousecode"  xorm:"'WAREHOUSECODE'"`                    // 仓库代码
	WAREHOUSETYPE      int32   `json:"warehousetype"  xorm:"'WAREHOUSETYPE'"`                    // 仓库类型 - 1 厂库  2 自有库  3 合作库
	USERNAME           string  `json:"username"`                                                 // 机构名称
	ENUMDICNAME        string  `json:"enumdicname"`                                              // 单位名称
	DELIVERYGOODSID    int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`                // 现货品种id
	DELIVERYGOODSCODE  string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`            // 现货品种代码
	DELIVERYGOODSNAME  string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`            // 现货品种名称
	GOODSUNITID        int32   `json:"goodsunitid"  xorm:"'GOODSUNITID'"`                        // 现货商品单位id
}

func (r *Ermcp3AreaStock) calc() {
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
}

func (r *Ermcp3AreaStock) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.TODAYBUYINQTY," +
		"       t.TODAYPRODUCEINQTY," +
		"       t.TODAYSELLOUTQTY," +
		"       t.TODAYPRODUCEOUTQTY," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.USERID," +
		"       t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.WAREHOUSEINFOID," +
		"       t.DELIVERYGOODSID," +
		"       t.ORISTOCK," +
		"       t.CURSTOCK," +
		"       w.wrstandardname," +
		"       w.wrstandardcode," +
		"       w.unitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       h.warehousename," +
		"       h.warehousecode," +
		"       h.warehousetype," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid" +
		"  FROM ERMCP_AREASTOCK t" +
		"  LEFT JOIN WRSTANDARD w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  LEFT JOIN dgfactoryitem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  LEFT JOIN deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  LEFT JOIN WAREHOUSEINFO h" +
		"    on t.warehouseinfoid = h.autoid" +
		" WHERE 1 = 1"
	sqlId.And("t.USERID", r.USERID)
	sqlId.Join(" order by t.WRSTANDARDID, t.UPDATETIME desc")
	return sqlId.String()
}

// GetDataEx 获取机构库存信息
func (r *Ermcp3AreaStock) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3AreaStock, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	if err == nil {
		for i := range sData {
			sData[i].calc()
		}
	}
	return sData, err
}

// Ermcp3SpotGoodsPrice 现货市价
type Ermcp3SpotGoodsPrice struct {
	WRSTANDARDID      int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`           // 现货品类ID(通用则为0)
	SPOTGOODSBRANDID  int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`   // 现货品牌ID(通用则为0, 不为0则须先有品类ID)
	CURRENCYID        int32   `json:"currencyid"  xorm:"'CURRENCYID'"`               // 报价货币ID
	SPOTGOODSPRICE    float64 `json:"spotgoodsprice"  xorm:"'SPOTGOODSPRICE'"`       // 现货价格
	TRADEDATE         string  `json:"tradedate"  xorm:"'TRADEDATE'"`                 // 交易日(yyyyMMdd)
	OPERATESRC        int32   `json:"operatesrc"  xorm:"'OPERATESRC'"`               // 最后操作来源 - 1:管理端 2:终端
	OPERATEID         int64   `json:"operateid"  xorm:"'OPERATEID'"`                 // 最后操作人
	OPERATETIME       string  `json:"operatetime"  xorm:"'OPERATETIME'"`             // 最后操作时间
	ISVALID           int32   `json:"isvalid"  xorm:"'ISVALID'"`                     // 是否有效 - 0:无效 1:有效
	AREAUSERID        int64   `json:"areauserid"  xorm:"'AREAUSERID'"`               // 所属机构id
	WRSTANDARDCODE    string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`       // 品类代码
	WRSTANDARDNAME    string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`       // 品类名称
	UNITID            int32   `json:"unitid"  xorm:"'UNITID'"`                       // 现货商品单位id
	BRANDNAME         string  `json:"brandname"  xorm:"'BRANDNAME'"`                 // 品牌名称
	GMUNITID          int32   `json:"gmunitid"  xorm:"'GMUNITID'"`                   // 品类单位id
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货商品id
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货商品代码
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货商品名称

	OPERATORNAME  string `json:"operatorname"`  // 操作人名称
	ENUMDICNAME   string `json:"enumdicname"`   // 现货商品单位名称
	GBENUMDICNAME string `json:"gbenumdicname"` // 品类单位名称
	CURRENCYNAME  string `json:"currencyname"`  // 币种名称
}

func (r *Ermcp3SpotGoodsPrice) calc() {
	if r.OPERATESRC == 1 {
		r.OPERATORNAME = mtpcache.GetSystemmangerLoginCode(r.OPERATEID)
	} else {
		r.OPERATORNAME = mtpcache.GetLoginCodeByLoginId(r.OPERATEID)
	}
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
	r.GBENUMDICNAME = mtpcache.GetEnumDicitemName(r.GMUNITID)
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
}

func (r *Ermcp3SpotGoodsPrice) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.CURRENCYID," +
		"       t.SPOTGOODSPRICE," +
		"       t.TRADEDATE," +
		"       t.OPERATESRC," +
		"       t.OPERATEID," +
		"       to_char(t.OPERATETIME, 'yyyy-mm-dd hh24:mi:ss') OPERATETIME," +
		"       t.ISVALID," +
		"       w.areauserid," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       w.unitid gmunitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname," +
		"       g.goodsunitid unitid" +
		"  FROM ERMCP_SPOTGOODSPRICE t" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  left join dgfactoryitem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		" WHERE 1 = 1"
	sqlId.And("w.areauserid", r.AREAUSERID)
	return sqlId.String()
}

// GetDataEx 获取现货市价
func (r *Ermcp3SpotGoodsPrice) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3SpotGoodsPrice, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	if err == nil {
		for i := range sData {
			sData[i].calc()
		}
	}
	return sData, err
}

// Ermcp3SpotGoodsPriceLog 现货市价操作日志
type Ermcp3SpotGoodsPriceLog struct {
	LOGID             int64   `json:"logid"  xorm:"'LOGID'"`                         // 日志id
	WRSTANDARDID      int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`           // 现货品类ID(通用则为0)
	SPOTGOODSBRANDID  int32   `json:"spotgoodsbrandid"  xorm:"'SPOTGOODSBRANDID'"`   // 现货品牌ID(通用则为0, 不为0则须先有品类ID)
	CURRENCYID        int64   `json:"currencyid"  xorm:"'CURRENCYID'"`               // 报价货币ID
	SPOTGOODSPRICE    float64 `json:"spotgoodsprice"  xorm:"'SPOTGOODSPRICE'"`       // 现货价格
	TRADEDATE         string  `json:"tradedate"  xorm:"'TRADEDATE'"`                 // 交易日(yyyyMMdd)
	OPERATESRC        int32   `json:"operatesrc"  xorm:"'OPERATESRC'"`               // 最后操作来源 - 1:管理端 2:终端
	OPERATEID         int64   `json:"operateid"  xorm:"'OPERATEID'"`                 // 最后操作人
	OPERATETIME       string  `json:"operatetime"  xorm:"'OPERATETIME'"`             // 最后操作时间
	AREAUSERID        int64   `json:"areauserid"  xorm:"'AREAUSERID'"`               // 所属机构id
	WRSTANDARDCODE    string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`       // 品类代码
	WRSTANDARDNAME    string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`       // 品类名称
	UNITID            int32   `json:"unitid"  xorm:"'UNITID'"`                       // 现货商品单位id
	BRANDNAME         string  `json:"brandname"  xorm:"'BRANDNAME'"`                 // 品牌名称
	GMUNITID          int32   `json:"gmunitid"  xorm:"'GMUNITID'"`                   // 品类单位id
	DELIVERYGOODSID   int32   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'"`     // 现货商品id
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货商品代码
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 现货商品名称

	OPERATORNAME  string `json:"operatorname"`  // 操作人名称
	ENUMDICNAME   string `json:"enumdicname"`   // 现货商品单位名称
	GBENUMDICNAME string `json:"gbenumdicname"` // 品类单位名称
}

func (r *Ermcp3SpotGoodsPriceLog) calc() {
	if r.OPERATESRC == 1 {
		r.OPERATORNAME = mtpcache.GetSystemmangerLoginCode(r.OPERATEID)
	} else {
		r.OPERATORNAME = mtpcache.GetLoginCodeByLoginId(r.OPERATEID)
	}
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
	r.GBENUMDICNAME = mtpcache.GetEnumDicitemName(r.GMUNITID)
}

func (r *Ermcp3SpotGoodsPriceLog) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.WRSTANDARDID," +
		"       t.SPOTGOODSBRANDID," +
		"       t.CURRENCYID," +
		"       t.SPOTGOODSPRICE," +
		"       t.TRADEDATE," +
		"       t.OPERATESRC," +
		"       t.OPERATEID," +
		"       to_char(t.OPERATETIME, 'yyyy-mm-dd hh24:mi:ss') OPERATETIME," +
		"       t.LOGID," +
		"       w.areauserid," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       w.unitid gmunitid," +
		"       gb.dgfactoryitemvalue brandname," +
		"       g.goodsunitid unitid ," +
		"       g.deliverygoodsid," +
		"       g.deliverygoodscode," +
		"       g.deliverygoodsname" +
		"  FROM ERMCP_SPOTGOODSPRICELOG t" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  left join dgfactoryitem gb" +
		"    on t.spotgoodsbrandid = gb.dgfactoryitemid" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		" WHERE 1 = 1"
	sqlId.And("w.areauserid", r.AREAUSERID)
	sqlId.And("t.deliverygoodsid", r.DELIVERYGOODSID)
	sqlId.AndEx("t.SPOTGOODSBRANDID", r.SPOTGOODSBRANDID, r.SPOTGOODSBRANDID > 0)
	sqlId.AndEx("t.wrstandardid", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	return sqlId.String()
}

// GetDataEx 获取现货市价信息日志
func (r *Ermcp3SpotGoodsPriceLog) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3SpotGoodsPriceLog, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	if err == nil {
		for i := range sData {
			sData[i].calc()
		}
	}
	return sData, err
}

// Ermcp3HedgePlan 套保计划表结构
type Ermcp3HedgePlan struct {
	Areauserid        int64   `json:"areauserid"  xorm:"'Areauserid'"`               // 用户ID
	Hedgeplanid       string  `json:"hedgeplanid"  xorm:"'Hedgeplanid'"`             // 套保计划ID(601+Unix秒时间戳(10位)+xxxxxx)
	Hedgeplanno       string  `json:"hedgeplanno"  xorm:"'Hedgeplanno'"`             // 套保计划编号(名称)
	Contracttype      int     `json:"contracttype"  xorm:"'Contracttype'"`           // 计划类型 - 1:采购 -1:销售
	Deliverygoodsid   int     `json:"deliverygoodsid"  xorm:"'Deliverygoodsid'"`     // 现货品种ID
	Deliverygoodsname string  `json:"deliverygoodsname"  xorm:"'Deliverygoodsname'"` // 现货品种名称
	DELIVERYGOODSCODE string  `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"` // 现货品种代码
	Producttype       int     `json:"producttype"  xorm:"'Producttype'"`             // 产品类型 - 1:标准仓单 2:等标 3:非标
	Spotgoodsdesc     string  `json:"spotgoodsdesc"  xorm:"'Spotgoodsdesc'"`         // 商品型号
	Planqty           float64 `json:"planqty"  xorm:"'Planqty'"`                     // 计划数量
	Convertfactor     float64 `json:"convertfactor"  xorm:"'Convertfactor'"`         // 标仓系数
	Plantime          string  `json:"plantime"  xorm:"'Plantime'"`                   // 计划时间
	Hedgeplanstatus   int     `json:"hedgeplanstatus"  xorm:"'Hedgeplanstatus'"`     // 套保计划状态 -  0:未提交 1:待审核 2:执行中 3:正常完结 4:审核拒绝 5:异常完结 6:已撤回
	Remark            string  `json:"remark"  xorm:"'Remark'"`                       // 备注
	CREATETIME        string  `json:"createtime"  xorm:"'CREATETIME'"`               // 创建时间
	UPDATETIME        string  `json:"updatetime"  xorm:"'UPDATETIME'"`               // 更新时间
	AUDITTIME         string  `json:"audittime"  xorm:"'AUDITTIME'"`                 // 审核时间
	UNITID            int32   `json:"unitid"  xorm:"'UNITID'"`                       // 单位id(现货商品)
	ENUMDICNAME       string  `json:"enumdicname"`                                   // 单位名称(现货商品)
	WRSTANDARDID      int32   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`           // 品类id
	WRSTANDARDCODE    string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`       // 品类代码
	WRSTANDARDNAME    string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`       // 品类名称
	ACCOUNTID         int64   `json:"accountid"  xorm:"'ACCOUNTID'"`                 // 资金账号
	TAACCOUNTNAME     string  `json:"taaccountname"  xorm:"'TAACCOUNTNAME'"`         // 资金账号名称
	TRADEUSERID       int64   `json:"tradeuserid"  xorm:"'TRADEUSERID'"`             // 交易用户id
	CURRENCYID        int32   `json:"currencyid"  xorm:"'CURRENCYID'"`               // 币种id
	WRUNITID          int32   `json:"wrunitid"  xorm:"'WRUNITID'"`                   // 品类单位id
	AUDITID           int64   `json:"auditid"  xorm:"'auditid'"`                     // 审核id
	AUDITSRC          int32   `json:"auditsrc"  xorm:"'AUDITSRC'"`                   // 审核来源 1-管理端 2-终端
	AUDITREMARK       string  `json:"auditremark"  xorm:"'auditremark'"`             // 审核备注
	AUDITNAME         string  `json:"auditname"  xorm:"'AUDITNAME'"`                 // 审核人名称
	VATRATE           float64 `json:"vatrate"  xorm:"'VATRATE'"`                     // 增值税率

	WRUNITIDNAME  string `json:"wrunitidame"`   // 品类单位名称
	TRADEUSERNAME string `json:"tradeusername"` // 交易用户名称
	CURRENCYNAME  string `json:"currencyname"`  // 币种名称

	UserType int32 `json:"-"` // 过滤条件, 用户类型
}

func (r *Ermcp3HedgePlan) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.UNITID)
	r.TRADEUSERNAME = mtpcache.GetUserNameByUserId(r.TRADEUSERID)
	r.CURRENCYNAME = mtpcache.GetCurrencyName(r.CURRENCYID)
	if r.WRUNITID > 0 {
		r.WRUNITIDNAME = mtpcache.GetEnumDicitemName(r.WRUNITID)
	}
	if r.AUDITSRC == 1 {
		if r.AUDITNAME == "" && r.AUDITID > 0 {
			r.AUDITNAME = mtpcache.GetSystemmangerLoginCode(r.AUDITID)
		}
	}
}

func (r *Ermcp3HedgePlan) buildSql(status string) string {
	str := "select to_char(t.Hedgeplanid) Hedgeplanid," +
		"       t.Hedgeplanno," +
		"       t.Contracttype," +
		"       t.Deliverygoodsid," +
		"       g.Deliverygoodsname," +
		"       g.deliverygoodscode," +
		"       g.goodsunitid unitid," +
		"       t.Producttype," +
		"       t.Spotgoodsdesc," +
		"       t.Planqty," +
		"       t.Convertfactor," +
		"       to_char(t.Plantime, 'yyyy-mm-dd hh24:mi:ss') Plantime," +
		"       to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"       to_char(t.updatetime, 'yyyy-mm-dd hh24:mi:ss') updatetime," +
		"       to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') audittime," +
		"       t.Hedgeplanstatus," +
		"       t.Remark," +
		"       t.wrstandardid," +
		"       w.wrstandardcode," +
		"       w.wrstandardname," +
		"       w.unitid wrunitid," +
		"       w.vatrate," +
		"       t.accountid," +
		"       t.tradeuserid," +
		"       t.currencyid," +
		"       t.auditid," +
		"       t.auditsrc," +
		"       t.auditremark," +
		"       ta.accountname taaccountname," +
		"       l.accountname auditname" +
		"  from ermcp_hedgeplan t" +
		"  left join deliverygoods g" +
		"    on t.deliverygoodsid = g.deliverygoodsid" +
		"  left join wrstandard w" +
		"    on t.wrstandardid = w.wrstandardid" +
		"  left join taaccount ta" +
		"    on t.accountid = ta.accountid" +
		"  left join loginaccount l on t.auditid=l.loginid" +
		" where t.hedgeplanstatus in (%v)"

	if r.UserType == 0 {
		// 终端没传值, 则自己去找
		userId := mtpcache.GetAreaUserId(r.Areauserid, 0)
		if userId == r.Areauserid {
			r.UserType = 2
		} else {
			r.UserType = 7
		}
	}

	if r.UserType == 2 {
		str += "   and t.areauserid = %v"
	} else if r.UserType == 7 {
		// 企业成员用tradeuserid匹配
		str += "   and t.tradeuserid = %v"
	} else {
		str += "   and t.areauserid = %v"
	}

	if status == "2" {
		// 执行中状态，按审核时间倒序
		str += " order by t.audittime desc"
	} else {
		str += " order by t.updatetime desc"
	}

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

// GetData 从数据库中查询套保计划数据
func (r *Ermcp3HedgePlan) GetData(status string) ([]Ermcp3HedgePlan, error) {
	e := db.GetEngine()
	sData := make([]Ermcp3HedgePlan, 0)
	if err := e.SQL(r.buildSql(status)).Find(&sData); err != nil {
		logger.GetLogger().Errorf("query hedgeplan:%v", err)
		return sData, err
	}
	for i := range sData {
		sData[i].calc()
	}

	return sData, nil
}

// Ermcp3GoodsGroup 商品组
type Ermcp3GoodsGroup struct {
	GOODSGROUPID       int32   `json:"goodsgroupid"  xorm:"'GOODSGROUPID'"`             // 商品组ID(自增ID)
	GOODSGROUPNAME     string  `json:"goodsgroupname"  xorm:"'GOODSGROUPNAME'"`         // 商品组名称
	GOODSGROUPSTATUS   int32   `json:"goodsgroupstatus"  xorm:"'GOODSGROUPSTATUS'"`     // 商品组状态 - 1:正常 2:注销
	MARKETID           int32   `json:"marketid"  xorm:"'MARKETID'"`                     // 所属市场ID
	CREATETIME         string  `json:"createtime"  xorm:"'CREATETIME'"`                 // 创建时间
	CREATORID          int32   `json:"creatorid"  xorm:"'CREATORID'"`                   // 创建人
	MODIFYTIME         string  `json:"modifytime"  xorm:"'MODIFYTIME'"`                 // 修改时间
	MODIFIERID         int32   `json:"modifierid"  xorm:"'MODIFIERID'"`                 // 修改人
	QUOTESOURCEGROUPID int64   `json:"quotesourcegroupid"  xorm:"'QUOTESOURCEGROUPID'"` // 所属行情源分组ID[参考行情市场用\通道交易]
	OUTERGROUPCODE     string  `json:"outergroupcode"  xorm:"'OUTERGROUPCODE'"`         // 外部商品组代码[通道交易]
	CANSHORT           int32   `json:"canshort"  xorm:"'CANSHORT'"`                     // 是否允许做空[通道交易] - 0:不能做空 1:可以做空
	EXEXCHANGEID       int64   `json:"exexchangeid"  xorm:"'EXEXCHANGEID'"`             // 外部交易所ID[通道交易]
	CURRENCYID         int32   `json:"currencyid"  xorm:"'CURRENCYID'"`                 // 报价货币ID
	GOODUNITID         int32   `json:"goodunitid"  xorm:"'GOODUNITID'"`                 // 报价单位ID
	AGREEUNIT          float32 `json:"agreeunit"  xorm:"'AGREEUNIT'"`                   // 合约单位
	DECIMALPLACE       int32   `json:"decimalplace"  xorm:"'DECIMALPLACE'"`             // 报价小数位
	QUOTEMINUNIT       int32   `json:"quoteminunit"  xorm:"'QUOTEMINUNIT'"`             // 行情最小变动单位 [整数，报价小数位一起使用]
	INNERDEALMODE      int32   `json:"innerdealmode"  xorm:"'INNERDEALMODE'"`           // 内部成交方式[通道交易] - 1:净头寸 2:开平 3:平今
	OUTERDEALMODE      int32   `json:"outerdealmode"  xorm:"'OUTERDEALMODE'"`           // 外部成交方式[通道交易]- 1:净头寸 2:开平 3:平今
	SYNCGOODSQTY       int32   `json:"syncgoodsqty"  xorm:"'SYNCGOODSQTY'"`             // 同步合约数[通道交易-投资管理用] - 0表示不限
	CLOSEPRICEMODE     int32   `json:"closepricemode"  xorm:"'CLOSEPRICEMODE'"`         // 强平价格方式 - 1:市价 2:最新价 3:涨跌停（未实现）
	GROUPCATEGROYID    int32   `json:"groupcategroyid"  xorm:"'GROUPCATEGROYID'"`       // 品种分类ID
	EXEXCHANGECODE     string  `json:"exexchangecode"  xorm:"'EXEXCHANGECODE'"`         // 交易所代码
	EXEXCHANGENAME     string  `json:"exexchangename"  xorm:"'EXEXCHANGENAME'"`         // 交易所名称
	EXCHANGEFULLNAME   string  `json:"exchangefullname"  xorm:"'EXCHANGEFULLNAME'"`     // 交易所全称

	ENUMDICNAME string `json:"enumdicname"`                                     // 单位名称
	AreaUserId  int64  `json:"areauserid" form:"areauserid" binding:"required"` // 所属机构id(套保品是有areauserid的,当要排除已配置商品组时需要areauserid过滤)
	ExcludeCfg  int32  `json:"excludecfg" form:"excludecfg"`                    // 排除套保品已配置期货分类下的商品组(但显示已配置商品组) 1-排除
}

func (r *Ermcp3GoodsGroup) calc() {
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID)
}

func (r *Ermcp3GoodsGroup) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.GOODSGROUPID," +
		"       t.GOODSGROUPNAME," +
		"       t.GOODSGROUPSTATUS," +
		"       t.MARKETID," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.CREATORID," +
		"       to_char(t.MODIFYTIME, 'yyyy-mm-dd hh24:mi:ss') MODIFYTIME," +
		"       t.MODIFIERID," +
		"       t.QUOTESOURCEGROUPID," +
		"       t.OUTERGROUPCODE," +
		"       t.CANSHORT," +
		"       t.EXEXCHANGEID," +
		"       t.CURRENCYID," +
		"       t.GOODUNITID," +
		"       t.AGREEUNIT," +
		"       t.DECIMALPLACE," +
		"       t.QUOTEMINUNIT," +
		"       t.INNERDEALMODE," +
		"       t.OUTERDEALMODE," +
		"       t.SYNCGOODSQTY," +
		"       t.CLOSEPRICEMODE," +
		"       t.GROUPCATEGROYID," +
		"       e.exexchangecode," +
		"       e.exexchangename," +
		"       e.exchangefullname" +
		"  FROM GOODSGROUP t" +
		"  LEFT JOIN market m" +
		"    on t.marketid = m.marketid" +
		"  LEFT JOIN externalexchange e on t.exexchangeid=e.autoid" +
		" WHERE 1 = 1" +
		"   and m.trademode = 15"
	if r.ExcludeCfg == 1 {
		str := "select t.goodsgroupid" +
			"  from ERMS_MiddleGoods t" +
			" where t.isvalid=1 and t.areauserid = %v" +
			" union all " +
			"select goodsgroupid" +
			"  from goodsgroup t" +
			" where t.groupcategroyid not in" +
			"       (select distinct g.groupcategroyid" +
			"          from erms_middlegoods t" +
			"          left join goodsgroup g" +
			"            on t.goodsgroupid = g.goodsgroupid" +
			"         where t.isvalid=1 and t.areauserid = %v)"
		str = fmt.Sprintf(str, r.AreaUserId, r.AreaUserId)
		sqlId.Join(fmt.Sprintf(" and t.goodsgroupid in(%v)", str))
	}
	return sqlId.String()
}

// GetData
func (r *Ermcp3GoodsGroup) GetData() ([]Ermcp3GoodsGroup, error) {
	sData := make([]Ermcp3GoodsGroup, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// GetDataEx 商品组信息
func (r *Ermcp3GoodsGroup) GetDataEx() (interface{}, error) {
	return r.GetData()
}

// Ermcp3RootUserAccount 顶级用户
type Ermcp3RootUserAccount struct {
	ROOTUSERID       int64  `json:"rootuserid"  xorm:"ROOTUSERID"`             // 根用户ID
	CANRECOMMEND     int32  `json:"canrecommend"  xorm:"CANRECOMMEND"`         // 是否可推荐 - 0:不可 1;可
	USERID           int64  `json:"userid"  xorm:"USERID"`                     // 用户ID
	USERTYPE         int32  `json:"usertype"  xorm:"USERTYPE"`                 // 账户类型 -  1:交易所 2:机构 3:会员子机构 4:经纪人 5:投资者 6:客户 7:企业成员(云平台)
	MODIFYSTATUS     int32  `json:"modifystatus"  xorm:"MODIFYSTATUS"`         // 变更状态 1 未变更 2 变更中 3 变更待审核 4 变更待复核(投资者)
	ACCOUNTNAME      string `json:"accountname"  xorm:"ACCOUNTNAME"`           // 账户名称(机构名称)
	PARENTUSERID     int64  `json:"parentuserid"  xorm:"PARENTUSERID"`         // 所属机构ID
	BROKER           int64  `json:"broker"  xorm:"BROKER"`                     // 所属经纪人ID
	SUBAREALEVELPATH string `json:"subarealevelpath"  xorm:"SUBAREALEVELPATH"` // 子机构层级路径(逗号分隔,首尾加逗号)
	REFEREEUSERID    int64  `json:"refereeuserid"  xorm:"REFEREEUSERID"`       // 推荐人ID
	MEMBERUSERID     int64  `json:"memberuserid"  xorm:"MEMBERUSERID"`         // 所属会员ID
	ACCOUNTSTATUS    int32  `json:"accountstatus"  xorm:"ACCOUNTSTATUS"`       // 账户状态 -  1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:停用(注销) 7:注销(删除)
	RECKONACCOUNTID  int64  `json:"reckonaccountid"  xorm:"RECKONACCOUNTID"`   // 默认结算资金账号ID(机构分润使用) 作废
	CREATETIME       string `json:"createtime"  xorm:"CREATETIME"`             // 创建时间
	CREATORID        int64  `json:"creatorid"  xorm:"CREATORID"`               // 创建人
	AUDITTIME        string `json:"audittime"  xorm:"AUDITTIME"`               // 审核时间
	AUDITUSERID      int64  `json:"audituserid"  xorm:"AUDITUSERID"`           // 审核人
	AUDITREMARK      string `json:"auditremark"  xorm:"AUDITREMARK"`           // 审核备注
	CANCELTIME       string `json:"canceltime"  xorm:"CANCELTIME"`             // 销户时间
	CANCELUSERID     int64  `json:"canceluserid"  xorm:"CANCELUSERID"`         // 销户人
	MODIFYTIME       string `json:"modifytime"  xorm:"MODIFYTIME"`             // 修改时间
	MODIFIERID       int64  `json:"modifierid"  xorm:"MODIFIERID"`             // 修改人
	MODIFYREMARK     string `json:"modifyremark"  xorm:"MODIFYREMARK"`         // 变更备注
	MAXINVESTORNUM   int64  `json:"maxinvestornum"  xorm:"MAXINVESTORNUM"`     // 最大用户数(经纪会员下投资者个数)
	HASAUTH          int32  `json:"hasauth"  xorm:"HASAUTH"`                   // 是否已实名认证 - 0:未认证 1:已认证 2:已提交(待审核) 3:已拒绝
	REFERCOUNT       int32  `json:"refercount"  xorm:"REFERCOUNT"`             // 推荐总人数
	REFERNUM         string `json:"refernum"  xorm:"REFERNUM"`                 // 推荐码
	ISANONYMOUS      int32  `json:"isanonymous"  xorm:"ISANONYMOUS"`           // 是否匿名下单 - 0:否 1:是
	PARENTTOPUSER    string `json:"parenttopuser"  xorm:"PARENTTOPUSER"`       // 上级顶级机构 [092=0,1时，默认为1， 092=2时若自已为顶级，则填入自己，自己不为顶级，填入ParentUserID的"ParentTopUser"]
	SUBACCOUNTLEVEL  int32  `json:"subaccountlevel"  xorm:"SUBACCOUNTLEVEL"`   // 子账户层数
	APPLYSRC         int32  `json:"applysrc"  xorm:"APPLYSRC"`                 // 申请来源 - 1:管理端 2:终端
	AUDITSRC         int32  `json:"auditsrc"  xorm:"AUDITSRC"`                 // 审核来源 - 1:管理端 2:终端
}

func (r *Ermcp3RootUserAccount) calc() {

}

func (r *Ermcp3RootUserAccount) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.ROOTUSERID," +
		"       t.CANRECOMMEND," +
		"       t.USERID," +
		"       t.USERTYPE," +
		"       t.MODIFYSTATUS," +
		"       t.ACCOUNTNAME," +
		"       t.PARENTUSERID," +
		"       t.BROKER," +
		"       t.SUBAREALEVELPATH," +
		"       t.REFEREEUSERID," +
		"       t.MEMBERUSERID," +
		"       t.ACCOUNTSTATUS," +
		"       t.RECKONACCOUNTID," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       t.CREATORID," +
		"       to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," +
		"       t.AUDITUSERID," +
		"       t.AUDITREMARK," +
		"       to_char(t.CANCELTIME, 'yyyy-mm-dd hh24:mi:ss') CANCELTIME," +
		"       t.CANCELUSERID," +
		"       to_char(t.MODIFYTIME, 'yyyy-mm-dd hh24:mi:ss') MODIFYTIME," +
		"       t.MODIFIERID," +
		"       t.MODIFYREMARK," +
		"       t.MAXINVESTORNUM," +
		"       t.HASAUTH," +
		"       t.REFERCOUNT," +
		"       t.REFERNUM," +
		"       t.ISANONYMOUS," +
		"       t.PARENTTOPUSER," +
		"       t.SUBACCOUNTLEVEL," +
		"       t.APPLYSRC," +
		"       t.AUDITSRC" +
		"  FROM USERACCOUNT t" +
		" WHERE 1 = 1 and t.userid in(select rootuserid from useraccount where userid=%v)"
	sqlId.FormatParam(r.USERID)
	return sqlId.String()
}

// GetDataEx 获取顶级用户
func (r *Ermcp3RootUserAccount) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3RootUserAccount, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3PaAreaSubject 交易主体
type Ermcp3PaAreaSubject struct {
	SUBJECTID       int64  `json:"subjectid"  xorm:"SUBJECTID" form:"subjectid"`                   // 主体ID(SEQ_ERMCP_PA_AREASUBJECT)
	SUBJECTTYPE     int32  `json:"subjecttype"  xorm:"SUBJECTTYPE"`                                // 主体类型 - 1:交易主体 2:业务部门
	SUBJECTNAME     string `json:"subjectname"  xorm:"SUBJECTNAME"`                                // 主体名称
	PARENTSUBJECTID string `json:"parentsubjectid"  xorm:"PARENTSUBJECTID" form:"parentsubjectid"` // 上级主体ID
	AREAUSERID      int64  `json:"areauserid"  xorm:"AREAUSERID" form:"userid"`                    // 所属机构
	CREATETIME      string `json:"createtime"  xorm:"CREATETIME"`                                  // 创建时间
	CREATORSRC      int32  `json:"creatorsrc"  xorm:"CREATORSRC"`                                  // 创建人来源 - 1:管理端 2:终端 3:交易
	CREATORID       string `json:"creatorid"  xorm:"CREATORID" form:"creatorid"`                   // 创建人ID
	MODIFIERSRC     int32  `json:"modifiersrc"  xorm:"MODIFIERSRC"`                                // 修改人来源 - 1:管理端 2:终端
	MODIFIERID      string `json:"modifierid"  xorm:"MODIFIERID" form:"modifierid"`                // 修改人
	MODIFYTIME      string `json:"modifytime"  xorm:"MODIFYTIME"`                                  // 修改时间
	SUBJECTSTATUS   int32  `json:"subjectstatus"  xorm:"SUBJECTSTATUS" form:"subjectstatus"`       // 主体状态 - 1:正常 2: 停用
	CANMODIFY       int32  `json:"canmodify"  xorm:"CANMODIFY"`                                    // 是否可修改 - 0:否 1:是
	REMARK          string `json:"remark"  xorm:"REMARK"`                                          // 备注
}

func (r *Ermcp3PaAreaSubject) calc() {

}

func (r *Ermcp3PaAreaSubject) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT t.SUBJECTID,
       t.SUBJECTTYPE,
       t.SUBJECTNAME,
       t.PARENTSUBJECTID,
       t.AREAUSERID,
       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME,
       t.CREATORSRC,
       t.CREATORID,
       t.MODIFIERSRC,
       t.MODIFIERID,
       to_char(t.MODIFYTIME, 'yyyy-mm-dd hh24:mi:ss') MODIFYTIME,
       t.SUBJECTSTATUS,
       t.CANMODIFY,
       t.REMARK
  FROM ERMCP_PA_AREASUBJECT t
 WHERE 1 = 1
`
	areaUserId := mtpcache.GetAreaUserId(r.AREAUSERID, 0)
	sqlId.And("t.AREAUSERID", areaUserId)
	sqlId.AndEx("t.SUBJECTID", r.SUBJECTID, r.SUBJECTID > 0)
	sqlId.AndEx("t.SUBJECTTYPE", r.SUBJECTTYPE, r.SUBJECTTYPE > 0)
	sqlId.AndEx("t.SUBJECTSTATUS", r.SUBJECTSTATUS, r.SUBJECTSTATUS > 0)
	return sqlId.String()
}

// GetDataEx 获取交易主体
func (r *Ermcp3PaAreaSubject) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3PaAreaSubject, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3PaAreaAuditCfg 审核设置
type Ermcp3PaAreaAuditCfg struct {
	AREAUSERID            int64  `json:"areauserid"  xorm:"AREAUSERID" form:"userid"`         // 用户ID
	CUSTOMERAUDITFLAG     int32  `json:"customerauditflag"  xorm:"CUSTOMERAUDITFLAG"`         // 客户资料是否需要审核 - 0:不需审核 1:需要审核
	SPOTCONTRACTAUDITFLAG int32  `json:"spotcontractauditflag"  xorm:"SPOTCONTRACTAUDITFLAG"` // 现货合同是否需要审核 - 0:不需审核 1:需要审核
	POINTPRICEAUDITFLAG   int32  `json:"pointpriceauditflag"  xorm:"POINTPRICEAUDITFLAG"`     // 点价是否需要审核 - 0:不需审核 1:需要审核
	DELIVERYAUDITFLAG     int32  `json:"deliveryauditflag"  xorm:"DELIVERYAUDITFLAG"`         // 交收是否需要审核 - 0:不需审核 1:需要审核
	MONEYAUDITFLAG        int32  `json:"moneyauditflag"  xorm:"MONEYAUDITFLAG"`               // 款项是否需要审核 - 0:不需审核 1:需要审核
	INVOICEAUDITFLAG      int32  `json:"invoiceauditflag"  xorm:"INVOICEAUDITFLAG"`           // 发票是否需要审核 - 0:不需审核 1:需要审核
	BUYINAUDITFLAG        int32  `json:"buyinauditflag"  xorm:"BUYINAUDITFLAG"`               // 采购入库是否需要审核 - 0:不需审核 1:需要审核
	SELLOUTAUDITFLAG      int32  `json:"selloutauditflag"  xorm:"SELLOUTAUDITFLAG"`           // 销售出库是否需要审核 - 0:不需审核 1:需要审核
	PRODUCEINAUDITFLAG    int32  `json:"produceinauditflag"  xorm:"PRODUCEINAUDITFLAG"`       // 生产入库是否需要审核 - 0:不需审核 1:需要审核
	PRODUCEOUTAUDITFLAG   int32  `json:"produceoutauditflag"  xorm:"PRODUCEOUTAUDITFLAG"`     // 生产出库是否需要审核 - 0:不需审核 1:需要审核
	OPERATORSRC           int32  `json:"operatorsrc"  xorm:"OPERATORSRC"`                     // 操作来源 - 1:管理端 2:终端
	OPERATORID            int64  `json:"operatorid"  xorm:"OPERATORID" form:"operatorid"`     // 操作人
	UPDATETIME            string `json:"updatetime"  xorm:"UPDATETIME"`                       // 更新时间
}

func (r *Ermcp3PaAreaAuditCfg) calc() {

}

func (r *Ermcp3PaAreaAuditCfg) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT t.AREAUSERID,
       t.CUSTOMERAUDITFLAG,
       t.SPOTCONTRACTAUDITFLAG,
       t.POINTPRICEAUDITFLAG,
       t.DELIVERYAUDITFLAG,
       t.MONEYAUDITFLAG,
       t.INVOICEAUDITFLAG,
       t.BUYINAUDITFLAG,
       t.SELLOUTAUDITFLAG,
       t.PRODUCEINAUDITFLAG,
       t.PRODUCEOUTAUDITFLAG,
       t.OPERATORSRC,
       t.OPERATORID,
       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME
  FROM ERMCP_PA_AREAAUDITCONFIG t
 WHERE 1 = 1
`
	areaUserId := mtpcache.GetAreaUserId(r.AREAUSERID, 0)
	sqlId.And("t.AREAUSERID", areaUserId)
	return sqlId.String()
}

// GetDataEx 获取审核设置
func (r *Ermcp3PaAreaAuditCfg) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3PaAreaAuditCfg, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// XHDeliveryGoods 商品信息-现货品种
type XHDeliveryGoods struct {
	AREAUSERID        int64  `json:"areauserid"  xorm:"'AREAUSERID'" form:"areauserid"`                // 所属机构
	DELIVERYGOODSID   int64  `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'" form:"deliverygoodsid"` // 现货品种ID(SEQ_DELIVERYGOODS)
	DELIVERYGOODSCODE string `json:"deliverygoodscode"  xorm:"'DELIVERYGOODSCODE'"`                    // 现货品种代码
	DELIVERYGOODSNAME string `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"`                    // 现货品种名称
	ISVALID           int32  `json:"isvalid"  xorm:"'ISVALID'"`                                        // 是否有效 - 0:无效 1:有效
	DGSTATUS          int32  `json:"dgstatus"  xorm:"'DGSTATUS'"`                                      // 品种状态 - 作废 - 0:未激活 1:正常
	REMARK            string `json:"remark"  xorm:"'REMARK'"`                                          // 备注
	UNITID            int64  `json:"unitid"  xorm:"'UNITID'"`                                          // 单位id(目前看,数据库中是空值)

	WDLST []XHWrstandard `json:"wdlst"` // 品类(现货商品)列表
	BDLST []XHBRAND      `json:"bdlst"` // 品牌列表
}

func (r *XHDeliveryGoods) calc() {
	if r.WDLST == nil {
		r.WDLST = make([]XHWrstandard, 0)
		r.BDLST = make([]XHBRAND, 0)
	}
}

func (r *XHDeliveryGoods) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT t.AREAUSERID,
       t.DELIVERYGOODSID,
       t.DELIVERYGOODSCODE,
       t.DELIVERYGOODSNAME,
       t.ISVALID,
       t.DGSTATUS,
       t.GOODSUNITID UNITID,
       t.REMARK
  FROM DELIVERYGOODS t
 WHERE 1 = 1
`
	sqlId.AndEx("t.AREAUSERID", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	return sqlId.String()
}

// GetDataEx 获取商品信息-现货品种
func (r *XHDeliveryGoods) GetDataEx() (interface{}, error) {
	sData := make([]XHDeliveryGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}

	if len(sData) > 0 {
		// 获取品类列表
		m1 := XHWrstandard{AREAUSERID: r.AREAUSERID, DELIVERYGOODSID: r.DELIVERYGOODSID}
		if d1, err := m1.GetDataEx(); err == nil {
			lstWd := d1.([]XHWrstandard)
			for i := range sData {
				sData[i].addWdlst(lstWd)
			}
		}

		// 获取品牌列表
		m2 := XHBRAND{DELIVERYGOODSID: r.DELIVERYGOODSID, AREAUSERID: r.AREAUSERID}
		if d2, err := m2.GetDataEx(); err == nil {
			lstBrand := d2.([]XHBRAND)
			for i := range sData {
				sData[i].addBrandlst(lstBrand)
			}
		}
	}

	return sData, err
}

// 增加品类列表
func (r *XHDeliveryGoods) addWdlst(lstWd []XHWrstandard) {
	for i := range lstWd {
		if lstWd[i].DELIVERYGOODSID == r.DELIVERYGOODSID {
			r.WDLST = append(r.WDLST, lstWd[i])
		}
	}
}

// 增加品牌列表
func (r *XHDeliveryGoods) addBrandlst(lstBrand []XHBRAND) {
	for i := range lstBrand {
		if lstBrand[i].DELIVERYGOODSID == r.DELIVERYGOODSID {
			r.BDLST = append(r.BDLST, lstBrand[i])
		}
	}
}

// XHBRAND 现货品种/品牌
type XHBRAND struct {
	BRANDID         int64  `json:"brandid"`   // 品牌id
	BRANDNAME       string `json:"brandname"` // 品牌名称
	DELIVERYGOODSID int64  `json:"-"`         // 品种ID
	AREAUSERID      int64  `json:"-"`         // 用户id
}

// GetDataEx 获取现货品种/品牌
func (r *XHBRAND) GetDataEx() (interface{}, error) {
	sData := make([]XHBRAND, 0)
	m := Ermcp3Brand{AREAUSERID: r.AREAUSERID, DELIVERYGOODSID: r.DELIVERYGOODSID}
	d, err := m.GetData()
	if err == nil {
		for _, v := range d {
			sData = append(sData, XHBRAND{DELIVERYGOODSID: v.DELIVERYGOODSID, BRANDID: v.BRANDID, BRANDNAME: v.BRANDNAME})
		}
	}
	return sData, err
}

// XHWrstandard 现货品种/品类
type XHWrstandard struct {
	AREAUSERID      int64   `json:"-"  xorm:"'AREAUSERID'" form:"areauserid"`                         // 所属机构
	DELIVERYGOODSID int64   `json:"deliverygoodsid"  xorm:"'DELIVERYGOODSID'" form:"deliverygoodsid"` // 品种ID
	WRSTANDARDID    int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`                              // 现货商品ID(SEQ_WRSTANDARD)
	WRSTANDARDCODE  string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`                          // 现货商品代码
	WRSTANDARDNAME  string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`                          // 现货商品名称
	VATRATE         float64 `json:"vatrate"  xorm:"'VATRATE'"`                                        // 增值税率
	ENUMDICNAME     string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`                                // 单位名称
	UNITID          int64   `json:"unitid"  xorm:"'UNITID'"`                                          // 单位id

	MGLST []XHMiddleGoods `json:"mglst"` // 套保品列表
}

func (r *XHWrstandard) calc() {
	if r.MGLST == nil {
		r.MGLST = make([]XHMiddleGoods, 0)
	}
}

func (r *XHWrstandard) buildSql() string {
	var sqlId utils.SQLVal = `
select t.areauserid,
       t.deliverygoodsid,
       t.wrstandardid,
       t.wrstandardcode,
       t.wrstandardname,
       t.vatrate,
       t.unitid,
       e.enumdicname
  from wrstandard t
  left join enumdicitem e
    on t.unitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 where 1 = 1
`
	sqlId.AndEx("t.AREAUSERID", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.DELIVERYGOODSID", r.DELIVERYGOODSID, r.DELIVERYGOODSID > 0)
	return sqlId.String()
}

// GetDataEx 获取现货品种/品类
func (r *XHWrstandard) GetDataEx() (interface{}, error) {
	sData := make([]XHWrstandard, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}

	if len(sData) > 0 {
		// 获取套保品列表
		m := XHMiddleGoods{AREAUSERID: r.AREAUSERID, WRSTANDARDID: r.WRSTANDARDID}
		if d, err := m.GetDataEx(); err == nil {
			lstMidd := d.([]XHMiddleGoods)
			for i := range sData {
				sData[i].addMgLst(lstMidd)
			}
		}
	}

	return sData, err
}

func (r *XHWrstandard) addMgLst(midd []XHMiddleGoods) {
	for i := range midd {
		if r.WRSTANDARDID == midd[i].WRSTANDARDID {
			r.MGLST = append(r.MGLST, midd[i])
		}
	}
}

// XHMiddleGoods 现货品种/品类/套保品
type XHMiddleGoods struct {
	AREAUSERID      int64   `json:"-"  xorm:"'AREAUSERID'" form:"areauserid"`     // 所属机构
	WRSTANDARDID    int64   `json:"-"  xorm:"'WRSTANDARDID'" form:"wrstandardid"` // 现货商品ID(SEQ_WRSTANDARD)
	CONVERTRATIO    float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"`          // 拆算系数
	MIDDLEGOODSID   int64   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`        // 套保品种id
	MIDDLEGOODSCODE string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"`    // 套保品种代码
	MIDDLEGOODSNAME string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`    // 套保品种名称
	ENUMDICNAME     string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`            // 单位名称
	UNITID          int64   `json:"unitid"  xorm:"'UNITID'"`                      // 单位id
	GOODSGROUPID    int64   `json:"-"  xorm:"'GOODSGROUPID'"`                     // 关联期货商品组id

	GPLST []TBGoodsGroup // 期货品种列表
}

func (r *XHMiddleGoods) calc() {
	r.GPLST = make([]TBGoodsGroup, 0)
}

func (r *XHMiddleGoods) buildSql() string {
	var sqlId utils.SQLVal = `
select w.wrstandardid,
       c.convertratio,
       m.areauserid,
       m.middlegoodsid,
       m.middlegoodscode,
       m.middlegoodsname,
       m.goodsunitid unitid,
       m.goodsgroupid,
       e.enumdicname
  from wrstandard w
 inner join erms2_wrsconvertdetail c
    on w.wrstandardid = c.wrstandardid
 inner join erms_middlegoods m
    on c.middlegoodsid = m.middlegoodsid
  left join enumdicitem e
    on m.goodsunitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 where 1 = 1 and w.isvalid = 1
`
	sqlId.AndEx("m.areauserid", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("w.WRSTANDARDID", r.WRSTANDARDID, r.WRSTANDARDID > 0)
	return sqlId.String()
}

// GetDataEx 获取现货品种/品类/套保品
func (r *XHMiddleGoods) GetDataEx() (interface{}, error) {
	sData := make([]XHMiddleGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	if len(sData) > 0 {
		// 获取关联期货品种
		m1 := TBGoodsGroup{}
		if d1, err := m1.GetDataEx(); err == nil {
			gpLst := d1.([]TBGoodsGroup)
			for i := range sData {
				sData[i].addGpLst(gpLst)
			}
		}
	}
	return sData, err
}

func (r *XHMiddleGoods) addGpLst(lst []TBGoodsGroup) {
	for i := range lst {
		if r.GOODSGROUPID == lst[i].DESTGOODSGROUPID {
			r.GPLST = append(r.GPLST, lst[i])
		}
	}
}

// TBMiddleGoods 商品信息-套保品种
type TBMiddleGoods struct {
	AREAUSERID         int64   `json:"areauserid"  xorm:"'AREAUSERID'" form:"areauserid"`          // 机构用户ID
	MIDDLEGOODSID      int64   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'" form:"middlegoodsid"` // 套保品种ID(SEQ_ERMS_MIDDLEGOODS)
	MIDDLEGOODSCODE    string  `json:"middlegoodscode"  xorm:"'MIDDLEGOODSCODE'"`                  // 套保品种代码
	MIDDLEGOODSNAME    string  `json:"middlegoodsname"  xorm:"'MIDDLEGOODSNAME'"`                  // 套保品种名称
	NEEDHEDGERATIO     float64 `json:"needhedgeratio"  xorm:"'NEEDHEDGERATIO'"`                    // 套保比率
	NEEDARBITRAGERATIO float64 `json:"needarbitrageratio"  xorm:"'NEEDARBITRAGERATIO'"`            // 套利比率
	GOODSGROUPID       int64   `json:"-"  xorm:"'GOODSGROUPID'"`                                   // 关联期货商品组id
	ENUMDICNAME        string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`                          // 单位名称

	GPLST []TBGoodsGroup // 期货品种列表
	WDLST []TBWrstandard // 商品信息列表(现货品类)
}

func (r *TBMiddleGoods) calc() {
	r.GPLST = make([]TBGoodsGroup, 0)
	r.WDLST = make([]TBWrstandard, 0)
}

func (r *TBMiddleGoods) buildSql() string {
	var sqlId utils.SQLVal = `
select t.areauserid,
       t.middlegoodsid,
       t.middlegoodscode,
       t.middlegoodsname,
       t.needhedgeratio,
       t.needarbitrageratio,
       t.goodsgroupid,
       e.enumdicname
  from erms_middlegoods t
  left join enumdicitem e
    on t.goodsunitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 where 1 = 1 and t.isvalid=1
`
	sqlId.AndEx("t.areauserid", r.AREAUSERID, r.AREAUSERID > 0)
	sqlId.AndEx("t.middlegoodsid", r.MIDDLEGOODSID, r.MIDDLEGOODSID > 0)
	return sqlId.String()
}

// GetDataEx 获取商品信息-套保品种
func (r *TBMiddleGoods) GetDataEx() (interface{}, error) {
	sData := make([]TBMiddleGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	if len(sData) > 0 {
		// 获取关联期货品种
		m1 := TBGoodsGroup{}
		if d1, err := m1.GetDataEx(); err == nil {
			gpLst := d1.([]TBGoodsGroup)
			for i := range sData {
				sData[i].addGpLst(gpLst)
			}
		}

		// 获取关联现货商品
		m2 := TBWrstandard{}
		if d2, err := m2.GetDataEx(); err == nil {
			wdLst := d2.([]TBWrstandard)
			for i := range sData {
				sData[i].addWdLst(wdLst)
			}
		}
	}
	return sData, err
}

func (r *TBMiddleGoods) addGpLst(lst []TBGoodsGroup) {
	for i := range lst {
		if r.GOODSGROUPID == lst[i].DESTGOODSGROUPID {
			r.GPLST = append(r.GPLST, lst[i])
		}
	}
}

func (r *TBMiddleGoods) addWdLst(lst []TBWrstandard) {
	for i := range lst {
		if r.MIDDLEGOODSID == lst[i].MIDDLEGOODSID {
			r.WDLST = append(r.WDLST, lst[i])
		}
	}
}

// TBGoodsGroup 套保品种/期货品种
type TBGoodsGroup struct {
	GOODSGROUPID     int64     `json:"goodsgroupid"  xorm:"'GOODSGROUPID'"`     // 商品组ID(自增ID)
	GOODSGROUPNAME   string    `json:"goodsgroupname"  xorm:"'GOODSGROUPNAME'"` // 商品组名称
	GOODUNITID       int32     `json:"goodunitid"  xorm:"'GOODUNITID'"`         // 报价单位ID
	CONVERTRATIO     float64   `json:"convertratio"  xorm:"'CONVERTRATIO'"`     // 折算系数
	DESTGOODSGROUPID int64     `json:"-"  xorm:"'DESTGOODSGROUPID'"`            // 目标商品组id
	EXEXCHANGECODE   string    `json:"exexchangecode"  xorm:"'EXEXCHANGECODE'"` // 交易所代码
	EXEXCHANGENAME   string    `json:"exexchangename"  xorm:"'EXEXCHANGENAME'"` // 交易所名称
	ENUMDICNAME      string    `json:"enumdicname"  xorm:"'ENUMDICNAME'"`       // 单位名称
	GDLST            []TBGoods // 商品列表
}

func (r *TBGoodsGroup) calc() {
	r.GDLST = make([]TBGoods, 0)
}

func (r *TBGoodsGroup) buildSql() string {
	var sqlId utils.SQLVal = `
select t.goodsgroupid,
       t.goodsgroupname,
       t.goodunitid,
       c.convertratio,
       c.destgoodsgroupid,
       ex.exexchangecode,
       ex.exexchangename,
       e.enumdicname
  from goodsgroup t
  inner join ermcp_ggconvertconfig c
    on t.goodsgroupid = c.srcgoodsgroupid
  left join externalexchange ex on t.exexchangeid=ex.autoid
  left join enumdicitem e
    on t.goodunitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 where 1 = 1 and t.goodsgroupstatus=1
`

	return sqlId.String()
}

// GetDataEx 获取套保品种/期货品种
func (r *TBGoodsGroup) GetDataEx() (interface{}, error) {
	sData := make([]TBGoodsGroup, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	if len(sData) > 0 {
		// 获取关联商品
		m := TBGoods{}
		if d, err := m.GetDataEx(); err == nil {
			gdLst := d.([]TBGoods)
			for i := range sData {
				sData[i].addGdLst(gdLst)
			}
		}
	}
	return sData, err
}

func (r *TBGoodsGroup) addGdLst(lst []TBGoods) {
	for i := range lst {
		if r.GOODSGROUPID == lst[i].GOODSGROUPID {
			r.GDLST = append(r.GDLST, lst[i])
		}
	}
}

// TBGoods 套保品种/期货品种/期货合约
type TBGoods struct {
	GOODSID      int64   `json:"goodsid"  xorm:"'GOODSID'"`         // 商品ID(自增ID SEQ_GOODS)
	GOODSCODE    string  `json:"goodscode"  xorm:"'GOODSCODE'"`     // 商品代码(内部)
	GOODSNAME    string  `json:"goodsname"  xorm:"'GOODSNAME'"`     // 商品名称
	GOODSGROUPID int64   `json:"-"  xorm:"'GOODSGROUPID'"`          // 所属商品组ID
	AGREEUNIT    float64 `json:"agreeunit"  xorm:"'AGREEUNIT'"`     // 合约乘数
	ENUMDICNAME  string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"` // 单位名称
}

func (r *TBGoods) calc() {

}

func (r *TBGoods) buildSql() string {
	var sqlId utils.SQLVal = `
select t.goodsid,
       t.goodscode,
       t.goodsname,
       t.goodsgroupid,
       t.agreeunit,
       e.enumdicname
  from goods t
  left join enumdicitem e
    on t.goodunitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 where 1 = 1
   and t.goodsstatus not in (4, 5, 6)
   and t.goodsgroupid in
       (select srcgoodsgroupid from ermcp_ggconvertconfig)
`

	return sqlId.String()
}

// GetDataEx 获取套保品种/期货品种/期货合约
func (r *TBGoods) GetDataEx() (interface{}, error) {
	sData := make([]TBGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// TBWrstandard 套保品种/商品信息(现货商品)
type TBWrstandard struct {
	WRSTANDARDID      int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`           // 现货商品ID(SEQ_WRSTANDARD)
	WRSTANDARDCODE    string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`       // 现货商品代码
	WRSTANDARDNAME    string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'"`       // 现货商品名称
	DELIVERYGOODSID   int64   `json:"-"  xorm:"'DELIVERYGOODSID'"`                   // 品种ID
	VATRATE           float64 `json:"vatrate"  xorm:"'VATRATE'"`                     // 增值税率
	DELIVERYGOODSNAME string  `json:"deliverygoodsname"  xorm:"'DELIVERYGOODSNAME'"` // 品种名称
	CONVERTRATIO      float64 `json:"convertratio"  xorm:"'CONVERTRATIO'"`           // 折算系数
	MIDDLEGOODSID     int64   `json:"-"  xorm:"'MIDDLEGOODSID'"`                     // 品种id
	ENUMDICNAME       string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`             // 单位名称
}

func (r *TBWrstandard) calc() {

}

func (r *TBWrstandard) buildSql() string {
	var sqlId utils.SQLVal = `
select t.wrstandardid,
       t.wrstandardcode,
       t.wrstandardname,
       t.deliverygoodsid,
       t.vatrate,
       g.deliverygoodsname,
       c.convertratio,
       c.middlegoodsid,
       e.enumdicname
  from wrstandard t
  left join deliverygoods g
    on t.deliverygoodsid = g.deliverygoodsid
 inner join erms2_wrsconvertdetail c
    on t.wrstandardid = c.wrstandardid
  left join enumdicitem e
    on t.unitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 where 1 = 1 and t.isvalid=1
 order by c.middlegoodsid
`
	return sqlId.String()
}

// GetDataEx 获取套保品种/商品信息(现货商品)
func (r *TBWrstandard) GetDataEx() (interface{}, error) {
	sData := make([]TBWrstandard, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3PaTradeLink 期现单据关联
type Ermcp3PaTradeLink struct {
	ORDERTYPE         int32   `json:"ordertype"  xorm:"ORDERTYPE"`                  // 单据类型 - 1:期货单 2:看涨期权单 3:看跌期权单
	OPTIONCODE        string  `json:"optioncode"  xorm:"OPTIONCODE"`                // 期权代码 [期权]
	PREMIUM           float64 `json:"premium"  xorm:"PREMIUM"`                      // 权利金 [期权]
	CHARGE            float64 `json:"charge"  xorm:"CHARGE"`                        // 手续费
	CLOSEPL           float64 `json:"closepl"  xorm:"CLOSEPL"`                      // 平仓盈亏
	EXECUTEPRICE      int32   `json:"executeprice"  xorm:"EXECUTEPRICE"`            // 执行价 [期权]
	TRADEID           string  `json:"tradeid"  xorm:"TRADEID" form:"tradeid"`       // 期货单号 | db:成交单号(108+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx)
	BUYORSELL         int32   `json:"buyorsell"  xorm:"BUYORSELL"`                  // 方向 - 0:买 1:卖
	TRADEDATE         string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"` // 交易日(yyyyMMdd)
	ACCOUNTID         int64   `json:"accountid"  xorm:"ACCOUNTID" form:"accountid"` // 期货账户代码 | 账号ID
	GOODSID           int32   `json:"goodsid"  xorm:"GOODSID" form:"goodsid"`       // 商品ID
	MARKETID          int32   `json:"marketid"  xorm:"MARKETID" form:"marketid"`    // 市场ID
	TRADETIME         string  `json:"tradetime"  xorm:"TRADETIME"`                  // 时间 | db:成交时间
	TRADEPRICE        float64 `json:"tradeprice"  xorm:"TRADEPRICE"`                // 成交价格
	TRADELOT          int32   `json:"tradelot"  xorm:"TRADELOT"`                    // 成交数量(期货数量) ps:这是手数,转换为具体多少吨要乘以AGREEUNIT | db:成交手数
	TRADEAMOUNT       float64 `json:"tradeamount"  xorm:"TRADEAMOUNT"`              // 成交金额(账户)
	TRADETYPE         int32   `json:"tradetype"  xorm:"TRADETYPE"`                  // 成交类别 - 1:正常委托成交 2:风控斩仓成交 3:修正持仓成交 4:管理端斩仓成交
	CHANNELBUILDTYPE  int32   `json:"channelbuildtype"  xorm:"CHANNELBUILDTYPE"`    // 委托单据类型 0:无 1:建仓 2:平仓
	CLOSETYPE         int32   `json:"closetype"  xorm:"CLOSETYPE"`                  // 平仓方式 - 0:无 1:平今 2:平昨
	HEDGEFLAG         int32   `json:"hedgeflag"  xorm:"HEDGEFLAG"`                  // 投机套保标志 - 0:无 1:投机 2:套保 3:套利 4:套期保值(平安\合同) 5:单边(平安) 6:移仓(平安) 7:错单处理(平安) 8:跨期套利(平安) 9:套期保值(计划) 10:套利(合同)  11:换月(合同) 12:交割(金瑞)
	PARENTACCOUNTID   int64   `json:"parentaccountid"  xorm:"PARENTACCOUNTID"`      // 所属母账户
	RELATEDOUTTRADEID string  `json:"relatedouttradeid"  xorm:"RELATEDOUTTRADEID"`  // 关联外部成交单ID
	SPOTCONTRACTID    string  `json:"spotcontractid"  xorm:"SPOTCONTRACTID"`        // 现货合同ID  [4:套期保值\10\11]
	GOODSGROUPID      int32   `json:"goodsgroupid"  xorm:"GOODSGROUPID"`            // 对冲品种ID[期货合约商品组ID]
	AGREEUNIT         float64 `json:"agreeunit"  xorm:"AGREEUNIT"`                  // 合约乘数
	MIDDLEGOODSID     int64   `json:"middlegoodsid"  xorm:"MIDDLEGOODSID"`          // 套保品种ID
	CONVERTRATIO      float64 `json:"convertratio"  xorm:"CONVERTRATIO"`            // 折算系数
	MIDDLEGOODSQTY    float64 `json:"middlegoodsqty"  xorm:"MIDDLEGOODSQTY"`        // 套保品种数量 = TradeQty * AgreeUnit * ConvertRatio
	RELATEDQTY        float64 `json:"relatedqty"  xorm:"RELATEDQTY"`                // 关联数量
	RELATEDLOT        float64 `json:"relatedlot"  xorm:"RELATEDLOT"`                // 已关联数量(已关联数量手) | db:关联手数
	AREAUSERID        string  `json:"userid"  xorm:"AREAUSERID" form:"userid"`      // 所属机构 - 所属母账户的UserID

	GOODSCODE     string `json:"goodscode"  xorm:"'GOODSCODE'"`         // 合约代码
	GOODSNAME     string `json:"goodsname"  xorm:"'GOODSNAME'"`         // 合约名称
	GOODUNITID    int32  `json:"goodunitid"  xorm:"'GOODUNITID'"`       // 单位id
	USERNAME      string `json:"username"  xorm:"'USERNAME'"`           // 用户名称
	TANAME        string `json:"taname"  xorm:"'TANAME'"`               // 期货账户名称
	ENUMDICNAME   string `json:"enumdicname"  xorm:"'ENUMDICNAME'"`     // 单位名称
	ORDERTYPENAME string `json:"ordertypename"  xorm:"'ORDERTYPENAME'"` // 订单类型
}

func (r *Ermcp3PaTradeLink) calc() {
	var str1, str2 string
	if r.BUYORSELL == 0 {
		str1 = "买入"
	} else {
		str1 = "卖出"
	}
	if r.CHANNELBUILDTYPE == 1 {
		str2 = "建仓"
	} else {
		str2 = "平仓"
	}

	r.ORDERTYPENAME = str1 + str2
}

func (r *Ermcp3PaTradeLink) buildSql() string {
	var sqlId utils.SQLVal = `
SELECT t.ORDERTYPE,
       t.OPTIONCODE,
       t.PREMIUM,
       t.CHARGE,
       t.CLOSEPL,
       t.EXECUTEPRICE,
       to_char(t.TRADEID) TRADEID,
       t.BUYORSELL,
       t.TRADEDATE,
       t.ACCOUNTID,
       t.GOODSID,
       t.MARKETID,
       to_char(t.TRADETIME, 'yyyy-mm-dd hh24:mi:ss') TRADETIME,
       t.TRADEPRICE,
       t.TRADELOT,
       t.TRADEAMOUNT,
       t.TRADETYPE,
       t.CHANNELBUILDTYPE,
       t.CLOSETYPE,
       t.HEDGEFLAG,
       t.PARENTACCOUNTID,
       to_char(t.RELATEDOUTTRADEID) RELATEDOUTTRADEID,
       t.SPOTCONTRACTID,
       t.GOODSGROUPID,
       t.AGREEUNIT,
       t.MIDDLEGOODSID,
       t.CONVERTRATIO,
       t.MIDDLEGOODSQTY,
       t.RELATEDQTY,
       t.RELATEDLOT,
       t.AREAUSERID,
       g.goodscode,
       g.goodsname,
       g.goodunitid,
       u.accountname username,
       ta.accountname taname,
       e.enumdicname
  FROM ERMCP_PA_TRADELINK t
  LEFT JOIN goods g
    on t.goodsid = g.goodsid
  LEFT JOIN taaccount ta
    on t.accountid = ta.accountid
  LEFT JOIN useraccount u
    on ta.relateduserid = u.userid
  LEFT JOIN enumdicitem e
    on g.goodunitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 WHERE 1 = 1
`
	sqlId.JoinFormat(" and %v in (ta.relateduserid, ta.userid)", r.AREAUSERID)
	return sqlId.String()
}

// GetDataEx 获取期现单据关联
func (r *Ermcp3PaTradeLink) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3PaTradeLink, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3SCMiddleGoods 业务关联现货订单
type Ermcp3SCMiddleGoods struct {
	USERID                int64   `json:"userid"  xorm:"'USERID'" form:"userid"`                         // 用户id
	CUSTOMERUSERID        int64   `json:"customeruserid"  xorm:"'CUSTOMERUSERID'"`                       // 对手id
	CUSTOMERNAME          string  `json:"customername"  xorm:"'CUSTOMERNAME'" form:"customername"`       // 对手手名称
	CONTRACTNO            string  `json:"contractno"  xorm:"'CONTRACTNO'" form:"contractno"`             // 合同编号
	SPOTCONTRACTID        string  `json:"spotcontractid"  xorm:"'SPOTCONTRACTID'"`                       // 合同编号
	WRSTANDARDID          int64   `json:"wrstandardid"  xorm:"'WRSTANDARDID'"`                           // 现货商品id
	WRSTANDARDCODE        string  `json:"wrstandardcode"  xorm:"'WRSTANDARDCODE'"`                       // 现货商品代码
	WRSTANDARDNAME        string  `json:"wrstandardname"  xorm:"'WRSTANDARDNAME'" form:"wrstandardname"` // 现货商品名称
	UNITID                int32   `json:"unitid"  xorm:"'UNITID'"`                                       // 现货单位id
	VATRATE               float64 `json:"vatrate"  xorm:"'VATRATE'"`                                     // 增值税率
	MIDDLEGOODSPRICEDQTY3 float64 `json:"middlegoodspricedqty3"  xorm:"'MIDDLEGOODSPRICEDQTY3'"`         // 应套保数量 = MiddleGoodsPricedQty2 *  套保比率
	PRICEDQTY             float64 `json:"pricedqty"  xorm:"'PRICEDQTY'"`                                 // 已关联数量 | 已定价量
	MIDDLEGOODSID         int64   `json:"middlegoodsid"  xorm:"'MIDDLEGOODSID'"`                         // 套保品种ID
	NEEDHEDGERATIO        float64 `json:"needhedgeratio"  xorm:"'NEEDHEDGERATIO'"`                       // 套保比例
	CONTRACTTYPE          int     `json:"contracttype"  xorm:"'CONTRACTTYPE'"`                           // 合同类型 1:采购 -1:销售

	BUYORSELL    int32 `json:"-" form:"buyorsell"`    // 买卖方向, 用作筛选
	GOODSGROUPID int32 `json:"-" form:"goodsgroupid"` // 期货商品组id, 用作筛选
}

func (r *Ermcp3SCMiddleGoods) calc() {

}

func (r *Ermcp3SCMiddleGoods) buildSql() string {
	var sqlId utils.SQLVal = `
with tmp as
 (select k.configid,
         k.srcgoodsgroupid,
         k.destgoodsgroupid,
         k.convertratio,
         m.middlegoodsid
    from ermcp_ggconvertconfig k
   inner join erms_middlegoods m
      on k.destgoodsgroupid = m.goodsgroupid)
select s.userid,
       s.customeruserid,
       u1.accountname customername,
       s.contractno,
       s.contracttype,
       to_char(s.spotcontractid) spotcontractid,
       w.wrstandardid,
       w.wrstandardcode,
       w.wrstandardname,
       w.unitid,
       w.vatrate,
       t.middlegoodspricedqty3,
       t.pricedqty,
       t.middlegoodsid,
       mg.needhedgeratio
  from ermcp_scmiddlegoods t
 inner join ermcp_spotcontract s
    on t.spotcontractid = s.spotcontractid
  left join wrstandard w
    on t.wrstandardid = w.wrstandardid
  left join erms_middlegoods mg
    on t.middlegoodsid = mg.middlegoodsid
  left join useraccount u1
    on s.customeruserid = u1.userid
  left join enumdicitem e on w.unitid=e.enumitemname and e.enumdiccode='goodsunit'
 where 1 = 1 and t.middlegoodspricedqty3 > t.pricedqty
   and t.middlegoodsid in
       (select middlegoodsid from tmp where tmp.srcgoodsgroupid = %v)
`
	sqlId.FormatParam(r.GOODSGROUPID)
	if r.BUYORSELL == 0 {
		r.CONTRACTTYPE = -1
	} else {
		r.CONTRACTTYPE = 1
	}
	sqlId.And("s.CONTRACTTYPE", r.CONTRACTTYPE)
	sqlId.JoinFormat(" and %v in(s.userid, s.tradeuserid, s.saleuserid, s.tradeuserid)", r.USERID)
	sqlId.AndLike("s.contractno", r.CONTRACTNO)
	sqlId.AndLike("u1.accountname", r.CUSTOMERNAME)
	sqlId.AndLike("w.wrstandardname", r.WRSTANDARDNAME)

	return sqlId.String()
}

// GetDataEx 获取业务关联现货订单
func (r *Ermcp3SCMiddleGoods) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3SCMiddleGoods, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3TradeLinkDetail 期现关联记录
type Ermcp3TradeLinkDetail struct {
	LINKDETAILID   string  `json:"linkdetailid"  xorm:"LINKDETAILID" form:"linkdetailid"`       // 关联ID(614+Unix秒时间戳(10位)+xxxxxx)
	SPOTCONTRACTID string  `json:"spotcontractid"  xorm:"SPOTCONTRACTID" form:"spotcontractid"` // 现货合同\套保计划ID [4,10,11 - 现货合同ID]  [9:套期保值(计划) - 套保计划ID]
	LINKSTATUS     int32   `json:"linkstatus"  xorm:"LINKSTATUS"`                               // 关联状态 - 1:成功 2:失败
	RETCODE        int32   `json:"retcode"  xorm:"RETCODE"`                                     // 返回码
	TRADEID        string  `json:"tradeid"  xorm:"TRADEID" form:"tradeid"`                      // 期货订单号 | 内部成交单ID
	BUYORSELL      int32   `json:"buyorsell"  xorm:"BUYORSELL"`                                 // 方向 - 0:买 1:卖
	HEDGEFLAG      int32   `json:"hedgeflag"  xorm:"HEDGEFLAG"`                                 // 业务类型 | 投机套保标志 - 0:无 1:投机 2:套保 3:套利 4:套期保值(平安\合同) 5:单边(平安) 6:移仓(平安) 7:错单处理(平安) 8:跨期套利(平安) 9:套期保值(计划) 10:套利(合同)  11:换月(合同)  12:交割(金瑞)
	RELATEDLOT     float64 `json:"relatedlot"  xorm:"RELATEDLOT"`                               // 关联手数
	GOODSID        int32   `json:"goodsid"  xorm:"GOODSID" form:"goodsid"`                      // 商品ID
	GOODSGROUPID   int32   `json:"goodsgroupid"  xorm:"GOODSGROUPID" form:"goodsgroupid"`       // 对冲品种ID[期货合约商品组ID]
	MIDDLEGOODSID  int64   `json:"middlegoodsid"  xorm:"MIDDLEGOODSID" form:"middlegoodsid"`    // 套保品种ID
	RELATEDQTY     float64 `json:"relatedqty"  xorm:"RELATEDQTY"`                               // 关联数量 = RelatedLot * AgreeUnit * ConvertRatio
	TRADEDATE      string  `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`                // 关联交易日(yyyyMMdd)
	RELATEDMODE    int32   `json:"relatedmode"  xorm:"RELATEDMODE"`                             // 关联方式 - 1:自动关联 2:手动关联 3:解绑关联
	TRADETRADEDATE string  `json:"tradetradedate"  xorm:"TRADETRADEDATE" form:"tradetradedate"` // 成交交易日(yyyyMMdd)
	TRADEPRICE     float64 `json:"tradeprice"  xorm:"TRADEPRICE"`                               // 成交价格
	TRADELOT       int32   `json:"tradelot"  xorm:"TRADELOT"`                                   // 成交数量 | 成交手数
	SALEUSERID     string  `json:"saleuserid"  xorm:"SALEUSERID" form:"saleuserid"`             // 业务员ID
	BIZSUBJECTID   string  `json:"bizsubjectid"  xorm:"BIZSUBJECTID" form:"bizsubjectid"`       // 归属业务部门ID - SubjectID
	AGREEUNIT      float64 `json:"agreeunit"  xorm:"AGREEUNIT"`                                 // 合约乘数
	CONVERTRATIO   float64 `json:"convertratio"  xorm:"CONVERTRATIO"`                           // 折算系数
	AREAUSERID     string  `json:"areauserid"  xorm:"AREAUSERID" form:"userid"`                 // 所属机构
	CREATORSRC     int32   `json:"creatorsrc"  xorm:"CREATORSRC"`                               // 创建人来源 - 1:管理端 2:终端 3:交易
	CREATORID      string  `json:"creatorid"  xorm:"CREATORID" form:"creatorid"`                // 创建人ID
	CREATETIME     string  `json:"createtime"  xorm:"CREATETIME"`                               // 关联时间 | 创建时间

	ACCOUNTID        int64  `json:"accountid"  xorm:"'ACCOUNTID'"`                     // 资金账号
	TRADETIME        string `json:"tradetime"  xorm:"'TRADETIME'"`                     // 期货成交时间
	CONTRACTNO       string `json:"contractno"  xorm:"'CONTRACTNO'" form:"contractno"` // 现货编号 (合同编号)
	TANAME           string `json:"taname"  xorm:"'TANAME'" form:"taname"`             // 期货账户
	USERNAME         string `json:"username"  xorm:"'USERNAME'"`                       // 用户名称
	GOODSCODE        string `json:"goodscode"  xorm:"'GOODSCODE'"`                     // 合约代码
	GOODSNAME        string `json:"goodsname"  xorm:"'GOODSNAME'"`                     // 合约名称
	ENUMDICNAME      string `json:"enumdicname"  xorm:"'ENUMDICNAME'"`                 // 单位名称
	CHANNELBUILDTYPE int32  `json:"channelbuildtype"  xorm:"'CHANNELBUILDTYPE'"`       // 委托单据类型 0:无 1:建仓 2:平仓
}

func (r *Ermcp3TradeLinkDetail) calc() {

}

func (r *Ermcp3TradeLinkDetail) buildSql() string {
	var sqlId utils.SQLVal = `
select t.LINKSTATUS,
       t.RETCODE,
       to_char(t.LINKDETAILID) LINKDETAILID,
       to_char(t.TRADEID) TRADEID,
       t.BUYORSELL,
       t.HEDGEFLAG,
       t.SPOTCONTRACTID,
       t.RELATEDLOT,
       t.GOODSID,
       t.GOODSGROUPID,
       t.MIDDLEGOODSID,
       t.RELATEDQTY,
       t.TRADEDATE,
       t.RELATEDMODE,
       t.TRADETRADEDATE,
       t.TRADEPRICE,
       t.TRADELOT,
       t.SALEUSERID,
       t.BIZSUBJECTID,
       t.AGREEUNIT,
       t.CONVERTRATIO,
       t.AREAUSERID,
       t.CREATORSRC,
       t.CREATORID,
       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME,
       k.accountid,
       to_char(k.tradetime, 'yyyy-mm-dd hh24:mi:ss') tradetime,
       s.contractno,
       ta.accountname taname,
       u.accountname username,
       g.goodscode,
       g.goodsname,
       g.agreeunit,
       e.enumdicname,
       k.channelbuildtype
  from ERMCP_PA_TradeLinkDetail t
 inner join ermcp_pa_tradelink k
    on t.tradeid = k.tradeid
   and t.buyorsell = k.buyorsell
 inner join ermcp_spotcontract s
    on t.spotcontractid = s.spotcontractid
  left join taaccount ta
    on k.accountid = ta.accountid
  left join useraccount u
    on ta.relateduserid = u.userid
  left join goods g
    on t.goodsid = g.goodsid
  left join enumdicitem e
    on g.goodunitid = e.enumitemname
   and e.enumdiccode = 'goodsunit'
 where 1 = 1
`
	sqlId.JoinFormat(" and %v in (ta.relateduserid, ta.userid)", r.AREAUSERID)
	sqlId.AndLike("t.TRADEID", r.TRADEID)
	sqlId.AndLike("s.contractno", r.CONTRACTNO)
	sqlId.AndLike("ta.accountname", r.TANAME)
	return sqlId.String()
}

// GetDataEx 获取期现关联记录
func (r *Ermcp3TradeLinkDetail) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3TradeLinkDetail, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3OutTradeLink 外部成交单关联
type Ermcp3OutTradeLink struct {
	GOODSID          int32   `json:"goodsid"  xorm:"'GOODSID'"`                                  // 合约id
	GOODSCODE        string  `json:"goodscode"  xorm:"'GOODSCODE'" form:"goodscode"`             // 合约代码
	GOODSNAME        string  `json:"goodsname"  xorm:"'GOODSNAME'" form:"goodsname"`             // 合约名称
	TRADETIME        string  `json:"tradetime"  xorm:"'TRADETIME'"`                              // 成交时间
	HEDGEACCOUNTCODE string  `json:"hedgeaccountcode"  xorm:"'HEDGEACCOUNTCODE'"`                // 外部账号 | 对冲账号
	BUYORSELL        int32   `json:"buyorsell"  xorm:"'BUYORSELL'"`                              // 买卖方向 - 0:买 1:卖
	TRADEQTY         int64   `json:"tradeqty"  xorm:"'TRADEQTY'"`                                // 成交数量
	TRADEPRICE       float64 `json:"tradeprice"  xorm:"'TRADEPRICE'"`                            // 成交价格
	HASINNERORDER    int32   `json:"hasinnerorder"  xorm:"'HASINNERORDER'" form:"hasinnerorder"` // 是否有内部单关联 - 0:无 1:有
	CHANNELBUILDTYPE int32   `json:"channelbuildtype"  xorm:"'CHANNELBUILDTYPE'"`                // 开平标志- 0:无 1:建仓 2:平仓
	OUTTRADEID       string  `json:"outtradeid"  xorm:"'OUTTRADEID'" form:"outtradeid"`          // 外部订单号 | 外部成交单ID(114+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx)
	EXEXCHANGECODE   string  `json:"exexchangecode"  xorm:"'EXEXCHANGECODE'"`                    // 交易所代码
	EXEXCHANGENAME   string  `json:"exexchangename"  xorm:"'EXEXCHANGENAME'"`                    // 交易所名称
	CURRENCYID       int32   `json:"-"  xorm:"'CURRENCYID'"`                                     // 币种id
	CURRENCYNAME     string  `json:"currencyname"  xorm:"'CURRENCYNAME'"`                        // 币种名称

	USERID int64 `json:"-" form:"userid"` // 用户id, 用于过滤
}

func (r *Ermcp3OutTradeLink) calc() {

}

func (r *Ermcp3OutTradeLink) buildSql() string {
	var sqlId utils.SQLVal = `
select g.goodsid,
       g.goodscode,
       g.goodsname,
       to_char(t.tradetime, 'yyyy-mm-dd hh24:mi:ss') tradetime,
       c.hedgeaccountcode,
       t.buyorsell,
       t.tradeqty,
       t.tradeprice,
       t.hasinnerorder,
       t.channelbuildtype,
       to_char(t.outtradeid) outtradeid,
       ex.exexchangecode,
       ex.exexchangename,
       ta.currencyid,
       e.enumdicname currencyname
  from hedge_outtradedetail t
 inner join goods g
    on t.hedgegoodsid = g.goodsid
  left join goodsgroup gp
    on g.goodsgroupid = gp.goodsgroupid
  left join externalexchange ex
    on gp.exexchangeid = ex.autoid
  left join hedge_outmainconfig c
    on t.accountid = c.accountid
  left join taaccount ta
    on t.accountid = ta.accountid
  left join enumdicitem e
    on ta.currencyid = e.enumitemname
   and e.enumdiccode = 'currency'
 where 1 = 1
   and %v in (ta.userid, ta.relateduserid)
`
	sqlId.FormatParam(r.USERID)
	sqlId.AndEx("t.hasinnerorder", r.HASINNERORDER, r.HASINNERORDER != -1)
	sqlId.AndLike("t.outtradeid", r.OUTTRADEID)
	sqlId.AndLike("g.goodscode||g.goodsname", r.GOODSNAME)
	sqlId.Join(" order by t.tradetime desc")
	return sqlId.String()
}

// GetDataEx 获取外部成交单关联
func (r *Ermcp3OutTradeLink) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3OutTradeLink, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3SubTaaccount 期货子账户
type Ermcp3SubTaaccount struct {
	ACCOUNTID         int64  `json:"accountid"  xorm:"'ACCOUNTID'"`                                          // 资金账户ID
	TANAME            string `json:"taname"  xorm:"'TANAME'"`                                                // 资金账号名称
	TRADESTATUS       int32  `json:"tradestatus"  xorm:"'TRADESTATUS'"`                                      // 交易状态 - 1:正常 2:受限 3:冻结(停用) 4:禁止建仓(人工受限) 5:禁止交易(人工冻结) 6:待激活 7:已删除(注销)
	PARENTACCOUNTID   int64  `json:"parentaccountid"  xorm:"'PARENTACCOUNTID'" form:"parentaccountid"`       // 所属根账号(母账号)
	PARENTACCOUNTCODE string `json:"parentaccountcode"  xorm:"'PARENTACCOUNTCODE'" form:"parentaccountcode"` // 所属根账号代码(母账号代码)
	USERNAME          string `json:"username"  xorm:"'USERNAME'"`                                            // 交易用户
}

func (r *Ermcp3SubTaaccount) calc() {

}

func (r *Ermcp3SubTaaccount) buildSql() string {
	var sqlId utils.SQLVal = `
select t.accountid,
       t.accountname      taname,
       t.tradestatus,
       t.parentaccountid,
       c.hedgeaccountcode parentaccountcode,
       u.accountname      username
  from taaccount t
 inner join hedge_outmainconfig c
    on t.parentaccountid = c.accountid
  left join useraccount u
    on t.relateduserid = u.userid
 where 1 = 1 and t.ismain=0
`
	sqlId.AndEx("t.parentaccountid", r.PARENTACCOUNTID, r.PARENTACCOUNTID > 0)
	sqlId.AndEx("c.hedgeaccountcode", r.PARENTACCOUNTCODE, r.PARENTACCOUNTCODE != "")
	return sqlId.String()
}

// GetDataEx 获取期货子账户
func (r *Ermcp3SubTaaccount) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3SubTaaccount, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// Ermcp3InnerTradeLink 外部成交关联的内部成交单
type Ermcp3InnerTradeLink struct {
	OUTTRADEID       string  `json:"outtradeid"  xorm:"'OUTTRADEID'" form:"outtradeid"` // 外部成交单号
	TRADETIME        string  `json:"tradetime"  xorm:"'TRADETIME'"`                     // 关联时间 | 成交时间 ps:因为数据库表里没有关联时间, 所以取外部成交单的成交时间
	TRADEID          string  `json:"tradeid"  xorm:"'TRADEID'"`                         // 期货单号 | 内部成交单号(108+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx)
	ACCOUNTID        int64   `json:"accountid"  xorm:"'ACCOUNTID'"`                     // 期货账号代码 | 资金账号ID
	TANAME           string  `json:"taname"  xorm:"'TANAME'"`                           // 期货账户名称
	USERNAME         string  `json:"username"  xorm:"'USERNAME'"`                       // 用户名称
	GOODSID          int64   `json:"goodsid"  xorm:"'GOODSID'"`                         // 商品ID
	GOODSCODE        string  `json:"goodscode"  xorm:"'GOODSCODE'"`                     // 商品代码
	GOODSNAME        string  `json:"goodsname"  xorm:"'GOODSNAME'"`                     // 商品名称
	BUYORSELL        int32   `json:"buyorsell"  xorm:"'BUYORSELL'"`                     // 方向 - 0:买 1:卖
	CHANNELBUILDTYPE int32   `json:"channelbuildtype"  xorm:"'CHANNELBUILDTYPE'"`       // 委托单据类型 0:无 1:建仓 2:平仓
	TRADEQTY         int64   `json:"tradeqty"  xorm:"'TRADEQTY'"`                       // 期货成交数量
	TRADEPRICE       float64 `json:"tradeprice"  xorm:"'TRADEPRICE'"`                   // 成交价格
	CURRENCYID       int32   `json:"currencyid"  xorm:"'CURRENCYID'"`                   // 币种id
	CURRENCYNAME     string  `json:"currencyname"  xorm:"'CURRENCYNAME'"`               // 结算币种 名称
}

func (r *Ermcp3InnerTradeLink) calc() {

}

func (r *Ermcp3InnerTradeLink) buildSql() string {
	var sqlId utils.SQLVal = `
select to_char(k.outtradeid) outtradeid,
       t.tradetime,
       to_char(t.tradeid) tradeid,
       t.accountid,
       ta.accountname taname,
       u.accountname username,
       t.goodsid,
       g.goodscode,
       g.goodsname,
       t.buyorsell,
       t.channelbuildtype,
       t.tradeqty,
       t.tradeprice,
       ta.currencyid,
       e.enumdicname currencyname
  from hedge_innertradedetail t
 inner join hedge_outtradedetail k
    on k.mhpinnertradeid = t.tradeid
  left join taaccount ta
    on t.accountid = ta.accountid
  left join useraccount u
    on ta.relateduserid = u.userid
  left join goods g
    on t.goodsid = g.goodsid
  left join enumdicitem e
    on ta.currencyid = e.enumitemname
   and e.enumdiccode = 'currency'
 where 1 = 1
`
	sqlId.AndEx("k.outtradeid", r.OUTTRADEID, r.OUTTRADEID != "")
	return sqlId.String()
}

// GetDataEx 获取外部成交关联的内部成交单
func (r *Ermcp3InnerTradeLink) GetDataEx() (interface{}, error) {
	sData := make([]Ermcp3InnerTradeLink, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}