aonestar.public

Parameters

Name	Type	Mode
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)
revenue_calc_type	integer	IN (DEFAULT fn_revenue_calc_type())
adate	date	OUT
total_rooms	integer	OUT
occ_rooms	integer	OUT
occ_adult	integer	OUT
occ_child	integer	OUT
occ_infant	integer	OUT
ooi_rooms	integer	OUT
ooo_rooms	integer	OUT
comp_rooms	integer	OUT
revenue	t_money	OUT
occ_percent_actual	t_percent	OUT
occ_percent_overall	t_percent	OUT
adr	t_money	OUT
rev_par	t_money	OUT

Definition

DECLARE 
    system_date DATE;
    history_start date;
    history_end date;
    forecast_start date;
    forecast_end date;
    actual_total_rooms int;
BEGIN
    system_date := fn_system_date();
    start_date  := coalesce(start_date, system_date);
    end_date    := coalesce(end_date, start_date);

    history_start := IIF(start_date <= system_date-1, start_date, null); 
    history_end   := IIF(end_date <= system_date-1, end_date, system_date-1); 

    forecast_start:= IIF(start_date >= system_date, start_date, system_date); 
    forecast_end  := IIF(end_date >= system_date, end_date, NULL); 

    RAISE NOTICE 'forecast: % - %', forecast_start, forecast_end;
    RAISE NOTICE 'history: % - %', history_start, history_end;

    IF (history_start IS NOT NULL) THEN 
        FOR adate, actual_total_rooms, ooo_rooms, ooi_rooms, occ_rooms, occ_adult, occ_child, occ_infant, comp_rooms, revenue IN    
            SELECT rc.date_ref, rc.total_rooms, rc.ooo_rooms, rc.ooi_rooms, rc.occ_rooms, rc.occ_adult, rc.occ_child, rc.occ_infant, rc.comp_rooms, 
                rc.individual_revenue+rc.group_revenue 
            FROM recapitulate rc
            WHERE rc.date_ref BETWEEN history_start AND history_end
        LOOP 
            total_rooms         := actual_total_rooms - ooi_rooms;      
            --CASE WHEN total_rooms
            occ_percent_overall := (occ_rooms * 100.00 / NULLIF(total_rooms,0))::t_percent; 
            occ_percent_actual  := (occ_rooms * 100.00 / NULLIF(total_rooms - ooo_rooms,0))::t_percent; 
            
            adr     := revenue / NULLIF(occ_rooms,0);
            rev_par := revenue / NULLIF(total_rooms,0);
            RETURN NEXT; 
        END LOOP;
    END IF;
    
    IF (forecast_start IS NOT NULL) THEN 
        actual_total_rooms = (SELECT count(id) FROM room);
        FOR adate IN SELECT fn_date_range(forecast_start, forecast_end) AS adate 
        LOOP 
            occ_rooms := 0; occ_adult := 0; occ_child := 0; occ_infant := 0; comp_rooms := 0; revenue := 0;  ooo_rooms:= 0; ooi_rooms := 0;
        
            SELECT count(DISTINCT iif(oo.ooo_type = 'OOO', room_id, NULL)),
                   --count(DISTINCT iif(oo.ooo_type = 'OOS', room_id, NULL)),
                   count(DISTINCT iif(oo.ooo_type = 'OOI', room_id, NULL))
            FROM out_of_order oo
            WHERE adate BETWEEN oo.start_date AND oo.end_date-1
            INTO ooo_rooms, ooi_rooms; --, oos_rooms
        
            WITH OCC AS (
                --SELECT register_id, total_amount AS amount FROM sp_get_charge_schedule(adate, 'room')
                SELECT sum(bi.qty - bi.inhouse_qty) as occ_rooms,
                    sum(bi.adult * (bi.qty - bi.inhouse_qty)) as occ_adult,
                    sum(bi.child * (bi.qty - bi.inhouse_qty)) as occ_child,
                    sum(bi.infant * (bi.qty - bi.inhouse_qty)) as occ_infant,
                    sum(iif(br.comp, (bi.qty - bi.inhouse_qty), 0)) as comp_rooms,
                    case revenue_calc_type 
                        when 1 then sum(rb.room * (bi.qty - bi.inhouse_qty))
                        else sum(br.room_rate * (bi.qty - bi.inhouse_qty)) 
                    end as revenue
                FROM booking_items bi 
                    LEFT JOIN booking b ON b.id = bi.booking_id 
                    LEFT JOIN booking_rates br ON br.booking_item = bi.item_id AND br.charge_date = adate  
                    LEFT JOIN sp_booking_rates_breakdown(adate) rb on rb.booking_item = bi.item_id 
                WHERE b.status IN ('R','C','I') 
                    and (bi.qty - bi.inhouse_qty) > 0
                    and adate between bi.arrival AND bi.departure-1
                union all 
                SELECT 
                    COUNT(DISTINCT iif(NOT day_used, rg.room_id, NULL)) AS occ_rooms,
                    sum(iif(NOT day_used, rg.adult, 0)) AS occ_adult,   
                    sum(iif(NOT day_used, rg.child, 0)) AS occ_child,   
                    sum(iif(NOT day_used, rg.infant, 0)) AS occ_infant,     
                    COUNT(DISTINCT iif(rr.comp, rg.room_id, NULL)) AS comp_rooms,
                    case revenue_calc_type 
                        when 1 then sum(rs.room)
                        else sum(rr.room_rate) 
                    end as revenue
                    --sum(rr.room_rate) AS revenue
                FROM registration rg 
                    LEFT JOIN registration_rates rr ON rr.register_id = rg.id AND rr.charge_date = adate
                    LEFT JOIN sp_rate_schedule_breakdown(adate) rs on rs.register_id = rg.id
                WHERE (rg.status IN ('I','O')) AND (adate BETWEEN rg.arrival AND rg.departure-1)
                --(adate >= system_date AND rg.status = 'I') 
                  --  OR (adate < system_date AND $1 BETWEEN rg.arrival AND rg.departure-1 AND rg.status <> 'X')
            )
            SELECT 
                COALESCE(SUM(occ.occ_rooms), 0) AS occ_rooms,
                COALESCE(sum(occ.occ_adult), 0) AS occ_adult,   
                COALESCE(sum(occ.occ_child), 0) AS occ_child,   
                COALESCE(sum(occ.occ_infant), 0) AS occ_infant,     
                COALESCE(SUM(occ.comp_rooms), 0) AS comp_rooms,
                COALESCE(sum(occ.revenue), 0) AS revenue
            FROM OCC
            INTO occ_rooms, occ_adult, occ_child, occ_infant, comp_rooms, revenue;

            total_rooms         := actual_total_rooms - ooi_rooms;      
            occ_percent_overall := (occ_rooms * 100.00 / NULLIF(total_rooms,0))::t_percent; 
            occ_percent_actual  := (occ_rooms * 100.00 / NULLIF(total_rooms - ooo_rooms,0))::t_percent; 
            
            adr     := COALESCE(revenue / NULLIF(occ_rooms,0), 0);
            rev_par := COALESCE(revenue / NULLIF(total_rooms,0), 0);
        
            RETURN NEXT;
        END LOOP;   
    END IF;
END

sp_get_occupancy_calendar

Parameters

Definition