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 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())
seq	integer	OUT
group_id	integer	OUT
group_name	text	OUT
id	integer	OUT
name	text	OUT
rooms	integer	OUT
revenue	t_money	OUT
adult	integer	OUT
child	integer	OUT
infant	integer	OUT
arr_rooms	integer	OUT
arr_adult	integer	OUT
arr_child	integer	OUT
arr_infant	integer	OUT
adr	t_money	OUT

Definition

DECLARE 
    day_adjust int = 0;--fn_system_date() - '2019-01-25'::date; 

    segments json = $JSON${
        "date"            : {"key":"to_char(rs.date_ref, 'YYYYMMDD')::int", "name": "to_char(rs.date_ref,'dd/MM/yyyy')", "no_reference": true}, 
        "month"           : {"key":"to_char(rs.date_ref, 'YYYYMM')::int",   "name": "to_char(rs.date_ref, 'Month')", "no_reference": true},  
        "year"            : {"key":"to_char(rs.date_ref, 'YYYY')::int",     "name": "to_char(rs.date_ref, 'YYYY')", "no_reference": true}, 
        "channel"         : {"key":"rs.channel_id"},
        "channel_type"   : {"key":"cn.type_id", "ref_table": "channel cn ON cn.id = rs.channel_id LEFT JOIN channel_type"},
        "origin"          : {"key":"rs.origin_id"},
        "market"          : {"key":"coalesce(rs.market_id, n.default_market_id)", "ref_table": "nationality n ON n.id = rs.guest_nation_id LEFT JOIN market"}, 
        "rate_plan"       : {"key":"rs.rateplan_id"},
        "rateplan"        : {"key":"rs.rateplan_id", "ref_table": "rate_plan"},
        "rateplan_code"   : {"key":"rs.rateplan_id", "ref_table": "rate_plan", "name": "itm.code::text"},
        "sales_staff"     : {"key":"rs.sales_id"},
        "reservation_type": {"key":"rs.rsvtype_id"},
        "nationality"     : {"key":"rs.guest_nation_id"},
        "country"         : {"key":"coalesce(rs.guest_country_res_id,rs.guest_country_id)"},
        "occupation"      : {"key":"rs.guest_occupation_id"},
        "vip"             : {"key":"rs.guest_vip_id"},
        "room_number"     : {"key":"rs.room_id","name": "room_number::text", "no_reference": true},  
        "room_type"       : {"key":"rs.roomtype_id"},
        "bed_type"        : {"key":"rs.bedtype_id"},
        "roomtype"        : {"key":"rs.roomtype_id", "ref_table": "room_type"},
        "roomtype_code"   : {"key":"rs.roomtype_id", "ref_table": "room_type", "name": "itm.code::text"},
        "bedtype"         : {"key":"rs.bedtype_id", "ref_table": "bed_type"},
        "building"        : {"key":"rs.building_id"},
        "room_location"   : {"key":"rs.location_id"},
        "room_exposure"   : {"key":"rs.exposure_id"},
        "age_range"       : {"key":"fn_age_range(rs.guest_age)", "no_reference": true},
        "advance_book"    : {"key":"fn_abp_range(rs.advance_book_period)", "no_reference": true}
    }$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 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;

    OTHER_CONDITION TEXT = $$CASE WHEN %s IS NULL OR lower(%s) IN ('others','other','unknown') THEN %s ELSE %s END$$;

    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(rs.in_house_qty) AS rooms, 
            case $5
               when 1 then sum(rs.in_house_qty * coalesce(rs.room_charge, rs.room_rate))
               else sum(rs.in_house_qty * rs.room_rate)
            end AS revenue,
            sum(rs.adult) AS adult, 
            sum(rs.child) AS child, 
            sum(rs.infant+rs.extra_infant) AS infant,
            sum(IIF(rs.date_ref = rs.start_date, rs.in_house_qty, 0)) AS arr_rooms,
            sum(IIF(rs.date_ref = rs.start_date, rs.adult, 0)) AS arr_adult,
            sum(IIF(rs.date_ref = rs.start_date, rs.child, 0)) AS arr_child,
            sum(IIF(rs.date_ref = rs.start_date, rs.infant, 0)) AS arr_infant--,
            --sum(rs.in_house_qty * rs.room_rate)/ nullif(sum(rs.in_house_qty),0) AS adr
        FROM room_summary rs  
            {item_join_stmt}
            {group_join_stmt}    
        WHERE rs.room_status = 'OCC' AND (rs.date_ref BETWEEN $1 AND $2)
            AND ($3 IS NULL OR {item_name} LIKE '%'||$3||'%')
        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,
        sum(rooms)::int, sum(revenue)::t_money, sum(adult)::int, sum(child)::int, sum(infant)::int, 
        sum(arr_rooms)::int, sum(arr_adult)::int, sum(arr_child)::int, sum(arr_infant)::int,
        (sum(revenue) / nullif(sum(rooms),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 NULL) AND (start_date IS NULL) AND (period_type IS NULL) THEN 
       period_type := 'TODAY';
    END IF;

    --PERFORM fn_period(period_type, start_date, end_date);
--    start_date := coalesce(start_date, fn_system_date()) - day_adjust; 
--    end_date   := coalesce(end_date, fn_system_date()) - day_adjust; 
    SELECT p.start_date, p.end_date 
    FROM fn_period(period_type, start_date, end_date) P
    INTO start_date, end_date;
    
--    start_date := start_date - day_adjust;
--    end_date := end_date - day_adjust;
    no_group    := segment_group IS NULL;
    limit_rows  := coalesce(limit_rows,  IIF(segment IN ('date','month','year'), 999999, 5));
    --sort_column := coalesce(sort_column||'  DESC', IIF(segment IN ('date','month','year'), 'id', 'rooms DESC'));
    sort_column := CASE
        WHEN sort_column IS NULL THEN IIF(segment IN ('date','month','year'), 'id', '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 %, sort: %, limit: %', start_date, end_date, sort_column, limit_rows;
    
    segment       := lower(segment);
    segment_group := lower(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', '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      := 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');

    --RAISE NOTICE '%', item_key, item_name, group_key, group_name;
   
    STMT := replace(STMT, '{group_key}', group_key);
    STMT := replace(STMT, '{group_name}', group_name);
    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;

END

sp_productivity

Parameters

Definition