<?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.xxfc.platform.campsite.mapper.CampsiteShopMapper">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.xxfc.platform.campsite.entity.CampsiteShop" id="campsiteShopMap">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="logo" column="logo"/>
        <result property="intro" column="intro"/>
        <result property="url" column="url"/>
        <result property="concat" column="concat"/>
        <result property="idNumber" column="id_number"/>
        <result property="phone" column="phone"/>
        <result property="companyId" column="company_id"/>
        <result property="companyName" column="company_name"/>
        <result property="startTime" column="start_time"/>
        <result property="province" column="province"/>
        <result property="provinceName" column="province_name"/>
        <result property="city" column="city"/>
        <result property="cityName" column="city_name"/>
        <result property="longitude" column="longitude"/>
        <result property="latitude" column="latitude"/>
        <result property="address" column="address"/>
        <result property="servicePhone" column="service_phone"/>
        <result property="configure" column="configure"/>
        <result property="content" column="content"/>
        <result property="status" column="status"/>
        <result property="crtTime" column="crt_time"/>
        <result property="updTime" column="upd_time"/>
        <result property="isDel" column="is_del"/>
        <result property="hot" column="hot"/>
        <result property="saleState" column="sale_state"/>
        <result property="alt" column="alt"/>
        <result property="imgTitle" column="img_title"/>
        <result property="imgKeyword" column="img_keyword"/>
        <result property="imgDesc" column="img_desc"/>
    </resultMap>

    <!--根据类型查询全部-->
    <select id="findAllCampsiteShopsByTypeOrCodeOrName" resultType="com.xxfc.platform.campsite.dto.CampsiteShopPageDTO">
        SELECT
        cs.id AS `id`,
        cs. NAME AS `name`,
        cs.logo AS `logo`,
        cs.url AS `url`,
        cs.province_name AS `provinceName`,
        cs.city_name AS `cityName`,
        cs.service_phone AS `phone`,
        cs.longitude AS `longitude`,
        cs.latitude AS `latitude`,
        cs.`address` AS `address`,
        cs.hot AS `hot`,
        cs.crt_time AS `crtTime`,
        cs.concat AS `concat`,
        cs.alt AS `alt`,
        cs.img_title AS `imgTitle`,
        cs.img_keyword AS `imgKeyword`,
        cs.img_desc AS `imgDesc`,
        REPLACE(GROUP_CONCAT( DISTINCT ct.`name` ),',','|') AS `storeTypeName`
        FROM campsite_shop cs
        LEFT JOIN campsite_shop_tag cst ON cs.id = cst.shop_id
        LEFT JOIN campsite_tag ct ON cst.tag_id = ct.id
        WHERE
        cs.sale_state = 1
        AND cs.is_del = 0

        <if test="type!=null">
            and cst.tag_id=#{type}
        </if>
        <if test="addrProvince != null">
            and cs.`province`=#{addrProvince}
        </if>
        <if test="addrCity != null">
            and cs.`city`=#{addrCity}
        </if>
        <if test="name!=null and name!=''">
            and cs.`name` like concat('%',#{name},'%')
        </if>
        <if test="keyWord!=null and keyWord!=''">
            and cs.`name` like concat('%',#{keyWord},'%')
        </if>
        GROUP BY cs.id
        order by cs.hot DESC,cs.crt_time DESC
    </select>

    <!--首页查询全部-->
    <select id="findAllByHome" resultType="com.github.wxiaoqi.security.common.vo.GoodDataVO">
        select cs.id as `id`,cs.name as `name`,cs.logo as `imgUrl`,cs.province_name as `name1`,cs.longitude,cs.latitude,cs.alt,cs.img_title as `imgTitle`,cs.img_keyword as `imgKeyword`,cs.img_desc as `imgDesc`
        FROM `campsite_shop`  cs
        where cs.sale_state=1 and cs.is_del=0
        order  by cs.hot desc
        limit #{start,jdbcType=INTEGER},#{size,jdbcType=INTEGER}
    </select>

    <select id="findAllAndJlByHome" resultType="com.github.wxiaoqi.security.common.vo.GoodDataVO">
        select cs.id as `id`,cs.name as `name`,cs.logo as `imgUrl`,cs.province_name as `name1`,cs.longitude,cs.latitude,cs.alt,cs.img_title as `imgTitle`,cs.img_keyword as `imgKeyword`,cs.img_desc as `imgDesc`,
        ROUND(( (2 * ASIN( SQRT( POW( SIN((latitude * PI() / 180.0
        - #{lat} * PI() / 180.0)/2), 2)
        +COS( latitude * PI() / 180.0)*COS(#{lat} * PI() / 180.0)
        *POW(SIN((longitude * PI() / 180.0 - #{lon} * PI() /
          180.0)/2),2))))*6378.137)*10000)/10000 as distance
        FROM `campsite_shop`  cs
        where cs.sale_state=1 and cs.is_del=0
        order  by cs.hot desc
        limit #{start,jdbcType=INTEGER},#{size,jdbcType=INTEGER}
    </select>

    <select id="findAll" resultType="com.github.wxiaoqi.security.common.vo.GoodDataVO">
        select cs.id as `id`,cs.name as `name`,cs.logo as `imgUrl`,cs.province_name as `name1`,cs.longitude,cs.latitude,cs.alt,cs.img_title as `imgTitle`,cs.img_keyword as `imgKeyword`,cs.img_desc as `imgDesc`
        FROM `campsite_shop`  cs
        where cs.sale_state=1 and cs.is_del=0
        order  by cs.hot desc
    </select>

    <!--根据id查询详情-->
    <select id="findCampsiteShopDetailById" resultType="com.xxfc.platform.campsite.dto.CampsiteShopDetailDTO">
        select `name` as `name`,`province_name` as `provinceName`,`city_name` as `cityName`,`address` as `address`,`service_phone` as `phone`,`logo` as `logo`,`poster_background` as `posterBackground` ,
        `content` as `content`,`configure` as `configure`,`longitude` as `longitude`,`latitude` as `latitude`,`alt`,img_title as `imgTitle`,img_keyword as `imgKeyword`,img_desc as `imgDesc` from `campsite_shop` where `id`=#{id}
    </select>

    <!--根据查询条件分页查询-->
    <select id="findAllCampsiteShops" parameterType="com.xxfc.platform.campsite.dto.CampsiteShopAdminFindDTO"
            resultType="com.xxfc.platform.campsite.dto.CampsiteShopAdminPageDTO">
        SELECT `id`,`name`,`hot`,`crt_time` as `crtTime`,`start_time` as `shopStartTime`,`sale_state` as
        `saleState` FROM `campsite_shop`
        where `is_del`=0
        <if test="startTime != null and endTime!=null">
            and start_time between #{startTime} and #{endTime}
        </if>
        <if test="startTime != null and endTime==null">
            and <![CDATA[ start_time >= #{startTime} ]]>
        </if>
        <if test="endTime != null and startTime==null">
            and <![CDATA[ start_time <= #{endTime} ]]>
        </if>
        <if test="name != null and name != ''">
            and `name` like concat('%',#{name},'%')
        </if>
        ORDER BY `hot` DESC
    </select>

    <!--根据id更新营地状态-->
    <update id="updateCampsiteStatusById">
        update `campsite_shop` set `is_del`=#{status} where `id`=#{id}
    </update>

    <!--根据id更新营地上下架状态-->
    <update id="updateCampsiteSaleStatusById">
        update `campsite_shop` set `sale_state`=#{status} where `id`=#{id}
    </update>

    <!--检验营地名称是否存在-->
    <select id="checkNameExist" resultType="int">
        select count(`id`) from `campsite_shop` where `name`=#{name}
        <if test="id != null">
            and `id`!=#{id}
        </if>
    </select>
</mapper>