CREATE Procedure [dbo].[Sp_Auto_OURow_Factor] @KindFlag Smallint, @FactorId BIGINT, @PersonPercent INT 
As 

Declare @AutoId Bigint
Declare @Value Float
Declare @MaxPercent Real
Declare @MaxPrice Money
Declare @TotPrice Money
Declare @TaxValue Money
Declare @CachePrice Money
Declare @Qty Money
Declare @GoodsPrice Money
Declare @TaxFlag Smallint
Declare @TmpPrice Money
Declare @TmpPer Real
Declare @Price MONEY
Declare @Percent Real
Declare @AutoInc UNIQUEIDENTIFIER
Declare @ShowPrice Char(1)
Declare @PriceRow Char(1)

DECLARE @CalcIncrementalDeductionRow AS TABLE
(
   Id UNIQUEIDENTIFIER DEFAULT(NEWID()),
   GoodsId BIGINT,
   Autoid BIGINT,
   Name NVARCHAR(30),
   Type CHAR(1),
   Code_Over_Under SMALLINT,
   Max_Percent REAL,
   Max_price MONEY,
   ShowPrice CHAR(1),
   Price_Row CHAR(1),
   Price MONEY DEFAULT(0),
   [Percent] REAL DEFAULT(0),
   Qty MONEY,
   GoodsPrice MONEY,
   TaxFlag CHAR(1)
)


INSERT Into @CalcIncrementalDeductionRow (GoodsId, AutoId, Name, Type, Code_Over_Under, Max_Percent, Max_Price, ShowPrice, Price_Row, Price, [Percent], Qty,GoodsPrice, TaxFlag)
SELECT GoodsId, IncrementalDeductionRow.AutoId, Name, Type, IncrementalDeductionRow.Code_Over_Under, Max_Percent, Max_Price, ShowPrice, IncrementalDeductionRow.Price_Row, 0,0,Factor.K_Qty1, Factor.Price, Factor.Takhfif_Flag FROM (
SELECT Tmpheadh.Id_Havaleh , Autoid GoodsId, K_Qty1 , 
CASE
  WHEN Kind_Flag IN(5,7,9,6,0) THEN Price_Forosh
  WHEN Kind_Flag IN(2,3,4,8) THEN Price_KHarid
END AS Price,Takhfif_Flag FROM TmpHavaleh WITH (NOLOCK)
INNER JOIN TmpHeadh WITH (NOLOCK) ON Tmpheadh.Id_Havaleh = dbo.TmpHavaleh.Id_Havaleh
Where Tmpheadh.Id_Havaleh = @FactorId
) AS Factor
CROSS JOIN 
(
 SELECT Autoid, Name, Type, Over_Under.AutoId Code_Over_Under,
 CASE 
	WHEN ShowPrice = 2 THEN @PersonPercent
	ELSE Max_Percent  
 END as Max_Percent, 
 Max_Price, ShowPrice , ISNULL(Formula,'0') Price_Row, SortDisplay
 FROM Over_Under WITH (NOLOCK) 
 WHERE Active = 1 
 AND Action_Code = @KindFlag  
 AND CONVERT(VARCHAR(10), GETDATE(),111) BETWEEN AzTarikh AND TaTarikh
 ) AS IncrementalDeductionRow 
 ORDER BY Factor.GoodsId, IncrementalDeductionRow.SortDisplay

Declare CursorIncrementalDeductionRow Cursor For
SELECT GoodsId, Qty , GoodsPrice, TaxFlag FROM @CalcIncrementalDeductionRow
GROUP BY GoodsId, Qty , GoodsPrice, TaxFlag
Open CursorIncrementalDeductionRow
Fetch Next From CursorIncrementalDeductionRow Into  @AutoId, @Qty , @GoodsPrice,@TaxFlag
While @@FETCH_STATUS = 0 
Begin
    Select @TaxValue = @Qty * @GoodsPrice * @TaxFlag
    Select @TotPrice = @Qty * @GoodsPrice  

	Declare Cursor_TmpOverUnderRow Cursor For
    SELECT Id, Max_Percent, Max_Price, Price, [percent] [PercentValue], ShowPrice, Price_Row From @CalcIncrementalDeductionRow Where GoodsId = @AutoId
	AND ShowPrice IN(1,2)
	Open Cursor_TmpOverUnderRow
	Fetch Next From Cursor_TmpOverUnderRow Into @AutoInc, @MaxPercent, @MaxPrice , @Price, @Percent, @ShowPrice, @PriceRow
	while @@FETCH_STATUS = 0 
	Begin
		IF @PriceRow = 0
			SET @CachePrice = @TotPrice
		else
			SET @CachePrice = @TaxValue

		IF @MaxPrice > 0
		BEGIN
            IF (ISNULL(@Price,0) > @MaxPrice)
			BEGIN  				
				IF @CachePrice = 0 SET @CachePrice = 1
				SET @TmpPer = @MaxPrice * 100/@CachePrice

				IF @ShowPrice = 2 
					UPDATE @CalcIncrementalDeductionRow SET [Percent] = @TmpPer,Price = ROUND(@MaxPrice,0) WHERE Id = @AutoInc
                ELSE 
					UPDATE @CalcIncrementalDeductionRow Set [Percent] = @TmpPer, Price = ROUND(@CachePrice,0) WHERE Id = @AutoInc
            END
			ELSE
			BEGIN
				IF (ISNULL(@Percent,0) = 0) OR (ISNULL(@Price,0) = 0)
				BEGIN 
				IF @CachePrice = 0 SET @CachePrice = 1
				SET @TmpPer = @MaxPrice * 100/@CachePrice
				UPDATE @CalcIncrementalDeductionRow Set [Percent] = @TmpPer, Price = ROUND(@MaxPrice,0) WHERE Id = @AutoInc
				END 
			END  
		END 
		ELSE IF @MaxPercent > 0 
		BEGIN
			IF (ISNULL(@Percent,0) > @MaxPercent)
			BEGIN  
				SET @TmpPrice = (@CachePrice * @MaxPercent/100)  
				UPDATE @CalcIncrementalDeductionRow Set [Percent] = @MaxPercent, Price = ROUND(@TmpPrice,0) WHERE Id = @AutoInc
            END
			ELSE 
            BEGIN
				IF (ISNULL(@Percent,0) = 0) OR (ISNULL(@Price,0) = 0)
				BEGIN 
				SET @TmpPrice = (@CachePrice * @MaxPercent/100)  
				UPDATE @CalcIncrementalDeductionRow Set [Percent] = @MaxPercent, Price = ROUND(@TmpPrice,0) WHERE Id = @AutoInc
				END 
            END
		END
		Fetch Next From Cursor_TmpOverUnderRow Into @AutoInc, @MaxPercent, @MaxPrice , @Price, @Percent, @ShowPrice, @PriceRow
	End 
	Close Cursor_TmpOverUnderRow
	Deallocate Cursor_TmpOverUnderRow
	/**********************************************************************/    
    INSERT INTO dbo.Tmp_Over_Under_Row(AutoId, Name, Type, Code_Over_Under, Max_Percent, Max_Price, ShowPrice, Price_Row,Price,[Percent])
    SELECT GoodsId ,Name, Type, AutoId, Max_Percent, Max_Price, ShowPrice, Price_Row,Price,[Percent] FROM @CalcIncrementalDeductionRow
	WHERE GoodsId = @AutoId
	AND AutoId NOT IN(SELECT Code_Over_Under FROM Tmp_Over_Under_Row WHERE AutoId = @AutoId)

	Exec dbo.Sp_Duty_Row @FactorId, @AutoId, @KindFlag
    /**********************************************************************/    
    Fetch Next From CursorIncrementalDeductionRow Into @AutoId, @Qty , @GoodsPrice,@TaxFlag
End
Close CursorIncrementalDeductionRow
Deallocate CursorIncrementalDeductionRow


