<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.muchinfo.mtp3interface.mapper.IUseraccountMapper">

    <resultMap id="PageResultMap" type="com.muchinfo.mtp3pojos.vo.organ.UserAccountRspResult">
        <id column="USERID" property="userid" />
        <result column="USERTYPE" property="usertype" />
        <result column="MODIFYSTATUS" property="modifystatus" />
        <result column="ACCOUNTNAME" property="accountname" />
        <result column="PARENTUSERID" property="parentuserid" />
        <result column="BROKER" property="broker" />
        <result column="SUBAREALEVELPATH" property="subarealevelpath" />
        <result column="REFEREEUSERID" property="refereeuserid" />
        <result column="MEMBERUSERID" property="memberuserid" />
        <result column="ACCOUNTSTATUS" property="accountstatus" />
        <result column="RECKONACCOUNTID" property="reckonaccountid" />
        <result column="CREATETIME" property="createtime" />
        <result column="CREATORID" property="creatorid" />
        <result column="AUDITTIME" property="audittime" />
        <result column="AUDITUSERID" property="audituserid" />
        <result column="AUDITREMARK" property="auditremark" />
        <result column="CANCELTIME" property="canceltime" />
        <result column="CANCELUSERID" property="canceluserid" />
        <result column="MODIFYTIME" property="modifytime" />
        <result column="MODIFIERID" property="modifierid" />
        <result column="MODIFYREMARK" property="modifyremark" />
        <result column="MAXINVESTORNUM" property="maxinvestornum" />
        <result column="HASAUTH" property="hasauth" />
        <result column="REFERCOUNT" property="refercount" />
        <result column="REFERNUM" property="refernum" />
        <result column="ISANONYMOUS" property="isanonymous" />
        <result column="PARENTTOPUSER" property="parenttopuser" />
        <result column="SUBACCOUNTLEVEL" property="subaccountlevel" />
        <result column="APPLYSRC" property="applysrc" />
        <result column="AUDITSRC" property="auditsrc" />
        <result column="ROOTUSERID" property="rootuserid" />
        <result column="CANRECOMMEND" property="canrecommend" />
        <result column="CREATETRADEDATE" property="createtradedate" />
        <result column="SECREFERCOUNT" property="secrefercount" />
        <result column="HASUPLOADED" property="hasuploaded" />
        <result column="UPLOADEDTIME" property="uploadedtime" />
        <result column="OUTERUSERID" property="outeruserid" />
        <result column="TEAMMANAGERUSERID" property="teammanageruserid" />
        <result column="SUBJECTID" property="subjectid" />
        <result column="CREDITQUOTA" property="creditquota" />
        <result column="MODIFYSRC" property="modifysrc" />
        <result column="SJACCOUNTNO" property="sjaccountno" />
        <association property="userName" column="CREATORID"
                     jdbcType="DECIMAL"
                     select="com.muchinfo.mtp3interface.mapper.ISystemmanagerMapper.selectUsersName"></association>
        <association property="parentname" column="PARENTUSERID"
                     jdbcType="VARCHAR" select="selectOrganName"></association>
        <association property="memberusername" column="MEMBERUSERID"
                     jdbcType="VARCHAR"
                     select="selectOrganName"></association>
        <association property="roleIds" column="USERID" jdbcType="VARCHAR"
                     javaType="java.lang.String"
                     select="com.muchinfo.mtp3interface.mapper.IArearoleMapper.getRolesByAreaid"></association>
    </resultMap>
    <!-- 子机构开户推荐人下拉列表 -->
    <resultMap id="investorResultMap" type="com.muchinfo.mtp3pojos.vo.common.InvestorSelectResult">
        <id column="userid" property="userId" />
        <result column="accountname" property="accountName" />
        <result column="loginid" property="loginId" />
    </resultMap>
    <!-- 投资者机构分页查询 -->
    <resultMap id="investorPageResultMap" type="com.muchinfo.mtp3pojos.vo.investor.DealerQueryResult">
        <id column="USERID" property="userId" />
        <result column="loginId" property="loginId" />
        <result column="accountId" property="accountId" />
        <result column="refereeName" property="refereeName" />
        <result column="accountName" property="accountName" />
        <result column="hasauth" property="hasAuth" />
        <result column="accountstatus" property="accountStatus" />
        <result column="modifystatus" property="modifyStatus" />
        <result column="createtime" property="createTime" />
        <result column="modifytime" property="modifyTime" />
        <result column="audittime" property="auditTime" />
        <result column="canceltime" property="cancelTime" />
        <association property="memberUserName" column="MEMBERUSERID"
                     jdbcType="VARCHAR" select="selectOrganName"></association>
        <association property="parentName" column="PARENTUSERID"
                     jdbcType="VARCHAR" select="selectOrganName"></association>
    </resultMap>
    <!-- 交易商详情返回 -->
    <resultMap id="DetailResultMap" type="com.muchinfo.mtp3pojos.vo.investor.UserAccountDetailVo">
        <!-- 变更表 -->
        <result column="userid" property="userId" />
        <result column="accountname" property="accountName"/>
        <result column="createtime" property="createTime" />
        <result column="modifytime" property="modifyTime"/>
        <result column="modifystatus" property="modifyStatus" />
        <result column="hasauth" property="hasAuth" />
        <result column="modifyremark" property="modifyRemark" />
        <result column="audittime" property="auditTime" />
        <result column="accountstatus" property="accountStatus" />
        <result column="parentuserid" property="parentuserid" />
        <result column="usertype" property="usertype" />
        <result column="refereeuserid" property="refereeuserid" />
        <result column="auditremark" property="auditremark" />
        <result column="newcontent" property="newcontent" />
        <result column="oldcontent" property="oldcontent" />
        <association property="auditAccountName" column="AUDITUSERID"
                     jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.ISystemmanagerMapper.selectUsersName"></association>
        <association property="createName" column="CREATORID"
                     jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.ISystemmanagerMapper.selectUsersName"></association>
        <association property="modifyName" column="MODIFIERID"
                     jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.ISystemmanagerMapper.selectUsersName"></association>
        <association property="memberUserName" column="MEMBERUSERID"
                     jdbcType="VARCHAR" select="selectOrganName"></association>
        <association property="borkerName" column="BROKER"
                     jdbcType="VARCHAR" select="selectOrganName"></association>
        <association property="parentUserName" column="PARENTUSERID"
                     jdbcType="VARCHAR" select="selectOrganName"></association>
        <association property="refereeUserName" column="REFEREEUSERID"
                     jdbcType="VARCHAR" select="selectOrganName"></association>
    </resultMap>

    <resultMap id="InvestorModifyResult" type="com.muchinfo.mtp3pojos.vo.investor.InvestorModifyResult">
        <association property="memberusername" column="MEMBERUSERID"
                     jdbcType="VARCHAR"
                     select="selectOrganName"></association>
        <association property="parentname" column="PARENTUSERID"
                     jdbcType="VARCHAR"
                     select="selectOrganName"></association>
    </resultMap>
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        USERID, USERTYPE, MODIFYSTATUS, ACCOUNTNAME, PARENTUSERID, BROKER, SUBAREALEVELPATH, REFEREEUSERID, MEMBERUSERID, ACCOUNTSTATUS, RECKONACCOUNTID, CREATETIME, CREATORID, AUDITTIME, AUDITUSERID, AUDITREMARK, CANCELTIME, CANCELUSERID, MODIFYTIME, MODIFIERID, MODIFYREMARK, MAXINVESTORNUM, HASAUTH, REFERCOUNT, REFERNUM, ISANONYMOUS, PARENTTOPUSER, SUBACCOUNTLEVEL, APPLYSRC, AUDITSRC, ROOTUSERID, CANRECOMMEND, CREATETRADEDATE, SECREFERCOUNT, HASUPLOADED, UPLOADEDTIME, OUTERUSERID, TEAMMANAGERUSERID, SUBJECTID, CREDITQUOTA, MODIFYSRC, SJACCOUNTNO
    </sql>
    <delete id="deleteSubAreaGroupDetailByUserid" parameterType="java.lang.Long">
        delete
        from subareagroupdetail t
        where t.subareagroupid in
              (select sg.subareagroupid from subareagroup sg where sg.areauserid = #{userid})
    </delete>

    <select id="queryOrganSelect"
            parameterType="com.muchinfo.mtp3pojos.vo.common.OrganSelectParam"
            resultType="com.muchinfo.mtp3pojos.vo.common.OrganSelectResult">
        select aa.* from (
        <choose>
            <when test="vo.roles != null and vo.roles !='' ">
                select distinct o.userid ,o.memberuserid, o.accountname, o.parentuserid,o.usertype usertype
                from
                useraccount o,arearole r
            </when>
            <otherwise>
                select o.userid ,o.memberuserid, o.accountname, o.parentuserid ,o.usertype usertype
                from
                useraccount o
            </otherwise>
        </choose>
        <where>
            1=1
            and o.accountstatus = 4
            <if test="vo.accountname != null and vo.accountname !=''">
                and (o.accountname like
                '%'||#{vo.accountname,jdbcType=VARCHAR}||'%' or o.userid like
                '%'||#{vo.accountname,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.roles != null and vo.roles !=''">
                and o.userid = r.userid and r.roletype in (${vo.roles})
            </if>
            <if test="vo.exists != null and vo.exists !=''">
                and not exists (select 1 from arearole t where o.userid
                =
                t.userid and t.roletype in (${vo.exists}))
            </if>
            <if test="vo.companyexists != null and vo.companyexists !=''">
                and not exists (select 1 from MS_ExtendRole t where o.userid
                = t.areauserid and t.roletypeid in (${vo.companyexists}))
            </if>
            <choose>
                <when test="vo.allUsersButPlatform !=null and vo.allUsersButPlatform == 1">
                    and o.usertype > 1
                </when>
                <otherwise>
                    <if test="vo.usertype != null and vo.usertype != '' ">
                        and o.usertype in (${vo.usertype})
                    </if>
                    <if test="vo.parentuserid != null">
                        start with o.userid = #{vo.parentuserid}
                        connect by prior o.userid = o.parentuserid and o.usertype!=2
                    </if>
                    <if test="vo.planformanceuserid != null">
                        and o.parentuserid in(select userid from useraccount ua
                        start with ua.userid = #{vo.planformanceuserid} connect by prior ua.userid = ua.parentuserid
                        and ua.userid != #{vo.planformanceuserid})
                    </if>
                    <if test="vo.parentuseridfirst != null">
                        and o.parentuserid = #{vo.parentuseridfirst}
                    </if>
                    <if test="vo.memberuserid !=null">
                        and (o.memberuserid = #{vo.memberuserid} or o.userid = #{vo.memberuserid})
                    </if>
                </otherwise>
            </choose>
            <if test="vo.userid != null and vo.userid !=''">
                and o.userid = #{vo.userid}
            </if>
            <if test="vo.orgtype != null and vo.orgtype !=''">
                and r.orgtype = #{vo.orgtype}
            </if>
            <if test="vo.subarealevelpath != null and vo.subarealevelpath !=''">
                and o.subarealevelpath like #{vo.subarealevelpath}||'%'
            </if>
        </where>
        order by o.userid ) aa
        <choose>
            <when test="vo.pageSize == 0">
                where 1 = 1
            </when>
            <otherwise>
                where 1 = 1  and rownum &lt;= #{vo.pageSize}
            </otherwise>
        </choose>
    </select>


    <select id="queryOrganPage" resultMap="PageResultMap"
            parameterType="com.muchinfo.mtp3pojos.vo.organ.UserAccountReqParam">
        with tmp as (
        SELECT T.userid, (LISTAGG(T.accountid, ',') WITHIN GROUP (ORDER BY T.accountid)) AS ACCOUNTID FROM taaccount T GROUP BY T.userid
        )
        select
        (select max(t.modifytime) from arearole t where t.userid=o.userid) rolemodifytime,
        tmp.accountid, o.*
        from useraccount o
        left join tmp on o.userid = tmp.userid
        <where>
            <if test="vo.accountname != null and vo.accountname != '' ">
                and ((o.accountname like
                '%'||#{vo.accountname}||'%')
                or(o.userid like '%'||#{vo.accountname}||'%'))
            </if>
            <if test="vo.usertype != null">
                and o.usertype = #{vo.usertype}
            </if>
            <if test="vo.accountstatus != null">
                and o.accountstatus = #{vo.accountstatus}
            </if>
            <if test="vo.parentuserid != null">
                and o.parentuserid = #{vo.parentuserid}
            </if>
            <if test="vo.memberuserid != null">
                and o.memberuserid = #{vo.memberuserid}
            </if>
            <if test="vo.roles != null and vo.roles !='' ">
                and o.userid in (select userid from arearole where roletype in (${vo.roles}))
            </if>
            <if test="vo.starttime != null and vo.starttime !='' ">
                <![CDATA[and o.createtime >= to_date(#{vo.starttime}||' 00:00:00', 'yyyy-mm-dd hh24:mi:ss')]]>
            </if>
            <if test="vo.endtime != null and vo.endtime !='' ">
                <![CDATA[and o.createtime <= to_date(#{vo.endtime}||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')]]>
            </if>
            <if test="vo.markets != null and vo.markets !='' ">
                and o.userid in (select t.areauserid from arearolemarket t where t.marketid in (${vo.markets}))
            </if>
        </where>
        order by o.accountstatus,o.userid
    </select>

    <select id="querySonOrganPage" resultMap="PageResultMap"
            parameterType="com.muchinfo.mtp3pojos.vo.organ.UserAccountReqParam">
        select o.*,
        i.userinfotype,
        ua.userid || '/' || ua.accountname as parentname,
        ua1.userid || '/' || ua1.accountname as memberusername
        from (SELECT (LEVEL - 1) subarealevel, t.*
        FROM useraccount t
        where t.usertype in (3,4)
        START
        WITH t.userid = 1
        CONNECT BY PRIOR t.userid = t.parentuserid
        ORDER BY LEVEL) o
        left join userinfo i on o.userid = i.userid
        left join useraccount ua on o.parentuserid = ua.userid
        left join useraccount ua1 on o.memberuserid = ua1.userid
        <where>
            1=1
            and o.usertype in (3,4)
            <if test="vo.accountname != null and vo.accountname != '' ">
                and ((o.accountname like
                '%'||#{vo.accountname,jdbcType=VARCHAR}||'%')
                or(o.userid like '%'||#{vo.accountname,jdbcType=VARCHAR}||'%'))
            </if>
            <if test="vo.usertype != null">
                and o.usertype = #{vo.usertype}
            </if>
            <if test="vo.accountstatus != null">
                and o.accountstatus = #{vo.accountstatus}
            </if>
            <if test="vo.parentuserid != null">
                and o.parentuserid = #{vo.parentuserid}
            </if>
            <if test="vo.subarealevel != null and vo.subarealevel != ''">
                and o.subarealevel = #{vo.subarealevel}
            </if>
            <if test="vo.memberuserid != null">
                and o.memberuserid = #{vo.memberuserid}
            </if>
            <if test="vo.markets != null and vo.markets !='' ">
                and o.memberuserid in (select t.areauserid from arearolemarket t where t.marketid in (${vo.markets}))
            </if>
        </where>
        order by o.accountstatus,o.userid
    </select>

    <select id="selectOrganName" parameterType="java.lang.Long"
            resultType="java.lang.String">
        select t.userid || '/' || t.accountname "accountname"
        from useraccount t
        where t.userid = #{userid,jdbcType=DECIMAL}
    </select>

    <select id="queryInvestorPage" resultMap="investorPageResultMap"
            parameterType="com.muchinfo.mtp3pojos.vo.investor.DealerQueryParam">
        with temp as(
        select la.userid,la.loginid,la.loginstatus from loginaccount la
        ),
        t1 as(
        select temp.userid userid,listagg(temp.loginid, ',') within group(order by temp.loginid) loginid
        from temp where temp.loginstatus in (1,2) group by temp.userid
        ),
        t2 as(
        select max(ba.updatetime) updatetime,ta.userid from bank_accountsign ba
        left join taaccount ta on ba.accountcode=ta.accountid
        where ba.signstatus=4 group by ta.userid
        )
        select uaref.accountname refereeName, t1.loginid loginId,ta.accountid accountId,o.accountname accountName
         ,o.hasauth hasAuth,o.accountstatus,o.modifystatus,o.createtime,o.modifytime,o.audittime,o.canceltime,o.userid,
        o.memberuserid, o.parentuserid
        from useraccount o
        left join t1 on o.userid = t1.userid
        left join userinfo u on o.userid = u.userid
        left join t2 on t2.userid=o.userid
        left join useraccount ub on ub.userid=o.broker
        left join taaccount ta on o.userid=ta.userid and ta.ismain=1
        left join useraccount uaref on o.refereeuserid = uaref.userid
        <where>
            1 = 1
            and o.usertype = 5
            <if test="vo.hasAuth != null">
                AND o.hasauth = #{vo.hasAuth}
            </if>
            <if test="vo.accountName != null and vo.accountName != '' ">
                and ((o.accountname like '%'|| #{vo.accountName,jdbcType=VARCHAR}||'%')
                or(o.userid like '%'||#{vo.accountName,jdbcType=VARCHAR}||'%')
                or t1.loginid like '%'||#{vo.accountName,jdbcType=VARCHAR}||'%'
                or ta.accountid like '%'||#{vo.accountName,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.brokerName != null and vo.brokerName != '' ">
                and ((o.broker like
                '%'||#{vo.brokerName,jdbcType=VARCHAR}||'%')
                or(ub.accountname like '%'||#{vo.brokerName,jdbcType=VARCHAR}||'%'))
            </if>
            <if test="vo.accountStatus != null">
                and o.accountstatus = #{vo.accountStatus}
            </if>
            <if test="vo.memberUserId != null">
                and o.subarealevelpath like '%,'||#{vo.memberUserId}||',%'
            </if>
            <if test="vo.mobilePhone != null and vo.mobilePhone != '' ">
                and u.mobile = #{vo.mobilePhone,jdbcType=VARCHAR}
            </if>
            <if test="vo.cardNum != null and vo.cardNum != '' ">
                and u.cardnum = #{vo.cardNum,jdbcType=VARCHAR}
            </if>
            <if test="vo.parentUserId != null and vo.parentUserId!=0">
                and o.userid in (select userid from useraccount uo
                start with uo.parentuserid = #{vo.parentUserId}
                connect by prior uo.userid =uo.parentuserid and uo.userid !=#{vo.parentUserId} )
            </if>
            <if test="vo.timeType != null">
                <choose>
                    <when test="vo.timeType eq 0">
                        <if test="vo.startTime != null and vo.startTime != ''">
                            <![CDATA[and o.createtime >= to_date(#{vo.startTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                        <if test="vo.endTime != null and vo.endTime != ''">
                            <![CDATA[and o.createtime <= to_date(#{vo.endTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                    </when>
                    <when test="vo.timeType eq 1">
                        <if test="vo.startTime != null and vo.startTime != ''">
                            <![CDATA[and o.audittime >= to_date(#{vo.startTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                        <if test="vo.endTime != null and vo.endTime != ''">
                            <![CDATA[and o.audittime <= to_date(#{vo.endTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                    </when>
                    <when test="vo.timeType eq 4">
                        <if test="vo.startTime != null and vo.startTime != ''">
                            <![CDATA[and t2.updatetime >= to_date(#{vo.startTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                        <if test="vo.endTime != null and vo.endTime != ''">
                            <![CDATA[and t2.updatetime <= to_date(#{vo.endTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                    </when>
                    <when test="vo.timeType eq 6">
                        and o.accountstatus = 6
                        <if test="vo.startTime != null and vo.startTime != ''">
                            <![CDATA[and o.canceltime >= to_date(#{vo.startTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                        <if test="vo.endTime != null and vo.endTime != ''">
                            <![CDATA[and o.canceltime <= to_date(#{vo.endTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                    </when>
                    <when test="vo.timeType eq 2">
                        <if test="vo.startTime != null and vo.startTime != ''">
                            <![CDATA[and t2.updatetime >= to_date(#{vo.startTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                        <if test="vo.endTime != null and vo.endTime != ''">
                            <![CDATA[and t2.updatetime <= to_date(#{vo.endTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                        </if>
                    </when>
                </choose>
            </if>
            <if test="vo.timeType == null">
                <if test="vo.startTime != null and vo.startTime != ''">
                    <![CDATA[and o.createtime >= to_date(#{vo.startTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                </if>
                <if test="vo.endTime != null and vo.endTime != ''">
                    <![CDATA[and o.createtime <= to_date(#{vo.endTime}, 'yyyy-mm-dd hh24:mi:ss')]]>
                </if>
            </if>
            <if test="vo.refNameOrPhone != null and vo.refNameOrPhone != ''">
                AND uaref.accountname like '%'||#{vo.refNameOrPhone}||'%'
            </if>
        </where>
        order by o.accountstatus,decode(o.modifystatus,4,1,3,2,2,3,1,4,5,5,6,6),o.userid
    </select>


    <select id="queryInvestorSelect" resultMap="investorResultMap"
            parameterType="com.muchinfo.mtp3pojos.vo.common.InvestorSelectParam">
        with temp as(
        select la.userid,la.loginid,la.loginstatus from loginaccount la
        ),
        t1 as(
        select temp.userid userid,listagg(temp.loginid, ',') within group(order by temp.loginid) loginid
        from temp where temp.loginstatus in (1,2) group by temp.userid
        )
        select o.userid,o.accountname,t1.loginid
        from useraccount o
        left join t1 on o.userid = t1.userid
        left join taaccount ta on o.userid=ta.userid and ta.ismain=1
        <where>
            1 = 1
            and o.usertype = 5
            <if test="vo.accountStatus != null">
                and o.accountstatus = #{vo.accountStatus}
            </if>
            <if test="vo.searchName != null and vo.searchName != '' ">
                and ((o.accountname like '%'|| #{vo.searchName,jdbcType=VARCHAR}||'%')
                or(o.userid like '%'||#{vo.searchName,jdbcType=VARCHAR}||'%')
                or t1.loginid like '%'||#{vo.searchName,jdbcType=VARCHAR}||'%'
                or ta.accountid like '%'||#{vo.searchName,jdbcType=VARCHAR}||'%')
            </if>
            <if test="vo.areaIds != null and vo.areaIds != '' ">
                and o.parentuserid in (${vo.areaIds})
            </if>
            <if test="vo.parentUserid != null and vo.parentUserid!=0">
                <choose>
                    <when test="vo.rolesCount!=null and vo.rolesCount==3">
                        and o.memberuserid in (
                        select userid from useraccount u1 where u1.parentuserid=#{vo.parentUserid}
                        or u1.userid=#{vo.parentUserid}
                        )
                    </when>
                    <otherwise>
                        and o.userid in (select userid from useraccount uo
                        start with uo.parentuserid = #{vo.parentUserid}
                        connect by prior uo.userid =uo.parentuserid and uo.userid !=#{vo.parentUserid} )
                    </otherwise>
                </choose>
            </if>
        </where>
        and rownum &lt;= 10
        order by o.accountstatus,decode(o.modifystatus,4,1,3,2,2,3,1,4,5,5,6,6),o.userid
    </select>


    <select id="selectDetail" parameterType="java.lang.Long" resultMap="DetailResultMap">
        select t.*
        from useraccount t
        where t.userid = #{userid}
    </select>

    <select id="selectNewDetail" parameterType="java.lang.Long" resultMap="DetailResultMap">
        select t.*
        from draft_useraccount_new t
        where t.userid = #{userid}
    </select>

    <select id="selectUserName" parameterType="java.lang.Long"
            resultType="java.lang.String">
        select u.accountname
        from useraccount u
                 left join taaccount t on u.userid = t.userid
        where t.accountid = #{matchaccountid}
    </select>

    <select id="getOrganListAll" parameterType="java.lang.String" resultType="com.muchinfo.mtp3pojos.vo.common.InvestorSelectResult">
        SELECT o.memberuserid as userId, o.accountname as accountName
        FROM useraccount o
        WHERE (o.accountstatus = 4 AND
        (o.usertype = 3 OR EXISTS
        (SELECT userid FROM arearole WHERE roletype = 7 AND userid = o.userid)))
        <if test="userid != 0">
            AND o.subarealevelpath LIKE '%,'||#{userId}||',%'
        </if>
        <if test="searchCode != null and searchCode !=''">
            and (o.accountname like '%'||#{searchCode}||'%'
            or o.userid like '%'||#{searchCode}||'%')
        </if>
        and rownum &lt;= 10
        ORDER BY o.userid
    </select>

    <update id="updateAccount" parameterType="java.lang.Long">
        update useraccount
        set ReferCount = nvl(ReferCount, 0) + 1
        where userid in (select t.userid
                         from useraccount t
        start with t.userid =#{userid}
        connect by prior RefereeUserID = userid)
               and userid &lt;&gt; #{userid}
    </update>

    <select id="queryModifyPage" resultMap="InvestorModifyResult"
            parameterType="com.muchinfo.mtp3pojos.vo.investor.InvestorModifyParam">
        select o.userid ,o.memberuserid ,o.parentuserid,o.audittime,o.modifystatus,
        lo.loginids || '/' || ua.accountname as accountnamedisplay
        from draft_useraccount_new o
        left join useraccount ua on o.userid = ua.userid
        left join VIEW_USER_LOGINIDS lo on o.userid = lo.userid
        <where>
            and o.usertype = 5
            <if test="vo.parentname != null and vo.parentname != '' ">
                and (o.memberuserid = ${vo.parentname} or o.parentuserid = ${vo.parentname})
            </if>
            <if test="vo.accountname != null and vo.accountname != '' ">
                and ((o.accountname like '%'||#{vo.accountname,jdbcType=VARCHAR}||'%')
                or(o.userid like '%'||#{vo.accountname,jdbcType=VARCHAR}||'%'))
            </if>
            <choose>
                <when test="vo.memberuserid != null">
                    and o.memberuserid = #{vo.memberuserid} and o.modifystatus in (3,4)
                </when>
                <otherwise>
                    and o.modifystatus in (3,4)
                </otherwise>
            </choose>
        </where>
        order by o.modifytime desc,o.accountstatus,o.userid
    </select>

    <select id="getAreaAndAllChilds" resultType="com.muchinfo.mtp3pojos.vo.organ.UserAccountRspResult"
            parameterType="java.lang.Long">
        <choose>
            <when test="areaid==null or areaid==0">
                select o.* from useraccount o
                start with o.parentuserid = 0
                connect by prior
                o.userid =o.parentuserid and o.usertype in (3,4)
            </when>
            <otherwise>
                select o.* from useraccount o
                start with o.userid = #{areaid}
                connect by prior
                o.userid =o.parentuserid and o.usertype in (3,4)
            </otherwise>
        </choose>
    </select>

    <select id="querySonOrganLevel" resultType="java.lang.Integer"
            parameterType="java.lang.Long">
        select subarealevel
        from (SELECT (LEVEL - 1) as subarealevel, t.*
              FROM useraccount t
              where t.usertype in (2, 3)
                  START
              WITH t.userid = #{memberuserid}
              CONNECT BY PRIOR t.userid = t.parentuserid
              ORDER BY LEVEL) ua
        where ua.userid = #{userid}
    </select>


    <select id="getNoGroupSubOrgan" resultType="com.muchinfo.mtp3pojos.vo.profitshar.ProfitSharGroupOrganResult">
        select u.userid, u.accountname
        from useraccount u
        where u.accountstatus = 4
          and u.usertype = 3
          and u.parentuserid = #{areauserid}
          and not exists(select 1 from subareagroupdetail m where m.userid = u.userid)
    </select>


    <select id="queryPersonalized" resultType="com.muchinfo.mtp3pojos.vo.investor.InvestorPersonalizedResult">
        select t.*,u.accountname as areausername from usergroup t,useraccount u
        <where>
            t.areauserid = u.userid
            <if test="groupname != null and groupname !=''">
                and t.groupname like '%'||#{groupname}||'%'
            </if>
            <if test="userid != null">
                and t.areauserid = #{userid}
            </if>
        </where>
        order by t.autoid
    </select>


    <select id="queryPersonalizedDetail" resultType="com.muchinfo.mtp3pojos.vo.investor.InvestorPerDetailQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.investor.InvestorPerDetailQueryParam">
        select t.*,u.accountname from usergroupdetail t ,useraccount u ,userinfo i
        <where>
            t.userid = u.userid and u.userid = i.userid
            <if test="vo.usergroupid != null">
                and t.usergroupid = #{vo.usergroupid}
            </if>
            <if test="vo.accountname != null and vo.accountname != '' ">
                and ((u.accountname like '%'||#{vo.accountname,jdbcType=VARCHAR}||'%')
                or (u.userid like '%'||#{vo.accountname,jdbcType=VARCHAR}||'%'))
            </if>
            <if test="vo.mobilephone != null and vo.mobilephone != '' ">
                and i.mobile = #{vo.mobilephone,jdbcType=VARCHAR}
            </if>
            <if test="vo.cardnum != null and vo.cardnum != '' ">
                and i.cardnum = #{vo.cardnum,jdbcType=VARCHAR}
            </if>
        </where>
        order by t.userid
    </select>


    <select id="queryAccountRiskConfigPage" parameterType="com.muchinfo.mtp3pojos.vo.investor.AccountRiskConfigParam"
            resultType="com.muchinfo.mtp3pojos.vo.investor.AccountRiskConfigResult">
        select a.userid,a.accountid,a.customertype,u.accountname as username , ua.userid || '/' || ua.accountname as memberusername
        from AccountRiskConfig a
        left join useraccount u on a.userid = u.userid
        left join useraccount ua on ua.userid = u.memberuserid
        left join taaccount t on a.accountid=t.accountid
        <where>
            and u.accountstatus = 4
            and u.usertype=5
            <if test="vo.accountid != null">
                and a.accountid = #{accountid}
            </if>
            <if test="vo.username != null and vo.username != ''">
                and ((u.accountname like
                '%'||#{vo.username,jdbcType=VARCHAR}||'%')
                or(u.userid like '%'||#{vo.username,jdbcType=VARCHAR}||'%'))
            </if>
            <if test="vo.memberuserid != null">
                and a.userid in (
                select t.userid from useraccount t where u.usertype=5 start with t.userid =#{vo.memberuserid}
                connect by prior t.userid = t.parentuserid
                and t.usertype !=2
                )
            </if>
            order by a.userid ,a.accountid
        </where>

    </select>

    <select id="getCustomerTypeByGoodsid" resultType="com.muchinfo.mtp3pojos.vo.investor.InvestorInitBZResult">
        select g.marginalgorithm,e.enumdicname,e.enumitemname,
        g.marginalgorithm, g.marketmarginvalue
        from goodsmarginconfig g,enumdicitem e,riskratiotype r
        where g.customertypeid = e.enumitemname
        and e.enumitemstatus = 1 and g.goodsid = #{goodsid}
        and e.enumitemname = r.customertype
        and r.riskcontrolmode=1
        and e.enumdiccode='customerType'
        <if test="customertypeid != null  and customertypeid != ''">
            and g.customertypeid=#{customertypeid}
        </if>
    </select>


    <select id="getTradeRuleDescriptions" resultType="com.muchinfo.mtp3pojos.vo.investor.RulesConfigVo">
        with cf as(select u.paramid,u.paramvalue from usergrouptradeconfig u
        where u.ruletype=2 and u.memberuserid = #{vo.memberuserid} and
        u.usergroupid=0
        and u.marketid = #{vo.marketid} and u.goodsid = #{vo.goodsid}
        ),
        baserule as (
        select t.ruleid,t.paramvalue from goodstraderule t where t.goodsid = #{vo.goodsid}
        ),
        jjs as (
        select gs.qtydecimalplace,t.ruleid,t.paramvalue from goodstraderule t,goods gs
        where t.goodsid=gs.goodsid and t.goodsid = #{vo.goodsid}
        union
        select gp.qtydecimalplace,t.ruleid,t.paramvalue from GoodsGroupTradeRule t, goodsgroup gp
        where t.goodsgroupid = gp.goodsgroupid and t.ruleid not in (select ruleid from baserule)
        and t.goodsgroupid=(select g.goodsgroupid from goods g where g.goodsid=#{vo.goodsid})
        )
        <if test="vo.usergroupid != null">
            , co as (select u.paramid, u.paramvalue from usergrouptradeconfig u
            where u.ruletype=2 <if test="vo.memberuserid != null">and u.memberuserid = #{vo.memberuserid}</if> and
            u.usergroupid=#{vo.usergroupid} and u.marketid = #{vo.marketid} and u.goodsid = #{vo.goodsid}
            )
        </if>
        <if test="vo.usergroupid2 != null">
            , co1 as (select u.paramid, u.paramvalue from usergrouptradeconfig u
            where u.ruletype=2 <if test="vo.memberuserid != null">and u.memberuserid = #{vo.memberuserid}</if> and
            u.usergroupid=#{vo.usergroupid2} and u.marketid = #{vo.marketid} and u.goodsid = #{vo.goodsid}
            )
        </if>

        select t.*,g.qtydecimalplace,g.paramvalue,cf.paramvalue dvalue
        <if test="vo.usergroupid != null">
            ,co.paramvalue cvalue
        </if>
        <if test="vo.usergroupid2 != null">
            ,co1.paramvalue gvalue
        </if>

        from traderuledescription t
        left join traderuleconfig c on t.ruleid = c.ruleid and c.ispersonalized = 1
        left join jjs g on t.ruleid = g.ruleid
        left join cf cf on t.ruleid = cf.paramid
        <if test="vo.usergroupid != null">
            left join co co on t.ruleid = co.paramid
        </if>
        <if test="vo.usergroupid2 != null">
            left join co1 co1 on t.ruleid = co1.paramid
        </if>
        where c.trademodeid = (select m.trademode from market m where m.marketid = #{vo.marketid} )
    </select>

    <select id="getTradeFeeDescriptions" resultType="com.muchinfo.mtp3pojos.vo.investor.FeesConfigVo">
        with cf as(
        select u.paramid,u.feealgorithm, u.paramvalue
        from usergrouptradeconfig u
        where u.ruletype=3 <if test="vo.memberuserid != null">and u.memberuserid = #{vo.memberuserid}</if>   and
        u.usergroupid=0
        and u.marketid = #{vo.marketid} and u.goodsid = #{vo.goodsid}
        ),
        basefee as (
        select t.tradefeeid,t.feealgorithm,t.exchangevalue,t.memberdefaultvalue,t.memberminvalue,t.membermaxvalue
        from GoodsTradeFee t where t.goodsid = #{vo.goodsid}
        ),
        jjs as (
        select * from basefee t
        union
        select t.tradefeeid,t.feealgorithm,t.exchangevalue,t.memberdefaultvalue,t.memberminvalue,t.membermaxvalue
        from GoodsGroupTradeFee t
        where t.goodsgroupid=(select g.goodsgroupid from goods g where g.goodsid=#{vo.goodsid})
        and t.tradefeeid not in (select tradefeeid from basefee)
        )
        <if test="vo.usergroupid != null">
            , co as (select u.paramid,u.feealgorithm, u.paramvalue from usergrouptradeconfig u
            where u.ruletype=3 <if test="vo.memberuserid != null">and u.memberuserid = #{vo.memberuserid}</if> and
            u.usergroupid=#{vo.usergroupid} and u.marketid = #{vo.marketid} and u.goodsid = #{vo.goodsid}
            )
        </if>
        <if test="vo.usergroupid2 != null">
            , co1 as (select u.paramid,u.feealgorithm, u.paramvalue from usergrouptradeconfig u
            where u.ruletype=3 <if test="vo.memberuserid != null">and u.memberuserid = #{vo.memberuserid}</if> and
            u.usergroupid=#{vo.usergroupid2} and u.marketid = #{vo.marketid} and u.goodsid = #{vo.goodsid}
            )
        </if>
        select t.*,g.feealgorithm, g.memberminvalue,g.membermaxvalue,
        cf.feealgorithm drithm, cf.paramvalue dvalue
        <choose>
            <when test="vo.ruletype != null">
                ,g.exchangevalue exchangevalue
            </when>
            <otherwise>
                ,g.memberdefaultvalue exchangevalue
            </otherwise>
        </choose>
        <if test="vo.usergroupid != null">
            ,co.feealgorithm crithm,co.paramvalue cvalue
        </if>
        <if test="vo.usergroupid2 != null">
            ,co1.feealgorithm grithm,co1.paramvalue gvalue
        </if>
       ,g.memberdefaultvalue exchangevalue
        from tradefeedescription t
        left join jjs g on t.tradefeeid = g.tradefeeid
        left join tradefeeconfig c on t.tradefeedescriptionid = c.tradefeedescriptionid and c.ispersonalized = 1
        left join cf cf on t.tradefeeid = cf.paramid
        <if test="vo.usergroupid != null">
            left join co co on t.tradefeeid = co.paramid
        </if>
        <if test="vo.usergroupid2 != null">
            left join co1 co1 on t.tradefeeid = co1.paramid
        </if>
        where c.tradepropertyid=(select m.tradeproperty from market m where m.marketid = #{vo.marketid})
        and g.feealgorithm is not null
    </select>

    <!-- 查询经纪会员+下属所有子机构-->
    <select id="queryOrganSelect2And3"
            parameterType="com.muchinfo.mtp3pojos.vo.common.OrganSelectParam"
            resultType="com.muchinfo.mtp3pojos.vo.common.OrganSelectResult">
        SELECT o.userid ,o.memberuserid, o.accountname, o.parentuserid,o.usertype usertype
        FROM useraccount o
        WHERE (o.accountstatus = 4 AND
        (o.usertype = 3 OR EXISTS
        (SELECT userid FROM arearole WHERE roletype = 7 AND userid = o.userid)))
        <if test="vo.userid!=null  and vo.userid!=''">
            AND o.subarealevelpath LIKE '%,'||#{vo.userid}||',%'
        </if>
        <if test="vo.searchcode != null and vo.searchcode !=''">
            and (o.accountname like '%'||#{vo.searchcode}||'%'
            or o.userid like '%'||#{vo.searchcode}||'%')
        </if>

        and rownum &lt;= 10
        ORDER BY o.userid
    </select>

    <select id="queryUserAccount" resultType="com.muchinfo.mtp3pojos.entity.Useraccount">
        select ua.* from useraccount ua
        WHERE ua.ACCOUNTSTATUS = 4
        AND UA.USERID = #{userid,jdbcType=DECIMAL}
        start with ua.userid in (SELECT ar.USERID FROM AREAROLE ar WHERE AR.ROLETYPE = 7)
        connect by prior ua.userid = ua.parentuserid and ua.usertype = 3
    </select>

    <select id="queryUra" resultType="com.muchinfo.mtp3pojos.entity.Userreckonaccount">
        select t.*, e.enumdicname
        from userreckonaccount t
                 left join enumdicitem e on e.enumitemname = t.currencyid
        where e.enumdiccode = 'currency'
          and t.userid = #{userid}
          and t.defaultaccounttype = 2
    </select>

    <select id="queryExchSonOrgin" resultType="com.muchinfo.mtp3pojos.vo.common.OrganSelectResult"
            parameterType="com.muchinfo.mtp3pojos.vo.common.OrganSelectParam">
        <![CDATA[select t.userid,t.accountname,t.parentuserid,t.memberuserid from useraccount t
        left join arearole a on t.userid = a.userid]]>
        <where>
            <if test="vo.roles != null and vo.roles !='' and vo.usertype != null and vo.usertype !=''">
                <![CDATA[and ((a.roletype in (${vo.roles}) and t.usertype=2) or t.usertype in (${vo.usertype}))
				and length(t.subarealevelpath)-length(replace(t.subarealevelpath,','))<${vo.level}]]>
            </if>
            <if test="vo.searchcode != null and vo.searchcode !=''">
                <![CDATA[and (t.accountname like
				'%'||#{vo.searchcode}||'%' or t.userid like
				'%'||#{vo.searchcode}||'%')]]>
            </if>
        </where>
        and rownum &lt;= 10
    </select>

    <select id="queryOrganSonModify" resultMap="PageResultMap">
        select o.*
        from draft_useraccount_new o
        left join (SELECT (LEVEL - 1) subarealevel, t.*
        FROM useraccount t
        where t.usertype in (3, 4)
        START WITH t.userid = 1
        CONNECT BY PRIOR t.userid = t.parentuserid
        ORDER BY LEVEL) ua on o.userid = ua.userid
        <where>
            and o.usertype in (3,4) and o.modifystatus = 4
            <if test="vo.subarealevel != null and vo.subarealevel != '' ">
                and ua.subarealevel = #{vo.subarealevel}
            </if>
            <if test="vo.accountname != null and vo.accountname != '' ">
                and ((o.accountname like '%'||#{vo.accountname,jdbcType=VARCHAR}||'%')
                or(o.userid like '%'||#{vo.accountname,jdbcType=VARCHAR}||'%'))
            </if>
            <if test="vo.accountstatus != null">
                and o.accountstatus = #{vo.accountstatus}
            </if>
        </where>
        order by o.modifytime desc,o.accountstatus,o.userid
    </select>

    <select id="queryOrganSonTransfer" resultType="com.muchinfo.mtp3pojos.vo.organ.son.OrganSonTransferResult">
        select outua.subarealevel "outlevel", inua.subarealevel "inlevel", t.* from investortransfer t
        left join
        (SELECT (LEVEL - 1) subarealevel, t.*
        FROM useraccount t
        where t.usertype in (2,3)
        START
        WITH t.userid = 1
        CONNECT BY PRIOR t.userid = t.parentuserid
        ORDER BY LEVEL) outua on t.areauserid = outua.userid
        left join
        (SELECT (LEVEL - 1) subarealevel, t.*
        FROM useraccount t
        where t.usertype in (2,3)
        START
        WITH t.userid = 1
        CONNECT BY PRIOR t.userid = t.parentuserid
        ORDER BY LEVEL) inua on t.newareauserid = inua.userid
        <where>
            <if test="vo.outmemberuserid != null and vo.outmemberuserid!=0">
                and (t.memberuserid = #{vo.outmemberuserid} or t.areauserid = #{vo.outmemberuserid}
                or t.brokeruserid = #{vo.outmemberuserid})
            </if>
            <if test="vo.outinvestor != null and vo.outinvestor!=0">
                and t.investoruserid = #{vo.outinvestor}
            </if>
            <if test="vo.inuserid != null and vo.inuserid!=0">
                and (t.newmemberuserid = #{vo.inuserid} or t.newareauserid = #{vo.inuserid}
                or t.newbrokeruserid = #{vo.inuserid})
            </if>
            <if test="vo.transferstatus != null">
                and t.transferstatus = #{vo.transferstatus}
            </if>
            <choose>
                <when test="vo.transferusertype != null and vo.transferusertype == 1">
                    and t.transfertype in (1,2)
                </when>
                <when test="vo.transferusertype != null and vo.transferusertype == 2">
                    and t.transfertype in (3)
                </when>
            </choose>
        </where>
        order by t.autoid desc
    </select>

    <select id="getInvestorTODiversion" resultType="com.muchinfo.mtp3pojos.entity.Useraccount">
        select * from useraccount o
        <where>
            and 1=1
            and o.usertype =5
            <if test="memberuserid != null">
                and o.memberuserid = #{memberuserid}
            </if>
            <if test="brokeruserid != null">
                and o.broker = #{brokeruserid}
            </if>
            <if test="areauserid != null">
                start with o.userid = #{areauserid}
                connect by prior o.userid = o.parentuserid
            </if>
        </where>
    </select>

    <select id="queryUser" resultType="com.muchinfo.mtp3pojos.entity.Useraccount"
            parameterType="java.lang.String">
        select ua.*
        from useraccount ua
        left join useraccount tua on ua.teammanageruserid = tua.userid
        left join userinfo ui on ua.userid = ui.userid
        left join division p on ui.provinceid = p.autoid
        left join division c on ui.cityid = c.autoid
        left join division d on ui.districtid = d.autoid
        <where>
            <if test="subarealevelpath!=null and subarealevelpath!=''">
                and ua.subarealevelpath like '%' || #{subarealevelpath}||'%'
            </if>
        </where>
        order by ua.userid
    </select>

    <select id="querySonOrganPageByLevel" resultType="com.muchinfo.mtp3pojos.vo.common.OrganSelectResult">
        select ua.*,
        ua1.userid || '/' || ua1.accountname "memberusername" ,
        ua2.userid || '/' || ua2.accountname "parentname"
        from useraccount ua
        left join useraccount ua1 on ua.memberuserid = ua1.userid
        left join useraccount ua2 on ua.parentuserid = ua2.userid
        where ua.usertype=3
        <if test="subarealevel != null and subarealevel != '' ">
            and (LENGTH(ua.subarealevelpath) - LENGTH(REPLACE(ua.subarealevelpath, ',', ''))-2) = #{subarealevel}
        </if>
        <if test="searchcode != null and searchcode != '' ">
            and ((ua.accountname like '%'||#{searchcode}||'%')
            or(ua.userid like '%'||#{searchcode}||'%'))
        </if>
    </select>
    <select id="usercancelapply" resultType="com.muchinfo.mtp3pojos.vo.investor.InvestorCancelApplyResult">
        select sm.logincode auditlogincode,
		sm.username auditusername,
		ua.accountname,
		ua.accountstatus,
		uca.* from USERCANCELAPPLY uca
		left join systemmanager sm on sm.autoid = uca.auditid
		left join useraccount ua on uca.userid = ua.userid
        <where>
            <if test="vo.accountname != null and vo.accountname != ''">
                <![CDATA[and (ua.accountname like '%${vo.accountname}%' escape '/' or uca.userid like '%${vo.accountname}%' escape '/')]]>
            </if>
            <if test="vo.handlestatus != null">
                <![CDATA[and uca.handlestatus in (${vo.handlestatus})]]>
            </if>
        </where>
        <![CDATA[order by uca.handlestatus asc,uca.applytime asc]]>
    </select>

    <!-- 获取未配置交易个性化的用户 -->
    <select id="getNoTradeConfigInvestorsLikeName" resultType="com.muchinfo.mtp3pojos.vo.investor.InitInvestorTradeResult">
        select * from (
        select t.*  from useraccount t where t.accountstatus=4
        <if test="memberuserid != null">
            start with t.userid = #{memberuserid}
            connect by t.parentuserid = prior t.userid
        </if>
        and t.usertype!=2
        ) a where a.usertype = 5
        <if test="memberuserid != null">
            and a.memberuserid = #{memberuserid}
        </if>
        <if test="searchName != null">
            and a.accountname like '%'||#{searchName}||'%'
        </if>
        and not exists (select 1  from accounttradeconfig u
        where <if test="memberuserid != null"> u.memberuserid = #{memberuserid} and </if> a.userid = u.userid)
        and rownum &lt;= 10
    </select>
</mapper>
