<?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.ruoyi.bst.refund.mapper.RefundMapper">

    <resultMap type="RefundVO" id="RefundResult" autoMapping="true"/>

    <sql id="selectRefundVo">
        select
            br.id,
            br.no,
            br.pay_id,
            br.amount,
            br.create_time,
            br.status,
            br.reason,
            br.user_id,
            br.user_name,
            br.type,
            bp.channel_id,
            bp.app_id,
            bp.no as pay_no,
            bp.amount as pay_amount,
            bp.bst_id as pay_bst_id,
            bp.bst_type as pay_bst_type,
            bp.area_id as pay_area_id
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        bst_refund br
            left join bst_pay bp on br.pay_id = bp.id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and br.id = #{query.id}</if>
        <if test="query.no != null  and query.no != ''"> and br.no like concat('%', #{query.no}, '%')</if>
        <if test="query.eqNo != null  and query.eqNo != ''"> and br.no = #{query.eqNo}</if>
        <if test="query.payId != null "> and br.pay_id = #{query.payId}</if>
        <if test="query.status != null  and query.status != ''"> and br.status = #{query.status}</if>
        <if test="query.reason != null  and query.reason != ''"> and br.reason like concat('%', #{query.reason}, '%')</if>
        <if test="query.userId != null "> and br.user_id = #{query.userId}</if>
        <if test="query.userName != null  and query.userName != ''"> and br.user_name like concat('%', #{query.userName}, '%')</if>
        <if test="query.payBstId != null "> and bp.bst_id = #{query.payBstId}</if>
        <if test="query.payBstType != null  and query.payBstType != ''"> and bp.bst_type = #{query.payBstType}</if>
        <if test="query.payAreaId != null "> and bp.area_id = #{query.payAreaId}</if>
        <if test="query.channelId != null "> and bp.channel_id = #{query.channelId}</if>
        <if test="query.type != null  and query.type != ''"> and br.type = #{query.type}</if>
        <if test="query.createDateRange != null and query.createDateRange.size() > 1"> 
            and date(br.create_time) &gt;= #{query.createDateRange[0]}
            and date(br.create_time) &lt;= #{query.createDateRange[1]}
        </if>
        <if test="query.createDate != null">and date(br.create_time) = #{query.createDate}</if>
        <if test="query.statusList != null and query.statusList.size() > 0">
            and br.status in
            <foreach item="status" collection="query.statusList" open="(" separator="," close=")">
                #{status}
            </foreach>
        </if>
        ${query.params.dataScope}
    </sql>

    <select id="selectRefundList" parameterType="RefundQuery" resultMap="RefundResult">
        <include refid="selectRefundVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectRefundById" parameterType="Long" resultMap="RefundResult">
        <include refid="selectRefundVo"/>
        where br.id = #{id}
    </select>

    <insert id="insertRefund" parameterType="Refund" useGeneratedKeys="true" keyProperty="id">
        insert into bst_refund
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="no != null and no != ''">no,</if>
            <if test="payId != null">pay_id,</if>
            <if test="amount != null">amount,</if>
            <if test="createTime != null">create_time,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="reason != null">reason,</if>
            <if test="userId != null">user_id,</if>
            <if test="userName != null">user_name,</if>
            <if test="type != null and type != ''">type,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="no != null and no != ''">#{no},</if>
            <if test="payId != null">#{payId},</if>
            <if test="amount != null">#{amount},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="reason != null">#{reason},</if>
            <if test="userId != null">#{userId},</if>
            <if test="userName != null">#{userName},</if>
            <if test="type != null and type != ''">#{type},</if>
         </trim>
    </insert>

    <update id="updateRefund" parameterType="Refund">
        update bst_refund
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <sql id="updateColumns">
        <if test="data.no != null and data.no != ''">no = #{data.no},</if>
        <if test="data.payId != null">pay_id = #{data.payId},</if>
        <if test="data.amount != null">amount = #{data.amount},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.status != null and data.status != ''">status = #{data.status},</if>
        <if test="data.reason != null">reason = #{data.reason},</if>
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.userName != null">user_name = #{data.userName},</if>
        <if test="data.type != null and data.type != ''">type = #{data.type},</if>
    </sql>

    <delete id="deleteRefundById" parameterType="Long">
        delete from bst_refund where id = #{id}
    </delete>

    <delete id="deleteRefundByIds" parameterType="String">
        delete from bst_refund where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- updateByQuery -->

    <update id="updateByQuery">
        update bst_refund br
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        <where>
            <include refid="searchCondition"/>
        </where>
    </update>

    <!-- selectSumofAmount --> 

    <select id="selectSumofAmount">
        select sum(br.amount)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

</mapper>
