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

Example 6 with QueryFilter

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

the class HibernateTrackedEntityAttributeStore method getTrackedEntityInstanceUidWithUniqueAttributeValue.

@Override
public Optional<String> getTrackedEntityInstanceUidWithUniqueAttributeValue(TrackedEntityInstanceQueryParams params) {
    // ---------------------------------------------------------------------
    // Select clause
    // ---------------------------------------------------------------------
    SqlHelper hlp = new SqlHelper(true);
    String hql = "select tei.uid from TrackedEntityInstance tei ";
    if (params.hasOrganisationUnits()) {
        String orgUnitUids = params.getOrganisationUnits().stream().map(OrganisationUnit::getUid).collect(Collectors.joining(", ", "'", "'"));
        hql += "inner join tei.organisationUnit as ou ";
        hql += hlp.whereAnd() + " ou.uid in (" + orgUnitUids + ") ";
    }
    for (QueryItem item : params.getAttributes()) {
        for (QueryFilter filter : item.getFilters()) {
            final String encodedFilter = filter.getSqlFilter(statementBuilder.encode(StringUtils.lowerCase(filter.getFilter()), false));
            hql += hlp.whereAnd() + " exists (from TrackedEntityAttributeValue teav where teav.entityInstance=tei";
            hql += " and teav.attribute.uid='" + item.getItemId() + "'";
            if (item.isNumeric()) {
                hql += " and teav.plainValue " + filter.getSqlOperator() + encodedFilter + ")";
            } else {
                hql += " and lower(teav.plainValue) " + filter.getSqlOperator() + encodedFilter + ")";
            }
        }
    }
    if (!params.isIncludeDeleted()) {
        hql += hlp.whereAnd() + " tei.deleted is false";
    }
    Query<String> query = getTypedQuery(hql);
    Iterator<String> it = query.iterate();
    if (it.hasNext()) {
        return Optional.of(it.next());
    }
    return Optional.empty();
}

Example 7 with QueryFilter

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

the class HibernateTrackedEntityInstanceStore method joinAttributeValueWithoutQueryParameter.

/**
 * Generates a single INNER JOIN for each attribute we are searching on. We
 * can search by a range of operators. All searching is using lower() since
 * attribute values are case insensitive.
 *
 * @param attributes
 * @param filterItems
 */
private void joinAttributeValueWithoutQueryParameter(StringBuilder attributes, List<QueryItem> filterItems) {
    for (QueryItem queryItem : filterItems) {
        String col = statementBuilder.columnQuote(queryItem.getItemId());
        String teaId = col + ".trackedentityattributeid";
        String teav = "lower(" + col + ".value)";
        String teiid = col + ".trackedentityinstanceid";
        attributes.append(" INNER JOIN trackedentityattributevalue ").append(col).append(" ON ").append(teaId).append(EQUALS).append(queryItem.getItem().getId()).append(" AND ").append(teiid).append(" = TEI.trackedentityinstanceid ");
        for (QueryFilter filter : queryItem.getFilters()) {
            String encodedFilter = statementBuilder.encode(filter.getFilter(), false);
            attributes.append("AND ").append(teav).append(SPACE).append(filter.getSqlOperator()).append(SPACE).append(StringUtils.lowerCase(filter.getSqlFilter(encodedFilter)));
        }
    }
}

Example 8 with QueryFilter

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

the class JdbcEventStore method getEventSelectQuery.

private String getEventSelectQuery(EventSearchParams params, List<OrganisationUnit> organisationUnits, User user) {
    SqlHelper hlp = new SqlHelper();
    StringBuilder sqlBuilder = new StringBuilder().append("select " + getEventSelectIdentifiersByIdScheme(params.getIdSchemes()) + " psi.uid as psi_uid, " + "ou.uid as ou_uid, p.uid as p_uid, ps.uid as ps_uid, coc.uid as coc_uid, " + "psi.programstageinstanceid as psi_id, psi.status as psi_status, psi.executiondate as psi_executiondate, " + "psi.eventdatavalues as psi_eventdatavalues, psi.duedate as psi_duedate, psi.completedby as psi_completedby, psi.storedby as psi_storedby, " + "psi.created as psi_created, psi.createdbyuserinfo as psi_createdbyuserinfo, psi.lastupdated as psi_lastupdated, psi.lastupdatedbyuserinfo as psi_lastupdatedbyuserinfo, " + "psi.completeddate as psi_completeddate, psi.deleted as psi_deleted, " + "ST_AsText( psi.geometry ) as psi_geometry, au.uid as user_assigned, (au.firstName || ' ' || au.surName) as user_assigned_name," + "au.username as user_assigned_username, cocco.categoryoptionid AS cocco_categoryoptionid, deco.uid AS deco_uid, ");
    if ((params.getCategoryOptionCombo() == null || params.getCategoryOptionCombo().isDefault()) && !isSuper(user)) {
        sqlBuilder.append("decoa.can_access AS decoa_can_access, cocount.option_size AS option_size, ");
    }
    for (QueryItem item : params.getDataElementsAndFilters()) {
        final String col = item.getItemId();
        final String dataValueValueSql = "psi.eventdatavalues #>> '{" + col + ", value}'";
        String queryCol = " " + (item.isNumeric() ? castToNumber(dataValueValueSql) : lower(dataValueValueSql));
        queryCol += " as " + col + ", ";
        sqlBuilder.append(queryCol);
    }
    sqlBuilder.append("pi.uid as pi_uid, pi.status as pi_status, pi.followup as pi_followup, " + "p.type as p_type, ps.uid as ps_uid, ou.name as ou_name, " + "tei.trackedentityinstanceid as tei_id, tei.uid as tei_uid, teiou.uid as tei_ou, teiou.name as tei_ou_name, tei.created as tei_created, tei.inactive as tei_inactive " + "from programstageinstance psi " + "inner join programinstance pi on pi.programinstanceid=psi.programinstanceid " + "inner join program p on p.programid=pi.programid " + "inner join programstage ps on ps.programstageid=psi.programstageid " + "inner join categoryoptioncombo coc on coc.categoryoptioncomboid=psi.attributeoptioncomboid " + "inner join categoryoptioncombos_categoryoptions cocco on psi.attributeoptioncomboid=cocco.categoryoptioncomboid " + "inner join dataelementcategoryoption deco on cocco.categoryoptionid=deco.categoryoptionid " + "left join trackedentityprogramowner po on (pi.trackedentityinstanceid=po.trackedentityinstanceid) " + "inner join organisationunit ou on (coalesce(po.organisationunitid, psi.organisationunitid)=ou.organisationunitid) " + "left join trackedentityinstance tei on tei.trackedentityinstanceid=pi.trackedentityinstanceid " + "left join organisationunit teiou on (tei.organisationunitid=teiou.organisationunitid) " + "left join userinfo au on (psi.assigneduserid=au.userinfoid) ");
    Set<String> joinedColumns = new HashSet<>();
    String eventDataValuesWhereSql = "";
    for (QueryItem item : params.getDataElementsAndFilters()) {
        final String col = item.getItemId();
        final String optCol = item.getItemId() + "opt";
        final String dataValueValueSql = "psi.eventdatavalues #>> '{" + col + ", value}'";
        if (!joinedColumns.contains(col)) {
            if (item.hasOptionSet() && item.hasFilter()) {
                sqlBuilder.append("inner join optionvalue as " + optCol + " on lower(" + optCol + ".code) = " + "lower(" + dataValueValueSql + ") and " + optCol + ".optionsetid = " + item.getOptionSet().getId() + " ");
            }
            joinedColumns.add(col);
        }
        if (item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                final String encodedFilter = statementBuilder.encode(filter.getFilter(), false);
                final String queryCol = item.isNumeric() ? " CAST( " + dataValueValueSql + " AS NUMERIC)" : "lower( " + dataValueValueSql + " )";
                if (!item.hasOptionSet()) {
                    if (!eventDataValuesWhereSql.isEmpty()) {
                        eventDataValuesWhereSql += " and ";
                    }
                    if (QueryOperator.LIKE.getValue().equalsIgnoreCase(filter.getSqlOperator())) {
                        eventDataValuesWhereSql += " " + queryCol + " " + filter.getSqlOperator() + " " + StringUtils.lowerCase(filter.getSqlFilter(encodedFilter)) + " ";
                    } else {
                        eventDataValuesWhereSql += " " + queryCol + " " + filter.getSqlOperator() + " " + StringUtils.lowerCase(item.isNumeric() ? encodedFilter : filter.getSqlFilter(encodedFilter)) + " ";
                    }
                } else if (QueryOperator.IN.getValue().equalsIgnoreCase(filter.getSqlOperator())) {
                    sqlBuilder.append("and " + queryCol + " " + filter.getSqlOperator() + " " + StringUtils.lowerCase(item.isNumeric() ? encodedFilter : filter.getSqlFilter(encodedFilter)) + " ");
                } else if (QueryOperator.LIKE.getValue().equalsIgnoreCase(filter.getSqlOperator())) {
                    sqlBuilder.append("and lower(" + optCol + DOT_NAME + " " + filter.getSqlOperator() + " " + StringUtils.lowerCase(filter.getSqlFilter(encodedFilter)) + " ");
                } else {
                    sqlBuilder.append("and lower(" + optCol + DOT_NAME + " " + filter.getSqlOperator() + " " + StringUtils.lowerCase(item.isNumeric() ? encodedFilter : filter.getSqlFilter(encodedFilter)) + " ");
                }
            }
        }
    }
    if ((params.getCategoryOptionCombo() == null || params.getCategoryOptionCombo().isDefault()) && !isSuper(user)) {
        sqlBuilder.append(getCategoryOptionSharingForUser(user));
    }
    if (!eventDataValuesWhereSql.isEmpty()) {
        sqlBuilder.append(hlp.whereAnd() + eventDataValuesWhereSql + " ");
    }
    if (params.getTrackedEntityInstance() != null) {
        sqlBuilder.append(hlp.whereAnd() + " tei.trackedentityinstanceid=" + params.getTrackedEntityInstance().getId() + " ");
    }
    if (params.getProgram() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" p.programid = ").append(params.getProgram().getId()).append(" ");
    }
    if (params.getProgramStage() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" ps.programstageid = ").append(params.getProgramStage().getId()).append(" ");
    }
    if (params.getProgramStatus() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" pi.status = '").append(params.getProgramStatus()).append("' ");
    }
    if (params.getFollowUp() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" pi.followup is ").append(params.getFollowUp() ? "true" : "false").append(" ");
    }
    sqlBuilder.append(addLastUpdatedFilters(params, hlp, true));
    // Comparing milliseconds instead of always creating new Date( 0 );
    if (params.getSkipChangedBefore() != null && params.getSkipChangedBefore().getTime() > 0) {
        String skipChangedBefore = DateUtils.getLongDateString(params.getSkipChangedBefore());
        sqlBuilder.append(hlp.whereAnd()).append(PSI_LASTUPDATED_GT).append(skipChangedBefore).append("' ");
    }
    if (params.getCategoryOptionCombo() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" psi.attributeoptioncomboid = ").append(params.getCategoryOptionCombo().getId()).append(" ");
    }
    if (!organisationUnits.isEmpty() || params.getOrgUnit() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(getOrgUnitSql(hlp, params, organisationUnits));
    }
    if (params.getStartDate() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" (psi.executiondate >= '").append(getMediumDateString(params.getStartDate())).append("' ").append("or (psi.executiondate is null and psi.duedate >= '").append(getMediumDateString(params.getStartDate())).append("')) ");
    }
    if (params.getEndDate() != null) {
        Date dateAfterEndDate = getDateAfterAddition(params.getEndDate(), 1);
        sqlBuilder.append(hlp.whereAnd()).append(" (psi.executiondate < '").append(getMediumDateString(dateAfterEndDate)).append("' ").append("or (psi.executiondate is null and psi.duedate < '").append(getMediumDateString(dateAfterEndDate)).append("')) ");
    }
    if (params.getProgramType() != null) {
        sqlBuilder.append(hlp.whereAnd()).append(" p.type = '").append(params.getProgramType()).append("' ");
    }
    if (params.getEventStatus() != null) {
        if (params.getEventStatus() == EventStatus.VISITED) {
            sqlBuilder.append(hlp.whereAnd()).append(PSI_STATUS_EQ).append(EventStatus.ACTIVE.name()).append("' and psi.executiondate is not null ");
        } else if (params.getEventStatus() == EventStatus.OVERDUE) {
            sqlBuilder.append(hlp.whereAnd()).append(" date(now()) > date(psi.duedate) and psi.status = '").append(EventStatus.SCHEDULE.name()).append("' ");
        } else {
            sqlBuilder.append(hlp.whereAnd()).append(PSI_STATUS_EQ).append(params.getEventStatus().name()).append("' ");
        }
    }
    if (params.getEvents() != null && !params.getEvents().isEmpty() && !params.hasFilters()) {
        sqlBuilder.append(hlp.whereAnd()).append(" (psi.uid in (").append(getQuotedCommaDelimitedString(params.getEvents())).append(")) ");
    }
    if (params.hasAssignedUsers()) {
        sqlBuilder.append(hlp.whereAnd()).append(" (au.uid in (").append(getQuotedCommaDelimitedString(params.getAssignedUsers())).append(")) ");
    }
    if (params.isIncludeOnlyUnassignedEvents()) {
        sqlBuilder.append(hlp.whereAnd()).append(" (au.uid is null) ");
    }
    if (params.isIncludeOnlyAssignedEvents()) {
        sqlBuilder.append(hlp.whereAnd()).append(" (au.uid is not null) ");
    }
    if (!params.isIncludeDeleted()) {
        sqlBuilder.append(hlp.whereAnd()).append(" psi.deleted is false ");
    }
    if (params.hasSecurityFilter()) {
        sqlBuilder.append(hlp.whereAnd() + " (p.uid in (" + getQuotedCommaDelimitedString(params.getAccessiblePrograms()) + ")) ");
        sqlBuilder.append(hlp.whereAnd() + " (ps.uid in (" + getQuotedCommaDelimitedString(params.getAccessibleProgramStages()) + ")) ");
    }
    if (params.isSynchronizationQuery()) {
        sqlBuilder.append(hlp.whereAnd()).append(" psi.lastupdated > psi.lastsynchronized ");
    }
    if (!CollectionUtils.isEmpty(params.getProgramInstances())) {
        sqlBuilder.append(hlp.whereAnd()).append(" (pi.uid in (" + getQuotedCommaDelimitedString(params.getProgramInstances()) + "))");
    }
    return sqlBuilder.toString();
}

Example 9 with QueryFilter

use of org.hisp.dhis.common.QueryFilter 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 10 with QueryFilter

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

the class TrackedEntityInstanceQueryTest method testIfUniqueFiltersArePresentInAttributesOrFilters.

@Test
void testIfUniqueFiltersArePresentInAttributesOrFilters() {
    TrackedEntityInstanceQueryParams params = new TrackedEntityInstanceQueryParams();
    QueryItem nonUniq1 = new QueryItem(new TrackedEntityAttribute(), null, ValueType.TEXT, AggregationType.NONE, null, false);
    QueryItem nonUniq2 = new QueryItem(new TrackedEntityAttribute(), null, ValueType.TEXT, AggregationType.NONE, null, false);
    QueryItem uniq1 = new QueryItem(new TrackedEntityAttribute(), null, ValueType.TEXT, AggregationType.NONE, null, true);
    QueryFilter qf = new QueryFilter(QueryOperator.EQ, "test");
    nonUniq1.getFilters().add(qf);
    nonUniq2.getFilters().add(qf);
    params.addAttribute(nonUniq1);
    params.addAttribute(nonUniq2);
    params.addAttribute(uniq1);
    assertEquals(params.hasUniqueFilter(), false);
    uniq1.getFilters().add(qf);
    assertEquals(params.hasUniqueFilter(), true);
    params.getAttributes().clear();
    params.addFilter(nonUniq1);
    params.addFilter(nonUniq2);
    assertEquals(params.hasUniqueFilter(), false);
    params.addFilter(uniq1);
    assertEquals(params.hasUniqueFilter(), true);
}