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
}