CREATE procedure dbo.ProductPricePeriod @ReservationArrival char(8) , @ReservationDeparture char(8) , @adults int, @children1 int, @children2 int, @ProductNumber int, @RoomTypeNumber int, @PeriodStartDate char(8) , @PeriodEndDate char(8), @OnlyAddon BIT = 0, @RealAdults INT = -1, @RealChildren1 INT = -1, @RealChildren2 INT = -1, @TotalProductPrice MONEY OUTPUT, @InclAddon BIT = 1  AS
SET NOCOUNT ON

/*
 Changes:
   KJ 20120501: Bug: Fox for "Price wrong if multiple pax and fixed price on day product". Case #CR042840
   KJ 20100310: Bug: Fix for "Addon wrong calculated - also use hotelsetup setting"
   KJ 20100115: Bug: Fix for "Don't show a resultset"
   KJ 20090717: Bug: Fix for "Children1 + Children2 price calculated wrong for DayProducts" case# 9278
   KJ 20090430: Ext: Fix for "Exclude Addon from price"
   KJ 20080710: Bug: Fix for Dayproducts price calculated wrong.
   KJ 20080604: Bug: Fix for Dayproducts calculated wrong.
   KJ 20071106: StayAddon not calculated correct.
   KJ 20071106: Added TotalPrice as OUTPUT parameter
   KJ 20070814: Fix for "One person pays for all gives wrong result"
   KJ 20061024: Fix for "the SUM function requires 1 argument"
   KJ 20060721: AddonType should be read from product and not hotelsetup.
   KJ 20060716: Almost complete Rewrite of calc of Addon
   KJ 20060716: Added AddonPerPerson 
   KJ 20060716: Added @ShowPrint  
   Sidst rettet af KS d. 10/5-2004

*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

declare @sql Nvarchar(1000)
declare @result money
declare @datestamp datetime
declare @arrivaldate datetime
declare @departuredate datetime
declare @adultfield varchar(25)
declare @children1field varchar(25)
declare @children2field varchar(25)
declare @allfields varchar(800)
declare @Arrivaladdonadultsfields varchar(50)
declare @Arrivaladdonchildren1fields varchar(50)
declare @Arrivaladdonchildren2fields varchar(50)
declare @Stayaddonadultsfields varchar(50)
declare @Stayaddonchildren1fields varchar(50)
declare @Stayaddonchildren2fields varchar(50)
declare @departureAddonadultsfields varchar(50)
declare @departureAddonchildren1fields varchar(50)
declare @departureAddonchildren2fields varchar(50)

DECLARE @AdultPrice MONEY
DECLARE @Children1Price MONEY
DECLARE @Children2Price MONEY

--Declare @TotalProductPrice money
DECLARE @AdultAddon MONEY
DECLARE @Children1Addon MONEY
DECLARE @Children2Addon MONEY
DECLARE @AddOnFieldsPerPerson BIT
DECLARE @AddOnFieldsPerPersonProduct BIT
DECLARE @InternalPeriodEndDate VARCHAR(8)

DECLARE @OnePersonPaysForAll BIT

DECLARE @DayProduct BIT
DECLARE @FixedPrice BIT

DECLARE @tmpAdults INT
DECLARE @tmpChildren1 INT
DECLARE @tmpChildren2 INT

SET @Arrivaladdonadultsfields = ''
SET @Arrivaladdonchildren1fields = ''
SET @Arrivaladdonchildren2fields = ''
SET @Stayaddonadultsfields = ''
SET @Stayaddonchildren1fields = ''
SET @Stayaddonchildren2fields = ''
SET @departureAddonadultsfields = ''
SET @departureAddonchildren1fields = ''
SET @departureAddonchildren2fields = ''

SET @InternalPeriodEndDate = CONVERT(VARCHAR(8), CONVERT(DATETIME, @PeriodEndDate, 112) + 1, 112)

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

SELECT @AddOnFieldsPerPersonProduct = AddonType FROM Products WITH (NOLOCK) WHERE ProductNumber = @ProductNumber
SET @AddOnFieldsPerPersonProduct = ISNULL(@AddOnFieldsPerPersonProduct, 0)

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

SET @result = 0
SET @TotalProductPrice = 0

SET @InclAddon = ISNULL(@InclAddon, 1)

if @adults > 9 set @adults = 9
if @children1 > 9 set @children1 = 9
if @children2 > 9 set @children2 = 9

IF @ShowPrint = 1
BEGIN
  PRINT @ReservationArrival
  PRINT @PeriodStartDate
  PRINT @ReservationDeparture
  PRINT @PeriodEndDate
  PRINT @InternalPeriodEndDate
  PRINT @adults
END

SELECT @DayProduct = DayProduct, @FixedPrice = FixedPrice FROM Products WITH (NOLOCK) WHERE ProductNumber = @ProductNumber

if @ReservationArrival = @PeriodStartDate
begin
  if @adults > 1 AND @AddOnFieldsPerPerson = 1
    set @ArrivalAddonadultsfields = 'AdultArrivalAddon' + Convert(VarChar(2), @adults) 
  else
    set @Arrivaladdonadultsfields = 'AdultArrivalAddon'
  if @children1 > 1 AND @AddOnFieldsPerPerson = 1
    set @Arrivaladdonchildren1fields = 'Children1ArrivalAddon' + Convert(VarChar(2), @children1) 
  else
    set @Arrivaladdonchildren1fields = 'Children1ArrivalAddon'
  if @children2 > 1 AND @AddOnFieldsPerPerson = 1
    set @Arrivaladdonchildren2fields = 'Children2ArrivalAddon' + Convert(VarChar(2), @children2) 
  else
    set @Arrivaladdonchildren2fields = 'Children2ArrivalAddon'
end

if @ReservationDeparture = @InternalPeriodEndDate
begin
  if @adults > 1 AND @AddOnFieldsPerPerson = 1
    set @departureaddonadultsfields = 'AdultDepartureAddon' + Convert(VarChar(2), @adults) 
  else
    set @departureaddonadultsfields = 'AdultDepartureAddon'
  if @children1 > 1 AND @AddOnFieldsPerPerson = 1
    set @departureaddonchildren1fields = 'Children1DepartureAddon' + Convert(VarChar(2), @children1) 
  else
    set @departureaddonchildren1fields = 'Children1DepartureAddon'
  if @children2 > 1 AND @AddOnFieldsPerPerson = 1
    set @departureaddonchildren2fields = 'Children2DepartureAddon' + Convert(VarChar(2), @children2) 
  else
    set @departureaddonchildren2fields = 'Children2DepartureAddon'
end

if (@ReservationArrival <> @PeriodStartDate) AND (@ReservationDeparture >= @InternalPeriodEndDate)
begin
  if @adults > 1 AND @AddOnFieldsPerPerson = 1
    set @Stayaddonadultsfields = 'AdultStayAddon' + Convert(VarChar(2), @adults) 
  else
    set @Stayaddonadultsfields = 'AdultStayAddon'
  if @children1 > 1 AND @AddOnFieldsPerPerson = 1
    set @Stayaddonchildren1fields = 'Children1StayAddon' + Convert(VarChar(2), @children1) 
  else
    set @Stayaddonchildren1fields = 'Children1StayAddon'
  if @children2 > 1 AND @AddOnFieldsPerPerson = 1
    set @Stayaddonchildren2fields = 'Children2StayAddon' + Convert(VarChar(2), @children2) 
  else
    set @Stayaddonchildren2fields = 'Children2StayAddon'
end

IF @OnlyAddon = 0
BEGIN
  IF @DayProduct = 1
  BEGIN
    SET @tmpAdults = 0
    SET @tmpChildren1 = 0
    SET @tmpChildren2 = 0
    IF @Adults >= 1 
    BEGIN
      SET @tmpAdults = @Adults
      SET @Adults = 1
    END
    IF @Children1 >= 1 
    BEGIN
      SET @tmpChildren1 = @Children1
      SET @Children1 = 1
    END
    IF @Children2 >= 1 
    BEGIN
      SET @tmpChildren2 = @Children2
      SET @Children2 = 1
    END
  END

  IF @OnePersonPaysForAll = 0 AND (@RealAdults > -1 OR @RealChildren1 > -1 OR @RealChildren2 > -1)
  BEGIN
    SET @TotalProductPrice = 0
    if @adults>0
      set @adultfield = 'AdultPrice' +  + Convert(VarChar(2), @adults)
    else
      set @adultfield = '0'
    if @children1>0
      set @children1field = 'Children1Price' +  + Convert(VarChar(2), @children1)
    else
      set @children1field = '0' 
    if @children2>0
      set @children2field = 'Children2Price' +  + Convert(VarChar(2), @children2)
    else
      set @children2field = '0' 

    SET @sql = N'select ' + '@AdultPrice = SUM('+@adultfield+'), @Children1Price = SUM('+@children1field +'), @Children2Price = SUM('+@children2field +') from ProductPrice WITH (nolock) where productnumber = ' + convert(varchar(10), @productnumber) + ' and datestamp >= ' + @PeriodStartDate + ' and datestamp <= ' + @PeriodEndDate + ' and Roomtypenumber = ' + convert(varchar(10), @roomtypenumber)
    IF @ShowPrint = 1
      PRINT @sql
    exec sp_executesql @sql, N'@AdultPrice MONEY OUTPUT, @Children1Price MONEY OUTPUT, @Children2Price MONEY OUTPUT', @AdultPrice OUTPUT, @Children1Price OUTPUT, @Children2Price OUTPUT

    IF ISNULL(@AdultPrice, 0) = 0 
      SET @AdultPrice = 0
    SET @TotalProductPrice = @TotalProductPrice + (@AdultPrice*@RealAdults)

    IF ISNULL(@Children1Price, 0) = 0 
      SET @Children1Price = 0
    SET @TotalProductPrice = @TotalProductPrice + (@Children1Price*@RealChildren1)

    IF ISNULL(@Children2Price, 0) = 0 
      SET @Children2Price = 0
    SET @TotalProductPrice = @TotalProductPrice + (@Children2Price*@RealChildren2)
  END
  ELSE
  BEGIN
    if @adults>0
      set @adultfield = 'AdultPrice' +  + Convert(VarChar(2), @adults)
    else
      set @adultfield = '0'
    if @children1>0
      set @children1field = 'Children1Price' +  + Convert(VarChar(2), @children1)
    else
      set @children1field = '0' 
    if @children2>0
      set @children2field = 'Children2Price' +  + Convert(VarChar(2), @children2)
    else
      set @children2field = '0' 

    SET @TotalProductPrice = 0

    IF @DayProduct = 0 
      SET @sql = N'select ' + '@result = SUM('+@adultfield+ ' + ' +@children1field + ' + ' +@children2field +')  from ProductPrice WITH (nolock) where productnumber = ' + convert(varchar(10), @productnumber) + ' and datestamp >= ' + @PeriodStartDate + ' and datestamp <= ' + @PeriodEndDate + ' and Roomtypenumber = ' + convert(varchar(10), @roomtypenumber)
    ELSE 
    BEGIN
      IF @FixedPrice = 1
        SET @sql = N'select ' + '@result = SUM(' + @adultfield + ') + SUM(' + @children1field + ') + SUM(' + @children2field + ') FROM ProductPrice WITH (NOLOCK) WHERE ProductNumber = ' + convert(varchar(10), @productnumber) + ' and datestamp >= ' + @PeriodStartDate + ' and datestamp <= ' + @PeriodEndDate + ' AND Roomtypenumber = ' + convert(varchar(10), @roomtypenumber)
      ELSE
        SET @sql = N'select ' + '@result = SUM('+@adultfield+ ')*' + CONVERT(VARCHAR, @tmpAdults) + ' + SUM('+@children1field+ ')*' + CONVERT(VARCHAR, @tmpChildren1) + ' + SUM('+@children2field+ ')*' + CONVERT(VARCHAR, @tmpChildren2) + ' FROM ProductPrice WITH (NOLOCK) WHERE ProductNumber = ' + convert(varchar(10), @productnumber) + ' and datestamp >= ' + @PeriodStartDate + ' and datestamp <= ' + @PeriodEndDate + ' AND Roomtypenumber = ' + convert(varchar(10), @roomtypenumber)
    END
  
    IF @ShowPrint = 1
      print @sql
    exec sp_executesql @sql, N'@result money OUTPUT', @result OUTPUT

    IF ISNULL(@result, 0) = 0 
      SET @result = 0
    SET @TotalProductPrice = @TotalProductPrice + @result
  END
END

IF @ShowPrint = 1
BEGIN
  PRINT convert(char,@result)
  PRINT @ReservationArrival 
  PRINT @PeriodStartDate
  PRINT @ReservationDeparture
  PRINT @PeriodEndDate
  PRINT @InternalPeriodEndDate
  PRINT @AddOnFieldsPerPerson
  PRINT @AddOnFieldsPerPersonProduct
  PRINT @Adults
END

SET @AdultAddon = 0
SET @Children1Addon = 0
SET @Children2Addon = 0

if @ReservationArrival = @PeriodStartDate 
begin
  IF @ShowPrint = 1
    PRINT 'ArrivalAddon'

  IF @ArrivalAddonadultsfields = ''
    SET @ArrivalAddonadultsfields = '0'
  IF @Arrivaladdonchildren1fields = ''
    SET @Arrivaladdonchildren1fields = '0'
  IF @Arrivaladdonchildren2fields = ''
    SET @Arrivaladdonchildren2fields = '0'

  IF @InclAddon = 0
  BEGIN
    SET @ArrivalAddonadultsfields = '0'
    SET @Arrivaladdonchildren1fields = '0'
    SET @Arrivaladdonchildren2fields = '0'
  END

  set @sql = N'select ' + '@AdultAddon = SUM('+@ArrivalAddonadultsfields+ '), @Children1Addon = SUM(' +@Arrivaladdonchildren1fields+ '), @Children2Addon = SUM(' +@Arrivaladdonchildren2fields +')  from ProductPrice WITH (nolock) where productnumber = ' + convert(varchar(10), @productnumber) + ' and datestamp = ' + @PeriodStartDate  + ' and Roomtypenumber = ' + convert(varchar(10), @roomtypenumber)
  IF @ShowPrint = 1
    PRINT @sql
  exec sp_executesql @sql, N'@AdultAddon MONEY OUTPUT, @Children1Addon MONEY OUTPUT, @Children2Addon MONEY OUTPUT', @AdultAddon OUTPUT, @Children1Addon OUTPUT, @Children2Addon OUTPUT

  IF ISNULL(@AdultAddon,0)=0 SET @AdultAddon = 0
  IF ISNULL(@Children1Addon,0)=0 SET @Children1Addon = 0
  IF ISNULL(@Children2Addon,0)=0 SET @Children2Addon = 0

  IF @ShowPrint = 1
  BEGIN
    PRINT CONVERT(VARCHAR(10), @AdultAddon)
    PRINT CONVERT(VARCHAR(10), @Children1Addon)
    PRINT CONVERT(VARCHAR(10), @Children2Addon)
  END

  IF @AddOnFieldsPerPersonProduct = 0
  BEGIN
    IF @RealAdults > -1 OR @RealChildren1 > -1 OR @RealChildren2 > -1
      SET @TotalProductPrice = @TotalProductPrice + (@AdultAddon * @RealAdults) + (@Children1Addon * @RealChildren1) + (@Children2Addon * @RealChildren2)
    ELSE 
      SET @TotalProductPrice = @TotalProductPrice + (@AdultAddon * @Adults) + (@Children1Addon * @Children1) + (@Children2Addon * @Children2)
  END
  ELSE
    SET @TotalProductPrice = @TotalProductPrice + @AdultAddon + @Children1Addon + @Children2Addon
end

SET @AdultAddon = 0
SET @Children1Addon = 0
SET @Children2Addon = 0

if @ReservationDeparture = @InternalPeriodEndDate
begin
  IF @ShowPrint = 1
    PRINT 'DepartureAddon'

  IF @DepartureAddonAdultsFields = ''
    SET @DepartureAddonAdultsFields = '0'
  IF @DepartureAddonChildren1Fields = ''
    SET @DepartureAddonChildren1Fields = '0'
  IF @DepartureAddonChildren2Fields = ''
    SET @DepartureAddonChildren2Fields = '0'

  IF @InclAddon = 0
  BEGIN
    SET @DepartureAddonAdultsFields = '0'
    SET @DepartureAddonChildren1Fields = '0'
    SET @DepartureAddonChildren2Fields = '0'
  END

  set @sql = N'select @AdultAddon = SUM('+@DepartureAddonAdultsFields+ '), @Children1Addon = SUM(' +@DepartureAddonChildren1Fields+ '), @Children2Addon = SUM(' +@DepartureAddonChildren2Fields +')  from ProductPrice WITH (nolock) where productnumber = ' + convert(varchar(10), @productnumber) + ' and datestamp = ' + @ReservationDeparture  + ' and Roomtypenumber = ' + convert(varchar(10), @roomtypenumber)
  IF @ShowPrint = 1
    PRINT @sql
  exec sp_executesql @sql, N'@AdultAddon MONEY OUTPUT, @Children1Addon MONEY OUTPUT, @Children2Addon MONEY OUTPUT', @AdultAddon OUTPUT, @Children1Addon OUTPUT, @Children2Addon OUTPUT

  IF ISNULL(@AdultAddon,0)=0 SET @AdultAddon = 0
  IF ISNULL(@Children1Addon,0)=0 SET @Children1Addon = 0
  IF ISNULL(@Children2Addon,0)=0 SET @Children2Addon = 0

  IF @ShowPrint = 1
  BEGIN
    PRINT CONVERT(VARCHAR(10), @AdultAddon)
    PRINT CONVERT(VARCHAR(10), @Children1Addon)
    PRINT CONVERT(VARCHAR(10), @Children2Addon)
  END

  IF @AddOnFieldsPerPersonProduct = 0
  BEGIN
    IF @RealAdults > -1 OR @RealChildren1 > -1 OR @RealChildren2 > -1
      SET @TotalProductPrice = @TotalProductPrice + (@AdultAddon * @RealAdults) + (@Children1Addon * @RealChildren1) + (@Children2Addon * @RealChildren2)
    ELSE 

      SET @TotalProductPrice = @TotalProductPrice + (@AdultAddon * @Adults) + (@Children1Addon * @Children1) + (@Children2Addon * @Children2)
  END
  ELSE
    SET @TotalProductPrice = @TotalProductPrice + @AdultAddon + @Children1Addon + @Children2Addon
end

SET @AdultAddon = 0
SET @Children1Addon = 0
SET @Children2Addon = 0

IF (@ReservationArrival <> @PeriodStartDate) AND (@ReservationDeparture <> @InternalPeriodEndDate)
begin
  IF @ShowPrint = 1
    PRINT 'StayAddon'

  IF @StayAddonadultsfields = ''
    SET @StayAddonadultsfields = '0'
  IF @StayAddonchildren1fields = ''
    SET @StayAddonchildren1fields = '0'
  IF @StayAddonchildren2fields = ''
    SET @StayAddonchildren2fields = '0'

  IF @InclAddon = 0
  BEGIN
    SET @StayAddonadultsfields = '0'
    SET @StayAddonchildren1fields = '0'
    SET @StayAddonchildren2fields = '0'
  END

  set @sql = N'select @AdultAddon = SUM('+@StayAddonadultsfields+ '), @Children1Addon = SUM(' +@StayAddonchildren1fields+ '), @Children2Addon = SUM(' +@StayAddonchildren2fields +')  from ProductPrice WITH (nolock) where productnumber = ' + convert(varchar(10), @productnumber) + ' and datestamp = ' + @PeriodStartDate  + ' and Roomtypenumber = ' + convert(varchar(10), @roomtypenumber)
  IF @ShowPrint = 1
    PRINT @sql
  exec sp_executesql @sql, N'@AdultAddon MONEY OUTPUT, @Children1Addon MONEY OUTPUT, @Children2Addon MONEY OUTPUT', @AdultAddon OUTPUT, @Children1Addon OUTPUT, @Children2Addon OUTPUT

  IF ISNULL(@AdultAddon,0)=0 SET @AdultAddon = 0
  IF ISNULL(@Children1Addon,0)=0 SET @Children1Addon = 0
  IF ISNULL(@Children2Addon,0)=0 SET @Children2Addon = 0

  IF @ShowPrint = 1
  BEGIN
    PRINT CONVERT(VARCHAR(10), @AdultAddon)
    PRINT CONVERT(VARCHAR(10), @Children1Addon)
    PRINT CONVERT(VARCHAR(10), @Children2Addon)
  END

  IF @AddOnFieldsPerPersonProduct = 0
  BEGIN
    IF @RealAdults > -1 OR @RealChildren1 > -1 OR @RealChildren2 > -1
      SET @TotalProductPrice = @TotalProductPrice + (@AdultAddon * @RealAdults) + (@Children1Addon * @RealChildren1) + (@Children2Addon * @RealChildren2)
    ELSE 
      SET @TotalProductPrice = @TotalProductPrice + (@AdultAddon * @Adults) + (@Children1Addon * @Children1) + (@Children2Addon * @Children2)
  END
  ELSE
    SET @TotalProductPrice = @TotalProductPrice + @AdultAddon + @Children1Addon + @Children2Addon
end

IF @ShowPrint = 1
  print convert(char,@TotalProductPrice)