Professional PHP5 (Programmer to Programmer Series)
ISBN: N/A
EAN: N/A
EAN: N/A
Year: 2003
Pages: 182
Pages: 182
The phrase "working data'' refers to the data collected by your application in its day-to-day operation. Here are a few examples:
In an e-commerce site, working data will be collected on orders placed by customers.
In the Sales Force Automation application, working data will be collected on the communications made with clients by sales people.
In a company Intranet featuring a time sheet facility, working data will be collected on the hours worked by employees on each account.
Not all data held and recorded in a database can be validly classified as working data. For example, information on user logins, access groups, sessions, and so forth relate solely to the administrative operation of the system and are not at the heart of it.
A bold statement: It is highly unlikely you will be able to detail your application's reporting requirements at the specification stage (see Chapter 19, "Project Management Methodologies'').
The first reason for this is that your client will not be able to see the wood for the trees, at this stage, and will be so caught up on what she wants the application to do that she may well struggle to take a step backward, approach the project from a different angle, and come up with details of the required reports.
The second reason is that in a high-pressure commercial environment, reports are needed only when they're needed. Think back to the days before computers. When a boss used to come over and ask a secretary for a report, the secretary didn't have the report already written and ready to be handed over to the boss right then and there. The same rule also applies nowadays. Reporting requirements are inextricably linked to commercial objectives and market conditions, which change rapidly and constantly. They are reactive.
With this in mind, it is perfectly reasonable to state in a functional specification that "this application will have a facility for producing reports, the exact nature of and implementation of which shall be determined in the weeks and months following the launch of the application.'' Of course, the onus remains on you or your project manager to ensure that this is a commercially viable promise.
The biggest challenge you will face in implementing any form of reporting system is ensuring that you are able to effectively manage the requests put forward by your client.
In practice, this means reading between the lines to allow you to stay one step ahead of your clients' requirements. They may struggle to articulate what they really need, irrespective of what they want. You can make life considerably easier for yourself if you attempt to side-step their own proposed solution (what they want) and get to their objectives instead (what they need). You'll meet an example of this dichotomy shortly.
The client instinctively tries to take ownership of designing the report. Often, they may see themselves as being helpful. In practice, their limited experience and narrow perspective on the task at hand can mean this is simply not true. Their own approach to the problem what they want will not accurately or completely solve the problem at hand what they need. Even if it does, you may well find yourself hugely limiting report re-use possibilities should you blindly follow their proposed solution.
Consider the following dialogue between you and your client, for whom you have built an online bookstore:
| CLIENT: | I want to be able to see how my June sales figures compare to my May sales figures. |
| YOU: | OK, that shouldn't be a problem. |
| CLIENT: | I'm particularly interested in the figures for any titles published by Acme Publishing Inc, so I'd like you to build me a report showing the figures for Acme in May compared to the figures for Acme in June, and the percentage change between the two. |
| YOU: | Sure. Give us a few hours and we'll have the report with you. |
Allow this approach and you'll be at the beck and call of your client for months, as they repeatedly request the same report with slightly different criteria, and you have to respond accordingly.
Instead, convince them to let you take ownership of the report design process. Let them take ownership of defining their objectives; but you should design the report.
How you achieve this very much depends on the relationship you have with the client. Some clients will be happy to bow to your experience and let you take over; with others, proactive psychology and clever language skills will be required.
However you approach it, you are looking for dialogues similar to the following, whereby you combine your knowledge of the client's business objectives with pure common sense to propose an attractive solution on the spot:
| CLIENT: | I want to be able to see how my June sales figures compare to my May sales figures. |
| YOU: | How about I build you a report showing sales volumes in dollars, whereby you select an author or group of authors, or publisher or group of publishers, and specify a couple of date ranges to compare? |
| CLIENT: | That's fine. Can I also have it showing the percentage difference between the two date ranges? |
| YOU: | Great idea. Okay, give us a day or two and we'll be good to go. |
In the previous dialogue, you quickly got to the objective at hand by discarding the chaff offered by the client (the months in question) and then rapidly identified likely useful criteria (authors, publishers, and dates) and communicated them to the client. One thing you weren't able to know was that the client wanted the results shown as a percentage as well as in dollars, but this information was duly offered.
In fact, one tactic for dealing with difficult clients can be to deliberately withhold relatively minor nuggets of information such as this so that they may suggest them. Of course, you may well have known them to be requirements all along, but letting your client suggest them leaves them feeling involved in the design process. You, of course, know that the design process is firmly in your hands.
It's worth discussing with your clients how they want the report to be presented. There are a number of potential formats, with each suited to a slightly different scenario.
What you should always avoid, of course, is verbal delivery. In the previous dialogue, the need was immediate, and the client would have been content with the answers over the phone. The emphasis, however, should always be on the client requesting and viewing all reports from within the application itself. We demonstrate later in this section how to produce such an interface for your clients.
Assuming electronic delivery, therefore, the following formats are among those available to you:
HTML This has the obvious advantage of familiarity and ease of use, plus seamless display on-screen in your client's Web browser. Also has the disadvantage of being notoriously difficult to print with any degree of fidelity. Probably best suited for small outputs.
PDF Using packages such as the R&OS PDF generation class (www.ros.co.nz/pdf), it is technically quite simple to produce PDF output using PHP. Unfortunately, the concept of the page as a canvas is taken a little too literally here. A great deal of time can be spent (or wasted, depending on your perspective) drawing tables and manually word-wrapping text. The resulting PDF, however, will always print perfectly and is often be favored by the client for this reason.
Microsoft Excel Some clients may actually want their report generated as an Excel spreadsheet so that they can play with the results. Obviously, if they were to tell you what they plan to do in Excel, you could build that into the report yourself and simply produce a PDF, but that is often not practical in reality. Mercifully, it is possible to produce an Excel spreadsheet using PHP; a suitable package can be found in PEAR (see http://pear.php.net/package/Spreadsheet_Excel_Writer for more details).
XML You may actually wish to consider producing your reports in XML and then translating to one of the previous three formats (or some other format) using XSLT or some other translation mechanism (such as Apache FOP see http://xml.apache.org/fop/). Although it takes a little longer to implement at the outset, this approach does have the benefit of allowing you to keep your results data extraction layers and presentation layers totally separate, thereby saving you time in the long run. This is the approach we adopt in this chapter.
There are three key steps to designing a report for your application: designing the user interface for requesting the report, determining what the output data needs to be, and figuring out how to extract that output data from the database.
Using our chosen architecture, which we describe shortly, you do not have to concern yourself with the appearance of the user interface on a report-by-report basis. Rather, you concern yourself with the parameters that the user may specify to produce the report.
For each report you are commissioned to write, you need to draw up a list of input criteria. For each criterion, you need to determine the following:
If a number, is this a minimum value, a maximum value, or an exact value?
If a string, is this an exact string or a partial match?
If a partial match, is this left-biased, right-biased, or not biased at all?
If a string, is it case sensitive?
If a maximum value parameter, is it inclusive or exclusive?
Is this parameter optional or mandatory?
What default value should be presented in the interface, if any?
What implicit value should be taken if optional and not specified?
In the previous book sales figures comparison example report, we might draw up a list of parameters to ask our user, as follows:
First date range start day, month, and year (exact, mandatory)
First date range end day, month, and year (exact, inclusive, mandatory)
Second date range start day, month, and year (exact, mandatory)
Second date range end day, month, and year (exact, inclusive, mandatory)
Publisher(s) to include (optional, implicit value any)
Author(s) to include (optional, implicit value any)
The preceding is an exhaustive list of our input parameters into our report. Next, we must decide what output information we want to see.
For the purposes of your sample report, don't concern yourself too much with appearance. Instead, assume that the output will be in XML so it can be presented in whatever format suits best, be it PDF, HTML, or another format.
The key data that needs to be displayed, however, is as follows:
A summary of the input criteria used to generate the results, to aid in making sense of the results
The sales in dollars in the first date range
The sales in dollars in the second date range
The difference in dollars between the two date ranges
The percentage difference (positive or negative) between the two date ranges
With this information in mind, we can now concern ourselves with how we arrive at the previous output from the input parameters upon which we have already decided.
The process of extracting the data in this report is relatively simple. In any report, though, this will almost always be achieved by means of SQL queries made against the database.
Throughout this book, you have been coached to make use of the GenericObject class to minimize your use of SQL queries, hence maximizing the portability of your application and the cleanliness of your code.
This counsel does not apply here, however.
There is little point in adapting your carefully crafted object hierarchy to arbitrarily support some obscure query you need to perform solely for the purpose of producing a report. You will find it makes your core application more difficult to support and maintain.
In addition, you should not consider your application and its database to be so inextricably linked. It is not beyond the realms of possibility that you might at some stage in the future introduce a Web service connector to your application written in some language other than PHP, which may read and write directly from the database.
Accordingly, you should extract the data directly from the database and concern yourself with optimizing your SQL queries to produce the desired output as quickly as possible, rather than somewhat awkwardly adjusting your SQL to fit snugly with your object model, as you did in Chapter 7.
The data set or sets you are producing will be constructed using the output of one or more SQL queries. Each SQL query may have to be adapted or built dynamically depending on the input criteria the user has specified.
For example, consider our book sales scenario; if the requesting user simply wants to know the volumes for two periods, you can use the following queries:
SELECT COUNT(id) FROM order WHERE date_placed >= '2004-05-01' AND date_placed <= '2004-05-31'; SELECT COUNT(id) FROM order WHERE date_placed >= '2004-03-01' AND date_placed <= '2004-03-31';
If, however, the report is a little more involved and further criteria have been specified, you need to amend the query slightly. Say, for example, that a particular author has been specified:
SELECT COUNT(id) FROM order WHERE date_placed >= '2004-05-01' AND date_placed <= '2004-05-31' AND book_id IN (SELECT id FROM book WHERE author_id=1295); SELECT COUNT(id) FROM order WHERE date_placed >= '2004-03-01' AND date_placed <= '2004-03-31' AND book_id IN (SELECT id FROM book WHERE author_id=1295);
The previous queries rely on a heavily simplified schema for the purposes of clarity, of course. In practice, support for more than one book will likely be allowed in any given order.
For any given report, it is very likely that there will be a number of possible query formats, depending on the verbosity or otherwise of criteria provided. Using a switch statement to account for each combination of criteria would be unwise because this could quickly run to hundreds of permutations for a complex criteria set.
Instead, it's best to use standard conditional logic to build the SQL query as a string, based on the criteria supplied.
Consider the following code snippet:
$strSQL = "SELECT COUNT(id) FROM order WHERE date_placed >= '$from_date' AND date_placed <= '$until_date'"; if ($author_id) { $strSQL .= "AND book_id IN (SELECT id FROM book WHERE author_id=$author_id)"; }; if ($publish_id) { $strSQL .= "AND book_id IN (SELECT id FROM book WHERE publisher_id = $publish_id)"; };
As demonstrated, you have dynamically constructed the SQL query based on the criteria supplied; using conditionals, you have appended to a base SQL query and increased its complexity depending on the number of criteria actually supplied.
This theory can be applied to the nth degree when designing your report. Consider the input criteria which must be supplied and compare with the input criteria which might be supplied. Hard code the query where you can be sure input criteria will be supplied; use conditionals where it can be supplied.