Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
Hi, I am using PivotMatrix with laravel with MySQL connection. It works fine if the query return data but throws an error is the MySQL query returns null.
I am using a date range filter so if data is not found between two dates it throws error
ErrorException: Undefined array key username in file vendor/koolreport/pivot/widgets/RowHeaders.tpl.php on line 30
Please do needful.
OrderReport.php
class OrderReport extends \koolreport\KoolReport
{
use \koolreport\laravel\Friendship;
use \koolreport\clients\jQuery;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
use \koolreport\excel\ExcelExportable;
protected function defaultParamValues()
{
$start_date = Carbon::now()->subMonth(1)->toDateString();
$end_date = Carbon::now()->toDateString();
return array(
"dateRange"=>array($start_date,$end_date),
);
}
protected function bindParamsToInputs()
{
return array(
"dateRange",
);
}
function settings()
{
return array(
"dataSources"=>array(
"elo"=>array(
"class"=>'\koolreport\laravel\Eloquent', // This is important
)
)
);
}
function setup()
{
if(true)
{
$node = $this->src("elo")->query(
OrderLineData::select(['order_test.month','group_categories.type AS group', 'ordersheetlinedata.source AS source', 'orderlinedata.c_quantity AS quantity','order_test.created_at as created_at'])
->leftjoin('ordersheetlinedata', function ($join) {
$join->on('ordersheetlinedata.id', '=', 'orderlinedata.order_sheet_data_id');
})->leftjoin('orderlines', function ($join) {
$join->on('orderlines.id', '=', 'orderlinedata.order_line_id');
})->leftjoin('order_test', function ($join) {
$join->on('order_test.id', '=', 'orderlines.order_id');
})->leftjoin('companies', function ($join) {
$join->on('companies.id', '=', 'orders.company_id');
})->leftjoin('group_categories', function ($join) {
$join->on('group_categories.code', '=', 'ordersheetlinedata.bcat');
})
);
if(!empty($this->params['dateRange'])){
$node->pipe(new Filter(array(
array('created_at', '>=', $this->params['dateRange'][0]),
array('created_at', '<=', $this->params['dateRange'][1]),
)))
->saveTo($node);
}
$node->pipe(new Map([
'{meta}' => function($meta) {
$meta['rows']['month']['type'] = 'string';
$meta['rows']['group']['type'] = 'string';
$meta['rows']['source']['type'] = 'string';
$meta['rows']['quantity']['type'] = 'number';
return $meta;
}
]));
$node->pipe(new Pivot(array(
"dimensions" => array(
"row" => "group,source"
),
"aggregates"=>array(
"sum" => "quantity",
)
)))
->pipe(new ColumnMeta(array(
"quantity"=>array(
'type' => 'number',
),
)))
->pipe($this->dataStore("order_data"));
}
}
}
OrderReport.view.php
<?php use \koolreport\pivot\widgets\PivotMatrix; use \koolreport\inputs\DateRangePicker;
?> <form id='form1' class="form-inline" method="post">
<?php echo csrf_field() ?>
<input type="hidden" name="koolPivotUpdate" value="1" />
<div class=" pb-5 mb-6 border-gray-200">
<div class="-ml-4 -mt-2 flex items-center justify-between flex-wrap sm:flex-nowrap">
<div class="ml-4 mt-2">
<?php
DateRangePicker::create(array(
"name" => "dateRange",
"clientEvents"=>array(
"apply.daterangepicker"=>"function(e, picker){
var pivot = window['order_data'];
var date_range = [];
date_range[0] = picker.startDate.format('YYYY-MM-DD');
date_range[1] = picker.endDate.format('YYYY-MM-DD');
console.log(date_range);
pivot.updateScope('dateRange',date_range);
pivot.update();
}",
),
));
?>
</div>
<div class="ml-4 mt-2 flex-shrink-0 space-x-3">
<button type="submit" formaction="<?php echo route('reports.export') ?>" class="inline-flex items-center justify-center px-4 py-2 border border-transparent text-sm font-medium rounded-md shadow-primary-500/50 hover:shadow-primary-400/50 shadow-lg text-white bg-primary-600 hover:bg-primary-700 focus:outline-none focus:ring-2 focus:ring-offset-2 focus:ring-primary-500 uppercase">
<svg class="-ml-0.5 mr-2 h-5 w-5" fill="none" viewBox="0 0 24 24" stroke="currentColor" stroke-width="2">
<path stroke-linecap="round" stroke-linejoin="round" d="M4 16v1a3 3 0 003 3h10a3 3 0 003-3v-1m-4-4l-4 4m0 0l-4-4m4 4V4"></path>
</svg>
Export
</button>
</div>
</div>
</div>
<div class="p-0">
<?php
PivotMatrix::create(array(
"name" => 'order_data',
"dataStore"=>$this->dataStore("order_data"),
"ajaxUrl" => route('reports.ajax'),
"serverSide" => true,
"measures"=>array(
"quantity - sum",
),
'rowSort' => array(
'group' => 'asc',
'source' => 'asc',
),
'waitingFields' => array(
"month" => "label",
),
'columnSort' => array(
'quantity - sum' => 'asc'
),
'headerMap' => function($v, $f) {
switch ($v) {
case 'quantity - sum': return 'Total Quantity';
case 'group': return 'Group';
case 'source': return 'Source';
case 'month': return 'Month';
}
return $v;
},
'totalName' => 'Total Quantity',
'showDataHeaders' => true,
"scope" => array(
"_token" => csrf_token(),
),
'cssClass' => array(
'waitingField' => function($field) {
return 'wf-' . $field;
}
),
'paging' => array(
'size' => 100,
'maxDisplayedPages' => 5,
'sizeSelect' => array(5, 10, 20, 50, 100)
),
"clientEvents"=>array(
"beforeUpdate"=>"handleAfterFieldMove",
),
));
?>
</div>
</form>
<script type="text/javascript">
function handleAfterFieldMove()
{
if(window['order_data'] != undefined)
{
var pivot = window['order_data'];
var date_range = [];
console.log($("input[name=dateRange]"));
$("input[name='dateRange[]']").each( function () {
date_range.push($(this).val());
});
pivot.updateScope('dateRange',date_range);
}
}
</script>
@Sebastian thank you for your response.
if you are checking in core php then do inspect the element you can see a warning in HTML like below :
Warning</b>: Undefined array key "budget_group" in <b>/opt/lampp/htdocs/koolreport/examples/vendor/koolreport/pivot/widgets/RowHeaders.tpl.php</b> on line <b>29
As I am using the Laravel framework so any kind of warning then laravel throws Viewexception so it's the issue in the laravel.
Tks, pls open the file koolreport/pivot/widgets/RowHeaders.tpl.php and replace the following line:
data-node = '<?= htmlspecialchars($node[$rf], ENT_QUOTES) ?>'
with this one:
data-node = '<?= htmlspecialchars((string) Util::get($node, $rf, ""), ENT_QUOTES) ?>'
Then try the report again and let us know if it solves your issue.
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo