aonestar.public

Parameters

Name	Type	Mode
arrival	date	IN
booking_id	integer	IN
booking_item	integer	IN (DEFAULT NULL)
bedtypes	int4[]	IN (DEFAULT NULL)
locations	int4[]	IN (DEFAULT NULL)
buildings	int4[]	IN (DEFAULT NULL)
exposures	int4[]	IN (DEFAULT NULL)
super_block	boolean	IN (DEFAULT false)
block_user	text	IN (DEFAULT NULL)

Definition

DECLARE 
    bk record;
    rooms int[];
    block_count int = 0;
    _bedtypes int[];
    _result_code text;
    _result_data json;
    _msg_code text;
    _state   TEXT;
    _msg   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;
    messages jsonb = $${
        "10201" : "%s rooms have been blocked.",
        "10210" : "No room to be blocked."
    }$$;
BEGIN
    CALL sp_check_endday_process();
    CALL sp_check_record_lock(block_user, booking_id := $1, booking_item := $2);
    block_count = 0;

    FOR bk in (
        SELECT bi.booking_id, bi.item_id, arrival, departure, --generate_series(1, bi.qty-bi.block_qty-bi.inhouse_qty) seq, 
               bi.roomtype_id, bi.bedtype_id --, bi.qty, bi.block_qty, bi.inhouse_qty, bi.arrival, bi.departure
        FROM booking_items bi--, LATERAL fn_system_date() sysdate  
        WHERE bi.booking_id = $1 AND ($2 IS NULL OR bi.item_id = $2) AND (qty-block_qty-inhouse_qty) > 0 
        ORDER BY 1,2,3
    )
    LOOP 
        _bedtypes := NULLIF(COALESCE(bedtypes, ARRAY[bk.bedtype_id]), '{NULL}'::int[]);
--        RAISE NOTICE '%', bedtypes;
        RAISE NOTICE 'Params: %, %, %, %, %, %, %', bk.roomtype_id, bk.arrival, bk.departure, _bedtypes, locations, buildings, exposures;
        rooms = ARRAY(SELECT fn_find_available_room(bk.roomtype_id, bk.arrival, bk.departure, _bedtypes, locations, buildings, exposures));
        RAISE NOTICE 'Rooms: %', rooms;
        IF fn_length(rooms) > 0 THEN
            SELECT result_data, result_code, msg_code
            FROM sp_block_rooms(bk.item_id, rooms, super_block, block_user)
            INTO _result_data, _result_code, _msg_code;
            RAISE NOTICE 'Result: %, %, %', _result_code, _msg_code, _result_data;
            block_count := block_count + COALESCE(json_array_length(_result_data), 0);
        END IF;
    END LOOP;

    IF block_count > 0 THEN 
        RETURN fn_result_success('10201', messages->>'10201', block_count::text);
    ELSE
        RETURN fn_result_error('10210', messages->>'10210');
    END IF;

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_auto_block_rooms',
        jsonb_build_object(
            'booking_id', $1,
            'booking_item', $2,
            'super_block', super_block,
            'block_user', block_user,
            'bedtypes', bedtypes,
            'locations', locations,
            'buildings', buildings,
            'exposures', exposures
        )
    );
END