/****** Object:  StoredProcedure [dbo].[OnlineAvailableTables3]    Script Date: 04/19/2012 12:06:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[OnlineAvailableTables3] 
  @AStartDateTime DATETIME
, @AEndDateTime DATETIME
, @AHotelNumber INT = 0
, @APax INT = 0
, @ATableType varchar(4) = ''

AS

/*
LJE/20120423 Roy did optimization.
LJE/20120423 Only check tables.
LJE/20120419 Created. Based on OnlineAvailableTables2 - but only return number of free tables in "allocated section".

*/

  DECLARE
  @TableNumberId INT,
  @TableNumber INT,
  @MaxPax INT,
  @TableTypeNumber INT,
  @TableType VARCHAR(5),
  @TableName VARCHAR(20),
  @StartTime VARCHAR(5),
  @EndTime VARCHAR(5),
  @HotelNumber INT,
  @Color INT,
  @RowCount INT
   
  SET @StartTime = CONVERT(VARCHAR(5),@AStartDateTime,108)   
  SET @EndTime = CONVERT(VARCHAR(5),@AEndDateTime,108) 

  CREATE TABLE #AvailableTables (
		TableNumberId INT NOT NULL,
 		TableNumber INT NOT NULL,
 		TableName VARCHAR(20),
        TableTypeNumber INT,
        TableType VARCHAR(5),
        MaxPax INT,
        Color INT,
        HotelNumber INT)

  DECLARE #TableCur CURSOR LOCAL FORWARD_ONLY FOR--LOCAL FAST_FORWARD FOR
  SELECT DISTINCT ConferenceRooms.ConferenceRoomNumber
    , ConferenceRooms.Number
    , ConferenceRooms.Name
    , ConferenceRooms.ConferenceRoomTypeNumber
    , ConferenceRoomTypes.Type
    , (CASE WHEN ISNULL(@APax, 0) = 0 THEN NULL ELSE ConferenceRooms.Pax END) AS Pax
    , Color, ConferenceRooms.HotelNumber
  FROM ConferenceRooms with (NOLOCK)
  INNER JOIN ConferenceRoomTypes with (NOLOCK) ON ConferenceRooms.ConferenceRoomTypeNumber = ConferenceRoomTypes.ConferenceRoomTypeNumber
  WHERE ISNULL(IsTable,0) = 1 AND CONVERT(CHAR(8),ConferenceRooms.TableDate, 112) = CONVERT(VARCHAR(8), @AStartDateTime,112)      
  AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ConferenceRooms.HotelNumber = @AHotelNumber))
  AND ConferenceRoomTypes.ResourceType = 8 -- Only look for: 8="Table Schedule".
  AND (@ATableType = '' OR ConferenceRoomTypes.Type = @ATableType )
  /*
  AND ConferenceRooms.ConferenceRoomTypeNumber in
     (
     select distinct s2.ResourceID from Schedule s2, ConferenceRoomTypes c2  
     where s2.ResourceId = c2.ConferenceRoomTypeNumber
		AND (CHARINDEX('Eat',s2.Caption) > 0 
		  OR CHARINDEX('Brunch',s2.Caption) > 0 
		  OR CHARINDEX('Lunch',s2.Caption) > 0 
		  OR CHARINDEX('Dinner',s2.Caption) > 0)
        AND (@ATableType = '' OR c2.Type = @ATableType)
     )
     */
  AND (@APax = 0 OR (@APax > 0 AND ConferenceRooms.Pax >= @APax))   

  OPEN #TableCur
  FETCH NEXT FROM #TableCur
  INTO @TableNumberId, @TableNumber, @TableName, @TableTypeNumber, @TableType, @MaxPax, @Color, @HotelNumber

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    INSERT INTO #AvailableTables (TableNumberID, TableNumber, TableName, TableTypeNumber, TableType, MaxPax, Color, HotelNumber ) VALUES (@TableNumberID,@TableNumber, @TableName,  @TableTypeNumber, @TableType, @MaxPax, @Color, @HotelNumber)
    FETCH NEXT FROM #TableCur
    INTO @TableNumberId, @TableNumber, @TableName, @TableTypeNumber, @TableType, @MaxPax, @Color, @HotelNumber
  END

  CLOSE #TableCur 
  DEALLOCATE #TableCur

  DECLARE #Rescur CURSOR LOCAL FORWARD_ONLY FOR--LOCAL FAST_FORWARD FOR
  SELECT ReservationConferenceLine.ConferenceRoomNumber
  FROM ReservationConferenceLine with (NOLOCK)  
  INNER JOIN ConferenceRoomTypes WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = ReservationConferenceLine.BookedConferenceRoomTypeNumber
    AND ConferenceRoomTypes.Resourcetype = 8
  INNER JOIN ConferenceRooms with (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
    
  WHERE 
  ReservationConferenceLine.Tablebooking = 1 AND  -- Only check tables.
  ReservationConferenceLine.GuestNo = 0  AND 
  ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0 /*AND Reservations.Status < 7*/ AND TableStatus < 6
  AND ConferenceRooms.IsTable = 1
  AND ((@AHotelNumber = -1) OR (@AHotelNumber > -1 AND ConferenceRooms.HotelNumber = @AHotelNumber))
  AND CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) < DATEADD(MINUTE, ISNULL(ConferenceRooms.PauseInMinutes,0),@AEndDateTime )
  AND CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) > DATEADD(MINUTE,- ISNULL(ConferenceRooms.PauseInMinutes,0),@AStartDateTime)

  OPEN #Rescur

  FETCH NEXT FROM #Rescur
  INTO @TableNumberId


  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    DELETE FROM #AvailableTables WHERE TableNumberID = @TableNumberID
    FETCH NEXT FROM #Rescur
    INTO @TableNumberId
  END

  CLOSE #Rescur
  DEALLOCATE #Rescur


  SELECT @RowCount = count(*)
  FROM #AvailableTables with (NOLOCK) 
 
  DROP TABLE #AvailableTables
  
  return @RowCount
 
