KoolReport
  1. Home
  2. Dashboard
  3. DataSouce

DataSource

Overview #

DataSource is the intermediate bridge between dashboard's application and your database, help to make connection to your data and facilitate building query.

PDOSource #

PDOSource represents connection and query to database systems. KoolReport's Dashboard supports MySQL, PostgresQL and SQLServer.

MySQL #

In order to make datasource for your database, you create a new class derived from prebuilt source class of dashboard and provide connection:

Example:

<?php
//AutoMaker.php

use \koolreport\dashboard\sources\MySQL;

class AutoMaker extends MySQL
{
    protected function connection()
    {
        return [
            "connectionString"=>"mysql:host=localhost;dbname=automaker",
            "username"=>"root",
            "password"=>"",
            "charset"=>"utf8"
        ];
    }
}

*We use MySQL as an example, you can use PostgreSQL or SQLServer in the same way

All done! And now everywhere in your application, you can make query:

class PaymentTable extends Table
{
    protected function dataSource()
    {
        return AutoMaker::table("payments")
                ->select("customerNumber","paymentDate","amount")
                ->where("paymentDate",">","2004-01-01");
    }
}

The query syntax of MySQL datasource follows the syntax of QueryBuilder.

Run query #

In case that you want to get data directly, you may end the query with run() method, the datasource will execute query immediately and return data in form of DataStore.

Example:

$payments = AutoMaker::table("payments")->run();

$payment->sort(["amount"=>"desc"]);

foreach($payments as $payment)
{
    echo $payment["paymentDate"];
}

Raw query #

If you like to have your own SQL query, you can use static rawSQL() method:

AutoMaker::rawSQL("
    SELECT paymentDate, amount
    FROM payments
")

Call procedure #

The PDOSource support call procedure with or without parameters.

Example:

//Without parameters
AutoMaker::procedure()->call("GetAllEmployees"); //Call procedure GetAllEmployees

//With parameters
AutoMaker::procedure()->call("GetEmployeeAtLocation",["NY"]); Get employees at New Yorks

PostgreSQL #

PostgreSQL datasource works in the same way like above MySQL, all you need to do is to replace the MySQL with PostgreSQL. Of course, you need to provide connection information within connection() method.

<?php
//AutoMaker.php

use \koolreport\dashboard\sources\PostgreSQL;

class AutoMaker extends PostgreSQL
{
    protected function connection()
    {
        return [
            "connectionString"=>"pgsql:host=localhost;port=5432;dbname=automaker;",
            "username"=>"root",
            "password"=>"poweroot",
            "charset"=>"utf8"
        ];
    }
}

SQLServer #

SQLServer datasource works in the same way like above MySQL, all you need to do is to replace the MySQL with SQLServer. Of course, you need to provide connection information within connection() method.

<?php
//AutoMaker.php

use \koolreport\dashboard\sources\SQLServer;

class AutoMaker extends SQLServer
{
    protected function connection()
    {
        return [
            "connectionString"=>"sqlsrv:Server=localhost\\SQLEXPRESS;Database=MyDatabase",
            "username"=>"MyUsername",
            "password"=>"MyPassword",
            "charset"=>"utf8"
        ];
    }
}

Oracle #

You can make connection to Oracle database as following:

<?php
//AutoMaker.php

use \koolreport\dashboard\sources\Oracle;

class AutoMaker extends Oracle 
{
    protected function connection()
    {
        return [
            "connectionString"=>"oci:dbname=yoursid",
            "username"=>"MyUsername",
            "password"=>"MyPassword",
            "charset"=>"utf8"
        ];
    }
}

Since our query builder does not fully support Oracle yet so we suggest you to use rawSQL() with your own query when query data. Another note is that returned field's name from Oracle is in capitalized mode so please take this note when you provide field name to fields().

Example of table widget with Oracle datasource

<?php
use \koolreport\dashboard\widgets\Table;
use \koolreport\dashboard\fields\Number;
use \koolreport\dashboard\fields\Text;

class MyTable extends Table
{
    protected function dataSource()
    {
        return AutoMaker::table("customers")->rawSQL("
            SELECT customerNumber, customerName
            FROM customers
        ");
    }

    protected function fields()
    {
        return [
            Number::create("CUSTOMERNUMBER"),
            Text::create("CUSTOMERNAME"),
        ]
    }
}

SQLite #

<?php
//AutoMaker.php

use \koolreport\dashboard\sources\Firebird;

class AutoMaker extends Firebird 
{
    protected function connection()
    {
        return [
            "connectionString"=>"sqlite:automaker.sqlite3",
        ];
    }
}

Firebird #

<?php
//AutoMaker.php

use \koolreport\dashboard\sources\SQLite;

class AutoMaker extends SQLite 
{
    protected function connection()
    {
        return [
            "connectionString"=>"firebird:dbname=x.x.x.x:C:\baza.eu3",
            "username"=>"SYSDBA",
            "password"=>"xxx"
        ];
    }
}

Caching #

PDOSource supports data caching. The cache system of Dashboard is very flexible, you can implement caching for all queries or for some that you choose, more details.

FileSource #

FileSource deals with data from files such as CSV or Excel file. You do not need to create separate DataSource class like above database connection, instead you do as following:

<?php

use \koolreport\dashboard\widgets\Table;
use \koolreport\dashboard\sources\CSV;
class PaymentTable extends Table
{
    protected function dataSource()
    {
        return CSV::file("/path/to/file.csv")
            ->select("customerName","productName","amount");
    }
}

Above example we read CSV file, reading Excel file is the same:

<?php

use \koolreport\dashboard\widgets\Table;
use \koolreport\dashboard\sources\Excel;
class PaymentTable extends Table
{
    protected function dataSource()
    {
        return Excel::file("/path/to/file.xlsx")
            ->select("customerName","productName","amount");
    }
}

Get data #

Anywhere, you can get data from your file by ending the query with run() method. The filesource will trigger loading data from your file and convert it to DataStore object.

Example:

$saleCSV = CSV::file("sale.csv")->run();

$saleCSV->sort(["customerName"=>"asc"]);

foreach($saleCSV as $row)
{
    echo $row["customerNumber"];
}

Select #

Of course, you may not want to get all columns from your file. Using select() method, you can choose which columns to be available:

CSV::file("file.csv")->select("name","email")

Excel::file("file.xlsx")->select("name","email")

To change name of column, you may use alias function

CSV::file("file.csv")
    ->select('customerName')->alias('name')
    ->addSelect('customerAge')->alias('age')

Aggregates #

The query builder also provides a variety of aggregate methods such as count, max, min, avg, and sum. You may call any of these methods after constructing your query:

CSV::file("sale.csv")->groupBy("country")->sum("amount")

CSV::file("sale.csv")->count()

CSV::file("sale.csv")->groupBy('state')
    ->avg('income')->alias('avgIncome')

Where clauses #

Simple where #

You may use the where method on a file source instance to add where clauses to the query. The most basic call to where requires three arguments. The first argument is the name of the column. The second argument is an operator. Finally, the third argument is the value to evaluate against the column.

CSV::file("file.csv")->where('votes', '=', 100)

For convenience, if you simply want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where method:

CSV::file("file.csv")->where('votes', 100)

Or Statement #

You may chain where constraints together as well as add or clauses to the query. The orWhere method accepts the same arguments as the where method:

CSV::file("file.csv")
    ->where('votes', '>', 100)
    ->orWhere('name', 'John')

Ordering #

The orderBy method allows you to sort the result of the query by a given column. The first argument to the orderBy method should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either "asc" or "desc":

CSV::file("file.csv")
                ->orderBy('name', 'desc')

Grouping #

The groupBy and having methods may be used to group the query results. The having method's signature is similar to that of the where method:

CSV::file("users.csv")
        ->groupBy('account_id')
        ->having('account_id', '>', 100)

You may pass multiple arguments to the groupBy method to group by multiple columns:

DB::table('users')
        ->groupBy('first_name', 'status')
        ->having('account_id', '>', 100)

Limit & Offset #

To limit the number of results returned from the query, or to skip a given number of results in the query, you may use the limit and offset methods:

DB::table('users')
        ->offset(10)
        ->limit(5)

Get started with KoolReport

KoolReport will help you to construct good php data report by gathering your data from multiple sources, transforming them into valuable insights, and finally visualizing them in stunning charts and graphs.