aonestar.public

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

sp_tr_room_block_avail_change

Parameters

Definition