CREATE PROCEDURE dbo.MonaLongStayGetReservations @AHotelCustomerNumber INT AS
/*
  Created by Kent Johansen, June 2020

  Changes:
    KJ 20210528: Bug: Fix for "All reservation types is added to list". Case #CR210856
    KJ 20210125: Bug: Fix for "Wrong period end for prebill and using recurring pattern".
    KJ 20210121: Bug: Fix for "Wrong Reservations selected if prebill"
    KJ 20210118: Bug: Fix for "Wrong Reservations selected if not prebill"

*/

DECLARE @SystemDateStr CHAR(8)
DECLARE @SystemDate DATETIME
DECLARE @MONALongStayResType VARCHAR(100)

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

SELECT @MONALongStayResType = dbo.GetHotelSetupKeyValue('MonaLongStayResType', @AHotelCustomerNumber)
SET @MONALongStayResType = ISNULL(@MONALongStayResType, '')

SELECT InvoiceAutoSetting.PreBill, Reservations.ReferenceNumber, Reservations.GuestNumber,
CASE WHEN InvoiceAutoSetting.PreBill = 1 THEN
  CONVERT(DATETIME, Reservations.ArrivalDate, 112) + 
   ((DATEDIFF(DAY, CONVERT(DATETIME, Reservations.ArrivalDate, 112), @SystemDate) / InvoiceAutoSetting.ArrivalCompDays) * InvoiceAutoSetting.ArrivalCompDays) 
ELSE
  CONVERT(DATETIME, Reservations.ArrivalDate, 112) + 
   ((DATEDIFF(DAY, CONVERT(DATETIME, Reservations.ArrivalDate, 112), @SystemDate) / InvoiceAutoSetting.ArrivalCompDays) * InvoiceAutoSetting.ArrivalCompDays) - InvoiceAutoSetting.ArrivalCompDays 
END AS PeriodStartDate,

CASE WHEN InvoiceAutoSetting.PreBill = 1 THEN
  CONVERT(DATETIME, Reservations.ArrivalDate, 112) + 
   ((DATEDIFF(DAY, CONVERT(DATETIME, Reservations.ArrivalDate, 112), @SystemDate) / InvoiceAutoSetting.ArrivalCompDays) * InvoiceAutoSetting.ArrivalCompDays) + InvoiceAutoSetting.ArrivalCompDays - 1
ELSE
  CONVERT(DATETIME, Reservations.ArrivalDate, 112) + 
   ((DATEDIFF(DAY, CONVERT(DATETIME, Reservations.ArrivalDate, 112), @SystemDate) / InvoiceAutoSetting.ArrivalCompDays) * InvoiceAutoSetting.ArrivalCompDays)
END AS PeriodEndDate
INTO #tmp
FROM Reservations WITH (NOLOCK)
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
LEFT OUTER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
LEFT OUTER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = RoomTypes.HotelNumber 
INNER JOIN InvoiceAutoSetting WITH (NOLOCK) ON InvoiceAutoSetting.Id = Reservations.FK_InvoiceAutoSettingId
WHERE InvoiceAutoSetting.InvoiceType = 0 -- Compare to Arrival
AND ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM dbo.iter_intlist_to_table(@MONALongStayResType))
AND ISNULL(Customer.CustomerNumber, ReservationMain.FkCustomerHotel) = @AHotelCustomerNumber
AND Reservations.Status = 5
AND (
 (
   InvoiceAutoSetting.PreBill = 1 AND CONVERT(DATETIME, Reservations.ArrivalDate, 112) + 
     ((DATEDIFF(DAY, CONVERT(DATETIME, Reservations.ArrivalDate, 112), @SystemDate) / InvoiceAutoSetting.ArrivalCompDays) * InvoiceAutoSetting.ArrivalCompDays) - 
      InvoiceAutoSetting.NotificationDays = @SystemDate
 ) OR
 (
   InvoiceAutoSetting.PreBill = 0 AND CONVERT(DATETIME, Reservations.ArrivalDate, 112) + 
     ((DATEDIFF(DAY, CONVERT(DATETIME, Reservations.ArrivalDate, 112), @SystemDate) / InvoiceAutoSetting.ArrivalCompDays) * InvoiceAutoSetting.ArrivalCompDays) = @SystemDate
 )
)

UNION

SELECT InvoiceAutoSetting.PreBill, Reservations.ReferenceNumber, Reservations.GuestNumber, 
CASE WHEN InvoiceAutoSetting.PreBill = 1 THEN
  RecurringPatternSpecified.StartDateTime
ELSE
  (
    SELECT TOP 1 rps.StartDateTime
    FROM RecurringPatternSpecified rps WITH (NOLOCK)
    WHERE rps.FK_RecurringPatternId = RecurringPatternSpecified.FK_RecurringPatternId
    AND rps.StartDateTime < RecurringPatternSpecified.StartDateTime
    ORDER BY rps.StartDateTime  
  )
END AS PeriodStartDate,

CASE WHEN InvoiceAutoSetting.PreBill = 1 THEN
  (
    SELECT TOP 1 rps.StartDateTime - 1
    FROM RecurringPatternSpecified rps WITH (NOLOCK)
    WHERE rps.FK_RecurringPatternId = RecurringPatternSpecified.FK_RecurringPatternId
    AND rps.StartDateTime > RecurringPatternSpecified.StartDateTime
    ORDER BY rps.StartDateTime  
  )
ELSE
  RecurringPatternSpecified.StartDateTime
END AS PeriodEndDate

FROM Reservations WITH (NOLOCK)
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
LEFT OUTER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
LEFT OUTER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = RoomTypes.HotelNumber 
INNER JOIN InvoiceAutoSetting WITH (NOLOCK) ON InvoiceAutoSetting.Id = Reservations.FK_InvoiceAutoSettingId
INNER JOIN RecurringPatternSpecified WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternId = InvoiceAutoSetting.FK_RecurringPattern
WHERE InvoiceAutoSetting.InvoiceType = 1 -- Pattern
AND ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM dbo.iter_intlist_to_table(@MONALongStayResType))
AND ISNULL(Customer.CustomerNumber, ReservationMain.FkCustomerHotel) = @AHotelCustomerNumber
AND Reservations.Status = 5
AND (
 (
   InvoiceAutoSetting.PreBill = 1 AND RecurringPatternSpecified.StartDateTime - InvoiceAutoSetting.NotificationDays = @SystemDate
 ) OR
 (
   InvoiceAutoSetting.PreBill = 0 AND RecurringPatternSpecified.StartDateTime = @SystemDate
 )
)

SELECT InvoiceAutoSetting.PreBill, InvoiceAutoSetting.SendSMS,
Reservations.ArrivalDate,
CASE WHEN InvoiceAutoSetting.PreBill = 1 THEN
  #tmp.PeriodStartDate - InvoiceAutoSetting.NotificationDays 
ELSE
  #tmp.PeriodEndDate
END AS NotificationDate,
#tmp.PeriodStartDate,
#tmp.PeriodEndDate,
Reservations.ReferenceNumber, Reservations.GuestNumber
FROM Reservations WITH (NOLOCK)
INNER JOIN #tmp ON #tmp.ReferenceNumber = Reservations.ReferenceNumber
  AND #tmp.GuestNumber = Reservations.GuestNumber
INNER JOIN InvoiceAutoSetting WITH (NOLOCK) ON InvoiceAutoSetting.Id = Reservations.FK_InvoiceAutoSettingId

DROP TABLE #tmp