CREATE PROCEDURE dbo.ArrangementCalcLinkedWares @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AHotelNumber INT, @AUseTimeFields BIT = 0, @ASearchType INT = 0, @AReferenceNumberCSV VARCHAR(1000) = '' AS
/*
--CREATED AV 2008 1011 Used in the lower ware plan on the hall days plan in booking module.
Changes:
  KJ 20181002: Wrong number of wares if All selected in hall days plan. Case #CR153298
  KJ 20170712: Ext: Search for refno and Main course refno.
  KJ 20170117: Req #3286. Added Warelink group to Arrangement -> Weekplan report
  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
DECLARE @HotelCustomerNumber INT
DECLARE @AStartDate CHAR(8)
DECLARE @AEndDate CHAR (8)

SET @AStartDate = CONVERT(CHAR(8), @AStartDatetime, 112)
SET @AEndDate = CONVERT(CHAR(8), @AEndDatetime, 112)

SELECT @HotelCustomerNumber = CustomerNumber
FROM Customer WITH (NOLOCK) 
WHERE HotelNumber = @AHotelNumber
AND LocalHotel=1 
AND CustomerTypeNumber = 7
AND ISNULL(MarkAsDeleted, 0) = 0

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

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 >= @AStartDate  AND Arrangementlines.Date <= @AEndDate
    AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
    AND (ISNULL(@AReferenceNumberCSV, '') = '' OR ArrangementReservationsXtab.ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV)))
    
    SELECT SUM(Pieces) AS Pieces, Date, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
    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 >= @AStartDate  AND Arrangementlines.Date <= @AEndDate
      AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))

	  AND (
      (ISNULL(@AReferenceNumberCSV, '') = '' AND (ArrangementReservationsXtab.ReferenceNumber NOT IN (SELECT FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumber = FK_ReferenceNumberSub)))
      OR
      (ISNULL(@AReferenceNumberCSV, '') <> '') AND ((@ASearchType=1 AND ArrangementReservationsXtab.ReferenceNumber = @AReferenceNumberCSV) OR (@ASearchType=2 AND ArrangementReservationsXtab.ReferenceNumber IN (
        SELECT FK_ReferenceNumberSub
        FROM ReservationReservationXtab WITH (NOLOCK)
        WHERE FK_ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV))
      )))
    )

    SELECT SUM(Pieces) AS Pieces, Date, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
    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, ISNULL(ArrangementLines.DepartureTime, '') AS 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 >= @AStartDate  AND Arrangementlines.Date <= @AEndDate
    AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
    AND (ISNULL(@AReferenceNumberCSV, '') = '' OR ArrangementReservationsXtab.ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV)))

    SELECT SUM(Pieces) AS Pieces, #tmpArr2.[Date], #tmpArr2.[Time], #tmpArr2.DepartureTime, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
    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], ISNULL(ArrangementLines.DepartureTime, '') AS 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

	--INNER JOIN ReservationReservationXtab WITH (NOLOCK) ON ReservationReservationXtab.FK_ReferenceNumberSub = ArrangementReservationsXtab.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 >= @AStartDate AND Arrangementlines.Date <= @AEndDate
      AND (@AHotelNumber <= -1 OR (@AHotelNumber > -1 AND ReservationMain.HotelNumber = @AHotelNumber))
	  AND (
      (ISNULL(@AReferenceNumberCSV, '') = '' AND (ArrangementReservationsXtab.ReferenceNumber NOT IN (SELECT FK_ReferenceNumber FROM ReservationReservationXtab WITH (NOLOCK) WHERE FK_ReferenceNumber = FK_ReferenceNumberSub)))
      OR
      (ISNULL(@AReferenceNumberCSV, '') <> '') AND ((@ASearchType=1 AND ArrangementReservationsXtab.ReferenceNumber = @AReferenceNumberCSV) OR (@ASearchType=2 AND ArrangementReservationsXtab.ReferenceNumber IN (
        SELECT FK_ReferenceNumberSub
        FROM ReservationReservationXtab WITH (NOLOCK)
        WHERE FK_ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReferenceNumberCSV))
      )))
    )

    SELECT SUM(Pieces) AS Pieces, #tmpArr3.[Date], #tmpArr3.[Time], #tmpArr3.DepartureTime, WareLinkGroup.Id AS WareLinkNumber, WareLinkGroup.Name AS Name, WareLinkGroup.SortNumber
    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