Codeunit 8610 Questionnaire Management
src/Layers/W1/BaseApp/System/RapidStart/QuestionnaireManagement.Codeunit.al892 lines, Copyright (c) Microsoft Corporation. MIT
namespace System.IO;using System;using System.Reflection;using System.Utilities;using System.Xml;codeunit 8610 "Questionnaire Management"{ trigger OnRun() begin end; var#pragma warning disable AA0470 KeyFieldMissingErr: Label 'The value of the key field %1 has not been filled in for questionnaire %2.';#pragma warning restore AA0470 OpenXMLManagement: Codeunit "OpenXML Management"; XMLDOMMgt: Codeunit "XML DOM Management"; ConfigPackageMgt: Codeunit "Config. Package Management"; ConfigProgressBar: Codeunit "Config. Progress Bar"; ConfigValidateMgt: Codeunit "Config. Validate Management"; FileMgt: Codeunit "File Management"; ExportingQuestionnaireTxt: Label 'Exporting questionnaire'; ImportingQuestionnaireTxt: Label 'Importing questionnaire'; XMLSchemaCreationErr: Label 'Could not create the XML schema.'; ApplyingAnswersTxt: Label 'Applying answers'; UpdatingQuestionnaireTxt: Label 'Updating questionnaire'; TypeHelper: Codeunit "Type Helper"; WrkBkWriter: DotNet WorkbookWriter; FieldNameCaptionList: Text; ExportToExcel: Boolean; CreatingExcelWorksheetTxt: Label 'Creating Excel worksheet'; DownloadTxt: Label 'Download'; AllFilesTxt: Label '*.*|*.*'; DefaultTxt: Label 'Default'; CalledFromCode: Boolean; ImportFileTxt: Label 'Import File'; XMLFileFilterTxt: Label 'XML file (*.xml)|*.xml', Comment = 'Only translate ''XML Files'' {Split=r"[\|\(]\*\.[^ |)]*[|) ]?"}'; CreateWrkBkFailedErr: Label 'Could not create the Excel workbook.'; procedure UpdateQuestions(ConfigQuestionArea: Record "Config. Question Area") var ConfigQuestion: Record "Config. Question"; "Field": Record "Field"; NextQuestionNo: Integer; begin if ConfigQuestionArea."Table ID" = 0 then exit; ConfigQuestion.SetRange("Questionnaire Code", ConfigQuestionArea."Questionnaire Code"); ConfigQuestion.SetRange("Question Area Code", ConfigQuestionArea.Code); if ConfigQuestion.FindLast() then NextQuestionNo := ConfigQuestion."No." + 1 else NextQuestionNo := 1; ConfigPackageMgt.SetFieldFilter(Field, ConfigQuestionArea."Table ID", 0); if Field.FindSet() then repeat ConfigQuestion.Init(); ConfigQuestion."Questionnaire Code" := ConfigQuestionArea."Questionnaire Code"; ConfigQuestion."Question Area Code" := ConfigQuestionArea.Code; ConfigQuestion."No." := NextQuestionNo; ConfigQuestion."Table ID" := ConfigQuestionArea."Table ID"; ConfigQuestion."Field ID" := Field."No."; if not QuestionExist(ConfigQuestion) then begin UpdateQuestion(ConfigQuestion); ConfigQuestion."Answer Option" := BuildAnswerOption(ConfigQuestionArea."Table ID", Field."No."); ConfigQuestion.Insert(); NextQuestionNo := NextQuestionNo + 1; end; until Field.Next() = 0; end; local procedure UpdateQuestion(var ConfigQuestion: Record "Config. Question") var RecRef: RecordRef; FieldRef: FieldRef; begin if ConfigQuestion.Question <> '' then exit; if ConfigQuestion."Table ID" = 0 then exit; RecRef.Open(ConfigQuestion."Table ID"); FieldRef := RecRef.Field(ConfigQuestion."Field ID"); ConfigQuestion.Question := FieldRef.Caption + '?'; end; procedure UpdateQuestionnaire(ConfigQuestionnaire: Record "Config. Questionnaire"): Boolean var ConfigQuestionArea: Record "Config. Question Area"; begin if ConfigQuestionnaire.Code = '' then exit; ConfigQuestionArea.Reset(); ConfigQuestionArea.SetRange("Questionnaire Code", ConfigQuestionnaire.Code); if ConfigQuestionArea.FindSet() then begin ConfigProgressBar.Init(ConfigQuestionArea.Count, 1, UpdatingQuestionnaireTxt); repeat ConfigProgressBar.Update(ConfigQuestionArea.Code); UpdateQuestions(ConfigQuestionArea); until ConfigQuestionArea.Next() = 0; ConfigProgressBar.Close(); exit(true); end; exit(false); end; local procedure QuestionExist(ConfigQuestion: Record "Config. Question"): Boolean var ConfigQuestion2: Record "Config. Question"; begin ConfigQuestion2.Reset(); ConfigQuestion2.SetCurrentKey("Questionnaire Code", "Question Area Code", "Field ID"); ConfigQuestion2.SetRange("Questionnaire Code", ConfigQuestion."Questionnaire Code"); ConfigQuestion2.SetRange("Question Area Code", ConfigQuestion."Question Area Code"); ConfigQuestion2.SetRange("Field ID", ConfigQuestion."Field ID"); exit(not ConfigQuestion2.IsEmpty); end; procedure BuildAnswerOption(TableID: Integer; FieldID: Integer): Text[250] var "Field": Record "Field"; RecRef: RecordRef; FieldRef: FieldRef; BooleanText: Text[30]; begin if not TypeHelper.GetField(TableID, FieldID, Field) then exit; case Field.Type of Field.Type::Option: begin RecRef.Open(Field.TableNo); FieldRef := RecRef.Field(Field."No."); exit(FieldRef.OptionCaption); end; Field.Type::Boolean: begin BooleanText := Format(true) + ',' + Format(false); exit(BooleanText) end; else exit(Format(Field.Type)); end; end; procedure ApplyAnswers(ConfigQuestionnaire: Record "Config. Questionnaire"): Boolean var ConfigQuestionArea: Record "Config. Question Area"; begin ConfigQuestionArea.Reset(); ConfigQuestionArea.SetRange("Questionnaire Code", ConfigQuestionnaire.Code); if ConfigQuestionArea.FindSet() then begin ConfigProgressBar.Init(ConfigQuestionArea.Count, 1, ApplyingAnswersTxt); repeat ConfigProgressBar.Update(ConfigQuestionArea.Code); ApplyAnswer(ConfigQuestionArea); until ConfigQuestionArea.Next() = 0; ConfigProgressBar.Close(); exit(true); end; exit(false); end; procedure ApplyAnswer(ConfigQuestionArea: Record "Config. Question Area") var RecRef: RecordRef; begin if ConfigQuestionArea."Table ID" = 0 then exit; RecRef.Open(ConfigQuestionArea."Table ID"); RecRef.Init(); InsertRecordWithKeyFields(RecRef, ConfigQuestionArea); ModifyRecordWithOtherFields(RecRef, ConfigQuestionArea); end; local procedure InsertRecordWithKeyFields(var RecRef: RecordRef; ConfigQuestionArea: Record "Config. Question Area") var ConfigQuestion: Record "Config. Question"; RecRef1: RecordRef; KeyRef: KeyRef; FieldRef: FieldRef; KeyFieldCount: Integer; begin ConfigQuestion.SetRange("Questionnaire Code", ConfigQuestionArea."Questionnaire Code"); ConfigQuestion.SetRange("Question Area Code", ConfigQuestionArea.Code); KeyRef := RecRef.KeyIndex(1); for KeyFieldCount := 1 to KeyRef.FieldCount do begin FieldRef := KeyRef.FieldIndex(KeyFieldCount); ConfigQuestion.SetRange("Field ID", FieldRef.Number); if ConfigQuestion.FindFirst() then ConfigValidateMgt.ValidateFieldValue(RecRef, FieldRef, ConfigQuestion.Answer, false, GlobalLanguage) else if KeyRef.FieldCount <> 1 then Error(KeyFieldMissingErr, FieldRef.Name, ConfigQuestionArea.Code); end; RecRef1 := RecRef.Duplicate(); if RecRef1.Find() then begin RecRef := RecRef1; exit end; RecRef.Insert(true); end; local procedure ModifyRecordWithOtherFields(var RecRef: RecordRef; ConfigQuestionArea: Record "Config. Question Area") var ConfigQuestion: Record "Config. Question"; TempConfigPackageField: Record "Config. Package Field" temporary; ConfigPackageManagement: Codeunit "Config. Package Management"; FieldRef: FieldRef; ErrorText: Text[250]; begin ConfigQuestion.SetRange("Questionnaire Code", ConfigQuestionArea."Questionnaire Code"); ConfigQuestion.SetRange("Question Area Code", ConfigQuestionArea.Code); if ConfigQuestion.FindSet() then repeat TempConfigPackageField.DeleteAll(); if ConfigQuestion.Answer <> '' then begin FieldRef := RecRef.Field(ConfigQuestion."Field ID"); ConfigValidateMgt.ValidateFieldValue(RecRef, FieldRef, ConfigQuestion.Answer, false, GlobalLanguage); ConfigPackageManagement.GetFieldsOrder(RecRef, '', TempConfigPackageField); ErrorText := ConfigPackageManagement.ValidateFieldRefRelationAgainstCompanyData(FieldRef, TempConfigPackageField); if ErrorText <> '' then Error(ErrorText); end; until ConfigQuestion.Next() = 0; RecRef.Modify(true); end; [Scope('OnPrem')] procedure ExportQuestionnaireAsXML(XMLDataFile: Text; var ConfigQuestionnaire: Record "Config. Questionnaire"): Boolean var QuestionnaireXML: DotNet XmlDocument; ToFile: Text[1024]; FileName: Text; Exported: Boolean; begin QuestionnaireXML := QuestionnaireXML.XmlDocument(); GenerateQuestionnaireXMLDocument(QuestionnaireXML, ConfigQuestionnaire); Exported := true; if not ExportToExcel then begin FileName := XMLDataFile; ToFile := DefaultTxt + '.xml'; if not CalledFromCode then FileName := FileMgt.ServerTempFileName('.xml'); QuestionnaireXML.Save(FileName); if not CalledFromCode then Exported := FileMgt.DownloadHandler(FileName, DownloadTxt, '', AllFilesTxt, ToFile); end else begin FileName := XMLDataFile; QuestionnaireXML.Save(FileName); end; exit(Exported); end; [Scope('OnPrem')] procedure GenerateQuestionnaireXMLDocument(QuestionnaireXML: DotNet XmlDocument; var ConfigQuestionnaire: Record "Config. Questionnaire") var ConfigQuestionArea: Record "Config. Question Area"; RecRef: RecordRef; DocumentNode: DotNet XmlNode; begin XMLDOMMgt.LoadXMLDocumentFromText( '<?xml version="1.0" encoding="UTF-16" standalone="yes"?><Questionnaire></Questionnaire>', QuestionnaireXML); DocumentNode := QuestionnaireXML.DocumentElement; RecRef.GetTable(ConfigQuestionnaire); CreateFieldSubtree(RecRef, DocumentNode); ConfigQuestionArea.SetRange("Questionnaire Code", ConfigQuestionnaire.Code); if ConfigQuestionArea.FindSet() then begin ConfigProgressBar.Init(ConfigQuestionArea.Count, 1, ExportingQuestionnaireTxt); repeat ConfigProgressBar.Update(ConfigQuestionArea.Code); CreateQuestionNodes(QuestionnaireXML, ConfigQuestionArea); until ConfigQuestionArea.Next() = 0; ConfigProgressBar.Close(); end; end; [Scope('OnPrem')] procedure ImportQuestionnaireAsXMLFromClient(): Boolean var ServerFileName: Text; begin ServerFileName := FileMgt.ServerTempFileName('.xml'); if Upload(ImportFileTxt, '', XMLFileFilterTxt, '', ServerFileName) then exit(ImportQuestionnaireAsXML(ServerFileName)); exit(false); end; [Scope('OnPrem')] procedure ImportQuestionnaireAsXML(XMLDataFile: Text): Boolean var QuestionnaireXML: DotNet XmlDocument; begin XMLDOMMgt.LoadXMLDocumentFromFile(XMLDataFile, QuestionnaireXML); exit(ImportQuestionnaireXMLDocument(QuestionnaireXML)); end; [Scope('OnPrem')] procedure ImportQuestionnaireXMLDocument(QuestionnaireXML: DotNet XmlDocument): Boolean var ConfigQuestionnaire: Record "Config. Questionnaire"; ConfigQuestionArea: Record "Config. Question Area"; ConfigQuestion: Record "Config. Question"; QuestionAreaNodes: DotNet XmlNodeList; QuestionAreaNode: DotNet XmlNode; QuestionNodes: DotNet XmlNodeList; QuestionnaireNode: DotNet XmlNode; AreaNodeCount: Integer; NodeCount: Integer; begin QuestionnaireNode := QuestionnaireXML.SelectSingleNode('//Questionnaire'); UpdateInsertQuestionnaireField(ConfigQuestionnaire, QuestionnaireNode); QuestionAreaNodes := QuestionnaireNode.SelectNodes('child::*[position() >= 3]'); ConfigProgressBar.Init(QuestionAreaNodes.Count, 1, ImportingQuestionnaireTxt); for AreaNodeCount := 0 to QuestionAreaNodes.Count - 1 do begin QuestionAreaNode := QuestionAreaNodes.Item(AreaNodeCount); ConfigProgressBar.Update(GetNodeValue(QuestionAreaNode, 'Code')); ConfigQuestionArea."Questionnaire Code" := ConfigQuestionnaire.Code; UpdateInsertQuestionAreaFields(ConfigQuestionArea, QuestionAreaNode); QuestionNodes := QuestionAreaNode.SelectNodes('ConfigQuestion'); for NodeCount := 0 to QuestionNodes.Count - 1 do begin ConfigQuestion.Init(); ConfigQuestion."Questionnaire Code" := ConfigQuestionArea."Questionnaire Code"; ConfigQuestion."Question Area Code" := ConfigQuestionArea.Code; ConfigQuestion."Table ID" := ConfigQuestionArea."Table ID"; UpdateInsertQuestionFields(ConfigQuestion, QuestionNodes.Item(NodeCount)) end; end; ConfigProgressBar.Close(); exit(true); end; [Scope('OnPrem')] procedure ExportQuestionnaireToExcel(ExcelFile: Text; var ConfigQuestionnaire: Record "Config. Questionnaire"): Boolean var TempBlob: Codeunit "Temp Blob"; ColumnNodes: DotNet XmlNodeList; MapXML: DotNet XmlDocument; NamespaceMgr: DotNet XmlNamespaceManager; QuestionnaireXML: DotNet XmlDocument; QuestionAreaNodes: DotNet XmlNodeList; QuestionAreaNode: DotNet XmlNode; QuestionNodes: DotNet XmlNodeList; QuestionnaireNode: DotNet XmlNode; "Table": DotNet Table; WorksheetWriter: DotNet WorksheetWriter; RootElementName: Text; TempConfigQuestionnaireFileName: Text; TempSchemaFileName: Text; begin CreateFieldNameCaptionList(DATABASE::"Config. Question"); CreateEmptyBook(TempBlob); TempSchemaFileName := CreateSchemaFile(ConfigQuestionnaire, RootElementName); OpenXMLManagement.ImportSchema(WrkBkWriter, TempSchemaFileName, 1, RootElementName); OpenXMLManagement.CleanMapInfo(WrkBkWriter.Workbook.WorkbookPart.CustomXmlMappingsPart.MapInfo); TempConfigQuestionnaireFileName := CreateConfigQuestionnaireXMLFile(ConfigQuestionnaire); OpenXMLManagement.CreateSchemaConnection(WrkBkWriter, TempConfigQuestionnaireFileName); OpenXMLManagement.CreateTableStyles(WrkBkWriter.Workbook); ReadXSDSchema(TempSchemaFileName, MapXML, NamespaceMgr); XMLDOMMgt.LoadXMLDocumentFromFile(TempConfigQuestionnaireFileName, QuestionnaireXML); QuestionnaireNode := QuestionnaireXML.SelectSingleNode('//Questionnaire'); QuestionAreaNodes := QuestionnaireNode.SelectNodes('child::*[position() >= 3]'); ConfigProgressBar.Init(QuestionAreaNodes.Count, 1, CreatingExcelWorksheetTxt); foreach QuestionAreaNode in QuestionAreaNodes do begin ConfigProgressBar.Update(QuestionAreaNode.Name); FillQuestionAreaHeader(WorksheetWriter, QuestionAreaNode); QuestionNodes := QuestionAreaNode.SelectNodes('ConfigQuestion'); if not IsNull(QuestionNodes) then begin GetColumnsFromSchema(MapXML, NamespaceMgr, QuestionAreaNode.Name, ColumnNodes); OpenXMLManagement.AddTable(WorksheetWriter, 2, ColumnNodes.Count, QuestionNodes.Count, Table); AddColumns(WorksheetWriter, Table, ColumnNodes, QuestionNodes); WriteData(WorksheetWriter, ColumnNodes, QuestionNodes); end; end; FillQuestionnaireHeader(WorksheetWriter, QuestionnaireNode); WrkBkWriter.Workbook.Save(); WrkBkWriter.Close(); Clear(WrkBkWriter); ConfigProgressBar.Close(); if ExcelFile = '' then ExcelFile := ConfigQuestionnaire.Code; if FileMgt.GetExtension(ExcelFile) = '' then ExcelFile += '.xlsx'; FileMgt.BLOBExport(TempBlob, ExcelFile, true); FILE.Erase(TempSchemaFileName); FILE.Erase(TempConfigQuestionnaireFileName); exit(true); end; local procedure CreateQuestionNodes(QuestionnaireXML: DotNet XmlDocument; ConfigQuestionArea: Record "Config. Question Area") var ConfigQuestion: Record "Config. Question"; DocumentElement: DotNet XmlElement; QuestionAreaNode: DotNet XmlNode; QuestionNode: DotNet XmlNode; RecRef: RecordRef; QuestionRecRef: RecordRef; begin DocumentElement := QuestionnaireXML.DocumentElement; QuestionAreaNode := QuestionnaireXML.CreateElement(GetElementName(ConfigQuestionArea.Code + 'Questions')); DocumentElement.AppendChild(QuestionAreaNode); RecRef.GetTable(ConfigQuestionArea); CreateFieldSubtree(RecRef, QuestionAreaNode); ConfigQuestion.SetRange("Questionnaire Code", ConfigQuestionArea."Questionnaire Code"); ConfigQuestion.SetRange("Question Area Code", ConfigQuestionArea.Code); if ConfigQuestion.FindSet() then repeat QuestionNode := QuestionnaireXML.CreateElement(GetElementName(ConfigQuestion.TableName)); QuestionAreaNode.AppendChild(QuestionNode); QuestionRecRef.GetTable(ConfigQuestion); CreateFieldSubtree(QuestionRecRef, QuestionNode); until ConfigQuestion.Next() = 0; end; procedure GetElementName(NameIn: Text): Text begin NameIn := DelChr(NameIn, '=', '?''`'); NameIn := ConvertStr(NameIn, '<>,./\+&()%:', ' '); NameIn := ConvertStr(NameIn, '-', '_'); NameIn := DelChr(NameIn, '=', ' '); exit(NameIn); end; local procedure CreateFieldSubtree(var RecRef: RecordRef; var Node: DotNet XmlElement) var FieldRef: FieldRef; FieldNode: DotNet XmlNode; XmlDom: DotNet XmlDocument; i: Integer; begin XmlDom := Node.OwnerDocument; for i := 1 to RecRef.FieldCount do begin FieldRef := RecRef.FieldIndex(i); if not FieldException(RecRef.Number, FieldRef.Number) then begin FieldNode := XmlDom.CreateElement(GetElementName(FieldRef.Name)); if FieldRef.Class = FieldClass::FlowField then FieldRef.CalcField(); FieldNode.InnerText := Format(FieldRef.Value); XMLDOMMgt.AddAttribute(FieldNode, 'fieldlength', Format(FieldRef.Length)); Node.AppendChild(FieldNode); end; end; end; local procedure CreateFieldNameCaptionList(TableID: Integer) var FieldRef: FieldRef; RecRef: RecordRef; i: Integer; begin FieldNameCaptionList := ''; RecRef.Open(TableID); for i := 1 to RecRef.FieldCount do begin FieldRef := RecRef.FieldIndex(i); FieldNameCaptionList += StrSubstNo('[%1]%2;', GetElementName(FieldRef.Name), FieldRef.Caption); end; RecRef.Close(); end; local procedure GetCaptionByXMLFieldName(XMLFieldName: Text) Caption: Text var Pos: Integer; begin Pos := StrPos(FieldNameCaptionList, StrSubstNo('[%1]', XMLFieldName)); if Pos = 0 then exit(XMLFieldName); Caption := CopyStr(FieldNameCaptionList, Pos + StrLen(XMLFieldName) + 2); Caption := CopyStr(Caption, 1, StrPos(Caption, ';') - 1); end; local procedure FindNode(var ParentNode: DotNet XmlNode; ChildNodeName: Text; var ChildNode: DotNet XmlNode): Boolean begin ChildNode := ParentNode.SelectSingleNode(ChildNodeName); exit(not IsNull(ChildNode)); end; local procedure GetNodeValue(var RecordNode: DotNet XmlNode; FieldNodeName: Text): Text var FieldNode: DotNet XmlNode; begin FieldNode := RecordNode.SelectSingleNode(FieldNodeName); exit(FieldNode.InnerText); end; local procedure GetXMLNodeValue(var RecordNode: DotNet XmlNode; NodeName: Text; var xPath: Text): Text var FieldNode: DotNet XmlNode; begin if FindNode(RecordNode, GetElementName(NodeName), FieldNode) then begin xPath := GetXPath(FieldNode); exit(FieldNode.InnerText); end; end; local procedure GetXPath(var XMLNode: DotNet XmlNode): Text var ParentXMLNode: DotNet XmlNode; begin if IsNull(XMLNode.ParentNode) then exit(''); ParentXMLNode := XMLNode.ParentNode; exit(GetXPath(ParentXMLNode) + '/' + XMLNode.Name); end; local procedure UpdateInsertQuestionnaireField(var ConfigQuestionnaire: Record "Config. Questionnaire"; RecordNode: DotNet XmlNode) var RecRef: RecordRef; begin RecRef.Open(DATABASE::"Config. Questionnaire"); ValidateRecordFields(RecRef, RecordNode); RecRef.SetTable(ConfigQuestionnaire); end; local procedure UpdateInsertQuestionAreaFields(var ConfigQuestionArea: Record "Config. Question Area"; RecordNode: DotNet XmlNode) var RecRef: RecordRef; begin RecRef.GetTable(ConfigQuestionArea); ValidateRecordFields(RecRef, RecordNode); RecRef.SetTable(ConfigQuestionArea); end; local procedure UpdateInsertQuestionFields(var ConfigQuestion: Record "Config. Question"; RecordNode: DotNet XmlNode) var "Field": Record "Field"; RecRef: RecordRef; begin RecRef.GetTable(ConfigQuestion); ValidateRecordFields(RecRef, RecordNode); RecRef.SetTable(ConfigQuestion); if TypeHelper.GetField(ConfigQuestion."Table ID", ConfigQuestion."Field ID", Field) then ModifyConfigQuestionAnswer(ConfigQuestion, Field); end; local procedure FieldNodeExists(var RecordNode: DotNet XmlNode; FieldNodeName: Text): Boolean var FieldNode: DotNet XmlNode; begin FieldNode := RecordNode.SelectSingleNode(FieldNodeName); if not IsNull(FieldNode) then exit(true); end; local procedure GetXLColumnID(ColumnNo: Integer): Text[10] var ExcelBuf: Record "Excel Buffer"; begin ExcelBuf.Init(); ExcelBuf.Validate("Column No.", ColumnNo); exit(ExcelBuf.xlColID); end; local procedure FieldException(TableID: Integer; FieldID: Integer): Boolean var ConfigQuestionArea: Record "Config. Question Area"; ConfigQuestion: Record "Config. Question"; begin case TableID of DATABASE::"Config. Questionnaire": exit(false); DATABASE::"Config. Question Area": exit(FieldID in [ConfigQuestionArea.FieldNo("Questionnaire Code"), ConfigQuestionArea.FieldNo("Table Name")]); DATABASE::"Config. Question": exit(FieldID in [ConfigQuestion.FieldNo("Questionnaire Code"), ConfigQuestion.FieldNo("Question Area Code"), ConfigQuestion.FieldNo("Table ID")]); end; end; procedure SetCalledFromCode() begin CalledFromCode := true; end; local procedure ValidateKeyFields(RecRef: RecordRef; RecordNode: DotNet XmlNode) var KeyRef: KeyRef; FieldRef: FieldRef; KeyFieldCount: Integer; begin KeyRef := RecRef.KeyIndex(1); for KeyFieldCount := 1 to KeyRef.FieldCount do begin FieldRef := KeyRef.FieldIndex(KeyFieldCount); if FieldNodeExists(RecordNode, GetElementName(FieldRef.Name)) then ConfigValidateMgt.ValidateFieldValue( RecRef, FieldRef, GetNodeValue(RecordNode, GetElementName(FieldRef.Name)), false, GlobalLanguage); end; end; local procedure ValidateFields(RecRef: RecordRef; RecordNode: DotNet XmlNode) var "Field": Record "Field"; FieldRef: FieldRef; begin ConfigPackageMgt.SetFieldFilter(Field, RecRef.Number, 0); if Field.FindSet() then repeat FieldRef := RecRef.Field(Field."No."); if FieldNodeExists(RecordNode, GetElementName(FieldRef.Name)) then ConfigValidateMgt.ValidateFieldValue( RecRef, FieldRef, GetNodeValue(RecordNode, GetElementName(FieldRef.Name)), false, GlobalLanguage) until Field.Next() = 0; end; local procedure ValidateRecordFields(RecRef: RecordRef; RecordNode: DotNet XmlNode) var RecRef1: RecordRef; begin ValidateKeyFields(RecRef, RecordNode); RecRef1 := RecRef.Duplicate(); if not RecRef1.Find() then RecRef.Insert(true); ValidateFields(RecRef, RecordNode); RecRef.Modify(true); end; procedure ModifyConfigQuestionAnswer(var ConfigQuestion: Record "Config. Question"; FieldRec: Record "Field") var DateFormula: DateFormula; OptionInt: Integer; begin case FieldRec.Type of FieldRec.Type::Option, FieldRec.Type::Boolean: begin if ConfigQuestion.Answer <> '' then begin OptionInt := TypeHelper.GetOptionNo(ConfigQuestion.Answer, ConfigQuestion."Answer Option"); ConfigQuestion."Answer Option" := BuildAnswerOption(ConfigQuestion."Table ID", ConfigQuestion."Field ID"); if OptionInt <> -1 then ConfigQuestion.Answer := SelectStr(OptionInt + 1, ConfigQuestion."Answer Option"); end else begin ConfigQuestion.Answer := ''; ConfigQuestion."Answer Option" := BuildAnswerOption(ConfigQuestion."Table ID", ConfigQuestion."Field ID"); end; ConfigQuestion.Modify(); end; FieldRec.Type::DateFormula: begin Evaluate(DateFormula, ConfigQuestion.Answer); ConfigQuestion.Answer := Format(DateFormula); ConfigQuestion.Modify(); end; end; end; local procedure CreateSchemaFile(ConfigQuestionnaire: Record "Config. Questionnaire"; var RootElementName: Text) FileName: Text var ConfigQuestionnaireSchema: XMLport "Config. Questionnaire Schema"; OStream: OutStream; TempSchemaFile: File; begin FileName := FileMgt.ServerTempFileName('xsd'); TempSchemaFile.Create(FileName); TempSchemaFile.CreateOutStream(OStream); ConfigQuestionnaire.SetRecFilter(); RootElementName := ConfigQuestionnaireSchema.GetRootElementName(); ConfigQuestionnaireSchema.SetTableView(ConfigQuestionnaire); ConfigQuestionnaireSchema.SetDestination(OStream); if not ConfigQuestionnaireSchema.Export() then Error(XMLSchemaCreationErr); TempSchemaFile.Close(); end; local procedure CreateConfigQuestionnaireXMLFile(ConfigQuestionnaire: Record "Config. Questionnaire") FileName: Text begin ExportToExcel := true; CalledFromCode := true; FileName := FileMgt.ServerTempFileName('xml'); ExportQuestionnaireAsXML(FileName, ConfigQuestionnaire); ExportToExcel := false; end; local procedure CreateEmptyBook(var TempBlob: Codeunit "Temp Blob") var InStream: InStream; begin TempBlob.CreateInStream(InStream); WrkBkWriter := WrkBkWriter.Create(InStream); if IsNull(WrkBkWriter) then Error(CreateWrkBkFailedErr); WrkBkWriter.DeleteWorksheet(WrkBkWriter.FirstWorksheet.Name); end; local procedure WriteData(var WorksheetWriter: DotNet WorksheetWriter; ColumnNodes: DotNet XmlNodeList; QuestionNodes: DotNet XmlNodeList) var ColumnNode: DotNet XmlNode; QuestionNode: DotNet XmlNode; ColumnNo: Integer; RowNo: Integer; Value: Text; begin RowNo := 2; // to put the first data row to the 3rd row foreach QuestionNode in QuestionNodes do begin RowNo += 1; ColumnNo := 0; foreach ColumnNode in ColumnNodes do begin ColumnNo += 1; Value := GetNodeValue(QuestionNode, GetAttribute('name', ColumnNode)); WorksheetWriter.SetCellValueText(RowNo, GetXLColumnID(ColumnNo), Value, WorksheetWriter.DefaultCellDecorator); end; end; end; local procedure AddColumns(var WorksheetWriter: DotNet WorksheetWriter; "Table": DotNet Table; ColumnNodes: DotNet XmlNodeList; QuestionNodes: DotNet XmlNodeList) var FieldNode: DotNet XmlNode; QuestionNode: DotNet XmlNode; ColumnName: Text; xPathPrefix: Text; FieldName: Text; FieldType: Text; ColumnId: Integer; begin QuestionNode := QuestionNodes.Item(0); xPathPrefix := GetXPath(QuestionNode) + '/'; ColumnId := 0; foreach FieldNode in ColumnNodes do begin ColumnId += 1; FieldName := GetAttribute('name', FieldNode); ColumnName := GetCaptionByXMLFieldName(FieldName); FieldType := GetAttribute('type', FieldNode); OpenXMLManagement.AddColumnHeaderWithXPath( WorksheetWriter, Table, ColumnId, ColumnName, FieldType, xPathPrefix + FieldName); WorksheetWriter.SetCellValueText(2, GetXLColumnID(ColumnId), ColumnName, WorksheetWriter.DefaultCellDecorator); end; end; local procedure FillQuestionnaireHeader(var WorksheetWriter: DotNet WorksheetWriter; QuestionnaireNode: DotNet XmlNode) var ConfigQuestionnaire: Record "Config. Questionnaire"; SingleXMLCells: DotNet SingleXmlCells; Description: array[2] of Text; XPath: array[2] of Text; begin Description[2] := GetXMLNodeValue(QuestionnaireNode, ConfigQuestionnaire.FieldName(Description), XPath[2]); if Description[2] <> '' then begin Description[1] := GetXMLNodeValue(QuestionnaireNode, ConfigQuestionnaire.FieldName(Code), XPath[1]); WorksheetWriter := WrkBkWriter.AddWorksheet(Description[2]); AddSingleXMLCells(WorksheetWriter, SingleXMLCells); OpenXMLManagement.SetSingleCellValue(WorksheetWriter, SingleXMLCells, 1, 'A', Description[1], XPath[1]); OpenXMLManagement.SetSingleCellValue(WorksheetWriter, SingleXMLCells, 1, 'B', Description[2], XPath[2]); end; end; local procedure FillQuestionAreaHeader(var WorksheetWriter: DotNet WorksheetWriter; QuestionAreaNode: DotNet XmlNode) var ConfigQuestionArea: Record "Config. Question Area"; SingleXMLCells: DotNet SingleXmlCells; Description: array[3] of Text; XPath: array[3] of Text; i: Integer; begin Description[1] := GetXMLNodeValue(QuestionAreaNode, ConfigQuestionArea.FieldName(Code), XPath[1]); Description[2] := GetXMLNodeValue(QuestionAreaNode, ConfigQuestionArea.FieldName(Description), XPath[2]); if Description[2] = '' then Description[2] := Description[1]; Description[3] := GetXMLNodeValue(QuestionAreaNode, ConfigQuestionArea.FieldName("Table ID"), XPath[3]); WorksheetWriter := WrkBkWriter.AddWorksheet(Description[2]); AddSingleXMLCells(WorksheetWriter, SingleXMLCells); for i := 1 to ArrayLen(Description) do OpenXMLManagement.SetSingleCellValue(WorksheetWriter, SingleXMLCells, 1, GetXLColumnID(i), Description[i], XPath[i]); end; local procedure AddSingleXMLCells(var WrkShtWriter: DotNet WorksheetWriter; var SingleXMLCells: DotNet SingleXmlCells) begin WrkShtWriter.AddSingleCellTablePart(); SingleXMLCells := SingleXMLCells.SingleXmlCells(); WrkShtWriter.Worksheet.WorksheetPart.SingleCellTablePart.SingleXmlCells := SingleXMLCells; end; local procedure GetAttribute(AttributeName: Text; XMLNode: DotNet XmlNode): Text[1024] var XMLAttributeNode: DotNet XmlNode; begin XMLAttributeNode := XMLNode.Attributes.GetNamedItem(AttributeName); if IsNull(XMLAttributeNode) then exit(''); exit(Format(XMLAttributeNode.InnerText)); end; local procedure ReadXSDSchema(FileName: Text; var MapXML: DotNet XmlDocument; var NamespaceMgr: DotNet XmlNamespaceManager) begin XMLDOMMgt.LoadXMLDocumentFromFile(FileName, MapXML); CreateNameSpaceManager(MapXML, NamespaceMgr); end; local procedure CreateNameSpaceManager(XmlDocument: DotNet XmlDocument; var NamespaceMgr: DotNet XmlNamespaceManager) begin if not IsNull(NamespaceMgr) then Clear(NamespaceMgr); NamespaceMgr := NamespaceMgr.XmlNamespaceManager(XmlDocument.NameTable); PopulateNamespaceManager(XmlDocument.DocumentElement, NamespaceMgr); end; local procedure PopulateNamespaceManager(XmlNode: DotNet XmlNode; var NamespaceMgr: DotNet XmlNamespaceManager) var Attribute: DotNet XmlAttribute; Attributes: DotNet XmlAttributeCollection; i: Integer; Prefix: Text; begin if not IsNull(XmlNode) then begin Attributes := XmlNode.Attributes; for i := 0 to Attributes.Count - 1 do begin Attribute := Attributes.Item(i); if StrPos(Attribute.Name, 'xmlns') = 1 then if StrPos(Attribute.Name, ':') > 0 then begin Prefix := CopyStr(Attribute.Name, StrPos(Attribute.Name, ':') + 1); NamespaceMgr.AddNamespace(Prefix, Attribute.Value); end; end; end; end; local procedure GetColumnsFromSchema(MapXML: DotNet XmlDocument; NamespaceMgr: DotNet XmlNamespaceManager; QuestionAreaName: Text; var ColumnNodes: DotNet XmlNodeList) var Node: DotNet XmlNode; SchemaPath: Text; begin SchemaPath := 'xsd:complexType/xsd:sequence/xsd:element'; Node := MapXML.DocumentElement.SelectSingleNode( StrSubstNo('xsd:element/%2[@name=''%1'']', QuestionAreaName, SchemaPath), NamespaceMgr); ColumnNodes := Node.SelectNodes(StrSubstNo('%1[@name=''ConfigQuestion'']/%1', SchemaPath), NamespaceMgr); end;}