<?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.bonus.mapper.BonusMapper">

    <resultMap type="BonusVO" id="BonusResult" autoMapping="true"/>

    <sql id="checkAmount">
        bb.invalid_amount + bb.wait_amount + bb.payed_amount + bb.refund_amount = bb.amount
    </sql>

    <sql id="selectBonusVo">
        select
            bb.id,
            bb.bst_id,
            bb.bst_type,
            bb.status,
            bb.arrival_id,
            bb.arrival_name,
            bb.arrival_type,
            bb.point,
            bb.amount,
            bb.invalid_amount,
            bb.wait_amount,
            bb.payed_amount,
            bb.refund_amount,
            bb.pre_pay_time,
            bb.pay_time,
            bb.to_balance,
            bb.reason,
            bb.create_time,
            bb.area_id
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        bst_bonus bb
            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 bb.id = #{query.id}</if>
        <if test="query.bstId != null "> and bb.bst_id = #{query.bstId}</if>
        <if test="query.bstType != null  and query.bstType != ''"> and bb.bst_type = #{query.bstType}</if>
        <if test="query.status != null  and query.status != ''"> and bb.status = #{query.status}</if>
        <if test="query.arrivalId != null "> and bb.arrival_id = #{query.arrivalId}</if>
        <if test="query.arrivalName != null  and query.arrivalName != ''"> and bb.arrival_name like concat('%', #{query.arrivalName}, '%')</if>
        <if test="query.arrivalType != null  and query.arrivalType != ''"> and bb.arrival_type = #{query.arrivalType}</if>
        <if test="query.toBalance != null "> and bb.to_balance = #{query.toBalance}</if>
        <if test="query.reason != null  and query.reason != ''"> and bb.reason like concat('%', #{query.reason}, '%')</if>
        <if test="query.prePayTimeStart != null "> and bb.pre_pay_time &gt;= #{query.prePayTimeStart}</if>
        <if test="query.prePayTimeEnd != null "> and bb.pre_pay_time &lt;= #{query.prePayTimeEnd}</if>
        <if test="query.areaId != null "> and bb.area_id = #{query.areaId}</if>
        <if test="query.arrivalTypes != null and query.arrivalTypes.size() > 0">
            and bb.arrival_type in
            <foreach collection="query.arrivalTypes" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.createDateRange != null and query.createDateRange.size() > 1">
            and bb.create_time &gt;= #{query.createDateRange[0]}
            and bb.create_time &lt;= #{query.createDateRange[1]}
        </if>
        <if test="query.statusList != null and query.statusList.size() > 0">
            and bb.status in
            <foreach collection="query.statusList" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        ${@com.ruoyi.framework.util.DataScopeUtil@dataScope(
            null,
            "su.user_id",
            null,
            null,
            query.scope
        )}
        ${query.params.dataScope}
    </sql>

    <select id="selectBonusList" parameterType="BonusQuery" resultMap="BonusResult">
        <include refid="selectBonusVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectBonusById" parameterType="Long" resultMap="BonusResult">
        <include refid="selectBonusVo"/>
        where bb.id = #{id}
    </select>

    <insert id="insertBonus" parameterType="Bonus" useGeneratedKeys="true" keyProperty="id">
        insert into bst_bonus
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bstId != null">bst_id,</if>
            <if test="bstType != null and bstType != ''">bst_type,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="arrivalId != null">arrival_id,</if>
            <if test="arrivalName != null and arrivalName != ''">arrival_name,</if>
            <if test="arrivalType != null and arrivalType != ''">arrival_type,</if>
            <if test="point != null">point,</if>
            <if test="amount != null">amount,</if>
            <if test="invalidAmount != null">invalid_amount,</if>
            <if test="waitAmount != null">wait_amount,</if>
            <if test="payedAmount != null">payed_amount,</if>
            <if test="refundAmount != null">refund_amount,</if>
            <if test="prePayTime != null">pre_pay_time,</if>
            <if test="payTime != null">pay_time,</if>
            <if test="toBalance != null">to_balance,</if>
            <if test="reason != null">reason,</if>
            <if test="createTime != null">create_time,</if>
            <if test="areaId != null">area_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="bstId != null">#{bstId},</if>
            <if test="bstType != null and bstType != ''">#{bstType},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="arrivalId != null">#{arrivalId},</if>
            <if test="arrivalName != null and arrivalName != ''">#{arrivalName},</if>
            <if test="arrivalType != null and arrivalType != ''">#{arrivalType},</if>
            <if test="point != null">#{point},</if>
            <if test="amount != null">#{amount},</if>
            <if test="invalidAmount != null">#{invalidAmount},</if>
            <if test="waitAmount != null">#{waitAmount},</if>
            <if test="payedAmount != null">#{payedAmount},</if>
            <if test="refundAmount != null">#{refundAmount},</if>
            <if test="prePayTime != null">#{prePayTime},</if>
            <if test="payTime != null">#{payTime},</if>
            <if test="toBalance != null">#{toBalance},</if>
            <if test="reason != null">#{reason},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="areaId != null">#{areaId},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="Bonus" useGeneratedKeys="true" keyProperty="id">
        insert into bst_bonus
        <trim prefix="(" suffix=")" suffixOverrides=",">
            bst_id,
            bst_type,
            status,
            arrival_id,
            arrival_name,
            arrival_type,
            point,
            amount,
            invalid_amount,
            wait_amount,
            payed_amount,
            refund_amount,
            pre_pay_time,
            pay_time,
            to_balance,
            reason,
            create_time,
            area_id,
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.bstId != null ">#{i.bstId},</if>
                <if test="i.bstId == null ">default,</if>
                <if test="i.bstType != null  and i.bstType != ''">#{i.bstType},</if>
                <if test="i.bstType == null  or i.bstType == ''">default,</if>
                <if test="i.status != null  and i.status != ''">#{i.status},</if>
                <if test="i.status == null  or i.status == ''">default,</if>
                <if test="i.arrivalId != null ">#{i.arrivalId},</if>
                <if test="i.arrivalId == null ">default,</if>
                <if test="i.arrivalName != null and i.arrivalName != ''">#{i.arrivalName},</if>
                <if test="i.arrivalName == null  or i.arrivalName == ''">default,</if>
                <if test="i.arrivalType != null  and i.arrivalType != ''">#{i.arrivalType},</if>
                <if test="i.arrivalType == null  or i.arrivalType == ''">default,</if>
                <if test="i.point != null ">#{i.point},</if>
                <if test="i.point == null ">default,</if>
                <if test="i.amount != null ">#{i.amount},</if>
                <if test="i.amount == null ">default,</if>
                <if test="i.invalidAmount != null ">#{i.invalidAmount},</if>
                <if test="i.invalidAmount == null ">default,</if>
                <if test="i.waitAmount != null ">#{i.waitAmount},</if>
                <if test="i.waitAmount == null ">default,</if>
                <if test="i.payedAmount != null ">#{i.payedAmount},</if>
                <if test="i.payedAmount == null ">default,</if>
                <if test="i.refundAmount != null ">#{i.refundAmount},</if>
                <if test="i.refundAmount == null ">default,</if>
                <if test="i.prePayTime != null ">#{i.prePayTime},</if>
                <if test="i.prePayTime == null ">default,</if>
                <if test="i.payTime != null ">#{i.payTime},</if>
                <if test="i.payTime == null ">default,</if>
                <if test="i.toBalance != null ">#{i.toBalance},</if>
                <if test="i.toBalance == null ">default,</if>
                <if test="i.reason != null ">#{i.reason},</if>
                <if test="i.reason == null ">default,</if>
                <if test="i.createTime != null ">#{i.createTime},</if>
                <if test="i.createTime == null ">default,</if>
                <if test="i.areaId != null ">#{i.areaId},</if>
                <if test="i.areaId == null ">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="updateBonus" parameterType="Bonus">
        update bst_bonus
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <sql id="updateColumns">
        <if test="data.bstId != null">bst_id = #{data.bstId},</if>
        <if test="data.bstType != null and data.bstType != ''">bst_type = #{data.bstType},</if>
        <if test="data.status != null and data.status != ''">status = #{data.status},</if>
        <if test="data.arrivalId != null">arrival_id = #{data.arrivalId},</if>
        <if test="data.arrivalType != null and data.arrivalType != ''">arrival_type = #{data.arrivalType},</if>
        <if test="data.arrivalName != null and data.arrivalName != ''">arrival_name = #{data.arrivalName},</if>
        <if test="data.point != null">point = #{data.point},</if>
        <if test="data.amount != null">amount = #{data.amount},</if>
        <if test="data.invalidAmount != null">invalid_amount = #{data.invalidAmount},</if>
        <if test="data.waitAmount != null">wait_amount = #{data.waitAmount},</if>
        <if test="data.payedAmount != null">payed_amount = #{data.payedAmount},</if>
        <if test="data.refundAmount != null">refund_amount = #{data.refundAmount},</if>
        <if test="data.prePayTime != null">pre_pay_time = #{data.prePayTime},</if>
        <if test="data.payTime != null">pay_time = #{data.payTime},</if>
        <if test="data.toBalance != null">to_balance = #{data.toBalance},</if>
        <if test="data.reason != null">reason = #{data.reason},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.areaId != null">area_id = #{data.areaId},</if>
    </sql>

    <delete id="deleteBonusById" parameterType="Long">
        delete from bst_bonus where id = #{id}
    </delete>

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

    
    <update id="prepay">
        update bst_bonus bb
        <trim prefix="SET" suffixOverrides=",">
            bb.wait_amount = bb.wait_amount + bb.invalid_amount,
            bb.invalid_amount = 0,
            bb.status = 'WAIT_DIVIDE',
            <foreach open="bb.pre_pay_time = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.prePayTime != null">
                        WHEN #{item.id} THEN #{item.prePayTime}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN bb.pre_pay_time
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where bb.id in
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item.id}
            </foreach>
            and <include refid="checkAmount"/>
            and bb.status = 'INVALID'
    </update>

    <update id="pay">
        update bst_bonus bb
        set payed_amount = payed_amount + #{amount},
            wait_amount = wait_amount - #{amount},
            status = 'DIVIDEND',
            pay_time = #{payTime}
        where id = #{id}
            and wait_amount >= #{amount}
            and status = 'WAIT_DIVIDE'
            and <include refid="checkAmount"/>
    </update>

    <update id="refundWhenInvalid">
        update bst_bonus bb
        set refund_amount = refund_amount + #{amount},
            invalid_amount = invalid_amount - #{amount}
        where id = #{id} and invalid_amount >= #{amount} and status = 'INVALID'
          and <include refid="checkAmount"/>
    </update>

    <update id="refundWhenWaitDivide">
        update bst_bonus bb
        set refund_amount = refund_amount + #{amount},
            wait_amount = wait_amount - #{amount}
        where id = #{id} and wait_amount >= #{amount} and status = 'WAIT_DIVIDE'
          and <include refid="checkAmount"/>
    </update>

    <update id="refundWhenDividend">
        update bst_bonus bb
        set refund_amount = refund_amount + #{amount},
            payed_amount = payed_amount - #{amount}
        where id = #{id} and payed_amount >= #{amount} and status = 'DIVIDEND'
        and <include refid="checkAmount"/>
    </update>

    <!-- selectCount -->

    <select id="selectCount" resultType="Integer">
        select count(bb.id)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- selectSumOfAmount -->

    <select id="selectSumOfAmount" resultType="BigDecimal">
        select sum(bb.amount)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- selectSumOfRefundedAmount -->

    <select id="selectSumOfRefundAmount" resultType="BigDecimal">
        select sum(bb.refund_amount)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- selectDailySumOfAmount -->

    <select id="selectDailySumOfAmount" resultMap="com.ruoyi.common.mapper.CommonMapper.LocalDateDecimalVO">
        select
            date(bb.create_time) as `key`
            sum(bb.amount) as `value`
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <!-- selectDailyStat -->

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

    <select id="selectSumOfWaitAmount" resultType="java.math.BigDecimal">
        select sum(bb.wait_amount)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

</mapper>
