Examples with DataApprovalState org.hisp.dhis.dataapproval.DataApprovalState used on opensource projects

Example 1 with DataApprovalState

use of org.hisp.dhis.dataapproval.DataApprovalState 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 2 with DataApprovalState

use of org.hisp.dhis.dataapproval.DataApprovalState in project dhis2-core by dhis2.

the class HibernateDataApprovalStore method getDataApprovalStatuses.

@Override
public List<DataApprovalStatus> getDataApprovalStatuses(DataApprovalWorkflow workflow, Period period, Collection<OrganisationUnit> orgUnits, int orgUnitLevel, CategoryCombo attributeCombo, Set<CategoryOptionCombo> attributeOptionCombos, List<DataApprovalLevel> userApprovalLevels, Map<Integer, DataApprovalLevel> levelMap) {
    // ---------------------------------------------------------------------
    // Get validation criteria
    // ---------------------------------------------------------------------
    final User user = currentUserService.getCurrentUser();
    final String strArrayUserGroups = CollectionUtils.isEmpty(user.getGroups()) ? null : "{" + String.join(",", user.getGroups().stream().map(group -> group.getUid()).collect(Collectors.toList())) + "}";
    final String co_group_sharing_check_query = strArrayUserGroups != null ? " and (not " + JsonbFunctions.HAS_USER_GROUP_IDS + "( co.sharing, '" + strArrayUserGroups + "') or not " + JsonbFunctions.CHECK_USER_GROUPS_ACCESS + "( co.sharing, '" + AclService.LIKE_READ_METADATA + "', '" + strArrayUserGroups + "') )" : "";
    List<DataApprovalLevel> approvalLevels = workflow.getSortedLevels();
    Set<OrganisationUnit> userOrgUnits = user.getDataViewOrganisationUnitsWithFallback();
    boolean isDefaultCombo = attributeOptionCombos != null && attributeOptionCombos.size() == 1 && categoryService.getDefaultCategoryOptionCombo().equals(attributeOptionCombos.toArray()[0]);
    boolean maySeeDefaultCategoryCombo = (CollectionUtils.isEmpty(user.getCogsDimensionConstraints()) && CollectionUtils.isEmpty(user.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 (orgUnits != null) {
        for (OrganisationUnit orgUnit : orgUnits) {
            if (!orgUnit.isDescendant(userOrgUnits)) {
                log.debug("User " + user.getUsername() + " can't see orgUnit " + orgUnit.getName());
                // Unapprovable.
                return new ArrayList<>();
            }
        }
    }
    // ---------------------------------------------------------------------
    // Get other information
    // ---------------------------------------------------------------------
    boolean acceptanceRequiredForApproval = systemSettingManager.getBoolSetting(SettingKey.ACCEPTANCE_REQUIRED_FOR_APPROVAL);
    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;
    if (orgUnits == null) {
        orgUnitLevel = 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() && (orgUnits == null || orgUnitLevel == highestUserApprovalLevel.getOrgUnitLevel())) {
        approvedAboveLevel = approvalLevelAboveUser;
    } else if (orgUnits != 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;
    String orgUnitIds = "";
    if (orgUnits != null) {
        orgUnitIds = StringUtils.join(IdentifiableObjectUtils.getIdentifiers(orgUnits), ",");
        highestApprovedOrgUnitCompare = "da.organisationunitid in (" + orgUnitIds + ") ";
    } 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 += ") ";
    }
    String coEndDateExtension = workflow.getSqlCoEndDateExtension();
    // Not approved above if this is
    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 = coc.categoryoptioncomboid " + ")";
    }
    // Ready below if this is the lowest
    String readyBelowSubquery = "true";
    if (approvalLevelBelowOrgUnit != null) {
        readyBelowSubquery = // Ready if nothing expected
        "not exists ( " + // unapproved(/unaccepted)
        "select 1 " + // Lower-level Data Approval
        "from organisationunit dao " + // is needed to be ready.
        "where " + statementBuilder.position("o.uid", "dao.path") + " = " + pathPositionAtLevel(orgUnitLevel) + " " + "and dao.hierarchylevel = " + approvalLevelBelowOrgUnit.getOrgUnitLevel() + " " + // Data for this workflow is collected
        "and exists ( " + // somewhere at or below DAO
        "select 1 from organisationunit child " + "where " + statementBuilder.position("dao.uid", "child.path") + " <> 0 " + "and child.organisationunitid in ( " + "select distinct sourceid " + "from datasetsource dss " + "join dataset ds on ds.datasetid = dss.datasetid " + "where ds.workflowid = " + workflow.getId() + ") " + ") " + (// Default combo options never have an
        isDefaultCombo ? // Default combo options never have an
        "" : // organisation unit mapping.
        // No AOCs without all attribute
        "and not exists (" + // options valid for org unit.
        "select 1 " + "from categoryoptioncombos_categoryoptions cc1 " + "where cc1.categoryoptioncomboid = coc.categoryoptioncomboid " + "and ( " + // If there are orgUnit mappings...
        "exists ( " + "select 1 " + "from categoryoption_organisationunits co1 " + "where co1.categoryoptionid = cc1.categoryoptionid ) " + // then one of them should map to
        "and not exists (" + // this orgUnit.
        "select 1 " + "from categoryoption_organisationunits co1 " + "join organisationunit o1 on o1.organisationunitid = co1.organisationunitid " + "where co1.categoryoptionid = cc1.categoryoptionid " + "and " + statementBuilder.position("o1.uid", "dao.path") + " between 2 and " + pathPositionAtLevel(approvalLevelBelowOrgUnit) + " " + ") " + ") " + ") ") + // Data not approved(/accepted) below where
        "and not exists (" + // it needs to be if ready.
        "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 = coc.categoryoptioncomboid " + (acceptanceRequiredForApproval ? "and da.accepted " : "") + ") " + ") ";
    }
    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 = coc.categoryoptioncomboid " + "and " + highestApprovedOrgUnitCompare + userApprovalLevelRestrictions + ") as highest_approved, " + readyBelowSubquery + " as ready_below, " + approvedAboveSubquery + " as approved_above " + "from categoryoptioncombo coc " + "join organisationunit o on " + (orgUnits != null ? "o.organisationunitid in (" + orgUnitIds + ") " : "o.hierarchylevel = " + orgUnitLevel + userOrgUnitRestrictions + " ") + // Exclude any attribute option combo (COC)
    "where not exists ( " + // unwanted attribute option (CO):
    "select 1 " + "from categoryoptioncombos_categoryoptions cocco " + "join dataelementcategoryoption co on co.categoryoptionid = cocco.categoryoptionid " + "where cocco.categoryoptioncomboid = coc.categoryoptioncomboid " + "and ( " + "(co.startdate is not null and co.startdate > '" + endDate + // CO
    "') " + // late.
    "or (co.enddate is not null and co.enddate" + coEndDateExtension + " < '" + startDate + // CO
    "') " + // early
    "or ( " + // This CO has orgunit mapping
    "exists ( " + "select 1 " + "from categoryoption_organisationunits coo " + "where coo.categoryoptionid = co.categoryoptionid " + // and not mapped to an orgunit we are
    ") and not exists (" + // looking for
    "select 1 " + "from categoryoption_organisationunits coo " + "join organisationunit o2 on o2.organisationunitid = coo.organisationunitid " + "where coo.categoryoptionid = co.categoryoptionid " + "and ( " + statementBuilder.position("o.uid", "o2.path") + " <> 0  or " + statementBuilder.position("o2.uid", "o.path") + " <> 0 " + ") " + ") " + ") " + (// Filter out COs the user doesn't have
    isSuperUser ? // Filter out COs the user doesn't have
    "" : // permission to see.
    "or ( ( co.sharing->>'public' is null or left(co.sharing->>'public', 1) != 'r' )" + " and ( co.sharing->>'owner' is null or co.sharing->>'owner' != '" + user.getUid() + "' )" + " and ( not " + JsonbFunctions.HAS_USER_ID + "( co.sharing, '" + user.getUid() + "') or not " + JsonbFunctions.CHECK_USER_ACCESS + "( co.sharing, '" + user.getUid() + "', '" + AclService.LIKE_READ_METADATA + "') )" + co_group_sharing_check_query + " )") + ") " + ") " + (attributeCombo == null ? "" : "and coc.categoryoptioncomboid in (select c9.categoryoptioncomboid from categorycombos_optioncombos c9 where c9.categorycomboid = " + attributeCombo.getId() + " ) ") + (attributeOptionCombos == null || attributeOptionCombos.isEmpty() ? "" : "and coc.categoryoptioncomboid in (" + StringUtils.join(IdentifiableObjectUtils.getIdentifiers(attributeOptionCombos), ",") + ") ") + // Filter AOCs if specified.
    "and exists ( " + // mapped to a dataset of the workflow.
    "select 1 from organisationunit o3 " + "where o3.path like o.path || '%' and o3.organisationunitid in ( " + "select distinct sourceid " + "from datasetsource dss " + "join dataset ds on ds.datasetid = dss.datasetid " + "where ds.workflowid = " + workflow.getId() + ") " + ")";
    log.debug("User " + user.getUsername() + " superuser " + isSuperUser + " workflow " + workflow.getName() + " period " + period.getIsoDate() + " orgUnits " + (orgUnits == null ? "null" : orgUnits) + " attributeCombo " + (attributeCombo == null ? "null" : attributeCombo.getName()));
    log.debug("Get approval SQL: " + sql);
    // ---------------------------------------------------------------------
    // Fetch query results and process them
    // ---------------------------------------------------------------------
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    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 && acceptanceRequiredForApproval && 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(DataApprovalStatus.builder().state(state).approvedLevel(approvedLevel).approvedOrgUnitId(approvedOrgUnitId).actionLevel(actionLevel).organisationUnitUid(ouUid).organisationUnitName(ouName).attributeOptionComboUid(aocUid).accepted(accepted).build());
        }
    }
    return statusList;
}