<?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.store.mapper.StoreMapper">

    <resultMap type="StoreVO" id="EStoreResult" autoMapping="true">
        <result property="tags" column="tags" typeHandler="com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler"/>
        <result property="typeTags" column="type_tags" typeHandler="com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler"/>
        <result property="pictures" column="picture" typeHandler="com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler"/>
    </resultMap>

    <sql id="selectEStoreVo">
        select
            s.store_id,
            s.code as qr_text,
            s.name,
            s.type,
            s.contact_name,
            s.contact_mobile,
            s.idcard,
            s.picture,
            s.address,
            s.lng,
            s.lat,
            s.create_time,
            s.create_by,
            s.update_time,
            s.update_by,
            s.deleted,
            s.business_time_start,
            s.business_time_end,
            s.province,
            s.city,
            s.county,
            s.server_phone,
            s.simple_address,
            s.merchant_id,
            m.user_name merchantName,
            s.status,
            s.code,
            s.gate_sn,
            s.gate_id,
            s.manager_id,
            s.manager_name,
            s.manager_phone,
            s.tags,
            s.type_tags,
            s.minimum_time,
            s.unlock_mode,
            s.unlock_condition,
            s.unlock_time,
            c.name as cityName
        from ss_store s
        left join ss_city c on c.city_id = s.city_id
        left join ss_user m on m.user_id = s.merchant_id
    </sql>

    <sql id="searchCondition">
        <if test="query.name != null  and query.name != ''"> and s.name like concat('%', #{query.name}, '%')</if>
        <if test="query.contactName != null  and query.contactName != ''"> and s.contact_name like concat('%', #{query.contactName}, '%')</if>
        <if test="query.type != null  and query.type != ''"> and FIND_IN_SET( #{query.type}, s.type_tags )</if>
        <if test="query.contactMobile != null  and query.contactMobile != ''"> and s.contact_mobile = #{query.contactMobile}</if>
        <if test="query.idcard != null  and query.idcard != ''"> and s.idcard = #{query.idcard}</if>
        <if test="query.storeId != null  and query.storeId != ''"> and s.store_id = #{query.storeId}</if>
        <if test="query.picture != null  and query.picture != ''"> and s.picture = #{query.picture}</if>
        <if test="query.lng != null "> and s.lng = #{query.lng}</if>
        <if test="query.lat != null "> and s.lat = #{query.lat}</if>
        <if test="query.merchantId != null "> and s.merchant_id = #{query.merchantId}</if>
        <if test="query.businessTimeStart != null "> and s.business_time_start = #{query.businessTimeStart}</if>
        <if test="query.businessTimeEnd != null "> and s.business_time_end = #{query.businessTimeEnd}</if>
        <if test="query.province != null  and query.province != ''"> and s.province = #{query.province}</if>
        <if test="query.cityId != null  and query.cityId != ''"> and s.city_id = #{query.cityId}</if>
        <if test="query.county != null  and query.county != ''"> and s.county = #{query.county}</if>
        <if test="query.serverPhone != null  and query.serverPhone != ''"> and s.server_phone = #{query.serverPhone}</if>
        <if test="query.simpleAddress != null  and query.simpleAddress != ''"> and s.simple_address = #{query.simpleAddress}</if>
        <if test="query.status != null  and query.status != ''"> and s.status = #{query.status}</if>
        <if test="query.code != null  and query.code != ''"> and s.code like concat('%', #{query.code}, '%')</if>
        <if test="query.gateSn != null  and query.gateSn != ''"> and s.gate_sn = #{query.gateSn}</if>
        <if test="query.gateId != null "> and s.gate_id = #{query.gateId}</if>
        <if test="query.managerName != null  and query.managerName != ''"> and s.manager_name like concat('%', #{query.managerName}, '%')</if>
        <if test="query.deleted == null">and s.deleted = false</if>
        <if test="query.deleted != null "> and s.deleted = #{query.deleted}</if>
    </sql>

    <select id="selectEStoreList" parameterType="StoreQuery" resultMap="EStoreResult">
        <include refid="selectEStoreVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectEStoreByStoreId" parameterType="Long" resultMap="EStoreResult">
        <include refid="selectEStoreVo"/>
        where s.store_id = #{storeId} and s.deleted = 0
    </select>

    <select id="selectEStoreByCityId" parameterType="Long" resultMap="EStoreResult">
        <include refid="selectEStoreVo"/>
        where s.city_id = #{cityId}  and s.deleted = 0
    </select>

    <select id="getStoreListByMerchantId" parameterType="Long" resultMap="EStoreResult">
        <include refid="selectEStoreVo"/>
        where s.merchant_id = #{merchantId} and s.deleted = 0
    </select>

    <select id="isExistTypeTags" resultType="java.lang.Boolean">
        select count(1) from ss_store where find_in_set(#{type}, type_tags) and store_id = #{storeId} and deleted = 0
    </select>

    <select id="selectCount" resultType="java.lang.Integer">
        select count(1)
        from ss_store
        where deleted = 0
        <if test="userId != null and userId != ''">
            and merchant_id = #{userId}
        </if>
    </select>

    <select id="selectStoreIdByMerchantId" resultType="java.lang.Long">
        select store_id from ss_store where merchant_id  = #{mchId} and deleted = 0 limit 1
    </select>

    <select id="getId" resultType="java.lang.Long">
        select store_id from ss_store where code = #{storeCode} and deleted = 0 limit 1
    </select>

    <select id="checkCodeIsExist" resultType="java.lang.Boolean">
        SELECT COUNT(1)
        FROM ss_store
        WHERE deleted = 0 AND code = #{code}
        <if test="storeId != null ">  AND store_id != #{storeId}</if>
    </select>

    <update id="updateStoreTypeTags">
        UPDATE ss_store
        SET type_tags = CONCAT( CASE WHEN type_tags IS NOT NULL AND type_tags != '' THEN CONCAT( type_tags, ',' ) END, #{type} )
        WHERE
        store_id = #{storeId}
    </update>

    <insert id="insertEStore" parameterType="Store" useGeneratedKeys="true" keyProperty="storeId">
        insert into ss_store
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">name,</if>
            <if test="type != null and type != ''">type,</if>
            <if test="contactName != null and contactName != ''">contact_name,</if>
            <if test="contactMobile != null and contactMobile != ''">contact_mobile,</if>
            <if test="idcard != null">idcard,</if>
            <if test="picture != null">picture,</if>
            <if test="address != null and address != ''">address,</if>
            <if test="lng != null">lng,</if>
            <if test="lat != null">lat,</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="businessTimeStart != null">business_time_start,</if>
            <if test="businessTimeEnd != null">business_time_end,</if>
            <if test="province != null and province != ''">province,</if>
            <if test="city != null and city != ''">city,</if>
            <if test="county != null and county != ''">county,</if>
            <if test="serverPhone != null">server_phone,</if>
            <if test="simpleAddress != null">simple_address,</if>
            <if test="merchantId != null">merchant_id,</if>
            <if test="status != null">status,</if>
            <if test="code != null">code,</if>
            <if test="gateSn != null">gate_sn,</if>
            <if test="gateId != null">gate_id,</if>
            <if test="managerId != null">manager_id,</if>
            <if test="managerName != null">manager_name,</if>
            <if test="cityId != null">city_id,</if>
            <if test="tags != null">tags,</if>
            <if test="typeTags != null">type_tags,</if>
            <if test="minimumTime != null">minimum_time,</if>
            <if test="unlockMode != null">unlock_mode,</if>
            <if test="unlockCondition != null">unlock_condition,</if>
            <if test="unlockTime != null">unlock_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">#{name},</if>
            <if test="type != null and type != ''">#{type},</if>
            <if test="contactName != null and contactName != ''">#{contactName},</if>
            <if test="contactMobile != null and contactMobile != ''">#{contactMobile},</if>
            <if test="idcard != null">#{idcard},</if>
            <if test="picture != null">#{picture},</if>
            <if test="address != null and address != ''">#{address},</if>
            <if test="lng != null">#{lng},</if>
            <if test="lat != null">#{lat},</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="businessTimeStart != null">#{businessTimeStart},</if>
            <if test="businessTimeEnd != null">#{businessTimeEnd},</if>
            <if test="province != null and province != ''">#{province},</if>
            <if test="city != null and city != ''">#{city},</if>
            <if test="county != null and county != ''">#{county},</if>
            <if test="serverPhone != null">#{serverPhone},</if>
            <if test="simpleAddress != null">#{simpleAddress},</if>
            <if test="merchantId != null">#{merchantId},</if>
            <if test="status != null">#{status},</if>
            <if test="code != null">#{code},</if>
            <if test="gateSn != null">#{gateSn},</if>
            <if test="gateId != null">#{gateId},</if>
            <if test="managerId != null">#{managerId},</if>
            <if test="managerName != null">#{managerName},</if>
            <if test="cityId != null">#{cityId},</if>
            <if test="tags != null">#{tags,typeHandler=com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler},</if>
            <if test="typeTags != null">#{typeTags,typeHandler=com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler},</if>
            <if test="minimumTime != null">#{minimumTime},</if>
            <if test="unlockMode != null">#{unlockMode},</if>
            <if test="unlockCondition != null">#{unlockCondition},</if>
            <if test="unlockTime != null">#{unlockTime},</if>
         </trim>
    </insert>

    <update id="updateEStore" parameterType="Store">
        update ss_store
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where store_id = #{data.storeId}
    </update>

    <sql id="updateColumns">
        <if test="data.name != null">name = #{data.name},</if>
        <if test="data.type != null and data.type != ''">type = #{data.type},</if>
        <if test="data.contactName != null and data.contactName != ''">contact_name = #{data.contactName},</if>
        <if test="data.contactMobile != null and data.contactMobile != ''">contact_mobile = #{data.contactMobile},</if>
        <if test="data.idcard != null">idcard = #{data.idcard},</if>
        <if test="data.picture != null">picture = #{data.picture},</if>
        <if test="data.address != null and data.address != ''">address = #{data.address},</if>
        <if test="data.lng != null">lng = #{data.lng},</if>
        <if test="data.lat != null">lat = #{data.lat},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.createBy != null">create_by = #{data.createBy},</if>
        <if test="data.updateTime != null">update_time = #{data.updateTime},</if>
        <if test="data.updateBy != null">update_by = #{data.updateBy},</if>
        <if test="data.deleted != null">deleted = #{data.deleted},</if>
        <if test="data.businessTimeStart != null">business_time_start = #{data.businessTimeStart},</if>
        <if test="data.businessTimeEnd != null">business_time_end = #{data.businessTimeEnd},</if>
        <if test="data.province != null and data.province != ''">province = #{data.province},</if>
        <if test="data.city != null and data.city != ''">city = #{data.city},</if>
        <if test="data.county != null and data.county != ''">county = #{data.county},</if>
        <if test="data.serverPhone != null">server_phone = #{data.serverPhone},</if>
        <if test="data.simpleAddress != null">simple_address = #{data.simpleAddress},</if>
        <if test="data.merchantId != null">merchant_id = #{data.merchantId},</if>
        <if test="data.status != null">status = #{data.status},</if>
        <if test="data.code != null">code = #{data.code},</if>
        <if test="data.gateSn != null">gate_sn = #{data.gateSn},</if>
        <if test="data.gateId != null">gate_id = #{data.gateId},</if>
        <if test="data.managerId != null">manager_id = #{data.managerId},</if>
        <if test="data.managerName != null">manager_name = #{data.managerName},</if>
        <if test="data.cityId != null">city_id = #{data.cityId},</if>
        <if test="data.tags != null">tags = #{data.tags,typeHandler=com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler},</if>
        <if test="data.typeTags != null">type_tags = #{data.typeTags,typeHandler=com.ruoyi.system.mapper.typehandler.StringSplitListTypeHandler},</if>
        <if test="data.minimumTime != null">minimum_time = #{data.minimumTime},</if>
        <if test="data.unlockMode != null">unlock_mode = #{data.unlockMode},</if>
        <if test="data.unlockCondition != null">unlock_condition = #{data.unlockCondition},</if>
        <if test="data.unlockTime != null">unlock_time = #{data.unlockTime},</if>
    </sql>


    <update id="deleteEStoreByStoreId" parameterType="Long">
        update ss_store
        set deleted = 1
        where store_id = #{roomId}
    </update>

    <update id="deleteEStoreByStoreIds" parameterType="List">
        update ss_store
        set deleted = 1
        where store_id in
        <foreach item="storeId" collection="array" open="(" separator="," close=")">
            #{storeId}
        </foreach>
    </update>

    <update id="offlineDeviceByDeviceId">
        update ss_store
        set gate_id = null,
            gate_sn = null
        where gate_id = #{deviceId}
    </update>

</mapper>
