PHP code example of koolreport / querybuilder

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

koolreport / querybuilder example snippets

$querybuilder = DB::...;

$querybuilder->setSchemas(array(
    "salesSchema" => array(
        "tables" => array(
            "customers"=>array(
                "customerNumber"=>array(
                    "alias"=>"Customer Number",
                ),
                "customerName"=>array(
                    "alias"=>"Customer Name",
                ),
            ),
            "orders"=>array(
                "orderNumber"=>array(
                    "alias"=>"Order Number"
                ),
                "orderDate"=>array(
                    "alias"=>"Order Date",
                    "type" => "datetime"
                ),
                "orderMonth" => [
                    "expression" => "month(orderDate)",
                ]
            ),
            ...
        ),
    ),
    ...
));

use \koolreport\querybuilder\DB;
use \koolreport\querybuilder\MySQL;

class MyReport 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(MySQL::type(
            DB::table("payments") // Equivalent to : "SELECT * FROM payments"
        ))
        ->pipe($this->dataStore('payments'));
    }
}

DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
    })

$sortBy = null;

$users = DB::table('users')
->when($sortBy, 
    function ($query) use ($sortBy) {
        return $query->orderBy($sortBy);
    },
    function ($query) {
        return $query->orderBy('name');
    }
)

$user_role = "admin"; //"registered_user","public"

DB::table('orders')
    ->branch($user_role,[
        "admin"=>function($query){
            $query->whereIn('state',['TX','NY','DC'])
        },
        "registered_user"=>function($query){
            $query->whereIn('state',['TX','NY'])
        },
        "public"=>function($query){
            $query->where('state','TX')
        },        
    ])