CREATE PROCEDURE dbo.TableIsOverBooked @AStartDateTime DATETIME, @AEndDateTIME DATETIME, @APax INT, @AResult BIT OUTPUT, @AHotelNumber INT = -1, @AType INT = 0  AS

--Created AV 2008 1112 used in DGI online bowling booking
--Last changed: AV 2009 0618 read maxpax from scheduler table, not hotelsetup value

/*
DECLARE @TotalNumberOfPax INT
DECLARE @FoundPax INT

SELECT @TotalNumberOfPax = ISNULL(KeyValue, 0) FROM HotelSetup (NOLOCK) WHERE SearchKey = 'MaxPaxTotalTables'

SELECT @FoundPax = ISNULL(SUM (ISNULL(ReservationConferenceLine.Adults,0) + ISNULL(ReservationConferenceLine.Children1, 0) + ISNULL (ReservationConferenceLine.Children2, 0)),0)   FROM ReservationConferenceLine (NOLOCK)
INNER JOIN Reservations (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber AND ISNULL(ReservationConferenceLine.GuestNo, 0) = Reservations.GuestNumber 
LEFT OUTER JOIN ReservationMain (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationConferenceLine.ReferenceNumber
WHERE Tablebooking = 1 AND Reservations.Status < 7 AND
(@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber)) AND
CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) < @AEndDateTime 
AND CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > @AStartDateTime


SET @AResult = 0

IF @FoundPax + @APax > @TotalNumberOfPax
  SET @AResult = 1
*/

DECLARE @TotalNumberOfPax INT
DECLARE @FoundPax INT
DECLARE @Found BIT
DECLARE @AOpeningDateTime DATETIME
DECLARE @ACloseDateTime DATETIME


--SELECT @TotalNumberOfPax = ISNULL(KeyValue, 0) FROM HotelSetup (NOLOCK) WHERE SearchKey = 'MaxPaxTotalTables'
SET @AResult = 0

EXEC dbo.GetSchedulerExpressData @AStartDateTime, @AType,  @Found OUTPUT, @TotalNumberOfPax OUTPUT, @AOpeningDateTime OUTPUT, @ACloseDateTime OUTPUT
IF @Found = 0
  SET @TotalNumberOfPax = 0

  SELECT @FoundPax = ISNULL(SUM (ISNULL(ReservationConferenceLine.Adults,0) + ISNULL(ReservationConferenceLine.Children1, 0) + ISNULL (ReservationConferenceLine.Children2, 0)),0)   FROM ReservationConferenceLine WITH (NOLOCK)
  INNER JOIN Reservations WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber AND ISNULL(ReservationConferenceLine.GuestNo, 0) = Reservations.GuestNumber 
  LEFT OUTER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationConferenceLine.ReferenceNumber 
  LEFT OUTER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
  WHERE Tablebooking = 1 AND Reservations.Status < 7 AND ISNULL(ReservationConferenceLine.TableStatus, 0) < 6 AND
  (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber)) AND
  CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) < @AEndDateTime 
  AND CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > @AStartDateTime
  AND (
   (@AType > 0 AND ((ReservationConferenceLine.ConferenceRoomNumber IS NOT NULL AND ConferenceRooms.ConferenceRoomTypeNumber = @AType) OR (ReservationConferenceLine.ConferenceRoomNumber IS NULL AND ReservationConferenceLine.BookedConferenceRoomTypeNumber = @AType)))
  OR (@AType = 0)
  )

IF @FoundPax + @APax > @TotalNumberOfPax
  SET @AResult = 1
