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

Example 1 with SqlResultSetRow

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

the class OldGetSitesHandler method joinIndicatorValues.

private Promise<Void> joinIndicatorValues(final GetSites command, SqlTransaction tx, final Multimap<Integer, SiteDTO> siteMap, final Map<Integer, SiteDTO> periodMap) {
    final Promise<Void> complete = new Promise<>();
    Log.trace("Starting joinIndicatorValues()");
    SqlQuery query = SqlQuery.select().appendColumn("P.SiteId", "SiteId").appendColumn("V.IndicatorId", "SourceIndicatorId").appendColumn("I.ActivityId", "SourceActivityId").appendColumn("D.IndicatorId", "DestIndicatorId").appendColumn("D.ActivityId", "DestActivityId").appendColumn("I.Type").appendColumn("I.Expression").appendColumn("V.Value").appendColumn("V.TextValue").appendColumn("V.DateValue").appendColumn("P.ReportingPeriodId", "PeriodId").from(Tables.REPORTING_PERIOD, "P").innerJoin(Tables.INDICATOR_VALUE, "V").on("P.ReportingPeriodId = V.ReportingPeriodId").innerJoin(Tables.INDICATOR, "I").on("I.IndicatorId = V.IndicatorId").leftJoin(Tables.INDICATOR_LINK, "L").on("L.SourceIndicatorId=I.IndicatorId").leftJoin(Tables.INDICATOR, "D").on("L.DestinationIndicatorId=D.IndicatorId").whereTrue("I.dateDeleted IS NULL");
    if (weAreFetchingAllSitesForAnActivityAndThereAreNoLinkedSites(command, siteMap)) {
        query.where("I.ActivityId").in(command.getFilter().getRestrictions(DimensionType.Activity));
    } else {
        query.where("P.SiteId").in(siteMap.keySet());
    }
    query.execute(tx, new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            Log.trace("Received results for join indicators");
            for (final SqlResultSetRow row : results.getRows()) {
                FieldTypeClass indicatorType = FormFieldType.valueOf(row.getString("Type"));
                String expression = row.getString("Expression");
                boolean isCalculatedIndicator = !Strings.isNullOrEmpty(expression);
                Object indicatorValue = null;
                if (isCalculatedIndicator) {
                // ignore -> see joinCalculatedIndicatorValues
                } else {
                    // if indicator is no calculated then assign value directly
                    if (indicatorType == FieldTypeClass.QUANTITY) {
                        if (!row.isNull("Value")) {
                            indicatorValue = row.getDouble("Value");
                        }
                    } else if (indicatorType == FieldTypeClass.FREE_TEXT || indicatorType == FieldTypeClass.NARRATIVE || indicatorType == ReferenceType.TYPE_CLASS || indicatorType == AttachmentType.TYPE_CLASS) {
                        if (!row.isNull("TextValue")) {
                            indicatorValue = row.getString("TextValue");
                        }
                    } else if (indicatorType == FieldTypeClass.LOCAL_DATE) {
                        indicatorValue = row.getDate("DateValue");
                    } else if (indicatorType == FieldTypeClass.BOOLEAN) {
                        if (!row.isNull("BooleanValue")) {
                            indicatorValue = row.getBoolean("BooleanValue");
                        }
                    }
                }
                int sourceActivityId = row.getInt("SourceActivityId");
                if (command.isFetchAllReportingPeriods()) {
                    SiteDTO site = periodMap.get(row.getInt("PeriodId"));
                    if (site != null) {
                        site.setIndicatorValue(row.getInt("SourceIndicatorId"), indicatorValue);
                    }
                } else {
                    for (SiteDTO site : siteMap.get(row.getInt("SiteId"))) {
                        if (sourceActivityId == site.getActivityId()) {
                            int indicatorId = row.getInt("SourceIndicatorId");
                            site.setIndicatorValue(indicatorId, indicatorValue);
                        } else if (!row.isNull("DestActivityId")) {
                            int destActivityId = row.getInt("DestActivityId");
                            if (site.getActivityId() == destActivityId) {
                                int indicatorId = row.getInt("DestIndicatorId");
                                site.setIndicatorValue(indicatorId, indicatorValue);
                            }
                        }
                    }
                }
            }
            Log.trace("Done populating dtos for join indicators");
            // after normal indicators are evaluated try to calculate indicators with expression
            joinCalculatedIndicatorValues(complete, tx, siteMap);
        }
    });
    return complete;
}

Example 2 with SqlResultSetRow

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

the class OldGetSitesHandler method joinCalculatedIndicatorValues.

private void joinCalculatedIndicatorValues(final Promise<Void> complete, SqlTransaction tx, final Multimap<Integer, SiteDTO> siteMap) {
    Log.trace("Starting joinIndicatorValues()");
    final Set<Integer> activityIds = Sets.newHashSet();
    for (SiteDTO siteDTO : siteMap.values()) {
        activityIds.add(siteDTO.getActivityId());
    }
    SqlQuery query = SqlQuery.select().appendColumn("I.IndicatorId", "indicatorId").appendColumn("I.Name", "indicatorName").appendColumn("I.ActivityId", "activityId").appendColumn("I.Type", "type").appendColumn("I.Expression", "expression").appendColumn("I.nameInExpression", "code").appendColumn("I.calculatedAutomatically", "calculatedAutomatically").from(Tables.INDICATOR, "I").where("I.ActivityId").in(activityIds).and("I.dateDeleted IS NULL").orderBy("I.SortOrder");
    Log.info(query.toString());
    query.execute(tx, new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, final SqlResultSet results) {
            Multimap<Integer, FormField> fields = HashMultimap.create();
            for (SqlResultSetRow row : results.getRows()) {
                fields.put(row.getInt("activityId"), createField(row));
            }
            // Have to resolve symbols on a per-form basis
            for (Integer activityId : fields.keySet()) {
                Collection<FormField> activityFields = fields.get(activityId);
                FormSymbolTable symbolTable = new FormSymbolTable(activityFields);
                PartialEvaluator<SiteDTO> evaluator = new PartialEvaluator<>(symbolTable, new SiteFieldReaderFactory());
                List<CalculatedIndicatorReader> readers = Lists.newArrayList();
                for (FormField field : activityFields) {
                    if (field.getType() instanceof CalculatedFieldType) {
                        try {
                            FieldReader<SiteDTO> reader = evaluator.partiallyEvaluate(field);
                            if (reader.getType() instanceof QuantityType) {
                                readers.add(new CalculatedIndicatorReader(field, reader));
                            }
                        } catch (Exception e) {
                            // we don't want to fail whole GetSites command due to invalid expression.
                            Log.error("Failed to evaluate calculated field: " + field + ", expression: " + ((CalculatedFieldType) field.getType()).getExpression(), e);
                        }
                    }
                }
                for (SiteDTO site : siteMap.values()) {
                    for (CalculatedIndicatorReader reader : readers) {
                        reader.read(site);
                    }
                }
            }
            complete.onSuccess(null);
        }
    });
}

Example 3 with SqlResultSetRow

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

the class OldGetSitesHandler method joinEntities.

private Promise<Void> joinEntities(SqlTransaction tx, final Multimap<Integer, SiteDTO> siteMap) {
    final Promise<Void> complete = new Promise<>();
    Log.trace("Starting joinEntities()");
    SqlQuery.select("site.SiteId", "Link.adminEntityId", "e.name", "e.adminLevelId", "e.adminEntityParentId", "x1", "y1", "x2", "y2").from(Tables.SITE).innerJoin(Tables.LOCATION).on("location.LocationId = site.LocationId").innerJoin(Tables.LOCATION_ADMIN_LINK, "Link").on("Link.LocationId = location.LocationId").innerJoin(Tables.ADMIN_ENTITY, "e").on("Link.AdminEntityId = e.AdminEntityId").where("site.SiteId").in(siteMap.keySet()).execute(tx, new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            Log.trace("Received results for joinEntities()");
            Map<Integer, AdminEntityDTO> entities = Maps.newHashMap();
            for (SqlResultSetRow row : results.getRows()) {
                int adminEntityId = row.getInt("adminEntityId");
                AdminEntityDTO entity = entities.get(adminEntityId);
                if (entity == null) {
                    entity = GetAdminEntitiesHandler.toEntity(row);
                    entities.put(adminEntityId, entity);
                }
                for (SiteDTO site : siteMap.get(row.getInt("SiteId"))) {
                    site.setAdminEntity(entity.getLevelId(), entity);
                }
            }
            Log.trace("Done populating results for joinEntities");
            complete.onSuccess(null);
        }
    });
    return complete;
}

Example 4 with SqlResultSetRow

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

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