Coverage Report

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

Classes in this File

Line Coverage

Branch Coverage

Complexity

SQLUtils

0/170

0/90

4.444

SQLUtils$DateComponent

0/8

N/A

4.444

 /*
  * Copyright 2007 The Kuali Foundation
  *
  * Licensed under the Educational Community License, Version 1.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/ecl1.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.sql.Timestamp;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Calendar;
 import java.util.Collection;
 import java.util.Collections;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 
 import org.apache.commons.lang.StringUtils;
 import org.kuali.rice.core.framework.logic.LogicalOperator;
 import org.kuali.rice.kns.util.KNSConstants;
 
 
 /**
  * Utility class for working with SQL.
  * 
  * @author Kuali Rice Team (rice.collab@kuali.org)
  */
 public final class SQLUtils {
         
         private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(SQLUtils.class);
 
         private static final String DATE_REGEX_SMALL_TWO_DIGIT_YEAR = "^\\d{1,2}/\\d{1,2}/\\d{2}$|^\\d{1,2}-\\d{1,2}-\\d{2}$"; // matches M/d/yy or MM/dd/yy or M-d-yy or MM-dd-yy
         private static final String DATE_REGEX_SMALL_TWO_DIGIT_YEAR_SPLIT = "(\\d{1,2})[/,-](\\d{1,2})[/,-](\\d{2})";
         private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR = "^\\d{1,2}/\\d{1,2}/\\d{4}$|^\\d{1,2}-\\d{1,2}-\\d{4}$"; // matches M/d/yyyy or MM/dd/yyyy or M-d-yyyy or MM-dd-yyyy
         private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_SPLIT = "(\\d{1,2})[/,-](\\d{1,2})[/,-](\\d{4})";
         
         private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST = "^\\d{4}/\\d{1,2}/\\d{1,2}$|^\\d{4}-\\d{1,2}-\\d{1,2}$"; // matches yyyy/M/d or yyyy/MM/dd or yyyy-M-d or yyyy-MM-dd
         private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST_SPLIT = "(\\d{4})[/,-](\\d{1,2})[/,-](\\d{1,2})";
         
         private static final String DATE_REGEX_WHOLENUM_SMALL = "^\\d{6}$"; // matches MMddyy
         private static final String DATE_REGEX_WHOLENUM_SMALL_SPLIT = "(\\d{2})(\\d{2})(\\d{2})";
         private static final String DATE_REGEX_WHOLENUM_LARGE = "^\\d{8}$"; // matches MMddyyyy
         private static final String DATE_REGEX_WHOLENUM_LARGE_SPLIT = "(\\d{2})(\\d{2})(\\d{4})";
 
         private static final String TIME_REGEX = "([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])";
         
         
         private static final Collection<String> DOCUMENT_SEARCH_DATE_VALIDATION_REGEX_EXPRESSIONS = Collections.unmodifiableCollection(Arrays.asList(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR, DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST));
         
         
         private static final Map<String, String> REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION;
         static {
                 final Map<String, String> map = new HashMap<String, String>();
                 
                 map.put(DATE_REGEX_SMALL_TWO_DIGIT_YEAR, DATE_REGEX_SMALL_TWO_DIGIT_YEAR_SPLIT);
                 map.put(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR, DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_SPLIT);
                 map.put(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST, DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST_SPLIT);
                 map.put(DATE_REGEX_WHOLENUM_SMALL, DATE_REGEX_WHOLENUM_SMALL_SPLIT);
                 map.put(DATE_REGEX_WHOLENUM_LARGE,DATE_REGEX_WHOLENUM_LARGE_SPLIT);
                 REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION = Collections.unmodifiableMap(map);
         }
         
         private SQLUtils() {
                 throw new UnsupportedOperationException("do not call");
         }
         
     /**
      * A method to format any variety of date strings into a common format
      *
      * @param date
      *            A string date in one of a few different formats
      * @return A string representing a date in the format yyyy/MM/dd or null if date is invalid
      */
     public static String getSqlFormattedDate(String date) {
         DateComponent dc = formatDateToDateComponent(date, REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.keySet());
         if (dc == null) {
             return null;
         }
         return dc.getYear() + "/" + dc.getMonth() + "/" + dc.getDate();
     }
     
     public static Timestamp convertStringDateToTimestamp(String dateWithoutTime) {
         Pattern p = Pattern.compile(TIME_REGEX);
         Matcher util = p.matcher(dateWithoutTime);
         if (util.find()) {
             dateWithoutTime = StringUtils.substringBeforeLast(dateWithoutTime, " ");
         }
         DateComponent formattedDate = formatDateToDateComponent(dateWithoutTime, REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.keySet());
         if (formattedDate == null) {
             return null;
         }
         Calendar c = Calendar.getInstance();
         c.clear();
         c.set(Calendar.MONTH, Integer.valueOf(formattedDate.getMonth()).intValue() - 1);
         c.set(Calendar.DATE, Integer.valueOf(formattedDate.getDate()).intValue());
         c.set(Calendar.YEAR, Integer.valueOf(formattedDate.getYear()).intValue());
         return convertCalendar(c);
     }
 
     private static class DateComponent {
         protected String month;
         protected String date;
         protected String year;
 
         public DateComponent(String year, String month, String date) {
             this.month = month;
             this.date = date;
             this.year = year;
         }
 
         public String getDate() {
             return date;
         }
 
         public String getMonth() {
             return month;
         }
 
         public String getYear() {
             return year;
         }
     }
     
     /**
      * A method to format any variety of date strings into a common format
      *
      * @param date
      *            A string date in one of a few different formats
      * @return A string representing a date in the format MM/dd/yyyy or null if date is invalid
      */
     public static String getEntryFormattedDate(String date) {
         Pattern p = Pattern.compile(TIME_REGEX);
         Matcher util = p.matcher(date);
         if (util.find()) {
             date = StringUtils.substringBeforeLast(date, " ");
         }
         DateComponent dc = formatDateToDateComponent(date, DOCUMENT_SEARCH_DATE_VALIDATION_REGEX_EXPRESSIONS);
         if (dc == null) {
             return null;
         }
         return dc.getMonth() + "/" + dc.getDate() + "/" + dc.getYear();
     }
 
     private static DateComponent formatDateToDateComponent(String date, Collection<String> regularExpressionList) {
         String matchingRegexExpression = null;
         for (String string : regularExpressionList) {
             String matchRegex = string;
             if (!REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.containsKey(matchRegex)) {
                 String errorMsg = "";
                 LOG.error("formatDateToDateComponent(String,List) " + errorMsg);
 
             }
             Pattern p = Pattern.compile(matchRegex);
             if ((p.matcher(date)).matches()) {
                 matchingRegexExpression = matchRegex;
                 break;
             }
         }
 
         if (matchingRegexExpression == null) {
             String errorMsg = "formatDate(String,List) Date string given '" + date + "' is not valid according to Workflow defaults.  Returning null value.";
             if (StringUtils.isNotBlank(date)) {
                 LOG.warn(errorMsg);
             } else {
                 LOG.debug(errorMsg);
             }
             return null;
         }
         String regexSplitExpression = REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.get(matchingRegexExpression);
 
         // Check date formats and reformat to yyyy/MM/dd
         // well formed MM/dd/yyyy
         Pattern p = Pattern.compile(regexSplitExpression);
         Matcher util = p.matcher(date);
         util.matches();
         if (regexSplitExpression.equals(DATE_REGEX_SMALL_TWO_DIGIT_YEAR_SPLIT)) {
             StringBuffer yearBuf = new StringBuffer();
             StringBuffer monthBuf = new StringBuffer();
             StringBuffer dateBuf = new StringBuffer();
             Integer year = Integer.valueOf(util.group(3));
 
             if (year.intValue() <= 50) {
                 yearBuf.append("20").append(util.group(3));
             } else if (util.group(3).length() < 3) {
                 yearBuf.append("19").append(util.group(3));
             } else {
                 yearBuf.append(util.group(3));
             }
 
             if (util.group(1).length() < 2) {
                 monthBuf.append("0").append(util.group(1));
             } else {
                 monthBuf.append(util.group(1));
             }
 
             if (util.group(2).length() < 2) {
                 dateBuf.append("0").append(util.group(2));
             } else {
                 dateBuf.append(util.group(2));
             }
 
             return new DateComponent(yearBuf.toString(), monthBuf.toString(), dateBuf.toString());
 
             // small date format M/d/yyyy | MM/dd/yyyy | M-d-yyyy | MM-dd-yyyy
         } else if (regexSplitExpression.equals(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_SPLIT)) {
             StringBuffer yearBuf = new StringBuffer(util.group(3));
             StringBuffer monthBuf = new StringBuffer();
             StringBuffer dateBuf = new StringBuffer();
 
             if (util.group(1).length() < 2) {
                 monthBuf.append("0").append(util.group(1));
             } else {
                 monthBuf.append(util.group(1));
             }
 
             if (util.group(2).length() < 2) {
                 dateBuf.append("0").append(util.group(2));
             } else {
                 dateBuf.append(util.group(2));
             }
 
             return new DateComponent(yearBuf.toString(), monthBuf.toString(), dateBuf.toString());
 
             // small date format yyyy/M/d | yyyy/MM/dd | yyyy-M-d | yyyy-MM-dd
         } else if (regexSplitExpression.equals(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST_SPLIT)) {
             StringBuffer yearBuf = new StringBuffer(util.group(1));
             StringBuffer monthBuf = new StringBuffer();
             StringBuffer dateBuf = new StringBuffer();
 
             if (util.group(2).length() < 2) {
                 monthBuf.append("0").append(util.group(2));
             } else {
                 monthBuf.append(util.group(2));
             }
 
             if (util.group(3).length() < 2) {
                 dateBuf.append("0").append(util.group(3));
             } else {
                 dateBuf.append(util.group(3));
             }
 
             return new DateComponent(yearBuf.toString(), monthBuf.toString(), dateBuf.toString());
 
             // large number MMddyyyy
         } else if (regexSplitExpression.equals(DATE_REGEX_WHOLENUM_LARGE_SPLIT)) {
             return new DateComponent(util.group(3), util.group(1), util.group(2));
 
             // small number MMddyy
         } else if (regexSplitExpression.equals(DATE_REGEX_WHOLENUM_SMALL_SPLIT)) {
             StringBuffer yearBuf = new StringBuffer();
             Integer year = Integer.valueOf(util.group(3));
 
             if (year.intValue() < 50) {
                 yearBuf.append("20");
             } else {
                 yearBuf.append("19");
             }
             yearBuf.append(util.group(3));
             return new DateComponent(yearBuf.toString(), util.group(1), util.group(2));
         } else {
             LOG.warn("formatDate(String,List) Date string given '" + date + "' is not valid according to Workflow defaults.  Returning null value.");
             return null;
         }
     }
     
     public static Calendar convertTimestamp(Timestamp timestamp) {
         if (timestamp == null) {
             return null;
         }
         Calendar calendar = Calendar.getInstance();
         calendar.setTime(timestamp);
         return calendar;
     }
 
     public static Timestamp convertCalendar(Calendar calendar) {
         if (calendar == null) {
             return null;
         }
         return new Timestamp(calendar.getTimeInMillis());
     }
    
         public static String cleanDate(String string) {                
         for (LogicalOperator op : LogicalOperator.RANGE_CHARACTERS) {
             string = StringUtils.replace(string, op.op(), "");
         }
         return string;
     }
    
         public static String cleanNumericOfValidOperators(String string){
                 for (LogicalOperator op : LogicalOperator.RANGE_CHARACTERS) {
             string = StringUtils.replace(string, op.op(), "");
         }
                 string = StringUtils.replace(string, LogicalOperator.OR.op(), "");
                 string = StringUtils.replace(string, LogicalOperator.AND.op(), "");
                 string = StringUtils.replace(string, LogicalOperator.NOT.op(), "");
 
                 return string;
         }
         
     /**
      * Removes all query characters from a string.
      *
      * @param string
      * @return Cleaned string
      */
     public static String cleanString(String string) {
         for (LogicalOperator op : LogicalOperator.QUERY_CHARACTERS) {
             string = StringUtils.replace(string, op.op(), "");
         }
         return string;
     }
     
     /**
      * Splits the values then cleans them of any other query characters like *?!><...
      *
      * @param valueEntered
      * @param propertyDataType
      * @return
      */
     public static List<String> getCleanedSearchableValues(String valueEntered, String propertyDataType) {
              List<String> lRet = null;
              List<String> lTemp = getSearchableValues(valueEntered);
              if(lTemp != null && !lTemp.isEmpty()){
                      lRet = new ArrayList<String>();
                      for(String val: lTemp){
                              // Clean the wildcards appropriately, depending on the field's data type.
                              if (KNSConstants.DATA_TYPE_STRING.equals(propertyDataType)) {
                                      lRet.add(clean(val));
                              } else if (KNSConstants.DATA_TYPE_FLOAT.equals(propertyDataType) || KNSConstants.DATA_TYPE_LONG.equals(propertyDataType)) {
                                      lRet.add(SQLUtils.cleanNumericOfValidOperators(val));
                              } else if (KNSConstants.DATA_TYPE_DATE.equals(propertyDataType)) {
                                      lRet.add(SQLUtils.cleanDate(val));
                              } else {
                                      lRet.add(clean(val));
                              }
                      }
              }
              return lRet;
     }
     
     /**
     * Splits the valueEntered on locical operators and, or, and between
     *
     * @param valueEntered
     * @return
     */
          private static List<String> getSearchableValues(String valueEntered) {
                  List<String> lRet = new ArrayList<String>();
                  getSearchableValueRecursive(valueEntered, lRet);
                  return lRet;
          }
 
          private static void getSearchableValueRecursive(String valueEntered, List lRet) {
                  if(valueEntered == null) {
                          return;
                  }
 
                  valueEntered = valueEntered.trim();
 
                  if(lRet == null){
                          throw new NullPointerException("The list passed in is by reference and should never be null.");
                  }
 
                  if (StringUtils.contains(valueEntered, LogicalOperator.BETWEEN.op())) {
                          List<String> l = Arrays.asList(valueEntered.split("\\.\\."));
                          for(String value : l){
                                  getSearchableValueRecursive(value,lRet);
                          }
                          return;
                  }
                  if (StringUtils.contains(valueEntered, LogicalOperator.OR.op())) {
                          List<String> l = Arrays.asList(StringUtils.split(valueEntered, LogicalOperator.OR.op()));
                          for(String value : l){
                                  getSearchableValueRecursive(value,lRet);
                          }
                          return;
                  }
                  if (StringUtils.contains(valueEntered, LogicalOperator.AND.op())) {
                          //splitValueList.addAll(Arrays.asList(StringUtils.split(valueEntered, KNSConstants.AND.op())));
                          List<String> l = Arrays.asList(StringUtils.split(valueEntered, LogicalOperator.AND.op()));
                          for(String value : l){
                                  getSearchableValueRecursive(value,lRet);
                          }
                          return;
                  }
 
                  // lRet is pass by ref and should NEVER be null
                  lRet.add(valueEntered);
    }
          
     /**
      * Removes all query characters from a string.
      *
      * @param string
      * @return Cleaned string
      */
     private static String clean(String string) {
         for (int i = 0; i < KNSConstants.QUERY_CHARACTERS.length; i++) {
             string = StringUtils.replace(string, KNSConstants.QUERY_CHARACTERS[i], KNSConstants.EMPTY_STRING);
         }
         return string;
     }
 }

1		/*
2		* Copyright 2007 The Kuali Foundation
3		*
4		* Licensed under the Educational Community License, Version 1.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/ecl1.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.sql.Timestamp;
19		import java.util.ArrayList;
20		import java.util.Arrays;
21		import java.util.Calendar;
22		import java.util.Collection;
23		import java.util.Collections;
24		import java.util.HashMap;
25		import java.util.List;
26		import java.util.Map;
27		import java.util.regex.Matcher;
28		import java.util.regex.Pattern;
29
30		import org.apache.commons.lang.StringUtils;
31		import org.kuali.rice.core.framework.logic.LogicalOperator;
32		import org.kuali.rice.kns.util.KNSConstants;
33
34
35		/**
36		* Utility class for working with SQL.
37		*
38		* @author Kuali Rice Team (rice.collab@kuali.org)
39		*/
40		public final class SQLUtils {
41
42	0	private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(SQLUtils.class);
43
44		private static final String DATE_REGEX_SMALL_TWO_DIGIT_YEAR = "^\\d{1,2}/\\d{1,2}/\\d{2}$\|^\\d{1,2}-\\d{1,2}-\\d{2}$"; // matches M/d/yy or MM/dd/yy or M-d-yy or MM-dd-yy
45		private static final String DATE_REGEX_SMALL_TWO_DIGIT_YEAR_SPLIT = "(\\d{1,2})[/,-](\\d{1,2})[/,-](\\d{2})";
46		private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR = "^\\d{1,2}/\\d{1,2}/\\d{4}$\|^\\d{1,2}-\\d{1,2}-\\d{4}$"; // matches M/d/yyyy or MM/dd/yyyy or M-d-yyyy or MM-dd-yyyy
47		private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_SPLIT = "(\\d{1,2})[/,-](\\d{1,2})[/,-](\\d{4})";
48
49		private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST = "^\\d{4}/\\d{1,2}/\\d{1,2}$\|^\\d{4}-\\d{1,2}-\\d{1,2}$"; // matches yyyy/M/d or yyyy/MM/dd or yyyy-M-d or yyyy-MM-dd
50		private static final String DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST_SPLIT = "(\\d{4})[/,-](\\d{1,2})[/,-](\\d{1,2})";
51
52		private static final String DATE_REGEX_WHOLENUM_SMALL = "^\\d{6}$"; // matches MMddyy
53		private static final String DATE_REGEX_WHOLENUM_SMALL_SPLIT = "(\\d{2})(\\d{2})(\\d{2})";
54		private static final String DATE_REGEX_WHOLENUM_LARGE = "^\\d{8}$"; // matches MMddyyyy
55		private static final String DATE_REGEX_WHOLENUM_LARGE_SPLIT = "(\\d{2})(\\d{2})(\\d{4})";
56
57		private static final String TIME_REGEX = "([0-1][0-9]\|2[0-3]):([0-5][0-9]):([0-5][0-9])";
58
59
60	0	private static final Collection<String> DOCUMENT_SEARCH_DATE_VALIDATION_REGEX_EXPRESSIONS = Collections.unmodifiableCollection(Arrays.asList(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR, DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST));
61
62
63		private static final Map<String, String> REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION;
64		static {
65	0	final Map<String, String> map = new HashMap<String, String>();
66
67	0	map.put(DATE_REGEX_SMALL_TWO_DIGIT_YEAR, DATE_REGEX_SMALL_TWO_DIGIT_YEAR_SPLIT);
68	0	map.put(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR, DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_SPLIT);
69	0	map.put(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST, DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST_SPLIT);
70	0	map.put(DATE_REGEX_WHOLENUM_SMALL, DATE_REGEX_WHOLENUM_SMALL_SPLIT);
71	0	map.put(DATE_REGEX_WHOLENUM_LARGE,DATE_REGEX_WHOLENUM_LARGE_SPLIT);
72	0	REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION = Collections.unmodifiableMap(map);
73	0	}
74
75	0	private SQLUtils() {
76	0	throw new UnsupportedOperationException("do not call");
77		}
78
79		/**
80		* A method to format any variety of date strings into a common format
81		*
82		* @param date
83		* A string date in one of a few different formats
84		* @return A string representing a date in the format yyyy/MM/dd or null if date is invalid
85		*/
86		public static String getSqlFormattedDate(String date) {
87	0	DateComponent dc = formatDateToDateComponent(date, REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.keySet());
88	0	if (dc == null) {
89	0	return null;
90		}
91	0	return dc.getYear() + "/" + dc.getMonth() + "/" + dc.getDate();
92		}
93
94		public static Timestamp convertStringDateToTimestamp(String dateWithoutTime) {
95	0	Pattern p = Pattern.compile(TIME_REGEX);
96	0	Matcher util = p.matcher(dateWithoutTime);
97	0	if (util.find()) {
98	0	dateWithoutTime = StringUtils.substringBeforeLast(dateWithoutTime, " ");
99		}
100	0	DateComponent formattedDate = formatDateToDateComponent(dateWithoutTime, REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.keySet());
101	0	if (formattedDate == null) {
102	0	return null;
103		}
104	0	Calendar c = Calendar.getInstance();
105	0	c.clear();
106	0	c.set(Calendar.MONTH, Integer.valueOf(formattedDate.getMonth()).intValue() - 1);
107	0	c.set(Calendar.DATE, Integer.valueOf(formattedDate.getDate()).intValue());
108	0	c.set(Calendar.YEAR, Integer.valueOf(formattedDate.getYear()).intValue());
109	0	return convertCalendar(c);
110		}
111
112		private static class DateComponent {
113		protected String month;
114		protected String date;
115		protected String year;
116
117	0	public DateComponent(String year, String month, String date) {
118	0	this.month = month;
119	0	this.date = date;
120	0	this.year = year;
121	0	}
122
123		public String getDate() {
124	0	return date;
125		}
126
127		public String getMonth() {
128	0	return month;
129		}
130
131		public String getYear() {
132	0	return year;
133		}
134		}
135
136		/**
137		* A method to format any variety of date strings into a common format
138		*
139		* @param date
140		* A string date in one of a few different formats
141		* @return A string representing a date in the format MM/dd/yyyy or null if date is invalid
142		*/
143		public static String getEntryFormattedDate(String date) {
144	0	Pattern p = Pattern.compile(TIME_REGEX);
145	0	Matcher util = p.matcher(date);
146	0	if (util.find()) {
147	0	date = StringUtils.substringBeforeLast(date, " ");
148		}
149	0	DateComponent dc = formatDateToDateComponent(date, DOCUMENT_SEARCH_DATE_VALIDATION_REGEX_EXPRESSIONS);
150	0	if (dc == null) {
151	0	return null;
152		}
153	0	return dc.getMonth() + "/" + dc.getDate() + "/" + dc.getYear();
154		}
155
156		private static DateComponent formatDateToDateComponent(String date, Collection<String> regularExpressionList) {
157	0	String matchingRegexExpression = null;
158	0	for (String string : regularExpressionList) {
159	0	String matchRegex = string;
160	0	if (!REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.containsKey(matchRegex)) {
161	0	String errorMsg = "";
162	0	LOG.error("formatDateToDateComponent(String,List) " + errorMsg);
163
164		}
165	0	Pattern p = Pattern.compile(matchRegex);
166	0	if ((p.matcher(date)).matches()) {
167	0	matchingRegexExpression = matchRegex;
168	0	break;
169		}
170	0	}
171
172	0	if (matchingRegexExpression == null) {
173	0	String errorMsg = "formatDate(String,List) Date string given '" + date + "' is not valid according to Workflow defaults. Returning null value.";
174	0	if (StringUtils.isNotBlank(date)) {
175	0	LOG.warn(errorMsg);
176		} else {
177	0	LOG.debug(errorMsg);
178		}
179	0	return null;
180		}
181	0	String regexSplitExpression = REGEX_EXPRESSION_MAP_TO_REGEX_SPLIT_EXPRESSION.get(matchingRegexExpression);
182
183		// Check date formats and reformat to yyyy/MM/dd
184		// well formed MM/dd/yyyy
185	0	Pattern p = Pattern.compile(regexSplitExpression);
186	0	Matcher util = p.matcher(date);
187	0	util.matches();
188	0	if (regexSplitExpression.equals(DATE_REGEX_SMALL_TWO_DIGIT_YEAR_SPLIT)) {
189	0	StringBuffer yearBuf = new StringBuffer();
190	0	StringBuffer monthBuf = new StringBuffer();
191	0	StringBuffer dateBuf = new StringBuffer();
192	0	Integer year = Integer.valueOf(util.group(3));
193
194	0	if (year.intValue() <= 50) {
195	0	yearBuf.append("20").append(util.group(3));
196	0	} else if (util.group(3).length() < 3) {
197	0	yearBuf.append("19").append(util.group(3));
198		} else {
199	0	yearBuf.append(util.group(3));
200		}
201
202	0	if (util.group(1).length() < 2) {
203	0	monthBuf.append("0").append(util.group(1));
204		} else {
205	0	monthBuf.append(util.group(1));
206		}
207
208	0	if (util.group(2).length() < 2) {
209	0	dateBuf.append("0").append(util.group(2));
210		} else {
211	0	dateBuf.append(util.group(2));
212		}
213
214	0	return new DateComponent(yearBuf.toString(), monthBuf.toString(), dateBuf.toString());
215
216		// small date format M/d/yyyy \| MM/dd/yyyy \| M-d-yyyy \| MM-dd-yyyy
217	0	} else if (regexSplitExpression.equals(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_SPLIT)) {
218	0	StringBuffer yearBuf = new StringBuffer(util.group(3));
219	0	StringBuffer monthBuf = new StringBuffer();
220	0	StringBuffer dateBuf = new StringBuffer();
221
222	0	if (util.group(1).length() < 2) {
223	0	monthBuf.append("0").append(util.group(1));
224		} else {
225	0	monthBuf.append(util.group(1));
226		}
227
228	0	if (util.group(2).length() < 2) {
229	0	dateBuf.append("0").append(util.group(2));
230		} else {
231	0	dateBuf.append(util.group(2));
232		}
233
234	0	return new DateComponent(yearBuf.toString(), monthBuf.toString(), dateBuf.toString());
235
236		// small date format yyyy/M/d \| yyyy/MM/dd \| yyyy-M-d \| yyyy-MM-dd
237	0	} else if (regexSplitExpression.equals(DATE_REGEX_SMALL_FOUR_DIGIT_YEAR_FIRST_SPLIT)) {
238	0	StringBuffer yearBuf = new StringBuffer(util.group(1));
239	0	StringBuffer monthBuf = new StringBuffer();
240	0	StringBuffer dateBuf = new StringBuffer();
241
242	0	if (util.group(2).length() < 2) {
243	0	monthBuf.append("0").append(util.group(2));
244		} else {
245	0	monthBuf.append(util.group(2));
246		}
247
248	0	if (util.group(3).length() < 2) {
249	0	dateBuf.append("0").append(util.group(3));
250		} else {
251	0	dateBuf.append(util.group(3));
252		}
253
254	0	return new DateComponent(yearBuf.toString(), monthBuf.toString(), dateBuf.toString());
255
256		// large number MMddyyyy
257	0	} else if (regexSplitExpression.equals(DATE_REGEX_WHOLENUM_LARGE_SPLIT)) {
258	0	return new DateComponent(util.group(3), util.group(1), util.group(2));
259
260		// small number MMddyy
261	0	} else if (regexSplitExpression.equals(DATE_REGEX_WHOLENUM_SMALL_SPLIT)) {
262	0	StringBuffer yearBuf = new StringBuffer();
263	0	Integer year = Integer.valueOf(util.group(3));
264
265	0	if (year.intValue() < 50) {
266	0	yearBuf.append("20");
267		} else {
268	0	yearBuf.append("19");
269		}
270	0	yearBuf.append(util.group(3));
271	0	return new DateComponent(yearBuf.toString(), util.group(1), util.group(2));
272		} else {
273	0	LOG.warn("formatDate(String,List) Date string given '" + date + "' is not valid according to Workflow defaults. Returning null value.");
274	0	return null;
275		}
276		}
277
278		public static Calendar convertTimestamp(Timestamp timestamp) {
279	0	if (timestamp == null) {
280	0	return null;
281		}
282	0	Calendar calendar = Calendar.getInstance();
283	0	calendar.setTime(timestamp);
284	0	return calendar;
285		}
286
287		public static Timestamp convertCalendar(Calendar calendar) {
288	0	if (calendar == null) {
289	0	return null;
290		}
291	0	return new Timestamp(calendar.getTimeInMillis());
292		}
293
294		public static String cleanDate(String string) {
295	0	for (LogicalOperator op : LogicalOperator.RANGE_CHARACTERS) {
296	0	string = StringUtils.replace(string, op.op(), "");
297		}
298	0	return string;
299		}
300
301		public static String cleanNumericOfValidOperators(String string){
302	0	for (LogicalOperator op : LogicalOperator.RANGE_CHARACTERS) {
303	0	string = StringUtils.replace(string, op.op(), "");
304		}
305	0	string = StringUtils.replace(string, LogicalOperator.OR.op(), "");
306	0	string = StringUtils.replace(string, LogicalOperator.AND.op(), "");
307	0	string = StringUtils.replace(string, LogicalOperator.NOT.op(), "");
308
309	0	return string;
310		}
311
312		/**
313		* Removes all query characters from a string.
314		*
315		* @param string
316		* @return Cleaned string
317		*/
318		public static String cleanString(String string) {
319	0	for (LogicalOperator op : LogicalOperator.QUERY_CHARACTERS) {
320	0	string = StringUtils.replace(string, op.op(), "");
321		}
322	0	return string;
323		}
324
325		/**
326		* Splits the values then cleans them of any other query characters like *?!><...
327		*
328		* @param valueEntered
329		* @param propertyDataType
330		* @return
331		*/
332		public static List<String> getCleanedSearchableValues(String valueEntered, String propertyDataType) {
333	0	List<String> lRet = null;
334	0	List<String> lTemp = getSearchableValues(valueEntered);
335	0	if(lTemp != null && !lTemp.isEmpty()){
336	0	lRet = new ArrayList<String>();
337	0	for(String val: lTemp){
338		// Clean the wildcards appropriately, depending on the field's data type.
339	0	if (KNSConstants.DATA_TYPE_STRING.equals(propertyDataType)) {
340	0	lRet.add(clean(val));
341	0	} else if (KNSConstants.DATA_TYPE_FLOAT.equals(propertyDataType) \|\| KNSConstants.DATA_TYPE_LONG.equals(propertyDataType)) {
342	0	lRet.add(SQLUtils.cleanNumericOfValidOperators(val));
343	0	} else if (KNSConstants.DATA_TYPE_DATE.equals(propertyDataType)) {
344	0	lRet.add(SQLUtils.cleanDate(val));
345		} else {
346	0	lRet.add(clean(val));
347		}
348		}
349		}
350	0	return lRet;
351		}
352
353		/**
354		* Splits the valueEntered on locical operators and, or, and between
355		*
356		* @param valueEntered
357		* @return
358		*/
359		private static List<String> getSearchableValues(String valueEntered) {
360	0	List<String> lRet = new ArrayList<String>();
361	0	getSearchableValueRecursive(valueEntered, lRet);
362	0	return lRet;
363		}
364
365		private static void getSearchableValueRecursive(String valueEntered, List lRet) {
366	0	if(valueEntered == null) {
367	0	return;
368		}
369
370	0	valueEntered = valueEntered.trim();
371
372	0	if(lRet == null){
373	0	throw new NullPointerException("The list passed in is by reference and should never be null.");
374		}
375
376	0	if (StringUtils.contains(valueEntered, LogicalOperator.BETWEEN.op())) {
377	0	List<String> l = Arrays.asList(valueEntered.split("\\.\\."));
378	0	for(String value : l){
379	0	getSearchableValueRecursive(value,lRet);
380		}
381	0	return;
382		}
383	0	if (StringUtils.contains(valueEntered, LogicalOperator.OR.op())) {
384	0	List<String> l = Arrays.asList(StringUtils.split(valueEntered, LogicalOperator.OR.op()));
385	0	for(String value : l){
386	0	getSearchableValueRecursive(value,lRet);
387		}
388	0	return;
389		}
390	0	if (StringUtils.contains(valueEntered, LogicalOperator.AND.op())) {
391		//splitValueList.addAll(Arrays.asList(StringUtils.split(valueEntered, KNSConstants.AND.op())));
392	0	List<String> l = Arrays.asList(StringUtils.split(valueEntered, LogicalOperator.AND.op()));
393	0	for(String value : l){
394	0	getSearchableValueRecursive(value,lRet);
395		}
396	0	return;
397		}
398
399		// lRet is pass by ref and should NEVER be null
400	0	lRet.add(valueEntered);
401	0	}
402
403		/**
404		* Removes all query characters from a string.
405		*
406		* @param string
407		* @return Cleaned string
408		*/
409		private static String clean(String string) {
410	0	for (int i = 0; i < KNSConstants.QUERY_CHARACTERS.length; i++) {
411	0	string = StringUtils.replace(string, KNSConstants.QUERY_CHARACTERS[i], KNSConstants.EMPTY_STRING);
412		}
413	0	return string;
414		}
415		}