What is a Pre Query?

Pre Query Script lets you specify any custom search clause that would get appended to the default search criteria generated by the platform. You can manipulate the whereClause that gets used during the search. Pre Query script can be defined for Datasource

When do we use it?

When your application requires implementing custom search, you can write Pre Query Script on the Datasource. The platform invokes the Pre-query script before performing a search allowing a developer to take more control on the query being executed to get the right results.

Examples

Example 1

The whereClause of a Search can be manipulated as below. The platform appends this to the generated whereClause

// check if whereClause already exists
if (whereClause.length() > 0) {
	// whereClause already exists. append AND or OR clause as required
	whereClause.append(" AND ");
}
// use bind variables. e.g. ? or :EVENT_STATUS
whereClause.append("x.EVENT_STATUS = ?");
// append the bind variable
params.add(101);
Example 2

Advanced manipulation of whereClause

String custNo = data.get("AccountNumber");
// check if the custNo is entered by the user in the search region
if (custNo != null) {
	if (whereClause.length() > 0) {
		whereClause.append(" AND ");
	}
	if (custNo.indexOf("%") != -1) {
		// wildchar found, use LIKE instea
		whereClause.append("X.ACCOUNT_NUMBER LIKE ?");
	} else {
		whereClause.append("X.ACCOUNT_NUMBER = ?");
	}
	params.add(custNo);
}
Example 3

The order by clause of a Search can be manipulated as below. The platform appends this to the generated order By clause

if (orderByClause.length() > 0) {
  orderByClause.append(", ");
}
orderByClause.append("x.NET_LOSS_AMOUNT");

Advanced Usages

Calling a PL/SQL procedure before executing the query
// Get the required parameter values from the current search criteria
// provided by the user i.e. user entered values in the search panel
String CustomerNameDummy = data.get("PartyName");
String CustomerNumberDummy = data.get("AccountNumber");
String ContactNameDummy = data.get("ContactName");
String EmailDummy = data.get("EmailAddress");
String PostalCodeDummy = data.get("PostalCode");

// Prepare the model to be used invoke the Pre-Query PL/SQL Procedure
RPCModelData model = new RPCModelData();
model.set("CustomerName", CustomerNameDummy);
model.set("CustomerNumber", CustomerNumberDummy);
model.set("ContactName", ContactNameDummy);
model.set("ContactEmail", EmailDummy);
model.set("PostalCode", PostalCodeDummy);
model.set("SearchCriteria", searchvalue);
// Invoke the Pre-Query PL/SQL Procedure. This will call
// PACKAGE.INSERT_ROW Procedure
db.postObjectToDB("CustomerPreQueryAPI", model);
// Get the return value
Double searchvalue = model.get("SearchCriteria");
// Use the return value in the where clause
if (searchvalue != null) {
	if (whereClause.length() > 0) {
		whereClause.append(" AND ");
	}
	whereClause.append("x.search_context_id = ? ");
	params.add(searchvalue);
}

Replacing FROM CLAUSE of a query

if (someCondition) {
  fromClause.setLength(0); // remove the existing FROM CLAUSE
  fromClause.append(" FROM NEW_TABLE_OR_VIEW_NAME x"); // alias should always be x
}

Manipulating the bind variable

// make sure to uncheck the "Query Allowed" property for this attribute
// so that the system won't include this attr in the system generated
// where clause
String phoneNo = data.get("Phone Number");
if (phoneNo != null) {
	if (whereClause.length() > 0) {
		whereClause.append(" AND ");
	}
	whereClause.append("x.transposed_phone_number LIKE ?");
	params.add(new StringBuilder(phoneNo.replaceAll("[^0-9]", "")).reverse().append("%").toString());
}

Replacing the query with a custom query with UNIONs

Double custaccountid = data.get("customerid");
if (custaccountid != null) {
	// backup the existing bind variables if any as we need the repeat
	// the where clause of every UNION
	List paramsCopy = new ArrayList();
	paramsCopy.addAll(params);
	StringBuffer newWhereClause = new StringBuffer();
	if (whereClause.length() > 0) {
		newWhereClause.append(" AND ");
	}
	// backup the select clause as we need to repeat for every UNION
	String sel = selectClause.toString();
	newWhereClause.append("x.bill_customer_id = ?");
	/**
	 ** RA_TOTAL_ROWS will help the platform to know the total rows
	 * thereby avoiding the use of an extra query to calculate the total
	 * rows i.e. select count(1)
	 **/
	fullSQL.append("SELECT X.*, count(1) over() RA_TOTAL_ROWS FROM (");
	fullSQL.append(sel).append(fromClause).append(" WHERE ").append(whereClause).append(newWhereClause);
	params.add(custaccountid);
	fullSQL.append(" UNION ");
	newWhereClause.setLength(0);
	if (whereClause.length() > 0) {
		newWhereClause.append(" AND ");
	}
	newWhereClause.append("x.ship_customer_id = ?");
	fullSQL.append(sel).append(fromClause).append(" WHERE ").append(whereClause).append(newWhereClause);
	params.addAll(paramsCopy);
	params.add(custaccountid);
	fullSQL.append(" UNION ");
	newWhereClause.setLength(0);
	if (whereClause.length() > 0) {
		newWhereClause.append(" AND ");
	}
	newWhereClause.append("x.customer_id = ?");
	fullSQL.append(sel).append(fromClause).append(" WHERE ").append(whereClause).append(newWhereClause);
	params.addAll(paramsCopy);
	params.add(custaccountid);
	fullSQL.append(") X");
	if (orderByClause.length() > 0) {
		fullSQL.append(" ORDER BY ").append(orderByClause.toString().toLowerCase().replaceAll("_", ""));
	}
}

Restricting Blind Query

In order to restrict the user from performing a blind query on a given data source, add the follow code at the end of the pre-query script.

if (whereClause.length() == 0) {
  throw new RAException("Blind Query", "You must specify atleast one search criteria");
}

Query Allowed

By default, all attributes are query allowed and hence the platform will automatically generate whereClause when a user perform a search on an attribute. At times, the developer may want to programmatically control the whereClause for an attribute. In such cases uncheck the Query Allowed property at the data source attribute level for platform to ignore those attributes while generating the whereClause

Custom Handling of Grid Filters

You can access the data related to the grid filters through FilterConfig object. Use data.getFilterConfigs() method to retrieve the list of FilterConfig 

import com.extjs.gxt.ui.client.data.FilterConfig;

List<filterconfig> filterConfigs = data.getFilterConfigs();
if (filterConfigs != null) {
    for (FilterConfig filter : filterConfigs) {
      if ("item".equals(filter.getField()) && filter.getValue() != null) {
        whereClause.append(" AND x.ITEM LIKE ?");
        params.add(((String) filter.getValue()).toUpperCase() + "%");
      }
    }
}

Range Query

By default platform would generated range search fields in simple search panel for all date & number attributes. For custom search panel, developer can define calculated from & to attributes with Select & Query Allowed unchecked to hold the range search values and use pre-query script similar to the following example to perform the range query

Date from = data.get("Date From");
Date to = data.get("Date To");

if (from != null) {
	// check if whereClause already exists
	if (whereClause.length() > 0) {
		// whereClause already exists. append AND or OR clause as
		// required
		whereClause.append(" AND ");
	}
	// use bind variables. e.g. ? or :EVENT_STATUS
	whereClause.append("x.ORDER_DATE >= ?");
	// append the bind variable
	params.add(from);
}

if (to != null) {
	if (whereClause.length() > 0) {
		whereClause.append(" AND ");
	}
	whereClause.append("x.ORDER_DATE <= ?");
	params.add(to);
}

Query By Name

Sometimes you may want to provide a simple way of performing the search to your users similar to the one shown in the below screenshot. You can achieve this by using a similar pre-query as shown below

                                                                                                                                                                                                                 boolean showAll = "Y".equals(db.getProfile("SHOW_ALL_ACTIVITIES"));
// from a given customer... show all activities for that customer
if (whereClause.indexOf("CUST_ACCOUNT_ID") == -1) {
	String group = data.get("groupcode");
	String dueBy = data.get("dueby");

	if ("MY".equals(group)) {
		if (whereClause.length() > 0) {
			whereClause.append(" AND ");
		}
		whereClause.append("x.OWNER_ID = ?");
		params.add(userid);
	} else if ("ME".equals(group)) {
		if (whereClause.length() > 0) {
			whereClause.append(" AND ");
		}
		whereClause.append("x.CREATED_BY = ?");
		params.add(userid);
	}

	if (data.get("Creation Date") != null) {
		if (whereClause.length() > 0) {
			whereClause.append(" AND ");
		}
		whereClause.append("x.CREATION_DATE > ?");
		params.add(data.get("Creation Date"));
	}
	Date fromDate = new Date();
	Calendar c = Calendar.getInstance();
	c.setTime(fromDate);
	c.roll(Calendar.DATE, false);
	fromDate = dateUtils.getEnd(c.getTime());

	Date toDate = dateUtils.getEnd(new Date());

	Date yestDate = dateUtils.getStart(new Date());

	String statusCode = "Closed";
	if (dueBy != null) {
		if (whereClause.length() > 0) {
			whereClause.append(" AND ");
		}
		if ("YESTERDAY".equals(dueBy)) {
			whereClause.append(" x.status_code != ? AND x.PLANNED_END_DATE < ?");
			params.add(statusCode);
			params.add(yestDate);
		} else {
			whereClause.append("x.PLANNED_END_DATE > ? AND x.PLANNED_END_DATE < ?");
		}
		if ("TODAY".equals(dueBy)) {
			params.add(fromDate);
			params.add(toDate);
		} else if ("TOMORROW".equals(dueBy)) {
			c.setTime(fromDate);
			c.roll(Calendar.DATE, true);
			fromDate = c.getTime();
			params.add(fromDate);
			c.setTime(toDate);
			c.roll(Calendar.DATE, true);
			toDate = c.getTime();
			params.add(toDate);
		} else if ("THIS WEEK".equals(dueBy)) {
			c.setTime(fromDate);
			c.add(Calendar.DAY_OF_WEEK, c.getFirstDayOfWeek() - c.get(Calendar.DAY_OF_WEEK));
			fromDate = c.getTime();
			params.add(fromDate);
			c.add(Calendar.DAY_OF_YEAR, 7);
			toDate = c.getTime();
			params.add(toDate);
		} else if ("NEXT WEEK".equals(dueBy)) {
			c.setTime(fromDate);
			c.add(Calendar.DAY_OF_WEEK, c.getFirstDayOfWeek() - c.get(Calendar.DAY_OF_WEEK));
			c.add(Calendar.DAY_OF_YEAR, 7);
			fromDate = c.getTime();
			params.add(fromDate);
			c.add(Calendar.DAY_OF_YEAR, 7);
			toDate = c.getTime();
			params.add(toDate);
		}
	}

	if (!showAll) {
		if (whereClause.length() > 0) {
			whereClause.append(" AND ");
		}
		whereClause
				.append("(x.GROUP_ID IN (SELECT G.GROUP_ID FROM JTF_RS_GROUP_MEMBERS M, JTF_RS_GROUPS_B G, jtf_rs_roles_vl RL, jtf_rs_role_relations RR")
				.append(" WHERE M.GROUP_ID = G.GROUP_ID AND G.START_DATE_ACTIVE < SYSDATE AND NVL(G.END_DATE_ACTIVE, SYSDATE+1) > SYSDATE AND G.ATTRIBUTE1 = 'Y' AND M.DELETE_FLAG = 'N' AND RR.ROLE_ID = RL.ROLE_ID AND RR.role_resource_type = 'RS_GROUP_MEMBER' AND RR.delete_flag = 'N' AND M.RESOURCE_ID = ?")
				.append(" AND RR.ROLE_RESOURCE_ID = M.GROUP_MEMBER_ID AND RL.ROLE_CODE = 'GWT_MANAGER')")
				.append(" OR x.GROUP_ID IN (SELECT G.GROUP_ID FROM JTF_RS_GROUP_MEMBERS M, JTF_RS_GROUPS_B G WHERE M.GROUP_ID = G.GROUP_ID AND G.ATTRIBUTE1 = 'Y' AND G.ATTRIBUTE2 = 'Y' AND M.RESOURCE_ID = ?)")
				.append(" OR x.OWNER_ID = ? OR x.CREATED_BY = ?")
				.append(" OR EXISTS (SELECT 1 FROM xxdaz_mace_subscriptions XMS WHERE  XMS.USER_ID = ? AND XMS.ACTIVITY_ID = X.ACTIVITY_ID AND XMS.SUBSCRIBED_FLAG = 'Y'))");
		params.add(db.getUserSession().getResourceId());
		params.add(db.getUserSession().getResourceId());
		params.add(userid);
		params.add(userid);
		params.add(userid);
	}
}                                                                                                                                                                                                              

Variables available for Pre Query

data
an RPCModelData instance that contains user's input from the search panel (RPCModelData)
whereClause
system generated whereClause if any (StringBuilder)
params
bind values in an array of objects (ArrayList<Object>)
selectClause
the select portion of the query SQL (StringBuilder)
fromClause
the from portion of the query (StringBuilder)
orderByClause
the order by clause of the query if any (StringBuilder)
fullSQL
selectClause + fromClause + whereClause + orderByClause (StringBuilder)
db
an instance of  ScriptUtil  class
dateUtils
an instance of DateUtils class
user
signed in user's username
userid
signed in user's userid (Double)
subDomain
current application's subdomain
dsName
Alias Name of the current Datasource
skipQuery
Set this to true to skip the query execution (boolean)
rows
The data to be returned to the browser when skipQuery is set to true (List<RPCModelData>)
skipPagination
Set "Y" to fetch all rows (Y/N)