<?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.withdrawalrecord.mapper.WithdrawalRecordMapper">

    <resultMap type="com.ruoyi.frequency.withdrawalrecord.entity.WithdrawalRecord" id="WithdrawalRecordResult">
        <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="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="bank"    column="bank"    />
        <result property="subbranch"    column="subbranch"    />
        <result property="bankCard"    column="bank_card"    />
        <result property="processTime"    column="process_time"    />
        <result property="name"    column="name"    />
    </resultMap>


    <sql id="selectWithdrawalRecordVo">
        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.violation_deduction,
            t.wi_id,
            t.bank,
            t.subbranch,
            t.bank_card,
            t.full_name,
            case when h.id is not null then h.name else a.name end as name,
            t.process_time
        from t_withdrawal_record AS t
                 LEFT JOIN t_store AS s ON s.id = t.user_id and t.user_type = 2
                 LEFT JOIN t_sale AS sa ON sa.id = t.user_id and t.user_type = 3
                 LEFT JOIN t_highend_auth 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 a ON t.user_id = a.store_id and a.del_flag = 1 and t.user_type = 2
    </sql>

    <select id="selectWithdrawalRecordList" parameterType="com.ruoyi.frequency.withdrawalrecord.entity.WithdrawalRecord" resultMap="WithdrawalRecordResult">
        <include refid="selectWithdrawalRecordVo"/>
        <where> AND t.del_flag != 2
            <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="delFlag != null and delFlag != ''"> and t.del_flag = #{delFlag}</if>
            <if test="bankCard != null  and bankCard != ''"> and t.bank_card like concat('%', #{bankCard}, '%')</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>
            <if test="userInfo != null and userInfo.nickname != null  and userInfo.nickname != '' ">
                and (
                s.nickname like concat('%', #{userInfo.nickname}, '%')
                or
                sa.nickname like concat('%', #{userInfo.nickname}, '%')
                )

            </if>
            <if test="userInfo != null and  userInfo.mobile != null and  userInfo.mobile != '' ">
                and (
                s.mobile like concat('%', #{userInfo.mobile}, '%')
                or
                sa.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>
        </where>
        order by t.create_time desc
    </select>

    <select id="statistics" parameterType="com.ruoyi.frequency.withdrawalrecord.entity.WithdrawalRecord" resultType="com.ruoyi.frequency.withdrawalrecord.model.result.WithdrawalRecordCount">
        select
        count(1)as total,
        IFNULL(sum(t.price + t.service_charge + t.violation_deduction),0)as amount,
        IFNULL(sum(t.service_charge),0)as serviceCharge,
        IFNULL(sum(t.violation_deduction),0)as violationDeduction,
        IFNULL(sum(t.price),0)as price
        from t_withdrawal_record AS t
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="selectWithdrawalRecordById" parameterType="Long" resultMap="WithdrawalRecordResult">
        <include refid="selectWithdrawalRecordVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertWithdrawalRecord" parameterType="com.ruoyi.frequency.withdrawalrecord.entity.WithdrawalRecord">
        insert into t_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>
        </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>
        </trim>
    </insert>

    <update id="updateWithdrawalRecord" parameterType="com.ruoyi.frequency.withdrawalrecord.entity.WithdrawalRecord">
        update t_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>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteWithdrawalRecordById" parameterType="Long">
        delete from t_withdrawal_record where id = #{id}
    </delete>

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

    <select id="withdrawalRecordTimePage" resultType="com.ruoyi.controller.resp.WithdrawalRecordPageTimeResp">
        SELECT
        DATE_FORMAT( t.create_time, '%Y-%m' ) time,
        IFNULL( sum(t2.price), 0 ) totalAmount
        FROM
        t_withdrawal_record t
        LEFT JOIN t_withdrawal_record t2 ON t.id = t2.id
        AND t2.`status` = 2
        WHERE t.user_id = #{userVo.userId}
        AND t.user_type = #{userVo.userType}
        AND t.del_flag = 1
        <if test="req.beginTime != null ">
            and  t.create_time >= #{req.beginTime}

        </if>
        <if test="req.endTime != null ">
            and  t.create_time &lt; #{req.endTime}
        </if>
        GROUP BY
        DATE_FORMAT( t.create_time, '%Y-%m' )
        ORDER BY
        DATE_FORMAT( t.create_time, '%Y-%M' ) DESC
    </select>

    <select id="getDetail" resultType="com.ruoyi.controller.resp.WithdrawalRecordPageResp">
        SELECT
            t.id,
            t.create_time ,
            t.price amount,
            t.total_amount totalAmount,
            t.`status`,
            t.refuse_reason,
            t.transfer_voucher,
            t.remarks,
            t.service_charge,
            t.violation_deduction,
            t.bank,
            t.subbranch,
            t.bank_card,
            t.full_name,
            t.process_time,
            t.create_time applyTime
        FROM t_withdrawal_record t
        WHERE t.id = #{id}
    </select>

    <select id="withdrawnAmount" resultType="java.math.BigDecimal">
        SELECT
            IFNULL((
                       SELECT
                           sum( t.price + t.service_charge )
                       FROM
                           t_withdrawal_record t
                       WHERE
                           t.del_flag = 1
                         AND t.user_type = #{userVo.userType}
                         AND t.user_id = #{userVo.userId}
                         AND t.STATUS = 5
                   ),
                   0)
    </select>
</mapper>
