/****** Object:  StoredProcedure [dbo].[OnlineGetTableBookingAvailability]    Script Date: 04/19/2012 12:08:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

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

AS

/*
SG/2021-10-19: Loop thru all check-in-times replaced. Much faster.
SG/2015-07-29: Call to OnlineAvailableTables4 with correct HotelNumber
SG/2015-07-13: No. of fields in #AvailableTables reduced
SG/2015-05-28: @DepartmentNumber changed to varchar
SG/2013-11-06: New optional parameter @DepartmentNumber. If used, tables are connected to departments (in OnlineAvailableTables4).
SG/2013-10-11: Calling OnlineAvailableTables4 once instead of once per timeslot * tableType
LJE/20120611: call to OnlineAvailableTables4 - not ...Tables3.
LJE/20120423: CRX.ConferenceRoomNumber removed.
LJE/20120418: Created.

Note:  Get table booking availability for a period with several intervals.
       Result will give you a list with all intervals 1000-1200, 1030-1230, ... - and
       for each line you can see "free tables" in allocated, "occupied tables" in not allocated.
*/

  CREATE TABLE #AvailableTables (
		TableNumberId INT NOT NULL,
        TableType VARCHAR(5),
        StartTime DateTime)

  CREATE TABLE #NotAllocatedTables (
        StartDateTime DATETIME NULL
        ,EndDateTime DATETIME NULL
        ,TableType VARCHAR(4) NULL
        ,OccupiedNotAllocatedTables INT NULL  --OccupiedNotAllowedTables as in C# is easier to understand!
        ,FreeAllocatedTables INT NULL
        )
  
  DECLARE @CounterAllocated INT
  DECLARE @CounterNotAllocated INT
  DECLARE @TableType varchar(4)
  DECLARE @IntervalStartDateTime DATETIME
  DECLARE @IntervalEndDateTime DATETIME
  DECLARE @DefaultTableBookingMinutes INT
    select @DefaultTableBookingMinutes = KeyValue from HOTELSETUP where searchkey = 'DefaultTableBookingMinutes'
  DECLARE @IntervalBetweenArrivals INT
    select @IntervalBetweenArrivals = KeyValue from HOTELSETUP where searchkey = 'OnlineIntervalBetweenArrivals'

-- Declare tabletype cursor, having tabletype numbers, i.e. "2,4,5".
  DECLARE #TableTypeCur CURSOR LOCAL FORWARD_ONLY FOR
  SELECT Type FROM CONFERENCEROOMTYPES WITH (NOLOCK) 
  WHERE ResourceType = 8  
  ORDER BY Type-- Only look for: 8="Table Schedule".

EXEC	OnlineAvailableTables4 --Populates #AvailableTables
			@AStartDateTime = @AStartDateTime
			,@AEndDateTime = @AEndDateTime
			,@AHotelNumber = @AHotelNumber
			,@APax = 0
			,@DepartmentNumber = @DepartmentNumber

--  
  OPEN #TableTypeCur
  FETCH NEXT FROM #TableTypeCur INTO @TableType
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
  
    -- Find # of allocated tables... 
    -- Pr. tidsinterval, pr. type
    INSERT INTO #NotAllocatedTables  
	  (StartDateTime
      ,EndDateTime
      ,TableType
      ,OccupiedNotAllocatedTables
      ,FreeAllocatedTables) 
	  SELECT 
	  StartTime,
	  DATEADD(minute,@DefaultTableBookingMinutes,StartTime),
	  @TableType,
	  0,
	  COUNT (*) 
	FROM #AvailableTables
    WHERE #AvailableTables.TableType = @TableType 
	Group By StartTime

	--Find # of not allocated tables...

	DECLARE #OccupiedTableCur CURSOR LOCAL FORWARD_ONLY FOR
	SELECT #NotAllocatedTables.StartDateTime,
	       COUNT(*)
	FROM ReservationConferenceLine RCL WITH (NOLOCK) 
	INNER JOIN RESERVATIONS R WITH (NOLOCK) 
	  ON RCL.ReferenceNumber = R.ReferenceNumber AND
		 RCL.TableBooking = 1 AND
		 R.GuestNumber = ISNULL(RCL.GuestNo, 0)
	LEFT OUTER JOIN ConferenceRooms CR WITH (NOLOCK) ON RCL.conferenceroomNumber = CR.ConferenceRoomNumber
	INNER JOIN ReservationMain RM WITH (NOLOCK) ON RM.Referencenumber = R.Referencenumber
	INNER JOIN ConferenceRoomTypes CRT WITH (NOLOCK) ON CRT.ConferenceRoomTypeNumber = RCL.BookedConferenceRoomTypeNumber
	CROSS JOIN #NotAllocatedTables
	WHERE 
	  (LinkLineNumber IS NULL) 
	  AND (CONVERT(DATETIME, RCL.ArrivalDate, 112) + CONVERT(DATETIME, RCL.ArrivalTime, 108) <= DATEADD(minute, @DefaultTableBookingMinutes, #NotAllocatedTables.StartDateTime)) 
	  AND (CONVERT(DATETIME, RCL.DepartureDate, 112) + CONVERT(DATETIME, RCL.DepartureTime, 108) >= DATEADD(minute, 1, #NotAllocatedTables.StartDateTime)) 
	  AND (RCL.TableStatus IN (0, 1, 2, 3, 4, 5)) 
	  AND (IsNull(RM.HotelNumber, 0) = @AHotelNumber) 
	  AND (CRT.Type = @TableType)
	  AND (IsNull(RCL.ConferenceRoomNumber, 0) = 0)
	  AND #NotAllocatedTables.TableType = @TableType
    GROUP BY #NotAllocatedTables.StartDateTime

  Declare @Start DATETIME
  Declare @OccNotAllTables INT

  OPEN #OccupiedTableCur
  FETCH NEXT FROM #OccupiedTableCur INTO @Start, @OccNotAllTables
  
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    UPDATE #NotAllocatedTables SET OccupiedNotAllocatedTables = @OccNotAllTables
	WHERE TableType = @TableType
	and StartDateTime = @Start
    FETCH NEXT FROM #OccupiedTableCur INTO  @Start, @OccNotAllTables
  END
  CLOSE #OccupiedTableCur
  DEALLOCATE #OccupiedTableCur

  FETCH NEXT FROM #TableTypeCur INTO @TableType
END 
CLOSE #TableTypeCur

select * from #NotAllocatedTables order by StartDateTime,TableType

DEALLOCATE #TableTypeCur      
DROP TABLE #NotAllocatedTables
DROP TABLE #AvailableTables
