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

Example 1 with InQueryFilter

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

the class JdbcEnrollmentAnalyticsManager 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 params 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.
 *
 * @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)) {
        sql += hlp.whereAnd() + " ou in (" + getQuotedCommaDelimitedString(getUids(params.getDimensionOrFilterItems(ORGUNIT_DIM_ID))) + ") ";
    } else if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.CHILDREN)) {
        sql += hlp.whereAnd() + " ou in (" + getQuotedCommaDelimitedString(getUids(params.getOrganisationUnitChildren())) + ") ";
    } else // Descendants
    {
        sql += hlp.whereAnd() + " (";
        for (DimensionalItemObject object : params.getDimensionOrFilterItems(ORGUNIT_DIM_ID)) {
            OrganisationUnit unit = (OrganisationUnit) object;
            sql += "uidlevel" + unit.getLevel() + " = '" + unit.getUid() + "' or ";
        }
        sql = removeLastOr(sql) + ") ";
    }
    // ---------------------------------------------------------------------
    // 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 += "and " + col + " in (" + getQuotedCommaDelimitedString(getUids(dim.getItems())) + ") ";
    }
    if (params.hasProgramStage()) {
        sql += "and 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 += "and " + field + " " + filter.getSqlOperator() + " " + getSqlFilter(filter, item) + " ";
                }
            }
        }
    }
    for (QueryItem item : params.getItemFilters()) {
        if (item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                sql += "and " + 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 += "and (" + sqlFilter + ") ";
    }
    if (params.hasProgramStatus()) {
        sql += "and enrollmentstatus in (" + params.getProgramStatus().stream().map(p -> encode(p.name(), true)).collect(joining(",")) + ") ";
    }
    if (params.isCoordinatesOnly()) {
        sql += "and (longitude is not null and latitude is not null) ";
    }
    if (params.isGeometryOnly()) {
        sql += "and " + quoteAlias(params.getCoordinateField()) + " is not null ";
    }
    if (params.isCompletedOnly()) {
        sql += "and completeddate is not null ";
    }
    if (params.hasBbox()) {
        sql += "and " + quoteAlias(params.getCoordinateField()) + " && ST_MakeEnvelope(" + params.getBbox() + ",4326) ";
    }
    return sql;
}

Example 2 with InQueryFilter

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