The Challenge of the Commission Report
In this article, read how a manually-created Google Sheet became quite a challenge to build as a report within the Angular application.
Join the DZone community and get the full member experience.
Join For FreeAs 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:
Determine which agents had sales for the report period.
Put the agents into a
Map(Integer, AgentInfo),
whereAgentInfo
is an inner-class containing the id, fullName, and totalCommission.Create a new
com.fasterxml.jackson.databind.ObjectMapper
and someArrayNodes
to store the data.Initialize
BigDecimals
for totalSales, totalCommission, and totalProfit.For each property sold during the report period:
Get the totalAgentAmount paid to all agents for the property sold.
Initialize a new
BigDecimal
for referralAmount.Create a
BigDecimal
for the profit of the current property sale.Use an
ObjectNode
to store all the values for this entry needed in the report.Add the values to the total
BigDecimals
created in step 4.For each agent in the
Map(Integer, AgentInfo)
created in step 2:Determine the amount (if any) to be applied to the current agent.
Update the totalCommission for that agent in the
Map(Integer, AgentInfo)
.Add each value to the payload, using the
AgentInfo.getFullName()
as the key and the commissionAmount as the value.
Check the referral name and amount attributes on the property and update the
ObjectNode
with this information.
Compute the totals on the final line of the
ObjectNode
, which required iterating through theMap(Integer, AgentInfo)
.Organize the
ObjectNodes
into theObjectMapper
.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:
The Challenge of the Commission Report (this article)
Have a really great day!
Opinions expressed by DZone contributors are their own.
Comments