Codeunit 1491 Edit in Excel Filters Impl.
src/System Application/App/Edit in Excel/src/Filters/EditInExcelFiltersImpl.Codeunit.al252 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 System.Integration.Excel;using System;using System.Reflection;/// <summary>/// This codeunit provides an interface to running Edit in Excel for a specific page./// </summary>codeunit 1491 "Edit in Excel Filters Impl."{ Access = Internal; InherentEntitlements = X; InherentPermissions = X; var FieldFilters: DotNet GenericDictionary2; // Dictionary of [Text, DotNet FilterCollectionNode]; FieldTypes: Dictionary of [Text, Text]; FilterAlreadyExistErr: Label 'A filter for field %1 already exist, please use the existing entry.', Comment = '%1 = ODataFieldName, ex.: No_'; FilterDoesNotExistErr: Label 'No filter exists for field %1.', Comment = '%1 = ODataFieldName, ex.: No_'; EditInExcelTelemetryCategoryTxt: Label 'Edit in Excel Filters', Locked = true; ChildNodesJsonTok: Label 'childNodes', Locked = true; TypeJsonTok: Label 'type', Locked = true; LeftNodeJsonTok: Label 'leftNode', Locked = true; RightNodeJsonTok: Label 'rightNode', Locked = true; NameJsonTok: Label 'name', Locked = true; ValueJsonTok: Label 'value', Locked = true; TypeNotFoundTxt: Label 'The %1 json token was not found.', Locked = true; NodeTypeNotRecognizedTxt: Label 'Node type %1 was not recognized.', Locked = true; FilterContainsMultipleOperatorsTxt: Label 'The page filter contains multiple operators, the latter was removed.', Locked = true; FieldPayloadEdmTypeTok: Label 'fieldPayload.%1.edmType', Locked = true; FieldPayloadAlNameTok: Label 'fieldPayload.%1.alName', Locked = true; FieldNotOnThePageTxt: Label 'Field not on the page', Locked = true; procedure AddField(ODataFieldName: Text; EditinExcelFilterCollectionType: Enum "Edit in Excel Filter Collection Type"; EditInExcelEdmType: Enum "Edit in Excel Edm Type"): Codeunit "Edit in Excel Fld Filter Impl." begin TryAdd(ODataFieldName, EditinExcelFilterCollectionType, Format(EditInExcelEdmType)); exit(Get(ODataFieldName)); end; procedure AddField(ODataFieldName: Text; EditInExcelFilterType: Enum "Edit in Excel Filter Type"; FilterValue: Text; EditInExcelEdmType: Enum "Edit in Excel Edm Type") EditinExcelFieldFilter: Codeunit "Edit in Excel Fld Filter Impl." begin EditinExcelFieldFilter := AddField(ODataFieldName, "Edit in Excel Filter Collection Type"::"and", EditInExcelEdmType); Get(ODataFieldName).AddFilterValueV2(EditInExcelFilterType, FilterValue); end; procedure Get(ODataFieldName: Text): Codeunit "Edit in Excel Fld Filter Impl." var EditinExcelFldFilterImpl: Codeunit "Edit in Excel Fld Filter Impl."; FilterCollectionNode: DotNet FilterCollectionNode; EdmType: Text; begin if not (FieldFilters.TryGetValue(ODataFieldName, FilterCollectionNode) or FieldTypes.Get(ODataFieldName, EdmType)) then Error(FilterDoesNotExistErr, ODataFieldName); EditinExcelFldFilterImpl.Initialize(ODataFieldName, EdmType, FilterCollectionNode, EditinExcelFldFilterImpl); exit(EditinExcelFldFilterImpl); end; procedure Remove(ODataFieldName: Text): Boolean begin exit(FieldFilters.Remove(ODataFieldName) and FieldTypes.Remove(ODataFieldName)) end; procedure Contains(ODataFieldName: Text): Boolean begin exit(FieldFilters.ContainsKey(ODataFieldName) and FieldTypes.ContainsKey(ODataFieldName)) end; internal procedure ConvertFromJsonFilters(JsonFilter: JsonObject; JsonPayload: JsonObject; PageId: Integer; FilterErrors: Dictionary of [Text, Boolean]) var FieldFilterGroupingOperator: Dictionary of [Text, Enum "Edit in Excel Filter Collection Type"]; begin ConvertStructuredFiltersToEntityFilterCollection(JsonFilter, JsonPayload, FieldFilterGroupingOperator, "Edit in Excel Filter Collection Type"::"and", PageId, FilterErrors); end; internal procedure GetFilters(var SpecifiedFieldFilters: DotNet GenericDictionary2) begin SpecifiedFieldFilters := FieldFilters; end; procedure ConvertStructuredFiltersToEntityFilterCollection(StructuredFilterObject: JsonObject; ODataJsonPayload: JsonObject; var FieldFilterGroupingOperator: Dictionary of [Text, Enum "Edit in Excel Filter Collection Type"]; ParentGroupingOperator: Enum "Edit in Excel Filter Collection Type"; PageId: Integer; FilterErrors: Dictionary of [Text, Boolean]) var ChildNodesJsonFilterArray: JsonArray; TypeJsonToken: JsonToken; ChildNodesJsonToken: JsonToken; ExcelFilterNodeType: Enum "Excel Filter Node Type"; FieldGroupingOperator: Enum "Edit in Excel Filter Collection Type"; EditinExcelFilterType: Enum "Edit in Excel Filter Type"; FilterValue: Text; NodeType: Text; ODataFieldName: Text; EdmType: Text; begin if not StructuredFilterObject.Get(TypeJsonTok, TypeJsonToken) then begin Session.LogMessage('0000I3U', StrSubstNo(TypeNotFoundTxt, TypeJsonTok), Verbosity::Error, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit; end; NodeType := TypeJsonToken.AsValue().AsText().ToLower(); if not Enum::"Excel Filter Node Type".Names().Contains(NodeType) then begin Session.LogMessage('0000I3V', StrSubstNo(NodeTypeNotRecognizedTxt, NodeType), Verbosity::Error, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit; end; ExcelFilterNodeType := "Excel Filter Node Type".FromInteger("Excel Filter Node Type".Ordinals().Get("Excel Filter Node Type".Names().IndexOf(NodeType))); case ExcelFilterNodeType of "Excel Filter Node Type"::"and", "Excel Filter Node Type"::"or": begin if not StructuredFilterObject.Get(ChildNodesJsonTok, ChildNodesJsonToken) then begin Session.LogMessage('0000I3W', StrSubstNo(TypeNotFoundTxt, ChildNodesJsonTok), Verbosity::Error, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit; end; ChildNodesJsonFilterArray := ChildNodesJsonToken.AsArray(); foreach ChildNodesJsonToken in ChildNodesJsonFilterArray do ConvertStructuredFiltersToEntityFilterCollection( ChildNodesJsonToken.AsObject(), ODataJsonPayload, FieldFilterGroupingOperator, "Edit in Excel Filter Collection Type".FromInteger(ExcelFilterNodeType.AsInteger()), PageId, FilterErrors); end; "Excel Filter Node Type"::lt, "Excel Filter Node Type"::le, "Excel Filter Node Type"::eq, "Excel Filter Node Type"::ge, "Excel Filter Node Type"::gt, "Excel Filter Node Type"::ne: begin if not ReadNodeValues(StructuredFilterObject, ODataJsonPayload, ODataFieldName, EdmType, FilterValue, PageId, FilterErrors) then exit; EditinExcelFilterType := "Edit in Excel Filter Type".FromInteger(ExcelFilterNodeType.AsInteger()); if not FieldFilterGroupingOperator.Get(ODataFieldName, FieldGroupingOperator) then begin FieldFilterGroupingOperator.Add(ODataFieldName, ParentGroupingOperator); TryAdd(ODataFieldName, "Edit in Excel Filter Collection Type".FromInteger(ParentGroupingOperator.AsInteger()), EdmType); end else if FieldGroupingOperator <> ParentGroupingOperator then begin Session.LogMessage('0000I3X', FilterContainsMultipleOperatorsTxt, Verbosity::Warning, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit; // OData does not support filtering on a field with both 'and' and 'or' hence if we see both, ignore the second type end; Get(ODataFieldName).AddFilterValueV2(EditinExcelFilterType, FilterValue); end; end; end; local procedure ReadNodeValues(JsonFilterObject: JsonObject; ODataJsonPayload: JsonObject; var ODataFieldName: Text; var EdmType: Text; var FilterValue: Text; PageNumber: Integer; FilterErrors: Dictionary of [Text, Boolean]): Boolean var PageControlField: Record "Page Control Field"; LeftNodeJsonToken: JsonToken; RightNodeJsonToken: JsonToken; NameJsonToken: JsonToken; TypeJsonToken: JsonToken; AlNameJsonToken: JsonToken; ExternalTableFieldName: Text; FieldAlName: Text; begin if not JsonFilterObject.Get(LeftNodeJsonTok, LeftNodeJsonToken) then begin Session.LogMessage('0000I3Y', StrSubstNo(TypeNotFoundTxt, LeftNodeJsonTok), Verbosity::Error, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit(false); end; // Get ODataFieldName if not LeftNodeJsonToken.AsObject().Get(NameJsonTok, NameJsonToken) then begin Session.LogMessage('0000I3Z', StrSubstNo(TypeNotFoundTxt, NameJsonTok), Verbosity::Error, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit(false); end; ExternalTableFieldName := NameJsonToken.AsValue().AsText(); if not ODataJsonPayload.SelectToken(StrSubstNo(FieldPayloadAlNameTok, ExternalTableFieldName), AlNameJsonToken) then begin Session.LogMessage('0000I5T', StrSubstNo(TypeNotFoundTxt, ExternalTableFieldName), Verbosity::Error, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit(false); end; FieldAlName := AlNameJsonToken.AsValue().AsText(); PageControlField.SetRange(PageNo, PageNumber); PageControlField.SetRange(ControlName, FieldAlName); if PageControlField.IsEmpty() then if not IsKey(PageNumber, ExternalTableFieldName) then begin Session.LogMessage('0000I5U', FieldNotOnThePageTxt, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); if not FilterErrors.ContainsKey(FieldAlName) then FilterErrors.Add(FieldAlName, true); exit(false); end; ODataFieldName := NameJsonToken.AsValue().AsText(); // Get EdmType if not ODataJsonPayload.SelectToken(StrSubstNo(FieldPayloadEdmTypeTok, ODataFieldName), TypeJsonToken) then begin Session.LogMessage('0000I40', StrSubstNo(TypeNotFoundTxt, LeftNodeJsonTok), Verbosity::Error, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit(false); end; EdmType := TypeJsonToken.AsValue().AsText(); // Get FilterValue if not JsonFilterObject.Get(RightNodeJsonTok, RightNodeJsonToken) then begin Session.LogMessage('0000I41', StrSubstNo(TypeNotFoundTxt, RightNodeJsonTok), Verbosity::Error, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit(false); end; if not RightNodeJsonToken.AsObject().Get(ValueJsonTok, NameJsonToken) then begin Session.LogMessage('0000I42', StrSubstNo(TypeNotFoundTxt, ValueJsonTok), Verbosity::Error, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', EditInExcelTelemetryCategoryTxt); exit(false); end; FilterValue := NameJsonToken.AsValue().AsText(); exit(true); end; local procedure IsKey(PageNumber: Integer; ExternalTableFieldName: Text): Boolean var FieldMetadata: Record "Field"; PageMetadata: Record "Page Metadata"; EditInExcelImpl: codeunit "Edit in Excel Impl."; ExternalizedFieldName: Text; begin if PageMetadata.Get(PageNumber) then begin FieldMetadata.SetRange(TableNo, PageMetadata.SourceTable); if FieldMetadata.FindSet() then repeat ExternalizedFieldName := EditInExcelImpl.ExternalizeODataObjectName(FieldMetadata.FieldName); if ExternalizedFieldName = ExternalTableFieldName then if FieldMetadata.IsPartOfPrimaryKey then exit(true); until FieldMetadata.Next() = 0; end; exit(false); end; local procedure TryAdd(ODataFieldName: Text; EditInExcelFilterOperatorType: Enum "Edit in Excel Filter Collection Type"; EdmType: Text) var FilterCollectionNode: DotNet FilterCollectionNode; begin if IsNull(FieldFilters) then FieldFilters := FieldFilters.Dictionary(); if FieldFilters.ContainsKey(ODataFieldName) then Error(FilterAlreadyExistErr, ODataFieldName); FilterCollectionNode := FilterCollectionNode.FilterCollectionNode(); FilterCollectionNode.Operator := Format(EditInExcelFilterOperatorType); FieldFilters.Add(ODataFieldName, FilterCollectionNode); FieldTypes.Add(ODataFieldName, EdmType); end;}