Codeunit 8361 Financial Report Export Job

Base Application, Microsoft.Finance.FinancialReports

src/Layers/W1/BaseApp/Finance/FinancialReports/FinancialReportExportJob.Codeunit.al421 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.Finance.FinancialReports;using Microsoft.EServices.EDocument;using System.Email;using System.IO;using System.Security.AccessControl;using System.Security.User;using System.Utilities;codeunit 8361 "Financial Report Export Job"{    Access = Internal;    trigger OnRun()    begin        ExportSchedules();        ExportPackages();    end;    var        FileMgt: Codeunit "File Management";        FinReportMgt: Codeunit "Financial Report Mgt.";        EmailSubjectLbl: Label 'Financial Report: %1', Comment = '%1 = report description.';        PackageEmailSubjectLbl: Label 'Financial Report Package: %1', Comment = '%1 = report description.';        ExcelContentTypeTxt: Label 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', Locked = true;        ExcelExtTok: Label 'xlsx', Locked = true;        PDFContentTypeTxt: Label 'application/pdf', Locked = true;        PDFExtTok: Label 'pdf', Locked = true;        SendingFailedErr: Label 'The email was not sent because of the following error: "%1"', Comment = '%1 = the error that occurred.';    local procedure ExportSchedules()    var        FinancialReportSchedule: Record "Financial Report Schedule";    begin        FinancialReportSchedule.SetFilter("Next Run Date/Time", '<>%1&<=%2', 0DT, CurrentDateTime());        if FinancialReportSchedule.FindSet(true) then            repeat                ExportSchedule(FinancialReportSchedule);            until FinancialReportSchedule.Next() = 0;    end;    local procedure ExportSchedule(FinancialReportSchedule: Record "Financial Report Schedule")    var        FinancialReport: Record "Financial Report";        FinancialReportExportLog: Record "Financial Report Export Log";        FinancialReportRecipient: Record "Financial Report Recipient";        User: Record User;        UserSetup: Record "User Setup";        Email: Codeunit Email;        EmailMessage: Codeunit "Email Message";        UserNames: List of [Text[50]];        UserEmails: List of [Text];        ReportDescription: Text[250];        SendEmailField: Boolean;    begin        FinancialReportExportLog."Financial Report Name" := FinancialReportSchedule."Financial Report Name";        FinancialReportExportLog."Financial Report Schedule Code" := FinancialReportSchedule.Code;        FinancialReportExportLog."Start Date/Time" := CurrentDateTime();        FinancialReportExportLog.Insert();        FinancialReportRecipient.SetRange("Financial Report Name", FinancialReportSchedule."Financial Report Name");        FinancialReportRecipient.SetRange("Financial Report Schedule Code", FinancialReportSchedule.Code);        if FinancialReportRecipient.FindSet() then            repeat                User.SetRange("User Name", FinancialReportRecipient."User ID");                if not User.FindFirst() then                    continue;                if User.State = User.State::Disabled then                    continue;                UserNames.Add(FinancialReportRecipient."User ID");                if FinancialReportSchedule."Send Email" then begin                    UserSetup.Get(FinancialReportRecipient."User ID");                    UserSetup.TestField("E-Mail");                    UserEmails.Add(UserSetup."E-Mail");                end;            until FinancialReportRecipient.Next() = 0        else            exit;        if not (FinancialReportSchedule."Export to Excel" or FinancialReportSchedule."Export to PDF") then            exit;        FinancialReport.Get(FinancialReportSchedule."Financial Report Name");        if (Format(FinancialReportSchedule."Start Date Filter Formula") <> '') or            (Format(FinancialReportSchedule."End Date Filter Formula") <> '') or            (FinancialReportSchedule."Date Filter Period Formula" <> '')        then begin            FinancialReport.StartDateFilterFormula := FinancialReportSchedule."Start Date Filter Formula";            FinancialReport.EndDateFilterFormula := FinancialReportSchedule."End Date Filter Formula";            FinancialReport.DateFilterPeriodFormula := FinancialReportSchedule."Date Filter Period Formula";            FinancialReport.DateFilterPeriodFormulaLID := FinancialReportSchedule."Date Filter Period Formula LID";        end;        ReportDescription := StrSubstNo(            '%1 - %2', FinancialReport.Description = '' ? Format(FinancialReport.Name) : FinancialReport.Description,            FinancialReportSchedule.Description = '' ? Format(FinancialReportSchedule.Code) : FinancialReportSchedule.Description);        SendEmailField := FinancialReportSchedule."Send Email";        FinancialReportSchedule."Send Email" := FinancialReportSchedule."Send Email" and (UserEmails.Count() > 0);        if FinancialReportSchedule."Send Email" then            CreateEmailMessage(FinancialReportSchedule, ReportDescription, UserEmails, EmailMessage);        if FinancialReportSchedule."Export to Excel" then            ExportExcel(FinancialReportSchedule, FinancialReport, ReportDescription, FinancialReportExportLog.SystemId, UserNames, EmailMessage);        if FinancialReportSchedule."Export to PDF" then            ExportPdf(FinancialReportSchedule, FinancialReport, ReportDescription, FinancialReportExportLog.SystemId, UserNames, EmailMessage);        if FinancialReportSchedule."Send Email" then begin            Email.AddRelation(                EmailMessage, Database::"Financial Report Export Log", FinancialReportExportLog.SystemId,                Enum::"Email Relation Type"::"Primary Source", Enum::"Email Relation Origin"::"Compose Context");            if not Email.Send(EmailMessage, Enum::"Email Scenario"::"Financial Report") then                Error(SendingFailedErr, GetLastErrorText());        end;        FinancialReportSchedule."Send Email" := SendEmailField;        FinancialReportSchedule.CalcNextRunDate();        FinancialReportSchedule.Modify();        Commit();    end;    local procedure CreateEmailMessage(        FinancialReportSchedule: Record "Financial Report Schedule"; ReportDescription: Text; var UserEmails: List of [Text]; var EmailMessage: Codeunit "Email Message")    var        FinancialReportExportEmail: Report "Financial Report Export Email";        TempBlob: Codeunit "Temp Blob";        RecRef: RecordRef;        InStr: InStream;        OutStr: OutStream;        EmailBody: Text;    begin        TempBlob.CreateOutStream(OutStr);        RecRef.GetTable(FinancialReportSchedule);        RecRef.SetRecFilter();        FinancialReportExportEmail.SaveAs('', ReportFormat::Html, OutStr, RecRef);        TempBlob.CreateInStream(InStr);        InStr.ReadText(EmailBody);        EmailMessage.Create(UserEmails, StrSubstNo(EmailSubjectLbl, ReportDescription), EmailBody, true);    end;    internal procedure ExportExcel(        FinancialReportSchedule: Record "Financial Report Schedule"; FinancialReport: Record "Financial Report";        ReportDescription: Text[250]; LogSystemId: Guid; var UserNames: List of [Text[50]]; var EmailMessage: Codeunit "Email Message")    var        AccScheduleLine: Record "Acc. Schedule Line";        FinReportExcelTemplate: Record "Fin. Report Excel Template";        ExportAccSchedToExcel: Report "Export Acc. Sched. to Excel";        TempBlob: Codeunit "Temp Blob";        ExcelTemplateCode: Code[20];        InStr: InStream;        OutStr: OutStream;        AccScheduleView: Text;        IsHandled: Boolean;    begin        // Create filtered acc. schedule line        AccScheduleView := FinancialReportSchedule.GetReportFilters();        if AccScheduleView = '' then            FinReportMgt.SetAccScheduleLineFilter(FinancialReport, AccScheduleLine)        else begin            AccScheduleLine.SetView(AccScheduleView);            AccScheduleLine.SetRange("Schedule Name", FinancialReport."Financial Report Row Group");        end;        FinReportMgt.CalcAccScheduleLineDateFilter(FinancialReport, AccScheduleLine);        ExportAccSchedToExcel.SetOptions(            AccScheduleLine, FinancialReport."Financial Report Column Group", FinancialReport.UseAmountsInAddCurrency,            FinancialReportSchedule."Financial Report Name", FinancialReport.DimPerspective);        // Get excel template if any        ExcelTemplateCode :=            FinancialReportSchedule."Excel Template Code" <> '' ? FinancialReportSchedule."Excel Template Code" : FinancialReport."Excel Template Code";        if ExcelTemplateCode <> '' then begin            FinReportExcelTemplate.Get(FinancialReport.Name, ExcelTemplateCode);            ExportAccSchedToExcel.SetUseExistingTemplate(FinReportExcelTemplate);        end;        ExportAccSchedToExcel.SetRunForExport();        TempBlob.CreateOutStream(OutStr);        OnBeforeSaveExcel(FinancialReportSchedule, FinancialReport, ExportAccSchedToExcel, OutStr, IsHandled);        if not IsHandled then begin            ExportAccSchedToExcel.SetSaveToStream(true);            ExportAccSchedToExcel.Execute('');            ExportAccSchedToExcel.GetSavedStream(OutStr);        end;        TempBlob.CreateInStream(InStr);        CreateInboxEntries(InStr, UserNames, Report::"Export Acc. Sched. to Excel", ReportDescription, Enum::"Report Inbox Output Type"::Excel, LogSystemId);        if FinancialReportSchedule."Send Email" then begin            InStr.ResetPosition();            EmailMessage.AddAttachment(CopyStr(FileMgt.CreateFileNameWithExtension(ReportDescription, ExcelExtTok), 1, MaxStrLen(ReportDescription)), ExcelContentTypeTxt, InStr);        end;    end;    internal procedure ExportPdf(        FinancialReportSchedule: Record "Financial Report Schedule"; FinancialReport: Record "Financial Report";        ReportDescription: Text[250]; LogSystemId: Guid; var UserNames: List of [Text[50]]; var EmailMessage: Codeunit "Email Message")    var        AccScheduleLine: Record "Acc. Schedule Line";        AccountSchedule: Report "Account Schedule";        TempBlob: Codeunit "Temp Blob";        InStr: InStream;        OutStr: OutStream;        AccScheduleParam: Text;        IsHandled: Boolean;    begin        // Get report parameters or fallback to default acc. schedule line filter        AccScheduleParam := FinancialReportSchedule.GetReportParameters();        if AccScheduleParam = '' then            FinReportMgt.SetAccScheduleFilter(FinancialReport, AccountSchedule)        else begin            AccountSchedule.SetFinancialReportName(FinancialReport.Name);            FinReportMgt.CalcAccScheduleLineDateFilter(FinancialReport, AccScheduleLine);            AccountSchedule.SetDateFilterHidden(AccScheduleLine.GetFilter("Date Filter"));        end;        TempBlob.CreateOutStream(OutStr);        AccountSchedule.SetRunForExport();        OnBeforeSavePdf(FinancialReportSchedule, FinancialReport, AccScheduleParam, AccountSchedule, OutStr, IsHandled);        if not IsHandled then            AccountSchedule.SaveAs(AccScheduleParam, ReportFormat::Pdf, OutStr);        TempBlob.CreateInStream(InStr);        CreateInboxEntries(InStr, UserNames, Report::"Account Schedule", ReportDescription, Enum::"Report Inbox Output Type"::PDF, LogSystemId);        if FinancialReportSchedule."Send Email" then begin            InStr.ResetPosition();            EmailMessage.AddAttachment(CopyStr(FileMgt.CreateFileNameWithExtension(ReportDescription, PDFExtTok), 1, MaxStrLen(ReportDescription)), PDFContentTypeTxt, InStr);        end;    end;    local procedure ExportPackages()    var        FinRepPackage: Record "Financial Report Package";        FinRepPackageSchedule: Record "Fin. Report Package Schedule";    begin        FinRepPackageSchedule.SetFilter("Next Run Date/Time", '<>%1&<=%2', 0DT, CurrentDateTime());        if FinRepPackageSchedule.FindSet() then            repeat                if FinRepPackage.Code <> FinRepPackageSchedule."Package Code" then                    FinRepPackage.Get(FinRepPackageSchedule."Package Code");                ExportPackageSchedule(FinRepPackage, FinRepPackageSchedule);            until FinRepPackageSchedule.Next() = 0;    end;    local procedure ExportPackageSchedule(FinRepPackage: Record "Financial Report Package"; var FinRepPackageSchedule: Record "Fin. Report Package Schedule")    var        User: Record User;        UserSetup: Record "User Setup";        FinRepPackageRecipient: Record "Fin. Report Package Recipient";        FinReportPackageReport: Record "Fin. Report Package Report";        FinRepPackageExportLog: Record "Fin. Rep. Package Export Log";        AccountSchedule: Report "Account Schedule";        Email: Codeunit Email;        EmailMessage: Codeunit "Email Message";        TempBlob: Codeunit "Temp Blob";        IsHandled: Boolean;        InStr: InStream;        UserNames: List of [Text[50]];        UserEmails: List of [Text];        OutStr: OutStream;        AccScheduleParam: Text;        ReportDescription: Text;    begin        FinRepPackageExportLog."Package Code" := FinRepPackageSchedule."Package Code";        FinRepPackageExportLog."Schedule Code" := FinRepPackageSchedule."Schedule Code";        FinRepPackageExportLog."Start Date/Time" := CurrentDateTime();        FinRepPackageExportLog.Insert();        FinRepPackageRecipient.SetRange("Package Code", FinRepPackageSchedule."Package Code");        FinRepPackageRecipient.SetRange("Schedule Code", FinRepPackageSchedule."Schedule Code");        if not FinRepPackageRecipient.FindSet() then            exit;        repeat            User.SetRange("User Name", FinRepPackageRecipient."User ID");            if not User.FindFirst() then                continue;            if User.State = User.State::Disabled then                continue;            UserNames.Add(FinRepPackageRecipient."User ID");            if FinRepPackageSchedule."Send Email" then begin                UserSetup.Get(FinRepPackageRecipient."User ID");                UserSetup.TestField("E-Mail");                UserEmails.Add(UserSetup."E-Mail");            end;        until FinRepPackageRecipient.Next() = 0;        if UserNames.Count() = 0 then            exit;        FinReportPackageReport.SetAutoCalcFields("Report Parameters");        FinReportPackageReport.SetRange("Package Code", FinRepPackageSchedule."Package Code");        if not FinReportPackageReport.FindSet() then            exit;        AccScheduleParam := InitAccSchFromPackageReport(AccountSchedule, FinReportPackageReport);        if FinReportPackageReport.Next() <> 0 then            repeat                AccountSchedule.AddPackageReportToAppend(FinReportPackageReport);            until FinReportPackageReport.Next() = 0;        AccountSchedule.SetRunForExport();        TempBlob.CreateOutStream(OutStr);        OnBeforeSaveAccountSchedule(FinRepPackageSchedule, FinReportPackageReport, AccScheduleParam, AccountSchedule, OutStr, IsHandled);        if not IsHandled then            AccountSchedule.SaveAs(AccScheduleParam, ReportFormat::PDF, OutStr);        TempBlob.CreateInStream(InStr);        ReportDescription := StrSubstNo('%1 (%2)',            FinRepPackage.Description <> '' ? FinRepPackage.Description : FinRepPackage.Code,            FinRepPackageSchedule.Name <> '' ? FinRepPackageSchedule.Name : FinRepPackageSchedule."Schedule Code");        CreateInboxEntries(InStr, UserNames, Report::"Account Schedule", CopyStr(ReportDescription, 1, 250), Enum::"Report Inbox Output Type"::PDF, FinRepPackageExportLog.SystemId);        if FinRepPackageSchedule."Send Email" then begin            CreatePackageEmailMessage(FinRepPackageSchedule, ReportDescription, UserEmails, EmailMessage);            InStr.ResetPosition();            EmailMessage.AddAttachment(CopyStr(FileMgt.CreateFileNameWithExtension(ReportDescription, PDFExtTok), 1, 250), PDFContentTypeTxt, InStr);            Email.AddRelation(                EmailMessage, Database::"Fin. Rep. Package Export Log", FinRepPackageExportLog.SystemId,                Enum::"Email Relation Type"::"Primary Source", Enum::"Email Relation Origin"::"Compose Context");            if not Email.Send(EmailMessage, Enum::"Email Scenario"::"Financial Report") then                Error(SendingFailedErr, GetLastErrorText());        end;        FinRepPackageSchedule.CalcNextRunDate();        FinRepPackageSchedule.Modify();        Commit();    end;    procedure InitAccSchFromPackageReport(var AccountSchedule: Report "Account Schedule"; var FinReportPackageReport: Record "Fin. Report Package Report") AccScheduleParam: Text    var        AccScheduleLine: Record "Acc. Schedule Line";        FinancialReport: Record "Financial Report";    begin        FinancialReport.Get(FinReportPackageReport."Financial Report Name");        if (Format(FinReportPackageReport."Start Date Filter Formula") <> '') or            (Format(FinReportPackageReport."End Date Filter Formula") <> '') or            (FinReportPackageReport."Date Filter Period Formula" <> '')        then begin            FinancialReport.StartDateFilterFormula := FinReportPackageReport."Start Date Filter Formula";            FinancialReport.EndDateFilterFormula := FinReportPackageReport."End Date Filter Formula";            FinancialReport.DateFilterPeriodFormula := FinReportPackageReport."Date Filter Period Formula";            FinancialReport.DateFilterPeriodFormulaLID := FinReportPackageReport."Date Filter Period Formula LID";        end;        AccScheduleParam := FinReportPackageReport.GetReportParameters();        if AccScheduleParam = '' then            FinReportMgt.SetAccScheduleFilter(FinancialReport, AccountSchedule)        else begin            AccountSchedule.SetFinancialReportName(FinancialReport.Name);            FinReportMgt.CalcAccScheduleLineDateFilter(FinancialReport, AccScheduleLine);            AccountSchedule.SetDateFilterHidden(AccScheduleLine.GetFilter("Date Filter"));        end;        AccountSchedule.SetPackageCode(FinReportPackageReport."Package Code");    end;    local procedure CreatePackageEmailMessage(FinRepPackageSchedule: Record "Fin. Report Package Schedule"; ReportDescription: Text; var UserEmails: List of [Text]; var EmailMessage: Codeunit "Email Message")    var        FinRepPackageExportEmail: Report "Fin. Rep. Package Export Email";        TempBlob: Codeunit "Temp Blob";        InStr: InStream;        OutStr: OutStream;        EmailBody: Text;    begin        TempBlob.CreateOutStream(OutStr);        FinRepPackageExportEmail.SetContext(FinRepPackageSchedule, ReportDescription);        FinRepPackageExportEmail.SaveAs('', ReportFormat::Html, OutStr);        TempBlob.CreateInStream(InStr);        InStr.ReadText(EmailBody);        EmailMessage.Create(UserEmails, StrSubstNo(PackageEmailSubjectLbl, ReportDescription), EmailBody, true);    end;    local procedure CreateInboxEntries(        var InStr: InStream; var UserNames: List of [Text[50]];        ReportId: Integer; ReportDescription: Text[250]; OutputType: Enum "Report Inbox Output Type"; LogSystemId: Guid)    var        ReportInbox: Record "Report Inbox";        OutStr: OutStream;        UserName: Text[50];    begin        ReportInbox.Init();        ReportInbox."Report ID" := ReportId;        ReportInbox.Description := ReportDescription;        ReportInbox."Output Type" := OutputType;        ReportInbox."Report Output".CreateOutStream(OutStr);        CopyStream(OutStr, InStr);        ReportInbox."Job Queue Log Entry ID" := LogSystemId;        ReportInbox."Created Date-Time" := RoundDateTime(CurrentDateTime, 60000);        foreach UserName in UserNames do begin            ReportInbox."Entry No." := 0;            ReportInbox."User ID" := UserName;            ReportInbox.Insert(true);        end;    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeSavePdf(FinancialReportSchedule: Record "Financial Report Schedule"; FinancialReport: Record "Financial Report"; AccScheduleParam: Text; var AccountSchedule: Report "Account Schedule"; var OutStr: OutStream; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeSaveExcel(FinancialReportSchedule: Record "Financial Report Schedule"; FinancialReport: Record "Financial Report"; var ExportAccSchedToExcel: Report "Export Acc. Sched. to Excel"; var OutStr: OutStream; var IsHandled: Boolean)    begin    end;    [IntegrationEvent(false, false)]    local procedure OnBeforeSaveAccountSchedule(        FinRepPackageSchedule: Record "Fin. Report Package Schedule"; FinRepPackageReport: Record "Fin. Report Package Report";        AccScheduleParam: Text; var AccountSchedule: Report "Account Schedule"; var OutStr: OutStream; var IsHandled: Boolean)    begin    end;}