1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
7 # This program is free software: you can redistribute it and/or modify
8 # it under the terms of the GNU Affero General Public License as
9 # published by the Free Software Foundation, either version 3 of the
10 # License, or (at your option) any later version.
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU Affero General Public License for more details.
17 # You should have received a copy of the GNU Affero General Public License
18 # along with this program. If not, see <http://www.gnu.org/licenses/>.
20 ##############################################################################
23 from osv import osv, fields
24 from osv.orm import intersect
26 from tools.translate import _
29 class account_analytic_account(osv.osv):
30 _name = "account.analytic.account"
31 _inherit = "account.analytic.account"
33 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
35 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
37 acc_set = ",".join(map(str, ids2))
38 cr.execute("select account_analytic_line.account_id, COALESCE(sum(amount_currency),0.0) \
39 from account_analytic_line \
40 join account_analytic_journal \
41 on account_analytic_line.journal_id = account_analytic_journal.id \
42 where account_analytic_line.account_id =ANY(%s) \
43 and account_analytic_journal.type = 'sale' \
44 group by account_analytic_line.account_id" ,(ids2,))
45 for account_id, sum in cr.fetchall():
46 res[account_id] = round(sum,2)
48 return self._compute_currency_for_level_tree(cr, uid, ids, ids2, res, acc_set, context)
50 def _ca_to_invoice_calc(self, cr, uid, ids, name, arg, context={}):
53 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
55 # Amount uninvoiced hours to invoice at sale price
57 # This computation doesn't take care of pricelist !
58 # Just consider list_price
59 acc_set = ",".join(map(str, ids2))
60 cr.execute("""SELECT account_analytic_account.id, \
61 COALESCE(sum (product_template.list_price * \
62 account_analytic_line.unit_amount * \
63 ((100-hr_timesheet_invoice_factor.factor)/100)),0.0) \
65 FROM product_template \
66 join product_product \
67 on product_template.id = product_product.product_tmpl_id \
68 JOIN account_analytic_line \
69 on account_analytic_line.product_id = product_product.id \
70 JOIN account_analytic_journal \
71 on account_analytic_line.journal_id = account_analytic_journal.id \
72 JOIN account_analytic_account \
73 on account_analytic_account.id = account_analytic_line.account_id \
74 JOIN hr_timesheet_invoice_factor \
75 on hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
76 WHERE account_analytic_account.id =ANY(%s) \
77 AND account_analytic_line.invoice_id is null \
78 AND account_analytic_line.to_invoice IS NOT NULL \
79 and account_analytic_journal.type in ('purchase','general') \
80 GROUP BY account_analytic_account.id;""",(ids2,))
81 for account_id, sum in cr.fetchall():
82 res[account_id] = round(sum,2)
85 res.setdefault(obj_id, 0.0)
86 res2.setdefault(obj_id, 0.0)
87 for child_id in self.search(cr, uid,
88 [('parent_id', 'child_of', [obj_id])]):
89 if child_id != obj_id:
90 res[obj_id] += res.get(child_id, 0.0)
91 res2[obj_id] += res2.get(child_id, 0.0)
92 # sum both result on account_id
94 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
97 def _hours_qtt_non_invoiced_calc (self, cr, uid, ids, name, arg, context={}):
99 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
101 cr.execute("select account_analytic_line.account_id, COALESCE(sum(unit_amount),0.0) \
102 from account_analytic_line \
103 join account_analytic_journal \
104 on account_analytic_line.journal_id = account_analytic_journal.id \
105 where account_analytic_line.account_id =ANY(%s) \
106 and account_analytic_journal.type='general' \
107 and invoice_id is null \
108 AND to_invoice IS NOT NULL \
109 GROUP BY account_analytic_line.account_id;",(ids2,))
110 for account_id, sum in cr.fetchall():
111 res[account_id] = round(sum,2)
113 res.setdefault(obj_id, 0.0)
114 for child_id in self.search(cr, uid,
115 [('parent_id', 'child_of', [obj_id])]):
116 if child_id != obj_id:
117 res[obj_id] += res.get(child_id, 0.0)
119 res[id] = round(res.get(id, 0.0),2)
122 def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
124 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
126 cr.execute("select account_analytic_line.account_id,COALESCE(SUM(unit_amount),0.0) \
127 from account_analytic_line \
128 join account_analytic_journal \
129 on account_analytic_line.journal_id = account_analytic_journal.id \
130 where account_analytic_line.account_id =ANY(%s) \
131 and account_analytic_journal.type='general' \
132 GROUP BY account_analytic_line.account_id",(ids2,))
134 for account_id, sum in ff:
135 res[account_id] = round(sum,2)
137 res.setdefault(obj_id, 0.0)
138 for child_id in self.search(cr, uid,
139 [('parent_id', 'child_of', [obj_id])]):
140 if child_id != obj_id:
141 res[obj_id] += res.get(child_id, 0.0)
143 res[id] = round(res.get(id, 0.0),2)
146 def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
148 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
150 acc_set = ",".join(map(str, ids2))
151 cr.execute("""select account_analytic_line.account_id,COALESCE(sum(amount_currency),0.0) \
153 from account_analytic_line \
154 join account_analytic_journal \
155 on account_analytic_line.journal_id = account_analytic_journal.id \
156 where account_analytic_line.account_id =ANY(%s) \
158 GROUP BY account_analytic_line.account_id""",(ids2,))
159 for account_id, sum in cr.fetchall():
160 res[account_id] = round(sum,2)
161 return self._compute_currency_for_level_tree(cr, uid, ids, ids2, res, acc_set, context)
163 # TODO Take care of pricelist and purchase !
164 def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
167 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
169 # This computation doesn't take care of pricelist !
170 # Just consider list_price
172 cr.execute("""select account_analytic_line.account_id as account_id, \
173 COALESCE(sum((account_analytic_line.unit_amount * pt.list_price) \
174 - (account_analytic_line.unit_amount * pt.list_price \
175 * hr.factor)),0.0) as somme
176 from account_analytic_line \
177 left join account_analytic_journal \
178 on (account_analytic_line.journal_id = account_analytic_journal.id) \
179 join product_product pp \
180 on (account_analytic_line.product_id = pp.id) \
181 join product_template pt \
182 on (pp.product_tmpl_id = pt.id) \
183 join account_analytic_account a \
184 on (a.id=account_analytic_line.account_id) \
185 join hr_timesheet_invoice_factor hr \
186 on (hr.id=a.to_invoice) \
187 where account_analytic_line.account_id =ANY(%s) \
188 and a.to_invoice IS NOT NULL \
189 and account_analytic_journal.type in ('purchase','general')
190 GROUP BY account_analytic_line.account_id""",(ids2,))
191 for account_id, sum in cr.fetchall():
192 res2[account_id] = round(sum,2)
195 res.setdefault(obj_id, 0.0)
196 res2.setdefault(obj_id, 0.0)
197 for child_id in self.search(cr, uid,
198 [('parent_id', 'child_of', [obj_id])]):
199 if child_id != obj_id:
200 res[obj_id] += res.get(child_id, 0.0)
201 res[obj_id] += res2.get(child_id, 0.0)
203 # sum both result on account_id
205 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
208 def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
210 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
212 cr.execute("select account_analytic_line.account_id, max(date) \
213 from account_analytic_line \
214 where account_id =ANY(%s) \
215 and invoice_id is null \
216 GROUP BY account_analytic_line.account_id" ,(ids2,))
217 for account_id, sum in cr.fetchall():
218 res[account_id] = sum
220 res.setdefault(obj_id, '')
221 for child_id in self.search(cr, uid,
222 [('parent_id', 'child_of', [obj_id])]):
223 if res[obj_id] < res.get(child_id, ''):
224 res[obj_id] = res.get(child_id, '')
226 res[id] = res.get(id, '')
229 def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
231 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
233 cr.execute ("select account_analytic_line.account_id, \
234 date(max(account_invoice.date_invoice)) \
235 from account_analytic_line \
236 join account_invoice \
237 on account_analytic_line.invoice_id = account_invoice.id \
238 where account_analytic_line.account_id =ANY(%s) \
239 and account_analytic_line.invoice_id is not null \
240 GROUP BY account_analytic_line.account_id",(ids2,))
241 for account_id, sum in cr.fetchall():
242 res[account_id] = sum
244 res.setdefault(obj_id, '')
245 for child_id in self.search(cr, uid,
246 [('parent_id', 'child_of', [obj_id])]):
247 if res[obj_id] < res.get(child_id, ''):
248 res[obj_id] = res.get(child_id, '')
250 res[id] = res.get(id, '')
253 def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
255 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
257 cr.execute("select account_analytic_line.account_id, max(date) \
258 from account_analytic_line \
259 where account_id =ANY(%s) \
260 and invoice_id is not null \
261 GROUP BY account_analytic_line.account_id;",(ids2,))
262 for account_id, sum in cr.fetchall():
263 res[account_id] = sum
265 res.setdefault(obj_id, '')
266 for child_id in self.search(cr, uid,
267 [('parent_id', 'child_of', [obj_id])]):
268 if res[obj_id] < res.get(child_id, ''):
269 res[obj_id] = res.get(child_id, '')
271 res[id] = res.get(id, '')
274 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
276 for account in self.browse(cr, uid, ids):
277 if account.quantity_max != 0:
278 res[account.id] = account.quantity_max - account.hours_quantity
282 res[id] = round(res.get(id, 0.0),2)
285 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
287 for account in self.browse(cr, uid, ids):
288 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
289 if res[account.id] < 0:
292 res[id] = round(res.get(id, 0.0),2)
295 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
297 for account in self.browse(cr, uid, ids):
298 if account.hours_qtt_invoiced == 0:
301 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
303 res[id] = round(res.get(id, 0.0),2)
306 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
308 for account in self.browse(cr, uid, ids):
309 if account.ca_invoiced == 0:
311 elif account.total_cost != 0.0:
312 res[account.id] = -(account.real_margin / account.total_cost) * 100
314 res[account.id] = 0.0
316 res[id] = round(res.get(id, 0.0),2)
319 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
321 for account in self.browse(cr, uid, ids):
322 if account.amount_max != 0:
323 res[account.id] = account.amount_max - account.ca_invoiced
327 res[id] = round(res.get(id, 0.0),2)
330 def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
332 for account in self.browse(cr, uid, ids):
333 res[account.id] = account.ca_invoiced + account.total_cost
335 res[id] = round(res.get(id, 0.0),2)
338 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context={}):
340 for account in self.browse(cr, uid, ids):
341 res[account.id] = account.ca_theorical + account.total_cost
343 res[id] = round(res.get(id, 0.0),2)
346 def _month(self, cr, uid, ids, name, arg, context=None):
349 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
351 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
352 'WHERE account_id =ANY(%s) AND unit_amount <> 0.0',(ids2,))
353 res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
358 def _user(self, cr, uid, ids, name, arg, context=None):
360 cr.execute('SELECT MAX(id) FROM res_users')
361 max_user = cr.fetchone()[0]
363 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
365 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
366 'WHERE account_id =ANY(%s) AND unit_amount <> 0.0',(ids2,))
367 res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
373 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced Amount', help="Total customer invoiced amount for this account."),
374 'total_cost': fields.function(_total_cost_calc, method=True, type='float', string='Total Costs', help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets."),
375 'ca_to_invoice': fields.function(_ca_to_invoice_calc, method=True, type='float', string='Uninvoiced Amount', help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs."),
376 'ca_theorical': fields.function(_ca_theorical_calc, method=True, type='float', string='Theorical Revenue', help="Based on the costs you had on the project, what would have been the revenue if all these costs have been invoiced at the normal sale price provided by the pricelist."),
377 'hours_quantity': fields.function(_hours_quantity_calc, method=True, type='float', string='Hours Tot', help="Number of hours you spent on the analytic account (from timesheet). It computes on all journal of type 'general'."),
378 'last_invoice_date': fields.function(_last_invoice_date_calc, method=True, type='date', string='Last Invoice Date', help="Date of the last invoice created for this analytic account."),
379 'last_worked_invoiced_date': fields.function(_last_worked_invoiced_date_calc, method=True, type='date', string='Date of Last Invoiced Cost', help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
380 'last_worked_date': fields.function(_last_worked_date_calc, method=True, type='date', string='Date of Last Cost/Work', help="Date of the latest work done on this account."),
381 'hours_qtt_non_invoiced': fields.function(_hours_qtt_non_invoiced_calc, method=True, type='float', string='Uninvoiced Hours', help="Number of hours (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
382 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, method=True, type='float', string='Invoiced Hours', help="Number of hours that can be invoiced plus those that already have been invoiced."),
383 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining Hours', help="Computed using the formula: Maximum Quantity - Hours Tot."),
384 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining Revenue', help="Computed using the formula: Max Invoice Price - Invoiced Amount."),
385 'revenue_per_hour': fields.function(_revenue_per_hour_calc, method=True, type='float', string='Revenue per Hours (real)', help="Computed using the formula: Invoiced Amount / Hours Tot."),
386 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real Margin', help="Computed using the formula: Invoiced Amount - Total Costs."),
387 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical Margin', help="Computed using the formula: Theorial Revenue - Total Costs"),
388 'real_margin_rate': fields.function(_real_margin_rate_calc, method=True, type='float', string='Real Margin Rate (%)', help="Computes using the formula: (Real Margin / Total Costs) * 100."),
389 'month_ids': fields.function(_month, method=True, type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
390 'user_ids': fields.function(_user, method=True, type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
392 account_analytic_account()
394 class account_analytic_account_summary_user(osv.osv):
395 _name = "account_analytic_analysis.summary.user"
396 _description = "Hours summary by user"
401 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
403 account_obj = self.pool.get('account.analytic.account')
404 cr.execute('SELECT MAX(id) FROM res_users')
405 max_user = cr.fetchone()[0]
406 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
407 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
408 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
410 cr.execute('SELECT id, unit_amount ' \
411 'FROM account_analytic_analysis_summary_user ' \
412 'WHERE account_id =ANY(%s) ' \
413 'AND "user" =ANY(%s)',(account_ids2, user_ids,))
414 for sum_id, unit_amount in cr.fetchall():
415 res[sum_id] = unit_amount
417 res.setdefault(obj_id, 0.0)
418 for child_id in account_obj.search(cr, uid,
419 [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
420 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
421 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)
423 res[id] = round(res.get(id, 0.0), 2)
427 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
428 'unit_amount': fields.function(_unit_amount, method=True, type='float',
429 string='Total Time'),
430 'user' : fields.many2one('res.users', 'User'),
433 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
434 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
436 '(u.account_id * u.max_user) + u."user" AS id, ' \
437 'u.account_id AS account_id, ' \
438 'u."user" AS "user", ' \
439 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
442 'a.id AS account_id, ' \
443 'u1.id AS "user", ' \
444 'MAX(u2.id) AS max_user ' \
446 'res_users AS u1, ' \
447 'res_users AS u2, ' \
448 'account_analytic_account AS a ' \
449 'GROUP BY u1.id, a.id ' \
453 'l.account_id AS account_id, ' \
454 'l.user_id AS "user", ' \
455 'SUM(l.unit_amount) AS unit_amount ' \
456 'FROM account_analytic_line AS l, ' \
457 'account_analytic_journal AS j ' \
458 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
459 'GROUP BY l.account_id, l.user_id ' \
462 'u.account_id = l.account_id ' \
463 'AND u."user" = l."user"' \
465 'GROUP BY u."user", u.account_id, u.max_user' \
468 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
475 fields = self._columns.keys()
477 # construct a clause for the rules :
478 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
480 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
481 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
484 cr.execute('SELECT MAX(id) FROM res_users')
485 max_user = cr.fetchone()[0]
487 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
488 for i in range(0, len(ids), cr.IN_MAX):
489 sub_ids = ids[i:i+cr.IN_MAX]
491 cr.execute('select %s from \"%s\" where id in (%s) ' \
492 'and account_id in (%s) ' \
493 'and "user" in (%s) and %s order by %s' % \
494 (','.join(fields_pre2 + ['id']), self._table,
495 ','.join([str(x) for x in sub_ids]),
496 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
497 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
499 if not cr.rowcount == len({}.fromkeys(sub_ids)):
500 raise except_orm(_('AccessError'),
501 _('You try to bypass an access rule (Document type: %s).') % self._description)
503 cr.execute('select %s from \"%s\" where id in (%s) ' \
504 'and account_id in (%s) ' \
505 'and "user" in (%s) order by %s' % \
506 (','.join(fields_pre2 + ['id']), self._table,
507 ','.join([str(x) for x in sub_ids]),
508 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
509 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
511 res.extend(cr.dictfetchall())
513 res = map(lambda x: {'id': x}, ids)
516 if self._columns[f].translate:
517 ids = map(lambda x: x['id'], res)
518 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
520 r[f] = res_trans.get(r['id'], False) or r[f]
522 for table in self._inherits:
523 col = self._inherits[table]
524 cols = intersect(self._inherit_fields.keys(), fields)
527 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
535 record.update(res3[record[col]])
536 if col not in fields:
539 # all fields which need to be post-processed by a simple function (symbol_get)
540 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
542 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
543 # to get the _symbol_get in each occurence
545 for f in fields_post:
546 r[f] = self.columns[f]._symbol_get(r[f])
547 ids = map(lambda x: x['id'], res)
549 # all non inherited fields for which the attribute whose name is in load is False
550 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
551 for f in fields_post:
552 # get the value of that field for all records/ids
553 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
555 record[f] = res2[record['id']]
559 account_analytic_account_summary_user()
561 class account_analytic_account_summary_month(osv.osv):
562 _name = "account_analytic_analysis.summary.month"
563 _description = "Hours summary by month"
568 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
570 account_obj = self.pool.get('account.analytic.account')
571 account_ids = [int(str(int(x))[:-6]) for x in ids]
572 month_ids = [int(str(int(x))[-6:]) for x in ids]
573 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
575 cr.execute('SELECT id, unit_amount ' \
576 'FROM account_analytic_analysis_summary_month ' \
577 'WHERE account_id =ANY(%s) ' \
578 'AND month_id =ANY(%s) ',(account_ids2, month_ids,))
579 for sum_id, unit_amount in cr.fetchall():
580 res[sum_id] = unit_amount
582 res.setdefault(obj_id, 0.0)
583 for child_id in account_obj.search(cr, uid,
584 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
585 if child_id != int(str(int(obj_id))[:-6]):
586 res[obj_id] += res.get(int(child_id * 1000000 + int(str(int(obj_id))[-6:])), 0.0)
588 res[id] = round(res.get(id, 0.0), 2)
592 'account_id': fields.many2one('account.analytic.account', 'Analytic Account',
594 'unit_amount': fields.function(_unit_amount, method=True, type='float',
595 string='Total Time'),
596 'month': fields.char('Month', size=25, readonly=True),
600 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
601 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
603 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
604 'd.account_id AS account_id, ' \
605 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
606 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
607 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
614 'a.id AS account_id, ' \
615 'l.month AS month ' \
618 'DATE_TRUNC(\'month\', l.date) AS month ' \
619 'FROM account_analytic_line AS l, ' \
620 'account_analytic_journal AS j ' \
621 'WHERE j.type = \'general\' ' \
622 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
624 'account_analytic_account AS a ' \
625 'GROUP BY l.month, a.id ' \
627 'GROUP BY d2.account_id, d2.month ' \
631 'l.account_id AS account_id, ' \
632 'DATE_TRUNC(\'month\', l.date) AS month, ' \
633 'SUM(l.unit_amount) AS unit_amount ' \
634 'FROM account_analytic_line AS l, ' \
635 'account_analytic_journal AS j ' \
636 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
637 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
640 'd.account_id = l.account_id ' \
641 'AND d.month = l.month' \
643 'GROUP BY d.month, d.account_id ' \
646 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
653 fields = self._columns.keys()
655 # construct a clause for the rules :
656 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
658 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
659 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
663 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
664 for i in range(0, len(ids), cr.IN_MAX):
665 sub_ids = ids[i:i+cr.IN_MAX]
667 cr.execute('select %s from \"%s\" where id in (%s) ' \
668 'and account_id in (%s) ' \
669 'and month_id in (%s) and %s order by %s' % \
670 (','.join(fields_pre2 + ['id']), self._table,
671 ','.join([str(x) for x in sub_ids]),
672 ','.join([str(x)[:-6] for x in sub_ids]),
673 ','.join([str(x)[-6:] for x in sub_ids]), d1,
675 if not cr.rowcount == len({}.fromkeys(sub_ids)):
676 raise except_orm(_('AccessError'),
677 _('You try to bypass an access rule (Document type: %s).') % self._description)
679 cr.execute('select %s from \"%s\" where id in (%s) ' \
680 'and account_id in (%s) ' \
681 'and month_id in (%s) order by %s' % \
682 (','.join(fields_pre2 + ['id']), self._table,
683 ','.join([str(x) for x in sub_ids]),
684 ','.join([str(x)[:-6] for x in sub_ids]),
685 ','.join([str(x)[-6:] for x in sub_ids]),
687 res.extend(cr.dictfetchall())
689 res = map(lambda x: {'id': x}, ids)
692 if self._columns[f].translate:
693 ids = map(lambda x: x['id'], res)
694 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
696 r[f] = res_trans.get(r['id'], False) or r[f]
698 for table in self._inherits:
699 col = self._inherits[table]
700 cols = intersect(self._inherit_fields.keys(), fields)
703 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
711 record.update(res3[record[col]])
712 if col not in fields:
715 # all fields which need to be post-processed by a simple function (symbol_get)
716 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
718 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
719 # to get the _symbol_get in each occurence
721 for f in fields_post:
722 r[f] = self.columns[f]._symbol_get(r[f])
723 ids = map(lambda x: x['id'], res)
725 # all non inherited fields for which the attribute whose name is in load is False
726 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
727 for f in fields_post:
728 # get the value of that field for all records/ids
729 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
731 record[f] = res2[record['id']]
735 account_analytic_account_summary_month()
738 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: