Here is my Setup file
<?php
require_once "../assets/koolreport_pro_6.0.6/core/autoload.php";
use \koolreport\KoolReport;
use \koolreport\querybuilder\DB;
use \koolreport\querybuilder\SQLServer;
use \koolreport\processes\CalculatedColumn;
use \koolreport\processes\ColumnMeta;
class store extends \koolreport\KoolReport {
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
protected function defaultParamValues()
{
return array();
}
protected function bindParamsToInputs()
{
return array("selcategory");
}
public function settings() {
require_once "../maintenance/config.php";
return $dataSources;
}
public function setup() {
$this->params['qry'] =
"SELECT Stores.itemId
,Stores.dId
,Stores.loc
,Stores.pn
,Stores.altPn
,Stores.descr
,Stores.qty
,Stores.minQty
,Stores.info
,Stores.cat
,Stores.updatedby
,Stores.updated
,Stores.created
,Stores.createdby
,Stores.isDeleted
FROM Stores
WHERE isDeleted = 0" ;
$this->src('sqlserver')->query(
DB::table('Stores_itemCategory')
->where('IsDeleted', '=', 0)
->orderBy('name')
->toSQLServer())
->pipe($this->dataStore('category'));
}
}
?>
View File
<?php
use \koolreport\datagrid\DataTables;
use \koolreport\widgets\koolphp\Table;
use \koolreport\processes\CalculatedColumn;
use \koolreport\inputs\Select;
use \koolreport\processes\Filter;
$excluded_columns = array();
if ($_SESSION['UserType'] != 'admin') {
$excluded_columns= [ array( "targets" => 10 , "visible" => false)];
}
print_object ($this->params['selcategory']);
?>
<div class="container-sm">
<h1 class="text-center m-3">Maintenance Stores</h1>
<div class="row m-2">
<div class="col-md-10">
<form id="selectionform" name="selectionform" action='' class="col-md-9 p-0" method="post">
<div class="row">
<div class="col-sm-3">
<label class="form-label">Resource</label>
<?php
Select::create(array(
"name"=>"selcategory",
'scope' => $this->params,
"dataStore"=>$this->dataStore("category"),
"defaultOption"=>array("All Category" => null ),
"dataBind"=>array(
"text"=>"name",
"value"=>"name",
),
"clientEvents"=>array(
"change"=>"function(){
// console.log( assignedDevice )
// frm = document.getElementById('selectionform') ;
// frm.action = 'index.php?action=snags&rid='+ selectResourceId.val()+'&statusid='+ selectStatus.val() ;
$('#selectionform').submit();
}",
),
"attributes"=>array(
"class" => "form-select",
)
));
?>
</div>
</div>
</form>
</div>
<div class="text-end pe-4">
<button type="button" class= "addstoreItembtn btn btn-primary bi bi-gear-wide-connected m-1" > Add New Item</button>
</div>
</div>
<?php
DataTables::create(array(
"name" => "storesTable",
'scope' => $this->params['selcategory'],
'dataSource' => function($scope) {
return $this->src('sqlserver')
->query($this->params['qry']." AND cat LIKE '".$scope ."'")
->pipe(new CalculatedColumn(array(
"update"=>array(
"exp"=>function($data){
return (date( 'd-M-Y H:i', $data['updated']));
}
)
)))
->pipe(new CalculatedColumn(array(
"editicon"=>array(
"exp"=>function($data){
return '<div itemid='. $data['itemId'] . ' class="edititem btn text-primary p-0" data-bs-toggle="tooltip" title="Edit Item">
<i class="bi bi-pencil-square"></i>
</div>';
}
)
)))
->pipe(new CalculatedColumn(array(
"deleteicon"=>array(
"exp"=>function($data){
return '<div itemid='. $data['itemId'] . ' class="deleteitem btn text-danger p-0" data-bs-toggle="tooltip" title="Deleted Item">
<i class="bi bi-trash"></i>
</div>';
}
)
)))
->pipe(new CalculatedColumn(array(
"minalert"=>array(
"exp"=>function($data){
if ($data['qty'] == 0) {
return '<div class="text-danger p-0 fs-4" data-bs-toggle="tooltip" title="No stock">
<i class="bi bi-info-circle-fill"></i>
</div>';
} else if ( $data['qty'] <= $data['minQty'] ) {
return '<div class="text-warning p-0 fs-4" data-bs-toggle="tooltip" title="Stock low">
<i class="bi bi-info-circle-fill"></i>
</div>';
}
return null;
}
)
)));
},
"themeBase" => "bs4",
"serverSide" => true,
"method"=>"post",
"responsive" => true,
"options"=>array(
"fastRender" => true,
"searching" => true,
"ordering" => true,
"paging" => true,
"mark" => array("element"=>"mark" ,"separateWordSearch"=> false),
"order" => array(3,"desc"),
"lengthMenu" => [[15, 30, 50, 100, -1], [15, 30, 50, 100, "All"]],
"columnDefs" => $excluded_columns,
),
"columns"=>array(
"minalert"=>array(
"label" => "",
"className" => "text-center",
"searchable" => false,
"orderable" => false,
),
"dId"=>array(
"label" => "Device",
),
"cat"=>array(
"label" => "Category",
),
"descr"=>array(
"label" => "Part Description",
),
"pn"=>array(
"label" => " Part Number",
),
"qty"=>array(
"label" => "Quantity",
"searchable" => false,
"orderable" => false,
),
"minQty"=>array(
"label" => "MinQuantity",
"searchable" => false,
"orderable" => false,
),
"loc"=>array(
"label" => "Location",
),
"info"=>array(
"label" => "Remarks",
),
"editicon"=>array(
"label" => "View/Edit",
"className" => "text-center",
"searchable" => false,
"orderable" => false,
),
"deleteicon"=>array(
"label" => "",
"className" => "text-center",
"searchable" => false,
"orderable" => false,
)
),
"cssClass"=>array(
"table" => "table table-bordered table-striped align-middle small",
"th" => "cssHeader"
)
));
?>
</div>
the Query return 0 row when I remove . " AND cat LIKE '".$scope ."'" it returns all row. I want to filter the table base on $this->params['selcategory'] selection
Can you Help me.