Report 38 Trial Balance by Period

// ------------------------------------------------------------------------------------------------
// Copyright (c) Microsoft Corporation. All rights reserved.
// Licensed under the MIT License. See License.txt in the project root for license information.
// ------------------------------------------------------------------------------------------------
namespace Microsoft.Finance.GeneralLedger.Reports;

using Microsoft.Finance.Analysis;
using Microsoft.Finance.GeneralLedger.Account;
using Microsoft.Foundation.Company;
using Microsoft.Foundation.Enums;
using Microsoft.Foundation.Period;
using System.Utilities;

/// <summary>
/// Generates trial balance report with period-by-period breakdown across multiple accounting periods.
/// Displays account balances across consecutive periods for trend analysis and period-to-period comparison.
/// </summary>
/// <remarks>
/// Data sources: G/L Account and Accounting Period tables with multi-period balance calculations.
/// Supports configurable rounding factors, indentation levels, and comprehensive dimension filtering.
/// Used for multi-period financial analysis, trend reporting, and management dashboard requirements.
/// </remarks>
report 38 "Trial Balance by Period"
{
    ApplicationArea = Basic, Suite;
    Caption = 'Trial Balance by Period';
    PreviewMode = PrintLayout;
    UsageCategory = ReportsAndAnalysis;
    DataAccessIntent = ReadOnly;
    DefaultRenderingLayout = RDLCLayout;

    dataset
    {
        dataitem("G/L Account"; "G/L Account")
        {
            DataItemTableView = sorting("No.");
            RequestFilterFields = "No.", "Income/Balance", "Account Type", "Global Dimension 1 Filter", "Global Dimension 2 Filter";
            column(COMPANYNAME; COMPANYPROPERTY.DisplayName())
            {
            }
            column(Text015___FORMAT_Indent_; StrSubstNo(IndentationLevelCap, SelectStr(Indent + 1, IndentTxt)))
            {
            }
            column(G_L_Account__TABLECAPTION__________GLFilter; TableCaption + ': ' + GLFilter)
            {
            }
            column(GLFilter; GLFilter)
            {
            }
            column(LastPageText50; LastPageText50Lbl)
            {
            }
            column(LastPageText51; LastPageText51Lbl)
            {
            }
            column(Text052; Text052Lbl)
            {
            }
            column(RoundingText; RoundingText)
            {
            }
            column(RoundingFactorInt; RoundingFactorInt)
            {
            }
            column(STRSUBSTNO___1__2__Header_1_1__Header_1_2__; StrSubstNo('%1 %2', Header[1, 1], Header[1, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_2_1__Header_2_2__; StrSubstNo('%1 %2', Header[2, 1], Header[2, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_4_1__Header_4_2__; StrSubstNo('%1 %2', Header[4, 1], Header[4, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_3_1__Header_3_2__; StrSubstNo('%1 %2', Header[3, 1], Header[3, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_7_1__Header_7_2__; StrSubstNo('%1 %2', Header[7, 1], Header[7, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_6_1__Header_6_2__; StrSubstNo('%1 %2', Header[6, 1], Header[6, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_5_1__Header_5_2__; StrSubstNo('%1 %2', Header[5, 1], Header[5, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_11_1__Header_11_2__; StrSubstNo('%1 %2', Header[11, 1], Header[11, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_10_1__Header_10_2__; StrSubstNo('%1 %2', Header[10, 1], Header[10, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_9_1__Header_9_2__; StrSubstNo('%1 %2', Header[9, 1], Header[9, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_8_1__Header_8_2__; StrSubstNo('%1 %2', Header[8, 1], Header[8, 2]))
            {
            }
            column(STRSUBSTNO___1__2__Header_12_1__Header_12_2__; StrSubstNo('%1 %2', Header[12, 1], Header[12, 2]))
            {
            }
            column(STRSUBSTNO_____1__FORMAT_CLOSINGDATE_StartDate_1____1___; StrSubstNo('..%1', Format(ClosingDate(StartDate[1] - 1))))
            {
            }
            column(LastPage; LastPage)
            {
            }
            column(Trial_Balance_by_PeriodCaption; Trial_Balance_by_PeriodCaptionLbl)
            {
            }
            dataitem(BlankLineCounter; "Integer")
            {
                DataItemTableView = sorting(Number);

                trigger OnPreDataItem()
                begin
                    SetRange(Number, 1, "G/L Account"."No. of Blank Lines");
                end;
            }
            dataitem("Integer"; "Integer")
            {
                DataItemTableView = sorting(Number) where(Number = const(1));
                column(ColumnValuesAsText_4_; ColumnValuesAsText[4])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_3_; ColumnValuesAsText[3])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_2_; ColumnValuesAsText[2])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_1_; ColumnValuesAsText[1])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_10_; ColumnValuesAsText[10])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_9_; ColumnValuesAsText[9])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_8_; ColumnValuesAsText[8])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_7_; ColumnValuesAsText[7])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_6_; ColumnValuesAsText[6])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_13_; ColumnValuesAsText[13])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_12_; ColumnValuesAsText[12])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_11_; ColumnValuesAsText[11])
                {
                    AutoCalcField = false;
                }
                column(ColumnValuesAsText_5_; ColumnValuesAsText[5])
                {
                    AutoCalcField = false;
                }
                column(G_L_Account___No__; "G/L Account"."No.")
                {
                }
                column(PADSTR_____G_L_Account__Indentation___2___G_L_Account__Name; PadStr('', "G/L Account".Indentation * 2) + "G/L Account".Name)
                {
                }
                column(GLAccountType; GLAccountType)
                {
                }
                column(PageGroupNo; PageGroupNo)
                {
                }
                column(G_L_Account___No__of_Blank_Lines_; "G/L Account"."No. of Blank Lines")
                {
                }
                column(GLAccountNewPage; "G/L Account"."New Page")
                {
                }

                trigger OnAfterGetRecord()
                begin
                    GLAccountType := "G/L Account"."Account Type".AsInteger();
                    if IsNewPage then begin
                        PageGroupNo := PageGroupNo + 1;
                        IsNewPage := false;
                    end;

                    if "G/L Account"."New Page" then
                        IsNewPage := true;
                end;
            }

            trigger OnAfterGetRecord()
            begin
                LastPage := false;
                Clear(ColumnValuesAsText);

                SetRange("Date Filter", 0D, ClosingDate(StartDate[1] - 1));
                CalcNetChange("G/L Account");
                ColumnValuesAsText[1] := RoundAmount("Net Change");

                for i := 2 to MaxCount do begin
                    SetRange("Date Filter", StartDate[i - 1], EndDate[i - 1]);
                    CalcNetChange("G/L Account");
                    ColumnValuesAsText[i] := RoundAmount("Net Change");
                end;
            end;

            trigger OnPreDataItem()
            begin
                RoundingFactorInt := RoundingFactor.AsInteger();
                PageGroupNo := 1;

                // Indentation Level
                case Indent of
                    Indent::"0":
                        begin
                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,
                              "Account Type"::"End-Total");
                            SetRange(Indentation, 0);
                        end;
                    Indent::"1":
                        begin
                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,
                              "Account Type"::"End-Total");
                            SetRange(Indentation, 1);
                        end;
                    Indent::"2":
                        begin
                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,
                              "Account Type"::"End-Total");
                            SetRange(Indentation, 2);
                        end;
                    Indent::"3":
                        begin
                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,
                              "Account Type"::"End-Total");
                            SetRange(Indentation, 3);
                        end;
                    Indent::"4":
                        begin
                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,
                              "Account Type"::"End-Total");
                            SetRange(Indentation, 4);
                        end;
                    Indent::"5":
                        begin
                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,
                              "Account Type"::"End-Total");
                            SetRange(Indentation, 5);
                        end;
                end;

                CompanyInfo.CalcFields(Picture);

                if PeriodStartingDate = 0D then
                    Error(Text004);
                AccountingPeriod.Reset();
                if not AccountingPeriod.Get(PeriodStartingDate) then
                    Error(Text005);

                case RoundingFactor of
                    RoundingFactor::"1":
                        RoundingText := Text011;
                    RoundingFactor::"1000":
                        RoundingText := Text012;
                    RoundingFactor::"1000000":
                        RoundingText := Text013;
                end;

                StartDate[1] := PeriodStartingDate;
                EndDate[1] := AccPeriodEndDate(StartDate[1]);
                Header[1, 1] := Format(StartDate[1]);
                Header[1, 2] := Format(EndDate[1]);

                AccountingPeriod2.SetFilter("Starting Date", '>=%1', AccountingPeriod."Starting Date");
                MaxCount := AccountingPeriod2.Count();
                if MaxCount > 13 then
                    MaxCount := 13;

                for i := 2 to MaxCount do begin
                    AccountingPeriod.Next();

                    StartDate[i] := AccountingPeriod."Starting Date";
                    EndDate[i] := AccPeriodEndDate(StartDate[i]);

                    Header[i, 1] := Format(StartDate[i]);
                    Header[i, 2] := Format(EndDate[i]);
                end;
            end;
        }
    }

    requestpage
    {
        SaveValues = true;
        AboutTitle = 'About Trial Balance by Period';
        AboutText = 'Get a detailed breakdown of your closing balances over 12 accounting periods for each G/L account, or summarized by totaling accounts.';

        layout
        {
            area(content)
            {
                group(Options)
                {
                    Caption = 'Options';
                    field(StartingDate; PeriodStartingDate)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = 'Starting Date';
                        ToolTip = 'Specifies the date from which the report or batch job processes information.';
                        ShowMandatory = true;
                    }
                    field(RoundingFactor; RoundingFactor)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = 'Rounding Factor';
                        ToolTip = 'Specifies a rounding factor that will be used in the balance.';
                    }
                    field(Indent; Indent)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = 'Indentation Level';
                        OptionCaption = 'None,0,1,2,3,4,5';
                        ToolTip = 'Specifies the indentation level that sets the range filter for the accounts you want to be displayed or printed. For example, if you select Indentation Level 1, you filter all Level 1 accounts ranging from Begin-Total to End-Total. If there is both a Begin-Total and an End-Total account in the selected range, the report only shows the End-Total.';

                        trigger OnValidate()
                        begin
                            CheckIndent();
                        end;
                    }
                }
            }
        }

        actions
        {
        }
    }

    rendering
    {
        layout(RDLCLayout)
        {
            Type = RDLC;
            LayoutFile = './Finance/GeneralLedger/Reports/TrialBalancebyPeriod.rdlc';
            Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';
        }
    }

    labels
    {
    }

    trigger OnPreReport()
    begin
        GLFilter := "G/L Account".GetFilters();
    end;

    var
        CompanyInfo: Record "Company Information";
        AccountingPeriod: Record "Accounting Period";
        AccountingPeriod2: Record "Accounting Period";
        GLIndent: Record "G/L Account";
        MatrixMgt: Codeunit "Matrix Management";
        i: Integer;
        RoundingFactorInt: Integer;
        PageGroupNo: Integer;
        GLAccountType: Integer;
        LastPage: Boolean;
#pragma warning disable AA0074
        Text004: Label 'Enter the starting date for the first period.';
        Text005: Label 'The starting date is not the starting date of an accounting period.';
        Text011: Label 'Amounts are rounded to 1';
        Text012: Label 'Amounts are in whole 1000s.';
        Text013: Label 'Amounts are in whole 1000000s.';
#pragma warning disable AA0470
        Text014: Label 'Indentation Level %1 is not used in the Chart of Accounts. This Chart of Accounts uses max. %2 levels.';
        IndentationLevelCap: Label 'Indentation Level : %1';
#pragma warning restore AA0470
#pragma warning restore AA0074
        IsNewPage: Boolean;
        MaxIndent: Text[5];
        LastPageText50Lbl: Label '/ Last Page';
        LastPageText51Lbl: Label '/ Continued';
        Text052Lbl: Label 'Page';
        Trial_Balance_by_PeriodCaptionLbl: Label 'Trial Balance by Period';
        IndentTxt: Label 'None,0,1,2,3,4,5';

    protected var
        GLFilter: Text;
        PeriodStartingDate: Date;
        StartDate: array[20] of Date;
        EndDate: array[20] of Date;
        ColumnValuesAsText: array[13] of Text[30];
        RoundingText: Text[80];
        Header: array[13, 2] of Text[100];
        RoundingFactor: Enum "Analysis Rounding Factor";
        MaxCount: Integer;
        Indent: Option "None","0","1","2","3","4","5";

    local procedure AccPeriodEndDate(UseStartDate: Date): Date
    var
        AccountingPeriod2: Record "Accounting Period";
    begin
        AccountingPeriod2."Starting Date" := UseStartDate;
        if AccountingPeriod2.Find('>') then
            exit(AccountingPeriod2."Starting Date" - 1);
        exit(DMY2Date(31, 12, 9999));
    end;

    local procedure CalcNetChange(var GLAccount: Record "G/L Account")
    begin
        GLAccount.CalcFields("Net Change");
        OnAfterCalcNetChange(GLAccount);
    end;

    /// <summary>
    /// Formats decimal amounts according to the specified rounding factor for multi-period trial balance display.
    /// Utilizes Matrix Management codeunit for consistent amount formatting across period columns.
    /// </summary>
    /// <param name="Value">Decimal amount to be formatted</param>
    /// <returns>Formatted amount as text with appropriate rounding applied</returns>
    procedure RoundAmount(Value: Decimal): Text[30]
    begin
        exit(MatrixMgt.FormatAmount(Value, RoundingFactor, false));
    end;

    procedure InitializeRequest(NewPeriodStartingDate: Date; NewRoundingFactor: Option; NewIndent: Option)
    begin
        PeriodStartingDate := NewPeriodStartingDate;
        RoundingFactor := "Analysis Rounding Factor".FromInteger(NewRoundingFactor);
        if NewIndent <> Indent::None then begin
            Indent := NewIndent;
            CheckIndent();
        end;
    end;

    local procedure CheckIndent()
    begin
        GLIndent.Reset();
        MaxIndent := '';
        if GLIndent.FindSet() then
            repeat
                if Format(GLIndent.Indentation) > MaxIndent then
                    MaxIndent := Format(GLIndent.Indentation);
            until GLIndent.Next() = 0;

        if Format(Indent) > MaxIndent then
            if Indent <> Indent::None then
                Error(Text014, SelectStr(Indent + 1, IndentTxt), MaxIndent);
    end;

    [IntegrationEvent(true, false)]
    local procedure OnAfterCalcNetChange(var GLAccount: Record "G/L Account")
    begin
    end;
}