Rev 20 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<html>
<body>
<h1>
reports.php
</h1>
<p>
reports.php is a class for storing simple report information in a database so it can be rapidly integrated into an application. It consists of two classes <strong>Report</strong> and <strong>QueryParameters</strong>, with <strong>QueryParameters</strong> being a class used by Report to parse, display and hold the values of the parameters to the query
</p>
<h2>
Expected database structure
</h2>
The database is expected to, at a minimum, contain the following columns:
<table border="1">
<caption>
Data structure for Reports table
</caption>
<thead align="center" bgcolor="lightGray">
<tr>
<th>
Column Name
</th>
<th>
Data Type
</th>
<th>
Contents
</th>
<th>
Notes
</th>
</tr>
</thead>
<tbody align="left" valign="top">
<tr>
<td>
report_id
</td>
<td>
int
</td>
<td>
Unique Identifer for each row
</td>
<td>
Generally set up as int unsigned not null auto_increment
</td>
</tr>
<tr>
<td>
name
</td>
<td>
varchar(20)
</td>
<td>
Display name of report
</td>
<td>
This is displayed to the client when they choose which report to generate
</td>
</tr>
<tr>
<td>
query
</td>
<td>
text
</td>
<td>
An SQL select statement with with optional parameters
</td>
<td>
Parameters are of the form <<em>parameter_name</em>>, where the < and > are delimiters. parameter_name must be alphanumeric. See note below for a special, runtime parameter to limit the same report based upon programmatic input.
</td>
</tr>
<tr>
<td>
parameters
</td>
<td>
text
</td>
<td>
Parameters, in a special format, for the query.
</td>
<td>
parameters is a single line for each parameter in the column query. The line contains 4 values, delimited by the special sequence <em>++</em>, but delimiter may be redefined using the parametersDelimiter setter/getter in the QueryParameters class. See below for more information on this column
</td>
</tr>
</tbody>
</table>
<p><strong>Special Parameters</strong>: the special parameter <additionalLimitations>, is provided to allow a program to filter more limited information based upon the user running the report. For example, if a user should only see a subset of the total report, and a second user see a different subset, this parameter will allow runtime modification of this. To use this parameter, you must do the two following things:</p>
<ul>
<li>In your query, surround all of your where clause (exclusive of the keyword <em>where</em>) in parentheses. At the outside of this block, place the special token <additionalLimitations></li>
<li>In your call to Report->run, include the first parameter (empty, if need be), then pass the additional values to be use to limit in the second parameter.</li>
</ul>
<p>Examples below assume <additionalLimitations> is set to <em>(owner_id=5)</em></p>
<table width="100%" border="1">
<caption><additionalLimitations> Parameter Example</caption>
<thead bgcolor="lightGray">
<tr>
<th>Query</th>
<th>Result</th>
</tr>
</thead>
<tbody>
<tr>
<td>select * from table1 where ( table1.col1 = 'joe' ) <additionalLimitations></td>
<td>select * from table1 where ( table1.col1 = 'joe' ) and (owner_id=5)</td>
</tr>
<tr>
<td>select lastname,firstname from address where (zip_code in ('75214','75206'))<additionalLimitations></td>
<td>select lastname,firstname from address where (zip_code in ('75214','75206')) and (owner_id=5)</td>
</tr>
<tr>
<td>select username, permissions from login where (login like '%rodo%)select lastname,firstname from address where (zip_code in ('75214','75206'))<additionalLimitations></td>
<td>select username, permissions from login where (login like '%rodo%)select lastname,firstname from address where (zip_code in ('75214','75206')) and (owner_id=5)</td>
</tr>
</tbody>
</table>
<p><em>Note that if no parameter is passed in, <additionalLimitations> is set to the empty string so the above queries are still valid.</em></p>
<h3>
parameters column
</h3>
<p>
The parameters column is actually a series of one or more lines, with each line corresponding to one parameter in the query column. Each line (parameter entry) consists of four values, delimited by the special string <em>++</em> (this may be redefined by QueryParameters->parametersDelimiter). The four values are defined in the following table
</p>
<table border="1">
<caption>
Parameters Entry Definition
</caption>
<thead bgcolor="lightGray">
<tr>
<th>
Entry
</th>
<th>
Name
</th>
<th>
Notes
</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td>
0
</td>
<td>
name
</td>
<td>
parameter name in query, ie what is between the < and > in a parameterized query
</td>
</tr>
<tr>
<td>
1
</td>
<td>
Prompt
</td>
<td>
Display Prompt for user. Displayed when user runs a report and the class wants to know what the value is for a parameter.
</td>
</tr>
<tr>
<td>
2
</td>
<td>
query
</td>
<td>
If defined, query is executed and a drop down list (select) is created from the results. Query returns two columns. First column is the value returned by the select, second column is the prompt shown to user.
</td>
</tr>
<tr>
<td>
3
</td>
<td>
Display Query
</td>
<td>
Normally, the report title is simply a list of the parameters used to generate it. If, however, this field is defined, it will be used as the title. The special parameter <value> will contain the value chosen by the user from query above.
</td>
</tr>
</tbody>
</table>
<p>
A real world example would be a class schedule, where a report was designed to create a roster of students signed up for a specific class. The table structures are defined as:
</p>
<pre> create table classes (
classes_id int unsigned not null auto_increment,
instructor_id int unsigned not null comment 'link into instructors table',
location_id int unsigned not null comment 'link into locations table',
levels_id int unsigned not null comment 'lin to levels table',
start_time datetime not null comment 'date and time class starts',
end_time datetime not null comment 'data and time class ends',
price decimal(5,2) not null comment 'Cost in dollars',
max_students int unsigned default 1 comment 'maximum number of students who can sign up',
primary key (classes_id)
) comment 'Table will hold class information for all classes';
create table instructor (
instructor_id int unsigned not null auto_increment,
name varchar(20) comment 'display name of instructor',
description text comment 'Additional information about the instructor',
primary key (instructor_id)
) comment 'Holds information about instructor';
create table location (
location_id int unsigned not null auto_increment,
name varchar(64) comment 'display name of location',
information text comment 'any additional information to be displayed',
primary key (location_id)
) comment 'holds information about each location';
create table student_classes (
student_id int unsigned not null comment 'Student ID of this student',
classes_id int unsigned not null comment 'classes_id of the class they are signed up for',
date_entered timestamp comment 'so we can track when this record was created',
payment_valid varchar(128) default null comment 'Paypal validation code, or null if not yet paid for',
primary key (student_id,classes_id)
) comment 'Link table between students and classes';
create view students_registered as select * from student_classes where payment_valid is not null;
create table student (
student_id int unsigned not null auto_increment,
name varchar(20) comment 'display name of student',
adult varchar(64) comment 'information on parent/guardian',
contact text comment 'contact information on adult/student',
primary key (student_id)
) comment 'information about students signed up for a class';
</pre>
<p>
In this case, we want a report that displays all of the students signed up for a particular class. We will call the report Class Roster, and the query will be
</p>
<pre> select student.name 'Student',
student.adult 'Responsible Party',
student.contact 'Contact Info'
from student join student_classes using (student_id)
join classes using (classes_id)
where classes_id = <classes_id>
</pre>
<p>
Additionally, we want to display a title on the report showing which instructor, class, etc... were used in the selection. This comes from the Display Query parameter.
</p>
<p>
Note that we want one parameter, which we have called classes_id, to be input by the user when they generate this report. Thus, we will have a parameter. The paraemters for this particular case are shown in the following table
</p>
<table border="1">
<caption>
Parameter for classes_id
</caption>
<thead align="center" bgcolor="lightGray">
<tr>
<th>
Position
</th>
<th>
Parameter
</th>
<th>
Value
</th>
<th>
Notes
</th>
</tr>
</thead>
<tbody valign="top">
<tr>
<td>
0
</td>
<td>
name
</td>
<td>
classes_id
</td>
<td>
same as the <classes_id> in the query
</td>
</tr>
<tr>
<td>
1
</td>
<td>
prompt
</td>
<td>
Select Class
</td>
<td>
Displayed to user
</td>
</tr>
<tr>
<td>
2
</td>
<td>
query
</td>
<td>
select classes_id,concat(start_time,' - ',instructor.name,' - ',location.name) class from classes join instructor using (instructor_id) join location using (location_id)
</td>
<td>
Since query is defined, a <select> will be created. The value of the options will be taken from the classes.classes_id, and the display value will be teh concat.
</td>
</tr>
<tr>
<td>
3
</td>
<td>
Display Query
</td>
<td>
select concat(start_time,' - ',instructor.name,' - ',location.name) class from classes join instructor using (instructor_id) join location using (location_id) where classes.classes_id=<value>
</td>
<td>
The title of the report will get its value from this.
</td>
</tr>
</tbody>
</table>
<h3>
How it would work
</h3>
<p>
For the above definition, the report class would generate a single form with a prompt <em>Choose Class</em>, and a <select> displaying a list of all classes. The user would then select the class they wanted a report for and click the Submit button.
</p>
<p>
The <strong>Report</strong> class would then acquire the information from the form (with help from the <strong>QueryParameters</strong> class), taking the value returned from the <select> and replacing <class_id> with that in the query. The query is then run and formatted into a table. Then, <em>Display Query</em> (from the parameter) has <value> replaced with the same value returned from the <select> and it is run, with the result being used for the title (multiple <em>Display Query</em>'s, say in multiple parameter entries, would be concated). The report is then displayed.
</p>
<h2>
Report class
</h2>
<p>
The report class is designed to be semi automatic. So long as the report is correctly defined in the database, a simple PHP bloc will allow the user to select the report they wish to display, show parameters and wait for user input, then display the report. An example follows:
</p>
<pre> <?php
include_once('reports.php');
if ( $_POST['action'] == 'run' ) { // we have a report name, and all the parameters
$thisReport = new Report();
$thisReport->loadFromDatabase($_POST['report']);
print $thisReport->run( );
} elseif ($_POST['report']) { // a report is defined, so let's show the parameters for it
print '<FORM action="' . $_SERVER['PHP_SELF'] . '" method="POST" enctype="multipart/form-data">';
print "<INPUT type='hidden' name='report' value='" . $_POST['report'] . "'>";
print "<INPUT type='hidden' name='action' value='run'>";
$thisReport = new Report();
$thisReport->loadFromDatabase($_POST['report']);
print $thisReport->parameterInputScreen();
print '<INPUT type="submit" name="Submit" value="Submit">';
print '</form>';
} else {
print '<FORM action="' . $_SERVER['PHP_SELF'] . '" method="POST" enctype="multipart/form-data">';
print "<select name='report'>\n" . Report::listAllReports() . "</select>";
print '<INPUT type="submit" name="Submit" value="Submit">';
print '</form>';
}
?>
</pre>
<p>
This block is in reverse order, ie the first conditional is the last section to be executed.
</p>
<p>
The first section (<em>if</em>) assumes we have a report, and all parameter values have been entered. It then simply calls loadFromDatabase with the report id, then runs the report (with run()). The output is immediately displayed.
</p>
<p>
The second section (<em>elsif</em>) knows a report has been requested, but since we fell through the first one, we have not got the values for the parameters. So, we create a form using the parameterInputScreen function, which will create a table with the required input fields so the user can put in values for all the parameters. Note that I left the <form> tag out of the generation. This allows the programmer to add additional tags, if necessary.
</p>
<p>
The third section (<em>else</em>) is the one executed first. We have no parameters. Thus we call the static Reports function listAllReports, which simply creates a <select> with a list of all reports defined in the reports table in the database.
</p>
<p>
<strong>Note:</strong>The delimiter used in the parameters can be set with Report->parametersDelimiter(<em>new_delimiter</em>). This function will return the old delimiter or, if called with no parameter, will return the current delimiter (without modifying it)
</p>
<p>
<strong>Warning:</strong> In this iteration of the Report class, there is little error checking. If you create a bad report definition, or if the user does not fill in the parameters correctly, the report will die with an ugly error. I intend to make the error checking more robust in the future, but it remains a problem in this version.
</p>
<h2>
QueryParameters class
</h2>
<p>
The queryParameters class is not designed to be called directly. It is used exetensively by the Report class, but that is its only function so far. If you are curious about how it was built, read the internal documentation in the code itself.
</p>
<h2>
Creating a Report Definition
</h2>
<p>
Creating a report definition is non-trivial, and has a lot of room for error. One of the plans for the next version is a Report->CreateDefinition() which will solve this.
</p>
</body>
</html>