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

Example 26 with QueryItem

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

the class ActivityReportingServiceImpl method findLostToFollowUp.

@Override
public String findLostToFollowUp(int orgUnitId, String searchEventInfos) throws NotAllowedException {
    String[] searchEventInfosArray = searchEventInfos.split("-");
    EventStatus eventStatus = EventStatus.ACTIVE;
    if (searchEventInfosArray[1].equalsIgnoreCase("Scheduled in future")) {
        eventStatus = EventStatus.SCHEDULE;
    } else if (searchEventInfosArray[1].equalsIgnoreCase("Overdue")) {
        eventStatus = EventStatus.OVERDUE;
    }
    String eventsInfo = "";
    Calendar toCalendar = new GregorianCalendar();
    toCalendar.add(Calendar.DATE, -1);
    toCalendar.add(Calendar.YEAR, 100);
    Date toDate = toCalendar.getTime();
    Calendar fromCalendar = new GregorianCalendar();
    fromCalendar.add(Calendar.DATE, -1);
    fromCalendar.add(Calendar.YEAR, -100);
    Date fromDate = fromCalendar.getTime();
    TrackedEntityInstanceQueryParams param = new TrackedEntityInstanceQueryParams();
    List<TrackedEntityAttribute> trackedEntityAttributeList = new ArrayList<>(attributeService.getTrackedEntityAttributesByDisplayOnVisitSchedule(true));
    for (TrackedEntityAttribute trackedEntityAttribute : trackedEntityAttributeList) {
        QueryItem queryItem = new QueryItem(trackedEntityAttribute);
        param.addAttribute(queryItem);
    }
    param.addOrganisationUnit(organisationUnitService.getOrganisationUnit(orgUnitId));
    param.setEventStatus(eventStatus);
    param.setEventStartDate(fromDate);
    param.setEventEndDate(toDate);
    Grid programStageInstanceGrid = entityInstanceService.getTrackedEntityInstancesGrid(param);
    List<List<Object>> rows = programStageInstanceGrid.getRows();
    if (rows.size() == 0) {
        throw NotAllowedException.NO_EVENT_FOUND;
    } else if (rows.size() > 0) {
        for (List<Object> row : rows) {
            for (int i = 5; i < row.size(); i++) {
                eventsInfo += row.get(i) + "/";
                if (i == row.size() - 1) {
                    eventsInfo += "$";
                }
            }
        }
        throw new NotAllowedException(eventsInfo);
    } else {
        return "";
    }
}

Example 27 with QueryItem

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

the class HibernateTrackedEntityInstanceStore method getTrackedEntityInstancesGrid.

@Override
public List<Map<String, String>> getTrackedEntityInstancesGrid(TrackedEntityInstanceQueryParams params) {
    SqlHelper hlp = new SqlHelper();
    // ---------------------------------------------------------------------
    // Select clause
    // ---------------------------------------------------------------------
    String sql = "select tei.uid as " + TRACKED_ENTITY_INSTANCE_ID + ", " + "tei.created as " + CREATED_ID + ", " + "tei.lastupdated as " + LAST_UPDATED_ID + ", " + "ou.uid as " + ORG_UNIT_ID + ", " + "ou.name as " + ORG_UNIT_NAME + ", " + "te.uid as " + TRACKED_ENTITY_ID + ", " + (params.isIncludeDeleted() ? "tei.deleted as " + DELETED + ", " : "") + "tei.inactive as " + INACTIVE_ID + ", ";
    for (QueryItem item : params.getAttributes()) {
        String col = statementBuilder.columnQuote(item.getItemId());
        sql += item.isNumeric() ? "CAST( " + col + ".value AS NUMERIC ) as " : col + ".value as ";
        sql += col + ", ";
    }
    sql = removeLastComma(sql) + " ";
    // ---------------------------------------------------------------------
    // From and where clause
    // ---------------------------------------------------------------------
    sql += getFromWhereClause(params, hlp);
    // ---------------------------------------------------------------------
    // Order clause
    // ---------------------------------------------------------------------
    sql += getOrderClause(params);
    if (params.isPaging()) {
        sql += " limit " + params.getPageSizeWithDefault() + " offset " + params.getOffset();
    }
    // ---------------------------------------------------------------------
    // Query
    // ---------------------------------------------------------------------
    log.info("Query: " + sql);
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    log.debug("Tracked entity instance query SQL: " + sql);
    List<Map<String, String>> list = new ArrayList<>();
    while (rowSet.next()) {
        final Map<String, String> map = new HashMap<>();
        map.put(TRACKED_ENTITY_INSTANCE_ID, rowSet.getString(TRACKED_ENTITY_INSTANCE_ID));
        map.put(CREATED_ID, rowSet.getString(CREATED_ID));
        map.put(LAST_UPDATED_ID, rowSet.getString(LAST_UPDATED_ID));
        map.put(ORG_UNIT_ID, rowSet.getString(ORG_UNIT_ID));
        map.put(ORG_UNIT_NAME, rowSet.getString(ORG_UNIT_NAME));
        map.put(TRACKED_ENTITY_ID, rowSet.getString(TRACKED_ENTITY_ID));
        map.put(INACTIVE_ID, rowSet.getString(INACTIVE_ID));
        if (params.isIncludeDeleted()) {
            map.put(DELETED, rowSet.getString(DELETED));
        }
        for (QueryItem item : params.getAttributes()) {
            map.put(item.getItemId(), rowSet.getString(item.getItemId()));
        }
        list.add(map);
    }
    return list;
}

Example 28 with QueryItem

use of org.hisp.dhis.common.QueryItem 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;
}