Possible regression - civicrm_line_item.line_total is incorrectly tax inclusive - potentially 5.39+
In 5.39 there was a fix to stop the situation where the contribution.total_amount was recalculated on edit when tax was involved. However this fix had a bug and civicrm_line_item.line_total [stored the tax INCLUSIVE amount](see https://github.com/civicrm/civicrm-core/commit/e967ce8fe2b58b94e2163dde395542e55599da13#diff-4c9d0b1abe07057a4eea2b47bc627eecb95face8ed8d86c1c005312a52cca811L4026-L4035) We have merged a fix to the rc but I opened this to try to determine if any released versions are affected. This rose to a bigger issue in the rc because of changes on top of it - which assumed it was right.
The reason this went awol is largely because we had too behaviours - if the contribution bao didn't receive tax_amount it treated total_amount as exclusive and if it did it treated it as inclusive. The former was locked in by tests & not the latter leading to confusion (I'm still confused)
At this stage I'm unsure about real-world issues with this as
- it didn't affect any core forms that I'm aware of as they pass in 'line_item' or 'skipLineItem'
- this was the 'correct' behaviour historically for Contribution.create api calls that should have tax_amount passed in, but didn't
- we have a deprecation notice in the code that would likely have been triggered if line item tax totals did not add up to contribution totals & it's unlikely people would miss wrong contribution totals.
However, my head breaks a bit when I try to think through this
I think this would pick up any wrong rows
SELECT SUM(li.line_total + li.tax_amount) as lines_total, count(li.id) as c, SUM(c.total_amount) as total_amount FROM civicrm_contribution c LEFT JOIN civicrm_line_item li ON li.contribution_id = c.id GROUP BY c.id HAVING c > 1 AND lines_total <> total_amount