Source Code Cross Referenced for StandardDocumentSearchGenerator.java in  » ERP-CRM-Financial » Kuali-Financial-System » edu » iu » uis » eden » docsearch » Java Source Code / Java DocumentationJava Source Code and Java Documentation

Java Source Code / Java Documentation
1. 6.0 JDK Core
2. 6.0 JDK Modules
3. 6.0 JDK Modules com.sun
4. 6.0 JDK Modules com.sun.java
5. 6.0 JDK Modules sun
6. 6.0 JDK Platform
7. Ajax
8. Apache Harmony Java SE
9. Aspect oriented
10. Authentication Authorization
11. Blogger System
12. Build
13. Byte Code
14. Cache
15. Chart
16. Chat
17. Code Analyzer
18. Collaboration
19. Content Management System
20. Database Client
21. Database DBMS
22. Database JDBC Connection Pool
23. Database ORM
24. Development
25. EJB Server geronimo
26. EJB Server GlassFish
27. EJB Server JBoss 4.2.1
28. EJB Server resin 3.1.5
29. ERP CRM Financial
30. ESB
31. Forum
32. GIS
33. Graphic Library
34. Groupware
35. HTML Parser
36. IDE
37. IDE Eclipse
38. IDE Netbeans
39. Installer
40. Internationalization Localization
41. Inversion of Control
42. Issue Tracking
43. J2EE
44. JBoss
45. JMS
46. JMX
47. Library
48. Mail Clients
49. Net
50. Parser
51. PDF
52. Portal
53. Profiler
54. Project Management
55. Report
56. RSS RDF
57. Rule Engine
58. Science
59. Scripting
60. Search Engine
61. Security
62. Sevlet Container
63. Source Control
64. Swing Library
65. Template Engine
66. Test Coverage
67. Testing
68. UML
69. Web Crawler
70. Web Framework
71. Web Mail
72. Web Server
73. Web Services
74. Web Services apache cxf 2.0.1
75. Web Services AXIS2
76. Wiki Engine
77. Workflow Engines
78. XML
79. XML UI
Java
Java Tutorial
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL / SQL
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Java Source Code / Java Documentation » ERP CRM Financial » Kuali Financial System » edu.iu.uis.eden.docsearch 
Source Cross Referenced  Class Diagram Java Document (Java Doc) 


0001:        /*
0002:         * Copyright 2005-2006 The Kuali Foundation.
0003:         * 
0004:         * 
0005:         * Licensed under the Educational Community License, Version 1.0 (the "License");
0006:         * you may not use this file except in compliance with the License.
0007:         * You may obtain a copy of the License at
0008:         * 
0009:         * http://www.opensource.org/licenses/ecl1.php
0010:         * 
0011:         * Unless required by applicable law or agreed to in writing, software
0012:         * distributed under the License is distributed on an "AS IS" BASIS,
0013:         * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0014:         * See the License for the specific language governing permissions and
0015:         * limitations under the License.
0016:         */
0017:        package edu.iu.uis.eden.docsearch;
0018:
0019:        import java.sql.ResultSet;
0020:        import java.sql.SQLException;
0021:        import java.util.ArrayList;
0022:        import java.util.Arrays;
0023:        import java.util.Calendar;
0024:        import java.util.Collection;
0025:        import java.util.HashMap;
0026:        import java.util.Iterator;
0027:        import java.util.List;
0028:        import java.util.Map;
0029:
0030:        import org.apache.commons.lang.StringUtils;
0031:
0032:        import edu.iu.uis.eden.EdenConstants;
0033:        import edu.iu.uis.eden.KEWServiceLocator;
0034:        import edu.iu.uis.eden.WorkflowServiceError;
0035:        import edu.iu.uis.eden.WorkflowServiceErrorImpl;
0036:        import edu.iu.uis.eden.doctype.DocumentType;
0037:        import edu.iu.uis.eden.doctype.DocumentTypeService;
0038:        import edu.iu.uis.eden.exception.EdenUserNotFoundException;
0039:        import edu.iu.uis.eden.routetemplate.WorkflowAttributeValidationError;
0040:        import edu.iu.uis.eden.user.AuthenticationUserId;
0041:        import edu.iu.uis.eden.user.WorkflowUser;
0042:        import edu.iu.uis.eden.user.WorkflowUserId;
0043:        import edu.iu.uis.eden.util.Utilities;
0044:        import edu.iu.uis.eden.web.KeyValueSort;
0045:        import edu.iu.uis.eden.workgroup.GroupNameId;
0046:        import edu.iu.uis.eden.workgroup.Workgroup;
0047:
0048:        /**
0049:         *
0050:         * @author delyea
0051:         */
0052:        public class StandardDocumentSearchGenerator implements 
0053:                DocumentSearchGenerator {
0054:            private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger
0055:                    .getLogger(StandardDocumentSearchGenerator.class);
0056:
0057:            private static final String DATABASE_WILDCARD_CHARACTER_STRING = "%";
0058:            private static final char DATABASE_WILDCARD_CHARACTER = DATABASE_WILDCARD_CHARACTER_STRING
0059:                    .toCharArray()[0];
0060:
0061:            private static final String CREATE_DATE_FIELD_STRING = " DOC_HDR.DOC_CRTE_DT ";
0062:            private static final String APPROVE_DATE_FIELD_STRING = " DOC_HDR.DOC_APRV_DT ";
0063:            private static final String FINALIZATION_DATE_FIELD_STRING = " DOC_HDR.DOC_FNL_DT ";
0064:            private static final String LAST_STATUS_UPDATE_DATE = " DOC_HDR.DOC_RTE_STAT_MDFN_DT ";
0065:
0066:            private static List searchableAttributes;
0067:            private static DocSearchCriteriaVO criteria;
0068:            private static WorkflowUser searchingUser;
0069:
0070:            public StandardDocumentSearchGenerator() {
0071:                super ();
0072:                this .searchableAttributes = new ArrayList();
0073:            }
0074:
0075:            /**
0076:             * @param searchableAttributes
0077:             */
0078:            public StandardDocumentSearchGenerator(List searchableAttributes) {
0079:                this ();
0080:                this .searchableAttributes = searchableAttributes;
0081:            }
0082:
0083:            public DocSearchCriteriaVO getCriteria() {
0084:                return criteria;
0085:            }
0086:
0087:            public void setCriteria(DocSearchCriteriaVO criteria) {
0088:                StandardDocumentSearchGenerator.criteria = criteria;
0089:            }
0090:
0091:            public List getSearchableAttributes() {
0092:                return searchableAttributes;
0093:            }
0094:
0095:            public void setSearchableAttributes(List searchableAttributes) {
0096:                this .searchableAttributes = searchableAttributes;
0097:            }
0098:
0099:            public WorkflowUser getSearchingUser() {
0100:                return searchingUser;
0101:            }
0102:
0103:            public void setSearchingUser(WorkflowUser searchingUser) {
0104:                StandardDocumentSearchGenerator.searchingUser = searchingUser;
0105:            }
0106:
0107:            /* (non-Javadoc)
0108:             * @see edu.iu.uis.eden.docsearch.DocumentSearchGenerator#clearSearch(edu.iu.uis.eden.docsearch.DocSearchCriteriaVO)
0109:             */
0110:            public DocSearchCriteriaVO clearSearch(
0111:                    DocSearchCriteriaVO searchCriteria) {
0112:                return new DocSearchCriteriaVO();
0113:            }
0114:
0115:            /* (non-Javadoc)
0116:             * @see edu.iu.uis.eden.docsearch.DocumentSearchGenerator#performPreSearchConditions(edu.iu.uis.eden.user.WorkflowUser, edu.iu.uis.eden.docsearch.DocSearchCriteriaVO)
0117:             */
0118:            public List<WorkflowServiceError> performPreSearchConditions(
0119:                    WorkflowUser user, DocSearchCriteriaVO searchCriteria) {
0120:                setCriteria(searchCriteria);
0121:                return new ArrayList<WorkflowServiceError>();
0122:            }
0123:
0124:            protected SearchAttributeCriteriaComponent getSearchableAttributeByFieldName(
0125:                    String name) {
0126:                if (StringUtils.isBlank(name)) {
0127:                    throw new IllegalArgumentException(
0128:                            "Attempted to find Searchable Attribute with blank Field name '"
0129:                                    + name + "'");
0130:                }
0131:                for (Iterator iter = getCriteria().getSearchableAttributes()
0132:                        .iterator(); iter.hasNext();) {
0133:                    SearchAttributeCriteriaComponent critComponent = (SearchAttributeCriteriaComponent) iter
0134:                            .next();
0135:                    if (name.equals(critComponent.getFormKey())) {
0136:                        return critComponent;
0137:                    }
0138:                }
0139:                return null;
0140:            }
0141:
0142:            protected void addErrorMessageToList(
0143:                    List<WorkflowServiceError> errors, String message) {
0144:                errors.add(new WorkflowServiceErrorImpl(message,
0145:                        "general.message", message));
0146:            }
0147:
0148:            /* (non-Javadoc)
0149:             * @see edu.iu.uis.eden.docsearch.DocumentSearchGenerator#executeSearch(edu.iu.uis.eden.docsearch.DocSearchCriteriaVO, edu.iu.uis.eden.database.platform.Platform)
0150:             */
0151:            public String generateSearchSql(DocSearchCriteriaVO searchCriteria)
0152:                    throws EdenUserNotFoundException {
0153:                setCriteria(searchCriteria);
0154:                return getDocSearchSQL();
0155:            }
0156:
0157:            /* (non-Javadoc)
0158:             * @see edu.iu.uis.eden.docsearch.DocumentSearchGenerator#validateSearchableAttributes()
0159:             */
0160:            public List<WorkflowServiceError> validateSearchableAttributes(
0161:                    DocSearchCriteriaVO searchCriteria) {
0162:                setCriteria(searchCriteria);
0163:                List<WorkflowServiceError> errors = new ArrayList<WorkflowServiceError>();
0164:                List searchableAttributes = criteria.getSearchableAttributes();
0165:                if (searchableAttributes != null
0166:                        && !searchableAttributes.isEmpty()) {
0167:                    Map paramMap = new HashMap();
0168:                    for (Iterator iter = searchableAttributes.iterator(); iter
0169:                            .hasNext();) {
0170:                        SearchAttributeCriteriaComponent component = (SearchAttributeCriteriaComponent) iter
0171:                                .next();
0172:                        if (!Utilities.isEmpty(component.getValues())) {
0173:                            paramMap.put(component.getFormKey(), component
0174:                                    .getValues());
0175:                        } else {
0176:                            paramMap.put(component.getFormKey(), component
0177:                                    .getValue());
0178:                        }
0179:                    }
0180:                    DocumentType documentType = KEWServiceLocator
0181:                            .getDocumentTypeService().findByName(
0182:                                    criteria.getDocTypeFullName());
0183:                    try {
0184:                        for (Iterator iter = documentType
0185:                                .getSearchableAttributes().iterator(); iter
0186:                                .hasNext();) {
0187:                            SearchableAttribute searchableAttribute = (SearchableAttribute) iter
0188:                                    .next();
0189:                            List searchableErrors = validateSearchableAttribute(
0190:                                    searchableAttribute, paramMap);
0191:                            if (!Utilities.isEmpty(searchableAttributes)) {
0192:                                for (Iterator iterator = searchableErrors
0193:                                        .iterator(); iterator.hasNext();) {
0194:                                    WorkflowAttributeValidationError error = (WorkflowAttributeValidationError) iterator
0195:                                            .next();
0196:                                    errors.add(new WorkflowServiceErrorImpl(
0197:                                            error.getKey(),
0198:                                            "routetemplate.xmlattribute.error",
0199:                                            error.getMessage()));
0200:                                }
0201:                            }
0202:                        }
0203:                    } catch (Exception e) {
0204:                        LOG
0205:                                .error(
0206:                                        "error finding searchable attribute in when validating document search criteria.",
0207:                                        e);
0208:                    }
0209:                }
0210:                return errors;
0211:            }
0212:
0213:            public List<WorkflowAttributeValidationError> validateSearchableAttribute(
0214:                    SearchableAttribute searchableAttribute,
0215:                    Map searchAttributesParameterMap) {
0216:                return searchableAttribute
0217:                        .validateUserSearchInputs(searchAttributesParameterMap);
0218:            }
0219:
0220:            protected QueryComponent getSearchableAttributeSql(
0221:                    List searchableAttributes, String whereClausePredicatePrefix) {
0222:                StringBuffer fromSql = new StringBuffer();
0223:                StringBuffer whereSql = new StringBuffer();
0224:
0225:                int tableIndex = 1;
0226:
0227:                Map<String, List> searchableAttributeRangeComponents = new HashMap<String, List>();
0228:                for (Iterator iterator = searchableAttributes.iterator(); iterator
0229:                        .hasNext(); tableIndex++) {
0230:                    SearchAttributeCriteriaComponent criteriaComponent = (SearchAttributeCriteriaComponent) iterator
0231:                            .next();
0232:                    if (!criteriaComponent.isSearchable()) {
0233:                        continue;
0234:                    }
0235:
0236:                    SearchableAttributeValue searchAttribute = criteriaComponent
0237:                            .getSearchableAttributeValue();
0238:                    if (searchAttribute == null) {
0239:                        // key given for propertyField must not be on document
0240:                        String errorMsg = "The search attribute value associated with key '"
0241:                                + criteriaComponent.getSavedKey()
0242:                                + "' cannot be found";
0243:                        LOG.error("getSearchableAttributeSql() " + errorMsg);
0244:                        throw new RuntimeException(errorMsg);
0245:                    }
0246:                    if (criteriaComponent.isRangeSearch()) {
0247:                        if (searchableAttributeRangeComponents
0248:                                .containsKey(criteriaComponent.getSavedKey())) {
0249:                            List<SearchAttributeCriteriaComponent> criteriaComponents = (List) searchableAttributeRangeComponents
0250:                                    .get(criteriaComponent.getSavedKey());
0251:                            List<SearchAttributeCriteriaComponent> newCriteriaComponents = new ArrayList<SearchAttributeCriteriaComponent>();
0252:                            newCriteriaComponents.addAll(criteriaComponents);
0253:                            newCriteriaComponents.add(criteriaComponent);
0254:                            searchableAttributeRangeComponents.put(
0255:                                    criteriaComponent.getSavedKey(),
0256:                                    newCriteriaComponents);
0257:                        } else {
0258:                            searchableAttributeRangeComponents
0259:                                    .put(
0260:                                            criteriaComponent.getSavedKey(),
0261:                                            Arrays
0262:                                                    .asList(new SearchAttributeCriteriaComponent[] { criteriaComponent }));
0263:                        }
0264:                        continue;
0265:                    }
0266:                    // if where clause is empty then use passed in prefix... otherwise generate one
0267:                    String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix
0268:                            : getGeneratedPredicatePrefix(whereSql.length());
0269:                    QueryComponent qc = generateSearchableAttributeSql(
0270:                            criteriaComponent, whereClausePrefix, tableIndex);
0271:                    fromSql.append(qc.getFromSql());
0272:                    whereSql.append(qc.getWhereSql());
0273:
0274:                }
0275:
0276:                for (Iterator iter = searchableAttributeRangeComponents
0277:                        .keySet().iterator(); iter.hasNext();) {
0278:                    String keyName = (String) iter.next();
0279:                    List criteriaComponents = searchableAttributeRangeComponents
0280:                            .get(keyName);
0281:                    // if where clause is empty then use passed in prefix... otherwise generate one
0282:                    String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix
0283:                            : getGeneratedPredicatePrefix(whereSql.length());
0284:                    QueryComponent qc = generateSearchableAttributeRangeSql(
0285:                            keyName, criteriaComponents, whereClausePrefix,
0286:                            tableIndex);
0287:                    fromSql.append(qc.getFromSql());
0288:                    whereSql.append(qc.getWhereSql());
0289:                }
0290:
0291:                QueryComponent qc = new QueryComponent("", fromSql.toString(),
0292:                        whereSql.toString());
0293:                return qc;
0294:            }
0295:
0296:            protected QueryComponent generateSearchableAttributeSql(
0297:                    SearchAttributeCriteriaComponent criteriaComponent,
0298:                    String whereSqlStarter, int tableIndex) {
0299:                String tableIdentifier = "EXT" + tableIndex;
0300:                String queryTableColumnName = tableIdentifier
0301:                        + ".DOC_HDR_EXT_VAL";
0302:                QueryComponent joinSqlComponent = getSearchableAttributeJoinSql(
0303:                        criteriaComponent.getSearchableAttributeValue(),
0304:                        tableIdentifier, whereSqlStarter, criteriaComponent
0305:                                .getSavedKey());
0306:                StringBuffer fromSql = new StringBuffer(joinSqlComponent
0307:                        .getFromSql());
0308:                StringBuffer whereSql = new StringBuffer(joinSqlComponent
0309:                        .getWhereSql());
0310:
0311:                whereSql.append(generateSearchableAttributeDefaultWhereSql(
0312:                        criteriaComponent, queryTableColumnName));
0313:
0314:                return new QueryComponent("", fromSql.toString(), whereSql
0315:                        .toString());
0316:            }
0317:
0318:            protected QueryComponent generateSearchableAttributeRangeSql(
0319:                    String searchAttributeKeyName, List criteriaComponents,
0320:                    String whereSqlStarter, int tableIndex) {
0321:                StringBuffer fromSql = new StringBuffer();
0322:                StringBuffer whereSql = new StringBuffer();
0323:                boolean joinAlreadyPerformed = false;
0324:                String tableIdentifier = "EXT" + tableIndex;
0325:                String queryTableColumnName = tableIdentifier
0326:                        + ".DOC_HDR_EXT_VAL";
0327:
0328:                for (Iterator iter = criteriaComponents.iterator(); iter
0329:                        .hasNext();) {
0330:                    SearchAttributeCriteriaComponent criteriaComponent = (SearchAttributeCriteriaComponent) iter
0331:                            .next();
0332:                    if (!searchAttributeKeyName.equals(criteriaComponent
0333:                            .getSavedKey())) {
0334:                        String errorMsg = "Key value of searchable attribute component with savedKey '"
0335:                                + criteriaComponent.getSavedKey()
0336:                                + "' does not match specified savedKey value '"
0337:                                + searchAttributeKeyName + "'";
0338:                        LOG.error("generateSearchableAttributeRangeSql() "
0339:                                + errorMsg);
0340:                        throw new RuntimeException(errorMsg);
0341:                    }
0342:                    if (!joinAlreadyPerformed) {
0343:                        QueryComponent joinSqlComponent = getSearchableAttributeJoinSql(
0344:                                criteriaComponent.getSearchableAttributeValue(),
0345:                                tableIdentifier, whereSqlStarter,
0346:                                searchAttributeKeyName);
0347:                        fromSql.append(joinSqlComponent.getFromSql());
0348:                        whereSql.append(joinSqlComponent.getWhereSql());
0349:                        joinAlreadyPerformed = true;
0350:                    }
0351:                    whereSql.append(generateSearchableAttributeDefaultWhereSql(
0352:                            criteriaComponent, queryTableColumnName));
0353:                }
0354:
0355:                return new QueryComponent("", fromSql.toString(), whereSql
0356:                        .toString());
0357:            }
0358:
0359:            protected StringBuffer generateSearchableAttributeDefaultWhereSql(
0360:                    SearchAttributeCriteriaComponent criteriaComponent,
0361:                    String queryTableColumnName) {
0362:                StringBuffer whereSql = new StringBuffer();
0363:                String initialClauseStarter = "and";
0364:                //        whereSql.append(" " + initialClauseStarter + " ");
0365:
0366:                boolean valueIsDate = (criteriaComponent
0367:                        .getSearchableAttributeValue() instanceof  SearchableAttributeDateTimeValue);
0368:                boolean valueIsString = (criteriaComponent
0369:                        .getSearchableAttributeValue() instanceof  SearchableAttributeStringValue);
0370:                boolean addCaseInsensitivityForValue = (!criteriaComponent
0371:                        .isCaseSensitive())
0372:                        && criteriaComponent.getSearchableAttributeValue()
0373:                                .allowsCaseInsensitivity();
0374:                String attributeValueSearched = criteriaComponent.getValue();
0375:                List<String> attributeValuesSearched = criteriaComponent
0376:                        .getValues();
0377:
0378:                StringBuffer whereSqlTemp = new StringBuffer();
0379:                if (valueIsDate) {
0380:                    if (criteriaComponent.isRangeSearch()) {
0381:                        // for a range search just add the criteria
0382:                        whereSqlTemp.append(constructWhereClauseDateElement(
0383:                                initialClauseStarter, queryTableColumnName,
0384:                                criteriaComponent.isSearchInclusive(),
0385:                                criteriaComponent.isComponentLowerBoundValue(),
0386:                                attributeValueSearched));
0387:                    } else {
0388:                        if (!Utilities.isEmpty(attributeValuesSearched)) {
0389:                            // for a multivalue date search we need multiple ranges entered
0390:                            whereSqlTemp.append(initialClauseStarter).append(
0391:                                    " (");
0392:                            boolean firstValue = true;
0393:                            for (Iterator iter = attributeValuesSearched
0394:                                    .iterator(); iter.hasNext();) {
0395:                                String attributeValueEntered = (String) iter
0396:                                        .next();
0397:                                whereSqlTemp.append(" ( ");
0398:                                whereSqlTemp
0399:                                        .append(constructWhereClauseDateElement(
0400:                                                "", queryTableColumnName,
0401:                                                criteriaComponent
0402:                                                        .isSearchInclusive(),
0403:                                                true, attributeValueEntered));
0404:                                whereSqlTemp
0405:                                        .append(constructWhereClauseDateElement(
0406:                                                "and", queryTableColumnName,
0407:                                                criteriaComponent
0408:                                                        .isSearchInclusive(),
0409:                                                false, attributeValueEntered));
0410:                                whereSqlTemp.append(" ) ");
0411:                                String separator = " or ";
0412:                                if (firstValue) {
0413:                                    firstValue = false;
0414:                                    separator = "";
0415:                                }
0416:                                whereSqlTemp.append(separator);
0417:                            }
0418:                            whereSqlTemp.append(") ");
0419:                        } else {
0420:                            // below is a search for a single date field.... must do a range of 'time' so we can find any value regardless of the time associated with it
0421:                            whereSqlTemp
0422:                                    .append(constructWhereClauseDateElement(
0423:                                            initialClauseStarter,
0424:                                            queryTableColumnName,
0425:                                            criteriaComponent
0426:                                                    .isSearchInclusive(), true,
0427:                                            attributeValueSearched));
0428:                            whereSqlTemp
0429:                                    .append(constructWhereClauseDateElement(
0430:                                            initialClauseStarter,
0431:                                            queryTableColumnName,
0432:                                            criteriaComponent
0433:                                                    .isSearchInclusive(),
0434:                                            false, attributeValueSearched));
0435:                        }
0436:                    }
0437:                } else {
0438:                    boolean usingWildcards = false;
0439:                    StringBuffer prefix = new StringBuffer("");
0440:                    StringBuffer suffix = new StringBuffer("");
0441:                    if (valueIsString) {
0442:                        prefix.append("'");
0443:                        suffix.insert(0, "'");
0444:                    }
0445:                    if (criteriaComponent.isAllowWildcards()
0446:                            && criteriaComponent.getSearchableAttributeValue()
0447:                                    .allowsWildcards()) {
0448:                        if (!Utilities.isEmpty(attributeValuesSearched)) {
0449:                            List<String> newList = new ArrayList<String>();
0450:                            for (Iterator iter = attributeValuesSearched
0451:                                    .iterator(); iter.hasNext();) {
0452:                                String attributeValueEntered = (String) iter
0453:                                        .next();
0454:                                newList.add(attributeValueEntered.trim()
0455:                                        .replace('*',
0456:                                                DATABASE_WILDCARD_CHARACTER));
0457:                                usingWildcards |= (attributeValueEntered
0458:                                        .indexOf(DATABASE_WILDCARD_CHARACTER_STRING) != -1);
0459:                            }
0460:                            attributeValuesSearched = newList;
0461:                        } else {
0462:                            attributeValueSearched = attributeValueSearched
0463:                                    .trim().replace('*',
0464:                                            DATABASE_WILDCARD_CHARACTER);
0465:                            usingWildcards |= (attributeValueSearched
0466:                                    .indexOf(DATABASE_WILDCARD_CHARACTER_STRING) != -1);
0467:                        }
0468:                        if (criteriaComponent.isAutoWildcardBeginning()) {
0469:                            usingWildcards |= true;
0470:                            if (prefix.length() == 0) {
0471:                                prefix.append("'"
0472:                                        + DATABASE_WILDCARD_CHARACTER_STRING);
0473:                            } else {
0474:                                prefix
0475:                                        .append(DATABASE_WILDCARD_CHARACTER_STRING);
0476:                            }
0477:                        }
0478:                        if (criteriaComponent.isAutoWildcardEnd()) {
0479:                            usingWildcards |= true;
0480:                            if (suffix.length() == 0) {
0481:                                suffix.insert(0,
0482:                                        DATABASE_WILDCARD_CHARACTER_STRING
0483:                                                + "'");
0484:                            } else {
0485:                                suffix.insert(0,
0486:                                        DATABASE_WILDCARD_CHARACTER_STRING);
0487:                            }
0488:                        }
0489:                    }
0490:                    String prefixToUse = prefix.toString();
0491:                    String suffixToUse = suffix.toString();
0492:                    if (addCaseInsensitivityForValue) {
0493:                        queryTableColumnName = "upper(" + queryTableColumnName
0494:                                + ")";
0495:                        prefixToUse = "upper(" + prefix.toString();
0496:                        suffixToUse = suffix.toString() + ")";
0497:                    }
0498:                    if (!Utilities.isEmpty(attributeValuesSearched)) {
0499:                        // for a multivalue search we need multiple 'or' clause statements entered
0500:                        whereSqlTemp.append(initialClauseStarter).append(" (");
0501:                        boolean firstValue = true;
0502:                        for (Iterator iter = attributeValuesSearched.iterator(); iter
0503:                                .hasNext();) {
0504:                            String attributeValueEntered = (String) iter.next();
0505:                            String separator = " or ";
0506:                            if (firstValue) {
0507:                                firstValue = false;
0508:                                separator = "";
0509:                            }
0510:                            String sqlOperand = getSqlOperand(criteriaComponent
0511:                                    .isRangeSearch(), criteriaComponent
0512:                                    .isSearchInclusive(), (criteriaComponent
0513:                                    .isRangeSearch() && criteriaComponent
0514:                                    .isComponentLowerBoundValue()),
0515:                                    usingWildcards);
0516:                            whereSqlTemp.append(constructWhereClauseElement(
0517:                                    separator, queryTableColumnName,
0518:                                    sqlOperand, attributeValueEntered,
0519:                                    prefixToUse, suffixToUse));
0520:                        }
0521:                        whereSqlTemp.append(") ");
0522:                    } else {
0523:                        String sqlOperand = getSqlOperand(criteriaComponent
0524:                                .isRangeSearch(), criteriaComponent
0525:                                .isSearchInclusive(), (criteriaComponent
0526:                                .isRangeSearch() && criteriaComponent
0527:                                .isComponentLowerBoundValue()), usingWildcards);
0528:                        whereSqlTemp.append(constructWhereClauseElement(
0529:                                initialClauseStarter, queryTableColumnName,
0530:                                sqlOperand, attributeValueSearched,
0531:                                prefixToUse, suffixToUse));
0532:                    }
0533:                }
0534:                whereSqlTemp.append(" ");
0535:                return whereSql.append(whereSqlTemp);
0536:            }
0537:
0538:            private QueryComponent getSearchableAttributeJoinSql(
0539:                    SearchableAttributeValue attributeValue,
0540:                    String tableIdentifier, String whereSqlStarter,
0541:                    String attributeTableKeyColumnName) {
0542:                return new QueryComponent("",
0543:                        generateSearchableAttributeFromSql(attributeValue,
0544:                                tableIdentifier).toString(),
0545:                        generateSearchableAttributeWhereClauseJoin(
0546:                                whereSqlStarter, tableIdentifier,
0547:                                attributeTableKeyColumnName).toString());
0548:            }
0549:
0550:            private StringBuffer generateSearchableAttributeWhereClauseJoin(
0551:                    String whereSqlStarter, String tableIdentifier,
0552:                    String attributeTableKeyColumnName) {
0553:                StringBuffer whereSql = new StringBuffer(
0554:                        constructWhereClauseElement(whereSqlStarter,
0555:                                "DOC_HDR.DOC_HDR_ID", "=", tableIdentifier
0556:                                        + ".DOC_HDR_ID", null, null));
0557:                whereSql.append(constructWhereClauseElement(" and ",
0558:                        tableIdentifier + ".DOC_HDR_EXT_VAL_KEY", "=",
0559:                        attributeTableKeyColumnName, "'", "'"));
0560:                return whereSql;
0561:            }
0562:
0563:            private StringBuffer generateSearchableAttributeFromSql(
0564:                    SearchableAttributeValue attributeValue,
0565:                    String tableIdentifier) {
0566:                StringBuffer fromSql = new StringBuffer();
0567:                String tableName = attributeValue.getAttributeTableName();
0568:                if (StringUtils.isBlank(tableName)) {
0569:                    String errorMsg = "The table name associated with Searchable Attribute with class '"
0570:                            + attributeValue.getClass()
0571:                            + "' returns as '"
0572:                            + tableName + "'";
0573:                    LOG.error("getSearchableAttributeSql() " + errorMsg);
0574:                    throw new RuntimeException(errorMsg);
0575:                }
0576:                fromSql.append(" ," + tableName + " " + tableIdentifier + " ");
0577:                return fromSql;
0578:            }
0579:
0580:            private StringBuffer constructWhereClauseDateElement(
0581:                    String clauseStarter, String queryTableColumnName,
0582:                    boolean inclusive, boolean valueIsLowerBound,
0583:                    String dateValueToSearch) {
0584:                StringBuffer sqlOperand = new StringBuffer(getSqlOperand(true,
0585:                        inclusive, valueIsLowerBound, false));
0586:                String timeValueToSearch = null;
0587:                if (valueIsLowerBound) {
0588:                    timeValueToSearch = "00:00:00";
0589:                } else {
0590:                    timeValueToSearch = "23:59:59";
0591:                }
0592:                return new StringBuffer().append(constructWhereClauseElement(
0593:                        clauseStarter, queryTableColumnName, sqlOperand
0594:                                .toString(), DocSearchUtils.getDateSQL(
0595:                                DocSearchUtils
0596:                                        .getSqlFormattedDate(dateValueToSearch
0597:                                                .trim()), timeValueToSearch
0598:                                        .trim()), "", ""));
0599:            }
0600:
0601:            private StringBuffer constructWhereClauseElement(
0602:                    String clauseStarter, String queryTableColumnName,
0603:                    String operand, String valueToSearch, String valuePrefix,
0604:                    String valueSuffix) {
0605:                StringBuffer whereSql = new StringBuffer();
0606:                valuePrefix = (valuePrefix != null) ? valuePrefix : "";
0607:                valueSuffix = (valueSuffix != null) ? valueSuffix : "";
0608:                whereSql.append(" " + clauseStarter + " ").append(
0609:                        queryTableColumnName).append(" " + operand + " ")
0610:                        .append(valuePrefix).append(valueToSearch).append(
0611:                                valueSuffix).append(" ");
0612:                return whereSql;
0613:            }
0614:
0615:            /**
0616:             * For the following we first check for a ranged search because a ranged search
0617:             * does not allow for wildcards
0618:             */
0619:            private String getSqlOperand(boolean rangeSearch,
0620:                    boolean inclusive, boolean valueIsLowerBound,
0621:                    boolean usingWildcards) {
0622:                StringBuffer sqlOperand = new StringBuffer("=");
0623:                if (rangeSearch) {
0624:                    if (valueIsLowerBound) {
0625:                        sqlOperand = new StringBuffer(">");
0626:                    } else {
0627:                        sqlOperand = new StringBuffer("<");
0628:                    }
0629:                    if (inclusive) {
0630:                        sqlOperand.append("=");
0631:                    }
0632:
0633:                } else if (usingWildcards) {
0634:                    sqlOperand = new StringBuffer("like");
0635:                }
0636:                return sqlOperand.toString();
0637:            }
0638:
0639:            /**
0640:             * @param resultSet
0641:             * @param criteria
0642:             * @return
0643:             * @throws EdenUserNotFoundException
0644:             * @throws SQLException
0645:             */
0646:            public List<DocSearchVO> processResultSet(ResultSet resultSet,
0647:                    DocSearchCriteriaVO searchCriteria)
0648:                    throws EdenUserNotFoundException, SQLException {
0649:                setCriteria(searchCriteria);
0650:                int size = 0;
0651:                List docList = new ArrayList();
0652:                Map resultMap = new HashMap();
0653:                //        while ((size < DocSearchCriteriaVO.SEARCH_RESULT_CAP) && resultSet.next()) {
0654:                while ((resultMap.size() < DocSearchCriteriaVO.SEARCH_RESULT_CAP)
0655:                        && resultSet.next()) {
0656:                    DocSearchVO docSearchVO = processRow(resultSet);
0657:                    docSearchVO.setSuperUserSearch(getCriteria()
0658:                            .getSuperUserSearch());
0659:                    if (!resultMap.containsKey(docSearchVO.getRouteHeaderId())) {
0660:                        docList.add(docSearchVO);
0661:                        resultMap.put(docSearchVO.getRouteHeaderId(),
0662:                                docSearchVO);
0663:                    } else {
0664:                        // handle duplicate rows with different search data
0665:                        DocSearchVO previousEntry = (DocSearchVO) resultMap
0666:                                .get(docSearchVO.getRouteHeaderId());
0667:                        handleMultipleDocumentRows(previousEntry, docSearchVO);
0668:                    }
0669:                    size++;
0670:                }
0671:                // if we have threshold+1 results, then we have more results than we are going to display
0672:                criteria.setOverThreshold(resultSet.next());
0673:                LOG
0674:                        .debug("Processed " + size
0675:                                + " document search result rows.");
0676:                return docList;
0677:            }
0678:
0679:            /**
0680:             * Handles multiple document rows by collapsing them and their data into the searchable attribute columns.
0681:             * 
0682:             * TODO this is currently concatenating strings together with HTML elements, this seems bad in this location,
0683:             * perhaps we should move this to the web layer (and perhaps enhance the searchable attributes
0684:             * portion of the DocSearchVO data structure?)
0685:             */
0686:            private void handleMultipleDocumentRows(DocSearchVO existingRow,
0687:                    DocSearchVO newRow) {
0688:
0689:                for (Iterator iterator = newRow.getSearchableAttributes()
0690:                        .iterator(); iterator.hasNext();) {
0691:                    KeyValueSort newData = (KeyValueSort) iterator.next();
0692:                    String newRowValue = newData.getValue();
0693:                    boolean foundMatch = false;
0694:                    for (Iterator dataIt = existingRow
0695:                            .getSearchableAttributes().iterator(); dataIt
0696:                            .hasNext();) {
0697:                        KeyValueSort existingData = (KeyValueSort) dataIt
0698:                                .next();
0699:                        if (existingData.getKey().equals(newData.getKey())) {
0700:                            String existingRowValue = existingData.getValue();
0701:                            if (!Utilities.isEmpty(newRowValue)) {
0702:                                String valueToSet = "";
0703:                                if (Utilities.isEmpty(existingRowValue)) {
0704:                                    valueToSet = newRowValue;
0705:                                } else {
0706:                                    valueToSet = existingRowValue + "<br>"
0707:                                            + newRowValue;
0708:                                }
0709:                                existingData.setvalue(valueToSet);
0710:                                if ((existingData.getSortValue() == null)
0711:                                        && (newData.getSortValue() != null)) {
0712:                                    existingData.setSortValue(newData
0713:                                            .getSortValue());
0714:                                }
0715:                            }
0716:                            foundMatch = true;
0717:                        }
0718:                    }
0719:                    if (!foundMatch) {
0720:                        existingRow.addSearchableAttribute(new KeyValueSort(
0721:                                newData));
0722:                    }
0723:                }
0724:            }
0725:
0726:            private DocSearchVO processRow(ResultSet rs) throws SQLException,
0727:                    EdenUserNotFoundException {
0728:                DocSearchVO docSearchVO = new DocSearchVO();
0729:
0730:                docSearchVO
0731:                        .setRouteHeaderId(new Long(rs.getLong("DOC_HDR_ID")));
0732:
0733:                String docTypeLabel = rs.getString("DOC_TYP_LBL_TXT");
0734:                String activeIndicatorCode = rs.getString("DOC_TYP_ACTV_IND");
0735:
0736:                docSearchVO.setDocRouteStatusCode(rs
0737:                        .getString("DOC_RTE_STAT_CD"));
0738:                docSearchVO.setDateCreated(rs.getTimestamp("DOC_CRTE_DT"));
0739:                docSearchVO.setDocumentTitle(rs.getString("DOC_TTL"));
0740:                docSearchVO.setDocTypeLabel(docTypeLabel);
0741:
0742:                if ((activeIndicatorCode == null)
0743:                        || (activeIndicatorCode.trim().length() == 0)) {
0744:                    docSearchVO.setActiveIndicatorCode(EdenConstants.ACTIVE_CD);
0745:                } else {
0746:                    docSearchVO.setActiveIndicatorCode(activeIndicatorCode);
0747:                }
0748:
0749:                if ((docTypeLabel == null)
0750:                        || (docTypeLabel.trim().length() == 0)) {
0751:                    docSearchVO.setDocTypeHandlerUrl("");
0752:                } else {
0753:                    docSearchVO.setDocTypeHandlerUrl(rs
0754:                            .getString("DOC_TYP_HDLR_URL_ADDR"));
0755:                }
0756:
0757:                docSearchVO.setInitiatorWorkflowId(rs
0758:                        .getString("DOC_INITR_PRSN_EN_ID"));
0759:
0760:                WorkflowUser user = KEWServiceLocator.getUserService()
0761:                        .getWorkflowUser(
0762:                                new WorkflowUserId(docSearchVO
0763:                                        .getInitiatorWorkflowId()));
0764:
0765:                docSearchVO.setInitiatorNetworkId(user
0766:                        .getAuthenticationUserId().getAuthenticationId());
0767:                docSearchVO.setInitiatorName(user.getDisplayName());
0768:                docSearchVO.setInitiatorFirstName(user.getGivenName());
0769:                docSearchVO.setInitiatorLastName(user.getLastName());
0770:                docSearchVO
0771:                        .setInitiatorTransposedName(user.getTransposedName());
0772:                docSearchVO.setInitiatorEmailAddress(user.getEmailAddress());
0773:
0774:                List searchAttributeValues = DocSearchUtils
0775:                        .getSearchableAttributeValueObjectTypes();
0776:                for (Iterator iter = searchAttributeValues.iterator(); iter
0777:                        .hasNext();) {
0778:                    SearchableAttributeValue searchAttValue = (SearchableAttributeValue) iter
0779:                            .next();
0780:                    String prefixName = searchAttValue.getAttributeDataType()
0781:                            .toUpperCase();
0782:                    searchAttValue.setSearchableAttributeKey(rs
0783:                            .getString(prefixName + "_KEY"));
0784:                    searchAttValue.setupAttributeValue(rs, prefixName
0785:                            + "_VALUE");
0786:                    if ((!Utilities.isEmpty(searchAttValue
0787:                            .getSearchableAttributeKey()))
0788:                            && (searchAttValue.getSearchableAttributeValue() != null)) {
0789:                        //                docSearchVO.addSearchableAttribute(new KeyValueSort(searchAttValue.getSearchableAttributeKey(),searchAttValue.getSearchableAttributeDisplayValue(),searchAttValue.getSearchableAttributeValue()));
0790:                        docSearchVO.addSearchableAttribute(new KeyValueSort(
0791:                                searchAttValue.getSearchableAttributeKey(),
0792:                                searchAttValue
0793:                                        .getSearchableAttributeDisplayValue(),
0794:                                searchAttValue.getSearchableAttributeValue(),
0795:                                searchAttValue));
0796:                    }
0797:                }
0798:
0799:                return docSearchVO;
0800:            }
0801:
0802:            protected String getDocSearchSQL() throws EdenUserNotFoundException {
0803:                // the DISTINCT here is important as it filters out duplicate rows which could occur as the result of doc search extension values...
0804:                StringBuffer selectSQL = new StringBuffer(
0805:                        "select DISTINCT(DOC_HDR.DOC_HDR_ID), DOC_HDR.DOC_INITR_PRSN_EN_ID, DOC_HDR.DOC_RTE_STAT_CD, DOC_HDR.DOC_CRTE_DT, DOC_HDR.DOC_TTL, DOC1.DOC_TYP_LBL_TXT, DOC1.DOC_TYP_HDLR_URL_ADDR, DOC1.DOC_TYP_ACTV_IND");
0806:                StringBuffer fromSQL = new StringBuffer(
0807:                        " from EN_DOC_TYP_T DOC1 ");
0808:                String docHeaderTableAlias = "DOC_HDR";
0809:                StringBuffer fromSQLForDocHeaderTable = new StringBuffer(
0810:                        ", EN_DOC_HDR_T " + docHeaderTableAlias + " ");
0811:                StringBuffer whereSQL = new StringBuffer();
0812:                whereSQL.append(getRouteHeaderIdSql(
0813:                        criteria.getRouteHeaderId(),
0814:                        getGeneratedPredicatePrefix(whereSQL.length())));
0815:                whereSQL.append(getInitiatorSql(criteria.getInitiator(),
0816:                        getGeneratedPredicatePrefix(whereSQL.length())));
0817:                whereSQL.append(getAppDocIdSql(criteria.getAppDocId(),
0818:                        getGeneratedPredicatePrefix(whereSQL.length())));
0819:                whereSQL.append(getDateCreatedSql(
0820:                        criteria.getFromDateCreated(), criteria
0821:                                .getToDateCreated(),
0822:                        getGeneratedPredicatePrefix(whereSQL.length())));
0823:                whereSQL.append(getDateLastModifiedSql(criteria
0824:                        .getFromDateLastModified(), criteria
0825:                        .getToDateLastModified(),
0826:                        getGeneratedPredicatePrefix(whereSQL.length())));
0827:                whereSQL.append(getDateApprovedSql(criteria
0828:                        .getFromDateApproved(), criteria.getToDateApproved(),
0829:                        getGeneratedPredicatePrefix(whereSQL.length())));
0830:                whereSQL.append(getDateFinalizedSql(criteria
0831:                        .getFromDateFinalized(), criteria.getToDateFinalized(),
0832:                        getGeneratedPredicatePrefix(whereSQL.length())));
0833:                // flags for the table being added to the FROM class of the sql
0834:                boolean actionTakenTable = false;
0835:                if ((!"".equals(getViewerSql(criteria.getViewer(),
0836:                        getGeneratedPredicatePrefix(whereSQL.length()))))
0837:                        || (!""
0838:                                .equals(getWorkgroupViewerSql(criteria
0839:                                        .getWorkgroupViewerName(),
0840:                                        getGeneratedPredicatePrefix(whereSQL
0841:                                                .length()))))) {
0842:                    whereSQL.append(getViewerSql(criteria.getViewer(),
0843:                            getGeneratedPredicatePrefix(whereSQL.length())));
0844:                    whereSQL.append(getWorkgroupViewerSql(criteria
0845:                            .getWorkgroupViewerName(),
0846:                            getGeneratedPredicatePrefix(whereSQL.length())));
0847:                    fromSQL.append(", EN_ACTN_RQST_T ");
0848:                    actionTakenTable = true;
0849:                }
0850:
0851:                if (!("".equals(getApproverSql(criteria.getApprover(),
0852:                        getGeneratedPredicatePrefix(whereSQL.length()))))) {
0853:                    whereSQL.append(getApproverSql(criteria.getApprover(),
0854:                            getGeneratedPredicatePrefix(whereSQL.length())));
0855:                    if (!actionTakenTable) {
0856:                        fromSQL.append(", EN_ACTN_TKN_T ");
0857:                    }
0858:                    actionTakenTable = true;
0859:                }
0860:
0861:                if (!("".equals(getDocTypeFullNameWhereSql(criteria
0862:                        .getDocTypeFullName(),
0863:                        getGeneratedPredicatePrefix(whereSQL.length()))))) {
0864:                    whereSQL.append(getDocTypeFullNameWhereSql(criteria
0865:                            .getDocTypeFullName(),
0866:                            getGeneratedPredicatePrefix(whereSQL.length())));
0867:                }
0868:
0869:                if (!"".equals(getDocRouteNodeSql(criteria.getDocRouteNodeId(),
0870:                        criteria.getDocRouteNodeLogic(),
0871:                        getGeneratedPredicatePrefix(whereSQL.length())))) {
0872:                    whereSQL.append(getDocRouteNodeSql(criteria
0873:                            .getDocRouteNodeId(), criteria
0874:                            .getDocRouteNodeLogic(),
0875:                            getGeneratedPredicatePrefix(whereSQL.length())));
0876:                    fromSQL.append(", EN_RTE_NODE_INSTN_T ");
0877:                }
0878:
0879:                filterOutNonQueryAttributes();
0880:                if ((criteria.getSearchableAttributes() != null)
0881:                        && (criteria.getSearchableAttributes().size() > 0)) {
0882:                    QueryComponent queryComponent = getSearchableAttributeSql(
0883:                            criteria.getSearchableAttributes(),
0884:                            getGeneratedPredicatePrefix(whereSQL.length()));
0885:                    selectSQL.append(queryComponent.getSelectSql());
0886:                    fromSQL.append(queryComponent.getFromSql());
0887:                    whereSQL.append(queryComponent.getWhereSql());
0888:                }
0889:
0890:                // at this point we haven't appended doc title to the query, if the document title is the only field
0891:                // which was entered, we want to set the "from" date to be X days ago.  This will allow for a
0892:                // more efficient query
0893:                Integer defaultCreateDateDaysAgoValue = null;
0894:                //        whereSQL.append(getDocTitleSql(criteria.getDocTitle(), getGeneratedPredicatePrefix(whereSQL.length())));
0895:                String tempWhereSql = getDocTitleSql(criteria.getDocTitle(),
0896:                        getGeneratedPredicatePrefix(whereSQL.length()));
0897:                if (((whereSQL == null) || (StringUtils.isBlank(whereSQL
0898:                        .toString())))
0899:                        && (StringUtils.isNotBlank(tempWhereSql))) {
0900:                    // doc title is not blank
0901:                    defaultCreateDateDaysAgoValue = EdenConstants.DOCUMENT_SEARCH_DOC_TITLE_CREATE_DATE_DAYS_AGO;
0902:                }
0903:                whereSQL.append(tempWhereSql);
0904:                if (((whereSQL == null) || (StringUtils.isBlank(whereSQL
0905:                        .toString())))
0906:                        && (StringUtils.isBlank(criteria.getDocRouteStatus()))) {
0907:                    // if they haven't set any criteria, default the from created date to today minus days from constant variable
0908:                    defaultCreateDateDaysAgoValue = EdenConstants.DOCUMENT_SEARCH_NO_CRITERIA_CREATE_DATE_DAYS_AGO;
0909:                }
0910:                if (defaultCreateDateDaysAgoValue != null) {
0911:                    // add a default create date
0912:                    Calendar calendar = Calendar.getInstance();
0913:                    calendar.add(Calendar.DATE, defaultCreateDateDaysAgoValue
0914:                            .intValue());
0915:                    criteria.setFromDateCreated(EdenConstants
0916:                            .getDefaultDateFormat().format(calendar.getTime()));
0917:                    whereSQL.append(getDateCreatedSql(criteria
0918:                            .getFromDateCreated(), criteria.getToDateCreated(),
0919:                            getGeneratedPredicatePrefix(whereSQL.length())));
0920:                }
0921:                whereSQL.append(getDocRouteStatusSql(criteria
0922:                        .getDocRouteStatus(),
0923:                        getGeneratedPredicatePrefix(whereSQL.length())));
0924:                whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length()))
0925:                        .append(" DOC_HDR.DOC_TYP_ID = DOC1.DOC_TYP_ID ");
0926:
0927:                String finalizedSql = generateFinalSQL(new QueryComponent(
0928:                        selectSQL.toString(), fromSQL.append(
0929:                                fromSQLForDocHeaderTable).toString(), whereSQL
0930:                                .toString()), docHeaderTableAlias);
0931:                LOG.info("*********** SQL ***************");
0932:                LOG.info(finalizedSql);
0933:                LOG.info("*******************************");
0934:                return finalizedSql;
0935:            }
0936:
0937:            private String generateFinalSQL(QueryComponent searchSQL,
0938:                    String docHeaderTableAlias) {
0939:                String sqlPrefix = "Select * from (";
0940:                String sqlSuffix = ") FINAL_SEARCH order by FINAL_SEARCH.DOC_HDR_ID desc";
0941:                StringBuffer finalSql = new StringBuffer();
0942:                List searchableAttributeValues = DocSearchUtils
0943:                        .getSearchableAttributeValueObjectTypes();
0944:                List tableAliasComponentNames = new ArrayList();
0945:                for (Iterator iterator = searchableAttributeValues.iterator(); iterator
0946:                        .hasNext();) {
0947:                    SearchableAttributeValue attValue = (SearchableAttributeValue) iterator
0948:                            .next();
0949:                    tableAliasComponentNames.add(attValue
0950:                            .getAttributeDataType().toUpperCase());
0951:                }
0952:                for (Iterator iter = searchableAttributeValues.iterator(); iter
0953:                        .hasNext();) {
0954:                    SearchableAttributeValue attributeValue = (SearchableAttributeValue) iter
0955:                            .next();
0956:                    QueryComponent qc = generateSqlForSearchableAttributeValue(
0957:                            attributeValue, tableAliasComponentNames,
0958:                            docHeaderTableAlias);
0959:                    StringBuffer currentSql = new StringBuffer();
0960:                    currentSql.append(searchSQL.getSelectSql()
0961:                            + qc.getSelectSql() + searchSQL.getFromSql()
0962:                            + qc.getFromSql() + searchSQL.getWhereSql()
0963:                            + qc.getWhereSql());
0964:                    if (finalSql.length() == 0) {
0965:                        finalSql.append(sqlPrefix).append(" ( ").append(
0966:                                currentSql);
0967:                    } else {
0968:                        finalSql.append(" ) UNION ( " + currentSql.toString());
0969:                    }
0970:                }
0971:                finalSql.append(" ) " + sqlSuffix);
0972:                return finalSql.toString();
0973:            }
0974:
0975:            private QueryComponent generateSqlForSearchableAttributeValue(
0976:                    SearchableAttributeValue attributeValue,
0977:                    List tableAliasComponentNames, String docHeaderTableAlias) {
0978:                StringBuffer selectSql = new StringBuffer();
0979:                StringBuffer fromSql = new StringBuffer();
0980:                String currentAttributeTableAlias = "SA_"
0981:                        + attributeValue.getAttributeDataType().toUpperCase();
0982:                fromSql.append(" LEFT OUTER JOIN "
0983:                        + attributeValue.getAttributeTableName() + " "
0984:                        + currentAttributeTableAlias + " ON ("
0985:                        + docHeaderTableAlias + ".DOC_HDR_ID = "
0986:                        + currentAttributeTableAlias + ".DOC_HDR_ID)");
0987:                for (Iterator iter = tableAliasComponentNames.iterator(); iter
0988:                        .hasNext();) {
0989:                    String aliasComponentName = (String) iter.next();
0990:                    if (aliasComponentName.equalsIgnoreCase(attributeValue
0991:                            .getAttributeDataType())) {
0992:                        selectSql.append(", " + currentAttributeTableAlias
0993:                                + ".DOC_HDR_EXT_VAL_KEY as "
0994:                                + aliasComponentName + "_KEY, "
0995:                                + currentAttributeTableAlias
0996:                                + ".DOC_HDR_EXT_VAL as " + aliasComponentName
0997:                                + "_VALUE");
0998:                    } else {
0999:                        selectSql.append(", NULL as " + aliasComponentName
1000:                                + "_KEY, NULL as " + aliasComponentName
1001:                                + "_VALUE");
1002:                    }
1003:                }
1004:                return new QueryComponent(selectSql.toString(), fromSql
1005:                        .toString(), "");
1006:            }
1007:
1008:            protected String getRouteHeaderIdSql(String routeHeaderId,
1009:                    String whereClausePredicatePrefix) {
1010:                if ((routeHeaderId == null) || "".equals(routeHeaderId.trim())) {
1011:                    return "";
1012:                } else {
1013:                    Long rh = new Long(routeHeaderId.trim());
1014:                    return new StringBuffer(whereClausePredicatePrefix
1015:                            + " DOC_HDR.DOC_HDR_ID = ").append(rh.intValue())
1016:                            .toString();
1017:                }
1018:            }
1019:
1020:            protected String getInitiatorSql(String initiator,
1021:                    String whereClausePredicatePrefix)
1022:                    throws EdenUserNotFoundException {
1023:                if ((initiator == null) || "".equals(initiator.trim())) {
1024:                    return "";
1025:                } else {
1026:                    String userWorkflowId = KEWServiceLocator.getUserService()
1027:                            .getWorkflowUser(
1028:                                    new AuthenticationUserId(initiator.trim()))
1029:                            .getWorkflowUserId().getWorkflowId();
1030:                    return new StringBuffer(whereClausePredicatePrefix
1031:                            + " DOC_HDR.DOC_INITR_PRSN_EN_ID = '").append(
1032:                            userWorkflowId).append("'").toString();
1033:                }
1034:            }
1035:
1036:            protected String getDocTitleSql(String docTitle,
1037:                    String whereClausePredicatePrefix) {
1038:                if (StringUtils.isBlank(docTitle)) {
1039:                    return "";
1040:                } else {
1041:                    if (!docTitle.trim().endsWith("*")) {
1042:                        docTitle = docTitle.trim().concat("*")
1043:                                .replace('*', '%');
1044:                    } else {
1045:                        docTitle = docTitle.trim().replace('*', '%');
1046:                    }
1047:                    // quick and dirty ' replacement that isn't the best but should work for all dbs
1048:                    docTitle = docTitle.trim().replace('\'', '%');
1049:                    return new StringBuffer(whereClausePredicatePrefix
1050:                            + " upper(DOC_HDR.DOC_TTL) like '%").append(
1051:                            docTitle.toUpperCase()).append("'").toString();
1052:                }
1053:            }
1054:
1055:            // special methods that return the sql needed to complete the search
1056:            // or nothing if the field was not filled in
1057:            protected String getAppDocIdSql(String appDocId,
1058:                    String whereClausePredicatePrefix) {
1059:                if ((appDocId == null) || "".equals(appDocId.trim())) {
1060:                    return "";
1061:                } else {
1062:                    if (!appDocId.trim().endsWith("*")) {
1063:                        appDocId = appDocId.trim().concat("*")
1064:                                .replace('*', '%');
1065:                    } else {
1066:                        appDocId = appDocId.trim().replace('*', '%');
1067:                    }
1068:                    return new StringBuffer(whereClausePredicatePrefix
1069:                            + " upper(DOC_HDR.DOC_APPL_DOC_ID) like '%")
1070:                            .append(appDocId.toUpperCase()).append("'")
1071:                            .toString();
1072:                }
1073:            }
1074:
1075:            protected String getDateCreatedSql(String fromDateCreated,
1076:                    String toDateCreated, String whereClausePredicatePrefix) {
1077:                return establishDateString(fromDateCreated, toDateCreated,
1078:                        CREATE_DATE_FIELD_STRING, whereClausePredicatePrefix);
1079:            }
1080:
1081:            protected String getDateApprovedSql(String fromDateApproved,
1082:                    String toDateApproved, String whereClausePredicatePrefix) {
1083:                return establishDateString(fromDateApproved, toDateApproved,
1084:                        APPROVE_DATE_FIELD_STRING, whereClausePredicatePrefix);
1085:            }
1086:
1087:            protected String getDateFinalizedSql(String fromDateFinalized,
1088:                    String toDateFinalized, String whereClausePredicatePrefix) {
1089:                return establishDateString(fromDateFinalized, toDateFinalized,
1090:                        FINALIZATION_DATE_FIELD_STRING,
1091:                        whereClausePredicatePrefix);
1092:            }
1093:
1094:            protected String getDateLastModifiedSql(
1095:                    String fromDateLastModified, String toDateLastModified,
1096:                    String whereClausePredicatePrefix) {
1097:                return establishDateString(fromDateLastModified,
1098:                        toDateLastModified, LAST_STATUS_UPDATE_DATE,
1099:                        whereClausePredicatePrefix);
1100:            }
1101:
1102:            protected String getViewerSql(String viewer,
1103:                    String whereClausePredicatePrefix)
1104:                    throws EdenUserNotFoundException {
1105:                String returnSql = "";
1106:                if ((viewer != null) && (!"".equals(viewer.trim()))) {
1107:                    WorkflowUser user = KEWServiceLocator.getUserService()
1108:                            .getWorkflowUser(
1109:                                    new AuthenticationUserId(viewer.trim()));
1110:                    String userWorkflowId = user.getWorkflowId();
1111:                    returnSql = whereClausePredicatePrefix
1112:                            + " DOC_HDR.DOC_HDR_ID = EN_ACTN_RQST_T.DOC_HDR_ID and EN_ACTN_RQST_T.ACTN_RQST_PRSN_EN_ID = '"
1113:                            + userWorkflowId + "'";
1114:                }
1115:                return returnSql;
1116:            }
1117:
1118:            protected String getWorkgroupViewerSql(String workgroupName,
1119:                    String whereClausePredicatePrefix) {
1120:                String sql = "";
1121:                if (!Utilities.isEmpty(workgroupName)) {
1122:                    Workgroup workgroup = KEWServiceLocator
1123:                            .getWorkgroupService().getWorkgroup(
1124:                                    new GroupNameId(workgroupName));
1125:                    sql = whereClausePredicatePrefix
1126:                            + " DOC_HDR.DOC_HDR_ID = EN_ACTN_RQST_T.DOC_HDR_ID and EN_ACTN_RQST_T.WRKGRP_ID = "
1127:                            + workgroup.getWorkflowGroupId().getGroupId();
1128:                }
1129:                return sql;
1130:            }
1131:
1132:            protected String getApproverSql(String approver,
1133:                    String whereClausePredicatePrefix)
1134:                    throws EdenUserNotFoundException {
1135:                String returnSql = "";
1136:                if ((approver != null) && (!"".equals(approver.trim()))) {
1137:                    String userWorkflowId = KEWServiceLocator.getUserService()
1138:                            .getWorkflowUser(
1139:                                    new AuthenticationUserId(approver.trim()))
1140:                            .getWorkflowUserId().getWorkflowId();
1141:                    returnSql = whereClausePredicatePrefix
1142:                            + " DOC_HDR.DOC_HDR_ID = EN_ACTN_TKN_T.DOC_HDR_ID and upper(EN_ACTN_TKN_T.ACTN_TKN_CD) = '"
1143:                            + EdenConstants.ACTION_TAKEN_APPROVED_CD
1144:                            + "' and EN_ACTN_TKN_T.ACTN_TKN_PRSN_EN_ID = '"
1145:                            + userWorkflowId + "'";
1146:                }
1147:                return returnSql;
1148:            }
1149:
1150:            protected String getDocTypeFullNameWhereSql(String docTypeFullName,
1151:                    String whereClausePredicatePrefix) {
1152:                StringBuffer returnSql = new StringBuffer("");
1153:                if ((docTypeFullName != null)
1154:                        && (!"".equals(docTypeFullName.trim()))) {
1155:                    DocumentTypeService docSrv = (DocumentTypeService) KEWServiceLocator
1156:                            .getDocumentTypeService();
1157:                    DocumentType docType = docSrv.findByName(docTypeFullName
1158:                            .trim());
1159:                    if (docType != null) {
1160:                        returnSql.append(whereClausePredicatePrefix)
1161:                                .append("(");
1162:                        addDocumentTypeNameToSearchOn(returnSql, docType
1163:                                .getName(), "");
1164:                        if (docType.getChildrenDocTypes() != null) {
1165:                            addChildDocumentTypes(returnSql, docType
1166:                                    .getChildrenDocTypes());
1167:                        }
1168:                        addExtraDocumentTypesToSearch(returnSql, docType);
1169:                        returnSql.append(")");
1170:                    }
1171:                }
1172:                return returnSql.toString();
1173:            }
1174:
1175:            private void addChildDocumentTypes(StringBuffer whereSql,
1176:                    Collection childDocumentTypes) {
1177:                for (Iterator iter = childDocumentTypes.iterator(); iter
1178:                        .hasNext();) {
1179:                    DocumentType child = (DocumentType) iter.next();
1180:                    addDocumentTypeNameToSearchOn(whereSql, child.getName());
1181:                    addChildDocumentTypes(whereSql, child.getChildrenDocTypes());
1182:                }
1183:            }
1184:
1185:            protected void addExtraDocumentTypesToSearch(StringBuffer whereSql,
1186:                    DocumentType docType) {
1187:            }
1188:
1189:            protected void addDocumentTypeNameToSearchOn(StringBuffer whereSql,
1190:                    String documentTypeName) {
1191:                this .addDocumentTypeNameToSearchOn(whereSql, documentTypeName,
1192:                        " or ");
1193:            }
1194:
1195:            private void addDocumentTypeNameToSearchOn(StringBuffer whereSql,
1196:                    String documentTypeName, String clause) {
1197:                whereSql.append(clause).append(
1198:                        " DOC1.DOC_TYP_NM = '" + documentTypeName + "'");
1199:            }
1200:
1201:            protected String getDocRouteNodeSql(String docRouteLevel,
1202:                    String docRouteLevelLogic, String whereClausePredicatePrefix) {
1203:                // -1 is the default 'blank' choice from the route node drop down a number is used because the ojb RouteNode object is used to
1204:                // render the node choices on the form.
1205:                String returnSql = "";
1206:                if ((docRouteLevel != null)
1207:                        && (!"".equals(docRouteLevel.trim()))
1208:                        && (!docRouteLevel.equals("-1"))) {
1209:                    String operator = " = ";
1210:                    if ("before".equalsIgnoreCase(docRouteLevelLogic.trim())) {
1211:                        operator = " < ";
1212:                    } else if ("after".equalsIgnoreCase(docRouteLevelLogic
1213:                            .trim())) {
1214:                        operator = " > ";
1215:                    }
1216:                    returnSql = whereClausePredicatePrefix
1217:                            + "DOC_HDR.DOC_HDR_ID = EN_RTE_NODE_INSTN_T.DOC_ID and EN_RTE_NODE_INSTN_T.ACTV_IND = 1 and EN_RTE_NODE_INSTN_T.RTE_NODE_ID "
1218:                            + operator + "'" + docRouteLevel.trim() + "' ";
1219:                }
1220:                return returnSql;
1221:            }
1222:
1223:            protected String getDocRouteStatusSql(String docRouteStatus,
1224:                    String whereClausePredicatePrefix) {
1225:                if ((docRouteStatus == null)
1226:                        || "".equals(docRouteStatus.trim())) {
1227:                    return whereClausePredicatePrefix
1228:                            + "DOC_HDR.DOC_RTE_STAT_CD != '"
1229:                            + EdenConstants.ROUTE_HEADER_INITIATED_CD + "'";
1230:                } else {
1231:                    return whereClausePredicatePrefix
1232:                            + " DOC_HDR.DOC_RTE_STAT_CD = '"
1233:                            + docRouteStatus.trim() + "'";
1234:                }
1235:            }
1236:
1237:            // ---- utility methods
1238:
1239:            /**  
1240:             * TODO we should probably clean this up some, but we are going to exclude those KeyLabelPairs
1241:             * that have a null label.  This will happen in the case of Quickfinders which don't really
1242:             * represent criteria anyway.  Note however, that it is legal for the label to be the empty string.
1243:             * At some point we will probably need to do some more work to untangle this mess
1244:             */
1245:            private void filterOutNonQueryAttributes() {
1246:                List newAttributes = new ArrayList();
1247:                for (Iterator iterator = criteria.getSearchableAttributes()
1248:                        .iterator(); iterator.hasNext();) {
1249:                    SearchAttributeCriteriaComponent component = (SearchAttributeCriteriaComponent) iterator
1250:                            .next();
1251:                    if (component != null) {
1252:                        if ((StringUtils.isNotBlank(component.getValue()))
1253:                                || (!Utilities.isEmpty(component.getValues()))) {
1254:                            newAttributes.add(component);
1255:                        }
1256:                    }
1257:                }
1258:                criteria.setSearchableAttributes(newAttributes);
1259:            }
1260:
1261:            private String getGeneratedPredicatePrefix(int whereClauseSize) {
1262:                return (whereClauseSize > 0) ? " and " : " where ";
1263:            }
1264:
1265:            protected String establishDateString(String fromDate,
1266:                    String toDate, String columnDbName,
1267:                    String whereStatementClause) {
1268:                StringBuffer dateSqlString = new StringBuffer(
1269:                        whereStatementClause).append(" " + columnDbName + " ");
1270:                if (fromDate != null
1271:                        && DocSearchUtils.getSqlFormattedDate(fromDate) != null
1272:                        && toDate != null
1273:                        && DocSearchUtils.getSqlFormattedDate(toDate) != null) {
1274:                    return dateSqlString.append(
1275:                            " >= "
1276:                                    + DocSearchUtils.getDateSQL(DocSearchUtils
1277:                                            .getSqlFormattedDate(fromDate
1278:                                                    .trim()), null)
1279:                                    + " and "
1280:                                    + columnDbName
1281:                                    + " <= "
1282:                                    + DocSearchUtils.getDateSQL(
1283:                                            DocSearchUtils
1284:                                                    .getSqlFormattedDate(toDate
1285:                                                            .trim()),
1286:                                            "23:59:59")).toString();
1287:                } else {
1288:                    if (fromDate != null
1289:                            && DocSearchUtils.getSqlFormattedDate(fromDate) != null) {
1290:                        return dateSqlString
1291:                                .append(
1292:                                        " >= "
1293:                                                + DocSearchUtils
1294:                                                        .getDateSQL(
1295:                                                                DocSearchUtils
1296:                                                                        .getSqlFormattedDate(fromDate
1297:                                                                                .trim()),
1298:                                                                null))
1299:                                .toString();
1300:                    } else if (toDate != null
1301:                            && DocSearchUtils.getSqlFormattedDate(toDate) != null) {
1302:                        return dateSqlString
1303:                                .append(
1304:                                        " <= "
1305:                                                + DocSearchUtils
1306:                                                        .getDateSQL(
1307:                                                                DocSearchUtils
1308:                                                                        .getSqlFormattedDate(toDate
1309:                                                                                .trim()),
1310:                                                                "23:59:59"))
1311:                                .toString();
1312:                    } else {
1313:                        return "";
1314:                    }
1315:                }
1316:            }
1317:        }
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.