KoolReport's Forum

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

PivotMatrix not responding as expected #908

Open Ardan Peddell opened this topic on on Jun 6 - 12 comments

Ardan Peddell commented on Jun 6

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>
David Winterburn commented on Jun 10

Hi Ardan,

I think what you described is keeping PivotMatrix's state across form submitting. We will find a simple solution for this problem. Meanwhile there's an workaround you could try. Please add the following hidden input to the form where PivotMatrix is in:

<form>
    ...
    <?php PivotMatrix::create(...); ?>
    <input type="hidden" name="koolPivotUpdate" value=1 />
    ...
</form>

Let us know if we understand your question correctly. Thanks!

Ardan Peddell commented on Jun 10

Hi David, I don't think i'm talking about keeping PivotMatrix state across submitting in this instance - although that is another issue I am trying to work at. What I mean is that for the script example above, when I first load the report I get the following:-

However, if I then drag one of the row fields up to the holding area, the filed rows do not change - although the ddata does, and you can see the field then shows in both the row and holding area. This issue happens whether I drag rows to columns, or visa versa etc.

The screenshots above are with the just release 4.1.0 version

It may be an issue with my code however, as the figures on the 2nd screenshot should actually increase as they should show without the 'outletName' grouping - but really not sure and would appreciate your help.

Thanks, Ardan

David Winterburn commented on Jun 11

Hi Ardan,

It seems this bug happens when there're multiple PivotMatrixes in a page. We are aware of this and will release a fix soon. Thanks for your feedback!

David Winterburn commented on Jun 11

Please try installing Pivot 6.1.1 and see if it fixes this problem for you. Thanks!

Ardan Peddell commented 7 days ago

Hi David, I have tried again on a report with two PivotMatrix and the problem still exists with 6.1.1. With only one PivotMatrix on the report, the draggable fields works as expected - however, there seems to be an issue with choosing the sort order on any of the fields - none of the sort choices seem to do anything.

David Winterburn commented 6 days ago

Hi Ardan,

Is it possible for you to send me a link to your page where this happens? If it is please send the link via email to support@koolphp.net. Thanks!

Ardan Peddell commented 6 days ago

Hi David, link sent via email

David Winterburn commented 6 days ago

Hi Ardan,

It costed me some time but I've found out why the PivotMatrixes in your report behave so strangely. Basically you use a cache trait (\koolreport\cache\FileCache in this case) which reuses previous same name datastores so you won't be able to see newer results when changing a PivotMatrix. Cache traits in the current version use a report's parameters as keys to get cached data, which regrettedly don't include a PivotMatrix's state.

A solution for now is remove the cache trait and see if your report works normally. We will see in the future for PivotMatrix to work with Cache if it is possible. Thanks!

Ardan Peddell commented 6 days ago

Hi David, Noted on the cache and will remove it. Many thanks for taking the time to find the issue.

Ardan Peddell commented 6 days ago

Hi David, removing the unused \koolreport\cache\FileCache worked and the dragging fields process works now.

However, two issues still remain:- 1. With a DateRangePicker select of multiple days, whenever a field is dragged on the report to say the holding area, the data reverts back to only one day (current day) 2. Sorting on the data fields still has no effect.

I will see tomorrow if I can find any obvious code errors that may affect this

David Winterburn commented 5 days ago

Hi Ardan,

To keep the parameters' state (says "dateRange") when changing a PivotMatrix, please add the following property to your PivotMatrix:

    PivotMatrix::create(array(
                    ...
                    "scope" =>  array(
                        "dateRange" => $this->params['dateRange']
                    ),
                    ...
    ));

As for sorting, would you please turn your link live again for us to look into it for you? Thanks!

Ardan Peddell commented 4 days ago

Hi David - the scope entry seems to have done the trick. I'll email you later today once I have the link live again to check the sorting Many thanks again

Give tips to supporter for his good work

If you feel that supporter has done a good work, consider giving him some credit. Any amount put into the tips box below is appreciated. By doing so, you have contribtuted to the existence of KoolReport and the quality of support.

Tips box
None yet

Pivot