StringBuffer orderClause = new StringBuffer();
StringBuffer clause = new StringBuffer();
ResultSet resultSet = null;
WhereClauseVO wcvo = null;
// When we are building creating a non table field then we must have to make
// the list for those field
// then add it to the create table back
StringBuffer nonTableFieldQuery = new StringBuffer();
// It a collection of query which we have to run after creating the table
// we must have to build this query by joining the field relation ship with
// the primary table
// then update the column in the temporary table.
ArrayList updateQueryList = new ArrayList();
ReportVO report = new ReportVO();
report = this.getStandardReport(userId, reportId);
try {
InitialContext ctx = new InitialContext();
Object oh = ctx.lookup("local/AdvancedSearch");
AdvancedSearchLocalHome cfh = (AdvancedSearchLocalHome) javax.rmi.PortableRemoteObject.narrow(oh,
com.centraview.advancedsearch.AdvancedSearchLocalHome.class);
AdvancedSearchLocal localAdvancedSearch = (AdvancedSearchLocal) cfh.create();
localAdvancedSearch.setDataSource(this.dataSource);
// Here is where the advanced Search is actually performed and the results
// effect the
// results of the report query.
String whereClause = localAdvancedSearch.getWhereClauseForReport(userId, search, "");
HashMap primaryTableMap = localAdvancedSearch.getPrimaryTableForModule(report.getModuleId());
// get the primary Table Name and Table Primary Key incase If the user
// doesn't selects the primary key
// You must have to add a hidden field in your report criteria coz primary
// key only link to all other tables
String primaryKey = primaryTableMap.get("TablePrimaryKey").toString();
String primaryTable = primaryTableMap.get("TableName").toString();
ArrayList tableIds = new ArrayList();
int tableId = ((Number) primaryTableMap.get("TableID")).intValue();
// add select statement to query string to create the temporary Table
strQuery.append("SELECT DISTINCT ");
// add select statement to final query string to generate Report Query
finalQuery.append("SELECT DISTINCT ");
// select searchtable.tablename, searchfield.fieldname,
// reportcontent.sequencenumber, searchtable.searchtableid,
// searchfield.isontable, searchfield.realtablename,
// searchfield.relationshipquery, reportcontent.fieldid from searchtable,
// searchfield,
// reportcontent where reportcontent.reportid = ? and
// searchfield.searchfieldid = reportcontent.fieldid and
// searchtable.searchtableid = reportcontent.tableId order by
// reportcontent.sequencenumber
dataAccessLayer.setSql("reports.selectclause");
dataAccessLayer.setInt(1, reportId);
resultSet = dataAccessLayer.executeQueryNonParsed();
whereClauseCollection = this.convertRStoWhereClauseVO(resultSet);
// we are going to reuse the resultSet reference so clean up here.
try {
resultSet.close();
resultSet = null;
} catch (SQLException se) {}
// making select clause
// oddly from the Where Clause VO, apparently it can double as a Select
// Clause VO ;-)
boolean first = true;
// Build the Select QueryFields List
Collection selectQueryList = new ArrayList();
// Build the Select non Table Fields List
Collection nonTableFieldList = new ArrayList();
// Build the Select final Query Fields List
Collection finalQueryList = new ArrayList();
// If the user as not selected the primary key then we must have to add
// that key in the table
boolean primaryKeyFlag = true;
// Iterate the Where claues and get the information of the Field.
// If field is in on the table then add it to the selectQueryList
// If field is not on the table then add it to the nonTableFieldList
Iterator it = whereClauseCollection.iterator();
while (it.hasNext()) {
wcvo = (WhereClauseVO) it.next();
if (wcvo.getIsOnTable().equals("Y")) {
// we must have to check wheather the user wants to view the primary
// key column or not
// If not then we must have to add the primary key in the temporary
// Table
if (wcvo.getFieldName() != null && (wcvo.getFieldName().trim()).equalsIgnoreCase(primaryKey.trim())) {
primaryKeyFlag = false;
}// end of if(wcvo.getFieldName() != null &&
// (wcvo.getFieldName().trim()).equalsIgnoreCase(primaryKey.trim()))
// Add on table field to the query list
selectQueryList.add(wcvo.getTableName() + "." + wcvo.getFieldName() + " ");
// user wants to see the column so we have to add in the final query
// also
finalQueryList.add(" `" + wcvo.getFieldName() + "` ");
}// end of if (wcvo.getIsOnTable().equals("Y"))
else {
// create a unique name for the non table field and then we must have
// to add the field id to it..
String nonTableField = "adHocField";
int customTableID = 0;
// we will check wheather the current field is belonging to
// customfield table
if (SearchVO.CUSTOM_FIELD_TABLEID != null && !SearchVO.CUSTOM_FIELD_TABLEID.equals("")) {
customTableID = Integer.parseInt(SearchVO.CUSTOM_FIELD_TABLEID);
}
// If its belonging to custom field table then we must have to concat
// the "CF"
// to the uniquie field name why we have to do that since user might
// select the fieldID 1 belonging to the searchtable
// for customfield can also have field id 1
// It will get conflicted and break the report
if (wcvo.getSearchTableId() == customTableID) {
nonTableField += "CF";
}
// add the non Table field in the list
nonTableFieldList.add(" `" + nonTableField + wcvo.getFieldId() + "` Text NOT NULL default '' ");
// add the user selected field in the final query
finalQueryList.add(nonTableField + wcvo.getFieldId() + " ");
// build the update script for the non table fields
StringBuffer updateQuery = new StringBuffer();
updateQuery.append("UPDATE tempAdHocReport ");
String relationShipQuery = wcvo.getRelationshipQuery();
String realTableName = wcvo.getRealTableName();
// alias determination
Collection alias = new ArrayList();
StringTokenizer aliasCommaTokens = new StringTokenizer(realTableName, ",");
while (aliasCommaTokens.hasMoreTokens()) {
String aliasRealTable = aliasCommaTokens.nextToken();
StringTokenizer aliasTokens = new StringTokenizer(aliasRealTable, " ");
String tempTableName = aliasTokens.nextToken();
if (aliasTokens.hasMoreTokens()) {
alias.add(aliasTokens.nextToken());
}// end of if(aliasTokens.hasMoreTokens())
}// end of while(aliasCommaTokens.hasMoreTokens())
if (realTableName != null) {
updateQuery.append(" , " + realTableName);
String thisRelationship = "";
if (relationShipQuery != null) {
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);
// 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 (!(realTableName.indexOf(tableName) != -1) && (!alias.contains(tableName))) {
updateQuery.append(", " + tableName);
alias.add(tableName);
} // end of if statement (!tables.contains(tableName))
} // end of if statement (index > -1)
} // end of while loop (relationshipTokens.hasMoreTokens())
updateQuery.append(" SET `" + nonTableField + wcvo.getFieldId() + "` = " + wcvo.getFieldName() + " ");
updateQuery.append(" WHERE " + primaryTable + "." + primaryKey + " = tempAdHocReport." + primaryKey + " " + whereClause);
thisRelationship = " AND " + relationShipQuery;
// if the sub relationShip column is empty then we must have to
// add the value to the update query
String subRelationShipQuery = wcvo.getSubRelationShipQuery();
if (subRelationShipQuery != null && !subRelationShipQuery.equals("")) {
thisRelationship += subRelationShipQuery;
}// end of if(subRelationShipQuery != null &&
// !subRelationShipQuery.equals(""))
}// end of if (relationShipQuery != null)
updateQuery.append(thisRelationship);
}// end of if(realTableName != null)
updateQueryList.add(updateQuery.toString());
}// end of else for if (wcvo.getIsOnTable().equals("Y"))
}// end of while (it.hasNext())
Iterator thisTableIterator = selectQueryList.iterator();
while (thisTableIterator.hasNext()) {
String currentTable = (String) thisTableIterator.next();
strQuery.append(currentTable);
if (thisTableIterator.hasNext()) {
strQuery.append(", ");
} // end of if statement (thisTableIterator.hasNext())
} // end of while loop (thisTableIterator.hasNext())
// if the user as not selected the primary key then we must have to add
// that field
// so that we don't break the association for the update script
if (primaryKeyFlag) {
strQuery.append(", " + primaryTable + "." + primaryKey);
}
thisTableIterator = finalQueryList.iterator();
while (thisTableIterator.hasNext()) {
String currentTable = (String) thisTableIterator.next();
finalQuery.append(currentTable);
if (thisTableIterator.hasNext()) {
finalQuery.append(", ");
} // end of if statement (thisTableIterator.hasNext())
} // end of while loop (thisTableIterator.hasNext())
finalQuery.append(" FROM `tempAdHocReport` ");
thisTableIterator = nonTableFieldList.iterator();
if (thisTableIterator.hasNext()) {
nonTableFieldQuery.append("(");
while (thisTableIterator.hasNext()) {
String currentTable = (String) thisTableIterator.next();
nonTableFieldQuery.append(currentTable);
if (thisTableIterator.hasNext()) {
nonTableFieldQuery.append(", ");
} // end of if statement (thisTableIterator.hasNext())
} // end of while loop (thisTableIterator.hasNext())
nonTableFieldQuery.append(")");
}
// getting table ids
it = whereClauseCollection.iterator();
while (it.hasNext()) {
wcvo = (WhereClauseVO) it.next();
if ((tableId != wcvo.getSearchTableId()) && (!tableIds.contains(new Integer(wcvo.getSearchTableId())))) {
tableIds.add(new Integer(wcvo.getSearchTableId()));
}// end of if ((tableId != wcvo.getSearchTableId()) &&
// (!tableIds.contains(new Integer(wcvo.getSearchTableId()))))
}// end of while (it.hasNext())
ArrayList fromTables = new ArrayList();
dataAccessLayer.clearParameters();
dataAccessLayer.setSql("reports.fromclause");
dataAccessLayer.setInt(1, reportId);
resultSet = dataAccessLayer.executeQueryNonParsed();
// geting tables for from statement
while (resultSet.next()) {
String tableName = resultSet.getString(1);
// Make sure we aren't adding the uncessesary customfield
// table in to the from clause.
if (!tableName.equals("customfield")) {
fromTables.add(tableName);
}// end of if (!tableName.equals("customfield"))
}// end of while (resultSet.next())
// we are going to reuse the resultSet reference so clean up here.
try {
resultSet.close();
resultSet = null;
} catch (SQLException se) {}
first = true;
strQuery.append(" FROM ");
// making all from tables
String table = "";
it = fromTables.iterator();
while (it.hasNext()) {
table = it.next().toString();
if (first) {
first = false;
strQuery.append(" " + table);
} else {
strQuery.append(", " + table);
}
}
strQuery.append(" WHERE 1=1 ");
strQuery.append(whereClause);
// add order by statement to query string
dataAccessLayer.setSqlQueryToNull();
dataAccessLayer.setSql("reports.orderbyclause");
dataAccessLayer.setInt(1, reportId);
resultSet = dataAccessLayer.executeQueryNonParsed();
// build the order by clause and the sorting which ever is selected by the
// user
first = true;
while (resultSet.next()) {
if (first) {
finalQuery.append(" ORDER BY ");
first = false;
} else {
finalQuery.append(", ");
}
// Loop through the whereClauseCollection and find the right field name
// to
// stick in the ORDER BY Statement. Maybe there could be a smarter way
// then looping
// each time, but on average there shouldn't be more than 3 sorts
// anyway.
it = whereClauseCollection.iterator();
while (it.hasNext()) {
int orderTableId = resultSet.getInt(1);
int orderFieldId = resultSet.getInt(2);
WhereClauseVO current = (WhereClauseVO) it.next();
// if the tableId and fieldId from the orderbyclause query match, then
// throw in the
// same name we used in the SELECT part of the query.
if (current.getTableId() == orderTableId && current.getFieldId() == orderFieldId) {
if (current.getIsOnTable().equals("Y")) {
finalQuery.append(current.getFieldName() + " ");
}// end of if (current.getIsOnTable().equals("Y")
else {
String nonTableField = "adHocField";
int customTableID = 0;
if (SearchVO.CUSTOM_FIELD_TABLEID != null && !SearchVO.CUSTOM_FIELD_TABLEID.equals("")) {
customTableID = Integer.parseInt(SearchVO.CUSTOM_FIELD_TABLEID);
}
if (current.getSearchTableId() == customTableID) {
nonTableField += "CF";
}
finalQuery.append(nonTableField + current.getFieldId() + " ");
}// end of else the if (current.getIsOnTable().equals("Y")
}// end of if (current.getTableId() == orderTableId &&
// current.getFieldId() == orderFieldId)
} // end while (it.hasNext())
// Stick the ASC or DESC in there.