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

Example 16 with QueryFilter

use of org.hisp.dhis.common.QueryFilter 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;
}

Example 17 with QueryFilter

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

the class HibernateTrackedEntityInstanceStore method getFromWhereClause.

/**
     * From, join and where clause. For attribute params, restriction is set in
     * inner join. For query params, restriction is set in where clause.
     */
private String getFromWhereClause(TrackedEntityInstanceQueryParams params, SqlHelper hlp) {
    final String regexp = statementBuilder.getRegexpMatch();
    final String wordStart = statementBuilder.getRegexpWordStart();
    final String wordEnd = statementBuilder.getRegexpWordEnd();
    final String anyChar = "\\.*?";
    String sql = "from trackedentityinstance tei " + "inner join trackedentity te on tei.trackedentityid = te.trackedentityid " + "inner join organisationunit ou on tei.organisationunitid = ou.organisationunitid ";
    for (QueryItem item : params.getAttributesAndFilters()) {
        final String col = statementBuilder.columnQuote(item.getItemId());
        final String joinClause = item.hasFilter() ? "inner join" : "left join";
        sql += joinClause + " " + "trackedentityattributevalue as " + col + " " + "on " + col + ".trackedentityinstanceid = tei.trackedentityinstanceid " + "and " + col + ".trackedentityattributeid = " + item.getItem().getId() + " ";
        if (!params.isOrQuery() && item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                final String encodedFilter = statementBuilder.encode(filter.getFilter(), false);
                final String queryCol = item.isNumeric() ? (col + ".value") : "lower(" + col + ".value)";
                sql += "and " + queryCol + " " + filter.getSqlOperator() + " " + StringUtils.lowerCase(filter.getSqlFilter(encodedFilter)) + " ";
            }
        }
    }
    if (params.hasTrackedEntity()) {
        sql += hlp.whereAnd() + " tei.trackedentityid = " + params.getTrackedEntity().getId() + " ";
    }
    params.handleOrganisationUnits();
    if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.ALL)) {
    // No restriction
    } else if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.DESCENDANTS)) {
        String ouClause = " (";
        SqlHelper orHlp = new SqlHelper(true);
        for (OrganisationUnit organisationUnit : params.getOrganisationUnits()) {
            ouClause += orHlp.or() + "ou.path like '" + organisationUnit.getPath() + "%'";
        }
        ouClause += ")";
        sql += hlp.whereAnd() + ouClause;
    } else // SELECTED (default)        
    {
        sql += hlp.whereAnd() + " tei.organisationunitid in (" + getCommaDelimitedString(getIdentifiers(params.getOrganisationUnits())) + ") ";
    }
    if (params.hasProgram()) {
        sql += hlp.whereAnd() + " exists (" + "select pi.trackedentityinstanceid " + "from programinstance pi ";
        if (params.hasEventStatus()) {
            sql += "left join programstageinstance psi " + "on pi.programinstanceid = psi.programinstanceid and psi.deleted is false ";
        }
        sql += "where pi.trackedentityinstanceid = tei.trackedentityinstanceid " + "and pi.programid = " + params.getProgram().getId() + " ";
        if (params.hasProgramStatus()) {
            sql += "and pi.status = '" + params.getProgramStatus() + "' ";
        }
        if (params.hasFollowUp()) {
            sql += "and pi.followup = " + params.getFollowUp() + " ";
        }
        if (params.hasProgramEnrollmentStartDate()) {
            sql += "and pi.enrollmentdate >= '" + getMediumDateString(params.getProgramEnrollmentStartDate()) + "' ";
        }
        if (params.hasProgramEnrollmentEndDate()) {
            sql += "and pi.enrollmentdate <= '" + getMediumDateString(params.getProgramEnrollmentEndDate()) + "' ";
        }
        if (params.hasProgramIncidentStartDate()) {
            sql += "and pi.incidentdate >= '" + getMediumDateString(params.getProgramIncidentStartDate()) + "' ";
        }
        if (params.hasProgramIncidentEndDate()) {
            sql += "and pi.incidentdate <= '" + getMediumDateString(params.getProgramIncidentEndDate()) + "' ";
        }
        if (params.hasEventStatus()) {
            sql += getEventStatusWhereClause(params);
        }
        if (!params.isIncludeDeleted()) {
            sql += " and pi.deleted is false ";
        }
        sql += ") ";
    }
    if (params.isOrQuery() && params.hasAttributesOrFilters()) {
        final String start = params.getQuery().isOperator(QueryOperator.LIKE) ? anyChar : wordStart;
        final String end = params.getQuery().isOperator(QueryOperator.LIKE) ? anyChar : wordEnd;
        sql += hlp.whereAnd() + " (";
        List<String> queryTokens = getTokens(params.getQuery().getFilter());
        for (String queryToken : queryTokens) {
            final String query = statementBuilder.encode(queryToken, false);
            sql += "(";
            for (QueryItem item : params.getAttributesAndFilters()) {
                final String col = statementBuilder.columnQuote(item.getItemId());
                sql += col + ".value " + regexp + " '" + start + StringUtils.lowerCase(query) + end + "' or ";
            }
            sql = removeLastOr(sql) + ") and ";
        }
        sql = removeLastAnd(sql) + ") ";
    }
    if (!params.isIncludeDeleted()) {
        sql += hlp.whereAnd() + " tei.deleted is false ";
    }
    return sql;
}

Example 18 with QueryFilter

use of org.hisp.dhis.common.QueryFilter 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) {
    StringBuilder sqlBuilder = new StringBuilder().append("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 " + "left join trackedentityprogramowner po on (pi.trackedentityinstanceid=po.trackedentityinstanceid) " + "inner join organisationunit ou on (coalesce(po.organisationunitid, psi.organisationunitid)=ou.organisationunitid) " + "left join userinfo au on (psi.assigneduserid=au.userinfoid) ");
    Set<String> joinedColumns = new HashSet<>();
    String eventDataValuesWhereSql = "";
    for (QueryItem item : params.getDataElementsAndFilters()) {
        final String col = item.getItemId();
        final String optCol = item.getItemId() + "opt";
        final String dataValueValueSql = "psi.eventdatavalues #>> '{" + col + ", value}'";
        if (!joinedColumns.contains(col)) {
            if (item.hasOptionSet() && item.hasFilter()) {
                sqlBuilder.append("inner join optionvalue as " + optCol + " on lower(" + optCol + ".code) = " + "lower(" + dataValueValueSql + ") and " + optCol + ".optionsetid = " + item.getOptionSet().getId() + " ");
            }
            joinedColumns.add(col);
        }
        if (item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                final String encodedFilter = statementBuilder.encode(filter.getFilter(), false);
                final String queryCol = " " + (item.isNumeric() ? castToNumber(dataValueValueSql) : lower(dataValueValueSql));
                if (!item.hasOptionSet()) {
                    if (!eventDataValuesWhereSql.isEmpty()) {
                        eventDataValuesWhereSql += " and ";
                    }
                    eventDataValuesWhereSql += " " + queryCol + " " + filter.getSqlOperator() + " " + StringUtils.lowerCase(filter.getSqlFilter(encodedFilter)) + " ";
                } else if (QueryOperator.IN.getValue().equalsIgnoreCase(filter.getSqlOperator())) {
                    sqlBuilder.append("and ").append(queryCol).append(" ").append(filter.getSqlOperator()).append(" ").append(StringUtils.lowerCase(filter.getSqlFilter(encodedFilter))).append(" ");
                } else {
                    sqlBuilder.append("and lower( ").append(optCol).append(DOT_NAME).append(" ").append(filter.getSqlOperator()).append(" ").append(StringUtils.lowerCase(filter.getSqlFilter(encodedFilter))).append(" ");
                }
            }
        }
    }
    if (!eventDataValuesWhereSql.isEmpty()) {
        sqlBuilder.append(hlp.whereAnd()).append(eventDataValuesWhereSql).append(" ");
    }
    if (!organisationUnits.isEmpty() || params.getOrgUnit() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(getOrgUnitSql(hlp, params, organisationUnits));
    }
    if (params.getProgramStage() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" ps.programstageid = ").append(params.getProgramStage().getId()).append(" ");
    }
    if (params.getCategoryOptionCombo() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" psi.attributeoptioncomboid = ").append(params.getCategoryOptionCombo().getId()).append(" ");
    }
    if (params.getStartDate() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" (psi.executiondate >= '").append(getMediumDateString(params.getStartDate())).append("' ").append("or (psi.executiondate is null and psi.duedate >= '").append(getMediumDateString(params.getStartDate())).append("')) ");
    }
    if (params.getEndDate() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" (psi.executiondate <= '").append(getMediumDateString(params.getEndDate())).append("' ").append("or (psi.executiondate is null and psi.duedate <= '").append(getMediumDateString(params.getEndDate())).append("')) ");
    }
    sqlBuilder.append(addLastUpdatedFilters(params, hlp, false));
    if (params.isSynchronizationQuery()) {
        sqlBuilder.append(hlp.whereAnd()).append(" psi.lastupdated > psi.lastsynchronized ");
    }
    if (params.getSkipChangedBefore() != null && params.getSkipChangedBefore().getTime() > 0) {
        String skipChangedBefore = DateUtils.getLongDateString(params.getSkipChangedBefore());
        sqlBuilder.append(hlp.whereAnd()).append(PSI_LASTUPDATED_GT).append(skipChangedBefore).append("' ");
    }
    if (params.getDueDateStart() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" psi.duedate is not null and psi.duedate >= '").append(DateUtils.getLongDateString(params.getDueDateStart())).append("' ");
    }
    if (params.getDueDateEnd() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" psi.duedate is not null and psi.duedate <= '").append(DateUtils.getLongDateString(params.getDueDateEnd())).append("' ");
    }
    if (!params.isIncludeDeleted()) {
        sqlBuilder.append(hlp.whereAnd()).append(" psi.deleted is false ");
    }
    if (params.getEventStatus() != null) {
        if (params.getEventStatus() == EventStatus.VISITED) {
            sqlBuilder.append(hlp.whereAnd()).append(PSI_STATUS_EQ).append(EventStatus.ACTIVE.name()).append("' and psi.executiondate is not null ");
        } else if (params.getEventStatus() == EventStatus.OVERDUE) {
            sqlBuilder.append(hlp.whereAnd()).append(" date(now()) > date(psi.duedate) and psi.status = '").append(EventStatus.SCHEDULE.name()).append("' ");
        } else {
            sqlBuilder.append(hlp.whereAnd()).append(PSI_STATUS_EQ).append(params.getEventStatus().name()).append("' ");
        }
    }
    if (params.getEvents() != null && !params.getEvents().isEmpty() && !params.hasFilters()) {
        sqlBuilder.append(hlp.whereAnd()).append(" (psi.uid in (").append(getQuotedCommaDelimitedString(params.getEvents())).append(")) ");
    }
    if (params.hasAssignedUsers()) {
        sqlBuilder.append(hlp.whereAnd() + " (au.uid in (" + getQuotedCommaDelimitedString(params.getAssignedUsers()) + ")) ");
    }
    if (params.isIncludeOnlyUnassignedEvents()) {
        sqlBuilder.append(hlp.whereAnd()).append(" (au.uid is null) ");
    }
    if (params.isIncludeOnlyAssignedEvents()) {
        sqlBuilder.append(hlp.whereAnd()).append(" (au.uid is not null) ");
    }
    return sqlBuilder.toString();
}

Example 19 with QueryFilter

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

the class DefaultEventDataQueryService method getQueryItem.

private QueryItem getQueryItem(String dimensionString, Program program, EventOutputType type) {
    String[] split = dimensionString.split(DIMENSION_NAME_SEP);
    if (split.length % 2 != 1) {
        throwIllegalQueryEx(ErrorCode.E7222, dimensionString);
    }
    QueryItem queryItem = queryItemLocator.getQueryItemFromDimension(split[0], program, type);
    if (// Filters specified
    split.length > 1) {
        for (int i = 1; i < split.length; i += 2) {
            QueryOperator operator = QueryOperator.fromString(split[i]);
            QueryFilter filter = new QueryFilter(operator, split[i + 1]);
            queryItem.addFilter(filter);
        }
    }
    return queryItem;
}

Example 20 with QueryFilter

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

the class EventAnalyticsServiceMetadataTest method testGetQueryItemDimensionMetadata.

// -------------------------------------------------------------------------
// Tests
// -------------------------------------------------------------------------
@Test
@SuppressWarnings("unchecked")
void testGetQueryItemDimensionMetadata() {
    DimensionalObject periods = new BaseDimensionalObject(DimensionalObject.PERIOD_DIM_ID, DimensionType.PERIOD, Lists.newArrayList(peA));
    DimensionalObject orgUnits = new BaseDimensionalObject(DimensionalObject.ORGUNIT_DIM_ID, DimensionType.ORGANISATION_UNIT, Lists.newArrayList(ouA));
    QueryItem itemLegendSet = new QueryItem(deA, lsA, deA.getValueType(), deA.getAggregationType(), null);
    QueryItem itemLegendSetFilter = new QueryItem(deB, lsA, deB.getValueType(), deB.getAggregationType(), null);
    itemLegendSetFilter.addFilter(new QueryFilter(QueryOperator.IN, leA.getUid() + OPTION_SEP + leB.getUid() + OPTION_SEP + leC.getUid()));
    QueryItem item = new QueryItem(deC, null, deC.getValueType(), deC.getAggregationType(), null);
    QueryItem itemFilter = new QueryItem(deD, null, deD.getValueType(), deD.getAggregationType(), null);
    itemFilter.addFilter(new QueryFilter(QueryOperator.GT, "10"));
    QueryItem itemOptionSet = new QueryItem(deE, null, deE.getValueType(), deE.getAggregationType(), osA);
    QueryItem itemOptionSetFilter = new QueryItem(deF, null, deE.getValueType(), deE.getAggregationType(), osA);
    itemOptionSetFilter.addFilter(new QueryFilter(QueryOperator.IN, opA.getCode() + OPTION_SEP + opB.getCode()));
    EventQueryParams params = new EventQueryParams.Builder().withProgram(prA).addDimension(periods).addDimension(orgUnits).addItem(itemLegendSet).addItem(itemLegendSetFilter).addItem(item).addItem(itemFilter).addItem(itemOptionSet).addItem(itemOptionSetFilter).withSkipData(true).withSkipMeta(false).withDisplayProperty(DisplayProperty.NAME).build();
    Grid grid = eventAnalyticsService.getAggregatedEventData(params);
    Map<String, Object> metadata = grid.getMetaData();
    assertNotNull(metadata);
    Map<String, Object> dimensionItems = (Map<String, Object>) metadata.get(AnalyticsMetaDataKey.DIMENSIONS.getKey());
    assertNotNull(dimensionItems);
    List<String> itemsLegendSet = (List<String>) dimensionItems.get(itemLegendSet.getItemId());
    List<String> itemsLegendSetFilter = (List<String>) dimensionItems.get(itemLegendSetFilter.getItemId());
    List<String> items = (List<String>) dimensionItems.get(item.getItemId());
    List<String> itemsFilter = (List<String>) dimensionItems.get(itemFilter.getItemId());
    List<String> itemsOptionSet = (List<String>) dimensionItems.get(itemOptionSet.getItemId());
    List<String> itemsOptionSetFilter = (List<String>) dimensionItems.get(itemOptionSetFilter.getItemId());
    assertNotNull(itemsLegendSet);
    assertNotNull(itemsLegendSetFilter);
    assertNotNull(items);
    assertNotNull(itemsFilter);
    assertNotNull(itemsOptionSet);
    assertNotNull(itemsOptionSetFilter);
    assertEquals(4, itemsLegendSet.size());
    assertEquals(itemsLegendSet, Lists.newArrayList(leA.getUid(), leB.getUid(), leC.getUid(), leD.getUid()));
    assertEquals(3, itemsLegendSetFilter.size());
    assertTrue(itemsLegendSetFilter.containsAll(IdentifiableObjectUtils.getUids(Sets.newHashSet(leA, leB, leC))));
    assertTrue(items.isEmpty());
    assertTrue(itemsFilter.isEmpty());
    assertFalse(itemsOptionSet.isEmpty());
    assertEquals(2, itemsOptionSetFilter.size());
    assertTrue(itemsOptionSetFilter.containsAll(IdentifiableObjectUtils.getUids(Sets.newHashSet(opA, opB))));
}