Report 7132 Export Item Budget to Excel

Base Application, Microsoft.Inventory.Analysis

src/Layers/W1/BaseApp/Inventory/Analysis/ExportItemBudgettoExcel.Report.al384 lines, Copyright (c) Microsoft Corporation. MIT

// ------------------------------------------------------------------------------------------------// 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.Analysis;using Microsoft.Finance.Analysis;using Microsoft.Finance.Dimension;using Microsoft.Finance.GeneralLedger.Setup;using Microsoft.Foundation.Enums;using System.IO;using System.Utilities;report 7132 "Export Item Budget to Excel"{    Caption = 'Export Item Budget to Excel';    ProcessingOnly = true;    UseRequestPage = false;    dataset    {        dataitem("Integer"; "Integer")        {            DataItemTableView = sorting(Number) where(Number = const(1));            trigger OnAfterGetRecord()            var                Window: Dialog;                ClientFileName: Text;                RecNo: Integer;                TotalRecNo: Integer;                RowNo: Integer;                ColumnNo: Integer;            begin                if DoUpdateExistingWorksheet then begin                    if ServerFileName = '' then                        ServerFileName := FileMgt.UploadFile(Text002, ExcelFileExtensionTok);                    if ServerFileName = '' then                        exit;                    ClientFileName := FileMgt.GetFileName(ServerFileName);                    SheetName := TempExcelBuffer.SelectSheetsName(ServerFileName);                    if SheetName = '' then                        exit;                end;                Window.Open(                  Text000 +                  '@1@@@@@@@@@@@@@@@@@@@@@\');                TempExcelBuffer.DeleteAll();                Clear(TempExcelBuffer);                ItemBudgetName.Get(AnalysisArea, BudgetName);                GLSetup.Get();                if DateFilter = '' then                    Error(Text010, Text003);                if FindLine('-') then                    repeat                        TotalRecNo := TotalRecNo + 1;                    until NextLine(1) = 0;                RowNo := 1;                EnterCell(RowNo, 1, Text001, false, false, true, '', TempExcelBuffer."Cell Type"::Text);                EnterCell(RowNo, 2, '', false, false, true, '', TempExcelBuffer."Cell Type"::Text);                RowNo := RowNo + 1;                EnterFilterInCell(RowNo, BudgetName, ItemBudgetName.TableCaption());                if GlobalDim1Filter <> '' then begin                    RowNo := RowNo + 1;                    Dim.Get(GLSetup."Global Dimension 1 Code");                    EnterFilterInCell(RowNo, GlobalDim1Filter, Dim."Filter Caption");                end;                if GlobalDim2Filter <> '' then begin                    RowNo := RowNo + 1;                    Dim.Get(GLSetup."Global Dimension 2 Code");                    EnterFilterInCell(RowNo, GlobalDim2Filter, Dim."Filter Caption");                end;                if BudgetDim1Filter <> '' then begin                    RowNo := RowNo + 1;                    Dim.Get(ItemBudgetName."Budget Dimension 1 Code");                    EnterFilterInCell(RowNo, BudgetDim1Filter, Dim."Filter Caption");                end;                if BudgetDim2Filter <> '' then begin                    RowNo := RowNo + 1;                    Dim.Get(ItemBudgetName."Budget Dimension 2 Code");                    EnterFilterInCell(RowNo, BudgetDim2Filter, Dim."Filter Caption");                end;                if BudgetDim3Filter <> '' then begin                    RowNo := RowNo + 1;                    Dim.Get(ItemBudgetName."Budget Dimension 3 Code");                    EnterFilterInCell(RowNo, BudgetDim3Filter, Dim."Filter Caption");                end;                if ItemFilter <> '' then begin                    RowNo := RowNo + 1;                    EnterFilterInCell(RowNo, ItemFilter, Text004);                end;                if DateFilter <> '' then begin                    RowNo := RowNo + 1;                    EnterFilterInCell(RowNo, DateFilter, Text003);                end;                if SourceNoFilter <> '' then begin                    RowNo := RowNo + 1;                    if SourceTypeFilter = SourceTypeFilter::Customer then                        EnterFilterInCell(RowNo, SourceNoFilter, Text005)                    else                        EnterFilterInCell(RowNo, SourceNoFilter, Text006);                end;                RowNo := RowNo + 2;                EnterFilterInCell(RowNo, LineDimCode, Text008);                RowNo := RowNo + 1;                EnterFilterInCell(RowNo, ColumnDimCode, Text009);                RowNo := RowNo + 1;                case ValueType of                    ValueType::"Sales Amount":                        ShowValueAsText := Text012;                    ValueType::"Cost Amount":                        if AnalysisArea = AnalysisArea::Sales then                            ShowValueAsText := Text014                        else                            ShowValueAsText := Text013;                    ValueType::Quantity:                        ShowValueAsText := Text015;                end;                EnterFilterInCell(RowNo, ShowValueAsText, Text011);                RowNo := RowNo + 2;                if FindLine('-') then begin                    if FindColumn('-') then begin                        ColumnNo := 1;                        EnterCell(RowNo, ColumnNo, Text007, false, true, false, '', TempExcelBuffer."Cell Type"::Text);                        OnOnAfterGetRecordOnAfterEnterTableDataCell(TempExcelBuffer, RowNo, ColumnNo, AnalysisArea, BudgetName);                        repeat                            ColumnNo := ColumnNo + 1;                            EnterCell(RowNo, ColumnNo, ColumnDimCodeBuffer.Code, false, false, false, '', TempExcelBuffer."Cell Type"::Text);                        until NextColumn(1) = 0;                    end;                    repeat                        RecNo := RecNo + 1;                        Window.Update(1, Round(RecNo / TotalRecNo * 10000, 1));                        RowNo := RowNo + 1;                        ColumnNo := 1;                        EnterCell(                          RowNo, ColumnNo, LineDimCodeBuffer.Code, LineDimCodeBuffer."Show in Bold", false, false, '', TempExcelBuffer."Cell Type"::Text);                        OnOnAfterGetRecordOnAfterEnterLineDimensionCodeCell(TempExcelBuffer, RowNo, ColumnNo, LineDimCodeBuffer, AnalysisArea, BudgetName);                        if FindColumn('-') then                            repeat                                ColumnNo := ColumnNo + 1;                                ColumnValue :=                                  ItemBudgetManagement.CalculateAmount(                                    ValueType, true,                                    ItemStatisticsBuffer, ItemBudgetName,                                    ItemFilter, SourceTypeFilter, SourceNoFilter, DateFilter,                                    GlobalDim1Filter, GlobalDim2Filter, BudgetDim1Filter, BudgetDim2Filter, BudgetDim3Filter,                                    LineDimType, LineDimCodeBuffer, ColumnDimType, ColumnDimCodeBuffer);                                EnterCell(                                  RowNo,                                  ColumnNo,                                  MatrixMgt.FormatAmount(ColumnValue, RoundingFactor, false),                                  LineDimCodeBuffer."Show in Bold",                                  false,                                  false,                                  '',                                  TempExcelBuffer."Cell Type"::Number)                            until NextColumn(1) = 0;                    until NextLine(1) = 0;                end;                Window.Close();                if DoUpdateExistingWorksheet then begin                    TempExcelBuffer.UpdateBookExcel(ServerFileName, SheetName, false);                    TempExcelBuffer.WriteSheet('', CompanyName, UserId);                    TempExcelBuffer.CloseBook();                    if not TestMode then                        TempExcelBuffer.OpenExcelWithName(ClientFileName);                end else begin                    TempExcelBuffer.CreateBook(ServerFileName, TempExcelBuffer.GetExcelReference(10));                    TempExcelBuffer.WriteSheet(                      PadStr(StrSubstNo('%1 %2', ItemBudgetName.Name, ItemBudgetName.Description), 30),                      CompanyName, UserId);                    TempExcelBuffer.CloseBook();                    if not TestMode then                        TempExcelBuffer.OpenExcel();                end;            end;        }    }    requestpage    {        layout        {        }        actions        {        }    }    labels    {    }    var        ItemBudgetName: Record "Item Budget Name";        Dim: Record Dimension;        LineDimCodeBuffer: Record "Dimension Code Buffer";        ColumnDimCodeBuffer: Record "Dimension Code Buffer";        ItemStatisticsBuffer: Record "Item Statistics Buffer";        TempExcelBuffer: Record "Excel Buffer" temporary;        GLSetup: Record "General Ledger Setup";        ItemBudgetManagement: Codeunit "Item Budget Management";        MatrixMgt: Codeunit "Matrix Management";        FileMgt: Codeunit "File Management";        LineDimCode: Text[30];        ColumnDimCode: Text[30];        DateFilter: Text;        InternalDateFilter: Text;        ShowValueAsText: Text[30];        ServerFileName: Text;        SheetName: Text[250];        BudgetName: Code[10];        GlobalDim1Filter: Text;        GlobalDim2Filter: Text;        BudgetDim1Filter: Text;        BudgetDim2Filter: Text;        BudgetDim3Filter: Text;        SourceNoFilter: Text;        ItemFilter: Text;        ColumnValue: Decimal;        AnalysisArea: Enum "Analysis Area Type";        ValueType: Enum "Item Analysis Value Type";        SourceTypeFilter: Enum "Analysis Source Type";        PeriodType: Enum "Analysis Period Type";        LineDimType: Enum "Item Budget Dimension Type";        ColumnDimType: Enum "Item Budget Dimension Type";        RoundingFactor: Enum "Analysis Rounding Factor";        PeriodInitialized: Boolean;        DoUpdateExistingWorksheet: Boolean;        TestMode: Boolean;#pragma warning disable AA0074        Text000: Label 'Analyzing Data...\\';        Text001: Label 'Filters';        Text002: Label 'Update Workbook';        Text003: Label 'Date Filter';        Text004: Label 'Item Filter';        Text005: Label 'Customer Filter';        Text006: Label 'Vendor Filter';        Text007: Label 'Table Data';        Text008: Label 'Show as Lines';        Text009: Label 'Show as Columns';#pragma warning disable AA0470        Text010: Label '%1 must not be blank.';#pragma warning restore AA0470        Text011: Label 'Show Value as';        Text012: Label 'Sales Amount';        Text013: Label 'Cost Amount';        Text014: Label 'COGS Amount';        Text015: Label 'Quantity';#pragma warning restore AA0074        ExcelFileExtensionTok: Label '.xlsx', Locked = true;    procedure SetParameters(NewAnalysisArea: Enum "Analysis Area Type"; NewBudgName: Code[10]; NewValueType: Enum "Item Analysis Value Type"; NewGlobalDim1Filter: Text; NewGlobalDim2Filter: Text; NewBudgDim1Filter: Text; NewBudgDim2Filter: Text; NewBudgDim3Filter: Text; NewDateFilter: Text; NewSourceTypeFilter: Enum "Analysis Source Type"; NewSourceNoFilter: Text; NewItemFilter: Text; NewInternalDateFilter: Text; NewPeriodInitialized: Boolean; NewPeriodType: Enum "Analysis Period Type"; NewLineDimType: Enum "Item Budget Dimension Type"; NewColumnDimType: Enum "Item Budget Dimension Type"; NewLineDimCode: Text[30]; NewColumnDimCode: Text[30]; NewRoundingFactor: Enum "Analysis Rounding Factor")    begin        AnalysisArea := NewAnalysisArea;        BudgetName := NewBudgName;        ValueType := NewValueType;        GlobalDim1Filter := NewGlobalDim1Filter;        GlobalDim2Filter := NewGlobalDim2Filter;        BudgetDim1Filter := NewBudgDim1Filter;        BudgetDim2Filter := NewBudgDim2Filter;        BudgetDim3Filter := NewBudgDim3Filter;        DateFilter := NewDateFilter;        ItemFilter := NewItemFilter;        SourceTypeFilter := NewSourceTypeFilter;        SourceNoFilter := NewSourceNoFilter;        InternalDateFilter := NewInternalDateFilter;        PeriodInitialized := NewPeriodInitialized;        PeriodType := NewPeriodType;        LineDimType := NewLineDimType;        ColumnDimType := NewColumnDimType;        LineDimCode := NewLineDimCode;        ColumnDimCode := NewColumnDimCode;        RoundingFactor := NewRoundingFactor;    end;    local procedure EnterFilterInCell(RowNo: Integer; "Filter": Text; FieldName: Text[100])    begin        EnterCell(RowNo, 1, FieldName, false, false, false, '', TempExcelBuffer."Cell Type"::Text);        EnterCell(RowNo, 2, CopyStr(Filter, 1, 250), false, false, false, '', TempExcelBuffer."Cell Type"::Text);    end;    local procedure EnterCell(RowNo: Integer; ColumnNo: Integer; CellValue: Text[250]; Bold: Boolean; Italic: Boolean; UnderLine: Boolean; NumberFormat: Text[30]; CellType: Option)    begin        TempExcelBuffer.Init();        TempExcelBuffer.Validate("Row No.", RowNo);        TempExcelBuffer.Validate("Column No.", ColumnNo);        TempExcelBuffer."Cell Value as Text" := CellValue;        TempExcelBuffer.Formula := '';        TempExcelBuffer.Bold := Bold;        TempExcelBuffer.Italic := Italic;        TempExcelBuffer.Underline := UnderLine;        TempExcelBuffer.NumberFormat := NumberFormat;        TempExcelBuffer."Cell Type" := CellType;        TempExcelBuffer.Insert();    end;    local procedure FindLine(Which: Text[1024]): Boolean    begin        exit(          ItemBudgetManagement.FindRecord(            ItemBudgetName, LineDimType, LineDimCodeBuffer, Which,            ItemFilter, SourceNoFilter, PeriodType, DateFilter, PeriodInitialized, InternalDateFilter,            GlobalDim1Filter, GlobalDim2Filter, BudgetDim1Filter, BudgetDim2Filter, BudgetDim3Filter));    end;    local procedure NextLine(Steps: Integer): Integer    begin        exit(          ItemBudgetManagement.NextRecord(            ItemBudgetName, LineDimType, LineDimCodeBuffer, Steps,            ItemFilter, SourceNoFilter, PeriodType, DateFilter,            GlobalDim1Filter, GlobalDim2Filter, BudgetDim1Filter, BudgetDim2Filter, BudgetDim3Filter));    end;    local procedure FindColumn(Which: Text[1024]): Boolean    begin        exit(          ItemBudgetManagement.FindRecord(            ItemBudgetName, ColumnDimType, ColumnDimCodeBuffer, Which,            ItemFilter, SourceNoFilter, PeriodType, DateFilter, PeriodInitialized, InternalDateFilter,            GlobalDim1Filter, GlobalDim2Filter, BudgetDim1Filter, BudgetDim2Filter, BudgetDim3Filter));    end;    local procedure NextColumn(Steps: Integer): Integer    begin        exit(          ItemBudgetManagement.NextRecord(            ItemBudgetName, ColumnDimType, ColumnDimCodeBuffer, Steps,            ItemFilter, SourceNoFilter, PeriodType, DateFilter,            GlobalDim1Filter, GlobalDim2Filter, BudgetDim1Filter, BudgetDim2Filter, BudgetDim3Filter));    end;    procedure SetUpdateExistingWorksheet(UpdateExistingWorksheet: Boolean)    begin        DoUpdateExistingWorksheet := UpdateExistingWorksheet;    end;    procedure SetFileNameSilent(NewFileName: Text)    begin        ServerFileName := NewFileName;    end;    procedure SetTestMode(NewTestMode: Boolean)    begin        TestMode := NewTestMode;    end;    [IntegrationEvent(false, false)]    local procedure OnOnAfterGetRecordOnAfterEnterTableDataCell(var TempExcelBuffer: Record "Excel Buffer" temporary; HeaderRowNo: Integer; var ColumnNo: Integer; AnalysisArea: Enum "Analysis Area Type"; BudgetName: Code[10])    begin    end;    [IntegrationEvent(false, false)]    local procedure OnOnAfterGetRecordOnAfterEnterLineDimensionCodeCell(var TempExcelBuffer: Record "Excel Buffer" temporary; DataRowNo: Integer; var ColumnNo: Integer; LineDimCodeBuffer: Record "Dimension Code Buffer"; AnalysisArea: Enum "Analysis Area Type"; BudgetName: Code[10])    begin    end;}