/* ===================================================================
    * Copyright 2002-06 SRI International.
    * Released under the MOZILLA PUBLIC LICENSE Version 1.1
    * which can be obtained at http://www.mozilla.org/MPL/MPL-1.1.html
    * This software is distributed on an "AS IS"
    * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.
    * See the License for the specific language governing rights and
    * limitations under the License.
    * =================================================================== */
  
  package com.sri.common.util;
  
  import com.jcorporate.expresso.core.controller.ControllerException;
  import com.jcorporate.expresso.core.db.DBException;
  import com.jcorporate.expresso.core.misc.ConfigManager;
  import com.jcorporate.expresso.core.misc.ConfigurationException;
  import com.jcorporate.expresso.core.misc.StringUtil;
  
  /***
   * <p>Title: QueryUtils </p>
   * <p>Description: Utilities for formatting keywords and where clauses for queries </p>
   * <p>Note:  DBObj.buildWhereClause() basically works for building where clauses,
   * so why do I write similar methods here? There are some issues with buildWhereClause
   * and package protection for it (and various) methods, so I had to do my own methods
   * here. Hopefully a later version of Expresso will make more methods public
   * instead of protected. Here are some of the issues with using
   * DBObject.buildWhereClause() in the version of Expresso we are currently using:</p>
   * <ol>
   * <li>) it only allows AND as a conjunction; no option to do OR,
   * which I need for keyword matches
   * <li> it automatically prepends WHERE to the front (WHERE is added by setWhereClause)
   * so we'd have to manually strip it
   * <li> it's package protected instead of public so all DBObjects you want
   * to call buildWhereClause on will have to implement a new public method that
   * ust calls buildWhereClause
   * </ol>
   * <p> So, I wrote my own below. In some of the methods below I would have liked to
   * pass the DBObject and just look for what fields were set (like buildWhereClause does)
   * but I can't even access getAllFieldsIterator() here (it is package protected
   * also) so I can't loop through the fields in the object to see which ones are set.
   * Aaag! So I just construct and pass arrays with the relevant fields instead.<p>
   *
   * @author Patti Schank and Zaz Harris
   */
  
  public class QueryUtils {
      // characters that could mess up database queries
      public static final String[] STRIP_CHARS = {"\n", "\r"};
  
      // like wildcards that should be escaped
      public static final String[] LIKE_WILDCARDS = {"%", "_"};
  
      // special chars for db
      public static final String ESCAPE_CHAR = "//";
      public static final String ENCLOSE_CHAR = "'";
  
  //    private static Category sLog = null;
  
      // "unknown" value constant
      public static String UNKNOWN_VALUE = "(unknown)";
  
      // descending sort
      public static String DESC_SORT = " DESC";
  
      /***
       * Create logger as necessary.
       *
       * @return Logger
       */
  //    private static Category getLogger() {
  //        if (sLog == null) {
  //            setupLog();
  //        }
  //        return sLog;
  //    }
  
      /***
       * Set up log.
       */
  //    private static synchronized void setupLog() {
  //        if (sLog == null) {
  //
  //            sLog = Category.getInstance(QueryUtils.class.getName());
  //        }
  //    }
  
      /***
       * Prepares a value to be in a like clause by escaping the enclosing
       * character and any wildcards and stripping any characters db can't handle.
       * Returns null if value is null.
       *
       * @param value The value to be modified
       * @return The modified value ready to be used in a like clause
       */
      public static String prepLikeValue(String value) {
          if (value == null) return null;
          value = stripChars(value);
          value = escapeChar(value, ENCLOSE_CHAR);
          for (int i = 0; i < LIKE_WILDCARDS.length; i++) {
             value = escapeChar(value, LIKE_WILDCARDS[i]);
         }
         return value;
     }
 
     /***
      * Prepares a value to be in a equals clause by escaping the enclosing
      * character and stripping any characters db can't handle.
      * Returns null if value is null.
      *
      * @param value The value to be modified
      * @return The modified value ready to be used in an equals clause
      */
     public static String prepEqualsValue(String value) {
         if (value == null) return null;
         value = stripChars(value);
         value = escapeChar(value, ENCLOSE_CHAR);
         return value;
     }
 
     /***
      * Removes undesirable characters from a string.  Returns null if value is
      * null.
      *
      * @param value The string value passed in
      * @return The stripped string
      */
     public static String stripChars(String value) {
         if (value == null) return null;
         for (int i = 0; i < STRIP_CHARS.length; i++) {
             int stripIndex = value.indexOf(STRIP_CHARS[i]);
             while (stripIndex != -1) {
                 if (stripIndex == value.length() - 1) {
                     value = value.substring(value.length() - 2);
                     break;
                 }
 
                 value = value.substring(0, stripIndex) + value.substring(stripIndex + 1);
                 stripIndex = value.indexOf(STRIP_CHARS[i]);
             }
         }
         return value;
     }
 
     /***
      * Precedes each instance of a character passed in with the escape character
      * in the string passed in.
      *
      * @param value     The string to modify
      * @param charToEsc The character precede with the escape character
      * @return The modified string
      */
     public static String escapeChar(String value, String charToEsc) {
         if (value == null) return null;
         return StringUtil.replace(value, charToEsc, ESCAPE_CHAR + charToEsc);
     }
 
 
     /***
      * Generate a subclause that determines if the values exist for the given fields
      * of an object. Uses AND and is case sensitive. Could use DBObj.buildWhereClause
      * but that only works within the expresso package and doesn't allow option of OR
      * conjunction! Can't use DBObj methods because they are protected (duh, why?)
      * Use this if you are constructing a custom clause clause for other
      * purposes and need to do an exact match on fields also.
      * Returns a clause of the form
      * <p/>
      * DbObj.FIELDNAME1 = '<value1>'
      * AND    DbObj.FIELDNAME2 = '<value2>'
      * AND ... (repeat for field and value 2...n)
      *
      * @param fields the array of fields
      * @param values the array of values
      * @return a String with the clause
      */
     public static String fieldEqualsValueClause(String[] fields,
                                                 String[] values) {
         return fieldEqualsValueClause(fields, values, false, true, false);
     }
 
     /***
      * Generate a subclause that determines if the values exist for the given fields
      * of an object. Uses AND by default, unless the parameters specify to use OR.
      * Use this if you are constructing a custom clause clause for other
      * purposes and need to do an exact match on fields also.
      * Returns a clause of the form
      * <p/>
      * DbObj.FIELDNAME1 = '<value1>'
      * <OR/AND>   DbObj.FIELDNAME2 = '<value2>'
      * <OR/AND> .... (repeat for field and value 2...n)
      *
      * @param fields        the array of fields
      * @param values        the array of values
      * @param useOr         true if the clause should OR results, otherwise it will AND
      * @param caseSensitive true if the field match should be case sensitive
      * @param not           If true, matches field that aren't equal
      * @return a String with the clause
      */
     public static String fieldEqualsValueClause(String[] fields,
                                                 String[] values,
                                                 boolean useOr,
                                                 boolean caseSensitive,
                                                 boolean not) {
         // if we don't have the same number of fields as values, abort
         if (fields == null || values == null || fields.length != values.length) {
             return null;
         }
 
         // use AND by default unless told to use OR
         String conjunction = "AND";
         if (useOr) {
             conjunction = "OR";
         }
 
         StringBuffer clause = new StringBuffer();
         clause.append("(");
 
         // loop through field array and construct a clause that looks
         // for a match with the cooresponding value
         for (int i = 0; i < fields.length; i++) {
             if (i > 0) {
                 // more than one field to match; add a conjunction
                 clause.append(" ");
                 clause.append(conjunction);
                 clause.append(" ");
             }
             clause.append(fieldEqualsValueClause(fields[i], values[i], caseSensitive, not));
         }
         clause.append(")");
         return clause.toString();
     }
 
     /***
      * Constructs a SQL where clause checking if a field equals any of the
      * values in the array passed in.  For example:
      * <p/>
      * ((Field1 == 'Value1') OR (Field1 == 'Value2') OR (Field1 == 'Value3'))
      * <p/>
      * where field = "Field1" and value = ["Value1", "Value2", "Value3"]
      *
      * @param field  The field to check
      * @param values The values to check the field against
      * @return The constructed SQL clause; null if not needed
      */
     public static String fieldEqualsManyValuesClause(String field,
                                                      String[] values) {
         return fieldEqualsManyValuesClause(field, values, true, false);
     }
 
     /***
      * Constructs a SQL where clause checking if a field equals any of the
      * values in the array passed in.  For example:
      * <p/>
      * ((Field1 == 'Value1') OR (Field1 == 'Value2') OR (Field1 == 'Value3'))
      * <p/>
      * where field = "Field1" and value = ["Value1", "Value2", "Value3"]
      *
      * @param field         The field to check
      * @param values        The values to check the field against
      * @param caseSensitive If true, query is case sensitive
      * @param not           If true, checks if field doesn't equal any of the values
      * @return The constructed SQL clause; null if not needed
      */
     public static String fieldEqualsManyValuesClause(String field,
                                                      String[] values,
                                                      boolean caseSensitive,
                                                      boolean not) {
         // check incoming values
         if (field == null || values == null || values.length == 0) return null;
 
         /*
         return fieldInSetClause(field, values, caseSensitive, not);
         */
 
         // use fieldEqualsValueClause
         String[] fields = new String[values.length];
         for (int i = 0; i < fields.length; i++) fields[i] = field;
         if (not) {
             return fieldEqualsValueClause(fields, values, false, caseSensitive, true);
         } else {
             return fieldEqualsValueClause(fields, values, true, caseSensitive, false);
         }
     }
 
     /***
      * Generate a subclause that determines if the values exist for the given fields
      * of an object. Returns a clause of the form
      * <p/>
      * DbObj.FIELDNAME = '<value>'
      *
      * @param field The field of the object to look in
      * @param value The value to look for
      * @return a String with the clause
      */
     public static String fieldEqualsValueClause(String field,
                                                 String value) {
         return fieldEqualsValueClause(field, value, true, false);
     }
 
     /***
      * Generate a subclause that determines if the values exist for the given fields
      * of an object. Returns a clause of the form
      * <p/>
      * DbObj.FIELDNAME = '<value>'
      *
      * @param field         The field of the object to look in
      * @param value         The value to look for
      * @param caseSensitive If true, query is case sensitive
      * @param not           If true, query checks if field does NOT equal value
      * @return a String with the clause
      */
     public static String fieldEqualsValueClause(String field,
                                                 String value,
                                                 boolean caseSensitive,
                                                 boolean not) {
         if (field == null || value == null) return null;
 
         StringBuffer clause = new StringBuffer();
 
         // strip any characters that could break a clause
         value = prepEqualsValue(value);
 
         // build clause string
         if (!caseSensitive) {
             clause.append("UPPER(");
             clause.append(field);
             clause.append(") ");
             if (not) clause.append("!");
             clause.append("= '");
             clause.append(value.toUpperCase());
             clause.append("'");
         } else {
             clause.append(field);
             clause.append(" ");
             if (not) clause.append("!");
             clause.append("= '");
             clause.append(value);
             clause.append("'");
         }
         return clause.toString();
     }
 
     /***
      * Generate a subclause that determines if the values is null a given field
      * of an object. Returns a clause of the form
      * <p/>
      * DbObj.FIELDNAME = '<value>'
      *
      * @param field The field of the object to look in
      * @param not   If true, query checks if field is NOT null
      * @return a String with the clause
      */
     public static String fieldEqualsNullClause(String field,
                                                boolean not) {
         if (field == null) return null;
 
         StringBuffer clause = new StringBuffer();
 
         // build clause string
         clause.append(field);
         clause.append(" IS");
         if (not) clause.append(" NOT");
         clause.append(" NULL");
 
         return clause.toString();
     }
 
 
     /***
      * Generate a subclause that determines if all of the keywords
      * exist in the given field of an object using AND. Returns a clause
      * of the form:
      * <p/>
      * (<field> LIKE '<keyword1>' AND
      * <field> LIKE '<keyword2>' AND
      * <field> LIKE '<keyword3>' AND ... )
      * <p/>
      * Returns null if field is null or all keywords to match are null.
      *
      * @param keywordArray The keyword array to match
      * @param field        The field to match
      * @return a String with the clause
      */
     public static String keywordMatchClause(String[] keywordArray,
                                             String field) {
         String[] fields = {field};
         return keywordMatchClause(keywordArray, fields, false);
     }
 
     /***
      * Generate a subclause that determines if all of the keywords
      * exist in the given fields of an object using AND. Returns a clause
      * of the form:
      * <p/>
      * <p/>
      * ((<field1> LIKE '<keyword1>' OR
      * <field2> LIKE '<keyword1>' OR
      * <field3> LIKE '<keyword1>') AND
      * (<field1> LIKE '<keyword2>' OR
      * <field2> LIKE '<keyword2>' OR
      * <field3> LIKE '<keyword2>') AND ...)
      * <p/>
      * Returns null if all fields are null or all keywords to match are null.
      *
      * @param keywordArray the keyword array to match
      * @param fields       The fields to match
      * @return a String with the clause
      */
     public static String keywordMatchClause(String[] keywordArray,
                                             String[] fields) {
         return keywordMatchClause(keywordArray, fields, false);
     }
 
     /***
      * Generate a subclause that determines if the keywords exist in the given fields
      * of an object. Uses AND by default, unless the parameters specify to use OR.
      * Returns a clause of the form:
      * <p/>
      * ((<field1> LIKE '<keyword1>' OR
      * <field2> LIKE '<keyword1>' OR
      * <field3> LIKE '<keyword1>') AND/OR
      * (<field1> LIKE '<keyword2>' OR
      * <field2> LIKE '<keyword2>' OR
      * <field3> LIKE '<keyword2>') AND/OR ...)
      * <p/>
      * Returns null if all fields are null or all keywords to match are null.
      *
      * @param keywordArray the keyword string to match
      * @param fields       The array of fields to match
      * @param useOr        true if the clause should OR results, otherwise it will AND
      * @return a String with the clause
      */
     private static String keywordMatchClause(String[] keywordArray,
                                              String[] fields,
                                              boolean useOr) {
         if (keywordArray == null) return null;
 
         // use AND by default unless told to use OR
         String conjunction = "AND";
         if (useOr) conjunction = "OR";
 
         StringBuffer clause = new StringBuffer();
 
         // loop through keyword array and construct a clause that looks
         // for a match on all of the keywords.
         boolean foundOne = false;
         for (int i = 0; i < keywordArray.length; i++) {
             // Get match clause
             String oneMatchClause = keywordMatchClause(keywordArray[i], fields);
 
             // Add to main clause if not null
             if (oneMatchClause != null) {
                 if (foundOne) {
                     // more than one keyword to match; add a conjunction
                     clause.append(" ");
                     clause.append(conjunction);
                     clause.append(" ");
                 }
                 clause.append(oneMatchClause);
                 foundOne = true;
             }
         }
 
         // Return null if empty clause
         String result = clause.toString();
         if (result.equals("")) return null;
 
         return ("(" + result + ")");
     }
 
     /***
      * Generate a subclause that determines if the keywords exist in the given fields
      * of an object. Strips any dangerous characters like single or double
      * quote or wildcard match (%) from clause. Returns a clause of the form:
      * <p/>
      * <field> LIKE '%<keyword1>%'
      * <p/>
      * Returns null if keywordToMatch or field is null.
      *
      * @param keywordToMatch the keyword string to match
      * @param field          the field of the object to look in
      * @return a String with the clause
      */
     public static String keywordMatchClause(String keywordToMatch,
                                             String field) {
         String[] fields = {field};
         return keywordMatchClause(keywordToMatch, fields);
     }
 
     /***
      * Generate a subclause that determines if the keywords exist in the given
      * fields of an object. Allows unlimited fields to be specified, any of
      * which can be null. Strips any dangerous characters like single or double
      * quote or wildcard match (%) from clause. Returns a clause of the form:
      * <p/>
      * <field1> LIKE '%<keyword>%'
      * <field2> LIKE '%<keyword>%'
      * <field3> LIKE '%<keyword>%' ... )
      * <p/>
      * Returns null if all fields are null or keywordToMatch is null.
      *
      * @param keywordToMatch The keyword string to match
      * @param fields         The fields to match
      * @return a String with the clause
      */
     public static String keywordMatchClause(String keywordToMatch,
                                             String[] fields
     ) {
         if (keywordToMatch == null) return null;
 
         StringBuffer clause = new StringBuffer();
 
         boolean foundOne = false;
         for (int i = 0; i < fields.length; i++) {
             String oneField = fields[i];
             if (oneField != null) {
                 if (foundOne) clause.append(" OR ");
                 clause.append(fieldLikeValue(oneField, keywordToMatch));
                 foundOne = true;
             }
         }
 
         // Return null if empty clause
         String result = clause.toString();
         if (result.equals("")) return null;
 
         return ("(" + result + ")");
     }
 
     /***
      * Returns a REGEXP clause to determine if a field is blank.  This is
      * MySQL dependent.
      *
      * @param field   The field to check
      * @param inverse If true, checks if field is NOT blank
      * @return A REGEXP clause
      */
     public static String fieldIsBlankClause(String field,
                                             boolean inverse) {
         if (field == null) return null;
         StringBuffer clause = new StringBuffer();
         clause.append("(LENGTH(LTRIM(RTRIM(");
         clause.append(field);
         clause.append(")))");
         if (inverse) clause.append("!");
         clause.append("=0)");
         return clause.toString();
     }
 
     /***
      * Return a LIKE clause of the form <field> LIKE '%<value>%'
      * Returns null if value or field is null.
      *
      * @param field the database object field
      * @param value the value to match
      * @return A LIKE clause
      */
     public static String fieldLikeValue(String field,
                                         String value) {
         if (value == null || field == null) return null;
         StringBuffer clause = new StringBuffer();
 
         value = prepLikeValue(value);
 
         clause.append(field);
         clause.append(" LIKE '%");
         clause.append(value);
         clause.append("%'");
         return clause.toString();
     }
 
     /***
      * Takes a field and an alphabetical "chunk" and constructs a query to check
      * for values for the field passed in starting with characters within that
      * chunk's (inclusive) range.
      * <p/>
      * Valid chunks are three characters in the form A-C, where A is the
      * beginning of the range, C is the end, and - is any placeholder character.
      * <p/>
      * So if field,A-C produces:
      * <p/>
      * ((field LIKE 'A%') || (field LIKE 'B%') || (field LIKE 'C%'))
      * <p/>
      * If field or alphaChunk is null, returns null.
      *
      * @param field        The field to use in the clause
      * @param alphaChunk   The alphabetical "chunk" to use as a range
      * @param reverseChunk If true, the sort is reversed
      * @return The constructed like clause checking for an alphabetical range
      * @throws ControllerException
      */
     private static String advAlphaLikeClause(String field,
                                              String alphaChunk,
                                              boolean reverseChunk)
             throws ControllerException {
         // Get ranges
         int dashIndex = alphaChunk.indexOf('-');
         String begRange = alphaChunk.substring(0, dashIndex);
         String endRange = alphaChunk.substring(dashIndex + 1);
         if (reverseChunk) {
             String temp = begRange;
             begRange = endRange;
             endRange = temp;
         }
 
         // Check range is valid
         if (begRange.compareTo(endRange) == 1) {
             throw new ControllerException("TI: Bad range passed to advAlphaLikeClause: [" + begRange + "] to [" + endRange + "]");
         }
 
         // Adjust beginning range, if necessary
         while (endRange.charAt(0) == begRange.charAt(0) &&
                 endRange.length() > begRange.length()) {
             begRange += "a";
         }
 
         // If ranges are the same, just return like clause
         if (begRange.equalsIgnoreCase(endRange)) {
             return "(" + field + " LIKE '" + begRange + "%')";
         }
 
         // Make like clause
         String curPrefix = begRange;
         String likeClause = "((" + field + " LIKE '" + begRange + "%')";
         while (!curPrefix.equalsIgnoreCase(endRange)) {
             curPrefix = incAlphaStr(curPrefix);
             if (curPrefix.charAt(0) == endRange.charAt(0)) {
                 if (curPrefix.length() > endRange.length()) {
                     curPrefix = curPrefix.substring(0, endRange.length());
                 } else if (endRange.charAt(0) == curPrefix.charAt(0)) {
                     while (endRange.length() > curPrefix.length()) {
                         curPrefix += "a";
                     }
                 }
             }
             likeClause += " OR (" + field + " LIKE '" + curPrefix + "%')";
         }
         likeClause += ")";
 
         return likeClause;
     }
 
     /***
      * "Increments" an alpha string, rolling Z to A, while preserving case,
      * e.g. "ABC" becomes "ABD" and "Az" becomes "Ba".
      *
      * @param str The string to "increment"
      * @return The resulting incremented string
      */
     private static String incAlphaStr(String str) {
         int length = str.length();
         if (length == 0) return "";
         boolean doBreak = false;
         for (int i = length - 1; i >= 0; i--) {
             char c = str.charAt(i);
             if (c == 'z') {
                 c = 'a';
             } else if (c == 'Z') {
                 c = 'A';
             } else {
                 doBreak = true;
                 c++;
             }
             str = str.substring(0, i) + c + str.substring(i + 1, str.length());
             if (doBreak) break;
             doBreak = false;
         }
         return str;
     }
 
 
     /***
      * See comments in other method signature.
      *
      * @param field      The field to use in the clause
      * @param alphaChunk The alphabetical "chunk" to use as a range
      * @return The constructed like clause checking for an alphabetical range
      * @throws ControllerException
      */
     public static String alphaLikeClause(String field,
                                          String alphaChunk)
             throws ControllerException {
         return alphaLikeClause(field, alphaChunk, false);
     }
 
     /***
      * Takes a field and an alphabetical "chunk" and constructs a query to check
      * for values for the field passed in starting with characters within that
      * chunk's (inclusive) range.
      * <p/>
      * Valid chunks are three characters in the form A-C, where A is the
      * beginning of the range, C is the end, and - is any placeholder character.
      * <p/>
      * So if field,A-C produces:
      * <p/>
      * ((field LIKE 'A%') || (field LIKE 'B%') || (field LIKE 'C%'))
      * <p/>
      * If field or alphaChunk is null, returns null.
      *
      * @param field        The field to use in the clause
      * @param alphaChunk   The alphabetical "chunk" to use as a range
      * @param reverseChunk If true, the sort is reversed
      * @return The constructed like clause checking for an alphabetical range
      * @throws ControllerException
      */
     public static String alphaLikeClause(String field,
                                          String alphaChunk,
                                          boolean reverseChunk)
             throws ControllerException {
         // Return null if one or the other is blank or empty
         if (StringUtil.isBlankOrNull(field) ||
                 StringUtil.isBlankOrNull(alphaChunk)) {
             return null;
         }
 
         // SPECIAL CASE: ignore reverse if chunk is not letters
         if (!Character.isLetter(alphaChunk.charAt(0))) reverseChunk = false;
 
         // If solid range, just return simple like clause
         int dashIndex = alphaChunk.indexOf('-');
         if (dashIndex == -1) {
             return "(" + field + " LIKE '" + alphaChunk + "%')";
         }
 
         // Throw exception if dash isn't in the middle somewhere
         if (alphaChunk.startsWith("-") || alphaChunk.endsWith("-")) {
             throw new ControllerException("TI: Bad chunk passed to alphaLikeClause: " + alphaChunk);
         }
 
         // Go to advanced method if chunk is longer than 3 characters
         if (alphaChunk.length() > 3) {
             return advAlphaLikeClause(field, alphaChunk, reverseChunk);
         }
 
         // Do simple alpha chunk processing
         char first = alphaChunk.charAt(0);
         char last = first;
         if (alphaChunk.length() >= 3) last = alphaChunk.charAt(2);
         if (reverseChunk) {
             char temp = first;
             first = last;
             last = temp;
         }
         String likeClause = "(";
         for (char c = first; c <= last; c++) {
             likeClause += "(" + field + " LIKE '" + c + "%')";
             if (c != last) likeClause += " OR ";
         }
         likeClause += ")";
         if (likeClause.equals("()")) likeClause = "";
         return likeClause;
     }
 
 
     /***
      * Returns a boolean string for use in a database query, given the database
      * context found in the request.
      *
      * @param dataContext The database context
      * @param value       The boolean value
      * @return A boolean value string for use in a database query
      * @throws DBException
      */
     public static String getBoolStr(String dataContext,
                                     boolean value)
             throws DBException {
         try {
             boolean nativeBoolean = ConfigManager.getContext(dataContext).getJdbc().isNativeBool();
 
             if (value) {
                 if (nativeBoolean) {
                     return "true";
                 } else {
                     return "Y";
                 }
             } else {
                 if (nativeBoolean) {
                     return "false";
                 } else {
                     return "N";
                 }
             }
         } catch (ConfigurationException ce) {
             throw new DBException(ce);
         }
     }
 
     /***
      * Adds necessary fields to fieldsToRetrieve.
      *
      * @param fieldsToRetrieve String of fields to retrieve
      * @param requiredFields   Required fields
      * @return Appended fieldsToRetrieve string
      */
     public static String addAnyRequiredField(String fieldsToRetrieve,
                                              String[] requiredFields) {
         for (int i = 0; i < requiredFields.length; i++) {
             if (fieldsToRetrieve.indexOf(requiredFields[i]) == -1) {
                 fieldsToRetrieve += "|" + requiredFields[i];
             }
         }
         return fieldsToRetrieve;
     }
 
     /***
      * Return true if the value passed in is in the alphabetical chunk passed
      * in.
      *
      * @param alphaChunk   The alphabetical chunk
      * @param value        The value to check
      * @param reverseChunk If true, the sort is reversed
      * @return True if the value passed in is in the alphabetical chunk
      * @throws ControllerException
      */
     public static boolean isValueInAlphaChunk(String alphaChunk,
                                               String value,
                                               boolean reverseChunk)
             throws ControllerException {
         value = value.toLowerCase();
 
         // Get ranges
         int dashIndex = alphaChunk.indexOf('-');
         String begRange = alphaChunk.substring(0, dashIndex);
         String endRange = alphaChunk.substring(dashIndex + 1);
         if (reverseChunk) {
             String temp = begRange;
             begRange = endRange;
             endRange = temp;
         }
 
         // Check range is valid
         if (begRange.compareTo(endRange) == 1) {
             throw new ControllerException("TI: Bad range passed to advAlphaLikeClause: [" + begRange + "] to [" + endRange + "]");
         }
 
         // Adjust beginning range, if necessary
         while (endRange.charAt(0) == begRange.charAt(0) &&
                 endRange.length() > begRange.length()) {
             begRange += "a";
         }
 
         // If ranges are the same, just see if value starts with the chunk
         if (begRange.equalsIgnoreCase(endRange)) {
             return value.startsWith(begRange.toLowerCase());
         }
 
         // Make like clause
         String curPrefix = begRange;
         while (!curPrefix.equalsIgnoreCase(endRange)) {
             curPrefix = incAlphaStr(curPrefix);
             if (curPrefix.charAt(0) == endRange.charAt(0)) {
                 if (curPrefix.length() > endRange.length()) {
                     curPrefix = curPrefix.substring(0, endRange.length());
                 } else if (endRange.charAt(0) == curPrefix.charAt(0)) {
                     while (endRange.length() > curPrefix.length()) {
                         curPrefix += "a";
                     }
                 }
             }
             if (value.startsWith(curPrefix.toLowerCase())) return true;
         }
 
         return false;
     }
 
     /***
      * Generate a subclause that determines if a field has a value in the given
      * set of values.
      * <p/>
      * DbObj.FIELDNAME IN ('<value1>','<value2>' ...)
      *
      * @param field  the field name
      * @param values the array of values
      * @param not    If true, matches field that aren't equal
      * @return a String with the clause
      */
     public static String fieldInSetClause(String field,
                                           String[] values,
                                           boolean not) {
         // if we don't have the same number of fields as values, abort
         if (field == null || values == null) return null;
 
         StringBuffer clause = new StringBuffer("(");
         clause.append(field);
         if (not) clause.append(" NOT");
         clause.append(" IN (");
         for (int i = 0; i < values.length; i++) {
             if (i != 0) clause.append(",");
             String value = "'" + values[i] + "'";
             clause.append(value);
         }
         clause.append("))");
         return clause.toString();
     }
 }