aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN
register_ids	int4[]	IN
keep_booking	boolean	IN (DEFAULT false)
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;
    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;
    messages    jsonb = $${
        "20301": "Early checkout?",
        "20302": "Charge schedule not posted."
    }$$;
BEGIN
    CALL sp_check_endday_process();
    
    user_name = coalesce(user_name, fn_current_user());

    -- Check out guests
    FOR reg IN 
        select rg.id AS register_id, rg.status, rg.room_id, rg.folio_id, rg.booking_id, rg.guest_id, rg.arrival, rg.departure, rm.room_number
        from registration rg LEFT JOIN room rm ON rm.id = rg.room_id 
        where rg.id = ANY(register_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 charge schedule */
        CALL sp_check_charge_schedule(reg.register_id);
       
        /* 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; */
    
        close_result = sp_close_folio(reg.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 = 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;
           
            /* 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; 
           
            --TODO: Keep history
        
            IF _early_checkout THEN
                PERFORM sp_log_inhouse(reg.register_id, 'Early check out', 
                    format('Original departure = %L%L', reg.departure, ', '||early_checkout_reason), 
                    user_name);
            ELSE
                PERFORM sp_log_inhouse(reg.register_id, 'Check out', NULL, user_name);
            END IF;
            
            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
                )
            );
        ELSE
            PERFORM sp_raise_error(close_result);
        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