aonestar.public

Parameters

Name	Type	Mode
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)

Definition

WITH dataset as (
        Select * from sp_room_avail_by_roomtype($1, $2)
    ),actual AS (
        SELECT adate AS "date", 
               sum(AVL) room_avl, 
               sum(RSV) room_rsv, sum(CFM) room_cfm, sum(INH) room_inh, sum(HSU) room_hsu, sum(OOO) room_ooo, /*TTL-sum(OOI)*/sum(TTL) room_ttl, sum(TTT) room_ttt, 
               sum(OOS) room_oos, sum(OOI) room_ooi
        FROM dataset
        GROUP BY adate--, rm.ttl
        ORDER BY adate
    ), actual_sum AS (
        SELECT sum(room_avl) room_avl, 
               sum(room_rsv) room_rsv, sum(room_cfm) room_cfm, sum(room_inh) room_inh, sum(room_hsu) room_hsu, sum(room_ooo) room_ooo, sum(room_ttl) room_ttl, 
               sum(room_ttt) room_ttt, sum(room_oos) room_oos, sum(room_ooi) room_ooi
        FROM actual              
    ), forecast AS (                                                                                                              -- ((TTL-sum(OOI))
       SELECT adate AS "date", sum(ARR) room_arr, sum(DEP) room_dep, sum(OCC) room_occ, (sum(OCC)*100.00 / sum(TTL) )::t_percent  AS occ_percent, sum(rev) AS revenue
       FROM dataset--, rm
       GROUP BY adate--, TTL    
       ORDER BY adate         
    ), forecast_sum AS (
       SELECT sum(ARR) room_arr, sum(DEP) room_dep, sum(OCC) room_occ, (sum(OCC)*100.00 / (sum(TTL)))::t_percent  AS occ_percent, sum(rev) AS revenue
       FROM dataset
    ) 
    SELECT 
       jsonb_build_array(jsonb_build_object('type','actual', 'name','Actual', 'code', NULL,
             --SELECT 'actual' AS "type", 'Actual' AS "name", NULL AS code, 
               'summary', (SELECT row_to_json(actual_sum) FROM actual_sum),
               'period',  (SELECT jsonb_agg(actual) FROM actual)
       )) 
       || jsonb_build_object('type','forecast', 'name','Forecast', 'code', NULL,
             --SELECT 'actual' AS "type", 'Actual' AS "name", NULL AS code, 
               'summary', (SELECT row_to_json(forecast_sum) FROM forecast_sum),
               'period',  (SELECT jsonb_agg(forecast) FROM forecast)
       ) 
       || jsonb_agg(rm_types)
    FROM (SELECT 'room' AS "type", "name", code, rt.id AS roomtype_id, COALESCE(rt.total_rooms,0) AS room_ttl,
                jsonb_agg(rt_sum)->0 AS summary,
                --jsonb_agg(rt_daily) AS "period"
                (SELECT jsonb_agg(rt_daily) 
                 FROM (SELECT adate AS "date", 
                            RSV+CFM AS room_rsv, INH+HSU AS room_inh, OOO AS room_ooo, OOS AS room_oos, OOI AS room_ooi, AVL AS room_avl, rev AS revenue
                       FROM dataset
                       WHERE roomtype_id = rt.id
                       ORDER BY adate
                      ) AS rt_daily
                ) AS "period"
          FROM room_type rt,
                LATERAL (  
                    SELECT sum(RSV+CFM) AS room_rsv, sum(INH+HSU) AS room_inh, sum(OOO) AS room_ooo, sum(OOS) AS room_oos, sum(OOI) AS room_ooi, sum(AVL) AS room_avl, sum(rev) AS revenue
                    FROM dataset
                    WHERE roomtype_id = rt.id
                ) AS rt_sum
          GROUP BY 1,2,3,4,5
          ORDER BY rt.order_seq, name
        ) AS rm_types

sp_get_room_avail_js

Parameters

Definition