aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_tr_booking_avail_change
Parameters
Name
Type
Mode
Definition
DECLARE activity_type text; rsv record; blk record; BEGIN /* Trigger: after update of status, channel_id */ IF (new.status in ('R','C','W','N','X')) THEN /* channel changed */ IF (new.status <> 'W' and old.channel_id is distinct from new.channel_id) THEN FOR rsv IN ( SELECT item_id, roomtype_id, arrival, departure, qty -block_qty -inhouse_qty AS qty FROM booking_items WHERE booking_id = new.id ) LOOP PERFORM sp_update_avail('RSV', 'Channel changed', rsv.roomtype_id, rsv.arrival, rsv.departure-1, -rsv.qty, OLD.channel_id, rsv.item_id, booking_id := OLD.id); PERFORM sp_update_avail('RSV', 'Channel changed', rsv.roomtype_id, rsv.arrival, rsv.departure-1, rsv.qty, NEW.channel_id, rsv.item_id, booking_id := NEW.id); END LOOP; FOR blk IN ( SELECT b.booking_item, r.roomtype_id, b.arrival, b.departure FROM room_block b LEFT JOIN room r ON r.id = b.room_id WHERE booking_id = new.id ) LOOP PERFORM sp_update_avail('BLK', 'Channel changed', blk.roomtype_id, blk.arrival, blk.departure-1, -1, OLD.channel_id, blk.booking_item, booking_id := OLD.id); PERFORM sp_update_avail('BLK', 'Channel changed', blk.roomtype_id, blk.arrival, blk.departure-1, 1, NEW.channel_id, blk.booking_item, booking_id := NEW.id); END LOOP; END IF; /* Rebook */ IF (old.status in ('W','X','N') and new.status in ('R','C')) THEN FOR rsv IN (SELECT item_id, roomtype_id, arrival, departure, qty FROM booking_items WHERE booking_id = NEW.id) LOOP PERFORM sp_update_avail('RSV', 'Rebook', rsv.roomtype_id, rsv.arrival, rsv.departure-1, rsv.qty, NEW.channel_id, rsv.item_id, booking_id := NEW.id); END LOOP; END IF; /* Wait List, Canceled and No-show*/ IF (old.status in ('R','C') and new.status in ('W','X','N')) THEN activity_type = CASE (new.status) WHEN 'W' THEN 'Waitlist' WHEN 'X' THEN 'Cancel booking' WHEN 'N' THEN 'No-show' END; FOR rsv IN (SELECT item_id, roomtype_id, arrival, departure, qty FROM booking_items WHERE booking_id = new.id) LOOP PERFORM sp_update_avail('RSV', activity_type, rsv.roomtype_id, rsv.arrival, rsv.departure-1, -rsv.qty, OLD.channel_id, rsv.item_id, booking_id := OLD.id); END LOOP; END IF; END IF; RETURN NULL; END