aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN (DEFAULT NULL)
folio_id	integer	IN (DEFAULT NULL)
new_departure	date	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
    system_date date = fn_system_date();
    _register_id int;
    _folio_id int;
    _folio_type char(1); 
    _status char(1); 
    _arrival date;
    _departure date;
    _booking_id int;
    _booking_item int;
    --_booking_status char(1);
    _room_id int;
    _close_time timestamptz;
    _room_status char(3);
    log_title TEXT = 'Cancel checkout';
    log_msg TEXT;
    msg_prefix TEXT = '204';
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    messages jsonb = $${
        "20401": "Registration not found (%s)", 
        "20402": "Guest is not checked out",
        "20403": "Cancellation is only allowed on the day of checkout", 
        "20404": "Room %s is not available",
        "20205": "The selected date cannot be in the past is %s"
    }$$;   -- add by T.Supol 30-May-2025
    
     -- "20205": "The selected date cannot be in the past is %s"  
      --  "20205": "The selected date cannot be in the past"  
   
    v_system_date date;
begin
        
    CALL sp_check_endday_process();
    CALL sp_check_record_lock(user_name, register_id := $1);

    -- ===== add by T.Supol 30-May-2025
    
    v_system_date := fn_system_date();
  --  RAISE NOTICE 'date 1 %', $3;
  --  RAISE NOTICE 'date 2 %', v_system_date;
    
    if new_departure < v_system_date  then
   --     RAISE NOTICE '$3 < v_system_date';
        return fn_result_error('20205', messages->>'20205', to_char(fn_system_date(), 'DD-MM-YYYY'));
 
    end if;
   
    -- ================================= 
    
   
    
    -- CHECK WHETHER CANCEL-CHECKIN IS AVAILABLE
    /*
     1. check status
     2. check stay period
     3. check room avail
     */
    _register_id := $1;
    _folio_id    := $2;

    IF _folio_id IS NOT NULL THEN 
        SELECT f.folio_type, f.register_id, rg.booking_id, 
            coalesce(bk.arrival, rg.arrival), 
            coalesce(bk.departure, rg.departure), 
            coalesce(bk.status, rg.status)  
        FROM folio f 
            LEFT JOIN registration rg ON f.register_id  = rg.id
            LEFT JOIN booking bk ON bk.id = f.booking_id 
        WHERE f.id = _folio_id
        INTO _folio_type, _register_id, _booking_id, _arrival, _departure, _status;
    ELSE
        _folio_type := 'R';
        SELECT rg.status, rg.arrival, rg.departure, rg.booking_id, rg.booking_item, rg.room_id, rs.status, rg.folio_id 
        FROM registration rg LEFT JOIN room_status rs ON rs.room_id = rg.room_id
        WHERE id = _register_id
        INTO _status, _arrival, _departure, _booking_id, _booking_item, _room_id, _room_status, _folio_id;
    END IF;

    CASE
        WHEN (_folio_type = 'R' AND _status IS NULL) THEN 
            RETURN fn_result_error('20401', messages->>'20401', _register_id::text); 
        WHEN (_status <> 'O') THEN
            RETURN fn_result_error('20402', messages->>'20402'); 
        WHEN (_departure <> system_date) THEN 
            RETURN fn_result_error('20403', messages->>'20403'); 
        WHEN (_folio_type = 'R' AND _room_status <> 'VAC') THEN 
            RETURN fn_result_error('20404', messages->>'20404', fn_room_number(_room_id) ); 
        ELSE 
    END CASE;

    new_departure := iif(_departure = new_departure, NULL, new_departure);
    log_msg       := 'Departure changed to '||new_departure::TEXT; 
    _close_time   := (SELECT f.close_time FROM folio f WHERE f.id = _folio_id);

    UPDATE folio AS f
    SET status = 'A', 
        close_time = NULL,
        close_user = NULL,
        close_date = COALESCE(new_departure, close_date)
    WHERE f.id = _folio_id;

    UPDATE folio_items AS fi    
    SET closed = FALSE, 
        close_date = NULL,
        close_time = NULL,
        close_user = NULL,
        close_shift = NULL
    WHERE fi.folio_id = _folio_id AND fi.close_date = _departure AND fi.close_time = _close_time;  

    IF _booking_id NOTNULL THEN 
        UPDATE booking b 
        SET status = 'I', departure = coalesce(new_departure, departure) 
        WHERE b.id =_booking_id AND b.status = 'O';
        --PERFORM sp_log_booking(_booking_id, NULL, log_title, log_msg, user_name); 
    END IF; 

    UPDATE registration
    SET status = 'I', departure = coalesce(new_departure, departure) 
    WHERE id = _register_id;

    UPDATE room_status rs SET status = 'OCC' WHERE rs.room_id = _room_id;   

    PERFORM sp_log_inhouse(_register_id, log_title, log_msg, user_name);
    

    -- if master account has been checked out, change chargetobook's value to 'N'
    -- update name list
    -- delete history

    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_cancel_checkout', 
        jsonb_build_object(
            'register_id', register_id,
            'folio_id', folio_id,
            'new_departure', new_departure
        )
    );
END;