Page Comparison

...

Code Block

language	javascript
title	ui.js

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:

Code Block

language	javascript
title	ui.js

isc.ListGrid.create({
    "ID": "supplyItemGridsupplyItem",
    "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 () {
        supplyItemGridsupplyItem.filterData(advancedFilter.getCriteria());
    }
});
isc.HStack.create({
    "membersMargin": 10,
    "ID": "gridButtons",
    "members": [
        isc.IButton.create({
            "top": 250,
            "title": "Edit New",
            "click": "supplyItemGridsupplyItem.startEditingNew()"
        }),
        isc.IButton.create({
            "top": 250,
            "left": 100,
            "title": "Save all",
            "click": "supplyItemGridsupplyItem.saveAllEdits()"
        })
    ]
});
isc.VStack.create({
    "membersMargin": 10,
    "members": [advancedFilter, filterButton, supplyItemGridsupplyItem, gridButtons]
});

Also note, the filter has been removed top of the grid, as it is being replaced with the FilterBuilder.

...

Code Block

language	php
title	RPCManager.php

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 and move out the existing criteria building code into a separate method called buildStandardCriteria():

...

method：

Code Block

language	php
title	DataSource.php

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 s ";
	
	$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;
 
}
 
private function buildStandardCriteria($request)
{
	$query = '';  
	$query_count = '';
	
	if ( count($request->getDataKeys()) != 0 )
	{
	   $query .= ' where ';               
		 
		foreach($request->data as $key => $value)
		{ 
}

Note a new method called buildAdvancedCriteria() is required which builds the query criteria from the AdvancedCriteria delivered by the FilterBuilder:

Code Block

language	php
title	RPCManager.php

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:

Code Block

language	php

private function buildCriterion($advancedCriteria)
{      			// get the field         
			$field$criterias = $this->getField($key)$advancedCriteria['criteria'];
			if(!empty($field))  $operator = $advancedCriteria['operator'];             
	$result = '';
	$query = '';
	$value = array();
	
	foreach($criterias as $c)
	{
		if(isset($c['fieldName']))            			{    
				$type$fn = $field$c['typefieldName'];
		
		if(isset($c['operator']))
				if($op $type == "text" ||
 $c['operator'];
						$type == "link"
||
		if(isset($c['value']))
			$type{ == "enum" || 					$type == "image" ||
					$typeif ($c['value'] === "ntext"TRUE )
				{
$val = '1';
			else if($c['value'] === FALSE)
			$query	$val .= "s." . $key . " like " . " '%" . $value . "%' and " ;'0';
			else
				$val = $c['value'];                    
				} else 				{ 					"s." . $key . "=" . " '" . $value . "' and " ;  
		
		if(isset($c['start']))    
				}
			}$start = $c['start'];
								
			$query_count .= "s." . $key . " like " . "'%" . $value . "%' and " ;  if(isset($c['end']))    
			$end = $c['end'];
		
		if(isset($c['criteria']))    
     
			}
$criteria = $c['criteria'];
		else
		// remove 'and' of the query		$criteria = null;
		
		if(empty($criteria))
		{
			switch($op)
			{
				case 'equals':                            
					$query = substr($query, 0, strrpos($query, 'and')"$fn = ?"; 
					array_push($value, $val);            	}                                           
					break;
	return $query;
}			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.

Versions Compared

Old Version 1

New Version Current

Key