[IMP] sale,sale_*: denormalize sale.order to speed up sale.report

Impacted modules: sale, sale_margin, sale_timesheet, pos_sale

This speed up a lot reports like sale.report when multiple currencies
with multiple rates are used, even with few currencies and few rates.
Using `_select_companies_rates` in SQL view bring lots of JOINs,
which slow down the view generation. This commit adds a computed
stored field containing the currency rate at the order date to speed
up report generation (database schema denormalisation).

This commit also use this new column on sale.report extensions and on
the project.profitability.report in sale_timesheet module.

Since a4d6bff197b894f70eaecf5aa4e2088e5b014780, the pos.report is
included in the sale.report. So, the same change had to be
applied on the pos.order model.

Task #1877418
Closes #25089

8 files changed, 41 insertions, 37 deletions

diff --git a/addons/pos_sale/models/__init__.py b/addons/pos_sale/models/__init__.py
index 00d1440560a..a1233390863 100644
--- a/addons/pos_sale/models/__init__.py
+++ b/addons/pos_sale/models/__init__.py
@@ -2,5 +2,6 @@
 # Part of Odoo. See LICENSE file for full copyright and licensing details.
 
 from . import pos_config
+from . import pos_order
 from . import crm_team
 from . import pos_session
diff --git a/addons/pos_sale/models/pos_order.py b/addons/pos_sale/models/pos_order.py
new file mode 100644
index 00000000000..03d31171c26
--- /dev/null
+++ b/addons/pos_sale/models/pos_order.py
@@ -0,0 +1,16 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+
+from odoo import api, fields, models
+
+
+class PosOrder(models.Model):
+    _inherit = 'pos.order'
+
+    currency_rate = fields.Float("Currency Rate", compute='_compute_currency_rate', store=True, digits=(12, 6), readonly=True, help='The rate of the currency to the currency of rate applicable at the date of the order')
+
+    @api.depends('pricelist_id.currency_id', 'date_order', 'company_id')
+    def _compute_currency_rate(self):
+        for order in self:
+            date_order = order.date_order or fields.Datetime.now()
+            order.currency_rate = order.pricelist_id.currency_id._get_rates(order.company_id, date_order).get(order.company_id.currency_id.id, 1.0)
diff --git a/addons/pos_sale/report/sale_report.py b/addons/pos_sale/report/sale_report.py
index ddc713c0bb9..aa019441f27 100644
--- a/addons/pos_sale/report/sale_report.py
+++ b/addons/pos_sale/report/sale_report.py
@@ -14,7 +14,7 @@ class SaleReport(models.Model):
                                             ('invoiced', 'Invoiced')], string='Status', readonly=True)
 
     def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
-        with_clause += ''', pol_tax AS (SELECT line_tax.pos_order_line_id AS pol_id,sum(tax.amount/100) AS amount
+        with_clause += '''pol_tax AS (SELECT line_tax.pos_order_line_id AS pol_id,sum(tax.amount/100) AS amount
                        FROM account_tax_pos_order_line_rel line_tax
                          JOIN account_tax tax ON line_tax.account_tax_id = tax.id
                       GROUP BY line_tax.pos_order_line_id)'''
@@ -58,7 +58,7 @@ class SaleReport(models.Model):
                left join product_template t on (p.product_tmpl_id=t.id)
                left join uom_uom u on (u.id=t.uom_id)) AS volume,
             l.discount as discount,
-            sum((l.price_unit * l.discount / 100.0 / COALESCE(cr.rate, 1.0))) as discount_amount,
+            sum((l.price_unit * l.discount / 100.0 / CASE COALESCE(pos.currency_rate, 0) WHEN 0 THEN 1.0 ELSE pos.currency_rate END)) as discount_amount,
             NULL as order_id
         '''
 
@@ -76,10 +76,6 @@ class SaleReport(models.Model):
                     LEFT JOIN pos_session session ON (session.id = pos.session_id)
                     LEFT JOIN pos_config config ON (config.id = session.config_id)
                 left join product_pricelist pp on (pos.pricelist_id = pp.id)
-                left join currency_rate cr on (cr.currency_id = pp.currency_id and
-                    cr.company_id = pos.company_id and
-                    cr.date_start <= coalesce(pos.date_order, now()) and
-                    (cr.date_end is null or cr.date_end > coalesce(pos.date_order, now())))
         '''
 
         groupby_ = '''
diff --git a/addons/sale/models/sale.py b/addons/sale/models/sale.py
index dc91312706d..ef1c8b1c1e5 100644
--- a/addons/sale/models/sale.py
+++ b/addons/sale/models/sale.py
@@ -160,6 +160,7 @@ class SaleOrder(models.Model):
     amount_by_group = fields.Binary(string="Taxe amount by group", compute='_amount_by_group', help="type: [(name, amount, base, formated amount, formated base)]")
     amount_tax = fields.Monetary(string='Taxes', store=True, readonly=True, compute='_amount_all')
     amount_total = fields.Monetary(string='Total', store=True, readonly=True, compute='_amount_all', track_visibility='always', track_sequence=6)
+    currency_rate = fields.Float("Currency Rate", compute='_compute_currency_rate', store=True, digits=(12, 6), readonly=True, help='The rate of the currency to the currency of rate 1 applicable at the date of the order')
 
     payment_term_id = fields.Many2one('account.payment.term', string='Payment Terms', oldname='payment_term')
     fiscal_position_id = fields.Many2one('account.fiscal.position', oldname='fiscal_position', string='Fiscal Position')
@@ -183,6 +184,11 @@ class SaleOrder(models.Model):
     authorized_transaction_ids = fields.Many2many('payment.transaction', compute='_compute_authorized_transaction_ids',
                                                   string='Authorized Transactions', copy=False, readonly=True)
 
+    @api.depends('pricelist_id', 'date_order', 'company_id')
+    def _compute_currency_rate(self):
+        for order in self:
+            order.currency_rate = order.currency_id._get_rates(order.company_id, order.date_order).get(order.currency_id.id, 1.0)
+
     def _compute_access_url(self):
         super(SaleOrder, self)._compute_access_url()
         for order in self:
diff --git a/addons/sale/report/report_all_channels_sales.py b/addons/sale/report/report_all_channels_sales.py
index 7c2e6edcc00..234e39e83f2 100644
--- a/addons/sale/report/report_all_channels_sales.py
+++ b/addons/sale/report/report_all_channels_sales.py
@@ -27,7 +27,6 @@ class PosSaleReport(models.Model):
 
     def _so(self):
         so_str = """
-            WITH currency_rate as (%s)
                 SELECT sol.id AS id,
                     so.name AS name,
                     so.partner_id AS partner_id,
@@ -37,10 +36,10 @@ class PosSaleReport(models.Model):
                     so.user_id AS user_id,
                     pt.categ_id AS categ_id,
                     so.company_id AS company_id,
-                    sol.price_total / COALESCE(cr.rate, 1.0) AS price_total,
+                    sol.price_total / CASE COALESCE(so.currency_rate, 0) WHEN 0 THEN 1.0 ELSE so.currency_rate END AS price_total,
                     so.pricelist_id AS pricelist_id,
                     rp.country_id AS country_id,
-                    sol.price_subtotal / COALESCE (cr.rate, 1.0) AS price_subtotal,
+                    sol.price_subtotal / CASE COALESCE(so.currency_rate, 0) WHEN 0 THEN 1.0 ELSE so.currency_rate END AS price_subtotal,
                     (sol.product_uom_qty / u.factor * u2.factor) as product_qty,
                     so.analytic_account_id AS analytic_account_id,
                     so.team_id AS team_id
@@ -51,14 +50,10 @@ class PosSaleReport(models.Model):
                     JOIN res_partner rp ON (so.partner_id = rp.id)
                     LEFT JOIN product_template pt ON (pro.product_tmpl_id = pt.id)
                     LEFT JOIN product_pricelist pp ON (so.pricelist_id = pp.id)
-                    LEFT JOIN currency_rate cr ON (cr.currency_id = pp.currency_id AND
-                        cr.company_id = so.company_id AND
-                        cr.date_start <= COALESCE(so.date_order, now()) AND
-                        (cr.date_end IS NULL OR cr.date_end > COALESCE(so.date_order, now())))
                     LEFT JOIN uom_uom u on (u.id=sol.product_uom)
                     LEFT JOIN uom_uom u2 on (u2.id=pt.uom_id)
             WHERE so.state != 'cancel'
-        """ % self.env['res.currency']._select_companies_rates()
+        """
         return so_str
 
     def _from(self):
diff --git a/addons/sale/report/sale_report.py b/addons/sale/report/sale_report.py
index 0af13520c2a..0c9aa4b7e7c 100644
--- a/addons/sale/report/sale_report.py
+++ b/addons/sale/report/sale_report.py
@@ -52,7 +52,7 @@ class SaleReport(models.Model):
     order_id = fields.Integer('Order ID', readonly=True)
 
     def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
-        with_ = """currency_rate as (%s) %s""" % (self.env['res.currency']._select_companies_rates(), with_clause)
+        with_ = ("WITH %s" % with_clause) if with_clause else ""
 
         select_ = """
             min(l.id) as id,
@@ -62,10 +62,10 @@ class SaleReport(models.Model):
             sum(l.qty_delivered / u.factor * u2.factor) as qty_delivered,
             sum(l.qty_invoiced / u.factor * u2.factor) as qty_invoiced,
             sum(l.qty_to_invoice / u.factor * u2.factor) as qty_to_invoice,
-            sum(l.price_total / COALESCE(cr.rate, 1.0)) as price_total,
-            sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) as price_subtotal,
-            sum(l.untaxed_amount_to_invoice / COALESCE(cr.rate, 1.0)) as untaxed_amount_to_invoice,
-            sum(l.untaxed_amount_invoiced / COALESCE(cr.rate, 1.0)) as untaxed_amount_invoiced,
+            sum(l.price_total / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_total,
+            sum(l.price_subtotal / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_subtotal,
+            sum(l.untaxed_amount_to_invoice / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as untaxed_amount_to_invoice,
+            sum(l.untaxed_amount_invoiced / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as untaxed_amount_invoiced,
             count(*) as nbr,
             s.name as name,
             s.date_order as date,
@@ -85,7 +85,7 @@ class SaleReport(models.Model):
             sum(p.weight * l.product_uom_qty / u.factor * u2.factor) as weight,
             sum(p.volume * l.product_uom_qty / u.factor * u2.factor) as volume,
             l.discount as discount,
-            sum((l.price_unit * l.discount / 100.0 / COALESCE(cr.rate, 1.0))) as discount_amount,
+            sum((l.price_unit * l.discount / 100.0 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END)) as discount_amount,
             s.id as order_id
         """
 
@@ -101,10 +101,6 @@ class SaleReport(models.Model):
                     left join uom_uom u on (u.id=l.product_uom)
                     left join uom_uom u2 on (u2.id=t.uom_id)
                     left join product_pricelist pp on (s.pricelist_id = pp.id)
-                    left join currency_rate cr on (cr.currency_id = pp.currency_id and
-                        cr.company_id = s.company_id and
-                        cr.date_start <= coalesce(s.date_order, now()) and
-                        (cr.date_end is null or cr.date_end > coalesce(s.date_order, now())))
                 %s
         """ % from_clause
 
@@ -130,7 +126,7 @@ class SaleReport(models.Model):
             s.id %s
         """ % (groupby)
 
-        return 'WITH %s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (with_, select_, from_, groupby_)
+        return '%s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (with_, select_, from_, groupby_)
 
     @api.model_cr
     def init(self):
diff --git a/addons/sale_margin/report/sale_report.py b/addons/sale_margin/report/sale_report.py
index e89559e8304..1ac87891e97 100644
--- a/addons/sale_margin/report/sale_report.py
+++ b/addons/sale_margin/report/sale_report.py
@@ -10,5 +10,5 @@ class SaleReport(models.Model):
     margin = fields.Float('Margin')
 
     def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
-        fields['margin'] = ", SUM(l.margin / COALESCE(cr.rate, 1.0)) AS margin"
+        fields['margin'] = ", SUM(l.margin / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) AS margin"
         return super(SaleReport, self)._query(with_clause, fields, groupby, from_clause)
diff --git a/addons/sale_timesheet/report/project_profitability_report_analysis.py b/addons/sale_timesheet/report/project_profitability_report_analysis.py
index 0829c9e875c..ae2e26819c4 100644
--- a/addons/sale_timesheet/report/project_profitability_report_analysis.py
+++ b/addons/sale_timesheet/report/project_profitability_report_analysis.py
@@ -36,7 +36,6 @@ class ProfitabilityAnalysis(models.Model):
         tools.drop_view_if_exists(self._cr, self._table)
         query = """
             CREATE VIEW %s AS (
-                WITH currency_rate as (%s)
                 SELECT
                     ROW_NUMBER() OVER (ORDER BY P.id, SOL.id) AS id,
                     P.id AS project_id,
@@ -51,7 +50,7 @@ class ProfitabilityAnalysis(models.Model):
                     SOL.product_id AS product_id,
                     SOL.qty_delivered_method AS sale_qty_delivered_method,
                     CASE
-                       WHEN SOL.qty_delivered_method = 'analytic' THEN (SOL.untaxed_amount_to_invoice / COALESCE(CR.rate, 1.0))
+                       WHEN SOL.qty_delivered_method = 'analytic' THEN (SOL.untaxed_amount_to_invoice / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END)
                        ELSE 0.0
                     END AS expense_amount_untaxed_to_invoice,
                     CASE
@@ -59,17 +58,17 @@ class ProfitabilityAnalysis(models.Model):
                        THEN
                             CASE
                                 WHEN T.expense_policy = 'sales_price'
-                                THEN (SOL.price_reduce / COALESCE(CR.rate, 1.0)) * SOL.qty_invoiced
+                                THEN (SOL.price_reduce / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END) * SOL.qty_invoiced
                                 ELSE -COST_SUMMARY.expense_cost
                             END
                        ELSE 0.0
                     END AS expense_amount_untaxed_invoiced,
                     CASE
-                       WHEN SOL.qty_delivered_method = 'timesheet' THEN (SOL.untaxed_amount_to_invoice / COALESCE(CR.rate, 1.0))
+                       WHEN SOL.qty_delivered_method = 'timesheet' THEN (SOL.untaxed_amount_to_invoice / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END)
                        ELSE 0.0
                     END AS amount_untaxed_to_invoice,
                     CASE
-                       WHEN SOL.qty_delivered_method = 'timesheet' THEN (SOL.untaxed_amount_invoiced / COALESCE(CR.rate, 1.0))
+                       WHEN SOL.qty_delivered_method = 'timesheet' THEN (SOL.untaxed_amount_invoiced / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END)
                        ELSE 0.0
                     END AS amount_untaxed_invoiced,
                     COST_SUMMARY.timesheet_unit_amount AS timesheet_unit_amount,
@@ -145,12 +144,7 @@ class ProfitabilityAnalysis(models.Model):
                     LEFT JOIN sale_order S ON SOL.order_id = S.id
                     LEFT JOIN product_product PP on (SOL.product_id=PP.id)
                     LEFT JOIN product_template T on (PP.product_tmpl_id=T.id)
-                    LEFT JOIN currency_rate CR ON (CR.currency_id = SOL.currency_id
-                        AND CR.currency_id != C.currency_id
-                        AND CR.company_id = SOL.company_id
-                        AND CR.date_start <= COALESCE(S.date_order, now())
-                        AND (CR.date_end IS NULL OR cr.date_end > COALESCE(S.date_order, now())))
                     WHERE P.active = 't' AND P.analytic_account_id IS NOT NULL
             )
-        """ % (self._table, self.env['res.currency']._select_companies_rates())
+        """ % self._table
         self._cr.execute(query)