Java tutorial
/******************************************************************************* * Copyright (c) 2009 David Harrison. * All rights reserved. This program and the accompanying materials * are made available under the terms of the GNU Public License v3.0 * which accompanies this distribution, and is available at * http://www.gnu.org/licenses/gpl-3.0.html * * Contributors: * David Harrison - initial API and implementation ******************************************************************************/ package com.sfs.whichdoctor.search.sql; import com.sfs.DataFilter; import com.sfs.Formatter; import com.sfs.beans.BuilderBean; import com.sfs.beans.ObjectTypeBean; import com.sfs.beans.UserBean; import com.sfs.whichdoctor.beans.AccreditationBean; import com.sfs.whichdoctor.beans.AddressBean; import com.sfs.whichdoctor.beans.EmailBean; import com.sfs.whichdoctor.beans.ExamBean; import com.sfs.whichdoctor.beans.ItemBean; import com.sfs.whichdoctor.beans.MembershipBean; import com.sfs.whichdoctor.beans.PersonBean; import com.sfs.whichdoctor.beans.QualificationBean; import com.sfs.whichdoctor.beans.RotationBean; import com.sfs.whichdoctor.beans.SearchBean; import com.sfs.whichdoctor.beans.SpecialtyBean; import com.sfs.whichdoctor.beans.SupervisorBean; import com.sfs.whichdoctor.beans.TagBean; import com.sfs.whichdoctor.beans.WorkshopBean; import com.sfs.whichdoctor.dao.MembershipDAO; import com.sfs.whichdoctor.dao.PersonDAO; import com.sfs.whichdoctor.dao.RotationDAO; import com.sfs.whichdoctor.dao.WhichDoctorDaoException; import com.sfs.whichdoctor.search.TagSearchDAO; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.HashMap; import java.util.Map; import javax.annotation.Resource; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; /** * The Class PersonSqlHandler. * * @author David Harrison */ public class PersonSqlHandler extends SqlHandlerBase { /** The data logger. */ private static Logger dataLogger = Logger.getLogger(PersonSqlHandler.class); /** The Constant LIMIT. */ private static final int LIMIT = 50; /** The tag search dao. */ @Resource private TagSearchDAO tagSearchDAO; /** The person dao. */ @Resource private PersonDAO personDAO; /** The membership dao. */ @Resource private MembershipDAO membershipDAO; /** The rotation dao. */ @Resource private RotationDAO rotationDAO; /** * Instantiates a new person sql handler. */ public PersonSqlHandler() { super(); this.setType("person"); this.setIdentifierColumn("people.GUID"); this.setDefaultOrder("LastName"); } /** * Initiate a SearchBean for the search type. * * @param user the user * * @return configured SearchBean */ public final SearchBean initiate(final UserBean user) { SearchBean search = new SearchBean(); PersonBean searchCriteria = new PersonBean(); PersonBean searchConstraints = new PersonBean(); searchCriteria.setMembershipDetails(membershipDAO.getAllInstances()); searchConstraints.setMembershipDetails(membershipDAO.getAllInstances()); /** Address default parameters **/ AddressBean addressCriteria = new AddressBean(); AddressBean addressConstraints = new AddressBean(); addressConstraints.setReturnedMail(true); addressConstraints.setRequestNoMail(true); Collection<AddressBean> addressDetailsCriteria = new ArrayList<AddressBean>(); Collection<AddressBean> addressDetailsConstraints = new ArrayList<AddressBean>(); addressDetailsCriteria.add(addressCriteria); addressDetailsConstraints.add(addressConstraints); searchCriteria.setAddress(addressDetailsCriteria); searchConstraints.setAddress(addressDetailsConstraints); /** Email default parameters **/ EmailBean emailCriteria = new EmailBean(); EmailBean emailConstraints = new EmailBean(); emailConstraints.setRequestNoMail(true); emailConstraints.setReturnedMail(true); emailConstraints.setEmailQuestions(true); Collection<EmailBean> emailDetailsCriteria = new ArrayList<EmailBean>(); Collection<EmailBean> emailDetailsConstraints = new ArrayList<EmailBean>(); emailDetailsCriteria.add(emailCriteria); emailDetailsConstraints.add(emailConstraints); searchCriteria.setEmail(emailDetailsCriteria); searchConstraints.setEmail(emailDetailsConstraints); /** Search default parameters **/ search.setRequestedPage(1); search.setOrderColumn("people.LastName"); search.setOrderColumn2("people.FirstName"); search.setLimit(LIMIT); search.setOrderAscending(true); search.setType("person"); search.setSearchCriteria(searchCriteria); search.setSearchConstraints(searchConstraints); return search; } /** * Gets the group by. * * @return the group by */ public final String getGroupBy() { return ""; } /** * Gets the count sql. * * @return the count sql */ public final String getCountSql() { return "SELECT count(DISTINCT people.GUID) " + this.getSQL().getValue("person/search"); } /** * Gets the select sql. * * @return the select sql */ public final String getSelectSql() { return "SELECT DISTINCT people.GUID " + getSQL().getValue("person/search"); } /** * Load the identified objects and return results as a Collection of Objects. * * @param uniqueIds the unique ids * @param loadDetails the load details * * @return a Collection of Objects */ public final Collection<Object> load(final Collection<Integer> uniqueIds, final BuilderBean loadDetails) { Collection<Object> results = new ArrayList<Object>(); if (uniqueIds != null) { for (Integer uniqueId : uniqueIds) { try { PersonBean person = this.personDAO.loadGUID(uniqueId, loadDetails); results.add(person); } catch (Exception e) { dataLogger.error("Error loading person (" + uniqueId + ") for search: " + e.getMessage()); } } } return results; } /** * Construct the SQL string, description and parameters. * * @param objCriteria Object containing search criteria values * @param objConstraints Object containing search constraint values * * @return Map containing a String[] { sql, description } => * Collection< Object > parameters * * @throws IllegalArgumentException the illegal argument exception */ @SuppressWarnings("unchecked") public final Map<String[], Collection<Object>> construct(final Object objCriteria, final Object objConstraints) throws IllegalArgumentException { PersonBean searchCriteria = null; PersonBean searchConstraints = null; if (objCriteria instanceof PersonBean) { searchCriteria = (PersonBean) objCriteria; } if (objConstraints instanceof PersonBean) { searchConstraints = (PersonBean) objConstraints; } if (searchCriteria == null) { throw new IllegalArgumentException("The search criteria must " + "be a valid PersonBean"); } if (searchConstraints == null) { throw new IllegalArgumentException("The search constraints must " + "be a valid PersonBean"); } StringBuffer sqlWHERE = new StringBuffer(); StringBuffer description = new StringBuffer(); Collection<Object> parameters = new ArrayList<Object>(); if (searchCriteria.getTags() != null) { try { for (TagBean tag : searchCriteria.getTags()) { Map<String[], Collection<Object>> results = this.tagSearchDAO.construct(tag, new TagBean()); for (String[] index : results.keySet()) { String tagWHERE = index[0]; String tagDescription = index[1]; Collection<Object> tagParameters = results.get(index); if (tagWHERE.compareTo("") != 0) { // A WHERE condition is defined // Add to the SQL WHERE clause sqlWHERE.append( " " + this.getSQL().getValue("person/searchTags") + " WHERE " + tagWHERE + ")"); /* Add to the description and process the arrays */ description.append(tagDescription); if (tagParameters != null) { parameters.addAll(tagParameters); } } } } } catch (Exception e) { dataLogger.error("Error setting tag search options: " + e.getMessage()); } } /* Basic Search is used to locate a person based on their full name */ if (StringUtils.isNotBlank(searchCriteria.getBasicSearch())) { String searchString = searchCriteria.getBasicSearch().trim(); int identifier = 0; try { identifier = Integer.parseInt(searchString); } catch (NumberFormatException nfe) { dataLogger.debug("Error parsing Basic Search: " + nfe.getMessage()); } // If the search string ends with ) then meta-data is probably included if (searchString.endsWith(")") && searchString.indexOf(" (") > 0) { // The search string has brackets - remove the brackets and their content. searchString = searchString.substring(0, searchString.lastIndexOf(" (")); } if (identifier == 0) { sqlWHERE.append(" AND (concat(title.Name, ' ', people.FirstName, ' ', "); sqlWHERE.append("people.LastName) LIKE ? OR concat(title.Name, ' ', "); sqlWHERE.append("people.PreferredName, ' ', people.LastName) LIKE ? "); sqlWHERE.append("OR concat(title.Name,' ', people.Lastname) LIKE ? "); sqlWHERE.append("OR concat(title.Name, ' ', people.FirstName, ' ', "); sqlWHERE.append("people.MaidenName) LIKE ? )"); description.append(" and a name like '" + searchString + "'"); for (int i = 0; i < 4; i++) { parameters.add("%" + searchString + "%"); } } else { sqlWHERE.append(" AND (people.PersonIdentifier = ?)"); description.append(" and a MIN equal to '" + identifier + "'"); parameters.add(identifier); } } /* * Name field is tricky. Need to build a conjoint first/last search * field */ if (searchCriteria.getFirstName() != null) { if (searchCriteria.getFirstName().compareTo("") != 0) { sqlWHERE.append(" AND (people.FirstName LIKE ? " + "OR people.PreferredName LIKE ?)"); description.append(" and a first name like '" + searchCriteria.getFirstName() + "'"); parameters.add(searchCriteria.getFirstName() + "%"); parameters.add(searchCriteria.getFirstName() + "%"); } } if (searchCriteria.getLastName() != null) { if (searchCriteria.getLastName().compareTo("") != 0) { sqlWHERE.append(" AND people.LastName LIKE ?"); description .append(" and a last name like '" + DataFilter.getHtml(searchCriteria.getLastName()) + "'"); parameters.add(searchCriteria.getLastName() + "%"); } } if (searchCriteria.getId() > 0) { boolean maximum = false; boolean minimum = false; boolean range = false; boolean single = true; if (searchConstraints.getId() < 0) { minimum = true; single = false; } if (searchConstraints.getId() > 0) { range = true; single = false; } if (searchConstraints.getId() == 999999999) { maximum = true; range = false; single = false; } if (minimum) { // Get values less than supplied ID sqlWHERE.append(" AND people.PersonId <= ?"); description.append(" and a person Id like '" + searchCriteria.getId() + "'"); parameters.add(searchCriteria.getId()); } if (maximum) { maximum = true; // Get values greater than supplied ID sqlWHERE.append(" AND people.PersonId >= ?"); description.append(" and a person Id greater than '" + searchCriteria.getId() + "'"); parameters.add(searchCriteria.getId()); } if (range) { // Search Constraints between A and B if (searchCriteria.getId() > searchConstraints.getId()) { // A greater than B sqlWHERE.append(" AND people.PersonId BETWEEN ? AND ?"); description.append(" and a person Id between '" + searchConstraints.getId() + "' and '" + searchCriteria.getId() + "'"); parameters.add(searchConstraints.getId()); parameters.add(searchCriteria.getId()); } else if (searchCriteria.getId() < searchConstraints.getId()) { // B greater than A sqlWHERE.append(" AND people.PersonId BETWEEN ? AND ?"); description.append(" and a person Id between '" + searchCriteria.getId() + "' and '" + searchConstraints.getId() + "'"); parameters.add(searchCriteria.getId()); parameters.add(searchConstraints.getId()); } else { // A = B sqlWHERE.append(" AND people.PersonId = ?"); description.append(" and a person Id equal to '" + searchCriteria.getId() + "'"); parameters.add(searchCriteria.getId()); } } if (single) { sqlWHERE.append(" AND people.PersonId = ?"); description.append(" and a person Id equal to '" + searchCriteria.getId() + "'"); parameters.add(searchCriteria.getId()); } } if (searchCriteria.getPersonIdentifier() > 0) { final int pidCriteria = searchCriteria.getPersonIdentifier(); final int pidConstraints = searchConstraints.getPersonIdentifier(); if (pidCriteria > 0) { boolean maximum = false; boolean minimum = false; boolean range = false; boolean single = true; if (pidConstraints < 0) { minimum = true; single = false; } if (pidConstraints > 0) { range = true; single = false; } if (pidConstraints == 999999999) { maximum = true; range = false; single = false; } if (minimum) { // Get values less than supplied MIN sqlWHERE.append(" AND people.PersonIdentifier <= ?"); description.append(" and a MIN less than '" + pidCriteria + "'"); parameters.add(pidCriteria); } if (maximum) { maximum = true; // Get values greater than supplied MIN sqlWHERE.append(" AND people.PersonIdentifier >= ?"); description.append(" and a MIN greater than '" + pidCriteria + "'"); parameters.add(pidCriteria); } if (range) { // Search Constraints between A and B if (pidCriteria > pidConstraints) { // A greater than B sqlWHERE.append(" AND people.PersonIdentifier BETWEEN ? AND ?"); description.append(" and a MIN between '" + pidConstraints + "' and '" + pidCriteria + "'"); parameters.add(pidConstraints); parameters.add(pidCriteria); } else if (pidCriteria < pidConstraints) { // B greater than A sqlWHERE.append(" AND people.PersonIdentifier BETWEEN ? AND ?"); description.append(" and a MIN between '" + pidCriteria + "' and '" + pidConstraints + "'"); parameters.add(pidCriteria); parameters.add(pidConstraints); } else { // A = B sqlWHERE.append(" AND people.PersonIdentifier = ?"); description.append(" and a MIN equal to '" + pidCriteria + "'"); parameters.add(pidCriteria); } } if (single) { sqlWHERE.append(" AND people.PersonIdentifier = ?"); description.append(" and a MIN equal to '" + pidCriteria + "'"); parameters.add(pidCriteria); } } } if (searchCriteria.getGUIDList() != null) { final StringBuffer guidWHERE = new StringBuffer(); for (String guid : searchCriteria.getGUIDList()) { if (StringUtils.isNotBlank(guid)) { guidWHERE.append(" OR people.GUID = ?"); parameters.add(guid); } } if (guidWHERE.length() > 0) { // Append the guidWHERE buffer to the sqlWHERE buffer sqlWHERE.append(" AND ("); // Append the guidWHERE but strip the first OR statement sqlWHERE.append(guidWHERE.toString().substring(4)); sqlWHERE.append(")"); description.append(" and has a GUID in the supplied list"); } } if (searchCriteria.getIdentifierList() != null) { final StringBuffer identifierWHERE = new StringBuffer(); for (String identifier : searchCriteria.getIdentifierList()) { if (StringUtils.isNotBlank(identifier)) { identifierWHERE.append(" OR people.PersonIdentifier = ?"); parameters.add(identifier); } } if (identifierWHERE.length() > 0) { // Append the identifierWHERE buffer to the sqlWHERE buffer sqlWHERE.append(" AND ("); // Append the identifierWHERE but strip the first OR statement sqlWHERE.append(identifierWHERE.toString().substring(4)); sqlWHERE.append(")"); description.append(" and has a MIN number in the supplied list"); } } if (searchCriteria.getTitle() != null) { if (searchCriteria.getTitle().compareTo("") != 0) { sqlWHERE.append(" AND title.Name LIKE ?"); description.append(" and a title like '" + searchCriteria.getTitle() + "'"); parameters.add(searchCriteria.getTitle()); } } if (searchCriteria.getHonors() != null) { if (searchCriteria.getHonors().compareTo("") != 0) { sqlWHERE.append(" AND people.Honors LIKE ?"); description.append(" and honors like '" + searchCriteria.getHonors() + "'"); parameters.add("%" + searchCriteria.getHonors() + "%"); } } if (searchCriteria.getGender() != null) { if (searchCriteria.getGender().compareTo("") != 0) { // Only current supervisors sqlWHERE.append(" AND Gender LIKE ?"); description.append(" and a gender like '" + searchCriteria.getGender() + "'"); parameters.add(searchCriteria.getGender()); } } if (StringUtils.isNotBlank(searchCriteria.getTrainingStatus())) { sqlWHERE.append(" AND trainingstatus.Class LIKE ?"); description.append(" and a training status of '" + searchCriteria.getTrainingStatus() + "'"); parameters.add(searchCriteria.getTrainingStatus()); } if (StringUtils.isNotBlank(searchCriteria.getTrainingStatusDetail())) { sqlWHERE.append(" AND trainingstatus.Name LIKE ?"); description .append(" and a training status detail of '" + searchCriteria.getTrainingStatusDetail() + "'"); parameters.add(searchCriteria.getTrainingStatusDetail()); } // Process the membership parameters ArrayList<MembershipBean> memDetailsCriteria = (ArrayList<MembershipBean>) searchCriteria .getMembershipDetails(); ArrayList<MembershipBean> memDetailsContaints = (ArrayList<MembershipBean>) searchConstraints .getMembershipDetails(); for (int a = 0; a < memDetailsCriteria.size(); a++) { MembershipBean membershipCriteria = memDetailsCriteria.get(a); MembershipBean membershipConstraints = memDetailsContaints.get(a); Object[] result = null; final String className = membershipCriteria.getMembershipClass(); final String typeName = membershipCriteria.getMembershipType(); if (StringUtils.equalsIgnoreCase(className, "RACP") && StringUtils.equalsIgnoreCase(typeName, "")) { result = processRACPMembership(membershipCriteria, membershipConstraints); } if (StringUtils.equalsIgnoreCase(className, "RACP") && StringUtils.equalsIgnoreCase(typeName, "Fellowship Details")) { result = processRACPFellowship(membershipCriteria, membershipConstraints); } if (StringUtils.equalsIgnoreCase(className, "RACP") && StringUtils.equalsIgnoreCase(typeName, "Affiliation")) { result = processRACPAffiliation(membershipCriteria, membershipConstraints); } if (result != null) { String sql = (String) result[0]; String desc = (String) result[1]; Collection<Object> param = (Collection<Object>) result[2]; dataLogger.debug("Membership SQL: " + sql); // Build the membership sub-search if (StringUtils.isNotBlank(sql)) { sqlWHERE.append(" "); sqlWHERE.append(this.getSQL().getValue("person/searchMembership")); sqlWHERE.append(sql); sqlWHERE.append(")"); description.append(desc); // Append the class and type fields parameters.add(className); parameters.add(typeName); parameters.addAll(param); } } } /* Search for persons region */ if (searchCriteria.getRegion() != null) { if (searchCriteria.getRegion().compareTo("") != 0 && searchCriteria.getRegion().compareTo("Unknown?") != 0) { sqlWHERE.append(" AND region.Class LIKE ?"); description.append(" and is in the '" + searchCriteria.getRegion() + "' region"); parameters.add(searchCriteria.getRegion() + "%"); } } /* Get address */ if (searchCriteria.getFirstAddress() != null && searchConstraints.getFirstAddress() != null) { final AddressBean address = searchCriteria.getFirstAddress(); final AddressBean address2 = searchConstraints.getFirstAddress(); final StringBuffer addressSearch = new StringBuffer(); if (address.getPrimary() && address2.getPrimary()) { addressSearch.append(" AND address.PrimaryAddress = ?"); description.append(" is the preferred address"); parameters.add(true); } if (StringUtils.isNotBlank(address.getContactClass())) { addressSearch.append(" AND addresstype.Class LIKE ?"); description.append(" and has an address class of '" + address.getContactClass() + "'"); parameters.add(address.getContactClass()); } if (StringUtils.isNotBlank(address.getContactType()) && !StringUtils.equals(address.getContactType(), "Any")) { addressSearch.append(" AND addresstype.Name LIKE ?"); description.append(" and has an address type of '" + address.getContactType() + "'"); parameters.add(address.getContactType()); } if (StringUtils.isNotBlank(address.getCity())) { addressSearch.append(" AND address.City LIKE ?"); description.append(" and has an address in '" + address.getCity() + "' city"); parameters.add(address.getCity() + "%"); } if (StringUtils.isNotBlank(address.getState())) { addressSearch.append(" AND loc_state.Name LIKE ?"); description.append(" and has an address in the state of '" + address.getState() + "'"); parameters.add(address.getState() + "%"); } if (StringUtils.isNotBlank(address.getCountry())) { addressSearch.append(" AND loc_state.Class LIKE ?"); description.append(" and has an address in '" + address.getCountry() + "'"); parameters.add(address.getCountry() + "%"); } if (StringUtils.isNotBlank(address.getCountryAbbreviation())) { addressSearch.append(" AND loc_state.Abbreviation LIKE ?"); description.append(" and has an address in '" + address.getCountryAbbreviation() + "'"); parameters.add(address.getCountryAbbreviation()); } if (address.getReturnedMail() && address2.getReturnedMail()) { // Only returned mail addressSearch.append(" AND address.ReturnedMail = true"); description.append(" and has an address with returned mail"); } if (!address.getReturnedMail() && !address2.getReturnedMail()) { // Only non-returned mail addressSearch.append(" AND address.ReturnedMail = false"); description.append(" and has an address without returned mail"); } if (address.getRequestNoMail() && address2.getRequestNoMail()) { // Only those who have requested no mail addressSearch.append(" AND address.RequestNoMail = true"); description.append(" and has an address which has requested no mail"); } if (!address.getRequestNoMail() && !address2.getRequestNoMail()) { // Only those who have requested mail addressSearch.append(" AND address.RequestNoMail = false"); description.append(" and has an address which accepts mail"); } if (addressSearch.length() > 0) { sqlWHERE.append(" " + getSQL().getValue("person/searchAddress") + addressSearch.toString() + ")"); } } // Perform an email search if (searchCriteria.getFirstEmailAddress() != null && searchConstraints.getFirstEmailAddress() != null) { final EmailBean email = searchCriteria.getFirstEmailAddress(); final EmailBean email2 = searchConstraints.getFirstEmailAddress(); final StringBuffer emailSearch = new StringBuffer(); if (email.getPrimary() && email2.getPrimary()) { emailSearch.append(" AND email.PrimaryEmail = ?"); description.append(" is the preferred email address"); parameters.add(true); } if (StringUtils.isNotBlank(email.getContactType())) { emailSearch.append(" AND emailtype.Name LIKE ?"); description.append(" and has an email address type of '" + email.getContactType() + "'"); parameters.add(email.getContactType()); } if (email.getReturnedMail() && email2.getReturnedMail()) { // Only returned mail emailSearch.append(" AND email.ReturnedMail = true"); description.append(" and has an email address with returned mail"); } if (!email.getReturnedMail() && !email2.getReturnedMail()) { // Only non-returned mail emailSearch.append(" AND email.ReturnedMail = false"); description.append(" and has an email address without returned mail"); } if (email.getRequestNoMail() && email2.getRequestNoMail()) { // Only those who have requested no mail emailSearch.append(" AND email.RequestNoMail = true"); description.append(" and has an email address which has requested no mail"); } if (!email.getRequestNoMail() && !email2.getRequestNoMail()) { // Only those who have requested mail emailSearch.append(" AND email.RequestNoMail = false"); description.append(" and has an email address which accepts mail"); } if (email.getEmailQuestions() && email2.getEmailQuestions()) { // Get only EmailQuestions people emailSearch.append(" AND email.EmailQuestions = true"); description.append(" and wishes to recieve email questions"); } if (!email.getEmailQuestions() && !email2.getEmailQuestions()) { // Get only no EmailQuestions people emailSearch.append(" AND email.EmailQuestions = false"); description.append(" and does not wish to recieve email questions"); } if (StringUtils.isNotBlank(email.getEmail())) { // Do a search for the email address emailSearch.append(" AND email.Email = ?"); description.append(" and has an email address of '" + email.getEmail() + "'"); parameters.add(email.getEmail()); } if (emailSearch.length() > 0) { sqlWHERE.append(" " + getSQL().getValue("person/searchEmail") + emailSearch.toString() + ")"); } } // Check outstanding balances if (searchCriteria.getFinancialSummary() != null) { // Check if an identifier is set - if not do not bother searching. if (searchCriteria.getFinancialSummary().getId() > 0) { final StringBuffer financialSummarySearch = new StringBuffer(); final double closingBalanceA = searchCriteria.getFinancialSummary().getClosingBalance(); if (searchConstraints.getFinancialSummary() != null) { final double closingBalanceB = searchConstraints.getFinancialSummary().getClosingBalance(); financialSummarySearch.append(" WHERE searchindex.IndexType LIKE ?"); parameters.add("Current Balance"); if (closingBalanceA == closingBalanceB) { financialSummarySearch.append(" AND searchindex.CurrencyValue = ?"); description.append( " and has a current balance of '" + Formatter.toCurrency(closingBalanceA) + "'"); parameters.add(closingBalanceA); } if (closingBalanceA < closingBalanceB) { financialSummarySearch.append(" AND searchindex.CurrencyValue " + "BETWEEN ? AND ?"); description.append( " and has a current balance between '" + Formatter.toCurrency(closingBalanceA) + "' and '" + Formatter.toCurrency(closingBalanceB) + "'"); parameters.add(closingBalanceA); parameters.add(closingBalanceB); } if (closingBalanceA > closingBalanceB) { financialSummarySearch.append(" AND searchindex.CurrencyValue " + "BETWEEN ? AND ?"); description.append( " and has a current balance between '" + Formatter.toCurrency(closingBalanceB) + "' and '" + Formatter.toCurrency(closingBalanceA) + "'"); parameters.add(closingBalanceB); parameters.add(closingBalanceA); } } else { financialSummarySearch.append(" AND searchindex.CurrencyValue = ?"); description.append( " and has a current balance of '" + Formatter.toCurrency(closingBalanceA) + "'"); parameters.add(closingBalanceA); } sqlWHERE.append( " " + getSQL().getValue("person/searchBalance") + financialSummarySearch.toString() + ")"); } } // Do a search for organisations if the TreeMap exists if (searchCriteria.getEmployers() != null) { StringBuffer organisationSearch = new StringBuffer(); for (String index : searchCriteria.getEmployers().keySet()) { ItemBean employer = searchCriteria.getEmployers().get(index); if (organisationSearch.length() > 0) { organisationSearch.append(" OR "); } organisationSearch.append("organisation.Name = ?"); description.append(" and has been employed by '" + employer.getName() + "'"); parameters.add(employer.getName()); } if (organisationSearch.length() > 0) { sqlWHERE.append( this.getSQL().getValue("person/searchWorkplace") + " AND (" + organisationSearch + "))"); } } if (searchCriteria.getWorkshops() != null) { StringBuffer workshopSearch = new StringBuffer(); StringBuffer noWorkshopSearch = new StringBuffer(); for (WorkshopBean workshop : searchCriteria.getWorkshops()) { if (workshop.getType() != null) { if (workshop.getType().compareTo("") != 0) { workshopSearch.append(" AND workshoptype.Class = ?"); description.append(" and has been to a '" + workshop.getType() + "' workshop"); parameters.add(workshop.getType()); } } if (workshop.getWorkshopDate() != null) { Date workshopDate = workshop.getWorkshopDate(); Date workshop2Date = null; if (searchConstraints.getWorkshops() != null) { for (WorkshopBean workshop2 : searchConstraints.getWorkshops()) { workshop2Date = workshop2.getWorkshopDate(); } } if (workshop2Date != null) { String fieldA = this.getDf().format(workshopDate); String fieldB = this.getDf().format(workshop2Date); int larger = workshopDate.compareTo(workshop2Date); if (larger > 0) { if (workshop.getMemo().compareTo("Did not attend:") == 0) { // People without workshops or outside of the time workshopSearch.append(" AND (workshop.Date NOT BETWEEN ? AND ? "); workshopSearch.append("OR workshop.Date IS NULL)"); description.append(" and has not been to a workshop between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } else { // Workshop date after Workshop2 date workshopSearch.append(" AND workshop.Date BETWEEN ? AND ?"); description.append(" and has been to a workshop between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } } if (larger < 0) { if (workshop.getMemo().compareTo("Did not attend:") == 0) { // People without workshops or outside of the time workshopSearch.append(" AND (workshop.Date NOT BETWEEN ? AND ? "); workshopSearch.append("OR workshop.Date IS NULL)"); description.append(" and has not been to a workshop between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } else { // Workshop date before Workshop2 date workshopSearch.append(" AND workshop.Date BETWEEN ? AND ?"); description.append(" and has been to a workshop between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } } if (larger == 0) { if (workshop.getMemo().compareTo("Did not attend:") == 0) { // Workshop not on the date specified String field = this.getDf().format(workshop.getWorkshopDate()); workshopSearch.append(" AND (workshop.Date IS NULL OR " + "workshop.Date != ?)"); description.append(" and has not been to a workshop on '" + field + "'"); parameters.add(field); } else { // Workshop and Workshop2 are equal String field = this.getDf().format(workshop.getWorkshopDate()); workshopSearch.append(" AND workshop.Date = ?"); description.append(" and has been to a workshop on '" + field + "'"); parameters.add(field); } } } else { if (workshop.getMemo() != null) { if (workshop.getMemo().compareTo("Did not attend:") == 0) { // Workshop Date on the date specified String field = this.getDf().format(workshop.getWorkshopDate()); workshopSearch.append(" AND (workshop.Date IS NULL " + "OR workshop.Date != ?)"); description.append(" and has not been to a workshop on '" + field + "'"); parameters.add(field); } else { // Workshop Date on the date specified String field = this.getDf().format(workshop.getWorkshopDate()); workshopSearch.append(" AND workshop.Date = ?"); description.append(" and has been to a workshop on '" + field + "'"); parameters.add(field); } } } } if (workshop.getMemo() != null) { if (workshop.getMemo().compareTo("Has attended any") == 0) { noWorkshopSearch.append(" AND workshop.Date IS NOT NULL"); description.append(" and has attended any workshop"); } if (workshop.getMemo().compareTo("Has never attended any") == 0) { noWorkshopSearch.append(" AND workshop.Date IS NULL"); description.append(" and has never attended any workshops"); } } } if (workshopSearch.length() > 0) { sqlWHERE.append( " " + this.getSQL().getValue("person/searchWorkshop") + workshopSearch.toString() + ")"); } if (noWorkshopSearch.length() > 0) { sqlWHERE.append(" " + this.getSQL().getValue("person/searchNoWorkshop") + noWorkshopSearch.toString() + ")"); } } if (searchCriteria.getExams() != null) { StringBuffer examSearch = new StringBuffer(); for (ExamBean examCriteria : searchCriteria.getExams()) { if (examCriteria.getType() != null) { if (examCriteria.getType().compareTo("") != 0) { examSearch.append(" AND examtype.Class = ?"); description .append(" and has or is intending to undertake a '" + examCriteria.getType() + "'"); parameters.add(examCriteria.getType()); } } if (examCriteria.getStatus() != null) { if (examCriteria.getStatus().compareTo("") != 0) { examSearch.append(" AND examstatus.Class = ?"); description.append(" and the person '" + examCriteria.getStatus() + "' their exam"); parameters.add(examCriteria.getStatus()); } } if (examCriteria.getStatusLevel() != null) { if (examCriteria.getStatusLevel().compareTo("") != 0) { examSearch.append(" AND examstatus.Name = ?"); description.append( " and their exam fell within the '" + examCriteria.getStatusLevel() + "' band"); parameters.add(examCriteria.getStatusLevel()); } } if (examCriteria.getDateSat() != null) { Date examCriteriaDate = examCriteria.getDateSat(); Date examConstraintDate = null; if (searchConstraints.getExams() != null) { for (ExamBean examConstraint : searchConstraints.getExams()) { examConstraintDate = examConstraint.getDateSat(); } } if (examConstraintDate != null) { int larger = examCriteriaDate.compareTo(examConstraintDate); if (larger > 0) { // Exam Criteria date after Exam Constraint date String fieldA = this.getDf().format(examCriteriaDate); String fieldB = this.getDf().format(examConstraintDate); examSearch.append(" AND exam.DateSat BETWEEN ? AND ?"); description.append(" and sat an exam between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // Exam Criteria date before Exam Constraint date String fieldA = this.getDf().format(examCriteriaDate); String fieldB = this.getDf().format(examConstraintDate); examSearch.append(" AND exam.DateSat BETWEEN ? AND ?"); description.append(" and sat an exam between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // Exam on the date specified String field = this.getDf().format(examCriteriaDate); examSearch.append(" AND exam.DateSat = ?"); description.append("and sat an exam on '" + field + "'"); parameters.add(field); } } else { // Exam on the date specified String field = this.getDf().format(examCriteriaDate); examSearch.append(" AND exam.DateSat = ?"); description.append("and sat an exam on '" + field + "'"); parameters.add(field); } } } if (examSearch.length() > 0) { sqlWHERE.append(" " + this.getSQL().getValue("person/searchExam") + examSearch.toString() + ")"); } } if (searchCriteria.getQualifications() != null) { // Perform a qualification search StringBuffer qualificationSearch = new StringBuffer(); for (QualificationBean qualification : searchCriteria.getQualifications()) { if (qualification.getQualificationType() != null) { if (qualification.getQualificationType().compareTo("") != 0) { qualificationSearch.append(" AND qualificationtype.Class = ?"); description .append(" and has a '" + qualification.getQualificationType() + "' qualification"); parameters.add(qualification.getQualificationType()); } } if (qualification.getQualificationSubType() != null) { if (qualification.getQualificationSubType().compareTo("") != 0) { qualificationSearch.append(" AND qualificationtype.Name = ?"); description.append(" and has a qualification type of '" + qualification.getQualificationSubType() + "'"); parameters.add(qualification.getQualificationSubType()); } } if (qualification.getInstitution() != null) { if (qualification.getInstitution().compareTo("") != 0) { qualificationSearch.append(" AND qualification.Institution LIKE ?"); description.append( " and the qualification was awarded by '" + qualification.getInstitution() + "'"); parameters.add("%" + qualification.getInstitution() + "%"); } } if (qualification.getCountry() != null) { if (qualification.getCountry().compareTo("") != 0) { qualificationSearch.append(" AND qualification.Country LIKE ?"); description.append( " and the qualification was awarded in '" + qualification.getCountry() + "'"); parameters.add(qualification.getCountry() + "%"); } } if (qualification.getYear() > 0) { int qualificationCriteriaYear = qualification.getYear(); int qualificationConstraintYear = 0; if (searchConstraints.getQualifications() != null) { for (QualificationBean qualification2 : searchConstraints.getQualifications()) { qualificationConstraintYear = qualification2.getYear(); } } if (qualificationConstraintYear > 0) { if (qualificationConstraintYear > qualificationCriteriaYear) { // Qualification Criteria date after Qualification // Constraint date qualificationSearch.append(" AND qualification.Year BETWEEN ? AND ?"); description.append(" and the qualification was attained " + "between '" + qualificationCriteriaYear + "' and '" + qualificationConstraintYear + "'"); parameters.add(qualificationCriteriaYear); parameters.add(qualificationConstraintYear); } if (qualificationConstraintYear < qualificationCriteriaYear) { // Qualification Criteria date before Qualification // Constraint date qualificationSearch.append(" AND qualification.Year BETWEEN ? AND ?"); description.append(" and the qualification was attained " + "between '" + qualificationCriteriaYear + "' and '" + qualificationConstraintYear + "'"); parameters.add(qualificationConstraintYear); parameters.add(qualificationCriteriaYear); } if (qualificationConstraintYear == qualificationCriteriaYear) { // Qualification on the year specified qualificationSearch.append(" AND qualification.Year = ?"); description.append( "and qualification was attained on '" + qualificationCriteriaYear + "'"); parameters.add(qualificationCriteriaYear); } } else { // Exam on the date specified qualificationSearch.append(" AND qualification.Year = ?"); description.append( "and the qualification was attained on '" + qualificationCriteriaYear + "'"); parameters.add(qualificationCriteriaYear); } } } if (qualificationSearch.length() > 0) { sqlWHERE.append(" " + this.getSQL().getValue("person/searchQualification") + qualificationSearch.toString() + ")"); } } // Perform accreditation search if (searchCriteria.getTrainingSummary("Search") != null) { for (String index : searchCriteria.getTrainingSummary("Search").keySet()) { AccreditationBean[] details = searchCriteria.getTrainingSummary("Search").get(index); AccreditationBean accredA = null; AccreditationBean accredB = null; try { accredA = details[0]; } catch (Exception e) { dataLogger.error("Error casting summary (accredA): " + e.getMessage()); } try { accredB = details[1]; } catch (Exception e) { dataLogger.error("Error casting summary (accredB): " + e.getMessage()); } if (accredA != null && accredB != null) { StringBuffer accreditationSearch = new StringBuffer(); if (StringUtils.isNotBlank(accredA.getAbbreviation())) { description.append(" and has an accreditation type of '" + accredA.getAbbreviation() + "'"); parameters.add(accredA.getAbbreviation()); } else { parameters.add(""); } String searchIndex1 = " AND searchindex.ObjectTypeId1 = 0"; if (StringUtils.isNotBlank(accredA.getAccreditationClass())) { searchIndex1 = " AND searchtype1.Class = ?"; description.append( " and has an accreditation division of '" + accredA.getAccreditationClass() + "'"); parameters.add(accredA.getAccreditationClass()); } String searchIndex2 = " AND searchindex.ObjectTypeId2 = 0"; if (StringUtils.isNotBlank(accredA.getSpecialtyType())) { searchIndex2 = " AND searchtype2.Class = ?"; description .append(" and has an accreditation type of '" + accredA.getSpecialtyType() + "'"); parameters.add(accredA.getSpecialtyType()); } if (accredA.getWeeksCertified() == accredB.getWeeksCertified()) { // Equal to accreditationSearch.append(" WHERE (searchindex.IndexType = ? " + searchIndex1 + searchIndex2 + " AND searchindex.NumericValue = ?)"); description .append(" and has been accredited for " + accredA.getWeeksCertified() + " weeks"); parameters.add(accredA.getWeeksCertified()); } else { // Range accreditationSearch.append(" WHERE (searchindex.IndexType = ? " + searchIndex1 + searchIndex2 + " AND searchindex.NumericValue BETWEEN ? AND ?)"); description.append(" and has been accredited between " + accredA.getWeeksCertified() + " and " + accredB.getWeeksCertified() + " weeks"); parameters.add(accredA.getWeeksCertified()); parameters.add(accredB.getWeeksCertified()); } if (accreditationSearch.length() > 0) { sqlWHERE.append(" " + getSQL().getValue("person/searchAccreditation") + accreditationSearch.toString() + ")"); } } } } if (searchCriteria.getRotations() != null) { HashMap<Integer, Integer> guids = new HashMap<Integer, Integer>(); for (RotationBean rotation : searchCriteria.getRotations()) { if (rotation.getGUID() > 0) { try { RotationBean loaded = this.rotationDAO.loadGUID(rotation.getGUID()); guids.put(loaded.getPersonId(), 0); if (loaded.getSupervisors() != null) { for (SupervisorBean sup : loaded.getSupervisors()) { if (sup != null && sup.getPersonGUID() > 0) { guids.put(sup.getPersonGUID(), 0); } } } } catch (WhichDoctorDaoException wde) { // Error loading rotation dataLogger.error("Error loading rotation: " + wde.getMessage()); } } } if (guids.size() > 0) { StringBuffer rotationWHERE = new StringBuffer(); for (Integer guid : guids.keySet()) { if (rotationWHERE.length() > 0) { rotationWHERE.append(" OR "); } rotationWHERE.append("people.GUID = ?"); parameters.add(guid); } sqlWHERE.append(" AND ("); sqlWHERE.append(rotationWHERE.toString()); sqlWHERE.append(")"); description.append(" and was associated with a rotation"); } } // Perform a specialty search if (searchCriteria.getSpecialtyList() != null) { StringBuffer specialtySearch = new StringBuffer(); for (SpecialtyBean specialty : searchCriteria.getSpecialtyList()) { if (StringUtils.isNotBlank(specialty.getTrainingOrganisation())) { specialtySearch.append(" AND trainingprogram.Class = ?"); description.append(" and is training, or has trained, for a " + "specialty organised by the '" + specialty.getTrainingOrganisation() + "'"); parameters.add(specialty.getTrainingOrganisation()); } if (StringUtils.isNotBlank(specialty.getTrainingProgram())) { specialtySearch.append(" AND trainingprogram.Name = ?"); description.append(" and is training, or has trained, in the '" + specialty.getTrainingProgram() + "' specialty"); parameters.add(specialty.getTrainingProgram()); } if (specialty.getTrainingProgramYear() > 0) { int trainingProgramYearA = specialty.getTrainingProgramYear(); int trainingProgramYearB = 0; for (SpecialtyBean spConstnt : searchConstraints.getSpecialtyList()) { trainingProgramYearB = spConstnt.getTrainingProgramYear(); } if (trainingProgramYearB > 0) { if (trainingProgramYearB < trainingProgramYearA) { // Switch the two variables so that the criteria is smaller trainingProgramYearA = trainingProgramYearB; trainingProgramYearB = specialty.getTrainingProgramYear(); } specialtySearch.append(" AND specialty.TrainingProgramYear " + "BETWEEN ? AND ?"); description.append(" and a specialty with a curriculum year " + "between " + trainingProgramYearA + " and " + trainingProgramYearB); parameters.add(trainingProgramYearA); parameters.add(trainingProgramYearB); } else { specialtySearch.append(" AND specialty.TrainingProgramYear = ?"); description.append(" and a specialty with a curriculum year of " + trainingProgramYearA); parameters.add(trainingProgramYearA); } } if (StringUtils.isNotBlank(specialty.getStatus())) { specialtySearch.append(" AND specialtystatus.Class = ?"); description.append(" and has a specialty status of '" + specialty.getStatus() + "'"); parameters.add(specialty.getStatus()); } } if (specialtySearch.length() > 0) { sqlWHERE.append( " " + getSQL().getValue("person/searchSpecialty") + specialtySearch.toString() + ")"); } } // Other searches: cancelled, date issued if (searchCriteria.getCreatedDate() != null) { if (searchConstraints.getCreatedDate() != null) { int larger = searchCriteria.getCreatedDate().compareTo(searchConstraints.getCreatedDate()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getCreatedDate()); String fieldB = this.getDf().format(searchConstraints.getCreatedDate()); description.append(" and was created between '" + fieldB + "' and '" + fieldA + "'"); sqlWHERE.append(" AND guid.CreatedDate BETWEEN ? AND ?"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getCreatedDate()); String fieldB = this.getDf().format(searchConstraints.getCreatedDate()); sqlWHERE.append(" AND guid.CreatedDate BETWEEN ? AND ?"); description.append(" and was created between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCritier and SearchConstraint are equal String field = this.getDf().format(searchCriteria.getCreatedDate()); sqlWHERE.append(" AND guid.CreatedDate = ?"); description.append(" and was created on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(searchCriteria.getCreatedDate()); sqlWHERE.append(" AND guid.CreatedDate = ?"); description.append(" and was created on '" + field + "'"); parameters.add(field); } } if (searchCriteria.getModifiedDate() != null) { if (searchConstraints.getModifiedDate() != null) { int larger = searchCriteria.getModifiedDate().compareTo(searchConstraints.getModifiedDate()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getModifiedDate()); String fieldB = this.getDf().format(searchConstraints.getModifiedDate()); sqlWHERE.append(" AND guid.ModifiedDate BETWEEN ? AND ?"); description.append(" and was modified between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getModifiedDate()); String fieldB = this.getDf().format(searchConstraints.getModifiedDate()); sqlWHERE.append(" AND guid.ModifiedDate BETWEEN ? AND ?"); description.append(" and was modified between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCritier and SearchConstraint are equal String field = this.getDf().format(searchCriteria.getModifiedDate()); sqlWHERE.append(" AND guid.ModifiedDate = ?"); description.append(" and was modified on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(searchCriteria.getModifiedDate()); sqlWHERE.append(" AND guid.ModifiedDate = ?"); description.append(" and was modified on '" + field + "'"); parameters.add(field); } } if (searchCriteria.getIncludeGUIDList() != null) { final StringBuffer guidWHERE = new StringBuffer(); for (String guid : searchCriteria.getIncludeGUIDList()) { if (StringUtils.isNotBlank(guid)) { guidWHERE.append(" OR people.GUID = ?"); parameters.add(guid); } } if (guidWHERE.length() > 0) { // Append the guidWHERE buffer to the sqlWHERE buffer sqlWHERE.append(" OR ("); // Append the guidWHERE but strip the first OR statement sqlWHERE.append(guidWHERE.toString().substring(4)); sqlWHERE.append(")"); description.append(" and has a GUID in the supplied list"); } } String[] index = new String[] { sqlWHERE.toString(), DataFilter.getHtml(description.toString()) }; Map<String[], Collection<Object>> results = new HashMap<String[], Collection<Object>>(); results.put(index, parameters); return results; } /** * Process racp membership. * * @param criteria the criteria * @param constraints the constraints * * @return the object[] */ @SuppressWarnings("unchecked") private Object[] processRACPMembership(final MembershipBean criteria, final MembershipBean constraints) { StringBuffer sqlWHERE = new StringBuffer(); StringBuffer description = new StringBuffer(); Collection<Object> parameters = new ArrayList<Object>(); if (criteria.getIntField("Candidate Number") > 0) { int cnCriteria = criteria.getIntField("Candidate Number"); int cnConstraints = constraints.getIntField("Candidate Number"); if (cnCriteria > 0) { boolean maximum = false; boolean minimum = false; boolean range = false; boolean single = true; if (cnConstraints < 0) { minimum = true; single = false; } if (cnConstraints > 0) { range = true; single = false; } if (cnConstraints == 999999999) { maximum = true; range = false; single = false; } if (minimum) { // Get values less than supplied Candidate Number sqlWHERE.append(" AND " + criteria.getDataTableName("Candidate Number") + "." + criteria.getDataFieldName("Candidate Number") + " <= ?"); description.append(" and a candidate number less than '" + cnCriteria + "'"); parameters.add(cnCriteria); } if (maximum) { maximum = true; // Get values greater than supplied Candidate Number sqlWHERE.append(" AND " + criteria.getDataTableName("Candidate Number") + "." + criteria.getDataFieldName("Candidate Number") + " >= ?"); description.append(" and a candidate number greater than '" + cnCriteria + "'"); parameters.add(cnCriteria); } if (range) { // Search Constraints between A and B if (cnCriteria > cnConstraints) { // A greater than B sqlWHERE.append(" AND " + criteria.getDataTableName("Candidate Number") + "." + criteria.getDataFieldName("Candidate Number") + " BETWEEN ? AND ?"); description.append( " and a candidate number between '" + cnConstraints + "' and '" + cnCriteria + "'"); parameters.add(cnConstraints); parameters.add(cnCriteria); } else if (cnCriteria < cnConstraints) { // B greater than A sqlWHERE.append(" AND " + criteria.getDataTableName("Candidate Number") + "." + criteria.getDataFieldName("Candidate Number") + " BETWEEN ? AND ?"); description.append( " and a candidate number between '" + cnCriteria + "' and '" + cnConstraints + "'"); parameters.add(cnCriteria); parameters.add(cnConstraints); } else { // A = B sqlWHERE.append(" AND " + criteria.getDataTableName("Candidate Number") + "." + criteria.getDataFieldName("Candidate Number") + " = ?"); description.append(" and a candidate number equal to '" + cnCriteria + "'"); parameters.add(cnCriteria); } } if (single) { sqlWHERE.append(" AND " + criteria.getDataTableName("Candidate Number") + "." + criteria.getDataFieldName("Candidate Number") + " = ?"); description.append(" and a candidate number equal to '" + cnCriteria + "'"); parameters.add(cnCriteria); } } } String[] objectArray = { "Training Type", "Status", "Membership Type", "Division", "Region Representative" }; for (int i = 0; i < objectArray.length; i++) { String fieldName = objectArray[i]; Object[] results = processMembershipObjectType(criteria, fieldName); String sql = (String) results[0]; String desc = (String) results[1]; Collection<Object> param = (Collection<Object>) results[2]; if (StringUtils.isNotBlank(sql)) { sqlWHERE.append(sql); description.append(desc); parameters.addAll(param); } } if (StringUtils.isNotBlank(criteria.getField("Supervisor Status"))) { // Only current supervisors sqlWHERE.append(" AND " + criteria.getDataTableName("Supervisor Status") + "." + criteria.getDataFieldName("Supervisor Status") + " LIKE ?"); description.append(" and a Supervisor Status of '" + criteria.getField("Supervisor Status") + "'"); parameters.add(criteria.getField("Supervisor Status")); } if (StringUtils.equalsIgnoreCase(criteria.getField("Financial Excemption"), "Yes")) { // Only financial excemption sqlWHERE.append(" AND " + criteria.getDataTableName("Financial Excemption") + "." + criteria.getDataFieldName("Financial Excemption") + " = ?"); description.append(" and has a financial excemption"); parameters.add("Yes"); } if (StringUtils.equalsIgnoreCase(constraints.getField("Financial Excemption"), "No")) { // Only non-financial excemption sqlWHERE.append(" AND " + criteria.getDataTableName("Financial Excemption") + "." + criteria.getDataFieldName("Financial Excemption") + " = ?"); description.append(" and does not have a financial excemption"); parameters.add("No"); } // Search for date joined if (criteria.getJoinedDate() != null) { if (constraints.getJoinedDate() != null) { int larger = criteria.getJoinedDate().compareTo(constraints.getJoinedDate()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = this.getDf().format(criteria.getJoinedDate()); String fieldB = this.getDf().format(constraints.getJoinedDate()); sqlWHERE.append(" AND membership.JoinedDate BETWEEN ? AND ?"); description.append(" and a registration date between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = this.getDf().format(criteria.getJoinedDate()); String fieldB = this.getDf().format(constraints.getJoinedDate()); sqlWHERE.append(" AND membership.JoinedDate BETWEEN ? AND ?"); description.append(" and a registration date between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCriteria and SearchConstraint are equal String field = this.getDf().format(criteria.getJoinedDate()); sqlWHERE.append(" AND membership.JoinedDate = ?"); description.append(" and a registration date on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(criteria.getJoinedDate()); sqlWHERE.append(" AND membership.JoinedDate = ?"); description.append(" and a registration date on '" + field + "'"); parameters.add(field); } } // Search for date left if (criteria.getLeftDate() != null) { if (constraints.getLeftDate() != null) { int larger = criteria.getLeftDate().compareTo(constraints.getLeftDate()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = this.getDf().format(criteria.getLeftDate()); String fieldB = this.getDf().format(constraints.getLeftDate()); sqlWHERE.append(" AND membership.LeftDate BETWEEN ? AND ?"); description.append(" and a retirement date between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = this.getDf().format(criteria.getLeftDate()); String fieldB = this.getDf().format(constraints.getLeftDate()); sqlWHERE.append(" AND membership.LeftDate BETWEEN ? AND ?"); description.append(" and a retirement date between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCriteria and SearchConstraint are equal String field = this.getDf().format(criteria.getLeftDate()); sqlWHERE.append(" AND membership.LeftDate = ?"); description.append(" and a retirement date on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(criteria.getLeftDate()); sqlWHERE.append(" AND membership.LeftDate = ?"); description.append(" and a retirement date on '" + field + "'"); parameters.add(field); } } return new Object[] { sqlWHERE.toString(), description.toString(), parameters }; } /** * Process racp fellowship. * * @param criteria the criteria * @param constraints the constraints * * @return the object[] */ private Object[] processRACPFellowship(final MembershipBean criteria, final MembershipBean constraints) { StringBuffer sqlWHERE = new StringBuffer(); StringBuffer description = new StringBuffer(); Collection<Object> parameters = new ArrayList<Object>(); if (criteria.getIntField("FRACP") > 0) { final int fracpCriteria = criteria.getIntField("FRACP"); final int fracpConstraints = constraints.getIntField("FRACP"); if (fracpCriteria > 0) { boolean maximum = false; boolean minimum = false; boolean range = false; boolean single = true; if (fracpConstraints < 0) { minimum = true; single = false; } if (fracpConstraints > 0) { range = true; single = false; } if (fracpConstraints == 999999999) { maximum = true; range = false; single = false; } if (minimum) { // Get values less than supplied FRACP sqlWHERE.append(" AND " + criteria.getDataTableName("FRACP") + "." + criteria.getDataFieldName("FRACP") + " <= ?"); description.append(" and a FRACP less than '" + fracpCriteria + "'"); parameters.add(fracpCriteria); } if (maximum) { maximum = true; // Get values greater than supplied FRACP sqlWHERE.append(" AND " + criteria.getDataTableName("FRACP") + "." + criteria.getDataFieldName("FRACP") + " >= ?"); description.append(" and a FRACP greater than '" + fracpCriteria + "'"); parameters.add(fracpCriteria); } if (range) { // Search Constraints between A and B if (fracpCriteria > fracpConstraints) { // A greater than B sqlWHERE.append(" AND " + criteria.getDataTableName("FRACP") + "." + criteria.getDataFieldName("FRACP") + " BETWEEN ? AND ?"); description.append( " and a FRACP between '" + fracpConstraints + "' and '" + fracpCriteria + "'"); parameters.add(fracpConstraints); parameters.add(fracpCriteria); } else if (fracpCriteria < fracpConstraints) { // B greater than A sqlWHERE.append(" AND " + criteria.getDataTableName("FRACP") + "." + criteria.getDataFieldName("FRACP") + " BETWEEN ? AND ?"); description.append( " and a FRACP between '" + fracpCriteria + "' and '" + fracpConstraints + "'"); parameters.add(fracpCriteria); parameters.add(fracpConstraints); } else { // A = B sqlWHERE.append(" AND " + criteria.getDataTableName("FRACP") + "." + criteria.getDataFieldName("FRACP") + " = ?"); description.append(" and a FRACP equal to '" + fracpCriteria + "'"); parameters.add(fracpCriteria); } } if (single) { sqlWHERE.append(" AND " + criteria.getDataTableName("FRACP") + "." + criteria.getDataFieldName("FRACP") + " = ?"); description.append(" and a FRACP equal to '" + fracpCriteria + "'"); parameters.add(fracpCriteria); } } } // Search for date of Fellowship if (StringUtils.isNotBlank(criteria.getField("Fellowship Date"))) { Date fellowshipCriteria = criteria.getDateField("Fellowship Date"); Date fellowshipConstraints = constraints.getDateField("Fellowship Date"); if (fellowshipCriteria != null) { if (fellowshipConstraints != null) { int larger = fellowshipCriteria.compareTo(fellowshipConstraints); if (larger > 0) { // SearchCriteria date after SearchConstraint // date String fieldA = this.getDf().format(fellowshipCriteria); String fieldB = this.getDf().format(fellowshipConstraints); sqlWHERE.append(" AND " + criteria.getDataTableName("Fellowship Date") + "." + criteria.getDataFieldName("Fellowship Date") + " BETWEEN ? AND ?"); description.append(" and a Fellowship date between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint // date String fieldA = this.getDf().format(fellowshipCriteria); String fieldB = this.getDf().format(fellowshipConstraints); sqlWHERE.append(" AND " + criteria.getDataTableName("Fellowship Date") + "." + criteria.getDataFieldName("Fellowship Date") + " BETWEEN ? AND ?"); description.append(" and a Fellowship date between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCritier and SearchConstraint are equal String field = this.getDf().format(fellowshipCriteria); sqlWHERE.append(" AND " + criteria.getDataTableName("Fellowship Date") + "." + criteria.getDataFieldName("Fellowship Date") + " = ?"); description.append(" and a Fellowship date on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(fellowshipCriteria); sqlWHERE.append(" AND " + criteria.getDataTableName("Fellowship Date") + "." + criteria.getDataFieldName("Fellowship Date") + " = ?"); description.append(" and a Fellowship date on '" + field + "'"); parameters.add(field); } } } String[] objectArray = { "Admitting SAC" }; for (int i = 0; i < objectArray.length; i++) { String fieldName = objectArray[i]; if (criteria.getField(fieldName).compareTo("") != 0) { ObjectTypeBean object = criteria.getObjectTypeField(fieldName); if (object.getClassName() != null) { if (object.getClassName().compareTo("") != 0) { sqlWHERE.append(" AND " + criteria.getDataTableName(fieldName) + ".Class = ?"); description.append(" and a " + fieldName + " of '" + object.getClassName() + "'"); parameters.add(object.getClassName()); } } if (object.getName() != null) { if (object.getName().compareTo("") != 0) { sqlWHERE.append(" AND " + criteria.getDataTableName(fieldName) + ".Name = ?"); description.append(" and a sub-" + fieldName + " of '" + object.getName() + "'"); parameters.add(object.getName()); } } } } if (StringUtils.isNotBlank(criteria.getField("Admitting Country"))) { sqlWHERE.append(" AND " + criteria.getDataTableName("Admitting Country") + "." + criteria.getDataFieldName("Admitting Country") + " = ?"); description .append(" and was admitted to Fellowship by '" + criteria.getField("Admitting Country") + "'"); parameters.add(criteria.getField("Admitting Country")); } return new Object[] { sqlWHERE.toString(), description.toString(), parameters }; } /** * Process racp affiliation. * * @param criteria the criteria * @param constraints the constraints * * @return the object[] */ private Object[] processRACPAffiliation(final MembershipBean criteria, final MembershipBean constraints) { StringBuffer sqlWHERE = new StringBuffer(); StringBuffer description = new StringBuffer(); Collection<Object> parameters = new ArrayList<Object>(); String[] objectArray = { "Affiliation" }; for (int i = 0; i < objectArray.length; i++) { String fieldName = objectArray[i]; if (criteria.getField(fieldName).compareTo("") != 0) { ObjectTypeBean object = criteria.getObjectTypeField(fieldName); if (object.getClassName() != null) { if (object.getClassName().compareTo("") != 0) { sqlWHERE.append(" AND " + criteria.getDataTableName(fieldName) + ".Class = ?"); description.append(" and a " + fieldName + " of '" + object.getClassName() + "'"); parameters.add(object.getClassName()); } } if (object.getName() != null) { if (object.getName().compareTo("") != 0) { sqlWHERE.append(" AND " + criteria.getDataTableName(fieldName) + ".Name = ?"); description.append(" and a " + fieldName + " of '" + object.getName() + "'"); parameters.add(object.getName()); } } } } return new Object[] { sqlWHERE.toString(), description.toString(), parameters }; } /** * Process membership object type. * * @param criteria the criteria * @param fieldName the field name * * @return the object[] */ private Object[] processMembershipObjectType(final MembershipBean criteria, final String fieldName) { StringBuffer sqlWHERE = new StringBuffer(); StringBuffer description = new StringBuffer(); Collection<Object> parameters = new ArrayList<Object>(); if (criteria.getField(fieldName).compareTo("") != 0) { ObjectTypeBean object = criteria.getObjectTypeField(fieldName); if (StringUtils.isNotBlank(object.getClassName())) { sqlWHERE.append(" AND " + criteria.getDataTableName(fieldName) + ".Class = ?"); description.append(" and a " + fieldName + " of '" + object.getClassName() + "'"); parameters.add(object.getClassName()); } if (StringUtils.isNotBlank(object.getName())) { sqlWHERE.append(" AND " + criteria.getDataTableName(fieldName) + ".Name = ?"); description.append(" and a sub-" + fieldName + " of '" + object.getName() + "'"); parameters.add(object.getName()); } } return new Object[] { sqlWHERE.toString(), description.toString(), parameters }; } }