KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Are there any breaking changes moving from KR Pro 5.1.0 to 5.6.1? #2670

Open Keith Burke opened this topic on on May 11 - 16 comments

Keith Burke commented on May 11

So, I've been running 5.1.0 for a long time. I only recently got around to upgrading to 5.6.1 using Composer.

  1. I updated my development machine [windows 10 xampp php 7.4], tested a few reports. All looked good.
  2. I updated my test webserver [centos 7, php 7.4], tested a few reports. All looked good.
  3. Same with production webserver [identical to test server]. All looked good.

However, I have a small subset of reports that just will not show any data. I reverted back to 5.1.0 and the reports ran correctly. Upgraded again to 5.6.1 and reports run but do not show any data without any errors on the webpage nor in the console.

I've emptied the kool-reports assets folder, just in case there are conflicts, I've cleared browser caches etc. I've var_dumped the datastore result and can see the expected data. It's just that the data won't display in the DataTable on a small subset of reports.

Most of my reports are identical in format.

1. Run the SQL [mysqli]
2. The view links the datastore result object to the DataGrid/DataTable.
3. Each view has a datagrid\DataTables,  inputs\BSelect and two inputs\DateTimePicker.
4. My problem reports also have a google\ColumnChart but the problem persists even if I remove all traces of the chart.

It may be a co-incidence but on my problem reports, if I only select 10 or less items from the BSelect, data then appears in the DataTable.

My view is relatively standard and is below

I notice that the changelog for 5.6.1 mentions some changes for DataStore related to php 8.1. Maybe this has effected it somehow?

Has anything like this come up before? It's taking me so much time to work this out. Besides anything above, my reports work on 5.1.0 but some of them do not on 5.6.1.

<?php 
    use \koolreport\datagrid\DataTables;
    use \koolreport\widgets\google\ColumnChart;
    use \koolreport\inputs\BSelect;    
    use \koolreport\inputs\DateTimePicker;    
?>

<div class="row">
    <div class="col-md-4">
        <form method="post">
            <div class="row">
            <div class="col-md-4 form-group">
                <center><p class="bg-primary">Sites</p></center>
<?php
                BSelect::create(array(
                    "name"=>"siteSelect",
                    "placeholder"=>"Select Site",
                    "multiple"=>true,
                    "dataStore"=>$this->dataStore("names_result"),
                    "dataBind"=>"site_name",
                    "attributes"=>array(
                        "size"=>10,
                    ),
                    'options' => array(
                        'numberDisplayed' => 5,
                        'includeSelectAllOption' => true
                    )
                ));
?>
            </div>
            <div class="col-md-8 form-group">
                <div class="col-md-6">
                    <center><p class="bg-primary">From Date</p></center>
                    <?php
                    DateTimePicker::create(array(
                        "name"=>"startDatePicker",
                        "maxDate"=>"@endDatePicker",
                        "format"=>"DD/MM/YYYY HH:mm"
                    ));
                    ?>
                </div>
                <div class="col-md-6">
                    <center><p class="bg-primary">To Date</p></center>
                    <?php
                    DateTimePicker::create(array(
                        "name"=>"endDatePicker",
                        "minDate"=>"@startDatePicker",
                        "format"=>"DD/MM/YYYY HH:mm"
                    ));
                    ?>
                </div>
            </div>
                </div><div class="row">
            <div class="col-md-4 form-group text-center">
                <button class="btn btn-success pull-left"><i class="glyphicon glyphicon-refresh"></i> Refresh</button>
            </div></div>
        </form>
    </div>
    <div class="col-md-4"">        
        <h1><center>Daily Footfall Detail</center></h1>        
    </div>
    <div class="col-md-4" style="margin:1px;padding:0px"> </div>
</div>
<hr>
<div class="row">
    <div class="col-md-12">
        <center><p class="bg-primary">Daily Footfall Detail - Between <?php echo $this->params["startDatePicker"] ;?> and <?php echo $this->params["endDatePicker"] ;?></p></center>
<?php   
 ColumnChart::create(array(
        "dataStore"=>$this->dataStore('query_result'),
        "width"=>"100%",
        "height"=>"250px",
        "columns"=>array(
            "EntryDate"
            ,"Daily Total"	
        )
    ));

?>
    </div>
</div>
<div class="row">
    <div class="col-md-12">
<?php
		DataTables::create(array(
		    "dataStore"=>$this->dataStore('query_result')
                    ,"showFooter"=>"bottom"
		    ,"cssClass"=>array(
		        "table"=>"table table-hover table-bordered",
                        "th"=>"cssHeader",
                        "tr"=>"cssItem"
			)
		    ,"options"=>array(
							"colReorder"=>true,
							"fixedHeader"=>true,
							"select"=>true,
							"info"=>false,
		        			)
		));
?>
    </div>
</div>

Sebastian Morales commented on May 12

Pls let us know whether this happen with Core 5.5.0? And what if you use core/Table instead of datagrid/DataTables? Tks,

Keith Burke commented on May 12

So,

KR Pro 5.9.0 which contains core 5.5.0 : DataGrid/DataTable Works fine KR Pro 5.9.1 also works KR Pro 5.10.0, 5.16.0, 5.16.1 [core 5.6.0] : Fails to show data without error.

koolphp\Table works or doesn't work with the versions as above with the dataGrid.

So it looks like it might have been the work relating to the DataStore / PHP 8.1 changes?

Finding the KoolReport Pro Composer versions was more difficult that it should have been :) Anyone reading, who knows as much about Composer as I do, and that's not a lot..... To show all versions of KRPro, run the following command...

composer show "koolreport/pro" -a

Which gives you these versions, as of 12 May 2022. But good luck knowing which core versions are in them :)

versions : * 5.16.1, 5.16.0, 5.10.0, 5.9.1, 5.9.0, 5.7.1, 5.7.0, 5.0.4, 5.0.3, 5.0.2, 5.0.1, 5.0.0, 4.7.1, 4.7.0, 4.5.1, 4.5.0, 4.3.2, 4.3.1, 4.3.0, 4.1.2, 4.1.1, 4.1.0, 4.0.1, 4.0.0

Sebastian Morales commented on May 12

Hi, I think there's a bug with DataStore's latest version in function offsetGet. Pls open the file koolreport/core/src/core/DataStore.php and remove the following line:

    public function offsetGet($index)
    {
        return null; // remove this line
        return isset($this->rows[$index]) ? $this->rows[$index] : null;
    }

Let us know if this works for you. We will publish a new version soon to fix this bug. Tks,

Keith Burke commented on May 12

Good catch. Definite bug but not the bug that's effecting me. Still no data shows on 5.6.0 implementing your change.

I noticed that there were quite some changes in 5.6.0 DataStore.php, so I just copied in the 5.1.0 version but kept everything else the same. Still didn't work.

Sebastian Morales commented on May 12

Does this happen with Core 5.5.0 (Pro 5.9.0)?

Keith Burke commented on May 12

Everything appears to work perfectly with Core 5.5.0 / Pro 5.9.0.

Sebastian Morales commented on May 12

Pls post your report's setup where you query and pipe data to your datastore. Tks,

Keith Burke commented on May 12

This is the entire file. I modified the field names in the SQL to protect the innocent. Forgive any spelling mistakes, if any but the SQL runs perfectly unmodified.


include ("../../includes/koolReportAutoLoad.php");

    use \koolreport\processes\Sort;
    
class DailyFootfall extends koolreport\KoolReport
{

    use \koolreport\clients\Bootstrap;
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;

    protected function defaultParamValues()
    {
        $this->src('query_source')
        ->query("Select site_name from sites where site_status in (1);")
        ->pipe($this->dataStore('select_all_site_names_result'))
        ->requestDataSending();
        
        $siteData = $this->dataStore("select_all_site_names_result")->data();
        
        
        $siteList = array();
        
        foreach ($siteData as $item)
            $siteList[] = $item["site_name"];
        
        // Below is to give yesterday's date.
        // time() - 60 * 60 * 24
        return array(
            "siteSelect"=>array(),
            "siteSelect"=>$siteList,
            "startDatePicker"=>date("Y-m-d 00:00:00", time() - 60 * 60 * 24 * 14),
            "endDatePicker"=>date("Y-m-d 23:59:59", time() - 60 * 60 * 24),
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "siteSelect"=>"siteSelect",
            "startDatePicker",
            "endDatePicker",
            "options"
        );
    }

    public function settings()
    {
        $config = include "../../includes/config.php";
        return $config;
    }

    public function setup()
    {
        $this->src('query_source')
        ->query("SELECT
                    `x`.`EntryDate` AS `EntryDate`,
                    sum(Site1)  as Site1,
                    sum(Site2)  as Site2,
                    sum(Site3)  as Site3,
                    sum(Site4)  as Site4,
                    sum(Site5)  as Site5,
                    sum(Site6)  as Site6,
                    sum(Site7)  as Site7,
                    sum(Site8)  as Site8,
                    sum(Site9)  as Site9,
                    sum(Site10) as Site10,
                    sum(Site11) as Site11,
                    sum(Site12) as Site12,
                    sum(Site13) as Site13,
                    sum(Site14) as Site14,
                    sum(Site15) as Site15,
                    sum(Site16) as Site16,
                    sum(Site17) as Site17,
                    sum(Total) as 'Daily Total'
                FROM
                    (SELECT 
                        `s`.`site_name` AS `site`,
                            DATE_FORMAT( a.entry_DateTime,  '%Y-%m-%d' ) EntryDate
                            , case when s.site_Name = 'Site1' then 1 else 0 end as Site1
                            , case when s.site_Name = 'Site2' then 1 else 0 end as Site2
                            , case when s.site_Name = 'Site3' then 1 else 0 end as Site3
                            , case when s.site_Name = 'Site4' then 1 else 0 end as Site4
                            , case when s.site_Name = 'Site5' then 1 else 0 end as Site5
                            , case when s.site_Name = 'Site6' then 1 else 0 end as Site6
                            , case when s.site_Name = 'Site7' then 1 else 0 end as Site7
                            , case when s.site_Name = 'Site8' then 1 else 0 end as Site8
                            , case when s.site_Name = 'Site9' then 1 else 0 end as Site9
                            , case when s.site_Name = 'Site10' then 1 else 0 end as Site10
                            , case when s.site_Name = 'Site11' then 1 else 0 end as Site11
                            , case when s.site_Name = 'Site12' then 1 else 0 end as Site12
                            , case when s.site_Name = 'Site13' then 1 else 0 end as Site13
                            , case when s.site_Name = 'Site14' then 1 else 0 end as Site14
                            , case when s.site_Name = 'Site15' then 1 else 0 end as Site15
                            , case when s.site_Name = 'Site16' then 1 else 0 end as Site16
                            , case when s.site_Name = 'Site17' then 1 else 0 end as Site17
                , 1 as Total
                    FROM
                        (`access_control`.`access_history` `a`  USE INDEX (ndxEntryDate)
                        JOIN `members`.`sites` `s` ON ((`a`.`site_id` = `s`.`id`)))
                    WHERE `s`.`site_name` IN (:sites) and `a`.`entry_datetime` between :start and :end 
                ) `x`
                GROUP BY `x`.`EntryDate`;")
        ->params(array(
            ":sites"=>$this->params["siteSelect"],
            ":start"=>$this->params["startDatePicker"],
            ":end"=>$this->params["endDatePicker"]
        ))
		->pipe(new Sort(array("EntryDate"=>"asc")))
        ->pipe($this->dataStore('query_result'));
        
        
        
        
  ////////////////////////////////////////////////////
	$this->src('query_source')
	->query("Select * from sites where site_status in (1);")
	->pipe(new Sort(array("site_name"=>"asc")))
	->pipe($this->dataStore('site_names_result'));
  ////////////////////////////////////////////////////      
    }
}

Sebastian Morales commented on May 12

Thanks a lot for your feedback! In your report's settings()'s config.php, do you use the default PdoDataSource class or or MySQLDataSource one? If it's the former, pls try to copy content of PdoDataSource.php in Core 5.5.0 to Core.5.6.0 and see if it solves the issue. Rgds,

Keith Burke commented on May 12

I use the MySQLDataSource. I copied the MySQLDataSource.php from 5.5.0 to 5.6.1 and everything works. We are getting much closer. Thanks.

    return array(
            "dataSources"=>array(
                "query_source"=>array(
                    "host"=>*************,
                    "dbname"=>*************,
                    "username"=>*************,
                    "password"=>*************,
                    "charset"=>"utf8",
                    "class" => "\koolreport\datasources\MySQLDataSource",
                ),
            ),
            "assets"=>array(
                "path"=>"C:/xampp/htdocs/******public_html/assets/kool-reports",
                "url"=>"/assets/kool-reports"
            ),
        );
Sebastian Morales commented on May 12

Can you print out and let us know your report params value in report setup where there should be data but non appears:

    public function setup()
    {
        var_dump($this->params);
        ...
Keith Burke commented on May 12

Here you go. And the weird thing is, if I pick 10 or less sites from the BSelect, the data shows. Any more than 10, and no data displays.

array(3) { ["siteSelect"]=> array(17) { [0]=> string(13) "Site1" [1]=> string(14) "Site2" [2]=> string(3) "Site3" [3]=> string(9) "Site4" [4]=> string(10) "Site5" [5]=> string(7) "Site6" [6]=> string(14) "Site7" [7]=> string(13) "Site8" [8]=> string(13) "Site9" [9]=> string(13) "Site10" [10]=> string(12) "Site11" [11]=> string(10) "Site12" [12]=> string(8) "Site13" [13]=> string(11) "Site14" [14]=> string(10) "Site15" [15]=> string(6) "Site16" [16]=> string(8) "Site17" } ["startDatePicker"]=> string(19) "2022-01-01 00:00:00" ["endDatePicker"]=> string(19) "2022-05-11 23:59:59" } 
Sebastian Morales commented on May 12

What an observation there, Keith! I can now replicate this bug when there are more than 10 values in an array parameter with MySQLDataSource.php. We will find a solution to fix it and let you know soon. Tks,

Keith Burke commented on May 12

Fantastic to know you can replicate. Always 90% of the work :)

Sebastian Morales commented on May 13

Hi Keith, pls open the file koolreport/core/src/datasources/MySQLDataSource.php and replace function prepareAndBind with the following new one to see if it solves the bug:

<?php
    ...
    protected function prepareAndBind($query, $params = [])
    {
        $sortedLenPaNames = array_keys($params);
        // Sort param names, longest name first,
        // so that longer ones are replaced before shorter ones in query
        // to avoid case when a shorter name is a substring of a longer name
        usort(
            $sortedLenPaNames,
            function ($k1, $k2) {
                return strlen($k2) - strlen($k1);
            }
        );

        // Spread array parameters
        foreach ($sortedLenPaNames as $paName) {
            $paValue = $params[$paName];
            if (gettype($paValue) === "array") {
                $numValues = strlen((string)count($paValue));
                $paramList = [];
                foreach ($paValue as $i => $value) {
                    $order = $i + 1;
                    // Pad order to keep all array param name length equal
                    $order = str_pad($order, $numValues, "0", STR_PAD_LEFT);
                    $paArrElName = $paName . "_arr_$order";
                    $paramList[] = $paArrElName;
                    $params[$paArrElName] = $value;
                }
                $query = str_replace($paName, implode(",", $paramList), $query);
            }
        }
        
        $sortedLenPaNames = array_keys($params);
        usort(
            $sortedLenPaNames,
            function ($k1, $k2) {
                return strlen($k2) - strlen($k1);
            }
        );
        $newParams = [];
        $positions = [];
        $originalQuery = $query;
        foreach ($sortedLenPaNames as $paName) {
            $count = 1;
            $pos = -1;
            while (true) {
                $pos = strpos($query, $paName, $pos + 1);
                if ($pos === false) {
                    break;
                } else {
                    $newPaName = $count > 1 ? $paName . "_" . $count : $paName;
                    $newParams[$newPaName] = $params[$paName];
                    $positions[$newPaName] = $pos;
                    $query = substr_replace($query, str_repeat("?", strlen($paName)), $pos, strlen($paName));
                }
                $count++;
            }
        }
        $sortedLenPaNames = array_keys($newParams);
        usort(
            $sortedLenPaNames,
            function ($k1, $k2) {
                return strlen($k2) - strlen($k1);
            }
        );
        $query = $originalQuery;
        foreach ($sortedLenPaNames as $paName) {
            $query = str_replace($paName, "?", $query);
        }

        // Sort new params by their positions, leftist one first
        $sortedPosNewParams = $newParams;
        uksort(
            $sortedPosNewParams,
            function ($k1, $k2) use ($positions) {
                return $positions[$k1] - $positions[$k2];
            }
        );

        $stmt = $this->connection->prepare($query);
        if ($stmt === false) {
            throw new \Exception(
                "Mysql error: " . $this->connection->error . " || Sql query = $query"
            );
        }

        $typeStr = "";
        foreach ($sortedPosNewParams as $v) {
            $typeStr .= is_double($v) ? "d" : (is_int($v) ? "i" : "s");
        }
        if (!empty($typeStr)) {
            $arr = [$typeStr] + $newParams;
            $refArr = [];
            foreach ($arr as $k => $v) {
                $refArr[] = &$arr[$k];
            }
            $bindParamResult = call_user_func_array(array($stmt, 'bind_param'), $refArr);
            // call_user_func_array(array($stmt, 'bind_param'), array_merge([$typeStr], $params));
            // call_user_func_array(array($stmt, 'bind_param'), array_merge([$typeStr], array_values($params)));
            // $stmt->bind_param($typeStr, ...array_values($params)); //spread operator ... only available since PHP 5.6
            // $stmt->bind_param($typeStr, ...$params); //spread operator ... only available since PHP 5.6
            if ($bindParamResult === false) {
                throw new \Exception(
                    'bind_param() failed: ' . htmlspecialchars($stmt->error)
                        . " || Sql query = $query"
                        . " || Params = $refArr"
                );
            }
        }
        return $stmt;
    } 

Rgds,

Keith Burke commented on May 13

Sebastian,

Confirmed. That resolved my issue. Well done for the quick turnaround.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
bug
help needed
solved

DataGrid