Subversion Repositories php_library

Rev

Rev 20 | Details | Compare with Previous | 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>
20 rodolico 73 
             Parameters are of the form &lt;<em>parameter_name</em>&gt;, where the &lt; and &gt; are delimiters. parameter_name must be alphanumeric. See note below for a special, runtime parameter to limit the same report based upon programmatic input.
14 rodolico 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>
20 rodolico 92 
    <p><strong>Special Parameters</strong>: the special parameter &lt;additionalLimitations&gt;, 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>
93 
   <ul>
94 
      <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 &lt;additionalLimitations&gt;</li>
95 
      <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>
96 
   </ul>
97 
    <p>Examples below assume &lt;additionalLimitations&gt; is set to <em>(owner_id=5)</em></p>
98 
   <table width="100%" border="1">
99 
  <caption>&lt;additionalLimitations&gt; Parameter Example</caption>
100 
  <thead bgcolor="lightGray">
101 
    <tr>
102 
      <th>Query</th>
103 
      <th>Result</th>
104 
    </tr>
105 
  </thead>
106 
  <tbody>
107 
    <tr>
108 
      <td>select * from table1 where ( table1.col1  = 'joe' ) &lt;additionalLimitations&gt;</td>
109 
      <td>select * from table1 where ( table1.col1  = 'joe' )  and (owner_id=5)</td>
110 
    </tr>
111 
    <tr>
112 
      <td>select lastname,firstname from address where (zip_code in ('75214','75206'))&lt;additionalLimitations&gt;</td>
113 
      <td>select lastname,firstname from address where (zip_code in ('75214','75206')) and (owner_id=5)</td>
114 
    </tr>
115 
    <tr>
116 
      <td>select username, permissions from login where (login like '%rodo%)select lastname,firstname from address where (zip_code in ('75214','75206'))&lt;additionalLimitations&gt;</td>
117 
      <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>
118 
    </tr>
119 
  </tbody>
120 
</table>
121 
<p><em>Note that if no parameter is passed in, &lt;additionalLimitations&gt; is set to the empty string so the above queries are still valid.</em></p>
14 rodolico 122 
    <h3>
123 
       parameters column
124 
    </h3>
125 
    <p>
126 
       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
127 
    </p>
128 
    <table border="1">
129 
      <caption>
130 
         Parameters Entry Definition
131 
      </caption>
132 
      <thead bgcolor="lightGray">
133 
        <tr>
134 
          <th>
135 
             Entry
136 
          </th>
137 
          <th>
138 
             Name
139 
          </th>
140 
          <th>
141 
             Notes
142 
          </th>
143 
        </tr>
144 
      </thead>
145 
      <tbody valign="top">
146 
        <tr>
147 
          <td>
148 
 
149 
          </td>
150 
          <td>
151 
             name
152 
          </td>
153 
          <td>
154 
             parameter name in query, ie what is between the &lt; and &gt; in a parameterized query
155 
          </td>
156 
        </tr>
157 
        <tr>
158 
          <td>
159 
             1
160 
          </td>
161 
          <td>
162 
             Prompt
163 
          </td>
164 
          <td>
165 
             Display Prompt for user. Displayed when user runs a report and the class wants to know what the value is for a parameter.
166 
          </td>
167 
        </tr>
168 
        <tr>
169 
          <td>
170 
             2
171 
          </td>
172 
          <td>
173 
             query
174 
          </td>
175 
          <td>
176 
             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.
177 
          </td>
178 
        </tr>
179 
        <tr>
180 
          <td>
181 
             3
182 
          </td>
183 
          <td>
184 
             Display Query
185 
          </td>
186 
          <td>
187 
             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.
188 
          </td>
189 
        </tr>
190 
      </tbody>
191 
    </table>
192 
    <p>
193 
       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:
194 
    </p>
195 
    <pre> create table classes (
196 
   classes_id     int unsigned not null auto_increment,
197 
   instructor_id  int unsigned not null comment 'link into instructors table',
198 
   location_id    int unsigned not null comment 'link into locations table',
199 
   levels_id      int unsigned not null comment 'lin to levels table',
200 
   start_time     datetime not null comment 'date and time class starts',
201 
   end_time       datetime not null comment 'data and time class ends',
202 
   price          decimal(5,2) not null comment 'Cost in dollars',
203 
   max_students   int unsigned default 1 comment 'maximum number of students who can sign up',
204 
   primary key    (classes_id)
205 
) comment 'Table will hold class information for all classes';
206 
 
207 
create table instructor (
208 
   instructor_id  int unsigned not null auto_increment,
209 
   name           varchar(20) comment 'display name of instructor',
210 
   description    text comment 'Additional information about the instructor',
211 
   primary key    (instructor_id)
212 
) comment 'Holds information about instructor';
213 
 
214 
create table location (
215 
   location_id    int unsigned not null auto_increment,
216 
   name           varchar(64) comment 'display name of location',
217 
   information    text comment 'any additional information to be displayed',
218 
   primary key    (location_id)
219 
) comment 'holds information about each location';
220 
 
221 
create table student_classes (
222 
   student_id     int unsigned not null comment 'Student ID of this student',
223 
   classes_id     int unsigned not null comment 'classes_id of the class they are signed up for',
224 
   date_entered   timestamp comment 'so we can track when this record was created',
225 
   payment_valid  varchar(128) default null comment 'Paypal validation code, or null if not yet paid for',
226 
   primary key    (student_id,classes_id)
227 
) comment 'Link table between students and classes';
228 
 
229 
create view students_registered as select * from student_classes where payment_valid is not null;
230 
 
231 
create table student (
232 
   student_id     int unsigned not null auto_increment,
233 
   name           varchar(20) comment 'display name of student',
234 
   adult          varchar(64) comment 'information on parent/guardian',
235 
   contact        text comment 'contact information on adult/student',
236 
   primary key    (student_id)
237 
) comment 'information about students signed up for a class';
238 
    </pre>
239 
    <p>
240 
       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
241 
    </p>
242 
    <pre> select student.name 'Student',
243 
       student.adult 'Responsible Party',
244 
       student.contact 'Contact Info'
245 
from student join student_classes using (student_id)
246 
     join classes using (classes_id)
247 
where classes_id = &lt;classes_id&gt;
248 
    </pre>
249 
    <p>
250 
       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.
251 
    </p>
252 
    <p>
253 
       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
254 
    </p>
255 
    <table border="1">
256 
      <caption>
257 
         Parameter for classes_id
258 
      </caption>
259 
      <thead align="center" bgcolor="lightGray">
260 
        <tr>
261 
          <th>
262 
             Position
263 
          </th>
264 
          <th>
265 
             Parameter
266 
          </th>
267 
          <th>
268 
             Value
269 
          </th>
270 
          <th>
271 
             Notes
272 
          </th>
273 
        </tr>
274 
      </thead>
275 
      <tbody valign="top">
276 
        <tr>
277 
          <td>
278 
 
279 
          </td>
280 
          <td>
281 
             name
282 
          </td>
283 
          <td>
284 
             classes_id
285 
          </td>
286 
          <td>
287 
             same as the &lt;classes_id&gt; in the query
288 
          </td>
289 
        </tr>
290 
        <tr>
291 
          <td>
292 
             1
293 
          </td>
294 
          <td>
295 
             prompt
296 
          </td>
297 
          <td>
298 
             Select Class
299 
          </td>
300 
          <td>
301 
             Displayed to user
302 
          </td>
303 
        </tr>
304 
        <tr>
305 
          <td>
306 
             2
307 
          </td>
308 
          <td>
309 
             query
310 
          </td>
311 
          <td>
312 
             select classes_id,concat(start_time,' - ',instructor.name,' - ',location.name) class from classes join instructor using (instructor_id) join location using (location_id)
313 
          </td>
314 
          <td>
315 
             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.
316 
          </td>
317 
        </tr>
318 
        <tr>
319 
          <td>
320 
             3
321 
          </td>
322 
          <td>
323 
             Display Query
324 
          </td>
325 
          <td>
326 
             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;
327 
          </td>
328 
          <td>
329 
             The title of the report will get its value from this.
330 
          </td>
331 
        </tr>
332 
      </tbody>
333 
    </table>
334 
    <h3>
335 
       How it would work
336 
    </h3>
337 
    <p>
338 
       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.
339 
    </p>
340 
    <p>
341 
       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.
342 
    </p>
343 
    <h2>
344 
       Report class
345 
    </h2>
346 
    <p>
347 
       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:
348 
    </p>
349 
    <pre> &lt;?php
350 
         include_once('reports.php');
351 
 
352 
         if ( $_POST['action'] == 'run' ) { // we have a report name, and all the parameters
353 
            $thisReport = new Report();
354 
            $thisReport-&gt;loadFromDatabase($_POST['report']);
355 
            print $thisReport-&gt;run(  );
356 
         } elseif ($_POST['report']) { // a report is defined, so let's show the parameters for it
357 
            print '&lt;FORM action="' . $_SERVER['PHP_SELF'] . '" method="POST" enctype="multipart/form-data"&gt;';
358 
            print "&lt;INPUT type='hidden' name='report' value='" . $_POST['report'] . "'&gt;";
359 
            print "&lt;INPUT type='hidden' name='action' value='run'&gt;";
360 
            $thisReport = new Report();
361 
            $thisReport-&gt;loadFromDatabase($_POST['report']);
362 
            print $thisReport-&gt;parameterInputScreen();
363 
            print '&lt;INPUT type="submit" name="Submit" value="Submit"&gt;';
364 
            print '&lt;/form&gt;';
365 
         } else {
366 
            print '&lt;FORM action="' . $_SERVER['PHP_SELF'] . '" method="POST" enctype="multipart/form-data"&gt;';
367 
            print "&lt;select name='report'&gt;\n" . Report::listAllReports() . "&lt;/select&gt;";
368 
            print '&lt;INPUT type="submit" name="Submit" value="Submit"&gt;';
369 
            print '&lt;/form&gt;';
370 
         }
371 
      ?&gt;
372 
    </pre>
373 
    <p>
374 
       This block is in reverse order, ie the first conditional is the last section to be executed.
375 
    </p>
376 
    <p>
377 
       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.
378 
    </p>
379 
    <p>
380 
       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.
381 
    </p>
382 
    <p>
383 
       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.
384 
    </p>
385 
    <p>
386 
      <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)
387 
    </p>
388 
    <p>
389 
      <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.
390 
    </p>
391 
    <h2>
392 
       QueryParameters class
393 
    </h2>
394 
    <p>
395 
       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.
396 
    </p>
397 
    <h2>
398 
       Creating a Report Definition
399 
    </h2>
400 
    <p>
401 
       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.
402 
    </p>
403 
  </body>
404 
</html>