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

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

    <sql id="selectBonusVo">
        select
            sb.id,
            sb.bill_id,
            sb.bill_no,
            sb.status,
            sb.arrival_id,
            sb.arrival_name,
            sb.arrival_type,
            sb.point,
            sb.amount,
            sb.refund_amount,
            sb.ancestors,
            sb.create_time,
            sb.pay_time,
            sb.dept_id,
            sb.pre_pay_time,
            sb.payed_amount,
            sb.wait_amount,
            sb.to_balance,
            sb.by_hand,
            sb.bst_type
        <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        from ss_bonus sb
            left join sys_dept sd on sd.dept_id = sb.dept_id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and sb.id = #{query.id}</if>
        <if test="query.billId != null "> and sb.bill_id = #{query.billId}</if>
        <if test="query.deptId != null "> and sb.dept_id = #{query.deptId}</if>
        <if test="query.billNo != null and query.billNo != ''"> and sb.bill_no like concat('%', #{query.billNo}, '%')</if>
        <if test="query.status != null  and query.status != ''"> and sb.status = #{query.status}</if>
        <if test="query.arrivalId != null "> and sb.arrival_id = #{query.arrivalId}</if>
        <if test="query.arrivalType != null  and query.arrivalType != ''"> and sb.arrival_type = #{query.arrivalType}</if>
        <if test="query.arrivalName != null  and query.arrivalName != ''"> and sb.arrival_name like concat('%', #{query.arrivalName}, '%')</if>
        <if test="query.payTimeYear != null "> and year(sb.pay_time) = #{query.payTimeYear}</if>
        <if test="query.payDateStart != null "> and date(sb.pay_time) >= #{query.payDateStart}</if>
        <if test="query.payDateEnd != null "> and date(sb.pay_time) &lt;= #{query.payDateEnd}</if>
        <if test="query.payTimeStart != null "> and sb.pay_time >= #{query.payTimeStart}</if>
        <if test="query.payTimeEnd != null "> and sb.pay_time &lt;= #{query.payTimeEnd}</if>
        <if test="query.prePayTimeStart != null "> and sb.pre_pay_time >= #{query.prePayTimeStart}</if>
        <if test="query.prePayTimeEnd != null "> and sb.pre_pay_time &lt;= #{query.prePayTimeEnd}</if>
        <if test="query.toBalance != null "> and sb.to_balance = #{query.toBalance}</if>
        <if test="query.byHand != null "> and sb.by_hand = #{query.byHand}</if>
        <if test="query.bstType != null "> and sb.bst_type = #{query.bstType}</if>
        <if test="query.payDate != null "> and date(sb.pay_time) = #{query.payDate}</if>
        <if test="query.hasRefund != null ">
            <if test="query.hasRefund">
                and sb.refund_amount > 0
            </if>
            <if test="!query.hasRefund">
                and sb.refund_amount = 0
            </if>
        </if>
        <if test="query.billIds != null and query.billIds.size() > 0 ">
            and sb.bill_id in
            <foreach collection="query.billIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="query.arrivalTypes != null and query.arrivalTypes.size() > 0 ">
            and sb.arrival_type in
            <foreach collection="query.arrivalTypes" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="query.arrivalIds != null and query.arrivalIds.size() > 0 ">
            and sb.arrival_id in
            <foreach collection="query.arrivalIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </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 sb.id = #{id}
    </select>

    <select id="selectCountByArrival" resultType="com.ruoyi.common.domain.vo.CommonCountVO">
        select
            sb.arrival_id as `key`,
            count(sb.id) as `count`
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <select id="selectBillAmountByArrival" resultType="com.ruoyi.common.domain.vo.CommonSumVO">
        select
            sb.arrival_id as `key`,
            sum(stb.money) as `sum`
        from ss_bonus sb
            left join sm_transaction_bill stb on stb.bill_id = sb.bill_id
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <resultMap id="ProvideBonusVO" type="ProvideBonusVO" autoMapping="true">
        <result property="arrivalAmount" column="arrival_amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>

    <select id="selectProvideBonus" resultMap="ProvideBonusVO">
        select
            p.arrival_id as provider_id,
            a.arrival_id as arrival_id,
            sum(a.amount - a.refund_amount) as arrival_amount
        from ss_bonus p
            left join ss_bonus a on a.bill_id = p.bill_id
        <where>
            <if test="query.providerIds != null and query.providerIds.size() > 0">
                and p.arrival_id in
                <foreach collection="query.providerIds" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="query.providerTypes != null and query.providerTypes.size() > 0">
                and p.arrival_type in
                <foreach collection="query.providerTypes" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="query.arrivalId != null">
                and a.arrival_id = #{query.arrivalId}
            </if>
            <if test="query.arrivalTypes != null and query.arrivalTypes.size() > 0">
                and a.arrival_type in
                <foreach collection="query.arrivalTypes" item="item" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
        group by p.arrival_id, a.arrival_id
    </select>

    <resultMap id="BonusMonthAmountVO" type="BonusMonthAmountVO">
        <result property="amount" column="amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>

    <select id="selectMonthPayedAmount" resultMap="BonusMonthAmountVO">
        select
            year(sb.pay_time) as `year`,
            month(sb.pay_time) as `month`,
            sum(payed_amount) as amount
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `month`
    </select>

    <select id="selectDailyAmount" resultType="BonusDailyAmountVO">
        select
            date(sb.pay_time) as `key`,
            sum(amount) as `sum`
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select
            count(sb.id)
        from ss_bonus sb
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <resultMap id="LongDecimalVO" type="LongDecimalVO" autoMapping="true">
        <id column="key" property="key"/>
        <id column="value" property="value" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>

    <select id="selectWaitAmountGroupByArrival" resultType="LongDecimalVO">
        select
            sb.arrival_id as `key`,
            sum(sb.wait_amount) as `value`
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <select id="selectPayedAmountGroupByArrival" resultType="LongDecimalVO">
        select
            sb.arrival_id as `key`,
            sum(sb.payed_amount) as `value`
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <resultMap id="LocalDateDecimalVO" type="LocalDateDecimalVO" autoMapping="true">
        <result column="value" property="value" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>
    <select id="selectDailyPayedAmount" resultMap="LocalDateDecimalVO">
        select
            date(sb.pay_time) as `key`,
            sum(sb.payed_amount) as `value`
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `key`
    </select>

    <select id="selectSumOfPayedAmount" resultType="java.math.BigDecimal">
        select
            sum(sb.payed_amount)
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <insert id="insertBonus" parameterType="Bonus" useGeneratedKeys="true" keyProperty="id">
        insert into ss_bonus
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="billId != null">bill_id,</if>
            <if test="billNo != null">bill_no,</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="refundAmount != null">refund_amount,</if>
            <if test="ancestors != null and ancestors != ''">ancestors,</if>
            <if test="createTime != null">create_time,</if>
            <if test="payTime != null">pay_time,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="prePayTime != null">pre_pay_time,</if>
            <if test="payedAmount != null">payed_amount,</if>
            <if test="waitAmount != null">wait_amount,</if>
            <if test="toBalance != null">to_balance,</if>
            <if test="byHand != null">by_hand,</if>
            <if test="bstType != null and bstType != ''">bst_type,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="billId != null">#{billId},</if>
            <if test="billNo != null">#{billNo},</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="refundAmount != null">#{refundAmount},</if>
            <if test="ancestors != null and ancestors != ''">#{ancestors},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="payTime != null">#{payTime},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="prePayTime != null">#{prePayTime},</if>
            <if test="payedAmount != null">#{payedAmount},</if>
            <if test="waitAmount != null">#{waitAmount},</if>
            <if test="toBalance != null">#{toBalance},</if>
            <if test="byHand != null">#{byHand},</if>
            <if test="bstType != null and bstType != ''">#{bstType},</if>
         </trim>
    </insert>

    <insert id="batchInsert">
        insert into ss_bonus(
            bill_id,
            bill_no,
            status,
            arrival_id,
            arrival_name,
            arrival_type,
            point,
            amount,
            refund_amount,
            ancestors,
            create_time,
            pay_time,
            dept_id,
            pre_pay_time,
            payed_amount,
            wait_amount,
            to_balance,
            bst_type
        )
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.billId != null">#{i.billId},</if>
                <if test="i.billId == null">default,</if>
                <if test="i.billNo != null">#{i.billNo},</if>
                <if test="i.billNo == null">default,</if>
                <if test="i.status != null and i.status != ''">#{i.status},</if>
                <if test="i.status == null">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.refundAmount != null">#{i.refundAmount},</if>
                <if test="i.refundAmount == null">default,</if>
                <if test="i.ancestors != null and i.ancestors != ''">#{i.ancestors},</if>
                <if test="i.ancestors == null or i.ancestors == ''">default,</if>
                <if test="i.createTime != null">#{i.createTime},</if>
                <if test="i.createTime == null">default,</if>
                <if test="i.payTime != null">#{i.payTime},</if>
                <if test="i.payTime == null">default,</if>
                <if test="i.deptId != null">#{i.deptId},</if>
                <if test="i.deptId == null">default,</if>
                <if test="i.prePayTime != null">#{i.prePayTime},</if>
                <if test="i.prePayTime == null">default,</if>
                <if test="i.payedAmount != null">#{i.payedAmount},</if>
                <if test="i.payedAmount == null">default,</if>
                <if test="i.waitAmount != null">#{i.waitAmount},</if>
                <if test="i.waitAmount == null">default,</if>
                <if test="i.toBalance != null">#{i.toBalance},</if>
                <if test="i.toBalance == null">default,</if>
                <if test="i.bstType != null and i.bstType != ''">#{i.bstType},</if>
                <if test="i.bstType == null or i.bstType == ''">default,</if>
            </trim>
        </foreach>
    </insert>

    <!--金额规则，无论金额如何变化，都需要遵循这个规则-->
    <sql id="amountRule">
        amount >= refund_amount + wait_amount + payed_amount
    </sql>

    <update id="refundWhenWaitDivide">
        update ss_bonus
        set refund_amount = refund_amount + #{amount},
            wait_amount = wait_amount - #{amount}
        where id = #{id} and wait_amount >= #{amount} and status = '2'
          and <include refid="amountRule"/>
    </update>

    <update id="refundWhenDividend">
        update ss_bonus
        set refund_amount = refund_amount + #{amount},
            payed_amount = payed_amount - #{amount}
        where id = #{id} and payed_amount >= #{amount} and status = '3'
        and <include refid="amountRule"/>
    </update>

    <update id="pay">
        update ss_bonus
        set payed_amount = payed_amount + #{amount},
            wait_amount = wait_amount - #{amount},
            status = '3',
            pay_time = #{payTime}
        where id = #{id} and wait_amount >= #{amount} and status = '2'
            and <include refid="amountRule"/>
    </update>

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

    <update id="batchUpdateAmount">
        update ss_bonus
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.amount != null">
                        WHEN #{item.id} THEN #{item.amount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN amount
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="wait_amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.waitAmount != null">
                        WHEN #{item.id} THEN #{item.waitAmount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN wait_amount
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="status = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.status != null">
                        WHEN #{item.id} THEN #{item.status}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `status`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="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 pre_pay_time
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.id}
        </foreach>
        and `status` = '1'
    </update>

    <sql id="updateColumns">
        <if test="data.billId != null">bill_id = #{data.billId},</if>
        <if test="data.billNo != null">bill_no = #{data.billNo},</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.arrivalName != null and data.arrivalName != ''">arrival_name = #{data.arrivalName},</if>
        <if test="data.arrivalType != null and data.arrivalType != ''">arrival_type = #{data.arrivalType},</if>
        <if test="data.point != null">`point` = #{data.point},</if>
        <if test="data.amount != null">amount = #{data.amount},</if>
        <if test="data.refundAmount != null">refund_amount = #{data.refundAmount},</if>
        <if test="data.ancestors != null and data.ancestors != ''">ancestors = #{data.ancestors},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.payTime != null">pay_time = #{data.payTime},</if>
        <if test="data.deptId != null">dept_id = #{data.deptId},</if>
        <if test="data.prePayTime != null">pre_pay_time = #{data.prePayTime},</if>
        <if test="data.payedAmount != null">payed_amount = #{data.payedAmount},</if>
        <if test="data.waitAmount != null">wait_amount = #{data.waitAmount},</if>
        <if test="data.toBalance != null">to_balance = #{data.toBalance},</if>
        <if test="data.byHand != null">by_hand = #{data.byHand},</if>
        <if test="data.bstType != null">bst_type = #{data.bstType},</if>
    </sql>

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

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