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'"`                       // 开户状态(网上开户表wskh_userinfo) 1:未提交 2: 待初审 3:初审拒绝 4:待复审 5:复审拒绝 6:测评不通过 ; 账户状态(正式表useraccount) 1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:停用(注销) 7:注销(删除)
	STATUSDESC        string `json:"statusdesc"`                                    // 账户状态中文描述
	ATTACHMENT1       string `json:"attachment1"  xorm:"'ATTACHMENT1'"`             // 附件1
	CARDFRONTPHOTOURL string `json:"cardfrontphotourl"  xorm:"'CARDFRONTPHOTOURL'"` // 证件正面图片地址
	CARDBACKPHOTOURL  string `json:"cardbackphotourl"  xorm:"'CARDBACKPHOTOURL'"`   // 证件反面图片地址
	PROXYSTATEMENTURL string `json:"proxystatementurl"  xorm:"'PROXYSTATEMENTURL'"` // 授权委托书
	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'"`                 // 登录账号(代码)
	BROKERID          int64  `json:"brokerid"  xorm:"'BROKERID'"`                   // 所属客户经理id
	BROKERNAME        string `json:"brokername"  xorm:"'BROKERNAME'"`               // 所属客户经理名称
	BIRTHDAY          string `json:"birthday"  xorm:"'BIRTHDAY'" form:"birthday"`   // 生日
	SEX               int32  `json:"sex"  xorm:"'SEX'"`                             // 性别 0-女 1-男

	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:"querytype"` // 查询类型 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)

	if r.CARDTYPE >= 0 {
		// 如果数据库里cardtype为空, 则nvl(cardtype,-1)取出, 用于判断是否为空
		r.CARDTYPENAME = mtpcache.GetCardName(r.CARDTYPE)
	} else {
		r.CARDTYPE = 0
	}

	fDesc := func(status int32, vDesc string) {
		if r.STATUS == status {
			r.STATUSDESC = vDesc
		}
	}

	switch r.QUERYTYPE {
	case 1, 2: // 网上开户表
		r.ADDRESS = DecryptField(r.ADDRESS)
		r.TELPHONE = DecryptField(r.TELPHONE)
		//开户状态 -  1:未提交 2: 待初审 3:初审拒绝 4:待复审 5:复审拒绝 6:测评不通过
		fDesc(1, "未提交")
		fDesc(2, "待初审")
		fDesc(3, "初审拒绝")
		fDesc(4, "待复审")
		fDesc(5, "复审拒绝")
		fDesc(6, "测评不通过")
	case 3, 4: // 正式表
		r.TELPHONE = DecryptField(r.TELPHONE)
		//账户状态 -  1:待激活 2:待审核 3:待复审 4:正常 5:审核拒绝 6:停用(注销) 7:注销(删除)
		fDesc(1, "待激活")
		fDesc(2, "待审核")
		fDesc(3, "待复审")
		fDesc(4, "正常")
		fDesc(5, "审核拒绝")
		fDesc(6, "停用(注销)")
		fDesc(7, "注销(删除)")
	}
	r.CARDNUM = DecryptField(r.CARDNUM)
	r.MOBILE = DecryptField(r.MOBILE)
	r.EMAIL = DecryptField(r.EMAIL)
	if r.LOGINCODE == "" {
		r.LOGINCODE = r.MOBILE
	}

	if len(r.CUSTOMERNAME) == 0 {
		r.CUSTOMERNAME = r.USERNAME
	}
}

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 u.accountname brokername, a.*
  from (select t.userid,
               t.memberareaid MEMBERUSERID,
               t.USERINFOTYPE,
               t.username,
               t.CUSTOMERNAME,
               t.NICKNAME,
               nvl(t.CARDTYPE,-1) CARDTYPE,
               t.CARDNUM,
               t.mobilephone MOBILE,
               t.TELPHONE,
               t.cardaddress ADDRESS,
               t.REMARK,
               t.ATTACHMENT1,
               t.CARDFRONTPHOTOURL,
               t.CARDBACKPHOTOURL,
               t.proxystatementurl,
               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,
               t.sex,
               to_char(t.birthday, 'yyyy-mm-dd hh24:mi:ss') birthday,
               to_number(t.brokerid) brokerid,
               t.birthday birthday2
          from wskh_userinfo t
         where 1=1 %v) a
  left join useraccount u
    on a.brokerid = u.userid
 where 1 = 1
   and a.status in (%v)
`
	var strParm string
	if r.IncludeSub == 0 {
		strParm = fmt.Sprintf(" and t.areaid = %v", r.USERID)
	} else {
		str := `
		and t.areaid in (select t.userid
		from useraccount t
		where t.subarealevelpath like '%%,%v,%%'
		)
	`
		strParm = fmt.Sprintf(str, r.USERID)
	}
	status := DecodeStr(r.QUERYTYPE == 1, "1", "2,4,5")
	sqlId.FormatParam(strParm, 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))
	sqlId.JoinEx(len(r.BROKERNAME) > 0, fmt.Sprintf(` and u.accountname like '%%%v%%'`, r.BROKERNAME))
	if len(r.BIRTHDAY) == 2 {
		sqlId.JoinFormat(" and to_char(a.birthday2, 'mm') = '%v'", r.BIRTHDAY)
	} else if len(r.BIRTHDAY) == 4 {
		sqlId.JoinFormat(" and to_char(a.birthday2, 'mmdd') = '%v'", r.BIRTHDAY)
	}
	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,
       nvl(u.CARDTYPEID,-1) CARDTYPE,
       u.CARDNUM,
       u.MOBILE,
       u.TELPHONE,
       u.ADDRESS,
       u.REMARK,
       u.ATTACHMENT1,
       u.CARDFRONTPHOTOURL,
       u.CARDBACKPHOTOURL,
       u.proxystatementurl,
       u.countryid,
       u.provinceid,
       u.districtid,
       u.taxpayernum,
       u.legalpersonname,
       u.contactname,
       u.email,
       u.cityid,
       u.sex,
       to_char(u.birthday, 'yyyy-mm-dd hh24:mi:ss') birthday,
       u2.accountname brokername
  from (select t.userid,
               t.usertype,
               t.parentuserid,
               t.memberuserid,
               t.broker,
               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
  left join useraccount u2 on a.broker=u2.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))
	sqlId.JoinEx(len(r.BROKERNAME) > 0, fmt.Sprintf(` and u2.accountname like '%%%v%%'`, r.BROKERNAME))
	if len(r.BIRTHDAY) == 2 {
		sqlId.JoinFormat(" and to_char(u.birthday, 'mm') = '%v'", r.BIRTHDAY)
	} else if len(r.BIRTHDAY) == 4 {
		sqlId.JoinFormat(" and to_char(u.birthday, 'mmdd') = '%v'", r.BIRTHDAY)
	}
	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"`                           // 银行编号
	BANKNAME            string  `json:"bankname"  xorm:"'BANKNAME'"`                     // 银行名称
	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() {
	r.BANKACCOUNTNAME = DecryptField(r.BANKACCOUNTNAME)
	r.BANKACCOUNTNO = DecryptField(r.BANKACCOUNTNO)
}

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," +
		"       b.bankname" +
		"  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 BANK_BANKINFO b on t.bankid=b.bankid" +
		" 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))
	}
	sqlId.Join(" order by t.UPDATETIME desc")
	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-企业
	MOBILE          string  `json:"-"  xorm:"'MOBILE'"`                      // 手机号码
	MARKETID        int32   `json:"marketid"  xorm:"'MARKETID'"`             // 市场id
	TRADEMODE       int32   `json:"trademode"  xorm:"'TRADEMODE'"`           // 交易模式
}

func (r *QhjTradeEx) dealConvert() {
	if r.LOGINCODE == "" {
		r.LOGINCODE = DecryptField(r.MOBILE)
	}
}

// QhjMgrPositionRsp 持仓汇总
type QhjMgrPositionRsp 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() {
	r.dealConvert()
}

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," +
		"       ui.mobile," +
		"       g.goodsname," +
		"       g.goodscode," +
		"       g.goodunitid," +
		"       g.qtydecimalplace," +
		"       g.decimalplace," +
		"       g.agreeunit," +
		"       m.marketid," +
		"       m.trademode," +
		"       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 market m on g.marketid=m.marketid" +
		"  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("m.TRADEMODE", r.TRADEMODE, r.TRADEMODE > 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([]QhjMgrPositionRsp, 0)
	for i := range sData {
		sData[i].calc()
		v := QhjMgrPositionRsp{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
}

// QhjMgrTradeDetailRsp
type QhjMgrTradeDetailRsp struct {
	TRADEID     string  `json:"tradeid"`     // 成交单号
	GOODSID     int32   `json:"goodsid"`     // 商品id
	BUYORSELL   int32   `json:"buyorsell"`   // 成交类型(方向) 0:买 1:卖
	ORDERID     string  `json:"orderid"`     // 关联委托单号
	TRADEDATE   string  `json:"tradedate"`   // 交易日
	ACCOUNTID   int64   `json:"accountid"`   // 资金账号
	TRADETIME   string  `json:"tradetime"`   // 成交时间
	TRADEPRICE  float64 `json:"tradeprice"`  // 价格
	TRADEQTY    float64 `json:"tradeqty"`    // 数量
	TRADEAMOUNT float64 `json:"tradeamount"` // 金额
	QhjTradeEx  `xorm:"extends"`
}

// QhjMgrTradeDetail 成交明细
type QhjMgrTradeDetail struct {
	TRADEID                 string  `json:"tradeid"  xorm:"TRADEID2"`                                // 成交单号(101+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx)
	BUYORSELL               int32   `json:"buyorsell"  xorm:"BUYORSELL"`                             // 方向 - 0:买 1:卖
	ORDERID                 string  `json:"orderid"  xorm:"ORDERID2"`                                // 委托单号
	TRADEDATE               string  `json:"tradedate"  xorm:"TRADEDATE"`                             // 交易日(yyyyMMdd)
	ACCOUNTID               int64   `json:"accountid"  xorm:"ACCOUNTID"`                             // 账户ID[报价币种]
	GOODSID                 int32   `json:"goodsid"  xorm:"GOODSID"`                                 // 商品ID
	MEMBERUSERID            int64   `json:"memberuserid"  xorm:"MEMBERUSERID"`                       // 会员id 个人投资者 需要填写
	MATCHACCOUNTID          int64   `json:"matchaccountid"  xorm:"MATCHACCOUNTID"`                   // 对手账号id
	TRADETIME               string  `json:"tradetime"  xorm:"TRADETIME2"`                            // 成交时间
	TRADEPRICE              float64 `json:"tradeprice"  xorm:"TRADEPRICE"`                           // 成交价格
	TRADEQTY                float64 `json:"tradeqty"  xorm:"TRADEQTY"`                               // 成交数量
	TRADEAMOUNT             float64 `json:"tradeamount"  xorm:"TRADEAMOUNT"`                         // 成交金额[账户币种，用于所有权]
	CLOSEPL                 float64 `json:"closepl"  xorm:"CLOSEPL"`                                 // 平仓盈亏
	INTCLOSEPL              int64   `json:"intclosepl"  xorm:"INTCLOSEPL"`                           // 整型盈亏(用于交易结算试算平衡-收益权)
	OPENCHARGE              float64 `json:"opencharge"  xorm:"OPENCHARGE"`                           // 建仓手续费(支付总手续费=(交易所比率+会员比率)*成交金额)
	CLOSECHARGE             float64 `json:"closecharge"  xorm:"CLOSECHARGE"`                         // 平仓手续费(支付总手续费=(交易所比率+会员比率)*成交金额)
	TRADETYPE               int32   `json:"tradetype"  xorm:"TRADETYPE"`                             // 成交类别 - 1:正常委托成交 2:定向做市成交(接单) 3:交割协议平仓成交 4:交割减仓成交 5:到期强平成交 6:风控斩仓成交 7:协议平仓(管理端)成交 8:仓单转持仓成交 9: 交割协议转让成交 10:受托竞价成交(接单) 11:协议转让成交 12:系统强行平仓 13:期权违约平仓
	BUILDTYPE               int32   `json:"buildtype"  xorm:"BUILDTYPE"`                             // 委托单据类型 1:建仓 2:平仓 3:先平后建
	OPENQTY                 float64 `json:"openqty"  xorm:"OPENQTY"`                                 // 开仓数量(先建后平操作 需要记录)
	CLOSEQTY                float64 `json:"closeqty"  xorm:"CLOSEQTY"`                               // 平仓数量(先建后平操作 需要记录)
	STATUS                  int32   `json:"status"  xorm:"STATUS"`                                   // 处理状态 - 1:待处理 2:已处理 3:处理失败
	ISRECKONED              int32   `json:"isreckoned"  xorm:"ISRECKONED"`                           // 是否结算 - 0:未结算 1:已结算
	TRADEPROPERTY           int32   `json:"tradeproperty"  xorm:"TRADEPROPERTY"`                     // 交易属性
	OPENFEEALGORITHM        int32   `json:"openfeealgorithm"  xorm:"OPENFEEALGORITHM"`               // 建仓手续费收取方式  1：比率  2：固定
	OPENMEMBERCHARGEVALUE   float64 `json:"openmemberchargevalue"  xorm:"OPENMEMBERCHARGEVALUE"`     // 建仓会员手续费设置值
	OPENEXCHAGECHARGEVALUE  float64 `json:"openexchagechargevalue"  xorm:"OPENEXCHAGECHARGEVALUE"`   // 建仓交易所手续费设置值
	CLOSEFEEALGORITHM       int32   `json:"closefeealgorithm"  xorm:"CLOSEFEEALGORITHM"`             // 平仓手续费收取方式 1：比率  2：固定
	CLOSEMEMBERCHARGEVALUE  float64 `json:"closememberchargevalue"  xorm:"CLOSEMEMBERCHARGEVALUE"`   // 平仓会员手续费设置值
	CLOSEEXCHAGECHARGEVALUE float64 `json:"closeexchagechargevalue"  xorm:"CLOSEEXCHAGECHARGEVALUE"` // 平仓交易所手续费设置值
	OPTIONTYPE              int32   `json:"optiontype"  xorm:"OPTIONTYPE"`                           // 期权类型 - 1:认购(看涨) 2:认沽(看跌)
	PREMIUM                 float64 `json:"premium"  xorm:"PREMIUM"`                                 // 权利金 - [持仓单的权利金]
	ISPREEXERCISE           int32   `json:"ispreexercise"  xorm:"ISPREEXERCISE"`                     // 是否预申报- 0:否 1:是
	PREEXERCISEPRICE        float64 `json:"preexerciseprice"  xorm:"PREEXERCISEPRICE"`               // 预申报价格
	ISCONFIRMEXERCISE       int32   `json:"isconfirmexercise"  xorm:"ISCONFIRMEXERCISE"`             // 是否确认行权- 0:否 1:是
	ISMAIN                  int32   `json:"ismain"  xorm:"ISMAIN"`                                   // 是否主单 - 0:不是 1:是
	PERFORMANCEPLANID       int64   `json:"performanceplanid"  xorm:"PERFORMANCEPLANID"`             // 履约计划ID[期权]
	PERFORMANCESTATUS       int32   `json:"performancestatus"  xorm:"PERFORMANCESTATUS"`             // 履约状态[期权] - 0:无履约 1:未履约 2:履约中 3:履约完成
	CREDITAMOUNT            float64 `json:"creditamount"  xorm:"CREDITAMOUNT"`                       // 授信金额
	GCACCOUNTID             int64   `json:"gcaccountid"  xorm:"GCACCOUNTID"`                         // 账户ID[合约币种]
	CLOSEPL2                float64 `json:"closepl2"  xorm:"CLOSEPL2"`                               // 平仓盈亏[逐笔]
	RELATEDOUTTRADEID       int64   `json:"relatedouttradeid"  xorm:"RELATEDOUTTRADEID"`             // 关联外部成交单ID
	QhjTradeEx              `xorm:"extends"`

	IncludeSub int32  `json:"-"` // 是否包含子级 1-包含
	BeginDate  string `json:"-"` // 开始交易日
	EndDate    string `json:"-"` // 结束交易日
	FilterName string `json:"-"` // 模糊搜索
}

func (r *QhjMgrTradeDetail) calc() {
	r.dealConvert()
}

func (r *QhjMgrTradeDetail) buildSql(bQueryHis bool) 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," +
		"       ui.mobile," +
		"       g.goodsname," +
		"       g.goodscode," +
		"       g.goodunitid," +
		"       g.qtydecimalplace," +
		"       g.decimalplace," +
		"       g.agreeunit," +
		"       m.marketid," +
		"       m.trademode," +
		"       e.enumdicname," +
		"       to_char(t.TRADEID) TRADEID2," +
		"       to_char(t.ORDERID) ORDERID2," +
		"       to_char(t.TRADETIME, 'yyyy-mm-dd hh24:mi:ss') TRADETIME2," +
		"       t.*" +
		"  from %v 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 market m on g.marketid=m.marketid" +
		"  left join enumdicitem e" +
		"    on e.enumitemname = g.goodunitid" +
		"   and e.enumdiccode = 'goodsunit'" +
		" where 1 = 1"
	if bQueryHis {
		sqlId.FormatParam("his_trade_tradedetail")
		sqlId.And("t.isvaliddata", 1)
	} else {
		sqlId.FormatParam("trade_tradedetail")
	}
	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("m.TRADEMODE", r.TRADEMODE, r.TRADEMODE > 0)
	sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0)
	if len(r.BeginDate) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE >=%v", r.BeginDate)
	}
	if len(r.EndDate) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE <=%v", r.EndDate)
	}
	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 *QhjMgrTradeDetail) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrTradeDetail, 0)
	sCurData := make([]QhjMgrTradeDetail, 0)
	err := db.GetEngine().SQL(r.buildSql(false)).Find(&sCurData)
	sHisData := make([]QhjMgrTradeDetail, 0)
	err = db.GetEngine().SQL(r.buildSql(true)).Find(&sHisData)
	sData2 := make([]QhjMgrTradeDetailRsp, 0)
	sData = append(sData, sCurData...)
	sData = append(sData, sHisData...)
	for i := range sData {
		sData[i].calc()
		v := QhjMgrTradeDetailRsp{QhjTradeEx: sData[i].QhjTradeEx}
		v.ACCOUNTID, v.TRADEID = sData[i].ACCOUNTID, sData[i].TRADEID
		v.TRADEAMOUNT, v.TRADEQTY = sData[i].TRADEAMOUNT, sData[i].TRADEQTY
		v.TRADEDATE, v.TRADETIME = sData[i].TRADEDATE, sData[i].TRADETIME
		v.GOODSID, v.ORDERID = sData[i].GOODSID, sData[i].ORDERID
		v.TRADEPRICE, v.BUYORSELL = sData[i].TRADEPRICE, sData[i].BUYORSELL
		zoonFun := NewZoomFun(int(v.QTYDECIMALPLACE))
		zoonFun(&v.TRADEQTY)
		sData2 = append(sData2, v)
	}
	return sData2, err
}

// QhjMgrTradeOrderDetailRsp 委托明细(给终端)
type QhjMgrTradeOrderDetailRsp struct {
	GOODSID     int32   `json:"goodsid"`     // 商品id
	ORDERID     string  `json:"orderid"`     // 关联委托单号
	TRADEDATE   string  `json:"tradedate"`   // 交易日
	ACCOUNTID   int64   `json:"accountid"`   // 资金账号
	ORDERTIME   string  `json:"ordertime"`   // 委托时间
	ORDERPRICE  float64 `json:"orderprice"`  // 价格
	ORDERQTY    float64 `json:"orderqty"`    // 数量
	AMOUNT      float64 `json:"amount"`      // 金额
	ORDERSTATUS int32   `json:"orderstatus"` // 委托状态 - 1： 委托请求 2：待冻结 3：委托成功 4： 委托失败 5：配对成功 6： 已撤销 7：部分成交 8：已成交 9：部成部撤 10：成交失败 11：已拒绝 12:经过摘牌(先摘后挂专用-先摘后挂已摘过) 13:冻结成功(通道交易专用) 14:通道已撤 15:通道部成部撤 16:成交失败违约(荷兰式竞拍专用)
	QhjTradeEx  `xorm:"extends"`
}

// QhjMgrTradeOrderDetail 委托明细
type QhjMgrTradeOrderDetail struct {
	CLOSEMEMBERCHARGEVALUE  float64 `json:"closememberchargevalue"  xorm:"CLOSEMEMBERCHARGEVALUE"`   // 平仓会员手续费设置值
	CLOSEEXCHAGECHARGEVALUE float64 `json:"closeexchagechargevalue"  xorm:"CLOSEEXCHAGECHARGEVALUE"` // 平仓交易所手续费设置值
	OPTIONTYPE              int32   `json:"optiontype"  xorm:"OPTIONTYPE"`                           // 期权类型 - 1:认购(看涨) 2:认沽(看跌)
	PREMIUM                 float64 `json:"premium"  xorm:"PREMIUM"`                                 // 权利金
	ISPREEXERCISE           int32   `json:"ispreexercise"  xorm:"ISPREEXERCISE"`                     // 是否预申报- 0:否 1:是
	PREEXERCISEPRICE        float64 `json:"preexerciseprice"  xorm:"PREEXERCISEPRICE"`               // 预申报价格
	ISCONFIRMEXERCISE       int32   `json:"isconfirmexercise"  xorm:"ISCONFIRMEXERCISE"`             // 是否确认行权- 0:否 1:是
	QUOTEID                 int64   `json:"quoteid"  xorm:"QUOTEID"`                                 // 报价单ID
	GCACCOUNTID             int64   `json:"gcaccountid"  xorm:"GCACCOUNTID"`                         // 账户ID[合约币种]
	SESSIONID               int64   `json:"sessionid"  xorm:"SESSIONID"`                             // 会话ID
	FREEZEQTY               float64 `json:"freezeqty"  xorm:"FREEZEQTY"`                             // 冻结数量
	AMOUNTFLAG              int32   `json:"amountflag"  xorm:"AMOUNTFLAG"`                           // 资金标识 - 1:余额 2;待付
	ORDERFLAG               int32   `json:"orderflag"  xorm:"ORDERFLAG"`                             // 委托标识 - 1:按量 2:按金额
	ORDERID                 string  `json:"orderid"  xorm:"ORDERID2"`                                // 委托单号(100+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx)
	TRADEDATE               string  `json:"tradedate"  xorm:"TRADEDATE"`                             // 交易日(yyyyMMdd)
	BUILDTYPE               int32   `json:"buildtype"  xorm:"BUILDTYPE"`                             // 委托单据类型 - 1:建仓 2:平仓 3:先平后建
	PREORDERID              string  `json:"preorderid"  xorm:"PREORDERID"`                           // 关联预埋单号(止盈止损单时填写)
	CANCELORDERID           int64   `json:"cancelorderid"  xorm:"CANCELORDERID"`                     // 撤单单号(撤单时填写)
	RELATEDID               string  `json:"relatedid"  xorm:"RELATEDID"`                             // 关联单号(交割单)
	MARKETID                int32   `json:"marketid"  xorm:"MARKETID"`                               // 市场ID
	GOODSID                 int32   `json:"goodsid"  xorm:"GOODSID"`                                 // 商品ID
	ACCOUNTID               int64   `json:"accountid"  xorm:"ACCOUNTID"`                             // 账户ID[报价币种]
	MEMBERUSERID            int64   `json:"memberuserid"  xorm:"MEMBERUSERID"`                       // 所属会员UserID
	BUYORSELL               int32   `json:"buyorsell"  xorm:"BUYORSELL"`                             // 买卖 - 0:买 1:卖
	PRICEMODE               int32   `json:"pricemode"  xorm:"PRICEMODE"`                             // 取价方式 - 1:市价 2: 限价
	ORDERPRICE              float64 `json:"orderprice"  xorm:"ORDERPRICE"`                           // 委托价格
	MARKETMAXSUB            float64 `json:"marketmaxsub"  xorm:"MARKETMAXSUB"`                       // 市价最大偏移范围
	ORDERQTY                float64 `json:"orderqty"  xorm:"ORDERQTY"`                               // 委托数量
	TRADEQTY                float64 `json:"tradeqty"  xorm:"TRADEQTY"`                               // 成交数量
	CANCELQTY               float64 `json:"cancelqty"  xorm:"CANCELQTY"`                             // 撤单数量
	OPENQTY                 float64 `json:"openqty"  xorm:"OPENQTY"`                                 // 开仓数量(先建后平操作,需要记录)
	CLOSEQTY                float64 `json:"closeqty"  xorm:"CLOSEQTY"`                               // 平仓数量(先建后平操作 需要记录)
	OPENTRADEQTY            float64 `json:"opentradeqty"  xorm:"OPENTRADEQTY"`                       // 开仓成交数量(先建后平操作,需要记录)
	CLOSETRADEQTY           float64 `json:"closetradeqty"  xorm:"CLOSETRADEQTY"`                     // 平仓成交数量(先建后平操作,需要记录)
	FREEZEMARGIN            float64 `json:"freezemargin"  xorm:"FREEZEMARGIN"`                       // 冻结保证金(冻结交易金额)
	UNFREEZEMARGIN          float64 `json:"unfreezemargin"  xorm:"UNFREEZEMARGIN"`                   // 解冻保证金
	FREEZECHARGE            float64 `json:"freezecharge"  xorm:"FREEZECHARGE"`                       // 冻结手续费
	UNFREEZECHARGE          float64 `json:"unfreezecharge"  xorm:"UNFREEZECHARGE"`                   // 解冻手续费
	OPENFREEZECHARGE        float64 `json:"openfreezecharge"  xorm:"OPENFREEZECHARGE"`               // 开仓冻结手续费(先建后平操作,需要记录)
	CLOSEFREEZECHARGE       float64 `json:"closefreezecharge"  xorm:"CLOSEFREEZECHARGE"`             // 平仓冻结手续费(先建后平操作,需要记录)
	OPENUNFREEZECHARGE      float64 `json:"openunfreezecharge"  xorm:"OPENUNFREEZECHARGE"`           // 开仓解冻手续费(先建后平操作,需要记录)
	CLOSEUNFREEZECHARGE     float64 `json:"closeunfreezecharge"  xorm:"CLOSEUNFREEZECHARGE"`         // 平仓解冻手续费(先建后平操作,需要记录)
	VALIDTYPE               int32   `json:"validtype"  xorm:"VALIDTYPE"`                             // 有效类型 - 1当日有效 2本周有效 3指定日期有效 4一直有效 5指定时间有效
	VALIDTIME               string  `json:"validtime"  xorm:"VALIDTIME"`                             // 有效期限
	VOLUMETYPE              int32   `json:"volumetype"  xorm:"VOLUMETYPE"`                           // 当时间有效类型为 “立即执行否则取消 IOC” 时，需要此项 - 0:任意量  1:最小量（暂时不支持） 2:全部量
	OPERATETYPE             int32   `json:"operatetype"  xorm:"OPERATETYPE"`                         // 操作类型 - 1:正常下单 2:斩仓 3:转单 4:结算撤单 5:系统卖出(适用于先平后建的卖出) 6:行情源报价 7:（结算)到期强平 8:（结算)协议转让 9:系统对冲单 10:（结算)到期无效 11:交割协议转让 12:交割协议平仓 13:交割成交(所有权) 14:管理端强行平仓 15:管理端协议转让 ... 23:融资买入
	ORDERTIME               string  `json:"ordertime"  xorm:"ORDERTIME2"`                            // 委托时间
	ORDERSRC                int32   `json:"ordersrc"  xorm:"ORDERSRC"`                               // 委托来源 -  1:客户端 2:管理端 3:风控服务 4:交割服务 5:交易服务 6:交易日结 7:商品强平 8:管理端商品退市强平 9:交易接口 10:交割服务商被动(受托竞价) 11:预埋触发
	ORDERSTATUS             int32   `json:"orderstatus"  xorm:"ORDERSTATUS"`                         // 委托状态 - 1： 委托请求 2：待冻结 3：委托成功 4： 委托失败 5：配对成功 6： 已撤销 7：部分成交 8：已成交 9：部成部撤 10：成交失败 11：已拒绝 12:经过摘牌(先摘后挂专用-先摘后挂已摘过) 13:冻结成功(通道交易专用) 14:通道已撤 15:通道部成部撤 16:成交失败违约(荷兰式竞拍专用)
	OPERATORID              int64   `json:"operatorid"  xorm:"OPERATORID"`                           // 登录账号(LoginID)
	UPDATETIME              string  `json:"updatetime"  xorm:"UPDATETIME"`                           // 更新时间
	CLIENTORDERTIME         string  `json:"clientordertime"  xorm:"CLIENTORDERTIME"`                 // 客户端委托时间
	CLIENTTICKET            string  `json:"clientticket"  xorm:"CLIENTTICKET"`                       // 客户端流水号
	UUID                    string  `json:"uuid"  xorm:"UUID"`                                       // 发起端唯一id
	CLIENTTYPE              int32   `json:"clienttype"  xorm:"CLIENTTYPE"`                           // 客户端类型 - 0:保留为未填终端类型 1:PC管理端 2:PC交易端 3:手机客户端_安卓 4:网页客户端 5:微信客户端 6:手机客户端_苹果 7:网上开户客户端 8:无效终端编号 9:报价终端(中江)
	RETCODE                 int32   `json:"retcode"  xorm:"RETCODE"`                                 // 错误代码
	TRADEPROPERTY           int32   `json:"tradeproperty"  xorm:"TRADEPROPERTY"`                     // 交易属性
	LISTINGSELECTTYPE       int32   `json:"listingselecttype"  xorm:"LISTINGSELECTTYPE"`             // 挂牌点选类型 - 1:挂牌 2:摘牌 3:先摘后挂
	DELISTINGTYPE           int32   `json:"delistingtype"  xorm:"DELISTINGTYPE"`                     // 摘牌类型 - 1:价格最优 2:点选成交
	MARGINALGORITHM         int32   `json:"marginalgorithm"  xorm:"MARGINALGORITHM"`                 // 保证金收取方式  1：比率  2：固定
	MARGINVALUE             float64 `json:"marginvalue"  xorm:"MARGINVALUE"`                         // 即市保证金设置值
	OPENFEEALGORITHM        int32   `json:"openfeealgorithm"  xorm:"OPENFEEALGORITHM"`               // 建仓手续费收取方式  1：比率  2：固定
	OPENMEMBERCHARGEVALUE   float64 `json:"openmemberchargevalue"  xorm:"OPENMEMBERCHARGEVALUE"`     // 建仓会员手续费设置值
	OPENEXCHAGECHARGEVALUE  float64 `json:"openexchagechargevalue"  xorm:"OPENEXCHAGECHARGEVALUE"`   // 建仓交易所手续费设置值
	CLOSEFEEALGORITHM       int32   `json:"closefeealgorithm"  xorm:"CLOSEFEEALGORITHM"`             // 平仓手续费收取方式 1：比率  2：固定

	QhjTradeEx `xorm:"extends"`

	IncludeSub int32  `json:"-"` // 是否包含子级 1-包含
	BeginDate  string `json:"-"` // 开始交易日
	EndDate    string `json:"-"` // 结束交易日
	FilterName string `json:"-"` // 模糊搜索
}

func (r *QhjMgrTradeOrderDetail) calc() {
	r.dealConvert()
}

func (r *QhjMgrTradeOrderDetail) buildSql(bQueryHis bool) 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," +
		"       ui.mobile," +
		"       g.goodsname," +
		"       g.goodscode," +
		"       g.goodunitid," +
		"       g.qtydecimalplace," +
		"       g.decimalplace," +
		"       g.agreeunit," +
		"       m.marketid," +
		"       m.trademode," +
		"       e.enumdicname," +
		"       to_char(t.ORDERID) ORDERID2," +
		"       to_char(t.ordertime, 'yyyy-mm-dd hh24:mi:ss') ORDERTIME2," +
		"       t.*" +
		"  from %v 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 market m" +
		"    on g.marketid = m.marketid" +
		"  left join enumdicitem e" +
		"    on e.enumitemname = g.goodunitid" +
		"   and e.enumdiccode = 'goodsunit'" +
		" where 1 = 1"
	if bQueryHis {
		sqlId.FormatParam("his_trade_orderdetail")
		sqlId.And("t.isvaliddata", 1)
	} else {
		sqlId.FormatParam("trade_orderdetail")
	}
	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("m.TRADEMODE", r.TRADEMODE, r.TRADEMODE > 0)
	sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0)
	if len(r.BeginDate) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE >=%v", r.BeginDate)
	}
	if len(r.EndDate) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE <=%v", r.EndDate)
	}
	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 *QhjMgrTradeOrderDetail) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrTradeOrderDetail, 0)
	sCurData := make([]QhjMgrTradeOrderDetail, 0)
	err := db.GetEngine().SQL(r.buildSql(false)).Find(&sCurData)
	sHisData := make([]QhjMgrTradeOrderDetail, 0)
	err = db.GetEngine().SQL(r.buildSql(true)).Find(&sHisData)
	sData2 := make([]QhjMgrTradeOrderDetailRsp, 0)
	sData = append(sData, sCurData...)
	sData = append(sData, sHisData...)
	for i := range sData {
		sData[i].calc()
		v := QhjMgrTradeOrderDetailRsp{QhjTradeEx: sData[i].QhjTradeEx}
		v.ACCOUNTID, v.ORDERID = sData[i].ACCOUNTID, sData[i].ORDERID
		v.ORDERPRICE, v.ORDERQTY = sData[i].ORDERPRICE, sData[i].ORDERQTY
		v.TRADEDATE, v.ORDERTIME = sData[i].TRADEDATE, sData[i].ORDERTIME
		v.GOODSID, v.ORDERSTATUS = sData[i].GOODSID, sData[i].ORDERSTATUS
		zoonFun := NewZoomFun(int(v.QTYDECIMALPLACE))
		zoonFun(&v.ORDERQTY)
		v.AMOUNT = v.ORDERPRICE * v.ORDERQTY * v.AGREEUNIT
		sData2 = append(sData2, v)
	}
	return sData2, err
}

// QhjMgrTradePayOrder 待付款单据
type QhjMgrTradePayOrder struct {
	TRADEID        string  `json:"tradeid"  xorm:"TRADEID"`                // 成交单号(101+Unix秒时间戳(10位)+2位(MarketServiceID)+xxxx)
	TRADEDATE      string  `json:"tradedate"  xorm:"TRADEDATE"`            // 交易日(yyyyMMdd)
	MARKETID       int32   `json:"marketid"  xorm:"MARKETID"`              // 市场ID
	GOODSID        int32   `json:"goodsid"  xorm:"GOODSID"`                // 商品ID
	BUYORDERID     string  `json:"buyorderid"  xorm:"BUYORDERID"`          // 买方委托单号(关联单号)
	BUYACCOUNTID   int64   `json:"buyaccountid"  xorm:"BUYACCOUNTID"`      // 买方账号ID[报价币种]
	SELLORDERID    string  `json:"sellorderid"  xorm:"SELLORDERID"`        // 卖方委托单号
	SELLACCOUNTID  int64   `json:"sellaccountid"  xorm:"SELLACCOUNTID"`    // 卖方账号ID[报价币种]
	TRADEAMOUNT    float64 `json:"tradeamount"  xorm:"TRADEAMOUNT"`        // 成交金额(优惠后金额)
	TRADECHARGE    float64 `json:"tradecharge"  xorm:"TRADECHARGE"`        // 成交手续费(买方)
	PAYFLAG        int32   `json:"payflag"  xorm:"PAYFLAG"`                // 付款标识 - 1:未支付 2:已支付 3:已过期 4:已撤销 5:结算过期 6:预售终止
	CREATETIME     string  `json:"createtime"  xorm:"CREATETIME2"`         // 创建时间
	PAYTIME        string  `json:"paytime"  xorm:"PAYTIME2"`               // 付款时间
	PAYLIMITEDTIME string  `json:"paylimitedtime"  xorm:"PAYLIMITEDTIME2"` // 支付期限
	OFFAMOUNT      float64 `json:"offamount"  xorm:"OFFAMOUNT"`            // 优惠金额
	TRADEPRICE     float64 `json:"tradeprice"  xorm:"TRADEPRICE"`          // 成交价格(价格)
	TRADEQTY       float64 `json:"tradeqty"  xorm:"TRADEQTY"`              // 成交数量(数量)
	PAYAMOUNT      float64 `json:"payamount"  xorm:"PAYAMOUNT"`            // 支付金额(金额)
	OPERATETYPE    int32   `json:"operatetype"  xorm:"OPERATETYPE"`        // 买委托操作类型 - 1:正常下单 16:融资买入
	ADVANCERATIO   float64 `json:"advanceratio"  xorm:"ADVANCERATIO"`      // 首付比率

	TRADETIME  string `json:"tradetime"` // 成交时间
	QhjTradeEx `xorm:"extends"`

	IncludeSub    int32  `json:"-"` // 是否包含子级 1-包含
	BeginDate     string `json:"-"` // 开始交易日
	EndDate       string `json:"-"` // 结束交易日
	FilterName    string `json:"-"` // 模糊搜索
	FilterPayFlag string `json:"-"` // 付款标识
}

func (r *QhjMgrTradePayOrder) calc() {
	ZoomByDecimal(int(r.QTYDECIMALPLACE), &r.TRADEQTY)
	r.TRADETIME = r.CREATETIME
	r.dealConvert()
}

func (r *QhjMgrTradePayOrder) 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," +
		"       ui.mobile," +
		"       g.goodsname," +
		"       g.goodscode," +
		"       g.goodunitid," +
		"       g.qtydecimalplace," +
		"       g.decimalplace," +
		"       g.agreeunit," +
		"       m.marketid," +
		"       m.trademode," +
		"       e.enumdicname," +
		"       to_char(t.BUYORDERID) BUYORDERID2," +
		"       to_char(t.TRADEID) TRADEID2," +
		"       to_char(t.Paylimitedtime, 'yyyy-mm-dd hh24:mi:ss') Paylimitedtime2," +
		"       to_char(t.PAYTIME, 'yyyy-mm-dd hh24:mi:ss') PAYTIME2," +
		"       to_char(t.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') CREATETIME2," +
		"       t.*" +
		"  from TRADE_PAYORDER t" +
		" inner join taaccount ta" +
		"    on t.buyaccountid = 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 market m" +
		"    on g.marketid = m.marketid" +
		"  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("m.TRADEMODE", r.TRADEMODE, r.TRADEMODE > 0)
	sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0)
	if len(r.FilterPayFlag) > 0 {
		sqlId.JoinFormat(" and t.PAYFLAG in(%v)", r.FilterPayFlag)
	}
	if len(r.BeginDate) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE >=%v", r.BeginDate)
	}
	if len(r.EndDate) > 0 {
		sqlId.JoinFormat(" and t.TRADEDATE <=%v", r.EndDate)
	}
	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 *QhjMgrTradePayOrder) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrTradePayOrder, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjMgrContract 千海金融资明细(合同)
type QhjMgrContract struct {
	USERID              int64   `json:"userid"  xorm:"USERID"`                             // 用户ID
	SCFCONTRACTID       string  `json:"scfcontractid"  xorm:"SCFCONTRACTID"`               // SCF合同ID(310+Unix秒时间戳(10位)+xxxxxx)
	SCFCONTRACTTYPE     int32   `json:"scfcontracttype"  xorm:"SCFCONTRACTTYPE"`           // 合同类型 - 1:仓单回购 2:仓单质押 3:合作套保 4:资金贷款 5:融资单
	GOODSID             int32   `json:"goodsid"  xorm:"GOODSID"`                           // 商品ID [5:融资单]
	CONTRACTCONFIRMTIME string  `json:"contractconfirmtime"  xorm:"CONTRACTCONFIRMTIME"`   // 合同确认时间(时间)
	WRQTY               float64 `json:"wrqty"  xorm:"WRQTY"`                               // 仓单数量(数量)
	TRADEPRICE          float64 `json:"tradeprice"  xorm:"TRADEPRICE"`                     // 成交价格(成本价格)
	LENDERAMOUNT        float64 `json:"lenderamount"  xorm:"LENDERAMOUNT"`                 // 贷款金额(总额|订单总额)
	MARGINRATIO         float64 `json:"marginratio"  xorm:"MARGINRATIO"`                   // 保证金比率(首付比例)
	INITMARGIN          float64 `json:"initmargin"  xorm:"'INITMARGIN'"`                   // 初始保证金(首付款)
	RECOVEREDMARGIN     float64 `json:"recoveredmargin"  xorm:"'RECOVEREDMARGIN'"`         // 已追缴保证金
	REMAINAMOUNT        float64 `json:"remainamount"  xorm:"'REMAINAMOUNT'"`               // 合同剩余金额(融资额)
	TOTALINTEREST       float64 `json:"totalinterest"  xorm:"'TOTALINTEREST'"`             // 已计总利息(利息)
	GOODSCODE           string  `json:"goodscode"  xorm:"'GOODSCODE'"`                     // 商品代码
	GOODSNAME           string  `json:"goodsname"  xorm:"'GOODSNAME'"`                     // 商品名称
	GOODUNITID          int32   `json:"goodunitid"  xorm:"'GOODUNITID'"`                   // 商品单位id
	CURRISKLEVEL        int32   `json:"currisklevel"  xorm:"'CURRISKLEVEL'"`               // 风险级别 - 1:正常 2:追加货款 3:支付尾款
	RECKONRECOVERMARGIN float64 `json:"reckonrecovermargin"  xorm:"'RECKONRECOVERMARGIN'"` // 结算应追缴保底金(结算时到过风险率时临时存储) - [仓单回购\仓单质押]
	DECIMALPLACE        int32   `json:"decimalplace"  xorm:"'DECIMALPLACE'"`               // 商品报价小数位
	SCFCONTRACTSTATUS   int32   `json:"scfcontractstatus"  xorm:"'SCFCONTRACTSTATUS'"`     // 合同状态 - 1:待确认 2:已确认 3:确认拒绝 4:已支付保证金 5:已激活 6:已违约 7:到期结束 8:延期结束 9:违约结束 10:已注销 11:已关闭 99:未提交
	LOGINCODE           string  `json:"logincode"  xorm:"'LOGINCODE'"`                     // 账号(登录代码)
	USERINFOTYPE        int32   `json:"userinfotype"  xorm:"'USERINFOTYPE'"`               // 用户类型 1-个人 2-企业
	MOBILE              string  `json:"mobile"  xorm:"'MOBILE'"`                           // 电话号码(用户资料里的电话)
	TRADEDATE           string  `json:"tradedate"  xorm:"'TRADEDATE'"`                     // 交易日

	PAYAMOUNT   float64 `json:"payamount"  xorm:"'PAYAMOUNT'"` // 已付(已付货款)
	USERNAME    string  `json:"username"  xorm:"'USERNAME'"`   // 用户名称
	ENUMDICNAME string  `json:"enumdicname"`                   // 单位名称

	BeginDate       string `json:"-"` // 开始交易日
	EndDate         string `json:"-"` // 结束交易日
	FilterName      string `json:"-"` // 模糊搜索
	FilterRiskLevel string `json:"-"` // 风险级别
}

func (r *QhjMgrContract) calc() {
	r.USERNAME = mtpcache.GetUserNameByUserId(r.USERID)
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.GOODUNITID)
	// 已付(已付货款) = 已追缴保证金  + 初始保证金
	r.PAYAMOUNT = r.RECOVEREDMARGIN + r.INITMARGIN
	if r.SCFCONTRACTSTATUS == 10 || r.SCFCONTRACTSTATUS == 11 {
		// 当合同关闭时, 利息重置为0
		r.TOTALINTEREST = 0
	}
	if r.LOGINCODE == "" {
		r.LOGINCODE = DecryptField(r.MOBILE)
	}
}

func (r *QhjMgrContract) 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 t.borroweruserid userid," +
		"       to_char(t.scfcontractid) scfcontractid," +
		"       t.scfcontracttype," +
		"       t.goodsid," +
		"       to_char(t.contractconfirmtime, 'yyyy-mm-dd hh24:mi:ss') contractconfirmtime," +
		"       t.wrqty," +
		"       t.tradeprice," +
		"       t.lenderamount," +
		"       t.marginratio," +
		"       s.initmargin," +
		"       s.recoveredmargin," +
		"       s.remainamount," +
		"       s.totalinterest," +
		"       s.currisklevel," +
		"       s.reckonrecovermargin," +
		"       s.scfcontractstatus," +
		"       s.tradedate," +
		"       g.goodscode," +
		"       g.goodsname," +
		"       g.decimalplace," +
		"       g.goodunitid," +
		"       ui.userinfotype," +
		"       ui.mobile," +
		"       u.accountname," +
		"       k.logincode" +
		"  from scf_contract t" +
		"  left join scf_contractinfo s" +
		"    on t.scfcontractid = s.scfcontractid" +
		"  left join goods g" +
		"    on t.goodsid = g.goodsid" +
		"  left join userinfo ui on t.borroweruserid=ui.userid" +
		"  left join k on ui.userid=k.userid" +
		"  left join useraccount u on t.borroweruserid=u.userid" +
		" where 1 = 1"
	sqlId.AndEx("t.borroweruserid", r.USERID, r.USERID > 0)
	sqlId.AndEx("ui.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0)
	sqlId.AndEx("t.goodsid", r.GOODSID, r.GOODSID > 0)
	if len(r.FilterRiskLevel) > 0 {
		sqlId.JoinFormat(" and s.currisklevel in(%v)", r.FilterRiskLevel)
	}
	if len(r.BeginDate) > 0 {
		sqlId.JoinFormat(" and s.TRADEDATE >=%v", r.BeginDate)
	}
	if len(r.EndDate) > 0 {
		sqlId.JoinFormat(" and s.TRADEDATE <=%v", r.EndDate)
	}
	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 *QhjMgrContract) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrContract, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjMgrPickGoods 提货商品
type QhjMgrPickGoods struct {
	PICKUPGOODSID        string  `json:"pickupgoodsid"  xorm:"PICKUPGOODSID"`               // 提货商品ID
	PICKUPGOODSNAME      string  `json:"pickupgoodsname"  xorm:"PICKUPGOODSNAME"`           // 提货商品名称
	PICKUPGOODSUNITID    int32   `json:"pickupgoodsunitid"  xorm:"PICKUPGOODSUNITID"`       // 单位ID
	PICKUPGOODSAGREEUNIT float64 `json:"pickupgoodsagreeunit"  xorm:"PICKUPGOODSAGREEUNIT"` // 合约乘数
	PICKUPGOODSDESC      string  `json:"pickupgoodsdesc"  xorm:"PICKUPGOODSDESC"`           // 描述
	IMAGEADDRESS         string  `json:"imageaddress"  xorm:"IMAGEADDRESS"`                 // 图片
	REMARK               string  `json:"remark"  xorm:"REMARK"`                             // 备注
	MODIFYSRC            int32   `json:"modifysrc"  xorm:"MODIFYSRC"`                       // 修改来源 - 1:管理端 2:终端
	MODIFIERID           int64   `json:"modifierid"  xorm:"MODIFIERID"`                     // 修改人
	UPDATETIME           string  `json:"updatetime"  xorm:"UPDATETIME"`                     // 更新时间
	PREPAREDAYS          int32   `json:"preparedays"  xorm:"PREPAREDAYS"`                   // 备货期(天)
	PICKUPGOODSSTATUS    int32   `json:"pickupgoodsstatus"  xorm:"PICKUPGOODSSTATUS"`       // 状态 - 1:正常 2:停用 3:注销
	GOODSID              int32   `json:"goodsid"  xorm:"'GOODSID'"`                         // 商品id
	PICKUPRATIO          float64 `json:"pickupratio"  xorm:"'PICKUPRATIO'"`                 // 提货系数

	MODIFIERNAME string `json:"modifiername"` // 修改人名称
	FilterStatus string `json:"-"`            // 状态过淲
	ENUMDICNAME  string `json:"enumdicname"`  // 单位名称(提货商品)
}

func (r *QhjMgrPickGoods) calc() {
	if r.MODIFYSRC == 1 {
		r.MODIFIERNAME = mtpcache.GetSystemmangerLoginCode(r.MODIFIERID)
	} else {
		r.MODIFIERNAME = mtpcache.GetLoginCodeByLoginId(r.MODIFIERID)
	}
	if r.MODIFIERNAME == "" {
		r.MODIFIERNAME = mtpcache.GetUserNameByUserId(r.MODIFIERID)
	}
	r.ENUMDICNAME = mtpcache.GetEnumDicitemName(r.PICKUPGOODSUNITID)
}

func (r *QhjMgrPickGoods) buildSql() string {
	var sqlId utils.SQLVal = "SELECT t.PICKUPGOODSID," +
		"       t.PICKUPGOODSNAME," +
		"       t.PICKUPGOODSUNITID," +
		"       t.PICKUPGOODSAGREEUNIT," +
		"       to_char(t.PICKUPGOODSDESC) PICKUPGOODSDESC," +
		"       to_char(t.IMAGEADDRESS) IMAGEADDRESS," +
		"       t.REMARK," +
		"       t.MODIFYSRC," +
		"       t.MODIFIERID," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       t.PREPAREDAYS," +
		"       t.PICKUPGOODSSTATUS," +
		"  FROM PICKUPGOODS t" +
		" WHERE 1 = 1"
	if len(r.FilterStatus) > 0 {
		sqlId.Join(fmt.Sprintf(" and t.PICKUPGOODSSTATUS in(%v)", r.FilterStatus))
	}
	return sqlId.String()
}

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

// 经纪人扩展信息
type BrokerExInfo struct {
	USERNAME         string `json:"username"  xorm:"'USERNAME'"`                 // 用户名称
	PARENTUSERID     int64  `json:"parentuserid"  xorm:"'PARENTUSERID'"`         // 所属机构id
	USERINFOTYPE     int32  `json:"userinfotype"  xorm:"'USERINFOTYPE'"`         // 用户类型  1-个人 2-企业
	MOBILE           string `json:"mobile"  xorm:"'MOBILE'"`                     // 手机号码
	PARENTUSERNAME   string `json:"parentusername"  xorm:"'PARENTUSERNAME'"`     // 所属机构名称
	LOGINCODE        string `json:"logincode"  xorm:"'LOGINCODE'"`               // 登录代码
	SUBAREALEVELPATH string `json:"subarealevelpath"  xorm:"'SUBAREALEVELPATH'"` // 层级路径
	REFEREEUSERID    int64  `json:"refereeuserid"  xorm:"'REFEREEUSERID'"`       // 推荐人id(经纪人)
	REFERNUM         string `json:"refernum"  xorm:"'REFERNUM'"`                 // 推荐码
}

// QhjMgrBrokerApply 经济人申请表
type QhjMgrBrokerApply struct {
	APPLYSRC          int32  `json:"applysrc"  xorm:"APPLYSRC" form:"applysrc"`                            // 申请来源 - 1:管理端 2:终端
	APPLYSTATUS       int32  `json:"applystatus"  xorm:"APPLYSTATUS" form:"applystatus"`                   // 申请状态 - 1:待审核 2:审核通过 3:审核拒绝 4:处理失败 5:已撤回
	AUDITSRC          int32  `json:"auditsrc"  xorm:"AUDITSRC" form:"auditsrc"`                            // 审核来源 - 1:管理端 2:终端
	APPLICANTID       int64  `json:"applicantid"  xorm:"APPLICANTID" form:"applicantid"`                   // 申请人
	AUDITORID         int64  `json:"auditorid"  xorm:"AUDITORID" form:"auditorid"`                         // 审核人
	BROKERAPPLYID     int64  `json:"brokerapplyid"  xorm:"BROKERAPPLYID" form:"brokerapplyid"`             // 申请ID(SEQ_BROKERAPPLY)
	USERID            int64  `json:"userid"  xorm:"USERID" form:"userid"`                                  // 申请人用户ID
	APPLYTIME         string `json:"applytime"  xorm:"APPLYTIME" form:"applytime"`                         // 申请时间
	AUDITREMARK       string `json:"auditremark"  xorm:"AUDITREMARK" form:"auditremark"`                   // 审核备注
	AUDITTIME         string `json:"audittime"  xorm:"AUDITTIME" form:"audittime"`                         // 审核时间
	AUDITTRADEDATE    string `json:"audittradedate"  xorm:"AUDITTRADEDATE" form:"audittradedate"`          // 审核交易日(yyyyMMdd)
	CARDBACKPHOTOURL  string `json:"cardbackphotourl"  xorm:"CARDBACKPHOTOURL" form:"cardbackphotourl"`    // 证件背面图片地址
	CARDFRONTPHOTOURL string `json:"cardfrontphotourl"  xorm:"CARDFRONTPHOTOURL" form:"cardfrontphotourl"` // 证件正面图片地址
	CARDNUM           string `json:"cardnum"  xorm:"CARDNUM" form:"cardnum"`                               // 证件号码(加密存储) 注:数据库没存证件类型字段,产品说类型固定为身份证
	CUSTOMERNAME      string `json:"customername"  xorm:"CUSTOMERNAME" form:"customername"`                // 姓名
	REMARK            string `json:"remark"  xorm:"REMARK" form:"remark"`                                  // 备注
	TRADEDATE         string `json:"tradedate"  xorm:"TRADEDATE" form:"tradedate"`                         // 交易日(yyyyMMdd)
	BrokerExInfo      `xorm:"extends"`

	IncludeSub   int32  `json:"-" form:"includesub"`              // 是否包含子级 1-包含
	FilterName   string `json:"-" form:"filtername"`              // 模糊搜索名称
	FilterStatus string `json:"filterstatus" form:"filterstatus"` // 筛选条件
}

func (r *QhjMgrBrokerApply) calc() {
	r.MOBILE = DecryptField(r.MOBILE)
	r.CARDNUM = DecryptField(r.CARDNUM)
	if r.LOGINCODE == "" {
		r.LOGINCODE = r.MOBILE
	}
}

func (r *QhjMgrBrokerApply) 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 t.CUSTOMERNAME," +
		"       t.CARDNUM," +
		"       t.CARDFRONTPHOTOURL," +
		"       t.CARDBACKPHOTOURL," +
		"       t.BROKERAPPLYID," +
		"       t.USERID," +
		"       t.TRADEDATE," +
		"       t.APPLYSRC," +
		"       t.APPLICANTID," +
		"       t.REMARK," +
		"       to_char(t.APPLYTIME, 'yyyy-mm-dd hh24:mi:ss') APPLYTIME," +
		"       t.APPLYSTATUS," +
		"       t.AUDITTRADEDATE," +
		"       t.AUDITORID," +
		"       t.AUDITSRC," +
		"       to_char(t.AUDITTIME, 'yyyy-mm-dd hh24:mi:ss') AUDITTIME," +
		"       t.AUDITREMARK," +
		"       u.accountname USERNAME," +
		"       u.parentuserid," +
		"       u.refereeuserid," +
		"       u.refernum," +
		"       ui.userinfotype," +
		"       ui.mobile," +
		"       u2.accountname PARENTUSERNAME," +
		"       k.logincode," +
		"       u.subarealevelpath" +
		"  FROM BROKERAPPLY t" +
		"  INNER JOIN USERACCOUNT u on t.userid=u.userid" +
		"  INNER JOIN USERINFO ui on t.userid=ui.userid" +
		"  LEFT JOIN k on t.userid=k.userid" +
		"  LEFT JOIN USERACCOUNT u2 on u.parentuserid=u2.userid" +
		" WHERE 1 = 1"
	if r.IncludeSub == 1 {
		sqlId.JoinFormat(" and u.subarealevelpath like '%%,%v,%%,%%'", r.USERID)
	} else {
		sqlId.And("u.parentuserid", r.USERID)
	}
	sqlId.JoinEx(r.FilterStatus != "", fmt.Sprintf(" and t.APPLYSTATUS in(%v)", r.FilterStatus))
	sqlId.AndEx("ui.USERINFOTYPE", r.USERINFOTYPE, r.USERINFOTYPE > 0)
	if len(r.FilterName) > 0 {
		sqlId.JoinFormat(" and (k.logincode like '%%%v%%' or t.CUSTOMERNAME like '%%%v%%')", r.FilterName, r.FilterName)
	}
	return sqlId.String()
}

// GetDataEx 获取经济人申请表
func (r *QhjMgrBrokerApply) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrBrokerApply, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}

// QhjMgrBrokerRewardReport 经纪人奖励报表
type QhjMgrBrokerRewardReport struct {
	CYCLETIME         string  `json:"cycletime"  xorm:"CYCLETIME" form:"cycletime"`                         // 周期时间 日(YYYYMMDD) 月(YYYYMM)  季(YYYYQ) 年(YYYY) 周(YYYYMMDD) 全(0)【原值】
	CYCLETYPE         int32   `json:"cycletype"  xorm:"CYCLETYPE" form:"cycletype"`                         // 周期类型 - 0:日 1:月 2:季 3:年 4:周 5:全报表【原值】
	USERID            int64   `json:"userid"  xorm:"USERID" form:"userid"`                                  // 经纪人用户ID
	DIRECTTOTALNUM    int32   `json:"directtotalnum"  xorm:"DIRECTTOTALNUM" form:"directtotalnum"`          // 直推总人数【期末】
	INDIRECTTOTALNUM  int32   `json:"indirecttotalnum"  xorm:"INDIRECTTOTALNUM" form:"indirecttotalnum"`    // 间推总人数【期末】
	TODAYREWARDAMOUNT float64 `json:"todayrewardamount"  xorm:"TODAYREWARDAMOUNT" form:"todayrewardamount"` // 奖励总金额【汇总】
	UPDATETIME        string  `json:"updatetime"  xorm:"UPDATETIME" form:"updatetime"`                      // 更新时间
	USERNAME          string  `json:"username"  xorm:"'USERNAME'"`                                          // 用户名称
	REFEREEUSERID     int64   `json:"refereeuserid"  xorm:"'REFEREEUSERID'"`                                // 推荐人id(经纪人id)
	REFERNUM          string  `json:"refernum"  xorm:"'REFERNUM'"`                                          // 推荐码
	SUBAREALEVELPATH  string  `json:"subarealevelpath"  xorm:"'SUBAREALEVELPATH'"`                          // 用户层级路径
	CREATETIME        string  `json:"createtime"  xorm:"'CREATETIME'"`                                      // 创建时间(注册时间)
	USERINFOTYPE      int32   `json:"userinfotype"  xorm:"'USERINFOTYPE'"`                                  // 用户类型  1-个人 2-企业
	MOBILE            string  `json:"mobile"  xorm:"'MOBILE'"`                                              // 电话号码
	REFEREEUSERNAME   string  `json:"refereeusername"  xorm:"'REFEREEUSERNAME'"`                            // 推荐人名称(经纪人) 上级经纪人名称

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

func (r *QhjMgrBrokerRewardReport) calc() {
	r.MOBILE = DecryptField(r.MOBILE)
}

func (r *QhjMgrBrokerRewardReport) sqlDay() string {
	var sqlId utils.SQLVal = "SELECT t.reckondate as CYCLETIME," +
		"       0 as CYCLETYPE," +
		"       t.USERID," +
		"       t.DIRECTTOTALNUM," +
		"       t.INDIRECTTOTALNUM," +
		"       t.TODAYREWARDAMOUNT," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       u.accountname USERNAME," +
		"       u.refereeuserid," +
		"       u.refernum," +
		"       u.subarealevelpath," +
		"       to_char(u.createtime, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       ui.userinfotype," +
		"       ui.mobile," +
		"       u2.accountname REFEREEUSERNAME" +
		"  FROM RECKON_BROKERREWARD t" +
		"  INNER JOIN USERACCOUNT u on t.userid=u.userid" +
		"  INNER JOIN USERINFO ui on t.userid=ui.userid" +
		"  LEFT JOIN USERACCOUNT u2 on u.refereeuserid=u2.userid" +
		" WHERE 1 = 1"

	return sqlId.String()
}

func (r *QhjMgrBrokerRewardReport) sqlCycle() string {
	var sqlId utils.SQLVal = "SELECT t.CYCLETIME," +
		"       t.CYCLETYPE," +
		"       t.USERID," +
		"       t.DIRECTTOTALNUM," +
		"       t.INDIRECTTOTALNUM," +
		"       t.TODAYREWARDAMOUNT," +
		"       to_char(t.UPDATETIME, 'yyyy-mm-dd hh24:mi:ss') UPDATETIME," +
		"       u.accountname USERNAME," +
		"       u.refereeuserid," +
		"       u.refernum," +
		"       u.subarealevelpath," +
		"       to_char(u.createtime, 'yyyy-mm-dd hh24:mi:ss') CREATETIME," +
		"       ui.userinfotype," +
		"       ui.mobile," +
		"       u2.accountname REFEREEUSERNAME" +
		"  FROM REPORT_BROKERREWARD t" +
		"  INNER JOIN USERACCOUNT u on t.userid=u.userid" +
		"  INNER JOIN USERINFO ui on t.userid=ui.userid" +
		"  LEFT JOIN USERACCOUNT u2 on u.refereeuserid=u2.userid" +
		" WHERE 1 = 1"

	return sqlId.String()
}

func (r *QhjMgrBrokerRewardReport) buildSql() string {
	var sqlId utils.SQLVal
	if r.CYCLETYPE == 0 {
		sqlId = utils.SQLVal(r.sqlDay())
		sqlId.AndEx("t.reckondate", r.CYCLETIME, r.CYCLETIME != "")
	} else {
		sqlId = utils.SQLVal(r.sqlCycle())
		sqlId.And("t.CYCLETYPE", r.CYCLETYPE)
		// 转换时间
		// 兼容性处理, 月报表日期如果是yyyymmdd, 只取yyyymm
		if r.CYCLETYPE == 1 && len(r.CYCLETIME) > 6 {
			r.CYCLETIME = r.CYCLETIME[:6]
		} else if r.CYCLETYPE == 4 {
			r.CYCLETIME = mtpcache.GetWeekIW(r.CYCLETIME)
		}
		sqlId.And("t.CYCLETIME", r.CYCLETIME)
	}
	if r.IncludeSub == 1 {
		//sqlId.JoinFormat(" and u.subarealevelpath like '%%,%v,%%,%%'", r.USERID)
		// 要用 refereeuserid 递归查询, 不能用subarealevelpath
	} else {
		sqlId.And("u.refereeuserid", r.USERID)
	}
	if len(r.FilterName) > 0 {
		sqlId.JoinFormat(" and u.accountname like '%%%v%%'", r.FilterName)
	}

	return sqlId.String()
}

// GetDataEx 获取经纪人奖励报表
func (r *QhjMgrBrokerRewardReport) GetDataEx() (interface{}, error) {
	sData := make([]QhjMgrBrokerRewardReport, 0)
	err := db.GetEngine().SQL(r.buildSql()).Find(&sData)
	for i := range sData {
		sData[i].calc()
	}
	return sData, err
}