<?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.analysis.PtrnDataMapper">

    <select id="findAllByDateRangeLINK" parameterType="com.its.op.dto.its.analysis.PtrnDataParam" resultType="com.its.op.dto.its.analysis.PtrnDataDto">
        SELECT MAX(LINK_ID)     AS ID,
               MAX(SECT_NAME)   AS SECT_NAME,
               MAX(LINK_LENG)   AS SECT_LNGT,
               MAX(DAY_TYPE_CD) AS DAY_TYPE_CD,
               MAX(STAT_HM)     AS PTRN_HM,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, SPED,    0)) AS BASE_SPED,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, TRVL_HH, 0)) AS BASE_TRVL_HH,
               MAX(DECODE(STAT_YM, #{cond.COMP_MN}, SPED,    0)) AS COMP_SPED,
               MAX(DECODE(STAT_YM, #{cond.COMP_MN}, TRVL_HH, 0)) AS COMP_TRVL_HH,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, SPED,    0)) - MAX(DECODE(STAT_YM, #{cond.COMP_MN}, SPED,    0)) AS DIFF_SPED,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, TRVL_HH, 0)) - MAX(DECODE(STAT_YM, #{cond.COMP_MN}, TRVL_HH, 0)) AS DIFF_TRVL_HH
        FROM (SELECT B.STAT_YM, A.LINK_ID AS LINK_ID, SECT_NAME, A.LINK_LENG AS LINK_LENG,
                     B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH
              FROM (SELECT T2.LINK_ID, T2.F_NODE_ID, T2.T_NODE_ID, T2.LINK_LENG, T3.NODE_NAME||'-'||T4.NODE_NAME SECT_NAME,
                           T2.SECT_GRAD_CD
                    FROM TB_LINK T2, TB_NODE T3, TB_NODE T4
                    WHERE T2.LINK_ID = TO_NUMBER(#{cond.ID})
                      AND T2.F_NODE_ID  = T3.NODE_ID
                      AND T2.T_NODE_ID  = T4.NODE_ID
                   ) A,
                   (SELECT STAT_YM, LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH
                <if test='cond.TABLE_NAME eq "TB_LINK_PTRN_5M"'>
                    FROM TB_LINK_PTRN_5M A
                </if>
                <if test='cond.TABLE_NAME eq "TB_LINK_PTRN_15M"'>
                    FROM TB_LINK_PTRN_15M A
                </if>
                    WHERE LINK_ID = TO_NUMBER(#{cond.ID})
                      AND STAT_YM IN ( #{cond.BASE_MN}, #{cond.COMP_MN} )
                      AND STAT_HM BETWEEN #{cond.FROM_HM} AND #{cond.TO_HM}
                      AND DAY_TYPE_CD = #{cond.PTRN_WEEK}
                   ) B
              WHERE A.LINK_ID = B.LINK_ID
             )
        GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM
        ORDER BY STAT_HM
    </select>

    <select id="findAllByDateRangeIFSC" parameterType="com.its.op.dto.its.analysis.PtrnDataParam" resultType="com.its.op.dto.its.analysis.PtrnDataDto">
        SELECT MAX(LINK_ID)     AS ID,
               MAX(SECT_NAME)   As SECT_NAME,
               MAX(LINK_LENG)   AS SECT_LNGT,
               MAX(DAY_TYPE_CD) AS DAY_TYPE_CD,
               MAX(STAT_HM)     AS PTRN_HM,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, SPED,    0)) AS BASE_SPED,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, TRVL_HH, 0)) AS BASE_TRVL_HH,
               MAX(DECODE(STAT_YM, #{cond.COMP_MN}, SPED,    0)) AS COMP_SPED,
               MAX(DECODE(STAT_YM, #{cond.COMP_MN}, TRVL_HH, 0)) AS COMP_TRVL_HH,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, SPED,    0)) - MAX(DECODE(STAT_YM, #{cond.COMP_MN}, SPED,    0)) AS DIFF_SPED,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, TRVL_HH, 0)) - MAX(DECODE(STAT_YM, #{cond.COMP_MN}, TRVL_HH, 0)) AS DIFF_TRVL_HH
        FROM (SELECT B.STAT_YM, A.LINK_ID AS LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,
                     B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH
              FROM (SELECT T2.IFSC_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD
                    FROM TB_IFSC T2
                    WHERE T2.IFSC_ID = TO_NUMBER(#{cond.ID})
                   ) A,
                   (SELECT STAT_YM, IFSC_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH
                <if test='cond.TABLE_NAME eq "TB_IFSC_PTRN_5M"'>
                    FROM TB_IFSC_PTRN_5M A
                </if>
                <if test='cond.TABLE_NAME eq "TB_IFSC_PTRN_15M"'>
                    FROM TB_IFSC_PTRN_15M A
                </if>
                    WHERE IFSC_ID = TO_NUMBER(#{cond.ID})
                      AND STAT_YM IN ( #{cond.BASE_MN}, #{cond.COMP_MN} )
                      AND STAT_HM BETWEEN #{cond.FROM_HM} AND #{cond.TO_HM}
                      AND DAY_TYPE_CD = #{cond.PTRN_WEEK}
                   ) B
              WHERE A.LINK_ID = B.LINK_ID
             )
        GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM
        ORDER BY STAT_HM
    </select>

    <select id="findAllByDateRangeROAD" parameterType="com.its.op.dto.its.analysis.PtrnDataParam" resultType="com.its.op.dto.its.analysis.PtrnDataDto">
        SELECT MAX(LINK_ID)     AS ID,
               MAX(SECT_NAME)   AS SECT_NAME,
               MAX(LINK_LENG)   AS SECT_LNGT,
               MAX(DAY_TYPE_CD) AS DAY_TYPE_CD,
               MAX(STAT_HM)     AS PTRN_HM,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, SPED,    0)) AS BASE_SPED,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, TRVL_HH, 0)) AS BASE_TRVL_HH,
               MAX(DECODE(STAT_YM, #{cond.COMP_MN}, SPED,    0)) AS COMP_SPED,
               MAX(DECODE(STAT_YM, #{cond.COMP_MN}, TRVL_HH, 0)) AS COMP_TRVL_HH,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, SPED,    0)) - MAX(DECODE(STAT_YM, #{cond.COMP_MN}, SPED,    0)) AS DIFF_SPED,
               MAX(DECODE(STAT_YM, #{cond.BASE_MN}, TRVL_HH, 0)) - MAX(DECODE(STAT_YM, #{cond.COMP_MN}, TRVL_HH, 0)) AS DIFF_TRVL_HH
        FROM (SELECT B.STAT_YM, A.LINK_ID LINK_ID, SECT_NAME, A.LINK_LENG LINK_LENG,
                     B.DAY_TYPE_CD, B.STAT_HM, B.TFVL, B.SPED, B.OCPY_RATE, B.TRVL_HH
              FROM (SELECT T2.ROAD_ID LINK_ID, T2.SECT_LNGT LINK_LENG, T2.STRT_NM||'-'||T2.END_NM SECT_NAME, T2.SECT_GRAD_CD
                    FROM TB_ROAD T2
                    WHERE T2.ROAD_ID = TO_NUMBER(#{cond.ID})
                   ) A,
                   (SELECT STAT_YM, ROAD_ID LINK_ID, DAY_TYPE_CD, STAT_HM, TFVL, SPED, OCPY_RATE, TRVL_HH
                <if test='cond.TABLE_NAME eq "TB_ROAD_PTRN_5M"'>
                    FROM TB_ROAD_PTRN_5M A
                </if>
                <if test='cond.TABLE_NAME eq "TB_ROAD_PTRN_15M"'>
                    FROM TB_ROAD_PTRN_15M A
                </if>
                    WHERE ROAD_ID = TO_NUMBER(#{cond.ID})
                      AND STAT_YM IN ( #{cond.BASE_MN}, #{cond.COMP_MN} )
                      AND STAT_HM BETWEEN #{cond.FROM_HM} AND #{cond.TO_HM}
                      AND DAY_TYPE_CD = #{cond.PTRN_WEEK}
                   ) B
              WHERE A.LINK_ID = B.LINK_ID
             )
        GROUP BY LINK_ID, DAY_TYPE_CD, STAT_HM
        ORDER BY STAT_HM
    </select>

</mapper>
