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

Example 1 with SqlResultSet

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

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

the class OldGetSitesHandler method queryTotalLength.

private Promise<Void> queryTotalLength(SqlTransaction tx, GetSites command, ExecutionContext context, final SiteResult result) {
    final Promise<Void> promise = new Promise<>();
    if (isMySql()) {
        tx.executeSql("SELECT FOUND_ROWS() site_count", new SqlResultCallback() {

            @Override
            public void onSuccess(SqlTransaction tx, SqlResultSet results) {
                result.setTotalLength(results.getRow(0).getInt("site_count"));
                promise.resolve(null);
            }
        });
    } else {
        // otherwise we have to execute the whole thing again
        SqlQuery query = countQuery(command, context);
        query.execute(tx, new SqlResultCallback() {

            @Override
            public void onSuccess(SqlTransaction tx, SqlResultSet results) {
                result.setTotalLength(results.getRow(0).getInt("site_count"));
                promise.resolve(null);
            }
        });
    }
    return promise;
}

Example 3 with SqlResultSet

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

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

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