Revision as of 13:26, 3 July 2017

GIAE functionality overview

This functionality allows export of data from agility to any text based file format (xml, comma separated value, html etc.). The export and import procedures can be run at defined intervals from the background process or manually. Additionally the export function can be defined as a monitor expression that will execute based upon a condition. It is also possible to publish the Import definition as web service and the export definition as a web service client. This functionality allows distribution of Agility data to other systems

Before you start

General Import and Export operates with web services at a very low level. In other words to exchange data you must understand SOAP protocol structure using properly setup http headers.

It is recommended that a ‘RequestTool’ is available for design, configuration and testing.

A Request tool is an application which will help process the test procedure. This tool helps to generate http request, and grabs the request result.

A Request Tool has been included in Agility and is available under the default Agility Admin menu:

System Configuration -> Import Export -> Request Tool

General Export

Simple export example

You can think of and export definition as a sequential program written inside an xml file.

This Simple example showing a CSV export work order data where the work order start date is within the last 6 months will help to understand how it works.

<?xml version="1.0" encoding="utf-8"?> <export> <contents>

<const> "JobCode" + Separator() + "Desc" + Separator() + "StartDate"+ Separator() + "Due" + NewLine() </const>

<foreach table="woJob" datacontext="database" classname="DataBO.ProcessMngt.JobBO"> <dbfilter> woJob.StartDate >= DATEADD(NOW(), 'MONTH', -6) order by woJob.StartDate </dbfilter>

CsvEncode( JobCode, FullDescription, StartDate, DueDate ) + NewLine() </foreach>

</contents> </export>

The ‘Const’ and ‘data’ sections hold string expressions which will be output to file after processing. The difference in those sections is only logical. The Const section is evaluated without context and is simply output ‘as is’, and the data is evaluated in the context of the foreach element. The key to understanding export is to understanding the foreach element.

This element is responsible for:

• the context and format of data to export • filter and retrieve data to export • loop through retrieved data results and: o evaluate defined variables o evaluate string expressions from all data sections defined inside foreach element, o execute nested foreach elements

The Foreach element can operate on different datacontext instances, all the available options will be explained in next chapter. In above example datacontext is set to database. The database context requires additional parameters: table, classname, and dbfilter.

As you would expect in this context the system will create a query based on the definition in the dbfilter element. The Foreach element will then loop through results of the query contained in the dbfilter element and for each row the system will execute the string expression inside data element and will output to file. The String expression contains a few new functions which will help to produce well formatted xml or csv files.

This is a sample of the output from the above example

The Export Definition

Configuration section

The configuration section contains some parameters relating to formatting and behavior of the output

<export> <configuration> <separator>;</separator> <textquote>"</textquote> <newline>\n</newline> <encoding>UTF8</encoding>

   		<header></header>

</configuration> <contents> ...

The above configuration sample show the default values of the configuration elements. The element names are self-explanatory with the exception of the header element which is explained below.

<export> <configuration> <newline>\n</newline> <encoding>UTF8</encoding> <header> Content-Type: text/xml; SOAPAction: http://www.fastnetasp.com/GeneralImport; </header> </configuration>

This header element is used to build proper request format and response headers when Export is used to communicate over http protocol.

Useful export string expression functions

• Separator() - Returns separator defined in configuration • TextQuote() - Returns text quote defined in configuration • NewLine() - Returns new line defined in configuration • CsvEncode(arg1, arg2, ...) - Encode passed arguments to valid csv structure • XmlEncode(arg) - Encode argument to valid xml text • HtmlEncode(arg) - Encode argument to valid html text • Convert(type, value, specific culture) – This converts values with optional culture specifications. Refer to section "Exchange data localization" for further information.

Classname property definition

Classname defines business object which will be used in child elements. Classname property can be used in foreach & contents elements.

<foreach table="woJob" datacontext="dataset" classname="DataBO.ProcessMngt.JobBO"> In above example export will create new JobBO. Export functionality allows to pass "external" business object into export (from export control or when export is triggered on event). When some part of definition should operate on external business object then clasname should be prefixed with "External:" <foreach table="woJob" datacontext="dataset" classname="External:DataBO.ProcessMngt.JobBO"> External prefix is new functionality introduced in agility v6.x. Intention of this functionality is to replace and clarify external context understanding. From version 6 and above datacontext: externaldataset is obstolete. Instead "External:" prefix should be used in classname property. Contents element similar like foreach can define classname too. In this case business object is used in evaluation of string expression functions (which is required for some functions like "SYSPARAM").

Foreach element definition

The foreach can operate on different datacontexts: • dataset The Business object declared in classname, fills the dataset by executing getDataByFilter (dbfilter). Extremely useful when you wish to export the whole business object data. Consideration should be given to filtering in the sense of using a wide filter as this can consume a lot of resource where a large row count is returned. The foreach elements can be nested. With nested foreach you can process child tables. Nested foreach can also skip context definitions (apart from the table property), in which case nested foreach will inherit context from its parent.

• externaldataset Used in conjunction with "MonitorExpression". The Dataset for export is taken from the Business Object which fires MonitorExpression.

• database This context creates a query based upon dbfilter and retrieves data using the declared business classname.

• externalselectedkeys This context works similar to the database context. It adds to dbfilter keys selected from a scan to export or keys evaluated to true with MonitorExpression.

• query This allows definition of a raw SQL query defined as queryfilter. For this to work table and database properties must be defined.

• mappings This is used when the Export is embedded inside and Import to generate an "import result".

• expression This context allows to retrieve rows directly from business object. It means that function which will feed export should be hardcoded into business object. Expression context should be avoided! It constrains export definition with hardcoded business logic. It should be used only when there is no other way to retrieve data. Please check "AttendeeAvailabilityExchangeEvents.xml" sample definition for usage details.

It is important to know that if the classname property is omitted then the context is inherited from the parent foreach element. If the classname property exists then system will always initialize a new business object (there is only one exception for this rule with externaldataset context). The best way to understand how it works is to look at examples for the foreach element. The foreach element has many useful features; • Infinite depth for nested foreach elements. • A nested foreach can have its own context of any type (you can start with a dataset context and inside put a foreach with query context.) • With the string expression you can grab any parent data by creating query’s on the fly. You can use this feature in all context types! Even if you create custom sql query (although in this case you must additionally define classname and table which must exist inside the specified business object dataset). • Data can be filtered by the string expression. These string expressions should be defined inside a filter element, shown below: <filter datarowstate= "Added,Modified,Deleted,Unchanged"> if(code="a1"; True; False)</filter>

Variables definition

Variables can be defined directly inside export contents, and inside foreach elements as shown in the example below:

<foreach table="woJob" datacontext="database" classname="DataBO.ProcessMngt.JobBO">

<dbfilter>woJob.StartDate >= DATEADD(NOW(), 'MONTH', -6) order by woJob.StartDate </dbfilter>

<variable> <name>JobID</name> <value>woJob.JobID</value> </variable> ...

The Variable name is defined as a string and the value is defined as a string expression. In version 6.x introduced is shorter notation of variable:

<variable name="JobID">woJob.JobID</variable>

In version 5.1.12 introduced is new special variable called SKIPEXPORT. This variable is optionally and allows controlling output file generation process. When you want to use it, you have to add this variable in globally scope and set its value on true. The next in any other section like foreach you can set its value on false when any data to export exists. When variable is not set the system will be generated empty output file.

Functional way of describing XML/HTML

Along with linq microsoft introduced very flexible way of generating xml files using functions. Major part of this functionality is integrated in string expression. Idea is very simple. Xml document is created by set of functions which represent document. Functions available in string expressions: XDocument(object[]) XElement(name, object[]) XAttribute(name, object[]) XNamespace(url) Xmlns()

Please refer sample_xml_functional.xml for example definition. In time of writing it is not sure if functional description of xml document is better than outputting string directly in CDATA sections. Benefit of functional approach is that it guarantee valid xml structure and is resistant for "typo" bugs.

Generating JSON output

JSON is often used with ajax enabled web controls. It is commonly used for exchanging data with javascript as data written in JSON format do not require any transformation on javascript side.

Calendar event example in JSON format:

[obrazek]

Brackets [] are sequence notation and {} brackets are objects. All in all we have defined 2 objects in array. It is easy for export to generate such data format, but created are additional functions which makes it even simpler. There are two additional functions available in export (and in string expression).

JSequence and JObject. Above sample with usage of JSequence and JObject functions in string expression notation:

[obrazek2]

Benefit of using functional version is that it automatically handle data types conversion and will guard proper, comma, quotes, brackets notation.

It is good to know that with "functional way" each call function call is not immediately transformed to string, but created is structure which can be appended in any part of export and finally converted to string at end of export.

Please refer BookingsAllEvents.xml definition for example which is using JSON generator.

Export confinguration options can be found under 'configuration/interface' section:

Export Data to Pipe from scan

Export on Event

Export to Remote Connection

Export and Import Queue

General Import

Import definition extensions for web

Import configuration

Logging to system

Binding Import Data request with import definition

What else is there?

Exchange data with localization