<?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.dashboard.DashboardMapper">

    <select id="findAllCmtrGradCountSttsLink" resultType="com.its.op.dto.its.dashboard.CmtrGradCountSttsDto">
        <![CDATA[
--         select prcn_dt, sum(ltc1) as ltc1, sum(ltc2) as ltc2, sum(ltc3) as ltc3, sum(ltc0) as ltc0,
--                0 as total, 0 as rate_ltc1, 0 as rate_ltc2, 0 as rate_ltc3, 0 as rate_ltc0
--         from (select prcn_dt,
--                      sum(decode(cmtr_grad_cd,'LTC1', 1, 0)) as ltc1,
--                      sum(decode(cmtr_grad_cd,'LTC2', 1, 0)) as ltc2,
--                      sum(decode(cmtr_grad_cd,'LTC3', 1, 0)) as ltc3,
--                      sum(decode(cmtr_grad_cd,'LTC0', 1, 0)) as ltc0
--               from tb_link_traf_hs
--               where prcn_dt >= to_char(sysdate-1/24, 'YYYYMMDDHH24MISS')
--               group by prcn_dt, cmtr_grad_cd
--              )
--         group by prcn_dt
--         order by prcn_dt
        select prcn_dt, LINK_LIGT_NUM as ltc1, LINK_DELY_NUM as ltc2, LINK_CNGS_NUM as ltc3,
               (select count(1) from tb_link where del_yn = 'N') - (LINK_LIGT_NUM + LINK_DELY_NUM + LINK_CNGS_NUM) as ltc0,
               0 as total, 0 as rate_ltc1, 0 as rate_ltc2, 0 as rate_ltc3, 0 as rate_ltc0
        from tb_trfc_prcn_stat
        where prcn_dt >= to_char(sysdate-1/24, 'YYYYMMDDHH24MISS')
        order by prcn_dt
        ]]>
    </select>

    <select id="findAllCmtrGradCountSttsIfsc" resultType="com.its.op.dto.its.dashboard.CmtrGradCountSttsDto">
        <![CDATA[
--         select prcn_dt, sum(ltc1) as ltc1, sum(ltc2) as ltc2, sum(ltc3) as ltc3, sum(ltc0) as ltc0,
--                0 as total, 0 as rate_ltc1, 0 as rate_ltc2, 0 as rate_ltc3, 0 as rate_ltc0
--         from (select prcn_dt,
--                      sum(decode(cmtr_grad_cd,'LTC1', 1, 0)) as ltc1,
--                      sum(decode(cmtr_grad_cd,'LTC2', 1, 0)) as ltc2,
--                      sum(decode(cmtr_grad_cd,'LTC3', 1, 0)) as ltc3,
--                      sum(decode(cmtr_grad_cd,'LTC0', 1, 0)) as ltc0
--               from tb_ifsc_traf_hs
--               where prcn_dt >= to_char(sysdate-1/24, 'YYYYMMDDHH24MISS')
--               group by prcn_dt, cmtr_grad_cd
--              )
--         group by prcn_dt
--         order by prcn_dt
        select prcn_dt, IFSC_LIGT_NUM as ltc1, IFSC_DELY_NUM as ltc2, IFSC_CNGS_NUM as ltc3,
               (select count(1) from tb_ifsc where del_yn = 'N') - (IFSC_LIGT_NUM + IFSC_DELY_NUM + IFSC_CNGS_NUM) as ltc0,
               0 as total, 0 as rate_ltc1, 0 as rate_ltc2, 0 as rate_ltc3, 0 as rate_ltc0
        from tb_trfc_prcn_stat
        where prcn_dt >= to_char(sysdate-1/24, 'YYYYMMDDHH24MISS')
        order by prcn_dt
        ]]>
    </select>

    <select id="findAllCmtrGradCountSttsRoad" resultType="com.its.op.dto.its.dashboard.CmtrGradCountSttsDto">
        <![CDATA[
--         select prcn_dt, sum(ltc1) as ltc1, sum(ltc2) as ltc2, sum(ltc3) as ltc3, sum(ltc0) as ltc0,
--                0 as total, 0 as rate_ltc1, 0 as rate_ltc2, 0 as rate_ltc3, 0 as rate_ltc0
--         from (select prcn_dt,
--                      sum(decode(cmtr_grad_cd,'LTC1', 1, 0)) as ltc1,
--                      sum(decode(cmtr_grad_cd,'LTC2', 1, 0)) as ltc2,
--                      sum(decode(cmtr_grad_cd,'LTC3', 1, 0)) as ltc3,
--                      sum(decode(cmtr_grad_cd,'LTC0', 1, 0)) as ltc0
--               from tb_road_traf_hs
--               where prcn_dt >= to_char(sysdate-1/24, 'YYYYMMDDHH24MISS')
--               group by prcn_dt, cmtr_grad_cd
--              )
--         group by prcn_dt
--         order by prcn_dt
        select prcn_dt, ROAD_LIGT_NUM as ltc1, ROAD_DELY_NUM as ltc2, ROAD_CNGS_NUM as ltc3,
               (select count(1) from tb_road where del_yn = 'N') - (ROAD_LIGT_NUM + ROAD_DELY_NUM + ROAD_CNGS_NUM) as ltc0,
               0 as total, 0 as rate_ltc1, 0 as rate_ltc2, 0 as rate_ltc3, 0 as rate_ltc0
        from tb_trfc_prcn_stat
        where prcn_dt >= to_char(sysdate-1/24, 'YYYYMMDDHH24MISS')
        order by prcn_dt
        ]]>
    </select>

</mapper>
