Coverage Report

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

Classes in this File

Line Coverage

Branch Coverage

Complexity

SqlBuilder

0/177

0/100

3.286

SqlBuilder$SQLBuilderException

0/2

N/A

3.286

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