CREATE PROCEDURE [dbo].[Create_Sanad_Auto] @msg_num BigInt  OUTPUT, @Act  Smallint ,  @Code  VarChar(20)  ,@Price Money,@Id BigInt,@PNote NvarChar(100),@Nu_Over_Under Bigint
AS

  Declare  @Acc_Code          VarChar(20)
  Declare  @Acc_Code_Equal    VarChar(20)
  Declare  @Parent_Code       VarChar(20)
  Declare  @Parent_Code_Equal VarChar(20)
  Declare  @Type              Bit  
  Declare  @ActNote           NvarChar(100)
  Declare  @Nu_Sanad          Int
  Declare  @Auto              Char(1)  
  Declare  @Flag              Char(1)
  Declare  @UserId            Smallint
  Declare  @Fiexd             Char(1)  
  Declare  @IdSanad           Int
  Declare  @TypeFactor        Char(1)
  Declare  @TypeSanad         Char(1)
  Declare  @StoreCode         Varchar(3)
  Declare  @Autoid            BIGINT
  DECLARE  @TotAfzayandeh      Money

  Begin Tran C1 

    Select @TypeFactor = Kind_Flag, @UserId = User_Id, @StoreCode = Store_Code, @TotAfzayandeh = ISNULL(Tot_Afzayandeh,0) From HeadH Where Id_Havaleh = @Id
    Select @TypeSanad = Type From Auto_Code
    if @PNote = 'Only_Ou'
    begin
      Select @UserId= User_Id From Headc Where Id_Cheqe = @Id
      Set @PNote = ''
    end
    
    if (@UserId is Null Or @UserId = '')
      Set @userId = Manager.dbo.SessionUser(@@spid,0)
    
    IF (@TypeSanad Is Null) Or (@TypeSanad = '')
      Set @TypeSanad = '0'
     

    Select @Nu_Sanad = Sanad From HeadH Where Id_Havaleh = @id
    IF @TypeFactor in(7,8)
    BEGIN
      IF @TotAfzayandeh = 0 
        SET @Nu_Sanad = 0
    END

    IF ((@Nu_Sanad Is Null) Or (@Nu_Sanad = '') Or (@Nu_Sanad = 0))
		Select @NU_Sanad=Id_Sanad  From dbo.heads 
		Where CONVERT(Varchar(10), Tarikh_User, 111) = CONVERT(Varchar(10), GETDATE(), 111)
		And Auto=1
     		
	
	IF (@NU_Sanad Is Null) Or (@NU_Sanad=0) 
	Begin 
	  Insert Into Dbo.Heads(Auto,Fiexd,Nu_Manual, Store_Code, User_Id)   Values(1,0,dbo.samsi_Date(GetDate()) , @StoreCode, @UserId)
	  Select @Nu_Sanad=SCOPE_IDENTITY() 
	  IF @@Error > 0  
	  Begin
	    ROLLBACK TRANSACTION C1
	    Set    @msg_num=-5
	    RETURN @msg_num  
	  End
	End  
	
    Select @msg_Num=@Nu_Sanad
    Update headH Set Sanad = @msg_num Where Id_Havaleh = @id
	   
	
    DECLARE Cursor_Act CURSOR FOR
    SELECT  Type , Note_In_Sanad , Parent_Code,Parent_Code_Equal FROM  dbo.Act Where    (Act_Code=@Act )

    OPEN Cursor_Act

    FETCH NEXT FROM Cursor_Act INTO  @Type,@ActNote , @Parent_Code,@Parent_Code_Equal

    WHILE @@FETCH_STATUS = 0
    BEGIN 
      Set @Acc_Code      ='0'
      Set @Acc_Code_Equal='0' 
      ----------------------------------------------------------------------------------------------------------------------------------------
      SELECT @Acc_Code=dbo.Acc_link.Acc_Code FROM dbo. Acc_link
      WHERE (Acc_link.Code = @Code) And (Acc_Parent_Code=@Parent_Code )
         
      SELECT @Acc_Code_Equal=dbo.Acc_link.Acc_Code FROM dbo. Acc_link
      WHERE  (Acc_link.Code = @Code) And (Acc_Parent_Code=@Parent_Code_Equal )
    
      IF (@Acc_Code='0' Or @Acc_Code Is NULL) And (@Acc_Code_Equal='0' Or @Acc_Code_Equal Is NULL)  
      Begin
        IF (Select Has_Child from Dbo.Account where Acc_Code=@Parent_Code)='0' 
          Set @Acc_Code=@Parent_Code -- اگر مستقيما کد حسابداري وجود داشت
        Else print ' code account not exist'
      End
      Else      
        If (@Acc_Code='0') And (@Acc_Code_Equal<>'0')                 
          Set @Acc_Code=@Acc_Code_Equal -- If Code Equql exist
        Else
          If (@Acc_Code<>'0') And (@Acc_Code_Equal<>'0')                    
            If ((Select Sum(Bedeh) from Dbo.Account Where Acc_Code=@Acc_Code_Equal) > 0) Or ((Select Sum(Bestan) from Dbo.Account Where Acc_Code=@Acc_Code_Equal) > 0 )         
              Set @Acc_Code=@Acc_Code_Equal -- If Code Equql exist
       ----------------------------------------------------------------------------------------------------------------------------------------
       IF @Type = 0   
       Begin
         IF @TypeFactor in ('7', '8')
         Begin
           IF @Code = '10000'           
           Begin 
             IF @TypeSanad = '1'
             Begin
               Select @Autoid = Autoid From Sanad Where Type = @Type 
                                                  And Acc_Code = @Acc_Code
                                                  And FlagFactor = @Act 
                                                  And User_ID = @UserId
                                                  And Id_Sanad = @Nu_Sanad
               IF @Autoid is not null                                                   
                 Update Sanad Set Bedeh = Bedeh + @Price Where AutoId = @Autoid
               Else
               Begin
                 Insert Into  Dbo.Sanad (Id_Sanad   ,  Acc_Code ,     Note , Bedeh    , Auto_Create  , Edit_Flag ,Type  ,Nu_Havaleh ,Nu_Over_Under , FlagFactor, User_ID, Nu_Cooperative)  
                 Values                 (@Nu_Sanad  , @Acc_Code , @ActNote + ' ' + 'خريداران متفرقه' , @Price    , 1    ,    1      ,@Type  ,@Id ,@Nu_Over_Under, @Act, @UserId, @Act)
                 IF @@Error > 0  
                 Begin
                   CLOSE Cursor_Act
                   DEALLOCATE Cursor_Act
                   Set  @msg_num=-5  --msg-num = -5 خطاي سيستم
                   ROLLBACK TRANSACTION C1
       	           RETURN   @msg_num  --@@Error
                 End
               End
             End --IF @TypeSanad = '1'
             Else
             Begin
               Insert Into Dbo.Sanad (Id_Sanad  , Acc_Code  , Note ,  Bedeh  , Auto_Create  , Edit_Flag ,Type    ,Nu_Havaleh ,Nu_Over_Under, User_ID, Nu_Cooperative)  
                           Values    (@Nu_Sanad , @Acc_Code ,@ActNote + ' '+@PNote  ,  @Price ,1  , 1 ,@Type,@Id,@Nu_Over_Under, @UserId, @Act)
               If @@Error > 0  
               Begin
                 CLOSE Cursor_Act
                 DEALLOCATE Cursor_Act
                 Set  @msg_num=-5  --msg-num = -5 خطاي سيستم
                 ROLLBACK TRANSACTION C1
       	         RETURN   @msg_num  --@@Error
               End
               Select @msg_Num=SCOPE_IDENTITY() --   به دست آورن اخرين شماره خوکار رديف سند
             End
           End --IF @Code = '10000'           
           Else
           Begin
             Insert Into Dbo.Sanad (Id_Sanad  , Acc_Code  , Note ,  Bedeh  , Auto_Create  , Edit_Flag ,Type    ,Nu_Havaleh ,Nu_Over_Under, User_ID, Nu_Cooperative)  
             Values       (@Nu_Sanad , @Acc_Code ,@ActNote + ' '+@PNote  ,  @Price ,1  , 1 ,@Type,@Id,@Nu_Over_Under, @UserId, @Act)
             If @@Error > 0  
             Begin
               CLOSE Cursor_Act
               DEALLOCATE Cursor_Act
               Set  @msg_num=-5  --msg-num = -5 خطاي سيستم
               ROLLBACK TRANSACTION C1
       	       RETURN   @msg_num  --@@Error
             End
             Select @msg_Num=SCOPE_IDENTITY() --   به دست آورن اخرين شماره خودکار رديف سند
           End 
         End --IF @TypeFactor in ('7', '8')
         Else
         Begin
           Insert Into Dbo.Sanad (Id_Sanad  , Acc_Code  , Note ,  Bedeh  , Auto_Create  , Edit_Flag ,Type    ,Nu_Havaleh ,Nu_Over_Under, User_ID, Nu_Cooperative)  
           Values       (@Nu_Sanad , @Acc_Code ,@ActNote + ' '+@PNote  ,  @Price ,1  , 1 ,@Type,@Id,@Nu_Over_Under, @UserId, @Act)
           IF @@Error > 0  
           Begin
             CLOSE Cursor_Act
             DEALLOCATE Cursor_Act
             Set  @msg_num=-5  --msg-num = -5 خطاي سيستم
             ROLLBACK TRANSACTION C1
             RETURN   @msg_num  --@@Error
           End
           Select @msg_Num=SCOPE_IDENTITY() --   به دست آورن اخرين شماره خوکار رديف سند
         End
       End --If @Type = 0   
       ----------------------------------------------------------------------------------------------------------------------------------------
       Else --(2)
       Begin
         IF @TypeFactor in ('7', '8')
         Begin
           IF @Code = '10000'           
           Begin 
             IF @TypeSanad = '1'
             Begin
               Select @Autoid = Autoid From Sanad Where Type = @Type 
												  And Acc_Code = @Acc_Code
                                                  And FlagFactor = @Act 
                                                  And User_ID = @UserId
                                                  And Id_Sanad = @Nu_Sanad
               IF @Autoid is not null                                                   
                 Update Sanad Set Bestan = Bestan + @Price Where AutoId = @Autoid
             
               Else
               Begin
                 Insert Into  Dbo.Sanad (Id_Sanad   , Acc_Code  ,      Note , Bestan  , Auto_Create  , Edit_Flag ,Type  ,Nu_Havaleh ,Nu_Over_Under , FlagFactor,User_ID, Nu_Cooperative)  
                 Values                 (@Nu_Sanad  , @Acc_Code ,  @ActNote + ' ' + 'خريداران متفرقه' , @Price  ,        1     ,    1      ,@Type ,@Id        ,@Nu_Over_Under, @Act,@UserId, @Act)
                 IF @@Error > 0  
                 Begin
                   ROLLBACK TRANSACTION C1
                   Set  @msg_num=-5
                   RETURN   @msg_num  --@@Error
                 End
               End
             End --IF @TypeSanad = '1'
             Else 
             Begin
               Insert Into  Dbo.Sanad (Id_Sanad   , Acc_Code , Note      ,   Bestan        , Auto_Create  , Edit_Flag , Type    ,Nu_Havaleh,Nu_Over_Under, User_ID, Nu_Cooperative )  
               Values   (@Nu_Sanad  , @Acc_Code  , @ActNote+' ' +@PNote  ,    @Price   , 1 , 1 , @Type,@Id,@Nu_Over_Under, @UserId, @Act)
               If @@Error > 0  
               Begin
                 CLOSE Cursor_Act
                 DEALLOCATE Cursor_Act
                 Set  @msg_num=-5  --msg-num = -5 خطاي سيستم
                 ROLLBACK TRANSACTION C1
                 RETURN   @msg_num  --@@Error
               End
               Select @msg_Num=SCOPE_IDENTITY() --   به دست آورن اخرين شماره خوکار رديف سند
             End
           End --IF @Code = '10000
           Else 
           Begin
             Insert Into  Dbo.Sanad (Id_Sanad   , Acc_Code , Note      ,   Bestan        , Auto_Create  , Edit_Flag , Type    ,Nu_Havaleh,Nu_Over_Under, User_ID, Nu_Cooperative )  
             Values   (@Nu_Sanad  , @Acc_Code  , @ActNote+' ' +@PNote  ,    @Price   , 1 , 1 , @Type,@Id,@Nu_Over_Under, @UserId, @Act)
             If @@Error > 0  
             Begin
               CLOSE Cursor_Act
               DEALLOCATE Cursor_Act
               Set  @msg_num=-5  --msg-num = -5 خطاي سيستم
               ROLLBACK TRANSACTION C1
               RETURN   @msg_num  --@@Error
             End
             Select @msg_Num=SCOPE_IDENTITY() --   به دست آورن اخرين شماره خوکار رديف سند
           End
         End -- IF @TypeFactor in ('7', '8')
         Else 
         Begin
           Insert Into  Dbo.Sanad (Id_Sanad   , Acc_Code , Note      ,   Bestan        , Auto_Create  , Edit_Flag , Type    ,Nu_Havaleh,Nu_Over_Under, User_ID, Nu_Cooperative )  
                         Values   (@Nu_Sanad  , @Acc_Code  , @ActNote+' ' +@PNote ,    @Price   , 1 , 1 , @Type,@Id,@Nu_Over_Under, @UserId, @Act)
           If @@Error > 0  
           Begin
             CLOSE Cursor_Act
             DEALLOCATE Cursor_Act
             Set  @msg_num=-5  --msg-num = -5 خطاي سيستم
             ROLLBACK TRANSACTION C1
             RETURN   @msg_num  --@@Error
           End
           Select @msg_Num=SCOPE_IDENTITY() --   به دست آورن اخرين شماره خوکار رديف سند
         End
       End --(2)
        ----------------------------------------------------------------------------------------------------------------------------------------
       FETCH NEXT FROM Cursor_Act
       INTO  @Type,@ActNote , @Parent_Code,@Parent_Code_Equal 
    END
  
    CLOSE Cursor_Act
    DEALLOCATE Cursor_Act


    Commit  Tran C1 
    RETURN @msg_Num
