CREATE PROCEDURE dbo.MonaConferenceLostCustomerBusiness @AHotelCustomerNumber INT, @ALostCustomer BIT AS
SET NOCOUNT ON
/*
  Created by: Kent Johansen, july 2023
  Changes
*/

DECLARE @LReservationType INT
DECLARE @LDaysStr VARCHAR(100)
DECLARE @LPaxStr VARCHAR(100)
DECLARE @LAddressTypeStr VARCHAR(100)
DECLARE @LDays INT
DECLARE @LPax INT
DECLARE @LFromStatus INT
DECLARE @LToStatus INT
DECLARE @LAddressType INT
DECLARE @LFromDate DATETIME
DECLARE @LToDate DATETIME
DECLARE @SystemDateStr CHAR(8)
DECLARE @SystemDate DATETIME

SELECT @SystemDateStr = SystemDate FROM System WITH (NOLOCK)
SET @SystemDate = CONVERT(DATETIME, @SystemDateStr)

IF @ALostCustomer = 1
BEGIN
  SELECT @LReservationType = dbo.GetHotelSetupKeyValue('MonaConferenceLostCustomerReservationTypes', @AHotelCustomerNumber)
  SELECT @LDaysStr = dbo.GetHotelSetupKeyValue('MonaConferenceLostCustomerDays', @AHotelCustomerNumber)
  SELECT @LPaxStr = dbo.GetHotelSetupKeyValue('MonaConferenceLostCustomerPax', @AHotelCustomerNumber)
  SELECT @LAddressTypeStr = dbo.GetHotelSetupKeyValue('MonaConferenceLostCustomerGuestType', @AHotelCustomerNumber)
  SELECT @LFromStatus = dbo.GetHotelSetupKeyValue('MonaConferenceLostCustomerFromStatus', @AHotelCustomerNumber)
  SELECT @LToStatus = dbo.GetHotelSetupKeyValue('MonaConferenceLostCustomerToStatus', @AHotelCustomerNumber)
  SET @LFromStatus = ISNULL(@LFromStatus, 0) -- Confirmed
END
ELSE
BEGIN
  SELECT @LReservationType = dbo.GetHotelSetupKeyValue('MonaConferenceLostBusinessReservationTypes', @AHotelCustomerNumber)
  SELECT @LDaysStr = dbo.GetHotelSetupKeyValue('MonaConferenceLostBusinessDays', @AHotelCustomerNumber)
  SELECT @LPaxStr = dbo.GetHotelSetupKeyValue('MonaConferenceLostBusinessPax', @AHotelCustomerNumber)
  SELECT @LAddressTypeStr = dbo.GetHotelSetupKeyValue('MonaConferenceLostBusinessGuestType', @AHotelCustomerNumber)
  SELECT @LFromStatus = dbo.GetHotelSetupKeyValue('MonaConferenceLostBusinessFromStatus', @AHotelCustomerNumber)
  SELECT @LToStatus = dbo.GetHotelSetupKeyValue('MonaConferenceLostBusinessToStatus', @AHotelCustomerNumber)
  SET @LFromStatus = ISNULL(@LFromStatus, 4) -- Tentative
END

SET @LDays = ISNULL(@LDaysStr, 0)
SET @LPax = ISNULL(@LPaxStr, 0)
SET @LAddressType = ISNULL(@LAddressTypeStr, 2) -- atCompany
SET @LToStatus = ISNULL(@LToStatus, 7) -- Cancelled

SET @LFromDate = CAST(DATEADD(DAY, -@LDays, @SystemDate) AS DATETIME) 
SET @LToDate = CAST(CONVERT(CHAR(8), @LFromDate, 112) + ' 23:59:59.99' AS DATETIME)
/*
SET @LToDate = CAST(CONVERT(CHAR(8), DATEADD(DAY, 100, @LFromDate), 112) + ' 23:59:59.99' AS DATETIME)

PRINT @LReservationType
PRINT @LDays
PRINT @LPax
PRINT @LFromDate
PRINT @LToDate
PRINT @LFromStatus
PRINT @LToStatus
*/

SELECT DISTINCT Reservations.ReferenceNumber, Reservations.GuestNumber, @LAddressType AS AddressType, ReservationAddress.Email, ReservationAddress.Link
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
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = Audit_Log_Data.Key1
  AND Reservations.GuestNumber = Audit_Log_Data.Key2
INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationConferenceLine.GuestNo = Reservations.GuestNumber
  AND ReservationConferenceLine.Plenum = 1
  AND ISNULL(ReservationConferenceLine.Adults, 0) + ISNULL(ReservationConferenceLine.Children1, 0) + ISNULL(ReservationConferenceLine.Children2, 0) >= @LPax
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
	AND (ISNULL(@LReservationType, 0) = 0  OR (POWER(2, ReservationMain.ReservationType) & @LReservationType > 0))
  AND ReservationMain.FkCustomerHotel = @AHotelCustomerNumber
INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
  AND ReservationAddressXTab.AddressTypeNumber = @LAddressType
INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
WHERE Audit_Log_Transactions.Table_Name = 'Reservations' 
AND Audit_Log_Data.Col_Name = 'Status'
AND Audit_Log_Transactions.Modified_Date BETWEEN @LFromDate AND @LToDate
AND Audit_Log_Transactions.Audit_Action_Id = 1 -- Only updates
AND (ISNULL(@LFromStatus, 0) = 0  OR (POWER(2, CAST(Audit_Log_Data.Old_Value AS INT)) & @LFromStatus > 0))
AND (ISNULL(@LToStatus, 0) = 0  OR (POWER(2, CAST(Audit_Log_Data.New_Value AS INT)) & @LToStatus > 0))