Subversion Repositories php_library

<?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.

                       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

?>