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 @AutoInc Bigint
Declare @ShowPrice Char(1)
Declare @PriceRow Char(1)

DELETE FROM dbo.Tmp_Over_Under_Row WHERE AutoId IN(SELECT AutoId FROM dbo.TmpHavaleh WHERE Id_Havaleh = @IdHavaleh)

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
  /**********************************************************************/    
  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) 
  /**********************************************************************/    
    Select @TaxValue = @Qty * @GoodsPrice * @TaxFlag
    Select @TotPrice = @Qty * @GoodsPrice  

	Declare Cursor_TmpOverUnderRow Cursor For
	Select AutoId_Inc, Max_Percent, Max_Price, Price, ShowPrice, Price_Row From Tmp_Over_Under_Row Where AutoId = @AutoId
	Open Cursor_TmpOverUnderRow
	Fetch Next From Cursor_TmpOverUnderRow Into @AutoInc, @MaxPercent, @MaxPrice , @Price, @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 @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 IF (@MaxPercent > 0 and @TmpPer > @MaxPercent) 
				BEGIN
					 UPDATE Tmp_Over_Under_Row SET [Percent] = @MaxPercent ,
					 Price = ROUND(@CachePrice * @MaxPercent /100,0) WHERE AutoId_Inc = @AutoInc
                END 
				ELSE
					UPDATE Tmp_Over_Under_Row Set [Percent] = @TmpPer, Price = ROUND(@CachePrice,0) WHERE AutoId_Inc = @AutoInc
			END 
			ELSE IF @MaxPercent > 0 
			BEGIN
				SET @TmpPrice = (@CachePrice * @MaxPercent/100)  
            
				IF @ShowPrice = 2 
				  UPDATE Tmp_Over_Under_Row SET [Percent] = @MaxPercent,Price = ROUND(@TmpPrice,0) WHERE AutoId_Inc = @AutoInc

				ELSE IF (@MaxPrice > 0 and @TmpPrice > @MaxPrice) 
				BEGIN
				  IF @CachePrice = 0 SET @CachePrice = 1
					 UPDATE Tmp_Over_Under_Row SET [Percent] = (@MaxPrice * 100/@CachePrice),
					 Price = @MaxPrice WHERE AutoId_Inc = @AutoInc
				END
				ELSE
					UPDATE Tmp_Over_Under_Row Set [Percent] = @MaxPercent, Price = ROUND(@TmpPrice,0) WHERE AutoId_Inc = @AutoInc
			END
		END 
		Fetch Next From Cursor_TmpOverUnderRow Into @AutoInc, @MaxPercent, @MaxPrice , @Price, @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
