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

Example 11 with SqlResultSet

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

the class GetAdminLevelsHandler method execute.

@Override
public void execute(GetAdminLevels command, ExecutionContext context, final AsyncCallback<AdminLevelResult> callback) {
    if (CollectionUtil.isEmpty(command.getIndicatorIds())) {
        callback.onSuccess(new AdminLevelResult(new ArrayList<AdminLevelDTO>()));
        return;
    }
    String hasPolygonsSubQuery = "exists (select e.adminentityid from adminentity e " + "where e.adminlevelid=level.adminlevelid and geometry is not null)";
    SqlQuery.select().appendColumn("level.adminlevelId", "id").appendColumn("level.name", "name").appendColumn(hasPolygonsSubQuery, "polygons").from(Tables.INDICATOR, "i").innerJoin(Tables.ACTIVITY, "a").on("i.activityId=a.activityId").innerJoin(Tables.USER_DATABASE, "db").on("a.databaseid=db.databaseid").innerJoin(Tables.COUNTRY, "c").on("db.countryid=c.countryid").innerJoin(Tables.ADMIN_LEVEL, "level").on("level.countryid=c.countryid").where("i.indicatorId").in(command.getIndicatorIds()).groupBy("level.adminlevelid").groupBy("level.name").execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            List<AdminLevelDTO> levels = Lists.newArrayList();
            for (SqlResultSetRow row : results.getRows()) {
                AdminLevelDTO level = new AdminLevelDTO();
                level.setId(row.getInt("id"));
                level.setName(row.getString("name"));
                level.setPolygons(row.getBoolean("polygons"));
                levels.add(level);
            }
            callback.onSuccess(new AdminLevelResult(levels));
        }
    });
}

Example 12 with SqlResultSet

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

the class GetDimensionLabelsHandler method execute.

@Override
public void execute(GetDimensionLabels command, ExecutionContext context, final AsyncCallback<DimensionLabels> callback) {
    SqlQuery query = composeQuery(command);
    query.execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            Map<Integer, String> labels = Maps.newHashMap();
            for (SqlResultSetRow row : results.getRows()) {
                labels.put(row.getInt("id"), row.getString("name"));
            }
            callback.onSuccess(new DimensionLabels(labels));
        }
    });
}

Example 13 with SqlResultSet

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

the class PivotQuery method execute.

@Override
public void execute(final AsyncCallback<Void> callback) {
    baseTable.setupQuery(command, query);
    if (command.isPivotedBy(DimensionType.Location) || command.isPivotedBy(DimensionType.Site)) {
        query.leftJoin(Tables.LOCATION, "Location").on("Location.LocationId=" + baseTable.getDimensionIdColumn(DimensionType.Location));
    }
    if (command.isPivotedBy(DimensionType.Partner)) {
        query.leftJoin(Tables.PARTNER, "Partner").on("Partner.PartnerId=" + baseTable.getDimensionIdColumn(DimensionType.Partner));
    }
    if (command.isPivotedBy(DimensionType.Project)) {
        SqlQuery activeProjects = SqlQuery.selectAll().from(Tables.PROJECT, "AllProjects").where("AllProjects.dateDeleted").isNull();
        query.leftJoin(activeProjects, "Project").on("Project.ProjectId=" + baseTable.getDimensionIdColumn(DimensionType.Project));
    }
    addDimensionBundlers();
    // otherwise permissions have already been taken into account during synchronization
    if (isRemote()) {
        appendVisibilityFilter();
    }
    if (filter.getEndDateRange().getMinDate() != null) {
        query.where(baseTable.getDateCompleteColumn()).greaterThanOrEqualTo(filter.getEndDateRange().getMinDate());
    }
    if (filter.getEndDateRange().getMaxDate() != null) {
        query.where(baseTable.getDateCompleteColumn()).lessThanOrEqualTo(filter.getEndDateRange().getMaxDate());
    }
    appendDimensionRestrictions();
    Log.debug("PivotQuery (" + baseTable.getClass() + ") executing query: " + query.sql());
    query.execute(tx, new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            for (SqlResultSetRow row : results.getRows()) {
                Bucket bucket = new Bucket();
                bucket.setAggregationMethod(row.getInt(ValueFields.AGGREGATION));
                bucket.setCount(row.getInt(ValueFields.COUNT));
                if (!row.isNull(ValueFields.SUM)) {
                    bucket.setSum(row.getDouble(ValueFields.SUM));
                }
                bucket.setCategory(new Dimension(DimensionType.Target), baseTable.getTargetCategory());
                for (Bundler bundler : bundlers) {
                    bundler.bundle(row, bucket);
                }
                context.addBucket(bucket);
            }
            callback.onSuccess(null);
        }
    });
}

Example 14 with SqlResultSet

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

the class OldGetSitesHandler method doQuery.

private void doQuery(final GetSites command, final ExecutionContext context, final AsyncCallback<SiteResult> callback) {
    // in order to pull in the linked queries, we want to
    // to create two queries that we union together.
    // for performance reasons, we want to apply all of the joins
    // and filters on both parts of the union query
    SqlQuery unioned;
    if (command.isFetchLinks()) {
        unioned = unionedQuery(context, command);
        unioned.appendAllColumns();
    } else {
        unioned = primaryQuery(context, command);
    }
    if (isMySql() && command.getLimit() >= 0) {
        // with this feature, MySQL will keep track of the total
        // number of rows regardless of our limit statement.
        // This way we don't have to execute the query twice to
        // get the total count
        // 
        // unfortunately, this is not available on sqlite
        unioned.appendKeyword("SQL_CALC_FOUND_ROWS");
    }
    applySort(unioned, command.getSortInfo());
    applyPaging(unioned, command);
    final Multimap<Integer, SiteDTO> siteMap = HashMultimap.create();
    final List<SiteDTO> sites = new ArrayList<SiteDTO>();
    final Map<Integer, SiteDTO> reportingPeriods = Maps.newHashMap();
    final SiteResult result = new SiteResult(sites);
    result.setOffset(command.getOffset());
    Log.trace("About to execute primary query: " + unioned.sql());
    unioned.execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            Log.trace("Primary query returned " + results.getRows().size() + ", rows, starting to add to map");
            for (SqlResultSetRow row : results.getRows()) {
                SiteDTO site = toSite(command, row);
                sites.add(site);
                siteMap.put(site.getId(), site);
                if (command.isFetchAllReportingPeriods()) {
                    reportingPeriods.put(row.getInt("PeriodId"), site);
                }
            }
            Log.trace("Finished adding to map");
            List<Promise<Void>> queries = Lists.newArrayList();
            if (command.getLimit() <= 0) {
                result.setTotalLength(results.getRows().size());
            } else {
                queries.add(queryTotalLength(tx, command, context, result));
            }
            if (!sites.isEmpty()) {
                if (command.isFetchAdminEntities()) {
                    queries.add(joinEntities(tx, siteMap));
                }
                if (command.isFetchAttributes()) {
                    queries.add(joinAttributeValues(command, tx, siteMap));
                }
                if (command.fetchAnyIndicators()) {
                    queries.add(joinIndicatorValues(command, tx, siteMap, reportingPeriods));
                }
            }
            Promise.waitAll(queries).then(Functions.constant(result)).then(callback);
        }
    });
}

Example 15 with SqlResultSet

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

the class OldGetSitesHandler method joinAttributeValues.

private Promise<Void> joinAttributeValues(GetSites command, SqlTransaction tx, final Multimap<Integer, SiteDTO> siteMap) {
    Log.trace("Starting joinAttributeValues() ");
    final Promise<Void> complete = new Promise<>();
    SqlQuery sqlQuery = SqlQuery.select().appendColumn("v.AttributeId", "attributeId").appendColumn("a.Name", "attributeName").appendColumn("v.Value", "value").appendColumn("v.SiteId", "siteId").appendColumn("g.name", "groupName").from(Tables.ATTRIBUTE_VALUE, "v").leftJoin(Tables.ATTRIBUTE, "a").on("v.AttributeId = a.AttributeId").leftJoin(Tables.ATTRIBUTE_GROUP, "g").on("a.AttributeGroupId=g.AttributeGroupId").whereTrue("v.Value=1").and("g.dateDeleted IS NULL").orderBy("groupName, attributeName");
    if (weAreFetchingAllSitesForAnActivityAndThereAreNoLinkedSites(command, siteMap)) {
        sqlQuery.leftJoin(Tables.ATTRIBUTE_GROUP_IN_ACTIVITY, "ag").on("ag.attributeGroupId=g.attributeGroupId").where("ag.ActivityId").in(command.getFilter().getRestrictions(DimensionType.Activity));
    } else {
        sqlQuery.where("v.SiteId").in(siteMap.keySet());
    }
    sqlQuery.execute(tx, new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            Log.trace("Received results for joinAttributeValues() ");
            for (SqlResultSetRow row : results.getRows()) {
                int attributeId = row.getInt("attributeId");
                boolean value = row.getBoolean("value");
                String groupName = row.getString("groupName");
                String attributeName = row.getString("attributeName");
                for (SiteDTO site : siteMap.get(row.getInt("siteId"))) {
                    site.setAttributeValue(attributeId, value);
                    if (value) {
                        site.addDisplayAttribute(groupName, attributeName);
                    }
                }
            }
            Log.trace("Done populating results for joinAttributeValues()");
            complete.onSuccess(null);
        }
    });
    return complete;
}