CREATE PROCEDURE  [dbo].[CreateGroupHesab]  @AzTarikh SmalldateTime, @TaTarikh SmalldateTime, @AzCode Varchar(20), @TaCode Varchar(20), @TypeMandeh Smallint, @iValue Bigint, @Control1 Char(1) , @Control2 Char(1)  
AS
Declare @NuManual Float
Declare @UserId Smallint
Declare @Tarikh SmalldateTime
Declare @AccCode Varchar(20)
Declare @GuidSanad Varchar(90)
Declare @Note  Nvarchar(120)
Declare @Tash  Nvarchar(2)
Declare @Mandeh Money
Declare @Bestan Money
Declare @Bedeh Money
Declare @BeforeMandeh Money
Declare @Type Char(1)
Declare @ParentCode bigint

Truncate Table GroupHesab
Declare Cursor_Update Cursor Local For
SELECT Sanad.Acc_code
FROM HeadS INNER JOIN Sanad ON Sanad.Id_Sanad = HeadS.Id_Sanad 
WHERE (Sanad.Acc_Code >=@AzCode) 
And (Sanad.Acc_Code <= @TaCode) 
And (HeadS.Tarikh_User >= @AzTarikh) 
And (HeadS.Tarikh_User<= @TaTarikh) 
And (Sanad.BEDEH <> 0 or Sanad.BESTAN <> 0) 
Group By Sanad.Acc_Code
Open Cursor_Update
Fetch Next From Cursor_Update Into @AccCode 
While @@Fetch_Status = 0 
Begin
  Select @BeforeMandeh = SUM (Sanad.BEDEH) - SUM (Sanad.BESTAN) 
                        FROM HeadS HeadS INNER JOIN Sanad Sanad 
                        ON  Sanad.Id_Sanad = HeadS.Id_Sanad 
                        WHERE Sanad.Acc_Code = @AccCode 
                        And HeadS.Tarikh_User < @AzTarikh
  Set @Note = 'مانده حساب قبلي'
  Set @Type = ''
  Set @Bedeh = 0 
  Set @Bestan = 0
  Set @Tash = ''
  IF @BeforeMandeh > 0 
  Begin
    Set @Type = '0'
    Set @Bedeh = @BeforeMandeh
    Set @Tash = 'بد'
  End
  Else IF @BeforeMandeh < 0 
  Begin
    Set @Type = '1'
    Set @Bestan = @BeforeMandeh
    Set @Tash = 'بس'
  End
  Insert Into GroupHesab (Guid_Sanad,  Acc_Code, NOTE, Bedeh, Bestan, Mandeh, Tashkhis)
              Values  ('---',  @AccCode, @Note, @Bedeh, @Bestan, IsNull(@BeforeMandeh,0), @Tash)
  Select @ParentCode = @@Identity

  Insert Into GroupHesab (Acc_Code, TarikhSanad, NOTE, Bedeh, Bestan, NU_Manual, User_ID, Guid_Sanad, Mandeh, Control, Parent_Code)
  SELECT Sanad.Acc_code,HeadS.Tarikh_User, Sanad.NOTE, Sanad.BEDEH, Sanad.BESTAN, 
  HeadS.Nu_Manual, Sanad.user_id, Sanad.Guid_Sanad, (Sanad.BEDEH - Sanad.BESTAN) , Sanad.Control, @ParentCode
  FROM HeadS INNER JOIN Sanad ON Sanad.Id_Sanad = HeadS.Id_Sanad 
  WHERE (Sanad.Acc_Code = @AccCode) 
And (HeadS.Tarikh_User>= @AzTarikh) 
And (HeadS.Tarikh_User <= @TaTarikh) 
  And (Sanad.BEDEH <> 0 or Sanad.BESTAN <> 0) 
  And (Control = @Control1 Or Control = @Control2) 
  ORDER BY HeadS.Tarikh_User ,HeadS.Nu_Manual, Sanad.Tarikh, 
  Sanad.Nu_Havaleh Desc, Nu_Over_Under, Nu_Vajeh 
  -------------------------------------------------------------
  Declare Cursor_Update1 Cursor Local For
  SELECT Guid_Sanad, Mandeh, Bedeh, Bestan FROM GroupHesab 
  Where Acc_Code = @AccCode

  Open Cursor_Update1
  Fetch Next From Cursor_Update1 Into @GuidSanad, @Mandeh, @Bedeh, @Bestan
  While @@Fetch_Status = 0 
  Begin
    IF @GuidSanad = '---' 
    Begin
      If @Bedeh <> 0 
        Set @BeforeMandeh = @Bedeh
      Else If @Bestan <> 0 
        Set @BeforeMandeh = @Bestan
      Else 
       Set @BeforeMandeh = 0 
    End
    Else
    begin
      Set @BeforeMandeh = @BeforeMandeh + @Mandeh
      IF @BeforeMandeh > 0 
        Set @Tash = 'بد'
      Else IF @BeforeMandeh < 0 
        Set @Tash = 'بس'
     Else
       Set @Tash = ''
      Update GroupHesab Set Mandeh= @BeforeMandeh , TashKhis = @Tash Where Guid_Sanad = @GuidSanad
    End
    Fetch Next From Cursor_Update1 Into @GuidSanad, @Mandeh, @Bedeh, @Bestan
  End 
   Update GroupHesab Set FlagMandeh = '1'  Where Guid_Sanad = @GuidSanad
  Update GroupHesab Set Mandeh= Abs(Mandeh), Bestan = Abs(Bestan)  Where Acc_Code = @AccCode
  Close Cursor_Update1
  Deallocate Cursor_Update1
  Fetch Next From Cursor_Update Into @AccCode 
End
Close Cursor_Update
Deallocate Cursor_Update

IF @TypeMandeh <> 31 
Begin
   IF @TypeMandeh = 21
   Begin
      Declare Cursor_Delete Cursor Local For
      Select Gr.Acc_Code From GroupHesab Gr Where Gr.FlagMandeh = '1' 
      And Not Exists(Select * From GroupHesab Gr1  Where Gr1.FlagMandeh = '1' 
      And Gr1.Mandeh = @iValue   And Gr.Acc_Code = Gr1.Acc_Code)
   End
   Else  IF @TypeMandeh = 22
   Begin
      Declare Cursor_Delete Cursor Local For
      Select Gr.Acc_Code From GroupHesab Gr Where Gr.FlagMandeh = '1' 
      And Not Exists(Select * From GroupHesab Gr1  Where Gr1.FlagMandeh = '1' 
      And Gr1.Mandeh <> @iValue   And Gr.Acc_Code = Gr1.Acc_Code)
   End
   Else  IF @TypeMandeh = 23
   Begin
      Declare Cursor_Delete Cursor Local For
      Select Gr.Acc_Code From GroupHesab Gr Where Gr.FlagMandeh = '1' 
      And Not Exists(Select * From GroupHesab Gr1  Where Gr1.FlagMandeh = '1' 
      And Gr1.Mandeh > @iValue   And Gr.Acc_Code = Gr1.Acc_Code)
   End
   Else  IF @TypeMandeh = 24
   Begin
      Declare Cursor_Delete Cursor Local For
      Select Gr.Acc_Code From GroupHesab Gr Where Gr.FlagMandeh = '1' 
      And Not Exists(Select * From GroupHesab Gr1  Where Gr1.FlagMandeh = '1' 
      And Gr1.Mandeh >= @iValue   And Gr.Acc_Code = Gr1.Acc_Code)
   End
   Else  IF @TypeMandeh = 25
   Begin
      Declare Cursor_Delete Cursor Local For
      Select Gr.Acc_Code From GroupHesab Gr Where Gr.FlagMandeh = '1' 
      And Not Exists(Select * From GroupHesab Gr1  Where Gr1.FlagMandeh = '1' 
      And Gr1.Mandeh < @iValue   And Gr.Acc_Code = Gr1.Acc_Code)
   End
   Else  IF @TypeMandeh = 26
   Begin
      Declare Cursor_Delete Cursor Local For
      Select Gr.Acc_Code From GroupHesab Gr Where Gr.FlagMandeh = '1' 
      And Not Exists(Select * From GroupHesab Gr1  Where Gr1.FlagMandeh = '1' 
      And Gr1.Mandeh <= @iValue   And Gr.Acc_Code = Gr1.Acc_Code)
   End
   Open  Cursor_Delete
   Fetch Next From Cursor_Delete Into @AccCode
   While @@Fetch_Status = 0 
   Begin
      Delete From GroupHesab Where Acc_Code = @AccCode
      Fetch Next From Cursor_Delete Into @AccCode
   End
   Close  Cursor_Delete
   Deallocate Cursor_Delete
End
