<?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.bonusRefund.mapper.BonusRefundMapper">

    <resultMap type="BonusRefundVO" id="BonusRefundResult" autoMapping="true"/>

    <sql id="selectBonusRefundVo">
        select
            bbr.id,
            bbr.bonus_id,
            bbr.refund_amount,
            bbr.create_time,
            bb.arrival_type as bonus_arrival_type,
            bb.arrival_name as bonus_arrival_name,
            bb.arrival_id as bonus_arrival_id,
            bb.area_id as bonus_area_id,
            bb.channel_id as bonus_channel_id
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        bst_bonus_refund bbr
            left join bst_bonus bb on bb.id = bbr.bonus_id
            left join sys_user su on su.user_id = bb.arrival_id and bb.arrival_type in ('AGENT', 'MCH', 'JOIN', 'PARTNER')
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and bbr.id = #{query.id}</if>
        <if test="query.bonusId != null "> and bbr.bonus_id = #{query.bonusId}</if>
        <if test="query.createDate != null "> and date(bbr.create_time) = #{query.createDate}</if>
        <if test="query.bonusArrivalType != null and query.bonusArrivalType != '' "> and bb.arrival_type = #{query.bonusArrivalType}</if>
        <if test="query.bonusArrivalName != null and query.bonusArrivalName != '' "> and bb.arrival_name like concat('%', #{query.bonusArrivalName}, '%')</if>
        <if test="query.bonusArrivalId != null "> and bb.arrival_id = #{query.bonusArrivalId}</if>
        <if test="query.bonusAreaId != null "> and bb.area_id = #{query.bonusAreaId}</if>
        <if test="query.bonusChannelId != null "> and bb.channel_id = #{query.bonusChannelId}</if>
        <if test="query.bonusArrivalTypes != null and query.bonusArrivalTypes.size() > 0 ">
            and bb.arrival_type in
            <foreach collection="query.bonusArrivalTypes" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.bonusStatusList != null and query.bonusStatusList.size() > 0">
            and bb.status in
            <foreach collection="query.bonusStatusList" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.createDateRange != null and query.createDateRange.size() > 1">
            and date(bbr.create_time) &gt;= #{query.createDateRange[0]}
            and date(bbr.create_time) &lt;= #{query.createDateRange[1]}
        </if>
        ${@com.ruoyi.framework.util.DataScopeUtil@dataScope(
            null,
            "su.user_id",
            null,
            null,
            query.scope
        )}
        ${query.params.dataScope}
    </sql>

    <select id="selectBonusRefundList" parameterType="BonusRefundQuery" resultMap="BonusRefundResult">
        <include refid="selectBonusRefundVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectBonusRefundById" parameterType="Long" resultMap="BonusRefundResult">
        <include refid="selectBonusRefundVo"/>
        where bbr.id = #{id}
    </select>

    <insert id="insertBonusRefund" parameterType="BonusRefund" useGeneratedKeys="true" keyProperty="id">
        insert into bst_bonus_refund
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bonusId != null">bonus_id,</if>
            <if test="refundAmount != null">refund_amount,</if>
            <if test="createTime != null">create_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="bonusId != null">#{bonusId},</if>
            <if test="refundAmount != null">#{refundAmount},</if>
            <if test="createTime != null">#{createTime},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="BonusRefund" useGeneratedKeys="true" keyProperty="id">
        insert into bst_bonus_refund
        <trim prefix="(" suffix=")" suffixOverrides=",">
            bonus_id,
            refund_amount,
            create_time,
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.bonusId != null ">#{i.bonusId},</if>
                <if test="i.bonusId == null ">default,</if>
                <if test="i.refundAmount != null ">#{i.refundAmount},</if>
                <if test="i.refundAmount == null ">default,</if>
                <if test="i.createTime != null ">#{i.createTime},</if>
                <if test="i.createTime == null ">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="batchUpdate">
        update bst_bonus_refund
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="bonus_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.bonusId != null ">
                        WHEN #{item.id} THEN #{item.bonusId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `bonus_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="refund_amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.refundAmount != null ">
                        WHEN #{item.id} THEN #{item.refundAmount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `refund_amount`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="create_time = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.createTime != null ">
                        WHEN #{item.id} THEN #{item.createTime}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `create_time`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </update>

    <update id="updateBonusRefund" parameterType="BonusRefund">
        update bst_bonus_refund
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <sql id="updateColumns">
        <if test="data.bonusId != null">bonus_id = #{data.bonusId},</if>
        <if test="data.refundAmount != null">refund_amount = #{data.refundAmount},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
    </sql>

    <delete id="deleteBonusRefundById" parameterType="Long">
        delete from bst_bonus_refund where id = #{id}
    </delete>

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

    <!-- selectSumOfAmount -->

    <select id="selectSumOfAmount" parameterType="BonusRefundQuery" resultType="BigDecimal">
        select sum(bbr.refund_amount)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- selectDailyStat -->

    <select id="selectDailyStat" resultType="BonusRefundDailyStatVO">
        select
            <foreach item="item" collection="keys" separator=",">
                ${item}
            </foreach>
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `date`
    </select>

</mapper>
