Examples with SqlQueryBuilder org.openmrs.module.reporting.evaluation.querybuilder.SqlQueryBuilder used on opensource projects

Example 1 with SqlQueryBuilder

use of org.openmrs.module.reporting.evaluation.querybuilder.SqlQueryBuilder in project openmrs-module-pihcore by PIH.

the class ActiveVisitsListPageController method get.

public void get(PageModel model, @SpringBean CoreAppsProperties coreAppsProperties, @RequestParam(value = "location", required = false) Integer location, @RequestParam(value = "timeQualifier", required = false) String timeQualifier, UiSessionContext uiSessionContext) throws EvaluationException {
    EvaluationContext context = new EvaluationContext();
    SqlQueryBuilder q = new SqlQueryBuilder();
    q.append("(select e.patient_id, e.encounter_datetime as lastSeen");
    q.append(" from encounter e");
    q.append(" inner join (");
    q.append(" select patient_id, max(encounter_datetime) lastSeen ");
    q.append(" from encounter");
    q.append(" where voided = 0 and visit_id in ( select visit_id from visit where date_stopped is null and voided = 0)");
    q.append(" group by patient_id");
    q.append(" ) b on e.patient_id = b.patient_id and  e.encounter_datetime = b.lastSeen");
    q.append(" where e.voided = 0 and e.visit_id in");
    q.append(" ( select visit_id from visit where date_stopped is null and voided = 0)");
    if (location != null) {
        q.append(" and e.location_id = :location").addParameter("location", location);
    }
    q.append(" )");
    q.append(" union");
    q.append(" (select v.patient_id, v.date_started as lastSeen");
    q.append(" from visit v");
    q.append(" where v.date_stopped is null and v.voided = 0 ");
    q.append(" and v.visit_id not in");
    q.append(" (select e.visit_id from encounter e where e.patient_id = v.patient_id and e.visit_id is not null and e.voided = 0))");
    q.append(" order by lastSeen desc");
    DbSessionFactory dbSessionFactory = Context.getRegisteredComponents(DbSessionFactory.class).get(0);
    List<Object[]> resultSet = q.evaluateToList(dbSessionFactory, context);
    List<Integer> activeVisitsCohort = new ArrayList<Integer>();
    if (resultSet != null && resultSet.size() > 0) {
        for (Object[] activeVisit : resultSet) {
            activeVisitsCohort.add((Integer) activeVisit[0]);
        }
    }
    model.addAttribute("locale", uiSessionContext.getLocale());
    model.addAttribute("lastLocation", location);
    model.addAttribute("activeVisitsCohort", activeVisitsCohort);
    model.addAttribute("dashboardUrl", coreAppsProperties.getDashboardUrl());
    // used to determine if we display links to patient dashboard)
    model.put("privilegePatientDashboard", CoreAppsConstants.PRIVILEGE_PATIENT_DASHBOARD);
}