001 /**
002 * Copyright 2005-2012 The Kuali Foundation
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package org.kuali.rice.kew.impl.document.search;
017
018 import org.apache.commons.collections.CollectionUtils;
019 import org.apache.commons.lang.StringUtils;
020 import org.joda.time.DateTime;
021 import org.kuali.rice.core.api.CoreApiServiceLocator;
022 import org.kuali.rice.core.api.resourceloader.GlobalResourceLoader;
023 import org.kuali.rice.core.api.uif.RemotableAttributeError;
024 import org.kuali.rice.core.api.uif.RemotableAttributeField;
025 import org.kuali.rice.core.api.util.RiceConstants;
026 import org.kuali.rice.core.api.util.RiceKeyConstants;
027 import org.kuali.rice.core.framework.persistence.jdbc.sql.Criteria;
028 import org.kuali.rice.core.framework.persistence.jdbc.sql.SqlBuilder;
029 import org.kuali.rice.core.framework.persistence.platform.DatabasePlatform;
030 import org.kuali.rice.kew.api.KewApiConstants;
031 import org.kuali.rice.kew.api.KewApiServiceLocator;
032 import org.kuali.rice.kew.api.document.Document;
033 import org.kuali.rice.kew.api.document.DocumentStatus;
034 import org.kuali.rice.kew.api.document.DocumentStatusCategory;
035 import org.kuali.rice.kew.api.document.attribute.DocumentAttribute;
036 import org.kuali.rice.kew.api.document.attribute.DocumentAttributeFactory;
037 import org.kuali.rice.kew.api.document.search.DocumentSearchCriteria;
038 import org.kuali.rice.kew.api.document.search.DocumentSearchResult;
039 import org.kuali.rice.kew.api.document.search.DocumentSearchResults;
040 import org.kuali.rice.kew.api.document.search.RouteNodeLookupLogic;
041 import org.kuali.rice.kew.docsearch.DocumentSearchInternalUtils;
042 import org.kuali.rice.kew.docsearch.QueryComponent;
043 import org.kuali.rice.kew.docsearch.SearchableAttributeValue;
044 import org.kuali.rice.kew.doctype.bo.DocumentType;
045 import org.kuali.rice.kew.doctype.service.DocumentTypeService;
046 import org.kuali.rice.kew.engine.node.RouteNode;
047 import org.kuali.rice.kew.service.KEWServiceLocator;
048 import org.kuali.rice.kew.util.PerformanceLogger;
049 import org.kuali.rice.kim.api.identity.Person;
050 import org.kuali.rice.kim.api.identity.principal.PrincipalContract;
051 import org.kuali.rice.kim.api.services.KimApiServiceLocator;
052 import org.kuali.rice.krad.util.GlobalVariables;
053 import org.kuali.rice.krad.util.MessageMap;
054
055 import java.sql.ResultSet;
056 import java.sql.SQLException;
057 import java.sql.Statement;
058 import java.sql.Timestamp;
059 import java.util.ArrayList;
060 import java.util.Collection;
061 import java.util.Collections;
062 import java.util.HashMap;
063 import java.util.HashSet;
064 import java.util.List;
065 import java.util.Map;
066 import java.util.Set;
067 import java.util.TreeSet;
068
069
070 /**
071 * Reference implementation of the {@code DocumentSearchGenerator}.
072 *
073 * @author Kuali Rice Team (rice.collab@kuali.org)
074 */
075 public class DocumentSearchGeneratorImpl implements DocumentSearchGenerator {
076
077 private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(DocumentSearchGeneratorImpl.class);
078
079 private static final String ROUTE_NODE_TABLE = "KREW_RTE_NODE_T";
080 private static final String ROUTE_NODE_INST_TABLE = "KREW_RTE_NODE_INSTN_T";
081 private static final String DATABASE_WILDCARD_CHARACTER_STRING = "%";
082 private static final char DATABASE_WILDCARD_CHARACTER = DATABASE_WILDCARD_CHARACTER_STRING.toCharArray()[0];
083
084 private DatabasePlatform dbPlatform;
085 private MessageMap messageMap;
086
087 private SqlBuilder sqlBuilder = null;
088
089 @Override
090 public DocumentSearchCriteria clearSearch(DocumentSearchCriteria criteria) {
091 return DocumentSearchCriteria.Builder.create().build();
092 }
093
094 public DocumentType getValidDocumentType(String documentTypeFullName) {
095 if (!org.apache.commons.lang.StringUtils.isEmpty(documentTypeFullName)) {
096 DocumentType documentType = KEWServiceLocator.getDocumentTypeService().findByName(documentTypeFullName);
097 if (documentType == null) {
098 throw new RuntimeException("No Valid Document Type Found for document type name '" + documentTypeFullName + "'");
099 }
100 return documentType;
101 }
102 return null;
103 }
104
105 @Override
106 public List<RemotableAttributeError> validateSearchableAttributes(DocumentSearchCriteria.Builder criteria) {
107 List<RemotableAttributeError> errors = new ArrayList<RemotableAttributeError>();
108 DocumentType documentType = null;
109 try{
110 documentType = getValidDocumentType(criteria.getDocumentTypeName());
111 }catch(RuntimeException re){
112 errors.add(RemotableAttributeError.Builder.create("documentTypeName", re.getMessage()).build());
113 }
114
115 if (documentType != null) {
116 errors = KEWServiceLocator.getDocumentSearchCustomizationMediator().validateLookupFieldParameters(documentType, criteria.build());
117 }
118 return errors == null ? Collections.<RemotableAttributeError>emptyList() : Collections.unmodifiableList(errors);
119 }
120
121 public QueryComponent getSearchableAttributeSql(Map<String, List<String>> documentAttributeValues, List<RemotableAttributeField> searchFields, String whereClausePredicatePrefix) {
122
123 StringBuilder fromSql = new StringBuilder();
124 StringBuilder whereSql = new StringBuilder();
125
126 //Map<String, List<SearchAttributeCriteriaComponent>> searchableAttributeRangeComponents = new HashMap<String,List<SearchAttributeCriteriaComponent>>();
127 Criteria finalCriteria = null;
128 int tableIndex = 1;
129 SqlBuilder sqlBuilder = this.getSqlBuilder();
130
131 for (String documentAttributeName : documentAttributeValues.keySet()) {
132
133 List<String> searchValues = documentAttributeValues.get(documentAttributeName);
134 if (CollectionUtils.isEmpty(searchValues)) {
135 continue;
136 }
137
138 String tableAlias = "EXT" + tableIndex;
139 RemotableAttributeField searchField = getSearchFieldByName(documentAttributeName, searchFields);
140 String tableName = DocumentSearchInternalUtils.getAttributeTableName(searchField);
141 boolean caseSensitive = DocumentSearchInternalUtils.isLookupCaseSensitive(searchField);
142
143 Criteria crit = null;
144
145 Class<?> dataTypeClass = DocumentSearchInternalUtils.getDataTypeClass(searchField);
146 if (searchValues.size() > 1) {
147 // if there's more than one entry, we need to do an "in"
148 crit = new Criteria(tableName, tableAlias);
149 crit.setDbPlatform(sqlBuilder.getDbPlatform());
150 crit.in("VAL", searchValues, dataTypeClass);
151 } else {
152 crit = sqlBuilder.createCriteria("VAL", searchValues.get(0) , tableName, tableAlias, dataTypeClass, !caseSensitive);
153 }
154 sqlBuilder.addCriteria("KEY_CD", documentAttributeName, String.class, false, false, crit); // this is always of type string.
155 sqlBuilder.andCriteria("DOC_HDR_ID", tableAlias + ".DOC_HDR_ID", "KREW_DOC_HDR_T", "DOC_HDR", SqlBuilder.JoinType.class, false, false, crit);
156
157 if (finalCriteria == null ){
158 finalCriteria = crit;
159 } else{
160 sqlBuilder.andCriteria(finalCriteria, crit);
161 }
162
163 // - below is the old code
164 // if where clause is empty then use passed in prefix... otherwise generate one
165 String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
166 QueryComponent qc = generateSearchableAttributeSql(tableName, documentAttributeName, whereClausePrefix, tableIndex);
167 fromSql.append(qc.getFromSql());
168 tableIndex++;
169 }
170
171 if (finalCriteria == null) {
172 return new QueryComponent("", "", "");
173 }
174
175 String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
176
177 return new QueryComponent("", fromSql.toString(), whereClausePrefix + " " + finalCriteria.buildWhere());
178 }
179
180 private RemotableAttributeField getSearchFieldByName(String fieldName, List<RemotableAttributeField> searchFields) {
181 for (RemotableAttributeField searchField : searchFields) {
182 if (searchField.getName().equals(fieldName)) {
183 return searchField;
184 }
185 }
186 throw new IllegalStateException("Failed to locate a RemotableAttributeField for fieldName=" + fieldName);
187 }
188
189 public QueryComponent generateSearchableAttributeSql(String tableName, String documentAttributeName, String whereSqlStarter,int tableIndex) {
190 String tableIdentifier = "EXT" + tableIndex;
191 QueryComponent joinSqlComponent = getSearchableAttributeJoinSql(tableName, tableIdentifier, whereSqlStarter, documentAttributeName);
192 return new QueryComponent("", joinSqlComponent.getFromSql(), joinSqlComponent.getWhereSql());
193 }
194
195 public QueryComponent getSearchableAttributeJoinSql(String tableName, String tableIdentifier, String whereSqlStarter, String attributeTableKeyColumnName) {
196 return new QueryComponent("", generateSearchableAttributeFromSql(tableName, tableIdentifier).toString(), generateSearchableAttributeWhereClauseJoin(whereSqlStarter, tableIdentifier, attributeTableKeyColumnName).toString());
197 }
198
199 public StringBuilder generateSearchableAttributeWhereClauseJoin(String whereSqlStarter,String tableIdentifier,String attributeTableKeyColumnName) {
200 StringBuilder whereSql = new StringBuilder(constructWhereClauseElement(whereSqlStarter, "DOC_HDR.DOC_HDR_ID", "=", getDbPlatform().escapeString(tableIdentifier + ".DOC_HDR_ID"), null, null));
201 whereSql.append(constructWhereClauseElement(" and ", tableIdentifier + ".KEY_CD", "=",
202 getDbPlatform().escapeString(attributeTableKeyColumnName), "'", "'"));
203 return whereSql;
204 }
205
206 public StringBuilder generateSearchableAttributeFromSql(String tableName, String tableIdentifier) {
207 if (StringUtils.isBlank(tableName)) {
208 throw new IllegalArgumentException("tableName was null or blank");
209 }
210 if (StringUtils.isBlank(tableIdentifier)) {
211 throw new IllegalArgumentException("tableIdentifier was null or blank");
212 }
213 StringBuilder fromSql = new StringBuilder();
214 fromSql.append(" ,").append(tableName).append(" ").append(getDbPlatform().escapeString(tableIdentifier)).append(" ");
215 return fromSql;
216 }
217
218 public StringBuilder constructWhereClauseElement(String clauseStarter,String queryTableColumnName,String operand,String valueToSearch,String valuePrefix,String valueSuffix) {
219 StringBuilder whereSql = new StringBuilder();
220 valuePrefix = (valuePrefix != null) ? valuePrefix : "";
221 valueSuffix = (valueSuffix != null) ? valueSuffix : "";
222 whereSql.append(" " + clauseStarter + " ").append(getDbPlatform().escapeString(queryTableColumnName)).append(" " + operand + " ").append(valuePrefix).append(valueToSearch).append(valueSuffix).append(" ");
223 return whereSql;
224 }
225
226 @Override
227 public DocumentSearchResults.Builder processResultSet(DocumentSearchCriteria criteria, boolean criteriaModified, Statement searchAttributeStatement, ResultSet resultSet, int maxResultCap, int fetchLimit) throws SQLException {
228 DocumentSearchCriteria.Builder criteriaBuilder = DocumentSearchCriteria.Builder.create(criteria);
229 DocumentSearchResults.Builder results = DocumentSearchResults.Builder.create(criteriaBuilder);
230 results.setCriteriaModified(criteriaModified);
231 int size = 0;
232 List<DocumentSearchResult.Builder> resultList = new ArrayList<DocumentSearchResult.Builder>();
233 results.setSearchResults(resultList);
234 Map<String, DocumentSearchResult.Builder> resultMap = new HashMap<String, DocumentSearchResult.Builder>();
235 PerformanceLogger perfLog = new PerformanceLogger();
236 int iteration = 0;
237 int startAt = (criteria.getStartAtIndex()==null) ? 0 : criteria.getStartAtIndex();
238 maxResultCap += startAt;
239 boolean resultSetHasNext = resultSet.next();
240 while ( resultSetHasNext && resultMap.size() < maxResultCap && iteration++ < fetchLimit && startAt >= 0) {
241
242 if(iteration <= startAt) {
243 resultSetHasNext = resultSet.next();
244 continue;
245 }
246
247 DocumentSearchResult.Builder resultBuilder = processRow(criteria, searchAttributeStatement, resultSet);
248 String documentId = resultBuilder.getDocument().getDocumentId();
249 if (!resultMap.containsKey(documentId)) {
250 resultList.add(resultBuilder);
251 resultMap.put(documentId, resultBuilder);
252 size++;
253 } else {
254 // handle duplicate rows with different search data
255 DocumentSearchResult.Builder previousEntry = resultMap.get(documentId);
256 handleMultipleDocumentRows(previousEntry, resultBuilder);
257 }
258 resultSetHasNext = resultSet.next();
259 }
260
261 perfLog.log("Time to read doc search results.", true);
262 // if we have threshold+1 results, then we have more results than we are going to display
263 results.setOverThreshold(resultSetHasNext);
264
265 LOG.debug("Processed "+size+" document search result rows.");
266 return results;
267 }
268
269 /**
270 * Handles multiple document rows by collapsing them into the list of document attributes on the existing row.
271 * The two rows must represent the same document.
272 *
273 * @param existingRow the existing row to combine the new row into
274 * @param newRow the new row from which to combine document attributes with the existing row
275 */
276 private void handleMultipleDocumentRows(DocumentSearchResult.Builder existingRow, DocumentSearchResult.Builder newRow) {
277 for (DocumentAttribute.AbstractBuilder<?> newDocumentAttribute : newRow.getDocumentAttributes()) {
278 existingRow.getDocumentAttributes().add(newDocumentAttribute);
279 }
280 }
281
282 protected DocumentSearchResult.Builder processRow(DocumentSearchCriteria criteria, Statement searchAttributeStatement, ResultSet rs) throws SQLException {
283
284 String documentId = rs.getString("DOC_HDR_ID");
285 String initiatorPrincipalId = rs.getString("INITR_PRNCPL_ID");
286 String documentTypeName = rs.getString("DOC_TYP_NM");
287 org.kuali.rice.kew.api.doctype.DocumentType documentType =
288 KewApiServiceLocator.getDocumentTypeService().getDocumentTypeByName(documentTypeName);
289 if (documentType == null) {
290 throw new IllegalStateException("Failed to locate a document type with the given name: " + documentTypeName);
291 }
292 String documentTypeId = documentType.getId();
293
294 Document.Builder documentBuilder = Document.Builder.create(documentId, initiatorPrincipalId, documentTypeName, documentTypeId);
295 DocumentSearchResult.Builder resultBuilder = DocumentSearchResult.Builder.create(documentBuilder);
296
297 String statusCode = rs.getString("DOC_HDR_STAT_CD");
298 Timestamp createTimestamp = rs.getTimestamp("CRTE_DT");
299 String title = rs.getString("TTL");
300 String applicationDocumentStatus = rs.getString("APP_DOC_STAT");
301
302 documentBuilder.setStatus(DocumentStatus.fromCode(statusCode));
303 documentBuilder.setDateCreated(new DateTime(createTimestamp.getTime()));
304 documentBuilder.setTitle(title);
305 documentBuilder.setApplicationDocumentStatus(applicationDocumentStatus);
306
307 // TODO - KULRICE-5755 - should probably set as many properties on the document as we can
308 documentBuilder.setDocumentHandlerUrl(rs.getString("DOC_HDLR_URL"));
309
310 if (isUsingAtLeastOneSearchAttribute(criteria)) {
311 populateDocumentAttributesValues(resultBuilder, searchAttributeStatement);
312 }
313
314 return resultBuilder;
315 }
316
317 /**
318 * This method performs searches against the search attribute value tables (see classes implementing
319 * {@link org.kuali.rice.kew.docsearch.SearchableAttributeValue}) to get data to fill in search attribute values on the given resultBuilder parameter
320 *
321 * @param resultBuilder - document search result object getting search attributes added to it
322 * @param searchAttributeStatement - statement being used to call the database for queries
323 * @throws SQLException
324 */
325 public void populateDocumentAttributesValues(DocumentSearchResult.Builder resultBuilder, Statement searchAttributeStatement) throws SQLException {
326 searchAttributeStatement.setFetchSize(50);
327 String documentId = resultBuilder.getDocument().getDocumentId();
328 List<SearchableAttributeValue> attributeValues = DocumentSearchInternalUtils
329 .getSearchableAttributeValueObjectTypes();
330 PerformanceLogger perfLog = new PerformanceLogger(documentId);
331 for (SearchableAttributeValue searchAttValue : attributeValues) {
332 String attributeSql = "select KEY_CD, VAL from " + searchAttValue.getAttributeTableName() + " where DOC_HDR_ID = '" + documentId + "'";
333 ResultSet attributeResultSet = null;
334 try {
335 attributeResultSet = searchAttributeStatement.executeQuery(attributeSql);
336 while (attributeResultSet.next()) {
337 searchAttValue.setSearchableAttributeKey(attributeResultSet.getString("KEY_CD"));
338 searchAttValue.setupAttributeValue(attributeResultSet, "VAL");
339 if ( (!org.apache.commons.lang.StringUtils.isEmpty(searchAttValue.getSearchableAttributeKey())) && (searchAttValue.getSearchableAttributeValue() != null) ) {
340 DocumentAttribute documentAttribute = searchAttValue.toDocumentAttribute();
341 resultBuilder.getDocumentAttributes().add(DocumentAttributeFactory.loadContractIntoBuilder(
342 documentAttribute));
343 }
344 }
345 } finally {
346 if (attributeResultSet != null) {
347 try {
348 attributeResultSet.close();
349 } catch (Exception e) {
350 LOG.warn("Could not close searchable attribute result set for class " + searchAttValue.getClass().getName(),e);
351 }
352 }
353 }
354 }
355 perfLog.log("Time to execute doc search search attribute queries.", true);
356 }
357
358 public String generateSearchSql(DocumentSearchCriteria criteria, List<RemotableAttributeField> searchFields) {
359
360 String docTypeTableAlias = "DOC1";
361 String docHeaderTableAlias = "DOC_HDR";
362
363 String sqlPrefix = "Select * from (";
364 String sqlSuffix = ") FINAL_SEARCH order by FINAL_SEARCH.CRTE_DT desc";
365 // the DISTINCT here is important as it filters out duplicate rows which could occur as the result of doc search extension values...
366 StringBuilder selectSQL = new StringBuilder("select DISTINCT("+ docHeaderTableAlias +".DOC_HDR_ID), "+ docHeaderTableAlias +".INITR_PRNCPL_ID, "
367 + docHeaderTableAlias +".DOC_HDR_STAT_CD, "+ docHeaderTableAlias +".CRTE_DT, "+ docHeaderTableAlias +".TTL, "+ docHeaderTableAlias +".APP_DOC_STAT, "+ docTypeTableAlias +".DOC_TYP_NM, "
368 + docTypeTableAlias +".LBL, "+ docTypeTableAlias +".DOC_HDLR_URL, "+ docTypeTableAlias +".ACTV_IND");
369 StringBuilder fromSQL = new StringBuilder(" from KREW_DOC_TYP_T "+ docTypeTableAlias +" ");
370 StringBuilder fromSQLForDocHeaderTable = new StringBuilder(", KREW_DOC_HDR_T " + docHeaderTableAlias + " ");
371
372 StringBuilder whereSQL = new StringBuilder();
373 whereSQL.append(getDocumentIdSql(criteria.getDocumentId(), getGeneratedPredicatePrefix(whereSQL.length()), docHeaderTableAlias));
374 whereSQL.append(getInitiatorSql(criteria.getInitiatorPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length())));
375 whereSQL.append(getAppDocIdSql(criteria.getApplicationDocumentId(), getGeneratedPredicatePrefix(whereSQL.length())));
376 whereSQL.append(getDateCreatedSql(criteria.getDateCreatedFrom(), criteria.getDateCreatedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
377 whereSQL.append(getDateLastModifiedSql(criteria.getDateLastModifiedFrom(), criteria.getDateLastModifiedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
378 whereSQL.append(getDateApprovedSql(criteria.getDateApprovedFrom(), criteria.getDateApprovedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
379 whereSQL.append(getDateFinalizedSql(criteria.getDateFinalizedFrom(), criteria.getDateFinalizedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
380
381 // flags for the table being added to the FROM class of the sql
382 String principalViewerSql = getViewerSql(criteria.getViewerPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length()));
383 String groupViewerSql = getGroupViewerSql(criteria.getGroupViewerId(), getGeneratedPredicatePrefix(whereSQL.length()));
384 if (StringUtils.isNotBlank(principalViewerSql) || StringUtils.isNotBlank(groupViewerSql)) {
385 whereSQL.append(principalViewerSql);
386 whereSQL.append(groupViewerSql);
387 fromSQL.append(", KREW_ACTN_RQST_T ");
388 }
389
390 if (!("".equals(getApproverSql(criteria.getApproverPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length()))))) {
391 whereSQL.append(getApproverSql(criteria.getApproverPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length())));
392 fromSQL.append(", KREW_ACTN_TKN_T ");
393 }
394
395
396
397 String docRouteNodeSql = getDocRouteNodeSql(criteria.getDocumentTypeName(), criteria.getRouteNodeName(), criteria.getRouteNodeLookupLogic(), getGeneratedPredicatePrefix(whereSQL.length()));
398 if (StringUtils.isNotBlank(docRouteNodeSql)) {
399 whereSQL.append(docRouteNodeSql);
400 fromSQL.append(", KREW_RTE_NODE_INSTN_T ");
401 fromSQL.append(", KREW_RTE_NODE_T ");
402 }
403
404 if (!criteria.getDocumentAttributeValues().isEmpty()) {
405 QueryComponent queryComponent = getSearchableAttributeSql(criteria.getDocumentAttributeValues(), searchFields, getGeneratedPredicatePrefix(
406 whereSQL.length()));
407 selectSQL.append(queryComponent.getSelectSql());
408 fromSQL.append(queryComponent.getFromSql());
409 whereSQL.append(queryComponent.getWhereSql());
410 }
411
412 whereSQL.append(getDocTypeFullNameWhereSql(criteria, getGeneratedPredicatePrefix(whereSQL.length())));
413 whereSQL.append(getDocTitleSql(criteria.getTitle(), getGeneratedPredicatePrefix(whereSQL.length())));
414 whereSQL.append(getDocumentStatusSql(criteria.getDocumentStatuses(), criteria.getDocumentStatusCategories(), getGeneratedPredicatePrefix(whereSQL.length())));
415 whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_TYP_ID = DOC1.DOC_TYP_ID ");
416 fromSQL.append(fromSQLForDocHeaderTable);
417
418 // App Doc Status Value and Transition clauses
419 String statusTransitionWhereClause = getStatusTransitionDateSql(criteria.getDateApplicationDocumentStatusChangedFrom(), criteria.getDateApplicationDocumentStatusChangedTo(), getGeneratedPredicatePrefix(whereSQL.length()));
420 whereSQL.append(getAppDocStatusSql(criteria.getApplicationDocumentStatus(), getGeneratedPredicatePrefix(whereSQL.length()), statusTransitionWhereClause.length() ));
421 if (statusTransitionWhereClause.length() > 0){
422 whereSQL.append(statusTransitionWhereClause);
423 whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_HDR_ID = STAT_TRAN.DOC_HDR_ID ");
424 fromSQL.append(", KREW_APP_DOC_STAT_TRAN_T STAT_TRAN ");
425 }
426
427 String finalizedSql = sqlPrefix + " " + selectSQL.toString() + " " + fromSQL.toString() + " " + whereSQL.toString() + " " + sqlSuffix;
428
429 LOG.info("*********** SEARCH SQL ***************");
430 LOG.info(finalizedSql);
431 LOG.info("**************************************");
432 return finalizedSql;
433 }
434
435 public String getDocumentIdSql(String documentId, String whereClausePredicatePrefix, String tableAlias) {
436 if (StringUtils.isBlank(documentId)) {
437 return "";
438 } else {
439 // Using true for caseInsensitive causes bad performance for MYSQL databases since function indexes cannot be added.
440 // Due to this, false is passed for caseInsensitive
441 Criteria crit = getSqlBuilder().createCriteria("DOC_HDR_ID", documentId, "KREW_DOC_HDR_T", tableAlias, String.class, false, true);
442 return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
443 }
444 }
445
446 public String getDocTitleSql(String docTitle, String whereClausePredicatePrefix) {
447 if (StringUtils.isBlank(docTitle)) {
448 return "";
449 } else {
450 // quick and dirty ' replacement that isn't the best but should work for all dbs
451 docTitle = docTitle.trim().replace("\'", "\'\'");
452 SqlBuilder sqlBuild = new SqlBuilder();
453 Criteria crit = new Criteria("KREW_DOC_HDR_T", "DOC_HDR");
454 sqlBuild.addCriteria("TTL", docTitle, String.class, true, true, crit);
455 return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
456 }
457 }
458
459 // special methods that return the sql needed to complete the search
460 // or nothing if the field was not filled in
461 public String getAppDocIdSql(String appDocId, String whereClausePredicatePrefix) {
462 if (StringUtils.isBlank(appDocId)) {
463 return "";
464 } else {
465 String tableAlias = "DOC_HDR";
466 Criteria crit = getSqlBuilder().createCriteria("APP_DOC_ID", appDocId, "KREW_DOC_HDR_T", tableAlias,String.class);
467 return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
468 }
469 }
470
471 public String getDateCreatedSql(DateTime fromDateCreated, DateTime toDateCreated, String whereClausePredicatePrefix) {
472 return establishDateString(fromDateCreated, toDateCreated, "KREW_DOC_HDR_T", "DOC_HDR", "CRTE_DT", whereClausePredicatePrefix);
473 }
474
475 public String getDateApprovedSql(DateTime fromDateApproved, DateTime toDateApproved, String whereClausePredicatePrefix) {
476 return establishDateString(fromDateApproved, toDateApproved, "KREW_DOC_HDR_T", "DOC_HDR", "APRV_DT", whereClausePredicatePrefix);
477 }
478
479 public String getDateFinalizedSql(DateTime fromDateFinalized, DateTime toDateFinalized, String whereClausePredicatePrefix) {
480 return establishDateString(fromDateFinalized, toDateFinalized, "KREW_DOC_HDR_T", "DOC_HDR", "FNL_DT", whereClausePredicatePrefix);
481 }
482
483 public String getDateLastModifiedSql(DateTime fromDateLastModified, DateTime toDateLastModified, String whereClausePredicatePrefix) {
484 return establishDateString(fromDateLastModified, toDateLastModified, "KREW_DOC_HDR_T", "DOC_HDR", "STAT_MDFN_DT", whereClausePredicatePrefix);
485 }
486
487 public String getStatusTransitionDateSql(DateTime fromStatusTransitionDate, DateTime toStatusTransitionDate, String whereClausePredicatePrefix) {
488 return establishDateString(fromStatusTransitionDate, toStatusTransitionDate, "KREW_DOC_HDR_T", "DOC_HDR", "APP_DOC_STAT_MDFN_DT", whereClausePredicatePrefix);
489 }
490
491 public String getViewerSql(String viewer, String whereClausePredicatePrefix) {
492 StringBuilder returnSql = new StringBuilder();
493 if (StringUtils.isNotBlank(viewer)) {
494 Map<String, String> m = new HashMap<String, String>();
495 m.put("principalName", viewer);
496
497 // This will search for people with the ability for the valid operands.
498 List<Person> personList = KimApiServiceLocator.getPersonService().findPeople(m, false);
499 List<String> principalList = new ArrayList<String>();
500
501 if(CollectionUtils.isEmpty(personList)) {
502 // findPeople allows for wildcards, but the person must be active. If no one was found,
503 // check for an exact inactive user.
504 PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(viewer.trim());
505 if (tempPrincipal != null) {
506 principalList.add(tempPrincipal.getPrincipalId());
507 } else {
508 // they entered something that returned nothing... so we should return nothing
509
510 return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
511 }
512 }
513
514 for (Person person : personList){
515 principalList.add(person.getPrincipalId());
516 }
517
518 Criteria crit = new Criteria("KREW_ACTN_RQST_T", "KREW_ACTN_RQST_T");
519 crit.in("PRNCPL_ID", principalList, String.class);
520 returnSql.append(whereClausePredicatePrefix + "( (DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and " + crit.buildWhere() + " )");
521
522 Set<String> viewerGroupIds = new TreeSet<String>();
523
524 if(CollectionUtils.isNotEmpty(principalList)) {
525 for(String principalId: principalList){
526 viewerGroupIds.addAll(KimApiServiceLocator.getGroupService().getGroupIdsByPrincipalId(principalId));
527 }
528 }
529
530 // Documents routed to users as part of a workgoup should be returned.
531 // Use Chad's escape stuff
532 if (viewerGroupIds != null && !viewerGroupIds.isEmpty()) {
533
534 returnSql.append(" or ( " +
535 "DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID " +
536 "and KREW_ACTN_RQST_T.GRP_ID in (");
537
538 boolean first = true;
539 for (String groupId : viewerGroupIds){
540 if(!first){
541 returnSql.append(",");
542 }
543 returnSql.append("'").append(groupId).append("'");
544 first = false;
545 }
546 returnSql.append("))");
547 }
548 returnSql.append(")");
549 }
550 return returnSql.toString();
551 }
552
553 public String getGroupViewerSql(String groupId, String whereClausePredicatePrefix) {
554 String sql = "";
555 if (StringUtils.isNotBlank(groupId)) {
556 sql = whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and KREW_ACTN_RQST_T.GRP_ID = '" + groupId + "'";
557 }
558 return sql;
559 }
560
561 public String getInitiatorSql(String initiatorPrincipalName, String whereClausePredicatePrefix) {
562
563 if (StringUtils.isBlank(initiatorPrincipalName)) {
564 return "";
565 }
566
567 String tableAlias = "DOC_HDR";
568
569 Map<String, String> m = new HashMap<String, String>();
570 m.put("principalName", initiatorPrincipalName);
571
572 // This will search for people with the ability for the valid operands.
573 List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
574 List<String> principalList = new ArrayList<String>();
575
576 if(pList == null || pList.isEmpty() ){
577 // findPeople allows for wildcards, but the person must be active. If no one was found,
578 // check for an exact inactive user.
579 PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(initiatorPrincipalName.trim());
580 if (tempPrincipal != null) {
581 principalList.add(tempPrincipal.getPrincipalId());
582 } else {
583 // they entered something that returned nothing... so we should return nothing
584 return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
585 }
586 }
587
588 for(Person p: pList){
589 principalList.add(p.getPrincipalId());
590 }
591
592 Criteria crit = new Criteria("KREW_DOC_HDR_T", tableAlias);
593 crit.in("INITR_PRNCPL_ID", principalList, String.class);
594
595 return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
596 }
597
598 public String getApproverSql(String approver, String whereClausePredicatePrefix) {
599 String returnSql = "";
600 if (StringUtils.isNotBlank(approver)) {
601 Map<String, String> m = new HashMap<String, String>();
602 m.put("principalName", approver);
603
604 // This will search for people with the ability for the valid operands.
605 List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
606 List<String> principalList = new ArrayList<String>();
607
608 if(pList == null || pList.isEmpty() ){
609 // findPeople allows for wildcards, but the person must be active. If no one was found,
610 // check for an exact inactive user.
611 PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(approver.trim());
612
613 if (tempPrincipal != null) {
614 principalList.add(tempPrincipal.getPrincipalId());
615 } else {
616 // they entered something that returned nothing... so we should return nothing
617 return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
618 }
619 }
620
621 for(Person p: pList){
622 principalList.add(p.getPrincipalId());
623 }
624
625 Criteria crit = new Criteria("KREW_ACTN_TKN_T", "KREW_ACTN_TKN_T");
626 crit.in("PRNCPL_ID", principalList, String.class);
627
628 returnSql = whereClausePredicatePrefix +
629 " DOC_HDR.DOC_HDR_ID = KREW_ACTN_TKN_T.DOC_HDR_ID and upper(KREW_ACTN_TKN_T.ACTN_CD) in ('" +
630 KewApiConstants.ACTION_TAKEN_APPROVED_CD + "','" + KewApiConstants.ACTION_TAKEN_BLANKET_APPROVE_CD + "')" +
631 " and " + crit.buildWhere();
632 }
633 return returnSql;
634 }
635
636 public String getDocTypeFullNameWhereSql(DocumentSearchCriteria criteria, String whereClausePredicatePrefix) {
637 List<String> documentTypeNamesToSearch = new ArrayList<String>();
638 String primaryDocumentTypeName = criteria.getDocumentTypeName();
639 if (StringUtils.isNotBlank(primaryDocumentTypeName)) {
640 documentTypeNamesToSearch.add(primaryDocumentTypeName);
641 }
642 documentTypeNamesToSearch.addAll(criteria.getAdditionalDocumentTypeNames());
643 StringBuilder returnSql = new StringBuilder("");
644 if (CollectionUtils.isNotEmpty(documentTypeNamesToSearch)) {
645 int index = 0;
646 for (String documentTypeName : documentTypeNamesToSearch) {
647 if (StringUtils.isNotBlank(documentTypeName)) {
648 String clause = index++ == 0 ? "" : " or ";
649 DocumentTypeService docSrv = KEWServiceLocator.getDocumentTypeService();
650 DocumentType docType = docSrv.findByName(documentTypeName.trim());
651 if (docType != null) {
652 addDocumentTypeNameToSearchOn(returnSql, documentTypeName.trim(), clause);
653 if (docType.getChildrenDocTypes() != null) {
654 addChildDocumentTypes(returnSql, docType.getChildrenDocTypes());
655 }
656 } else{
657 addDocumentTypeLikeNameToSearchOn(returnSql, documentTypeName.trim(), clause);
658 }
659 }
660 }
661 }
662 if (returnSql.length() > 0) {
663 returnSql.insert(0, "(");
664 returnSql.insert(0, whereClausePredicatePrefix);
665 returnSql.append(")");
666 }
667 return returnSql.toString();
668 }
669
670 public void addChildDocumentTypes(StringBuilder whereSql, Collection<DocumentType> childDocumentTypes) {
671 for (DocumentType child : childDocumentTypes) {
672 addDocumentTypeNameToSearchOn(whereSql, child.getName());
673 addChildDocumentTypes(whereSql, child.getChildrenDocTypes());
674 }
675 }
676
677 public void addDocumentTypeNameToSearchOn(StringBuilder whereSql, String documentTypeName) {
678 this.addDocumentTypeNameToSearchOn(whereSql, documentTypeName, " or ");
679 }
680
681 public void addDocumentTypeNameToSearchOn(StringBuilder whereSql, String documentTypeName, String clause) {
682 whereSql.append(clause).append(" DOC1.DOC_TYP_NM = '" + documentTypeName + "'");
683 }
684 public void addDocumentTypeLikeNameToSearchOn(StringBuilder whereSql, String documentTypeName, String clause) {
685 documentTypeName = documentTypeName.replace('*', '%');
686 whereSql.append(clause).append(" DOC1.DOC_TYP_NM LIKE '" + documentTypeName + "'");
687 }
688
689 public String getDocRouteNodeSql(String documentTypeFullName, String routeNodeName, RouteNodeLookupLogic docRouteLevelLogic, String whereClausePredicatePrefix) {
690 // -1 is the default 'blank' choice from the route node drop down a number is used because the ojb RouteNode object is used to
691 // render the node choices on the form.
692 String returnSql = "";
693 if (StringUtils.isNotBlank(routeNodeName)) {
694 if (docRouteLevelLogic == null) {
695 docRouteLevelLogic = RouteNodeLookupLogic.EXACTLY;
696 }
697 StringBuilder routeNodeCriteria = new StringBuilder("and " + ROUTE_NODE_TABLE + ".NM ");
698 if (RouteNodeLookupLogic.EXACTLY == docRouteLevelLogic) {
699 routeNodeCriteria.append("= '" + getDbPlatform().escapeString(routeNodeName) + "' ");
700 } else {
701 routeNodeCriteria.append("in (");
702 // below buffer used to facilitate the addition of the string ", " to separate out route node names
703 StringBuilder routeNodeInCriteria = new StringBuilder();
704 boolean foundSpecifiedNode = false;
705 List<RouteNode> routeNodes = KEWServiceLocator.getRouteNodeService().getFlattenedNodes(getValidDocumentType(documentTypeFullName), true);
706 for (RouteNode routeNode : routeNodes) {
707 if (routeNodeName.equals(routeNode.getRouteNodeName())) {
708 // current node is specified node so we ignore it outside of the boolean below
709 foundSpecifiedNode = true;
710 continue;
711 }
712 // below logic should be to add the current node to the criteria if we haven't found the specified node
713 // and the logic qualifier is 'route nodes before specified'... or we have found the specified node and
714 // the logic qualifier is 'route nodes after specified'
715 if ( (!foundSpecifiedNode && RouteNodeLookupLogic.BEFORE == docRouteLevelLogic) ||
716 (foundSpecifiedNode && RouteNodeLookupLogic.AFTER == docRouteLevelLogic) ) {
717 if (routeNodeInCriteria.length() > 0) {
718 routeNodeInCriteria.append(", ");
719 }
720 routeNodeInCriteria.append("'" + routeNode.getRouteNodeName() + "'");
721 }
722 }
723 if (routeNodeInCriteria.length() > 0) {
724 routeNodeCriteria.append(routeNodeInCriteria);
725 } else {
726 routeNodeCriteria.append("''");
727 }
728 routeNodeCriteria.append(") ");
729 }
730 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() + " ";
731 }
732 return returnSql;
733 }
734
735 public String getDocumentStatusSql(List<DocumentStatus> documentStatuses, List<DocumentStatusCategory> categories, String whereClausePredicatePrefix) {
736 if (CollectionUtils.isEmpty(documentStatuses) && CollectionUtils.isEmpty(categories)) {
737 return whereClausePredicatePrefix + "DOC_HDR.DOC_HDR_STAT_CD != '" + DocumentStatus.INITIATED.getCode() + "'";
738 } else {
739 // include all given document statuses
740 Set<DocumentStatus> statusesToInclude = new HashSet<DocumentStatus>(documentStatuses);
741
742 // add all statuses from each category
743 for (DocumentStatusCategory category : categories) {
744 Set<DocumentStatus> categoryStatuses = DocumentStatus.getStatusesForCategory(category);
745 statusesToInclude.addAll(categoryStatuses);
746 }
747
748 Set<String> statusCodes = new HashSet<String>();
749 for (DocumentStatus statusToInclude : statusesToInclude) {
750 statusCodes.add("'" + getDbPlatform().escapeString(statusToInclude.getCode()) + "'");
751 }
752 return whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_STAT_CD in (" + StringUtils.join(statusCodes, ", ") +")";
753 }
754 }
755
756 /**
757 * This method generates the where clause fragment related to Application Document Status.
758 * If the Status value only is defined, search for the appDocStatus value in the route header.
759 * If either the transition from/to dates are defined, search agains the status transition history.
760 */
761 public String getAppDocStatusSql(String appDocStatus, String whereClausePredicatePrefix, int statusTransitionWhereClauseLength) {
762 if (StringUtils.isBlank(appDocStatus)) {
763 return "";
764 } else {
765 if (statusTransitionWhereClauseLength > 0){
766 return whereClausePredicatePrefix + " STAT_TRAN.APP_DOC_STAT_TO = '" + getDbPlatform().escapeString(appDocStatus.trim()) + "'";
767 }else{
768 return whereClausePredicatePrefix + " DOC_HDR.APP_DOC_STAT = '" + getDbPlatform().escapeString(appDocStatus.trim()) + "'";
769 }
770 }
771 }
772
773 public String getGeneratedPredicatePrefix(int whereClauseSize) {
774 return (whereClauseSize > 0) ? " and " : " where ";
775 }
776
777 public String establishDateString(DateTime fromDate, DateTime toDate, String tableName, String tableAlias, String colName, String whereStatementClause) {
778
779 String fromDateValue = null;
780 if (fromDate != null) {
781 fromDateValue = CoreApiServiceLocator.getDateTimeService().toDateString(fromDate.toDate());
782 }
783
784 String toDateValue = null;
785 if (toDate != null) {
786 toDateValue = CoreApiServiceLocator.getDateTimeService().toDateString(toDate.toDate());
787 toDateValue += " 23:59:59";
788 }
789
790 String searchValue = null;
791 if (fromDateValue != null && toDateValue != null) {
792 searchValue = fromDateValue + " .. " + toDateValue;
793 } else if (fromDateValue != null) {
794 searchValue = ">= " + fromDateValue;
795 } else if (toDateValue != null) {
796 searchValue = "<= " + toDateValue;
797 } else {
798 return "";
799 }
800
801 Criteria crit = getSqlBuilder().createCriteria(colName, searchValue, tableName, tableAlias, java.sql.Date.class, true, true);
802 return new StringBuilder(whereStatementClause).append(crit.buildWhere()).toString();
803
804 }
805
806 public DatabasePlatform getDbPlatform() {
807 if (dbPlatform == null) {
808 dbPlatform = (DatabasePlatform) GlobalResourceLoader.getService(RiceConstants.DB_PLATFORM);
809 }
810 return dbPlatform;
811 }
812
813 public SqlBuilder getSqlBuilder() {
814 if(sqlBuilder == null){
815 sqlBuilder = new SqlBuilder();
816 sqlBuilder.setDbPlatform(getDbPlatform());
817 sqlBuilder.setDateTimeService(CoreApiServiceLocator.getDateTimeService());
818 }
819 return this.sqlBuilder;
820 }
821
822 public void setSqlBuilder(SqlBuilder sqlBuilder) {
823 this.sqlBuilder = sqlBuilder;
824 }
825
826 /**
827 * A helper method for determining whether any searchable attributes are in use for the search.
828 *
829 * @return True if the search criteria contains at least one searchable attribute or the criteria's doc type name is
830 * non-blank; false otherwise.
831 */
832 protected boolean isUsingAtLeastOneSearchAttribute(DocumentSearchCriteria criteria) {
833 return criteria.getDocumentAttributeValues().size() > 0 || StringUtils.isNotBlank(criteria.getDocumentTypeName());
834 }
835
836 }