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

Example 16 with SqlResultSetRow

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

the class GetAdminEntitiesHandler method execute.

@Override
public void execute(GetAdminEntities cmd, ExecutionContext context, final AsyncCallback<AdminEntityResult> callback) {
    SqlQuery query = SqlQuery.select("AdminEntity.adminEntityId", "AdminEntity.name", "AdminEntity.adminLevelId", "AdminEntity.adminEntityParentId", "x1", "y1", "x2", "y2").from(Tables.ADMIN_ENTITY, "AdminEntity").whereTrue("not AdminEntity.deleted");
    query.orderBy("AdminEntity.name");
    if (cmd.getLevelId() != null) {
        query.where("AdminEntity.AdminLevelId").equalTo(cmd.getLevelId());
    } else {
        query.leftJoin(Tables.ADMIN_LEVEL, "level").on("AdminEntity.AdminLevelID=level.AdminLevelId");
        query.appendColumn("level.name", "levelName");
    }
    if (cmd.getEntityIds() != null && !cmd.getEntityIds().isEmpty()) {
        query.where("AdminEntity.AdminEntityId").in(cmd.getEntityIds());
    }
    if (cmd.getParentId() != null) {
        if (cmd.getParentId() == GetAdminEntities.ROOT) {
            query.where("AdminEntity.AdminEntityParentId IS NULL");
        } else {
            query.where("AdminEntity.AdminEntityParentId").equalTo(cmd.getParentId());
        }
    }
    if (cmd.getFilter() != null) {
        Filter filter = cmd.getFilter();
        if (filter.isRestricted(DimensionType.Activity) || filter.isRestricted(DimensionType.Database) || filter.isRestricted(DimensionType.Indicator)) {
            SqlQuery subQuery = SqlQuery.select("link.AdminEntityId").from(Tables.SITE, "site").leftJoin(Tables.LOCATION, "Location").on("Location.LocationId = site.LocationId").leftJoin(Tables.LOCATION_ADMIN_LINK, "link").on("link.LocationId = Location.LocationId");
            if (filter.isRestricted(DimensionType.Activity)) {
                subQuery.where("site.ActivityId").in(filter.getRestrictions(DimensionType.Activity));
            }
            if (filter.isRestricted(DimensionType.Database)) {
                subQuery.leftJoin(Tables.ACTIVITY, "activity").on("site.ActivityId=activity.ActivityId").where("activity.DatabaseId").in(filter.getRestrictions(DimensionType.Database));
            }
            if (filter.isRestricted(DimensionType.Indicator)) {
                subQuery.leftJoin(Tables.REPORTING_PERIOD, "rp").on("site.siteId=rp.SiteId").leftJoin(Tables.INDICATOR_VALUE, "iv").on("iv.reportingPeriodId=rp.reportingPeriodId").where("iv.indicatorId").in(filter.getRestrictions(DimensionType.Indicator));
            }
            query.where("AdminEntity.AdminEntityId").in(subQuery);
        }
    }
    if (cmd.getFilter() != null && cmd.getFilter().isRestricted(DimensionType.AdminLevel)) {
        if (cmd.getLevelId() == null) {
            query.where("AdminEntityId").in(cmd.getFilter().getRestrictions(DimensionType.AdminLevel));
        } else {
            SqlQuery subQuery = SqlQuery.select("adminEntityId").from(Tables.ADMIN_ENTITY, "AdminEntity").where("AdminLevelId").equalTo(cmd.getLevelId()).where("AdminEntityId").in(cmd.getFilter().getRestrictions(DimensionType.AdminLevel));
            query.where("AdminEntity.AdminEntityId").in(subQuery);
        }
    }
    query.execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            final List<AdminEntityDTO> entities = new ArrayList<AdminEntityDTO>();
            Set<String> names = Sets.newHashSet();
            Set<String> duplicates = Sets.newHashSet();
            for (SqlResultSetRow row : results.getRows()) {
                AdminEntityDTO entity = toEntity(row);
                if (!names.add(entity.getName())) {
                    duplicates.add(entity.getName());
                }
                entities.add(entity);
            }
            for (int i = 0; i != entities.size(); ++i) {
                if (duplicates.contains(entities.get(i).getName())) {
                    String levelName = results.getRow(i).getString("levelName");
                    entities.get(i).setName(entities.get(i).getName() + " [" + levelName + "]");
                }
            }
            callback.onSuccess(new AdminEntityResult(entities));
        }
    });
}

Example 17 with SqlResultSetRow

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

the class GetSiteAttachmentsHandler method execute.

@Override
public void execute(GetSiteAttachments command, ExecutionContext context, final AsyncCallback<SiteAttachmentResult> callback) {
    dtos = new ArrayList<SiteAttachmentDTO>();
    SqlQuery.selectAll().from(Tables.SITE_ATTACHMENT, "s").where("s.siteid").equalTo(command.getSiteId()).execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            for (SqlResultSetRow row : results.getRows()) {
                SiteAttachmentDTO dto = new SiteAttachmentDTO();
                dto.setSiteId(row.getInt("siteid"));
                dto.setBlobId(row.getString("blobid"));
                dto.setFileName(row.getString("filename"));
                dto.setUploadedBy(row.getInt("uploadedby"));
                dto.setBlobSize(row.getInt("blobsize"));
                dto.setContentType(row.getString("contenttype"));
                dtos.add(dto);
            }
            callback.onSuccess(new SiteAttachmentResult(dtos));
        }
    });
}

Example 18 with SqlResultSetRow

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

the class UpdateMonthlyReportsAsync method queryPeriodMap.

private Promise<Map<Month, Integer>> queryPeriodMap(UpdateMonthlyReports command, ExecutionContext context) {
    final Promise<Map<Month, Integer>> promise = new Promise<>();
    SqlQuery.select("reportingPeriodId", "Date2").from(Tables.REPORTING_PERIOD, "rp").where("siteId").equalTo(command.getSiteId()).execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(SqlTransaction tx, SqlResultSet results) {
            Map<Month, Integer> periodMap = Maps.newHashMap();
            for (SqlResultSetRow row : results.getRows()) {
                Date endDate = row.getDate("Date2");
                Month month = Month.of(endDate);
                periodMap.put(month, row.getInt("reportingPeriodId"));
            }
            promise.resolve(periodMap);
        }
    });
    return promise;
}

Example 19 with SqlResultSetRow

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

the class GetReportModelHandler method loadMetadataAndCallback.

private void loadMetadataAndCallback(final GetReportModel cmd, final ExecutionContext context, final ReportDTO reportDTO, final AsyncCallback<ReportDTO> callback) {
    final int userId = context.getUser().getId();
    SqlQuery mySubscriptions = SqlQuery.selectAll().from("reportsubscription").where("userId").equalTo(userId);
    SqlQuery myDatabases = SqlQuery.selectSingle("d.databaseid").from("userdatabase", "d").leftJoin(SqlQuery.selectAll().from(Tables.USER_PERMISSION, "UserPermission").where("UserPermission.UserId").equalTo(userId), "p").on("p.DatabaseId = d.DatabaseId").where("d.ownerUserId").equalTo(userId).or("p.AllowView").equalTo(1);
    SqlQuery.select().appendColumn("r.reportTemplateId", "reportId").appendColumn("r.title", "title").appendColumn("r.ownerUserId", "ownerUserId").appendColumn("o.name", "ownerName").appendColumn("s.dashboard", "dashboard").appendColumn("s.emaildelivery", "emaildelivery").appendColumn("s.emailday", "emailday").appendColumn(SqlQuery.selectSingle("max(defaultDashboard)").from("reportvisibility", "v").where("v.databaseId").in(myDatabases).whereTrue("v.reportid = r.reportTemplateId"), "defaultDashboard").from("reporttemplate", "r").leftJoin("userlogin o").on("o.userid = r.ownerUserId").leftJoin(mySubscriptions, "s").on("r.reportTemplateId = s.reportId").where("r.ownerUserId").equalTo(userId).where("r.reportTemplateId").equalTo(cmd.getReportId()).execute(context.getTransaction(), new SqlResultCallback() {

        @Override
        public void onSuccess(final SqlTransaction tx, final SqlResultSet results) {
            List<ReportMetadataDTO> dtos = Lists.newArrayList();
            for (SqlResultSetRow row : results.getRows()) {
                ReportMetadataDTO dto = new ReportMetadataDTO();
                dto.setId(row.getInt("reportId"));
                dto.setAmOwner(row.getInt("ownerUserId") == userId);
                dto.setOwnerName(row.getString("ownerName"));
                dto.setTitle(row.getString("title"));
                dto.setEditAllowed(dto.getAmOwner());
                if (!row.isNull("emaildelivery")) {
                    dto.setEmailDelivery(EmailDelivery.valueOf(row.getString("emaildelivery")));
                }
                if (row.isNull("emailday")) {
                    dto.setDay(1);
                } else {
                    dto.setDay(row.getInt("emailday"));
                }
                if (row.isNull("dashboard")) {
                    // inherited from database-wide visibility
                    dto.setDashboard(!row.isNull("defaultDashboard") && row.getBoolean("defaultDashboard"));
                } else {
                    dto.setDashboard(row.getBoolean("dashboard"));
                }
                dtos.add(dto);
            }
            if (dtos.size() == 0) {
                ReportMetadataDTO dummy = new ReportMetadataDTO();
                dummy.setId(cmd.getReportId());
                dtos.add(dummy);
            }
            // there should be only one result
            reportDTO.setReportMetadataDTO(dtos.get(0));
            // exit handler with both the report and metadata objects
            // filled
            memcache(cmd, reportDTO);
            callback.onSuccess(reportDTO);
        }
    });
}