CREATE PROCEDURE dbo.YPPO @AHotelCustomerNumber INT, @AFromDate DATETIME, @AToDate DATETIME AS
/*
  Created by Kent Johansen, July 2018
  Get reservation info for YP PO

  Changes:
  KJ 20200709: Use ReservationCreateDate
  KJ 20200313: TotalRoomRevenue and Additional revenue not correct.
  KJ 20190325: Always use create time from main reservation
  KJ 20180903: Sometimes cancel date is present for other status that cancelled
  KJ 20180830: Cancel date time sometimes NULL

*/

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

SELECT TOP 1 @LOTARequestorId = OTARequestor.RequestorID
FROM CustomerOTA WITH (NOLOCK)
INNER JOIN OTARequestor WITH (NOLOCK) ON OTARequestor.RequestorID = CustomerOTA.RequestorID
  AND OTARequestor.Id IN ('Yield Planet', 'YP')
WHERE CustomerNumber = @AHotelCustomerNumber

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, Reservations.ReferenceNumber AS BookingCode, '' AS BookingGroupCode, 
Reservations.ReservationCreateDate AS CreationDateTime,

ISNULL((SELECT TOP 1 Alias FROM OTAAliases WITH (NOLOCK) WHERE FK_RequestorId = @LOTARequestorId AND Kind = 4 AND FK_LookupId = Segments.Number), Segments.Text) AS Segment,
PointOfSales.Text AS Source, 
ISNULL((SELECT TOP 1 Alias FROM OTAAliases WITH (NOLOCK) WHERE FK_RequestorId = @LOTARequestorId AND Kind = 1 AND FK_LookupId = Products.ProductNumber), Products.ProductName) AS RatePlan,
ISNULL((SELECT TOP 1 Alias FROM OTAAliases WITH (NOLOCK) WHERE FK_RequestorId = @LOTARequestorId AND Kind = 0 AND FK_LookupId = RoomTypes.RoomTypeNumber), 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, 

'' AS Channel,

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,

@DefaultCurrencyCode AS Currency,
CAST(0 AS MONEY) AS TotalRoomRevenue,
CAST(0 AS MONEY) AS TotalAdditionalRevenue,
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)
INNER JOIN ReservationRoomlineXtab WITH (NOLOCK) ON ReservationRoomlineXTab.ReferenceNumber = Reservations.ReferenceNumber 
  AND ReservationRoomlineXtab.GuestNumber = Reservations.GuestNumber
INNER JOIN ReservationRoomline WITH (NOLOCK) ON ReservationRoomline.LineNumber=ReservationRoomlineXtab.LineNumber
  AND ((Reservations.GuestNumber > 0 AND Reservations.ArrivalDate BETWEEN @AFromDate AND @AToDate) OR
    (Reservations.GuestNumber = 0 AND ReservationRoomline.ArrivalDate BETWEEN @AFromDate AND @AToDate))
INNER JOIN ReservationMain WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber 
  AND Reservations.ReferenceNumber > 0
INNER JOIN RoomTypes WITH (NOLOCK) ON ReservationRoomLine.BookedTypeNumber = RoomTypes.RoomTypeNumber
INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = RoomTypes.HotelNumber
  AND Customer.CustomerTypeNumber = 7  
  AND ISNULL(Customer.MarkAsDeleted, 0) = 0
  AND Customer.CustomerNumber = @AHotelCustomerNumber
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
) a
WHERE a.num = 1
ORDER BY BookingCode

SELECT @NumberOfDays = MAX(DateDifference)
FROM #tmptable

SET @Counter = 0
WHILE @Counter < @NumberOfDays
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 RoomRevenue' + CAST(@Counter AS VARCHAR(2)) + ' MONEY';
	EXEC (@sql)
	SELECT @sql = 'ALTER TABLE #tmptable ADD AdditionalRevenue' + CAST(@Counter AS VARCHAR(2)) + ' MONEY';
	EXEC (@sql)
END

SELECT * FROM #tmptable

DROP TABLE #tmptable