CREATE PROCEDURE [dbo].[RoomTypeDifferentialUpdatePrices] @ARoomTypeDifferentialIdCSV VARCHAR(200), @AStartDate DATETIME, @AEndDate DATETIME, @ABasePrice FLOAT, @ANumberOfDecimals INT, @AHotelCustomerNumber INT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen October 2013
  Update ProductPrices according to setup in RoomTypeDifferentialItem
  
  Changes:
    KJ 20240515: Ext: Added HotelCustomerNumber, for update of prices on this hotel
    KJ 20220217: Ext: Multiple periods.
    KJ 20210923: Ext: Added period to Room type differentials / Grids
    KJ 20190920: Optimized sql
    KJ 20161221: Bug: Fix for "Wrong products is updated when using grids with multiple products related". Case #CR117666
    KJ 20161121: Bug: Fix for "Wrong products is update". Case #CR116384
    KJ 20160526: Added daytype Req #3038
    KJ 20140205: Product number is now a part of the Differential item.
*/

DECLARE @DateFirst INT
SELECT @DateFirst = @@DATEFIRST
SET DATEFIRST 1

DECLARE @RoomTypeNumber INT
DECLARE @PersonType SMALLINT
DECLARE @PersonCount SMALLINT
DECLARE @Ratio FLOAT 
DECLARE @InPercent BIT
DECLARE @sql NVARCHAR(1000)
DECLARE @FieldType VARCHAR(50)
DECLARE @FieldName VARCHAR(50)
DECLARE @ProductNumber INT
DECLARE @DayType INT
DECLARE @dtRTDFrom DATETIME
DECLARE @dtRTDTo DATETIME
DECLARE @dtFrom DATETIME
DECLARE @dtTo DATETIME

DECLARE @CurDate DATETIME
SET @CurDate = @AStartDate

DECLARE db_cur CURSOR FAST_FORWARD FOR
  SELECT RoomTypeDifferentialItem.FK_RoomTypeNumber, RoomTypeDifferentialItem.PersonType, RoomTypeDifferentialItem.PersonCount, 
  RoomTypeDifferentialItem.Ratio, RoomTypeDifferentialItem.InPercent, RoomTypeDifferentialProductsXTab.FKProductNumber, ISNULL(RoomTypeDifferential.DayType, 0),
  RoomTypeDifferentialPeriod.FromDate, RoomTypeDifferentialPeriod.ToDate
  FROM RoomTypeDifferentialItem WITH (NOLOCK)
  INNER JOIN RoomTypeDifferential WITH (NOLOCK) ON RoomTypeDifferential.Id = RoomTypeDifferentialItem.FK_RoomTypeDifferentialId
    AND RoomTypeDifferential.Id IN(SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ARoomTypeDifferentialIdCSV))
  INNER JOIN RoomTypeDifferentialProductsXTab WITH (NOLOCK) ON RoomTypeDifferentialProductsXTab.FKRoomTypeDifferentialId = RoomTypeDifferential.Id
    AND RoomTypeDifferentialProductsXTab.FKProductNumber = RoomTypeDifferentialItem.FK_ProductNumber
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RoomTypeDifferentialItem.FK_RoomTypeNumber
  INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = RoomTypes.HotelNumber
    AND Customer.CustomerTypeNumber = 7
    AND (Customer.HotelNumber > 0 OR LocalHotel = 1)
    AND ISNULL(Customer.MarkAsDeleted, 0) = 0
    AND (@AHotelCustomerNumber <= 0 OR Customer.CustomerNumber = @AHotelCustomerNumber)
  LEFT OUTER JOIN RoomTypeDifferentialPeriod WITH (NOLOCK) ON RoomTypeDifferentialPeriod.FK_RoomTypeDifferentialId = RoomTypeDifferential.Id
  WHERE (
    ((ISNULL(RoomTypeDifferentialPeriod.FromDate, 0) = 0) OR (ISNULL(RoomTypeDifferentialPeriod.ToDate, 0) = 0))
    OR ((RoomTypeDifferentialPeriod.ToDate >= @AStartDate) AND (RoomTypeDifferentialPeriod.FromDate <= @AEndDate))
  )
OPEN db_cur

FETCH NEXT FROM db_cur INTO @RoomTypeNumber, @PersonType, @PersonCount, @Ratio, @InPercent, @ProductNumber, @DayType, @dtRTDFrom, @dtRTDTo

WHILE @@FETCH_STATUS = 0
BEGIN
  IF ISNULL(@dtRTDFrom, 0) > 0 AND CAST(@dtRTDFrom AS DATE) > CAST(@AStartDate AS DATE)
    SET @dtFrom = @dtRTDFrom
  ELSE
    SET @dtFrom = @AStartDate
  IF ISNULL(@dtRTDTO, 0) > 0 AND CAST(@dtRTDTO AS DATE) < CAST(@AEndDate AS DATE)
    SET @dtTo = @dtRTDTo
  ELSE
    SET @dtTo = @AEndDate

  SET @FieldType = 
  CASE @PersonType
    WHEN 0 THEN 'AdultPrice'
    WHEN 1 THEN 'Children1Price'
    WHEN 2 THEN 'Children2Price'
  END

  SET @FieldName = @FieldType + CAST(@PersonCount AS VARCHAR)
  IF @InPercent = 0
  BEGIN
    SET @sql = N'UPDATE ProductPrice SET ' + @FieldName + ' = ROUND(' + CAST(@ABasePrice AS VARCHAR) + ' + ' + CAST(@Ratio AS VARCHAR) + ', ' + CAST(@ANumberOfDecimals AS VARCHAR) + ' ) ' +
      'WHERE ProductNumber = '+ CAST(@ProductNumber AS VARCHAR) + ' ' + 
      'AND DateStamp >= ''' + CONVERT(CHAR(8), @dtFrom, 112) + ''' ' +
      'AND DateStamp <= ''' + CONVERT(CHAR(8), @dtTo, 112) + ''' ' +
      'AND RoomTypeNumber = ' + CAST(@RoomTypeNumber AS VARCHAR) + ' ' +
      'AND ISNULL(' + @FieldName + ', 0) <> 0 ' +
	  'AND (' + CAST(@DayType AS VARCHAR) + ' = 0 OR POWER(2, DATEPART(dw, DateStamp)-1) & ' + CAST(@DayType AS VARCHAR) + ' > 0)'
  END
  ELSE
  BEGIN
    SET @sql = N'UPDATE ProductPrice SET ' + @FieldName + ' = ROUND(' + CAST(@ABasePrice AS VARCHAR) + ' + (' + CAST(@ABasePrice AS VARCHAR) + ' * (' + CAST(@Ratio AS VARCHAR) + '/100.0)), ' + CAST(@ANumberOfDecimals AS VARCHAR) + ' ) ' +
      'WHERE ProductNumber = '+ CAST(@ProductNumber AS VARCHAR) + ' ' + 
      'AND DateStamp >= ''' + CONVERT(CHAR(8), @dtFrom, 112) + ''' ' +
      'AND DateStamp <= ''' + CONVERT(CHAR(8), @dtTo, 112) + ''' ' +
      'AND RoomTypeNumber = ' + CAST(@RoomTypeNumber AS VARCHAR) + ' ' +
      'AND ISNULL(' + @FieldName + ', 0) <> 0 ' +
	  'AND (' + CAST(@DayType AS VARCHAR) + ' = 0 OR POWER(2, DATEPART(dw, DateStamp)-1) & ' + CAST(@DayType AS VARCHAR) + ' > 0)'
  END

--  PRINT @sql
  exec sp_executesql @sql, N''
  
  FETCH NEXT FROM db_cur INTO @RoomTypeNumber, @PersonType, @PersonCount, @Ratio, @InPercent, @ProductNumber, @DayType, @dtRTDFrom, @dtRTDTo
END

CLOSE db_cur
DEALLOCATE db_cur

SET DATEFIRST @DateFirst