<?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.mall.mapper.MallShopShopMapper">

    <resultMap type="MallShopShop" id="MallShopShopResult">
        <result property="shopId"    column="shop_id"    />
        <result property="sysUserId"    column="sys_user_id"    />
        <result property="shopName"    column="shop_name"    />
        <result property="shopPhone"    column="shop_phone"    />
        <result property="shopAccount"    column="shop_account"    />
        <result property="shopPassword"    column="shop_password"    />
        <result property="shopImage"    column="shop_image"    />
        <result property="shopBanner"    column="shop_banner"    />
        <result property="shopLicense"    column="shop_license"    />
        <result property="shopPermit"    column="shop_permit"    />
        <result property="shopBusinessBegin"    column="shop_business_begin"    />
        <result property="shopBusinessEnd"    column="shop_business_end"    />
        <result property="shopMoney"    column="shop_money"    />
        <result property="provinceId"    column="province_id"    />
        <result property="provinceName"    column="province_name"    />
        <result property="cityId"    column="city_id"    />
        <result property="cityName"    column="city_name"    />
        <result property="areaId"    column="area_id"    />
        <result property="areaName"    column="area_name"    />
        <result property="address"    column="address"    />
        <result property="longitude"    column="longitude"    />
        <result property="latitude"    column="latitude"    />
        <result property="visitorCount"    column="visitor_count"    />
        <result property="shopStatus"    column="shop_status"    />
        <result property="createAt"    column="create_at"    />
        <result property="updateAt"    column="update_at"    />
        <result property="delFlag"    column="del_flag"    />
    </resultMap>

    <sql id="selectMallShopShopVo">
        select shop_id, sys_user_id, shop_name, shop_phone, shop_account, shop_password, shop_image, shop_banner, shop_license, shop_permit, shop_business_begin, shop_business_end, shop_money, province_id, province_name, city_id, city_name, area_id, area_name, address, longitude, latitude, visitor_count, shop_status, create_at, update_at, del_flag from mall_shop_shop
    </sql>

    <select id="selectMallShopShopList" parameterType="MallShopShop" resultMap="MallShopShopResult">
        select mss.shop_id, mss.sys_user_id, mss.shop_name, mss.shop_phone, mss.shop_account, mss.shop_password, mss.shop_image, mss.shop_banner, mss.shop_license, mss.shop_permit, mss.shop_business_begin, mss.shop_business_end, mss.shop_money,
               mss.province_id, mrProvince.name province_name, mss.city_id, mrCity.name city_name, mss.area_id, mrArea.name area_name, mss.address,
               mss.longitude, mss.latitude, mss.visitor_count, mss.shop_status, mss.create_at, mss.update_at, mss.del_flag
        from mall_shop_shop mss
        left join mall_region mrProvince on mss.province_id = mrProvince.id
        left join mall_region mrCity on mss.city_id = mrCity.id
        left join mall_region mrArea on mss.area_id = mrArea.id
        <where>
            <if test="shopId != null "> and mss.shop_id = #{shopId}</if>
            <if test="sysUserId != null "> and mss.sys_user_id = #{sysUserId}</if>
            <if test="shopName != null  and shopName != ''"> and mss.shop_name like concat('%', #{shopName}, '%')</if>
            <if test="shopPhone != null  and shopPhone != ''"> and mss.shop_phone like concat('%', #{shopPhone}, '%')</if>
            <if test="shopAccount != null  and shopAccount != ''"> and mss.shop_account like concat('%', #{shopAccount}, '%')</if>
            <if test="shopPassword != null  and shopPassword != ''"> and mss.shop_password = #{shopPassword}</if>
            <if test="shopImage != null  and shopImage != ''"> and mss.shop_image = #{shopImage}</if>
            <if test="shopBanner != null  and shopBanner != ''"> and mss.shop_banner = #{shopBanner}</if>
            <if test="shopLicense != null  and shopLicense != ''"> and mss.shop_license = #{shopLicense}</if>
            <if test="shopPermit != null  and shopPermit != ''"> and mss.shop_permit = #{shopPermit}</if>
            <if test="shopBusinessBegin != null  and shopBusinessBegin != ''"> and mss.shop_business_begin = #{shopBusinessBegin}</if>
            <if test="shopBusinessEnd != null  and shopBusinessEnd != ''"> and mss.shop_business_end = #{shopBusinessEnd}</if>
            <if test="shopMoney != null "> and mss.shop_money = #{shopMoney}</if>
            <if test="provinceId != null "> and mss.province_id = #{provinceId}</if>
            <if test="provinceName != null  and provinceName != ''"> and mrProvince.name like concat('%', #{provinceName}, '%')</if>
            <if test="cityId != null "> and mss.city_id = #{cityId}</if>
            <if test="cityName != null  and cityName != ''"> and mrCity.name like concat('%', #{cityName}, '%')</if>
            <if test="areaId != null "> and mss.area_id = #{areaId}</if>
            <if test="areaName != null  and areaName != ''"> and mrArea.name like concat('%', #{areaName}, '%')</if>
            <if test="address != null  and address != ''"> and mss.address like concat('%', #{address}, '%')</if>
            <if test="longitude != null  and longitude != ''"> and mss.longitude = #{longitude}</if>
            <if test="latitude != null  and latitude != ''"> and mss.latitude = #{latitude}</if>
            <if test="visitorCount != null "> and mss.visitor_count = #{visitorCount}</if>
            <if test="shopStatus != null "> and mss.shop_status = #{shopStatus}</if>
            <if test="delFlag != null "> and mss.del_flag = #{delFlag}</if>
            <if test="createAt != null  and createAt != ''"> and mss.create_at = #{createAt}</if>
            <if test="updateAt != null  and updateAt != ''"> and mss.update_at = #{updateAt}</if>
        </where>
        order by mss.create_at desc
    </select>

    <select id="selectMallShopShopByShopId" parameterType="Long" resultMap="MallShopShopResult">
        <include refid="selectMallShopShopVo"/>
        where shop_id = #{shopId}
    </select>

    <insert id="insertMallShopShop" parameterType="MallShopShop" useGeneratedKeys="true" keyProperty="shopId">
        insert into mall_shop_shop
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="sysUserId != null">sys_user_id,</if>
            <if test="shopName != null">shop_name,</if>
            <if test="shopPhone != null">shop_phone,</if>
            <if test="shopAccount != null">shop_account,</if>
            <if test="shopPassword != null">shop_password,</if>
            <if test="shopImage != null">shop_image,</if>
            <if test="shopBanner != null">shop_banner,</if>
            <if test="shopLicense != null">shop_license,</if>
            <if test="shopPermit != null">shop_permit,</if>
            <if test="shopBusinessBegin != null">shop_business_begin,</if>
            <if test="shopBusinessEnd != null">shop_business_end,</if>
            <if test="shopMoney != null">shop_money,</if>
            <if test="provinceId != null">province_id,</if>
            <if test="provinceName != null">province_name,</if>
            <if test="cityId != null">city_id,</if>
            <if test="cityName != null">city_name,</if>
            <if test="areaId != null">area_id,</if>
            <if test="areaName != null">area_name,</if>
            <if test="address != null">address,</if>
            <if test="longitude != null">longitude,</if>
            <if test="latitude != null">latitude,</if>
            <if test="visitorCount != null">visitor_count,</if>
            <if test="shopStatus != null">shop_status,</if>
            <if test="createAt != null">create_at,</if>
            <if test="updateAt != null">update_at,</if>
            <if test="delFlag != null">del_flag,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="sysUserId != null">#{sysUserId},</if>
            <if test="shopName != null">#{shopName},</if>
            <if test="shopPhone != null">#{shopPhone},</if>
            <if test="shopAccount != null">#{shopAccount},</if>
            <if test="shopPassword != null">#{shopPassword},</if>
            <if test="shopImage != null">#{shopImage},</if>
            <if test="shopBanner != null">#{shopBanner},</if>
            <if test="shopLicense != null">#{shopLicense},</if>
            <if test="shopPermit != null">#{shopPermit},</if>
            <if test="shopBusinessBegin != null">#{shopBusinessBegin},</if>
            <if test="shopBusinessEnd != null">#{shopBusinessEnd},</if>
            <if test="shopMoney != null">#{shopMoney},</if>
            <if test="provinceId != null">#{provinceId},</if>
            <if test="provinceName != null">#{provinceName},</if>
            <if test="cityId != null">#{cityId},</if>
            <if test="cityName != null">#{cityName},</if>
            <if test="areaId != null">#{areaId},</if>
            <if test="areaName != null">#{areaName},</if>
            <if test="address != null">#{address},</if>
            <if test="longitude != null">#{longitude},</if>
            <if test="latitude != null">#{latitude},</if>
            <if test="visitorCount != null">#{visitorCount},</if>
            <if test="shopStatus != null">#{shopStatus},</if>
            <if test="createAt != null">#{createAt},</if>
            <if test="updateAt != null">#{updateAt},</if>
            <if test="delFlag != null">#{delFlag},</if>
         </trim>
    </insert>

    <update id="updateMallShopShop" parameterType="MallShopShop">
        update mall_shop_shop
        <trim prefix="SET" suffixOverrides=",">
            <if test="sysUserId != null">sys_user_id = #{sysUserId},</if>
            <if test="shopName != null">shop_name = #{shopName},</if>
            <if test="shopPhone != null">shop_phone = #{shopPhone},</if>
            <if test="shopAccount != null">shop_account = #{shopAccount},</if>
            <if test="shopPassword != null">shop_password = #{shopPassword},</if>
            <if test="shopImage != null">shop_image = #{shopImage},</if>
            <if test="shopBanner != null">shop_banner = #{shopBanner},</if>
            <if test="shopLicense != null">shop_license = #{shopLicense},</if>
            <if test="shopPermit != null">shop_permit = #{shopPermit},</if>
            <if test="shopBusinessBegin != null">shop_business_begin = #{shopBusinessBegin},</if>
            <if test="shopBusinessEnd != null">shop_business_end = #{shopBusinessEnd},</if>
            <if test="shopMoney != null">shop_money = #{shopMoney},</if>
            <if test="provinceId != null">province_id = #{provinceId},</if>
            <if test="provinceName != null">province_name = #{provinceName},</if>
            <if test="cityId != null">city_id = #{cityId},</if>
            <if test="cityName != null">city_name = #{cityName},</if>
            <if test="areaId != null">area_id = #{areaId},</if>
            <if test="areaName != null">area_name = #{areaName},</if>
            <if test="address != null">address = #{address},</if>
            <if test="longitude != null">longitude = #{longitude},</if>
            <if test="latitude != null">latitude = #{latitude},</if>
            <if test="visitorCount != null">visitor_count = #{visitorCount},</if>
            <if test="shopStatus != null">shop_status = #{shopStatus},</if>
            <if test="createAt != null">create_at = #{createAt},</if>
            <if test="updateAt != null">update_at = #{updateAt},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
        </trim>
        where shop_id = #{shopId}
    </update>

    <delete id="deleteMallShopShopByShopId" parameterType="Long">
        delete from mall_shop_shop where shop_id = #{shopId}
    </delete>

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

    <select id="selectMallShopShopByShopName" resultMap="MallShopShopResult">
        <include refid="selectMallShopShopVo"/>
        where shop_name = #{shopName}
    </select>

    <select id="selectMallShopShopByShopAccount" resultMap="MallShopShopResult">
        <include refid="selectMallShopShopVo"/>
        where shop_account = #{shopAccount}
    </select>

    <select id="selectMallShopShopBySysUserId" resultMap="MallShopShopResult">
        <include refid="selectMallShopShopVo"/>
        where sys_user_id = #{sysUserId}
    </select>

    <select id="getShopNearestDistance" parameterType="map" resultType="map">
        select shop_id shopId,
        shop_name shopName,
        shop_image shopImage,
        shop_phone shopPhone,
        CONCAT(
        IFNULL(`province_name`, ''),
        IFNULL(`city_name`, ''),
        IFNULL(`area_name`, ''),
        IFNULL(`address`, '')
        ) shopAddress,
        ROUND(
        st_distance (
        POINT (#{longitude}, #{latitude}),
        POINT (longitude, latitude)
        ) * 111195 / 1000,
        1 ) AS distance
        from mall_shop_shop
        where
            del_flag = 0
            and shop_status = 0
        order by distance asc
    </select>

    <select id="queryShopList" resultType="map">
        select shop_id shopId,
               shop_name shopName,
               shop_image shopImage,
               shop_phone shopPhone,
               CONCAT(
               IFNULL(`province_name`, ''),
               IFNULL(`city_name`, ''),
               IFNULL(`area_name`, ''),
               IFNULL(`address`, '')
                   ) shopAddress
        from mall_shop_shop
        where
            del_flag = 0
          and shop_status = 0
        order by shop_id asc
    </select>

    <select id="selectAllShopList" resultType="cascaderVO">
        select mss.shop_id id,
               mss.shop_name name,
               mss.shop_phone phone
        from mall_shop_shop mss
        where mss.del_flag = '0'
        order by mss.shop_id asc
    </select>

</mapper>
