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

Example 6 with SqlHelper

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

the class HibernateTrackedEntityInstanceStore method buildTrackedEntityInstanceHql.

private String buildTrackedEntityInstanceHql(TrackedEntityInstanceQueryParams params) {
    String hql = "select distinct tei from TrackedEntityInstance tei left join tei.trackedEntityAttributeValues";
    SqlHelper hlp = new SqlHelper(true);
    if (params.hasTrackedEntity()) {
        hql += hlp.whereAnd() + "tei.trackedEntity.uid='" + params.getTrackedEntity().getUid() + "'";
    }
    if (params.hasLastUpdatedStartDate()) {
        hql += hlp.whereAnd() + "tei.lastUpdated >= '" + getMediumDateString(params.getLastUpdatedStartDate()) + "'";
    }
    if (params.hasLastUpdatedEndDate()) {
        hql += hlp.whereAnd() + "tei.lastUpdated < '" + getMediumDateString(params.getLastUpdatedEndDate()) + "'";
    }
    if (params.hasOrganisationUnits()) {
        params.handleOrganisationUnits();
        if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.DESCENDANTS)) {
            String ouClause = "(";
            SqlHelper orHlp = new SqlHelper(true);
            for (OrganisationUnit organisationUnit : params.getOrganisationUnits()) {
                ouClause += orHlp.or() + "tei.organisationUnit.path LIKE '" + organisationUnit.getPath() + "%'";
            }
            ouClause += ")";
            hql += hlp.whereAnd() + ouClause;
        } else {
            hql += hlp.whereAnd() + "tei.organisationUnit.uid in (" + getQuotedCommaDelimitedString(getUids(params.getOrganisationUnits())) + ")";
        }
    }
    if (params.hasQuery()) {
        QueryFilter queryFilter = params.getQuery();
        String filter = queryFilter.getSqlFilter(queryFilter.getFilter());
        hql += hlp.whereAnd() + " exists (from TrackedEntityAttributeValue teav where teav.entityInstance=tei";
        hql += " and teav.plainValue " + queryFilter.getSqlOperator() + filter + ")";
    }
    if (params.hasFilters()) {
        for (QueryItem queryItem : params.getFilters()) {
            for (QueryFilter queryFilter : queryItem.getFilters()) {
                String filter = queryFilter.getSqlFilter(StringUtils.lowerCase(queryFilter.getFilter()));
                hql += hlp.whereAnd() + " exists (from TrackedEntityAttributeValue teav where teav.entityInstance=tei";
                hql += " and teav.attribute.uid='" + queryItem.getItemId() + "'";
                if (queryItem.isNumeric()) {
                    hql += " and teav.plainValue " + queryFilter.getSqlOperator() + filter + ")";
                } else {
                    hql += " and lower(teav.plainValue) " + queryFilter.getSqlOperator() + filter + ")";
                }
            }
        }
    }
    if (params.hasProgram()) {
        hql += hlp.whereAnd() + "exists (from ProgramInstance pi where pi.entityInstance=tei";
        hql += " and pi.program.uid = '" + params.getProgram().getUid() + "'";
        if (params.hasProgramStatus()) {
            hql += hlp.whereAnd() + "pi.status = " + params.getProgramStatus();
        }
        if (params.hasFollowUp()) {
            hql += hlp.whereAnd() + "pi.followup = " + params.getFollowUp();
        }
        if (params.hasProgramEnrollmentStartDate()) {
            hql += hlp.whereAnd() + "pi.enrollmentDate >= '" + getMediumDateString(params.getProgramEnrollmentStartDate()) + "'";
        }
        if (params.hasProgramEnrollmentEndDate()) {
            hql += hlp.whereAnd() + "pi.enrollmentDate < '" + getMediumDateString(params.getProgramEnrollmentEndDate()) + "'";
        }
        if (params.hasProgramIncidentStartDate()) {
            hql += hlp.whereAnd() + "pi.incidentDate >= '" + getMediumDateString(params.getProgramIncidentStartDate()) + "'";
        }
        if (params.hasProgramIncidentEndDate()) {
            hql += hlp.whereAnd() + "pi.incidentDate < '" + getMediumDateString(params.getProgramIncidentEndDate()) + "'";
        }
        hql += " and pi.deleted is false";
        hql += ")";
    }
    hql += hlp.whereAnd() + " tei.deleted is false ";
    return hql;
}

Example 7 with SqlHelper

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

the class HibernateTrackedEntityInstanceStore method getTrackedEntityInstanceCount.

@Override
public int getTrackedEntityInstanceCount(TrackedEntityInstanceQueryParams params) {
    SqlHelper hlp = new SqlHelper();
    // ---------------------------------------------------------------------
    // Select clause
    // ---------------------------------------------------------------------
    String sql = "select count(tei.uid) as " + TRACKED_ENTITY_INSTANCE_ID + " ";
    // ---------------------------------------------------------------------
    // From and where clause
    // ---------------------------------------------------------------------
    sql += getFromWhereClause(params, hlp);
    // ---------------------------------------------------------------------
    // Query
    // ---------------------------------------------------------------------
    Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
    log.debug("Tracked entity instance count SQL: " + sql);
    return count;
}

Example 8 with SqlHelper

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

the class JdbcAnalyticsManager method getFromWhereClause.

/**
     * Generates the from clause of the query SQL.
     */
private String getFromWhereClause(DataQueryParams params, String partition) {
    SqlHelper sqlHelper = new SqlHelper();
    String sql = "from " + getPartitionSql(params, partition) + " ";
    for (DimensionalObject dim : params.getDimensions()) {
        if (!dim.getItems().isEmpty() && !dim.isFixed()) {
            String col = statementBuilder.columnQuote(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 = statementBuilder.columnQuote(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 = LEVEL_PREFIX + unit.getLevel();
            Integer level = params.getDataApprovalLevels().get(unit);
            sql += "(" + ouCol + " = '" + unit.getUid() + "' and " + COL_APPROVALLEVEL + " <= " + level + ") or ";
        }
        sql = removeLastOr(sql) + ") ";
    }
    if (params.isRestrictByOrgUnitOpeningClosedDate() && params.hasStartEndDate()) {
        sql += sqlHelper.whereAnd() + " (" + "(" + statementBuilder.columnQuote("ouopeningdate") + " <= '" + getMediumDateString(params.getStartDate()) + "' or " + statementBuilder.columnQuote("ouopeningdate") + " is null) and " + "(" + statementBuilder.columnQuote("oucloseddate") + " >= '" + getMediumDateString(params.getEndDate()) + "' or " + statementBuilder.columnQuote("oucloseddate") + " is null)) ";
    }
    if (params.isRestrictByCategoryOptionStartEndDate() && params.hasStartEndDate()) {
        sql += sqlHelper.whereAnd() + " (" + "(" + statementBuilder.columnQuote("costartdate") + " <= '" + getMediumDateString(params.getStartDate()) + "' or " + statementBuilder.columnQuote("costartdate") + " is null) and " + "(" + statementBuilder.columnQuote("coenddate") + " >= '" + getMediumDateString(params.getEndDate()) + "' or " + statementBuilder.columnQuote("coenddate") + " is null)) ";
    }
    if (!params.isRestrictByOrgUnitOpeningClosedDate() && !params.isRestrictByCategoryOptionStartEndDate() && params.hasStartEndDate()) {
        sql += sqlHelper.whereAnd() + " " + statementBuilder.columnQuote("pestartdate") + "  >= '" + getMediumDateString(params.getStartDate()) + "' ";
        sql += "and " + statementBuilder.columnQuote("peenddate") + " <= '" + getMediumDateString(params.getEndDate()) + "' ";
    }
    if (params.isTimely()) {
        sql += sqlHelper.whereAnd() + " timely is true ";
    }
    return sql;
}

Example 9 with SqlHelper

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

the class JdbcAnalyticsManager method getMeasureCriteriaSql.

/**
     * Returns a HAVING clause restricting the result based on the measure criteria
     */
private String getMeasureCriteriaSql(DataQueryParams params) {
    SqlHelper sqlHelper = new SqlHelper();
    String sql = " ";
    for (MeasureFilter filter : params.getMeasureCriteria().keySet()) {
        Double criterion = params.getMeasureCriteria().get(filter);
        sql += sqlHelper.havingAnd() + " " + getNumericValueColumn(params) + " " + OPERATOR_SQL_MAP.get(filter) + " " + criterion + " ";
    }
    return sql;
}

Example 10 with SqlHelper

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

the class JdbcRawAnalyticsManager method getStatement.

private String getStatement(DataQueryParams params, String partition) {
    List<String> dimensionColumns = params.getDimensions().stream().map(d -> statementBuilder.columnQuote(d.getDimensionName())).collect(Collectors.toList());
    setOrgUnitSelect(params, dimensionColumns);
    SqlHelper sqlHelper = new SqlHelper();
    String sql = "select " + StringUtils.join(dimensionColumns, ", ") + ", " + DIM_NAME_OU + ", value " + "from " + partition + " ax " + "inner join organisationunit ou on ax.ou = ou.uid " + "inner join _periodstructure ps on ax.pe = ps.iso ";
    for (DimensionalObject dim : params.getDimensions()) {
        if (!dim.getItems().isEmpty() && !dim.isFixed()) {
            String col = statementBuilder.columnQuote(dim.getDimensionName());
            if (DimensionalObject.ORGUNIT_DIM_ID.equals(dim.getDimension())) {
                sql += sqlHelper.whereAnd() + " (";
                for (DimensionalItemObject item : dim.getItems()) {
                    OrganisationUnit unit = (OrganisationUnit) item;
                    sql += DIM_NAME_OU + " like '" + unit.getPath() + "%' or ";
                }
                sql = TextUtils.removeLastOr(sql) + ") ";
            } else {
                sql += sqlHelper.whereAnd() + " " + col + " in (" + getQuotedCommaDelimitedString(getUids(dim.getItems())) + ") ";
            }
        }
    }
    if (params.hasStartEndDate()) {
        sql += sqlHelper.whereAnd() + " " + "ps.startdate >= '" + DateUtils.getMediumDateString(params.getStartDate()) + "' and " + "ps.enddate <= '" + DateUtils.getMediumDateString(params.getEndDate()) + "' ";
    }
    return sql;
}