Can I do something like the code below?
@staticmethod
def table_query():
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
move = Move.__table__()
product_id = Transaction().context.get('product')
warehouse_id = Transaction().context.get('warehouse', -1)
warehouse_query = Location.search([
('parent', 'child_of', [warehouse_id]),
], query=True, order=[])
from_date = Transaction().context.get('from_date')
to_date = Transaction().context.get('to_date')
query = """
SELECT row_number() over (order by f.effective_date) as
id,
f.effective_date,
origin,
shipment,
f.name,
f.product,
f.default_uom AS uom,
f.frm AS from_location,
f.to_d AS to_location,
CASE f.frm::text || f.to_d::text
WHEN 'lost_foundstorage'::text THEN f.qty
WHEN 'customerstorage'::text THEN f.qty
WHEN 'supplierstorage'::text THEN f.qty
ELSE 0::double precision
END AS debit,
CASE f.frm::text || f.to_d::text
WHEN 'storagelost_found'::text THEN f.qty
WHEN 'storagecustomer'::text THEN f.qty
WHEN 'storagesupplier'::text THEN f.qty
ELSE 0::double precision
END AS credit,
0 as saldo
FROM (SELECT sm.origin,
sm.shipment,
sm.effective_date,
frm_loc.type AS frm,
to_loc.type AS to_d,
pt.name,
sm.product,
pt.default_uom,
pu.symbol AS uom,
SUM(sm.quantity / pu.factor) AS qty
FROM stock_move sm
JOIN stock_location to_loc ON to_loc.id =
sm.to_location
JOIN stock_location frm_loc ON frm_loc.id =
sm.from_location
JOIN product_product pp ON pp.id = sm.product
JOIN product_template pt ON pt.id = pp.template
JOIN product_uom pu ON pu.id = sm.uom
WHERE sm.state::text = 'done'::text
AND pt.active = TRUE
GROUP BY sm.effective_date,
frm_loc.type,
to_loc.type,
sm.product,
pt.name,
pt.default_uom,
pu.symbol,
sm.shipment,
sm.origin
ORDER BY sm.effective_date ASC,
pt.name) f
"""
return query
