First Report
projectsbysource.php
<?php
use \koolreport\KoolReport;
use \koolreport\processes\Group;
use \koolreport\processes\Sort;
class projectsbysource extends KoolReport
{
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
use \koolreport\export\Exportable;
use \koolreport\excel\ExcelExportable;
protected function defaultParamValues()
{
return array(
"dateRange" => array(
date("2025-01-01 00:00:00"), // First day of the current month
date("Y-m-t 23:59:59") // Last day of the current month
)
);
}
protected function bindParamsToInputs()
{
return array(
"dateRange"
);
}
function settings()
{
return array(
"dataSources"=>array(
"restorepm"=>array(
"connectionString"=>DATABASE_TYPE.":host=".DATABASE_HOST.";dbname=".DATABASE_NAME,
"username"=>DATABASE_USER,
"password"=>DATABASE_PASSWORD,
"charset"=>"utf8"
),
)
);
}
protected function setup()
{
$this->src('restorepm')
->query("SELECT s.source AS source_name, p.number AS project_number, p.created
FROM Projects p
JOIN Sources s ON p.source_id = s.id
WHERE p.created BETWEEN :start AND :end;")
->params(array(
":start"=>$this->params["dateRange"][0],
":end"=>$this->params["dateRange"][1]
))
->pipe(new Group(array(
"by"=>"source_name",
"count"=>"project_count"
)))
->pipe(new Sort(array(
"project_count"=>"desc"
)))
->pipe($this->dataStore('projects_by_source'));
}
}
Second Report
projectsbysourcedetail.php
<?php
use \koolreport\KoolReport;
use \koolreport\processes\Group;
use \koolreport\processes\Sort;
class projectsbysourceDetail extends KoolReport
{
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
use \koolreport\export\Exportable;
use \koolreport\excel\ExcelExportable;
protected function defaultParamValues()
{
return array(
"dateRange" => array(
date("2025-01-01 00:00:00"), // First day of the current month
date("Y-m-t 23:59:59") // Last day of the current month
)
);
}
protected function bindParamsToInputs()
{
return array(
"dateRange"
);
}
function settings()
{
return array(
"dataSources"=>array(
"restorepm"=>array(
"connectionString"=>DATABASE_TYPE.":host=".DATABASE_HOST.";dbname=".DATABASE_NAME,
"username"=>DATABASE_USER,
"password"=>DATABASE_PASSWORD,
"charset"=>"utf8"
),
)
);
}
protected function setup()
{
// Set a unique name for the report
$this->name = "my_unique_report_name";
$this->src('restorepm')
->query("SELECT s.source AS source_name, p.number AS project_number, p.created
FROM Projects p
JOIN Sources s ON p.source_id = s.id
WHERE p.created BETWEEN :start AND :end;")
->params(array(
":start"=>$this->params["dateRange"][0],
":end"=>$this->params["dateRange"][1]
))
->pipe(new Group(array(
"by"=>"source_name",
"count"=>"project_count"
)))
->pipe(new Sort(array(
"project_count"=>"desc"
)))
->pipe($this->dataStore('projects_by_source'));
}
}