aonestar.public

Parameters

Name	Type	Mode
roomtype_id	integer	IN
arrival	date	IN
departure	date	IN
qty	integer	IN
booking_item	integer	IN (DEFAULT NULL)
channel_id	integer	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

DECLARE 
    allow_overbook boolean;
    overbook_date text;
    stop_sell_date text;
    old_roomtype int;
    old_arrival date;
    old_departure date;
    old_qty int;
    current_avail int;
    roomtype TEXT;
BEGIN 
    /* result_code, msg_code
       0, 10100 : Available 
       4, 10101 : All rooms were full 
       2, 10102 : Specified room type is not sellable (confirm user to make booking)
       4, 10103 : Specified room type not enough, overbook not allow
       2, 10104 : Specified room type not enough (confirm user to make booking)
    */
    result_code = '0';
    msg_code = '00';
                  
    select bi.qty, bi.arrival, bi.departure, bi.roomtype_id 
    from booking_items bi
    where item_id = $5
    into old_qty, old_arrival, old_departure, old_roomtype;

    -- Check total room available
    WITH A AS (
        SELECT avail_date, sum(avail) avail 
        FROM sp_get_room_available($2, $3-1)
        GROUP BY avail_date
    ), B AS (
        SELECT avail_date, 
            iif(avail_date BETWEEN old_arrival AND old_departure-1, avail + old_qty, avail) curr_avail
        FROM A 
    )
    SELECT avail_date, curr_avail FROM B WHERE curr_avail < $4        
    ORDER BY avail_date LIMIT 1
    INTO overbook_date, current_avail;

    msg_code = iif(overbook_date IS NULL, '00', '01');

    -- Check available by room type    
    IF (msg_code = '00') THEN
        WITH A AS (
            SELECT avail_date, stop_sell,
                iif(old_roomtype = $1 and avail_date BETWEEN old_arrival AND old_departure-1, avail + old_qty, avail) AS avail
            FROM sp_get_room_available($2, $3-1, $1)
        ), 
        B AS (SELECT min(avail_date) not_avail_date FROM A WHERE avail < $4), 
        C AS (SELECT min(avail_date) stop_sell_date FROM A WHERE stop_sell)
        SELECT to_char(B.not_avail_date, 'dd Mon yyyy'), to_char(C.stop_sell_date, 'dd Mon yyyy')             
        FROM B, C 
        INTO overbook_date, stop_sell_date;
        
        msg_code = CASE 
                      WHEN (stop_sell_date IS NOT NULL) then '02'--'201'
                      WHEN (overbook_date  IS NOT NULL) then '03'--'402'
                      ELSE '00'
                   END;
    
        IF msg_code <> '00' THEN
            SELECT r.name, COALESCE(r.allow_overbook, fn_sys_param('ALLOW_OVERBOOK', true)) 
            FROM room_type r WHERE r.id = $1
            INTO roomtype, allow_overbook;
            
            msg_code = iif(msg_code = '03' AND allow_overbook, '04', msg_code);
        END IF;
    END IF;  

    result_code = case (msg_code)
        when '00' then '0'
        when '01' then '4'
        when '02' then '2'  
        when '03' then '4'                                     
        when '04' then '2'
    end;   
    result_msg = case (msg_code)
        when '00' then 'Success'
        when '01' then sys_msg_format('101'||msg_code, 'All rooms were full on %s, overbooking is not allowed.', overbook_date)
        when '02' then sys_msg_format('101'||msg_code, '%s is not sellable on %s.', roomtype, stop_sell_date)    
        when '03' then sys_msg_format('101'||msg_code, '%s rooms is not available on %s, overbooking is not allowed.', roomtype, overbook_date)                                      
        when '04' then sys_msg_format('101'||msg_code, '%s is not available on %s.', roomtype, overbook_date)
    end;

    msg_code = '101'||msg_code; 
             
    --RAISE NOTICE 'check.result_code = %', result_code;         
    --result_code = iif(result_code = '22', '11', result_code);

    --RETURN (result_code, result_msg);
END

sp_check_room_available

Description

Parameters

Definition