aonestar.public

Parameters

Name	Type	Mode
period_type	text	IN (DEFAULT NULL)
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)
recalculate	boolean	IN (DEFAULT false)
total_rooms	smallint	OUT
ooo_rooms	smallint	OUT
oos_rooms	smallint	OUT
ooi_rooms	smallint	OUT
occ_rooms	smallint	OUT
occ_adult	smallint	OUT
occ_child	smallint	OUT
occ_infant	smallint	OUT
hsu_rooms	smallint	OUT
hsu_adult	smallint	OUT
hsu_child	smallint	OUT
hsu_infant	smallint	OUT
comp_rooms	smallint	OUT
comp_adult	smallint	OUT
comp_child	smallint	OUT
comp_infant	smallint	OUT
individual_rooms	smallint	OUT
individual_adult	smallint	OUT
individual_child	smallint	OUT
individual_infant	smallint	OUT
individual_revenue	t_money	OUT
individual_breakdown	jsonb	OUT
group_rooms	smallint	OUT
group_adult	smallint	OUT
group_child	smallint	OUT
group_infant	smallint	OUT
group_revenue	t_money	OUT
group_breakdown	jsonb	OUT
arrival_rooms	smallint	OUT
arrival_adult	smallint	OUT
arrival_child	smallint	OUT
arrival_infant	smallint	OUT
departure_rooms	smallint	OUT
departure_adult	smallint	OUT
departure_child	smallint	OUT
departure_infant	smallint	OUT
day_used_qty	smallint	OUT
day_used_adult	smallint	OUT
day_used_child	smallint	OUT
day_used_infant	smallint	OUT
day_used_revenue	t_money	OUT
booking_rooms	smallint	OUT
booking_adult	smallint	OUT
booking_child	smallint	OUT
booking_infant	smallint	OUT
walk_in_rooms	smallint	OUT
walk_in_adult	smallint	OUT
walk_in_child	smallint	OUT
walk_in_infant	smallint	OUT
early_checkout	smallint	OUT
extended_stay	smallint	OUT
canceled_rooms	smallint	OUT
canceled_adult	smallint	OUT
canceled_child	smallint	OUT
canceled_infant	smallint	OUT
no_show_rooms	smallint	OUT
no_show_adult	smallint	OUT
no_show_child	smallint	OUT
no_show_infant	smallint	OUT
tomorrow_expected_arrival	smallint	OUT
tomorrow_expected_departure	smallint	OUT

Definition

DECLARE 
	system_date date;
	recalc_start date;
	recalc_end date;
    history_start date;
    history_end date;
    _adate date;
BEGIN
	system_date := fn_system_date();
	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;

CASE period_type 
	   WHEN 'TODAY' THEN
            start_date  := system_date;
            end_date    := system_date;
            recalculate := TRUE;
       WHEN 'MTD' THEN 
            end_date    := coalesce(end_date, system_date);
            start_date  := date_trunc('MONTH', end_date)::DATE;
       WHEN 'YTD' THEN 
            end_date    := coalesce(end_date, system_date);
            start_date  := date_trunc('YEAR', end_date)::DATE;
       ELSE 
    END CASE;

IF recalculate THEN 
        recalc_start  := start_date;
        recalc_end    := end_date;    
    ELSIF start_date IS NOT NULL THEN 
        end_date      := coalesce(end_date, start_date);
        history_start := IIF(start_date < system_date, start_date, NULL); 
        history_end   := IIF(end_date < system_date, end_date, system_date-1);
        recalc_start  := IIF(end_date >= system_date, system_date, NULL);
        recalc_end    := IIF(end_date >= system_date, system_date, NULL);
    END IF;
	
	RAISE NOTICE 'recalc : % to %', recalc_start, recalc_end;
	RAISE NOTICE 'history: % to %', history_start, history_end;
	
	--history_end := IIF(end_date > system_date, system_date, end_date);
	
	IF (recalc_start IS NOT NULL) OR recalculate THEN 
		--date_ref = adate;
		total_rooms = (SELECT count(id) FROM room);
	
		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 is_overlapped(recalc_start, recalc_end, oo.start_date, oo.end_date-1)
		--WHERE $1 BETWEEN oo.start_date AND oo.end_date-1
		INTO ooo_rooms, oos_rooms, ooi_rooms;
	
		WITH REV AS (
			SELECT register_id, total_amount AS amount 
			FROM sp_get_charge_schedule(charge_type => 'room')
			WHERE charge_date BETWEEN recalc_start AND recalc_end
		), INH AS ( /* in-house today */
			SELECT COUNT(DISTINCT iif(NOT day_used, rg.room_id, NULL)) AS occ_rooms,
				COALESCE(sum(iif(NOT day_used, rg.adult, 0)), 0) AS occ_adult,	
				COALESCE(sum(iif(NOT day_used, rg.child, 0)), 0) AS occ_child,	
				COALESCE(sum(iif(NOT day_used, rg.infant, 0)), 0) AS occ_infant,	
				COUNT(DISTINCT iif(house_used, rg.room_id, NULL)) AS hsu_rooms,
				COALESCE(sum(iif(house_used, rg.adult, 0)), 0) AS hsu_adult,	
				COALESCE(sum(iif(house_used, rg.child, 0)), 0) AS hsu_child,	
				COALESCE(sum(iif(house_used, rg.infant, 0)), 0) AS hsu_infant,	
				COUNT(DISTINCT iif(rr.comp, rg.room_id, NULL)) AS comp_rooms,
				COALESCE(sum(iif(rr.comp, rg.adult, 0)), 0) AS comp_adult,	
				COALESCE(sum(iif(rr.comp, rg.child, 0)), 0) AS comp_child,	
				COALESCE(sum(iif(rr.comp, rg.infant, 0)), 0) AS comp_infant,	
				COUNT(DISTINCT iif(NOT rg.is_group, rg.room_id, NULL)) AS individual_rooms,
				COALESCE(sum(iif(NOT rg.is_group, rg.adult, 0)), 0) AS individual_adult,	
				COALESCE(sum(iif(NOT rg.is_group, rg.child, 0)), 0) AS individual_child,	
				COALESCE(sum(iif(NOT rg.is_group, rg.infant, 0)), 0) AS individual_infant,	
				COALESCE(sum(iif(NOT rg.is_group, rev.amount, 0)), 0) AS individual_revenue,	
				COUNT(DISTINCT iif(rg.is_group, rg.room_id, NULL)) AS group_rooms,
				COALESCE(sum(iif(rg.is_group, rg.adult, 0)), 0) AS group_adult,	
				COALESCE(sum(iif(rg.is_group, rg.child, 0)), 0) AS group_child,	
				COALESCE(sum(iif(rg.is_group, rg.infant, 0)), 0) AS group_infant,	
				COALESCE(sum(iif(rg.is_group, rev.amount, 0)), 0) AS group_revenue,	
				COUNT(DISTINCT iif(rg.walk_in, rg.room_id, NULL)) AS walk_in_rooms,
				COALESCE(sum(iif(rg.walk_in, rg.adult, 0)), 0) AS walk_in_adult,	
				COALESCE(sum(iif(rg.walk_in, rg.child, 0)), 0) AS walk_in_child,	
				COALESCE(sum(iif(rg.walk_in, rg.infant, 0)), 0) AS walk_in_infant,	
				COUNT(DISTINCT iif(rg.booking_id IS NOT NULL, rg.room_id, NULL)) AS booking_rooms,
				COALESCE(sum(iif(rg.booking_id IS NOT NULL, rg.adult, 0)), 0) AS booking_adult,	
				COALESCE(sum(iif(rg.booking_id IS NOT NULL, rg.child, 0)), 0) AS booking_child,	
				COALESCE(sum(iif(rg.booking_id IS NOT NULL, rg.infant, 0)), 0) AS booking_infant	
			FROM registration rg 
				LEFT JOIN registration_rates rr ON rr.register_id = rg.id AND rr.charge_date BETWEEN recalc_start AND recalc_end
				LEFT JOIN REV ON rev.register_id = rg.id
			WHERE (NOT recalculate AND rg.status = 'I')
			     OR (recalculate AND rg.status <> 'X' AND is_overlapped(recalc_start, recalc_end, rg.arrival, rg.departure-1))
			     --($1 >= system_date AND rg.status = 'I') 
				 --	OR ($1 < system_date AND $1 BETWEEN rg.arrival AND rg.departure-1 AND rg.status <> 'X')			     
		), arr AS ( /* arrival today */
			SELECT 	
				COUNT(DISTINCT rg.room_id) AS arrival_rooms,
				sum(rg.adult) AS arrival_adult,	
				sum(rg.child) AS arrival_child,	
				sum(rg.infant) AS arrival_infant,	
				COUNT(iif(rg.day_used, rg.id, NULL)) AS day_used_qty,
				sum(iif(rg.day_used, rg.adult, 0)) AS day_used_adult,	
				sum(iif(rg.day_used, rg.child, 0)) AS day_used_child,	
				sum(iif(rg.day_used, rg.infant, 0)) AS day_used_infant,	
				sum(iif(rg.day_used, rev.amount, 0)) AS day_used_revenue
			FROM registration rg LEFT JOIN REV ON rev.register_id = rg.id
			WHERE (rg.arrival BETWEEN recalc_start AND recalc_end) AND (rg.status <> 'X')
		), dep AS ( /* departure today */
			SELECT count(DISTINCT rg.room_id) AS departure_rooms,
				sum(rg.adult) AS departure_adult,	
				sum(rg.child) AS departure_child,	
				sum(rg.infant) AS departure_infant,
				COUNT(DISTINCT iif(rg.early_checkout, rg.room_id, NULL)) AS early_checkout
			FROM registration rg 
			WHERE (rg.departure BETWEEN recalc_start AND recalc_end) AND (rg.status <> 'X')
		), ext AS ( /* extend today */
			SELECT count(DISTINCT rg.room_id) AS extended_stay
			FROM registration_log lg LEFT JOIN registration rg ON rg.id = lg.register_id 
			WHERE (lg.log_date BETWEEN recalc_start AND recalc_end) AND (lg.log_type = 'Extend stay')
		)
		SELECT inh.occ_rooms, inh.occ_adult, inh.occ_child, inh.occ_infant, inh.hsu_rooms, inh.hsu_adult, inh.hsu_child, inh.hsu_infant, inh.comp_rooms, 
			 inh.comp_adult, inh.comp_child, inh.comp_infant, inh.individual_rooms, inh.individual_adult, inh.individual_child, inh.individual_infant, 
			 inh.individual_revenue, inh.group_rooms, inh.group_adult, inh.group_child, inh.group_infant, inh.group_revenue, 
		     COALESCE(arr.arrival_rooms,0), COALESCE(arr.arrival_adult,0), COALESCE(arr.arrival_child,0), COALESCE(arr.arrival_infant,0), 
		     COALESCE(dep.departure_rooms,0), COALESCE(dep.departure_adult,0), COALESCE(dep.departure_child,0), COALESCE(dep.departure_infant,0),
		     COALESCE(arr.day_used_qty,0), COALESCE(arr.day_used_adult,0), COALESCE(arr.day_used_child,0), COALESCE(arr.day_used_infant,0), COALESCE(arr.day_used_revenue,0), 
		     inh.booking_rooms, inh.booking_adult, inh.booking_child, inh.booking_infant, inh.walk_in_rooms, 
			 inh.walk_in_adult, inh.walk_in_child, inh.walk_in_infant, COALESCE(dep.early_checkout,0), COALESCE(ext.extended_stay,0)
		FROM inh, arr, dep, ext
		INTO occ_rooms, occ_adult, occ_child, occ_infant, hsu_rooms, hsu_adult, hsu_child, hsu_infant, comp_rooms, comp_adult, comp_child, comp_infant, 
			 individual_rooms, individual_adult, individual_child, individual_infant, individual_revenue, 
		     group_rooms, group_adult, group_child, group_infant, group_revenue, arrival_rooms, arrival_adult, arrival_child, arrival_infant,
		     departure_rooms, departure_adult, departure_child, departure_infant, day_used_qty, day_used_adult, day_used_child, day_used_infant, 
		     day_used_revenue, booking_rooms, booking_adult, 
			 booking_child, booking_infant, walk_in_rooms, walk_in_adult, walk_in_child, walk_in_infant, early_checkout, extended_stay; 
		    
		/* booking cancelation and no-show */
	    SELECT COALESCE(sum(iif(bk.status = 'X', bi.qty, 0)), 0) AS canceled_rooms,
	         COALESCE(sum(iif(bk.status = 'X', bi.qty * bi.adult, 0)), 0) AS canceled_adult,
	         COALESCE(sum(iif(bk.status = 'X', bi.qty * bi.child, 0)), 0) AS canceled_child,
	         COALESCE(sum(iif(bk.status = 'X', bi.qty * bi.infant, 0)), 0) AS canceled_infant,
	         COALESCE(sum(iif(bk.status in ('R','C','N'), bi.qty-bi.inhouse_qty, 0)), 0) AS no_show_rooms,
	         COALESCE(sum(iif(bk.status in ('R','C','N'), (bi.qty-bi.inhouse_qty) * bi.adult, 0)), 0) AS no_show_adult,
	         COALESCE(sum(iif(bk.status in ('R','C','N'), (bi.qty-bi.inhouse_qty) * bi.child, 0)), 0) AS no_show_child,
	         COALESCE(sum(iif(bk.status in ('R','C','N'), (bi.qty-bi.inhouse_qty) * bi.infant, 0)), 0) AS no_show_infant
	    FROM booking bk INNER JOIN booking_items bi ON bi.booking_id = bk.id 
	    WHERE bi.arrival BETWEEN recalc_start AND recalc_end
	    INTO canceled_rooms, canceled_adult, canceled_child, canceled_infant, no_show_rooms, no_show_adult, no_show_child, no_show_infant;
	
	    /* expected arrival tomorrow */
	    IF (recalc_start = recalc_end) THEN 
    	    SELECT sum(bi.qty - bi.inhouse_qty)
    	    FROM booking bk INNER JOIN booking_items bi ON bi.booking_id = bk.id 
    	    where bi.arrival = (recalc_end +1) and bk.status in ('R','C','I')
    		into tomorrow_expected_arrival; 
    	
    		/* expected departure tomorrow */
    		SELECT COUNT(DISTINCT rg.room_id)
    		FROM registration rg 
    		WHERE rg.departure = (recalc_end+1) AND rg.status <> 'X'
     		INTO tomorrow_expected_departure;
 	
     	    tomorrow_expected_arrival   := COALESCE(tomorrow_expected_arrival, 0);
     	    tomorrow_expected_departure := COALESCE(tomorrow_expected_departure, 0);
 		END IF;
 		
	    RETURN NEXT;
	ELSE
		--RETURN query 
		SELECT *
		FROM recapitulate rc
		WHERE rc.date_ref BETWEEN history_start AND history_end
		INTO _adate,
			total_rooms, ooo_rooms, oos_rooms, ooi_rooms, occ_rooms, occ_adult, occ_child, occ_infant, hsu_rooms, 
			hsu_adult, hsu_child, hsu_infant, comp_rooms, comp_adult, comp_child, comp_infant, individual_rooms, 
			individual_adult, individual_child, individual_infant, individual_revenue, individual_breakdown, group_rooms, 
			group_adult, group_child, group_infant, group_revenue, group_breakdown, arrival_rooms, arrival_adult, 
			arrival_child, arrival_infant, departure_rooms, departure_adult, departure_child, departure_infant, 
			day_used_qty, day_used_adult, day_used_child, day_used_infant, day_used_revenue, booking_rooms, booking_adult, 
			booking_child, booking_infant, walk_in_rooms, walk_in_adult, walk_in_child, walk_in_infant, early_checkout, 
			extended_stay, canceled_rooms, canceled_adult, canceled_child, canceled_infant, no_show_rooms, no_show_adult, 
			no_show_child, no_show_infant, tomorrow_expected_arrival, tomorrow_expected_departure; 		
		RETURN NEXT;
	END IF;
END

sp_get_recapitulatexxx

Parameters

Definition