Monday, December 31, 2007

Creating a Document with nice Headers and Footers when all you have to work with.. is HTML (A solution found in Word 2007)

Our newest client is pushing the limits of our reporting tools. Our customers, to this point, have been quite happy with either no header/footer (they print on letter head), or one header at the top and one at the bottom of their documents. And, in fact, all of them have either transmitted their report results via inline email (which means we can use HTML to add Header/Footer, and no one cares about "Pages"), or they have printed their documents (on letter head). So, headers and footers on each page has never been an issue... until now!

So, the problem is this : You let your customers enter as little as 1 sentence or as much as 50 pages of text into a rich text area, with formatting, tables, images, etc. Then, they want to output their fluid body of a document into a template of sorts with headers/footers and better yet, in PDF Format, for emailing to customers!

Well, HTML doesn't know anything about page breaks, and I don't know about you, but programming to the PDF standard, or even using FPDF is a nightmare I wouldn't wish on any developer, especially not if you want your users to be able to create content, that's later saved in PDF Format.

I had no luck with the HTML to PDF Converters; they lack CSS support almost completely, don't handle images particularly well, and if an integration package that's supposed to make my life easier has me pulling my hair out after 6 hours working on a simple sample that they provided, I tend to look for alternate solutions.

Enter : Microsoft Word 2007.

Tho previous "new releases" of word carried with them only a nicer logo, and 5 new features buried under 30 menus that no one needs any way, it appears that Word 2007 has introduced an actual improvement that will make my job here a lot easier : Open XML.

See, previously a Word Document was encoded in some way, so generating an actual word document was near impossible; especially for those of us who don't use Microsoft programming languages. Word did, however, do a nice job of taking my HTML Document (named with .doc on it), and converting it to look just like a Word document when opened in Word. There were some issues here tho (Save As... "Web Page" ??; and no support for Headers, Footers, Water marks, etc.) which didn't make it much more attractive as a final format. Meaning it didn't add anything I couldn't already do with just straight HTML.

Word 2007, however, has changed that landscape quite a bit. DOCX files (the new, Word 2007 document extension) is now just a .zip file! Upon unzipping one, you will find the insides quite interesting. Heavy use of XML, and document relationships which point one XML document to another in the tiered directory structure inside your Word Document. This structured format is termed OpenXML.

The prior "mystery encoding" is gone, and we are left with something that humans can understand.. for the most part :-) The document.xml file (the body of the actual document) still uses XML to describe the body of your document, which would leave me in a position much like writing to fpdf or some other non-html structure.

So, Treff and I set out trying to figure out how Microsoft could go to an open standard like this, support converting HTML to it's native format (for display inside Word) without allowing us to feed it HTML, embed the HTML inside the docx file in some way. Were they really that... unhelpful?

Turns out, the answer is no... they weren't! It's actually quite easy to take normal HTML, and merge it into a Word Document with Header, Footers and Water marks and these elements stay in tact, while the body of the document changes to what you added to the HTML. AND... once opened, Word converts this HTML to it's document.xml "Voodoo" syntax, and saves as a normal .docx document, or as a Word 2003/XP format... OR as a PDF!

It took me a while to get all the pieces to come together, but at this point the process to create a PDF with Headers, Footers and Water mark from HTML input involves :
  • Create your headers/footers/watermark in Word 2007, save as DOCX
  • Unzip the document
  • Follow this example for adding HTML to it
  • Save all files, ZIP, change name to .docx.
  • Open the document in Word
  • Save as PDF
Next step is to work out creating the Header/Footer with HTML Contents, and setting the water mark image. Then, I can create a Report Output type in WorkXpress that allows the Implementer (IM) to create a Header, a Body, a Footer, and set a Water mark image, click Next, and have a Word .docx download right to their machine!

If only PDF had an open standard like that, we could skip all the remaining silly steps.

Friday, December 28, 2007

A Quick Books integration

A few days ago I set about trying to figure out how we're going to add Quickbooks integration functionality to WorkXpress, and today I had my first bit of success.

I've found that the tricky part of developing an "Integration" is almost always figuring out what the other side has to offer. What does *their* API look like? How do I connect to their systems? What 128,000,000,000 bit keys do I need to access their systems, and where do I get them? Now that I have one.. where do I put it?!? It goes on and on.

So what's the first step? Making a Connection to the third party, in an elementary way.

The trouble with Quickbooks is that it's on it's 7th SDK revision. So, the net is covered in a sea of integration examples from all different revisions of Quickbooks connectivity; and I had trouble even figuring out which one would work with the version of quickbooks the client wants to use.

In the end, it was QuickBooks Enterprise 2007, which meant SDK 7.0. This version of QuickBooks uses the "Web Connector", or QBWC.

The Web Connectors role in allowing Quickbooks to be exposed is quite cool.

Quickbooks, as you know, is a "standalone desktop app". Which is tech speak for "a program you install, in Windows, that just works." So, how does a developer connect to this kind of application? Through the web connector!

The QBWC is another desktop app that you install on the computer with the QuickBooks company file. These three things (an install of QuickBooks, QuickBooks Web Connector, and the Company File you want to access) all live on one Windows machine, that becomes the "QuickBooks Server", or the computer with Windows that is always running that always has this quickbooks file open in QB, and the QBWC always running.

Once I got the QBWC running, it was time to tell it about my application; IE : LET ME IN! It turns out, instead of providing access to Quickbooks directly, this little gems job is to make SOAP calls out to another server, retrieve work to do, do the work with QBooks, and return the results from the requested work, via SOAP.

So you don't make "Calls" to Quickbooks really, what you do is you queue up work for Quickbooks to do the next time it connects, which QBWC controls the frequency of.

You provide QBWC with a username and password to give back to you to authenticate itself, and you have to implement authenticate for your own UName/PW. There's a series of simple calls that the QBWC makes to get its work, and report back it's progress/completion/response.

Instead of reinventing the wheel on this side, I found a PHP Framework for Quickbooks on the Intuit Developers Blog, developed by Keith Palmer. Keith went ahead and wrote a MySQL Driven, PHP supported Soap Server that handles receiving communication from QBWC, queueing up tasks to send to QBWC, and sending them when they are requested.

All you have to do is figure out how you want to store your data for the queue, and implement request and response functions which are the interface between your app (the one making the request)... and your app (the one that you need to effect when there is a response).

So, he's gone ahead and ripped out all of the tough stuff, and left us with simple implementation, based on what WE need to tell, and listen to, from Quickbooks. Thanks Keith!

So.. I modified his examples a little bit, and built myself a page, that queues up an Add Customer request, that will add me, myself and I to the QuickBooks File when the QBWC asks for work.

Now that I have the simple implementation working, it's time to move on to what the customer actually wants :-) And, all the while, trying to figure out an elegant way to allow an Implementer to simply create an action, answer some questions, and BOOM, pull data from their QuickBooks installation.

Chances are pretty good, since we're under the time (as usual) on this project, that I will just implement exactly what's needed for this customer, and worry about circling back and implementing something elegant later. Once we have one customer integrated with Quickbooks, we can setup similar integrations for any other customer.

Thursday, December 27, 2007

What is WorkXpress?

Well, you can check out the marketing version of what WorkXpress is here : http://www.workxpress.com

I can give you the short story here :

The Engine
WorkXpress is an engine for building applications that are served over the web. It is designed with a high level of usability in mind, so much so that the goal is to allow non-programmers, people who are essentially "really good with Excel", to create sophisticated, totally customized (and customizable) web based applications to run their department, their division, or ultimately their whole company.

The Technology
The technology is based in the open source world of products utilizing PHP, MySQL, Apache on Gentoo Linux based servers. We use other open source packages such as many PEAR packages and JQuery for javascript.

How it works
The website has a bit about the building blocks on it but I'd like to talk about them from a more.. traditional-programming perspective.

Items
In WorkXpress, we track things. These things can be grouped into types. In any CRM Tool people track Contacts and Companies. These things are types of items, or Item Types. Each individual Contact or Company is AN item.

You can think of Item Types like Tables in a traditional database model. When you're designing a CRM Tool, you decide you will definitely need a 'Contacts' Table, and this table will have columns like "First Name", "Last Name", "Phone Number", etc.

In WorkXpress, the Table is an Item Type, the Columns are 'Fields', and each Row in the Table, is an Item.

Item Types's go a few steps further than tables, and start to be more like OO Classes when you add Rules. If Fields are like Member Variables in your class, then Rules are like Methods. You can (through a web-based, point and click interface) build simple, or complex methods which are fired when certain events happen. For instance, there is a set of Rules that fire when an Item of that type is created (like the constructor of a class), an item of that type is recycled (destructor). The difference is that the instances of these classes are preserved, saved in the database, so they are available to other users, and they are available the next time you login, etc. Much like rows in a table.

Fields
We've already covered Fields a bit, they are simply data stored on an item. When you create a field, you setup it's parameters like a label, width, max characters, etc. These options vary greatly based on the type of field you create. Currently, WorkXpress supports over 40 Field Types, from the familiar and common Text Box, Text Area (Long Text), Check Box, and Select Box to the advanced field types like Date/Time Pickers, Rich Formatted Text (Like a forum post body, blog entry, etc. where you have formatting controls), Item Selectors, File Uploads and Buttons.

Some field types map directly to INPUT elements, and some are a collection of Javascript and INPUT elements to create a very pleasant user experience using these fields. The goal here is to make each field type as naturally easy to use, and familiar as a select box.

Layouts

Stepping out of backend world for a second, every application needs an interface. Some of the elements of the interface are provided by the fields that are created/placed on a page. The remaining interface elements are provided by a block we call Layouts.

First, each page in WorkXpress is just that.. a Page Layout. This Layout has one important job : receive the information about what item we're going to show data for, and draw it's child layouts with that information.

Inside each Page Layout is a series of other layouts, of varying types. When a new Item Type is created, an Add Page and a Home Page (or View Page) are both created, a single Field Layout is created, and placed on both the Add and Home Pages. Then, the first field is created and placed on this Field Layout, giving the implementer a very basic framework to start with.

So, one major type of Layout is a Field Layout. This layout.. shows fields and allows those fields to be edited.

Another major type of Layout is a List Layout, or Related Item List. This layout shows a list of items that are attached to the item the page is about, via relationships. The implementer sets up what columns (Fields) are shown about each related item, and the Layout allows new relationships to be added, and existing relationships to be destroyed.

Relationships
Ok, so I have Contacts (Drew, John and Sally), and I have Companies (Express Dynamics, Best Buy) and I want to somehow store the fact that Drew is an Employee of Express Dynamics and John and Sally are employees of Best Buy. How do I do that? We have created relationships to track exactly this kind of situation.

First, you create the Relationship Type. Much like an Item Type, a Relationship Type describes the kinds of relationships we're going to make. In this case, we want to Relate Contacts to Companies, and we want a related Comapny to be called an "Employer" and a related Contact to be called an "Employee". These 4 pieces of info (Item Types on both sides, names on both sides) is all it takes to setup a relationship.

Second, we create some way to create these relationships. For this we have 2 constructs.

The Item Picker
The Item Picker is a type of field, that allows either 1 or many other items to be selected on an item. So, if you wanted a field on your Contact that allows you to select this Contact's Employer from the list of all Companies, you would create an Item picker on Contact, that selects Companies. On the item picker, you can choose a relationship for it to automatically create/maintain as the fields value is changed. It's important that we create the relationship as well as set the pickers value if we want to access this contacts employer later, through Actions.

An Item Picker with a value filled in (Employer Field on a Contact) will show the selected item's Name field, with it's Icon as a link to the selected item's Homepage.

The List (or Related Item List)
As described above, the List is a Type of Layout. It can be configured to use one or many relationships to show related items, and the implementer gets to pick what columns to show about the related item. So, in a List Layout one could show not only this Contact's Employer, but also its address, office line, yearly revanue, etc. Any other fields about Company the Implementer see's fit to show.

This is highly applicable on the other side, from the Company. One could show the first and last names, Email Address, Phone Number, Birthdate, etc. of each Employee attached to the Company (which there may be several).

Actions
Actions are programmatic functionality setup to be executed at various specific times while the application is in use. Actions can be added to Fields, Relationship Types or Item Types. Fields can have actions before and after saving, and before and after clearing that field. Relationship Types can have actions before and after creating a relationship of that type, before and after recycling a relationship of that type, and before and after deleting a relationship of that type. Items has a longer list, some of the highlights include before and after adding an item, updating and item, and recycling an item. These "times" when actions are executed are called "Events".

Each action can be configured to run on one or many events. So if you have something that you want to happen when a user adds a Contact, and anytime that Contact is updated, you can write one action and put it on both events.

Each action has 3 parts : evaluation, operation on pass, operation on fail.

Evaluations occur before any action takes place, the if in an if... then... else... type format, the evaluations job is to make sure that this action should run now...

Operation on pass and fail are the same, they are just the then... and else... parts in an if... then.. else... format. There are many Operation types that do many types of different things. They all have point and click interfaces to setup.

One quick example of an action is the age old "We want to store and sort contacts in a "Last Name, First Name" format.

No problem. Add an action on after add and after update with no evaluation. Make the Pass Operation a "Build String" operation, and begin building your value. You are presented with a map of information, starting on Contact (since you're writing a Contact Action), which shows all the fields on Contact. Click Last Name, "Finish", and you've added the first piece of the string. Then add just a Comma, and lastly choose First name from the map.

Then, you select a place to store this new string, which will also show a map, starting on Contact. Select "Contact Name", which is the field responsible for showing the Contact in pickers, and at the top of the homepage, etc.

You can also use actions to grab values from items through relationships. Let's say you have an "Employer Address" field on Contact. This field is supposed to show this Contacts, Employers, Address field. No Problem! Add an action, and use the map to navigate from Contact, across to Company through Employee -> Employer, and select the Address Field. Choose the local (Contact) "Employers Address" field, and save.

More on Items, Fields, Layouts, Actions and Relationships soon! Stay tuned!