Examples with SqlHelper org.hisp.dhis.commons.util.SqlHelper used on opensource projects

Example 36 with SqlHelper

use of org.hisp.dhis.commons.util.SqlHelper 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 37 with SqlHelper

use of org.hisp.dhis.commons.util.SqlHelper in project dhis2-core by dhis2.

the class HibernateOrganisationUnitStore method buildOrganisationUnitDistinctUidsSql.

private String buildOrganisationUnitDistinctUidsSql(OrganisationUnitQueryParams params) {
    SqlHelper hlp = new SqlHelper();
    String sql = "select distinct o.uid from organisationunit o ";
    if (params.isFetchChildren()) {
        sql += " left outer join organisationunit c ON o.organisationunitid = c.parentid ";
    }
    if (params.hasParents()) {
        sql += hlp.whereAnd() + " (";
        for (OrganisationUnit parent : params.getParents()) {
            sql += "o.path like '" + escapeSql(parent.getPath()) + "%'" + " or ";
        }
        sql = TextUtils.removeLastOr(sql) + ") ";
    }
    return sql;
}

Example 38 with SqlHelper

use of org.hisp.dhis.commons.util.SqlHelper in project dhis2-core by dhis2.

the class HibernateProgramMessageStore method getHqlQuery.

// -------------------------------------------------------------------------
// Supportive Methods
// -------------------------------------------------------------------------
private Query<ProgramMessage> getHqlQuery(ProgramMessageQueryParams params) {
    SqlHelper helper = new SqlHelper(true);
    String hql = " select distinct pm from ProgramMessage pm ";
    if (params.hasProgramInstance()) {
        hql += helper.whereAnd() + "pm.programInstance = :programInstance";
    }
    if (params.hasProgramStageInstance()) {
        hql += helper.whereAnd() + "pm.programStageInstance = :programStageInstance";
    }
    hql += params.getMessageStatus() != null ? helper.whereAnd() + "pm.messageStatus = :messageStatus" : "";
    hql += params.getAfterDate() != null ? helper.whereAnd() + "pm.processeddate > :processeddate" : "";
    hql += params.getBeforeDate() != null ? helper.whereAnd() + "pm.processeddate < :processeddate" : "";
    Query<ProgramMessage> query = getQuery(hql);
    if (params.hasProgramInstance()) {
        query.setParameter("programInstance", params.getProgramInstance());
    }
    if (params.hasProgramStageInstance()) {
        query.setParameter("programStageInstance", params.getProgramStageInstance());
    }
    if (params.getMessageStatus() != null) {
        query.setParameter("messageStatus", params.getMessageStatus());
    }
    if (params.getAfterDate() != null) {
        query.setParameter("processeddate", params.getAfterDate());
    }
    if (params.getBeforeDate() != null) {
        query.setParameter("processeddate", params.getBeforeDate());
    }
    return query;
}

Example 39 with SqlHelper

use of org.hisp.dhis.commons.util.SqlHelper in project dhis2-core by dhis2.

the class HibernateValidationResultStore method getAllUnreportedValidationResults.

@Override
public List<ValidationResult> getAllUnreportedValidationResults() {
    SqlHelper sqlHelper = new SqlHelper();
    // as we use where already below
    sqlHelper.whereAnd();
    return getQuery("from ValidationResult vr where vr.notificationSent = false" + getUserRestrictions(sqlHelper)).list();
}