//resulting IDs
Collection resultsCollection = new ArrayList();
//List of ALL the tables needed for this query
Collection allTables = new ArrayList();
CVDal cvdal = new CVDal(this.dataSource);
//Key: FieldID, Value: comma seperated IDs.
HashMap interumResults = new HashMap();
//Just so I don't have to query the db twice.
//Key: FieldID, Value: Field Name.
HashMap searchFields = new HashMap();
//Just so I don't have to query the db twice.
//Key: TableID, Value: Table Name.
HashMap searchTables = new HashMap();
try
{
int numberOfGroups = searchVO.getNumberOfGroups();
int criteriaSize = searchVO.getNumberOfCriteria();
String primaryTable = "";
Number primaryTableID = new Integer(0);
String primaryKey = "";
//Get the Primary Table for this module.
String mainTableQuery = "SELECT st.TablePrimaryKey, st.SearchTableID, "
+ "st.TableName FROM searchtable st, searchmodule sm WHERE "
+ "sm.SearchTableID = st.SearchTableID AND sm.ModuleID = ? "
+ "AND sm.IsPrimaryTable = 'Y'";
cvdal.setSqlQuery(mainTableQuery);
cvdal.setInt(1, searchVO.getModuleID());
Collection mainTableResults = cvdal.executeQuery();
cvdal.setSqlQueryToNull();
if (mainTableResults != null)
{
Iterator mainTableIterator = mainTableResults.iterator();
//Only get the first one.
if (mainTableIterator.hasNext())
{
HashMap mainTableHashMap = (HashMap) mainTableIterator.next();
primaryTable = (String) mainTableHashMap.get("TableName");
primaryKey = (String) mainTableHashMap.get("TablePrimaryKey");
primaryTableID = (Number) mainTableHashMap.get("SearchTableID");
} //end of while loop (mainTableIterator.hasNext())
} //end of if statement (mainTableResults != null)
//end of Get the Primary Table for this module.
//Start Criteria Searches
Collection criteriaCollection = searchVO.getSearchCriteria();
if (criteriaCollection != null)
{
Iterator criteriaIterator = criteriaCollection.iterator();
// There are two paths for a criterion. If it is a standard
// field we have to do a whole bunch of stuff to build a fancy
// query. If it is a custom field then we can rather easily
// get the data we seek.
int searchCriteriaCounter = 0;
while (criteriaIterator.hasNext())
{
String thisTable = "";
String thisRealTable = "";
String thisPrimaryKey = "";
String thisField = "";
String thisRelationship = "";
SearchCriteriaVO searchCriteria = (SearchCriteriaVO)criteriaIterator.next();
StringBuffer selectQuery = new StringBuffer();
if (searchCriteria.getTableID().equals(SearchVO.CUSTOM_FIELD_TABLEID))
{
// We are a custom field criteria
// Find out if we are scalar or multiple.
String customFieldTypeQuery = "SELECT fieldType FROM customfield "
+ "WHERE customFieldId = ?";
cvdal.setSqlQuery(customFieldTypeQuery);
cvdal.setInt(1, Integer.parseInt(searchCriteria.getFieldID()));
Collection typeResults = cvdal.executeQuery();
cvdal.setSqlQueryToNull();
String customFieldType = null;
if (typeResults != null)
{
Iterator typeIterator = typeResults.iterator();
if (typeIterator.hasNext())
{
HashMap type = (HashMap)typeIterator.next();
customFieldType = (String)type.get("fieldType");
} // end if (typeResults != null)
} // end if (typeIterator.hasNext())
if (customFieldType.equals("SCALAR"))
{
selectQuery.append("SELECT cfv.recordId FROM customfieldscalar AS cfv, "
+ "customfield AS cf WHERE cf.customfieldid = cfv.customfieldid "
+ "AND cf.customfieldid = ");
} else {
selectQuery.append("SELECT cfm.recordId FROM customfieldmultiple AS cfm, "
+ "customfieldvalue AS cfv, customfield AS cf "
+ "WHERE cfm.valueid = cfv.valueid AND cf.customfieldid = cfm.customfieldid "
+ "AND cf.customfieldid = ");
}
selectQuery.append(searchCriteria.getFieldID());
selectQuery.append(" AND cfv.value ");
String valueCondition = (String) this.getConditionString(
searchCriteria.getValue(), "value", "cfv",
Integer.parseInt(searchCriteria.getConditionID()));
selectQuery.append(valueCondition);
thisPrimaryKey = "recordId";
searchTables.put(new Integer(searchCriteria.getTableID()), primaryTable);
searchFields.put(new Integer(searchCriteria.getFieldID()), primaryKey);
} else {
// Gather table information for each criterion
String tableInfoString = "SELECT TableName, TablePrimaryKey "
+ "FROM searchtable WHERE SearchTableID = ?";
cvdal.setSqlQuery(tableInfoString);
cvdal.setInt(1, Integer.parseInt(searchCriteria.getTableID()));
Collection tableResults = cvdal.executeQuery();
cvdal.setSqlQueryToNull();
if (tableResults != null)
{
Iterator tableIterator = tableResults.iterator();
if (tableIterator.hasNext())
{
HashMap tableHashMap = (HashMap) tableIterator.next();
thisTable = (String) tableHashMap.get("TableName");
thisPrimaryKey = (String) tableHashMap.get("TablePrimaryKey");
searchTables.put(new Integer(searchCriteria.getTableID()), thisTable);
searchFields.put(new Integer(searchCriteria.getFieldID()), thisPrimaryKey);
if (!allTables.contains(thisTable))
{
allTables.add(thisTable);
} //end of if statement (!tables.contains(tableName))
} //end of while loop (tableIterator.hasNext())
} //end of if statement (tableResults != null)
// Gather Field Information for each criterion
String fieldInfoString = "SELECT FieldName, "
+ "IsOnTable, RelationshipQuery, RealTableName "
+ "FROM searchfield WHERE SearchFieldID = ?";
cvdal.setSqlQuery(fieldInfoString);
cvdal.setInt(1, Integer.parseInt(searchCriteria.getFieldID()));
Collection fieldResults = cvdal.executeQuery();
cvdal.setSqlQueryToNull();
if (fieldResults != null)
{
Iterator fieldIterator = fieldResults.iterator();
if (fieldIterator.hasNext())
{
HashMap fieldHashMap = (HashMap) fieldIterator.next();
String isOnTable = (String) fieldHashMap.get("IsOnTable");
thisField = (String) fieldHashMap.get("FieldName");
if (isOnTable.equalsIgnoreCase("N"))
{
thisRealTable = (String) fieldHashMap.get("RealTableName");
if (!allTables.contains(thisRealTable))
{
allTables.add(thisRealTable);
} //end of if statement (!tables.contains(tableName))
// alias determination
Collection alias = new ArrayList();
StringTokenizer aliasCommaTokens = new StringTokenizer(thisRealTable, ",");
while(aliasCommaTokens.hasMoreTokens()){
String aliasRealTable = aliasCommaTokens.nextToken();
StringTokenizer aliasTokens = new StringTokenizer(aliasRealTable, " ");
int tokenLen = aliasTokens.countTokens();
if(tokenLen > 1){
if(aliasTokens.hasMoreTokens())
{
String tempTableName = aliasTokens.nextToken();
alias.add(aliasTokens.nextToken());
}//end of if(aliasTokens.hasMoreTokens())
}
else{
if(aliasTokens.hasMoreTokens())
{
alias.add(aliasTokens.nextToken());
}
}
}//end of while(aliasCommaTokens.hasMoreTokens())
thisRelationship = (String) fieldHashMap.get("RelationshipQuery");
if (thisRelationship != null)
{
StringTokenizer relationshipTokens = new StringTokenizer(thisRelationship, " ");
while (relationshipTokens.hasMoreTokens())
{
String thisToken = relationshipTokens.nextToken();
int index = thisToken.indexOf(".");
if (index > -1)
{
String tableName = null;
if (thisToken.indexOf("(") == 0) { // grrrrrrr
tableName = thisToken.substring(1, index);
} else {
tableName = thisToken.substring(0, index);
}
//Incase if you added the new line to the Query then before
//check for the occurance. We will eliminate the new line return character.
tableName = tableName.replaceAll("\n","");
if ((!allTables.contains(tableName)) && (!alias.contains(tableName)))
{
allTables.add(tableName);
} //end of if statement (!tables.contains(tableName))
} //end of if statement (index > -1)
} //end of while loop (relationshipTokens.hasMoreTokens())
} //end of if statement (thisRelationship != null)
} //end of if statement (isOnTable.equalsIgnoreCase("N"))
} //end of if statement (fieldIterator.hasNext())
} //end of if statement (fieldResults != null)
if (thisRealTable.length() < 1)
{
thisRealTable = thisTable;
} //end of if statement (thisRealTable.length() < 1)
// This is the actual interesting part of the WHERE clause
String valueCondition = (String) this.getConditionString(
searchCriteria.getValue(), thisField, thisRealTable,
Integer.parseInt(searchCriteria.getConditionID()));
// Build the criterion Query
selectQuery.append("SELECT ");
selectQuery.append(thisTable);
selectQuery.append(".");
selectQuery.append(thisPrimaryKey);
selectQuery.append(" FROM ");
Iterator thisTableIterator = allTables.iterator();
while (thisTableIterator.hasNext())
{
String currentTable = (String) thisTableIterator.next();
selectQuery.append(currentTable);
if (thisTableIterator.hasNext())
{
selectQuery.append(", ");
} //end of if statement (thisTableIterator.hasNext())
} //end of while loop (thisTableIterator.hasNext())
// The actual Question the user is asking about this field:
StringBuffer fieldCriterion = new StringBuffer();
//If there is a relationship, this following piece
//should be setup in the database.
if (thisRelationship == null || thisRelationship.length() < 1)
{
fieldCriterion.append(thisRealTable);
fieldCriterion.append(".");
} //end of if statement (thisRelationship == null ...
fieldCriterion.append(thisField);
fieldCriterion.append(" ");
fieldCriterion.append(valueCondition);
// the following things don't have "record" rights applied
// they can be skipped and just do this thing.
if (thisRealTable.equalsIgnoreCase("methodofcontact")
|| thisRealTable.equalsIgnoreCase("customfield")
|| thisRealTable.equalsIgnoreCase("address")
|| (thisRelationship != null && thisRelationship.length() > 0))
{
//This is bad.... bad bad bad, but there isn't an easier way
//to do this.
selectQuery.append(" WHERE ");
if (thisRelationship != null && thisRelationship.length() > 0)
{
selectQuery.append(thisRelationship);
selectQuery.append(" AND ");
} //end of if statement (thisRelationship != null ...
selectQuery.append(fieldCriterion);
} //end of if statement (thisTable.equals("methodofcontact"))...
else
{
// Gotta do some record rights crap.
selectQuery.append(this.getRecordPermissionQuery(individualID,
thisRealTable, thisPrimaryKey, selectQuery, fieldCriterion,
searchVO.getModuleID(),allTables));
} //end of else statement (thisTable.equals("methodofcontact"))...
} // end else for if (searchCriteria.getTableID().equals(CUSTOM_FIELD_TABLEID))
// built the query now get iterum results.
cvdal.setSqlQuery(selectQuery.toString());
Collection searchResults = cvdal.executeQuery();
cvdal.setSqlQueryToNull();
if (searchResults != null)
{
StringBuffer commaDelimitedIDs = new StringBuffer("");
Iterator resultsIterator = searchResults.iterator();
while (resultsIterator.hasNext())
{
HashMap resultsHashMap = (HashMap) resultsIterator.next();
Number primaryID = (Number) resultsHashMap.get(thisPrimaryKey);
commaDelimitedIDs.append(primaryID.toString());
if (resultsIterator.hasNext())
{
commaDelimitedIDs.append(", ");
} //end of if statement (resultsIterator.hasNext())
} //end of while loop (resultsIterator.hasNext())
if (commaDelimitedIDs.length() < 1)
{
commaDelimitedIDs.append("0");
} //end of if statement (commaDelimitedIDs.length() < 1)
// interumResults are now keyed on a unique counter
interumResults.put(new Integer(searchCriteriaCounter), commaDelimitedIDs.toString());
} //end of if statement (searchResults != null)
// whack the tables from this list
allTables.clear();
// The guaranteed unique counter is now stored in the collection of searchCriteria
// which we are working with.
searchCriteria.setSearchCriteriaID(String.valueOf(searchCriteriaCounter));
searchCriteriaCounter++;
} //end of while loop (criteriaIterator.hasNext())
} //end of if statement (criteriaCollection != null)
//End Criteria Searches
// Build the relationship and get out the primary keys we need.
if (criteriaCollection != null)
{
Iterator criteriaIterator = criteriaCollection.iterator();
while (criteriaIterator.hasNext())
{
SearchCriteriaVO searchCriteria = (SearchCriteriaVO) criteriaIterator.next();
Integer searchCriteriaId = new Integer(searchCriteria.getSearchCriteriaID());
// See if its worth finding the relationship, etc. If there is no
// interum results then there will most definitely be no final results
String ids = (String)interumResults.get(searchCriteriaId);
String relationshipQuery = "";
if (ids != null && ids.length() > 0 && !ids.equals("0")) // oh yeah, and if it equals 0, we can skip it too.
{
// if the table for this criteria is not the primary table, then we need to
// get the relationship query from searchtablerelate, and run a query to
// reduce back to the primarykey.
if ((primaryTableID.intValue() != Integer.parseInt(searchCriteria.getTableID()))
&& !(searchCriteria.getTableID().equals(SearchVO.CUSTOM_FIELD_TABLEID)))
{
String tableString = "SELECT RelationshipQuery FROM searchtablerelate " +
"WHERE (LeftSearchTableID = ? AND RightSearchTableID = ?) " +
"OR (LeftSearchTableID = ? AND RightSearchTableID = ?)";
cvdal.setSqlQuery(tableString);
cvdal.setInt(1, primaryTableID.intValue());
cvdal.setInt(2, Integer.parseInt(searchCriteria.getTableID()));
cvdal.setInt(3, Integer.parseInt(searchCriteria.getTableID()));
cvdal.setInt(4, primaryTableID.intValue());
Collection relationCollection = cvdal.executeQuery();
cvdal.setSqlQueryToNull();
if (relationCollection != null)
{
Iterator relationIterator = relationCollection.iterator();
if (relationIterator.hasNext())
{
HashMap relationHashMap = (HashMap)relationIterator.next();
relationshipQuery = (String)relationHashMap.get("RelationshipQuery");
// build up a collection of all the tables referenced in the relationship
// query.
StringTokenizer relationshipTokens = new StringTokenizer(relationshipQuery, " ");
while (relationshipTokens.hasMoreTokens())
{
String thisToken = relationshipTokens.nextToken();
int index = thisToken.indexOf(".");
if (index > -1)
{
String tableName = thisToken.substring(0, index);
if (!allTables.contains(tableName))
{
allTables.add(tableName);
} //end of if statement (!tables.contains(tableName))
} //end of if statement (index > -1)
} //end of while loop (relationshipTokens.hasMoreTokens())
} //end of if statement (relationIterator.hasNext())
} //end of if statement (relationCollection != null)
// Run another query for each criteria that now gets the
// PrimaryTable.PrimaryKey for each set of interum results.
// In this way the final query will be done to sort out the
// ANDs, ORs and GROUPINGS
if (!allTables.contains(primaryTable))
{
allTables.add(primaryTable);
}
StringBuffer primaryKeyQuery = new StringBuffer("SELECT ");
primaryKeyQuery.append(primaryTable + "." + primaryKey);
primaryKeyQuery.append(" FROM ");
Iterator tableIterator = allTables.iterator();
while (tableIterator.hasNext())
{
String thisTable = (String) tableIterator.next();
primaryKeyQuery.append(thisTable);
if (tableIterator.hasNext())
{
primaryKeyQuery.append(",");
} //end of if statement (tableIterator.hasNext())
primaryKeyQuery.append(" ");
} //end of while loop (tableIterator.hasNext())
primaryKeyQuery.append(" WHERE ");
if (!relationshipQuery.equals(""))
{
primaryKeyQuery.append(relationshipQuery);
primaryKeyQuery.append(" AND ");
}
primaryKeyQuery.append((String)searchTables.get(new Integer(searchCriteria.getTableID())));
primaryKeyQuery.append(".");
primaryKeyQuery.append((String) searchFields.get(new Integer(searchCriteria.getFieldID())));
primaryKeyQuery.append(" IN ( ");
primaryKeyQuery.append(ids);
primaryKeyQuery.append(") ");
cvdal.setSqlQuery(primaryKeyQuery.toString());
// This is the collection of primary ids based on the criteria query
Collection primaryKeyCollection = cvdal.executeQuery();
cvdal.setSqlQueryToNull();
if (primaryKeyCollection != null)
{
StringBuffer commaDelimitedIDs = new StringBuffer("");
Iterator resultsIterator = primaryKeyCollection.iterator();
while (resultsIterator.hasNext())
{
HashMap resultsHashMap = (HashMap)resultsIterator.next();
Number primaryID = (Number)resultsHashMap.get(primaryKey);
commaDelimitedIDs.append(primaryID.toString());
if (resultsIterator.hasNext())
{
commaDelimitedIDs.append(", ");
} //end of if statement (resultsIterator.hasNext())
} //end of while loop (resultsIterator.hasNext())
if (commaDelimitedIDs.length() < 1)
{
commaDelimitedIDs.append("0");
} //end of if statement (commaDelimitedIDs.length() < 1)
// replace the existing iterumResults with the new improved interumResults
// Which now are actual primaryKey ids, so the final query will have no
// joins, only boolean logic and IN operators.
interumResults.put(searchCriteriaId, commaDelimitedIDs.toString());
}
// clear this every time, as we will only be needing it for the preceding query
allTables.clear();
} //end if ((primaryTableID.intValue() != Integer.parseInt(searchCriteria.getTableID())) && !(searchCriteria.getTableID().equals(SearchVO.CUSTOM_FIELD_TABLEID)))
} // end if (ids != null && ids.length() > 0 && !ids.equals("0"))
} //end of while loop (criteriaIterator.hasNext())
} //end of if statement (criteriaCollection != null)
//End of build the relationship
//Build the final query
StringBuffer finalQuery = new StringBuffer();
finalQuery.append("SELECT DISTINCT ");
finalQuery.append(primaryTable + "." + primaryKey);
finalQuery.append(" FROM ");
finalQuery.append(primaryTable);
finalQuery.append(" WHERE ");
// count is the number of criteria stuck on the end of this query
int count = 0;
Integer[] groupIDs = searchVO.getGroupIDs();
for (int i = 0; i < groupIDs.length; i++)
{
Collection groupCollection = null;
// just do a seek and destroy mission. This way is probably better, saves DB access
// to get the collection of criteria for this group.
groupCollection = this.getSearchCriteriaForGroup(groupIDs[i].intValue(), criteriaCollection);
HashMap searchConditions = SearchVO.getConditionOptions();
if (groupCollection != null)
{
boolean firstCriteria = true;
Iterator groupIterator = groupCollection.iterator();
while (groupIterator.hasNext())
{
SearchCriteriaVO searchCriteria = (SearchCriteriaVO) groupIterator.next();
String ids = (String)interumResults.get(new Integer(searchCriteria.getSearchCriteriaID()));
// Check to see if it is worth appending this critieria on the Query.
// if the interumResults don't exist or there isn't at least 1 id then skip it.
if (ids != null && ids.length() > 0)
{
count++;
if (i != 0) // For all but first group.
{
finalQuery.append(" ");
finalQuery.append(searchCriteria.getExpressionType().toUpperCase());
finalQuery.append(" ");
} //end of if statement (i != 0)
if (!firstCriteria) // for all but first criteria in a group
{
finalQuery.append(" ");
finalQuery.append(searchCriteria.getExpressionType().toUpperCase());
finalQuery.append(" ");
} //end of if statement (!firstCriteria)
else // The first criteria in a group should open a parens.
{
finalQuery.append("(");
firstCriteria = false;
} //end of else statement (!firstCriteria)
finalQuery.append("("); // every criteria is surrounded by individual parens
finalQuery.append(primaryTable);
finalQuery.append(".");
finalQuery.append(primaryKey);
finalQuery.append(" IN ( ");
finalQuery.append(ids);
finalQuery.append(") ");
finalQuery.append(") ");
if (!groupIterator.hasNext())
{
finalQuery.append(") ");
} //end of if statement (groupIterator.hasNext())
} //end of if statement (ids.length() > 0)
} //end of while loop (groupIterator.hasNext())
} //end of if statement (groupCollection != null)
} //end of for loop (int i = 0; i < groupIDs.length; i++)
//End of build the final query
// If we haven't appended any criteria
// we need to make sure we don't get anything from this query
if (count == 0)
{
//Just so we don't get ALL!! of the results.
finalQuery.append(" 1=0 ");
}
//Run the final query
cvdal.setSqlQueryToNull();
cvdal.setSqlQuery(finalQuery.toString());
Collection rawResults = cvdal.executeQuery();
if (rawResults != null)
{
Iterator rawIterator = rawResults.iterator();
while (rawIterator.hasNext())
{
HashMap rawHashMap = (HashMap) rawIterator.next();
Number primaryID = (Number) rawHashMap.get(primaryKey);
resultsCollection.add(primaryID);
} //end of while loop (rawIterator.hasNext())
} //end of if statement (rawResults != null)
//End of Run the final query
} //end of try block
catch (Exception e)
{
System.out.println("[Exception] AdvancedSearchEJB.performSearch: " + e);
e.printStackTrace();
} //end of catch block (Exception)
finally
{
cvdal.destroy();
cvdal = null;
} //end of finally block
return resultsCollection;
} //end of performSearch method