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, except_orm
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 res.update(dict([(i, {}) for i in parent_ids]))
39 accounts = self.browse(cr, uid, ids, context=context)
43 cr.execute('SELECT MAX(id) FROM res_users')
44 max_user = cr.fetchone()[0]
46 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
47 'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
48 result = cr.fetchall()
52 res[id][f] = [int((id * max_user) + x[0]) for x in result]
53 elif f == 'month_ids':
55 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
56 'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
57 result = cr.fetchall()
61 res[id][f] = [int(id * 1000000 + int(x[0])) for x in result]
62 elif f == 'last_worked_invoiced_date':
66 cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
67 FROM account_analytic_line \
68 WHERE account_id IN %s \
69 AND invoice_id IS NOT NULL \
70 GROUP BY account_analytic_line.account_id;", (parent_ids,))
71 for account_id, sum in cr.fetchall():
72 if account_id not in res:
74 res[account_id][f] = sum
75 for account in accounts:
76 for child in account.child_ids:
77 if res[account.id].get(f, '') < res.get(child.id, {}).get(f, ''):
78 res[account.id][f] = res.get(child.id, {}).get(f, '')
79 elif f == 'ca_to_invoice':
84 # Amount uninvoiced hours to invoice at sale price
86 # This computation doesn't take care of pricelist !
87 # Just consider list_price
88 cr.execute("""SELECT account_analytic_account.id, \
89 COALESCE(SUM (product_template.list_price * \
90 account_analytic_line.unit_amount * \
91 ((100-hr_timesheet_invoice_factor.factor)/100)), 0.0) \
93 FROM product_template \
94 JOIN product_product \
95 ON product_template.id = product_product.product_tmpl_id \
96 JOIN account_analytic_line \
97 ON account_analytic_line.product_id = product_product.id \
98 JOIN account_analytic_journal \
99 ON account_analytic_line.journal_id = account_analytic_journal.id \
100 JOIN account_analytic_account \
101 ON account_analytic_account.id = account_analytic_line.account_id \
102 JOIN hr_timesheet_invoice_factor \
103 ON hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
104 WHERE account_analytic_account.id IN %s \
105 AND account_analytic_line.invoice_id IS NULL \
106 AND account_analytic_line.to_invoice IS NOT NULL \
107 AND account_analytic_journal.type IN ('purchase','general') \
108 GROUP BY account_analytic_account.id;""", (parent_ids,))
109 for account_id, sum in cr.fetchall():
110 if account_id not in res:
112 res[account_id][f] = round(sum, dp)
114 for account in accounts:
115 #res.setdefault(account.id, 0.0)
116 res2.setdefault(account.id, 0.0)
117 for child in account.child_ids:
118 if child.id != account.id:
119 res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
120 res2[account.id] += res2.get(child.id, 0.0)
121 # sum both result on account_id
123 res[id][f] = round(res.get(id, {}).get(f, 0.0), dp) + round(res2.get(id, 0.0), 2)
124 elif f == 'last_invoice_date':
128 cr.execute ("SELECT account_analytic_line.account_id, \
129 DATE(MAX(account_invoice.date_invoice)) \
130 FROM account_analytic_line \
131 JOIN account_invoice \
132 ON account_analytic_line.invoice_id = account_invoice.id \
133 WHERE account_analytic_line.account_id IN %s \
134 AND account_analytic_line.invoice_id IS NOT NULL \
135 GROUP BY account_analytic_line.account_id",(parent_ids,))
136 for account_id, lid in cr.fetchall():
137 res[account_id][f] = lid
138 for account in accounts:
139 for child in account.child_ids:
140 if res[account.id][f] < res.get(child.id, {}).get(f, ''):
141 res[account.id][f] = res.get(child.id, {}).get(f, '')
142 elif f == 'last_worked_date':
146 cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
147 FROM account_analytic_line \
148 WHERE account_id IN %s \
149 AND invoice_id IS NULL \
150 GROUP BY account_analytic_line.account_id",(parent_ids,))
151 for account_id, lwd in cr.fetchall():
152 if account_id not in res:
154 res[account_id][f] = lwd
155 for account in accounts:
156 for child in account.child_ids:
157 if res[account.id][f] < res.get(child.id, {}).get(f, ''):
158 res[account.id][f] = res.get(child.id, {}).get(f, '')
159 elif f == 'hours_qtt_non_invoiced':
163 cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
164 FROM account_analytic_line \
165 JOIN account_analytic_journal \
166 ON account_analytic_line.journal_id = account_analytic_journal.id \
167 WHERE account_analytic_line.account_id IN %s \
168 AND account_analytic_journal.type='general' \
169 AND invoice_id IS NULL \
170 AND to_invoice IS NOT NULL \
171 GROUP BY account_analytic_line.account_id;",(parent_ids,))
172 for account_id, sua in cr.fetchall():
173 if account_id not in res:
175 res[account_id][f] = round(sua, dp)
176 for account in accounts:
177 for child in account.child_ids:
178 if account.id != child.id:
179 res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
181 res[id][f] = round(res[id][f], dp)
182 elif f == 'hours_quantity':
186 cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
187 FROM account_analytic_line \
188 JOIN account_analytic_journal \
189 ON account_analytic_line.journal_id = account_analytic_journal.id \
190 WHERE account_analytic_line.account_id IN %s \
191 AND account_analytic_journal.type='general' \
192 GROUP BY account_analytic_line.account_id",(parent_ids,))
194 for account_id, hq in ff:
195 if account_id not in res:
197 res[account_id][f] = round(hq, dp)
198 for account in accounts:
199 for child in account.child_ids:
200 if account.id != child.id:
201 if account.id not in res:
202 res[account.id] = {f: 0.0}
203 res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
205 res[id][f] = round(res[id][f], dp)
206 elif f == 'ca_theorical':
207 # TODO Take care of pricelist and purchase !
212 # This computation doesn't take care of pricelist !
213 # Just consider list_price
215 cr.execute("""SELECT account_analytic_line.account_id AS account_id, \
216 COALESCE(SUM((account_analytic_line.unit_amount * pt.list_price) \
217 - (account_analytic_line.unit_amount * pt.list_price \
218 * hr.factor)), 0.0) AS somme
219 FROM account_analytic_line \
220 LEFT JOIN account_analytic_journal \
221 ON (account_analytic_line.journal_id = account_analytic_journal.id) \
222 JOIN product_product pp \
223 ON (account_analytic_line.product_id = pp.id) \
224 JOIN product_template pt \
225 ON (pp.product_tmpl_id = pt.id) \
226 JOIN account_analytic_account a \
227 ON (a.id=account_analytic_line.account_id) \
228 JOIN hr_timesheet_invoice_factor hr \
229 ON (hr.id=a.to_invoice) \
230 WHERE account_analytic_line.account_id IN %s \
231 AND a.to_invoice IS NOT NULL \
232 AND account_analytic_journal.type IN ('purchase', 'general')
233 GROUP BY account_analytic_line.account_id""",(parent_ids,))
234 for account_id, sum in cr.fetchall():
235 res2[account_id] = round(sum, dp)
237 for account in accounts:
238 res2.setdefault(account.id, 0.0)
239 for child in account.child_ids:
240 if account.id != child.id:
241 if account.id not in res:
242 res[account.id] = {f: 0.0}
243 res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
244 res[account.id][f] += res2.get(child.id, 0.0)
246 # sum both result on account_id
248 res[id][f] = round(res[id][f], dp) + round(res2.get(id, 0.0), dp)
252 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
255 child_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)], context=context))
264 cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
265 FROM account_analytic_line \
266 JOIN account_analytic_journal \
267 ON account_analytic_line.journal_id = account_analytic_journal.id \
268 WHERE account_analytic_line.account_id IN %s \
269 AND account_analytic_journal.type = 'sale' \
270 GROUP BY account_analytic_line.account_id", (child_ids,))
271 for account_id, sum in cr.fetchall():
272 res[account_id][name] = round(sum,2)
273 data = self._compute_level_tree(cr, uid, ids, child_ids, res, [name], context=context)
275 res_final[i] = data[i][name]
278 def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
281 child_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)], context=context))
291 cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
292 FROM account_analytic_line \
293 JOIN account_analytic_journal \
294 ON account_analytic_line.journal_id = account_analytic_journal.id \
295 WHERE account_analytic_line.account_id IN %s \
297 GROUP BY account_analytic_line.account_id""",(child_ids,))
298 for account_id, sum in cr.fetchall():
299 res[account_id][name] = round(sum,2)
300 data = self._compute_level_tree(cr, uid, ids, child_ids, res, [name], context)
302 res_final[i] = data[i][name]
305 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
307 for account in self.browse(cr, uid, ids, context=context):
308 if account.quantity_max != 0:
309 res[account.id] = account.quantity_max - account.hours_quantity
311 res[account.id] = 0.0
313 res[id] = round(res.get(id, 0.0),2)
316 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
318 for account in self.browse(cr, uid, ids, context=context):
319 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
320 if res[account.id] < 0:
321 res[account.id] = 0.0
323 res[id] = round(res.get(id, 0.0),2)
326 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
328 for account in self.browse(cr, uid, ids, context=context):
329 if account.hours_qtt_invoiced == 0:
332 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
334 res[id] = round(res.get(id, 0.0),2)
337 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
339 for account in self.browse(cr, uid, ids, context=context):
340 if account.ca_invoiced == 0:
342 elif account.total_cost != 0.0:
343 res[account.id] = -(account.real_margin / account.total_cost) * 100
345 res[account.id] = 0.0
347 res[id] = round(res.get(id, 0.0),2)
350 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
352 for account in self.browse(cr, uid, ids, context=context):
353 if account.amount_max != 0:
354 res[account.id] = account.amount_max - account.ca_invoiced
358 res[id] = round(res.get(id, 0.0),2)
361 def _real_margin_calc(self, cr, uid, ids, name, arg, context=None):
363 for account in self.browse(cr, uid, ids, context=context):
364 res[account.id] = account.ca_invoiced + account.total_cost
366 res[id] = round(res.get(id, 0.0),2)
369 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
371 for account in self.browse(cr, uid, ids, context=context):
372 res[account.id] = account.ca_theorical + account.total_cost
374 res[id] = round(res.get(id, 0.0),2)
378 'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
379 help="Total customer invoiced amount for this account.",
380 digits_compute=dp.get_precision('Account')),
381 'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
382 help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
383 digits_compute=dp.get_precision('Account')),
384 'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
385 help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
386 digits_compute=dp.get_precision('Account')),
387 'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
388 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.",
389 digits_compute=dp.get_precision('Account')),
390 'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Hours Tot',
391 help="Number of hours you spent on the analytic account (from timesheet). It computes on all journal of type 'general'."),
392 'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
393 help="If invoice from the costs, this is the date of the latest invoiced."),
394 'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
395 help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
396 'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
397 help="Date of the latest work done on this account."),
398 'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Hours',
399 help="Number of hours (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
400 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Hours',
401 help="Number of hours that can be invoiced plus those that already have been invoiced."),
402 'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Hours',
403 help="Computed using the formula: Maximum Quantity - Hours Tot."),
404 'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
405 help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
406 digits_compute=dp.get_precision('Account')),
407 'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Hours (real)',
408 help="Computed using the formula: Invoiced Amount / Hours Tot.",
409 digits_compute=dp.get_precision('Account')),
410 'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
411 help="Computed using the formula: Invoiced Amount - Total Costs.",
412 digits_compute=dp.get_precision('Account')),
413 'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
414 help="Computed using the formula: Theorial Revenue - Total Costs",
415 digits_compute=dp.get_precision('Account')),
416 'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
417 help="Computes using the formula: (Real Margin / Total Costs) * 100.",
418 digits_compute=dp.get_precision('Account')),
419 'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
420 'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
423 account_analytic_account()
425 class account_analytic_account_summary_user(osv.osv):
426 _name = "account_analytic_analysis.summary.user"
427 _description = "Hours Summary by User"
432 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
434 account_obj = self.pool.get('account.analytic.account')
435 cr.execute('SELECT MAX(id) FROM res_users')
436 max_user = cr.fetchone()[0]
437 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
438 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
439 parent_ids = tuple(account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)], context=context))
441 cr.execute('SELECT id, unit_amount ' \
442 'FROM account_analytic_analysis_summary_user ' \
443 'WHERE account_id IN %s ' \
444 'AND "user" IN %s',(parent_ids, tuple(user_ids),))
445 for sum_id, unit_amount in cr.fetchall():
446 res[sum_id] = unit_amount
448 res.setdefault(obj_id, 0.0)
449 for child_id in account_obj.search(cr, uid,
450 [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
451 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
452 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)
454 res[id] = round(res.get(id, 0.0), 2)
458 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
459 'unit_amount': fields.function(_unit_amount, type='float',
460 string='Total Time'),
461 'user': fields.many2one('res.users', 'User'),
465 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
466 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
468 '(u.account_id * u.max_user) + u."user" AS id, ' \
469 'u.account_id AS account_id, ' \
470 'u."user" AS "user", ' \
471 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
474 'a.id AS account_id, ' \
475 'u1.id AS "user", ' \
476 'MAX(u2.id) AS max_user ' \
478 'res_users AS u1, ' \
479 'res_users AS u2, ' \
480 'account_analytic_account AS a ' \
481 'GROUP BY u1.id, a.id ' \
485 'l.account_id AS account_id, ' \
486 'l.user_id AS "user", ' \
487 'SUM(l.unit_amount) AS unit_amount ' \
488 'FROM account_analytic_line AS l, ' \
489 'account_analytic_journal AS j ' \
490 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
491 'GROUP BY l.account_id, l.user_id ' \
494 'u.account_id = l.account_id ' \
495 'AND u."user" = l."user"' \
497 'GROUP BY u."user", u.account_id, u.max_user' \
500 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
507 fields = self._columns.keys()
508 res_trans_obj = self.pool.get('ir.translation')
510 # construct a clause for the rules:
511 d1, d2, tables = self.pool.get('ir.rule').domain_get(cr, user, self._name, 'read', context=context)
513 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
514 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
516 cr.execute('SELECT MAX(id) FROM res_users')
517 max_user = cr.fetchone()[0]
519 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
520 for i in range(0, len(ids), cr.IN_MAX):
521 sub_ids = ids[i:i+cr.IN_MAX]
523 cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
524 'AND account_id IN (%s) ' \
525 'AND "user" IN (%s) AND %s ORDER BY %s' % \
526 (','.join(fields_pre2 + ['id']), self._table,
527 ','.join([str(x) for x in sub_ids]),
528 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
529 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
531 if not cr.rowcount == len({}.fromkeys(sub_ids)):
532 raise except_orm(_('AccessError'),
533 _('You try to bypass an access rule (Document type: %s).') % self._description)
535 cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
536 'AND account_id IN (%s) ' \
537 'AND "user" IN (%s) ORDER BY %s' % \
538 (','.join(fields_pre2 + ['id']), self._table,
539 ','.join([str(x) for x in sub_ids]),
540 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
541 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
543 res.extend(cr.dictfetchall())
545 res = map(lambda x: {'id': x}, ids)
547 if self._columns[f].translate:
548 ids = map(lambda x: x['id'], res)
549 res_trans = res_trans_obj._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
551 r[f] = res_trans.get(r['id'], False) or r[f]
553 for table in self._inherits:
554 col = self._inherits[table]
555 cols = intersect(self._inherit_fields.keys(), fields)
558 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
566 record.update(res3[record[col]])
567 if col not in fields:
570 # all fields which need to be post-processed by a simple function (symbol_get)
571 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
573 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
574 # to get the _symbol_get in each occurence
576 for f in fields_post:
577 r[f] = self.columns[f]._symbol_get(r[f])
578 ids = map(lambda x: x['id'], res)
580 # all non inherited fields for which the attribute whose name is in load is False
581 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
582 for f in fields_post:
583 # get the value of that field for all records/ids
584 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
586 record[f] = res2[record['id']]
590 account_analytic_account_summary_user()
592 class account_analytic_account_summary_month(osv.osv):
593 _name = "account_analytic_analysis.summary.month"
594 _description = "Hours summary by month"
598 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
600 account_obj = self.pool.get('account.analytic.account')
601 account_ids = [int(str(int(x))[:-6]) for x in ids]
602 month_ids = [int(str(int(x))[-6:]) for x in ids]
603 parent_ids = tuple(account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)], context=context))
605 cr.execute('SELECT id, unit_amount ' \
606 'FROM account_analytic_analysis_summary_month ' \
607 'WHERE account_id IN %s ' \
608 'AND month_id IN %s ',(parent_ids, tuple(month_ids),))
609 for sum_id, unit_amount in cr.fetchall():
610 res[sum_id] = unit_amount
612 res.setdefault(obj_id, 0.0)
613 for child_id in account_obj.search(cr, uid,
614 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
615 if child_id != int(str(int(obj_id))[:-6]):
616 res[obj_id] += res.get(int(child_id * 1000000 + int(str(int(obj_id))[-6:])), 0.0)
618 res[id] = round(res.get(id, 0.0), 2)
622 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
623 'unit_amount': fields.function(_unit_amount, type='float', string='Total Time'),
624 'month': fields.char('Month', size=32, readonly=True),
628 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
629 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
631 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000::bigint))::bigint AS id, ' \
632 'd.account_id AS account_id, ' \
633 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
634 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
635 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
642 'a.id AS account_id, ' \
643 'l.month AS month ' \
646 'DATE_TRUNC(\'month\', l.date) AS month ' \
647 'FROM account_analytic_line AS l, ' \
648 'account_analytic_journal AS j ' \
649 'WHERE j.type = \'general\' ' \
650 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
652 'account_analytic_account AS a ' \
653 'GROUP BY l.month, a.id ' \
655 'GROUP BY d2.account_id, d2.month ' \
659 'l.account_id AS account_id, ' \
660 'DATE_TRUNC(\'month\', l.date) AS month, ' \
661 'SUM(l.unit_amount) AS unit_amount ' \
662 'FROM account_analytic_line AS l, ' \
663 'account_analytic_journal AS j ' \
664 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
665 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
668 'd.account_id = l.account_id ' \
669 'AND d.month = l.month' \
671 'GROUP BY d.month, d.account_id ' \
674 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
681 fields = self._columns.keys()
682 res_trans_obj = self.pool.get('ir.translation')
683 # construct a clause for the rules:
684 d1, d2, tables= self.pool.get('ir.rule').domain_get(cr, user, self._name)
686 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
687 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
690 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
691 for i in range(0, len(ids), cr.IN_MAX):
692 sub_ids = ids[i:i+cr.IN_MAX]
694 cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
695 'AND account_id IN (%s) ' \
696 'AND month_id IN (%s) AND %s ORDER BY %s' % \
697 (','.join(fields_pre2 + ['id']), self._table,
698 ','.join([str(x) for x in sub_ids]),
699 ','.join([str(x)[:-6] for x in sub_ids]),
700 ','.join([str(x)[-6:] for x in sub_ids]), d1,
702 if not cr.rowcount == len({}.fromkeys(sub_ids)):
703 raise except_orm(_('AccessError'),
704 _('You try to bypass an access rule (Document type: %s).') % self._description)
706 cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
707 'AND account_id IN (%s) ' \
708 'AND month_id IN (%s) ORDER BY %s' % \
709 (','.join(fields_pre2 + ['id']), self._table,
710 ','.join([str(x) for x in sub_ids]),
711 ','.join([str(x)[:-6] for x in sub_ids]),
712 ','.join([str(x)[-6:] for x in sub_ids]),
714 res.extend(cr.dictfetchall())
716 res = map(lambda x: {'id': x}, ids)
719 if self._columns[f].translate:
720 ids = map(lambda x: x['id'], res)
721 res_trans = res_trans_obj._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
723 r[f] = res_trans.get(r['id'], False) or r[f]
725 for table in self._inherits:
726 col = self._inherits[table]
727 cols = intersect(self._inherit_fields.keys(), fields)
730 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
738 record.update(res3[record[col]])
739 if col not in fields:
742 # all fields which need to be post-processed by a simple function (symbol_get)
743 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
745 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
746 # to get the _symbol_get in each occurence
748 for f in fields_post:
749 r[f] = self.columns[f]._symbol_get(r[f])
750 ids = map(lambda x: x['id'], res)
752 # all non inherited fields for which the attribute whose name is in load is False
753 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
754 for f in fields_post:
755 # get the value of that field for all records/ids
756 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
758 record[f] = res2[record['id']]
761 account_analytic_account_summary_month()
763 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: