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

Example 6 with SqlQuery

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

the class SearchLocationsHandler method retrieveLocations.

private void retrieveLocations(final SearchLocations command, final ExecutionContext context, final AsyncCallback<LocationResult> callback, final int limit, final int totalCount) {
    SqlQuery query = baseQuery(command).appendColumns("LocationId", "Name", "Axe", "X", "Y", "LocationTypeId").setLimitClause(dialect.limitClause(0, limit));
    query.execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            // Create a list of locations from query result
            List<LocationDTO> locations = Lists.newArrayList();
            Map<Integer, LocationDTO> locationsById = Maps.newHashMap();
            for (SqlResultSetRow row : results.getRows()) {
                LocationDTO location = LocationDTO.fromSqlRow(row);
                locations.add(location);
                locationsById.put(location.getId(), location);
            }
            LocationResult result = new LocationResult(locations);
            result.setOffset(0);
            result.setTotalLength(totalCount > results.getRows().size() ? totalCount : results.getRows().size());
            callback.onSuccess(result);
        }
    });
}

Example 7 with SqlQuery

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

the class SearchLocationsHandler method baseQuery.

private SqlQuery baseQuery(final SearchLocations command) {
    SqlQuery query = SqlQuery.select().from("location");
    query.where("workflowStatusId").equalTo(LocationDTO.VALIDATED);
    if (command.getAdminEntityIds() != null) {
        for (Integer adminEntityId : command.getAdminEntityIds()) {
            query.where("LocationId").in(SqlQuery.select("LocationId").from("locationadminlink").where("adminentityid").equalTo(adminEntityId));
        }
    }
    query.orderBy("location.name");
    if (command.getLocationTypeId() != 0) {
        query.where("locationTypeID").equalTo(command.getLocationTypeId());
    }
    if (!Strings.isNullOrEmpty(command.getName())) {
        query.where("Name").startsWith(command.getName());
    }
    if (command.getIndicatorIds() != null && !command.getIndicatorIds().isEmpty()) {
        query.where("LocationID").in(SqlQuery.selectDistinct().appendColumns("LocationId").from("site", "site").leftJoin("reportingperiod", "period").on("site.SiteID=period.SiteId").leftJoin("indicatorvalue", "iv").on("iv.ReportingPeriodId=period.ReportingPeriodId").where("iv.IndicatorId").in(command.getIndicatorIds()));
    }
    return query;
}

Example 8 with SqlQuery

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

the class GetLocationsHandler method execute.

@Override
public void execute(final GetLocations command, final ExecutionContext context, final AsyncCallback<LocationResult> callback) {
    if (!command.hasLocationIds() && command.getLocationTypeId() == null) {
        callback.onSuccess(new LocationResult());
        return;
    }
    final Map<Integer, LocationDTO> dtos = new HashMap<>();
    SqlQuery query = SqlQuery.select("locationID", "name", "axe", "x", "y", "workflowStatusId", "LocationTypeId").from(Tables.LOCATION, "Location");
    if (!command.getLocationIds().isEmpty()) {
        query.where("LocationId").in(command.getLocationIds());
    }
    if (command.getLocationTypeId() != null) {
        query.where("locationTypeId").equalTo(command.getLocationTypeId());
    }
    query.where("workflowstatusid").equalTo("validated");
    query.execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            for (SqlResultSetRow row : results.getRows()) {
                final LocationDTO dto = new LocationDTO();
                dto.setId(row.getInt("locationID"));
                dto.setName(row.getString("name"));
                dto.setAxe(row.getString("axe"));
                dto.setWorkflowStatusId(row.getString("workflowStatusId"));
                dto.setLocationTypeId(row.getInt("LocationTypeId"));
                if (!row.isNull("x") && !row.isNull("y")) {
                    dto.setLatitude(row.getDouble("y"));
                    dto.setLongitude(row.getDouble("x"));
                }
                dtos.put(dto.getId(), dto);
            }
            SqlQuery query = SqlQuery.select().appendColumn("AdminEntity.AdminEntityId", "adminEntityId").appendColumn("AdminEntity.Name", "name").appendColumn("AdminEntity.AdminLevelId", "levelId").appendColumn("AdminEntity.AdminEntityParentId", "parentId").appendColumn("link.LocationID", "locationId").from(Tables.LOCATION_ADMIN_LINK, "link").leftJoin(Tables.ADMIN_ENTITY, "AdminEntity").on("link.AdminEntityId=AdminEntity.AdminEntityId").whereTrue("AdminEntity.AdminEntityId is not null");
            if (!command.getLocationIds().isEmpty()) {
                query.where("link.LocationId").in(command.getLocationIds());
            }
            if (command.getLocationTypeId() != null) {
                query.leftJoin(Tables.LOCATION, "Location").on("link.LocationId=Location.LocationId");
                query.where("Location.LocationTypeId").equalTo(command.getLocationTypeId());
            }
            query.execute(context.getTransaction(), new SqlResultCallback() {

                @Override
                public void onSuccess(SqlTransaction tx, SqlResultSet results) {
                    for (SqlResultSetRow row : results.getRows()) {
                        AdminEntityDTO entity = new AdminEntityDTO();
                        entity.setId(row.getInt("adminEntityId"));
                        entity.setName(row.getString("name"));
                        entity.setLevelId(row.getInt("levelId"));
                        if (!row.isNull("parentId")) {
                            entity.setParentId(row.getInt("parentId"));
                        }
                        LocationDTO dto = dtos.get(row.getInt("locationId"));
                        if (dto != null) {
                            dto.setAdminEntity(entity.getLevelId(), entity);
                        }
                    }
                    List<LocationDTO> list = new ArrayList<>(dtos.values());
                    callback.onSuccess(new LocationResult(list));
                }
            });
        }
    });
}

Example 9 with SqlQuery

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

the class GetMonthlyReportsHandlerAsync method queryIndicators.

private Promise<SqlResultSet> queryIndicators(final GetMonthlyReports command, ExecutionContext context) {
    final Promise<SqlResultSet> promise = new Promise<>();
    SqlQuery activityQuery = SqlQuery.select("activityId").from(Tables.SITE).where("siteId").equalTo(command.getSiteId());
    SqlQuery.select().appendColumn("i.indicatorId", "indicatorId").appendColumn("i.name", "indicatorName").appendColumn("i.category", "category").appendColumn("a.name", "activityName").appendColumn("a.activityId", "activityId").from(Tables.INDICATOR, "i").leftJoin(Tables.ACTIVITY, "a").on("i.activityId=a.activityId").where("a.activityId").in(activityQuery).orderBy("i.sortOrder").execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet indicatorResults) {
            promise.resolve(indicatorResults);
        }
    });
    return promise;
}

Example 10 with SqlQuery

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