[FIX] pos_sale: performances and UOM

Performance issue
-----------------

In databases with a lot of POS order lines, the Sales Analysis report is
quite slow. This impacts performances in the report itself and in the
form views of products and product templates. Indeed, they use the
`sale.report` model to compute the `sales_count` field on products.

opw-1987368

Bug on UOM
----------

- Activate Units of Measure
- Create a product A, set its UOM to 'g' (grams)
- Sell one product in the POS

In the Sales Analysis report, the amount is multiplied by 1000.

opw-1978462

Bug on taxes
------------

See issue #24746

All issues originates from the SQL view which is quite complex for no
good reason. Indeed, in v12 the fields `price_subtotal` and
`price_subtotal_incl` are stored and contain the necessary amounts.

closes odoo/odoo#33304

Signed-off-by: Nicolas Martinelli (nim) <nim@odoo.com>


Co-authored-by: Nicolas Martinelli <nim@odoo.com>

1 files changed, 4 insertions, 11 deletions

diff --git a/addons/pos_sale/report/sale_report.py b/addons/pos_sale/report/sale_report.py
index 2c147e97083..ee8bac1b856 100644
--- a/addons/pos_sale/report/sale_report.py
+++ b/addons/pos_sale/report/sale_report.py
@@ -14,11 +14,6 @@ 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
-                       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)'''
-
         res = super(SaleReport, self)._query(with_clause, fields, groupby, from_clause)
 
         select_ = '''
@@ -29,10 +24,10 @@ class SaleReport(models.Model):
             sum(l.qty * u.factor) AS qty_delivered,
             CASE WHEN pos.state = 'invoiced' THEN sum(qty) ELSE 0 END AS qty_invoiced,
             CASE WHEN pos.state != 'invoiced' THEN sum(qty) ELSE 0 END AS qty_to_invoice,
-            (COALESCE(COALESCE((l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)) )) + (l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)) ) * pol_tax.amount), (l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)) ))), 1.0)) / MIN(CASE COALESCE(pos.currency_rate, 0) WHEN 0 THEN 1.0 ELSE pos.currency_rate END) AS price_total,
-            (COALESCE((l.qty * u.factor * l.price_unit * ((1 - COALESCE(l.discount / 100, 0)) )), 1.0)) / MIN(CASE COALESCE(pos.currency_rate, 0) WHEN 0 THEN 1.0 ELSE pos.currency_rate END) AS price_subtotal,
-            (CASE WHEN pos.state != 'invoiced' THEN ((l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)))) + (l.price_unit * l.qty * u.factor * (1 - COALESCE(l.discount / 100, 0)) * pol_tax.amount)) ELSE 0 END) / MIN(CASE COALESCE(pos.currency_rate, 0) WHEN 0 THEN 1.0 ELSE pos.currency_rate END) AS amount_to_invoice,
-            (CASE WHEN pos.state = 'invoiced' THEN ((l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)) )) + (l.price_unit * l.qty * u.factor * (1 - COALESCE(l.discount / 100, 0)) * pol_tax.amount)) ELSE 0 END) / MIN(CASE COALESCE(pos.currency_rate, 0) WHEN 0 THEN 1.0 ELSE pos.currency_rate END) AS amount_invoiced,
+            SUM(l.price_subtotal_incl) / MIN(CASE COALESCE(pos.currency_rate, 0) WHEN 0 THEN 1.0 ELSE pos.currency_rate END) AS price_total,
+            SUM(l.price_subtotal) / MIN(CASE COALESCE(pos.currency_rate, 0) WHEN 0 THEN 1.0 ELSE pos.currency_rate END) AS price_subtotal,
+            (CASE WHEN pos.state != 'invoiced' THEN SUM(l.price_subtotal_incl) ELSE 0 END) / MIN(CASE COALESCE(pos.currency_rate, 0) WHEN 0 THEN 1.0 ELSE pos.currency_rate END) AS amount_to_invoice,
+            (CASE WHEN pos.state = 'invoiced' THEN SUM(l.price_subtotal_incl) ELSE 0 END) / MIN(CASE COALESCE(pos.currency_rate, 0) WHEN 0 THEN 1.0 ELSE pos.currency_rate END) AS amount_invoiced,
             count(*) AS nbr,
             pos.name AS name,
             pos.date_order AS date,
@@ -69,7 +64,6 @@ class SaleReport(models.Model):
             pos_order_line l
                   join pos_order pos on (l.order_id=pos.id)
                   left join res_partner partner ON (pos.partner_id = partner.id OR pos.partner_id = NULL)
-                    left join pol_tax pol_tax on pol_tax.pol_id=l.id
                     left join product_product p on (l.product_id=p.id)
                     left join product_template t on (p.product_tmpl_id=t.id)
                     LEFT JOIN uom_uom u ON (u.id=t.uom_id)
@@ -96,7 +90,6 @@ class SaleReport(models.Model):
             p.product_tmpl_id,
             partner.country_id,
             partner.commercial_partner_id,
-            pol_tax.amount,
             u.factor,
             config.crm_team_id
         '''