CREATE PROCEDURE dbo.RoomTypeDifferentialUpdatePricesInvalidList @ARoomTypeDifferentialIdCSV VARCHAR(200), @AStartDate DATETIME, @AEndDate DATETIME, @ABasePrice FLOAT, @ANumberOfDecimals INT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen November 2013
  Get list of invalid prices when updateing room type differentials
  
  Changes:
    KJ 20220217: Ext: Multiple periods.
    KJ 20210923: Ext: Added period to Room type differentials / Grids
    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 @FieldType VARCHAR(50)
DECLARE @FieldName VARCHAR(50)
DECLARE @sql NVARCHAR(4000)
DECLARE @SelectProductNumber INT
DECLARE @DayType INT
DECLARE @dtRTDFrom DATETIME
DECLARE @dtRTDTo DATETIME
DECLARE @dtFrom DATETIME
DECLARE @dtTo DATETIME

DECLARE @DateStamp DATETIME
DECLARE @ProductNumber INT
DECLARE @Price MONEY
DECLARE @MinPrice MONEY
DECLARE @MaxPrice MONEY

DECLARE @CurDate DATETIME
SET @CurDate = @AStartDate

SELECT @MinPrice = CONVERT(MONEY, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'RoomTypeDifferentialMinPrice'
SELECT @MaxPrice = CONVERT(MONEY, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'RoomTypeDifferentialMaxPrice'
SET @MinPrice = ISNULL(@MinPrice, 0)
SET @MaxPrice = ISNULL(@MaxPrice, 0)

CREATE TABLE #InvalidPrices (
  DateStamp DATETIME,
  ProductNumber INT,
  FieldName VARCHAR(25),
  RoomTypeNumber INT,
  Price MONEY
)

IF @MinPrice > 0 OR @MaxPrice > 0
BEGIN
  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
    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, @SelectProductNumber, @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'DECLARE db_cur_invalid CURSOR FOR ' +
        'SELECT ProductNumber, CONVERT(DATETIME, DateStamp, 112), RoomTypeNumber, ROUND(' + CAST(@ABasePrice AS VARCHAR) + ' + ' + CAST(@Ratio AS VARCHAR) + ', ' + CAST(@ANumberOfDecimals AS VARCHAR) + ') ' +
        'FROM ProductPrice WITH (NOLOCK) ' +
        'WHERE ProductNumber = '+ CAST(@SelectProductNumber 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 (ROUND(' + CAST(@ABasePrice AS VARCHAR) + ' + ' + CAST(@Ratio AS VARCHAR) + ', ' + CAST(@ANumberOfDecimals AS VARCHAR) + ') > ' + CAST(@MaxPrice AS VARCHAR) + ' OR ' +
        ' ROUND(' + CAST(@ABasePrice AS VARCHAR) + ' + ' + CAST(@Ratio AS VARCHAR) + ', ' + CAST(@ANumberOfDecimals AS VARCHAR) + ') < ' + CAST(@MinPrice AS VARCHAR) + ') ' +
        'AND (' + CAST(@DayType AS VARCHAR) + ' = ''0'' OR POWER(2, DATEPART(dw, DateStamp)-1) & ' + CAST(@DayType AS VARCHAR) + ' > 0)'

    END
    ELSE
    BEGIN
      SET @sql = N'DECLARE db_cur_invalid CURSOR FOR ' +
        'SELECT ProductNumber, CONVERT(DATETIME, DateStamp, 112), RoomTypeNumber, ROUND(' + CAST(@ABasePrice AS VARCHAR) + ' + (' + CAST(@ABasePrice AS VARCHAR) + ' * (' + CAST(@Ratio AS VARCHAR) + '/100.0)), ' + CAST(@ANumberOfDecimals AS VARCHAR) + ' ) ' +
        'FROM ProductPrice WITH (NOLOCK) ' +
        'WHERE ProductNumber = '+ CAST(@SelectProductNumber 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 (ROUND(' + CAST(@ABasePrice AS VARCHAR) + ' + (' + CAST(@ABasePrice AS VARCHAR) + ' * (' + CAST(@Ratio AS VARCHAR) + '/100.0)), ' + CAST(@ANumberOfDecimals AS VARCHAR) + ' ) > ' + CAST(@MaxPrice AS VARCHAR) + ' OR ' +
        ' ROUND(' + CAST(@ABasePrice AS VARCHAR) + ' + (' + CAST(@ABasePrice AS VARCHAR) + ' * (' + CAST(@Ratio AS VARCHAR) + '/100.0)), ' + CAST(@ANumberOfDecimals AS VARCHAR) + ' ) < ' + CAST(@MinPrice AS VARCHAR) + ') ' +
        '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''

    OPEN db_cur_invalid
    FETCH NEXT FROM db_cur_invalid INTO @ProductNumber, @DateStamp, @RoomTypeNumber, @Price
    WHILE @@FETCH_STATUS = 0
    BEGIN
      INSERT #InvalidPrices VALUES (@DateStamp, @ProductNumber, @FieldName, @RoomTypeNumber, @Price)
  
      FETCH NEXT FROM db_cur_invalid INTO @ProductNumber, @DateStamp, @RoomTypeNumber, @Price
    END
  
    CLOSE db_cur_invalid
    DEALLOCATE db_cur_invalid
  
    FETCH NEXT FROM db_cur INTO @RoomTypeNumber, @PersonType, @PersonCount, @Ratio, @InPercent, @SelectProductNumber, @DayType, @dtRTDFrom, @dtRTDTo
  END

  CLOSE db_cur
  DEALLOCATE db_cur
END

SELECT #InvalidPrices.DateStamp, #InvalidPrices.FieldName, #InvalidPrices.Price, Products.ProductName, RoomTypes.Type
FROM #InvalidPrices
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #InvalidPrices.RoomTypeNumber
INNER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = #InvalidPrices.ProductNumber

DROP TABLE #InvalidPrices