CREATE PROCEDURE dbo.ArrangementLinkWares @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AHotelNumber INT AS
/*
  Created by Kent Johansen June 2015
  Return a list of linked wares grouped by recurring pattern
  Changes:
    KJ 20221216: Ext: Multiple rules per WareLinkGroup.
    KJ 20160114: Optimized sql.
    KJ 20150609: Fix for "Wrong number of Pieces"

*/
DECLARE @IncludeTentativeInArrReports BIT
SELECT @IncludeTentativeInArrReports = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'IncludeTentativeInArrReports'

SELECT * INTO TmpArr FROM (
  SELECT 
  ISNULL((
    SELECT SUM(ISNULL(AL.Pieces, 0))
    FROM ArrangementReservationsXtab ARXtab WITH (NOLOCK)
    INNER JOIN Reservations R WITH (NOLOCK) ON ARXtab.ReferenceNumber = R.ReferenceNumber
      AND ARXtab.GuestNumber = R.GuestNumber
      AND R.Status NOT IN (7,8)
      AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND R.Status <> 4))
    INNER JOIN Arrangementlines AL WITH (NOLOCK) ON ARXtab.ArrangementLineNumber = AL.ArrangementLineNumber
      AND AL.WareNumber = Ware.WareNumber
      AND AL.[Date] >= @AStartDateTime
      AND AL.[Date] <= @AEndDateTime
    INNER JOIN ReservationMain RM WITH (NOLOCK) ON R.ReferenceNumber = RM.ReferenceNumber
      AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND RM.HotelNumber = @AHotelNumber))
    WHERE 1=1
    AND (CONVERT(DATETIME, AL.Date, 112) + CONVERT(DATETIME, AL.Time, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) OR
    CONVERT(DATETIME, AL.Date, 112) + CONVERT(DATETIME, AL.DepartureTime, 108) BETWEEN RecurringPatternSpecified.StartDateTime AND DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime))
  ), 0) AS Pieces, 
  RecurringPatternSpecified.StartDateTime, DATEADD(millisecond, -3, RecurringPatternSpecified.EndDateTime) AS EndDateTime, Ware.WareLinkNumber, Ware.WareNumber, Ware.WareName, -1 AS ArrangementLineNumber
  FROM RecurringPatternSpecified WITH (NOLOCK)
  INNER JOIN WareLinkGroupRecurringPatternXTab WITH (NOLOCK) ON WareLinkGroupRecurringPatternXTab.FKRecurringPatternId = RecurringPatternSpecified.FK_RecurringPatternId
  INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = WareLinkGroupRecurringPatternXTab.FKWareLinkGroupId
  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
  INNER JOIN WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
    AND WareGroups.WareGroupNumber IN (8)
  WHERE 1=1
  AND RecurringPatternSpecified.StartDateTime BETWEEN @AStartDateTime AND @AStartDateTime+1
  AND ISNULL(RecurringPatternSpecified.Deleted, 0) = 0

  UNION

  SELECT DISTINCT
  ArrangementLines.Pieces, 
  CONVERT(datetime, ArrangementLines.Date, 112), CONVERT(DATETIME, ArrangementLines.Date, 112), Ware.WareLinkNumber, Ware.WareNumber, Ware.WareName, ArrangementLines.ArrangementLineNumber
  FROM ArrangementReservationsXtab WITH (NOLOCK) 
  INNER JOIN Reservations WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
    AND Status NOT IN (7,8)
    AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
  INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber 
  INNER JOIN Ware WITH (NOLOCK) ON ArrangementLines.WareNumber = Ware.WareNumber 
    AND ISNULL(Ware.WareLinkNumber, 0) > 0 
  INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.Referencenumber
  INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber
  LEFT OUTER JOIN WareLinkGroupRecurringPatternXTab WITH (NOLOCK) ON WareLinkGroupRecurringPatternXTab.FKWareLinkGroupId = WareLinkGroup.Id
  WHERE ArrangementLines.Date >= @AStartDateTime
  AND Arrangementlines.Date <= @AEndDateTime
  AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
  AND WareLinkGroupRecurringPatternXTab.FKRecurringPatternId IS NULL
) AS tmp

SELECT SUM(ISNULL(Pieces, 0)) AS Pieces, StartDateTime, DATEADD(millisecond, -3, EndDateTime) AS EndDateTime, WareLinkNumber, WareNumber, WareName
FROM TmpArr
GROUP BY StartDateTime, DATEADD(millisecond, -3, EndDateTime), WareLinkNumber, WareNumber, WareName
DROP TABLE TmpArr