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

Example 6 with SqlTransaction

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

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

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

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

Example 10 with SqlTransaction

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

the class GetAdminLevelsHandler method execute.

@Override
public void execute(GetAdminLevels command, ExecutionContext context, final AsyncCallback<AdminLevelResult> callback) {
    if (CollectionUtil.isEmpty(command.getIndicatorIds())) {
        callback.onSuccess(new AdminLevelResult(new ArrayList<AdminLevelDTO>()));
        return;
    }
    String hasPolygonsSubQuery = "exists (select e.adminentityid from adminentity e " + "where e.adminlevelid=level.adminlevelid and geometry is not null)";
    SqlQuery.select().appendColumn("level.adminlevelId", "id").appendColumn("level.name", "name").appendColumn(hasPolygonsSubQuery, "polygons").from(Tables.INDICATOR, "i").innerJoin(Tables.ACTIVITY, "a").on("i.activityId=a.activityId").innerJoin(Tables.USER_DATABASE, "db").on("a.databaseid=db.databaseid").innerJoin(Tables.COUNTRY, "c").on("db.countryid=c.countryid").innerJoin(Tables.ADMIN_LEVEL, "level").on("level.countryid=c.countryid").where("i.indicatorId").in(command.getIndicatorIds()).groupBy("level.adminlevelid").groupBy("level.name").execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            List<AdminLevelDTO> levels = Lists.newArrayList();
            for (SqlResultSetRow row : results.getRows()) {
                AdminLevelDTO level = new AdminLevelDTO();
                level.setId(row.getInt("id"));
                level.setName(row.getString("name"));
                level.setPolygons(row.getBoolean("polygons"));
                levels.add(level);
            }
            callback.onSuccess(new AdminLevelResult(levels));
        }
    });
}