<?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.reconciliationDate.mapper.ReconciliationDateMapper">

    <resultMap type="ReconciliationDateVO" id="ReconciliationDateResult" autoMapping="true"/>

    <sql id="selectReconciliationDateVo">
        select
            brd.id,
            brd.date,
            brd.channel_id,
            brd.channel_name,
            brd.area_id,
            brd.order_pay_amount,
            brd.order_refund_amount,
            brd.order_actual_amount,
            brd.pay_amount,
            brd.refund_amount,
            brd.actual_amount,
            brd.user_bonus,
            brd.user_bonus_refund,
            brd.user_actual_bonus,
            brd.platform_bonus,
            brd.platform_bonus_refund,
            brd.platform_actual_bonus,
            brd.total_bonus,
            brd.total_bonus_refund,
            brd.actual_bonus,
            brd.difference,
            brd.channel_cost,
            ba.name as area_name
        from bst_reconciliation_date brd
            left join bst_area ba on ba.id = brd.area_id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and brd.id = #{query.id}</if>
        <if test="query.date != null "> and brd.date = #{query.date}</if>
        <if test="query.channelId != null "> and brd.channel_id = #{query.channelId}</if>
        <if test="query.channelName != null  and query.channelName != ''"> and brd.channel_name like concat('%', #{query.channelName}, '%')</if>
        <if test="query.areaId != null "> and brd.area_id = #{query.areaId}</if>
        <if test="query.areaName != null  and query.areaName != ''"> and ba.name like concat('%', #{query.areaName}, '%')</if>
        <if test="query.dateRange != null and query.dateRange.size() > 0">
            and date(brd.date) &gt;= #{query.dateRange[0]}
            and date(brd.date) &lt;= #{query.dateRange[1]}
        </if>
        ${query.params.dataScope}
    </sql>

    <select id="selectReconciliationDateList" parameterType="ReconciliationDateQuery" resultMap="ReconciliationDateResult">
        <include refid="selectReconciliationDateVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectReconciliationDateById" parameterType="Long" resultMap="ReconciliationDateResult">
        <include refid="selectReconciliationDateVo"/>
        where brd.id = #{id}
    </select>

    <insert id="insertReconciliationDate" parameterType="ReconciliationDate" useGeneratedKeys="true" keyProperty="id">
        insert into bst_reconciliation_date
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="date != null">date,</if>
            <if test="channelId != null">channel_id,</if>
            <if test="channelName != null">channel_name,</if>
            <if test="areaId != null">area_id,</if>
            <if test="orderPayAmount != null">order_pay_amount,</if>
            <if test="orderRefundAmount != null">order_refund_amount,</if>
            <if test="orderActualAmount != null">order_actual_amount,</if>
            <if test="payAmount != null">pay_amount,</if>
            <if test="refundAmount != null">refund_amount,</if>
            <if test="actualAmount != null">actual_amount,</if>
            <if test="userBonus != null">user_bonus,</if>
            <if test="userBonusRefund != null">user_bonus_refund,</if>
            <if test="userActualBonus != null">user_actual_bonus,</if>
            <if test="platformBonus != null">platform_bonus,</if>
            <if test="platformBonusRefund != null">platform_bonus_refund,</if>
            <if test="platformActualBonus != null">platform_actual_bonus,</if>
            <if test="totalBonus != null">total_bonus,</if>
            <if test="totalBonusRefund != null">total_bonus_refund,</if>
            <if test="actualBonus != null">actual_bonus,</if>
            <if test="difference != null">difference,</if>
            <if test="channelCost != null">channel_cost,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="date != null">#{date},</if>
            <if test="channelId != null">#{channelId},</if>
            <if test="channelName != null">#{channelName},</if>
            <if test="areaId != null">#{areaId},</if>
            <if test="orderPayAmount != null">#{orderPayAmount},</if>
            <if test="orderRefundAmount != null">#{orderRefundAmount},</if>
            <if test="orderActualAmount != null">#{orderActualAmount},</if>
            <if test="payAmount != null">#{payAmount},</if>
            <if test="refundAmount != null">#{refundAmount},</if>
            <if test="actualAmount != null">#{actualAmount},</if>
            <if test="userBonus != null">#{userBonus},</if>
            <if test="userBonusRefund != null">#{userBonusRefund},</if>
            <if test="userActualBonus != null">#{userActualBonus},</if>
            <if test="platformBonus != null">#{platformBonus},</if>
            <if test="platformBonusRefund != null">#{platformBonusRefund},</if>
            <if test="platformActualBonus != null">#{platformActualBonus},</if>
            <if test="totalBonus != null">#{totalBonus},</if>
            <if test="totalBonusRefund != null">#{totalBonusRefund},</if>
            <if test="actualBonus != null">#{actualBonus},</if>
            <if test="difference != null">#{difference},</if>
            <if test="channelCost != null">#{channelCost},</if>
         </trim>
    </insert>

    <insert id="batchInsert" parameterType="ReconciliationDate" useGeneratedKeys="true" keyProperty="id">
        insert into bst_reconciliation_date
        <trim prefix="(" suffix=")" suffixOverrides=",">
            date,
            channel_id,
            channel_name,
            area_id,
            order_pay_amount,
            order_refund_amount,
            order_actual_amount,
            pay_amount,
            refund_amount,
            actual_amount,
            user_bonus,
            user_bonus_refund,
            user_actual_bonus,
            platform_bonus,
            platform_bonus_refund,
            platform_actual_bonus,
            total_bonus,
            total_bonus_refund,
            actual_bonus,
            difference,
            channel_cost,
        </trim>
        values
        <foreach collection="list" item="i" separator=",">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="i.date != null ">#{i.date},</if>
                <if test="i.date == null ">default,</if>
                <if test="i.channelId != null ">#{i.channelId},</if>
                <if test="i.channelId == null ">default,</if>
                <if test="i.channelName != null ">#{i.channelName},</if>
                <if test="i.channelName == null ">default,</if>
                <if test="i.areaId != null ">#{i.areaId},</if>
                <if test="i.areaId == null ">default,</if>
                <if test="i.orderPayAmount != null ">#{i.orderPayAmount},</if>
                <if test="i.orderPayAmount == null ">default,</if>
                <if test="i.orderRefundAmount != null ">#{i.orderRefundAmount},</if>
                <if test="i.orderRefundAmount == null ">default,</if>
                <if test="i.orderActualAmount != null ">#{i.orderActualAmount},</if>
                <if test="i.orderActualAmount == null ">default,</if>
                <if test="i.payAmount != null ">#{i.payAmount},</if>
                <if test="i.payAmount == null ">default,</if>
                <if test="i.refundAmount != null ">#{i.refundAmount},</if>
                <if test="i.refundAmount == null ">default,</if>
                <if test="i.actualAmount != null ">#{i.actualAmount},</if>
                <if test="i.actualAmount == null ">default,</if>
                <if test="i.userBonus != null ">#{i.userBonus},</if>
                <if test="i.userBonus == null ">default,</if>
                <if test="i.userBonusRefund != null ">#{i.userBonusRefund},</if>
                <if test="i.userBonusRefund == null ">default,</if>
                <if test="i.userActualBonus != null ">#{i.userActualBonus},</if>
                <if test="i.userActualBonus == null ">default,</if>
                <if test="i.platformBonus != null ">#{i.platformBonus},</if>
                <if test="i.platformBonus == null ">default,</if>
                <if test="i.platformBonusRefund != null ">#{i.platformBonusRefund},</if>
                <if test="i.platformBonusRefund == null ">default,</if>
                <if test="i.platformActualBonus != null ">#{i.platformActualBonus},</if>
                <if test="i.platformActualBonus == null ">default,</if>
                <if test="i.totalBonus != null ">#{i.totalBonus},</if>
                <if test="i.totalBonus == null ">default,</if>
                <if test="i.totalBonusRefund != null ">#{i.totalBonusRefund},</if>
                <if test="i.totalBonusRefund == null ">default,</if>
                <if test="i.actualBonus != null ">#{i.actualBonus},</if>
                <if test="i.actualBonus == null ">default,</if>
                <if test="i.difference != null ">#{i.difference},</if>
                <if test="i.difference == null ">default,</if>
                <if test="i.channelCost != null ">#{i.channelCost},</if>
                <if test="i.channelCost == null ">default,</if>
            </trim>
        </foreach>
    </insert>

    <update id="batchUpdate">
        update bst_reconciliation_date
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="date = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.date != null ">
                        WHEN #{item.id} THEN #{item.date}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `date`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="channel_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.channelId != null ">
                        WHEN #{item.id} THEN #{item.channelId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `channel_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="channel_name = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.channelName != null ">
                        WHEN #{item.id} THEN #{item.channelName}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `channel_name`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="area_id = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.areaId != null ">
                        WHEN #{item.id} THEN #{item.areaId}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `area_id`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="order_pay_amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.orderPayAmount != null ">
                        WHEN #{item.id} THEN #{item.orderPayAmount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `order_pay_amount`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="order_refund_amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.orderRefundAmount != null ">
                        WHEN #{item.id} THEN #{item.orderRefundAmount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `order_refund_amount`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="order_actual_amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.orderActualAmount != null ">
                        WHEN #{item.id} THEN #{item.orderActualAmount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `order_actual_amount`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="pay_amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.payAmount != null ">
                        WHEN #{item.id} THEN #{item.payAmount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `pay_amount`
                    </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="actual_amount = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.actualAmount != null ">
                        WHEN #{item.id} THEN #{item.actualAmount}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `actual_amount`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="user_bonus = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.userBonus != null ">
                        WHEN #{item.id} THEN #{item.userBonus}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `user_bonus`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="user_bonus_refund = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.userBonusRefund != null ">
                        WHEN #{item.id} THEN #{item.userBonusRefund}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `user_bonus_refund`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="user_actual_bonus = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.userActualBonus != null ">
                        WHEN #{item.id} THEN #{item.userActualBonus}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `user_actual_bonus`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="platform_bonus = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.platformBonus != null ">
                        WHEN #{item.id} THEN #{item.platformBonus}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `platform_bonus`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="platform_bonus_refund = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.platformBonusRefund != null ">
                        WHEN #{item.id} THEN #{item.platformBonusRefund}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `platform_bonus_refund`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="platform_actual_bonus = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.platformActualBonus != null ">
                        WHEN #{item.id} THEN #{item.platformActualBonus}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `platform_actual_bonus`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="total_bonus = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.totalBonus != null ">
                        WHEN #{item.id} THEN #{item.totalBonus}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `total_bonus`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="total_bonus_refund = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.totalBonusRefund != null ">
                        WHEN #{item.id} THEN #{item.totalBonusRefund}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `total_bonus_refund`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="actual_bonus = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.actualBonus != null ">
                        WHEN #{item.id} THEN #{item.actualBonus}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `actual_bonus`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="difference = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.difference != null ">
                        WHEN #{item.id} THEN #{item.difference}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `difference`
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="channel_cost = CASE id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.channelCost != null ">
                        WHEN #{item.id} THEN #{item.channelCost}
                    </when>
                    <otherwise>
                        WHEN #{item.id} THEN `channel_cost`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where id in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </update>

    <update id="updateReconciliationDate" parameterType="ReconciliationDate">
        update bst_reconciliation_date
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <sql id="updateColumns">
        <if test="data.date != null">date = #{data.date},</if>
        <if test="data.channelId != null">channel_id = #{data.channelId},</if>
        <if test="data.channelName != null">channel_name = #{data.channelName},</if>
        <if test="data.areaId != null">area_id = #{data.areaId},</if>
        <if test="data.orderPayAmount != null">order_pay_amount = #{data.orderPayAmount},</if>
        <if test="data.orderRefundAmount != null">order_refund_amount = #{data.orderRefundAmount},</if>
        <if test="data.orderActualAmount != null">order_actual_amount = #{data.orderActualAmount},</if>
        <if test="data.payAmount != null">pay_amount = #{data.payAmount},</if>
        <if test="data.refundAmount != null">refund_amount = #{data.refundAmount},</if>
        <if test="data.actualAmount != null">actual_amount = #{data.actualAmount},</if>
        <if test="data.userBonus != null">user_bonus = #{data.userBonus},</if>
        <if test="data.userBonusRefund != null">user_bonus_refund = #{data.userBonusRefund},</if>
        <if test="data.userActualBonus != null">user_actual_bonus = #{data.userActualBonus},</if>
        <if test="data.platformBonus != null">platform_bonus = #{data.platformBonus},</if>
        <if test="data.platformBonusRefund != null">platform_bonus_refund = #{data.platformBonusRefund},</if>
        <if test="data.platformActualBonus != null">platform_actual_bonus = #{data.platformActualBonus},</if>
        <if test="data.totalBonus != null">total_bonus = #{data.totalBonus},</if>
        <if test="data.totalBonusRefund != null">total_bonus_refund = #{data.totalBonusRefund},</if>
        <if test="data.actualBonus != null">actual_bonus = #{data.actualBonus},</if>
        <if test="data.difference != null">difference = #{data.difference},</if>
        <if test="data.channelCost != null">channel_cost = #{data.channelCost},</if>
    </sql>

    <delete id="deleteReconciliationDateById" parameterType="Long">
        delete from bst_reconciliation_date where id = #{id}
    </delete>

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