Please check my whole code.
query.php
<?php
namespace App\visualquerysample;
//Step 1: Load KoolReport
class vquery extends \koolreport\KoolReport
{
use \koolreport\visualquery\Bindable;
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)",
]
// "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" => "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" => ".",
];
}
use \koolreport\laravel\Friendship;
protected function setup()
{
$params = \koolreport\core\Utility::get($this->queryParams, 'visualquery1');
$qb = $this->paramsToQueryBuilder($params);
$this->queryStr = $params ? $qb->toMySQL() : "select * from customers where 1=0";
$this->paramQuery = $params ? $qb->toMySQL(['useSQLParams' => "name"]) : "select * from customers where 1=0";
$this->sqlParams = $qb->getSQLParams();
$this
->src('mysql')
->query($this->paramQuery)
->params($this->sqlParams)
->pipe(new \koolreport\processes\ColumnMeta([
"Order Number" => [
"type" => "string"
],
"orderMonth" => [
"type" => "string"
],
]))
->pipe($this->dataStore('vqDS'));
}
}
query.view.php
<?php
use \koolreport\visualQuery\VisualQuery;
use \koolreport\datagrid\DataTables;
?>
<form method="post" action="/querybuilder">
<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>
and from controller
`
public function index()
{
$queryBuilder = new vquery();
$queryBuilder->run();
return view("querybuilder.index", ["queryBuilder" => $queryBuilder]);
}
and route:
Route::get('/querybuilder', [QueryBuilderController::class, 'index'])
->name('querybuilder');
Route::post('/querybuilder', [QueryBuilderController::class, 'index'])
->name('querybuilder1');