Report 713 Inventory - Customer Sales

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

using Microsoft.Inventory.Item;
using Microsoft.Inventory.Ledger;
using Microsoft.Sales.Customer;
using System.Utilities;

report 713 "Inventory - Customer Sales"
{
    ApplicationArea = Basic, Suite;
    Caption = 'Inventory Customer Sales';
    ToolTip = 'View, print, or save a list of customers that have purchased selected items within a selected period, for example, to analyze customers'' purchasing patterns. The report specifies quantity, amount, discount, profit percentage, and profit.';
    DefaultRenderingLayout = Excel;
    UsageCategory = ReportsAndAnalysis;

    dataset
    {
        dataitem(ReportHeader; "Integer")
        {
            DataItemTableView = sorting(Number) where(Number = const(0));
#if not CLEAN27
            column(CompanyName; COMPANYPROPERTY.DisplayName())
            {
                ObsoleteState = Pending;
                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';
                ObsoleteTag = '27.0';
            }
#endif
#if not CLEAN28
            column(PeriodText; PeriodText)
            {
                ObsoleteState = Pending;
                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';
                ObsoleteTag = '28.0';
            }
#endif
            column(ItemFilter; ItemFilter)
            {
            }
            column(ItemLedgEntryFilter; ItemLedgEntryFilter)
            {
            }
        }
        dataitem(Item; Item)
        {
            DataItemTableView = sorting("No.");
            PrintOnlyIfDetail = true;
            RequestFilterFields = "No.", "No. 2", "Search Description", "Assembly BOM", "Inventory Posting Group";
            column(No_Item; "No.")
            {
                IncludeCaption = true;
            }
            column(Description_Item; Description)
            {
                IncludeCaption = true;
            }
            column(BaseUnitofMeasure_Item; "Base Unit of Measure")
            {
                IncludeCaption = true;
            }
            dataitem("Item Ledger Entry"; "Item Ledger Entry")
            {
                DataItemLink = "Item No." = field("No."), "Variant Code" = field("Variant Filter"), "Location Code" = field("Location Filter"), "Global Dimension 1 Code" = field("Global Dimension 1 Filter"), "Global Dimension 2 Code" = field("Global Dimension 2 Filter");
                DataItemTableView = sorting("Item No.", "Source No.", "Posting Date", "Source Type") where("Source Type" = const(Customer));
                RequestFilterFields = "Posting Date", "Source No.";
                dataitem("Integer"; "Integer")
                {
                    column(SourceNo_ItemLedgEntry; TempValueEntryBuf."Source No.")
                    {
                        IncludeCaption = true;
                    }
                    column(CustName; GetCustName(TempValueEntryBuf."Source No."))
                    {
                    }
                    column(InvQty_ItemLedgEntry; -TempValueEntryBuf."Invoiced Quantity")
                    {
                        DecimalPlaces = 0 : 5;
                    }
                    column(SalesAmtActual_ItemLedgEntry; TempValueEntryBuf."Sales Amount (Actual)")
                    {
                        IncludeCaption = true;
                        AutoFormatType = 1;
                    }
                    column(Profit_ItemLedgEntry; TempValueEntryBuf."Sales Amount (Expected)")
                    {
                        IncludeCaption = true;
                        AutoFormatType = 1;
                    }
                    column(DiscountAmount; -TempValueEntryBuf."Purchase Amount (Expected)")
                    {
                        AutoFormatType = 1;
                    }
                    column(ProfitPct_ItemLedgEntry; ProfitPct)
                    {
                        DecimalPlaces = 1 : 1;
                    }
                    trigger OnAfterGetRecord()
                    begin
                        if Number = 1 then
                            TempValueEntryBuf.FindFirst()

                        else
                            TempValueEntryBuf.Next();

                        ProfitPct := 0;
                        if TempValueEntryBuf."Sales Amount (Actual)" <> 0 then
                            ProfitPct := TempValueEntryBuf."Sales Amount (Expected)" / TempValueEntryBuf."Sales Amount (Actual)" * 100;
                    end;

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

                    trigger OnPreDataItem()
                    begin
                        TempValueEntryBuf.Reset();
                        SetRange(Number, 1, TempValueEntryBuf.Count());
                    end;
                }

                trigger OnAfterGetRecord()
                begin
                    if IsNewGroup() then
                        AddReportLine(ValueEntryBuf);

                    IncrLineAmounts(ValueEntryBuf, "Item Ledger Entry");

                    if IsLastEntry() then
                        AddReportLine(ValueEntryBuf);

                    if not ReportHasData then
                        ReportHasData := true;
                end;

                trigger OnPreDataItem()
                begin
                    LastItemLedgEntryNo := GetLastItemLedgerEntryNo("Item Ledger Entry");
                    Clear(ValueEntryBuf);
                    ReportLineNo := 0;
                end;
            }
            dataitem(SubTotals; Integer)
            {
                DataItemTableView = sorting(Number) where(Number = const(1));
                column(SubTotals_InvQty; SubtotalsInvQty)
                {
                    DecimalPlaces = 0 : 5;
                }
                column(SubTotals_SalesAmtActual; SubtotalsSalesAmtActual)
                {
                    DecimalPlaces = 2 : 2;
                }
                column(SubTotals_DiscountAmount; SubtotalsDiscountAmount)
                {
                    DecimalPlaces = 2 : 2;
                }
                column(SubTotals_Profit; SubtotalsItemProfit)
                {
                    DecimalPlaces = 2 : 2;
                }
                column(SubTotals_ProfitPct; SubtotalsItemProfitPct)
                {
                    DecimalPlaces = 1 : 1;
                }
                column(SubTotals_Description; Item.Description)
                {
                }
                trigger OnPreDataItem()
                begin
                    if TempValueEntryBuf2.IsEmpty() then
                        CurrReport.Break();

                    SubtotalsInvQty := 0;
                    SubtotalsSalesAmtActual := 0;
                    SubtotalsDiscountAmount := 0;
                    SubtotalsItemProfit := 0;
                    SubtotalsItemProfitPct := 0;

                    TempValueEntryBuf2.Reset();
                    if TempValueEntryBuf2.FindSet() then
                        repeat
                            SubtotalsInvQty += (-TempValueEntryBuf2."Invoiced Quantity");
                            SubtotalsSalesAmtActual += TempValueEntryBuf2."Sales Amount (Actual)";
                            SubtotalsDiscountAmount += (-TempValueEntryBuf2."Purchase Amount (Expected)");
                            SubtotalsItemProfit += TempValueEntryBuf2."Sales Amount (Expected)";
                        until TempValueEntryBuf2.Next() = 0;
                    if SubtotalsSalesAmtActual <> 0 then
                        SubtotalsItemProfitPct := SubtotalsItemProfit / SubtotalsSalesAmtActual * 100;
                end;

                trigger OnAfterGetRecord()
                begin
                    TotalsSalesAmtActual += SubtotalsSalesAmtActual;
                    TotalsDiscountAmount += SubtotalsDiscountAmount;
                    TotalsProfit += SubtotalsItemProfit;
                end;

                trigger OnPostDataItem()
                begin
                    TempValueEntryBuf2.DeleteAll();
                end;
            }
        }
        dataitem(Totals; Integer)
        {
            DataItemTableView = sorting(Number) where(Number = const(1));
            column(Totals_SalesAmtActual; TotalsSalesAmtActual)
            {
                DecimalPlaces = 2 : 2;
            }
            column(Totals_DiscountAmount; TotalsDiscountAmount)
            {
                DecimalPlaces = 2 : 2;
            }
            column(Totals_Profit; TotalsProfit)
            {
                DecimalPlaces = 2 : 2;
            }
            column(Totals_ProfitPct; TotalsProfitPct)
            {
                DecimalPlaces = 1 : 1;
            }

            trigger OnPreDataItem()
            begin
                if not ReportHasData then
                    CurrReport.Break();

                if TotalsSalesAmtActual <> 0 then
                    TotalsProfitPct := TotalsProfit / TotalsSalesAmtActual * 100;
            end;
        }
    }

    requestpage
    {
        AboutTitle = 'About Inventory Customer Sales';
        AboutText = 'Analyse your customer sales per item to understand sales trends, optimise inventory management and improve marketing efforts. Assess the relationship between discounts, sales amount and volume of sales for each customer/item combination in the given period.';

        layout
        {
            area(content)
            {
                group(Options)
                {
                    Visible = false;
                    Caption = 'Options';
#if not CLEAN28
                    field(PostingDateFilter; PostingDateFilter)
                    {
                        ApplicationArea = Basic, Suite;
                        Caption = 'Posting Date Filter';
                        ObsoleteState = Pending;
                        ObsoleteReason = 'This request page field will be removed in a future release.';
                        ObsoleteTag = '28.0';
                    }
#endif
                    // Used to set report headers across multiple languages
                    field(RequestItemFilter; ItemFilter)
                    {
                        ApplicationArea = All;
                        Caption = 'Item Filter';
                        ToolTip = 'Specifies the Item Filters applied to this report.';
                        Visible = false;
                    }
                    field(RequestItemLedgEntryFilter; ItemLedgEntryFilter)
                    {
                        ApplicationArea = All;
                        Caption = 'Item Ledger Entry Filter';
                        ToolTip = 'Specifies the Item Ledger Entry Filters applied to this report.';
                        Visible = false;
                    }
                }
            }
        }

        actions
        {
        }
#if not CLEAN28        
        trigger OnQueryClosePage(CloseAction: Action): Boolean
        begin
            PostingDateFilter := "Item Ledger Entry".GetFilter("Posting Date");
        end;
#endif

        trigger OnClosePage()
        begin
            UpdateRequestPageFilterValues();
        end;
    }

    rendering
    {
        layout(Excel)
        {
            Caption = 'Inventory Customer Sales Excel';
            LayoutFile = '.\Inventory\Reports\InventoryCustomerSales.xlsx';
            Type = Excel;
            Summary = 'Built in layout for the Inventory Customer Sales Excel report.';
        }
        layout(Word)
        {
            Caption = 'Inventory Customer Sales Word';
            LayoutFile = '.\Inventory\Reports\InventoryCustomerSales.docx';
            Type = Word;
            Summary = 'Built in layout for the Inventory Customer Sales Word report.';
        }
#if not CLEAN27
        layout(RDLC)
        {
            Caption = 'Inventory Customer Sales RDLC (Obsolete)';
            Type = RDLC;
            LayoutFile = '.\Inventory\Reports\InventoryCustomerSales.rdlc';
            ObsoleteState = Pending;
            ObsoleteReason = 'The RDLC layout has been replaced by the Excel and Word layouts and will be removed in a future release.';
            ObsoleteTag = '27.0';
            Summary = 'Built in layout for the Inventory Customer Sales RDLC (Obsolete) report.';
        }
#endif
    }

    labels
    {
        DataRetrievedLbl = 'Data retrieved:';
        InventoryCustomerSalesLbl = 'Inventory Customer Sales';
        InvCustomerSalesPrintLbl = 'Inv. Cust. Sales (Print)', MaxLength = 31, Comment = 'Excel worksheet name.';
        InvCustomerSalesAnalysisLbl = 'Inv. Cust. Sales (Analysis)', MaxLength = 31, Comment = 'Excel worksheet name.';
        CustomerNoLbl = 'Customer No.';
        CustNameLbl = 'Name';
        InvQtyLbl = 'Invoiced Quantity';
        AmountLbl = 'Amount';
        DiscountAmtLbl = 'Discount Amount';
        ProfitLbl = 'Profit';
        ProfitPctLbl = 'Profit %';
        PeriodLbl = 'Period';
        // About the report labels
        AboutTheReportLbl = 'About the report', MaxLength = 31, Comment = 'Excel worksheet name.';
        EnvironmentLbl = 'Environment';
        CompanyLbl = 'Company';
        UserLbl = 'User';
        RunOnLbl = 'Run on';
        ReportNameLbl = 'Report name';
        DocumentationLbl = 'Documentation';
#if not CLEAN27
        ReportTitle = 'Inventory - Customer Sales';
        Page = 'Page';
        CustomerNo = 'Customer No.';
        Name = 'Name';
        InvoicedQty = 'Invoiced Quantity';
        Amount = 'Amount';
        DiscountAmt = 'Discount Amount';
        Profit = 'Profit';
        ProfitPct = 'Profit %';
        Total = 'Total';
#endif
    }

    trigger OnPreReport()
    begin
        UpdateRequestPageFilterValues();
    end;

    var
        ValueEntryBuf: Record "Value Entry";
        TempValueEntryBuf: Record "Value Entry" temporary;
        TempValueEntryBuf2: Record "Value Entry" temporary;
        ItemFilter: Text;
        ItemLedgEntryFilter: Text;
#if not CLEAN28
        PostingDateFilter: Text;
#endif
        LastItemLedgEntryNo: Integer;
        ReportLineNo: Integer;
        ProfitPct: Decimal;
        SubtotalsInvQty: Decimal;
        SubtotalsSalesAmtActual: Decimal;
        SubtotalsDiscountAmount: Decimal;
        SubtotalsItemProfit: Decimal;
        SubtotalsItemProfitPct: Decimal;
        TotalsSalesAmtActual: Decimal;
        TotalsDiscountAmount: Decimal;
        TotalsProfit: Decimal;
        TotalsProfitPct: Decimal;
        ReportHasData: Boolean;
#if not CLEAN28
        PeriodText: Text;
        PeriodInfoTxt: Label 'Period: %1', Comment = '%1 - period name';
#endif
        TableFiltersTxt: Label '%1: %2', Locked = true;

    local procedure CalcDiscountAmount(ItemLedgerEntryNo: Integer): Decimal
    var
        ValueEntry: Record "Value Entry";
    begin
        ValueEntry.SetCurrentKey("Item Ledger Entry No.");
        ValueEntry.SetRange("Item Ledger Entry No.", ItemLedgerEntryNo);
        ValueEntry.CalcSums("Discount Amount");
        exit(ValueEntry."Discount Amount");
    end;

    local procedure GetLastItemLedgerEntryNo(var ItemLedgerEntry: Record "Item Ledger Entry"): Integer
    var
        LastItemLedgerEntry: Record "Item Ledger Entry";
    begin
        LastItemLedgerEntry.Copy(ItemLedgerEntry);
        if LastItemLedgerEntry.FindLast() then
            exit(LastItemLedgerEntry."Entry No.");
        exit(0);
    end;

    local procedure IncrLineAmounts(var ValueEntryBuf2: Record "Value Entry"; CurrItemLedgerEntry: Record "Item Ledger Entry")
    var
        Profit: Decimal;
        DiscountAmount: Decimal;
    begin
        CurrItemLedgerEntry.CalcFields("Sales Amount (Actual)", "Cost Amount (Actual)", "Cost Amount (Non-Invtbl.)");
        Profit := CurrItemLedgerEntry."Sales Amount (Actual)" + CurrItemLedgerEntry."Cost Amount (Actual)" + CurrItemLedgerEntry."Cost Amount (Non-Invtbl.)";
        DiscountAmount := CalcDiscountAmount(CurrItemLedgerEntry."Entry No.");

        if ValueEntryBuf2."Item No." = '' then begin
            ValueEntryBuf2.Init();
            ValueEntryBuf2."Item No." := CurrItemLedgerEntry."Item No.";
            ValueEntryBuf2."Source No." := CurrItemLedgerEntry."Source No.";
        end;
        ValueEntryBuf2."Invoiced Quantity" += CurrItemLedgerEntry."Invoiced Quantity";
        ValueEntryBuf2."Sales Amount (Actual)" += CurrItemLedgerEntry."Sales Amount (Actual)";
        ValueEntryBuf2."Sales Amount (Expected)" += Profit;
        ValueEntryBuf2."Purchase Amount (Expected)" += DiscountAmount;
    end;

    local procedure AddReportLine(var ValueEntryBuf2: Record "Value Entry")
    begin
        TempValueEntryBuf := ValueEntryBuf2;
        ReportLineNo += 1;
        TempValueEntryBuf."Entry No." := ReportLineNo;
        TempValueEntryBuf.Insert();
        TempValueEntryBuf2.Init();
        TempValueEntryBuf2.TransferFields(TempValueEntryBuf);
        TempValueEntryBuf2.Insert();
        Clear(ValueEntryBuf2);
    end;

    local procedure IsNewGroup(): Boolean
    begin
        exit(("Item Ledger Entry"."Source No." <> ValueEntryBuf."Source No.") and (ValueEntryBuf."Source No." <> ''));
    end;

    local procedure IsLastEntry(): Boolean
    begin
        exit("Item Ledger Entry"."Entry No." = LastItemLedgEntryNo);
    end;

    local procedure GetCustName(CustNo: Code[20]): Text[100]
    var
        Customer: Record Customer;
    begin
        if Customer.Get(CustNo) then
            exit(Customer.Name);
        exit('');
    end;

    local procedure GetTableFilters(TableName: Text; Filters: Text): Text
    begin
        if Filters <> '' then
            exit(StrSubstNo(TableFiltersTxt, TableName, Filters));
        exit('');
    end;

    // Ensures Layout Filter Headings are up to date
    local procedure UpdateRequestPageFilterValues()
    begin
        ItemFilter := GetTableFilters(Item.TableCaption(), Item.GetFilters);
        ItemLedgEntryFilter := GetTableFilters("Item Ledger Entry".TableCaption(), "Item Ledger Entry".GetFilters);
#if not CLEAN28
        PeriodText := StrSubstNo(PeriodInfoTxt, "Item Ledger Entry".GetFilter("Posting Date"));
#endif
    end;
}