Adding Method to Retrieve Data
Add the below method to EquipWorkOrderDC.java. This method retrieves aggregated work order data from the F4801 in EnterpriseOne and formats the results. The first part creates two data service aggregation requests to summarize estimated and actual hours for in-process and completed work orders. Once the responses are received from the AIS Server, they are processed separately to add the aggregated sum amounts to the two lists bound to the DVT bar chart components. See Executing AIS Calls for Retrieving Data for additional details on data service aggregation requests.
private void retrieveData(String chartType)
{
// For demo purposes, only display the first ten data entries in either chart.
int understatedDataCount = 0;
int overstatedDataCount = 0;
// Clear chart data lists before adding new data items.
understatedChartData.clear();
overstatedChartData.clear();
try
{
// Create F4801 aggregation data request to sum actual and estimated hours for
// completed work orders, ordered by equipment, assigned to, or supervisor.
DataRequest f4801DataReq1 = new DataRequest(loginEnv);
f4801DataReq1.setDataServiceType(DataRequest.TYPE_AGGREGATION);
f4801DataReq1.setTargetName("F4801");
f4801DataReq1.setTargetType(DataRequest.TARGET_TABLE);
// Sum estimated and actual hours.
AggregationInfo sumHoursAgg1 = new AggregationInfo(loginEnv);
sumHoursAgg1.addAggregationColumn(ACTUAL_HOURS_ALIAS, AggregationType.AGG_TYPE_SUM());
sumHoursAgg1.addAggregationColumn(EST_HOURS_ALIAS, AggregationType.AGG_TYPE_SUM());
// Group data according to user selection.
if (chartType.equals(GROUP_BY_ASSIGNED_TO))
{
sumHoursAgg1.addAggregationGroupBy(ASSIGNED_TO_ALIAS);
sumHoursAgg1.addAggregationOrderBy(ASSIGNED_TO_ALIAS, OrderByDirection.ORDER_DIRECT_ASCENDING());
}
else if (chartType.equals(GROUP_BY_SUPERVISOR))
{
sumHoursAgg1.addAggregationGroupBy(SUPERVISOR_ALIAS);
sumHoursAgg1.addAggregationOrderBy(SUPERVISOR_ALIAS, OrderByDirection.ORDER_DIRECT_ASCENDING());
}
else
{
sumHoursAgg1.addAggregationGroupBy(EQUIP_NUMBER_ALIAS);
sumHoursAgg1.addAggregationOrderBy(EQUIP_NUMBER_ALIAS, OrderByDirection.ORDER_DIRECT_ASCENDING());
}
// Add aggregation to data request.
f4801DataReq1.setAggregation(sumHoursAgg1);
// Create query to retrieve F4801 data where actual hours are greater than zero and
// completion percentage is 100.00.
Query completedWOQuery = new Query(loginEnv);
completedWOQuery.setAutoFind(true);
completedWOQuery.setMatchType(Query.MATCH_ALL);
// Add query conditions.
completedWOQuery.addNumberCondition("F4801.HRSA", NumericOperator.GREATER(), new BigDecimal(0.00));
completedWOQuery.addNumberCondition("F4801.PC", NumericOperator.EQUAL(), new BigDecimal(100.00));
// Add Query to data request.
f4801DataReq1.setQuery(completedWOQuery);
// Create F4801 aggregation data request to sum actual and estimated hours for
// in process work orders, ordered by equipment, assigned to, or supervisor.
DataRequest f4801DataReq2 = new DataRequest(loginEnv);
f4801DataReq2.setDataServiceType(DataRequest.TYPE_AGGREGATION);
f4801DataReq2.setTargetName("F4801");
f4801DataReq2.setTargetType(DataRequest.TARGET_TABLE);
// Sum estimated and actual hours.
AggregationInfo sumHoursAgg2 = new AggregationInfo(loginEnv);
sumHoursAgg2.addAggregationColumn(ACTUAL_HOURS_ALIAS, AggregationType.AGG_TYPE_SUM());
sumHoursAgg2.addAggregationColumn(EST_HOURS_ALIAS, AggregationType.AGG_TYPE_SUM());
// Group data according to user selection.
if (chartType.equals(GROUP_BY_ASSIGNED_TO))
{
logger.finest("**** Grouping data by Assigned To (ANP) field *****");
sumHoursAgg2.addAggregationGroupBy(ASSIGNED_TO_ALIAS);
sumHoursAgg2.addAggregationOrderBy(ASSIGNED_TO_ALIAS, OrderByDirection.ORDER_DIRECT_ASCENDING());
}
else if (chartType.equals(GROUP_BY_SUPERVISOR))
{
logger.finest("**** Grouping data by Supervisor (ANPA) field *****");
sumHoursAgg2.addAggregationGroupBy(SUPERVISOR_ALIAS);
sumHoursAgg2.addAggregationOrderBy(SUPERVISOR_ALIAS, OrderByDirection.ORDER_DIRECT_ASCENDING());
}
else
{
logger.finest("**** Grouping data by Equipment Number (NUMB) field *****");
sumHoursAgg2.addAggregationGroupBy(EQUIP_NUMBER_ALIAS);
sumHoursAgg2.addAggregationOrderBy(EQUIP_NUMBER_ALIAS, OrderByDirection.ORDER_DIRECT_ASCENDING());
}
// Add aggregation to second data request.
f4801DataReq2.setAggregation(sumHoursAgg2);
// Create query to retrieve F4801 data where actual hours are greater than zero and
// completion percentage is less than 100.00.
Query inProcessWOQuery = new Query(loginEnv);
inProcessWOQuery.setAutoFind(true);
inProcessWOQuery.setMatchType(Query.MATCH_ALL);
// Add query conditions.
inProcessWOQuery.addNumberCondition("F4801.HRSA", NumericOperator.GREATER(), new BigDecimal(0.00));
inProcessWOQuery.addNumberCondition("F4801.PC", NumericOperator.LESS(), new BigDecimal(100.00));
// Add query to second data request.
f4801DataReq2.setQuery(inProcessWOQuery);
// Batch data requests.
BatchDataRequest batchDataRequest = new BatchDataRequest(loginEnv);
batchDataRequest.getDataRequests().add(f4801DataReq1);
batchDataRequest.getDataRequests().add(f4801DataReq2);
// Execute data service request
logger.finest("***** Executing Data Aggregation Service - Start *****");
String response = JDERestServiceProvider.jdeRestServiceCall(loginEnv, batchDataRequest, JDERestServiceProvider.POST_METHOD, JDERestServiceProvider.DATA_SERVICE_URI);
logger.finest("***** Executing Data Aggregation Service - Complete *****");
if (response != null)
{
// Process data service responses.
ArrayNode array1 = AggregationResponseHelper.getAggregateValuesArrayBatch(response, "F4801", 0);
for (JsonNode node : array1)
{
JsonNode groupByInfo = node.get("HRSA_SUM");
Double actualHours = groupByInfo.asDouble();
JsonNode groupBy2 = node.get("HRSO_SUM");
Double estHours = groupBy2.asDouble();
// Build list of ChartDataItem objects for complete work order chart.
// The group by value is assigned as both the group and series value.
// The difference in actual and estimated sum amounts is x value.
Double result = actualHours - estHours;
if (result > 0.0)
{
if (understatedDataCount < 10)
{
// Create entry for the bar chart.
understatedDataCount++;
ChartDataItem dataItem;
if (chartType.equals(GROUP_BY_ASSIGNED_TO))
{
// Get Assigned To value.
String assignedTo = node.get(AggregationResponseHelper.GROUP_BY).get(ASSIGNED_TO_ALIAS).asText().trim();
dataItem = new ChartDataItem(assignedTo, assignedTo, result);
}
else if (chartType.equals(GROUP_BY_SUPERVISOR))
{
// Get Supervisor value.
String supervisor = node.get(AggregationResponseHelper.GROUP_BY).get(SUPERVISOR_ALIAS).asText().trim();
dataItem = new ChartDataItem(supervisor, supervisor, result);
}
else
{
// Get equipment number value.
String equipment = node.get(AggregationResponseHelper.GROUP_BY).get(EQUIP_NUMBER_ALIAS).asText().trim();
dataItem = new ChartDataItem(equipment, equipment, result);
}
understatedChartData.add(dataItem);
}
else
{
// Stop processing the result set.
break;
}
}
}
// Process sum results for work orders that are not yet complete.
ArrayNode array2 = AggregationResponseHelper.getAggregateValuesArrayBatch(response, "F4801", 1);
for (JsonNode node : array2)
{
JsonNode groupByInfo = node.get("HRSA_SUM");
Double actualHours = groupByInfo.asDouble();
JsonNode groupBy2 = node.get("HRSO_SUM");
Double estHours = groupBy2.asDouble();
// Build list of ChartDataItem objects for in process Work order chart.
// The group by value is assigned as both the group and series value.
// The difference in actual and estimated sum amounts is x value.
Double result = actualHours - estHours;
if (result > 0.0)
{
if (overstatedDataCount < 10)
{
// Create entry for the bar chart.
overstatedDataCount++;
ChartDataItem dataItem;
if (chartType.equals(GROUP_BY_ASSIGNED_TO))
{
// Get Assigned To value.
String assignedTo = node.get(AggregationResponseHelper.GROUP_BY).get(ASSIGNED_TO_ALIAS).asText().trim();
dataItem = new ChartDataItem(assignedTo, assignedTo, result);
}
else if (chartType.equals(GROUP_BY_SUPERVISOR))
{
// Get Supervisor value.
String supervisor = node.get(AggregationResponseHelper.GROUP_BY).get(SUPERVISOR_ALIAS).asText().trim();
dataItem = new ChartDataItem(supervisor, supervisor, result);
}
else
{
// Get equipment number value.
String equipment = node.get(AggregationResponseHelper.GROUP_BY).get(EQUIP_NUMBER_ALIAS).asText().trim();
dataItem = new ChartDataItem(equipment, equipment, result);
}
overstatedChartData.add(dataItem);
}
else
{
// Stop processing the result set.
break;
}
}
}
// If either chart's data set is empty, add a blank data item to the list, so blank charts
// continue to display on the page.
if (understatedChartData.isEmpty())
{
logger.finest("***** No chart data. Adding empty value to chart data list. *****");
understatedChartData.add(new ChartDataItem(".", "", 0.0));
}
if (overstatedChartData.isEmpty())
{
logger.finest("***** No chart data. Adding empty value to chart data list. *****");
overstatedChartData.add(new ChartDataItem(".", "", 0.0));
}
}
else
{
logger.finest("***** Data Service Response is null. No data to process. *****");
}
}
catch (CapabilityException e)
{
processErrorException(e);
}
catch (JDERestServiceException e)
{
processErrorException(JDERestServiceProvider.handleServiceException(e));
}
catch (Exception e)
{
processErrorException(e);
}
}