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

  Changes:
    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"
*/

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(ArrangementLines.Pieces, 0))
    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 = Ware.WareNumber
    WHERE Status < 6 
    AND (ISNULL(@AArrangementLineNumber, -1) = -1 OR ArrangementLines.ArrangementLineNumber <> @AArrangementLineNumber)
    AND (CONVERT(DATETIME, ArrangementLines.Date, 112) + CONVERT(DATETIME, ArrangementLines.Time, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) OR
    CONVERT(DATETIME, ArrangementLines.Date, 112) + CONVERT(DATETIME, ArrangementLines.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(ArrangementLines.Pieces, 0))
    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 = Ware.WareNumber
    INNER JOIN Ware w1 WITH (NOLOCK) ON w1.WareLinkNumber = WareLinkGroup.Id
	  AND w1.WareNumber = ArrangementLines.WareNumber
    WHERE Status < 6 
    AND (ISNULL(@AArrangementLineNumber, -1) = -1 OR ArrangementLines.ArrangementLineNumber <> @AArrangementLineNumber)
    AND (CONVERT(DATETIME, ArrangementLines.Date, 112) + CONVERT(DATETIME, ArrangementLines.Time, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) OR
    CONVERT(DATETIME, ArrangementLines.Date, 112) + CONVERT(DATETIME, ArrangementLines.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