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

Example 21 with QueryFilter

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

the class AbstractJdbcEventAnalyticsManagerTest method verifyGetSelectSqlWithTextDataElementIgnoringCase.

@Test
void verifyGetSelectSqlWithTextDataElementIgnoringCase() {
    DimensionalItemObject dio = new BaseDimensionalItemObject(dataElementA.getUid());
    QueryItem item = new QueryItem(dio);
    item.setValueType(ValueType.TEXT);
    QueryFilter queryFilter = new QueryFilter(QueryOperator.IEQ, "IEQ");
    String column = subject.getSelectSql(queryFilter, item, from, to);
    assertThat(column, is("lower(ax.\"" + dataElementA.getUid() + "\")"));
}

Example 22 with QueryFilter

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

the class AbstractJdbcEventAnalyticsManagerTest method verifyGetSelectSqlWithProgramIndicator.

@Test
void verifyGetSelectSqlWithProgramIndicator() {
    ProgramIndicator programIndicator = createProgramIndicator('A', programA, "9.0", null);
    QueryItem item = new QueryItem(programIndicator);
    subject.getSelectSql(new QueryFilter(), item, from, to);
    verify(programIndicatorService).getAnalyticsSql(programIndicator.getExpression(), programIndicator, from, to);
}

Example 23 with QueryFilter

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

the class JdbcEventAnalyticsManager method getWhereClause.

/**
 * Returns a from and where SQL clause. If this is a program indicator with
 * non-default boundaries, the relationship with the reporting period is
 * specified with where conditions on the enrollment or incident dates. If
 * the default boundaries is used, or the query does not include program
 * indicators, the periods are joined in from the analytics tables the
 * normal way. A where clause can never have a mix of indicators with
 * non-default boundaries and regular analytics table periods.
 * <p>
 * If the query has a non-default time field specified, the query will use
 * the period type columns from the {@code date period structure} resource
 * table through an alias to reflect the period aggregation.
 *
 * @param params the {@link EventQueryParams}.
 */
@Override
protected String getWhereClause(EventQueryParams params) {
    String sql = "";
    SqlHelper hlp = new SqlHelper();
    // ---------------------------------------------------------------------
    // Periods
    // ---------------------------------------------------------------------
    sql += hlp.whereAnd() + " " + timeFieldSqlRenderer.renderTimeFieldSql(params);
    if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.SELECTED)) {
        String orgUnitCol = quoteAlias(params.getOrgUnitFieldFallback());
        sql += hlp.whereAnd() + " " + orgUnitCol + OPEN_IN + getQuotedCommaDelimitedString(getUids(params.getDimensionOrFilterItems(ORGUNIT_DIM_ID))) + ") ";
    } else if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.CHILDREN)) {
        String orgUnitCol = quoteAlias(params.getOrgUnitFieldFallback());
        sql += hlp.whereAnd() + " " + orgUnitCol + OPEN_IN + getQuotedCommaDelimitedString(getUids(params.getOrganisationUnitChildren())) + ") ";
    } else // Descendants
    {
        String orgUnitAlias = getOrgUnitAlias(params);
        String sqlSnippet = getOrgDescendantsSqlSnippet(orgUnitAlias, params.getDimensionOrFilterItems(ORGUNIT_DIM_ID));
        if (sqlSnippet != null && !sqlSnippet.trim().isEmpty()) {
            sql += hlp.whereAnd() + " " + sqlSnippet;
        }
    }
    // ---------------------------------------------------------------------
    // Organisation unit group sets
    // ---------------------------------------------------------------------
    List<DimensionalObject> dynamicDimensions = params.getDimensionsAndFilters(Sets.newHashSet(DimensionType.ORGANISATION_UNIT_GROUP_SET, DimensionType.CATEGORY));
    for (DimensionalObject dim : dynamicDimensions) {
        String col = quoteAlias(dim.getDimensionName());
        sql += hlp.whereAnd() + " " + col + OPEN_IN + getQuotedCommaDelimitedString(getUids(dim.getItems())) + ") ";
    }
    if (params.hasProgramStage()) {
        sql += hlp.whereAnd() + " " + quoteAlias("ps") + " = '" + params.getProgramStage().getUid() + "' ";
    }
    for (QueryItem item : params.getItems()) {
        if (item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                String field = getSelectSql(filter, item, params.getEarliestStartDate(), params.getLatestEndDate());
                if (IN.equals(filter.getOperator())) {
                    InQueryFilter inQueryFilter = new InQueryFilter(field, statementBuilder.encode(filter.getFilter(), false), item.isText());
                    sql += hlp.whereAnd() + " " + inQueryFilter.getSqlFilter();
                } else {
                    sql += hlp.whereAnd() + " " + field + " " + filter.getSqlOperator() + " " + getSqlFilter(filter, item) + " ";
                }
            }
        }
    }
    for (QueryItem item : params.getItemFilters()) {
        if (item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                sql += hlp.whereAnd() + " " + getSelectSql(filter, item, params.getEarliestStartDate(), params.getLatestEndDate()) + " " + filter.getSqlOperator() + " " + getSqlFilter(filter, item) + " ";
            }
        }
    }
    if (params.hasProgramIndicatorDimension() && params.getProgramIndicator().hasFilter()) {
        String filter = programIndicatorService.getAnalyticsSql(params.getProgramIndicator().getFilter(), params.getProgramIndicator(), params.getEarliestStartDate(), params.getLatestEndDate());
        String sqlFilter = ExpressionUtils.asSql(filter);
        sql += hlp.whereAnd() + " (" + sqlFilter + ") ";
    }
    if (params.hasProgramIndicatorDimension()) {
        String anyValueFilter = programIndicatorService.getAnyValueExistsClauseAnalyticsSql(params.getProgramIndicator().getExpression(), params.getProgramIndicator().getAnalyticsType());
        if (anyValueFilter != null) {
            sql += hlp.whereAnd() + " (" + anyValueFilter + ") ";
        }
    }
    if (params.hasProgramStatus()) {
        sql += hlp.whereAnd() + " pistatus in (" + params.getProgramStatus().stream().map(p -> encode(p.name(), true)).collect(joining(",")) + ") ";
    }
    if (params.hasEventStatus()) {
        sql += hlp.whereAnd() + " psistatus in (" + params.getEventStatus().stream().map(e -> encode(e.name(), true)).collect(joining(",")) + ") ";
    }
    if (params.isCoordinatesOnly() || params.isGeometryOnly()) {
        if (params.isCoordinateOuFallback()) {
            sql += hlp.whereAnd() + " (" + quoteAlias(resolveCoordinateFieldColumnName(params.getCoordinateField(), params)) + " is not null or " + quoteAlias(resolveCoordinateFieldColumnName(params.getFallbackCoordinateField(), params)) + " is not null )";
        } else {
            sql += hlp.whereAnd() + " " + quoteAlias(resolveCoordinateFieldColumnName(params.getCoordinateField(), params)) + " is not null ";
        }
    }
    if (params.isCompletedOnly()) {
        sql += hlp.whereAnd() + " completeddate is not null ";
    }
    if (params.hasBbox()) {
        sql += hlp.whereAnd() + " " + quoteAlias(params.getCoordinateField()) + " && ST_MakeEnvelope(" + params.getBbox() + ",4326) ";
    }
    if (!params.isSkipPartitioning() && params.hasPartitions() && !params.hasNonDefaultBoundaries() && !params.hasTimeField()) {
        sql += hlp.whereAnd() + " " + quoteAlias("yearly") + OPEN_IN + TextUtils.getQuotedCommaDelimitedString(params.getPartitions().getPartitions()) + ") ";
    }
    if (params.getAggregationTypeFallback().isFirstOrLastPeriodAggregationType()) {
        sql += hlp.whereAnd() + " " + quoteAlias("pe_rank") + " = 1 ";
    }
    return sql;
}

Example 24 with QueryFilter

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

the class EventsAnalyticsManagerTest method createRequestParamsWithFilter.

private EventQueryParams createRequestParamsWithFilter(ValueType queryItemValueType) {
    EventQueryParams.Builder params = new EventQueryParams.Builder(createRequestParams(queryItemValueType));
    QueryItem queryItem = params.build().getItems().get(0);
    queryItem.addFilter(new QueryFilter(QueryOperator.GT, "10"));
    return params.build();
}

Example 25 with QueryFilter

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

the class TrackedEntityCriteriaMapper method map.

@Transactional(readOnly = true)
public TrackedEntityInstanceQueryParams map(TrackedEntityInstanceCriteria criteria) {
    TrackedEntityInstanceQueryParams params = new TrackedEntityInstanceQueryParams();
    final Date programEnrollmentStartDate = ObjectUtils.firstNonNull(criteria.getProgramEnrollmentStartDate(), criteria.getProgramStartDate());
    final Date programEnrollmentEndDate = ObjectUtils.firstNonNull(criteria.getProgramEnrollmentEndDate(), criteria.getProgramEndDate());
    Set<OrganisationUnit> possibleSearchOrgUnits = new HashSet<>();
    User user = currentUserService.getCurrentUser();
    if (user != null) {
        possibleSearchOrgUnits = user.getTeiSearchOrganisationUnitsWithFallback();
    }
    QueryFilter queryFilter = getQueryFilter(criteria.getQuery());
    Map<String, TrackedEntityAttribute> attributes = attributeService.getAllTrackedEntityAttributes().stream().collect(Collectors.toMap(TrackedEntityAttribute::getUid, att -> att));
    if (criteria.getAttribute() != null) {
        for (String attr : criteria.getAttribute()) {
            QueryItem it = getQueryItem(attr, attributes);
            params.getAttributes().add(it);
        }
    }
    if (criteria.getFilter() != null) {
        for (String filt : criteria.getFilter()) {
            QueryItem it = getQueryItem(filt, attributes);
            params.getFilters().add(it);
        }
    }
    for (String orgUnit : criteria.getOrgUnits()) {
        OrganisationUnit organisationUnit = organisationUnitService.getOrganisationUnit(orgUnit);
        if (organisationUnit == null) {
            throw new IllegalQueryException("Organisation unit does not exist: " + orgUnit);
        }
        if (!organisationUnitService.isInUserHierarchy(organisationUnit.getUid(), possibleSearchOrgUnits)) {
            throw new IllegalQueryException("Organisation unit is not part of the search scope: " + orgUnit);
        }
        params.getOrganisationUnits().add(organisationUnit);
    }
    validateAssignedUser(criteria);
    if (criteria.getOuMode() == OrganisationUnitSelectionMode.CAPTURE && user != null) {
        params.getOrganisationUnits().addAll(user.getOrganisationUnits());
    }
    Program program = validateProgram(criteria);
    List<OrderParam> orderParams = toOrderParams(criteria.getOrder());
    validateOrderParams(program, orderParams, attributes);
    params.setQuery(queryFilter).setProgram(program).setProgramStage(validateProgramStage(criteria, program)).setProgramStatus(criteria.getProgramStatus()).setFollowUp(criteria.getFollowUp()).setLastUpdatedStartDate(criteria.getLastUpdatedStartDate()).setLastUpdatedEndDate(criteria.getLastUpdatedEndDate()).setLastUpdatedDuration(criteria.getLastUpdatedDuration()).setProgramEnrollmentStartDate(programEnrollmentStartDate).setProgramEnrollmentEndDate(programEnrollmentEndDate).setProgramIncidentStartDate(criteria.getProgramIncidentStartDate()).setProgramIncidentEndDate(criteria.getProgramIncidentEndDate()).setTrackedEntityType(validateTrackedEntityType(criteria)).setOrganisationUnitMode(criteria.getOuMode()).setEventStatus(criteria.getEventStatus()).setEventStartDate(criteria.getEventStartDate()).setEventEndDate(criteria.getEventEndDate()).setAssignedUserSelectionMode(criteria.getAssignedUserMode()).setAssignedUsers(criteria.getAssignedUsers()).setTrackedEntityInstanceUids(criteria.getTrackedEntityInstances()).setSkipMeta(criteria.isSkipMeta()).setPage(criteria.getPage()).setPageSize(criteria.getPageSize()).setTotalPages(criteria.isTotalPages()).setSkipPaging(criteria.isSkipPaging()).setIncludeDeleted(criteria.isIncludeDeleted()).setIncludeAllAttributes(criteria.isIncludeAllAttributes()).setUser(user).setOrders(orderParams);
    return params;
}