CREATE PROCEDURE ReservationStatusChangeLog @AHotelNumberCSV VARCHAR(500), @AFromDate DATETIME, @AEndDate DATETIME, @AFromStatusCSV VARCHAR(100), @AToStatusCSV VARCHAR(100) AS
/*
  Created by Kent Johansen December 2015
  Purpose: Returns log for reservation status changes
  
  Changes:

*/

-- Select status changes for reservations > 0
SELECT Audit_Log_Data.Key1 AS ReferenceNumber, Audit_Log_Data.Key2 AS GuestNumber, Audit_Log_Transactions.Modified_Date,
ISNULL(
(
  SELECT TOP 1 ALD.New_Value
  FROM Audit_Log_Transactions ALT WITH (NOLOCK)
  INNER JOIN Audit_Log_Data ALD WITH (NOLOCK) ON ALD.Audit_Log_Transaction_Id = ALT.Audit_Log_Transaction_Id
  WHERE ALT.Table_Name = 'ReservationRoomLineXtab'
  AND ALD.Col_Name = 'LineNumber'
  AND ALT.Modified_Date <= Audit_Log_Transactions.Modified_Date
  AND ALD.Key1 = Audit_Log_Data.Key1
  AND ALD.Key2 = Audit_Log_Data.Key2
  ORDER BY ALD.Audit_Log_Data_Id DESC
), -1) AS LineNumber
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 Audit_Log_Data.Key2 > 0
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))

UNION
-- Union with changes from reservationroomline where guest number = 0
SELECT 
ISNULL(
(
  SELECT TOP 1 ALD.New_Value
  FROM Audit_Log_Transactions ALT WITH (NOLOCK)
  INNER JOIN Audit_Log_Data ALD WITH (NOLOCK) ON ALD.Audit_Log_Transaction_Id = ALT.Audit_Log_Transaction_Id
  WHERE ALT.Table_Name = 'ReservationRoomLineXtab'
  AND ALD.Col_Name = 'ReferenceNumber'
  AND ALT.Modified_Date <= Audit_Log_Transactions.Modified_Date
  AND ALD.Key3 = Audit_Log_Data.Key1
  ORDER BY ALD.Audit_Log_Data_Id DESC
), -1) AS ReferenceNumber,

ISNULL(
(
  SELECT TOP 1 ALD.New_Value
  FROM Audit_Log_Transactions ALT WITH (NOLOCK)
  INNER JOIN Audit_Log_Data ALD WITH (NOLOCK) ON ALD.Audit_Log_Transaction_Id = ALT.Audit_Log_Transaction_Id
  WHERE ALT.Table_Name = 'ReservationRoomLineXtab'
  AND ALD.Col_Name = 'GuestNumber'
  AND ALT.Modified_Date <= Audit_Log_Transactions.Modified_Date
  AND ALD.Key3 = Audit_Log_Data.Key1
  ORDER BY ALD.Audit_Log_Data_Id DESC
), -1) AS GuestNumber,

Audit_Log_Transactions.Modified_Date,

ISNULL(
(
  SELECT TOP 1 ALD.New_Value
  FROM Audit_Log_Transactions ALT WITH (NOLOCK)
  INNER JOIN Audit_Log_Data ALD WITH (NOLOCK) ON ALD.Audit_Log_Transaction_Id = ALT.Audit_Log_Transaction_Id
  WHERE ALT.Table_Name = 'ReservationRoomLineXtab'
  AND ALD.Col_Name = 'LineNumber'
  AND ALT.Modified_Date <= Audit_Log_Transactions.Modified_Date
  AND ALD.Key3 = Audit_Log_Data.Key1
  ORDER BY ALD.Audit_Log_Data_Id DESC
), -1) AS LineNumber
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))
AND ISNULL(
(
  SELECT TOP 1 ALD.New_Value
  FROM Audit_Log_Transactions ALT WITH (NOLOCK)
  INNER JOIN Audit_Log_Data ALD WITH (NOLOCK) ON ALD.Audit_Log_Transaction_Id = ALT.Audit_Log_Transaction_Id
  WHERE ALT.Table_Name = 'ReservationRoomLineXtab'
  AND ALD.Col_Name = 'GuestNumber'
  AND ALT.Modified_Date <= Audit_Log_Transactions.Modified_Date
  AND ALD.Key3 = Audit_Log_Data.Key1
  ORDER BY ALD.Audit_Log_Data_Id DESC
), -1) = 0

-- Go ahead and process the abone result set.
SELECT 
(
  CASE WHEN #tmptable.GuestNumber = 0 THEN
  (
    ISNULL(
    (
      SELECT TOP 1 ALD.New_Value
      FROM Audit_Log_Transactions ALT WITH (NOLOCK)
      INNER JOIN Audit_Log_Data ALD WITH (NOLOCK) ON ALD.Audit_Log_Transaction_Id = ALT.Audit_Log_Transaction_Id
      WHERE ALT.Table_Name = 'ReservationRoomLine'
      AND ALD.Col_Name = 'NumberOfRooms'
      AND ALT.Modified_Date <= #tmptable.Modified_Date
      AND ALD.Key1 = #tmptable.LineNumber
      ORDER BY ALD.Audit_Log_Data_Id DESC
    ), 0)
  )  
  ELSE 
  (
    1 
  ) END
) AS NumberOfRooms,
(
  CASE WHEN #tmptable.GuestNumber = 0 THEN
  (
    ISNULL(
    (
      SELECT TOP 1 CAST(ALD.New_Value AS MONEY)
      FROM Audit_Log_Transactions ALT WITH (NOLOCK)
      INNER JOIN Audit_Log_Data ALD WITH (NOLOCK) ON ALD.Audit_Log_Transaction_Id = ALT.Audit_Log_Transaction_Id
      WHERE ALT.Table_Name = 'ReservationRoomLine'
      AND ALD.Col_Name = 'TotalPrice'
      AND ALT.Modified_Date <= #tmptable.Modified_Date
      AND ALD.Key1 = #tmptable.LineNumber
      ORDER BY ALD.Audit_Log_Data_Id DESC
    ), 0.00)
  )  
  ELSE 
  (
    ISNULL(
    (
      SELECT TOP 1 CAST(ALD.New_Value AS MONEY)
      FROM Audit_Log_Transactions ALT WITH (NOLOCK)
      INNER JOIN Audit_Log_Data ALD WITH (NOLOCK) ON ALD.Audit_Log_Transaction_Id = ALT.Audit_Log_Transaction_Id
      WHERE ALT.Table_Name = 'Reservations'
      AND ALD.Col_Name = 'TotalPrice'
      AND ALT.Modified_Date <= #tmptable.Modified_Date
      AND ALD.Key1 = #tmptable.ReferenceNumber
      AND ALD.Key2 = #tmptable.GuestNumber
      ORDER BY ALD.Audit_Log_Data_Id DESC
    ), 0.00)
  ) END

) AS TotalPrice,
Reservations.ReferenceNumber, Reservations.GuestNumber, Reservations.ArrivalDate,
ReservationAddress.LastName + CASE WHEN ReservationAddress.FirstName = '' THEN '' ELSE ', ' + ReservationAddress.FirstName END AS FullName,
raComp.LastName AS CompanyName, #tmptable.*
FROM #tmptable
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = #tmptable.ReferenceNumber 
  AND Reservations.GuestNumber = #tmptable.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)))
WHERE #tmptable.LineNumber > 0

DROP TABLE #tmptable