Search in sources :

Example 11 with SqlResultCallback

use of com.bedatadriven.rebar.sql.client.SqlResultCallback 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));
        }
    });
}
Also used : SqlResultSet(com.bedatadriven.rebar.sql.client.SqlResultSet) SqlQuery(com.bedatadriven.rebar.sql.client.query.SqlQuery) SqlResultCallback(com.bedatadriven.rebar.sql.client.SqlResultCallback) SqlTransaction(com.bedatadriven.rebar.sql.client.SqlTransaction) SqlResultSetRow(com.bedatadriven.rebar.sql.client.SqlResultSetRow) DimensionLabels(org.activityinfo.legacy.shared.command.GetDimensionLabels.DimensionLabels) GetDimensionLabels(org.activityinfo.legacy.shared.command.GetDimensionLabels) Map(java.util.Map)

Example 12 with SqlResultCallback

use of com.bedatadriven.rebar.sql.client.SqlResultCallback 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);
        }
    });
}
Also used : SqlResultSet(com.bedatadriven.rebar.sql.client.SqlResultSet) SqlQuery(com.bedatadriven.rebar.sql.client.query.SqlQuery) Bucket(org.activityinfo.legacy.shared.command.result.Bucket) SqlResultCallback(com.bedatadriven.rebar.sql.client.SqlResultCallback) SqlTransaction(com.bedatadriven.rebar.sql.client.SqlTransaction) Dimension(org.activityinfo.legacy.shared.reports.model.Dimension) AdminDimension(org.activityinfo.legacy.shared.reports.model.AdminDimension) DateDimension(org.activityinfo.legacy.shared.reports.model.DateDimension) AttributeGroupDimension(org.activityinfo.legacy.shared.reports.model.AttributeGroupDimension) SqlResultSetRow(com.bedatadriven.rebar.sql.client.SqlResultSetRow)

Example 13 with SqlResultCallback

use of com.bedatadriven.rebar.sql.client.SqlResultCallback 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);
        }
    });
}
Also used : SqlQuery(com.bedatadriven.rebar.sql.client.query.SqlQuery) SqlTransaction(com.bedatadriven.rebar.sql.client.SqlTransaction) SqlResultSetRow(com.bedatadriven.rebar.sql.client.SqlResultSetRow) SqlResultSet(com.bedatadriven.rebar.sql.client.SqlResultSet) SiteResult(org.activityinfo.legacy.shared.command.result.SiteResult) SqlResultCallback(com.bedatadriven.rebar.sql.client.SqlResultCallback)

Example 14 with SqlResultCallback

use of com.bedatadriven.rebar.sql.client.SqlResultCallback 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;
}
Also used : Promise(org.activityinfo.promise.Promise) SqlResultSet(com.bedatadriven.rebar.sql.client.SqlResultSet) SqlQuery(com.bedatadriven.rebar.sql.client.query.SqlQuery) SqlResultCallback(com.bedatadriven.rebar.sql.client.SqlResultCallback) SqlTransaction(com.bedatadriven.rebar.sql.client.SqlTransaction) SqlResultSetRow(com.bedatadriven.rebar.sql.client.SqlResultSetRow)

Example 15 with SqlResultCallback

use of com.bedatadriven.rebar.sql.client.SqlResultCallback 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();
        }
    });
}
Also used : SqlResultSet(com.bedatadriven.rebar.sql.client.SqlResultSet) SqlResultCallback(com.bedatadriven.rebar.sql.client.SqlResultCallback) SqlTransaction(com.bedatadriven.rebar.sql.client.SqlTransaction) Dimension(org.activityinfo.legacy.shared.reports.model.Dimension) AdminDimension(org.activityinfo.legacy.shared.reports.model.AdminDimension) DateDimension(org.activityinfo.legacy.shared.reports.model.DateDimension) AttributeGroupDimension(org.activityinfo.legacy.shared.reports.model.AttributeGroupDimension) AttributeGroupDimension(org.activityinfo.legacy.shared.reports.model.AttributeGroupDimension) SqlResultSetRow(com.bedatadriven.rebar.sql.client.SqlResultSetRow) EntityCategory(org.activityinfo.legacy.shared.reports.content.EntityCategory)

Aggregations

SqlResultCallback (com.bedatadriven.rebar.sql.client.SqlResultCallback)23 SqlResultSet (com.bedatadriven.rebar.sql.client.SqlResultSet)23 SqlTransaction (com.bedatadriven.rebar.sql.client.SqlTransaction)23 SqlResultSetRow (com.bedatadriven.rebar.sql.client.SqlResultSetRow)18 SqlQuery (com.bedatadriven.rebar.sql.client.query.SqlQuery)13 Promise (org.activityinfo.promise.Promise)10 ArrayList (java.util.ArrayList)4 List (java.util.List)4 Map (java.util.Map)3 Date (java.util.Date)2 Filter (org.activityinfo.legacy.shared.command.Filter)2 LocationResult (org.activityinfo.legacy.shared.command.result.LocationResult)2 AdminEntityDTO (org.activityinfo.legacy.shared.model.AdminEntityDTO)2 LocationDTO (org.activityinfo.legacy.shared.model.LocationDTO)2 EntityCategory (org.activityinfo.legacy.shared.reports.content.EntityCategory)2 AdminDimension (org.activityinfo.legacy.shared.reports.model.AdminDimension)2 AttributeGroupDimension (org.activityinfo.legacy.shared.reports.model.AttributeGroupDimension)2 DateDimension (org.activityinfo.legacy.shared.reports.model.DateDimension)2 Dimension (org.activityinfo.legacy.shared.reports.model.Dimension)2 Function (com.google.common.base.Function)1