Examples of pre-query scripts

1. 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");
}

9. Query allowed By default, all attributes are query allowed and the platform automatically generates whereClause when a user perform a search on an attribute. To programmatically control the whereClause, uncheck the Query Allowed property while defining the attribute properties to ignore those attributes while generating the whereClause.

10. Custom handling of grid filters

You can access the data related to the grid filters through FilterConfig object. You can usedata.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() + "%");
      }
    }
}

11. Range query

By default, platform generates range search fields in a simple search panel for all date and number attributes. For custom search panel, while defining the attribute properties, you can define calculated from and to attributes with Select and Query Allowed unchecked. This will limit the range for search values.

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);
    }
}