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

Example 31 with SqlHelper

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

the class HibernateTrackedEntityInstanceStore method joinAttributeValueWithQueryParameter.

/**
 * Generates a single INNER JOIN for searching for an attribute by query
 * strings. Searches are done using lower() expression, since attribute
 * values are case insensitive. The query search is extremely slow compared
 * to alternatives. A query string (Can be multiple) has to match at least 1
 * attribute value for each attribute we have access to. We use Regex to
 * search, allowing both exact match and with wildcards (EQ or LIKE).
 *
 * @param params
 * @param attributes
 */
private void joinAttributeValueWithQueryParameter(TrackedEntityInstanceQueryParams params, StringBuilder attributes) {
    final String regexp = statementBuilder.getRegexpMatch();
    final String wordStart = statementBuilder.getRegexpWordStart();
    final String wordEnd = statementBuilder.getRegexpWordEnd();
    final String anyChar = "\\.*?";
    final String start = params.getQuery().isOperator(QueryOperator.LIKE) ? anyChar : wordStart;
    final String end = params.getQuery().isOperator(QueryOperator.LIKE) ? anyChar : wordEnd;
    SqlHelper orHlp = new SqlHelper(true);
    List<Long> itemIds = params.getAttributesAndFilters().stream().map(QueryItem::getItem).map(DimensionalItemObject::getId).collect(Collectors.toList());
    attributes.append("INNER JOIN trackedentityattributevalue Q ").append("ON Q.trackedentityinstanceid IN (").append(getCommaDelimitedString(itemIds)).append(") AND (");
    for (String queryToken : getTokens(params.getQuery().getFilter())) {
        final String query = statementBuilder.encode(queryToken, false);
        attributes.append(orHlp.or()).append("lower(Q.value) ").append(regexp).append(" '").append(start).append(StringUtils.lowerCase(query)).append(end).append(SINGLE_QUOTE);
    }
    attributes.append(")");
}

Example 32 with SqlHelper

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

the class HibernateTrackedEntityInstanceStore method getFromSubQueryProgramStageInstance.

/**
 * Generates an INNER JOIN with the program instances if event-filters are
 * specified. In the case of user assignment is part of the filter, we join
 * with the userinfo table as well.
 *
 * @param params
 * @return an SQL INNER JOIN for filtering on events.
 */
private String getFromSubQueryProgramStageInstance(TrackedEntityInstanceQueryParams params) {
    StringBuilder events = new StringBuilder();
    SqlHelper whereHlp = new SqlHelper(true);
    events.append("INNER JOIN (").append("SELECT PSI.programinstanceid ").append("FROM programstageinstance PSI ");
    if (params.hasAssignedUsers()) {
        events.append("INNER JOIN (").append("SELECT userinfoid AS userid ").append("FROM userinfo ").append("WHERE uid IN (").append(encodeAndQuote(params.getAssignedUsers())).append(") ").append(") AU ON AU.userid = PSI.assigneduserid");
    }
    if (params.hasEventStatus()) {
        String start = getMediumDateString(params.getEventStartDate());
        String end = getMediumDateString(params.getEventEndDate());
        if (params.isEventStatus(EventStatus.COMPLETED)) {
            events.append(getQueryDateConditionBetween(whereHlp, PSI_EXECUTIONDATE, start, end)).append(whereHlp.whereAnd()).append(PSI_STATUS).append(EQUALS).append(SINGLE_QUOTE).append(EventStatus.COMPLETED.name()).append(SINGLE_QUOTE).append(SPACE);
        } else if (params.isEventStatus(EventStatus.VISITED) || params.isEventStatus(EventStatus.ACTIVE)) {
            events.append(getQueryDateConditionBetween(whereHlp, PSI_EXECUTIONDATE, start, end)).append(whereHlp.whereAnd()).append(PSI_STATUS).append(EQUALS).append(SINGLE_QUOTE).append(EventStatus.ACTIVE.name()).append(SINGLE_QUOTE).append(SPACE);
        } else if (params.isEventStatus(EventStatus.SCHEDULE)) {
            events.append(getQueryDateConditionBetween(whereHlp, PSI_DUEDATE, start, end)).append(whereHlp.whereAnd()).append(PSI_STATUS).append(SPACE).append(IS_NOT_NULL).append(whereHlp.whereAnd()).append(PSI_EXECUTIONDATE).append(SPACE).append(IS_NULL).append(whereHlp.whereAnd()).append("date(now()) <= date(PSI.duedate) ");
        } else if (params.isEventStatus(EventStatus.OVERDUE)) {
            events.append(getQueryDateConditionBetween(whereHlp, PSI_DUEDATE, start, end)).append(whereHlp.whereAnd()).append(PSI_STATUS).append(SPACE).append(IS_NOT_NULL).append(whereHlp.whereAnd()).append(PSI_EXECUTIONDATE).append(SPACE).append(IS_NULL).append(whereHlp.whereAnd()).append("date(now()) > date(PSI.duedate) ");
        } else if (params.isEventStatus(EventStatus.SKIPPED)) {
            events.append(getQueryDateConditionBetween(whereHlp, PSI_DUEDATE, start, end)).append(whereHlp.whereAnd()).append(PSI_STATUS).append(EQUALS).append(SINGLE_QUOTE).append(EventStatus.SKIPPED.name()).append(SINGLE_QUOTE).append(SPACE);
        }
    }
    if (params.hasProgramStage()) {
        events.append(whereHlp.whereAnd()).append("PSI.programstageid = ").append(params.getProgramStage().getId()).append(SPACE);
    }
    if (params.isIncludeOnlyUnassignedEvents()) {
        events.append(whereHlp.whereAnd()).append("PSI.assigneduserid IS NULL ");
    }
    if (params.isIncludeOnlyAssignedEvents()) {
        events.append(whereHlp.whereAnd()).append("PSI.assigneduserid IS NOT NULL ");
    }
    if (!params.isIncludeDeleted()) {
        events.append(whereHlp.whereAnd()).append("PSI.deleted IS FALSE");
    }
    events.append(") PSI ON PSI.programinstanceid = PI.programinstanceid ");
    return events.toString();
}

Example 33 with SqlHelper

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

the class JdbcEventStore method getOrgUnitSql.

private String getOrgUnitSql(SqlHelper hlp, EventSearchParams params, List<OrganisationUnit> organisationUnits) {
    StringBuilder orgUnitSql = new StringBuilder();
    if (params.getOrgUnit() != null && !params.isPathOrganisationUnitMode()) {
        orgUnitSql.append(" ou.organisationunitid = " + params.getOrgUnit().getId() + " ");
    } else {
        SqlHelper orHlp = new SqlHelper(true);
        String path = "ou.path LIKE '";
        for (OrganisationUnit organisationUnit : organisationUnits) {
            if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.DESCENDANTS)) {
                orgUnitSql.append(orHlp.or()).append(path).append(organisationUnit.getPath()).append("%' ").append(hlp.whereAnd()).append(" ou.hierarchylevel > " + organisationUnit.getLevel());
            } else if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.CHILDREN)) {
                orgUnitSql.append(orHlp.or()).append(path).append(organisationUnit.getPath()).append("%' ").append(hlp.whereAnd()).append(" ou.hierarchylevel = " + (organisationUnit.getLevel() + 1));
            } else {
                orgUnitSql.append(orHlp.or()).append(path).append(organisationUnit.getPath()).append("%' ");
            }
        }
        if (!organisationUnits.isEmpty()) {
            orgUnitSql.insert(0, " (");
            orgUnitSql.append(") ");
            if (params.isPathOrganisationUnitMode()) {
                orgUnitSql.insert(0, " (");
                orgUnitSql.append(orHlp.or()).append(" (ou.organisationunitid = " + params.getOrgUnit().getId() + ")) ");
            }
        }
    }
    return orgUnitSql.toString();
}

Example 34 with SqlHelper

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

the class JdbcEventStore method buildGridSql.

private String buildGridSql(EventSearchParams params, List<OrganisationUnit> organisationUnits) {
    SqlHelper hlp = new SqlHelper();
    // ---------------------------------------------------------------------
    // Select clause
    // ---------------------------------------------------------------------
    StringBuilder sqlBuilder = new StringBuilder().append("select ").append(COLUMNS_ALIAS_MAP.entrySet().stream().map(col -> col.getKey() + " as " + col.getValue()).collect(Collectors.joining(", "))).append(" , ");
    for (QueryItem item : params.getDataElementsAndFilters()) {
        final String col = item.getItemId();
        final String dataValueValueSql = "psi.eventdatavalues #>> '{" + col + ", value}'";
        String queryCol = item.isNumeric() ? castToNumber(dataValueValueSql) : dataValueValueSql;
        queryCol += " as " + col + ", ";
        sqlBuilder.append(queryCol);
    }
    String intermediateSql = sqlBuilder.toString();
    sqlBuilder = new StringBuilder().append(removeLastComma(intermediateSql)).append(" ");
    // ---------------------------------------------------------------------
    // From and where clause
    // ---------------------------------------------------------------------
    sqlBuilder.append(getFromWhereClause(params, hlp, organisationUnits));
    // ---------------------------------------------------------------------
    // Order clause
    // ---------------------------------------------------------------------
    sqlBuilder.append(getGridOrderQuery(params));
    // ---------------------------------------------------------------------
    // Paging clause
    // ---------------------------------------------------------------------
    sqlBuilder.append(getEventPagingQuery(params));
    return sqlBuilder.toString();
}

Example 35 with SqlHelper

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

the class JdbcAnalyticsManager method getWhereClause.

/**
 * Generates the where clause of the query SQL.
 */
private String getWhereClause(DataQueryParams params, AnalyticsTableType tableType) {
    SqlHelper sqlHelper = new SqlHelper();
    String sql = "";
    for (DimensionalObject dim : params.getDimensions()) {
        if (!dim.getItems().isEmpty() && !dim.isFixed()) {
            String col = quoteAlias(dim.getDimensionName());
            sql += sqlHelper.whereAnd() + " " + col + " in (" + getQuotedCommaDelimitedString(getUids(dim.getItems())) + ") ";
        }
    }
    // ---------------------------------------------------------------------
    // Filters
    // ---------------------------------------------------------------------
    ListMap<String, DimensionalObject> filterMap = params.getDimensionFilterMap();
    for (String dimension : filterMap.keySet()) {
        List<DimensionalObject> filters = filterMap.get(dimension);
        if (DimensionalObjectUtils.anyDimensionHasItems(filters)) {
            sql += sqlHelper.whereAnd() + " ( ";
            for (DimensionalObject filter : filters) {
                if (filter.hasItems()) {
                    String col = quoteAlias(filter.getDimensionName());
                    sql += col + " in (" + getQuotedCommaDelimitedString(getUids(filter.getItems())) + ") or ";
                }
            }
            sql = removeLastOr(sql) + ") ";
        }
    }
    if (params.isDataApproval()) {
        sql += sqlHelper.whereAnd() + " ( ";
        for (OrganisationUnit unit : params.getDataApprovalLevels().keySet()) {
            String ouCol = quoteAlias(LEVEL_PREFIX + unit.getLevel());
            Integer level = params.getDataApprovalLevels().get(unit);
            sql += "(" + ouCol + " = '" + unit.getUid() + "' and " + quoteAlias(COL_APPROVALLEVEL) + " <= " + level + ") or ";
        }
        sql = removeLastOr(sql) + ") ";
    }
    if (params.isRestrictByOrgUnitOpeningClosedDate() && params.hasStartEndDateRestriction()) {
        sql += sqlHelper.whereAnd() + " (" + "(" + quoteAlias("ouopeningdate") + " <= '" + getMediumDateString(params.getStartDateRestriction()) + "' or " + quoteAlias("ouopeningdate") + " is null) and " + "(" + quoteAlias("oucloseddate") + " >= '" + getMediumDateString(params.getEndDateRestriction()) + "' or " + quoteAlias("oucloseddate") + " is null)) ";
    }
    if (params.isRestrictByCategoryOptionStartEndDate() && params.hasStartEndDateRestriction()) {
        sql += sqlHelper.whereAnd() + " (" + "(" + quoteAlias("costartdate") + " <= '" + getMediumDateString(params.getStartDateRestriction()) + "' or " + quoteAlias("costartdate") + " is null) and " + "(" + quoteAlias("coenddate") + " >= '" + getMediumDateString(params.getEndDateRestriction()) + "' or " + quoteAlias("coenddate") + " is null)) ";
    }
    if (tableType.hasPeriodDimension() && params.hasStartDate()) {
        sql += sqlHelper.whereAnd() + " " + quoteAlias("pestartdate") + "  >= '" + getMediumDateString(params.getStartDate()) + "' ";
    }
    if (tableType.hasPeriodDimension() && params.hasEndDate()) {
        sql += sqlHelper.whereAnd() + " " + quoteAlias("peenddate") + " <= '" + getMediumDateString(params.getEndDate()) + "' ";
    }
    if (params.isTimely()) {
        sql += sqlHelper.whereAnd() + " " + quoteAlias("timely") + " is true ";
    }
    if (!params.isSkipPartitioning() && params.hasPartitions()) {
        sql += sqlHelper.whereAnd() + " " + quoteAlias("year") + " in (" + TextUtils.getCommaDelimitedString(params.getPartitions().getPartitions()) + ") ";
    }
    if (params.getAggregationType().isFirstOrLastOrLastInPeriodAggregationType()) {
        sql += sqlHelper.whereAnd() + " " + quoteAlias("pe_rank") + " = 1 ";
    }
    return sql;
}