Example usage for org.hibernate Query setDouble

Introduction

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

Prototype

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

Source Link

Document

Bind a named double-valued parameter.

Usage

From source file:org.mzd.shap.domain.dao.SampleDaoSpringHibernate.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<AnnotationHistogramDTO> annotationHistogram(final Sample sample, final Annotator annotator,
        final Double confidence, final Collection<Taxonomy> excludedTaxons) {

    Object result = getHibernateTemplate().execute(new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            Query query;
            if (excludedTaxons == null || excludedTaxons.size() == 0) {
                query = session.createSQLQuery(HISTO_QUERY_NOTAX);
            } else {
                query = session.createSQLQuery(HISTO_QUERY_WITHTAX).setParameterList("excludedTaxons",
                        excludedTaxons, new CustomType(TaxonomyUserType.class, null));
            }//from w  w w.j ava 2  s. c  o m
            query.setResultTransformer(Transformers.aliasToBean(AnnotationHistogramDTO.class));
            query.setInteger("sampleId", sample.getId());
            query.setDouble("confidence", confidence);
            query.setInteger("annotatorId", annotator.getId());
            return query.list();
        }
    });
    return (List<AnnotationHistogramDTO>) result;
}

---

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");
    }//w  ww  . j  ava 2s. co m
    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

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   ww w  .j ava 2s .  c  om

    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.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * /*w  ww  . jav a  2s  .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
 * /*  w  w  w.  java  2  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.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  a va2  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

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 .  jav  a 2  s . c  om*/

    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:ru.codemine.ccms.dao.SalesDAOImpl.java

License:Open Source License

@Override
public boolean updatePlanAll(Double plan, LocalDate startDate, LocalDate endDate) {
    if (plan == null || startDate == null || endDate == null || startDate.isAfter(endDate))
        return false;

    Query updateQuery = getSession().createQuery("UPDATE SalesMeta Sm " + "SET Sm.plan = :plan "
            + "WHERE Sm.startDate = :startdate " + "AND Sm.endDate = :enddate");
    updateQuery.setDate("startdate", startDate.toDate());
    updateQuery.setDate("enddate", endDate.toDate());
    updateQuery.setDouble("plan", plan);

    return updateQuery.executeUpdate() > 0;
}

---

• «
• 1
• 2
• 3
• 4
• »