aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_item record;
    v_block record;
    old_active boolean := OLD.status IN ('R', 'C', 'I');
    new_active boolean := NEW.status IN ('R', 'C', 'I');
BEGIN
    IF old_active AND NOT new_active THEN
        FOR v_item IN
            SELECT bi.item_id, bi.roomtype_id, bi.arrival, bi.departure,
                   GREATEST(bi.qty - bi.block_qty - bi.inhouse_qty, 0) AS qty
            FROM booking_items bi
            WHERE bi.booking_id = OLD.id
        LOOP
            IF v_item.qty > 0 THEN
                PERFORM sp_update_avail(
                    'RSV', 'Booking status changed', v_item.roomtype_id,
                    v_item.arrival, v_item.departure - 1, -v_item.qty,
                    OLD.channel_id, v_item.item_id, booking_id := OLD.id
                );
            END IF;
        END LOOP;

FOR v_block IN
            SELECT rb.booking_item, rb.room_id, rm.roomtype_id,
                   rb.arrival, rb.departure
            FROM room_block rb
            JOIN room rm ON rm.id = rb.room_id
            WHERE rb.booking_id = OLD.id AND rb.status = 'B'
        LOOP
            PERFORM sp_update_avail(
                'BLK', 'Booking status changed', v_block.roomtype_id,
                v_block.arrival, v_block.departure - 1, -1,
                OLD.channel_id, v_block.booking_item, v_block.room_id,
                booking_id := OLD.id
            );
        END LOOP;

ELSIF NOT old_active AND new_active THEN
        FOR v_item IN
            SELECT bi.item_id, bi.roomtype_id, bi.arrival, bi.departure,
                   GREATEST(bi.qty - bi.block_qty - bi.inhouse_qty, 0) AS qty
            FROM booking_items bi
            WHERE bi.booking_id = NEW.id
        LOOP
            IF v_item.qty > 0 THEN
                PERFORM sp_update_avail(
                    'RSV', 'Booking status changed', v_item.roomtype_id,
                    v_item.arrival, v_item.departure - 1, v_item.qty,
                    NEW.channel_id, v_item.item_id, booking_id := NEW.id
                );
            END IF;
        END LOOP;

ELSIF old_active AND new_active
          AND OLD.channel_id IS DISTINCT FROM NEW.channel_id
    THEN
        FOR v_item IN
            SELECT bi.item_id, bi.roomtype_id, bi.arrival, bi.departure,
                   GREATEST(bi.qty - bi.block_qty - bi.inhouse_qty, 0) AS qty
            FROM booking_items bi
            WHERE bi.booking_id = NEW.id
        LOOP
            IF v_item.qty > 0 THEN
                PERFORM sp_update_avail(
                    'RSV', 'Channel changed', v_item.roomtype_id,
                    v_item.arrival, v_item.departure - 1, -v_item.qty,
                    OLD.channel_id, v_item.item_id, booking_id := OLD.id
                );
                PERFORM sp_update_avail(
                    'RSV', 'Channel changed', v_item.roomtype_id,
                    v_item.arrival, v_item.departure - 1, v_item.qty,
                    NEW.channel_id, v_item.item_id, booking_id := NEW.id
                );
            END IF;
        END LOOP;

FOR v_block IN
            SELECT rb.booking_item, rb.room_id, rm.roomtype_id,
                   rb.arrival, rb.departure
            FROM room_block rb
            JOIN room rm ON rm.id = rb.room_id
            WHERE rb.booking_id = NEW.id AND rb.status = 'B'
        LOOP
            PERFORM sp_update_avail(
                'BLK', 'Channel changed', v_block.roomtype_id,
                v_block.arrival, v_block.departure - 1, -1,
                OLD.channel_id, v_block.booking_item, v_block.room_id,
                booking_id := OLD.id
            );
            PERFORM sp_update_avail(
                'BLK', 'Channel changed', v_block.roomtype_id,
                v_block.arrival, v_block.departure - 1, 1,
                NEW.channel_id, v_block.booking_item, v_block.room_id,
                booking_id := NEW.id
            );
        END LOOP;
    END IF;

RETURN NULL;
END

sp_tr_booking_avail_change

Parameters

Definition