:cubicleman.com

Ok, so I dove into the Spry world last week. As a first example, I wrote a People Soft position tree ‘crawler’. A ‘crawler’ is what I call a app that crawls up and down a position tree via drill downs/ups. In short a org chart. For most of the repots I have to write, this type of approach is used, displaying rolled up data along the way. So, lets jump right in, shall we?

First off you will probably want to take a look at the app, eh?

http://cubicleman.com/spryexamples/orgcrawl/orgcrawl.html

Yeah, its ugly, but hey it’s a example of Spry code, not CSS, eh?

The above link uses PHP in the middle in front of some MySQL data I put together to simulate a People Soft database. I will be discussing the ColdFusion based middle here though. I don’t have a ColdFusion based host and well, this shows that Spry, aka AJAX stuff, could care less about your middle and back ends, its only concerned about the front end.

Ok, Spry tools work well with XML, in fact the framework comes with a JavaScript XPATH implementation. So, I chose to use XML. I created a file called data.cfm which has two default URL vars, type and posnbr. type will help us determine whether to pull manager data or data for employees under the manager. posnbr is the position number in the reporting tree to address.

<cfif url.type IS 'manager'>
	<cfquery name="daData" datasource="rapid">
		SELECT *
		FROM pspath
		where posnbr = <cfqueryparam cfsqltype="cf_sql_varchar" value="#url.startposnbr#" />
	</cfquery>
<cfelse>
	<cfquery name="daData" datasource="rapid">
		SELECT *
		FROM pspath
		where reports_to = <cfqueryparam cfsqltype="cf_sql_varchar" value="#url.startposnbr#" />
		and posnbr != <cfqueryparam cfsqltype="cf_sql_varchar" value="#url.startposnbr#" />
	</cfquery>
</cfif>

Fairly self-explanatory, if its a manager only request, pull the manager data, else pull the employees reporting to the requested posnbr.

Now once that query is exectuted we need to massage the query into some XML. Since one of my virtues is laziness ( see brainyquote), I surfed over to cflib.org to find a UDF. Bingo! http://cflib.org/udf.cfm?ID=648 Nathan Distenfass has a UDF he shared to do the job. Thus the next FEW lines of CF code are as follows:

<cfcontent  type="text/xml" reset="true"><cfoutput>#toString(queryToXML(daData))#</cfoutput>

This modifies the HTTP header to tell the client to expect XML then outputs the XML. By default the root node is query and the child nodes representing the rows are row.

Ok, that was painless. Now for the HTML end of the schtick. First off we include the Spry framewok code

<script language="JavaScript" type="text/javascript" src="/common/javascript/Spry/xpath.js"></script>
<script language="JavaScript" type="text/javascript" src="/common/javascript/Spry/SpryData.js"></script>

Now, to kick it all off we can simply add, in our case, two lines of JavaScript to the body of our doc:

var empData = new Spry.Data.XMLDataSet("data.cfm?type=employees", "/query/row");
var manData = new Spry.Data.XMLDataSet("data.cfm?type=manager", "/query/row");

These setup two global vars of type Spry.Data.XMLDataSet, empData to store employee data and manData to store only the managers data. The first argument in the constructor is the location of the XML data, a URL. The second argument is the xpath to the root of the data we want stored in the XMLDataSet contained inside the full XML data set. Thus we could point to a much larger XML data set and filter down for smaller sets to use, something i could have done here but didn’t. Since the above is in the body of our document, it will fire when loaded. Now for the fun stuff! Now that we have this dataset in Spry, we can loop over it much like we do with CFOUTPUT

<tr spry:repeat="empData">
	<td>{empData::name}</td>
	<td>{empData::emplid}</td>
	<td>{empData::posnbr}</td>
	<td>{empData::jobtitle}</td>
</tr>

spry:repeat="empdata"
is equivalent to
<cfouput query="empData" >
telling Spry to loop over the empData dataset. The uber kewl thing is it is bound and event driven, meaning, if empData changes later, the loop is re-executed! woohoo! On each iteration through the loop we bind to elements in the empData dataset by way of the curly braces
{empData::jobtitle}
jobtitle would be a child node of row in this case. If jobtitle was a attribute of row we would use the @ like this
{empData::@jobtitle}. Ok, so now you know how to display the data via a Spry dataset. Lets add sorting! It is fairly simple to sort tabular data on the client via Spry and a Spry dataset. Simply use
datasetName.sort(attributes to sort on list, option)
In our case we will add this to the table headers via the onClick event. The ‘toggle’ option tells Spry to toggle ascending and descending. bamm!

<tr>
	<th onclick="empData.sort('name','toggle');">Name</th>
	<th onclick="empData.sort('emplid','toggle');">Emplid</th>
	<th onclick="empData.sort('posnbr','toggle');">Posnbr</th>
	<th onclick="empData.sort('jobtitle','toggle');">Job Title</th>
</tr>

Ok, so now we have our table built and populated with default data and its sortable. How to drill down or up through the reporting tree? This is handled via the posnbr and reports_to fields for each employee record. To drill up and down the tree we need to send a posnbr in our data request. So, we add a onClick event to the up or down arrow image displayed next to the name in the table

<td>{empData::displayname} <img src="/common/images/down.gif" onclick="drill('{empData::posnbr}')"/> </td>

When a user clicks this, the drill() function is called passing the current employees posnbr. Notice the use of binding again?

The drill() function sets the URL to our data set based on the posnbr passed to it. We then call the load() command on the Spry dataset, this tells Spry to go and fetch the data again, store it, and fire the events to update the UI pieces using it.

function drill(posnbr )	{
	empData.setURL('data.cfm?type=employees&startposnbr=' + posnbr);
	empData.loadData();
	manData.setURL('data.cfm?type=manager&startposnbr=' + posnbr);
	manData.loadData();
}

That’s it! Default data is used on page load, when a user clicks to drill, new data is loaded and displayed. Oh, and its sortable. Notice something annoying? When the user performs the drill, there is no inidcation of processing going on. Users like to see that something is going on, eh? So, we will add some code to turn the cursor to the hour glass. Turning it on is fairly simple. Just add
document.body.style.cursor='wait';
to the first line of the drill() function above.
Now, how to turn it off? We need to know when processing is complete, eh? Spry gives us a event we can listen for to do this called onDataChanged. To use it we need to add a listener object. We do this by adding this to the document body.

var myObserver = new Object;
myObserver.onDataChanged = function(dataSet, notificationType)
{
	document.body.style.cursor='auto';
};

shazam! Now we are cooking with gas! Hope you all enjoyed this and happy coding!

References:

• Adobe Labs Official Spry Site
• Charlie Arehart’s Spry Compendium
• Ray ‘Jedi’ Camden’s Spry Posts
• PHP Source
• Source Files