Hi, I am experiencing a similar problem as a previous issue:- https://www.koolreport.com/forum/topics/219#pivot-matrix-not-working219
I have just made sure I am using the latest versions of the packages.
On a PivotMatrix report, when I move fields from a header to say the holding box, the first time it seems to work - however whatever date range I have selected gets reset to the current day. I am also not then able to move the field back into the header.
If I try moving any other headers, sometimes they appear to move into the required new header, but they also appear in the original field.
Please let me know if you need be to be a bit more specific. Thanks
<?php
require_once "../../../koolreport/autoload.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\CalculatedColumn;
class PosSalesCovers extends koolreport\KoolReport
{
use \koolreport\cache\FileCache;
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"),date("Y-m-d")),
"outlets"=>array(),
);
}
protected function bindParamsToInputs()
{
return array(
"dateRange"=>"dateRange",
"outlets"=>"outlets",
);
}
function settings()
{
// //Get default connection from config.php
$config = include "../../config.php";
return array(
"dataSources"=>array(
"pos"=>$config["pos"]
)
);
}
protected function setup()
{
$this->src('pos')
->query("SELECT
outlet_name AS outletName,
outlet_id AS outlet_id,
YEAR(business_date) AS businessYear,
MONTH(business_date) AS businessMonth,
DAY(business_date) AS businessDay,
shift_name AS shiftName,
category AS categoryMain,
SUM(sale_total) AS sale_total
FROM pos.report_shift_category_sales
JOIN salesareas
ON salesareas.id = outlet_id
".
(($this->params["outlets"]!=array())?"AND salesareas.id IN (:outlets)":"")
."
WHERE business_date BETWEEN :start AND :end
GROUP BY business_date, outlet_id, category, shift_name
")
->params(array(
":start"=>$this->params["dateRange"][0],
":end"=>$this->params["dateRange"][1],
":outlets"=>$this->params["outlets"]
))
->pipe(new ColumnMeta(array(
"sale_total"=>array(
'type' => 'number',
"decimals"=>0,
"prefix" => "$",
),
)))
->pipe(new Pivot(array(
"dimensions" => array(
"row" => "businessYear, businessMonth, businessDay, outletName, shiftName",
"column" => "categoryMain"
),
"aggregates"=>array(
"sum" => "sale_total",
)
)))
->pipe($this->dataStore('shift_sales'));
$this->src('pos')
->query("SELECT
outlet_name AS outletName,
shift_name AS shiftName,
YEAR(business_date) AS businessYear,
MONTH(business_date) AS businessMonth,
DAY(business_date) AS businessDay,
business_hour AS businessHour,
COUNT(DISTINCT(receipt_id)) AS receiptQty,
SUM( sale_amount ) AS sale_total,
SUM( no_of_pax ) AS paxQty,
pos.salesareas.occupancy AS occupancy
FROM pos.salesareas
INNER JOIN pos.report_shift_covers_sales
ON pos.salesareas.id = pos.report_shift_covers_sales.outlet_id
WHERE business_date BETWEEN :start AND :end
GROUP BY business_date,outlet_id,shift_name,business_hour
")
->params(array(
":start"=>$this->params["dateRange"][0],
":end"=>$this->params["dateRange"][1]
))
->pipe(new CalculatedColumn(array(
"covers_per_receipt"=>"{paxQty}/{receiptQty}",
"sales_per_receipt"=>"{sale_total}/{receiptQty}",
"sales_per_pax"=>"{sale_total}/{paxQty}",
"occupancy_pct"=>"{paxQty}/{occupancy}*100"
)))
->pipe(new ColumnMeta(array(
"receiptQty"=>array(
'type' => 'number',
),
"sale_total"=>array(
'type' => 'number',
"decimals"=>0,
"prefix" => "$",
),
"paxQty"=>array(
'type' => 'number',
),
"covers_per_receipt"=>array(
'type' => 'number',
"decimals"=>1,
),
"sales_per_receipt"=>array(
'type' => 'number',
"decimals"=>0,
"prefix" => "$",
),
"sales_per_pax"=>array(
'type' => 'number',
"decimals"=>0,
"prefix" => "$",
),
"occupancy_pct"=>array(
'type' => 'number',
"decimals"=>0,
"suffix" => "%",
),
)))
->pipe(new Pivot(array(
"dimensions" => array(
"row" => "businessYear, businessMonth, businessDay, outletName, shiftName, businessHour",
"column" => ""
),
"aggregates"=>array(
"sum" => "receiptQty,sale_total,paxQty",
"avg" => "covers_per_receipt,sales_per_receipt,sales_per_pax,occupancy_pct",
)
)))
->pipe($this->dataStore('covers_sales'));
$this->src("pos")
->query("SELECT id,name FROM salesareas
WHERE parent_id IS NULL AND house_id = 1
ORDER BY name")
->pipe($this->dataStore("outlets"));
}
}
<?php
use \koolreport\pivot\widgets\PivotMatrix;
use \koolreport\pivot\widgets\PivotTable;
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;
use \koolreport\inputs\DateRangePicker;
use \koolreport\inputs\MultiSelect;
use \koolreport\widgets\koolphp\Table;
?>
<div class="report-content">
<div class="text-center">
<h1>Shift Matrix Reports</h1>
<p class="lead">Sales and Covers reports by date range</p>
</div>
<form method="post">
<div class="row">
<div class="col-md-8 offset-md-2">
<div class="form-group">
<?php
DateRangePicker::create(array(
"name"=>"dateRange"
))
?>
</div>
<div class="form-group">
<?php
MultiSelect::create(array(
"name"=>"outlets",
"dataStore"=>$this->dataStore("outlets"),
"dataBind"=>array(
"text"=>"name",
"value"=>"id",
),
"attributes"=>array(
"class"=>"form-control",
"size"=>5,
)
));
?>
</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>
<div class="text-center">
<h2>Shift Sales Data</h2>
</div>
<form id='form1' class="form-inline" method="post">
<?php
PivotMatrix::create(array(
"id" => "pivotMatrix1",
'dataSource' => $this->dataStore('shift_sales'),
'hideSubtotalColumn' => true,
"measures"=>array(
"sale_total - sum",
),
'height' => '900px',
'headerMap' => function($v, $f) {
switch ($v) {
case 'sale_total - sum': return 'Total Sales';
case 'businessYear': return 'Year';
case 'businessMonth': return 'Month';
case 'businessDay': return 'Day';
case 'outletName': return 'Outlet';
case 'shiftName': return 'Shift';
case 'categoryMain': return 'Category';
}
$r = $v;
if ($f === 'businessYear')
$r = $v;
$map = array(
'1' => 'JAN',
'2' => 'FEB',
'3' => 'MAR',
'4' => 'APR',
'5' => 'MAY',
'6' => 'JUN',
'7' => 'JUL',
'8' => 'AUG',
'9' => 'SEP',
'10' => 'OCT',
'11' => 'NOV',
'12' => 'DEC',
);
if ($f === 'businessMonth')
$r = $map[$v];
return $r;
},
'totalName' => 'Total',
'paging' => array(
'size' => 15,
'maxDisplayedPages' => 5,
'sizeSelect' => array(5, 10, 15, 20, 50)
)
));
?>
</form>
<div class="text-center">
<h2>Cover Sales Data</h2>
</div>
<form id='form1' class="form-inline" method="post">
<?php
PivotMatrix::create(array(
"id" => "pivotMatrix2",
'dataSource' => $this->dataStore('covers_sales'),
'rowCollapseLevels' => array(2),
'hideSubtotalRow' => true,
'hideSubtotalColumn' => true,
// 'template' => 'PivotTable-Bun',
"measures"=>array(
"receiptQty - sum",
"sale_total - sum",
"paxQty - sum",
"covers_per_receipt - avg",
"sales_per_receipt - avg",
"sales_per_pax - avg",
"occupancy_pct - avg",
),
'showDataHeaders' => true,
'width' => '100%',
'height' => '900px',
'headerMap' => function($v, $f) {
switch ($v) {
case 'sale_total - sum': return 'Total Sales';
case 'receiptQty - sum': return 'Total Bills';
case 'paxQty - sum': return 'Total Covers';
case 'covers_per_receipt - avg': return 'Covers per Bill';
case 'sales_per_receipt - avg': return 'Sales per Bill';
case 'sales_per_pax - avg': return 'Sales per Cover';
case 'occupancy_pct - avg': return 'Occupancy';
case 'businessYear': return 'Year';
case 'businessMonth': return 'Month';
case 'businessDay': return 'Day';
case 'outletName': return 'Outlet';
case 'shiftName': return 'Shift';
case 'businessHour': return 'Hour';
}
$r = $v;
if ($f === 'businessYear')
$r = $v;
$map = array(
'1' => 'JAN',
'2' => 'FEB',
'3' => 'MAR',
'4' => 'APR',
'5' => 'MAY',
'6' => 'JUN',
'7' => 'JUL',
'8' => 'AUG',
'9' => 'SEP',
'10' => 'OCT',
'11' => 'NOV',
'12' => 'DEC',
);
if ($f === 'businessMonth')
$r = $map[$v];
$map = array(
'1' => '8am',
'2' => '9am',
'3' => '10am',
'4' => '11am',
'5' => '12pm',
'6' => '1pm',
'7' => '2pm',
'8' => '3pm',
'9' => '4pm',
'10' => '5pm',
'11' => '6pm',
'12' => '7pm',
'13' => '8pm',
'14' => '9pm',
'15' => '10pm',
'16' => '11pm',
'17' => '12am',
'18' => '1am',
'19' => '2am',
'20' => '3am',
'21' => '4am',
'22' => '5am',
'23' => '6am',
'24' => '7am',
);
if ($f === 'businessHour')
$r = $map[$v];
return $r;
},
'totalName' => 'Total',
'paging' => array(
'size' => 15,
'maxDisplayedPages' => 5,
'sizeSelect' => array(5, 10, 15, 20, 50)
)
));
?>
</form>
</div>
