Rev 47 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
/*
Author: R. W. Rodolico
Date: 20090403
Class which holds basic reports for generation in client programs.
Assumes a database table of at least the following format (all additional columns ignored)
create table report (
report_id int unsigned not null auto_increment,
name varchar(20) not null comment 'Display Name of Report',
query text not null comment 'Query to be executed',
parameters text comment 'All parameters used in above',
primary key (report_id)
) comment 'holds definition for report';
Columns are defined as:
name -- This is the name of the report for display. This is displayed by Report::listAllReports
query -- an SQL Query for the report. Parameters may be inserted by surrounding them with '<' and '>'
and these parameters will be replaced by user input values on execution.
parameters -- List of parameter information to form query. These are one parameter per line. Each line
is a $parametersDelimiter delimited string of data.
0 - parameter name in query
1 - user prompt for value
2 - query.
3 - displayQuery
This class is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>
*/
/* class QueryParameters
Designed as a simple helper class to Reports. It simplified the writing of the Reports class by abstracting
all of the parameters functions. The parameters are a very complex topic in and of themselves.
*/
class QueryParameters {
protected $parameter; // parameter name. matches <param_name> in report query
protected $prompt; // user prompt when requesting a value for this parameter
protected $query; // if defined, generates a <select> block to acquire the value for this parameter instead of an <INPUT> block
protected $displayQuery; // if defined, result is concated with any other displayQuery results for the title of the report
protected $value; // value used for displayQuery. See documentation
private $parametersDelimiter = '++'; // parameter delimiter
function __constructor ( $parameter = '', $prompt = '', $query = '' ) {
$this->parameter = $parameter;
$this->prompt = $prompt;
$this->query = $query;
}
/* following functions are setters/getters for the class members */
function parametersDelimiter ( $newDelimiter = '' ) {
$oldValue = $this->parametersDelimiter;
if ($newDelimiter) {
$this->parametersDelimiter = $newDelimiter;
}
return $oldValue;
}
function name ( $newParameter = '' ) {
$oldParameter = $this->parameter;
if ($newParameter) {
$this->parameter = $newParameter;
}
return $oldParameter;
}
function value ( $newValue = '' ) {
$oldValue = $this->value;
if ($newValue) {
$this->value = $newValue;
}
return $oldValue;
}
function prompt ( $newPrompt = '' ) {
$oldPrompt = $this->prompt;
if ($newPrompt) {
$this->prompt = $newPrompt;
}
return $oldPrompt;
}
function query ( $newQuery = '' ) {
$oldQuery = $this->query;
if ($newQuery) {
$this->query = $newQuery;
}
return $oldQuery;
}
function displayQuery ( $newQuery = '' ) {
$oldQuery = $this->displayQuery;
if ($newQuery) {
$this->displayQuery = $newQuery;
}
return $oldQuery;
}
// takes a single line of a parameters entry and breaks it into its values
function stringToParameter( $string ) {
$temp = explode( $this->parametersDelimiter, $string );
$this->parameter = $temp[0];
$this->prompt = $temp[1];
$this->query = $temp[2];
$this->displayQuery = $temp[3];
}
/*
creates a td with the prompt, and an <input> enter the value for that parameter.
If query is defined, will generate a <select> instead
*/
function toHTML () {
$result = "<td>$this->prompt</td><td>";
if ( $this->query ) {
$result .= "<select name='report_param_$this->parameter'>" . queryToSelect( $this->query ) . '</select>';
} else {
$result .= "<input type='text' name='report_param_$this->parameter'>";
}
$result .= '</td>';
return $result;
}
} // class QueryParameters
/*
the Report class is designed to be semi-automatic. It can, with little code, create a <select> containing the available reports,
display any parameters required for that report, then run the report and return a table containing the results.
*/
class Report {
protected $name; // name of report
protected $title; // title for the report
protected $query; // query to generate the report
protected $parameters = array(); // array of parameters entries for the report. Class QueryParameters
private $tableName = 'report';
private $nameField = 'name';
private $queryField = 'query';
private $parametersField = 'parameters';
private $idField = 'report_id';
/* will simply get a list of all reports from the table and allow the user to select one */
static public function listAllReports( $tableName = 'report', $nameField = 'name', $idField = 'report_id' ) {
return queryToSelect( "select $idField, $nameField from $tableName" );
}
function __constructor ($name = '', $title = '', $query = '', $parameters = array() ) {
$this->name = $name;
$this->title = $title;
$this->query = $query;
$this->parameters = $parameters;
}
/* setters/getters for the various class members */
function name ( $newName = '' ) {
$oldName = $this->name;
if ($newName) {
$this->name = $newName;
}
return $oldName;
}
function query ( $newQuery = '' ) {
$oldQuery = $this->query;
if ($newQuery) {
$this->query = $newQuery;
}
return $oldQuery;
}
function parameters ( $newParameters = array() ) {
$oldParameters = $this->parameters;
if ($newParameters) {
$this->parameters = $newParameters;
}
return $oldParameters;
}
function addParameter ( $newParameter = array() ) {
$this->parameters[] = $newParameter;
}
/* get the report definition from the database. Will decipher the definition, filling out all class members with their values */
function loadFromDatabase ( $id ) {
$sql = "select $this->nameField, $this->queryField, $this->parametersField from $this->tableName where $this->idField = $id";
$result = queryDatabaseExtended( $sql );
$result = $result['data'][0];
$this->name = $result[$this->nameField];
$this->title = $result[$this->nameField];
$this->query = $result[$this->queryField];
$parameters = $result[$this->parametersField];
$parameters = explode("\n", $parameters);
foreach ( $parameters as $thisParam ) {
if (! $thisParam ) {
continue;
}
$temp = new QueryParameters();
$temp->stringToParameter($thisParam);
$this->parameters[] = $temp;
}
}
/*
runs the report
$parameters, if passed in, is an array of paramtername=>value and will be used in place of any values on the form
$additionalLimitations is a partial where clause. When passed in, it will replace the special parameter <additionalLimitations>.
if it is not passed in, <additionalLimitations> will be removed
$makeTitle, if set to false, will simply return the data in a table. If true, will return the title and the data
The return value is the title in an H1, the conditions in H2's, and the results in a table
*/
function run( $parameters = '', $additionalLimitations = '', $makeTitle = true, $titleTags = array('startTag' => '<h4>', 'endTag' => '</h4>', 'conditionStart' => '<h2>', 'conditionEnd' => '</h2>' ) ) {
$conditions = array();
foreach ( $this->parameters as $parameter ) {
if ( $parameters[$parameter->name()] ) { // in the array passed in
$parameter->value( $parameters[$parameter->name()] );
} elseif ($_POST['report_param_' . $parameter->name()]) { // in $_POST
$parameter->value($_POST['report_param_' . $parameter->name()]);
}
}
// print "<pre>"; print_r( $this ); print "</pre>";
foreach ( $this->parameters as $parameter ) {
$toFind = '<' . $parameter->name() . '>';
$this->query = preg_replace( "/$toFind/", $parameter->value(), $this->query );
if ( $parameter->displayQuery() ) {
//$result = preg_replace( '/<value>/', $parameter->value(), $parameter->displayQuery() );
$sql = preg_replace( '/<value>/', $parameter->value(), $parameter->displayQuery() );
// print "<pre>$sql</pre>\n";
$result = queryDatabaseExtended($sql , false);
//print "<pre>" . print_r( $result ); print "</pre>";
$conditions[] = $parameter->name() . ' - ' . $result['data'][0][0];
} else {
$conditions[] = $parameter->name() . ' - ' . $parameter->value();
}
}
if ($additionalLimitations) { // the user wants us to limit additionally
$this->query = preg_replace( '/<additionalLimitations>/', ' and ' . $additionalLimitations, $this->query );
} else {
$this->query = preg_replace( '/<additionalLimitations>/', '', $this->query );
}
// print "<pre>$this->query</pre>";
$return = queryToTable($this->query);
if ( $return && $makeTitle ) {
$return = $titleTags['startTag'] . $this->title . $titleTags['endTag'] .
$titleTags['conditionStart'] .
implode($titleTags['conditionEnd'] . $titleTags['conditionStart'], $conditions) .
$titleTags['conditionEnd'] .
$return;
}
return $return;
}
/*
Creates a table with the necessary INPUT or SELECT fields to get the parameter values from the user
returns a table suitable for inserting into a FORM
*/
function parameterInputScreen () {
$result = "<table border='1'>\n";
$result .= "<tr><td colspan='2'>$this->name</td></tr>";
foreach ( $this->parameters as $parameter ) {
$result .= '<tr>' . $parameter->toHTML() . '</tr>';
}
return $result . '</table>';
}
// kept because some code used the old name. I messed up when I wrote the original. I don't think the name is indicative of the
// use of this function. This will be removed in the future.
function toHTML( ) {
return parameterInputScreen();
}
} // class Report
?>