CREATE PROCEDURE [dbo].[Create_Taraz]  @AzTarikh Datetime, @TaTarikh Datetime, @AzCode Varchar(20), @TaCode Varchar(20), @ComputerName Varchar (20), @NuAftatah Float, @Number1 Float , @Number2 Float
AS

delete from Taraz  where ComputerName = @ComputerName

declare @Acc_Code   Varchar(20)
declare @Acc_Name  Nvarchar(30)
declare @Bedeh         Money
declare @Bestan        Money
declare @fristPeriodBedeh     Money
declare @fristPeriodBestan    Money
Declare @Parent_Code     int
Declare @FlagSelect Char(1)
-----------------------------------------------------------------------------------------------------------------------------------------------------
--  تراز آزمايشي
If (@NuAftatah = 0 ) 
Begin
  IF (@AzCode <> ''  and  @TaCode <> '')
  Begin
      declare tmpTaraz_Cursor cursor local for
      select  Sanad.Acc_Code, Account.Acc_Name, SUM (isnull(Sanad.Bedeh,0)) sumBedeh, 
      SUM (isnull(Sanad.bestan,0)) sumBestan,Account.Parent_Code
      from    HeadS inner JOIN Sanad on HeadS.ID_Sanad = Sanad.ID_Sanad inner join Account  on 
                       Sanad.Acc_Code = Account.Acc_Code
      where  (Convert(Varchar(10), HeadS.Tarikh_User, 111) >=  @AzTarikh)  
      AND    (Convert(Varchar(10), HeadS.Tarikh_User, 111) <= @TaTarikh)  
      And  (Sanad.Acc_Code >=  @AzCode)  AND (Sanad.Acc_Code <= @TaCode)
      And Nu_Manual>=@Number1 And Nu_Manual<=@Number2
      group by Sanad.Acc_Code, Account.Acc_Name,Account.Parent_Code
      Set @FlagSelect = '1'
  End
  Else
  Begin
     declare tmpTaraz_Cursor cursor local for
     Select  Sanad.Acc_Code, Account.Acc_Name, SUM (isnull(Sanad.Bedeh,0)) sumBedeh, SUM (isnull(Sanad.bestan,0)) sumBestan,Account.Parent_Code
     From    HeadS inner JOIN Sanad on HeadS.ID_Sanad = Sanad.ID_Sanad inner join Account  on 
                       Sanad.Acc_Code = Account.Acc_Code
      where  (Convert(Varchar(10), HeadS.Tarikh_User, 111) >=  @AzTarikh)  
      AND    (Convert(Varchar(10), HeadS.Tarikh_User, 111) <= @TaTarikh)  
      And Nu_Manual>=@Number1 And Nu_Manual<=@Number2
      Group by Sanad.Acc_Code, Account.Acc_Name,Account.Parent_Code
      Set @FlagSelect = '2'
   End
   open    tmpTaraz_Cursor
 
   fetch next from  tmpTaraz_Cursor into @Acc_Code, @Acc_Name, @bedeh, @bestan,@Parent_Code

    while @@FETCH_STATUS = 0     
    begin
	insert into  Taraz (Acc_Name,     Acc_Code ,    bedeh,      Bestan, ComputerName, Parent_Code)  
        		values (@Acc_Name, @Acc_Code, isnull(@bedeh,0), isnull(@Bestan,0), @ComputerName, @Parent_Code)	
	
  	fetch next from  tmpTaraz_Cursor into @Acc_Code,@Acc_Name,  @bedeh, @bestan,@Parent_Code
     end

    Close tmpTaraz_Cursor
    Deallocate tmpTaraz_Cursor

    Exec Create_Taraz_Over  @AzTarikh, @TaTarikh , @AzCode, @TaCode , @ComputerName , @FlagSelect, 0   -- ساخت پرنت كدها
End -- پايان تراز آزمايشي 
---------------------------------------------------
Else -- تراز دوره اي
  If (@NuAftatah <> 0) 
  Begin
    if (@AzCode <> ''  and  @TaCode <> '')
    Begin
      declare tmpTaraz_Cursor cursor local for
      select  Sanad.Acc_Code, Account.Acc_Name, SUM (isnull(Sanad.Bedeh,0)) sumBedeh, SUM (isnull(Sanad.bestan,0)) sumBestan, Account.Parent_Code
       from    HeadS inner JOIN Sanad on HeadS.ID_Sanad = Sanad.ID_Sanad inner join Account  on 
                       Sanad.Acc_Code = Account.Acc_Code
      where  (Convert(Varchar(10), HeadS.Tarikh_User, 111) >=  @AzTarikh)  
      AND    (Convert(Varchar(10), HeadS.Tarikh_User, 111) <= @TaTarikh)  
      And  (Sanad.Acc_Code >=  @AzCode)  AND (Sanad.Acc_Code <= @TaCode) And (HeadS.Nu_Manual <> @NuAftatah)
      group by Sanad.Acc_Code, Account.Acc_Name, Account.Parent_Code
      Set @FlagSelect = '3'
   End
   else
   Begin
      declare tmpTaraz_Cursor cursor local for
      select  Sanad.Acc_Code, Account.Acc_Name, SUM (isnull(Sanad.Bedeh,0)) sumBedeh, SUM (isnull(Sanad.bestan,0)) sumBestan, Account.Parent_Code
      from    HeadS inner JOIN Sanad on HeadS.ID_Sanad = Sanad.ID_Sanad inner join Account  on 
                       Sanad.Acc_Code = Account.Acc_Code
       where  (Convert(Varchar(10), HeadS.Tarikh_User, 111) >=  @AzTarikh)  
       AND    (Convert(Varchar(10), HeadS.Tarikh_User, 111) <= @TaTarikh)  And (HeadS.Nu_Manual <> @NuAftatah)
       group by Sanad.Acc_Code, Account.Acc_Name, Account.Parent_Code
      Set @FlagSelect = '4'
   End

    open    tmpTaraz_Cursor
  
     fetch next from  tmpTaraz_Cursor into @Acc_Code, @Acc_Name, @bedeh, @bestan, @Parent_Code

     while @@FETCH_STATUS = 0     

     begin
	insert into  Taraz (Acc_Name,     Acc_Code ,    bedeh,      Bestan, ComputerName, Parent_Code)  
        		values (@Acc_Name, @Acc_Code, isnull(@bedeh,0), isnull(@Bestan,0), @ComputerName, @Parent_Code)	
	
  	fetch next from  tmpTaraz_Cursor into @Acc_Code,@Acc_Name,  @bedeh, @bestan, @Parent_Code
     end
    close tmpTaraz_Cursor
    deallocate tmpTaraz_Cursor

--------سند افتتاحيه
    declare tmpTaraz_Cursor2 cursor local for
    select  Sanad.Acc_Code, Account.Acc_Name, SUM (isnull(Sanad.Bedeh,0)),  SUM (isnull(Sanad.bestan,0)) 
    from    HeadS inner JOIN Sanad on HeadS.ID_Sanad = Sanad.ID_Sanad inner join Account  on 
                       Sanad.Acc_Code = Account.Acc_Code
    where  (Heads.Nu_Manual = @NuAftatah) 
    group by Sanad.Acc_Code, Account.Acc_Name

    open    tmpTaraz_Cursor2
 
    fetch next from  tmpTaraz_Cursor2 into @Acc_Code, @Acc_Name, @bedeh, @bestan
 
    while @@FETCH_STATUS = 0     
     begin
        if  (select Count(*) From Taraz Where (Acc_Code=@Acc_Code) and (ComputerName=@ComputerName) ) =0 
	 insert into Taraz (acc_name, Acc_Code, fristPeriodBedeh, fristPeriodBestan, ComputerName) 
        		values (@Acc_Name, @Acc_Code, Isnull( @bedeh,0), isnull( @Bestan,0), @ComputerName)	
         else 
            Update  Taraz set fristPeriodBedeh=fristPeriodBedeh+Isnull( @bedeh,0), fristPeriodBestan=fristPeriodBestan+isnull( @Bestan,0), ComputerName=@ComputerName
             Where Acc_Code=@Acc_Code and ComputerName=@ComputerName

         fetch next from  tmpTaraz_Cursor2 into @Acc_Code,@Acc_Name,  @bedeh, @bestan
     end
     Exec Create_Taraz_Over  @AzTarikh, @TaTarikh , @AzCode, @TaCode , @ComputerName , @FlagSelect, @NuAftatah   -- ساخت پرنت كدها
     close tmpTaraz_Cursor2
     deallocate tmpTaraz_Cursor2
  End -- پايان تراز دوره اي
