Subversion Repositories php_library

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
14 rodolico 1 
<html>
2 
  <body>
3 
    <h1>
4 
       reports.php
5 
    </h1>
6 
    <p>
7 
       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
8 
    </p>
9 
    <h2>
10 
       Expected database structure
11 
    </h2>
12 
     The database is expected to, at a minimum, contain the following columns:
13 
    <table border="1">
14 
      <caption>
15 
         Data structure for Reports table
16 
      </caption>
17 
      <thead align="center" bgcolor="lightGray">
18 
        <tr>
19 
          <th>
20 
             Column Name
21 
          </th>
22 
          <th>
23 
             Data Type
24 
          </th>
25 
          <th>
26 
             Contents
27 
          </th>
28 
          <th>
29 
             Notes
30 
          </th>
31 
        </tr>
32 
      </thead>
33 
      <tbody align="left" valign="top">
34 
        <tr>
35 
          <td>
36 
             report_id
37 
          </td>
38 
          <td>
39 
             int
40 
          </td>
41 
          <td>
42 
             Unique Identifer for each row
43 
          </td>
44 
          <td>
45 
             Generally set up as int unsigned not null auto_increment
46 
          </td>
47 
        </tr>
48 
        <tr>
49 
          <td>
50 
             name
51 
          </td>
52 
          <td>
53 
             varchar(20)
54 
          </td>
55 
          <td>
56 
             Display name of report
57 
          </td>
58 
          <td>
59 
             This is displayed to the client when they choose which report to generate
60 
          </td>
61 
        </tr>
62 
        <tr>
63 
          <td>
64 
             query
65 
          </td>
66 
          <td>
67 
             text
68 
          </td>
69 
          <td>
70 
             An SQL select statement with with optional parameters
71 
          </td>
72 
          <td>
73 
             Parameters are of the form &lt;<em>parameter_name</em>&gt;, where the &lt; and &gt; are delimiters. parameter_name must be alphanumeric
74 
          </td>
75 
        </tr>
76 
        <tr>
77 
          <td>
78 
             parameters
79 
          </td>
80 
          <td>
81 
             text
82 
          </td>
83 
          <td>
84 
             Parameters, in a special format, for the query.
85 
          </td>
86 
          <td>
87 
             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
88 
          </td>
89 
        </tr>
90 
      </tbody>
91 
    </table>
92 
    <h3>
93 
       parameters column
94 
    </h3>
95 
    <p>
96 
       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
97 
    </p>
98 
    <table border="1">
99 
      <caption>
100 
         Parameters Entry Definition
101 
      </caption>
102 
      <thead bgcolor="lightGray">
103 
        <tr>
104 
          <th>
105 
             Entry
106 
          </th>
107 
          <th>
108 
             Name
109 
          </th>
110 
          <th>
111 
             Notes
112 
          </th>
113 
        </tr>
114 
      </thead>
115 
      <tbody valign="top">
116 
        <tr>
117 
          <td>
118 
 
119 
          </td>
120 
          <td>
121 
             name
122 
          </td>
123 
          <td>
124 
             parameter name in query, ie what is between the &lt; and &gt; in a parameterized query
125 
          </td>
126 
        </tr>
127 
        <tr>
128 
          <td>
129 
             1
130 
          </td>
131 
          <td>
132 
             Prompt
133 
          </td>
134 
          <td>
135 
             Display Prompt for user. Displayed when user runs a report and the class wants to know what the value is for a parameter.
136 
          </td>
137 
        </tr>
138 
        <tr>
139 
          <td>
140 
             2
141 
          </td>
142 
          <td>
143 
             query
144 
          </td>
145 
          <td>
146 
             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.
147 
          </td>
148 
        </tr>
149 
        <tr>
150 
          <td>
151 
             3
152 
          </td>
153 
          <td>
154 
             Display Query
155 
          </td>
156 
          <td>
157 
             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 &lt;value&gt; will contain the value chosen by the user from query above.
158 
          </td>
159 
        </tr>
160 
      </tbody>
161 
    </table>
162 
    <p>
163 
       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:
164 
    </p>
165 
    <pre> create table classes (
166 
   classes_id     int unsigned not null auto_increment,
167 
   instructor_id  int unsigned not null comment 'link into instructors table',
168 
   location_id    int unsigned not null comment 'link into locations table',
169 
   levels_id      int unsigned not null comment 'lin to levels table',
170 
   start_time     datetime not null comment 'date and time class starts',
171 
   end_time       datetime not null comment 'data and time class ends',
172 
   price          decimal(5,2) not null comment 'Cost in dollars',
173 
   max_students   int unsigned default 1 comment 'maximum number of students who can sign up',
174 
   primary key    (classes_id)
175 
) comment 'Table will hold class information for all classes';
176 
 
177 
create table instructor (
178 
   instructor_id  int unsigned not null auto_increment,
179 
   name           varchar(20) comment 'display name of instructor',
180 
   description    text comment 'Additional information about the instructor',
181 
   primary key    (instructor_id)
182 
) comment 'Holds information about instructor';
183 
 
184 
create table location (
185 
   location_id    int unsigned not null auto_increment,
186 
   name           varchar(64) comment 'display name of location',
187 
   information    text comment 'any additional information to be displayed',
188 
   primary key    (location_id)
189 
) comment 'holds information about each location';
190 
 
191 
create table student_classes (
192 
   student_id     int unsigned not null comment 'Student ID of this student',
193 
   classes_id     int unsigned not null comment 'classes_id of the class they are signed up for',
194 
   date_entered   timestamp comment 'so we can track when this record was created',
195 
   payment_valid  varchar(128) default null comment 'Paypal validation code, or null if not yet paid for',
196 
   primary key    (student_id,classes_id)
197 
) comment 'Link table between students and classes';
198 
 
199 
create view students_registered as select * from student_classes where payment_valid is not null;
200 
 
201 
create table student (
202 
   student_id     int unsigned not null auto_increment,
203 
   name           varchar(20) comment 'display name of student',
204 
   adult          varchar(64) comment 'information on parent/guardian',
205 
   contact        text comment 'contact information on adult/student',
206 
   primary key    (student_id)
207 
) comment 'information about students signed up for a class';
208 
    </pre>
209 
    <p>
210 
       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
211 
    </p>
212 
    <pre> select student.name 'Student',
213 
       student.adult 'Responsible Party',
214 
       student.contact 'Contact Info'
215 
from student join student_classes using (student_id)
216 
     join classes using (classes_id)
217 
where classes_id = &lt;classes_id&gt;
218 
    </pre>
219 
    <p>
220 
       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.
221 
    </p>
222 
    <p>
223 
       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
224 
    </p>
225 
    <table border="1">
226 
      <caption>
227 
         Parameter for classes_id
228 
      </caption>
229 
      <thead align="center" bgcolor="lightGray">
230 
        <tr>
231 
          <th>
232 
             Position
233 
          </th>
234 
          <th>
235 
             Parameter
236 
          </th>
237 
          <th>
238 
             Value
239 
          </th>
240 
          <th>
241 
             Notes
242 
          </th>
243 
        </tr>
244 
      </thead>
245 
      <tbody valign="top">
246 
        <tr>
247 
          <td>
248 
 
249 
          </td>
250 
          <td>
251 
             name
252 
          </td>
253 
          <td>
254 
             classes_id
255 
          </td>
256 
          <td>
257 
             same as the &lt;classes_id&gt; in the query
258 
          </td>
259 
        </tr>
260 
        <tr>
261 
          <td>
262 
             1
263 
          </td>
264 
          <td>
265 
             prompt
266 
          </td>
267 
          <td>
268 
             Select Class
269 
          </td>
270 
          <td>
271 
             Displayed to user
272 
          </td>
273 
        </tr>
274 
        <tr>
275 
          <td>
276 
             2
277 
          </td>
278 
          <td>
279 
             query
280 
          </td>
281 
          <td>
282 
             select classes_id,concat(start_time,' - ',instructor.name,' - ',location.name) class from classes join instructor using (instructor_id) join location using (location_id)
283 
          </td>
284 
          <td>
285 
             Since query is defined, a &lt;select&gt; will be created. The value of the options will be taken from the classes.classes_id, and the display value will be teh concat.
286 
          </td>
287 
        </tr>
288 
        <tr>
289 
          <td>
290 
             3
291 
          </td>
292 
          <td>
293 
             Display Query
294 
          </td>
295 
          <td>
296 
             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=&lt;value&gt;
297 
          </td>
298 
          <td>
299 
             The title of the report will get its value from this.
300 
          </td>
301 
        </tr>
302 
      </tbody>
303 
    </table>
304 
    <h3>
305 
       How it would work
306 
    </h3>
307 
    <p>
308 
       For the above definition, the report class would generate a single form with a prompt <em>Choose Class</em>, and a &lt;select&gt; displaying a list of all classes. The user would then select the class they wanted a report for and click the Submit button.
309 
    </p>
310 
    <p>
311 
       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 &lt;select&gt; and replacing &lt;class_id&gt; with that in the query. The query is then run and formatted into a table. Then, <em>Display Query</em> (from the parameter) has &lt;value&gt; replaced with the same value returned from the &lt;select&gt; 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.
312 
    </p>
313 
    <h2>
314 
       Report class
315 
    </h2>
316 
    <p>
317 
       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:
318 
    </p>
319 
    <pre> &lt;?php
320 
         include_once('reports.php');
321 
 
322 
         if ( $_POST['action'] == 'run' ) { // we have a report name, and all the parameters
323 
            $thisReport = new Report();
324 
            $thisReport-&gt;loadFromDatabase($_POST['report']);
325 
            print $thisReport-&gt;run(  );
326 
         } elseif ($_POST['report']) { // a report is defined, so let's show the parameters for it
327 
            print '&lt;FORM action="' . $_SERVER['PHP_SELF'] . '" method="POST" enctype="multipart/form-data"&gt;';
328 
            print "&lt;INPUT type='hidden' name='report' value='" . $_POST['report'] . "'&gt;";
329 
            print "&lt;INPUT type='hidden' name='action' value='run'&gt;";
330 
            $thisReport = new Report();
331 
            $thisReport-&gt;loadFromDatabase($_POST['report']);
332 
            print $thisReport-&gt;parameterInputScreen();
333 
            print '&lt;INPUT type="submit" name="Submit" value="Submit"&gt;';
334 
            print '&lt;/form&gt;';
335 
         } else {
336 
            print '&lt;FORM action="' . $_SERVER['PHP_SELF'] . '" method="POST" enctype="multipart/form-data"&gt;';
337 
            print "&lt;select name='report'&gt;\n" . Report::listAllReports() . "&lt;/select&gt;";
338 
            print '&lt;INPUT type="submit" name="Submit" value="Submit"&gt;';
339 
            print '&lt;/form&gt;';
340 
         }
341 
      ?&gt;
342 
    </pre>
343 
    <p>
344 
       This block is in reverse order, ie the first conditional is the last section to be executed.
345 
    </p>
346 
    <p>
347 
       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.
348 
    </p>
349 
    <p>
350 
       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 &lt;form&gt; tag out of the generation. This allows the programmer to add additional tags, if necessary.
351 
    </p>
352 
    <p>
353 
       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 &lt;select&gt; with a list of all reports defined in the reports table in the database.
354 
    </p>
355 
    <p>
356 
      <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)
357 
    </p>
358 
    <p>
359 
      <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.
360 
    </p>
361 
    <h2>
362 
       QueryParameters class
363 
    </h2>
364 
    <p>
365 
       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.
366 
    </p>
367 
    <h2>
368 
       Creating a Report Definition
369 
    </h2>
370 
    <p>
371 
       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.
372 
    </p>
373 
  </body>
374 
</html>