initial commit 2

1 files changed, 138 insertions, 0 deletions

diff --git a/addons/stock/report/report_stock_quantity.py b/addons/stock/report/report_stock_quantity.py
new file mode 100644
index 00000000..c54f1b1a
--- /dev/null
+++ b/addons/stock/report/report_stock_quantity.py
@@ -0,0 +1,138 @@
+# -*- coding: utf-8 -*-
+# Part of Odoo. See LICENSE file for full copyright and licensing details.
+
+from odoo import fields, models, tools, api
+
+
+class ReportStockQuantity(models.Model):
+    _name = 'report.stock.quantity'
+    _auto = False
+    _description = 'Stock Quantity Report'
+
+    date = fields.Date(string='Date', readonly=True)
+    product_tmpl_id = fields.Many2one('product.template', related='product_id.product_tmpl_id')
+    product_id = fields.Many2one('product.product', string='Product', readonly=True)
+    state = fields.Selection([
+        ('forecast', 'Forecasted Stock'),
+        ('in', 'Forecasted Receipts'),
+        ('out', 'Forecasted Deliveries'),
+    ], string='State', readonly=True)
+    product_qty = fields.Float(string='Quantity', readonly=True)
+    move_ids = fields.One2many('stock.move', readonly=True)
+    company_id = fields.Many2one('res.company', readonly=True)
+    warehouse_id = fields.Many2one('stock.warehouse', readonly=True)
+
+    def init(self):
+        tools.drop_view_if_exists(self._cr, 'report_stock_quantity')
+        query = """
+CREATE or REPLACE VIEW report_stock_quantity AS (
+SELECT
+    MIN(id) as id,
+    product_id,
+    state,
+    date,
+    sum(product_qty) as product_qty,
+    company_id,
+    warehouse_id
+FROM (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 ALL
+    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 ALL
+    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')) AS forecast_qty
+GROUP BY product_id, state, date, company_id, warehouse_id
+);
+"""
+        self.env.cr.execute(query)
+
+    @api.model
+    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
+        for i in range(len(domain)):
+            if domain[i][0] == 'product_tmpl_id' and domain[i][1] in ('=', 'in'):
+                tmpl = self.env['product.template'].browse(domain[i][2])
+                # Avoid the subquery done for the related, the postgresql will plan better with the SQL view
+                # and then improve a lot the performance for the forecasted report of the product template.
+                domain[i] = ('product_id', 'in', tmpl.with_context(active_test=False).product_variant_ids.ids)
+        return super().read_group(domain, fields, groupby, offset, limit, orderby, lazy)