aonestar.public

Parameters

Name	Type	Mode
period_type	text	IN (DEFAULT NULL)
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)
segment	text	IN (DEFAULT 'month')
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
bookings	integer	OUT
room_nights	integer	OUT
revenue	t_money	OUT
adr	t_money	OUT

Definition

DECLARE
    segments json = $JSON${
        "date"            : {"key":"to_char(rp.arrival,'YYYYMMDD')::int",    "name":"to_char(rp.arrival,'dd/MM/yyyy')",   "no_reference":true},
        "month"           : {"key":"to_char(rp.arrival,'YYYYMM')::int",      "name":"to_char(rp.arrival,'FMMon YYYY')", "no_reference":true},
        "year"            : {"key":"to_char(rp.arrival,'YYYY')::int",        "name":"to_char(rp.arrival,'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;

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';

from_clause TEXT;

-- $1=start_date  $2=end_date  $3=filter_text  $4=no_group  $5=revenue_calc_type  $6=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,
            SUM(CASE WHEN rp.status = 'CXL' THEN -1 ELSE 1 END)         AS bookings,
            SUM(CASE WHEN rp.status = 'CXL' THEN -COALESCE(rp.room_qty,1)
                                             ELSE  COALESCE(rp.room_qty,1) END) AS room_nights,
            SUM((CASE WHEN rp.status = 'CXL' THEN -1 ELSE 1 END) *
                (CASE $5 WHEN 1 THEN COALESCE(rp.room_charge, 0)
                         ELSE COALESCE(rp.room_rate, 0) * COALESCE(rp.room_qty, 1) END)
            )                                                             AS revenue
        FROM {from_clause} rp
            {item_join_stmt}
            {group_join_stmt}
        WHERE ((rp.arrival BETWEEN $1 AND $2) OR ($1 BETWEEN rp.arrival AND rp.departure - 1))
          AND ($3 IS NULL OR {item_name} ILIKE '%'||$3||'%')
          AND fn_match_dows(rp.arrival, $6)
        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(bookings),    0)::int     AS bookings,
        COALESCE(SUM(room_nights), 0)::int     AS room_nights,
        COALESCE(SUM(revenue),     0)::t_money AS revenue,
        (SUM(revenue) / NULLIF(SUM(room_nights), 0))::t_money AS adr
    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 NOT NULL OR (start_date IS NULL AND end_date IS NULL) THEN
        SELECT p.start_date, p.end_date
        INTO start_date, end_date
        FROM fn_period(COALESCE(period_type, 'TODAY'), start_date, end_date) p;
    END IF;
    start_date := COALESCE(start_date, end_date);
    end_date   := COALESCE(end_date, start_date);

segment    := lower(COALESCE(segment, 'month'));
    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', 'room_nights 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;

segment_group := lower(segment_group);

from_clause := format(
        $F$(
            SELECT %s FROM rsv_pickup        WHERE date_ref <> %L
            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 'Booking Pace Stat: % to %, segment: %, group: %', start_date, end_date, segment, segment_group;

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')::bool, false);

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')::bool, false);
    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, '{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, revenue_calc_type, days;
END

sp_rsv_booking_pace_stat

Parameters

Definition