There are 4422 rows being returned from database. Problem is that this report have become very very slow and taking around 2 mins to load completely.
My PivotReport.php
<?php
namespace App\Reports;
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
class PivotReport extends \koolreport\KoolReport
{
use \koolreport\laravel\Friendship;
use \koolreport\export\Exportable;
use \koolreport\excel\ExcelExportable;
// By adding above statement, you have claim the friendship between two frameworks
// As a result, this report will be able to accessed all databases of Laravel
// There are no need to define the settings() function anymore
// while you can do so if you have other datasources rather than those
// defined in Laravel.
function setup()
{
$this->src('mysql')
->query("SELECT CONCAT(CONCAT('Week Commencing', ' ', WEEK(nr.created_date)), '/', MONTH(nr.created_date)) as wk, ct.team_name, nr.user_name,
YEAR(nr.created_date) as year,
DAY(nr.created_date) as day, QUARTER(nr.created_date)as quarter,
SUM(IF(nr.notes_type = 126, 1, 0)) AS candidate_added,
SUM(IF(nr.notes_type = 76, 1, 0)) AS dm_added,
SUM(IF(nr.notes_type = 102, 1, 0)) AS account_added,
SUM(IF((nr.notes_type = 139 AND nr.candidate_id> 0), 1, 0)) AS cv_send,
SUM(IF(nr.notes_type = 399, 1, 0)) AS grade_b_jobs,
SUM(IF((nr.notes_type = 71 AND nr.candidate_id> 0), 1, 0)) AS cand_spec_send,
SUM(IF((nr.notes_type = 71 AND nr.dm_id> 0), 1, 0)) AS dm_spec_send,
SUM(IF(nr.notes_type = 207, 1, 0)) AS cv_upload,
SUM(IF(nr.notes_type = 213, 1, 0)) AS add_dm_hiring_profile
FROM `notes_report` nr
LEFT JOIN company_teams ct ON nr.team_id = ct.id
WHERE nr.notes_type
IN(126,76,102,139,399,71,207,213)
GROUP BY wk, ct.team_name, nr.user_name, year
ORDER BY wk, ct.team_name, nr.user_name, year
")
->pipe(new Filter(array(
//array('name', 'startsWith', 'a'),
array('year', '>', 2003),
)))
/* ->params(array(
* WHERE exo.name LIKE :name AND YEAR(nr.created_date) > :year
":name"=>"%Th%",
":year"=>"2003"
)) */
->pipe(new ColumnMeta(array(
"candidate_added"=>array(
'type' => 'number',
//"prefix" => "$",
),
)))
->pipe(new Pivot(array(
"dimensions"=>array(
"column" => "year",
"row" => "wk, team_name, user_name",
),
"aggregates"=>array(
"sum"=>"candidate_added",
"count"=>"candidate_added"
)
)))
->pipe($this->dataStore('pivot'));
}
}
My PivotReport.view.php
<?php
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\pivot\widgets\PivotMatrix;
use \koolreport\core\Utility;
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\instant\Widget;
use \koolreport\datasources\CSVDataSource;
?>
<div class='report-content'>
<div class="text-center">
<h1>Sale Report</h1>
<p class="lead">
Summarize amount of sales and number of sales by three dimensions: customers, categories and products
</p>
</div>
<div>
<?php
/* PivotTable::create(array(
'dataStore'=>$this->dataStore('pivot'),
'rowDimension'=>'row',
'measures'=>array(
'candidate_added - sum',
'candidate_added - count',
),
'rowSort' => array(
'candidate_added - sum' => 'desc',
),
'rowCollapseLevels' => array(1),
'totalName' => 'All',
'width' => '100%',
'nameMap' => array(
'candidate_added - sum' => 'Total Candidates Added',
'candidate_added - count' => 'Number of Candidates',
),
)); */
?>
</div>
<form id='form1' class="form-inline" method="post">
<?php
PivotMatrix::create(array(
"id" => "pivotMatrix1",
'dataSource' => $this->dataStore('pivot'),
"measures"=>array(
"candidate_added - sum",
),
'rowSort' => array(
'candidate_added - sum' => 'desc',
'user_name' => 'desc',
),
'columnSort' => array(
'month' => function($a, $b) {
return (int)$a < (int)$b;
},
),
'columnCollapseLevels' => array(0),
'rowCollapseLevels' => array(0),
'width' => '100%',
'height' => '500px',
'headerMap' => function($v, $f) {
switch ($v) {
case 'candidate_added - sum': return 'Total Candiates Added';
case 'candidate_added - count': return 'Number of Canidate Added';
case 'candidate_added - avg': return 'Average Candiate Added';
case 'year': return 'Year';
case 'month': return 'Month';
case 'day': return 'Day';
case 'wk': return 'Week Commecing';
case 'team_name': return 'Team';
case 'user_name': return 'User';
}
$r = $v;
if ($f === 'year')
$r = 'Year ' . $v;
$map = array(
'1' => 'January',
'2' => 'February',
'3' => 'March',
'4' => 'April',
'5' => 'May',
'6' => 'June',
'7' => 'July',
'8' => 'August',
'9' => 'September',
'10' => 'October',
'11' => 'November',
'12' => 'December',
);
if ($f === 'month')
$r = $map[$v];
return $r;
},
'totalName' => 'All',
'waitingFields' => array(
'candidate_added - count' => 'data',
'month' => 'label',
'day' => 'label',
),
'paging' => array(
'size' => 5,
'maxDisplayedPages' => 5,
'sizeSelect' => array(5, 10, 20, 50, 100)
)
));
?>
</form>
</div>
