<?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="SmStoreResult" autoMapping="true"/>

    <sql id="selectSmStoreVo">
        select
            ss.store_id,
            ss.name,
            ss.user_id,
            ss.group_sort,
            ss.is_default,
            ss.address,
            ss.lng,
            ss.lat,
            ss.create_time,
            ss.create_by,
            ss.update_time,
            ss.update_by,
            ss.deleted,
            ss.picture,
            ss.business_time_start,
            ss.business_time_end,
            ss.province,
            ss.city,
            ss.county,
            ss.type,
            ss.contact_name,
            ss.contact_mobile,
            ss.show,
            ss.status,
            ss.enabled,
            ss.use_out_time,
            if(su.is_real, su.real_name, su.user_name) as user_name
        from sm_store ss
            left join sm_user su on su.user_id = ss.user_id
    </sql>

    <sql id="searchCondition">
        <if test="query.name != null  and query.name != ''"> and ss.name like concat('%', #{query.name}, '%')</if>
        <if test="query.userId != null "> and ss.user_id = #{query.userId}</if>
        <if test="query.show != null "> and ss.show = #{query.show}</if>
        <if test="query.storeId != null "> and ss.store_id = #{query.storeId}</if>
        <if test="query.isDefault != null "> and ss.is_default = #{query.isDefault}</if>
        <if test="query.type != null "> and ss.type = #{query.type}</if>
        <if test="query.address != null  and query.address != ''"> and ss.address like concat('%', #{query.address}, '%')</if>
        <if test="query.deleted != null "> and ss.deleted = #{query.deleted}</if>
        <if test="query.deleted == null "> and ss.deleted = false</if>
        <if test="query.userName != null "> and if(su.is_real, su.real_name, su.user_name) like concat('%', #{query.userName}, '%')</if>
        <if test="query.status != null "> and ss.status = #{query.status}</if>
        <if test="query.enabled != null "> and ss.enabled = #{query.enabled}</if>
        <if test="query.useOutTime != null "> and ss.use_out_time = #{query.useOutTime}</if>
        <if test="query.keyword != null and query.keyword != ''">
            and (
                ss.name like concat('%', #{query.keyword}, '%')
                or ss.contact_name like concat('%', #{query.keyword}, '%')
                or ss.contact_mobile like concat('%', #{query.keyword}, '%')
                or ss.address like concat('%', #{query.keyword}, '%')
            )
        </if>
        <if test="query.center != null and query.center.size() == 2 and query.radius != null">
            and #{query.radius} >= round(st_distance_sphere(point(#{query.center[0]}, #{query.center[1]}), point(ss.lng, ss.lat)))
        </if>
        <if test="query.storeIds != null and query.storeIds.size() > 0">
            and ss.store_id in
            <foreach collection="query.storeIds" close=")" item="item" open="(" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="query.userIds != null and query.userIds.size() > 0">
            and ss.user_id in
            <foreach collection="query.userIds" close=")" item="item" open="(" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="query.inStaff != null">
            and ss.store_id
            <if test="!query.inStaff">not</if>
            in (<include refid="selectStaffViewStoreIds"/>)
        </if>
    </sql>

    <!--查询员工可查看的店铺ID列表-->
    <sql id="selectStaffViewStoreIds">
        select distinct sss.store_id
        from ss_store_staff sss
        <where>
            <if test="query.staffUserId != null">and sss.user_id = #{query.staffUserId}</if>
            <if test="query.staffPermission != null and query.staffPermission != ''">
                and find_in_set(#{query.staffPermission}, sss.permissions)
            </if>
        </where>
    </sql>

    <select id="selectSmStoreList" parameterType="StoreQuery" resultMap="SmStoreResult">
        <include refid="selectSmStoreVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectSmStoreById" parameterType="Long" resultMap="SmStoreResult">
        <include refid="selectSmStoreVo"/>
        where ss.store_id = #{storeId} and ss.deleted = false
    </select>

    <select id="selectSmStoreByIds" resultMap="SmStoreResult">
        <include refid="selectSmStoreVo"/>
        where ss.store_id in
        <foreach collection="storeIds" close=")" item="item" open="(" separator=",">
            #{item}
        </foreach>
        and ss.deleted = false
    </select>

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

    <select id="selectOne" resultMap="SmStoreResult">
        <include refid="selectSmStoreVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        order by ss.group_sort asc, ss.create_time asc
        limit 1
    </select>

    <select id="selectCommonCount" resultType="com.ruoyi.ss.store.domain.StoreCountVO">
        <trim prefix="select" suffixOverrides=",">
            <if test="query.groupBy != null">
                ss.${query.groupBy},
            </if>
            count(ss.store_id) as `count`
        </trim>
        from sm_store ss
        <where>
            <include refid="searchCondition"/>
        </where>
        <if test="query.groupBy != null">
            group by ss.${query.groupBy}
        </if>
    </select>

    <insert id="insertSmStore" parameterType="Store" useGeneratedKeys="true" keyProperty="storeId">
        <selectKey keyProperty="storeId" resultType="Long" order="AFTER">
            SELECT LAST_INSERT_ID();
        </selectKey>
        insert into sm_store
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">name,</if>
            <if test="userId != null">user_id,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="groupSort != null">group_sort,</if>
            <if test="isDefault != null">is_default,</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="businessTimeStart != null">business_time_start,</if>
            <if test="businessTimeEnd != null">business_time_end,</if>
            <if test="province != null">province,</if>
            <if test="city != null">city,</if>
            <if test="county != null">county,</if>
            <if test="type != null">`type`,</if>
            <if test="contactName != null">`contact_name`,</if>
            <if test="contactMobile != null">`contact_mobile`,</if>
            <if test="show != null">`show`,</if>
            <if test="status != null">`status`,</if>
            <if test="enabled != null">`enabled`,</if>
            <if test="useOutTime != null">use_out_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">#{name},</if>
            <if test="userId != null">#{userId},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="groupSort != null">#{groupSort},</if>
            <if test="isDefault != null">#{isDefault},</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="businessTimeStart != null">#{businessTimeStart},</if>
            <if test="businessTimeEnd != null">#{businessTimeEnd},</if>
            <if test="province != null">#{province},</if>
            <if test="city != null">#{city},</if>
            <if test="county != null">#{county},</if>
            <if test="type != null">#{type},</if>
            <if test="contactName != null">#{contactName},</if>
            <if test="contactMobile != null">#{contactMobile},</if>
            <if test="show != null">#{show},</if>
            <if test="status != null">#{status},</if>
            <if test="enabled != null">#{enabled},</if>
            <if test="useOutTime != null">#{useOutTime},</if>
         </trim>
    </insert>

    <update id="updateSmStore" parameterType="Store">
        update sm_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.userId != null">user_id = #{data.userId},</if>
        <if test="data.groupSort != null">group_sort = #{data.groupSort},</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.businessTimeStart != null">business_time_start = #{data.businessTimeStart},</if>
        <if test="data.businessTimeEnd != null">business_time_end = #{data.businessTimeEnd},</if>
        <if test="data.province != null">province = #{data.province},</if>
        <if test="data.city != null">city = #{data.city},</if>
        <if test="data.county != null">county = #{data.county},</if>
        <if test="data.type != null">`type` = #{data.type},</if>
        <if test="data.updateTime != null">`update_time` = #{data.updateTime},</if>
        <if test="data.updateBy != null">`update_by` = #{data.updateBy},</if>
        <if test="data.contactName != null">`contact_name` = #{data.contactName},</if>
        <if test="data.contactMobile != null">`contact_mobile` = #{data.contactMobile},</if>
        <if test="data.show != null">`show` = #{data.show},</if>
        <if test="data.status != null">`status` = #{data.status},</if>
        <if test="data.enabled != null">`enabled` = #{data.enabled},</if>
        <if test="data.useOutTime != null">use_out_time = #{data.useOutTime},</if>
    </sql>

    <update id="updateByQuery">
        update sm_store ss
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        <where>
            <include refid="searchCondition"/>
        </where>
    </update>

    <update id="updateSort">
        update sm_store
        set group_sort = group_sort + #{offset}
        where group_sort between #{start} and #{end}
    </update>

    <update id="batchUpdate">
        UPDATE sm_store
        <trim prefix="SET" suffixOverrides=",">
            <foreach open="name = CASE store_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.name != null">
                        WHEN #{item.storeId} THEN #{item.name}
                    </when>
                    <otherwise>
                        WHEN #{item.storeId} THEN name
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="user_id = CASE store_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.userId != null">
                        WHEN #{item.storeId} THEN #{item.userId}
                    </when>
                    <otherwise>
                        WHEN #{item.storeId} THEN user_id
                    </otherwise>
                </choose>
            </foreach>
            <foreach open="group_sort = CASE store_id" collection="list" item="item" close="END,">
                <choose>
                    <when test="item.groupSort != null">
                        WHEN #{item.storeId} THEN #{item.groupSort}
                    </when>
                    <otherwise>
                        WHEN #{item.storeId} THEN group_sort
                    </otherwise>
                </choose>
            </foreach>
        </trim>
        WHERE store_id IN
        <foreach open="(" collection="list" item="item" close=")" separator=",">
            #{item.storeId}
        </foreach>
    </update>

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

    <update id="setDefault">
        update sm_store
        set is_default = if(store_id = #{storeId}, true, false)
        where user_id = #{userId}
    </update>

    <delete id="deleteSmStoreById" parameterType="Long">
        delete from sm_store where store_id = #{storeId}
    </delete>

    <delete id="deleteSmStoreByIds" parameterType="String">
        delete from sm_store where store_id in
        <foreach item="storeId" collection="array" open="(" separator="," close=")">
            #{storeId}
        </foreach>
    </delete>
</mapper>
