List of usage examples for org.hibernate SQLQuery setInteger
@Deprecated @SuppressWarnings("unchecked") default Query<R> setInteger(int position, int val)
From source file:org.openmrs.module.mirebalaisreports.cohort.definition.evaluator.LastDispositionBeforeExitCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition cohortDefinition, EvaluationContext context) throws EvaluationException { LastDispositionBeforeExitCohortDefinition cd = (LastDispositionBeforeExitCohortDefinition) cohortDefinition; Location exitFromWard = cd.getExitFromWard(); List<Concept> dispositions = cd.getDispositions(); List<Concept> dispositionsToConsider = cd.getDispositionsToConsider(); String sql = "select distinct v.patient_id " + "from visit v " + "inner join encounter exit_encounter " + " on exit_encounter.visit_id = v.visit_id " + " and exit_encounter.voided = false " + " and exit_encounter.encounter_type = :exitEncounterType " + " and exit_encounter.encounter_datetime between :exitOnOrAfter and :exitOnOrBefore "; if (exitFromWard != null) { sql += " and exit_encounter.location_id = :exitFromWard "; }//from ww w . j a v a 2s .c om sql += "inner join encounter obs_encounter " + " on obs_encounter.visit_id = v.visit_id " + " and obs_encounter.encounter_id = (" + " select find_obs_encounter.encounter_id " + " from encounter find_obs_encounter " + " inner join obs has_obs " + " on has_obs.encounter_id = find_obs_encounter.encounter_id " + " and has_obs.voided = false " + " and has_obs.concept_id = :dispositionConcept "; if (dispositionsToConsider != null) { sql += " and has_obs.value_coded in (:dispositionsToConsider) "; } sql += " where find_obs_encounter.visit_id = v.visit_id " + " and find_obs_encounter.voided = false " + " order by find_obs_encounter.encounter_datetime desc, find_obs_encounter.date_created desc limit 1 " + // if we wanted to require disposition at the same location as exit // " and find_obs_encounter.location_id = :exitFromWard " + " )" + "inner join obs o " + " on o.voided = false " + " and o.concept_id = :dispositionConcept " + " and o.encounter_id = obs_encounter.encounter_id " + "where v.voided = false " + " and o.value_coded in (:dispositions) "; SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql); query.setInteger("dispositionConcept", dispositionService.getDispositionDescriptor().getDispositionConcept().getId()); query.setParameterList("dispositions", idList(dispositions)); query.setInteger("exitEncounterType", emrApiProperties.getExitFromInpatientEncounterType().getId()); query.setTimestamp("exitOnOrAfter", cd.getExitOnOrAfter()); query.setTimestamp("exitOnOrBefore", cd.getExitOnOrBefore()); if (exitFromWard != null) { query.setInteger("exitFromWard", exitFromWard.getId()); } if (dispositionsToConsider != null) { query.setParameterList("dispositionsToConsider", idList(dispositionsToConsider)); } Cohort c = new Cohort(); for (Integer i : (List<Integer>) query.list()) { c.addMember(i); } return new EvaluatedCohort(c, cohortDefinition, context); }
From source file:org.openmrs.module.mirebalaisreports.dataset.definition.evaluator.NonCodedDiagnosisDataSetEvaluator.java
License:Open Source License
@Override public DataSet evaluate(DataSetDefinition dataSetDefinition, EvaluationContext context) throws EvaluationException { Long startTime = new Date().getTime(); NonCodedDiagnosisDataSetDefinition dsd = (NonCodedDiagnosisDataSetDefinition) dataSetDefinition; Date fromDate = ObjectUtil.nvl(dsd.getFromDate(), DateUtils.addDays(new Date(), -7)); Date toDate = ObjectUtil.nvl(dsd.getToDate(), new Date()); fromDate = DateUtil.getStartOfDay(fromDate); toDate = DateUtil.getEndOfDay(toDate); String nonCoded = ObjectUtil.nvl(dsd.getNonCoded(), null); Provider provider = ObjectUtil.nvl(dsd.getProvider(), null); Integer userId = null;//from w w w. j a va2 s .c om if (provider != null) { List<User> users = userService.getUsersByPerson(provider.getPerson(), true); if (users != null && users.size() > 0) { userId = users.get(0).getId(); } } PatientIdentifierType primaryIdentifierType = emrApiProperties.getPrimaryIdentifierType(); Concept nonCodedConcept = emrApiProperties.getDiagnosisMetadata().getNonCodedDiagnosisConcept(); StringBuilder sqlQuery = new StringBuilder( "select " + " o.value_text as 'nonCodedDiagnosis', " + " o.creator as 'creatorId', " + " n.given_name as 'creatorFirstName', " + " n.family_name as 'creatorLastName', " + " o.date_created as 'dateCreated', " + " o.person_id as 'patientId', " + " id1.identifier as 'patientIdentifier', " + " o.obs_id as 'obsId', " + " e.visit_id as 'visitId', " + " e.encounter_datetime as 'encounterDateTime', " + " n1.given_name as 'patientFirstName', " + " n1.family_name as 'patientLastName'"); sqlQuery.append(" from obs o "); sqlQuery.append( " inner join patient_identifier as id1 on (o.person_id = id1.patient_id and id1.identifier_type = :primaryIdentifierType ) "); sqlQuery.append(" inner join encounter as e on (o.encounter_id = e.encounter_id) "); sqlQuery.append(" inner join users as u on (o.creator = u.user_id) "); sqlQuery.append(" inner join person_name as n on (u.person_id = n.person_id and n.voided=0) "); sqlQuery.append(" inner join person_name as n1 on (o.person_id = n1.person_id and n1.voided=0) "); sqlQuery.append(" "); sqlQuery.append(" where o.voided = 0 "); sqlQuery.append(" and o.concept_id = :nonCodedConcept "); if (fromDate != null) { sqlQuery.append(" and o.date_created > :startDate "); } if (toDate != null) { sqlQuery.append(" and o.date_created < :endDate "); } if (userId != null) { sqlQuery.append(" and o.creator = :userId "); } if (StringUtils.isNotBlank(nonCoded)) { sqlQuery.append(" and o.value_text like '%").append(nonCoded).append("%'"); } SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery.toString()); query.setInteger("primaryIdentifierType", primaryIdentifierType.getId()); query.setInteger("nonCodedConcept", nonCodedConcept.getId()); if (fromDate != null) { query.setTimestamp("startDate", fromDate); } if (toDate != null) { query.setTimestamp("endDate", toDate); } if (userId != null) { query.setInteger("userId", userId); } List<Object[]> list = query.list(); SimpleDataSet dataSet = new SimpleDataSet(dataSetDefinition, context); for (Object[] o : list) { DataSetRow row = new DataSetRow(); row.addColumnValue(new DataSetColumn("nonCodedDiagnosis", "nonCodedDiagnosis", String.class), o[0]); row.addColumnValue(new DataSetColumn("creatorId", "creatorId", String.class), o[1]); row.addColumnValue(new DataSetColumn("creatorFirstName", "creatorFirstName", String.class), o[2]); row.addColumnValue(new DataSetColumn("creatorLastName", "creatorLastName", String.class), o[3]); row.addColumnValue(new DataSetColumn("dateCreated", "dateCreated", String.class), o[4]); row.addColumnValue(new DataSetColumn("patientId", "patientId", String.class), o[5]); row.addColumnValue(new DataSetColumn("patientIdentifier", "patientIdentifier", String.class), o[6]); row.addColumnValue(new DataSetColumn("obsId", "obsId", String.class), o[7]); row.addColumnValue(new DataSetColumn("visitId", "visitId", String.class), o[8]); row.addColumnValue(new DataSetColumn("encounterDateTime", "encounterDateTime", String.class), o[9]); row.addColumnValue(new DataSetColumn("patientFirstName", "patientFirstName", String.class), o[10]); row.addColumnValue(new DataSetColumn("patientLastName", "patientLastName", String.class), o[11]); dataSet.addRow(row); } return dataSet; }
From source file:org.openmrs.module.nbs.datasource.HibernateLogicProviderDAO.java
License:Open Source License
public List<Integer> getAllProviders(Integer patientId, ArrayList<Integer> encounterList) { String encounterRestrictions = ""; if (encounterList != null && encounterList.size() == 0) { return null; }/*from www . j a v a 2 s.c o m*/ if (encounterList != null) { encounterRestrictions = " and encounter_id in (:encounterList)"; } String sql = "select distinct value_numeric as provider_id from obs where encounter_id in ( " + "select encounter_id from encounter where patient_id=?" + encounterRestrictions + ") " + "and concept_id=? and value_numeric is not null"; ConceptService conceptService = Context.getConceptService(); Concept providerUserIdConcept = conceptService.getConceptByName("PROVIDER_USER_ID"); if (providerUserIdConcept == null) { return null; } SQLQuery qry = this.sessionFactory.getCurrentSession().createSQLQuery(sql); qry.setInteger(0, patientId); qry.setInteger(1, providerUserIdConcept.getConceptId()); if (encounterList != null) { qry.setParameterList("encounterList", encounterList); } qry.addScalar("provider_id"); try { List<Double> tmpList = qry.list(); List<Integer> resultList = new ArrayList(); for (Double item : tmpList) { resultList.add(item.intValue()); } return resultList; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:org.openmrs.module.orderextension.api.db.HibernateOrderExtensionDAO.java
License:Open Source License
/** * @see org.openmrs.module.orderextension.api.db.OrderExtensionDAO#getMaxNumberOfCyclesForRegimen(org.openmrs.module.orderextension.DrugRegimen) *//*from ww w. j av a 2s .co m*/ @Override public Integer getMaxNumberOfCyclesForRegimen(Patient patient, DrugRegimen regimen) { SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery( "select MAX(og.cycle_number) from orderextension_order_group og, orderextension_order er, orders o where og.id = er.group_id and er.order_id = o.order_id and o.voided = 0 and og.voided = 0 and og.order_set_id = :orderSetId and o.patient_id = :patientId and o.start_date >= :startDate"); query.setInteger("patientId", patient.getId()); query.setInteger("orderSetId", regimen.getOrderSet().getId()); query.setDate("startDate", regimen.getFirstDrugOrderStartDate()); return (Integer) query.uniqueResult(); }
From source file:org.openmrs.module.tracdataquality.db.hibernate.HibernateDataQualityDAO.java
License:Open Source License
/** * gets patients without observation for a given concept * // w w w . ja va 2s . c om * @see org.openmrs.module.tracdataquality.db.DataQualityDAO#getPatientsWithoutAnObs(org.openmrs.Concept) */ @SuppressWarnings("unchecked") public List<Patient> getPatientsWithoutAnObs(Concept concept) { ArrayList<Patient> patientList = new ArrayList<Patient>(); if (concept != null) { PatientService patientService = Context.getPatientService(); PersonService personService = Context.getPersonService(); Session session = sessionFactory.getCurrentSession(); //SQLQuery query = session.createSQLQuery("select distinct person_id from obs where person_id not in(select distinct person_id from obs where concept_id = ?) "); SQLQuery query = session.createSQLQuery("select distinct ob.person_id from obs ob" + " INNER JOIN person s on s.person_id=ob.person_id" + " INNER JOIN patient p on s.person_id=p.patient_id" + " INNER JOIN patient_program pg on pg.patient_id=p.patient_id" + " INNER JOIN program prog on prog.program_id=pg.program_id AND (prog.program_id=1 OR prog.program_id=2)" + " where ob.person_id not in" + " (select distinct obb.person_id from obs obb where obb.concept_id = ?) and pg.date_completed is null and p.voided = 0 and pg.voided = 0 ;"); query.setInteger(0, concept.getConceptId()); List<Integer> personIds = query.list(); for (Integer personId : personIds) { try { if (personService.getPerson(personId).isPatient() && !(patientService.getPatient(personId) == null)) { patientList.add(patientService.getPatient(personId)); } } catch (ObjectNotFoundException onfe) { log.info("patient with id " + personId + " not found"); } catch (UnexpectedRollbackException pae) { log.info("roll back exception when trying to load patient " + personId); } catch (PropertyAccessException pae) { log.info("property access exception when trying to load patient " + personId); } } } return patientList; }
From source file:org.openmrs.module.tracdataquality.db.hibernate.HibernateDataQualityDAO.java
License:Open Source License
public List<Patient> getPatientsWithoutAnObsAdmissionMode(Concept concept) { ArrayList<Patient> patientList = new ArrayList<Patient>(); PatientService patientService = Context.getPatientService(); PersonService personService = Context.getPersonService(); Session session = sessionFactory.getCurrentSession(); int transferInConcept = getGlobalProperty("programOver.transferredInConceptId"); //SQLQuery query = session.createSQLQuery("select distinct person_id from obs where person_id not in(select distinct person_id from obs where concept_id = ?) "); SQLQuery query = session.createSQLQuery("select distinct ob.person_id from obs ob" + " INNER JOIN person s on s.person_id=ob.person_id" + " INNER JOIN patient p on s.person_id=p.patient_id" + " INNER JOIN patient_program pg on pg.patient_id=p.patient_id" + " INNER JOIN program prog on prog.program_id=pg.program_id AND prog.program_id=2" + " where ob.person_id not in" + " (select distinct obb.person_id from obs obb where obb.concept_id = ? or obb.concept_id = " + transferInConcept + ") and pg.date_completed is null and p.voided = 0 and pg.voided = 0 ;"); query.setInteger(0, concept.getConceptId()); List<Integer> personIds = query.list(); for (Integer personId : personIds) { try {/*from www . j a va2 s .co m*/ if (personService.getPerson(personId).isPatient() && !(patientService.getPatient(personId) == null)) { patientList.add(patientService.getPatient(personId)); } } catch (ObjectNotFoundException onfe) { log.info("patient with id " + personId + " not found"); } catch (UnexpectedRollbackException pae) { log.info("roll back exception when trying to load patient " + personId); } catch (PropertyAccessException pae) { log.info("property access exception when trying to load patient " + personId); } } return patientList; }
From source file:org.openmrs.module.tracdataquality.db.hibernate.HibernateDataQualityDAO.java
License:Open Source License
/** * gets patients with observation for a given concept * /*from w w w. jav a 2 s . co m*/ * @see org.openmrs.module.tracdataquality.db.DataQualityDAO#getPatientsWithObs(org.openmrs.Concept) */ @SuppressWarnings("unchecked") public List<Patient> getPatientsWithObs(Concept concept) { ArrayList<Patient> patientList = new ArrayList<Patient>(); PatientService patientService = Context.getPatientService(); PersonService personService = Context.getPersonService(); Session session = sessionFactory.getCurrentSession(); //SQLQuery query = session.createSQLQuery("select distinct person_id from obs where concept_id = ? and voided = 0"); //this cause an error, cannot cast a array into integer // SQLQuery query = session // .createSQLQuery("select distinct ob.person_id,pg.date_completed from obs ob" // + " INNER JOIN person s on s.person_id=ob.person_id" // + " INNER JOIN patient p on s.person_id=p.patient_id" // + " INNER JOIN patient_program pg on pg.patient_id=p.patient_id" // + " INNER JOIN program prog on prog.program_id=pg.program_id AND (prog.program_id=1 OR prog.program_id=2) where ob.concept_id = ? and ob.voided = 0 " // + " AND pg.date_completed is null and p.voided = 0 and pg.voided = 0 "); SQLQuery query = session.createSQLQuery("select distinct ob.person_id from obs ob" + " INNER JOIN person s on s.person_id=ob.person_id" + " INNER JOIN patient p on s.person_id=p.patient_id" + " INNER JOIN patient_program pg on pg.patient_id=p.patient_id" + " INNER JOIN program prog on prog.program_id=pg.program_id AND (prog.program_id=1 OR prog.program_id=2) where ob.concept_id = ? and ob.voided = 0 " + " AND pg.date_completed is null and p.voided = 0 and pg.voided = 0 "); query.setInteger(0, concept.getConceptId()); List<Integer> personIds = query.list(); for (Integer personId : personIds) { try { if (personService.getPerson(personId).isPatient()) { patientList.add(patientService.getPatient(personId)); } } catch (ObjectNotFoundException onfe) { log.info("patient with id " + personId + " not found"); } catch (UnexpectedRollbackException pae) { log.info("roll back exception when trying to load patient " + personId); } catch (PropertyAccessException pae) { log.info("property access exception when trying to load patient " + personId); } } return patientList; }
From source file:org.openmrs.module.tracdataquality.db.hibernate.HibernateDataQualityDAO.java
License:Open Source License
/** * gets patients without identifiers//from w w w . jav a2s . c o m * * @see org.openmrs.module.tracdataquality.db.DataQualityDAO#getPatientsWithoutIdentifiers() */ @SuppressWarnings("unchecked") public List<Integer> getPatientsWithoutIdentifiers(int typeId) { Session session = sessionFactory.getCurrentSession(); /*SQLQuery query = session .createSQLQuery("select patient_id from patient where patient_id not in (select patient_id from patient_identifier where identifier_type=?)"); */ SQLQuery query = session.createSQLQuery("select p.patient_id from patient p" + " INNER JOIN patient_program pg on pg.patient_id=p.patient_id" + " INNER JOIN program prog on prog.program_id=pg.program_id" + " where p.patient_id not in" + " (select pi.patient_id from patient_identifier pi where pi.identifier_type=?)" + " AND (prog.program_id=1 OR prog.program_id=2) AND pg.date_completed is null and p.voided = 0 and pg.voided = 0 "); query.setInteger(0, typeId); List<Integer> patientIds = query.list(); return patientIds; }
From source file:org.openmrs.module.tracdataquality.db.hibernate.HibernateDataQualityDAO.java
License:Open Source License
/** * gets patients without attribute//from ww w . j a v a 2 s.c o m * * @see org.openmrs.module.tracdataquality.db.DataQualityDAO#getPatientsWithoutAttribute(int) */ @SuppressWarnings("unchecked") public List<Integer> getPatientsWithoutAttribute(int typeId) { Session session = sessionFactory.getCurrentSession(); /*SQLQuery query = session .createSQLQuery("select person_id from person where person_id not in (select person_id from person_attribute where person_attribute_type_id= ?)");*/ SQLQuery query = session.createSQLQuery("select person_id from person s" + " INNER JOIN patient p on s.person_id=p.patient_id" + " INNER JOIN patient_program pg on pg.patient_id=p.patient_id" + " INNER JOIN program prog on prog.program_id=pg.program_id" + " where s.person_id not in" + " (select person_id from person_attribute where person_attribute_type_id= ?)" + " AND (prog.program_id=1 OR prog.program_id=2) AND pg.date_completed is null and p.voided = 0 and pg.voided = 0 "); query.setInteger(0, typeId); List<Integer> patientIds = query.list(); return patientIds; }
From source file:org.openxdata.server.dao.hibernate.HibernateEditableDAO.java
License:Apache License
@Override @SuppressWarnings("unchecked") @Secured("Perm_View_Form_Data") public List<FormDataHeader> getFormData(Integer formDefId, Integer userId, Date fromDate, Date toDate) { Session session = getSession();//from www . j av a 2 s. com String sql = "select d.form_data_id,d.form_definition_version_id, " + "fd.name as formName, fdv.name as versionName, u.user_name as creator, " + "d.date_created, u2.user_name as changed_by, d.date_changed,d.description " + "from form_data d inner join users u on u.user_id=d.creator " + "inner join form_definition_version fdv on fdv.form_definition_version_id=d.form_definition_version_id " + "inner join form_definition fd on fd.form_definition_id=fdv.form_definition_id " + "left join users u2 on u2.user_id=d.changed_by "; String filter = ""; if (formDefId != null) { filter += " d.form_definition_version_id = :formDefId"; } if (userId != null) { if (!filter.equals("")) filter += " and"; filter += " d.creator = :userId"; } if (fromDate != null) { if (!filter.equals("")) filter += " and"; filter += " d.date_created >= :fromDate"; } if (toDate != null) { if (!filter.equals("")) filter += " and"; filter += " d.date_created <= :toDate"; } if (!filter.equals("")) { filter = "where " + filter; sql += filter; } sql += " order by d.date_changed desc, d.date_created desc"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(FormDataHeader.class); if (formDefId != null) { query.setInteger("formDefId", formDefId); } if (userId != null) { query.setInteger("userId", userId); } if (fromDate != null) { query.setDate("fromDate", fromDate); } if (toDate != null) { query.setDate("toDate", toDate); } List<FormDataHeader> items = query.list(); return items; }