Dear Sir,
how to implement INPUT DATE RANGE FILTER WITH PIVOT TABLE VIEW
kindly advice the below code...
ExReport.php
<?php
require_once "koolreport/core/autoload.php";
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\CalculatedColumn;
class ExReport extends \koolreport\KoolReport
{
use \koolreport\bootstrap4\Theme;
protected function defaultParamValues()
{
return array(
//"dateRange"=>array(date("Y-m-d"),date("Y-m-d")),
"dateRange"=>array(date("Y-m-d 00:00:00"),date("Y-m-d 23:59:59")),
//),
);
}
protected function bindParamsToInputs()
{
return array(
"dateRange"=>"dateRange",
//"dateRange",
);
}
public function settings()
{
return array(
"dataSources"=>array(
"Report"=>array(
"connectionString"=>"mysql:host=localhost;dbname=str_mgm",
"username"=>"root",
"password"=>"Tenda@24",
"charset"=>"utf8"
),
)
);
}
//rtaken from no pivotMatrix1
protected function setup()
{
$this->src("Report")
->query(" SELECT YEAR(Issue_Date) as orderYear, MONTH(Issue_Date) as OrderMonth, DepName, Product_Name, Total_Cost FROM Stores_Expenses_table WHERE Issue_Date BETWEEN :start AND :end ")
->params(array(
":start"=>$this->params["dateRange"][0],
":end"=>$this->params["dateRange"][1],
))
->pipe(new ColumnMeta(array(
"Total_Cost"=>array(
'type' => 'number',
"prefix" => ""
),
)))
->pipe(new Pivot(array(
"dimensions"=>array(
"column" => "orderYear, OrderMonth",
"row" => "L1, L2_NAME, L3_NAME, L4, L5",
),
"aggregates"=>array(
"sum"=>"Total_Cost",
)
)))
->pipe($this->dataStore('ExReport'));
}
}
ExReport.view.php
<?php
//use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\DateRangePicker;
//use \koolreport\inputs\MultiSelect;
use \koolreport\pivot\widgets\PivotMatrix;
//use \koolreport\pivot\processes\Pivot;
use \koolreport\pivot\widgets\PivotTable;
?>
<div class="report-content">
<div class="text-center">
<h1>List of order</h1>
<p class="lead">Choose date ranges and customer to view orders</p>
</div>
<form method="post">
<div class="row">
<div class="col-md-8 offset-md-2">
<div class="col-md-4 form-group text-center">
<?php
DateRangePicker::create(array(
"name"=>"dateRange",
//"format"=>"MMM DD, YYYY", //Jul 3rd, 2017
"ranges"=>array(
"Today"=>DateRangePicker::today(),
"Yesterday"=>DateRangePicker::yesterday(),
"Last 7 days"=>DateRangePicker::last7days(),
"Last 30 days"=>DateRangePicker::last30days(),
"This month"=>DateRangePicker::thisMonth(),
"Last month"=>DateRangePicker::lastMonth(),
)
));
?>
</div>
<div class="form-group text-center">
<button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load</button>
</div>
</div>
</div>
</form>
<?php
// $dataStore = $this->dataStore('ExReport');
PivotTable::create(array(
"name" => "PivotTable2",
"dataStore" => $this->dataStore("ExReport"),
"rowDimension" => "row",
"columnDimension" => "column",
"measures"=>array(
"Total_Cost - sum",
'Total_Cost - count',
'Total_Cost - avg',
),
'rowSort' => array(
'Total_Cost - sum' => 'desc',
),
'columnSort' => array(
'orderMonth' => function ($a, $b) {
return (int) $a < (int) $b;
},
),
'rowCollapseLevels' => array(0, 1),
'columnCollapseLevels' => array(0),
'width' => '100%',
'map' => array(
'rowHeader' => function($rowHeader, $headerInfo) {
$v = $rowHeader;
if (isset($headerInfo['childOrder']))
$v = $headerInfo['childOrder'] . ". " . $v;
return $v;
},
'columnHeader' => function($colHeader, $headerInfo) {
$v = $colHeader;
if ($headerInfo['fieldName'] === 'orderYear')
$v = 'Year-' . $v;
else if ($headerInfo['fieldName'] === 'orderQuarter')
$v = 'Quarter-' . $v;
if (isset($headerInfo['childOrder']))
$v = $headerInfo['childOrder'] . ". " . $v;
//return $v;
},
),
// 'showDataHeaders' => true,
));
?>
</div>