Overview

We have learned in previous section how to connect to database, pulling data and piping them through series of processes to get meaningful data. Those data are stored in the data stores of the report ready to be used to create beautiful visualization. In this section, we will learn about the view of report and how to use Widgets in your view.

/
├── SaleReport.php
└── SaleReport.view.php

                    

Example

<?php
    use \koolreport\widgets\koolphp\Table;
?>
<h1>Sale Report</h1>
<?php 
    Table::create(array(
        "dataStore"=>$this->dataStore('sales'),
        "columns"=>array("product","sale_amount")
    ));
?>
                

Multiple views

A report can have many different views. Each view may hold different charts/tales describe the same data or each view is for different purpose. The purpose could be to display html on web or to generate PDF, XML, JSON.

<?php
...
$saleReport = new SaleReport();
$saleReport->run();
$saleReport->render(); //--> This will render the default view SaleReport.view.php
...
$saleReport->render("SaleReport_AnotherView");//--> This will render the view: SaleReport_AnotherView.view.php
                

Get rendered content

Sometime we may need to get the report rendered content to further process. By enter true in second parameter of render() function, it will return the content instead of sending content to browser.

$content = $saleReport->render("SaleReport",true);//--> Return the view content
                

Koolphp Widgets

Table

Table widget help us to display data in table format. It's classname is \koolreport\widgets\koolphp\Table Let look at below example before we go details on the options.

Examples

<?php
    use \koolreport\widgets\koolphp\Table;
?>
<h1>Sale Report</h1>
<?php 
    Table::create(array(
        "dataStore"=>$this->dataStore('sales'),
        "columns"=>array(
            "product"=>array(
                "label"=>"Product Name"
            ),
            "sale_amount"=>array(
                "type"=>"number",
                "label"=>"Sale Amount",
                "prefix"=>"$"
            )
        ),
        "cssClass"=>array(
            "table"=>"table table-bordered"
        )
    ));
?>
                    

Options

<?php
//Use data store
Table::create(array(
    "dataSource"=>$this->dataStore("mydata"),
    ...
))
?>

<?php
//Use DataSource
Table::create(array(
    "dataSource"=>$this->src("sakila")->query("select * from orders"),
    ...
))
?>

<?php
//Use Process
Table::create(array(
    "dataSource"=>(
        $this->src("sakila")->query("select * from orders")
        ->pipe(new Filter(array(
            array("payment_date",">","2014-01-01")
        )))
    
    ),
    ...
))
?>

<?php
//Use associate array
Table::create(array(
    "dataSource"=>array(
        array("name"=>"Peter","age"=>35),
        array("name"=>"Karl","age"=>32),
    )
));
?>

<?php
//Use normal array
Table::create(array(
    "dataSource"=>array(
        array("name","age"),
        array("Peter",35),
        array("Karl",32),
    )
));
?>

                                        

"data"=>array(
    array("name"=>"Peter","age"=>30),
    array("name"=>"Karl","age"=>25)
)

Columns options

"columns" options simply contain the list of column you want to show on the table. The simplest form of columns settings is array of columns name for example: array("id","name","address"). More advanced, you can add extra meta data for columns like below:

<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "sale_amount"=>array(
                "type"=>"number",
                "label"=>"Sale Amount",
                "prefix"=>"$"
            )
        ),
        ...
    ));
?>
                        

Table has special "{others}" columns. This mean that beside columns has been specified settings in "columns", other columns will be added and use the settings specified in {others}. This is necessary when you want to give common settings for many columns at the same time. Or in the case that you do not know name of columns but you know the settings for them.

<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "item"=>array(
                "type"=>"string",
            ),
            "{others}"=>array(
                "type"=>"number",
                "prefix"=>"$",
            )
        ),
        ...
    ));
?>
                        

Format column value

One of important feature in Table is ability to format value dynamically. Normally, by settings type for column, the value of column will be formatted automatically. However, in some case, you may need manual format, you can do so with formatValue settings. formatValue can be a string or a function. For simple format, you may use string:

<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "amount"=>array(
                'formatValue'=>'$ @value', // format to $12
            ),
        ),
        ...
    ));
?>
                        

Or you can specify in function:

<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "amount"=>array(
                'formatValue'=>function($value)
                {
                    return "$ ".$value;
                }
            ),
        ),
        ...
    ));
?>
                        

Set cssStyle for a column

You can specify css for a specific column. The "cssStyle" can be css string or an array of component "th", "td", "tf"

<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "amount"=>array(
                "cssStyle"=>"text-align:right"
            ),
            "customerName"=>array(
                "cssStyle"=>array(
                    "th"=>"font-weight:bold;text-align:center",
                    "tr"=>"text-align:center",
                    "tf"=>"text-align:center",
                )
            )
        ),
        ...
    ));
?>                    
                    

Show/hide header

You can show or hide the header of table simply by setting "showHeader"=>true or "showHeader"=>false

Aggregated footer

Table has capability to show aggregated result of a column at footer. To do so you need to turn on footer by setting "showFooter"=>"top" or "showFooter"=>"bottom". On the column you want to aggregate, you set "footer"=>"sum". The Table support "avg", "min", "max" operation as well.

<?php 
    Table::create(array(
        ...
        "showFooter"=>"bottom",
        "columns"=>array(
            "amount"=>array(
                "footer"=>"sum"
            ),
            "sale"=>array(
                "footer"=>"avg",
                "footerText"=>"Avg Sale: @value",
            )
        ),
        ...
    ));
?>                    
                    

The "footerText" can be used to set any text at footer of column, it also can act as template. In above example, the @value will be replace with average of sale.

CssClass options

//Set tring to "tr"
"tr"=>"row-css-class"
//or set function to it, the function will take $row as parameter. Based on data, you can set class for the row
"tr"=>function($row){
    return "row-css-class";
}
                                        

//Set tring to "tr"
"th"=>"row-css-class"
//or set function to it, the function will take $columnName as parameter. Based on columnName, you can set class for this header
"th"=>function($columnName){
    return "header-css-class";
}
                                        

//Set tring to "td"
"td"=>"cell-css-class"
//or set function to it, the function will take $row and $columnName as parameters. Based on data, you can set class for the cell
"td"=>function($row,$columnName){
    return "cell-css-class";
}
                                        

Table paging

Table can be paginated with following settings:

<?php 
    Table::create(array(
        ...
        "paging"=>array(
            "pageSize"=>7,
            "pageIndex"=>0,
            "align"=>"center"
        )
    ));
?>                    
                    

The parameter pageSize sets number of row in a page. The pageIndex can be used to set current page for Table, its default value is 0 which indicated table is in the first page. The align is used to set position of pager, its value can be "left", "right" and "center".

Client events

Table supports the following client events

<?php 
Table::create(array(
    ...
    "clientEvents"=>array(
        "rowClick"=>"function(e){
            console.log(e.rowIndex);
            console.log(e.rowData);
            console.log(e.table);
        }"
    ));
?> 
                    

Google Charts

Google Charts is a great package providing various types of charts more than enough to visualize your data. What is the best thing? Google Charts is totally free.

Common Settings

Below are common settings for Google Charts.

//Use dataStore
"dataSource"=>$this->dataStore("data")
//Use datasource
"dataSource"=>$this->src("sakila")->query("...")
//Use process
"dataSource"=>(
    $this->src("sakila")->query("...")
    ->pipe(new Filter(array(
        ...
    )))
)
//Use associate array
"dataSource"=>array(
    array("name"=>"Peter", "age"=>35),
    array("name"=>"Karl", "age"=>32),
)
//Use array
"dataSource"=>array(
    array("nane","age")
    array"Peter",35),
    array"Karl",32),
)

Client events

Google Charts supports following events:

Example:

<?php 
    ColumnChart::create(array(
        ...
        "clientEvents"=>array(
            "itemSelect"=>"function(params){
                console.log(params.selectedValue);
            }"
        )
    ));
?>
                        

LineChart

LineChart class name is \koolreport\widgets\google\LineChart. Please see the example code below

Example

<?php
    use \koolreport\widgets\google\LineChart;
?>
<?php 
    LineChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

BarChart

BarChart class name is \koolreport\widgets\google\BarChart. Please see the example code below

Example

<?php
    use \koolreport\widgets\google\BarChart;
?>
<?php 
    BarChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

ColumnChart

ColumnChart class name is \koolreport\widgets\google\ColumnChart. Please see the example code below

Example

<?php
    use \koolreport\widgets\google\ColumnChart;
?>
<?php 
    ColumnChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

PieChart

PieChart class name is \koolreport\widgets\google\PieChart. Please see the example code below

Example

<?php
    use \koolreport\widgets\google\PieChart;
?>
<?php 
    PieChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

DonutChart

DonutChart class name is \koolreport\widgets\google\DonutChart. Please see the example code below

Example

<?php
    use \koolreport\widgets\google\DonutChart;
?>
<?php 
    DonutChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

ScatterChart

LineChart class name is \koolreport\widgets\google\ScatterChart. Please see the example code below

Example

<?php
    use \koolreport\widgets\google\ScatterChart;
?>
<?php 
    ScatterChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

AreaChart

AreaChart class name is \koolreport\widgets\google\AreaChart. Please see the example code below

Example

<?php
    use \koolreport\widgets\google\AreaChart;
?>
<?php 
    AreaChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

Map

Map class name is \koolreport\widgets\google\Map. Please see the example code below

Example

<?php
    use \koolreport\widgets\google\Map;
?>
<?php 
    Map::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

Other available charts

Other that those common chart above, Google library contains many other charts such as Histogram, OrgChart, GeoChart etc. All are included in \koolreport\widgets\google namespace which you can explore further. The common settings are the same but you need to refer to Google Charts documentation for details of data structure and availale options for each charts.

SubReport

SubReport is a feature of KoolReport that allows another report can be loaded inside a main one. The greatest feature is the ability to update a sub report through AJAX.

The SubReport feature comes from the needs of dividing a big report into smaller reports for better management which we called "divide and conquer" strategy. Also by dividing into smaller and separated reports, we promote the reusable code among reports, for example a subreport can be used in two different report.

Use cases

• When you have big report and would like to "divide and conquer", handle part by part
• When you like to create reusable code among different reports
• When you want to render render different reports dynamically
• When you want to update report partially, for example
  • Update charts via ajax
  • Make related data-driven drop-down combobox
  • Real-time reports, automatically updated report

Setup

Let say we have a ParentReport and would like to have a ChildReport inside.

<?php
// ChildReport.php
class ChildReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "automaker"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=automaker",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        )
    }

    function setup()
    {
        $this->src("automaker")->query("
            SELECT customerNumber,customerName from customers
        ")->pipe($this->dataStore("customers")))
    }
}            
                

<?php
// ChildReport.view.php
use \koolreport\widgets\koolphp\Table;    
?>

<?php
Table::create(array(
    "dataStore"=>$this->dataStore("customers"),
));
?>
                

<php

require "ChildReport.php";

class ParentReport extends \koolreport\KoolReport
{
    use \koolreport\core\SubReport;

    function settings()
    {
        return array(
            "subReports"=>array(
                "childreport"=>ChildReport::class
            )
        )
    }

    function setup()
    {

    }
}
                

<?php
//ParentReport.view.php
?>
<html>
    <head>
        <title>Parent Report
    </head>
    <body>
        <h1>Parent Report

        <div>
            <?php $this->subReport("childreport"); ?>
        </div>
    </body>
</html>
                

As you expected, the parent report will render with child report inside.

Partially rendering

One of greatest feature of sub report is that you may only render the child report through parent report. If you want to render any child report, you do:

<?php
$report = ParentReport(array(
    "@subReport"=>"childreport"
));
                

Update from client-side

We are able to partially update the view of child report at client-side from parent report. To do so, in the parent report, you do:

<script type='text/javascript'>
    function update()
    {
        subReport.update("childreport",{
            "any_param":"any_value"
        });
    }
</script>
                

By calling the update() function the child report will update its view. The parameters inserted at the end will be transferred to child report at server-side, the child report will render updated view and refeshed at client-side.

Create new widget

A widget normally contain a widget class and a widget template. The widget class will take care of the logic layer preparing data to be rendered in widget template.

/
├── my_widget_asset_folder/
│   ├── mywidget.css
│   └── mywidget.js
├── MyWidget.php
└── MyWidget.tpl.php
                

<?php
class MyWidget extends \koolreport\core\Widget
{
    protected $anyParam;
    protected function onInit()
    {
        // Get called when the widget is initiated
        // You may access the widget settings params through $this->params
        $this->anyValue= $this->params["anyValue"];
    }

    protected function resourceSettings()
    {
        // You only need this function if your widget requires client resource like js or css
        // Or it need library such as jquery, font-awesome
        return array(
            "library"=>array("jQuery"),//If you require "jquery","font-awesome" or "raphael" or just delete it
            "folder"=>"my_widget_asset_folder", // Folder that contains resources for client
            "js"=>array("mywidget.js"), //List all required js
            "css"=>array("mywidget.css"), // List all required css
        )
    }

    protected function onRender()
    {
        // Here you got to prepare data to render
        // Call $this->template() to render widget template with data.
                
        $this->template("MyWidget",array(
            "any_variable"=>$any_variable
        ));
    }
}
                

<h3>My widget rendering</h3>


<?php
// Your variable input in the $this->template() function will be available as well
echo $any_variable;

//The $this variable refers to the your widget's object. 
?>
<script type="text/javascript">
KoolReport.widget.init(<?php echo json_encode($this->getResources()); ?>,function(){
    //Any javascript code to initiate your widget if required.
});
</script>