aonestar.public

Parameters

Name	Type	Mode
period_type	text	IN (DEFAULT NULL)
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)
date_type	character	IN (DEFAULT 'B')
segment	text	IN (DEFAULT NULL)
segment_group	text	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())
days	json	IN (DEFAULT NULL)
seq	integer	OUT
group_id	integer	OUT
group_name	text	OUT
id	integer	OUT
name	text	OUT
new_bookings	integer	OUT
new_rooms	integer	OUT
new_roomnights	integer	OUT
new_revenue	t_money	OUT
new_adult	integer	OUT
new_child	integer	OUT
new_infant	integer	OUT
clx_bookings	integer	OUT
clx_rooms	integer	OUT
clx_roomnights	integer	OUT
clx_revenue	t_money	OUT
clx_adult	integer	OUT
clx_child	integer	OUT
clx_infant	integer	OUT
nos_bookings	integer	OUT
nos_rooms	integer	OUT
nos_roomnights	integer	OUT
amd_rooms	integer	OUT
amd_roomnights	integer	OUT
amd_revenue	t_money	OUT
net_bookings	integer	OUT
net_rooms	integer	OUT
net_roomnights	integer	OUT
net_revenue	t_money	OUT
adr	t_money	OUT
alos	numeric	OUT
alt	numeric	OUT

Definition

DECLARE
    segments json = $JSON${
        "date"            : {"key":"to_char(rp.date_ref, 'YYYYMMDD')::int", "name": "to_char(rp.date_ref,'dd/MM/yyyy')", "no_reference": true},
        "month"           : {"key":"to_char(rp.date_ref, 'YYYYMM')::int",   "name": "to_char(rp.date_ref, 'FMMon YYYY')", "no_reference": true},
        "year"            : {"key":"to_char(rp.date_ref, 'YYYY')::int",     "name": "to_char(rp.date_ref, 'YYYY')", "no_reference": true},
        "channel"         : {"key":"rp.channel_id"},
        "channel_type"    : {"key":"cn.type_id", "ref_table": "channel cn ON cn.id = rp.channel_id LEFT JOIN channel_type"},
        "origin"          : {"key":"rp.origin_id"},
        "market"          : {"key":"coalesce(rp.market_id, n.default_market_id)", "ref_table": "nationality n ON n.id = rp.guest_nation_id LEFT JOIN market"},
        "rate_plan"       : {"key":"rp.rateplan_id"},
        "rateplan"        : {"key":"rp.rateplan_id", "ref_table": "rate_plan"},
        "rateplan_code"   : {"key":"rp.rateplan_id", "ref_table": "rate_plan", "name": "itm.code::text"},
        "mealtype"        : {"key":"rp.mealtype_id", "ref_table": "meal_type"},
        "meal_type"       : {"key":"rp.mealtype_id", "ref_table": "meal_type"},
        "sales_staff"     : {"key":"rp.sales_id"},
        "reservation_type": {"key":"rp.rsvtype_id"},
        "nationality"     : {"key":"rp.guest_nation_id"},
        "country"         : {"key":"coalesce(rp.guest_country_res_id, rp.guest_country_id)"},
        "occupation"      : {"key":"rp.guest_occupation_id"},
        "vip"             : {"key":"rp.guest_vip_id"},
        "age_range"       : {"key":"fn_age_range_id(rp.guest_age)", "name":"fn_age_range(rp.guest_age)", "no_reference": true},
        "advance_book"    : {"key":"fn_leadtime_range_id(rp.lead_time)", "name":"fn_leadtime_range(rp.lead_time)", "no_reference": true},
        "lead_time"       : {"key":"fn_leadtime_range_id(rp.lead_time)", "name":"fn_leadtime_range(rp.lead_time)", "no_reference": true},
        "room_type"       : {"key":"rp.roomtype_id"},
        "bed_type"        : {"key":"rp.bedtype_id"},
        "roomtype"        : {"key":"rp.roomtype_id", "ref_table": "room_type"},
        "roomtype_code"   : {"key":"rp.roomtype_id", "ref_table": "room_type", "name": "itm.code::text"},
        "bedtype"         : {"key":"rp.bedtype_id", "ref_table": "bed_type"},
        "room_group"      : {"key":"rt.group_id", "ref_table": "room_type rt ON rt.id = rp.roomtype_id LEFT JOIN room_group"},
        "rate_group"      : {"key":"rpln.rategroup_id", "ref_table": "rate_plan rpln ON rpln.id = rp.rateplan_id LEFT JOIN rate_group"}
    }$JSON$::json;

item_key       TEXT = '';
    item_name      TEXT = 'itm.name::text';
    item_ref_table TEXT;
    item_ref_key   TEXT = 'id';
    item_join_stmt TEXT;
    item_has_reference bool;

group_key           TEXT = 'NULL::int';
    group_name_var      TEXT = 'NULL::text';
    group_ref_table     TEXT;
    group_ref_key       TEXT;
    group_join_stmt     TEXT;
    group_has_reference bool = FALSE;
    group_sort_column   TEXT = 'group_name';
    no_group bool;

date_condition TEXT;
    dow_condition  TEXT;
    from_clause    TEXT;

v_system_date  date := fn_system_date();

COL_LIST CONSTANT text :=
        'date_ref, status, booking_id, booking_item,'
        ' roomtype_id, bedtype_id, book_type, house_used, comp, day_used,'
        ' rateplan_id, mealtype_id, stay_type, arrival, departure,'
        ' adult, child, infant, extra_adult, extra_child, extra_infant,'
        ' room_rate, extra_adult_rate, extra_child_rate, extra_infant_rate,'
        ' room_charge, extra_charge, other_charge, total_charge,'
        ' promotion_id, channel_id, origin_id, sales_id, market_id, rsvtype_id,'
        ' lead_time, return_guest, guest_id, guest_sex, guest_age,'
        ' guest_nation_id, guest_lang_id, guest_country_id, guest_country_res_id,'
        ' guest_city_id, guest_occupation_id, guest_vip_id,'
        ' guest_name, guest_member_code, room_qty';

-- $1=start_date  $2=end_date  $3=filter_text  $4=no_group  $5=date_type  $6=revenue_calc_type  $7=days
    STMT TEXT =
    $SQL$
    WITH summ AS (
        SELECT
            {group_key}    AS group_id,
            {group_name}   AS group_name,
            {item_key}     AS id,
            {item_name}    AS name,
            -- NEW bookings (NEW + RBK only; RBN excluded)
            COUNT(DISTINCT rp.booking_id)
                FILTER (WHERE rp.status IN ('NEW','RBK')) AS new_bookings,
            SUM(rp.room_qty)
                FILTER (WHERE rp.status IN ('NEW','RBK')) AS new_rooms,
            SUM(rp.room_qty * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status IN ('NEW','RBK')) AS new_roomnights,
            SUM(CASE $6 WHEN 1 THEN COALESCE(rp.room_charge, 0)
                        ELSE COALESCE(rp.room_rate, 0) * COALESCE(rp.room_qty, 1) * GREATEST(rp.departure - rp.arrival, 1) END)
                FILTER (WHERE rp.status IN ('NEW','RBK')) AS new_revenue,
            SUM((rp.adult  + rp.extra_adult)  * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status IN ('NEW','RBK')) AS new_adult,
            SUM((rp.child  + rp.extra_child)  * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status IN ('NEW','RBK')) AS new_child,
            SUM((rp.infant + rp.extra_infant) * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status IN ('NEW','RBK')) AS new_infant,
            -- CXL bookings
            COUNT(DISTINCT rp.booking_id)
                FILTER (WHERE rp.status = 'CXL') AS clx_bookings,
            SUM(rp.room_qty)
                FILTER (WHERE rp.status = 'CXL') AS clx_rooms,
            SUM(rp.room_qty * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status = 'CXL') AS clx_roomnights,
            SUM(CASE $6 WHEN 1 THEN COALESCE(rp.room_charge, 0)
                        ELSE COALESCE(rp.room_rate, 0) * COALESCE(rp.room_qty, 1) * GREATEST(rp.departure - rp.arrival, 1) END)
                FILTER (WHERE rp.status = 'CXL') AS clx_revenue,
            SUM((rp.adult  + rp.extra_adult)  * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status = 'CXL') AS clx_adult,
            SUM((rp.child  + rp.extra_child)  * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status = 'CXL') AS clx_child,
            SUM((rp.infant + rp.extra_infant) * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status = 'CXL') AS clx_infant,
            -- NOS (no-show) — tracked for reporting, not deducted from net
            COUNT(DISTINCT rp.booking_id)
                FILTER (WHERE rp.status = 'NOS') AS nos_bookings,
            SUM(rp.room_qty)
                FILTER (WHERE rp.status = 'NOS') AS nos_rooms,
            SUM(rp.room_qty * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status = 'NOS') AS nos_roomnights,
            SUM(CASE $6 WHEN 1 THEN COALESCE(rp.room_charge, 0)
                        ELSE COALESCE(rp.room_rate, 0) * COALESCE(rp.room_qty, 1) * GREATEST(rp.departure - rp.arrival, 1) END)
                FILTER (WHERE rp.status = 'NOS') AS nos_revenue,
            -- AMD delta (sum of OLD+NEW pairs — net change from amends)
            SUM(rp.room_qty)
                FILTER (WHERE rp.status = 'AMD') AS amd_rooms,
            SUM(rp.room_qty * GREATEST(rp.departure - rp.arrival, 1))
                FILTER (WHERE rp.status = 'AMD') AS amd_roomnights,
            SUM(CASE $6 WHEN 1 THEN COALESCE(rp.room_charge, 0)
                        ELSE COALESCE(rp.room_rate, 0) * COALESCE(rp.room_qty, 1) * GREATEST(rp.departure - rp.arrival, 1) END)
                FILTER (WHERE rp.status = 'AMD') AS amd_revenue,
            -- ALOS / ALT: NEW+RBK only
            AVG(rp.departure - rp.arrival) FILTER (WHERE rp.status IN ('NEW','RBK')) AS alos,
            AVG(rp.lead_time)              FILTER (WHERE rp.status IN ('NEW','RBK')) AS alt
        FROM {from_clause} rp
            {item_join_stmt}
            {group_join_stmt}
        WHERE {date_condition}
          AND ($3 IS NULL OR {item_name} ILIKE '%'||$3||'%')
          {dow_condition}
        GROUP BY 1, 2, 3, 4
    ), items AS (
        SELECT *,
            ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY (id IS NULL), {sort_column}) AS seq,
            CASE WHEN group_id IS NULL OR lower(group_name) IN ('others','other','unknown') THEN TRUE ELSE FALSE END AS is_other_group,
            CASE WHEN id       IS NULL OR lower(name)       IN ('others','other','unknown') THEN TRUE ELSE FALSE END AS is_other_item
        FROM summ
    )
    SELECT
        CASE WHEN is_other_item OR seq > {limit_rows} THEN 999 ELSE seq END::int AS seq,
        CASE WHEN $4 THEN NULL WHEN is_other_group THEN 999999           ELSE group_id   END AS group_id,
        CASE WHEN $4 THEN NULL WHEN is_other_group THEN 'Others/Unknown' ELSE group_name END AS group_name,
        CASE WHEN is_other_item OR seq > {limit_rows} THEN 999999          ELSE id   END AS id,
        CASE WHEN is_other_item OR seq > {limit_rows} THEN 'Others/Unknown' ELSE name END AS name,
        COALESCE(SUM(new_bookings),   0)::int,
        COALESCE(SUM(new_rooms),      0)::int,
        COALESCE(SUM(new_roomnights), 0)::int,
        COALESCE(SUM(new_revenue),    0)::t_money,
        COALESCE(SUM(new_adult),      0)::int,
        COALESCE(SUM(new_child),      0)::int,
        COALESCE(SUM(new_infant),     0)::int,
        COALESCE(SUM(clx_bookings),   0)::int,
        COALESCE(SUM(clx_rooms),      0)::int,
        COALESCE(SUM(clx_roomnights), 0)::int,
        COALESCE(SUM(clx_revenue),    0)::t_money,
        COALESCE(SUM(clx_adult),      0)::int,
        COALESCE(SUM(clx_child),      0)::int,
        COALESCE(SUM(clx_infant),     0)::int,
        COALESCE(SUM(nos_bookings),   0)::int,
        COALESCE(SUM(nos_rooms),      0)::int,
        COALESCE(SUM(nos_roomnights), 0)::int,
        -- AMD columns
        COALESCE(SUM(amd_rooms),      0)::int,
        COALESCE(SUM(amd_roomnights), 0)::int,
        COALESCE(SUM(amd_revenue),    0)::t_money,
        -- net = new + amd_delta - clx
        (COALESCE(SUM(new_bookings),   0) - COALESCE(SUM(clx_bookings),   0))::int,
        (COALESCE(SUM(new_rooms),      0) + COALESCE(SUM(amd_rooms),      0) - COALESCE(SUM(clx_rooms),      0))::int,
        (COALESCE(SUM(new_roomnights), 0) + COALESCE(SUM(amd_roomnights), 0) - COALESCE(SUM(clx_roomnights), 0))::int,
        (COALESCE(SUM(new_revenue),    0) + COALESCE(SUM(amd_revenue),    0) - COALESCE(SUM(clx_revenue),    0))::t_money,
        -- ADR = net_revenue / net_roomnights
        ((COALESCE(SUM(new_revenue), 0) + COALESCE(SUM(amd_revenue), 0) - COALESCE(SUM(clx_revenue), 0)) /
         NULLIF(COALESCE(SUM(new_roomnights), 0) + COALESCE(SUM(amd_roomnights), 0) - COALESCE(SUM(clx_roomnights), 0), 0))::t_money AS adr,
        ROUND(AVG(alos), 1) AS alos,
        ROUND(AVG(alt),  1) AS alt
    FROM items
    GROUP BY is_other_group, 1, 2, 3, 4, 5
    ORDER BY is_other_group, {group_sort_column}, 1, 5;

$SQL$;

BEGIN
    period_type := upper(period_type);

IF (period_type IS NULL) AND (start_date IS NULL) AND (end_date IS NULL) THEN
        period_type := 'TODAY';
    END IF;

SELECT p.start_date, p.end_date
    FROM fn_period(period_type, start_date, end_date) p
    INTO start_date, end_date;

no_group   := segment_group IS NULL;
    limit_rows := COALESCE(limit_rows, 2147483647);
    sort_column := CASE
        WHEN sort_column IS NULL THEN IIF(segment IN ('date','month','year','age_range','advance_book','lead_time'), 'id', 'new_rooms DESC')
        WHEN sort_column ILIKE ANY('{% asc,% desc}') THEN sort_column
        WHEN lower(sort_column) NOT IN ('id','name','group_id','group_name') THEN sort_column || ' DESC'
        ELSE sort_column
    END;

RAISE NOTICE 'Period: % to %, date_type: %, sort: %, limit: %', start_date, end_date, date_type, sort_column, limit_rows;

segment       := lower(segment);
    segment_group := lower(segment_group);

date_condition := CASE upper(date_type)
        WHEN 'S' THEN $$(rp.arrival BETWEEN $1 AND $2) OR ($1 BETWEEN rp.arrival AND rp.departure - 1)$$
        ELSE $$rp.date_ref BETWEEN $1 AND $2$$
    END;

dow_condition := 'AND fn_match_dows(' ||
        CASE upper(date_type) WHEN 'S' THEN 'rp.arrival' ELSE 'rp.date_ref' END ||
        ', $7)';

IF v_system_date BETWEEN start_date AND end_date THEN
        -- AMD rows are historical ledger — always read from DB even for today's date_ref
        from_clause := format(
            $F$(
                SELECT %s FROM rsv_pickup    WHERE (date_ref <> %L OR status = 'AMD')
                UNION ALL
                SELECT %L, %s FROM sp_get_rsv_pickup(%L)
            )$F$,
            COL_LIST,
            v_system_date,
            v_system_date,
            replace(COL_LIST, 'date_ref, ', ''),
            v_system_date
        );
        RAISE NOTICE 'Using live data from sp_get_rsv_pickup(%) for today', v_system_date;
    ELSE
        from_clause := 'rsv_pickup';
    END IF;

item_key           := COALESCE(segments->segment->>'key', segment || '_id');
    item_name          := COALESCE(segments->segment->>'name', item_name);
    item_has_reference := NOT COALESCE(segments->segment->>'no_reference', 'false')::bool;

IF item_has_reference THEN
        item_ref_table := COALESCE(segments->segment->>'ref_table', segment);
        item_ref_key   := COALESCE(segments->segment->>'ref_key', 'id');
        item_join_stmt := 'LEFT JOIN ' || item_ref_table || ' AS itm ON itm.' || item_ref_key || ' = ' || item_key;
    END IF;

IF segment_group IS NOT NULL THEN
        group_key           := COALESCE(segments->segment_group->>'key', segment_group || '_id');
        group_name_var      := COALESCE(segments->segment_group->>'name', 'grp.name::text');
        group_has_reference := NOT COALESCE(segments->segment_group->>'no_reference', 'false')::bool;
    END IF;

IF group_has_reference THEN
        group_ref_table := COALESCE(segments->segment_group->>'ref_table', segment_group);
        group_ref_key   := COALESCE(segments->segment_group->>'ref_key', 'id');
        group_join_stmt := 'LEFT JOIN ' || group_ref_table || ' AS grp ON grp.' || group_ref_key || ' = ' || group_key;
    END IF;

group_sort_column := IIF(group_has_reference, 'group_name', 'group_id');

STMT := replace(STMT, '{from_clause}',       from_clause);
    STMT := replace(STMT, '{group_key}',         group_key);
    STMT := replace(STMT, '{group_name}',        group_name_var);
    STMT := replace(STMT, '{item_key}',          item_key);
    STMT := replace(STMT, '{item_name}',         item_name);
    STMT := replace(STMT, '{item_join_stmt}',    COALESCE(item_join_stmt, ''));
    STMT := replace(STMT, '{group_join_stmt}',   COALESCE(group_join_stmt, ''));
    STMT := replace(STMT, '{date_condition}',    date_condition);
    STMT := replace(STMT, '{dow_condition}',     dow_condition);
    STMT := replace(STMT, '{sort_column}',       sort_column);
    STMT := replace(STMT, '{limit_rows}',        limit_rows::text);
    STMT := replace(STMT, '{group_sort_column}', group_sort_column);

RAISE NOTICE '%', STMT;

RETURN QUERY EXECUTE stmt USING start_date, end_date, filter_text, no_group, date_type, revenue_calc_type, days;
END

sp_rsv_pickup_stat

Parameters

Definition