aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    old_roomtype int;
    new_roomtype int;
    v_room_id int;
    old_arrdate date;
    old_depdate date;
    new_arrdate date;
    new_depdate date;
    cur_arrdate date;
    cur_depdate date;
    rsv_roomtype int;
    rsv_arrdate date;
    rsv_depdate date;
    rsv_status text;
BEGIN

   new_roomtype := (select rm.roomtype_id from room rm where rm.id = new.room_id);

   /* Get previous occpied period of this room */
   SELECT min(rg.arrival), max(rg.departure)-1
   FROM registration rg
   WHERE (rg.status = 'I') and (rg.room_id = new.room_id) and (rg.id <> old.id)
   INTO cur_arrdate, cur_depdate;

   new_arrdate = least(new.arrival ,coalesce(cur_arrdate, new.arrival));
   new_depdate = greatest(new.departure-1, coalesce(cur_depdate, new.departure-1));

   /* Check in */
   IF (TG_OP = 'INSERT') THEN
      IF new.booking_item is not null THEN
        SELECT IIF(rb.id is null, 'RSV', 'BLK'),
               coalesce(rm.roomtype_id, bi.roomtype_id),
               bi.arrival, bi.departure-1
        FROM booking_items bi
            left join room_block rb on rb.booking_item = bi.item_id and rb.room_id = new.room_id --rb.register_id = new.id
            left join room rm on rm.id = rb.room_id
        WHERE bi.item_id = new.booking_item
        INTO rsv_status, rsv_roomtype, rsv_arrdate, rsv_depdate;

        PERFORM sp_log_debug('sp_tr_registration_avail_change',
                format(E'Check In> %L, type: %L, period: %L - %L', rsv_status, rsv_roomtype, rsv_arrdate, rsv_depdate));

        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;

      /* Remove previous occupied of this room */
      IF (cur_arrdate is not null) THEN
         PERFORM sp_update_avail
         ('INH','Check in', new_roomtype, cur_arrdate, cur_depdate, -1, new.channel_id, null, new.room_id, new.id);
      END IF;

      /* Add new occupied period */
      PERFORM sp_update_avail
      ('INH','Check in', new_roomtype, new_arrdate, new_depdate, 1, new.channel_id, null, new.room_id, new.id);
   END IF;


   /* Modify room number, channel, arrival or departure */
   IF (TG_OP = 'UPDATE') THEN
       old_roomtype := (select rm.roomtype_id from room rm where rm.id = old.room_id);
       old_arrdate  := least(old.arrival, coalesce(cur_arrdate, old.arrival));
       old_depdate  := greatest(old.departure-1, coalesce(cur_depdate, old.departure-1));

       PERFORM sp_log_debug('sp_tr_registration_avail_change',
                            format(E'Old> %L, type: %L, period: %L - %L, occ: %L - %L\nNew> %L, type: %L, period: %L - %L, occ: %L - %L',
                                   old.status, old_roomtype, old.arrival, old.departure, old_arrdate, old_depdate,
                                   new.status, new_roomtype, new.arrival, new.departure, new_arrdate, new_depdate));

       IF (old.status = 'I' and new.status = 'I')
            and (old.room_id <> new.room_id
                or old_arrdate <> new_arrdate
                or old_depdate <> new_depdate
                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_depdate <> old_depdate)
          THEN
             PERFORM sp_update_avail('INH','Update',
                old_roomtype, old_arrdate, old_depdate, -1, old.channel_id, null, old.room_id, old.id);
             PERFORM sp_update_avail('INH','Update',
                new_roomtype, new_arrdate, new_depdate,  1, new.channel_id, null, new.room_id, new.id);
         END IF;

       /* Cancel checkin */
       ELSIF (old.status = 'I' and new.status = 'X' ) then

          /* Single registration in this room */
          if (cur_depdate is null) then
             PERFORM sp_update_avail('INH','Cancel check-in',
               old_roomtype, old.arrival, old.departure-1, -1, old.channel_id, null, old.room_id, old.id);

          /* Multiple accounts in this room */
          elsif (new.departure-1 >= old_depdate) then
               PERFORM sp_update_avail('INH','Cancel check-in',
                 old_roomtype, old_arrdate, old_depdate, -1, old.channel_id, null, old.room_id, old.id);
               PERFORM sp_update_avail('INH','Cancel check-in',
                 new_roomtype, new_arrdate, new_depdate, 1, new.channel_id, null, new.room_id, new.id);
          end if;

          /* Restore RSV/BLK */
          IF new.booking_item is not null THEN
             SELECT IIF(rb.id is null, 'RSV', 'BLK'),
                   coalesce(rm.roomtype_id, bi.roomtype_id),
                   bi.arrival, bi.departure-1
             FROM booking_items bi
                left join room_block rb on rb.booking_item = bi.item_id and rb.register_id = new.id
                left join room rm on rm.id = rb.room_id
             WHERE bi.item_id = new.booking_item
             INTO rsv_status, rsv_roomtype, rsv_arrdate, rsv_depdate;

             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;

       /* Early check out */
       ELSIF (old.status = 'I' and new.status = 'O' and old.departure <> new.departure) then
          PERFORM sp_update_avail
          ('INH','Early checkout', old_roomtype, old.arrival, old.departure-1, -1, old.channel_id, null, old.room_id, old.id);
--          PERFORM sp_update_avail
--          ('INH','Early checkout', new_roomtype, new.arrival, new.departure,  1, new.channel_id, null, new.room_id, new.id);

       /* Re-checkin with departure changed */
       ELSIF (old.status = 'O' and new.status = 'I' and new.departure <> old.departure) then
--          PERFORM sp_update_avail
--          ('INH','Cancel checkout', old_roomtype, old.arrival, old.departure, -1, old.channel_id, null, old.room_id, old.id);
          PERFORM sp_update_avail
          ('INH','Cancel checkout', new_roomtype, new.arrival, new.departure-1,  1, new.channel_id, null, new.room_id, new.id);

       END IF;

   END IF;
   RETURN NULL;
END