How can I sort teh data by date column in pivot table as you can see in image in DD/MM/YY form the dater having year 19 shoudl come on top but not coming
i am using laravel and my TeamReport.php is
<?php
namespace App\Reports;
use \koolreport\processes\Filter;
use \koolreport\pivot\processes\Pivot;
//use \koolreport\processes\Group;
use \koolreport\processes\Sort;
class TeamReport extends \koolreport\KoolReport
{
use \koolreport\laravel\Friendship;
use \koolreport\export\Exportable;
use \koolreport\excel\ExcelExportable;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
protected function defaultParamValues()
{
return array(
"dateRange"=>array(
//date('Y-m-d H:i:s', strtotime('-1 year')),
//date('Y-m-d H:i:s')
date('Y-m-d H:i:s', strtotime('-9 months')),
date('Y-m-d H:i:s', strtotime('-7 months'))
),
);
}
protected function bindParamsToInputs()
{
return array(
"dateRange"=>"dateRange",
);
}
// 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.
/*
*
* CV Sends
1st Interviews
Other Interviews
Final interviews
Committed jobs (A jobs)
Hot Leads
Job Ad leads
New Logo Jobs
*/
function setup()
{
$this->src('mysql')
->query("SELECT DATE_FORMAT(SUBDATE(nr.created_date, WEEKDAY(nr.created_date)), '%Y-%m-%d') as commenced,
CONCAT('WC ',DATE_FORMAT(SUBDATE(nr.created_date, WEEKDAY(nr.created_date)), '%d/%m/%y')) as WeekCommencing,
ct.team_name as TeamName, nr.user_name as UserName,
SUM(IF((nr.notes_type = 139 AND nr.candidate_id> 0), 1, 0)) AS CvSends,
SUM(IF(nr.notes_type = 114, 1, 0)) AS 1stInterviews,
SUM(IF(nr.notes_type = 113, 1, 0)) AS FinalInterviews,
SUM(IF(nr.notes_type = 85, 1, 0)) AS GradeAJobs,
SUM(IF(nr.notes_type = 401, 1, 0)) AS HotLeads,
SUM(IF((nr.notes_type = 104 AND nr.candidate_id> 0), 1, 0)) AS Deals
FROM `notes_report` nr
LEFT JOIN company_teams ct ON nr.team_id = ct.id
WHERE nr.notes_type
IN(139,114,113,85,401,104 ) AND nr.created_date > :start AND nr.created_date < :end
GROUP BY WeekCommencing, TeamName, UserName
ORDER BY commenced, TeamName ASC
")
->params(array(
":start"=>$this->params["dateRange"][0],
":end"=>$this->params["dateRange"][1],
))
->pipe(new Filter(array(
//array('name', 'startsWith', 'a'),
//array('year', '>', 2003),
)))
->pipe(new Pivot(array(
"dimensions"=>array(
//"column" => "year",
"row" => "WeekCommencing, TeamName, UserName",
),
"aggregates"=>array(
"sum"=>"CvSends,1stInterviews,FinalInterviews,GradeAJobs,HotLeads,Deals",
),
//'partialProcessing' => true, //add this line
)))
->pipe(new Sort(array(
"commenced"=>"asc"
)))
->pipe($this->dataStore('pivot'));
}
}
and my TeamReport.view.php is
<?php
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\inputs\DateRangePicker;
?>
<div class='report-content'>
<div class="text-center">
<h1>Pulse Pivot Report</h1>
<p class="lead">
Choose date range
</p>
<form method="post" >
<div class="row">
<div class="col-md-8 offset-md-2">
<div class="form-group">
<?php echo csrf_field() ?>
<?php
DateRangePicker::create(array(
"name"=>"dateRange"
))
?>
</div>
<div class="form-group text-center">
<button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load</button>
</div>
</div>
</div>
</form>
</div>
<?php
PivotTable::create(array(
'dataStore'=>$this->dataStore('pivot'),
'showDataHeaders' => true,
'rowCollapseLevels' => array(0),
'columnCollapseLevels' => array(0, 0, 0),
'headerMap' => array(
'CvSends - sum' => 'Cv Sends',
'1stInterviews - sum' => '1st Interviews',
'FinalInterviews - sum' => 'Final Interviews',
'GradeAJobs - sum' => 'Grade A Jobs',
'HotLeads - sum' => 'Hot Leads',
'Deals - sum' => 'Deals',
),
'rowSort' => array(
'commenced' => 'asc',
),
'columnSort' => array(
'commenced' => 'asc',
'TeamName' => 'asc'
),
//'hideTotalRow' => true,
//'hideTotalColumn' => true,
//'hideSubtotalRow' => true,
//'hideSubtotalColumn' => true,
/*'headerMap' => function($v, $f) {
switch ($v) {
case 'CvSends': return 'Cand Added';
case '1stIntervs': return 'Number of Sales';
case 'FinalIntervs': return 'Average Sales';
case 'GradeAJobs': return 'Year';
case 'HotLeads': return 'Week';
case 'Deals': return 'Day';
}
},*/
/*"rowDimension"=>"row",
"columnDimension"=>"column",
'rowDimension'=>'row',
'measures'=>array(
'CvSends',
'1stIntervs',
),
'rowSort' => array(
'candidate_added - sum' => 'desc',
),
'totalName' => 'All',
'width' => '100%',
'nameMap' => array(
'candidate_added - sum' => 'Total Candidates Added',
'candidate_added - count' => 'Number of Candidates',
),*/
));
?>
</div>