CREATE PROCEDURE dbo.MISDeletedReport @AHotelCustomerNumberCSV VARCHAR(100), @ASegmentNumberCSV VARCHAR(500), @ACountryNumberCSV VARCHAR(1000), @ARefNumberCSV VARCHAR(500), 
  @AResStatusCSV VARCHAR(50), @AFromDate DATETIME, @AToDate DATETIME, @AInclVAT BIT, @AInclPackage BIT, @AInclFolioAmount BIT, @ABasedOnArrival BIT, @AIncludeProvision BIT, @AExcludeRefNumbers BIT AS
/*
  Created by Kent Johansen, March 2020
  Get MIS numbers for MIS Deleted report aka Corona/covid19 report

  Changes:
    KJ 20200925: Bug: Fix for "No turnawayReason is shown". Case #CR199328
    KJ 20200922: Group by turnaway reason
    KJ 20200720: Group by turnaway note.
    KJ 20200625: Added Turnaway note. Req #3715

*/
SET NOCOUNT ON

SELECT MISTableViewDeleted.ReferenceNumber, MISTableViewDeleted.GuestNumber, MISTableViewDeleted.CancellationDate,
(
  SELECT ReservationAddress.CountryNumber
  FROM ReservationAddress WITH (NOLOCK)
  INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXtab.AddressNumber = ReservationAddress.AddressNumber
    AND ReservationAddressXtab.ReferenceNumber = MISTableViewDeleted.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = MISTableViewDeleted.GuestNumber
    AND ReservationAddressXTab.AddressTypeNumber = 1
) AS CountryNumber
INTO #MISDeleted
FROM MISTableViewDeleted WITH (NOLOCK)
WHERE 1 = 1
AND ((@ABasedOnArrival = 1  AND MISTableViewDeleted.DateStamp BETWEEN @AFromDate AND @AToDate) OR
 (@ABasedOnArrival = 0  AND MISTableViewDeleted.CancellationDate BETWEEN @AFromDate AND @AToDate))
AND MISTableViewDeleted.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND (@ASegmentNumberCSV = '' OR MISTableViewDeleted.SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND MISTableViewDeleted.ReservationStatus IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AResStatusCSV))
AND MISTableViewDeleted.ReferenceNumber > 0

AND (@ARefNumberCSV = '' OR 
  (
    (@AExcludeRefNumbers = 1 AND MISTableViewDeleted.ReferenceNumber NOT IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARefNumberCSV))) OR
    (@AExcludeRefNumbers = 0 AND MISTableViewDeleted.ReferenceNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARefNumberCSV)))
  )
)
AND (@AInclFolioAmount = 1 OR ISNULL(MISTableViewDeleted.FolioNumber, 0) <= 0)
AND (@AIncludeProvision = 1 OR ISNULL(MISTableViewDeleted.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
GROUP BY MISTableViewDeleted.ReferenceNumber, MISTableViewDeleted.GuestNumber, MISTableViewDeleted.CancellationDate
HAVING (CASE WHEN @AInclPackage = 1 THEN
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISTableViewDeleted.BookedPriceInclPackage) + SUM(MISTableViewDeleted.ActualPriceInclPackage) 
  ELSE  
    SUM(MISTableViewDeleted.BookedPriceInclPackageExVat) + SUM(MISTableViewDeleted.ActualPriceInclPackageExVat) 
  END
ELSE
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISTableViewDeleted.BookedPriceExPackage) + SUM(MISTableViewDeleted.ActualPriceExPackage) 
  ELSE  
    SUM(MISTableViewDeleted.BookedPriceExPackageExVat) + SUM(MISTableViewDeleted.ActualPriceExPackageExVat) 
  END
END) > 0

SELECT #MISDeleted.ReferenceNumber, #MISDeleted.GuestNumber, #MISDeleted.CancellationDate,

CASE WHEN ISNULL(ReservationAddress.FirstName, '') = '' THEN
  ReservationAddress.LastName
ELSE
  ISNULL(ReservationAddress.FirstName, '') + ' ' + ReservationAddress.LastName
END AS FirstLastName,
CASE WHEN ISNULL(ReservationAddress.FirstName, '') = '' THEN
  ReservationAddress.LastName
ELSE
  ISNULL(ReservationAddress.LastName, '') + ' ' + ReservationAddress.FirstName
END AS LastFirstName,
(
  SELECT LastName
  FROM ReservationAddress raComp WITH (NOLOCK)
  INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXtab.AddressNumber = raComp.AddressNumber
    AND ReservationAddressXtab.ReferenceNumber = #MISDeleted.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = #MISDeleted.GuestNumber
    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 = #MISDeleted.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = #MISDeleted.GuestNumber
    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 = #MISDeleted.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = #MISDeleted.GuestNumber
    AND raComp.AddressType = 4
) AS AgentName,
(
  SELECT MAX(ArrDate)
  FROM
  (
    SELECT MIN(ArrivalDate) AS ArrDate 
    FROM Reservations WITH (NOLOCK) 
    WHERE Reservations.ReferenceNumber = #MISDeleted.ReferenceNumber 
    AND Reservations.GuestNumber = #MISDeleted.GuestNumber
    UNION
    SELECT MIN(ArrivalDate) 
    FROM ReservationConferenceline WITH (NOLOCK)
    WHERE ReservationConferenceline.ReferenceNumber = #MISDeleted.ReferenceNumber
    AND ISNULL(ReservationConferenceline.GuestNo, 0) = #MISDeleted.GuestNumber
    UNION
    SELECT MIN(ArrivalDate) 
    FROM ReservationRoomLine WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXtab WITH (NOLOCK) ON ReservationRoomLineXtab.LineNumber = ReservationRoomLine.LineNumber
      AND ReservationRoomLineXtab.ReferenceNumber = #MISDeleted.ReferenceNumber
      AND ReservationRoomLineXtab.GuestNumber = #MISDeleted.GuestNumber
  ) x
)
AS ArrivalDate, 

(
  SELECT MAX(DepDate)
  FROM
  (
    SELECT MAX(DepartureDate) AS DepDate 
    FROM Reservations WITH (NOLOCK) 
    WHERE Reservations.ReferenceNumber = #MISDeleted.ReferenceNumber 
    AND Reservations.GuestNumber = #MISDeleted.GuestNumber
    UNION
    SELECT MAX(DepartureDate) 
    FROM ReservationConferenceline WITH (NOLOCK)
    WHERE ReservationConferenceline.ReferenceNumber = #MISDeleted.ReferenceNumber
    AND ISNULL(ReservationConferenceline.GuestNo, 0) = #MISDeleted.GuestNumber
    UNION
    SELECT MAX(DepartureDate) 
    FROM ReservationRoomLine WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXtab WITH (NOLOCK) ON ReservationRoomLineXtab.LineNumber = ReservationRoomLine.LineNumber
      AND ReservationRoomLineXtab.ReferenceNumber = #MISDeleted.ReferenceNumber
      AND ReservationRoomLineXtab.GuestNumber = #MISDeleted.GuestNumber
  ) x
)
AS DepartureDate,
(
  SELECT CASE WHEN @AInclPackage = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceInclPackage) + SUM(MISTableViewDeleted.ActualPriceInclPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceInclPackageExVat) + SUM(MISTableViewDeleted.ActualPriceInclPackageExVat) 
    END
  ELSE
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceExPackage) + SUM(MISTableViewDeleted.ActualPriceExPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceExPackageExVat) + SUM(MISTableViewDeleted.ActualPriceExPackageExVat) 
    END
  END
  FROM MISTableViewDeleted WITH (NOLOCK)
  WHERE MISTableViewDeleted.ReferenceNumber = #MISDeleted.ReferenceNumber
  AND MISTableViewDeleted.GuestNumber = #MISDeleted.GuestNumber
  AND MISTableViewDeleted.ValueType = 1
) AS TurnoverRoom,
(
  SELECT CASE WHEN @AInclPackage = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceInclPackage) + SUM(MISTableViewDeleted.ActualPriceInclPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceInclPackageExVat) + SUM(MISTableViewDeleted.ActualPriceInclPackageExVat) 
    END
  ELSE
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceExPackage) + SUM(MISTableViewDeleted.ActualPriceExPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceExPackageExVat) + SUM(MISTableViewDeleted.ActualPriceExPackageExVat) 
    END
  END
  FROM MISTableViewDeleted WITH (NOLOCK)
  WHERE MISTableViewDeleted.ReferenceNumber = #MISDeleted.ReferenceNumber
  AND MISTableViewDeleted.GuestNumber = #MISDeleted.GuestNumber
  AND MISTableViewDeleted.ValueType = 3
) AS TurnoverDaymeeting,
(
  SELECT CASE WHEN @AInclPackage = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceInclPackage) + SUM(MISTableViewDeleted.ActualPriceInclPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceInclPackageExVat) + SUM(MISTableViewDeleted.ActualPriceInclPackageExVat) 
    END
  ELSE
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceExPackage) + SUM(MISTableViewDeleted.ActualPriceExPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceExPackageExVat) + SUM(MISTableViewDeleted.ActualPriceExPackageExVat) 
    END
  END
  FROM MISTableViewDeleted WITH (NOLOCK)
  WHERE MISTableViewDeleted.ReferenceNumber = #MISDeleted.ReferenceNumber
  AND MISTableViewDeleted.GuestNumber = #MISDeleted.GuestNumber
  AND MISTableViewDeleted.ValueType = 2
) AS TurnoverHalls,
(
  SELECT CASE WHEN @AInclPackage = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceInclPackage) + SUM(MISTableViewDeleted.ActualPriceInclPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceInclPackageExVat) + SUM(MISTableViewDeleted.ActualPriceInclPackageExVat) 
    END
  ELSE
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceExPackage) + SUM(MISTableViewDeleted.ActualPriceExPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceExPackageExVat) + SUM(MISTableViewDeleted.ActualPriceExPackageExVat) 
    END
  END
  FROM MISTableViewDeleted WITH (NOLOCK)
  WHERE MISTableViewDeleted.ReferenceNumber = #MISDeleted.ReferenceNumber
  AND MISTableViewDeleted.GuestNumber = #MISDeleted.GuestNumber
  AND MISTableViewDeleted.ValueType = 4
) AS TurnoverRestaurant,
(
  SELECT CASE WHEN @AInclPackage = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceInclPackage) + SUM(MISTableViewDeleted.ActualPriceInclPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceInclPackageExVat) + SUM(MISTableViewDeleted.ActualPriceInclPackageExVat) 
    END
  ELSE
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableViewDeleted.BookedPriceExPackage) + SUM(MISTableViewDeleted.ActualPriceExPackage) 
    ELSE  
      SUM(MISTableViewDeleted.BookedPriceExPackageExVat) + SUM(MISTableViewDeleted.ActualPriceExPackageExVat) 
    END
  END
  FROM MISTableViewDeleted WITH (NOLOCK)
  WHERE MISTableViewDeleted.ReferenceNumber = #MISDeleted.ReferenceNumber
  AND MISTableViewDeleted.GuestNumber = #MISDeleted.GuestNumber
  AND MISTableViewDeleted.ValueType = 5
) AS TurnoverOther,
CAST(ReservationNotes.Text AS VARCHAR(8000)) AS TurnawayNote, ISNULL(CancellationCodes.Text, '') AS TurnawayReason, ISNULL(CancellationCodes.Number, 9999) AS TurnAwayReasonNumber 
FROM #MISDeleted
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = #MISDeleted.ReferenceNumber
  AND Reservations.GuestNumber = #MISDeleted.GuestNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = #MISDeleted.ReferenceNumber
  AND ReservationAddressXTab.GuestNumber = #MISDeleted.GuestNumber
INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXtab.AddressNumber
  AND ReservationAddress.AddressType = 1
LEFT OUTER JOIN ReservationNotes WITH (NOLOCK) ON ReservationNotes.ReferenceNumber = #MISDeleted.ReferenceNumber
  AND ReservationNotes.GuestNumber = #MISDeleted.GuestNumber
  AND ReservationNotes.NotePage = 4
LEFT OUTER JOIN CancellationCodes WITH (NOLOCK) ON CancellationCodes.Number = Reservations.TurnawayNumber
WHERE (@ACountryNumberCSV = '' OR #MISDeleted.CountryNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ACountryNumberCSV)))
ORDER BY #MISDeleted.ReferenceNumber, #MISDeleted.GuestNumber

DROP TABLE #MISDeleted