<?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.IncidentStatisticsMapper">

    <select id="findAllTypeDD" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.IncidentTypeStatDto">
        SELECT NVL(STAT_DT,      '전체') AS STAT_DT,
               NVL(DATA_CNT,       0) AS DATA_CNT,
               NVL(STAT_DTNM,    '전체') AS STAT_DT_FMT,
               DECODE(STAT_DT, NULL, '-', NVL(INCD_TYPE_CD, '소계')) AS INCD_TYPE_CD,
               NVL(INCD_OCRR_NUM,  0) AS INCD_OCRR_NUM,
               NVL(FTLT_PNUM,      0) AS FTLT_PNUM,
               NVL(INJR_PNUM,      0) AS INJR_PNUM,
               NVL(PRPR_DMGE_AMT,  0) AS PRPR_DMGE_AMT
        FROM (
                 SELECT SUBSTR(STAT_DT, 1, 8) AS STAT_DT, COUNT(1) AS DATA_CNT,
                        TO_CHAR(TO_DATE(SUBSTR(STAT_DT, 1, 8), 'YYYYMMDD'), 'YYYY-MM-DD') AS STAT_DTNM,
                        INCD_TYPE_CD AS INCD_TYPE_CD,
                        SUM(INCD_OCRR_NUM) AS INCD_OCRR_NUM,
                        SUM(FTLT_PNUM)     AS FTLT_PNUM,
                        SUM(INJR_PNUM)     AS INJR_PNUM,
                        SUM(PRPR_DMGE_AMT) AS PRPR_DMGE_AMT
                 FROM TB_INCD_TYPE_STAT_DD
                 WHERE STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 8), INCD_TYPE_CD)
                 ORDER BY STAT_DT
             )
    </select>

    <select id="findAllTypeMN" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.IncidentTypeStatDto">
        SELECT NVL(STAT_DT,      '전체') AS STAT_DT,
               NVL(DATA_CNT,       0) AS DATA_CNT,
               NVL(STAT_DTNM,    '전체') AS STAT_DT_FMT,
               DECODE(STAT_DT, NULL, '-', NVL(INCD_TYPE_CD, '소계')) AS INCD_TYPE_CD,
               NVL(INCD_OCRR_NUM,  0) AS INCD_OCRR_NUM,
               NVL(FTLT_PNUM,      0) AS FTLT_PNUM,
               NVL(INJR_PNUM,      0) AS INJR_PNUM,
               NVL(PRPR_DMGE_AMT,  0) AS PRPR_DMGE_AMT
        FROM (
                 SELECT SUBSTR(STAT_DT, 1, 6) AS STAT_DT, COUNT(1) AS DATA_CNT,
                        TO_CHAR(TO_DATE(SUBSTR(STAT_DT, 1, 6), 'YYYYMM'), 'YYYY-MM') AS STAT_DTNM,
                        INCD_TYPE_CD AS INCD_TYPE_CD,
                        SUM(INCD_OCRR_NUM) AS INCD_OCRR_NUM,
                        SUM(FTLT_PNUM)     AS FTLT_PNUM,
                        SUM(INJR_PNUM)     AS INJR_PNUM,
                        SUM(PRPR_DMGE_AMT) AS PRPR_DMGE_AMT
                 FROM TB_INCD_TYPE_STAT_MN
                 WHERE STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 6), INCD_TYPE_CD)
                 ORDER BY STAT_DT
             )
    </select>

    <select id="findAllTypeYY" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.IncidentTypeStatDto">
        SELECT NVL(STAT_DT,      '전체') AS STAT_DT,
               NVL(DATA_CNT,       0) AS DATA_CNT,
               NVL(STAT_DTNM,    '전체') AS STAT_DT_FMT,
               DECODE(STAT_DT, NULL, '-', NVL(INCD_TYPE_CD, '소계')) AS INCD_TYPE_CD,
               NVL(INCD_OCRR_NUM,  0) AS INCD_OCRR_NUM,
               NVL(FTLT_PNUM,      0) AS FTLT_PNUM,
               NVL(INJR_PNUM,      0) AS INJR_PNUM,
               NVL(PRPR_DMGE_AMT,  0) AS PRPR_DMGE_AMT
        FROM (
                 SELECT SUBSTR(STAT_DT, 1, 4) AS STAT_DT, COUNT(1) AS DATA_CNT,
                        SUBSTR(STAT_DT, 1, 4) AS STAT_DTNM,
                        INCD_TYPE_CD AS INCD_TYPE_CD,
                        SUM(INCD_OCRR_NUM) AS INCD_OCRR_NUM,
                        SUM(FTLT_PNUM) AS FTLT_PNUM,
                        SUM(INJR_PNUM) AS INJR_PNUM,
                        SUM(PRPR_DMGE_AMT) AS PRPR_DMGE_AMT
                 FROM TB_INCD_TYPE_STAT_MN
                 WHERE STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 4), INCD_TYPE_CD)
                 ORDER BY STAT_DT
             )
    </select>


    <select id="findAllSectDD" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.IncidentSectStatDto">
        SELECT NVL(A.STAT_DT,      '전체') AS STAT_DT,
               NVL(A.DATA_CNT,       0) AS DATA_CNT,
               NVL(A.STAT_DTNM,    '전체') AS STAT_DT_FMT,
               NVL(A.LINK_ID,        0) AS LINK_ID,
               '[' || B.F_NODE || '] ' || C.NODE_NAME AS F_NAME,
               '[' || B.T_NODE || '] ' || D.NODE_NAME AS T_NAME,
               NVL(A.INCD_OCRR_NUM,  0) AS INCD_OCRR_NUM,
               NVL(A.FTLT_PNUM,      0) AS FTLT_PNUM,
               NVL(A.INJR_PNUM,      0) AS INJR_PNUM,
               NVL(A.PRPR_DMGE_AMT,  0) AS PRPR_DMGE_AMT
        FROM (
                 SELECT SUBSTR(STAT_DT, 1, 8) AS STAT_DT, COUNT(1) AS DATA_CNT,
                        TO_CHAR(TO_DATE(SUBSTR(STAT_DT, 1, 8), 'YYYYMMDD'), 'YYYY-MM-DD') AS STAT_DTNM,
                        LINK_ID AS LINK_ID,
                        SUM(INCD_OCRR_NUM) AS INCD_OCRR_NUM,
                        SUM(FTLT_PNUM)     AS FTLT_PNUM,
                        SUM(INJR_PNUM)     AS INJR_PNUM,
                        SUM(PRPR_DMGE_AMT) AS PRPR_DMGE_AMT
                 FROM TB_INCD_SECT_STAT_DD
                 WHERE STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 8), LINK_ID)
             ) 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
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllSectMN" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.IncidentSectStatDto">
        SELECT NVL(A.STAT_DT,      '전체') AS STAT_DT,
               NVL(A.DATA_CNT,       0) AS DATA_CNT,
               NVL(A.STAT_DTNM,    '전체') AS STAT_DT_FMT,
               '[' || B.F_NODE || '] ' || C.NODE_NAME AS F_NAME,
               '[' || B.T_NODE || '] ' || D.NODE_NAME AS T_NAME,
               NVL(A.LINK_ID,        0) AS LINK_ID,
               NVL(A.INCD_OCRR_NUM,  0) AS INCD_OCRR_NUM,
               NVL(A.FTLT_PNUM,      0) AS FTLT_PNUM,
               NVL(A.INJR_PNUM,      0) AS INJR_PNUM,
               NVL(A.PRPR_DMGE_AMT,  0) AS PRPR_DMGE_AMT
        FROM (
                 SELECT SUBSTR(STAT_DT, 1, 6) AS STAT_DT, COUNT(1) AS DATA_CNT,
                        TO_CHAR(TO_DATE(SUBSTR(STAT_DT, 1, 6), 'YYYYMM'), 'YYYY-MM') AS STAT_DTNM,
                        LINK_ID AS LINK_ID,
                        SUM(INCD_OCRR_NUM) AS INCD_OCRR_NUM,
                        SUM(FTLT_PNUM)     AS FTLT_PNUM,
                        SUM(INJR_PNUM)     AS INJR_PNUM,
                        SUM(PRPR_DMGE_AMT) AS PRPR_DMGE_AMT
                 FROM TB_INCD_SECT_STAT_MN
                 WHERE STAT_DT BETWEEN '20201201000000' AND '20211201000000'
                 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 6), LINK_ID)
             ) 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
        ORDER BY A.STAT_DT
    </select>

    <select id="findAllSectYY" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.statistics.IncidentSectStatDto">
        SELECT NVL(A.STAT_DT,      '전체') AS STAT_DT,
               NVL(A.DATA_CNT,       0) AS DATA_CNT,
               NVL(A.STAT_DTNM,    '전체') AS STAT_DT_FMT,
               NVL(A.LINK_ID,        0) AS LINK_ID,
               '[' || B.F_NODE || '] ' || C.NODE_NAME AS F_NAME,
               '[' || B.T_NODE || '] ' || D.NODE_NAME AS T_NAME,
               NVL(A.INCD_OCRR_NUM,  0) AS INCD_OCRR_NUM,
               NVL(A.FTLT_PNUM,      0) AS FTLT_PNUM,
               NVL(A.INJR_PNUM,      0) AS INJR_PNUM,
               NVL(A.PRPR_DMGE_AMT,  0) AS PRPR_DMGE_AMT
        FROM (
                 SELECT SUBSTR(STAT_DT, 1, 4) AS STAT_DT, COUNT(1) AS DATA_CNT,
                        SUBSTR(STAT_DT, 1, 4) AS STAT_DTNM,
                        LINK_ID AS LINK_ID,
                        SUM(INCD_OCRR_NUM) AS INCD_OCRR_NUM,
                        SUM(FTLT_PNUM) AS FTLT_PNUM,
                        SUM(INJR_PNUM) AS INJR_PNUM,
                        SUM(PRPR_DMGE_AMT) AS PRPR_DMGE_AMT
                 FROM TB_INCD_SECT_STAT_MN
                 WHERE STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                 GROUP BY ROLLUP(SUBSTR(STAT_DT, 1, 4), LINK_ID)
             ) 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
        ORDER BY A.STAT_DT
    </select>

</mapper>
