aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN
user_name	text	IN (DEFAULT NULL)
force_early_checkout	boolean	IN (DEFAULT false)
early_checkout_reason	text	IN (DEFAULT NULL)

Definition

DECLARE 
	system_date date = fn_system_date();
	close_result json;
	_early_checkout bool;
	_status   char(1);
	_room_id  int; 
	_folio_id int;
	_booking_id int;
	_guest_id int;
    _arrival date;
	_departure date;
	_ooo_status char(3);
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    is_last_room boolean = FALSE;
    --error     TEXT;
    msg_group TEXT = '203';
--    messages    jsonb = $${
--        "00": "Success",
--        "01": "Registration not found (%s).", 
--        "02": "Guest already checked out.",
--        "03": "Guest is not check-in.",
--        "04": "Auto-charge schedule not posted.", 
--        "05": "There are un-posted utility charges.", 
--        "06": "Please issue tax invoice before check out."
--    }$$;
BEGIN
    CALL sp_check_endday_process();

select status, room_id, folio_id, booking_id, guest_id, arrival, departure 
	from registration rg 
	where id = $1
	into _status, _room_id, _folio_id, _booking_id, _guest_id, _arrival, _departure;
	
	CALL sp_check_folio(_folio_id);

_early_checkout = _departure < system_date;

IF _early_checkout AND NOT force_early_checkout THEN
    	RETURN fn_result_confirm('20301','Early checkout?');
    END IF;
	
	--TODO: Check auto-post
	--TODO:	Check utilty charges
	--TODO: Check tax invoice issuing
   
    /* Check charge schedule */
    IF EXISTS(SELECT * FROM sp_get_charge_schedule(register_id => $1, posted => false) WHERE charge_date = system_date) THEN 
    	RETURN fn_result_error('20302','Charge schedule not posted.');
    END IF;
   
    /* Check group folio
    IF _booking_id IS NOT NULL THEN 
    	is_last_room = NOT EXISTS(SELECT * FROM registration WHERE booking_id = _booking_id AND id <> $1);
    	
    END IF; */
   
    user_name = coalesce(user_name, fn_current_user());

close_result = sp_close_folio(_folio_id, NULL, user_name, NULL, FALSE);

IF close_result->>'result_code' = '0' 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 = $1;
	
		/* update room status, if no other guest stay in this room */
		IF NOT EXISTS(SELECT id FROM registration rg WHERE rg.id <> $1 AND rg.room_id = _room_id AND rg.status='I') THEN 
			_ooo_status = (SELECT oo.ooo_type FROM out_of_order oo
						   WHERE oo.room_id = _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 = _room_id; 
	    END IF;
	   
	    /* reset other folios that charge to this folio */
	    UPDATE folio_items fi
	    SET charge_to_folio = NULL
	    WHERE fi.charge_to_folio = _folio_id; 
	   
	    --TODO: Keep history
	
	    IF _early_checkout THEN
			PERFORM sp_log_inhouse($1, 'Early check out', 
				format('Original departure = %L%L', _departure, ', '||early_checkout_reason), 
				user_name);
		ELSE
			PERFORM sp_log_inhouse($1, 'Check out', NULL, user_name);
		END IF;
		
		PERFORM sp_send_message_to_guest(_guest_id, 
            msg_template => 'guest-thankyou2', 
            params       => jsonb_build_object(
                'booking_id' , _booking_id,
                'register_id', $1,
                'room_number', fn_room_number(_room_id),
                'arrival'    , _arrival,
                'departure'  , _departure
            )
        );
	ELSE
		RETURN close_result;
	END IF;
	
    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, format('sp_check_out(%L)', $1));
END

sp_check_out_old

Parameters

Definition