aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN
new_status	character	IN
remark	text	IN
user_name	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

DECLARE 
    old_status char(1);
    sys_date date;
   -- arrival date;
    v_arrival date;
    v_departure date;
    channel_id int;
    log_type TEXT;
    log_msg TEXT;
    v_folio_id int;    
    v_state   TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
    is_rebook bool;
    v_booking_item int;
BEGIN   
    /* Confirm          : R -> C
     * Unconfirm        : C -> R 
     * Cancel           : R,C,W -> X
     * Rebook           : X -> R
     * Waiting List     : R,C -> W 
     * Revert Wait List : W -> R
     */
    CALL sp_check_record_lock(user_name, booking_id := $1);
        
    SELECT b.status, b.arrival, b.departure, b.channel_id, b.folio_id  
    FROM booking b WHERE id = $1
    INTO old_status, v_arrival, v_departure, channel_id, v_folio_id;

    is_rebook := old_status in ('N','X') and new_status in ('R','C');
    
    IF (new_status = 'C' AND old_status = 'R') THEN
        -- Confirm
        SELECT a.result_code, a.result_msg, a.msg_code
        FROM sp_confirm_booking(booking_id, remark, user_name) AS a
        INTO result_code, result_msg, msg_code;
        RETURN;
    ELSEIF (new_status = 'R' AND old_status = 'C') THEN
        -- Unconfirm
        SELECT a.result_code, a.result_msg, a.msg_code
        FROM sp_unconfirm_booking(booking_id, user_name) AS a 
        INTO result_code, result_msg, msg_code;
        RETURN;
    END IF;
        
    sys_date = fn_system_date();
    
    msg_code = CASE  
        WHEN (old_status IN ('I','O')) THEN '03' -- Already checked in
        WHEN (new_status = old_status) THEN '04'
        WHEN (new_status = 'X' AND old_status = 'N') THEN '04'
        WHEN (new_status = 'W' AND old_status IN ('N','X')) THEN '04' 
        --WHEN (new_status IN ('R','C') AND old_status = 'W') THEN '04'
        WHEN (new_status IN ('R','C') AND old_status = 'X' AND v_arrival < sys_date) THEN '05'
        WHEN (new_status IN ('R','C') AND old_status = 'N' AND v_departure < sys_date) THEN '06'
        ELSE '00'
    END;

    IF msg_code = '00' THEN
        -- Recheck room avail
        IF new_status IN ('R','C') THEN
           SELECT a.result_code, a.result_msg, COALESCE(a.msg_code,'00') 
           FROM booking_items bi, 
                LATERAL sp_check_room_available(bi.roomtype_id, bi.arrival, bi.departure, bi.qty, null, channel_id) AS a
           WHERE bi.booking_id = $1 AND a.result_code = '4'
           LIMIT 1
           INTO result_code, result_msg, msg_code;
       
           IF result_code = '4' THEN 
               RETURN; 
           END IF; 
       
           msg_code = '00'; 
         
           RAISE NOTICE '% % %', result_code, result_msg, msg_code;
        END IF;
   
        IF msg_code = '00' THEN 
           -- Update booking status 
           UPDATE booking b
           SET status        = new_status,
               cancel_user   = iif(new_status = 'X', user_name, null),  
               cancel_time   = iif(new_status = 'X', current_timestamp, null),
               cancel_reason = iif(new_status = 'X', remark, null),
               cancel_date   = iif(new_status = 'X', sys_date, null),
               arrival       = IIF(is_rebook and arrival < sys_date, sys_date, arrival)
           WHERE b.id = $1; 
          
           -- ===============================

           -- Cancel blocked rooms when cancel, wait-list or no-show
           IF old_status in ('R','C') and new_status in ('N','X','W') THEN
               perform sp_unblock_all(booking_id, user_name);

               update booking_items bi set block_qty = 0 where bi.booking_id = $1; 
           END IF;   
       
           -- save booking log
           log_type = CASE 
               WHEN new_status = 'X' THEN 'Cancel Booking'
               WHEN new_status in ('R','C') AND old_status in ('N','X') THEN 'Rebook'
               ELSE 'Change Status'
           END;
           log_msg = CASE 
               WHEN new_status = 'X' THEN 'Cancel Booking'||coalesce(', '||nullif(remark,''), '')
               WHEN new_status = 'W' THEN 'Change status to Wait List'
               WHEN new_status in ('R','C') AND old_status = 'X' THEN 'Rebook'
               WHEN new_status in ('R','C') AND old_status = 'N' THEN 'Rebook from No Show'
               WHEN new_status in ('R','C') AND old_status = 'W' THEN 'Revert status from Wait List'
           END;
           PERFORM sp_log_booking($1, null, log_type, log_msg, user_name);
        END IF;
       
       

        /* Rebook */
        IF msg_code = '00' and is_rebook THEN      

           FOR v_booking_item, v_departure in (
                Select bi.item_id, bi.departure 
                from booking_items bi where bi.booking_id = $1) 
           LOOP
               update booking_items bi
               set arrival = sys_date          
               where bi.item_id = v_booking_item and bi.arrival < sys_date;

                -- Remove previous rate schedule
                IF v_departure = sys_date THEN
                    UPDATE booking_rates br SET charge_date = sys_date 
                    WHERE br.booking_id = $1 and br.charge_date = (sys_date - 1);
                END IF;

                DELETE FROM booking_rates br
                WHERE br.booking_id = $1 and br.charge_date < sys_date;
           END LOOP;     

            -- Repoen deposit folio after rebook
            IF v_folio_id is not null THEN 
                WITH F AS (
                    SELECT f.close_date, f.close_time
                    FROM folio f
                    WHERE id = v_folio_id
                )
                UPDATE folio_items AS fi    
                SET closed = FALSE, 
                    close_date = NULL,
                    close_time = NULL,
                    close_user = NULL,
                    close_shift = NULL
                FROM F
                WHERE fi.folio_id = v_folio_id AND fi.close_date = f.close_date AND fi.close_time = f.close_time;  
    
                UPDATE folio AS f
                SET status = 'A', 
                    close_time = NULL,
                    close_user = NULL
                WHERE f.id = v_folio_id;
            END IF;
        END IF;
       
    END IF;

    result_code = iif(msg_code = '00', '0', '4');
    msg_code = '104'||msg_code;
    result_msg = CASE msg_code 
        WHEN '10400' THEN 'Success'
        WHEN '10403' THEN sys_msg(msg_code, 'Booking is already checked in.')
        WHEN '10404' THEN sys_msg(msg_code, 'Invalid booking status.')
        WHEN '10405' THEN sys_msg(msg_code, 'Cannot rebook, the arrival is out of date.')
        WHEN '10406' THEN sys_msg(msg_code, 'Cannot rebook, the departure is out of date.')
    END;
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state   = RETURNED_SQLSTATE,
        result_msg= MESSAGE_TEXT,
        v_detail  = PG_EXCEPTION_DETAIL,
        v_hint    = PG_EXCEPTION_HINT,
        v_context = PG_EXCEPTION_CONTEXT;
    result_code = '4'; msg_code = '99';     
    PERFORM sp_log_error('sp_change_booking_status', result_msg, v_detail, v_state, v_hint, v_context, null, user_name); 
END