5. Adding support for AdvancedCriteria with PHP
- Kris Jin (Unlicensed)
Description
In this example the last sample will be modified to make use of the FilterBuilder and the underlying AdvancedCriteria system, to build functionality resembling this showcase sample (but using our supplyItem DataSource, instead of the one in the webpage)
Prerequisites
As this sample extends the previous one, in order to be able to run it, please ensure you have the latest build of SmartClient (at least version 8.3p). This can be downloaded from here.
Adding FilterBuilder to front-end
Modify Scripts/ui.js to include the relevant code for creating the FilterBuilder:
isc.FilterBuilder.create({
"ID": "advancedFilter",
"dataSource": "supplyItem",
"topOperator": "and"
});
The ListGrid also requires additional code to add the FilterBuilder. This will require adding a vertical layout (VStack), together with the grid and the button needed to add for applying the filter on the ListGrid. Also going to add a horizontal layout (HStack) which will contain the two already existing buttons used for saving all data and creating a new record:
isc.ListGrid.create({
"ID": "supplyItem",
"width": 700, "height": 224, "alternateRecordStyles": true,
"dataSource": supplyItem,
"autoFetchData":true,
"dataPageSize":20,
"canEdit":true,
"canRemoveRecords":true,
"autoSaveEdits": false
});
isc.IButton.create({
"ID": "filterButton",
"title": "Filter",
"click": function () {
supplyItem.filterData(advancedFilter.getCriteria());
}
});
isc.HStack.create({
"membersMargin": 10,
"ID": "gridButtons",
"members": [
isc.IButton.create({
"top": 250,
"title": "Edit New",
"click": "supplyItem.startEditingNew()"
}),
isc.IButton.create({
"top": 250,
"left": 100,
"title": "Save all",
"click": "supplyItem.saveAllEdits()"
})
]
});
isc.VStack.create({
"membersMargin": 10,
"members": [advancedFilter, filterButton, supplyItem, gridButtons]
});
Also note, the filter has been removed top of the grid, as it is being replaced with the FilterBuilder.
Parsing the AdvancedCriteria
The AdvancedCriteria built by the FilterBuilder is sent in the JSON payload when doing a fetch() request. It is formatted like this:
// an AdvancedCriteria
{
"_constructor":"AdvancedCriteria",
"operator":"and",
"criteria":[
// this is a Criterion
{ "fieldName":"salary", "operator":"lessThan", "value":"80000" },
{ "fieldName":"salary", "operator":"lessThan", "value":"80000" },
... possibly more criterions ..
{ "operator":"or", "criteria":[
{ "fieldName":"title", "operator":"iContains", "value":"Manager" },
{ "fieldName":"reports", "operator":"notNull" }
{ "operator":"and", "criteria": [
.. some more criteria or criterion here
]}
]}
},
{ "operator":"or", "criteria":[
{ "fieldName":"title", "operator":"iContains", "value":"Manager" },
{ "fieldName":"reports", "operator":"notNull" }
]}
.. possibly more criterions or criterias
]
}
As you can see it is a tree structure, with it's leafs being criterion and the nodes being criteria. If the the criteria member is null, then it is a leaf, otherwise it is a node which has sub-criterias or sub-criterions.
Changes to DSRequest
Store an AdvancedCriteria condition data in the DSRequest object by adding a new property to it:
var $advancedCriteria;
public function getAdvancedCriteria()
{
return $this->advancedCriteria;
}
// set the advanced criteria
public function setAdvancedCriteria($advancedCriteria)
{
$this->advancedCriteria = $advancedCriteria;
}
The rest of the DSRequest's code remains unchanged. This new property will be initialized from the RPCManager which will load advanced criteria if the request has one during the parsing of the DSRequest objects.
Firstly, a helper method is needed in the RPCManager to decide if the DSRequest has AdvancedCriteria or not:
private function checkAdvancedCriteria($data)
{
$blCheck = array_key_exists("_constructor", $data) && array_key_exists("operator", $data);
return $blCheck;
}
Define a method which iterates over the list of requests in the transaction and for each load the advanced criteria if the request has one:
protected function parseAdvancedCriterias($operations)
{
$data = $operations['data'];
if($this->checkAdvancedCriteria($data))
{
return $data;
}
return null;
}
This method receives the operation list from request. Now modify the processTransaction in RPCManager to parse the advanced criteria:
private function processTransaction()
{
// retrieve the requests with data in form of supplyItem
// store transaction num, we'll use it later to see if there was a transaction or not
$transactionNum = $transaction_request['transactionNum'];
$idx = 0;
$queueFailed = false;
$res_list = array();
$operations = $transaction_request['operations'];
// create transaction
R::begin();
try{
foreach($operations as $op)
{
$res = $op;
// parse advanced criterias, if any
$advancedCriteria = $this->parseAdvancedCriterias($op);
... ... ... ... ...
}
// if there were no errors, commit the transaction
if (!$queueFailed)
{
R::commit();
}
}
catch(Exception $e) {
R::rollback();
}
... ... ... ... ...
}
Refactor the existing fetch() method：
private function fetch($request)
{
// get the DataSource
$ds = $this->data_source;
$tbl_name = $ds['ID'];
// check the advanced cretira
if(empty($request->advancedCriteria))
{
$query_result = $this->buildStandardCriteria($request);
} else
{
$query_result = $this->buildAdvancedCriteria($request);
}
$query = "select * from $tbl_name ";
$query .= $query_result['query'];
// sort by
if( !empty($request->sortBy) )
{
// set the orderBy
$query .= " order by ";
// we start to build a coma separated list of items. First item won't have coma
// but every possible next will do
$seperator = "";
foreach($request->sortBy as $index => $sort)
{
// if column name is with -, then ordering is descending, otherwise ascending
if ( strpos($index, '-') === 0 )
{
$query .= $seperator . $sort . " ASC";
} else
{
$query .= $seperator . substr($sort, 1) . " DESC";
}
$separator = ',';
}
}
//DSResponse
$response = new DSResponse();
$products = R::getAll($query, $query_result['value']);
// get the count
$count = count($products);
$response->setData($products);
$response->setStartRow($request->startRow);
$response->setEndRow($request->endRow);
$response->setTotalRows($count);
$response->setStatus(0);
// sanity check, if no rows, return 0
if ($response->getEndRow() < 0 )
{
$response->setEndRow(0);
}
return $response;
}
Note a new method called buildAdvancedCriteria() is required which builds the query criteria from the AdvancedCriteria delivered by the FilterBuilder:
private function buildAdvancedCriteria($request)
{
$advancedCriteria = $request->getAdvancedCriteria();
if(!empty($advancedCriteria))
{
$criteria_query = $this->buildCriterion($advancedCriteria);
$return = array();
$return['query'] = ' where '.$criteria_query['query'];
$return['value'] = $criteria_query['value'];
return $return;
}
return null;
}
For parsing the tree itself, introduce a recursive function:
private function buildCriterion($advancedCriteria)
{
$criterias = $advancedCriteria['criteria'];
$operator = $advancedCriteria['operator'];
$result = '';
$query = '';
$value = array();
foreach($criterias as $c)
{
if(isset($c['fieldName']))
$fn = $c['fieldName'];
if(isset($c['operator']))
$op = $c['operator'];
if(isset($c['value']))
{
if ($c['value'] === TRUE )
$val = '1';
else if($c['value'] === FALSE)
$val = '0';
else
$val = $c['value'];
}
if(isset($c['start']))
$start = $c['start'];
if(isset($c['end']))
$end = $c['end'];
if(isset($c['criteria']))
$criteria = $c['criteria'];
else
$criteria = null;
if(empty($criteria))
{
switch($op)
{
case 'equals':
$query = "$fn = ?";
array_push($value, $val);
break;
case 'notEqual':
$query = "$fn != ?";
array_push($value, $val);
break;
case 'iEquals':
$query = "UPPER($fn) = ?";
array_push($value, "UPPER('{$val}')");
break;
case 'iNotEqual':
$query = "UPPER($fn) != ?";
array_push($value, "UPPER('{$val}')");
break;
case 'greaterThan':
$query = "$fn > ?";
array_push($value, $val);
break;
case 'lessThan':
$query = "$fn < ?";
array_push($value, $val);
break;
case 'greaterOrEqual':
$query = "$fn >= ?";
array_push($value, $val);
break;
case 'lessOrEqual':
$query = "$fn <= ?";
array_push($value, $val);
break;
case 'contains':
$query = "$fn LIKE ?";
array_push($value, "%{$val}%");
break;
case 'startsWith':
$query = "$fn LIKE ?";
array_push($value, "{$val}%");
break;
case 'endsWith':
$query = "$fn LIKE ?";
array_push($value, "%{$val}");
break;
case 'iContains':
$query = "$fn LIKE ?";
array_push($value, "%{$val}%");
break;
case 'iStartsWith':
$query = "UPPER($fn) LIKE ?";
array_push($value, "UPPER('{$val}%')");
break;
case 'iEndsWith':
$query = "UPPER($fn) LIKE ?";
array_push($value, "UPPER('%{$val}')");
break;
case 'notContains':
$query = "$fn NOT LIKE ?";
array_push($value, "%{$val}%");
break;
case 'notStartsWith':
$query = "$fn NOT LIKE ?";
array_push($value, "{$val}%");
break;
case 'notEndsWith':
$query = "$fn NOT LIKE ?";
array_push($value, "%{$val}");
break;
case 'iNotContains':
$query = "UPPER($fn) NOT LIKE ?";
array_push($value, "UPPER('%{$val}%')");
break;
case 'iNotStartsWith':
$query = "UPPER($fn) NOT LIKE ?";
array_push($value, "UPPER('{$val}%')");
break;
case 'iNotEndsWith':
$query = "UPPER($fn) NOT LIKE ?";
array_push($value, "UPPER('%{$val}')");
break;
case 'isNull':
$query = "$fn IS NULL";
break;
case 'notNull':
$query = "$fn IS NOT NULL";
break;
case 'equalsField':
$query = "$fn LIKE ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'iEqualsField':
$query = "UPPER($fn) LIKE ?";
array_push($value, "UPPER(CONCAT('{$val}', '%'))");
break;
case 'iNotEqualField':
$query = "UPPER($fn) NOT LIKE ?";
array_push($value, "UPPER(CONCAT('{$val}', '%'))");
break;
case 'notEqualField':
$query = "$fn NOT LIKE ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'greaterThanField':
$query = "$fn > ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'lessThanField':
$query = "$fn < ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'greaterOrEqualField':
$query = "$fn >= ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'lessOrEqualField':
$query = "$fn <= ?";
array_push($value, "CONCAT('{$val}', '%')");
break;
case 'iBetweenInclusive':
$query = "$fn BETWEEM ? AND ?";
array_push($value, $start);
array_push($value, $end);
break;
case 'betweenInclusive':
$query = "$fn BETWEEM ? AND ?";
array_push($value, $start);
array_push($value, $end);
break;
}
$result .= " ".$query." ".$operator." ";
}else
{
// build the list of subcriterias or criterions
$temp = $result;
$result1 = $this->buildCriterion($c);
$result = $temp . "(".$result1['query'].") ".$operator." ";
foreach($result1['value'] as $val)
{
array_push($value, $val);
}
}
}
$result_query = substr($result, 0, strrpos($result, $operator));
$advanced_critical['query'] = $result_query;
$advanced_critical['value'] = $value;
return $advanced_critical;
}
If criterion exist (criteria property in this case will be null), simply build the query criteria and return it (this is the condition to exit from recursivity). If the criteria property is not null (the else branch), thencall the function again for each child criterion or criteria, and then assemble back everything using the specified operator before returning it to the caller.
At this point you should be able to run the sample and use the FilterBuilder and see it affect the grid entries.
The complete code for this sample project can be downloaded from here.