<?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.its.op.dao.mapper.its.statistics.TrafficTermStatisticsMapper">

    <select id="findAllLink15M" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.LINK_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.LINK_LENG AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT LINK_ID,
                     SUBSTR(STAT_DT, 1, 12) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 12), 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_LINK_STAT_15M A
              WHERE LINK_ID = TO_NUMBER(#{LINK_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_LINK B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.LINK_ID = B.LINK_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllLinkHH" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.LINK_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.LINK_LENG AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT LINK_ID,
                     SUBSTR(STAT_DT, 1, 12) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 12), 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_LINK_STAT_HH A
              WHERE LINK_ID = TO_NUMBER(#{LINK_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_LINK B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.LINK_ID = B.LINK_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllLinkDD" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.LINK_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD HH24') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.LINK_LENG AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT LINK_ID,
                     SUBSTR(STAT_DT, 1, 10) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 10), 'YYYY-MM-DD HH24') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_LINK_STAT_DD A
              WHERE LINK_ID = TO_NUMBER(#{LINK_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_LINK B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.LINK_ID = B.LINK_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllLinkMN" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.LINK_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.LINK_LENG AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT LINK_ID,
                     SUBSTR(STAT_DT, 1, 8) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 8), 'YYYY-MM-DD') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_LINK_STAT_MN A
              WHERE LINK_ID = TO_NUMBER(#{LINK_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_LINK B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.LINK_ID = B.LINK_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllLinkYY" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.LINK_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.LINK_LENG AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT LINK_ID,
                     SUBSTR(STAT_DT, 1, 6) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 6), 'YYYY-MM') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_LINK_STAT_YY A
              WHERE LINK_ID = TO_NUMBER(#{LINK_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_LINK B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.LINK_ID = B.LINK_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllIfsc15M" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.IFSC_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT IFSC_ID,
                     SUBSTR(STAT_DT, 1, 12) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 12), 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_IFSC_STAT_15M A
              WHERE IFSC_ID = TO_NUMBER(#{IFSC_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_IFSC B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.IFSC_ID = B.IFSC_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllIfscHH" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.IFSC_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT IFSC_ID,
                     SUBSTR(STAT_DT, 1, 12) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 12), 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_IFSC_STAT_HH A
              WHERE IFSC_ID = TO_NUMBER(#{IFSC_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_IFSC B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.IFSC_ID = B.IFSC_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllIfscDD" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.IFSC_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD HH24') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT IFSC_ID,
                     SUBSTR(STAT_DT, 1, 10) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 10), 'YYYY-MM-DD HH24') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_IFSC_STAT_DD A
              WHERE IFSC_ID = TO_NUMBER(#{IFSC_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_IFSC B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.IFSC_ID = B.IFSC_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllIfscMN" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.IFSC_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT IFSC_ID,
                     SUBSTR(STAT_DT, 1, 8) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 8), 'YYYY-MM-DD') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_IFSC_STAT_MN A
              WHERE IFSC_ID = TO_NUMBER(#{IFSC_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_IFSC B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.IFSC_ID = B.IFSC_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllIfscYY" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.IFSC_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT IFSC_ID,
                     SUBSTR(STAT_DT, 1, 6) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 6), 'YYYY-MM') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_IFSC_STAT_YY A
              WHERE IFSC_ID = TO_NUMBER(#{IFSC_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_IFSC B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.IFSC_ID = B.IFSC_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllRoad15M" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.ROAD_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT ROAD_ID,
                     SUBSTR(STAT_DT, 1, 12) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 12), 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_ROAD_STAT_15M A
              WHERE ROAD_ID = TO_NUMBER(#{ROAD_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_ROAD B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.ROAD_ID = B.ROAD_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllRoadHH" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.ROAD_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT ROAD_ID,
                     SUBSTR(STAT_DT, 1, 12) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 12), 'YYYY-MM-DD HH24:MI') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_ROAD_STAT_HH A
              WHERE ROAD_ID = TO_NUMBER(#{ROAD_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_ROAD B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.ROAD_ID = B.ROAD_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllRoadDD" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.ROAD_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD HH24') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT ROAD_ID,
                     SUBSTR(STAT_DT, 1, 10) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 10), 'YYYY-MM-DD HH24') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_ROAD_STAT_DD A
              WHERE ROAD_ID = TO_NUMBER(#{ROAD_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_ROAD B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.ROAD_ID = B.ROAD_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllRoadMN" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.ROAD_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM-DD') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT ROAD_ID,
                     SUBSTR(STAT_DT, 1, 8) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 8), 'YYYY-MM-DD') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_ROAD_STAT_MN A
              WHERE ROAD_ID = TO_NUMBER(#{ROAD_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_ROAD B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.ROAD_ID = B.ROAD_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllRoadYY" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.TrafficStatDto">
        SELECT A.ROAD_ID AS ID, A.STAT_DT, TO_CHAR(A.STAT_DT_FMT, 'YYYY-MM') AS STAT_DT_FMT,
               NVL(TRIM(C.NODE_NAME), '값없음') AS F_NAME, NVL(TRIM(D.NODE_NAME), '값없음') AS T_NAME,
               B.SECT_LNGT AS SECT_LNGT,
               A.TFVL, A.SPED, A.OCPY_RATE, A.TRVL_HH
        FROM (SELECT ROAD_ID,
                     SUBSTR(STAT_DT, 1, 6) AS STAT_DT,
                     TO_DATE(SUBSTR(STAT_DT, 1, 6), 'YYYY-MM') AS STAT_DT_FMT,
                     TFVL, SPED, OCPY_RATE, TRVL_HH
              FROM TB_ROAD_STAT_YY A
              WHERE ROAD_ID = TO_NUMBER(#{ROAD_ID})
                AND STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
             ) A,
             TB_ROAD B, TB_NODE C, TB_NODE D
        WHERE B.DEL_YN = 'N'
          AND A.ROAD_ID = B.ROAD_ID
          AND B.F_NODE_ID = C.NODE_ID(+)
          AND B.T_NODE_ID = D.NODE_ID(+)
        ORDER BY A.STAT_DT
    </select>

</mapper>
