The Challenge of the Commission Report

As a follow-up to my "New Application Journey" article, I wanted to talk about a challenge I encountered while trying to build a report that was manually created each month using Google Sheets.

Recap

As a TL;DR (too long, didn't read) to the original article, I wasn't happy with the application my mother-in-law was using for her very small business in the southeast section of the United States. So, I used her business needs to create a new application from scratch using Angular, MySQL, and the AWS environment. As I started building out reports for the data, I realized that automating the manual report she created in Google Sheets was not going to be as easy as I expected.

The Commission Report

The Commission Report is a report that combines Property information and Agent Sale information. On a monthly basis, this information was put into a Google Sheet using the following structure:

The property information is common for reports, but the interesting requirement is the ability to list all agents who had at least one sale during the period of the report. This is challenging because the data was defined as shown below:

I knew that building this report was not going to be as simple as returning a query and looping through the results like I had done elsewhere in the application.

Referral Information

The Commission Report also had a second table, where a list of individuals who received a payment for referring someone to buy a property from my mother-in-law's company. This table simply contained a name and amount. Here is an example of what this simple table should look like:

Building the Report

Using the Java/API I realized my approach was going to take the following steps:

1. Determine which agents had sales for the report period.

2. Put the agents into a Map(Integer, AgentInfo), where AgentInfo is an inner-class containing the id, fullName, and totalCommission.

3. Create a new com.fasterxml.jackson.databind.ObjectMapper and some ArrayNodes to store the data.

4. Initialize BigDecimals for totalSales, totalCommission, and totalProfit.

5. For each property sold during the report period:

   1. Get the totalAgentAmount paid to all agents for the property sold.

   2. Initialize a new BigDecimal for referralAmount.

   3. Create a BigDecimal for the profit of the current property sale.

   4. Use an ObjectNode to store all the values for this entry needed in the report.

   5. Add the values to the total BigDecimals created in step 4.

   6. For each agent in the Map(Integer, AgentInfo) created in step 2:

      1. Determine the amount (if any) to be applied to the current agent.

      2. Update the totalCommission for that agent in the Map(Integer, AgentInfo).

      3. Add each value to the payload, using the AgentInfo.getFullName() as the key and the commissionAmount as the value.

   7. Check the referral name and amount attributes on the property and update the ObjectNode with this information.

6. Compute the totals on the final line of the ObjectNode, which required iterating through the Map(Integer, AgentInfo).

7. Organize the ObjectNodes into the ObjectMapper.

8. Return the primary ArrayNode, which contained all the child information.

Processing the Report in Angular

When running the report, I ended up with three collections of data:

• The list of properties sold, which contained key/value pairs for each agent and their sale for that given property (properties).

• A single line containing the totals for each column of the report (agentTotals).

• A list of referrals that were paid during the reporting period (referrals).

Within angular I was able to set a reportColumns: string[] using the following syntax:

this.reportColumns = Object.keys(data[0].properties[0]); 

On the template side, I was able to get a list of columns using the following code:

<th *ngFor="let key of reportColumns">
  {{key}}
</th>

For the core data in the report, the template contained the following logic:

<tr *ngFor="let thisCommission of reportData[0].properties">
  <td *ngFor="let key of reportColumns">
    {{thisCommission[key]}}
  </td>
</tr>

Then, the footer used the following approach:

<tr *ngFor="let thisCommissionTotal of reportData[1].agentTotals">
  <th>Totals</th>
  <th *ngFor="let key of totalColumns">
    {{thisCommissionTotal[key]}}
  </th>
</tr>

The referrals were simple, by comparison, as shown below:

<tr *ngFor="let thisReferral of reportData[2].referrals">
  <td>
    {{thisReferral["Property Name"]}}
  </td>
  <td>
    {{thisReferral["Name"]}}
  </td>
  <td>
    {{thisReferral["Amount"]}}
  </td>
</tr>

No Pipe Support

Once I got the report functional, I realized I needed to format the report a little nicer. However, based upon the design above, I did not have the option to use the pipe functionality to format the data on rendering, since I was using the {{thisCommissionTotal[key]}} approach.

So, I ended up doing the formatting in the Java API, thus returning all string data to the Angular client. In hindsight, I probably could have included another attribute in the payload for the appropriate pipe value (i.e. date, currency, etc.).

A Functional Report in Place

With everything in place, the Commission Report was working as expected. It looked great on the screen and comparing the report against the actual data, showed numbers that matched other views and reports.

The only exception we ran into was when my mother-in-law attempted to print the report. The browser (Chrome) always wants to print the table in portrait mode (instead of landscape). I did try using the following CSS code, which rotated the report 270 degrees, but it still doesn't print as expected:

@media print {
.printed {
  height: 8.5in;
  width: 11in;
  position: absolute;
  bottom: 1.5in;
  left: -1.25in;
  -ms-transform: rotate(270deg);
  /* IE 9 */
  -webkit-transform: rotate(270deg);
  /* Chrome, Safari, Opera */
  transform: rotate(270deg);
  }
}

Basically, the page is switched to landscape, but I only see about 75% before the browser tries to start a new page (on the same page). Also, there is only ever one page that prints out.

Still looking into a fix for this situation. In the meantime, copying the report from the HTML page into Google Sheets provides a printable option for now.

Looking Ahead

This article is a continuation of a multi-part series that I am putting together regarding my new application journey to providing a better application experience for my mother-in-law. Below, is a list of the current and planned articles, if you are interested in reading more:

• New Application Journey

• Okta, a nice solution - even for small apps 

• FormBuilder in Angular 6

• The Challenge of the Commission Report (this article)

• Making the County List Dynamic

• New Version of the Commission Report

• What I Learned After Initial Deployment

Have a really great day!