We have many frameworks like AIF, DIXF, etc. already available out of box in dynamics AX to export or import the data, you guys might have guessing why again a new framework?
The reason is simple, I just wanted to build one simple framework, which takes a query as input and gives the XML file with all the fields in the query. It is as that simple.
In the below example, I have a created a query for sales order, with Data source as SalesTable and Salesline. I have added few fields from Sales Table and also form sales line. The query look like this.
Now I want to pass this Query and get the XML with all the fields which are ther in the query and also with the where condition (Range)
Now I have to create a FW class, which has a construc method which takes the Query as parameter as shown below.
public static server MK_ExportHelp construct(Query _query)
{
return new MK_ExportHelp(_query);
}
This method call's the new method as below
protected void new(Query _query)
{
dataQuery = _query;
}
///
/// This method is used to create a XML form the query
///
The reason is simple, I just wanted to build one simple framework, which takes a query as input and gives the XML file with all the fields in the query. It is as that simple.
In the below example, I have a created a query for sales order, with Data source as SalesTable and Salesline. I have added few fields from Sales Table and also form sales line. The query look like this.
 |
| Query |
Now I have to create a FW class, which has a construc method which takes the Query as parameter as shown below.
public static server MK_ExportHelp construct(Query _query)
{
return new MK_ExportHelp(_query);
}
This method call's the new method as below
protected void new(Query _query)
{
dataQuery = _query;
}
///
/// This method is used to create a XML form the query
///
/// File path where the xml will be stored
///
///
/// Primary field to identify unique record
///
///
/// Export helper class by Mallik on 25/07/2017
///
public void getXMLFile(Filename _filePath,FieldID _uniqueFieldID)
{
container lDSTablesCon;
container lFieldLists;
int j,k;
Query lquery;
QueryRun queryRun;
QueryBuildDataSource qbds;
TableId lTableId;
str tableName;
Common commonTable;
FileIoPermission lPerm;
str uniqFieldVal,preVal;
boolean headChanged;
XmlDocument xmlDoc; //to create blank XMLDocument
XmlElement xmlRoot; // XML root node
XmlElement xmlField;
XmlElement xmlRecord;
XmlElement xmlChild;
XMLWriter xmlWriter;
InventTable inventTable;
DictTable dTable;// = new DictTable(tablenum(InventTable));
DictField dField;
int i, fieldIds;
FieldId lfieldId;
str value;
lDSTablesCon = this.getDataSource();
if(lDSTablesCon != conNull())
{
lquery = new query(dataQuery);
xmlDoc = XmlDocument::newBlank();
xmlRoot = xmlDoc.createElement(lquery.name());
queryRun = new QueryRun(lquery);
k =0;
//headChanged = false;
while (queryRun.next())
{
k++;
headChanged = false;
for(j = 1; j <= conLen(lDSTablesCon); j++)
{
tableName = conPeek(lDSTablesCon,j);
lTableId = tableName2id(tableName);
dTable = new DictTable(lTableId);
lFieldLists = conPeek(allFieldlist,j);
fieldIds = conLen(lFieldLists);
commonTable = queryRun.get(lTableId);
if (k != 1 && uniqFieldVal != commonTable.(_uniqueFieldID))
{
uniqFieldVal = commonTable.(_uniqueFieldID);
if (preVal != uniqFieldVal)
{
headChanged = true;
}
else
{
headChanged = false;
}
//continue;
}
if (k != 1 && !headChanged)
{
headChanged = true;
continue;
}
preVal = commonTable.(_uniqueFieldID);
//info(common.(fieldnum(custTable, name)));
// Loop through all the fields in the record
// Create a XmlElement (record) to hold the
// contents of the current record.
if (j == 2)
{
xmlChild = xmlDoc.createElement(tableName);
}
else
{
xmlRecord = xmlDoc.createElement(tableName);
}
for (i=1; i<=fieldIds; i++)
{
lfieldId = dTable.fieldName2Id(conPeek(lFieldLists,i));
// Find the DictField object that matches
// the fieldId
dField = dTable.fieldObject(lfieldId);
// Create a new XmlElement (field) and
// have the name equal to the name of the
// dictField
xmlField = xmlDoc.createElement(dField.name());
// Convert values to string. I have just added
// a couple of conversion as an example.
// Use tableName.(fieldId) instead of fieldname
// to get the content of the field.
switch (dField.baseType())
{
case Types::Int64 :
value = int642str(commonTable.(lfieldId));
break;
case Types::Integer :
value = int2str(commonTable.(lfieldId));
break;
default :
value = commonTable.(lfieldId);
break;
}
// Set the innerText of the XmlElement (field)
// to the value from the table
xmlField.innerText(value);
// Append the field as a child node to the record
if (j == 2)
{
xmlChild.appendChild(xmlField);
xmlRecord.appendChild(xmlChild);
}
else
{
xmlRecord.appendChild(xmlField);
}
}// end of for
// Add the record as a child node to the root
xmlRoot.appendChild(xmlRecord);
}// end of TableID for loop
/* // Add the record as a child node to the root
xmlRoot.appendChild(xmlRecord);*/
}// end of while
// Add the root to the XmlDocument
xmlDoc.appendChild(xmlRoot);
lPerm = new FileIoPermission(_filePath,'RW');
lPerm.assert();
// Create a new object of the XmlWriter class
// in order to be able to write the xml to a file
xmlWriter = XMLWriter::newFile(_filePath);//----------------------------------@"c:\Items.xml");
// Write the content of the XmlDocument to the
// file as specified by the XmlWriter
xmlDoc.writeTo(xmlWriter);
}// end of if
}
Internally this method calls getTable method and getFields which will return respective datasource tables and all the fields in the query. And here is the code for it.
public container getDataSource()
{
query query;
queryRun queryRun;
int numDataTables,numFields;
int i,j;
TableId lTableId;
container conDataTables;
container conFieldList;
QueryBuildDataSource qbds;
QueryBuildFieldList fieldList;
query = new query(dataQuery);// ('CustTableCube');
numDataTables = query.dataSourceCount();
allFieldlist = conNull();
for (i =1;i <=numDataTables;i++)
{
calledDataSource = true;
qbds = query.dataSourceNo(i);
lTableId = qbds.table();
conDataTables = conIns(conDataTables,i,tableId2name(lTableId));
fieldList = qbds.fields();
numFields = fieldList.fieldCount();
conFieldList = conNull();
for (j=1; j <= numFields; j++)
{
conFieldList = conIns (conFieldList,j,fieldId2name(lTableId,fieldList.field(j)));
}
allFieldlist = conIns(allFieldlist,i,conFieldList);
}
return conDataTables;
}
public container getAllFields()
{
if (calledDataSource)
{
return allFieldlist;
}
else
{
this.getDataSource();
return allFieldlist;
}
}
--------------------------------------------------------------------------------------------------------------------------Now the second part to read the XML data and return a container
--------------------------------------------------------------------------------------------------------------------------
The Final part is now, how we can read the data form the above created XML. Below is the code which can read the data form the XML and returns a container.
public container readXMLData(FileName _fileName)
{
#define.node('Envelope')
XmlDocument xmlDocument;
XmlNode xmlInformationNode;
XmlNode xmlInformationNode1;
XmlNodeList xmlInformationsNodeList;
XmlNodeList xmlChildNodeList;
XmlNodeList xmlChildNodeList1;
XmlNodeList xmlChildNodeList2;
XmlNodeList xmlChildNodeList3;
XmlNodeList xmlChildNodeList4;
XmlNodeList xmlChildNodeList5;
XmlElement nodeTable;
XmlElement salesTableNode;
XmlElement salesLineNode;
XmlElement finDimNode;
int m,i,l;
int j, k;
container headerCon, lineCon, recCon, retCon;
int nodecount;
boolean finDim = false;
FileIOPermission fioPermission;
Commaio file;
Map recHeadData,recLineData;
#define.filename(_fileName)
#file
CustAccount accNum;
nodecount = 0;
// Assert permission.
fioPermission = new FileIOPermission(#filename ,"R");
fioPermission.assert();
xmlDocument = xmlDocument::newFile(_fileName);
info(xmlDocument.documentElement().nodeName());
xmlInformationsNodeList = xmlDocument.documentElement().childNodes();
m = xmlInformationsNodeList.length();
setPrefix("@SYS98689");
headerCon = conNull();
lineCon = conNull();
recCon = conNull();
retCon = conNull();
recHeadData = new Map(Types::String, Types::String);
recLineData = new Map(Types::String, Types::String);
for ( m = 0; m < xmlInformationsNodeList.length(); m++) //Num of Recoreds-
{
headerCon = conNull();
lineCon = conNull();
recHeadData = new Map(Types::String, Types::String);
recLineData = new Map(Types::String, Types::String);
xmlChildNodeList = xmlInformationsNodeList.item(m).childNodes();
j = xmlChildNodeList.length();
l = 0;
for (j = 0; j < xmlChildNodeList.length() ; j++)// num rows in record (Fields) header nodes-
{
xmlChildNodeList1 = xmlChildNodeList.item(j).childNodes();
xmlInformationNode1 = xmlChildNodeList1.item(j);
nodeTable = xmlChildNodeList.nextNode();
if (xmlInformationNode1 != null && xmlInformationNode1.hasChildNodes())
{
l++;
k = xmlChildNodeList1.length();
for (k = 0; k < xmlChildNodeList1.length() ; k++)// Fields has sub elemets (Lines) Node
{
xmlInformationNode = xmlChildNodeList1.item(k);
nodeTable = xmlChildNodeList1.nextNode();
recLineData.insert(nodeTable.nodeName(),nodeTable.text());
if (xmlInformationNode.hasChildNodes())//getNamedElement('CustAccount');
{
xmlChildNodeList2 = xmlInformationNode.childNodes();
nodeTable = xmlChildNodeList2.nextNode();
// Line record
// lineCon = lineCon + nodeTable.text();
}
//info(strFmt('%1 -> %2', xmlInformationNode.nodeName(), xmlInformationNode.innerText()));
}// for k
//lineCon += recLineData.pack();
lineCon = conIns(lineCon,l, recLineData.pack());
}
else if (j == xmlChildNodeList.length() -1)
{
l++;
for (k = 0; k < xmlChildNodeList1.length() ; k++)// Fields has sub elemets (Lines) Node
{
xmlInformationNode = xmlChildNodeList1.item(k);
nodeTable = xmlChildNodeList1.nextNode();
recLineData.insert(nodeTable.nodeName(),nodeTable.text());
if (xmlInformationNode.hasChildNodes())//getNamedElement('CustAccount');
{
xmlChildNodeList2 = xmlInformationNode.childNodes();
nodeTable = xmlChildNodeList2.nextNode();
// Line record
//lineCon = lineCon + nodeTable.text();
}
}
//lineCon += recLineData.pack();
lineCon = conIns(lineCon,l, recLineData.pack());
}
else
{
// Header record
recHeadData.insert(nodeTable.nodeName(),nodeTable.text());
headerCon = headerCon + nodeTable.text();
}
}// for j
recCon = conIns(recCon,1, recHeadData.pack());//headerCon);
// recCon = conIns(recCon,2, recLineData.pack());//lineCon);
recCon = conIns(recCon,2,lineCon);
retCon += recCon;
recCon = conNull();
}// for m
CodeAccessPermission::revertAssert();
return retCon;
}
-------------------------------------------------------------------------------------------------------------------------
How to use the framework class to do the things
--------------------------------------------------------------------------------------------------------------------------
We are almost there, now we have to read the container and do your stuff, for this I have created a job, which uses the above framework class and to create a XML and also reads the data from the created XML and pints the info.
static void MK_ExportText(Args _args)
{
MK_ExportHelp eh;
container conTab, recCon;
container hCon,lCon,lineCon;
str fname;
Filename fileName;
int i,j,k;
Map hMap;
Map lMap;
MapEnumerator henu,lenu;
Query lquery = new Query('MK_SalesOrder');
//Construct the framework class object with the query.
eh = MK_ExportHelp::construct(lquery);
fname =MK_ExportHelp::getFileName("AMS_SOTest1");
fileName = @"\\Dscax201201\aif\OutBound\Error\MK_SalesOrder.xml";
// Call this method to create CSV file
//eh.getCSVFile(fileName,fieldNum(SalesTable, SalesId),'|');
// Call this method to create XML file
eh.getXMLFile(fileName,fieldNum(SalesTable, SalesId));
//Call this method to read the data from the above created XML file.
conTab = eh.readXMLData(fileName);
i = 1;
while (i <= conLen(conTab))
{
recCon = conNull();
hCon = conNull();
lCon = conNull();
hCon = conPeek(conTab,i);
lCon = conPeek(conTab,i+1);
hMap = Map::create(hCon);//conPeek(recCon,1));
henu = new MapEnumerator(hMap);
while (henu.moveNext())
{
info(strFmt("Header %1 -- > %2",henu.currentValue(),henu.currentKey()));
}
for (j = 1; j <= conLen(lCon);j ++)
{
lineCon = conNull();
lineCon = conPeek(lCon,j);
lMap = Map::create(lineCon);//conPeek(recCon,2));
lenu = new MapEnumerator(lMap);
while (lenu.moveNext())
{
info(strFmt("Line %1 -- > %2",lenu.currentValue(),lenu.currentKey()));
}
}
i += 2;
}
}
