Agent Sales Report - "Total Cost" calculation appears to be incorrect when Products are spread over multiple Invoices

[johnM2401]

Describe the bug When the "Total Cost" is calculated on the Agent Sales report, it appears to be calculating the Total Cost incorrectly, similar to issue #297

As far as I can tell, it appears to be doing this by calculating the Total Price, and applying it to each Product. Effectively Multiplying the Actual Total Cost by the Number of Unique Products.

This appears to only occur when the same products are spread over multiple Invoices.

For example;

I have two invoices.

Invoice 1 has:

A Line Item with: Quantity 1, Cost 5 A 2nd Line Item with: Quantity 5, Cost 1

Total Cost : 10

Invoice 2 has:

A Line Item with: Quantity 1, Cost 5 A 2nd Line Item with: Quantity 5, Cost 1

Total Cost : 10

The Cumulative Cost of the Products used in these Line Items is $20

However, the "Total Cost" value appears to come out as $40, on the Agent Sales Report ($20 * 2 Unique Products)

See Screenshots for an Example

Component What component does this bug relate to?

1. Report - Agent Sales report

Solution File AgentSalesReport.prpt

To Reproduce Steps to reproduce the behavior:

1. Create two Products, One with a cost of $5. Another with a cost of $1.
2. Create an Invoice, add a Line item, Choosing the $5-Cost Product, with a Quantity of 1
3. Add a Second Line item this Invoice, Choosing the $1-Cost Product, with a Quantity of 5

4. Create a 2nd Invoice, add a Line item, Choosing the $5-Cost Product, with a Quantity of 1
5. Add a Second Line item this Invoice, Choosing the $1-Cost Product, with a Quantity of 5

6. Pull this to the DWH with the ./run script
7. View the Periodic Agent Country Sales Report, to see the correct Total Cost value: ($20 Total Cost)
8. View the Agent Sales Report, to see how this is shown, showing the incorrect Total Cost: ($40 Total Cost)

Expected behavior The "Total Cost" will show an accurate running number for each Item/Invoice

Screenshots

Note:

Invoice 1:

Invoice 2:

Agent Sales Report:

If I then create a third invoice:

The Agent Sales Report shows as:

Where the Total Cost should actually be $30:

Desktop (please complete the following information):

• OS: Ubuntu 18.04
• Browser: Firefox 88.0.1

Please let me know if anything needs clarified!

[fraserward]

@johnM2401 Thanks John.

Its an SQL issue that as you stated happens on separate invoices being raised with the same product items.

This would have have gotten worse over time as with each new invoice with the same product would have multiplied. i.e. 4 invoices with same product would have multiplied the real result by 4 and so on.

You can retest here on this branch.... https://github.com/ivylabs/suitecrm-analytics/tree/feature-300

@harrisward Pull request ... https://github.com/ivylabs/suitecrm-analytics/pull/304

[johnM2401]

Hey @fraserward

Looks good to me, Both Reports appear to now show the real "Total Cost" value, when products are used across multiple Line Items & Invoices

Can be merged to master.