<?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"/>
        <result property="corporationId" column="corporation_id"/>
        <result property="companyId" column="company_id"/>
    </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"/>
        <result property="corporationId" column="corporationId"/>
        <result property="companyId" column="company_id"/>
        <result property="companyName" column="companyName"/>
        <result property="corporationName" column="corporationName"/>
    </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 t.rank DESC,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>
        order by t.rank DESC
    </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 and name!='全部') 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,
        c.`name` as companyName,
        IFNULL(i.`name`,ci.`name`) as corporationName,
        if(i.id > 0,i.id,g.corporation_id)  as corporationId
        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
        LEFT JOIN  branch_company  c ON g.company_id=c.id
        LEFT JOIN  company_info i ON c.company_id=i.id
        LEFT JOIN  company_info ci ON g.corporation_id=ci.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.companyId != null and params.companyId > 0">
                and g.company_id=#{params.companyId}
            </if>
            <if test="params.corporationId != null and params.corporationId > 0 ">
                and g.corporation_id=#{params.corporationId}
            </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>
            <if test="params.dataCompanyIds != null and params.dataCompanyIds.size > 0">
                and g.company_id in
                <foreach collection="params.dataCompanyIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="params.dataCorporationIds != null and params.dataCorporationIds.size > 0">
                and ( i.id  in
                <foreach collection="params.dataCorporationIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                or ( g.corporation_id  in
                <foreach collection="params.dataCorporationIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
                and g.company_id = 0  )
                )
            </if>
        </where>

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

    <select id="getList" resultType="com.github.wxiaoqi.security.common.vo.GoodDataVO">
        SELECT
        g.id AS `id`,
        g. NAME AS `name`,
        g.cover AS `imgUrl`,
        g.price
        FROM
        tour_good g,
        tour_good_tag t
        WHERE
        g.id = t.good_id
        AND g.is_del = 0
        AND t.is_del = 0
        AND g.status = 1
        AND t.tag_id IN
        <foreach collection="tagIds" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        GROUP BY g.id
        ORDER BY
        g.rank ASC,
        g.crt_time DESC
        LIMIT #{limit}
    </select>

    <select id="newTour" resultType="com.github.wxiaoqi.security.common.vo.GoodDataVO">
        SELECT
	    id,
        name,
        cover AS imgUrl,
        price
        FROM
        	tour_good
        WHERE
        	is_del = 0
        AND
            status = 1
        ORDER BY
        	crt_time DESC
        LIMIT #{limit}
    </select>
</mapper>