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

Example 1 with SqlQuery

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

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

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

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

the class OldGetSitesHandler method applyPrimaryIndicatorFilter.

private void applyPrimaryIndicatorFilter(SqlQuery query, Filter filter) {
    SqlQuery subQuery = new SqlQuery().appendColumn("period.SiteId").from(Tables.INDICATOR_VALUE, "iv").leftJoin(Tables.REPORTING_PERIOD, "period").on("iv.ReportingPeriodId=period.ReportingPeriodId").where("iv.IndicatorId").in(filter.getRestrictions(DimensionType.Indicator)).whereTrue("iv.Value IS NOT NULL");
    query.where("site.SiteId").in(subQuery);
}

Example 5 with SqlQuery

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

the class OldGetSitesHandler method applyFilter.

private void applyFilter(SqlQuery query, GetSites command) {
    Filter filter = command.getFilter();
    if (filter != null) {
        if (filter.getRestrictedDimensions() != null && filter.getRestrictedDimensions().size() > 0) {
            query.onlyWhere(" AND (");
            boolean isFirst = true;
            boolean isRestricted = false;
            for (DimensionType type : filter.getRestrictedDimensions()) {
                if (isQueryableType(type)) {
                    addJoint(query, isFirst);
                    isRestricted = true;
                }
                if (type == DimensionType.Activity) {
                    query.onlyWhere("activity.ActivityId").in(filter.getRestrictions(type));
                } else if (type == DimensionType.Database) {
                    query.onlyWhere("activity.DatabaseId").in(filter.getRestrictions(type));
                } else if (type == DimensionType.Partner) {
                    query.onlyWhere("site.PartnerId").in(filter.getRestrictions(type));
                } else if (type == DimensionType.Project) {
                    query.onlyWhere("site.ProjectId").in(filter.getRestrictions(type));
                } else if (type == DimensionType.AdminLevel) {
                    query.onlyWhere("site.LocationId").in(SqlQuery.select("Link.LocationId").from(Tables.LOCATION_ADMIN_LINK, "Link").where("Link.AdminEntityId").in(filter.getRestrictions(type)));
                } else if (type == DimensionType.Site) {
                    query.onlyWhere("site.SiteId").in(filter.getRestrictions(type));
                } else if (type == DimensionType.Attribute) {
                    Set<Integer> attributes = filter.getRestrictions(DimensionType.Attribute);
                    boolean isFirstAttr = true;
                    for (Integer attribute : attributes) {
                        SqlQuery attributefilter = SqlQuery.select().appendColumn("1", "__VAL_EXISTS").from("attributevalue", "av").whereTrue("av.value=1").and("av.SiteId = site.SiteId").where("av.AttributeId").equalTo(attribute);
                        addJoint(query, isFirstAttr);
                        if (isFirstAttr) {
                            isFirstAttr = false;
                        }
                        query.onlyWhere("EXISTS (" + attributefilter.sql() + ") ");
                        query.appendParameter(attribute);
                    }
                } else if (type == DimensionType.Location) {
                    query.onlyWhere("location.locationId").in(filter.getRestrictions(type));
                }
                if (isQueryableType(type) && isFirst) {
                    isFirst = false;
                }
            }
            if (!isRestricted) {
                query.onlyWhere(" 1=1 ");
            }
            query.onlyWhere(")");
        }
        if (!command.isFetchAllReportingPeriods()) {
            // it does not make sense to filter monthly activity by site dates (filter by reporting period instead)
            applyDateRangeFilter("site.Date1", filter.getStartDateRange(), query);
            applyDateRangeFilter("site.Date2", filter.getEndDateRange(), query);
        }
    }
}