Coverage Report

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

Classes in this File

Line Coverage

Branch Coverage

Complexity

SqlBuilder

0/178

0/100

3.207

SqlBuilder$JoinType

0/1

N/A

3.207

SqlBuilder$SQLBuilderException

0/2

N/A

3.207

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