Hello team, I am trying to access kool report with my existing code igniter application. I have done Barchar,ColumnChart and Table with same configration and it is runing fine. On visual query I am having errors. tried to troubleshoot and sems like on visualQuery.php/ onInit function where this line is having error because in $report queryParams is not define.
$queryParams = Util::get($report->queryParams, $this->name);
I am having following error, kindly guide me what I am doing wrong.
Error :
Undefined property: koolreport\core\WidgetContainer::$queryParams Filename: visualquery/VisualQuery.php Line Number: 85
and
Type: Error
Message: Call to undefined method koolreport\core\WidgetContainer::paramsToValue()
Filename: C:\wamp64\www\my-folder\assets\koolreport\visualquery\VisualQuery.php
Line Number: 86
my report.php file
/**
* Class Reports
*/
class Reports extends Admin_Controller
{
public function graphical_report() {
$data['report'] = new MyReport;
$this->layout->set($data);
$this->layout->buffer('content', 'reports/graphical_report_view');
$this->layout->render();
}
}
require_once APPPATH.'..\assets\koolreport\core\autoload.php';
use \koolreport\core\Utility as Util;
class MyReport extends \koolreport\KoolReport
{
use \koolreport\bootstrap4\Theme;
// use \koolreport\clients\Bootstrap;
use \koolreport\visualquery\Bindable;
public function defineSchemas()
{
return [
"salesSchema" => array(
"tables" => [
"customers" => array(
"{meta}" => [
"alias" => "Table Customers"
],
"customerNumber" => array(
"alias" => "Customer Number",
),
"customerName" => array(
"alias" => "Customer Name",
),
),
"orders" => array(
"{meta}" => [
"alias" => "Table Orders"
],
"orderNumber" => array(
"alias" => "Order Number"
),
"orderDay" => array(
"alias" => "Order Day",
"expression" => "date(orderDate)",
"type" => "date",
),
"orderDate" => array(
"alias" => "Order Date",
"type" => "datetime",
),
"orderMonth" => [
"expression" => "month(orderDate)",
"type" => "number",
]
// "customerNumber"=>array(
// "alias"=>"Customer Number"
// )
),
"orderdetails" => array(
"{meta}" => [
"alias" => "Order Details"
],
// "orderNumber"=>array(
// "alias"=>"Order Number"
// ),
"quantityOrdered" => array(
"alias" => "Quantity",
"type" => "number",
),
"priceEach" => array(
"alias" => "Price Each",
"type" => "number",
"decimal" => 2,
"prefix" => "$",
),
// "productCode"=>array(
// "alias"=>"Product Code"
// ),
"cost" => [
// "expression" => "orderdetails.quantityOrdered * orderdetails.priceEach",
"expression" => "quantityOrdered * priceEach",
"alias" => "Order Cost",
"type" => "number",
"decimal" => 2,
"prefix" => "$",
]
),
"products" => array(
"{meta}" => [
"alias" => "Table Products"
],
"productCode" => array(
"alias" => "Product Code"
),
"productName" => array(
// "alias" => "Product Name"
),
)
],
"relations" => [
["orders.customerNumber", "leftjoin", "customers.customerNumber"],
["orders.orderNumber", "join", "orderdetails.orderNumber"],
["orderdetails.productCode", "leftjoin", "products.productCode"],
],
),
"separator" => "."
];
}
function settings()
{
return array(
"dataSources" => array(
"automaker" => array(
"connectionString" => "mysql:host=localhost;dbname=automaker",
"username" => "root",
"password" => "",
"charset" => "utf8"
),
'mysql' => [
'host' => 'localhost',
'username' => 'root',
'password' => '',
'dbname' => 'automaker',
'class' => '\koolreport\datasources\MySQLDataSource',
],
)
);
}
protected function setup()
{
$params = Util::get($this->queryParams, 'visualquery1');
// echo "params = "; Util::prettyPrint($params);
$qb = $this->paramsToQueryBuilder($params);
// $arr = $qb->toArray();
// echo "qb array="; Util::prettyPrint($arr);
$this->query = $params ?
$qb->toMySQL() : "select * from customers where 1=0";
$this->paramQuery = $params ?
// $qb->toMySQL(['useSQLParams' => "question"]) : "select * from customers where 1=0";
$qb->toMySQL(['useSQLParams' => "name"]) : "select * from customers where 1=0";
$this->sqlParams = $qb->getSQLParams();
// echo "paramQuery="; echo $this->paramQuery; echo "<br>";
// Util::prettyPrint($this->sqlParams);
$this
->src('automaker')
// ->query($this->query)
->query($this->paramQuery)
->params($this->sqlParams)
->pipe(new \koolreport\processes\ColumnMeta([
"Order Number" => [
"type" => "string"
],
"orderMonth" => [
"type" => "string"
],
]))
->pipe($this->dataStore('vqDS'));
}
}
my view file.
<?php
// use \koolreport\widgets\koolphp\Table;
// use \koolreport\widgets\google\ColumnChart;
// use \koolreport\widgets\google\BarChart;
use \koolreport\visualQuery\VisualQuery;
use \koolreport\datagrid\DataTables;
?>
<html>
<head>
<title>KoolReport visual query</title>
</head>
<body>
<form method="post">
<div class="report-content">
<div class="text-center">
<h1>VisualQuery</h1>
<p class="lead">
The basic working of VisualQuery
</p>
</div>
<?php
\koolreport\visualquery\VisualQuery::create(array(
"name" => "visualquery1",
"themeBase" => "bs4",
"schema" => "salesSchema",
"defaultValue" => [
"selectDistinct" => false,
"selectTables" => [
"orders",
"orderdetails",
"products",
],
"selectFields" => [
"products.productName",
],
"filters" => [
"(",
[
"field" => "orders.orderDay",
"operator" => ">",
"value1" => "2001-01-01",
"value2" => "",
"logic" => "and",
"toggle" => true,
],
[
"field" => "products.productCode",
"operator" => "nbtw",
"value1" => "2",
"value2" => "998",
"logic" => "or",
"toggle" => true,
],
["products.productName", "<>", "a", "", "or", "toggle" => false],
["products.productName", "nin", "a,b,c", "", "or"],
["products.productName", "ctn", "a", "", "or"],
")",
],
"groups" => [
[
"field" => "orderdetails.cost",
"aggregate" => "sum",
"toggle" => true
]
],
"havings" => [
"(",
[
"field" => "sum(orderdetails.cost)",
"operator" => ">",
"value1" => "10000",
"value2" => "",
"logic" => "and",
"toggle" => true,
],
["products.productName", "<>", "a", "", "or", "toggle" => false],
")",
],
"sorts" => [
[
"field" => "sum(orderdetails.cost)",
"direction" => "desc",
"toggle" => true
],
["products.productName", "desc", "toggle" => false]
],
"limit" => [
"offset" => 5,
"limit" => 10,
"toggle" => false,
]
],
"activeTab" => "filters",
));
?>
<button type='submit' class='btn btn-light' >Submit</button>
<style>
pre {
overflow-x: auto;
white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word;
}
</style>
<div style="margin: 30px; width:800px">
<b>Select query:</b>
<pre style="width:800px"><?php echo $this->queryStr; ?></pre>
<b>Query with parameters:</b>
<pre style="width:800px"><?php echo $this->paramQuery; ?></pre>
<b>Parameters:</b>
<?php \koolreport\core\Utility::prettyPrint($this->sqlParams); ?>
</div>
<?php
// print_r($this->dataStore('vqDS')->meta());
DataTables::create(array(
"name" => "charttable1",
"dataSource" => $this->dataStore('vqDS'),
// "columns" => ["Quantity", "Product Name"],
"options" => [
"paging" => true
]
));
?>
</div>
</form>
</body>
</html>