1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.rice.kew.impl.document.search;
17
18 import org.apache.commons.collections.CollectionUtils;
19 import org.apache.commons.lang.ArrayUtils;
20 import org.apache.commons.lang.StringUtils;
21 import org.joda.time.DateTime;
22 import org.kuali.rice.core.api.CoreApiServiceLocator;
23 import org.kuali.rice.core.api.resourceloader.GlobalResourceLoader;
24 import org.kuali.rice.core.api.uif.RemotableAttributeError;
25 import org.kuali.rice.core.api.uif.RemotableAttributeField;
26 import org.kuali.rice.core.api.util.RiceConstants;
27 import org.kuali.rice.core.api.util.RiceKeyConstants;
28 import org.kuali.rice.core.framework.persistence.jdbc.sql.Criteria;
29 import org.kuali.rice.core.framework.persistence.jdbc.sql.SqlBuilder;
30 import org.kuali.rice.core.framework.persistence.platform.DatabasePlatform;
31 import org.kuali.rice.kew.api.KewApiConstants;
32 import org.kuali.rice.kew.api.KewApiServiceLocator;
33 import org.kuali.rice.kew.api.document.Document;
34 import org.kuali.rice.kew.api.document.DocumentStatus;
35 import org.kuali.rice.kew.api.document.DocumentStatusCategory;
36 import org.kuali.rice.kew.api.document.attribute.DocumentAttribute;
37 import org.kuali.rice.kew.api.document.attribute.DocumentAttributeFactory;
38 import org.kuali.rice.kew.api.document.search.DocumentSearchCriteria;
39 import org.kuali.rice.kew.api.document.search.DocumentSearchResult;
40 import org.kuali.rice.kew.api.document.search.DocumentSearchResults;
41 import org.kuali.rice.kew.api.document.search.RouteNodeLookupLogic;
42 import org.kuali.rice.kew.docsearch.DocumentSearchInternalUtils;
43 import org.kuali.rice.kew.docsearch.QueryComponent;
44 import org.kuali.rice.kew.docsearch.SearchableAttributeValue;
45 import org.kuali.rice.kew.doctype.bo.DocumentType;
46 import org.kuali.rice.kew.doctype.service.DocumentTypeService;
47 import org.kuali.rice.kew.engine.node.RouteNode;
48 import org.kuali.rice.kew.service.KEWServiceLocator;
49 import org.kuali.rice.kew.util.PerformanceLogger;
50 import org.kuali.rice.kim.api.identity.Person;
51 import org.kuali.rice.kim.api.identity.principal.PrincipalContract;
52 import org.kuali.rice.kim.api.services.KimApiServiceLocator;
53 import org.kuali.rice.krad.util.GlobalVariables;
54 import org.kuali.rice.krad.util.MessageMap;
55
56 import java.sql.ResultSet;
57 import java.sql.SQLException;
58 import java.sql.Statement;
59 import java.sql.Timestamp;
60 import java.util.ArrayList;
61 import java.util.Arrays;
62 import java.util.Collection;
63 import java.util.Collections;
64 import java.util.HashMap;
65 import java.util.HashSet;
66 import java.util.List;
67 import java.util.Map;
68 import java.util.Set;
69 import java.util.TreeSet;
70
71
72
73
74
75
76
77 public class DocumentSearchGeneratorImpl implements DocumentSearchGenerator {
78
79 private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(DocumentSearchGeneratorImpl.class);
80
81 private static final String ROUTE_NODE_TABLE = "KREW_RTE_NODE_T";
82 private static final String ROUTE_NODE_INST_TABLE = "KREW_RTE_NODE_INSTN_T";
83 private static final String DATABASE_WILDCARD_CHARACTER_STRING = "%";
84 private static final char DATABASE_WILDCARD_CHARACTER = DATABASE_WILDCARD_CHARACTER_STRING.toCharArray()[0];
85
86 private DatabasePlatform dbPlatform;
87 private MessageMap messageMap;
88
89 private SqlBuilder sqlBuilder = null;
90
91 @Override
92 public DocumentSearchCriteria clearSearch(DocumentSearchCriteria criteria) {
93 return DocumentSearchCriteria.Builder.create().build();
94 }
95
96 public DocumentType getValidDocumentType(String documentTypeFullName) {
97 if (!org.apache.commons.lang.StringUtils.isEmpty(documentTypeFullName)) {
98 DocumentType documentType = KEWServiceLocator.getDocumentTypeService().findByNameCaseInsensitive(documentTypeFullName);
99 if (documentType == null) {
100 throw new RuntimeException("No Valid Document Type Found for document type name '" + documentTypeFullName + "'");
101 }
102 return documentType;
103 }
104 return null;
105 }
106
107 @Override
108 public List<RemotableAttributeError> validateSearchableAttributes(DocumentSearchCriteria.Builder criteria) {
109 List<RemotableAttributeError> errors = new ArrayList<RemotableAttributeError>();
110 DocumentType documentType = null;
111 try{
112 documentType = getValidDocumentType(criteria.getDocumentTypeName());
113 }catch(RuntimeException re){
114 errors.add(RemotableAttributeError.Builder.create("documentTypeName", re.getMessage()).build());
115 }
116
117 if (documentType != null) {
118 errors = KEWServiceLocator.getDocumentSearchCustomizationMediator().validateLookupFieldParameters(documentType, criteria.build());
119 } else {
120 criteria.setDocumentAttributeValues(new HashMap<String, List<String>>());
121 }
122 return errors == null ? Collections.<RemotableAttributeError>emptyList() : Collections.unmodifiableList(errors);
123 }
124
125 public QueryComponent getSearchableAttributeSql(Map<String, List<String>> documentAttributeValues, List<RemotableAttributeField> searchFields, String whereClausePredicatePrefix) {
126
127 StringBuilder fromSql = new StringBuilder();
128 StringBuilder whereSql = new StringBuilder();
129
130
131 Criteria finalCriteria = null;
132 int tableIndex = 1;
133 SqlBuilder sqlBuilder = this.getSqlBuilder();
134
135 for (String documentAttributeName : documentAttributeValues.keySet()) {
136 String documentAttributeNameForSQL = documentAttributeName;
137 if (documentAttributeName.contains(KewApiConstants.DOCUMENT_ATTRIBUTE_FIELD_PREFIX)) {
138 documentAttributeNameForSQL = documentAttributeName.replaceFirst(KewApiConstants.DOCUMENT_ATTRIBUTE_FIELD_PREFIX, "");
139 }
140 List<String> searchValues = documentAttributeValues.get(documentAttributeName);
141 if (CollectionUtils.isEmpty(searchValues)) {
142 continue;
143 }
144
145 String tableAlias = "EXT" + tableIndex;
146 RemotableAttributeField searchField = getSearchFieldByName(documentAttributeName, searchFields);
147 String tableName = DocumentSearchInternalUtils.getAttributeTableName(searchField);
148 boolean caseSensitive = DocumentSearchInternalUtils.isLookupCaseSensitive(searchField);
149
150 Criteria crit = null;
151
152 Class<?> dataTypeClass = DocumentSearchInternalUtils.getDataTypeClass(searchField);
153 if (searchValues.size() > 1) {
154
155 crit = new Criteria(tableName, tableAlias);
156 crit.setDbPlatform(sqlBuilder.getDbPlatform());
157 crit.in("VAL", searchValues, dataTypeClass);
158 } else {
159 crit = sqlBuilder.createCriteria("VAL", searchValues.get(0) , tableName, tableAlias, dataTypeClass, !caseSensitive);
160 }
161
162 sqlBuilder.addCriteria("KEY_CD", documentAttributeNameForSQL, String.class, false, false, crit);
163 sqlBuilder.andCriteria("DOC_HDR_ID", tableAlias + ".DOC_HDR_ID", "KREW_DOC_HDR_T", "DOC_HDR", SqlBuilder.JoinType.class, false, false, crit);
164
165 if (finalCriteria == null ){
166 finalCriteria = crit;
167 } else{
168 sqlBuilder.andCriteria(finalCriteria, crit);
169 }
170
171
172
173 String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
174 QueryComponent qc = generateSearchableAttributeSql(tableName, documentAttributeNameForSQL, whereClausePrefix, tableIndex);
175 fromSql.append(qc.getFromSql());
176 tableIndex++;
177 }
178
179 if (finalCriteria == null) {
180 return new QueryComponent("", "", "");
181 }
182
183 String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
184
185 return new QueryComponent("", fromSql.toString(), whereClausePrefix + " " + finalCriteria.buildWhere());
186 }
187
188 private RemotableAttributeField getSearchFieldByName(String fieldName, List<RemotableAttributeField> searchFields) {
189 for (RemotableAttributeField searchField : searchFields) {
190 if (searchField.getName().equals(fieldName)
191 || searchField.getName().equals(KewApiConstants.DOCUMENT_ATTRIBUTE_FIELD_PREFIX + fieldName)) {
192 return searchField;
193 }
194 }
195 throw new IllegalStateException("Failed to locate a RemotableAttributeField for fieldName=" + fieldName);
196 }
197
198 public QueryComponent generateSearchableAttributeSql(String tableName, String documentAttributeName, String whereSqlStarter,int tableIndex) {
199 String tableIdentifier = "EXT" + tableIndex;
200 QueryComponent joinSqlComponent = getSearchableAttributeJoinSql(tableName, tableIdentifier, whereSqlStarter, documentAttributeName);
201 return new QueryComponent("", joinSqlComponent.getFromSql(), joinSqlComponent.getWhereSql());
202 }
203
204 public QueryComponent getSearchableAttributeJoinSql(String tableName, String tableIdentifier, String whereSqlStarter, String attributeTableKeyColumnName) {
205 return new QueryComponent("", generateSearchableAttributeFromSql(tableName, tableIdentifier).toString(), generateSearchableAttributeWhereClauseJoin(whereSqlStarter, tableIdentifier, attributeTableKeyColumnName).toString());
206 }
207
208 public StringBuilder generateSearchableAttributeWhereClauseJoin(String whereSqlStarter,String tableIdentifier,String attributeTableKeyColumnName) {
209 StringBuilder whereSql = new StringBuilder(constructWhereClauseElement(whereSqlStarter, "DOC_HDR.DOC_HDR_ID", "=", getDbPlatform().escapeString(tableIdentifier + ".DOC_HDR_ID"), null, null));
210 whereSql.append(constructWhereClauseElement(" and ", tableIdentifier + ".KEY_CD", "=",
211 getDbPlatform().escapeString(attributeTableKeyColumnName), "'", "'"));
212 return whereSql;
213 }
214
215 public StringBuilder generateSearchableAttributeFromSql(String tableName, String tableIdentifier) {
216 if (StringUtils.isBlank(tableName)) {
217 throw new IllegalArgumentException("tableName was null or blank");
218 }
219 if (StringUtils.isBlank(tableIdentifier)) {
220 throw new IllegalArgumentException("tableIdentifier was null or blank");
221 }
222 StringBuilder fromSql = new StringBuilder();
223 fromSql.append(" ,").append(tableName).append(" ").append(getDbPlatform().escapeString(tableIdentifier)).append(" ");
224 return fromSql;
225 }
226
227 public StringBuilder constructWhereClauseElement(String clauseStarter,String queryTableColumnName,String operand,String valueToSearch,String valuePrefix,String valueSuffix) {
228 StringBuilder whereSql = new StringBuilder();
229 valuePrefix = (valuePrefix != null) ? valuePrefix : "";
230 valueSuffix = (valueSuffix != null) ? valueSuffix : "";
231 whereSql.append(" " + clauseStarter + " ").append(getDbPlatform().escapeString(queryTableColumnName)).append(" " + operand + " ").append(valuePrefix).append(valueToSearch).append(valueSuffix).append(" ");
232 return whereSql;
233 }
234
235 @Override
236 public DocumentSearchResults.Builder processResultSet(DocumentSearchCriteria criteria, boolean criteriaModified, Statement searchAttributeStatement, ResultSet resultSet, int maxResultCap, int fetchLimit) throws SQLException {
237 DocumentSearchCriteria.Builder criteriaBuilder = DocumentSearchCriteria.Builder.create(criteria);
238 DocumentSearchResults.Builder results = DocumentSearchResults.Builder.create(criteriaBuilder);
239 results.setCriteriaModified(criteriaModified);
240 int size = 0;
241 List<DocumentSearchResult.Builder> resultList = new ArrayList<DocumentSearchResult.Builder>();
242 results.setSearchResults(resultList);
243 Map<String, DocumentSearchResult.Builder> resultMap = new HashMap<String, DocumentSearchResult.Builder>();
244 PerformanceLogger perfLog = new PerformanceLogger();
245 int iteration = 0;
246 int startAt = (criteria.getStartAtIndex()==null) ? 0 : criteria.getStartAtIndex();
247 maxResultCap += startAt;
248 boolean resultSetHasNext = resultSet.next();
249 while ( resultSetHasNext && resultMap.size() < maxResultCap && iteration++ < fetchLimit && startAt >= 0) {
250
251 if(iteration <= startAt) {
252 resultSetHasNext = resultSet.next();
253 continue;
254 }
255
256 DocumentSearchResult.Builder resultBuilder = processRow(criteria, searchAttributeStatement, resultSet);
257 String documentId = resultBuilder.getDocument().getDocumentId();
258 if (!resultMap.containsKey(documentId)) {
259 resultList.add(resultBuilder);
260 resultMap.put(documentId, resultBuilder);
261 size++;
262 } else {
263
264 DocumentSearchResult.Builder previousEntry = resultMap.get(documentId);
265 handleMultipleDocumentRows(previousEntry, resultBuilder);
266 }
267 resultSetHasNext = resultSet.next();
268 }
269
270 perfLog.log("Time to read doc search results.", true);
271
272 results.setOverThreshold(resultSetHasNext);
273
274 LOG.debug("Processed "+size+" document search result rows.");
275 return results;
276 }
277
278
279
280
281
282
283
284
285 private void handleMultipleDocumentRows(DocumentSearchResult.Builder existingRow, DocumentSearchResult.Builder newRow) {
286 for (DocumentAttribute.AbstractBuilder<?> newDocumentAttribute : newRow.getDocumentAttributes()) {
287 existingRow.getDocumentAttributes().add(newDocumentAttribute);
288 }
289 }
290
291
292
293
294
295
296
297
298
299 protected DocumentSearchResult.Builder processRow(DocumentSearchCriteria criteria, Statement searchAttributeStatement, ResultSet rs) throws SQLException {
300
301 String documentId = rs.getString("DOC_HDR_ID");
302 String initiatorPrincipalId = rs.getString("INITR_PRNCPL_ID");
303 String documentTypeName = rs.getString("DOC_TYP_NM");
304 org.kuali.rice.kew.api.doctype.DocumentType documentType =
305 KewApiServiceLocator.getDocumentTypeService().getDocumentTypeByName(documentTypeName);
306 if (documentType == null) {
307 throw new IllegalStateException("Failed to locate a document type with the given name: " + documentTypeName);
308 }
309 String documentTypeId = documentType.getId();
310
311 Document.Builder documentBuilder = Document.Builder.create(documentId, initiatorPrincipalId, documentTypeName, documentTypeId);
312 DocumentSearchResult.Builder resultBuilder = DocumentSearchResult.Builder.create(documentBuilder);
313
314 String statusCode = rs.getString("DOC_HDR_STAT_CD");
315 Timestamp createTimestamp = rs.getTimestamp("CRTE_DT");
316 String title = rs.getString("TTL");
317 String applicationDocumentStatus = rs.getString("APP_DOC_STAT");
318
319 documentBuilder.setStatus(DocumentStatus.fromCode(statusCode));
320 documentBuilder.setDateCreated(new DateTime(createTimestamp.getTime()));
321 documentBuilder.setTitle(title);
322 documentBuilder.setApplicationDocumentStatus(applicationDocumentStatus);
323 documentBuilder.setApplicationDocumentStatusDate(new DateTime(rs.getTimestamp("APP_DOC_STAT_MDFN_DT")));
324 documentBuilder.setDateApproved(new DateTime(rs.getTimestamp("APRV_DT")));
325 documentBuilder.setDateFinalized(new DateTime(rs.getTimestamp("FNL_DT")));
326 documentBuilder.setApplicationDocumentId(rs.getString("APP_DOC_ID"));
327 documentBuilder.setDateLastModified(new DateTime(rs.getTimestamp("STAT_MDFN_DT")));
328 documentBuilder.setRoutedByPrincipalId(rs.getString("RTE_PRNCPL_ID"));
329
330
331 documentBuilder.setDocumentHandlerUrl(rs.getString("DOC_HDLR_URL"));
332
333 if (isUsingAtLeastOneSearchAttribute(criteria)) {
334 populateDocumentAttributesValues(resultBuilder, searchAttributeStatement);
335 }
336
337 return resultBuilder;
338 }
339
340
341
342
343
344
345
346
347
348 public void populateDocumentAttributesValues(DocumentSearchResult.Builder resultBuilder, Statement searchAttributeStatement) throws SQLException {
349 searchAttributeStatement.setFetchSize(50);
350 String documentId = resultBuilder.getDocument().getDocumentId();
351 List<SearchableAttributeValue> attributeValues = DocumentSearchInternalUtils
352 .getSearchableAttributeValueObjectTypes();
353 PerformanceLogger perfLog = new PerformanceLogger(documentId);
354 for (SearchableAttributeValue searchAttValue : attributeValues) {
355 String attributeSql = "select KEY_CD, VAL from " + searchAttValue.getAttributeTableName() + " where DOC_HDR_ID = '" + documentId + "'";
356 ResultSet attributeResultSet = null;
357 try {
358 attributeResultSet = searchAttributeStatement.executeQuery(attributeSql);
359 while (attributeResultSet.next()) {
360 searchAttValue.setSearchableAttributeKey(attributeResultSet.getString("KEY_CD"));
361 searchAttValue.setupAttributeValue(attributeResultSet, "VAL");
362 if ( (!org.apache.commons.lang.StringUtils.isEmpty(searchAttValue.getSearchableAttributeKey())) && (searchAttValue.getSearchableAttributeValue() != null) ) {
363 DocumentAttribute documentAttribute = searchAttValue.toDocumentAttribute();
364 resultBuilder.getDocumentAttributes().add(DocumentAttributeFactory.loadContractIntoBuilder(
365 documentAttribute));
366 }
367 }
368 } finally {
369 if (attributeResultSet != null) {
370 try {
371 attributeResultSet.close();
372 } catch (Exception e) {
373 LOG.warn("Could not close searchable attribute result set for class " + searchAttValue.getClass().getName(),e);
374 }
375 }
376 }
377 }
378 perfLog.log("Time to execute doc search search attribute queries.", true);
379 }
380
381 public String generateSearchSql(DocumentSearchCriteria criteria, List<RemotableAttributeField> searchFields) {
382
383 String docTypeTableAlias = "DOC1";
384 String docHeaderTableAlias = "DOC_HDR";
385
386 String sqlPrefix = "Select * from (";
387 String sqlSuffix = ") FINAL_SEARCH order by FINAL_SEARCH.CRTE_DT desc";
388
389
390 StringBuilder selectSQL = new StringBuilder("select DISTINCT("+ docHeaderTableAlias +".DOC_HDR_ID), "
391 + StringUtils.join(new String[] {
392 docHeaderTableAlias + ".INITR_PRNCPL_ID",
393 docHeaderTableAlias + ".DOC_HDR_STAT_CD",
394 docHeaderTableAlias + ".CRTE_DT",
395 docHeaderTableAlias + ".TTL",
396 docHeaderTableAlias + ".APP_DOC_STAT",
397 docHeaderTableAlias + ".STAT_MDFN_DT",
398 docHeaderTableAlias + ".APRV_DT",
399 docHeaderTableAlias + ".FNL_DT",
400 docHeaderTableAlias + ".APP_DOC_ID",
401 docHeaderTableAlias + ".RTE_PRNCPL_ID",
402 docHeaderTableAlias + ".APP_DOC_STAT_MDFN_DT",
403 docTypeTableAlias + ".DOC_TYP_NM",
404 docTypeTableAlias + ".LBL",
405 docTypeTableAlias + ".DOC_HDLR_URL",
406 docTypeTableAlias + ".ACTV_IND"
407 }, ", "));
408 StringBuilder fromSQL = new StringBuilder(" from KREW_DOC_TYP_T "+ docTypeTableAlias +" ");
409 StringBuilder fromSQLForDocHeaderTable = new StringBuilder(", KREW_DOC_HDR_T " + docHeaderTableAlias + " ");
410
411 StringBuilder whereSQL = new StringBuilder();
412 whereSQL.append(getDocumentIdSql(criteria.getDocumentId(), getGeneratedPredicatePrefix(whereSQL.length()), docHeaderTableAlias));
413 whereSQL.append(getInitiatorSql(criteria.getInitiatorPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length())));
414 whereSQL.append(getAppDocIdSql(criteria.getApplicationDocumentId(), getGeneratedPredicatePrefix(whereSQL.length())));
415 whereSQL.append(getDateCreatedSql(criteria.getDateCreatedFrom(), criteria.getDateCreatedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
416 whereSQL.append(getDateLastModifiedSql(criteria.getDateLastModifiedFrom(), criteria.getDateLastModifiedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
417 whereSQL.append(getDateApprovedSql(criteria.getDateApprovedFrom(), criteria.getDateApprovedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
418 whereSQL.append(getDateFinalizedSql(criteria.getDateFinalizedFrom(), criteria.getDateFinalizedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
419
420
421 String principalViewerSql = getViewerSql(criteria.getViewerPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length()));
422 String groupViewerSql = getGroupViewerSql(criteria.getGroupViewerId(), getGeneratedPredicatePrefix(whereSQL.length()));
423 if (StringUtils.isNotBlank(principalViewerSql) || StringUtils.isNotBlank(groupViewerSql)) {
424 whereSQL.append(principalViewerSql);
425 whereSQL.append(groupViewerSql);
426 fromSQL.append(", KREW_ACTN_RQST_T ");
427 }
428
429 if (!("".equals(getApproverSql(criteria.getApproverPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length()))))) {
430 whereSQL.append(getApproverSql(criteria.getApproverPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length())));
431 fromSQL.append(", KREW_ACTN_TKN_T ");
432 }
433
434
435
436 String docRouteNodeSql = getDocRouteNodeSql(criteria.getDocumentTypeName(), criteria.getRouteNodeName(), criteria.getRouteNodeLookupLogic(), getGeneratedPredicatePrefix(whereSQL.length()));
437 if (StringUtils.isNotBlank(docRouteNodeSql)) {
438 whereSQL.append(docRouteNodeSql);
439 fromSQL.append(", KREW_RTE_NODE_INSTN_T ");
440 fromSQL.append(", KREW_RTE_NODE_T ");
441 }
442
443 if (!criteria.getDocumentAttributeValues().isEmpty()) {
444 QueryComponent queryComponent = getSearchableAttributeSql(criteria.getDocumentAttributeValues(), searchFields, getGeneratedPredicatePrefix(
445 whereSQL.length()));
446 selectSQL.append(queryComponent.getSelectSql());
447 fromSQL.append(queryComponent.getFromSql());
448 whereSQL.append(queryComponent.getWhereSql());
449 }
450
451 whereSQL.append(getDocTypeFullNameWhereSql(criteria, getGeneratedPredicatePrefix(whereSQL.length())));
452 whereSQL.append(getDocTitleSql(criteria.getTitle(), getGeneratedPredicatePrefix(whereSQL.length())));
453 whereSQL.append(getDocumentStatusSql(criteria.getDocumentStatuses(), criteria.getDocumentStatusCategories(), getGeneratedPredicatePrefix(whereSQL.length())));
454 whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_TYP_ID = DOC1.DOC_TYP_ID ");
455 fromSQL.append(fromSQLForDocHeaderTable);
456
457
458 String statusTransitionWhereClause = getStatusTransitionDateSql(criteria.getDateApplicationDocumentStatusChangedFrom(), criteria.getDateApplicationDocumentStatusChangedTo(), getGeneratedPredicatePrefix(whereSQL.length()));
459 whereSQL.append(getAppDocStatusSql(criteria.getApplicationDocumentStatus(), getGeneratedPredicatePrefix(whereSQL.length()), statusTransitionWhereClause.length() ));
460 if (statusTransitionWhereClause.length() > 0){
461 whereSQL.append(statusTransitionWhereClause);
462 whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_HDR_ID = STAT_TRAN.DOC_HDR_ID ");
463 fromSQL.append(", KREW_APP_DOC_STAT_TRAN_T STAT_TRAN ");
464 }
465
466 String finalizedSql = sqlPrefix + " " + selectSQL.toString() + " " + fromSQL.toString() + " " + whereSQL.toString() + " " + sqlSuffix;
467
468 LOG.info("*********** SEARCH SQL ***************");
469 LOG.info(finalizedSql);
470 LOG.info("**************************************");
471 return finalizedSql;
472 }
473
474 public String getDocumentIdSql(String documentId, String whereClausePredicatePrefix, String tableAlias) {
475 if (StringUtils.isBlank(documentId)) {
476 return "";
477 } else {
478
479
480 Criteria crit = getSqlBuilder().createCriteria("DOC_HDR_ID", documentId, "KREW_DOC_HDR_T", tableAlias, String.class, false, true);
481 return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
482 }
483 }
484
485 public String getDocTitleSql(String docTitle, String whereClausePredicatePrefix) {
486 if (StringUtils.isBlank(docTitle)) {
487 return "";
488 } else {
489
490 docTitle = docTitle.trim().replace("\'", "\'\'");
491 SqlBuilder sqlBuild = new SqlBuilder();
492 Criteria crit = new Criteria("KREW_DOC_HDR_T", "DOC_HDR");
493 sqlBuild.addCriteria("TTL", docTitle, String.class, true, true, crit);
494 return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
495 }
496 }
497
498
499
500 public String getAppDocIdSql(String appDocId, String whereClausePredicatePrefix) {
501 if (StringUtils.isBlank(appDocId)) {
502 return "";
503 } else {
504 String tableAlias = "DOC_HDR";
505 Criteria crit = getSqlBuilder().createCriteria("APP_DOC_ID", appDocId, "KREW_DOC_HDR_T", tableAlias,String.class);
506 return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
507 }
508 }
509
510 public String getDateCreatedSql(DateTime fromDateCreated, DateTime toDateCreated, String whereClausePredicatePrefix) {
511 return establishDateString(fromDateCreated, toDateCreated, "KREW_DOC_HDR_T", "DOC_HDR", "CRTE_DT", whereClausePredicatePrefix);
512 }
513
514 public String getDateApprovedSql(DateTime fromDateApproved, DateTime toDateApproved, String whereClausePredicatePrefix) {
515 return establishDateString(fromDateApproved, toDateApproved, "KREW_DOC_HDR_T", "DOC_HDR", "APRV_DT", whereClausePredicatePrefix);
516 }
517
518 public String getDateFinalizedSql(DateTime fromDateFinalized, DateTime toDateFinalized, String whereClausePredicatePrefix) {
519 return establishDateString(fromDateFinalized, toDateFinalized, "KREW_DOC_HDR_T", "DOC_HDR", "FNL_DT", whereClausePredicatePrefix);
520 }
521
522 public String getDateLastModifiedSql(DateTime fromDateLastModified, DateTime toDateLastModified, String whereClausePredicatePrefix) {
523 return establishDateString(fromDateLastModified, toDateLastModified, "KREW_DOC_HDR_T", "DOC_HDR", "STAT_MDFN_DT", whereClausePredicatePrefix);
524 }
525
526 public String getStatusTransitionDateSql(DateTime fromStatusTransitionDate, DateTime toStatusTransitionDate, String whereClausePredicatePrefix) {
527 return establishDateString(fromStatusTransitionDate, toStatusTransitionDate, "KREW_DOC_HDR_T", "DOC_HDR", "APP_DOC_STAT_MDFN_DT", whereClausePredicatePrefix);
528 }
529
530 public String getViewerSql(String viewer, String whereClausePredicatePrefix) {
531 StringBuilder returnSql = new StringBuilder();
532 if (StringUtils.isNotBlank(viewer)) {
533 Map<String, String> m = new HashMap<String, String>();
534 m.put("principalName", viewer);
535
536
537 List<Person> personList = KimApiServiceLocator.getPersonService().findPeople(m, false);
538 List<String> principalList = new ArrayList<String>();
539
540 if(CollectionUtils.isEmpty(personList)) {
541
542
543 PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(viewer.trim());
544 if (tempPrincipal != null) {
545 principalList.add(tempPrincipal.getPrincipalId());
546 } else {
547
548
549 return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
550 }
551 }
552
553 for (Person person : personList){
554 principalList.add(person.getPrincipalId());
555 }
556
557 Criteria crit = new Criteria("KREW_ACTN_RQST_T", "KREW_ACTN_RQST_T");
558 crit.in("PRNCPL_ID", principalList, String.class);
559 returnSql.append(whereClausePredicatePrefix + "( (DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and " + crit.buildWhere() + " )");
560
561 Set<String> viewerGroupIds = new TreeSet<String>();
562
563 if(CollectionUtils.isNotEmpty(principalList)) {
564 for(String principalId: principalList){
565 viewerGroupIds.addAll(KimApiServiceLocator.getGroupService().getGroupIdsByPrincipalId(principalId));
566 }
567 }
568
569
570
571 if (viewerGroupIds != null && !viewerGroupIds.isEmpty()) {
572
573 returnSql.append(" or ( " +
574 "DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID " +
575 "and KREW_ACTN_RQST_T.GRP_ID in (");
576
577 boolean first = true;
578 for (String groupId : viewerGroupIds){
579 if(!first){
580 returnSql.append(",");
581 }
582 returnSql.append("'").append(groupId).append("'");
583 first = false;
584 }
585 returnSql.append("))");
586 }
587 returnSql.append(")");
588 }
589 return returnSql.toString();
590 }
591
592 public String getGroupViewerSql(String groupId, String whereClausePredicatePrefix) {
593 String sql = "";
594 if (StringUtils.isNotBlank(groupId)) {
595 sql = whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and KREW_ACTN_RQST_T.GRP_ID = '" + groupId + "'";
596 }
597 return sql;
598 }
599
600 public String getInitiatorSql(String initiatorPrincipalName, String whereClausePredicatePrefix) {
601
602 if (StringUtils.isBlank(initiatorPrincipalName)) {
603 return "";
604 }
605
606 String tableAlias = "DOC_HDR";
607
608 Map<String, String> m = new HashMap<String, String>();
609 m.put("principalName", initiatorPrincipalName);
610
611
612 List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
613 List<String> principalList = new ArrayList<String>();
614
615 if(pList == null || pList.isEmpty() ){
616
617
618 PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(initiatorPrincipalName.trim());
619 if (tempPrincipal != null) {
620 principalList.add(tempPrincipal.getPrincipalId());
621 } else {
622
623 return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
624 }
625 }
626
627 for(Person p: pList){
628 principalList.add(p.getPrincipalId());
629 }
630
631 Criteria crit = new Criteria("KREW_DOC_HDR_T", tableAlias);
632 crit.in("INITR_PRNCPL_ID", principalList, String.class);
633
634 return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
635 }
636
637 public String getApproverSql(String approver, String whereClausePredicatePrefix) {
638 String returnSql = "";
639 if (StringUtils.isNotBlank(approver)) {
640 Map<String, String> m = new HashMap<String, String>();
641 m.put("principalName", approver);
642
643
644 List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
645 List<String> principalList = new ArrayList<String>();
646
647 if(pList == null || pList.isEmpty() ){
648
649
650 PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(approver.trim());
651
652 if (tempPrincipal != null) {
653 principalList.add(tempPrincipal.getPrincipalId());
654 } else {
655
656 return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
657 }
658 }
659
660 for(Person p: pList){
661 principalList.add(p.getPrincipalId());
662 }
663
664 Criteria crit = new Criteria("KREW_ACTN_TKN_T", "KREW_ACTN_TKN_T");
665 crit.in("PRNCPL_ID", principalList, String.class);
666
667 returnSql = whereClausePredicatePrefix +
668 " DOC_HDR.DOC_HDR_ID = KREW_ACTN_TKN_T.DOC_HDR_ID and upper(KREW_ACTN_TKN_T.ACTN_CD) in ('" +
669 KewApiConstants.ACTION_TAKEN_APPROVED_CD + "','" + KewApiConstants.ACTION_TAKEN_BLANKET_APPROVE_CD + "')" +
670 " and " + crit.buildWhere();
671 }
672 return returnSql;
673 }
674
675 public String getDocTypeFullNameWhereSql(DocumentSearchCriteria criteria, String whereClausePredicatePrefix) {
676 List<String> documentTypeNamesToSearch = new ArrayList<String>();
677 String primaryDocumentTypeName = criteria.getDocumentTypeName();
678 if (StringUtils.isNotBlank(primaryDocumentTypeName)) {
679 documentTypeNamesToSearch.add(primaryDocumentTypeName);
680 }
681 documentTypeNamesToSearch.addAll(criteria.getAdditionalDocumentTypeNames());
682 StringBuilder returnSql = new StringBuilder("");
683 if (CollectionUtils.isNotEmpty(documentTypeNamesToSearch)) {
684 int index = 0;
685 for (String documentTypeName : documentTypeNamesToSearch) {
686 if (StringUtils.isNotBlank(documentTypeName)) {
687 String clause = index++ == 0 ? "" : " or ";
688 DocumentTypeService docSrv = KEWServiceLocator.getDocumentTypeService();
689 DocumentType docType = docSrv.findByNameCaseInsensitive(documentTypeName.trim());
690 if (docType != null) {
691 if (documentTypeName.contains("*") || documentTypeName.contains("%")) {
692 addDocumentTypeLikeNameToSearchOn(returnSql, documentTypeName.trim(), clause);
693 } else {
694 addDocumentTypeNameToSearchOn(returnSql, documentTypeName.trim(), clause);
695 }
696 if (docType.getChildrenDocTypes() != null) {
697 addChildDocumentTypes(returnSql, docType.getChildrenDocTypes());
698 }
699 } else{
700 addDocumentTypeLikeNameToSearchOn(returnSql, documentTypeName.trim(), clause);
701 }
702 }
703 }
704 }
705 if (returnSql.length() > 0) {
706 returnSql.insert(0, "(");
707 returnSql.insert(0, whereClausePredicatePrefix);
708 returnSql.append(")");
709 }
710 return returnSql.toString();
711 }
712
713 public void addChildDocumentTypes(StringBuilder whereSql, Collection<DocumentType> childDocumentTypes) {
714 for (DocumentType child : childDocumentTypes) {
715 addDocumentTypeNameToSearchOn(whereSql, child.getName());
716 addChildDocumentTypes(whereSql, child.getChildrenDocTypes());
717 }
718 }
719
720 public void addDocumentTypeNameToSearchOn(StringBuilder whereSql, String documentTypeName) {
721 this.addDocumentTypeNameToSearchOn(whereSql, documentTypeName, " or ");
722 }
723
724 public void addDocumentTypeNameToSearchOn(StringBuilder whereSql, String documentTypeName, String clause) {
725 whereSql.append(clause).append("upper(DOC1.DOC_TYP_NM) = '" + documentTypeName.toUpperCase() + "'");
726 }
727 public void addDocumentTypeLikeNameToSearchOn(StringBuilder whereSql, String documentTypeName, String clause) {
728 documentTypeName = documentTypeName.replace('*', '%');
729 whereSql.append(clause).append(" upper(DOC1.DOC_TYP_NM) LIKE '" + documentTypeName.toUpperCase() + "'");
730 }
731
732 public String getDocRouteNodeSql(String documentTypeFullName, String routeNodeName, RouteNodeLookupLogic docRouteLevelLogic, String whereClausePredicatePrefix) {
733
734
735 String returnSql = "";
736 if (StringUtils.isNotBlank(routeNodeName)) {
737 if (docRouteLevelLogic == null) {
738 docRouteLevelLogic = RouteNodeLookupLogic.EXACTLY;
739 }
740 StringBuilder routeNodeCriteria = new StringBuilder("and " + ROUTE_NODE_TABLE + ".NM ");
741 if (RouteNodeLookupLogic.EXACTLY == docRouteLevelLogic) {
742 routeNodeCriteria.append("= '" + getDbPlatform().escapeString(routeNodeName) + "' ");
743 } else {
744 routeNodeCriteria.append("in (");
745
746 StringBuilder routeNodeInCriteria = new StringBuilder();
747 boolean foundSpecifiedNode = false;
748 List<RouteNode> routeNodes = KEWServiceLocator.getRouteNodeService().getFlattenedNodes(getValidDocumentType(documentTypeFullName), true);
749 for (RouteNode routeNode : routeNodes) {
750 if (routeNodeName.equals(routeNode.getRouteNodeName())) {
751
752 foundSpecifiedNode = true;
753 continue;
754 }
755
756
757
758 if ( (!foundSpecifiedNode && RouteNodeLookupLogic.BEFORE == docRouteLevelLogic) ||
759 (foundSpecifiedNode && RouteNodeLookupLogic.AFTER == docRouteLevelLogic) ) {
760 if (routeNodeInCriteria.length() > 0) {
761 routeNodeInCriteria.append(", ");
762 }
763 routeNodeInCriteria.append("'" + routeNode.getRouteNodeName() + "'");
764 }
765 }
766 if (routeNodeInCriteria.length() > 0) {
767 routeNodeCriteria.append(routeNodeInCriteria);
768 } else {
769 routeNodeCriteria.append("''");
770 }
771 routeNodeCriteria.append(") ");
772 }
773 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() + " ";
774 }
775 return returnSql;
776 }
777
778 public String getDocumentStatusSql(List<DocumentStatus> documentStatuses, List<DocumentStatusCategory> categories, String whereClausePredicatePrefix) {
779 if (CollectionUtils.isEmpty(documentStatuses) && CollectionUtils.isEmpty(categories)) {
780 return whereClausePredicatePrefix + "DOC_HDR.DOC_HDR_STAT_CD != '" + DocumentStatus.INITIATED.getCode() + "'";
781 } else {
782
783 Set<DocumentStatus> statusesToInclude = new HashSet<DocumentStatus>(documentStatuses);
784
785
786 for (DocumentStatusCategory category : categories) {
787 Set<DocumentStatus> categoryStatuses = DocumentStatus.getStatusesForCategory(category);
788 statusesToInclude.addAll(categoryStatuses);
789 }
790
791 Set<String> statusCodes = new HashSet<String>();
792 for (DocumentStatus statusToInclude : statusesToInclude) {
793 statusCodes.add("'" + getDbPlatform().escapeString(statusToInclude.getCode()) + "'");
794 }
795 return whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_STAT_CD in (" + StringUtils.join(statusCodes, ", ") +")";
796 }
797 }
798
799
800
801
802
803
804 public String getAppDocStatusSql(String appDocStatus, String whereClausePredicatePrefix, int statusTransitionWhereClauseLength) {
805 if (StringUtils.isBlank(appDocStatus)) {
806 return "";
807 } else {
808 if (statusTransitionWhereClauseLength > 0){
809 return whereClausePredicatePrefix + " STAT_TRAN.APP_DOC_STAT_TO = '" + getDbPlatform().escapeString(appDocStatus.trim()) + "'";
810 }else{
811 return whereClausePredicatePrefix + " DOC_HDR.APP_DOC_STAT = '" + getDbPlatform().escapeString(appDocStatus.trim()) + "'";
812 }
813 }
814 }
815
816 public String getGeneratedPredicatePrefix(int whereClauseSize) {
817 return (whereClauseSize > 0) ? " and " : " where ";
818 }
819
820 public String establishDateString(DateTime fromDate, DateTime toDate, String tableName, String tableAlias, String colName, String whereStatementClause) {
821
822 String fromDateValue = null;
823 if (fromDate != null) {
824 fromDateValue = CoreApiServiceLocator.getDateTimeService().toDateString(fromDate.toDate());
825 }
826
827 String toDateValue = null;
828 if (toDate != null) {
829 toDateValue = CoreApiServiceLocator.getDateTimeService().toDateString(toDate.toDate());
830 toDateValue += " 23:59:59";
831 }
832
833 String searchValue = null;
834 if (fromDateValue != null && toDateValue != null) {
835 searchValue = fromDateValue + " .. " + toDateValue;
836 } else if (fromDateValue != null) {
837 searchValue = ">= " + fromDateValue;
838 } else if (toDateValue != null) {
839 searchValue = "<= " + toDateValue;
840 } else {
841 return "";
842 }
843
844 Criteria crit = getSqlBuilder().createCriteria(colName, searchValue, tableName, tableAlias, java.sql.Date.class, true, true);
845 return new StringBuilder(whereStatementClause).append(crit.buildWhere()).toString();
846
847 }
848
849 public DatabasePlatform getDbPlatform() {
850 if (dbPlatform == null) {
851 dbPlatform = (DatabasePlatform) GlobalResourceLoader.getService(RiceConstants.DB_PLATFORM);
852 }
853 return dbPlatform;
854 }
855
856 public SqlBuilder getSqlBuilder() {
857 if(sqlBuilder == null){
858 sqlBuilder = new SqlBuilder();
859 sqlBuilder.setDbPlatform(getDbPlatform());
860 sqlBuilder.setDateTimeService(CoreApiServiceLocator.getDateTimeService());
861 }
862 return this.sqlBuilder;
863 }
864
865 public void setSqlBuilder(SqlBuilder sqlBuilder) {
866 this.sqlBuilder = sqlBuilder;
867 }
868
869
870
871
872
873
874
875 protected boolean isUsingAtLeastOneSearchAttribute(DocumentSearchCriteria criteria) {
876 return criteria.getDocumentAttributeValues().size() > 0 || StringUtils.isNotBlank(criteria.getDocumentTypeName());
877 }
878
879 }