Example usage for org.hibernate Query setDate

List of usage examples for org.hibernate Query setDate

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. setDate

Introduction

In this page you can find the example usage for org.hibernate Query setDate.

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setDate(String name, Date val)

Source Link

Document

Bind the val (time is truncated) of a given Date object to a named query parameter.

Usage

From source file:org.opencustomer.db.dao.crm.custom.CompanyListDAO.java

License:Mozilla Public License

public long countList(String companyName, Integer companyStateId, Integer companyTypeId, Integer sectorId,
        Integer categoryId, Integer ratingId, Date lastContactDateStart, Date lastContactDateEnd, UserVO user) {
    String _companyName = toLower(adjustWildcards(companyName));

    long count = 0;

    try {// www .j a  v a2s .  c  o m
        StringBuilder hql = new StringBuilder();

        hql.append(" select c.id ");
        hql.append(" from ").append(CompanyVO.class.getName()).append(" c ");
        hql.append(" left join c.companyType ct ");
        hql.append(" left join c.companyState cs ");
        hql.append(" left join c.sector s ");
        hql.append(" left join c.category ca ");
        hql.append(" left join c.rating r ");
        hql.append(" left join c.contacts co ");
        hql.append(" left join c.personContacts pc ");
        hql.append(" left join pc.contact co2 ");
        hql.append(" where 1=1 ");

        if (companyName != null)
            hql.append(" AND LOWER(c.companyName) like :companyName");
        if (companyStateId != null)
            hql.append(" AND c.companyState = :companyState");
        if (companyTypeId != null)
            hql.append(" AND c.companyType = :companyType");
        if (sectorId != null)
            hql.append(" AND c.sector = :sector");
        if (ratingId != null)
            hql.append(" AND c.rating = :rating");
        if (categoryId != null)
            hql.append(" AND c.category = :category");
        if (user != null) {
            hql.append(" AND (c.accessGlobal != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" OR (c.accessGroup != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND exists (from ").append(UserVO.class.getName())
                    .append(" as u where u.id = :userId and u.profile.usergroups.id = c.ownerGroup)) ");
            hql.append(" OR (c.accessUser != '" + EntityAccess.Access.NONE + "' ");
            hql.append(" AND c.ownerUser = :userId)) ");
        }
        hql.append(" group by c.id, c.companyName, ct.name, cs.name, s.name");
        if (lastContactDateStart != null || lastContactDateEnd != null) {
            hql.append(" having ");
            if (lastContactDateStart != null) {
                hql.append(" (case when max(co.contactTimestamp) is null then max(co2.contactTimestamp) else ");
                hql.append(
                        "    case when max(co2.contactTimestamp) is null then max(co.contactTimestamp) else ");
                hql.append(
                        "       case when max(co.contactTimestamp) > max(co2.contactTimestamp) then max(co.contactTimestamp) else max(co2.contactTimestamp) end ");
                hql.append("    end ");
                hql.append(" end ");
                hql.append(" >= :lastContactDateStart)");
            }
            if (lastContactDateEnd != null) {
                if (lastContactDateStart != null)
                    hql.append(" and ");
                hql.append(" (case when max(co.contactTimestamp) is null then max(co2.contactTimestamp) else ");
                hql.append(
                        "    case when max(co2.contactTimestamp) is null then max(co.contactTimestamp) else ");
                hql.append(
                        "       case when max(co.contactTimestamp) > max(co2.contactTimestamp) then max(co.contactTimestamp) else max(co2.contactTimestamp) end ");
                hql.append("    end ");
                hql.append(" end ");
                hql.append(" <= :lastContactDateEnd)");
            }
        }

        // companyStateId, Integer companyTypeId, Integer sectorTypeId, Date
        // lastContactDateStart, Date lastContactDateEnd
        Query query = HibernateContext.getSession().createQuery(hql.toString());
        if (companyName != null)
            query.setString("companyName", _companyName);
        if (companyStateId != null)
            query.setInteger("companyState", companyStateId);
        if (companyTypeId != null)
            query.setInteger("companyType", companyTypeId);
        if (sectorId != null)
            query.setInteger("sector", sectorId);
        if (ratingId != null)
            query.setInteger("rating", ratingId);
        if (categoryId != null)
            query.setInteger("category", categoryId);
        if (lastContactDateStart != null)
            query.setDate("lastContactDateStart", lastContactDateStart);
        if (lastContactDateEnd != null)
            query.setDate("lastContactDateEnd", lastContactDateEnd);
        if (user != null)
            query.setInteger("userId", user.getId());

        count = query.list().size();

        if (log.isDebugEnabled())
            log.debug("count " + count + " companies");
    } catch (HibernateException e) {
        log.error("Could not count companies", e);
        throw e;
    }

    return count;
}

From source file:org.openmrs.api.db.hibernate.HibernateDiagnosisDAO.java

License:Mozilla Public License

/**
 * Gets all active diagnoses related to the specified patient.
 *
 * @param patient the patient whose active diagnoses are being queried.
 * @return all active diagnoses associated with the specified patient.
 *//*from  w w w. jav a 2  s.com*/
@SuppressWarnings("unchecked")
@Override
public List<Diagnosis> getActiveDiagnoses(Patient patient, Date fromDate) {
    String fromDateCriteria = "";
    if (fromDate != null) {
        fromDateCriteria = " and d.dateCreated >= :fromDate ";
    }
    Query query = sessionFactory.getCurrentSession()
            .createQuery("from Diagnosis d where d.patient.patientId = :patientId and d.voided = false "
                    + fromDateCriteria + " order by d.dateCreated desc");
    query.setInteger("patientId", patient.getId());
    if (fromDate != null) {
        query.setDate("fromDate", fromDate);
    }
    return query.list();
}

From source file:org.openmrs.api.db.hibernate.HibernatePatientDAO.java

License:Mozilla Public License

/**
 * Inserts a row into the patient table This avoids hibernate's bunging of our
 * person/patient/user inheritance//from   w w w  .  ja v  a  2  s.c om
 *
 * @param patient
 */
private void insertPatientStubIfNeeded(Patient patient) {

    boolean stubInsertNeeded = false;

    if (patient.getPatientId() != null) {
        // check if there is a row with a matching patient.patient_id
        String sql = "SELECT 1 FROM patient WHERE patient_id = :patientId";
        Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
        query.setInteger("patientId", patient.getPatientId());

        stubInsertNeeded = (query.uniqueResult() == null);
    }

    if (stubInsertNeeded) {
        if (patient.getCreator() == null) { //If not yet persisted
            patient.setCreator(Context.getAuthenticatedUser());
        }
        if (patient.getDateCreated() == null) { //If not yet persisted
            patient.setDateCreated(new Date());
        }

        String insert = "INSERT INTO patient (patient_id, creator, voided, date_created) VALUES (:patientId, :creator, 0, :dateCreated)";
        Query query = sessionFactory.getCurrentSession().createSQLQuery(insert);
        query.setInteger("patientId", patient.getPatientId());
        query.setInteger("creator", patient.getCreator().getUserId());
        query.setDate("dateCreated", patient.getDateCreated());

        query.executeUpdate();

        //Without evicting person, you will get this error when promoting person to patient
        //org.hibernate.NonUniqueObjectException: a different object with the same identifier
        //value was already associated with the session: [org.openmrs.Patient#]
        //see TRUNK-3728
        Person person = (Person) sessionFactory.getCurrentSession().get(Person.class, patient.getPersonId());
        sessionFactory.getCurrentSession().evict(person);
    }

}

From source file:org.openmrs.api.db.hibernate.HibernatePatientSetDAO.java

License:Mozilla Public License

/**
 * given program, workflow, and state, within a given date range
 * /*w  w  w .j av  a 2  s  .co  m*/
 * @param program The program the patient must have been in
 * @param stateList List of states the patient must have been in (implies a workflow) (can be
 *            null)
 * @param fromDate If not null, then only patients in the given program/workflow/state on or
 *            after this date
 * @param toDate If not null, then only patients in the given program/workflow/state on or
 *            before this date
 * @return Cohort of Patients matching criteria
 */
public Cohort getPatientsByProgramAndState(Program program, List<ProgramWorkflowState> stateList, Date fromDate,
        Date toDate) {
    Integer programId = program == null ? null : program.getProgramId();
    List<Integer> stateIds = null;
    if (stateList != null && stateList.size() > 0) {
        stateIds = new ArrayList<Integer>();
        for (ProgramWorkflowState state : stateList) {
            stateIds.add(state.getProgramWorkflowStateId());
        }
    }

    List<String> clauses = new ArrayList<String>();
    clauses.add("pp.voided = false");
    if (programId != null) {
        clauses.add("pp.program_id = :programId");
    }
    if (stateIds != null) {
        clauses.add("ps.state in (:stateIds)");
        clauses.add("ps.voided = false");
    }
    if (fromDate != null) {
        clauses.add("(pp.date_completed is null or pp.date_completed >= :fromDate)");
        if (stateIds != null) {
            clauses.add("(ps.end_date is null or ps.end_date >= :fromDate)");
        }
    }
    if (toDate != null) {
        clauses.add("(pp.date_enrolled is null or pp.date_enrolled <= :toDate)");
        if (stateIds != null) {
            clauses.add("(ps.start_date is null or ps.start_date <= :toDate)");
        }
    }

    StringBuilder sql = new StringBuilder();
    sql.append("select pp.patient_id ");
    sql.append("from patient_program pp ");
    sql.append("inner join patient p on pp.patient_id = p.patient_id and p.voided = false ");
    if (stateIds != null) {
        sql.append("inner join patient_state ps on pp.patient_program_id = ps.patient_program_id ");
    }
    for (ListIterator<String> i = clauses.listIterator(); i.hasNext();) {
        sql.append(i.nextIndex() == 0 ? " where " : " and ");
        sql.append(i.next());
    }
    sql.append(" group by pp.patient_id");
    log.debug("query: " + sql);

    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());
    if (programId != null) {
        query.setInteger("programId", programId);
    }
    if (stateIds != null) {
        query.setParameterList("stateIds", stateIds);
    }
    if (fromDate != null) {
        query.setDate("fromDate", fromDate);
    }
    if (toDate != null) {
        query.setDate("toDate", toDate);
    }

    return new Cohort(query.list());
}

From source file:org.openmrs.api.db.hibernate.HibernatePatientSetDAO.java

License:Mozilla Public License

/**
 * a given program. If fromDate != null, then only those patients who were in the program at any
 * time after that date if toDate != null, then only those patients who were in the program at
 * any time before that date//w  w w. j a  v  a2 s  . com
 */
public Cohort getPatientsInProgram(Integer programId, Date fromDate, Date toDate) {
    String sql = "select pp.patient_id from patient_program pp ";
    sql += " inner join patient p on pp.patient_id = p.patient_id and p.voided = false ";
    sql += " where pp.voided = false and pp.program_id = :programId ";
    if (fromDate != null) {
        sql += " and (date_completed is null or date_completed >= :fromDate) ";
    }
    if (toDate != null) {
        sql += " and (date_enrolled is null or date_enrolled <= :toDate) ";
    }
    log.debug("sql: " + sql);

    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    query.setCacheMode(CacheMode.IGNORE);

    query.setInteger("programId", programId);
    if (fromDate != null) {
        query.setDate("fromDate", fromDate);
    }
    if (toDate != null) {
        query.setDate("toDate", toDate);
    }

    return new Cohort(query.list());
}

From source file:org.openmrs.api.db.hibernate.HibernatePatientSetDAO.java

License:Mozilla Public License

public Cohort getPatientsHavingObs(Integer conceptId, PatientSetService.TimeModifier timeModifier,
        PatientSetService.Modifier modifier, Object value, Date fromDate, Date toDate) {
    if (conceptId == null && value == null) {
        throw new IllegalArgumentException("Can't have conceptId == null and value == null");
    }/*from   w w  w  .  j  a  va2 s .  c om*/
    if (conceptId == null && (timeModifier != TimeModifier.ANY && timeModifier != TimeModifier.NO)) {
        throw new IllegalArgumentException("If conceptId == null, timeModifier must be ANY or NO");
    }
    if (conceptId == null && modifier != Modifier.EQUAL) {
        throw new IllegalArgumentException("If conceptId == null, modifier must be EQUAL");
    }
    Concept concept = null;
    if (conceptId != null) {
        concept = Context.getConceptService().getConcept(conceptId);
    }
    Number numericValue = null;
    String stringValue = null;
    Concept codedValue = null;
    Date dateValue = null;
    String valueSql = null;
    if (value != null) {
        if (concept == null) {
            if (value instanceof Concept) {
                codedValue = (Concept) value;
            } else {
                codedValue = Context.getConceptService().getConceptByName(value.toString());
            }
            valueSql = "o.value_coded";
        } else if (concept.getDatatype().isNumeric()) {
            if (value instanceof Number) {
                numericValue = (Number) value;
            } else {
                numericValue = new Double(value.toString());
            }
            valueSql = "o.value_numeric";
        } else if (concept.getDatatype().isText()) {
            stringValue = value.toString();
            valueSql = "o.value_text";
            if (modifier == null) {
                modifier = Modifier.EQUAL;
            }
        } else if (concept.getDatatype().isCoded()) {
            if (value instanceof Concept) {
                codedValue = (Concept) value;
            } else {
                codedValue = Context.getConceptService().getConceptByName(value.toString());
            }
            valueSql = "o.value_coded";
        } else if (concept.getDatatype().isDate()) {
            if (value instanceof Date) {
                dateValue = (Date) value;
            } else {
                try {
                    dateValue = Context.getDateFormat().parse(value.toString());
                } catch (ParseException ex) {
                    throw new IllegalArgumentException("Cannot interpret " + dateValue
                            + " as a date in the format " + Context.getDateFormat());
                }
            }
            valueSql = "o.value_datetime";
        } else if (concept.getDatatype().isBoolean()) {
            if (value instanceof Concept) {
                codedValue = (Concept) value;
            } else {
                boolean asBoolean = false;
                if (value instanceof Boolean) {
                    asBoolean = ((Boolean) value).booleanValue();
                } else {
                    asBoolean = Boolean.valueOf(value.toString());
                }
                codedValue = asBoolean ? Context.getConceptService().getTrueConcept()
                        : Context.getConceptService().getFalseConcept();
            }
            valueSql = "o.value_coded";
        }
    }

    StringBuilder sb = new StringBuilder();
    boolean useValue = value != null;
    boolean doSqlAggregation = timeModifier == TimeModifier.MIN || timeModifier == TimeModifier.MAX
            || timeModifier == TimeModifier.AVG;
    boolean doInvert = false;

    String dateSql = "";
    String dateSqlForSubquery = "";
    if (fromDate != null) {
        dateSql += " and o.obs_datetime >= :fromDate ";
        dateSqlForSubquery += " and obs_datetime >= :fromDate ";
    }
    if (toDate != null) {
        dateSql += " and o.obs_datetime <= :toDate ";
        dateSqlForSubquery += " and obs_datetime <= :toDate ";
    }

    if (timeModifier == TimeModifier.ANY || timeModifier == TimeModifier.NO) {
        if (timeModifier == TimeModifier.NO) {
            doInvert = true;
        }
        sb.append("select o.person_id from obs o "
                + "inner join patient p on o.person_id = p.patient_id and p.voided = false "
                + "where o.voided = false ");
        if (conceptId != null) {
            sb.append("and concept_id = :concept_id ");
        }
        sb.append(dateSql);

    } else if (timeModifier == TimeModifier.FIRST || timeModifier == TimeModifier.LAST) {
        boolean isFirst = timeModifier == PatientSetService.TimeModifier.FIRST;
        sb.append("select o.person_id " + "from obs o inner join (" + "    select person_id, "
                + (isFirst ? "min" : "max") + "(obs_datetime) as obs_datetime" + "    from obs"
                + "    where voided = false and concept_id = :concept_id " + dateSqlForSubquery
                + "    group by person_id"
                + ") subq on o.person_id = subq.person_id and o.obs_datetime = subq.obs_datetime "
                + " inner join patient p on o.person_id = p.patient_id and p.voided = false "
                + "where o.voided = false and o.concept_id = :concept_id ");

    } else if (doSqlAggregation) {
        String sqlAggregator = timeModifier.toString();
        valueSql = sqlAggregator + "(" + valueSql + ")";
        sb.append("select o.person_id " + "from obs o "
                + "inner join patient p on o.person_id = p.patient_id and p.voided = false "
                + "where o.voided = false and concept_id = :concept_id " + dateSql + "group by o.person_id ");

    } else {
        throw new IllegalArgumentException("TimeModifier '" + timeModifier + "' not recognized");
    }

    if (useValue) {
        sb.append(doSqlAggregation ? " having " : " and ");
        sb.append(valueSql + " ");
        sb.append(modifier.getSqlRepresentation() + " :value");
    }
    if (!doSqlAggregation) {
        sb.append(" group by o.person_id ");
    }

    log.debug("query: " + sb);
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString());
    query.setCacheMode(CacheMode.IGNORE);

    if (conceptId != null) {
        query.setInteger("concept_id", conceptId);
    }
    if (useValue) {
        if (numericValue != null) {
            query.setDouble("value", numericValue.doubleValue());
        } else if (codedValue != null) {
            query.setInteger("value", codedValue.getConceptId());
        } else if (stringValue != null) {
            query.setString("value", stringValue);
        } else if (dateValue != null) {
            query.setDate("value", dateValue);
        } else {
            throw new IllegalArgumentException(
                    "useValue is true, but numeric, coded, string, boolean, and date values are all null");
        }
    }
    if (fromDate != null) {
        query.setDate("fromDate", fromDate);
    }
    if (toDate != null) {
        query.setDate("toDate", toDate);
    }

    Cohort ret;
    if (doInvert) {
        ret = getAllPatients();
        ret.getMemberIds().removeAll(query.list());
    } else {
        ret = new Cohort(query.list());
    }

    return ret;
}

From source file:org.openmrs.api.db.hibernate.HibernatePatientSetDAO.java

License:Mozilla Public License

/**
 * <pre>//from   w ww.j a v a2  s. co  m
 * Returns the set of patients that have encounters, with several optional parameters:
 *   of type encounterType
 *   at a given location
 *   from filling out a specific form
 *   on or after fromDate
 *   on or before toDate
 *   patients with at least minCount of the given encounters
 *   patients with up to maxCount of the given encounters
 * </pre>
 */
public Cohort getPatientsHavingEncounters(List<EncounterType> encounterTypeList, Location location, Form form,
        Date fromDate, Date toDate, Integer minCount, Integer maxCount) {
    List<Integer> encTypeIds = null;
    if (encounterTypeList != null && encounterTypeList.size() > 0) {
        encTypeIds = new ArrayList<Integer>();
        for (EncounterType t : encounterTypeList) {
            encTypeIds.add(t.getEncounterTypeId());
        }
    }
    Integer locationId = location == null ? null : location.getLocationId();
    Integer formId = form == null ? null : form.getFormId();
    List<String> whereClauses = new ArrayList<String>();
    whereClauses.add("e.voided = false");
    if (encTypeIds != null) {
        whereClauses.add("e.encounter_type in (:encTypeIds)");
    }
    if (locationId != null) {
        whereClauses.add("e.location_id = :locationId");
    }
    if (formId != null) {
        whereClauses.add("e.form_id = :formId");
    }
    if (fromDate != null) {
        whereClauses.add("e.encounter_datetime >= :fromDate");
    }
    if (toDate != null) {
        whereClauses.add("e.encounter_datetime <= :toDate");
    }
    List<String> havingClauses = new ArrayList<String>();
    if (minCount != null) {
        havingClauses.add("count(*) >= :minCount");
    }
    if (maxCount != null) {
        havingClauses.add("count(*) >= :maxCount");
    }
    StringBuilder sb = new StringBuilder();
    sb.append(" select e.patient_id from encounter e ");
    sb.append(" inner join patient p on e.patient_id = p.patient_id and p.voided = false ");
    for (ListIterator<String> i = whereClauses.listIterator(); i.hasNext();) {
        sb.append(i.nextIndex() == 0 ? " where " : " and ");
        sb.append(i.next());
    }
    sb.append(" group by e.patient_id ");
    for (ListIterator<String> i = havingClauses.listIterator(); i.hasNext();) {
        sb.append(i.nextIndex() == 0 ? " having " : " and ");
        sb.append(i.next());
    }
    log.debug("query: " + sb);

    Query query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString());
    if (encTypeIds != null) {
        query.setParameterList("encTypeIds", encTypeIds);
    }
    if (locationId != null) {
        query.setInteger("locationId", locationId);
    }
    if (formId != null) {
        query.setInteger("formId", formId);
    }
    if (fromDate != null) {
        query.setDate("fromDate", fromDate);
    }
    if (toDate != null) {
        query.setDate("toDate", toDate);
    }
    if (minCount != null) {
        query.setInteger("minCount", minCount);
    }
    if (maxCount != null) {
        query.setInteger("maxCount", maxCount);
    }

    return new Cohort(query.list());
}

From source file:org.openmrs.api.db.hibernate.HibernatePatientSetDAO.java

License:Mozilla Public License

/**
 * within <code>startTime</code> and <code>endTime</code>
 * /*from  www  . j  a v a 2s  .c  o m*/
 * @param conceptId
 * @param startTime
 * @param endTime
 * @return PatientSet
 */
public Cohort getPatientsHavingDateObs(Integer conceptId, Date startTime, Date endTime) {
    StringBuffer sb = new StringBuffer();
    sb.append("select o.person_id from obs o " + "where concept_id = :concept_id ");
    sb.append(" and o.value_datetime between :startValue and :endValue");
    sb.append(" and o.voided = '0'");

    Query query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString());
    query.setCacheMode(CacheMode.IGNORE);

    query.setInteger("concept_id", conceptId);
    query.setDate("startValue", startTime);
    query.setDate("endValue", endTime);

    return new Cohort(query.list());
}

From source file:org.openmrs.api.db.hibernate.HibernatePatientSetDAO.java

License:Mozilla Public License

public Cohort getPatientsHavingNumericObs(Integer conceptId, PatientSetService.TimeModifier timeModifier,
        PatientSetService.Modifier modifier, Number value, Date fromDate, Date toDate) {

    Concept concept = Context.getConceptService().getConcept(conceptId);
    if (!concept.isNumeric()) {
        // throw new IllegalArgumentException(concept + " is not numeric");
    }/*from   w ww.j  a  v a2  s .co m*/

    StringBuffer sb = new StringBuffer();
    boolean useValue = modifier != null && value != null;
    boolean doSqlAggregation = timeModifier == TimeModifier.MIN || timeModifier == TimeModifier.MAX
            || timeModifier == TimeModifier.AVG;
    String valueSql = "o.value_numeric";
    boolean doInvert = false;

    String dateSql = "";
    if (fromDate != null) {
        dateSql += " and o.obs_datetime >= :fromDate ";
    }
    if (toDate != null) {
        dateSql += " and o.obs_datetime <= :toDate ";
    }

    if (timeModifier == TimeModifier.ANY || timeModifier == TimeModifier.NO) {
        if (timeModifier == TimeModifier.NO) {
            doInvert = true;
        }
        sb.append("select o.person_id from obs o " + "where voided = false and concept_id = :concept_id ");
        sb.append(dateSql);
    } else if (timeModifier == TimeModifier.FIRST || timeModifier == TimeModifier.LAST) {
        boolean isFirst = timeModifier == PatientSetService.TimeModifier.FIRST;
        sb.append("select o.person_id " + "from obs o inner join (" + "    select person_id, "
                + (isFirst ? "min" : "max") + "(obs_datetime) as obs_datetime" + "    from obs"
                + "    where voided = false and concept_id = :concept_id " + dateSql + "    group by person_id"
                + ") subq on o.person_id = subq.person_id and o.obs_datetime = subq.obs_datetime "
                + "where o.voided = false and o.concept_id = :concept_id ");
    } else if (doSqlAggregation) {
        String sqlAggregator = timeModifier.toString();
        valueSql = sqlAggregator + "(o.value_numeric)";
        sb.append("select o.person_id " + "from obs o where o.voided = false and concept_id = :concept_id "
                + dateSql + "group by o.person_id ");
    } else {
        throw new IllegalArgumentException("TimeModifier '" + timeModifier + "' not recognized");
    }

    if (useValue) {
        sb.append(doSqlAggregation ? "having " : " and ");
        sb.append(valueSql + " ");
        sb.append(modifier.getSqlRepresentation() + " :value");
    }
    if (!doSqlAggregation) {
        sb.append(" group by o.person_id ");
    }

    log.debug("query: " + sb);
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString());
    query.setCacheMode(CacheMode.IGNORE);

    query.setInteger("concept_id", conceptId);
    if (useValue) {
        query.setDouble("value", value.doubleValue());
    }
    if (fromDate != null) {
        query.setDate("fromDate", fromDate);
    }
    if (toDate != null) {
        query.setDate("toDate", fromDate);
    }

    Cohort ret;
    if (doInvert) {
        ret = getAllPatients();
        ret.getMemberIds().removeAll(query.list());
    } else {
        ret = new Cohort(query.list());
    }

    return ret;
}

From source file:org.openmrs.api.db.hibernate.HibernatePatientSetDAO.java

License:Mozilla Public License

public Cohort getPatientsByCharacteristics(String gender, Date minBirthdate, Date maxBirthdate, Integer minAge,
        Integer maxAge, Boolean aliveOnly, Boolean deadOnly, Date effectiveDate) throws DAOException {

    if (effectiveDate == null) {
        effectiveDate = new Date();
    }/*  w  w w.  j  ava 2  s . c  o m*/

    StringBuffer queryString = new StringBuffer("select patientId from Patient patient");
    List<String> clauses = new ArrayList<String>();

    clauses.add("patient.voided = false");

    if (gender != null) {
        gender = gender.toUpperCase();
        clauses.add("patient.gender = :gender");
    }
    if (minBirthdate != null) {
        clauses.add("patient.birthdate >= :minBirthdate");
    }
    if (maxBirthdate != null) {
        clauses.add("patient.birthdate <= :maxBirthdate");
    }
    if (aliveOnly != null && aliveOnly) {
        clauses.add("patient.dead = false");
    }
    if (deadOnly != null && deadOnly) {
        clauses.add("patient.dead = true");
    }

    Date maxBirthFromAge = null;
    if (minAge != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(effectiveDate);
        cal.add(Calendar.YEAR, -minAge);
        maxBirthFromAge = cal.getTime();
        clauses.add("patient.birthdate <= :maxBirthFromAge");
    }
    Date minBirthFromAge = null;
    if (maxAge != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(effectiveDate);
        cal.add(Calendar.YEAR, -(maxAge + 1));
        minBirthFromAge = cal.getTime();
        clauses.add("patient.birthdate > :minBirthFromAge");
    }

    clauses.add("(patient.birthdate is null or patient.birthdate <= :effectiveDate)");

    boolean first = true;
    for (String clause : clauses) {
        if (first) {
            queryString.append(" where ").append(clause);
            first = false;
        } else {
            queryString.append(" and ").append(clause);
        }
    }
    Query query = sessionFactory.getCurrentSession().createQuery(queryString.toString());
    query.setCacheMode(CacheMode.IGNORE);
    if (gender != null) {
        query.setString("gender", gender);
    }
    if (minBirthdate != null) {
        query.setDate("minBirthdate", minBirthdate);
    }
    if (maxBirthdate != null) {
        query.setDate("maxBirthdate", maxBirthdate);
    }
    if (minAge != null) {
        query.setDate("maxBirthFromAge", maxBirthFromAge);
    }
    if (maxAge != null) {
        query.setDate("minBirthFromAge", minBirthFromAge);
    }
    query.setDate("effectiveDate", effectiveDate);

    return new Cohort(query.list());
}