Overview

KoolReport has public method called settings(). In your derived report from KoolReport, you need to declare all of your report settings including the list of datasources and their connections settings in this function.


<?php
class MyReport extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "mysql_datasource"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=mysql_databases",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
                "csv_datasource"=>array(
                    "class"=>'\koolreport\datasources\CSVDataSource'
                    "filePath"=>"/var/public/data/sales.csv"
                ),
            )
        );
    }

    ....
}

The "mysql_datasource" or "csv_datasource" are source names that you name them yourself. You can give them a meaningful name such as "sale2015". Going with the names are the array() containing the connection type and settings.

In above example, the "mysql_datasource" settings has no "class" property so KoolReport will take PdoDataSource as default connection. The "csv_datasource" has defined property "class"=>'\koolreport\datasources\CSVDataSource' which will instruct KoolReport to use the class to handle connection.

Use single quote for class declaration.

The datasource class is going with namespace so the backslash (\) is used. To safe string reason, the single quote should be used.

PdoDataSource

PdoDataSource is the default datasource in KoolReport. This datasource helps you to connect to various databases such as MySQL, SQL Server, Oracle and many others. The full list of supported databases is here.

Settings

Name	type	default	description
connectionString	string		Set the PDO connection string
username	string		User login name
password	string		User password
charset	string	"utf8"	Set the charset of database.

Methods

Methods	return	description
query(string $str_query)	`PdoDataSource`	This method is used in report's `setup()` function. It will help to setup query string which will be excuted when report is run.
params(array $params)	`PdoDataSource`	This method is used to set list of parameters for query statement

Examples


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

    public function setup()
    {
        $this->src('mysql_datasource')
        ->query("SELECT * FROM tblPurchase where status=:status")
        ->params(array(":status"=>"completed"))
        ->pipe(..)
        ->pipe(..)
        ->pipe($this->dataStore('purchase_summary'));
    }
}

In above example, we query all data from table tblPurchase of "mysql_datasource". The query result will be piped through many processes in between until it reaches the final data store called "purchase_summary".

MySQLDataSource

Although using PDODataSource can connect to MySQL. However if for some reasons, you do not have the PDO Driver, you may use the old traditional connection to MySQL using MySQLDataSource

Settings

Name	type	description
class	string	Must set to `'\koolreport\datasources\MySQLDataSource'`
host	string	Host of database
username	string	Your login username
password	string	Your password
dbname	string	Database name
charset	string	Charset

Methods

Methods	return	description
query(string $str_query)	`MySQLDataSource`	This method is used in report's `setup()` function. It will help to setup query string which will be excuted when report is run.
params(array $params)	`MySQLDataSource`	This method is used to set list of parameters for query statement

Examples


<?php
class MyReport extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "mysql"=>array(
                    'host' => 'localhost',
                    'username' => 'root',
                    'password' => '',
                    'dbname' => 'automaker',
                    'charset' => 'utf8',  
                    'class' => "\koolreport\datasources\MySQLDataSource"  
                ),
            )
        );
    }
    public function setup()
    {
        $this->src('mysql')
        ->query("SELECT * FROM tblPurchase where status=:status")
        ->params(array(":status"=>"completed"))
        ->pipe(..)
        ->pipe(..)
        ->pipe($this->dataStore('purchase_summary'));
    }
}

SQLSRVDataSource

Although using PDODataSource can connect to SQL Server. However if for some reasons, you do not have the PDO Driver, you may use the old traditional connection to MySQL using SQLSRVDataSource

Settings

Name	type	description
class	string	Must set to `'\koolreport\datasources\SQLSRVDataSource'`
host	string	Host of database
username	string	Your login username
password	string	Your password
dbname	string	Database name
charset	string	Charset

Methods

Methods	return	description
query(string $str_query)	`SQLSRVDataSource`	This method is used in report's `setup()` function. It will help to setup query string which will be excuted when report is run.
params(array $params)	`SQLSRVDataSource`	This method is used to set list of parameters for query statement

Examples


<?php
class MyReport extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "sqlserver"=>array(
                    'host' => 'localhost',
                    'username' => 'root',
                    'password' => '',
                    'dbname' => 'automaker',
                    'charset' => 'utf8',  
                    'class' => "\koolreport\datasources\SQLSRVDataSource"  
                ),
            )
        );
    }
    public function setup()
    {
        $this->src('sqlserver')
        ->query("SELECT * FROM tblPurchase where status=:status")
        ->params(array(":status"=>"completed"))
        ->pipe(..)
        ->pipe(..)
        ->pipe($this->dataStore('purchase_summary'));
    }
}

ArrayDataSource

Not all of your data comes from database, some may come from your application itself in form of array. ArrayDataSource helps you to source those data to produce data analysis and report.

Settings

Name	type	default	description
class	string		Must set to `'\koolreport\datasources\ArrayDataSource'`
data	array	array()	Contain data in array
dataFormat	string	"associate"	You can set value either `"table"` or `"associate"`.

Methods

Methods	return	description
load(array $data, string$format = "associate")	`ArrayDataSource`	This allow us to load an array in the `setup()` function of KoolReport. This load() function support both type of table format `"associate"` and `"table"`

Examples

Below example shows data input in associate format:


<?php
class MyReport extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "array_example_datasource"=>array(
                    "class"=>'\koolreport\datasources\ArrayDataSource',
                    "dataFormat"=>"associate",
                    "data"=>array(
                        array("customerName"=>"Johny Deep","dollar_sales"=>100),
                        array("customerName"=>"Angelina Jolie","dollar_sales"=>200),
                        array("customerName"=>"Brad Pitt","dollar_sales"=>200),
                        array("customerName"=>"Nocole Kidman","dollar_sales"=>100),
                    )
                ),
            )
        );
    }
}

Below example shows data input in table format:


<?php
class MyReport extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "array_example_datasource"=>array(
                    "class"=>'\koolreport\datasources\ArrayDataSource',
                    "dataFormat"=>"table", //Table data format
                    "data"=>array(
                        array("customerName","dollar_sales"),
                        array("Johny Deep",100),
                        array("Angelina Jolie",200),
                        array("Brad Pitt",200),
                        array("Nocole Kidman",100),
                    )
                ),
            )
        );
    }
}

CSVDataSource

In many case, data is stored in CSV for example log file. The CSVDataSource will help us to read data from those file and pipe their data into our processing chain.

Options

Name	type	default	description
class	string		Must set to `'\koolreport\datasources\CSVDataSource'`
filePath	string		The full file path to the `.csv` file.
fieldSeparator	string	","	Set the field separator in your csv, some file use `"\t"` tab as field separator
charset	string	"utf8"	Charset of your CSV file
firstRowData	boolean	false	Whether the first row is data. Normally the first row contain the field name so default value of this property is false.

Examples


<?php
class MyReport extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "csv_example_datasource"=>array(
                    "class"=>'\koolreport\datasources\CSVDataSource',
                    "filePath"=>"/var/storage/sales.csv",
                ),
            )
        );
    }
    public function setup()
    {
        $this->src('csv_example_datasource')
        ->pipe(..)
        ->pipe(...)
        ...
        ->pipe($this->dataStore('salescsv'));
    }
}

ExcelDataSource

ExcelDataSource help you to get data from your current Microsoft Excel file. Underline of ExcelDataSource is the open-source library called phpoffice/PHPExcel which helps us to read various Excel version. Please install the Excel package.

Settings

Name	type	default	description
class	string		Must set to `'\koolreport\excel\ExcelDataSource'`
filePath	string		The full file path to your Excel file.
charset	string	"utf8"	Charset of your Excel file
firstRowData	boolean	false	Whether the first row is data. Normally the first row contain the field name so default value of this property is false.

Examples


<?php
class MyReport extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "excel_example_datasource"=>array(
                    "class"=>'\koolreport\excel\ExcelDataSource',
                    "filePath"=>"/var/storage/sales.xls",
                ),
            )
        );
    }
    public function setup()
    {
        $this->src('excel_example_datasource')
        ->pipe(..)
        ->pipe(...)
        ...
        ->pipe($this->dataStore('sales.excel'));
    }
}

MongoDataSource

You need to install the MongoDB package.

Settings

Name	type	description
class	string	Must set to `'\koolreport\mongodb\MongoDataSource'`
connectionString	string	Define connection string to MongoDB. If you use connectionString, you do not need to use properties host, username and password.
host	string	MongoDB host
username	string	Username
password	string	Password
database	string	The name of database you want to connect

Examples


<?php
class MyReport extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "mongo_purchase"=>array(
                    "class"=>'\koolreport\mongodb\MongoDataSource',
                    "connectionString"=>"mongo://johndoe:secret_password@localhost:65432",
                    "database"=>"dbpurchase"
                ),
            )
        );
    }
    public function setup()
    {
        $this->src('mongo_purchase')
        ->query(array("colection"=>"cPurchases"))
        ->pipe(..)
        ->pipe(...)
        ...
        ->pipe($this->dataStore('mongo_purchases'));
    }
}

ReportDataSource

ReportDataSource is a special data source which help to get data from another report. Let imagine we create a report that requires data already existed in another report. We want to connect to existed report and get those data rather than spending time to rewrite code. ReportDataSource will help you to do so.

Settings

Name	type	default	description
class	string		Must set to `'\koolreport\datasources\ReportDataSource'`
report	string		The full class name of source report you want to get data from.
params	array	array()	Parameters that will be inserted to source report when inititated
key	string		This is optional parameter. In case that you need to create more than one datasource from single source report (only difference in params) then you specify different `key` for each datasource.

Methods

Methods	return	description
dataStore(string $name)	`ReportDataSource`	This function is used in `setup()` function of report. It specifies the `name` of data store in the source report we want to get data from.

Examples

For example, we need to create TotalSaleReport which will need data from HardwareSaleReport and SoftWareSaleReport


<?php

require "HardwareSaleReport.php";
require "SoftwareSaleReport.php";

class TotalSaleReport extends \koolreport\KoolReport
{
    public function settings()
    {
        return array(
            "dataSources"=>array(
                "hardwareSaleReport"=>array(
                    "class"=>"/koolreport/datasources/ReportDataSource",
                    "report"=>"HardwareSaleReport",
                    "params"=>array("month"=>1,"year"=>2017)
                ),
                "softwareSaleReport"=>array(
                    "class"=>"/koolreport/datasources/ReportDataSource",
                    "report"=>"SoftwareSaleReport",
                    "params"=>array("month"=>1,"year"=>2017)
                ),
            )
        );
    }
    public function setup()
    {
        $this->src('hardwareSaleReport')
        ->dataStore('sale') //We want to get data from "sale" data store of HardwareSaleReport
        ...
        ->pipe(this->dataStore('sale_of_hardware'));
    }
}

The above report will run two sub-reports which are HardwareSaleReport and SoftwareSaleReport. This two reports receive month=1 and year=2017 as parameters. As in the setup() function, we see that the report will get data from 'sale' datastore in the HardwareSaleReport to process further.

Creating new datasource

Creating a new datasource is simple. Below are the template:


<?php
class NewDataSource extends \koolreport\core\DataSource
{
    public function onInit()
    {
        // Get called when the datasource is created.
        // You may get the detail settings from $this->params to init the connection.
    }
    public function start()
    {
        // When run, KoolReport will call this function. Here you will start sending data to the pipeline
        // Call $this->sendMeta($metaData) to send $metaData to next nodes.
        // Detail of $metaData structure is below.
        // Call $this->startInput(null) before sending any data
        // Start looping your data and call $this->next($data) to send each of data row to next process.
        // On finishing, please call $this->endInput(null) to notify next nodes that you have end sending data.
    }
}

Metadata structure

The meta data should be sent before you sent any data. The meta data is a associate array in following format:


array(
    "columns"=>array(
        "column_one"=>array(
            "type"=>"number"
        ),
        "column_two"=>array(
            "type"=>"string"
        ),
        "column_three"=>array(
            "type"=>"datetime"
        )
    )
);

Data structure

The following is the data structure that you will send using next() function:


array("column_one"=>13,"column_two"=>"John Doe","column_three"=>"2015-12-25 00:00:00")

Data Sources

Overview

Use single quote for class declaration.

PdoDataSource

Settings

Methods

Examples

MySQLDataSource

Settings

Methods

Examples

SQLSRVDataSource

Settings

Methods

Examples

ArrayDataSource

Settings

Methods

Examples

CSVDataSource

Options

Examples

ExcelDataSource

Settings

Examples

MongoDataSource

Settings

Examples

ReportDataSource

Settings

Methods

Examples

Creating new datasource

Metadata structure

Data structure