CREATE PROCEDURE dbo.ReservationStatusChangeLog @AHotelNumberCSV VARCHAR(500), @AFromDate DATETIME, @AEndDate DATETIME, @AFromStatusCSV VARCHAR(100), @AToStatusCSV VARCHAR(100), @AReservationTypeCSV VARCHAR(100),
  @AInclVAT BIT, @AInclPackage BIT AS
/*
  Created by Kent Johansen December 2015
  Purpose: Returns log for reservation status changes
  
  Changes:
    KJ 20250318: Bug: Fix for "Field 'Modified_Date' not found".
    KJ 20250311: Ext: Status change report show turnover and pax from MIS. Also show all reservations and not only reservations with rooms.
    KJ 20250110: Bug: Fix for "Conversion failed when converting the varchar value '2023-09-17 00:00:00.000' to data type int". Case #CR252977
*/

SELECT DISTINCT Audit_Log_Data.Key1 AS ReferenceNumber, Audit_Log_Data.Key2 AS GuestNumber, Audit_Log_Transactions.Modified_Date
INTO #tmptable
FROM Audit_Log_Transactions WITH (NOLOCK)
INNER JOIN Audit_Log_Data WITH (NOLOCK) ON Audit_Log_Data.Audit_Log_Transaction_Id = Audit_Log_Transactions.Audit_Log_Transaction_Id
WHERE Audit_Log_Transactions.Table_Name = 'Reservations' 
AND Audit_Log_Data.Col_Name = 'Status'
AND Audit_Log_Transactions.Modified_Date BETWEEN @AFromDate AND @AEndDate
AND Audit_Log_Transactions.Audit_Action_Id = 1 -- Only updates
AND dbo.IsReallyNumeric(Audit_Log_Data.Key2) = 1 
AND dbo.IsReallyNumeric(Audit_Log_Data.Old_Value) = 1 AND CAST(Audit_Log_Data.Old_Value AS INT) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AFromStatusCSV))
AND dbo.IsReallyNumeric(Audit_Log_Data.New_Value) = 1 AND CAST(Audit_Log_Data.New_Value AS INT) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AToStatusCSV))

UNION

-- Union with changes from reservationroomline where guest number = 0
SELECT Audit_Log_Data.Key1 AS ReferenceNumber, Audit_Log_Data.Key2 AS GuestNumber, Audit_Log_Transactions.Modified_Date
FROM Audit_Log_Transactions WITH (NOLOCK)
INNER JOIN Audit_Log_Data WITH (NOLOCK) ON Audit_Log_Data.Audit_Log_Transaction_Id = Audit_Log_Transactions.Audit_Log_Transaction_Id
WHERE Audit_Log_Transactions.Table_Name = 'ReservationRoomLine' 
AND Audit_Log_Data.Col_Name = 'StatusRL'
AND Audit_Log_Transactions.Modified_Date BETWEEN @AFromDate AND @AEndDate
AND Audit_Log_Transactions.Audit_Action_Id = 1 -- Only updates
AND CAST(Audit_Log_Data.Old_Value AS INT) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AFromStatusCSV))
AND CAST(Audit_Log_Data.New_Value AS INT) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AToStatusCSV))

--SELECT * FROM #tmptable

SELECT MISTableView.ValueType, MISTableView.ReferenceNumber, MISTableView.GuestNumber, Modified_Date,
CASE WHEN @AInclPackage = 1 THEN
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISTableView.BookedPriceInclPackage) + SUM(MISTableView.ActualPriceInclPackage) 
  ELSE  
    SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat) 
  END
ELSE
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISTableView.BookedPriceExPackage) + SUM(MISTableView.ActualPriceExPackage) 
  ELSE  
    SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) 
  END
END AS Turnover,
SUM(MISTableView.BookedNumberOfRooms) + SUM(MISTableView.ActualNumberOfRooms) AS NumberOfRooms,
SUM(MISTableView.BookedAdult)+SUM(MISTableView.BookedChildren1)+SUM(MISTableView.BookedChildren2)+SUM(MISTableView.ActualAdult)+SUM(MISTableView.ActualChildren1)+SUM(MISTableView.ActualChildren2) AS PAX
INTO #MISTmp
FROM MISTableView WITH (NOLOCK)
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
  AND (ISNULL(@AHotelNumberCSV, '') = '' OR Customer.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberCSV)))
INNER JOIN #tmptable ON #tmptable.ReferenceNumber = MISTableView.ReferenceNumber
  AND #tmptable.GuestNumber = MISTableView.GuestNumber
WHERE ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)
GROUP BY MISTableView.ValueType, MISTableView.ReferenceNumber, MISTableView.GuestNumber, Modified_Date

--SELECT * FROM #MISTmp

-- Go ahead and process the abone result set.
SELECT DISTINCT Reservations.ReferenceNumber, Reservations.GuestNumber, Reservations.ArrivalDate, Modified_Date,
ReservationAddress.LastName + CASE WHEN ReservationAddress.FirstName = '' THEN '' ELSE ', ' + ReservationAddress.FirstName END AS FullName,
raComp.LastName AS CompanyName,
(
  SELECT SUM(Turnover)
  FROM #MISTmp
  WHERE #MISTmp.ReferenceNumber = Reservations.ReferenceNumber
  AND #MISTmp.GuestNumber = Reservations.GuestNumber
  AND #MISTmp.ValueType = 1
) AS TurnoverRooms,
(
  SELECT SUM(NumberOfRooms)
  FROM #MISTmp
  WHERE #MISTmp.ReferenceNumber = Reservations.ReferenceNumber
  AND #MISTmp.GuestNumber = Reservations.GuestNumber
  AND #MISTmp.ValueType = 1
) AS NumberOfRooms,
(
  SELECT SUM(Turnover)
  FROM #MISTmp
  WHERE #MISTmp.ReferenceNumber = Reservations.ReferenceNumber
  AND #MISTmp.GuestNumber = Reservations.GuestNumber
  AND #MISTmp.ValueType = 2
) AS TurnoverHals,
(
  SELECT SUM(Turnover)
  FROM #MISTmp
  WHERE #MISTmp.ReferenceNumber = Reservations.ReferenceNumber
  AND #MISTmp.GuestNumber = Reservations.GuestNumber
  AND #MISTmp.ValueType = 3
) AS TurnoverDayMeeting, 
(
  SELECT SUM(Pax)
  FROM #MISTmp
  WHERE #MISTmp.ReferenceNumber = Reservations.ReferenceNumber
  AND #MISTmp.GuestNumber = Reservations.GuestNumber
  AND #MISTmp.ValueType = 3
) AS PaxDayMeeting, 
(
  SELECT SUM(Turnover)
  FROM #MISTmp
  WHERE #MISTmp.ReferenceNumber = Reservations.ReferenceNumber
  AND #MISTmp.GuestNumber = Reservations.GuestNumber
  AND #MISTmp.ValueType = 4
) AS TurnoverRestaurant, 
(
  SELECT SUM(Turnover)
  FROM #MISTmp
  WHERE #MISTmp.ReferenceNumber = Reservations.ReferenceNumber
  AND #MISTmp.GuestNumber = Reservations.GuestNumber
  AND #MISTmp.ValueType = 5
) AS TurnoverOther
FROM #MISTmp
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = #MISTmp.ReferenceNumber 
  AND Reservations.GuestNumber = #MISTmp.GuestNumber 
INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
  AND ReservationAddress.AddressType = 1
LEFT OUTER JOIN ReservationAddressXTab raxComp WITH (NOLOCK) ON raxComp.ReferenceNumber = Reservations.ReferenceNumber
  AND raxComp.GuestNumber = Reservations.GuestNumber
  AND raxComp.AddressTypeNumber = 2
LEFT OUTER JOIN ReservationAddress raComp WITH (NOLOCK) ON raComp.AddressNumber = raxComp.AddressNumber
  AND raComp.AddressType = 2
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  AND (@AHotelNumberCSV = '' OR ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberCSV)))
  AND (ISNULL(@AReservationTypeCSV, '') = '' OR ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationTypeCSV)))

DROP TABLE #tmptable
DROP TABLE #MISTmp