aonestar.public

Parameters

Name	Type	Mode
period1	text	IN
period2	text	IN (DEFAULT NULL)
period3	text	IN (DEFAULT NULL)
period4	text	IN (DEFAULT NULL)
date_ref	date	IN (DEFAULT NULL)
date_type	character	IN (DEFAULT 'B')
segment	text	IN (DEFAULT NULL)
segment_group	text	IN (DEFAULT NULL)
sort_period	integer	IN (DEFAULT NULL)
sort_column	text	IN (DEFAULT NULL)
limit_rows	integer	IN (DEFAULT NULL)
filter_text	text	IN (DEFAULT NULL)
revenue_calc_type	integer	IN (DEFAULT fn_revenue_calc_type())
seq	integer	OUT
group_id	integer	OUT
group_name	text	OUT
id	integer	OUT
name	text	OUT
p1_new_bookings	integer	OUT
p1_new_rooms	integer	OUT
p1_new_roomnights	integer	OUT
p1_new_revenue	t_money	OUT
p1_new_adult	integer	OUT
p1_new_child	integer	OUT
p1_new_infant	integer	OUT
p1_clx_bookings	integer	OUT
p1_clx_rooms	integer	OUT
p1_clx_roomnights	integer	OUT
p1_clx_revenue	t_money	OUT
p1_clx_adult	integer	OUT
p1_clx_child	integer	OUT
p1_clx_infant	integer	OUT
p1_nos_bookings	integer	OUT
p1_nos_rooms	integer	OUT
p1_nos_roomnights	integer	OUT
p1_net_bookings	integer	OUT
p1_net_rooms	integer	OUT
p1_net_roomnights	integer	OUT
p1_net_revenue	t_money	OUT
p1_adr	t_money	OUT
p1_alos	numeric	OUT
p1_alt	numeric	OUT
p2_new_bookings	integer	OUT
p2_new_rooms	integer	OUT
p2_new_roomnights	integer	OUT
p2_new_revenue	t_money	OUT
p2_new_adult	integer	OUT
p2_new_child	integer	OUT
p2_new_infant	integer	OUT
p2_clx_bookings	integer	OUT
p2_clx_rooms	integer	OUT
p2_clx_roomnights	integer	OUT
p2_clx_revenue	t_money	OUT
p2_clx_adult	integer	OUT
p2_clx_child	integer	OUT
p2_clx_infant	integer	OUT
p2_nos_bookings	integer	OUT
p2_nos_rooms	integer	OUT
p2_nos_roomnights	integer	OUT
p2_net_bookings	integer	OUT
p2_net_rooms	integer	OUT
p2_net_roomnights	integer	OUT
p2_net_revenue	t_money	OUT
p2_adr	t_money	OUT
p2_alos	numeric	OUT
p2_alt	numeric	OUT
p3_new_bookings	integer	OUT
p3_new_rooms	integer	OUT
p3_new_roomnights	integer	OUT
p3_new_revenue	t_money	OUT
p3_new_adult	integer	OUT
p3_new_child	integer	OUT
p3_new_infant	integer	OUT
p3_clx_bookings	integer	OUT
p3_clx_rooms	integer	OUT
p3_clx_roomnights	integer	OUT
p3_clx_revenue	t_money	OUT
p3_clx_adult	integer	OUT
p3_clx_child	integer	OUT
p3_clx_infant	integer	OUT
p3_nos_bookings	integer	OUT
p3_nos_rooms	integer	OUT
p3_nos_roomnights	integer	OUT
p3_net_bookings	integer	OUT
p3_net_rooms	integer	OUT
p3_net_roomnights	integer	OUT
p3_net_revenue	t_money	OUT
p3_adr	t_money	OUT
p3_alos	numeric	OUT
p3_alt	numeric	OUT
p4_new_bookings	integer	OUT
p4_new_rooms	integer	OUT
p4_new_roomnights	integer	OUT
p4_new_revenue	t_money	OUT
p4_new_adult	integer	OUT
p4_new_child	integer	OUT
p4_new_infant	integer	OUT
p4_clx_bookings	integer	OUT
p4_clx_rooms	integer	OUT
p4_clx_roomnights	integer	OUT
p4_clx_revenue	t_money	OUT
p4_clx_adult	integer	OUT
p4_clx_child	integer	OUT
p4_clx_infant	integer	OUT
p4_nos_bookings	integer	OUT
p4_nos_rooms	integer	OUT
p4_nos_roomnights	integer	OUT
p4_net_bookings	integer	OUT
p4_net_rooms	integer	OUT
p4_net_roomnights	integer	OUT
p4_net_revenue	t_money	OUT
p4_adr	t_money	OUT
p4_alos	numeric	OUT
p4_alt	numeric	OUT

Definition

DECLARE
    STMT text = $$
    WITH summ AS (
        SELECT
            COALESCE(p1.group_id, p2.group_id, p3.group_id, p4.group_id) AS group_id,
            COALESCE(p1.group_name, p2.group_name, p3.group_name, p4.group_name) AS group_name,
            COALESCE(p1.id, p2.id, p3.id, p4.id) AS id,
            COALESCE(p1.name, p2.name, p3.name, p4.name) AS name,
            COALESCE(p1.group_id, p2.group_id, p3.group_id, p4.group_id) = 999999 AS is_other_group,
            COALESCE(p1.id, p2.id, p3.id, p4.id) = 999999 AS is_other_item,
            -- p1
            sum(p1.new_bookings)   AS p1_new_bookings,
            sum(p1.new_rooms)      AS p1_new_rooms,
            sum(p1.new_roomnights) AS p1_new_roomnights,
            sum(p1.new_revenue)    AS p1_new_revenue,
            sum(p1.new_adult)      AS p1_new_adult,
            sum(p1.new_child)      AS p1_new_child,
            sum(p1.new_infant)     AS p1_new_infant,
            sum(p1.clx_bookings)   AS p1_clx_bookings,
            sum(p1.clx_rooms)      AS p1_clx_rooms,
            sum(p1.clx_roomnights) AS p1_clx_roomnights,
            sum(p1.clx_revenue)    AS p1_clx_revenue,
            sum(p1.clx_adult)      AS p1_clx_adult,
            sum(p1.clx_child)      AS p1_clx_child,
            sum(p1.clx_infant)     AS p1_clx_infant,
            sum(p1.nos_bookings)   AS p1_nos_bookings,
            sum(p1.nos_rooms)      AS p1_nos_rooms,
            sum(p1.nos_roomnights) AS p1_nos_roomnights,
            sum(p1.net_revenue)    AS p1_net_rev,
            sum(p1.net_roomnights) AS p1_net_rn,
            avg(p1.alos)           AS p1_alos,
            avg(p1.alt)            AS p1_alt,
            -- p2
            sum(p2.new_bookings)   AS p2_new_bookings,
            sum(p2.new_rooms)      AS p2_new_rooms,
            sum(p2.new_roomnights) AS p2_new_roomnights,
            sum(p2.new_revenue)    AS p2_new_revenue,
            sum(p2.new_adult)      AS p2_new_adult,
            sum(p2.new_child)      AS p2_new_child,
            sum(p2.new_infant)     AS p2_new_infant,
            sum(p2.clx_bookings)   AS p2_clx_bookings,
            sum(p2.clx_rooms)      AS p2_clx_rooms,
            sum(p2.clx_roomnights) AS p2_clx_roomnights,
            sum(p2.clx_revenue)    AS p2_clx_revenue,
            sum(p2.clx_adult)      AS p2_clx_adult,
            sum(p2.clx_child)      AS p2_clx_child,
            sum(p2.clx_infant)     AS p2_clx_infant,
            sum(p2.nos_bookings)   AS p2_nos_bookings,
            sum(p2.nos_rooms)      AS p2_nos_rooms,
            sum(p2.nos_roomnights) AS p2_nos_roomnights,
            sum(p2.net_revenue)    AS p2_net_rev,
            sum(p2.net_roomnights) AS p2_net_rn,
            avg(p2.alos)           AS p2_alos,
            avg(p2.alt)            AS p2_alt,
            -- p3
            sum(p3.new_bookings)   AS p3_new_bookings,
            sum(p3.new_rooms)      AS p3_new_rooms,
            sum(p3.new_roomnights) AS p3_new_roomnights,
            sum(p3.new_revenue)    AS p3_new_revenue,
            sum(p3.new_adult)      AS p3_new_adult,
            sum(p3.new_child)      AS p3_new_child,
            sum(p3.new_infant)     AS p3_new_infant,
            sum(p3.clx_bookings)   AS p3_clx_bookings,
            sum(p3.clx_rooms)      AS p3_clx_rooms,
            sum(p3.clx_roomnights) AS p3_clx_roomnights,
            sum(p3.clx_revenue)    AS p3_clx_revenue,
            sum(p3.clx_adult)      AS p3_clx_adult,
            sum(p3.clx_child)      AS p3_clx_child,
            sum(p3.clx_infant)     AS p3_clx_infant,
            sum(p3.nos_bookings)   AS p3_nos_bookings,
            sum(p3.nos_rooms)      AS p3_nos_rooms,
            sum(p3.nos_roomnights) AS p3_nos_roomnights,
            sum(p3.net_revenue)    AS p3_net_rev,
            sum(p3.net_roomnights) AS p3_net_rn,
            avg(p3.alos)           AS p3_alos,
            avg(p3.alt)            AS p3_alt,
            -- p4
            sum(p4.new_bookings)   AS p4_new_bookings,
            sum(p4.new_rooms)      AS p4_new_rooms,
            sum(p4.new_roomnights) AS p4_new_roomnights,
            sum(p4.new_revenue)    AS p4_new_revenue,
            sum(p4.new_adult)      AS p4_new_adult,
            sum(p4.new_child)      AS p4_new_child,
            sum(p4.new_infant)     AS p4_new_infant,
            sum(p4.clx_bookings)   AS p4_clx_bookings,
            sum(p4.clx_rooms)      AS p4_clx_rooms,
            sum(p4.clx_roomnights) AS p4_clx_roomnights,
            sum(p4.clx_revenue)    AS p4_clx_revenue,
            sum(p4.clx_adult)      AS p4_clx_adult,
            sum(p4.clx_child)      AS p4_clx_child,
            sum(p4.clx_infant)     AS p4_clx_infant,
            sum(p4.nos_bookings)   AS p4_nos_bookings,
            sum(p4.nos_rooms)      AS p4_nos_rooms,
            sum(p4.nos_roomnights) AS p4_nos_roomnights,
            sum(p4.net_revenue)    AS p4_net_rev,
            sum(p4.net_roomnights) AS p4_net_rn,
            avg(p4.alos)           AS p4_alos,
            avg(p4.alt)            AS p4_alt
        FROM sp_rsv_pickup_stat($1, $5,
                date_type := $6, segment := $7, segment_group := $8,
                filter_text := $9, limit_rows := 99999,
                revenue_calc_type := $10) AS p1

FULL OUTER JOIN sp_rsv_pickup_stat($2, $5,
                date_type := $6, segment := $7, segment_group := $8,
                filter_text := $9, limit_rows := 99999,
                revenue_calc_type := $10) AS p2
            ON p1.id = p2.id AND p1.group_id = p2.group_id

FULL OUTER JOIN sp_rsv_pickup_stat($3, $5,
                date_type := $6, segment := $7, segment_group := $8,
                filter_text := $9, limit_rows := 99999,
                revenue_calc_type := $10) AS p3
            ON COALESCE(p1.id, p2.id) = p3.id AND COALESCE(p1.group_id, p2.group_id) = p3.group_id

FULL OUTER JOIN sp_rsv_pickup_stat($4, $5,
                date_type := $6, segment := $7, segment_group := $8,
                filter_text := $9, limit_rows := 99999,
                revenue_calc_type := $10) AS p4
            ON COALESCE(p1.id, p2.id, p3.id) = p4.id
           AND COALESCE(p1.group_id, p2.group_id, p3.group_id) = p4.group_id

GROUP BY 1, 2, 3, 4, 5, 6
    ), items AS (
        SELECT ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY is_other_item, %s NULLS LAST, name) AS seq, *
        FROM summ
    )
    SELECT
        CASE WHEN is_other_item OR seq > %L THEN 999 ELSE seq END::int AS seq,
        group_id, group_name,
        CASE WHEN is_other_item OR seq > %L THEN 999999           ELSE id   END AS id,
        CASE WHEN is_other_item OR seq > %L THEN 'Others/Unknown' ELSE name END AS name,
        -- p1
        COALESCE(sum(p1_new_bookings),   0)::int,
        COALESCE(sum(p1_new_rooms),      0)::int,
        COALESCE(sum(p1_new_roomnights), 0)::int,
        COALESCE(sum(p1_new_revenue),    0)::t_money,
        COALESCE(sum(p1_new_adult),      0)::int,
        COALESCE(sum(p1_new_child),      0)::int,
        COALESCE(sum(p1_new_infant),     0)::int,
        COALESCE(sum(p1_clx_bookings),   0)::int,
        COALESCE(sum(p1_clx_rooms),      0)::int,
        COALESCE(sum(p1_clx_roomnights), 0)::int,
        COALESCE(sum(p1_clx_revenue),    0)::t_money,
        COALESCE(sum(p1_clx_adult),      0)::int,
        COALESCE(sum(p1_clx_child),      0)::int,
        COALESCE(sum(p1_clx_infant),     0)::int,
        COALESCE(sum(p1_nos_bookings),   0)::int,
        COALESCE(sum(p1_nos_rooms),      0)::int,
        COALESCE(sum(p1_nos_roomnights), 0)::int,
        (COALESCE(sum(p1_new_bookings),   0) - COALESCE(sum(p1_clx_bookings),   0))::int,
        (COALESCE(sum(p1_new_rooms),      0) - COALESCE(sum(p1_clx_rooms),      0) - COALESCE(sum(p1_nos_rooms),      0))::int,
        COALESCE(sum(p1_net_rn),  0)::int,
        COALESCE(sum(p1_net_rev), 0)::t_money,
        (sum(p1_net_rev) / NULLIF(sum(p1_net_rn), 0))::t_money AS p1_adr,
        ROUND(AVG(p1_alos), 1) AS p1_alos,
        ROUND(AVG(p1_alt),  1) AS p1_alt,
        -- p2
        COALESCE(sum(p2_new_bookings),   0)::int,
        COALESCE(sum(p2_new_rooms),      0)::int,
        COALESCE(sum(p2_new_roomnights), 0)::int,
        COALESCE(sum(p2_new_revenue),    0)::t_money,
        COALESCE(sum(p2_new_adult),      0)::int,
        COALESCE(sum(p2_new_child),      0)::int,
        COALESCE(sum(p2_new_infant),     0)::int,
        COALESCE(sum(p2_clx_bookings),   0)::int,
        COALESCE(sum(p2_clx_rooms),      0)::int,
        COALESCE(sum(p2_clx_roomnights), 0)::int,
        COALESCE(sum(p2_clx_revenue),    0)::t_money,
        COALESCE(sum(p2_clx_adult),      0)::int,
        COALESCE(sum(p2_clx_child),      0)::int,
        COALESCE(sum(p2_clx_infant),     0)::int,
        COALESCE(sum(p2_nos_bookings),   0)::int,
        COALESCE(sum(p2_nos_rooms),      0)::int,
        COALESCE(sum(p2_nos_roomnights), 0)::int,
        (COALESCE(sum(p2_new_bookings),   0) - COALESCE(sum(p2_clx_bookings),   0))::int,
        (COALESCE(sum(p2_new_rooms),      0) - COALESCE(sum(p2_clx_rooms),      0) - COALESCE(sum(p2_nos_rooms),      0))::int,
        COALESCE(sum(p2_net_rn),  0)::int,
        COALESCE(sum(p2_net_rev), 0)::t_money,
        (sum(p2_net_rev) / NULLIF(sum(p2_net_rn), 0))::t_money AS p2_adr,
        ROUND(AVG(p2_alos), 1) AS p2_alos,
        ROUND(AVG(p2_alt),  1) AS p2_alt,
        -- p3
        COALESCE(sum(p3_new_bookings),   0)::int,
        COALESCE(sum(p3_new_rooms),      0)::int,
        COALESCE(sum(p3_new_roomnights), 0)::int,
        COALESCE(sum(p3_new_revenue),    0)::t_money,
        COALESCE(sum(p3_new_adult),      0)::int,
        COALESCE(sum(p3_new_child),      0)::int,
        COALESCE(sum(p3_new_infant),     0)::int,
        COALESCE(sum(p3_clx_bookings),   0)::int,
        COALESCE(sum(p3_clx_rooms),      0)::int,
        COALESCE(sum(p3_clx_roomnights), 0)::int,
        COALESCE(sum(p3_clx_revenue),    0)::t_money,
        COALESCE(sum(p3_clx_adult),      0)::int,
        COALESCE(sum(p3_clx_child),      0)::int,
        COALESCE(sum(p3_clx_infant),     0)::int,
        COALESCE(sum(p3_nos_bookings),   0)::int,
        COALESCE(sum(p3_nos_rooms),      0)::int,
        COALESCE(sum(p3_nos_roomnights), 0)::int,
        (COALESCE(sum(p3_new_bookings),   0) - COALESCE(sum(p3_clx_bookings),   0))::int,
        (COALESCE(sum(p3_new_rooms),      0) - COALESCE(sum(p3_clx_rooms),      0) - COALESCE(sum(p3_nos_rooms),      0))::int,
        COALESCE(sum(p3_net_rn),  0)::int,
        COALESCE(sum(p3_net_rev), 0)::t_money,
        (sum(p3_net_rev) / NULLIF(sum(p3_net_rn), 0))::t_money AS p3_adr,
        ROUND(AVG(p3_alos), 1) AS p3_alos,
        ROUND(AVG(p3_alt),  1) AS p3_alt,
        -- p4
        COALESCE(sum(p4_new_bookings),   0)::int,
        COALESCE(sum(p4_new_rooms),      0)::int,
        COALESCE(sum(p4_new_roomnights), 0)::int,
        COALESCE(sum(p4_new_revenue),    0)::t_money,
        COALESCE(sum(p4_new_adult),      0)::int,
        COALESCE(sum(p4_new_child),      0)::int,
        COALESCE(sum(p4_new_infant),     0)::int,
        COALESCE(sum(p4_clx_bookings),   0)::int,
        COALESCE(sum(p4_clx_rooms),      0)::int,
        COALESCE(sum(p4_clx_roomnights), 0)::int,
        COALESCE(sum(p4_clx_revenue),    0)::t_money,
        COALESCE(sum(p4_clx_adult),      0)::int,
        COALESCE(sum(p4_clx_child),      0)::int,
        COALESCE(sum(p4_clx_infant),     0)::int,
        COALESCE(sum(p4_nos_bookings),   0)::int,
        COALESCE(sum(p4_nos_rooms),      0)::int,
        COALESCE(sum(p4_nos_roomnights), 0)::int,
        (COALESCE(sum(p4_new_bookings),   0) - COALESCE(sum(p4_clx_bookings),   0))::int,
        (COALESCE(sum(p4_new_rooms),      0) - COALESCE(sum(p4_clx_rooms),      0) - COALESCE(sum(p4_nos_rooms),      0))::int,
        COALESCE(sum(p4_net_rn),  0)::int,
        COALESCE(sum(p4_net_rev), 0)::t_money,
        (sum(p4_net_rev) / NULLIF(sum(p4_net_rn), 0))::t_money AS p4_adr,
        ROUND(AVG(p4_alos), 1) AS p4_alos,
        ROUND(AVG(p4_alt),  1) AS p4_alt
    FROM items
    GROUP BY is_other_group, 1, 2, 3, 4, 5
    ORDER BY is_other_group, group_name, 1, 5;
    $$;
BEGIN
    limit_rows  := COALESCE(limit_rows, IIF(segment IN ('date','month','year'), 999999, 5));
    sort_column := CASE
        WHEN sort_column IS NULL THEN IIF(segment IN ('date','month','year'), 'id', 'p' || COALESCE(sort_period::TEXT,'1') || '_new_rooms DESC')
        WHEN lower(sort_column) IN ('id','name','group_id','group_name') THEN sort_column
        ELSE 'p' || COALESCE(sort_period::TEXT,'1') || '_' || sort_column || ' DESC'
    END;

RAISE NOTICE 'sort: %, limit: %', sort_column, limit_rows;

stmt := format(stmt, sort_column, limit_rows, limit_rows, limit_rows);
    RAISE NOTICE '%', stmt;

RETURN QUERY
    EXECUTE stmt USING period1, period2, period3, period4, date_ref, date_type, segment, segment_group, filter_text, revenue_calc_type;
END

sp_rsv_pickup_compare

Parameters

Definition