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

177

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

178

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

179

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

180

181

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

182

* The arguments of a function are always the same:

183

184

- cr: the cursor database (from psycopg)

185

- uid: the user id (for security checks)

186

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

187

188

.. i18n: A complete example

189

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

190

191

A complete example

192

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

193

194

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

195

196

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:

197

198

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

199

.. i18n:

200

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

201

.. i18n: ...

202

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

203

.. i18n: bom_result = False

204

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

205

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

206

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

207

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

208

.. i18n: max_prop = 0

209

.. i18n: result = False

210

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

211

.. i18n: prop = 0

212

.. i18n: for prop_id in bom.property_ids:

213

.. i18n: if prop_id.id in properties:

214

.. i18n: prop+=1

215

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

216

.. i18n: result = bom.id

217

.. i18n: max_prop = prop

218

.. i18n: return result

219

.. i18n:

220

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

221

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

222

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

223

.. i18n: if factor<bom.product_rounding:

224

.. i18n: factor = bom.product_rounding

225

.. i18n: result = []

226

.. i18n: result2 = []

227

.. i18n: phantom = False

228

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

229

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

230

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

231

.. i18n: if newbom:

232

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

233

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

234

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

235

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

236

.. i18n: phantom = True

237

.. i18n: else:

238

.. i18n: phantom = False

239

.. i18n: if not phantom:

240

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

241

.. i18n: result.append(

242

.. i18n: {

243

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

244

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

245

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

246

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

247

.. i18n: 'product_uos_qty': bom.product_uos and

248

.. i18n: bom.product_uos_qty * factor or False,

249

.. i18n: 'product_uos': bom.product_uos and bom.product_uos.id or False,

250

.. i18n: })

251

.. i18n: if bom.routing_id:

252

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

253

.. i18n: wc = wc_use.workcenter_id

254

.. i18n: d, m = divmod(factor, wc_use.workcenter_id.capacity_per_cycle)

255

.. i18n: mult = (d + (m and 1.0 or 0.0))

256

.. i18n: cycle = mult * wc_use.cycle_nbr

257

.. i18n: result2.append({

258

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

259

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

260

.. i18n: 'sequence': level+(wc_use.sequence or 0),

261

.. i18n: 'cycle': cycle,

262

.. i18n: 'hour': float(wc_use.hour_nbr*mult +

263

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

264

.. i18n: (wc.time_efficiency or 1.0)),

265

.. i18n: })

266

.. i18n: for bom2 in bom.bom_lines:

267

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

268

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

269

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

270

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

271

.. i18n: return result, result2

272

273

.. code-block:: python

274

275

class mrp_bom(osv.osv):

276

...

277

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

278

bom_result = False

279

# Why searching on BoM without parent ?

280

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

281

order by sequence', (product_id,))

282

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

283

max_prop = 0

284

result = False

285

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

286

prop = 0

287

for prop_id in bom.property_ids:

288

if prop_id.id in properties:

289

prop+=1

290

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

291

result = bom.id

292

max_prop = prop

293

return result

294

295

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

296

factor = factor / (bom.product_efficiency or 1.0)

297

factor = rounding(factor, bom.product_rounding)

298

if factor<bom.product_rounding:

299

factor = bom.product_rounding

300

result = []

301

result2 = []

302

phantom = False

303

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

304

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

305

bom.product_uom.id, properties)

306

if newbom:

307

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

308

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

309

result = result + res[0]

310

result2 = result2 + res[1]

311

phantom = True

312

else:

313

phantom = False

314

if not phantom:

315

if addthis and not bom.bom_lines:

316

result.append(

317

{

318

'name': bom.product_id.name,

319

'product_id': bom.product_id.id,

320

'product_qty': bom.product_qty * factor,

321

'product_uom': bom.product_uom.id,

322

'product_uos_qty': bom.product_uos and

323

bom.product_uos_qty * factor or False,

324

'product_uos': bom.product_uos and bom.product_uos.id or False,

325

})

326

if bom.routing_id:

327

for wc_use in bom.routing_id.workcenter_lines:

328

wc = wc_use.workcenter_id

329

d, m = divmod(factor, wc_use.workcenter_id.capacity_per_cycle)

330

mult = (d + (m and 1.0 or 0.0))

331

cycle = mult * wc_use.cycle_nbr

332

result2.append({

333

'name': bom.routing_id.name,

334

'workcenter_id': wc.id,

335

'sequence': level+(wc_use.sequence or 0),

336

'cycle': cycle,

337

'hour': float(wc_use.hour_nbr*mult +

338

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

339

(wc.time_efficiency or 1.0)),

340

})

341

for bom2 in bom.bom_lines:

342

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

343

addthis=True, level=level+10)

344

result = result + res[0]

345

result2 = result2 + res[1]

346

return result, result2

Older »