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"
)
));
?>