<?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="getCoordinateList" parameterType="java.util.Map" resultMap="tourGoodMap">
        SELECT t.*  from tour_good t
            left  join (
            select good_id,
            IFNULL(ROUND(( (2 * ASIN( SQRT( POW( SIN((latitude * PI() / 180.0- #{params.latitude}* PI() / 180.0)/2), 2)+COS( latitude * PI() / 180.0)*COS( #{params.latitude} * PI() / 180.0)
            *POW(SIN((longitude * PI() / 180.0 - #{params.longitude}* PI() /180.0)/2),2))))*6378.137)*10000)/10000 ,0 ) AS distance
            FROM tour_good_site WHERE type=2 ) gs ON t.id=gs.good_id
        where  t.is_del=0 AND  t.status=1
        <if test="params.distance != null and params.distance != ''and params.distance >0">
            and  gs.distance &lt; #{params.distance}
        </if>
        <if test="params.query != null and params.query != ''">
            and (t.`name` like CONCAT('%',#{params.query},'%') or t.introduce like CONCAT('%',#{params.query},'%'))
        </if>
          order  by gs.distance
    </select>

    <!--查询旅游路线列表-->
    <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.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 (SELECT * FROM tour_good_tag WHERE is_del=0) tag ON g.id=tag.good_id
        LEFT JOIN (SELECT * FROM tour_tag WHERE is_del=0) t ON tag.tag_id=t.id
         where g.recommend=1 and g.status=1 and g.is_del=0
         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 (SELECT * FROM tour_good_tag WHERE is_del=0) tag ON g.id=tag.good_id
        LEFT JOIN (SELECT * FROM tour_tag WHERE is_del=0) 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} and is_del=0 )
        </if>
        </where>

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

</mapper>