CREATE PROCEDURE [dbo].[OnlineGetPaxReservationOnDateTime]    
  @AArrivalDateTime DATETIME
AS
/*
    Changes:     
	  LJE20120411   Take all ResourceType="Table Schedule" (value=8) into consideration when calculating.
	  LJE20111010   When using OnlineOnArrivalBuffer - only look forward in time: BETWEEN DATEADD(minute,0,...) AND DATEADD(minute,@Buffer,...).
      LJE20110927	Created.
      
	Notes:       
	  Get number of pax and number of reservations for a specific date time, i.e.
	    time 18:00 - you may find 10 guests already checking-in for a table booking.
	               - you may find 5 reservations already made.
	  Take all "Table Schedule" resource types (value=8) into consideration - not only items found in
	  scheduler.			   
	  
	Example:
	  exec OnlineGetPaxReservationOnDateTime  '2011-10-01 12:00'
*/

DECLARE @CurrentNumberOfPaxOnDateTime INT
DECLARE @CurrentNumberOfReservationOnDateTime INT
DECLARE @BookedConferenceRoomTypeNumber INT
DECLARE @ArrivalDate VARCHAR(8)
DECLARE @ArrivalTime VARCHAR(8)
DECLARE @Adults INT
DECLARE @Children1 INT
DECLARE @Children2 INT
DECLARE @ReferenceNumber INT
DECLARE @Buffer INT

select @Buffer = KeyValue from HOTELSETUP where searchkey = 'OnlineOnArrivalBuffer' 
set @Buffer = ISNULL(@Buffer,0) -- Extend search criteria? Buffer in minutes. Default is 0 minutes.

  CREATE TABLE #TempTable (
    ArrivalDateTime DATETIME
    ,ConferenceRoomTypeNumber INT
    ,Pax INT
    ,ReferenceNumber INT
  )
  
 DECLARE #TempCursor CURSOR FAST_FORWARD READ_ONLY FOR
   SELECT ArrivalDate,ArrivalTime,BookedConferenceRoomTypeNumber,Adults,Children1,Children2
      ,ReferenceNumber 
     FROM ReservationConferenceLine rcl
     WHERE rcl.BookedConferenceRoomTypeNumber
       IN (
	   /*SELECT DISTINCT ResourceID 
           FROM Schedule 
           WHERE CHARINDEX('Eat',Caption) = 1
             OR CHARINDEX('Brunch',Caption) = 1
             OR CHARINDEX('Lunch',Caption) = 1
             OR CHARINDEX('Dinner',Caption) = 1
			 */
			SELECT ConferenceRoomTypeNumber 
			FROM CONFERENCEROOMTYPES WITH (NOLOCK)   
			where ResourceType = 8  -- Only look for: 8="Table Schedule".
           )
       -- AND rcl.BookedConferenceRoomTypeNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AConferenceRoomTypeNumberList) where number >= 0)
       AND CONVERT(datetime,CONVERT(CHAR(8),ArrivalDate, 112) + ' ' + CONVERT(CHAR(5),ArrivalTime,108))   
       BETWEEN  DATEADD(minute,0,@AArrivalDateTime) AND DATEADD(minute,@Buffer,@AArrivalDateTime)
       AND rcl.TableStatus != 7  -- Bypass cancelled reservations/7.
    
 OPEN #TempCursor
    FETCH NEXT FROM #TempCursor
    INTO @ArrivalDate,@ArrivalTime,@BookedConferenceRoomTypeNumber,@Adults,@Children1,@Children2,@ReferenceNumber
    WHILE (@@FETCH_STATUS = 0)
    BEGIN

       INSERT INTO #TempTable (ArrivalDateTime,ConferenceRoomTypeNumber,Pax,ReferenceNumber) 
       values (
        CONVERT(datetime,CONVERT(CHAR(8),@ArrivalDate, 112) + ' ' + CONVERT(CHAR(5),@ArrivalTime,108))
        , @BookedConferenceRoomTypeNumber
        , 0 + @Adults + @Children1 + @Children2
        , @ReferenceNumber)
    
      FETCH NEXT FROM #TempCursor
          INTO @ArrivalDate,@ArrivalTime,@BookedConferenceRoomTypeNumber,@Adults,@Children1,@Children2,@ReferenceNumber
    END

 CLOSE #TempCursor

 --SELECT * from #TempTable  
 --SELECT @CurrentNumberOfReservationOnDateTime = COUNT(*) FROM #TempTable
 SELECT @CurrentNumberOfReservationOnDateTime = COUNT(*) 
 FROM 
 (SELECT DISTINCT ReferenceNumber FROM #TempTable) as InnerQuery -- Select number of rows having different ReferenceNumber.

 SELECT @CurrentNumberOfPaxOnDateTime = SUM(Pax) FROM #TempTable
 
 SELECT ISNULL(@CurrentNumberOfPaxOnDateTime,0) as CurrentNumberOfPaxOnDateTime
   ,@CurrentNumberOfReservationOnDateTime as CurrentNumberOfReservationOnDateTime
 
DEALLOCATE #TempCursor      
DROP TABLE #TempTable 





