<?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="egovframework.service.mapper.TrafficStatsMapper">

	<select id="getVertexHistoryByRoadText" resultType="java.util.HashMap">
		SELECT A.ROAD_ID,
			   --B.ROAD_NM,
			   REPLACE(B.ROAD_NM, ROAD_NM, #{atrdNm}) AS ROAD_NM,
			   B.STRT_NM,
			   B.END_NM,
			   A.STAT_DT,
			   A.SPED,
			   A.TRVL_HH
		FROM
			(SELECT ROAD_ID,
					STAT_DT,
					SPED,
					TRVL_HH
			 FROM

				<if test='sTimeTypeVal == "sTime"'>
					TB_ROAD_STAT_HH WHERE SUBSTR(STAT_DT,0,10) BETWEEN #{yyyymmddhh} AND #{yyyymmddhh2}
				</if>
				<if test='sTimeTypeVal == "sDay"'>
					TB_ROAD_STAT_DD WHERE SUBSTR(STAT_DT,0,8) BETWEEN #{yyyymmddhh} AND #{yyyymmddhh2}
				</if>
				<if test='sTimeTypeVal == "sMonth"'>
					TB_ROAD_STAT_MN WHERE SUBSTR(STAT_DT,0,6) BETWEEN #{yyyymmddhh} AND #{yyyymmddhh2}
				</if>
				<if test='sTimeTypeVal == "sYear"'>
					TB_ROAD_STAT_YY WHERE SUBSTR(STAT_DT,0,4) BETWEEN #{yyyymmddhh} AND #{yyyymmddhh2}
				</if>
			   AND ROAD_ID IN (SELECT
							 	A.ROAD_ID
							 FROM
								 TB_ATRD_RLTN_ROAD A,
								 TB_ATRD B
							 WHERE
								 A.ATRD_ID = B.ATRD_ID
									<if test="atrdNm != null">
										AND	B.ATRD_NM LIKE '%'|| #{atrdNm} ||'%'
									</if>
							 )
			 ORDER BY STAT_DT,ROAD_ID) A, TB_ROAD B
		WHERE A.ROAD_ID = B.ROAD_ID
		ORDER BY ROAD_NM, STAT_DT

	</select>

            <select id="getAtrdNm" resultType="java.util.HashMap">
                SELECT
                    ATRD_ID,
                    ATRD_NM
                FROM
                    TB_ATRD
                WHERE
                    DRCT_CD = '1'
                ORDER BY
                    ATRD_NM
            </select>

            <select id="getVdsCtlrList" resultType="java.util.HashMap">
                SELECT A.LINK_ID,
                       C.NODE_NAME AS STR_NAME,
                       D.NODE_NAME AS END_NAME
                FROM TB_VDS_DTCT_RLTN_LINK A,
                     TB_LINK               B,
                     TB_NODE               C,
                     TB_NODE               D
                WHERE A.LINK_ID   = B.LINK_ID
                  AND B.F_NODE_ID = C.NODE_ID
                  AND B.T_NODE_ID = D.NODE_ID
                GROUP BY A.LINK_ID, C.NODE_NAME, D.NODE_NAME
                ORDER BY A.LINK_ID
        <!--
                SELECT A.LINK_ID,
                       C.NODE_NAME AS STR_NAME,
                       D.NODE_NAME AS END_NAME
                  FROM TB_VDS_DTCT_RLTN_LINK A,
                       TB_LINK               B,
                       TB_NODE               C,
                       TB_NODE               D
                 WHERE A.LINK_ID   = B.LINK_ID
                   AND B.F_NODE = C.NODE_ID
                   AND B.T_NODE = D.NODE_ID
                 GROUP BY A.LINK_ID, C.NODE_NAME, D.NODE_NAME
                 ORDER BY A.LINK_ID
                 -->
    </select>
    
   <select id="getVdsStatsInfo" parameterType="java.util.HashMap" resultType="egovframework.vo.VdsStatsVO">
    	SELECT SUBSTR(STAT_DD,1,4)||'/'||SUBSTR(STAT_DD,5,2)||'/'||SUBSTR(STAT_DD,7,2) STAT_DD, 
		       LISTAGG(STAT_HH,',') WITHIN GROUP(ORDER BY STAT_HH) AS STAT_DAY,
		       LISTAGG(TFVL,   ',') WITHIN GROUP(ORDER BY STAT_HH) AS TFVL
		  FROM (SELECT /*+ INDEX(A PK_VDS_DTCT_HH_STAT) */
		               SUBSTR(A.STAT_DT, 1, 8) AS STAT_DD, SUBSTR(A.STAT_DT, 9, 2) AS STAT_HH,
		               SUM(A.TFVL) AS TFVL 
		          FROM TB_VDS_DTCT_HH_STAT A
		         WHERE A.STAT_DT BETWEEN #{statsStrtDate} AND #{statsEndDate}
		           AND A.VDS_DTCT_NMBR IN (SELECT VDS_DTCT_NMBR
		                                     FROM TB_VDS_DTCT_RLTN_LINK A
		                                    WHERE A.LINK_ID = #{linkId})
		         GROUP BY SUBSTR(A.STAT_DT, 1, 8), SUBSTR(A.STAT_DT, 9, 2)
		       )
		 GROUP BY STAT_DD
    </select>
	
</mapper>