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.FacesRegister.api.db.hibernate.HibernateRegisterDAO.java

License:Open Source License

public void query(Integer locationId, Date startDate, Date endDate) {

    StringBuilder strSQL = new StringBuilder();
    strSQL.append("select e.patient_id, date(o.value_datetime)as Date_Started_ART ");
    strSQL.append("from encounter e ");
    strSQL.append("join obs o on o.encounter_id=e.encounter_id and o.concept_id in (6746,6739) ");
    strSQL.append("where e.encounter_type in (21,22) ");
    strSQL.append("and e.location_id=" + locationId
            + " and date(o.value_datetime)  between :startDate and :endDate ");
    strSQL.append("union ");
    strSQL.append("select e.patient_id,e.encounter_datetime as Date_Started_ART ");
    strSQL.append("from encounter e ");
    strSQL.append(/*from   ww w.  j  a  v a2  s. com*/
            "join obs o on o.encounter_id=e.encounter_id and o.voided=0 and o.concept_id=1255 and o.value_coded=1256 ");
    strSQL.append("left outer join x_concept_name cn on cn.concept_id=o.value_coded ");
    strSQL.append("where e.location_id=:locationId ");
    //strSQL.append("and date(e.encounter_datetime) between '" + startDate +"' and '"+endDate+"' ");
    strSQL.append("and date(e.encounter_datetime) between :startDate and :endDate ");
    strSQL.append("and e.voided=0 ");

    log.info("Executing Query: " + strSQL.toString());

    //Passing parameters to the query
    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString());
    if (startDate != null)
        queryResults.setDate("startDate", startDate);
    if (endDate != null)
        queryResults.setDate("endDate", endDate);
    if (locationId != null)
        queryResults.setInteger("locationId", locationId);

    queryResults.setCacheable(true);
    setQueryRst(queryResults);

}

---

From source file:org.openmrs.module.FacesRegister.api.db.hibernate.HibernateRegisterDAO.java

License:Open Source License

public HashMap<Patient, String> getEligibilityCriteria(List<Person> who, Date endDate)//gets reason for eligibility
{
    List<Integer> ids = new ArrayList<Integer>();
    for (Person p : who) //get Ids of all patients from the selected cohort
        ids.add(p.getPersonId());/*from www.j a v  a  2s . c o  m*/
    StringBuilder strSQL = new StringBuilder();
    strSQL.append("select e.patient_id, ");
    strSQL.append("max(if(o.concept_id in (6796,6318,6317),c.name,null)) as Eligible_Thro ");
    strSQL.append("from encounter e ");
    strSQL.append(
            "join obs o on o.encounter_id =e.encounter_id and o.voided=0 and o.concept_id in (6796,6318,6317) ");
    strSQL.append("left outer join concept_name c on c.concept_id=o.value_coded ");
    strSQL.append("where e.voided=0 and date(e.encounter_datetime)<=:endDate and patient_id in (:ids) ");
    strSQL.append("group by e.patient_id; ");

    log.info("Executing Query: " + strSQL.toString());
    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString());

    if (endDate != null)
        queryResults.setDate("endDate", endDate);
    if (who != null)
        queryResults.setParameterList("ids", ids);
    //pass parameters to the query

    HashMap<Patient, String> eligibility = new HashMap<Patient, String>();
    List list = queryResults.list(); //convert the generated list into a list object
    Iterator it = list.iterator();
    while (it.hasNext()) {
        Object[] row = (Object[]) it.next();
        Patient p = new Patient();
        p = Context.getPatientService().getPatient((Integer) row[0]); //call openmrs API
        eligibility.put(p, (String) row[1]);
    }
    //List<Obs> obs=Context.getObsService().getObservationsByPersonAndConcept(who, question);
    return eligibility;
}

---

From source file:org.openmrs.module.FacesRegister.api.db.hibernate.HibernateRegisterDAO.java

License:Open Source License

public HashMap<Patient, String> getWHOatStart(List<Person> who, Date fromDate, Date toDate) {
    List<Integer> ids = new ArrayList<Integer>();
    for (Person p : who) //get Ids of all patients from the selected cohort
        ids.add(p.getPersonId());/*from www.ja v a  2  s.c o  m*/
    StringBuilder strSQL = new StringBuilder();
    strSQL.append("select e.patient_id, ");
    strSQL.append(
            "mid(max(concat(e.encounter_datetime,if(o.concept_id in(6745,6794,1901,5356,6377),c.name,null))),20)as Who_Stage ");
    strSQL.append("from encounter e ");
    strSQL.append(
            "join obs o on o.encounter_id =e.encounter_id and o.voided=0 and o.concept_id in (6745,6794,1901,5356,6377) ");
    strSQL.append(
            "left outer join concept_name c on c.concept_id=o.value_coded and c.concept_name_type='SHORT' ");
    strSQL.append(
            "where e.voided=0 and date(e.encounter_datetime) between :fromDate and :toDate and patient_id in (:ids) ");
    strSQL.append("group by e.patient_id; ");
    log.info("Executing Query: " + strSQL.toString());
    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString());

    //pass parameters to the query
    if (fromDate != null)
        queryResults.setDate("fromDate", fromDate);
    if (toDate != null)
        queryResults.setDate("toDate", toDate);
    if (who != null)
        queryResults.setParameterList("ids", ids);

    HashMap<Patient, String> whoStage = new HashMap<Patient, String>();
    List list = queryResults.list(); //convert the generated list into a list object

    Iterator it = list.iterator();
    while (it.hasNext()) {
        Object[] row = (Object[]) it.next();
        Patient p = new Patient();
        p = Context.getPatientService().getPatient((Integer) row[0]); //call openmrs API
        whoStage.put(p, (String) row[1]);
    }
    //List<Obs> obs=Context.getObsService().getObservationsByPersonAndConcept(who, question);
    return whoStage;

}

---

From source file:org.openmrs.module.FacesRegister.api.db.hibernate.HibernateRegisterDAO.java

License:Open Source License

public HashMap<Patient, String> getWeightatStart(List<Person> who, Date fromDate, Date toDate) {
    List<Integer> ids = new ArrayList<Integer>();
    for (Person p : who) //get Ids of all patients from the selected cohort
        ids.add(p.getPersonId());//from  w ww  . j  a  v a 2 s. c  om
    StringBuilder strSQL = new StringBuilder();
    strSQL.append("select e.patient_id, ");
    strSQL.append(
            "mid(max(concat(e.encounter_datetime,if(o.concept_id in(5089),o.value_numeric,null))),20) as Who_Stage ");
    strSQL.append("from encounter e ");
    strSQL.append("join obs o on o.encounter_id =e.encounter_id and o.voided=0 and o.concept_id in (5089) ");
    strSQL.append(
            "where e.voided=0 and date(e.encounter_datetime) between :fromDate and :toDate and patient_id in (:ids) ");
    strSQL.append("group by e.patient_id; ");
    log.info("Executing Query: " + strSQL.toString());
    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString());

    //pass parameters to the query
    if (fromDate != null)
        queryResults.setDate("fromDate", fromDate);
    if (toDate != null)
        queryResults.setDate("toDate", toDate);
    if (who != null)
        queryResults.setParameterList("ids", ids);

    HashMap<Patient, String> weight = new HashMap<Patient, String>();
    List list = queryResults.list(); //convert the generated list into a list object

    Iterator it = list.iterator();
    while (it.hasNext()) {
        Object[] row = (Object[]) it.next();
        Patient p = new Patient();
        p = Context.getPatientService().getPatient((Integer) row[0]); //call openmrs API
        weight.put(p, (String) row[1]);
    }
    //List<Obs> obs=Context.getObsService().getObservationsByPersonAndConcept(who, question);
    return weight;

}

---

From source file:org.openmrs.module.FacesRegister.api.db.hibernate.HibernateRegisterDAO.java

License:Open Source License

public HashMap<Patient, String> getHeightatStart(List<Person> who, Date fromDate, Date toDate) {
    List<Integer> ids = new ArrayList<Integer>();
    for (Person p : who) //get Ids of all patients from the selected cohort
        ids.add(p.getPersonId());// w  w  w . j av  a 2 s  .c  o m
    StringBuilder strSQL = new StringBuilder();
    strSQL.append("select e.patient_id, ");
    strSQL.append(
            "mid(max(concat(e.encounter_datetime,if(o.concept_id in(6744),o.value_numeric,null))),20) as Who_Stage ");
    strSQL.append("from encounter e ");
    strSQL.append("join obs o on o.encounter_id =e.encounter_id and o.voided=0 and o.concept_id in (6744) ");
    strSQL.append(
            "where e.voided=0 and date(e.encounter_datetime) between :fromDate and :toDate and patient_id in (:ids) ");
    strSQL.append("group by e.patient_id; ");
    log.info("Executing Query: " + strSQL.toString());
    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString());

    //pass parameters to the query
    if (fromDate != null)
        queryResults.setDate("fromDate", fromDate);
    if (toDate != null)
        queryResults.setDate("toDate", toDate);
    if (who != null)
        queryResults.setParameterList("ids", ids);

    HashMap<Patient, String> height = new HashMap<Patient, String>();
    List list = queryResults.list(); //convert the generated list into a list object

    Iterator it = list.iterator();
    while (it.hasNext()) {
        Object[] row = (Object[]) it.next();
        Patient p = new Patient();
        p = Context.getPatientService().getPatient((Integer) row[0]); //call openmrs API
        height.put(p, (String) row[1]);
    }
    //List<Obs> obs=Context.getObsService().getObservationsByPersonAndConcept(who, question);
    return height;

}

---

From source file:org.openmrs.module.FacesRegister.api.db.hibernate.HibernateRegisterDAO.java

License:Open Source License

public HashMap<Patient, String> getCD4atStart(List<Person> who, Date fromDate, Date toDate) {
    List<Integer> ids = new ArrayList<Integer>();
    for (Person p : who) //get Ids of all patients from the selected cohort
        ids.add(p.getPersonId());//from   w  w  w  .  java2 s  . co m
    StringBuilder strSQL = new StringBuilder();
    strSQL.append("select e.patient_id, ");
    strSQL.append(
            "mid(max(concat(e.encounter_datetime,if(o.concept_id in(5497,6314),o.value_numeric,null))),20) as CD4_Count ");
    strSQL.append("from encounter e ");
    strSQL.append(
            "join obs o on o.encounter_id =e.encounter_id and o.voided=0 and o.concept_id in (5497,6314) ");
    strSQL.append("where e.voided=0 and date(e.encounter_datetime)<=:toDate and e.patient_id in (:ids) ");
    strSQL.append("group by e.patient_id; ");

    log.info("Executing Query: " + strSQL.toString());
    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString());
    //pass parameters to the query
    if (toDate != null)
        queryResults.setDate("toDate", toDate);
    if (who != null)
        queryResults.setParameterList("ids", ids);

    HashMap<Patient, String> cd4 = new HashMap<Patient, String>();
    List list = queryResults.list(); //convert the generated list into a list object

    Iterator it = list.iterator();
    while (it.hasNext()) {
        Object[] row = (Object[]) it.next();
        Patient p = new Patient();
        p = Context.getPatientService().getPatient((Integer) row[0]); //call openmrs API
        if (row[1] != null)
            cd4.put(p, (String) row[1]);
        else
            cd4.put(p, null);

    }

    return cd4;

}

---

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

License:Open Source License

public Cohort getPatientsWithAgeRange(Integer minAge, DurationUnit minAgeUnit, Integer maxAge,
        DurationUnit maxAgeUnit, boolean unknownAgeIncluded, Date effectiveDate) {

    if (effectiveDate == null) {
        effectiveDate = new Date();
    }// w  ww . jav a  2  s .c  o m
    if (minAgeUnit == null) {
        minAgeUnit = DurationUnit.YEARS;
    }
    if (maxAgeUnit == null) {
        maxAgeUnit = DurationUnit.YEARS;
    }

    String sql = "select t.patient_id from patient t, person p where t.patient_id = p.person_id and t.voided = false and ";
    Map<String, Date> paramsToSet = new HashMap<String, Date>();

    Date maxBirthFromAge = effectiveDate;
    if (minAge != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(effectiveDate);
        cal.add(minAgeUnit.getCalendarField(), -minAgeUnit.getFieldQuantity() * minAge);
        maxBirthFromAge = cal.getTime();
    }

    String c = "p.birthdate <= :maxBirthFromAge";
    paramsToSet.put("maxBirthFromAge", maxBirthFromAge);

    Date minBirthFromAge = null;
    if (maxAge != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(effectiveDate);
        cal.add(maxAgeUnit.getCalendarField(), -(maxAgeUnit.getFieldQuantity() * maxAge + 1));
        minBirthFromAge = cal.getTime();
        c = "(" + c + " and p.birthdate >= :minBirthFromAge)";
        paramsToSet.put("minBirthFromAge", minBirthFromAge);
    }

    if (unknownAgeIncluded) {
        c = "(p.birthdate is null or " + c + ")";
    }

    sql += c;

    log.debug("Executing: " + sql + " with params: " + paramsToSet);

    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    for (Map.Entry<String, Date> entry : paramsToSet.entrySet()) {
        query.setDate(entry.getKey(), entry.getValue());
    }

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

---

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

License:Open Source License

/**
 * select orders.patient_id from drug, drug_order, orders where
 * drug_order.order_id = orders.order_id and drug.drug_id =
 * drug_order.drug_inventory_id and drug.drug_id IN (:drugIds) and
 * (discontinued_date >= '2006-01-01' and discontinued_date <= '2006-01-31')
 * /*from  ww w.  ja  v a  2  s  .  co m*/
 * @param drugs
 *            the list of drugs to match against patient
 * @param whichColumn
 *            the start_date or discontinued_date
 * @param changedOnOrAfter
 *            the patient started or stopped the given drug(s) after this
 *            date
 * @param changedOnOrBefore
 *            the patient started or stopped the given drug(s) before this
 *            date
 * @return a cohort of patients that started or stopped the given list of
 *         drugs between the given dates
 */
private Cohort getPatientsHavingStartedOrCompletedDrugOrders(List<Drug> drugs, String whichColumn,
        Date changedOnOrAfter, Date changedOnOrBefore) {

    List<Integer> drugIds = new ArrayList<Integer>();
    for (Drug drug : drugs)
        drugIds.add(drug.getDrugId());

    // Create SQL query
    StringBuilder sql = new StringBuilder();
    sql.append("select orders.patient_id ");
    sql.append("from drug, drug_order, orders ");
    sql.append("where orders.order_id = drug_order.order_id ");
    sql.append("and drug.drug_id = drug_order.drug_inventory_id ");
    if (drugIds != null && !drugIds.isEmpty()) {
        sql.append("and drug_order.drug_inventory_id in (:drugIds) ");
    }
    sql.append("and orders." + whichColumn + " is not null ");
    if (changedOnOrAfter != null) {
        sql.append("and orders." + whichColumn + " >= :changedOnOrAfter ");
    }
    if (changedOnOrBefore != null) {
        sql.append("and orders." + whichColumn + " <= :changedOnOrBefore ");
    }
    sql.append("and drug.retired = false ");
    sql.append("and orders.voided = false ");
    sql.append("group by orders.patient_id ");
    log.warn("query: " + sql);

    // Execute query
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());

    log.debug("Patients having started or stopped drug orders between dates:\n " + query.getQueryString());

    if (drugIds != null && !drugIds.isEmpty())
        query.setParameterList("drugIds", drugIds);
    if (changedOnOrAfter != null)
        query.setDate("changedOnOrAfter", changedOnOrAfter);
    if (changedOnOrBefore != null)
        query.setDate("changedOnOrBefore", changedOnOrBefore);
    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 a 2 s  .co m*/
 * @param drugs
 *            the list of drugs to match against patient
 * @param asOfDate
 *            the date up to which the patient should be on the given drugs
 * @return a cohort of patients that started or stopped the given list of
 *         drugs between the given dates
 */
public Cohort getPatientsHavingActiveDrugOrders(List<Drug> drugs, Date asOfDate) {
    List<Integer> drugIds = new ArrayList<Integer>();
    for (Drug drug : drugs)
        drugIds.add(drug.getDrugId());

    // Create SQL query
    StringBuilder sql = new StringBuilder();
    sql.append("select orders.patient_id ");
    sql.append("from drug, drug_order, orders ");
    sql.append("where orders.order_id = drug_order.order_id ");
    sql.append("and drug.drug_id = drug_order.drug_inventory_id ");
    if (drugIds != null && !drugIds.isEmpty()) {
        sql.append("and drug_order.drug_inventory_id in (:drugIds) ");
    }
    sql.append("and orders.start_date is not null ");
    if (asOfDate != null) {
        sql.append("and orders.start_date <= :asOfDate ");
        sql.append("and (orders.auto_expire_date is null or orders.auto_expire_date > :asOfDate) ");
        sql.append("and (orders.discontinued_date is null or orders.discontinued_date > :asOfDate) ");
    }
    sql.append("and drug.retired = false ");
    sql.append("and orders.voided = false ");
    sql.append("group by orders.patient_id ");
    log.warn("query: " + sql);

    // Execute query
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());

    log.debug("Patients having active drug orders between dates:\n " + query.getQueryString());

    if (drugIds != null && !drugIds.isEmpty())
        query.setParameterList("drugIds", drugIds);
    if (asOfDate != null)
        query.setDate("asOfDate", asOfDate);
    return new Cohort(query.list());
}

---

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

License:Open Source License

/**
 * TODO: Fails to leave out patients who are voided.  
 * //  ww w.  j  a  v a2  s  .c om
 * Returns the set of patients that are in a given program, 
 * workflow, and state, within a given date range
 * 
 * @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 ");
    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());

    log.debug("Patients having programs and states between dates: " + query.getQueryString());

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

---

• «
• 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
• »