CREATE PROCEDURE [dbo].[GenerateWellnessAvailability] 
(
  @StartPeriod DATETIME, 
  @EndPeriod DATETIME, 
  @AWarenumberCSV varchar(max) = '', 
  @AConferenceRoomNumber INT = -1, 
  @ATreaterConferenceRoomNumber INT = -1,
	@AExcludeSessionID VARCHAR(900) = '',
	@ASessionTimeOutMinutes INT = NULL
)
/*
	Changes:
  2025-04-07 MLJ: Supports bookings in guestshopper basket
*/
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @T0 datetime=GETDATE()
  DECLARE @T1 datetime=@T0
  DECLARE @T2 datetime
  DECLARE @RC int

  DECLARE @SQLStr NVARCHAR(MAX)
  DECLARE @HallNumberID INT
  DECLARE @WareDuration INT
  DECLARE @CurrentStartDatetime datetime
  DECLARE @CurrentEndDatetime datetime	
  DECLARE @TmpHallTypeNumber INT
  DECLARE @OpeningTime time
  DECLARE @CloseTime time
  DECLARE @CurrentDate datetime

  CREATE TABLE #TmpHallNumbers(
    HallNumberID INT,
    HallName VARCHAR(255),
    HallNumber INT,
    ConferenceRoomTypeNumber INT,
    OpeningTime time,
    CloseTime time,
    WareNumber INT,
    WareName VARCHAR(50),
    Duration INT,
    ResourceType INT,
	PauseInMinutes int,
	TreatmentDuration int
  )

  CREATE TABLE #HallPeriods (
    Duration INT,
    OpeningTime time,
    CloseTime time,
    StartDatetime datetime,
    EndDatetime datetime,
    StartDateTimeChar varchar(16),
    EndDateTimeChar varchar(16),
    StartTime time,
    EndTime time
  )

  CREATE TABLE #RCL
  (
    ConferenceRoomNumber int,
    ArrivalDateTime varchar(16),
    DepartureDateTime varchar(16),
  )

  CREATE TABLE #MySchedulerExpressData (
    ConferenceRoomtypeNumber INT,
    OpeningTime datetime,
    ClosingTime datetime
  )

  -- Temporary table which contains found schedules.
  IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'#SchedulerExpressData')
  BEGIN
    DROP TABLE #SchedulerExpressData
  END
  CREATE TABLE #SchedulerExpressData (Pax int, OpeningDateTime datetime, CloseDateTime datetime)

  --SET @T2=GETDATE();
  --PRINT 'TEST 1 '+ CAST(DATEDIFF(ms, @T1, @T2) AS varchar) + ' msecs'
  --SET @T1=@T2

  SET @SQLStr = 'SELECT WareHallXtab.FK_HallNumber, ConferenceRooms.Name, CONFERENCEROOMS.Number, ConferenceRooms.ConferenceRoomTypeNumber, ConferenceRooms.OpeningTime, ConferenceRooms.CloseTime, Ware.Warenumber, Ware.Warename, Ware.BookTimeInMinutes, ConferenceRoomTypes.ResourceType, ConferenceRooms.PauseInMinutes, ' +
				'isnull(isnull(isnull(d.Duration, Ware.BookTimeInMinutes - isnull(ConferenceRooms.PauseInMinutes, 0)), ConferenceRooms.PauseInMinutes), 0) As TreatmentDuration ' +
		'FROM WareHallXTab WITH (NOLOCK) ' +
				'INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = FK_HallNumber ' +
				'INNER JOIN CONFERENCEROOMTYPES WITH (NOLOCK) ON CONFERENCEROOMTYPES.ConferenceRoomTypeNumber = ConferenceRooms.ConferenceRoomTypeNumber ' +
				'INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = WareHallXTab.FK_WareNumber ' +
				'left  join OnlineWareDelivery d WITH (NOLOCK) on (d.WareNumber = Ware.WareNumber) ' +
				'WHERE (1=1) '

  IF (@AWarenumberCSV <> '')
    SET @SQLStr = @SQLStr + ' AND WareHallXtab.FK_WareNumber in (' + @AWarenumberCSV +') '
  IF (@AConferenceRoomNumber > 0) AND (@ATreaterConferenceRoomNumber > 0)
    SET @SQLStr = @SQLStr + ' AND ((WareHallXTab.FK_HallNumber = ' + CAST(@AConferenceRoomNumber AS varchar(8)) + ' AND ConferenceRoomTypes.ResourceType IN (1,4,7,10)) OR (WareHallXTab.FK_HallNumber = ' + CAST(@ATreaterConferenceRoomNumber AS varchar(8)) + ' AND ConferenceRoomTypes.ResourceType IN (4,10,13)))'
  IF (@AConferenceRoomNumber > 0) AND (@ATreaterConferenceRoomNumber <= 0)
    SET @SQLStr = @SQLStr + ' AND ((WareHallXTab.FK_HallNumber = ' + CAST(@AConferenceRoomNumber AS varchar(8)) + ' AND ConferenceRoomTypes.ResourceType IN (1,4,7,10)) ' +
    ' OR (WareHallXTab.FK_HallNumber IN ( ' +
    ' SELECT DISTINCT ConferenceRoomNumber ' +
    ' FROM ConferenceRooms WITH (NOLOCK) ' +
    ' INNER JOIN ConferenceRoomTypes WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = CONFERENCEROOMS.ConferenceRoomTypeNumber AND CONFERENCEROOMTYPES.ResourceType in (4,10,13))))'
  IF (@ATreaterConferenceRoomNumber > 0) AND (@AConferenceRoomNumber <= 0)
    SET @SQLStr = @SQLStr + ' AND ((WareHallXTab.FK_HallNumber = ' + CAST(@ATreaterConferenceRoomNumber AS varchar(8)) + ' AND ConferenceRoomTypes.ResourceType IN (4,10,13)) ' +
    ' OR (WareHallXTab.FK_HallNumber IN ( ' +
    ' SELECT DISTINCT ConferenceRoomNumber ' +
    ' FROM ConferenceRooms WITH (NOLOCK) ' +
    ' INNER JOIN ConferenceRoomTypes WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = CONFERENCEROOMS.ConferenceRoomTypeNumber AND CONFERENCEROOMTYPES.ResourceType in (1,4,7,10))))'

  INSERT INTO #TmpHallNumbers EXEC sp_executesql @SQLStr

  --SET @T2=GETDATE();
  --PRINT 'TEST 2 '+ CAST(DATEDIFF(ms, @T1, @T2) AS varchar) + ' msecs'
  --SET @T1=@T2

  DECLARE TmpSchedCur CURSOR FOR
    SELECT MIN(HallNumberID), ConferenceRoomTypeNumber FROM #TmpHallNumbers GROUP BY ConferenceRoomTypeNumber
  OPEN TmpSchedCur
  FETCH NEXT FROM TmpSchedCur INTO @HallNumberID, @TmpHallTypeNumber
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    SET @CurrentDate = @StartPeriod
    WHILE @CurrentDate <= @EndPeriod
    BEGIN
      DELETE FROM #SchedulerExpressData
      EXEC GetSchedulerExpressData3 @CurrentDate, @TmpHallTypeNumber
      INSERT INTO #MySchedulerExpressData (ConferenceRoomtypeNumber, OpeningTime, ClosingTime) 
        SELECT @TmpHallTypeNumber, OpeningDatetime, CloseDateTime FROM #SchedulerExpressData
      --DELETE FROM #SchedulerExpressData
      IF @@ROWCOUNT=0
      BEGIN
	    INSERT INTO #MySchedulerExpressData (ConferenceRoomtypeNumber, OpeningTime, ClosingTime)
          SELECT TOP 1 @TmpHallTypeNumber,
        CASE WHEN OpeningTime IS NULL THEN '1899-12-30' ELSE OpeningTime END,
        CASE WHEN CloseTime IS NULL OR YEAR(CloseTime)=1899 THEN '2999-12-31' ELSE CloseTime END
        FROM ConferenceRooms WITH (NOLOCK) 
        WHERE ConferenceRoomNumber = @HallNumberID
      END
      SET @CurrentDate = DATEADD(day,1,@CurrentDate)
    END

    /*
    DELETE FROM #SchedulerExpressData
    --TODO:	  EXEC GetSchedulerExpressData3 @StartPeriod, @TmpHallTypeNumber

	  INSERT INTO #MySchedulerExpressData (ConferenceRoomtypeNumber, OpeningTime, ClosingTime)
    SELECT @TmpHallTypeNumber,OpeningDateTime, CloseDateTime FROM #SchedulerExpressData
    IF @@ROWCOUNT=0
    BEGIN
	    INSERT INTO #MySchedulerExpressData (ConferenceRoomtypeNumber, OpeningTime, ClosingTime)
      SELECT TOP 1 @TmpHallTypeNumber,
        CASE WHEN OpeningTime IS NULL THEN '1899-12-30' ELSE OpeningTime END,
        CASE WHEN CloseTime IS NULL OR YEAR(CloseTime)=1899 THEN '2999-12-31' ELSE CloseTime END
      FROM ConferenceRooms WITH (NOLOCK) 
      WHERE ConferenceRoomNumber = @HallNumberID
    END
	*/
    FETCH NEXT FROM TmpSchedCur INTO @HallNumberID, @TmpHallTypeNumber
  END
  CLOSE TmpSchedCur
  DEALLOCATE TmpSchedCur

  --SET @T2=GETDATE();
  --PRINT 'TEST 3 '+ CAST(DATEDIFF(ms, @T1, @T2) AS varchar) + ' msecs'
  --SET @T1=@T2

  SELECT * 
  INTO #RoomHallNumbers
  FROM #TmpHallNumbers
  WHERE ResourceType IN (1,4,7,10)
  AND ISNULL(Duration,0) > 0
  AND CloseTime > OpeningTime
  
  SELECT *
  INTO #TreaterHallNumbers
  FROM #TmpHallNumbers
  WHERE ResourceType IN (4,10,13)

  --SET @T2=GETDATE();
  --PRINT 'TEST 4 '+ CAST(DATEDIFF(ms, @T1, @T2) AS varchar) + ' msecs'
  --SET @T1=@T2

  DECLARE @CurrentCloseDatetime datetime
  DECLARE TmpHallNumberCur CURSOR FORWARD_ONLY READ_ONLY FOR 
    SELECT DISTINCT Duration, OpeningTime, CloseTime 
    FROM #RoomHallNumbers 
  OPEN TmpHallNumberCur
  WHILE 1=1
  BEGIN
    FETCH NEXT FROM TmpHallNumberCur INTO @WareDuration, @OpeningTime, @CloseTime
    IF (@@FETCH_STATUS = -1)
      BREAK
    SET @CurrentStartDatetime = CAST(CAST(@StartPeriod AS Date) AS datetime) + CAST(@OpeningTime as datetime)
    SET @CurrentCloseDatetime = CAST(CAST(@CurrentStartDatetime AS Date) AS datetime) + CAST(@CloseTime as datetime)
    WHILE @CloseTime > @OpeningTime 
    BEGIN
      SET @CurrentEndDatetime = DATEADD(minute, @WareDuration, @CurrentStartDatetime)
      IF @CurrentEndDatetime > @EndPeriod
        BREAK
      IF @CurrentEndDatetime  > @CurrentCloseDatetime
      BEGIN
        SET @CurrentStartDatetime = DATEADD(day, 1, CAST(CAST(@CurrentStartDatetime AS Date) AS datetime)) + CAST(@OpeningTime as datetime)
        SET @CurrentCloseDatetime = CAST(CAST(@CurrentStartDatetime AS Date) AS datetime) + CAST(@CloseTime as datetime)
        CONTINUE
      END
      IF @CurrentStartDatetime >= @StartPeriod
        INSERT INTO #HallPeriods
        VALUES (@WareDuration, @OpeningTime, @CloseTime, @CurrentStartDatetime, @CurrentEndDatetime, 
          CONVERT(varchar, @CurrentStartDatetime, 112)+CONVERT(varchar, @CurrentStartDatetime, 108), 
          CONVERT(varchar, @CurrentEndDatetime, 112)+CONVERT(varchar, @CurrentEndDatetime, 108), 
          @CurrentStartDatetime, @CurrentEndDatetime
        ) 

      SET @CurrentStartDatetime = @CurrentEndDatetime
    END

  END
  CLOSE TmpHallNumberCur
  DEALLOCATE TmpHallNumberCur

  --SET @T2=GETDATE();
  --PRINT 'TEST 5 '+ CAST(DATEDIFF(ms, @T1, @T2) AS varchar) + ' msecs'
  --SET @T1=@T2

  DECLARE @StartDateChar char(8) =  CONVERT(char(8), @StartPeriod, 112)
  DECLARE @EndDateChar char(8) =  CONVERT(char(8), @EndPeriod, 112)

	IF ISNULL(@ASessionTimeOutMinutes,0) <= 0
		SELECT @ASessionTimeOutMinutes = CAST(KeyValue AS INT) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'PicassoOnlineReservationCompleteTimeOut'
	IF ISNULL(@ASessionTimeOutMinutes,0) <= 0
		SET @ASessionTimeOutMinutes = 10
	DECLARE @SessionExpire datetime = DATEADD(MINUTE, -@ASessionTimeOutMinutes, GETDATE())

  INSERT INTO #RCL
  SELECT RCL.ConferenceRoomNumber, RCL.ArrivalDate+RCL.ArrivalTime, RCL.DepartureDate+RCL.DepartureTime
  FROM ReservationConferenceLine RCL WITH (NOLOCK)
  INNER JOIN #TmpHallNumbers T ON T.HallNumberID=RCL.ConferenceRoomNumber
  INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = RCL.ReferenceNumber AND Reservations.GuestNumber = RCL.GuestNo AND Reservations.Status <= 5
  WHERE RCL.ArrivalDate <= @EndDateChar
  AND RCL.DepartureDate >= @StartDateChar
  UNION
  SELECT RCL.ConferenceRoomNumber, RCL.ArrivalDate+RCL.ArrivalTime, RCL.DepartureDate+RCL.DepartureTime
  FROM ReservationConferenceLine RCL WITH (NOLOCK)
  INNER JOIN #TmpHallNumbers T ON T.HallNumberID=RCL.ConferenceRoomNumber
  WHERE RCL.ReferenceNumber <= 0
  AND RCL.ArrivalDate <= @EndDateChar
  AND RCL.DepartureDate >= @StartDateChar
  UNION
  -- Add halls from shopping basket in GuestShopper
  SELECT TP.HallConferenceRoomNumber, CONVERT(varchar(8), TP.FromDateTime, 112)+CONVERT(varchar(5), TP.FromDateTime, 108), CONVERT(varchar(8), TP.ToDateTime, 112)+CONVERT(varchar(5), TP.ToDateTime, 108)
  FROM TransactionPeriod TP WITH (NOLOCK) 
  INNER JOIN #TmpHallNumbers T ON T.HallNumberID=TP.HallConferenceRoomNumber
	WHERE TP.FromDateTime < @Endperiod
  AND TP.ToDateTime > @StartPeriod
	AND TP.FK_TransactionNumber IS NULL
	AND TP.DateStampSold >= @SessionExpire
	AND TP.SessionId IS NOT NULL
	AND TP.SessionId <> ISNULL(@AExcludeSessionID,'')
  UNION
  -- Add treaters from shopping basket in GuestShopper
  SELECT TP.TreaterConferenceRoomNumber, CONVERT(varchar(8), TP.FromDateTime, 112)+CONVERT(varchar(5), TP.FromDateTime, 108), CONVERT(varchar(8), TP.ToDateTime, 112)+CONVERT(varchar(5), TP.ToDateTime, 108)
  FROM TransactionPeriod TP WITH (NOLOCK) 
  INNER JOIN #TmpHallNumbers T ON T.HallNumberID=TP.TreaterConferenceRoomNumber
	WHERE TP.FromDateTime < @Endperiod
  AND TP.ToDateTime > @StartPeriod
	AND TP.FK_TransactionNumber IS NULL
	AND TP.DateStampSold >= @SessionExpire
	AND TP.SessionId IS NOT NULL
	AND TP.SessionId <> ISNULL(@AExcludeSessionID,'')
  ;

  --SET @RC=@@ROWCOUNT
  --SET @T2=GETDATE();
  --PRINT 'TEST 6 '+ CAST(DATEDIFF(ms, @T1, @T2) AS varchar) + ' msecs. Rowcount='+CAST(@RC AS varchar)
  --SET @T1=@T2

  SELECT
    R.WareNumber, 
    R.Warename, 
    R.HallNumberID AS ConferenceRoomNumber, 
    R.HallNumber AS Number, 
    R.HallName AS Name,
    R.ResourceType,
    T.HallNumberID AS TreaterConferenceRoomNumber, 
    T.HallNumber AS TreaterNumber,
    T.HallName AS TreaterName,
    T.ResourceType AS TreaterResourceType,
    X.StartDatetime AvailableStartTime,
    X.EndDatetime AvailableEndTime,
	isnull(T.PauseInMinutes, 0) As PauseInMinutes,
	isnull(T.TreatmentDuration, 0) As TreatmentDuration
  FROM #RoomHallNumbers R
  INNER JOIN #HallPeriods X ON X.Duration=R.Duration AND X.OpeningTime=R.OpeningTime AND X.CloseTime=R.CloseTime
  CROSS APPLY (
	    SELECT TOP 1 1 OK1
	    FROM #MySchedulerExpressData
	    WHERE (1=1)
	      AND ConferenceroomTypeNumber = R.ConferenceroomTypeNumber
	      AND OpeningTime <= X.StartDatetime
	      AND ClosingTime >= X.EndDatetime
  ) RX
  INNER JOIN #TreaterHallNumbers T ON T.WareNumber = R.WareNumber 
	  AND T.HallNumberID <> R.HallNumberID                     
	  AND T.ResourceType <> R.ResourceType
    AND T.OpeningTime <= X.StartTime
    AND T.CloseTime >= X.EndTime
  CROSS APPLY (
	    SELECT TOP 1 1 OK2
	    FROM #MySchedulerExpressData
	    WHERE (1=1)
	      AND ConferenceroomTypeNumber = T.ConferenceroomTypeNumber
	      AND OpeningTime <= X.StartDatetime
	      AND ClosingTime >= X.EndDatetime
  ) TX
  WHERE 1=1
  AND NOT EXISTS (
    SELECT *
    FROM #RCL RCL 
    WHERE (1=1)
      AND RCL.ConferenceRoomNumber=R.HallNumberID
      AND (RCL.ArrivalDateTime < X.EndDateTimeChar)
      AND (RCL.DepartureDateTime > X.StartDateTimeChar)
  )
  AND NOT EXISTS (
    SELECT *
    FROM #RCL RCL 
    WHERE (1=1)
      AND RCL.ConferenceRoomNumber=T.HallNumberID
      AND (RCL.ArrivalDateTime < X.EndDateTimeChar)
      AND (RCL.DepartureDateTime > X.StartDateTimeChar)
  )
  ORDER BY WareNumber, AvailableStartTime ASC, AvailableEndTime ASC, ConferenceRoomNumber

  --SET @RC=@@ROWCOUNT
  --SET @T2=GETDATE();
  --PRINT 'TEST 7 '+ CAST(DATEDIFF(ms, @T1, @T2) AS varchar) + ' msecs. Rowcount='+CAST(@RC AS varchar)
  --SET @T1=@T2

  DROP TABLE #MySchedulerExpressData
  DROP TABLE #TmpHallNumbers
  DROP TABLE #HallPeriods
  DROP TABLE #SchedulerExpressData
  DROP TABLE #RoomHallNumbers
  DROP TABLE #TreaterHallNumbers
  DROP TABLE #RCL

  --SET @T2=GETDATE();
  --PRINT 'TEST 8 '+ CAST(DATEDIFF(ms, @T1, @T2) AS varchar) + ' msecs'
  --PRINT 'TOTAL '+ CAST(DATEDIFF(ms, @T0, @T2) AS varchar) + ' msecs'
  --SET @T1=@T2
END