CREATE PROCEDURE dbo.OnlineAvailableTables @AStartDateTime datetime, @AEndDateTime Datetime, @AHotelNumber INT = -1, @AType INT = 0, @APax INT = 0, @ATypes VARCHAR(100) = '' AS

--Created 2009 0106 AV. 
/*
  Last Changed: 
  AV 2009 0109 use conferencerooms.conferenceroomtypenumber instead of reservationconferenceline.bookedConferenceroomtypenumber
  AV + RN 2009 0618 Added Color and Hotelnumber to returning dataset
  AV 2009 0619 call of udf DBO.IsHallTypeInList
*/

  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
   
  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 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 (@AType = 0 OR (@AType > 0 AND ConferenceRooms.ConferenceRoomTypeNumber = @AType))   
  AND (@APax = 0 OR (@APax > 0 AND ConferenceRooms.Pax >= @APax))   
--  AND (@ATypes = '' OR (@ATypes <> '' AND ConferenceRooms.ConferenceRoomTypeNumber IN (@ATypes)))     
  AND DBO.IsHallTypeInList(ConferenceRooms.ConferenceRoomTypeNumber, @ATypes) = 1

  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 FAST_FORWARD FOR
  SELECT DISTINCT ConferenceRoomsXtab.CollectsConferenceRoomNumber  FROM ReservationConferenceLine WITH (NOLOCK)  INNER JOIN RESERVATIONS WITH (NOLOCK) 
  ON RESERVATIONCONFERENCELINE.ReferenceNumber = RESERVATIONS.ReferenceNumber
  LEFT OUTER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  INNER JOIN CONFERENCEROOMSXTAB WITH (NOLOCK) 
  ON ConferenceRoomsxtab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber 
  INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRoomsXtab.CollectsConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
  WHERE 
  Reservations.GuestNumber = 0  AND 
  ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0 AND Reservations.Status < 7 AND TableStatus < 6
  AND (@AType = 0 OR (@AType > 0 AND ConferenceRooms.ConferenceRoomTypeNumber = @AType))   
  AND ((@AHotelNumber = -1) OR (@AHotelNumber > -1 AND ReservationMain.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 TableNumberId, TableNumber, TableName, TableTypeNumber, MaxPax, Color, HotelNumber  FROM #AvailableTables WITH (NOLOCK) ORDER BY TableNumber

  DROP TABLE #AvailableTables
