aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN
rateplan_id	integer	IN
room_id	integer	IN
arrival	date	IN
departure	date	IN
adult	integer	IN
child	integer	IN (DEFAULT 0)
infant	integer	IN (DEFAULT 0)
extra_adult	integer	IN (DEFAULT 0)
extra_child	integer	IN (DEFAULT 0)
extra_infant	integer	IN (DEFAULT 0)
checkin_data	jsonb	IN (DEFAULT NULL)
check_rate	boolean	IN (DEFAULT true)
check_avail	boolean	IN (DEFAULT true)
register_id	integer	IN
rateplan_id	integer	IN
room_number	t_room_number	IN
arrival	date	IN
departure	date	IN
adult	integer	IN
child	integer	IN (DEFAULT 0)
infant	integer	IN (DEFAULT 0)
extra_adult	integer	IN (DEFAULT 0)
extra_child	integer	IN (DEFAULT 0)
extra_infant	integer	IN (DEFAULT 0)
checkin_data	jsonb	IN (DEFAULT NULL)
check_rate	boolean	IN (DEFAULT true)
check_avail	boolean	IN (DEFAULT true)

Definition

declare 
    _rate_id int;
    _schedule jsonb;
    _summary jsonb;
    _state   TEXT;
    _msg   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;
    _booking_item int;
    _adult int;
    _child int;
    _infant int;
    _include_adult int;
    _include_child int;
    _include_infant int;
    _extra_adult int;
    _extra_child int;
    _extra_infant int;
    _rate_name TEXT; 
    _empty_rates int;
    _roomtype_id int; 
    _roomtype_code text;
    _roomtype_name text;
    is_rate_changed bool;
    is_room_changed bool;
    is_departure_changed bool;
    is_pax_changed bool;
    keep_original_rate bool;
    result_data jsonb;
    requirements jsonb;
    old_rate_schedule jsonb;
    old_schedule_end date;   
    schedule_end date;
    last_schedule jsonb;
    last_item_idx int;
    is_walkin bool;
    system_date date;
    is_open_rate bool;
    is_comp_rate bool;
    open_rate_amount t_money;
    open_extra_adult t_money;
    open_extra_child t_money;
    open_mealtype_id int;
    def_mealtype_id int;
begin
    select plan_type = 9, plan_type = 5, mealtype_id
    from rate_plan where id = $2
    into is_open_rate, is_comp_rate, def_mealtype_id;

--    PERFORM sp_log_debug('sp_verify_rate_and_available[INPUT]', jsonb_pretty(
--        jsonb_build_object(
--            'register_id' , register_id, 
--            'rateplan_id' , rateplan_id, 
--            'is_open_rate', is_open_rate,
--            'room_id'     , room_id, 
--            'arrival'     , arrival, 
--            'departure'   , departure, 
--            'adult'       , adult,
--            'child'       , child,
--            'infant'      , infant, 
--            'extra_adult' , extra_adult, 
--            'extra_child' , extra_child, 
--            'extra_infant', extra_infant,
--            'check_rate'  , check_rate,
--            'check_avail' , check_avail,
--            'checkin_data', checkin_data   
--        ))
--    ); 
    
    IF check_avail THEN 
        CALL sp_check_room_activity(room_id, arrival, departure, register_id, (checkin_data->>'block_id')::int);
    END IF;

    select rm.roomtype_id, rt.code, rt.name
    from room rm inner join room_type rt on rt.id = rm.roomtype_id
    where rm.id = $3
    into _roomtype_id, _roomtype_code, _roomtype_name;

    system_date := fn_system_date();

    _rate_id   := (checkin_data->>'rate_id')::int;
    _rate_name := (SELECT name FROM rate_plan WHERE id = $2);
    is_walkin  := (register_id is null) and (checkin_data->'booking_id' is null);

    IF (rateplan_id IS NOT NULL AND check_rate) THEN 
        
        SELECT rd.rate_id, COALESCE(rd.include_adult, 0), COALESCE(rd.include_child, 0), COALESCE(rd.include_infant, 0) 
        FROM v_rate_details rd 
        WHERE rd.rateplan_id = $2 AND rd.roomtype_id = _roomtype_id
        INTO _rate_id, _include_adult, _include_child, _include_infant;
        
        keep_original_rate := coalesce((checkin_data->>'keep_original_rate')::bool, false) 
                           -- and (checkin_data ? 'data_old') 
                            and not is_walkin 
                            and (coalesce(checkin_data->>'rate_schedule','[]') <> '[]'); 

        --PERFORM sp_log_debug(format('sp_verify_rate_and_available(keep_original_rate=%L)',keep_original_rate), checkin_data::text);

        if keep_original_rate and (checkin_data->'rate_schedule' is null) then
            PERFORM sp_log_warning('sp_verify_rate_and_available', 'Keep original rate, old rate_schedule is null', false, true);
        end if;
         
        IF keep_original_rate THEN                
            is_pax_changed := (checkin_data->>'adult')::int <> adult
                               or (checkin_data->>'child')::int <> child  
                               or (checkin_data->>'infant')::int <> infant;

            is_departure_changed := (checkin_data->>'departure')::date <> departure;

            old_rate_schedule := checkin_data->'rate_schedule';

            last_item_idx := jsonb_array_length(old_rate_schedule)-1;    
            last_schedule := old_rate_schedule->last_item_idx;
            old_schedule_end := (last_schedule->>'date')::date;

            schedule_end := iif(arrival = departure, departure, departure-1);

            if schedule_end < old_schedule_end then
                -- remove dates after new schedule_end
                select jsonb_agg(item)    
                from (
                    SELECT item
                    FROM jsonb_array_elements(old_rate_schedule) AS item  
                    WHERE (item->>'date')::date <= schedule_end
                    ORDER BY item->>'date'
                ) as new_schedule
                into _schedule; 
                --PERFORM sp_log_debug('sp_verify_rate_and_available(S1)', jsonb_pretty(_schedule));
            elsif schedule_end > old_schedule_end then
                -- duplicate last schedule until new schedule_end
                select old_rate_schedule || jsonb_agg(item)    
                from (
--                    SELECT item
--                    FROM jsonb_array_elements(old_rate_schedule) AS item;  
--                    UNION ALL
                    SELECT jsonb_set(last_schedule, '{date}', to_jsonb(adate::TEXT)) as item
                    from fn_date_range(old_schedule_end+1, schedule_end) adate
                    ORDER BY adate
                ) as new_schedule
                into _schedule;  
                --PERFORM sp_log_debug('sp_verify_rate_and_available(S2)', jsonb_pretty(_schedule)); 
            else
                _schedule := old_rate_schedule;    
                --PERFORM sp_log_debug('sp_verify_rate_and_available(S3)', jsonb_pretty(_schedule));    
            end if;    

            IF (_schedule IS NULL) OR (jsonb_array_length(_schedule) = 0) THEN  
                RETURN fn_result_error('10107', 'Invalid rate schedule', _rate_name);
            END IF;        

        ELSE -- Get inventory rates
            open_rate_amount := (checkin_data->'open_rate'->>'amount')::t_money;
            open_extra_adult := (checkin_data->'open_rate'->>'extra_adult_rate')::t_money;
            open_extra_child := (checkin_data->'open_rate'->>'extra_child_rate')::t_money;
            open_mealtype_id := coalesce((checkin_data->'open_rate'->>'mealtype_id')::int, def_mealtype_id);

            WITH posted as (
                select * from registration_rates r 
                where r. register_id = $1 and (r.charge_date < system_date or r.posted)
            ), rates AS (
                select p.charge_date as "date", 
                     p.room_rate AS amount,
                     p.extrabed_rate AS extra_adult_rate, 
                     p.child_rate AS extra_child_rate, 
                     p.infant_rate AS extra_infant_rate, 
                     is_comp_rate AS comp, p.comp_abf,
                     p.rateplan_id,
                     COALESCE(p.mealtype_id, def_mealtype_id) AS mealtype_id
                from posted p
                union all
                SELECT rs.rate_date AS "date",
                     IIF(is_open_rate and open_rate_amount is not null, open_rate_amount, rs.rate_amount) AS amount,
                     IIF(is_open_rate and open_extra_adult is not null, open_extra_adult, rs.extra_adult) AS extra_adult_rate,
                     IIF(is_open_rate and open_extra_child is not null, open_extra_child, rs.extra_child) AS extra_child_rate,
--                     rs.rate_amount AS amount, --start_date, rate_amount, stop_sell, cta, ctd, min_stay, max_stay, restricted  
--                     rs.extra_adult AS extra_adult_rate,
--                     rs.extra_child AS extra_child_rate,
                     rs.extra_infant AS extra_infant_rate,
                     is_comp_rate AS comp, rs.comp_abf,
                     null as rateplan_id,
                     CASE
                         WHEN is_open_rate THEN open_mealtype_id
                         ELSE def_mealtype_id
                     END AS mealtype_id
                FROM sp_get_rate_schedule(_rate_id, $4, $5, false) AS rs
                WHERE rs.rate_date not in (select charge_date from posted)
            )
            SELECT json_agg(rates), SUM(IIF(amount IS NULL, 1, 0)) FROM rates
            INTO _schedule, _empty_rates;
    
            IF (_schedule IS NULL) OR (_empty_rates > 0) OR (jsonb_array_length(_schedule) = 0) THEN  
                RETURN fn_result_error('10106', 'Rate "%s" is not available during this period', _rate_name);
            END IF;
        END IF;
      
--        raise notice '%', jsonb_pretty(_schedule);
        checkin_data := jsonb_set(checkin_data, '{rate_schedule}', _schedule);
    
        WITH requests AS ( 
            SELECT NULL::int AS id, inc.inclusion_id, inc.name, inc.multiplier AS "charge_type", inc.frequency, 
                 inc.charge_dept, inc.charge_rate, inc.qty, NULL AS start_date, NULL AS end_date, NULL AS request_time,
                 'Inclusive' AS remark, -- COALESCE(checkin_data->>'charge_to','O') = 'B' as charge_to_booking,               
                 TRUE AS inclusive                
            FROM sp_get_rate_inclusions((checkin_data->>'rateplan_id')::int) AS inc
            UNION ALL 
            SELECT *
            FROM jsonb_to_recordset(COALESCE(checkin_data->'requirements', '[]'::jsonb)) 
                 AS req(id int,
                   inclusion_id int,
                   name TEXT,
                   charge_type smallint,
                   frequency smallint, 
                   charge_dept int,
                   charge_rate t_money,
                   qty int,
                   start_date date,
                   end_date date,
                   request_time time,
                   remark TEXT,
                   inclusive bool
                 ) 
            WHERE NOT COALESCE(inclusive, false)
        )  
        SELECT COALESCE(json_agg(requests), '[]') from requests
        INTO requirements;
    
       -- PERFORM sp_log_debug('sp_verify_rate_and_available(requirements)', jsonb_pretty(requirements)); 
      
        checkin_data := jsonb_set(checkin_data, '{requirements}', requirements);
    END IF;
     
    result_data = jsonb_build_object(   
       'room_id'      , $3, 
       'room_number'  , fn_room_number($3),
       'roomtype_id'  , _roomtype_id, 
       'roomtype_code', _roomtype_code, 
       'roomtype_name', _roomtype_name,
       'rate_id'      , _rate_id, 
       'rateplan_id'  , $2, 
       'rateplan_name', _rate_name,
       'mealtype_id'  , COALESCE(open_mealtype_id, def_mealtype_id),
       'rate_schedule', COALESCE(_schedule, checkin_data->'rate_schedule'), 
       --'requirements', COALESCE(requirements, checkin_data->'requirements'), 
       'arrival'      , arrival, 
       'departure'    , departure,
       'nights'       , departure-arrival, 
       'adult'        , COALESCE(adult, (checkin_data->>'adult')::int),
       'child'        , COALESCE(child, (checkin_data->>'child')::int, 0),
       'infant'       , COALESCE(infant, (checkin_data->>'infant')::int, 0)
    );  

    checkin_data := checkin_data||result_data;

--    PERFORM sp_log_debug('sp_verify_rate_and_available[DATA]', jsonb_pretty(checkin_data)); 

    _summary     := sp_get_checkin_summary_js(checkin_data);

    result_data  := jsonb_set(result_data, '{summary}', _summary);

--    PERFORM sp_log_debug('sp_verify_rate_and_available[RESULT]', jsonb_pretty(result_data)); 
   
    RETURN fn_result_success(result_data);     
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _state   = RETURNED_SQLSTATE,
        _msg= MESSAGE_TEXT,
        _detail  = PG_EXCEPTION_DETAIL,
        _hint    = PG_EXCEPTION_HINT,
        _context = PG_EXCEPTION_CONTEXT;
    
    RETURN fn_handle_error(_state, _msg, _detail, _hint, _context, 'sp_verify_rate_and_available', 
        jsonb_build_object(
            'register_id' , register_id, 
            'rateplan_id' , rateplan_id, 
            'room_id'     , room_id, 
            'arrival'     , arrival, 
            'departure'   , departure, 
            'adult'       , adult,
            'child'       , child,
            'infant'      , infant, 
            'extra_adult' , extra_adult, 
            'extra_child' , extra_child, 
            'extra_infant', extra_infant,
            'check_rate'  , check_rate,
            'check_avail' , check_avail,
            'checkin_data', checkin_data   
        )
    ); 
end