[REF] account: use dynamic `_table_query` in invoice analysis

closes odoo/odoo#56080

Related: odoo/upgrade#1664
Related: odoo/enterprise#9914
Signed-off-by: Raphael Collet (rco) <rco@openerp.com>

4 files changed, 53 insertions, 98 deletions

diff --git a/addons/account/models/partner.py b/addons/account/models/partner.py
index c602a92240c..e72f3f8a723 100644
--- a/addons/account/models/partner.py
+++ b/addons/account/models/partner.py
@@ -302,11 +302,9 @@ class ResPartner(models.Model):
         return self._asset_difference_search('payable', operator, operand)
 
     def _invoice_total(self):
-        account_invoice_report = self.env['account.invoice.report']
         if not self.ids:
             return True
 
-        user_currency_id = self.env.company.currency_id.id
         all_partners_and_children = {}
         all_partner_ids = []
         for partner in self:
@@ -314,30 +312,14 @@ class ResPartner(models.Model):
             all_partners_and_children[partner] = self.with_context(active_test=False).search([('id', 'child_of', partner.id)]).ids
             all_partner_ids += all_partners_and_children[partner]
 
-        # searching account.invoice.report via the ORM is comparatively expensive
-        # (generates queries "id in []" forcing to build the full table).
-        # In simple cases where all invoices are in the same currency than the user's company
-        # access directly these elements
-
-        # generate where clause to include multicompany rules
-        where_query = account_invoice_report._where_calc([
-            ('partner_id', 'in', all_partner_ids), ('state', 'not in', ['draft', 'cancel']),
-            ('move_type', 'in', ('out_invoice', 'out_refund'))
-        ])
-        account_invoice_report._apply_ir_rules(where_query, 'read')
-        from_clause, where_clause, where_clause_params = where_query.get_sql()
-
-        # price_total is in the company currency
-        query = """
-                  SELECT SUM(price_subtotal) as total, partner_id
-                    FROM account_invoice_report account_invoice_report
-                   WHERE %s
-                   GROUP BY partner_id
-                """ % where_clause
-        self.env.cr.execute(query, where_clause_params)
-        price_totals = self.env.cr.dictfetchall()
+        domain = [
+            ('partner_id', 'in', all_partner_ids),
+            ('state', 'not in', ['draft', 'cancel']),
+            ('move_type', 'in', ('out_invoice', 'out_refund')),
+        ]
+        price_totals = self.env['account.invoice.report'].read_group(domain, ['price_subtotal'], ['partner_id'])
         for partner, child_ids in all_partners_and_children.items():
-            partner.total_invoiced = sum(price['total'] for price in price_totals if price['partner_id'] in child_ids)
+            partner.total_invoiced = sum(price['price_subtotal'] for price in price_totals if price['partner_id'][0] in child_ids)
 
     def _compute_journal_item_count(self):
         AccountMoveLine = self.env['account.move.line']
diff --git a/addons/account/models/res_currency.py b/addons/account/models/res_currency.py
index 8bf887cf521..29b4f9050ed 100644
--- a/addons/account/models/res_currency.py
+++ b/addons/account/models/res_currency.py
@@ -2,7 +2,6 @@
 # Part of Odoo. See LICENSE file for full copyright and licensing details.
 
 from odoo import api, models, fields, _
-
 from odoo.exceptions import UserError
 
 
@@ -36,3 +35,31 @@ class ResCurrency(models.Model):
         self.ensure_one()
         return bool(self.env['account.move.line'].search_count(['|', ('currency_id', '=', self.id), ('company_currency_id', '=', self.id)]))
 
+    @api.model
+    def _get_query_currency_table(self, options):
+        ''' Construct the currency table as a mapping company -> rate to convert the amount to the user's company
+        currency in a multi-company/multi-currency environment.
+        The currency_table is a small postgresql table construct with VALUES.
+        :param options: The report options.
+        :return:        The query representing the currency table.
+        '''
+
+        user_company = self.env.company
+        user_currency = user_company.currency_id
+        if options.get('multi_company', False):
+            companies = self.env.companies
+            conversion_date = options['date']['date_to']
+            currency_rates = companies.mapped('currency_id')._get_rates(user_company, conversion_date)
+        else:
+            companies = user_company
+            currency_rates = {user_currency.id: 1.0}
+
+        conversion_rates = []
+        for company in companies:
+            conversion_rates.extend((
+                company.id,
+                currency_rates[user_company.currency_id.id] / currency_rates[company.currency_id.id],
+                user_currency.decimal_places,
+            ))
+        query = '(VALUES %s) AS currency_table(company_id, rate, precision)' % ','.join('(%s, %s, %s)' for i in companies)
+        return self.env.cr.mogrify(query, conversion_rates).decode(self.env.cr.connection.encoding)
diff --git a/addons/account/report/account_invoice_report.py b/addons/account/report/account_invoice_report.py
index eb2a8633c78..4e9957c319f 100644
--- a/addons/account/report/account_invoice_report.py
+++ b/addons/account/report/account_invoice_report.py
@@ -1,6 +1,5 @@
 # -*- coding: utf-8 -*-
 
-from odoo import tools
 from odoo import models, fields, api
 
 from functools import lru_cache
@@ -69,6 +68,10 @@ class AccountInvoiceReport(models.Model):
         'res.partner': ['country_id'],
     }
 
+    @property
+    def _table_query(self):
+        return '%s %s %s' % (self._select(), self._from(), self._where())
+
     @api.model
     def _select(self):
         return '''
@@ -94,8 +97,8 @@ class AccountInvoiceReport(models.Model):
                 template.categ_id                                           AS product_categ_id,
                 line.quantity / NULLIF(COALESCE(uom_line.factor, 1) / COALESCE(uom_template.factor, 1), 0.0) * (CASE WHEN move.move_type IN ('in_invoice','out_refund','in_receipt') THEN -1 ELSE 1 END)
                                                                             AS quantity,
-                -line.balance                                               AS price_subtotal,
-                -line.balance / NULLIF(COALESCE(uom_line.factor, 1) / COALESCE(uom_template.factor, 1), 0.0)
+                -line.balance * currency_table.rate                         AS price_subtotal,
+                -line.balance / NULLIF(COALESCE(uom_line.factor, 1) / COALESCE(uom_template.factor, 1), 0.0) * currency_table.rate
                                                                             AS price_average,
                 COALESCE(partner.country_id, commercial_partner.country_id) AS country_id
         '''
@@ -113,7 +116,10 @@ class AccountInvoiceReport(models.Model):
                 LEFT JOIN uom_uom uom_template ON uom_template.id = template.uom_id
                 INNER JOIN account_move move ON move.id = line.move_id
                 LEFT JOIN res_partner commercial_partner ON commercial_partner.id = move.commercial_partner_id
-        '''
+                JOIN {currency_table} ON currency_table.company_id = line.company_id
+        '''.format(
+            currency_table=self.env['res.currency']._get_query_currency_table({'multi_company': True, 'date': {'date_to': fields.Date.today()}}),
+        )
 
     @api.model
     def _where(self):
@@ -123,66 +129,6 @@ class AccountInvoiceReport(models.Model):
                 AND NOT line.exclude_from_invoice_tab
         '''
 
-    def init(self):
-        tools.drop_view_if_exists(self.env.cr, self._table)
-        self.env.cr.execute('''
-            CREATE OR REPLACE VIEW %s AS (
-                %s %s %s
-            )
-        ''' % (
-            self._table, self._select(), self._from(), self._where()
-        ))
-
-    @api.model
-    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
-        @lru_cache(maxsize=32)  # cache to prevent a SQL query for each data point
-        def get_rate(currency_id):
-            return self.env['res.currency']._get_conversion_rate(
-                self.env['res.currency'].browse(currency_id),
-                self.env.company.currency_id,
-                self.env.company,
-                self._fields['invoice_date'].today()
-            )
-
-        # First we get the structure of the results. The results won't be correct in multi-currency,
-        # but we need this result structure.
-        # By adding 'ids:array_agg(id)' to the fields, we will be able to map the results of the
-        # second step in the structure of the first step.
-        result_ref = super(AccountInvoiceReport, self).read_group(
-            domain, fields + ['ids:array_agg(id)'], groupby, offset, limit, orderby, False
-        )
-
-        # In mono-currency, the results are correct, so we don't need the second step.
-        if len(self.env.companies.mapped('currency_id')) <= 1:
-            return result_ref
-
-        # Reset all fields needing recomputation.
-        for res_ref in result_ref:
-            for field in {'price_average', 'price_subtotal'} & set(res_ref):
-                res_ref[field] = 0.0
-
-        # Then we perform another read_group, but this time we group by 'currency_id'. This way, we
-        # are able to convert in batch in the current company currency.
-        # During the process, we fill in the result structure we got in the previous step. To make
-        # the mapping, we use the aggregated ids.
-        result = super(AccountInvoiceReport, self).read_group(
-            domain, fields + ['ids:array_agg(id)'], set(groupby) | {'company_currency_id'}, offset, limit, orderby, False
-        )
-        for res in result:
-            if res.get('company_currency_id') and self.env.company.currency_id.id != res['company_currency_id'][0]:
-                for field in {'price_average', 'price_subtotal'} & set(res):
-                    res[field] = self.env.company.currency_id.round((res[field] or 0.0) * get_rate(res['company_currency_id'][0]))
-            # Since the size of result_ref should be resonable, it should be fine to loop inside a
-            # loop.
-            for res_ref in result_ref:
-                if res.get('ids') and res_ref.get('ids') and set(res['ids']) <= set(res_ref['ids']):
-                    for field in {'price_subtotal'} & set(res_ref):
-                        res_ref[field] += res[field]
-                    for field in {'price_average'} & set(res_ref):
-                        res_ref[field] = (res_ref[field] + res[field]) / 2 if res_ref[field] else res[field]
-
-        return result_ref
-
 
 class ReportInvoiceWithoutPayment(models.AbstractModel):
     _name = 'report.account.report_invoice'
diff --git a/addons/website_crm_partner_assign/report/crm_partner_report.py b/addons/website_crm_partner_assign/report/crm_partner_report.py
index e5563e2976d..5483e3727be 100644
--- a/addons/website_crm_partner_assign/report/crm_partner_report.py
+++ b/addons/website_crm_partner_assign/report/crm_partner_report.py
@@ -1,8 +1,7 @@
 # -*- coding: utf-8 -*-
 # Part of Odoo. See LICENSE file for full copyright and licensing details.
 
-from odoo import api, fields, models
-from odoo import tools
+from odoo import fields, models
 
 
 class CrmPartnerReportAssign(models.Model):
@@ -30,14 +29,13 @@ class CrmPartnerReportAssign(models.Model):
                         'grade_id', 'parent_id', 'team_id', 'user_id'],
     }
 
-    def init(self):
+    @property
+    def _table_query(self):
         """
             CRM Lead Report
             @param cr: the current row, from the database cursor
         """
-        tools.drop_view_if_exists(self._cr, 'crm_partner_report_assign')
-        self._cr.execute("""
-            CREATE OR REPLACE VIEW crm_partner_report_assign AS (
+        return """
                 SELECT
                     coalesce(i.id, p.id - 1000000000) as id,
                     p.id as partner_id,
@@ -53,6 +51,8 @@ class CrmPartnerReportAssign(models.Model):
                     i.invoice_date as date
                 FROM
                     res_partner p
-                    left join account_invoice_report i
+                    left join ({account_invoice_report}) i
                         on (i.partner_id=p.id and i.move_type in ('out_invoice','out_refund') and i.state='open')
-            )""")
+            """.format(
+                account_invoice_report=self.env['account.invoice.report']._table_query
+            )