[FIX] stock: correct product_qty filter in forecast inventory report

Behavior prior to the fix:

- when adding a filter on "Quantity < 0" (or > 0) on the forecast
inventory report the quantity reported becomes incorrect: for example if
there is a restock on Tuesday and a shipment on Friday that drives the
forecasted quantity into a negative, the forecasted inventory report
instead reports a negative quantity on Monday.
- the problem occurs because the view the report is based on is built
using positive and negative entries that are supposed to cancel each
other when accounting for stock items (quant).  This way the quantity is
reported as 0 before the quant is moved in the inventory (there is a
positive entry for the quant, and a negative entry for the move up to
the move effective date).  But when a filter on quantity is placed the
"cancelling" entries are not loaded and the report ends up with just the
negative value

Behavior after the fix:

- the quantities are reported as expected: with a negative on Friday,
and 0 on the days before
- to achieve this the view was modified to perform the grouping of
forecast entries inside of the view.  This way we have one row per
product / day / state and we don't risk selecting only part of the
report.
- as the view is only read in aggregate there will not be any visible
changes to its consumers

opw-2379098

closes odoo/odoo#62457

X-original-commit: 4e4584c4b00d70215713029fc1c2a96641d99aa9
Signed-off-by: Nicolas Galler <ngaller@users.noreply.github.com>
Signed-off-by: Nicolas Martinelli (nim) <nim@odoo.com>

3 files changed, 175 insertions, 87 deletions

diff --git a/addons/stock/report/report_stock_quantity.py b/addons/stock/report/report_stock_quantity.py
index 0a6d35f3c73..ea8a1472ac3 100644
--- a/addons/stock/report/report_stock_quantity.py
+++ b/addons/stock/report/report_stock_quantity.py
@@ -26,94 +26,106 @@ class ReportStockQuantity(models.Model):
         tools.drop_view_if_exists(self._cr, 'report_stock_quantity')
         query = """
 CREATE or REPLACE VIEW report_stock_quantity AS (
+WITH forecast_qty AS (
+    SELECT
+        m.id,
+        m.product_id,
+        CASE
+            WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN 'out'
+            WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN 'in'
+        END AS state,
+        m.date::date AS date,
+        CASE
+            WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN -product_qty
+            WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN product_qty
+        END AS product_qty,
+        m.company_id,
+        CASE
+            WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN whs.id
+            WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN whd.id
+        END AS warehouse_id
+    FROM
+        stock_move m
+    LEFT JOIN stock_location ls on (ls.id=m.location_id)
+    LEFT JOIN stock_location ld on (ld.id=m.location_dest_id)
+    LEFT JOIN stock_warehouse whs ON ls.parent_path like concat('%/', whs.view_location_id, '/%')
+    LEFT JOIN stock_warehouse whd ON ld.parent_path like concat('%/', whd.view_location_id, '/%')
+    LEFT JOIN product_product pp on pp.id=m.product_id
+    LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id
+    WHERE
+        pt.type = 'product' AND
+        product_qty != 0 AND
+        (whs.id IS NOT NULL OR whd.id IS NOT NULL) AND
+        (whs.id IS NULL OR whd.id IS NULL OR whs.id != whd.id) AND
+        m.state NOT IN ('cancel', 'draft', 'done')
+    UNION
+    SELECT
+        -q.id as id,
+        q.product_id,
+        'forecast' as state,
+        date.*::date,
+        q.quantity as product_qty,
+        q.company_id,
+        wh.id as warehouse_id
+    FROM
+        GENERATE_SERIES((now() at time zone 'utc')::date - interval '3month',
+        (now() at time zone 'utc')::date + interval '3 month', '1 day'::interval) date,
+        stock_quant q
+    LEFT JOIN stock_location l on (l.id=q.location_id)
+    LEFT JOIN stock_warehouse wh ON l.parent_path like concat('%/', wh.view_location_id, '/%')
+    WHERE
+        (l.usage = 'internal' AND wh.id IS NOT NULL) OR
+        l.usage = 'transit'
+    UNION
+    SELECT
+        m.id,
+        m.product_id,
+        'forecast' as state,
+        GENERATE_SERIES(
+        CASE
+            WHEN m.state = 'done' THEN (now() at time zone 'utc')::date - interval '3month'
+            ELSE m.date::date
+        END,
+        CASE
+            WHEN m.state != 'done' THEN (now() at time zone 'utc')::date + interval '3 month'
+            ELSE m.date::date - interval '1 day'
+        END, '1 day'::interval)::date date,
+        CASE
+            WHEN ((whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit') AND m.state = 'done' THEN product_qty
+            WHEN ((whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit') AND m.state = 'done' THEN -product_qty
+            WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN -product_qty
+            WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN product_qty
+        END AS product_qty,
+        m.company_id,
+        CASE
+            WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN whs.id
+            WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN whd.id
+        END AS warehouse_id
+    FROM
+        stock_move m
+    LEFT JOIN stock_location ls on (ls.id=m.location_id)
+    LEFT JOIN stock_location ld on (ld.id=m.location_dest_id)
+    LEFT JOIN stock_warehouse whs ON ls.parent_path like concat('%/', whs.view_location_id, '/%')
+    LEFT JOIN stock_warehouse whd ON ld.parent_path like concat('%/', whd.view_location_id, '/%')
+    LEFT JOIN product_product pp on pp.id=m.product_id
+    LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id
+    WHERE
+        pt.type = 'product' AND
+        product_qty != 0 AND
+        (whs.id IS NOT NULL OR whd.id IS NOT NULL) AND
+        (whs.id IS NULL or whd.id IS NULL OR whs.id != whd.id) AND
+        m.state NOT IN ('cancel', 'draft')
+) -- /forecast_qty
 SELECT
-    m.id,
-    m.product_id,
-    CASE
-        WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN 'out'
-        WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN 'in'
-    END AS state,
-    m.date::date AS date,
-    CASE
-        WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN -product_qty
-        WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN product_qty
-    END AS product_qty,
-    m.company_id,
-    CASE
-        WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN whs.id
-        WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN whd.id
-    END AS warehouse_id
-FROM
-    stock_move m
-LEFT JOIN stock_location ls on (ls.id=m.location_id)
-LEFT JOIN stock_location ld on (ld.id=m.location_dest_id)
-LEFT JOIN stock_warehouse whs ON ls.parent_path like concat('%/', whs.view_location_id, '/%')
-LEFT JOIN stock_warehouse whd ON ld.parent_path like concat('%/', whd.view_location_id, '/%')
-LEFT JOIN product_product pp on pp.id=m.product_id
-LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id
-WHERE
-    pt.type = 'product' AND
-    product_qty != 0 AND
-    (whs.id IS NOT NULL OR whd.id IS NOT NULL) AND
-    (whs.id IS NULL OR whd.id IS NULL OR whs.id != whd.id) AND
-    m.state NOT IN ('cancel', 'draft', 'done')
-UNION
-SELECT
-    -q.id as id,
-    q.product_id,
-    'forecast' as state,
-    date.*::date,
-    q.quantity as product_qty,
-    q.company_id,
-    wh.id as warehouse_id
-FROM
-    GENERATE_SERIES((now() at time zone 'utc')::date - interval '3month',
-    (now() at time zone 'utc')::date + interval '3 month', '1 day'::interval) date,
-    stock_quant q
-LEFT JOIN stock_location l on (l.id=q.location_id)
-LEFT JOIN stock_warehouse wh ON l.parent_path like concat('%/', wh.view_location_id, '/%')
-WHERE
-    (l.usage = 'internal' AND wh.id IS NOT NULL) OR
-    l.usage = 'transit'
-UNION
-SELECT
-    m.id,
-    m.product_id,
-    'forecast' as state,
-    GENERATE_SERIES(
-    CASE
-        WHEN m.state = 'done' THEN (now() at time zone 'utc')::date - interval '3month'
-        ELSE m.date::date
-    END,
-    CASE
-        WHEN m.state != 'done' THEN (now() at time zone 'utc')::date + interval '3 month'
-        ELSE m.date::date - interval '1 day'
-    END, '1 day'::interval)::date date,
-    CASE
-        WHEN ((whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit') AND m.state = 'done' THEN product_qty
-        WHEN ((whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit') AND m.state = 'done' THEN -product_qty
-        WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN -product_qty
-        WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN product_qty
-    END AS product_qty,
-    m.company_id,
-    CASE
-        WHEN (whs.id IS NOT NULL AND whd.id IS NULL) OR ls.usage = 'transit' THEN whs.id
-        WHEN (whs.id IS NULL AND whd.id IS NOT NULL) OR ld.usage = 'transit' THEN whd.id
-    END AS warehouse_id
-FROM
-    stock_move m
-LEFT JOIN stock_location ls on (ls.id=m.location_id)
-LEFT JOIN stock_location ld on (ld.id=m.location_dest_id)
-LEFT JOIN stock_warehouse whs ON ls.parent_path like concat('%/', whs.view_location_id, '/%')
-LEFT JOIN stock_warehouse whd ON ld.parent_path like concat('%/', whd.view_location_id, '/%')
-LEFT JOIN product_product pp on pp.id=m.product_id
-LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id
-WHERE
-    pt.type = 'product' AND
-    product_qty != 0 AND
-    (whs.id IS NOT NULL OR whd.id IS NOT NULL) AND
-    (whs.id IS NULL or whd.id IS NULL OR whs.id != whd.id) AND
-    m.state NOT IN ('cancel', 'draft')
+    MIN(id) as id,
+    product_id,
+    state,
+    date,
+    sum(product_qty) as product_qty,
+    company_id,
+    warehouse_id
+FROM forecast_qty
+GROUP BY product_id, state, date, company_id, warehouse_id
 );
 """
         self.env.cr.execute(query)
diff --git a/addons/stock/tests/__init__.py b/addons/stock/tests/__init__.py
index d94033cd6b6..cc3d2dfa9f5 100644
--- a/addons/stock/tests/__init__.py
+++ b/addons/stock/tests/__init__.py
@@ -17,4 +17,5 @@ from . import test_packing_neg
 from . import test_proc_rule
 from . import test_wise_operator
 from . import test_report
+from . import test_report_stock_quantity
 from . import test_report_tours
diff --git a/addons/stock/tests/test_report_stock_quantity.py b/addons/stock/tests/test_report_stock_quantity.py
new file mode 100644
index 00000000000..bce00974312
--- /dev/null
+++ b/addons/stock/tests/test_report_stock_quantity.py
@@ -0,0 +1,75 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+from odoo import fields, tests
+
+
+class TestReportStockQuantity(tests.TransactionCase):
+    def setUp(self):
+        super().setUp()
+        self.product1 = self.env['product.product'].create({
+            'name': 'Mellohi',
+            'default_code': 'C418',
+            'type': 'product',
+            'categ_id': self.env.ref('product.product_category_all').id,
+            'tracking': 'lot',
+            'barcode': 'scan_me'
+        })
+        wh = self.env['stock.warehouse'].create({
+            'name': 'Base Warehouse',
+            'code': 'TESTWH'
+        })
+        self.categ_unit = self.env.ref('uom.product_uom_categ_unit')
+        self.uom_unit = self.env['uom.uom'].search([('category_id', '=', self.categ_unit.id), ('uom_type', '=', 'reference')], limit=1)
+        self.customer_location = self.env.ref('stock.stock_location_customers')
+        self.supplier_location = self.env.ref('stock.stock_location_suppliers')
+        # replenish
+        self.move1 = self.env['stock.move'].create({
+            'name': 'test_in_1',
+            'location_id': self.supplier_location.id,
+            'location_dest_id': wh.lot_stock_id.id,
+            'product_id': self.product1.id,
+            'product_uom': self.uom_unit.id,
+            'product_uom_qty': 100.0,
+            'state': 'done',
+            'date': fields.Datetime.now(),
+        })
+        self.quant1 = self.env['stock.quant'].create({
+            'product_id': self.product1.id,
+            'location_id': wh.lot_stock_id.id,
+            'quantity': 100.0,
+        })
+        # ship
+        self.move2 = self.env['stock.move'].create({
+            'name': 'test_out_1',
+            'location_id': wh.lot_stock_id.id,
+            'location_dest_id': self.customer_location.id,
+            'product_id': self.product1.id,
+            'product_uom': self.uom_unit.id,
+            'product_uom_qty': 120.0,
+            'state': 'partially_available',
+            'date': fields.Datetime.add(fields.Datetime.now(), days=3),
+            'date_deadline': fields.Datetime.add(fields.Datetime.now(), days=3),
+        })
+        self.env['base'].flush()
+
+    def test_report_stock_quantity(self):
+        from_date = fields.Date.to_string(fields.Date.add(fields.Date.today(), days=-1))
+        to_date = fields.Date.to_string(fields.Date.add(fields.Date.today(), days=4))
+        report = self.env['report.stock.quantity'].read_group(
+            [('date', '>=', from_date), ('date', '<=', to_date), ('product_id', '=', self.product1.id)],
+            ['product_qty', 'date', 'product_id', 'state'],
+            ['date:day', 'product_id', 'state'],
+            lazy=False)
+        forecast_report = [x['product_qty'] for x in report if x['state'] == 'forecast']
+        self.assertEqual(forecast_report, [0, 100, 100, 100, -20, -20])
+
+    def test_report_stock_quantity_with_product_qty_filter(self):
+        from_date = fields.Date.to_string(fields.Date.add(fields.Date.today(), days=-1))
+        to_date = fields.Date.to_string(fields.Date.add(fields.Date.today(), days=4))
+        report = self.env['report.stock.quantity'].read_group(
+            [('product_qty', '<', 0), ('date', '>=', from_date), ('date', '<=', to_date), ('product_id', '=', self.product1.id)],
+            ['product_qty', 'date', 'product_id', 'state'],
+            ['date:day', 'product_id', 'state'],
+            lazy=False)
+        forecast_report = [x['product_qty'] for x in report if x['state'] == 'forecast']
+        self.assertEqual(forecast_report, [-20, -20])