[FIX] account: invoice analysis multicurrency + various imp

Task 2206699

* All the amounts are now expressed in the company currency
  - The amounts are taken from debit/credit on the aml
  - When opening the report for multiple companies, convert in the
    current company's currency
* New default view:
  - Group by month instead of category
  - Line chart instead of bar chart
  - Filter on customers
* Remove useless fields:
  - name
  - invoice_partner_bank_id
  - invoice_payment_term_id
  - residual (including taxes)
  - amount_total (including taxes)
  - nbr_lines as it is a generic feature
* Rename currency_id to the more correct company_currency_id
* Remove useless group_by clause from the SQL query

closes odoo/odoo#47066

Related: odoo/upgrade#1021
Signed-off-by: oco-odoo <oco-odoo@users.noreply.github.com>

2 files changed, 32 insertions, 76 deletions

diff --git a/addons/account/report/account_invoice_report.py b/addons/account/report/account_invoice_report.py
index d52ea97175f..03d41a20e72 100644
--- a/addons/account/report/account_invoice_report.py
+++ b/addons/account/report/account_invoice_report.py
@@ -3,6 +3,8 @@
 from odoo import tools
 from odoo import models, fields, api
 
+from functools import lru_cache
+
 
 class AccountInvoiceReport(models.Model):
     _name = "account.invoice.report"
@@ -13,10 +15,9 @@ class AccountInvoiceReport(models.Model):
 
     # ==== Invoice fields ====
     move_id = fields.Many2one('account.move', readonly=True)
-    name = fields.Char('Invoice #', readonly=True)
     journal_id = fields.Many2one('account.journal', string='Journal', readonly=True)
     company_id = fields.Many2one('res.company', string='Company', readonly=True)
-    currency_id = fields.Many2one('res.currency', string='Currency', readonly=True)
+    company_currency_id = fields.Many2one('res.currency', string='Company Currency', readonly=True)
     partner_id = fields.Many2one('res.partner', string='Partner', readonly=True)
     commercial_partner_id = fields.Many2one('res.partner', string='Partner Company', help="Commercial Entity")
     country_id = fields.Many2one('res.country', string="Country")
@@ -39,11 +40,6 @@ class AccountInvoiceReport(models.Model):
     ], string='Payment Status', readonly=True)
     fiscal_position_id = fields.Many2one('account.fiscal.position', string='Fiscal Position', readonly=True)
     invoice_date = fields.Date(readonly=True, string="Invoice Date")
-    invoice_payment_term_id = fields.Many2one('account.payment.term', string='Payment Terms', readonly=True)
-    invoice_partner_bank_id = fields.Many2one('res.partner.bank', string='Bank Account', readonly=True)
-    nbr_lines = fields.Integer(string='Line Count', readonly=True)
-    residual = fields.Float(string='Due Amount', readonly=True)
-    amount_total = fields.Float(string='Total', readonly=True)
 
     # ==== Invoice line fields ====
     quantity = fields.Float(string='Product Quantity', readonly=True)
@@ -84,9 +80,8 @@ class AccountInvoiceReport(models.Model):
                 line.analytic_account_id,
                 line.journal_id,
                 line.company_id,
-                COALESCE(line.currency_id, line.company_currency_id)        AS currency_id,
+                line.company_currency_id,
                 line.partner_id AS commercial_partner_id,
-                move.name,
                 move.state,
                 move.move_type,
                 move.partner_id,
@@ -95,39 +90,14 @@ class AccountInvoiceReport(models.Model):
                 move.payment_state,
                 move.invoice_date,
                 move.invoice_date_due,
-                move.invoice_payment_term_id,
-                move.invoice_partner_bank_id,
-                move.amount_residual_signed                                 AS residual,
-                ROUND(
-                  line.price_total / COALESCE(
-                    (SELECT rate FROM res_currency_rate cr WHERE
-                      cr.currency_id = line.currency_id AND
-                      cr.company_id = line.company_id AND
-                      cr.name <= COALESCE(line.date,NOW())
-                    ORDER BY cr.name DESC
-                    LIMIT 1)
-                    ,1),
-                  COALESCE((SELECT decimal_places
-                   FROM res_currency rc INNER JOIN res_currency_rate cr ON
-                    rc.id = cr.currency_id
-                   WHERE cr.currency_id = (COALESCE(line.currency_id, line.company_currency_id)) AND
-                         cr.company_id = line.company_id
-                   LIMIT 1
-                   ),2))
-                    *
-                   (CASE WHEN move.amount_total_signed < 0
-                        THEN -1
-                        ELSE 1
-                    END)                                                    AS amount_total,
                 uom_template.id                                             AS product_uom_id,
                 template.categ_id                                           AS product_categ_id,
-                SUM(line.quantity / NULLIF(COALESCE(uom_line.factor, 1) * COALESCE(uom_template.factor, 1), 0.0))
+                line.quantity / NULLIF(COALESCE(uom_line.factor, 1) / COALESCE(uom_template.factor, 1), 0.0)
                                                                             AS quantity,
-                -SUM(line.balance)                                          AS price_subtotal,
-                -SUM(line.balance / NULLIF(COALESCE(uom_line.factor, 1) * COALESCE(uom_template.factor, 1), 0.0))
+                -line.balance                                               AS price_subtotal,
+                -line.balance / NULLIF(COALESCE(uom_line.factor, 1) / COALESCE(uom_template.factor, 1), 0.0)
                                                                             AS price_average,
-                COALESCE(partner.country_id, commercial_partner.country_id) AS country_id,
-                1                                                           AS nbr_lines
+                COALESCE(partner.country_id, commercial_partner.country_id) AS country_id
         '''
 
     @api.model
@@ -153,47 +123,33 @@ class AccountInvoiceReport(models.Model):
                 AND NOT line.exclude_from_invoice_tab
         '''
 
-    @api.model
-    def _group_by(self):
-        return '''
-            GROUP BY
-                line.id,
-                line.move_id,
-                line.product_id,
-                line.account_id,
-                line.analytic_account_id,
-                line.journal_id,
-                line.company_id,
-                line.currency_id,
-                line.partner_id,
-                move.name,
-                move.state,
-                move.move_type,
-                move.amount_residual_signed,
-                move.amount_total_signed,
-                move.partner_id,
-                move.invoice_user_id,
-                move.fiscal_position_id,
-                move.payment_state,
-                move.invoice_date,
-                move.invoice_date_due,
-                move.invoice_payment_term_id,
-                move.invoice_partner_bank_id,
-                uom_template.id,
-                template.categ_id,
-                COALESCE(partner.country_id, commercial_partner.country_id)
-        '''
-
     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 %s
+                %s %s %s
             )
         ''' % (
-            self._table, self._select(), self._from(), self._where(), self._group_by()
+            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()
+            )
+        result = super(AccountInvoiceReport, self).read_group(domain, fields, set(groupby) | {'company_currency_id'}, offset, limit, orderby, False)
+        for res in result:
+            if 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] * get_rate(res['company_currency_id'][0]))
+        return result
+
 
 class ReportInvoiceWithoutPayment(models.AbstractModel):
     _name = 'report.account.report_invoice'
diff --git a/addons/account/report/account_invoice_report_view.xml b/addons/account/report/account_invoice_report_view.xml
index e8851a8ad6c..a0f62db2da8 100644
--- a/addons/account/report/account_invoice_report_view.xml
+++ b/addons/account/report/account_invoice_report_view.xml
@@ -6,8 +6,8 @@
          <field name="model">account.invoice.report</field>
          <field name="arch" type="xml">
              <pivot string="Invoices Analysis" disable_linking="True">
-                 <field name="product_categ_id" type="row"/>
-                 <field name="invoice_date" type="col"/>
+                 <field name="product_categ_id" type="col"/>
+                 <field name="invoice_date" type="row"/>
                  <field name="price_subtotal" type="measure"/>
              </pivot>
          </field>
@@ -17,7 +17,7 @@
          <field name="name">account.invoice.report.graph</field>
          <field name="model">account.invoice.report</field>
          <field name="arch" type="xml">
-             <graph string="Invoices Analysis">
+             <graph string="Invoices Analysis" type="line">
                  <field name="product_categ_id"/>
                  <field name="price_subtotal" type="measure"/>
              </graph>
@@ -112,7 +112,7 @@
         <field name="name">Invoices Analysis</field>
         <field name="res_model">account.invoice.report</field>
         <field name="view_mode">graph,pivot</field>
-        <field name="context">{'search_default_current':1, 'res_partner_search_mode': 'supplier', 'group_by':[], 'group_by_no_leaf':1, 'search_default_year': 1}</field>
+        <field name="context">{'search_default_current':1, 'search_default_supplier': 1, 'group_by':['invoice_date'], 'group_by_no_leaf':1}</field>
         <field name="search_view_id" ref="view_account_invoice_report_search"/>
         <field name="help">From this report, you can have an overview of the amount invoiced from your vendors. The search tool can also be used to personalise your Invoices reports and so, match this analysis to your needs.</field>
     </record>
@@ -120,7 +120,7 @@
         <field name="name">Invoices Analysis</field>
         <field name="res_model">account.invoice.report</field>
         <field name="view_mode">graph,pivot</field>
-        <field name="context">{'search_default_current':1, 'res_partner_search_mode': 'customer', 'group_by':[], 'group_by_no_leaf':1, 'search_default_year': 1}</field>
+        <field name="context">{'search_default_current':1, 'search_default_customer': 1, 'group_by':['invoice_date'], 'group_by_no_leaf':1}</field>
         <field name="search_view_id" ref="view_account_invoice_report_search"/>
         <field name="help">From this report, you can have an overview of the amount invoiced to your customers. The search tool can also be used to personalise your Invoices reports and so, match this analysis to your needs.</field>
     </record>