CREATE PROCEDURE  [dbo].[Avg_Price_Kala] AS

  Declare @A_Code              Varchar(5)
  Declare @K_Code              Varchar(15)
  Declare @Mojodi              Money
  Declare @Sender              Varchar(8)
  Declare @Reciver             Varchar(8)
  Declare @K_Qty               Money
  Declare @Price_Kharid        Money
  Declare @Avg_Price_Kharid    Money
  Declare @Temp                Money
  Declare @AutoId              Int
  Declare @Resive_Fa           Real
  Declare @Exit_Fa             Real
  Declare @Kind_Flag           Char
  Declare @Radif               Int
  
  Declare Anbar_Cursor Cursor local For
  Select K_Code From Anbar 
  Group By K_Code
  Open Anbar_Cursor
  Set @temp = 0
  Fetch Next From Anbar_Cursor Into @K_Code

  while @@FETCH_STATUS = 0     
  begin
    Set @Mojodi = 0 
    Set @Avg_Price_Kharid = 0 
    ----------------------------------------------------------------------------------------------------
    Declare Havaleh_Cursor Cursor local For
    Select AutoId, Sender, Reciver, K_Code, K_Qty1, Price_Kharid, Kind_Flag,
    Case  
       when kind_flag='2' then 1
       when kind_flag='3' then 2    
       when kind_flag='6' then 3    
       when kind_flag='8' then 4    
       when kind_flag='0' then 5    
       when kind_flag='1' then 6    
       when kind_flag='4' then 7    
       when kind_flag='5' then 8    
       when kind_flag='7' then 9    
       when kind_flag='9' then 10   
    End as RadiFFactor  
    From Havaleh INNER JOIN
    headH ON Havaleh.Id_Havaleh = headH.Id_Havaleh
    Where K_Code = @K_Code    
    Order By headH.Tarikh_faktor , RadifFactor, Havaleh.AutoId

    Open Havaleh_Cursor
    Fetch Next From  Havaleh_Cursor Into @AutoId, @Sender, @Reciver, @K_Code, @K_Qty, @Price_Kharid, @Kind_Flag, @Radif
    While @@FETCH_STATUS = 0     
    Begin
      IF  Len(@Reciver) = 3    
      Begin
        Select @Mojodi = Avg_Price_Forosh ,@Avg_Price_Kharid = Avg_Price_Omdeh
        From Anbar Where K_Code = @K_Code And A_Code = @Reciver
      
        IF (@Kind_Flag = '3') Or (@Kind_Flag = '2') Or (@Kind_Flag = '1' ) 
        Begin
          IF @Kind_Flag = '1' And Len(@Sender) = 3
            Select @Price_Kharid  = Avg_Price_Omdeh  From  Anbar Where K_Code = @K_Code And A_Code = @Sender
        
          IF @Mojodi < 0
            Set  @Temp = @Price_Kharid
            
          Else IF (@K_Qty + @Mojodi) >= 0 
            Set @Temp = ((@K_Qty * @Price_Kharid) + (@Mojodi * @Avg_Price_Kharid))/(@K_Qty + @Mojodi)  
          Else
            Set @Temp = 0
        
          IF @Kind_Flag = '1' And Len(@Sender) <> 3
            Set @Temp=@Price_Kharid             
        End
        Else
          Set @Temp = @Avg_Price_Kharid
           
          IF (@Kind_Flag in (6,8,0) And @Temp = 0)
            Set @Temp = @Price_Kharid 
           
          IF (@Mojodi + @K_Qty) < 0 
            Set @Temp = 0
      
    
          Set @Temp=Round(@Temp,3)

          IF @Kind_Flag = '1' and Len(@Sender) = 3
            Update Havaleh Set  Tot_Omdeh = @Temp Where AutoId = @AutoId
          Else 
            Update Havaleh Set  Tot_Omdeh = @Temp, Avg_Price = @Temp Where AutoId = @AutoId
        
          Set @Mojodi = @Mojodi + @K_Qty
          Set @Avg_Price_Kharid = @Temp
        
          Update  Anbar Set Avg_Price_Forosh =  @Mojodi, Avg_Price_Omdeh = @Temp
          Where K_Code = @K_Code And A_Code = @Reciver
      End
    
      IF Len(@Sender) = 3
      Begin
        Select @Mojodi = Avg_Price_Forosh , @Avg_Price_Kharid = Avg_Price_Omdeh
        From Anbar Where K_Code = @K_Code And A_Code = @Sender
      
        IF (@Mojodi  - @K_Qty) < 0 
          Set @Avg_Price_Kharid = 0
      
        Set @Avg_Price_Kharid=ROUND(@Avg_Price_Kharid,3)
            
        Update Havaleh Set Avg_Price = @Avg_Price_Kharid Where AutoId = @AutoId
         
        Set @Mojodi = @Mojodi  - @K_Qty
      
        Update  Anbar Set Avg_Price_Forosh =  @Mojodi, Avg_Price_Omdeh = @Avg_Price_Kharid
        Where K_Code = @K_Code And A_Code = @Sender
      End
      Fetch Next From Havaleh_Cursor Into @AutoId, @Sender, @Reciver, @K_Code, @K_Qty, @Price_Kharid, @Kind_Flag,@Radif
    End
    Close Havaleh_Cursor
    Deallocate Havaleh_Cursor
    Update  Anbar Set Avg_Price_Kharid = @Avg_Price_Kharid Where K_Code = @K_Code And A_Code = @A_Code
    -------------------------------------------------------------------------------------------------------------
    Fetch Next From  Anbar_Cursor Into @K_Code
  end

  Close Anbar_Cursor
  Deallocate Anbar_Cursor
