Examples with SqlResultSet com.bedatadriven.rebar.sql.client.SqlResultSet used on opensource projects

Example 16 with SqlResultSet

use of com.bedatadriven.rebar.sql.client.SqlResultSet in project activityinfo by bedatadriven.

the class CalculatedIndicatorsQuery method queryAttributeGroups.

private void queryAttributeGroups() {
    Set<Integer> groupIds = Sets.newHashSet();
    for (Dimension dim : query.getDimensions()) {
        if (dim instanceof AttributeGroupDimension) {
            AttributeGroupDimension groupDim = (AttributeGroupDimension) dim;
            groupIds.add(groupDim.getAttributeGroupId());
        }
    }
    SqlQuery.select().appendColumn("g.attributeGroupId", "groupId").appendColumn("g.name", "groupName").appendColumn("a.attributeId").appendColumn("a.name").appendColumn("a.sortOrder").from(Tables.ATTRIBUTE, "a").leftJoin(Tables.ATTRIBUTE_GROUP, "g").on("a.attributeGroupId=g.attributeGroupId").where("a.attributeGroupId").in(groupIds).execute(queryContext.getExecutionContext().getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            for (SqlResultSetRow row : results.getRows()) {
                int groupId = row.getInt("groupId");
                int attributeId = row.getInt("attributeId");
                int sortOrder = row.getInt("sortOrder");
                String attributeName = row.getString("name");
                attributes.put(groupId, new EntityCategory(attributeId, attributeName, sortOrder));
            }
            querySites();
        }
    });
}

Example 17 with SqlResultSet

use of com.bedatadriven.rebar.sql.client.SqlResultSet in project activityinfo by bedatadriven.

the class CalculatedIndicatorsQuery method execute.

@Override
public void execute(final AsyncCallback<Void> callback) {
    this.callback = callback;
    final SqlQuery query = SqlQuery.selectDistinct().appendColumn("i.indicatorId", "indicatorId").appendColumn("i.name", "indicatorName").appendColumn("i.activityId", "activityId").appendColumn("i.sortOrder", "indicatorOrder").appendColumn("i.aggregation", "aggregation").appendColumn("a.name", "activityName").appendColumn("a.category", "activityCategory").appendColumn("a.sortOrder", "activityOrder").appendColumn("db.DatabaseId", "databaseId").appendColumn("db.name", "databaseName").from(Tables.INDICATOR, "i").leftJoin(Tables.ACTIVITY, "a").on("a.activityId=i.activityId").leftJoin(Tables.USER_DATABASE, "db").on("a.databaseId=db.databaseId").whereTrue("i.calculatedAutomatically=1 and i.Expression is not null");
    Filter filter = this.query.getFilter();
    if (filter.isRestricted(DimensionType.Indicator)) {
        query.where("i.indicatorId").in(filter.getRestrictions(DimensionType.Indicator));
    } else if (filter.isRestricted(DimensionType.Activity)) {
        query.where("i.activityId").in(filter.getRestrictions(DimensionType.Activity));
    } else if (filter.isRestricted(DimensionType.Database)) {
        query.where("a.databaseId").in(filter.getRestrictions(DimensionType.Database));
    } else {
        // too broad
        callback.onSuccess(null);
        return;
    }
    // enforce visibility rules
    query.whereTrue(visibilityRules());
    query.execute(queryContext.getExecutionContext().getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            if (results.getRows().isEmpty()) {
                callback.onSuccess(null);
            } else {
                boolean hasSumAggregation = false;
                for (SqlResultSetRow row : results.getRows()) {
                    LOGGER.finer("row = " + row);
                    int activityId = row.getInt("activityId");
                    int indicatorId = row.getInt("indicatorId");
                    activityIds.add(activityId);
                    indicatorIds.add(indicatorId);
                    activityMap.put(activityId, new EntityCategory(activityId, row.getString("activityName"), row.getInt("activityOrder")));
                    activityCategoryMap.put(activityId, new SimpleCategory(row.getString("activityCategory")));
                    activityToDatabaseMap.put(activityId, new EntityCategory(row.getInt("databaseId"), row.getString("databaseName")));
                    indicatorMap.put(indicatorId, new EntityCategory(indicatorId, row.getString("indicatorName"), row.getInt("indicatorOrder")));
                    indicatorAggregationMap.put(indicatorId, row.getInt("aggregation"));
                    if (!hasSumAggregation) {
                        hasSumAggregation = row.getInt("aggregation") == 0;
                    }
                }
                // set aggregation to Sum for all indicators if at least one indicator has different aggregation
                if (hasSumAggregation) {
                    for (Map.Entry<Integer, Integer> entry : indicatorAggregationMap.entrySet()) {
                        // set to Sum
                        entry.setValue(0);
                    }
                }
                if (queryContext.getCommand().isPivotedBy(DimensionType.AttributeGroup)) {
                    queryAttributeGroups();
                } else {
                    querySites();
                }
            }
        }
    });
}

Example 18 with SqlResultSet

use of com.bedatadriven.rebar.sql.client.SqlResultSet in project activityinfo by bedatadriven.

the class GetActivityFormHandler method applyPermissions.

private Promise<ActivityFormDTO> applyPermissions(final ExecutionContext context, final ActivityFormDTO form) {
    final Promise<ActivityFormDTO> result = new Promise<>();
    SqlQuery.selectAll().appendColumn("allowView").appendColumn("allowViewAll").appendColumn("allowEdit").appendColumn("allowEditAll").appendColumn("allowDesign").appendColumn("partnerId").from(Tables.USER_PERMISSION, "p").where("p.UserId").equalTo(context.getUser().getId()).where("p.DatabaseId").equalTo(form.getDatabaseId()).execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            if (results.getRows().isEmpty()) {
                if (form.getPublished() == Published.ALL_ARE_PUBLISHED.getIndex()) {
                    result.resolve(form);
                } else {
                    result.reject(new IllegalAccessCommandException("User " + context.getUser().getId() + " does not have access to form " + form.getId()));
                }
                return;
            }
            SqlResultSetRow row = results.getRow(0);
            if (!row.getBoolean("allowView")) {
                if (form.getPublished() == Published.ALL_ARE_PUBLISHED.getIndex()) {
                    result.resolve(form);
                } else {
                    result.reject(new IllegalAccessCommandException("User " + context.getUser().getId() + " does not have access to form " + form.getId()));
                }
                return;
            }
            form.setEditAllowed(row.getBoolean("allowEdit"));
            form.setEditAllAllowed(row.getBoolean("allowEditAll"));
            form.setDesignAllowed(row.getBoolean("allowDesign"));
            form.setCurrentPartnerId(row.getInt("partnerId"));
            result.resolve(form);
        }
    });
    return result;
}

Example 19 with SqlResultSet

use of com.bedatadriven.rebar.sql.client.SqlResultSet in project activityinfo by bedatadriven.

the class GetActivityFormsHandler method execute.

@Override
public void execute(GetActivityForms command, final ExecutionContext context, final AsyncCallback<ActivityFormResults> callback) {
    composeQuery(command.getFilter()).execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, final SqlResultSet results) {
            context.execute(new GetSchema(), new AsyncCallback<SchemaDTO>() {

                @Override
                public void onFailure(Throwable caught) {
                    callback.onFailure(caught);
                }

                @Override
                public void onSuccess(SchemaDTO schema) {
                    LOGGER.log(Level.INFO, "Forms matching filter: " + results.getRows().size());
                    final List<Promise<ActivityFormDTO>> pending = new ArrayList<>();
                    for (SqlResultSetRow row : results.getRows()) {
                        int activityId = row.getInt("activityId");
                        boolean visible = (schema.getActivityById(activityId) != null);
                        if (visible) {
                            pending.add(fetchForm(context, activityId));
                        }
                    }
                    LOGGER.log(Level.INFO, "Forms pending: " + pending.size());
                    Promise.waitAll(pending).then(new Function<Void, ActivityFormResults>() {

                        @Nullable
                        @Override
                        public ActivityFormResults apply(@Nullable Void aVoid) {
                            LOGGER.log(Level.INFO, "Form loading completed.");
                            List<ActivityFormDTO> forms = new ArrayList<>();
                            for (Promise<ActivityFormDTO> pendingForm : pending) {
                                forms.add(pendingForm.get());
                            }
                            return new ActivityFormResults(forms);
                        }
                    }).then(callback);
                }
            });
        }
    });
}

Example 20 with SqlResultSet

use of com.bedatadriven.rebar.sql.client.SqlResultSet in project activityinfo by bedatadriven.

the class GetAdminEntitiesHandler method execute.

@Override
public void execute(GetAdminEntities cmd, ExecutionContext context, final AsyncCallback<AdminEntityResult> callback) {
    SqlQuery query = SqlQuery.select("AdminEntity.adminEntityId", "AdminEntity.name", "AdminEntity.adminLevelId", "AdminEntity.adminEntityParentId", "x1", "y1", "x2", "y2").from(Tables.ADMIN_ENTITY, "AdminEntity").whereTrue("not AdminEntity.deleted");
    query.orderBy("AdminEntity.name");
    if (cmd.getLevelId() != null) {
        query.where("AdminEntity.AdminLevelId").equalTo(cmd.getLevelId());
    } else {
        query.leftJoin(Tables.ADMIN_LEVEL, "level").on("AdminEntity.AdminLevelID=level.AdminLevelId");
        query.appendColumn("level.name", "levelName");
    }
    if (cmd.getEntityIds() != null && !cmd.getEntityIds().isEmpty()) {
        query.where("AdminEntity.AdminEntityId").in(cmd.getEntityIds());
    }
    if (cmd.getParentId() != null) {
        if (cmd.getParentId() == GetAdminEntities.ROOT) {
            query.where("AdminEntity.AdminEntityParentId IS NULL");
        } else {
            query.where("AdminEntity.AdminEntityParentId").equalTo(cmd.getParentId());
        }
    }
    if (cmd.getFilter() != null) {
        Filter filter = cmd.getFilter();
        if (filter.isRestricted(DimensionType.Activity) || filter.isRestricted(DimensionType.Database) || filter.isRestricted(DimensionType.Indicator)) {
            SqlQuery subQuery = SqlQuery.select("link.AdminEntityId").from(Tables.SITE, "site").leftJoin(Tables.LOCATION, "Location").on("Location.LocationId = site.LocationId").leftJoin(Tables.LOCATION_ADMIN_LINK, "link").on("link.LocationId = Location.LocationId");
            if (filter.isRestricted(DimensionType.Activity)) {
                subQuery.where("site.ActivityId").in(filter.getRestrictions(DimensionType.Activity));
            }
            if (filter.isRestricted(DimensionType.Database)) {
                subQuery.leftJoin(Tables.ACTIVITY, "activity").on("site.ActivityId=activity.ActivityId").where("activity.DatabaseId").in(filter.getRestrictions(DimensionType.Database));
            }
            if (filter.isRestricted(DimensionType.Indicator)) {
                subQuery.leftJoin(Tables.REPORTING_PERIOD, "rp").on("site.siteId=rp.SiteId").leftJoin(Tables.INDICATOR_VALUE, "iv").on("iv.reportingPeriodId=rp.reportingPeriodId").where("iv.indicatorId").in(filter.getRestrictions(DimensionType.Indicator));
            }
            query.where("AdminEntity.AdminEntityId").in(subQuery);
        }
    }
    if (cmd.getFilter() != null && cmd.getFilter().isRestricted(DimensionType.AdminLevel)) {
        if (cmd.getLevelId() == null) {
            query.where("AdminEntityId").in(cmd.getFilter().getRestrictions(DimensionType.AdminLevel));
        } else {
            SqlQuery subQuery = SqlQuery.select("adminEntityId").from(Tables.ADMIN_ENTITY, "AdminEntity").where("AdminLevelId").equalTo(cmd.getLevelId()).where("AdminEntityId").in(cmd.getFilter().getRestrictions(DimensionType.AdminLevel));
            query.where("AdminEntity.AdminEntityId").in(subQuery);
        }
    }
    query.execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            final List<AdminEntityDTO> entities = new ArrayList<AdminEntityDTO>();
            Set<String> names = Sets.newHashSet();
            Set<String> duplicates = Sets.newHashSet();
            for (SqlResultSetRow row : results.getRows()) {
                AdminEntityDTO entity = toEntity(row);
                if (!names.add(entity.getName())) {
                    duplicates.add(entity.getName());
                }
                entities.add(entity);
            }
            for (int i = 0; i != entities.size(); ++i) {
                if (duplicates.contains(entities.get(i).getName())) {
                    String levelName = results.getRow(i).getString("levelName");
                    entities.get(i).setName(entities.get(i).getName() + " [" + levelName + "]");
                }
            }
            callback.onSuccess(new AdminEntityResult(entities));
        }
    });
}