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

Example 6 with SqlResultSet

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

use of com.bedatadriven.rebar.sql.client.SqlResultSet 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 8 with SqlResultSet

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

the class GetMonthlyReportsHandlerAsync method execute.

@Override
public void execute(final GetMonthlyReports command, final ExecutionContext context, AsyncCallback<MonthlyReportResult> callback) {
    final Promise<SqlResultSet> indicators = queryIndicators(command, context);
    final Promise<SqlResultSet> periods = queryPeriods(command, context);
    Promise.waitAll(indicators, periods).then(new Function<Void, MonthlyReportResult>() {

        @Nullable
        @Override
        public MonthlyReportResult apply(@Nullable Void input) {
            Map<Integer, IndicatorRowDTO> indicatorMap = new HashMap<Integer, IndicatorRowDTO>();
            List<IndicatorRowDTO> rows = Lists.newArrayList();
            for (SqlResultSetRow indicatorRow : indicators.get().getRows()) {
                IndicatorRowDTO dto = new IndicatorRowDTO();
                dto.setIndicatorId(indicatorRow.getInt("indicatorId"));
                dto.setSiteId(command.getSiteId());
                dto.setIndicatorName(indicatorRow.getString("indicatorName"));
                dto.setCategory(indicatorRow.getString("category"));
                dto.setActivityName(indicatorRow.getString("activityName"));
                dto.setActivityId(indicatorRow.getInt("activityId"));
                indicatorMap.put(dto.getIndicatorId(), dto);
                rows.add(dto);
            }
            for (SqlResultSetRow period : periods.get().getRows()) {
                Date endDate = period.getDate("Date2");
                Month month = Month.of(endDate);
                if (month.compareTo(command.getStartMonth()) >= 0 && month.compareTo(command.getEndMonth()) <= 0) {
                    IndicatorRowDTO row = indicatorMap.get(period.getInt("indicatorId"));
                    if (row != null) {
                        row.setValue(month, period.getDouble("value"));
                    }
                }
            }
            return new MonthlyReportResult(rows);
        }
    }).then(callback);
}

Example 9 with SqlResultSet

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

the class GetMonthlyReportsHandlerAsync method queryPeriods.

private Promise<SqlResultSet> queryPeriods(final GetMonthlyReports command, final ExecutionContext context) {
    final Promise<SqlResultSet> result = new Promise<>();
    SqlQuery.select("rp.Date1", "rp.Date2", "v.indicatorId", "v.value").from(Tables.REPORTING_PERIOD, "rp").leftJoin(Tables.INDICATOR_VALUE, "v").on("rp.ReportingPeriodId=v.ReportingPeriodId").where("rp.SiteId").equalTo(command.getSiteId()).execute(context.getTransaction(), new SqlResultCallback() {

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

Example 10 with SqlResultSet

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

the class DeleteSiteHandlerAsync method execute.

@Override
public void execute(DeleteSite command, ExecutionContext context, final AsyncCallback<VoidResult> callback) {
    context.getTransaction().executeSql("DELETE FROM indicatorvalue WHERE ReportingPeriodId IN " + "(SELECT ReportingPeriodId FROM reportingperiod WHERE siteid = ?)", new Object[] { command.getId() });
    SqlUpdate.delete(Tables.ATTRIBUTE_VALUE).where("siteId", command.getId()).execute(context.getTransaction());
    SqlUpdate.delete(Tables.REPORTING_PERIOD).where("siteId", command.getId()).execute(context.getTransaction());
    SqlUpdate.update(Tables.SITE).value("dateDeleted", new Date()).value("timeEdited", new Date().getTime()).where("siteId", command.getId()).execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            callback.onSuccess(null);
        }
    });
}