CREATE  Procedure  [dbo].[Report_CycleAnbar]  @A_Code Char(3), @BeforeDate smalldatetime, @StrWhere Varchar(50), @StrField Varchar(100), @StrGroup Varchar(25) As
  Declare @StrCommand NVarchar(4000)
  Declare @Parameter Nvarchar(500)
  Set @StrCommand = 
                   ' Declare @KCode  Varchar(15)
 		           Declare Cursor_Insert Cursor For
                   SELECT Havaleh.K_Code 
                   FROM headH WITH (NOLOCK) INNER JOIN  Havaleh WITH (NOLOCK) ON headH.Id_Havaleh =Havaleh.Id_Havaleh 
                   Where ((Havaleh.Sender =@ACode) or (Havaleh.Reciver = @ACode)) 
                   And (HeadH.Tarikh_Faktor <  @Date1)   
                   Group By Havaleh.K_Code 
                   Open Cursor_Insert
                   Fetch Next From Cursor_Insert Into @KCode
                   While @@Fetch_Status = 0 
                   Begin
                    Insert into ##SoodzianOld (K_Code, Price, Radif)
                    Select K_code,Price,                             
                    case
    		       when kind_flag=''2'' then 1    
                       when kind_flag=''3'' then 2     
    		       when kind_flag=''1'' then 3   
    		       when kind_flag=''6'' then 4   
                       when kind_flag=''8'' then 5     
                       when kind_flag=''0'' 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 Radif
                     From (
                     SELECT Havaleh.K_Code,Tarikh_Faktor Tarikh,HeadH.Id_Havaleh,Kind_Flag,' + @StrField + 
                     ' FROM headH WITH (NOLOCK) INNER JOIN  Havaleh WITH (NOLOCK) ON headH.Id_Havaleh =Havaleh.Id_Havaleh
                     WHERE (Havaleh.K_Code= @KCode)
                     And((Havaleh.Sender =@ACode) or (Havaleh.Reciver = @ACode))
                     And (HeadH.Tarikh_Faktor <  @Date1)'
                    IF @StrWhere <> ''''''
                      Set @StrCommand = @StrCommand  + @StrWhere 
                    Set @StrCommand = @StrCommand + 
                    ' group by Havaleh.K_Code,Tarikh_Faktor ,HeadH.Id_Havaleh, 
                     Kind_Flag, Sender ,Reciver, ' + @StrGroup + 
                    ') Table1  
                    Order by Tarikh ,Radif 

                    Insert Into ##Soodzian (A_Code, K_Code, CalcPrice)
                    Select Top 1 @ACode, ##SoodzianOld.K_code , Round(##SoodzianOld.Price *  T2.Mandeh,0) SumKol  From ##SoodzianOld
                    Inner Join 
                    (
                       Select K_Code, IsNull(Sum(Vorod),0) - IsNull(Sum(Sodor),0) Mandeh From (
                       Select K_Code , 
                       Case
                         When Sender =@ACode Then SUM(dbo.Havaleh.K_Qty1)
                         End As Sodor,
                       Case
                         When Reciver = @ACode Then  SUM(dbo.Havaleh.K_Qty1) 
                       End As Vorod
                       FROM headH WITH (NOLOCK) INNER JOIN Havaleh WITH (NOLOCK) ON headH.Id_Havaleh = Havaleh.Id_Havaleh 
                       WHERE (Havaleh.K_Code= @KCode) 
                       And((Havaleh.Sender =@ACode) or (Havaleh.Reciver = @ACode)) 
                       And (HeadH.Tarikh_Faktor <  @Date1)   
                       group by Sender , K_Code, Reciver
                     ) As T1 Group By K_Code
                    ) As T2 On T2.K_Code = ##SoodzianOld.K_Code
                    Where T2.Mandeh > 0 
                  Order By ##SoodzianOld.AutoId Desc
                  Truncate Table ##SoodzianOld
                Fetch Next From Cursor_Insert Into @KCode
              End
              Close Cursor_Insert
              Deallocate Cursor_Insert'

  Set @Parameter = N'@ACode Char(3), @Date1 smalldatetime'
  Exec Sp_ExecuteSql  @StrCommand, @Parameter, @ACode = @A_Code, @Date1 = @BeforeDate
