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

Example 16 with SqlQuery

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

the class OldGetSitesHandler method countQuery.

private SqlQuery countQuery(GetSites command, ExecutionContext context) {
    SqlQuery unioned = unionedQuery(context, command);
    unioned.appendColumn("count(*)", "site_count");
    return unioned;
}

Example 17 with SqlQuery

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

the class OldGetSitesHandler method primaryQuery.

private SqlQuery primaryQuery(ExecutionContext context, GetSites command) {
    SqlQuery query = SqlQuery.select().appendColumn("site.SiteId").appendColumn("(0)", "Linked").appendColumn("activity.ActivityId").appendColumn("activity.name", "ActivityName").appendColumn("db.DatabaseId", "DatabaseId").appendColumn("site.DateCreated", "DateCreated").appendColumn("site.projectId", "ProjectId").appendColumn("project.name", "ProjectName").appendColumn("project.dateDeleted", "ProjectDateDeleted").appendColumn("site.comments", "Comments").appendColumn("site.DateEdited").appendColumn("site.timeEdited", "TimeEdited");
    if (command.isFetchAllReportingPeriods()) {
        query.appendColumn("period.Date1", "Date1").appendColumn("period.Date2", "Date2").appendColumn("period.ReportingPeriodId", "PeriodId");
        query.from(Tables.REPORTING_PERIOD, "period").leftJoin(Tables.SITE, "site").on("site.SiteId=period.SiteId");
        LocalDate filterMinDate = command.getFilter().getEndDateRange().getMinLocalDate();
        LocalDate filterMaxDate = command.getFilter().getEndDateRange().getMaxLocalDate();
        if (filterMinDate != null) {
            query.where("period.Date1").greaterThanOrEqualTo(filterMinDate);
        }
        if (filterMaxDate != null) {
            query.where("period.Date2").lessThanOrEqualTo(filterMaxDate);
        }
    } else {
        query.from(Tables.SITE);
        query.appendColumn("site.Date1", "Date1").appendColumn("site.Date2", "Date2");
    }
    query.whereTrue("site.dateDeleted is null").leftJoin(Tables.ACTIVITY).on("site.ActivityId = activity.ActivityId").leftJoin(Tables.USER_DATABASE, "db").on("activity.DatabaseId = db.DatabaseId").leftJoin(Tables.PARTNER).on("site.PartnerId = partner.PartnerId").leftJoin(Tables.PROJECT).on("site.ProjectId = project.ProjectId");
    if (command.isFetchPartner()) {
        query.appendColumn("partner.PartnerId", "PartnerId").appendColumn("partner.name", "PartnerName");
    }
    if (command.isFetchLocation()) {
        query.appendColumn("location.locationId", "LocationId").appendColumn("location.name", "LocationName").appendColumn("location.axe", "LocationAxe").appendColumn("locationType.name", "LocationTypeName").appendColumn("location.x", "x").appendColumn("location.y", "y").appendColumn("location.workflowStatusId", "workflowStatusId");
    }
    if (locationJoinRequired(command)) {
        query.leftJoin(Tables.LOCATION).on("site.LocationId = location.LocationId").leftJoin(Tables.LOCATION_TYPE, "locationType").on("location.LocationTypeId = locationType.LocationTypeId");
    }
    applyPermissions(query, context);
    applyFilter(query, command);
    Optional<Integer> adminLevelId = adminLevelId(command.getSortInfo().getSortField());
    if (command.isFetchAdminEntities() && adminLevelId.isPresent()) {
        query.appendColumn("derived.name", "adminName");
        query.leftJoin(locationToAdminTable(adminLevelId.get()), "derived").on("derived.locationId = location.LocationId");
    }
    if (command.getFilter().isRestricted(DimensionType.Indicator)) {
        applyPrimaryIndicatorFilter(query, command.getFilter());
    }
    System.out.println(query.sql());
    return query;
}

Example 18 with SqlQuery

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

the class OldGetSitesHandler method linkedQuery.

private SqlQuery linkedQuery(ExecutionContext context, GetSites command) {
    SqlQuery query = SqlQuery.select().appendColumn("DISTINCT site.SiteId", "SiteId").appendColumn("1", "Linked").appendColumn("activity.ActivityId").appendColumn("activity.name", "ActivityName").appendColumn("db.DatabaseId", "DatabaseId").appendColumn("site.DateCreated", "DateCreated").appendColumn("site.projectId", "ProjectId").appendColumn("project.name", "ProjectName").appendColumn("project.dateDeleted", "ProjectDateDeleted").appendColumn("site.comments", "Comments").appendColumn("site.DateEdited").appendColumn("site.timeEdited", "TimeEdited").appendColumn("site.Date1", "Date1").appendColumn("site.Date2", "Date2");
    if (command.isFetchPartner()) {
        query.appendColumn("partner.PartnerId", "PartnerId").appendColumn("partner.name", "PartnerName");
    }
    if (command.isFetchLocation()) {
        query.appendColumn("location.locationId", "LocationId").appendColumn("location.name", "LocationName").appendColumn("location.axe", "LocationAxe").appendColumn("locationType.name", "LocationTypeName").appendColumn("location.x", "x").appendColumn("location.y", "y").appendColumn("location.workflowStatusId", "workflowStatusId");
    }
    if (command.getFilter().isRestricted(DimensionType.Indicator)) {
        /*
             * When filtering by indicators, restructure the query to fetch the
             * results more efficiently
             */
        query.from(Tables.INDICATOR_LINK, "link").innerJoin(Tables.INDICATOR_VALUE, "siv").on("link.SourceIndicatorId = siv.IndicatorId").innerJoin(Tables.REPORTING_PERIOD, "srp").on("siv.ReportingPeriodId = srp.ReportingPeriodId").innerJoin(Tables.SITE, "site").on("srp.SiteId=site.SiteId").innerJoin(Tables.INDICATOR, "di").on("link.DestinationIndicatorId=di.IndicatorId").innerJoin(Tables.ACTIVITY, "activity").on("di.ActivityId=activity.ActivityId").where("link.DestinationIndicatorId").in(command.getFilter().getRestrictions(DimensionType.Indicator));
    } else {
        query.from(Tables.SITE).innerJoin(Tables.INDICATOR, "si").on("si.activityid=site.activityid").innerJoin(Tables.INDICATOR_LINK, "link").on("si.indicatorId=link.sourceindicatorid").innerJoin(Tables.INDICATOR, "di").on("link.destinationIndicatorId=di.indicatorid").leftJoin(Tables.ACTIVITY).on("di.ActivityId = activity.ActivityId");
    }
    query.leftJoin(Tables.USER_DATABASE, "db").on("activity.DatabaseId = db.DatabaseId").leftJoin(Tables.PARTNER).on("site.PartnerId = partner.PartnerId").leftJoin(Tables.PROJECT).on("site.ProjectId = project.ProjectId").whereTrue("site.dateDeleted is null");
    if (locationJoinRequired(command)) {
        query.leftJoin(Tables.LOCATION).on("site.LocationId = location.LocationId").leftJoin(Tables.LOCATION_TYPE, "locationType").on("location.LocationTypeId = locationType.LocationTypeId");
    }
    Optional<Integer> adminLevelId = adminLevelId(command.getSortInfo().getSortField());
    if (command.isFetchAdminEntities() && adminLevelId.isPresent()) {
        query.appendColumn("derived.name", "adminName");
        query.leftJoin(locationToAdminTable(adminLevelId.get()), "derived").on("derived.locationId = location.LocationId");
    }
    applyPermissions(query, context);
    applyFilter(query, command);
    return query;
}

Example 19 with SqlQuery

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

the class OldGetSitesHandler method unionedQuery.

private SqlQuery unionedQuery(ExecutionContext context, GetSites command) {
    SqlQuery primaryQuery = primaryQuery(context, command);
    SqlQuery linkedQuery = linkedQuery(context, command);
    SqlQuery unioned = SqlQuery.select().from(unionAll(primaryQuery, linkedQuery), "u");
    for (Object param : primaryQuery.parameters()) {
        unioned.appendParameter(param);
    }
    for (Object param : linkedQuery.parameters()) {
        unioned.appendParameter(param);
    }
    return unioned;
}

Example 20 with SqlQuery

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

the class CalculatedIndicatorsQuery method execute.

@Override
public void execute(final AsyncCallback<Void> callback) {
    this.callback = callback;
    final SqlQuery query = SqlQuery.selectDistinct().appendColumn("i.indicatorId", "indicatorId").appendColumn("i.name", "indicatorName").appendColumn("i.activityId", "activityId").appendColumn("i.sortOrder", "indicatorOrder").appendColumn("i.aggregation", "aggregation").appendColumn("a.name", "activityName").appendColumn("a.category", "activityCategory").appendColumn("a.sortOrder", "activityOrder").appendColumn("db.DatabaseId", "databaseId").appendColumn("db.name", "databaseName").from(Tables.INDICATOR, "i").leftJoin(Tables.ACTIVITY, "a").on("a.activityId=i.activityId").leftJoin(Tables.USER_DATABASE, "db").on("a.databaseId=db.databaseId").whereTrue("i.calculatedAutomatically=1 and i.Expression is not null");
    Filter filter = this.query.getFilter();
    if (filter.isRestricted(DimensionType.Indicator)) {
        query.where("i.indicatorId").in(filter.getRestrictions(DimensionType.Indicator));
    } else if (filter.isRestricted(DimensionType.Activity)) {
        query.where("i.activityId").in(filter.getRestrictions(DimensionType.Activity));
    } else if (filter.isRestricted(DimensionType.Database)) {
        query.where("a.databaseId").in(filter.getRestrictions(DimensionType.Database));
    } else {
        // too broad
        callback.onSuccess(null);
        return;
    }
    // enforce visibility rules
    query.whereTrue(visibilityRules());
    query.execute(queryContext.getExecutionContext().getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            if (results.getRows().isEmpty()) {
                callback.onSuccess(null);
            } else {
                boolean hasSumAggregation = false;
                for (SqlResultSetRow row : results.getRows()) {
                    LOGGER.finer("row = " + row);
                    int activityId = row.getInt("activityId");
                    int indicatorId = row.getInt("indicatorId");
                    activityIds.add(activityId);
                    indicatorIds.add(indicatorId);
                    activityMap.put(activityId, new EntityCategory(activityId, row.getString("activityName"), row.getInt("activityOrder")));
                    activityCategoryMap.put(activityId, new SimpleCategory(row.getString("activityCategory")));
                    activityToDatabaseMap.put(activityId, new EntityCategory(row.getInt("databaseId"), row.getString("databaseName")));
                    indicatorMap.put(indicatorId, new EntityCategory(indicatorId, row.getString("indicatorName"), row.getInt("indicatorOrder")));
                    indicatorAggregationMap.put(indicatorId, row.getInt("aggregation"));
                    if (!hasSumAggregation) {
                        hasSumAggregation = row.getInt("aggregation") == 0;
                    }
                }
                // set aggregation to Sum for all indicators if at least one indicator has different aggregation
                if (hasSumAggregation) {
                    for (Map.Entry<Integer, Integer> entry : indicatorAggregationMap.entrySet()) {
                        // set to Sum
                        entry.setValue(0);
                    }
                }
                if (queryContext.getCommand().isPivotedBy(DimensionType.AttributeGroup)) {
                    queryAttributeGroups();
                } else {
                    querySites();
                }
            }
        }
    });
}