Codeunit 6710 ODataUtility
src/Layers/W1/BaseApp/System/OData/ODataUtility.Codeunit.al707 lines, Copyright (c) Microsoft Corporation. MIT
namespace System.Integration;using System;using System.Apps;using System.Azure.Identity;using System.Environment;using System.Globalization;using System.Integration.Excel;using System.IO;using System.Reflection;using System.Utilities;codeunit 6710 ODataUtility{ Permissions = TableData "Tenant Web Service OData" = rimd, TableData "Tenant Web Service Columns" = rimd, TableData "Tenant Web Service Filter" = rimd, TableData "Tenant Web Service" = r; trigger OnRun() begin end; var EnvironmentInfo: Codeunit "Environment Information"; Language: Codeunit Language; WorksheetWriter: DotNet WorksheetWriter; WorkbookWriter: DotNet WorkbookWriter; ODataProtocolVersion: Option V3,V4; BalanceSheetHeadingTxt: Label 'Balance Sheet'; BalanceSheetNameTxt: Label 'BalanceSheet', Locked = true; CompanyTxt: Label 'Company'; CurrencyTxt: Label 'Currency'; PrintedTxt: Label 'Printed'; AsOfDateTxt: Label 'As of Date'; DesciptionTxt: Label 'Description'; BalanceTxt: Label 'Balance'; PrintDollarLinesTxt: Label 'Print 0 Value Lines'; SummaryTrialBalanceHeadingTxt: Label 'Summary Trial Balance'; SummaryTrialBalanceNameTxt: Label 'SummaryTrialBalance', Locked = true; FromDateTxt: Label 'From Date'; ToDateTxt: Label 'To Date'; NoTxt: Label 'No.'; NameTxt: Label 'Name'; TotalDebitActivitiesTxt: Label 'Total Debit Activities'; TotalCreditActivitiesTxt: Label 'Total Credit Activities'; EndingBalanceTxt: Label 'Ending Balance'; BeginningBalanceTxt: Label 'Beginning Balance'; IncomeStatementHeadingTxt: Label 'Income Statement'; IncomeStatementNameTxt: Label 'IncomeStatement', Locked = true; AccountNameTxt: Label 'Account Name'; NetChangeTxt: Label 'Net Change'; StatementOfRetainedEarningsHeadingTxt: Label 'Statement of Retained Earnings'; StatementOfRetainedEarningsNameTxt: Label 'StatementOfRetainedEarnings', Locked = true; StartingDateTxt: Label 'Starting Date'; EndingDateTxt: Label 'Ending Date'; AgedAccountsPayableHeadingTxt: Label 'Aged Accounts Payable'; AgedAccountsPayableNameTxt: Label 'AgedAccountsPayable', Locked = true; AgedAccountsReceivableHeaderTxt: Label 'Aged Accounts Receivable'; AgedAccountsReceivableNameTxt: Label 'AgedAccountsReceivable', Locked = true; BalanceDueTxt: Label 'Balance Due'; CurrentTxt: Label 'Current'; UpTo30DaysTxt: Label 'Up to 30 Days'; Days31To60Txt: Label '31-60 Days'; Over60DaysTxt: Label 'Over 60 Days'; AgedByTxt: Label 'Aged By'; AgedAsOfDateTxt: Label 'Aged as of Date'; DueDateTxt: Label 'Due Date'; CashFlowHeadingTxt: Label 'Cash Flow Statement'; CashFlowNameTxt: Label 'CashFlow', Locked = true; PeriodStartTxt: Label 'Period Start'; PeriodEndTxt: Label 'Period End'; DescriptionStaticTxt: Label 'Description', Locked = true; BalanceStaticTxt: Label 'Balance', Locked = true; NoStaticTxt: Label 'No.', Locked = true; NameStaticTxt: Label 'Name', Locked = true; BeginningBalanceStaticTxt: Label 'Beginning Balance', Locked = true; TotalDebitStaticTxt: Label 'Total Debit Activities', Locked = true; TotalCreditStaticTxt: Label 'Total Credit Activities', Locked = true; EndingBalanceStaticTxt: Label 'Ending Balance', Locked = true; AccountNameStaticTxt: Label 'Account Name', Locked = true; NetChangeStaticTxt: Label 'Net Change', Locked = true; BalanceDueStaticTxt: Label 'Balance Due', Locked = true; CurrentStaticTxt: Label 'Current', Locked = true; UpTo30StaticTxt: Label 'Up to 30 Days', Locked = true; Days31To60StaticTxt: Label '31-60 Days', Locked = true; Over60StaticTxt: Label 'Over 60 Days', Locked = true; WebServiceErr: Label 'The webservice %1 required for this excel report is missing.', Comment = '%1 - Web service name'; ODataUtilityTelemetryCategoryTxt: Label 'AL OData Utility', Locked = true; NoTokenForMetadataTelemetryErr: Label 'Access token could not be retrieved.', Locked = true; FailedToSendRequestErr: Label 'The request could not be sent. Details: %1.', Comment = '%1 = a more detailed error message'; ErrorStatusCodeReturnedErr: Label 'The request failed with status code: %1.', Comment = '%1 = a http status code, for example 401'; BearerTokenTemplateTxt: Label 'Bearer %1', Locked = true; CallingEndpointTxt: Label 'Calling endpoint %1 with correlation id %2', Locked = true; EditInExcelUsageWithCentralizedDeploymentsTxt: Label 'Edit in Excel invoked with "Use Centralized deployments" = %1', Locked = true; SaveFileDialogTitleMsg: Label 'Save XML file'; MetadataFileNameTxt: Label 'metadata.xml', Locked = true; SaveFileDialogFilterMsg: Label 'XML Files (*.xml)|*.xml'; [TryFunction] procedure GenerateSelectText(ServiceNameParam: Text; ObjectTypeParam: Option ,,,,,"Codeunit",,,"Page","Query"; var SelectTextParam: Text) var TenantWebServiceColumns: Record "Tenant Web Service Columns"; TenantWebService: Record "Tenant Web Service"; FirstColumn: Boolean; begin if TenantWebService.Get(ObjectTypeParam, ServiceNameParam) then begin FirstColumn := true; TenantWebServiceColumns.SetRange(TenantWebServiceID, TenantWebService.RecordId); if TenantWebServiceColumns.Find('-') then begin SelectTextParam := '$select='; repeat if not FirstColumn then SelectTextParam += ',' else FirstColumn := false; SelectTextParam += TenantWebServiceColumns."Field Name"; until TenantWebServiceColumns.Next() = 0; end; end; end; [TryFunction] procedure GenerateODataV3FilterText(ServiceNameParam: Text; ObjectTypeParam: Option ,,,,,"Codeunit",,,"Page","Query"; var FilterTextParam: Text) begin ODataProtocolVersion := ODataProtocolVersion::V3; FilterTextParam := GenerateFilterText(ServiceNameParam, ObjectTypeParam); if FilterTextParam <> '' then FilterTextParam := StrSubstNo('$filter=%1', FilterTextParam); end; [TryFunction] procedure GenerateODataV4FilterText(ServiceNameParam: Text; ObjectTypeParam: Option ,,,,,"Codeunit",,,"Page","Query"; var FilterTextParam: Text) begin ODataProtocolVersion := ODataProtocolVersion::V4; FilterTextParam := GenerateFilterText(ServiceNameParam, ObjectTypeParam); if FilterTextParam <> '' then FilterTextParam := StrSubstNo('$filter=%1', FilterTextParam); end; local procedure GenerateFilterText(ServiceNameParam: Text; ObjectTypeParam: Option ,,,,,"Codeunit",,,"Page","Query"): Text var TenantWebService: Record "Tenant Web Service"; TableItemFilterTextDictionary: DotNet GenericDictionary2; FilterText: Text; begin if TenantWebService.Get(ObjectTypeParam, ServiceNameParam) then begin TableItemFilterTextDictionary := TableItemFilterTextDictionary.Dictionary(); GetNAVFilters(TenantWebService, TableItemFilterTextDictionary); FilterText := CombineFiltersFromTables(TenantWebService, TableItemFilterTextDictionary); end; exit(FilterText); end; procedure GenerateODataV3Url(ServiceRootUrlParam: Text; ServiceNameParam: Text; ObjectTypeParam: Option ,,,,,,,,"Page","Query"): Text begin exit(GenerateUrl(ServiceRootUrlParam, ServiceNameParam, ObjectTypeParam, ODataProtocolVersion::V3)); end; procedure GenerateODataV4Url(ServiceRootUrlParam: Text; ServiceNameParam: Text; ObjectTypeParam: Option ,,,,,,,,"Page","Query"): Text begin exit(GenerateUrl(ServiceRootUrlParam, ServiceNameParam, ObjectTypeParam, ODataProtocolVersion::V4)); end; local procedure GenerateUrl(ServiceRootUrlParam: Text; ServiceNameParam: Text; ObjectTypeParam: Option ,,,,,,,,"Page","Query"; ODataProtocolVersionParam: Option V3,V4): Text var TenantWebService: Record "Tenant Web Service"; TenantWebServiceOData: Record "Tenant Web Service OData"; WebServiceManagement: Codeunit "Web Service Management"; ODataUrl: Text; SelectText: Text; FilterText: Text; begin if TenantWebService.Get(ObjectTypeParam, ServiceNameParam) then begin TenantWebServiceOData.SetRange(TenantWebServiceID, TenantWebService.RecordId); if TenantWebServiceOData.FindFirst() then begin SelectText := WebServiceManagement.GetODataSelectClause(TenantWebServiceOData); if ODataProtocolVersionParam = ODataProtocolVersionParam::V3 then FilterText := WebServiceManagement.GetODataFilterClause(TenantWebServiceOData) else FilterText := WebServiceManagement.GetODataV4FilterClause(TenantWebServiceOData); end; end; ODataUrl := BuildUrl(ServiceRootUrlParam, SelectText, FilterText); exit(ODataUrl); end; local procedure BuildUrl(ServiceRootUrlParam: Text; SelectTextParam: Text; FilterTextParam: Text): Text var ODataUrl: Text; preSelectTextConjunction: Text; begin if StrPos(ServiceRootUrlParam, '?tenant=') > 0 then preSelectTextConjunction := '&' else preSelectTextConjunction := '?'; if (StrLen(SelectTextParam) > 0) and (StrLen(FilterTextParam) > 0) then ODataUrl := ServiceRootUrlParam + preSelectTextConjunction + SelectTextParam + '&' + FilterTextParam else if StrLen(SelectTextParam) > 0 then ODataUrl := ServiceRootUrlParam + preSelectTextConjunction + SelectTextParam else // FilterText is based on SelectText, so it doesn't make sense to have only the FilterText. ODataUrl := ServiceRootUrlParam; exit(ODataUrl); end; local procedure CombineFiltersFromTables(var TenantWebService: Record "Tenant Web Service"; TableItemFilterTextDictionaryParam: DotNet GenericDictionary2): Text var WebServiceManagement: Codeunit "Web Service Management"; KeyValuePair: DotNet GenericKeyValuePair2; ODataFilterGenerator: DotNet ODataFilterGenerator; "Filter": Text; Conjunction: Text; DataItemFilterText: Text; FilterTextForSelectedColumns: Text; begin foreach KeyValuePair in TableItemFilterTextDictionaryParam do begin FilterTextForSelectedColumns := WebServiceManagement.RemoveUnselectedColumnsFromFilter(TenantWebService, KeyValuePair.Key, KeyValuePair.Value); case TenantWebService."Object Type" of TenantWebService."Object Type"::Page: DataItemFilterText := ODataFilterGenerator.CreateODataV4Filter(KeyValuePair.Key, FilterTextForSelectedColumns, 0); TenantWebService."Object Type"::Query: DataItemFilterText := ODataFilterGenerator.CreateODataV4Filter(KeyValuePair.Key, FilterTextForSelectedColumns, TenantWebService."Object ID"); end; Filter := StrSubstNo('%1%2%3', Filter, Conjunction, DataItemFilterText); Conjunction := ' and '; end; exit(Filter); end; [TryFunction] local procedure FindColumnsFromNAVFilters(var TenantWebService: Record "Tenant Web Service"; TableItemFilterTextDictionaryParam: DotNet GenericDictionary2; var ColumnListParam: DotNet GenericList1) var TenantWebServiceColumns: Record "Tenant Web Service Columns"; FieldTable: Record "Field"; Regex: DotNet Regex; LocalFilterSegments: DotNet Array; TempString1: DotNet String; TempString2: DotNet String; KeyValuePair: DotNet GenericKeyValuePair2; LocalFilterText: Text; I: Integer; Column: Text; IndexOfKeyStart: Integer; IndexOfValueEnd: Integer; begin // SORTING(No.) where(No=FILTER(01121212..01454545|31669966),Balance Due=FILTER(>0)) foreach KeyValuePair in TableItemFilterTextDictionaryParam do begin LocalFilterText := DelStr(KeyValuePair.Value, 1, StrPos(KeyValuePair.Value, 'WHERE') + 5); // becomes No=FILTER(01121212..01454545|31669966),Balance Due=FILTER(>0)) LocalFilterText := DelStr(LocalFilterText, StrLen(LocalFilterText), 1); // remove ), becomes No=FILTER(01121212..01454545|31669966),Balance Due=FILTER(>0) LocalFilterSegments := Regex.Split(LocalFilterText, '=FILTER'); // No (01121212..01454545|31669966),Balance Due (>0) // Break all the filters into key value pairs. for I := 0 to LocalFilterSegments.Length - 2 do begin TempString1 := LocalFilterSegments.GetValue(I); TempString2 := LocalFilterSegments.GetValue(I + 1); IndexOfKeyStart := TempString1.LastIndexOf(','); IndexOfValueEnd := TempString2.LastIndexOf(','); // Start index of the key is either at the beginning or right after the comma. if IndexOfKeyStart > 0 then IndexOfKeyStart := IndexOfKeyStart + 1 else IndexOfKeyStart := 0; // End index of the value is either right before the comma or at the end. Make sure we don't confuse commas in last filter value. if (IndexOfValueEnd < 0) or (I = LocalFilterSegments.Length - 2) then IndexOfValueEnd := TempString2.Length; Column := TempString1.Substring(IndexOfKeyStart, TempString1.Length - IndexOfKeyStart); // Add to the list if the field is in the dataset. FieldTable.SetRange(TableNo, KeyValuePair.Key); FieldTable.SetFilter(ObsoleteState, '<>%1', FieldTable.ObsoleteState::Removed); FieldTable.SetRange("Field Caption", Column); if FieldTable.FindFirst() then begin TenantWebServiceColumns.SetRange(TenantWebServiceID, TenantWebService.RecordId); TenantWebServiceColumns.SetRange("Data Item", KeyValuePair.Key); TenantWebServiceColumns.SetRange("Field Number", FieldTable."No."); if TenantWebServiceColumns.FindFirst() then ColumnListParam.Add(Column); end; end; end; end; local procedure GetNAVFilters(var TenantWebService: Record "Tenant Web Service"; var TableItemFilterTextDictionaryParam: DotNet GenericDictionary2) var TenantWebServiceFilter: Record "Tenant Web Service Filter"; WebServiceManagement: Codeunit "Web Service Management"; FilterText: Text; begin TenantWebServiceFilter.SetRange(TenantWebServiceID, TenantWebService.RecordId()); if TenantWebServiceFilter.Find('-') then repeat FilterText := WebServiceManagement.RetrieveTenantWebServiceFilter(TenantWebServiceFilter); if StrLen(FilterText) > 0 then TableItemFilterTextDictionaryParam.Add(TenantWebServiceFilter."Data Item", FilterText); until TenantWebServiceFilter.Next() = 0; end; [Scope('OnPrem')] procedure GetColumnsFromFilter(var TenantWebService: Record "Tenant Web Service"; FilterText: Text; var ColumnList: DotNet GenericList1) var TableItemFilterTextDictionary: DotNet GenericDictionary2; begin TableItemFilterTextDictionary := TableItemFilterTextDictionary.Dictionary(); TableItemFilterTextDictionary.Add(1, FilterText); FindColumnsFromNAVFilters(TenantWebService, TableItemFilterTextDictionary, ColumnList); end; procedure EditJournalWorksheetInExcel(PageCaption: Text[240]; PageId: Text; JournalBatchName: Text; JournalTemplateName: Text) var EditinExcel: Codeunit "Edit in Excel"; EditinExcelFilters: Codeunit "Edit in Excel Filters"; ObjectId: Integer; begin EditinExcelFilters.AddFieldV2('Journal_Batch_Name', Enum::"Edit in Excel Filter Type"::Equal, JournalBatchName, Enum::"Edit in Excel Edm Type"::"Edm.String"); EditinExcelFilters.AddFieldV2('Journal_Template_Name', Enum::"Edit in Excel Filter Type"::Equal, JournalTemplateName, Enum::"Edit in Excel Edm Type"::"Edm.String"); Evaluate(ObjectId, CopyStr(PageId, 5)); EditinExcel.EditPageInExcel(PageCaption, ObjectId, EditinExcelFilters); end; [Scope('OnPrem')] procedure GenerateExcelTemplateWorkBook(ObjectTypeParm: Option ,,,,,"Codeunit",,,"Page","Query"; ServiceNameParm: Text[50]; ShowDialogParm: Boolean; StatementType: Option BalanceSheet,SummaryTrialBalance,CashFlowStatement,StatementOfRetainedEarnings,AgedAccountsReceivable,AgedAccountsPayable,IncomeStatement) var Company: Record Company; TenantWebService: Record "Tenant Web Service"; FileManagement: Codeunit "File Management"; AzureADTenant: Codeunit "Azure AD Tenant"; TempBlob: Codeunit "Temp Blob"; OfficeAppInfo: DotNet OfficeAppInfo; WorkbookSettingsManager: DotNet WorkbookSettingsManager; SettingsObject: DotNet DynamicsExtensionSettings; NvOutStream: OutStream; NvInStream: InStream; HostName: Text; FileName: Text; TempFileName: Text[60]; begin if not TenantWebService.Get(ObjectTypeParm, ServiceNameParm) then Error(WebServiceErr, ServiceNameParm); CreateOfficeAppInfo(OfficeAppInfo); HostName := GetHostName(); if StrPos(HostName, '?') <> 0 then HostName := CopyStr(HostName, 1, StrPos(HostName, '?') - 1); TempFileName := ServiceNameParm + '.xltm'; NavApp.GetResource('ExcelTemplates/' + TempFileName, NvInStream); TempBlob.CreateOutStream(NvOutStream); CopyStream(NvOutStream, NvInStream); // Collect data for template translations to match company generated for WorkbookWriter := WorkbookWriter.Open(NvOutStream); WorksheetWriter := WorkbookWriter.FirstWorksheet; case StatementType of StatementType::BalanceSheet: AddBalanceSheetCellValues(); StatementType::SummaryTrialBalance: AddSummaryTrialBalancetCellValues(); StatementType::AgedAccountsPayable: AddAgedAccountsPayableCellValues(); StatementType::AgedAccountsReceivable: AddAgedAccountsReceivableCellValues(); StatementType::CashFlowStatement: AddCashFlowStatementCellValues(); StatementType::IncomeStatement: AddIncomeStatementCellValues(); StatementType::StatementOfRetainedEarnings: AddStatementOfRetainedEarningsCellValues(); end; WorkbookSettingsManager := WorkbookSettingsManager.WorkbookSettingsManager(WorkbookWriter.Document); SettingsObject := SettingsObject.DynamicsExtensionSettings(); WorkbookSettingsManager.SettingsObject.Headers.Clear(); if EnvironmentInfo.IsSaaS() then WorkbookSettingsManager.SettingsObject.Headers.Add('BCEnvironment', EnvironmentInfo.GetEnvironmentName()); if Company.Get(TenantWebService.CurrentCompany) then WorkbookSettingsManager.SettingsObject.Headers.Add('Company', Format(Company.Id, 0, 4)) else WorkbookSettingsManager.SettingsObject.Headers.Add('Company', TenantWebService.CurrentCompany); WorkbookSettingsManager.SetAppInfo(OfficeAppInfo); WorkbookSettingsManager.SetHostName(HostName); WorkbookSettingsManager.SetAuthenticationTenant(AzureADTenant.GetAadTenantId()); WorkbookSettingsManager.SetLanguage(Language.GetCultureName(WindowsLanguage)); WorkbookWriter.Close(); FileName := TenantWebService."Service Name" + '.xltm'; FileManagement.BLOBExport(TempBlob, FileName, ShowDialogParm); end; local procedure CreateOfficeAppInfo(var OfficeAppInfo: DotNet OfficeAppInfo) // Note: Keep this in sync with System Module - Edit in Excel - Edit in Excel Impl. var EditinExcelSettings: record "Edit in Excel Settings"; begin OfficeAppInfo := OfficeAppInfo.OfficeAppInfo(); if EditinExcelSettings.Get() and EditinExcelSettings."Use Centralized deployments" then begin OfficeAppInfo.Id := '61bcc63f-b860-4280-8280-3e4fb5ea7726'; OfficeAppInfo.Store := 'EXCatalog'; OfficeAppInfo.StoreType := 'EXCatalog'; OfficeAppInfo.Version := '1.3.0.0'; end else begin OfficeAppInfo.Id := 'WA104379629'; OfficeAppInfo.Store := 'en-US'; OfficeAppInfo.StoreType := 'OMEX'; OfficeAppInfo.Version := '1.3.0.0'; end; Session.LogMessage('0000F7M', StrSubstNo(EditInExcelUsageWithCentralizedDeploymentsTxt, EditinExcelSettings."Use Centralized deployments"), Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', ODataUtilityTelemetryCategoryTxt); end; procedure ExternalizeName(Name: Text) ConvertedName: Text var CurrentPosition: Integer; begin ConvertedName := Name; // Mimics the behavior of the compiler when converting a field or web service name to OData. CurrentPosition := StrPos(ConvertedName, '%'); while CurrentPosition > 0 do begin ConvertedName := DelStr(ConvertedName, CurrentPosition, 1); ConvertedName := InsStr(ConvertedName, 'Percent', CurrentPosition); CurrentPosition := StrPos(ConvertedName, '%'); end; CurrentPosition := 1; while CurrentPosition <= StrLen(ConvertedName) do begin if ConvertedName[CurrentPosition] in [' ', '\', '/', '''', '"', '.', '(', ')', '-', ':'] then if CurrentPosition > 1 then begin if ConvertedName[CurrentPosition - 1] = '_' then begin ConvertedName := DelStr(ConvertedName, CurrentPosition, 1); CurrentPosition -= 1; end else ConvertedName[CurrentPosition] := '_'; end else ConvertedName[CurrentPosition] := '_'; CurrentPosition += 1; end; ConvertedName := RemoveTrailingUnderscore(ConvertedName); end; local procedure RemoveTrailingUnderscore(Input: Text): Text begin Input := DelChr(Input, '>', '_'); exit(Input); end; local procedure AddBalanceSheetCellValues() begin WorksheetWriter.Name(BalanceSheetNameTxt); WorksheetWriter.UpdateCellValueText(2, 'B', BalanceSheetHeadingTxt); WorksheetWriter.SetCellValueText(4, 'B', CompanyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(5, 'B', CurrencyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(7, 'B', PrintedTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(8, 'B', AsOfDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(10, 'B', PrintDollarLinesTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.UpdateCellValueText(11, 'B', DesciptionTxt); WorksheetWriter.UpdateTableColumnHeader('BalanceSheetTable', DescriptionStaticTxt, DesciptionTxt); WorksheetWriter.UpdateCellValueText(11, 'C', BalanceTxt); WorksheetWriter.UpdateTableColumnHeader('BalanceSheetTable', BalanceStaticTxt, BalanceTxt); end; local procedure AddSummaryTrialBalancetCellValues() begin WorksheetWriter.Name(SummaryTrialBalanceNameTxt); WorksheetWriter.UpdateCellValueText(2, 'B', SummaryTrialBalanceHeadingTxt); WorksheetWriter.SetCellValueText(2, 'F', CompanyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(4, 'B', PrintedTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(4, 'F', FromDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(5, 'F', ToDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(6, 'B', PrintDollarLinesTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.UpdateCellValueText(7, 'B', NoTxt); WorksheetWriter.UpdateTableColumnHeader('SummaryTrialBalanceTable', NoStaticTxt, NoTxt); WorksheetWriter.UpdateCellValueText(7, 'C', NameTxt); WorksheetWriter.UpdateTableColumnHeader('SummaryTrialBalanceTable', NameStaticTxt, NameTxt); WorksheetWriter.UpdateCellValueText(7, 'D', BeginningBalanceTxt); WorksheetWriter.UpdateTableColumnHeader('SummaryTrialBalanceTable', BeginningBalanceStaticTxt, BeginningBalanceTxt); WorksheetWriter.UpdateCellValueText(7, 'E', TotalDebitActivitiesTxt); WorksheetWriter.UpdateTableColumnHeader('SummaryTrialBalanceTable', TotalDebitStaticTxt, TotalDebitActivitiesTxt); WorksheetWriter.UpdateCellValueText(7, 'F', TotalCreditActivitiesTxt); WorksheetWriter.UpdateTableColumnHeader('SummaryTrialBalanceTable', TotalCreditStaticTxt, TotalCreditActivitiesTxt); WorksheetWriter.UpdateCellValueText(7, 'G', EndingBalanceTxt); WorksheetWriter.UpdateTableColumnHeader('SummaryTrialBalanceTable', EndingBalanceStaticTxt, EndingBalanceTxt); end; local procedure AddIncomeStatementCellValues() begin WorksheetWriter.Name(IncomeStatementNameTxt); WorksheetWriter.UpdateCellValueText(2, 'B', IncomeStatementHeadingTxt); WorksheetWriter.SetCellValueText(5, 'B', CompanyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(6, 'B', CurrencyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(7, 'B', PrintedTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(9, 'B', FromDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(10, 'B', ToDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(12, 'B', PrintDollarLinesTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.UpdateCellValueText(13, 'B', AccountNameTxt); WorksheetWriter.UpdateTableColumnHeader('IncomeStatementTable', AccountNameStaticTxt, AccountNameTxt); WorksheetWriter.UpdateCellValueText(13, 'C', NetChangeTxt); WorksheetWriter.UpdateTableColumnHeader('IncomeStatementTable', NetChangeStaticTxt, NetChangeTxt); end; local procedure AddStatementOfRetainedEarningsCellValues() begin WorksheetWriter.Name(StatementOfRetainedEarningsNameTxt); WorksheetWriter.UpdateCellValueText(2, 'B', StatementOfRetainedEarningsHeadingTxt); WorksheetWriter.SetCellValueText(5, 'B', CompanyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(6, 'B', CurrencyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(7, 'B', PrintedTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(9, 'B', StartingDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(10, 'B', EndingDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(12, 'B', PrintDollarLinesTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.UpdateCellValueText(13, 'B', DesciptionTxt); WorksheetWriter.UpdateTableColumnHeader('StatementofRetainedEarningsTable', DescriptionStaticTxt, DesciptionTxt); WorksheetWriter.UpdateCellValueText(13, 'C', NetChangeTxt); WorksheetWriter.UpdateTableColumnHeader('StatementofRetainedEarningsTable', NetChangeStaticTxt, NetChangeTxt); end; local procedure AddAgedAccountsPayableCellValues() begin WorksheetWriter.Name(AgedAccountsPayableNameTxt); WorksheetWriter.UpdateCellValueText(2, 'C', AgedAccountsPayableHeadingTxt); WorksheetWriter.SetCellValueText(2, 'G', CompanyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(4, 'C', AgedAsOfDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(4, 'G', CurrencyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(5, 'C', AgedByTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(5, 'D', DueDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(5, 'G', PrintedTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(6, 'H', PrintDollarLinesTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.UpdateCellValueText(7, 'C', NoTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsPayableTable', NoStaticTxt, NoTxt); WorksheetWriter.UpdateCellValueText(7, 'D', NameTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsPayableTable', NameStaticTxt, NameTxt); WorksheetWriter.UpdateCellValueText(7, 'E', BalanceDueTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsPayableTable', BalanceDueStaticTxt, BalanceDueTxt); WorksheetWriter.UpdateCellValueText(7, 'F', CurrentTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsPayableTable', CurrentStaticTxt, CurrentTxt); WorksheetWriter.UpdateCellValueText(7, 'G', UpTo30DaysTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsPayableTable', UpTo30StaticTxt, UpTo30DaysTxt); WorksheetWriter.UpdateCellValueText(7, 'H', Days31To60Txt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsPayableTable', Days31To60StaticTxt, Days31To60Txt); WorksheetWriter.UpdateCellValueText(7, 'I', Over60DaysTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsPayableTable', Over60StaticTxt, Over60DaysTxt); end; local procedure AddAgedAccountsReceivableCellValues() begin WorksheetWriter.Name(AgedAccountsReceivableNameTxt); WorksheetWriter.UpdateCellValueText(2, 'C', AgedAccountsReceivableHeaderTxt); WorksheetWriter.SetCellValueText(2, 'G', CompanyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(4, 'C', AgedAsOfDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(4, 'G', CurrencyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(5, 'C', AgedByTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(5, 'D', DueDateTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(5, 'G', PrintedTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(6, 'H', PrintDollarLinesTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.UpdateCellValueText(7, 'C', NoTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsReceivableTable', NoStaticTxt, NoTxt); WorksheetWriter.UpdateCellValueText(7, 'D', NameTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsReceivableTable', NameStaticTxt, NameTxt); WorksheetWriter.UpdateCellValueText(7, 'E', BalanceDueTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsReceivableTable', BalanceDueStaticTxt, BalanceDueTxt); WorksheetWriter.UpdateCellValueText(7, 'F', CurrentTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsReceivableTable', CurrentStaticTxt, CurrentTxt); WorksheetWriter.UpdateCellValueText(7, 'G', UpTo30DaysTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsReceivableTable', UpTo30StaticTxt, UpTo30DaysTxt); WorksheetWriter.UpdateCellValueText(7, 'H', Days31To60Txt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsReceivableTable', Days31To60StaticTxt, Days31To60Txt); WorksheetWriter.UpdateCellValueText(7, 'I', Over60DaysTxt); WorksheetWriter.UpdateTableColumnHeader('AgedAccountsReceivableTable', Over60StaticTxt, Over60DaysTxt); end; local procedure AddCashFlowStatementCellValues() begin WorksheetWriter.Name(CashFlowNameTxt); WorksheetWriter.UpdateCellValueText(2, 'B', CashFlowHeadingTxt); WorksheetWriter.SetCellValueText(5, 'B', CompanyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(6, 'B', CurrencyTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(7, 'B', PrintedTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(9, 'B', PeriodStartTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(10, 'B', PeriodEndTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.SetCellValueText(12, 'B', PrintDollarLinesTxt, WorksheetWriter.DefaultCellDecorator); WorksheetWriter.UpdateCellValueText(13, 'B', DesciptionTxt); WorksheetWriter.UpdateTableColumnHeader('CashFlowTable', DescriptionStaticTxt, DesciptionTxt); WorksheetWriter.UpdateCellValueText(13, 'C', NetChangeTxt); WorksheetWriter.UpdateTableColumnHeader('CashFlowTable', NetChangeStaticTxt, NetChangeTxt); end; [Scope('OnPrem')] procedure GetTenantWebServiceMetadata(TenantWebService: Record "Tenant Web Service"; var TenantWebServiceMetadata: DotNet QueryMetadataReader) var AllObj: Record AllObj; ApplicationObjectMetadata: Record "Application Object Metadata"; inStream: InStream; begin AllObj.Get(TenantWebService."Object Type", TenantWebService."Object ID"); ApplicationObjectMetadata.Get(AllObj."App Runtime Package ID", TenantWebService."Object Type", TenantWebService."Object ID"); if not ApplicationObjectMetadata.Metadata.HasValue() then exit; ApplicationObjectMetadata.CalcFields(Metadata); ApplicationObjectMetadata.Metadata.CreateInStream(inStream, TEXTENCODING::Windows); TenantWebServiceMetadata := TenantWebServiceMetadata.FromStream(inStream); end; [Scope('OnPrem')] procedure DownloadODataMetadataDocument() var HttpWebRequestMgt: Codeunit "Http Web Request Mgt."; MetadataTempBlob: Codeunit "Temp Blob"; HttpStatusCode: DotNet HttpStatusCode; ResponseHeaders: DotNet NameValueCollection; ResponseInStream: InStream; FileName: Text; begin MetadataTempBlob.CreateInStream(ResponseInStream); if not CreateMetadataWebRequest(HttpWebRequestMgt) then Error(FailedToSendRequestErr, GetLastErrorText()); if not HttpWebRequestMgt.GetResponse(ResponseInStream, HttpStatusCode, ResponseHeaders) then Error(FailedToSendRequestErr, GetLastErrorText()); if not HttpStatusCode.Equals(HttpStatusCode.OK) then Error(ErrorStatusCodeReturnedErr, HttpStatusCode); FileName := MetadataFileNameTxt; DownloadFromStream(ResponseInStream, SaveFileDialogTitleMsg, '', SaveFileDialogFilterMsg, FileName); end; [Scope('OnPrem')] procedure CreateMetadataWebRequest(var HttpWebRequestMgt: Codeunit "Http Web Request Mgt."): Boolean var AzureAdMgt: Codeunit "Azure AD Mgt."; UrlHelper: Codeunit "Url Helper"; Token: SecretText; Endpoint: Text; CorrelationId: Guid; begin if not EnvironmentInfo.IsSaaS() then exit(false); Endpoint := GetUrl(CLIENTTYPE::ODataV4) + '/$metadata'; Token := AzureAdMgt.GetAccessTokenAsSecretText(UrlHelper.GetFixedEndpointWebServiceUrl(), '', false); if Token.IsEmpty() then begin Session.LogMessage('0000E51', NoTokenForMetadataTelemetryErr, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', ODataUtilityTelemetryCategoryTxt); exit(false); end; CorrelationId := CreateGuid(); Session.LogMessage('0000E53', StrSubstNo(CallingEndpointTxt, Endpoint, CorrelationId), Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', ODataUtilityTelemetryCategoryTxt); HttpWebRequestMgt.Initialize(Endpoint); HttpWebRequestMgt.SetMethod('GET'); HttpWebRequestMgt.AddHeader('Authorization', SecretStrSubstNo(BearerTokenTemplateTxt, Token)); HttpWebRequestMgt.AddHeader('x-ms-correlation-id', CorrelationId); HttpWebRequestMgt.SetUserAgent('BusinessCentral/cod6170'); exit(true); end; local procedure GetExcelAddinProviderServiceUrl(): Text var UrlHelper: Codeunit "Url Helper"; begin exit(UrlHelper.GetExcelAddinProviderServiceUrl()); end; procedure GetHostName(): Text begin if EnvironmentInfo.IsSaaS() then exit(GetExcelAddinProviderServiceUrl()); exit(GetUrl(CLIENTTYPE::Web)); end;}