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

Example 26 with SqlHelper

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

the class DefaultSqlViewService method getSqlForQuery.

private String getSqlForQuery(Grid grid, SqlView sqlView, Map<String, String> criteria, Map<String, String> variables, List<String> filters, List<String> fields) {
    boolean hasCriteria = criteria != null && !criteria.isEmpty();
    boolean hasFilter = filters != null && !filters.isEmpty();
    String sql = SqlViewUtils.substituteSqlVariables(sqlView.getSqlQuery(), variables);
    if (hasCriteria || hasFilter) {
        sql = SqlViewUtils.removeQuerySeparator(sql);
        String outerSql = "select " + QueryUtils.parseSelectFields(fields) + " from " + "(" + sql + ") as qry ";
        SqlHelper sqlHelper = new SqlHelper();
        if (hasCriteria) {
            outerSql += getCriteriaSqlClause(criteria, sqlHelper);
        }
        if (hasFilter) {
            outerSql += parseFilters(filters, sqlHelper);
        }
        sql = outerSql;
    }
    return sql;
}

Example 27 with SqlHelper

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

the class JdbcAnalyticsManager method getPartitionSql.

/**
     * If preAggregationMeasureCriteria is specified, generates a query which
     * provides a filtered view of the data according to the criteria .If not, 
     * returns the full view of the partition.
     */
private String getPartitionSql(DataQueryParams params, String partition) {
    if (params.isDataType(DataType.NUMERIC) && !params.getPreAggregateMeasureCriteria().isEmpty()) {
        SqlHelper sqlHelper = new SqlHelper();
        String sql = "";
        sql += "(select * from " + partition + " ";
        for (MeasureFilter filter : params.getPreAggregateMeasureCriteria().keySet()) {
            Double criterion = params.getPreAggregateMeasureCriteria().get(filter);
            sql += sqlHelper.whereAnd() + " value " + OPERATOR_SQL_MAP.get(filter) + " " + criterion + " ";
        }
        sql += ") as " + partition;
        return sql;
    } else {
        return partition;
    }
}

Example 28 with SqlHelper

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

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

the class HibernateTrackedEntityInstanceStore method getFromSubQuery.

/**
 * Generates the SQL of the subquery, used to find the correct subset of
 * tracked entity instances to return. Orchestrates all the different
 * segments of the SQL into a complete subquery.
 *
 * @param params
 * @param isCountQuery indicates if the query is a count query. In that case
 *        we skip order and limit.
 * @param isGridQuery indicates if the query is a grid query.
 * @return an SQL subquery
 */
private String getFromSubQuery(TrackedEntityInstanceQueryParams params, boolean isCountQuery, boolean isGridQuery) {
    SqlHelper whereAnd = new SqlHelper(true);
    StringBuilder fromSubQuery = new StringBuilder().append("(").append(getFromSubQuerySelect(params)).append(" FROM trackedentityinstance TEI ").append(getFromSubQueryJoinAttributeConditions(whereAnd, params)).append(getFromSubQueryJoinProgramOwnerConditions(params)).append(getFromSubQueryJoinOrgUnitConditions(params)).append(getFromSubQueryJoinOrderByAttributes(params)).append(getFromSubQueryTrackedEntityConditions(whereAnd, params)).append(getFromSubQueryProgramInstanceConditions(whereAnd, params));
    if (!isCountQuery) {
        // SORT
        fromSubQuery.append(getQueryOrderBy(true, params, isGridQuery)).append(getFromSubQueryLimitAndOffset(params));
    }
    return fromSubQuery.append(") TEI ").toString();
}

Example 30 with SqlHelper

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

the class HibernateTrackedEntityInstanceStore method getFromSubQueryJoinOrgUnitConditions.

/**
 * Generates an INNER JOIN for organisation units. If a program is
 * specified, we join on program ownership (PO), if not we join by tracked
 * entity instance (TEI). Based on the ouMode, they will boil down to either
 * DESCENDANTS (requiring matching on PATH), ALL (No constraints) or not
 * DESCENDANTS or ALL (SELECTED) which will match against a collection of
 * ids.
 *
 * @param params
 * @return a SQL INNER JOIN for organisation units
 */
private String getFromSubQueryJoinOrgUnitConditions(TrackedEntityInstanceQueryParams params) {
    StringBuilder orgUnits = new StringBuilder();
    params.handleOrganisationUnits();
    orgUnits.append(" INNER JOIN organisationunit OU ").append("ON OU.organisationunitid = ").append((params.hasProgram() ? "PO.organisationunitid " : "TEI.organisationunitid "));
    if (!params.hasOrganisationUnits()) {
        return orgUnits.toString();
    }
    if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.DESCENDANTS)) {
        SqlHelper orHlp = new SqlHelper(true);
        orgUnits.append("AND (");
        for (OrganisationUnit organisationUnit : params.getOrganisationUnits()) {
            orgUnits.append(orHlp.or()).append("OU.path LIKE '").append(organisationUnit.getPath()).append("%'");
        }
        orgUnits.append(") ");
    } else if (!params.isOrganisationUnitMode(OrganisationUnitSelectionMode.ALL)) {
        orgUnits.append("AND OU.organisationunitid IN (").append(getCommaDelimitedString(getIdentifiers(params.getOrganisationUnits()))).append(") ");
    }
    return orgUnits.toString();
}