Example usage for org.hibernate CacheMode IGNORE

List of usage examples for org.hibernate CacheMode IGNORE

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. CacheMode
  6. IGNORE

Introduction

In this page you can find the example usage for org.hibernate CacheMode IGNORE.

Prototype

CacheMode IGNORE

To view the source code for org.hibernate CacheMode IGNORE.

Click Source Link

Document

The session will never interact with the cache, except to invalidate cache items when updates occur.

Usage

From source file:org.openmrs.module.patientregistration.service.db.HibernatePatientRegistrationDAO.java

License:Open Source License

public List<Integer> getUnknownPersonId() {
    List<Integer> queryResults = null;
    StringBuilder sql = new StringBuilder();
    sql.append("select distinct pa.person.personId ");
    sql.append("from PersonAttribute pa ");
    sql.append("where pa.attributeType.name='");
    sql.append(PatientRegistrationConstants.UNKNOWN_PATIENT_PERSON_ATTRIBUTE_TYPE_NAME);
    sql.append("' and pa.value='true' ");
    try {//  www . j ava 2  s  .c  o m
        Query query = sessionFactory.getCurrentSession().createQuery(sql.toString());
        query.setCacheMode(CacheMode.IGNORE);
        queryResults = query.list();
        if (queryResults != null && queryResults.size() > 0) {
            return queryResults;
        }

    } catch (Exception e) {
        log.error("error retrieving the IDs of the unknown persons", e);
    }
    return queryResults;
}

From source file:org.openmrs.module.patientregistration.service.db.HibernatePatientRegistrationDAO.java

License:Open Source License

public List<Integer> getPhoneticsPersonId(String firstName, String lastName) {

    List<Integer> queryResults = null;
    if (StringUtils.isBlank(firstName) || (StringUtils.isBlank(lastName))) {
        return queryResults;
    }//from w  w w  .  j ava2s .c o m
    StringBuilder sql = new StringBuilder();
    sql.append("select distinct np1.personName.person.personId ");
    sql.append("from NamePhonetic np1 ");
    sql.append("where np1.renderedString like '").append(firstName).append("%' ");
    sql.append("and np1.nameField=1 ");
    sql.append("and np1.personName.personNameId in ");
    sql.append("(select np2.personName.personNameId from NamePhonetic np2 ");
    sql.append("where np2.renderedString like '").append(lastName).append("%' ");
    sql.append("and np2.nameField=3) ");
    try {
        Query query = sessionFactory.getCurrentSession().createQuery(sql.toString());
        query.setCacheMode(CacheMode.IGNORE);

        queryResults = query.list();
        if (queryResults != null && queryResults.size() > 0) {
            return queryResults;

        }

    } catch (Exception e) {
        log.error("error retrieving name phonetics", e);
    }

    return queryResults;
}

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

License:Open Source License

public Cohort getPatientsWithGender(boolean includeMales, boolean includeFemales,
        boolean includeUnknownGender) {

    if (!includeMales && !includeFemales && !includeUnknownGender) {
        return new Cohort();
    }//from  w w  w . j a v a  2  s. co  m

    String prefixTerm = "";
    StringBuilder query = new StringBuilder(
            "select patientId from Patient patient where patient.voided = false and ( ");
    if (includeMales) {
        query.append(" patient.gender = 'M' ");
        prefixTerm = " or";
    }
    if (includeFemales) {
        query.append(prefixTerm + " patient.gender = 'F'");
        prefixTerm = " or";
    }
    if (includeUnknownGender) {
        query.append(
                prefixTerm + " patient.gender is null or (patient.gender != 'M' and patient.gender != 'F')");
    }
    query.append(")");
    Query q = sessionFactory.getCurrentSession().createQuery(query.toString());
    q.setCacheMode(CacheMode.IGNORE);
    return new Cohort(q.list());
}

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

License:Open Source License

/**
 * /* w w  w .  j  av a  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

/**
 * //  w w  w .  jav a 2s.  co 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   www .  j a v  a2s . c o  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  a v 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

/**
 * @see org.openmrs.module.reporting.cohort.query.db.CohortQueryDAO#getPatientsHavingBirthAndDeath(java.util.Date, java.util.Date, java.util.Date, java.util.Date)
 *///from w  w w . j av  a  2 s . co  m
public Cohort getPatientsHavingBirthAndDeath(Date bornOnOrAfter, Date bornOnOrBefore, Date diedOnOrAfter,
        Date diedOnOrBefore) {
    StringBuilder sql = new StringBuilder();
    sql.append(" select patient_id ");
    sql.append(" from patient pat ");
    sql.append(" inner join person per on pat.patient_id = per.person_id ");
    sql.append(" where pat.voided = false and per.voided = false ");
    if (bornOnOrAfter != null)
        sql.append(" and birthdate >= :bornOnOrAfter ");
    if (bornOnOrBefore != null)
        sql.append(" and birthdate <= :bornOnOrBefore ");
    if (diedOnOrAfter != null)
        sql.append(" and death_date >= :diedOnOrAfter ");
    if (diedOnOrBefore != null)
        sql.append(" and death_date <= :diedOnOrBefore ");

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

    if (bornOnOrAfter != null)
        q.setTimestamp("bornOnOrAfter", bornOnOrAfter);
    if (bornOnOrBefore != null)
        q.setTimestamp("bornOnOrBefore", DateUtil.getEndOfDayIfTimeExcluded(bornOnOrBefore));
    if (diedOnOrAfter != null)
        q.setTimestamp("diedOnOrAfter", diedOnOrAfter);
    if (diedOnOrBefore != null)
        q.setTimestamp("diedOnOrBefore", DateUtil.getEndOfDayIfTimeExcluded(diedOnOrBefore));

    return new Cohort(q.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/*  w  ww. ja v a  2 s . co 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 ww. j  av  a 2s. c o  m
    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;
}