CREATE PROCEDURE dbo.RecurringPatternGetWare @AStartDate DATETIME, @AEndDate DATETIME, @AWareNumberCSV VARCHAR(1000), @AArrangementLineNumber INT = -1, @AUseCache BIT = 1 AS
/*
  Created by Kent Johansen, November 2014
  Returns all recurring patterns

  Changes:
    KJ 20200117: Bug: Fix for "Number of available pieces is wrong". Case #CR178490
    KJ 20190515: Bug: Fix for "Number of available pieces is wrong". Case #CR164615
    RN 20170913: Added parameter if cache should be used.
    KJ 20170522: Using cache to get data
    KJ 20160114: Optimized sql.
    KJ 20150421: Bug: Fix for "Wrong number of available". Case #CR086590
    KJ 20150326: Ext: Added ArrangementLineNumber as input. Don't include this arr line in sold count.
    KJ 20150310: Ext: Added Ware link group.
    KJ 20150102: Bug: Fix for "Available count wrong"
*/

DECLARE @SystemDate DATETIME
SELECT @SystemDate = SystemDate FROM System WITH (NOLOCK)

IF @AUseCache = 1 AND @AEndDate < DATEADD(YEAR, 1, @SystemDate) AND (SELECT COUNT(*) FROM RecurringPatternWareCache WITH (NOLOCK)) > 0
BEGIN
  SELECT RecurringPatternWareCache.WareNumber, Ware.WareName, RecurringPatternWareCache.StartDateTime, RecurringPatternWareCache.EndDateTime, RecurringPatternWareCache.Available AS Available
  FROM RecurringPatternWareCache WITH (NOLOCK)
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = RecurringPatternWareCache.WareNumber
    AND RecurringPatternWareCache.WareNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareNumberCSV))
  WHERE StartDateTime >= @AStartDate
  AND EndDateTime <= @AEndDate
END
ELSE
BEGIN
  SELECT DISTINCT ArrangementLines.ArrangementLineNumber, ArrangementLines.Pieces, ArrangementLines.Date, ArrangementLines.WareNumber,
  ArrangementLines.Time, ArrangementLines.DepartureTime
  INTO #tmpPieces
  FROM ArrangementReservationsXtab WITH (NOLOCK) 
  INNER JOIN Reservations WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
  INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
    AND ArrangementLines.WareNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareNumberCSV))
    AND ArrangementLines.WareNumber IN (
      SELECT WareNumber 
      FROM Ware WITH (NOLOCK)
      WHERE Ware.WareLinkNumber IN (SELECT WareLinkNumber FROM Ware WITH (NOLOCK) WHERE WareNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareNumberCSV)))
    )
  WHERE Status < 6 
  AND (ISNULL(@AArrangementLineNumber, -1) = -1 OR ArrangementLines.ArrangementLineNumber <> @AArrangementLineNumber)
  AND Date >= CONVERT(CHAR(8), @AStartDate, 112)
  AND Date <= CONVERT(CHAR(8), @AEndDate, 112)

  SELECT db.WareNumber, db.WareName, db.StartDateTime, db.EndDateTime, MIN(db.Available) AS Available
  FROM
  (
    SELECT Ware.WareNumber, Ware.WareName, RecurringPatternSpecified.StartDateTime, DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) AS EndDateTime, RecurringRule.RuleValue, 
    RecurringRule.RuleValue- 
    ISNULL((
      SELECT SUM(ISNULL(#tmpPieces.Pieces, 0))
      FROM #tmpPieces WITH (NOLOCK) 
      WHERE 1 = 1
      AND (CONVERT(DATETIME, #tmpPieces.Date, 112) + CONVERT(DATETIME, #tmpPieces.Time, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) OR
      CONVERT(DATETIME, #tmpPieces.Date, 112) + CONVERT(DATETIME, #tmpPieces.DepartureTime, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime))
    ), 0) AS Available
    FROM RecurringPatternSpecified WITH (NOLOCK) 
    INNER JOIN RecurringPatternWareXTab WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternID = RecurringPatternSpecified.FK_RecurringPatternID 
    INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
      AND RecurringRule.RuleType = 4 -- Number for sale
    INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = RecurringPatternSpecified.FK_RecurringPatternID
    INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = RecurringPatternWareXTab.FK_WareNumber
    WHERE RecurringPatternWareXTab.FK_WareNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareNumberCSV)) 
    AND RecurringPatternSpecified.StartDateTime BETWEEN @AStartDate AND @AEndDate
    AND ISNULL(RecurringPatternSpecified.Deleted, 0) = 0

    UNION

    SELECT Ware.WareNumber, Ware.WareName, RecurringPatternSpecified.StartDateTime, DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) AS EndDateTime, RecurringRule.RuleValue, 
    RecurringRule.RuleValue- 
    ISNULL((
      SELECT SUM(ISNULL(#tmpPieces.Pieces, 0))
      FROM #tmpPieces WITH (NOLOCK) 
      INNER JOIN Ware w1 WITH (NOLOCK) ON w1.WareLinkNumber = WareLinkGroup.Id
	    AND w1.WareNumber = #tmpPieces.WareNumber
      WHERE 1 = 1
      AND (CONVERT(DATETIME, #tmpPieces.Date, 112) + CONVERT(DATETIME, #tmpPieces.Time, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) OR
      CONVERT(DATETIME, #tmpPieces.Date, 112) + CONVERT(DATETIME, #tmpPieces.DepartureTime, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime))
    ), 0) AS Available
    FROM RecurringPatternSpecified WITH (NOLOCK) 
    INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.FK_RecurringPatternId = RecurringPatternSpecified.FK_RecurringPatternId
    INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
      AND RecurringRule.RuleType = 4 -- Number for sale
    INNER JOIN RecurringPattern WITH (NOLOCK) ON RecurringPattern.Id = RecurringPatternSpecified.FK_RecurringPatternID
    INNER JOIN Ware WITH (NOLOCK) ON Ware.WareLinkNumber = WareLinkGroup.Id
    WHERE 1=1
    AND Ware.WareNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareNumberCSV)) 
    AND RecurringPatternSpecified.StartDateTime BETWEEN @AStartDate AND @AEndDate
    AND ISNULL(RecurringPatternSpecified.Deleted, 0) = 0
  ) AS db
  GROUP BY db.WareNumber, db.WareName, db.StartDateTime, db.EndDateTime
  ORDER BY db.WareNumber, db.StartDateTime

  DROP TABLE #tmpPieces
END