MTP20_IF/models/qhjPCWeb.go

/**
* @Author: zou.yingbin
* @Create  : 2021/6/24 10:07
* @Modify  : 2021/6/24 10:07
 */

package models

import (
	"fmt"
	"mtp2_if/db"
	"mtp2_if/mtpcache"
	"mtp2_if/utils"
)

// QhjMgrSubArea 子机构列表
type QhjMgrSubArea struct {
	PARENTTOPUSER    string `json:"parenttopuser"  xorm:"PARENTTOPUSER"`       // 上级顶级机构 [092=0,1时，默认为1， 092=2时若自已为顶级，则填入自己，自己不为顶级，填入ParentUserID的"ParentTopUser"]
	SUBACCOUNTLEVEL  int32  `json:"subaccountlevel"  xorm:"SUBACCOUNTLEVEL"`   // 子账户层数
	ROOTUSERID       string `json:"rootuserid"  xorm:"ROOTUSERID"`             // 根用户ID
	USERID           int64  `json:"userid"  xorm:"USERID"`                     // 用户ID
	USERTYPE         int32  `json:"usertype"  xorm:"USERTYPE"`                 // 账户类型 -  1:交易所 2:机构 3:会员子机构 4:经纪人 5:投资者 6:客户 7:企业成员(云平台)
	ACCOUNTNAME      string `json:"accountname"  xorm:"ACCOUNTNAME"`           // 账户名称(机构名称)
	PARENTUSERID     int64  `json:"parentuserid"  xorm:"PARENTUSERID"`         // 所属机构ID
	SUBAREALEVELPATH string `json:"subarealevelpath"  xorm:"SUBAREALEVELPATH"` // 子机构层级路径(逗号分隔,首尾加逗号)
	MEMBERUSERID     int64  `json:"memberuserid"  xorm:"MEMBERUSERID"`         // 所属会员ID
}

func (r *QhjMgrSubArea) calc() {

}

func (r *QhjMgrSubArea) buildSql() string {
	var sqlId utils.SQLVal = "select t.userid," +
		"       t.accountname," +
		"       t.parentuserid," +
		"       t.rootuserid," +
		"       t.memberuserid," +
		"       t.parenttopuser," +
		"       t.subarealevelpath," +
		"       t.subaccountlevel," +
		"       t.usertype" +
		"  from useraccount t" +
		" where 1 = 1"
	sqlId.And("t.usertype", 2)
	sqlId.Join(fmt.Sprintf("and t.userid != %v", r.USERID))
	sqlId.Join(fmt.Sprintf(" and t.subarealevelpath like ',%%%v%%,'", r.USERID))
	return sqlId.String()
}

// GetDataEx 获取子机构列表
func (r *QhjMgrSubArea) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrSubArea, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjMgrCustomer 客户资料
type QhjMgrCustomer struct {
	USERID            int64  `json:"userid"  xorm:"'USERID'"`                       // 用户ID
	MEMBERUSERID      int64  `json:"memberuserid"  xorm:"'MEMBERUSERID'"`           // 所属机构ID(所属会员)
	PARENTUSERID      int64  `json:"parentuserid"  xorm:"'PARENTUSERID'"`           // 上级机构(所属机构)
	USERINFOTYPE      int32  `json:"userinfotype"  xorm:"'USERINFOTYPE'"`           // 客户类型 1-个人 2-企业
	CUSTOMERNAME      string `json:"customername"  xorm:"'CUSTOMERNAME'"`           // 客户名称
	CARDTYPE          int32  `json:"cardtype"  xorm:"'CARDTYPE'"`                   // 证件类型
	CARDNUM           string `json:"cardnum"  xorm:"'CARDNUM'"`                     // 证件号码
	MOBILE            string `json:"mobile"  xorm:"'MOBILE'"`                       // 手机号码
	TELPHONE          string `json:"telphone"  xorm:"'TELPHONE'"`                   // 联系电话
	ADDRESS           string `json:"address"  xorm:"'ADDRESS'"`                     // 通讯地址
	REMARK            string `json:"remark"  xorm:"'REMARK'"`                       // 备注
	STATUS            int32  `json:"status"  xorm:"'STATUS'"`                       // 账户状态 -  1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:注销
	STATUSDESC        string `json:"statusdesc"`                                    // 账户状态中文描述
	ATTACHMENT1       string `json:"attachment1"  xorm:"'ATTACHMENT1'"`             // 附件1
	CARDFRONTPHOTOURL string `json:"cardfrontphotourl"  xorm:"'CARDFRONTPHOTOURL'"` // 证件正面图片地址
	CARDBACKPHOTOURL  string `json:"cardbackphotourl"  xorm:"'CARDBACKPHOTOURL'"`   // 证件反面图片地址
	COUNTRYID         int32  `json:"countryid"  xorm:"'COUNTRYID'"`                 // 国家
	PROVINCEID        int32  `json:"provinceid"  xorm:"'PROVINCEID'"`               // 省
	CITYID            int32  `json:"cityid"  xorm:"'CITYID'"`                       // 市
	DISTRICTID        int32  `json:"districtid"  xorm:"'DISTRICTID'"`               // 区域
	CREATETIME        string `json:"createtime"  xorm:"'CREATETIME'"`               // 创建时间
	MODIFYTIME        string `json:"modifytime"  xorm:"'MODIFYTIME'"`               // 修改时间
	AUDITTIME         string `json:"audittime"  xorm:"'AUDITTIME'"`                 // 审核时间
	NICKNAME          string `json:"nickname"  xorm:"'NICKNAME'"`                   // 昵称
	TAXPAYERNUM       string `json:"taxpayernum"  xorm:"'taxpayernum'"`             // 纳税人识别号
	LEGALPERSONNAME   string `json:"legalpersonname"  xorm:"'legalpersonname'"`     // 法人姓名(企业)
	CONTACTNAME       string `json:"contactname"  xorm:"'contactname'"`             // 联系人
	EMAIL             string `json:"email"  xorm:"'EMAIL'"`                         // 邮件
	USERNAME          string `json:"username"  xorm:"'USERNAME'"`                   // 用户名称
	LOGINCODE         string `json:"logincode"  xorm:"'LOGINCODE'"`                 // 登录账号(代码)

	COUNTRYNAME    string `json:"countryname"`    // 国家名称
	CITYNAME       string `json:"cityname"`       // 城市名称
	PROVINCENAME   string `json:"provincename"`   // 省名称
	DISTRICTNAME   string `json:"districtname"`   // 地区名称
	CARDTYPENAME   string `json:"cardtypename"`   // 证件类型名称
	MEMBERUSERNAME string `json:"memberusername"` // 所属会员名称
	PARENTUSERNAME string `json:"parentusername"` //所属机构名称

	QUERYTYPE  int32 `json:"-"` // 查询类型(1:未提交(网上开户表) 2:待审核(网上开户表) 3:正常 4:停用)
	IncludeSub int32 `json:"-"` // 包子机构客户 1-包含
}

func (r *QhjMgrCustomer) calc() {
	r.MEMBERUSERNAME = mtpcache.GetUserNameByUserId(r.MEMBERUSERID)
	if len(r.USERNAME) == 0 {
		r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	}
	r.PARENTUSERNAME = mtpcache.GetUserNameByUserId(r.PARENTUSERID)
	r.COUNTRYNAME = mtpcache.GetDivisionName(r.COUNTRYID)
	r.CITYNAME = mtpcache.GetDivisionName(r.CITYID)
	r.PROVINCENAME = mtpcache.GetDivisionName(r.PROVINCEID)
	r.DISTRICTNAME = mtpcache.GetDivisionName(r.DISTRICTID)

	switch r.QUERYTYPE {
	case 1, 2: // 网上开户表
		r.ADDRESS = DecryptField(r.ADDRESS)
		r.TELPHONE = DecryptField(r.TELPHONE)
	case 3, 4: // 正式表
		r.TELPHONE = DecryptField(r.TELPHONE)
	}
	r.CARDNUM = DecryptField(r.CARDNUM)
	r.MOBILE = DecryptField(r.MOBILE)
	r.EMAIL = DecryptField(r.EMAIL)
}

func (r *QhjMgrCustomer) level() int32 {
	if r.IncludeSub > 0 {
		// 层级, 最多20层
		return 20
	}
	return 1
}

func (r *QhjMgrCustomer) buildSql() string {
	if r.QUERYTYPE == 1 || r.QUERYTYPE == 2 {
		return r.buildSqlWskh()
	}
	return r.buildSqlNormal()
}

func (r *QhjMgrCustomer) buildSqlWskh() string {
	var sqlId utils.SQLVal = "select *" +
		"  from (select t.userid," +
		"               t.memberareaid MEMBERUSERID," +
		"               t.USERINFOTYPE," +
		"               t.username," +
		"               t.CUSTOMERNAME," +
		"               t.NICKNAME," +
		"               t.CARDTYPE," +
		"               t.CARDNUM," +
		"               t.mobilephone MOBILE," +
		"               t.TELPHONE," +
		"               t.cardaddress ADDRESS," +
		"               t.REMARK," +
		"               t.ATTACHMENT1," +
		"               t.CARDFRONTPHOTOURL," +
		"               t.CARDBACKPHOTOURL," +
		"               t.userstate status," +
		"               t.countryid," +
		"               t.provinceid," +
		"               t.districtid," +
		"               to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"               t.cityid," +
		"               to_char(t.modifiedtime, 'yyyy-mm-dd hh24:mi:ss') modifytime," +
		"               to_char(t.auditime, 'yyyy-mm-dd hh24:mi:ss') audittime," +
		"               t.taxpayernum," +
		"               t.legalpersonname," +
		"               t.contactname," +
		"               t.email," +
		"               t.areaid parentuserid," +
		"               t.logincode," +
		"               t.usertype" +
		"          from wskh_userinfo t" +
		"         start with t.areaid = %v" +
		"        connect by prior t.userid = t.areaid" +
		"               and t.usertype in (2, 6)" +
		"               and level = %v) a" +
		" where 1 = 1" +
		"   and a.usertype != 2" +
		"   and a.status in (%v)"
	status := DecodeStr(r.QUERYTYPE == 1, "1", "2,4,5")
	sqlId.FormatParam(r.USERID, r.level(), status)
	sqlId.AndEx("a.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0)
	sqlId.JoinEx(len(r.CUSTOMERNAME) > 0, fmt.Sprintf(` and a.customername like '%%%v%%'`, r.CUSTOMERNAME))
	sqlId.JoinEx(len(r.NICKNAME) > 0, fmt.Sprintf(` and a.nickname like '%%%v%%'`, r.NICKNAME))
	return sqlId.String()
}

func (r *QhjMgrCustomer) buildSqlNormal() string {
	var sqlId utils.SQLVal = "with tmp as" +
		" (select t.userid, wm_concat(t.logincode) logincode" +
		"    from loginaccount t" +
		"   group by t.userid)" +
		"select a.*," +
		"       k.logincode," +
		"       u.USERINFOTYPE," +
		"       u.CUSTOMERNAME," +
		"       u.NICKNAME," +
		"       u.CARDTYPEID CARDTYPE," +
		"       u.CARDNUM," +
		"       u.MOBILE," +
		"       u.TELPHONE," +
		"       u.ADDRESS," +
		"       u.REMARK," +
		"       u.ATTACHMENT1," +
		"       u.CARDFRONTPHOTOURL," +
		"       u.CARDBACKPHOTOURL," +
		"       u.countryid," +
		"       u.provinceid," +
		"       u.districtid," +
		"       u.taxpayernum," +
		"       u.legalpersonname," +
		"       u.contactname," +
		"       u.email," +
		"       u.cityid" +
		"  from (select t.userid," +
		"               t.usertype," +
		"               t.parentuserid," +
		"               t.memberuserid," +
		"               t.accountstatus status," +
		"               to_char(t.createtime, 'yyyy-mm-dd hh24:mi:ss') createtime," +
		"               to_char(t.modifytime, 'yyyy-mm-dd hh24:mi:ss') modifytime," +
		"               to_char(t.audittime, 'yyyy-mm-dd hh24:mi:ss') audittime" +
		"          from useraccount t where 1=1 and t.usertype = 6 %v" +
		"         ) a" +
		"  left join tmp k" +
		"    on a.userid = k.userid" +
		"  left join userinfo u" +
		"    on a.userid = u.userid" +
		" where 1 = 1"
	status := DecodeStr(r.QUERYTYPE == 3, "4", "6")
	var sqlParam utils.SQLVal
	sqlParam.And("t.accountstatus", status)
	if r.IncludeSub == 1 {
		sqlParam.Join(fmt.Sprintf(" and t.subarealevelpath like '%%,%v,%%'", r.USERID))
	} else {
		sqlParam.And("t.parentuserid", r.USERID)
	}
	sqlId.FormatParam(sqlParam.String())
	sqlId.AndEx("u.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0)
	sqlId.JoinEx(len(r.CUSTOMERNAME) > 0, fmt.Sprintf(` and u.customername like '%%%v%%'`, r.CUSTOMERNAME))
	sqlId.JoinEx(len(r.NICKNAME) > 0, fmt.Sprintf(` and u.nickname like '%%%v%%'`, r.NICKNAME))
	return sqlId.String()
}

// GetDataEx 获取客户资料
func (r *QhjMgrCustomer) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrCustomer, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].QUERYTYPE = r.QUERYTYPE
		sData[i].calc()
	}
	return sData, err
}

// QhjMgrAccountOutInApply 充值提现（出入金）
type QhjMgrAccountOutInApply struct {
	EXECUTETYPE         int32   `json:"executetype"  xorm:"EXECUTETYPE"`                 // 申请类型 - 1:出金 2:入金 3: 单边账调整:入金; 4:单边账调整:出金 5:外部母账户调整:入金 6:外部母账户调整:出金 7:外部子账户:入金 8:外部子账户:出金
	AMOUNT              float64 `json:"amount"  xorm:"AMOUNT"`                           // 金额
	CURRENCY            string  `json:"currency"  xorm:"CURRENCY"`                       // 币种
	CHARGE              float64 `json:"charge"  xorm:"CHARGE"`                           // 手续费
	ACCOUNTPWD          string  `json:"accountpwd"  xorm:"ACCOUNTPWD"`                   // 资金密码
	UPDATETIME          string  `json:"updatetime"  xorm:"UPDATETIME"`                   // 更新时间(申请时间)
	BANKID              string  `json:"bankid"  xorm:"BANKID"`                           // 银行编号
	BRANCHBANKID        string  `json:"branchbankid"  xorm:"BRANCHBANKID"`               // 银行支行号
	BRANCHBANKNAME      string  `json:"branchbankname"  xorm:"BRANCHBANKNAME"`           // 银行支行名称
	BANKACCOUNTNO       string  `json:"bankaccountno"  xorm:"BANKACCOUNTNO"`             // 银行卡号
	BANKACCOUNTNAME     string  `json:"bankaccountname"  xorm:"BANKACCOUNTNAME"`         // 银行账户名
	EXTENDINFO          string  `json:"extendinfo"  xorm:"EXTENDINFO"`                   // 扩展信息
	AUDITID             int64   `json:"auditid"  xorm:"AUDITID"`                         // 审核人
	AUDITTIME           string  `json:"audittime"  xorm:"AUDITTIME"`                     // 审核时间
	REMARK              string  `json:"remark"  xorm:"REMARK"`                           // 备注
	NETADDR             string  `json:"netaddr"  xorm:"NETADDR"`                         // 调转网址
	ACCOUNTTICKET       string  `json:"accountticket"  xorm:"ACCOUNTTICKET"`             // 最新账户服务流水号
	CHECKERRORFLAG      int32   `json:"checkerrorflag"  xorm:"CHECKERRORFLAG"`           // 对账差错标志 - 1:为单边账;其它为正常出入金
	REMARK2             string  `json:"remark2"  xorm:"REMARK2"`                         // 备注(失败原因)
	REAUDITID           int64   `json:"reauditid"  xorm:"REAUDITID"`                     // 复审人
	REAUDITTIME         string  `json:"reaudittime"  xorm:"REAUDITTIME"`                 // 复审时间
	REAUDITREMARK       string  `json:"reauditremark"  xorm:"REAUDITREMARK"`             // 复审备注
	APPLYREMARK         string  `json:"applyremark"  xorm:"APPLYREMARK"`                 // 申请备注
	RELATEDORDERID      string  `json:"relatedorderid"  xorm:"RELATEDORDERID"`           // 三方关联ID
	CAPAMOUNTOUT        float64 `json:"capamountout"  xorm:"CAPAMOUNTOUT"`               // 出金(劣后本金) - 外部子账户
	INFAMOUNT           float64 `json:"infamount"  xorm:"INFAMOUNT"`                     // 劣后金额(自有)
	PRIAMOUNT           float64 `json:"priamount"  xorm:"PRIAMOUNT"`                     // 优先金额(授信)
	BANK_APPLY_TICKET   string  `json:"bank_apply_ticket"  xorm:"BANK_APPLY_TICKET"`     // 银行申请流水
	CERTIFICATEPHOTOURL string  `json:"certificatephotourl"  xorm:"CERTIFICATEPHOTOURL"` // 凭证地址
	SOUCREAMOUNT        float64 `json:"soucreamount"  xorm:"SOUCREAMOUNT"`               // 原始出入金金额
	SOUCRECURRENCYID    int64   `json:"soucrecurrencyid"  xorm:"SOUCRECURRENCYID"`       // 原始出入金币种
	ACCOUNTCODE         string  `json:"accountcode"  xorm:"ACCOUNTCODE"`                 // 资金账号
	CUSBANKID           string  `json:"cusbankid"  xorm:"CUSBANKID"`                     // 托管银行编号
	TRADEDATE           string  `json:"tradedate"  xorm:"TRADEDATE"`                     // 交易日(yyyyMMdd)
	EXCHTICKET          string  `json:"exchticket"  xorm:"EXCHTICKET"`                   // 银行服务流水号
	EXTOPERATEID        int64   `json:"extoperateid"  xorm:"EXTOPERATEID"`               // 交易服务流水号
	BANKTICKET          string  `json:"bankticket"  xorm:"BANKTICKET"`                   // 银行流水
	APPLYSTATUS         int32   `json:"applystatus"  xorm:"APPLYSTATUS"`                 // 申请状态 - 1:待审核 2:待复审 3:初审拒绝 4:交易冻结中 5:交易解冻中 6:交易解冻扣款中 7:交易入金中 8:交易冻结/解冻/扣款中(银行发起出金时用) 9:银行出金中 10:银行入金中 11:成功 12:失败 13:银行审核中  14:账户服务入金失败； 15:账户服务解冻失败； 16:账户服务解冻扣款失败； 17:账户服务出金失败 18:复审通过 19:复审拒绝 20:提交审核，账户冻结中 21:审核拒绝，账户解冻中；22: 待审核，账户服务解冻回滚中； 23：待复审，账户服务解冻回滚中； 24： 审核通过，账户冻结金额检查中；25： 复审通过，账户冻结金额检查中；
	USERID              int64   `json:"userid"  xorm:"'USERID'"`                         // 用户id
	LOGINCODE           string  `json:"logincode"  xorm:"'LOGINCODE'"`                   // 登录账号(账号)
	USERINFOTYPE        int32   `json:"userinfotype"  xorm:"'USERINFOTYPE'"`             // 账户类型 1-个人 2-企业
	ACCOUNTNAME         string  `json:"accountname"  xorm:"'ACCOUNTNAME'"`               // 用户名称(名称)

	QUERYTYPE  int32  `json:"-"` // 查询类型 1-提现 2-充值
	STATUS     int32  `json:"-"` // 查询状态 1-待审核 2-审核通过 3-审核拒绝
	BeginDate  string `json:"-"` // 开始日期(yyyymmdd)
	EndDate    string `json:"-"` // 结束日期(yyyymmdd)
	FilterName string `json:"-"` // 账户(模糊匹配)
}

func (r *QhjMgrAccountOutInApply) calc() {

}

func (r *QhjMgrAccountOutInApply) buildSql() string {
	var sqlId utils.SQLVal = "with tmp as(select t.userid, wm_concat(t.logincode) logincode from loginaccount t group by t.userid)" +
		"SELECT t.EXECUTETYPE," +
		"       t.AMOUNT," +
		"       t.CURRENCY," +
		"       t.CHARGE," +
		"       t.ACCOUNTPWD," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.BANKID," +
		"       t.BRANCHBANKID," +
		"       t.BRANCHBANKNAME," +
		"       t.BANKACCOUNTNO," +
		"       t.BANKACCOUNTNAME," +
		"       t.EXTENDINFO," +
		"       t.AUDITID," +
		"       to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," +
		"       t.REMARK," +
		"       t.NETADDR," +
		"       t.ACCOUNTTICKET," +
		"       t.CHECKERRORFLAG," +
		"       t.REMARK2," +
		"       t.REAUDITID," +
		"       to_char(t.REAUDITTIME, 'yyyy-mm-dd hh24:mi:ss') REAUDITTIME," +
		"       t.REAUDITREMARK," +
		"       t.APPLYREMARK," +
		"       t.RELATEDORDERID," +
		"       t.CAPAMOUNTOUT," +
		"       t.INFAMOUNT," +
		"       t.PRIAMOUNT," +
		"       t.BANK_APPLY_TICKET," +
		"       to_char(t.CERTIFICATEPHOTOURL) CERTIFICATEPHOTOURL," +
		"       t.SOUCREAMOUNT," +
		"       t.SOUCRECURRENCYID," +
		"       t.ACCOUNTCODE," +
		"       t.CUSBANKID," +
		"       t.TRADEDATE," +
		"       t.EXCHTICKET," +
		"       t.EXTOPERATEID," +
		"       t.BANKTICKET," +
		"       t.APPLYSTATUS," +
		"       ta.userid," +
		"       u.accountname," +
		"       ui.userinfotype," +
		"       tmp.logincode" +
		"  FROM BANK_ACCOUNTOUTINAPPLY t" +
		"  INNER JOIN TAACCOUNT ta on t.accountcode=to_char(ta.accountid)" +
		"  INNER JOIN USERACCOUNT u on ta.relateduserid=u.userid" +
		"  INNER JOIN USERINFO ui on u.userid=ui.userid" +
		"  LEFT JOIN tmp on u.userid=tmp.userid" +
		" WHERE 1 = 1"
	sqlId.And("EXECUTETYPE", r.QUERYTYPE)
	switch r.STATUS {
	case 1:
		sqlId.JoinFormat(" and t.APPLYSTATUS in(%v)", InStr(1, 2))
	case 2:
		sqlId.JoinFormat(" and t.APPLYSTATUS in(%v)", InStr(11, 18, 24, 25))
	case 3:
		sqlId.JoinFormat(" and t.APPLYSTATUS in(%v)", InStr(3, 12, 21))
	}
	if len(r.BeginDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.TRADEDATE >= %v", r.BeginDate))
	}
	if len(r.EndDate) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.TRADEDATE <= %v", r.EndDate))
	}
	if len(r.FilterName) > 0 {
		sqlId.Join(fmt.Sprintf(" and (tmp.logincode like '%%%v%%' or u.accountname like '%%%v%%')", r.FilterName, r.FilterName))
	}
	return sqlId.String()
}

// GetDataEx 获取充值提现（出入金）
func (r *QhjMgrAccountOutInApply) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrAccountOutInApply, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjTradeEx 额外的用户和商品信息
type QhjTradeEx struct {
	LOGINCODE       string  `json:"logincode"  xorm:"'LOGINCODE'"`           // 登录代码(账户)
	USERID          int64   `json:"userid"  xorm:"'USERID'"`                 // 用户id
	ACCOUNTNAME     string  `json:"accountname"  xorm:"'ACCOUNTNAME'"`       // 用户名称
	ENUMDICNAME     string  `json:"enumdicname"  xorm:"'ENUMDICNAME'"`       // 单位名称(商品)
	GOODSCODE       string  `json:"goodscode"  xorm:"GOODSCODE"`             // 商品代码(内部)
	GOODSNAME       string  `json:"goodsname"  xorm:"GOODSNAME"`             // 商品名称
	GOODUNITID      int32   `json:"goodunitid"  xorm:"GOODUNITID"`           // 报价单位ID
	AGREEUNIT       float64 `json:"agreeunit"  xorm:"AGREEUNIT"`             // 合约乘数
	DECIMALPLACE    int32   `json:"decimalplace"  xorm:"DECIMALPLACE"`       // 报价小数位
	QTYDECIMALPLACE int32   `json:"qtydecimalplace"  xorm:"QTYDECIMALPLACE"` // 成交量小数位
	USERINFOTYPE    int32   `json:"userinfotype"  xorm:"'USERINFOTYPE'"`     // 账户类型 1-个人 2-企业
}

// QhjMgrPosition 持仓汇总
type QhjMgrPosition struct {
	QhjTradeEx   `xorm:"extends"`
	ACCOUNTID    int64   `json:"accountid"`    // 账号Id
	GOODSID      int32   `json:"goodsid"`      // 商品Id
	HOLDERTYPE   int32   `json:"holdertype"`   // 持仓类别 - 1:单边持仓 2:双边持仓
	HOLDERQTY    float64 `json:"holderqty"`    // 持有数量
	FROZENQTY    float64 `json:"frozenqty"`    // 冻结数量
	AVIALQTY     float64 `json:"avialqty"`     // 可用数量
	HOLDERAMOUNT float64 `json:"holderamount"` // 金额
	AVERAGEPRICE float64 `json:"averageprice"` // 均价
}

// QhjMgrTradePosition 持仓汇总
type QhjMgrTradePosition struct {
	ACCOUNTID           int64   `json:"accountid"  xorm:"ACCOUNTID"`                     // 账号Id
	GOODSID             int32   `json:"goodsid"  xorm:"GOODSID"`                         // 商品Id
	HOLDERTYPE          int32   `json:"holdertype"  xorm:"HOLDERTYPE"`                   // 持仓类别 - 1:单边持仓 2:双边持仓
	BUYPOSITIONQTY      float64 `json:"buypositionqty"  xorm:"BUYPOSITIONQTY"`           // 买期初持仓数量
	BUYHOLDERAMOUNT     float64 `json:"buyholderamount"  xorm:"BUYHOLDERAMOUNT"`         // 买期初持仓总金额[商品币种]
	BUYCURPOSITIONQTY   float64 `json:"buycurpositionqty"  xorm:"BUYCURPOSITIONQTY"`     // 买当前持仓总数量
	BUYCURHOLDERAMOUNT  float64 `json:"buycurholderamount"  xorm:"BUYCURHOLDERAMOUNT"`   // 买当前持仓总金额[商品币种]
	BUYFROZENQTY        float64 `json:"buyfrozenqty"  xorm:"BUYFROZENQTY"`               // 买持仓冻结数量
	BUYOTHERFROZENQTY   float64 `json:"buyotherfrozenqty"  xorm:"BUYOTHERFROZENQTY"`     // 买持仓其他冻结数量(交割冻结)
	BUYOPENREQQTY       float64 `json:"buyopenreqqty"  xorm:"BUYOPENREQQTY"`             // 买开仓申请数量(用于比较最大持仓数量)
	BUYOPENTOTALQTY     float64 `json:"buyopentotalqty"  xorm:"BUYOPENTOTALQTY"`         // 今日买开仓总数量
	BUYCLOSETOTALQTY    float64 `json:"buyclosetotalqty"  xorm:"BUYCLOSETOTALQTY"`       // 今日买平仓总数量
	SELLPOSITIONQTY     float64 `json:"sellpositionqty"  xorm:"SELLPOSITIONQTY"`         // 卖期初持仓数量
	SELLHOLDERAMOUNT    float64 `json:"sellholderamount"  xorm:"SELLHOLDERAMOUNT"`       // 卖期初持仓总金额[商品币种]
	SELLCURPOSITIONQTY  float64 `json:"sellcurpositionqty"  xorm:"SELLCURPOSITIONQTY"`   // 卖当前持仓数量
	SELLCURHOLDERAMOUNT float64 `json:"sellcurholderamount"  xorm:"SELLCURHOLDERAMOUNT"` // 卖当前持仓总金额[商品币种]
	SELLFROZENQTY       float64 `json:"sellfrozenqty"  xorm:"SELLFROZENQTY"`             // 卖持仓冻结
	SELLOTHERFROZENQTY  float64 `json:"sellotherfrozenqty"  xorm:"SELLOTHERFROZENQTY"`   // 卖持仓其他冻结(交割冻结)
	SELLOPENREQQTY      float64 `json:"sellopenreqqty"  xorm:"SELLOPENREQQTY"`           // 卖开仓申请数量(用于比较最大持仓数量)
	SELLOPENTOTALQTY    float64 `json:"sellopentotalqty"  xorm:"SELLOPENTOTALQTY"`       // 今日卖开仓总数量
	SELLCLOSETOTALQTY   float64 `json:"sellclosetotalqty"  xorm:"SELLCLOSETOTALQTY"`     // 今日卖平仓总数量
	USEDMARGIN          float64 `json:"usedmargin"  xorm:"USEDMARGIN"`                   // 占用保证金[商品币种]
	TRADEPROPERTY       int32   `json:"tradeproperty"  xorm:"TRADEPROPERTY"`             // 交易属性
	BUYTNQTY            float64 `json:"buytnqty"  xorm:"BUYTNQTY"`                       // 买T+N冻结总量
	BUYTNUSEDQTY        float64 `json:"buytnusedqty"  xorm:"BUYTNUSEDQTY"`               // 买T+N使用量(可以使用T+N的冻结数量)
	SELLTNQTY           float64 `json:"selltnqty"  xorm:"SELLTNQTY"`                     // 卖T+N冻结总量
	SELLTNUSEDQTY       float64 `json:"selltnusedqty"  xorm:"SELLTNUSEDQTY"`             // 卖T+N使用量(可以使用T+N的冻结数量)
	BUYCURTDPOSITION    float64 `json:"buycurtdposition"  xorm:"BUYCURTDPOSITION"`       // 买期末今日头寸
	BUYFRETDPOSITION    float64 `json:"buyfretdposition"  xorm:"BUYFRETDPOSITION"`       // 买冻结今日头寸
	SELLCURTDPOSITION   float64 `json:"sellcurtdposition"  xorm:"SELLCURTDPOSITION"`     // 卖期末今日头寸
	SELLFRETDPOSITION   float64 `json:"sellfretdposition"  xorm:"SELLFRETDPOSITION"`     // 卖冻结今日头寸
	QhjTradeEx          `xorm:"extends"`

	IncludeSub int32  `json:"-"` // 是否包含子级 1-包含
	FilterName string `json:"-"` // 模糊搜索
}

func (r *QhjMgrTradePosition) calc() {

}

func (r *QhjMgrTradePosition) buildSql() string {
	var sqlId utils.SQLVal = "with k as" +
		" (select t.userid, wm_concat(t.logincode) logincode" +
		"    from loginaccount t" +
		"   group by t.userid)" +
		"select u.userid," +
		"       u.accountname," +
		"       k.logincode," +
		"       u.subarealevelpath," +
		"       ui.userinfotype," +
		"       g.goodsname," +
		"       g.goodscode," +
		"       g.goodunitid," +
		"       g.qtydecimalplace," +
		"       g.decimalplace," +
		"       e.enumdicname," +
		"       t.*" +
		"  from tradeposition t" +
		" inner join taaccount ta" +
		"    on t.accountid = ta.accountid" +
		" inner join useraccount u" +
		"    on ta.relateduserid = u.userid" +
		" inner join userinfo ui" +
		"    on u.userid = ui.userid" +
		"  left join k" +
		"    on u.userid = k.userid" +
		"  left join goods g" +
		"    on t.goodsid = g.goodsid" +
		"  left join enumdicitem e" +
		"    on e.enumitemname = g.goodunitid" +
		"   and e.enumdiccode = 'goodsunit'" +
		" where 1 = 1"
	if r.IncludeSub == 1 {
		sqlId.JoinFormat(" and u.subarealevelpath like '%%,%v,%%'", r.USERID)
	} else {
		sqlId.And("u.parentuserid", r.USERID)
	}
	sqlId.AndEx("ui.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0)
	sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0)
	if len(r.FilterName) > 0 {
		sqlId.JoinFormat(" and (k.logincode like '%%%v%%' or u.accountname like '%%%v%%')", r.FilterName, r.FilterName)
	}
	return sqlId.String()
}

// GetDataEx 获取持仓汇总
func (r *QhjMgrTradePosition) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrTradePosition, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	sData2 := make([]QhjMgrPosition, 0)
	for i := range sData {
		sData[i].calc()
		v := QhjMgrPosition{QhjTradeEx: sData[i].QhjTradeEx}
		v.GOODSID = sData[i].GOODSID
		v.ACCOUNTID, v.HOLDERTYPE = sData[i].ACCOUNTID, sData[i].HOLDERTYPE
		v.HOLDERAMOUNT = sData[i].BUYCURHOLDERAMOUNT
		v.HOLDERQTY = sData[i].BUYCURPOSITIONQTY
		v.FROZENQTY = sData[i].BUYFROZENQTY
		v.AVIALQTY = v.HOLDERQTY - v.FROZENQTY
		zoomFun := NewZoomFun(int(v.QTYDECIMALPLACE))
		zoomFun(&v.HOLDERQTY, &v.FROZENQTY, &v.AVIALQTY)
		if v.HOLDERQTY > 1e-10 {
			v.AVERAGEPRICE = v.HOLDERAMOUNT / v.HOLDERQTY
		}
		sData2 = append(sData2, v)
	}
	return sData2, err
}