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

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

Changes:
  KJ 20210115: Ext: Added calc provision based on period. Req #3936
  KJ 20210111: Ext: Added calc provision cased on product setting. Req #3935
  KJ 20210108: Ext: OwnerReport report changed to be used without using owner products
  KJ 20210107: Bug: Fix for "Owner percent is calculated as integer - should be float". Case #CR203639
  KJ 20200709: Reservations.ReservationCreateDate
  KJ 20110504: Ext: sql is now dynamic - only select reservation create date if @AOnlyOwnerStay = 1. performance issue
  KJ 20110427: Ext: Added RoomNumberKey to output
  KJ 20110426: Ext: Added possible to show only owner reservations. Req# 893
  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 @RoomNumberKey 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 @CreatedDate DATETIME

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 @NettoPrice MONEY
DECLARE @ResultPackagePrice MONEY

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

DECLARE @EnvironmentWareNumber INT
DECLARE @EnvironmentWareText VARCHAR(50)
DECLARE @EnvironmentAccountRate REAL
DECLARE @PriceEnvironment MONEY
DECLARE @NettoPriceEnvironment MONEY
DECLARE @OwnerElectricityReplaceWareText BIT

DECLARE @CleaningWareNumber INT
DECLARE @CleaningWareText VARCHAR(50)
DECLARE @CleaningWareTextEx VARCHAR(50)
DECLARE @CleaningAccountRate REAL
DECLARE @PriceCleaning MONEY
DECLARE @NettoPriceCleaning MONEY
DECLARE @OwnerCleaningReplaceWareText BIT

DECLARE @AdditionalWareNumber INT
DECLARE @AdditionalWareText VARCHAR(50)
DECLARE @AdditionalWareTextEx VARCHAR(50)
DECLARE @AdditionalAccountRate REAL
DECLARE @PriceAdditional MONEY
DECLARE @NettoPriceAdditional MONEY
DECLARE @OwnerAdditionalReplaceWareText BIT

DECLARE @Additional2WareNumber INT
DECLARE @Additional2WareText VARCHAR(50)
DECLARE @Additional2AccountRate REAL
DECLARE @PriceAdditional2 MONEY
DECLARE @NettoPriceAdditional2 MONEY
DECLARE @OwnerAdditional2ReplaceWareText BIT
DECLARE @Additional2Pieces INT

DECLARE @OwnerElectricityWareText VARCHAR(100)
DECLARE @OwnerCleaningWareText VARCHAR(100)
DECLARE @OwnerAdditionalWareText VARCHAR(100)
DECLARE @OwnerAdditional2WareText VARCHAR(100)

DECLARE @EnvironmentPieces INT
DECLARE @AdditionalPieces INT
DECLARE @CleaningPieces INT

DECLARE @OwnerElectricityUseFromWare BIT
DECLARE @OwnerAdditionalUseFromWare BIT

DECLARE @HotelCustomerNumber INT
DECLARE @tmpRateStr VARCHAR(50)
DECLARE @ProvisionAmount MONEY
DECLARE @ProvisionVATAmount MONEY
DECLARE @SplitBillNumber INT

DECLARE @OwnerCalcProvisionBeforeOwnerPart BIT
DECLARE @OwnerCalcProvisionBeforeOwnerPartProduct BIT
DECLARE @ProvisionExcludePeriodStart CHAR(8)
DECLARE @ProvisionExcludePeriodEnd CHAR(8)
DECLARE @ProvisionExcludePeriodSummer BIT
DECLARE @DoCalcProvision BIT
DECLARE @ResDepartureDateStrShort CHAR(4)

SELECT @OwnerCalcProvisionBeforeOwnerPart = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OwnerCalcProvisionBeforeOwnerPart'
SET @OwnerCalcProvisionBeforeOwnerPart = ISNULL(@OwnerCalcProvisionBeforeOwnerPart, 1)

SELECT @ProvisionExcludePeriodStart = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OwnerProvisionExcludePeriodStart'
SELECT @ProvisionExcludePeriodEnd = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OwnerProvisionExcludePeriodEnd'

SET @ProvisionExcludePeriodStart = ISNULL(@ProvisionExcludePeriodStart, '')
SET @ProvisionExcludePeriodEnd = ISNULL(@ProvisionExcludePeriodEnd, '')

IF @ProvisionExcludePeriodStart <> ''
  SET @ProvisionExcludePeriodStart = SUBSTRING(@ProvisionExcludePeriodStart, 5, 4)
IF @ProvisionExcludePeriodEnd <> ''
  SET @ProvisionExcludePeriodEnd = SUBSTRING(@ProvisionExcludePeriodEnd, 5, 4)
SET @ProvisionExcludePeriodSummer = 0
IF @ProvisionExcludePeriodEnd > @ProvisionExcludePeriodStart
  SET @ProvisionExcludePeriodSummer = 1

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,

  ElectricityPrice MONEY NULL,
  CleaningPrice MONEY NULL, 
  AdditionalPrice MONEY NULL,
  Additional2Price MONEY NULL,

  PackagePrice MONEY NULL,
  OwnerPercent FLOAT NULL,
  NettoPrice MONEY NULL,
  OwnerTotal MONEY NULL,
  RoomNumber INT NULL,
  RoomNumberKey INT NULL,
  ReservationStatus INT NULL,
  ProductName VARCHAR(50),
  ProductType VARCHAR(50),
  ProductDescription VARCHAR(50),
  CreatedDate DATETIME
)

IF @ShowPrint = 1
BEGIN
  PRINT '@NewDepartureDate'
  PRINT @NewDepartureDate
END

IF @AOnlyOwnerStay = 1
BEGIN
  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, Rooms.RoomNumber,
    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,
    ISNULL(CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.Adults ELSE Reservations.Adults END, 0) AS Adults,
    ISNULL(CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.Children1 ELSE Reservations.Children1 END, 0) AS Children1,
    ISNULL(CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.Children2 ELSE Reservations.Children2 END, 0) 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, 
    Reservations.ReservationCreateDate,
    Customer.CustomerNumber,
    Reservations.SplitBill
    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
      AND (ISNULL(@AOnlyOwnerStay, 0) = 0 OR ISNULL(ReservationAddress.Link, -1) = Rooms.CustomerNumber)  
    INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = Rooms.HotelNumber 
    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, @RoomNumberKey, @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, @CreatedDate, @HotelCustomerNumber, @SplitBillNumber
END
ELSE
BEGIN
  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, Rooms.RoomNumber,
    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,
    ISNULL(CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.Adults ELSE Reservations.Adults END, 0) AS Adults,
    ISNULL(CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.Children1 ELSE Reservations.Children1 END, 0) AS Children1,
    ISNULL(CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.Children2 ELSE Reservations.Children2 END, 0) 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,
    Customer.CustomerNumber,
    Reservations.SplitBill
    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
      AND (ISNULL(@AOnlyOwnerStay, 0) = 0 OR ISNULL(ReservationAddress.Link, -1) = Rooms.CustomerNumber)  
    INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = Rooms.HotelNumber 
    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, @RoomNumberKey, @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, @HotelCustomerNumber, @SplitBillNumber
END

CREATE TABLE #PackageInfoOwner
(
  View_DateStamp DATETIME,
  View_Id INT,
  View_FK_ProductNumber INT,
  View_PacketType INT
)

INSERT INTO #PackageInfoOwner
  EXEC ProductPricePacketInfo @AStartDateTime, @AEndDateTime, -2

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @Pax = @Adults + @Children1 + @Children2

  SET @NettoPriceEnvironment = 0
  SET @NettoPriceCleaning = 0
  SET @NettoPriceAdditional = 0
  SET @NettoPriceAdditional2 = 0

  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 @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 Environment
  SELECT @OwnerElectricityWareText = dbo.GetHotelSetupKeyValue ('OwnerElectricityWareText', @HotelCustomerNumber)
  SET @OwnerElectricityWareText = ISNULL(@OwnerElectricityWareText, '')
  IF @OwnerElectricityWareText = ''
    SET @OwnerElectricityWareText = 'kw/h'

  SELECT @OwnerElectricityReplaceWareText = dbo.GetHotelSetupKeyValue ('OwnerElectricityReplaceWareText', @HotelCustomerNumber)
  IF ISNULL(@OwnerElectricityReplaceWareText, 0) = 0
    SET @OwnerElectricityReplaceWareText = 0

  SELECT @EnvironmentWareNumber = dbo.GetHotelSetupKeyValue ('ElectricityWareName', @HotelCustomerNumber)
  IF ISNULL(@EnvironmentWareNumber, 0) > 0
    SELECT @EnvironmentWareText = WareName FROM Ware WITH (NOLOCK) WHERE WareNumber = @EnvironmentWareNumber

  IF @OwnerElectricityReplaceWareText = 1
    SET @EnvironmentWareText = @OwnerElectricityWareText

  SELECT @tmpRateStr = dbo.GetHotelSetupKeyValue ('ElectricityAccountRate', @HotelCustomerNumber)
  SET @EnvironmentAccountRate = ISNULL(CONVERT(REAL, REPLACE(@tmpRateStr, ',', '.')), 100)
  SELECT @OwnerElectricityUseFromWare = dbo.GetHotelSetupKeyValue ('OwnerElectricityUseFromWare', @HotelCustomerNumber)
  
  IF ISNULL(@OwnerElectricityUseFromWare, 0) = 1
    SELECT @PriceEnvironment = Amount, @EnvironmentPieces = Pieces FROM DBO.GetFolioAmountByWareType (@ReferenceNumber, @GuestNumber, -1, 1, @ResArrivalDateStr, @ResDepartureDateStr)
  ELSE  
    SELECT @PriceEnvironment = Amount, @EnvironmentPieces = Pieces FROM DBO.GetFolioAmountByWareNumber (@ReferenceNumber, @GuestNumber, -1, @EnvironmentWareNumber, @ResArrivalDateStr, @ResDepartureDateStr)
  IF ISNULL(@PriceEnvironment, 0) > 0
    SET @NettoPriceEnvironment = (@PriceEnvironment * @EnvironmentAccountRate / 100)

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

  -- Calc Cleaning
  SELECT @OwnerCleaningWareText = dbo.GetHotelSetupKeyValue ('OwnerCleaningWareText', @HotelCustomerNumber)
  SET @OwnerCleaningWareText = ISNULL(@OwnerCleaningWareText, '')
  IF @OwnerCleaningWareText = ''
    SET @OwnerCleaningWareText = 'm3'

  SELECT @OwnerCleaningReplaceWareText = dbo.GetHotelSetupKeyValue ('OwnerCleaningReplaceWareText', @HotelCustomerNumber)
  IF ISNULL(@OwnerCleaningReplaceWareText, 0) = 0
    SET @OwnerCleaningReplaceWareText = 0
    
  SELECT @CleaningWareNumber = dbo.GetHotelSetupKeyValue ('CleaningWareName', @HotelCustomerNumber)
  IF ISNULL(@CleaningWareNumber, 0) > 0
    SELECT @CleaningWareText = WareName FROM Ware WITH (NOLOCK) WHERE WareNumber = @CleaningWareNumber
    
  IF @OwnerCleaningReplaceWareText = 1
    SET @CleaningWareText = @OwnerCleaningWareText

  SELECT @tmpRateStr = dbo.GetHotelSetupKeyValue ('CleaningAccountRate', @HotelCustomerNumber)
  SET @CleaningAccountRate = ISNULL(CONVERT(REAL, REPLACE(@tmpRateStr, ',', '.')), 100)
  
  SELECT @PriceCleaning = Amount, @CleaningPieces = Pieces FROM DBO.GetFolioAmountByWareNumber (@ReferenceNumber, @GuestNumber, -1, @CleaningWareNumber, @ResArrivalDateStr, @ResDepartureDateStr)
  IF ISNULL(@PriceCleaning, 0) > 0
    SET @NettoPriceCleaning = (@PriceCleaning * @CleaningAccountRate / 100)

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

  -- Calc Additional
  SELECT @OwnerAdditionalWareText = dbo.GetHotelSetupKeyValue ('OwnerAdditionalWareText', @HotelCustomerNumber)
  SET @OwnerAdditionalWareText = ISNULL(@OwnerAdditionalWareText, '')
  IF @OwnerAdditionalWareText = ''
    SET @OwnerAdditionalWareText = 'm3'
  
  SELECT @OwnerAdditionalReplaceWareText = dbo.GetHotelSetupKeyValue ('OwnerAdditionalReplaceWareText', @HotelCustomerNumber)
  IF ISNULL(@OwnerAdditionalReplaceWareText, 0) = 0
    SET @OwnerAdditionalReplaceWareText = 0

  SELECT @AdditionalWareNumber = dbo.GetHotelSetupKeyValue ('AdditionalWareName', @HotelCustomerNumber)
  IF ISNULL(@AdditionalWareNumber, 0) > 0
    SELECT @AdditionalWareText = WareName FROM Ware WITH (NOLOCK) WHERE WareNumber = @AdditionalWareNumber

  IF @OwnerAdditionalReplaceWareText = 1
    SET @AdditionalWareText = @OwnerAdditionalWareText
  
  SELECT @tmpRateStr = dbo.GetHotelSetupKeyValue ('AdditionalAccountRate', @HotelCustomerNumber)
  SET @AdditionalAccountRate = ISNULL(CONVERT(REAL, REPLACE(@tmpRateStr, ',', '.')), 100)
  SELECT @OwnerAdditionalUseFromWare = dbo.GetHotelSetupKeyValue ('OwnerAdditionalUseFromWare', @HotelCustomerNumber)
  
  IF ISNULL(@OwnerAdditionalUseFromWare, 0) = 1
    SELECT @PriceAdditional = Amount, @AdditionalPieces = Pieces FROM DBO.GetFolioAmountByWareType (@ReferenceNumber, @GuestNumber, -1, 6, @ResArrivalDateStr, @ResDepartureDateStr)
  ELSE  
    SELECT @PriceAdditional = Amount, @AdditionalPieces = Pieces FROM DBO.GetFolioAmountByWareNumber (@ReferenceNumber, @GuestNumber, -1, @AdditionalWareNumber, @ResArrivalDateStr, @ResDepartureDateStr)
  IF ISNULL(@PriceAdditional, 0) > 0
    SET @NettoPriceAdditional = (@PriceAdditional * @AdditionalAccountRate / 100)

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

  -- Calc Additional 2
  SELECT @OwnerAdditional2WareText = dbo.GetHotelSetupKeyValue ('OwnerAdditional2WareText', @HotelCustomerNumber)
  SET @OwnerAdditional2WareText = ISNULL(@OwnerAdditional2WareText, '')
  
  SELECT @OwnerAdditional2ReplaceWareText = dbo.GetHotelSetupKeyValue ('OwnerAdditional2ReplaceWareText', @HotelCustomerNumber)
  IF ISNULL(@OwnerAdditional2ReplaceWareText, 0) = 0
    SET @OwnerAdditional2ReplaceWareText = 0

  SELECT @Additional2WareNumber = dbo.GetHotelSetupKeyValue ('Additional2WareName', @HotelCustomerNumber)
  IF ISNULL(@Additional2WareNumber, 0) > 0
    SELECT @Additional2WareText = WareName FROM Ware WITH (NOLOCK) WHERE WareNumber = @Additional2WareNumber

  IF @OwnerAdditional2ReplaceWareText = 1
    SET @Additional2WareText = @OwnerAdditional2WareText
  
  SELECT @tmpRateStr = dbo.GetHotelSetupKeyValue ('Additional2AccountRate', @HotelCustomerNumber)
  SET @Additional2AccountRate = ISNULL(CONVERT(REAL, REPLACE(@tmpRateStr, ',', '.')), 100)
  
  SELECT @PriceAdditional2 = Amount, @Additional2Pieces = Pieces FROM DBO.GetFolioAmountByWareNumber (@ReferenceNumber, @GuestNumber, -1, @Additional2WareNumber, @ResArrivalDateStr, @ResDepartureDateStr)

  IF ISNULL(@PriceAdditional2, 0) > 0
    SET @NettoPriceAdditional2 = (@PriceAdditional2 * @Additional2AccountRate / 100)

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

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

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

  SET @DoCalcProvision = 0
  IF ISNULL(@OwnerCalcProvisionBeforeOwnerPartProduct, @OwnerCalcProvisionBeforeOwnerPart) = 1
  BEGIN
    IF @ShowPrint = 1
    BEGIN
      PRINT 'Check for provision period'
      PRINT '@ProvisionExcludePeriodStart - End'
      PRINT @ProvisionExcludePeriodStart
      PRINT @ProvisionExcludePeriodEnd
      PRINT '@ProvisionExcludePeriodSummer'
      PRINT @ProvisionExcludePeriodSummer
    END
    SET @DoCalcProvision = 1

    IF @ProvisionExcludePeriodStart <> '' AND @ProvisionExcludePeriodEnd <> ''
    BEGIN
      SET @ResDepartureDateStrShort = SUBSTRING(@ResDepartureDateStr, 5, 4)
      IF @ProvisionExcludePeriodSummer = 1
      BEGIN
        IF @ResDepartureDateStrShort >= @ProvisionExcludePeriodStart AND @ResDepartureDateStrShort <= @ProvisionExcludePeriodEnd
          SET @DoCalcProvision = 0
      END
      ELSE
      BEGIN
        IF (@ResDepartureDateStrShort <= '1231' AND @ResDepartureDateStrShort >= @ProvisionExcludePeriodStart) OR
          (@ResDepartureDateStrShort >= '0101' AND @ResDepartureDateStrShort <= @ProvisionExcludePeriodEnd)
          SET @DoCalcProvision = 0
      END
    END    
  END

  SET @ProvisionVATAmount = 0
  IF @DoCalcProvision = 1
    EXEC CalcProvision @AllocatedTypeNumber, @Referencenumber, @GuestNumber, @TotalPrice, 0, @SplitBillNumber, @ProvisionAmount OUTPUT, @ProvisionVATAmount OUTPUT
  ELSE
  BEGIN
    SET @ProvisionAmount  = 0
    SET @ProvisionVATAmount  = 0
  END

  IF @ShowPrint = 1
  BEGIN
    PRINT '@DoCalcProvision'
    PRINT @DoCalcProvision
    PRINT '@ProvisionAmount'
    PRINT @ProvisionAmount
    PRINT '@OwnerCalcProvisionBeforeOwnerPartProduct'
    PRINT @OwnerCalcProvisionBeforeOwnerPartProduct
    PRINT '@OwnerCalcProvisionBeforeOwnerPart'
    PRINT @OwnerCalcProvisionBeforeOwnerPart
  END

  SET @TotalPrice = @TotalPrice - @ResultPackagePrice - ISNULL(@ProvisionAmount, 0)
  SET @NettoPrice = (@TotalPrice * @osPercent / 100)

  INSERT INTO #Reservations VALUES (@Referencenumber, @GuestName, @GuestNumber, @HotelNumber, @ArrivalDate, @DepartureDate, @Pax, @Adults, @Children1, @Children2, @TotalPrice, 
    @NettoPriceEnvironment, @NettoPriceCleaning, @NettoPriceAdditional, @NettoPriceAdditional2, @ResultPackagePrice, @osPercent, @NettoPrice, 
    (@NettoPrice + @NettoPriceEnvironment + @NettoPriceCleaning + @NettoPriceAdditional + @NettoPriceAdditional2),
    @RoomNumber, @RoomNumberKey, @Status, @ProductName, @ProductType, @ProductDescription, @CreatedDate)

  IF @AOnlyOwnerStay = 1
    FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestName, @GuestNumber, @HotelNumber, @RoomNumber, @RoomNumberKey, @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, @CreatedDate, @HotelCustomerNumber, @SplitBillNumber
  ELSE
    FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestName, @GuestNumber, @HotelNumber, @RoomNumber, @RoomNumberKey, @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, @HotelCustomerNumber, @SplitBillNumber
END

CLOSE db_cur
DEALLOCATE db_cur

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