Report Creator Component
Under most circumstances web-based intranet applications call for some type of flexible
reporting for its users. Report design is often times a fixed process which immobilizes
a user's ability to create new reports on the fly. Utilizing some existing ingredients
within Cake I managed to bake a fairly straightforward reporting component that can easily
integrate into any Cake application.
reporting for its users. Report design is often times a fixed process which immobilizes
a user's ability to create new reports on the fly. Utilizing some existing ingredients
within Cake I managed to bake a fairly straightforward reporting component that can easily
integrate into any Cake application.
What can this component do?
The report creator component provides users with an ability to rapidly create dynamicreports based off a preset of Cake models.
How do I use this component?
I have compiled a package of six files that will allow you to instantly harness thepower of this component.
The package can be downloaded at the following address:
http://www.internaq.com/files/reportcomponent.zip
Install Instructions:
File: report.css
/webroot/css/report.cssFile: reports_controller.php
Include this controller action within the controller you wish to have reporting capability.In this file you will need to set the models you wish to use for reporting in the model
array. Also note that you must add the following line to your controller:
var $components = array ('Report');
Controller Class:
Download code
<?php
/**********************************************************************************************************
Function: createReport()
Action: Build a dynamic report.
**********************************************************************************************************/
function createReport()
{
if (!empty($this->data))
{
//Determine if user is pulling existing report or deleting report
if(isset($this->params['form']['existing']))
{
if($this->params['form']['existing']=='Pull')
{
//Pull report
$this->Report->pull_report($this->data['Misc']['saved_reports']);
}
else if($this->params['form']['existing']=='Delete')
{
//Delete report
$this->Report->delete_report($this->data['Misc']['saved_reports']);
//Return user to form
$this->flash('Your report has been deleted.','/'.$this->name.'/'.$this->action.'');
}
}
else
{
//Filter out fields
$this->Report->init_display($this->data);
//Set sort parameter
if(!isset($this->params['form']['order_by_primary'])) { $this->params['form']['order_by_primary']=NULL; }
if(!isset($this->params['form']['order_by_secondary'])) { $this->params['form']['order_by_secondary']=NULL; }
$this->Report->get_order_by($this->params['form']['order_by_primary'], $this->params['form']['order_by_secondary']);
//Store report name
if(!empty($this->params['form']['report_name']))
{
$this->Report->save_report_name($this->params['form']['report_name']);
}
//Store report if save was executed
if($this->params['form']['submit']=='Create And Save Report')
{
if(empty($this->params['form']['report_name']))
{
//Return user to form
$this->flash('Must enter a report name when saving.','/'.$this->name.'/'.$this->action.'');
}
else
{
$this->Report->save_report();
}
}
}
//Set report fields
$this->set('report_fields', $this->Report->report_fields);
//Set report name
$this->set('report_name', $this->Report->report_name);
//Allow search to go 2 associations deep
$this->{$this->modelClass}->recursive = 2;
//Set report data
$this->set('report_data', $this->{$this->modelClass}->findAll(NULL,NULL,$this->Report->order_by));
}
else
{
//Setup options for report component
/*
You can setup a level two association by doing the following:
"VehicleDriver"=>"Employee" ie $models = Array ("Vehicle", "VehicleDriver"=>"Employee");
Please note that all fields within a level two association cannot be sorted.
*/
$models = Array ("");
//Set array of fields
$this->set('report_form', $this->Report->init_form($models));
//Set current controller
$this->set('cur_controller', $this->name);
//Pull all existing reports
$this->set('existing_reports', $this->Report->existing_reports());
}
}
?>
File: report.php
/controllers/components/report.phpThis is the component file. The default path for saving reports is within
the /app/tmp/report/ folder. If the folder does not exist the component will
create it for you.
Component Class:
Download code
<?php
class ReportComponent extends Object
{
/**
* Place holder for the models array.
*
* @var array
* @access public
*/
var $model = Array();
/**
* Place holder for the fields.
*
* @var array
* @access public
*/
var $columns = Array();
/**
* Specify DEFAULT folder off root directory to store reports in.
*
* @var string
* @access public
*/
var $path ="/app/tmp/reports/";
/**
* Place holder for the report fields.
*
* @var array
* @access public
*/
var $report_fields = Array();
/**
* Place holder for the order by clause.
*
* @var string
* @access public
*/
var $order_by = NULL;
/**
* Place holder for the report name.
*
* @var string
* @access public
*/
var $report_name = NULL;
/**
* Startup - Link the component to the controller.
*
* @param controller
*/
function startup(&$controller)
{
// This method takes a reference to the controller which is loading it.
// Perform controller initialization here.
$this->controller =& $controller;
}
/**
* Initialize the report form by creating links to models
* and storing table meta data.
*
* @models array
*/
function init_form($models)
{
foreach($models as $model=> $value)
{
$this-> model = new $value;
$columns = $this->model->loadInfo();
//Extract field names from array
for($j=0; $j<count($columns->value); $j++)
{
$arr[$value][$j]=$columns->value[$j]['name'];
}
//If two level deep association exists set value
if(!empty($model))
{
$arr['associated_table'][$value]=$model;
}
}
return $arr;
}
/**
* Initializes the report display.
*
* @form array
*/
function init_display($form)
{
//get fields that were selected
$this->report_fields=$this->get_selected($form);
//sort fields by priority
$this->report_fields=$this->sort_fields($this->report_fields);
}
/**
* Extracts all selected fields from form.
*
* @form array
*/
function get_selected($form)
{
foreach ($form as $model => $field) {
foreach ($field as $name) {
if(!empty($name['include'])) {
$arr[]=$name;
}
}
}
return $arr;
}
/**
* Sorts all selected fields from form by priority
* entered (1-left ... 10-right).
*
* @fields array
*/
function sort_fields($fields)
{
for ($i=0; $i < sizeof($fields)-1; $i++)
{
for ($j=0; $j<sizeof($fields)-1-$i; $j++)
{
if ($fields[$j]['priority'] > $fields[$j+1]['priority'])
{
$tmp = $fields[$j];
$fields[$j] = $fields[$j+1];
$fields[$j+1] = $tmp;
}
}
}
return $fields;
}
/**
* Sets up the order by clause.
*
* @primary string
* @secondary string
*/
function get_order_by($primary, $secondary)
{
//Store primary sort if exists
if(!empty($primary))
{
$this->order_by=$primary;
//Store secondary sort if exists
if(!empty($secondary))
{
$this->order_by.=",".$secondary;
}
}
else
{
$this->order_by=NULL;
}
}
/**
* Saves the newly created report.
*
* @order_by string
*/
function save_report()
{
$content='<? $report_fields=Array(';
for($i=0; $i<count($this->report_fields); $i++)
{
//get number of elements
$total=count($this->report_fields[$i]);
$counter=0;
$content.='Array(';
foreach($this->report_fields[$i] as $report_field => $value)
{
$counter++;
if($total!=$counter)
{
$content.='"'.$report_field.'" => "'.$value.'", ';
}
else
{
$content.='"'.$report_field.'" => "'.$value.'"';
}
}
if(($i+1)==count($this->report_fields))
{
$content.=')';
}
else
{
$content.='), ';
}
}
$content.=');';
$content.='$order_by="'.$this->order_by.'";';
$content.='$report_name="'.$this->report_name.'"; ?>';
//Create directory if specified one does not already exist
if(!is_dir($_SERVER['DOCUMENT_ROOT'].$this->path))
{
mkdir($_SERVER['DOCUMENT_ROOT'].$this->path);
}
$file_name = $this->report_name.".php";
$handle = fopen($_SERVER['DOCUMENT_ROOT'].$this->path.$file_name, 'w');
fwrite($handle, $content);
fclose($handle);
}
/**
* Saves report name.
*
* @report_name string
*/
function save_report_name($report_name)
{
$this->report_name=$report_name;
}
/**
* Pulls listing of existing reports..
*
*/
function existing_reports()
{
//create an array to hold directory list
$results = array();
//create a handler for the directory
$handler = opendir($_SERVER['DOCUMENT_ROOT'].$this->path);
//keep going until all files in directory have been read
while ($file = readdir($handler))
{
// if $file isn't this directory or its parent, add it to the results array
if ($file != '.' && $file != '..')
{
$results[$file] = str_replace(".php", "", $file);
}
}
closedir($handler);
return $results;
}
/**
* Pulls field array from existing report..
*
* @report string
*/
function pull_report($report)
{
//Pull file
require($_SERVER['DOCUMENT_ROOT'].$this->path.$report);
//Store data
$this->order_by=$order_by;
$this->report_fields=$report_fields;
$this->report_name=$report_name;
}
/**
* Deletes an existing report..
*
* @report string
*/
function delete_report($report)
{
unlink($_SERVER['DOCUMENT_ROOT'].$this->path.$report);
}
}
?>
File: report_form.thtml
/views/elements/report_form.thmlThis file handles the look and display of the form retrieval page.
View Template:
Download code
<div id="report_form" class="report_form">
<table>
<tr>
<td valign="top">
<form action="/<?= $cur_controller; ?>/createReport/" method="post">
<fieldset>
<legend style="background: #E51336;">Saved Reports</legend>
<table class="report_small">
<tr>
<td><?php echo $html->selectTag('Misc/saved_reports', $existing_reports); ?></td>
<td><input type="submit" name="existing" value="Pull" style="font: normal normal bold 8pt arial; color: #FFFFFF; background: #0066CC;"></td>
<td><input type="submit" name="existing" value="Delete" style="font: normal normal bold 8pt arial; color: #FFFFFF; background: #E51336;" onclick="return confirm('Are you sure you want to delete this report?')"></td>
</tr>
</table>
</fieldset>
</form>
</td>
<td width="20"></td>
<td valign="top">
<form action="/<?= $cur_controller; ?>/createReport/" method="post">
<fieldset>
<legend style="background: #E51336;">New Report</legend>
<table class="report_small">
<tr>
<td>Report Name</td>
<td><input type="text" name="report_name" style="width: 180px;"></td>
</tr>
<tr>
<td></td>
<td>* Field required if saving report</td>
</tr>
</table>
</fieldset>
</td>
</tr>
</table>
<? foreach ($report_form as $key => $value): ?>
<? if($key!='associated_table') { ?>
<div style="height: 15px;"><!-- Spacer --></div>
<fieldset>
<legend><?= $key; ?> Table</legend>
<table class="report">
<tr class="header">
<td>Field</td>
<td>Display Name</td>
<td style="text-align: center;">Priority</td>
<td style="text-align: center;">Sort By Primary</td>
<td style="text-align: center;">Sort By Secondary</td>
<td style="text-align: center;">Include</td>
</tr>
<? for ($i=0; $i<count($value); $i++) { ?>
<tr class="body" onClick="if($('<?= $key; ?><?= $i; ?>').checked == true){ this.className='body_selected'; } else { this.className='body'; }">
<td>
<?= $value[$i]; ?><input type="hidden" name="data[<?= $key; ?>][<?= $value[$i] ;?>][field_name]" value="<?= $value[$i]; ?>"><input type="hidden" name="data[<?= $key; ?>][<?= $value[$i] ;?>][model]" value="<?= $key; ?>"><input type="hidden" name="data[<?= $key; ?>][<?= $value[$i] ;?>][associated_table]" value="<? if(!empty($table_data['associated_table'][$key])) { echo $table_data['associated_table'][$key]; } ?>">
</td>
<td>
<input type="text" name="data[<?= $key; ?>][<?= $value[$i] ;?>][display_name]" onFocus="if($('<?= $key; ?><?= $i; ?>').checked == false){ this.className='body_selected'; $('<?= $key; ?><?= $i; ?>').checked = true; }"></td>
<td style="text-align: center;"><input type="text" name="data[<?= $key; ?>][<?= $value[$i] ;?>][priority]" style="width: 50px;" onFocus="if($('<?= $key; ?><?= $i; ?>').checked == false){ this.className='body_selected'; $('<?= $key; ?><?= $i; ?>').checked = true; }">
</td>
<td style="text-align: center;"><input type="radio" name="order_by_primary" value="<?= $key; ?>.<?= $value[$i]; ?>"></td>
<td style="text-align: center;"><input type="radio" name="order_by_secondary" value="<?= $key; ?>.<?= $value[$i]; ?>"></td>
<td style="text-align: center;"><input type="checkbox" id="<?= $key; ?><?= $i; ?>" name="data[<?= $key; ?>][<?= $value[$i] ;?>][include]"></td>
</tr>
<? } ?>
</table>
</fieldset>
<? } ?>
<?php endforeach; ?>
<div style="height: 15px;"><!-- Spacer --></div>
<table cellspacing="0" cellpadding="0">
<tr>
<td><input type="submit" name="submit" value="Create Report"></td>
<td width="10"></td>
<td><input type="submit" name="submit" value="Create And Save Report"></td>
</tr>
</table>
</form>
</div>
File: report_display.thtml
/views/elements/report_display.thmlThis file handles the look and display of the report page.
View Template:
Download code
<div id="report_display" class="report_display">
<div class="report_name"><?= $report_name ?></div>
<div class="report_date_stamp">Report run on <?= date('m/d/Y'); ?></div>
<div style="height: 25px;"></div>
<table class="report">
<tr class="header">
<? foreach ($report_fields as $field): ?>
<td><? echo ($field['display_name']=='' ? $field['field_name'] : $field['display_name']); ?></td>
<?php endforeach; ?>
</tr>
<? for($i=0; $i<count($report_data); $i++) { ?>
<tr class="body">
<? foreach ($report_fields as $field): ?>
<td>
<?
//Check to see if associated table is being used
if(!empty($report_data[$i][$field['associated_table']][$field['model']][$field['field_name']])) {
echo $report_data[$i][$field['associated_table']][$field['model']][$field['field_name']];
}
else if(!empty($report_data[$i][$field['model']][$field['field_name']])) {
echo $report_data[$i][$field['model']][$field['field_name']];
}
?>
</td>
<?php endforeach; ?>
</tr>
<? } ?>
</table>
</div>
File: create_report.thtml
/views/ --controller folder -- /create_report.thtmlThis is the view file that will handle outputting the correct user displays.
File: prototype.js
/webroot/js/prototype.jsI use a shorthand feature from the prototype.js library so please include this file.
Well that's it. Hopefully this will help add a little more efficiency to your programming
arsenal. I plan on updating this script over the next couple of months to allow users to build
reports that utilize aggregate functions.
Comments
Comment
1 Bug Question
Thanks for this nice component.
However, I have a few suggestions and questions -
1. You should mention the version of cake with which this has been tested. I had some problem running it with my cake version.
2. I faced problems with you defining things with $_SERVER['DOCUMENT_ROOT']. My doc root is mapped to C:/apache/htdocs/app/webroot and concatinating it with $this->path won't work. So, I removed $_SERVER and made the path variable relative to the root.
3. With the new cake version, you will need to use loadModel($value); in your init_form() function else it will not work.
Question:
- It does not work for me when I include more than one model in the array. Infact it doesn't work for me if the incldued models are other than the one I have included the createReport method in.
eg. I have users_controller where I have the createReport() function. I define the foll:
$models = Array ("User", "Fan");
Now, anytime any query is run, it only runs a findAll on "User". The problem seems to be in this line:
$this->set('report_data', $this->{$this->modelClass}->findAll(NULL,NULL,$this->Report->order_by));
The value of $this->modelClass is always User.
Any thoughts on how to get around this?
Thanks.
Comment
2 In response
Make sure that the models you include in your model array are also included in the uses array in your controller. This is the only obvious reason I can think that this is not working for you. I have not tested this with any other versions of cake.
Thanks,
Gene
Comment
3 Very good component but for older versions
This is really very good component but seems to be for older versions of cakePHP. Will be gr8 if someone can update this component to make it compatible with newer versions of cakephph.
Question
4 About the Component
I really like the look of this components and want to use it in my application. I'm using cakePHP 1.2, however, and I get a fatal error when line 78 in component executes...
Fatal error: Class '' not found in C:\url\app\controllers\components\report.php on line 78
I haven't changed any of the code apart from
Any suggestions why this may be??
Thanks,
Viktor
Question
5 New Version
I am writing to express my interest, along with others, in your Report Creator Component. I would like to use its functionality with the current version of cake. If you find the time to update it, I would greatly appreciate it if you let me know.
Have a great one,
Locke Crawford
Comment
6 not working in cakphp 1.2.4
Is there anybody can tell me what should I change in the " function init_form($models) " function as
$columns = $this->model->loadInfo(); is not working for me.
I am getting the error exactly:
Warning (512): SQL Error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'loadInfo' at line 1 [CORE\cake\libs\model\datasources\dbo_source.php, line 524]
Code | Context
$sql = "loadInfo"
$error = "1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'loadInfo' at line 1"
$out = null
$out = null;
if ($error) {
trigger_error("SQL Error: {$this->error}", E_USER_WARNING);
DboSource::showQuery() - CORE\cake\libs\model\datasources\dbo_source.php, line 524
DboSource::execute() - CORE\cake\libs\model\datasources\dbo_source.php, line 201
DboSource::fetchAll() - CORE\cake\libs\model\datasources\dbo_source.php, line 336
DboSource::query() - CORE\cake\libs\model\datasources\dbo_source.php, line 297
Model::call__() - CORE\cake\libs\model\model.php, line 441
Overloadable::__call() - CORE\cake\libs\overloadable_php5.php, line 52
Mentor::loadInfo() - [internal], line ??
ReportComponent::init_form() - APP\controllers\components\report.php, line 79
ReportsController::createReport() - APP\controllers\admin\reports_controller.php, line 87
Object::dispatchMethod() - CORE\cake\libs\object.php, line 116
Dispatcher::_invoke() - CORE\cake\dispatcher.php, line 227
Dispatcher::dispatch() - CORE\cake\dispatcher.php, line 194
[main] - APP\webroot\index.php, line 88
Query: loadInfo
Any help is highly appreciable.
Thanks
Almas
Comment
7 Modifications for new cake versions
Rename
create_report.thtml
report_display.thtml
report_form.thtml
to
create_report.ctp
report_display.ctp
report_form.ctp
File: report.php
--------------------------------------------------
on line 79 change:
$columns = $this->model->loadInfo();
$columns = $this->model->schema();
--------------------------------------------------
Modify the loop on line 82 ( //Extract field names from array )
for($j=0; $j<count($columns->value); $j++)
{
$arr[$value][$j]=$columns->value[$j]['name'];
}
$j = 0;
foreach($columns as $col => $val) {
$arr[$value][$j] = $col;
$j++;
}
--------------------------------------------------
File: controller_action.php
on line 14 ( //Pull report ) change:
$this->Report->pull_report($this->data['Misc']['saved_reports']);
$this->Report->pull_report($this->data['Misc/saved_reports']);
--------------------------------------------------
File: report_form.ctp
on line 7 and 26 I had to include the application path:
<form action="/app/<?= $cur_controller; ?>/createReport/" method="post">
on line 13 change:
<td><?php echo $html->selectTag('Misc/saved_reports', $existing_reports); ?></td>
to:
<td><?php echo $form->input('Misc/saved_reports',array('label'=>'Misc/saved_reports','type'=>'select','options'=>$existing_reports,'empty'=>'- Select a Report -')); ?></td>
--------------------------------------------------
Hope this works for everybody... otherwise let me know.
Good luck!
Luis Dias
Question
8 Graph plugin
Can anybody share if they have integrated visual graphs for this 'Report' component?
Thanks,
Naveed