CREATE PROCEDURE dbo.FBReportCustom @AFromDate DATETIME, @AToDate DATETIME, @AHotelCustomerNumberCSV VARCHAR(200), @AReservationStatusCSV VARCHAR(100), @ARoomNumberCSV VARCHAR(MAX), @AWareLinkGroupCSV VARCHAR(500), @AReservationTypeCSV VARCHAR(100), @ASortOrder INT AS
/*
  Created by Kent Johansen, February 2024
  Changes:
  KJ 20240620: Bug: Fix for "Arr line not added if no room line". Case #CR245684
  KJ 20240620: Bug: Fix for "#tmp.ReferenceNumber could not be bound"
  KJ 20240418: Bug: Fix for "No records found".

*/
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
DECLARE @NameAtHallPlan INT

SELECT @NameAtHallPlan = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NameAtHallPlan'

SELECT DISTINCT Reservations.ReferenceNumber, Reservations.GuestNumber
INTO #tmpRef
FROM Reservations WITH (NOLOCK)
LEFT OUTER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
LEFT OUTER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber
  AND ((ISNULL(@AHotelCustomerNumberCSV, '') = '') OR (ReservationMain.FkCustomerHotel IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))))
  AND ((ISNULL(@AReservationTypeCSV, '') = '') OR (ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationTypeCSV))))
INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
  AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
AND ArrangementLines.[Date] >= CONVERT(VARCHAR(8), @AFromDate, 112)
AND ArrangementLines.[Date] <= CONVERT(VARCHAR(8), @AToDate, 112)
WHERE ((ISNULL(@AReservationStatusCSV, '') = '') OR Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusCSV)))
AND (@ARoomNumberCSV = '' OR ReservationRoomLine.RoomNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberCSV)))

SELECT DISTINCT Reservations.ReferenceNumber, ArrangementLines.Date, ReservationMain.ReservationType,
WareLinkGroup.SortNumber AS WareLinkSortNumber, 'WareLinkGroup_' + CAST(Id AS VARCHAR) AS WareLinkGroupName,
ArrangementLines.ArrangementLineNumber, ArrangementLines.Pieces,
CASE WHEN CONVERT(BIT, (SELECT COUNT(*) FROM ArrangementRoomXTab  WITH (NOLOCK) WHERE ArrangementRoomXTab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber)) = 0 THEN
  ArrangementReservationsXtab.GuestNumber
ELSE
  0
END AS GuestNumber,
WareLinkGroup.Id AS WareLinkGroupId, 
ReservationAddress.LastName AS GuestLastName,
(
  SELECT LastName
  FROM ReservationAddress raComp WITH (NOLOCK)
  INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXtab.AddressNumber = raComp.AddressNumber
    AND ReservationAddressXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = 0
    AND raComp.AddressType = 2
) AS CompanyName,
(
  SELECT LastName
  FROM ReservationAddress raComp WITH (NOLOCK)
  INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXtab.AddressNumber = raComp.AddressNumber
    AND ReservationAddressXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = 0
    AND raComp.AddressType = 3
) AS AgencyName,
(
  SELECT LastName
  FROM ReservationAddress raComp WITH (NOLOCK)
  INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXtab.AddressNumber = raComp.AddressNumber
    AND ReservationAddressXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = 0
    AND raComp.AddressType = 4
) AS AgentName
INTO #tmp
FROM ArrangementLines WITH (NOLOCK)
INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
INNER JOIN Reservations WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
  AND ((ISNULL(@AReservationStatusCSV, '') = '') OR Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusCSV)))
INNER JOIN ReservationMain WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber
  AND ((ISNULL(@AHotelCustomerNumberCSV, '') = '') OR (ReservationMain.FkCustomerHotel IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))))
  AND ((ISNULL(@AReservationTypeCSV, '') = '') OR (ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationTypeCSV))))
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = ArrangementLines.WareNumber AND WareLinkNumber IS NOT NULL
INNER JOIN WareLinkGroup WITH (NOLOCK) ON WareLinkGroup.Id = Ware.WareLinkNumber
  AND WareLinkGroup.Id IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareLinkGroupCSV))
INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationAddressXTab.GuestNumber = 0
INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
  AND ReservationAddress.AddressType = 1
INNER JOIN #tmpRef WITH (NOLOCK) ON #tmpRef.ReferenceNumber = Reservations.ReferenceNumber
  AND #tmpRef.GuestNumber = Reservations.GuestNumber
WHERE ArrangementLines.[Date] >= CONVERT(VARCHAR(8), @AFromDate, 112)
AND ArrangementLines.[Date] <= CONVERT(VARCHAR(8), @AToDate, 112)

--SELECT * FROM #tmp

SELECT @cols = STUFF
(
  (
    SELECT ',' + QUOTENAME('WareLinkGroup_' + CAST(Id AS VARCHAR))
    FROM WareLinkGroup WITH (NOLOCK)
    WHERE Id IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareLinkGroupCSV))
    GROUP BY SortNumber, Id, Name
    ORDER BY SortNumber, Id, Name
    FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 
  1, 
  1, 
  ''
);

SET @query = N'

WITH T AS
(
  SELECT Date, #tmp.ReferenceNumber, #tmp.GuestNumber, WareLinkGroupName, ReservationType, SUM(Pieces) AS Pieces, '
IF @NameAtHallPlan = 1
  SET @query = @query + 'GuestLastName AS ArrName '
ELSE IF @NameAtHallPlan = 2
  SET @query = @query + 'CASE WHEN ISNULL(CompanyName, '''') = '''' THEN GuestLastName ELSE CompanyName END AS ArrName '
ELSE IF @NameAtHallPlan = 3
  SET @query = @query + 'CASE WHEN ISNULL(AgencyName, '''') <> '''' THEN AgencyName WHEN ISNULL(CompanyName, '''') <> '''' THEN CompanyName ELSE GuestLastName END AS ArrName '
ELSE IF @NameAtHallPlan = 4
  SET @query = @query + 'CASE WHEN ISNULL(AgentName, '''') = '''' THEN GuestLastName ELSE AgentName END AS ArrName '
SET @query = @query + 'FROM #tmp ';
SET @query = @query + 'GROUP BY Date, #tmp.ReferenceNumber, #tmp.GuestNumber, WareLinkGroupName, ReservationType, '
IF @NameAtHallPlan = 1
  SET @query = @query + 'GuestLastName '
ELSE IF @NameAtHallPlan = 2
  SET @query = @query + 'GuestLastName, CompanyName '
ELSE IF @NameAtHallPlan = 3
  SET @query = @query + 'AgencyName, CompanyName, GuestLastName '
ELSE IF @NameAtHallPlan = 4
  SET @query = @query + 'AgentName, GuestLastName '

SET @query = @query + ')

SELECT * 
FROM T 
PIVOT 
(
  SUM(Pieces) FOR WareLinkGroupName IN (' + @cols + ')
) AS pvt '

IF @ASortOrder = 0
  SET @query = @query + 'ORDER BY Date, ArrName'
ELSE IF @ASortOrder = 1
  SET @query = @query + 'ORDER BY Date'
ELSE IF @ASortOrder = 2
  SET @query = @query + 'ORDER BY Date, ReferenceNumber'

exec sp_executesql @query;

DROP TABLE #tmpRef
DROP TABLE #tmp