aonestar.public

Parameters

Name	Type	Mode
ayear	integer	IN
amonth	integer	IN
p_occ_type	text	IN
p_segment_type	text	IN
p_segment_id	integer	IN
p_unassigned	boolean	IN
p_revenue_calc_type	integer	IN
ayear	integer	IN
amonth	integer	IN
occ_type	text	IN (DEFAULT 'overall')

Definition

DECLARE
    v_start_date date := fn_month_start(ayear, amonth);
    v_end_date date := fn_month_end(ayear, amonth);
    v_system_date date := fn_system_date();
    v_weekends json := fn_sys_param('CALENDAR', 'WEEKEND_DAYS', NULL::json);
    v_segment_type text := COALESCE(NULLIF(lower(p_segment_type), ''), 'overall');
    v_occ_type text := COALESCE(NULLIF(lower(p_occ_type), ''), 'overall');
    v_revenue_calc_type integer := COALESCE(p_revenue_calc_type, fn_revenue_calc_type());
    v_target public.sales_target;
    v_result json;
BEGIN
    IF v_occ_type NOT IN ('overall', 'actual') THEN
        RAISE EXCEPTION 'occ_type must be overall or actual' USING ERRCODE = '22023';
    END IF;
    IF v_segment_type NOT IN ('overall','origin','sales_staff','market','channel','room_type','rate_plan') THEN
        RAISE EXCEPTION 'Unsupported segment_type: %', v_segment_type USING ERRCODE = '22023';
    END IF;
    IF v_segment_type = 'overall' AND (p_segment_id IS NOT NULL OR COALESCE(p_unassigned, false)) THEN
        RAISE EXCEPTION 'overall cannot have segment_id or unassigned' USING ERRCODE = '22023';
    END IF;
    IF v_segment_type <> 'overall' AND ((p_segment_id IS NULL) = NOT COALESCE(p_unassigned, false)) THEN
        RAISE EXCEPTION 'Specify exactly one of segment_id or unassigned' USING ERRCODE = '22023';
    END IF;

IF NOT COALESCE(p_unassigned, false) THEN
        SELECT * INTO v_target
        FROM public.sales_target st
        WHERE st.target_month = v_start_date
          AND st.segment_type = v_segment_type
          AND CASE v_segment_type
              WHEN 'overall'     THEN true
              ELSE st.segment_id = p_segment_id
          END;
    END IF;

WITH base AS (
        SELECT
            c.*,
            CASE WHEN c.adate < v_system_date THEN
                CASE v_revenue_calc_type
                    WHEN 1 THEN rc.total_revenue_room_gross
                    WHEN 2 THEN rc.total_revenue_room_net
                    ELSE rc.total_revenue_rate_gross
                END
                ELSE c.revenue
            END::numeric AS selected_revenue
        FROM public.sp_get_occupancy_calendar(v_start_date, v_end_date, v_revenue_calc_type) c
        LEFT JOIN public.recapitulate rc ON rc.date_ref = c.adate AND c.adate < v_system_date
    ), selected AS (
        SELECT
            d.date_ref,
            sum(d.room_night)::integer AS room_night,
            sum(d.revenue)::numeric AS revenue
        FROM public.fn_occupancy_calendar_details(v_start_date, v_end_date, v_revenue_calc_type) d
        WHERE v_segment_type = 'overall'
           OR CASE v_segment_type
              WHEN 'origin'      THEN (d.origin_id = p_segment_id OR (COALESCE(p_unassigned,false) AND d.origin_id IS NULL))
              WHEN 'sales_staff' THEN (d.sales_id = p_segment_id OR (COALESCE(p_unassigned,false) AND d.sales_id IS NULL))
              WHEN 'market'      THEN (d.market_id = p_segment_id OR (COALESCE(p_unassigned,false) AND d.market_id IS NULL))
              WHEN 'channel'     THEN (d.channel_id = p_segment_id OR (COALESCE(p_unassigned,false) AND d.channel_id IS NULL))
              WHEN 'room_type'   THEN (d.roomtype_id = p_segment_id OR (COALESCE(p_unassigned,false) AND d.roomtype_id IS NULL))
              WHEN 'rate_plan'   THEN (d.rateplan_id = p_segment_id OR (COALESCE(p_unassigned,false) AND d.rateplan_id IS NULL))
              ELSE false
           END
        GROUP BY d.date_ref
    ), daily AS (
        -- Date spine: the calendar must cover every day of the month even when a past
        -- day has no recapitulate row yet (end-day not run, or before go-live), because
        -- sp_get_occupancy_calendar's history arm only emits days that exist there.
        SELECT
            dr.date_ref,
            b.total_rooms,
            b.ooo_rooms,
            CASE WHEN v_segment_type = 'overall' THEN COALESCE(b.occ_rooms, 0)
                 ELSE COALESCE(s.room_night, 0) END AS room_night,
            CASE WHEN v_segment_type = 'overall' THEN COALESCE(b.selected_revenue, 0)
                 ELSE COALESCE(s.revenue, 0) END AS revenue
        FROM fn_date_range(v_start_date, v_end_date) AS dr(date_ref)
        LEFT JOIN base b     ON b.adate    = dr.date_ref
        LEFT JOIN selected s ON s.date_ref = dr.date_ref
    ), calendar AS (
        SELECT
            d.date_ref AS "date",
            fn_match_dows(d.date_ref, v_weekends) AS weekend,
            COALESCE(d.room_night * 100.0 / NULLIF(
                CASE v_occ_type WHEN 'actual' THEN d.total_rooms - d.ooo_rooms ELSE d.total_rooms END, 0
            ), 0)::t_percent AS occ_percent,
            d.room_night AS occ_rooms,
            d.room_night,
            d.revenue::t_money AS revenue,
            COALESCE(d.revenue / NULLIF(d.room_night, 0), 0)::t_money AS adr,
            COALESCE(d.revenue / NULLIF(d.total_rooms, 0), 0)::t_money AS rev_par
        FROM daily d
    ), summ AS (
        SELECT
            (sum(room_night) * 100.0 / NULLIF(sum(
                CASE v_occ_type WHEN 'actual' THEN total_rooms - ooo_rooms ELSE total_rooms END
            ), 0))::t_percent AS occ_percent,
            sum(room_night)::integer AS occ_rooms,
            sum(room_night)::integer AS room_night,
            sum(revenue)::t_money AS revenue,
            COALESCE(sum(revenue) / NULLIF(sum(room_night), 0), 0)::t_money AS adr,
            COALESCE(sum(revenue) / NULLIF(sum(total_rooms), 0), 0)::t_money AS rev_par,
            sum(total_rooms)::integer AS available_room_nights
        FROM daily
    ), payload AS (
        SELECT jsonb_build_object(
            'calendar_start', v_start_date,
            'calendar_end', v_end_date,
            'system_date', v_system_date,
            'segment', jsonb_build_object(
                'segment_type', v_segment_type,
                'segment_id', p_segment_id,
                'unassigned', COALESCE(p_unassigned, false)
            ),
            'calculation_basis', jsonb_build_object(
                'occ_type', v_occ_type,
                'revenue_calc_type', v_revenue_calc_type,
                'history_source', CASE WHEN v_segment_type = 'overall' THEN 'recapitulate' ELSE 'room_summary' END,
                'current_and_future_source', 'booking_and_registration'
            ),
            'summary', to_jsonb(s) || jsonb_build_object(
                'target', CASE WHEN v_target.id IS NULL THEN NULL ELSE jsonb_build_object(
                    'id', v_target.id,
                    'target_year', extract(year FROM v_target.target_month)::integer,
                    'target_month', extract(month FROM v_target.target_month)::integer,
                    'occ_percent', v_target.occ_percent,
                    'room_night', v_target.room_night,
                    'revenue', v_target.revenue,
                    'adr', v_target.adr,
                    'rev_par', v_target.rev_par,
                    'note', v_target.note
                ) END,
                'comparison', CASE WHEN v_target.id IS NULL THEN NULL ELSE jsonb_build_object(
                    'occ_percent', jsonb_build_object('variance', s.occ_percent - v_target.occ_percent,
                        'achievement_percent', s.occ_percent * 100.0 / NULLIF(v_target.occ_percent, 0),
                        'comparable', v_target.occ_percent IS NOT NULL),
                    'room_night', jsonb_build_object('variance', s.room_night - v_target.room_night,
                        'achievement_percent', s.room_night * 100.0 / NULLIF(v_target.room_night, 0),
                        'comparable', v_target.room_night IS NOT NULL),
                    'revenue', jsonb_build_object('variance', s.revenue - v_target.revenue,
                        'achievement_percent', s.revenue * 100.0 / NULLIF(v_target.revenue, 0),
                        'comparable', v_target.revenue IS NOT NULL),
                    'adr', jsonb_build_object('variance', s.adr - v_target.adr,
                        'achievement_percent', s.adr * 100.0 / NULLIF(v_target.adr, 0),
                        'comparable', v_target.adr IS NOT NULL),
                    'rev_par', jsonb_build_object('variance', s.rev_par - v_target.rev_par,
                        'achievement_percent', s.rev_par * 100.0 / NULLIF(v_target.rev_par, 0),
                        'comparable', v_target.rev_par IS NOT NULL)
                ) END
            ),
            'calendar', (SELECT COALESCE(jsonb_agg(to_jsonb(c) ORDER BY c.date), '[]'::jsonb) FROM calendar c)
        ) AS result
        FROM summ s
    )
    SELECT result::json INTO v_result FROM payload;
    RETURN v_result;
END

sp_occupancy_calendar_js

Parameters

Definition