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

License:Mozilla Public License

/**
 * Returns a Map from patientId to a Collection of drugIds for drugs active for the patients on
 * that date If patientIds is null then do this for all patients Does not return anything for
 * voided patients/*from  w  w w  . j  a v a 2s .  co m*/
 * 
 * @throws DAOException
 */
@SuppressWarnings("unchecked")
public Map<Integer, Collection<Integer>> getActiveDrugIds(Collection<Integer> patientIds, Date fromDate,
        Date toDate) throws DAOException {
    Set<Integer> idsLookup = patientIds == null ? null
            : (patientIds instanceof HashSet ? (HashSet<Integer>) patientIds
                    : new HashSet<Integer>(patientIds));

    Map<Integer, Collection<Integer>> ret = new HashMap<Integer, Collection<Integer>>();

    List<String> whereClauses = new ArrayList<String>();
    whereClauses.add("o.voided = false");
    if (toDate != null) {
        whereClauses.add("o.date_activated <= :toDate");
    }
    if (fromDate != null) {
        whereClauses.add("(o.auto_expire_date is null or o.auto_expire_date > :fromDate)");
        whereClauses.add("(o.date_stopped is null or o.date_stopped > :fromDate)");
    }

    StringBuilder sql = new StringBuilder("select o.patient_id, d.drug_inventory_id " + "from orders o "
            + "    inner join patient p on o.patient_id = p.patient_id and p.voided = false "
            + "    inner join drug_order d on o.order_id = d.order_id ");
    for (ListIterator<String> i = whereClauses.listIterator(); i.hasNext();) {
        sql.append((i.nextIndex() == 0 ? " where " : " and ")).append(i.next());
    }

    log.debug("sql= " + sql);

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

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

    List<Object[]> results = (List<Object[]>) query.list();
    for (Object[] row : results) {
        Integer patientId = (Integer) row[0];
        if (idsLookup == null || idsLookup.contains(patientId)) {
            Integer drugId = (Integer) row[1];
            Collection<Integer> drugIds = ret.get(patientId);
            if (drugIds == null) {
                drugIds = new HashSet<Integer>();
                ret.put(patientId, drugIds);
            }
            drugIds.add(drugId);
        }
    }
    return ret;
}

---

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

License:Mozilla Public License

public Cohort getPatientsHavingDrugOrder(List<Drug> drugList, List<Concept> drugConceptList, Date startDateFrom,
        Date startDateTo, Date stopDateFrom, Date stopDateTo, Boolean discontinued, List<Concept> orderReason) {
    if (drugList != null && drugList.size() == 0) {
        drugList = null;/*w w  w.  j a  v  a 2 s  .com*/
    }
    if (drugConceptList != null && drugConceptList.size() == 0) {
        drugConceptList = null;
    }
    StringBuilder sb = new StringBuilder();
    sb.append(" select distinct patient.id from DrugOrder where voided = false and patient.voided = false ");
    if (drugList != null) {
        sb.append(" and drug.id in (:drugIdList) ");
    }
    if (drugConceptList != null) {
        sb.append(" and concept.id in (:drugConceptIdList) ");
    }
    if (startDateFrom != null && startDateTo != null) {
        sb.append(" and dateActivated between :startDateFrom and :startDateTo ");
    } else {
        if (startDateFrom != null) {
            sb.append(" and dateActivated >= :startDateFrom ");
        }
        if (startDateTo != null) {
            sb.append(" and dateActivated <= :startDateTo ");
        }
    }
    if (orderReason != null && orderReason.size() > 0) {
        sb.append(" and orderReason.id in (:orderReasonIdList) ");
    }
    if (discontinued != null) {
        if (discontinued) {
            if (stopDateFrom != null && stopDateTo != null) {
                sb.append(" and dateStopped between :stopDateFrom and :stopDateTo ");
            } else {
                if (stopDateFrom != null) {
                    sb.append(" and dateStopped >= :stopDateFrom ");
                }
                if (stopDateTo != null) {
                    sb.append(" and dateStopped <= :stopDateTo ");
                }
            }
        } else { // discontinued == false
            if (stopDateFrom != null && stopDateTo != null) {
                sb.append(" and autoExpireDate between :stopDateFrom and :stopDateTo ");
            } else {
                if (stopDateFrom != null) {
                    sb.append(" and autoExpireDate >= :stopDateFrom ");
                }
                if (stopDateTo != null) {
                    sb.append(" and autoExpireDate <= :stopDateTo ");
                }
            }
        }
    } else { // discontinued == null, so we need either
        if (stopDateFrom != null && stopDateTo != null) {
            sb.append(" and coalesce(dateStopped, autoExpireDate) between :stopDateFrom and :stopDateTo ");
        } else {
            if (stopDateFrom != null) {
                sb.append(" and coalesce(dateStopped, autoExpireDate) >= :stopDateFrom ");
            }
            if (stopDateTo != null) {
                sb.append(" and coalesce(dateStopped, autoExpireDate) <= :stopDateTo ");
            }
        }
    }
    log.debug("sql = " + sb);
    Query query = sessionFactory.getCurrentSession().createQuery(sb.toString());

    if (drugList != null) {
        List<Integer> ids = new ArrayList<Integer>();
        for (Drug d : drugList) {
            ids.add(d.getDrugId());
        }
        query.setParameterList("drugIdList", ids);
    }
    if (drugConceptList != null) {
        List<Integer> ids = new ArrayList<Integer>();
        for (Concept c : drugConceptList) {
            ids.add(c.getConceptId());
        }
        query.setParameterList("drugConceptIdList", ids);
    }
    if (startDateFrom != null) {
        query.setDate("startDateFrom", startDateFrom);
    }
    if (startDateTo != null) {
        query.setDate("startDateTo", startDateTo);
    }
    if (stopDateFrom != null) {
        query.setDate("stopDateFrom", stopDateFrom);
    }
    if (stopDateTo != null) {
        query.setDate("stopDateTo", stopDateTo);
    }
    if (discontinued != null) {
        query.setBoolean("discontinued", discontinued);
    }
    if (orderReason != null && orderReason.size() > 0) {
        List<Integer> ids = new ArrayList<Integer>();
        for (Concept c : orderReason) {
            ids.add(c.getConceptId());
        }
        query.setParameterList("orderReasonIdList", ids);
    }

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

---

From source file:org.openmrs.module.amrsreports.db.hibernate.MohHibernateCoreDAO.java

License:Open Source License

private void applySubstitutions(Query q, Map<String, Object> substitutions) {
    for (Map.Entry<String, Object> e : substitutions.entrySet()) {
        if (e.getValue() instanceof Collection) {
            q.setParameterList(e.getKey(), (Collection) e.getValue());
        } else if (e.getValue() instanceof Object[]) {
            q.setParameterList(e.getKey(), (Object[]) e.getValue());
        } else if (e.getValue() instanceof Cohort) {
            q.setParameterList(e.getKey(), ((Cohort) e.getValue()).getMemberIds());
        } else if (e.getValue() instanceof Date) {
            q.setDate(e.getKey(), (Date) e.getValue());
        } else {//from  w  w  w .j a  va  2s . c o  m
            q.setParameter(e.getKey(), e.getValue());
        }
    }
}

---

From source file:org.openmrs.module.clinicalsummary.db.hibernate.HibernateIndexDAO.java

License:Open Source License

/**
 * @see IndexDAO#saveInitialDate(org.openmrs.Location, java.util.Date)
 *//*from   w ww  .j  a v a 2  s  . c o m*/
@Override
public Integer saveInitialDate(final Location location, final Date date) throws DAOException {
    String hqlString = "update Index index set index.initialDate = :initialDate where index.location = :location";
    Query query = sessionFactory.getCurrentSession().createQuery(hqlString);
    query.setDate("initialDate", date);
    query.setParameter("location", location);
    return query.executeUpdate();
}

---

From source file:org.openmrs.module.facesreception.api.db.hibernate.HibernateFacesReceptionDAO.java

License:Open Source License

public List<DemoPerson> getPersonList(Date startDob, Date endDob) {
    List<DemoPerson> listofPateints = new ArrayList<DemoPerson>();
    StringBuilder strSQL = new StringBuilder();
    strSQL.append(/*from w w w.  ja va2s.co  m*/
            "select p.person_id,p.birthdate,p.gender,concat_ws(' ',pn.given_name,pn.family_name) as name ");
    strSQL.append("from person p ");
    strSQL.append("join person_name pn on pn.person_id=p.person_id");
    strSQL.append("where p.voided=0 and birthdate between :startDate and :endDate");

    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString());

    //Passing Paramenters
    if (startDob != null) {
        queryResults.setDate("birthdate", startDob);
    }
    if (endDob != null) {
        queryResults.setDate("birthdate", endDob);
    }

    List list = queryResults.list();
    Iterator it = list.iterator();
    while (it.hasNext()) {
        Object[] row = (Object[]) it.next();
        DemoPerson p = new DemoPerson();
        p.setPersonID((Integer) row[0]);
        p.setDOB((Date) row[1]);
        p.setGender((String) row[2]);
        p.setPersonName((String) row[3]);
        listofPateints.add(p);
    }
    return listofPateints;

}

---

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

License:Open Source License

public HashMap<Patient, String> getPatientStatus(List<Person> who, Date endDate, Integer monthN) {
    HashMap<Patient, String> patientStatusList = new HashMap<Patient, String>();
    List<Integer> ids = new ArrayList<Integer>();
    for (Person p : who) {
        ids.add(p.getPersonId()); //cast int to Integer
    }//  www.  j a va 2  s  . c  om
    StringBuilder strSQL = new StringBuilder();
    strSQL.append(
            "select patient_id,if(Patient_Status is not null,Patient_Status,if(mths<=3,'ACTIVE','LOST') ) as P_Status ");
    strSQL.append("from ");
    strSQL.append("(");
    strSQL.append(
            "select e.patient_id,max(e.encounter_datetime) as Enc_date, datediff(date_add(:endDate, interval :n1 month),max(e.encounter_datetime)) div (365.25 div 12)  as mths, ");
    strSQL.append("mid(max(concat(encounter_datetime,if(o.concept_id=1655, case o.value_coded ");
    strSQL.append("               when 1650 then 'DEATH' ");
    strSQL.append("              when 1654 then 'LOST' ");
    strSQL.append("              when 1652 then 'TO' ");
    strSQL.append("              when 1653 then 'TO' ");
    strSQL.append("              when 1809 then 'STOP' ");
    strSQL.append("              when 1650 then 'DEATH' ");
    strSQL.append("            when 1650 then 'Other' end,null))),20) as Patient_Status ");
    strSQL.append("from encounter e ");
    strSQL.append("join patient p on p.patient_id=e.patient_id and p.voided =0 ");
    strSQL.append("join person pa on pa.person_id=e.patient_id and pa.voided=0 ");
    strSQL.append(
            "left outer join obs o on  o.encounter_id=e.encounter_id and o.concept_id=1655 and o.voided=0 ");
    strSQL.append(
            "left outer join concept_name cn on cn.concept_id=o.value_coded and cn.concept_name_type='FULLY_SPECIFIED' ");
    strSQL.append(
            "where e.voided=0 and e.encounter_datetime <= date_add(:endDate, interval :n1 month) and e.patient_id in (:ids) ");
    strSQL.append("group by e.patient_id)x; ");

    log.info("Executing Query: " + strSQL.toString());
    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString());
    //Pass Parameters
    if (who != null)
        queryResults.setParameterList("ids", ids);
    if (endDate != null)
        queryResults.setDate("endDate", endDate);
    if (monthN != null)
        queryResults.setInteger("n1", monthN);

    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)
            patientStatusList.put(p, (String) row[1]);
        else
            patientStatusList.put(p, null);
    }
    return patientStatusList;
}

---

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

License:Open Source License

public HashMap<Patient, PatientStatus> getSixMonthStatus(List<Person> who, Date startDate, Integer n1,
        Integer n2) {//from   ww  w .  j  a va2  s.  c o  m
    HashMap<Patient, PatientStatus> statusList = new HashMap<Patient, PatientStatus>();
    List<Integer> ids = new ArrayList<Integer>();
    for (Person p : who) {
        ids.add(p.getPersonId()); //cast int to Integer
    }
    StringBuilder strSQL = new StringBuilder();
    strSQL.append(
            "select e.patient_id, mid(max(concat(e.encounter_datetime,if(o.concept_id in (6150),cn.name,null))),20) as TB_Status, ");
    strSQL.append(
            "mid(max(concat(e.encounter_datetime,if(o.concept_id in (5089),o.value_numeric,null))),20) as weight, ");
    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 person pa on pa.person_id=e.patient_id and pa.voided=0 ");
    strSQL.append("join patient p on p.patient_id=e.patient_id and p.voided=0 ");
    strSQL.append(
            "join obs o on o.encounter_id=e.encounter_id and o.concept_id in (6150,5089,5497,6314) and o.voided=0 ");
    strSQL.append(
            "left outer join concept_name cn on cn.concept_id=o.value_coded and cn.concept_name_type='FULLY_SPECIFIED' ");
    strSQL.append("where e.voided=0 and e.patient_id in (:ids) ");
    strSQL.append(
            "and e.encounter_datetime>= date_add(:startDate, interval :n1 month) and e.encounter_datetime< date_add(:startDate, interval :n2 month) ");
    strSQL.append("group by e.patient_id;");
    log.info("Executing Query: " + strSQL.toString());
    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString());

    //Pass Parameters
    if (who != null)
        queryResults.setParameterList("ids", ids);
    if (startDate != null)
        queryResults.setDate("startDate", startDate);
    if (n1 != null)
        queryResults.setInteger("n1", n1);
    if (n2 != null)
        queryResults.setInteger("n2", n2);

    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
        PatientStatus patStatus = new PatientStatus();
        patStatus.setPatient(p);
        if (row[1] != null)
            patStatus.setTbStatus((String) row[1]);
        else
            patStatus.setTbStatus(null);
        if (row[2] != null)
            patStatus.setWeight((String) row[2]);
        else
            patStatus.setWeight(null);

        if (row[3] != null)
            patStatus.setCd4((String) row[3]);
        else
            patStatus.setCd4(null);

        statusList.put(p, patStatus);
    }
    return statusList;

}

---

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

License:Open Source License

public Integer nMonths(Date endDate) {
    /*//from  ww  w  .  j  a  v  a  2 s .c om
     * Get the Number of months since the start of ART
     */
    StringBuilder strSQL = new StringBuilder();
    strSQL.append(
            "select datediff(curdate(),date_add(:endDate,interval 1 day))div (365.25 div 12) as Nmonths;");

    log.info("Executing Query: " + strSQL.toString());
    Query queryResults = sessionFactory.getCurrentSession().createSQLQuery(strSQL.toString())
            .addScalar("Nmonths", Hibernate.INTEGER);

    //Pass Parameters
    if (endDate != null)
        queryResults.setDate("endDate", endDate);

    Integer n = 0;
    List list = queryResults.list(); //convert the generated list into a list object
    n = (Integer) list.get(0);
    /*
    Iterator it = list.iterator();
      while(it.hasNext()){
     Object[] row = (Object[])it.next();
        n = ((Integer) row[0]).intValue();    //call openmrs API
         }
         */
    return n;

}

---

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

License:Open Source License

public HashMap<Patient, String> getMonthlyReg(List<Person> who, Date endDate, Integer monthN) {
    List<Integer> ids = new ArrayList<Integer>();
    for (Person p : who) {
        ids.add(p.getPersonId()); //cast int to Integer
    }/*from   w w w. j  a v a 2  s.c o  m*/
    StringBuilder strSQL = new StringBuilder();
    strSQL.append("select x.patient_id, mid(max(x.Reg1),20) as reg ");
    strSQL.append("from ");
    strSQL.append("( ");
    strSQL.append("select e.encounter_id,e.patient_id, ");
    strSQL.append(
            "concat(e.encounter_datetime,if(o.concept_id=1571,group_concat(distinct c.name order by c.name asc),null)) as Reg1 ");
    strSQL.append("from encounter e ");
    strSQL.append("join obs o on o.encounter_id=e.encounter_id and o.concept_id in (1571) and o.voided=0 ");
    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 e.patient_id in (:ids) and e.encounter_datetime between date_add(:endDate, interval 1 day) and date_add(:endDate, interval :monthN month) ");
    strSQL.append("group by  e.encounter_Id)x ");
    strSQL.append("group by x.patient_id ");

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

    //Pass Parameters
    if (who != null)
        queryResults.setParameterList("ids", ids);
    if (endDate != null)
        queryResults.setDate("endDate", endDate);
    if (monthN != null)
        queryResults.setInteger("monthN", monthN);

    HashMap<Patient, String> patientRegList = 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)
            patientRegList.put(p, (String) row[1]);
        else
            patientRegList.put(p, null);
    }
    return patientRegList;

}

---

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

License:Open Source License

public HashMap<Patient, String> getPatientsGivenArtDates(Integer locationId, Date startDate, Date endDate) {

    StringBuilder strSQL = new StringBuilder();
    strSQL.append(//www  .  ja  v a2s .  c om
            "select e.patient_id, max(if(o.concept_id in (6746,6739),date(o.value_datetime),null)) as Date_Started_ART,max(if(o.concept_id=1592 and o.value_coded=1405,1,0))as PPCT ");
    strSQL.append("from encounter e ");
    strSQL.append("join obs o on o.encounter_id=e.encounter_id and o.concept_id in (6746,6739,1592) ");
    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("group by e.patient_id ");
    strSQL.append("having PPCT=0 and Date_Started_ART is not Null ");
    strSQL.append("union ");
    strSQL.append(
            "select e.patient_id,max(if(o.concept_id=1255 and o.value_coded=1256,e.encounter_datetime,null)) as Date_Started_ART,max(if(o.concept_id=1592 and o.value_coded=1405,1,0))as PPCT ");
    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(1255,1592) and o.value_coded in (1256,1405,1407)");
    strSQL.append("left outer join 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 ");
    strSQL.append("group by e.patient_id ");
    strSQL.append("having PPCT=0 and Date_Started_ART is not Null ");

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

    //pass parameters to the query
    if (startDate != null)
        queryResults.setDate("startDate", startDate);
    if (endDate != null)
        queryResults.setDate("endDate", endDate);
    if (locationId != null)
        queryResults.setInteger("locationId", locationId);

    HashMap<Patient, String> patientStartDate = new HashMap<Patient, String>(); //patients art start date
    List<Patient> patient = new ArrayList<Patient>();

    //query(locationId,startDate,endDate);
    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
        patient.add(p);
        patientStartDate.put(p, (String) row[1].toString());

    }
    return patientStartDate;
}

---

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