CREATE PROCEDURE DBO.RecalculateProductPrices @TheProductNumber INT,  @FromDate DATETIME, @ToDate DATETIME, @ModifyType INT, @Round INT,
						@Monday BIT, @Tuesday BIT, @Wednesday BIT, @Thursday BIT, @Friday BIT, @Saturday BIT, @Sunday BIT,
						@Adult1 FLOAT (53), @Adult2 FLOAT (53), @Adult3 FLOAT (53), @Adult4 FLOAT (53), @Adult5 FLOAT (53), @Adult6 FLOAT (53), @Adult7 FLOAT (53), @Adult8 FLOAT (53), @Adult9 FLOAT (53),
						@Child11 FLOAT (53), @Child12 FLOAT (53), @Child13 FLOAT (53), @Child14 FLOAT (53), @Child15 FLOAT (53), @Child16 FLOAT (53), @Child17 FLOAT (53), @Child18 FLOAT (53), @Child19 FLOAT (53),
						@Child21 FLOAT (53), @Child22 FLOAT (53), @Child23 FLOAT (53), @Child24 FLOAT (53), @Child25 FLOAT (53), @Child26 FLOAT (53), @Child27 FLOAT (53), @Child28 FLOAT (53), @Child29 FLOAT (53) AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen 03/2-2003

    Last modified by: 
    20130121 RN: BUG: Replaced weekdays type from TINYINT to BIT in parameter list.
    20060221 JP: BUG: Arithmetic overflow error converting numeric to data type smallmoney
    17/02-2003

    @ModifyType: 0 Percent     1 Fixed

    @Round: 0 : None (two decimals)   1 : OneDecimal   2 : Integer (no decimals)

    NOTE: CONVERT from FLOAT to CHAR only has precision of 6 decimals!!!! But when system only works at 2 decimals this should not be a problem!?

                @SQLStr used because of DayOfWeek stuff. Couldn't be done nicely in SQL

*/

DECLARE @SystemDate DATETIME
SELECT @SystemDate=CONVERT (DATETIME, SystemDate) FROM System WITH (NOLOCK) WHERE Number=1
IF @FromDate < @SystemDate
  SET @FromDate = @SystemDate
IF @ToDate < @SystemDate
  SET @ToDate = @SystemDate

DECLARE @DayOfWeek VARCHAR (15)
SET @DayOfWeek = '('
IF @Monday=1
  SET @DayOfWeek = @DayOfWeek+'2'

IF @Tuesday=1
BEGIN
  IF @DayOfWeek <> '('
    SET @DayOfWeek = @DayOfWeek+','
  SET @DayOfWeek = @DayOfWeek+'3'
END

IF @Wednesday=1
BEGIN
  IF @DayOfWeek <> '('
    SET @DayOfWeek = @DayOfWeek+','
  SET @DayOfWeek = @DayOfWeek+'4'
END

IF @Thursday=1
BEGIN
  IF @DayOfWeek <> '('
    SET @DayOfWeek = @DayOfWeek+','
  SET @DayOfWeek = @DayOfWeek+'5'
END

IF @Friday=1
BEGIN
  IF @DayOfWeek <> '('
    SET @DayOfWeek = @DayOfWeek+','
  SET @DayOfWeek = @DayOfWeek+'6'
END

IF @Saturday=1
BEGIN
  IF @DayOfWeek <> '('
    SET @DayOfWeek = @DayOfWeek+','
  SET @DayOfWeek = @DayOfWeek+'7'
END

IF @Sunday=1
BEGIN
  IF @DayOfWeek <> '('
    SET @DayOfWeek = @DayOfWeek+','
  SET @DayOfWeek = @DayOfWeek+'1'
END
SET @DayOfWeek = @DayOfWeek+')'

DECLARE @SQLStr VARCHAR (5000)

IF @ModifyType in (0, 2)  -- 0: Percent  2: Percent (VAT type)
BEGIN
  IF @ModifyType = 0
  BEGIN
    SET @Adult1=1+(@Adult1/100)
    SET @Adult2=1+(@Adult2/100)
    SET @Adult3=1+(@Adult3/100)
    SET @Adult4=1+(@Adult4/100)
    SET @Adult5=1+(@Adult5/100)
    SET @Adult6=1+(@Adult6/100)
    SET @Adult7=1+(@Adult7/100)
    SET @Adult8=1+(@Adult8/100)
    SET @Adult9=1+(@Adult9/100)

    SET @Child11=1+(@Child11/100)
    SET @Child12=1+(@Child12/100)
    SET @Child13=1+(@Child13/100)
    SET @Child14=1+(@Child14/100)
    SET @Child15=1+(@Child15/100)
    SET @Child16=1+(@Child16/100)
    SET @Child17=1+(@Child17/100)
    SET @Child18=1+(@Child18/100)
    SET @Child19=1+(@Child19/100)

    SET @Child21=1+(@Child21/100)
    SET @Child22=1+(@Child22/100)
    SET @Child23=1+(@Child23/100)
    SET @Child24=1+(@Child24/100)
    SET @Child25=1+(@Child25/100)
    SET @Child26=1+(@Child26/100)
    SET @Child27=1+(@Child27/100)
    SET @Child28=1+(@Child28/100)
    SET @Child29=1+(@Child29/100)
  END
  ELSE IF @ModifyType = 2
  BEGIN
    /*    + 25% = * 1.25    - 25% = * 0.80    */
    IF @Adult1<0
      SET @Adult1=1/(1+(ABS (@Adult1)/100))
    ELSE 
      SET @Adult1 = 1+@Adult1/100
    IF @Adult2<0
      SET @Adult2=1/(1+(ABS (@Adult2)/100))
    ELSE 
      SET @Adult2 = 1+@Adult2/100
    IF @Adult3<0
      SET @Adult3=1/(1+(ABS (@Adult3)/100))
    ELSE 
      SET @Adult3 = 1+@Adult3/100
    IF @Adult4<0
      SET @Adult4=1/(1+(ABS (@Adult4)/100))
    ELSE 
      SET @Adult4 = 1+@Adult4/100
    IF @Adult5<0
      SET @Adult5=1/(1+(ABS (@Adult5)/100))
    ELSE 
      SET @Adult5 = 1+@Adult5/100
    IF @Adult6<0
      SET @Adult6=1/(1+(ABS (@Adult6)/100))
    ELSE 
      SET @Adult6 = 1+@Adult6/100
    IF @Adult7<0
      SET @Adult7=1/(1+(ABS (@Adult7)/100))
    ELSE 
      SET @Adult7 = 1+@Adult7/100
    IF @Adult8<0
      SET @Adult8=1/(1+(ABS (@Adult8)/100))
    ELSE 
      SET @Adult8 = 1+@Adult8/100
    IF @Adult9<0
      SET @Adult9=1/(1+(ABS (@Adult9)/100))
    ELSE 
      SET @Adult9 = 1+@Adult9/100

    IF @Child11<0
      SET @Child11=1/(1+(ABS (@Child11)/100))
    ELSE 
      SET @Child11 = 1+@Child11/100
    IF @Child12<0
      SET @Child12=1/(1+(ABS (@Child12)/100))
    ELSE 
      SET @Child12 = 1+@Child12/100
    IF @Child13<0
      SET @Child13=1/(1+(ABS (@Child13)/100))
    ELSE 
      SET @Child13 = 1+@Child13/100
    IF @Child14<0
      SET @Child14=1/(1+(ABS (@Child14)/100))
    ELSE 
      SET @Child14 = 1+@Child14/100
    IF @Child15<0
      SET @Child15=1/(1+(ABS (@Child15)/100))
    ELSE 
      SET @Child15 = 1+@Child15/100
    IF @Child16<0
      SET @Child16=1/(1+(ABS (@Child16)/100))
    ELSE 
      SET @Child16 = 1+@Child16/100
    IF @Child17<0
      SET @Child17=1/(1+(ABS (@Child17)/100))
    ELSE 
      SET @Child17 = 1+@Child17/100
    IF @Child18<0
      SET @Child18=1/(1+(ABS (@Child18)/100))
    ELSE 
      SET @Child18 = 1+@Child18/100
    IF @Child19<0
      SET @Child19=1/(1+(ABS (@Child19)/100))
    ELSE 
      SET @Child19 = 1+@Child19/100

    IF @Child21<0
      SET @Child21=1/(1+(ABS (@Child21)/100))
    ELSE 
      SET @Child21 = 1+@Child21/100
    IF @Child22<0
      SET @Child22=1/(1+(ABS (@Child22)/100))
    ELSE 
      SET @Child22 = 1+@Child22/100
    IF @Child23<0
      SET @Child23=1/(1+(ABS (@Child23)/100))
    ELSE 
      SET @Child23 = 1+@Child23/100
    IF @Child24<0
      SET @Child24=1/(1+(ABS (@Child24)/100))
    ELSE 
      SET @Child24 = 1+@Child24/100
    IF @Child25<0
      SET @Child25=1/(1+(ABS (@Child25)/100))
    ELSE 
      SET @Child25 = 1+@Child25/100
    IF @Child26<0
      SET @Child26=1/(1+(ABS (@Child26)/100))
    ELSE 
      SET @Child26 = 1+@Child26/100
    IF @Child27<0
      SET @Child27=1/(1+(ABS (@Child27)/100))
    ELSE 
      SET @Child27 = 1+@Child27/100
    IF @Child28<0
      SET @Child28=1/(1+(ABS (@Child28)/100))
    ELSE 
      SET @Child28 = 1+@Child28/100
    IF @Child29<0
      SET @Child29=1/(1+(ABS (@Child29)/100))
    ELSE 
      SET @Child29 = 1+@Child29/100
  END

  SET @SQLStr = '
    UPDATE ProductPrice SET
    AdultPrice1=AdultPrice1*'+CONVERT (CHAR, @Adult1)+',
    AdultPrice2=AdultPrice2*'+CONVERT (CHAR, @Adult2)+',
    AdultPrice3=AdultPrice3*'+CONVERT (CHAR, @Adult3)+',
    AdultPrice4=AdultPrice4*'+CONVERT (CHAR, @Adult4)+',
    AdultPrice5=AdultPrice5*'+CONVERT (CHAR, @Adult5)+',
    AdultPrice6=AdultPrice6*'+CONVERT (CHAR, @Adult6)+',
    AdultPrice7=AdultPrice7*'+CONVERT (CHAR, @Adult7)+',
    AdultPrice8=AdultPrice8*'+CONVERT (CHAR, @Adult8)+',
    AdultPrice9=AdultPrice9*'+CONVERT (CHAR, @Adult9)+',
    Children1Price1=Children1Price1*'+CONVERT (CHAR, @Child11)+',
    Children1Price2=Children1Price2*'+CONVERT (CHAR, @Child12)+',
    Children1Price3=Children1Price3*'+CONVERT (CHAR, @Child13)+',
    Children1Price4=Children1Price4*'+CONVERT (CHAR, @Child14)+',
    Children1Price5=Children1Price5*'+CONVERT (CHAR, @Child15)+',
    Children1Price6=Children1Price6*'+CONVERT (CHAR, @Child16)+',
    Children1Price7=Children1Price7*'+CONVERT (CHAR, @Child17)+',
    Children1Price8=Children1Price8*'+CONVERT (CHAR, @Child18)+',
    Children1Price9=Children1Price9*'+CONVERT (CHAR, @Child19)+',
    Children2Price1=Children2Price1*'+CONVERT (CHAR, @Child21)+',
    Children2Price2=Children2Price2*'+CONVERT (CHAR, @Child22)+',
    Children2Price3=Children2Price3*'+CONVERT (CHAR, @Child23)+',
    Children2Price4=Children2Price4*'+CONVERT (CHAR, @Child24)+',
    Children2Price5=Children2Price5*'+CONVERT (CHAR, @Child25)+',
    Children2Price6=Children2Price6*'+CONVERT (CHAR, @Child26)+',
    Children2Price7=Children2Price7*'+CONVERT (CHAR, @Child27)+',
    Children2Price8=Children2Price8*'+CONVERT (CHAR, @Child28)+',
    Children2Price9=Children2Price9*'+CONVERT (CHAR, @Child29)+'
    WHERE ProductNumber='+CONVERT (CHAR, @TheProductNumber)+' AND DateStamp>='+CONVERT (CHAR, @FromDate, 112)+' AND DateStamp<='+CONVERT (CHAR, @ToDate, 112)+' AND DATEPART (WEEKDAY, DateStamp) IN '+@DayOfWeek
  EXECUTE (@SQLStr)
END
ELSE IF @ModifyType = 1 -- Fixed
BEGIN
/*  SET @SQLStr = '
    UPDATE ProductPrice SET
    AdultPrice1=AdultPrice1+'+CONVERT (CHAR, @Adult1)+',
    AdultPrice2=AdultPrice2+'+CONVERT (CHAR, @Adult2)+',
    AdultPrice3=AdultPrice3+'+CONVERT (CHAR, @Adult3)+',
    AdultPrice4=AdultPrice4+'+CONVERT (CHAR, @Adult4)+',
    AdultPrice5=AdultPrice5+'+CONVERT (CHAR, @Adult5)+',
    AdultPrice6=AdultPrice6+'+CONVERT (CHAR, @Adult6)+',
    AdultPrice7=AdultPrice7+'+CONVERT (CHAR, @Adult7)+',
    AdultPrice8=AdultPrice8+'+CONVERT (CHAR, @Adult8)+',
    AdultPrice9=AdultPrice9+'+CONVERT (CHAR, @Adult9)+',
    Children1Price1=Children1Price1+'+CONVERT (CHAR, @Child11)+',
    Children1Price2=Children1Price2+'+CONVERT (CHAR, @Child12)+',
    Children1Price3=Children1Price3+'+CONVERT (CHAR, @Child13)+',
    Children1Price4=Children1Price4+'+CONVERT (CHAR, @Child14)+',
    Children1Price5=Children1Price5+'+CONVERT (CHAR, @Child15)+',
    Children1Price6=Children1Price6+'+CONVERT (CHAR, @Child16)+',
    Children1Price7=Children1Price7+'+CONVERT (CHAR, @Child17)+',
    Children1Price8=Children1Price8+'+CONVERT (CHAR, @Child18)+',
    Children1Price9=Children1Price9+'+CONVERT (CHAR, @Child19)+',
    Children2Price1=Children2Price1+'+CONVERT (CHAR, @Child21)+',
    Children2Price2=Children2Price2+'+CONVERT (CHAR, @Child22)+',
    Children2Price3=Children2Price3+'+CONVERT (CHAR, @Child23)+',
    Children2Price4=Children2Price4+'+CONVERT (CHAR, @Child24)+',
    Children2Price5=Children2Price5+'+CONVERT (CHAR, @Child25)+',
    Children2Price6=Children2Price6+'+CONVERT (CHAR, @Child26)+',
    Children2Price7=Children2Price7+'+CONVERT (CHAR, @Child27)+',
    Children2Price8=Children2Price8+'+CONVERT (CHAR, @Child28)+',
    Children2Price9=Children2Price9+'+CONVERT (CHAR, @Child29)+'
    WHERE ProductNumber='+CONVERT (CHAR, @TheProductNumber)+' AND DateStamp>='+CONVERT (CHAR, @FromDate, 112)+' AND DateStamp<='+CONVERT (CHAR, @ToDate, 112)+' AND DATEPART (WEEKDAY, DateStamp) IN '+@DayOfWeek */
  DECLARE @cnt SMALLINT
  DECLARE @Ad FLOAT (53)
  DECLARE @C1 FLOAT (53)
  DECLARE @C2 FLOAT (53)
  SET @cnt=1
  WHILE @cnt < 10
  BEGIN
    IF @cnt=1
    BEGIN
      SET @Ad = @Adult1
      SET @C1 = @Child11
      SET @C2 = @Child21
    END
    ELSE IF @cnt=2
    BEGIN
      SET @Ad = @Adult2
      SET @C1 = @Child12
      SET @C2 = @Child22
    END
    ELSE IF @cnt=3
    BEGIN
      SET @Ad = @Adult3
      SET @C1 = @Child13
      SET @C2 = @Child23
    END
    ELSE IF @cnt=4
    BEGIN
      SET @Ad = @Adult4
      SET @C1 = @Child14
      SET @C2 = @Child24
    END
    ELSE IF @cnt=5
    BEGIN
      SET @Ad = @Adult5
      SET @C1 = @Child15
      SET @C2 = @Child25
    END
    ELSE IF @cnt=6
    BEGIN
      SET @Ad = @Adult6
      SET @C1 = @Child16
      SET @C2 = @Child26
    END
    ELSE IF @cnt=7
    BEGIN
      SET @Ad = @Adult7
      SET @C1 = @Child17
      SET @C2 = @Child27
    END
    ELSE IF @cnt=8
    BEGIN
      SET @Ad = @Adult8
      SET @C1 = @Child18
      SET @C2 = @Child28
    END
    ELSE IF @cnt=9
    BEGIN
      SET @Ad = @Adult9
      SET @C1 = @Child19
      SET @C2 = @Child29
    END

    SET @SQLStr = '
      UPDATE ProductPrice SET AdultPrice'+CONVERT(VARCHAR, @cnt)+'=AdultPrice'+CONVERT(VARCHAR, @cnt)+'+'+CONVERT (VARCHAR, @Ad)+'
      WHERE AdultPrice'+CONVERT(VARCHAR, @cnt)+'<>0 AND ProductNumber='+CONVERT (VARCHAR, @TheProductNumber)+' AND DateStamp>='+CONVERT (CHAR, @FromDate, 112)+' AND DateStamp<='+CONVERT (CHAR, @ToDate, 112)+' AND DATEPART (WEEKDAY, DateStamp) IN '+@DayOfWeek 
    EXECUTE (@SQLStr)

    SET @SQLStr = '
      UPDATE ProductPrice SET Children1Price'+CONVERT(VARCHAR, @cnt)+'=Children1Price'+CONVERT(VARCHAR, @cnt)+'+'+CONVERT (VARCHAR, @C1)+'
      WHERE Children1Price'+CONVERT(VARCHAR, @cnt)+'<>0 AND ProductNumber='+CONVERT (VARCHAR, @TheProductNumber)+' AND DateStamp>='+CONVERT (CHAR, @FromDate, 112)+' AND DateStamp<='+CONVERT (CHAR, @ToDate, 112)+' AND DATEPART (WEEKDAY, DateStamp) IN '+@DayOfWeek 
    EXECUTE (@SQLStr)

    SET @SQLStr = '
      UPDATE ProductPrice SET Children2Price'+CONVERT(VARCHAR, @cnt)+'=Children2Price'+CONVERT(VARCHAR, @cnt)+'+'+CONVERT (VARCHAR, @C2)+'
      WHERE Children2Price'+CONVERT(VARCHAR, @cnt)+'<>0 AND ProductNumber='+CONVERT (VARCHAR, @TheProductNumber)+' AND DateStamp>='+CONVERT (CHAR, @FromDate, 112)+' AND DateStamp<='+CONVERT (CHAR, @ToDate, 112)+' AND DATEPART (WEEKDAY, DateStamp) IN '+@DayOfWeek 
    EXECUTE (@SQLStr)

    SET @cnt=@cnt+1
  END
END

-- Round prices --
--SET @Round= - @Round
DECLARE @RoundStr CHAR (2)
SET @RoundStr = CONVERT (CHAR, -@Round)

SET @SQLStr = '
  UPDATE ProductPrice SET
  AdultPrice1=ROUND (CONVERT(MONEY,AdultPrice1) * 100, '+@RoundStr+') / 100,
  AdultPrice2=ROUND (CONVERT(MONEY,AdultPrice2) * 100, '+@RoundStr+') / 100,
  AdultPrice3=ROUND (CONVERT(MONEY,AdultPrice3) * 100, '+@RoundStr+') / 100,
  AdultPrice4=ROUND (CONVERT(MONEY,AdultPrice4) * 100, '+@RoundStr+') / 100,
  AdultPrice5=ROUND (CONVERT(MONEY,AdultPrice5) * 100, '+@RoundStr+') / 100,
  AdultPrice6=ROUND (CONVERT(MONEY,AdultPrice6) * 100, '+@RoundStr+') / 100,
  AdultPrice7=ROUND (CONVERT(MONEY,AdultPrice7) * 100, '+@RoundStr+') / 100,
  AdultPrice8=ROUND (CONVERT(MONEY,AdultPrice8) * 100, '+@RoundStr+') / 100,
  AdultPrice9=ROUND (CONVERT(MONEY,AdultPrice9) * 100, '+@RoundStr+') / 100,
  Children1Price1=ROUND (CONVERT(MONEY,Children1Price1) * 100, '+@RoundStr+') / 100,
  Children1Price2=ROUND (CONVERT(MONEY,Children1Price2) * 100, '+@RoundStr+') / 100,
  Children1Price3=ROUND (CONVERT(MONEY,Children1Price3) * 100, '+@RoundStr+') / 100,
  Children1Price4=ROUND (CONVERT(MONEY,Children1Price4) * 100, '+@RoundStr+') / 100,
  Children1Price5=ROUND (CONVERT(MONEY,Children1Price5) * 100, '+@RoundStr+') / 100,
  Children1Price6=ROUND (CONVERT(MONEY,Children1Price6) * 100, '+@RoundStr+') / 100,
  Children1Price7=ROUND (CONVERT(MONEY,Children1Price7) * 100, '+@RoundStr+') / 100,
  Children1Price8=ROUND (CONVERT(MONEY,Children1Price8) * 100, '+@RoundStr+') / 100,
  Children1Price9=ROUND (CONVERT(MONEY,Children1Price9) * 100, '+@RoundStr+') / 100,
  Children2Price1=ROUND (CONVERT(MONEY,Children2Price1) * 100, '+@RoundStr+') / 100,
  Children2Price2=ROUND (CONVERT(MONEY,Children2Price2) * 100, '+@RoundStr+') / 100,
  Children2Price3=ROUND (CONVERT(MONEY,Children2Price3) * 100, '+@RoundStr+') / 100,
  Children2Price4=ROUND (CONVERT(MONEY,Children2Price4) * 100, '+@RoundStr+') / 100,
  Children2Price5=ROUND (CONVERT(MONEY,Children2Price5) * 100, '+@RoundStr+') / 100,
  Children2Price6=ROUND (CONVERT(MONEY,Children2Price6) * 100, '+@RoundStr+') / 100,
  Children2Price7=ROUND (CONVERT(MONEY,Children2Price7) * 100, '+@RoundStr+') / 100,
  Children2Price8=ROUND (CONVERT(MONEY,Children2Price8) * 100, '+@RoundStr+') / 100,
  Children2Price9=ROUND (CONVERT(MONEY,Children2Price9) * 100, '+@RoundStr+') / 100
  WHERE ProductNumber='+CONVERT (CHAR, @TheProductNumber)+' AND DateStamp>='+CONVERT (CHAR, @FromDate, 112)+' AND DateStamp<='+CONVERT (CHAR, @ToDate, 112)+' AND DATEPART (WEEKDAY, DateStamp) IN '+@DayOfWeek
EXECUTE (@SQLStr)

/*
select round (666.66 * 100 , 0) / 100  -- two decimals
select round (666.66 * 100 , -1) / 100  -- one decimals
select round (666.66 * 100 , -2) / 100  -- integer

select round (666.6 * 100 , 0) / 100
select round (666.6 * 100 , -1) / 100
select round (666.6 * 100 , -2) / 100
*/

