CREATE PROCEDURE DBO.[GetRooms] @ReferenceNumber INT, @GuestNumber INT AS
/*
  nb20191028 CR172746 Changed order by as requested by Villy
  nb20090320 +product reset arrivalprice which is also used to indicate price overwritten
  nb20050628: Created
  NOTE: Get the room info
*/
SET NOCOUNT ON
DECLARE @DebugLevel TINYINT, @tPre DATETIME, @tPost DATETIME
SET @DebugLevel = 0
IF @DebugLevel > 0
BEGIN
  SET @tPre = GETDATE();
  PRINT 'Call SP [GetRooms] begin --------------------------------'
END

CREATE TABLE #PriceRooms(LineNumber INT NOT NULL
, PriceTotal MONEY NOT NULL
, TaxTotal MONEY NOT NULL
, PriceArrival MONEY NOT NULL
, TaxArrival MONEY NOT NULL
)
EXEC dbo.GetContentRoomLine @ReferenceNumber, @GuestNumber, 0, NULL

SELECT ROW_NUMBER() OVER(ORDER BY RRL.ArrivalDate, RRL.DepartureDate, RRL.BookedTypeNumber, RRL.NumberOfRooms, RRL.Adults, RRL.Children1, RRL.Children2, RRL.Price, RRL.TotalPrice) AS RowNumber
, BookedTypeNumber, Convert(DateTime, ArrivalDate) AS ArrivalDate, Convert(DateTime, DepartureDate) AS DepartureDate
, Adults, Children1, Children2 
, RRL.Price
, SUM(RRL.NumberOfRooms) AS NumberOfRooms
, SUM(PriceTotal) AS TotalPrice
, TaxArrival AS VatArrival
, SUM(TaxTotal) AS VatTotal
FROM dbo.ReservationRoomline RRL WITH (NOLOCK) INNER JOIN 
  #PriceRooms PR WITH (NOLOCK) ON PR.LineNumber = RRL.LineNumber
WHERE RRL.LineNumber  IN (
  SELECT LineNumber
  FROM dbo.Reservations R WITH (NOLOCK) INNER JOIN
    dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON R.ReferenceNumber= RLX.ReferenceNumber AND R.GuestNumber= RLX.GuestNumber AND R.Status NOT IN (7,8)
  WHERE RLX.ReferenceNumber=@ReferenceNumber
) 
GROUP BY RRL.ArrivalDate, RRL.DepartureDate, RRL.BookedTypeNumber, RRL.NumberOfRooms, RRL.Adults, RRL.Children1, RRL.Children2, RRL.Price, RRL.TotalPrice, TaxArrival, TaxTotal
ORDER BY RRL.ArrivalDate, RRL.DepartureDate, RRL.BookedTypeNumber, RRL.NumberOfRooms, RRL.Adults, RRL.Children1, RRL.Children2, RRL.Price, RRL.TotalPrice
DROP TABLE #PriceRooms
IF @DebugLevel > 0 
BEGIN
  SET @tPost = GETDATE();
  PRINT 'Time SP [GetRooms]: ' + Convert(VARCHAR, DATEDIFF(millisecond, @tPre, @tPost))
END
