CREATE PROCEDURE dbo.MiWell @AHotelCustomerNumber INT, @AFromDate DATETIME, @AToDate DATETIME AS
/*
  Created by Kent Johansen, March 2020
  Get reservation info for MiWell

  Changes:
    20201103: Bug: Fix for "The table "#tmptable" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. 
      INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.".
*/

SET NOCOUNT ON
DECLARE @Counter INT
DECLARE @HotelCountryNumber INT
DECLARE @DefaultCurrencyCode CHAR(3)
DECLARE @sql VARCHAR(100)
DECLARE @NumberOfDays INT

SELECT @DefaultCurrencyCode = CurrencyCode 
FROM Countries WITH (NOLOCK) 
WHERE Countrynumber = (Select CONVERT(INT,KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='HotelCurrency')

IF ISNULL(@DefaultCurrencyCode, '') = ''
BEGIN
  SELECT @HotelCountryNumber = CONVERT(INT,KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='CountryCode'
  IF ISNULL(@HotelCountryNumber, 0) > 0
    SELECT TOP 1 @DefaultCurrencyCode = CurrencyCode FROM Countries WITH (NOLOCK) WHERE CountryNumber=@HotelCountryNumber ORDER BY SortNumber
  IF ISNULL(@DefaultCurrencyCode, '') = ''
    SELECT TOP 1 @DefaultCurrencyCode = CurrencyCode FROM Countries WITH (NOLOCK) ORDER BY SortNumber    
END

SELECT *, DATEDIFF(day, ArrivalDate, DepartureDate) AS DateDifference
INTO #tmptable
FROM
(

SELECT 
GETUTCDATE() AS ReportDateUtc, CAST(Reservations.ReferenceNumber AS VARCHAR) AS BookingCode, CAST(Reservations.GuestNumber AS VARCHAR) AS BookingCode2,
Reservations.ReservationCreateDate AS CreationDateTime,
(
  SELECT TOP 1 Link FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 1
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS GuestNo,
(
  SELECT CountryName FROM Countries WITH (NOLOCK) WHERE CountryNumber = ((SELECT TOP 1 CountryNumber FROM ReservationAddress WITH (NOLOCK) WHERE (AddressNumber IN
   (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND AddressType = 1))
) AS GuestCountry,
(
  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 ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND AddressType = 1))
) AS GuestISO2,
(
  SELECT TOP 1 ZipCode FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 1
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS GuestZipCode,
(
  SELECT TOP 1 LastName FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 2
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS CompanyName,
(
  SELECT TOP 1 Link FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 2
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS CompanyNo,
(
  SELECT TOP 1 LastName FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 3
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS AgencyName,
(
  SELECT TOP 1 Link FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 3
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS AgencyNo,
(
  SELECT TOP 1 LastName FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 4
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS AgentName,
(
  SELECT TOP 1 Link FROM ReservationAddress WITH (NOLOCK) WHERE AddressType = 4
    AND AddressNumber IN (SELECT AddressNumber FROM ReservationAddressXTab WITH (NOLOCK) WHERE ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)
) AS AgentNo,
Segments.Text AS Segment,
PointOfSales.Text AS POS,
PurposeCodes.Text AS PurposeCode,
GuestTypes.Text AS GuestType,
RoomTypes.Type AS RoomType,

CASE Reservations.GuestNumber WHEN 0 THEN 
  CASE ISNULL(ReservationRoomLine.ArrivalDate, '') WHEN '' THEN 
    CAST(Reservations.ArrivalDate AS DATETIME) 
  ELSE 
    CAST(ReservationRoomLine.ArrivalDate AS DATETIME) 
  END 
ELSE 
  CAST(Reservations.ArrivalDate AS DATETIME) 
END AS ArrivalDate,

CASE Reservations.GuestNumber WHEN 0 THEN 
  CASE ISNULL(ReservationRoomLine.DepartureDate, '') WHEN '' THEN 
    CAST(Reservations.DepartureDate AS DATETIME) 
  ELSE 
    CAST(ReservationRoomLine.DepartureDate AS DATETIME) 
  END 
ELSE 
  CAST(Reservations.DepartureDate AS DATETIME) 
END AS DepartureDate,

Reservations.Status, 

CASE WHEN Reservations.Status = 7 THEN
  ISNULL(
    (
      SELECT TOP 1 Date 
      FROM ReservationFollowUpXTab rfx WITH (NOLOCK) 
      INNER JOIN FollowUp f WITH (NOLOCK) ON rfx.FollowUpNumber = f.FollowUpNumber 
      WHERE RFX.ReferenceNumber = Reservations.ReferenceNumber 
      AND RFX.GuestNumber = Reservations.GuestNumber 
      AND type = 5 
      AND SubType = 3 
      ORDER BY f.FollowUpNumber
    ),
    (
      SELECT TOP 1 Date 
      FROM ReservationFollowUpXTab rfx WITH (NOLOCK) 
      INNER JOIN FollowUp f WITH (NOLOCK) ON rfx.FollowUpNumber = f.FollowUpNumber 
      WHERE RFX.ReferenceNumber = Reservations.ReferenceNumber 
      AND RFX.GuestNumber = 0
      AND type = 5 
      AND SubType = 3 
      ORDER BY f.FollowUpNumber
    )
  )  
ELSE  
  NULL
END AS CancellationDateTime,

CASE WHEN (ROW_NUMBER() OVER (PARTITION BY ReservationRoomLineXTAB.LineNumber ORDER BY ReservationRoomLineXTAB.LineNumber) > 1)
  OR (((SELECT COUNT(*) FROM dbo.ReservationRoomLineXTab WITH (NOLOCK) WHERE LineNumber = ReservationRoomLine.LineNumber) > 1) AND (Reservations.ArrivalDate <> ReservationRoomLine.ArrivalDate)) OR (CASE WHEN Reservations.GuestNumber = 0 THEN Reservations.Adults + Reservations.Children1 + Reservations.Children2 ELSE ReservationRoomline.Adults + ReservationRoomline.Children1 + ReservationRoomline.Children2 END = 0) THEN 0 ELSE ReservationRoomLine.NumberOfRooms 
END AS NumberOfRooms,
ReservationRoomLine.Adults + ReservationRoomLine.Children1 + ReservationRoomLine.Children2 AS Pax,
ReservationRoomLine.LineNumber,

@DefaultCurrencyCode AS Currency,
CAST(0 AS MONEY) AS TotalRevenueRoom,
CAST(0 AS MONEY) AS TotalRevenueDaymeeting,
CAST(0 AS MONEY) AS TotalRevenueHalls,
CAST(0 AS MONEY) AS TotalRevenueHallsInclPackage,
CAST(0 AS MONEY) AS TotalRevenueRestaurant,
CAST(0 AS MONEY) AS TotalRevenueOther,

ROW_NUMBER() OVER (PARTITION BY Reservations.ReferenceNumber, Reservations.GuestNumber, ReservationRoomline.LineNumber ORDER BY Reservations.ReferenceNumber, Reservations.GuestNumber) as num,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.Day2
ELSE
  Reservations.Day2
END AS Day2,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.Day3
ELSE
  Reservations.Day3
END AS Day3,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.Day4
ELSE
  Reservations.Day4
END AS Day4,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.ProductNumber
ELSE
  Reservations.ProductNumber
END AS ProductNumber,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.ProductNumber2
ELSE
  Reservations.ProductNumber2
END AS ProductNumber2,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.ProductNumber3
ELSE
  Reservations.ProductNumber3
END AS ProductNumber3,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.ProductNumber4
ELSE
  Reservations.ProductNumber4
END AS ProductNumber4,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.PriceOverwritten
ELSE
  Reservations.PriceOverwritten
END AS PriceOverwritten,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.PriceOverwritten2
ELSE
  Reservations.PriceOverwritten2
END AS PriceOverwritten2,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.PriceOverwritten3
ELSE
  Reservations.PriceOverwritten3
END AS PriceOverwritten3,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.PriceOverwritten4
ELSE
  Reservations.PriceOverwritten4
END AS PriceOverwritten4,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.Adults
ELSE
  Reservations.Adults
END AS Adults,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.Children1
ELSE
  Reservations.Children1
END AS Children1,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.Children2
ELSE
  Reservations.Children2
END AS Children2,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.Price
ELSE
  Reservations.Price
END AS Price,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.FirstPrice2
ELSE
  Reservations.FirstPrice2
END AS FirstPrice2,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.FirstPrice3
ELSE
  Reservations.FirstPrice3
END AS FirstPrice3,
CASE WHEN Reservations.GuestNumber = 0 THEN 
  ReservationRoomLine.FirstPrice4
ELSE
  Reservations.FirstPrice4
END AS FirstPrice4,
Reservations.GuestNumber,
ReservationRoomLine.BookedTypeNumber
FROM Reservations WITH (NOLOCK)
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
INNER JOIN ReservationMain WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber 
LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON ReservationRoomLine.BookedTypeNumber = RoomTypes.RoomTypeNumber
LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = 
  CASE WHEN ISNULL(RoomTypes.HotelNumber, 0) > 0 THEN RoomTypes.HotelNumber ELSE ReservationMain.HotelNumber END
LEFT OUTER JOIN PurposeCodes WITH (NOLOCK) ON Reservations.PurposeCode = PurposeCodes.PurposeCodeNumber
LEFT OUTER JOIN GuestTypes WITH (NOLOCK) ON Reservations.GuestType = GuestTypes.TypeNumber
LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON Reservations.PointOfSale = PointOfSales.Number
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Reservations.MARKETSEGMENT = Segments.Number
LEFT OUTER JOIN Products WITH (NOLOCK) ON CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomline.ProductNumber ELSE Reservations.ProductNumber END = Products.ProductNumber
WHERE CASE Reservations.GuestNumber WHEN 0 THEN 
  CASE ISNULL(ReservationRoomLine.ArrivalDate, '') WHEN '' THEN 
    CAST(Reservations.ArrivalDate AS DATETIME) 
  ELSE 
    CAST(ReservationRoomLine.ArrivalDate AS DATETIME) 
  END 
ELSE 
  CAST(Reservations.ArrivalDate AS DATETIME) 
END BETWEEN @AFromDate AND @AToDate
AND Reservations.ReferenceNumber > 0
AND Customer.CustomerTypeNumber = 7  
AND ISNULL(Customer.MarkAsDeleted, 0) = 0
AND Customer.CustomerNumber = @AHotelCustomerNumber
) a
WHERE a.num = 1
ORDER BY BookingCode

SELECT @NumberOfDays = MAX(DateDifference) + 1
FROM #tmptable

SET @Counter = 0
WHILE @Counter < @NumberOfDays AND @NumberOfDays <= 95
BEGIN 
	SET @Counter = @Counter + 1;

	SELECT @sql = 'ALTER TABLE #tmptable ADD StayDate' + CAST(@Counter AS VARCHAR(2)) + ' DATETIME';
	EXEC (@sql)
	SELECT @sql = 'ALTER TABLE #tmptable ADD RateCode' + CAST(@Counter AS VARCHAR(2)) + ' VARCHAR(100)';
	EXEC (@sql)
	SELECT @sql = 'ALTER TABLE #tmptable ADD RevenueRoom' + CAST(@Counter AS VARCHAR(2)) + ' MONEY';
	EXEC (@sql)
	SELECT @sql = 'ALTER TABLE #tmptable ADD RevenueDaymeeting' + CAST(@Counter AS VARCHAR(2)) + ' MONEY';
	EXEC (@sql)
	SELECT @sql = 'ALTER TABLE #tmptable ADD RevenueHalls' + CAST(@Counter AS VARCHAR(2)) + ' MONEY';
	EXEC (@sql)
	SELECT @sql = 'ALTER TABLE #tmptable ADD RevenueHallsInclPackage' + CAST(@Counter AS VARCHAR(2)) + ' MONEY';
	EXEC (@sql)
	SELECT @sql = 'ALTER TABLE #tmptable ADD RevenueRestaurant' + CAST(@Counter AS VARCHAR(2)) + ' MONEY';
	EXEC (@sql)
	SELECT @sql = 'ALTER TABLE #tmptable ADD RevenueOther' + CAST(@Counter AS VARCHAR(2)) + ' MONEY';
	EXEC (@sql)
END

SELECT * FROM #tmptable ORDER BY BookingCode

DROP TABLE #tmptable