Making a Pika Report
From PikaDocs
Each Pika report consists of several files stored in a subfolder of the "reports" folder on your Pika site.
| Table of contents |
title.txt
The title.txt file contains the title of the report that you would like to appear in the Reports section of Pika CMS. The file needs to be in the same directory as the report php files that you are creating and should have the title typed on the first line.
index.php
3 // LIBRARIES
4 chdir('../../');
5 require_once('pika-danio.php');
6
7 pika_init();
8
9 require_once('pikaMisc.php');
Section 1.
Library Includes:
pika-danio.php – library include for connection to the database and user authentication.
pika_init(); - connects to the database and authenticates the user for the page.
Both of these lines should be at the top of each report.php since they handle creating all of the back end functions required to create a report object in Pika.
pikaMisc.php - Contains helper functions such as fetchStaffArray() which may be necessary for the data you are trying to retrieve. This is an optional statement and only needs to be called if you need functions out of it. Otherwise it should not be included to keep the code clean and as small as possible.
Section 2.
Variables:
12 // VARIABLES
13 $report_title = 'LAMP Docket Report';
14 $buffer = '';
15 $staff_array = pikaMisc::fetchStaffArray();
16 $base_url = pl_settings_get('base_url');
17 $main_html = array();
In this section we create all of the variables that will need to be set to generate the report form for users to generate the report.
$report_title – The title that appears in the Pika navigation bar is set as a string.
$buffer – This variable holds all of the php generated html prior to displaying on the screen.
$staff_array – This array variable contains the list of staff set up as users in Pika organized by user_id. This will be used to generate a user selection list that will be displayed on the form to choose which staff member's data to return.
$base_url – This variable is set to the base_url value retrieved from the settings.php file via the function pl_settings_get('base_url');
$main_html – This array variable contains settings that Pika will merge into the report. For example the html template that will be used ($main_html['content'])
Section 3.
Main Code:
19 // MAIN CODE
20 pl_menu_set_temp('user_id', $staff_array);
21
22 $main_html['page_title'] = $report_title;
23 $main_html['content'] = pl_template("reports/lamp_docket/form.html");
24 $main_html['nav'] = "<a href=\"{$base_url}/\">Pika Home</a>
25 > <a href=\"{$base_url}/reports/\">Reports</a> >{$report_title}";
26 $buffer = pl_template('templates/default.html', $main_html);
27 pika_exit($buffer);
This section, in conjunction with the form.html template generates the html form that users see when they select this report.
pl_menu_set_temp('user_id', $staff_array); - Creates the html dropdown menu containing each staff member's name as an option to be selected by the user. The menu is created with the name user_id and can be referenced in the html template form.html.
There are several values added at this point to the $main_html array variable. $main_html['title] – Sets the title that is displayed in the html browser for the page displayed $main_html['nav'] – The html code here controls the navigation text that appears at the top of the form. Ex. Pika Home > Reports > LAMP Docket Report. $main_html['content'] – specifies the name of the html template that contains the input and dropdown boxes for the user. (In this case its form.html).
Once all of the settings are created as values in the $main_html array variable it can be appended into the default Pika page template for display to the web browser with the function pl_template() and stored in the variable $buffer. The last line: pika_exit($buffer) displays the page on the browser.
form.html
Section 1.
HTML Template:
1 <form action="report.php" method=POST name=form1 target="_blank"> 2 3 <p class=de> 4 Staff Member:<br/> 5 %%[user_id menu]%%<br/> 6 7 %%[closed_cases,yes_no,checkbox]%% Show Closed Cases<br/> 8 9 Sort By:<br/> 10 <select name='order_format' tabindex="1"> 11 <option value='open_date' selected>Opened Date 12 <option value='close_date'>Closed Date 13 </select> 14 <br/> 15 16 Office:<br/> 17 %%[office,office]%%<br/> 18 19 Output Format:<br/> 20 %%[report_format radio]%%<br/> 21 22 <input type=submit name="gen" value="Generate Report" tabindex="1"> 23 </p> 24 25 </form>
The html that appears here is inserted into the center of the pika page and each of the %%[variablename]%% tags is replaced with html php output corresponding to the variablename. So for example: %%[user_id menu]%% will be replaced with the html code to create a dropdown form menu containing each staff member's name. The list of names that is inserted in this menu had to be created in the index.php. Other menus are generated automatically without the need to set them up in index.php such as the %%[office, office]%% menu which creates a dropdown box populated with all of the offices set up in Pika. All of these inputs are enclosed in the <form></form> html tags and are posted to report.php when the user hits the Generate Report button on the screen.
report.php
Section 1.
Library Includes:
8 // LIBRARIES
9
10 chdir('../../');
11 require_once ('pika-danio.php');
12
13 pika_init();
14
15 require_once('pikaMisc.php');
pika-danio.php – library include for connection to the database and user authentication.
pika_init(); - connects to the database and authenticates the user for the page.
Both of these lines should be at the top of each report.php since they handle creating all of the back end functions required to create a report object in Pika.
pikaMisc.php - Contains helper functions such as fetchStaffArray() which may be necessary for the data you are trying to retrieve. This is an optional statement and only needs to be called if you need functions out of it. Otherwise it should not be included to keep the code clean and as small as possible.
Section 2.
Variable Declarations:
18 // VARIABLES
19
20 $user_id = pl_grab_post('user_id');
21 $clean_user_id = mysql_escape_string($user_id);
22 $closed_cases = pl_grab_post('closed_cases');
23 $orderby = pl_grab_post('order_format');
24 $clean_orderby = mysql_escape_string($orderby);
25 $office = pl_grab_post('office');
26 $clean_office = mysql_escape_string($office);
27 $report_format = $_SESSION['report_session'] =
pl_grab_post('report_format', 'html');
28 $staff = pikaMisc::fetchStaffArray();
29 $report_title = 'LAMP Docket Report';
This section reads in all of the values submitted from form.html and index.php and instantiates them as variables. Some things to note here are:
$report_format – The format of the report selected from form.html is retrieved here and is either html, pdf, or csv. The $_SESSION variable sets the value the user selects to their default value for this report when/if they use it more than once. So if a user selects html for the first report then if they run the report again it will default to html unless they specify otherwise.
The preferred method of submitting information via forms is POST and that is the method employed in form.html. Values are retrieved by the name associated with the <select> or <input> tags of the same name found in form.html. For example: $orderby = pl_grab_post('order_format'); corresponds to the order by dropdown in form.html <select name=”order_format”>. The function pl_grab_post(); retrieves the value specified by name from the POST data.
An important thing to remember is that data that is submitted by users must be checked and cleaned before passing it directly into the program. A number of malicious MySQL attack methods can be prevented by removing any special characters used by attackers from forms. In this program we create two variables for each of the submitted values. The first is the raw data retrieved from the form.html inputs and the second is the variable passed through a function that removes MySQL attack strings. For example:
$office = pl_grab_post('office'); $clean_office = mysql_escape_string($office);
Section 3.
Build the SQL query:
33 // BUILD SQL STATEMENT
34
35 if (!$closed_cases) {
36 $sqlclosed = " AND (close_date IS NULL OR close_date='0000-00-00')";
37 }
38
39 if (!$orderby) {
40 $sqlorderby= " ORDER BY last_name ASC";
41 }
42 else {
43 $sqlorderby = " ORDER BY {$clean_orderby} ASC";
44 }
45
46 if (!$office) {
47 $sqloffice = "";
48 }
49 else {
50 $sqloffice = " AND cases.office = '{$clean_office}'";
51 }
52
53 if (!$user_id)
54 {
55 trigger_error('Notice: no attorney was selected.');
56 }
57
58 $sql_row_nbr = "SET @id =0";
59 $sql = "SELECT @id := @id + 1 AS nbr, open_date, close_date, last_name,
60 first_name, menu_problem.label, user_id, cocounsel1,
61 cocounsel2
62 FROM cases
63 LEFT JOIN contacts ON cases.client_id=contacts.contact_id
64 LEFT JOIN menu_problem ON menu_problem.value=cases.problem
65 WHERE (user_id='{$clean_user_id}'
66 OR cocounsel1='{$clean_user_id}'
67 OR cocounsel2='{$clean_user_id}')";
68
69
70 $sql .= $sqlclosed . $sqloffice . $sqlorderby;
This is a series of conditionals for this report to modify the query based upon the users' selection(s) from the form.html file. In this case we are pulling a list of cases associated with a particular staff member and optionally selecting whether to list them within a date range and to include or exclude closed cases. One interesting note is the use of a MySQL variable @id to number the rows that are returned from the query sequentially.
Section 4.
Build the Report object:
73 // BUILD REPORT OBJECT
74
75 if ('pdf' == $report_format)
76 {
77 require_once ('plPdfReportTable.php');
78 require_once ('plPdfReport.php');
79 $r = new plPdfReport();
80 }
81
82 else if ('csv' == $report_format)
83 {
84 require_once ('plCsvReportTable.php');
85 require_once ('plCsvReport.php');
86 $r = new plCsvReport();
87 }
88
89 else if ('html' == $report_format)
90 {
91 require_once ('plHtmlReportTable.php');
92 require_once ('plHtmlReport.php');
93 $r = new plHtmlReport();
94 }
95
96 else
97 {
98 trigger_error("Invalid report format: '{$report_format}'");
99 die("Invalid report format: '{$report_format}'");
100 }
101
102 $r->set_title($report_title);
103 $r->set_header(array("", "Date Opened", "Date Closed", "Last Name",
104 "First Name", "Case Type", "Counsel", "Co-counsel", "Co-counsel"));
105 if ($show_sql)
106 {
107 $r->set_sql($sql);
108 }
This report can be viewed in several different ways (html, pdf, csv). The above code determines the selected output format and creates a report object of the corresponding type. In Pika the report functions are segregated by the type of document that they return and require the correct library files to be included to run successfully. Once the report output format has been chosen and a report object has been created using the appropriate libraries (ex. $r) we set the title of the report with the $r->set_title($report_title) method by passing it the variable which contains our report title string (“Lamp Docket Report”). The $r->set_header(); function are the column labels that will appear in the first row of the table of returned results. The order that the items in set_header(); appear is how they will appear from left to right at the top of each column returned. These should match up with the order in which they are returned by the SQL query which also returns from left to right based on the order of the fields listed in the query. $r->set_sql($sql); is a useful debugging tool as it outputs the text of the query that is sent to the MySQL database server. In this section of code the set_sql(); can be turned on and off selectively based on the conditional $show_sql retrieved from the form.html.
Section 5.
Run the Report:
113 // RUN THE REPORT
114
115 mysql_query($sql_row_nbr) or trigger_error("");
116 $result = mysql_query($sql) or trigger_error("");
117
118 while ($row = mysql_fetch_assoc($result))
119 {
120 $row['open_date'] = pl_date_unmogrify($row['open_date']);
121 $row['close_date'] = pl_date_unmogrify($row['close_date']);
122
123 $row['user_id'] = $staff[$row['user_id']];
124 $row['cocounsel1'] = $staff[$row['cocounsel1']];
125 $row['cocounsel2'] = $staff[$row['cocounsel2']];
126
127 $r->add_row($row);
128 }
129
130 $r->display();
131 exit();
In this final section of the report the SQL query is passed to the MySQL database and a result is returned and placed in the variable $result. Since we are using MySQL variables for row numbering there are two queries. The string stored in $sql_row_nbr creates the @id variable and sets its value to 0. The second, $sql, is the query that retrieves the docket list of cases. Queries are passed to the MySQL database through the function mysql_query(); and an array is returned and stored in the variable $result. When using this function it is also important to handle errors that may be generated in a logical manner so following each call to mysql_query() is the syntax or trigger_error(); which provides useful debugging information if something should go wrong during execution. The returned array is then passed into a while loop to output its data row by row and copying it into the associative array $row. The function mysql_fetch_assoc(); pulls each row off of $result and copies it as an associative array to the $row variable. This allows each column in an individual row to be manipulated and output using the column name as a key. In this example both the open_date and close_date returned by MySQL need to be reformatted to appear in the familiar MM-DD-YYYY format (MySQL stores date values in YYYY-DD-MM format which may not be desired in the resultant report). $row['open_date'] = pl_date_unmogrify($row['open_date']); performs this reformatting and sets the new value in the $row['open_date'] array location. $row['user_id'] = $staff[$row['user_id']]; performs a lookup of the user_id, returns the name from the $staff array (created with fetchStaffArray();), and chages the value in $row['user_id'] to that name. Once we have made all of the changes necessary to the data in $row we append this single row to the $r report object and repeat the process until all of the rows in $result have been appended to $r. Finally, we output the report object to the screen buffer the process of which creates a fully html formatted table containing the data from the query using the $r->display(); method. The exit(); function closes the php environment and returns the now complete report document (html, pdf, csv, or otherwise).
initial posting by ? presentation modified: Andrew Cameron 14:58, 7 Dec 2005 (EST)
