KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.

ColumnMeta being ignored with KoolReport 4.0 #915

Open Ardan Peddell opened this topic on on Jun 9 - 10 comments

Ardan Peddell commented on Jun 9

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>
KoolReport commented on Jun 9

We do not make change to ColumnMeta. Could you please post your code, I would like to see.

Ardan Peddell commented on Jun 9

Hi, following is another example of a report that doesn't seem to pass the ColumnMeta formatting:-

<?php

require_once "../../../koolreport/core/autoload.php";

use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\Group;
use \koolreport\processes\CalculatedColumn;

class DailySalesReport2 extends koolreport\KoolReport
{
//  use \koolreport\amazing\Theme;
  use \koolreport\inputs\Bindable;
  use \koolreport\inputs\POSTBinding;
  use \koolreport\export\Exportable;

  protected function defaultParamValues()
  {
      return array(
          "reportDate"=>date("Y-m-d"),
      );
  }

  protected function bindParamsToInputs()
  {
      return array(
          "reportDate",
      );
  }

    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,
          shift_name AS shiftName,
          category AS categoryMain,
          sale_total AS saleTotal,
          cost_total AS costTotal,
          ROUND((cost_total / sale_total * 100),2) AS cosTotal
                FROM pos.report_shift_category_sales
                WHERE business_date = :start
                ")
      ->params(array(
            ":start"=>$this->params["reportDate"]
      ))
      ->saveTo($node1);

       $node1->pipe(new ColumnMeta(array(
            "saleTotal"=>array(
            'type' => 'number',
            "decimals"=>0,
            "prefix" => "$",
            ),
            "costTotal"=>array(
            'type' => 'number',
            "decimals"=>0,
            "prefix" => "$",
            ),
         )))
         ->pipe(new Pivot(array(
             "dimensions"=>array(
                 "row" => "shiftName",
                 "column" => "outletName"
             ),
             "aggregates"=>array(
                 "sum"=>"saleTotal,costTotal",
                 "avg"=>"cosTotal"
             )
         )))
        ->pipe($this->dataStore('categorySales'));

        $node1->pipe(new ColumnMeta(array(
             "saleTotal"=>array(
             'type' => 'number',
             "decimals"=>0,
             "prefix" => "$",
             ),
             "costTotal"=>array(
             'type' => 'number',
             "decimals"=>0,
             "prefix" => "$",
             ),
          )))
          ->pipe(new Pivot(array(
              "dimensions"=>array(
                  "row" => "categoryMain",
                  "column" => "outletName"
              ),
              "aggregates"=>array(
                  "sum"=>"saleTotal,costTotal",
                  "avg"=>"cosTotal"
              )
          )))
         ->pipe($this->dataStore('shiftSales'));

         $node1->pipe(new Group(array(
              "by"=>"",
              "sum"=>"saleTotal,costTotal",
              "avg"=>"cosTotal"
          )))
          ->pipe($this->dataStore('daySalesTotal'));
    }
}

<?php
    use \koolreport\inputs\DateTimePicker;
    use \koolreport\pivot\widgets\PivotTable;
    use \koolreport\amazing\SimpleCard;

?>

<div class="report-content">
  <div class="text-center">
    <h1>Daily Sales</h1>
    <p class="lead">Single Day Consolidated Report
    </p>
  </div>

  <form method="post">
    <input type='hidden' name='koolPivotUpdate' />
    <div class='row'>
      <div class="col-md-4">
        <div class="form-group">
        <?php DateTimePicker::create(array("name"=>"reportDate")); ?>
        </div>
      </div>
      <div class="form-group text-center">
         <button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load</button>
      </div>
    </div>
  </form>
  <div class="row">
      <div class="col-sm-4">
      <?php
      SimpleCard::create(array(
          "title"=>"Total Sales",
          "value"=>$this->dataStore("daySalesTotal")->sum("saleTotal"),
          "preset"=>"success",
          "format"=>array(
              "value"=>array(
                  "prefix"=>"$"
              )
          ),
          "cssClass"=>array(
              "icon"=>"fa fa-dollar",
          ),
          "cssStyle"=>array(
              "title"=>"font-weight:bold",
              "icon"=>"font-size:32px"
          )
      ));
      ?>
      </div>
      <div class="col-sm-4">
      <?php
      SimpleCard::create(array(
              "title"=>"Total Cost",
              "value"=>$this->dataStore("daySalesTotal")->sum("costTotal"),
              "preset"=>"danger",
              "format"=>array(
                  "value"=>array(
                      "prefix"=>"$"
                  )
              ),
              "cssClass"=>array(
                  "icon"=>"fa fa-dollar",
              ),
              "cssStyle"=>array(
                  "title"=>"font-weight:bold",
                  "icon"=>"font-size:32px"
              )
          ));
          ?>
          </div>
          <div class="col-sm-4">
          <?php
          SimpleCard::create(array(
                  "title"=>"Cost of Sales",
                  "value"=>$this->dataStore("daySalesTotal")->avg("cosTotal"),
                  "preset"=>"info",
                  "format"=>array(
                      "value"=>array(
                          "suffix"=>"%"
                      )
                  ),
                  "cssClass"=>array(
                      "icon"=>"fa fa-percent",
                  ),
                  "cssStyle"=>array(
                      "title"=>"font-weight:bold",
                      "icon"=>"font-size:32px"
                  )
              ));
              ?>
              </div>
  </div>
  </div>
  <div class="col-sm-12">

    <?php
    PivotTable::create(array(
              "id" => "pivotTable1",
              "dataSource"=>$this->dataStore('categorySales'),
  //            'template' => 'PivotMatrix-Bun',
  //            'hideSubtotalRow' => true,
              "hideSubtotalColumn" => true,
  //            'hideTotalRow' => true,
  //            'hideTotalColumn' => true,
  //            'rowCollapseLevels' => array(0),
  //            'columnCollapseLevels' => array(0),
              "measures"=>array(
                  "saleTotal - sum",
                  "costTotal - sum",
  //                "cosTotal - avg",
              ),
  //            "showDataHeaders" => true,
  //            'width' => '800px',
  //            'height' => '800px',
              "totalName" => "Totals",
  //            'columnWidth' => '100px',
    ));
    ?>
    <?php
    PivotTable::create(array(
              "id" => "pivotTable2",
              "dataSource"=>$this->dataStore('shiftSales'),
  //            'template' => 'PivotMatrix-Bun',
  //            'hideSubtotalRow' => true,
              "hideSubtotalColumn" => true,
  //            'hideTotalRow' => true,
  //            'hideTotalColumn' => true,
  //            'rowCollapseLevels' => array(0),
  //            'columnCollapseLevels' => array(0),
              "headerMap" => array(
                  "saleTotal - sum" => "Total Sales",
                  "costTotal - sum" => "Total Cost",
  //                'cosTotal - avg' => 'Cos',
              ),
              "measures"=>array(
                  "saleTotal - sum",
                  "costTotal - sum",
  //                "cosTotal - avg",
              ),
  //            "showDataHeaders" => true,
  //            'width' => '800px',
  //            'height' => '800px',
              "totalName" => "Totals",
  //            'columnWidth' => '100px',
    ));
    ?>
  </div>

</div>

Results as folows:-

David Winterburn commented on Jun 10

Hi Ardan,

We've fixed this issue with the newly released version 6.1.0 of Pivot in KoolReport Pro 4.1.0. Please try installing the new version and let us know if it fixes the issue for you. Thanks!

Keith Burke commented on Jun 10

Nope. Problem still persists with 4.1.0.

Ardan Peddell commented on Jun 10

Looks like that did the trick. ColumnMeta process seems to process the data now as expected. Will test further and let you know if any issues. Thank you for the quick work

Ardan Peddell commented on Jun 10

One issue - I think headerMap doesn't seem to be working and more. It does work as expected in V3.25.4

Keith Burke commented on Jun 10

Yup, It's the headerMap that isn't working for me.

David Winterburn commented on Jun 11

Thanks for your all feedback! We've fixed the issue with "headerMap" and it will be available soon.

David Winterburn commented on Jun 11

Hi,

Please try installing Pivot 6.1.1 and see if it fixes the "headerMap" property for you. Thanks!

Ardan Peddell commented on Jun 11

Hi David, Just tested and headerMap is working again - thanks for the fix.

KoolReport Is Free and Open-Source!

KoolReport is a professional php reporting framework which saves you tons of time to construct dynamic data report & dashboard.

  • Connect to various datasources such as MySQL, SQL Server, MongoDB or even from CSV or Excel file.
  • Contain series of powerful built-in data processes for your data manipulation.
  • Generate stunning charts and graphs to help you communicate data insights to your audiences effectively.
  • Integrate seamlessly with any php frameworks such as Laravel, CodeIgniter, Symfony.
Download Now and register our tutorials to get started!

Download KoolReport
solved

None