1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
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)
|
