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