aonestar.public

Parameters

Name	Type	Mode
register_ids	int4[]	IN (DEFAULT NULL)
folio_ids	int4[]	IN (DEFAULT NULL)
early_checkout_reason	text	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
	system_date date = fn_system_date();
	close_result json;
	_early_checkout bool;
	bk_folio_id int;
	bk_departure date;
	_ooo_status char(3);
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    reg record;
    --is_last_room boolean = FALSE;
    log_title TEXT;
    log_msg TEXT;
    messages    jsonb = $${
        "20301": "Early checkout?",
        "20302": "Charge schedule not posted."
    }$$;
BEGIN
    CALL sp_check_endday_process();
    
    user_name = coalesce(user_name, fn_current_user());

IF register_ids IS NOT NULL THEN 
        folio_ids = folio_ids || (SELECT id FROM folio WHERE register_id = ANY(register_ids));
    END IF;

-- Check out guests
    FOR reg IN 
        SELECT f.id AS folio_id, f.folio_type, f.register_id, f.booking_id, rg.status, rg.room_id, rg.guest_id, rm.room_number, 
               IIF(f.folio_type='B', b.arrival, rg.arrival) AS arrival, 
               IIF(f.folio_type='B', b.departure, rg.departure) AS departure
    	FROM folio f 
    	   LEFT JOIN registration rg ON rg.id = f.register_id 
    	   LEFT JOIN room rm ON rm.id = rg.room_id 
    	   LEFT JOIN booking b ON b.booking_id = f.booking_id
    	WHERE f.id = ANY(folio_ids)
    LOOP 
    	CALL sp_check_folio(reg.folio_id);
    
    	_early_checkout = reg.departure < system_date;
    
--        IF _early_checkout AND NOT force_early_checkout THEN
--        	RETURN fn_result_confirm('20301', messages->>'20301');
--        END IF;
    	
    	--TODO:	Check utilty charges
    	--TODO: Check tax invoice issuing
       
        /* Check auto-post room charge */
        IF EXISTS(SELECT * FROM sp_get_charge_schedule(folio_id => reg.folio_id, posted => FALSE, post_date => system_date)) THEN 
        	PERFORM sp_raise_error('20302', messages->>'20302');
        END IF;
    
        /* Check auto-post other charges */
        -- TODO: 
       
    
    	close_result = sp_close_folio(reg.folio_id, NULL, user_name, NULL, FALSE);
    
    	IF close_result->>'result_code' = '4' THEN 
    	   PERFORM sp_raise_error(close_result);
    	END IF;
    	
 
		IF (reg.folio_type = 'R') THEN 
    		UPDATE registration rg 
    		SET status = 'O', 
    			early_checkout = _early_checkout,
    			departure = system_date,
    			checkout_time = current_timestamp,
    			checkout_user = user_name
    		WHERE rg.id = reg.register_id;
		
            /* update room status, if no other guest stay in this room */
    		IF NOT EXISTS(SELECT id FROM registration rg 
    		              WHERE rg.id <> reg.register_id AND rg.room_id = reg.room_id AND rg.status='I') 
    		THEN 
    			_ooo_status = (SELECT oo.ooo_type FROM out_of_order oo
    						   WHERE oo.room_id =reg.room_id AND oo.start_date = system_date
    						   LIMIT 1);
    			
    			UPDATE room_status rs 
    		    SET status = COALESCE(_ooo_status, 'VAC'), 
    		    	hk_status = fn_get_param('ROOM_STATUS','CHECKOUT_STATUS', 'DI') 
    		    WHERE rs.room_id = reg.room_id; 
    	    END IF;
    	
    	--ELSEIF (reg.folio_type = 'B') THEN 
    	
        END IF;
	   
	    /* reset other folios that charge to this folio */
	    UPDATE folio_items fi
	    SET charge_to_folio = NULL
	    WHERE fi.charge_to_folio = reg.folio_id; 
    
        log_title = IIF(_early_checkout, 'Early check out', 'Check out');
        log_msg   = IIF(_early_checkout, format('Original departure = %L%L', reg.departure, ', '||early_checkout_reason), null);
	    
	    /* Do booking checkout */
    	IF reg.folio_type = 'B' OR NOT EXISTS(SELECT * FROM registration r WHERE r.booking_id = reg.booking_id) THEN 
            UPDATE booking b 
            SET ststus = 'O', departure_time = current_time
            WHERE b.id = reg.booking_id AND b.status = 'I';
            PERFORM sp_log_booking(reg.booking_id, NULL, log_title, log_msg, user_name); 
        END IF;
    
        IF reg.folio_type = 'R' THEN
            PERFORM sp_log_inhouse(reg.register_id, log_title, log_msg, user_name); 
    		PERFORM sp_send_message_to_guest(reg.guest_id, 
                msg_template => 'guest-thankyou2', 
                params       => jsonb_build_object(
                    'booking_id' , reg.booking_id,
                    'register_id', reg.register_id,
                    'room_number', reg.room_number,
                    'arrival'    , reg.arrival,
                    'departure'  , reg.departure
                )
            );
        END IF;
         
        -- Check out booking
        IF NOT keep_booking THEN 
            SELECT b.folio_id, b.departure FROM booking b WHERE b.id = $1
            INTO bk_folio_id, bk_departure;
        
            IF bk_folio_id IS NOT NULL THEN 
                close_result = sp_close_folio(bk_folio_id, NULL, user_name, NULL, FALSE);
                IF close_result->>'result_code' = '4' THEN 
                    PERFORM sp_raise_error(close_result); 
                END IF;
            END IF;
        
            UPDATE booking SET status = 'O', departure_time = current_time WHERE id = $1;
        
            IF bk_departure < system_date THEN
                PERFORM sp_log_booking($1, NULL, 'Early check out', 
                    format('Original departure = %L%L', bk_departure, ', '||early_checkout_reason), user_name);
            ELSE
                PERFORM sp_log_booking($1, NULL, 'Check out', NULL, user_name);
            END IF;
        END IF;
	END LOOP;
	
    RETURN fn_result_success();
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _msg_text = MESSAGE_TEXT,
        _sqlstate = RETURNED_SQLSTATE,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT; 
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_check_out_booking', 
        jsonb_build_object(
            'booking_id', booking_id,
            'register_ids', register_ids,
            'keep_booking', keep_booking,
            'force_early_checkout', force_early_checkout,
            'early_checkout_reason', early_checkout_reason
        )
    );
END

sp_group_check_out

Parameters

Definition