CREATE PROCEDURE [dbo].[OwnerReportReservationsTimeShare] @AStartDateTime DateTime, @AEndDateTime DateTime, @AReservationStatusStr VARCHAR(25), @ARoomNumberStr VARCHAR(2500), 
  @AHotelNumber INT = -1, @AUserIdNumber INT = 0, @AAddToFolio BIT = 0, @APostTicketUsage BIT = 1, @AOnlyGetNettoPrice BIT = 0, @ANettoPrice MONEY = 0 OUTPUT AS
SET NOCOUNT ON

/*
  Created by Kent Johansen
  Used by owner calculation by product/time share

  Changes:
    KJ 20160707: Ext: Added Owner Product description.
    KJ 20150908: New: Alpen travel owner calculation req #2725
    KJ 20140519: Bug: Fix for "No calculation if @AHotelNumber is null". Case #CR072551
    KJ 20130918: Bug: Fix for "Wrong owner ware used". Case #CR064271
    KJ 20130702: Bug: Fix for "Error converting data type varchar to int.". Case #CR061197
    KJ 20130606: Ext: Use wtChargePower/wtChargeWater to get amount Case #CR059367
    KJ 20130606: Ext: Use wtChargePower/wtChargeWater to get amount Case #CR059367
    KJ 20130506: Bug: Fix for "Wrong account incl/excl VAT". Case #CR055013
    KJ 20130502: Bug: Fix for "Wrong text on owner postings". Case #cr055530
    KJ 20130423: Ext: Ownerware by roomtype. Incl/excl VAT. Req #1576
    KJ 20130304: Bug: Fix for "Implicit conversion from data type money to nvarchar is not allowed. Use the CONVERT function to run this quer". Case #CR53526
    KJ 20130213: Bug: Fix for "The number of variables declared in the INTO list must match that of selected columns.". Case #CR052911
    KJ 20120211: Bug: Fix for "Invalud productNumber 2". Case #CR052780
    KJ 20130205: Ext: Support for +product
    KJ 20130115: Ext: Changed setup for Additional text. Case #CR051665
    KJ 20130102: Ext: More settings per hotel. Req #1413
    KJ 20120910: Bug: Fix for "General SQL Error, Incorrect syntax" when nul product. Case #CR046695
    KJ 20120702: Bug: Fix for "General SQL Error, Incorrect syntax". Case #CR044805 + flere
    KJ 20120412: Ext: Possible to have more than one usage fee ware per room.
    KJ 20120411: Ext: Changed how to get usage fee. Removed from Hotelsetup-> Roomtypes to Ware->Rooms+rule Req #1197
    KJ 20120222: Ext: Poosible to setup ownerware account number per hotel. Req #1163+1162
    KJ 20120116: Bug: Fix for "Departure addon calculated wrong". Case #CR038806
    KJ 20111021: Ext: Added "Owner discount text for online owner login". Req #1008
    KJ 20111018: Ext: Added Owner discount Req #1008.
    KJ 20110726: Bug: Fix for "Missing space between ref and date". Case# CR026136
    KJ 20110629: Ext: "Only post ticketfee if roomtype is added to ware". req# 956
    KJ 20110624: Bug: Fix for "Wrong format of date". Case# CR024969
    KJ 20110607: Bug: Fix for "Ticket fee can be posted multiple times if owner change since last post". Case# CR024380
    KJ 20110512: Ext: Added owner period text Req# 899
    KJ 20110420: Ext: Added replace owner ware text. Req# 892
    KJ 20110202: Bug: Fix for "D6/D8 should only be based on number of adults and NOT pax". Case# CR019359
    KJ 20110131: Bug: Fix for "Convert error of ie 325,33 to MONEY". Case# CR019235
    KJ 20110110: Req: Out room possible for Owner house. Owner calculation is done when out room is made. Req# 753.
    KJ 20101207: Bug: Fix for "Only calculate addon once per reservation" case# CR018007
    KJ 20101004: Bug: Fix for "Usage fee and ticket fee not posted for all rooms"
    KJ 20100920: Added AOwnertype (Logi, OwnerElectricity, OwnerCleaning, OwnerAdditional)
    KJ 20100831: Ext: Added FolioNumber as output parameter to OwnerAddToFolio
    KJ 20100830: Ext: Added post of ticket fee
    KJ 20100817: Ext: Added post of brugsret
    KJ 20100728: Ext: Added calculate price by allocated type
    KJ 20100728: Ext: Added setup for owner ware text on electricity, cleaning and additional
    KJ 20100312: Bug: Fix for "Reservation not shown if the owner himself is stay"
    KJ 20100311: Ext: Added D6/D8 text
    KJ 20100217: Bug: Always use departuredate
    KJ 20100203: Bug: Always use departuredate
    KJ 20100201: Bug: Fix for "List of reservations not correct for AddToFolio=1"
    KJ 20100211: Ext: Added owner percent and special owner product for calculation
    KJ 20100115: Ext: Added ResTurnover column
    KJ 20100112: Ext: Added Owner calculation per room number
    KJ 20100111: Ext: Added pieces for Environment, Cleaning and additional wares
    KJ 20080103: Fix for "Reservations with no customernumber not included in select statement"
    KJ 20071113: Bug: Fix for "Do not include reservations when the owner uses their own house/room"
    KJ 20071112: Bug "Fix for ARoomNumberStr sometimes null"
    KJ 20071112: Fix for "Wrong departuredate for environment/cleaning/additional calculation"

*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

SET @AAddToFolio = ISNULL(@AAddToFolio, 0)
SET @APostTicketUsage = ISNULL(@APostTicketUsage, 1)
SET @AHotelNumber = ISNULL(@AHotelNumber, -1)

IF @AAddToFolio = 0
BEGIN
  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, 
    ResTotalPrice MONEY NULL,
    ElectricityPrice MONEY NULL,
    CleaningPrice MONEY NULL, 
    AdditionalPrice MONEY NULL,
    TotalPrice MONEY NULL,
    NettoPrice MONEY NULL,
    RoomNumber INT NULL,
    ResTurnover MONEY NULL,
    OwnerPercent INT NULL,
    AdditionalText VARCHAR(50),
    OwnerWareText VARCHAR(50),
    OwnerProductDescription1 VARCHAR(128),
    OwnerProductDescription2 VARCHAR(128),
    OwnerProductDescription3 VARCHAR(128),
    OwnerProductDescription4 VARCHAR(128)
  )
END

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

IF ISNULL(@ARoomNumberStr, '') = ''
  SET @ARoomNumberStr = ''

DECLARE @ReservationDays INT
DECLARE @tmpTotalProductPrice MONEY
DECLARE @tmpTotalProductPrice2 MONEY
DECLARE @ArrivalDateStr VARCHAR(8)
DECLARE @DepartureDateStr VARCHAR(8)
DECLARE @DepartureDateStrOneMore VARCHAR(8)
DECLARE @tmpRateStr VARCHAR(50)

DECLARE @TimeShareDays INT
DECLARE @tmpDaysProduct1 INT
DECLARE @tmpDaysProduct2 INT
DECLARE @tmpTimeShareProduct1 INT
DECLARE @tmpTimeShareProduct2 INT
DECLARE @OwnerWareNumber INT
DECLARE @OwnerWareText VARCHAR(50)
DECLARE @tmpOwnerWareText VARCHAR(50)
DECLARE @OwnerWareText1 VARCHAR(50)
DECLARE @TotalProductPrice MONEY
DECLARE @NettoPrice MONEY

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

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

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

DECLARE @OwnerConsumption BIT
DECLARE @EnvironmentPieces INT
DECLARE @AdditionalPieces INT
DECLARE @CleaningPieces INT

DECLARE @OwnerElectricityReplaceWareText BIT
DECLARE @OwnerCleaningReplaceWareText BIT
DECLARE @OwnerAdditionalReplaceWareText BIT

DECLARE @ProductNumber INT
DECLARE @OwnerProductNumber INT
DECLARE @ProductNumber2 INT
DECLARE @OwnerProductNumber2 INT
DECLARE @ProductNumber3 INT
DECLARE @OwnerProductNumber3 INT
DECLARE @ProductNumber4 INT
DECLARE @OwnerProductNumber4 INT
DECLARE @PAX INT
DECLARE @TotalAllPrice MONEY

DECLARE @AddAdditionalText INT
DECLARE @AddPeriodText BIT

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

DECLARE @OwnerCalculationUseAllocatedType BIT

DECLARE @UsageFeeWareNumber INT 
DECLARE @UsageFeeWareText VARCHAR(80)
DECLARE @UsageFeePrice MONEY

DECLARE @TicketFeeWareNumber INT 
DECLARE @TicketFeeWareText VARCHAR(80)
DECLARE @TicketFeePrice MONEY
DECLARE @LastTicketFeePostRecur DATETIME
DECLARE @LastOwnerChange DATETIME
DECLARE @LastTicketFeePost DATETIME

DECLARE @InclusiveAddon BIT 
DECLARE @HotelCustomerNumber INT

DECLARE @OwnerElectricityUseFromWare BIT
DECLARE @OwnerAdditionalUseFromWare BIT

DECLARE @s VARCHAR(100)

SELECT @OwnerCalculationUseAllocatedType = CONVERT(BIT, KeyValue) FROM HotelSetup WHERE SearchKey = 'OwnerCalculationUseAllocatedType'
SET @OwnerCalculationUseAllocatedType = ISNULL(@OwnerCalculationUseAllocatedType, 0) 

DECLARE @OwnerPartAsProduct BIT
SELECT @OwnerPartAsProduct = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OwnerPartAsProduct'
IF ISNULL(@OwnerPartAsProduct, 1) = 1
  SET @OwnerPartAsProduct = 1

SELECT @TimeShareDays = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'TimeShareDays'
SELECT @tmpTimeShareProduct1 = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'TimeShareProduct'
SELECT @tmpTimeShareProduct2 = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'TimeShareProduct2'

SELECT @OwnerConsumption = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OwnerConsumptionElAndWater'
IF ISNULL(@OwnerConsumption, 0) = 0
  SET @OwnerConsumption = 0

SELECT @AddPeriodText = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OwnerAddPeriodText'
IF ISNULL(@AddPeriodText, 0) = 0
  SET @AddPeriodText = 0

IF @ShowPrint = 1
BEGIN
  PRINT '@tmpTimeShareProduct1'
  PRINT @tmpTimeShareProduct1
  PRINT '@tmpTimeShareProduct2'
  PRINT @tmpTimeShareProduct2
END

-- Ticket fee
SELECT @TicketFeeWareNumber = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OwnerTicketFeeWareNumber'

SELECT DISTINCT @TicketFeeWareText = Ware.WareName, @TicketFeePrice = CONVERT(MONEY, RecurringRule.RuleValue)
FROM RecurringPatternSpecified WITH (NOLOCK)
INNER JOIN RecurringPatternWareXTab WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternID = RecurringPatternSpecified.FK_RecurringPatternID
  AND RecurringPatternWareXTab.FK_WareNumber = @TicketFeeWareNumber
INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = RecurringPatternWareXTab.FK_WareNumber
WHERE RecurringPatternSpecified.StartDateTime = CONVERT(CHAR(8), @AStartDateTime, 112)
AND RecurringPatternSpecified.Deleted = 0

IF ISNULL(@TicketFeePrice, 0) <> 0
BEGIN
  --Get number of pax between today and last time ticketfee was posted
  SELECT TOP 1 @LastTicketFeePostRecur = RecurringPatternSpecified.StartDateTime
  FROM RecurringPatternSpecified WITH (NOLOCK)
  INNER JOIN RecurringPatternWareXTab WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternID = RecurringPatternSpecified.FK_RecurringPatternID
    AND RecurringPatternWareXTab.FK_WareNumber = @TicketFeeWareNumber
  INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = RecurringPatternWareXTab.FK_WareNumber
  WHERE RecurringPatternSpecified.StartDateTime < CONVERT(CHAR(8), @AStartDateTime, 112)
  AND RecurringPatternSpecified.Deleted = 0
  ORDER BY RecurringPatternSpecified.StartDateTime DESC
  
  IF @ShowPrint = 1
  BEGIN
    PRINT '@LastTicketFeePostRecur'
    PRINT @LastTicketFeePostRecur
  END
END

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 @CurrentDate DATETIME
DECLARE @TmpProductStartDate DATETIME 
DECLARE @TmpProductEndDate DATETIME 
DECLARE @Adults INT
DECLARE @Children1 INT
DECLARE @Children2 INT 
DECLARE @Price MONEY
DECLARE @TotalPrice MONEY
DECLARE @BookedTypeNumber INT
DECLARE @AllocatedTypeNumber INT
DECLARE @NoOfRooms INT
DECLARE @Rooms INT
DECLARE @osType INT
DECLARE @osPercent INT
DECLARE @OwnerCustomerNumber INT
DECLARE @Link INT
DECLARE @LineNumber INT
DECLARE @Status INT
DECLARE @OwnerDiscountRate SMALLMONEY
DECLARE @DiscoutTxt VARCHAR(20)
DECLARE @Day2 INT
DECLARE @Day3 INT
DECLARE @Day4 INT
DECLARE @TmpRoomType INT
DECLARE @Addon1 MONEY
DECLARE @Addon2 MONEY
DECLARE @Addon3 MONEY
DECLARE @Addon4 MONEY
DECLARE @DummyStr VARCHAR(50)
DECLARE @DummyInt INT
DECLARE @DummyMoney MONEY
DECLARE @DummyBit BIT
DECLARE @PriceProduct1 MONEY
DECLARE @PriceProduct2 MONEY
DECLARE @PriceProduct3 MONEY
DECLARE @PriceProduct4 MONEY
DECLARE @OwnerProductDescription1 VARCHAR(128)
DECLARE @OwnerProductDescription2 VARCHAR(128)
DECLARE @OwnerProductDescription3 VARCHAR(128)
DECLARE @OwnerProductDescription4 VARCHAR(128)

IF @AAddToFolio = 1
BEGIN
  DECLARE db_cur CURSOR LOCAL FOR
  SELECT
    R.ReferenceNumber,
    CASE WHEN ra.FirstName = '' THEN ra.LastName ELSE ra.FirstName + ' ' + ra.LastName END,
    R.GuestNumber, Rooms.HotelNumber, Rooms.Number, Rooms.RoomNumber,
    CASE WHEN R.GuestNumber = 0 THEN rr.ArrivalDate ELSE R.ArrivalDate END,
    CASE WHEN R.GuestNumber = 0 THEN rr.DepartureDate ELSE R.DepartureDate END,
    CASE WHEN R.GuestNumber = 0 THEN rr.Adults ELSE R.Adults END,
    CASE WHEN R.GuestNumber = 0 THEN rr.Children1 ELSE R.Children1 END,
    CASE WHEN R.GuestNumber = 0 THEN rr.Children2 ELSE R.Children2 END,
    CASE WHEN R.GuestNumber = 0 THEN rr.Price ELSE R.Price END,
    CASE WHEN R.GuestNumber = 0 THEN rr.TotalPrice ELSE R.TotalPrice END,
    CASE WHEN R.GuestNumber = 0 THEN ISNULL(rr.Day2,0) ELSE ISNULL(R.Day2,0) END,
    CASE WHEN R.GuestNumber = 0 THEN ISNULL(rr.Day3,0) ELSE ISNULL(R.Day3,0) END,
    CASE WHEN R.GuestNumber = 0 THEN ISNULL(rr.Day4,0) ELSE ISNULL(R.Day4,0) END,
    
    CASE WHEN R.GuestNumber = 0 THEN rr.ProductNumber ELSE R.ProductNumber END,
    CASE WHEN R.GuestNumber = 0 THEN rro.FK_ProductsOwnerProduct ELSE Ro.FK_ProductsOwnerProduct END,
    CASE WHEN R.GuestNumber = 0 THEN rr.ProductNumber2 ELSE R.ProductNumber2 END,
    CASE WHEN R.GuestNumber = 0 THEN rro2.FK_ProductsOwnerProduct ELSE Ro2.FK_ProductsOwnerProduct END,
    CASE WHEN R.GuestNumber = 0 THEN rr.ProductNumber3 ELSE R.ProductNumber3 END,
    CASE WHEN R.GuestNumber = 0 THEN rro3.FK_ProductsOwnerProduct ELSE Ro3.FK_ProductsOwnerProduct END,
    CASE WHEN R.GuestNumber = 0 THEN rr.ProductNumber4 ELSE R.ProductNumber4 END,
    CASE WHEN R.GuestNumber = 0 THEN rro4.FK_ProductsOwnerProduct ELSE Ro4.FK_ProductsOwnerProduct END,
 
    rr.BookedTypeNumber,
    Rooms.RoomTypeNumber,
    ISNULL(rr.NumberOfRooms,0),
    (CASE WHEN R.GuestNumber = 0 THEN
      CONVERT(INT, CONVERT(DATETIME, CASE WHEN rr.DepartureDate > @DepartureDate THEN @NewDepartureDate ELSE rr.DepartureDate END, 112) - CONVERT(DATETIME, CASE WHEN rr.ArrivalDate <= @ArrivalDate THEN @ArrivalDate ELSE rr.ArrivalDate END, 112))
    ELSE
      CONVERT(INT, CONVERT(DATETIME, CASE WHEN R.DepartureDate > @DepartureDate THEN @NewDepartureDate ELSE R.DepartureDate END, 112) - CONVERT(DATETIME, CASE WHEN R.ArrivalDate <= @ArrivalDate THEN @ArrivalDate ELSE R.ArrivalDate END, 112))
    END)* rr.NumberOfRooms,
    Rooms.OwnerSettlementType, Rooms.OwnerSettlementPercent, Rooms.CustomerNumber, ra.Link,
    rrx.LineNumber,
    R.Status, 
    (
      SELECT TOP 1 raOwnerDisc.OwnerDiscountRate 
      FROM ReservationAddressXTab raxOwnerDisc WITH (NOLOCK)
      INNER JOIN ReservationAddress raOwnerDisc WITH (NOLOCK) ON raxOwnerDisc.ReferenceNumber = R.ReferenceNumber 
        AND raxOwnerDisc.GuestNumber = R.GuestNumber
        AND raOwnerDisc.AddressNumber = raxOwnerDisc.AddressNumber
        AND raOwnerDisc.AddressType = R.DiscountCustomerType
    ) ,
    Customer.CustomerNumber,
	'','','',''
    FROM Reservations R WITH (NOLOCK)
    LEFT OUTER JOIN Products ro WITH (NOLOCK) ON ro.ProductNumber = r.ProductNumber
    LEFT OUTER JOIN Products ro2 WITH (NOLOCK) ON ro2.ProductNumber = r.ProductNumber2
    LEFT OUTER JOIN Products ro3 WITH (NOLOCK) ON ro3.ProductNumber = r.ProductNumber3
    LEFT OUTER JOIN Products ro4 WITH (NOLOCK) ON ro4.ProductNumber = r.ProductNumber4
    INNER JOIN ReservationRoomLineXTab rrx WITH (NOLOCK) ON R.ReferenceNumber = rrx.ReferenceNumber 
      AND R.GuestNumber = rrx.GuestNumber
    INNER JOIN ReservationRoomLine rr WITH (NOLOCK) ON rrx.LineNumber = rr.LineNumber
      AND rr.DepartureDate= @AStartDateTime
    LEFT OUTER JOIN Products rro WITH (NOLOCK) ON rro.ProductNumber = rr.ProductNumber
    LEFT OUTER JOIN Products rro2 WITH (NOLOCK) ON rro2.ProductNumber = rr.ProductNumber2
    LEFT OUTER JOIN Products rro3 WITH (NOLOCK) ON rro3.ProductNumber = rr.ProductNumber3
    LEFT OUTER JOIN Products rro4 WITH (NOLOCK) ON rro4.ProductNumber = rr.ProductNumber4
    INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = rr.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 rax WITH (NOLOCK) ON R.ReferenceNumber = rax.ReferenceNumber 
      AND R.GuestNumber = rax.GuestNumber
    INNER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber 
      AND ra.AddressType = 1
      AND ISNULL(ra.Link, -1) <> Rooms.CustomerNumber
    INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = Rooms.HotelNumber 
    WHERE 1=1
    AND R.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusStr))
    AND R.ReferenceNumber > 0
    AND ((R.GuestNumber = 0 AND rr.Adults+rr.Children1+rr.Children2 > 0) OR (R.GuestNumber > 0 AND R.PaysAdults+R.PaysChildren1+R.PaysChildren2 > 0))
    ORDER BY Rooms.Number
END
ELSE
BEGIN 
  DECLARE db_cur CURSOR LOCAL FOR
  SELECT
    R.ReferenceNumber,
    CASE WHEN ra.FirstName = '' THEN ra.LastName ELSE ra.FirstName + ' ' + ra.LastName END,
    R.GuestNumber, Rooms.HotelNumber, Rooms.Number, Rooms.RoomNumber,
    CASE WHEN R.GuestNumber = 0 THEN rr.ArrivalDate ELSE R.ArrivalDate END AS ArrivalDate,
    CASE WHEN R.GuestNumber = 0 THEN rr.DepartureDate ELSE R.DepartureDate END AS DepartureDate,
    CASE WHEN R.GuestNumber = 0 THEN rr.Adults ELSE R.Adults END AS Adults,
    CASE WHEN R.GuestNumber = 0 THEN rr.Children1 ELSE R.Children1 END AS Children1,
    CASE WHEN R.GuestNumber = 0 THEN rr.Children2 ELSE R.Children2 END AS Children2,
    CASE WHEN R.GuestNumber = 0 THEN rr.Price ELSE R.Price END AS Price,
    CASE WHEN R.GuestNumber = 0 THEN rr.TotalPrice ELSE R.TotalPrice END AS TotalPrice,
    CASE WHEN R.GuestNumber = 0 THEN ISNULL(rr.Day2,0) ELSE ISNULL(R.Day2,0) END,
    CASE WHEN R.GuestNumber = 0 THEN ISNULL(rr.Day3,0) ELSE ISNULL(R.Day3,0) END,
    CASE WHEN R.GuestNumber = 0 THEN ISNULL(rr.Day4,0) ELSE ISNULL(R.Day4,0) END,
    CASE WHEN R.GuestNumber = 0 THEN rr.ProductNumber ELSE R.ProductNumber END AS ProductNumber,
    CASE WHEN R.GuestNumber = 0 THEN rro.FK_ProductsOwnerProduct ELSE Ro.FK_ProductsOwnerProduct END AS OwnerProductNumber,
    CASE WHEN R.GuestNumber = 0 THEN rr.ProductNumber2 ELSE R.ProductNumber2 END,
    CASE WHEN R.GuestNumber = 0 THEN rro2.FK_ProductsOwnerProduct ELSE Ro2.FK_ProductsOwnerProduct END,
    CASE WHEN R.GuestNumber = 0 THEN rr.ProductNumber3 ELSE R.ProductNumber3 END,
    CASE WHEN R.GuestNumber = 0 THEN rro3.FK_ProductsOwnerProduct ELSE Ro3.FK_ProductsOwnerProduct END,
    CASE WHEN R.GuestNumber = 0 THEN rr.ProductNumber4 ELSE R.ProductNumber4 END,
    CASE WHEN R.GuestNumber = 0 THEN rro4.FK_ProductsOwnerProduct ELSE Ro4.FK_ProductsOwnerProduct END,

    rr.BookedTypeNumber,
    Rooms.RoomTypeNumber,
    ISNULL(rr.NumberOfRooms,0),
    (CASE WHEN R.GuestNumber = 0 THEN
      CONVERT(INT, CONVERT(DATETIME, CASE WHEN rr.DepartureDate > @DepartureDate THEN @NewDepartureDate ELSE rr.DepartureDate END, 112) - CONVERT(DATETIME, CASE WHEN rr.ArrivalDate <= @ArrivalDate THEN @ArrivalDate ELSE rr.ArrivalDate END, 112))
    ELSE
      CONVERT(INT, CONVERT(DATETIME, CASE WHEN R.DepartureDate > @DepartureDate THEN @NewDepartureDate ELSE R.DepartureDate END, 112) - CONVERT(DATETIME, CASE WHEN R.ArrivalDate <= @ArrivalDate THEN @ArrivalDate ELSE R.ArrivalDate END, 112))
    END)* rr.NumberOfRooms,
    Rooms.OwnerSettlementType, Rooms.OwnerSettlementPercent, Rooms.CustomerNumber, ra.Link,
    rrx.LineNumber,
    R.Status, 
    (
      SELECT TOP 1 raOwnerDisc.OwnerDiscountRate 
      FROM ReservationAddressXTab raxOwnerDisc WITH (NOLOCK)
      INNER JOIN ReservationAddress raOwnerDisc WITH (NOLOCK) ON raxOwnerDisc.ReferenceNumber = R.ReferenceNumber 
        AND raxOwnerDisc.GuestNumber = R.GuestNumber
        AND raOwnerDisc.AddressNumber = raxOwnerDisc.AddressNumber
        AND raOwnerDisc.AddressType = R.DiscountCustomerType
    ),
    Customer.CustomerNumber,
  (CASE WHEN R.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 = rr.ProductNumber
	 )
   ELSE 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = r.ProductNumber
	 )
   END  
  ),

  (CASE WHEN r.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 = rr.ProductNumber2
	 )
   ELSE 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = r.ProductNumber2
	 )
   END  
  ),

  (CASE WHEN r.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 = rr.ProductNumber3
	 )
   ELSE 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = r.ProductNumber3
	 )
   END  
  ),

  (CASE WHEN r.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 = rr.ProductNumber4
	 )
   ELSE 
     (
	 SELECT ProductsOwner.Description 
	 FROM Products ProductsOwner WITH (NOLOCK)
	 INNER JOIN Products WITH (NOLOCK) ON Products.FK_ProductsOwnerProduct = ProductsOwner.ProductNumber
	 AND Products.ProductNumber = r.ProductNumber4
	 )
   END  
  )
    FROM Reservations R WITH (NOLOCK)
    LEFT OUTER JOIN Products ro WITH (NOLOCK) ON ro.ProductNumber = r.ProductNumber
    LEFT OUTER JOIN Products ro2 WITH (NOLOCK) ON ro2.ProductNumber = r.ProductNumber2
    LEFT OUTER JOIN Products ro3 WITH (NOLOCK) ON ro3.ProductNumber = r.ProductNumber3
    LEFT OUTER JOIN Products ro4 WITH (NOLOCK) ON ro4.ProductNumber = r.ProductNumber4
    INNER JOIN ReservationRoomLineXTab rrx WITH (NOLOCK) ON R.ReferenceNumber = rrx.ReferenceNumber 
      AND R.GuestNumber = rrx.GuestNumber
    INNER JOIN ReservationRoomLine rr WITH (NOLOCK) ON rrx.LineNumber = rr.LineNumber
      AND rr.DepartureDate >= @AStartDateTime AND rr.DepartureDate <= @AEndDateTime
    LEFT OUTER JOIN Products rro WITH (NOLOCK) ON rro.ProductNumber = rr.ProductNumber
    LEFT OUTER JOIN Products rro2 WITH (NOLOCK) ON rro2.ProductNumber = rr.ProductNumber2
    LEFT OUTER JOIN Products rro3 WITH (NOLOCK) ON rro3.ProductNumber = rr.ProductNumber3
    LEFT OUTER JOIN Products rro4 WITH (NOLOCK) ON rro4.ProductNumber = rr.ProductNumber4
    INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = rr.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 rax WITH (NOLOCK) ON R.ReferenceNumber = rax.ReferenceNumber 
      AND R.GuestNumber = rax.GuestNumber
    INNER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber 
      AND AddressType = 1
    INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = Rooms.HotelNumber 
    WHERE 1=1
    AND R.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusStr))
    AND R.ReferenceNumber > 0
    AND ((R.GuestNumber = 0 AND rr.Adults+rr.Children1+rr.Children2 > 0) OR (R.GuestNumber > 0 AND R.PaysAdults+R.PaysChildren1+R.PaysChildren2 > 0))
    ORDER BY Rooms.Number
END

OPEN db_cur

FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestName, @GuestNumber, @HotelNumber, @RoomNumber, @RoomNumberKey, @ArrivalDate, @DepartureDate, @Adults, @Children1, @Children2, @Price, @TotalPrice, 
  @Day2, @Day3, @Day4, @ProductNumber, @OwnerProductNumber, @ProductNumber2, @OwnerProductNumber2, @ProductNumber3, @OwnerProductNumber3, @ProductNumber4, @OwnerProductNumber4, 
  @BookedTypeNumber, @AllocatedTypeNumber, @NoOfRooms, @Rooms, @osType, @osPercent, @OwnerCustomerNumber, @Link, @LineNumber, @Status, @OwnerDiscountRate, @HotelCustomerNumber,
  @OwnerProductDescription1, @OwnerProductDescription2, @OwnerProductDescription3, @OwnerProductDescription4

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @Adults = ISNULL(@Adults, -1)
  SET @Children1 = ISNULL(@Children1, -1)
  SET @Children2 = ISNULL(@Children2, -1)

  SET @ReservationDays = DATEDIFF(DAY, @ArrivalDate, @DepartureDate)
  SET @tmpDaysProduct1 = ISNULL(CONVERT(INT, (@ReservationDays/@TimeShareDays)*@TimeShareDays), 0)
  SET @tmpDaysProduct2 = ISNULL(@ReservationDays - @tmpDaysProduct1, 0)

  SET @tmpTotalProductPrice = 0
  SET @tmpTotalProductPrice2 = 0

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

  SET @OwnerWareNumber = 0

  IF @ShowPrint = 1
  BEGIN
    PRINT ''
    PRINT '*******************************************************************'
    PRINT '@TimeShareDays'
    PRINT @TimeShareDays
    PRINT '@ReservationDays'
    PRINT @ReservationDays
    PRINT '@tmpDaysProduct1'
    PRINT @tmpDaysProduct1
    PRINT '@tmpDaysProduct2'
    PRINT @tmpDaysProduct2
    PRINT 'ArrivalDate'
    PRINT CONVERT(CHAR(8), @ArrivalDate, 112)
    PRINT '@tmpDaysProduct1'
    PRINT @tmpDaysProduct1
    PRINT '@tmpDaysProduct2'
    PRINT @tmpDaysProduct2
  END

  IF ISNULL(@OwnerProductNumber, 0) <= 0
  BEGIN
    IF @tmpDaysProduct1 > 0
    BEGIN
      SET @ArrivalDateStr = CONVERT(CHAR(8), @ArrivalDate, 112)
      SET @DepartureDateStr = CONVERT(CHAR(8), @ArrivalDate+@tmpDaysProduct1-1, 112)
      SET @DepartureDateStrOneMore = CONVERT(CHAR(8), @ArrivalDate+@tmpDaysProduct1, 112)
 
      IF @ShowPrint = 1
      BEGIN
        PRINT '@ArrivalDateStr'
        PRINT @ArrivalDateStr
        PRINT '@DepartureDateStr'
        PRINT @DepartureDateStr
      END
 
 
      IF @OwnerCalculationUseAllocatedType = 1
        EXEC ProductPricePeriod @ArrivalDateStr, @DepartureDateStrOneMore, @Adults, @Children1, @Children2, @tmpTimeShareProduct1, @AllocatedTypeNumber, @ArrivalDateStr, @DepartureDateStr, 0, -1, -1, -1, @tmpTotalProductPrice OUTPUT
      ELSE
        EXEC ProductPricePeriod @ArrivalDateStr, @DepartureDateStrOneMore, @Adults, @Children1, @Children2, @tmpTimeShareProduct1, @BookedTypeNumber, @ArrivalDateStr, @DepartureDateStr, 0, -1, -1, -1, @tmpTotalProductPrice OUTPUT

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

    IF @tmpDaysProduct2 > 0
    BEGIN
      SET @ArrivalDateStr = CONVERT(CHAR(8), @ArrivalDate, 112)
      SET @DepartureDateStr = CONVERT(CHAR(8), @ArrivalDate+@tmpDaysProduct2-1, 112)
      SET @DepartureDateStrOneMore = CONVERT(CHAR(8), @ArrivalDate+@tmpDaysProduct2, 112)
 
      IF @ShowPrint = 1
      BEGIN
        PRINT '@ArrivalDateStr'
        PRINT @ArrivalDateStr
        PRINT '@DepartureDateStr'
        PRINT @DepartureDateStr
        PRINT '@Adults'
        PRINT @Adults
        PRINT '@Children1'
        PRINT @Children1
        PRINT '@Children2'
        PRINT @Children2
        PRINT '@tmpTimeShareProduct2'
        PRINT @tmpTimeShareProduct2
        PRINT '@BookedTypeNumber'
        PRINT @BookedTypeNumber
      END

      IF ISNULL(@tmpDaysProduct1, 0) = 0
        SET @InclusiveAddon = 1
      ELSE
        SET @InclusiveAddon = 0      


      IF @OwnerCalculationUseAllocatedType = 1
        EXEC ProductPricePeriod @ArrivalDateStr, @DepartureDateStrOneMore, @Adults, @Children1, @Children2, @tmpTimeShareProduct2, @AllocatedTypeNumber, @ArrivalDateStr, @DepartureDateStr, 0, -1, -1, -1, @tmpTotalProductPrice2 OUTPUT, @InclusiveAddon
      ELSE
        EXEC ProductPricePeriod @ArrivalDateStr, @DepartureDateStrOneMore, @Adults, @Children1, @Children2, @tmpTimeShareProduct2, @BookedTypeNumber, @ArrivalDateStr, @DepartureDateStr, 0, -1, -1, -1, @tmpTotalProductPrice2 OUTPUT, @InclusiveAddon

      IF @ShowPrint = 1
      BEGIN
        PRINT '@tmpTotalProductPrice2'
        PRINT CONVERT(VARCHAR, @tmpTotalProductPrice2)
      END
    END  
  END
  ELSE
  BEGIN
    -- user other owner product...
    SET @ArrivalDateStr = CONVERT(CHAR(8), @ArrivalDate, 112)
    SET @DepartureDateStr = CONVERT(CHAR(8), @DepartureDate-1, 112)
    SET @DepartureDateStrOneMore = CONVERT(CHAR(8), @DepartureDate, 112)

    IF @ShowPrint = 1
    BEGIN
      PRINT '@OwnerProductNumber'
      PRINT @OwnerProductNumber
      PRINT '@ArrivalDateStr'
      PRINT @ArrivalDateStr
      PRINT '@DepartureDateStr'
      PRINT @DepartureDateStr
    END  

    IF @Day2 > 0
    BEGIN
      -- Handle +product
      IF @OwnerCalculationUseAllocatedType = 1
        SET @TmpRoomType = @AllocatedTypeNumber
      ELSE
        SET @TmpRoomType = @BookedTypeNumber      
      
      SET @CurrentDate = @ArrivalDate
      
      WHILE @CurrentDate <= @DepartureDate
      BEGIN
        SET @TmpProductStartDate = @ArrivalDate
        SET @TmpProductEndDate = @ArrivalDate + @Day2-1
        
        IF @CurrentDate <= @TmpProductEndDate
          EXEC ProductPricesDay @OwnerProductNumber, @TmpProductStartDate, @TmpProductEndDate, @CurrentDate, @TmpRoomType, 1, @Adults, @Children1, @Children2, @Addon1 OUTPUT, @DummyStr OUTPUT, @DummyStr OUTPUT, @PriceProduct1 OUTPUT, @DummyInt OUTPUT, @DummyMoney OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, -1, 0

        IF @Day3 = 0
        BEGIN
          SET @TmpProductStartDate = CONVERT(DATETIME, @ArrivalDate) + @Day2-1 
          SET @TmpProductEndDate = CONVERT(DATETIME, @DepartureDate) 

          IF @CurrentDate >= @TmpProductStartDate
            EXEC ProductPricesDay @OwnerProductNumber2, @TmpProductStartDate, @TmpProductEndDate, @CurrentDate, @TmpRoomType, 1, @Adults, @Children1, @Children2, @Addon2 OUTPUT, @DummyStr OUTPUT, @DummyStr OUTPUT, @PriceProduct2 OUTPUT, @DummyInt OUTPUT, @DummyMoney OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, -1, 0
        END 
        ELSE
        BEGIN
          SET @TmpProductStartDate = CONVERT(DATETIME, @ArrivalDate) + @Day2-1 
          SET @TmpProductEndDate = CONVERT(DATETIME, @ArrivalDate) + @Day3-1 
          IF (@CurrentDate >= @TmpProductStartDate) AND (@CurrentDate <= @TmpProductEndDate)
            EXEC ProductPricesDay @OwnerProductNumber2, @TmpProductStartDate, @TmpProductEndDate, @CurrentDate, @TmpRoomType, 1, @Adults, @Children1, @Children2, @Addon2 OUTPUT, @DummyStr OUTPUT, @DummyStr OUTPUT, @PriceProduct2 OUTPUT, @DummyInt OUTPUT, @DummyMoney OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, -1, 0

          IF @Day4 = 0
          BEGIN
            SET @TmpProductStartDate = CONVERT(DATETIME, @ArrivalDate) + @Day3-1
            SET @TmpProductEndDate  = CONVERT(DATETIME, @DepartureDate)
            IF @CurrentDate >= @TmpProductStartDate
              EXEC ProductPricesDay @OwnerProductNumber3, @TmpProductStartDate, @TmpProductEndDate, @CurrentDate, @TmpRoomType, 1, @Adults, @Children1, @Children2, @Addon3 OUTPUT, @DummyStr OUTPUT, @DummyStr OUTPUT, @PriceProduct3 OUTPUT, @DummyInt OUTPUT, @DummyMoney OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, -1, 0
          END 
          ELSE 
          BEGIN
            SET @TmpProductStartDate = CONVERT(DATETIME, @ArrivalDate) + @Day3-1 
            SET @TmpProductEndDate = CONVERT(DATETIME, @ArrivalDate) + @Day4-1				
            IF (@CurrentDate >= @TmpProductStartDate) AND (@CurrentDate <= @TmpProductEndDate) 
              EXEC ProductPricesDay @OwnerProductNumber3, @TmpProductStartDate, @TmpProductEndDate, @CurrentDate, @TmpRoomType, 1, @Adults, @Children1, @Children2, @Addon3 OUTPUT, @DummyStr OUTPUT, @DummyStr OUTPUT, @PriceProduct3 OUTPUT, @DummyInt OUTPUT, @DummyMoney OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, -1, 0

            SET @TmpProductStartDate = CONVERT(DATETIME, @ArrivalDate) + @Day4-1 
            SET @TmpProductEndDate = CONVERT(DATETIME, @DepartureDate) 
            IF (@CurrentDate >= @TmpProductStartDate) AND (@CurrentDate <= @TmpProductEndDate) 					
              EXEC ProductPricesDay @OwnerProductNumber4, @TmpProductStartDate, @TmpProductEndDate, @CurrentDate, @TmpRoomType, 1, @Adults, @Children1, @Children2, @Addon4 OUTPUT, @DummyStr OUTPUT, @DummyStr OUTPUT, @PriceProduct4 OUTPUT, @DummyInt OUTPUT, @DummyMoney OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, @DummyBit OUTPUT, -1, 0
          END 
        END 

        SET @tmpTotalProductPrice = @tmpTotalProductPrice + ISNULL(@PriceProduct1, 0) + ISNULL(@PriceProduct2, 0) + ISNULL(@PriceProduct3, 0) + ISNULL(@PriceProduct4, 0) +
          ISNULL(@Addon1, 0) + ISNULL(@Addon2, 0) + ISNULL(@Addon3, 0) + ISNULL(@Addon4, 0)
        SET @CurrentDate = DATEADD(DAY,1,@CurrentDate)

        IF @ShowPrint = 1
        BEGIN
          PRINT '@tmpTotalProductPrice'
          PRINT CONVERT(VARCHAR, @tmpTotalProductPrice)
        END
      END      
    END
    ELSE
    BEGIN   
      IF @OwnerCalculationUseAllocatedType = 1
        EXEC ProductPricePeriod @ArrivalDateStr, @DepartureDateStrOneMore, @Adults, @Children1, @Children2, @OwnerProductNumber, @AllocatedTypeNumber, @ArrivalDateStr, @DepartureDateStr, 0, -1, -1, -1, @tmpTotalProductPrice OUTPUT
      ELSE
        EXEC ProductPricePeriod @ArrivalDateStr, @DepartureDateStrOneMore, @Adults, @Children1, @Children2, @OwnerProductNumber, @BookedTypeNumber, @ArrivalDateStr, @DepartureDateStr, 0, -1, -1, -1, @tmpTotalProductPrice OUTPUT
    END
  END
  
  -- Calc Product
  SET @TotalProductPrice = ISNULL(@tmpTotalProductPrice, 0) + ISNULL(@tmpTotalProductPrice2, 0)
  
  IF @osPercent IS NULL  -- needed to ensure that hotels with no percent still works
    SET @osPercent = 100
  IF @osPercent > 0
    SET @NettoPrice = (@TotalProductPrice * @osPercent / 100)
    
  SET @DiscoutTxt = ''
  IF @NettoPrice > 0 AND @OwnerDiscountRate > 0
  BEGIN
    SET @DiscoutTxt = CAST(@OwnerDiscountRate AS VARCHAR(2)) + '% Rabat'
    SET @NettoPrice = @NettoPrice - ((@NettoPrice * @OwnerDiscountRate) / 100)
  END

  SET @DepartureDateStr = CONVERT(CHAR(8), @DepartureDate, 112)

  -- 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, @ArrivalDateStr, @DepartureDateStr)
  ELSE  
    SELECT @PriceEnvironment = Amount, @EnvironmentPieces = Pieces FROM DBO.GetFolioAmountByWareNumber (@ReferenceNumber, @GuestNumber, -1, @EnvironmentWareNumber, @ArrivalDateStr, @DepartureDateStr)
  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, @ArrivalDateStr, @DepartureDateStr)
  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 ('OwnerElectricityUseFromWare', @HotelCustomerNumber)
  
  IF ISNULL(@OwnerAdditionalUseFromWare, 0) = 1
    SELECT @PriceAdditional = Amount, @AdditionalPieces = Pieces FROM DBO.GetFolioAmountByWareType (@ReferenceNumber, @GuestNumber, -1, 6, @ArrivalDateStr, @DepartureDateStr)
  ELSE  
    SELECT @PriceAdditional = Amount, @AdditionalPieces = Pieces FROM DBO.GetFolioAmountByWareNumber (@ReferenceNumber, @GuestNumber, -1, @AdditionalWareNumber, @ArrivalDateStr, @DepartureDateStr)
  IF ISNULL(@PriceAdditional, 0) > 0
    SET @NettoPriceAdditional = (@PriceAdditional * @AdditionalAccountRate / 100)

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

  SET @PAX = SUM(ISNULL(@Adults, 0) + ISNULL(@Children1, 0) + ISNULL(@Children2, 0))

  SELECT @AddAdditionalText = dbo.GetHotelSetupKeyValue ('OwnerPartAddAdditionalText', @HotelCustomerNumber)

    -- Get OwnerWareNumber
  IF @OwnerPartAsProduct = 1
  BEGIN
    EXEC OwnerGetOwnerWareNumber @ProductNumber, 0, @OwnerWareNumber OUTPUT

    SELECT @tmpOwnerWareText = WareName 
    FROM Ware (NOLOCK) 
    WHERE Type = 5  --wtOwner
    AND ISNULL(MarkAsDeleted, 0) = 0
  END
  ELSE
  BEGIN
    SELECT @OwnerWareNumber = Ware.WareNumber, @tmpOwnerWareText = Ware.WareName
    FROM Ware WITH (NOLOCK) 
    INNER JOIN WareRoomTypeXTab WITH (NOLOCK) ON WareRoomTypeXTab.FK_WareNumber = Ware.WareNumber
      AND WareRoomTypeXTab.FK_RoomTypeNumber = @AllocatedTypeNumber
    INNER JOIN WareDepartmentsXtab WITH (NOLOCK) ON WareDepartmentsXtab.WareNumber = Ware.WareNumber
      AND WareDepartmentsXtab.Type = 1 -- Cashier Department
    INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareDepartmentsXtab.FK_Department
      AND Departments.HotelNumber = @HotelNumber
    WHERE Ware.Type = 5  --wtOwner
    AND ISNULL(Ware.MarkAsDeleted, 0) = 0

    IF ISNULL(@OwnerWareNumber, 0) <= 0
    BEGIN
      SELECT @OwnerWareNumber = Ware.WareNumber, @tmpOwnerWareText = Ware.WareName
      FROM Ware WITH (NOLOCK) 
      INNER JOIN WareDepartmentsXtab WITH (NOLOCK) ON WareDepartmentsXtab.WareNumber = Ware.WareNumber
        AND WareDepartmentsXtab.Type = 1 -- Cashier Department
      INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = WareDepartmentsXtab.FK_Department
        AND Departments.HotelNumber = @HotelNumber
      WHERE Ware.Type = 5  --wtOwner
      AND ISNULL(Ware.MarkAsDeleted, 0) = 0
    END
  END

  IF ISNULL(@AddAdditionalText, 0) > 0
  BEGIN
    IF @AddAdditionalText = 1
    BEGIN
      IF ISNULL(@Adults, 0) <= 6
        SET @OwnerWareText1 = @tmpOwnerWareText + ' D6'
      ELSE IF ISNULL(@Adults, 0) > 6
        SET @OwnerWareText1 = @tmpOwnerWareText + ' D8'
      ELSE
        SET @OwnerWareText1 = @tmpOwnerWareText
    END
    ELSE IF @AddAdditionalText = 2
    BEGIN
      IF @PAX <= 4
        SET @OwnerWareText1 = @tmpOwnerWareText + ' 1-4 pers'
      ELSE IF @PAX <= 6 
        SET @OwnerWareText1 = @tmpOwnerWareText + ' 5-6 pers'
      ELSE IF @PAX <= 7 
        SET @OwnerWareText1 = @tmpOwnerWareText + ' 7 pers'
      ELSE IF @PAX > 7 
        SET @OwnerWareText1 = @tmpOwnerWareText + ' 8 pers'      
    END
    ELSE IF @AddAdditionalText = 3
    BEGIN
      SET @s = CAST(ISNULL(@Adults, 0) AS VARCHAR)  + ' voksne'
      IF ISNULL(@Children1, 0) + ISNULL(@Children2, 0) > 0
        SET @s = @s + ' ' + CAST(ISNULL(@Children1, 0) AS VARCHAR) + ' børn'
      SET @OwnerWareText1 = @tmpOwnerWareText + ' ' + @s
    END
  END
  
  IF @AddPeriodText = 1
    SET @OwnerWareText = @tmpOwnerWareText + ' (' + CONVERT(CHAR(10), @ArrivalDate, 104) + ' - ' + CONVERT(CHAR(10), @DepartureDate, 104)+')'
  IF @DiscoutTxt <> ''
    SET @OwnerWareText = @OwnerWareText + ' ' + @DiscoutTxt

  IF @AAddToFolio = 1
  BEGIN
    SET @EnvironmentWareTextEx = @EnvironmentWareText
    SET @CleaningWareTextEx = @CleaningWareText
    SET @AdditionalWareTextEx = @AdditionalWareText
    
    IF @OwnerConsumption = 1
    BEGIN
      SET @EnvironmentWareTextEx = @EnvironmentWareText + ' (' + CONVERT(VARCHAR, @EnvironmentPieces) + ' '+ @OwnerElectricityWareText + ')'
      SET @CleaningWareTextEx = @CleaningWareText + ' (' + CONVERT(VARCHAR, @CleaningPieces) + ' ' + @OwnerCleaningWareText + ')'
      SET @AdditionalWareTextEx = @AdditionalWareText + ' (' + CONVERT(VARCHAR, @AdditionalPieces) + ' ' + @OwnerAdditionalWareText + ')'
    END

    DECLARE @tmp INT
    
    IF @AOnlyGetNettoPrice = 1
      SET @ANettoPrice = @NettoPrice
    ELSE
      EXEC OwnerAddToFolio @ReferenceNumber, @GuestNumber, @OwnerCustomerNumber, @HotelNumber, @AllocatedTypeNumber,
        @OwnerWareNumber, @OwnerWareText, @AUserIdNumber, @NettoPrice, 
        @AdditionalWareNumber, @AdditionalWareTextEx, @NettoPriceAdditional, 
        @EnvironmentWareNumber, @EnvironmentWareTextEx, @NettoPriceEnvironment,
        @CleaningWareNumber, @CleaningWareTextEx, @NettoPriceCleaning, @RoomNumberKey,
        -1, '', 0,
        -1, '', 0, 0, 
        -1, '', 0, @tmp
  END
  ELSE
  BEGIN
    IF ISNULL(@Link, 0) = @OwnerCustomerNumber
    BEGIN
      SET @TotalProductPrice = 0
      SET @NettoPriceEnvironment = 0
      SET @NettoPriceCleaning = 0
      SET @NettoPriceAdditional = 0
      SET @TotalAllPrice = 0 
      SET @NettoPrice = 0
      SET @TotalPrice = 0
    END
    
    SET @TotalAllPrice = SUM(ISNULL(@NettoPrice, 0) + ISNULL(@NettoPriceEnvironment, 0) + ISNULL(@NettoPriceCleaning, 0) + ISNULL(@NettoPriceAdditional, 0))
    
    INSERT INTO #Reservations (Referencenumber, GuestName, GuestNumber, HotelNumber, ReservationArrival, ReservationDeparture, Pax, Adults, Children1,
      Children2, ResTotalPrice, ElectricityPrice, CleaningPrice, AdditionalPrice, TotalPrice, NettoPrice, RoomNumber, ResTurnover, OwnerPercent, AdditionalText, OwnerWareText,
	  OwnerProductDescription1, OwnerProductDescription2, OwnerProductDescription3, OwnerProductDescription4)
    VALUES (@ReferenceNumber, @GuestName, @GuestNumber, @HotelNumber, @ArrivalDate, @DepartureDate, @Pax, @Adults, @Children1, 
      @Children2, @TotalProductPrice, @NettoPriceEnvironment, @NettoPriceCleaning, @NettoPriceAdditional, @TotalAllPrice, @NettoPrice, @RoomNumber, @TotalPrice, @osPercent, @OwnerWareText1, @OwnerWareText,
	  @OwnerProductDescription1, @OwnerProductDescription2, @OwnerProductDescription3, @OwnerProductDescription4)
  END

  FETCH NEXT FROM db_cur INTO @ReferenceNumber, @GuestName, @GuestNumber, @HotelNumber, @RoomNumber, @RoomNumberKey, @ArrivalDate, @DepartureDate, @Adults, @Children1, @Children2, @Price, @TotalPrice, 
    @Day2, @Day3, @Day4, @ProductNumber, @OwnerProductNumber, @ProductNumber2, @OwnerProductNumber2, @ProductNumber3, @OwnerProductNumber3, @ProductNumber4, @OwnerProductNumber4, 
    @BookedTypeNumber, @AllocatedTypeNumber, @NoOfRooms, @Rooms, @osType, @osPercent, @OwnerCustomerNumber, @Link, @LineNumber, @Status, @OwnerDiscountRate, @HotelCustomerNumber,
	@OwnerProductDescription1, @OwnerProductDescription2, @OwnerProductDescription3, @OwnerProductDescription4
END

CLOSE db_cur
DEALLOCATE db_cur

IF @AAddToFolio = 0
BEGIN
  SELECT * FROM #Reservations WITH (NOLOCK)
  ORDER BY RoomNumber, ReservationArrival

  DROP TABLE #Reservations
END
ELSE IF @APostTicketUsage = 1
BEGIN
  IF @ShowPrint = 1
    PRINT 'Post brugsret BEGIN'
  
  DECLARE db_cur1 CURSOR LOCAL FOR
    SELECT Rooms.HotelNumber, Rooms.Number, Rooms.RoomNumber,
    Rooms.RoomTypeNumber,
    Rooms.OwnerSettlementType, Rooms.OwnerSettlementPercent, Rooms.CustomerNumber
    FROM Rooms WITH (NOLOCK)
    WHERE ((@AHotelNumber = -1) OR (Rooms.HotelNumber = @AHotelNumber))
    AND ISNULL(CustomerNumber, 0) > 0
  OPEN db_cur1
  
  FETCH NEXT FROM db_cur1 INTO @HotelNumber, @RoomNumber, @RoomNumberKey, @BookedTypeNumber, @osType, @osPercent, @OwnerCustomerNumber
    
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @ShowPrint = 1
      PRINT 'Post for RoomNumber: ' + CONVERT(VARCHAR, @RoomNumberKey)
    
    -- Get Usage fee
    DECLARE #usageFeeCur CURSOR LOCAL FOR
      SELECT DISTINCT RecurringPatternWareXTab.FK_WareNumber, Ware.WareName, Ware.Price
      FROM RecurringPatternSpecified WITH (NOLOCK)
      INNER JOIN RecurringPatternWareXTab WITH (NOLOCK) ON RecurringPatternWareXTab.FK_RecurringPatternID = RecurringPatternSpecified.FK_RecurringPatternID
      INNER JOIN RecurringRule WITH (NOLOCK) ON RecurringPatternSpecified.FK_RecurringPatternID = RecurringRule.FK_RecurringPatternID
        AND RecurringRule.RuleType = 26 -- rtOwnerPayment
      INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = RecurringPatternWareXTab.FK_WareNumber
      INNER JOIN WareRoomXTab WITH (NOLOCK) ON WareRoomXTab.FK_WareNumber = Ware.WareNumber
      INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = WareRoomXTab.FK_RoomNumber
        AND ISNULL(Rooms.CustomerNumber, -1) = @OwnerCustomerNumber
      WHERE RecurringPatternSpecified.StartDateTime = CONVERT(CHAR(8), @AStartDateTime, 112)
      AND RecurringPatternSpecified.Deleted = 0
    OPEN #usageFeeCur
    
    FETCH NEXT FROM #usageFeeCur INTO @UsageFeeWareNumber, @UsageFeeWareText, @UsageFeePrice
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC OwnerAddToFolio NULL, NULL, @OwnerCustomerNumber, @HotelNumber, -1,
        -1, '', @AUserIdNumber, 0, 
        -1, '', 0, 
        -1, '', 0,
        -1, '', 0, @RoomNumberKey,
        @UsageFeeWareNumber, @UsageFeeWareText, @UsageFeePrice,
        -1, '', 0, 0, 
        -1, '', 0, 
        @tmp

      FETCH NEXT FROM #usageFeeCur INTO @UsageFeeWareNumber, @UsageFeeWareText, @UsageFeePrice
    END
    CLOSE #usageFeeCur
    DEALLOCATE #usageFeeCur
   
    -- Get ticket fee info
    IF ISNULL(@TicketFeePrice, 0) <> 0
    BEGIN
      SET @LastOwnerChange = NULL
    
      SELECT @LastOwnerChange = f.Date FROM Followup f WITH (NOLOCK)
      INNER JOIN FollowupRoomsXTab frx WITH (NOLOCK) ON frx.FK_FollowupNumber = f.FollowupNumber
        AND frx.FK_RoomNumber = @RoomNumberKey
      WHERE Type = 20
      AND Date > @LastTicketFeePostRecur    

      IF @LastOwnerChange > @LastTicketFeePostRecur
        SET @LastTicketFeePost = @LastOwnerChange
      ELSE
        SET @LastTicketFeePost = @LastTicketFeePostRecur

      DECLARE @DoPostTicketFee INT
      SELECT @DoPostTicketFee = COUNT(*)
      FROM Rooms WITH(NOLOCK)
      INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
      INNER JOIN WareRoomTypeXTab WITH (NOLOCK) ON WareRoomTypeXTab.FK_RoomTypeNumber = RoomTypes.RoomTypeNumber
        AND WareRoomTypeXTab.FK_WareNumber = @TicketFeeWareNumber
      WHERE Rooms.RoomNumber = @RoomNumberKey
      
      IF ISNULL(@DoPostTicketFee, 0) > 0
        EXEC OwnerPostTicketFee @LastTicketFeePost, @AEndDateTime, @RoomNumberKey, @TicketFeeWareNumber, @TicketFeeWareText, @TicketFeePrice,
          NULL, NULL, @OwnerCustomerNumber, @HotelNumber, @OwnerWareNumber, @AUserIdNumber 
    END

    FETCH NEXT FROM db_cur1 INTO @HotelNumber, @RoomNumber, @RoomNumberKey, @BookedTypeNumber, @osType, @osPercent, @OwnerCustomerNumber
  END      

  IF @ShowPrint = 1
    PRINT 'Post brugsret END'
    
  CLOSE db_cur1
  DEALLOCATE db_cur1
END