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