CREATE PROCEDURE [dbo].[RecurringPatternAvailableWares]
(
	@WarePeriods [RecurringPatternAvailableWaresTable] readonly,
	@ReturnSpecified bit = 0,
	@SessionTimeOutMinutes INT = NULL,
	@ExcludeReferenceNumber INT = -1,
	@ExcludeSessionID VARCHAR(900) = '',
	@ReturnSpecifiedDateTime datetime = NULL
)
/*
  Created by Mikael Jeppesen/Kent Johansen, July 2022
  Get availability for rules

  TODO: Support for menu ware

  Last Changed:
    2024-11-14 MLJ Now supports max. for sale on ware in combination with warelinkgroup
    2024-04-12 MLJ Fixed problem with max. arrivals
    2024-04-09 MLJ Max. accumulated changed to max. arrivals
    2024-04-09 MLJ Now supports max. accumulated.
    2023-08-31 MLJ Now supports free bookings periods that do not necessarily match recurring pattern periods.
    2023-08-24 MLJ Now supports any time periods. Before time periods were restricted to the time periods defined in recurring patterns 
    2023-06-20 MLJ Fixed problem when @ReturnSpecified=1. Problem was introduced in last change 2023-05-03
    2023-05-03 MLJ Now supports overlapping periods
    2023-03-02 MLJ Added missing NOLOCK
    KJ 20221216: Ext: Multiple rules per WareLinkGroup.
    2022-10-31 MLJ Fixed bug with deleted periods
    2022-10-04 MLJ "Max number for sale" for ware link groups are handled as TOTAL number of sold pieces for ALL wares related to the ware link group
    2022-09-06 MLJ Handling special case where Ware (arrangement line) was booked entire day from 00:00 to 23:59
    2022-08-31 MLJ Fix (4)
    2022-08-03 MLJ Fix (3)

*/
AS
BEGIN
	SET NOCOUNT ON
  DECLARE @MaxConcurrentRuleType int = 4
  DECLARE @MaxArrivalsRuleType int = 30

	IF ISNULL(@SessionTimeOutMinutes,0) <= 0
		SELECT @SessionTimeOutMinutes = CAST(KeyValue AS INT) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'PicassoOnlineReservationCompleteTimeOut'
	IF ISNULL(@SessionTimeOutMinutes,0) <= 0
		SET @SessionTimeOutMinutes = 10
	DECLARE @SessionExpire datetime = DATEADD(MINUTE, -@SessionTimeOutMinutes, GETDATE())
	DECLARE @DayStarts datetime
	SELECT @DayStarts = TRY_CONVERT(DATETIME, KeyValue, 108) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Arr24Hoursstarts' AND LEN(KeyValue)=5
	IF @DayStarts IS NULL
		SET @DayStarts = CONVERT(DATETIME, '07:00', 108) 

	DECLARE @T1 datetime = GETDATE()
	DECLARE @T2 datetime
  DECLARE @TotalRecurringCount INT = 0
  DECLARE @ConcurrentRecurringCount INT = 0
	;
	WITH P AS
	(
		SELECT DISTINCT WP.WareNumber, X.FK_RecurringPatternId RecurringPatternId, WP.StartDateTime, WP.EndDateTime, NULL WareLinkGroupID
		FROM RecurringPatternWareXTab X WITH (NOLOCK) 
		INNER JOIN @WarePeriods WP ON WP.WareNumber=X.FK_WareNumber
		UNION
		SELECT DISTINCT WP.WareNumber, WareLinkGroupRecurringPatternXTab.FKRecurringPatternId RecurringPatternId, WP.StartDateTime, WP.EndDateTime, WLG.Id WareLinkGroupID
		FROM WareLinkGroup WLG WITH (NOLOCK) 
		INNER JOIN WareLinkGroupRecurringPatternXTab WITH (NOLOCK) ON WareLinkGroupRecurringPatternXTab.FKWareLinkGroupId = WLG.Id
		INNER JOIN Ware W WITH (NOLOCK) ON W.WareLinkNumber=WLG.Id AND ISNULL(W.markasdeleted, 0) = 0 
		INNER JOIN @WarePeriods WP ON WP.WareNumber=W.WareNumber
	)
	SELECT DISTINCT P.WareNumber, RecurringPatternSpecified.StartDateTime, RecurringPatternSpecified.EndDateTime, RecurringRule.RuleType, ISNULL(CONVERT(INT, RecurringRule.RuleValue),0) ForSale, P.WareLinkGroupID
	INTO #RecurringRuleTypes 
	FROM P
	INNER JOIN RecurringPatternSpecified WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternId=P.RecurringPatternId
		AND RecurringPatternSpecified.StartDateTime < P.EndDateTime 
		AND RecurringPatternSpecified.EndDateTime > P.StartDateTime
		AND ISNULL(RecurringPatternSpecified.Deleted, 0) = 0
	INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID AND RecurringRule.RuleType IN (@MaxConcurrentRuleType,@MaxArrivalsRuleType) 
  SET @TotalRecurringCount = @@ROWCOUNT

	SELECT DISTINCT WareNumber, StartDateTime, EndDateTime, ForSale, WareLinkGroupID
	INTO #Recurring 
	FROM #RecurringRuleTypes
  WHERE RuleType = @MaxConcurrentRuleType
  SET @ConcurrentRecurringCount = @@ROWCOUNT

	DECLARE @IncompleteWarePeriods [RecurringPatternAvailableWaresTable]
	IF @ReturnSpecified=0
	BEGIN
		-- Find incomplete ware periods, i.e. ware periods where RecurringPatternSpecified periods do not exist for the entire ware period.
		INSERT INTO @IncompleteWarePeriods
		SELECT WP.*
		FROM @WarePeriods WP
		OUTER APPLY (
			SELECT TOP 1 P.StartDateTime
			FROM #Recurring P
			WHERE P.WareNumber=WP.WareNumber
			AND P.StartDateTime < WP.EndDateTime 
			AND P.EndDateTime > WP.StartDateTime
			ORDER BY P.StartDateTime
		) RS
		WHERE RS.StartDateTime IS NULL OR RS.StartDateTime > WP.StartDateTime
		UNION
		SELECT DISTINCT WP.*
		FROM @WarePeriods WP
		INNER JOIN #Recurring P1 ON P1.WareNumber=WP.WareNumber AND P1.StartDateTime < WP.EndDateTime AND P1.EndDateTime > WP.StartDateTime
		LEFT OUTER JOIN #Recurring P2 ON P2.WareNumber=WP.WareNumber AND P2.StartDateTime <= P1.EndDateTime AND P2.EndDateTime > P1.EndDateTime
		WHERE P1.EndDateTime < WP.EndDateTime
		AND P2.StartDateTime IS NULL
	END
	DECLARE @CompleteWarePeriods [RecurringPatternAvailableWaresTable]
	INSERT INTO @CompleteWarePeriods
	SELECT *
	FROM @WarePeriods
	EXCEPT 
	SELECT *
	FROM @IncompleteWarePeriods

	--SET @T2 = GETDATE()
	--PRINT 'Insert @CompleteWarePeriods ' + CAST(DATEDIFF(MILLISECOND, @T1, @T2) AS VARCHAR)
	--SET @T1 = GETDATE()

	DECLARE @WareMap TABLE
	(
		WareNumber int,
    WareLinkGroupID int
	)
	INSERT INTO @WareMap 
	SELECT DISTINCT W1.WareNumber, W1.WareLinkNumber WareLinkGroupID
	FROM @CompleteWarePeriods WP
	INNER JOIN Ware W1 WITH (NOLOCK) ON W1.WareNumber=WP.WareNumber
	UNION
	SELECT DISTINCT W2.WareNumber, W2.WareLinkNumber WareLinkGroupID
	FROM @CompleteWarePeriods WP
	INNER JOIN Ware W1 WITH (NOLOCK) ON W1.WareNumber=WP.WareNumber
	INNER JOIN Ware W2 WITH (NOLOCK) ON W2.WareLinkNumber=W1.WareLinkNumber AND W2.WareNumber<>W1.WareNumber

	--SET @T2 = GETDATE()
	--PRINT 'Insert @WareMap ' + CAST(DATEDIFF(MILLISECOND, @T1, @T2) AS VARCHAR)
	--SET @T1 = GETDATE()

	DECLARE @StartDateTime datetime, @EndDateTime datetime
  ;WITH D
  AS
  (
  	SELECT StartDateTime, EndDateTime
	  FROM @CompleteWarePeriods
    UNION ALL
    SELECT StartDateTime, EndDateTime
    FROM #RecurringRuleTypes
    WHERE RuleType=@MaxArrivalsRuleType
  )
  SELECT @StartDateTime=MIN(StartDateTime), @EndDateTime=MAX(EndDateTime)
  FROM D
	DECLARE @StartDate char(8)=CONVERT(CHAR(8),@StartDateTime,112), @EndDate char(8)=CONVERT(CHAR(8),DATEADD(DAY, 1, @EndDateTime),112)

	-- Sold by Arrangementline
	;
	WITH L AS
	(
		SELECT DISTINCT L.ArrangementLineNumber, WM.WareNumber, WM.WareLinkGroupID,
			CASE WHEN W.EndTime < W.StartTime THEN W.StartDate+W.StartTime
				WHEN  L.[Time]='00:00' AND L.DepartureTime='23:59' THEN W.StartDate+W.StartTime
				WHEN W.StartTime < @DayStarts THEN DATEADD(DAY, 1, W.StartDate)+W.StartTime
				ELSE W.StartDate+W.StartTime END StartDateTime, 
			CASE WHEN W.EndTime < W.StartTime THEN DATEADD(DAY, 1, W.StartDate)+W.EndTime
				WHEN  L.[Time]='00:00' AND L.DepartureTime='23:59' THEN W.StartDate+W.EndTime
				WHEN W.StartTime < @DayStarts THEN DATEADD(DAY, 1, W.StartDate)+W.EndTime
				ELSE W.StartDate+W.EndTime END EndDateTime, 
			L.Pieces
		FROM ArrangementLines L WITH (NOLOCK)
		CROSS APPLY (
			SELECT CONVERT(DATETIME,L.Date,112) StartDate,
				CONVERT(DATETIME, L.Time, 108) StartTime,
				CASE WHEN L.DepartureTime IS NULL OR LEN(L.DepartureTime) < 5 OR L.DepartureTime=L.Time THEN DATEADD(SECOND,1, CONVERT(DATETIME, L.Time, 108))
				ELSE CONVERT(DATETIME, L.DepartureTime, 108) END EndTime
		) W
		INNER JOIN ArrangementReservationsXtab X WITH (NOLOCK) ON X.ArrangementLineNumber=L.ArrangementLineNumber
		INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber = X.ReferenceNumber AND R.GuestNumber = X.GuestNumber AND R.Status < 7
		INNER JOIN @WareMap WM ON WM.WareNumber=L.WareNumber 
		WHERE L.[Date] BETWEEN @StartDate AND @EndDate -- Performance optimization
		AND LEN(L.Time) = 5
		AND R.ReferenceNumber <> ISNULL(@ExcludeReferenceNumber,-1)
	)
	SELECT L.WareNumber, L.StartDateTime, L.EndDateTime, L.Pieces, L.WareLinkGroupID
	INTO #Arrangement
	FROM L
	OUTER APPLY (
		SELECT TOP 1 WP.ExcludeArrangementLineNumber
		FROM @CompleteWarePeriods WP
		WHERE L.WareNumber = WP.WareNumber AND L.StartDateTime < WP.EndDateTime AND L.EndDateTime > WP.StartDateTime AND WP.ExcludeArrangementLineNumber=L.ArrangementLineNumber
	) X
  WHERE X.ExcludeArrangementLineNumber IS NULL 
  AND L.StartDateTime < @EndDateTime AND L.EndDateTime > @StartDateTime

	--SET @T2 = GETDATE()
	--PRINT 'Get ArrangementLine ' + CAST(DATEDIFF(MILLISECOND, @T1, @T2) AS VARCHAR)
	--SET @T1 = GETDATE()

	-- Sold by transaction (added to basket in guestshopper)
	INSERT INTO #Arrangement
	SELECT WM.WareNumber, TP.FromDateTime StartDateTime, TP.ToDateTime EndDateTime, ISNULL(TP.Pieces, 0), WM.WareLinkGroupID
	FROM @WareMap WM
	INNER JOIN TransactionPeriod TP WITH (NOLOCK) ON TP.FK_WareNumber=WM.WareNumber 
		AND TP.FromDateTime < @EndDateTime AND TP.ToDateTime > @StartDateTime 
		AND TP.FK_TransactionNumber IS NULL
		AND TP.DateStampSold >= @SessionExpire
		AND TP.SessionId IS NOT NULL
		AND TP.SessionId <> ISNULL(@ExcludeSessionID,'')

	--SET @T2 = GETDATE()
	--PRINT 'Get Basket ' + CAST(DATEDIFF(MILLISECOND, @T1, @T2) AS VARCHAR)
	--SET @T1 = GETDATE()

	-- Sold by transaction
	INSERT INTO #Arrangement
	SELECT WM.WareNumber WareNumber, TP.FromDateTime StartDateTime, TP.ToDateTime EndDateTime, T.Pieces, WM.WareLinkGroupID
	FROM @WareMap WM 
	INNER JOIN TransactionPeriod TP WITH (NOLOCK) ON TP.FromDateTime < @EndDateTime AND TP.ToDateTime > @StartDateTime
	INNER JOIN Transactions T WITH (NOLOCK) ON T.TransactionNumber = TP.FK_TransactionNumber AND T.WareNumber=WM.WareNumber

	--SET @T2 = GETDATE()
	--PRINT 'Get Transactions ' + CAST(DATEDIFF(MILLISECOND, @T1, @T2) AS VARCHAR)
	--SET @T1 = GETDATE()

	;WITH D AS (
		SELECT DISTINCT WareNumber, StartDateTime [DateTime] FROM #Arrangement
		UNION
		SELECT DISTINCT WareNumber, EndDateTime [DateTime] FROM #Arrangement
		UNION
		SELECT DISTINCT WareNumber, StartDateTime [DateTime] FROM #Recurring
		UNION
		SELECT DISTINCT WareNumber, EndDateTime [DateTime] FROM #Recurring
	)
	SELECT WareNumber, [DateTime], ROW_NUMBER() OVER (PARTITION BY WareNumber ORDER BY [DateTime]) [Index]
	INTO #ArrangementDate
	FROM D

	SELECT D1.WareNumber, D1.[DateTime] StartDateTime, D2.[DateTime] EndDateTime
	INTO #ArrangementPeriod
	FROM #ArrangementDate D1
	INNER JOIN #ArrangementDate D2 ON D2.WareNumber=D1.WareNumber AND D2.[Index]=D1.[Index]+1

  ;
  WITH X
  AS
  (
  	SELECT AP.WareNumber, AP.StartDateTime, AP.EndDateTime, ISNULL(A.Available, RC.ForSale) Available, RC.WareLinkGroupID,
      ROW_NUMBER() OVER (PARTITION BY AP.WareNumber, AP.StartDateTime, AP.EndDateTime ORDER BY A.Available) SortIndex
	  FROM #ArrangementPeriod AP
	  CROSS APPLY (
		  SELECT ForSale, WareLinkGroupID
		  FROM #Recurring RC 
		  WHERE RC.WareNumber = AP.WareNumber AND RC.StartDateTime < AP.EndDateTime AND RC.EndDateTime > AP.StartDateTime
	  ) RC
    CROSS APPLY (
      SELECT RC.ForSale-SUM(A.Pieces) Available
      FROM #Arrangement A 
      WHERE (RC.WareLinkGroupID IS NOT NULL OR A.WareNumber = AP.WareNumber)
      AND (RC.WareLinkGroupID IS NULL OR ISNULL(A.WareLinkGroupID,0) = ISNULL(RC.WareLinkGroupID,0))
      AND A.StartDateTime < AP.EndDateTime 
      AND A.EndDateTime > AP.StartDateTime
    ) A
  )
	SELECT WareNumber, StartDateTime, EndDateTime, Available, WareLinkGroupID
	INTO #Available
	FROM X
  WHERE X.SortIndex=1

	--SET @T2 = GETDATE()
	--PRINT 'Get SUM ' + CAST(DATEDIFF(MILLISECOND, @T1, @T2) AS VARCHAR)
	--SET @T1 = GETDATE()

  DECLARE @MaxArrivals TABLE (WareNumber INT NOT NULL, StartDateTime DATETIME NOT NULL, Available INT)
  IF @TotalRecurringCount > @ConcurrentRecurringCount
  BEGIN
    WITH A
    AS
    (
      SELECT RR.WareNumber, RR.StartDateTime, RR.EndDateTime, RR.ForSale-ISNULL(SUM(A.Pieces),0) Available, RR.WareLinkGroupID
      FROM #RecurringRuleTypes RR
  	  LEFT OUTER JOIN #Arrangement A ON (RR.WareLinkGroupID IS NOT NULL OR A.WareNumber = RR.WareNumber) 
        AND (RR.WareLinkGroupID IS NULL OR ISNULL(A.WareLinkGroupID,0) = ISNULL(RR.WareLinkGroupID,0))
        AND A.StartDateTime >= RR.StartDateTime AND A.StartDateTime < RR.EndDateTime
      WHERE RR.RuleType=@MaxArrivalsRuleType
  	  GROUP BY RR.WareNumber, RR.StartDateTime, RR.EndDateTime, RR.ForSale, RR.WareLinkGroupID
    )
    INSERT INTO @MaxArrivals
    SELECT WareNumber, StartDateTime, MIN(Available) Available
    FROM A
    GROUP BY WareNumber, StartDateTime
  END

	IF @ReturnSpecified=0
		SELECT WP.WareNumber, WP.StartDateTime, WP.EndDateTime, ISNULL(X.Available,A.Available) Available, A.WareLinkGroupID
		FROM @CompleteWarePeriods WP
		CROSS APPLY (
			SELECT TOP 1 A.Available, A.WareLinkGroupID
			FROM #Available A 
			WHERE A.WareNumber=WP.WareNumber 
			AND A.StartDateTime < WP.EndDateTime 
			AND A.EndDateTime > WP.StartDateTime
			ORDER BY A.Available
		) A
    OUTER APPLY (
      SELECT MIN(AR.Available) Available
      FROM @MaxArrivals AR
      WHERE WP.WareNumber = AR.WareNumber 
      AND WP.StartDateTime = AR.StartDateTime
      AND AR.Available < A.Available
    ) X
		UNION ALL
		SELECT WareNumber, StartDateTime, EndDateTime, 0 Available, NULL WareLinkGroupID
		FROM @IncompleteWarePeriods
	ELSE
		SELECT RC.WareNumber, RC.StartDateTime, RC.EndDateTime, ISNULL(X.Available,A.Available) Available, A.WareLinkGroupID
		FROM #Recurring RC
		CROSS APPLY (
			SELECT TOP 1 A.Available, A.WareLinkGroupID
			FROM #Available A 
			WHERE A.WareNumber=RC.WareNumber 
			AND A.StartDateTime < RC.EndDateTime 
			AND A.EndDateTime > RC.StartDateTime
			ORDER BY A.Available
		) A
    OUTER APPLY (
      SELECT MIN(AR.Available) Available
      FROM @MaxArrivals AR
      WHERE RC.WareNumber = AR.WareNumber 
      AND RC.StartDateTime = AR.StartDateTime
      AND AR.Available < A.Available
    ) X
    WHERE ISNULL(A.WareLinkGroupID,0)=ISNULL(RC.WareLinkGroupID,0)
		ORDER BY RC.StartDateTime

	SET @T2 = GETDATE()
	PRINT 'Return resultset ' + CAST(DATEDIFF(MILLISECOND, @T1, @T2) AS VARCHAR)
	SET @T1 = GETDATE()

	DROP TABLE #ArrangementDate
	DROP TABLE #ArrangementPeriod
	DROP TABLE #Arrangement
	DROP TABLE #Available
	DROP TABLE #Recurring
  DROP TABLE #RecurringRuleTypes
END
