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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.BankAccountsignlog">
        <id column="EXCHTICKET" property="exchticket" />
        <result column="DIRECT" property="direct" />
        <result column="ACCOUNTCODE" property="accountcode" />
        <result column="CUSBANKID" property="cusbankid" />
        <result column="SIGNTYPE" property="signtype" />
        <result column="ISFORCE" property="isforce" />
        <result column="DEALSTATUS" property="dealstatus" />
        <result column="CURRENCY" property="currency" />
        <result column="ACCOUNTTYPE" property="accounttype" />
        <result column="ACCOUNTNAME" property="accountname" />
        <result column="BANKCARDTYPE" property="bankcardtype" />
        <result column="BANKACCOUNTNO" property="bankaccountno" />
        <result column="BANKACCOUNTTYPE" property="bankaccounttype" />
        <result column="BANKCHILDACCOUNT" property="bankchildaccount" />
        <result column="BANKID" property="bankid" />
        <result column="BRANCHBANKID" property="branchbankid" />
        <result column="BRANCHBANKNAME" property="branchbankname" />
        <result column="BANKACCOUNTNAME" property="bankaccountname" />
        <result column="CARDTYPE" property="cardtype" />
        <result column="CARDNO" property="cardno" />
        <result column="BANKPROVINCE" property="bankprovince" />
        <result column="BANKCITY" property="bankcity" />
        <result column="MOBILEPHONE" property="mobilephone" />
        <result column="EXTENDINFO" property="extendinfo" />
        <result column="CREATETIME" property="createtime" />
        <result column="REMARK" property="remark" />
        <result column="NETADDR" property="netaddr" />
        <result column="CHANGEAMOUNT" property="changeamount" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        EXCHTICKET, DIRECT, ACCOUNTCODE, CUSBANKID, SIGNTYPE, ISFORCE, DEALSTATUS, CURRENCY, ACCOUNTTYPE, ACCOUNTNAME, BANKCARDTYPE, BANKACCOUNTNO, BANKACCOUNTTYPE, BANKCHILDACCOUNT, BANKID, BRANCHBANKID, BRANCHBANKNAME, BANKACCOUNTNAME, CARDTYPE, CARDNO, BANKPROVINCE, BANKCITY, MOBILEPHONE, EXTENDINFO, CREATETIME, REMARK, NETADDR, CHANGEAMOUNT
    </sql>
    <select id="signLogQuery"
            resultType="com.muchinfo.mtp3pojos.vo.bankbusiness.BankAccountSignLogQueryResult">
        select b.accountcode , b.accountname , b.cusbankid , b.exchticket ,
        b.bankaccountno , b.bankaccountname , b.bankid , b.dealstatus ,
        b.cardtype , b.cardno , b.currency , b.CREATETIME,
        b.signtype , b.accounttype, b.bankchildaccount,
        c.cusbankname, c.canmodifyphone,c.canmodifybankcard,c.canbindcard,
        ta.userid,b.mobilephone,c.canmodifysigninfo
        from (select row_number() over(partition by ExchTicket order by DealStatus desc,CreateTime desc)rn, a.* from Bank_AccountSignLog a) b
        left join TAAccount ta on ta.accountid = b.accountcode
        left join useraccount ua on ta.relateduserid = ua.userid
        left join Bank_CusBankInfo c on b.cusbankid = c.cusbankid
        <where>
            b.rn =1
            <if test="vo.usertypes != null and vo.usertypes != '' ">
                and ua.usertype in (${vo.usertypes})
            </if>
            <if test="vo.accountcode != null and vo.accountcode != '' ">
                and b.accountcode = #{vo.accountcode}
            </if>
            <if test="vo.signtype != null">
                and b.signtype = #{vo.signtype}
            </if>
            <if test="vo.cusbankid != null and vo.cusbankid != '' ">
                and b.cusbankid = #{vo.cusbankid}
            </if>
            <if test="vo.userid !=null and vo.userid != '' ">
                and ta.userid = #{vo.userid}
            </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.cardtype !=null and vo.cardtype != '' ">
                and b.cardtype = #{vo.cardtype}
            </if>
            <if test="vo.startdate != null and vo.startdate != ''">
                <![CDATA[and b.createtime >= to_date(#{vo.startdate}, 'yyyy-mm-dd hh24:mi:ss')]]>
            </if>
            <if test="  vo.enddate != null and vo.enddate != ''">
                <![CDATA[and b.createtime <= to_date(#{vo.enddate}, 'yyyy-mm-dd hh24:mi:ss')]]>
            </if>

        </where>
        order by b.CREATETIME desc
    </select>

</mapper>
