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 ##############################################################################
22 from osv import osv, fields
23 from osv.orm import intersect
25 from tools.translate import _
26 from decimal_precision import decimal_precision as dp
29 class account_analytic_account(osv.osv):
30 _name = "account.analytic.account"
31 _inherit = "account.analytic.account"
33 def _analysis_all(self, cr, uid, ids, fields, arg, context=None):
35 res = dict([(i, {}) for i in ids])
37 parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)], context=context))
38 accounts = self.browse(cr, uid, ids, context=context)
42 cr.execute('SELECT MAX(id) FROM res_users')
43 max_user = cr.fetchone()[0]
45 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
46 'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
47 result = cr.fetchall()
51 res[id][f] = [int((id * max_user) + x[0]) for x in result]
52 elif f == 'month_ids':
54 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
55 'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
56 result = cr.fetchall()
60 res[id][f] = [int(id * 1000000 + int(x[0])) for x in result]
61 elif f == 'last_worked_invoiced_date':
65 cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
66 FROM account_analytic_line \
67 WHERE account_id IN %s \
68 AND invoice_id IS NOT NULL \
69 GROUP BY account_analytic_line.account_id;", (parent_ids,))
70 for account_id, sum in cr.fetchall():
71 if account_id not in res:
73 res[account_id][f] = sum
74 for account in accounts:
75 for child in account.child_ids:
76 if res[account.id].get(f, '') < res.get(child.id, {}).get(f, ''):
77 res[account.id][f] = res.get(child.id, {}).get(f, '')
78 elif f == 'ca_to_invoice':
83 # Amount uninvoiced hours to invoice at sale price
85 # This computation doesn't take care of pricelist !
86 # Just consider list_price
87 cr.execute("""SELECT account_analytic_account.id, \
88 COALESCE(SUM (product_template.list_price * \
89 account_analytic_line.unit_amount * \
90 ((100-hr_timesheet_invoice_factor.factor)/100)), 0.0) \
92 FROM product_template \
93 JOIN product_product \
94 ON product_template.id = product_product.product_tmpl_id \
95 JOIN account_analytic_line \
96 ON account_analytic_line.product_id = product_product.id \
97 JOIN account_analytic_journal \
98 ON account_analytic_line.journal_id = account_analytic_journal.id \
99 JOIN account_analytic_account \
100 ON account_analytic_account.id = account_analytic_line.account_id \
101 JOIN hr_timesheet_invoice_factor \
102 ON hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
103 WHERE account_analytic_account.id IN %s \
104 AND account_analytic_line.invoice_id IS NULL \
105 AND account_analytic_line.to_invoice IS NOT NULL \
106 AND account_analytic_journal.type IN ('purchase','general') \
107 GROUP BY account_analytic_account.id;""", (parent_ids,))
108 for account_id, sum in cr.fetchall():
109 if account_id not in res:
111 res[account_id][f] = round(sum, dp)
113 for account in accounts:
114 #res.setdefault(account.id, 0.0)
115 res2.setdefault(account.id, 0.0)
116 for child in account.child_ids:
117 if child.id != account.id:
118 res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
119 res2[account.id] += res2.get(child.id, 0.0)
120 # sum both result on account_id
122 res[id][f] = round(res.get(id, {}).get(f, 0.0), dp) + round(res2.get(id, 0.0), 2)
123 elif f == 'last_invoice_date':
127 cr.execute ("SELECT account_analytic_line.account_id, \
128 DATE(MAX(account_invoice.date_invoice)) \
129 FROM account_analytic_line \
130 JOIN account_invoice \
131 ON account_analytic_line.invoice_id = account_invoice.id \
132 WHERE account_analytic_line.account_id IN %s \
133 AND account_analytic_line.invoice_id IS NOT NULL \
134 GROUP BY account_analytic_line.account_id",(parent_ids,))
135 for account_id, lid in cr.fetchall():
136 res[account_id][f] = lid
137 for account in accounts:
138 for child in account.child_ids:
139 if res[account.id][f] < res.get(child.id, {}).get(f, ''):
140 res[account.id][f] = res.get(child.id, {}).get(f, '')
141 elif f == 'last_worked_date':
145 cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
146 FROM account_analytic_line \
147 WHERE account_id IN %s \
148 AND invoice_id IS NULL \
149 GROUP BY account_analytic_line.account_id",(parent_ids,))
150 for account_id, lwd in cr.fetchall():
151 if account_id not in res:
153 res[account_id][f] = lwd
154 for account in accounts:
155 for child in account.child_ids:
156 if res[account.id][f] < res.get(child.id, {}).get(f, ''):
157 res[account.id][f] = res.get(child.id, {}).get(f, '')
158 elif f == 'hours_qtt_non_invoiced':
162 cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
163 FROM account_analytic_line \
164 JOIN account_analytic_journal \
165 ON account_analytic_line.journal_id = account_analytic_journal.id \
166 WHERE account_analytic_line.account_id IN %s \
167 AND account_analytic_journal.type='general' \
168 AND invoice_id IS NULL \
169 AND to_invoice IS NOT NULL \
170 GROUP BY account_analytic_line.account_id;",(parent_ids,))
171 for account_id, sua in cr.fetchall():
172 if account_id not in res:
174 res[account_id][f] = round(sua, dp)
175 for account in accounts:
176 for child in account.child_ids:
177 if account.id != child.id:
178 res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
180 res[id][f] = round(res[id][f], dp)
181 elif f == 'hours_quantity':
185 cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
186 FROM account_analytic_line \
187 JOIN account_analytic_journal \
188 ON account_analytic_line.journal_id = account_analytic_journal.id \
189 WHERE account_analytic_line.account_id IN %s \
190 AND account_analytic_journal.type='general' \
191 GROUP BY account_analytic_line.account_id",(parent_ids,))
193 for account_id, hq in ff:
194 if account_id not in res:
196 res[account_id][f] = round(hq, dp)
197 for account in accounts:
198 for child in account.child_ids:
199 if account.id != child.id:
200 if account.id not in res:
201 res[account.id] = {f: 0.0}
202 res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
204 res[id][f] = round(res[id][f], dp)
205 elif f == 'ca_theorical':
206 # TODO Take care of pricelist and purchase !
211 # This computation doesn't take care of pricelist !
212 # Just consider list_price
214 cr.execute("""SELECT account_analytic_line.account_id AS account_id, \
215 COALESCE(SUM((account_analytic_line.unit_amount * pt.list_price) \
216 - (account_analytic_line.unit_amount * pt.list_price \
217 * hr.factor)), 0.0) AS somme
218 FROM account_analytic_line \
219 LEFT JOIN account_analytic_journal \
220 ON (account_analytic_line.journal_id = account_analytic_journal.id) \
221 JOIN product_product pp \
222 ON (account_analytic_line.product_id = pp.id) \
223 JOIN product_template pt \
224 ON (pp.product_tmpl_id = pt.id) \
225 JOIN account_analytic_account a \
226 ON (a.id=account_analytic_line.account_id) \
227 JOIN hr_timesheet_invoice_factor hr \
228 ON (hr.id=a.to_invoice) \
229 WHERE account_analytic_line.account_id IN %s \
230 AND a.to_invoice IS NOT NULL \
231 AND account_analytic_journal.type IN ('purchase', 'general')
232 GROUP BY account_analytic_line.account_id""",(parent_ids,))
233 for account_id, sum in cr.fetchall():
234 res2[account_id] = round(sum, dp)
236 for account in accounts:
237 res2.setdefault(account.id, 0.0)
238 for child in account.child_ids:
239 if account.id != child.id:
240 if account.id not in res:
241 res[account.id] = {f: 0.0}
242 res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
243 res[account.id][f] += res2.get(child.id, 0.0)
245 # sum both result on account_id
247 res[id][f] = round(res[id][f], dp) + round(res2.get(id, 0.0), dp)
251 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
254 child_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)], context=context))
263 cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
264 FROM account_analytic_line \
265 JOIN account_analytic_journal \
266 ON account_analytic_line.journal_id = account_analytic_journal.id \
267 WHERE account_analytic_line.account_id IN %s \
268 AND account_analytic_journal.type = 'sale' \
269 GROUP BY account_analytic_line.account_id", (child_ids,))
270 for account_id, sum in cr.fetchall():
271 res[account_id][name] = round(sum,2)
272 data = self._compute_level_tree(cr, uid, ids, child_ids, res, [name], context)
274 res_final[i] = data[i][name]
277 def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
280 child_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)], context=context))
290 cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
291 FROM account_analytic_line \
292 JOIN account_analytic_journal \
293 ON account_analytic_line.journal_id = account_analytic_journal.id \
294 WHERE account_analytic_line.account_id IN %s \
296 GROUP BY account_analytic_line.account_id""",(child_ids,))
297 for account_id, sum in cr.fetchall():
298 res[account_id][name] = round(sum,2)
299 data = self._compute_level_tree(cr, uid, ids, child_ids, res, [name], context)
301 res_final[i] = data[i][name]
304 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
306 for account in self.browse(cr, uid, ids, context=context):
307 if account.quantity_max != 0:
308 res[account.id] = account.quantity_max - account.hours_quantity
310 res[account.id] = 0.0
312 res[id] = round(res.get(id, 0.0),2)
315 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
317 for account in self.browse(cr, uid, ids, context=context):
318 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
319 if res[account.id] < 0:
320 res[account.id] = 0.0
322 res[id] = round(res.get(id, 0.0),2)
325 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
327 for account in self.browse(cr, uid, ids):
328 if account.hours_qtt_invoiced == 0:
331 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
333 res[id] = round(res.get(id, 0.0),2)
336 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
338 for account in self.browse(cr, uid, ids):
339 if account.ca_invoiced == 0:
341 elif account.total_cost != 0.0:
342 res[account.id] = -(account.real_margin / account.total_cost) * 100
344 res[account.id] = 0.0
346 res[id] = round(res.get(id, 0.0),2)
349 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
351 for account in self.browse(cr, uid, ids):
352 if account.amount_max != 0:
353 res[account.id] = account.amount_max - account.ca_invoiced
357 res[id] = round(res.get(id, 0.0),2)
360 def _real_margin_calc(self, cr, uid, ids, name, arg, context=None):
362 for account in self.browse(cr, uid, ids):
363 res[account.id] = account.ca_invoiced + account.total_cost
365 res[id] = round(res.get(id, 0.0),2)
368 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
370 for account in self.browse(cr, uid, ids):
371 res[account.id] = account.ca_theorical + account.total_cost
373 res[id] = round(res.get(id, 0.0),2)
377 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced Amount',
378 help="Total customer invoiced amount for this account.",
379 digits_compute=dp.get_precision('Account')),
380 'total_cost': fields.function(_total_cost_calc, method=True, type='float', string='Total Costs',
381 help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
382 digits_compute=dp.get_precision('Account')),
383 'ca_to_invoice': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
384 help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
385 digits_compute=dp.get_precision('Account')),
386 'ca_theorical': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='float', string='Theorical Revenue',
387 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.",
388 digits_compute=dp.get_precision('Account')),
389 'hours_quantity': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='float', string='Hours Tot',
390 help="Number of hours you spent on the analytic account (from timesheet). It computes on all journal of type 'general'."),
391 'last_invoice_date': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='date', string='Last Invoice Date',
392 help="Date of the last invoice created for this analytic account."),
393 'last_worked_invoiced_date': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
394 help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
395 'last_worked_date': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
396 help="Date of the latest work done on this account."),
397 'hours_qtt_non_invoiced': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='float', string='Uninvoiced Hours',
398 help="Number of hours (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
399 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, method=True, type='float', string='Invoiced Hours',
400 help="Number of hours that can be invoiced plus those that already have been invoiced."),
401 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining Hours',
402 help="Computed using the formula: Maximum Quantity - Hours Tot."),
403 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining Revenue',
404 help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
405 digits_compute=dp.get_precision('Account')),
406 'revenue_per_hour': fields.function(_revenue_per_hour_calc, method=True, type='float', string='Revenue per Hours (real)',
407 help="Computed using the formula: Invoiced Amount / Hours Tot.",
408 digits_compute=dp.get_precision('Account')),
409 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real Margin',
410 help="Computed using the formula: Invoiced Amount - Total Costs.",
411 digits_compute=dp.get_precision('Account')),
412 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical Margin',
413 help="Computed using the formula: Theorial Revenue - Total Costs",
414 digits_compute=dp.get_precision('Account')),
415 'real_margin_rate': fields.function(_real_margin_rate_calc, method=True, type='float', string='Real Margin Rate (%)',
416 help="Computes using the formula: (Real Margin / Total Costs) * 100.",
417 digits_compute=dp.get_precision('Account')),
418 'month_ids': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
419 'user_ids': fields.function(_analysis_all, method=True, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
422 account_analytic_account()
424 class account_analytic_account_summary_user(osv.osv):
425 _name = "account_analytic_analysis.summary.user"
426 _description = "Hours Summary by User"
431 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
433 account_obj = self.pool.get('account.analytic.account')
434 cr.execute('SELECT MAX(id) FROM res_users')
435 max_user = cr.fetchone()[0]
436 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
437 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
438 parent_ids = tuple(account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)], context=context))
440 cr.execute('SELECT id, unit_amount ' \
441 'FROM account_analytic_analysis_summary_user ' \
442 'WHERE account_id IN %s ' \
443 'AND "user" IN %s',(parent_ids, tuple(user_ids),))
444 for sum_id, unit_amount in cr.fetchall():
445 res[sum_id] = unit_amount
447 res.setdefault(obj_id, 0.0)
448 for child_id in account_obj.search(cr, uid,
449 [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
450 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
451 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)
453 res[id] = round(res.get(id, 0.0), 2)
457 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
458 'unit_amount': fields.function(_unit_amount, method=True, type='float',
459 string='Total Time'),
460 'user': fields.many2one('res.users', 'User'),
464 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
465 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
467 '(u.account_id * u.max_user) + u."user" AS id, ' \
468 'u.account_id AS account_id, ' \
469 'u."user" AS "user", ' \
470 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
473 'a.id AS account_id, ' \
474 'u1.id AS "user", ' \
475 'MAX(u2.id) AS max_user ' \
477 'res_users AS u1, ' \
478 'res_users AS u2, ' \
479 'account_analytic_account AS a ' \
480 'GROUP BY u1.id, a.id ' \
484 'l.account_id AS account_id, ' \
485 'l.user_id AS "user", ' \
486 'SUM(l.unit_amount) AS unit_amount ' \
487 'FROM account_analytic_line AS l, ' \
488 'account_analytic_journal AS j ' \
489 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
490 'GROUP BY l.account_id, l.user_id ' \
493 'u.account_id = l.account_id ' \
494 'AND u."user" = l."user"' \
496 'GROUP BY u."user", u.account_id, u.max_user' \
499 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
506 fields = self._columns.keys()
507 res_trans_obj = self.pool.get('ir.translation')
509 # construct a clause for the rules:
510 d1, d2, tables = self.pool.get('ir.rule').domain_get(cr, user, self._name, 'read', context=context)
512 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
513 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
515 cr.execute('SELECT MAX(id) FROM res_users')
516 max_user = cr.fetchone()[0]
518 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
519 for i in range(0, len(ids), cr.IN_MAX):
520 sub_ids = ids[i:i+cr.IN_MAX]
522 cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
523 'AND account_id IN (%s) ' \
524 'AND "user" IN (%s) AND %s ORDER BY %s' % \
525 (','.join(fields_pre2 + ['id']), self._table,
526 ','.join([str(x) for x in sub_ids]),
527 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
528 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
530 if not cr.rowcount == len({}.fromkeys(sub_ids)):
531 raise except_orm(_('AccessError'),
532 _('You try to bypass an access rule (Document type: %s).') % self._description)
534 cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
535 'AND account_id IN (%s) ' \
536 'AND "user" IN (%s) ORDER BY %s' % \
537 (','.join(fields_pre2 + ['id']), self._table,
538 ','.join([str(x) for x in sub_ids]),
539 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
540 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
542 res.extend(cr.dictfetchall())
544 res = map(lambda x: {'id': x}, ids)
546 if self._columns[f].translate:
547 ids = map(lambda x: x['id'], res)
548 res_trans = res_trans_obj._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
550 r[f] = res_trans.get(r['id'], False) or r[f]
552 for table in self._inherits:
553 col = self._inherits[table]
554 cols = intersect(self._inherit_fields.keys(), fields)
557 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
565 record.update(res3[record[col]])
566 if col not in fields:
569 # all fields which need to be post-processed by a simple function (symbol_get)
570 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
572 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
573 # to get the _symbol_get in each occurence
575 for f in fields_post:
576 r[f] = self.columns[f]._symbol_get(r[f])
577 ids = map(lambda x: x['id'], res)
579 # all non inherited fields for which the attribute whose name is in load is False
580 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
581 for f in fields_post:
582 # get the value of that field for all records/ids
583 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
585 record[f] = res2[record['id']]
589 account_analytic_account_summary_user()
591 class account_analytic_account_summary_month(osv.osv):
592 _name = "account_analytic_analysis.summary.month"
593 _description = "Hours summary by month"
597 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
599 account_obj = self.pool.get('account.analytic.account')
600 account_ids = [int(str(int(x))[:-6]) for x in ids]
601 month_ids = [int(str(int(x))[-6:]) for x in ids]
602 parent_ids = tuple(account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)], context=context))
604 cr.execute('SELECT id, unit_amount ' \
605 'FROM account_analytic_analysis_summary_month ' \
606 'WHERE account_id IN %s ' \
607 'AND month_id IN %s ',(parent_ids, tuple(month_ids),))
608 for sum_id, unit_amount in cr.fetchall():
609 res[sum_id] = unit_amount
611 res.setdefault(obj_id, 0.0)
612 for child_id in account_obj.search(cr, uid,
613 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
614 if child_id != int(str(int(obj_id))[:-6]):
615 res[obj_id] += res.get(int(child_id * 1000000 + int(str(int(obj_id))[-6:])), 0.0)
617 res[id] = round(res.get(id, 0.0), 2)
621 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
622 'unit_amount': fields.function(_unit_amount, method=True, type='float', string='Total Time'),
623 'month': fields.char('Month', size=32, readonly=True),
627 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
628 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
630 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
631 'd.account_id AS account_id, ' \
632 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
633 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
634 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
641 'a.id AS account_id, ' \
642 'l.month AS month ' \
645 'DATE_TRUNC(\'month\', l.date) AS month ' \
646 'FROM account_analytic_line AS l, ' \
647 'account_analytic_journal AS j ' \
648 'WHERE j.type = \'general\' ' \
649 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
651 'account_analytic_account AS a ' \
652 'GROUP BY l.month, a.id ' \
654 'GROUP BY d2.account_id, d2.month ' \
658 'l.account_id AS account_id, ' \
659 'DATE_TRUNC(\'month\', l.date) AS month, ' \
660 'SUM(l.unit_amount) AS unit_amount ' \
661 'FROM account_analytic_line AS l, ' \
662 'account_analytic_journal AS j ' \
663 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
664 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
667 'd.account_id = l.account_id ' \
668 'AND d.month = l.month' \
670 'GROUP BY d.month, d.account_id ' \
673 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
680 fields = self._columns.keys()
681 res_trans_obj = self.pool.get('ir.translation')
682 # construct a clause for the rules:
683 d1, d2, tables= self.pool.get('ir.rule').domain_get(cr, user, self._name)
685 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
686 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
689 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
690 for i in range(0, len(ids), cr.IN_MAX):
691 sub_ids = ids[i:i+cr.IN_MAX]
693 cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
694 'AND account_id IN (%s) ' \
695 'AND month_id IN (%s) AND %s ORDER BY %s' % \
696 (','.join(fields_pre2 + ['id']), self._table,
697 ','.join([str(x) for x in sub_ids]),
698 ','.join([str(x)[:-6] for x in sub_ids]),
699 ','.join([str(x)[-6:] for x in sub_ids]), d1,
701 if not cr.rowcount == len({}.fromkeys(sub_ids)):
702 raise except_orm(_('AccessError'),
703 _('You try to bypass an access rule (Document type: %s).') % self._description)
705 cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
706 'AND account_id IN (%s) ' \
707 'AND month_id IN (%s) ORDER BY %s' % \
708 (','.join(fields_pre2 + ['id']), self._table,
709 ','.join([str(x) for x in sub_ids]),
710 ','.join([str(x)[:-6] for x in sub_ids]),
711 ','.join([str(x)[-6:] for x in sub_ids]),
713 res.extend(cr.dictfetchall())
715 res = map(lambda x: {'id': x}, ids)
718 if self._columns[f].translate:
719 ids = map(lambda x: x['id'], res)
720 res_trans = res_trans_obj._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
722 r[f] = res_trans.get(r['id'], False) or r[f]
724 for table in self._inherits:
725 col = self._inherits[table]
726 cols = intersect(self._inherit_fields.keys(), fields)
729 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
737 record.update(res3[record[col]])
738 if col not in fields:
741 # all fields which need to be post-processed by a simple function (symbol_get)
742 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
744 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
745 # to get the _symbol_get in each occurence
747 for f in fields_post:
748 r[f] = self.columns[f]._symbol_get(r[f])
749 ids = map(lambda x: x['id'], res)
751 # all non inherited fields for which the attribute whose name is in load is False
752 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
753 for f in fields_post:
754 # get the value of that field for all records/ids
755 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
757 record[f] = res2[record['id']]
760 account_analytic_account_summary_month()
762 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: