CREATE PROCEDURE DBO.ProductPrices @OldProductNumber int, @StartDate datetime, @EndDate datetime, @OldRoomTypeNumber int, @NumberOfRooms int, 
  @AdultPax int, @Child1Pax int, @Child2Pax int, @CalcTotalPrice real OUTPUT, @CalcArrivalPrice real OUTPUT, @AddonAmount smallmoney = 0 OUTPUT, @AddonVatAmount smallmoney=0 OUTPUT,
  @PlusProduct BIT=0, @OverrideNameListSetting INT = 0, @ACustomerNumber INT = 0, @ARateCap Money = 0, @AFlatDiscount Money = 0,
  @AForcePriceoverwrite BIT = 0 OUTPUT, @AHotelNumber INT = -1, @AAdultPriceIsZero BIT = 0 OUTPUT AS
SET NOCOUNT ON

/*Created by Jacob Pedersen

  KJ 20210920: Bug: Fix for "Do not return no prices if no price on departure date"
    KJ 20191015: Ext: Return if Adult price is zero.
    KJ 20141219: Ext: Changed datatype for prices. Handle amount > 214.000.
    KJ 20131014: ProductPrice Rewrite.
    nb20090320 disabled prints
    AV 2007 1109 SET @OverrideNameListSetting = ISNULL(@OverrideNameListSetting, 0)
    TL 20071108 Implemented option to override namelist setting with the one from the reservation (plus products)
    AV 2006 1106 PricePerType implemented on DayProducts
    3.36
    JP 20060420 BUG: fix for wrong arrival price possibly caused by change 20060307
    JP 20060307 BUG/OPTIMIZE: Better SQL WHERE clause matching primary index. Possible fix for weird lock errors
    3.35
    JP 20051027: BUG: Changed variables from smallmoney to money because of internal MSSQL calculation bug.  Money1 = Money1 + (SmallMoney * Integer). Because SmallMoney is used it will use this as a temp result instead of using Money which is the end result.
nb20050823: AddonVat calculation
    JP 20050502 changed: money to smallmoney. removed productpricenumber
    JP 20050128 changed: KA wants children2 on dayproducts
    Last Update: 22-03-2004 JP Fix for Addons så den tager højde for FirstPersonPays problemet (Kobæk res 609 og Dan sag #73713)
    Last Update: 16-03-2004 JP Fix for Addons kobæk. Medfører fejl for DAN (6/8-03) Afventer email fra ham
    Last Update: 01-03-2004 nb Extension with AddOnAmount and AddonVatAmount
    Last Update: 04-09-2003 Jacob Fix for NameListSetting
    Last Update: 06-08-2003 Jacob Fix for Addon per person
    Last Update: 21-07-2003 Spider  Rettet select @var = data to set @var = data
    Last Update: 26-06-2003 Jacob Pedersen Fix for OnePersonPaysForAll=0
    Last Update: 16-01-2003 Jacob Pedersen cursor changed to fast_forward for speed
    Last Update: 17-10-2002 Jacob Pedersen smallmoneyfields
    Last Update: 23-09-2002 Jacob Pedersen Correct price for +product when changes on departuredate
    Last Update: 26-11-2002 Jacob Pedersen New addon fields
    Last Update: 05-12-2002 Jacob Pedersen Fixes for addon calculation

    NOTE: See JP 20051027 fix for info on Money contra SmallMoney fields!
*/
-- NOTES:
-- * Input: OLD values are called this so that they do not conflict with the fields in the table
-- * For star-products you have to call this routine once per line
-- @OverrideNameListSetting: 0 = Use HotelSetup setting, 1 = Namelist per person, 2 = Namelist per room 
DECLARE @ShowPrint BIT
SET @ShowPrint = 0

IF @ShowPrint = 1 
BEGIN
  DECLARE @Msg VARCHAR(2000) 
  PRINT 'Call SP ProductPrices begin --------------------------------'
  SET @Msg = 'DECLARE @OldProductNumber int, @StartDate datetime, @EndDate datetime, @OldRoomTypeNumber int, @NumberOfRooms int, @AdultPax int, @Child1Pax int, @Child2Pax int, @CalcTotalPrice real, @CalcArrivalPrice real, @AddonAmount smallmoney, @AddonVatAmount smallmoney, @PlusProduct BIT, @OverrideNameListSetting INT, @ACustomerNumber INT'+CHAR(13)
  IF ISNULL( @OldProductNumber, -1) = -1 
    SET @Msg = @Msg+'SET @OldProductNumber = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @OldProductNumber = '+CONVERT(VARCHAR,@OldProductNumber)+CHAR(13)
  SET @Msg = @Msg + 'SET @StartDate = CONVERT(DATETIME, '''+CONVERT(VARCHAR, ISNULL(@StartDate, -1), 112)+''' , 112)'+CHAR(13)
  SET @Msg = @Msg + 'SET @EndDate = CONVERT(DATETIME, '''+CONVERT(VARCHAR, ISNULL(@EndDate, -1), 112)+''' , 112)'+CHAR(13)
  IF ISNULL( @OldRoomTypeNumber, -1) = -1 
    SET @Msg = @Msg+'SET @OldRoomTypeNumber = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg+'SET @OldRoomTypeNumber = '+CONVERT(VARCHAR, @OldRoomTypeNumber)+CHAR(13)
  IF ISNULL( @NumberOfRooms, -1) = -1 
    SET @Msg = @Msg+'SET @NumberOfRooms = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @NumberOfRooms = '+CONVERT(VARCHAR,@NumberOfRooms)+CHAR(13)
  IF ISNULL( @AdultPax, -1) = -1 
    SET @Msg = @Msg+'SET @AdultPax = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @AdultPax = '+CONVERT(VARCHAR,@AdultPax)+CHAR(13)
  IF ISNULL( @Child1Pax, -1) = -1 
    SET @Msg = @Msg+'SET @Child1Pax = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @Child1Pax = '+CONVERT(VARCHAR,@Child1Pax)+CHAR(13)
  IF ISNULL( @Child2Pax, -1) = -1 
    SET @Msg = @Msg+'SET @Child2Pax = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @Child2Pax = '+CONVERT(VARCHAR,@Child2Pax)+CHAR(13)
  IF ISNULL( @CalcTotalPrice, -1) = -1 
    SET @Msg = @Msg+'SET @CalcTotalPrice = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @CalcTotalPrice = '+CONVERT(VARCHAR,@CalcTotalPrice)+CHAR(13)
  IF ISNULL( @CalcArrivalPrice, -1) = -1 
    SET @Msg = @Msg+'SET @CalcArrivalPrice = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @CalcArrivalPrice = '+CONVERT(VARCHAR,@CalcArrivalPrice)+CHAR(13)
  IF ISNULL( @AddonAmount, -1) = -1 
    SET @Msg = @Msg+'SET @AddonAmount = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @AddonAmount = '+CONVERT(VARCHAR,@AddonAmount)+CHAR(13)
  IF ISNULL( @AddonVatAmount, -1) = -1 
    SET @Msg = @Msg+'SET @AddonVatAmount = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @AddonVatAmount = '+CONVERT(VARCHAR,@AddonVatAmount)+CHAR(13)
  IF ISNULL( @PlusProduct, -1) = -1 
    SET @Msg = @Msg+'SET @PlusProduct = NULL'+CHAR(13)
  ELSE
    SET @Msg = @Msg + 'SET @PlusProduct = '+CONVERT(VARCHAR,@PlusProduct)+CHAR(13)
  SET @Msg = @Msg+'EXEC ProductPrices @OldProductNumber, @StartDate, @EndDate, @OldRoomTypeNumber, @NumberOfRooms, @AdultPax, @Child1Pax, @Child2Pax, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT, @AddonAmount OUTPUT, @AddonVatAmount OUTPUT, @PlusProduct'+ CHAR(13)
  SET @Msg = @Msg + 'PRINT CONVERT(varchar, @CalcTotalPrice) --' + CONVERT(varchar, ISNULL(@CalcTotalPrice, -1)) + CHAR(13)
  SET @Msg = @Msg + 'PRINT CONVERT(varchar, @CalcArrivalPrice) --' + CONVERT(varchar, ISNULL(@CalcArrivalPrice, -1))+ CHAR(13)
  SET @Msg = @Msg + 'PRINT CONVERT(varchar, @AddonAmount) --' + CONVERT(varchar, ISNULL(@AddonAmount, -1))+ CHAR(13)
  SET @Msg = @Msg + 'PRINT CONVERT(varchar, @AddonVatAmount) --' + CONVERT(varchar, ISNULL(@AddonVatAmount, -1)) + CHAR(13)
  PRINT @Msg
  PRINT 'Call SP ProductPrices end --------------------------------'
END

-- Declare all fields in ProductPrice
Declare @ProductNumber INT
Declare @DateStamp CHAR (8)
Declare @RoomtypeNumber INT
Declare @DayPrice MONEY
Declare @AdultPrice1 MONEY
Declare @AdultPrice2 MONEY
Declare @AdultPrice3 MONEY
Declare @AdultPrice4 MONEY
Declare @AdultPrice5 MONEY
Declare @AdultPrice6 MONEY
Declare @AdultPrice7 MONEY
Declare @AdultPrice8 MONEY
Declare @AdultPrice9 MONEY
Declare @Children1Price1 MONEY
Declare @Children1Price2 MONEY
Declare @Children1Price3 MONEY
Declare @Children1Price4 MONEY
Declare @Children1Price5 MONEY
Declare @Children1Price6 MONEY
Declare @Children1Price7 MONEY
Declare @Children1Price8 MONEY
Declare @Children1Price9 MONEY
Declare @Children2Price1 MONEY
Declare @Children2Price2 MONEY
Declare @Children2Price3 MONEY
Declare @Children2Price4 MONEY
Declare @Children2Price5 MONEY
Declare @Children2Price6 MONEY
Declare @Children2Price7 MONEY
Declare @Children2Price8 MONEY
Declare @Children2Price9 MONEY

Declare @AdultArrivalAddon MONEY
Declare @AdultStayAddon MONEY
Declare @AdultDepartureAddon MONEY
Declare @Children1ArrivalAddon MONEY
Declare @Children1StayAddon MONEY
Declare @Children1DepartureAddon MONEY
Declare @Children2ArrivalAddon MONEY
Declare @Children2StayAddon MONEY
Declare @Children2DepartureAddon MONEY

Declare @AdultArrivalAddon2 MONEY
Declare @AdultStayAddon2 MONEY
Declare @AdultDepartureAddon2 MONEY
Declare @Children1ArrivalAddon2 MONEY
Declare @Children1StayAddon2 MONEY
Declare @Children1DepartureAddon2 MONEY
Declare @Children2ArrivalAddon2 MONEY
Declare @Children2StayAddon2 MONEY
Declare @Children2DepartureAddon2 MONEY

Declare @AdultArrivalAddon3 MONEY
Declare @AdultStayAddon3 MONEY
Declare @AdultDepartureAddon3 MONEY
Declare @Children1ArrivalAddon3 MONEY
Declare @Children1StayAddon3 MONEY
Declare @Children1DepartureAddon3 MONEY
Declare @Children2ArrivalAddon3 MONEY
Declare @Children2StayAddon3 MONEY
Declare @Children2DepartureAddon3 MONEY

Declare @AdultArrivalAddon4 MONEY
Declare @AdultStayAddon4 MONEY
Declare @AdultDepartureAddon4 MONEY
Declare @Children1ArrivalAddon4 MONEY
Declare @Children1StayAddon4 MONEY
Declare @Children1DepartureAddon4 MONEY
Declare @Children2ArrivalAddon4 MONEY
Declare @Children2StayAddon4 MONEY
Declare @Children2DepartureAddon4 MONEY

Declare @AdultArrivalAddon5 MONEY
Declare @AdultStayAddon5 MONEY
Declare @AdultDepartureAddon5 MONEY
Declare @Children1ArrivalAddon5 MONEY
Declare @Children1StayAddon5 MONEY
Declare @Children1DepartureAddon5 MONEY
Declare @Children2ArrivalAddon5 MONEY
Declare @Children2StayAddon5 MONEY
Declare @Children2DepartureAddon5 MONEY

Declare @AdultArrivalAddon6 MONEY
Declare @AdultStayAddon6 MONEY
Declare @AdultDepartureAddon6 MONEY
Declare @Children1ArrivalAddon6 MONEY
Declare @Children1StayAddon6 MONEY
Declare @Children1DepartureAddon6 MONEY
Declare @Children2ArrivalAddon6 MONEY
Declare @Children2StayAddon6 MONEY
Declare @Children2DepartureAddon6 MONEY

Declare @AdultArrivalAddon7 MONEY
Declare @AdultStayAddon7 MONEY
Declare @AdultDepartureAddon7 MONEY
Declare @Children1ArrivalAddon7 MONEY
Declare @Children1StayAddon7 MONEY
Declare @Children1DepartureAddon7 MONEY
Declare @Children2ArrivalAddon7 MONEY
Declare @Children2StayAddon7 MONEY
Declare @Children2DepartureAddon7 MONEY

Declare @AdultArrivalAddon8 MONEY
Declare @AdultStayAddon8 MONEY
Declare @AdultDepartureAddon8 MONEY
Declare @Children1ArrivalAddon8 MONEY
Declare @Children1StayAddon8 MONEY
Declare @Children1DepartureAddon8 MONEY
Declare @Children2ArrivalAddon8 MONEY
Declare @Children2StayAddon8 MONEY
Declare @Children2DepartureAddon8 MONEY

Declare @AdultArrivalAddon9 MONEY
Declare @AdultStayAddon9 MONEY
Declare @AdultDepartureAddon9 MONEY
Declare @Children1ArrivalAddon9 MONEY
Declare @Children1StayAddon9 MONEY
Declare @Children1DepartureAddon9 MONEY
Declare @Children2ArrivalAddon9 MONEY
Declare @Children2StayAddon9 MONEY
Declare @Children2DepartureAddon9 MONEY

Declare @FormatedStartDate CHAR (8)
Declare @FormatedEndDate CHAR (8)
DECLARE @FormatedEndDateMinusOne CHAR(8)
Declare @TotalPrice REAL
Declare @PricePerType BIT
DECLARE @InternalCourseProduction BIT
DECLARE @TempPrice MONEY

DECLARE @UseProductPricePerHour BIT
DECLARE @ProductPriceHours INT
DECLARE @FirstDayPriceHours INT
DECLARE @TempChar char(8)
DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @TempHourPrice money

DECLARE @HotelCustomerNumber INT

SET @OverrideNameListSetting = ISNULL(@OverrideNameListSetting, 0)

Set @FormatedStartDate =  Convert (Char (8), @StartDate, 112)
Set @FormatedEndDate =  Convert (Char (8), @EndDate, 112)
Set @FormatedEndDateMinusOne =  Convert (Char (8), @EndDate-1, 112)
Set @TotalPrice = 0
Set @CalcArrivalPrice = 0
SET @DayPrice = 0

DECLARE @AddOnFieldsPerPerson Bit
DECLARE @OnePersonPaysForAll Bit

SELECT @OnePersonPaysForAll=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey ='OnePersonPaysForAll'
IF IsNull (@OnePersonPaysForAll, 0)=0
  SET @OnePersonPaysForAll=0

SELECT @AddOnFieldsPerPerson=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AddOnFieldsPerPerson'
IF IsNull (@AddOnFieldsPerPerson, 0)=0
  SET @AddOnFieldsPerPerson=0

SELECT TOP 1 @HotelCustomerNumber = CustomerNumber
FROM Customer WITH (NOLOCK) 
WHERE CustomerTypeNumber = 7 and (HotelNumber = @AHotelNumber)
AND ISNULL(MarkAsDeleted, 0) = 0
ORDER BY CustomerNumber DESC
  
SELECT @InternalCourseProduction = dbo.GetHotelSetupKeyValue('InternalCourseProduction', @HotelCustomerNumber)
IF ISNULL(@InternalCourseProduction, 0) = 0
  SET @InternalCourseProduction = 0
  
SELECT @UseProductPricePerHour=ProductPricePerHour FROM ProductExtended WITH (NOLOCK) WHERE FK_ProductNumber = @OldProductNumber
IF ISNULL(@UseProductPricePerHour, 0) = 0
  SET @UseProductPricePerHour = 0

Declare @DayProduct Bit 
Declare @FixedPrice Bit

DECLARE @IsDayProduct BIT
DECLARE @IsFixedPrice BIT
DECLARE @AddonType SMALLINT
DECLARE @NameListSetting INT
DECLARE @AddonSpecArrival BIT
DECLARE @AddonSpecStay BIT
DECLARE @AddonSpecDeparture BIT

SELECT @IsDayProduct = DayProduct, @IsFixedPrice = FixedPrice, @AddonType=ISNULL(AddonType, 0), @NameListSetting = ISNULL(NameListSetting, 0), @PricePerType = ISNULL(PricePerType,0)
, @AddonSpecArrival = (CASE WHEN BillTextArrival IS NULL OR LEN(LTRIM(BilltextArrival)) = 0 THEN 0 ELSE 1 END)
, @AddonSpecStay = (CASE WHEN BillTextStay IS NULL OR LEN(LTRIM(BilltextStay)) = 0 THEN 0 ELSE 1 END)
, @AddonSpecDeparture = (CASE WHEN BillTextdeparture IS NULL OR LEN(LTRIM(BilltextDeparture)) = 0 THEN 0 ELSE 1 END)
FROM Products WITH (NOLOCK)
WHERE ProductNumber = @OldProductNumber

SET @AAdultPriceIsZero = 0

-- 20140520/RN:
IF @IsDayProduct = 1
BEGIN
  IF @UseProductPricePerHour = 1
  BEGIN
    SET @ProductPriceHours = CAST(DATEDIFF(minute, @StartDate, @EndDate) / 60 AS Int)
    IF DATEDIFF(minute, @StartDate, @EndDate) % 60 > 0 -- Modulus function
      SET @ProductPriceHours = @ProductPriceHours + 1
    IF (DATEDIFF(day, @StartDate, @EndDate) > 0)
    BEGIN
      -- There is more than one day on the conferenceline, so we need to get the number
      -- of hours for the first day
      SET @TempChar = CAST(CONVERT(CHAR(8), @StartDate, 108) AS Char(8))
      SET @StartTime = CONVERT(datetime, @TempChar, 108)
      SET @EndTime = CONVERT(datetime, '23:59:00', 108)
      SET @FirstDayPriceHours = CAST(DATEDIFF(minute, @StartTime, @EndTime) / 60 AS Int)
      IF DATEDIFF(minute,@StartTime, @EndTime) % 60 > 0
        SET @FirstDayPriceHours = @FirstDayPriceHours + 1
    END
    ELSE
      SET @FirstDayPriceHours = @ProductPriceHours
  END
END

IF @OverrideNameListSetting = 0
BEGIN
  IF @NameListSetting = 0  -- FollowHotelSetup
  BEGIN
    SELECT @NameListSetting=ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'NameListReservationPerPerson'

    IF @NameListSetting = 0  -- Create reservation per room/hall
      SET @NameListSetting = 2
    ELSE IF @NameListSetting = 1 --Create reservation per person or room
      SET @NameListSetting = 1
  END
END
ELSE
  SET @NameListSetting = @OverrideNameListSetting

IF @PricePerType = 0 AND @IsDayProduct=1
  SET @OldRoomTypeNumber = 0

DECLARE @Modifier1 Smallint
DECLARE @Modifier2 Smallint

IF (@IsDayProduct = 0 AND @AddonType = 0 AND @AddOnFieldsPerPerson = 0) or 
  (@IsDayProduct = 0 AND @AddOnFieldsPerPerson = 1 and @NameListSetting = 1 and @OnePersonPaysForAll = 0)   -- JP 2004-03-22 Skulle virke både for kobæk og Dan
BEGIN
  SET @Modifier1 = 1
  SET @Modifier2 = 0
END
ELSE
BEGIN
  SET @Modifier1 = 0
  SET @Modifier2 = 1
END

DECLARE @AddonSpecVat REAL
DECLARE @VatValueNumber INT

IF (@AddonSpecArrival = 1 OR @AddonSpecStay = 1 OR @AddonSpecDeparture  = 1)
BEGIN
  SELECT @VatValueNumber=Accounts.VatValueNumber  FROM Ware WITH (NOLOCK)
  INNER JOIN WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber AND (WareGroups.Payment = 4) AND (Ware.SortNumber = 5)
  INNER JOIN Accounts WITH (NOLOCK) ON Ware.ResultAccountNumber = Accounts.AccountNumber

  EXEC GetVatvalueFromVATValueNumber @VatValueNumber, @AddonSpecVat OUTPUT
END 
ELSE
  SET @VatValueNumber = -1

DECLARE CopyCur Cursor READ_ONLY FAST_FORWARD FOR
  Select ProductNumber, DateStamp, RoomtypeNumber,
    AdultPrice1, AdultPrice2, AdultPrice3, AdultPrice4, AdultPrice5, AdultPrice6, AdultPrice7, AdultPrice8, AdultPrice9,
    Children1Price1, Children1Price2, Children1Price3, Children1Price4, Children1Price5, Children1Price6, Children1Price7, Children1Price8, Children1Price9,
    Children2Price1, Children2Price2, Children2Price3, Children2Price4, Children2Price5, Children2Price6, Children2Price7, Children2Price8, Children2Price9,
    AdultArrivalAddon, AdultStayAddon, AdultDepartureAddon, Children1ArrivalAddon,Children1StayAddon, Children1DepartureAddon, Children2ArrivalAddon, Children2StayAddon, Children2DepartureAddon,
    AdultArrivalAddon2, AdultStayAddon2, AdultDepartureAddon2, Children1ArrivalAddon2, Children1StayAddon2, Children1DepartureAddon2, Children2ArrivalAddon2, Children2StayAddon2, Children2DepartureAddon2,
    AdultArrivalAddon3, AdultStayAddon3, AdultDepartureAddon3, Children1ArrivalAddon3, Children1StayAddon3, Children1DepartureAddon3, Children2ArrivalAddon3, Children2StayAddon3, Children2DepartureAddon3,
    AdultArrivalAddon4, AdultStayAddon4, AdultDepartureAddon4, Children1ArrivalAddon4, Children1StayAddon4, Children1DepartureAddon4, Children2ArrivalAddon4, Children2StayAddon4, Children2DepartureAddon4,
    AdultArrivalAddon5, AdultStayAddon5, AdultDepartureAddon5, Children1ArrivalAddon5, Children1StayAddon5, Children1DepartureAddon5, Children2ArrivalAddon5, Children2StayAddon5, Children2DepartureAddon5,
    AdultArrivalAddon6, AdultStayAddon6, AdultDepartureAddon6, Children1ArrivalAddon6, Children1StayAddon6, Children1DepartureAddon6, Children2ArrivalAddon6, Children2StayAddon6, Children2DepartureAddon6,
    AdultArrivalAddon7, AdultStayAddon7, AdultDepartureAddon7, Children1ArrivalAddon7, Children1StayAddon7, Children1DepartureAddon7, Children2ArrivalAddon7, Children2StayAddon7, Children2DepartureAddon7,
    AdultArrivalAddon8, AdultStayAddon8, AdultDepartureAddon8, Children1ArrivalAddon8, Children1StayAddon8, Children1DepartureAddon8, Children2ArrivalAddon8, Children2StayAddon8, Children2DepartureAddon8,
    AdultArrivalAddon9, AdultStayAddon9, AdultDepartureAddon9, Children1ArrivalAddon9, Children1StayAddon9, Children1DepartureAddon9, Children2ArrivalAddon9, Children2StayAddon9, Children2DepartureAddon9
From ProductPrice WITH (NOLOCK)
WHERE ProductNumber = @OldProductNumber AND DateStamp BETWEEN @FormatedStartDate AND @FormatedEndDate AND RoomTypeNumber = @OldRoomTypeNumber
ORDER BY DateStamp -- MEGA IMPORTANT OR ELSE WE MIGHT END UP WITH A WRONG ARRIVAL PRICE

Open CopyCur

Fetch Next From CopyCur
Into @ProductNumber, @DateStamp, @RoomtypeNumber,
    @AdultPrice1, @AdultPrice2, @AdultPrice3, @AdultPrice4, @AdultPrice5, @AdultPrice6, @AdultPrice7, @AdultPrice8, @AdultPrice9,
    @Children1Price1, @Children1Price2, @Children1Price3, @Children1Price4, @Children1Price5, @Children1Price6, @Children1Price7, @Children1Price8, @Children1Price9,
    @Children2Price1, @Children2Price2, @Children2Price3, @Children2Price4, @Children2Price5, @Children2Price6, @Children2Price7, @Children2Price8, @Children2Price9,
    @AdultArrivalAddon, @AdultStayAddon, @AdultDepartureAddon, @Children1ArrivalAddon,@Children1StayAddon, @Children1DepartureAddon, @Children2ArrivalAddon, @Children2StayAddon, @Children2DepartureAddon,
    @AdultArrivalAddon2, @AdultStayAddon2, @AdultDepartureAddon2, @Children1ArrivalAddon2, @Children1StayAddon2, @Children1DepartureAddon2, @Children2ArrivalAddon2, @Children2StayAddon2, @Children2DepartureAddon2,
    @AdultArrivalAddon3, @AdultStayAddon3, @AdultDepartureAddon3, @Children1ArrivalAddon3, @Children1StayAddon3, @Children1DepartureAddon3, @Children2ArrivalAddon3, @Children2StayAddon3, @Children2DepartureAddon3,
    @AdultArrivalAddon4, @AdultStayAddon4, @AdultDepartureAddon4, @Children1ArrivalAddon4, @Children1StayAddon4, @Children1DepartureAddon4, @Children2ArrivalAddon4, @Children2StayAddon4, @Children2DepartureAddon4,
    @AdultArrivalAddon5, @AdultStayAddon5, @AdultDepartureAddon5, @Children1ArrivalAddon5, @Children1StayAddon5, @Children1DepartureAddon5, @Children2ArrivalAddon5, @Children2StayAddon5, @Children2DepartureAddon5,
    @AdultArrivalAddon6, @AdultStayAddon6, @AdultDepartureAddon6, @Children1ArrivalAddon6, @Children1StayAddon6, @Children1DepartureAddon6, @Children2ArrivalAddon6, @Children2StayAddon6, @Children2DepartureAddon6,
    @AdultArrivalAddon7, @AdultStayAddon7, @AdultDepartureAddon7, @Children1ArrivalAddon7, @Children1StayAddon7, @Children1DepartureAddon7, @Children2ArrivalAddon7, @Children2StayAddon7, @Children2DepartureAddon7,
    @AdultArrivalAddon8, @AdultStayAddon8, @AdultDepartureAddon8, @Children1ArrivalAddon8, @Children1StayAddon8, @Children1DepartureAddon8, @Children2ArrivalAddon8, @Children2StayAddon8, @Children2DepartureAddon8,
    @AdultArrivalAddon9, @AdultStayAddon9, @AdultDepartureAddon9, @Children1ArrivalAddon9, @Children1StayAddon9, @Children1DepartureAddon9, @Children2ArrivalAddon9, @Children2StayAddon9, @Children2DepartureAddon9

DECLARE @AddonAmountArrival smallmoney SET @AddonAmountArrival=0 
DECLARE @AddonAmountStay smallmoney SET @AddonAmountStay=0
DECLARE @AddonAmountDeparture smallmoney SET @AddonAmountDeparture=0

SELECT CONVERT(CHAR(8), IndividualDate, 112) AS IndividualDate
INTO #tmdDateRange
FROM dbo.DateRange(@FormatedStartDate, @FormatedEndDateMinusOne, -1)

While @@FETCH_STATUS = 0
Begin
  DELETE #tmdDateRange WHERE IndividualDate = @DateStamp

  SET @DayPrice = 0

  if @IsDayProduct = 0
  begin
    IF @AddOnFieldsPerPerson = 0  -- following code was made to make the calculation code below more simple!
    BEGIN
      SET @AdultArrivalAddon2 = @AdultArrivalAddon
      SET @AdultArrivalAddon3 = @AdultArrivalAddon
      SET @AdultArrivalAddon4 = @AdultArrivalAddon
      SET @AdultArrivalAddon5 = @AdultArrivalAddon
      SET @AdultArrivalAddon6 = @AdultArrivalAddon
      SET @AdultArrivalAddon7 = @AdultArrivalAddon
      SET @AdultArrivalAddon8 = @AdultArrivalAddon
      SET @AdultArrivalAddon9 = @AdultArrivalAddon

      SET @Children1ArrivalAddon2 = @Children1ArrivalAddon
      SET @Children1ArrivalAddon3 = @Children1ArrivalAddon
      SET @Children1ArrivalAddon4 = @Children1ArrivalAddon
      SET @Children1ArrivalAddon5 = @Children1ArrivalAddon
      SET @Children1ArrivalAddon6 = @Children1ArrivalAddon
      SET @Children1ArrivalAddon7 = @Children1ArrivalAddon
      SET @Children1ArrivalAddon8 = @Children1ArrivalAddon
      SET @Children1ArrivalAddon9 = @Children1ArrivalAddon

      SET @Children2ArrivalAddon2 = @Children2ArrivalAddon
      SET @Children2ArrivalAddon3 = @Children2ArrivalAddon
      SET @Children2ArrivalAddon4 = @Children2ArrivalAddon
      SET @Children2ArrivalAddon5 = @Children2ArrivalAddon
      SET @Children2ArrivalAddon6 = @Children2ArrivalAddon
      SET @Children2ArrivalAddon7 = @Children2ArrivalAddon
      SET @Children2ArrivalAddon8 = @Children2ArrivalAddon
      SET @Children2ArrivalAddon9 = @Children2ArrivalAddon

      SET @AdultStayAddon2 = @AdultStayAddon
      SET @AdultStayAddon3 = @AdultStayAddon
      SET @AdultStayAddon4 = @AdultStayAddon
      SET @AdultStayAddon5 = @AdultStayAddon
      SET @AdultStayAddon6 = @AdultStayAddon
      SET @AdultStayAddon7 = @AdultStayAddon
      SET @AdultStayAddon8 = @AdultStayAddon
      SET @AdultStayAddon9 = @AdultStayAddon

      SET @Children1StayAddon2 = @Children1StayAddon
      SET @Children1StayAddon3 = @Children1StayAddon
      SET @Children1StayAddon4 = @Children1StayAddon
      SET @Children1StayAddon5 = @Children1StayAddon
      SET @Children1StayAddon6 = @Children1StayAddon
      SET @Children1StayAddon7 = @Children1StayAddon
      SET @Children1StayAddon8 = @Children1StayAddon
      SET @Children1StayAddon9 = @Children1StayAddon

      SET @Children2StayAddon2 = @Children2StayAddon
      SET @Children2StayAddon3 = @Children2StayAddon
      SET @Children2StayAddon4 = @Children2StayAddon
      SET @Children2StayAddon5 = @Children2StayAddon
      SET @Children2StayAddon6 = @Children2StayAddon
      SET @Children2StayAddon7 = @Children2StayAddon
      SET @Children2StayAddon8 = @Children2StayAddon
      SET @Children2StayAddon9 = @Children2StayAddon

      SET @AdultDepartureAddon2 = @AdultDepartureAddon
      SET @AdultDepartureAddon3 = @AdultDepartureAddon
      SET @AdultDepartureAddon4 = @AdultDepartureAddon
      SET @AdultDepartureAddon5 = @AdultDepartureAddon
      SET @AdultDepartureAddon6 = @AdultDepartureAddon
      SET @AdultDepartureAddon7 = @AdultDepartureAddon
      SET @AdultDepartureAddon8 = @AdultDepartureAddon
      SET @AdultDepartureAddon9 = @AdultDepartureAddon

      SET @Children1DepartureAddon2 = @Children1DepartureAddon
      SET @Children1DepartureAddon3 = @Children1DepartureAddon
      SET @Children1DepartureAddon4 = @Children1DepartureAddon
      SET @Children1DepartureAddon5 = @Children1DepartureAddon
      SET @Children1DepartureAddon6 = @Children1DepartureAddon
      SET @Children1DepartureAddon7 = @Children1DepartureAddon
      SET @Children1DepartureAddon8 = @Children1DepartureAddon
      SET @Children1DepartureAddon9 = @Children1DepartureAddon

      SET @Children2DepartureAddon2 = @Children2DepartureAddon
      SET @Children2DepartureAddon3 = @Children2DepartureAddon
      SET @Children2DepartureAddon4 = @Children2DepartureAddon
      SET @Children2DepartureAddon5 = @Children2DepartureAddon
      SET @Children2DepartureAddon6 = @Children2DepartureAddon
      SET @Children2DepartureAddon7 = @Children2DepartureAddon
      SET @Children2DepartureAddon8 = @Children2DepartureAddon
      SET @Children2DepartureAddon9 = @Children2DepartureAddon
    END
    -- Overnight
    if (@PlusProduct=1) AND (@DateStamp = @FormatedEndDate) AND (@FormatedStartDate=@FormatedEndDate) -- "departuredate" but are dealing with a period where person is arriving and departing same day. 
    --this is for use with + prod where you have a product change on the departuredate. it must not add price in this case. only addons!
    begin
      if @AdultPax = 1
      BEGIN
        set @DayPrice = @AdultDepartureAddon
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon
      END
      else if @AdultPax = 2  -- @AddOnFieldsPerPerson = 0:  x * (2 * 1 + 0) = 2x        @AddOnFieldsPerPerson = 1  x * ( 2 * 0 + 1) = x
      BEGIN
        set @DayPrice = @AdultDepartureAddon2 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon2 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 3 
      BEGIN
        set @DayPrice = @AdultDepartureAddon3 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon3 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 4 
      BEGIN
        set @DayPrice = @AdultDepartureAddon4 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon4 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 5 
      BEGIN
        set @DayPrice = @AdultDepartureAddon5 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon5 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 6 
      BEGIN
        set @DayPrice = @AdultDepartureAddon6 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon6 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 7 
      BEGIN
        set @DayPrice = @AdultDepartureAddon7 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon7 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 8 
      BEGIN
        set @DayPrice = @AdultDepartureAddon8 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon8 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 9 
      BEGIN
        set @DayPrice = @AdultDepartureAddon9 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon9 * (@AdultPax * @Modifier1 + @Modifier2)
      END

      if @Child1Pax = 1 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon
      END
      else if @Child1Pax = 2 
      BEGIN
        set @DayPrice =@DayPrice + @Children1DepartureAddon2 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon2 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 3
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon3 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon3 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 4 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon4 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon4 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 5 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon5 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon5 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 6 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon6 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon6 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 7 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon7 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon7 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 8 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon8 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon8 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 9 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon9 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon9 * (@Child1Pax * @Modifier1 + @Modifier2)
      END

      if @Child2Pax = 1 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon
      END
      else if @Child2Pax = 2 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon2* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon2* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 3 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon3* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon3* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 4 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon4* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon4* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 5 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon5* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon5* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 6 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon6* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon6* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 7 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon7* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon7* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 8 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon8* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon8* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 9 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon9* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon9* (@Child2Pax * @Modifier1 + @Modifier2)
      END
    end
    else if @DateStamp = @FormatedStartDate  -- "arrival"
    begin
      IF @AAdultPriceIsZero = 0
      BEGIN
        IF
          (@OnePersonPaysForAll = 0 AND @NameListSetting = 1 AND ISNULL(@AdultPrice1, 0) = 0) OR 
          (@AdultPax = 1 AND ISNULL(@AdultPrice1, 0) = 0) OR
          (@AdultPax = 2 AND ISNULL(@AdultPrice2, 0) = 0) OR
          (@AdultPax = 3 AND ISNULL(@AdultPrice3, 0) = 0) OR
          (@AdultPax = 4 AND ISNULL(@AdultPrice4, 0) = 0) OR
          (@AdultPax = 5 AND ISNULL(@AdultPrice5, 0) = 0) OR
          (@AdultPax = 6 AND ISNULL(@AdultPrice6, 0) = 0) OR
          (@AdultPax = 7 AND ISNULL(@AdultPrice7, 0) = 0) OR
          (@AdultPax = 8 AND ISNULL(@AdultPrice8, 0) = 0) OR
          (@AdultPax = 9 AND ISNULL(@AdultPrice9, 0) = 0)
          SET @AAdultPriceIsZero = 1
      END

      if (@OnePersonPaysForAll = 0) and (@NameListSetting = 1)
      BEGIN
        set @DayPrice = @AdultPrice1 * @AdultPax + @AdultArrivalAddon * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 1 
      BEGIN
        set @DayPrice = @AdultPrice1 + @AdultArrivalAddon
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon
      END
      else if @AdultPax = 2 
      BEGIN
        set @DayPrice = @AdultPrice2 + @AdultArrivalAddon2* (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon2* (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 3 
      BEGIN
        set @DayPrice = @AdultPrice3 + @AdultArrivalAddon3* (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon3* (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 4 
      BEGIN
        set @DayPrice = @AdultPrice4 + @AdultArrivalAddon4* (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon4* (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 5 
      BEGIN
        set @DayPrice = @AdultPrice5 + @AdultArrivalAddon5* (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon5* (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 6 
      BEGIN
        set @DayPrice = @AdultPrice6 + @AdultArrivalAddon6* (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon6* (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 7 
      BEGIN
        set @DayPrice = @AdultPrice7 + @AdultArrivalAddon7* (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon7* (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 8 
      BEGIN
        set @DayPrice = @AdultPrice8 + @AdultArrivalAddon8* (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon8* (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 9 
      BEGIN
        set @DayPrice = @AdultPrice9 + @AdultArrivalAddon9* (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @AdultArrivalAddon9* (@AdultPax * @Modifier1 + @Modifier2)
      END

      if (@OnePersonPaysForAll = 0) and (@NameListSetting = 1)
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price1 * @Child1Pax + @Children1ArrivalAddon * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 1 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price1 + @Children1ArrivalAddon
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon
      END
      else if @Child1Pax = 2 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price2 + @Children1ArrivalAddon2* (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon2* (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 3 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price3 + @Children1ArrivalAddon3* (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon3* (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 4 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price4 + @Children1ArrivalAddon4* (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon4* (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 5 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price5 + @Children1ArrivalAddon5* (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon5* (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 6 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price6 + @Children1ArrivalAddon6* (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon6* (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 7 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price7 + @Children1ArrivalAddon7* (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon7* (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 8 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price8 + @Children1ArrivalAddon8* (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon8* (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 9 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price9 + @Children1ArrivalAddon9* (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children1ArrivalAddon9* (@Child1Pax * @Modifier1 + @Modifier2)
      END

      if (@OnePersonPaysForAll = 0) and (@NameListSetting = 1)
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price1 * @Child2Pax + @Children2ArrivalAddon * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 1 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price1 + @Children2ArrivalAddon
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon
      END
      else if @Child2Pax = 2 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price2 + @Children2ArrivalAddon2* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon2* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 3 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price3 + @Children2ArrivalAddon3* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon3* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 4 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price4 + @Children2ArrivalAddon4* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon4* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 5 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price5 + @Children2ArrivalAddon5* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon5* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 6 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price6 + @Children2ArrivalAddon6* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon6* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 7 
      BEGIN
        set @DayPrice = @DayPrice +  @Children2Price7 + @Children2ArrivalAddon7* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon7* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 8 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price8 + @Children2ArrivalAddon8* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon8* (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 9 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price9 + @Children2ArrivalAddon9* (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountArrival = @AddonAmountArrival + @Children2ArrivalAddon9* (@Child2Pax * @Modifier1 + @Modifier2)
      END
    end
    else if @DateStamp = @FormatedEndDate  -- "departure"
    begin
      if (@OnePersonPaysForAll = 0) and (@NameListSetting = 1)
      BEGIN
        set @DayPrice = @AdultDepartureAddon * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 1 
      BEGIN
        set @DayPrice = @AdultDepartureAddon
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon
      END
      else if @AdultPax = 2 
      BEGIN
        set @DayPrice = @AdultDepartureAddon2 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon2 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 3 
      BEGIN
        set @DayPrice = @AdultDepartureAddon3 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon3 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 4 
      BEGIN
        set @DayPrice = @AdultDepartureAddon4 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon4 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 5 
      BEGIN
        set @DayPrice = @AdultDepartureAddon5 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon5 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 6 
      BEGIN
        set @DayPrice = @AdultDepartureAddon6 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon6 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 7 
      BEGIN
        set @DayPrice = @AdultDepartureAddon7 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon7 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 8 
      BEGIN
        set @DayPrice = @AdultDepartureAddon8 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon8 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 9 
      BEGIN
        set @DayPrice = @AdultDepartureAddon9 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @AdultDepartureAddon9 * (@AdultPax * @Modifier1 + @Modifier2)
      END

      if (@OnePersonPaysForAll = 0) and (@NameListSetting = 1)
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 1 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon
      END
      else if @Child1Pax = 2 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon2 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon2 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 3 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon3 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon3 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 4 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon4 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon4 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 5 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon5 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon5 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 6 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon6 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon6 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 7 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon7 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon7 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 8 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon8 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon8 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 9 
      BEGIN
        set @DayPrice = @DayPrice + @Children1DepartureAddon9 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children1DepartureAddon9 * (@Child1Pax * @Modifier1 + @Modifier2)
      END

      if (@OnePersonPaysForAll = 0) and (@NameListSetting = 1)
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 1 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon
      END
      else if @Child2Pax = 2 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon2 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon2 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 3 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon3 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon3 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 4 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon4 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon4 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 5 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon5 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon5 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 6 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon6 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon6 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 7 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon7 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon7 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 8 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon8 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon8 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 9 
      BEGIN
        set @DayPrice = @DayPrice + @Children2DepartureAddon9 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountDeparture = @AddonAmountDeparture + @Children2DepartureAddon9 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
    end
    else  -- "stay"
    begin
      IF @AAdultPriceIsZero = 0
      BEGIN
        IF
          (@OnePersonPaysForAll = 0 AND @NameListSetting = 1 AND ISNULL(@AdultPrice1, 0) = 0) OR 
          (@AdultPax = 1 AND ISNULL(@AdultPrice1, 0) = 0) OR
          (@AdultPax = 2 AND ISNULL(@AdultPrice2, 0) = 0) OR
          (@AdultPax = 3 AND ISNULL(@AdultPrice3, 0) = 0) OR
          (@AdultPax = 4 AND ISNULL(@AdultPrice4, 0) = 0) OR
          (@AdultPax = 5 AND ISNULL(@AdultPrice5, 0) = 0) OR
          (@AdultPax = 6 AND ISNULL(@AdultPrice6, 0) = 0) OR
          (@AdultPax = 7 AND ISNULL(@AdultPrice7, 0) = 0) OR
          (@AdultPax = 8 AND ISNULL(@AdultPrice8, 0) = 0) OR
          (@AdultPax = 9 AND ISNULL(@AdultPrice9, 0) = 0)
          SET @AAdultPriceIsZero = 1
      END

      if (@OnePersonPaysForAll = 0) and (@NameListSetting = 1)
      BEGIN
        set @DayPrice = @AdultPrice1 * @AdultPax + @AdultStayAddon * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 1 
      BEGIN
        set @DayPrice = @AdultPrice1 + @AdultStayAddon
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon
      END
      else if @AdultPax = 2 
      BEGIN
        set @DayPrice = @AdultPrice2 + @AdultStayAddon2 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon2 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 3 
      BEGIN
        set @DayPrice = @AdultPrice3 + @AdultStayAddon3 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon3 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 4 
      BEGIN
        set @DayPrice = @AdultPrice4 + @AdultStayAddon4 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon4 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 5 
      BEGIN
        set @DayPrice = @AdultPrice5 + @AdultStayAddon5 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon5 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 6 
      BEGIN
        set @DayPrice = @AdultPrice6 + @AdultStayAddon6 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon6 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 7 
      BEGIN
        set @DayPrice = @AdultPrice7 + @AdultStayAddon7 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon7 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 8 
      BEGIN
        set @DayPrice = @AdultPrice8 + @AdultStayAddon8 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon8 * (@AdultPax * @Modifier1 + @Modifier2)
      END
      else if @AdultPax = 9 
      BEGIN
        set @DayPrice = @AdultPrice9 + @AdultStayAddon9 * (@AdultPax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @AdultStayAddon9 * (@AdultPax * @Modifier1 + @Modifier2)
      END

      if (@OnePersonPaysForAll = 0) and (@NameListSetting = 1)
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price1 * @Child1Pax + @Children1StayAddon * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 1 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price1 + @Children1StayAddon
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon
      END
      else if @Child1Pax = 2 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price2 + @Children1StayAddon2 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon2 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 3 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price3 + @Children1StayAddon3 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon3 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 4 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price4 + @Children1StayAddon4 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon4 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 5 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price5 + @Children1StayAddon5 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon5 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 6 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price6 + @Children1StayAddon6 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon6 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 7 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price7 + @Children1StayAddon7* (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon7* (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 8 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price8 + @Children1StayAddon8 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon8 * (@Child1Pax * @Modifier1 + @Modifier2)
      END
      else if @Child1Pax = 9 
      BEGIN
        set @DayPrice = @DayPrice + @Children1Price9 + @Children1StayAddon9 * (@Child1Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children1StayAddon9 * (@Child1Pax * @Modifier1 + @Modifier2)
      END

      if (@OnePersonPaysForAll = 0) and (@NameListSetting = 1)
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price1 * @Child2Pax + @Children2StayAddon * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 1 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price1 + @Children2StayAddon
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon
      END
      else if @Child2Pax = 2 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price2 + @Children2StayAddon2 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon2 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 3 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price3 + @Children2StayAddon3 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon3 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 4 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price4 + @Children2StayAddon4 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon4 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 5 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price5 + @Children2StayAddon5 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon5 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 6 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price6 + @Children2StayAddon6 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon6 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 7 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price7 + @Children2StayAddon7 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon7 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 8 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price8 + @Children2StayAddon8 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon8 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
      else if @Child2Pax = 9 
      BEGIN
        set @DayPrice = @DayPrice + @Children2Price9 + @Children2StayAddon9 * (@Child2Pax * @Modifier1 + @Modifier2)
        SET @AddonAmountStay = @AddonAmountStay + @Children2StayAddon9 * (@Child2Pax * @Modifier1 + @Modifier2)
      END
    end
    SET @DayPrice = @DayPrice - @AFlatDiscount
    IF (@ARateCap > 0) AND (@DayPrice > @ARateCap)
      SET @DayPrice = @ARateCap
    SET @TotalPrice = @TotalPrice + @DayPrice
  end
  else if @IsDayProduct = 1
  begin
    -- 20120611/RN:
    IF @InternalCourseProduction = 1
    BEGIN
      IF (ISNULL(@ACustomerNumber, 0) > 0) AND (ISNULL(@AdultPrice1, 0) > 0)
      BEGIN
        SELECT @TempPrice = ISNULL(ProductPrice, -1) FROM CustomerProductPrice WITH (NOLOCK)
        WHERE FK_CustomerNumber = @ACustomerNumber AND FK_ProductNumber = @ProductNumber
        IF (@TempPrice >= 0)
        BEGIN
          SET @AdultPrice1 = @TempPrice
          SET @AForcePriceoverwrite = 1
        END
      END
    END

    if @IsFixedPrice = 0
    begin
      -- DayProduct, NOT fixedprice
      if @DateStamp = @FormatedStartDate  -- "arrival"
      begin
        if @Child1Pax >0
        begin
          if @adultpax > 0
            set @CalcArrivalPrice = @AdultPrice1 + (@Children1Price1 * 1) --Strange but true..
          else
            set @CalcArrivalPrice =  (@Children1Price1 * 1) --Strange but true..
        end
        else
        begin
          if @AdultPax > 0 -- 20130522/RN: CR054990
            set @CalcArrivalPrice = @AdultPrice1 
        end

        if @Child2Pax >0
          set @CalcarrivalPrice = @CalcarrivalPrice + @Children2Price1  --  JP 20050128

        set @TotalPrice = @TotalPrice + (@AdultPrice1 * @AdultPax)
        set @TotalPrice = @TotalPrice + (@Children1Price1 * @Child1Pax)
        set @TotalPrice = @TotalPrice + (@Children2Price1 * @Child2Pax)  -- JP 20050128
      end
      else  -- "stay"
      begin
        set @TotalPrice = @TotalPrice + (@AdultPrice1 * @AdultPax)
        set @TotalPrice = @TotalPrice + (@Children1Price1 * @Child1Pax)
        set @TotalPrice = @TotalPrice + (@Children2Price1 * @Child2Pax) --  JP 20050128
      end
    end
    else if @IsFixedPrice = 1
    begin
      -- DayProduct, fixedprice
      IF NOT @UseProductPricePerHour = 1
      BEGIN
        if @DateStamp = @FormatedStartDate  -- "arrival"
        begin
          if @Child1Pax >0
            set @CalcarrivalPrice = @AdultPrice1 + @Children1Price1
          else
            set @CalcarrivalPrice = @AdultPrice1

          if @Child2Pax >0
            set @CalcarrivalPrice = @CalcarrivalPrice + @Children2Price1  --  JP 20050128

          set @TotalPrice = @TotalPrice + @CalcArrivalPrice
        end
        else  -- "stay"
        begin
          set @TotalPrice = @TotalPrice + @AdultPrice1
          if @Child1Pax >0
            set @TotalPrice = @TotalPrice + @Children1Price1
          if @Child2Pax >0
            set @TotalPrice = @TotalPrice + @Children2Price1  --  JP 20050128
        end
      END
      ELSE
      BEGIN
        IF @DateStamp = @FormatedStartDate -- "Arrival"
        BEGIN
          IF @Child1Pax > 0
            SET @TempHourPrice = @AdultPrice1 + @Children1Price1
          ELSE
            SET @TempHourPrice = @AdultPrice1
      
          IF @Child2Pax > 0
            SET @TempHourPrice = @TempHourPrice + @Children2Price1

          SET @CalcArrivalPrice = @TempHourPrice * @FirstDayPriceHours
          SET @TotalPrice = @TempHourPrice * @ProductPriceHours
        END
        ELSE
        BEGIN -- "Stay"
          SET @TempHourPrice = @AdultPrice1
          IF @Child1Pax > 0
            SET @TempHourPrice = @TempHourPrice + @Children1Price1
          IF @Child2Pax > 0
            SET @TempHourPrice = @TempHourPrice + @Children2Price1
          SET @TotalPrice = @TempHourPrice * @ProductPriceHours
        END
      END
    end
  end

  if @DateStamp = @FormatedStartDate and (@isdayproduct=0 or @isfixedPrice=1) and (@UseProductPricePerHour = 0)      -- Arrival date
    Set @CalcArrivalPrice = @TotalPrice

  if @DateStamp = @FormatedEndDate and (@isdayproduct=0 or @isfixedPrice=1) and (@UseProductPricePerHour = 0) and (@EndDate - @StartDate = 1)      -- DepatureDate date
    Set @CalcArrivalPrice = @CalcArrivalPrice + @DayPrice

  Fetch Next From CopyCur
  Into @ProductNumber, @DateStamp, @RoomtypeNumber,
  @AdultPrice1, @AdultPrice2, @AdultPrice3, @AdultPrice4, @AdultPrice5, @AdultPrice6, @AdultPrice7, @AdultPrice8, @AdultPrice9,
  @Children1Price1, @Children1Price2, @Children1Price3, @Children1Price4, @Children1Price5, @Children1Price6, @Children1Price7, @Children1Price8, @Children1Price9,
  @Children2Price1, @Children2Price2, @Children2Price3, @Children2Price4, @Children2Price5, @Children2Price6, @Children2Price7, @Children2Price8, @Children2Price9,
  @AdultArrivalAddon, @AdultStayAddon, @AdultDepartureAddon, @Children1ArrivalAddon, @Children1StayAddon,  @Children1DepartureAddon, @Children2ArrivalAddon, 
  @Children2StayAddon,@Children2DepartureAddon,
  @AdultArrivalAddon2, @AdultStayAddon2, @AdultDepartureAddon2, @Children1ArrivalAddon2, @Children1StayAddon2, @Children1DepartureAddon2, @Children2ArrivalAddon2, @Children2StayAddon2, @Children2DepartureAddon2,
  @AdultArrivalAddon3, @AdultStayAddon3, @AdultDepartureAddon3, @Children1ArrivalAddon3, @Children1StayAddon3, @Children1DepartureAddon3, @Children2ArrivalAddon3, @Children2StayAddon3, @Children2DepartureAddon3,
  @AdultArrivalAddon4, @AdultStayAddon4, @AdultDepartureAddon4, @Children1ArrivalAddon4, @Children1StayAddon4, @Children1DepartureAddon4, @Children2ArrivalAddon4, @Children2StayAddon4, @Children2DepartureAddon4,
  @AdultArrivalAddon5, @AdultStayAddon5, @AdultDepartureAddon5, @Children1ArrivalAddon5, @Children1StayAddon5, @Children1DepartureAddon5, @Children2ArrivalAddon5, @Children2StayAddon5, @Children2DepartureAddon5,
  @AdultArrivalAddon6, @AdultStayAddon6, @AdultDepartureAddon6, @Children1ArrivalAddon6, @Children1StayAddon6, @Children1DepartureAddon6, @Children2ArrivalAddon6, @Children2StayAddon6, @Children2DepartureAddon6,
  @AdultArrivalAddon7, @AdultStayAddon7, @AdultDepartureAddon7, @Children1ArrivalAddon7, @Children1StayAddon7, @Children1DepartureAddon7, @Children2ArrivalAddon7, @Children2StayAddon7, @Children2DepartureAddon7,
  @AdultArrivalAddon8, @AdultStayAddon8, @AdultDepartureAddon8, @Children1ArrivalAddon8, @Children1StayAddon8, @Children1DepartureAddon8, @Children2ArrivalAddon8, @Children2StayAddon8, @Children2DepartureAddon8,
  @AdultArrivalAddon9, @AdultStayAddon9, @AdultDepartureAddon9, @Children1ArrivalAddon9, @Children1StayAddon9, @Children1DepartureAddon9, @Children2ArrivalAddon9, @Children2StayAddon9, @Children2DepartureAddon9
End

Close CopyCur
Deallocate CopyCur

IF @AAdultPriceIsZero = 0 AND (SELECT COUNT(*) FROM #tmdDateRange) > 0
  SET @AAdultPriceIsZero = 1

DROP TABLE #tmdDateRange

Set @CalcTotalPrice = @TotalPrice * @NumberOfRooms

IF (@AddonSpecArrival = 1 OR @AddonSpecStay = 1 OR @AddonSpecDeparture = 1)
BEGIN
  IF (@AddonSpecArrival = 1)  
    SET @AddonVATAmount = @AddonAmountArrival *(1-(1/convert(float,((@AddonSpecVat/100)+1))))
  IF (@AddonSpecStay = 1)
    SET @AddonVATAmount = @AddonVATAmount + @AddonAmountStay *(1-(1/convert(float,((@AddonSpecVat/100)+1))))
  IF (@AddonSpecDeparture = 1)
    SET @AddonVATAmount = @AddonVATAmount + @AddonAmountDeparture *(1-(1/convert(float,((@AddonSpecVat/100)+1))))
END 
ELSE
  SET @AddonVATAmount = 0

SET @AddonAmount = @AddonAmountArrival + @AddonAmountStay + @AddonAmountDeparture