1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.rice.kew.docsearch;
17
18 import org.apache.commons.collections.CollectionUtils;
19 import org.apache.commons.lang.StringUtils;
20 import org.kuali.rice.core.api.CoreApiServiceLocator;
21 import org.kuali.rice.core.api.exception.RiceRuntimeException;
22 import org.kuali.rice.core.api.resourceloader.GlobalResourceLoader;
23 import org.kuali.rice.core.api.search.SearchOperator;
24 import org.kuali.rice.core.api.uif.RemotableAttributeError;
25 import org.kuali.rice.core.api.util.RiceConstants;
26 import org.kuali.rice.core.api.util.RiceKeyConstants;
27 import org.kuali.rice.core.api.util.type.TypeUtils;
28 import org.kuali.rice.core.framework.persistence.jdbc.sql.Criteria;
29 import org.kuali.rice.core.framework.persistence.jdbc.sql.SQLUtils;
30 import org.kuali.rice.core.framework.persistence.jdbc.sql.SqlBuilder;
31 import org.kuali.rice.core.framework.persistence.platform.DatabasePlatform;
32 import org.kuali.rice.kew.api.WorkflowRuntimeException;
33 import org.kuali.rice.kew.doctype.SecuritySession;
34 import org.kuali.rice.kew.doctype.bo.DocumentType;
35 import org.kuali.rice.kew.doctype.service.DocumentTypeService;
36 import org.kuali.rice.kew.engine.node.RouteNode;
37 import org.kuali.rice.kew.exception.WorkflowServiceError;
38 import org.kuali.rice.kew.exception.WorkflowServiceErrorImpl;
39 import org.kuali.rice.kew.service.KEWServiceLocator;
40 import org.kuali.rice.kew.util.KEWConstants;
41 import org.kuali.rice.kew.util.PerformanceLogger;
42 import org.kuali.rice.kew.web.KeyValueSort;
43 import org.kuali.rice.kim.api.group.Group;
44 import org.kuali.rice.kim.api.identity.Person;
45 import org.kuali.rice.kim.api.identity.principal.EntityNamePrincipalName;
46 import org.kuali.rice.kim.api.services.KimApiServiceLocator;
47 import org.kuali.rice.krad.UserSession;
48 import org.kuali.rice.krad.util.GlobalVariables;
49 import org.kuali.rice.krad.util.KRADConstants;
50 import org.kuali.rice.krad.util.MessageMap;
51 import org.kuali.rice.krad.util.ObjectUtils;
52
53 import java.math.BigDecimal;
54 import java.sql.ResultSet;
55 import java.sql.SQLException;
56 import java.sql.Statement;
57 import java.sql.Timestamp;
58 import java.text.SimpleDateFormat;
59 import java.util.ArrayList;
60 import java.util.Arrays;
61 import java.util.Calendar;
62 import java.util.Collection;
63 import java.util.Collections;
64 import java.util.HashMap;
65 import java.util.HashSet;
66 import java.util.Iterator;
67 import java.util.List;
68 import java.util.Map;
69 import java.util.Set;
70 import java.util.TreeSet;
71
72
73
74
75
76
77 public class StandardDocumentSearchGenerator implements DocumentSearchGenerator {
78 private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(StandardDocumentSearchGenerator.class);
79
80 private static final String ROUTE_NODE_TABLE = "KREW_RTE_NODE_T";
81 private static final String ROUTE_NODE_INST_TABLE = "KREW_RTE_NODE_INSTN_T";
82 private static final String DATABASE_WILDCARD_CHARACTER_STRING = "%";
83 private static final char DATABASE_WILDCARD_CHARACTER = DATABASE_WILDCARD_CHARACTER_STRING.toCharArray()[0];
84
85 private static DocSearchCriteriaDTO criteria;
86 private static String searchingUser;
87
88 private boolean isProcessResultSet = true;
89
90 private DatabasePlatform dbPlatform;
91 private MessageMap messageMap;
92
93 private SqlBuilder sqlBuilder = null;
94
95 public DocSearchCriteriaDTO getCriteria() {
96 return criteria;
97 }
98
99 public void setCriteria(DocSearchCriteriaDTO criteria) {
100 StandardDocumentSearchGenerator.criteria = criteria;
101 }
102
103 public String getSearchingUser() {
104 return searchingUser;
105 }
106
107 public void setSearchingUser(String searchingUser) {
108 StandardDocumentSearchGenerator.searchingUser = searchingUser;
109 }
110
111 public DocSearchCriteriaDTO clearSearch(DocSearchCriteriaDTO searchCriteria) {
112 return new DocSearchCriteriaDTO();
113 }
114
115 public List<WorkflowServiceError> performPreSearchConditions(String principalId, DocSearchCriteriaDTO searchCriteria) {
116 setCriteria(searchCriteria);
117 return new ArrayList<WorkflowServiceError>();
118 }
119
120 public SearchAttributeCriteriaComponent getSearchableAttributeByFieldName(String name) {
121 if (StringUtils.isBlank(name)) {
122 throw new IllegalArgumentException("Attempted to find Searchable Attribute with blank Field name '" + name + "'");
123 }
124 for (SearchAttributeCriteriaComponent critComponent : getCriteria().getSearchableAttributes())
125 {
126
127 if (name.equals(critComponent.getFormKey()))
128 {
129 return critComponent;
130 }
131 }
132 return null;
133 }
134
135 public void addErrorMessageToList(List<WorkflowServiceError> errors, String message) {
136 errors.add(new WorkflowServiceErrorImpl(message,"general.message",message));
137 }
138
139
140
141
142 public String generateSearchSql(DocSearchCriteriaDTO searchCriteria) {
143 setCriteria(searchCriteria);
144 return getDocSearchSQL();
145 }
146
147 public DocumentType getValidDocumentType(String documentTypeFullName) {
148 if (!org.apache.commons.lang.StringUtils.isEmpty(documentTypeFullName)) {
149 DocumentType documentType = KEWServiceLocator.getDocumentTypeService().findByName(documentTypeFullName);
150 if (documentType == null) {
151 throw new RuntimeException("No Valid Document Type Found for document type name '" + documentTypeFullName + "'");
152 }
153 return documentType;
154 }
155 return null;
156 }
157
158 @Override
159 public List<RemotableAttributeError> validateSearchableAttributes(DocSearchCriteriaDTO searchCriteria) {
160 setCriteria(searchCriteria);
161 List<RemotableAttributeError> errors = new ArrayList<RemotableAttributeError>();
162 List<SearchAttributeCriteriaComponent> searchableAttributes = criteria.getSearchableAttributes();
163 if (searchableAttributes != null && !searchableAttributes.isEmpty()) {
164 Map<String, List<String>> paramMap = new HashMap<String, List<String>>();
165 for (SearchAttributeCriteriaComponent component : searchableAttributes) {
166 if (!CollectionUtils.isEmpty(component.getValues())) {
167 paramMap.put(component.getFormKey(), component.getValues());
168 } else {
169 paramMap.put(component.getFormKey(), Collections.singletonList(component.getValue()));
170 }
171 }
172
173 DocumentType documentType = getValidDocumentType(criteria.getDocTypeFullName());
174 if (documentType != null) {
175 errors = KEWServiceLocator.getDocumentLookupCustomizationMediator().validateSearchFieldParameters(documentType, paramMap);
176 }
177 }
178 return errors == null ? Collections.<RemotableAttributeError>emptyList() : Collections.unmodifiableList(errors);
179 }
180
181 private Class getSearchableAttributeClass(SearchableAttributeValue sav){
182 if(sav instanceof SearchableAttributeDateTimeValue){
183 return Timestamp.class;
184 }else if(sav instanceof SearchableAttributeFloatValue){
185 return Float.TYPE;
186 }else if(sav instanceof SearchableAttributeLongValue){
187 return Long.TYPE;
188 }else if(sav instanceof SearchableAttributeStringValue){
189 return String.class;
190 }else{
191 return null;
192 }
193 }
194
195
196
197
198
199
200 private static List<String> cleanUpperBounds(List<String> stringDates) {
201 List<String> lRet = null;
202 if(stringDates != null && !stringDates.isEmpty()){
203 lRet = new ArrayList<String>();
204 for(String stringDate:stringDates){
205 lRet.add(cleanUpperBound(stringDate));
206 }
207 }
208 return lRet;
209 }
210
211
212
213
214
215
216
217
218 private static String cleanUpperBound(String stringDate){
219 try{
220 java.sql.Timestamp dt = CoreApiServiceLocator.getDateTimeService().convertToSqlTimestamp(stringDate);
221 SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");
222
223 if("00:00:00".equals(sdfTime.format(dt))){
224 stringDate = stringDate + " 23:59:59";
225 }
226 } catch (Exception ex){
227 GlobalVariables.getMessageMap().putError(KRADConstants.DOCUMENT_ERRORS, RiceKeyConstants.ERROR_CUSTOM, new String[] { "Invalid Date Input: " + stringDate });
228 }
229 return stringDate;
230 }
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246 private static String parseAndConvertDateToRange(String propertyValue) {
247
248 String sRet = propertyValue;
249
250 if (StringUtils.contains(propertyValue, SearchOperator.BETWEEN.op())) {
251 String[] rangeValues = propertyValue.split("\\.\\.");
252 sRet = ObjectUtils.clean(rangeValues[0].trim()) + " " + SearchOperator.BETWEEN.op() + " " + cleanUpperBound(ObjectUtils.clean(rangeValues[1].trim()));
253 } else if (propertyValue.startsWith(SearchOperator.LESS_THAN_EQUAL.op())) {
254 sRet = SearchOperator.LESS_THAN_EQUAL + cleanUpperBound(ObjectUtils.clean(propertyValue));
255 } else if (propertyValue.startsWith(SearchOperator.LESS_THAN.op())) {
256 sRet = SearchOperator.LESS_THAN + cleanUpperBound(ObjectUtils.clean(propertyValue));
257 }
258
259 return sRet;
260 }
261
262
263
264
265
266
267
268
269
270
271
272
273
274 private void validateBounds(SearchAttributeCriteriaComponent lowerBound, SearchAttributeCriteriaComponent upperBound){
275
276 Class type = getSearchableAttributeClass(lowerBound.getSearchableAttributeValue());
277 Class upperType = getSearchableAttributeClass(upperBound.getSearchableAttributeValue());
278
279
280 if(type.getName().compareTo(upperType.getName()) != 0){
281 String err = "Type Mismatch. Must compare two like types";
282 LOG.error("validateBounds() " + err);
283 throw new RuntimeException(err);
284 }
285
286 String errorMsg = "The search attribute range values are out of order. Lower bound must be <= Upper Bound. ["
287 + lowerBound.getValue() + ", " + upperBound.getValue() + "] for type " + type.getName();
288
289 if(TypeUtils.isIntegralClass(type) || TypeUtils.isDecimalClass(type)){
290
291 BigDecimal lVal = SqlBuilder.stringToBigDecimal(lowerBound.getValue());
292 BigDecimal uVal = SqlBuilder.stringToBigDecimal(upperBound.getValue());
293
294 if(lVal.compareTo(uVal) > 0){
295 LOG.error("validateBounds() " + errorMsg);
296 throw new RuntimeException(errorMsg);
297 }
298
299 }else if(TypeUtils.isTemporalClass(type)){
300 java.sql.Timestamp lVal = null;
301 java.sql.Timestamp uVal = null;
302 try{
303 lVal = CoreApiServiceLocator.getDateTimeService().convertToSqlTimestamp(lowerBound.getValue());
304 uVal = CoreApiServiceLocator.getDateTimeService().convertToSqlTimestamp(upperBound.getValue());
305 }catch(Exception ex){
306 LOG.error("validateBounds() " + errorMsg);
307 throw new RuntimeException(errorMsg, ex);
308 }
309
310 if(lVal.compareTo(uVal) > 0){
311 LOG.error("validateBounds() " + errorMsg);
312 throw new RuntimeException(errorMsg);
313 }
314
315 }else if(TypeUtils.isStringClass(type)){
316
317
318 if(lowerBound.isCaseSensitive() != upperBound.isCaseSensitive()){
319 LOG.warn("validateBounds(): Cannot Validate because mismatch case sensitivity ["
320 + lowerBound.getValue() + ", " + upperBound.getValue() + "] for type " + type.getName());
321 }else if(lowerBound.isCaseSensitive()){
322 if(lowerBound.getValue().compareTo(upperBound.getValue()) > 0){
323 LOG.error("validateBounds() " + errorMsg);
324 throw new RuntimeException(errorMsg);
325 }
326 }else{
327 if(lowerBound.getValue().compareToIgnoreCase(upperBound.getValue()) > 0){
328 LOG.error("validateBounds() " + errorMsg);
329 throw new RuntimeException(errorMsg);
330 }
331 }
332 }
333 }
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350 private void combineAndFormatSearchableComponents(
351 List<SearchAttributeCriteriaComponent> searchableAttributes) {
352
353 Map<String, List<SearchAttributeCriteriaComponent>> searchableAttributeRangeComponents = new HashMap<String, List<SearchAttributeCriteriaComponent>>();
354
355 for (SearchAttributeCriteriaComponent criteriaComponent : searchableAttributes)
356 {
357 if (!criteriaComponent.isSearchable())
358 {
359 continue;
360 }
361
362 SearchableAttributeValue searchAttribute = criteriaComponent.getSearchableAttributeValue();
363 if (searchAttribute == null)
364 {
365
366 String errorMsg = "The search attribute value associated with key '"
367 + criteriaComponent.getSavedKey() + "' cannot be found";
368 LOG.error("getSearchableAttributeSql() " + errorMsg);
369 throw new RuntimeException(errorMsg);
370 }
371
372 Class clazz = getSearchableAttributeClass(searchAttribute);
373
374 if (criteriaComponent.isRangeSearch())
375 {
376
377 if (searchableAttributeRangeComponents.containsKey(criteriaComponent.getSavedKey()))
378 {
379 List<SearchAttributeCriteriaComponent> criteriaComponents = searchableAttributeRangeComponents.get(criteriaComponent.getSavedKey());
380 List<SearchAttributeCriteriaComponent> newCriteriaComponents = new ArrayList<SearchAttributeCriteriaComponent>();
381 newCriteriaComponents.addAll(criteriaComponents);
382 newCriteriaComponents.add(criteriaComponent);
383 searchableAttributeRangeComponents.put(criteriaComponent.getSavedKey(), newCriteriaComponents);
384 } else
385 {
386 searchableAttributeRangeComponents.put(criteriaComponent.getSavedKey(),
387 Arrays.asList(criteriaComponent)
388 );
389 }
390
391
392 if (TypeUtils.isTemporalClass(clazz) && criteriaComponent.isComponentUpperBoundValue())
393 {
394 criteriaComponent.setValue(cleanUpperBound(criteriaComponent.getValue()));
395 criteriaComponent.setValues(cleanUpperBounds(criteriaComponent.getValues()));
396 }
397
398 } else
399 {
400 if (TypeUtils.isTemporalClass(clazz))
401 {
402 criteriaComponent.setValue(criteriaComponent.getValue());
403 }
404 }
405 }
406
407
408
409 for (String keyName : searchableAttributeRangeComponents.keySet()) {
410 List<SearchAttributeCriteriaComponent> criteriaComponents = searchableAttributeRangeComponents
411 .get(keyName);
412
413 SearchAttributeCriteriaComponent newComp = null;
414 SearchAttributeCriteriaComponent lowerBound = null;
415 SearchAttributeCriteriaComponent upperBound = null;
416
417 for (SearchAttributeCriteriaComponent component : criteriaComponents) {
418 if (component.isComponentLowerBoundValue()) {
419 lowerBound = component;
420 } else if (component.isComponentUpperBoundValue()) {
421 upperBound = component;
422 } else {
423 String errorMsg = "The search attribute value associated with key '"
424 + component.getSavedKey()
425 + "' is not upper or lower bound";
426 LOG.error("getSearchableAttributeSql() " + errorMsg);
427 throw new RuntimeException(errorMsg);
428 }
429 }
430
431
432
433 if (lowerBound != null && upperBound != null) {
434
435
436 validateBounds(lowerBound, upperBound);
437
438
439
440
441
442 lowerBound.setRangeSearch(false);
443 upperBound.setRangeSearch(false);
444 if (lowerBound.isSearchInclusive()) {
445 lowerBound.setValue(SearchOperator.GREATER_THAN_EQUAL.op() + lowerBound.getValue());
446 } else {
447 lowerBound.setValue(SearchOperator.GREATER_THAN.op() + lowerBound.getValue());
448 }
449 if (upperBound.isSearchInclusive()) {
450 upperBound.setValue(SearchOperator.LESS_THAN_EQUAL.op() + upperBound.getValue());
451 } else {
452 upperBound.setValue(SearchOperator.LESS_THAN.op() + upperBound.getValue());
453 }
454
455 } else if (lowerBound != null) {
456 newComp = new SearchAttributeCriteriaComponent(lowerBound
457 .getFormKey(), null, false);
458 if (lowerBound.isSearchInclusive()) {
459 newComp.setValue(SearchOperator.GREATER_THAN_EQUAL.op() + lowerBound.getValue());
460 } else {
461 newComp.setValue(SearchOperator.GREATER_THAN.op() + lowerBound.getValue());
462 }
463 newComp.setSearchInclusive(lowerBound.isSearchInclusive());
464 newComp.setCaseSensitive(lowerBound.isCaseSensitive());
465 newComp.setAllowInlineRange(lowerBound.isAllowInlineRange());
466 newComp.setCanHoldMultipleValues(lowerBound
467 .isCanHoldMultipleValues());
468 newComp.setLookupableFieldType(lowerBound
469 .getLookupableFieldType());
470 newComp.setSearchable(true);
471 newComp.setSearchableAttributeValue(lowerBound
472 .getSearchableAttributeValue());
473 newComp.setSavedKey(lowerBound.getSavedKey());
474 searchableAttributes.add(newComp);
475 } else if (upperBound != null) {
476 newComp = new SearchAttributeCriteriaComponent(upperBound
477 .getFormKey(), null, false);
478 if (upperBound.isSearchInclusive()) {
479 newComp.setValue(SearchOperator.LESS_THAN_EQUAL.op() + upperBound.getValue());
480 } else {
481 newComp.setValue(SearchOperator.LESS_THAN.op() + upperBound.getValue());
482 }
483 newComp.setSearchInclusive(upperBound.isSearchInclusive());
484 newComp.setCaseSensitive(upperBound.isCaseSensitive());
485 newComp.setAllowInlineRange(upperBound.isAllowInlineRange());
486 newComp.setCanHoldMultipleValues(upperBound.isCanHoldMultipleValues());
487 newComp.setLookupableFieldType(upperBound.getLookupableFieldType());
488 newComp.setSearchable(true);
489 newComp.setSearchableAttributeValue(upperBound.getSearchableAttributeValue());
490 newComp.setSavedKey(upperBound.getSavedKey());
491 searchableAttributes.add(newComp);
492 }
493
494 }
495
496
497
498 for (Iterator<SearchAttributeCriteriaComponent> iterator = searchableAttributes.iterator(); iterator
499 .hasNext();) {
500 SearchAttributeCriteriaComponent criteriaComponent = (SearchAttributeCriteriaComponent) iterator
501 .next();
502 if (!criteriaComponent.isSearchable()) {
503 continue;
504 }
505
506 if (criteriaComponent.isRangeSearch()) {
507 iterator.remove();
508 }
509
510 }
511 }
512
513 public QueryComponent getSearchableAttributeSql(List<SearchAttributeCriteriaComponent> searchableAttributes, String whereClausePredicatePrefix) {
514
515
516
517
518
519
520
521 combineAndFormatSearchableComponents(searchableAttributes);
522
523 StringBuffer fromSql = new StringBuffer();
524 StringBuffer whereSql = new StringBuffer();
525
526 int tableIndex = 1;
527 String tableAlias = "EXT" + tableIndex;
528
529 Map<String, List<SearchAttributeCriteriaComponent>> searchableAttributeRangeComponents = new HashMap<String,List<SearchAttributeCriteriaComponent>>();
530 Criteria finalCriteria = null;
531
532 for (Iterator<SearchAttributeCriteriaComponent> iterator = searchableAttributes.iterator(); iterator.hasNext(); tableIndex++) {
533 SearchAttributeCriteriaComponent criteriaComponent = iterator.next();
534 if (!criteriaComponent.isSearchable()) {
535 continue;
536 }
537
538 SqlBuilder sqlBuild = this.getSqlBuilder();
539
540 SearchableAttributeValue searchAttribute = criteriaComponent.getSearchableAttributeValue();
541 if (searchAttribute == null) {
542
543 String errorMsg = "The search attribute value associated with key '" + criteriaComponent.getSavedKey() + "' cannot be found";
544 LOG.error("getSearchableAttributeSql() " + errorMsg);
545 throw new RuntimeException(errorMsg);
546 }
547
548 tableAlias = "EXT" + tableIndex;
549
550 Class c = getSearchableAttributeClass(searchAttribute);
551
552 boolean addCaseInsensitivityForValue = (!criteriaComponent.isCaseSensitive()) && criteriaComponent.getSearchableAttributeValue().allowsCaseInsensitivity();
553
554 Criteria crit = null;
555 List<String> searchValues = criteriaComponent.getValues();
556 if (searchValues != null && !searchValues.isEmpty()) {
557 crit = new Criteria(searchAttribute.getAttributeTableName(), tableAlias);
558 crit.setDbPlatform(sqlBuild.getDbPlatform());
559 crit.in("VAL", criteriaComponent.getValues(), c);
560 } else {
561 crit = sqlBuild.createCriteria("VAL", criteriaComponent.getValue() , searchAttribute.getAttributeTableName(), tableAlias, c, addCaseInsensitivityForValue, searchAttribute.allowsWildcards());
562 }
563 sqlBuild.addCriteria("KEY_CD", criteriaComponent.getSavedKey(), String.class, false, false, crit);
564 sqlBuild.andCriteria("DOC_HDR_ID", tableAlias + ".DOC_HDR_ID", "KREW_DOC_HDR_T", "DOC_HDR", SqlBuilder.JoinType.class, false, false, crit);
565
566 if(finalCriteria == null ){
567 finalCriteria = crit;
568 }else{
569 sqlBuild.andCriteria(finalCriteria, crit);
570 }
571
572
573
574 String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
575 QueryComponent qc = generateSearchableAttributeSql(criteriaComponent, whereClausePrefix, tableIndex);
576 fromSql.append(qc.getFromSql());
577
578 }
579
580 for (String keyName : searchableAttributeRangeComponents.keySet()) {
581 List<SearchAttributeCriteriaComponent> criteriaComponents = searchableAttributeRangeComponents.get(keyName);
582
583 String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
584 QueryComponent qc = generateSearchableAttributeRangeSql(keyName, criteriaComponents, whereClausePrefix, tableIndex);
585 fromSql.append(qc.getFromSql());
586 }
587
588 if (finalCriteria == null) {
589 return new QueryComponent("", "", "");
590 }
591
592 String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
593
594 return new QueryComponent("",fromSql.toString(),whereClausePrefix + " "+ finalCriteria.buildWhere());
595 }
596
597 public QueryComponent generateSearchableAttributeSql(SearchAttributeCriteriaComponent criteriaComponent,String whereSqlStarter,int tableIndex) {
598 String tableIdentifier = "EXT" + tableIndex;
599 String queryTableColumnName = tableIdentifier + ".VAL";
600 QueryComponent joinSqlComponent = getSearchableAttributeJoinSql(criteriaComponent.getSearchableAttributeValue(), tableIdentifier, whereSqlStarter, criteriaComponent.getSavedKey());
601 StringBuffer fromSql = new StringBuffer(joinSqlComponent.getFromSql());
602 StringBuffer whereSql = new StringBuffer(joinSqlComponent.getWhereSql());
603
604
605
606
607 return new QueryComponent("",fromSql.toString(),whereSql.toString());
608 }
609
610 public QueryComponent generateSearchableAttributeRangeSql(String searchAttributeKeyName, List<SearchAttributeCriteriaComponent> criteriaComponents,String whereSqlStarter,int tableIndex) {
611 StringBuffer fromSql = new StringBuffer();
612 StringBuffer whereSql = new StringBuffer();
613 boolean joinAlreadyPerformed = false;
614 String tableIdentifier = "EXT" + tableIndex;
615 String queryTableColumnName = tableIdentifier + ".VAL";
616
617 for (SearchAttributeCriteriaComponent criteriaComponent : criteriaComponents) {
618 if (!searchAttributeKeyName.equals(criteriaComponent.getSavedKey())) {
619 String errorMsg = "Key value of searchable attribute component with savedKey '" + criteriaComponent.getSavedKey() + "' does not match specified savedKey value '" + searchAttributeKeyName + "'";
620 LOG.error("generateSearchableAttributeRangeSql() " + errorMsg);
621 throw new RuntimeException(errorMsg);
622 }
623 if (!joinAlreadyPerformed) {
624 QueryComponent joinSqlComponent = getSearchableAttributeJoinSql(criteriaComponent.getSearchableAttributeValue(), tableIdentifier, whereSqlStarter, searchAttributeKeyName);
625 fromSql.append(joinSqlComponent.getFromSql());
626 whereSql.append(joinSqlComponent.getWhereSql());
627 joinAlreadyPerformed = true;
628 }
629 whereSql.append(generateSearchableAttributeDefaultWhereSql(criteriaComponent, queryTableColumnName));
630 }
631
632 return new QueryComponent("",fromSql.toString(),whereSql.toString());
633 }
634
635 public StringBuilder generateSearchableAttributeDefaultWhereSql(SearchAttributeCriteriaComponent criteriaComponent,String queryTableColumnName) {
636 StringBuilder whereSql = new StringBuilder();
637 String initialClauseStarter = "and";
638
639
640 boolean valueIsDate = (criteriaComponent.getSearchableAttributeValue() instanceof SearchableAttributeDateTimeValue);
641 boolean valueIsString = (criteriaComponent.getSearchableAttributeValue() instanceof SearchableAttributeStringValue);
642 boolean valueIsLong = (criteriaComponent.getSearchableAttributeValue() instanceof SearchableAttributeLongValue);
643 boolean valueIsFloat = (criteriaComponent.getSearchableAttributeValue() instanceof SearchableAttributeFloatValue);
644 boolean addCaseInsensitivityForValue = (!criteriaComponent.isCaseSensitive()) && criteriaComponent.getSearchableAttributeValue().allowsCaseInsensitivity();
645 String attributeValueSearched = criteriaComponent.getValue();
646 List<String> attributeValuesSearched = criteriaComponent.getValues();
647
648 StringBuilder whereSqlTemp = new StringBuilder();
649 if (valueIsDate) {
650 if (criteriaComponent.isRangeSearch()) {
651
652 whereSqlTemp.append(constructWhereClauseDateElement(initialClauseStarter, queryTableColumnName, criteriaComponent.isSearchInclusive(), criteriaComponent.isComponentLowerBoundValue(), attributeValueSearched));
653 } else if(criteriaComponent.isAllowInlineRange()) {
654 whereSqlTemp.append(constructWhereClauseDateElement(initialClauseStarter, queryTableColumnName, criteriaComponent.isSearchInclusive(), false, attributeValueSearched, criteriaComponent.isAllowInlineRange()));
655 } else {
656 if (!CollectionUtils.isEmpty(attributeValuesSearched)) {
657
658 whereSqlTemp.append(initialClauseStarter).append(" (");
659 boolean firstValue = true;
660 for (String attributeValueEntered : attributeValuesSearched) {
661 whereSqlTemp.append(" ( ");
662 whereSqlTemp.append(constructWhereClauseDateElement("", queryTableColumnName, criteriaComponent.isSearchInclusive(), true, attributeValueEntered));
663 whereSqlTemp.append(constructWhereClauseDateElement("and", queryTableColumnName, criteriaComponent.isSearchInclusive(), false, attributeValueEntered));
664 whereSqlTemp.append(" ) ");
665 String separator = " or ";
666 if (firstValue) {
667 firstValue = false;
668 separator = "";
669 }
670 whereSqlTemp.append(separator);
671 }
672 whereSqlTemp.append(") ");
673 } else {
674
675 whereSqlTemp.append(constructWhereClauseDateElement(initialClauseStarter, queryTableColumnName, criteriaComponent.isSearchInclusive(), true, attributeValueSearched));
676 whereSqlTemp.append(constructWhereClauseDateElement(initialClauseStarter, queryTableColumnName, criteriaComponent.isSearchInclusive(), false, attributeValueSearched));
677 }
678 }
679 } else {
680 boolean usingWildcards = false;
681 StringBuffer prefix = new StringBuffer("");
682 StringBuffer suffix = new StringBuffer("");
683 if (valueIsString) {
684 prefix.append("'");
685 suffix.insert(0,"'");
686 }
687
688
689 if (!CollectionUtils.isEmpty(attributeValuesSearched)) {
690 List<String> newList = new ArrayList<String>();
691 for (String attributeValueEntered : attributeValuesSearched) {
692 newList.add(attributeValueEntered.trim().replace('*', DATABASE_WILDCARD_CHARACTER));
693 usingWildcards |= (attributeValueEntered.contains(DATABASE_WILDCARD_CHARACTER_STRING));
694 }
695 attributeValuesSearched = newList;
696 } else {
697 attributeValueSearched = attributeValueSearched.trim().replace('*', DATABASE_WILDCARD_CHARACTER);
698 usingWildcards |= (attributeValueSearched.indexOf(DATABASE_WILDCARD_CHARACTER_STRING) != -1);
699 }
700 String prefixToUse = prefix.toString();
701 String suffixToUse = suffix.toString();
702
703 if (addCaseInsensitivityForValue) {
704 queryTableColumnName = "upper(" + queryTableColumnName + ")";
705 prefixToUse = "upper(" + prefix.toString();
706 suffixToUse = suffix.toString() + ")";
707 }
708
709 if (!CollectionUtils.isEmpty(attributeValuesSearched)) {
710
711 whereSqlTemp.append(initialClauseStarter).append(" (");
712 boolean firstValue = true;
713 for (String attributeValueEntered : attributeValuesSearched) {
714 checkNumberFormattingIfNumeric(attributeValueEntered, valueIsLong, valueIsFloat);
715
716 String separator = " or ";
717 if (firstValue) {
718 firstValue = false;
719 separator = "";
720 }
721 String sqlOperand = getSqlOperand(criteriaComponent.isRangeSearch(), criteriaComponent.isSearchInclusive(), (criteriaComponent.isRangeSearch() && criteriaComponent.isComponentLowerBoundValue()), usingWildcards);
722 whereSqlTemp.append(constructWhereClauseElement(separator, queryTableColumnName, sqlOperand, getDbPlatform().escapeString(attributeValueEntered), prefixToUse, suffixToUse));
723 }
724 whereSqlTemp.append(") ");
725 } else {
726 String sqlOperand = getSqlOperand(criteriaComponent.isRangeSearch(), criteriaComponent.isSearchInclusive(), (criteriaComponent.isRangeSearch() && criteriaComponent.isComponentLowerBoundValue()), usingWildcards);
727 if(criteriaComponent.isAllowInlineRange()) {
728 for (SearchOperator range : SearchOperator.RANGE_CHARACTERS) {
729 int index = StringUtils.indexOf(attributeValueSearched, range.op());
730 if(index != -1) {
731 sqlOperand=range.op();
732 if(!StringUtils.equals(sqlOperand, SearchOperator.BETWEEN.op())) {
733 attributeValueSearched = StringUtils.remove(attributeValueSearched, range.op());
734
735 } else {
736 String[] rangeValues = StringUtils.split(attributeValueSearched, SearchOperator.BETWEEN.op());
737 if(rangeValues!=null && rangeValues.length>1) {
738 checkNumberFormattingIfNumeric(rangeValues[0], valueIsLong, valueIsFloat);
739
740
741 whereSqlTemp.append(constructWhereClauseElement(initialClauseStarter, queryTableColumnName, SearchOperator.GREATER_THAN_EQUAL.op(), getDbPlatform().escapeString(rangeValues[0]), prefixToUse, suffixToUse));
742 attributeValueSearched = rangeValues[1];
743 sqlOperand = SearchOperator.LESS_THAN_EQUAL.op();
744 } else {
745 throw new RuntimeException("What to do here...Range search \"..\" without one element");
746 }
747 }
748 break;
749 }
750 }
751 }
752 checkNumberFormattingIfNumeric(attributeValueSearched, valueIsLong, valueIsFloat);
753 whereSqlTemp.append(constructWhereClauseElement(initialClauseStarter, queryTableColumnName, sqlOperand, getDbPlatform().escapeString(attributeValueSearched), prefixToUse, suffixToUse));
754 }
755 }
756 whereSqlTemp.append(" ");
757 return whereSql.append(whereSqlTemp);
758 }
759
760
761
762
763
764
765
766
767
768 private void checkNumberFormattingIfNumeric(String testValue, boolean valueIsLong, boolean valueIsFloat) {
769 if (valueIsLong) {
770 try { Long.parseLong(testValue.trim()); }
771 catch (Exception exc) { throw new RiceRuntimeException("Invalid number format", exc); }
772 }
773 if (valueIsFloat) {
774 try { new BigDecimal(testValue.trim()); }
775 catch (Exception exc) { throw new RiceRuntimeException("Invalid number format", exc); }
776 }
777 }
778
779 public QueryComponent getSearchableAttributeJoinSql(SearchableAttributeValue attributeValue,String tableIdentifier,String whereSqlStarter,String attributeTableKeyColumnName) {
780 return new QueryComponent("",generateSearchableAttributeFromSql(attributeValue, tableIdentifier).toString(),generateSearchableAttributeWhereClauseJoin(whereSqlStarter, tableIdentifier, attributeTableKeyColumnName).toString());
781 }
782
783 public StringBuffer generateSearchableAttributeWhereClauseJoin(String whereSqlStarter,String tableIdentifier,String attributeTableKeyColumnName) {
784 StringBuffer whereSql = new StringBuffer(constructWhereClauseElement(whereSqlStarter, "DOC_HDR.DOC_HDR_ID", "=", getDbPlatform().escapeString(tableIdentifier + ".DOC_HDR_ID"), null, null));
785 whereSql.append(constructWhereClauseElement(" and ", tableIdentifier + ".KEY_CD", "=", getDbPlatform().escapeString(attributeTableKeyColumnName), "'", "'"));
786 return whereSql;
787 }
788
789 public StringBuffer generateSearchableAttributeFromSql(SearchableAttributeValue attributeValue,String tableIdentifier) {
790 StringBuffer fromSql = new StringBuffer();
791 String tableName = getDbPlatform().escapeString(attributeValue.getAttributeTableName());
792 if (StringUtils.isBlank(tableName)) {
793 String errorMsg = "The table name associated with Searchable Attribute with class '" + attributeValue.getClass() + "' returns as '" + tableName + "'";
794 LOG.error("getSearchableAttributeSql() " + errorMsg);
795 throw new RuntimeException(errorMsg);
796 }
797 fromSql.append(" ," + tableName + " " + getDbPlatform().escapeString(tableIdentifier) + " ");
798 return fromSql;
799 }
800
801 public StringBuffer constructWhereClauseDateElement(String clauseStarter,String queryTableColumnName,boolean inclusive,boolean valueIsLowerBound,String dateValueToSearch) {
802 return constructWhereClauseDateElement(clauseStarter, queryTableColumnName, inclusive, valueIsLowerBound, dateValueToSearch,false);
803 }
804
805 public StringBuffer constructWhereClauseDateElement(String clauseStarter,String queryTableColumnName,boolean inclusive,boolean valueIsLowerBound,String dateValueToSearch, boolean isAllowInlineRange) {
806 StringBuffer whereSQLBuffer = new StringBuffer();
807 StringBuffer sqlOperand = new StringBuffer(getSqlOperand(true, inclusive, valueIsLowerBound, false));
808 String lowerTimeBound = "00:00:00";
809 String upperTimeBound = "23:59:59";
810
811 String timeValueToSearch = null;
812 if (valueIsLowerBound) {
813 timeValueToSearch = lowerTimeBound;
814 } else {
815 timeValueToSearch = upperTimeBound;
816 }
817
818 if(isAllowInlineRange) {
819 for (SearchOperator range : SearchOperator.RANGE_CHARACTERS) {
820 int index = StringUtils.indexOf(dateValueToSearch, range.op());
821 if(index != -1) {
822 sqlOperand=new StringBuffer(range.op());
823 if(!StringUtils.equals(sqlOperand.toString(), SearchOperator.BETWEEN.op())) {
824 dateValueToSearch = StringUtils.remove(dateValueToSearch,range.op());
825 if(range == SearchOperator.GREATER_THAN) {
826 timeValueToSearch = upperTimeBound;
827 } else if(range == SearchOperator.LESS_THAN){
828 timeValueToSearch = lowerTimeBound;
829 }
830 } else {
831 String[] rangeValues = StringUtils.split(dateValueToSearch, SearchOperator.BETWEEN.op());
832 if(rangeValues!=null && rangeValues.length>1) {
833
834
835 timeValueToSearch = lowerTimeBound;
836 whereSQLBuffer.append(constructWhereClauseElement(clauseStarter, queryTableColumnName, SearchOperator.GREATER_THAN_EQUAL.op(), getDbPlatform().getDateSQL(getDbPlatform().escapeString(SQLUtils.getSqlFormattedDate(rangeValues[0].trim())), timeValueToSearch.trim()), "", ""));
837
838 dateValueToSearch = rangeValues[1];
839 sqlOperand = new StringBuffer(SearchOperator.LESS_THAN_EQUAL.op());
840 timeValueToSearch = upperTimeBound;
841 } else {
842 throw new RuntimeException("What to do here...Range search \"..\" without one element");
843 }
844 }
845 break;
846 }
847 }
848 }
849 return whereSQLBuffer.append(constructWhereClauseElement(clauseStarter, queryTableColumnName, sqlOperand.toString(), getDbPlatform().getDateSQL(getDbPlatform().escapeString(SQLUtils.getSqlFormattedDate(dateValueToSearch.trim())), timeValueToSearch.trim()), "", ""));
850 }
851
852 public StringBuffer constructWhereClauseElement(String clauseStarter,String queryTableColumnName,String operand,String valueToSearch,String valuePrefix,String valueSuffix) {
853 StringBuffer whereSql = new StringBuffer();
854 valuePrefix = (valuePrefix != null) ? valuePrefix : "";
855 valueSuffix = (valueSuffix != null) ? valueSuffix : "";
856 whereSql.append(" " + clauseStarter + " ").append(getDbPlatform().escapeString(queryTableColumnName)).append(" " + operand + " ").append(valuePrefix).append(valueToSearch).append(valueSuffix).append(" ");
857 return whereSql;
858 }
859
860
861
862
863
864 public String getSqlOperand(boolean rangeSearch, boolean inclusive, boolean valueIsLowerBound, boolean usingWildcards) {
865 StringBuffer sqlOperand = new StringBuffer("=");
866 if (rangeSearch) {
867 if (valueIsLowerBound) {
868 sqlOperand = new StringBuffer(">");
869 } else {
870 sqlOperand = new StringBuffer("<");
871 }
872 if (inclusive) {
873 sqlOperand.append("=");
874 }
875
876 } else if (usingWildcards) {
877 sqlOperand = new StringBuffer("like");
878 }
879 return sqlOperand.toString();
880 }
881
882
883
884
885 public List<DocSearchDTO> processResultSet(Statement searchAttributeStatement, ResultSet resultSet,DocSearchCriteriaDTO searchCriteria) throws SQLException {
886 String principalId = null;
887 return processResultSet(searchAttributeStatement, resultSet, searchCriteria, principalId);
888 }
889
890
891
892
893
894
895
896
897 public List<DocSearchDTO> processResultSet(Statement searchAttributeStatement, ResultSet resultSet,DocSearchCriteriaDTO searchCriteria, String principalId) throws SQLException {
898 setCriteria(searchCriteria);
899 int size = 0;
900 List<DocSearchDTO> docList = new ArrayList<DocSearchDTO>();
901 Map<String, DocSearchDTO> resultMap = new HashMap<String, DocSearchDTO>();
902 PerformanceLogger perfLog = new PerformanceLogger();
903 int iteration = 0;
904 boolean resultSetHasNext = resultSet.next();
905 while ( resultSetHasNext &&
906 ( (searchCriteria.getThreshold() == null) || (resultMap.size() < searchCriteria.getThreshold().intValue()) ) &&
907 ( (searchCriteria.getFetchLimit() == null) || (iteration < searchCriteria.getFetchLimit().intValue()) ) ) {
908 iteration++;
909 DocSearchDTO docCriteriaDTO = processRow(searchAttributeStatement, resultSet);
910 docCriteriaDTO.setSuperUserSearch(getCriteria().getSuperUserSearch());
911 if (!resultMap.containsKey(docCriteriaDTO.getDocumentId())) {
912 docList.add(docCriteriaDTO);
913 resultMap.put(docCriteriaDTO.getDocumentId(), docCriteriaDTO);
914 size++;
915 } else {
916
917 DocSearchDTO previousEntry = (DocSearchDTO)resultMap.get(docCriteriaDTO.getDocumentId());
918 handleMultipleDocumentRows(previousEntry, docCriteriaDTO);
919 }
920 resultSetHasNext = resultSet.next();
921 }
922
923
924
925
926
927
928
929
930
931 Set<String> initiatorPrincipalIdSet = new HashSet<String>();
932 for (DocSearchDTO docSearchRow : docList) {
933 initiatorPrincipalIdSet.add(docSearchRow.getInitiatorWorkflowId());
934 }
935 List<String> initiatorPrincipalIds = new ArrayList<String>();
936 initiatorPrincipalIds.addAll(initiatorPrincipalIdSet);
937 if(initiatorPrincipalIds != null && !initiatorPrincipalIds.isEmpty()){
938 Map<String, EntityNamePrincipalName> entityNames = KimApiServiceLocator.getIdentityService().getDefaultNamesForPrincipalIds(initiatorPrincipalIds);
939 for (DocSearchDTO docSearchRow : docList) {
940 EntityNamePrincipalName name = entityNames.get(docSearchRow.getInitiatorWorkflowId());
941 if (name != null) {
942 docSearchRow.setInitiatorFirstName(name.getDefaultName().getFirstName());
943 docSearchRow.setInitiatorLastName(name.getDefaultName().getLastName());
944 docSearchRow.setInitiatorName(name.getDefaultName().getFormattedName());
945 docSearchRow.setInitiatorNetworkId(name.getPrincipalName());
946 if (StringUtils.isNotBlank(name.getDefaultName().getFormattedName())) {
947 docSearchRow.setInitiatorTransposedName(name.getDefaultName().getFormattedName());
948 } else if (StringUtils.isNotBlank(name.getPrincipalName())) {
949 docSearchRow.setInitiatorTransposedName(name.getPrincipalName());
950 } else {
951 docSearchRow.setInitiatorTransposedName(docSearchRow.getInitiatorWorkflowId());
952 }
953
954 docSearchRow.setInitiatorEmailAddress("");
955 }
956 }
957 }
958
959
960
961 perfLog.log("Time to read doc search results.", true);
962
963 criteria.setOverThreshold(resultSetHasNext);
964
965 final UserSession userSession = createUserSession(searchCriteria, principalId);
966 if (userSession != null) {
967
968
969
970 perfLog = new PerformanceLogger();
971 SecuritySession securitySession = new SecuritySession(userSession);
972 for (Iterator<DocSearchDTO> iterator = docList.iterator(); iterator.hasNext();) {
973 DocSearchDTO docCriteriaDTO = (DocSearchDTO) iterator.next();
974 if (!KEWServiceLocator.getDocumentSecurityService().docSearchAuthorized(userSession, docCriteriaDTO, securitySession)) {
975 iterator.remove();
976 criteria.setSecurityFilteredRows(criteria.getSecurityFilteredRows() + 1);
977 }
978 }
979 perfLog.log("Time to filter document search results for security.", true);
980 }
981
982 LOG.debug("Processed "+size+" document search result rows.");
983 return docList;
984 }
985
986 private static UserSession createUserSession(DocSearchCriteriaDTO searchCriteria, String principalId) {
987 UserSession userSession = GlobalVariables.getUserSession();
988 if ( (userSession == null) && StringUtils.isNotBlank(principalId)) {
989 LOG.info("Authenticated User Session is null... using parameter user: " + principalId);
990 Person user = KimApiServiceLocator.getPersonService().getPerson(principalId);
991 if (user != null) {
992 userSession = new UserSession(user.getPrincipalName());
993 }
994 } else if (searchCriteria.isOverridingUserSession()) {
995 if (principalId == null) {
996 LOG.error("Search Criteria specified UserSession override but given user paramter is null");
997 throw new WorkflowRuntimeException("Search criteria specified UserSession override but given user is null.");
998 }
999 LOG.info("Search Criteria specified UserSession override. Using user: " + principalId);
1000 Person user = KimApiServiceLocator.getPersonService().getPerson(principalId);
1001 if (user != null) {
1002 userSession = new UserSession(user.getPrincipalName());
1003 }
1004 }
1005 return userSession;
1006 }
1007
1008
1009
1010
1011
1012
1013
1014
1015 public void handleMultipleDocumentRows(DocSearchDTO existingRow, DocSearchDTO newRow) {
1016
1017 for (KeyValueSort newData : newRow.getSearchableAttributes()) {
1018 String newRowValue = newData.getValue();
1019 boolean foundMatch = false;
1020 for (KeyValueSort existingData : existingRow.getSearchableAttributes()) {
1021 if (existingData.getKey().equals(newData.getKey())) {
1022 String existingRowValue = existingData.getValue();
1023 if (!org.apache.commons.lang.StringUtils.isEmpty(newRowValue)) {
1024 String valueToSet = "";
1025 if (org.apache.commons.lang.StringUtils.isEmpty(existingRowValue)) {
1026 valueToSet = newRowValue;
1027 } else {
1028 valueToSet = existingRowValue + "<br>" + newRowValue;
1029 }
1030 existingData.setValue(valueToSet);
1031 if ( (existingData.getSortValue() == null) && (newData.getSortValue() != null) ) {
1032 existingData.setSortValue(newData.getSortValue());
1033 }
1034 }
1035 foundMatch = true;
1036 }
1037 }
1038 if (!foundMatch) {
1039 existingRow.addSearchableAttribute(new KeyValueSort(newData));
1040 }
1041 }
1042 }
1043
1044 public DocSearchDTO processRow(Statement searchAttributeStatement, ResultSet rs) throws SQLException {
1045 DocSearchDTO docCriteriaDTO = new DocSearchDTO();
1046
1047 docCriteriaDTO.setDocumentId(rs.getString("DOC_HDR_ID"));
1048
1049 String docTypeLabel = rs.getString("LBL");
1050 String activeIndicatorCode = rs.getString("ACTV_IND");
1051
1052 docCriteriaDTO.setDocRouteStatusCode(rs.getString("DOC_HDR_STAT_CD"));
1053 docCriteriaDTO.setDateCreated(rs.getTimestamp("CRTE_DT"));
1054 docCriteriaDTO.setDocumentTitle(rs.getString("TTL"));
1055 docCriteriaDTO.setDocTypeName(rs.getString("DOC_TYP_NM"));
1056 docCriteriaDTO.setDocTypeLabel(docTypeLabel);
1057 docCriteriaDTO.setAppDocStatus(rs.getString("APP_DOC_STAT"));
1058
1059 if ((activeIndicatorCode == null) || (activeIndicatorCode.trim().length() == 0)) {
1060 docCriteriaDTO.setActiveIndicatorCode(KEWConstants.ACTIVE_CD);
1061 } else {
1062 docCriteriaDTO.setActiveIndicatorCode(activeIndicatorCode);
1063 }
1064
1065 if ((docTypeLabel == null) || (docTypeLabel.trim().length() == 0)) {
1066 docCriteriaDTO.setDocTypeHandlerUrl("");
1067 } else {
1068 docCriteriaDTO.setDocTypeHandlerUrl(rs.getString("DOC_HDLR_URL"));
1069 }
1070
1071 docCriteriaDTO.setInitiatorWorkflowId(rs.getString("INITR_PRNCPL_ID"));
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103 if (isUsingAtLeastOneSearchAttribute()) {
1104 populateRowSearchableAttributes(docCriteriaDTO,searchAttributeStatement);
1105 }
1106 return docCriteriaDTO;
1107 }
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117 public void populateRowSearchableAttributes(DocSearchDTO docCriteriaDTO, Statement searchAttributeStatement) throws SQLException {
1118 searchAttributeStatement.setFetchSize(50);
1119 String documentId = docCriteriaDTO.getDocumentId();
1120 List<SearchableAttributeValue> attributeValues = DocSearchUtils.getSearchableAttributeValueObjectTypes();
1121 PerformanceLogger perfLog = new PerformanceLogger(documentId);
1122 for (SearchableAttributeValue searchAttValue : attributeValues) {
1123 String attributeSql = "select KEY_CD, VAL from " + searchAttValue.getAttributeTableName() + " where DOC_HDR_ID = " + documentId;
1124 ResultSet attributeResultSet = null;
1125 try {
1126 attributeResultSet = searchAttributeStatement.executeQuery(attributeSql);
1127 while (attributeResultSet.next()) {
1128 searchAttValue.setSearchableAttributeKey(attributeResultSet.getString("KEY_CD"));
1129 searchAttValue.setupAttributeValue(attributeResultSet, "VAL");
1130 if ( (!org.apache.commons.lang.StringUtils.isEmpty(searchAttValue.getSearchableAttributeKey())) && (searchAttValue.getSearchableAttributeValue() != null) ) {
1131 docCriteriaDTO.addSearchableAttribute(new KeyValueSort(searchAttValue.getSearchableAttributeKey(),searchAttValue.getSearchableAttributeDisplayValue(),searchAttValue.getSearchableAttributeValue(),searchAttValue));
1132 }
1133 }
1134 } finally {
1135 if (attributeResultSet != null) {
1136 try {
1137 attributeResultSet.close();
1138 } catch (Exception e) {
1139 LOG.warn("Could not close searchable attribute result set for class " + searchAttValue.getClass().getName(),e);
1140 }
1141 }
1142 }
1143 }
1144 perfLog.log("Time to execute doc search search attribute queries.", true);
1145 }
1146
1147
1148
1149
1150
1151 @Deprecated
1152 public void populateRowSearchableAttributes(DocSearchDTO docCriteriaDTO, Statement searchAttributeStatement, ResultSet rs) throws SQLException {
1153 List<SearchableAttributeValue> searchAttributeValues = DocSearchUtils.getSearchableAttributeValueObjectTypes();
1154 for (SearchableAttributeValue searchAttValue : searchAttributeValues) {
1155 String prefixName = searchAttValue.getAttributeDataType().toUpperCase();
1156 searchAttValue.setSearchableAttributeKey(rs.getString(prefixName + "_KEY"));
1157 searchAttValue.setupAttributeValue(rs, prefixName + "_VALUE");
1158 if ( (!org.apache.commons.lang.StringUtils.isEmpty(searchAttValue.getSearchableAttributeKey())) && (searchAttValue.getSearchableAttributeValue() != null) ) {
1159 docCriteriaDTO.addSearchableAttribute(new KeyValueSort(searchAttValue.getSearchableAttributeKey(),searchAttValue.getSearchableAttributeDisplayValue(),searchAttValue.getSearchableAttributeValue(),searchAttValue));
1160 }
1161 }
1162 }
1163
1164 public String getDocSearchSQL() {
1165
1166 String docTypeTableAlias = "DOC1";
1167 String docHeaderTableAlias = "DOC_HDR";
1168
1169 String sqlPrefix = "Select * from (";
1170 String sqlSuffix = ") FINAL_SEARCH order by FINAL_SEARCH.DOC_HDR_ID desc";
1171
1172 StringBuffer selectSQL = new StringBuffer("select DISTINCT("+ docHeaderTableAlias +".DOC_HDR_ID), "+ docHeaderTableAlias +".INITR_PRNCPL_ID, "
1173 + docHeaderTableAlias +".DOC_HDR_STAT_CD, "+ docHeaderTableAlias +".CRTE_DT, "+ docHeaderTableAlias +".TTL, "+ docHeaderTableAlias +".APP_DOC_STAT, "+ docTypeTableAlias +".DOC_TYP_NM, "
1174 + docTypeTableAlias +".LBL, "+ docTypeTableAlias +".DOC_HDLR_URL, "+ docTypeTableAlias +".ACTV_IND");
1175 StringBuffer fromSQL = new StringBuffer(" from KREW_DOC_TYP_T "+ docTypeTableAlias +" ");
1176 StringBuffer fromSQLForDocHeaderTable = new StringBuffer(", KREW_DOC_HDR_T " + docHeaderTableAlias + " ");
1177
1178 StringBuffer whereSQL = new StringBuffer();
1179 whereSQL.append(getDocumentIdSql(criteria.getDocumentId(), getGeneratedPredicatePrefix(whereSQL.length()), docHeaderTableAlias));
1180 whereSQL.append(getInitiatorSql(criteria.getInitiator(), getGeneratedPredicatePrefix(whereSQL.length())));
1181 whereSQL.append(getAppDocIdSql(criteria.getAppDocId(), getGeneratedPredicatePrefix(whereSQL.length())));
1182 whereSQL.append(getDateCreatedSql(criteria.getFromDateCreated(), criteria.getToDateCreated(), getGeneratedPredicatePrefix(whereSQL.length())));
1183 whereSQL.append(getDateLastModifiedSql(criteria.getFromDateLastModified(), criteria.getToDateLastModified(), getGeneratedPredicatePrefix(whereSQL.length())));
1184 whereSQL.append(getDateApprovedSql(criteria.getFromDateApproved(), criteria.getToDateApproved(), getGeneratedPredicatePrefix(whereSQL.length())));
1185 whereSQL.append(getDateFinalizedSql(criteria.getFromDateFinalized(), criteria.getToDateFinalized(), getGeneratedPredicatePrefix(whereSQL.length())));
1186
1187 if ((!"".equals(getViewerSql(criteria.getViewer(), getGeneratedPredicatePrefix(whereSQL.length())))) || (!"".equals(getWorkgroupViewerSql(criteria.getWorkgroupViewerId(), criteria.getWorkgroupViewerName(), getGeneratedPredicatePrefix(whereSQL.length()))))) {
1188 whereSQL.append(getViewerSql(criteria.getViewer(), getGeneratedPredicatePrefix(whereSQL.length())));
1189 whereSQL.append(getWorkgroupViewerSql(criteria.getWorkgroupViewerId(), criteria.getWorkgroupViewerName(), getGeneratedPredicatePrefix(whereSQL.length())));
1190 fromSQL.append(", KREW_ACTN_RQST_T ");
1191 }
1192
1193 if (!("".equals(getApproverSql(criteria.getApprover(), getGeneratedPredicatePrefix(whereSQL.length()))))) {
1194 whereSQL.append(getApproverSql(criteria.getApprover(), getGeneratedPredicatePrefix(whereSQL.length())));
1195 fromSQL.append(", KREW_ACTN_TKN_T ");
1196 }
1197
1198
1199
1200 String docRouteNodeSql = getDocRouteNodeSql(criteria.getDocTypeFullName(), criteria.getDocRouteNodeId(), criteria.getDocRouteNodeLogic(), getGeneratedPredicatePrefix(whereSQL.length()));
1201 if (!"".equals(docRouteNodeSql)) {
1202 whereSQL.append(docRouteNodeSql);
1203 fromSQL.append(", KREW_RTE_NODE_INSTN_T ");
1204 fromSQL.append(", KREW_RTE_NODE_T ");
1205 }
1206
1207 filterOutNonQueryAttributes();
1208 if ((criteria.getSearchableAttributes() != null) && (criteria.getSearchableAttributes().size() > 0)) {
1209 QueryComponent queryComponent = getSearchableAttributeSql(criteria.getSearchableAttributes(), getGeneratedPredicatePrefix(whereSQL.length()));
1210 selectSQL.append(queryComponent.getSelectSql());
1211 fromSQL.append(queryComponent.getFromSql());
1212 whereSQL.append(queryComponent.getWhereSql());
1213 }
1214
1215
1216
1217
1218 Integer defaultCreateDateDaysAgoValue = null;
1219
1220 String tempWhereSql = getDocTitleSql(criteria.getDocTitle(), getGeneratedPredicatePrefix(whereSQL.length()));
1221 if ( ((whereSQL == null) || (StringUtils.isBlank(whereSQL.toString()))) && (StringUtils.isNotBlank(tempWhereSql)) ) {
1222
1223 defaultCreateDateDaysAgoValue = KEWConstants.DOCUMENT_SEARCH_DOC_TITLE_CREATE_DATE_DAYS_AGO;
1224 }
1225 whereSQL.append(tempWhereSql);
1226 if ( ((whereSQL == null) || (StringUtils.isBlank(whereSQL.toString()))) && (StringUtils.isBlank(criteria.getDocRouteStatus())) ) {
1227
1228 defaultCreateDateDaysAgoValue = KEWConstants.DOCUMENT_SEARCH_NO_CRITERIA_CREATE_DATE_DAYS_AGO;
1229 }
1230 if (defaultCreateDateDaysAgoValue != null) {
1231
1232 Calendar calendar = Calendar.getInstance();
1233 calendar.add(Calendar.DATE, defaultCreateDateDaysAgoValue.intValue());
1234 criteria.setFromDateCreated(RiceConstants.getDefaultDateFormat().format(calendar.getTime()));
1235 whereSQL.append(getDateCreatedSql(criteria.getFromDateCreated(), criteria.getToDateCreated(), getGeneratedPredicatePrefix(whereSQL.length())));
1236 }
1237
1238 String docTypeFullNameSql = getDocTypeFullNameWhereSql(criteria.getDocTypeFullName(), getGeneratedPredicatePrefix(whereSQL.length()));
1239 if (!("".equals(docTypeFullNameSql))) {
1240 whereSQL.append(docTypeFullNameSql);
1241 }
1242 whereSQL.append(getDocRouteStatusSql(criteria.getDocRouteStatus(), getGeneratedPredicatePrefix(whereSQL.length())));
1243 whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_TYP_ID = DOC1.DOC_TYP_ID ");
1244 fromSQL.append(fromSQLForDocHeaderTable);
1245
1246
1247 String statusTransitionWhereClause = getStatusTransitionDateSql(criteria.getFromStatusTransitionDate(), criteria.getToStatusTransitionDate(), getGeneratedPredicatePrefix(whereSQL.length()));
1248 whereSQL.append(getAppDocStatusSql(criteria.getAppDocStatus(), getGeneratedPredicatePrefix(whereSQL.length()), statusTransitionWhereClause.length() ));
1249 if (statusTransitionWhereClause.length() > 0){
1250 whereSQL.append(statusTransitionWhereClause);
1251 whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_HDR_ID = STAT_TRAN.DOC_HDR_ID ");
1252 fromSQL.append(", KREW_APP_DOC_STAT_TRAN_T STAT_TRAN ");
1253 }
1254
1255 String finalizedSql = sqlPrefix + " " + selectSQL.toString() + " " + fromSQL.toString() + " " + whereSQL.toString() + " " + sqlSuffix;
1256
1257 LOG.info("*********** SEARCH SQL ***************");
1258 LOG.info(finalizedSql);
1259 LOG.info("**************************************");
1260 return finalizedSql;
1261 }
1262
1263
1264
1265
1266
1267
1268 @Deprecated
1269 public String generateFinalSQL(QueryComponent searchSQL,String docHeaderTableAlias, String standardSqlPrefix, String standardSqlSuffix) {
1270 StringBuffer finalSql = new StringBuffer();
1271 List<SearchableAttributeValue> searchableAttributeValues = DocSearchUtils.getSearchableAttributeValueObjectTypes();
1272 List<String> tableAliasComponentNames = new ArrayList<String>(searchableAttributeValues.size());
1273 for (SearchableAttributeValue attValue : searchableAttributeValues) {
1274 tableAliasComponentNames.add(attValue.getAttributeDataType().toUpperCase());
1275 }
1276 for (SearchableAttributeValue attributeValue : searchableAttributeValues) {
1277 QueryComponent qc = generateSqlForSearchableAttributeValue(attributeValue, tableAliasComponentNames, docHeaderTableAlias);
1278 StringBuffer currentSql = new StringBuffer();
1279 currentSql.append(searchSQL.getSelectSql() + qc.getSelectSql() + searchSQL.getFromSql() + qc.getFromSql() + searchSQL.getWhereSql() + qc.getWhereSql());
1280 if (finalSql.length() == 0) {
1281 finalSql.append(standardSqlPrefix).append(" ( ").append(currentSql);
1282 } else {
1283 finalSql.append(" ) UNION ( " + currentSql.toString());
1284 }
1285 }
1286 finalSql.append(" ) " + standardSqlSuffix);
1287 return finalSql.toString();
1288 }
1289
1290
1291
1292
1293
1294
1295
1296 @Deprecated
1297 public QueryComponent generateSqlForSearchableAttributeValue(SearchableAttributeValue attributeValue, List<String> tableAliasComponentNames, String docHeaderTableAlias) {
1298 StringBuffer selectSql = new StringBuffer();
1299 StringBuffer fromSql = new StringBuffer();
1300 String currentAttributeTableAlias = "SA_" + attributeValue.getAttributeDataType().toUpperCase();
1301 fromSql.append(" LEFT OUTER JOIN " + attributeValue.getAttributeTableName() + " " + currentAttributeTableAlias + " ON (" + docHeaderTableAlias + ".DOC_HDR_ID = " + currentAttributeTableAlias + ".DOC_HDR_ID)");
1302 for (String string : tableAliasComponentNames) {
1303 String aliasComponentName = (String) string;
1304 if (aliasComponentName.equalsIgnoreCase(attributeValue.getAttributeDataType())) {
1305 selectSql.append(", " + currentAttributeTableAlias + ".KEY_CD as " + aliasComponentName + "_KEY, " + currentAttributeTableAlias + ".VAL as " + aliasComponentName + "_VALUE");
1306 } else {
1307 selectSql.append(", NULL as " + aliasComponentName + "_KEY, NULL as " + aliasComponentName + "_VALUE");
1308 }
1309 }
1310 return new QueryComponent(selectSql.toString(),fromSql.toString(),"");
1311 }
1312
1313 public String getDocumentIdSql(String documentId, String whereClausePredicatePrefix, String tableAlias) {
1314
1315 if ((documentId == null) || "".equals(documentId.trim())) {
1316 return "";
1317 } else {
1318
1319
1320 Criteria crit = getSqlBuilder().createCriteria("DOC_HDR_ID", documentId, "KREW_DOC_HDR_T", tableAlias, String.class, false, true);
1321 return new StringBuffer(whereClausePredicatePrefix + crit.buildWhere()).toString();
1322 }
1323
1324 }
1325
1326 public String getInitiatorSql(String initiator, String whereClausePredicatePrefix) {
1327 String tableAlias = "DOC_HDR";
1328
1329 if ((initiator == null) || "".equals(initiator.trim())) {
1330 return "";
1331 }
1332
1333 Map<String, String> m = new HashMap<String, String>();
1334 m.put("principalName", initiator);
1335
1336
1337 List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
1338
1339 if(pList == null || pList.isEmpty() ){
1340
1341 return new StringBuffer(whereClausePredicatePrefix + " 1 = 0 ").toString();
1342 }
1343
1344 List<String> principalList = new ArrayList<String>();
1345
1346 for(Person p: pList){
1347 principalList.add(p.getPrincipalId());
1348 }
1349
1350 Criteria crit = new Criteria("KREW_DOC_HDR_T", tableAlias);
1351 crit.in("INITR_PRNCPL_ID", principalList, String.class);
1352
1353
1354 return new StringBuffer(whereClausePredicatePrefix + crit.buildWhere()).toString();
1355
1356
1357 }
1358
1359 public String getDocTitleSql(String docTitle, String whereClausePredicatePrefix) {
1360 if (StringUtils.isBlank(docTitle)) {
1361 return "";
1362 } else {
1363
1364
1365
1366
1367
1368
1369
1370
1371 docTitle = docTitle.trim().replace("\'", "\'\'");
1372
1373
1374 SqlBuilder sqlBuild = new SqlBuilder();
1375 Criteria crit = new Criteria("KREW_DOC_HDR_T", "DOC_HDR");
1376
1377 sqlBuild.addCriteria("TTL", docTitle, String.class, true, true, crit);
1378 return new StringBuffer(whereClausePredicatePrefix + crit.buildWhere()).toString();
1379
1380
1381
1382
1383 }
1384 }
1385
1386
1387
1388 public String getAppDocIdSql(String appDocId, String whereClausePredicatePrefix) {
1389 String tableAlias = "DOC_HDR";
1390
1391 if ((appDocId == null) || "".equals(appDocId.trim())) {
1392 return "";
1393 } else {
1394 Criteria crit = getSqlBuilder().createCriteria("APP_DOC_ID", appDocId, "KREW_DOC_HDR_T", tableAlias,String.class);
1395 return new StringBuffer(whereClausePredicatePrefix + crit.buildWhere()).toString();
1396 }
1397 }
1398
1399 public String getDateCreatedSql(String fromDateCreated, String toDateCreated, String whereClausePredicatePrefix) {
1400 return establishDateString(fromDateCreated, toDateCreated, "KREW_DOC_HDR_T", "DOC_HDR", "CRTE_DT", whereClausePredicatePrefix);
1401 }
1402
1403 public String getDateApprovedSql(String fromDateApproved, String toDateApproved, String whereClausePredicatePrefix) {
1404 return establishDateString(fromDateApproved, toDateApproved, "KREW_DOC_HDR_T", "DOC_HDR", "APRV_DT", whereClausePredicatePrefix);
1405 }
1406
1407 public String getDateFinalizedSql(String fromDateFinalized, String toDateFinalized, String whereClausePredicatePrefix) {
1408 return establishDateString(fromDateFinalized, toDateFinalized, "KREW_DOC_HDR_T", "DOC_HDR", "FNL_DT", whereClausePredicatePrefix);
1409
1410 }
1411
1412 public String getDateLastModifiedSql(String fromDateLastModified, String toDateLastModified, String whereClausePredicatePrefix) {
1413 return establishDateString(fromDateLastModified, toDateLastModified, "KREW_DOC_HDR_T", "DOC_HDR", "STAT_MDFN_DT", whereClausePredicatePrefix);
1414 }
1415
1416 public String getStatusTransitionDateSql(String fromStatusTransitionDate, String toStatusTransitionDate, String whereClausePredicatePrefix) {
1417 return establishDateString(fromStatusTransitionDate, toStatusTransitionDate, "KREW_DOC_HDR_T", "DOC_HDR", "APP_DOC_STAT_MDFN_DT", whereClausePredicatePrefix);
1418 }
1419
1420 public String getViewerSql(String viewer, String whereClausePredicatePrefix) {
1421 String returnSql = "";
1422 if ((viewer != null) && (!"".equals(viewer.trim()))) {
1423 Map<String, String> m = new HashMap<String, String>();
1424 m.put("principalName", viewer);
1425
1426
1427 List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
1428
1429 if(pList == null || pList.isEmpty() ){
1430
1431 return new StringBuffer(whereClausePredicatePrefix + " 1 = 0 ").toString();
1432 }
1433
1434 List<String> principalList = new ArrayList<String>();
1435
1436 for(Person p: pList){
1437 principalList.add(p.getPrincipalId());
1438 }
1439
1440 Criteria crit = new Criteria("KREW_ACTN_RQST_T", "KREW_ACTN_RQST_T");
1441
1442 crit.in("PRNCPL_ID", principalList, String.class);
1443
1444
1445
1446 returnSql = whereClausePredicatePrefix + "( (DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and " + crit.buildWhere() + " )";
1447
1448 Set<String> viewerGroupIds = new TreeSet<String>();
1449
1450 if(principalList != null && !principalList.isEmpty()){
1451 for(String principalId: principalList){
1452 viewerGroupIds.addAll(KimApiServiceLocator.getGroupService().getGroupIdsForPrincipal(principalId));
1453 }
1454 }
1455
1456
1457
1458
1459 if (viewerGroupIds != null && !viewerGroupIds.isEmpty()) {
1460
1461 returnSql += " or ( " +
1462 "DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID " +
1463 "and KREW_ACTN_RQST_T.GRP_ID in (";
1464
1465 boolean first = true;
1466 for(String groupId: viewerGroupIds){
1467 if(!first){
1468 returnSql += ",";
1469 }
1470 returnSql += "'" + groupId + "'";
1471 first = false;
1472 }
1473 returnSql += "))";
1474 }
1475 returnSql += ")";
1476 }
1477 return returnSql;
1478 }
1479
1480 public String getWorkgroupViewerSql(String id, String workgroupName, String whereClausePredicatePrefix) {
1481 String sql = "";
1482 if (!org.apache.commons.lang.StringUtils.isEmpty(workgroupName)) {
1483 Group group = KimApiServiceLocator.getGroupService().getGroup(id);
1484 sql = whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and KREW_ACTN_RQST_T.GRP_ID = " + group.getId();
1485 }
1486 return sql;
1487 }
1488
1489 public String getApproverSql(String approver, String whereClausePredicatePrefix) {
1490 String returnSql = "";
1491 if ((approver != null) && (!"".equals(approver.trim()))) {
1492 Map<String, String> m = new HashMap<String, String>();
1493 m.put("principalName", approver);
1494
1495
1496 List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
1497
1498 if(pList == null || pList.isEmpty() ){
1499 return "";
1500 }
1501
1502 List<String> principalList = new ArrayList<String>();
1503
1504 for(Person p: pList){
1505 principalList.add(p.getPrincipalId());
1506 }
1507
1508 Criteria crit = new Criteria("KREW_ACTN_TKN_T", "KREW_ACTN_TKN_T");
1509 crit.in("PRNCPL_ID", principalList, String.class);
1510
1511 returnSql = whereClausePredicatePrefix +
1512 " DOC_HDR.DOC_HDR_ID = KREW_ACTN_TKN_T.DOC_HDR_ID and upper(KREW_ACTN_TKN_T.ACTN_CD) in ('" +
1513 KEWConstants.ACTION_TAKEN_APPROVED_CD + "','" + KEWConstants.ACTION_TAKEN_BLANKET_APPROVE_CD + "')" +
1514 " and " + crit.buildWhere();
1515 }
1516 return returnSql;
1517 }
1518
1519 public String getDocTypeFullNameWhereSql(String docTypeFullName, String whereClausePredicatePrefix) {
1520 StringBuffer returnSql = new StringBuffer("");
1521 if ((docTypeFullName != null) && (!"".equals(docTypeFullName.trim()))) {
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531 DocumentTypeService docSrv = (DocumentTypeService) KEWServiceLocator.getDocumentTypeService();
1532 DocumentType docType = docSrv.findByName(docTypeFullName.trim());
1533 if (docType != null) {
1534 returnSql.append(whereClausePredicatePrefix).append("(");
1535 addDocumentTypeNameToSearchOn(returnSql,docType.getName(), "");
1536 if (docType.getChildrenDocTypes() != null) {
1537 addChildDocumentTypes(returnSql, docType.getChildrenDocTypes());
1538 }
1539 addExtraDocumentTypesToSearch(returnSql,docType);
1540 returnSql.append(")");
1541 }else{
1542 returnSql.append(whereClausePredicatePrefix).append("(");
1543 addDocumentTypeLikeNameToSearchOn(returnSql,docTypeFullName.trim(), "");
1544 returnSql.append(")");
1545 }
1546 }
1547 return returnSql.toString();
1548 }
1549
1550 public void addChildDocumentTypes(StringBuffer whereSql, Collection<DocumentType> childDocumentTypes) {
1551 for (DocumentType child : childDocumentTypes) {
1552 addDocumentTypeNameToSearchOn(whereSql, child.getName());
1553 addChildDocumentTypes(whereSql, child.getChildrenDocTypes());
1554 }
1555 }
1556
1557 public void addExtraDocumentTypesToSearch(StringBuffer whereSql,DocumentType docType) {}
1558
1559 public void addDocumentTypeNameToSearchOn(StringBuffer whereSql,String documentTypeName) {
1560 this.addDocumentTypeNameToSearchOn(whereSql, documentTypeName, " or ");
1561 }
1562
1563 public void addDocumentTypeNameToSearchOn(StringBuffer whereSql,String documentTypeName, String clause) {
1564 whereSql.append(clause).append(" DOC1.DOC_TYP_NM = '" + documentTypeName + "'");
1565 }
1566 public void addDocumentTypeLikeNameToSearchOn(StringBuffer whereSql,String documentTypeName, String clause) {
1567 documentTypeName = documentTypeName.replace('*', '%');
1568 whereSql.append(clause).append(" DOC1.DOC_TYP_NM LIKE '" + documentTypeName + "'");
1569 }
1570
1571 public String getDocRouteNodeSql(String documentTypeFullName, String docRouteLevel, String docRouteLevelLogic, String whereClausePredicatePrefix) {
1572
1573
1574 String returnSql = "";
1575 if ((docRouteLevel != null) && (!"".equals(docRouteLevel.trim())) && (!docRouteLevel.equals("-1"))) {
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585 String searchCriteriaRouteNodeName = "";
1586 try {
1587 RouteNode searchCriteriaRouteNode = KEWServiceLocator.getRouteNodeService().findRouteNodeById(docRouteLevel);
1588
1589 if (searchCriteriaRouteNode != null) {
1590 searchCriteriaRouteNodeName = searchCriteriaRouteNode.getRouteNodeName();
1591 }
1592 } catch (java.lang.NumberFormatException e) {
1593 searchCriteriaRouteNodeName = docRouteLevel;
1594 }
1595
1596 StringBuffer routeNodeCriteria = new StringBuffer("and " + ROUTE_NODE_TABLE + ".NM ");
1597 if (KEWConstants.DOC_SEARCH_ROUTE_STATUS_QUALIFIER_EXACT.equalsIgnoreCase(docRouteLevelLogic.trim())) {
1598 routeNodeCriteria.append("= '" + getDbPlatform().escapeString(searchCriteriaRouteNodeName) + "' ");
1599 } else {
1600 routeNodeCriteria.append("in (");
1601
1602 StringBuffer routeNodeInCriteria = new StringBuffer();
1603 boolean foundSpecifiedNode = false;
1604 List<RouteNode> routeNodes = KEWServiceLocator.getRouteNodeService().getFlattenedNodes(getValidDocumentType(documentTypeFullName), true);
1605 for (RouteNode routeNode : routeNodes) {
1606 if (searchCriteriaRouteNodeName.equals(routeNode.getRouteNodeName())) {
1607
1608
1609
1610
1611 foundSpecifiedNode = true;
1612 continue;
1613 }
1614
1615
1616
1617 if ( (!foundSpecifiedNode && (KEWConstants.DOC_SEARCH_ROUTE_STATUS_QUALIFIER_BEFORE.equalsIgnoreCase(docRouteLevelLogic.trim()))) ||
1618 (foundSpecifiedNode && (KEWConstants.DOC_SEARCH_ROUTE_STATUS_QUALIFIER_AFTER.equalsIgnoreCase(docRouteLevelLogic.trim()))) ) {
1619 if (routeNodeInCriteria.length() > 0) {
1620 routeNodeInCriteria.append(", ");
1621 }
1622 routeNodeInCriteria.append("'" + routeNode.getRouteNodeName() + "'");
1623 }
1624 }
1625 if (routeNodeInCriteria.length() > 0) {
1626 routeNodeCriteria.append(routeNodeInCriteria);
1627 } else {
1628 routeNodeCriteria.append("''");
1629 }
1630 routeNodeCriteria.append(") ");
1631 }
1632 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() + " ";
1633 }
1634 return returnSql;
1635 }
1636
1637 public String getDocRouteStatusSql(String docRouteStatuses, String whereClausePredicatePrefix) {
1638 if ((docRouteStatuses == null) || "".equals(docRouteStatuses.trim())) {
1639 return whereClausePredicatePrefix + "DOC_HDR.DOC_HDR_STAT_CD != '" + KEWConstants.ROUTE_HEADER_INITIATED_CD + "'";
1640 } else {
1641
1642
1643 List<String> docRouteStatusList = Arrays.asList(docRouteStatuses.split(","));
1644 String inList = "";
1645
1646 for(String docRouteStatus : docRouteStatusList){
1647 if(KEWConstants.DOCUMENT_STATUS_PARENT_TYPES.containsKey(docRouteStatus)){
1648
1649 for(String docStatusCd : KEWConstants.DOCUMENT_STATUS_PARENT_TYPES.get(docRouteStatus)){
1650 inList += "'" + getDbPlatform().escapeString(docStatusCd.trim()) + "',";
1651 }
1652 } else{
1653 inList += "'" + getDbPlatform().escapeString(docRouteStatus.trim()) + "',";
1654 }
1655 }
1656 inList = inList.substring(0,inList.length()-1);
1657
1658 return whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_STAT_CD in (" + inList +")";
1659 }
1660 }
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673 public String getAppDocStatusSql(String appDocStatus, String whereClausePredicatePrefix, int statusTransitionWhereClauseLength) {
1674 if ((appDocStatus == null) || "".equals(appDocStatus.trim())) {
1675 return "";
1676 } else {
1677 if (statusTransitionWhereClauseLength > 0){
1678 return whereClausePredicatePrefix + " STAT_TRAN.APP_DOC_STAT_TO = '" + getDbPlatform().escapeString(appDocStatus.trim()) + "'";
1679 }else{
1680 return whereClausePredicatePrefix + " DOC_HDR.APP_DOC_STAT = '" + getDbPlatform().escapeString(appDocStatus.trim()) + "'";
1681 }
1682 }
1683 }
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694 public void filterOutNonQueryAttributes() {
1695 List<SearchAttributeCriteriaComponent> newAttributes = new ArrayList<SearchAttributeCriteriaComponent>();
1696 for (SearchAttributeCriteriaComponent component : criteria.getSearchableAttributes()) {
1697 if (component != null) {
1698 if ( (StringUtils.isNotBlank(component.getValue())) || (!CollectionUtils.isEmpty(component.getValues())) ) {
1699 newAttributes.add(component);
1700 }
1701 }
1702 }
1703 criteria.setSearchableAttributes(newAttributes);
1704 }
1705
1706 public String getGeneratedPredicatePrefix(int whereClauseSize) {
1707 return (whereClauseSize > 0) ? " and " : " where ";
1708 }
1709
1710 public String establishDateString(String fromDate, String toDate, String tableName, String tableAlias, String colName, String whereStatementClause) {
1711
1712
1713
1714
1715
1716
1717 String searchVal = "";
1718
1719 if(fromDate != null && !"".equals(fromDate)) {
1720 try {
1721 CoreApiServiceLocator.getDateTimeService().convertToSqlTimestamp(fromDate);
1722 } catch (Exception exc) { throw new RiceRuntimeException("Invalid date format", exc); }
1723 }
1724
1725 if(toDate != null && !"".equals(toDate)){
1726 try{
1727 java.sql.Timestamp dt = CoreApiServiceLocator.getDateTimeService().convertToSqlTimestamp(toDate);
1728 SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");
1729
1730 if("00:00:00".equals(sdfTime.format(dt))){
1731 toDate += " 23:59:59";
1732 }
1733 }
1734 catch (Exception exc) { throw new RiceRuntimeException("Invalid date format", exc); }
1735 }
1736
1737
1738 if(fromDate != null && toDate != null && !"".equals(fromDate) && !"".equals(toDate)){
1739 searchVal = fromDate + " .. " + toDate;
1740 }else{
1741 if(fromDate != null && !"".equals(fromDate)){
1742 searchVal = ">= " + fromDate;
1743 }else if(toDate != null && !"".equals(toDate)){
1744 searchVal = "<= " + toDate;
1745 } else {
1746 searchVal = "";
1747 }
1748 }
1749
1750 if(searchVal == null || "".equals(searchVal)) {
1751 return "";
1752 }
1753
1754 Criteria crit = getSqlBuilder().createCriteria(colName, searchVal, tableName, tableAlias, java.sql.Date.class, true, true);
1755 return new StringBuffer(whereStatementClause + crit.buildWhere()).toString();
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774 }
1775
1776 public int getDocumentSearchResultSetLimit() {
1777 return DEFAULT_SEARCH_RESULT_CAP;
1778 }
1779
1780 public boolean isProcessResultSet(){
1781 return this.isProcessResultSet;
1782 }
1783 public void setProcessResultSet(boolean isProcessResultSet){
1784 this.isProcessResultSet = isProcessResultSet;
1785 }
1786
1787 public DatabasePlatform getDbPlatform() {
1788 if (dbPlatform == null) {
1789 dbPlatform = (DatabasePlatform) GlobalResourceLoader.getService(RiceConstants.DB_PLATFORM);
1790 }
1791 return dbPlatform;
1792 }
1793
1794 public MessageMap getMessageMap(DocSearchCriteriaDTO searchCriteria) {
1795 setCriteria(searchCriteria);
1796 return this.messageMap;
1797 }
1798
1799 private List<String> tokenizeCriteria(String input){
1800 List<String> lRet = null;
1801
1802 lRet = Arrays.asList(input.split("\\|"));
1803
1804 return lRet;
1805 }
1806 private boolean validateNumber(List<String> integers) {
1807 for(String integer: integers){
1808
1809
1810
1811 }
1812 return true;
1813 }
1814
1815
1816
1817
1818 public SqlBuilder getSqlBuilder() {
1819 if(sqlBuilder == null){
1820 sqlBuilder = new SqlBuilder();
1821 sqlBuilder.setDbPlatform(getDbPlatform());
1822 sqlBuilder.setDateTimeService(CoreApiServiceLocator.getDateTimeService());
1823 }
1824 return this.sqlBuilder;
1825 }
1826
1827
1828
1829
1830 public void setSqlBuilder(SqlBuilder sqlBuilder) {
1831 this.sqlBuilder = sqlBuilder;
1832 }
1833
1834
1835
1836
1837
1838
1839
1840 protected boolean isUsingAtLeastOneSearchAttribute() {
1841 return ( (criteria.getSearchableAttributes() != null && criteria.getSearchableAttributes().size() > 0) ||
1842 StringUtils.isNotBlank(criteria.getDocTypeFullName()) );
1843 }
1844
1845 }