aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    old_roomtype integer;
    new_roomtype integer;
    old_occupied_start date;
    old_occupied_end date;
    new_occupied_start date;
    new_occupied_end date;
    cur_occupied_start date;
    cur_occupied_end date;
    rsv_roomtype integer;
    rsv_arrdate date;
    rsv_depdate date;
    rsv_status text;
BEGIN
    IF TG_OP = 'UPDATE'
       AND NEW.share_room
       AND OLD.room_id IS DISTINCT FROM NEW.room_id
       AND OLD.status = 'I' AND NEW.status = 'I'
    THEN
        RETURN NULL;
    END IF;

SELECT rm.roomtype_id INTO new_roomtype
    FROM room rm WHERE rm.id = NEW.room_id;

SELECT min(rg.arrival), max(rg.departure) - 1
    INTO cur_occupied_start, cur_occupied_end
    FROM registration rg
    WHERE rg.status = 'I'
      AND rg.room_id = NEW.room_id
      AND rg.id <> NEW.id;

new_occupied_start := least(NEW.arrival, coalesce(cur_occupied_start, NEW.arrival));
    new_occupied_end := greatest(NEW.departure - 1, coalesce(cur_occupied_end, NEW.departure - 1));

IF TG_OP = 'INSERT' THEN
        IF NEW.booking_item IS NOT NULL
           AND NOT EXISTS (
               SELECT 1
               FROM registration rg
               WHERE rg.id <> NEW.id
                 AND rg.status = 'I'
                 AND rg.booking_item = NEW.booking_item
           )
        THEN
            SELECT iif(rb.id IS NULL, 'RSV', 'BLK'),
                   coalesce(rm.roomtype_id, bi.roomtype_id),
                   bi.arrival, bi.departure - 1
            INTO rsv_status, rsv_roomtype, rsv_arrdate, rsv_depdate
            FROM booking_items bi
            LEFT JOIN room_block rb
              ON rb.booking_item = bi.item_id AND rb.room_id = NEW.room_id
            LEFT JOIN room rm ON rm.id = rb.room_id
            WHERE bi.item_id = NEW.booking_item;

PERFORM sp_update_avail(
                rsv_status, 'Check in', rsv_roomtype,
                rsv_arrdate, rsv_depdate, -1,
                NEW.channel_id, NEW.booking_item, NEW.room_id, NEW.id, NEW.booking_id
            );
        END IF;

IF cur_occupied_start IS NOT NULL THEN
            PERFORM sp_update_avail(
                'INH', 'Check in', new_roomtype,
                cur_occupied_start, cur_occupied_end, -1,
                NEW.channel_id, NULL, NEW.room_id, NEW.id
            );
        END IF;

PERFORM sp_update_avail(
            'INH', 'Check in', new_roomtype,
            new_occupied_start, new_occupied_end, 1,
            NEW.channel_id, NULL, NEW.room_id, NEW.id
        );
    END IF;

IF TG_OP = 'UPDATE' THEN
        SELECT rm.roomtype_id INTO old_roomtype
        FROM room rm WHERE rm.id = OLD.room_id;

old_occupied_start := least(OLD.arrival, coalesce(cur_occupied_start, OLD.arrival));
        old_occupied_end := greatest(OLD.departure - 1, coalesce(cur_occupied_end, OLD.departure - 1));

IF OLD.status = 'I' AND NEW.status = 'I'
           AND (OLD.room_id <> NEW.room_id
                OR old_occupied_start <> new_occupied_start
                OR old_occupied_end <> new_occupied_end
                OR (OLD.channel_id IS DISTINCT FROM NEW.channel_id AND NOT NEW.share_room))
        THEN
            IF OLD.channel_id IS DISTINCT FROM NEW.channel_id
               OR new_roomtype <> old_roomtype
               OR new_occupied_end <> old_occupied_end
            THEN
                PERFORM sp_update_avail(
                    'INH', 'Update', old_roomtype,
                    old_occupied_start, old_occupied_end, -1,
                    OLD.channel_id, NULL, OLD.room_id, OLD.id
                );
                PERFORM sp_update_avail(
                    'INH', 'Update', new_roomtype,
                    new_occupied_start, new_occupied_end, 1,
                    NEW.channel_id, NULL, NEW.room_id, NEW.id
                );
            END IF;

ELSIF OLD.status = 'I' AND NEW.status = 'X' THEN
            PERFORM sp_update_avail(
                'INH', 'Cancel check-in', old_roomtype,
                old_occupied_start, old_occupied_end, -1,
                OLD.channel_id, NULL, OLD.room_id, OLD.id
            );
            IF cur_occupied_start IS NOT NULL THEN
                PERFORM sp_update_avail(
                    'INH', 'Cancel check-in', new_roomtype,
                    cur_occupied_start, cur_occupied_end, 1,
                    NEW.channel_id, NULL, NEW.room_id, NEW.id
                );
            END IF;

IF NEW.booking_item IS NOT NULL
               AND NOT EXISTS (
                   SELECT 1
                   FROM registration rg
                   WHERE rg.id <> NEW.id
                     AND rg.status = 'I'
                     AND rg.booking_item = NEW.booking_item
               )
            THEN
                SELECT iif(rb.id IS NULL, 'RSV', 'BLK'),
                       coalesce(rm.roomtype_id, bi.roomtype_id),
                       bi.arrival, bi.departure - 1
                INTO rsv_status, rsv_roomtype, rsv_arrdate, rsv_depdate
                FROM booking_items bi
                LEFT JOIN room_block rb
                  ON rb.booking_item = bi.item_id AND rb.room_id = NEW.room_id
                LEFT JOIN room rm ON rm.id = rb.room_id
                WHERE bi.item_id = NEW.booking_item;

PERFORM sp_update_avail(
                    rsv_status, 'Cancel check-in', rsv_roomtype,
                    rsv_arrdate, rsv_depdate, 1,
                    NEW.channel_id, NEW.booking_item, NEW.room_id, NEW.id, NEW.booking_id
                );
            END IF;

ELSIF OLD.status = 'I' AND NEW.status = 'O' AND OLD.departure <> NEW.departure THEN
            PERFORM sp_update_avail(
                'INH', 'Early checkout', old_roomtype,
                old_occupied_start, old_occupied_end, -1,
                OLD.channel_id, NULL, OLD.room_id, OLD.id
            );
            IF cur_occupied_start IS NOT NULL THEN
                PERFORM sp_update_avail(
                    'INH', 'Early checkout', new_roomtype,
                    cur_occupied_start, cur_occupied_end, 1,
                    NEW.channel_id, NULL, NEW.room_id, NEW.id
                );
            END IF;

ELSIF OLD.status = 'O' AND NEW.status = 'I' AND NEW.departure <> OLD.departure THEN
            IF cur_occupied_start IS NOT NULL THEN
                PERFORM sp_update_avail(
                    'INH', 'Cancel checkout', old_roomtype,
                    cur_occupied_start, cur_occupied_end, -1,
                    OLD.channel_id, NULL, OLD.room_id, OLD.id
                );
            END IF;
            PERFORM sp_update_avail(
                'INH', 'Cancel checkout', new_roomtype,
                new_occupied_start, new_occupied_end, 1,
                NEW.channel_id, NULL, NEW.room_id, NEW.id
            );
        END IF;
    END IF;

RETURN NULL;
END

sp_tr_registration_avail_change

Parameters

Definition