<?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.vds.VdsDtctTfvlStatMapper">

    <select id="findAllHourStatByDateRange" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.vds.VdsDtctTfvlHourDto">
       <![CDATA[
        SELECT LY.VDS_CTLR_NMBR, LY.VDS_CTLR_ID, LY.LCTN, LY.VDS_DTCT_NMBR AS DTCT_NMBR, LY.ISTL_LANE, LY.VDS_DTCT_NM,
               LY.DTCT_TYPE, LY.DTCT_TYPE_NM, LX.TRFTYPE, LY.VDS_CTLR_NMBR || '.' || LY.LCTN AS NM,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(TOTTFVL, 0, 0, TOTTFVL)) AS TOTTFVL,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(AVGTFVL, 0, 0, AVGTFVL)) AS AVGTFVL,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L00, NULL, '-', L00)) AS C00,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L01, NULL, '-', L01)) AS C01,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L02, NULL, '-', L02)) AS C02,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L03, NULL, '-', L03)) AS C03,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L04, NULL, '-', L04)) AS C04,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L05, NULL, '-', L05)) AS C05,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L06, NULL, '-', L06)) AS C06,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L07, NULL, '-', L07)) AS C07,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L08, NULL, '-', L08)) AS C08,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L09, NULL, '-', L09)) AS C09,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L10, NULL, '-', L10)) AS C10,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L11, NULL, '-', L11)) AS C11,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L12, NULL, '-', L12)) AS C12,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L13, NULL, '-', L13)) AS C13,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L14, NULL, '-', L14)) AS C14,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L15, NULL, '-', L15)) AS C15,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L16, NULL, '-', L16)) AS C16,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L17, NULL, '-', L17)) AS C17,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L18, NULL, '-', L18)) AS C18,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L19, NULL, '-', L19)) AS C19,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L20, NULL, '-', L20)) AS C20,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L21, NULL, '-', L21)) AS C21,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L22, NULL, '-', L22)) AS C22,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L23, NULL, '-', L23)) AS C23
        FROM (
                 SELECT GROUPING(VDS_DTCT_NMBR) AS GROUPING_VDS_DTCT_NMBR,
                        GROUPING(ISTL_LANE) AS GROUPING_ISTL_LANE,
                        GROUPING(TRFTYPE)   AS GROUPING_TRFTYPE,
                        CASE WHEN GROUPING(VDS_DTCT_NMBR ) = 1 THEN '검지기' ELSE VDS_DTCT_NMBR END AS GROUP_VDS_DTCT_NMBR,
                        CASE WHEN GROUPING(ISTL_LANE)  = 1 THEN '차로'   ELSE ISTL_LANE END AS GROUP_ISTL_LANE,
                        CASE WHEN GROUPING(TRFTYPE)    = 1 THEN '소계'   ELSE TRFTYPE   END AS GROUP_TRFTYPE,
                        VDS_DTCT_NMBR, ISTL_LANE, TRFTYPE,
                        SUM(TFVL)           TOTTFVL,
                        ROUND(AVG(TFVL), 0) AVGTFVL,
                        SUM(DECODE(STATHOUR, '00', TFVL)) AS L00,
                        SUM(DECODE(STATHOUR, '01', TFVL)) AS L01,
                        SUM(DECODE(STATHOUR, '02', TFVL)) AS L02,
                        SUM(DECODE(STATHOUR, '03', TFVL)) AS L03,
                        SUM(DECODE(STATHOUR, '04', TFVL)) AS L04,
                        SUM(DECODE(STATHOUR, '05', TFVL)) AS L05,
                        SUM(DECODE(STATHOUR, '06', TFVL)) AS L06,
                        SUM(DECODE(STATHOUR, '07', TFVL)) AS L07,
                        SUM(DECODE(STATHOUR, '08', TFVL)) AS L08,
                        SUM(DECODE(STATHOUR, '09', TFVL)) AS L09,
                        SUM(DECODE(STATHOUR, '10', TFVL)) AS L10,
                        SUM(DECODE(STATHOUR, '11', TFVL)) AS L11,
                        SUM(DECODE(STATHOUR, '12', TFVL)) AS L12,
                        SUM(DECODE(STATHOUR, '13', TFVL)) AS L13,
                        SUM(DECODE(STATHOUR, '14', TFVL)) AS L14,
                        SUM(DECODE(STATHOUR, '15', TFVL)) AS L15,
                        SUM(DECODE(STATHOUR, '16', TFVL)) AS L16,
                        SUM(DECODE(STATHOUR, '17', TFVL)) AS L17,
                        SUM(DECODE(STATHOUR, '18', TFVL)) AS L18,
                        SUM(DECODE(STATHOUR, '19', TFVL)) AS L19,
                        SUM(DECODE(STATHOUR, '20', TFVL)) AS L20,
                        SUM(DECODE(STATHOUR, '21', TFVL)) AS L21,
                        SUM(DECODE(STATHOUR, '22', TFVL)) AS L22,
                        SUM(DECODE(STATHOUR, '23', TFVL)) AS L23
                 FROM (
                          SELECT TO_CHAR(Y.VDS_DTCT_NMBR) AS VDS_DTCT_NMBR, Y.VDS_DTCT_NM,
                                 TO_CHAR(Y.ISTL_LANE) AS ISTL_LANE, X.TRFTYPE, X.STATHOUR, X.TFVL
                          FROM (SELECT /*+ INDEX(A PK_VDS_DTCT_STAT_HH) */
                                    'DTCT' AS TRFTYPE,
                                    A.VDS_DTCT_NMBR AS VDS_DTCT_NMBR, SUBSTR(A.STAT_DT, 9, 2) AS STATHOUR,
                                    A.TFVL AS TFVL
                                FROM TB_VDS_DTCT_STAT_HH A
                                WHERE 1=1
                                  AND A.STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                               ) X,
                               TB_VDS_DTCT Y
                          WHERE 1=1
                            AND Y.DEL_YN = 'N'
                            AND Y.VDS_DTCT_NMBR = X.VDS_DTCT_NMBR
                      )
                 GROUP BY ROLLUP (VDS_DTCT_NMBR, ISTL_LANE, TRFTYPE)
                 HAVING GROUPING(TRFTYPE) = 0
             ) LX,
             (
                 SELECT A.VDS_CTLR_NMBR AS VDS_CTLR_NMBR, A.VDS_CTLR_ID, A.VDS_NM AS LCTN, B.VDS_DTCT_NMBR,
                        B.ISTL_LANE, B.VDS_DTCT_NM, B.DTCT_DIR AS DTCT_TYPE,
                        (SELECT CMMN_CD_KOR_NM
                         FROM TB_CMMN_CD
                         WHERE CMMN_CLSF_CD = 'ACD'
                           AND CMMN_CD      = B.DTCT_DIR) AS DTCT_TYPE_NM
                 FROM TB_VDS_CTLR A,
                      TB_VDS_DTCT B
                 WHERE 1=1
                   AND A.DEL_YN = 'N'
                   AND B.DEL_YN = 'N'
                   AND A.VDS_CTLR_NMBR = B.VDS_CTLR_NMBR
             ) LY
        WHERE LY.VDS_DTCT_NMBR  = LX.VDS_DTCT_NMBR(+)
        ORDER BY LY.VDS_CTLR_NMBR, LY.VDS_DTCT_NMBR, LY.ISTL_LANE
        ]]>
    </select>

    <select id="findAllDayStatByDateRange" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.vds.VdsDtctTfvlDayDto">
       <![CDATA[
        SELECT LY.VDS_CTLR_NMBR, LY.VDS_CTLR_ID, LY.LCTN, LY.VDS_DTCT_NMBR AS DTCT_NMBR, LY.ISTL_LANE, LY.VDS_DTCT_NM,
               LY.DTCT_TYPE, LY.DTCT_TYPE_NM, LX.TRFTYPE,  LY.VDS_CTLR_NMBR || '.' || LY.LCTN AS NM,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(TOTTFVL, 0, 0, TOTTFVL)) AS TOTTFVL,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(AVGTFVL, 0, 0, AVGTFVL)) AS AVGTFVLX,
               DECODE(NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+
                      NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)+NVL(L13,0)+NVL(L14,0)+
                      NVL(L15,0)+NVL(L16,0)+NVL(L17,0)+NVL(L18,0)+NVL(L19,0)+NVL(L20,0)+NVL(L21,0)+
                      NVL(L22,0)+NVL(L23,0)+NVL(L24,0)+NVL(L25,0)+NVL(L26,0)+NVL(L27,0)+NVL(L28,0)+
                      NVL(L29,0)+NVL(L30,0)+NVL(L31,0), 0, 0,
                      ROUND((NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+
                           NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)+NVL(L13,0)+NVL(L14,0)+
                           NVL(L15,0)+NVL(L16,0)+NVL(L17,0)+NVL(L18,0)+NVL(L19,0)+NVL(L20,0)+NVL(L21,0)+
                           NVL(L22,0)+NVL(L23,0)+NVL(L24,0)+NVL(L25,0)+NVL(L26,0)+NVL(L27,0)+NVL(L28,0)+
                           NVL(L29,0)+NVL(L30,0)+NVL(L31,0)) /
                          (DECODE(L01,NULL,0,1)+DECODE(L02,NULL,0,1)+DECODE(L03,NULL,0,1)+
                           DECODE(L04,NULL,0,1)+DECODE(L05,NULL,0,1)+DECODE(L06,NULL,0,1)+
                           DECODE(L07,NULL,0,1)+DECODE(L08,NULL,0,1)+DECODE(L09,NULL,0,1)+
                           DECODE(L10,NULL,0,1)+DECODE(L11,NULL,0,1)+DECODE(L12,NULL,0,1)+
                           DECODE(L13,NULL,0,1)+DECODE(L14,NULL,0,1)+DECODE(L15,NULL,0,1)+
                           DECODE(L16,NULL,0,1)+DECODE(L17,NULL,0,1)+DECODE(L18,NULL,0,1)+
                           DECODE(L19,NULL,0,1)+DECODE(L20,NULL,0,1)+DECODE(L21,NULL,0,1)+
                           DECODE(L22,NULL,0,1)+DECODE(L23,NULL,0,1)+DECODE(L24,NULL,0,1)+
                           DECODE(L25,NULL,0,1)+DECODE(L26,NULL,0,1)+DECODE(L27,NULL,0,1)+
                           DECODE(L28,NULL,0,1)+DECODE(L29,NULL,0,1)+DECODE(L30,NULL,0,1)+
                           DECODE(L31,NULL,0,1)), 0)) AS AVGTFVL,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L01, NULL, '-', L01)) AS C01,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L02, NULL, '-', L02)) AS C02,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L03, NULL, '-', L03)) AS C03,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L04, NULL, '-', L04)) AS C04,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L05, NULL, '-', L05)) AS C05,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L06, NULL, '-', L06)) AS C06,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L07, NULL, '-', L07)) AS C07,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L08, NULL, '-', L08)) AS C08,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L09, NULL, '-', L09)) AS C09,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L10, NULL, '-', L10)) AS C10,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L11, NULL, '-', L11)) AS C11,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L12, NULL, '-', L12)) AS C12,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L13, NULL, '-', L13)) AS C13,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L14, NULL, '-', L14)) AS C14,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L15, NULL, '-', L15)) AS C15,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L16, NULL, '-', L16)) AS C16,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L17, NULL, '-', L17)) AS C17,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L18, NULL, '-', L18)) AS C18,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L19, NULL, '-', L19)) AS C19,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L20, NULL, '-', L20)) AS C20,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L21, NULL, '-', L21)) AS C21,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L22, NULL, '-', L22)) AS C22,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L23, NULL, '-', L23)) AS C23,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L24, NULL, '-', L24)) AS C24,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L25, NULL, '-', L25)) AS C25,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L26, NULL, '-', L26)) AS C26,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L27, NULL, '-', L27)) AS C27,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L28, NULL, '-', L28)) AS C28,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L29, NULL, '-', L29)) AS C29,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L30, NULL, '-', L30)) AS C30,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L31, NULL, '-', L31)) AS C31
        FROM (
                 SELECT GROUPING(VDS_DTCT_NMBR) AS GROUPING_VDS_DTCT_NMBR,
                        GROUPING(ISTL_LANE) AS GROUPING_ISTL_LANE,
                        GROUPING(TRFTYPE)   AS GROUPING_TRFTYPE,
                        CASE WHEN GROUPING(VDS_DTCT_NMBR ) = 1 THEN '검지기' ELSE VDS_DTCT_NMBR END AS GROUP_VDS_DTCT_NMBR,
                        CASE WHEN GROUPING(ISTL_LANE)  = 1 THEN '차로'   ELSE ISTL_LANE END AS GROUP_ISTL_LANE,
                        CASE WHEN GROUPING(TRFTYPE)    = 1 THEN '소계'   ELSE TRFTYPE   END AS GROUP_TRFTYPE,
                        VDS_DTCT_NMBR, ISTL_LANE, TRFTYPE,
                        SUM(TFVL)           TOTTFVL,
                        ROUND(AVG(TFVL), 0) AVGTFVL,
                        SUM(DECODE(STATDAY, '01', TFVL)) AS L01,
                        SUM(DECODE(STATDAY, '02', TFVL)) AS L02,
                        SUM(DECODE(STATDAY, '03', TFVL)) AS L03,
                        SUM(DECODE(STATDAY, '04', TFVL)) AS L04,
                        SUM(DECODE(STATDAY, '05', TFVL)) AS L05,
                        SUM(DECODE(STATDAY, '06', TFVL)) AS L06,
                        SUM(DECODE(STATDAY, '07', TFVL)) AS L07,
                        SUM(DECODE(STATDAY, '08', TFVL)) AS L08,
                        SUM(DECODE(STATDAY, '09', TFVL)) AS L09,
                        SUM(DECODE(STATDAY, '10', TFVL)) AS L10,
                        SUM(DECODE(STATDAY, '11', TFVL)) AS L11,
                        SUM(DECODE(STATDAY, '12', TFVL)) AS L12,
                        SUM(DECODE(STATDAY, '13', TFVL)) AS L13,
                        SUM(DECODE(STATDAY, '14', TFVL)) AS L14,
                        SUM(DECODE(STATDAY, '15', TFVL)) AS L15,
                        SUM(DECODE(STATDAY, '16', TFVL)) AS L16,
                        SUM(DECODE(STATDAY, '17', TFVL)) AS L17,
                        SUM(DECODE(STATDAY, '18', TFVL)) AS L18,
                        SUM(DECODE(STATDAY, '19', TFVL)) AS L19,
                        SUM(DECODE(STATDAY, '20', TFVL)) AS L20,
                        SUM(DECODE(STATDAY, '21', TFVL)) AS L21,
                        SUM(DECODE(STATDAY, '22', TFVL)) AS L22,
                        SUM(DECODE(STATDAY, '23', TFVL)) AS L23,
                        SUM(DECODE(STATDAY, '24', TFVL)) AS L24,
                        SUM(DECODE(STATDAY, '25', TFVL)) AS L25,
                        SUM(DECODE(STATDAY, '26', TFVL)) AS L26,
                        SUM(DECODE(STATDAY, '27', TFVL)) AS L27,
                        SUM(DECODE(STATDAY, '28', TFVL)) AS L28,
                        SUM(DECODE(STATDAY, '29', TFVL)) AS L29,
                        SUM(DECODE(STATDAY, '30', TFVL)) AS L30,
                        SUM(DECODE(STATDAY, '31', TFVL)) AS L31
                 FROM (
                          SELECT TO_CHAR(Y.VDS_DTCT_NMBR) AS VDS_DTCT_NMBR, Y.VDS_DTCT_NM,
                                 TO_CHAR(Y.ISTL_LANE) AS ISTL_LANE,  X.TRFTYPE, X.STATDAY, X.TFVL
                          FROM (SELECT /*+ INDEX(A PK_VDS_DTCT_STAT_HH) */
                                    'DTCT' AS TRFTYPE,
                                    A.VDS_DTCT_NMBR AS VDS_DTCT_NMBR, SUBSTR(A.STAT_DT, 7, 2) AS STATDAY,
                                    A.TFVL AS TFVL
                                FROM TB_VDS_DTCT_STAT_HH A
                                WHERE 1=1
                                  AND A.STAT_DT               BETWEEN #{FROM_DT} AND #{TO_DT}
                                  AND SUBSTR(A.STAT_DT, 9, 4) BETWEEN #{FROM_HM} AND #{TO_HM}
                               ) X,
                               TB_VDS_DTCT Y
                          WHERE 1=1
                            AND Y.DEL_YN = 'N'
                            AND Y.VDS_DTCT_NMBR = X.VDS_DTCT_NMBR
                      )
                 GROUP BY ROLLUP (VDS_DTCT_NMBR, ISTL_LANE, TRFTYPE)
                 HAVING GROUPING(TRFTYPE) = 0
             ) LX,
             (
                 SELECT A.VDS_CTLR_NMBR AS VDS_CTLR_NMBR, A.VDS_CTLR_ID, A.VDS_NM AS LCTN, B.VDS_DTCT_NMBR,
                        B.ISTL_LANE, B.VDS_DTCT_NM, B.DTCT_DIR AS DTCT_TYPE,
                        (SELECT CMMN_CD_KOR_NM
                         FROM TB_CMMN_CD
                         WHERE CMMN_CLSF_CD = 'ACD'
                           AND CMMN_CD      = B.DTCT_DIR) AS DTCT_TYPE_NM
                 FROM TB_VDS_CTLR A,
                      TB_VDS_DTCT B
                 WHERE 1=1
                   AND A.DEL_YN = 'N'
                   AND B.DEL_YN = 'N'
                   AND A.VDS_CTLR_NMBR = B.VDS_CTLR_NMBR
             ) LY
        WHERE LY.VDS_DTCT_NMBR  = LX.VDS_DTCT_NMBR(+)
        ORDER BY LY.VDS_CTLR_NMBR, LY.VDS_DTCT_NMBR, LY.ISTL_LANE
        ]]>
    </select>

    <select id="findAllMonthStatByDateRange" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.vds.VdsDtctTfvlMonthDto">
       <![CDATA[
        SELECT LY.VDS_CTLR_NMBR, LY.VDS_CTLR_ID, LY.LCTN, LY.VDS_DTCT_NMBR, LY.ISTL_LANE, LY.VDS_DTCT_NM,
               LY.DTCT_TYPE, LY.DTCT_TYPE_NM, LX.TRFTYPE,  LY.VDS_CTLR_NMBR || '.' || LY.LCTN AS NM,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(TOTTFVL, 0, 0, TOTTFVL)) AS TOTTFVL,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(AVGTFVL, 0, 0, AVGTFVL)) AS AVGTFVLX,
               DECODE(NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+
                      NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0), 0, 0,
                      ROUND((NVL(L01,0)+NVL(L02,0)+NVL(L03,0)+NVL(L04,0)+NVL(L05,0)+NVL(L06,0)+NVL(L07,0)+ NVL(L08,0)+NVL(L09,0)+NVL(L10,0)+NVL(L11,0)+NVL(L12,0)) /
                            (DECODE(L01,NULL,0,1)+DECODE(L02,NULL,0,1)+DECODE(L03,NULL,0,1)+
                             DECODE(L04,NULL,0,1)+DECODE(L05,NULL,0,1)+DECODE(L06,NULL,0,1)+
                             DECODE(L07,NULL,0,1)+DECODE(L08,NULL,0,1)+DECODE(L09,NULL,0,1)+
                             DECODE(L10,NULL,0,1)+DECODE(L11,NULL,0,1)+DECODE(L12,NULL,0,1)), 0)) AS AVGTFVL,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L01, NULL, '-', L01)) AS C01,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L02, NULL, '-', L02)) AS C02,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L03, NULL, '-', L03)) AS C03,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L04, NULL, '-', L04)) AS C04,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L05, NULL, '-', L05)) AS C05,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L06, NULL, '-', L06)) AS C06,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L07, NULL, '-', L07)) AS C07,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L08, NULL, '-', L08)) AS C08,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L09, NULL, '-', L09)) AS C09,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L10, NULL, '-', L10)) AS C10,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L11, NULL, '-', L11)) AS C11,
               DECODE(LX.VDS_DTCT_NMBR, NULL, '-', DECODE(L12, NULL, '-', L12)) AS C12
        FROM (
                 SELECT GROUPING(VDS_DTCT_NMBR) AS GROUPING_VDS_DTCT_NMBR,
                        GROUPING(ISTL_LANE) AS GROUPING_ISTL_LANE,
                        GROUPING(TRFTYPE)   AS GROUPING_TRFTYPE,
                        CASE WHEN GROUPING(VDS_DTCT_NMBR ) = 1 THEN '검지기' ELSE VDS_DTCT_NMBR END AS GROUP_VDS_DTCT_NMBR,
                        CASE WHEN GROUPING(ISTL_LANE)  = 1 THEN '차로'   ELSE ISTL_LANE END AS GROUP_ISTL_LANE,
                        CASE WHEN GROUPING(TRFTYPE)    = 1 THEN '소계'   ELSE TRFTYPE   END AS GROUP_TRFTYPE,
                        VDS_DTCT_NMBR, ISTL_LANE, TRFTYPE,
                        SUM(TFVL)           TOTTFVL,
                        ROUND(AVG(TFVL), 0) AVGTFVL,
                        SUM(DECODE(STATDAY, '01', TFVL)) AS L01,
                        SUM(DECODE(STATDAY, '02', TFVL)) AS L02,
                        SUM(DECODE(STATDAY, '03', TFVL)) AS L03,
                        SUM(DECODE(STATDAY, '04', TFVL)) AS L04,
                        SUM(DECODE(STATDAY, '05', TFVL)) AS L05,
                        SUM(DECODE(STATDAY, '06', TFVL)) AS L06,
                        SUM(DECODE(STATDAY, '07', TFVL)) AS L07,
                        SUM(DECODE(STATDAY, '08', TFVL)) AS L08,
                        SUM(DECODE(STATDAY, '09', TFVL)) AS L09,
                        SUM(DECODE(STATDAY, '10', TFVL)) AS L10,
                        SUM(DECODE(STATDAY, '11', TFVL)) AS L11,
                        SUM(DECODE(STATDAY, '12', TFVL)) AS L12
                 FROM (
                          SELECT TO_CHAR(Y.VDS_DTCT_NMBR) AS VDS_DTCT_NMBR, Y.VDS_DTCT_NM,
                                 TO_CHAR(Y.ISTL_LANE) AS ISTL_LANE,  X.TRFTYPE, X.STATDAY, X.TFVL
                          FROM (SELECT /*+ INDEX(A PK_VDS_DTCT_MN_STAT) */
                                    'DTCT' AS TRFTYPE,
                                    A.VDS_DTCT_NMBR AS VDS_DTCT_NMBR, SUBSTR(A.STAT_DT, 5, 2) AS STATDAY,
                                    A.TFVL AS TFVL
                                FROM TB_VDS_DTCT_STAT_MN A
                                WHERE 1=1
                                  AND A.STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                               ) X,
                               TB_VDS_DTCT Y
                          WHERE 1=1
                            AND Y.DEL_YN = 'N'
                            AND Y.VDS_DTCT_NMBR = X.VDS_DTCT_NMBR
                      )
                 GROUP BY ROLLUP (VDS_DTCT_NMBR, ISTL_LANE, TRFTYPE)
                 HAVING GROUPING(TRFTYPE) = 0
             ) LX,
             (
                 SELECT A.VDS_CTLR_NMBR AS VDS_CTLR_NMBR, A.VDS_CTLR_ID, A.VDS_NM AS LCTN, B.VDS_DTCT_NMBR,
                        B.ISTL_LANE, B.VDS_DTCT_NM, B.DTCT_DIR AS DTCT_TYPE,
                        (SELECT CMMN_CD_KOR_NM
                         FROM TB_CMMN_CD
                         WHERE CMMN_CLSF_CD = 'ACD'
                           AND CMMN_CD      = B.DTCT_DIR) AS DTCT_TYPE_NM
                 FROM TB_VDS_CTLR A,
                      TB_VDS_DTCT B
                 WHERE 1=1
                   AND A.DEL_YN = 'N'
                   AND B.DEL_YN = 'N'
                   AND A.VDS_CTLR_NMBR = B.VDS_CTLR_NMBR
             ) LY
        WHERE LY.VDS_DTCT_NMBR  = LX.VDS_DTCT_NMBR(+)
        ORDER BY LY.VDS_CTLR_NMBR, LY.VDS_DTCT_NMBR, LY.ISTL_LANE
        ]]>
    </select>

</mapper>
