CREATE Procedure [dbo].[Sp_Auto_OURow_Factor] @KindFlag Smallint, @IdHavaleh Bigint 
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 Bigint
Declare @ShowPrice Char(1)
Declare @PriceRow Char(1)

Declare Cursor_TmpHavaleh Cursor For
Select Tmpheadh.Id_Havaleh, Autoid, 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
INNER JOIN TmpHeadh ON Tmpheadh.Id_Havaleh = dbo.TmpHavaleh.Id_Havaleh
Where Tmpheadh.Id_Havaleh = @IdHavaleh
Open Cursor_TmpHavaleh
Fetch Next From Cursor_TmpHavaleh Into @IdHavaleh , @AutoId, @Qty , @GoodsPrice,@TaxFlag
While @@FETCH_STATUS = 0 
Begin
  /**********************************************************************/    
   Delete Tmp_Over_Under_Row From Tmp_Over_Under_Row INNER JOIN TmpHavaleh
   ON Tmp_Over_Under_Row.AutoId = TmpHavaleh.AutoId INNER JOIN
   Over_Under ON Tmp_Over_Under_Row.Code_Over_Under = Over_Under.AutoId
   WHERE TmpHavaleh.Id_Havaleh = @IdHavaleh
   And (
        Over_Under.AzTarikh > Convert(Varchar(10), Getdate(),111) OR 
		Over_Under.TaTarikh < Convert(Varchar(10), Getdate(),111) OR 
		Over_Under.Active = 0
	   )

  INSERT Into Tmp_Over_Under_Row (AutoId, Name, Type, Code_Over_Under, Max_Percent, Max_Price, ShowPrice, Price_Row)
  SELECT @AutoId Autoid, Name, Type, Over_Under.AutoId,
  CASE 
		WHEN ShowPrice = 2 THEN (
									SELECT ISNULL(CalcFormul,'0') CalcFormul
									FROM Guy Where Guy_Code = 
									CASE
									  WHEN @kindFlag IN (5,7,9,4) THEN (Select Reciver From TmpHavaleh Where Id_Havaleh =  @IdHavaleh Group by Reciver)
									  ELSE (Select Sender From TmpHavaleh Where Id_Havaleh =  @IdHavaleh Group by Sender)
 									END
								) 
		ELSE Max_Percent  
  END as Max_Percent, 
  Max_Price, ShowPrice , ISNULL(Formula,'0') Formula
  FROM Over_Under 
  WHERE Active = 1 
  AND Action_Code = @KindFlag  
  AND AzTarikh <= Convert(Varchar(10), Getdate(),111) 
  AND TaTarikh >= Convert(Varchar(10), Getdate(),111) 
  AND AutoId NOT IN(SELECT Code_Over_Under FROM dbo.Tmp_Over_Under_Row WHERE AutoId = @AutoId)

  /**********************************************************************/    
    Select @TaxValue = @Qty * @GoodsPrice * @TaxFlag
    Select @TotPrice = @Qty * @GoodsPrice  

	Declare Cursor_TmpOverUnderRow Cursor For
	Select AutoId_Inc, Max_Percent, Max_Price, Price, [percent] [PercentValue], ShowPrice, Price_Row From Tmp_Over_Under_Row Where AutoId = @AutoId
	Open Cursor_TmpOverUnderRow
	Fetch Next From Cursor_TmpOverUnderRow Into @AutoInc, @MaxPercent, @MaxPrice , @Price, @Percent, @ShowPrice, @PriceRow
	while @@FETCH_STATUS = 0 
	Begin
		IF @ShowPrice in (1,2)
		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 Tmp_Over_Under_Row SET [Percent] = @TmpPer,Price = ROUND(@MaxPrice,0) WHERE AutoId_Inc = @AutoInc
                    ELSE 
					  UPDATE Tmp_Over_Under_Row Set [Percent] = @TmpPer, Price = ROUND(@CachePrice,0) WHERE AutoId_Inc = @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 Tmp_Over_Under_Row Set [Percent] = @TmpPer, Price = ROUND(@MaxPrice,0) WHERE AutoId_Inc = @AutoInc
				  END 
				END  
			END 
			ELSE IF @MaxPercent > 0 
			BEGIN
				IF (ISNULL(@Percent,0) > @MaxPercent)
				BEGIN  
					SET @TmpPrice = (@CachePrice * @MaxPercent/100)  
					UPDATE Tmp_Over_Under_Row Set [Percent] = @MaxPercent, Price = ROUND(@TmpPrice,0) WHERE AutoId_Inc = @AutoInc
                END
				ELSE 
                BEGIN
				  IF (ISNULL(@Percent,0) = 0) OR (ISNULL(@Price,0) = 0)
				  BEGIN 
				    SET @TmpPrice = (@CachePrice * @MaxPercent/100)  
				    UPDATE Tmp_Over_Under_Row Set [Percent] = @MaxPercent, Price = ROUND(@TmpPrice,0) WHERE AutoId_Inc = @AutoInc
				  END 
                END
			END
		END 
		Fetch Next From Cursor_TmpOverUnderRow Into @AutoInc, @MaxPercent, @MaxPrice , @Price, @Percent, @ShowPrice, @PriceRow
	End 
	Close Cursor_TmpOverUnderRow
	Deallocate Cursor_TmpOverUnderRow
	/**********************************************************************/    
	Exec dbo.Sp_Duty_Row @idHavaleh, @AutoId, @KindFlag
    /**********************************************************************/    
    Fetch Next From Cursor_TmpHavaleh Into @IdHavaleh , @AutoId, @Qty , @GoodsPrice,@TaxFlag
End
Close Cursor_TmpHavaleh
Deallocate Cursor_TmpHavaleh
