com.sfs.whichdoctor.search.sql.RotationSqlHandler.java Source code

Java tutorial

Introduction

Here is the source code for com.sfs.whichdoctor.search.sql.RotationSqlHandler.java

Source

/*******************************************************************************
 * 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;
    }
}