Start line:  
End line:  

Snippet Preview

Snippet HTML Code

Stack Overflow Questions
Copyright 2005-2013 The Kuali Foundation Licensed under the Educational Community License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.opensource.org/licenses/ecl2.php Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
  
  package org.kuali.rice.kew.impl.document.search;
  
  
  import java.util.HashMap;
  import java.util.HashSet;
  import java.util.List;
  import java.util.Map;
  import java.util.Set;
  import java.util.TreeSet;


Reference implementation of the DocumentSearchGenerator.

Author(s):
Kuali Rice Team (rice.collab@kuali.org)
  
  public class DocumentSearchGeneratorImpl implements DocumentSearchGenerator {
  
      private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(DocumentSearchGeneratorImpl.class);
  
      private static final String ROUTE_NODE_TABLE = "KREW_RTE_NODE_T";
      private static final String ROUTE_NODE_INST_TABLE = "KREW_RTE_NODE_INSTN_T";
      private static final String DATABASE_WILDCARD_CHARACTER_STRING = "%";
      private static final char DATABASE_WILDCARD_CHARACTER = .toCharArray()[0];
  
      private DatabasePlatform dbPlatform;
      private MessageMap messageMap;
  
      private SqlBuilder sqlBuilder = null;
  
      @Override
          return DocumentSearchCriteria.Builder.create().build();
      }
  
      public DocumentType getValidDocumentType(String documentTypeFullName) {
          if (!org.apache.commons.lang.StringUtils.isEmpty(documentTypeFullName)) {
              DocumentType documentType = KEWServiceLocator.getDocumentTypeService().findByNameCaseInsensitive(documentTypeFullName);
              if (documentType == null) {
                  throw new RuntimeException("No Valid Document Type Found for document type name '" + documentTypeFullName + "'");
              }
              return documentType;
         }
         return null;
     }
 
     @Override
         List<RemotableAttributeErrorerrors = new ArrayList<RemotableAttributeError>();
         DocumentType documentType = null;
         try{
               documentType = getValidDocumentType(criteria.getDocumentTypeName());
         }catch(RuntimeException re){
             errors.add(RemotableAttributeError.Builder.create("documentTypeName"re.getMessage()).build());
         }
 
         if (documentType != null) {
             errors = KEWServiceLocator.getDocumentSearchCustomizationMediator().validateLookupFieldParameters(documentTypecriteria.build());
         } else {
             criteria.setDocumentAttributeValues(new HashMap<StringList<String>>());
         }
         return errors == null ? Collections.<RemotableAttributeError>emptyList() : Collections.unmodifiableList(errors);
     }
 
     public QueryComponent getSearchableAttributeSql(Map<StringList<String>> documentAttributeValuesList<RemotableAttributeFieldsearchFieldsString whereClausePredicatePrefix) {
 
         StringBuilder fromSql = new StringBuilder();
         StringBuilder whereSql = new StringBuilder();
 
         //Map<String, List<SearchAttributeCriteriaComponent>> searchableAttributeRangeComponents = new HashMap<String,List<SearchAttributeCriteriaComponent>>();
         Criteria finalCriteria = null;
         int tableIndex = 1;
         SqlBuilder sqlBuilder = this.getSqlBuilder();
 
         for (String documentAttributeName : documentAttributeValues.keySet()) {
             String documentAttributeNameForSQL = documentAttributeName;
             if (documentAttributeName.contains(.)) {
                 documentAttributeNameForSQL = documentAttributeName.replaceFirst(."");
             }
             List<StringsearchValues = documentAttributeValues.get(documentAttributeName);
             if (CollectionUtils.isEmpty(searchValues) || documentAttributeName.contains(.)) {
                 continue;
             }
 
             String tableAlias = "EXT" + tableIndex;
             RemotableAttributeField searchField = getSearchFieldByName(documentAttributeNamesearchFields);
             String tableName = DocumentSearchInternalUtils.getAttributeTableName(searchField);
             boolean caseSensitive = DocumentSearchInternalUtils.isLookupCaseSensitive(searchField);
 
             Criteria crit = null;
 
             Class<?> dataTypeClass = DocumentSearchInternalUtils.getDataTypeClass(searchField);
             if (searchValues.size() > 1) {
                 // if there's more than one entry, we need to do an "in"
                 crit = new Criteria(tableNametableAlias);
                 crit.setDbPlatform(sqlBuilder.getDbPlatform());
                 crit.in("VAL"searchValuesdataTypeClass);
             } else {
                 crit = sqlBuilder.createCriteria("VAL"searchValues.get(0) , tableNametableAliasdataTypeClass, !caseSensitive);
             }
 
             sqlBuilder.addCriteria("KEY_CD"documentAttributeNameForSQLString.classfalsefalsecrit); // this is always of type string.
             sqlBuilder.andCriteria("DOC_HDR_ID"tableAlias + ".DOC_HDR_ID""KREW_DOC_HDR_T""DOC_HDR"SqlBuilder.JoinType.classfalsefalsecrit);
 
             if (finalCriteria == null ){
                 finalCriteria = crit;
             } else{
                 sqlBuilder.andCriteria(finalCriteriacrit);
             }
 
             // - below is the old code
             // if where clause is empty then use passed in prefix... otherwise generate one
             String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
             QueryComponent qc = generateSearchableAttributeSql(tableNamedocumentAttributeNameForSQLwhereClausePrefixtableIndex);
             fromSql.append(qc.getFromSql());
             tableIndex++;
         }
 
         if (finalCriteria == null) {
             return new QueryComponent("""""");
         }
 
         String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
 
         return new QueryComponent(""fromSql.toString(), whereClausePrefix + " " + finalCriteria.buildWhere());
     }
 
     private RemotableAttributeField getSearchFieldByName(String fieldNameList<RemotableAttributeFieldsearchFields) {
         for (RemotableAttributeField searchField : searchFields) {
             if (searchField.getName().equals(fieldName)
                     || searchField.getName().equals(. + fieldName)) {
                 return searchField;
             }
         }
         throw new IllegalStateException("Failed to locate a RemotableAttributeField for fieldName=" + fieldName);
     }
 
     public QueryComponent generateSearchableAttributeSql(String tableNameString documentAttributeNameString whereSqlStarter,int tableIndex) {
         String tableIdentifier = "EXT" + tableIndex;
         QueryComponent joinSqlComponent = getSearchableAttributeJoinSql(tableNametableIdentifierwhereSqlStarterdocumentAttributeName);
         return new QueryComponent(""joinSqlComponent.getFromSql(), joinSqlComponent.getWhereSql());
     }
 
     public QueryComponent getSearchableAttributeJoinSql(String tableNameString tableIdentifierString whereSqlStarterString attributeTableKeyColumnName) {
         return new QueryComponent(""generateSearchableAttributeFromSql(tableNametableIdentifier).toString(), generateSearchableAttributeWhereClauseJoin(whereSqlStartertableIdentifierattributeTableKeyColumnName).toString());
     }
 
     public StringBuilder generateSearchableAttributeWhereClauseJoin(String whereSqlStarter,String tableIdentifier,String attributeTableKeyColumnName) {
         StringBuilder whereSql = new StringBuilder(constructWhereClauseElement(whereSqlStarter"DOC_HDR.DOC_HDR_ID""="getDbPlatform().escapeString(tableIdentifier + ".DOC_HDR_ID"), nullnull));
         whereSql.append(constructWhereClauseElement(" and "tableIdentifier + ".KEY_CD""=",
                 getDbPlatform().escapeString(attributeTableKeyColumnName), "'""'"));
         return whereSql;
     }
 
     public StringBuilder generateSearchableAttributeFromSql(String tableNameString tableIdentifier) {
         if (StringUtils.isBlank(tableName)) {
             throw new IllegalArgumentException("tableName was null or blank");
         }
         if (StringUtils.isBlank(tableIdentifier)) {
             throw new IllegalArgumentException("tableIdentifier was null or blank");
         }
         StringBuilder fromSql = new StringBuilder();
         fromSql.append(" ,").append(tableName).append(" ").append(getDbPlatform().escapeString(tableIdentifier)).append(" ");
         return fromSql;
     }
 
     public StringBuilder constructWhereClauseElement(String clauseStarter,String queryTableColumnName,String operand,String valueToSearch,String valuePrefix,String valueSuffix) {
         StringBuilder whereSql = new StringBuilder();
         valuePrefix = (valuePrefix != null) ? valuePrefix : "";
         valueSuffix = (valueSuffix != null) ? valueSuffix : "";
         whereSql.append(" " + clauseStarter + " ").append(getDbPlatform().escapeString(queryTableColumnName)).append(" " + operand + " ").append(valuePrefix).append(valueToSearch).append(valueSuffix).append(" ");
         return whereSql;
     }
 
     @Override
     public DocumentSearchResults.Builder processResultSet(DocumentSearchCriteria criteriaboolean criteriaModifiedStatement searchAttributeStatementResultSet resultSetint maxResultCapint fetchLimitthrows SQLException {
         DocumentSearchCriteria.Builder criteriaBuilder = DocumentSearchCriteria.Builder.create(criteria);
         DocumentSearchResults.Builder results = DocumentSearchResults.Builder.create(criteriaBuilder);
         results.setCriteriaModified(criteriaModified);
         int size = 0;
         List<DocumentSearchResult.BuilderresultList = new ArrayList<DocumentSearchResult.Builder>();
         results.setSearchResults(resultList);
         Map<StringDocumentSearchResult.BuilderresultMap = new HashMap<StringDocumentSearchResult.Builder>();
         PerformanceLogger perfLog = new PerformanceLogger();
         int iteration = 0;
         int startAt = (criteria.getStartAtIndex()==null) ? 0 : criteria.getStartAtIndex();
         maxResultCap += startAt;
         boolean resultSetHasNext = resultSet.next();
         while ( resultSetHasNext && resultMap.size() < maxResultCap && iteration++ < fetchLimit && startAt >= 0) {
 
             if(iteration <= startAt) {
                 resultSetHasNext = resultSet.next();
                 continue;
             }
 
             DocumentSearchResult.Builder resultBuilder = processRow(criteriasearchAttributeStatementresultSet);
             String documentId = resultBuilder.getDocument().getDocumentId();
             if (!resultMap.containsKey(documentId)) {
                 resultList.add(resultBuilder);
                 resultMap.put(documentIdresultBuilder);
                 size++;
             } else {
                 // handle duplicate rows with different search data
                 DocumentSearchResult.Builder previousEntry = resultMap.get(documentId);
                 handleMultipleDocumentRows(previousEntryresultBuilder);
             }
             resultSetHasNext = resultSet.next();
         }
 
         perfLog.log("Time to read doc search results."true);
         // if we have threshold+1 results, then we have more results than we are going to display
         results.setOverThreshold(resultSetHasNext);
 
         .debug("Processed "+size+" document search result rows.");
         return results;
     }

    
Handles multiple document rows by collapsing them into the list of document attributes on the existing row. The two rows must represent the same document.

Parameters:
existingRow the existing row to combine the new row into
newRow the new row from which to combine document attributes with the existing row
 
     private void handleMultipleDocumentRows(DocumentSearchResult.Builder existingRowDocumentSearchResult.Builder newRow) {
         for (DocumentAttribute.AbstractBuilder<?> newDocumentAttribute : newRow.getDocumentAttributes()) {
             existingRow.getDocumentAttributes().add(newDocumentAttribute);
         }
     }

    
Processes the search result row, returning a DocumentSearchResult

Parameters:
criteria the original search criteria
searchAttributeStatement statement being used to call the database for queries
rs the search result set
Returns:
a DocumentSearchResult representing the current ResultSet row
Throws:
java.sql.SQLException
 
     protected DocumentSearchResult.Builder processRow(DocumentSearchCriteria criteriaStatement searchAttributeStatementResultSet rsthrows SQLException {
 
         String documentId = rs.getString("DOC_HDR_ID");
         String initiatorPrincipalId = rs.getString("INITR_PRNCPL_ID");
         String documentTypeName = rs.getString("DOC_TYP_NM");
         org.kuali.rice.kew.api.doctype.DocumentType documentType =
                 KewApiServiceLocator.getDocumentTypeService().getDocumentTypeByName(documentTypeName);
         if (documentType == null) {
             throw new IllegalStateException("Failed to locate a document type with the given name: " + documentTypeName);
         }
         String documentTypeId = documentType.getId();
 
         Document.Builder documentBuilder = Document.Builder.create(documentIdinitiatorPrincipalIddocumentTypeNamedocumentTypeId);
         DocumentSearchResult.Builder resultBuilder = DocumentSearchResult.Builder.create(documentBuilder);
 
         String statusCode = rs.getString("DOC_HDR_STAT_CD");
         Timestamp createTimestamp = rs.getTimestamp("CRTE_DT");
         String title = rs.getString("TTL");
         String applicationDocumentStatus = rs.getString("APP_DOC_STAT");
 
         documentBuilder.setStatus(DocumentStatus.fromCode(statusCode));
         documentBuilder.setDateCreated(new DateTime(createTimestamp.getTime()));
         documentBuilder.setTitle(title);
         documentBuilder.setApplicationDocumentStatus(applicationDocumentStatus);
         documentBuilder.setApplicationDocumentStatusDate(new DateTime(rs.getTimestamp("APP_DOC_STAT_MDFN_DT")));
         documentBuilder.setDateApproved(new DateTime(rs.getTimestamp("APRV_DT")));
         documentBuilder.setDateFinalized(new DateTime(rs.getTimestamp("FNL_DT")));
         documentBuilder.setApplicationDocumentId(rs.getString("APP_DOC_ID"));
         documentBuilder.setDateLastModified(new DateTime(rs.getTimestamp("STAT_MDFN_DT")));
         documentBuilder.setRoutedByPrincipalId(rs.getString("RTE_PRNCPL_ID"));
 
         // TODO - KULRICE-5755 - should probably set as many properties on the document as we can
         documentBuilder.setDocumentHandlerUrl(rs.getString("DOC_HDLR_URL"));
 
         if (isUsingAtLeastOneSearchAttribute(criteria)) {
             populateDocumentAttributesValues(resultBuildersearchAttributeStatement);
         }
 
         return resultBuilder;
     }

    
This method performs searches against the search attribute value tables (see classes implementing org.kuali.rice.kew.docsearch.SearchableAttributeValue) to get data to fill in search attribute values on the given resultBuilder parameter

Parameters:
resultBuilder - document search result object getting search attributes added to it
searchAttributeStatement - statement being used to call the database for queries
Throws:
java.sql.SQLException
 
     public void populateDocumentAttributesValues(DocumentSearchResult.Builder resultBuilderStatement searchAttributeStatementthrows SQLException {
         searchAttributeStatement.setFetchSize(50);
         String documentId = resultBuilder.getDocument().getDocumentId();
         List<SearchableAttributeValueattributeValues = DocumentSearchInternalUtils
                 .getSearchableAttributeValueObjectTypes();
         PerformanceLogger perfLog = new PerformanceLogger(documentId);
         for (SearchableAttributeValue searchAttValue : attributeValues) {
             String attributeSql = "select KEY_CD, VAL from " + searchAttValue.getAttributeTableName() + " where DOC_HDR_ID = '" + documentId + "'";
             ResultSet attributeResultSet = null;
             try {
                 attributeResultSet = searchAttributeStatement.executeQuery(attributeSql);
                 while (attributeResultSet.next()) {
                     searchAttValue.setSearchableAttributeKey(attributeResultSet.getString("KEY_CD"));
                     searchAttValue.setupAttributeValue(attributeResultSet"VAL");
                     if ( (!org.apache.commons.lang.StringUtils.isEmpty(searchAttValue.getSearchableAttributeKey())) && (searchAttValue.getSearchableAttributeValue() != null) ) {
                         DocumentAttribute documentAttribute = searchAttValue.toDocumentAttribute();
                         resultBuilder.getDocumentAttributes().add(DocumentAttributeFactory.loadContractIntoBuilder(
                                 documentAttribute));
                     }
                 }
             } finally {
                 if (attributeResultSet != null) {
                     try {
                         attributeResultSet.close();
                     } catch (Exception e) {
                         .warn("Could not close searchable attribute result set for class " + searchAttValue.getClass().getName(),e);
                     }
                 }
             }
         }
         perfLog.log("Time to execute doc search search attribute queries."true);
     }
 
     @SuppressWarnings("deprecation")
     public String generateSearchSql(DocumentSearchCriteria criteriaList<RemotableAttributeFieldsearchFields) {
 
         String docTypeTableAlias   = "DOC1";
         String docHeaderTableAlias = "DOC_HDR";
 
         String sqlPrefix = "Select * from (";
         String sqlSuffix = ") FINAL_SEARCH order by FINAL_SEARCH.CRTE_DT desc";
         
         // the DISTINCT here is important as it filters out duplicate rows which could occur as the result of doc search extension values...
         StringBuilder selectSQL = new StringBuilder("select DISTINCT("docHeaderTableAlias +".DOC_HDR_ID), "
                                                     + StringUtils.join(new String[] {
                                                         docHeaderTableAlias + ".INITR_PRNCPL_ID",
                                                         docHeaderTableAlias + ".DOC_HDR_STAT_CD",
                                                         docHeaderTableAlias + ".CRTE_DT",
                                                         docHeaderTableAlias + ".TTL",
                                                         docHeaderTableAlias + ".APP_DOC_STAT",
                                                         docHeaderTableAlias + ".STAT_MDFN_DT",
                                                         docHeaderTableAlias + ".APRV_DT",
                                                         docHeaderTableAlias + ".FNL_DT",
                                                         docHeaderTableAlias + ".APP_DOC_ID",
                                                         docHeaderTableAlias + ".RTE_PRNCPL_ID",
                                                         docHeaderTableAlias + ".APP_DOC_STAT_MDFN_DT",
                                                         docTypeTableAlias + ".DOC_TYP_NM",
                                                         docTypeTableAlias + ".LBL",
                                                         docTypeTableAlias + ".DOC_HDLR_URL",
                                                         docTypeTableAlias + ".ACTV_IND"
                                                     }, ", "));
         StringBuilder fromSQL = new StringBuilder(" from KREW_DOC_TYP_T "docTypeTableAlias +" ");
         StringBuilder fromSQLForDocHeaderTable = new StringBuilder(", KREW_DOC_HDR_T " + docHeaderTableAlias + " ");
 
         StringBuilder whereSQL = new StringBuilder();
         whereSQL.append(getDocumentIdSql(criteria.getDocumentId(), getGeneratedPredicatePrefix(whereSQL.length()), docHeaderTableAlias));
         // if principalId criteria exists ignore deprecated principalName search term
         String principalInitiatorIdSql = getInitiatorIdSql(criteria.getInitiatorPrincipalId(), getGeneratedPredicatePrefix(whereSQL.length()));
         if (StringUtils.isNotBlank(principalInitiatorIdSql)) {
             whereSQL.append(principalInitiatorIdSql);
         } else {
             whereSQL.append(getInitiatorSql(criteria.getInitiatorPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length())));
         }
         whereSQL.append(getAppDocIdSql(criteria.getApplicationDocumentId(), getGeneratedPredicatePrefix(whereSQL.length())));
         whereSQL.append(getDateCreatedSql(criteria.getDateCreatedFrom(), criteria.getDateCreatedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
         whereSQL.append(getDateLastModifiedSql(criteria.getDateLastModifiedFrom(), criteria.getDateLastModifiedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
         whereSQL.append(getDateApprovedSql(criteria.getDateApprovedFrom(), criteria.getDateApprovedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
         whereSQL.append(getDateFinalizedSql(criteria.getDateFinalizedFrom(), criteria.getDateFinalizedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
 
         // flags for the table being added to the FROM class of the sql
         String principalViewerSql = getViewerSql(criteria.getViewerPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length()));
         String principalViewerIdSql = getViewerIdSql(criteria.getViewerPrincipalId(), getGeneratedPredicatePrefix(whereSQL.length()));
         // if principalId criteria exists ignore deprecated principalName search term
         if (StringUtils.isNotBlank(principalViewerIdSql)){
             principalViewerSql = "";
         }
         String groupViewerSql = getGroupViewerSql(criteria.getGroupViewerId(), getGeneratedPredicatePrefix(whereSQL.length()));
         if (StringUtils.isNotBlank(principalViewerSql) || StringUtils.isNotBlank(groupViewerSql) || StringUtils.isNotBlank(principalViewerIdSql) ) {
             whereSQL.append(principalViewerSql);
             whereSQL.append(principalViewerIdSql);
             whereSQL.append(groupViewerSql);
             fromSQL.append(", KREW_ACTN_RQST_T ");
         }
 
         String principalApproverSql =  getApproverSql(criteria.getApproverPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length()));
         String principalApproverIdSql = getApproverIdSql(criteria.getApproverPrincipalId(), getGeneratedPredicatePrefix(whereSQL.length()));
         // if principalId criteria exists ignore deprecated principalName search term
         if (StringUtils.isNotBlank(principalApproverIdSql)){
             principalApproverSql = "";
         }
         if (StringUtils.isNotBlank(principalApproverSql) || StringUtils.isNotBlank(principalApproverIdSql)) {
             whereSQL.append(principalApproverSql);
             whereSQL.append(principalApproverIdSql);
             fromSQL.append(", KREW_ACTN_TKN_T ");
         }
 
 
 
         String docRouteNodeSql = getDocRouteNodeSql(criteria.getDocumentTypeName(), criteria.getRouteNodeName(), criteria.getRouteNodeLookupLogic(), getGeneratedPredicatePrefix(whereSQL.length()));
         if (StringUtils.isNotBlank(docRouteNodeSql)) {
             whereSQL.append(docRouteNodeSql);
             fromSQL.append(", KREW_RTE_NODE_INSTN_T ");
             fromSQL.append(", KREW_RTE_NODE_T ");
         }
 
         if (!criteria.getDocumentAttributeValues().isEmpty()) {
             QueryComponent queryComponent = getSearchableAttributeSql(criteria.getDocumentAttributeValues(), searchFieldsgetGeneratedPredicatePrefix(
                     whereSQL.length()));
             selectSQL.append(queryComponent.getSelectSql());
             fromSQL.append(queryComponent.getFromSql());
             whereSQL.append(queryComponent.getWhereSql());
         }
 
         whereSQL.append(getDocTypeFullNameWhereSql(criteriagetGeneratedPredicatePrefix(whereSQL.length())));
         whereSQL.append(getDocTitleSql(criteria.getTitle(), getGeneratedPredicatePrefix(whereSQL.length())));
         whereSQL.append(getDocumentStatusSql(criteria.getDocumentStatuses(), criteria.getDocumentStatusCategories(), getGeneratedPredicatePrefix(whereSQL.length())));
         whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_TYP_ID = DOC1.DOC_TYP_ID ");
         fromSQL.append(fromSQLForDocHeaderTable);
 
         // App Doc Status Value and Transition clauses
 
         List<StringapplicationDocumentStatuses = criteria.getApplicationDocumentStatuses();
         // deal with legacy usage of applicationDocumentStatus (which is deprecated)
         if (!StringUtils.isBlank(criteria.getApplicationDocumentStatus())) {
             if (!criteria.getApplicationDocumentStatuses().contains(criteria.getApplicationDocumentStatus())) {
                 applicationDocumentStatuses = new ArrayList<String>(criteria.getApplicationDocumentStatuses());
                 applicationDocumentStatuses.add(criteria.getApplicationDocumentStatus());
             }
         }
 
         whereSQL.append(getAppDocStatusesSql(applicationDocumentStatusesgetGeneratedPredicatePrefix(
                 whereSQL.length()), statusTransitionWhereClause.length()));
         if (statusTransitionWhereClause.length() > 0){
         	whereSQL.append(statusTransitionWhereClause);
             whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_HDR_ID = STAT_TRAN.DOC_HDR_ID ");
         	fromSQL.append(", KREW_APP_DOC_STAT_TRAN_T STAT_TRAN ");
         }
 
         String finalizedSql = sqlPrefix + " " + selectSQL.toString() + " " + fromSQL.toString() + " " + whereSQL.toString() + " " + sqlSuffix;
 
         .info("*********** SEARCH SQL ***************");
         .info(finalizedSql);
         .info("**************************************");
         return finalizedSql;
     }
 
     public String getDocumentIdSql(String documentIdString whereClausePredicatePrefixString tableAlias) {
         if (StringUtils.isBlank(documentId)) {
             return "";
         } else {
         	// Using true for caseInsensitive causes bad performance for MYSQL databases since function indexes cannot be added.
         	// Due to this, false is passed for caseInsensitive
             Criteria crit = getSqlBuilder().createCriteria("DOC_HDR_ID"documentId"KREW_DOC_HDR_T"tableAliasString.classfalsetrue);
             return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
         }
     }
 
     public String getDocTitleSql(String docTitleString whereClausePredicatePrefix) {
         if (StringUtils.isBlank(docTitle)) {
             return "";
         } else {
             // quick and dirty ' replacement that isn't the best but should work for all dbs
             docTitle = docTitle.trim().replace("\'""\'\'");
             SqlBuilder sqlBuild = new SqlBuilder();
             Criteria crit = new Criteria("KREW_DOC_HDR_T""DOC_HDR");
             sqlBuild.addCriteria("TTL"docTitleString.classtruetruecrit);
             return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
         }
     }
 
     // special methods that return the sql needed to complete the search
     // or nothing if the field was not filled in
     public String getAppDocIdSql(String appDocIdString whereClausePredicatePrefix) {
         if (StringUtils.isBlank(appDocId)) {
             return "";
         } else {
             String tableAlias = "DOC_HDR";
             Criteria crit = getSqlBuilder().createCriteria("APP_DOC_ID"appDocId"KREW_DOC_HDR_T"tableAlias,String.class);
             return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
         }
     }
 
     public String getDateCreatedSql(DateTime fromDateCreatedDateTime toDateCreatedString whereClausePredicatePrefix) {
         return establishDateString(fromDateCreatedtoDateCreated"KREW_DOC_HDR_T""DOC_HDR""CRTE_DT"whereClausePredicatePrefix);
     }
 
     public String getDateApprovedSql(DateTime fromDateApprovedDateTime toDateApprovedString whereClausePredicatePrefix) {
         return establishDateString(fromDateApprovedtoDateApproved"KREW_DOC_HDR_T""DOC_HDR""APRV_DT"whereClausePredicatePrefix);
     }
 
     public String getDateFinalizedSql(DateTime fromDateFinalizedDateTime toDateFinalizedString whereClausePredicatePrefix) {
         return establishDateString(fromDateFinalizedtoDateFinalized"KREW_DOC_HDR_T""DOC_HDR""FNL_DT"whereClausePredicatePrefix);
     }
 
     public String getDateLastModifiedSql(DateTime fromDateLastModifiedDateTime toDateLastModifiedString whereClausePredicatePrefix) {
         return establishDateString(fromDateLastModifiedtoDateLastModified"KREW_DOC_HDR_T""DOC_HDR""STAT_MDFN_DT"whereClausePredicatePrefix);
     }
 
 	public String getStatusTransitionDateSql(DateTime fromStatusTransitionDateDateTime toStatusTransitionDateString whereClausePredicatePrefix) {
         return establishDateString(fromStatusTransitionDatetoStatusTransitionDate"KREW_DOC_HDR_T""DOC_HDR""APP_DOC_STAT_MDFN_DT"whereClausePredicatePrefix);
     }
 
     public String getViewerSql(String viewerString whereClausePredicatePrefix) {
         StringBuilder returnSql = new StringBuilder();
         if (StringUtils.isNotBlank(viewer)) {
             Map<StringStringm = new HashMap<StringString>();
             m.put("principalName"viewer);
 
             // This will search for people with the ability for the valid operands.
             List<PersonpersonList = KimApiServiceLocator.getPersonService().findPeople(mfalse);
             List<StringprincipalList = new ArrayList<String>();
 
             if(CollectionUtils.isEmpty(personList)) {
             	// findPeople allows for wildcards, but the person must be active.  If no one was found,
             	// check for an exact inactive user.
                 PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(viewer.trim());
         		if (tempPrincipal != null) {
                     principalList.add(tempPrincipal.getPrincipalId());
             	} else {
                     // they entered something that returned nothing... so we should return nothing
 
                     return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
             	}
             }
 
             for (Person person : personList){
                 principalList.add(person.getPrincipalId());
             }
 
             Criteria crit = new Criteria("KREW_ACTN_RQST_T""KREW_ACTN_RQST_T");
             crit.in("PRNCPL_ID"principalListString.class);
             returnSql.append(whereClausePredicatePrefix + "( (DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and " + crit.buildWhere() + " )");
 
             Set<StringviewerGroupIds = new TreeSet<String>();
 
             if(CollectionUtils.isNotEmpty(principalList)) {
                 for(String principalIdprincipalList){
                     viewerGroupIds.addAll(KimApiServiceLocator.getGroupService().getGroupIdsByPrincipalId(principalId));
                 }
             }
 
             // Documents routed to users as part of a workgoup should be returned.
             // Use Chad's escape stuff
             if (viewerGroupIds != null && !viewerGroupIds.isEmpty()) {
 
                 returnSql.append(" or ( " +
                     "DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID " +
                     "and KREW_ACTN_RQST_T.GRP_ID in (");
 
                 boolean first = true;
                 for (String groupId : viewerGroupIds){
                     if(!first){
                         returnSql.append(",");
                     }
                     returnSql.append("'").append(groupId).append("'");
                     first = false;
                 }
                 returnSql.append("))");
             }
             returnSql.append(")");
         }
         return returnSql.toString();
     }
 
     public String getViewerIdSql(String viewerIdString whereClausePredicatePrefix) {
         StringBuilder returnSql = new StringBuilder();
         if (StringUtils.isNotBlank(viewerId)) {
             Map<StringStringm = new HashMap<StringString>();
             m.put("principalId"viewerId);
 
             // This will search for people with the ability for the valid operands.
             List<PersonpersonList = KimApiServiceLocator.getPersonService().findPeople(mfalse);
             List<StringprincipalList = new ArrayList<String>();
 
             if(CollectionUtils.isEmpty(personList)) {
                 // they entered something that returned nothing... so we should return nothing
                 return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
             }
 
             for (Person person : personList){
                 principalList.add(person.getPrincipalId());
             }
 
             Criteria crit = new Criteria("KREW_ACTN_RQST_T""KREW_ACTN_RQST_T");
             crit.in("PRNCPL_ID"principalListString.class);
             returnSql.append(whereClausePredicatePrefix + "( DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and " + crit.buildWhere() + " )");
         }
         return returnSql.toString();
     }
 
     public String getGroupViewerSql(String groupIdString whereClausePredicatePrefix) {
         String sql = "";
         if (StringUtils.isNotBlank(groupId)) {
             sql = whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and KREW_ACTN_RQST_T.GRP_ID = '" + groupId + "'";
         }
         return sql;
     }
 
     public String getInitiatorSql(String initiatorPrincipalNameString whereClausePredicatePrefix) {
 
         if (StringUtils.isBlank(initiatorPrincipalName)) {
             return "";
         }
 
         String tableAlias = "DOC_HDR";
 
         Map<StringStringm = new HashMap<StringString>();
         m.put("principalName"initiatorPrincipalName);
 
         // This will search for people with the ability for the valid operands.
         List<PersonpList = KimApiServiceLocator.getPersonService().findPeople(mfalse);
         List<StringprincipalList = new ArrayList<String>();
 
         if(pList == null || pList.isEmpty() ){
        		// findPeople allows for wildcards, but the person must be active.  If no one was found,
        		// check for an exact inactive user.
        		PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(initiatorPrincipalName.trim());
        		if (tempPrincipal != null) {
        			principalList.add(tempPrincipal.getPrincipalId());
        		} else {
                 // they entered something that returned nothing... so we should return nothing
                 return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
         	}
         }
 
         for(Person ppList){
             principalList.add(p.getPrincipalId());
         }
 
         Criteria crit = new Criteria("KREW_DOC_HDR_T"tableAlias);
         crit.in("INITR_PRNCPL_ID"principalListString.class);
 
         return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
     }
 
     public String getInitiatorIdSql(String initiatorPrincipalIdString whereClausePredicatePrefix) {
 
         if (StringUtils.isBlank(initiatorPrincipalId)) {
             return "";
         }
 
         String tableAlias = "DOC_HDR";
 
         Map<StringStringm = new HashMap<StringString>();
         m.put("principalId"initiatorPrincipalId);
 
         // This will search for people with the ability for the valid operands.
         List<PersonpList = KimApiServiceLocator.getPersonService().findPeople(mfalse);
         List<StringprincipalList = new ArrayList<String>();
 
         if(pList == null || pList.isEmpty() ){
             // they entered something that returned nothing... so we should return nothing
             return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
         }
 
         for(Person ppList){
             principalList.add(p.getPrincipalId());
         }
 
         Criteria crit = new Criteria("KREW_DOC_HDR_T"tableAlias);
         crit.in("INITR_PRNCPL_ID"principalListString.class);
 
         return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
     }
 
     public String getApproverSql(String approverString whereClausePredicatePrefix) {
         String returnSql = "";
         if (StringUtils.isNotBlank(approver)) {
             Map<StringStringm = new HashMap<StringString>();
             m.put("principalName"approver);
 
             // This will search for people with the ability for the valid operands.
             List<PersonpList = KimApiServiceLocator.getPersonService().findPeople(mfalse);
             List<StringprincipalList = new ArrayList<String>();
 
             if(pList == null || pList.isEmpty() ){
            		// findPeople allows for wildcards, but the person must be active.  If no one was found,
            		// check for an exact inactive user.
                 PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(approver.trim());
 
                 if (tempPrincipal != null) {
            			principalList.add(tempPrincipal.getPrincipalId());
                 } else {
                     // they entered something that returned nothing... so we should return nothing
                     return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
                 }
             }
 
             for(Person ppList){
                 principalList.add(p.getPrincipalId());
             }
 
             Criteria crit = new Criteria("KREW_ACTN_TKN_T""KREW_ACTN_TKN_T");
             crit.in("PRNCPL_ID"principalListString.class);
 
             returnSql = whereClausePredicatePrefix +
             " DOC_HDR.DOC_HDR_ID = KREW_ACTN_TKN_T.DOC_HDR_ID and upper(KREW_ACTN_TKN_T.ACTN_CD) in ('" +
             " and " + crit.buildWhere();
         }
         return returnSql;
     }
 
     public String getApproverIdSql(String approverIdString whereClausePredicatePrefix) {
         String returnSql = "";
         if (StringUtils.isNotBlank(approverId)) {
             Map<StringStringm = new HashMap<StringString>();
             m.put("principalId"approverId);
 
             // This will search for people with the ability for the valid operands.
             List<PersonpList = KimApiServiceLocator.getPersonService().findPeople(mfalse);
             List<StringprincipalList = new ArrayList<String>();
 
             if(pList == null || pList.isEmpty() ){
                  // they entered something that returned nothing... so we should return nothing
                     return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
             }
 
             for(Person ppList){
                 principalList.add(p.getPrincipalId());
             }
 
             Criteria crit = new Criteria("KREW_ACTN_TKN_T""KREW_ACTN_TKN_T");
             crit.in("PRNCPL_ID"principalListString.class);
 
             returnSql = whereClausePredicatePrefix +
                     " DOC_HDR.DOC_HDR_ID = KREW_ACTN_TKN_T.DOC_HDR_ID and upper(KREW_ACTN_TKN_T.ACTN_CD) in ('" +
                     . + "','" + . + "')" +
                     " and " + crit.buildWhere();
         }
         return returnSql;
     }
 
     public String getDocTypeFullNameWhereSql(DocumentSearchCriteria criteriaString whereClausePredicatePrefix) {
         List<StringdocumentTypeNamesToSearch = new ArrayList<String>();
         String primaryDocumentTypeName = criteria.getDocumentTypeName();
         if (StringUtils.isNotBlank(primaryDocumentTypeName)) {
             documentTypeNamesToSearch.add(primaryDocumentTypeName);
         }
         documentTypeNamesToSearch.addAll(criteria.getAdditionalDocumentTypeNames());
         StringBuilder returnSql = new StringBuilder("");
         if (CollectionUtils.isNotEmpty(documentTypeNamesToSearch)) {
             int index = 0;
             for (String documentTypeName : documentTypeNamesToSearch) {
                 if (StringUtils.isNotBlank(documentTypeName)) {
                     String clause = index++ == 0 ? "" : " or ";
                     DocumentTypeService docSrv = KEWServiceLocator.getDocumentTypeService();
                     DocumentType docType = docSrv.findByNameCaseInsensitive(documentTypeName.trim());
                     if (docType != null) {
                         if (documentTypeName.contains("*") || documentTypeName.contains("%")) {
                             addDocumentTypeLikeNameToSearchOn(returnSqldocumentTypeName.trim(), clause);
                         } else {
                             addDocumentTypeNameToSearchOn(returnSqldocumentTypeName.trim(), clause);
                         }
                         if (docType.getChildrenDocTypes() != null) {
                             addChildDocumentTypes(returnSqldocType.getChildrenDocTypes());
                         }
                     } else{
                         addDocumentTypeLikeNameToSearchOn(returnSqldocumentTypeName.trim(), clause);
                     }
                 }
             }
         }
         if (returnSql.length() > 0) {
             returnSql.insert(0, "(");
             returnSql.insert(0, whereClausePredicatePrefix);
             returnSql.append(")");
         }
         return returnSql.toString();
     }
 
     public void addChildDocumentTypes(StringBuilder whereSqlCollection<DocumentTypechildDocumentTypes) {
         for (DocumentType child : childDocumentTypes) {
             addDocumentTypeNameToSearchOn(whereSqlchild.getName());
             addChildDocumentTypes(whereSqlchild.getChildrenDocTypes());
         }
     }
 
     public void addDocumentTypeNameToSearchOn(StringBuilder whereSqlString documentTypeName) {
         this.addDocumentTypeNameToSearchOn(whereSqldocumentTypeName" or ");
     }
 
     public void addDocumentTypeNameToSearchOn(StringBuilder whereSqlString documentTypeNameString clause) {
         whereSql.append(clause).append("upper(DOC1.DOC_TYP_NM) = '" + documentTypeName.toUpperCase() + "'");
     }
     public void addDocumentTypeLikeNameToSearchOn(StringBuilder whereSqlString documentTypeNameString clause) {
         documentTypeName = documentTypeName.replace('*''%');
         whereSql.append(clause).append(" upper(DOC1.DOC_TYP_NM) LIKE '" + documentTypeName.toUpperCase() + "'");
     }
 
     public String getDocRouteNodeSql(String documentTypeFullNameString routeNodeNameRouteNodeLookupLogic docRouteLevelLogicString whereClausePredicatePrefix) {
         // -1 is the default 'blank' choice from the route node drop down a number is used because the ojb RouteNode object is used to
         // render the node choices on the form.
         String returnSql = "";
         if (StringUtils.isNotBlank(routeNodeName)) {
             if (docRouteLevelLogic == null) {
                 docRouteLevelLogic = .;
             }
             StringBuilder routeNodeCriteria = new StringBuilder("and " +  + ".NM ");
             if (. == docRouteLevelLogic) {
         		routeNodeCriteria.append("= '" + getDbPlatform().escapeString(routeNodeName) + "' ");
             } else {
                 routeNodeCriteria.append("in (");
                 // below buffer used to facilitate the addition of the string ", " to separate out route node names
                 StringBuilder routeNodeInCriteria = new StringBuilder();
                 boolean foundSpecifiedNode = false;
                 List<RouteNoderouteNodes = KEWServiceLocator.getRouteNodeService().getFlattenedNodes(getValidDocumentType(documentTypeFullName), true);
                 for (RouteNode routeNode : routeNodes) {
                     if (routeNodeName.equals(routeNode.getRouteNodeName())) {
                         // current node is specified node so we ignore it outside of the boolean below
                         foundSpecifiedNode = true;
                         continue;
                     }
                     // below logic should be to add the current node to the criteria if we haven't found the specified node
                     // and the logic qualifier is 'route nodes before specified'... or we have found the specified node and
                     // the logic qualifier is 'route nodes after specified'
                     if ( (!foundSpecifiedNode && . == docRouteLevelLogic) ||
                          (foundSpecifiedNode && . == docRouteLevelLogic) ) {
                         if (routeNodeInCriteria.length() > 0) {
                             routeNodeInCriteria.append(", ");
                         }
                         routeNodeInCriteria.append("'" + routeNode.getRouteNodeName() + "'");
                     }
                 }
                 if (routeNodeInCriteria.length() > 0) {
                     routeNodeCriteria.append(routeNodeInCriteria);
                 } else {
                     routeNodeCriteria.append("''");
                 }
                 routeNodeCriteria.append(") ");
             }
             returnSql = whereClausePredicatePrefix + "DOC_HDR.DOC_HDR_ID = " +  + ".DOC_HDR_ID and " +  + ".RTE_NODE_ID = " +  + ".RTE_NODE_ID and " +  + ".ACTV_IND = 1 " + routeNodeCriteria.toString() + " ";
         }
         return returnSql;
     }
 
     public String getDocumentStatusSql(List<DocumentStatusdocumentStatusesList<DocumentStatusCategorycategoriesString whereClausePredicatePrefix) {
         if (CollectionUtils.isEmpty(documentStatuses) && CollectionUtils.isEmpty(categories)) {
             return whereClausePredicatePrefix + "DOC_HDR.DOC_HDR_STAT_CD != '" + ..getCode() + "'";
         } else {
             // include all given document statuses
             Set<DocumentStatusstatusesToInclude = new HashSet<DocumentStatus>(documentStatuses);
 
             // add all statuses from each category
             for (DocumentStatusCategory category : categories) {
                 Set<DocumentStatuscategoryStatuses = DocumentStatus.getStatusesForCategory(category);
                 statusesToInclude.addAll(categoryStatuses);
             }
 
             Set<StringstatusCodes = new HashSet<String>();
             for (DocumentStatus statusToInclude : statusesToInclude) {
                 statusCodes.add("'" + getDbPlatform().escapeString(statusToInclude.getCode()) + "'");
             }
             return whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_STAT_CD in (" + StringUtils.join(statusCodes", ") +")";
         }
     }

    
This method generates the where clause fragment related to Application Document Status. If the Status values only are defined, search for the appDocStatus value in the route header. If either the transition from/to dates are defined, search agains the status transition history.
 
     public String getAppDocStatusesSql(List<StringappDocStatusesString whereClausePredicatePrefixint statusTransitionWhereClauseLength) {
         if (CollectionUtils.isEmpty(appDocStatuses)) {
             return "";
         } else {
             String inList = buildAppDocStatusInList(appDocStatuses);
 
             if (statusTransitionWhereClauseLength > 0){
                 return whereClausePredicatePrefix + " STAT_TRAN.APP_DOC_STAT_TO" + inList;
             } else {
                 return whereClausePredicatePrefix + " DOC_HDR.APP_DOC_STAT" + inList;
             }
         }
     }
 
     private String buildAppDocStatusInList(List<StringappDocStatuses) {
         StringBuilder sql = new StringBuilder(" IN (");
 
         boolean first = true;
         for (String appDocStatus : appDocStatuses) {
             // commas before each element except the first one
             if (first) {
                 first = false;
             } else {
                 sql.append(",");
             }
 
             sql.append("'");
             sql.append(getDbPlatform().escapeString(appDocStatus.trim()));
             sql.append("'");
         }
 
         sql.append(")");