<?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.tour.mapper.TourGoodMapper">

	<!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.xxfc.platform.tour.entity.TourGood" id="tourGoodMap">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="cover" column="cover"/>
        <result property="price" column="price"/>
        <result property="childPrice" column="child_price"/>
        <result property="viewCount" column="view_count"/>
        <result property="saleCount" column="sale_count"/>
        <result property="describe" column="describes"/>
        <result property="content" column="content"/>
        <result property="explain" column="explains"/>
        <result property="introduce" column="introduce"/>
        <result property="rank" column="rank"/>
        <result property="status" column="status"/>
        <result property="crtTime" column="crt_time"/>
        <result property="updTime" column="upd_time"/>
        <result property="recommend" column="recommend"/>
        <result property="isMember" column="is_member"/>
        <result property="stock" column="stock"/>
        <result property="unit" column="unit"/>
        <result property="isOutside" column="is_outside"/>
    </resultMap>

    <resultMap type="com.xxfc.platform.tour.vo.TourGoodVo" id="tourGoodVoMap">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="cover" column="cover"/>
        <result property="price" column="price"/>
        <result property="childPrice" column="child_price"/>
        <result property="viewCount" column="view_count"/>
        <result property="saleCount" column="sale_count"/>
        <result property="describe" column="describes"/>
        <result property="explain" column="explains"/>
        <result property="content" column="content"/>
        <result property="introduce" column="introduce"/>
        <result property="rank" column="rank"/>
        <result property="status" column="status"/>
        <result property="crtTime" column="crt_time"/>
        <result property="updTime" column="upd_time"/>
        <result property="recommend" column="recommend"/>
        <result property="isMember" column="is_member"/>
        <result property="stock" column="stock"/>
        <result property="unit" column="unit"/>
        <result property="tagNames" column="tagNames"/>
        <result property="isOutside" column="is_outside"/>
    </resultMap>

    <!--查询旅游路线列表-->
    <select id="getGoodList" parameterType="java.util.Map" resultMap="tourGoodMap">
        SELECT * from tour_good t
        where  t.is_del=0 AND  t.status=1
        <if test="params.query != null and params.query != ''">
            and (t.`name` like CONCAT('%',#{params.query},'%') or t.introduce like CONCAT('%',#{params.query},'%'))
        </if>
       <if test="params.latitude != null and params.latitude != '' and params.longitude != null and params.longitude != ''">
           and t.id in (SELECT s.good_id from (
           select good_id,
           if(ROUND(( (2 * ASIN( SQRT( POW( SIN((latitude * PI() / 180.0- #{params.latitude}* PI() / 180.0)/2), 2)+COS( latitude * PI() / 180.0)*COS( #{params.longitude} * PI() / 180.0)
           *POW(SIN((longitude * PI() / 180.0 - #{params.longitude}* PI() /180.0)/2),2))))*6378.137)*10000)/10000 is null ,0,ROUND(( (2 * ASIN( SQRT( POW( SIN((latitude * PI() / 180.0- #{params.latitude}* PI() / 180.0)/2), 2)+COS( latitude * PI() / 180.0)*COS(#{params.longitude} * PI() / 180.0)
           *POW(SIN((longitude * PI() / 180.0 - #{params.longitude}* PI() /180.0)/2),2))))*6378.137)*10000)/10000 ) AS distance
           FROM tour_good_site
           order by distance
           ) s
           where s.distance &lt; #{params.distance})
       </if>
      <if test="params.tagId != null and params.tagId != '' ">
          and t.id in (SELECT tg.good_id from tour_good_tag tg
          where tg.tag_id = #{params.tagId}) and t.recommend = 1
          ORDER BY t.recommend
      </if>

    </select>

    <!--首页查询全部-->
    <select id="findAllByHome" resultType="com.github.wxiaoqi.security.common.vo.GoodDataVO">
        SELECT g.id as `id`,g.name as `name`,g.cover as `imgUrl`,g.price,
        REPLACE(GROUP_CONCAT( DISTINCT t.`name`),',','|')as name1
          from tour_good g
          LEFT JOIN tour_good_tag tag ON g.id=tag.good_id
        LEFT JOIN tour_tag t ON tag.tag_id=t.id
         where g.recommend=1
         GROUP BY g.id
        ORDER BY g.rank DESC ,g.id DESC
        limit #{start,jdbcType=INTEGER},#{size,jdbcType=INTEGER}
    </select>

    <select id="getOne" resultMap="tourGoodMap">
    SELECT *  FROM tour_good  WHERE  id =#{id} and is_del=0
</select>

    <!--后台查询旅游路线列表-->
    <select id="findGoodList" resultMap="tourGoodVoMap">
        SELECT g.*,REPLACE(GROUP_CONCAT( DISTINCT t.`name`),',','|')as tagNames  from tour_good g
        LEFT JOIN tour_good_tag tag ON g.id=tag.good_id
        LEFT JOIN tour_tag t ON tag.tag_id=t.id
        <where>
            g.is_del=0
        <if test="params.name != null and params.name != ''">
            and (g.`name` like CONCAT('%',#{params.name},'%') or g.introduce like CONCAT('%',#{params.name},'%'))
        </if>
        <if test="params.tagId != null and params.tagId != '' ">
            and g.id in (SELECT good_id from tour_good_tag
            where tag_id = #{params.tagId})
        </if>
        </where>

        GROUP BY g.id
        ORDER BY g.rank DESC ,g.id DESC
    </select>

</mapper>