Merge lp:~yann-papouin/ocb-addons/7.0-bug-1169074-pricelist-category-depth into lp:ocb-addons

[Yann Papouin]

Automatically derived from https://code.launchpad.net/~yann-papouin/openobject-addons/7.0-bug-1169074-pricelist-category-depth for https://code.launchpad.net/~openerp/openobject-addons/7.0.

[Holger Brunn (Therp) (hbrunn)]

same as 6.1-ocb

review: Approve (code review)

[Yannick Vaucher @ Camptocamp (yvaucher-c2c)]

Thanks for the job done

LGTM as clone of https://code.launchpad.net/~yann-papouin/ocb-addons/6.1-bug-1169074-pricelist-category-depth/+merge/184510

review: Approve (code review, no test)

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

As you needed to rewrite the query, isn't in an opportunity to remove the injection of the ids in the query and use params in execute()? Even though categ_ids are supposed to be safe here, the correct way is to use the query parameters.

review: Needs Information

[Yann Papouin (yann-papouin)]

It does not seems to be simple to parameterize this query.
Maybe this change could be made in another proposal and this one could be merged now ?

[Holger Brunn (Therp) (hbrunn)]

Development for 7.0 has moved to github on https://github.com/OCA/ocb - please move your merge proposal there if it is still valid.

(I close and reject this in order to have a cleaner overview for 6.1 MPs which indeed have to be done on launchpad)

review: Disapprove

Unmerged revisions

9986. By Yann Papouin on 2014-03-09

[FIX] Pricelist doesn't take category depth into account

=== modified file 'product/pricelist.py'
--- product/pricelist.py	2014-03-06 19:09:06 +0000
+++ product/pricelist.py	2014-03-10 09:56:21 +0000
@@ -219,8 +219,10 @@
                 categ_ids = _create_parent_category_list(categ_id, [categ_id])
                 if categ_ids:
                     categ_where = '(categ_id IN (' + ','.join(map(str, categ_ids)) + '))'
+                    categ_where_i = '(i.categ_id IN (' + ','.join(map(str, categ_ids)) + '))'
                 else:
                     categ_where = '(categ_id IS NULL)'
+                    categ_where_i = '(i.categ_id IS NULL)'
 
                 if partner:
                     partner_where = 'base <> -2 OR %s IN (SELECT name FROM product_supplierinfo WHERE product_id = %s) '
@@ -228,20 +230,41 @@
                 else:
                     partner_where = 'base <> -2 '
                     partner_args = ()
-
-                cr.execute(
-                    'SELECT i.*, pl.currency_id '
-                    'FROM product_pricelist_item AS i, '
-                        'product_pricelist_version AS v, product_pricelist AS pl '
-                    'WHERE (product_tmpl_id IS NULL OR product_tmpl_id = %s) '
-                        'AND (product_id IS NULL OR product_id = %s) '
-                        'AND (' + categ_where + ' OR (categ_id IS NULL)) '
-                        'AND (' + partner_where + ') '
-                        'AND price_version_id = %s '
-                        'AND (min_quantity IS NULL OR min_quantity <= %s) '
-                        'AND i.price_version_id = v.id AND v.pricelist_id = pl.id '
-                    'ORDER BY sequence',
-                    (tmpl_id, product_id) + partner_args + (pricelist_version_ids[0], qty))
+                  
+                query = (  
+                'SELECT '
+                    'i.*, pl.currency_id , p.* '
+                'FROM '
+                    'product_pricelist_item AS i '
+                    'JOIN product_pricelist_version AS v '
+                        'ON i.price_version_id = v.id '
+                    'JOIN product_pricelist AS pl '
+                        'ON v.pricelist_id = pl.id '
+                    'LEFT OUTER JOIN ( '
+                        'WITH RECURSIVE subtree(depth, categ_id, parent_id, name) AS ( '
+                                'SELECT 0, id, parent_id, name FROM product_category WHERE parent_id is NULL '
+                            'UNION '
+                                'SELECT depth+1, m.id, m.parent_id, m.name '
+                                'FROM subtree t, product_category m '
+                                'WHERE m.parent_id = t.categ_id '
+                        ') '
+                        'SELECT * '
+                        'FROM subtree '
+                        'WHERE (' + categ_where + ' OR (categ_id IS NULL)) ' 
+                    ') AS p '
+                        'on i.categ_id = p.categ_id '
+                'WHERE '
+                    '(product_tmpl_id IS NULL OR product_tmpl_id = %s) ' 
+                    'AND (product_id IS NULL OR product_id = %s) '
+                    'AND (' + categ_where_i + ' OR (i.categ_id IS NULL)) ' 
+                    'AND (' + partner_where + ') ' 
+                    'AND price_version_id = %s ' 
+                    'AND (min_quantity IS NULL OR min_quantity <= %s) '
+                'ORDER BY ' 
+                    'sequence, depth desc '  
+                ) % ((tmpl_id, product_id) + partner_args + (pricelist_version_ids[0], qty))
+                              
+                cr.execute(query)
                 res1 = cr.dictfetchall()
                 uom_price_already_computed = False
                 for res in res1: