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.UserBean; import com.sfs.beans.ObjectTypeBean; import com.sfs.whichdoctor.beans.AccreditationBean; import com.sfs.whichdoctor.beans.AddressBean; import com.sfs.whichdoctor.beans.AssessmentBean; import com.sfs.whichdoctor.beans.MembershipBean; import com.sfs.whichdoctor.beans.OrganisationBean; import com.sfs.whichdoctor.beans.PersonBean; 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.dao.RotationDAO; import com.sfs.whichdoctor.search.TagSearchDAO; import java.util.ArrayList; import java.util.Collection; 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 RotationSqlHandler. * * @author David Harrison */ public class RotationSqlHandler extends SqlHandlerBase { /** The data logger. */ private static Logger dataLogger = Logger.getLogger(RotationSqlHandler.class); /** The Constant LIMIT. */ private static final int LIMIT = 20; /** The tag search dao. */ @Resource private TagSearchDAO tagSearchDAO; /** The rotation dao. */ @Resource private RotationDAO rotationDAO; /** * Instantiates a new rotation sql handler. */ public RotationSqlHandler() { super(); this.setType("rotation"); this.setIdentifierColumn("rotation.GUID"); this.setDefaultOrder("rotation.StartDate"); } /** * 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(); RotationBean searchCriteria = new RotationBean(); RotationBean searchConstraints = new RotationBean(); search.setRequestedPage(1); search.setOrderColumn("rotation.StartDate"); search.setOrderColumn2("people.LastName"); search.setOrderColumn3("rotation.Description"); search.setLimit(LIMIT); search.setOrderAscending(true); search.setType("rotation"); 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 rotation.GUID) " + this.getSQL().getValue("rotation/search"); } /** * Gets the select sql. * * @return the select sql */ public final String getSelectSql() { return "SELECT DISTINCT rotation.GUID " + getSQL().getValue("rotation/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 { RotationBean rotation = this.rotationDAO.loadGUID(uniqueId, loadDetails); results.add(rotation); } catch (Exception e) { dataLogger.error("Error loading rotation (" + 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 */ public final Map<String[], Collection<Object>> construct(final Object objCriteria, final Object objConstraints) throws IllegalArgumentException { RotationBean searchCriteria = null; RotationBean searchConstraints = null; if (objCriteria instanceof RotationBean) { searchCriteria = (RotationBean) objCriteria; } if (objConstraints instanceof RotationBean) { searchConstraints = (RotationBean) objConstraints; } if (searchCriteria == null) { throw new IllegalArgumentException("The search criteria must be a valid RotationBean"); } if (searchConstraints == null) { throw new IllegalArgumentException("The search constraints must be a valid RotationBean"); } 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 (StringUtils.isNotBlank(tagWHERE)) { // A WHERE condition is defined, add to the SQL WHERE clause sqlWHERE.append(" " + this.getSQL().getValue("rotation/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()); } } /* * Used to locate a rotation based on a perons name or rotation * description */ 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); } } if (searchCriteria.getGUIDList() != null) { final StringBuffer guidWHERE = new StringBuffer(); for (String guid : searchCriteria.getGUIDList()) { if (StringUtils.isNotBlank(guid)) { guidWHERE.append(" OR rotation.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 is a rotation associated with a MIN number " + "in the supplied list"); } } // Build name field if (StringUtils.isNotBlank(searchCriteria.getDescription())) { sqlWHERE.append(" AND rotation.Description LIKE ?"); description.append(" and a rotation description like '" + searchCriteria.getDescription() + "'"); parameters.add("%" + searchCriteria.getDescription() + "%"); } if (StringUtils.isNotBlank(searchCriteria.getRotationType())) { String field = searchCriteria.getRotationType(); sqlWHERE.append(" AND rotationtype.Class LIKE ?"); description.append(" and a training level of '" + searchCriteria.getRotationType() + "'"); parameters.add(field); } if (StringUtils.isNotBlank(searchCriteria.getTrainingClass())) { String field = searchCriteria.getTrainingClass(); sqlWHERE.append(" AND trainingtype.Class LIKE ?"); description.append(" and a rotation training class of '" + searchCriteria.getTrainingClass() + "'"); parameters.add(field); } if (StringUtils.isNotBlank(searchCriteria.getTrainingType())) { String field = searchCriteria.getTrainingType(); sqlWHERE.append(" AND trainingtype.Name LIKE ?"); description.append(" and a rotation training type of '" + searchCriteria.getTrainingType() + "'"); parameters.add(field); } if (searchCriteria.getPersonId() > 0) { sqlWHERE.append(" AND rotation.PersonId = ?"); description.append(" and has a person GUID equal to '" + searchCriteria.getPersonId() + "'"); parameters.add(searchCriteria.getPersonId()); } if (searchCriteria.getYear() > 0) { if (searchConstraints.getYear() > 0) { int yearA = searchCriteria.getYear(); int yearB = searchConstraints.getYear(); if (yearB < yearA) { // Switch the two variables so that the criteria is smaller yearA = searchConstraints.getYear(); yearB = searchCriteria.getYear(); } sqlWHERE.append(" AND rotation.Year BETWEEN ? AND ?"); description.append(" and a rotation training year between " + yearA + " and " + yearB); parameters.add(yearA); parameters.add(yearB); } else { sqlWHERE.append(" AND rotation.Year = ?"); description.append(" and a rotation training year of " + searchCriteria.getYear()); parameters.add(searchCriteria.getYear()); } } if (searchCriteria.getOrganisation1() != null) { StringBuffer organisationSearch = new StringBuffer(); OrganisationBean organisation = searchCriteria.getOrganisation1(); if (StringUtils.isNotBlank(organisation.getName())) { description.append(" and a organisation named '" + organisation.getName() + "'"); organisationSearch.append(" AND (first_organisation.Name LIKE ? OR " + "first_organisation.Name LIKE ? OR rotation.Organisation1Name " + "LIKE ? OR rotation.Organisation2Name LIKE ?)"); for (int i = 0; i < 4; i++) { parameters.add("%" + organisation.getName() + "%"); } } if (organisation.getFirstAddress() != null) { AddressBean address = organisation.getFirstAddress(); if (StringUtils.isNotBlank(address.getCity())) { description.append(" and an organisation based in the city of '" + address.getCity() + "'"); organisationSearch .append(" AND (first_city.TextValue LIKE ? " + "OR second_city.TextValue LIKE ?)"); for (int i = 0; i < 2; i++) { parameters.add(address.getCity()); } } if (StringUtils.isNotBlank(address.getCountry())) { description.append(" and an organisation based in '" + address.getCountry() + "'"); organisationSearch.append( " AND (first_country.TextValue LIKE ? " + "OR second_country.TextValue LIKE ?)"); for (int i = 0; i < 2; i++) { parameters.add(address.getCountry()); } } } if (organisationSearch.length() > 0) { sqlWHERE.append(" " + getSQL().getValue("rotation/searchOrganisation") + organisationSearch.toString() + ")"); } } // Search for start or end date if (searchCriteria.getStartDate() != null) { if (searchConstraints.getStartDate() != null) { int larger = searchCriteria.getStartDate().compareTo(searchConstraints.getStartDate()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getStartDate()); String fieldB = this.getDf().format(searchConstraints.getStartDate()); sqlWHERE.append(" AND rotation.StartDate BETWEEN ? AND ?"); description.append(" and a start date between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getStartDate()); String fieldB = this.getDf().format(searchConstraints.getStartDate()); sqlWHERE.append(" AND rotation.StartDate BETWEEN ? AND ?"); description.append(" and a start date between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCritier and SearchConstraint are equal String field = this.getDf().format(searchCriteria.getStartDate()); sqlWHERE.append(" AND rotation.StartDate = ?"); description.append(" and a start date on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(searchCriteria.getStartDate()); sqlWHERE.append(" AND rotation.StartDate = ?"); description.append(" and a start date on '" + field + "'"); parameters.add(field); } } if (searchCriteria.getEndDate() != null) { if (searchConstraints.getEndDate() != null) { int larger = searchCriteria.getEndDate().compareTo(searchConstraints.getEndDate()); if (larger > 0) { // SearchCriteria date after SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getEndDate()); String fieldB = this.getDf().format(searchConstraints.getEndDate()); sqlWHERE.append(" AND rotation.EndDate BETWEEN ? AND ?"); description.append(" and a end date between '" + fieldB + "' and '" + fieldA + "'"); parameters.add(fieldB); parameters.add(fieldA); } if (larger < 0) { // SearchCriteria date before SearchConstraint date String fieldA = this.getDf().format(searchCriteria.getEndDate()); String fieldB = this.getDf().format(searchConstraints.getEndDate()); sqlWHERE.append(" AND rotation.EndDate BETWEEN ? AND ?"); description.append(" and a end date between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(fieldA); parameters.add(fieldB); } if (larger == 0) { // SearchCritier and SearchConstraint are equal String field = this.getDf().format(searchCriteria.getEndDate()); sqlWHERE.append(" AND rotation.EndDate = ?"); description.append(" and a end date on '" + field + "'"); parameters.add(field); } } else { String field = this.getDf().format(searchCriteria.getEndDate()); sqlWHERE.append(" AND rotation.EndDate = ?"); description.append(" and a end date on '" + field + "'"); parameters.add(field); } } if (searchCriteria.getTrainingTime() > 0) { if (searchConstraints.getTrainingTime() > 0) { if (searchCriteria.getTrainingTime() > searchConstraints.getTrainingTime()) { // SearchCriteria training time larger than SearchConstraint String fieldA = Formatter.toPercent(searchConstraints.getTrainingTime(), 0, "%"); String fieldB = Formatter.toPercent(searchCriteria.getTrainingTime(), 0, "%"); sqlWHERE.append(" AND rotation.TrainingTime BETWEEN ? AND ?"); description.append(" and a training time between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(searchConstraints.getTrainingTime()); parameters.add(searchCriteria.getTrainingTime()); } if (searchCriteria.getTrainingTime() < searchConstraints.getTrainingTime()) { // SearchCriteria training time less than SearchConstraint String fieldA = Formatter.toPercent(searchCriteria.getTrainingTime(), 0, "%"); String fieldB = Formatter.toPercent(searchConstraints.getTrainingTime(), 0, "%"); sqlWHERE.append(" AND rotation.TrainingTime BETWEEN ? AND ?"); description.append(" and a training time between '" + fieldA + "' and '" + fieldB + "'"); parameters.add(searchCriteria.getTrainingTime()); parameters.add(searchConstraints.getTrainingTime()); } if (searchCriteria.getTrainingTime() == searchConstraints.getTrainingTime()) { // SearchCritier and SearchConstraint are equal String field = Formatter.toPercent(searchCriteria.getTrainingTime(), 0, "%"); sqlWHERE.append(" AND rotation.TrainingTime = ?"); description.append(" and a training time of '" + field + "'"); parameters.add(searchCriteria.getTrainingTime()); } } else { String field = Formatter.toPercent(searchCriteria.getTrainingTime(), 0, "%"); sqlWHERE.append(" AND rotation.TrainingTime = ?"); description.append(" and a training time of '" + field + "'"); parameters.add(searchCriteria.getTrainingTime()); } } if (searchCriteria.getSupervisors() != null) { StringBuffer supervisorSearch = new StringBuffer(); for (SupervisorBean supervisor : searchCriteria.getSupervisors()) { if (supervisor.getPersonGUID() > 0) { if (supervisorSearch.length() > 0) { supervisorSearch.append(" OR "); } supervisorSearch.append("supervisors.PersonGUID = ?"); description.append(" and a supervisor with a GUID of '" + supervisor.getPersonGUID() + "'"); parameters.add(supervisor.getPersonGUID()); } } if (supervisorSearch.length() > 0) { sqlWHERE.append(" " + this.getSQL().getValue("rotation/searchSupervisor") + " AND (" + supervisorSearch + "))"); } } if (searchCriteria.getAccreditation() != null) { StringBuffer accreditationSearch = new StringBuffer(); for (AccreditationBean accreditation : searchCriteria.getAccreditation()) { if (StringUtils.isNotBlank(accreditation.getSpecialtyType())) { accreditationSearch.append(" AND accreditationtype.Name = ?"); description.append(" and an associated accreditation specialty of '" + accreditation.getSpecialtyType() + "'"); parameters.add(accreditation.getSpecialtyType()); } if (StringUtils.isNotBlank(accreditation.getAccreditationType())) { accreditationSearch.append(" AND accreditationtype.Name = ?"); description.append(" and an associated accreditation type of '" + accreditation.getAccreditationType() + "'"); parameters.add(accreditation.getAccreditationType()); } } if (accreditationSearch.length() > 0) { sqlWHERE.append( " " + getSQL().getValue("rotation/searchAccreditation") + accreditationSearch + ")"); } } // Do person name first and last search if (searchCriteria.getPersonSearch() != null) { PersonBean person = searchCriteria.getPersonSearch(); PersonBean personConstraints = searchConstraints.getPersonSearch(); if (personConstraints == null) { personConstraints = new PersonBean(); } if (StringUtils.isNotBlank(person.getFirstName())) { sqlWHERE.append(" AND people.FirstName LIKE ?"); description.append(" and a trainee's first name like '" + person.getFirstName() + "'"); parameters.add("%" + person.getFirstName() + "%"); } if (StringUtils.isNotBlank(person.getLastName())) { sqlWHERE.append(" AND people.LastName LIKE ?"); description.append(" and a trainee's last name like '" + person.getLastName() + "'"); parameters.add("%" + person.getLastName() + "%"); } if (person.getPersonIdentifier() > 0) { sqlWHERE.append(" AND people.PersonIdentifier = ?"); description.append(" and a trainee's with an identifier of '" + person.getPersonIdentifier() + "'"); parameters.add(person.getPersonIdentifier()); } /* Membership search */ if (person.getPrimaryMembership() != null) { MembershipBean mCriteria = person.getPrimaryMembership(); if (StringUtils.isNotBlank(mCriteria.getField("Division"))) { sqlWHERE.append(" AND " + mCriteria.getDataTableName("Division") + ".Class = ?"); description .append(" and a trainee with a division of '" + mCriteria.getField("Division") + "'"); parameters.add(mCriteria.getField("Division")); } if (mCriteria.getObjectTypeField("Training Type") != null) { ObjectTypeBean object = mCriteria.getObjectTypeField("Training Type"); sqlWHERE.append(" AND " + mCriteria.getDataTableName("Training Type") + ".Class = ?"); description.append(" and a trainee with training type of '" + object.getClassName() + "'"); parameters.add(object.getClassName()); } } // Perform a specialty search if (person.getSpecialtyList() != null) { StringBuffer specialtySearch = new StringBuffer(); for (SpecialtyBean specialty : person.getSpecialtyList()) { if (StringUtils.isNotBlank(specialty.getTrainingOrganisation())) { specialtySearch.append(" AND trainingprogram.Class = ?"); description.append(" and is associated with a person who is " + "in the '" + specialty.getTrainingOrganisation() + "' training organisation"); parameters.add(specialty.getTrainingOrganisation()); } if (StringUtils.isNotBlank(specialty.getTrainingProgram())) { specialtySearch.append(" AND trainingprogram.Name = ?"); description.append(" and is associated with a person who is " + "in the '" + specialty.getTrainingProgram() + "' training program"); parameters.add(specialty.getTrainingProgram()); } if (specialty.getTrainingProgramYear() > 0) { int trainingProgramYearA = specialty.getTrainingProgramYear(); int trainingProgramYearB = 0; for (SpecialtyBean spConstnt : personConstraints.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 is associated with a person whose " + "specialty status is '" + specialty.getStatus() + "'"); parameters.add(specialty.getStatus()); } } if (specialtySearch.length() > 0) { sqlWHERE.append( " " + getSQL().getValue("rotation/searchSpecialty") + specialtySearch.toString() + ")"); } } } if (searchCriteria.getAssessment() != null) { StringBuffer assessmentSearch = new StringBuffer(); for (AssessmentBean assessment : searchCriteria.getAssessment()) { if (StringUtils.isNotBlank(assessment.getTrainingOrganisation())) { assessmentSearch.append(" AND trainingprogram.Class = ?"); description.append(" and an assessment training organisation of '" + assessment.getTrainingOrganisation() + "'"); parameters.add(assessment.getTrainingOrganisation()); } if (StringUtils.isNotBlank(assessment.getTrainingProgram())) { assessmentSearch.append(" AND trainingprogram.Name = ?"); description.append( " and an assessment training program of '" + assessment.getTrainingProgram() + "'"); parameters.add(assessment.getTrainingProgram()); } if (StringUtils.isNotBlank(assessment.getCommitteeSpecialty())) { assessmentSearch.append(" AND assessment.SpecialtyType LIKE ?"); description .append(" and an assessment committee of '" + assessment.getCommitteeSpecialty() + "'"); parameters.add(assessment.getCommitteeSpecialty()); } if (StringUtils.isNotBlank(assessment.getApproved())) { assessmentSearch.append(" AND approval.Class = ?"); description.append(" and an approved status of '" + assessment.getApproved() + "'"); parameters.add(assessment.getApproved()); } if (StringUtils.isNotBlank(assessment.getApprovedCondition())) { assessmentSearch.append(" AND approval.Name = ?"); description.append(" and an approved condition of '" + assessment.getApprovedCondition() + "'"); parameters.add(assessment.getApprovedCondition()); } if (StringUtils.isNotBlank(assessment.getStatus())) { assessmentSearch.append(" AND rotationstatus.Class = ?"); description.append(" and a rotation status of '" + assessment.getStatus() + "'"); parameters.add(assessment.getStatus()); } if (StringUtils.isNotBlank(assessment.getStatusReason())) { assessmentSearch.append(" AND rotationstatus.Name = ?"); description.append(" and a rotation status reason of '" + assessment.getStatusReason() + "'"); parameters.add(assessment.getStatusReason()); } } if (assessmentSearch.length() > 0) { sqlWHERE.append( " " + getSQL().getValue("rotation/searchAssessment") + assessmentSearch.toString() + ")"); } } /* * Build SQL array based on People GUIDs - used in training summary * search */ if (searchCriteria.getPeopleGUIDs() != null) { StringBuffer peopleWHERE = new StringBuffer(); for (Integer guid : searchCriteria.getPeopleGUIDs()) { if (peopleWHERE.length() > 0) { peopleWHERE.append(" OR "); } peopleWHERE.append("rotation.PersonId = ?"); parameters.add(guid); } if (peopleWHERE.length() > 0) { sqlWHERE.append(" AND (" + peopleWHERE.toString() + ")"); } } /* Build SQL array based on list of types */ if (searchCriteria.getSummaryTypes() != null) { StringBuffer summaryWHERE = new StringBuffer(); for (String summaryType : searchCriteria.getSummaryTypes()) { if (StringUtils.isNotBlank(summaryType)) { if (summaryWHERE.length() > 0) { summaryWHERE.append(" OR "); } summaryWHERE.append(" rotationtype.Class LIKE ?"); parameters.add(summaryType); } } if (summaryWHERE.length() > 0) { sqlWHERE.append(" AND (" + summaryWHERE.toString() + ")"); } } 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()); sqlWHERE.append(" AND guid.CreatedDate BETWEEN ? AND ?"); description.append(" and was created between '" + fieldB + "' and '" + fieldA + "'"); 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 rotation.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; } }