Report 1143 Import Cost Budget from Excel

Base Application, Microsoft.CostAccounting.Budget

src/Layers/W1/BaseApp/CostAccounting/Budget/ImportCostBudgetfromExcel.Report.al363 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.CostAccounting.Budget;using Microsoft.CostAccounting.Account;using Microsoft.CostAccounting.Setup;using System.IO;report 1143 "Import Cost Budget from Excel"{    Caption = 'Import Cost Budget from Excel';    ProcessingOnly = true;    dataset    {        dataitem(TempCostBudgetBuffer; "Cost Budget Buffer")        {            DataItemTableView = sorting("Cost Type No.", "Cost Center Code", "Cost Object Code", Date);            UseTemporary = true;            trigger OnAfterGetRecord()            begin                RecNo := RecNo + 1;                if RecNo = 1 then begin                    Window.Open(                      Text028 +                      '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');                    TotalRecNo := Count;                    Window.Update(1, 0);                end;                if ImportOption = ImportOption::"Replace entries" then begin                    CostBudgetEntry.SetRange("Cost Type No.", "Cost Type No.");                    CostBudgetEntry.SetRange(Date, Date);                    CostBudgetEntry.SetFilter("Entry No.", '<=%1', LastEntryNoBeforeImport);                    if not CostBudgetEntry.IsEmpty() then                        CostBudgetEntry.DeleteAll(true);                end;                if Amount = 0 then                    CurrReport.Skip();                if not IsLineTypeCostType("Cost Type No.") then                    CurrReport.Skip();                CostBudgetEntry.Init();                CostBudgetEntry."Entry No." := EntryNo;                CostBudgetEntry."Budget Name" := ToCostBudgetName;                CostBudgetEntry."Cost Type No." := "Cost Type No.";                CostBudgetEntry.Date := Date;                CostBudgetEntry.Amount := Round(Amount);                CostBudgetEntry.Description := Description;                CostBudgetEntry."Cost Center Code" := "Cost Center Code";                CostBudgetEntry."Cost Object Code" := "Cost Object Code";                CostBudgetEntry.Insert(true);                EntryNo := EntryNo + 1;                Window.Update(1, Round(RecNo / TotalRecNo * 10000, 1));            end;            trigger OnPostDataItem()            begin                if RecNo > 0 then                    Message(Text004, CostBudgetEntry.TableCaption(), RecNo);            end;            trigger OnPreDataItem()            begin                RecNo := 0;                if not CostBudgetName.Get(ToCostBudgetName) then begin                    if not Confirm(Text001, false, CostBudgetName.TableCaption(), ToCostBudgetName) then                        CurrReport.Break();                    CostBudgetName.Name := ToCostBudgetName;                    OnPreDataItemOnBeforeCostBudgetNameInsert(CostBudgetName);                    CostBudgetName.Insert();                end else                    if not Confirm(Text003, false, LowerCase(Format(SelectStr(ImportOption + 1, Text027))), ToCostBudgetName) then                        CurrReport.Break();                LastEntryNoBeforeImport := CostBudgetEntry3.GetLastEntryNo();                EntryNo := LastEntryNoBeforeImport + 1            end;        }    }    requestpage    {        layout        {            area(content)            {                group(Options)                {                    Caption = 'Options';                    field(ToCostBudgetName; ToCostBudgetName)                    {                        ApplicationArea = CostAccounting;                        Caption = 'Budget Name';                        TableRelation = "Cost Budget Name";                        ToolTip = 'Specifies the name of the budget.';                    }                    field(ImportOption; ImportOption)                    {                        ApplicationArea = CostAccounting;                        Caption = 'Option';                        OptionCaption = 'Replace entries,Add entries';                        ToolTip = 'Specifies whether the budget entries are added from Excel to budget entries that are currently in cost accounting, or whether the entries are replaced in cost accounting with the budget entries from Excel.';                    }                    field(Description; Description)                    {                        ApplicationArea = CostAccounting;                        Caption = 'Description';                        ToolTip = 'Specifies the description of what you are importing from Excel.';                    }                }            }        }        actions        {        }        trigger OnQueryClosePage(CloseAction: Action): Boolean        var            FileMgt: Codeunit "File Management";        begin            if CloseAction = ACTION::OK then begin                ServerFileName := FileMgt.UploadFile(Text006, ExcelFileExtensionTok);                if ServerFileName = '' then                    exit(false);                SheetName := ExcelBuffer.SelectSheetsName(ServerFileName);                if SheetName = '' then                    exit(false);            end;        end;    }    labels    {    }    trigger OnPostReport()    begin        ExcelBuffer.DeleteAll();        TempCostBudgetBuffer.DeleteAll();    end;    trigger OnPreReport()    begin        if ToCostBudgetName = '' then            Error(Text000);        if CostType.Find('-') then            repeat                TempCostType.Init();                TempCostType := CostType;                TempCostType.Insert();            until CostType.Next() = 0;        ExcelBuffer.LockTable();        CostBudgetEntry.SetRange("Budget Name", ToCostBudgetName);        if not CostBudgetName.Get(ToCostBudgetName) then            Clear(CostBudgetName);        CostAccSetup.Get();        ExcelBuffer.OpenBook(ServerFileName, SheetName);        ExcelBuffer.ReadSheet();        AnalyzeData();    end;    var        ExcelBuffer: Record "Excel Buffer";        CostBudgetEntry: Record "Cost Budget Entry";        CostAccSetup: Record "Cost Accounting Setup";        CostType: Record "Cost Type";        TempCostType: Record "Cost Type" temporary;        CostBudgetName: Record "Cost Budget Name";        CostBudgetEntry3: Record "Cost Budget Entry";        Window: Dialog;        ServerFileName: Text;        SheetName: Text[250];        ToCostBudgetName: Code[10];        EntryNo: Integer;        LastEntryNoBeforeImport: Integer;        TotalRecNo: Integer;        RecNo: Integer;        Description: Text[50];        ImportOption: Option "Replace entries","Add entries";#pragma warning disable AA0074        Text000: Label 'You must specify a budget name to which to import.';#pragma warning disable AA0470        Text001: Label 'Do you want to create %1 with the name %2?';        Text003: Label 'Are you sure that you want to %1 for the budget name %2?';        Text004: Label '%1 table has been successfully updated with %2 entries.';#pragma warning restore AA0470        Text006: Label 'Import Excel File';        Text007: Label 'Analyzing Data...\\';        Text009: Label 'Cost Type No';#pragma warning disable AA0470        Text011: Label 'The text %1 can only be specified once in the Excel worksheet.';#pragma warning restore AA0470        Text014: Label 'Date';        Text017: Label 'Cost Center Code';        Text018: Label 'Cost Object Code';#pragma warning disable AA0470        Text019: Label 'You cannot import %1 value, which is not available in the %2 table.';#pragma warning restore AA0470        Text023: Label 'You cannot import the same information more than once.';        Text025: Label 'Cost Types have not been found in the Excel worksheet.';        Text026: Label 'Dates have not been recognized in the Excel worksheet.';        Text027: Label 'Replace entries,Add entries';        Text028: Label 'Importing from Excel worksheet';#pragma warning restore AA0074        ExcelFileExtensionTok: Label '.xlsx', Locked = true;    local procedure AnalyzeData()    var        TempExcelBuffer: Record "Excel Buffer" temporary;        TempCostBudgetBuffer2: Record "Cost Budget Buffer" temporary;        CostCenter: Record "Cost Center";        CostObject: Record "Cost Object";        HeaderRowNo: Integer;        TestDateTime: DateTime;        OldRowNo: Integer;    begin        Window.Open(          Text007 +          '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');        Window.Update(1, 0);        TotalRecNo := ExcelBuffer.Count();        RecNo := 0;        TempCostBudgetBuffer2.Init();        TempCostBudgetBuffer.DeleteAll();        HeaderRowNo := 0;        OldRowNo := 0;        if ExcelBuffer.Find('-') then            repeat                RecNo := RecNo + 1;                Window.Update(1, Round(RecNo / TotalRecNo * 10000, 1));                case true of                    StrPos(UpperCase(ExcelBuffer."Cell Value as Text"), UpperCase(Text009)) <> 0:                        if HeaderRowNo = 0 then begin                            HeaderRowNo := ExcelBuffer."Row No.";                            InsertTempExcelBuffer(ExcelBuffer, TempExcelBuffer, Text009);                        end else                            Error(Text011, Text009);                    StrPos(UpperCase(ExcelBuffer."Cell Value as Text"), UpperCase(Text017)) <> 0:                        if HeaderRowNo = ExcelBuffer."Row No." then                            InsertTempExcelBuffer(ExcelBuffer, TempExcelBuffer, Text017)                        else                            if (HeaderRowNo < ExcelBuffer."Row No.") and (HeaderRowNo <> 0) then                                Error(Text011, Text017);                    StrPos(UpperCase(ExcelBuffer."Cell Value as Text"), UpperCase(Text018)) <> 0:                        if HeaderRowNo = ExcelBuffer."Row No." then                            InsertTempExcelBuffer(ExcelBuffer, TempExcelBuffer, Text018)                        else                            if (HeaderRowNo < ExcelBuffer."Row No.") and (HeaderRowNo <> 0) then                                Error(Text011, Text018);                    ExcelBuffer."Row No." = HeaderRowNo:                        begin                            TempExcelBuffer := ExcelBuffer;                            if Evaluate(TestDateTime, TempExcelBuffer."Cell Value as Text") then begin                                TempExcelBuffer."Cell Value as Text" := Format(DT2Date(TestDateTime));                                TempExcelBuffer.Comment := Text014;                                TempExcelBuffer.Insert();                            end;                        end;                    (ExcelBuffer."Row No." > HeaderRowNo) and (HeaderRowNo > 0):                        begin                            if ExcelBuffer."Row No." <> OldRowNo then begin                                OldRowNo := ExcelBuffer."Row No.";                                Clear(TempCostBudgetBuffer2);                            end;                            TempExcelBuffer.SetRange("Column No.", ExcelBuffer."Column No.");                            if TempExcelBuffer.Find('-') then                                case TempExcelBuffer.Comment of                                    Text009:                                        begin                                            TempCostType.SetRange(                                              "No.",                                              CopyStr(                                                ExcelBuffer."Cell Value as Text",                                                1, MaxStrLen(TempCostBudgetBuffer2."Cost Type No.")));                                            if TempCostType.FindFirst() then                                                TempCostBudgetBuffer2."Cost Type No." :=                                                  CopyStr(                                                    ExcelBuffer."Cell Value as Text",                                                    1, MaxStrLen(TempCostBudgetBuffer2."Cost Type No."))                                            else                                                TempCostBudgetBuffer2."Cost Type No." := '';                                        end;                                    Text017:                                        if CostCenter.Get(CopyStr(                                                ExcelBuffer."Cell Value as Text", 1, MaxStrLen(TempCostBudgetBuffer2."Cost Center Code")))                                        then                                            TempCostBudgetBuffer2."Cost Center Code" := CostCenter.Code                                        else                                            Error(Text019, TempCostBudgetBuffer2.FieldCaption("Cost Center Code"), CostCenter.TableCaption());                                    Text018:                                        if CostObject.Get(CopyStr(                                                ExcelBuffer."Cell Value as Text", 1, MaxStrLen(TempCostBudgetBuffer2."Cost Object Code")))                                        then                                            TempCostBudgetBuffer2."Cost Object Code" := CostObject.Code                                        else                                            Error(Text019, TempCostBudgetBuffer2.FieldCaption("Cost Object Code"), CostObject.TableCaption());                                    Text014:                                        if TempCostBudgetBuffer2."Cost Type No." <> '' then begin                                            TempCostBudgetBuffer := TempCostBudgetBuffer2;                                            Evaluate(TempCostBudgetBuffer.Date, TempExcelBuffer."Cell Value as Text");                                            Evaluate(TempCostBudgetBuffer.Amount, ExcelBuffer."Cell Value as Text");                                            if not TempCostBudgetBuffer.Find('=') then                                                TempCostBudgetBuffer.Insert()                                            else                                                Error(Text023);                                        end;                                end;                        end;                end;            until ExcelBuffer.Next() = 0;        Window.Close();        TempExcelBuffer.Reset();        TempExcelBuffer.SetRange(Comment, Text009);        if not TempExcelBuffer.FindFirst() then            Error(Text025);        TempExcelBuffer.SetRange(Comment, Text014);        if not TempExcelBuffer.FindFirst() then            Error(Text026);    end;    procedure SetGLBudgetName(NewToCostBudgetName: Code[10])    begin        ToCostBudgetName := NewToCostBudgetName;    end;    local procedure IsLineTypeCostType(CostTypeNo: Code[20]): Boolean    var        CostType: Record "Cost Type";    begin        if not CostType.Get(CostTypeNo) then            exit(false);        exit(CostType.Type = CostType.Type::"Cost Type");    end;    local procedure InsertTempExcelBuffer(var ExcelBuffer: Record "Excel Buffer"; var TempExcelBuffer: Record "Excel Buffer" temporary; Text: Text[250])    begin        TempExcelBuffer := ExcelBuffer;        TempExcelBuffer.Comment := Text;        TempExcelBuffer.Insert();    end;    [IntegrationEvent(false, false)]    local procedure OnPreDataItemOnBeforeCostBudgetNameInsert(var CostBudgetName: Record "Cost Budget Name")    begin    end;}