Examples with QueryItem org.hisp.dhis.common.QueryItem used on opensource projects

Example 21 with QueryItem

use of org.hisp.dhis.common.QueryItem in project dhis2-core by dhis2.

the class EventSearchParams method getDataElementsAndFilters.

/**
     * Returns a list of dataElements and filters combined.
     */
public List<QueryItem> getDataElementsAndFilters() {
    List<QueryItem> items = new ArrayList<>();
    items.addAll(filters);
    for (QueryItem de : dataElements) {
        if (items != null && !items.contains(de)) {
            items.add(de);
        }
    }
    return items;
}

Example 22 with QueryItem

use of org.hisp.dhis.common.QueryItem in project dhis2-core by dhis2.

the class JdbcEventStore method getFromWhereClause.

/**
     * From, join and where clause. For dataElement params, restriction is set
     * in inner join. For query params, restriction is set in where clause.
     */
private String getFromWhereClause(EventSearchParams params, SqlHelper hlp, List<OrganisationUnit> organisationUnits) {
    String sql = "from programstageinstance psi " + "inner join programinstance pi on pi.programinstanceid = psi.programinstanceid " + "inner join program p on p.programid = pi.programid " + "inner join programstage ps on ps.programstageid = psi.programstageid " + "inner join categoryoptioncombo coc on coc.categoryoptioncomboid = psi.attributeoptioncomboid " + "inner join organisationunit ou on psi.organisationunitid = ou.organisationunitid ";
    for (QueryItem item : params.getDataElementsAndFilters()) {
        final String col = statementBuilder.columnQuote(item.getItemId());
        final String joinClause = item.hasFilter() ? "inner join" : "left join";
        sql += joinClause + " " + "trackedentitydatavalue as " + col + " " + "on " + col + ".programstageinstanceid = psi.programstageinstanceid " + "and " + col + ".dataelementid = " + item.getItem().getId() + " ";
        if (item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                final String encodedFilter = statementBuilder.encode(filter.getFilter(), false);
                final String queryCol = item.isNumeric() ? " CAST( " + (col + ".value AS NUMERIC)") : "lower(" + col + ".value)";
                sql += "and " + queryCol + " " + filter.getSqlOperator() + " " + StringUtils.lowerCase(filter.getSqlFilter(encodedFilter)) + " ";
            }
        }
    }
    if (organisationUnits != null && !organisationUnits.isEmpty()) {
        sql += hlp.whereAnd() + " psi.organisationunitid in (" + getCommaDelimitedString(getIdentifiers(organisationUnits)) + ") ";
    }
    if (params.getProgramStage() != null) {
        sql += hlp.whereAnd() + " ps.programstageid = " + params.getProgramStage().getId() + " ";
    }
    if (params.getCategoryOptionCombo() != null) {
        sql += hlp.whereAnd() + " psi.attributeoptioncomboid = " + params.getCategoryOptionCombo().getId() + " ";
    }
    if (params.getStartDate() != null) {
        sql += hlp.whereAnd() + " (psi.executiondate >= '" + getMediumDateString(params.getStartDate()) + "' " + "or (psi.executiondate is null and psi.duedate >= '" + getMediumDateString(params.getStartDate()) + "')) ";
    }
    if (params.getEndDate() != null) {
        sql += hlp.whereAnd() + " (psi.executiondate <= '" + getMediumDateString(params.getEndDate()) + "' " + "or (psi.executiondate is null and psi.duedate <= '" + getMediumDateString(params.getEndDate()) + "')) ";
    }
    if (params.getLastUpdatedStartDate() != null) {
        sql += hlp.whereAnd() + " psi.lastupdated >= '" + DateUtils.getLongDateString(params.getLastUpdatedStartDate()) + "' ";
    }
    if (params.getLastUpdatedEndDate() != null) {
        sql += hlp.whereAnd() + " psi.lastupdated <= '" + DateUtils.getLongDateString(params.getLastUpdatedEndDate()) + "' ";
    }
    if (params.getDueDateStart() != null) {
        sql += hlp.whereAnd() + " psi.duedate is not null and psi.duedate >= '" + DateUtils.getLongDateString(params.getDueDateStart()) + "' ";
    }
    if (params.getDueDateEnd() != null) {
        sql += hlp.whereAnd() + " psi.duedate is not null and psi.duedate <= '" + DateUtils.getLongDateString(params.getDueDateEnd()) + "' ";
    }
    if (!params.isIncludeDeleted()) {
        sql += hlp.whereAnd() + " psi.deleted is false ";
    }
    if (params.getEventStatus() != null) {
        if (params.getEventStatus() == EventStatus.VISITED) {
            sql += hlp.whereAnd() + " psi.status = '" + EventStatus.ACTIVE.name() + "' and psi.executiondate is not null ";
        } else if (params.getEventStatus() == EventStatus.OVERDUE) {
            sql += hlp.whereAnd() + " date(now()) > date(psi.duedate) and psi.status = '" + EventStatus.SCHEDULE.name() + "' ";
        } else {
            sql += hlp.whereAnd() + " psi.status = '" + params.getEventStatus().name() + "' ";
        }
    }
    if (params.getEvents() != null && !params.getEvents().isEmpty() && !params.hasFilters()) {
        sql += hlp.whereAnd() + " (psi.uid in (" + getQuotedCommaDelimitedString(params.getEvents()) + ")) ";
    }
    return sql;
}

Example 23 with QueryItem

use of org.hisp.dhis.common.QueryItem in project dhis2-core by dhis2.

the class AbstractEventService method getEventsGrid.

@Override
public Grid getEventsGrid(EventSearchParams params) {
    List<OrganisationUnit> organisationUnits = getOrganisationUnits(params);
    // ---------------------------------------------------------------------
    if (params.getDataElements().isEmpty() && params.getProgramStage() != null && params.getProgramStage().getProgramStageDataElements() != null) {
        for (ProgramStageDataElement pde : params.getProgramStage().getProgramStageDataElements()) {
            if (pde.getDisplayInReports()) {
                QueryItem qi = new QueryItem(pde.getDataElement(), pde.getDataElement().getLegendSet(), pde.getDataElement().getValueType(), pde.getDataElement().getAggregationType(), pde.getDataElement().hasOptionSet() ? pde.getDataElement().getOptionSet() : null);
                params.getDataElements().add(qi);
            }
        }
    }
    // ---------------------------------------------------------------------
    // Grid headers
    // ---------------------------------------------------------------------
    Grid grid = new ListGrid();
    for (String col : STATIC_EVENT_COLUMNS) {
        grid.addHeader(new GridHeader(col, col));
    }
    for (QueryItem item : params.getDataElements()) {
        grid.addHeader(new GridHeader(item.getItem().getUid(), item.getItem().getName()));
    }
    List<Map<String, String>> events = eventStore.getEventsGrid(params, organisationUnits);
    for (Map<String, String> event : events) {
        grid.addRow();
        for (String col : STATIC_EVENT_COLUMNS) {
            grid.addValue(event.get(col));
        }
        for (QueryItem item : params.getDataElements()) {
            grid.addValue(event.get(item.getItemId()));
        }
    }
    Map<String, Object> metaData = new HashMap<>();
    if (params.isPaging()) {
        int count = 0;
        if (params.isTotalPages()) {
            count = eventStore.getEventCount(params, organisationUnits);
        }
        Pager pager = new Pager(params.getPageWithDefault(), count, params.getPageSizeWithDefault());
        metaData.put(PAGER_META_KEY, pager);
    }
    grid.setMetaData(metaData);
    return grid;
}

Example 24 with QueryItem

use of org.hisp.dhis.common.QueryItem in project dhis2-core by dhis2.

the class TrackedEntityInstanceStoreTest method testQuery.

@Test
public void testQuery() {
    teiStore.save(teiA);
    teiStore.save(teiB);
    teiStore.save(teiC);
    teiStore.save(teiD);
    teiStore.save(teiE);
    teiStore.save(teiF);
    attributeValueService.addTrackedEntityAttributeValue(new TrackedEntityAttributeValue(atA, teiD, "Male"));
    attributeValueService.addTrackedEntityAttributeValue(new TrackedEntityAttributeValue(atA, teiE, "Male"));
    attributeValueService.addTrackedEntityAttributeValue(new TrackedEntityAttributeValue(atA, teiF, "Female"));
    programInstanceService.enrollTrackedEntityInstance(teiB, prA, new Date(), new Date(), ouB);
    programInstanceService.enrollTrackedEntityInstance(teiE, prA, new Date(), new Date(), ouB);
    // Get all
    TrackedEntityInstanceQueryParams params = new TrackedEntityInstanceQueryParams();
    List<TrackedEntityInstance> teis = teiStore.getTrackedEntityInstances(params);
    assertEquals(6, teis.size());
    // Filter by attribute
    params = new TrackedEntityInstanceQueryParams().addFilter(new QueryItem(atA, QueryOperator.EQ, "Male", ValueType.TEXT, AggregationType.NONE, null));
    teis = teiStore.getTrackedEntityInstances(params);
    assertEquals(2, teis.size());
    assertTrue(teis.contains(teiD));
    assertTrue(teis.contains(teiE));
    // Filter by attribute
    params = new TrackedEntityInstanceQueryParams().addFilter(new QueryItem(atA, QueryOperator.EQ, "Female", ValueType.TEXT, AggregationType.NONE, null));
    teis = teiStore.getTrackedEntityInstances(params);
    assertEquals(1, teis.size());
    assertTrue(teis.contains(teiF));
    // Filter by selected org units
    params = new TrackedEntityInstanceQueryParams().addOrganisationUnit(ouB).setOrganisationUnitMode(OrganisationUnitSelectionMode.SELECTED);
    teis = teiStore.getTrackedEntityInstances(params);
    assertEquals(2, teis.size());
    assertTrue(teis.contains(teiB));
    assertTrue(teis.contains(teiC));
    // Filter by descendants org units
    params = new TrackedEntityInstanceQueryParams().addOrganisationUnit(ouB).setOrganisationUnitMode(OrganisationUnitSelectionMode.DESCENDANTS);
    teis = teiStore.getTrackedEntityInstances(params);
    assertEquals(5, teis.size());
    assertTrue(teis.contains(teiB));
    assertTrue(teis.contains(teiC));
    assertTrue(teis.contains(teiD));
    assertTrue(teis.contains(teiE));
    assertTrue(teis.contains(teiF));
    // Filter by program enrollment
    params = new TrackedEntityInstanceQueryParams().setProgram(prA);
    teis = teiStore.getTrackedEntityInstances(params);
    assertEquals(2, teis.size());
    assertTrue(teis.contains(teiB));
    assertTrue(teis.contains(teiE));
}

Example 25 with QueryItem

use of org.hisp.dhis.common.QueryItem in project dhis2-core by dhis2.

the class ActivityReportingServiceImpl method findPatientInAdvanced.

/**
     * keyword is on format of
     * {attribute-id1}:{operator1}:{filter-value1};{attribute
     * -id2}:{operator2}:{filter-value2}
     */
@Override
public String findPatientInAdvanced(String keyword, int orgUnitId, int programId) throws NotAllowedException {
    TrackedEntityInstanceQueryParams param = new TrackedEntityInstanceQueryParams();
    List<TrackedEntityAttribute> displayAttributes = new ArrayList<>(attributeService.getTrackedEntityAttributesDisplayInList());
    for (TrackedEntityAttribute trackedEntityAttribute : displayAttributes) {
        QueryItem queryItem = new QueryItem(trackedEntityAttribute);
        param.addAttribute(queryItem);
    }
    if (programId != 0) {
        param.setProgram(programService.getProgram(programId));
    }
    if (orgUnitId != 0) {
        param.addOrganisationUnit(organisationUnitService.getOrganisationUnit(orgUnitId));
        param.setOrganisationUnitMode(OrganisationUnitSelectionMode.SELECTED);
    } else {
        param.setOrganisationUnitMode(OrganisationUnitSelectionMode.ALL);
    }
    String[] items = keyword.split(";");
    if (items == null) {
        items = new String[1];
        items[0] = keyword;
    }
    for (int i = 0; i < items.length; i++) {
        String[] split = keyword.split(":");
        if (split == null || (split.length != 3 && split.length != 2)) {
            throw NotAllowedException.INVALID_FILTER;
        }
        if (split.length == 2) {
            QueryOperator operator = QueryOperator.fromString(split[0]);
            param.setQuery(new QueryFilter(operator, split[1]));
        } else {
            TrackedEntityAttribute at = attributeService.getTrackedEntityAttributeByName(split[0]);
            QueryItem queryItem = new QueryItem(at, at.getLegendSets().get(0), at.getValueType(), at.getAggregationType(), at.getOptionSet());
            QueryOperator operator = QueryOperator.fromString(split[1]);
            queryItem.getFilters().add(new QueryFilter(operator, split[2]));
            param.getFilters().add(queryItem);
        }
    }
    Grid trackedEntityInstanceGrid = entityInstanceService.getTrackedEntityInstancesGrid(param);
    List<List<Object>> listOfTrackedEntityInstance = trackedEntityInstanceGrid.getRows();
    if (listOfTrackedEntityInstance.size() == 0) {
        throw NotAllowedException.NO_BENEFICIARY_FOUND;
    }
    /**
         * Grid columns: 0 = instance 1 = created 2 = lastupdated 3 = ou 4 = te
         * 5 onwards = attributes
         */
    int instanceIndex = 0;
    int teIndex = 4;
    List<Integer> attributesIndex = new ArrayList<>();
    List<GridHeader> headers = trackedEntityInstanceGrid.getHeaders();
    int index = 0;
    for (GridHeader header : headers) {
        if (header.getName().equals("instance")) {
            instanceIndex = index;
        } else if (header.getName().equals("te")) {
            teIndex = index;
        } else if (!header.getName().equals("created") && !header.getName().equals("lastupdated") && !header.getName().equals("ou")) {
            attributesIndex.add(new Integer(index));
        }
        index++;
    }
    String instanceInfo = "";
    String trackedEntityName = "";
    for (List<Object> row : listOfTrackedEntityInstance) {
        TrackedEntity te = trackedEntityService.getTrackedEntity((String) row.get(teIndex));
        if (!trackedEntityName.equals(te.getDisplayName())) {
            trackedEntityName = te.getDisplayName();
            instanceInfo += te.getDisplayName() + "$";
        }
        // NOTE: this line should be here but because the mobile client uses
        // the int TEI id, we will temprarily get the int id for now.
        // instanceInfo += (String) row.get( instanceIndex ) + "/";
        TrackedEntityInstance tei = entityInstanceService.getTrackedEntityInstance((String) row.get(instanceIndex));
        instanceInfo += tei.getId() + "/";
        // end of temproary fix
        String attText = "";
        for (Integer attIndex : attributesIndex) {
            if (row.get(attIndex.intValue()) != null) {
                attText += (String) row.get(attIndex.intValue()) + " ";
            }
        }
        instanceInfo += attText.trim() + "$";
    }
    return instanceInfo;
}