Example usage for org.hibernate Query 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.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * //from w  w w.  j  ava 2  s . c o m
 */
public Cohort getPatientsHavingBirthDateBetweenDates(Date bornOnOrAfter, Date bornOnOrBefore)
        throws DAOException {

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

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

    if (bornOnOrAfter != null) {
        clauses.add("patient.birthdate >= :bornOnOrAfter");
    }
    if (bornOnOrBefore != null) {
        clauses.add("patient.birthdate <= :bornOnOrBefore");
    }

    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());

    log.debug("Patients having birthdate between dates: " + query.getQueryString());

    query.setCacheMode(CacheMode.IGNORE);
    if (bornOnOrAfter != null) {
        query.setDate("bornOnOrAfter", bornOnOrAfter);
    }
    if (bornOnOrBefore != null) {
        query.setDate("bornOnOrBefore", bornOnOrBefore);
    }
    return new Cohort(query.list());

}

---

From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * //from  w ww . j av a2s.c  o  m
 */
public Cohort getPatientsHavingDiedBetweenDates(Date diedOnOrAfter, Date diedOnOrBefore) throws DAOException {

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

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

    if (diedOnOrAfter != null) {
        clauses.add("patient.deathDate >= :diedOnOrAfter");
    }
    if (diedOnOrBefore != null) {
        clauses.add("patient.deathDate <= :diedOnOrBefore");
    }

    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 (diedOnOrAfter != null) {
        query.setDate("diedOnOrAfter", diedOnOrAfter);
    }
    if (diedOnOrBefore != null) {
        query.setDate("diedOnOrBefore", diedOnOrBefore);
    }
    log.debug("Patients having died between dates query: " + query.getQueryString());

    return new Cohort(query.list());

}

---

From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * //from w w w.j  a  v a2 s . co m
 */
public Cohort getPatientsHavingObs(Integer conceptId, TimeModifier timeModifier, Modifier modifier,
        Object value, Date fromDate, Date toDate, List<User> providers, EncounterType encounterType) {

    if (conceptId == null && value == null)
        throw new IllegalArgumentException("Can't have conceptId == null and value == null");
    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);

    // TODO This should be refactored out
    Number numericValue = null;
    String stringValue = null;
    Concept codedValue = null;
    Date dateValue = null;
    Boolean booleanValue = 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 Boolean) {
                booleanValue = (Boolean) value;
            } else if (value instanceof Number) {
                numericValue = (Number) value;
                booleanValue = (numericValue.doubleValue() != 0.0) ? Boolean.TRUE : Boolean.FALSE;
            } else {
                booleanValue = Boolean.valueOf(value.toString());
            }
            valueSql = "o.value_numeric";
        }
    }

    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 ";
    }

    String encounterSql = "";
    String encounterSqlTable = "";
    String encounterJoin = "";
    String encounterSqlForSubquery = "";
    if (encounterType != null) {
        encounterSqlTable = ", encounter e ";
        encounterJoin = " and e.encounter_id = o.encounter_id ";
        encounterSql += " and e.encounter_type = :encounterType ";
        encounterSqlForSubquery = " inner join encounter e on e.encounter_id = o.encounter_id ";
    }

    if (timeModifier == TimeModifier.ANY || timeModifier == TimeModifier.NO) {
        if (timeModifier == TimeModifier.NO)
            doInvert = true;
        sb.append("select o.person_id from obs o " + encounterSqlTable + " where o.voided = false ");
        if (conceptId != null)
            sb.append("and o.concept_id = :concept_id ");

        sb.append(encounterJoin);
        sb.append(encounterSql);
        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 o"
                + "    where o.voided = false and o.concept_id = :concept_id " + dateSqlForSubquery
                + "    group by person_id"
                + ") subq on o.person_id = subq.person_id and o.obs_datetime = subq.obs_datetime "
                + encounterSqlForSubquery + "where o.voided = false and o.concept_id = :concept_id "
                + encounterSql);

    } else if (doSqlAggregation) {
        String sqlAggregator = timeModifier.toString();
        valueSql = sqlAggregator + "(" + valueSql + ")";
        sb.append("select o.person_id " + "from obs o " + encounterSqlTable
                + " where o.voided = false and o.concept_id = :concept_id " + dateSql + encounterJoin
                + encounterSql + "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 if (booleanValue != null)
            query.setDouble("value", booleanValue ? 1.0 : 0.0);
        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);
    if (encounterType != null)
        query.setInteger("encounterType", encounterType.getEncounterTypeId());

    log.debug("Patients having obs query: " + query.getQueryString());

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

    return ret;
}

---

From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * Encapsulates the common logic between getPatientsHavingRangedObs and getPatientsHavingDiscreteObs
 * //from w  w  w  . j  av  a2 s .co m
 * The arguments passed in fall into two types:
 * <ol>
 *     <li>arguments that limit which obs we will look at</li>
 *     <ul>
 *         <li>timeModifier</li>
 *         <li>question</li>
 *         <li>groupingConcept</li>
 *         <li>onOrAfter</li>
 *         <li>onOrBefore</li>
 *         <li>locationList</li>
 *         <li>encounterTypeList</li>
 *         <li></li>
 *         <li></li>
 *     </ul>
 *     <li>arguments that the obs values must match after being limited by the above arguments</li>
 *     <ul>
 *         <li>operator1</li>
 *         <li>value1</li>
 *         <li>operator2</li>
 *         <li>value2</li>
 *         <li>setOperator</li>
 *         <li>valueList</li>
 *     </ul>
 * </ol> 
 * 
 * @param timeModifier
 * @param question
 * @param groupingConcept
 * @param onOrAfter
 * @param onOrBefore
 * @param locationList
 * @param encounterTypeList
 * @param operator1
 * @param value1
 * @param operator2
 * @param value2
 * @param setOperator
 * @param valueList
 * @return
 */
private Cohort getPatientsHavingObs(TimeModifier timeModifier, Concept question, Concept groupingConcept,
        Date onOrAfter, Date onOrBefore, List<Location> locationList, List<EncounterType> encounterTypeList,
        RangeComparator operator1, Object value1, RangeComparator operator2, Object value2,
        SetComparator setOperator, List<? extends Object> valueList) {

    Integer questionConceptId = question == null ? null : question.getId();
    Integer groupingConceptId = groupingConcept == null ? null : groupingConcept.getId();
    if (groupingConceptId != null)
        throw new RuntimeException("grouping concept not yet implemented");

    List<Integer> locationIds = SqlUtils.openmrsObjectIdListHelper(locationList);
    List<Integer> encounterTypeIds = SqlUtils.openmrsObjectIdListHelper(encounterTypeList);

    boolean joinOnEncounter = encounterTypeIds != null;
    String dateAndLocationSql = ""; // TODO rename to include encounterType
    String dateAndLocationSqlForSubquery = "";
    if (onOrAfter != null) {
        dateAndLocationSql += " and o.obs_datetime >= :onOrAfter ";
        dateAndLocationSqlForSubquery += " and obs.obs_datetime >= :onOrAfter ";
    }
    if (onOrBefore != null) {
        dateAndLocationSql += " and o.obs_datetime <= :onOrBefore ";
        dateAndLocationSqlForSubquery += " and obs.obs_datetime <= :onOrBefore ";
    }
    if (locationIds != null) {
        dateAndLocationSql += " and o.location_id in (:locationIds) ";
        dateAndLocationSqlForSubquery += " and obs.location_id in (:locationIds) ";
    }
    if (encounterTypeIds != null) {
        dateAndLocationSql += " and e.encounter_type in (:encounterTypeIds) ";
        dateAndLocationSqlForSubquery += " and encounter.encounter_type in (:encounterTypeIds) ";
    }

    boolean doSqlAggregation = timeModifier == TimeModifier.MIN || timeModifier == TimeModifier.MAX
            || timeModifier == TimeModifier.AVG;
    boolean doInvert = timeModifier == TimeModifier.NO;

    String valueSql = null;
    List<String> valueClauses = new ArrayList<String>();
    List<Object> valueListForQuery = null;

    if (value1 != null || value2 != null) {
        valueSql = (value1 != null && value1 instanceof Number) ? " o.value_numeric " : " o.value_datetime ";
    } else if (valueList != null && valueList.size() > 0) {
        valueListForQuery = new ArrayList<Object>();
        if (valueList.get(0) instanceof String) {
            valueSql = " o.value_text ";
            for (Object o : valueList)
                valueListForQuery.add(o);
        } else {
            valueSql = " o.value_coded ";
            for (Object o : valueList) {
                if (o instanceof Concept)
                    valueListForQuery.add(((Concept) o).getConceptId());
                else if (o instanceof Number)
                    valueListForQuery.add(((Number) o).intValue());
                else
                    throw new IllegalArgumentException(
                            "Don't know how to handle " + o.getClass() + " in valueList");
            }
        }
    }

    if (doSqlAggregation) {
        valueSql = " " + timeModifier.toString() + "(" + valueSql + ") ";
    }

    if (value1 != null || value2 != null) {
        if (value1 != null) {
            valueClauses.add(valueSql + operator1.getSqlRepresentation() + " :value1 ");
        }
        if (value2 != null) {
            valueClauses.add(valueSql + operator2.getSqlRepresentation() + " :value2 ");
        }
    } else if (valueList != null && valueList.size() > 0) {
        valueClauses.add(valueSql + setOperator.getSqlRepresentation() + " (:valueList) ");
    }

    StringBuilder sql = new StringBuilder();
    sql.append(" select o.person_id from obs o ");
    sql.append(" inner join patient p on o.person_id = p.patient_id ");
    if (joinOnEncounter) {
        sql.append(" inner join encounter e on o.encounter_id = e.encounter_id ");
    }

    if (timeModifier == TimeModifier.ANY || timeModifier == TimeModifier.NO) {
        sql.append(" where o.voided = false and p.voided = false ");
        if (questionConceptId != null) {
            sql.append(" and concept_id = :questionConceptId ");
        }
        sql.append(dateAndLocationSql);
    } else if (timeModifier == TimeModifier.FIRST || timeModifier == TimeModifier.LAST) {
        boolean isFirst = timeModifier == PatientSetService.TimeModifier.FIRST;
        sql.append(" inner join ( ");
        sql.append("    select person_id, " + (isFirst ? "MIN" : "MAX") + "(obs_datetime) as odt ");
        sql.append("    from obs ");
        if (joinOnEncounter) {
            sql.append(" inner join encounter on obs.encounter_id = encounter.encounter_id ");
        }
        sql.append("             where obs.voided = false and obs.concept_id = :questionConceptId "
                + dateAndLocationSqlForSubquery + " group by person_id ");
        sql.append(" ) subq on o.person_id = subq.person_id and o.obs_datetime = subq.odt ");
        sql.append(" where o.voided = false and p.voided = false and o.concept_id = :questionConceptId ");
        sql.append(dateAndLocationSql);
    } else if (doSqlAggregation) {
        sql.append(" where o.voided = false and p.voided = false and concept_id = :questionConceptId "
                + dateAndLocationSql);
        sql.append(" group by o.person_id ");
    } else {
        throw new IllegalArgumentException("TimeModifier '" + timeModifier + "' not recognized");
    }

    if (valueClauses.size() > 0) {
        sql.append(doSqlAggregation ? " having " : " and ");
        for (Iterator<String> i = valueClauses.iterator(); i.hasNext();) {
            sql.append(i.next());
            if (i.hasNext())
                sql.append(" and ");
        }
    }

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

    if (questionConceptId != null)
        query.setInteger("questionConceptId", questionConceptId);
    if (value1 != null) {
        if (value1 instanceof Number)
            query.setDouble("value1", ((Number) value1).doubleValue());
        else
            query.setDate("value1", (Date) value1);
    }
    if (value2 != null) {
        if (value2 instanceof Number)
            query.setDouble("value2", ((Number) value2).doubleValue());
        else
            query.setDate("value2", (Date) value2);
    }
    if (valueListForQuery != null) {
        query.setParameterList("valueList", valueListForQuery);
    }
    if (onOrAfter != null)
        query.setTimestamp("onOrAfter", onOrAfter);
    if (onOrBefore != null)
        query.setTimestamp("onOrBefore", DateUtil.getEndOfDayIfTimeExcluded(onOrBefore));
    if (locationIds != null)
        query.setParameterList("locationIds", locationIds);
    if (encounterTypeIds != null)
        query.setParameterList("encounterTypeIds", encounterTypeIds);

    Cohort ret;
    if (doInvert) {
        ret = Cohorts.allPatients(null);
        ret.getMemberIds().removeAll(query.list());
    } else {
        ret = new Cohort(query.list());
    }
    return ret;
}

---

From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * Binds the given paramMap to the query by replacing all named 
 * parameters (e.g. :paramName) with their corresponding values 
 * in the parameter map.//from  w w w .jav a  2  s.co m
 * 
 * TODO Should add support for other classes.  
 * TODO Should refactor to make more generalizable (create a new param map with correct param values)
 * 
 * @param query
 * @param paramMap
 */
@SuppressWarnings("unchecked")
private void bindQueryParameters(Query query, Map<String, Object> paramMap) {

    // Iterate over parameters and bind them to the Query object
    for (String paramName : paramMap.keySet()) {

        Object paramValue = paramMap.get(paramName);

        // Indicates whether we should bind this parameter in the query 
        boolean bindParameter = (query.getQueryString().indexOf(":" + paramName) > 0);

        if (bindParameter) {

            // Make sure parameter value is not null
            if (paramValue == null) {
                // TODO Should try to convert 'columnName = null' to 'columnName IS NULL'  
                throw new ParameterException("Cannot bind an empty value to parameter " + paramName + ". "
                        + "Please provide a real value or use the 'IS NULL' constraint in your query (e.g. 'table.columnName IS NULL').");
            }

            // Cohort (needs to be first, otherwise it will resolve as OpenmrsObject)
            if (Cohort.class.isAssignableFrom(paramValue.getClass())) {
                query.setParameterList(paramName, ((Cohort) paramValue).getMemberIds());
            }
            // OpenmrsObject (e.g. Location)
            else if (OpenmrsObject.class.isAssignableFrom(paramValue.getClass())) {
                query.setInteger(paramName, ((OpenmrsObject) paramValue).getId());
            }
            // Collection<OpenmrsObject> (e.g. List<Location>)
            else if (Collection.class.isAssignableFrom(paramValue.getClass())) {
                Collection collection = (Collection) paramValue;
                if (collection.iterator().hasNext()) {
                    if (OpenmrsObject.class.isAssignableFrom(collection.iterator().next().getClass())) {
                        query.setParameterList(paramName, SqlUtils.openmrsObjectIdListHelper(
                                new ArrayList<OpenmrsObject>((Collection<OpenmrsObject>) paramValue)));
                    } else {
                        // a List of Strings, Integers?
                        query.setParameterList(paramName, SqlUtils
                                .objectListHelper(new ArrayList<Object>((Collection<Object>) paramValue)));
                    }
                } else {
                    query.setParameter(paramName, null);
                }
            }
            // java.util.Date and subclasses
            else if (paramValue instanceof Date) {
                query.setDate(paramName, (Date) paramValue);
            }
            // String, Integer, et al (this might break since this is a catch all for all other classes)
            else {
                query.setString(paramName, new String(paramValue.toString())); // need to create new string for some reason
            }
        }
    }
}

---

From source file:org.openmrs.module.reportingcompatibility.service.db.HibernateReportingCompatibilityDAO.java

License:Open Source License

/**
 * TODO: Fails to leave out patients who are voided Returns the set of patients that were in a
 * given program, workflow, and state, within a given date range
 * /*ww w  .java  2  s.com*/
 * @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.module.reportingcompatibility.service.db.HibernateReportingCompatibilityDAO.java

License:Open Source License

/**
 * TODO: Don't return voided patients Returns the set of patients that were ever in enrolled in
 * 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// ww w  .  jav  a2  s  .  c o  m
 */
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.module.reportingcompatibility.service.db.HibernateReportingCompatibilityDAO.java

License:Open Source 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");
    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");
    }//from  w w w  .j av  a2  s .  c om
    Concept concept = null;
    if (conceptId != null)
        concept = Context.getConceptService().getConcept(conceptId);
    Number numericValue = null;
    String stringValue = null;
    Concept codedValue = null;
    Date dateValue = null;
    Boolean booleanValue = 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 Boolean)
                booleanValue = (Boolean) value;
            else
                booleanValue = Boolean.valueOf(value.toString());
            valueSql = "o.value_numeric";
        }
    }

    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 if (booleanValue != null)
            query.setDouble("value", booleanValue ? 1.0 : 0.0);
        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.module.reportingcompatibility.service.db.HibernateReportingCompatibilityDAO.java

License:Open Source License

/**
 * <pre>/*w ww .  j  a  va 2s.c o  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.module.reportingcompatibility.service.db.HibernateReportingCompatibilityDAO.java

License:Open Source License

/**
 * TODO: don't return voided patients Gets all patients with an obs's value_date column value
 * within <code>startTime</code> and <code>endTime</code>
 * /*from  w ww.j av a 2 s .co 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());
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• »