<?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.withdraw.mapper.WithdrawMapper">

    <resultMap type="WithdrawVO" id="RlWithdrawResult" autoMapping="true" />

    <sql id="selectRlWithdrawVo">
        select w.id,
               w.withdraw_no,
               w.amount,
               w.status,
               w.create_time,
               w.reject_reason,
               w.audit_time,
               w.owner_name,
               w.owner_id,
               w.owner_phone,
               w.owner_type,
               w.wx_openid,
               w.handling_charge,
               w.cost,
               w.withdraw_channel_id,
               w.handling_charge_type,
               w.withdraw_handling_charge,
               w.user_withdraw_channel_id,
               w.received_amount,
               w.type,
               w.is_auto,
               w.after_balance,
               w.audit_time
        from ss_withdraw w
        left join ss_user u on w.owner_id = u.user_id
    </sql>

    <sql id="searchCondition">
        <if test="withdrawNo != null  and withdrawNo != ''"> and w.withdraw_no like concat('%', #{withdrawNo}, '%')</if>
        <if test="amount != null "> and w.amount = #{amount}</if>
        <if test="status != null  and status != ''"> and w.status = #{status}</if>
        <if test="rejectReason != null  and rejectReason != ''"> and w.reject_reason like concat('%', #{rejectReason}, '%')</if>
        <if test="ownerName != null  and ownerName != ''"> and w.owner_name like concat('%', #{ownerName}, '%')</if>
        <if test="ownerId != null "> and w.owner_id = #{ownerId}</if>
        <if test="ownerPhone != null  and ownerPhone != ''"> and w.owner_phone like concat('%', #{ownerPhone}, '%')</if>
        <if test="ownerType != null "> and w.owner_type = #{ownerType}</if>
        <if test="wxOpenid != null  and wxOpenid != ''"> and w.wx_openid like concat('%', #{wxOpenid}, '%')</if>
        <if test="handlingCharge != null "> and w.handling_charge = #{handlingCharge}</if>
        <if test="cost != null "> and w.cost = #{cost}</if>
        <if test="withdrawChannelId != null "> and w.withdraw_channel_id = #{withdrawChannelId}</if>
        <if test="userWithdrawChannelId != null "> and w.user_withdraw_channel_id = #{userWithdrawChannelId}</if>
        <if test="handlingChargeType != null "> and w.handling_charge_type = #{handlingChargeType}</if>
        <if test="withdrawHandlingCharge != null "> and w.withdraw_handling_charge = #{withdrawHandlingCharge}</if>
        <if test="type != null "> and w.type = #{type}</if>
        <if test="isAuto != null "> and w.is_auto = #{isAuto}</if>
        <if test="startData != null "> and date(w.audit_time) = #{startData}</if>
        <if test="statusList != null and  statusList !=''">
            AND w.status IN
            <foreach item="item" index="index" collection="statusList" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </sql>

    <select id="selectRlWithdrawList" parameterType="Withdraw" resultMap="RlWithdrawResult">
        <include refid="selectRlWithdrawVo"/>
        where 1=1
        <include refid="searchCondition"/>
        ${params.dataScope}
        order by create_time
    </select>

    <select id="selectRlWithdrawById" parameterType="Long" resultMap="RlWithdrawResult">
        <include refid="selectRlWithdrawVo"/>
        where w.id = #{id}
    </select>

    <select id="selectRlWithdrawByWithdrawNo" parameterType="string" resultMap="RlWithdrawResult">
        <include refid="selectRlWithdrawVo"/>
        where w.withdraw_no = #{withdrawNo}
    </select>

    <select id="cumulativeWithdrawalAmount" resultType="java.math.BigDecimal" parameterType="Long">
        SELECT SUM(amount) AS total_withdraw
        FROM ss_withdraw
        where owner_id = #{ownerId} and status in ('0', '1', '3', '4')
    </select>

    <select id="selectWithdrawingAmountByUserId" resultType="java.math.BigDecimal">
        SELECT
            COALESCE(SUM(amount), 0) AS withdrawingAmount
        FROM
            ss_withdraw
        WHERE
            owner_id = #{userId} AND
            status IN ('0', '3');
    </select>

    <select id="selectWithdrawAmount" resultType="java.math.BigDecimal">
        SELECT
            COALESCE(SUM(amount), 0) AS withdrawingAmount
        FROM
            ss_withdraw w
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectHandlingCharge" resultType="java.math.BigDecimal">
        SELECT
        COALESCE(SUM(handling_charge), 0) AS withdrawingAmount
        FROM
        ss_withdraw w
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

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

    <insert id="insertRlWithdraw" parameterType="Withdraw" useGeneratedKeys="true" keyProperty="id">
        insert into ss_withdraw
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="withdrawNo != null">withdraw_no,</if>
            <if test="amount != null">amount,</if>
            <if test="status != null">status,</if>
            <if test="createTime != null">create_time,</if>
            <if test="rejectReason != null">reject_reason,</if>
            <if test="auditTime != null">audit_time,</if>
            <if test="ownerName != null">owner_name,</if>
            <if test="ownerId != null">owner_id,</if>
            <if test="ownerPhone != null">owner_phone,</if>
            <if test="ownerType != null">owner_type,</if>
            <if test="wxOpenid != null">wx_openid,</if>
            <if test="handlingCharge != null">handling_charge,</if>
            <if test="cost != null">cost,</if>
            <if test="withdrawChannelId != null">withdraw_channel_id,</if>
            <if test="userWithdrawChannelId != null">user_withdraw_channel_id,</if>
            <if test="handlingChargeType != null">handling_charge_type,</if>
            <if test="withdrawHandlingCharge != null">withdraw_handling_charge,</if>
            <if test="receivedAmount != null">received_amount,</if>
            <if test="type != null">type,</if>
            <if test="isAuto != null">is_auto,</if>
            <if test="afterBalance != null">after_balance</if>
            <if test="auditTime != null">,audit_time</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="withdrawNo != null">#{withdrawNo},</if>
            <if test="amount != null">#{amount},</if>
            <if test="status != null">#{status},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="rejectReason != null">#{rejectReason},</if>
            <if test="auditTime != null">#{auditTime},</if>
            <if test="ownerName != null">#{ownerName},</if>
            <if test="ownerId != null">#{ownerId},</if>
            <if test="ownerPhone != null">#{ownerPhone},</if>
            <if test="ownerType != null">#{ownerType},</if>
            <if test="wxOpenid != null">#{wxOpenid},</if>
            <if test="handlingCharge != null">#{handlingCharge},</if>
            <if test="cost != null">#{cost},</if>
            <if test="withdrawChannelId != null">#{withdrawChannelId},</if>
            <if test="userWithdrawChannelId != null">#{userWithdrawChannelId},</if>
            <if test="handlingChargeType != null">#{handlingChargeType},</if>
            <if test="withdrawHandlingCharge != null">#{withdrawHandlingCharge},</if>
            <if test="receivedAmount != null">#{receivedAmount},</if>
            <if test="type != null">#{type},</if>
            <if test="isAuto != null">#{isAuto},</if>
            <if test="afterBalance != null">#{afterBalance},</if>
            <if test="auditTime != null">#{auditTime}</if>
         </trim>
    </insert>

    <update id="updateRlWithdraw" parameterType="Withdraw">
        update ss_withdraw
        <trim prefix="SET" suffixOverrides=",">
            <if test="withdrawNo != null">withdraw_no = #{withdrawNo},</if>
            <if test="amount != null">amount = #{amount},</if>
            <if test="status != null">status = #{status},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="rejectReason != null">reject_reason = #{rejectReason},</if>
            <if test="auditTime != null">audit_time = #{auditTime},</if>
            <if test="ownerName != null">owner_name = #{ownerName},</if>
            <if test="ownerId != null">owner_id = #{ownerId},</if>
            <if test="ownerPhone != null">owner_phone = #{ownerPhone},</if>
            <if test="ownerType != null">owner_type = #{ownerType},</if>
            <if test="wxOpenid != null">wx_openid = #{wxOpenid},</if>
            <if test="handlingCharge != null">handling_charge = #{handlingCharge},</if>
            <if test="cost != null">cost = #{cost},</if>
            <if test="withdrawChannelId != null">withdraw_channel_id = #{withdrawChannelId},</if>
            <if test="userWithdrawChannelId != null">user_withdraw_channel_id = #{userWithdrawChannelId},</if>
            <if test="handlingChargeType != null">handling_charge_type = #{handlingChargeType},</if>
            <if test="withdrawHandlingCharge != null">withdraw_handling_charge = #{withdrawHandlingCharge},</if>
            <if test="receivedAmount != null">received_amount = #{receivedAmount},</if>
            <if test="type != null">type = #{type},</if>
            <if test="isAuto != null">is_auto = #{isAuto},</if>
            <if test="afterBalance != null">after_balance = #{afterBalance},</if>
            <if test="auditTime != null">audit_time = #{auditTime}</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteRlWithdrawById" parameterType="Long">
        delete from ss_withdraw where id = #{id}
    </delete>

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