Report 121 Customer - Balance to Date

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

/// <summary>
/// Generates customer balance statements showing ledger entries and remaining amounts as of a specified date.
/// </summary>

using Microsoft.Finance.Currency;
using Microsoft.Sales.Customer;
using Microsoft.Sales.Receivables;
using Microsoft.Utilities;
using System.Text;
using System.Utilities;

report 121 "Customer - Balance to Date"
{
    ApplicationArea = Basic, Suite;
    Caption = 'Customer Balance to Date';
    ToolTip = 'View a list with customers'' payment history up until a certain date. You can use the report to extract your total sales income at the close of an accounting period or fiscal year.';
    UsageCategory = ReportsAndAnalysis;
    DataAccessIntent = ReadOnly;
    DefaultRenderingLayout = RDLCLayout;

    dataset
    {
        dataitem(Customer; Customer)
        {
            DataItemTableView = sorting("No.");
            PrintOnlyIfDetail = true;
            RequestFilterFields = "No.", "Date Filter", Blocked;
            column(TodayFormatted; Format(Today, 0, 4))
            {
            }
            column(TxtCustGeTranmaxDtFilter; StrSubstNo(Text000, Format(GetRangeMax("Date Filter"))))
            {
            }
            column(CompanyName; COMPANYPROPERTY.DisplayName())
            {
            }
            column(PrintOnePrPage; PrintOnePrPage)
            {
            }
            column(CustFilter; CustFilter)
            {
            }
            column(PrintAmountInLCY; PrintAmountInLCY)
            {
            }
            column(CustTableCaptCustFilter; TableCaption + ': ' + CustFilter)
            {
            }
            column(No_Customer; "No.")
            {
            }
            column(Name_Customer; Name)
            {
            }
            column(PhoneNo_Customer; "Phone No.")
            {
                IncludeCaption = true;
            }
            column(CustBalancetoDateCaption; CustBalancetoDateCaptionLbl)
            {
            }
            column(CurrReportPageNoCaption; CurrReportPageNoCaptionLbl)
            {
            }
            column(AllamtsareinLCYCaption; AllamtsareinLCYCaptionLbl)
            {
            }
            column(CustLedgEntryPostingDtCaption; CustLedgEntryPostingDtCaptionLbl)
            {
            }
            column(OriginalAmtCaption; OriginalAmtCaptionLbl)
            {
            }
            dataitem(CustLedgEntry3; "Cust. Ledger Entry")
            {
                DataItemTableView = sorting("Entry No.");
                column(PostingDt_CustLedgEntry; Format("Posting Date"))
                {
                    IncludeCaption = false;
                }
                column(DocType_CustLedgEntry; "Document Type")
                {
                    IncludeCaption = true;
                }
                column(DocNo_CustLedgEntry; "Document No.")
                {
                    IncludeCaption = true;
                }
                column(Desc_CustLedgEntry; Description)
                {
                    IncludeCaption = true;
                }
                column(OriginalAmt; Format(OriginalAmt, 0, AutoFormat.ResolveAutoFormat(Enum::"Auto Format"::AmountFormat, CurrencyCode)))
                {
                }
                column(EntryNo_CustLedgEntry; "Entry No.")
                {
                    IncludeCaption = true;
                }
                column(CurrencyCode; CurrencyCode)
                {
                }
                dataitem("Detailed Cust. Ledg. Entry"; "Detailed Cust. Ledg. Entry")
                {
                    DataItemLink = "Cust. Ledger Entry No." = field("Entry No."), "Posting Date" = field("Date Filter");
                    DataItemTableView = sorting("Cust. Ledger Entry No.", "Posting Date") where("Entry Type" = filter(<> "Initial Entry"));
                    column(EntType_DtldCustLedgEnt; "Entry Type")
                    {
                    }
                    column(postDt_DtldCustLedgEntry; Format("Posting Date"))
                    {
                    }
                    column(DocType_DtldCustLedgEntry; "Document Type")
                    {
                    }
                    column(DocNo_DtldCustLedgEntry; "Document No.")
                    {
                    }
                    column(Amt; Format(Amt, 0, AutoFormat.ResolveAutoFormat(Enum::"Auto Format"::AmountFormat, CurrencyCode)))
                    {
                    }
                    column(CurrencyCodeDtldCustLedgEntry; CurrencyCode)
                    {
                    }
                    column(EntNo_DtldCustLedgEntry; DtldCustLedgEntryNum)
                    {
                    }
                    column(RemainingAmt; Format(RemainingAmt, 0, AutoFormat.ResolveAutoFormat(Enum::"Auto Format"::AmountFormat, CurrencyCode)))
                    {
                    }

                    trigger OnAfterGetRecord()
                    begin
                        if not PrintUnappliedEntries then
                            if Unapplied then
                                CurrReport.Skip();

                        if PrintAmountInLCY then begin
                            Amt := "Amount (LCY)";
                            CurrencyCode := '';
                        end else begin
                            Amt := Amount;
                            CurrencyCode := "Currency Code";
                        end;
                        OnAfterDetailedCustLedgEntryOnAfterCalcAmt("Detailed Cust. Ledg. Entry", PrintAmountInLCY, Amt, CurrencyCode, MaxDate);

                        if Amt = 0 then
                            CurrReport.Skip();

                        DtldCustLedgEntryNum := DtldCustLedgEntryNum + 1;
                    end;

                    trigger OnPreDataItem()
                    begin
                        DtldCustLedgEntryNum := 0;
                        CustLedgEntry3.CopyFilter("Posting Date", "Posting Date");
                    end;
                }

                trigger OnAfterGetRecord()
                begin
                    if PrintAmountInLCY then begin
                        CalcFields("Original Amt. (LCY)", "Remaining Amt. (LCY)");
                        OriginalAmt := "Original Amt. (LCY)";
                        RemainingAmt := "Remaining Amt. (LCY)";
                        CurrencyCode := '';
                    end else begin
                        CalcFields("Original Amount", "Remaining Amount");
                        OriginalAmt := "Original Amount";
                        RemainingAmt := "Remaining Amount";
                        CurrencyCode := "Currency Code";
                    end;

                    OnAfterCustLedgEntry3OnAfterGetRecord(CustLedgEntry3, PrintAmountInLCY, OriginalAmt, RemainingAmt, CurrencyCode, MaxDate);
                end;

                trigger OnPreDataItem()
                var
                    TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary;
                    ClosedEntryIncluded: Boolean;
                begin
                    Reset();
                    SetRange("Date Filter", 0D, MaxDate);
                    FilterCustLedgerEntry(CustLedgEntry3);
                    if FindSet() then
                        repeat
                            if not Open then
                                ClosedEntryIncluded := CheckCustEntryIncluded("Entry No.");
                            if Open or ClosedEntryIncluded then begin
                                Mark(true);
                                TempCustLedgerEntry := CustLedgEntry3;
                                TempCustLedgerEntry.Insert();
                            end;
                        until Next() = 0;

                    SetCurrentKey("Entry No.");
                    MarkedOnly(true);

                    AddCustomerDimensionFilter(CustLedgEntry3);

                    CalcCustomerTotalAmount(TempCustLedgerEntry);
                end;
            }
            dataitem(Integer2; "Integer")
            {
                DataItemTableView = sorting(Number) where(Number = filter(1 ..));
                column(CustName; Customer.Name)
                {
                }
                column(TtlAmtCurrencyTtlBuff; TempCurrencyTotalBuffer."Total Amount")
                {
                    AutoFormatExpression = TempCurrencyTotalBuffer."Currency Code";
                    AutoFormatType = 1;
                }
                column(CurryCode_CurrencyTtBuff; TempCurrencyTotalBuffer."Currency Code")
                {
                }

                trigger OnAfterGetRecord()
                begin
                    if Number = 1 then
                        OK := TempCurrencyTotalBuffer.Find('-')
                    else
                        OK := TempCurrencyTotalBuffer.Next() <> 0;
                    if not OK then
                        CurrReport.Break();

                    TempCurrencyTotalBuffer2.UpdateTotal(
                      TempCurrencyTotalBuffer."Currency Code",
                      TempCurrencyTotalBuffer."Total Amount",
                      0,
                      Counter1);
                end;

                trigger OnPostDataItem()
                begin
                    TempCurrencyTotalBuffer.DeleteAll();
                end;

                trigger OnPreDataItem()
                begin
                    if not ShowEntriesWithZeroBalance then
                        TempCurrencyTotalBuffer.SetFilter("Total Amount", '<>0');
                end;
            }

            trigger OnAfterGetRecord()
            begin
                if MaxDate = 0D then
                    Error(BlankMaxDateErr);

                CalcFields("Net Change (LCY)", "Net Change");

                if ("Net Change (LCY)" = 0) and
                   ("Net Change" = 0) and
                   (not ShowEntriesWithZeroBalance)
                then
                    CurrReport.Skip();
            end;

            trigger OnPreDataItem()
            begin
                DateFilterTxt := GetFilter("Date Filter");
                SetRange("Date Filter", 0D, MaxDate);
            end;
        }
        dataitem(Integer3; "Integer")
        {
            DataItemTableView = sorting(Number) where(Number = filter(1 ..));
            column(CurryCode_CurrencyTtBuff2; TempCurrencyTotalBuffer2."Currency Code")
            {
            }
            column(TtlAmtCurrencyTtlBuff2; TempCurrencyTotalBuffer2."Total Amount")
            {
                AutoFormatExpression = TempCurrencyTotalBuffer2."Currency Code";
                AutoFormatType = 1;
            }
            column(TotalCaption; TotalCaptionLbl)
            {
            }

            trigger OnAfterGetRecord()
            begin
                if Number = 1 then
                    OK := TempCurrencyTotalBuffer2.Find('-')
                else
                    OK := TempCurrencyTotalBuffer2.Next() <> 0;
                if not OK then
                    CurrReport.Break();
            end;

            trigger OnPostDataItem()
            begin
                TempCurrencyTotalBuffer2.DeleteAll();
            end;

            trigger OnPreDataItem()
            begin
                TempCurrencyTotalBuffer2.SetFilter("Total Amount", '<>0');
            end;
        }
    }

    requestpage
    {
        AboutTitle = 'About Customer Balance to Date';
        AboutText = 'Generate customer statements to get a clear summary of amounts due. You can share the amounts with customers to follow up on payments. For example, if you need to close an accounting period or fiscal year.';
        SaveValues = true;

        layout
        {
            area(content)
            {
                group(Options)
                {
                    Caption = 'Options';
                    field("Ending Date"; MaxDate)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = 'Ending Date';
                        ToolTip = 'Specifies the last date until which information in the report is shown.';
                        ShowMandatory = true;
                    }
                    field(PrintAmountInLCY; PrintAmountInLCY)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = 'Show Amounts in LCY';
                        ToolTip = 'Specifies if amounts in the report are displayed in LCY. If you leave the check box blank, amounts are shown in foreign currencies.';
                    }
                    field(PrintOnePrPage; PrintOnePrPage)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = 'New Page per Customer';
                        ToolTip = 'Specifies if each customer balance is printed on a separate page, in case two or more customers are included in the report.';
                    }
                    field(PrintUnappliedEntries; PrintUnappliedEntries)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = 'Include Unapplied Entries';
                        ToolTip = 'Specifies if the report includes entries that have been applied and later unapplied using the Unapply action. By default, the report does not show such entries.';
                    }
                    field(ShowEntriesWithZeroBalance; ShowEntriesWithZeroBalance)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = 'Show Entries with Zero Balance';
                        ToolTip = 'Specifies if the report must include customer ledger entries with a balance of 0. By default, the report only includes customer ledger entries with a positive or negative balance.';
                    }
                }
            }
        }

        actions
        {
        }
    }

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

    labels
    {
    }

    trigger OnPreReport()
    var
        FormatDocument: Codeunit "Format Document";
    begin
        CustFilter := FormatDocument.GetRecordFiltersWithCaptions(Customer);
    end;

    var
        AutoFormat: Codeunit "Auto Format";
        DtldCustLedgEntryNum: Integer;
        OK: Boolean;
        DateFilterTxt: Text;

#pragma warning disable AA0074
#pragma warning disable AA0470
        Text000: Label 'Balance on %1';
#pragma warning restore AA0470
#pragma warning restore AA0074
        CustBalancetoDateCaptionLbl: Label 'Customer - Balance to Date';
        CurrReportPageNoCaptionLbl: Label 'Page';
        AllamtsareinLCYCaptionLbl: Label 'All amounts are in LCY.';
        CustLedgEntryPostingDtCaptionLbl: Label 'Posting Date';
        OriginalAmtCaptionLbl: Label 'Amount';
        TotalCaptionLbl: Label 'Total';
        BlankMaxDateErr: Label 'Ending Date must have a value.';

    protected var
        TempCurrencyTotalBuffer: Record "Currency Total Buffer" temporary;
        TempCurrencyTotalBuffer2: Record "Currency Total Buffer" temporary;
        CurrencyCode: Code[10];
        MaxDate: Date;
        PrintAmountInLCY: Boolean;
        PrintOnePrPage: Boolean;
        ShowEntriesWithZeroBalance: Boolean;
        PrintUnappliedEntries: Boolean;
        CustFilter: Text;
        OriginalAmt: Decimal;
        Amt: Decimal;
        RemainingAmt: Decimal;
        Counter1: Integer;

    /// <summary>
    /// Initializes the report request options for the Customer Balance to Date report.
    /// </summary>
    /// <param name="NewPrintAmountInLCY">True to print amounts in local currency.</param>
    /// <param name="NewPrintOnePrPage">True to start a new page per customer.</param>
    /// <param name="NewPrintUnappliedEntries">True to include unapplied entries.</param>
    /// <param name="NewEndingDate">The ending date for the balance calculation.</param>
    procedure InitializeRequest(NewPrintAmountInLCY: Boolean; NewPrintOnePrPage: Boolean; NewPrintUnappliedEntries: Boolean; NewEndingDate: Date)
    begin
        PrintAmountInLCY := NewPrintAmountInLCY;
        PrintOnePrPage := NewPrintOnePrPage;
        PrintUnappliedEntries := NewPrintUnappliedEntries;
        MaxDate := NewEndingDate;
    end;

    local procedure FilterCustLedgerEntry(var CustLedgerEntry: Record "Cust. Ledger Entry")
    begin
        CustLedgerEntry.SetCurrentKey("Customer No.", "Posting Date");
        CustLedgerEntry.SetRange("Customer No.", Customer."No.");
        CustLedgerEntry.SetRange("Posting Date", 0D, MaxDate);
    end;

    local procedure AddCustomerDimensionFilter(var CustLedgerEntry: Record "Cust. Ledger Entry")
    begin
        if Customer.GetFilter("Global Dimension 1 Filter") <> '' then
            CustLedgerEntry.SetFilter("Global Dimension 1 Code", Customer.GetFilter("Global Dimension 1 Filter"));
        if Customer.GetFilter("Global Dimension 2 Filter") <> '' then
            CustLedgerEntry.SetFilter("Global Dimension 2 Code", Customer.GetFilter("Global Dimension 2 Filter"));
        if Customer.GetFilter("Currency Filter") <> '' then
            CustLedgerEntry.SetFilter("Currency Code", Customer.GetFilter("Currency Filter"));
    end;

    local procedure CalcCustomerTotalAmount(var TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary)
    begin
        TempCustLedgerEntry.SetCurrentKey("Entry No.");
        TempCustLedgerEntry.SetRange("Date Filter", 0D, MaxDate);
        AddCustomerDimensionFilter(TempCustLedgerEntry);
        if TempCustLedgerEntry.FindSet() then
            repeat
                CalcRemainingAmount(TempCustLedgerEntry);
                if (RemainingAmt <> 0) or ShowEntriesWithZeroBalance then
                    TempCurrencyTotalBuffer.UpdateTotal(
                      CurrencyCode,
                      RemainingAmt,
                      0,
                      Counter1);
                OnCalcCustomerTotalAmountOnAfterUpdateCurrencyTotalBuffer(TempCustLedgerEntry, TempCurrencyTotalBuffer, Counter1, RemainingAmt, ShowEntriesWithZeroBalance);
            until TempCustLedgerEntry.Next() = 0;
    end;

    local procedure CalcRemainingAmount(var TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary);
    begin
        if PrintAmountInLCY then begin
            TempCustLedgerEntry.CalcFields("Remaining Amt. (LCY)");
            RemainingAmt := TempCustLedgerEntry."Remaining Amt. (LCY)";
            CurrencyCode := '';
        end else begin
            TempCustLedgerEntry.CalcFields("Remaining Amount");
            RemainingAmt := TempCustLedgerEntry."Remaining Amount";
            CurrencyCode := TempCustLedgerEntry."Currency Code";
        end;

        OnAfterCalcRemainingAmount(TempCustLedgerEntry, PrintAmountInLCY, RemainingAmt, CurrencyCode);
    end;

    local procedure CheckCustEntryIncluded(EntryNo: Integer): Boolean
    var
        CustLedgerEntry: Record "Cust. Ledger Entry";
        ClosingCustLedgerEntry: Record "Cust. Ledger Entry";
    begin
        if CustLedgerEntry.Get(EntryNo) and (CustLedgerEntry."Posting Date" <= MaxDate) then begin
            CustLedgerEntry.SetRange("Date Filter", 0D, MaxDate);
            CustLedgerEntry.CalcFields("Remaining Amount", "Original Amount", Amount);
            if CustLedgerEntry."Remaining Amount" <> 0 then
                exit(not CheckUnbalancedUnappliedEntryExists(EntryNo));

            if (ShowEntriesWithZeroBalance) and (DateFilterTxt <> '') then begin
                if ClosingCustLedgerEntry.Get(CustLedgerEntry."Closed by Entry No.") then begin
                    ClosingCustLedgerEntry.SetFilter("Date Filter", DateFilterTxt);
                    if CheckCustEntryIncludedWhenShowEntriesWithZeroBalance(ClosingCustLedgerEntry) then
                        exit(true);
                end;

                if (CustLedgerEntry.Amount = 0) and
                   (CustLedgerEntry."Original Amount" = 0) and
                   not CustLedgerEntry.Open and
                   (CustLedgerEntry."Posting Date" <= MaxDate)
                then
                    exit(true);
            end;

            if PrintUnappliedEntries then
                exit(CheckUnappliedEntryExists(EntryNo));
        end;

        exit(false);
    end;

    local procedure CheckUnappliedEntryExists(EntryNo: Integer): Boolean
    var
        DetailedCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
    begin
        DetailedCustLedgEntry.SetCurrentKey("Cust. Ledger Entry No.", "Entry Type", "Posting Date");
        DetailedCustLedgEntry.SetRange("Cust. Ledger Entry No.", EntryNo);
        DetailedCustLedgEntry.SetRange("Entry Type", DetailedCustLedgEntry."Entry Type"::Application);
        DetailedCustLedgEntry.SetFilter("Posting Date", '>%1', MaxDate);
        DetailedCustLedgEntry.SetRange(Unapplied, true);
        exit(not DetailedCustLedgEntry.IsEmpty);
    end;

    local procedure CheckCustEntryIncludedWhenShowEntriesWithZeroBalance(var ClosingCustLedgerEntry: Record "Cust. Ledger Entry"): Boolean
    var
        DateListText: List of [Text];
        MinDate: Date;
        ListMaxDate: Date;
    begin
        if StrPos(DateFilterTxt, '..') > 0 then begin
            DateListText := ClosingCustLedgerEntry.GetFilter("Date Filter").Split('..');
            if DateListText.Get(1) <> '' then
                Evaluate(MinDate, DateListText.Get(1));

            if DateListText.Get(2) <> '' then
                Evaluate(ListMaxDate, DateListText.Get(2));

            if (ListMaxDate = 0D) and (MinDate <> 0D) then
                exit(ClosingCustLedgerEntry."Posting Date" >= MinDate);

            if true in [
                (ClosingCustLedgerEntry."Posting Date" <= ListMaxDate) and (ClosingCustLedgerEntry."Posting Date" >= MinDate),
                (ClosingCustLedgerEntry."Posting Date" <= ListMaxDate)]
            then
                exit(true);
        end else
            if (ClosingCustLedgerEntry."Posting Date" = ClosingCustLedgerEntry.GetRangeMax("Date Filter")) then
                exit(true);
    end;

    local procedure CheckUnbalancedUnappliedEntryExists(EntryNo: Integer): Boolean
    var
        DetailedCustLedgEntry: Record "Detailed Cust. Ledg. Entry";
    begin
        DetailedCustLedgEntry.SetCurrentKey("Cust. Ledger Entry No.", "Entry Type", "Posting Date");
        DetailedCustLedgEntry.SetRange("Cust. Ledger Entry No.", EntryNo);
        DetailedCustLedgEntry.SetRange("Entry Type", DetailedCustLedgEntry."Entry Type"::Application);
        DetailedCustLedgEntry.SetFilter("Posting Date", '>%1', MaxDate);
        DetailedCustLedgEntry.SetRange(Unapplied, true);
        DetailedCustLedgEntry.CalcSums(Amount);
        exit(DetailedCustLedgEntry.Amount <> 0);
    end;

    [IntegrationEvent(false, false)]
    local procedure OnAfterCalcRemainingAmount(var TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary; PrintAmountInLCY: Boolean; var RemainingAmt: Decimal; var CurrencyCode: Code[10])
    begin
    end;

    [IntegrationEvent(false, false)]
    local procedure OnAfterCustLedgEntry3OnAfterGetRecord(var CustLedgerEntry: Record "Cust. Ledger Entry"; PrintAmountInLCY: Boolean; var OriginalAmt: Decimal; var RemainingAmt: Decimal; var CurrencyCode: Code[10]; MaxDate: Date)
    begin
    end;

    [IntegrationEvent(false, false)]
    local procedure OnAfterDetailedCustLedgEntryOnAfterCalcAmt(var DetailedCustLedgEntry: Record "Detailed Cust. Ledg. Entry"; PrintAmountInLCY: Boolean; var Amt: Decimal; var CurrencyCode: Code[10]; MaxDate: Date)
    begin
    end;

    [IntegrationEvent(false, false)]
    local procedure OnCalcCustomerTotalAmountOnAfterUpdateCurrencyTotalBuffer(var TempCustLedgerEntry: Record "Cust. Ledger Entry" temporary; var TempCurrencyTotalBuffer: Record "Currency Total Buffer" temporary; Counter1: Integer; RemainingAmt: Decimal; ShowEntriesWithZeroBalance: Boolean)
    begin
    end;
}