<?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.reconciliation.mapper.ReconciliationMapper">

    <resultMap type="ReconciliationVO" id="SsReconciliationResult" autoMapping="true" />

    <sql id="selectSsReconciliationVo">
        select
            reconciliation_id,
            order_paid,
            order_refund,
            settlement_amount,
            channel,
            channel_name,
            type,
            day,
            user_divide,
            user_refund,
            user_withdraw_amount,
            platform_service_fee,
            withdraw_service_fee,
            order_count,
            channel_cost,
            create_time,
            user_settlement,
            platform_revenue,
            user_balance
        from ss_reconciliation
    </sql>

    <sql id="searchCondition">
        <if test="query.orderPaid != null "> and order_paid = #{query.orderPaid}</if>
        <if test="query.orderRefund != null "> and order_refund = #{query.orderRefund}</if>
        <if test="query.settlementAmount != null "> and settlement_amount = #{query.settlementAmount}</if>
        <if test="query.channel != null "> and channel = #{query.channel}</if>
        <if test="query.channelName != null  and query.channelName != ''"> and channel_name like concat('%', #{query.channelName}, '%')</if>
        <if test="query.type != null "> and type = #{query.type}</if>
        <if test="query.day != null  and query.day != ''"> and day = #{query.day}</if>
        <if test="query.userDivide != null "> and user_divide = #{query.userDivide}</if>
        <if test="query.userRefund != null "> and user_refund = #{query.userRefund}</if>
        <if test="query.userWithdrawAmount != null "> and user_withdraw_amount = #{query.userWithdrawAmount}</if>
        <if test="query.platformServiceFee != null "> and platform_service_fee = #{query.platformServiceFee}</if>
        <if test="query.withdrawServiceFee != null "> and withdraw_service_fee = #{query.withdrawServiceFee}</if>
        <if test="query.orderCount != null "> and order_count = #{query.orderCount}</if>
        <if test="query.channelCost != null "> and channel_cost = #{query.channelCost}</if>
        <if test="query.userSettlement != null "> and user_settlement = #{query.userSettlement}</if>
        <if test="query.startTime != null "> and date(day) >= #{query.startTime}</if>
        <if test="query.endTime != null "> and date(day) &lt;= #{query.endTime}</if>
        <if test="query.params.beginTime != null and query.params.beginTime != ''"><!-- 开始时间检索 -->
            AND `day` &gt;= #{query.params.beginTime}
        </if>
        <if test="query.params.endTime != null and query.params.endTime != ''"><!-- 结束时间检索 -->
            AND `day` &lt;= #{query.params.endTime}
        </if>
    </sql>

    <select id="selectSsReconciliationList" parameterType="ReconciliationQuery" resultMap="SsReconciliationResult">
        <include refid="selectSsReconciliationVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectReconciliationListGroupBy" parameterType="ReconciliationQuery" resultMap="SsReconciliationResult">
        SELECT
        `day`,
            SUM(`order_paid`) AS `order_paid`,
            SUM(`order_refund`) AS `order_refund`,
            SUM(`settlement_amount`) AS `settlement_amount`,
            SUM(`user_divide`) AS `user_divide`,
            SUM(`user_refund`) AS `user_refund`,
            SUM(`user_withdraw_amount`) AS `user_withdraw_amount`,
            SUM(`platform_service_fee`) AS `platform_service_fee`,
            SUM(`withdraw_service_fee`) AS `withdraw_service_fee`,
            SUM(`order_count`) AS `order_count`,
            SUM(`channel_cost`) AS `channel_cost`,
            SUM(`user_settlement`) AS `user_settlement`,
            SUM(`platform_revenue`) AS `platform_revenue`,
            SUM(`user_balance`) AS `user_balance`
        FROM
        `ss_reconciliation`
        <where>
            <include refid="searchCondition"/>
        </where>
        group by `day`
    </select>

    <select id="selectSsReconciliationByReconciliationId" parameterType="Long" resultMap="SsReconciliationResult">
        <include refid="selectSsReconciliationVo"/>
        where reconciliation_id = #{reconciliationId}
    </select>

    <insert id="insertSsReconciliation" parameterType="Reconciliation" useGeneratedKeys="true" keyProperty="reconciliationId">
        insert into ss_reconciliation
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="orderPaid != null">order_paid,</if>
            <if test="orderRefund != null">order_refund,</if>
            <if test="settlementAmount != null">settlement_amount,</if>
            <if test="channel != null">channel,</if>
            <if test="channelName != null">channel_name,</if>
            <if test="type != null">type,</if>
            <if test="day != null">day,</if>
            <if test="userDivide != null">user_divide,</if>
            <if test="userRefund != null">user_refund,</if>
            <if test="userWithdrawAmount != null">user_withdraw_amount,</if>
            <if test="platformServiceFee != null">platform_service_fee,</if>
            <if test="withdrawServiceFee != null">withdraw_service_fee,</if>
            <if test="orderCount != null">order_count,</if>
            <if test="channelCost != null">channel_cost,</if>
            <if test="createTime != null">create_time,</if>
            <if test="userSettlement != null">user_settlement,</if>
            <if test="platformRevenue != null">platform_revenue,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="orderPaid != null">#{orderPaid},</if>
            <if test="orderRefund != null">#{orderRefund},</if>
            <if test="settlementAmount != null">#{settlementAmount},</if>
            <if test="channel != null">#{channel},</if>
            <if test="channelName != null">#{channelName},</if>
            <if test="type != null">#{type},</if>
            <if test="day != null">#{day},</if>
            <if test="userDivide != null">#{userDivide},</if>
            <if test="userRefund != null">#{userRefund},</if>
            <if test="userWithdrawAmount != null">#{userWithdrawAmount},</if>
            <if test="platformServiceFee != null">#{platformServiceFee},</if>
            <if test="withdrawServiceFee != null">#{withdrawServiceFee},</if>
            <if test="orderCount != null">#{orderCount},</if>
            <if test="channelCost != null">#{channelCost},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="userSettlement != null">#{userSettlement},</if>
            <if test="platformRevenue != null">#{platformRevenue},</if>
         </trim>
    </insert>

    <update id="updateSsReconciliation" parameterType="Reconciliation">
        update ss_reconciliation
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where reconciliation_id = #{data.reconciliationId}
    </update>

    <sql id="updateColumns">
        <if test="data.orderPaid != null">order_paid = #{data.orderPaid},</if>
        <if test="data.orderRefund != null">order_refund = #{data.orderRefund},</if>
        <if test="data.settlementAmount != null">settlement_amount = #{data.settlementAmount},</if>
        <if test="data.channel != null">channel = #{data.channel},</if>
        <if test="data.channelName != null">channel_name = #{data.channelName},</if>
        <if test="data.type != null">type = #{data.type},</if>
        <if test="data.day != null">day = #{data.day},</if>
        <if test="data.userDivide != null">user_divide = #{data.userDivide},</if>
        <if test="data.userRefund != null">user_refund = #{data.userRefund},</if>
        <if test="data.userWithdrawAmount != null">user_withdraw_amount = #{data.userWithdrawAmount},</if>
        <if test="data.platformServiceFee != null">platform_service_fee = #{data.platformServiceFee},</if>
        <if test="data.withdrawServiceFee != null">withdraw_service_fee = #{data.withdrawServiceFee},</if>
        <if test="data.orderCount != null">order_count = #{data.orderCount},</if>
        <if test="data.channelCost != null">channel_cost = #{data.channelCost},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.userSettlement != null">user_settlement = #{data.userSettlement},</if>
        <if test="data.platformRevenue != null">platform_revenue = #{data.platformRevenue},</if>
    </sql>

    <delete id="deleteSsReconciliationByReconciliationId" parameterType="Long">
        delete from ss_reconciliation where reconciliation_id = #{reconciliationId}
    </delete>

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