<?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.ITaaccountMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.Taaccount">
        <id column="ACCOUNTID" property="accountid" />
        <result column="USERID" property="userid" />
        <result column="CURRENCYID" property="currencyid" />
        <result column="CHANGEFLAG" property="changeflag" />
        <result column="PASSWORD" property="password" />
        <result column="TRADESTATUS" property="tradestatus" />
        <result column="SIGNSTATUS" property="signstatus" />
        <result column="ISMAIN" property="ismain" />
        <result column="PARENTACCOUNTID" property="parentaccountid" />
        <result column="RELATEDUSERID" property="relateduserid" />
        <result column="RELATEDACCOUNTSTATUS" property="relatedaccountstatus" />
        <result column="TAACCOUNTTYPE" property="taaccounttype" />
        <result column="ISRECKONACCOUNT" property="isreckonaccount" />
        <result column="ISMARKETACCOUNT" property="ismarketaccount" />
        <result column="BALANCE" property="balance" />
        <result column="ORIFREEZEMARGIN" property="orifreezemargin" />
        <result column="ORIUSEDMARGIN" property="oriusedmargin" />
        <result column="ORIOTHERFREEZEMARGIN" property="oriotherfreezemargin" />
        <result column="ORIFREEZECHARGE" property="orifreezecharge" />
        <result column="ORIMORTGAGECREDIT" property="orimortgagecredit" />
        <result column="ORIOTHERCREDIT" property="oriothercredit" />
        <result column="ORIOUTAMOUNTFREEZE" property="orioutamountfreeze" />
        <result column="FREEZEMARGIN" property="freezemargin" />
        <result column="USEDMARGIN" property="usedmargin" />
        <result column="OTHERFREEZEMARGIN" property="otherfreezemargin" />
        <result column="FREEZECHARGE" property="freezecharge" />
        <result column="MORTGAGECREDIT" property="mortgagecredit" />
        <result column="OTHERCREDIT" property="othercredit" />
        <result column="OUTAMOUNTFREEZE" property="outamountfreeze" />
        <result column="INAMOUNT" property="inamount" />
        <result column="CURRENTBALANCE" property="currentbalance" />
        <result column="OUTAMOUNT" property="outamount" />
        <result column="PAYCHARGE" property="paycharge" />
        <result column="CLOSEPL" property="closepl" />
        <result column="CREDITINCREASE" property="creditincrease" />
        <result column="CREDITDECREASE" property="creditdecrease" />
        <result column="OTHERCREDITINCREASE" property="othercreditincrease" />
        <result column="OTHERCREDITDECREASE" property="othercreditdecrease" />
        <result column="TRANSFERAMOUNT" property="transferamount" />
        <result column="OTHERPAY" property="otherpay" />
        <result column="OTHERINCOME" property="otherincome" />
        <result column="OUTTHRESHOLD" property="outthreshold" />
        <result column="TRADESTATUSCHANGETIME" property="tradestatuschangetime" />
        <result column="CHANGETIME" property="changetime" />
        <result column="THIRDINAMOUNT" property="thirdinamount" />
        <result column="THIRDOUTAMOUNT" property="thirdoutamount" />
        <result column="CAPITALBALANCE" property="capitalbalance" />
        <result column="FROMACCOUNTID" property="fromaccountid" />
        <result column="SUBLEVELPATH" property="sublevelpath" />
        <result column="SERIVCEGROUP" property="serivcegroup" />
        <result column="ACCOUNTNAME" property="accountname" />
        <result column="ACCOUNTFLAG" property="accountflag" />
        <result column="ORIDEBT" property="oridebt" />
        <result column="CURDEBT" property="curdebt" />
        <result column="MODIFYTIME" property="modifytime" />
        <result column="MODIFYSRC" property="modifysrc" />
        <result column="MODIFIERID" property="modifierid" />
    </resultMap>
    <!--列表分页查询-->
    <resultMap id="PageResultMap" type="com.muchinfo.mtp3pojos.vo.taaccount.TaAccountQueryResult">
        <result column="accountid" property="accountId" />
        <result column="ismain" property="isMain" />
        <result column="taaccounttype" property="taAccountType" />
        <result column="parentaccountid" property="parentAccountId" />
        <result column="tradestatus" property="tradeStatus" />
        <result column="relateduserid" property="relatedUserId" />
        <result column="userid" property="userId" />
        <result column="currencyid" property="currencyid" />
        <result column="relatedaccountstatus" property="relatedAccountStatus" />
        <association property="accountName" column="USERID"
                     jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.IUseraccountMapper.selectOrganName"></association>
        <association property="relatedName" column="RELATEDUSERID"
                     jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.IUseraccountMapper.selectOrganName"></association>
    </resultMap>
    <!--资金流水列表查询-->
    <resultMap id="resultAmountStream" type="com.muchinfo.mtp3pojos.vo.taaccount.TaAccountAmountStreamResult">
        <result column="histradedate" property="hisTradeDate" />
        <result column="accountid" property="accountId" />
        <result column="operatetype" property="operateType" />
        <result column="relationorderid" property="relationOrderId" />
        <result column="marketname" property="marketName" />
        <result column="goodsname" property="goodsName" />
        <result column="amount" property="amount" />
        <result column="balance" property="balance" />
        <result column="currentbalance" property="currentBalance" />
        <result column="createtime" property="createTime" />
        <result column="membername" property="memberName" />
        <result column="accountname" property="accountName" />
        <result column="businesscodename" property="businessCodeName" />
        <result column="amountadjusttypename" property="amountAdjustTypeName" />
        <association property="accountName" column="USERID"
                     jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.IUseraccountMapper.selectOrganName"></association>
    </resultMap>

    <resultMap id="monitorResultMap" extends="BaseResultMap"
               type="com.muchinfo.mtp3pojos.vo.marketrun.TaaccountMonitorVo">
        <result column="PARENTUSERID" jdbcType="DECIMAL" property="parentuserid"/>
        <result column="MEMBERUSERID" jdbcType="DECIMAL" property="memberuserid"/>
        <result column="USERTYPE" jdbcType="DECIMAL" property="usertype"/>
        <result column="SUBAREALEVELPATH" jdbcType="VARCHAR" property="subarealevelpath"/>
        <result column="USERACCOUNTNAME" jdbcType="VARCHAR" property="useraccountname"/>
        <result column="LOGINID" jdbcType="VARCHAR" property="loginid"/>
        <association property="memberusername" column="MEMBERUSERID" jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.IUseraccountMapper.selectOrganName"></association>
        <association property="parentusername" column="PARENTUSERID" jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.IUseraccountMapper.selectOrganName"></association>
        <!-- <association property="borkername" column="BROKER" jdbcType="VARCHAR"
            select="com.muchinfo.services.participantor.dao.IUserAccountDao.selectOrganName"></association> -->
    </resultMap>
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ACCOUNTID, USERID, CURRENCYID, CHANGEFLAG, PASSWORD, TRADESTATUS, SIGNSTATUS, ISMAIN, PARENTACCOUNTID, RELATEDUSERID, RELATEDACCOUNTSTATUS, TAACCOUNTTYPE, ISRECKONACCOUNT, ISMARKETACCOUNT, BALANCE, ORIFREEZEMARGIN, ORIUSEDMARGIN, ORIOTHERFREEZEMARGIN, ORIFREEZECHARGE, ORIMORTGAGECREDIT, ORIOTHERCREDIT, ORIOUTAMOUNTFREEZE, FREEZEMARGIN, USEDMARGIN, OTHERFREEZEMARGIN, FREEZECHARGE, MORTGAGECREDIT, OTHERCREDIT, OUTAMOUNTFREEZE, INAMOUNT, CURRENTBALANCE, OUTAMOUNT, PAYCHARGE, CLOSEPL, CREDITINCREASE, CREDITDECREASE, OTHERCREDITINCREASE, OTHERCREDITDECREASE, TRANSFERAMOUNT, OTHERPAY, OTHERINCOME, OUTTHRESHOLD, TRADESTATUSCHANGETIME, CHANGETIME, THIRDINAMOUNT, THIRDOUTAMOUNT, CAPITALBALANCE, FROMACCOUNTID, SUBLEVELPATH, SERIVCEGROUP, ACCOUNTNAME, ACCOUNTFLAG, ORIDEBT, CURDEBT, MODIFYTIME, MODIFYSRC, MODIFIERID
    </sql>

    <delete id="deleteUserMarket">
        delete from TaaccountMarket t where t.accountid in
        (select a.accountid
        from taaccount a where a.userid = #{userid})
        <if test="marketids!=null">
            and t.marketid not in
            <foreach collection="marketids" open="(" separator="," close=")"
                     item="item">
                #{item}
            </foreach>
        </if>
    </delete>
    <select id="queryTaAccountPage" resultMap="PageResultMap"
            parameterType="com.muchinfo.mtp3pojos.vo.taaccount.TaAccountQueryParam">
        select t.accountid, t.ismain,t.taaccounttype, t.parentaccountid
        ,t.tradestatus,t.userid,t.currencyid,t.outthreshold
        ,t.relateduserid,t.relatedaccountstatus
        from
        taaccount t
        <where>
            1 = 1
            <if test="vo.accountId !=null and vo.accountId !=''">
                and t.accountid = #{vo.accountId}
            </if>
            <if test="vo.userId != null and vo.userId !=''">
                and ((t.userid = #{vo.userId} and t.ismain = 1)
                or(t.relateduserid = #{vo.userId} and t.ismain = 0))
            </if>
        </where>
        order by accountid
    </select>

    <select id="queryTAAccount" resultType="com.muchinfo.mtp3pojos.vo.taaccount.TaAccountQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.taaccount.TaAccountReportParam">
        select t.accountid as accountId,u.accountname as accountName,t.userid
        from taaccount t
        left join useraccount u on t.userid = u.userid
        <where>
            <if test="vo.accountName != null and vo.accountName !='' ">
                and (u.accountname like
                '%'||#{vo.accountName}||'%'
                or t.accountid like
                '%'||#{vo.accountName}||'%')
            </if>
            <if test="vo.subLevelPath!=null and vo.subLevelPath !=''">
                and t.sublevelpath like '%'||#{vo.subLevelPath}||'%'
            </if>
            <if test="vo.userId != null">
                <choose>
                    <when test="vo.changeFlag!=null and vo.changeFlag==3">
                        and (u.memberuserid in (
                        select userid from useraccount uo where uo.parentuserid = #{vo.userId} or uo.userid=#{vo.userId}
                        ))
                    </when>
                    <otherwise>
                        and (u.userid in (select userid from useraccount uo
                        start with uo.userid = #{vo.userId}
                        connect by prior uo.userid =uo.parentuserid and
                        uo.userid!=#{vo.userId}))
                    </otherwise>
                </choose>
            </if>
            <if test="vo.accountId != null and  vo.accountId != ''">
                and t.accountid = #{vo.accountId}
            </if>
            <if test="vo.type != null and  vo.type!= ''">
                and u.usertype = #{vo.type}
            </if>
            <if test="vo.userType != null and vo.userType !='' ">
                and u.usertype in (${vo.userType})
            </if>
            and rownum &lt;= 10
        </where>
    </select>


    <select id="selectAmountStream" resultMap="resultAmountStream"
            parameterType="com.muchinfo.mtp3pojos.vo.taaccount.TaAccountAmountStreamParam">
        select
            t.autoid,t.accountid,t.operatetype,t.relationorderid,t.amount,
            t.balance,t.currentbalance,t.createtime,m.marketname,
            ( case when g.goodsname is null then '--'
            when g.goodsname is not null and g.goodscode is not null then g.goodsname || '/' || g.goodscode
            when g.goodsname is not null then g.goodsname
            else '--' end) goodsname,
            ua.userid || '/' || ua.accountname as membername,
            u.userid || '/' || u.accountname as accountname,
            case
            when (t.businesscode &gt; 700 and t.businesscode &lt; 800) or (t.businesscode &gt; 1900) then
            decode(businesscode, null, '--', 0, '系统', abe.enumdicname)
            else
            decode(operatetype, null, '--', 0, '系统', e.enumdicname)
            end as businesscodename,
            decode(t.amountadjusttype, null, '--', 0, '系统', 1, '单边账调整', 2, '资金调整') as amountadjusttypename
            <if test="vo.logType == 2">
                ,t.histradedate
            </if>
        <if test="vo.logType== 2">
            from his_taaccountlog t
        </if>
        <if test="vo.logType == 1">
            from taaccountlog t
        </if>
        left join market m on t.marketid = m.marketid
        left join (
        select t.goodsid,t.goodscode,t.goodsname from goods t
        union
        select t.goodsid,t.goodscode,t.goodsname from presale_listinginfo t
        union
        select t.goodsid,t.goodscode,t.goodsname from auction_orderinfo t
        union
        select t.goodsid,t.goodscode,t.goodsname from his_presale_listinginfo t where t.isvaliddata = 1
        union
        select t.goodsid,t.goodscode,t.goodsname from his_auction_orderinfo t where t.isvaliddata = 1
        union
        select w.wrstandardid goodsid,to_char(w.wrstandardcode) goodscode,to_char(w.wrstandardname) goodsname from
        wrstandard w
        ) g on t.goodsid = g.goodsid
        inner join taaccount ta on t.accountid = ta.accountid
        inner join useraccount u on ta.relateduserid = u.userid
        left join ENUMDICITEM e on t.operatetype=e.enumitemname and e.enumdiccode = 'operateType'
        left join useraccount ua on u.memberuserid = ua.userid
        left join ENUMDICITEM abe on t.businesscode=abe.enumitemname and abe.enumdiccode = 'accountBusinessCode'
        where
            t.amount &lt;&gt; 0 and (abe.enumitemstatus = 1 or t.businesscode = 0)
        <if test="vo.accountId != null and vo.accountId != ''">
            and ta.accountid = #{vo.accountId}
        </if>
        <if test="vo.userId != null and vo.userId != ''">
            and ta.relateduserid=#{vo.userId}
        </if>
        <if test="vo.operatetype != null">
            and t.operatetype = #{vo.operatetype}
        </if>
        <if test="vo.logType == 2 and vo.startTime != null and vo.startTime != '' and vo.endTime != null and vo.endTime != '' ">
            <![CDATA[and t.histradedate >= #{vo.startTime} and t.histradedate <= #{vo.endTime}]]>
        </if>
        order by t.autoid desc
    </select>

    <select id="queryTAAccountChildren" resultType="com.muchinfo.mtp3pojos.vo.common.TAAccountChildrenResult">
        select ta.accountid as accountId,
        ua.userid || '/' || ua.accountname relatedName
        from TAAccount ta
        inner join useraccount ua on ta.relateduserid = ua.userid
        where ta.tradestatus = 1
        <if test="accountName != null and accountName != '' ">
            and (ua.accountname like '%' || #{accountName} || '%'
            or ua.userid like '%' || #{accountName} || '%'
            or ta.accountid like '%' || #{accountName} || '%'
            <!-- or vlog.LOGINIDS like '%' || #{accountname,jdbcType=VARCHAR} || '%' -->
            )
        </if>
        <if test="usertype != null and usertype != '' ">
            and ua.usertype = #{usertype}
        </if>
        <if test="areaUserId != null and areaUserId != 1 ">
            START WITH ua.userid = #{areaUserId}
            CONNECT BY PRIOR ua.userid = ua.parentuserid
        </if>
        and rownum &lt;= 10
        order by ua.userid
    </select>

    <select id="queryRefUserSelect" resultType="com.muchinfo.mtp3pojos.vo.reportform.RefUserResult">
        select ta.*,
        ua.userid || '/' || ua.accountname relatedname,
        la.LOGINIDS as invloginids
        from TAAccount ta
        left join useraccount ua on ta.relateduserid = ua.userid
        left join VIEW_USER_LOGINIDS la on la.userid = ua.userid
        where ua.refereeuserid = #{refereeuserid}
        <if test="searchcode != null and searchcode != '' ">
            and (ua.accountname like '%' || #{searchcode} || '%'
            or ta.accountid like '%' || #{searchcode} || '%'
            or la.LOGINIDS like '%' || #{searchcode} || '%' )
        </if>
    </select>

    <select id="queryChildren" resultType="com.muchinfo.mtp3pojos.vo.reportform.TAAccountChildrenSelect">
        select ta.accountid,
        ua.userid || '/' || ua.accountname relatedname,
        vlog.LOGINIDS as invloginids
        from TAAccount ta
        inner join useraccount ua on ta.relateduserid = ua.userid
        left join VIEW_USER_LOGINIDS vlog on vlog.USERID = ua.userid
        where ta.tradestatus = 1
        <if test="searchcode != null and searchcode != '' ">
            and (ua.accountname like '%' || #{searchcode} || '%'
            or ua.userid like '%' || #{searchcode} || '%'
            or ta.accountid like '%' || #{searchcode} || '%')
        </if>
        <if test="relateduserid != null and relateduserid != 1 ">
            START WITH ua.userid = #{relateduserid}
            CONNECT BY PRIOR ua.userid = ua.parentuserid
        </if>
        order by ua.userid
    </select>

    <select id="getMonitorInvestor" resultMap="monitorResultMap">
        select t.*,
               u.usertype,
               u.userid,
               u.accountname useraccountname,
               u.parentuserid,
               u.memberuserid,
               u.broker,
               v.loginids    loginid
        from taaccount t
                 left join useraccount u on t.userid = u.userid
                 left join VIEW_USER_LOGINIDS v on t.userid = v.userid
        where 1 = 1
          and t.accountid = #{accountid}
        <if test="puserid != null">
            and t.relateduserid in
            (
            select userid from useraccount uo
            start with uo.userid = #{puserid}
            connect by prior uo.userid =uo.parentuserid
            )
        </if>
    </select>

    <select id="queryRoleOrganPage" resultType="com.muchinfo.mtp3pojos.vo.organ.UserAccountRspResult">
        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 o.*, r.modifytime rolemodifytime, tmp.accountid,sm.username userName
        from arearole r,useraccount o
        left join tmp on o.userid = tmp.userid
        left join systemmanager sm on sm.autoid =  o.creatorid
        <where>
            r.userid = o.userid and o.usertype = 2
            <if test="vo.roleid != null">
                and r.roletype = #{vo.roleid}
            </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.parentuserid != null">
                and o.parentuserid = #{vo.parentuserid}
            </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="queryWarehouseOrganPage" resultType="com.muchinfo.mtp3pojos.vo.organ.UserAccountRspResult">
        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 o.*, r.modifytime rolemodifytime, tmp.accountid,sm.username userName
        from arearole r, useraccount o
        left join tmp on o.userid = tmp.userid
        left join systemmanager sm on sm.autoid =  o.creatorid
        <where>
            r.userid = o.userid and r.roletype = 4 and o.usertype = 2
            <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>
        </where>
        order by o.accountstatus,o.userid
    </select>
    <select id="getNoTradeConfigAccounts" resultType="java.lang.Long"
            parameterType="com.muchinfo.mtp3pojos.vo.investor.PersonalizationDelParam">
        select t.accountid from taaccount t where <if test="vo.userid != null"> t.userid = #{vo.userid} and</if>
        not exists ( select 1 from accounttradeconfig u
        where u.accountid = t.accountid <if test="vo.memberuserid != null"> and u.memberuserid=#{vo.memberuserid} </if> <if test="vo.userid != null"> and u.userid = #{vo.userid} </if>
        <choose>
            <when test="vo.tradetype!=null and vo.tradetype==2">
                and u.tradetype=2
            </when>
            <otherwise>
                and (u.tradetype=1 or u.tradetype is null)
            </otherwise>
        </choose>
        )
    </select>
    <select id="getMonitorInvestorsByAccPos" resultType="com.muchinfo.mtp3pojos.vo.marketrun.TaaccountMonitorVo"
            parameterType="com.muchinfo.mtp3pojos.vo.marketrun.MarketRunPositionCountParam">
        select t.*,
        u.usertype,
        u.userid,
        u.accountname useraccountname,
        u.parentuserid,
        u.memberuserid,
        u.broker,
        v.loginids  loginid
        from taaccount t
        left join useraccount u on t.userid = u.userid
        left join useraccount ua on ua.userid = u.memberuserid
        left join VIEW_USER_LOGINIDS v on t.userid = v.userid
        where 1 = 1
        <if test="vo.goodsid != null and vo.goodsid !=''">
            and t.accountid in (select accountid from tradeposition tp where tp.goodsid = #{vo.goodsid})
        </if>
    </select>
    <select id="bankQueryAccInfo" resultType="com.muchinfo.mtp3pojos.vo.bankbusiness.BankQueryAccInfoResult"
            parameterType="java.lang.Integer">
        select t.accountid as accountid,us.accountname as accountname,
        u2.accountname as organizaName,u1.accountname as membername,
        t.currentbalance as balance,b.bankchildaccount
        from taaccount t
        left join bank_accountsign b on t.accountid = b.accountcode
        left join useraccount us on t.userid=us.userid
        left join useraccount u1 on us.memberuserid=u1.userid
        left join useraccount u2 on us.parentuserid=u2.userid
        <where>
            <if test="accountid != null and accountid != '' ">
                and t.accountid = #{accountid}
            </if>
        </where>
    </select>
</mapper>
