CREATE PROCEDURE [dbo].[CalcIncrementalDeductionFactor] @FactorId BIGINT, @KindFactor TINYINT, @PersonCode VARCHAR(5) , @PersonPercent REAL , @IsCalcIncrementalDeductionRow BIT
AS
	BEGIN TRY
	BEGIN TRAN

	DECLARE @Id UNIQUEIDENTIFIER
	DECLARE @PriceRow MONEY
    DECLARE @Maxpercent REAL 
    DECLARE @MaxPrice MONEY 
	DECLARE @Price Money
	DECLARE @ShowPrice CHAR(1)
    DECLARE @TotPrice MONEY
	DECLARE @TotTax MONEY 
	DECLARE @CachePrice MONEY 

	DECLARE @FactorDetail AS TABLE
    (
	  Id BIGINT PRIMARY KEY,
	  FactorId BIGINT,
	  Qty MONEY,
	  Price MONEY,
	  IsTax bit
	)

	DECLARE @IncrementalDeductionFactor AS TABLE
	(
      Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT(NEWID())  ,
	  Id_Havaleh BIGINT, 	  
	  Code_Over_Under smallint, 
	  Name NVARCHAR(30),
	  Type char(1),
	  Act_Code smallint, 
	  Price_Row MONEY,
	  Guy_Code varchar(5),
	  Max_Price money,
	  Max_Percent real,
	  ShowPrice CHAR(1),
	  price MONEY DEFAULT(0),
	  [percent] REAL DEFAULT(0),
	  Sorting CHAR(1)
	)

	INSERT INTO @FactorDetail(Id, FactorId, Qty,Price,IsTax)
	SELECT Autoid, Id_Havaleh, K_Qty1, 
	CASE
      WHEN @KindFactor IN (2,3,4,8) THEN Price_KHarid
	  WHEN @KindFactor IN (5,6,7,8,9,0) THEN Price_Forosh
	END price , 
	Takhfif_Flag FROM dbo.TmpHavaleh WITH (NOLOCK)
	WHERE Id_Havaleh = @FactorId

    SET @TotTax = 0 
	SELECT @TotTax   = SUM(Qty*Price) FROM @FactorDetail WHERE IsTax = 1
    SELECT @TotPrice = SUM(Qty*Price) FROM @FactorDetail 

	/****************************************************(Insert IncrementalDeductionFactor)***************************************************/
	INSERT INTO @IncrementalDeductionFactor(Id_Havaleh,Code_Over_Under, Name, Type, Act_Code, Price_Row,Guy_Code,Max_Price, Max_Percent, ShowPrice, Sorting)
	SELECT @FactorId, AutoId , Name, Type, Act_Code,
	CASE                                                                                         
	  WHEN ISNULL(Formula, 0) = 0	 THEN 0 
	  WHEN LTRIM(RTRIM(Formula)) = 1 THEN 1 
	END AS Price_Row,@PersonCode,Max_Price,Max_Percent,ShowPrice, SortDisplay FROM Over_Under With (Nolock) 
	WHERE Active = 1 AND Action_Code = @KindFactor AND ShowPrice <> 0
	AND CONVERT(VARCHAR(10), GETDATE(),111) BETWEEN AzTarikh AND TaTarikh
	ORDER By SortDisplay
	
	IF @IsCalcIncrementalDeductionRow = 1 
	BEGIN 
	    /****************************************(Insert IncrementalDeductionRow)**********************************************/
		DELETE dbo.Tmp_Over_Under_Row FROM dbo.Tmp_Over_Under_Row WITH (NOLOCK)
		INNER JOIN dbo.Over_Under ON Over_Under.AutoId = Tmp_Over_Under_Row.Code_Over_Under
		INNER JOIN dbo.TmpHavaleh WITH (NOLOCK) ON TmpHavaleh.AutoId = Tmp_Over_Under_Row.AutoId
		WHERE dbo.TmpHavaleh.Id_Havaleh = @FactorId AND Over_Under.ShowPrice <> 0

        DELETE dbo.Tmp_Over_Under_Factor FROM dbo.Tmp_Over_Under_Factor
        INNER JOIN Tmpheadh ON TmpHeadH.Id_Havaleh = Tmp_Over_Under_Factor.Id_Havaleh
        WHERE dbo.Tmp_Over_Under_Factor.Id_Havaleh = @FactorId
        And ISNULL(Items,0) <> -1 AND ShowPrice <> 0

		EXEC dbo.Sp_Auto_OURow_Factor @KindFactor, @FactorId, @PersonPercent

	    /****************************************(Replace IncrementalDeductionRow On IncrementalDeductionFactor)**********************************************/
		UPDATE @IncrementalDeductionFactor SET price = PublicTable.Price
		FROM @IncrementalDeductionFactor Inner Join
		(
		   SELECT Code_Over_Under,IsNull(Sum(Price),0) Price
		   FROM Tmp_Over_Under_Row With (Nolock) Inner Join TmpHavaleh With (Nolock)
		   ON Tmp_Over_Under_Row.AutoId = TmpHavaleh.AutoId
		   WHERE TmpHavaleh.Id_Havaleh = @FactorId 
		   AND Tmp_Over_Under_Row.Price <> 0
		   GROUP By Code_Over_Under
		 ) As PublicTable
		ON [@IncrementalDeductionFactor].Code_Over_Under = PublicTable.Code_Over_Under
		Inner Join Over_Under With (Nolock) on [@IncrementalDeductionFactor].Code_Over_Under = Over_Under.AutoId
		Where [@IncrementalDeductionFactor].Id_Havaleh = @FactorId And Over_Under.Formula <> 1

		INSERT INTO dbo.Tmp_Over_Under_Factor(Id_Havaleh,Name,Code_Over_Under, Type,Price, [Percent], Act_Code, Auto_id_Havaleh, Price_Row, Guy_Code, Max_Price, Max_percent, ShowPrice)
		SELECT Id_Havaleh, dbo.Over_Under.name,  Code_Over_Under,dbo.Over_Under.Type, ISNULL(Sum(Price),0) Price, (ISNULL(Sum(Price),0) * 100)/@TotPrice, Act_Code, 0, 0, @PersonCode, 0, 0 ,Over_Under.ShowPrice
		FROM Tmp_Over_Under_Row With (Nolock) Inner Join TmpHavaleh With (Nolock)
		ON Tmp_Over_Under_Row.AutoId = TmpHavaleh.AutoId
		INNER JOIN dbo.Over_Under ON Over_Under.AutoId = Tmp_Over_Under_Row.Code_Over_Under
		WHERE TmpHavaleh.Id_Havaleh = @FactorId And Tmp_Over_Under_Row.Price <> 0
		AND Code_Over_Under NOT IN(SELECT Code_over_Under FROM dbo.Tmp_Over_Under_Factor With (Nolock) WHERE Id_Havaleh =@FactorId)
		GROUP By Code_Over_Under, Id_Havaleh, dbo.Over_Under.Name, Over_Under.Type, Act_Code, Over_Under.ShowPrice

	END
    /****************************************(Navigate IncrementalDeductionFactor)*****************************/ 
	UPDATE @IncrementalDeductionFactor SET Price = Max_Price
	WHERE ShowPrice =1 AND Max_Price >0

	UPDATE @IncrementalDeductionFactor SET [percent] = Max_Percent
	WHERE ShowPrice =1 AND Max_Percent >0

	UPDATE @IncrementalDeductionFactor SET [percent] = @PersonPercent, Max_Percent =@PersonPercent
	WHERE ShowPrice =2 
	
	UPDATE @IncrementalDeductionFactor SET price = 0 , [Percent] = 0 
	WHERE ((ShowPrice = 2 AND @PersonPercent = 0) OR (ShowPrice = 3 AND Max_Price = 0 AND Price > 0))
    
	DECLARE CursorIncDedFactor CURSOR FOR
	SELECT Id, Price, price_Row, Max_Price, Max_Percent, ShowPrice FROM @IncrementalDeductionFactor
	WHERE ShowPrice IN(1,2) AND (Max_Price <> 0 OR Max_Percent <> 0)
	OPEN CursorIncDedFactor
	FETCH NEXT FROM CursorIncDedFactor INTO @Id, @Price, @PriceRow, @MaxPrice, @Maxpercent, @ShowPrice
	WHILE @@FETCH_STATUS = 0 
	BEGIN
	  IF @PriceRow = 0
	    SET @CachePrice = @TotPrice
	  ELSE
        SET @CachePrice = @TotTax

	  IF (@Maxpercent > 0 AND @MaxPrice = 0)
	    UPDATE @IncrementalDeductionFactor SET price= ISNULL(ROUND((Max_Percent * @CachePrice)/100,0),0) WHERE id = @Id 
	  ELSE IF (@MaxPrice > 0 AND @Maxpercent = 0)
	    UPDATE @IncrementalDeductionFactor SET [percent]= ISNULL(ROUND((@MaxPrice * 100)/@CachePrice,2),0) WHERE id = @Id 

	  FETCH NEXT FROM CursorIncDedFactor INTO @Id, @Price, @PriceRow, @MaxPrice, @Maxpercent, @ShowPrice
    END
    CLOSE CursorIncDedFactor
	DEALLOCATE CursorIncDedFactor

	UPDATE @IncrementalDeductionFactor SET [percent] = 0 WHERE [percent] <> 0 AND price = 0 
	UPDATE @IncrementalDeductionFactor SET Price = 0 WHERE price <> 0 AND [Percent] = 0  

	/*****************************************(Delete IncrementalDeductionFactor Except Eshop)*************************************************/
	INSERT INTO dbo.Tmp_Over_Under_Factor
	(
	    Id_Havaleh,
	    Name,
	    Code_Over_Under,
	    Type,
	    Price,
	    [Percent],
	    Act_Code,
	    Auto_id_Havaleh,
	    Price_Row,
	    Guy_Code,
	    Max_Price,
	    Max_percent,
	    ShowPrice
	)
	SELECT Id_Havaleh, Name,  Code_Over_Under, Type, Price, [Percent], Act_Code,  0,  Price_Row,  
	Guy_Code,  Max_Price, Max_percent, ShowPrice FROM @IncrementalDeductionFactor
	WHERE Code_Over_Under NOT IN(SELECT Code_over_Under 
	FROM dbo.Tmp_Over_Under_Factor With (Nolock) WHERE Id_Havaleh =@FactorId)
	AND price <> 0 AND [percent] <> 0
	ORDER BY Sorting

	UPDATE dbo.Tmp_Over_Under_Factor SET price = PublicTable.Price, [Percent] = (PublicTable.Price*100) /@TotPrice
	FROM Tmp_Over_Under_Factor 
	INNER Join
	(
		SELECT Code_Over_Under,IsNull(Sum(Price),0) Price
		FROM Tmp_Over_Under_Row With (Nolock) Inner Join TmpHavaleh With (Nolock)
		ON Tmp_Over_Under_Row.AutoId = TmpHavaleh.AutoId
		WHERE TmpHavaleh.Id_Havaleh = @FactorId AND ISNULL(Kind_Act,0) <> 0
		GROUP By Code_Over_Under
		) As PublicTable
	ON Tmp_Over_Under_Factor.Code_Over_Under = PublicTable.Code_Over_Under
	Inner Join Over_Under With (Nolock) on Tmp_Over_Under_Factor.Code_Over_Under = Over_Under.AutoId
	Where Tmp_Over_Under_Factor.Id_Havaleh = @FactorId And Over_Under.Formula <> 1
	AND Over_Under.ShowPrice = 0 

	UPDATE dbo.Tmp_Over_Under_Factor SET Guy_Code = @PersonCode WHERE Id_Havaleh = @FactorId AND Guy_Code <> @PersonCode
	DELETE FROM dbo.Tmp_Over_Under_Factor WHERE price = 0 AND [Percent] = 0 AND Auto_id_Havaleh = 0 AND Id_Havaleh = @FactorId
	DELETE FROM dbo.Tmp_Over_Under_Factor WHERE ShowPrice = 2 AND @PersonPercent = 0 AND Id_Havaleh = @FactorId

	/****************************************(Calc Tax On Factor)**********************************************/ 
	EXEC dbo.Sp_Duty @FactorId,  @TotTax , @KindFactor

	COMMIT TRAN
	END TRY
	BEGIN CATCH
		IF CURSOR_STATUS('global','CursorIncDedFactor') = 1
		BEGIN
			CLOSE CursorIncDedFactor
			DEALLOCATE CursorIncDedFactor
		END

		DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT;
		SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
		IF @@TRANCOUNT > 0
			ROLLBACK TRAN
		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH
