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

Example 11 with SqlQuery

use of com.bedatadriven.rebar.sql.client.query.SqlQuery 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 12 with SqlQuery

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

the class PivotQuery method appendDimensionRestrictions.

private void appendDimensionRestrictions() {
    if (filter != null) {
        if (filter.getRestrictedDimensions() != null && filter.getRestrictedDimensions().size() > 0) {
            query.where("(");
            boolean isFirst = true;
            for (DimensionType type : filter.getRestrictedDimensions()) {
                addJoint(query, isFirst);
                if (isFirst) {
                    isFirst = false;
                }
                if (type == DimensionType.AdminLevel) {
                    query.onlyWhere(baseTable.getDimensionIdColumn(DimensionType.Location)).in(SqlQuery.select("Link.LocationId").from(Tables.LOCATION_ADMIN_LINK, "Link").where("Link.AdminEntityId").in(filter.getRestrictions(DimensionType.AdminLevel)));
                } else if (type == DimensionType.Attribute) {
                    Set<Integer> attributes = filter.getRestrictions(DimensionType.Attribute);
                    boolean isFirstAttr = true;
                    for (Integer attribute : attributes) {
                        SqlQuery attributefilter = SqlQuery.select().appendColumn("1", "__VAL_EXISTS").from("attributevalue", "v").whereTrue("v.value=1").and("v.SiteId = Site.SiteId").where("v.AttributeId").equalTo(attribute);
                        addJoint(query, isFirstAttr);
                        if (isFirstAttr) {
                            isFirstAttr = false;
                        }
                        query.onlyWhere("EXISTS (" + attributefilter.sql() + ") ");
                        query.appendParameter(attribute);
                    }
                } else {
                    query.onlyWhere(baseTable.getDimensionIdColumn(type)).in(filter.getRestrictions(type));
                }
            }
            query.onlyWhere(")");
        }
    }
}

Example 13 with SqlQuery

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

the class DbUpdateBuilder method insert.

private void insert(String tableName, String criteria) {
    if (permission.isAllowView() && !database.isDeleted()) {
        SqlQuery query = SqlQuery.selectAll().from(tableName).whereTrue(criteria.toLowerCase());
        LOGGER.fine(query.sql());
        batch.insert().into(tableName).from(query).execute(entityManager);
    }
}

Example 14 with SqlQuery

use of com.bedatadriven.rebar.sql.client.query.SqlQuery 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 SqlQuery

use of com.bedatadriven.rebar.sql.client.query.SqlQuery 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;
}