<?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.vip.mapper.VipMapper">

    <resultMap type="VipVO" id="VipResult" autoMapping="true"/>

    <!-- 是否在有效期内 -->
    <sql id="inValid">
        (sv.start_time &lt;= now() and sv.end_time &gt;= now())
    </sql>

    <!-- 是否被限制 -->
    <sql id="inLimit">
        (
            sv.limit_type != '1' and sv.round_count >= sv.limit_count
        )
    </sql>

    <sql id="selectVipVo">
        select
            sv.id,
            sv.user_id,
            sv.level_id,
            sv.create_time,
            sv.start_time,
            sv.end_time,
            sv.discount,
            sv.store_id,
            sv.next_reset_time,
            sv.total_count,
            sv.round_count,
            sv.limit_type,
            sv.limit_count,
            sv.surplus_count,
            sv.deleted,
            <include refid="inValid"/> as in_valid,
            <include refid="inLimit"/> as in_limit,
            if(su.is_real, su.real_name, su.user_name) as user_name,
            svl.name as vip_level_name,
            svl.mch_id as vip_mch_id,
            ss.user_id as store_mch_id,
            ss.name as store_name,
            if(mch.is_real, mch.real_name, mch.user_name) as mch_name
        from ss_vip sv
            left join sm_user su on su.user_id = sv.user_id
            left join ss_vip_level svl on svl.id = sv.level_id
            left join sm_store ss on ss.store_id = svl.store_id
            left join sm_user mch on mch.user_id = svl.mch_id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and sv.id = #{query.id}</if>
        <if test="query.userId != null "> and sv.user_id = #{query.userId}</if>
        <if test="query.levelId != null "> and sv.level_id = #{query.levelId}</if>
        <if test="query.startTime != null "> and sv.start_time = #{query.startTime}</if>
        <if test="query.endTime != null "> and sv.end_time = #{query.endTime}</if>
        <if test="query.discount != null "> and sv.discount = #{query.discount}</if>
        <if test="query.storeId != null"> and sv.store_id = #{query.storeId}</if>
        <if test="query.storeName != null and query.storeName != ''">and ss.name like concat('%',#{query.storeName},'%')</if>
        <if test="query.userName != null and query.userName != ''">and if(su.is_real, su.real_name, su.user_name) like concat('%',#{query.userName},'%')</if>
        <if test="query.mchName != null and query.mchName != ''">and if(mch.is_real, mch.real_name, mch.user_name) like concat('%',#{query.mchName},'%')</if>
        <if test="query.vipLevelName != null and query.vipLevelName != ''">and svl.name like concat('%',#{query.vipLevelName},'%')</if>
        <if test="query.limitType != null  and query.limitType != ''"> and sv.limit_type = #{query.limitType}</if>
        <if test="query.limitCount != null "> and sv.limit_count = #{query.limitCount}</if>
        <if test="query.excludeId != null">and sv.id != #{query.excludeId}</if>
        <if test="query.deleted != null">and sv.deleted = #{query.deleted}</if>
        <if test="query.deleted == null">and sv.deleted = false</if>
        <if test="query.discountStart != null">and sv.discount &gt;= #{query.discountStart}</if>
        <if test="query.discountEnd != null">and sv.discount &lt;= #{query.discountEnd}</if>
        <if test="query.surplusCountStart != null">and sv.surplus_count &gt;= #{query.surplusCountStart}</if>
        <if test="query.surplusCountEnd != null">and sv.surplus_count &lt;= #{query.surplusCountEnd}</if>
        <if test="query.inValid != null">
            and
            <if test="!query.inValid">
                not
            </if>
            <include refid="inValid"/>
        </if>
        <if test="query.inLimit != null">
            and
            <if test="!query.inLimit">
                not
            </if>
            <include refid="inLimit"/>
        </if>
        <if test="query.vipLevelIds != null and query.vipLevelIds.size() > 0">
            and sv.level_id in
            <foreach collection="query.vipLevelIds" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="query.ids != null and query.ids.size() > 0">
            and sv.id in
            <foreach collection="query.ids" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </sql>

    <select id="selectVipList" parameterType="VipQuery" resultMap="VipResult">
        <include refid="selectVipVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectVipById" parameterType="Long" resultMap="VipResult">
        <include refid="selectVipVo"/>
        where sv.id = #{id}
    </select>

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

    <insert id="insertVip" parameterType="Vip" useGeneratedKeys="true" keyProperty="id">
        insert into ss_vip
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="levelId != null">level_id,</if>
            <if test="createTime != null">create_time,</if>
            <if test="startTime != null">start_time,</if>
            <if test="endTime != null">end_time,</if>
            <if test="discount != null">discount,</if>
            <if test="storeId != null">store_id,</if>
            <if test="nextResetTime != null">next_reset_time,</if>
            <if test="totalCount != null">total_count,</if>
            <if test="roundCount != null">round_count,</if>
            <if test="limitType != null and limitType != ''">limit_type,</if>
            <if test="limitCount != null">limit_count,</if>
            <if test="surplusCount != null">surplus_count,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="levelId != null">#{levelId},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="startTime != null">#{startTime},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="discount != null">#{discount},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="nextResetTime != null">#{nextResetTime},</if>
            <if test="totalCount != null">#{totalCount},</if>
            <if test="roundCount != null">#{roundCount},</if>
            <if test="limitType != null and limitType != ''">#{limitType},</if>
            <if test="limitCount != null">#{limitCount},</if>
            <if test="surplusCount != null">#{surplusCount},</if>
         </trim>
    </insert>

    <update id="updateVip" parameterType="Vip">
        update ss_vip
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <update id="renewal">
        update ss_vip sv
        set sv.end_time = date_add(if(now() > sv.end_time, now(), sv.end_time), interval #{days} day),
            sv.surplus_count = if (now() &lt;= sv.end_time, sv.surplus_count + #{count}, #{count})
        where sv.id = #{id}
    </update>

    <sql id="updateColumns">
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.levelId != null">level_id = #{data.levelId},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.startTime != null">start_time = #{data.startTime},</if>
        <if test="data.endTime != null">end_time = #{data.endTime},</if>
        <if test="data.discount != null">discount = #{data.discount},</if>
        <if test="data.storeId != null">store_id = #{data.storeId},</if>
        <if test="data.nextResetTime != null">next_reset_time = #{data.nextResetTime},</if>
        <if test="data.totalCount != null">total_count = #{data.totalCount},</if>
        <if test="data.roundCount != null">round_count = #{data.roundCount},</if>
        <if test="data.limitType != null and data.limitType != ''">limit_type = #{data.limitType},</if>
        <if test="data.limitCount != null">limit_count = #{data.limitCount},</if>
        <if test="data.surplusCount != null">surplus_count = #{data.surplusCount},</if>
    </sql>

    <delete id="deleteVipById" parameterType="Long">
        delete from ss_vip where id = #{id}
    </delete>

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

    <!-- logicDel -->

    <update id="logicDel">
        update ss_vip
        set deleted = true
        where id in
            <foreach item="id" collection="ids" open="(" separator="," close=")">
                #{id}
            </foreach>
            and deleted = false
    </update>
</mapper>
