aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN
rateplan_id	integer	IN
room_id	integer	IN
arrival	date	IN
departure	date	IN
checkin_data	jsonb	IN
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
result_data	jsonb	OUT

Definition

declare 
    _rate_id int;
    _schedule jsonb;
    _summary jsonb;
    _state   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;
    _booking_item int;
    OP_CHECKIN SMALLINT = 1;
    OP_WALKIN  SMALLINT = 2;
    OP_MODIFY  SMALLINT = 3;
    operation SMALLINT;
    _roomtype_id int; 
    is_rate_changed bool;
    is_room_changed bool;
    is_period_changed bool;
begin 
    result_code = '0'; result_msg = 'Success'; msg_code = '00';

--  select rs.result_code, rs.result_msg, rs.msg_code, rs.rate_id, rs.rate_schedule
--  from sp_get_rate_schedule_js(rateplan_id, roomtype_id, arrival, departure) rs
--  into result_code, result_msg, msg_code, _rate_id, _rate_schedule;

--    operation = CASE 
--        when (register_id IS null) AND (checkin_data->>'booking_id' IS NOT NULL) OP_CHECKIN
--        when (register_id IS null) AND (checkin_data->>'booking_id' IS NULL)     OP_WALKIN
--        ELSE OP_MODIFY
--    END;
--
--    _booking_item = (checkin_data->>'booking_item')::int; 
--
--    IF (operation = OP_CHECKIN) 
--
--    IF operation = OP_CHECKIN THEN
--        IF EXISTS(SELECT * FROM room_block rb WHERE rb.booking_item  = _booking_item AND rb.room_id = _room_id) THEN 
--            
--    -- Check room available
--        SELECT a.result_code, a.result_msg, a.msg_code 
--        FROM sp_check_room_available(roomtype_id, arrival, departure, 1, _booking_item) AS a
--        INTO result_code, result_msg, msg_code;
--    END IF;

    _roomtype_id = (SELECT roomtype_id FROM room WHERE id = $3);
    _rate_id = (SELECT rd.rate_id FROM rate_details rd WHERE rd.rateplan_id = $1 AND rd.roomtype_id = _roomtype_id);
    
    WITH rates AS (
        SELECT start_date, rate_amount, stop_sell, cta, ctd, min_stay, max_stay, restricted  
        FROM sp_get_rate_schedule(_rate_id, $4, $5, false)  
    ), dataset AS (
        SELECT start_date AS "date", rate_amount AS amount FROM rates
    )
    SELECT json_agg(rates) FROM rates
    INTO _schedule;

    --if result_code <> '4' then
    IF _schedule IS NOT NULL AND jsonb_array_length(_schedule) > 0 THEN  
       result_data = jsonb_build_object(   --checkin_data || 
           'rate_id', _rate_id, 
           'rateplan_id', rateplan_id, 
           'rate_schedule', _schedule, 
           'roomtype_id', _roomtype_id, 
           'arrival', arrival, 
           'departure', departure
       );  
       result_data = checkin_data || result_data; 
       _summary = sp_get_checkin_summary_js(result_data);
       result_data = result_data || _summary; 
        
--      
--      result_data = jsonb_set(checkin_data, array['rate_id'], _rate_id::text::jsonb);
--        result_data = jsonb_set(result_data, array['rateplan_id'], rateplan_id);
--        result_data = jsonb_set(result_data, array['rate_schedule'], _schedule);
--        result_data = jsonb_set(result_data, array['roomtype_id'], roomtype_id);
--        result_data = jsonb_set(result_data, array['arrival'], arrival);
--      result_data = jsonb_set(result_data, array['departure'], departure);
--      _summary = sp_get_checkin_summary_js();
--      result_data = jsonb_set(result_data, array['summary'], _summary);
    ELSE 
        result_code = '4';
        msg_code = '10105';
        result_msg = sys_msg(msg_code, 'No available rates during this period');
    end if;
    
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _state    = RETURNED_SQLSTATE,
        result_msg= MESSAGE_TEXT,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;    
    result_code = '4'; msg_code = '99';     
    PERFORM sp_log_error('sp_recalc_rates_and_summary_js', result_msg, _detail, _state, _hint, _context, checkin_data); 
end

sp_verify_rate_and_available2

Parameters

Definition