openerp-expert-framework team mailing list archive
-
openerp-expert-framework team
-
Mailing list archive
-
Message #00869
lp:~numerigraphe/openobject-addons/trunk-stock-simplify-report-sql into lp:openobject-addons
Numérigraphe has proposed merging lp:~numerigraphe/openobject-addons/trunk-stock-simplify-report-sql into lp:openobject-addons.
Requested reviews:
OpenERP Framework Experts (openerp-expert-framework)
OpenERP Core Team (openerp)
For more details, see:
https://code.launchpad.net/~numerigraphe/openobject-addons/trunk-stock-simplify-report-sql/+merge/111058
This branch simplifies the SQL query that sums quantities by location/product/lot, using a postgres 8.4's unset() set-returning function.
Note that the way postgres behaves when several SRF are in the "select" clause is called "peculiar" by postgres devs, but as far as I know it won't be deprecated any time soon.
This branch is based upon an initial improvement by Philippe Rossi.
Lionel Sausin.
--
https://code.launchpad.net/~numerigraphe/openobject-addons/trunk-stock-simplify-report-sql/+merge/111058
Your team OpenERP Framework Experts is requested to review the proposed merge of lp:~numerigraphe/openobject-addons/trunk-stock-simplify-report-sql into lp:openobject-addons.
=== modified file 'stock/report/report_stock.py'
--- stock/report/report_stock.py 2012-05-09 13:24:01 +0000
+++ stock/report/report_stock.py 2012-06-19 16:33:15 +0000
@@ -38,40 +38,25 @@
def init(self, cr):
drop_view_if_exists(cr, 'stock_report_prodlots')
cr.execute("""
- create or replace view stock_report_prodlots as (
- select max(id) as id,
- location_id,
- product_id,
- prodlot_id,
- sum(qty) as qty
- from (
- select -max(sm.id) as id,
- sm.location_id,
- sm.product_id,
- sm.prodlot_id,
- -sum(sm.product_qty /uo.factor) as qty
- from stock_move as sm
- left join stock_location sl
- on (sl.id = sm.location_id)
- left join product_uom uo
- on (uo.id=sm.product_uom)
- where state = 'done'
- group by sm.location_id, sm.product_id, sm.product_uom, sm.prodlot_id
- union all
- select max(sm.id) as id,
- sm.location_dest_id as location_id,
- sm.product_id,
- sm.prodlot_id,
- sum(sm.product_qty /uo.factor) as qty
- from stock_move as sm
- left join stock_location sl
- on (sl.id = sm.location_dest_id)
- left join product_uom uo
- on (uo.id=sm.product_uom)
- where sm.state = 'done'
- group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id
- ) as report
- group by location_id, product_id, prodlot_id
+ create or replace view stock_report_prodlots (
+ id,
+ location_id,
+ product_id,
+ prodlot_id,
+ qty
+ ) as (
+ select max(sm.id) as id,
+ unnest(array[sm.location_id, location_dest_id]) as location_any_id,
+ sm.product_id,
+ sm.prodlot_id,
+ sum(sm.product_qty /uo.factor * unnest(array[-1,+1]))
+ from stock_move as sm
+ left join stock_location sl
+ on (sl.id = sm.location_id)
+ left join product_uom uo
+ on (uo.id=sm.product_uom)
+ where state = 'done'
+ group by location_any_id, sm.product_id, sm.product_uom, sm.prodlot_id
)""")
def unlink(self, cr, uid, ids, context={}):
@@ -94,41 +79,25 @@
def init(self, cr):
drop_view_if_exists(cr, 'stock_report_tracklots')
cr.execute("""
- create or replace view stock_report_tracklots as (
-
- select max(id) as id,
- location_id,
- product_id,
- tracking_id,
- sum(qty) as name
- from (
- select -max(sm.id) as id,
- sm.location_id,
- sm.product_id,
- sm.tracking_id,
- -sum(sm.product_qty /uo.factor) as qty
- from stock_move as sm
- left join stock_location sl
- on (sl.id = sm.location_id)
- left join product_uom uo
- on (uo.id=sm.product_uom)
- where state = 'done'
- group by sm.location_id, sm.product_id, sm.product_uom, sm.tracking_id
- union all
- select max(sm.id) as id,
- sm.location_dest_id as location_id,
- sm.product_id,
- sm.tracking_id,
- sum(sm.product_qty /uo.factor) as qty
- from stock_move as sm
- left join stock_location sl
- on (sl.id = sm.location_dest_id)
- left join product_uom uo
- on (uo.id=sm.product_uom)
- where sm.state = 'done'
- group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.tracking_id
- ) as report
- group by location_id, product_id, tracking_id
+ create or replace view stock_report_tracklots (
+ id,
+ location_id,
+ product_id,
+ tracking_id,
+ name
+ ) as (
+ select max(sm.id) as id,
+ unnest(array[sm.location_id, location_dest_id]) as location_any_id,
+ sm.product_id,
+ sm.tracking_id,
+ sum(sm.product_qty /uo.factor * unnest(array[-1,+1])) as qty
+ from stock_move as sm
+ left join stock_location sl
+ on (sl.id = sm.location_id)
+ left join product_uom uo
+ on (uo.id=sm.product_uom)
+ where state = 'done'
+ group by location_any_id, sm.product_id, sm.product_uom, sm.tracking_id
)""")
def unlink(self, cr, uid, ids, context={}):
Follow ups