package org.activityinfo.legacy.shared.impl;
/*
* #%L
* ActivityInfo Server
* %%
* Copyright (C) 2009 - 2013 UNICEF
* %%
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public
* License along with this program. If not, see
* <http://www.gnu.org/licenses/gpl-3.0.html>.
* #L%
*/
import com.bedatadriven.rebar.sql.client.SqlResultCallback;
import com.bedatadriven.rebar.sql.client.SqlResultSet;
import com.bedatadriven.rebar.sql.client.SqlResultSetRow;
import com.bedatadriven.rebar.sql.client.SqlTransaction;
import com.bedatadriven.rebar.sql.client.query.SqlDialect;
import com.bedatadriven.rebar.sql.client.query.SqlQuery;
import com.bedatadriven.rebar.time.calendar.LocalDate;
import com.extjs.gxt.ui.client.Style.SortDir;
import com.extjs.gxt.ui.client.data.SortInfo;
import com.google.common.base.Functions;
import com.google.common.base.Strings;
import com.google.common.collect.*;
import com.google.gwt.user.client.rpc.AsyncCallback;
import com.google.inject.Inject;
import org.activityinfo.legacy.shared.Log;
import org.activityinfo.legacy.shared.command.DimensionType;
import org.activityinfo.legacy.shared.command.Filter;
import org.activityinfo.legacy.shared.command.GetSites;
import org.activityinfo.legacy.shared.command.result.SiteResult;
import org.activityinfo.legacy.shared.model.*;
import org.activityinfo.model.expr.eval.FieldReader;
import org.activityinfo.model.expr.eval.FormSymbolTable;
import org.activityinfo.model.expr.eval.PartialEvaluator;
import org.activityinfo.model.form.FormField;
import org.activityinfo.model.form.FormFieldType;
import org.activityinfo.model.legacy.CuidAdapter;
import org.activityinfo.model.type.FieldTypeClass;
import org.activityinfo.model.type.FieldValue;
import org.activityinfo.model.type.expr.CalculatedFieldType;
import org.activityinfo.model.type.number.Quantity;
import org.activityinfo.model.type.number.QuantityType;
import org.activityinfo.promise.Promise;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class GetSitesHandler implements CommandHandlerAsync<GetSites, SiteResult> {
private final SqlDialect dialect;
@Inject
public GetSitesHandler(SqlDialect dialect) {
super();
this.dialect = dialect;
}
@Override
public void execute(final GetSites command,
final ExecutionContext context,
final AsyncCallback<SiteResult> callback) {
Log.trace("Entering execute()");
doQuery(command, context, callback);
}
private void doQuery(final GetSites command,
final ExecutionContext context,
final AsyncCallback<SiteResult> callback) {
// in order to pull in the linked queries, we want to
// to create two queries that we union together.
// for performance reasons, we want to apply all of the joins
// and filters on both parts of the union query
SqlQuery unioned;
if(command.isFetchLinks()) {
unioned = unionedQuery(context, command);
unioned.appendAllColumns();
} else {
unioned = primaryQuery(context, command);
}
if (isMySql() && command.getLimit() >= 0) {
// with this feature, MySQL will keep track of the total
// number of rows regardless of our limit statement.
// This way we don't have to execute the query twice to
// get the total count
//
// unfortunately, this is not available on sqlite
unioned.appendKeyword("SQL_CALC_FOUND_ROWS");
}
applySort(unioned, command.getSortInfo());
applyPaging(unioned, command);
final Multimap<Integer, SiteDTO> siteMap = HashMultimap.create();
final List<SiteDTO> sites = new ArrayList<SiteDTO>();
final Map<Integer, SiteDTO> reportingPeriods = Maps.newHashMap();
final SiteResult result = new SiteResult(sites);
result.setOffset(command.getOffset());
Log.trace("About to execute primary query: " + unioned.toString());
unioned.execute(context.getTransaction(), new SqlResultCallback() {
@Override
public void onSuccess(SqlTransaction tx, SqlResultSet results) {
Log.trace("Primary query returned, starting to add to map");
for (SqlResultSetRow row : results.getRows()) {
SiteDTO site = toSite(command, row);
sites.add(site);
siteMap.put(site.getId(), site);
if(command.isFetchAllReportingPeriods()) {
reportingPeriods.put(row.getInt("PeriodId"), site);
}
}
Log.trace("Finished adding to map");
List<Promise<Void>> queries = Lists.newArrayList();
if (command.getLimit() <= 0) {
result.setTotalLength(results.getRows().size());
} else {
queries.add(queryTotalLength(tx, command, context, result));
}
if (!sites.isEmpty()) {
if (command.isFetchAdminEntities()) {
queries.add(joinEntities(tx, siteMap));
}
if (command.isFetchAttributes()) {
queries.add(joinAttributeValues(command, tx, siteMap));
}
if (command.fetchAnyIndicators()) {
queries.add(joinIndicatorValues(command, tx, siteMap, reportingPeriods));
}
}
Promise.waitAll(queries).then(Functions.constant(result)).then(callback);
}
});
}
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;
}
private String unionAll(SqlQuery primaryQuery, SqlQuery linkedQuery) {
StringBuilder union = new StringBuilder();
union.append("(").append(primaryQuery.sql()).append(" UNION ALL ").append(linkedQuery.sql()).append(")");
return union.toString();
}
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");
} 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");
}
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.getFilter());
if (command.getFilter().isRestricted(DimensionType.Indicator)) {
applyPrimaryIndicatorFilter(query, command.getFilter());
}
System.out.println(query.sql());
return query;
}
private boolean locationJoinRequired(GetSites command) {
return command.isFetchLocation() || command.getFilter().isRestricted(DimensionType.Location);
}
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");
}
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");
}
applyPermissions(query, context);
applyFilter(query, command.getFilter());
return query;
}
private void applyPaging(final SqlQuery query, GetSites command) {
if (command.getOffset() > 0 || command.getLimit() > 0) {
query.setLimitClause(dialect.limitClause(command.getOffset(), command.getLimit()));
}
}
private void applyPermissions(final SqlQuery query, ExecutionContext context) {
// Apply permissions if we are on the server, otherwise permissions have
// already been taken into account during synchronization
if (context.isRemote()) {
query.whereTrue("activity.DateDeleted IS NULL").and("db.DateDeleted IS NULL");
query.whereTrue("(db.OwnerUserId = ? OR " +
"db.DatabaseId in " +
"(SELECT p.DatabaseId from userpermission p where p.UserId = ? and p.AllowViewAll) or " +
"db.DatabaseId in " +
"(select p.DatabaseId from userpermission p where (p.UserId = ?) and p.AllowView and p" +
".PartnerId = site.PartnerId) " +
" OR (select count(*) from activity pa where pa.published>0 and pa.ActivityId = site" +
".ActivityId) > 0 )");
query.appendParameter(context.getUser().getId());
query.appendParameter(context.getUser().getId());
query.appendParameter(context.getUser().getId());
}
}
private void applySort(SqlQuery query, SortInfo sortInfo) {
if (sortInfo.getSortDir() != SortDir.NONE) {
String field = sortInfo.getSortField();
boolean ascending = sortInfo.getSortDir() == SortDir.ASC;
if (field.equals("date1")) {
query.orderBy("Date1", ascending);
} else if (field.equals("date2")) {
query.orderBy("Date2", ascending);
} else if (field.equals("locationName")) {
query.orderBy("LocationName", ascending);
} else if (field.equals("partner")) {
query.orderBy("PartnerName", ascending);
} else if (field.equals("locationAxe")) {
query.orderBy("LocationAxe", ascending);
} else if (field.startsWith(IndicatorDTO.PROPERTY_PREFIX)) {
int indicatorId = IndicatorDTO.indicatorIdForPropertyName(field);
query.orderBy(SqlQuery.selectSingle("SUM(v.Value)")
.from(Tables.INDICATOR_VALUE, "v")
.leftJoin(Tables.REPORTING_PERIOD, "r")
.on("v.ReportingPeriodId=r.ReportingPeriodId")
.whereTrue("v.IndicatorId=" + indicatorId)
.and("r.SiteId=u.SiteId"), ascending);
} else if (field.equals("DateEdited")) {
query.orderBy("DateEdited", ascending);
} else {
Log.error("Unimplemented sort on GetSites: '" + field + "");
}
}
}
private void applyFilter(SqlQuery query, Filter filter) {
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, filter.isLenient(), 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, filter.isLenient(), 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(")");
}
LocalDate filterMinDate = filter.getDateRange().getMinLocalDate();
if (filterMinDate != null) {
query.where("site.Date2").greaterThanOrEqualTo(filterMinDate);
}
LocalDate filterMaxDate = filter.getDateRange().getMaxLocalDate();
if (filterMaxDate != null) {
query.where("site.Date2").lessThanOrEqualTo(filterMaxDate);
}
}
}
private boolean isQueryableType(DimensionType type) {
return (type == DimensionType.Activity ||
type == DimensionType.Database ||
type == DimensionType.Partner ||
type == DimensionType.Project ||
type == DimensionType.AdminLevel ||
type == DimensionType.Attribute ||
type == DimensionType.Site ||
type == DimensionType.Location);
}
private void addJoint(SqlQuery query, boolean lenient, boolean first) {
if (!first) {
if (lenient) {
query.onlyWhere(" OR ");
} else {
query.onlyWhere(" AND ");
}
}
}
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);
}
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;
}
private SqlQuery countQuery(GetSites command, ExecutionContext context) {
SqlQuery unioned = unionedQuery(context, command);
unioned.appendColumn("count(*)", "site_count");
return unioned;
}
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;
}
private boolean weAreFetchingAllSitesForAnActivityAndThereAreNoLinkedSites(
GetSites command, Multimap<Integer,SiteDTO> siteMap) {
// are we limiting the number of rows to return?
if(command.getLimit() >= 0) {
return false;
}
// are we filtering on a SINGLE dimension??
Filter filter = command.getFilter();
if( filter.getRestrictedDimensions().size() != 1 ) {
return false;
}
// is that dimension the Activity dimension?
if( !filter.getRestrictedDimensions().contains(DimensionType.Activity)) {
return false;
}
// are there any linked sites?
if(command.isFetchLinks()) {
for (SiteDTO site : siteMap.values()) {
if(site.isLinked()) {
return false;
}
}
}
// RETURN ALL SITES for filtered Activity
return true;
}
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) {
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;
}
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) {
List<FormField> fields = Lists.newArrayList();
for(SqlResultSetRow row : results.getRows()) {
fields.add(createField(row));
}
FormSymbolTable symbolTable = new FormSymbolTable(fields);
PartialEvaluator<SiteDTO> evaluator = new PartialEvaluator<>(symbolTable, new SiteFieldReaderFactory());
List<CalculatedIndicatorReader> readers = Lists.newArrayList();
for(FormField field : fields) {
if(field.getType() instanceof CalculatedFieldType) {
FieldReader<SiteDTO> reader = evaluator.partiallyEvaluate(field);
if(reader.getType() instanceof QuantityType) {
readers.add(new CalculatedIndicatorReader(field, reader));
}
}
}
for(SiteDTO site : siteMap.values()) {
for(CalculatedIndicatorReader reader : readers) {
reader.read(site);
}
}
complete.onSuccess(null);
}
});
}
private FormField createField(SqlResultSetRow rs) {
IndicatorDTO indicator = new IndicatorDTO();
indicator.setId(rs.getInt("indicatorId"));
indicator.setName("indicatorName");
indicator.setTypeId(rs.getString("type"));
indicator.setExpression(rs.getString("expression"));
indicator.setSkipExpression(rs.getString("skipExpression"));
indicator.setNameInExpression(rs.getString("code"));
indicator.setCalculatedAutomatically(rs.getBoolean("calculatedAutomatically"));
indicator.setUnits(rs.getString("units"));
return indicator.asFormField();
}
private static class CalculatedIndicatorReader {
private String propertyName;
private FieldReader<SiteDTO> reader;
private CalculatedIndicatorReader(FormField field, FieldReader<SiteDTO> reader) {
this.propertyName = IndicatorDTO.getPropertyName(CuidAdapter.getLegacyIdFromCuid(field.getId()));
this.reader = reader;
}
public void read(SiteDTO site) {
FieldValue value = reader.readField(site);
if(value instanceof Quantity) {
site.set(propertyName, ((Quantity) value).getValue());
}
}
}
private Promise<Void> joinAttributeValues(GetSites command, SqlTransaction tx, final Multimap<Integer, SiteDTO> siteMap) {
Log.trace("Starting joinAttributeValues() ");
final Promise<Void> complete = new Promise<>();
SqlQuery sqlQuery = SqlQuery.select()
.appendColumn("v.AttributeId", "attributeId")
.appendColumn("a.Name", "attributeName")
.appendColumn("v.Value", "value")
.appendColumn("v.SiteId", "siteId")
.appendColumn("g.name", "groupName")
.from(Tables.ATTRIBUTE_VALUE, "v")
.leftJoin(Tables.ATTRIBUTE, "a")
.on("v.AttributeId = a.AttributeId")
.leftJoin(Tables.ATTRIBUTE_GROUP, "g")
.on("a.AttributeGroupId=g.AttributeGroupId")
.whereTrue("v.Value=1")
.orderBy("groupName, attributeName");
if(weAreFetchingAllSitesForAnActivityAndThereAreNoLinkedSites(command, siteMap)) {
sqlQuery.leftJoin(Tables.ATTRIBUTE_GROUP_IN_ACTIVITY, "ag")
.on("ag.attributeGroupId=g.attributeGroupId")
.where("ag.ActivityId").in(command.getFilter().getRestrictions(DimensionType.Activity));
} else {
sqlQuery.where("v.SiteId").in(siteMap.keySet());
}
sqlQuery.execute(tx, new SqlResultCallback() {
@Override
public void onSuccess(SqlTransaction tx, SqlResultSet results) {
Log.trace("Received results for joinAttributeValues() ");
for (SqlResultSetRow row : results.getRows()) {
int attributeId = row.getInt("attributeId");
boolean value = row.getBoolean("value");
String groupName = row.getString("groupName");
String attributeName = row.getString("attributeName");
for (SiteDTO site : siteMap.get(row.getInt("siteId"))) {
site.setAttributeValue(attributeId, value);
if (value) {
site.addDisplayAttribute(groupName, attributeName);
}
}
}
Log.trace("Done populating results for joinAttributeValues()");
complete.onSuccess(null);
}
});
return complete;
}
private SiteDTO toSite(GetSites query, SqlResultSetRow row) {
SiteDTO model = new SiteDTO();
model.setId(row.getInt("SiteId"));
model.setLinked(row.getBoolean("Linked"));
model.setActivityId(row.getInt("ActivityId"));
model.setDate1(row.getDate("Date1"));
model.setDate2(row.getDate("Date2"));
model.setDateCreated(row.getDate("DateCreated"));
model.setTimeEdited(row.getDouble("TimeEdited"));
if(query.isFetchLocation()) {
model.setLocationId(row.getInt("LocationId"));
model.setLocationName(row.getString("LocationName"));
model.setLocationAxe(row.getString("LocationAxe"));
if (!row.isNull("x") && !row.isNull("y")) {
model.setX(row.getDouble("x"));
model.setY(row.getDouble("y"));
}
}
if(query.isFetchPartner()) {
PartnerDTO partner = new PartnerDTO();
partner.setId(row.getInt("PartnerId"));
partner.setName(row.getString("PartnerName"));
model.setPartner(partner);
}
if (!row.isNull("ProjectId") && row.isNull("ProjectDateDeleted")) {
ProjectDTO project = new ProjectDTO();
project.setId(row.getInt("ProjectId"));
project.setName(row.getString("ProjectName"));
model.setProject(project);
}
if (query.isFetchAllReportingPeriods()) {
model.set("reportingPeriodId", row.get("PeriodId"));
}
model.setComments(row.getString("Comments"));
return model;
}
private boolean isMySql() {
return dialect.isMySql();
}
}