Source code

001/**
002 * Copyright 2005-2016 The Kuali Foundation
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package org.kuali.rice.kew.impl.document.search;
017
018import org.apache.commons.collections.CollectionUtils;
019import org.apache.commons.lang.StringUtils;
020import org.joda.time.DateTime;
021import org.kuali.rice.core.api.CoreApiServiceLocator;
022import org.kuali.rice.core.api.resourceloader.GlobalResourceLoader;
023import org.kuali.rice.core.api.uif.RemotableAttributeError;
024import org.kuali.rice.core.api.uif.RemotableAttributeField;
025import org.kuali.rice.core.api.util.RiceConstants;
026import org.kuali.rice.core.framework.persistence.jdbc.sql.Criteria;
027import org.kuali.rice.core.framework.persistence.jdbc.sql.SqlBuilder;
028import org.kuali.rice.core.framework.persistence.platform.DatabasePlatform;
029import org.kuali.rice.kew.api.KewApiConstants;
030import org.kuali.rice.kew.api.KewApiServiceLocator;
031import org.kuali.rice.kew.api.document.Document;
032import org.kuali.rice.kew.api.document.DocumentStatus;
033import org.kuali.rice.kew.api.document.DocumentStatusCategory;
034import org.kuali.rice.kew.api.document.attribute.DocumentAttribute;
035import org.kuali.rice.kew.api.document.attribute.DocumentAttributeFactory;
036import org.kuali.rice.kew.api.document.search.DocumentSearchCriteria;
037import org.kuali.rice.kew.api.document.search.DocumentSearchResult;
038import org.kuali.rice.kew.api.document.search.DocumentSearchResults;
039import org.kuali.rice.kew.api.document.search.RouteNodeLookupLogic;
040import org.kuali.rice.kew.docsearch.DocumentSearchInternalUtils;
041import org.kuali.rice.kew.docsearch.QueryComponent;
042import org.kuali.rice.kew.docsearch.SearchableAttributeValue;
043import org.kuali.rice.kew.doctype.bo.DocumentType;
044import org.kuali.rice.kew.doctype.service.DocumentTypeService;
045import org.kuali.rice.kew.engine.node.RouteNode;
046import org.kuali.rice.kew.service.KEWServiceLocator;
047import org.kuali.rice.kew.util.PerformanceLogger;
048import org.kuali.rice.kim.api.identity.Person;
049import org.kuali.rice.kim.api.identity.principal.PrincipalContract;
050import org.kuali.rice.kim.api.services.KimApiServiceLocator;
051import org.kuali.rice.krad.util.KRADConstants;
052import org.kuali.rice.krad.util.MessageMap;
053
054import java.sql.ResultSet;
055import java.sql.SQLException;
056import java.sql.Statement;
057import java.sql.Timestamp;
058import java.util.ArrayList;
059import java.util.Collection;
060import java.util.Collections;
061import java.util.HashMap;
062import java.util.HashSet;
063import java.util.List;
064import java.util.Map;
065import java.util.Set;
066import java.util.TreeSet;
067
068
069/**
070 * Reference implementation of the {@code DocumentSearchGenerator}.
071 *
072 * @author Kuali Rice Team (rice.collab@kuali.org)
073 */
074public class DocumentSearchGeneratorImpl implements DocumentSearchGenerator {
075
076    private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(DocumentSearchGeneratorImpl.class);
077
078    private static final String ROUTE_NODE_TABLE = "KREW_RTE_NODE_T";
079    private static final String ROUTE_NODE_INST_TABLE = "KREW_RTE_NODE_INSTN_T";
080    private static final String DATABASE_WILDCARD_CHARACTER_STRING = "%";
081    private static final char DATABASE_WILDCARD_CHARACTER = DATABASE_WILDCARD_CHARACTER_STRING.toCharArray()[0];
082
083    private org.kuali.rice.kew.api.doctype.DocumentTypeService apiDocumentTypeService;
084
085    private DatabasePlatform dbPlatform;
086    private MessageMap messageMap;
087
088    private SqlBuilder sqlBuilder = null;
089
090    @Override
091    public DocumentSearchCriteria clearSearch(DocumentSearchCriteria criteria) {
092        return DocumentSearchCriteria.Builder.create().build();
093    }
094
095    public DocumentType getValidDocumentType(String documentTypeFullName) {
096        if (!org.apache.commons.lang.StringUtils.isEmpty(documentTypeFullName)) {
097            DocumentType documentType = KEWServiceLocator.getDocumentTypeService().findByNameCaseInsensitive(documentTypeFullName);
098            if (documentType == null) {
099                throw new RuntimeException("No Valid Document Type Found for document type name '" + documentTypeFullName + "'");
100            }
101            return documentType;
102        }
103        return null;
104    }
105
106    @Override
107    public List<RemotableAttributeError> validateSearchableAttributes(DocumentSearchCriteria.Builder criteria) {
108        List<RemotableAttributeError> errors = new ArrayList<RemotableAttributeError>();
109        DocumentType documentType = null;
110        try{
111              documentType = getValidDocumentType(criteria.getDocumentTypeName());
112        }catch(RuntimeException re){
113            errors.add(RemotableAttributeError.Builder.create("documentTypeName", re.getMessage()).build());
114        }
115
116        if (documentType != null) {
117            errors = KEWServiceLocator.getDocumentSearchCustomizationMediator().validateLookupFieldParameters(documentType, criteria.build());
118        } else {
119            criteria.setDocumentAttributeValues(new HashMap<String, List<String>>());
120        }
121        return errors == null ? Collections.<RemotableAttributeError>emptyList() : Collections.unmodifiableList(errors);
122    }
123
124    public QueryComponent getSearchableAttributeSql(Map<String, List<String>> documentAttributeValues, List<RemotableAttributeField> searchFields, String whereClausePredicatePrefix) {
125
126        StringBuilder fromSql = new StringBuilder();
127        StringBuilder whereSql = new StringBuilder();
128
129        //Map<String, List<SearchAttributeCriteriaComponent>> searchableAttributeRangeComponents = new HashMap<String,List<SearchAttributeCriteriaComponent>>();
130        Criteria finalCriteria = null;
131        int tableIndex = 1;
132        SqlBuilder sqlBuilder = this.getSqlBuilder();
133
134        for (String documentAttributeName : documentAttributeValues.keySet()) {
135            String documentAttributeNameForSQL = documentAttributeName;
136            if (documentAttributeName.contains(KewApiConstants.DOCUMENT_ATTRIBUTE_FIELD_PREFIX)) {
137                documentAttributeNameForSQL = documentAttributeName.replaceFirst(KewApiConstants.DOCUMENT_ATTRIBUTE_FIELD_PREFIX, "");
138            }
139            List<String> searchValues = documentAttributeValues.get(documentAttributeName);
140            if (CollectionUtils.isEmpty(searchValues) || documentAttributeName.contains(KRADConstants.CHECKBOX_PRESENT_ON_FORM_ANNOTATION)) {
141                continue;
142            }
143
144            String tableAlias = "EXT" + tableIndex;
145            RemotableAttributeField searchField = getSearchFieldByName(documentAttributeName, searchFields);
146
147            String tableName = DocumentSearchInternalUtils.getAttributeTableName(searchField);
148            boolean caseSensitive = DocumentSearchInternalUtils.isLookupCaseSensitive(searchField);
149
150            Criteria crit = null;
151
152            Class<?> dataTypeClass = DocumentSearchInternalUtils.getDataTypeClass(searchField);
153            if (searchValues.size() > 1) {
154                // if there's more than one entry, we need to do an "in"
155                crit = new Criteria(tableName, tableAlias);
156                crit.setDbPlatform(sqlBuilder.getDbPlatform());
157                crit.in("VAL", searchValues, dataTypeClass);
158            } else {
159                crit = sqlBuilder.createCriteria("VAL", searchValues.get(0) , tableName, tableAlias, dataTypeClass, !caseSensitive);
160            }
161
162            sqlBuilder.addCriteria("KEY_CD", documentAttributeNameForSQL, String.class, false, false, crit); // this is always of type string.
163            sqlBuilder.andCriteria("DOC_HDR_ID", tableAlias + ".DOC_HDR_ID", "KREW_DOC_HDR_T", "DOC_HDR", SqlBuilder.JoinType.class, false, false, crit);
164
165            if (finalCriteria == null ){
166                finalCriteria = crit;
167            } else{
168                sqlBuilder.andCriteria(finalCriteria, crit);
169            }
170
171            // - below is the old code
172            // if where clause is empty then use passed in prefix... otherwise generate one
173            String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
174            QueryComponent qc = generateSearchableAttributeSql(tableName, documentAttributeNameForSQL, whereClausePrefix, tableIndex);
175            fromSql.append(qc.getFromSql());
176            tableIndex++;
177        }
178
179        if (finalCriteria == null) {
180            return new QueryComponent("", "", "");
181        }
182
183        String whereClausePrefix = (whereSql.length() == 0) ? whereClausePredicatePrefix : getGeneratedPredicatePrefix(whereSql.length());
184
185        return new QueryComponent("", fromSql.toString(), whereClausePrefix + " " + finalCriteria.buildWhere());
186    }
187
188    private RemotableAttributeField getSearchFieldByName(String fieldName, List<RemotableAttributeField> searchFields) {
189        for (RemotableAttributeField searchField : searchFields) {
190            if (searchField.getName().equals(fieldName)
191                    || searchField.getName().equals(KewApiConstants.DOCUMENT_ATTRIBUTE_FIELD_PREFIX + fieldName)) {
192                return searchField;
193            }
194        }
195        throw new IllegalStateException("Failed to locate a RemotableAttributeField for fieldName=" + fieldName);
196    }
197
198    public QueryComponent generateSearchableAttributeSql(String tableName, String documentAttributeName, String whereSqlStarter,int tableIndex) {
199        String tableIdentifier = "EXT" + tableIndex;
200        QueryComponent joinSqlComponent = getSearchableAttributeJoinSql(tableName, tableIdentifier, whereSqlStarter, documentAttributeName);
201        return new QueryComponent("", joinSqlComponent.getFromSql(), joinSqlComponent.getWhereSql());
202    }
203
204    public QueryComponent getSearchableAttributeJoinSql(String tableName, String tableIdentifier, String whereSqlStarter, String attributeTableKeyColumnName) {
205        return new QueryComponent("", generateSearchableAttributeFromSql(tableName, tableIdentifier).toString(), generateSearchableAttributeWhereClauseJoin(whereSqlStarter, tableIdentifier, attributeTableKeyColumnName).toString());
206    }
207
208    public StringBuilder generateSearchableAttributeWhereClauseJoin(String whereSqlStarter,String tableIdentifier,String attributeTableKeyColumnName) {
209        StringBuilder whereSql = new StringBuilder(constructWhereClauseElement(whereSqlStarter, "DOC_HDR.DOC_HDR_ID", "=", getDbPlatform().escapeString(tableIdentifier + ".DOC_HDR_ID"), null, null));
210        whereSql.append(constructWhereClauseElement(" and ", tableIdentifier + ".KEY_CD", "=",
211                getDbPlatform().escapeString(attributeTableKeyColumnName), "'", "'"));
212        return whereSql;
213    }
214
215    public StringBuilder generateSearchableAttributeFromSql(String tableName, String tableIdentifier) {
216        if (StringUtils.isBlank(tableName)) {
217            throw new IllegalArgumentException("tableName was null or blank");
218        }
219        if (StringUtils.isBlank(tableIdentifier)) {
220            throw new IllegalArgumentException("tableIdentifier was null or blank");
221        }
222        StringBuilder fromSql = new StringBuilder();
223        fromSql.append(" ,").append(tableName).append(" ").append(getDbPlatform().escapeString(tableIdentifier)).append(" ");
224        return fromSql;
225    }
226
227    public StringBuilder constructWhereClauseElement(String clauseStarter,String queryTableColumnName,String operand,String valueToSearch,String valuePrefix,String valueSuffix) {
228        StringBuilder whereSql = new StringBuilder();
229        valuePrefix = (valuePrefix != null) ? valuePrefix : "";
230        valueSuffix = (valueSuffix != null) ? valueSuffix : "";
231        whereSql.append(" " + clauseStarter + " ").append(getDbPlatform().escapeString(queryTableColumnName)).append(" " + operand + " ").append(valuePrefix).append(valueToSearch).append(valueSuffix).append(" ");
232        return whereSql;
233    }
234
235    @Override
236    public DocumentSearchResults.Builder processResultSet(DocumentSearchCriteria criteria, boolean criteriaModified, Statement searchAttributeStatement, ResultSet resultSet, int maxResultCap, int fetchLimit) throws SQLException {
237        DocumentSearchCriteria.Builder criteriaBuilder = DocumentSearchCriteria.Builder.create(criteria);
238        DocumentSearchResults.Builder results = DocumentSearchResults.Builder.create(criteriaBuilder);
239        results.setCriteriaModified(criteriaModified);
240
241        List<DocumentSearchResult.Builder> resultList = new ArrayList<DocumentSearchResult.Builder>();
242        results.setSearchResults(resultList);
243        Map<String, DocumentSearchResult.Builder> resultMap = new HashMap<String, DocumentSearchResult.Builder>();
244
245        int startAt = (criteria.getStartAtIndex()==null) ? 0 : criteria.getStartAtIndex();
246        int iteration = 0;
247        boolean resultSetHasNext = resultSet.next();
248
249        PerformanceLogger perfLog = new PerformanceLogger();
250
251        while (resultSetHasNext && resultMap.size() < maxResultCap && iteration < fetchLimit && startAt >= 0) {
252            if (iteration >= startAt) {
253                DocumentSearchResult.Builder resultBuilder = processRow(criteria, searchAttributeStatement, resultSet);
254                String documentId = resultBuilder.getDocument().getDocumentId();
255                if (!resultMap.containsKey(documentId)) {
256                    resultList.add(resultBuilder);
257                    resultMap.put(documentId, resultBuilder);
258                } else {
259                    // handle duplicate rows with different search data
260                    DocumentSearchResult.Builder previousEntry = resultMap.get(documentId);
261                    handleMultipleDocumentRows(previousEntry, resultBuilder);
262                }
263            }
264
265            iteration++;
266            resultSetHasNext = resultSet.next();
267        }
268
269        perfLog.log("Time to read doc search results.", true);
270        // if we have threshold+1 results, then we have more results than we are going to display
271        results.setOverThreshold(resultSetHasNext);
272
273        LOG.debug("Processed " + resultMap.size() + " document search result rows.");
274        return results;
275    }
276
277    /**
278     * Handles multiple document rows by collapsing them into the list of document attributes on the existing row.
279     * The two rows must represent the same document.
280     *
281     * @param existingRow the existing row to combine the new row into
282     * @param newRow the new row from which to combine document attributes with the existing row
283     */
284    private void handleMultipleDocumentRows(DocumentSearchResult.Builder existingRow, DocumentSearchResult.Builder newRow) {
285        for (DocumentAttribute.AbstractBuilder<?> newDocumentAttribute : newRow.getDocumentAttributes()) {
286            existingRow.getDocumentAttributes().add(newDocumentAttribute);
287        }
288    }
289
290    /**
291     * Processes the search result row, returning a DocumentSearchResult
292     * @param criteria the original search criteria
293     * @param searchAttributeStatement statement being used to call the database for queries
294     * @param rs the search result set
295     * @return a DocumentSearchResult representing the current ResultSet row
296     * @throws SQLException
297     */
298    protected DocumentSearchResult.Builder processRow(DocumentSearchCriteria criteria, Statement searchAttributeStatement, ResultSet rs) throws SQLException {
299
300        String documentId = rs.getString("DOC_HDR_ID");
301        String initiatorPrincipalId = rs.getString("INITR_PRNCPL_ID");
302        String documentTypeName = rs.getString("DOC_TYP_NM");
303        org.kuali.rice.kew.api.doctype.DocumentType documentType =
304                getApiDocumentTypeService().getDocumentTypeByName(documentTypeName);
305
306        String documentTypeId = "unavailable";
307        if (documentType == null) {
308            LOG.warn("Failed to locate a document type with the given name: " + documentTypeName);
309        } else {
310            documentTypeId = documentType.getId();
311        }
312
313        Document.Builder documentBuilder = Document.Builder.create(documentId, initiatorPrincipalId, documentTypeName, documentTypeId);
314        DocumentSearchResult.Builder resultBuilder = DocumentSearchResult.Builder.create(documentBuilder);
315
316        String statusCode = rs.getString("DOC_HDR_STAT_CD");
317        Timestamp createTimestamp = rs.getTimestamp("CRTE_DT");
318        String title = rs.getString("TTL");
319        String applicationDocumentStatus = rs.getString("APP_DOC_STAT");
320
321        documentBuilder.setStatus(DocumentStatus.fromCode(statusCode));
322        documentBuilder.setDateCreated(new DateTime(createTimestamp.getTime()));
323        documentBuilder.setTitle(title);
324        documentBuilder.setApplicationDocumentStatus(applicationDocumentStatus);
325        documentBuilder.setApplicationDocumentStatusDate(new DateTime(rs.getTimestamp("APP_DOC_STAT_MDFN_DT")));
326        documentBuilder.setDateApproved(new DateTime(rs.getTimestamp("APRV_DT")));
327        documentBuilder.setDateFinalized(new DateTime(rs.getTimestamp("FNL_DT")));
328        documentBuilder.setApplicationDocumentId(rs.getString("APP_DOC_ID"));
329        documentBuilder.setDateLastModified(new DateTime(rs.getTimestamp("STAT_MDFN_DT")));
330        documentBuilder.setRoutedByPrincipalId(rs.getString("RTE_PRNCPL_ID"));
331
332        // TODO - KULRICE-5755 - should probably set as many properties on the document as we can
333        documentBuilder.setDocumentHandlerUrl(rs.getString("DOC_HDLR_URL"));
334
335        if (isUsingAtLeastOneSearchAttribute(criteria)) {
336            populateDocumentAttributesValues(resultBuilder, searchAttributeStatement);
337        }
338
339        return resultBuilder;
340    }
341
342    /**
343     * This method performs searches against the search attribute value tables (see classes implementing
344     * {@link org.kuali.rice.kew.docsearch.SearchableAttributeValue}) to get data to fill in search attribute values on the given resultBuilder parameter
345     *
346     * @param resultBuilder - document search result object getting search attributes added to it
347     * @param searchAttributeStatement - statement being used to call the database for queries
348     * @throws SQLException
349     */
350    public void populateDocumentAttributesValues(DocumentSearchResult.Builder resultBuilder, Statement searchAttributeStatement) throws SQLException {
351        searchAttributeStatement.setFetchSize(50);
352        String documentId = resultBuilder.getDocument().getDocumentId();
353        List<SearchableAttributeValue> attributeValues = DocumentSearchInternalUtils
354                .getSearchableAttributeValueObjectTypes();
355        PerformanceLogger perfLog = new PerformanceLogger(documentId);
356        for (SearchableAttributeValue searchAttValue : attributeValues) {
357            String attributeSql = "select KEY_CD, VAL from " + searchAttValue.getAttributeTableName() + " where DOC_HDR_ID = '" + documentId + "'";
358            ResultSet attributeResultSet = null;
359            try {
360                attributeResultSet = searchAttributeStatement.executeQuery(attributeSql);
361                while (attributeResultSet.next()) {
362                    searchAttValue.setSearchableAttributeKey(attributeResultSet.getString("KEY_CD"));
363                    searchAttValue.setupAttributeValue(attributeResultSet, "VAL");
364                    if ( (!org.apache.commons.lang.StringUtils.isEmpty(searchAttValue.getSearchableAttributeKey())) && (searchAttValue.getSearchableAttributeValue() != null) ) {
365                        DocumentAttribute documentAttribute = searchAttValue.toDocumentAttribute();
366                        resultBuilder.getDocumentAttributes().add(DocumentAttributeFactory.loadContractIntoBuilder(
367                                documentAttribute));
368                    }
369                }
370            } finally {
371                if (attributeResultSet != null) {
372                    try {
373                        attributeResultSet.close();
374                    } catch (Exception e) {
375                        LOG.warn("Could not close searchable attribute result set for class " + searchAttValue.getClass().getName(),e);
376                    }
377                }
378            }
379        }
380        perfLog.log("Time to execute doc search search attribute queries.", true);
381    }
382
383    @SuppressWarnings("deprecation")
384    public String generateSearchSql(DocumentSearchCriteria criteria, List<RemotableAttributeField> searchFields) {
385
386        String docTypeTableAlias   = "DOC1";
387        String docHeaderTableAlias = "DOC_HDR";
388
389        String sqlPrefix = "Select * from (";
390        String sqlSuffix = ") FINAL_SEARCH order by FINAL_SEARCH.CRTE_DT desc";
391
392        // the DISTINCT here is important as it filters out duplicate rows which could occur as the result of doc search extension values...
393        StringBuilder selectSQL = new StringBuilder("select DISTINCT("+ docHeaderTableAlias +".DOC_HDR_ID), "
394                                                    + StringUtils.join(new String[] {
395                                                        docHeaderTableAlias + ".INITR_PRNCPL_ID",
396                                                        docHeaderTableAlias + ".DOC_HDR_STAT_CD",
397                                                        docHeaderTableAlias + ".CRTE_DT",
398                                                        docHeaderTableAlias + ".TTL",
399                                                        docHeaderTableAlias + ".APP_DOC_STAT",
400                                                        docHeaderTableAlias + ".STAT_MDFN_DT",
401                                                        docHeaderTableAlias + ".APRV_DT",
402                                                        docHeaderTableAlias + ".FNL_DT",
403                                                        docHeaderTableAlias + ".APP_DOC_ID",
404                                                        docHeaderTableAlias + ".RTE_PRNCPL_ID",
405                                                        docHeaderTableAlias + ".APP_DOC_STAT_MDFN_DT",
406                                                        docTypeTableAlias + ".DOC_TYP_NM",
407                                                        docTypeTableAlias + ".LBL",
408                                                        docTypeTableAlias + ".DOC_HDLR_URL",
409                                                        docTypeTableAlias + ".ACTV_IND"
410                                                    }, ", "));
411        StringBuilder fromSQL = new StringBuilder(" from KREW_DOC_TYP_T "+ docTypeTableAlias +" ");
412        StringBuilder fromSQLForDocHeaderTable = new StringBuilder(", KREW_DOC_HDR_T " + docHeaderTableAlias + " ");
413
414        StringBuilder whereSQL = new StringBuilder();
415        whereSQL.append(getDocumentIdSql(criteria.getDocumentId(), getGeneratedPredicatePrefix(whereSQL.length()), docHeaderTableAlias));
416        // if principalId criteria exists ignore deprecated principalName search term
417        String principalInitiatorIdSql = getInitiatorIdSql(criteria.getInitiatorPrincipalId(), getGeneratedPredicatePrefix(whereSQL.length()));
418        if (StringUtils.isNotBlank(principalInitiatorIdSql)) {
419            whereSQL.append(principalInitiatorIdSql);
420        } else {
421            whereSQL.append(getInitiatorSql(criteria.getInitiatorPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length())));
422        }
423        whereSQL.append(getAppDocIdSql(criteria.getApplicationDocumentId(), getGeneratedPredicatePrefix(whereSQL.length())));
424        whereSQL.append(getDateCreatedSql(criteria.getDateCreatedFrom(), criteria.getDateCreatedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
425        whereSQL.append(getDateLastModifiedSql(criteria.getDateLastModifiedFrom(), criteria.getDateLastModifiedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
426        whereSQL.append(getDateApprovedSql(criteria.getDateApprovedFrom(), criteria.getDateApprovedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
427        whereSQL.append(getDateFinalizedSql(criteria.getDateFinalizedFrom(), criteria.getDateFinalizedTo(), getGeneratedPredicatePrefix(whereSQL.length())));
428
429        // flags for the table being added to the FROM class of the sql
430        String principalViewerSql = getViewerSql(criteria.getViewerPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length()));
431        String principalViewerIdSql = getViewerIdSql(criteria.getViewerPrincipalId(), getGeneratedPredicatePrefix(whereSQL.length()));
432        // if principalId criteria exists ignore deprecated principalName search term
433        if (StringUtils.isNotBlank(principalViewerIdSql)){
434            principalViewerSql = "";
435        }
436        String groupViewerSql = getGroupViewerSql(criteria.getGroupViewerId(), getGeneratedPredicatePrefix(whereSQL.length()));
437        if (StringUtils.isNotBlank(principalViewerSql) || StringUtils.isNotBlank(groupViewerSql) || StringUtils.isNotBlank(principalViewerIdSql) ) {
438            whereSQL.append(principalViewerSql);
439            whereSQL.append(principalViewerIdSql);
440            whereSQL.append(groupViewerSql);
441            fromSQL.append(", KREW_ACTN_RQST_T ");
442        }
443
444        String principalApproverSql =  getApproverSql(criteria.getApproverPrincipalName(), getGeneratedPredicatePrefix(whereSQL.length()));
445        String principalApproverIdSql = getApproverIdSql(criteria.getApproverPrincipalId(), getGeneratedPredicatePrefix(whereSQL.length()));
446        // if principalId criteria exists ignore deprecated principalName search term
447        if (StringUtils.isNotBlank(principalApproverIdSql)){
448            principalApproverSql = "";
449        }
450        if (StringUtils.isNotBlank(principalApproverSql) || StringUtils.isNotBlank(principalApproverIdSql)) {
451            whereSQL.append(principalApproverSql);
452            whereSQL.append(principalApproverIdSql);
453            fromSQL.append(", KREW_ACTN_TKN_T ");
454        }
455
456
457
458        String docRouteNodeSql = getDocRouteNodeSql(criteria.getDocumentTypeName(), criteria.getRouteNodeName(), criteria.getRouteNodeLookupLogic(), getGeneratedPredicatePrefix(whereSQL.length()));
459        if (StringUtils.isNotBlank(docRouteNodeSql)) {
460            whereSQL.append(docRouteNodeSql);
461            fromSQL.append(", KREW_RTE_NODE_INSTN_T ");
462            fromSQL.append(", KREW_RTE_NODE_T ");
463        }
464
465        if (!criteria.getDocumentAttributeValues().isEmpty()) {
466            QueryComponent queryComponent = getSearchableAttributeSql(criteria.getDocumentAttributeValues(), searchFields, getGeneratedPredicatePrefix(
467                    whereSQL.length()));
468            selectSQL.append(queryComponent.getSelectSql());
469            fromSQL.append(queryComponent.getFromSql());
470            whereSQL.append(queryComponent.getWhereSql());
471        }
472
473        whereSQL.append(getDocTypeFullNameWhereSql(criteria, getGeneratedPredicatePrefix(whereSQL.length())));
474        whereSQL.append(getDocTitleSql(criteria.getTitle(), getGeneratedPredicatePrefix(whereSQL.length())));
475        whereSQL.append(getDocumentStatusSql(criteria.getDocumentStatuses(), criteria.getDocumentStatusCategories(), getGeneratedPredicatePrefix(whereSQL.length())));
476        whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_TYP_ID = DOC1.DOC_TYP_ID ");
477        fromSQL.append(fromSQLForDocHeaderTable);
478
479        // App Doc Status Value and Transition clauses
480        String statusTransitionWhereClause = getStatusTransitionDateSql(criteria.getDateApplicationDocumentStatusChangedFrom(), criteria.getDateApplicationDocumentStatusChangedTo(), getGeneratedPredicatePrefix(whereSQL.length()));
481
482        List<String> applicationDocumentStatuses = criteria.getApplicationDocumentStatuses();
483        // deal with legacy usage of applicationDocumentStatus (which is deprecated)
484        if (!StringUtils.isBlank(criteria.getApplicationDocumentStatus())) {
485            if (!criteria.getApplicationDocumentStatuses().contains(criteria.getApplicationDocumentStatus())) {
486                applicationDocumentStatuses = new ArrayList<String>(criteria.getApplicationDocumentStatuses());
487                applicationDocumentStatuses.add(criteria.getApplicationDocumentStatus());
488            }
489        }
490
491        whereSQL.append(getAppDocStatusesSql(applicationDocumentStatuses, getGeneratedPredicatePrefix(
492                whereSQL.length()), statusTransitionWhereClause.length()));
493        if (statusTransitionWhereClause.length() > 0){
494                whereSQL.append(statusTransitionWhereClause);
495            whereSQL.append(getGeneratedPredicatePrefix(whereSQL.length())).append(" DOC_HDR.DOC_HDR_ID = STAT_TRAN.DOC_HDR_ID ");
496                fromSQL.append(", KREW_APP_DOC_STAT_TRAN_T STAT_TRAN ");
497        }
498
499        String finalizedSql = sqlPrefix + " " + selectSQL.toString() + " " + fromSQL.toString() + " " + whereSQL.toString() + " " + sqlSuffix;
500
501        LOG.info("*********** SEARCH SQL ***************");
502        LOG.info(finalizedSql);
503        LOG.info("**************************************");
504        return finalizedSql;
505    }
506
507    public String getDocumentIdSql(String documentId, String whereClausePredicatePrefix, String tableAlias) {
508        if (StringUtils.isBlank(documentId)) {
509            return "";
510        } else {
511                // Using true for caseInsensitive causes bad performance for MYSQL databases since function indexes cannot be added.
512                // Due to this, false is passed for caseInsensitive
513            Criteria crit = getSqlBuilder().createCriteria("DOC_HDR_ID", documentId, "KREW_DOC_HDR_T", tableAlias, String.class, false, true);
514            return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
515        }
516    }
517
518    public String getDocTitleSql(String docTitle, String whereClausePredicatePrefix) {
519        if (StringUtils.isBlank(docTitle)) {
520            return "";
521        } else {
522            // quick and dirty ' replacement that isn't the best but should work for all dbs
523            docTitle = docTitle.trim().replace("\'", "\'\'");
524            SqlBuilder sqlBuild = new SqlBuilder();
525            Criteria crit = new Criteria("KREW_DOC_HDR_T", "DOC_HDR");
526            sqlBuild.addCriteria("TTL", docTitle, String.class, true, true, crit);
527            return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
528        }
529    }
530
531    // special methods that return the sql needed to complete the search
532    // or nothing if the field was not filled in
533    public String getAppDocIdSql(String appDocId, String whereClausePredicatePrefix) {
534        if (StringUtils.isBlank(appDocId)) {
535            return "";
536        } else {
537            String tableAlias = "DOC_HDR";
538            Criteria crit = getSqlBuilder().createCriteria("APP_DOC_ID", appDocId, "KREW_DOC_HDR_T", tableAlias,String.class);
539            return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
540        }
541    }
542
543    public String getDateCreatedSql(DateTime fromDateCreated, DateTime toDateCreated, String whereClausePredicatePrefix) {
544        return establishDateString(fromDateCreated, toDateCreated, "KREW_DOC_HDR_T", "DOC_HDR", "CRTE_DT", whereClausePredicatePrefix);
545    }
546
547    public String getDateApprovedSql(DateTime fromDateApproved, DateTime toDateApproved, String whereClausePredicatePrefix) {
548        return establishDateString(fromDateApproved, toDateApproved, "KREW_DOC_HDR_T", "DOC_HDR", "APRV_DT", whereClausePredicatePrefix);
549    }
550
551    public String getDateFinalizedSql(DateTime fromDateFinalized, DateTime toDateFinalized, String whereClausePredicatePrefix) {
552        return establishDateString(fromDateFinalized, toDateFinalized, "KREW_DOC_HDR_T", "DOC_HDR", "FNL_DT", whereClausePredicatePrefix);
553    }
554
555    public String getDateLastModifiedSql(DateTime fromDateLastModified, DateTime toDateLastModified, String whereClausePredicatePrefix) {
556        return establishDateString(fromDateLastModified, toDateLastModified, "KREW_DOC_HDR_T", "DOC_HDR", "STAT_MDFN_DT", whereClausePredicatePrefix);
557    }
558
559        public String getStatusTransitionDateSql(DateTime fromStatusTransitionDate, DateTime toStatusTransitionDate, String whereClausePredicatePrefix) {
560        return establishDateString(fromStatusTransitionDate, toStatusTransitionDate, "KREW_DOC_HDR_T", "DOC_HDR", "APP_DOC_STAT_MDFN_DT", whereClausePredicatePrefix);
561    }
562
563    public String getViewerSql(String viewer, String whereClausePredicatePrefix) {
564        StringBuilder returnSql = new StringBuilder();
565        if (StringUtils.isNotBlank(viewer)) {
566            Map<String, String> m = new HashMap<String, String>();
567            m.put("principalName", viewer);
568
569            // This will search for people with the ability for the valid operands.
570            List<Person> personList = KimApiServiceLocator.getPersonService().findPeople(m, false);
571            List<String> principalList = new ArrayList<String>();
572
573            if(CollectionUtils.isEmpty(personList)) {
574                // findPeople allows for wildcards, but the person must be active.  If no one was found,
575                // check for an exact inactive user.
576                PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(viewer.trim());
577                        if (tempPrincipal != null) {
578                    principalList.add(tempPrincipal.getPrincipalId());
579                } else {
580                    // they entered something that returned nothing... so we should return nothing
581
582                    return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
583                }
584            }
585
586            for (Person person : personList){
587                principalList.add(person.getPrincipalId());
588            }
589
590            Criteria crit = new Criteria("KREW_ACTN_RQST_T", "KREW_ACTN_RQST_T");
591            crit.in("PRNCPL_ID", principalList, String.class);
592            returnSql.append(whereClausePredicatePrefix + "( (DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and " + crit.buildWhere() + " )");
593
594            Set<String> viewerGroupIds = new TreeSet<String>();
595
596            if(CollectionUtils.isNotEmpty(principalList)) {
597                for(String principalId: principalList){
598                    viewerGroupIds.addAll(KimApiServiceLocator.getGroupService().getGroupIdsByPrincipalId(principalId));
599                }
600            }
601
602            // Documents routed to users as part of a workgoup should be returned.
603            // Use Chad's escape stuff
604            if (viewerGroupIds != null && !viewerGroupIds.isEmpty()) {
605
606                returnSql.append(" or ( " +
607                    "DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID " +
608                    "and KREW_ACTN_RQST_T.GRP_ID in (");
609
610                boolean first = true;
611                for (String groupId : viewerGroupIds){
612                    if(!first){
613                        returnSql.append(",");
614                    }
615                    returnSql.append("'").append(groupId).append("'");
616                    first = false;
617                }
618                returnSql.append("))");
619            }
620            returnSql.append(")");
621        }
622        return returnSql.toString();
623    }
624
625    public String getViewerIdSql(String viewerId, String whereClausePredicatePrefix) {
626        StringBuilder returnSql = new StringBuilder();
627        if (StringUtils.isNotBlank(viewerId)) {
628            Map<String, String> m = new HashMap<String, String>();
629            m.put("principalId", viewerId);
630
631            // This will search for people with the ability for the valid operands.
632            List<Person> personList = KimApiServiceLocator.getPersonService().findPeople(m, false);
633            List<String> principalList = new ArrayList<String>();
634
635            if(CollectionUtils.isEmpty(personList)) {
636                // they entered something that returned nothing... so we should return nothing
637                return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
638            }
639
640            for (Person person : personList){
641                principalList.add(person.getPrincipalId());
642            }
643
644            Criteria crit = new Criteria("KREW_ACTN_RQST_T", "KREW_ACTN_RQST_T");
645            crit.in("PRNCPL_ID", principalList, String.class);
646            returnSql.append(whereClausePredicatePrefix + "( DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and " + crit.buildWhere() + " )");
647        }
648        return returnSql.toString();
649    }
650
651    public String getGroupViewerSql(String groupId, String whereClausePredicatePrefix) {
652        String sql = "";
653        if (StringUtils.isNotBlank(groupId)) {
654            sql = whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_ID = KREW_ACTN_RQST_T.DOC_HDR_ID and KREW_ACTN_RQST_T.GRP_ID = '" + groupId + "'";
655        }
656        return sql;
657    }
658
659    public String getInitiatorSql(String initiatorPrincipalName, String whereClausePredicatePrefix) {
660
661        if (StringUtils.isBlank(initiatorPrincipalName)) {
662            return "";
663        }
664
665        String tableAlias = "DOC_HDR";
666
667        Map<String, String> m = new HashMap<String, String>();
668        m.put("principalName", initiatorPrincipalName);
669
670        // This will search for people with the ability for the valid operands.
671        List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
672        List<String> principalList = new ArrayList<String>();
673
674        if(pList == null || pList.isEmpty() ){
675                // findPeople allows for wildcards, but the person must be active.  If no one was found,
676                // check for an exact inactive user.
677                PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(initiatorPrincipalName.trim());
678                if (tempPrincipal != null) {
679                        principalList.add(tempPrincipal.getPrincipalId());
680                } else {
681                // they entered something that returned nothing... so we should return nothing
682                return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
683                }
684        }
685
686        for(Person p: pList){
687            principalList.add(p.getPrincipalId());
688        }
689
690        Criteria crit = new Criteria("KREW_DOC_HDR_T", tableAlias);
691        crit.in("INITR_PRNCPL_ID", principalList, String.class);
692
693        return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
694    }
695
696    public String getInitiatorIdSql(String initiatorPrincipalId, String whereClausePredicatePrefix) {
697
698        if (StringUtils.isBlank(initiatorPrincipalId)) {
699            return "";
700        }
701
702        String tableAlias = "DOC_HDR";
703
704        Map<String, String> m = new HashMap<String, String>();
705        m.put("principalId", initiatorPrincipalId);
706
707        // This will search for people with the ability for the valid operands.
708        List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
709        List<String> principalList = new ArrayList<String>();
710
711        if(pList == null || pList.isEmpty() ){
712            // they entered something that returned nothing... so we should return nothing
713            return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
714        }
715
716        for(Person p: pList){
717            principalList.add(p.getPrincipalId());
718        }
719
720        Criteria crit = new Criteria("KREW_DOC_HDR_T", tableAlias);
721        crit.in("INITR_PRNCPL_ID", principalList, String.class);
722
723        return new StringBuilder(whereClausePredicatePrefix + crit.buildWhere()).toString();
724    }
725
726    public String getApproverSql(String approver, String whereClausePredicatePrefix) {
727        String returnSql = "";
728        if (StringUtils.isNotBlank(approver)) {
729            Map<String, String> m = new HashMap<String, String>();
730            m.put("principalName", approver);
731
732            // This will search for people with the ability for the valid operands.
733            List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
734            List<String> principalList = new ArrayList<String>();
735
736            if(pList == null || pList.isEmpty() ){
737                        // findPeople allows for wildcards, but the person must be active.  If no one was found,
738                        // check for an exact inactive user.
739                PrincipalContract tempPrincipal = KimApiServiceLocator.getIdentityService().getPrincipalByPrincipalName(approver.trim());
740
741                if (tempPrincipal != null) {
742                                principalList.add(tempPrincipal.getPrincipalId());
743                } else {
744                    // they entered something that returned nothing... so we should return nothing
745                    return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
746                }
747            }
748
749            for(Person p: pList){
750                principalList.add(p.getPrincipalId());
751            }
752
753            Criteria crit = new Criteria("KREW_ACTN_TKN_T", "KREW_ACTN_TKN_T");
754            crit.in("PRNCPL_ID", principalList, String.class);
755
756            returnSql = whereClausePredicatePrefix +
757            " DOC_HDR.DOC_HDR_ID = KREW_ACTN_TKN_T.DOC_HDR_ID and upper(KREW_ACTN_TKN_T.ACTN_CD) in ('" +
758            KewApiConstants.ACTION_TAKEN_APPROVED_CD + "','" + KewApiConstants.ACTION_TAKEN_BLANKET_APPROVE_CD + "')" +
759            " and " + crit.buildWhere();
760        }
761        return returnSql;
762    }
763
764    public String getApproverIdSql(String approverId, String whereClausePredicatePrefix) {
765        String returnSql = "";
766        if (StringUtils.isNotBlank(approverId)) {
767            Map<String, String> m = new HashMap<String, String>();
768            m.put("principalId", approverId);
769
770            // This will search for people with the ability for the valid operands.
771            List<Person> pList = KimApiServiceLocator.getPersonService().findPeople(m, false);
772            List<String> principalList = new ArrayList<String>();
773
774            if(pList == null || pList.isEmpty() ){
775                 // they entered something that returned nothing... so we should return nothing
776                    return new StringBuilder(whereClausePredicatePrefix + " 1 = 0 ").toString();
777            }
778
779            for(Person p: pList){
780                principalList.add(p.getPrincipalId());
781            }
782
783            Criteria crit = new Criteria("KREW_ACTN_TKN_T", "KREW_ACTN_TKN_T");
784            crit.in("PRNCPL_ID", principalList, String.class);
785
786            returnSql = whereClausePredicatePrefix +
787                    " DOC_HDR.DOC_HDR_ID = KREW_ACTN_TKN_T.DOC_HDR_ID and upper(KREW_ACTN_TKN_T.ACTN_CD) in ('" +
788                    KewApiConstants.ACTION_TAKEN_APPROVED_CD + "','" + KewApiConstants.ACTION_TAKEN_BLANKET_APPROVE_CD + "')" +
789                    " and " + crit.buildWhere();
790        }
791        return returnSql;
792    }
793
794    public String getDocTypeFullNameWhereSql(DocumentSearchCriteria criteria, String whereClausePredicatePrefix) {
795        List<String> documentTypeNamesToSearch = new ArrayList<String>();
796        String primaryDocumentTypeName = criteria.getDocumentTypeName();
797        if (StringUtils.isNotBlank(primaryDocumentTypeName)) {
798            documentTypeNamesToSearch.add(primaryDocumentTypeName);
799        }
800        documentTypeNamesToSearch.addAll(criteria.getAdditionalDocumentTypeNames());
801        StringBuilder returnSql = new StringBuilder("");
802        if (CollectionUtils.isNotEmpty(documentTypeNamesToSearch)) {
803            int index = 0;
804            for (String documentTypeName : documentTypeNamesToSearch) {
805                if (StringUtils.isNotBlank(documentTypeName)) {
806                    String clause = index++ == 0 ? "" : " or ";
807                    DocumentTypeService docSrv = KEWServiceLocator.getDocumentTypeService();
808                    DocumentType docType = docSrv.findByNameCaseInsensitive(documentTypeName.trim());
809                    if (docType != null) {
810                        if (documentTypeName.contains("*") || documentTypeName.contains("%")) {
811                            addDocumentTypeLikeNameToSearchOn(returnSql, documentTypeName.trim(), clause);
812                        } else {
813                            addDocumentTypeNameToSearchOn(returnSql, documentTypeName.trim(), clause);
814                        }
815                        if (docType.getChildrenDocTypes() != null) {
816                            addChildDocumentTypes(returnSql, docType.getChildrenDocTypes());
817                        }
818                    } else{
819                        addDocumentTypeLikeNameToSearchOn(returnSql, documentTypeName.trim(), clause);
820                    }
821                }
822            }
823        }
824        if (returnSql.length() > 0) {
825            returnSql.insert(0, "(");
826            returnSql.insert(0, whereClausePredicatePrefix);
827            returnSql.append(")");
828        }
829        return returnSql.toString();
830    }
831
832    public void addChildDocumentTypes(StringBuilder whereSql, Collection<DocumentType> childDocumentTypes) {
833        for (DocumentType child : childDocumentTypes) {
834            addDocumentTypeNameToSearchOn(whereSql, child.getName());
835            addChildDocumentTypes(whereSql, child.getChildrenDocTypes());
836        }
837    }
838
839    public void addDocumentTypeNameToSearchOn(StringBuilder whereSql, String documentTypeName) {
840        this.addDocumentTypeNameToSearchOn(whereSql, documentTypeName, " or ");
841    }
842
843    public void addDocumentTypeNameToSearchOn(StringBuilder whereSql, String documentTypeName, String clause) {
844        whereSql.append(clause).append("upper(DOC1.DOC_TYP_NM) = '" + documentTypeName.toUpperCase() + "'");
845    }
846    public void addDocumentTypeLikeNameToSearchOn(StringBuilder whereSql, String documentTypeName, String clause) {
847        documentTypeName = documentTypeName.replace('*', '%');
848        whereSql.append(clause).append(" upper(DOC1.DOC_TYP_NM) LIKE '" + documentTypeName.toUpperCase() + "'");
849    }
850
851    public String getDocRouteNodeSql(String documentTypeFullName, String routeNodeName, RouteNodeLookupLogic docRouteLevelLogic, String whereClausePredicatePrefix) {
852        // -1 is the default 'blank' choice from the route node drop down a number is used because the ojb RouteNode object is used to
853        // render the node choices on the form.
854        String returnSql = "";
855        if (StringUtils.isNotBlank(routeNodeName)) {
856            if (docRouteLevelLogic == null) {
857                docRouteLevelLogic = RouteNodeLookupLogic.EXACTLY;
858            }
859            StringBuilder routeNodeCriteria = new StringBuilder("and " + ROUTE_NODE_TABLE + ".NM ");
860            if (RouteNodeLookupLogic.EXACTLY == docRouteLevelLogic) {
861                        routeNodeCriteria.append("= '" + getDbPlatform().escapeString(routeNodeName) + "' ");
862            } else {
863                routeNodeCriteria.append("in (");
864                // below buffer used to facilitate the addition of the string ", " to separate out route node names
865                StringBuilder routeNodeInCriteria = new StringBuilder();
866                boolean foundSpecifiedNode = false;
867                List<RouteNode> routeNodes = KEWServiceLocator.getRouteNodeService().getFlattenedNodes(getValidDocumentType(documentTypeFullName), true);
868                for (RouteNode routeNode : routeNodes) {
869                    if (routeNodeName.equals(routeNode.getRouteNodeName())) {
870                        // current node is specified node so we ignore it outside of the boolean below
871                        foundSpecifiedNode = true;
872                        continue;
873                    }
874                    // below logic should be to add the current node to the criteria if we haven't found the specified node
875                    // and the logic qualifier is 'route nodes before specified'... or we have found the specified node and
876                    // the logic qualifier is 'route nodes after specified'
877                    if ( (!foundSpecifiedNode && RouteNodeLookupLogic.BEFORE == docRouteLevelLogic) ||
878                         (foundSpecifiedNode && RouteNodeLookupLogic.AFTER == docRouteLevelLogic) ) {
879                        if (routeNodeInCriteria.length() > 0) {
880                            routeNodeInCriteria.append(", ");
881                        }
882                        routeNodeInCriteria.append("'" + routeNode.getRouteNodeName() + "'");
883                    }
884                }
885                if (routeNodeInCriteria.length() > 0) {
886                    routeNodeCriteria.append(routeNodeInCriteria);
887                } else {
888                    routeNodeCriteria.append("''");
889                }
890                routeNodeCriteria.append(") ");
891            }
892            returnSql = whereClausePredicatePrefix + "DOC_HDR.DOC_HDR_ID = " + ROUTE_NODE_INST_TABLE + ".DOC_HDR_ID and " + ROUTE_NODE_INST_TABLE + ".RTE_NODE_ID = " + ROUTE_NODE_TABLE + ".RTE_NODE_ID and " + ROUTE_NODE_INST_TABLE + ".ACTV_IND = 1 " + routeNodeCriteria.toString() + " ";
893        }
894        return returnSql;
895    }
896
897    public String getDocumentStatusSql(List<DocumentStatus> documentStatuses, List<DocumentStatusCategory> categories, String whereClausePredicatePrefix) {
898        if (CollectionUtils.isEmpty(documentStatuses) && CollectionUtils.isEmpty(categories)) {
899            return whereClausePredicatePrefix + "DOC_HDR.DOC_HDR_STAT_CD != '" + DocumentStatus.INITIATED.getCode() + "'";
900        } else {
901            // include all given document statuses
902            Set<DocumentStatus> statusesToInclude = new HashSet<DocumentStatus>(documentStatuses);
903
904            // add all statuses from each category
905            for (DocumentStatusCategory category : categories) {
906                Set<DocumentStatus> categoryStatuses = DocumentStatus.getStatusesForCategory(category);
907                statusesToInclude.addAll(categoryStatuses);
908            }
909
910            Set<String> statusCodes = new HashSet<String>();
911            for (DocumentStatus statusToInclude : statusesToInclude) {
912                statusCodes.add("'" + getDbPlatform().escapeString(statusToInclude.getCode()) + "'");
913            }
914            return whereClausePredicatePrefix + " DOC_HDR.DOC_HDR_STAT_CD in (" + StringUtils.join(statusCodes, ", ") +")";
915        }
916    }
917
918    /**
919     * This method generates the where clause fragment related to Application Document Status.
920     * If the Status values only are defined, search for the appDocStatus value in the route header.
921     * If either the transition from/to dates are defined, search agains the status transition history.
922     */
923    public String getAppDocStatusesSql(List<String> appDocStatuses, String whereClausePredicatePrefix, int statusTransitionWhereClauseLength) {
924        if (CollectionUtils.isEmpty(appDocStatuses)) {
925            return "";
926        } else {
927            String inList = buildAppDocStatusInList(appDocStatuses);
928
929            if (statusTransitionWhereClauseLength > 0){
930                return whereClausePredicatePrefix + " STAT_TRAN.APP_DOC_STAT_TO" + inList;
931            } else {
932                return whereClausePredicatePrefix + " DOC_HDR.APP_DOC_STAT" + inList;
933            }
934        }
935    }
936
937    private String buildAppDocStatusInList(List<String> appDocStatuses) {
938        StringBuilder sql = new StringBuilder(" IN (");
939
940        boolean first = true;
941        for (String appDocStatus : appDocStatuses) {
942            // commas before each element except the first one
943            if (first) {
944                first = false;
945            } else {
946                sql.append(",");
947            }
948
949            sql.append("'");
950            sql.append(getDbPlatform().escapeString(appDocStatus.trim()));
951            sql.append("'");
952        }
953
954        sql.append(")");
955
956        return sql.toString();
957    }
958
959    public String getGeneratedPredicatePrefix(int whereClauseSize) {
960        return (whereClauseSize > 0) ? " and " : " where ";
961    }
962
963    public String establishDateString(DateTime fromDate, DateTime toDate, String tableName, String tableAlias, String colName, String whereStatementClause) {
964
965        String fromDateValue = null;
966        if (fromDate != null) {
967            fromDateValue = CoreApiServiceLocator.getDateTimeService().toDateString(fromDate.toDate());
968        }
969
970        String toDateValue = null;
971        if (toDate != null) {
972            toDateValue = CoreApiServiceLocator.getDateTimeService().toDateString(toDate.toDate());
973            toDateValue += " 23:59:59";
974        }
975
976        String searchValue = null;
977        if (fromDateValue != null && toDateValue != null) {
978            searchValue = fromDateValue + " .. " + toDateValue;
979        } else if (fromDateValue != null) {
980            searchValue = ">= " + fromDateValue;
981        } else if (toDateValue != null) {
982            searchValue = "<= " + toDateValue;
983        } else {
984            return "";
985        }
986
987        Criteria crit = getSqlBuilder().createCriteria(colName, searchValue, tableName, tableAlias, java.sql.Date.class, true, true);
988        return new StringBuilder(whereStatementClause).append(crit.buildWhere()).toString();
989
990    }
991
992    public DatabasePlatform getDbPlatform() {
993        if (dbPlatform == null) {
994            dbPlatform = (DatabasePlatform) GlobalResourceLoader.getService(RiceConstants.DB_PLATFORM);
995        }
996        return dbPlatform;
997    }
998
999    public SqlBuilder getSqlBuilder() {
1000        if(sqlBuilder == null){
1001            sqlBuilder = new SqlBuilder();
1002            sqlBuilder.setDbPlatform(getDbPlatform());
1003            sqlBuilder.setDateTimeService(CoreApiServiceLocator.getDateTimeService());
1004        }
1005        return this.sqlBuilder;
1006    }
1007
1008    public void setSqlBuilder(SqlBuilder sqlBuilder) {
1009        this.sqlBuilder = sqlBuilder;
1010    }
1011
1012    /**
1013     * A helper method for determining whether any searchable attributes are in use for the search.
1014     *
1015     * @return True if the search criteria contains at least one searchable attribute or the criteria's doc type name is
1016     * non-blank; false otherwise.
1017     */
1018    protected boolean isUsingAtLeastOneSearchAttribute(DocumentSearchCriteria criteria) {
1019        return criteria.getDocumentAttributeValues().size() > 0 || StringUtils.isNotBlank(criteria.getDocumentTypeName());
1020    }
1021
1022    protected org.kuali.rice.kew.api.doctype.DocumentTypeService getApiDocumentTypeService() {
1023        if (apiDocumentTypeService == null) {
1024            apiDocumentTypeService = KewApiServiceLocator.getDocumentTypeService();
1025        }
1026
1027        return apiDocumentTypeService;
1028    }
1029
1030    protected void setApiDocumentTypeService(org.kuali.rice.kew.api.doctype.DocumentTypeService apiDocumentTypeService) {
1031        this.apiDocumentTypeService = apiDocumentTypeService;
1032    }
1033
1034}