<?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="bus.mapper.BCourseInfoMapper">

 <select id="list" parameterType="bus.model.dto.page.BCourseInfoPageDto" resultType="bus.model.vo.BCourseInfoVo">
     SELECT
     a.id,
     a.course_name,
     a.course_code,
     a.course_type,
     a.area,
     COUNT(b.station_id) AS stationNum,
     COUNT(c.bus_id) AS busNum,
     a.first_station_name as firstStationName,
     a.last_station_name as lastStationName,
     a.first_bus_space as firstBusSpace,
     a.status
     from b_course_info a
     LEFT JOIN
     b_course_station b ON a.id = b.course_id AND b.is_delete = 0
     LEFT JOIN
     b_course_bus c ON a.id = c.course_id AND c.is_delete = 0
   <where>
        a.is_delete = 0
       <if test="courseName != null and courseName != ''">
           and a.course_name like concat('%', #{courseName}, '%')
       </if>
       <if test="courseCode != null and courseCode != ''">
           and  a.course_code like concat('%', #{courseCode}, '%')
       </if>
       <if test="courseType != null and courseType != ''">
           and a.course_type = #{courseType}
       </if>
       <if test="status != null and status != ''">
           and  a.status = #{status}
       </if>
       <if test="id != null and id != ''">
           and a.id = #{id}
       </if>
   </where>
     GROUP BY
     a.id
     ORDER BY a.id dESC
 </select>

    <!-- 查询附近站点 -->
    <select id="findNearbyStations" resultType="bus.model.dto.BStationInfoDto"
            parameterType="bus.model.dto.NearbyRoutesDto" >
        SELECT
        id,
        name,
        latitude,
        longitude,
        ROUND(
        6378.137 * 2 * ASIN(
        SQRT(
        POW(SIN((#{latitude} - latitude) * PI() / 180 / 2), 2) +
        COS(#{latitude} * PI() / 180) * COS(latitude * PI() / 180) *
        POW(SIN((#{longitude} - longitude) * PI() / 180 / 2), 2)
        )
        ) * 1000
        , 2) AS distance
        FROM b_station_info
        WHERE
        is_delete = 0
        AND latitude BETWEEN #{latitude} -  #{radius} / 111300 AND #{latitude} + #{radius} / 111300
        AND longitude BETWEEN #{longitude} - #{radius} / (111300 * COS(#{latitude} * PI() / 180))
        AND #{longitude} + #{radius} / (111300 * COS(#{latitude} * PI() / 180))
        AND (
        6378.137 * 2 * ASIN(
        SQRT(
        POW(SIN((#{latitude} - latitude) * PI() / 180 / 2), 2) +
        COS(#{latitude} * PI() / 180) * COS(latitude * PI() / 180) *
        POW(SIN((#{longitude} - longitude) * PI() / 180 / 2), 2)
        )
        ) * 1000
        ) &lt;= #{radius}
        ORDER BY distance
    </select>

    <!-- 查询经过指定站点的路线 -->
    <select id="findRoutesByStations" resultType="bus.model.vo.BCourseInfoVo">
        SELECT DISTINCT
        r.id,
        r.course_name as courseName,
        r.course_code as courseCode,
        r.map_pic_url as mapPicUrl,
        r.first_station_name as firstStationName,
        r.last_station_name as lastStationName,
        r.first_bus_space as firstBusSpace,
        r.area as area,
        r.map_point as mapPoint,
        rs.station_id as stationId
        FROM
        b_course_info r
        INNER JOIN
        b_course_station rs ON r.id = rs.course_id
        WHERE
        r.is_delete = 0
        and rs.is_delete = 0
        AND rs.station_id IN
        <foreach collection="stationIds" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        ORDER BY
        rs.id
    </select>

    <!-- 查询经过指定站点的路线 -->
    <select id="findRoutesByStationId" resultType="bus.model.vo.BCourseInfoVo">
        SELECT DISTINCT
        r.id,
        r.course_name as courseName,
        r.course_code as courseCode,
        r.map_pic_url as mapPicUrl,
        r.first_station_name as firstStationName,
        r.last_station_name as lastStationName,
        r.first_bus_space as firstBusSpace,
        r.area as area,
        r.map_point as mapPoint,
        rs.station_id as stationId
        FROM
        b_course_info r
        INNER JOIN
        b_course_station rs ON r.id = rs.course_id
        WHERE
        r.is_delete = 0
        and rs.is_delete = 0
        AND rs.station_id IN
        <foreach collection="stationIds" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        ORDER BY
        r.id
    </select>

</mapper>
