~xrg/openobject-doc/trunk-xrg

.. 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.::

.. i18n:

.. i18n: sale = self.browse(cr, uid, ID)

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.::

sale = self.browse(cr, uid, ID)

.. 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)

(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)

.. 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::

.. i18n:

.. i18n: country_name = sale.partner_id.address[0].country_id.name

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::

country_name = sale.partner_id.address[0].country_id.name

.. 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)

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)

.. i18n: .. code-block:: python

.. i18n:

.. i18n: cr.execute('select partner_id from sale_order where id=%d', (ID,))

.. i18n: partner_id = cr.fetchone()[0]

.. i18n: cr.execute('select country_id from res_partner_address where partner_id=%d', (partner_id,))

.. i18n: country_id = cr.fetchone()[0]

.. i18n: cr.execute('select name from res_country where id=%d', (country_id,))

.. i18n: del partner_id

.. i18n: del country_id

.. i18n: country_name = cr.fetchone()[0]

.. code-block:: python

cr.execute('select partner_id from sale_order where id=%d', (ID,))

partner_id = cr.fetchone()[0]

cr.execute('select country_id from res_partner_address where partner_id=%d', (partner_id,))

country_id = cr.fetchone()[0]

cr.execute('select name from res_country where id=%d', (country_id,))

del partner_id

del country_id

country_name = cr.fetchone()[0]

.. 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:

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:

.. i18n: * Maybe some parts are in others functions

.. i18n: * There may be a loop in different elements

.. i18n: * You have to use intermediate variables like country_id

* Maybe some parts are in others functions

* There may be a loop in different elements

* You have to use intermediate variables like country_id

.. i18n: The first operation as an object call is much better for several reasons:

The first operation as an object call is much better for several reasons:

.. i18n: * It uses objects facilities and works with modules inheritances, overload, ...

.. i18n: * It's simpler, more explicit and uses less code

.. i18n: * It's much more efficient as you will see in the following examples

.. i18n: * Some fields do not directly correspond to a SQL field (e.g.: function fields in Python)

* It uses objects facilities and works with modules inheritances, overload, ...

* It's simpler, more explicit and uses less code

* It's much more efficient as you will see in the following examples

* Some fields do not directly correspond to a SQL field (e.g.: function fields in Python)

.. i18n: Example 2 - Prefetching

.. i18n: -----------------------

Example 2 - Prefetching

-----------------------

.. 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::

.. i18n:

.. i18n: partner_name = sale.partner_id.name

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::

partner_name = sale.partner_id.name

100

.. i18n: And this will not generate any SQL query as it has been prefetched by the object relational mapping engine of Open ERP.

101

102

And this will not generate any SQL query as it has been prefetched by the object relational mapping engine of Open ERP.

103

104

.. i18n: Loops and special fields

105

.. i18n: ------------------------

106

107

Loops and special fields

108

------------------------

109

110

.. 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:

111

112

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:

113

114

.. i18n: .. code-block:: python

115

.. i18n:

116

.. i18n: def get_totals(self, cr, uid, ids):

117

.. i18n: countries = {}

118

.. i18n: for sale in self.browse(cr, uid, ids):

119

.. i18n: country = sale.partner_invoice_id.country

120

.. i18n: countries.setdefault(country, 0.0)

121

.. i18n: countries[country] += sale.amount_untaxed

122

.. i18n: return countries

123

124

.. code-block:: python

125

126

def get_totals(self, cr, uid, ids):

127

countries = {}

128

for sale in self.browse(cr, uid, ids):

129

country = sale.partner_invoice_id.country

130

countries.setdefault(country, 0.0)

131

countries[country] += sale.amount_untaxed

132

return countries

133

134

.. i18n: And, to print them as a good way, you can add this on your object:

135

136

And, to print them as a good way, you can add this on your object:

137

138

.. i18n: .. code-block:: python

139

.. i18n:

140

.. i18n: def print_totals(self, cr, uid, ids):

141

.. i18n: result = self.get_totals(cr, uid, ids)

142

.. i18n: for country in result.keys():

143

.. i18n: print '[%s] %s: %.2f' (country.code, country.name, result[country])

144

145

.. code-block:: python

146

147

def print_totals(self, cr, uid, ids):

148

result = self.get_totals(cr, uid, ids)

149

for country in result.keys():

150

print '[%s] %s: %.2f' (country.code, country.name, result[country])

151

152

.. 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:

153

154

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:

155

156

.. i18n: 1. Reading the sale.order to get ID's of the partner's address

157

.. i18n: 2. Reading the partner's address for the countries

158

.. i18n: 3. Calling the amount_untaxed function that will compute a total of the sale order lines

159

.. i18n: 4. Reading the countries info (code and name)

160

161

1. Reading the sale.order to get ID's of the partner's address

162

2. Reading the partner's address for the countries

163

3. Calling the amount_untaxed function that will compute a total of the sale order lines

164

4. Reading the countries info (code and name)

165

166

.. i18n: That's great because if you run this code on 1000 sales orders, you have the guarantee to only have 4 SQL queries.

167

168

That's great because if you run this code on 1000 sales orders, you have the guarantee to only have 4 SQL queries.

169

170

.. i18n: Notes:

171

172

Notes:

173

174

.. i18n: * IDS is the list of the 10 ID's: [12,15,18,34, ...,99]

175

.. i18n: * The arguments of a function are always the same:

176

.. i18n: - cr: the cursor database (from psycopg)

177

.. i18n: - uid: the user id (for security checks)

178

.. 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.

179

180

* IDS is the list of the 10 ID's: [12,15,18,34, ...,99]

181

* The arguments of a function are always the same:

182

- cr: the cursor database (from psycopg)

183

- uid: the user id (for security checks)

184

* 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.

185

186

.. i18n: A complete example

187

.. i18n: ------------------

188

189

A complete example

190

------------------

191

192

.. 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:

193

194

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:

195

196

.. i18n: .. code-block:: python

197

.. i18n:

198

.. i18n: class mrp_bom(osv.osv):

199

.. i18n: ...

200

.. i18n: def _bom_find(self, cr, uid, product_id, product_uom, properties=[]):

201

.. i18n: bom_result = False

202

.. i18n: # Why searching on `BoM without parent ?

203

.. i18n: cr.execute('select id from mrp_bom where product_id=%d and bom_id is null

204

.. i18n: order by sequence', (product_id,))

205

.. i18n: ids = map(lambda x: x[0], cr.fetchall())

206

.. i18n: max_prop = 0

207

.. i18n: result = False

208

.. i18n: for bom in self.pool.get('mrp.bom').browse(cr, uid, ids):

209

.. i18n: prop = 0

210

.. i18n: for prop_id in bom.property_ids:

211

.. i18n: if prop_id.id in properties:

212

.. i18n: prop+=1

213

.. i18n: if (prop>max_prop) or ((max_prop==0) and not result):

214

.. i18n: result = bom.id

215

.. i18n: return result

216

.. i18n:

217

.. i18n: def _bom_explode(self, cr, uid, bom, factor, properties, addthis=False, level=10):

218

.. i18n: factor = factor / (bom.product_efficiency or 1.0)

219

.. i18n: factor = rounding(factor, bom.product_rounding)

220

.. i18n: if factor<bom.product_rounding:

221

.. i18n: factor = bom.product_rounding

222

.. i18n: result = []

223

.. i18n: result2 = []

224

.. i18n: if bom.type=='phantom' and not bom.bom_lines:

225

.. i18n: newbom = self._bom_find(cr, uid, bom.product_id.id,

226

.. i18n: bom.product_uom.id, properties)

227

.. i18n: if newbom:

228

.. i18n: res = self._bom_explode(cr, uid, self.browse(cr, uid, [newbom])[0],

229

.. i18n: factor*bom.product_qty, properties, addthis=True, level=level+10)

230

.. i18n: result = result + res[0]

231

.. i18n: result2 = result2 + res[1]

232

.. i18n: else:

233

.. i18n: return [],[]

234

.. i18n: else:

235

.. i18n: if addthis and not bom.bom_lines:

236

.. i18n: result.append(

237

.. i18n: {

238

.. i18n: 'name': bom.product_id.name,

239

.. i18n: 'product_id': bom.product_id.id,

240

.. i18n: 'product_qty': bom.product_qty * factor,

241

.. i18n: 'product_uom': bom.product_uom.id,

242

.. i18n: })

243

.. i18n: if bom.routing_id:

244

.. i18n: for wc_use in bom.routing_id.workcenter_lines:

245

.. i18n: wc = wc_use.workcenter_id

246

.. i18n: cycle = factor * wc_use.cycle_nbr

247

.. i18n: result2.append({

248

.. i18n: 'name': bom.routing_id.name,

249

.. i18n: 'workcenter_id': wc.id,

250

.. i18n: 'sequence': level,

251

.. i18n: 'cycle': cycle,

252

.. i18n: 'hour': wc_use.hour_nbr + (

253

.. i18n: wc.time_start+wc.time_stop+cycle*wc.time_cycle) *

254

.. i18n: (wc.time_efficiency or 1

255

.. i18n: })

256

.. i18n: for bom2 in bom.bom_lines:

257

.. i18n: res = self._bom_explode(cr, uid, bom2, factor, properties,

258

.. i18n: addthis=True, level=level+10)

259

.. i18n: result = result + res[0]

260

.. i18n: result2 = result2 + res[1]

261

.. i18n: return result, result2

262

263

.. code-block:: python

264

265

class mrp_bom(osv.osv):

266

...

267

def _bom_find(self, cr, uid, product_id, product_uom, properties=[]):

268

bom_result = False

269

# Why searching on `BoM without parent ?

270

cr.execute('select id from mrp_bom where product_id=%d and bom_id is null

271

order by sequence', (product_id,))

272

ids = map(lambda x: x[0], cr.fetchall())

273

max_prop = 0

274

result = False

275

for bom in self.pool.get('mrp.bom').browse(cr, uid, ids):

276

prop = 0

277

for prop_id in bom.property_ids:

278

if prop_id.id in properties:

279

prop+=1

280

if (prop>max_prop) or ((max_prop==0) and not result):

281

result = bom.id

282

return result

283

284

def _bom_explode(self, cr, uid, bom, factor, properties, addthis=False, level=10):

285

factor = factor / (bom.product_efficiency or 1.0)

286

factor = rounding(factor, bom.product_rounding)

287

if factor<bom.product_rounding:

288

factor = bom.product_rounding

289

result = []

290

result2 = []

291

if bom.type=='phantom' and not bom.bom_lines:

292

newbom = self._bom_find(cr, uid, bom.product_id.id,

293

bom.product_uom.id, properties)

294

if newbom:

295

res = self._bom_explode(cr, uid, self.browse(cr, uid, [newbom])[0],

296

factor*bom.product_qty, properties, addthis=True, level=level+10)

297

result = result + res[0]

298

result2 = result2 + res[1]

299

else:

300

return [],[]

301

else:

302

if addthis and not bom.bom_lines:

303

result.append(

304

{

305

'name': bom.product_id.name,

306

'product_id': bom.product_id.id,

307

'product_qty': bom.product_qty * factor,

308

'product_uom': bom.product_uom.id,

309

})

310

if bom.routing_id:

311

for wc_use in bom.routing_id.workcenter_lines:

312

wc = wc_use.workcenter_id

313

cycle = factor * wc_use.cycle_nbr

314

result2.append({

315

'name': bom.routing_id.name,

316

'workcenter_id': wc.id,

317

'sequence': level,

318

'cycle': cycle,

319

'hour': wc_use.hour_nbr + (

320

wc.time_start+wc.time_stop+cycle*wc.time_cycle) *

321

(wc.time_efficiency or 1

322

})

323

for bom2 in bom.bom_lines:

324

res = self._bom_explode(cr, uid, bom2, factor, properties,

325

addthis=True, level=level+10)

326

result = result + res[0]

327

result2 = result2 + res[1]

328

return result, result2

Older »