Merge lp:~openerp-dev/openobject-addons/7.0-pos-performance-cbi into lp:openobject-addons/7.0
- 7.0-pos-performance-cbi
- Merge into 7.0
Status: | Needs review |
---|---|
Proposed branch: | lp:~openerp-dev/openobject-addons/7.0-pos-performance-cbi |
Merge into: | lp:openobject-addons/7.0 |
Diff against target: |
730 lines (+334/-194) 4 files modified
point_of_sale/static/src/js/models.js (+0/-17) product/pricelist.py (+299/-153) product/product.py (+33/-22) product/product_pricelist_demo.yml (+2/-2) |
To merge this branch: | bzr merge lp:~openerp-dev/openobject-addons/7.0-pos-performance-cbi |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Eneldo Serrata (community) | Needs Fixing | ||
Fabien (Open ERP) | Disapprove | ||
Olivier Dony (Odoo) | surface | Needs Fixing | |
Review via email: mp+148396@code.launchpad.net |
Commit message
Description of the change
Fixes the algorithm for pricelist calculation to drastically improve performance.
This is still a work-in-progress (API needs to be corrected not to break the stable API), but currently brings down the time to load 28k products in the POS interfaces (that caches the prices) from 15min to 12sec.
Any feedback appreciated.
qdp (OpenERP) (qdp) wrote : | # |
let me be a reviewer, this piece of code is soooo exciting o_O
Olivier Dony (Odoo) (odo-openerp) wrote : | # |
Here are my comments after a surface scan (I did not test):
1/ First, thanks for restoring the compatibility with the original 7.0 API. However the way price_get_multi now works is not equivalent to the original code. As I understand it, price_get_multi() was working as follows:
- IN: `pricelist_ids`: list of pricelists to compute
- IN: `products_
- OUT: { prod_id1: {pricelist_id1: price, pricelist_id2: price},
(...)
where the returned prices will match the qty and partner that was provided *in the same tuple*.
Yes this API is nonsense (especially the 'item_id') and due to debatable past refactorings, but that's how it is :-(
With the current state of this merge proposal it seems that `products_
I understand that it is hard to optimize the query for fetching the `product.
Could we instead do the following:
- the price_get_batch() method becomes responsible for computing the price for several products for a single `qty` and `partner`, and all the code moves in it. The signature would be:
def price_get_
- the price_get_multi() method keeps the original 7.0 signature (no extra parameter), but it groups all entries from `products_
- this way the API still works exactly as before and the optimization works too because the POS will call it with qty=1 and partner=None for all products, so there will only be one group to pass to price_get_batch().
2/ what is the point of adding the extra argument "pricelist_
4/ the big `if` with the recursive query should probably be moved to a separate private method that abstracts the logic of computing res_per_prod. i.e.:
res_per_prod = self._pricelist
The _pricelist_table() method could then be split into 2 methods _pricelist_
5/ (not in your code, was already present) l.433-449: the logic to find the proper supplier for a given product should not be hardcoded, the product.product model has a `seller_id` function field that provides this info, so it should be...
Kevin Shenk (batonac) wrote : | # |
If this code is fixed and production ready, when will it be merged into the main edition?
van der Essen Frédéric (OpenERP) (fva-openerp) wrote : | # |
Any estimation on when this is going to be merged ? This bug is a showstopper for a _lot_ of users.
Fabien (Open ERP) (fp-tinyerp) wrote : | # |
This was an important improvement, but a better fix has been made in trunk-website-al.
Olivier Dony (Odoo) (odo-openerp) wrote : | # |
The optimized WITH RECURSIVE query and the workaround for seller_info_id field are still quite useful on top of the future trunk improvement [1], so setting back to Needs Review so that at least those changes are reviewed at some point for trunk.
The branch is also usable as is for 7.0 in the mean time (and probably 6.1), for customers affected by slow performance during POS initial load.
[1] http://
Eneldo Serrata (eneldoserrata) wrote : | # |
Hello Oliver i try to patch but i get conflict i'm using Versión 7.0-20131224-002412 any recommendation thanks
Unmerged revisions
- 8605. By Chris Biersbach (OpenERP)
-
[FIX] Now the fall-back to 8.3 is also functional again
- 8604. By Chris Biersbach (OpenERP)
-
[IMP] Added a comment
- 8603. By Chris Biersbach (OpenERP)
-
[IMP] Confusing read and search is a surefire sign you do not drink enough coffee...
- 8602. By Chris Biersbach (OpenERP)
-
[IMP] Removed some remaining debug code
- 8601. By Chris Biersbach (OpenERP)
-
[IMP] This takes into account the comments by ODO... various refactorings and small fixes
- 8600. By Chris Biersbach (OpenERP)
-
[IMP] Changed some method signatures and call to be in sync with the situation before my fixes
- 8599. By Chris Biersbach (OpenERP)
-
[FIX] small bug in the fall-back code
- 8598. By Chris Biersbach (OpenERP)
-
[IMP] Refactored the codeto include the fallback gor PostgreSQL prior to 8.4
- 8597. By Chris Biersbach (OpenERP)
-
[IMP] last small glitch, now all tests should go through. Also removed the debug code in the JS
- 8596. By Chris Biersbach (OpenERP)
-
[IMP] Another small improvement
Preview Diff
1 | === modified file 'point_of_sale/static/src/js/models.js' |
2 | --- point_of_sale/static/src/js/models.js 2013-01-28 17:18:00 +0000 |
3 | +++ point_of_sale/static/src/js/models.js 2013-03-01 15:13:44 +0000 |
4 | @@ -94,11 +94,9 @@ |
5 | // loads all the needed data on the sever. returns a deferred indicating when all the data has loaded. |
6 | load_server_data: function(){ |
7 | var self = this; |
8 | - |
9 | var loaded = self.fetch('res.users',['name','company_id'],[['id','=',this.session.uid]]) |
10 | .then(function(users){ |
11 | self.set('user',users[0]); |
12 | - |
13 | return self.fetch('res.company', |
14 | [ |
15 | 'currency_id', |
16 | @@ -113,16 +111,12 @@ |
17 | [['id','=',users[0].company_id[0]]]); |
18 | }).then(function(companies){ |
19 | self.set('company',companies[0]); |
20 | - |
21 | return self.fetch('res.partner',['contact_address'],[['id','=',companies[0].partner_id[0]]]); |
22 | }).then(function(company_partners){ |
23 | self.get('company').contact_address = company_partners[0].contact_address; |
24 | - |
25 | return self.fetch('res.currency',['symbol','position','rounding','accuracy'],[['id','=',self.get('company').currency_id[0]]]); |
26 | }).then(function(currencies){ |
27 | - console.log('Currency:',currencies[0]); |
28 | self.set('currency',currencies[0]); |
29 | - |
30 | return self.fetch('product.uom', null, null); |
31 | }).then(function(units){ |
32 | self.set('units',units); |
33 | @@ -131,23 +125,18 @@ |
34 | units_by_id[units[i].id] = units[i]; |
35 | } |
36 | self.set('units_by_id',units_by_id); |
37 | - |
38 | return self.fetch('product.packaging', null, null); |
39 | }).then(function(packagings){ |
40 | self.set('product.packaging',packagings); |
41 | - |
42 | return self.fetch('res.users', ['name','ean13'], [['ean13', '!=', false]]); |
43 | }).then(function(users){ |
44 | self.set('user_list',users); |
45 | - |
46 | return self.fetch('res.partner', ['name','ean13'], [['ean13', '!=', false]]); |
47 | }).then(function(partners){ |
48 | self.set('partner_list',partners); |
49 | - |
50 | return self.fetch('account.tax', ['amount', 'price_include', 'type']); |
51 | }).then(function(taxes){ |
52 | self.set('taxes', taxes); |
53 | - |
54 | return self.fetch( |
55 | 'pos.session', |
56 | ['id', 'journal_ids','name','user_id','config_id','start_at','stop_at'], |
57 | @@ -155,7 +144,6 @@ |
58 | ); |
59 | }).then(function(sessions){ |
60 | self.set('pos_session', sessions[0]); |
61 | - |
62 | return self.fetch( |
63 | 'pos.config', |
64 | ['name','journal_ids','shop_id','journal_id', |
65 | @@ -172,7 +160,6 @@ |
66 | self.iface_vkeyboard = !!pos_config.iface_vkeyboard; |
67 | self.iface_self_checkout = !!pos_config.iface_self_checkout; |
68 | self.iface_cashdrawer = !!pos_config.iface_cashdrawer; |
69 | - |
70 | return self.fetch('sale.shop',[],[['id','=',pos_config.shop_id[0]]]); |
71 | }).then(function(shops){ |
72 | self.set('shop',shops[0]); |
73 | @@ -180,11 +167,9 @@ |
74 | return self.fetch('product.packaging',['ean','product_id']); |
75 | }).then(function(packagings){ |
76 | self.db.add_packagings(packagings); |
77 | - |
78 | return self.fetch('pos.category', ['id','name','parent_id','child_id','image']) |
79 | }).then(function(categories){ |
80 | self.db.add_categories(categories); |
81 | - |
82 | return self.fetch( |
83 | 'product.product', |
84 | ['name', 'list_price','price','pos_categ_id', 'taxes_id', 'ean13', |
85 | @@ -194,7 +179,6 @@ |
86 | ); |
87 | }).then(function(products){ |
88 | self.db.add_products(products); |
89 | - |
90 | return self.fetch( |
91 | 'account.bank.statement', |
92 | ['account_id','currency','journal_id','state','name','user_id','pos_session_id'], |
93 | @@ -209,7 +193,6 @@ |
94 | return self.fetch('account.journal', undefined, [['id','in', journals]]); |
95 | }).then(function(journals){ |
96 | self.set('journals',journals); |
97 | - |
98 | // associate the bank statements with their journals. |
99 | var bank_statements = self.get('bank_statements'); |
100 | for(var i = 0, ilen = bank_statements.length; i < ilen; i++){ |
101 | |
102 | === modified file 'product/pricelist.py' |
103 | --- product/pricelist.py 2012-12-06 14:56:32 +0000 |
104 | +++ product/pricelist.py 2013-03-01 15:13:44 +0000 |
105 | @@ -125,93 +125,114 @@ |
106 | 'active': lambda *a: 1, |
107 | "currency_id": _get_currency |
108 | } |
109 | - |
110 | - #def price_get_multi(self, cr, uid, product_ids, context=None): |
111 | - def price_get_multi(self, cr, uid, pricelist_ids, products_by_qty_by_partner, context=None): |
112 | - """multi products 'price_get'. |
113 | - @param pricelist_ids: |
114 | - @param products_by_qty: |
115 | - @param partner: |
116 | - @param context: { |
117 | - 'date': Date of the pricelist (%Y-%m-%d),} |
118 | - @return: a dict of dict with product_id as key and a dict 'price by pricelist' as value |
119 | - """ |
120 | - |
121 | - def _create_parent_category_list(id, lst): |
122 | - if not id: |
123 | - return [] |
124 | - parent = product_category_tree.get(id) |
125 | - if parent: |
126 | - lst.append(parent) |
127 | - return _create_parent_category_list(parent, lst) |
128 | - else: |
129 | - return lst |
130 | - # _create_parent_category_list |
131 | - |
132 | - if context is None: |
133 | - context = {} |
134 | - |
135 | - date = time.strftime('%Y-%m-%d') |
136 | - if 'date' in context: |
137 | - date = context['date'] |
138 | - |
139 | - currency_obj = self.pool.get('res.currency') |
140 | - product_obj = self.pool.get('product.product') |
141 | + |
142 | + def _pricelist_table(self, cr, uid, pricelist_ids, pricelist_version_ids, prod_ids, products_dict, partner, qty, context=None): |
143 | + """ Calls a different method based on the version of PostgreSQL that is installed """ |
144 | + |
145 | + # Recursive queries are only supported in PostgreSQL versions 8.4 or newer |
146 | + if cr._cnx.server_version >= 80400: |
147 | + return self._pricelist_table_pg84(cr, uid, pricelist_ids, pricelist_version_ids, prod_ids, partner, qty, context=context) |
148 | + # fall back to the non-recursive query |
149 | + else: |
150 | + return self._pricelist_table_pg83(cr, uid, pricelist_ids, pricelist_version_ids, prod_ids, products_dict, partner, qty, context=context) |
151 | + |
152 | + def _pricelist_table_pg84(self, cr, uid, pricelist_ids, pricelist_version_ids, prod_ids, partner, qty, context=None): |
153 | + """ Version for PostgreSQL >= 8.4 """ |
154 | + |
155 | + res_per_prod = {} |
156 | + |
157 | + if partner: |
158 | + partner_where = 'base <> -2 OR %s IN (SELECT name FROM product_supplierinfo WHERE product_id = pp.product_tmpl_id) ' |
159 | + partner_args = (partner,) |
160 | + else: |
161 | + partner_where = 'base <> -2 ' |
162 | + partner_args = () |
163 | + |
164 | + for pricelist_id in pricelist_ids: |
165 | + |
166 | + cr.execute( |
167 | + # -- categorytree return (category id, category id + ids of parents categories) |
168 | + 'WITH RECURSIVE categorytree(id, parent_ids) AS ( ' |
169 | + 'SELECT id, ARRAY[id]::int[] ' |
170 | + 'FROM product_category ' |
171 | + 'WHERE parent_id IS NULL ' |
172 | + 'UNION ALL ' |
173 | + 'SELECT c.id, ct.parent_ids || c.id ' |
174 | + 'FROM product_category c ' |
175 | + 'JOIN categorytree ct ON ct.id = c.parent_id ' |
176 | + ') ' |
177 | + 'SELECT pp.id AS requested_product_id, i.*, pl.currency_id, pl.id AS pricelist ' |
178 | + 'FROM product_product pp ' |
179 | + 'LEFT JOIN product_template pt ON (pp.product_tmpl_id = pt.id) ' |
180 | + 'LEFT JOIN product_pricelist_item AS i ON ( ' |
181 | + '((i.product_tmpl_id IS NOT NULL AND i.product_tmpl_id = pp.product_tmpl_id)' |
182 | + 'OR (i.product_id IS NOT NULL AND i.product_id = pp.id) ' |
183 | + 'OR (i.categ_id IS NOT NULL AND i.categ_id IN (SELECT unnest(parent_ids) FROM categorytree WHERE id = pt.categ_id)) ' |
184 | + 'OR (i.product_tmpl_id IS NULL AND i.product_id IS NULL AND i.categ_id IS NULL) ' |
185 | + ' )' |
186 | + 'AND (' + partner_where + ') ' |
187 | + ') ' |
188 | + 'LEFT JOIN product_pricelist_version AS v ON (i.price_version_id = v.id) ' |
189 | + 'LEFT JOIN product_pricelist AS pl ON (v.pricelist_id = pl.id) ' |
190 | + 'WHERE pp.id in %s ' |
191 | + 'AND price_version_id = %s ' |
192 | + 'AND (min_quantity IS NULL OR min_quantity <= %s) ' |
193 | + 'ORDER BY sequence', |
194 | + partner_args + (tuple(prod_ids),) + (pricelist_version_ids[0], qty)) |
195 | + |
196 | + for row in cr.dictfetchall(): |
197 | + res_per_prod.setdefault(row['requested_product_id'], []) |
198 | + res_per_prod[row['requested_product_id']].append(row) |
199 | + |
200 | + return res_per_prod |
201 | + |
202 | + def _pricelist_table_pg83(self, cr, uid, pricelist_ids, pricelist_version_ids, prod_ids, products_dict, partner, qty, context=None): |
203 | + """ Version for PostgreSQL <= 8.3 """ |
204 | + |
205 | + res_per_prod = {} |
206 | + |
207 | product_category_obj = self.pool.get('product.category') |
208 | - product_uom_obj = self.pool.get('product.uom') |
209 | - supplierinfo_obj = self.pool.get('product.supplierinfo') |
210 | - price_type_obj = self.pool.get('product.price.type') |
211 | - |
212 | - # product.pricelist.version: |
213 | - if not pricelist_ids: |
214 | - pricelist_ids = self.pool.get('product.pricelist').search(cr, uid, [], context=context) |
215 | - |
216 | - pricelist_version_ids = self.pool.get('product.pricelist.version').search(cr, uid, [ |
217 | - ('pricelist_id', 'in', pricelist_ids), |
218 | - '|', |
219 | - ('date_start', '=', False), |
220 | - ('date_start', '<=', date), |
221 | - '|', |
222 | - ('date_end', '=', False), |
223 | - ('date_end', '>=', date), |
224 | - ]) |
225 | - if len(pricelist_ids) != len(pricelist_version_ids): |
226 | - raise osv.except_osv(_('Warning!'), _("At least one pricelist has no active version !\nPlease create or activate one.")) |
227 | - |
228 | - # product.product: |
229 | - product_ids = [i[0] for i in products_by_qty_by_partner] |
230 | - #products = dict([(item['id'], item) for item in product_obj.read(cr, uid, product_ids, ['categ_id', 'product_tmpl_id', 'uos_id', 'uom_id'])]) |
231 | - products = product_obj.browse(cr, uid, product_ids, context=context) |
232 | - products_dict = dict([(item.id, item) for item in products]) |
233 | - |
234 | - # product.category: |
235 | - product_category_ids = product_category_obj.search(cr, uid, []) |
236 | - product_categories = product_category_obj.read(cr, uid, product_category_ids, ['parent_id']) |
237 | - product_category_tree = dict([(item['id'], item['parent_id'][0]) for item in product_categories if item['parent_id']]) |
238 | - |
239 | - results = {} |
240 | - for product_id, qty, partner in products_by_qty_by_partner: |
241 | - for pricelist_id in pricelist_ids: |
242 | - price = False |
243 | - |
244 | - tmpl_id = products_dict[product_id].product_tmpl_id and products_dict[product_id].product_tmpl_id.id or False |
245 | - |
246 | - categ_id = products_dict[product_id].categ_id and products_dict[product_id].categ_id.id or False |
247 | + |
248 | + if partner: |
249 | + partner_where = 'base <> -2 OR %s IN (SELECT name FROM product_supplierinfo WHERE product_id = %s) ' |
250 | + else: |
251 | + partner_where = 'base <> -2 ' |
252 | + |
253 | + for pricelist_id in pricelist_ids: |
254 | + |
255 | + product_category_ids = product_category_obj.search(cr, uid, []) |
256 | + product_categories = product_category_obj.read(cr, uid, product_category_ids, ['parent_id']) |
257 | + product_category_tree = dict([(item['id'], item['parent_id'][0]) for item in product_categories if item['parent_id']]) |
258 | + |
259 | + def _create_parent_category_list(id, lst): |
260 | + if not id: |
261 | + return [] |
262 | + parent = product_category_tree.get(id) |
263 | + if parent: |
264 | + lst.append(parent) |
265 | + return _create_parent_category_list(parent, lst) |
266 | + else: |
267 | + return lst |
268 | + |
269 | + for product_id in prod_ids: |
270 | + |
271 | + tmpl_id = products_dict[product_id]['product_tmpl_id'] and products_dict[product_id]['product_tmpl_id'][0] or False |
272 | + |
273 | + categ_id = products_dict[product_id]['categ_id'] and products_dict[product_id]['categ_id'][0] or False |
274 | categ_ids = _create_parent_category_list(categ_id, [categ_id]) |
275 | + |
276 | if categ_ids: |
277 | categ_where = '(categ_id IN (' + ','.join(map(str, categ_ids)) + '))' |
278 | else: |
279 | categ_where = '(categ_id IS NULL)' |
280 | |
281 | if partner: |
282 | - partner_where = 'base <> -2 OR %s IN (SELECT name FROM product_supplierinfo WHERE product_id = %s) ' |
283 | partner_args = (partner, tmpl_id) |
284 | else: |
285 | - partner_where = 'base <> -2 ' |
286 | partner_args = () |
287 | - |
288 | + |
289 | cr.execute( |
290 | - 'SELECT i.*, pl.currency_id ' |
291 | + 'SELECT i.*, pl.currency_id , pl.id AS pricelist ' |
292 | 'FROM product_pricelist_item AS i, ' |
293 | 'product_pricelist_version AS v, product_pricelist AS pl ' |
294 | 'WHERE (product_tmpl_id IS NULL OR product_tmpl_id = %s) ' |
295 | @@ -223,87 +244,212 @@ |
296 | 'AND i.price_version_id = v.id AND v.pricelist_id = pl.id ' |
297 | 'ORDER BY sequence', |
298 | (tmpl_id, product_id) + partner_args + (pricelist_version_ids[0], qty)) |
299 | - res1 = cr.dictfetchall() |
300 | - uom_price_already_computed = False |
301 | - for res in res1: |
302 | - if res: |
303 | - if res['base'] == -1: |
304 | - if not res['base_pricelist_id']: |
305 | - price = 0.0 |
306 | - else: |
307 | - price_tmp = self.price_get(cr, uid, |
308 | - [res['base_pricelist_id']], product_id, |
309 | - qty, context=context)[res['base_pricelist_id']] |
310 | - ptype_src = self.browse(cr, uid, res['base_pricelist_id']).currency_id.id |
311 | - uom_price_already_computed = True |
312 | - price = currency_obj.compute(cr, uid, ptype_src, res['currency_id'], price_tmp, round=False) |
313 | - elif res['base'] == -2: |
314 | - # this section could be improved by moving the queries outside the loop: |
315 | - where = [] |
316 | - if partner: |
317 | - where = [('name', '=', partner) ] |
318 | - sinfo = supplierinfo_obj.search(cr, uid, |
319 | - [('product_id', '=', tmpl_id)] + where) |
320 | + |
321 | + res_per_prod.setdefault(product_id, []) |
322 | + row = cr.dictfetchone() |
323 | + row['requested_product_id'] = product_id |
324 | + res_per_prod[product_id].append(row) |
325 | + |
326 | + return res_per_prod |
327 | + |
328 | + def price_get_batch(self, cr, uid, pricelist_ids, prod_ids, qty, partner=None, context=None): |
329 | + """ Return the price for the products in prod_ids according to the pricelists, qty and partner provided |
330 | + This method should only be called directly when multiple products' prices are needed and the quantity and partner are the same for all the products. |
331 | + @param context: { |
332 | + 'date': Date of the pricelist (%Y-%m-%d), used to compute the correct pricelist version} |
333 | + @return: a dict of dict with product_id as key and a dict 'price by pricelist' as value |
334 | + """ |
335 | + |
336 | + if context is None: |
337 | + context = {} |
338 | + |
339 | + date = context.get('date') and context['date'] or time.strftime('%Y-%m-%d') |
340 | + |
341 | + if not pricelist_ids: |
342 | + pricelist_ids = self.search(cr, uid, [], context=context) |
343 | + |
344 | + pricelist_version_ids = self.pool.get('product.pricelist.version').search(cr, uid, [ |
345 | + ('pricelist_id', 'in', pricelist_ids), |
346 | + '|', |
347 | + ('date_start', '=', False), |
348 | + ('date_start', '<=', date), |
349 | + '|', |
350 | + ('date_end', '=', False), |
351 | + ('date_end', '>=', date), |
352 | + ]) |
353 | + |
354 | + if type(pricelist_version_ids) == int: |
355 | + pricelist_version_ids = [pricelist_version_ids] |
356 | + |
357 | + if len(pricelist_ids) != len(pricelist_version_ids): |
358 | + raise osv.except_osv(_('Warning!'), _("At least one pricelist has no active version !\nPlease create or activate one!\n")) |
359 | + |
360 | + currency_obj = self.pool.get('res.currency') |
361 | + product_obj = self.pool.get('product.product') |
362 | + product_uom_obj = self.pool.get('product.uom') |
363 | + supplierinfo_obj = self.pool.get('product.supplierinfo') |
364 | + price_type_obj = self.pool.get('product.price.type') |
365 | + |
366 | + product_data = product_obj.read(cr, uid, prod_ids, ['id', 'uos_id', 'uom_id', 'price_margin', 'price_extra', 'list_price', 'standard_price', 'product_tmpl_id', 'categ_id'], context=context) |
367 | + products_dict = {} |
368 | + |
369 | + for product in product_data: |
370 | + products_dict[product['id']] = product |
371 | + |
372 | + pricelists = {} |
373 | + suppliers = {} |
374 | + price_types = {} |
375 | + categ_ids_dict = {} |
376 | + currencies = {} |
377 | + |
378 | + results = {} |
379 | + |
380 | + res_per_prod = self._pricelist_table(cr, uid, pricelist_ids, pricelist_version_ids, prod_ids, products_dict, partner, qty, context=context) |
381 | + |
382 | + for product_id in prod_ids: |
383 | + price = False |
384 | + |
385 | + uom_price_already_computed = False |
386 | + |
387 | + chosen_pricelist = False |
388 | + |
389 | + for res in res_per_prod[product_id]: |
390 | + if res and res['product_id'] in (None, product_id): |
391 | + if res['base'] == -1: |
392 | + if not res['base_pricelist_id']: |
393 | price = 0.0 |
394 | - if sinfo: |
395 | - qty_in_product_uom = qty |
396 | - product_default_uom = product_obj.read(cr, uid, [product_id], ['uom_id'])[0]['uom_id'][0] |
397 | - supplier = supplierinfo_obj.browse(cr, uid, sinfo, context=context)[0] |
398 | - seller_uom = supplier.product_uom and supplier.product_uom.id or False |
399 | - if seller_uom and product_default_uom and product_default_uom != seller_uom: |
400 | - uom_price_already_computed = True |
401 | - qty_in_product_uom = product_uom_obj._compute_qty(cr, uid, product_default_uom, qty, to_uom_id=seller_uom) |
402 | - cr.execute('SELECT * ' \ |
403 | - 'FROM pricelist_partnerinfo ' \ |
404 | - 'WHERE suppinfo_id IN %s' \ |
405 | - 'AND min_quantity <= %s ' \ |
406 | - 'ORDER BY min_quantity DESC LIMIT 1', (tuple(sinfo),qty_in_product_uom,)) |
407 | - res2 = cr.dictfetchone() |
408 | - if res2: |
409 | - price = res2['price'] |
410 | else: |
411 | - price_type = price_type_obj.browse(cr, uid, int(res['base'])) |
412 | + price_tmp = self.price_get(cr, uid, |
413 | + [res['base_pricelist_id']], product_id, |
414 | + qty, context=context)[res['base_pricelist_id']] |
415 | + if not pricelists.get(res['base_pricelist_id']): |
416 | + pricelists[res['base_pricelist_id']] = self.browse(cr, uid, res['base_pricelist_id']) |
417 | + ptype_src = pricelists[res['base_pricelist_id']].currency_id |
418 | uom_price_already_computed = True |
419 | - price = currency_obj.compute(cr, uid, |
420 | - price_type.currency_id.id, res['currency_id'], |
421 | - product_obj.price_get(cr, uid, [product_id], |
422 | - price_type.field, context=context)[product_id], round=False, context=context) |
423 | - |
424 | - if price is not False: |
425 | - price_limit = price |
426 | - price = price * (1.0+(res['price_discount'] or 0.0)) |
427 | - price = rounding(price, res['price_round']) #TOFIX: rounding with tools.float_rouding |
428 | - price += (res['price_surcharge'] or 0.0) |
429 | - if res['price_min_margin']: |
430 | - price = max(price, price_limit+res['price_min_margin']) |
431 | - if res['price_max_margin']: |
432 | - price = min(price, price_limit+res['price_max_margin']) |
433 | - break |
434 | + if not currencies.get(res['currency_id']): |
435 | + currencies[res['currency_id']] = currency_obj.browse(cr, uid, res['currency_id']) |
436 | + price = currency_obj.compute(cr, uid, ptype_src, currencies[res['currency_id']], price_tmp, round=False) |
437 | + # this section could be improved by moving the queries outside the loop: |
438 | + elif res['base'] == -2: |
439 | + # /!\ this second read is currently needed, because including seller_info_id in the batch read leads to a huge performance overhead |
440 | + # (the call to name_get slows things |
441 | + products_dict[product_id]['seller_info_id'] = product_obj.read(cr, uid, product_id, ['seller_info_id'], context=context)['seller_info_id'] |
442 | + sinfo = products_dict[product_id]['seller_info_id'] and products_dict[product_id]['seller_info_id'][0] or False |
443 | + price = 0.0 |
444 | + if sinfo: |
445 | + qty_in_product_uom = qty |
446 | + product_default_uom = products_dict[product_id]['uom_id'] and products_dict[product_id]['uom_id'][0] or False |
447 | + if not suppliers.get(sinfo): |
448 | + suppliers[sinfo] = supplierinfo_obj.browse(cr, uid, sinfo, context=context) |
449 | + supplier = suppliers[sinfo] |
450 | + seller_uom = supplier.product_uom and supplier.product_uom.id or False |
451 | + if seller_uom and product_default_uom and product_default_uom != seller_uom: |
452 | + uom_price_already_computed = True |
453 | + qty_in_product_uom = product_uom_obj._compute_qty(cr, uid, product_default_uom, qty, to_uom_id=seller_uom) |
454 | + |
455 | + cr.execute('SELECT * ' \ |
456 | + 'FROM pricelist_partnerinfo ' \ |
457 | + 'WHERE suppinfo_id IN %s' \ |
458 | + 'AND min_quantity <= %s ' \ |
459 | + 'ORDER BY min_quantity DESC LIMIT 1', (tuple([sinfo]),qty_in_product_uom,)) |
460 | + res2 = cr.dictfetchone() |
461 | + if res2: |
462 | + price = res2['price'] |
463 | |
464 | else: |
465 | - # False means no valid line found ! But we may not raise an |
466 | - # exception here because it breaks the search |
467 | - price = False |
468 | - |
469 | - if price: |
470 | - results['item_id'] = res['id'] |
471 | - if 'uom' in context and not uom_price_already_computed: |
472 | - product = products_dict[product_id] |
473 | - uom = product.uos_id or product.uom_id |
474 | - price = product_uom_obj._compute_price(cr, uid, uom.id, price, context['uom']) |
475 | - |
476 | - if results.get(product_id): |
477 | - results[product_id][pricelist_id] = price |
478 | + index = int(res['base']) |
479 | + if not price_types.get(index): |
480 | + price_types[index] = price_type_obj.browse(cr, uid, index) |
481 | + price_type = price_types[index] |
482 | + uom_price_already_computed = True |
483 | + currency_id = res['currency_id'] |
484 | + if not currencies.get(currency_id): |
485 | + currencies[currency_id] = currency_obj.browse(cr, uid, currency_id) |
486 | + price = currency_obj.compute(cr, uid, |
487 | + price_type.currency_id, currencies[currency_id], |
488 | + product_obj.price_get(cr, uid, [product_id], |
489 | + price_type.field, context=context, products=[products_dict[product_id]])[product_id], round=False, context=context) |
490 | + |
491 | + if price is not False: |
492 | + price_limit = price |
493 | + price = price * (1.0+(res['price_discount'] or 0.0)) |
494 | + price = rounding(price, res['price_round']) #TOFIX: rounding with tools.float_rouding |
495 | + price += (res['price_surcharge'] or 0.0) |
496 | + if res['price_min_margin']: |
497 | + price = max(price, price_limit+res['price_min_margin']) |
498 | + if res['price_max_margin']: |
499 | + price = min(price, price_limit+res['price_max_margin']) |
500 | + chosen_pricelist = res['pricelist'] |
501 | + break |
502 | + |
503 | else: |
504 | - results[product_id] = {pricelist_id: price} |
505 | + # False means no valid line found ! But we may not raise an |
506 | + # exception here because it breaks the search |
507 | + price = False |
508 | + |
509 | + if price: |
510 | + if 'uom' in context and not uom_price_already_computed: |
511 | + product = products_dict[product_id] |
512 | + uom = product['uos_id'] or product['uom_id'] |
513 | + price = product_uom_obj._compute_price(cr, uid, uom.id, price, context['uom']) |
514 | + if not results.get(product_id): |
515 | + results[product_id] = {} |
516 | + results[product_id][chosen_pricelist] = price |
517 | |
518 | return results |
519 | |
520 | def price_get(self, cr, uid, ids, prod_id, qty, partner=None, context=None): |
521 | - res_multi = self.price_get_multi(cr, uid, pricelist_ids=ids, products_by_qty_by_partner=[(prod_id, qty, partner)], context=context) |
522 | - res = res_multi[prod_id] |
523 | - res.update({'item_id': {ids[-1]: res_multi.get('item_id', ids[-1])}}) |
524 | - return res |
525 | + """ Return the price for one product according to the pricelists, qty and partner provided |
526 | + This method should be called when a single product's price is needed. |
527 | + |
528 | + @return: a dict 'price by pricelist' |
529 | + """ |
530 | + |
531 | + if isinstance(prod_id, int): |
532 | + prod_id = [prod_id] |
533 | + |
534 | + if isinstance(ids, int): |
535 | + ids = [ids] |
536 | + |
537 | + return self.price_get_batch(cr, uid, ids, prod_id, qty, partner=partner, context=context)[prod_id[0]] |
538 | + |
539 | + def price_get_multi(self, cr, uid, pricelist_ids, products_by_qty_by_partner, context=None): |
540 | + """ Returns the prices for the products in products_by_qty_by_partner according to the pricelists in pricelist_ids |
541 | + and the provided qty and partner. |
542 | + This method should be called when multiple products' prices are needed and the quantities and partners differ. |
543 | + |
544 | + @param pricelist_ids: The pricelists to be considered in the computations |
545 | + @param products_by_qty_by_partner: a list of tuples (product_id, qty, partner_id), specifying for each product the quantity and partner |
546 | + @param context: { |
547 | + 'date': Date of the pricelist (%Y-%m-%d), used to compute the correct pricelist version} |
548 | + @return: a dict of dict with product_id as key and a dict 'price by pricelist' as value |
549 | + """ |
550 | + |
551 | + groups = {} |
552 | + res= {} |
553 | + |
554 | + # split products_by_qty_by_partner into groups with equal qty and partner |
555 | + # structure: dictionary with qty as keys of dictionary with partner as keys of lists of ids |
556 | + for product, qty, partner in products_by_qty_by_partner: |
557 | + if not groups.get(qty): |
558 | + groups[qty] = {} |
559 | + if not groups[qty].get(partner): |
560 | + groups[qty][partner] = [] |
561 | + # all products should be unique, but it can't hurt to do this test anyway |
562 | + if not product in groups[qty][partner]: |
563 | + groups[qty][partner].append(product) |
564 | + |
565 | + # call price_get_batch for every group, merge results into res dictionary |
566 | + for qty in groups: |
567 | + for partner in groups[qty]: |
568 | + current_res = self.price_get_batch(cr, uid, ids, groups[qty][partner], qty, partner=partner, context=context) |
569 | + for product in current_res: |
570 | + if not res.get(product): |
571 | + res[product] = {} |
572 | + for pricelist in current_res[product]: |
573 | + res[product][pricelist] = current_res[product][pricelist] |
574 | + |
575 | + return res |
576 | |
577 | product_pricelist() |
578 | |
579 | @@ -405,13 +551,13 @@ |
580 | _columns = { |
581 | 'name': fields.char('Rule Name', size=64, help="Explicit rule name for this pricelist line."), |
582 | 'price_version_id': fields.many2one('product.pricelist.version', 'Price List Version', required=True, select=True, ondelete='cascade'), |
583 | - 'product_tmpl_id': fields.many2one('product.template', 'Product Template', ondelete='cascade', help="Specify a template if this rule only applies to one product template. Keep empty otherwise."), |
584 | - 'product_id': fields.many2one('product.product', 'Product', ondelete='cascade', help="Specify a product if this rule only applies to one product. Keep empty otherwise."), |
585 | - 'categ_id': fields.many2one('product.category', 'Product Category', ondelete='cascade', help="Specify a product category if this rule only applies to products belonging to this category or its children categories. Keep empty otherwise."), |
586 | + 'product_tmpl_id': fields.many2one('product.template', 'Product Template', ondelete='cascade', help="Specify a template if this rule only applies to one product template. Keep empty otherwise.", select=1), |
587 | + 'product_id': fields.many2one('product.product', 'Product', ondelete='cascade', help="Specify a product if this rule only applies to one product. Keep empty otherwise.", select=1), |
588 | + 'categ_id': fields.many2one('product.category', 'Product Category', ondelete='cascade', help="Specify a product category if this rule only applies to products belonging to this category or its children categories. Keep empty otherwise.", select=1), |
589 | |
590 | - 'min_quantity': fields.integer('Min. Quantity', required=True, help="Specify the minimum quantity that needs to be bought/sold for the rule to apply."), |
591 | + 'min_quantity': fields.integer('Min. Quantity', required=True, help="Specify the minimum quantity that needs to be bought/sold for the rule to apply.", select=1), |
592 | 'sequence': fields.integer('Sequence', required=True, help="Gives the order in which the pricelist items will be checked. The evaluation gives highest priority to lowest sequence and stops as soon as a matching item is found."), |
593 | - 'base': fields.selection(_price_field_get, 'Based on', required=True, size=-1, help="Base price for computation."), |
594 | + 'base': fields.selection(_price_field_get, 'Based on', required=True, size=-1, help="Base price for computation.", select=1), |
595 | 'base_pricelist_id': fields.many2one('product.pricelist', 'Other Pricelist'), |
596 | |
597 | 'price_surcharge': fields.float('Price Surcharge', |
598 | @@ -422,11 +568,11 @@ |
599 | help="Sets the price so that it is a multiple of this value.\n" \ |
600 | "Rounding is applied after the discount and before the surcharge.\n" \ |
601 | "To have prices that end in 9.99, set rounding 10, surcharge -0.01" \ |
602 | - ), |
603 | + ,select=1), |
604 | 'price_min_margin': fields.float('Min. Price Margin', |
605 | digits_compute= dp.get_precision('Product Price'), help='Specify the minimum amount of margin over the base price.'), |
606 | 'price_max_margin': fields.float('Max. Price Margin', |
607 | - digits_compute= dp.get_precision('Product Price'), help='Specify the maximum amount of margin over the base price.'), |
608 | + digits_compute= dp.get_precision('Product Price'), help='Specify the maximum amount of margin over the base price.', select=1), |
609 | 'company_id': fields.related('price_version_id','company_id',type='many2one', |
610 | readonly=True, relation='res.company', string='Company', store=True) |
611 | } |
612 | |
613 | === modified file 'product/product.py' |
614 | --- product/product.py 2013-01-22 05:00:57 +0000 |
615 | +++ product/product.py 2013-03-01 15:13:44 +0000 |
616 | @@ -21,6 +21,7 @@ |
617 | |
618 | import math |
619 | import re |
620 | +import time |
621 | |
622 | from _common import rounding |
623 | |
624 | @@ -413,22 +414,24 @@ |
625 | return res |
626 | |
627 | def _product_price(self, cr, uid, ids, name, arg, context=None): |
628 | + |
629 | res = {} |
630 | if context is None: |
631 | context = {} |
632 | + if isinstance(ids, int): |
633 | + ids = [ids] |
634 | quantity = context.get('quantity') or 1.0 |
635 | pricelist = context.get('pricelist', False) |
636 | partner = context.get('partner', False) |
637 | + pricelist_obj = self.pool.get('product.pricelist') |
638 | if pricelist: |
639 | - for id in ids: |
640 | - try: |
641 | - price = self.pool.get('product.pricelist').price_get(cr,uid,[pricelist], id, quantity, partner=partner, context=context)[pricelist] |
642 | - except: |
643 | - price = 0.0 |
644 | - res[id] = price |
645 | + res = pricelist_obj.price_get_batch(cr, uid, [pricelist], ids, quantity, partner=partner, context=context) |
646 | for id in ids: |
647 | - res.setdefault(id, 0.0) |
648 | - return res |
649 | + res.setdefault(id, {pricelist: 0.0}) |
650 | + to_return = {} |
651 | + for product, prices in res.iteritems(): |
652 | + to_return[product] = prices[pricelist] |
653 | + return to_return |
654 | |
655 | def _get_product_available_func(states, what): |
656 | def _product_available(self, cr, uid, ids, name, arg, context=None): |
657 | @@ -681,10 +684,15 @@ |
658 | result = self.name_get(cr, user, ids, context=context) |
659 | return result |
660 | |
661 | - # |
662 | - # Could be overrided for variants matrices prices |
663 | - # |
664 | - def price_get(self, cr, uid, ids, ptype='list_price', context=None): |
665 | + def price_get(self, cr, uid, ids, ptype='list_price', context=None, products=None): |
666 | + # /!\ Attention: products contains dictionaries that result from calls to search, not browse records |
667 | + |
668 | + if type(ids) is int: |
669 | + ids = [ids] |
670 | + |
671 | + if products is None: |
672 | + products = self.read(cr, uid, ids, ['id', ptype, 'price_margin', 'price_extra', 'uom_id', 'uos_id'], context=context) |
673 | + |
674 | if context is None: |
675 | context = {} |
676 | |
677 | @@ -695,22 +703,25 @@ |
678 | |
679 | res = {} |
680 | product_uom_obj = self.pool.get('product.uom') |
681 | - for product in self.browse(cr, uid, ids, context=context): |
682 | - res[product.id] = product[ptype] or 0.0 |
683 | + for product in products: |
684 | + res[product['id']] = product[ptype] or 0.0 |
685 | if ptype == 'list_price': |
686 | - res[product.id] = (res[product.id] * (product.price_margin or 1.0)) + \ |
687 | - product.price_extra |
688 | + res[product['id']] = (res[product['id']] * (product['price_margin'] or 1.0)) + \ |
689 | + product['price_extra'] |
690 | if 'uom' in context: |
691 | - uom = product.uom_id or product.uos_id |
692 | - res[product.id] = product_uom_obj._compute_price(cr, uid, |
693 | - uom.id, res[product.id], context['uom']) |
694 | + uom = product['uom_id'] or product['uos_id'] |
695 | + if isinstance(uom, tuple): |
696 | + uom_id = uom and uom[0] |
697 | + else: |
698 | + uom_id = uom.id |
699 | + res[product['id']] = product_uom_obj._compute_price(cr, uid, |
700 | + uom_id, res[product['id']], context['uom']) |
701 | # Convert from price_type currency to asked one |
702 | if 'currency_id' in context: |
703 | # Take the price_type currency from the product field |
704 | # This is right cause a field cannot be in more than one currency |
705 | - res[product.id] = self.pool.get('res.currency').compute(cr, uid, price_type_currency_id, |
706 | - context['currency_id'], res[product.id],context=context) |
707 | - |
708 | + res[product['id']] = self.pool.get('res.currency').compute(cr, uid, price_type_currency_id, |
709 | + context['currency_id'], res[product['id']],context=context) |
710 | return res |
711 | |
712 | def copy(self, cr, uid, id, default=None, context=None): |
713 | |
714 | === modified file 'product/product_pricelist_demo.yml' |
715 | --- product/product_pricelist_demo.yml 2012-11-29 22:26:45 +0000 |
716 | +++ product/product_pricelist_demo.yml 2013-03-01 15:13:44 +0000 |
717 | @@ -52,11 +52,11 @@ |
718 | base: -2 |
719 | - |
720 | !record {model: pricelist.partnerinfo, id: supplier_pricelist0_product_pc2}: |
721 | - suppinfo_id: product_supplierinfo_2 |
722 | + suppinfo_id: product_supplierinfo_1 |
723 | min_quantity: 3 |
724 | price: 785 |
725 | - |
726 | !record {model: pricelist.partnerinfo, id: supplier_pricelist1_product_pc2}: |
727 | - suppinfo_id: product_supplierinfo_2 |
728 | + suppinfo_id: product_supplierinfo_1 |
729 | min_quantity: 1 |
730 | price: 790 |
The WITH RECURSIVE query requires postgres 8.4, so we might keep the original query for OpenERP 6.1 or slightly improve it. Without it the POS can still load 28k products in 40s.