Outputs in Repeating Sections

Sometimes you need to compute distinct output values for each of the elements of a variable-length data series. This is what we did before, in the customer rating example, where the application loops of the customers itself. There are cases, however, where doing the loop yourself severely limits the range of computations your users can formulate. Mainly this is because they then cannot aggregate over the whole series. For this case, AFC supports per-element outputs in repeating sections.

Sample Spreadsheet

For example, consider an application that must compute the individual yearly bonus amounts for each employee, given the total bonus amount for the entire company and some base data about each employee’s performance. Typically, such a computation will first compute some sort of overall performance indicator for each employee, given his or her base data. It will then sum this indicator across the company, and finally distribute the bonus to each employee proportionally to his or her individual performance.

So: given the total bonus amount, compute the individual bonus amounts for all employees. As (rather simplistic) base performance data, we will pass in the base salary and hours overtime for each employee. The sample spreadsheet shown below computes the total salary, including overtime at a fixed rate, as the employee performance indicator. It then computes each employee’s share of the bonus to be proportional to his or her share of the total salary of all employees (which is computed in the spreadsheet):

	A	B	C	D	E	F
1	Name	Base salary	Overtime	Total	Bonus %	Bonus amt
2	Ann	$5,600.00 (BaseSalary)	20 (HoursOvertime)	$6,600.00 =B2+C2*B$6	36% =D2/D$5	$7,232.88 =B$7*E2 (BonusAmount)
3	Beth	$5,400.00	15	$6,150.00 =B3+C3*B$6	34% =D3/D$5	$6,739.73 =B$7*E3
4	Charlie	$5,500.00	0	$5,500.00 =B4+C4*B$6	30% =D4/D$5	$6,027.40 =B$7*E4
5	Total			$18,250.00 =SUM(D2:D4)
6	Overtime rate	$50.00 (OvertimeSalaryPerHour)
7	Bonus total	$20,000.00 (BonusTotal)

A2:F4 (Employees)

To consider:

• The bonus total and fixed overtime rate (salary per hour) are global inputs.
• The base salary and hours overtime are inputs per employee.
• The final bonus amount is an output per employee.
• The employee name is not bound. It simply enhances the expressiveness of the example spreadsheet.
• Cell D5 is an intermediate sum over the repeating section.
• Cells E2:E4 use this intermediate sum again, within the section.

Note that in scenarios like this one, where an outer aggregation over a possibly large repeating section is reused within the section, the mandatory caching of values pays off quickly.

Interfaces

The following exposition assumes that you have read and understood the simpler customer rating example.

Inputs

Since we need to tell the computation how many employees there are, and what their base performance data is, our inputs look much like they did for the customer rating example. We are going to use a scaled long engine here, however, because this is a financial computation:

@ScaledLong( 4 )
public static interface BonusData
{
  long bonusTotal();
  long overtimeSalaryPerHour();
  EmployeeBonusData[] employees();
}

@ScaledLong( 4 )
public static interface EmployeeBonusData
{
  long baseSalary();
  int hoursOvertime();
}

Outputs

The factory, too, is straightforward:

public static interface BonusComputationFactory
{
  public BonusComputation newComputation( BonusData _data );
}

Now, however, we need to get at an output value for each individual employee (the bonus amount), not just a global output as before. The concept is the same as for the individual inputs: we define a global output which defines an accessor for an array of outputs for the individual employees:

@ScaledLong( 4 )
public static interface BonusComputation extends Resettable
{
  EmployeeBonusComputation[] employees();
}

@ScaledLong( 4 )
public static interface EmployeeBonusComputation
{
  long bonusAmount();
}

This accessor will be implemented by AFC, just like other output methods. Apart from arrays, AFC supports List and its ancestors Collection and Iterable, as well as Iterator as the result type of section accessor methods:

List<EmployeeBonusComputation> employeesList();
Collection<EmployeeBonusComputation> employeesCollection();
Iterator<EmployeeBonusComputation> employeesIterator();

Binding

Binding the sheet to this structure now becomes straightforward again. The main difference is that when binding the section, we bind both an input and an output method and target type:

Section binder = _builder.getRootBinder();
Range range = sheet.getRange( "Employees" );

// input
Method inputMethod = BonusData.class.getMethod( "employees" );
Class inputType = EmployeeBonusData.class;

// output
Method outputMethod = BonusComputation.class.getMethod( "employees" );
Class outputType = EmployeeBonusComputation.class;

Orientation orient = Orientation.VERTICAL;

Section employees = binder.defineRepeatingSection( range, orient, inputMethod, inputType,
    outputMethod, outputType );

The employee inputs are bound just as before:

Cell salaryCell = sheet.getCell( "BaseSalary" );
Method salaryMethod = inputType.getMethod( "baseSalary" );
employees.defineInputCell( salaryCell, salaryMethod );

Cell overtimeCell = sheet.getCell( "HoursOvertime" );
employees.defineInputCell( overtimeCell, "hoursOvertime" ); // shorter form

Binding the employee output, the bonus amount, is similar in concept as binding its inputs. We simply use defineOutputCell:

Cell bonusCell = sheet.getCell( "BonusAmount" );
Method bonusMethod = outputType.getMethod( "bonusAmount" );
employees.defineOutputCell( bonusCell, bonusMethod );

Finally, we need to bind the globals:

Cell bonusTotalCell = sheet.getCell( "BonusTotal" );
binder.defineInputCell( bonusTotalCell, "bonusTotal" );

Cell overtimeRateCell = sheet.getCell( "OvertimeSalaryPerHour" );
binder.defineInputCell( overtimeRateCell, "overtimeSalaryPerHour" );

Using It

The interfaces given above can now be used as follows. First, we set up the input data in mock objects and request a computation for it:

BonusDataImpl data = new BonusDataImpl( _bonusTotal, _overtimeSalaryPerHour );
for (int i = 0; i < _salaries.length; i++) {
  EmployeeBonusDataImpl emp = new EmployeeBonusDataImpl( _salaries[ i ], _hoursOvertime[ i ] );
  data.addEmployee( emp );
}
BonusComputation computation = _factory.newComputation( data );

Then, we read back individual bonus amounts from the computation:

EmployeeBonusComputation[] emps = computation.employees();
for (int i = 0; i < _expectedBonusAmounts.length; i++) {
  long expected = _expectedBonusAmounts[ i ];
  long actual = emps[ i ].bonusAmount();
  assertEquals( expected, actual );
}

Or, using the other supported return types List, Collection, and Iterator:

List<EmployeeBonusComputation> empList = computation.employeesList();
for (int i = 0; i < _expectedBonusAmounts.length; i++) {
  long expected = _expectedBonusAmounts[ i ];
  long actual = empList.get( i ).bonusAmount();
  assertEquals( expected, actual );
}

Collection<EmployeeBonusComputation> empColl = computation.employeesCollection();
assertEquals( _expectedBonusAmounts.length, empColl.size() );
int ix = 0;
for (EmployeeBonusComputation emp : empColl) {
  long expected = _expectedBonusAmounts[ ix++ ];
  long actual = emp.bonusAmount();
  assertEquals( expected, actual );
}

Iterator<EmployeeBonusComputation> empIter = computation.employeesIterator();
for (int i = 0; i < _expectedBonusAmounts.length; i++) {
  assertTrue( empIter.hasNext() );
  long expected = _expectedBonusAmounts[ i ];
  long actual = empIter.next().bonusAmount();
  assertEquals( expected, actual );
}

Generated Code

As in the simpler input-only case, AFC generates a separate engine class for the repeating section. This time, however, it implements our output interface, EmployeeBonusComputation:

package org.formulacompiler.gen;
import org.formulacompiler.runtime.Computation;
import org.formulacompiler.tutorials.BonusPerEmployee;

final class $Sect0
    implements Computation, BonusPerEmployee.EmployeeBonusComputation
{
    private final BonusPerEmployee.EmployeeBonusData $inputs;
    final $Root $parent;
    final $Root $root;
    
    $Sect0(BonusPerEmployee.EmployeeBonusData employeebonusdata, $Root root) {
        $parent = root;
        $root = root;
        $inputs = employeebonusdata;
    }
    
    public final void reset() {
        /* empty */
    }
    
    final long get$0() {
        $Root root = $parent;
        long l = root.get$0();
        long l_0_ = get$1() * 10000L;
        $Root root_1_ = $parent;
        return l * (l_0_ / root_1_.get$2()) / 10000L;
    }
    
    public final long bonusAmount() {
        return get$0();
    }
    
    final long get$1() {
        long l = get$2();
        long l_2_ = get$3();
        $Root root = $parent;
        return l + l_2_ * root.get$1() / 10000L;
    }
    
    final long get$2() {
        return $inputs.baseSalary();
    }
    
    final long get$3() {
        return (long) $inputs.hoursOvertime() * 10000L;
    }
}

Its instances are again built in the main engine when the repeating section is first accessed. In particular, this now happens when our section output accessor employees() is called:

package org.formulacompiler.gen;
import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.tutorials.BonusPerEmployee;

final class $Root extends BonusPerEmployee.BonusComputationDefaults
    implements Computation
{
    private final BonusPerEmployee.BonusData $inputs;
    final Environment $environment;
    private $Sect0[] get$Sect0;
    
    $Root(BonusPerEmployee.BonusData bonusdata, Environment environment) {
        $environment = environment;
        $inputs = bonusdata;
    }
    
    public final void reset() {
        get$Sect0 = null;
    }
    
    final long get$0() {
        return $inputs.bonusTotal();
    }
    
    final long get$1() {
        return $inputs.overtimeSalaryPerHour();
    }
    
    final long get$2() {
        return get$3();
    }
    
    final long get$3() {
        long l = 0L;
        $Sect0[] sect0s = get$Sect0();
        int i = sect0s.length;
        for (int i_0_ = 0; i_0_ < i; i_0_++) {
            $Sect0 sect0 = sect0s[i_0_];
            l += sect0.get$1();
        }
        return l;
    }
    
    final $Sect0[] get$Sect0() {
        if (get$Sect0 == null) {
            BonusPerEmployee.EmployeeBonusData[] employeebonusdatas
                = $inputs.employees();
            if (employeebonusdatas != null) {
                int i = employeebonusdatas.length;
                $Sect0[] sect0s = new $Sect0[i];
                for (int i_1_ = 0; i_1_ < i; i_1_++)
                    sect0s[i_1_] = new $Sect0(employeebonusdatas[i_1_], this);
                get$Sect0 = sect0s;
            } else
                get$Sect0 = new $Sect0[0];
        }
        return get$Sect0;
    }
    
    public final BonusPerEmployee.EmployeeBonusComputation[] employees() {
        return get$Sect0();
    }
}

Linking Outputs To Inputs

Note that in the setup shown above, we have no way of associating an employee output interface with its corresponding input interface other than by its position in the array (or collection, or iterator).

We can change this in much the same way as to get access to the input data to provide default output implementations. Namely, by accepting the input data as the sole argument to the output type’s constructor. To do this we have to change our employee output interface to an abstract class, of course. We can then expose an accessor for the input data value passed to our constructor:

@ScaledLong( 4 )
public static interface BonusComputation extends Resettable
{
  EmployeeBonusComputation[] employees();
}

@ScaledLong( 4 )
public static abstract class EmployeeBonusComputation
{
  private final EmployeeBonusData inputs;

  public EmployeeBonusComputation( EmployeeBonusData _inputs )
  {
    super();
    this.inputs = _inputs;
  }

  public EmployeeBonusData inputs()
  {
    return this.inputs;
  }

  public abstract long bonusAmount();
}

Now we have the desired association:

BonusComputation comp = _factory.newComputation( data );
EmployeeBonusComputation[] empOutputs = comp.employees();
EmployeeBonusData[] empInputs = data.employees();
for (int i = 0; i < _expectedBonusAmounts.length; i++) {
  assertSame( empInputs[ i ], empOutputs[ i ].inputs() );
}

Linking Outputs To Parent Outputs

In your constructor, you can also get the parent section’s output object in case you need to access it in your default implementations. Here’s how:

@ScaledLong( 4 )
public static interface BonusComputation extends Resettable
{
  EmployeeBonusComputation[] employees();
}

@ScaledLong( 4 )
public static abstract class EmployeeBonusComputation
{
  private final BonusComputation parent;

  public EmployeeBonusComputation( EmployeeBonusData _inputs, BonusComputation _parent )
  {
    super();
    this.parent = _parent;
  }

  public BonusComputation parent()
  {
    return this.parent;
  }

  public abstract long bonusAmount();
}

Note that if you need to get the parent, you must also specify the input object as a constructor parameter. Here’s the effect at runtime:

BonusComputation comp = _factory.newComputation( data );
EmployeeBonusComputation[] empOutputs = comp.employees();
for (int i = 0; i < _expectedBonusAmounts.length; i++) {
  assertSame( comp, empOutputs[ i ].parent() );
}