aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_tr_room_block_avail_change
Parameters
Name
Type
Mode
Definition
DECLARE v_room_id int; v_channel_id int; rsv_roomtype int; old_roomtype int; new_roomtype int; BEGIN CASE TG_OP WHEN 'INSERT' THEN SELECT bi.roomtype_id, b.channel_id FROM booking_items bi inner join booking b on b.id = bi.booking_id WHERE bi.item_id = new.booking_item INTO rsv_roomtype, v_channel_id; new_roomtype := (select r.roomtype_id from room r where r.id = new.room_id); IF (rsv_roomtype <> new_roomtype) THEN PERFORM sp_update_avail ('RSV', 'Block', rsv_roomtype, new.arrival, new.departure-1, -1, v_channel_id, new.booking_item, new.room_id, booking_id := new.booking_id); PERFORM sp_update_avail ('BLK', 'Block', new_roomtype, new.arrival, new.departure-1, 1, v_channel_id, new.booking_item, new.room_id, booking_id := new.booking_id); END IF; -- update booking set blockqty = coalesce(blockqty,0)+1 -- where accno = new.accno and seq = new.dtlseq; WHEN 'UPDATE' THEN IF (old.room_id <> new.room_id or old.arrival <> new.arrival or old.departure <> new.departure) THEN old_roomtype := (select r.roomtype_id from room r where r.id = old.room_id); new_roomtype := (select r.roomtype_id from room r where r.id = new.room_id); IF (old_roomtype <> new_roomtype or old.arrival <> new.arrival or old.departure <> new.departure) THEN v_channel_id := (select b.channel_id from booking b where b.id = new.booking_id); PERFORM sp_update_avail ('BLK', 'Update block', old_roomtype, old.arrival, old.departure-1, -1, v_channel_id, old.booking_item, old.room_id, booking_id := old.booking_id); PERFORM sp_update_avail ('BLK', 'Update block', new_roomtype, new.arrival, new.departure-1, 1, v_channel_id, new.booking_item, new.room_id, booking_id := new.booking_id); END IF; END IF; -- IF old.booking_item <> new.booking_item) THEN -- update booking set block_qty = coalesce(block_qty,0)-1 -- where accno = new.accno and seq = old.dtlseq; -- -- update booking set blockqty = coalesce(blockqty,0)+1 -- where accno = new.accno and seq = new.dtlseq; -- END IF; WHEN 'DELETE' THEN SELECT bi.roomtype_id, b.channel_id FROM booking_items bi inner join booking b on b.id = bi.booking_id WHERE bi.item_id = old.booking_item INTO rsv_roomtype, v_channel_id; old_roomtype := (select r.roomtype_id from room r where r.id = old.room_id); IF (rsv_roomtype <> old_roomtype) THEN PERFORM sp_update_avail ('BLK', 'Unblock', old_roomtype, old.arrival, old.departure-1, -1, v_channel_id, old.booking_item, old.room_id, booking_id := old.booking_id); PERFORM sp_update_avail ('RSV', 'Unblock', rsv_roomtype, old.arrival, old.departure-1, 1, v_channel_id, old.booking_item, old.room_id, booking_id := old.booking_id); END IF; -- update booking set blockqty = coalesce(blockqty,0)-1 -- where accno = old.accno and seq = old.dtlseq and coalesce(blockqty,0) > 0; END CASE; RETURN NULL; END