<?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.suit.mapper.SuitMapper">

    <resultMap type="SuitVO" id="SuitResult" autoMapping="true">
        <result property="gearAmount" column="gear_amount" typeHandler="com.ruoyi.system.mapper.typehandler.DecimalSplitListTypeHandler"/>
        <result property="gearTime" column="gear_time" typeHandler="com.ruoyi.system.mapper.typehandler.IntegerSplitListTypeHandler"/>
        <result property="price" column="price" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="deposit" column="deposit" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>

    <sql id="selectSuitVo">
        select
            ss.suit_id,
            ss.`name`,
            ss.`value`,
            ss.price,
            ss.deposit,
            ss.`description`,
            ss.create_time,
            ss.create_by,
            ss.update_time,
            ss.update_by,
            ss.deleted,
            ss.time_unit,
            ss.user_id,
            ss.sort,
            ss.fee_mode,
            ss.fee_type,
            ss.gear_amount,
            ss.gear_time,
            ss.enabled_low_power_close,
            ss.low_power,
            ss.enabled_voice,
            ss.voice_minutes,
            if(su.phonenumber is not null, su.phonenumber, su.user_name) as mobile_or_user_name,
            if(su.is_real, su.real_name, su.user_name) as user_name
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        sm_suit ss
            left join sm_user su on su.user_id = ss.user_id
    </sql>

    <sql id="searchCondition">
        <if test="suitId != null "> and ss.suit_id = #{suitId}</if>
        <if test="name != null  and name != ''"> and ss.name like concat('%', #{name}, '%')</if>
        <if test="mobileOrUserName != null  and mobileOrUserName != ''">
            and if(su.phonenumber is not null, su.phonenumber, su.user_name) like concat('%', #{mobileOrUserName}, '%')
        </if>
        <if test="value != null "> and ss.`value` = #{value}</if>
        <if test="price != null "> and ss.price = #{price}</if>
        <if test="description != null  and description != ''"> and ss.description = #{description}</if>
        <if test="deleted == null"> and ss.deleted = false</if>
        <if test="deleted != null"> and ss.deleted = #{deleted}</if>
        <if test="userId != null"> and ss.user_id = #{userId}</if>
        <if test="feeMode != null  and feeMode != ''"> and ss.fee_mode = #{feeMode}</if>
        <if test="feeType != null  and feeType != ''"> and ss.fee_type = #{feeType}</if>
        <if test="enabledLowPowerClose != null "> and ss.enabled_low_power_close = #{enabledLowPowerClose}</if>
        <if test="enabledVoice != null "> and ss.enabled_voice = #{enabledVoice}</if>
        <if test="userName != null and userName != ''">
            and if(su.is_real, su.real_name, su.user_name) like concat('%', #{userName}, '%')
        </if>
        <if test="deviceId != null">
            and ss.suit_id in (
                select distinct sds.suit_id from ss_device_suit sds where sds.device_id = #{deviceId}
            )
        </if>
        <if test="deviceName != null and deviceName != ''">
            and ss.suit_id in (
                select distinct sds.suit_id
                from ss_device_suit sds
                    left join sm_device sd on sd.device_id = sds.device_id
                where sd.device_name like concat('%', #{deviceName}, '%')
            )
        </if>
        <if test="suitIds != null and suitIds.size() > 0">
            and ss.suit_id in
            <foreach item="item" collection="suitIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </sql>

    <select id="selectSuitList" parameterType="SuitQuery" resultMap="SuitResult">
        <include refid="selectSuitVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        order by ss.sort asc, ss.create_time desc
    </select>

    <select id="selectSuitBySuitId" parameterType="Long" resultMap="SuitResult">
        <include refid="selectSuitVo"/>
        where ss.suit_id = #{suitId} and ss.deleted = false
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select count(ss.suit_id) as `count`
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectOne" resultMap="SuitResult">
        <include refid="selectSuitVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        limit 1
    </select>

    <insert id="insertSuit" parameterType="Suit">
        <selectKey resultType="java.lang.Long" keyProperty="suitId" order="AFTER">
            SELECT LAST_INSERT_ID();
        </selectKey>
        insert into sm_suit
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">`name`,</if>
            <if test="value != null">`value`,</if>
            <if test="price != null">price,</if>
            <if test="deposit != null">deposit,</if>
            <if test="description != null">`description`,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="deleted != null">deleted,</if>
            <if test="timeUnit != null">time_unit,</if>
            <if test="userId != null">user_id,</if>
            <if test="sort != null">sort,</if>
            <if test="feeMode != null and feeMode != ''">fee_mode,</if>
            <if test="feeType != null and feeType != ''">fee_type,</if>
            <if test="gearAmount != null">gear_amount,</if>
            <if test="gearTime != null">gear_time,</if>
            <if test="enabledLowPowerClose != null">enabled_low_power_close,</if>
            <if test="lowPower != null">low_power,</if>
            <if test="enabledVoice != null">enabled_voice,</if>
            <if test="voiceMinutes != null">voice_minutes,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null and name != ''">#{name},</if>
            <if test="value != null">#{value},</if>
            <if test="price != null">#{price},</if>
            <if test="deposit != null">#{deposit},</if>
            <if test="description != null">#{description},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="deleted != null">#{deleted},</if>
            <if test="timeUnit != null">#{timeUnit},</if>
            <if test="userId != null">#{userId},</if>
            <if test="sort != null">#{sort},</if>
            <if test="feeMode != null and feeMode != ''">#{feeMode},</if>
            <if test="feeType != null and feeType != ''">#{feeType},</if>
            <if test="gearAmount != null">#{gearAmount,typeHandler=com.ruoyi.system.mapper.typehandler.DecimalSplitListTypeHandler},</if>
            <if test="gearTime != null">#{gearTime,typeHandler=com.ruoyi.system.mapper.typehandler.IntegerSplitListTypeHandler},</if>
            <if test="enabledLowPowerClose != null">#{enabledLowPowerClose},</if>
            <if test="lowPower != null">#{lowPower},</if>
            <if test="enabledVoice != null">#{enabledVoice},</if>
            <if test="voiceMinutes != null">#{voiceMinutes},</if>
         </trim>
    </insert>

    <update id="updateSuit" parameterType="Suit">
        update sm_suit
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where suit_id = #{suitId}
    </update>

    <sql id="updateColumns">
        <if test="name != null and name != ''">`name` = #{name},</if>
        <if test="value != null">`value` = #{value},</if>
        <if test="price != null">price = #{price},</if>
        <if test="deposit != null">deposit = #{deposit},</if>
        <if test="description != null">`description` = #{description},</if>
        <if test="updateTime != null">update_time = #{updateTime},</if>
        <if test="updateBy != null">update_by = #{updateBy},</if>
        <if test="timeUnit != null">time_unit = #{timeUnit},</if>
        <if test="userId != null">user_id = #{userId},</if>
        <if test="sort != null">sort = #{sort},</if>
        <if test="feeMode != null and feeMode != ''">fee_mode = #{feeMode},</if>
        <if test="feeType != null and feeType != ''">fee_type = #{feeType},</if>
        <if test="gearAmount != null">gear_amount = #{gearAmount,typeHandler=com.ruoyi.system.mapper.typehandler.DecimalSplitListTypeHandler},</if>
        <if test="gearTime != null">gear_time = #{gearTime,typeHandler=com.ruoyi.system.mapper.typehandler.IntegerSplitListTypeHandler},</if>
        <if test="enabledLowPowerClose != null">enabled_low_power_close = #{enabledLowPowerClose},</if>
        <if test="lowPower != null">low_power = #{lowPower},</if>
        <if test="enabledVoice != null">enabled_voice = #{enabledVoice},</if>
        <if test="voiceMinutes != null">voice_minutes = #{voiceMinutes},</if>
    </sql>

    <update id="logicDel">
        update sm_suit
        set deleted = true
        where suit_id in
        <foreach item="suitId" collection="suitIds" open="(" separator="," close=")">
            #{suitId}
        </foreach>
    </update>

    <update id="batchUpdateSort">
        update sm_suit
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="sort = CASE suit_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.sort != null">
                        WHEN #{item.suitId} THEN #{item.sort}
                    </when>
                    <otherwise>
                        WHEN #{item.suitId} THEN `sort`
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        where suit_id in
        <foreach collection="list" open="(" close=")" item="item" separator=",">
            #{item.suitId}
        </foreach>
    </update>

    <delete id="deleteSuitBySuitId" parameterType="Long">
        delete from sm_suit where suit_id = #{suitId}
    </delete>

    <delete id="deleteSuitBySuitIds" parameterType="String">
        delete from sm_suit where suit_id in
        <foreach item="suitId" collection="array" open="(" separator="," close=")">
            #{suitId}
        </foreach>
    </delete>
</mapper>
