<?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.rse.RseOdStatHhMapper">

    <select id="findAllOdListByDateRange" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.rse.RseOdStatDto">
        <![CDATA[
        SELECT X.RSE_CTLR_NMBR, X.RSE_NM, NVL(Y.DEPART_TFVL, 0) AS DEPART_TFVL, NVL(ARRIVAL_TFVL, 0) AS ARRIVAL_TFVL
        FROM TB_RSE_CTLR X LEFT OUTER JOIN
             (SELECT RSE_CTLR_NMBR, SUM(DEPART_TFVL) AS DEPART_TFVL, SUM(ARRIVAL_TFVL) AS ARRIVAL_TFVL
              FROM (SELECT A.F_RSE_CTLR_NMBR AS RSE_CTLR_NMBR, SUM(TFVL) AS DEPART_TFVL, 0 AS ARRIVAL_TFVL
                    FROM TB_RSE_OD_STAT_HH A
                    WHERE A.STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                    GROUP BY A.F_RSE_CTLR_NMBR
                    UNION
                    SELECT A.T_RSE_CTLR_NMBR AS RSE_CTLR_NMBR, 0 AS DEPART_TFVL, SUM(TFVL) AS ARRIVAL_TFVL
                    FROM TB_RSE_OD_STAT_HH A
                    WHERE A.STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
                    GROUP BY A.T_RSE_CTLR_NMBR
                   )
              GROUP BY RSE_CTLR_NMBR) Y
             ON X.RSE_CTLR_NMBR = Y.RSE_CTLR_NMBR
        ORDER BY X.RSE_CTLR_NMBR
        ]]>
    </select>

    <select id="findAllListByDateRange" parameterType="java.util.HashMap" resultType="com.its.op.dto.its.rse.RseOdStatListDto">
        <![CDATA[
        SELECT A.F_RSE_CTLR_NMBR, A.T_RSE_CTLR_NMBR, SUM(TFVL) AS TFVL
        FROM TB_RSE_OD_STAT_HH A
        WHERE A.STAT_DT BETWEEN #{FROM_DT} AND #{TO_DT}
        GROUP BY A.F_RSE_CTLR_NMBR, A.T_RSE_CTLR_NMBR
        ]]>
    </select>

</mapper>
