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

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

    <sql id="selectWithdrawVo">
        select
            bw.id,
            bw.user_id,
            bw.account_id,
            bw.account_type,
            bw.account_no,
            bw.account_name,
            bw.account_mobile,
            bw.account_id_card,
            bw.bank_name,
            bw.bank_card_name,
            bw.bank_type,
            bw.create_time,
            bw.verify_user_id,
            bw.verify_time,
            bw.verify_remark,
            bw.service_type,
            bw.service_charge,
            bw.service_point,
            bw.amount,
            bw.arrival_amount,
            bw.status,
            bw.no,
            su.nick_name as user_name,
            suv.nick_name as verify_user_name
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        bst_withdraw bw
            left join sys_user su on su.user_id = bw.user_id
            left join sys_user suv on suv.user_id = bw.verify_user_id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and bw.id = #{query.id}</if>
        <if test="query.userId != null "> and bw.user_id = #{query.userId}</if>
        <if test="query.accountId != null "> and bw.account_id = #{query.accountId}</if>
        <if test="query.accountType != null  and query.accountType != ''"> and bw.account_type = #{query.accountType}</if>
        <if test="query.status != null  and query.status != ''"> and bw.status = #{query.status}</if>
        <if test="query.accountNo != null  and query.accountNo != ''"> and bw.account_no like concat('%', #{query.accountNo}, '%')</if>
        <if test="query.no != null  and query.no != ''"> and bw.no like concat('%', #{query.no}, '%')</if>
        <if test="query.accountName != null  and query.accountName != ''"> and bw.account_name like concat('%', #{query.accountName}, '%')</if>
        <if test="query.accountMobile != null  and query.accountMobile != ''"> and bw.account_mobile like concat('%', #{query.accountMobile}, '%')</if>
        <if test="query.accountIdCard != null  and query.accountIdCard != ''"> and bw.account_id_card like concat('%', #{query.accountIdCard}, '%')</if>
        <if test="query.bankName != null  and query.bankName != ''"> and bw.bank_name like concat('%', #{query.bankName}, '%')</if>
        <if test="query.bankCardName != null  and query.bankCardName != ''"> and bw.bank_card_name like concat('%', #{query.bankCardName}, '%')</if>
        <if test="query.bankType != null  and query.bankType != ''"> and bw.bank_type like concat('%', #{query.bankType}, '%')</if>
        <if test="query.verifyUserId != null "> and bw.verify_user_id = #{query.verifyUserId}</if>
        <if test="query.verifyRemark != null  and query.verifyRemark != ''"> and bw.verify_remark like concat('%', #{query.verifyRemark}, '%')</if>
        <if test="query.userName != null  and query.userName != ''"> and su.nick_name like concat('%', #{query.userName}, '%')</if>
        <if test="query.verifyUserName != null  and query.verifyUserName != ''"> and suv.nick_name like concat('%', #{query.verifyUserName}, '%')</if>
        <if test="query.serviceType != null  and query.serviceType != ''"> and bw.service_type = #{query.serviceType}</if>
        <if test="query.statusList != null and query.statusList.size() > 0">
            and bw.status in
            <foreach collection="query.statusList" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.createDateRange != null and query.createDateRange.size() >= 2">
            and date(bw.create_time) &gt;= #{query.createDateRange[0]}
            and date(bw.create_time) &lt;= #{query.createDateRange[1]}
        </if>
        ${@com.ruoyi.framework.util.DataScopeUtil@dataScope(
            null,
            "bw.user_id",
            null,
            null,
            query.scope
        )}
        ${query.params.dataScope}
    </sql>

    <select id="selectWithdrawList" parameterType="WithdrawQuery" resultMap="WithdrawResult">
        <include refid="selectWithdrawVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectWithdrawById" parameterType="Long" resultMap="WithdrawResult">
        <include refid="selectWithdrawVo"/>
        where bw.id = #{id}
    </select>

    <insert id="insertWithdraw" parameterType="Withdraw" useGeneratedKeys="true" keyProperty="id">
        insert into bst_withdraw
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="accountId != null">account_id,</if>
            <if test="accountType != null and accountType != ''">account_type,</if>
            <if test="accountNo != null and accountNo != ''">account_no,</if>
            <if test="accountName != null and accountName != ''">account_name,</if>
            <if test="accountMobile != null">account_mobile,</if>
            <if test="accountIdCard != null">account_id_card,</if>
            <if test="bankName != null">bank_name,</if>
            <if test="bankCardName != null">bank_card_name,</if>
            <if test="bankType != null">bank_type,</if>
            <if test="createTime != null">create_time,</if>
            <if test="verifyUserId != null">verify_user_id,</if>
            <if test="verifyTime != null">verify_time,</if>
            <if test="verifyRemark != null">verify_remark,</if>
            <if test="serviceType != null and serviceType != ''">service_type,</if>
            <if test="serviceCharge != null">service_charge,</if>
            <if test="servicePoint != null">service_point,</if>
            <if test="amount != null">amount,</if>
            <if test="arrivalAmount != null">arrival_amount,</if>
            <if test="status != null">status,</if>
            <if test="no != null">no,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="accountId != null">#{accountId},</if>
            <if test="accountType != null and accountType != ''">#{accountType},</if>
            <if test="accountNo != null and accountNo != ''">#{accountNo},</if>
            <if test="accountName != null and accountName != ''">#{accountName},</if>
            <if test="accountMobile != null">#{accountMobile},</if>
            <if test="accountIdCard != null">#{accountIdCard},</if>
            <if test="bankName != null">#{bankName},</if>
            <if test="bankCardName != null">#{bankCardName},</if>
            <if test="bankType != null">#{bankType},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="verifyUserId != null">#{verifyUserId},</if>
            <if test="verifyTime != null">#{verifyTime},</if>
            <if test="verifyRemark != null">#{verifyRemark},</if>
            <if test="serviceType != null and serviceType != ''">#{serviceType},</if>
            <if test="serviceCharge != null">#{serviceCharge},</if>
            <if test="servicePoint != null">#{servicePoint},</if>
            <if test="amount != null">#{amount},</if>
            <if test="arrivalAmount != null">#{arrivalAmount},</if>
            <if test="status != null">#{status},</if>
            <if test="no != null">#{no},</if>
         </trim>
    </insert>

    <update id="updateWithdraw" parameterType="Withdraw">
        update bst_withdraw
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <update id="updateByQuery">
        update bst_withdraw bw
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        <where>
            <include refid="searchCondition"/>
        </where>
    </update>

    <sql id="updateColumns">
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.accountId != null">account_id = #{data.accountId},</if>
        <if test="data.accountType != null and data.accountType != ''">account_type = #{data.accountType},</if>
        <if test="data.accountNo != null and data.accountNo != ''">account_no = #{data.accountNo},</if>
        <if test="data.accountName != null and data.accountName != ''">account_name = #{data.accountName},</if>
        <if test="data.accountMobile != null">account_mobile = #{data.accountMobile},</if>
        <if test="data.accountIdCard != null">account_id_card = #{data.accountIdCard},</if>
        <if test="data.bankName != null">bank_name = #{data.bankName},</if>
        <if test="data.bankCardName != null">bank_card_name = #{data.bankCardName},</if>
        <if test="data.bankType != null">bank_type = #{data.bankType},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.verifyUserId != null">verify_user_id = #{data.verifyUserId},</if>
        <if test="data.verifyTime != null">verify_time = #{data.verifyTime},</if>
        <if test="data.verifyRemark != null">verify_remark = #{data.verifyRemark},</if>
        <if test="data.serviceType != null and data.serviceType != ''">service_type = #{data.serviceType},</if>
        <if test="data.serviceCharge != null">service_charge = #{data.serviceCharge},</if>
        <if test="data.servicePoint != null">service_point = #{data.servicePoint},</if>
        <if test="data.amount != null">amount = #{data.amount},</if>
        <if test="data.arrivalAmount != null">arrival_amount = #{data.arrivalAmount},</if>
        <if test="data.status != null">status = #{data.status},</if>
        <if test="data.no != null">no = #{data.no},</if>
    </sql>

    <delete id="deleteWithdrawById" parameterType="Long">
        delete from bst_withdraw where id = #{id}
    </delete>

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

    <!-- selectSumOfAmount --> 

    <select id="selectSumOfAmount" parameterType="WithdrawQuery" resultType="BigDecimal">
        select sum(bw.amount)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- selectCount --> 

    <select id="selectCount" parameterType="WithdrawQuery" resultType="int">
        select count(bw.id)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>    
</mapper>
