CREATE PROCEDURE dbo.ArrangementCalcLinkedWares @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AHotelNumber INT, @AUseTimeFields BIT = 0 AS
/*
--CREATED AV 2008 1011 Used in the lower ware plan on the hall days plan in booking module.
Changes:
  RN 20160803:  CR110139, Fixed random error messages that Time and DepartureTime columns was missing
  RN 20160525: CR103764, Fixed wrong join resulting in "NEW" reservations was not in the result
  RN 20160309: CR099531, Do not include main course reservations in result.
  BA 20160304: CR099649/Fixing error at Hooks hotel: Time and DepartureTime cannot be found.
  RN 20160208: Changed to choose if arrangement arrival/departure time should be included.
  RN 20150717: Added Date in resulting datasets sort order. #CR090375
  KJ 20150624: Does not show empty warelink groups. #CR089462
  RN 20150623:BUG: Added WareLinkGroup SortNumber. #CR089498
  KJ 20150609:Bug: Fix for "Wrong number of pieces". Case #CR088798 + more
  KJ 20150603: Total rewrite of sp.
--Last changed AV 2009 0407 fix for cannot insert the value null into column pieces1
*/
DECLARE @IncludeTentativeInArrReports BIT
DECLARE @InternalCourseProduction BIT

SELECT @IncludeTentativeInArrReports = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'IncludeTentativeInArrReports'
SET @InternalCourseProduction = 0
SELECT @InternalCourseProduction = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'InternalCourseProduction'

IF (@AUseTimeFields = 0)
BEGIN
  IF (@InternalCourseProduction = 0)
  BEGIN
    SELECT DISTINCT
    ArrangementLines.Pieces, 
    ArrangementLines.Date, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
    INTO #tmpArr 
    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 
    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
      AND Status NOT IN (7,8)
      AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
    WHERE ArrangementLines.Date>=@AStartDateTime  AND Arrangementlines.Date<=@AEndDateTime
      AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))

    SELECT SUM(Pieces) AS Pieces, Date, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name
    FROM WareLinkGroup WITH (NOLOCK)
    LEFT OUTER JOIN #tmpArr ON #tmpArr.WareLinkNumber = WareLinkGroup.Id
    GROUP BY Date, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
    ORDER BY WareLinkGroup.SortNumber, Date

    DROP TABLE #tmpArr
  END
  ELSE
  BEGIN
    SELECT DISTINCT
    ArrangementLines.Pieces, 
    ArrangementLines.Date, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
    INTO #tmpArr1
    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 
    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 ReservationReservationXtab WITH (NOLOCK) ON ReservationReservationXtab.FK_ReferenceNumberSub = Reservations.ReferenceNumber AND ReservationReservationXtab.FK_ReferenceNumber <> ReservationReservationXtab.FK_ReferenceNumberSub

    INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber
      AND Status NOT IN (7,8)
      AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
    WHERE ArrangementLines.Date>=@AStartDateTime  AND Arrangementlines.Date<=@AEndDateTime
      AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))

    SELECT SUM(Pieces) AS Pieces, Date, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name
    FROM WareLinkGroup WITH (NOLOCK)
    LEFT OUTER JOIN #tmpArr1 ON #tmpArr1.WareLinkNumber = WareLinkGroup.Id
    GROUP BY Date, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
    ORDER BY WareLinkGroup.SortNumber, Date

    DROP TABLE #tmpArr1
  END
END
ELSE
BEGIN
  IF (@InternalCourseProduction = 0)
  BEGIN
    SELECT DISTINCT
    ArrangementLines.Pieces, 
    ArrangementLines.Date, ArrangementLines.Time, ArrangementLines.DepartureTime, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
    INTO #tmpArr2
    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 
    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
      AND Status NOT IN (7,8)
      AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
    WHERE ArrangementLines.Date>=@AStartDateTime  AND Arrangementlines.Date<=@AEndDateTime
      AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))

    SELECT SUM(Pieces) AS Pieces, #tmpArr2.[Date], #tmpArr2.[Time], #tmpArr2.DepartureTime, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name
    FROM WareLinkGroup WITH (NOLOCK)
    LEFT OUTER JOIN #tmpArr2 ON #tmpArr2.WareLinkNumber = WareLinkGroup.Id
    GROUP BY Date, #tmpArr2.[Time], #tmpArr2.DepartureTime, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
    ORDER BY WareLinkGroup.SortNumber, #tmpArr2.[Date], #tmpArr2.[Time]
  
    DROP TABLE #tmpArr2
  END
  ELSE
  BEGIN
    SELECT DISTINCT
    ArrangementLines.Pieces, 
    ArrangementLines.[Date], ArrangementLines.[Time], ArrangementLines.DepartureTime, Ware.WareLinkNumber, WareLinkGroup.Name, ArrangementLines.ArrangementLineNumber, WareLinkGroup.SortNumber
    INTO #tmpArr3
    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 
    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

	LEFT JOIN ReservationReservationXtab WITH (NOLOCK) ON ReservationReservationXtab.FK_ReferenceNumber = FK_ReferenceNumberSub

    INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber
      AND Status NOT IN (7,8)
      AND (@IncludeTentativeInArrReports = 1 OR (@IncludeTentativeInArrReports = 0 AND Status <> 4) )
    WHERE ArrangementLines.Date>=@AStartDateTime  AND Arrangementlines.Date<=@AEndDateTime
      AND (@AHotelNumber = -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))

    SELECT SUM(Pieces) AS Pieces, #tmpArr3.[Date], #tmpArr3.[Time], #tmpArr3.DepartureTime, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name
    FROM WareLinkGroup WITH (NOLOCK)
    LEFT OUTER JOIN #tmpArr3 ON #tmpArr3.WareLinkNumber = WareLinkGroup.Id
    GROUP BY Date, #tmpArr3.[Time], #tmpArr3.DepartureTime, WareLinkGroup.Id, WareLinkGroup.Name, WareLinkGroup.SortNumber
    ORDER BY WareLinkGroup.SortNumber, #tmpArr3.[Date], #tmpArr3.[Time]
  
    DROP TABLE #tmpArr3
  END

END