ADP DAP, PICK, and PowerShell

ADP’s DAP program allows the developer to access data objects on the UNIX server. There is a standard set of Business Data Objects that include groups of data fields related to each other. For example, in the Service Department objects there are objects called ServiceSalesClosed and ServiceSalesOpen, the former holding data relating to all the Service Transactions that are finished (i.e., closed) and the “Open” service sales are still in process. Inside the objects there are up 400+ fields of data.

My plan was to jump in and understand the XML Engine front end that ADP created and see what it could do. I decided that PowerShell would be the best way to experiment and learn.

I am a beginner at PowerShell especially as it relates to Xml so any readers that cares to offer helpful hints and/or critique, please do. Also feel free to correct my verbiage. I know that I am not using the terms root, node, child etc correctly at times. Also please note the back tick to indicate a line continuation. I have note found a good code inserted for PowerShell script for Windows Live Writer yet.

My first script was designed to set up the request as specified by ADP and use the .Net WebClient object to submit it. The request has a tag for Selection filtering but I really wanted to see all there was so I ignored that parameter.  Voila I received a response!

Here’s the script:

$Url = http://xxx.yyy.zz.aa:pppp
$webclient = new-object System.Net.WebClient
$soapMessage = @"
<?xml version="1.0" encoding="UTF-8"?>
<eoapi:Request xmlns:eoapi=http://www.adp.com/xxxxxx version="1.0" ShowUnknownElementErrors="Yes">
   <Session>
   <Connection>
      <Product>Product</Product>
      <Server>Server</Server>
      <Password>password</Password>
      <Pooling>No</Pooling>
   </Connection>
   <GetSet TimeoutSeconds="1800">
    <Qbject>TheDataObjectName</Qbject>
    <Group>DealershipCode</Group>
    <Selection></Selection>
    <MultiFlag>E</MultiFlag>
   <GetSet>
  </Session>
<eoapi:Request>
"@
# Upload the Soap payload ($SoapMessage) to the URL:PORT of the ADP XML Engine ($Url)
$result = ([xml]$webclient.UploadString($Url, $soapMessage))
# Eliminate the duplicate reply tags by starting the XML string at 2nd Reply tag (one with the #group attribute), then convert that string to Xml ([xml]) and store in $xmldata
$xmldata = [xml]($result.reply.session.get_InnerXML() | out-string | %{$_.substring($_.IndexOf('
$xmldata.reply.ServiceSalesClosed  |  Export-Csv "C:\Users\John Donnelly\Desktop\Output.csv" -NoTypeInformation
$excel = new-object -comobject Excel.Application
$excel.visible = $true
$workbook = $excel.Workbooks.open("C:\Users\John Donnelly\Desktop\Output.csv")

Explanation:

I set the $Url variable for the address of the XML Engine front end with IP and Port as follows:

$Url = http://xxx.yyy.zz.aa:pppp

and I then instantiate a WebClient object to handle the request response as follows:

$webclient = new-object System.Net.WebClient

I then setup the request payload as a variable called $soapmessage per the ADP DAP specifications (encoding, namespace etc):

$soapMessage = @"
<!xml version="1.0" encoding="UTF-8"?>
<eoapi:Request xmlns:eoapi=http://www.adp.com/xxxxxx version="1.0" ShowUnknownElementErrors="Yes">
   <Session>
   <Connection>
      <Product>Product</Product>
      <Server>Server</Server>
      <Password>password</Password>
      <Pooling>No</Pooling>
   </Connection>
   <GetSet TimeoutSeconds="1800">
    <Qbject>TheDataObjectName</Qbject>
    <Group>DealershipCode</Group>
    <Selection></Selection>
    <MultiFlag>E</MultiFlag>
   </GetSet>
  </Session>
<eoapi:Request>
"@

(FYI the Qbject tag above should be Object but WordPress won’t allow that particular tag.)

We invoke the WebClient UploadString method and supply the 2 parameters, URL and Payload . The result is returned and stored in a variable $result: as follows:

$result = ([xml]$webclient.UploadString($Url, $soapMessage))

The reply comes back with a header then a root Reply node with child nodes for connection error code and message. If there is a connection error you will receive error information here. If the connection is successful the response will include a Session Root node. If the Selection parameter is successful (in our case there isn’t one so it was successful) then you will receive a response as follows:

<Session>
  <ErrorMessage />0
  <Reply group="TheDealershipCode" object="TheDataObjectName" type="GetSet">
    <TheDataObjectName>blah blah blah</TheDataObjectName>
    <TheDataObjectName>blah2 blah2 blah2</TheDataObjectName>
 <ErrorMessage />0
 </Reply>
</Session>

If the Selection parameter generates an error you will see error information in the ErrorCode and ErrorMessage nodes in the first Reply node.

In my initial evaluation I didn’t need any data other than the TheDataObjectName nodes. The $result variable is typed as XmlDocument so we can use the dot method of extracting the inner Xml string. I created a new variable ($xmldata) by using the get_InnerXml() method to pull the Xml as a string from the Session Node (exclusive of the Session tags) as follows:

$xmldata = [xml]$result.reply.session.get_InnerXML()

Another way to say this (probably in my mind only) is that we drilled down to the session level (node) and grabbed the XML as a string. We then pipe it out:

| out-string | %{$_.substring($_.IndexOf('</pre>
and we then create a substring of the Session InnerXML starting with the&nbsp;Reply tag which happens to have an attribute for group which does not exist in the Reply tag we don't want.

At this point $xmldata is a string which we strongly typed as [xml].

The string is checked for well-formedness when typed so we know we have good XmlDocument object going forward.

If we take a look at the InnerXML for this XmlDocument object it will look like this:


<Reply group="TheDealershipCode" object="TheDataObjectName" type="GetSet">
  <TheDataObjectName>blah blah blah</TheDataObjectName>
  <TheDataObjectName>blah2 blah2 blah2</TheDataObjectName>
</Reply>

I then use dot notation to select the TheDataObjectName nodes and pipe them to the Export-Csv commandlet and save it to a file on my desktop with no type information:

$xmldata.reply.ServiceSalesClosed  |  Export-Csv "C:\Users\John Donnelly\Desktop\Output.csv" -NoTypeInformation

I then open the file in Excel to take a look.

If anyone has better suggestions on the script or comments please share.

Author: John Donnelly

A professional in the automobile business.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s