aonestar.public

Parameters

Name	Type	Mode
ayear	integer	IN
amonth	integer	IN
occ_type	text	IN (DEFAULT 'overall')

Definition

DECLARE 
    js_result json;
    start_date date;
    end_date date;
    weekends json;
BEGIN 
    start_date := fn_month_start(ayear, amonth);
    end_date   := fn_month_end(ayear, amonth);
    weekends   := fn_sys_param('CALENDAR', 'WEEKEND_DAYS', NULL::json);

    WITH occ AS (
        SELECT * FROM sp_get_occupancy_calendar(start_date, end_date)
    ), calendar AS (
        SELECT date_ref AS "date", 
            fn_match_dows(date_ref, weekends) AS weekend,
            COALESCE(CASE occ_type WHEN 'actual' THEN c.occ_percent_actual ELSE c.occ_percent_overall END, 0) AS occ_percent,
            --IIF(occ_type='actual', c.occ_percent_actual, c.occ_percent_overall) AS occ_percent,
            COALESCE(c.occ_rooms, 0) AS occ_rooms,
            COALESCE(c.revenue, 0) AS revenue,
            COALESCE(c.adr, 0) AS adr,
            COALESCE(c.rev_par, 0) AS rev_par
        FROM fn_date_range(start_date, end_date) AS date_ref LEFT JOIN occ c ON c.adate = date_ref
    ), summ AS (
        SELECT
            CASE occ_type
               WHEN 'actual' THEN (sum(occ_rooms) * 100.00 / NULLIF(sum(total_rooms - ooo_rooms),0))::t_percent
               ELSE (sum(occ_rooms) * 100.00 / NULLIF(sum(total_rooms),0))::t_percent
            END AS occ_percent,
            sum(occ_rooms) AS occ_rooms,
            sum(revenue) AS revenue,
            (sum(revenue) / NULLIF(sum(occ_rooms),0))::t_money AS adr,
            (sum(revenue) / NULLIF(sum(total_rooms),0))::t_money AS rev_par
        FROM occ 
    ), dataset AS (
        SELECT 
            start_date AS calendar_start, 
            end_date AS calendar_end,
            (SELECT row_to_json(summ) FROM summ) AS summary,
            (SELECT json_agg(calendar) FROM calendar) AS calendar
    )
    SELECT row_to_json(dataset) FROM dataset
    INTO js_result;

    RETURN js_result;
END

sp_occupancy_calendar_js

Parameters

Definition