[FIX] point_of_sale: fix ignored products from POS order report

- Open a POS session
- In the same POS order, add the following order lines:

     Product   |   Quantity   |   Price
  ----------------------------------------
    Product A  |      1       |  $ 1.00
    Product A  |     -1       |  $ 2.00
    Product B  |      1       |  $ 5.00

- The order has a total of $ 4.00
- Pay and validate order
- Go to Point of Sale > Reporting > Orders and switch to pivot view
The order has a total of 5.00

This is due to the fact that products, for which the sum of quantities is equal
to 0, are not retrieved by the SQL request.
(i.e. Sum of quantities for Product A = 1 - 1 = 0)
This is caused by the HAVING section that is there to prevent a division by 0
when computing average_price, which depends on the sum of quantities of the product.

The solution is to remove the HAVING section and to set average_price to NULL
when the sum of quantities for a product is equal to 0.

opw-2369023

closes odoo/odoo#63126

X-original-commit: 80d44f1735a49acf3a43c5c4638523d4434ac537
Signed-off-by: Anh Thao PHAM <kitan191@users.noreply.github.com>

1 files changed, 5 insertions, 9 deletions

diff --git a/addons/point_of_sale/report/pos_order_report.py b/addons/point_of_sale/report/pos_order_report.py
index 95a69f79adb..1ad50a021ec 100644
--- a/addons/point_of_sale/report/pos_order_report.py
+++ b/addons/point_of_sale/report/pos_order_report.py
@@ -46,7 +46,10 @@ class PosOrderReport(models.Model):
                 SUM(l.qty * l.price_unit / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) AS price_sub_total,
                 SUM(ROUND((l.qty * l.price_unit) * (100 - l.discount) / 100 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END, cu.decimal_places)) AS price_total,
                 SUM((l.qty * l.price_unit) * (l.discount / 100) / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) AS total_discount,
-                (SUM(l.qty*l.price_unit / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END)/SUM(l.qty * u.factor))::decimal AS average_price,
+                CASE
+                    WHEN SUM(l.qty * u.factor) = 0 THEN NULL
+                    ELSE (SUM(l.qty*l.price_unit / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END)/SUM(l.qty * u.factor))::decimal
+                END AS average_price,
                 SUM(cast(to_char(date_trunc('day',s.date_order) - date_trunc('day',s.create_date),'DD') AS INT)) AS delay_validation,
                 s.id as order_id,
                 s.partner_id AS partner_id,
@@ -88,12 +91,6 @@ class PosOrderReport(models.Model):
                 ps.config_id
         """
 
-    def _having(self):
-        return """
-            HAVING
-                SUM(l.qty * u.factor) != 0
-        """
-
     def init(self):
         tools.drop_view_if_exists(self._cr, self._table)
         self._cr.execute("""
@@ -101,7 +98,6 @@ class PosOrderReport(models.Model):
                 %s
                 %s
                 %s
-                %s
             )
-        """ % (self._table, self._select(), self._from(), self._group_by(),self._having())
+        """ % (self._table, self._select(), self._from(), self._group_by())
         )