-- msg_num=0 موردي براي صدور سند تعريف نشده است
--اين پروسيجر با گرفتن نوع عمل حسابداري و کد شخص بدهکار و کد شخص بستانکار -شرح مورد نظر در سند و همچنين ارتباط با  

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          BigInt
  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)

  Begin Tran C1 

    Select @TypeFactor = Kind_Flag, @UserId = User_Id, @StoreCode = Store_Code From HeadH Where Id_Havaleh = @Id
    Select @TypeSanad = Type From Auto_Code
    IF (@TypeSanad Is Null) Or (@TypeSanad = '')
      Set @TypeSanad = '0'

    -- ايجاد يک فچ روي رکوردهائي که در جدول اکت با کد اکت ورودي يکسان است
    
    Select @Nu_Sanad = Sanad From HeadH Where Id_Havaleh = @Id
    IF (@Nu_Sanad = 0  or  @Nu_Sanad Is  Null)
	  Select @Nu_Sanad = Id_Sanad From HeadH Inner Join Sanad
	  On Sanad.Nu_Havaleh = HeadH.Id_Havaleh
	  Where Id_Havaleh = @Id And (Sanad.Nu_Havaleh Is Not Null And Nu_Over_Under Is Null)
	  Group By Id_Sanad
	  
    IF (@Nu_Sanad Is Null) Or (@Nu_Sanad = '')
		Select @NU_Sanad=Id_Sanad,@Auto=Auto,@Fiexd=Fiexd From dbo.heads 
		Where (DateDiff(day,Tarikh_User,getdate() ) =0 ) And (Auto=1)

    IF (@Nu_Sanad Is not Null) And (@Auto='0') and (@Nu_Sanad  > 0) 
    Begin
 	  Update Dbo.Heads Set Auto='1' Where Id_Sanad =  @Nu_Sanad
	  IF @@Error > 0  
	  Begin
	   ROLLBACK TRANSACTION C1
	   Set  @msg_num=-5
	   Return   @msg_num  --@@Error
	  End
	End  
		
	IF (@NU_Sanad Is Null) Or (@NU_Sanad=0)  -- اگر قبلا با اين شماره در سندها وجود نداشته باشد
	Begin 
	  Insert Into Dbo.Heads(Auto,Fiexd,Nu_Manual,Store_Code)   
	  Values(1,0,dbo.samsi_Date(GetDate()), @StoreCode)
	  Select @Nu_Sanad=Scope_Identity() --   به دست آوردن شماره آخرين سند 
	  IF @@Error > 0  
	  Begin
	    ROLLBACK TRANSACTION C1
	    Set  @msg_num=-5
	    RETURN   @msg_num  --@@Error
	  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
               IF (Select Count(*) From Sanad Where Acc_Code = @Acc_Code And Bedeh >0 And Id_Sanad = @Nu_Sanad  
                   And Nu_Havaleh <> 0 And @Userid = User_Id And FlagFactor = @Act
                   And Nu_Over_Under Is Null And Nu_Ghest Is Null) >0
                 Update Sanad Set Bedeh = Bedeh + @Price Where Acc_Code = @Acc_Code  And Bedeh >0 and Id_Sanad = @Nu_Sanad 
                   And Nu_Havaleh <> 0 And @Userid = User_Id And FlagFactor = @Act
                   And Nu_Over_Under Is Null And Nu_Ghest Is Null
               Else
               Begin
                 Insert Into  Dbo.Sanad (Id_Sanad   ,  Acc_Code ,     Note , Bedeh    , Auto_Create  , Edit_Flag ,Type  ,Nu_Havaleh ,Nu_Over_Under , FlagFactor)  
                 Values                 (@Nu_Sanad  , @Acc_Code , @ActNote + ' ' + 'خريداران متفرقه' , @Price    , 1    ,    1      ,@Type  ,@Id ,@Nu_Over_Under, @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)  
                           Values    (@Nu_Sanad , @Acc_Code ,@ActNote + ' '+@PNote  ,  @Price ,1  , 1 ,@Type,@Id,@Nu_Over_Under)
               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)  
             Values       (@Nu_Sanad , @Acc_Code ,@ActNote + ' '+@PNote  ,  @Price ,1  , 1 ,@Type,@Id,@Nu_Over_Under)
             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)  
           Values       (@Nu_Sanad , @Acc_Code ,@ActNote + ' '+@PNote  ,  @Price ,1  , 1 ,@Type,@Id,@Nu_Over_Under)
           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
               IF (Select Count(*) From Sanad Where Acc_Code = @Acc_Code And Bestan >0
                  And Id_Sanad = @Nu_Sanad And Nu_Havaleh <> 0 And @Userid = User_Id 
                  And FlagFactor = @Act And Nu_Over_Under Is Null And Nu_Ghest Is Null)>0
                 Update Sanad Set Bestan = Bestan + @Price Where Acc_Code = @Acc_Code  And Bestan >0 
                   And Id_Sanad = @Nu_Sanad And Nu_Havaleh <> 0
                   And @Userid = User_Id And FlagFactor = @Act
                   And Nu_Over_Under Is Null And Nu_Ghest Is Null
               Else
               Begin
                 Insert Into  Dbo.Sanad (Id_Sanad   , Acc_Code  ,      Note , Bestan  , Auto_Create  , Edit_Flag ,Type  ,Nu_Havaleh ,Nu_Over_Under , FlagFactor)  
                 Values                 (@Nu_Sanad  , @Acc_Code ,  @ActNote + ' ' + 'خريداران متفرقه' , @Price  ,        1     ,    1      ,@Type ,@Id        ,@Nu_Over_Under, @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 )  
               Values   (@Nu_Sanad  , @Acc_Code  , @ActNote+' ' +@PNote  ,    @Price   , 1 , 1 , @Type,@Id,@Nu_Over_Under)
               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 )  
             Values   (@Nu_Sanad  , @Acc_Code  , @ActNote+' ' +@PNote  ,    @Price   , 1 , 1 , @Type,@Id,@Nu_Over_Under)
             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 )  
                         Values   (@Nu_Sanad  , @Acc_Code  , @ActNote+' ' +@PNote ,    @Price   , 1 , 1 , @Type,@Id,@Nu_Over_Under)
           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
