/****** Object:  StoredProcedure [dbo].[OnlineGetNotAllocatedTables]    Script Date: 11/03/2011 15:16:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[OnlineGetNotAllocatedTables] 
  @AStartDateTime DATETIME
, @AEndDateTime DATETIME
, @AHotelNumber INT = 0
, @ATableSize INT = 0 -- not in use!
, @ATableType varchar(4)

AS

/*
LJE/20111103: Find result using TableType.
LJE/20110817: Created, CR26539.
*/

select @AStartDateTime = DATEADD(minute,1,@AStartDateTime)
select @AEndDateTime = DATEADD(minute,0,@AEndDateTime)

SELECT COUNT(*)  AS CountNotAllocatedTables
FROM ReservationConferenceLine RCL WITH (NOLOCK) 
INNER JOIN RESERVATIONS R WITH (NOLOCK) 
  ON RCL.ReferenceNumber = R.ReferenceNumber AND
     RCL.TableBooking = 1 AND
     R.GuestNumber = ISNULL(RCL.GuestNo, 0)
LEFT OUTER JOIN ConferenceRooms CR WITH (NOLOCK) 
  ON RCL.conferenceroomNumber = CR.ConferenceRoomNumber
LEFT OUTER JOIN ConferenceRoomsXtab CRX WITH (NOLOCK) 
  ON CR.ConferenceRoomNumber = CRX.ConferenceRoomNumber
INNER JOIN ReservationMain RM WITH (NOLOCK) 
  ON RM.Referencenumber = R.Referencenumber
INNER JOIN ConferenceRoomTypes CRT WITH (NOLOCK) 
  ON CRT.ConferenceRoomTypeNumber = RCL.BookedConferenceRoomTypeNumber
WHERE 
  (LinkLineNumber IS NULL) 
  AND (CONVERT(DATETIME, RCL.ArrivalDate, 112) + CONVERT(DATETIME, RCL.ArrivalTime, 108) <= @AEndDateTime) 
  AND (CONVERT(DATETIME, RCL.DepartureDate, 112) + CONVERT(DATETIME, RCL.DepartureTime, 108) >= @AStartDateTime) 
  AND (RCL.TableStatus IN (0, 1, 2, 3, 4, 5)) 
  AND (IsNull(RM.HotelNumber, 0) = @AHotelNumber) 
  AND (CRT.Type = @ATableType)
  AND (IsNull(RCL.ConferenceRoomNumber, 0) = 0)

GO


