CREATE PROCEDURE DBO.ChargeAndCalcCancellationFee @ReferenceNumber int, @GuestNumber int, @TotalAmount money output, @CancellationFeeAmount money output, @FK_Department INT = 1, @UserIDNumber int = -1, @CountryNumber int = -1, @AllGuestNumbers bit = 0, @FirstAddressNumber INT = 0, @IgnoreReservationStatuses BIT = 0, @IncludeOutOfHouse BIT = 0, @CalcCancelledRes BIT = 0, @UseCalcFeePercent BIT = 0, @CalcFeePercent real = 0 AS
SET NOCOUNT ON

/*
  Changes:
   KJ 20240820: Bug: Fix for "Wrong price calculated after Kåre changes made in rev. 57627 (One person pays for all)" 
   KJ 20240214: Added timeZone
   KJ 20231129: Extras and discount per ware
   PHJ 20201105 Added param @UseCalcFeePercent BIT = 0, @CalcFeePercent real = 0 for use of insurance price calculation percentage rate when not yet set on the reservation.
   KJ 20200416: Bug: Fix for "Amount missing due to "illegal" DiscountCustomerType". Case #CR185713
   SG 20171103: Price was doubled. Added: Customer.MarkAsDeleted = 0 
   KJ 20161230: SQL optimize: No join predicate has been detected. Req 3279
   KJ 20161012: Bug: Fix for "Amount in New changed cancelled report wrong". Case #CR114426
   KJ 20160301: Bug: fix for "Wrong amount when running shared namelist Room/Hall". Case XX
   KJ 20151214: Bug: Fix for "Rooms not included when runung Maersk mode"
   KJ 20141104: Fix for "Error charge when running mixed resources".
--nb20140721 FolioIdentity discontinued
   KJ 20140409: Bug: 
   KJ 20120823: Bug: Fix for "Wrong price on hall products when children 2". Case #CR046123
   KJ 20110514: Bug: Fix for "CancellationFee sometimes charged without hotelcustomernumber"
   KJ 20100602: Bug: Fix for Customer/hotel can be deleted
   KJ 20100128: Ext: Makeing it more secure to only include extras and not package linies with faulty department
   KJ 20090313: Bug: Fix for "Do not calc Extra from cancelled guests"
   KJ 20080617: Ext: Added @CalcCancelledRes as input param - used to calc cancelled reservations to New/Changed/cancelled report
   KJ 20071210: Bug: Fix for "Cancellation fee charged multiple times"
   TL 20071108: BUG: Fix for wrong total price when using namelist per person on plus product (case #39489)
   AV 20071005 OPT: Arrangement manual lines: Code changed to inner joins instead of inner selects. Much faster
   TL 20070907: BUG: Missing RoomType in call to ProductPrice
   TL 20070814: BUG: Wrong amount on discount on room/hall/conference lines
   TL 20070807: BUG: Double amount when different discount specified on manual lines for company and guest
   KJ 20070601: Added "Check for Cancellation fee already charged"
   TL 20070516: Take reservation status into account when calculating hall prices
   KJ 20070410: Department rewrite.
   KJ 20070322: Added ISNULL to GuestNo
   KJ 20070123: Add arrangement even if Ex_Department isn't a autocharge dep.
   KJ 20070104: Added support for Halls on subres
   TL 20061117: Added @IncludeOutOfHouse
   KJ 20060629: Amounts wrong when not running Domino and multible sales channels in customer table whith a hotelnumber
   KJ 20060628: Using NumberOfDecimals to calculate cancellationFee
   KJ 20060505: Ext: Using FK_ExDepartment
   KJ 20060504: Removed @ExDepartment - not used
   KJ 20060424: Fix for Wrong @TotalAmount when star-product and priceoverwrite
   KJ 20051031: Possible fix for "General SQL error. Lock request time out period exceeded. A cursor with the name 'CancellationFeeResCur' does not exist."
   Rettet d. 28/10-2004 af KS: Bug : imp if not percent- only one charge
   Rettet d. 25/08-2004 af KS: Bug : null in firstprice
   Rettet d. 13/07-2004 af KS: Bug namelistsetting - if namelistproduct=0 take from horelsetup
   Rettet d. 09/07-2004 af KS: Bug pays... if guest=0 and not adults+child1+2
   Rettet d. 24/06-2004 af KS: Added hotelnumber>=0 and only saleschannel in join for hotelnumber
   Rettet d. 14/06-2004 af KS: Bug when discount and 2 addresses
   Rettet d. 14/05-2004 af KS: Imp. CustomerHotelNumber for multihotels
   Rettet d. 10/05-2004 : Rettet if @CancellationFeePercent>0 AND @NoCancellationFeeOnExtras = 1   til    if @CancellationFeePercent>0 AND @NoCancellationFeeOnExtras = 0     da det ellers er modsat booking
   Rettet d. 21/04-2004 : imp. new namelist-option
   Rettet d. 30/03-2004 : Imp percent or not when calc 
   Rettet d. 17/02-2004 : Rettet if @CancellationFeePercent>0 AND @NoCancellationFeeOnExtras = 1   til    if @CancellationFeePercent>0 AND @NoCancellationFeeOnExtras = 0     da det ellers er modsat booking
   Rettet d. 02/02-2004 : Imp. AddonIfPriceOverwritten
   Rettet d. 28/01-2004 : Fixed bug with no fee on mainres but on subres
   Rettet d. 02/12-2003 : imp. OnePersonPayForAll.
   Rettet d. 18/11-2003 : imp. namelist-option
   Rettet d. 24/09-2003 : select only reservations with status status<=5
   Rettet d. 22/09-2003 : calc CancellationFee one time and not as a sum pr. line
   Rettet d. 17/09-2003 : If priceoverwrite=0 and no product set priceoverwrite = 1
   Rettet d. 31/07-2003 : Fix bug with daystart and halls (dayshifttime)
   Rettet d. 14/04-2003 : added : charge only if cancelationfeeware
   Rettet d. 14/04-2003 : added if price is null --> set price=0 in extra
   Rettet d. 07/04-2003 : Removed (manuel linies pieces=0 -->> pieces=1) 
   Rettet d. 17/03-2003 : + product and extras
   Rettet d. 12/03-2003 : Jacob Pedersen: Undgåelse af låsning af ReservationAddressIdentity og FolioIdentity tabeller ved kald til FindFolioNumber
   Rettet d. 06/03-2003 : if hall and no product ->> fixedprice=0
   Rettet d. 05/03-2003 : added roomnumber on cancellationfee
   Rettet d. 05/03-2003 : added <<ExDepartment<>''>> when manuel arrangement lines
   Rettet d. 20/02-2003 : fixed error (manuel linies pieces=0 -->> pieces=1)  and doble linies in CancellationFeeArrResCur
   Rettet d. 18/02-2003 : fixed error doble halllines + arrivardate (startdate)
   Rettet d. 14/02-2003 : fixed error if priceoverwrite (missing *pieces)
   Rettet d. 10/02-2003 : fixed error if not namelist on all reslines
   Rettet d. 07/02-2003 : added guestnumber and all guestnumbers
   Rettet d. 23/01-2003 : imp @NoCancellationFeeOnExtras + Discount
   Rettet d. 13/12-2002 : Imp. @AddonIfPriceOverwritten
   Rettet d. 12/12-2002 : BugFix
   Rettet d. 11/12-2002 : Created and tested
*/

DECLARE @PostForDateTime DATETIME

Declare @StayEndDateStr Char(8)
Declare @StayStartDateStr Char(8)
Declare @StayEndDate DateTime
Declare @StayStartDate DateTime
Declare @DepartureTime char(5)

Declare @StayEndDate2 DateTime
Declare @StayEndDate3 DateTime
Declare @StayEndDate4 DateTime
Declare @StayStartDate2 DateTime
Declare @StayStartDate3 DateTime
Declare @StayStartDate4 DateTime

Declare @BookedTypeNumber int
Declare @ProductNumber int
Declare @PriceOverWritten bit
Declare @Adults int
Declare @Children1 smallint
Declare @Children2 smallint

Declare @PaysAdults int
Declare @PaysChildren1 smallint
Declare @PaysChildren2 smallint

Declare @Pieces int
Declare @Price money
DECLARE @ArrangementWareNumber INT

Declare @CalcTotalPrice Real
Declare @CalcArrivalPrice Real

Declare @FirstPrice1 money 
Declare @PriceOverwritten2 Bit
Declare @FirstPrice2 money
Declare @Day2 Int
Declare @ProductNumber2 Int
Declare @PriceOverwritten3 Bit
Declare @FirstPrice3 money
Declare @Day3 Int
Declare @ProductNumber3 Int
Declare @PriceOverwritten4 Bit
Declare @FirstPrice4 money
Declare @Day4 Int
Declare @ProductNumber4 Int

Declare @StarProduct bit

Declare @NameListReservationPerPersonHotelSetup Integer
Declare @NameListReservationPerPersonProduct Integer

Declare @NumberOfNights int

Declare @CancellationFeePercent real

Declare @Amount money
Declare @TotalAmountExtra Money
Declare @AmountDay1 money
Declare @AmountDay2 money
Declare @AmountDay3 money
Declare @AmountDay4 money

Declare @SplitbillNumber int

Declare @AddonIfPriceOverwritten bit

Declare @AllDISCOUNTRATE real
Declare @RoomDiscountRate real
Declare @MeetingDiscountRate real
Declare @HallDiscountRate real
DECLARE @ExtraDiscountRate REAL
DECLARE @DiscountIsFixedAmount BIT

Declare @FixedPrice Bit

Declare @DiscountCustomerType tinyint

Declare @NoCancellationFeeOnExtras bit

Declare @ResLineNumber int

Declare @TempGuestNumber int

Declare @AllDISCOUNTRATEMainRes real
Declare @RoomDiscountRateMainRes real
Declare @MeetingDiscountRateMainRes real
Declare @HallDiscountRateMainRes real
Declare @DiscountCustomerTypeMainRes tinyint
DECLARE @ExtraDiscountRateMainRes REAL

declare @ReservationConfLineNumber int

Declare @RoomNumber Int
Declare @HallNumber Int
Declare @VisualRoomNumber Int
Declare @VisualHallNumber Int
Declare @SideHouseNumber Int
Declare @SideHouse Bit

Declare @NameListSetting int

Declare @Arr24HoursStarts char(5)

Declare @OnePersonPaysForAll bit

Declare @AddonIfPriceOverwrittenAmount money

Declare @CancellationFeeInPercent bit

Declare @HotelCustomerNumber int

Declare @PrevLineNumber int

DECLARE @CentralBookingType INT

DECLARE @cnt INT

DECLARE @NumberOfDecimalsInPrices INT
SELECT @NumberOfDecimalsInPrices = CONVERT(INT,KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='NumberOfDecimalsInPrices'
IF ISNULL(@NumberOfDecimalsInPrices, 0) = 0 SET @NumberOfDecimalsInPrices = 0

Select @NameListReservationPerPersonHotelSetup = CONVERT(Integer,KeyValue) from HotelSetUp WITH (NOLOCK)  Where  SearchKey='NameListReservationPerPerson'

Select @NoCancellationFeeOnExtras = CONVERT(Bit,KeyValue) from HotelSetUp WITH (NOLOCK)  Where  SearchKey='NoCancellationFeeOnExtras'
if isnull(@NoCancellationFeeOnExtras,0)=0 SET @NoCancellationFeeOnExtras=0

Select @AddonIfPriceOverwritten = CONVERT(Bit,KeyValue) from HotelSetUp WITH (NOLOCK)  Where  SearchKey='AddonIfPriceOverwritten'
if isnull(@AddonIfPriceOverwritten,0)=0 SET @AddonIfPriceOverwritten=0

Select @Arr24HoursStarts = KeyValue from HotelSetUp WITH (NOLOCK)  Where  SearchKey='Arr24HoursStarts'

Select @OnePersonPaysForAll = CONVERT(Bit,KeyValue) from HotelSetUp WITH (NOLOCK)  Where  SearchKey='OnePersonPaysForAll'
if isnull(@OnePersonPaysForAll,0)=0 SET @OnePersonPaysForAll=0

Select @CancellationFeeInPercent = PriceInPercent from Ware WITH (NOLOCK) inner join WareGroups WITH (nolock) on Ware.WareGroupNumber=WareGroups.WareGroupNumber
where Ware.SortNumber=1 and WareGroups.Payment=4 and (Ware.MarkAsDeleted=0 or Ware.MarkAsDeleted is null) AND (WareGroups.MarkAsDeleted='0' or waregroups.MarkAsDeleted is null)

if isnull(@CancellationFeeInPercent,0)=0 SET @CancellationFeeInPercent = 0

SELECT @CentralBookingType = CONVERT(INT, Keyvalue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CentralBookingType'
IF ISNULL(@CentralBookingType,0)=0 SET @CentralBookingType = 0

SET @TotalAmount = 0
SET @CancellationFeeAmount = 0
SET @Amount = 0

SET @VisualHallNumber = NULL
SET @VisualRoomNumber = NULL

SET @TotalAmountExtra = 0

if @AllGuestNumbers=1
begin
  select
    @AllDISCOUNTRATEMainRes = RESERVATIONADDRESS.DISCOUNTRATE,
    @RoomDiscountRateMainRes = RESERVATIONADDRESS.RoomDiscountRate,
    @DiscountCustomerTypeMainRes = RESERVATIONS.DiscountCustomerType,
    @MeetingDiscountRateMainRes = RESERVATIONADDRESS.MeetingDiscountRate,
    @HallDiscountRateMainRes = RESERVATIONADDRESS.HallDiscountRate,
    @ExtraDiscountRateMainRes = ReservationAddress.ExtrasDiscountRate
  FROM Reservationaddress WITH (NOLOCK)
  inner join RESERVATIONADDRESSXTAB WITH (nolock) on RESERVATIONADDRESSXTAB.AddressNumber = Reservationaddress.AddressNumber
    AND RESERVATIONADDRESSXTAB.ReferenceNumber = @ReferenceNumber 
    and RESERVATIONADDRESSXTAB.GuestNumber=0
  inner join Reservations WITH (nolock) on Reservations.ReferenceNumber = RESERVATIONADDRESSXTAB.ReferenceNumber
    AND DiscountCustomerType > 0
  where (RESERVATIONADDRESS.DISCOUNTRATE>0 or RESERVATIONADDRESS.RoomDiscountRate>0 or RESERVATIONADDRESS.MeetingDiscountRate>0 or RESERVATIONADDRESS.HallDiscountRate>0 OR ReservationAddress.ExtrasDiscountRate <> 0)

  if @@RowCount=0
  begin
    SET @AllDISCOUNTRATEMainRes=0
    SET @RoomDiscountRateMainRes=0
    SET @MeetingDiscountRateMainRes=0
    SET @HallDiscountRateMainRes=0
    SET @DiscountCustomerTypeMainRes=0
    SET @ExtraDiscountRateMainRes = 0
  end
end

declare @resstatus int

if @IgnoreReservationStatuses = 1
  set @resstatus = 8
else if @IncludeOutOfHouse = 1
  set @resstatus = 6
else
  set @resstatus = 5

Declare CancellationFeeResCur Cursor Fast_Forward Read_Only For
SELECT
  RESERVATIONROOMLINEXTAB.ReferenceNumber,
  RESERVATIONROOMLINEXTAB.GuestNumber,
  Reservations.CancellationPercent,
  RESERVATIONROOMLINEXTAB.LineNumber,
  isnull(Customer.CustomerNumber,-1) 
FROM RESERVATIONROOMLINE WITH (NOLOCK) 
  INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER
  INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber 
    AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber
    AND RESERVATIONS.ReferenceNumber = @ReferenceNumber
    AND reservations.status<=@resstatus
    AND ((@AllGuestNumbers=1) OR (@AllGuestNumbers=0 AND Reservations.GuestNumber=@GuestNumber))
  INNER JOIN RESERVATIONMAIN WITH (NOLOCK) ON RESERVATIONMAIN.Referencenumber =  RESERVATIONS.ReferenceNumber
  INNER JOIN Customer WITH (NOLOCK) ON Customer.MarkAsDeleted = 0 And Customer.HotelNumber = 
  (
    CASE WHEN ISNULL(ReservationRoomLine.RoomNumber, 0) > 0 THEN
      (SELECT HotelNumber FROM Rooms WITH (NOLOCK) WHERE RoomNumber = ReservationRoomLine.RoomNumber)
    ELSE
      (SELECT HotelNumber FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber)
    END
  )
Open CancellationFeeResCur

Fetch Next From CancellationFeeResCur Into @ReferenceNumber, @TempGuestNumber, @CancellationFeePercent, @ResLineNumber, @HotelCustomerNumber

While (@@Fetch_Status=0)
begin
  if @UseCalcFeePercent > 0 set @CancellationFeePercent = @CalcFeePercent

  if IsNull(@Adults,0)=0     Set  @Adults = 0
  if IsNull(@Children1,0)=0  Set  @Children1 = 0
  if IsNull(@Children2,0)=0  Set  @Children2 = 0

  SET @Amount = 0
  SET @SideHouse = 0

  if @TempGuestNumber=0
  begin
    SELECT DISTINCT
      @AllDISCOUNTRATE = CASE WHEN RESERVATIONS.DiscountCustomerType = ReservationAddress.AddressType THEN RESERVATIONADDRESS.DISCOUNTRATE ELSE 0.0 END,
      @RoomDiscountRate = CASE WHEN RESERVATIONS.DiscountCustomerType = ReservationAddress.AddressType THEN RESERVATIONADDRESS.RoomDiscountRate ELSE 0.0 END
    FROM RESERVATIONROOMLINE WITH (NOLOCK) 
    INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER
      AND RESERVATIONROOMLINEXTAB.ReferenceNumber = @ReferenceNumber 
      AND RESERVATIONROOMLINEXTAB.GuestNumber = @TempGuestNumber 
    INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber 
      AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber
    INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK)  ON RESERVATIONADDRESSXTAB.GuestNumber = RESERVATIONS.GuestNumber 
      AND RESERVATIONADDRESSXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber
    INNER JOIN RESERVATIONADDRESS WITH (NOLOCK)  ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.AddressNumber
    WHERE RESERVATIONROOMLINE.LINENUMBER = @ResLineNumber
    AND (RESERVATIONADDRESS.AddressType = RESERVATIONS.DiscountCustomerType OR RESERVATIONS.DiscountCustomerType = 0 OR RESERVATIONS.DiscountCustomerType is null)

    SELECT DISTINCT
      @BookedTypeNumber = RESERVATIONROOMLINE.BookedTypeNumber,
      @RoomNumber = RESERVATIONROOMLINE.RoomNumber,
      @StayStartDateStr = RESERVATIONROOMLINE.ArrivalDate,
      @StayEndDateStr = RESERVATIONROOMLINE.DepartureDate,
      @Adults = RESERVATIONROOMLINE.Adults,
      @Children1 = RESERVATIONROOMLINE.Children1,
      @Children2 = RESERVATIONROOMLINE.Children2,
      @PaysAdults = RESERVATIONROOMLINE.Adults,
      @PaysChildren1 = RESERVATIONROOMLINE.Children1,
      @PaysChildren2 = RESERVATIONROOMLINE.Children2,
      @ProductNumber = RESERVATIONROOMLINE.ProductNumber,
      @Pieces = RESERVATIONROOMLINE.NumberOfRooms,
      @PriceOverwritten = RESERVATIONROOMLINE.PriceOverwritten,
      @Price = ISNULL(RESERVATIONROOMLINE.Price,0),
      @FirstPrice1 =  ISNULL(RESERVATIONROOMLINE.FirstPrice1,0),
      @PriceOverwritten2 = RESERVATIONROOMLINE.PriceOverwritten2,
      @FirstPrice2 =  ISNULL(RESERVATIONROOMLINE.FirstPrice2,0),
      @Day2 = RESERVATIONROOMLINE.Day2,
      @ProductNumber2 = RESERVATIONROOMLINE.ProductNumber2,
      @PriceOverwritten3 = RESERVATIONROOMLINE.PriceOverwritten3,
      @FirstPrice3 =  ISNULL(RESERVATIONROOMLINE.FirstPrice3,0),
      @Day3 = RESERVATIONROOMLINE.Day3,
      @ProductNumber3 = RESERVATIONROOMLINE.ProductNumber3,
      @PriceOverwritten4 = RESERVATIONROOMLINE.PriceOverwritten4,
      @FirstPrice4 =  ISNULL(RESERVATIONROOMLINE.FirstPrice4,0),
      @Day4 = RESERVATIONROOMLINE.Day4,
      @ProductNumber4 = RESERVATIONROOMLINE.ProductNumber4,
      @DiscountCustomerType = RESERVATIONS.DiscountCustomerType
    FROM RESERVATIONROOMLINE WITH (NOLOCK) 
    INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER
      AND RESERVATIONROOMLINEXTAB.ReferenceNumber = @ReferenceNumber 
      AND RESERVATIONROOMLINEXTAB.GuestNumber = @TempGuestNumber 
    INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber 
      AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber
    WHERE RESERVATIONROOMLINE.LINENUMBER = @ResLineNumber
  end
  else
  begin
    SELECT DISTINCT
      @AllDISCOUNTRATE = CASE WHEN RESERVATIONS.DiscountCustomerType = ReservationAddress.AddressType THEN RESERVATIONADDRESS.DISCOUNTRATE ELSE 0.0 END,
      @RoomDiscountRate = CASE WHEN RESERVATIONS.DiscountCustomerType = ReservationAddress.AddressType THEN RESERVATIONADDRESS.RoomDiscountRate ELSE 0.0 END
    FROM RESERVATIONROOMLINE WITH (NOLOCK) 
    INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER
    INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber 
      AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber
      AND RESERVATIONS.ReferenceNumber = @ReferenceNumber
      AND RESERVATIONS.GuestNumber = @TempGuestNumber
    INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK)  ON RESERVATIONADDRESSXTAB.GuestNumber = RESERVATIONS.GuestNumber AND RESERVATIONADDRESSXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber
    INNER JOIN RESERVATIONADDRESS WITH (NOLOCK)  ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.AddressNumber 
    WHERE ((ISNULL(@CalcCancelledRes,0) = 0) AND (ISNULL(RESERVATIONS.PaysAdults, 0) + ISNULL(RESERVATIONS.PaysChildren1, 0) + ISNULL(RESERVATIONS.PaysChildren2, 0) > 0)
      OR (ISNULL(@CalcCancelledRes,0) = 1) AND (ISNULL(RESERVATIONS.PaysAdults, 0) + ISNULL(RESERVATIONS.PaysChildren1, 0) + ISNULL(RESERVATIONS.PaysChildren2, 0) >= 0)) 
    AND ((RESERVATIONADDRESS.AddressType = RESERVATIONS.DiscountCustomerType) OR (RESERVATIONS.DiscountCustomerType IS NULL OR RESERVATIONS.DiscountCustomerType = 0))

    SELECT DISTINCT
      @BookedTypeNumber = RESERVATIONROOMLINE.BookedTypeNumber,
      @RoomNumber = RESERVATIONROOMLINE.RoomNumber,
      @StayStartDateStr = RESERVATIONS.ArrivalDate,
      @StayEndDateStr = RESERVATIONS.DepartureDate,
      @Adults = RESERVATIONS.Adults,
      @Children1 = RESERVATIONS.Children1,
      @Children2 = RESERVATIONS.Children2,
      @PaysAdults = RESERVATIONS.PaysAdults,
      @PaysChildren1 = RESERVATIONS.PaysChildren1,
      @PaysChildren2 = RESERVATIONS.PaysChildren2,
      @ProductNumber = RESERVATIONS.ProductNumber,
      @Pieces = RESERVATIONROOMLINE.NumberOfRooms,
      @PriceOverwritten = RESERVATIONS.PriceOverwritten,
      @Price = ISNULL(RESERVATIONS.Price,0),
      @FirstPrice1 = ISNULL(RESERVATIONS.FirstPrice1,0),
      @PriceOverwritten2 = RESERVATIONS.PriceOverwritten2,
      @FirstPrice2 = ISNULL(RESERVATIONS.FirstPrice2,0),
      @Day2 = RESERVATIONS.Day2,
      @ProductNumber2 = RESERVATIONS.ProductNumber2,
      @PriceOverwritten3 = RESERVATIONS.PriceOverwritten3,
      @FirstPrice3 = ISNULL(RESERVATIONS.FirstPrice3,0),
      @Day3 = RESERVATIONS.Day3,
      @ProductNumber3 = RESERVATIONS.ProductNumber3,
      @PriceOverwritten4 = RESERVATIONS.PriceOverwritten4,
      @FirstPrice4 = ISNULL(RESERVATIONS.FirstPrice4,0),
      @Day4 = RESERVATIONS.Day4,
      @ProductNumber4 = RESERVATIONS.ProductNumber4,
      @DiscountCustomerType = RESERVATIONS.DiscountCustomerType,
      @SideHouse = RESERVATIONS.SideHouse
    FROM RESERVATIONROOMLINE WITH (NOLOCK) 
    INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER
    INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber 
      AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber
      AND RESERVATIONS.ReferenceNumber = @ReferenceNumber
      AND RESERVATIONS.GuestNumber = @TempGuestNumber
    WHERE ((ISNULL(@CalcCancelledRes,0) = 0) AND (ISNULL(RESERVATIONS.PaysAdults, 0) + ISNULL(RESERVATIONS.PaysChildren1, 0) + ISNULL(RESERVATIONS.PaysChildren2, 0) > 0)
      OR (ISNULL(@CalcCancelledRes,0) = 1) AND (ISNULL(RESERVATIONS.PaysAdults, 0) + ISNULL(RESERVATIONS.PaysChildren1, 0) + ISNULL(RESERVATIONS.PaysChildren2, 0) >= 0)) 
  end

  IF @@ROWCOUNT>0
  BEGIN   
    if @UserIDNumber>-1
    begin
      if @AllGuestNumbers = 0 AND @ROOMNUMBER > 0 AND ISNULL(@VisualRoomNumber, '') = '' --@VisualRoomNumber = NULL
      begin      
        if @SideHouse =1
          SELECT @VisualRoomNumber = Number FROM Rooms WITH (NOLOCK) WHERE RoomNumber in (SELECT SideHouseNumber from Rooms WITH (NOLOCK)  WHERE RoomNumber = @ROOMNUMBER)
        else
          SELECT @VisualRoomNumber = Number FROM Rooms WITH (NOLOCK) WHERE RoomNumber = @ROOMNUMBER
      end
      else
        SET @VisualRoomNumber = NULL
    end

    IF ISNULL(@Day2,0) = 0               SET @Day2 = 0
    IF ISNULL(@Day3,0) = 0               SET @Day3 = 0
    IF ISNULL(@Day4,0) = 0               SET @Day4 = 0
    IF ISNULL(@AllDISCOUNTRATE,0)=0      SET @AllDISCOUNTRATE = 0
    IF ISNULL(@RoomDiscountRate,0)=0     SET @RoomDiscountRate = 0
    IF ISNULL(@DiscountCustomerType,0)=0 SET @DiscountCustomerType = 0

    IF @TempGuestNumber>0 AND @DiscountCustomerType=0 AND @DiscountCustomerTypeMainRes>0 AND (@AllGuestNumbers=1)
    begin
      SET @AllDISCOUNTRATE = @AllDISCOUNTRATEMainRes
      SET @RoomDiscountRate = @RoomDiscountRateMainRes
      SET @DiscountCustomerType = @DiscountCustomerTypeMainRes
    end

    if ((@Day2+@Day3+@Day4)>0)
      SET @StarProduct=1
    else
      SET @StarProduct=0

    Set @StayStartDate = (Convert (DATETIME, @StayStartDateStr))

    Select @NameListSetting = NameListSetting from ReservationRoomLine WITH (nolock) where LineNumber = @ResLineNumber

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

    IF @CalcPriceBasedOnOnePersonPaysForAll = 0
      SET @OnePersonPaysForAll = -1

    if @StarProduct=0
    begin
      if @ProductNumber>0
      begin
        if @TempGuestNumber>0
        begin
          SET @NameListReservationPerPersonProduct = @NameListSetting
          if @NameListReservationPerPersonProduct = 0 
            SET @NameListReservationPerPersonProduct = @NameListReservationPerPersonHotelSetup         
        end
        else
        begin
          Select @NameListReservationPerPersonProduct = NameListSetting from Products WITH (nolock) where productnumber=@ProductNumber
          if isnull(@NameListReservationPerPersonProduct,0) = 0 SET @NameListReservationPerPersonProduct = @NameListReservationPerPersonHotelSetup         
        end
      end
      else
        SET @NameListReservationPerPersonProduct = 0 

      if  ((@ProductNumber>0 AND @NameListReservationPerPersonProduct=1) OR ((@ProductNumber=0 OR @ProductNumber is null) AND @NameListReservationPerPersonHotelSetup=1)) AND @TempGuestNumber>0 -- only if namelist created
      begin
        SET @Adults = @PaysAdults
        SET @Children1 = @PaysChildren1
        SET @Children2 = @PaysChildren2
      end               

      if @PriceOverwritten = 0 and (@ProductNumber=0 or @ProductNumber is null)
        SET @PriceOverwritten = 1

      SET @StayEndDate = Convert(DateTime, @StayEndDateStr) 

      SET @NumberOfNights = Convert(Int,(@StayEndDate - @StayStartDate))

      IF @NumberOfNights = 0
        SET @NumberOfNights = 1

      if @PriceOverwritten = 1
      begin
        if @NameListReservationPerPersonProduct = 0
          SET @NameListReservationPerPersonProduct =  @NameListReservationPerPersonHotelSetup

        SET @Amount = Convert(Money, (@Price*@NumberOfNights*@Pieces))

        if @NameListReservationPerPersonProduct = 1
          if @OnePersonPaysForAll = 0 
            SET @Amount = @Amount * (@Adults + @Children1+ @Children2)

        if @AddonIfPriceOverwritten = 1 AND @ProductNumber>0
        begin
          EXEC CalcAddonPrice 0, @StayStartDate, @NumberOfNights, @Adults , @Children1, @Children2, @ProductNumber, @BookedTypeNumber, @AddonIfPriceOverwrittenAmount OUTPUT
          SET @Amount = @Amount + (@Pieces * @AddonIfPriceOverwrittenAmount)
        end
      end
      else
      begin
        exec ProductPrices @ProductNumber, @StayStartDate, @StayEndDate, @BookedTypeNumber, @Pieces, @Adults, @Children1, @Children2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
        --print convert(char,@CalcTotalPrice)
        SET @Amount = @CalcTotalPrice
      end
    end
    else
    begin
      --starproduct
      SET @AmountDay1 = 0
      SET @AmountDay2 = 0
      SET @AmountDay3 = 0
      SET @AmountDay4 = 0

      if isnull(@NameListSetting,0) = 0 
        SET @NameListSetting = 0

      if @NameListSetting = 1  -- 1 = per person, 2 = per room
      begin
        SET @Adults = @PaysAdults
        SET @Children1 = @PaysChildren1
        SET @Children2 = @PaysChildren2
      end

      SET @StayEndDate = Convert (DATETIME, @StayStartDateStr)+@Day2-1
      if @PriceOverwritten = 0 and (@ProductNumber=0 or @ProductNumber is null)  
        SET @PriceOverwritten = 1

      if @PriceOverwritten = 1
      begin
        SET @AmountDay1 = Convert(Money, (@Pieces*@Price*Convert(Int,(@StayEndDate - @StayStartDate))))

        if @NameListSetting = 1
          if @OnePersonPaysForAll = 0 
            SET @AmountDay1 = @AmountDay1 * (@Adults + @Children1+ @Children2)

        if @AddonIfPriceOverwritten = 1 AND @ProductNumber>0
        begin
          SET @NumberOfNights = Convert(Int,(@StayEndDate - @StayStartDate))
          EXEC CalcAddonPrice 0, @StayStartDate, @NumberOfNights, @Adults , @Children1, @Children2, @ProductNumber, @BookedTypeNumber, @AddonIfPriceOverwrittenAmount OUTPUT
          SET @AmountDay1 = @AmountDay1 + (@Pieces * @AddonIfPriceOverwrittenAmount)
        end
      end
      else
      begin
        exec ProductPrices @ProductNumber, @StayStartDate, @StayEndDate, @BookedTypeNumber, @Pieces, @Adults, @Children1, @Children2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT, 0, 0, 1, @NameListSetting
        SET @AmountDay1 = @CalcTotalPrice
      end 

      if (@Day3=0)
      begin
        Set @StayStartDate = (Convert (DATETIME, @StayStartDateStr)+@Day2-1)
        SET @StayEndDate = Convert(DateTime, @StayEndDateStr) 
        if @PriceOverwritten2 = 0 and (@ProductNumber2=0 or @ProductNumber2 is null)  
          SET @PriceOverwritten2 = 1

        if @PriceOverwritten2 = 1
        begin
          SET @AmountDay2 = @AmountDay2 + Convert(Money, (@Pieces*@FirstPrice2*Convert(Int,(@StayEndDate - @StayStartDate))))

          if @NameListSetting = 1
            if @OnePersonPaysForAll = 0 
              SET @AmountDay2 = @AmountDay2 * (@Adults + @Children1+ @Children2)

          if @AddonIfPriceOverwritten = 1 AND @ProductNumber2>0
          begin
            SET @NumberOfNights = Convert(Int,(@StayEndDate - @StayStartDate))
            EXEC CalcAddonPrice 0, @StayStartDate, @NumberOfNights, @Adults , @Children1, @Children2, @ProductNumber2, @BookedTypeNumber, @AddonIfPriceOverwrittenAmount OUTPUT
            SET @AmountDay2 = @AmountDay2 + (@Pieces * @AddonIfPriceOverwrittenAmount)
          end
        end
        else
        begin
          exec ProductPrices @ProductNumber2, @StayStartDate, @StayEndDate, @BookedTypeNumber, @Pieces, @Adults, @Children1, @Children2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT, 0, 0, 1, @NameListSetting
          SET @AmountDay2 = @AmountDay2 + @CalcTotalPrice
        end
      end
      else
      begin
        Set @StayStartDate = Convert (DATETIME, @StayStartDateStr)+@Day2-1
        SET @StayEndDate = Convert(DateTime, @StayStartDateStr) + @Day3-1

        if @PriceOverwritten2 = 0 and (@ProductNumber2=0 or @ProductNumber2 is null)  
           SET @PriceOverwritten2 = 1

        if @PriceOverwritten2 = 1
        begin
          SET @AmountDay2 = @AmountDay2 + Convert(Money, (@Pieces*@FirstPrice2*Convert(Int,(@StayEndDate - @StayStartDate))))
          if @NameListSetting = 1
            if @OnePersonPaysForAll = 0 
              SET @AmountDay2 = @AmountDay2 * (@Adults + @Children1+ @Children2)

          if @AddonIfPriceOverwritten = 1 AND @ProductNumber2>0
          begin
            SET @NumberOfNights = Convert(Int,(@StayEndDate - @StayStartDate))
            EXEC CalcAddonPrice 0, @StayStartDate, @NumberOfNights, @Adults , @Children1, @Children2, @ProductNumber2, @BookedTypeNumber, @AddonIfPriceOverwrittenAmount OUTPUT
            SET @AmountDay2 = @AmountDay2 + (@Pieces * @AddonIfPriceOverwrittenAmount)
          end
        end
        else
        begin
          exec ProductPrices @ProductNumber2, @StayStartDate, @StayEndDate, @BookedTypeNumber, @Pieces, @Adults, @Children1, @Children2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT, 0, 0, 1, @NameListSetting
          SET @AmountDay2 = @AmountDay2 + @CalcTotalPrice
        end

        if (@Day4=0)
        begin
          Set @StayStartDate = (Convert (DATETIME, @StayStartDateStr)+@Day3-1)
          Set @StayEndDate =  Convert (DATETIME, @StayEndDateStr)

          if @PriceOverwritten3 = 0 and (@ProductNumber3=0 or @ProductNumber3 is null)  
            SET @PriceOverwritten3 = 1

          if @PriceOverwritten3 = 1 OR ((@AddonIfPriceOverwritten=1) AND (@PriceOverwritten3 = 1))
          begin
            SET @AmountDay3 = @AmountDay3 + Convert(Money, (@Pieces*@FirstPrice3*Convert(Int,(@StayEndDate - @StayStartDate))))
            if @NameListSetting = 1
              if @OnePersonPaysForAll = 0 
                SET @AmountDay3 = @AmountDay3 * (@Adults + @Children1+ @Children2)

            if @AddonIfPriceOverwritten = 1 AND @ProductNumber3>0
            begin
              SET @NumberOfNights = Convert(Int,(@StayEndDate - @StayStartDate))
              EXEC CalcAddonPrice 0, @StayStartDate, @NumberOfNights, @Adults , @Children1, @Children2, @ProductNumber3, @BookedTypeNumber, @AddonIfPriceOverwrittenAmount OUTPUT
              SET @AmountDay3 = @AmountDay3 + (@Pieces * @AddonIfPriceOverwrittenAmount)
            end
          end
          else
          begin
            exec ProductPrices @ProductNumber3, @StayStartDate, @StayEndDate, @BookedTypeNumber, @Pieces, @Adults, @Children1, @Children2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT, 0, 0, 1, @NameListSetting
            SET @AmountDay3 = @AmountDay3 + @CalcTotalPrice
          end 
        end  
        else
        begin
          Set @StayStartDate = (Convert (DATETIME, @StayStartDateStr)+@Day3-1)

          if @PriceOverwritten3 = 0 and (@ProductNumber3=0 or @ProductNumber3 is null)  
            SET @PriceOverwritten3 = 1

          if @PriceOverwritten3 = 1 OR ((@AddonIfPriceOverwritten=1) AND (@PriceOverwritten3 = 1))
          begin
            SET @StayEndDate = Convert(DateTime, @StayStartDateStr) + @Day4-1
            SET @AmountDay3 = @AmountDay3 + Convert(Money, (@Pieces*@FirstPrice3*Convert(Int,(@StayEndDate - @StayStartDate)))) 
            if @NameListSetting = 1
              if @OnePersonPaysForAll = 0 
                SET @AmountDay3 = @AmountDay3 * (@Adults + @Children1+ @Children2)

            if @AddonIfPriceOverwritten = 1 AND @ProductNumber3>0
            begin
              SET @NumberOfNights = Convert(Int,(@StayEndDate - @StayStartDate))
              EXEC CalcAddonPrice 0, @StayStartDate, @NumberOfNights, @Adults , @Children1, @Children2, @ProductNumber3, @BookedTypeNumber, @AddonIfPriceOverwrittenAmount OUTPUT
              SET @AmountDay3 = @AmountDay3 + (@Pieces * @AddonIfPriceOverwrittenAmount)
            end
          end
          else
          begin
            Set @StayEndDate =  Convert (DATETIME, @StayStartDateStr)+@Day4-1
            exec ProductPrices @ProductNumber3, @StayStartDate, @StayEndDate, @BookedTypeNumber, @Pieces, @Adults, @Children1, @Children2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT, 0, 0, 1, @NameListSetting
            SET @AmountDay3 = @AmountDay3 + @CalcTotalPrice
          end

          Set @StayStartDate = (Convert (DATETIME, @StayStartDateStr)+@Day4-1)

          if @PriceOverwritten4= 0 and (@ProductNumber4=0 or @ProductNumber4 is null)  
            SET @PriceOverwritten4 = 1

          if @PriceOverwritten4 = 1
          begin
            SET @StayEndDate = Convert(DateTime, @StayEndDateStr)
            SET @AmountDay4 = @AmountDay4 + Convert(Money, (@Pieces*@FirstPrice4*Convert(Int,(@StayEndDate - @StayStartDate))))

            if @NameListSetting = 1
              if @OnePersonPaysForAll = 0 
                SET @AmountDay4 = @AmountDay4 * (@Adults + @Children1+ @Children2)

            if @AddonIfPriceOverwritten = 1 AND @ProductNumber4>0
            begin
              SET @NumberOfNights = Convert(Int,(@StayEndDate - @StayStartDate))
              EXEC CalcAddonPrice 0, @StayStartDate, @NumberOfNights, @Adults , @Children1, @Children2, @ProductNumber4, @BookedTypeNumber, @AddonIfPriceOverwrittenAmount OUTPUT
              SET @AmountDay4 = @AmountDay4 + (@Pieces * @AddonIfPriceOverwrittenAmount)
            end
          end
          else
          begin
            Set @StayEndDate =  Convert (DATETIME, @StayEndDateStr)
            exec ProductPrices @ProductNumber4, @StayStartDate, @StayEndDate, @BookedTypeNumber, @Pieces, @Adults, @Children1, @Children2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT, 0, 0, 1, @NameListSetting
            SET @AmountDay4 = @AmountDay4 + @CalcTotalPrice
          end
        end
      end
      SET @Amount = ISNULL(@AmountDay1, 0) + ISNULL(@AmountDay2, 0) + ISNULL(@AmountDay3, 0) + ISNULL(@AmountDay4, 0)
    end

    if @RoomDiscountRate>0 AND @DiscountCustomerType>0
      SET @Amount = @Amount - convert(money,@Amount*Convert(Float,(@RoomDiscountRate/100))) 

    if @AllDiscountRate>0 AND @DiscountCustomerType>0
      SET @Amount = @Amount - convert(money,@Amount*Convert(Float,(@AllDiscountRate/100))) 

    if @CancellationFeePercent>0
    begin
      if @CancellationFeeInPercent = 1
        SET @CancellationFeeAmount = @CancellationFeeAmount + Convert(money,@Amount*(@CancellationFeePercent/100))
      else
      begin
        if @CancellationFeeAmount=0
          SET @CancellationFeeAmount = @CancellationFeeAmount + Convert(money,@CancellationFeePercent)
      end 
    end

--    PRINT 'Room subtotal: ' + convert(varchar, @Amount)
    SET @TotalAmount = @TotalAmount + @Amount
  END
  Fetch Next From CancellationFeeResCur
  Into @ReferenceNumber, @TempGuestNumber,@CancellationFeePercent, @ResLineNumber, @HotelCustomerNumber
end

--PRINT 'Rooms total: ' + convert(varchar, @TotalAmount)

Close CancellationFeeResCur
Deallocate CancellationFeeResCur

-- find summen af halls/daymeetings Start

SELECT DISTINCT
  @AllDISCOUNTRATE = CASE WHEN RESERVATIONS.DiscountCustomerType = ReservationAddress.AddressType THEN RESERVATIONADDRESS.DISCOUNTRATE ELSE 0.0 END,
  @MeetingDiscountRate = CASE WHEN RESERVATIONS.DiscountCustomerType = ReservationAddress.AddressType THEN RESERVATIONADDRESS.MeetingDiscountRate ELSE 0.0 END,
  @HallDiscountRate = CASE WHEN RESERVATIONS.DiscountCustomerType = ReservationAddress.AddressType THEN RESERVATIONADDRESS.HallDiscountRate ELSE 0.0 END
from RESERVATIONConferenceLINE WITH (NOLOCK) 
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber=RESERVATIONConferenceLINE.ReferenceNumber 
  AND Reservations.GuestNumber=ISNULL(RESERVATIONConferenceLINE.GuestNo,0) 
  AND Reservations.Status <= @resstatus
  AND RESERVATIONConferenceLINE.ReferenceNumber = @ReferenceNumber 
  AND Reservations.GuestNumber = @GuestNumber 
INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK)  ON RESERVATIONADDRESSXTAB.GuestNumber = RESERVATIONS.GuestNumber 
  AND RESERVATIONADDRESSXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber
INNER JOIN RESERVATIONADDRESS WITH (NOLOCK)  ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.AddressNumber
INNER JOIN RESERVATIONMAIN WITH (NOLOCK) ON RESERVATIONMAIN.Referencenumber =  RESERVATIONS.ReferenceNumber 
Left outer join Customer WITH (NOLOCK) ON RESERVATIONMAIN.HotelNumber = Customer.HotelNumber 
  AND Customer.HotelNumber>=0 AND Customer.CustomerTypeNumber = 7 AND ISNULL(Customer.MarkAsDeleted, 0) = 0
WHERE ISNULL(RESERVATIONConferenceLINE.SchemePattern, 0) = 0
AND (RESERVATIONADDRESS.AddressType = RESERVATIONS.DiscountCustomerType OR RESERVATIONS.DiscountCustomerType IS NULL OR RESERVATIONS.DiscountCustomerType = 0)

SET @PrevLineNumber = -1
Declare CancellationFeeResCur1 Cursor Fast_Forward Read_Only For
SELECT DISTINCT
  RESERVATIONConferenceLINE.ConferenceRoomNumber,
  RESERVATIONConferenceLINE.ReservationConfLineNumber,
  RESERVATIONConferenceLINE.BookedConferenceRoomTypeNumber,
  RESERVATIONConferenceLINE.ArrivalDate,
  RESERVATIONConferenceLINE.DepartureDate,
  RESERVATIONConferenceLINE.DepartureTime, 
  RESERVATIONConferenceLINE.ProductNumber,
  ISNULL(RESERVATIONConferenceLINE.NumberOfRooms,1),
  RESERVATIONConferenceLINE.PriceOverWritten,
  RESERVATIONConferenceLINE.Adults,
  RESERVATIONConferenceLINE.Children1,
  RESERVATIONConferenceLINE.Children2,
  RESERVATIONConferenceLINE.Price, -- Total
  Reservations.CancellationPercent,
  RESERVATIONS.DiscountCustomerType,
  isnull(Customer.CustomerNumber,-1) 
from RESERVATIONConferenceLINE WITH (NOLOCK) 
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber=RESERVATIONConferenceLINE.ReferenceNumber 
  AND Reservations.GuestNumber=ISNULL(RESERVATIONConferenceLINE.GuestNo,0) 
  AND Reservations.Status <= @resstatus
  AND RESERVATIONConferenceLINE.ReferenceNumber = @ReferenceNumber 
  AND Reservations.GuestNumber = @GuestNumber 
INNER JOIN RESERVATIONMAIN WITH (NOLOCK) ON RESERVATIONMAIN.Referencenumber =  RESERVATIONS.ReferenceNumber 
Left outer join Customer WITH (NOLOCK) ON RESERVATIONMAIN.HotelNumber = Customer.HotelNumber 
  AND Customer.HotelNumber>=0 AND Customer.CustomerTypeNumber = 7 AND ISNULL(Customer.MarkAsDeleted, 0) = 0
WHERE ISNULL(RESERVATIONConferenceLINE.SchemePattern, 0) = 0

Open CancellationFeeResCur1

Fetch Next From CancellationFeeResCur1
Into @HallNumber, @ReservationConfLineNumber, @BookedTypeNumber, @StayStartDateStr, @StayEndDateStr , @DepartureTime, @ProductNumber, @Pieces, @PriceOverWritten, @Adults, @Children1, @Children2, @Price, @CancellationFeePercent, @DiscountCustomerType, @HotelCustomerNumber

if @UseCalcFeePercent > 0 set @CancellationFeePercent = @CalcFeePercent

While (@@Fetch_Status=0)
begin
  if @PrevLineNumber<>@ReservationConfLineNumber
  begin
    if IsNull(@Adults,0)=0     Set  @Adults = 0
    if IsNull(@Children1,0)=0  Set  @Children1 = 0
    if IsNull(@Children2,0)=0  Set  @Children2 = 0

    if @HallNumber > 0 AND @VisualHallNumber = NULL  -- TODO exchange with RoomNumberID when table has been nomalized
      SELECT  @VisualHallNumber = Number FROM ConferenceRooms WITH (NOLOCK)  WHERE ConferenceRoomNumber = @HallNumber  -- TODO use primary key instead! Then select is not needed anymore!
    else
      SET @VisualHallNumber = NULL

    IF ISNULL(@AllDISCOUNTRATE,0)=0      SET @AllDISCOUNTRATE = 0
    IF ISNULL(@MeetingDiscountRate,0)=0  SET @MeetingDiscountRate = 0
    IF ISNULL(@HallDiscountRate,0)=0     SET @HallDiscountRate = 0
    IF ISNULL(@ProductNumber,0)=0        SET @ProductNumber = 0
    IF ISNULL(@DiscountCustomerType,0)=0 SET @DiscountCustomerType = 0

    IF @TempGuestNumber>0 AND @DiscountCustomerType=0 AND @DiscountCustomerTypeMainRes>0 AND (@AllGuestNumbers=1)
    begin
      SET @AllDISCOUNTRATE = @AllDISCOUNTRATEMainRes
      SET @HallDiscountRate = @HallDiscountRateMainRes
      SET @MeetingDiscountRate = @MeetingDiscountRateMainRes
      SET @DiscountCustomerType = @DiscountCustomerTypeMainRes
    end

    SET @StayStartDate = Convert(DateTime, @StayStartDateStr)

    if  isNull(@BookedTypeNumber,0)=0
      SET @StayEndDate = convert(char(8),Convert(datetime,@StayEndDateStr) - 1,112)
    else
    begin
      if (@Arr24HoursStarts> @DepartureTime)          
        SET @StayEndDateStr = Convert(Char(8),Convert(DateTime,@StayEndDateStr)-1,112)
      SET @StayEndDate = Convert(DateTime, @StayEndDateStr)
    end

    if @PriceOverwritten = 0 and (@ProductNumber=0 or @ProductNumber is null)  
      SET @PriceOverwritten = 1

    if (@ProductNumber>0)
      Select @FixedPrice=FixedPrice from Products WITH (NOLOCK) where ProductNumber=@ProductNumber
    else
      SET @FixedPrice = 0

    if @PriceOverWritten=1
    begin
      SET @NumberOfNights = Convert(Int,(@StayEndDate - @StayStartDate)+1)
      if @NumberOfNights = 0 
        SET @NumberOfNights = 1
 
      if @FixedPrice=1
      begin
        if isnull(@Price,0)=0 
          SET @Price = 0
        SET @Amount = @Price*@Pieces*@NumberOfNights
      end
      else
      begin
        if isnull(@Price,0)=0 
          SET @Price = 0
        SET @Amount = Convert(Money, (@Pieces*@Price*(@Adults + @Children1 + @Children2)*@NumberOfNights))
      end
    end
    else
    begin
      exec ProductPrices @ProductNumber, @StayStartDate, @StayEndDate, @BookedTypeNumber, @Pieces, @Adults, @Children1, @Children2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
      SET @Amount = @CalcTotalPrice
    end

    if  @FixedPrice=0 AND @MeetingDiscountRate>0 AND @DiscountCustomerType>0
      SET @Amount = @Amount - convert(money,@Amount*Convert(Float,(@MeetingDiscountRate/100))) 
    else
    if  @FixedPrice=1 AND @HallDiscountRate>0 AND @DiscountCustomerType>0
      SET @Amount = @Amount - convert(money,@Amount*Convert(Float,(@HallDiscountRate/100))) 
  end

  if @AllDiscountRate>0 AND @DiscountCustomerType>0
    SET @Amount = @Amount - convert(money,@Amount*Convert(Float,(@AllDiscountRate/100))) 
  else
    if @PrevLineNumber = @ReservationConfLineNumber SET @Amount = 0

  SET @TotalAmount = @TotalAmount + @Amount

  if @CancellationFeePercent>0
  begin
    if @CancellationFeeInPercent = 1
      SET @CancellationFeeAmount = @CancellationFeeAmount + Convert(money,@Amount*(@CancellationFeePercent/100))
    else
    begin
      if @CancellationFeeAmount=0
        SET @CancellationFeeAmount = @CancellationFeeAmount + Convert(money,@CancellationFeePercent)
    end
  end

  SET @PrevLineNumber = @ReservationConfLineNumber
  Fetch Next From CancellationFeeResCur1
  Into @HallNumber, @ReservationConfLineNumber, @BookedTypeNumber, @StayStartDateStr, @StayEndDateStr , @DepartureTime, @ProductNumber, @Pieces, @PriceOverWritten, @Adults, @Children1, @Children2, @Price, @CancellationFeePercent, @DiscountCustomerType, @HotelCustomerNumber
end

Close CancellationFeeResCur1
Deallocate CancellationFeeResCur1
  -- find summen af halls/daymeetings End

-- find summen af manuelle linier Start
Select DISTINCT
  @AllDiscountRate = CASE WHEN Reservations.DiscountCustomerType = ReservationAddress.AddressType THEN RESERVATIONADDRESS.DISCOUNTRATE ELSE 0.0 END
from RESERVATIONS WITH (NOLOCK) 
INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESSXTAB.GuestNumber = RESERVATIONS.GuestNumber 
  AND RESERVATIONADDRESSXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber
INNER JOIN RESERVATIONADDRESS WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.AddressNumber
WHERE Reservations.ReferenceNumber=@ReferenceNumber 
AND (@AllGuestNumbers=1 OR Reservations.GuestNumber=@GuestNumber)  
AND (RESERVATIONADDRESS.AddressType = RESERVATIONS.DiscountCustomerType OR (RESERVATIONS.DiscountCustomerType IS NULL OR  RESERVATIONS.DiscountCustomerType=0))
AND Reservations.Status<=@resstatus
  
Declare CancellationFeeArrResCur Cursor FAST_Forward Read_Only For
  SELECT DISTINCT
    RESERVATIONS.GuestNumber,
    Splitbill, 
    CancellationPercent, 
    ArrivalDate, 
    DepartureDate,
    Reservations.DiscountCustomerType
  FROM Reservations WITH (NOLOCK) 
  WHERE Reservations.ReferenceNumber=@ReferenceNumber 
  AND (@AllGuestNumbers=1 OR Reservations.GuestNumber=@GuestNumber)  
  AND Reservations.Status<=@resstatus
Open CancellationFeeArrResCur

Fetch Next From CancellationFeeArrResCur
Into @TempGuestNumber, @SplitbillNumber, @CancellationFeePercent, @StayStartDateStr, @StayEndDateStr, @DiscountCustomerType

if @UseCalcFeePercent > 0 set @CancellationFeePercent = @CalcFeePercent

While (@@Fetch_Status=0)
Begin
  IF ISNULL(@AllDISCOUNTRATE, 0) = 0 SET @AllDISCOUNTRATE = 0
  IF ISNULL(@ExtraDiscountRate, 0) = 0 SET @ExtraDiscountRate = 0
  IF ISNULL(@DiscountCustomerType,0)=0 SET @DiscountCustomerType = 0

  IF @TempGuestNumber>0 AND @DiscountCustomerType=0 AND @DiscountCustomerTypeMainRes>0 AND (@AllGuestNumbers=1)
  begin
    SET @AllDISCOUNTRATE = @AllDISCOUNTRATEMainRes
    SET @ExtraDiscountRate = @ExtraDiscountRateMainRes
    SET @DiscountCustomerType = @DiscountCustomerTypeMainRes
  end

  Declare CancellationFeeResCur2 Cursor FAST_Forward Read_Only For
    SELECT Pieces, Price, WareNumber
    FROM arrangementlines WITH (NOLOCK) 
    INNER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = ArrangementLines.FK_ExDepartment
    INNER JOIN ArrangementReservationsXTab WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
    LEFT OUTER JOIN ArrangementRoomXTab WITH (NOLOCK) ON ArrangementRoomXTab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
    WHERE ArrangementReservationsXtab.ReferenceNumber = @ReferenceNumber 
    AND ArrangementReservationsXtab.GuestNumber = @TempGuestNumber 
    AND ((ArrangementLines.Date >= @StayStartDateStr) AND (ArrangementLines.Date <= @StayEndDateStr))
    AND ArrangementRoomXTab.ArrangementLineNumber IS NULL 
    AND ISNULL(ArrangementLines.PacketItemNumber, 0) = 0
  Open CancellationFeeResCur2

  Fetch Next From CancellationFeeResCur2 Into @Pieces, @Price, @ArrangementWareNumber

  While (@@Fetch_Status=0)
  Begin
    IF isnull(@Pieces,0)=0 SET @Pieces=0
    IF isnull(@Price,0)=0 SET @Price=0

    SET @Amount = (@Price * @Pieces)

    EXEC GetDiscountRateWare @ReferenceNumber, @TempGuestNumber, @ArrangementWareNumber, @DiscountCustomerType, @AllDiscountRate OUTPUT, @DiscountIsFixedAmount OUTPUT

    IF @AllDiscountRate > 0
    BEGIN
      IF @DiscountIsFixedAmount = 1
        SET @Amount = @Amount - (@Pieces * @AllDiscountRate)
      ELSE
        SET @Amount = @Amount - ROUND(((@Amount) * (@AllDiscountRate/100)), 2)
    END

    SET @TotalAmountExtra = @TotalAmountExtra + @Amount

    Fetch Next From CancellationFeeResCur2 Into @Pieces, @Price, @ArrangementWareNumber
  end
  Close CancellationFeeResCur2
  Deallocate CancellationFeeResCur2

  Fetch Next From CancellationFeeArrResCur
  Into @TempGuestNumber, @SplitbillNumber, @CancellationFeePercent, @StayStartDateStr, @StayEndDateStr, @DiscountCustomerType
end

Close CancellationFeeArrResCur
Deallocate CancellationFeeArrResCur

if @CancellationFeePercent>0 AND @NoCancellationFeeOnExtras = 0
begin
  if @CancellationFeeInPercent = 1
    SET @CancellationFeeAmount = @CancellationFeeAmount + Convert(money,(@TotalAmountExtra)*(@CancellationFeePercent/100))
  else
  if @CancellationFeeAmount=0
    SET @CancellationFeeAmount = @CancellationFeeAmount + Convert(money,(@CancellationFeePercent))
end

-- find summen af manuelle linier End
SET @TotalAmount = @TotalAmount + @TotalAmountExtra

IF ISNULL(@CancellationFeeAmount,0) = 0 SET @CancellationFeeAmount=0
IF ISNULL(@TotalAmount,0) = 0 SET @TotalAmount=0

SET @CancellationFeeAmount = Round(@CancellationFeeAmount, @NumberOfDecimalsInPrices)
SET @TotalAmount = Round(@TotalAmount,@NumberOfDecimalsInPrices)

if ((@UserIDNumber > -1) AND (@CountryNumber > -1)) AND (@CancellationFeeAmount>0) -- charge fee
begin
  Declare @WareNumber int
  Declare @FolioNumber int
  Declare @AddressType tinyint
  Declare @TransNrShowOnBillOnAdd int
  Declare @wareText varchar(50)
  Declare @NoteText varchar(10) 

  SET @WareNumber = 0

  Select @wareText = WareName, @WareNumber = WareNumber from Ware WITH (NOLOCK)    inner join WareGroups WITH (nolock) on Ware.WareGroupNumber=WareGroups.WareGroupNumber
  where Ware.SortNumber=1 and WareGroups.Payment=4 and (Ware.MarkAsDeleted=0 or Ware.MarkAsDeleted is null) AND (WareGroups.MarkAsDeleted='0' or waregroups.MarkAsDeleted is null)

  if @WareNumber>0
  begin
  /*
    check to se if cancellation fee allready is charged.
    date should be arrivaldate of the reservation.
  */
 
    Exec FindFolioNumber @ReferenceNumber, @GuestNumber, @UserIDNumber, @WareNumber, @SplitbillNumber, 1, @FolioNumber OUTPUT, @AddressType OUTPUT, @FirstAddressNumber

    if @VisualRoomNumber>0 
      SET @NoteText = Convert(char,@VisualRoomNumber)
    else if @VisualHallNumber>0 
      SET @NoteText = Convert(char,@VisualHallNumber)
    else
      SET @NoteText = ''

    SELECT @cnt = COUNT(*) FROM Transactions WITH (NOLOCK) 
    WHERE FolioNumber = @FolioNumber
    AND WareNumber = @WareNumber
    AND GuestNumber = @GuestNumber    
    AND (ISNULL(Note, '') = '' OR Note = @NoteText)

    IF ISNULL(@cnt, 0) = 0
    BEGIN
      IF ISNULL(@HotelCustomerNumber, 0) <= 0
      BEGIN
        SELECT @HotelCustomerNumber = Customer.CustomerNumber 
        FROM Customer WITH (NOLOCK) 
        WHERE HotelNumber IN (SELECT HotelNumber FROM ReservationMain WITH(NOLOCK) WHERE ReferenceNumber = @ReferenceNumber)
        AND CustomerTypeNumber = 7
        AND ISNULL(Customer.MarkAsDeleted, 0) = 0
      END
      EXEC SystemDateTime @StayStartDateStr, @PostForDateTime OUTPUT
      Exec Postering @PostForDateTime, 0, 1, 0, 0, 0, @UserIDNumber, @FolioNumber, 0, 0, 0, 0, @wareText, 1, @CancellationFeeAmount, @CountryNumber, 0, 0 , 0, 0, @FK_Department, @WareNumber, 0, 0 , 0, 0, @NoteText, @TransNrShowOnBillOnAdd OUTPUT, @HotelCustomerNumber
    END
  end
end