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 @iValue1 Money
Declare @iValue2 Money
Declare @iValue Money
Declare @KQty1 Money
Declare @PriceForosh Money
Declare @TakhfifFlag Smallint
Declare @TmpPrice Money
Declare @TmpPer Real
Declare @Price Money
Declare @AutoInc Bigint
Declare @ShowPrice Char(1)
Declare @PriceRow Char(1)

Declare Cursor_TmpHavaleh Cursor For
Select Id_Havaleh, Autoid, K_Qty1 , Price_Forosh, Takhfif_Flag From TmpHavaleh
Where Id_Havaleh = @IdHavaleh
Open Cursor_TmpHavaleh
Fetch Next From Cursor_TmpHavaleh Into @IdHavaleh , @AutoId, @KQty1 , @PriceForosh,@TakhfifFlag
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_TmpH, Name, Type, AutoId_Ou, Max_Percent, Max_Price, ShowPrice, Formula 
 From (
        SELECT TmpH.Id_Havaleh, TmpH.AutoId AS AutoId_Tmph, OU.Name, OU.Type, 
        OU.AutoId AS AutoId_OU, 
        case 
          When Ou.ShowPrice = '2' then (Select 
                                        Case
                                           When CalcFormul = '''' or CalcFormul Is Null then '0'
                                        else 
                                          CalcFormul 
                                        end As CalcFormul                                                  
                                        From Guy Where Guy_Code = (Select Reciver From TmpHavaleh Where Id_Havaleh =  @idHavaleh /*And AutoId = @AutoId*/ Group by Reciver)) 
          else  
          OU.Max_Percent  
        end as Max_Percent, 
        Ou.Max_Price, Ou.ShowPrice , 
        Case  
          When (OU.Formula is Null Or OU.Formula = '') Then '0'  
          Else   
            Ou.Formula 
        End as Formula 
        FROM Over_Under OU CROSS JOIN TmpHavaleh TmpH 
        WHERE  TmpH.Id_Havaleh = @idHavaleh  AND OU.Active = 1 
        AND OU.Action_Code = @KindFlag  
        AND OU.AzTarikh <= Convert(Varchar(10), Getdate(),111) 
        AND OU.TaTarikh >= Convert(Varchar(10), Getdate(),111) 
        AND TmpH.Id_Havaleh =  @idHavaleh And TmpH.AutoId = @AutoId
      ) As PublicTable 
      Where Not Exists (
                         Select * From Tmp_Over_Under_Row 
                         Where AutoId = PublicTable.AutoId_TmpH And Code_Over_Under = PublicTable.AutoId_Ou
                        ) 
      And PublicTable.Id_Havaleh = @idHavaleh
      
      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 TmpHavaleh.AutoId = @AutoId
      And (Over_Under.AzTarikh > Convert(Varchar(10), Getdate(),111) Or 
      Over_Under.TaTarikh < Convert(Varchar(10), Getdate(),111) Or 
      Over_Under.Active = 0)      
      /**********************************************************************/    
      Select @iValue2 = @KQty1 * @PriceForosh * @TakhfifFlag
      Select @iValue1 = @KQty1 * @PriceForosh  

      Declare Cursor_TmpOverUnderRow Cursor For
      Select AutoId_Inc, 
      Case 
        When ShowPrice = '2' then (Select 
                                        Case
                                           When CalcFormul = '''' or CalcFormul Is Null then '0'
                                        else 
                                          CalcFormul 
                                        end As CalcFormul                                                  
                                        From Guy Where Guy_Code = (Select Reciver From TmpHavaleh Where Id_Havaleh =  @idHavaleh /*And AutoId = @AutoId*/ Group by Reciver)) 
          else  
          Max_Percent  
      end As 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 @iValue = @iValue1
          else
            Set @iValue = @iValue2
        
          IF @MaxPercent > 0 
          Begin
            Select @TmpPer = @MaxPercent
            Select @TmpPrice = (@iValue * @MaxPercent/100)  
            
            IF @ShowPrice = '2' 
            Begin
              Update Tmp_Over_Under_Row Set [Percent] = @TmpPer,
              Price = Round(@TmpPrice,0) Where AutoId_Inc = @AutoInc
            End
            Else
            IF (@MaxPrice > 0 and @TmpPrice > @MaxPrice) 
            Begin
              IF @iValue = 0 Set @iValue = 1
              Update Tmp_Over_Under_Row Set [Percent] = @Price / (@iValue * @Maxpercent * 100),
              Price = @MaxPrice Where AutoId_Inc = @AutoInc
            End
            Else
            Begin
              Update Tmp_Over_Under_Row Set [Percent] = @TmpPer,
              Price = Round(@TmpPrice,0) Where AutoId_Inc = @AutoInc
            End
          End --IF @MaxPercent > 0 
          Else
          Begin
            IF @iValue = 0 Set @iValue = 1
            Update Tmp_Over_Under_Row Set [Percent] = (@MaxPrice /@iValue * @MaxPercent * 100)
            , Price = @MaxPrice  Where AutoId_Inc = @AutoInc
          End
        end --IF @ShowPrice = '1'
        Fetch Next From Cursor_TmpOverUnderRow Into @AutoInc, @MaxPercent, @MaxPrice , @Price, @ShowPrice, @PriceRow
      End -- while @@FETCH_STATUS = 0 
      Close Cursor_TmpOverUnderRow
      Deallocate Cursor_TmpOverUnderRow
      Exec dbo.Sp_Duty_Row @idHavaleh, @AutoId
      /**********************************************************************/    
    Fetch Next From Cursor_TmpHavaleh Into @IdHavaleh , @AutoId, @KQty1 , @PriceForosh,@TakhfifFlag
 End
 Close Cursor_TmpHavaleh
 Deallocate Cursor_TmpHavaleh
