i am struggling to learn KoolReport and created 2 reports like OrderList examples with my own tables and added daterange and customer parameters to filter data. now i am trying to create a Pivot report refering example report Bun Template , this is first attempt and definitely i have to consult here with seniors but before asking i have to struggle first to achieve. please check code below and advise as it render but only showing order_date on row other 3 columns are null, and also showing error below first page "Sorry, we found no orders." (it is custom message in case no data found). hope i am going in right direction with the help of seniors here. please help.
current output is as below:
the required result is in screenshot:
RiderSummary.php
<?php
namespace App\Reports;
require_once ROOTPATH . "load.koolreport.php";
use \koolreport\pivot\processes\Pivot;
use \koolreport\pivot\processes\PivotExtract;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Filter;
use \koolreport\KoolReport;
use \koolreport\processes\Sort;
class RiderSummary extends \koolreport\KoolReport
{
//use \koolreport\bootstrap4\Theme;
use \koolreport\amazing\Theme;
use \koolreport\codeigniter\Friendship;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
protected function defaultParamValues()
{
return array(
"dateRange"=>array(
"2022-12-01",
"2022-12-10",
),
"riders"=>NULL,
);
}
protected function bindParamsToInputs()
{
return array(
"dateRange"=>"dateRange",
"riders"=>"riders",
);
}
public function setup()
{
$node = $this->src("default")
->query("SELECT DATE(order_date) AS order_date, rider_id, rider_name, no_of_orders, rider_charges AS dc, order_total AS gvm FROM rider_data WHERE
rider_id = ".($this->params["riders"] == '' ? 'rider_id' : ':riders')."
AND
order_date >= :start
AND
order_date <= :end")
->params(array(
":start"=>$this->params["dateRange"][0],
":end"=>$this->params["dateRange"][1],
":riders"=>$this->params["riders"]
));
// $node->pipe(new Filter(array(
// array('order_date', '>', '0'),
// array('rider_name', '>', 'A'),
// )))
$node->pipe(new ColumnMeta(array(
"no_of_orders" => array(
'type' => 'number',
),
"dc" => array(
'type' => 'number',
),
"gvm" => array(
'type' => 'number',
),
)))
->saveTo($node2);
$node2->pipe(new Pivot(array(
"dimensions" => array(
"column" => "rider_name",
"row" => "order_date",
),
"aggregates" => array(
"count" => "no_of_orders",
"sum" => "dc",
"sum" => "gvm",
),
)))->pipe($this->dataStore('riderSales'));
$this->src("default")->query("
SELECT DISTINCT
rider_id,
rider_name
FROM
rider_summary
ORDER BY rider_name
")
->pipe($this->dataStore("riders"));
// echo $this->endSection();
}
}
RiderSummary.view.php
<?php
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\pivot\widgets\PivotMatrix;
//use \koolreport\datagrid\DataTables;
//use \koolreport\bootstrap4\Theme;
//use \koolreport\amazing\Theme;
// use \koolreport\clients\Bootstrap;
use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\DateRangePicker;
use \koolreport\inputs\Select2;
?>
<style>
.amazing {
width: 40%;
}
.select2 {
width: 100% !important;
}
.sidebar {
background: #ffffff !important;
}
</style>
<div class='report-content'>
<div class="text-center">
<h2>Rider Summary</h2>
</div>
<form method="post">
<div class="row">
<div class="col-md-8 offset-md-2">
<div class="form-group" style="display: flex; flex-direction: row; flex-wrap: nowrap; align-content: center; justify-content: center; align-items: center;">
<?php
DateRangePicker::create(array(
"name"=>"dateRange",
"format"=>"YYYY-MM-DD"
))
?>
<!-- </div>
<div class="form-group" style="display: flex; flex-direction: column; flex-wrap: nowrap; align-content: center; justify-content: center; align-items: center;"> -->
<strong>Select Rider</strong>
<?php
Select2::create(array(
"name"=>"riders",
"dataStore"=>$this->dataStore("riders"),
"defaultOption"=>array(
"All"=>NULL
),
"dataBind"=>array(
"text"=>"rider_name",
"value"=>"rider_id",
),
"attributes"=>array(
"class"=>"form-control",
)
));
?>
<div class="form-group text-center">
<button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load</button>
</div>
</div>
</div>
</div>
</form>
</div>
<div>
<?php
if($this->dataStore("riderSales")->countData()>0)
{
$dataStore = $this->dataStore('riderSales');
PivotTable::create(array(
"name" => "PivotTable1",
'template' => 'PivotTable-Bun',
"dataStore" => $dataStore,
"rowDimension" => "row",
"columnDimension" => "column",
"measures"=>array(
"no_of_orders - count",
"dc - sum",
"gvm - sum",
),
'rowSort' => array(
'order_date' => 'desc',
),
'rowCollapseLevels' => array(1),
'columnCollapseLevels' => array(0),
'width' => '100%',
));
}
else
{
?>
<div class="alert alert-warning">
<i class="glyphicon glyphicon-info-sign"></i> Sorry, we found no orders.
</div>
<?php
}
?>
</div>
regards
