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

Example 6 with SqlRowSet

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

the class HibernateDataApprovalStore method getDataApprovals.

@Override
public List<DataApprovalStatus> getDataApprovals(DataApprovalWorkflow workflow, Period period, OrganisationUnit orgUnit, DataElementCategoryCombo attributeCombo, Set<DataElementCategoryOptionCombo> attributeOptionCombos) {
    // ---------------------------------------------------------------------
    // Get validation criteria
    // ---------------------------------------------------------------------
    final User user = currentUserService.getCurrentUser();
    List<DataApprovalLevel> approvalLevels = workflow.getSortedLevels();
    List<DataApprovalLevel> userApprovalLevels = dataApprovalLevelService.getUserDataApprovalLevels(workflow);
    Set<OrganisationUnit> userOrgUnits = user.getDataViewOrganisationUnitsWithFallback();
    boolean isDefaultCombo = attributeOptionCombos != null && attributeOptionCombos.size() == 1 && categoryService.getDefaultDataElementCategoryOptionCombo().equals(attributeOptionCombos.toArray()[0]);
    boolean maySeeDefaultCategoryCombo = (CollectionUtils.isEmpty(user.getUserCredentials().getCogsDimensionConstraints()) && CollectionUtils.isEmpty(user.getUserCredentials().getCatDimensionConstraints()));
    if (isDefaultCombo && !maySeeDefaultCategoryCombo) {
        log.warn("DefaultCategoryCombo selected but user " + user.getUsername() + " lacks permission to see it.");
        // Unapprovable.
        return new ArrayList<>();
    }
    if (CollectionUtils.isEmpty(approvalLevels)) {
        log.warn("No approval levels configured for workflow " + workflow.getName());
        // Unapprovable.
        return new ArrayList<>();
    }
    if (CollectionUtils.isEmpty(userApprovalLevels)) {
        log.warn("No user approval levels for user " + user.getUsername() + ", workflow " + workflow.getName());
        // Unapprovable.
        return new ArrayList<>();
    }
    if (orgUnit != null && !orgUnit.isDescendant(userOrgUnits)) {
        log.debug("User " + user.getUsername() + " can't see orgUnit " + orgUnit.getName());
        // Unapprovable.
        return new ArrayList<>();
    }
    // ---------------------------------------------------------------------
    // Get other information
    // ---------------------------------------------------------------------
    final boolean isSuperUser = currentUserService.currentUserIsSuper();
    final String startDate = DateUtils.getMediumDateString(period.getStartDate());
    final String endDate = DateUtils.getMediumDateString(period.getEndDate());
    DataApprovalLevel highestApprovalLevel = approvalLevels.get(0);
    DataApprovalLevel highestUserApprovalLevel = userApprovalLevels.get(0);
    DataApprovalLevel lowestApprovalLevelForOrgUnit = null;
    DataApprovalLevel approvalLevelAboveOrgUnit = null;
    DataApprovalLevel approvalLevelBelowOrgUnit = null;
    DataApprovalLevel approvalLevelAboveUser = null;
    int orgUnitLevel = (orgUnit != null) ? orgUnit.getLevel() : approvalLevels.get(approvalLevels.size() - 1).getOrgUnitLevel();
    for (DataApprovalLevel dal : approvalLevels) {
        int dalOrgUnitLevel = dal.getOrgUnitLevel();
        if (dal.getLevel() < highestUserApprovalLevel.getLevel()) {
            approvalLevelAboveUser = dal;
        }
        if (dalOrgUnitLevel < orgUnitLevel) {
            approvalLevelAboveOrgUnit = dal;
        } else if (dal.getOrgUnitLevel() == orgUnitLevel) {
            lowestApprovalLevelForOrgUnit = dal;
        } else // dal.getOrgUnitLevel() > orgUnitLevel
        {
            approvalLevelBelowOrgUnit = dal;
            break;
        }
    }
    DataApprovalLevel approvedAboveLevel = null;
    if (highestUserApprovalLevel.getLevel() != highestApprovalLevel.getLevel() && (orgUnit == null || orgUnitLevel == highestUserApprovalLevel.getOrgUnitLevel())) {
        approvedAboveLevel = approvalLevelAboveUser;
    } else if (orgUnit != null && orgUnitLevel != highestUserApprovalLevel.getOrgUnitLevel()) {
        approvedAboveLevel = approvalLevelAboveOrgUnit;
    }
    log.debug("Workflow '" + workflow.getName() + "' levels: " + approvalLevels.size() + ", user levels: " + userApprovalLevels.size() + ", lowestApprovalLevelForOrgUnit: " + (lowestApprovalLevelForOrgUnit == null ? "-" : lowestApprovalLevelForOrgUnit.getLevel()) + ", approvalLevelAboveOrgUnit: " + (approvalLevelAboveOrgUnit == null ? "-" : approvalLevelAboveOrgUnit.getLevel()) + ", approvalLevelBelowOrgUnit: " + (approvalLevelBelowOrgUnit == null ? "-" : approvalLevelBelowOrgUnit.getLevel()) + ", approvalLevelAboveUser: " + (approvalLevelAboveUser == null ? "-" : approvalLevelAboveUser.getLevel()) + ", approvedAboveLevel: " + (approvedAboveLevel == null ? "-" : approvedAboveLevel.getLevel()));
    // ---------------------------------------------------------------------
    // Construct query
    // ---------------------------------------------------------------------
    String userOrgUnitRestrictions = "";
    if (!isSuperUser && !userOrgUnits.isEmpty()) {
        for (OrganisationUnit ou : userOrgUnits) {
            userOrgUnitRestrictions += (userOrgUnitRestrictions.length() == 0 ? " and ( " : " or ") + statementBuilder.position("'" + ou.getUid() + "'", "o.path") + " <> 0";
        }
        userOrgUnitRestrictions += " )";
    }
    String highestApprovedOrgUnitJoin = "";
    String highestApprovedOrgUnitCompare;
    if (orgUnit != null) {
        highestApprovedOrgUnitCompare = "da.organisationunitid = " + orgUnit.getId() + " ";
    } else {
        highestApprovedOrgUnitJoin = "join organisationunit dao on dao.organisationunitid = da.organisationunitid ";
        highestApprovedOrgUnitCompare = statementBuilder.position("dao.uid", "o.path") + " <> 0";
    }
    String userApprovalLevelRestrictions = "";
    if (!isSuperUser && userApprovalLevels.size() != approvalLevels.size()) {
        for (DataApprovalLevel dal : userApprovalLevels) {
            userApprovalLevelRestrictions += (userApprovalLevelRestrictions.length() == 0 ? " and dal.dataapprovallevelid in ( " : ", ") + dal.getId();
        }
        userApprovalLevelRestrictions += " ) ";
    }
    // Not approved above if this is the highest (lowest number) approval orgUnit level.
    String approvedAboveSubquery = "false";
    if (approvedAboveLevel != null) {
        approvedAboveSubquery = "exists(select 1 from dataapproval da " + "join period p on p.periodid = da.periodid " + "join organisationunit dao on dao.organisationunitid = da.organisationunitid " + "where " + statementBuilder.position("dao.uid", "o.path") + " = " + pathPositionAtLevel(approvedAboveLevel) + " " + "and '" + endDate + "' >= p.startdate and '" + endDate + "' <= p.enddate " + "and da.dataapprovallevelid = " + approvedAboveLevel.getId() + " " + "and da.workflowid = " + workflow.getId() + " and da.attributeoptioncomboid = cocco.categoryoptioncomboid)";
    }
    // Ready below if this is the lowest (highest number) approval orgUnit level.
    String readyBelowSubquery = "true";
    if (approvalLevelBelowOrgUnit != null) {
        boolean acceptanceRequiredForApproval = (Boolean) systemSettingManager.getSystemSetting(SettingKey.ACCEPTANCE_REQUIRED_FOR_APPROVAL);
        readyBelowSubquery = "not exists (select 1 from organisationunit dao " + "where not exists (select 1 from dataapproval da " + "join period p on p.periodid = da.periodid " + "where da.organisationunitid = dao.organisationunitid " + "and da.dataapprovallevelid = " + approvalLevelBelowOrgUnit.getId() + " " + "and '" + endDate + "' >= p.startdate and '" + endDate + "' <= p.enddate " + "and da.workflowid = " + workflow.getId() + " " + "and da.attributeoptioncomboid = cocco.categoryoptioncomboid " + (acceptanceRequiredForApproval ? "and da.accepted " : "") + ") " + "and " + statementBuilder.position("o.uid", "dao.path") + " = " + pathPositionAtLevel(orgUnitLevel) + " " + "and dao.hierarchylevel = " + approvalLevelBelowOrgUnit.getOrgUnitLevel() + " " + (isDefaultCombo ? "" : "and ( not exists ( select 1 from categoryoption_organisationunits c_o where c_o.categoryoptionid = cocco.categoryoptionid ) " + "or exists ( select 1 from categoryoption_organisationunits c_o " + "join organisationunit o2 on o2.organisationunitid = c_o.organisationunitid " + "where c_o.categoryoptionid = cocco.categoryoptionid and " + statementBuilder.position("o2.uid", "dao.path") + " between 2 and " + pathPositionAtLevel(approvalLevelBelowOrgUnit) + ") ) ") + ")";
    }
    final String sql = "select coc.uid as cocuid, o.uid as ouuid, o.name as ouname, " + "(select min(" + statementBuilder.concatenate(MAX_APPROVAL_LEVEL + " + dal.level", SQL_CAT, "da.accepted", SQL_CAT, "da.organisationunitid") + ") " + "from dataapproval da " + "join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " + highestApprovedOrgUnitJoin + "where da.workflowid = " + workflow.getId() + " " + "and da.periodid = " + getWorkflowPeriodId(workflow, endDate) + " " + "and da.attributeoptioncomboid = cocco.categoryoptioncomboid " + "and " + highestApprovedOrgUnitCompare + userApprovalLevelRestrictions + ") as highest_approved, " + readyBelowSubquery + " as ready_below, " + approvedAboveSubquery + " as approved_above " + "from categoryoptioncombo coc " + "join categoryoptioncombos_categoryoptions cocco on cocco.categoryoptioncomboid = coc.categoryoptioncomboid " + (attributeCombo == null ? "" : "join categorycombos_optioncombos ccoc on ccoc.categoryoptioncomboid = cocco.categoryoptioncomboid " + "and ccoc.categorycomboid = " + attributeCombo.getId() + " ") + "join dataelementcategoryoption co on co.categoryoptionid = cocco.categoryoptionid " + "and (co.startdate is null or co.startdate <= '" + endDate + "') and (co.enddate is null or co.enddate >= '" + startDate + "') " + "join organisationunit o on " + (orgUnit != null ? "o.organisationunitid = " + orgUnit.getId() : "o.hierarchylevel = " + orgUnitLevel + userOrgUnitRestrictions) + " " + "left join categoryoption_organisationunits coo on coo.categoryoptionid = co.categoryoptionid " + "left join organisationunit oc on oc.organisationunitid = coo.organisationunitid " + "where ( coo.categoryoptionid is null or " + statementBuilder.position("o.uid", "oc.path") + " <> 0 )" + (attributeOptionCombos == null || attributeOptionCombos.isEmpty() ? "" : " and cocco.categoryoptioncomboid in (" + StringUtils.join(IdentifiableObjectUtils.getIdentifiers(attributeOptionCombos), ",") + ") ") + (isSuperUser ? "" : " and ( co.publicaccess is null or left(co.publicaccess, 1) = 'r' or co.userid is null or co.userid = " + user.getId() + " or exists ( " + "select 1 from dataelementcategoryoptionusergroupaccesses couga " + "left join usergroupaccess uga on uga.usergroupaccessid = couga.usergroupaccessid " + "left join usergroupmembers ugm on ugm.usergroupid = uga.usergroupid " + "where couga.categoryoptionid = cocco.categoryoptionid and ugm.userid = " + user.getId() + ") )");
    log.debug("User " + user.getUsername() + " superuser " + isSuperUser + " workflow " + workflow.getName() + " period " + period.getIsoDate() + " orgUnit " + (orgUnit == null ? "null" : orgUnit.getName()) + " attributeCombo " + (attributeCombo == null ? "null" : attributeCombo.getName()));
    log.debug("Get approval SQL: " + sql);
    // ---------------------------------------------------------------------
    // Fetch query results and process them
    // ---------------------------------------------------------------------
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    Map<Integer, DataApprovalLevel> levelMap = dataApprovalLevelService.getDataApprovalLevelMap();
    List<DataApprovalStatus> statusList = new ArrayList<>();
    while (rowSet.next()) {
        final String aocUid = rowSet.getString(1);
        final String ouUid = rowSet.getString(2);
        final String ouName = rowSet.getString(3);
        final String highestApproved = rowSet.getString(4);
        final boolean readyBelow = rowSet.getBoolean(5);
        boolean approvedAbove = rowSet.getBoolean(6);
        final String[] approved = highestApproved == null ? null : highestApproved.split(SQL_CONCAT);
        final int level = approved == null ? 0 : Integer.parseInt(approved[0]) - MAX_APPROVAL_LEVEL;
        final boolean accepted = approved == null ? false : approved[1].substring(0, 1).equalsIgnoreCase("t");
        final int approvedOrgUnitId = approved == null ? 0 : Integer.parseInt(approved[2]);
        // null if not approved
        DataApprovalLevel approvedLevel = (level == 0 ? null : levelMap.get(level));
        DataApprovalLevel actionLevel = (approvedLevel == null ? lowestApprovalLevelForOrgUnit : approvedLevel);
        if (approvedAbove && accepted && approvedAboveLevel == approvalLevelAboveUser) {
            // Hide higher-level approval from user.
            approvedAbove = false;
        }
        if (ouUid != null) {
            DataApprovalState state = (approvedAbove ? APPROVED_ABOVE : approvedLevel == null ? lowestApprovalLevelForOrgUnit == null ? approvalLevelAboveOrgUnit == null ? UNAPPROVABLE : UNAPPROVED_ABOVE : readyBelow ? UNAPPROVED_READY : UNAPPROVED_WAITING : accepted ? ACCEPTED_HERE : APPROVED_HERE);
            statusList.add(new DataApprovalStatus(state, approvedLevel, approvedOrgUnitId, actionLevel, ouUid, ouName, aocUid, accepted, null));
            log.debug("Get approval result: level " + level + " dataApprovalLevel " + (actionLevel != null ? actionLevel.getLevel() : "[none]") + " approved " + (approvedLevel != null) + " readyBelow " + readyBelow + " approvedAbove " + approvedAbove + " accepted " + accepted + " state " + (state != null ? state.name() : "[none]") + " orgUnitUid " + ouUid + " aocUid " + aocUid);
        }
    }
    return statusList;
}

Example 7 with SqlRowSet

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

the class JdbcDataAnalysisStore method getStandardDeviation.

// -------------------------------------------------------------------------
// OutlierAnalysisStore implementation
// -------------------------------------------------------------------------
@Override
public Map<Integer, Double> getStandardDeviation(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 stddev_pop( 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 deviation " + "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 stdDev = rowSet.getObject("deviation");
        if (stdDev != null && !isEqual((Double) stdDev, ZERO)) {
            map.put(rowSet.getInt("organisationunitid"), (Double) stdDev);
        }
    }
    return map;
}

Example 8 with SqlRowSet

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

the class IdentityPopulator method updatePasswordLastUpdated.

private void updatePasswordLastUpdated() {
    try {
        String timestamp = DateUtils.getLongDateString();
        SqlRowSet resultSet = jdbcTemplate.queryForRowSet("SELECT * from users WHERE passwordlastupdated IS NULL");
        while (resultSet.next()) {
            String sql = "UPDATE users SET passwordlastupdated = '" + timestamp + "' WHERE passwordlastupdated IS NULL";
            jdbcTemplate.update(sql);
        }
    } catch (// Log and continue
    Exception ex) {
        log.error("Problem updating passwordLastUpdated on table user: " + ex.getMessage());
    }
}

Example 9 with SqlRowSet

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

the class FavoriteDataItemUpgrader method upgradeFavorites.

private void upgradeFavorites(Class<? extends BaseAnalyticalObject> favoriteClass, String favoriteTablename, Class<? extends IdentifiableObject> objectClass, String objectTablename) {
    String linkTablename = favoriteTablename + "_" + objectTablename + "s";
    String selectSql = "select " + favoriteTablename + "id, " + objectTablename + "id from " + linkTablename + " " + "order by " + favoriteTablename + "id, sort_order";
    SqlRowSet rs = jdbcTemplate.queryForRowSet(selectSql);
    while (rs.next()) {
        int rtId = rs.getInt(1);
        int obId = rs.getInt(2);
        BaseAnalyticalObject favorite = idObjectManager.get(favoriteClass, rtId);
        DimensionalItemObject object = (DimensionalItemObject) idObjectManager.get(objectClass, obId);
        DataDimensionItem item = DataDimensionItem.create(object);
        favorite.getDataDimensionItems().add(item);
        idObjectManager.update(favorite);
        log.debug("Upgraded " + favoriteTablename + " " + favorite.getUid() + " for " + objectTablename + " " + object.getUid());
    }
    String dropSql = "drop table " + linkTablename;
    jdbcTemplate.update(dropSql);
    log.info("Update done, dropped table " + linkTablename);
}

Example 10 with SqlRowSet

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

the class HibernateDbmsManager method getTableContent.

@Override
public List<List<Object>> getTableContent(String table) {
    List<List<Object>> tableContent = new ArrayList<>();
    SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet("select * from " + table);
    int cols = sqlRowSet.getMetaData().getColumnCount() + 1;
    List<Object> headers = new ArrayList<>();
    for (int i = 1; i < cols; i++) {
        headers.add(sqlRowSet.getMetaData().getColumnName(i));
    }
    tableContent.add(headers);
    while (sqlRowSet.next()) {
        List<Object> row = new ArrayList<>();
        for (int i = 1; i < cols; i++) {
            row.add(sqlRowSet.getObject(i));
        }
        tableContent.add(row);
    }
    return tableContent;
}