aonestar.public

Parameters

Name	Type	Mode
roomtype_id	integer	IN
arrival	date	IN
departure	date	IN
qty	integer	IN
adult	integer	IN
child	integer	IN (DEFAULT 0)
infant	integer	IN (DEFAULT 0)
booking_item	integer	IN (DEFAULT NULL)
i_origin_id	integer	IN (DEFAULT NULL)
i_market_id	integer	IN (DEFAULT NULL)
book_date	date	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
result_data	json	OUT

Definition

DECLARE 
    night int;
    adv_period int;
    sys_date date = fn_system_date();
BEGIN
    /* RESULT 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)
       4, 10105 : Rate not found
       
       pisanu created
    */

    result_code := '0';
    result_data := '[]';

    child  := coalesce(child, 0);
    infant := coalesce(infant, 0);

    if (booking_item < 1) then
        booking_item := null; 
    end if;

    if arrival < sys_date then 
        arrival := sys_date;
    end if; 
 
    -- Check room available
    SELECT a.result_code, a.result_msg, a.msg_code 
    FROM sp_check_room_available(
                  roomtype_id
                , arrival
                , departure
                , qty
                , booking_item
                ) AS a
    INTO result_code, result_msg, msg_code;

    --RAISE NOTICE 'result_code = %', result_code;

    -- Get available rates
    IF result_code < '4' THEN
        IF (book_date IS NULL) AND (booking_item IS NOT NULL) THEN
            book_date := (
                            SELECT b.book_date 
                            FROM booking_items bi LEFT join booking b ON b.id = bi.booking_id
                            WHERE bi.item_id = booking_item
                          );
        END IF;
    
        book_date  := coalesce(book_date, fn_system_date());
        night      := departure - arrival;
        adv_period := arrival - book_date;
    
        WITH Rates as (
            select * 
            from sp_get_available_rates(
                    roomtype_id, 
                    arrival, 
                    departure, 
                    qty, 
                    adult, 
                    child, 
                    infant, 
                    book_date, 
                    booking_item, 
                    null, 
                    i_origin_id, 
                    i_market_id, 
                    'D'
                )
        )
        SELECT json_agg(Rates) FROM Rates INTO result_data;
    
        IF result_data IS NULL THEN 
            result_data := '[]';
            result_code := '4';
            msg_code    := '10105';
            result_msg  := sys_msg(msg_code, 'No available rates during this period');
        END IF;
    END IF;
END

sp_get_available_rates_js

Parameters

Definition