Java tutorial
/** * Copyright 2005-2014 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 org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang.StringUtils; import org.joda.time.DateTime; import org.kuali.rice.core.api.CoreApiServiceLocator; import org.kuali.rice.core.api.resourceloader.GlobalResourceLoader; import org.kuali.rice.core.api.uif.RemotableAttributeError; import org.kuali.rice.core.api.uif.RemotableAttributeField; import org.kuali.rice.core.api.util.RiceConstants; import org.kuali.rice.core.framework.persistence.jdbc.sql.Criteria; import org.kuali.rice.core.framework.persistence.jdbc.sql.SqlBuilder; import org.kuali.rice.core.framework.persistence.platform.DatabasePlatform; import org.kuali.rice.kew.api.KewApiConstants; import org.kuali.rice.kew.api.KewApiServiceLocator; import org.kuali.rice.kew.api.document.Document; import org.kuali.rice.kew.api.document.DocumentStatus; import org.kuali.rice.kew.api.document.DocumentStatusCategory; import org.kuali.rice.kew.api.document.attribute.DocumentAttribute; import org.kuali.rice.kew.api.document.attribute.DocumentAttributeFactory; import org.kuali.rice.kew.api.document.search.DocumentSearchCriteria; import org.kuali.rice.kew.api.document.search.DocumentSearchResult; import org.kuali.rice.kew.api.document.search.DocumentSearchResults; import org.kuali.rice.kew.api.document.search.RouteNodeLookupLogic; import org.kuali.rice.kew.docsearch.DocumentSearchInternalUtils; import org.kuali.rice.kew.docsearch.QueryComponent; import org.kuali.rice.kew.docsearch.SearchableAttributeValue; import org.kuali.rice.kew.doctype.bo.DocumentType; import org.kuali.rice.kew.doctype.service.DocumentTypeService; import org.kuali.rice.kew.engine.node.RouteNode; import org.kuali.rice.kew.service.KEWServiceLocator; import org.kuali.rice.kew.util.PerformanceLogger; import org.kuali.rice.kim.api.identity.Person; import org.kuali.rice.kim.api.identity.principal.PrincipalContract; import org.kuali.rice.kim.api.services.KimApiServiceLocator; import org.kuali.rice.krad.util.KRADConstants; import org.kuali.rice.krad.util.MessageMap; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Collection; import java.util.Collections; 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 {@code DocumentSearchGenerator}. * * @author 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 = DATABASE_WILDCARD_CHARACTER_STRING.toCharArray()[0]; private org.kuali.rice.kew.api.doctype.DocumentTypeService apiDocumentTypeService; private DatabasePlatform dbPlatform; private MessageMap messageMap; private SqlBuilder sqlBuilder = null; @Override public DocumentSearchCriteria clearSearch(DocumentSearchCriteria criteria) { 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 public List<RemotableAttributeError> validateSearchableAttributes(DocumentSearchCriteria.Builder criteria) { List<RemotableAttributeError> errors = 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(documentType, criteria.build()); } else { criteria.setDocumentAttributeValues(new HashMap<String, List<String>>()); } return errors == null ? Collections.<RemotableAttributeError>emptyList() : Collections.unmodifiableList(errors); } public QueryComponent getSearchableAttributeSql(Map<String, List<String>> documentAttributeValues, List<RemotableAttributeField> searchFields, String 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(KewApiConstants.DOCUMENT_ATTRIBUTE_FIELD_PREFIX)) { documentAttributeNameForSQL = documentAttributeName .replaceFirst(KewApiConstants.DOCUMENT_ATTRIBUTE_FIELD_PREFIX, ""); } List<String> searchValues = documentAttributeValues.get(documentAttributeName); if (CollectionUtils.isEmpty(searchValues) || documentAttributeName.contains(KRADConstants.CHECKBOX_PRESENT_ON_FORM_ANNOTATION)) { continue; } String tableAlias = "EXT" + tableIndex; RemotableAttributeField searchField = getSearchFieldByName(documentAttributeName, searchFields); 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(tableName, tableAlias); crit.setDbPlatform(sqlBuilder.getDbPlatform()); crit.in("VAL", searchValues, dataTypeClass); } else { crit = sqlBuilder.createCriteria("VAL", searchValues.get(0), tableName, tableAlias, dataTypeClass, !caseSensitive); } sqlBuilder.addCriteria("KEY_CD", documentAttributeNameForSQL, String.class, false, false, crit); // this is always of type string. sqlBuilder.andCriteria("DOC_HDR_ID", tableAlias + ".DOC_HDR_ID", "KREW_DOC_HDR_T", "DOC_HDR", SqlBuilder.JoinType.class, false, false, crit); if (finalCriteria == null) { finalCriteria = crit; } else { sqlBuilder.andCriteria(finalCriteria, crit); } // - 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(tableName, documentAttributeNameForSQL, whereClausePrefix, tableIndex); 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 fieldName, List<RemotableAttributeField> searchFields) { for (RemotableAttributeField searchField : searchFields) { if (searchField.getName().equals(fieldName) || searchField.getName().equals(KewApiConstants.DOCUMENT_ATTRIBUTE_FIELD_PREFIX + fieldName)) { return searchField; } } throw new IllegalStateException("Failed to locate a RemotableAttributeField for fieldName=" + fieldName); } public QueryComponent generateSearchableAttributeSql(String tableName, String documentAttributeName, String whereSqlStarter, int tableIndex) { String tableIdentifier = "EXT" + tableIndex; QueryComponent joinSqlComponent = getSearchableAttributeJoinSql(tableName, tableIdentifier, whereSqlStarter, documentAttributeName); return new QueryComponent("", joinSqlComponent.getFromSql(), joinSqlComponent.getWhereSql()); } public QueryComponent getSearchableAttributeJoinSql(String tableName, String tableIdentifier, String whereSqlStarter, String attributeTableKeyColumnName) { return new QueryComponent("", generateSearchableAttributeFromSql(tableName, tableIdentifier).toString(), generateSearchableAttributeWhereClauseJoin(whereSqlStarter, tableIdentifier, attributeTableKeyColumnName).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"), null, null)); whereSql.append(constructWhereClauseElement(" and ", tableIdentifier + ".KEY_CD", "=", getDbPlatform().escapeString(attributeTableKeyColumnName), "'", "'")); return whereSql; } public StringBuilder generateSearchableAttributeFromSql(String tableName, String 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 criteria, boolean criteriaModified, Statement searchAttributeStatement, ResultSet resultSet, int maxResultCap, int fetchLimit) throws SQLException { DocumentSearchCriteria.Builder criteriaBuilder = DocumentSearchCriteria.Builder.create(criteria); DocumentSearchResults.Builder results = DocumentSearchResults.Builder.create(criteriaBuilder); results.setCriteriaModified(criteriaModified); List<DocumentSearchResult.Builder> resultList = new ArrayList<DocumentSearchResult.Builder>(); results.setSearchResults(resultList); Map<String, DocumentSearchResult.Builder> resultMap = new HashMap<String, DocumentSearchResult.Builder>(); int startAt = (criteria.getStartAtIndex() == null) ? 0 : criteria.getStartAtIndex(); int iteration = 0; boolean resultSetHasNext = resultSet.next(); PerformanceLogger perfLog = new PerformanceLogger(); while (resultSetHasNext && resultMap.size() < maxResultCap && iteration < fetchLimit && startAt >= 0) { if (iteration >= startAt) { DocumentSearchResult.Builder resultBuilder = processRow(criteria, searchAttributeStatement, resultSet); String documentId = resultBuilder.getDocument().getDocumentId(); if (!resultMap.containsKey(documentId)) { resultList.add(resultBuilder); resultMap.put(documentId, resultBuilder); } else { // handle duplicate rows with different search data DocumentSearchResult.Builder previousEntry = resultMap.get(documentId); handleMultipleDocumentRows(previousEntry, resultBuilder); } } iteration++; 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); LOG.debug("Processed " + resultMap.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. * * @param existingRow the existing row to combine the new row into * @param newRow the new row from which to combine document attributes with the existing row */ private void handleMultipleDocumentRows(DocumentSearchResult.Builder existingRow, DocumentSearchResult.Builder newRow) { for (DocumentAttribute.AbstractBuilder<?> newDocumentAttribute : newRow.getDocumentAttributes()) { existingRow.getDocumentAttributes().add(newDocumentAttribute); } } /** * Processes the search result row, returning a DocumentSearchResult * @param criteria the original search criteria * @param searchAttributeStatement statement being used to call the database for queries * @param rs the search result set * @return a DocumentSearchResult representing the current ResultSet row * @throws SQLException */ protected DocumentSearchResult.Builder processRow(DocumentSearchCriteria criteria, Statement searchAttributeStatement, ResultSet rs) throws 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 = getApiDocumentTypeService() .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(documentId, initiatorPrincipalId, documentTypeName, documentTypeId); 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(resultBuilder, searchAttributeStatement); } return resultBuilder; } /** * This method performs searches against the search attribute value tables (see classes implementing * {@link org.kuali.rice.kew.docsearch.SearchableAttributeValue}) to get data to fill in search attribute values on the given resultBuilder parameter * * @param resultBuilder - document search result object getting search attributes added to it * @param searchAttributeStatement - statement being used to call the database for queries * @throws SQLException */ public void populateDocumentAttributesValues(DocumentSearchResult.Builder resultBuilder, Statement searchAttributeStatement) throws SQLException { searchAttributeStatement.setFetchSize(50); String documentId = resultBuilder.getDocument().getDocumentId(); List<SearchableAttributeValue> attributeValues = 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) { LOG.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 criteria, List<RemotableAttributeField> searchFields) { 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(), searchFields, getGeneratedPredicatePrefix(whereSQL.length())); selectSQL.append(queryComponent.getSelectSql()); fromSQL.append(queryComponent.getFromSql()); whereSQL.append(queryComponent.getWhereSql()); } whereSQL.append(getDocTypeFullNameWhereSql(criteria, getGeneratedPredicatePrefix(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 String statusTransitionWhereClause = getStatusTransitionDateSql( criteria.getDateApplicationDocumentStatusChangedFrom(), criteria.getDateApplicationDocumentStatusChangedTo(), getGeneratedPredicatePrefix(whereSQL.length())); List<String> applicationDocumentStatuses = 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(applicationDocumentStatuses, getGeneratedPredicatePrefix(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; LOG.info("*********** SEARCH SQL ***************"); LOG.info(finalizedSql); LOG.info("**************************************"); return finalizedSql; } public String getDocumentIdSql(String documentId, String whereClausePredicatePrefix, String 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", tableAlias, String.class, false, true); return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString(); } } public String getDocTitleSql(String docTitle, String 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", docTitle, String.class, true, true, crit); 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 appDocId, String 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 fromDateCreated, DateTime toDateCreated, String whereClausePredicatePrefix) { return establishDateString(fromDateCreated, toDateCreated, "KREW_DOC_HDR_T", "DOC_HDR", "CRTE_DT", whereClausePredicatePrefix); } public String getDateApprovedSql(DateTime fromDateApproved, DateTime toDateApproved, String whereClausePredicatePrefix) { return establishDateString(fromDateApproved, toDateApproved, "KREW_DOC_HDR_T", "DOC_HDR", "APRV_DT", whereClausePredicatePrefix); } public String getDateFinalizedSql(DateTime fromDateFinalized, DateTime toDateFinalized, String whereClausePredicatePrefix) { return establishDateString(fromDateFinalized, toDateFinalized, "KREW_DOC_HDR_T", "DOC_HDR", "FNL_DT", whereClausePredicatePrefix); } public String getDateLastModifiedSql(DateTime fromDateLastModified, DateTime toDateLastModified, String whereClausePredicatePrefix) { return establishDateString(fromDateLastModified, toDateLastModified, "KREW_DOC_HDR_T", "DOC_HDR", "STAT_MDFN_DT", whereClausePredicatePrefix); } public String getStatusTransitionDateSql(DateTime fromStatusTransitionDate, DateTime toStatusTransitionDate, String whereClausePredicatePrefix) { return establishDateString(fromStatusTransitionDate, toStatusTransitionDate, "KREW_DOC_HDR_T", "DOC_HDR", "APP_DOC_STAT_MDFN_DT", whereClausePredicatePrefix); } public String getViewerSql(String viewer, String whereClausePredicatePrefix) { StringBuilder returnSql = new StringBuilder(); if (StringUtils.isNotBlank(viewer)) { Map<String, String> m = new HashMap<String, String>(); m.put("principalName", viewer); // This will search for people with the ability for the valid operands. List<Person> personList = KimApiServiceLocator.getPersonService().findPeople(m, false); List<String> principalList = 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", principalList, String.class); returnSql.append(whereClausePredicatePrefix + "( (DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and " + crit.buildWhere() + " )"); Set<String> viewerGroupIds = new TreeSet<String>(); if (CollectionUtils.isNotEmpty(principalList)) { for (String principalId : principalList) { 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 viewerId, String whereClausePredicatePrefix) { StringBuilder returnSql = new StringBuilder(); if (StringUtils.isNotBlank(viewerId)) { Map<String, String> m = new HashMap<String, String>(); m.put("principalId", viewerId); // This will search for people with the ability for the valid operands. List<Person> personList = KimApiServiceLocator.getPersonService().findPeople(m, false); List<String> principalList = 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", principalList, String.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 groupId, String 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 initiatorPrincipalName, String whereClausePredicatePrefix) { if (StringUtils.isBlank(initiatorPrincipalName)) { return ""; } String tableAlias = "DOC_HDR"; Map<String, String> m = new HashMap<String, String>(); m.put("principalName", initiatorPrincipalName); // This will search for people with the ability for the valid operands. List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false); List<String> principalList = 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 p : pList) { principalList.add(p.getPrincipalId()); } Criteria crit = new Criteria("KREW_DOC_HDR_T", tableAlias); crit.in("INITR_PRNCPL_ID", principalList, String.class); return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString(); } public String getInitiatorIdSql(String initiatorPrincipalId, String whereClausePredicatePrefix) { if (StringUtils.isBlank(initiatorPrincipalId)) { return ""; } String tableAlias = "DOC_HDR"; Map<String, String> m = new HashMap<String, String>(); m.put("principalId", initiatorPrincipalId); // This will search for people with the ability for the valid operands. List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false); List<String> principalList = 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 p : pList) { principalList.add(p.getPrincipalId()); } Criteria crit = new Criteria("KREW_DOC_HDR_T", tableAlias); crit.in("INITR_PRNCPL_ID", principalList, String.class); return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString(); } public String getApproverSql(String approver, String whereClausePredicatePrefix) { String returnSql = ""; if (StringUtils.isNotBlank(approver)) { Map<String, String> m = new HashMap<String, String>(); m.put("principalName", approver); // This will search for people with the ability for the valid operands. List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false); List<String> principalList = 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 p : pList) { principalList.add(p.getPrincipalId()); } Criteria crit = new Criteria("KREW_ACTN_TKN_T", "KREW_ACTN_TKN_T"); crit.in("PRNCPL_ID", principalList, String.class); returnSql = whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_ID = KREW_ACTN_TKN_T.DOC_HDR_ID and upper(KREW_ACTN_TKN_T.ACTN_CD) in ('" + KewApiConstants.ACTION_TAKEN_APPROVED_CD + "','" + KewApiConstants.ACTION_TAKEN_BLANKET_APPROVE_CD + "')" + " and " + crit.buildWhere(); } return returnSql; } public String getApproverIdSql(String approverId, String whereClausePredicatePrefix) { String returnSql = ""; if (StringUtils.isNotBlank(approverId)) { Map<String, String> m = new HashMap<String, String>(); m.put("principalId", approverId); // This will search for people with the ability for the valid operands. List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false); List<String> principalList = 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 p : pList) { principalList.add(p.getPrincipalId()); } Criteria crit = new Criteria("KREW_ACTN_TKN_T", "KREW_ACTN_TKN_T"); crit.in("PRNCPL_ID", principalList, String.class); returnSql = whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_ID = KREW_ACTN_TKN_T.DOC_HDR_ID and upper(KREW_ACTN_TKN_T.ACTN_CD) in ('" + KewApiConstants.ACTION_TAKEN_APPROVED_CD + "','" + KewApiConstants.ACTION_TAKEN_BLANKET_APPROVE_CD + "')" + " and " + crit.buildWhere(); } return returnSql; } public String getDocTypeFullNameWhereSql(DocumentSearchCriteria criteria, String whereClausePredicatePrefix) { List<String> documentTypeNamesToSearch = 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(returnSql, documentTypeName.trim(), clause); } else { addDocumentTypeNameToSearchOn(returnSql, documentTypeName.trim(), clause); } if (docType.getChildrenDocTypes() != null) { addChildDocumentTypes(returnSql, docType.getChildrenDocTypes()); } } else { addDocumentTypeLikeNameToSearchOn(returnSql, documentTypeName.trim(), clause); } } } } if (returnSql.length() > 0) { returnSql.insert(0, "("); returnSql.insert(0, whereClausePredicatePrefix); returnSql.append(")"); } return returnSql.toString(); } public void addChildDocumentTypes(StringBuilder whereSql, Collection<DocumentType> childDocumentTypes) { for (DocumentType child : childDocumentTypes) { addDocumentTypeNameToSearchOn(whereSql, child.getName()); addChildDocumentTypes(whereSql, child.getChildrenDocTypes()); } } public void addDocumentTypeNameToSearchOn(StringBuilder whereSql, String documentTypeName) { this.addDocumentTypeNameToSearchOn(whereSql, documentTypeName, " or "); } public void addDocumentTypeNameToSearchOn(StringBuilder whereSql, String documentTypeName, String clause) { whereSql.append(clause).append("upper(DOC1.DOC_TYP_NM) = '" + documentTypeName.toUpperCase() + "'"); } public void addDocumentTypeLikeNameToSearchOn(StringBuilder whereSql, String documentTypeName, String clause) { documentTypeName = documentTypeName.replace('*', '%'); whereSql.append(clause).append(" upper(DOC1.DOC_TYP_NM) LIKE '" + documentTypeName.toUpperCase() + "'"); } public String getDocRouteNodeSql(String documentTypeFullName, String routeNodeName, RouteNodeLookupLogic docRouteLevelLogic, String 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 = RouteNodeLookupLogic.EXACTLY; } StringBuilder routeNodeCriteria = new StringBuilder("and " + ROUTE_NODE_TABLE + ".NM "); if (RouteNodeLookupLogic.EXACTLY == 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<RouteNode> routeNodes = 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 && RouteNodeLookupLogic.BEFORE == docRouteLevelLogic) || (foundSpecifiedNode && RouteNodeLookupLogic.AFTER == 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 = " + ROUTE_NODE_INST_TABLE + ".DOC_HDR_ID and " + ROUTE_NODE_INST_TABLE + ".RTE_NODE_ID = " + ROUTE_NODE_TABLE + ".RTE_NODE_ID and " + ROUTE_NODE_INST_TABLE + ".ACTV_IND = 1 " + routeNodeCriteria.toString() + " "; } return returnSql; } public String getDocumentStatusSql(List<DocumentStatus> documentStatuses, List<DocumentStatusCategory> categories, String whereClausePredicatePrefix) { if (CollectionUtils.isEmpty(documentStatuses) && CollectionUtils.isEmpty(categories)) { return whereClausePredicatePrefix + "DOC_HDR.DOC_HDR_STAT_CD != '" + DocumentStatus.INITIATED.getCode() + "'"; } else { // include all given document statuses Set<DocumentStatus> statusesToInclude = new HashSet<DocumentStatus>(documentStatuses); // add all statuses from each category for (DocumentStatusCategory category : categories) { Set<DocumentStatus> categoryStatuses = DocumentStatus.getStatusesForCategory(category); statusesToInclude.addAll(categoryStatuses); } Set<String> statusCodes = 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<String> appDocStatuses, String whereClausePredicatePrefix, int 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<String> appDocStatuses) { 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(")"); return sql.toString(); } public String getGeneratedPredicatePrefix(int whereClauseSize) { return (whereClauseSize > 0) ? " and " : " where "; } public String establishDateString(DateTime fromDate, DateTime toDate, String tableName, String tableAlias, String colName, String whereStatementClause) { String fromDateValue = null; if (fromDate != null) { fromDateValue = CoreApiServiceLocator.getDateTimeService().toDateString(fromDate.toDate()); } String toDateValue = null; if (toDate != null) { toDateValue = CoreApiServiceLocator.getDateTimeService().toDateString(toDate.toDate()); toDateValue += " 23:59:59"; } String searchValue = null; if (fromDateValue != null && toDateValue != null) { searchValue = fromDateValue + " .. " + toDateValue; } else if (fromDateValue != null) { searchValue = ">= " + fromDateValue; } else if (toDateValue != null) { searchValue = "<= " + toDateValue; } else { return ""; } Criteria crit = getSqlBuilder().createCriteria(colName, searchValue, tableName, tableAlias, java.sql.Date.class, true, true); return new StringBuilder(whereStatementClause).append(crit.buildWhere()).toString(); } public DatabasePlatform getDbPlatform() { if (dbPlatform == null) { dbPlatform = (DatabasePlatform) GlobalResourceLoader.getService(RiceConstants.DB_PLATFORM); } return dbPlatform; } public SqlBuilder getSqlBuilder() { if (sqlBuilder == null) { sqlBuilder = new SqlBuilder(); sqlBuilder.setDbPlatform(getDbPlatform()); sqlBuilder.setDateTimeService(CoreApiServiceLocator.getDateTimeService()); } return this.sqlBuilder; } public void setSqlBuilder(SqlBuilder sqlBuilder) { this.sqlBuilder = sqlBuilder; } /** * A helper method for determining whether any searchable attributes are in use for the search. * * @return True if the search criteria contains at least one searchable attribute or the criteria's doc type name is * non-blank; false otherwise. */ protected boolean isUsingAtLeastOneSearchAttribute(DocumentSearchCriteria criteria) { return criteria.getDocumentAttributeValues().size() > 0 || StringUtils.isNotBlank(criteria.getDocumentTypeName()); } protected org.kuali.rice.kew.api.doctype.DocumentTypeService getApiDocumentTypeService() { if (apiDocumentTypeService == null) { apiDocumentTypeService = KewApiServiceLocator.getDocumentTypeService(); } return apiDocumentTypeService; } protected void setApiDocumentTypeService( org.kuali.rice.kew.api.doctype.DocumentTypeService apiDocumentTypeService) { this.apiDocumentTypeService = apiDocumentTypeService; } }