<?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="selectVipVo">
        select
            sv.id,
            sv.user_id,
            sv.store_id,
            sv.level_id,
            sv.expire_time,
            sv.create_time,
            if(su.is_real, su.real_name, su.user_name) as user_name,
            ss.name as store_name,
            ss.user_id as store_mch_id,
            svl.name as vip_level_name,
            svl.mch_id as vip_mch_id
        from ss_vip sv
            left join sm_user su on su.user_id = sv.user_id
            left join sm_store ss on ss.store_id = sv.store_id
            left join ss_vip_level svl on svl.id = sv.level_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.storeId != null "> and sv.store_id = #{query.storeId}</if>
        <if test="query.levelId != null "> and sv.level_id = #{query.levelId}</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.storeName != null and query.storeName != ''">and ss.name like concat('%',#{query.storeName},'%')</if>
        <if test="query.vipLevelName != null and query.vipLevelName != ''">and svl.name like concat('%',#{query.vipLevelName},'%')</if>
        <if test="query.excludeId != null">and sv.id != #{query.excludeId}</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>
    </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="storeId != null">store_id,</if>
            <if test="levelId != null">level_id,</if>
            <if test="expireTime != null">expire_time,</if>
            <if test="createTime != null">create_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="storeId != null">#{storeId},</if>
            <if test="levelId != null">#{levelId},</if>
            <if test="expireTime != null">#{expireTime},</if>
            <if test="createTime != null">#{createTime},</if>
         </trim>
    </insert>

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

    <sql id="updateColumns">
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.storeId != null">store_id = #{data.storeId},</if>
        <if test="data.levelId != null">level_id = #{data.levelId},</if>
        <if test="data.expireTime != null">expire_time = #{data.expireTime},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</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>
</mapper>
