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