<?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.hzs.member.account.mapper.CuMemberTransferMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.hzs.common.domain.member.account.CuMemberTransfer">
        <id column="PK_ID" property="pkId"/>
        <result column="DEL_FLAG" property="delFlag"/>
        <result column="PK_CREATOR" property="pkCreator"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="PK_MODIFIED" property="pkModified"/>
        <result column="MODIFIED_TIME" property="modifiedTime"/>
        <result column="PK_COUNTRY" property="pkCountry"/>
        <result column="PK_MEMBER" property="pkMember"/>
        <result column="TRANSFER_CODE" property="transferCode"/>
        <result column="PK_ACCOUNT" property="pkAccount"/>
        <result column="TO_PK_MEMBER" property="toPkMember"/>
        <result column="PK_TO_ACCOUNT" property="pkToAccount"/>
        <result column="PK_RATE" property="pkRate"/>
        <result column="TRANSFER_MONEY" property="transferMoney"/>
        <result column="REMARKS" property="remarks"/>
        <result column="APPROVE_STATE" property="approveState"/>
        <result column="APPROVER" property="approver"/>
        <result column="APPROVE_TIME" property="approveTime"/>
        <result column="SERVICE_CHARGE" property="serviceCharge"/>
        <result column="ISSUED_AMOUNT" property="issuedAmount"/>
    </resultMap>


    <sql id="select_base">
        select r.*,
               m.member_code as memberCode,
               m.member_name as memberName,
               t.member_code    toMemberCode,
               t.member_name    toMemberName
        from cu_member_transfer r
                 left join cu_member m
                           on r.pk_member = m.pk_id
                 left join cu_member t
                           on r.to_pk_member = t.pk_id
    </sql>

    <sql id="ids_where">
        <where>
            r.del_flag='0'
            and r.pk_id in
            <foreach collection="pkIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </where>
    </sql>

    <sql id="list_where">
        <where>
            r.del_flag='0'
            and m.system_type = #{systemType}
            <if test="memberName != null and memberName != ''">
                and m.member_name like #{memberName} || '%'
            </if>
            <if test="memberCode != null and memberCode != ''">
                and m.member_code like #{memberCode} || '%'
            </if>
            <if test="toMemberName != null and toMemberName != ''">
                and t.member_name like #{toMemberName} || '%'
            </if>
            <if test="toMemberCode != null and toMemberCode != ''">
                and t.member_code like #{toMemberCode} || '%'
            </if>
            <if test="pkAccount != null">
                and r.PK_ACCOUNT = #{pkAccount}
            </if>
            <if test="pkToAccount != null">
                and r.PK_TO_ACCOUNT = #{pkToAccount}
            </if>
            <if test="transferCode != null and transferCode != ''">
                and r.TRANSFER_CODE like #{transferCode} || '%'
            </if>
            <if test="remarks != null and remarks != ''">
                and r.REMARKS = #{remarks}
            </if>
            <if test="pkCountry != null">
                and r.pk_country = #{pkCountry}
            </if>
            <if test="startDate != null and startDate != ''">
                and r.CREATION_TIME >= to_date(#{startDate}, 'yyyy-mm-dd')
            </if>
            <if test="endDate != null and endDate != ''">
                and r.CREATION_TIME &lt;= to_date(#{endDate} || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
            </if>
        </where>
    </sql>

    <select id="selectMemberTransferList" resultType="com.hzs.common.domain.member.account.CuMemberTransferExt">
        <include refid="select_base"/>
        <choose>
            <when test="pkIds != null and pkIds.size >0">
                <include refid="ids_where"></include>
            </when>
            <otherwise>
                <include refid="list_where"></include>
            </otherwise>
        </choose>
        order by r.creation_time Desc
    </select>

</mapper>
