Hi guys, I have just updated to V4 and all my reports that use ColumMeta to format the data, such as setting number of decimal places, seems to have stopped working.
Prior to V4 the reports that have decimals set to 0, are now showing the raw data.
Is this a bug, or is there a change in the ColumnMeta formatting?
Thanks
Code as follows:-
PosSalesCovers.php
<?php
require_once "../../../koolreport/core/autoload.php";
//Disable memory_limit by setting it to minus 1.
ini_set("memory_limit", "-1");
//Disable the time limit by setting it to 0.
set_time_limit(0);
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;
// use \koolreport\amazing\Theme;
// function cacheSettings()
// {
// return array(
// "ttl"=>60,
// );
// }
function defaultParamValues()
{
return array(
"dateRange"=>array(date("Y-m-d"),date("Y-m-d")),
"outlets"=>array(),
);
}
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"]
)
);
}
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(
"column" => "categoryMain",
"row" => "businessYear, businessMonth, businessDay, outletName, shiftName",
),
"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(
"column" => "",
"row" => "businessYear, businessMonth, businessDay, outletName, shiftName",
),
"aggregates"=>array(
"sum" => "receiptQty,sale_total,paxQty",
"avg" => "covers_per_receipt,sales_per_receipt,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"));
}
}
PosSalesCovers.view.php
<?php
use \koolreport\pivot\widgets\PivotMatrix;
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\pivot\processes\Pivot;
use \koolreport\instant\Widget;
use \koolreport\inputs\DateRangePicker;
use \koolreport\inputs\MultiSelect;
?>
<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">
<input type='hidden' name='koolPivotUpdate' />
<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>
<div class="text-center">
<h2>Shift Sales Data</h2>
</div>
<?php
PivotMatrix::create(array(
"id" => "pivotMatrix_1",
'dataSource' => $this->dataStore('shift_sales'),
// 'rowCollapseLevels' => array(2),
// 'columnCollapseLevels' => array(0,0,0),
// 'hideSubtotalRow' => true,
'hideSubtotalColumn' => true,
// 'hideTotalRow' => true,
// 'hideTotalColumn' => true,
// 'showDataHeaders' => true,
// 'template' => 'PivotMatrix-Bun',
"measures"=>array(
"sale_total - sum",
),
// 'width' => '100%',
'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' => 20,
'maxDisplayedPages' => 5,
'sizeSelect' => array(5, 10, 15, 20, 50)
)
));
?>
<div class="text-center">
<h2>Cover Sales Data</h2>
</div>
<?php
PivotMatrix::create(array(
"id" => "pivotMatrix_2",
'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",
"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',
'waitingFields' => array(
'sales_per_pax - avg' => 'data',
'Hour' => 'label',
),
'paging' => array(
'size' => 20,
'maxDisplayedPages' => 5,
'sizeSelect' => array(5, 10, 15, 20, 50)
)
));
?>
</form>
</div>
