STaD

As promised, here are some screenshots from my new staff directory apps, with some commentary. Click a screenshot to view full size.

Administrative Backend

Though this may look a bit complicated, the administrative backend was the easiest to setup of the three apps I built for the staff directory. Using the Adobe Dreamweaver Developer Toolbox, the application setup is Wizard-based, once you’ve setup your SQL database query. I’ve found that the ADDT widgets do not scale well for large databases, but the speed is acceptable for databases with a few thousand records.

The above screenshot shows the update page for a staff member.

Intranet Staff Directory

The staff directory for the Intranet took some time to code due to using new AJAX and XML techniques on the SCOCA web.

First, the staff information is obtained from an XML page. This means that the directory page is not PHP, but a standard HTML page. This has the advantage of using much less overhead when the page displays. The disadvantage is that again, it won’t scale well with a large database.Look for an upcoming post about how this works.

Second, you’ll notice the use of a hover action. When a staff member hovers over an entry in the directory, a small window opens that includes the staff member’s picture, along with office/home phone numbers, and home address.

Before I move to the next screenshot, notice the link in the above screenshot for a staff member to update their information.

After clicking the update link, the form to update information appears on the same page as the directory, rather than opening on a new page. This is one of the advantages of using AJAX interactivity.

SCOCA Web Staff Directory

This is the version of the staff directory that will be seen by public visitors to the SCOCA web. Again, the data is being obtained from an XML page. The tabbed interface for each department is another use of AJAX.

My goal for our public web is to make applications intuitive to use. Here, the user simply clicks a department tab to view the members of the department. Clicking the staff member’s name will send an email, and the icon beside the name will allow a user to download a vCard for that person.

You may notice that there is no banner or menu system at the top of the page.  Those two items, along with the SCOCA home page, are the last aspects of the site that will be coded.

This week I’m starting on one of my big projects for the new SCOCA web, our Training Management System (TMS). TMS is arguably the most complex app I have on our web, especially on the backend for staff and administration management. I have a list of my own changes and new features, changes requested by staff, and changes requested by Administration. I’ve budgeted about four weeks for the project, and I plan to post more about what I’m doing as I get further into the coding. This week I’m outlining my final changes, and getting ready to setup for the new administrative backend.

I originally planned for the new web site to be online by the end of 2008. This will be my fourth version of the site, and the SCOCA web has probably been redone more than any other ITC web site in the state. I’ve traditionally used the redesign to learn and apply a new technology. The first web site I did for SCOCA was to basically make a static site that fits into the profile of a service oriented organization. The second redesign was the first to make use of Dynamic web pages using PHP and MySQL. The 2004 site made use of Flash coupled with XML.

Adding AJAX

I really like the functionality of the Flash-based home page and the Flash menus used on interior pages with the current site, but working with the Flash interface is a real pain, and I also had other problems trying to update anything on the Home page. I also didn’t like leaving my usual Dreamweaver environment and going into Flash, It really slowed things down for me.

For the new web, I spent time last summer learning some of the ins and outs of AJAX (Asynchronous Javascript and XML). I first noticed the use of AJAX on the netflix site, and now it is being utilized as probably the new de facto standard for web page interactivity. I can get most of the functionality of Flash, but with better integration with my PHP and MySQL environments. AJAX is fairly easy to integrate, and I’ll have much more to say on this topic later, with some examples that you can use to spruce up your own site.

Tear Down That Wall

As I began my redesign last September a few things became apparent. First, I really needed to migrate our applications to use our MySQL v5 server, but there are apps that we still use from 2003, and some of those databases are only compatible with our current MySQL v3 server. Second, if you code with Dreamweaver, you know that the default option is to store all of your scripts, images, and CSS files in the same directory as your app. This can make for a lot of unneeded redundancy on the site. The last huge gotcha is that when Adobe purchased Macromedia, they really changed their PHP environment, and although they released a Developer’s Toolbox, it is not compatible with the old Interakt toolbox (which Adobe also purchased). So after studying all of this I came to these conclusions:

• All apps need to be upgraded for MySQL v5 compatibility
• All apps need to be upgraded for Dreamweaver CS3 (and beyond) compatibility
• Javascript, CSS, PHP includes, and Image files need to be centralized

After looking at our current site, I soon realized there was no way I would have the new site ready by December. The biggest slowdown is making everything compatible with Dreamweaver CS3+. For example, my upgrade to the State Districts Directory web app should have only taken 20-30 minutes (after I migrated to MySQL v5), because all I wanted to do was add a simple vCard feature and change the CSS. I ended up having to recode from scratch because of syntax changes from the old tools to the new tools. Another very annoying problem was that there was a bug that kept changing MySQL queries when I used MID, RIGHT, LEFT for zip codes and phone numbers.The project ended up taking three days.

Most users of our site don’t see or care how it works, so they don’t know that almost every page that they see is generated dynamically from a database, and that the information gets there from even more web applications that our staff uses or are generated from CRON scipts for updates.

The new site is going to have to be up and running sometime between June and August. This will give some time for the staff to get used to new functionality and for the site to “settle” before the beginning of the 2009-10 school year.

One of my main reasons for posting on this blog is to show some of the processes I use and the problems that can occur when coding web applications. A good case in point is when I recoded our staff directory a couple of weeks ago. When I code something for SCOCA it almost always involves three applications: an application for SCOCA public users, an app for staff, and a backend for someone on the staff to administer. I’m going to write a bit about my decision to integrate vCards into the staff directory.

The vCard is a standard that allows exchange of contact information between various PIM (Personal Information Manager) software applications. You’ve probably seen vCards attached to emails or as links on web pages. vCards are now a fairly old standard. Lets take a look at some of its structure (I’ve modified the email address below to prevent bots from getting the real email):

BEGIN:VCARD
VERSION:3.0
CLASS:PUBLIC
PRODID:-//class_vcard from TroyWolf.com//Modification by SCOCA//EN
REV:2009-03-15 22:24:07
FN:Joshua Leeth
N:Leeth;Joshua;;;
TITLE:Network Engineer
ORG:SCOCA
ADR;TYPE=work:PO Box 577;;175 Beavercreek Road;Piketon;Ohio;45661;
EMAIL;TYPE=internet,pref:leeth@scoca.itc.com
TEL;TYPE=work,voice:740-289-5327
URL;TYPE=work:http://www.scoca-k12.org/departments/department.php?did=3

Even if you’re not a coder you can see this is a fairly straightforward structure, although if the vCard was introduced now it would undoubtedly be done with XML, making it more versatile for use in other applications. If you want to look at the structure of any vCard, simply load it into Notepad.

For the public staff directory, I wanted our users to be able to click an icon next to a staff member’s name to download a vCard for their own PIM. Since the staff directory is displayed from a MySQL database, I wanted to create the vCard “on the fly” when users click the icon for the vCard.

Although it can take time to code, dynamically creating vCards, vCalendars, and other types of documents isn’t extremely difficult once you know the MIME-type of a file and some of its structure. For SCOCA’s Training Management System (TMS), one of my backend applications dynamically creates an Excel spreadsheet that tracks all of the training we do at SCOCA, including numbers trained, districts served,who is doing the training etc., and ties this into our goals and strategies for our Continuous Improvement Plan. It took me about two weeks to figure out the proper Excel Structure and SQL queries to get it right for the requirements of SCOCA administration. For the vCard I was fairly sure that someone had probably already done the heavy lifting and created a PHP class that would make the process easier. After a bit of searching I found a vCard class coded by Troy Wolf.

One thing I wanted to try and add to the vCard was a staff photo. I knew that Outlook 2007 supported this feature so I thought it would be a nice addition. Looking through the vCard class file I noticed although a call was made to add a photo for the vCard, the proper structure was missing. I emailed Troy, and he replied that I was the first to discover this bug, and he told me where I should add it into his class.

I already have photos of each staff member stored in a directory on our web server, so my next step was to find how photos get into a vCard.. After a quick Google search, i found this line of code:

PHOTO;VALUE=URL;TYPE=GIF:http://www.abc.com/dir_photos/my_photo.gif

I couldn’t believe it was going to be this easy. It wasn’t.

The code simply wasn’t working, and I couldn’t see any pictures when loading the vCard into Outlook. I spent the rest of the day searching for answers, and of course I found “sort of” the same line of code, but with different syntax and additions. This is the time when coding can get frustrating, as I try to debug or find answers to problems.

The next day I found an alternative to getting the picture through a URL. I would need to convert each photograph to BASE64 and enter the entire structure into the vCard file. I was beginning to suspect that there was nothing wrong with my code, and it was probably that Outlook did not support a photo through a URL.

I didn’t want to code my own app just to convert 50 photos to BASE 64, so I found an online PHP application that would do it for me. All I had to do was enter the URL for each photo. I then created a new field in our staff SQL table and dropped each BASE64 conversion into the field. I imported my test vCard into Outlook, and there was the photo.

Ny final code for the vCard is a mixture of hard-coded information, and data retrieved from the database. Basically, I hard coded SCOCA’s address, and added a vCard note that links to our main web site. In the code excerpt below you can see how I added the hard coded info and the data pulled from MySQL: For each staff member’s web page I  hard coded the URL for the individual’s SCOCA department page, and then dynamically added the department ID at the end, pulled from the database.

query_Recordset1 = sprintf("SELECT * FROM scocastaffdir WHERE id = %s",

$vc->data['first_name'] = $row_Recordset1['firstname'];
$vc->data['last_name'] = $row_Recordset1['lastname'];
$vc->data['title'] = $row_Recordset1['position'];
$vc->data['work_po_box'] = "PO Box 577";
$vc->data['work_address'] = "175 Beavercreek Road";
$vc->data['work_city'] = "Piketon";
$vc->data['work_state'] = "Ohio";
$vc->data['work_postal_code'] = "45661";
$vc->data['office_tel'] = $row_Recordset1['phone'];
$vc->data['email1'] = $row_Recordset1['email'];
$vc->data['url'] = "http://www.scoca-k12.org/departments/department.php?did=".$row_Recordset1['did'];
$vc->data['photo'] = $row_Recordset1['binphoto'];
$vc->data['note'] = "Visit us on the web at http://www.scoca-k12.org";

Below is a screenshot of the vCard from Outlook (click the image to view full size):

In a later post I’ll show the end result of the three versions of the Staff calendar that I created.