<?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.frequency.bondwithdrawalrecord.mapper.BondWithdrawalRecordMapper">

    <resultMap type="com.ruoyi.frequency.bondwithdrawalrecord.entity.BondWithdrawalRecord" id="BondWithdrawalRecordResult">
        <result property="id"    column="id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="userType"    column="user_type"    />
        <result property="userId"    column="user_id"    />
        <result property="price"    column="price"    />
        <result property="serviceCharge"    column="service_charge"    />
        <result property="remarks"    column="remarks"    />
        <result property="status"    column="status"    />
        <result property="transferVoucher"    column="transfer_voucher"    />
        <result property="refuseReason"    column="refuse_reason"    />
        <result property="wiId"    column="wi_id"    />
        <result property="violationDeduction"    column="violation_deduction"    />
        <result property="bank"    column="bank"    />
        <result property="bankCard"    column="bank_card"    />
        <result property="subbranch"    column="subbranch"    />
        <result property="processTime"    column="process_time"    />
        <result property="name"    column="name"    />
    </resultMap>

    <sql id="selectBondWithdrawalRecordVo">
        SELECT
            t.id,
            t.create_by,
            t.create_time,
            t.update_by,
            t.update_time,
            t.del_flag,
            t.user_type,
            t.user_id,
            t.price,
            t.service_charge,
            t.remarks,
            t.`status`,
            t.transfer_voucher,
            t.refuse_reason,
            t.wi_id,
            t.violation_deduction,
            t.bank,
            t.subbranch,
            t.bank_card,
            t.process_time,
            case when h.id is not null then h.name else a.name end as name
        FROM
            t_bond_withdrawal_record AS t
                LEFT JOIN t_store AS s ON s.id = t.user_id and t.user_type = 2
                LEFT JOIN t_highend_auth AS h ON t.user_id = h.store_id
                AND t.user_type = 2
                AND h.`status` >= 3
                AND h.del_flag = 1
                LEFT JOIN t_auth AS a ON t.user_id = a.store_id
                AND a.del_flag = 1
                AND t.user_type = 2
    </sql>

    <select id="selectBondWithdrawalRecordList" parameterType="com.ruoyi.frequency.bondwithdrawalrecord.entity.BondWithdrawalRecord" resultMap="BondWithdrawalRecordResult">
        <include refid="selectBondWithdrawalRecordVo"/>
        <where>  and t.del_flag != 2
            <if test="userType != null "> and t.user_type = #{userType}</if>
            <if test="userId != null "> and t.user_id = #{userId}</if>
            <if test="price != null "> and t.price = #{price}</if>
            <if test="serviceCharge != null "> and t.service_charge = #{serviceCharge}</if>
            <if test="remarks != null  and remarks != ''"> and t.remarks = #{remarks}</if>
            <if test="status != null "> and t.status = #{status}</if>
            <if test="transferVoucher != null  and transferVoucher != ''"> and t.transfer_voucher = #{transferVoucher}</if>
            <if test="refuseReason != null  and refuseReason != ''"> and t.refuse_reason = #{refuseReason}</if>
            <if test="wiId != null "> and t.wi_id = #{wiId}</if>
            <if test="violationDeduction != null "> and t.violation_deduction = #{violationDeduction}</if>
            <if test="userInfo != null and userInfo.nickname != null  and userInfo.nickname != '' ">
                and
                s.nickname like concat('%', #{userInfo.nickname}, '%')

            </if>
            <if test="userInfo != null and  userInfo.mobile != null and  userInfo.mobile != '' ">
                and
                s.mobile like concat('%', #{userInfo.mobile}, '%')
            </if>
            <if test="beginTime != null ">
                and  t.create_time >= #{beginTime}

            </if>
            <if test="endTime != null ">
                and  t.create_time &lt; #{endTime}
            </if>

            <if test="processBeginTime != null ">
                and  t.process_time >= #{processBeginTime}

            </if>
            <if test="processEndTime != null ">
                and  t.process_time &lt;= #{processEndTime}
            </if>

            <if test="name != null  and name != ''">
                and (
                (h.id is not null and h.name like concat('%', #{name}, '%'))
                or
                (h.id is null and a.name like concat('%', #{name}, '%'))
                )
            </if>
        </where>
       order by t.create_time desc
    </select>

    <select id="statistics" parameterType="com.ruoyi.frequency.bondwithdrawalrecord.entity.BondWithdrawalRecord" resultType="com.ruoyi.frequency.bondwithdrawalrecord.model.result.BondWithdrawalRecordCount">
        SELECT
            count(1)AS total,
        IFNULL(sum(t.price + IFNULL(t.service_charge,0) + IFNULL(t.violation_deduction,0)),0)as amount
        FROM
        t_bond_withdrawal_record AS t
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="selectBondWithdrawalRecordById" parameterType="Long" resultMap="BondWithdrawalRecordResult">
        <include refid="selectBondWithdrawalRecordVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertBondWithdrawalRecord" parameterType="com.ruoyi.frequency.bondwithdrawalrecord.entity.BondWithdrawalRecord">
        insert into t_bond_withdrawal_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="userType != null">user_type,</if>
            <if test="userId != null">user_id,</if>
            <if test="price != null">price,</if>
            <if test="serviceCharge != null">service_charge,</if>
            <if test="remarks != null">remarks,</if>
            <if test="status != null">status,</if>
            <if test="transferVoucher != null">transfer_voucher,</if>
            <if test="refuseReason != null">refuse_reason,</if>
            <if test="wiId != null">wi_id,</if>
            <if test="violationDeduction != null">violation_deduction,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="userType != null">#{userType},</if>
            <if test="userId != null">#{userId},</if>
            <if test="price != null">#{price},</if>
            <if test="serviceCharge != null">#{serviceCharge},</if>
            <if test="remarks != null">#{remarks},</if>
            <if test="status != null">#{status},</if>
            <if test="transferVoucher != null">#{transferVoucher},</if>
            <if test="refuseReason != null">#{refuseReason},</if>
            <if test="wiId != null">#{wiId},</if>
            <if test="violationDeduction != null">#{violationDeduction},</if>
         </trim>
    </insert>

    <update id="updateBondWithdrawalRecord" parameterType="com.ruoyi.frequency.bondwithdrawalrecord.entity.BondWithdrawalRecord">
        update t_bond_withdrawal_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="userType != null">user_type = #{userType},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="price != null">price = #{price},</if>
            <if test="serviceCharge != null">service_charge = #{serviceCharge},</if>
            <if test="remarks != null">remarks = #{remarks},</if>
            <if test="status != null">status = #{status},</if>
            <if test="transferVoucher != null">transfer_voucher = #{transferVoucher},</if>
            <if test="refuseReason != null">refuse_reason = #{refuseReason},</if>
            <if test="wiId != null">wi_id = #{wiId},</if>
            <if test="violationDeduction != null">violation_deduction = #{violationDeduction},</if>
        </trim>
        where id = #{id}
    </update>

    <select id="withdrawalRecordPage" resultType="com.ruoyi.controller.resp.WithdrawalRecordPageResp">
        SELECT t3.`name`,
               t.create_time ,
               t.price amount,
               t.id,
               t.`status`,
               t.refuse_reason,
               t.transfer_voucher,
               t.remarks,
               t.service_charge,
               t2.area_code,
               t2.cash_account,
               t2.account_opening_sub_branch,
               t2.linkman,
               t2.contact_way,
               t.violation_deduction,
               t.bank,
               t.bank_card,
               t.subbranch,
               t.full_name,
               t.process_time,
               t.create_time applyTime
        FROM t_bond_withdrawal_record t
                 LEFT JOIN t_user_withdrawal_way_wi t2 ON t.wi_id = t2.id
                 LEFT JOIN t_pf_withdrawal_way t3 ON t2.pf_way_id = t3.id
        WHERE t.user_id = #{userVo.userId}
          AND t.user_type = #{userVo.userType}
          AND t.del_flag = 1
        ORDER BY t.create_time DESC
    </select>


    <select id="getDetail" resultType="com.ruoyi.controller.resp.WithdrawalRecordPageResp">
        SELECT
            t.id,
            t3.`name`,
            t.create_time ,
            t.price amount,
            t.`status`,
            t.refuse_reason,
            t.transfer_voucher,
            t.remarks,
            t.service_charge,
            t2.contact_way,
            t2.cash_account,
            t.violation_deduction,
            t.bank,
            t.bank_card,
            t.subbranch,
            t.full_name,
            t.process_time,
            t.create_time applyTime
        FROM t_bond_withdrawal_record t
                 LEFT JOIN t_user_withdrawal_way_wi t2 ON t.wi_id = t2.id
                 LEFT JOIN t_pf_withdrawal_way t3 ON t2.pf_way_id = t3.id
        WHERE t.id = #{id}
    </select>

</mapper>
