<?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.dividendDetail.mapper.DividendDetailMapper">

    <resultMap type="DividendDetailVO" id="RlDividendDetailResult" autoMapping="true" />

    <sql id="selectRlDividendDetailVo">
        select dd.id,
               dd.partner_id,
               dd.partner_name,
               dd.partner_phone,
               dd.partner_type,
               dd.order_no,
               dd.status,
               dd.refund_amount,
               dd.total_amount,
               dd.dividend_amount,
               dd.settlement_amount,
               dd.dividend_proportion,
               dd.create_time,
               dd.pay_channel
        from ss_dividend_detail dd
        left join ss_user u on u.user_id = dd.partner_id
    </sql>

    <sql id="searchCondition">
        <if test="agentName != null "> and a.name like concat('%', #{agentName}, '%')</if>
        <if test="partnerId != null "> and dd.partner_id = #{partnerId}</if>
        <if test="status != null "> and dd.status = #{status}</if>
        <if test="partnerName != null "> and dd.partner_name like concat('%', #{partnerName}, '%')</if>
        <if test="partnerPhone != null "> and dd.partner_phone like concat('%', #{partnerPhone}, '%')</if>
        <if test="partnerType != null "> and dd.partner_type = #{partnerType}</if>
        <if test="orderNo != null  and orderNo != ''"> and dd.order_no like concat('%', #{orderNo}, '%')</if>
        <if test="totalAmount != null "> and dd.total_amount = #{totalAmount}</if>
        <if test="dividendAmount != null "> and dd.dividend_amount = #{dividendAmount}</if>
        <if test="dividendProportion != null "> and dd.dividend_proportion = #{dividendProportion}</if>
        <if test="startTime != null"> and date(dd.create_time) >= #{startTime}</if>
        <if test="endTime != null"> and date(dd.create_time) &lt;= #{endTime}</if>
        <if test="payChannel != null "> and dd.pay_channel = #{payChannel}</if>
    </sql>

    <select id="selectRlDividendDetailList" parameterType="DividendDetailQuery" resultMap="RlDividendDetailResult">
        <include refid="selectRlDividendDetailVo"/>
        where 1=1
        <include refid="searchCondition"/>
        ${params.dataScope}
        order by dd.create_time desc
    </select>

    <select id="selectRlDividendDetailById" parameterType="Long" resultMap="RlDividendDetailResult">
        <include refid="selectRlDividendDetailVo"/>
        where id = #{id}
    </select>

    <select id="selectRlDividendDetailListByOrderNo" parameterType="String"
            resultMap="RlDividendDetailResult">
        <include refid="selectRlDividendDetailVo"/>
        where dd.order_no = #{orderNo}
    </select>

    <select id="select7DaysIncome" resultType="com.ruoyi.system.domain.vo.InCome">
        SELECT
            date_list.day,
            COALESCE(SUM(s.settlement_amount), 0) as orderIncome,
            COALESCE(COUNT(DISTINCT s.order_no), 0) as orderNum
        FROM (
                 SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL n DAY), '%Y-%m-%d') as day
                 FROM (
                     SELECT 0 as n UNION ALL
                     SELECT 1 UNION ALL
                     SELECT 2 UNION ALL
                     SELECT 3 UNION ALL
                     SELECT 4 UNION ALL
                     SELECT 5 UNION ALL
                     SELECT 6
                     ) nums
             ) date_list
                 LEFT JOIN ss_dividend_detail s ON DATE_FORMAT(s.create_time, '%Y-%m-%d') = date_list.day
            AND s.partner_id = #{merchantId}
            AND s.status = '1'
        GROUP BY date_list.day
        ORDER BY date_list.day DESC
    </select>


    <select id="select30DaysIncome" resultType="com.ruoyi.system.domain.vo.InCome">
        SELECT
            date_list.day,
            COALESCE(SUM(s.settlement_amount), 0) as orderIncome,
            COALESCE(COUNT(DISTINCT s.order_no), 0) as orderNum
        FROM (
                 SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL n DAY), '%Y-%m-%d') as day
                 FROM (
                     SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                     UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
                     UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
                     UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
                     UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
                     UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
                     ) nums
             ) date_list
                 LEFT JOIN ss_dividend_detail s ON DATE_FORMAT(s.create_time, '%Y-%m-%d') = date_list.day
            AND s.partner_id = #{merchantId}
            AND s.status = '1'
        GROUP BY date_list.day
        ORDER BY date_list.day DESC
    </select>

    <select id="getIncome" resultType="java.math.BigDecimal">
        SELECT
            COALESCE(SUM(dd.settlement_amount), 0) as income
        FROM ss_dividend_detail dd
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectIncome" resultType="com.ruoyi.system.domain.vo.InCome">
        SELECT
            DATE_FORMAT(dd.create_time, '%Y-%m-%d') as day,
            COALESCE(SUM(dd.settlement_amount), 0) as orderIncome,
            COALESCE(COUNT(DISTINCT dd.order_no), 0) as orderNum
        FROM ss_dividend_detail dd
        <where>
            <include refid="searchCondition"/>
        </where>
        GROUP BY day
        ORDER BY day ASC
    </select>

    <insert id="insertRlDividendDetail" parameterType="DividendDetail" useGeneratedKeys="true" keyProperty="id">
        insert into ss_dividend_detail
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="partnerId != null">partner_id,</if>
            <if test="partnerName != null">partner_name,</if>
            <if test="partnerPhone != null">partner_phone,</if>
            <if test="partnerType != null">partner_type,</if>
            <if test="orderNo != null and orderNo != ''">order_no,</if>
            <if test="totalAmount != null">total_amount,</if>
            <if test="dividendAmount != null">dividend_amount,</if>
            <if test="settlementAmount != null">settlement_amount,</if>
            <if test="dividendProportion != null">dividend_proportion,</if>
            <if test="createTime != null">create_time,</if>
            <if test="status != null">status,</if>
            <if test="payChannel != null">pay_channel,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="partnerId != null">#{partnerId},</if>
            <if test="partnerName != null">#{partnerName},</if>
            <if test="partnerPhone != null">#{partnerPhone},</if>
            <if test="partnerType != null">#{partnerType},</if>
            <if test="orderNo != null and orderNo != ''">#{orderNo},</if>
            <if test="totalAmount != null">#{totalAmount},</if>
            <if test="dividendAmount != null">#{dividendAmount},</if>
            <if test="settlementAmount != null">#{settlementAmount},</if>
            <if test="dividendProportion != null">#{dividendProportion},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="status != null">#{status},</if>
            <if test="payChannel != null">#{payChannel},</if>
         </trim>
    </insert>

    <update id="updateRlDividendDetail" parameterType="DividendDetail">
        update ss_dividend_detail
        <trim prefix="SET" suffixOverrides=",">
            <if test="partnerId != null">partner_id = #{partnerId},</if>
            <if test="partnerName != null">partner_name = #{partnerName},</if>
            <if test="partnerPhone != null">partner_phone = #{partnerPhone},</if>
            <if test="partnerType != null">partner_type = #{partnerType},</if>
            <if test="orderNo != null and orderNo != ''">order_no = #{orderNo},</if>
            <if test="totalAmount != null">total_amount = #{totalAmount},</if>
            <if test="dividendAmount != null">dividend_amount = #{dividendAmount},</if>
            <if test="settlementAmount != null">settlement_amount = #{settlementAmount},</if>
            <if test="dividendProportion != null">dividend_proportion = #{dividendProportion},</if>
            <if test="refundAmount != null">refund_amount = #{refundAmount},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="status != null">status = #{status},</if>
            <if test="payChannel != null">pay_channel = #{payChannel},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteRlDividendDetailById" parameterType="Long">
        delete from ss_dividend_detail where id = #{id}
    </delete>

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