openerp-expert-framework team mailing list archive

[Numérigraphe <informatique@xxxxxxxxxxxxxxxx>]

You have been requested to review the proposed merge of lp:~numerigraphe/openobject-addons/trunk-stock-simplify-report-sql into lp:openobject-addons.

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={}):