Suppose the company auditor approaches your paper-based office and requests the supporting documentation for a transaction shown in your accounts. This request is not a problem when the documents are few and easily found in your paper records. Audits, however, tend to be thorough, and transactions that merit an audit are often the complicated ones involving many documents that demand that you spend time tracing them. The probability that one or another testimonial goes astray increases with that complexity. If a single document is not traceable, the whole process takes on a shadow of doubt.

One reason documents become untraceable in a paper office is convoluted and competitive indexing systems. This manager wants a document stored in one file, and that manager wants the same document stored in a different file. Physical duplicates to satisfy both increase the bulk storage requirement. Arguments arise over which is the original, authoritative document.

A thoroughly professional accounting system allows details concerning related documents to be stored in the system with the transaction details so that retrieval is quick and easy. Where this facility is not provided, technology can still provide a workaround. This article explores how XML and PHP can provide the structure and retrieval mechanism to give this kind of auditing support.

An example transaction from the audit point of view

Documents associated with an accounting transaction might follow this pattern:

• Board resolution containing decision to contract
• Contract draft and discussion
• One or more of these documents:
  • Manager's justification for sole sourcing
  • Multiple quotes obtained from qualified suppliers
  • Manager's justification of choice from quotes submitted
• Signed contract with bid winner, together with supplemental agreements
• Invoices, and for each:
• Certification of work completed
• Canceled cheque or other certification of payment and receipt
• Final certification of work completed
• Documents related to taxes paid

In this case, the executive branch makes a decision to expend funds. The manager draws up a contract that fulfills the requirements and initiates a bidding process by calling for tenders. A supplier is chosen according to board contracting rules, and the contract is signed. Payments to the contractors, each of which probably requires its own transaction in the accounts, call for a certification of work completed and the payment made. Each stage requires a physical document or authoritative substitute that the manager can present to the auditor.

A handwritten book or file containing details of documents is quite workable in small offices. In an auditing context, however, technology has the advantage of being both fast and scalable.

Back to top

XML document database

You don't have to use XML as your back end, but an XML document is just a text file and so it is fully transparent and readable in small quantities. Consider the code in Listing 1.

<?xml version="1.0" encoding="UTF-8"?>
<auditList>
  <projects>
    <project projid="xyz987">
      <boardMinute>2011-04-07-xxxx</boardMinute>
      <draftContractA>2011-04-07-xxxx</draftContractA>
      <contractA>2011-04-07-xxxx</contractA>
      <contractB>2011-04-08-xxxx</contractB>
      <contractC>2011-04-09-xxxx</contractC>
      <workCertA>2011-04-11-xxxx</workCertA>
      <workCertB>2011-04-11-xxxx</workCertB>
      <workCertC>2011-04-11-xxxx</workCertC>
    </project>
  </projects>
  <transactions>
    <transaction accid="abc123" projid="xyz987">
      <soleSourceAuth></soleSourceAuth>
      <invoice>2011-04-11-xxxx</invoice>
      <cheque>2011-04-21-xxxx</cheque>
    </transaction>
    <transaction accid="def123" projid="xyz987">
      <sourceAuth>2011-04-07-xxxx</sourceAuth>
      <invoice>2011-04-11-xxxx</invoice>
      <cheque>2011-04-21-xxxx</cheque>
    </transaction>
    <transaction accid="ghi123" projid="xyz987">
      <sourceAuth>2011-04-07-xxxx</sourceAuth>
      <invoice>2011-04-11-xxxx</invoice>
      <cheque>2011-04-21-xxxx</cheque>
    </transaction>
  </transactions>
</auditList>

The markup in Listing 1 describes a basic system that is useful to both the accounting department and the project manager. The auditList element is the root and has two child elements—projects and transactions—each of which is a container for details relevant to the eponymous department. The original physical documents are kept in a binder with a separate binder for each day. As documents are added to the binder, they are given a unique consecutive number. Based on the date and the number, you can easily find any single document. The work for this project calls for three separate contractors—one is sole-sourced, and the others are sourced competitively. The project manager sees all the contracts as part of the same record because he thinks on a project-wide basis. The accounting manager needs a transaction-based list, so his records will refer to only one payment. The accid attribute will be the unique transaction ID number that the accounting system assigns to that transaction. This ID provides the positive link between the accounting and filing systems. Transactions also contain an attribute that relates to the project manager's list (projid), and this also works in the reverse direction, enabling the linking of project records to transactions.

Note: The sole-source authorization for contract A is missing: This omission will be a red flag for the auditor. This authorization is deliberately left blank at this point as a trap for a verification check.

This file can be visually read and interpreted without further technology, provided the list is short. In more extensive lists, you need some way of retrieving the information efficiently. There are many ways of doing this; one way is with PHP and the SimpleXML library of functions.

Back to top

PHP document retrieval

From the accounting point of view, the key into the list is the unique ID that the accounting system assigns to the transaction. The code in Listing 2 is intended to pull from the list either all transactions if no filter is specified or a single transaction together with the related project information.

<?php
$transFilter = $argv[1];
$xml = simplexml_load_file("backend.xml");
echo "=====\nSummary for accounts section\n";
if (isset($transFilter)) {
  echo "=====\nTransaction filter $transFilter\n=====\n";
} else {
  echo "=====\nNo filter - showing all transactions\n=====\n";
}
foreach ($xml->transactions->transaction as $t) {
  if (!$transFilter or $t['accid'] == $transFilter) {
    if ($t['accid'] == $transFilter) {
      $projectFilter = trim($t['projid']);
    }
    echo "Detail for transaction ".$t['accid']."\n";
    foreach ($t->children() as $tc) {
      echo $tc->getName()." : ".$tc."\n";
    }
  }
}
if (isset($projectFilter)) {
  echo "=====\nAssociated project $projectFilter\n=====\n";
  foreach ($xml->projects->project as $p) {
    if ($p['projid'] == trim($projectFilter)) {
      echo "Detail for project ".$p['projid']."\n";
      foreach ($p->children() as $pc) {
          echo $pc->getName()." : ".$pc."\n";
      }
    }
  }
}
echo "=====\nEnd of search\n=====\n";
?>

The code in Listing 2 first looks for a parameter passed in the $argv array. There might be one, or there might not. Then, the XML from Listing 1 is loaded into an object for further processing. Because the query is submitted by the accounting section, it goes straight to the transactions section and, depending on whether a filter is present, loops through all the transactions or looks for the transaction that matches the requested identifier. If the correct accid is found, the associated projid or project attribute is saved in a variable for later reference. If a specific transaction was requested, the second loop kicks in, examining the associated project details and printing them.

This script is called in the following way from the command line, where acct.php is the name of the script and def123 is the ID of the transaction obtained from the accounting system:

> php acct.php def123

Listing 3 shows the result of this query run against the data in Listing 1.

=====
Summary for accounts section
=====
Transaction filter def123
=====
Detail for transaction def123
sourceAuth : 2011-04-07-xxxx
invoice : 2011-04-11-xxxx
cheque : 2011-04-21-xxxx
=====
Associated project xyz987
=====
Detail for project xyz987
boardMinute : 2011-04-07-xxxx
draftContractA : 2011-04-07-xxxx
contractA : 2011-04-07-xxxx
contractB : 2011-04-08-xxxx
contractC : 2011-04-09-xxxx
workCertA : 2011-04-11-xxxx
workCertB : 2011-04-11-xxxx
workCertC : 2011-04-11-xxxx
=====
End of search
=====

This output provides the information required to retrieve the stored physical copies of the documents. It returns both the detailed keys for the specific transaction plus the project context. The auditor then has access to the board resolution, the contract, the source authorization, invoice, work certification, and canceled cheque. If the documents are all where they should be, this is the whole story—everyone is happy, and the auditor goes away impressed with your efficiency and organization.

Back to top

PHP integrity checking

A script can not only retrieve document information but also advise when documentation is incomplete. As noted earlier, it is important for compliance with board rules that either a justification for sole source exists or an appropriate choice is selected from multiple submitted bids. The code in Listing 4 provides such a check.

<?php
$xml = simplexml_load_file("backend.xml");
echo "=====\nIntegrity check\n=====\n";
$i = 0;
foreach ($xml->transactions->transaction as $t) {
  if ($t['accid'] == "" or !$t['accid']) {
    $accid = "# $i #";
    echo "Transaction accid missing at transaction $accid\n";
  } else {
    $accid = $t['accid'];
  }
  if (!$t['projid']) echo "Project id missing at transaction $accid\n";
  if ($t->soleSourceAuth=="" and $t->sourceAuth=="") 
      echo "Authorization problem at $accid\n";
  $i++;
}
echo "=====\nEnd of search\n=====\n";
?>

The script in Listing 4 begins by loading the XML back end into an object. It then looks at each transaction to determine whether an accid attribute is present. If that attribute is missing, the script prints a warning together with a count of the record examined. It then checks for an associated project ID number and prints a warning if one is absent. Finally, the code verifies that either a soleSourceAuth or a sourceAuth element is present and has a valid value, which in this case is not an empty string. If this test fails, the script prints a warning. You can perform this kind of integrity checking in other ways, but SimpleXML offers a quick and easy programmatic method.

Call this script in the following way, without arguments, because the code checks the whole list:

> php integrity.php

This call results in the warning provided in Listing 5 because you are checking the data in Listing 1, which has a known document reference missing.

=====
Integrity check
=====
Authorization problem at abc123
=====
End of search
=====

Back to top

Documents as images

Now that you have basic access to your audit information, a further profitable step is to ensure that all documents are available as images. Then, when an auditor requests the information, you quickly send the auditor a link to the online document collection together with a polite invitation for the auditor to contact you if further detail is required and get on with the far more important business of keeping your records up to date.

Here is Listing 1 again, this time with image information (see Listing 6).

<?xml version="1.0" encoding="UTF-8"?>
<auditList>
  <projects>
    <project projid="xyz987">
      <boardMinute image="minute987.pdf">2011-04-07-xxxx</boardMinute>
      <draftContractA image="contractdraft987.pdf">2011-04-07-xxxx</draftContractA>
      <contractA image="contract987A.jpg">2011-04-07-xxxx</contractA>
      <contractB image="contract987B.jpg">2011-04-08-xxxx</contractB>
      <contractC image="contract987C.jpg">2011-04-09-xxxx</contractC>
      <workCertA image="workcert987A.pdf">2011-04-11-xxxx</workCertA>
      <workCertB image="workcert987B.pdf">2011-04-11-xxxx</workCertB>
      <workCertC image="workcert987C.pdf">2011-04-11-xxxx</workCertC>
    </project>
  </projects>
  <transactions>
    <transaction accid="abc123" projid="xyz987">
      <soleSourceAuth image="ssauth987A.odt"></soleSourceAuth>
      <invoice image="invoice987A.png">2011-04-11-xxxx</invoice>
      <cheque image="cheque987A.jpg"></cheque>
    </transaction>
    <transaction accid="def123" projid="xyz987">
      <sourceAuth image="sourceauth987B.odt">2011-04-07-xxxx</sourceAuth>
      <invoice image="invoice987B.png">2011-04-11-xxxx</invoice>
      <cheque image="cheque987B.jpg"></cheque>
    </transaction>
    <transaction accid="ghi123" projid="xyz987">
      <sourceAuth image="sourceauth987C.odt">2011-04-07-xxxx</sourceAuth>
      <invoice image="invoice987C.png">2011-04-11-xxxx</invoice>
      <cheque image="cheque987C.jpg"></cheque>
    </transaction>
  </transactions>
</auditList>

Listing 6 is basically the same as Listing 1, with a bit more detail added. Some elements now have an attribute image, which contains the name of an image or document stored as a PDF, JPG, ODT, or PNG file. Note that in this case, the bank does not return physical cheques but provides JPG images online after the cheques have been through the clearing system. Therefore, images are available, but no physical documents are stored.

The availability of images calls for the accounting department to make modifications to the query for the benefit of the auditor. Listing 7 shows the updated query.

<?php
$transFilter = $argv[1];
if (!$transFilter) die ('No transaction specified\n');
$path2images = "/path/to/images/";
echo "=====\nAudit response HTML\n=====\n";
$xml = simplexml_load_file("backend2.xml");
echo "=====\nTransaction $transFilter\n=====\n";
foreach ($xml->transactions->transaction as $t) {
  if ($t['accid'] == $transFilter) {
    $projectFilter = trim($t['projid']);
    echo "Detail for accounting transaction '".$t['accid']."'\n";
    foreach ($t->children() as $tc) {
      echo $tc->getName()." : ".$tc." image "
	."<a href='$path2images".$tc['image']."'>".$tc['image']."</a>\n";
    }
  }
}
foreach ($xml->projects->project as $p) {
  if ($p['projid'] == $projectFilter) {
    echo "Detail for project ".$p['projid']."\n";
    foreach ($p->children() as $pc) {
      echo $pc->getName()." : ".$pc." image "
	."<a href='$path2images".$pc['image']."'>".$pc['image']."</a>\n";
    }
  }
}
echo "=====\nEnd of list\n=====\n";
?>

Listing 7 begins by expecting that the search is for a specific transaction. If no accounting ID is specified as a parameter, the script stops with a warning. Otherwise, it loads the modified back end into a SimpleXML object and searches the transactions for that accounting ID. When it is found, the associated project ID is stored for later use. The script then prints the information for associated documents. Now, the output contains both a reference to the physical document (where one exists) and a link embedded in HTML tags to the image stored somewhere on the network.

The following command-line interface command:

> php auditresponsehtml.php def123

produces the output in Listing 8.

=====
Audit response HTML
=====
=====
Transaction def123
=====
Detail for accounting transaction 'def123'
sourceAuth : 2011-04-07-xxxx image 
  <a href='/path/to/images/sourceauth987B.odt'>sourceauth987B.odt</a>
invoice : 2011-04-11-xxxx image 
  <a href='/path/to/images/invoice987B.png'>invoice987B.png</a>
cheque :  image 
  <a href='/path/to/images/cheque987B.jpg'>cheque987B.jpg</a>
Detail for project xyz987
boardMinute : 2011-04-07-xxxx image
  <a href='/path/to/images/minute987.pdf'>minute987.pdf</a>
draftContractA : 2011-04-07-xxxx image
  <a href='/path/to/images/contractdraft987.pdf'>contractdraft987.pdf</a>
contractA : 2011-04-07-xxxx image
  <a href='/path/to/images/contract987A.jpg'>contract987A.jpg</a>
contractB : 2011-04-08-xxxx image
  <a href='/path/to/images/contract987B.jpg'>contract987B.jpg</a>
contractC : 2011-04-09-xxxx image
  <a href='/path/to/images/contract987C.jpg'>contract987C.jpg</a>
workCertA : 2011-04-11-xxxx image
  <a href='/path/to/images/workcert987A.pdf'>workcert987A.pdf</a>
workCertB : 2011-04-11-xxxx image
  <a href='/path/to/images/workcert987B.pdf'>workcert987B.pdf</a>
workCertC : 2011-04-11-xxxx image
  <a href='/path/to/images/workcert987C.pdf'>workcert987C.pdf</a>
=====
End of list
=====

A number of assumptions are made here to keep things simple. First, the script assumes that all the files are there and correctly named; second, it assumes that the auditor understands the indexing system. And third, it assumes that the auditor's browser can handle the document type, perhaps as a download, as an add-on, or opened by an application.

Back to top

PHP and image functions

You can also manipulate images in some way before they are delivered to the reader. Say the image needs to be marked so that if printed, the new document cannot be mistaken for the original archive copy. PHP offers a number of image functions that can, for example, print a large "VOID" across the document or maybe the transaction ID in one corner.

Listing 9 provides an example PHP fragment for adding a string to a document before it is streamed to a browser.

<?php
$im = imagecreatefromjpeg("/path/to/images/contract987C.jpg");
$textcolor = imagecolorallocate($im, 0, 0, 100);
imagestring($im, 5, 55, 55, 'Archive: 2011-04-11-XXXX', $textcolor);
header('Content-type: image/png');
imagepng($im);
imagedestroy($im);
?>

In this code, the image is created from the original document, the overlay text colour is defined, and then the string Archive: 2011-04-11-XXXX is printed in the top left corner as part of the image, overprinting anything that was in that location before, 55 pixels down and across. The text colour should ideally take into account the colour of the background on which it will appear to make it clearly legible. The final image is then delivered to the browser.

Other PHP image functions include testing the image to see how large it is and, if the size is over a certain recommended limit, scaling the image to a more reasonable size for the auditor's screen.

Back to top

Indexing for other departments

Given that the examples here try to allow for both accounting and project department indexing of one set of physical files, it should be a simple matter to have other sections in the XML back end as children of the root element to act as containers for other departments. As long as the well-formedness of the overall document is maintained, PHP scripts can include or ignore other sections with ease.

If a new board manager wants to keep his board documents separate, this approach will call for some changes to Listing 1 and Listing 6. Taking the former as an example, see Listing 10.

<?xml version="1.0" encoding="UTF-8"?>
<auditList>
  <boardMinutes>
      <boardMinute boardMin="bm1234">2011-04-07-xxxx</boardMinute>
    ...
  </boardMinutes>
  <projects>
    <project projid="xyz987" boardMin="bm1234">
      <draftContractA>2011-04-07-xxxx</draftContractA>
      <contractA>2011-04-07-xxxx</contractA>
      ...
    </project>
  </projects>
  <transactions>
    ...
  </transactions>
  
</auditList>

In this modification of Listing 1, the board minute entry that used to be in the projects section has been moved to a new boardMinutes container, where an index boardMin has been added to a new boardMinute element in the new container and the same index has been added as an attribute to the related project element. Now, the projects section can find the related board information and vice versa. In this way, satisfying the needs of different managers is just a matter of appropriate container elements and indexing attributes.

Back to top

Conclusion

In this article, you explored some tools for dealing with an auditor's request for supporting documentation. Clearly, PHP working in cooperation with a document list organized as an XML file can help ensure that you can locate and present required documents with minimal delay. Organizing information this way helps to shift the burden of the work from a stressful, time-limited situation when the audit request comes in to an ongoing task of careful document classification and retrieval.

The setup described in this article can become more complicated with the introduction of XML schemas and doctypes to control the addition and entry of items and to check for missing elements. PHP and SimpleXML functions are quite sensitive to the well-formedness of the XML, so hand-editing files with a plain editor is not as effective as a specialist application such as Eclipse (see Resources), which works to ensure that the resulting document after editing is well formed.

The totally paperless office is still a rarity in the business world. One reason is that actively participating offices need to be running compatible software. With all the elements of this type of system as open source and free, the barriers to full adoption then become employee training and managerial reluctance to change.

Resources

Learn

• PHP and SimpleXML: Read more about this tool set that converts XML to an object that can be processed with normal property selectors and array iterators.
  
  
• SimpleXML processing with PHP (Elliotte Rusty Harold, developerWorks, October 2006): Discover the SimpleXML extension, which is bundled with PHP version 5 and enables PHP pages to query, search, modify, and republish XML in a PHP-friendly syntax.
  
  
• Get started with the Eclipse Platform (Chris Aniszczyk and David Gallardo, developerWorks, July 2007): Learn more about developing in Eclipse.
  
  
• Getting started with Eclipse, from the DB2 on Campus book series: Check out the free eBook. Find out what Eclipse is all about and practice using hands-on exercises.
  
  
• Introduction to XML (Doug Tidwell, developerWorks, August 2002): Get the basics about XML: what it is, why it was developed, and more in this tutorial.
  
  
• Thinking XML: Analyze financial reporting using XBRL (Uche Ogbuji, developerWorks, January 2009): Read about more advanced uses of XML in accounting and learn to interpret Extensible Business Reporting Language well enough to make sense of financial filings.
  
  
• XML development with Eclipse (Pawel Leszek, developerWorks, April 2003): Check out ideas on editing XML in a solid editor and harness the power of XML with Eclipse.
  
  
• Scripting in PHP: Learn more about this widely-used, general-purpose scripting language that is especially suited for web development and can be embedded into HTML.
  
  
• More articles by this author (Colin Beckingham, developerWorks, March 2009-current): Read articles about XML, voice recognition, XHTML, PHP, SMIL, and other technologies.
  
  
• New to XML? Get the resources you need to learn XML.
  
  
• XML area on developerWorks: Find the resources you need to advance your skills in the XML arena. See the XML technical library for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks
  
  
• IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
  
  
• developerWorks technical events and webcasts: Stay current with technology in these sessions.
  
  
• developerWorks on Twitter: Join today to follow developerWorks tweets.
  
  
• developerWorks podcasts: Listen to interesting interviews and discussions for software developers.
  
  
• developerWorks on-demand demos: Watch demos ranging from product installation and setup for beginners to advanced functionality for experienced developers.
  
  

Get products and technologies

• IBM product evaluation versions: Download or explore the online trials in the IBM SOA Sandbox and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

Discuss

• XML zone discussion forums: Participate in any of several XML-related discussions.
  
  
• The developerWorks community: Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• An example transaction from the audit point of view
• XML document database
• PHP document retrieval
• PHP integrity checking
• Documents as images
• PHP and image functions
• Indexing for other departments
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

With the no-charge DB2 Express-C, Zend Core PHP, and Java you can develop advanced Web 2.0 applications quickly that matter.

---

• Try: Download a free trial version of DB2 Express-C, which includes Zend Core PHP and pureXML technology that is ideally suited for managing both XML and relational data and powering a new breed of SOA and Web 2.0 solutions.
• Article: This IBM Redbook shows how to port PHP apps from MySQL to DB2, and includes code samples for creating Zend Core PHP applications.
• Tutorial: In this Mastering Facebook app development tutorial series, you will use Java and PHP in parallel to build a Facebook interface for an online stock trading J2EE application using the Spring framework.
• Buy: DB2 9 for Linux, UNIX and Windows

Dig deeper into XML on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers