[FIX] product_margin: Product margin

Steps to reproduce the bug:
- Install accounting.
- Go to Accounting Settings and activate multi-currencies and margin analysis.
- Company currency in EUR
- Currency rate for EUR is 1.0 and 1.5 for USD
- Create two invoices, one in USD and the other in EUR. Both for 30 and for product P.
- Confirm and register payment to make them fully payed.
- Go to Accounting > Reporting > Product Margins > Open Margins.

Bug:

The average sale unit price for P was 60 instead of 25

opw:2035968

closes odoo/odoo#34998

Signed-off-by: Simon Goffin (sig) <sig@openerp.com>

1 files changed, 8 insertions, 2 deletions

diff --git a/addons/product_margin/models/product_product.py b/addons/product_margin/models/product_product.py
index 9fc94ea6310..2e7b20f6e96 100644
--- a/addons/product_margin/models/product_product.py
+++ b/addons/product_margin/models/product_product.py
@@ -104,8 +104,9 @@ class ProductProduct(models.Model):
 
             #Cost price is calculated afterwards as it is a property
             sqlstr = """
+                WITH currency_rate AS ({})
                 select
-                    sum(l.price_unit * l.quantity)/nullif(sum(l.quantity),0) as avg_unit_price,
+                    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.quantity * (l.price_subtotal_signed/(nullif(l.quantity,0)))) as total,
                     sum(l.quantity * pt.list_price) as sale_expected
@@ -113,8 +114,13 @@ class ProductProduct(models.Model):
                 left join account_invoice i on (l.invoice_id = i.id)
                 left join product_product product on (product.id=l.product_id)
                 left join product_template pt on (pt.id = product.product_tmpl_id)
+                left join currency_rate cr on
+                (cr.currency_id = i.currency_id and
+                 cr.company_id = i.company_id and
+                 cr.date_start <= COALESCE(i.date_invoice, NOW()) and
+                 (cr.date_end IS NULL OR cr.date_end > COALESCE(i.date_invoice, NOW())))
                 where l.product_id = %s and i.state in %s and i.type IN %s and (i.date_invoice IS NULL or (i.date_invoice>=%s and i.date_invoice<=%s and i.company_id=%s))
-                """
+                """.format(self.env['res.currency']._select_companies_rates())
             invoice_types = ('out_invoice', 'in_refund')
             self.env.cr.execute(sqlstr, (val.id, states, invoice_types, date_from, date_to, company_id))
             result = self.env.cr.fetchall()[0]