List of usage examples for org.hibernate SQLQuery setTimestamp
@Deprecated @SuppressWarnings("unchecked") default Query<R> setTimestamp(int position, Date val)
From source file:com.lp.server.system.automatikjob.AutomatikjobPaternoster.java
License:Open Source License
private void speicherePaternostermenge(org.hibernate.Session session, Integer paternosterIId, String cNr, BigDecimal menge, Timestamp ts) { String sql = "UPDATE WW_ARTIKELLAGERPLAETZE SET N_LAGERSTANDPATERNOSTER = ?, T_AENDERN = ? " + "WHERE I_ID = (SELECT I_ID FROM WW_ARTIKELLAGERPLAETZE " + "WHERE LAGERPLATZ_I_ID = (SELECT I_ID FROM WW_LAGERPLATZ " + "WHERE PATERNOSTER_I_ID = ? AND ARTIKEL_I_ID = " + "(SELECT I_ID FROM WW_ARTIKEL WHERE C_NR = ?)))"; org.hibernate.SQLQuery sq = session.createSQLQuery(sql); sq.setBigDecimal(0, menge);//from w w w . j av a 2 s . c o m sq.setTimestamp(1, ts); sq.setInteger(2, paternosterIId); sq.setString(3, cNr); int anzahl = sq.executeUpdate(); if (anzahl == 0) { if (menge.doubleValue() > 0) { myLogger.info("Artikel " + cNr + " ist im Paternoster ohne Paternosterlagerplatz mit Menge " + menge.doubleValue()); } } }
From source file:com.syntelinc.BOK.ATM.withdrawpkg.CheckDailyLimit.java
public double getCurrentTotal(int accountid) { Configuration cfg = new Configuration().configure(); SessionFactory sf = cfg.buildSessionFactory(); Session s = sf.openSession();// w w w . j a va2 s. c om org.hibernate.Transaction t = s.beginTransaction(); SQLQuery q; q = s.createSQLQuery("select creditamt from checkingtrans where acctid=? AND TIME < ? AND TIME > ?"); q.setInteger(0, accountid); q.setTimestamp(1, getDayEnd()); q.setTimestamp(2, getDayStart()); List<BigDecimal> li = q.list(); double totalCredit = 0; if (li.isEmpty()) totalCredit = 0; else if (li.get(0) == null) totalCredit = 0; else { for (BigDecimal cred : li) { totalCredit += cred.doubleValue(); } } System.out.println(totalCredit); if (!li.isEmpty()) return totalCredit; else return 0; }
From source file:org.openmrs.module.isanteplusreports.dataset.definition.evaluator.ArvByPeriodDataSetEvaluator.java
License:Open Source License
/** * @return the sessionFactory/*from w w w . j a v a 2 s .c om*/ */ /*public SessionFactory getSessionFactory() { return sessionFactory; }*/ @Override public DataSet evaluate(DataSetDefinition dataSetDefinition, EvaluationContext context) throws EvaluationException { Long startTime = new Date().getTime(); ArvByPeriodDataSetDefinition dsd = (ArvByPeriodDataSetDefinition) dataSetDefinition; Date startDate = ObjectUtil.nvl(dsd.getStartDate(), DateUtils.addDays(new Date(), -7)); Date endDate = ObjectUtil.nvl(dsd.getEndDate(), new Date()); startDate = DateUtil.getStartOfDay(startDate); endDate = DateUtil.getEndOfDay(endDate); //PatientIdentifierType primaryIdentifierType = emrApiProperties.getPrimaryIdentifierType(); StringBuilder sqlQuery = new StringBuilder( "SELECT C.d as '0-35 jours', C.e as '36-89 jours', C.f as '90-120 jours', C.g as '121-180 jours', C.h as '>180 jours', C.d + C.e + C.f + C.g + C.h as 'Patient unique' FROM (" + "select " + "count(distinct case when DATEDIFF(pdis.next_dispensation_date,ifnull(DATE(pdis.dispensation_date),DATE(pdis.visit_date))) between 0 AND 35 THEN p.patient_id END) as d," + "count(distinct case when DATEDIFF(pdis.next_dispensation_date,ifnull(DATE(pdis.dispensation_date),DATE(pdis.visit_date))) between 36 AND 89 THEN p.patient_id END) as e," + "count(distinct case when DATEDIFF(pdis.next_dispensation_date,ifnull(DATE(pdis.dispensation_date),DATE(pdis.visit_date))) between 90 AND 120 THEN p.patient_id END) as f," + "count(distinct case when DATEDIFF(pdis.next_dispensation_date,ifnull(DATE(pdis.dispensation_date),DATE(pdis.visit_date))) between 121 AND 180 THEN p.patient_id END) as g," + "count(distinct case when DATEDIFF(pdis.next_dispensation_date,ifnull(DATE(pdis.dispensation_date),DATE(pdis.visit_date))) > 180 THEN p.patient_id END) as h"); sqlQuery.append( " FROM isanteplus.patient p, isanteplus.patient_dispensing pdis, (select pdisp.patient_id, MAX(ifnull(DATE(pdisp.dispensation_date),DATE(pdisp.visit_date))) as visit_date FROM isanteplus.patient_dispensing pdisp WHERE pdisp.arv_drug=1065 AND pdisp.voided <> 1 AND (pdisp.rx_or_prophy = 138405 OR pdisp.rx_or_prophy is null) AND ifnull(DATE(pdisp.dispensation_date),DATE(pdisp.visit_date)) BETWEEN :startDate AND :endDate GROUP BY 1) B "); sqlQuery.append(" WHERE p.patient_id=pdis.patient_id"); sqlQuery.append(" AND pdis.patient_id = B.patient_id"); sqlQuery.append(" AND ifnull(DATE(pdis.dispensation_date),DATE(pdis.visit_date)) = B.visit_date"); sqlQuery.append(" AND B.visit_date < pdis.next_dispensation_date"); sqlQuery.append(" AND pdis.arv_drug = 1065"); sqlQuery.append(" AND pdis.voided <> 1"); sqlQuery.append(" AND (pdis.rx_or_prophy = 138405 OR pdis.rx_or_prophy is null)"); if (startDate != null) { sqlQuery.append(" AND ifnull(DATE(pdis.dispensation_date),DATE(pdis.visit_date)) >= :startDate"); } if (endDate != null) { sqlQuery.append(" AND ifnull(DATE(pdis.dispensation_date),DATE(pdis.visit_date)) <= :endDate) C"); } SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery.toString()); //query.setInteger("primaryIdentifierType", primaryIdentifierType.getId()); if (startDate != null) { query.setTimestamp("startDate", startDate); } if (startDate != null) { query.setTimestamp("endDate", endDate); } List<Object[]> list = query.list(); SimpleDataSet dataSet = new SimpleDataSet(dataSetDefinition, context); for (Object[] o : list) { DataSetRow row = new DataSetRow(); row.addColumnValue(new DataSetColumn("0-35 jours", "0-35 jours", String.class), o[0]); row.addColumnValue(new DataSetColumn("36-89 jours", "36-89 jours", String.class), o[1]); row.addColumnValue(new DataSetColumn("90-120 jours", "90-120 jours", String.class), o[2]); row.addColumnValue(new DataSetColumn("121-180 jours", "121-180 jours", String.class), o[3]); row.addColumnValue(new DataSetColumn(">180", ">180", String.class), o[4]); row.addColumnValue(new DataSetColumn("patient_unique", "patient_unique", String.class), o[5]); dataSet.addRow(row); } return dataSet; }
From source file:org.openmrs.module.mirebalaisreports.cohort.definition.evaluator.AdmissionSoonAfterExitCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition cohortDefinition, EvaluationContext context) throws EvaluationException { AdmissionSoonAfterExitCohortDefinition cd = (AdmissionSoonAfterExitCohortDefinition) cohortDefinition; String sql = "select distinct admit.patient_id \n" + "from encounter admit \n" + "inner join encounter discharge \n" + " on discharge.patient_id = admit.patient_id \n" + " and discharge.voided = false \n" + " and discharge.encounter_type = :dischargeEncounterType \n" +/*from w w w .j a v a 2 s .com*/ // was mysql-specific " and discharge.encounter_datetime >= DATE_SUB(admit.encounter_datetime, INTERVAL :windowInDays DAY) \n" + " and discharge.encounter_datetime >= TIMESTAMPADD(DAY, -:windowInDays, admit.encounter_datetime) \n" + " and discharge.encounter_datetime < admit.encounter_datetime \n" + "where admit.voided = false \n" + " and admit.encounter_type = :admitEncounterType \n" + " and admit.encounter_datetime between :onOrAfter and :onOrBefore "; if (cd.getAdmissionLocation() != null) { sql += " and admit.location_id = :admissionLocation "; } SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql); query.setInteger("admitEncounterType", emrApiProperties.getAdmissionEncounterType().getId()); query.setInteger("dischargeEncounterType", emrApiProperties.getExitFromInpatientEncounterType().getId()); query.setTimestamp("onOrAfter", cd.getOnOrAfter()); query.setTimestamp("onOrBefore", cd.getOnOrBefore()); query.setInteger("windowInDays", cd.getWindowInDays()); if (cd.getAdmissionLocation() != null) { query.setInteger("admissionLocation", cd.getAdmissionLocation().getId()); } 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.cohort.definition.evaluator.DiedSoonAfterEncounterCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition def, EvaluationContext context) throws EvaluationException { DiedSoonAfterEncounterCohortDefinition cohortDefinition = (DiedSoonAfterEncounterCohortDefinition) def; String sql = " select distinct p.patient_id \n" + " from patient p \n" + " inner join person per \n" + " on p.patient_id = per.person_id \n" + " and per.voided = false \n" + " inner join encounter admit \n" + " on admit.patient_id = p.patient_id \n" + " and admit.encounter_type = :encounterType \n" + // was mysql-specific " and admit.encounter_datetime > DATE_SUB(per.death_date, INTERVAL :windowInHours HOUR) \n" + " and admit.encounter_datetime > TIMESTAMPADD(HOUR, -:windowInHours, per.death_date) \n" + " where per.death_date between :diedOnOrAfter and :diedOnOrBefore \n" + " and p.voided = false "; SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql); query.setTimestamp("diedOnOrAfter", cohortDefinition.getDiedOnOrAfter()); query.setTimestamp("diedOnOrBefore", cohortDefinition.getDiedOnOrBefore()); query.setInteger("encounterType", cohortDefinition.getEncounterType().getId()); query.setInteger("windowInHours", cohortDefinition.getWindowInHours()); Cohort c = new Cohort(); for (Integer i : (List<Integer>) query.list()) { c.addMember(i);/*from ww w . j a v a 2 s .c om*/ } return new EvaluatedCohort(c, cohortDefinition, context); }
From source file:org.openmrs.module.mirebalaisreports.cohort.definition.evaluator.InpatientLocationCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition cohortDefinition, EvaluationContext context) throws EvaluationException { InpatientLocationCohortDefinition cd = (InpatientLocationCohortDefinition) cohortDefinition; Date onDate = cd.getEffectiveDate(); if (onDate == null) { onDate = new Date(); }/*from w ww .j av a 2 s . co m*/ Location ward = cd.getWard(); Location visitLocation = null; if (ward != null) { visitLocation = adtService.getLocationThatSupportsVisits(ward); } EncounterType admissionEncounterType = emrApiProperties.getAdmissionEncounterType(); EncounterType dischargeEncounterType = emrApiProperties.getExitFromInpatientEncounterType(); EncounterType transferEncounterType = emrApiProperties.getTransferWithinHospitalEncounterType(); StringBuilder sb = new StringBuilder("select distinct v.patient_id " + "from visit v " + "inner join encounter admission " + " on v.visit_id = admission.visit_id " + " and admission.voided = false " + " and admission.encounter_type = :admissionEncounterType " + " and admission.encounter_datetime <= :onDate " + "inner join encounter mostRecentAdt " + " on v.visit_id = mostRecentAdt.visit_id " + " and mostRecentAdt.encounter_id = ( " + " select encounter_id " + " from encounter " + " where visit_id = v.visit_id " + " and voided = false " + " and encounter_type in (:adtEncounterTypes) " + " and encounter_datetime <= :onDate " + " order by encounter_datetime desc, date_created desc limit 1" + " ) "); sb.append("where v.voided = false"); if (visitLocation != null) { sb.append(" and v.location_id = :visitLocation "); } sb.append(" and v.date_started <= :onDate "); sb.append(" and (v.date_stopped is null or v.date_stopped > :onDate) "); if (ward != null) { sb.append(" and mostRecentAdt.location_id = :ward "); } sb.append(" and mostRecentAdt.encounter_type in (:admitOrTransferEncounterTypes)"); SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString()); query.setInteger("admissionEncounterType", admissionEncounterType.getId()); query.setTimestamp("onDate", onDate); if (visitLocation != null) { query.setInteger("visitLocation", visitLocation.getId()); } if (ward != null) { query.setInteger("ward", ward.getId()); } query.setParameterList("adtEncounterTypes", new Integer[] { admissionEncounterType.getId(), dischargeEncounterType.getId(), transferEncounterType.getId() }); query.setParameterList("admitOrTransferEncounterTypes", new Integer[] { admissionEncounterType.getId(), transferEncounterType.getId() }); // This does not actually work: org.hibernate.hql.ast.QuerySyntaxException: with-clause referenced two different from-clause elements // Query hql = sessionFactory.getCurrentSession().createQuery("select distinct(v.patient.id) " + // "from Visit v " + // "join v.encounters as mostRecentAdt " + // " with mostRecentAdt.voided = false " + // " and mostRecentAdt.encounterType in (:adtEncounterTypes) " + // " and mostRecentAdt.encounterDatetime = ( " + // " select max(encounterDatetime)" + // " from Encounter " + // " where visit = v " + // " and voided = false " + // " and encounterType in (:adtEncounterTypes) " + // " and encounterDatetime <= :onDate " + // " ) " + // "where v.voided = false " + // "and v.location = :visitLocation " + // "and v.startDatetime <= :onDate " + // "and (v.stopDatetime is null or v.stopDatetime > :onDate) " + // "and exists ( " + // " from Encounter admission " + // " where admission.visit = v " + // " and admission.voided = false " + // " and admission.encounterType = :admissionEncounterType " + // " and admission.encounterDatetime <= :onDate " + // ") " + // "and mostRecentAdt.location = :ward " + // "and mostRecentAdt.encounterType in (:admitOrTransferEncounterTypes) "); // // hql.setParameter("onDate", onDate); // hql.setParameter("visitLocation", visitLocation); // hql.setParameter("ward", ward); // hql.setParameter("admissionEncounterType", admissionEncounterType); // hql.setParameterList("adtEncounterTypes", adtEncounterTypes); // hql.setParameterList("admitOrTransferEncounterTypes", admitOrTransferEncounterTypes); 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.cohort.definition.evaluator.InpatientTransferCohortDefinitionEvaluator.java
License:Open Source License
@Override public EvaluatedCohort evaluate(CohortDefinition cohortDefinition, EvaluationContext context) throws EvaluationException { InpatientTransferCohortDefinition cd = (InpatientTransferCohortDefinition) cohortDefinition; Location outOfWard = cd.getOutOfWard(); Location inToWard = cd.getInToWard(); if (inToWard == null && outOfWard == null) { throw new IllegalArgumentException("Must specify outOfWard and/or inToWard"); }//from w ww. j a v a2 s.c o m Location visitLocation = adtService.getLocationThatSupportsVisits(outOfWard != null ? outOfWard : inToWard); if (visitLocation == null) { throw new IllegalArgumentException(outOfWard + " and its ancestor locations don't support visits"); } EncounterType admissionEncounterType = emrApiProperties.getAdmissionEncounterType(); EncounterType dischargeEncounterType = emrApiProperties.getExitFromInpatientEncounterType(); EncounterType transferEncounterType = emrApiProperties.getTransferWithinHospitalEncounterType(); String sql = "select distinct v.patient_id " + "from visit v " + "inner join encounter admission " + " on v.visit_id = admission.visit_id " + " and admission.voided = false " + " and admission.encounter_type = :admissionEncounterType " + " and admission.encounter_datetime <= :onOrBefore " + "inner join encounter transfer " + " on v.visit_id = transfer.visit_id " + " and transfer.voided = false " + " and transfer.encounter_type = :transferEncounterType " + " and transfer.encounter_datetime between :onOrAfter and :onOrBefore " + " and transfer.encounter_datetime > admission.encounter_datetime "; if (inToWard != null) { sql += " and transfer.location_id = :inToWard "; } sql += "inner join encounter adtBeforeTransfer " + " on v.visit_id = adtBeforeTransfer.visit_id " + " and adtBeforeTransfer.voided = false " + " and adtBeforeTransfer.encounter_type in (:adtEncounterTypes) " + " and adtBeforeTransfer.encounter_id = ( " + " select encounter_id " + " from encounter " + " where visit_id = v.visit_id " + " and voided = false " + " and encounter_type in (:adtEncounterTypes) " + " and encounter_datetime < transfer.encounter_datetime " + " order by encounter_datetime desc, date_created desc limit 1" + " ) " + "where v.voided = false" + " and v.location_id = :visitLocation " + " and adtBeforeTransfer.encounter_type in (:admitOrTransferEncounterTypes)"; if (outOfWard != null) { sql += " and adtBeforeTransfer.location_id = :outOfWard "; } SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql); query.setInteger("admissionEncounterType", admissionEncounterType.getId()); query.setInteger("transferEncounterType", transferEncounterType.getId()); query.setTimestamp("onOrBefore", cd.getOnOrBefore()); query.setTimestamp("onOrAfter", cd.getOnOrAfter()); query.setInteger("visitLocation", visitLocation.getId()); if (outOfWard != null) { query.setInteger("outOfWard", outOfWard.getId()); } if (inToWard != null) { query.setInteger("inToWard", inToWard.getId()); } query.setParameterList("adtEncounterTypes", new Integer[] { admissionEncounterType.getId(), dischargeEncounterType.getId(), transferEncounterType.getId() }); query.setParameterList("admitOrTransferEncounterTypes", new Integer[] { admissionEncounterType.getId(), transferEncounterType.getId() }); 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.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 2 s. c o m 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 ww .j a v a2s.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; }