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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.BankAccountoutinapply">
        <id column="ACCOUNTCODE" property="accountcode" />
        <result column="CUSBANKID" property="cusbankid" />
        <result column="TRADEDATE" property="tradedate" />
        <result column="EXCHTICKET" property="exchticket" />
        <result column="EXTOPERATEID" property="extoperateid" />
        <result column="BANKTICKET" property="bankticket" />
        <result column="APPLYSTATUS" property="applystatus" />
        <result column="EXECUTETYPE" property="executetype" />
        <result column="AMOUNT" property="amount" />
        <result column="CURRENCY" property="currency" />
        <result column="CHARGE" property="charge" />
        <result column="ACCOUNTPWD" property="accountpwd" />
        <result column="UPDATETIME" property="updatetime" />
        <result column="BANKID" property="bankid" />
        <result column="BRANCHBANKID" property="branchbankid" />
        <result column="BRANCHBANKNAME" property="branchbankname" />
        <result column="BANKACCOUNTNO" property="bankaccountno" />
        <result column="BANKACCOUNTNAME" property="bankaccountname" />
        <result column="EXTENDINFO" property="extendinfo" />
        <result column="AUDITID" property="auditid" />
        <result column="AUDITTIME" property="audittime" />
        <result column="REMARK" property="remark" />
        <result column="NETADDR" property="netaddr" />
        <result column="ACCOUNTTICKET" property="accountticket" />
        <result column="CHECKERRORFLAG" property="checkerrorflag" />
        <result column="REMARK2" property="remark2" />
        <result column="REAUDITID" property="reauditid" />
        <result column="REAUDITTIME" property="reaudittime" />
        <result column="REAUDITREMARK" property="reauditremark" />
        <result column="APPLYREMARK" property="applyremark" />
        <result column="RELATEDORDERID" property="relatedorderid" />
        <result column="CAPAMOUNTOUT" property="capamountout" />
        <result column="INFAMOUNT" property="infamount" />
        <result column="PRIAMOUNT" property="priamount" />
        <result column="BANK_APPLY_TICKET" property="bankApplyTicket" />
        <result column="CERTIFICATEPHOTOURL" property="certificatephotourl" />
        <result column="SOUCREAMOUNT" property="soucreamount" />
        <result column="SOUCRECURRENCYID" property="soucrecurrencyid" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ACCOUNTCODE, CUSBANKID, TRADEDATE, EXCHTICKET, EXTOPERATEID, BANKTICKET, APPLYSTATUS, EXECUTETYPE, AMOUNT, CURRENCY, CHARGE, ACCOUNTPWD, UPDATETIME, BANKID, BRANCHBANKID, BRANCHBANKNAME, BANKACCOUNTNO, BANKACCOUNTNAME, EXTENDINFO, AUDITID, AUDITTIME, REMARK, NETADDR, ACCOUNTTICKET, CHECKERRORFLAG, REMARK2, REAUDITID, REAUDITTIME, REAUDITREMARK, APPLYREMARK, RELATEDORDERID, CAPAMOUNTOUT, INFAMOUNT, PRIAMOUNT, BANK_APPLY_TICKET, CERTIFICATEPHOTOURL, SOUCREAMOUNT, SOUCRECURRENCYID
    </sql>

    <select id="queryPutOrIn" resultType="com.muchinfo.mtp3pojos.vo.bankaccountsign.PutOrInAmountQueryResult"
            parameterType="com.muchinfo.mtp3pojos.vo.bankaccountsign.PutOrInAmountQueryParam">
        select DISTINCT(b.accountcode),
        b.cusbankid,
        b.tradedate,
        b.exchticket,
        b.amount,
        b.currency,
        b.executetype,
        b.applystatus,
        b.updatetime,
        (case when
        lo.loginids is null then u.userid || '/' || u.accountname
        else u.userid || '/' || lo.loginids || '/' || u.accountname end) accountname,
        u.usertype,
        b.charge,
        (case when u.usertype=5 then u.userid || '/' || to_char(u1.accountname) else '--' end) parentaccountname,
        b.bankaccountno,
        c.bankname,
        bc.cusbankname,
        bu.accountname brokername,
        mu.accountname memberaccountname
        from Bank_AccountOutInApply b
        left join TAAccount ta on ta.accountid = b.accountcode
        left join useraccount u on ta.relateduserid = u.userid
        left join useraccount bu on u.broker = bu.userid
        left join useraccount mu on u.memberuserid = mu.userid
        left join useraccount u1 on u.parentuserid = u1.userid
        left join Bank_BankInfo c on b.BankID = c.BankID
        left join Bank_CusBankInfo bc on bc.cusbankid = b.cusbankid
        left join VIEW_USER_LOGINIDS lo on u.userid = lo.userid
        <where>
            <if test="vo.areauserid !=null and vo.areauserid != '' ">
                and u.subarealevelpath like '%,'||#{vo.areauserid}||',%'
            </if>
            <if test="vo.loginid !=null and vo.loginid != '' ">
                and ta.userid in (select la.userid from loginaccount la where la.loginid = #{vo.loginid})
            </if>
            <if test="vo.accountcode != null and vo.accountcode != '' ">
                and b.accountcode = #{vo.accountcode}
            </if>
            <if test="vo.executetype != null ">
                and b.executetype = #{vo.executetype}
            </if>
            <if test="vo.searchstatus != null and vo.searchstatus != '' and vo.searchstatus == 1 ">
                and b.applystatus = 11
            </if>
            <if test="vo.searchstatus != null and vo.searchstatus != '' and vo.searchstatus == 2 ">
                and b.applystatus in (12,14,15,16,17)
            </if>
            <if test="vo.accountname !=null and vo.accountname != '' ">
                and u.userid = #{vo.accountname}
            </if>
            <if test="vo.cusbankid != null and vo.cusbankid != '' ">
                and b.cusbankid = #{vo.cusbankid}
            </if>
            <if test="vo.usertype !=null and vo.usertype != '' ">
                and u.usertype in (${vo.usertype})
            </if>
            <if test="vo.tradedatestart != null and vo.tradedatestart != '' ">
                and b.tradedate &gt;= #{vo.tradedatestart}
            </if>
            <if test="vo.tradedateend != null and vo.tradedateend != '' ">
                and b.tradedate &lt;= #{vo.tradedateend}
            </if>
            <if test="vo.userid !=null and vo.userid != '' ">
                and u.userid = #{vo.userid}
            </if>
            <if test="vo.applystatus != null ">
                and b.applystatus = #{vo.applystatus}
            </if>
        </where>
        order by b.updatetime desc
    </select>

    <select id="queryById" resultType="com.muchinfo.mtp3pojos.vo.bankaccountsign.PutOrInAmountQueryResultDetail"
            parameterType="java.lang.String">
        select ba.accountcode,ba.cusbankid,ba.tradedate,ba.exchticket,
        ba.extoperateid,ba.bankticket , ba.applystatus ,
        ba.executetype,ba.amount,ba.currency ,
        ba.charge,ba.accountpwd,ba.updatetime,ba.bankid,ba.branchbankid,
        ba.branchbankname,ba.bankaccountno,ba.bankaccountname,bc.cusbankname,
        ba.extendinfo,ba.auditid,ba.audittime,ba.remark,ba.applyremark,
        bb.bankname,ba.netaddr,ba.accountticket,ba.checkerrorflag,
        ba.remark2,sy.username,ta.balance,ta.usedmargin, ta.curdebt
        from Bank_AccountOutInApply ba
        left join taaccount ta on ta.accountid = ba.accountcode
        left join Bank_CusBankInfo bc on bc.cusbankid = ba.cusbankid
        left join Bank_BankInfo bb on bb.bankid = ba.bankid
        left join SYSTEMMANAGER sy on ba.auditid = sy.autoid
        <where>
            <if test="accountcode != null and accountcode != '' ">
                and ba.accountcode = #{accountcode}
            </if>
            <if test="cusbankid != null and cusbankid != '' ">
                and ba.cusbankid = #{cusbankid}
            </if>
            <if test="tradedate != null and tradedate != '' ">
                and ba.tradedate = #{tradedate}
            </if>
            <if test="exchticket != null and exchticket != '' ">
                and ba.exchticket = #{exchticket}
            </if>
        </where>
    </select>
    <select id="queryCusbanksForArea" resultType="com.muchinfo.mtp3pojos.entity.BankCusbankinfo">
        select bc.* from tce_areacusbankconfig t
        inner join bank_cusbankinfo bc on t.cusbankid = bc.cusbankid
        where t.areauserid=${areauserid} and t.configtype=${configtype}
    </select>
</mapper>
