CREATE PROCEDURE [dbo].[CreateSanadStandard]  @Id_Sanad Smallint
 AS

---------------------------------------------------------------
Declare @Acc_Code Varchar(20)
Declare @Note NVarchar(120)
Declare @Acc_Name Nvarchar(130)
Declare @type Char(1)
Declare @Bedeh Money
Declare @Bestan Money
Declare @Acc_Level Smallint
Declare @Radif Smallint

set @Radif = 1

DECLARE Standard_Cursor CURSOR LOCAL FOR

SELECT Id_Sanad,acc.Acc_Code,SUM(sa.Bedeh) Bedeh, SUM(sa.Bestan) Bestan, Type, acc_name, acc_level 
FROM Sanad sa inner join account acc on sa.acc_code = acc.acc_code
WHERE Id_sanad = @Id_Sanad  and has_child = 0 
GROUP By Id_Sanad,acc.Acc_Code,Type, Acc_name, Acc_level 
ORDER by acc.acc_code

OPEN Standard_Cursor
  
FETCH NEXT FROM Standard_Cursor INTO @Id_Sanad, @Acc_Code, @Bedeh, @Bestan, @Type, @Acc_Name, @Acc_Level

WHILE @@FETCH_STATUS = 0     
BEGIN 
  INSERT INTO SanadStandard (Id_Sanad,  Acc_Code,  Note,      Bedeh,  Bestan,  Type,  Acc_Level, flagInsert, Radif)  
                     VALUES (@Id_Sanad, @Acc_Code, @Acc_Name, @Bedeh, @bestan, @Type, @Acc_Level, 0, @Radif)

  set @Radif = @Radif + 2
  FETCH NEXT FROM  Standard_Cursor INTO @Id_Sanad, @Acc_Code, @bedeh, @bestan, @Type, @Acc_Name, @Acc_Level
End

Close Standard_Cursor
Deallocate Standard_Cursor

----------------------------------------------------------------------------------------------------------
DECLARE Standard_Cursor CURSOR LOCAL FOR

SELECT Id_Sanad,acc.Acc_Code, sa.Bedeh, sa.Bestan, Type, Note, acc_level 
FROM Sanad sa inner join account acc on sa.acc_code = acc.acc_code
WHERE Id_sanad = @Id_Sanad  and has_child = 0 
ORDER by acc.acc_code, sa.bedeh, sa.bestan

set @Radif = 2

OPEN Standard_Cursor
  
FETCH NEXT FROM Standard_Cursor INTO @Id_Sanad, @Acc_Code, @Bedeh, @Bestan, @Type, @Acc_Name, @Acc_Level

WHILE @@FETCH_STATUS = 0     
BEGIN 
  INSERT INTO SanadStandard (Id_Sanad,  Acc_Code,  Note,      Bedeh,  Bestan,  Type,  Acc_Level, flagInsert, Radif)  
                     VALUES (@Id_Sanad, @Acc_Code, @Acc_Name + '   ', @Bedeh, @bestan, @Type, @Acc_Level, 1, @Radif)

  SET @Radif = @Radif + 2
  FETCH NEXT FROM  Standard_Cursor INTO @Id_Sanad, @Acc_Code, @bedeh, @bestan, @Type, @Acc_Name, @Acc_Level
End

Close Standard_Cursor
Deallocate Standard_Cursor
