1 # -*- encoding: utf-8 -*-
2 # -*- coding: utf-8 -*-
3 ##############################################################################
5 # Copyright (c) 2004-2008 TINY SPRL. (http://tiny.be) All Rights Reserved.
9 # WARNING: This program as such is intended to be used by professional
10 # programmers who take the whole responsability of assessing all potential
11 # consequences resulting from its eventual inadequacies and bugs
12 # End users who are looking for a ready-to-use solution with commercial
13 # garantees and support are strongly adviced to contract a Free Software
16 # This program is Free Software; you can redistribute it and/or
17 # modify it under the terms of the GNU General Public License
18 # as published by the Free Software Foundation; either version 2
19 # of the License, or (at your option) any later version.
21 # This program is distributed in the hope that it will be useful,
22 # but WITHOUT ANY WARRANTY; without even the implied warranty of
23 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
30 ##############################################################################
32 from osv import osv, fields
33 from tools.translate import _
36 class account_analytic_account(osv.osv):
37 _name = "account.analytic.account"
38 _inherit = "account.analytic.account"
40 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
42 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
44 acc_set = ",".join(map(str, ids2))
45 cr.execute("select account_analytic_line.account_id, sum(amount) \
46 from account_analytic_line \
47 join account_analytic_journal \
48 on account_analytic_line.journal_id = account_analytic_journal.id \
49 where account_analytic_line.account_id IN (%s) \
50 and account_analytic_journal.type = 'sale' \
51 group by account_analytic_line.account_id" % acc_set)
52 for account_id, sum in cr.fetchall():
53 res[account_id] = round(sum,2)
55 res.setdefault(obj_id, 0.0)
56 for child_id in self.search(cr, uid,
57 [('parent_id', 'child_of', [obj_id])]):
58 if child_id != obj_id:
59 res[obj_id] += res.get(child_id, 0.0)
61 res[id] = round(res.get(id, 0.0),2)
64 def _ca_to_invoice_calc(self, cr, uid, ids, name, arg, context={}):
67 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
69 # Amount uninvoiced hours to invoice at sale price
70 acc_set = ",".join(map(str, ids2))
71 cr.execute("""SELECT account_analytic_account.id, \
72 sum (product_template.list_price * \
73 account_analytic_line.unit_amount * \
74 ((100-hr_timesheet_invoice_factor.factor)/100)) \
76 FROM product_template \
77 join product_product \
78 on product_template.id = product_product.product_tmpl_id \
79 JOIN account_analytic_line \
80 on account_analytic_line.product_id = product_product.id \
81 JOIN account_analytic_journal \
82 on account_analytic_line.journal_id = account_analytic_journal.id \
83 JOIN account_analytic_account \
84 on account_analytic_account.id = account_analytic_line.account_id \
85 JOIN hr_timesheet_invoice_factor \
86 on hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
87 WHERE account_analytic_account.id IN (%s) \
88 AND account_analytic_line.invoice_id is null \
89 AND account_analytic_line.to_invoice IS NOT NULL \
90 GROUP BY account_analytic_account.id;"""%acc_set)
91 for account_id, sum in cr.fetchall():
92 res[account_id] = round(sum,2)
94 # Expense amount and purchase invoice
95 acc_set = ",".join(map(str, ids2))
96 cr.execute ("select account_analytic_line.account_id, sum(amount) \
97 from account_analytic_line \
98 join account_analytic_journal \
99 on account_analytic_line.journal_id = account_analytic_journal.id \
100 where account_analytic_line.account_id IN (%s) \
101 and account_analytic_journal.type = 'purchase' \
102 GROUP BY account_analytic_line.account_id;"%acc_set)
103 for account_id, sum in cr.fetchall():
104 res2[account_id] = round(sum,2)
106 res.setdefault(obj_id, 0.0)
107 res2.setdefault(obj_id, 0.0)
108 for child_id in self.search(cr, uid,
109 [('parent_id', 'child_of', [obj_id])]):
110 if child_id != obj_id:
111 res[obj_id] += res.get(child_id, 0.0)
112 res2[obj_id] += res2.get(child_id, 0.0)
113 # sum both result on account_id
115 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
118 def _hours_qtt_non_invoiced_calc (self, cr, uid, ids, name, arg, context={}):
120 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
122 acc_set = ",".join(map(str, ids2))
123 cr.execute("select account_analytic_line.account_id, sum(unit_amount) \
124 from account_analytic_line \
125 join account_analytic_journal \
126 on account_analytic_line.journal_id = account_analytic_journal.id \
127 where account_analytic_line.account_id IN (%s) \
128 and account_analytic_journal.type='general' \
129 and invoice_id is null \
130 AND to_invoice IS NOT NULL \
131 GROUP BY account_analytic_line.account_id;"%acc_set)
132 for account_id, sum in cr.fetchall():
133 res[account_id] = round(sum,2)
135 res.setdefault(obj_id, 0.0)
136 for child_id in self.search(cr, uid,
137 [('parent_id', 'child_of', [obj_id])]):
138 if child_id != obj_id:
139 res[obj_id] += res.get(child_id, 0.0)
141 res[id] = round(res.get(id, 0.0),2)
144 def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
146 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
148 acc_set = ",".join(map(str, ids2))
149 cr.execute("select account_analytic_line.account_id,sum(unit_amount) \
150 from account_analytic_line \
151 join account_analytic_journal \
152 on account_analytic_line.journal_id = account_analytic_journal.id \
153 where account_analytic_line.account_id IN (%s) \
154 and account_analytic_journal.type='general' \
155 GROUP BY account_analytic_line.account_id"%acc_set)
156 for account_id, sum in cr.fetchall():
157 res[account_id] = round(sum,2)
159 res.setdefault(obj_id, 0.0)
160 for child_id in self.search(cr, uid,
161 [('parent_id', 'child_of', [obj_id])]):
162 if child_id != obj_id:
163 res[obj_id] += res.get(child_id, 0.0)
165 res[id] = round(res.get(id, 0.0),2)
168 def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
170 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
172 acc_set = ",".join(map(str, ids2))
173 cr.execute("""select account_analytic_line.account_id,sum(amount) \
174 from account_analytic_line \
175 join account_analytic_journal \
176 on account_analytic_line.journal_id = account_analytic_journal.id \
177 where account_analytic_line.account_id IN (%s) \
179 GROUP BY account_analytic_line.account_id"""%acc_set)
180 for account_id, sum in cr.fetchall():
181 res[account_id] = round(sum,2)
183 res.setdefault(obj_id, 0.0)
184 for child_id in self.search(cr, uid,
185 [('parent_id', 'child_of', [obj_id])]):
186 if child_id != obj_id:
187 res[obj_id] += res.get(child_id, 0.0)
189 res[id] = round(res.get(id, 0.0),2)
192 def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
195 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
197 acc_set = ",".join(map(str, ids2))
198 # First part with expense and purchase
199 cr.execute("""select account_analytic_line.account_id,sum(amount) \
200 from account_analytic_line \
201 join account_analytic_journal \
202 on account_analytic_line.journal_id = account_analytic_journal.id \
203 where account_analytic_line.account_id IN (%s) \
204 and account_analytic_journal.type = 'purchase' \
205 GROUP BY account_analytic_line.account_id"""%acc_set)
206 for account_id, sum in cr.fetchall():
207 res[account_id] = round(sum,2)
209 # Second part with timesheet (with invoice factor)
210 acc_set = ",".join(map(str, ids2))
211 cr.execute("""select account_analytic_line.account_id as account_id, \
212 sum((account_analytic_line.unit_amount * pt.list_price) \
213 - (account_analytic_line.unit_amount * pt.list_price \
214 * hr.factor)) as somme
215 from account_analytic_line \
216 join account_analytic_journal \
217 on account_analytic_line.journal_id = account_analytic_journal.id \
218 join product_product pp \
219 on (account_analytic_line.product_id = pp.id) \
220 join product_template pt \
221 on (pp.product_tmpl_id = pt.id) \
222 join account_analytic_account a \
223 on (a.id=account_analytic_line.account_id) \
224 join hr_timesheet_invoice_factor hr \
225 on (hr.id=a.to_invoice) \
226 where account_analytic_line.account_id IN (%s) \
227 and a.to_invoice IS NOT NULL \
228 GROUP BY account_analytic_line.account_id"""%acc_set)
229 for account_id, sum in cr.fetchall():
230 res2[account_id] = round(sum,2)
233 res.setdefault(obj_id, 0.0)
234 res2.setdefault(obj_id, 0.0)
235 for child_id in self.search(cr, uid,
236 [('parent_id', 'child_of', [obj_id])]):
237 if child_id != obj_id:
238 res[obj_id] += res.get(child_id, 0.0)
239 res[obj_id] += res2.get(child_id, 0.0)
241 # sum both result on account_id
243 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
246 def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
248 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
250 acc_set = ",".join(map(str, ids2))
251 cr.execute("select account_analytic_line.account_id, max(date) \
252 from account_analytic_line \
253 where account_id IN (%s) \
254 and invoice_id is null \
255 GROUP BY account_analytic_line.account_id" % acc_set)
256 for account_id, sum in cr.fetchall():
257 res[account_id] = sum
259 res.setdefault(obj_id, '')
260 for child_id in self.search(cr, uid,
261 [('parent_id', 'child_of', [obj_id])]):
262 if res[obj_id] < res.get(child_id, ''):
263 res[obj_id] = res.get(child_id, '')
265 res[id] = res.get(id, '')
268 def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
270 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
272 acc_set = ",".join(map(str, ids2))
273 cr.execute ("select account_analytic_line.account_id, \
274 date(max(account_invoice.date_invoice)) \
275 from account_analytic_line \
276 join account_invoice \
277 on account_analytic_line.invoice_id = account_invoice.id \
278 where account_analytic_line.account_id IN (%s) \
279 and account_analytic_line.invoice_id is not null \
280 GROUP BY account_analytic_line.account_id"%acc_set)
281 for account_id, sum in cr.fetchall():
282 res[account_id] = sum
284 res.setdefault(obj_id, '')
285 for child_id in self.search(cr, uid,
286 [('parent_id', 'child_of', [obj_id])]):
287 if res[obj_id] < res.get(child_id, ''):
288 res[obj_id] = res.get(child_id, '')
290 res[id] = res.get(id, '')
293 def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
295 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
297 acc_set = ",".join(map(str, ids2))
298 cr.execute("select account_analytic_line.account_id, max(date) \
299 from account_analytic_line \
300 where account_id IN (%s) \
301 and invoice_id is not null \
302 GROUP BY account_analytic_line.account_id;"%acc_set)
303 for account_id, sum in cr.fetchall():
304 res[account_id] = sum
306 res.setdefault(obj_id, '')
307 for child_id in self.search(cr, uid,
308 [('parent_id', 'child_of', [obj_id])]):
309 if res[obj_id] < res.get(child_id, ''):
310 res[obj_id] = res.get(child_id, '')
312 res[id] = res.get(id, '')
315 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
317 for account in self.browse(cr, uid, ids):
318 if account.quantity_max <> 0:
319 res[account.id] = account.quantity_max - account.hours_quantity
323 res[id] = round(res.get(id, 0.0),2)
326 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
328 for account in self.browse(cr, uid, ids):
329 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
330 if res[account.id] < 0:
333 res[id] = round(res.get(id, 0.0),2)
336 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
338 for account in self.browse(cr, uid, ids):
339 if account.hours_qtt_invoiced == 0:
342 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
344 res[id] = round(res.get(id, 0.0),2)
347 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
349 for account in self.browse(cr, uid, ids):
350 if account.ca_invoiced == 0:
352 elif account.real_margin <> 0.0:
353 res[account.id] = (account.ca_invoiced / account.real_margin) * 100
355 res[account.id] = 0.0
357 res[id] = round(res.get(id, 0.0),2)
360 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
362 for account in self.browse(cr, uid, ids):
363 if account.amount_max <> 0:
364 res[account.id] = account.amount_max - account.ca_invoiced
368 res[id] = round(res.get(id, 0.0),2)
371 def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
373 for account in self.browse(cr, uid, ids):
374 res[account.id] = account.ca_invoiced + account.total_cost
376 res[id] = round(res.get(id, 0.0),2)
379 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context={}):
381 for account in self.browse(cr, uid, ids):
382 res[account.id] = account.ca_theorical + account.total_cost
384 res[id] = round(res.get(id, 0.0),2)
387 def _month(self, cr, uid, ids, name, arg, context=None):
390 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
391 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
392 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
393 'AND unit_amount <> 0.0')
394 res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
397 def _user(self, cr, uid, ids, name, arg, context=None):
399 cr.execute('SELECT MAX(id) FROM res_users')
400 max_user = cr.fetchone()[0]
402 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
403 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
404 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
405 'AND unit_amount <> 0.0')
406 res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
410 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced amount'),
411 'total_cost': fields.function(_total_cost_calc, method=True, type='float', string='Total cost'),
412 'ca_to_invoice': fields.function(_ca_to_invoice_calc, method=True, type='float', string='Uninvoiced amount'),
413 'ca_theorical': fields.function(_ca_theorical_calc, method=True, type='float', string='Theorical revenue'),
414 'hours_quantity': fields.function(_hours_quantity_calc, method=True, type='float', string='Hours tot'),
415 'last_invoice_date': fields.function(_last_invoice_date_calc, method=True, type='date', string='Last invoice date'),
416 'last_worked_invoiced_date': fields.function(_last_worked_invoiced_date_calc, method=True, type='date', string='Last invoiced worked date'),
417 'last_worked_date': fields.function(_last_worked_date_calc, method=True, type='date', string='Last worked date'),
418 'hours_qtt_non_invoiced': fields.function(_hours_qtt_non_invoiced_calc, method=True, type='float', string='Uninvoiced hours'),
419 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, method=True, type='float', string='Invoiced hours'),
420 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining hours'),
421 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining revenue'),
422 'revenue_per_hour': fields.function(_revenue_per_hour_calc, method=True, type='float', string='Revenue per hours (real)'),
423 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real margin'),
424 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical margin'),
425 'real_margin_rate': fields.function(_real_margin_rate_calc, method=True, type='float', string='Real margin rate (%)'),
426 'month_ids': fields.function(_month, method=True, type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
427 'user_ids': fields.function(_user, method=True, type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
429 account_analytic_account()
431 class account_analytic_account_summary_user(osv.osv):
432 _name = "account_analytic_analysis.summary.user"
433 _description = "Hours summary by user"
438 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
440 account_obj = self.pool.get('account.analytic.account')
441 cr.execute('SELECT MAX(id) FROM res_users')
442 max_user = cr.fetchone()[0]
443 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
444 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
445 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
446 user_set = ','.join([str(x) for x in user_ids])
448 acc_set = ','.join([str(x) for x in account_ids2])
449 cr.execute('SELECT id, unit_amount ' \
450 'FROM account_analytic_analysis_summary_user ' \
451 'WHERE account_id in (%s) ' \
452 'AND "user" in (%s) ' % \
454 for sum_id, unit_amount in cr.fetchall():
455 res[sum_id] = unit_amount
457 res.setdefault(obj_id, 0.0)
458 for child_id in account_obj.search(cr, uid,
459 [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
460 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
461 res[obj_id] += res.get((child_id * max_user) + obj_id -((obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)) * max_user), 0.0)
463 res[id] = round(res.get(id, 0.0), 2)
467 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
468 'unit_amount': fields.function(_unit_amount, method=True, type='float',
469 string='Total Time'),
470 'user' : fields.many2one('res.users', 'User'),
473 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
475 '(u.account_id * u.max_user) + u."user" AS id, ' \
476 'u.account_id AS account_id, ' \
477 'u."user" AS "user", ' \
478 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
481 'a.id AS account_id, ' \
482 'u1.id AS "user", ' \
483 'MAX(u2.id) AS max_user ' \
485 'res_users AS u1, ' \
486 'res_users AS u2, ' \
487 'account_analytic_account AS a ' \
488 'GROUP BY u1.id, a.id ' \
492 'l.account_id AS account_id, ' \
493 'l.user_id AS "user", ' \
494 'SUM(l.unit_amount) AS unit_amount ' \
495 'FROM account_analytic_line AS l, ' \
496 'account_analytic_journal AS j ' \
497 'WHERE j.type = \'general\' ' \
498 'GROUP BY l.account_id, l.user_id ' \
501 'u.account_id = l.account_id ' \
502 'AND u."user" = l."user"' \
504 'GROUP BY u."user", u.account_id, u.max_user' \
507 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
514 fields = self._columns.keys()
516 # construct a clause for the rules :
517 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
519 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
520 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
523 cr.execute('SELECT MAX(id) FROM res_users')
524 max_user = cr.fetchone()[0]
526 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
527 for i in range(0, len(ids), cr.IN_MAX):
528 sub_ids = ids[i:i+cr.IN_MAX]
530 cr.execute('select %s from \"%s\" where id in (%s) ' \
531 'and account_id in (%s) ' \
532 'and "user" in (%s) and %s order by %s' % \
533 (','.join(fields_pre2 + ['id']), self._table,
534 ','.join([str(x) for x in sub_ids]),
535 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
536 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
538 if not cr.rowcount == len({}.fromkeys(sub_ids)):
539 raise except_orm(_('AccessError'),
540 _('You try to bypass an access rule (Document type: %s).') % self._description)
542 cr.execute('select %s from \"%s\" where id in (%s) ' \
543 'and account_id in (%s) ' \
544 'and "user" in (%s) order by %s' % \
545 (','.join(fields_pre2 + ['id']), self._table,
546 ','.join([str(x) for x in sub_ids]),
547 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
548 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
550 res.extend(cr.dictfetchall())
552 res = map(lambda x: {'id': x}, ids)
555 if self._columns[f].translate:
556 ids = map(lambda x: x['id'], res)
557 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
559 r[f] = res_trans.get(r['id'], False) or r[f]
561 for table in self._inherits:
562 col = self._inherits[table]
563 cols = intersect(self._inherit_fields.keys(), fields)
566 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
574 record.update(res3[record[col]])
575 if col not in fields:
578 # all fields which need to be post-processed by a simple function (symbol_get)
579 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
581 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
582 # to get the _symbol_get in each occurence
584 for f in fields_post:
585 r[f] = self.columns[f]._symbol_get(r[f])
586 ids = map(lambda x: x['id'], res)
588 # all non inherited fields for which the attribute whose name is in load is False
589 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
590 for f in fields_post:
591 # get the value of that field for all records/ids
592 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
594 record[f] = res2[record['id']]
598 account_analytic_account_summary_user()
600 class account_analytic_account_summary_month(osv.osv):
601 _name = "account_analytic_analysis.summary.month"
602 _description = "Hours summary by month"
607 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
609 account_obj = self.pool.get('account.analytic.account')
610 account_ids = [int(str(int(x))[:-6]) for x in ids]
611 month_ids = [int(str(int(x))[-6:]) for x in ids]
612 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
613 month_set = ','.join([str(x) for x in month_ids])
615 acc_set = ','.join([str(x) for x in account_ids2])
616 cr.execute('SELECT id, unit_amount ' \
617 'FROM account_analytic_analysis_summary_month ' \
618 'WHERE account_id in (%s) ' \
619 'AND month_id in (%s) ' % \
620 (acc_set, month_set))
621 for sum_id, unit_amount in cr.fetchall():
622 res[sum_id] = unit_amount
624 res.setdefault(obj_id, 0.0)
625 for child_id in account_obj.search(cr, uid,
626 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
627 if child_id != int(str(int(obj_id))[:-6]):
628 res[obj_id] += res.get(int(child_id * 1000000 + int(obj_id)), 0.0)
630 res[id] = round(res.get(id, 0.0), 2)
634 'account_id': fields.many2one('account.analytic.account', 'Analytic Account',
636 'unit_amount': fields.function(_unit_amount, method=True, type='float',
637 string='Total Time'),
638 'month': fields.char('Month', size=25, readonly=True),
641 cr.execute('DROP VIEW IF EXISTS account_analytic_analysis_summary_month')
642 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
644 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
645 'd.account_id AS account_id, ' \
646 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
647 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
648 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
655 'a.id AS account_id, ' \
656 'l.month AS month ' \
659 'DATE_TRUNC(\'month\', l.date) AS month ' \
660 'FROM account_analytic_line AS l, ' \
661 'account_analytic_journal AS j ' \
662 'WHERE j.type = \'general\' ' \
663 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
665 'account_analytic_account AS a ' \
666 'GROUP BY l.month, a.id ' \
668 'GROUP BY d2.account_id, d2.month ' \
672 'l.account_id AS account_id, ' \
673 'DATE_TRUNC(\'month\', l.date) AS month, ' \
674 'SUM(l.unit_amount) AS unit_amount ' \
675 'FROM account_analytic_line AS l, ' \
676 'account_analytic_journal AS j ' \
677 'WHERE j.type = \'general\' ' \
678 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
681 'd.account_id = l.account_id ' \
682 'AND d.month = l.month' \
684 'GROUP BY d.month, d.account_id ' \
687 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
694 fields = self._columns.keys()
696 # construct a clause for the rules :
697 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
699 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
700 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
704 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
705 for i in range(0, len(ids), cr.IN_MAX):
706 sub_ids = ids[i:i+cr.IN_MAX]
708 cr.execute('select %s from \"%s\" where id in (%s) ' \
709 'and account_id in (%s) ' \
710 'and month_id in (%s) and %s order by %s' % \
711 (','.join(fields_pre2 + ['id']), self._table,
712 ','.join([str(x) for x in sub_ids]),
713 ','.join([str(x)[:-6] for x in sub_ids]),
714 ','.join([str(x)[-6:] for x in sub_ids]), d1,
716 if not cr.rowcount == len({}.fromkeys(sub_ids)):
717 raise except_orm(_('AccessError'),
718 _('You try to bypass an access rule (Document type: %s).') % self._description)
720 cr.execute('select %s from \"%s\" where id in (%s) ' \
721 'and account_id in (%s) ' \
722 'and month_id in (%s) order by %s' % \
723 (','.join(fields_pre2 + ['id']), self._table,
724 ','.join([str(x) for x in sub_ids]),
725 ','.join([str(x)[:-6] for x in sub_ids]),
726 ','.join([str(x)[-6:] for x in sub_ids]),
728 res.extend(cr.dictfetchall())
730 res = map(lambda x: {'id': x}, ids)
733 if self._columns[f].translate:
734 ids = map(lambda x: x['id'], res)
735 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
737 r[f] = res_trans.get(r['id'], False) or r[f]
739 for table in self._inherits:
740 col = self._inherits[table]
741 cols = intersect(self._inherit_fields.keys(), fields)
744 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
752 record.update(res3[record[col]])
753 if col not in fields:
756 # all fields which need to be post-processed by a simple function (symbol_get)
757 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
759 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
760 # to get the _symbol_get in each occurence
762 for f in fields_post:
763 r[f] = self.columns[f]._symbol_get(r[f])
764 ids = map(lambda x: x['id'], res)
766 # all non inherited fields for which the attribute whose name is in load is False
767 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
768 for f in fields_post:
769 # get the value of that field for all records/ids
770 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
772 record[f] = res2[record['id']]
776 account_analytic_account_summary_month()
779 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: