CREATE PROCEDURE dbo.HorestaOTA @AFromDate DATETIME, @AToDate DATETIME, @AHotelCustomerNumber INT AS
/*
  Created by: Kent Johansen, March 2025
  Select data to Horesta OTA output
*/
SELECT ROW_NUMBER() OVER(ORDER BY MISTableViewWithTime.ReferenceNumber) AS PMSProductLineId, 
CAST(MISTableViewWithTime.ReferenceNumber AS VARCHAR) + '_' + CAST((DATEDIFF(DAY, CONVERT(DATETIME, Reservations.ArrivalDate, 120), CONVERT(DATETIME, MISTableViewWithTime.DateStamp, 120)) + 1) AS VARCHAR) AS PMSCombinedReservationId,
CONVERT(DATETIME, MISTableViewWithTime.DateStamp, 120) AS DateStamp,
Departments.Name AS DepartmentName,
(SELECT ISO_3166_A2 FROM Countries WITH (NOLOCK) WHERE CountryNumber = ((SELECT TOP 1 CountryNumber FROM ReservationAddress WITH (NOLOCK) WHERE (AddressNumber IN
  (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReservationAddressXTab.ReferenceNumber = MISTableViewWithTime.ReferenceNumber AND ReservationAddressXTab.GuestNumber = MISTableViewWithTime.GuestNumber)) AND AddressType = 1))) AS ISO2,
CASE MISTableViewWithTime.ValueType 
  WHEN 1 THEN 'Overnight' 
  WHEN 2 THEN 'Hall'
  WHEN 3 THEN 'Daymeeting'
  WHEN 4 THEN 'Restaurant'
  WHEN 5 THEN 'Other'
END AS BusinessCategory,
WareGroups.Text AS WareGroupName, Ware.WareName AS WareName, 
SUM(CASE WHEN ISNULL(BookedWareCount, 0) + ISNULL(ActualWareCount, 0) <> 0 THEN 
  ISNULL(BookedWareCount, 0) + ISNULL(ActualWareCount, 0)
ELSE
  ISNULL(BookedNumberOfRooms, 0) + ISNULL(ActualNumberOfRooms, 0)
END) AS Pieces,
SUM(ActualPriceInclPackage + BookedPriceInclPackage) AS Amount,
ISNULL(Reservations.ReservationCreateDate, CONVERT(DATETIME, MISTableViewWithTime.DateStamp, 120)) AS ReservationCreateDate,
ISNULL(
(
  SELECT TOP 1 Date
  FROM ReservationFollowUpXTab WITH (NOLOCK)
  INNER JOIN FollowUp WITH (NOLOCK) ON FollowUp.FollowUpNumber = ReservationFollowUpXTab.FollowUpNumber
  WHERE ReservationFollowUpXTab.ReferenceNumber = MISTableViewWithTime.ReferenceNumber
  AND ReservationFollowUpXTab.GuestNumber = MISTableViewWithTime.GuestNumber
  AND Type = 5
  ORDER BY FollowUp.FollowUpNumber
), (CONVERT(DATETIME, MISTableViewWithTime.DateStamp, 120))) AS FollowupModifyDate

FROM MISTableViewWithTime
LEFT OUTER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTableViewWithTime.ReferenceNumber
  AND Reservations.GuestNumber = MISTableViewWithTime.GuestNumber
LEFT OUTER JOIN ReservationFollowUpXTab WITH (NOLOCK) ON ReservationFollowUpXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationFollowUpXTab.GuestNumber = Reservations.GuestNumber
LEFT OUTER JOIN Followup WITH (NOLOCK) ON FollowUp.FollowUpNumber = ReservationFollowUpXTab.FollowUpNumber
LEFT OUTER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = MISTableViewWithTime.DepartmentNumber
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableViewWithTime.HotelCustomerNumber
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = MISTableViewWithTime.WareNumber
INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
WHERE MISTableViewWithTime.DateStamp >= @AFromDate
AND (
  (
    (ISNULL(MISTableViewWithTime.ReferenceNumber, 0) > 0 AND Followup.Date BETWEEN @AFromDate AND @AToDate) OR 
    (ISNULL(MISTableViewWithTime.ReferenceNumber, 0) <= 0 AND MISTableViewWithTime.DateStamp BETWEEN @AFromDate AND @AToDate)
  )
)
AND MISTableViewWithTime.HotelCustomerNumber = @AHotelCustomerNumber
GROUP BY MISTableViewWithTime.ReferenceNumber, MISTableViewWithTime.GuestNumber, Reservations.ArrivalDate, MISTableViewWithTime.DateStamp, Departments.Name,
  MISTableViewWithTime.ValueType, WareGroups.Text, Ware.WareName, Reservations.ReservationCreateDate