/****** Object:  StoredProcedure [dbo].[OnlineAvailableTables2]    Script Date: 04/18/2012 14:06:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[OnlineAvailableTables2] 
  @AStartDateTime DATETIME
, @AEndDateTime DATETIME
, @AHotelNumber INT = 0
, @APax INT = 0
, @ATableSize INT = 0 -- not in use!
, @ATableType varchar(4) = ''
, @DepartmentNumber varchar(3) = '-1'

AS

/*
SG/2016-07-26: <= >= rettet tilbage til < > !
SG/2016-07-26: Fjernet betingelsen GuestNo = 0. Man kan godt bo på hotellet og reservere et bord i restauranten
SG/2016-07-21: < > ændret til <= >=
SG/2015-06-10: DepartmentNumber may be a string
SG/2013-11-06: New optional parameter @DepartmentNumber. If used, tables are connected to departments.
LJE/20120419: with (NO LOCK).
LJE/20120418: If @ATableType is '' - show all tabletypes. Also show TableType in final result. 
LJE/20120326: TableCur, Rescur (cursors) changed to: "LOCAL FORWARD_ONLY FOR".
LJE/20111103: Find result using TableType.
LJE/20111102: Find several ResourceId with same pax; "ResourceID in".
LJE/20111028: and (s.caption like '%brunch%' ...) -- must be a tablebooking instance.
LJE/20111025: Check also for TableSize=3 - IF @ATableSize IN (1,2,3,4,5)
LJE/20110927: Check also for TableSize=5 - IF @ATableSize IN (1,2,4,5)
LJE/20110803: AMealType is not necessary.
LJE/20110623: copy OnlineAvailableTables
*/

  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

  --DECLARE @ResourceID INT   -- ResourceID from Schedule-table, i.e. 9, 10, 11 (bord til 1, 2, 4).
  --SET @ResourceID = 1
   
  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
  LEFT OUTER JOIN TableTypeDepXtab x ON ConferenceRooms.ConferenceRoomTypeNumber = x.TableTypeNumber
  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 (@ResourceID = 0 OR (@ResourceID > 0 AND ConferenceRooms.ConferenceRoomTypeNumber in  --= @ResourceID))   
  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))
  and (x.DepartmentNumber = @DepartmentNumber OR @DepartmentNumber = '-1')
   
--  AND (@ATypes = '' OR (@ATypes <> '' AND ConferenceRooms.ConferenceRoomTypeNumber IN (@ATypes)))     
--  AND DBO.IsHallTypeInList(ConferenceRooms.ConferenceRoomTypeNumber, @ATypes) = 1
--  AND DBO.IsHallTypeInList(ConferenceRooms.ConferenceRoomTypeNumber, '9,10,11') = 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 FORWARD_ONLY FOR--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 (@ResourceID = 0 OR (@ResourceID > 0 AND ConferenceRooms.ConferenceRoomTypeNumber in  --= @ResourceID))   
  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 ((@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 , TableType 
  FROM #AvailableTables with (NOLOCK) 
  ORDER BY TableNumber
 
  DROP TABLE #AvailableTables
  