/****** Object:  StoredProcedure [dbo].[OnlineAvailableTables4]    Script Date: 06/11/2012 11:08:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[OnlineAvailableTables4] 
  @AStartDateTime DATETIME
, @AEndDateTime DATETIME
, @AHotelNumber INT = 0
, @APax INT = 0
, @DepartmentNumber Varchar(2) = '-1'

AS

/*
SG/2016-07-26: Fjernet betingelsen GuestNo = 0. Man kan godt bo på hotellet og reservere et bord i restauranten
SG/2016-07-21: Justeringer i availability.
SG/2016-05-26: Rettet, så der også tages hensyn til res. på "skæve" tider oprettet i Picasso. Samme resultater som ver. 2015-07-13 
SG/2016-05-10: Fjernet cursors, kombineret tider i SELECT i stedet for WHILE loop. Tid reduceret ca. 85%
SG/2015-07-13: #TableCur kun åbnet én gang. Antal felter reduceret. Ca. 20% reduceret tid.
SG/2015-05-28: @DepartmentNumber changed to varchar
SG/2013-11-06: New optional parameter @DepartmentNumber. If used, tables are connected to departments.
SG/2013-10-11: Called once from OnlineGetTableBookingAvailability instead of once per timeslot * tableType
LJE/20120611 code inherited from OnlineAvailableTables3 - xtab inserted for : Rescur - GuestNo may be NULL.
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),
@HotelNumber INT,
@CurrentStartDateTime Datetime,
@CurrentEndDateTime Datetime
DECLARE @DefaultTableBookingMinutes INT
select @DefaultTableBookingMinutes = KeyValue from HOTELSETUP where searchkey = 'DefaultTableBookingMinutes'
DECLARE @IntervalBetweenArrivals INT
select @IntervalBetweenArrivals = KeyValue from HOTELSETUP where searchkey = 'OnlineIntervalBetweenArrivals'

--TABLE #AvailableTables  Created in OnlineGetTableBookingAvailability

Declare @StartDate Datetime
SET @StartDate = CONVERT(VARCHAR(8), @AStartDateTime,112) --112 = yyyymmdd (date only)

-- Loop thru all check-in-times.  
SET @CurrentStartDateTime = @AStartDateTime
Declare @AllStartTimes varchar(max)
SET @AllStartTimes = Convert(Varchar(24), @CurrentStartDateTime)

SET @CurrentStartDateTime = DATEADD(minute,@IntervalBetweenArrivals,@CurrentStartDateTime)

WHILE (@CurrentStartDateTime <= @AEndDateTime)
BEGIN
  Set @AllStartTimes = @AllStartTimes + ',' + Convert(Varchar(24), @CurrentStartDateTime)  --(no spaces) ISO8601

  SET @CurrentStartDateTime = DATEADD(minute,@IntervalBetweenArrivals,@CurrentStartDateTime)
END
--print @AllStartTimes
  
  INSERT INTO #AvailableTables (TableNumberID, TableType, StartTime)
  SELECT DISTINCT ConferenceRooms.ConferenceRoomNumber
                        , ConferenceRoomTypes.Type
                        , Convert(DateTime, StartTimes.value)
  FROM ConferenceRooms with (NOLOCK)
  INNER JOIN ConferenceRoomTypes with (NOLOCK) ON ConferenceRooms.ConferenceRoomTypeNumber = ConferenceRoomTypes.ConferenceRoomTypeNumber
  LEFT OUTER JOIN TableTypeDepXtab x ON ConferenceRooms.ConferenceRoomTypeNumber = x.TableTypeNumber
  CROSS JOIN dbo.SplitListMax(@AllStartTimes) as StartTimes
  WHERE ISNULL(IsTable,0) = 1 
    AND CONVERT(CHAR(8),ConferenceRooms.TableDate, 112) = @StartDate      
    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 (@APax = 0 OR (@APax > 0 AND ConferenceRooms.Pax >= @APax))   
    and (x.DepartmentNumber = @DepartmentNumber OR @DepartmentNumber = '-1')

--  Print 'Efter INSERT INTO #AvailableTables  ' + Convert(char(25), GETDATE(), 114)

--SELECT * FROM #AvailableTables
  
-- Tidligere: Loop thru all check-in-times - again
  CREATE TABLE #Reservations (
		   RowID int IDENTITY(1, 1),
		   TableNumberId INT NOT NULL,
		   StartDateTime DateTime Not Null,
		   EndDateTime DateTime not Null)


--  Print 'Før INSERT INTO #Reservations: ' + Convert(char(25), GETDATE(), 114)

  INSERT INTO #Reservations (TableNumberId, StartDateTime, EndDateTime)
  SELECT DISTINCT ConferenceRoomsXtab.CollectsConferenceRoomNumber, 
                  ReservationConferenceLine.ArrivalDate + ' ' + ReservationConferenceLine.ArrivalTime,
			   ReservationConferenceLine.DepartureDate + ' ' + ReservationConferenceLine.DepartureTime
  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
  CROSS JOIN dbo.SplitListMax(@AllStartTimes) as StartTimes
  WHERE 
  ReservationConferenceLine.Tablebooking = 1  -- Only check tables.
  --AND ISNULL(ReservationConferenceLine.GuestNo,0) = 0 --AND 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 ReservationConferenceLine.ArrivalDate = @StartDate
                                                                                                                                                
  AND (
          --Start BETWEEN ResStart AND ResEnd
	  --OR End BETWEEN ResStart AND ResEnd
	  --OR (ResStart >= Start AND ResEnd <= End)
	  DATEADD(MINUTE,- ISNULL(ConferenceRooms.PauseInMinutes,0),StartTimes.Value)
	  BETWEEN
	    CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108)
	    AND
	    CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) 
	  OR
	  DATEADD(MINUTE, ISNULL(ConferenceRooms.PauseInMinutes,0) + @DefaultTableBookingMinutes, StartTimes.Value)
	  BETWEEN
	    CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108)
	    AND
	    CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108)
	  OR (
	  (CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112) + CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) >= DATEADD(MINUTE,- ISNULL(ConferenceRooms.PauseInMinutes,0),StartTimes.Value))
	   AND
	  (CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112) + CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) <= DATEADD(MINUTE, ISNULL(ConferenceRooms.PauseInMinutes,0) + @DefaultTableBookingMinutes, StartTimes.Value))
	  )
      )
  --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 (c2.Type = '' OR c2.Type = @ATableType)
    )


  -- Get the number of records in the temporary table
  Declare @NumberRecords Int;
  Declare @RowCount INT;
  Declare @ReservationStartDateTime DateTime
  Declare @ReservationEndDateTime DateTime

  SELECT @NumberRecords = @@ROWCOUNT
  SET @RowCount = 1

  --Print 'Efter INSERT INTO #Reservations:   ' + Convert(char(25), GETDATE(), 114) + ' Reservationer: ' + convert(char(3), @NumberRecords)

  WHILE @RowCount <= @NumberRecords
  BEGIN
    SELECT @TableNumberId = TableNumberId, @ReservationStartDateTime = StartDateTime, @ReservationEndDateTime = EndDateTime 
    FROM #Reservations 
    WHERE RowId = @RowCount
    
    --PRINT 'DELETE LOOP: @TableNumberId=' + convert(char(7), @TableNumberId) + ', @ReservationStartDateTime=' + convert(char(15),  @ReservationStartDateTime,108) + ', @ReservationEndDateTime=' + convert(char(15), @ReservationEndDateTime,108)

    DELETE FROM #AvailableTables 
    WHERE TableNumberID = @TableNumberID 
       AND (
	     @ReservationStartDateTime BETWEEN StartTime AND DateAdd(MINUTE, @DefaultTableBookingMinutes, StartTime)
		OR
		@ReservationEndDateTime  BETWEEN StartTime AND DateAdd(MINUTE, @DefaultTableBookingMinutes, StartTime)
		OR
		(@ReservationStartDateTime <= StartTime AND @ReservationEndDateTime >= DateAdd(MINUTE, @DefaultTableBookingMinutes, StartTime))
	      )
	  
    --print '@RowCount=' + convert(char(3), @RowCount) + ' @TableNumberId=' + convert(char(7), @TableNumberId)
    SET @RowCount = @RowCount + 1
  END

  --Print 'Efter DELETE FROM #AvailableTables:   ' + Convert(char(25), GETDATE(), 114)
--  Select * FROM #AvailableTables

-- DROP TABLE #AvailableTables don't drop, keep it for OnlineGetTableBookingAvailability
