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

Example 21 with SqlQuery

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

the class PivotQuery method defineAttributeDimension.

/**
 * Defines an a dimension based on an Attribute Group defined
 * by the user. This is essentially a custom dimension
 */
private void defineAttributeDimension(AttributeGroupDimension dim) {
    // this pivots the data by a single-valued attribute group
    String valueQueryAlias = "attributeValues" + dim.getAttributeGroupId();
    // this first query gives us the single chosen attribute for
    // each site, arbitrarily taking the attribute with the minimum
    // id if more than one attribute has been selected (i.e db is inconsistent)
    // note that we select attributes by NAME rather than
    // the attribute group id itself. This permits merging
    // of attributes from other activities/dbs with the same name
    SqlQuery groupNameQuery = SqlQuery.select().appendColumn("name").from(Tables.ATTRIBUTE_GROUP).whereTrue("AttributeGroupId=" + dim.getAttributeGroupId());
    SqlQuery derivedValueQuery = SqlQuery.select().appendColumn("v.siteId", "siteId").appendColumn("min(a.name)", "value").appendColumn("min(a.sortOrder)", "sortOrder").from("attributevalue", "v").leftJoin("attribute", "a").on("v.AttributeId = a.AttributeId").leftJoin("attributegroup", "g").on("a.AttributeGroupId=g.AttributeGroupId").whereTrue("v.value=1").where("g.name").in(groupNameQuery).groupBy("v.siteId");
    query.leftJoin(derivedValueQuery, valueQueryAlias).on(baseTable.getDimensionIdColumn(DimensionType.Site) + "=" + valueQueryAlias + ".SiteId");
    String valueAlias = appendDimColumn(valueQueryAlias + ".value");
    String sortOrderAlias = appendDimColumn(valueQueryAlias + ".sortOrder");
    bundlers.add(new AttributeBundler(dim, valueAlias, sortOrderAlias));
}

Example 22 with SqlQuery

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

the class PivotQuery method appendVisibilityFilter.

private void appendVisibilityFilter() {
    // Join the user permissions for this user to the database
    SqlQuery userPermissions = SqlQuery.selectAll().from(Tables.USER_PERMISSION).whereTrue("userId=" + userId);
    query.leftJoin(userPermissions, "UP").on("UP.databaseId=UserDatabase.DatabaseId");
    // Filter those visible to this user
    query.whereTrue(new StringBuilder().append("(").append("UserDatabase.OwnerUserId = ").append(userId).append(" ").append(" OR Activity.Published > 0").append(" OR UP.AllowViewAll").append(" OR (UP.AllowView AND UP.PartnerId=Site.PartnerId)").append(")").toString());
}

Example 23 with SqlQuery

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

Example 24 with SqlQuery

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

the class JpaBatchBuilder method insert.

public void insert(Class<?> entity, String criteria) {
    SqlQuery query = SqlQuery.select(columnsToSync(entity)).from(tableName(entity)).whereTrue(criteria);
    batch.insert().into(tableName(entity)).from(query).execute(entityManager);
}

Example 25 with SqlQuery

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

the class LocationUpdateBuilder method buildUpdate.

private SyncRegionUpdate buildUpdate() throws JSONException, IOException {
    // Always update the LocationType as the cost is negligible and we don't
    // track the version of the LocationType properties separately
    batch.insert(LocationType.class, "LocationTypeId=" + locationType.getId());
    SqlQuery query = SqlQuery.select().appendColumn("version", "latest").from(Tables.LOCATION).where("locationTypeId").equalTo(locationType.getId()).where("version").greaterThan(localVersion).orderBy("version");
    List<Long> longs = SqlQueryUtil.queryLongList(em, query);
    if (longs.isEmpty()) {
        batch.setVersion(locationType.getVersion());
        batch.setComplete(true);
    } else {
        // our intention is to reduce batch, so we cut versions into chunks
        if (longs.size() > chunkSize) {
            longs = longs.subList(0, chunkSize);
            batch.setComplete(false);
            batch.setVersion(Collections.max(longs));
        } else {
            batch.setComplete(true);
            batch.setVersion(locationType.getVersion());
        }
        queryChanged();
        linkAdminEntities();
    }
    return batch.buildUpdate();
}