CREATE PROCEDURE [dbo].[OnlineOwnerReportReservations] @AStartDateTime DateTime, @AEndDateTime DateTime, @AReservationStatusStr VARCHAR(25), @ARoomNumberStr VARCHAR(2500), @AHotelNumber INT = -1 AS
SET NOCOUNT ON

/*
Created by Kent Johansen on 4. august 2006
Returns reservations for owners. Used on owner report.

Changes:
  KJ 20210107: Bug: Fix for "Owner percent is calculated as integer - should be float". Case #CR203639
  KJ 20160629: Ext: Added Owner Product description.
  KJ 20070830: Bug: Fix for "Date problem"
  KJ 20070620: Added: Show only reservations with payadults.
  KJ 20070530: Added HotelNumber as input param
  KJ 20070308: Bug: Fix for set Day2+Day3+Day4 to 0 if NULL not -1
  KJ 20070131: Added product name and product type to output
  KJ 20070121: Minor optimization
  KJ 20061211: Pax/Adults/Child1/Child2 wrong.
  KJ 20061030: Added ReservationStatus
  KJ 20060911: Removed   AND Reservations.PaysAdults > 0 and Departure date changed to ArrivalDate
  KJ 20060907: Added GuestName
  KJ 20060907: Added package price
  KJ 20060904: Added "And ReferenceNumber > 0"
  KJ 20060903: Fix for "Wrong join for rooms"
  KJ 20060901: Added something

*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

DECLARE @NewDepartureDate VARCHAR(8)
SET @NewDepartureDate = CONVERT(VARCHAR(8), @AEndDateTime + 1, 112)

DECLARE @HotelSetupOwnerPercent INT
SELECT @HotelSetupOwnerPercent = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'LoginSplit'

DECLARE @ReferenceNumber INT
DECLARE @GuestName VARCHAR(100)
DECLARE @GuestNumber INT
DECLARE @HotelNumber INT
DECLARE @RoomNumber INT
DECLARE @ArrivalDate DATETIME
DECLARE @DepartureDate DATETIME
DECLARE @Adults INT
DECLARE @Children1 INT
DECLARE @Children2 INT
DECLARE @Price MONEY
DECLARE @TotalPrice MONEY
DECLARE @ProductNumber INT
DECLARE @ProductNumber2 INT
DECLARE @ProductNumber3 INT
DECLARE @ProductNumber4 INT
DECLARE @Day2 INT
DECLARE @Day3 INT
DECLARE @Day4 INT
DECLARE @PriceOverwritten BIT
DECLARE @PriceOverwritten2 BIT
DECLARE @PriceOverwritten3 BIT
DECLARE @PriceOverwritten4 BIT
DECLARE @FirstPrice1 MONEY
DECLARE @FirstPrice2 MONEY
DECLARE @FirstPrice3 MONEY
DECLARE @FirstPrice4 MONEY
DECLARE @BookedTypeNumber INT
DECLARE @AllocatedTypeNumber INT
DECLARE @NoOfRooms INT
DECLARE @Rooms INT
DECLARE @osType INTEGER
DECLARE @osPercent FLOAT

DECLARE @Pax INT
DECLARE @TmpLineNumber INT
DECLARE @LineNumber INT

DECLARE @ResArrivalDateStr CHAR(8)
DECLARE @ResDepartureDateStr CHAR(8)
DECLARE @PeriodArrivalDateStr CHAR(8)
DECLARE @PeriodDepartureDateStr CHAR(8)
DECLARE @ReservationTotalPrice MONEY
DECLARE @sql NVARCHAR(1000)

DECLARE @CleaningPrice MONEY
DECLARE @NettoPrice MONEY
DECLARE @ResultPackagePrice MONEY

DECLARE @Status INT
DECLARE @ProductName VARCHAR(50)
DECLARE @ProductType VARCHAR(50)

DECLARE @OwnerProductDescription1 VARCHAR(128)
DECLARE @OwnerProductDescription2 VARCHAR(128)
DECLARE @OwnerProductDescription3 VARCHAR(128)
DECLARE @OwnerProductDescription4 VARCHAR(128)

CREATE TABLE #Reservations (
  Referencenumber INT,
  GuestName VARCHAR(100),
  GuestNumber INT,
  HotelNumber INT NULL,
  ReservationArrival DATETIME,
  ReservationDeparture DATETIME,
  Pax INT NULL,
  Adults INT NULL,
  Children1 INT NULL,
  Children2 INT NULL,
  TotalPrice MONEY NULL,
  CleaningPrice MONEY NULL,
  PackagePrice MONEY NULL,
  OwnerPercent FLOAT NULL,
  NettoPrice MONEY NULL,
  RoomNumber INT NULL,
  ReservationStatus INT NULL,
  ProductName VARCHAR(50),
  ProductTypeName VARCHAR(50),
  OwnerProductDescription1 VARCHAR(128),
  OwnerProductDescription2 VARCHAR(128),
  OwnerProductDescription3 VARCHAR(128),
  OwnerProductDescription4 VARCHAR(128)
)

DECLARE db_cur CURSOR FOR
SELECT
  Reservations.ReferenceNumber,
  CASE WHEN ReservationAddress.FirstName = '' THEN ReservationAddress.LastName ELSE ReservationAddress.FirstName + ' ' + ReservationAddress.LastName END,
  Reservations.GuestNumber, Rooms.HotelNumber, Rooms.Number,
  CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.ArrivalDate ELSE Reservations.ArrivalDate END AS ArrivalDate,
  CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.DepartureDate ELSE Reservations.DepartureDate END AS DepartureDate,
  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.TotalPrice ELSE Reservations.TotalPrice END AS TotalPrice,
  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.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.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.FirstPrice1 ELSE Reservations.FirstPrice1 END AS FirstPrice1,
  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,
  ReservationRoomLine.BookedTypeNumber,
  Rooms.RoomTypeNumber,
  ISNULL(ReservationRoomline.NumberOfRooms,0),
  (CASE WHEN Reservations.GuestNumber = 0 THEN
    CONVERT(INT, CONVERT(DATETIME, CASE WHEN ReservationRoomline.DepartureDate > @DepartureDate THEN @NewDepartureDate ELSE ReservationRoomline.DepartureDate END, 112) - CONVERT(DATETIME, CASE WHEN ReservationRoomline.ArrivalDate <= @ArrivalDate THEN @ArrivalDate ELSE ReservationRoomline.ArrivalDate END, 112))
  ELSE
    CONVERT(INT, CONVERT(DATETIME, CASE WHEN Reservations.DepartureDate > @DepartureDate THEN @NewDepartureDate ELSE Reservations.DepartureDate END, 112) - CONVERT(DATETIME, CASE WHEN Reservations.ArrivalDate <= @ArrivalDate THEN @ArrivalDate ELSE Reservations.ArrivalDate END, 112))
  END)* ReservationRoomline.NumberOfRooms,
  Rooms.OwnerSettlementType, Rooms.OwnerSettlementPercent,
  ReservationRoomLineXTab.LineNumber,
  Reservations.Status,
  (CASE WHEN Reservations.GuestNumber = 0 THEN 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = ReservationRoomLine.ProductNumber
	 )
   ELSE 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = Reservations.ProductNumber
	 )
   END  
  ),

  (CASE WHEN Reservations.GuestNumber = 0 THEN 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = ReservationRoomLine.ProductNumber2
	 )
   ELSE 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = Reservations.ProductNumber2
	 )
   END  
  ),

  (CASE WHEN Reservations.GuestNumber = 0 THEN 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = ReservationRoomLine.ProductNumber3
	 )
   ELSE 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = Reservations.ProductNumber3
	 )
   END  
  ),

  (CASE WHEN Reservations.GuestNumber = 0 THEN 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = ReservationRoomLine.ProductNumber4
	 )
   ELSE 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = Reservations.ProductNumber4
	 )
   END  
  )

  FROM Reservations WITH (NOLOCK)
  INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber AND Reservations.GuestNumber = ReservationRoomLineXTab.GuestNumber
  INNER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLineXTab.LineNumber = ReservationRoomLine.LineNumber --AND ReservationRoomLine.ArrivalDate >= @AStartDateTime AND ReservationRoomLine.ArrivalDate <= @AEndDateTime
    AND RESERVATIONROOMLINE.DepartureDate>= @AStartDateTime AND RESERVATIONROOMLINE.ArrivalDate <= @AEndDateTime
  INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber AND ((@ARoomNumberStr = '') OR (Rooms.Number IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberStr)))) AND ((@AHotelNumber = -1) OR (Rooms.HotelNumber = @AHotelNumber))
  INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
  INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber AND AddressType = 1
  WHERE 1=1
  AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusStr))
  AND Reservations.ReferenceNumber > 0
  AND ((Reservations.GuestNumber = 0 AND ReservationRoomLine.Adults+ReservationRoomLine.Children1+ReservationRoomLine.Children2 > 0) OR 
      (Reservations.GuestNumber > 0 AND Reservations.PaysAdults+Reservations.PaysChildren1+Reservations.PaysChildren2 > 0))
  ORDER BY Rooms.Number
OPEN db_cur

FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestName, @GuestNumber, @HotelNumber, @RoomNumber, @ArrivalDate, @DepartureDate, @Adults, @Children1, @Children2, @Price, @TotalPrice, @ProductNumber, @ProductNumber2, @ProductNumber3, @ProductNumber4, @Day2, @Day3, @Day4,
  @PriceOverwritten, @PriceOverwritten2, @PriceOverwritten3, @PriceOverwritten4, @FirstPrice1, @FirstPrice2, @FirstPrice3, @FirstPrice4, @BookedTypeNumber, @AllocatedTypeNumber,
  @NoOfRooms, @Rooms, @osType, @osPercent, @LineNumber, @Status, @OwnerProductDescription1, @OwnerProductDescription2, @OwnerProductDescription3, @OwnerProductDescription4

WHILE @@FETCH_STATUS = 0
BEGIN
  --  SET @Pax = @Pax*@Rooms
  --  SET @Adults = @Adults*@Rooms
  --  SET @Children1 = @Children1*@Rooms
  --  SET @Children2 = @Children2*@Rooms

  IF @TmpLineNumber = @LineNumber
    SET @Rooms = 0
  SET @TmpLineNumber = @LineNumber

  SET @ResArrivalDateStr = CONVERT(CHAR(8), @ArrivalDate, 112)
  SET @ResDepartureDateStr = CONVERT(CHAR(8), @DepartureDate, 112)

  IF @ArrivalDate > @AStartDateTime
    SET @PeriodArrivalDateStr = CONVERT(CHAR(8), @ArrivalDate, 112)
  ELSE
    SET @PeriodArrivalDateStr = CONVERT(CHAR(8), @AStartDateTime, 112)
  IF @DepartureDate-1 < @AEndDateTime
    SET @PeriodDepartureDateStr = CONVERT(CHAR(8), @DepartureDate-1, 112)
  ELSE
    SET @PeriodDepartureDateStr = CONVERT(CHAR(8), @AEndDateTime, 112)

  IF @ShowPrint = 1
  BEGIN
    PRINT '-----------------'
    PRINT @ReferenceNumber
    PRINT @GuestNumber
    PRINT @HotelNumber
    PRINT @ResArrivalDateStr
    PRINT @ResDepartureDateStr
    PRINT @Adults
    PRINT @Children1
    PRINT @Children2
    PRINT @ProductNumber
    PRINT @ProductNumber2
    PRINT @ProductNumber3
    PRINT @ProductNumber4
    PRINT @Day2
    PRINT @Day3
    PRINT @Day4
    PRINT @AllocatedTypeNumber
    PRINT @PeriodArrivalDateStr
    PRINT @PeriodDepartureDateStr
  END

  SET @ProductNumber = ISNULL(@ProductNumber, -1)
  SET @ProductNumber2 = ISNULL(@ProductNumber2, -1)
  SET @ProductNumber3 = ISNULL(@ProductNumber3, -1)
  SET @ProductNumber4 = ISNULL(@ProductNumber4, -1)
  SET @Adults = ISNULL(@Adults, -1)
  SET @Children1 = ISNULL(@Children1, -1)
  SET @Children2 = ISNULL(@Children2, -1)
  SET @Day2 = ISNULL(@Day2, 0)
  SET @Day3 = ISNULL(@Day3, 0)
  SET @Day4 = ISNULL(@Day4, 0)
  SET @HotelNumber = ISNULL(@HotelNumber, -1)

  -- Calc packet price
  SET @sql = N'EXEC CalcPacketPriceTotal ' + '''' + CONVERT(VARCHAR(100), @ArrivalDate) + '''' + ', ' + '''' + CONVERT(VARCHAR(100), @DepartureDate) + '''' + ', ' + CONVERT(VARCHAR(10), @ProductNumber) + ', ' + CONVERT(VARCHAR(10), @ProductNumber2) + ', ' + CONVERT(VARCHAR(10), @ProductNumber3) + ', ' +
    CONVERT(VARCHAR(10), @ProductNumber4) + ', ' + CONVERT(VARCHAR(10), @Day2) + ', ' + CONVERT(VARCHAR(10), @Day3) + ', ' + CONVERT(VARCHAR(10), @Day4) + ', ' + CONVERT(VARCHAR(10), @AllocatedTypeNumber) + ', ' + CONVERT(VARCHAR(10), @HotelNumber) + ', ' + CONVERT(VARCHAR(10), @Adults) + ', ' + CONVERT(VARCHAR(10), @Children1) + ', ' + CONVERT(VARCHAR(10), @Children2) + ', @ResultPackagePrice OUTPUT'

  IF @ShowPrint = 1
    PRINT @sql

  EXEC sp_executesql @sql, N'@ResultPackagePrice MONEY OUTPUT', @ResultPackagePrice OUTPUT

  IF @ShowPrint = 1
  BEGIN
    PRINT '@ResultPackagePrice'
    PRINT CONVERT(VARCHAR(8), @ResultPackagePrice)
  END

  -- Calc Cleaning price
  SET @sql = N'EXEC CalcOwnerCleaningPrice ' + '''' + CONVERT(VARCHAR(100), @ArrivalDate) + '''' + ', ' + '''' + CONVERT(VARCHAR(100), @DepartureDate) + ''''  + ', ' + CONVERT(VARCHAR(10), @ProductNumber) + ', ' + CONVERT(VARCHAR(10), @ProductNumber2) + ', ' + CONVERT(VARCHAR(10), @ProductNumber3) + ', ' +
    CONVERT(VARCHAR(10), @ProductNumber4) + ', ' + CONVERT(VARCHAR(10), @Day2) + ', ' + CONVERT(VARCHAR(10), @Day3) + ', ' + CONVERT(VARCHAR(10), @Day4) + ', ' + CONVERT(VARCHAR(10), @AllocatedTypeNumber) + ', ' + CONVERT(VARCHAR(10), @HotelNumber) + ', @CleaningPrice OUTPUT'

  IF @ShowPrint = 1
    PRINT @sql

  EXEC sp_executesql @sql, N'@CleaningPrice MONEY OUTPUT', @CleaningPrice OUTPUT

  IF ISNULL(@ResultPackagePrice, 0) = 0
    SET @ResultPackagePrice = 0
  IF ISNULL(@TotalPrice, 0) = 0
    SET @TotalPrice = 0
  IF ISNULL(@CleaningPrice, 0) = 0
    SET @CleaningPrice = 0
  IF ISNULL(@osPercent, 0) = 0
    SET @osPercent = @HotelSetupOwnerPercent

  SET @TotalPrice = @TotalPrice - @ResultPackagePrice
  SET @NettoPrice = (@TotalPrice * @osPercent / 100)

  SELECT @ProductName = ProductName, @ProductType = Text
  FROM Products p WITH (NOLOCK)
  LEFT OUTER JOIN ProductTypes pt WITH (NOLOCK) ON pt.ProductTypeNumber = p.ProductTypeNumber
  WHERE ProductNumber = @ProductNumber

  INSERT INTO #Reservations VALUES (@Referencenumber, @GuestName, @GuestNumber, @HotelNumber, @ArrivalDate, @DepartureDate, @Pax, @Adults, @Children1, @Children2, @TotalPrice, @CleaningPrice, @ResultPackagePrice, @osPercent, @NettoPrice, @RoomNumber, @Status, @ProductName, @ProductType,
    @OwnerProductDescription1, @OwnerProductDescription2, @OwnerProductDescription3, @OwnerProductDescription4)

  FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestName, @GuestNumber, @HotelNumber, @RoomNumber, @ArrivalDate, @DepartureDate, @Adults, @Children1, @Children2, @Price, @TotalPrice, @ProductNumber, @ProductNumber2, @ProductNumber3, @ProductNumber4, @Day2, @Day3, @Day4,
    @PriceOverwritten, @PriceOverwritten2, @PriceOverwritten3, @PriceOverwritten4, @FirstPrice1, @FirstPrice2, @FirstPrice3, @FirstPrice4, @BookedTypeNumber,  @AllocatedTypeNumber,
    @NoOfRooms, @Rooms, @osType, @osPercent, @LineNumber, @Status, @OwnerProductDescription1, @OwnerProductDescription2, @OwnerProductDescription3, @OwnerProductDescription4
END

CLOSE db_cur
DEALLOCATE db_cur

SELECT * FROM #Reservations WITH (NOLOCK) ORDER BY RoomNumber, ReservationArrival
DROP TABLE #Reservations
GO

