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: }
|