Coverage Report

Coverage Report - org.kuali.rice.core.framework.persistence.jdbc.sql.SqlBuilder

Classes in this File

Line Coverage

Branch Coverage

Complexity

SqlBuilder

0/179

0/100

3.133

SqlBuilder$JoinType

0/1

N/A

3.133

SqlBuilder$SQLBuilderException

0/2

N/A

3.133

 /**
  * Copyright 2005-2011 The Kuali Foundation
  *
  * Licensed under the Educational Community License, Version 2.0 (the "License");
  * you may not use this file except in compliance with the License.
  * You may obtain a copy of the License at
  *
  * http://www.opensource.org/licenses/ecl2.php
  *
  * Unless required by applicable law or agreed to in writing, software
  * distributed under the License is distributed on an "AS IS" BASIS,
  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  * See the License for the specific language governing permissions and
  * limitations under the License.
  */
 package org.kuali.rice.core.framework.persistence.jdbc.sql;
 
 import org.apache.commons.lang.StringUtils;
 import org.kuali.rice.core.api.CoreConstants;
 import org.kuali.rice.core.api.datetime.DateTimeService;
 import org.kuali.rice.core.api.exception.RiceRuntimeException;
 import org.kuali.rice.core.api.resourceloader.GlobalResourceLoader;
 import org.kuali.rice.core.api.search.SearchOperator;
 import org.kuali.rice.core.api.util.RiceConstants;
 import org.kuali.rice.core.api.util.type.TypeUtils;
 import org.kuali.rice.core.framework.persistence.platform.DatabasePlatform;
 import org.kuali.rice.core.web.format.BooleanFormatter;
 
 import java.math.BigDecimal;
 import java.sql.Date;
 import java.sql.Timestamp;
 import java.text.ParseException;
 import java.text.SimpleDateFormat;
 
 /**
  * This is a description of what this class does - Garey don't forget to fill this in.
  *
  * @author Kuali Rice Team (rice.collab@kuali.org)
  *
  */
 public class SqlBuilder {
         private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(SqlBuilder.class);
 
         private DateTimeService dateTimeService;
         private DatabasePlatform dbPlatform;
         
         public static final  String EMPTY_STRING = "";
 
     public static final class JoinType {
 
         }
 
         /**
          * @param clazz
          * @return true if the given Class is an join type
          * @throws IllegalArgumentException
          *             if the given Class is null
          */
         public static boolean isJoinClass(Class clazz) {
                 return clazz.isAssignableFrom(JoinType.class);
         }
 
         public Criteria createCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType) {
                 return createCriteria(columnName, searchValue, tableName, tableAlias, propertyType, false, true);
         }
 
     public Criteria createCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType, boolean caseInsensitive) {
         return createCriteria(columnName, searchValue, tableName, tableAlias, propertyType, caseInsensitive, true);
     }
 
         public Criteria createCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType, boolean caseInsensitive, boolean allowWildcards) {
 
                 if (propertyType == null) {
                         return null;
                 }
 
                 Criteria criteria = new Criteria(tableName, tableAlias);
                 criteria.setDbPlatform(this.getDbPlatform());
 
                 // build criteria
                 addCriteria(columnName, searchValue, propertyType, caseInsensitive, allowWildcards, criteria);
                 return criteria;
         }
 
         public void andCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType, boolean caseInsensitive, boolean allowWildcards, Criteria addToThisCriteria) {
                 Criteria crit = createCriteria(columnName,searchValue, tableName, tableAlias, propertyType, caseInsensitive, allowWildcards);
 
                 addToThisCriteria.and(crit);
         }
         public void andCriteria(Criteria addToThisCriteria, Criteria newCriteria) {
                 addToThisCriteria.and(newCriteria);
         }
         public void orCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType, boolean caseInsensitive, boolean allowWildcards, Criteria addToThisCriteria) {
                 Criteria crit = createCriteria(columnName, searchValue,tableName, tableAlias, propertyType, caseInsensitive, allowWildcards);
 
                 addToThisCriteria.or(crit);
         }
 
         public void addCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, boolean allowWildcards, Criteria criteria) {
 
                 if(SqlBuilder.isJoinClass(propertyType)){ // treat this as a join table.
                         String temp = SQLUtils.cleanString(propertyValue);
                         criteria.eq(propertyName, temp, propertyType);
                         return;
                 }
 
                 if (StringUtils.contains(propertyValue, SearchOperator.OR.op())) {
                         addOrCriteria(propertyName, propertyValue, propertyType, caseInsensitive, criteria, allowWildcards);
                         return;
                 }
 
                 if ( StringUtils.contains(propertyValue, SearchOperator.AND.op())) {
                         addAndCriteria(propertyName, propertyValue, propertyType, caseInsensitive, criteria, allowWildcards);
                         return;
                 }
 
                 if (TypeUtils.isStringClass(propertyType)) {
                         if (StringUtils.contains(propertyValue,
                                         SearchOperator.NOT.op())) {
                                 addNotCriteria(propertyName, propertyValue, propertyType,
                                                 caseInsensitive, criteria, allowWildcards);
             } else if (propertyValue != null && (
                                             StringUtils.contains(propertyValue, SearchOperator.BETWEEN.op())
                                             || propertyValue.startsWith(">")
                                             || propertyValue.startsWith("<") ) ) {
                                 addStringRangeCriteria(propertyName, propertyValue, criteria, propertyType, caseInsensitive, allowWildcards);
                         } else {
                                 //if (!allowWildcards) {
                                 //        propertyValue = StringUtils.replace(propertyValue, "*", "\\*");
                                 //}
                                 // KULRICE-85 : made string searches case insensitive - used new
                                 // DBPlatform function to force strings to upper case
                                 if (caseInsensitive) {
                                         // TODO: What to do here now that the JPA version does not extend platform aware?
                                         propertyName = getDbPlatform().getUpperCaseFunction() + "(__JPA_ALIAS__." + propertyName + ")";
                                         //propertyName = "UPPER("+ tableAlias + "." + propertyName + ")";
                                         propertyValue = propertyValue.toUpperCase();
                                 }
                                 criteria.like(propertyName, propertyValue,propertyType, allowWildcards);
                         }
                 } else if (TypeUtils.isIntegralClass(propertyType) || TypeUtils.isDecimalClass(propertyType)) {
                         addNumericRangeCriteria(propertyName, propertyValue, criteria, propertyType);
                 } else if (TypeUtils.isTemporalClass(propertyType)) {
                         addDateRangeCriteria(propertyName, propertyValue, criteria, propertyType);
                 } else if (TypeUtils.isBooleanClass(propertyType)) {
                         String temp = SQLUtils.cleanString(propertyValue);
                         criteria.eq(propertyName, new BooleanFormatter().convertFromPresentationFormat(temp), propertyType);
                 } else {
                         LOG.error("not adding criterion for: " + propertyName + "," + propertyType + "," + propertyValue);
                 }
         }
 
         private void addOrCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, Criteria criteria, boolean allowWildcards) {
                 addLogicalOperatorCriteria(propertyName, propertyValue, propertyType, caseInsensitive, criteria, SearchOperator.OR.op(), allowWildcards);
         }
 
         private void addAndCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, Criteria criteria, boolean allowWildcards) {
                 addLogicalOperatorCriteria(propertyName, propertyValue, propertyType, caseInsensitive, criteria, SearchOperator.AND.op(), allowWildcards);
         }
 
         private void addNotCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, Criteria criteria, boolean allowWildcards) {
                 String[] splitPropVal = StringUtils.split(propertyValue, SearchOperator.NOT.op());
 
                 int strLength = splitPropVal.length;
                 // if more than one NOT operator assume an implicit and (i.e. !a!b = !a&!b)
                 if (strLength > 1) {
                         String expandedNot = SearchOperator.NOT + StringUtils.join(splitPropVal, SearchOperator.AND.op() + SearchOperator.NOT.op());
                         // we know that since this method is called, treatWildcardsAndOperatorsAsLiteral is false
                         addCriteria(propertyName, expandedNot, propertyType, caseInsensitive, allowWildcards, criteria);
                 } else {
                         // only one so add a not like
                         criteria.notLike(propertyName, splitPropVal[0], propertyType, allowWildcards);
                 }
         }
 
         private void addLogicalOperatorCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, Criteria criteria, String splitValue, boolean allowWildcards) {
                 String[] splitPropVal = StringUtils.split(propertyValue, splitValue);
 
                 Criteria subCriteria = new Criteria("N/A");
                 for (String element : splitPropVal) {
                         Criteria predicate = new Criteria("N/A", criteria.getAlias());
                         // we know that since this method is called, treatWildcardsAndOperatorsAsLiteral is false
                         addCriteria(propertyName, element, propertyType, caseInsensitive, allowWildcards, predicate);
                         if (splitValue == SearchOperator.OR.op()) {
                                 subCriteria.or(predicate);
                         }
                         if (splitValue == SearchOperator.AND.op()) {
                                 subCriteria.and(predicate);
                         }
                 }
 
                 criteria.and(subCriteria);
         }
 
         private Timestamp parseDate(String dateString) {
                 dateString = dateString.trim();
                 try {
                         Timestamp dt =  this.getDateTimeService().convertToSqlTimestamp(dateString);
                         return dt;
                 } catch (ParseException ex) {
                         return null;
                 }
         }
         public boolean isValidDate(String dateString){
                 //FIXME: wtf - weird!
                 try {
                         this.createCriteria("date", dateString.trim(), "validation", "test", Date.class);
                         return true;
                 } catch (Exception ex) {
                         return false;
                 }
         }
 
         public static boolean containsRangeCharacters(String string){
                 boolean bRet = false;
                 for (SearchOperator op : SearchOperator.RANGE_CHARACTERS) {
             if(StringUtils.contains(string, op.op())){
                     bRet = true;
             }
         }
                 return bRet;
         }
 
         private void addDateRangeCriteria(String propertyName, String propertyValue, Criteria criteria, Class propertyType) {
 
                 if (StringUtils.contains(propertyValue, SearchOperator.BETWEEN.op())) {
                         String[] rangeValues = propertyValue.split("\\.\\."); // this translate to the .. operator
                         criteria.between(propertyName, parseDate(SQLUtils.cleanDate(rangeValues[0])), parseDate(cleanUpperBound(SQLUtils.cleanDate(rangeValues[1]))), propertyType);
                 } else if (propertyValue.startsWith(SearchOperator.GREATER_THAN_EQUAL.op())) {
                         criteria.gte(propertyName, parseDate(SQLUtils.cleanDate(propertyValue)), propertyType);
                 } else if (propertyValue.startsWith(SearchOperator.LESS_THAN_EQUAL.op())) {
                         criteria.lte(propertyName, parseDate(cleanUpperBound(SQLUtils.cleanDate(propertyValue))),propertyType);
                 } else if (propertyValue.startsWith(SearchOperator.GREATER_THAN.op())) {
                         // we clean the upper bound here because if you say >12/22/09, it translates greater than
                         // the date... as in whole date. ie. the next day on.
                         criteria.gt(propertyName, parseDate(cleanUpperBound(SQLUtils.cleanDate(propertyValue))), propertyType);
                 } else if (propertyValue.startsWith(SearchOperator.LESS_THAN.op())) {
                         criteria.lt(propertyName, parseDate(SQLUtils.cleanDate(propertyValue)), propertyType);
                 } else {
                         String sDate = convertSimpleDateToDateRange(SQLUtils.cleanDate(propertyValue));
                         if(sDate.contains(SearchOperator.BETWEEN.op())){
                                 addDateRangeCriteria(propertyName, sDate, criteria, propertyType);
                         }else{
                                 criteria.eq(propertyName, parseDate(sDate), propertyType);
                         }
                 }
         }
 
         public static boolean isValidNumber(String value){
                 try{
                 stringToBigDecimal(value);
                         return true;
                 }catch(Exception ex){
                         return false;
                 }
         }
 
         public static String cleanNumeric(String value){
                 String cleanedValue = value.replaceAll("[^-0-9.]", "");
                 // ensure only one "minus" at the beginning, if any
                 if (cleanedValue.lastIndexOf('-') > 0) {
                         if (cleanedValue.charAt(0) == '-') {
                                 cleanedValue = "-" + cleanedValue.replaceAll("-", "");
                         } else {
                                 cleanedValue = cleanedValue.replaceAll("-", "");
                         }
                 }
                 // ensure only one decimal in the string
                 int decimalLoc = cleanedValue.lastIndexOf('.');
                 if (cleanedValue.indexOf('.') != decimalLoc) {
                         cleanedValue = cleanedValue.substring(0, decimalLoc).replaceAll("\\.", "") + cleanedValue.substring(decimalLoc);
                 }
                 return cleanedValue;
         }
 
         public static BigDecimal stringToBigDecimal(String value) {
 
                 //try {
                         return new BigDecimal(cleanNumeric(value));
                 /*
                 } catch (NumberFormatException ex) {
                         GlobalVariables.getMessageMap().putError(KRADConstants.DOCUMENT_ERRORS, RiceKeyConstants.ERROR_CUSTOM, new String[] { "Invalid Numeric Input: " + value });
                         return null;
                 }*/
         }
 
         private void addNumericRangeCriteria(String propertyName, String propertyValue, Criteria criteria, Class propertyType) {
 
                 if (StringUtils.contains(propertyValue, SearchOperator.BETWEEN.op())) {
                         String[] rangeValues = propertyValue.split("\\.\\."); // this translate to the .. operator
                         criteria.between(propertyName, stringToBigDecimal(rangeValues[0]), stringToBigDecimal(rangeValues[1]), propertyType);
                 } else if (propertyValue.startsWith(SearchOperator.GREATER_THAN_EQUAL.op())) {
                         criteria.gte(propertyName, stringToBigDecimal(propertyValue), propertyType);
                 } else if (propertyValue.startsWith(SearchOperator.LESS_THAN_EQUAL.op())) {
                         criteria.lte(propertyName, stringToBigDecimal(propertyValue), propertyType);
                 } else if (propertyValue.startsWith(SearchOperator.GREATER_THAN.op())) {
                         criteria.gt(propertyName, stringToBigDecimal(propertyValue), propertyType);
                 } else if (propertyValue.startsWith(SearchOperator.LESS_THAN.op())) {
                         criteria.lt(propertyName, stringToBigDecimal(propertyValue), propertyType);
                 } else {
                         criteria.eq(propertyName, stringToBigDecimal(propertyValue), propertyType);
                 }
         }
 
         private void addStringRangeCriteria(String propertyName, String propertyValue, Criteria criteria, Class propertyType, boolean caseInsensitive, boolean allowWildcards) {
 
                 if (StringUtils.contains(propertyValue, SearchOperator.BETWEEN.op())) {
                         String[] rangeValues = propertyValue.split("\\.\\."); // this translate to the .. operator
                         propertyName = this.getCaseAndLiteralPropertyName(propertyName, caseInsensitive);
                         String val1 = this.getCaseAndLiteralPropertyValue(rangeValues[0], caseInsensitive, allowWildcards);
                         String val2 = this.getCaseAndLiteralPropertyValue(rangeValues[1], caseInsensitive, allowWildcards);
                         criteria.between(propertyName, val1, val2, propertyType);
                 } else{
                         propertyName = this.getCaseAndLiteralPropertyName(propertyName, caseInsensitive);
                         String value = this.getCaseAndLiteralPropertyValue(SQLUtils.cleanString(propertyValue), caseInsensitive, allowWildcards);
 
                         if (propertyValue.startsWith(SearchOperator.GREATER_THAN_EQUAL.op())) {
                                 criteria.gte(propertyName, value, propertyType);
                         } else if (propertyValue.startsWith(SearchOperator.LESS_THAN_EQUAL.op())) {
                                 criteria.lte(propertyName, value, propertyType);
                         } else if (propertyValue.startsWith(SearchOperator.GREATER_THAN.op())) {
                                 criteria.gt(propertyName, value, propertyType);
                         } else if (propertyValue.startsWith(SearchOperator.LESS_THAN.op())) {
                                 criteria.lt(propertyName, value, propertyType);
                         }
                 }
         }
 
         private String getCaseAndLiteralPropertyName(String propertyName, boolean caseInsensitive){
                 // KULRICE-85 : made string searches case insensitive - used new
                 // DBPlatform function to force strings to upper case
                 if (caseInsensitive) {
                         // TODO: What to do here now that the JPA version does not extend platform aware?
                         propertyName = getDbPlatform().getUpperCaseFunction() + "(__JPA_ALIAS__." + propertyName + ")";
 
                 }
                 return propertyName;
         }
         private String getCaseAndLiteralPropertyValue(String propertyValue, boolean caseInsensitive, boolean allowWildcards){
                 //if (!allowWildcards) {
                 //        propertyValue = StringUtils.replace(propertyValue, "*", "\\*");
                 //}
                 // KULRICE-85 : made string searches case insensitive - used new
                 // DBPlatform function to force strings to upper case
                 if (caseInsensitive) {
                         //propertyName = "UPPER("+ tableAlias + "." + propertyName + ")";
                         propertyValue = propertyValue.toUpperCase();
                 }
                 return propertyValue;
         }
 
 
         protected DateTimeService getDateTimeService(){
                 if (dateTimeService == null) {
                         dateTimeService = GlobalResourceLoader.getService(CoreConstants.Services.DATETIME_SERVICE);
             }
             return dateTimeService;
         }
 
         /**
          * @param dateTimeService
          *            the dateTimeService to set
          */
         public void setDateTimeService(DateTimeService dateTimeService) {
                 this.dateTimeService = dateTimeService;
         }
 
         public DatabasePlatform getDbPlatform() {
             if (dbPlatform == null) {
                     dbPlatform = (DatabasePlatform) GlobalResourceLoader.getService(RiceConstants.DB_PLATFORM);
             }
             return dbPlatform;
     }
 
         public void setDbPlatform(DatabasePlatform dbPlatform){
                 this.dbPlatform = dbPlatform;
         }
 
          /**
      * When dealing with upperbound dates, it is a business requirement that if a timestamp isn't already
      * stated append 23:59:59 to the end of the date.  This ensures that you are searching for the entire
      * day.
      */
     private String cleanUpperBound(String stringDate){
             final java.sql.Timestamp dt;
             try {
                         dt = getDateTimeService().convertToSqlTimestamp(stringDate);
                 } catch (ParseException e) {
                         throw new SQLBuilderException(e);
                 }
                 SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");
 
                 if("00:00:00".equals(sdfTime.format(dt)) && !StringUtils.contains(stringDate, "00:00:00") && !StringUtils.contains(stringDate, "12:00 AM")){
                         stringDate = stringDate + " 23:59:59";
                 }
                 return stringDate;
     }
 
     /**
     *
     * This method will take a whole date like 03/02/2009 and convert it into
     * 03/02/2009 .. 03/02/20009 00:00:00
     *
     * This is used for non-range searchable attributes
     *
     * @param stringDate
     * @return
     */
    private String convertSimpleDateToDateRange(String stringDate){
            final java.sql.Timestamp dt;
            try {
                            dt = getDateTimeService().convertToSqlTimestamp(stringDate);
            } catch (ParseException e) {
                    throw new SQLBuilderException(e);
            }
            SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");
 
            if("00:00:00".equals(sdfTime.format(dt)) && !StringUtils.contains(stringDate, "00:00:00") && !StringUtils.contains(stringDate, "12:00 AM")){
                    stringDate = stringDate + " .. " + stringDate + " 23:59:59";
            }
 
                 return stringDate;
    }
 
         public static final class SQLBuilderException extends RiceRuntimeException {
                 public SQLBuilderException(Throwable t) {
                         super(t);
                 }
         }
 }

1		/**
2		* Copyright 2005-2011 The Kuali Foundation
3		*
4		* Licensed under the Educational Community License, Version 2.0 (the "License");
5		* you may not use this file except in compliance with the License.
6		* You may obtain a copy of the License at
7		*
8		* http://www.opensource.org/licenses/ecl2.php
9		*
10		* Unless required by applicable law or agreed to in writing, software
11		* distributed under the License is distributed on an "AS IS" BASIS,
12		* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13		* See the License for the specific language governing permissions and
14		* limitations under the License.
15		*/
16		package org.kuali.rice.core.framework.persistence.jdbc.sql;
17
18		import org.apache.commons.lang.StringUtils;
19		import org.kuali.rice.core.api.CoreConstants;
20		import org.kuali.rice.core.api.datetime.DateTimeService;
21		import org.kuali.rice.core.api.exception.RiceRuntimeException;
22		import org.kuali.rice.core.api.resourceloader.GlobalResourceLoader;
23		import org.kuali.rice.core.api.search.SearchOperator;
24		import org.kuali.rice.core.api.util.RiceConstants;
25		import org.kuali.rice.core.api.util.type.TypeUtils;
26		import org.kuali.rice.core.framework.persistence.platform.DatabasePlatform;
27		import org.kuali.rice.core.web.format.BooleanFormatter;
28
29		import java.math.BigDecimal;
30		import java.sql.Date;
31		import java.sql.Timestamp;
32		import java.text.ParseException;
33		import java.text.SimpleDateFormat;
34
35		/**
36		* This is a description of what this class does - Garey don't forget to fill this in.
37		*
38		* @author Kuali Rice Team (rice.collab@kuali.org)
39		*
40		*/
41	0	public class SqlBuilder {
42	0	private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(SqlBuilder.class);
43
44		private DateTimeService dateTimeService;
45		private DatabasePlatform dbPlatform;
46
47		public static final String EMPTY_STRING = "";
48
49	0	public static final class JoinType {
50
51		}
52
53		/**
54		* @param clazz
55		* @return true if the given Class is an join type
56		* @throws IllegalArgumentException
57		* if the given Class is null
58		*/
59		public static boolean isJoinClass(Class clazz) {
60	0	return clazz.isAssignableFrom(JoinType.class);
61		}
62
63		public Criteria createCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType) {
64	0	return createCriteria(columnName, searchValue, tableName, tableAlias, propertyType, false, true);
65		}
66
67		public Criteria createCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType, boolean caseInsensitive) {
68	0	return createCriteria(columnName, searchValue, tableName, tableAlias, propertyType, caseInsensitive, true);
69		}
70
71		public Criteria createCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType, boolean caseInsensitive, boolean allowWildcards) {
72
73	0	if (propertyType == null) {
74	0	return null;
75		}
76
77	0	Criteria criteria = new Criteria(tableName, tableAlias);
78	0	criteria.setDbPlatform(this.getDbPlatform());
79
80		// build criteria
81	0	addCriteria(columnName, searchValue, propertyType, caseInsensitive, allowWildcards, criteria);
82	0	return criteria;
83		}
84
85		public void andCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType, boolean caseInsensitive, boolean allowWildcards, Criteria addToThisCriteria) {
86	0	Criteria crit = createCriteria(columnName,searchValue, tableName, tableAlias, propertyType, caseInsensitive, allowWildcards);
87
88	0	addToThisCriteria.and(crit);
89	0	}
90		public void andCriteria(Criteria addToThisCriteria, Criteria newCriteria) {
91	0	addToThisCriteria.and(newCriteria);
92	0	}
93		public void orCriteria(String columnName, String searchValue, String tableName, String tableAlias, Class propertyType, boolean caseInsensitive, boolean allowWildcards, Criteria addToThisCriteria) {
94	0	Criteria crit = createCriteria(columnName, searchValue,tableName, tableAlias, propertyType, caseInsensitive, allowWildcards);
95
96	0	addToThisCriteria.or(crit);
97	0	}
98
99		public void addCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, boolean allowWildcards, Criteria criteria) {
100
101	0	if(SqlBuilder.isJoinClass(propertyType)){ // treat this as a join table.
102	0	String temp = SQLUtils.cleanString(propertyValue);
103	0	criteria.eq(propertyName, temp, propertyType);
104	0	return;
105		}
106
107	0	if (StringUtils.contains(propertyValue, SearchOperator.OR.op())) {
108	0	addOrCriteria(propertyName, propertyValue, propertyType, caseInsensitive, criteria, allowWildcards);
109	0	return;
110		}
111
112	0	if ( StringUtils.contains(propertyValue, SearchOperator.AND.op())) {
113	0	addAndCriteria(propertyName, propertyValue, propertyType, caseInsensitive, criteria, allowWildcards);
114	0	return;
115		}
116
117	0	if (TypeUtils.isStringClass(propertyType)) {
118	0	if (StringUtils.contains(propertyValue,
119		SearchOperator.NOT.op())) {
120	0	addNotCriteria(propertyName, propertyValue, propertyType,
121		caseInsensitive, criteria, allowWildcards);
122	0	} else if (propertyValue != null && (
123		StringUtils.contains(propertyValue, SearchOperator.BETWEEN.op())
124		\|\| propertyValue.startsWith(">")
125		\|\| propertyValue.startsWith("<") ) ) {
126	0	addStringRangeCriteria(propertyName, propertyValue, criteria, propertyType, caseInsensitive, allowWildcards);
127		} else {
128		//if (!allowWildcards) {
129		// propertyValue = StringUtils.replace(propertyValue, "", "\\");
130		//}
131		// KULRICE-85 : made string searches case insensitive - used new
132		// DBPlatform function to force strings to upper case
133	0	if (caseInsensitive) {
134		// TODO: What to do here now that the JPA version does not extend platform aware?
135	0	propertyName = getDbPlatform().getUpperCaseFunction() + "(__JPA_ALIAS__." + propertyName + ")";
136		//propertyName = "UPPER("+ tableAlias + "." + propertyName + ")";
137	0	propertyValue = propertyValue.toUpperCase();
138		}
139	0	criteria.like(propertyName, propertyValue,propertyType, allowWildcards);
140		}
141	0	} else if (TypeUtils.isIntegralClass(propertyType) \|\| TypeUtils.isDecimalClass(propertyType)) {
142	0	addNumericRangeCriteria(propertyName, propertyValue, criteria, propertyType);
143	0	} else if (TypeUtils.isTemporalClass(propertyType)) {
144	0	addDateRangeCriteria(propertyName, propertyValue, criteria, propertyType);
145	0	} else if (TypeUtils.isBooleanClass(propertyType)) {
146	0	String temp = SQLUtils.cleanString(propertyValue);
147	0	criteria.eq(propertyName, new BooleanFormatter().convertFromPresentationFormat(temp), propertyType);
148	0	} else {
149	0	LOG.error("not adding criterion for: " + propertyName + "," + propertyType + "," + propertyValue);
150		}
151	0	}
152
153		private void addOrCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, Criteria criteria, boolean allowWildcards) {
154	0	addLogicalOperatorCriteria(propertyName, propertyValue, propertyType, caseInsensitive, criteria, SearchOperator.OR.op(), allowWildcards);
155	0	}
156
157		private void addAndCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, Criteria criteria, boolean allowWildcards) {
158	0	addLogicalOperatorCriteria(propertyName, propertyValue, propertyType, caseInsensitive, criteria, SearchOperator.AND.op(), allowWildcards);
159	0	}
160
161		private void addNotCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, Criteria criteria, boolean allowWildcards) {
162	0	String[] splitPropVal = StringUtils.split(propertyValue, SearchOperator.NOT.op());
163
164	0	int strLength = splitPropVal.length;
165		// if more than one NOT operator assume an implicit and (i.e. !a!b = !a&!b)
166	0	if (strLength > 1) {
167	0	String expandedNot = SearchOperator.NOT + StringUtils.join(splitPropVal, SearchOperator.AND.op() + SearchOperator.NOT.op());
168		// we know that since this method is called, treatWildcardsAndOperatorsAsLiteral is false
169	0	addCriteria(propertyName, expandedNot, propertyType, caseInsensitive, allowWildcards, criteria);
170	0	} else {
171		// only one so add a not like
172	0	criteria.notLike(propertyName, splitPropVal[0], propertyType, allowWildcards);
173		}
174	0	}
175
176		private void addLogicalOperatorCriteria(String propertyName, String propertyValue, Class propertyType, boolean caseInsensitive, Criteria criteria, String splitValue, boolean allowWildcards) {
177	0	String[] splitPropVal = StringUtils.split(propertyValue, splitValue);
178
179	0	Criteria subCriteria = new Criteria("N/A");
180	0	for (String element : splitPropVal) {
181	0	Criteria predicate = new Criteria("N/A", criteria.getAlias());
182		// we know that since this method is called, treatWildcardsAndOperatorsAsLiteral is false
183	0	addCriteria(propertyName, element, propertyType, caseInsensitive, allowWildcards, predicate);
184	0	if (splitValue == SearchOperator.OR.op()) {
185	0	subCriteria.or(predicate);
186		}
187	0	if (splitValue == SearchOperator.AND.op()) {
188	0	subCriteria.and(predicate);
189		}
190		}
191
192	0	criteria.and(subCriteria);
193	0	}
194
195		private Timestamp parseDate(String dateString) {
196	0	dateString = dateString.trim();
197		try {
198	0	Timestamp dt = this.getDateTimeService().convertToSqlTimestamp(dateString);
199	0	return dt;
200	0	} catch (ParseException ex) {
201	0	return null;
202		}
203		}
204		public boolean isValidDate(String dateString){
205		//FIXME: wtf - weird!
206		try {
207	0	this.createCriteria("date", dateString.trim(), "validation", "test", Date.class);
208	0	return true;
209	0	} catch (Exception ex) {
210	0	return false;
211		}
212		}
213
214		public static boolean containsRangeCharacters(String string){
215	0	boolean bRet = false;
216	0	for (SearchOperator op : SearchOperator.RANGE_CHARACTERS) {
217	0	if(StringUtils.contains(string, op.op())){
218	0	bRet = true;
219		}
220		}
221	0	return bRet;
222		}
223
224		private void addDateRangeCriteria(String propertyName, String propertyValue, Criteria criteria, Class propertyType) {
225
226	0	if (StringUtils.contains(propertyValue, SearchOperator.BETWEEN.op())) {
227	0	String[] rangeValues = propertyValue.split("\\.\\."); // this translate to the .. operator
228	0	criteria.between(propertyName, parseDate(SQLUtils.cleanDate(rangeValues[0])), parseDate(cleanUpperBound(SQLUtils.cleanDate(rangeValues[1]))), propertyType);
229	0	} else if (propertyValue.startsWith(SearchOperator.GREATER_THAN_EQUAL.op())) {
230	0	criteria.gte(propertyName, parseDate(SQLUtils.cleanDate(propertyValue)), propertyType);
231	0	} else if (propertyValue.startsWith(SearchOperator.LESS_THAN_EQUAL.op())) {
232	0	criteria.lte(propertyName, parseDate(cleanUpperBound(SQLUtils.cleanDate(propertyValue))),propertyType);
233	0	} else if (propertyValue.startsWith(SearchOperator.GREATER_THAN.op())) {
234		// we clean the upper bound here because if you say >12/22/09, it translates greater than
235		// the date... as in whole date. ie. the next day on.
236	0	criteria.gt(propertyName, parseDate(cleanUpperBound(SQLUtils.cleanDate(propertyValue))), propertyType);
237	0	} else if (propertyValue.startsWith(SearchOperator.LESS_THAN.op())) {
238	0	criteria.lt(propertyName, parseDate(SQLUtils.cleanDate(propertyValue)), propertyType);
239		} else {
240	0	String sDate = convertSimpleDateToDateRange(SQLUtils.cleanDate(propertyValue));
241	0	if(sDate.contains(SearchOperator.BETWEEN.op())){
242	0	addDateRangeCriteria(propertyName, sDate, criteria, propertyType);
243		}else{
244	0	criteria.eq(propertyName, parseDate(sDate), propertyType);
245		}
246		}
247	0	}
248
249		public static boolean isValidNumber(String value){
250		try{
251	0	stringToBigDecimal(value);
252	0	return true;
253	0	}catch(Exception ex){
254	0	return false;
255		}
256		}
257
258		public static String cleanNumeric(String value){
259	0	String cleanedValue = value.replaceAll("[^-0-9.]", "");
260		// ensure only one "minus" at the beginning, if any
261	0	if (cleanedValue.lastIndexOf('-') > 0) {
262	0	if (cleanedValue.charAt(0) == '-') {
263	0	cleanedValue = "-" + cleanedValue.replaceAll("-", "");
264		} else {
265	0	cleanedValue = cleanedValue.replaceAll("-", "");
266		}
267		}
268		// ensure only one decimal in the string
269	0	int decimalLoc = cleanedValue.lastIndexOf('.');
270	0	if (cleanedValue.indexOf('.') != decimalLoc) {
271	0	cleanedValue = cleanedValue.substring(0, decimalLoc).replaceAll("\\.", "") + cleanedValue.substring(decimalLoc);
272		}
273	0	return cleanedValue;
274		}
275
276		public static BigDecimal stringToBigDecimal(String value) {
277
278		//try {
279	0	return new BigDecimal(cleanNumeric(value));
280		/*
281		} catch (NumberFormatException ex) {
282		GlobalVariables.getMessageMap().putError(KRADConstants.DOCUMENT_ERRORS, RiceKeyConstants.ERROR_CUSTOM, new String[] { "Invalid Numeric Input: " + value });
283		return null;
284		}*/
285		}
286
287		private void addNumericRangeCriteria(String propertyName, String propertyValue, Criteria criteria, Class propertyType) {
288
289	0	if (StringUtils.contains(propertyValue, SearchOperator.BETWEEN.op())) {
290	0	String[] rangeValues = propertyValue.split("\\.\\."); // this translate to the .. operator
291	0	criteria.between(propertyName, stringToBigDecimal(rangeValues[0]), stringToBigDecimal(rangeValues[1]), propertyType);
292	0	} else if (propertyValue.startsWith(SearchOperator.GREATER_THAN_EQUAL.op())) {
293	0	criteria.gte(propertyName, stringToBigDecimal(propertyValue), propertyType);
294	0	} else if (propertyValue.startsWith(SearchOperator.LESS_THAN_EQUAL.op())) {
295	0	criteria.lte(propertyName, stringToBigDecimal(propertyValue), propertyType);
296	0	} else if (propertyValue.startsWith(SearchOperator.GREATER_THAN.op())) {
297	0	criteria.gt(propertyName, stringToBigDecimal(propertyValue), propertyType);
298	0	} else if (propertyValue.startsWith(SearchOperator.LESS_THAN.op())) {
299	0	criteria.lt(propertyName, stringToBigDecimal(propertyValue), propertyType);
300		} else {
301	0	criteria.eq(propertyName, stringToBigDecimal(propertyValue), propertyType);
302		}
303	0	}
304
305		private void addStringRangeCriteria(String propertyName, String propertyValue, Criteria criteria, Class propertyType, boolean caseInsensitive, boolean allowWildcards) {
306
307	0	if (StringUtils.contains(propertyValue, SearchOperator.BETWEEN.op())) {
308	0	String[] rangeValues = propertyValue.split("\\.\\."); // this translate to the .. operator
309	0	propertyName = this.getCaseAndLiteralPropertyName(propertyName, caseInsensitive);
310	0	String val1 = this.getCaseAndLiteralPropertyValue(rangeValues[0], caseInsensitive, allowWildcards);
311	0	String val2 = this.getCaseAndLiteralPropertyValue(rangeValues[1], caseInsensitive, allowWildcards);
312	0	criteria.between(propertyName, val1, val2, propertyType);
313	0	} else{
314	0	propertyName = this.getCaseAndLiteralPropertyName(propertyName, caseInsensitive);
315	0	String value = this.getCaseAndLiteralPropertyValue(SQLUtils.cleanString(propertyValue), caseInsensitive, allowWildcards);
316
317	0	if (propertyValue.startsWith(SearchOperator.GREATER_THAN_EQUAL.op())) {
318	0	criteria.gte(propertyName, value, propertyType);
319	0	} else if (propertyValue.startsWith(SearchOperator.LESS_THAN_EQUAL.op())) {
320	0	criteria.lte(propertyName, value, propertyType);
321	0	} else if (propertyValue.startsWith(SearchOperator.GREATER_THAN.op())) {
322	0	criteria.gt(propertyName, value, propertyType);
323	0	} else if (propertyValue.startsWith(SearchOperator.LESS_THAN.op())) {
324	0	criteria.lt(propertyName, value, propertyType);
325		}
326		}
327	0	}
328
329		private String getCaseAndLiteralPropertyName(String propertyName, boolean caseInsensitive){
330		// KULRICE-85 : made string searches case insensitive - used new
331		// DBPlatform function to force strings to upper case
332	0	if (caseInsensitive) {
333		// TODO: What to do here now that the JPA version does not extend platform aware?
334	0	propertyName = getDbPlatform().getUpperCaseFunction() + "(__JPA_ALIAS__." + propertyName + ")";
335
336		}
337	0	return propertyName;
338		}
339		private String getCaseAndLiteralPropertyValue(String propertyValue, boolean caseInsensitive, boolean allowWildcards){
340		//if (!allowWildcards) {
341		// propertyValue = StringUtils.replace(propertyValue, "", "\\");
342		//}
343		// KULRICE-85 : made string searches case insensitive - used new
344		// DBPlatform function to force strings to upper case
345	0	if (caseInsensitive) {
346		//propertyName = "UPPER("+ tableAlias + "." + propertyName + ")";
347	0	propertyValue = propertyValue.toUpperCase();
348		}
349	0	return propertyValue;
350		}
351
352
353		protected DateTimeService getDateTimeService(){
354	0	if (dateTimeService == null) {
355	0	dateTimeService = GlobalResourceLoader.getService(CoreConstants.Services.DATETIME_SERVICE);
356		}
357	0	return dateTimeService;
358		}
359
360		/**
361		* @param dateTimeService
362		* the dateTimeService to set
363		*/
364		public void setDateTimeService(DateTimeService dateTimeService) {
365	0	this.dateTimeService = dateTimeService;
366	0	}
367
368		public DatabasePlatform getDbPlatform() {
369	0	if (dbPlatform == null) {
370	0	dbPlatform = (DatabasePlatform) GlobalResourceLoader.getService(RiceConstants.DB_PLATFORM);
371		}
372	0	return dbPlatform;
373		}
374
375		public void setDbPlatform(DatabasePlatform dbPlatform){
376	0	this.dbPlatform = dbPlatform;
377	0	}
378
379		/**
380		* When dealing with upperbound dates, it is a business requirement that if a timestamp isn't already
381		* stated append 23:59:59 to the end of the date. This ensures that you are searching for the entire
382		* day.
383		*/
384		private String cleanUpperBound(String stringDate){
385		final java.sql.Timestamp dt;
386		try {
387	0	dt = getDateTimeService().convertToSqlTimestamp(stringDate);
388	0	} catch (ParseException e) {
389	0	throw new SQLBuilderException(e);
390	0	}
391	0	SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");
392
393	0	if("00:00:00".equals(sdfTime.format(dt)) && !StringUtils.contains(stringDate, "00:00:00") && !StringUtils.contains(stringDate, "12:00 AM")){
394	0	stringDate = stringDate + " 23:59:59";
395		}
396	0	return stringDate;
397		}
398
399		/**
400		*
401		* This method will take a whole date like 03/02/2009 and convert it into
402		* 03/02/2009 .. 03/02/20009 00:00:00
403		*
404		* This is used for non-range searchable attributes
405		*
406		* @param stringDate
407		* @return
408		*/
409		private String convertSimpleDateToDateRange(String stringDate){
410		final java.sql.Timestamp dt;
411		try {
412	0	dt = getDateTimeService().convertToSqlTimestamp(stringDate);
413	0	} catch (ParseException e) {
414	0	throw new SQLBuilderException(e);
415	0	}
416	0	SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");
417
418	0	if("00:00:00".equals(sdfTime.format(dt)) && !StringUtils.contains(stringDate, "00:00:00") && !StringUtils.contains(stringDate, "12:00 AM")){
419	0	stringDate = stringDate + " .. " + stringDate + " 23:59:59";
420		}
421
422	0	return stringDate;
423		}
424
425	0	public static final class SQLBuilderException extends RiceRuntimeException {
426		public SQLBuilderException(Throwable t) {
427	0	super(t);
428	0	}
429		}
430		}