[MERGE] forward port branch saas-12.3 up to 40e8b67179f

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 362fc1fbccb..8065dc32d27 100644
--- a/addons/product_margin/models/product_product.py
+++ b/addons/product_margin/models/product_product.py
@@ -107,8 +107,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.balance) AS total,
                     SUM(l.quantity * pt.list_price) AS sale_expected
@@ -116,6 +117,11 @@ class ProductProduct(models.Model):
                 LEFT JOIN account_move i ON (l.move_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.invoice_date, NOW()) and
+                 (cr.date_end IS NULL OR cr.date_end > COALESCE(i.invoice_date, NOW())))
                 WHERE l.product_id = %s
                 AND i.state IN %s
                 AND i.invoice_payment_state IN %s
@@ -123,7 +129,7 @@ class ProductProduct(models.Model):
                 AND i.invoice_date BETWEEN %s AND  %s
                 AND i.company_id = %s
                 AND l.display_type IS NULL
-                """
+                """.format(self.env['res.currency']._select_companies_rates())
             invoice_types = ('out_invoice', 'in_refund')
             self.env.cr.execute(sqlstr, (val.id, states, invoice_payment_states, invoice_types, date_from, date_to, company_id))
             result = self.env.cr.fetchall()[0]