aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    old_channel_id integer;
    new_channel_id integer;
    old_booking_status char(1);
    new_booking_status char(1);
    old_reserved_roomtype integer;
    new_reserved_roomtype integer;
    old_blocked_roomtype integer;
    new_blocked_roomtype integer;
    old_item_exists boolean;
BEGIN
    IF TG_OP IN ('UPDATE', 'DELETE') AND OLD.status = 'B' THEN
        SELECT bi.roomtype_id, b.channel_id, b.status
        INTO old_reserved_roomtype, old_channel_id, old_booking_status
        FROM booking_items bi
        JOIN booking b ON b.id = bi.booking_id
        WHERE bi.item_id = OLD.booking_item;

old_item_exists := FOUND;

IF NOT old_item_exists THEN
            SELECT b.channel_id, b.status
            INTO old_channel_id, old_booking_status
            FROM booking b
            WHERE b.id = OLD.booking_id;
        END IF;

SELECT r.roomtype_id
        INTO old_blocked_roomtype
        FROM room r
        WHERE r.id = OLD.room_id;

IF old_booking_status IN ('R', 'C', 'I')
           AND (old_item_exists OR TG_OP = 'DELETE')
        THEN
            PERFORM sp_update_avail(
                'BLK', CASE WHEN TG_OP = 'DELETE' THEN 'Unblock' ELSE 'Update block' END,
                old_blocked_roomtype, OLD.arrival, OLD.departure - 1, -1,
                old_channel_id, OLD.booking_item, OLD.room_id,
                booking_id := OLD.booking_id
            );
            -- Explicit unblock/update restores the reservation. During an FK
            -- cascade the booking item is already gone, so its DELETE trigger
            -- releases only the non-blocked RSV balance and this trigger only
            -- removes BLK.
            IF old_item_exists THEN
                PERFORM sp_update_avail(
                    'RSV', CASE WHEN TG_OP = 'DELETE' THEN 'Unblock' ELSE 'Update block' END,
                    old_reserved_roomtype, OLD.arrival, OLD.departure - 1, 1,
                    old_channel_id, OLD.booking_item, OLD.room_id,
                    booking_id := OLD.booking_id
                );
            END IF;
        END IF;
    END IF;

IF TG_OP IN ('INSERT', 'UPDATE') AND NEW.status = 'B' THEN
        SELECT bi.roomtype_id, b.channel_id, b.status
        INTO new_reserved_roomtype, new_channel_id, new_booking_status
        FROM booking_items bi
        JOIN booking b ON b.id = bi.booking_id
        WHERE bi.item_id = NEW.booking_item;

SELECT r.roomtype_id
        INTO new_blocked_roomtype
        FROM room r
        WHERE r.id = NEW.room_id;

IF new_reserved_roomtype IS NOT NULL
           AND new_booking_status IN ('R', 'C', 'I')
        THEN
            PERFORM sp_update_avail(
                'RSV', CASE WHEN TG_OP = 'INSERT' THEN 'Block' ELSE 'Update block' END,
                new_reserved_roomtype, NEW.arrival, NEW.departure - 1, -1,
                new_channel_id, NEW.booking_item, NEW.room_id,
                booking_id := NEW.booking_id
            );
            PERFORM sp_update_avail(
                'BLK', CASE WHEN TG_OP = 'INSERT' THEN 'Block' ELSE 'Update block' END,
                new_blocked_roomtype, NEW.arrival, NEW.departure - 1, 1,
                new_channel_id, NEW.booking_item, NEW.room_id,
                booking_id := NEW.booking_id
            );
        END IF;
    END IF;

RETURN NULL;
END

sp_tr_room_block_avail_change

Parameters

Definition