CREATE  PROCEDURE CreateBalanceSheet
    @StartDate VARCHAR(10) ,
    @EndDate VARCHAR(10) ,
    @AccountLevel SMALLINT ,
    @ContainParentLevel BIT ,
    @StartAccountCode VARCHAR(20) ,
    @EndAccountCode VARCHAR(20) ,
    @StartNoteNumber FLOAT ,
    @EndNoteNumber FLOAT
AS
    DECLARE @ResultTable TABLE
        (
          AccountCode VARCHAR(20) ,
          AccountName NVARCHAR(90) ,
          AccountLevel SMALLINT ,
          Bedeh MONEY ,
          Bestan MONEY ,
          RemainBedeh MONEY ,
          RemainBestan MONEY
        )
    
    DECLARE @Sanad TABLE
        (
          AccountCode VARCHAR(20) ,
          Bedeh MONEY ,
          Bestan MONEY
        )
    INSERT  INTO @Sanad
            SELECT  Acc_Code ,
                    SUM(BEDEH) Bedeh ,
                    SUM(BESTAN) Bestan
            FROM    dbo.Sanad WITH ( NOLOCK )
                    INNER JOIN HeadS ON CAST(dbo.HeadS.Tarikh_User AS DATE) BETWEEN dbo.ShamsiToMiladi(@StartDate)
                                                              AND
                                                              dbo.ShamsiToMiladi(@EndDate)
                                        AND HeadS.Id_Sanad = Sanad.Id_Sanad
            WHERE   Nu_Manual BETWEEN @StartNoteNumber
                              AND     @EndNoteNumber
                    AND Acc_Code BETWEEN @StartAccountCode
                                 AND     @EndAccountCode
            GROUP BY Acc_Code 
  
    DECLARE @Acc TABLE
        (
          AccountCode VARCHAR(20) ,
          AccountName NVARCHAR(90) ,
          ParentCode INT ,
          AutoId INT ,
          AccountLevel SMALLINT ,
          HasChild BIT ,
          Bedeh MONEY ,
          Bestan MONEY
        )
    INSERT  INTO @Acc
            SELECT  Account.Acc_Code ,
                    Account.Acc_Name ,
                    Account.Parent_Code ,
                    Account.AutoId ,
                    Account.Acc_Level ,
                    Account.Has_Child ,
                    Sanad.Bedeh ,
                    Sanad.Bestan
            FROM    Account
                    LEFT JOIN @Sanad Sanad ON Sanad.AccountCode = Account.Acc_Code
            WHERE   ( Has_Child = 0
                      AND ( Sanad.Bedeh > 0
                            OR Sanad.Bestan > 0
                          )
                    )
                    OR ( Has_Child = 1 )
            ORDER BY Acc_Code;
    WITH    CTE
              AS ( SELECT   Acc.AutoId ,
                            Acc.ParentCode ,
                            Acc.AccountCode ,
                            Acc.AccountName ,
                            Acc.AccountLevel ,
                            CASE WHEN Acc.HasChild = 0 THEN Acc.Bedeh
                                 ELSE 0
                            END Bedeh ,
                            CASE WHEN Acc.HasChild = 0 THEN Acc.Bestan
                                 ELSE 0
                            END Bestan ,
                            Acc.HasChild
                   FROM     @Acc Acc
                   UNION ALL
                   SELECT   Acc.AutoId ,
                            Acc.ParentCode ,
                            Acc.AccountCode ,
                            Acc.AccountName ,
                            Acc.AccountLevel ,
                            CTE.Bedeh ,
                            CTE.Bestan ,
                            Acc.HasChild
                   FROM     @Acc Acc
                            JOIN CTE ON Acc.AutoId = CTE.ParentCode
                 )
        INSERT  INTO @ResultTable
                ( AccountCode ,
                  AccountName ,
                  AccountLevel ,
                  Bedeh ,
                  Bestan ,
                  RemainBedeh ,
                  RemainBestan
                )
                SELECT  AccountCode ,
                        AccountName ,
                        CTE.AccountLevel ,
                        SUM(Bedeh) Bedeh ,
                        SUM(Bestan) Bestan ,
                        SUM(CASE WHEN Bedeh > Bestan THEN Bedeh - Bestan
                            END) RemainBedeh ,
                        SUM(CASE WHEN Bestan > Bedeh THEN Bestan - Bedeh
                            END) RemainBedeh
                FROM    CTE
                GROUP BY AccountCode ,
                        AccountName ,
                        CTE.AccountLevel
                HAVING  SUM(Bedeh) > 0
                        OR SUM(Bestan) > 0
                ORDER BY AccountCode
  
    DECLARE @SumBedeh MONEY ,
        @SumBestan MONEY ,
        @SumRemainBedeh MONEY ,
        @SumRemainBestan MONEY 
        
    SELECT  @SumBedeh = SUM(Bedeh) ,
            @SumBestan = SUM(Bestan) ,
            @SumRemainBedeh = SUM(RemainBedeh) ,
            @SumRemainBestan = SUM(RemainBestan)
    FROM    @ResultTable
    WHERE   LEN(AccountCode) <= 2
    
    
    SELECT  * ,
            @SumBedeh SumBedeh ,
            @SumBestan SumBestan ,
            @SumRemainBedeh SumRemainBedeh ,
            @SumRemainBestan SumRemainBestan
    FROM    @ResultTable
    WHERE   ( ( @ContainParentLevel = 0
                AND AccountLevel = @AccountLevel
              )
              OR ( @ContainParentLevel = 1
                   AND AccountLevel <= @AccountLevel
                 )
            )
