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

/*

  Created by Kent Johansen November 2006
  Returns: Reservations/transactions for selected period with Owner part.

  TODO:
    - Support for +products
    - Support for bookkeeping report by POS, Segments, Rooms/Halls and VatValue

  Changes:
    KJ 20160502: Bug: Fix for "some changes for Alpen Travel calculation".
    KJ 20150908: New: Alpen travel owner calculation req #2725
    KJ 20150817: New: Alpen travel owner calculation req #2725
    KJ 20130506: Bug: Fix for "Wrong account incl/excl VAT". Case #CR055013
    KJ 20130102: Ext: More settings per hotel. Req #1413
    KJ 20120222: Ext: Poosible to setup ownerware account number per hotel. Req #1163+1162
    KJ 20111018: Ext: Added Owner discount Req #1008.
    KJ 20110726: Bug: Fix for "Missing space between ref and date". Case# CR026136
    KJ 20110624: Bug: Fix for "Wrong format of date". Case# CR024969
    KJ 20110512: Ext: Added owner period text Req# 899
    KJ 20110420: Ext: Added replace owner ware text. Req# 892
    KJ 20110308: Bug: Fix for "Cleaning price not initialized." + "Only add meter info if any"
    KJ 20110203: Ext: "Added Cleaning owner ware" Case# CR019267
    KJ 20110201: Bug: Fix for "Environment amount wrong". Case# CR019267
    KJ 20110127: Bug: Fix for "Invalid column name ExtraDepartureDateStr"
    KJ 20110113: Bug: Environment text wrong. 
    KJ 20110110: Ext: Environment calculated by using the hotelsetup percentage
    KJ 20110105: Ext: Changed environment rate to 2.40
    KJ 20101221: Ext: Added roomnumber for reservation address update
    KJ 20100920: Added AOwnertype (Logi, OwnerElectricity, OwnerCleaning, OwnerAdditional)
    KJ 20100903: Bug: Fix for "Missing parameter"
    KJ 20100831: Ext: Added FolioNumber as output parameter on OwnerAddToFolio
    KJ 20100827: Bug: Fix for "Missing paramter"
    KJ 20100218: Bug: Meter start and end wrong if multiple charges of environment
    KJ 20090928: Bug: Fix for "Post owner part as product is the default"
    KJ 20090902: Ext: case #400. Owner calculation to Vigsų
    KJ 20090401: Bug: Cannot insert the value NULL into coloumn WARENUMBER in table danparcspicasso.dbo,transactions. Coloumn does not allow NULLS"
    KJ 20090120: Bug: Handle for same roomnumber on multiple hotels.
    KJ 20081230: Ext: Added meter values to environment posting
    KJ 20081219: Bug: Fix for 'somehow the cursor select acted strange - had to copy the hole select statement'
    KJ 20080225: Optimized handle for RoomNumberStr = -1
    KJ 20071112: Showprint = 0
    KJ 20071109: Added cleaning to OwnerAddToFolio
    KJ 20070621: HotelNumber with default value.
    KJ 20070530: Added HotelNumber as input param
    KJ 20070502: Getting guestnumber from reservations.
    KJ 20070502: Get the environment/additional amount on all folios - also changed in GetFolioAmountByWareNumber
    KJ 20070502: Getting Environment ware from hotelsetup.
    KJ 20070501: Bug: Fix for "Only include not MovedDeleted"
    KJ 20070501: Bug: Fix for Handle moveddeleted logi trans
    KJ 20070501: Bug: Fix for @ARoomNumberStr IS NULL
    KJ 20070430: A little cleanup
    KJ 20070427: Bug: Fix for mark ownerwarenumber as OUTPUT param.
    KJ 20070125: Optimized. The join with ReservationAddressXTab and ReservationAddress removed. Getting the address later if needed.
    KJ 20070125: Only return result if not AddToFolio
    KJ 20070121: Optimized
    KJ 20070111: Get @OwnerWareNumber from the product according to hotelsetup setting - OwnerPartAsProduct
    KJ 20070110: Changed the way to get EnvironmentFee ware
    KJ 20070103: Added Environment Fee
    KJ 20070102: Added Additional ware
    KJ 20061228: Moved add to folio stuff to seperate sp
    KJ 20061211: Pax/Adults/Child1/Child2 wrong
    KJ 20062108: Many changes made to the CreateFolio stuff.
    KJ 20061204: Added CreateFolio as input param.

*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

IF @ShowPrint = 1
  PRINT 'OwnerReportReservationsByTransactions'

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, 
    TotalPrice MONEY NULL,
    CleaningPrice MONEY NULL, 
    PackagePrice MONEY NULL,
    OwnerPercent INT NULL,
    NettoPrice MONEY NULL,
    RoomNumber INT NULL,
    ReservationStatus INT NULL
  )
END

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

IF @ARoomNumberStr <> '-1'
BEGIN
  DECLARE @NewDepartureDate VARCHAR(8)
  SET @NewDepartureDate = CONVERT(VARCHAR(8), @AEndDateTime + 1, 112)

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

  DECLARE @UseOwnerOwnCalc BIT
  SELECT @UseOwnerOwnCalc = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'OwnerOwnUsePercentEnable'
  SET @UseOwnerOwnCalc = ISNULL(@UseOwnerOwnCalc, 0)

  DECLARE @UseOwnerReferralCalc BIT
  SELECT @UseOwnerReferralCalc = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'OwnerReferralPercentEnable'
  SET @UseOwnerReferralCalc = ISNULL(@UseOwnerReferralCalc, 0)

  DECLARE @ReferenceNumber INT
  DECLARE @GuestName VARCHAR(100)
  DECLARE @FirstName VARCHAR(100)
  DECLARE @LastName 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 @SpecArrivalDate DATETIME 
  DECLARE @SpecDepartureDate DATETIME
  DECLARE @SpecAdults INT
  DECLARE @SpecChildren1 INT
  DECLARE @SpecChildren2 INT
  DECLARE @SpecPrice MONEY
  DECLARE @SpecTotalPrice MONEY
  DECLARE @SpecProductNumber INT
  DECLARE @SpecProductNumber2 INT
  DECLARE @SpecProductNumber3 INT
  DECLARE @SpecProductNumber4 INT
  DECLARE @SpecDay2 INT
  DECLARE @SpecDay3 INT
  DECLARE @SpecDay4 INT
  DECLARE @SpecPriceOverwritten BIT
  DECLARE @SpecPriceOverwritten2 BIT
  DECLARE @SpecPriceOverwritten3 BIT
  DECLARE @SpecPriceOverwritten4 BIT
  DECLARE @SpecFirstPrice1 MONEY
  DECLARE @SpecFirstPrice2 MONEY
  DECLARE @SpecFirstPrice3 MONEY
  DECLARE @SpecFirstPrice4 MONEY
  DECLARE @BookedTypeNumber INT
  DECLARE @AllocatedTypeNumber INT
  DECLARE @NoOfRooms INT

  DECLARE @osType INTEGER
  DECLARE @osPercent FLOAT
  DECLARE @osPercentOwn FLOAT
  DECLARE @osPercentReferral FLOAT
  DECLARE @osPercentCalc FLOAT
  DECLARE @Status INT
  DECLARE @WithOutVat MONEY
  DECLARE @VatSum MONEY 
  DECLARE @TotalWithVat MONEY

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

  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 @sql NVARCHAR(1000)

  DECLARE @ExtraDepartureDateStr CHAR(8)

  DECLARE @OwnerCustomerNumber INT
  DECLARE @OwnerWareNumber INT
  DECLARE @OwnerWareText VARCHAR(50)
  DECLARE @tmpOwnerWareText VARCHAR(50)
  DECLARE @LogiFolioNumber INT

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

  DECLARE @EnvironmentWareNumber INT
  DECLARE @EnvironmentWareText VARCHAR(50)
  DECLARE @tmpEnvironmentWareText VARCHAR(50)
  DECLARE @PriceEnvironment MONEY
  DECLARE @NettoPriceEnvironment MONEY

  DECLARE @tmpRateStr VARCHAR(50)
  DECLARE @EnvironmentAccountRate REAL

  DECLARE @ProductTypeNumber INT
  DECLARE @OwnerPartAsProduct BIT
  
  DECLARE @RoomNumberKey INT
  DECLARE @RoomNumberKeyReferral INT
  
  DECLARE @CleaningWareNumber INT
  DECLARE @CleaningWareText VARCHAR(50)
  DECLARE @CleaningWareTextEx VARCHAR(50)
  DECLARE @AdditionalWareTextEx VARCHAR(50)
  DECLARE @CleaningAccountRate REAL
  DECLARE @PriceCleaning MONEY
  DECLARE @NettoPriceCleaning MONEY  

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

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

  DECLARE @OwnerElectricityReplaceWareText BIT
  DECLARE @OwnerCleaningReplaceWareText BIT
  DECLARE @OwnerAdditionalReplaceWareText BIT
  
  DECLARE @AddPeriodText BIT
  DECLARE @OwnerDiscountRate SMALLMONEY
  DECLARE @DiscoutTxt VARCHAR(20)
  
  DECLARE @HotelCustomerNumber INT
  DECLARE @AgentCustomerNumber INT

  DECLARE @OwnerCreditCardFeeWareText VARCHAR(100)
  DECLARE @OwnerCreditCardFeeReplaceWareText BIT
  DECLARE @CreditCardFeeAccountRate REAL
  DECLARE @CreditCardFeeWareNumber INT
  DECLARE @CreditCardFeeWareText VARCHAR(100)
  DECLARE @NettoPriceCreditCardFee MONEY
  DECLARE @OwnerCreditCardFeeRoomCSV VARCHAR(8000)
  DECLARE @cnt INT

  DECLARE @NettoPriceTicketFee MONEY
  DECLARE @TicketFeeWareNumber INT 
  DECLARE @TicketFeePerRes BIT
  DECLARE @TicketFeePrice MONEY
  DECLARE @TicketFeeWareText VARCHAR(100)
  DECLARE @TicketFeeRoomCSV VARCHAR(8000)

  DECLARE @OwnerUseSettlementType BIT
  DECLARE @RoomNumberStr VARCHAR(10)

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

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

  SELECT @OwnerWareNumber = WareNumber, @tmpOwnerWareText = WareName 
  FROM Ware (NOLOCK) 
  WHERE Type = 5  --wtOwner
  AND ISNULL(MarkAsDeleted, 0) = 0

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

  SELECT @TicketFeeWareNumber = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OwnerTicketFeeWareNumber'
  SELECT DISTINCT @TicketFeeWareText = Ware.WareName, @TicketFeePrice = Ware.Price
  FROM Ware WITH (NOLOCK)
  WHERE Ware.WareNumber = @TicketFeeWareNumber

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

  SET @AHotelNumber = ISNULL(@AHotelNumber, -1)

  IF @AAddToFolio = 1
  BEGIN
  DECLARE db_cur CURSOR LOCAL FOR
    SELECT DISTINCT 
    Transactions.FolioNumber,
    Folios.ReferenceNumber, 
    Reservations.GuestNumber,
    ReservationMain.HotelNumber,
    Rooms.Number, Rooms.RoomNumber,
    ReservationRoomLine.ArrivalDate, Reservations.ArrivalDate AS SpecArrivalDate,
    ReservationRoomLine.DepartureDate, Reservations.DepartureDate AS SpecDepartureDate,
    ReservationRoomLine.Adults, Reservations.Adults AS SpecAdults,  
    ReservationRoomLine.Children1, Reservations.Children1 AS SpecChildren1,  
    ReservationRoomLine.Children2, Reservations.Children2 AS SpecChildren2,
    ReservationRoomLine.Price, Reservations.Price AS SpecPrice,  
    ReservationRoomLine.TotalPrice, Reservations.TotalPrice AS SpecTotalPrice,  
    ReservationRoomLine.ProductNumber, Reservations.ProductNumber AS SpecProductNumber,  
    ReservationRoomLine.ProductNumber2, Reservations.ProductNumber2 AS SpecProductNumber2,  
    ReservationRoomLine.ProductNumber3, Reservations.ProductNumber3 AS SpecProductNumber3,  
    ReservationRoomLine.ProductNumber4, Reservations.ProductNumber4 AS SpecProductNumber4,  
    ReservationRoomLine.Day2, Reservations.Day2 AS SpecDay2,  
    ReservationRoomLine.Day3, Reservations.Day3 AS SpecDay3,  
    ReservationRoomLine.Day4, Reservations.Day4 AS SpecDay4,  
    ReservationRoomLine.PriceOverwritten, Reservations.PriceOverwritten AS SpecPriceOverwritten,  
    ReservationRoomLine.PriceOverwritten2, Reservations.PriceOverwritten2 AS SpecPriceOverwritten2,  
    ReservationRoomLine.PriceOverwritten3, Reservations.PriceOverwritten3 AS SpecPriceOverwritten3,  
    ReservationRoomLine.PriceOverwritten4, Reservations.PriceOverwritten4 AS SpecPriceOverwritten4,  
    ReservationRoomLine.FirstPrice1, Reservations.FirstPrice1 AS SpecFirstPrice1,  
    ReservationRoomLine.FirstPrice2, Reservations.FirstPrice2 AS SpecFirstPrice2,  
    ReservationRoomLine.FirstPrice3, Reservations.FirstPrice3 AS SpecFirstPrice3,  
    ReservationRoomLine.FirstPrice4, Reservations.FirstPrice4 AS SpecFirstPrice4,  
    ReservationRoomLine.BookedTypeNumber,
    Rooms.RoomTypeNumber,
    Rooms.OwnerSettlementType, Rooms.OwnerSettlementPercent, Rooms.OwnerSettlementPercentOwnUse, Rooms.CustomerNumber,
    ReservationRoomLineXTab.LineNumber,
    Reservations.Status,
    SUM(TransactionsVATSum.PriceOverwrite * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END)) AS SumWithoutVAT,
    SUM(TransactionsVAT.PriceOverwrite * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END)) AS VATSUM,
    SUM((TransactionsVATSum.PriceOverwrite + TransactionsVAT.PriceOverwrite) * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END)) AS ProductPrice,
    CustHot.CustomerNumber
    FROM Transactions (NOLOCK)
    INNER JOIN Ware (NOLOCK) ON Transactions.WareNumber = Ware.WareNumber
    INNER JOIN WareGroups (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber 
      AND WareGroups.Payment in (2,3,7,8)
    INNER JOIN Accounts (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
    INNER JOIN Transactions AS TransactionsVATSum (NOLOCK) ON TransactionsVATSum.GlobalTransactionNumber = Transactions.GlobalTransactionNumber 
    INNER JOIN Accounts AS AccountsVATSum (NOLOCK) ON AccountsVATSum.AccountNumber = TransactionsVATSum.AccountNumber
    INNER JOIN Transactions AS TransactionsVAT (NOLOCK) ON TransactionsVAT.GlobalTransactionNumber = Transactions.GlobalTransactionNumber  
    INNER JOIN Accounts AS AccountsVAT (NOLOCK) ON AccountsVAT.AccountNumber = TransactionsVAT.AccountNumber
    INNER JOIN Rooms (NOLOCK) ON Convert(Int,Transactions.Note) = Rooms.Number 
      AND ISNULL(OwnerSettlementType, 0) > 0  
      AND ((@ARoomNumberStr = '') OR (Rooms.Number IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberStr)))) 
      AND ((@AHotelNumber = -1) OR (Rooms.HotelNumber = @AHotelNumber))
    INNER JOIN Customer (NOLOCK) ON Customer.CustomerNumber = Rooms.CustomerNumber
    INNER JOIN Folios (NOLOCK) ON Transactions.Folionumber = Folios.FolioNumber 
      AND (((Transactions.Movedfolionr is null) OR (Transactions.Movedfolionr=0)) OR ((Transactions.Movedfolionr>0)))
    RIGHT OUTER JOIN Reservations (NOLOCK) on Reservations.ReferenceNumber = Folios.ReferenceNumber 
      AND Reservations.GuestNumber = Transactions.GuestNumber  
      AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusStr)) 
      AND Reservations.ReferenceNumber > 0
    LEFT OUTER JOIN ReservationRoomLineXTab (NOLOCK) on Reservations.ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber 
      AND Reservations.GuestNumber = ReservationRoomLineXTab.GuestNumber
    LEFT OUTER JOIN RESERVATIONROOMLINE (NOLOCK) ON RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
    LEFT OUTER JOIN ReservationMain (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber 
      AND ReservationType <= 7
      AND Rooms.HotelNumber = ReservationMain.HotelNumber
    INNER JOIN Countries (NOLOCK) ON  Customer.CountryNumber = Countries.CountryNumber
    INNER JOIN Customer CustHot WITH (NOLOCK) ON CustHot.HotelNumber = Rooms.HotelNumber
    WHERE Transactions.MovedDeleted = 0
    AND ((Transactions.FolioNumber= Folios.FolioNumber) AND TransactionsVATSum.FolioNumber = Folios.FolioNumber AND TransactionsVAT.FolioNumber = Folios.FolioNumber 
    AND ((Transactions.ShowOnBillFlag=1) and ((Transactions.Payment=0 AND AccountsVATSum.AccountType = 3 AND AccountsVAT.AccountType = 2))))
    AND Reservations.DepartureDate = CONVERT(VARCHAR(8), @AStartDateTime, 112)
    AND TransactionsVATSum.MovedDeleted = 0
    AND TransactionsVAT.MovedDeleted = 0
    AND ISNULL(ReservationMain.HotelNumber, -2) > -2
    GROUP BY 
      Transactions.FolioNumber,
      Folios.ReferenceNumber, 
      Folios.GuestNumber, Reservations.GuestNumber,
      ReservationMain.HotelNumber,
      Rooms.Number, Rooms.RoomNumber,
      ReservationRoomLine.ArrivalDate, Reservations.ArrivalDate,
      ReservationRoomLine.DepartureDate, Reservations.DepartureDate,
      ReservationRoomLine.ArrivalDate, Reservations.ArrivalDate,
      ReservationRoomLine.DepartureDate, Reservations.DepartureDate,
      ReservationRoomLine.Adults, Reservations.Adults,
      ReservationRoomLine.Children1, Reservations.Children1,
      ReservationRoomLine.Children2, Reservations.Children2,
      ReservationRoomLine.Price, Reservations.Price,
      ReservationRoomLine.TotalPrice, Reservations.TotalPrice,
      ReservationRoomLine.ProductNumber, Reservations.ProductNumber,
      ReservationRoomLine.ProductNumber2, Reservations.ProductNumber2,
      ReservationRoomLine.ProductNumber3, Reservations.ProductNumber3,
      ReservationRoomLine.ProductNumber4, Reservations.ProductNumber4,
      ReservationRoomLine.Day2, Reservations.Day2,
      ReservationRoomLine.Day3, Reservations.Day3,
      ReservationRoomLine.Day4, Reservations.Day4,
      ReservationRoomLine.PriceOverwritten, Reservations.PriceOverwritten,
      ReservationRoomLine.PriceOverwritten2, Reservations.PriceOverwritten2,
      ReservationRoomLine.PriceOverwritten3, Reservations.PriceOverwritten3,
      ReservationRoomLine.PriceOverwritten4, Reservations.PriceOverwritten4,
      ReservationRoomLine.FirstPrice1, Reservations.FirstPrice1,
      ReservationRoomLine.FirstPrice2, Reservations.FirstPrice2,
      ReservationRoomLine.FirstPrice3, Reservations.FirstPrice3,
      ReservationRoomLine.FirstPrice4, Reservations.FirstPrice4,
      ReservationRoomLine.BookedTypeNumber,
      Rooms.RoomTypeNumber,
      ISNULL(ReservationRoomline.NumberOfRooms,0), ReservationRoomline.NumberOfRooms,
      Rooms.OwnerSettlementType, Rooms.OwnerSettlementPercent, Rooms.OwnerSettlementPercentOwnUse, Rooms.CustomerNumber,
      ReservationRoomLineXTab.LineNumber,
      Reservations.Status,
      CustHot.CustomerNumber
    HAVING  SUM((TransactionsVATSum.PriceOverwrite + TransactionsVAT.PriceOverwrite) * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END)) > 0
    ORDER BY Rooms.Number
  END
  ELSE
  BEGIN
  DECLARE db_cur CURSOR LOCAL FOR
    SELECT DISTINCT 
    Transactions.FolioNumber,
    Folios.ReferenceNumber, 
    Reservations.GuestNumber,
    ReservationMain.HotelNumber,
    Rooms.Number, Rooms.RoomNumber,
    ReservationRoomLine.ArrivalDate, Reservations.ArrivalDate AS SpecArrivalDate,
    ReservationRoomLine.DepartureDate, Reservations.DepartureDate AS SpecDepartureDate,
    ReservationRoomLine.Adults, Reservations.Adults AS SpecAdults,  
    ReservationRoomLine.Children1, Reservations.Children1 AS SpecChildren1,  
    ReservationRoomLine.Children2, Reservations.Children2 AS SpecChildren2,
    ReservationRoomLine.Price, Reservations.Price AS SpecPrice,  
    ReservationRoomLine.TotalPrice, Reservations.TotalPrice AS SpecTotalPrice,  
    ReservationRoomLine.ProductNumber, Reservations.ProductNumber AS SpecProductNumber,  
    ReservationRoomLine.ProductNumber2, Reservations.ProductNumber2 AS SpecProductNumber2,  
    ReservationRoomLine.ProductNumber3, Reservations.ProductNumber3 AS SpecProductNumber3,  
    ReservationRoomLine.ProductNumber4, Reservations.ProductNumber4 AS SpecProductNumber4,  
    ReservationRoomLine.Day2, Reservations.Day2 AS SpecDay2,  
    ReservationRoomLine.Day3, Reservations.Day3 AS SpecDay3,  
    ReservationRoomLine.Day4, Reservations.Day4 AS SpecDay4,  
    ReservationRoomLine.PriceOverwritten, Reservations.PriceOverwritten AS SpecPriceOverwritten,  
    ReservationRoomLine.PriceOverwritten2, Reservations.PriceOverwritten2 AS SpecPriceOverwritten2,  
    ReservationRoomLine.PriceOverwritten3, Reservations.PriceOverwritten3 AS SpecPriceOverwritten3,  
    ReservationRoomLine.PriceOverwritten4, Reservations.PriceOverwritten4 AS SpecPriceOverwritten4,  
    ReservationRoomLine.FirstPrice1, Reservations.FirstPrice1 AS SpecFirstPrice1,  
    ReservationRoomLine.FirstPrice2, Reservations.FirstPrice2 AS SpecFirstPrice2,  
    ReservationRoomLine.FirstPrice3, Reservations.FirstPrice3 AS SpecFirstPrice3,  
    ReservationRoomLine.FirstPrice4, Reservations.FirstPrice4 AS SpecFirstPrice4,  
    ReservationRoomLine.BookedTypeNumber,
    Rooms.RoomTypeNumber,
    Rooms.OwnerSettlementType, Rooms.OwnerSettlementPercent, Rooms.OwnerSettlementPercentOwnUse, Rooms.CustomerNumber,
    ReservationRoomLineXTab.LineNumber,
    Reservations.Status,
    SUM(TransactionsVATSum.PriceOverwrite * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END)) AS SumWithoutVAT,
    SUM(TransactionsVAT.PriceOverwrite * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END)) AS VATSUM,
    SUM((TransactionsVATSum.PriceOverwrite + TransactionsVAT.PriceOverwrite) * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END)) AS ProductPrice,
    CustHot.CustomerNumber
    FROM Transactions (NOLOCK)
    INNER JOIN Ware (NOLOCK) ON Transactions.WareNumber = Ware.WareNumber
    INNER JOIN WareGroups (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber 
      AND WareGroups.Payment in (2,3,7,8)
    INNER JOIN Accounts (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
    INNER JOIN Transactions AS TransactionsVATSum (NOLOCK) ON TransactionsVATSum.GlobalTransactionNumber = Transactions.GlobalTransactionNumber 
    INNER JOIN Accounts AS AccountsVATSum (NOLOCK) ON AccountsVATSum.AccountNumber = TransactionsVATSum.AccountNumber
    INNER JOIN Transactions AS TransactionsVAT (NOLOCK) ON TransactionsVAT.GlobalTransactionNumber = Transactions.GlobalTransactionNumber  
    INNER JOIN Accounts AS AccountsVAT (NOLOCK) ON AccountsVAT.AccountNumber = TransactionsVAT.AccountNumber
    INNER JOIN Rooms (NOLOCK) ON Convert(Int,Transactions.Note) = Rooms.Number 
      AND ISNULL(OwnerSettlementType, 0) > 0  
      AND ((@ARoomNumberStr = '') OR (Rooms.Number IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberStr)))) 
      AND ((@AHotelNumber = -1) OR (Rooms.HotelNumber = @AHotelNumber))
    LEFT OUTER JOIN Customer C (NOLOCK) ON C.HotelNumber = Rooms.HotelNumber
      AND ((Transactions.HotelCustomerNumber = -1) OR (C.CustomerNumber = Transactions.HotelCustomerNumber))
    INNER JOIN Customer (NOLOCK) ON Customer.CustomerNumber = Rooms.CustomerNumber
    INNER JOIN Folios (NOLOCK) ON Transactions.Folionumber = Folios.FolioNumber 
      AND (((Transactions.Movedfolionr is null) OR (Transactions.Movedfolionr=0)) OR ((Transactions.Movedfolionr>0)))
    RIGHT OUTER JOIN Reservations (NOLOCK) on Reservations.ReferenceNumber = Folios.ReferenceNumber 
      AND Reservations.GuestNumber = Transactions.GuestNumber  
      AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusStr)) 
      AND Reservations.ReferenceNumber > 0
    LEFT OUTER JOIN ReservationRoomLineXTab (NOLOCK) on Reservations.ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber 
      AND Reservations.GuestNumber = ReservationRoomLineXTab.GuestNumber
    LEFT OUTER JOIN RESERVATIONROOMLINE (NOLOCK) ON RESERVATIONROOMLINEXTAB.LINENUMBER = RESERVATIONROOMLINE.LINENUMBER
    LEFT OUTER JOIN ReservationMain (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber AND ReservationType <= 7
      AND Rooms.HotelNumber = ReservationMain.HotelNumber
    INNER JOIN Countries (NOLOCK) ON  Customer.CountryNumber = Countries.CountryNumber
    INNER JOIN Customer CustHot WITH (NOLOCK) ON CustHot.HotelNumber = Rooms.HotelNumber
    WHERE Transactions.MovedDeleted = 0
    AND ((Transactions.FolioNumber= Folios.FolioNumber) AND TransactionsVATSum.FolioNumber = Folios.FolioNumber AND TransactionsVAT.FolioNumber = Folios.FolioNumber 
    AND ((Transactions.ShowOnBillFlag=1) and ((Transactions.Payment=0 AND AccountsVATSum.AccountType = 3 AND AccountsVAT.AccountType = 2))))
    AND Transactions.OriginalDate >= CONVERT(VARCHAR(8), @AStartDateTime, 112)
    AND Transactions.OriginalDate <= CONVERT(VARCHAR(8), @AEndDateTime, 112)
    AND TransactionsVATSum.MovedDeleted = 0
    AND TransactionsVAT.MovedDeleted = 0
    AND ISNULL(ReservationMain.HotelNumber, -2) > -2
    GROUP BY 
      Transactions.FolioNumber,
      Folios.ReferenceNumber, 
      Folios.GuestNumber, Reservations.GuestNumber,
      ReservationMain.HotelNumber,
      Rooms.Number, Rooms.RoomNumber,
      ReservationRoomLine.ArrivalDate, Reservations.ArrivalDate,
      ReservationRoomLine.DepartureDate, Reservations.DepartureDate,
      ReservationRoomLine.ArrivalDate, Reservations.ArrivalDate,
      ReservationRoomLine.DepartureDate, Reservations.DepartureDate,
      ReservationRoomLine.Adults, Reservations.Adults,
      ReservationRoomLine.Children1, Reservations.Children1,
      ReservationRoomLine.Children2, Reservations.Children2,
      ReservationRoomLine.Price, Reservations.Price,
      ReservationRoomLine.TotalPrice, Reservations.TotalPrice,
      ReservationRoomLine.ProductNumber, Reservations.ProductNumber,
      ReservationRoomLine.ProductNumber2, Reservations.ProductNumber2,
      ReservationRoomLine.ProductNumber3, Reservations.ProductNumber3,
      ReservationRoomLine.ProductNumber4, Reservations.ProductNumber4,
      ReservationRoomLine.Day2, Reservations.Day2,
      ReservationRoomLine.Day3, Reservations.Day3,
      ReservationRoomLine.Day4, Reservations.Day4,
      ReservationRoomLine.PriceOverwritten, Reservations.PriceOverwritten,
      ReservationRoomLine.PriceOverwritten2, Reservations.PriceOverwritten2,
      ReservationRoomLine.PriceOverwritten3, Reservations.PriceOverwritten3,
      ReservationRoomLine.PriceOverwritten4, Reservations.PriceOverwritten4,
      ReservationRoomLine.FirstPrice1, Reservations.FirstPrice1,
      ReservationRoomLine.FirstPrice2, Reservations.FirstPrice2,
      ReservationRoomLine.FirstPrice3, Reservations.FirstPrice3,
      ReservationRoomLine.FirstPrice4, Reservations.FirstPrice4,
      ReservationRoomLine.BookedTypeNumber,
      Rooms.RoomTypeNumber,
      ISNULL(ReservationRoomline.NumberOfRooms,0), ReservationRoomline.NumberOfRooms,
      Rooms.OwnerSettlementType, Rooms.OwnerSettlementPercent, Rooms.OwnerSettlementPercentOwnUse, Rooms.CustomerNumber,
      ReservationRoomLineXTab.LineNumber,
      Reservations.Status,
      CustHot.CustomerNumber
    HAVING  SUM((TransactionsVATSum.PriceOverwrite + TransactionsVAT.PriceOverwrite) * (CASE WHEN Transactions.Pieces > 0 THEN 1 ELSE -1 END)) > 0
    ORDER BY Rooms.Number
  END
  OPEN db_cur
	
  FETCH NEXT FROM db_cur INTO @LogiFolioNumber, @ReferenceNumber, @GuestNumber, @HotelNumber, @RoomNumber, @RoomNumberKey, 
    @ArrivalDate, @SpecArrivalDate, @DepartureDate, @SpecDepartureDate, @Adults, @SpecAdults, @Children1, @SpecChildren1, @Children2, @SpecChildren2, @Price, @SpecPrice, @TotalPrice, @SpecTotalPrice, 
    @ProductNumber, @SpecProductNumber, @ProductNumber2, @SpecProductNumber2, @ProductNumber3, @SpecProductNumber3, @ProductNumber4, @SpecProductNumber4, @Day2, @SpecDay2, @Day3, @SpecDay3, 
    @Day4, @SpecDay4, @PriceOverwritten, @SpecPriceOverwritten, @PriceOverwritten2, @SpecPriceOverwritten2, @PriceOverwritten3, @SpecPriceOverwritten3, @PriceOverwritten4, @SpecPriceOverwritten4, 
    @FirstPrice1, @SpecFirstPrice1, @FirstPrice2, @SpecFirstPrice2, @FirstPrice3, @SpecFirstPrice3, @FirstPrice4, @SpecFirstPrice4, 
    @BookedTypeNumber, @AllocatedTypeNumber, @osType, @osPercent, @osPercentOwn, @OwnerCustomerNumber, @LineNumber, @Status, @WithOutVat, @VatSum, @TotalWithVat, 
    @HotelCustomerNumber
	
  SET @CleaningPrice = 0
  SET @ResultPackagePrice = 0
  SET @NettoPrice = 0
  SET @NettoPriceReferral = 0

  WHILE @@FETCH_STATUS = 0
  BEGIN
    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 @tmpEnvironmentWareText = WareName FROM Ware (NOLOCK) WHERE WareNumber = @EnvironmentWareNumber
    IF @OwnerElectricityReplaceWareText = 1
      SET @tmpEnvironmentWareText = @OwnerElectricityWareText
    SELECT @tmpRateStr = dbo.GetHotelSetupKeyValue ('ElectricityAccountRate', @HotelCustomerNumber)
    SET @EnvironmentAccountRate = ISNULL(CONVERT(REAL, REPLACE(@tmpRateStr, ',', '.')), 100)
    
    SELECT @OwnerCleaningWareText = KeyValue FROM HotelSetup(NOLOCK) WHERE SearchKey = 'OwnerCleaningWareText'
    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 @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 (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 @OwnerCreditCardFeeWareText = KeyValue FROM HotelSetup(NOLOCK) WHERE SearchKey = 'OwnerCreditCardFeeWareText'
    SET @OwnerCreditCardFeeWareText = ISNULL(@OwnerCreditCardFeeWareText, '')
    SELECT @OwnerCreditCardFeeReplaceWareText = dbo.GetHotelSetupKeyValue('OwnerCreditCardFeeReplaceWareText', @HotelCustomerNumber)
    IF ISNULL(@OwnerCreditCardFeeReplaceWareText, 0) = 0
      SET @OwnerCreditCardFeeReplaceWareText = 0
    SELECT @CreditCardFeeWareNumber = dbo.GetHotelSetupKeyValue('CreditCardFeeWareName', @HotelCustomerNumber)
    IF ISNULL(@CreditCardFeeWareNumber, 0) > 0
      SELECT @CreditCardFeeWareText = WareName FROM Ware WITH (NOLOCK) WHERE WareNumber = @CreditCardFeeWareNumber
    IF @OwnerCreditCardFeeReplaceWareText = 1
      SET @CreditCardFeeWareText = @OwnerCreditCardFeeWareText
    SELECT @tmpRateStr = dbo.GetHotelSetupKeyValue('CreditCardFeeAccountRate', @HotelCustomerNumber)
    SET @CreditCardFeeAccountRate = ISNULL(CONVERT(REAL, REPLACE(@tmpRateStr, ',', '.')), 100)

    SELECT @OwnerCreditCardFeeRoomCSV = dbo.GetHotelSetupKeyValue('OwnerCreditCardFeeRoom', @HotelCustomerNumber)

    SELECT @TicketFeeRoomCSV = dbo.GetHotelSetupKeyValue('OwnerTicketFeeRoom', @HotelCustomerNumber)

    SELECT @TicketFeePerRes = dbo.GetHotelSetupKeyValue('OwnerTicketFeePerReservation', @HotelCustomerNumber)

    SELECT @AgentCustomerNumber = ReservationAddress.Link 
    FROM ReservationAddressXTab WITH (NOLOCK) 
    INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXtab.AddressNumber
      AND ReservationAddress.AddressType = 4
    WHERE ReservationAddressXTab.ReferenceNumber = @ReferenceNumber
      AND ReservationAddressXTab.GuestNumber = @GuestNumber    

    SELECT @RoomNumberKeyReferral = RoomNumber, @osPercentReferral = OwnerSettlementPercentReferral
    FROM Rooms WITH (NOLOCK)
    WHERE Rooms.CustomerNumber = @AgentCustomerNumber

    IF @ShowPrint = 1
      PRINT '--------------------------'
	
    SET @TmpLineNumber = @LineNumber
	
    IF @GuestNumber > 0
    BEGIN
      IF @ShowPrint = 1
        PRINT 'Use SPEC'
	
      SET @ArrivalDate = @SpecArrivalDate
      SET @DepartureDate = @SpecDepartureDate
      SET @Adults = @SpecAdults
      SET @Children1 = @SpecChildren1
      SET @Children2 = @SpecChildren2
      SET @Price = @SpecPrice
      SET @TotalPrice = @SpecTotalPrice
      SET @ProductNumber = @SpecProductNumber
      SET @ProductNumber2 = @SpecProductNumber2
      SET @ProductNumber3 = @SpecProductNumber3
      SET @ProductNumber4 = @SpecProductNumber4
      SET @Day2 = @SpecDay2
      SET @Day3 = @SpecDay3
      SET @Day4 = @SpecDay4
      SET @PriceOverwritten = @SpecPriceOverwritten
      SET @PriceOverwritten2 = @SpecPriceOverwritten2
      SET @PriceOverwritten3 = @SpecPriceOverwritten3
      SET @PriceOverwritten4 = @SpecPriceOverwritten4
      SET @FirstPrice1 = @SpecFirstPrice1
      SET @FirstPrice2 = @SpecFirstPrice2
      SET @FirstPrice3 = @SpecFirstPrice3
      SET @FirstPrice4 = @SpecFirstPrice4
    END
	
    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)

    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, -1)
    SET @Day3 = ISNULL(@Day3, -1)
    SET @Day4 = ISNULL(@Day4, -1)
    SET @HotelNumber = ISNULL(@HotelNumber, -1)

    IF @ShowPrint = 1
    BEGIN
      PRINT '@WithOutVat'
      PRINT CONVERT(VARCHAR(10), @WithOutVat)
      PRINT '@VatSum'
      PRINT CONVERT(VARCHAR(10), @VatSum)
      PRINT '@TotalWithVat'
      PRINT CONVERT(VARCHAR(10), @TotalWithVat)
    END

    SET @CleaningPrice = 0
    SET @ResultPackagePrice = @TotalPrice - @TotalWithVat

    IF @UseOwnerOwnCalc = 1 AND @OwnerCustomerNumber = @AgentCustomerNumber  -- The owner have booked own room
      SET @osPercentCalc = @osPercentOwn
    ELSE
      SET @osPercentCalc = @osPercent

    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(@osPercentCalc, 0) = 0
      SET @osPercentCalc = @HotelSetupOwnerPercent


    SET @TotalPrice = @TotalWithVat
    IF @osType = 3 AND @OwnerUseSettlementType = 1
    BEGIN
      -- Calculate according to related product
      SET @RoomNumberStr = CAST(@RoomNumber AS VARCHAR)
      EXEC OwnerReportReservationsTimeShare @AStartDateTime, @AEndDateTime, @AReservationStatusStr, @RoomNumberStr, -1, 1, 1, 0, 1, @NettoPrice OUTPUT

      SET @NettoPriceReferral = 0
    END
    ELSE IF @osType = 4 -- Calc by rule Get total amount of payout and get the percent
    BEGIN
      DECLARE @dtStart CHAR(8)
      DECLARE @dtEnd CHAR(8)
      DECLARE @TotalPayout MONEY
      DECLARE @Percentage MONEY
      DECLARE @Amount MONEY
      DECLARE @tmpPercentage MONEY
      DECLARE @tmpAmount MONEY
      DECLARE @tmp MONEY
      DECLARE @RestOwner MONEY

      SET @dtStart = CONVERT(CHAR(8), CONVERT(CHAR(4), YEAR(@AStartDateTime)) + '0101', 112)
      SET @dtEnd = CONVERT(CHAR(8), CONVERT(CHAR(4), YEAR(@AStartDateTime)) + '1231', 112)

      EXEC OwnerGetPaymentTotal @RoomNumber, @dtStart, @dtEnd, @HotelNumber, @TotalPayout OUTPUT, 0
      SET @TotalPayout = ISNULL(@TotalPayout, 0)

      DECLARE db_cur_percentage CURSOR LOCAL READ_ONLY SCROLL FOR
        SELECT Percentage, Amount
        FROM RulesSetting(NOLOCK)
        WHERE FK_RuleId = CAST(@osPercentCalc AS INT)
        ORDER BY Amount DESC
      OPEN db_cur_percentage

      FETCH NEXT FROM db_cur_percentage INTO @Percentage, @Amount

      WHILE @@FETCH_STATUS = 0
      BEGIN
        FETCH NEXT FROM db_cur_percentage INTO @tmpPercentage, @tmpAmount
        FETCH PRIOR FROM db_cur_percentage INTO @Percentage, @Amount
        
        IF @TotalPayout + (@TotalWithVat * @tmpPercentage / 100) < @Amount
        BEGIN
          SET @NettoPrice = (@TotalWithVat * @tmpPercentage / 100)
          BREAK 
        END
        ELSE
        BEGIN
          SET @tmp = @Amount - @TotalPayout

          IF @tmp > 0
          BEGIN
            SET @RestOwner = @TotalWithVat - ((@tmp/@tmpPercentage)*100)
            SET @NettoPrice = @tmp + ((@RestOwner * @Percentage / 100))
          END
          ELSE
          BEGIN
            SET @NettoPrice = (@TotalWithVat * @Percentage / 100)
          END

          BREAK 
        END
        
        FETCH NEXT FROM db_cur_percentage INTO @Percentage, @Amount
      END
      CLOSE db_cur_percentage
      DEALLOCATE db_cur_percentage
    END
    ELSE IF @ostype = 2
	BEGIN
	  DECLARE @NumberOfDays INT
	  SET @NumberOfDays = DATEDIFF(DAY, @ArrivalDate, @DepartureDate)
	  SET @NettoPrice = @NumberOfDays * @osPercentCalc

      IF @UseOwnerReferralCalc = 1
        SET @NettoPriceReferral = (@TotalPrice * @osPercentReferral / 100)
	END
	ELSE
    BEGIN
      SET @NettoPrice = (@TotalPrice * @osPercentCalc / 100)
      IF @UseOwnerReferralCalc = 1
        SET @NettoPriceReferral = (@TotalPrice * @osPercentReferral / 100)
    END

-- calc the discount....
    SELECT TOP 1 @OwnerDiscountRate = raOwnerDisc.OwnerDiscountRate 
    FROM ReservationAddressXTab raxOwnerDisc WITH (NOLOCK)
    INNER JOIN Reservations R WITH (NOLOCK) ON R.ReferenceNumber = @ReferenceNumber
      AND R.GuestNumber = @GuestNumber
    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

    SET @DiscoutTxt = ''
    IF @NettoPrice > 0 AND @OwnerDiscountRate > 0
    BEGIN
      SET @DiscoutTxt = CAST(@OwnerDiscountRate AS VARCHAR(2)) + '% Rabat'
      SET @NettoPrice = @NettoPrice - ((@NettoPrice * @OwnerDiscountRate) / 100)
      SET @NettoPriceReferral = @NettoPriceReferral - ((@NettoPriceReferral * @OwnerDiscountRate) / 100)
    END

    IF @AAddToFolio = 0
    BEGIN
      -- get the name and address
      SELECT @FirstName = ra.FirstName, @LastName = ra.LastName 
      FROM ReservationAddressXTab rax (NOLOCK)
      INNER JOIN ReservationAddress ra (NOLOCK) ON rax.AddressNumber = ra.AddressNumber AND rax.ReferenceNumber = @ReferenceNumber AND rax.GuestNumber = @GuestNumber AND AddressType = 1

      IF @FirstName = ''
        SET @GuestName = @LastName
      ELSE
        SET @GuestName = @FirstName + ' ' + @LastName
   
      INSERT INTO #Reservations (Referencenumber, GuestName, GuestNumber, HotelNumber, ReservationArrival, ReservationDeparture, Pax, Adults, Children1, Children2, TotalPrice, CleaningPrice, PackagePrice, OwnerPercent, NettoPrice, RoomNumber, ReservationStatus)
      VALUES (@ReferenceNumber, @GuestName, @GuestNumber, @HotelNumber, @ArrivalDate, @DepartureDate, @Pax, @Adults, @Children1, @Children2, @TotalPrice, @CleaningPrice, @ResultPackagePrice, @osPercentCalc, @NettoPrice, @RoomNumber, @Status)
    END

    IF @AAddToFolio = 1
    BEGIN
      IF @ShowPrint = 1
      BEGIN
        PRINT 'AddToFolio'
        PRINT @ReferenceNumber
        PRINT @GuestNumber
        PRINT @LogiFolioNumber
        PRINT @EnvironmentWareNumber
        PRINT @AdditionalWareNumber
        PRINT @ResArrivalDateStr
        PRINT @ExtraDepartureDateStr
      END

      SET @ExtraDepartureDateStr = CONVERT(CHAR(8), @DepartureDate + 1, 112)

      IF @ShowPrint = 1
        PRINT @ExtraDepartureDateStr

      -- Calc Additional
      SELECT @PriceAdditional = Amount, @AdditionalPieces = Pieces FROM DBO.GetFolioAmountByWareNumber (@ReferenceNumber, @GuestNumber, -1, @AdditionalWareNumber, @ResArrivalDateStr, @ExtraDepartureDateStr)
      SET @NettoPriceAdditional = (@PriceAdditional * @AdditionalAccountRate / 100)

      -- Calc Environment
      SET @NettoPriceEnvironment = 0
      SELECT @PriceEnvironment = Amount, @EnvironmentPieces = Pieces FROM DBO.GetFolioAmountByWareNumber (@ReferenceNumber, @GuestNumber, -1, @EnvironmentWareNumber, @ResArrivalDateStr, @ExtraDepartureDateStr)
      IF ISNULL(@PriceEnvironment, 0) > 0
        SET @NettoPriceEnvironment = (@PriceEnvironment * @EnvironmentAccountRate / 100)

      -- Calc Cleaning
      SET @NettoPriceCleaning = 0
      SELECT @PriceCleaning = Amount, @CleaningPieces = Pieces FROM DBO.GetFolioAmountByWareNumber (@ReferenceNumber, @GuestNumber, -1, @CleaningWareNumber, @ResArrivalDateStr, @ExtraDepartureDateStr)
      IF ISNULL(@PriceCleaning, 0) > 0
        SET @NettoPriceCleaning = (@PriceCleaning * @CleaningAccountRate / 100)

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

      IF @ShowPrint = 1
      BEGIN
        PRINT '@PriceAdditional'
        PRINT CONVERT(VARCHAR(10), @PriceAdditional)
        PRINT '@ProductNumber'
        PRINT @ProductNumber
      END

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

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

      IF DBO.TableExists('RoomMeterExt') = 1
      BEGIN
        DECLARE @EnvironmentStart INT
        DECLARE @EnvironmentEnd INT

        SELECT TOP 1 @EnvironmentStart = MeterValue FROM RoomMeterExt(NOLOCK)
        WHERE ReferenceNumber = @ReferenceNumber 
        AND GuestNumber = @GuestNumber
        AND MeterType = 0 -- Electricity
        --AND ReadType = 0 -- Checkin
        ORDER BY DateStamp ASC

        SELECT TOP 1 @EnvironmentEnd = MeterValue FROM RoomMeterExt(NOLOCK)
        WHERE ReferenceNumber = @ReferenceNumber 
        AND GuestNumber = @GuestNumber
        AND MeterType = 0 -- Electricity
        --AND ReadType = 1 -- Checkout
        ORDER BY DateStamp DESC

        SET @EnvironmentWareText = @tmpEnvironmentWareText
        IF ISNULL(@EnvironmentStart, 0) > 0 AND ISNULL(@EnvironmentEnd, 0) > 0 AND (ISNULL(@EnvironmentStart, 0)-ISNULL(@EnvironmentEnd, 0) <> 0)
        BEGIN
          IF @OwnerConsumption = 1 
            SET @EnvironmentWareText = @EnvironmentWareText + ' (' + CONVERT(VARCHAR(10), @EnvironmentPieces) + ' '+ @OwnerElectricityWareText + ')'
          ELSE
            SET @EnvironmentWareText = @EnvironmentWareText + ' ' + CONVERT(VARCHAR(10), @EnvironmentStart) + ' - ' + CONVERT(VARCHAR(10), @EnvironmentEnd)
        END
      END

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

      DECLARE @tmpINT INT
      
      IF @AddPeriodText = 1
        SET @OwnerWareText = @tmpOwnerWareText + ' (' + CONVERT(CHAR(10), @ArrivalDate, 104) + ' - ' + CONVERT(CHAR(10), @DepartureDate, 104)+')'
      IF @DiscoutTxt <> ''
        SET @OwnerWareText = @OwnerWareText + ' ' + @DiscoutTxt       

      -- Calc credit card fee
      SET @NettoPriceCreditCardFee = 0
      IF @CreditCardFeeAccountRate <> 0 AND @OwnerCreditCardFeeRoomCSV <> ''
      BEGIN
        SELECT @cnt = COUNT(*)
        FROM Rooms WITH (NOLOCK)
        WHERE Rooms.RoomNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@OwnerCreditCardFeeRoomCSV))
        AND Rooms.RoomNumber = @RoomNumberKey

        IF ISNULL(@cnt, 0) > 0
        BEGIN
          SET @NettoPriceCreditCardFee = 0
          SELECT @NettoPriceCreditCardFee = SUM(t.PriceOverwrite * (CASE t.Debit WHEN 1 THEN 1 ELSE -1 END)*CASE WHEN a.AccountType IN (4,5,6) THEN 1 ELSE 1+(t.VatValue/100) END)
          FROM Transactions t WITH (NOLOCK) 
          INNER JOIN Folios f WITH (NOLOCK) ON f.FolioNumber = t.FolioNumber
          INNER JOIN Accounts a WITH (NOLOCK) ON a.AccountNumber = t.AccountNumber
          INNER JOIN Ware w WITH (NOLOCK) ON w.WareNumber = t.WareNumber
          WHERE t.ShowOnBillFlag = 1
          AND w.OwnerCreditCardFee = 1
          AND f.ReferenceNumber = @ReferenceNumber
          AND f.GuestNumber = @GuestNumber
          AND ISNULL(t.MovedDeleted, 0) = 0

          IF ISNULL(@NettoPriceCreditCardFee, 0) <> 0
            SET @NettoPriceCreditCardFee = (@TotalPrice * @CreditCardFeeAccountRate / 100)

        END  
      END
      SET @NettoPriceCreditCardFee = ISNULL(@NettoPriceCreditCardFee, 0)

      -- calc Ticket fee per res
      SET @NettoPriceTicketFee = 0
      IF @TicketFeePerRes = 1
      BEGIN
        SELECT @cnt = COUNT(*)
        FROM Rooms WITH (NOLOCK)
        WHERE Rooms.RoomNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@TicketFeeRoomCSV))
        AND Rooms.RoomNumber = @RoomNumberKey
        IF ISNULL(@cnt, 0) > 0
          SET @NettoPriceTicketFee = @TicketFeePrice
      END

      EXEC OwnerAddToFolio @ReferenceNumber, @GuestNumber, @OwnerCustomerNumber, @HotelNumber, @BookedTypeNumber, 
        @OwnerWareNumber, @OwnerWareText, @AUserIdNumber, @NettoPrice, 
        @AdditionalWareNumber, @AdditionalWareTextEx, @NettoPriceAdditional,
        @EnvironmentWareNumber, @EnvironmentWareText, @NettoPriceEnvironment,
        @CleaningWareNumber, @CleaningWareTextEx, @NettoPriceCleaning, @RoomNumberKey,
        0, '', 0, 
        @TicketFeeWareNumber, @TicketFeeWareText, @NettoPriceTicketFee, 1, 
        @CreditCardFeeWareNumber, @CreditCardFeeWareText, @NettoPriceCreditCardFee,
        @tmpINT

      IF @UseOwnerReferralCalc = 1 AND @AgentCustomerNumber > 0 AND @AgentCustomerNumber <> @OwnerCustomerNumber
      BEGIN
        IF ISNULL(@RoomNumberKeyReferral, 0) > 0 AND @NettoPriceReferral > 0
          EXEC OwnerAddToFolio @ReferenceNumber, @GuestNumber, @AgentCustomerNumber, @HotelNumber, @BookedTypeNumber, 
            @OwnerWareNumber, @OwnerWareText, @AUserIdNumber, @NettoPriceReferral, 
            @AdditionalWareNumber, @AdditionalWareTextEx, 0,
            @EnvironmentWareNumber, @EnvironmentWareText, 0,
            @CleaningWareNumber, @CleaningWareTextEx, 0, @RoomNumberKeyReferral,
            0, '', 0, 
            0, '', 0, @tmpINT, 
            0, '', 0, 
            @tmpINT
      END
    END

    FETCH NEXT FROM db_cur INTO @LogiFolioNumber, @ReferenceNumber, @GuestNumber, @HotelNumber, @RoomNumber, @RoomNumberKey,
      @ArrivalDate, @SpecArrivalDate, @DepartureDate, @SpecDepartureDate, @Adults, @SpecAdults, @Children1, @SpecChildren1, @Children2, @SpecChildren2, @Price, @SpecPrice, @TotalPrice, @SpecTotalPrice,
      @ProductNumber, @SpecProductNumber, @ProductNumber2, @SpecProductNumber2, @ProductNumber3, @SpecProductNumber3, @ProductNumber4, @SpecProductNumber4, @Day2, @SpecDay2, @Day3, @SpecDay3,
      @Day4, @SpecDay4, @PriceOverwritten, @SpecPriceOverwritten, @PriceOverwritten2, @SpecPriceOverwritten2, @PriceOverwritten3, @SpecPriceOverwritten3, @PriceOverwritten4, @SpecPriceOverwritten4,
      @FirstPrice1, @SpecFirstPrice1, @FirstPrice2, @SpecFirstPrice2, @FirstPrice3, @SpecFirstPrice3, @FirstPrice4, @SpecFirstPrice4,
      @BookedTypeNumber, @AllocatedTypeNumber, @osType, @osPercent, @osPercentOwn, @OwnerCustomerNumber, @LineNumber, @Status, @WithOutVat, @VatSum, @TotalWithVat, 
      @HotelCustomerNumber
  END

  CLOSE db_cur
  DEALLOCATE db_cur
END

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

  DROP TABLE #Reservations
END