Examples of pre-query scripts
The manipulation of the WHERE clause of a Search. The platform appends the result of this query to the result of 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);
2. Advanced manipulation of the WHERE clause
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);
}
3. 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);
}
4. The manipulation of the ORDER BY clause of a search. The platform appends the result of this query to the result of the generated order by clause.
if (orderByClause.length() > 0) {
orderByClause.append(", ");
}
orderByClause.append("x.NET_LOSS_AMOUNT");
5. Replacing the 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
}
6. 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("phoneNumber");
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());
}
7. 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("_", ""));
}
}
8. Restricting blind query by appending the following 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");
}
10. Custom handling of grid filters
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() + "%");
}
}
}
11. Range query
Date from = data.get("dateFrom");
Date to = data.get("dateTo");
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);
}
12. Query by name
The following pre-query script shows a simple approach for performing search:
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("creationDate") != null) {
if (whereClause.length() > 0) {
whereClause.append(" AND ");
}
whereClause.append("x.CREATION_DATE > ?");
params.add(data.get("creationDate"));
}
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);
}
}
Last updated
Was this helpful?