Examples with SqlRowSet org.springframework.jdbc.support.rowset.SqlRowSet used on opensource projects

Example 1 with SqlRowSet

use of org.springframework.jdbc.support.rowset.SqlRowSet in project perun by CESNET.

the class PerunNotifPoolMessageDaoImpl method removeOldPoolMessages.

private void removeOldPoolMessages(long olderThan) {
    Set<Integer> proccessedIds = new HashSet<Integer>();
    long actualTimeInMillis = new DateTime().getMillis();
    SqlRowSet srs = this.getJdbcTemplate().queryForRowSet("SELECT id,created FROM pn_pool_message");
    while (srs.next()) {
        Timestamp timeStamp = srs.getTimestamp("created");
        if (timeStamp.getTime() + olderThan < actualTimeInMillis) {
            proccessedIds.add(srs.getInt("id"));
        }
    }
    removeAllPoolMessages(proccessedIds);
}

Example 2 with SqlRowSet

use of org.springframework.jdbc.support.rowset.SqlRowSet in project dhis2-core by dhis2.

the class JdbcEventStore method getEventRows.

@Override
public List<EventRow> getEventRows(EventSearchParams params, List<OrganisationUnit> organisationUnits) {
    List<EventRow> eventRows = new ArrayList<>();
    String sql = buildSql(params, organisationUnits);
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    log.debug("Event query SQL: " + sql);
    EventRow eventRow = new EventRow();
    eventRow.setEvent("not_valid");
    Set<String> notes = new HashSet<>();
    IdSchemes idSchemes = ObjectUtils.firstNonNull(params.getIdSchemes(), new IdSchemes());
    while (rowSet.next()) {
        if (rowSet.getString("psi_uid") == null) {
            continue;
        }
        if (eventRow.getUid() == null || !eventRow.getUid().equals(rowSet.getString("psi_uid"))) {
            eventRow = new EventRow();
            eventRow.setUid(rowSet.getString("psi_uid"));
            eventRow.setEvent(IdSchemes.getValue(rowSet.getString("psi_uid"), rowSet.getString("psi_code"), idSchemes.getProgramStageInstanceIdScheme()));
            eventRow.setTrackedEntityInstance(rowSet.getString("tei_uid"));
            eventRow.setTrackedEntityInstanceOrgUnit(rowSet.getString("tei_ou"));
            eventRow.setTrackedEntityInstanceOrgUnitName(rowSet.getString("tei_ou_name"));
            eventRow.setTrackedEntityInstanceCreated(rowSet.getString("tei_created"));
            eventRow.setTrackedEntityInstanceInactive(rowSet.getBoolean("tei_inactive"));
            eventRow.setDeleted(rowSet.getBoolean("psi_deleted"));
            eventRow.setProgram(IdSchemes.getValue(rowSet.getString("p_uid"), rowSet.getString("p_code"), idSchemes.getProgramIdScheme()));
            eventRow.setProgramStage(IdSchemes.getValue(rowSet.getString("ps_uid"), rowSet.getString("ps_code"), idSchemes.getProgramStageIdScheme()));
            eventRow.setOrgUnit(IdSchemes.getValue(rowSet.getString("ou_uid"), rowSet.getString("ou_code"), idSchemes.getOrgUnitIdScheme()));
            ProgramType programType = ProgramType.fromValue(rowSet.getString("p_type"));
            if (programType == ProgramType.WITHOUT_REGISTRATION) {
                eventRow.setEnrollment(rowSet.getString("pi_uid"));
                eventRow.setFollowup(rowSet.getBoolean("pi_followup"));
            }
            eventRow.setTrackedEntityInstance(rowSet.getString("tei_uid"));
            eventRow.setOrgUnitName(rowSet.getString("ou_name"));
            eventRow.setDueDate(DateUtils.getIso8601NoTz(rowSet.getDate("psi_duedate")));
            eventRow.setEventDate(DateUtils.getIso8601NoTz(rowSet.getDate("psi_executiondate")));
            eventRows.add(eventRow);
        }
        if (rowSet.getString("pav_value") != null && rowSet.getString("ta_uid") != null) {
            String valueType = rowSet.getString("ta_valuetype");
            Attribute attribute = new Attribute();
            attribute.setCreated(DateUtils.getIso8601NoTz(rowSet.getDate("pav_created")));
            attribute.setLastUpdated(DateUtils.getIso8601NoTz(rowSet.getDate("pav_lastupdated")));
            attribute.setValue(rowSet.getString("pav_value"));
            attribute.setDisplayName(rowSet.getString("ta_name"));
            attribute.setValueType(valueType != null ? ValueType.valueOf(valueType.toUpperCase()) : null);
            attribute.setAttribute(rowSet.getString("ta_uid"));
            eventRow.getAttributes().add(attribute);
        }
        if (rowSet.getString("pdv_value") != null && rowSet.getString("de_uid") != null) {
            DataValue dataValue = new DataValue();
            dataValue.setCreated(DateUtils.getIso8601NoTz(rowSet.getDate("pdv_created")));
            dataValue.setLastUpdated(DateUtils.getIso8601NoTz(rowSet.getDate("pdv_lastupdated")));
            dataValue.setValue(rowSet.getString("pdv_value"));
            dataValue.setProvidedElsewhere(rowSet.getBoolean("pdv_providedelsewhere"));
            dataValue.setDataElement(IdSchemes.getValue(rowSet.getString("de_uid"), rowSet.getString("de_code"), idSchemes.getDataElementIdScheme()));
            dataValue.setStoredBy(rowSet.getString("pdv_storedby"));
            eventRow.getDataValues().add(dataValue);
        }
        if (rowSet.getString("psinote_value") != null && !notes.contains(rowSet.getString("psinote_id"))) {
            Note note = new Note();
            note.setValue(rowSet.getString("psinote_value"));
            note.setStoredDate(rowSet.getString("psinote_storeddate"));
            note.setStoredBy(rowSet.getString("psinote_storedby"));
            eventRow.getNotes().add(note);
            notes.add(rowSet.getString("psinote_id"));
        }
    }
    return eventRows;
}

Example 3 with SqlRowSet

use of org.springframework.jdbc.support.rowset.SqlRowSet in project dhis2-core by dhis2.

the class JdbcEventStore method getEventsGrid.

@Override
public List<Map<String, String>> getEventsGrid(EventSearchParams params, List<OrganisationUnit> organisationUnits) {
    SqlHelper hlp = new SqlHelper();
    // ---------------------------------------------------------------------
    // Select clause
    // ---------------------------------------------------------------------
    String sql = "select psi.uid as " + EVENT_ID + ", " + "psi.created as " + EVENT_CREATED_ID + ", " + "psi.lastupdated as " + EVENT_LAST_UPDATED_ID + ", " + "psi.storedby as " + EVENT_STORED_BY_ID + ", " + "psi.completedby as " + EVENT_COMPLETED_BY_ID + ", " + "psi.completeddate as " + EVENT_COMPLETED_DATE_ID + ", " + "psi.duedate as " + EVENT_DUE_DATE_ID + ", " + "psi.executiondate as " + EVENT_EXECUTION_DATE_ID + ", " + "ou.uid as " + EVENT_ORG_UNIT_ID + ", " + "ou.name as " + EVENT_ORG_UNIT_NAME + ", " + "psi.status as " + EVENT_STATUS_ID + ", " + "psi.longitude as " + EVENT_LONGITUDE_ID + ", " + "psi.latitude as " + EVENT_LATITUDE_ID + ", " + "ps.uid as " + EVENT_PROGRAM_STAGE_ID + ", " + "p.uid as " + EVENT_PROGRAM_ID + ", " + "coc.uid as " + EVENT_ATTRIBUTE_OPTION_COMBO_ID + ", " + "psi.deleted as " + EVENT_DELETED + ", ";
    for (QueryItem item : params.getDataElementsAndFilters()) {
        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, organisationUnits);
    // ---------------------------------------------------------------------
    // Order clause
    // ---------------------------------------------------------------------
    sql += getGridOrderQuery(params);
    // ---------------------------------------------------------------------
    // Paging clause
    // ---------------------------------------------------------------------
    sql += getEventPagingQuery(params);
    // ---------------------------------------------------------------------
    // Query
    // ---------------------------------------------------------------------
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    log.debug("Event query SQL: " + sql);
    List<Map<String, String>> list = new ArrayList<>();
    while (rowSet.next()) {
        final Map<String, String> map = new HashMap<>();
        for (String col : STATIC_EVENT_COLUMNS) {
            map.put(col, rowSet.getString(col));
        }
        for (QueryItem item : params.getDataElements()) {
            map.put(item.getItemId(), rowSet.getString(item.getItemId()));
        }
        list.add(map);
    }
    return list;
}

Example 4 with SqlRowSet

use of org.springframework.jdbc.support.rowset.SqlRowSet in project dhis2-core by dhis2.

the class JdbcEventStore method getEvents.

// -------------------------------------------------------------------------
// EventStore implementation
// -------------------------------------------------------------------------
@Override
public List<Event> getEvents(EventSearchParams params, List<OrganisationUnit> organisationUnits) {
    List<Event> events = new ArrayList<>();
    String sql = buildSql(params, organisationUnits);
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    log.debug("Event query SQL: " + sql);
    Event event = new Event();
    event.setEvent("not_valid");
    Set<String> notes = new HashSet<>();
    IdSchemes idSchemes = ObjectUtils.firstNonNull(params.getIdSchemes(), new IdSchemes());
    while (rowSet.next()) {
        if (rowSet.getString("psi_uid") == null) {
            continue;
        }
        if (event.getUid() == null || !event.getUid().equals(rowSet.getString("psi_uid"))) {
            event = new Event();
            event.setUid(rowSet.getString("psi_uid"));
            event.setEvent(IdSchemes.getValue(rowSet.getString("psi_uid"), rowSet.getString("psi_code"), idSchemes.getProgramStageInstanceIdScheme()));
            event.setTrackedEntityInstance(rowSet.getString("tei_uid"));
            event.setStatus(EventStatus.valueOf(rowSet.getString("psi_status")));
            event.setProgram(IdSchemes.getValue(rowSet.getString("p_uid"), rowSet.getString("p_code"), idSchemes.getProgramIdScheme()));
            event.setProgramStage(IdSchemes.getValue(rowSet.getString("ps_uid"), rowSet.getString("ps_code"), idSchemes.getProgramStageIdScheme()));
            event.setOrgUnit(IdSchemes.getValue(rowSet.getString("ou_uid"), rowSet.getString("ou_code"), idSchemes.getOrgUnitIdScheme()));
            event.setDeleted(rowSet.getBoolean("psi_deleted"));
            ProgramType programType = ProgramType.fromValue(rowSet.getString("p_type"));
            if (programType != ProgramType.WITHOUT_REGISTRATION) {
                event.setEnrollment(rowSet.getString("pi_uid"));
                event.setEnrollmentStatus(EnrollmentStatus.fromProgramStatus(ProgramStatus.valueOf(rowSet.getString("pi_status"))));
                event.setFollowup(rowSet.getBoolean("pi_followup"));
            }
            event.setAttributeOptionCombo(rowSet.getString("coc_categoryoptioncombouid"));
            event.setAttributeCategoryOptions(rowSet.getString("deco_uid"));
            event.setTrackedEntityInstance(rowSet.getString("tei_uid"));
            event.setStoredBy(rowSet.getString("psi_storedby"));
            event.setOrgUnitName(rowSet.getString("ou_name"));
            event.setDueDate(DateUtils.getIso8601NoTz(rowSet.getDate("psi_duedate")));
            event.setEventDate(DateUtils.getIso8601NoTz(rowSet.getDate("psi_executiondate")));
            event.setCreated(DateUtils.getIso8601NoTz(rowSet.getDate("psi_created")));
            event.setLastUpdated(DateUtils.getIso8601NoTz(rowSet.getDate("psi_lastupdated")));
            event.setCompletedBy(rowSet.getString("psi_completedby"));
            event.setCompletedDate(DateUtils.getIso8601NoTz(rowSet.getDate("psi_completeddate")));
            Double longitude = rowSet.getDouble("psi_longitude");
            Double latitude = rowSet.getDouble("psi_latitude");
            if (longitude != null && latitude != null) {
                Coordinate coordinate = new Coordinate(longitude, latitude);
                try {
                    List<Double> list = OBJECT_MAPPER.readValue(coordinate.getCoordinateString(), new TypeReference<List<Double>>() {
                    });
                    coordinate.setLongitude(list.get(0));
                    coordinate.setLatitude(list.get(1));
                } catch (IOException ignored) {
                }
                if (coordinate.isValid()) {
                    event.setCoordinate(coordinate);
                }
            }
            events.add(event);
        } else {
            String attributeCategoryCombination = event.getAttributeCategoryOptions();
            String currentAttributeCategoryCombination = rowSet.getString("deco_uid");
            if (!attributeCategoryCombination.contains(currentAttributeCategoryCombination)) {
                event.setAttributeCategoryOptions(attributeCategoryCombination + ";" + currentAttributeCategoryCombination);
            }
        }
        if (rowSet.getString("pdv_value") != null && rowSet.getString("de_uid") != null && isNewDataValue(rowSet, event.getDataValues())) {
            DataValue dataValue = new DataValue();
            dataValue.setCreated(DateUtils.getIso8601NoTz(rowSet.getDate("pdv_created")));
            dataValue.setLastUpdated(DateUtils.getIso8601NoTz(rowSet.getDate("pdv_lastupdated")));
            dataValue.setValue(rowSet.getString("pdv_value"));
            dataValue.setProvidedElsewhere(rowSet.getBoolean("pdv_providedelsewhere"));
            dataValue.setDataElement(IdSchemes.getValue(rowSet.getString("de_uid"), rowSet.getString("de_code"), idSchemes.getDataElementIdScheme()));
            dataValue.setStoredBy(rowSet.getString("pdv_storedby"));
            event.getDataValues().add(dataValue);
        }
        if (rowSet.getString("psinote_value") != null && !notes.contains(rowSet.getString("psinote_id"))) {
            Note note = new Note();
            note.setValue(rowSet.getString("psinote_value"));
            note.setStoredDate(rowSet.getString("psinote_storeddate"));
            note.setStoredBy(rowSet.getString("psinote_storedby"));
            event.getNotes().add(note);
            notes.add(rowSet.getString("psinote_id"));
        }
    }
    return events;
}

Example 5 with SqlRowSet

use of org.springframework.jdbc.support.rowset.SqlRowSet in project dhis2-core by dhis2.

the class HibernateDataApprovalStore method getWorkflowPeriodId.

/**
     * Get the id for the workflow period that spans the given end date.
     * The workflow period may or may not be the same as the period for which
     * we are checking data validity. The workflow period will have a period
     * type that matches the workflow period type, and it will contain the
     * end date of the period for which we are checking data validity.
     *
     * Returns zero if there is no such workflow period.
     *
     * It turns out that this is much faster done as a separate query in
     * postgresql than imbedding this as a subquery in the larger query above.
     *
     * @param workflow workflow we are checking
     * @param endDate end date of the period we are checking approval for,
     *                formatted as a string for a SQL query.
     * @return id of the workflow period which overlaps with the endDate
     */
private int getWorkflowPeriodId(DataApprovalWorkflow workflow, String endDate) {
    final String sql = "select periodid from period where '" + endDate + "' >= startdate and '" + endDate + "' <= enddate and periodtypeid = " + workflow.getPeriodType().getId();
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    if (rowSet.next()) {
        return rowSet.getInt(1);
    }
    return 0;
}