<?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="kr.gmtc.gw.elastic.mapper.AeraMapper">


    <resultMap id="area" type="kr.gmtc.gw.elastic.vo.AnalyzeArea.Area" >
		<result property="id" column="AREA_ID" />
		<result property="name" column="AREA_NM" />
		<result property="areatype" column="AREA_TY" />
		<collection property="points" resultMap="point" />
	</resultMap>
	
	<resultMap id="point" type="kr.gmtc.gw.elastic.vo.AnalyzeArea.AreaPoint" >
		<result property="sn" column="SPOT_SN" />
		<result property="lon" column="LON" />
		<result property="lat" column="LAT" />
	</resultMap>


    <select id="selectAeraListMap" resultType="hashMap">
        SELECT M.AREA_ID, M.AREA_NM, M.AREA_TY, 
                ROW_NUMBER() OVER (PARTITION BY D.AREA_ID ORDER BY D.SPOT_SN) SPOT_SN  ,
                D.LON , D.LAT,
                TO_CHAR(D.LON) AS LON_STR,
                TO_CHAR(D.LAT) AS LAT_STR
            FROM UCM.SACP_AREA_MASTR M,
                UCM.SACP_AREA_DETAIL D
        WHERE AREA_TY = 'AF07001'
        AND AREA_NM NOT LIKE 'taxiway%'
        AND AREA_NM NOT LIKE 'TaxiLine%'  
        AND M.AREA_ID = D.AREA_ID (+)
        ORDER BY AREA_ID, SPOT_SN
    </select>


    <select id="selectAeraList" resultMap="area" >
        SELECT M.AREA_ID, M.AREA_NM, M.AREA_TY, 
                ROW_NUMBER() OVER (PARTITION BY D.AREA_ID ORDER BY D.SPOT_SN) SPOT_SN  ,
                D.LON , D.LAT,
                TO_CHAR(D.LON) AS LON_STR,
                TO_CHAR(D.LAT) AS LAT_STR
            FROM UCM.SACP_AREA_MASTR M,
                UCM.SACP_AREA_DETAIL D
        WHERE AREA_TY = 'AF07001'
        AND AREA_NM NOT LIKE 'taxiway%'
        AND AREA_NM NOT LIKE 'TaxiLine%'  
        AND M.AREA_ID = D.AREA_ID (+)
        ORDER BY AREA_ID, SPOT_SN
    </select>
    
    
     

</mapper>