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

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.muchinfo.mtp3pojos.entity.BankAccountsign">
        <id column="ACCOUNTCODE" property="accountcode" />
        <result column="CUSBANKID" property="cusbankid" />
        <result column="SIGNSTATUS" property="signstatus" />
        <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="DIRECT" property="direct" />
        <result column="MOBILEPHONE" property="mobilephone" />
        <result column="EXTENDINFO" property="extendinfo" />
        <result column="EXCHTICKET" property="exchticket" />
        <result column="UPDATETIME" property="updatetime" />
        <result column="APPLICATIONTIME" property="applicationtime" />
        <result column="NETADDR" property="netaddr" />
        <result column="AUDITID" property="auditid" />
        <result column="AUDITTIME" property="audittime" />
        <result column="APPLYEXCHTICKET" property="applyexchticket" />
    </resultMap>

    <resultMap type="com.muchinfo.mtp3pojos.vo.bankaccountsign.AccountSignQueryResult" id="pageResultMap">
        <result column="cusbankname" property="cusBankName" />
        <result column="bankaccountname" property="bankAccountName" />
        <result column="bankaccountno" property="bankAccountNo" />
        <result column="signstatus" property="signStatus" />
        <result column="accountcode" property="accountCode" />
        <result column="accountname" property="accountName" />
        <result column="bankchildaccount" property="bankChildAccount" />
        <result column="cardtype" property="cardType" />
        <result column="cardno" property="cardNo" />
        <result column="currency" property="currency" />
        <result column="canrelease" property="isCanRelease" />
        <result column="canmodifyphone" property="canModifyPhone" />
        <result column="canmodifysigninfo" property="canModifySignInfo" />
        <result column="applyexchticket" property="applyexchticket" />
        <result column="cusbankid" property="cusbankid" />
        <result column="canbindcard" property="canBindCard" />
        <association column="USERID" property="loginIds" jdbcType="VARCHAR"
                     select="com.muchinfo.mtp3interface.mapper.ILoginaccountMapper.getLoginidsByUserid" />
        <association column="BANKID" property="bankName" jdbcType="VARCHAR"
                     select="getBankname" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ACCOUNTCODE, CUSBANKID, SIGNSTATUS, CURRENCY, ACCOUNTTYPE, ACCOUNTNAME, BANKCARDTYPE, BANKACCOUNTNO, BANKACCOUNTTYPE, BANKCHILDACCOUNT, BANKID, BRANCHBANKID, BRANCHBANKNAME, BANKACCOUNTNAME, CARDTYPE, CARDNO, BANKPROVINCE, BANKCITY, DIRECT, MOBILEPHONE, EXTENDINFO, EXCHTICKET, UPDATETIME, APPLICATIONTIME, NETADDR, AUDITID, AUDITTIME, APPLYEXCHTICKET
    </sql>


    <select id="selectByParam" resultMap="pageResultMap"
            parameterType="com.muchinfo.mtp3pojos.vo.bankaccountsign.AccountSignQueryParam">
        select
        b.accountcode , b.accountname , b.cusbankid ,
        b.bankaccountno , b.bankaccountname , b.bankid ,
        b.cardtype , b.cardno , b.currency ,
        b.signstatus , b.accounttype, b.bankchildaccount,
        c.cusbankname, c.canmodifyphone,b.applyexchticket,c.canmodifybankcard,c.canbindcard,
        ta.userid,b.mobilephone,c.canmodifysigninfo,c.canrelease
        from Bank_AccountSign b
        left join TAAccount ta on ta.accountid = b.accountcode
        left join Bank_CusBankInfo c on b.cusbankid = c.cusbankid
        <where>
            <if test="vo.accountCode != null and vo.accountCode != '' ">
                and b.accountcode = #{vo.accountCode}
            </if>
            <if test="vo.signStatus != null and vo.signStatus != '' ">
                and b.signstatus = #{vo.signStatus}
            </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>
        </where>
        order by b.applicationtime desc
    </select>

    <select id="getBankname" parameterType="long" resultType="string">
        select
            bc.bankname
        from Bank_BankInfo bc
        where bc.bankid = #{bankid,jdbcType=VARCHAR}
    </select>

    <select id="query" resultType="com.muchinfo.mtp3pojos.vo.organ.UserAccountSignlogResult">
        select '--' as histradedate, t.* from Bank_AccountSignLog t
        <where>
            <if test="accountcode != null and accountcode != ''">
                and t.accountcode=#{accountcode}
            </if>
        </where>
        order by t.createtime desc
    </select>

    <select id="queryhis" resultType="com.muchinfo.mtp3pojos.vo.organ.UserAccountSignlogResult">
        select t.* from His_Bank_AccountSignLog t
        <where>
            <if test="accountcode != null and accountcode != ''">
                and t.accountcode=#{accountcode}
            </if>
        </where>
        order by t.createtime desc
    </select>
</mapper>
