CREATE PROCEDURE dbo.MonaConferenceReminderFollowup @AHotelCustomerNumber INT, @AReminder BIT AS
SET NOCOUNT ON
/*
  Created by: Kent Johansen, july 2023
  Reminder: Reservation Create x-days ago
  Followup: Reservation departure x-days ago
  Changes
*/

DECLARE @LReservationType INT
DECLARE @LDaysStr VARCHAR(100)
DECLARE @LPaxStr VARCHAR(100)
DECLARE @LAddressTypeStr VARCHAR(100)
DECLARE @LDays INT
DECLARE @LPax 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 @AReminder = 1
BEGIN
  SELECT @LReservationType = dbo.GetHotelSetupKeyValue('MonaConferenceReminderReservationTypes', @AHotelCustomerNumber)
  SELECT @LDaysStr = dbo.GetHotelSetupKeyValue('MonaConferenceReminderDays', @AHotelCustomerNumber)
  SELECT @LPaxStr = dbo.GetHotelSetupKeyValue('MonaConferenceReminderPax', @AHotelCustomerNumber)
  SELECT @LAddressTypeStr = dbo.GetHotelSetupKeyValue('MonaConferenceReminderGuestType', @AHotelCustomerNumber)
END
ELSE
BEGIN
  SELECT @LReservationType = dbo.GetHotelSetupKeyValue('MonaConferenceFollowupReservationTypes', @AHotelCustomerNumber)
  SELECT @LDaysStr = dbo.GetHotelSetupKeyValue('MonaConferenceFollowupDays', @AHotelCustomerNumber)
  SELECT @LPaxStr = dbo.GetHotelSetupKeyValue('MonaConferenceFollowupPax', @AHotelCustomerNumber)
  SELECT @LAddressTypeStr = dbo.GetHotelSetupKeyValue('MonaConferenceFollowupGuestType', @AHotelCustomerNumber)
END

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

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, 10, @LFromDate), 112) + ' 23:59:59.99' AS DATETIME)

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

SELECT DISTINCT Reservations.ReferenceNumber, Reservations.GuestNumber, @LAddressType AS AddressType, ReservationAddress.Email, ReservationAddress.Link
FROM Reservations WITH (NOLOCK)
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
  AND ((@AReminder = 1 AND Reservations.ReservationCreateDate BETWEEN @LFromDate AND @LToDate) OR (@AReminder = 0 AND ReservationConferenceLine.DepartureDate = CONVERT(CHAR(8), @LFromDate, 112)))
  AND ((@AReminder = 1 AND Reservations.Status < 7) OR (@AReminder = 0 AND Reservations.Status = 6))
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