<?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.base.track.mapper.SubPointDataMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.base.track.model.entity.SubPointData">
        <id column="id" property="id"/>
        <result column="satellite_id" property="satelliteId"/>
        <result column="date" property="date"/>
        <result column="lat" property="lat"/>
        <result column="lon" property="lon"/>
        <result column="lr_lat" property="lrLat"/>
        <result column="lr_lon" property="lrLon"/>
        <result column="rr_lat" property="rrLat"/>
        <result column="rr_lon" property="rrLon"/>
        <result column="ls_lat" property="lsLat"/>
        <result column="ls_lon" property="lsLon"/>
        <result column="rs_lat" property="rsLat"/>
        <result column="rs_lon" property="rsLon"/>
        <result column="alt" property="alt"/>
        <result column="orbit" property="orbit"/>
        <result column="updown" property="updown"/>
    </resultMap>


    <select id="getSingleSatelliteSubPointDataList" resultType="com.base.track.model.entity.SubPointData">
        select any_value(id) as id ,any_value(date) as date,any_value(satellite_id) as satellite_id,
        any_value(lat) as lat,any_value(lon) as lon,any_value(alt) as alt,any_value(orbit) as orbit,any_value(updown) as
        updown
        from `subpoint_${code}`
        <where>
            <if test="startTime!=null">
                date &gt;= #{startTime}
            </if>
            <if test="endTime!=null">
                and date &lt;= #{endTime}
            </if>
            and DATE_FORMAT(date,'%1')%1 =0 GROUP BY DATE_FORMAT(date,'%Y-%m-%d%H:%i')

        </where>
    </select>

    <select id="satelliteSubPointDataList" resultType="com.base.track.model.entity.SubPointData">
        select any_value(id) as id ,any_value(date) as date,any_value(satellite_id) as satellite_id,
        any_value(lat) as lat,any_value(lon) as lon,any_value(alt) as alt,any_value(orbit) as orbit,any_value(updown) as
        updown
        from `subpoint_${code}`
        <where>
            <if test="startTime!=null">
                date &gt;= #{startTime}
            </if>
            <if test="endTime!=null">
                and date &lt;= #{endTime}
            </if>
            and DATE_FORMAT(date,'%60')%60 =0 GROUP BY DATE_FORMAT(date,'%Y-%m-%d%H:%i')

        </where>
    </select>

    <select id="getSubPointDataLists" resultType="com.base.track.model.entity.SubPointData">
        select * from `subpoint_${code}`
        <where>
            <if test="startTime!=null">
                date &gt;= #{startTime}
            </if>
            <if test="endTime!=null">
                and date &lt;= #{endTime}
            </if>
            <if test="updown!=null and updown!=''">
                and updown = #{updown}
            </if>
        </where>
    </select>

    <delete id="deleteByDate" parameterType="string">
        delete
        from subpoint_data
        where date >= #{startTime}
          and date <![CDATA[<=]]> #{endTime}
    </delete>


    <select id="selectByStaAndTime" resultType="com.base.track.model.entity.SubPointData">
        select *
        from subpoint_data
        <where>
            <if test="code!=null">
                satellite_id in
                <foreach collection="code" index="index" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and date >= #{startTime}
            and date <![CDATA[<=]]> #{endTime}
        </where>
    </select>


    <select id="selectEleSubPointDataList" resultType="com.base.track.model.entity.SubPointData">
        select * from subpoint_${dto.code} a
        <where>
            <if test="dto.startTime!=null">
                a.date &gt;= #{dto.startTime}
            </if>
            <if test="dto.endTime!=null">
                and a.date &lt;= #{dto.endTime}
            </if>
            and a.updown = 0
            and (a.lr_lat &gt;= ${dto.southLat} and a.rr_lat &lt;= ${dto.northLat})
            and (a.lr_lon &lt; a.rr_lon and a.rr_lon &gt;= ${dto.westLon} and a.lr_lon &lt;= ${dto.eastLon})
        </where>
        union all
        select * from subpoint_${dto.code} b
        <where>
            <if test="dto.startTime!=null">
                b.date &gt;= #{dto.startTime}
            </if>
            <if test="dto.endTime!=null">
                and b.date &lt;= #{dto.endTime}
            </if>
            and b.updown = 0
            and (b.lr_lat &gt;= ${dto.southLat}
            and b.rr_lat &lt;= ${dto.northLat})
            and ((b.lr_lon &gt; b.rr_lon)
            and
            <choose>
                <when test="dto.westLon lt 0">
                    ((b.rr_lon) &gt;= ${dto.westLon})
                </when>
                <otherwise>
                    ((b.rr_lon) &gt;= ${dto.westLon} - 360)
                </otherwise>
            </choose>
            and
            <choose>
                <when test="dto.eastLon lt 0">
                    (b.lr_lon &lt;= ${dto.eastLon}+360)
                </when>
                <otherwise>
                    (b.lr_lon &lt;= ${dto.eastLon})
                </otherwise>
            </choose>
            )
        </where>
        union all
        select * from subpoint_${dto.code} c
        <where>
            <if test="dto.startTime!=null">
                c.date &gt;= #{dto.startTime}
            </if>
            <if test="dto.endTime!=null">
                and c.date &lt;= #{dto.endTime}
            </if>
            and c.updown = 1
            and (c.lr_lat &gt;= ${dto.southLat}
            and c.rr_lat &lt;= ${dto.northLat})
            and (c.lr_lon &lt; c.rr_lon
            and c.rr_lon &gt;= ${dto.westLon}
            and c.lr_lon &lt;= ${dto.eastLon})
        </where>
        union all
        select * from subpoint_${dto.code} d
        <where>
            <if test="dto.startTime!=null">
                d.date &gt;= #{dto.startTime}
            </if>
            <if test="dto.endTime!=null">
                and d.date &lt;= #{dto.endTime}
            </if>
            and d.updown = 1
            and (d.lr_lat &gt;= ${dto.southLat}
            and d.rr_lat &lt;= ${dto.northLat})
            and ((d.lr_lon &gt; d.rr_lon)
            and
            <choose>
                <when test="dto.westLon lt 0">
                    ((d.rr_lon) &gt;= ${dto.westLon})
                </when>
                <otherwise>
                    ((d.rr_lon) &gt;= ${dto.westLon} - 360)
                </otherwise>
            </choose>
            and
            <choose>
                <when test="dto.eastLon lt 0">
                    (d.lr_lon &lt;= ${dto.eastLon}+360)
                </when>
                <otherwise>
                    (d.lr_lon &lt;= ${dto.eastLon})
                </otherwise>
            </choose>
            )
        </where>
    </select>

    <select id="selectCommonSubPointDataList" resultType="com.base.track.model.entity.SubPointData">
        select * from subpoint_${dto.code} a
        <where>
            <if test="dto.startTime!=null">
                a.date &gt;= #{dto.startTime}
            </if>
            <if test="dto.endTime!=null">
                and a.date &lt;= #{dto.endTime}
            </if>
            and a.lr_lat &gt;= #{dto.southLat}
            <![CDATA[and a.rr_lat <= #{dto.northLat}  ]]>
            and a.lr_lon &lt; a.rr_lon
            and a.rr_lon >= #{dto.westLon}
            and a.lr_lon &lt;= #{dto.eastLon}
        </where>

        union all
        select * from subpoint_${dto.code} b
        <where>
            <if test="dto.startTime!=null">
                b.date &gt;= #{dto.startTime}
            </if>
            <if test="dto.endTime!=null">
                and b.date &lt;= #{dto.endTime}
            </if>
            and b.lr_lat &gt;= #{dto.southLat}
            and b.rr_lat &lt;= #{dto.northLat}
            and b.lr_lon &gt; a.rr_lon
            <choose>
                <when test="dto.westLon lt 0">
                    and b.rr_lon &gt;= #{dto.westLon}
                </when>
                <otherwise>
                    and b.rr_lon &gt;= #{dto.westLon} - 360
                </otherwise>
            </choose>
            <choose>
                <when test="dto.eastLon lt 0">
                    and a.lr_lon &lt;= #{dto.eastLon} + 360
                </when>
                <otherwise>
                    and a.lr_lon &lt;= #{dto.eastLon}
                </otherwise>
            </choose>
        </where>

        union all
        select * from subpoint_${dto.code} c
        <where>
            <if test="dto.startTime!=null">
                c.date &gt;= #{dto.startTime}
            </if>
            <if test="dto.endTime!=null">
                and c.date &lt;= #{dto.endTime}
            </if>
            and c.rr_lat &gt;= #{dto.southLat}
            and c.lr_lat &lt;= #{dto.northLat}
            and c.lr_lon &lt; c.rr_lon
            and c.rr_lon &gt;= #{dto.westLon}
            and c.lr_lon &lt;= #{dto.eastLon}
        </where>

        union all
        select * from subpoint_${dto.code} d
        <where>
            <if test="dto.startTime!=null">
                d.date &gt;= #{dto.startTime}
            </if>
            <if test="dto.endTime!=null">
                and d.date &lt;= #{dto.endTime}
            </if>
            and d.rr_lat &gt;= #{dto.southLat}
            and d.lr_lat &lt;= #{dto.northLat}
            and d.lr_lon &gt; a.rr_lon
            <choose>
                <when test="dto.westLon lt 0">
                    and d.rr_lon &gt;= #{dto.westLon}
                </when>
                <otherwise>
                    and d.rr_lon &gt;= #{dto.westLon} - 360
                </otherwise>
            </choose>
            <choose>
                <when test="dto.eastLon lt 0">
                    and d.lr_lon &lt;= #{dto.eastLon} + 360
                </when>
                <otherwise>
                    and d.lr_lon &lt;= #{dto.eastLon}
                </otherwise>
            </choose>
        </where>
    </select>

    <insert id="batch">
        insert into subpoint_${code}
        (id,date,satellite_id,lat,lon,lr_lat,lr_lon,rr_lat,rr_lon,ls_lat,ls_lon,rs_lat,rs_lon,alt,updown,orbit,near_point_num
        ,r,satellite_code,code)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.id},
            #{item.date},
            #{item.satelliteId},
            #{item.lat},
            #{item.lon},
            #{item.lrLat},
            #{item.lrLon},
            #{item.rrLat},
            #{item.rrLon},
            #{item.lsLat},
            #{item.lsLon},
            #{item.rsLat},
            #{item.rsLon},
            #{item.alt},
            #{item.updown},
            #{item.orbit},
            #{item.nearPointNum},
            #{item.r},
            #{item.satelliteCode},
            #{item.code}
            )
        </foreach>

    </insert>

    <select id="selectSubPointDataListforStrip1" resultType="com.base.track.model.entity.SubPointData">
        select * from subpoint_${code} a
        <where>
            a.updown=#{updownint}
            <if test="starttime!=null">
                and  a.date &gt;= #{starttime}
            </if>
            <if test="endtime!=null">
                and a.date &lt;= #{endtime}
            </if>
            and (a.lr_lat &gt;= #{southlat}
            and a.rr_lat  &lt;= #{northlat})
            and (a.lr_lon &lt; a.rr_lon
            and a.rr_lon >= #{westlonQuery}
            and a.lr_lon &lt;= #{eastlonQuery})
        </where>
        order by a.date asc
    </select>

    <select id="selectSubPointDataListforStrip2" resultType="com.base.track.model.entity.SubPointData">
        select * from subpoint_${code} a
        <where>
            a.updown=#{updownint}
            <if test="starttime!=null">
                and  a.date &gt;= #{starttime}
            </if>
            <if test="endtime!=null">
                and a.date &lt;= #{endtime}
            </if>
            and (a.lr_lat &gt;= #{southlat}
            and a.rr_lat  &lt;= #{northlat})
            and ((a.lr_lon > a.rr_lon)
            and ((a.rr_lon+360.0) >= #{westlonQuery})
            and (a.lr_lon &lt;= #{eastlonQuery}))
        </where>
        order by a.date asc
    </select>

    <select id="selectSubPointDataListforStrip3" resultType="com.base.track.model.entity.SubPointData">
        select * from subpoint_${code} a
        <where>
            a.updown=#{updownint}
            <if test="starttime!=null">
                and  a.date &gt;= #{starttime}
            </if>
            <if test="endtime!=null">
                and a.date &lt;= #{endtime}
            </if>
            and (a.rr_lat &gt;= #{southlat}
            and a.lr_lat  &lt;= #{northlat})
            and (a.lr_lon &lt;a.rr_lon
            and a.rr_lon >= #{westlonQuery}
            and a.lr_lon &lt;= #{eastlonQuery})
        </where>
        order by a.date asc
    </select>

    <select id="selectSubPointDataListforStrip4" resultType="com.base.track.model.entity.SubPointData">
        select * from subpoint_${code} a
        <where>
            a.updown=#{updownint}
            <if test="starttime!=null">
                and  a.date &gt;= #{starttime}
            </if>
            <if test="endtime!=null">
                and a.date &lt;= #{endtime}
            </if>
            and (a.rr_lat &gt;= #{southlat}
            and a.lr_lat  &lt;= #{northlat})
            and ((a.lr_lon > a.rr_lon)
            and ((a.rr_lon+360.0) >= #{westlonQuery})
            and (a.lr_lon &lt;= #{eastlonQuery}))
        </where>
        order by a.date asc
    </select>


</mapper>