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)
register_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;
    MESSAGES jsonb = 
    $${
        "10100": "Success",
        "10101": "All rooms were full on %s, overbooking is not allowed.",
        "10102": "%s is not sellable on %s.",
        "10103": "%s rooms is not available on %s, overbooking is not allowed.",
        "10104": "%s is not available on %s.",
        "80100": "Stay period is not valid"
    }
    $$;
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';

    IF (departure - arrival) > 1000 THEN
        result_code := '4';
        msg_code := '10108';   
        result_msg := MESSAGES->>'10108';
        RETURN;
    END IF;
                  
    IF register_id is not null THEN
        select 1, rg.arrival, rg.departure, rg.roomtype_id 
        from registration rg inner join room rm on rm.id = rg.room_id
        where rg.id = $7
        into old_qty, old_arrival, old_departure, old_roomtype;
    ELSIF booking_item is not null THEN
        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;
    END IF;

    -- 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;  

    msg_code = '101'||msg_code; 
 
    result_msg = case (msg_code)
        when '10100' then 'Success'
        when '10101' then sys_msg_format(msg_code, MESSAGES->>msg_code, overbook_date)
        when '10102' then sys_msg_format(msg_code, MESSAGES->>msg_code, roomtype, stop_sell_date)    
        when '10103' then sys_msg_format(msg_code, MESSAGES->>msg_code, roomtype, overbook_date)                                      
        when '10104' then sys_msg_format(msg_code, MESSAGES->>msg_code, roomtype, overbook_date)
    end;
    
END

sp_check_room_available

Parameters

Definition