[FIX] product_margin: qty in credit note

- Create a product P with a Sales Price of 5000 and a Cost of 3000.
- Create a bill for 10 units @ 3000, post
- Create an invoice for 10 units @ 5000, post
- Create a customer credit note for 5 units
- Open the Product Margin report

The # Purchased is 15 while the # Invoiced in Sale is 10.

The query incorrectly sums the invoice lines based on their type. We
should group customer invoices with customer refunds, and group vendor
bills with vendor refunds. When grouping we should subtract invoices
and refunds.

Note that the `avg_unit_price` is not modified since there is no reason
to refund a product at a different unit price.

opw-2211636

closes odoo/odoo#49349

X-original-commit: d3c6b16e2ad2166b636372b537292e06d4d575c4
Signed-off-by: Nicolas Martinelli (nim) <nim@odoo.com>

1 files changed, 6 insertions, 6 deletions

diff --git a/addons/product_margin/models/product_product.py b/addons/product_margin/models/product_product.py
index a6e16b5a93e..d5c5c6c151b 100644
--- a/addons/product_margin/models/product_product.py
+++ b/addons/product_margin/models/product_product.py
@@ -110,9 +110,9 @@ class ProductProduct(models.Model):
                 WITH currency_rate AS ({})
                 SELECT
                     SUM(l.price_unit / (CASE COALESCE(cr.rate, 0) WHEN 0 THEN 1.0 ELSE cr.rate END) * l.quantity) / NULLIF(SUM(l.quantity),0) AS avg_unit_price,
-                    SUM(l.quantity) AS num_qty,
-                    SUM(l.balance) AS total,
-                    SUM(l.quantity * pt.list_price) AS sale_expected
+                    SUM(l.quantity * (CASE WHEN i.move_type IN ('out_invoice', 'in_invoice') THEN 1 ELSE -1 END)) AS num_qty,
+                    SUM(ABS(l.balance) * (CASE WHEN i.move_type IN ('out_invoice', 'in_invoice') THEN 1 ELSE -1 END)) AS total,
+                    SUM(l.quantity * pt.list_price * (CASE WHEN i.move_type IN ('out_invoice', 'in_invoice') THEN 1 ELSE -1 END)) AS sale_expected
                 FROM account_move_line l
                 LEFT JOIN account_move i ON (l.move_id = i.id)
                 LEFT JOIN product_product product ON (product.id=l.product_id)
@@ -131,15 +131,15 @@ class ProductProduct(models.Model):
                 AND l.display_type IS NULL
                 AND l.exclude_from_invoice_tab = false
                 """.format(self.env['res.currency']._select_companies_rates())
-            invoice_types = ('out_invoice', 'in_refund')
+            invoice_types = ('out_invoice', 'out_refund')
             self.env.cr.execute(sqlstr, (val.id, states, payment_states, invoice_types, date_from, date_to, company_id))
             result = self.env.cr.fetchall()[0]
             res[val.id]['sale_avg_price'] = result[0] and result[0] or 0.0
             res[val.id]['sale_num_invoiced'] = result[1] and result[1] or 0.0
-            res[val.id]['turnover'] = result[2] and -result[2] or 0.0
+            res[val.id]['turnover'] = result[2] and result[2] or 0.0
             res[val.id]['sale_expected'] = result[3] and result[3] or 0.0
             res[val.id]['sales_gap'] = res[val.id]['sale_expected'] - res[val.id]['turnover']
-            invoice_types = ('in_invoice', 'out_refund')
+            invoice_types = ('in_invoice', 'in_refund')
             self.env.cr.execute(sqlstr, (val.id, states, payment_states, invoice_types, date_from, date_to, company_id))
             result = self.env.cr.fetchall()[0]
             res[val.id]['purchase_avg_price'] = result[0] and result[0] or 0.0