KoolReport's Forum

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

Using one template for developing dynamic reporting for table, charts and crosstabs #188

Closed Daniel Amamoo-Otchere opened this topic on on Jan 10, 2018 - 13 comments

Daniel Amamoo-Otchere commented on Jan 10, 2018

Hello Kool Team, I have just purchased the reporting tool and trying to use a single template in creating dynamic reporting template however with that of the table Grid when the columns are defined it does not show anything. How does one fix it so that it takes the array content as a string from a query field.

The code used for the dynamic datastore which works is :

<?php

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

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\Group;
use \koolreport\processes\Limit;

class  Employinfo extends KoolReport
{
    public function settings()
    {
        //Get default connection from config.php
        $config = include "config.php";

        return array(
            "dataSources"=>array(
            "employ_info"=>$config["employ"]
            )
        );
    }   
	// modify the setup function to accept a parameter string
   public function setup($query_string)
    {
        $this->src('employ_info')
        ->query($query_string)
   
        ->pipe($this->dataStore('employee_status'));
    } 
}

Now the issue at hand is that when we pass another query string which defines the column definition array under the view it does not display the table and gives an error "Undifined index"

<?php 
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\widgets\google\ColumnChart;
?>

<div class="text-center">
    <h1><?php echo $_SESSION['script_header1'] ?></h1>
    <h4><?php echo $_SESSION['script_header2'] ?></h4>
</div>

<hr/>
<?php
$query_array=$_SESSION['query_array'];
Table::create(array(
    "dataStore"=>$this->dataStore('employee_status'),
    "showFooter"=>"bottom",
     "columns"=>array($query_array,    
    "cssClass"=>array(
        "table"=>"table table-hover table-bordered"
       
    )
));
KoolReport commented on Jan 10, 2018

If the $query_array is a list of column names in array form then you can do

    ...
    "columns"=>$query_array,
    ... 
Daniel Amamoo-Otchere commented on Jan 12, 2018

Hello I tried it and it did not work. As an example let us say $query_array=' "employee_type"=>array("label"="Employee Type","type"=>"string") , "no_employees"=>array("label"="No of Employee","type"=>"number","footer"=>"count","footerText"=>"count: @value","cssStyle"=>"text=align:right" ' Can I represent it as "columns"=>$query_array,

or I will need it represented as

$query_array=array("employee_type"=>array("label"="Employee Type","type"=>"string") , "no_employees"=>array("label"="No of Employee","type"=>"number","footer"=>"count","footerText"=>"count: @value","cssStyle"=>"text=align:right" )

Kindly help me with the format that must come from the field in the database.

Thanks Daniel

KoolReport commented on Jan 12, 2018

The second one is correct format. The input for "columns" must be in array.

Daniel Amamoo-Otchere commented on Jan 12, 2018

Hello, Testing it with the following script : $query_array=array( "employee_type"=>array( "label"=>"Employee Type","type"=>"string"), "no_employees"=>array( "label"=>"No of Employee", "type"=>"number", "footer"=>"count", "footerText"=>"count: @value", "cssStyle"=>"text=align:right"))

error message Warning: Invalid argument supplied for foreach() in \koolreport\widgets\koolphp\Table.php on line 96

KoolReport commented on Jan 13, 2018

Please make sure that you have data for the the columnName that input to "columns". For example, if you dataStore() does not have "customerNumber" but you put into "columns" the "customerNumber", it will cause issue since Table could not find that column data.

Daniel Amamoo-Otchere commented on Jan 15, 2018

Hello, It is not still working as indicated so I decided to provide the database script and the koolreport script used for your testing and final advice.

Database scrript

CREATE DATABASE employee_data ;

CREATE TABLE employee (
    employee_id serial primary key,
    employee_name character varying(100),
    employee_number character varying(100),
    employee_type character varying(50)
);
INSERT INTO employee VALUES ( 'Desmond', '001', 'Permanent');
INSERT INTO employee (employee_name,employee_number,employee_type)  VALUES ( 'Desmond', '001', 'Permanent'), ( 'Daniel', '002', 'Permanent'), ( 'Victoria', '003', 'Permanent'),( 'Freda', '004', 'Temporary');

CREATE TABLE script_data (
    script_data_id serial primary key,
    script_header1 character varying(255),
    script_header2 character varying(100),
    script_sql text,
    query_array text
);

INSERT INTO script_data (script_header1,script_header2,script_sql,query_array)   VALUES ('Employee List', 'List of Employed Persons', 'Select employee_name,employee_number,employee_type from employee', ' "employee_name"=>array("label"="Employee Name","type"=>"string"),"employee_number"=>array("label"="Employee No","type"=>"string"),
"employee_type"=>array("label"="Employee Type","type"=>"string") ');INSERT INTO script_data (script_header1,script_header2,script_sql,query_array)  
VALUES ('Employee By Type', 'Employee categorisation', 'Select employee_type,count(*) as no_employees from employee group by employee_type', ' array( "employee_type"=>array( "label"=>"Employee Type","type"=>"string"), "no_employees"=>array( "label"=>"No of Employee", "type"=>"number", "footer"=>"count", "footerText"=>"count: @value", "cssStyle"=>"text=align:right"))';

Within KookReport.php a class variable was added

public $query_string;

setup function was modified to

	public function setup($query_string)
	{
		//This function will be override by decendant to define
		//how data will be executed.
	}

Index.php

<?php 
$conn = pg_connect("host=localhost dbname=employee user=postgres password=''");
// Check connection
if (!$conn) {
    die("Connection failed: ");
} 
// sql script to perform query
$sql = 'SELECT * from script_data where script_data_id =1 ';
		
$query = pg_query($conn, $sql);

if (!$query) {
	die ('SQL Error:');
}
		while ($row = pg_fetch_array($query))
		{
			
				$script_header1=$row['script_header1'];
				$script_header2=$row['script_header2'];
				$script_sql=$row['script_sql'];
			    $query_array=$row['query_array'];

		}
?>

<?php
require_once "Employinfo.php";
$_SESSION['script_header1']=$script_header1;
$_SESSION['script_header2']=$script_header2;
$_SESSION['query_array']=$query_array;
$query_string = $script_sql;
 
$report = new Employinfo;
$report->setup($query_string);
$report->run();
?>

<!DOCTYPE >
<html>
    <head>
        <title>Employ Info</title>
        <link rel="stylesheet" href="assets/bootstrap/css/bootstrap.min.css" />
        <link rel="stylesheet" href="assets/bootstrap/css/bootstrap-theme.min.css" />
        <link rel="stylesheet" href="assets/css/example.css" />
    </head>
    <body>      
        <div class="container box-container">
            <?php $report->render();?>
        </div>
    </body>
</html>

Employinfo.php

<?php

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

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\Group;
use \koolreport\processes\Limit;

class  Employinfo extends KoolReport
{
    public function settings()
    {
        //Get default connection from config.php
        $config = include "config.php";

        return array(
            "dataSources"=>array(
            "employ_info"=>$config["employ"]
            )
        );
    }   
	// modify the setup function to accept a parameter string
   public function setup($query_string)
    {
        $this->src('employ_info')
        ->query($query_string)
   
        ->pipe($this->dataStore('employee_status'));
    } 
}

Employinfo.view.php

<?php 
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\widgets\google\ColumnChart;
?>

<div class="text-center">
    <h1><?php echo $_SESSION['script_header1'] ?></h1>
    <h4><?php echo $_SESSION['script_header2'] ?></h4>
</div>

<hr/>
<?php
$query_array=$_SESSION['query_array'];
Table::create(array(
    "dataStore"=>$this->dataStore('employee_status'),
    "showFooter"=>"bottom",
     "columns"=>$query_array, 
        "cssClass"=>array(
        "table"=>"table table-hover table-bordered"
       
    )
));
?>
KoolReport commented on Jan 15, 2018

Now I understand, the $query_array need to be in array type, not just a string like now, you have to do this:

$query_array=$_SESSION['query_array'];
eval('$query_array='.$query_array.';');
...

Although the eval() function is not recommended by security but I only see the only option here.

Another solution is to save json string in database and when you receive the string, you use json_decode() to convert to array.

Daniel Amamoo-Otchere commented on Jan 15, 2018

Ok. using json will be great and I can format the query to return it but How do I then represent the string shown below which works when assigned to the "columns" key directly and not through a paramter as a json format for the headers to show (They are different from that returned from the database) and also have a computed column value as a footer.

"columns"=>array(
"employee_name"=>array(
"label"=>"EMPLOYEE NAME","type"=>"string"),
"employee_number"=>array(
"label"=>"EMPLOYEE NUMBER","type"=>"number",
"footer"=>"count",
"footerText"=>"count: @value",
"cssStyle"=>"text-align:right"),
"employee_type"=>array(
"label"=>"EMPLOYEE TYPE","type"=>"string"),
 ),

That is the main challenge now.

KoolReport commented on Jan 16, 2018

You do this:

echo json_encode(array(
    "employee_name"=>array(
        "label"=>"EMPLOYEE NAME","type"=>"string"
    ),
    "employee_number"=>array(
        "label"=>"EMPLOYEE NUMBER","type"=>"number",
        "footer"=>"count",
        "footerText"=>"count: @value",
        "cssStyle"=>"text-align:right"
    ),
    "employee_type"=>array(
        "label"=>"EMPLOYEE TYPE","type"=>"string")
    )
);

You will get the json format of the array there

Dessy commented on Jan 16, 2018

Hi I have been following the discussion and equally trying to achieve the same output.After getting the json format of the array and assigning it to a variable below.

$jsonn = '{"employee_name":{"label":"EMPLOYEE NAME","type":"string"},"employee_number":{"label":"EMPLOYEE NUMBER","type":"number","footer":"count","footerText":"count: @value","cssStyle":"text-align:right"},"employee_type":{"label":"EMPLOYEE TYPE","type":"string"}}'; $query_array=json_decode($jsonn);

Table::create(array(

"dataStore"=>$this->dataStore('employee_status'),
"showFooter"=>"bottom",
 "columns"=> $query_array,
 
"cssClass"=>array(
    "table"=>"table table-hover table-bordered"
   
)

));

An error of this nature was reported Warning: Illegal offset type in \koolreport\widgets\koolphp\Table.tpl.php on line 28 Warning: Illegal offset type in \koolreport\widgets\koolphp\Table.tpl.php on line 29 Recoverable fatal error: Object of class stdClass could not be converted to string in koolreport\widgets\koolphp\Table.tpl.php on line 36 Warning: Illegal offset type in koolreport\widgets\koolphp\Table.php on line 277 Warning: Illegal offset type in koolreport\widgets\koolphp\Table.php on line 269 Warning: Illegal offset type in koolreport\widgets\koolphp\Table.php on line 316

KoolReport commented on Jan 16, 2018

Hi, you do json_encode($jsonn,true)

Dessy commented on Jan 16, 2018

ok,after returning the json string from the database and applying json_decode($jsonn,true), bingo!!!! it's all working. thumbs up for the support.

Dessy commented on Jan 23, 2018

After implementing the same logic on a different database it seem not to be working this is the line of code. <?php $conn = pg_connect("host=localhost dbname="database name here" user=postgres password=""); // Check connection if (!$conn) {

die("Connection failed: ");

} // sql script to perform query $sql = 'select * from controller.get_tabular_report(1)';

$query = pg_query($conn, $sql);

if (!$query) {

die ('SQL Error:');

}

	while ($row = pg_fetch_array($query))
	{
		
			$script_header1=$row['report_name'];
			$script_header2=$row['chart_title'];
			$script_sql=$row['the_query'];
		        $query_array=$row['format_str'];

	}

?> <?php require_once "Employinfo.php"; $_SESSION['script_header1']=$script_header1; $_SESSION['script_header2']=$script_header2; $_SESSION['query_array']=$query_array; $query_string =$script_sql;

$report = new Employinfo(); $report->setup($query_string); $report->run(); ?> The idea here is to pass the $script_sql to $query_string above but failed on execution rather when the select statement is passed directly to the $query_string such as this

$query_string = "select a.date_payment_raised as date_payment_raised, a.payment_amount as payment_amount, b10.list_source_value as bill_received_id, b3.acada_year as academic_year_id from public.nak_applicant_payment a left join academic_year b3 on b3.academic_year_id = a.academic_year_id left join controller.mydatasourcelist b10 on b10.mydatasourcelist_id=a.bill_received_id group by b10.list_source_value,a.date_payment_raised,a.payment_amount,b3.acada_year"; it works. kindly advice on it.

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
help needed

DataGrid