CREATE PROCEDURE DBO.CheckInOutTables AS 
SET NOCOUNT ON

DECLARE @SystemDate Char(8)
Declare @Refno int
declare @guestno int
declare @Status int
declare @tablestatus int
DECLARE @NewFollowUpNumber INT
DECLARE @SystemDateTime DATETIME
DECLARE @Text char (75)
DECLARE @UseridNumber int
Select @SystemDate = SystemDate from System WITH (nolock)
Select @useridNumber = useridnumber from personel WITH (nolock) where useridcode = 'TECHOTEL' and (markasdeleted is null or markasdeleted =0)

----CHECKIN START----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE ResCur CURSOR FORWARD_ONLY FOR
Select distinct Referencenumber,GuestNumber,status from Reservations WITH (nolock) where (status < 5 and referencenumber in (select referencenumber from reservationConferenceLine WITH (nolock) where arrivaldate=@SystemDate)) or (status = 5)
OPEN ResCur

FETCH NEXT FROM ResCur
INTO @refno,@GuestNo,@Status

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  update ReservationConferenceLine set TableStatus = 5 where referencenumber=@RefNo and isnull(Guestno,0) = @Guestno and tableBooking=1 and tableStatus < 5 and arrivaldate <= @SystemDate

  if @status < 5
  begin
     Update Reservations set status = 5 where referencenumber=@Refno and guestnumber= @GuestNo

     SELECT @SystemDateTime = CONVERT (DATETIME, @SystemDate, 112)+CONVERT (CHAR, GetDate(), 108)
     set @Text = 'Checkin'
     INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType2)
     VALUES (@SystemDateTime, 5, 12, 3, @Text, @SystemDateTime, @UserIDNumber, 4)
     SELECT @NewFollowUpNumber = @@identity
      print @newfollowupnumber 
     INSERT INTO ReservationFollowUpXTab (Referencenumber, GuestNumber, FollowUpNumber) VALUES (@RefNo, @GuestNo, @NewFollowUpNumber)
  end
  FETCH NEXT FROM ResCur
  INTO @refno,@guestno,@Status
END

CLOSE ResCur
DEALLOCATE ResCur

----CHECKIN END----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----CHECKUD START----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @DoCheckOut bit
DECLARE ResCur CURSOR FORWARD_ONLY FOR
Select distinct Referencenumber,GuestNumber from Reservations WITH (nolock) where status = 5
OPEN ResCur

FETCH NEXT FROM ResCur
INTO @refno,@GuestNo

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  update ReservationConferenceLine set TableStatus = 6 where referencenumber=@RefNo and isnull(Guestno,0) = @Guestno and tableBooking=1 and tableStatus = 5 and departuredate <= @SystemDate

  set @DoCheckOut = 1   

  If exists (select tablestatus from reservationConferenceLine where ReferenceNumber=@Refno and isnull(Guestno,0) = @Guestno and TableBooking=1 and TableStatus < 6)
    set @DoCheckOut = 0 

  If exists (select ReservationConfLineNumber from reservationConferenceLine where ReferenceNumber=@Refno and isnull(Guestno,0) = @Guestno and  IsNull(TableBooking,0) =0 and Departuredate > @SystemDate )
    set @DoCheckOut = 0

  if @DoCheckOut = 1
  begin
     Update Reservations set status = 6 where referencenumber=@Refno and guestnumber= @GuestNo
     SELECT @SystemDateTime = CONVERT (DATETIME, @SystemDate, 112)+CONVERT (CHAR, GetDate(), 108)
     set @Text = 'CheckOut'
     INSERT INTO FollowUp ([Date], Type, Priority, State, Note, CreatedDate, CreatedBy, SubType2)
     VALUES (@SystemDateTime, 5, 12, 3, @Text, @SystemDateTime, @UserIDNumber, 6)
     SELECT @NewFollowUpNumber = @@identity
    print @newfollowupnumber
     INSERT INTO ReservationFollowUpXTab (Referencenumber, GuestNumber, FollowUpNumber) VALUES (@RefNo, @GuestNo, @NewFollowUpNumber)
  end
  FETCH NEXT FROM ResCur
  INTO @refno,@guestno
END

CLOSE ResCur
DEALLOCATE ResCur

----CHECKUD END----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

