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

Example 16 with SqlRowSet

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

the class JdbcAnalyticsManager method getKeyValueMap.

/**
     * Retrieves data from the database based on the given query and SQL and puts
     * into a value key and value mapping.
     */
private Map<String, Object> getKeyValueMap(DataQueryParams params, String sql, int maxLimit) {
    Map<String, Object> map = new HashMap<>();
    log.debug(String.format("Analytics SQL: %s", sql));
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    int counter = 0;
    while (rowSet.next()) {
        if (maxLimit > 0 && ++counter > maxLimit) {
            throw new IllegalQueryException("Query result set exceeds max limit: " + maxLimit);
        }
        StringBuilder key = new StringBuilder();
        for (DimensionalObject dim : params.getDimensions()) {
            String value = dim.isFixed() ? dim.getDimensionName() : rowSet.getString(dim.getDimensionName());
            key.append(value).append(DIMENSION_SEP);
        }
        key.deleteCharAt(key.length() - 1);
        if (params.isDataType(TEXT)) {
            String value = rowSet.getString(VALUE_ID);
            map.put(key.toString(), value);
        } else // NUMERIC
        {
            Double value = rowSet.getDouble(VALUE_ID);
            map.put(key.toString(), value);
        }
    }
    return map;
}

Example 17 with SqlRowSet

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

the class JdbcRawAnalyticsManager method getRawDataValues.

// -------------------------------------------------------------------------
// RawAnalyticsManager implementation
// -------------------------------------------------------------------------
@Override
public Grid getRawDataValues(DataQueryParams params, Grid grid) {
    List<DimensionalObject> dimensions = params.getDimensions();
    String sql = getStatement(params);
    log.debug("Get raw data SQL: " + sql);
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    while (rowSet.next()) {
        grid.addRow();
        for (DimensionalObject dim : dimensions) {
            grid.addValue(rowSet.getString(dim.getDimensionName()));
        }
        grid.addValue(rowSet.getDouble("value"));
    }
    return grid;
}

Example 18 with SqlRowSet

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

the class JdbcEventAnalyticsManager method getEvents.

private void getEvents(Grid grid, EventQueryParams params, String sql) {
    log.debug(String.format("Analytics event query SQL: %s", sql));
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    while (rowSet.next()) {
        grid.addRow();
        int index = 1;
        for (GridHeader header : grid.getHeaders()) {
            if (ITEM_LONGITUDE.equals(header.getName()) || ITEM_LATITUDE.equals(header.getName())) {
                double val = rowSet.getDouble(index);
                grid.addValue(Precision.round(val, COORD_DEC));
            } else if (Double.class.getName().equals(header.getType()) && !header.hasLegendSet()) {
                double val = rowSet.getDouble(index);
                grid.addValue(params.isSkipRounding() ? val : MathUtils.getRounded(val));
            } else {
                grid.addValue(rowSet.getString(index));
            }
            index++;
        }
    }
}

Example 19 with SqlRowSet

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

the class JdbcEventAnalyticsManager method getAggregatedEventData.

private void getAggregatedEventData(Grid grid, EventQueryParams params, String sql) {
    log.debug(String.format("Analytics event aggregate SQL: %s", sql));
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    while (rowSet.next()) {
        grid.addRow();
        if (params.isAggregateData()) {
            if (params.hasValueDimension()) {
                String itemId = params.getProgram().getUid() + COMPOSITE_DIM_OBJECT_PLAIN_SEP + params.getValue().getUid();
                grid.addValue(itemId);
            } else if (params.hasProgramIndicatorDimension()) {
                grid.addValue(params.getProgramIndicator().getUid());
            }
        } else {
            for (QueryItem queryItem : params.getItems()) {
                String itemValue = rowSet.getString(queryItem.getItemName());
                String gridValue = params.isCollapseDataDimensions() ? getCollapsedDataItemValue(params, queryItem, itemValue) : itemValue;
                grid.addValue(gridValue);
            }
        }
        for (DimensionalObject dimension : params.getDimensions()) {
            String dimensionValue = rowSet.getString(dimension.getDimensionName());
            grid.addValue(dimensionValue);
        }
        if (params.hasValueDimension()) {
            double value = rowSet.getDouble("value");
            grid.addValue(params.isSkipRounding() ? value : getRounded(value));
        } else if (params.hasProgramIndicatorDimension()) {
            double value = rowSet.getDouble("value");
            ProgramIndicator indicator = params.getProgramIndicator();
            grid.addValue(AnalyticsUtils.getRoundedValue(params, indicator.getDecimals(), value));
        } else {
            int value = rowSet.getInt("value");
            grid.addValue(value);
        }
        if (params.isIncludeNumDen()) {
            grid.addNullValues(3);
        }
    }
}

Example 20 with SqlRowSet

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

the class JdbcDataAnalysisStore method getAverage.

@Override
public Map<Integer, Double> getAverage(DataElement dataElement, DataElementCategoryOptionCombo categoryOptionCombo, Collection<OrganisationUnit> parents, Date from) {
    Map<Integer, Double> map = new HashMap<>();
    if (parents.isEmpty()) {
        return map;
    }
    String sql = "select ou.organisationunitid, " + "(select avg( cast( dv.value as " + statementBuilder.getDoubleColumnType() + " ) ) " + "from datavalue dv " + "inner join period pe on dv.periodid = pe.periodid " + "where dv.dataelementid = " + dataElement.getId() + " " + "and dv.categoryoptioncomboid = " + categoryOptionCombo.getId() + " " + "and pe.startdate >= '" + DateUtils.getMediumDateString(from) + "' " + "and dv.sourceid = ou.organisationunitid " + "and dv.deleted is false) as average " + "from organisationunit ou where (";
    for (OrganisationUnit parent : parents) {
        sql += "ou.path like '" + parent.getPath() + "%' or ";
    }
    sql = TextUtils.removeLastOr(sql) + ")";
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    while (rowSet.next()) {
        Object avg = rowSet.getObject("average");
        if (avg != null) {
            map.put(rowSet.getInt("organisationunitid"), (Double) avg);
        }
    }
    return map;
}