16
.. i18n: Suppose sale is a variable on a record of the sale.order object related to the 'sale_order' table. You can acquire such a variable doing this.::
18
.. i18n: sale = self.browse(cr, uid, ID)
21
Suppose sale is a variable on a record of the sale.order object related to the 'sale_order' table. You can acquire such a variable doing this.::
23
sale = self.browse(cr, uid, ID)
25
.. i18n: (where cr is the current row, from the database cursor, uid is the current user's ID for security checks, and ID is the sale order's ID or list of IDs if we want more than one)
28
(where cr is the current row, from the database cursor, uid is the current user's ID for security checks, and ID is the sale order's ID or list of IDs if we want more than one)
30
.. i18n: Suppose you want to get: the country name of the first contact of a partner related to the ID sale order. You can do the following in Open ERP::
32
.. i18n: country_name = sale.partner_id.address[0].country_id.name
35
Suppose you want to get: the country name of the first contact of a partner related to the ID sale order. You can do the following in Open ERP::
37
country_name = sale.partner_id.address[0].country_id.name
39
.. i18n: If you want to write the same thing in traditional SQL development, it will be in python: (we suppose cr is the cursor on the database, with psycopg)
42
If you want to write the same thing in traditional SQL development, it will be in python: (we suppose cr is the cursor on the database, with psycopg)
44
.. i18n: .. code-block:: python
46
.. i18n: cr.execute('select partner_id from sale_order where id=%d', (ID,))
47
.. i18n: partner_id = cr.fetchone()[0]
48
.. i18n: cr.execute('select country_id from res_partner_address where partner_id=%d', (partner_id,))
49
.. i18n: country_id = cr.fetchone()[0]
50
.. i18n: cr.execute('select name from res_country where id=%d', (country_id,))
51
.. i18n: del partner_id
52
.. i18n: del country_id
53
.. i18n: country_name = cr.fetchone()[0]
56
.. code-block:: python
58
cr.execute('select partner_id from sale_order where id=%d', (ID,))
59
partner_id = cr.fetchone()[0]
60
cr.execute('select country_id from res_partner_address where partner_id=%d', (partner_id,))
61
country_id = cr.fetchone()[0]
62
cr.execute('select name from res_country where id=%d', (country_id,))
65
country_name = cr.fetchone()[0]
67
.. i18n: Of course you can do better if you develop smartly in SQL, using joins or subqueries. But you have to be smart and most of the time you will not be able to make such improvements:
70
Of course you can do better if you develop smartly in SQL, using joins or subqueries. But you have to be smart and most of the time you will not be able to make such improvements:
72
.. i18n: * Maybe some parts are in others functions
73
.. i18n: * There may be a loop in different elements
74
.. i18n: * You have to use intermediate variables like country_id
77
* Maybe some parts are in others functions
78
* There may be a loop in different elements
79
* You have to use intermediate variables like country_id
81
.. i18n: The first operation as an object call is much better for several reasons:
84
The first operation as an object call is much better for several reasons:
86
.. i18n: * It uses objects facilities and works with modules inheritances, overload, ...
87
.. i18n: * It's simpler, more explicit and uses less code
88
.. i18n: * It's much more efficient as you will see in the following examples
89
.. i18n: * Some fields do not directly correspond to a SQL field (e.g.: function fields in Python)
92
* It uses objects facilities and works with modules inheritances, overload, ...
93
* It's simpler, more explicit and uses less code
94
* It's much more efficient as you will see in the following examples
95
* Some fields do not directly correspond to a SQL field (e.g.: function fields in Python)
97
.. i18n: Example 2 - Prefetching
98
.. i18n: -----------------------
101
Example 2 - Prefetching
102
-----------------------
104
.. i18n: Suppose that later in the code, in another function, you want to access the name of the partner associated to your sale order. You can use this::
106
.. i18n: partner_name = sale.partner_id.name
109
Suppose that later in the code, in another function, you want to access the name of the partner associated to your sale order. You can use this::
111
partner_name = sale.partner_id.name
113
.. i18n: And this will not generate any SQL query as it has been prefetched by the object relational mapping engine of Open ERP.
116
And this will not generate any SQL query as it has been prefetched by the object relational mapping engine of Open ERP.
118
.. i18n: Loops and special fields
119
.. i18n: ------------------------
122
Loops and special fields
123
------------------------
125
.. i18n: Suppose now that you want to compute the totals of 10 sales order by countries. You can do this in Open ERP within a Open ERP object:
128
Suppose now that you want to compute the totals of 10 sales order by countries. You can do this in Open ERP within a Open ERP object:
130
.. i18n: .. code-block:: python
132
.. i18n: def get_totals(self, cr, uid, ids):
133
.. i18n: countries = {}
134
.. i18n: for sale in self.browse(cr, uid, ids):
135
.. i18n: country = sale.partner_invoice_id.country
136
.. i18n: countries.setdefault(country, 0.0)
137
.. i18n: countries[country] += sale.amount_untaxed
138
.. i18n: return countries
141
.. code-block:: python
143
def get_totals(self, cr, uid, ids):
145
for sale in self.browse(cr, uid, ids):
146
country = sale.partner_invoice_id.country
147
countries.setdefault(country, 0.0)
148
countries[country] += sale.amount_untaxed
151
.. i18n: And, to print them as a good way, you can add this on your object:
154
And, to print them as a good way, you can add this on your object:
156
.. i18n: .. code-block:: python
158
.. i18n: def print_totals(self, cr, uid, ids):
159
.. i18n: result = self.get_totals(cr, uid, ids)
160
.. i18n: for country in result.keys():
161
.. i18n: print '[%s] %s: %.2f' (country.code, country.name, result[country])
164
.. code-block:: python
166
def print_totals(self, cr, uid, ids):
167
result = self.get_totals(cr, uid, ids)
168
for country in result.keys():
169
print '[%s] %s: %.2f' (country.code, country.name, result[country])
171
.. i18n: The 2 functions will generate 4 SQL queries in total ! This is due to the SQL engine of Open ERP that does prefetching, works on lists and uses caching methods. The 3 queries are:
174
The 2 functions will generate 4 SQL queries in total ! This is due to the SQL engine of Open ERP that does prefetching, works on lists and uses caching methods. The 3 queries are:
176
.. i18n: 1. Reading the sale.order to get ID's of the partner's address
177
.. i18n: 2. Reading the partner's address for the countries
178
.. i18n: 3. Calling the amount_untaxed function that will compute a total of the sale order lines
179
.. i18n: 4. Reading the countries info (code and name)
182
1. Reading the sale.order to get ID's of the partner's address
183
2. Reading the partner's address for the countries
184
3. Calling the amount_untaxed function that will compute a total of the sale order lines
185
4. Reading the countries info (code and name)
187
.. i18n: That's great because if you run this code on 1000 sales orders, you have the guarantee to only have 4 SQL queries.
190
That's great because if you run this code on 1000 sales orders, you have the guarantee to only have 4 SQL queries.
197
.. i18n: * IDS is the list of the 10 ID's: [12,15,18,34, ...,99]
198
.. i18n: * The arguments of a function are always the same:
200
.. i18n: - cr: the cursor database (from psycopg)
201
.. i18n: - uid: the user id (for security checks)
202
.. i18n: * If you run this code on 5000 sales orders, you may have 8 SQL queries because as SQL queries are not allowed to take too much memory, it may have to do two separate readings.
205
* IDS is the list of the 10 ID's: [12,15,18,34, ...,99]
206
* The arguments of a function are always the same:
208
- cr: the cursor database (from psycopg)
209
- uid: the user id (for security checks)
210
* If you run this code on 5000 sales orders, you may have 8 SQL queries because as SQL queries are not allowed to take too much memory, it may have to do two separate readings.
212
.. i18n: A complete example
213
.. i18n: ------------------
219
.. i18n: Here is a complete example, from the Open ERP official distribution, of the function that does bill of material explosion and computation of associated routings:
222
Here is a complete example, from the Open ERP official distribution, of the function that does bill of material explosion and computation of associated routings:
224
.. i18n: .. code-block:: python
226
.. i18n: class mrp_bom(osv.osv):
228
.. i18n: def _bom_find(self, cr, uid, product_id, product_uom, properties=[]):
229
.. i18n: bom_result = False
230
.. i18n: # Why searching on BoM without parent ?
231
.. i18n: cr.execute('select id from mrp_bom where product_id=%d and bom_id is null
232
.. i18n: order by sequence', (product_id,))
233
.. i18n: ids = map(lambda x: x[0], cr.fetchall())
234
.. i18n: max_prop = 0
235
.. i18n: result = False
236
.. i18n: for bom in self.pool.get('mrp.bom').browse(cr, uid, ids):
238
.. i18n: for prop_id in bom.property_ids:
239
.. i18n: if prop_id.id in properties:
241
.. i18n: if (prop>max_prop) or ((max_prop==0) and not result):
242
.. i18n: result = bom.id
243
.. i18n: max_prop = prop
244
.. i18n: return result
246
.. i18n: def _bom_explode(self, cr, uid, bom, factor, properties, addthis=False, level=10):
247
.. i18n: factor = factor / (bom.product_efficiency or 1.0)
248
.. i18n: factor = rounding(factor, bom.product_rounding)
249
.. i18n: if factor<bom.product_rounding:
250
.. i18n: factor = bom.product_rounding
252
.. i18n: result2 = []
253
.. i18n: phantom = False
254
.. i18n: if bom.type=='phantom' and not bom.bom_lines:
255
.. i18n: newbom = self._bom_find(cr, uid, bom.product_id.id,
256
.. i18n: bom.product_uom.id, properties)
258
.. i18n: res = self._bom_explode(cr, uid, self.browse(cr, uid, [newbom])[0],
259
.. i18n: factor*bom.product_qty, properties, addthis=True, level=level+10)
260
.. i18n: result = result + res[0]
261
.. i18n: result2 = result2 + res[1]
262
.. i18n: phantom = True
264
.. i18n: phantom = False
265
.. i18n: if not phantom:
266
.. i18n: if addthis and not bom.bom_lines:
267
.. i18n: result.append(
269
.. i18n: 'name': bom.product_id.name,
270
.. i18n: 'product_id': bom.product_id.id,
271
.. i18n: 'product_qty': bom.product_qty * factor,
272
.. i18n: 'product_uom': bom.product_uom.id,
273
.. i18n: 'product_uos_qty': bom.product_uos and
274
.. i18n: bom.product_uos_qty * factor or False,
275
.. i18n: 'product_uos': bom.product_uos and bom.product_uos.id or False,
277
.. i18n: if bom.routing_id:
278
.. i18n: for wc_use in bom.routing_id.workcenter_lines:
279
.. i18n: wc = wc_use.workcenter_id
280
.. i18n: d, m = divmod(factor, wc_use.workcenter_id.capacity_per_cycle)
281
.. i18n: mult = (d + (m and 1.0 or 0.0))
282
.. i18n: cycle = mult * wc_use.cycle_nbr
283
.. i18n: result2.append({
284
.. i18n: 'name': bom.routing_id.name,
285
.. i18n: 'workcenter_id': wc.id,
286
.. i18n: 'sequence': level+(wc_use.sequence or 0),
287
.. i18n: 'cycle': cycle,
288
.. i18n: 'hour': float(wc_use.hour_nbr*mult +
289
.. i18n: (wc.time_start+wc.time_stop+cycle*wc.time_cycle) *
290
.. i18n: (wc.time_efficiency or 1.0)),
292
.. i18n: for bom2 in bom.bom_lines:
293
.. i18n: res = self._bom_explode(cr, uid, bom2, factor, properties,
294
.. i18n: addthis=True, level=level+10)
295
.. i18n: result = result + res[0]
296
.. i18n: result2 = result2 + res[1]
297
.. i18n: return result, result2
300
.. code-block:: python
302
class mrp_bom(osv.osv):
304
def _bom_find(self, cr, uid, product_id, product_uom, properties=[]):
306
# Why searching on BoM without parent ?
307
cr.execute('select id from mrp_bom where product_id=%d and bom_id is null
308
order by sequence', (product_id,))
309
ids = map(lambda x: x[0], cr.fetchall())
312
for bom in self.pool.get('mrp.bom').browse(cr, uid, ids):
314
for prop_id in bom.property_ids:
315
if prop_id.id in properties:
317
if (prop>max_prop) or ((max_prop==0) and not result):
322
def _bom_explode(self, cr, uid, bom, factor, properties, addthis=False, level=10):
323
factor = factor / (bom.product_efficiency or 1.0)
324
factor = rounding(factor, bom.product_rounding)
325
if factor<bom.product_rounding:
326
factor = bom.product_rounding
330
if bom.type=='phantom' and not bom.bom_lines:
331
newbom = self._bom_find(cr, uid, bom.product_id.id,
332
bom.product_uom.id, properties)
334
res = self._bom_explode(cr, uid, self.browse(cr, uid, [newbom])[0],
335
factor*bom.product_qty, properties, addthis=True, level=level+10)
336
result = result + res[0]
337
result2 = result2 + res[1]
342
if addthis and not bom.bom_lines:
345
'name': bom.product_id.name,
346
'product_id': bom.product_id.id,
347
'product_qty': bom.product_qty * factor,
348
'product_uom': bom.product_uom.id,
349
'product_uos_qty': bom.product_uos and
350
bom.product_uos_qty * factor or False,
351
'product_uos': bom.product_uos and bom.product_uos.id or False,
354
for wc_use in bom.routing_id.workcenter_lines:
355
wc = wc_use.workcenter_id
356
d, m = divmod(factor, wc_use.workcenter_id.capacity_per_cycle)
357
mult = (d + (m and 1.0 or 0.0))
358
cycle = mult * wc_use.cycle_nbr
360
'name': bom.routing_id.name,
361
'workcenter_id': wc.id,
362
'sequence': level+(wc_use.sequence or 0),
364
'hour': float(wc_use.hour_nbr*mult +
365
(wc.time_start+wc.time_stop+cycle*wc.time_cycle) *
366
(wc.time_efficiency or 1.0)),
368
for bom2 in bom.bom_lines:
369
res = self._bom_explode(cr, uid, bom2, factor, properties,
370
addthis=True, level=level+10)
371
result = result + res[0]
372
result2 = result2 + res[1]
373
return result, result2