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])
36 parent_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
37 accounts = self.browse(cr, uid, ids, context=context)
41 cr.execute('SELECT MAX(id) FROM res_users')
42 max_user = cr.fetchone()[0]
44 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
45 'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
46 result = cr.fetchall()
50 res[id][f] = [int((id * max_user) + x[0]) for x in result]
51 elif f == 'month_ids':
53 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
54 'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
55 result = cr.fetchall()
59 res[id][f] = [int(id * 1000000 + int(x[0])) for x in result]
60 elif f == 'last_worked_invoiced_date':
64 cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
65 FROM account_analytic_line \
66 WHERE account_id IN %s \
67 AND invoice_id IS NOT NULL \
68 GROUP BY account_analytic_line.account_id;", (parent_ids,))
69 for account_id, sum in cr.fetchall():
70 if account_id not in res:
72 res[account_id][f] = sum
73 elif f == 'ca_to_invoice':
78 # Amount uninvoiced hours to invoice at sale price
80 # This computation doesn't take care of pricelist !
81 # Just consider list_price
82 cr.execute("""SELECT account_analytic_account.id, \
83 COALESCE(SUM (product_template.list_price * \
84 account_analytic_line.unit_amount * \
85 ((100-hr_timesheet_invoice_factor.factor)/100)), 0.0) \
87 FROM product_template \
88 JOIN product_product \
89 ON product_template.id = product_product.product_tmpl_id \
90 JOIN account_analytic_line \
91 ON account_analytic_line.product_id = product_product.id \
92 JOIN account_analytic_journal \
93 ON account_analytic_line.journal_id = account_analytic_journal.id \
94 JOIN account_analytic_account \
95 ON account_analytic_account.id = account_analytic_line.account_id \
96 JOIN hr_timesheet_invoice_factor \
97 ON hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
98 WHERE account_analytic_account.id IN %s \
99 AND account_analytic_line.invoice_id IS NULL \
100 AND account_analytic_line.to_invoice IS NOT NULL \
101 AND account_analytic_journal.type IN ('purchase', 'general') \
102 GROUP BY account_analytic_account.id;""", (parent_ids,))
103 for account_id, sum in cr.fetchall():
104 if account_id not in res:
106 res[account_id][f] = round(sum, dp)
108 # sum both result on account_id
110 res[id][f] = round(res.get(id, {}).get(f, 0.0), dp) + round(res2.get(id, 0.0), 2)
111 elif f == 'last_invoice_date':
115 cr.execute ("SELECT account_analytic_line.account_id, \
116 DATE(MAX(account_invoice.date_invoice)) \
117 FROM account_analytic_line \
118 JOIN account_invoice \
119 ON account_analytic_line.invoice_id = account_invoice.id \
120 WHERE account_analytic_line.account_id IN %s \
121 AND account_analytic_line.invoice_id IS NOT NULL \
122 GROUP BY account_analytic_line.account_id",(parent_ids,))
123 for account_id, lid in cr.fetchall():
124 res[account_id][f] = lid
125 elif f == 'last_worked_date':
129 cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
130 FROM account_analytic_line \
131 WHERE account_id IN %s \
132 AND invoice_id IS NULL \
133 GROUP BY account_analytic_line.account_id",(parent_ids,))
134 for account_id, lwd in cr.fetchall():
135 if account_id not in res:
137 res[account_id][f] = lwd
138 elif f == 'hours_qtt_non_invoiced':
142 cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
143 FROM account_analytic_line \
144 JOIN account_analytic_journal \
145 ON account_analytic_line.journal_id = account_analytic_journal.id \
146 WHERE account_analytic_line.account_id IN %s \
147 AND account_analytic_journal.type='general' \
148 AND invoice_id IS NULL \
149 AND to_invoice IS NOT NULL \
150 GROUP BY account_analytic_line.account_id;",(parent_ids,))
151 for account_id, sua in cr.fetchall():
152 if account_id not in res:
154 res[account_id][f] = round(sua, dp)
156 res[id][f] = round(res[id][f], dp)
157 elif f == 'hours_quantity':
161 cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
162 FROM account_analytic_line \
163 JOIN account_analytic_journal \
164 ON account_analytic_line.journal_id = account_analytic_journal.id \
165 WHERE account_analytic_line.account_id IN %s \
166 AND account_analytic_journal.type='general' \
167 GROUP BY account_analytic_line.account_id",(parent_ids,))
169 for account_id, hq in ff:
170 if account_id not in res:
172 res[account_id][f] = round(hq, dp)
174 res[id][f] = round(res[id][f], dp)
175 elif f == 'ca_theorical':
176 # TODO Take care of pricelist and purchase !
180 # This computation doesn't take care of pricelist !
181 # Just consider list_price
183 cr.execute("""SELECT account_analytic_line.account_id AS account_id, \
184 COALESCE(SUM((account_analytic_line.unit_amount * pt.list_price) \
185 - (account_analytic_line.unit_amount * pt.list_price \
186 * hr.factor)), 0.0) AS somme
187 FROM account_analytic_line \
188 LEFT JOIN account_analytic_journal \
189 ON (account_analytic_line.journal_id = account_analytic_journal.id) \
190 JOIN product_product pp \
191 ON (account_analytic_line.product_id = pp.id) \
192 JOIN product_template pt \
193 ON (pp.product_tmpl_id = pt.id) \
194 JOIN account_analytic_account a \
195 ON (a.id=account_analytic_line.account_id) \
196 JOIN hr_timesheet_invoice_factor hr \
197 ON (hr.id=a.to_invoice) \
198 WHERE account_analytic_line.account_id IN %s \
199 AND a.to_invoice IS NOT NULL \
200 AND account_analytic_journal.type IN ('purchase', 'general')
201 GROUP BY account_analytic_line.account_id""",(parent_ids,))
202 for account_id, sum in cr.fetchall():
203 res[account_id][f] = round(sum, dp)
206 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
209 child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
216 cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
217 FROM account_analytic_line \
218 JOIN account_analytic_journal \
219 ON account_analytic_line.journal_id = account_analytic_journal.id \
220 WHERE account_analytic_line.account_id IN %s \
221 AND account_analytic_journal.type = 'sale' \
222 GROUP BY account_analytic_line.account_id", (child_ids,))
223 for account_id, sum in cr.fetchall():
224 res[account_id] = round(sum,2)
228 def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
231 child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
237 cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
238 FROM account_analytic_line \
239 JOIN account_analytic_journal \
240 ON account_analytic_line.journal_id = account_analytic_journal.id \
241 WHERE account_analytic_line.account_id IN %s \
243 GROUP BY account_analytic_line.account_id""",(child_ids,))
244 for account_id, sum in cr.fetchall():
245 res[account_id] = round(sum,2)
249 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
251 for account in self.browse(cr, uid, ids, context=context):
252 if account.quantity_max != 0:
253 res[account.id] = account.quantity_max - account.hours_quantity
255 res[account.id] = 0.0
257 res[id] = round(res.get(id, 0.0),2)
260 def _remaining_hours_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
262 for account in self.browse(cr, uid, ids, context=context):
263 if account.quantity_max != 0:
264 res[account.id] = account.quantity_max - account.hours_qtt_invoiced
266 res[account.id] = 0.0
268 res[id] = round(res.get(id, 0.0),2)
271 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
273 for account in self.browse(cr, uid, ids, context=context):
274 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
275 if res[account.id] < 0:
276 res[account.id] = 0.0
278 res[id] = round(res.get(id, 0.0),2)
281 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
283 for account in self.browse(cr, uid, ids, context=context):
284 if account.hours_qtt_invoiced == 0:
287 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
289 res[id] = round(res.get(id, 0.0),2)
292 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
294 for account in self.browse(cr, uid, ids, context=context):
295 if account.ca_invoiced == 0:
297 elif account.total_cost != 0.0:
298 res[account.id] = -(account.real_margin / account.total_cost) * 100
300 res[account.id] = 0.0
302 res[id] = round(res.get(id, 0.0),2)
305 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
307 for account in self.browse(cr, uid, ids, context=context):
308 if account.amount_max != 0:
309 res[account.id] = account.amount_max - account.ca_invoiced
313 res[id] = round(res.get(id, 0.0),2)
316 def _real_margin_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.ca_invoiced + account.total_cost
321 res[id] = round(res.get(id, 0.0),2)
324 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
326 for account in self.browse(cr, uid, ids, context=context):
327 res[account.id] = account.ca_theorical + account.total_cost
329 res[id] = round(res.get(id, 0.0),2)
332 def _is_overdue_quantity(self, cr, uid, ids, fieldnames, args, context=None):
333 result = dict.fromkeys(ids, 0)
334 for record in self.browse(cr, uid, ids, context=context):
335 if record.quantity_max > 0.0:
336 result[record.id] = int(record.hours_quantity >= record.quantity_max)
338 result[record.id] = 0
341 def _get_analytic_account(self, cr, uid, ids, context=None):
343 for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
344 result.add(line.account_id.id)
347 def _get_total_estimation(self, account):
349 if account.fix_price_invoices:
350 tot_est += account.amount_max
351 if account.invoice_on_timesheets:
352 tot_est += account.hours_qtt_est
355 def _get_total_invoiced(self, account):
357 if account.fix_price_invoices:
358 total_invoiced += account.ca_invoiced
359 if account.invoice_on_timesheets:
360 total_invoiced += account.hours_qtt_invoiced
361 return total_invoiced
363 def _get_total_remaining(self, account):
364 total_remaining = 0.0
365 if account.fix_price_invoices:
366 total_remaining += account.remaining_ca
367 if account.invoice_on_timesheets:
368 total_remaining += account.remaining_hours
369 return total_remaining
371 def _get_total_toinvoice(self, account):
372 total_toinvoice = 0.0
373 if account.fix_price_invoices:
374 total_toinvoice += account.ca_to_invoice
375 if account.invoice_on_timesheets:
376 total_toinvoice += account.hours_qtt_non_invoiced
377 return total_toinvoice
379 def _sum_of_fields(self, cr, uid, ids, name, arg, context=None):
380 res = dict([(i, {}) for i in ids])
381 for account in self.browse(cr, uid, ids, context=context):
382 res[account.id]['est_total'] = self._get_total_estimation(account)
383 res[account.id]['invoiced_total'] = self._get_total_invoiced(account)
384 res[account.id]['remaining_total'] = self._get_total_remaining(account)
385 res[account.id]['toinvoice_total'] = self._get_total_toinvoice(account)
389 'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
391 'account.analytic.line' : (_get_analytic_account, None, 20),
393 'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
394 help="Total customer invoiced amount for this account.",
395 digits_compute=dp.get_precision('Account')),
396 'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
397 help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
398 digits_compute=dp.get_precision('Account')),
399 'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
400 help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
401 digits_compute=dp.get_precision('Account')),
402 'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
403 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.",
404 digits_compute=dp.get_precision('Account')),
405 'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Worked Time',
406 help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
407 'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
408 help="If invoice from the costs, this is the date of the latest invoiced."),
409 'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
410 help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
411 'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
412 help="Date of the latest work done on this account."),
413 'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
414 help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
415 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
416 help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
417 'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
418 help="Computed using the formula: Maximum Time - Total Worked Time"),
419 'remaining_hours_to_invoice': fields.function(_remaining_hours_to_invoice_calc, type='float', string='Remaining Time',
420 help="Computed using the formula: Maximum Time - Total Invoiced Time"),
421 'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
422 help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
423 digits_compute=dp.get_precision('Account')),
424 'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
425 help="Computed using the formula: Invoiced Amount / Total Time",
426 digits_compute=dp.get_precision('Account')),
427 'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
428 help="Computed using the formula: Invoiced Amount - Total Costs.",
429 digits_compute=dp.get_precision('Account')),
430 'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
431 help="Computed using the formula: Theorial Revenue - Total Costs",
432 digits_compute=dp.get_precision('Account')),
433 'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
434 help="Computes using the formula: (Real Margin / Total Costs) * 100.",
435 digits_compute=dp.get_precision('Account')),
436 'fix_price_invoices' : fields.boolean('Fix Price Invoices'),
437 'invoice_on_timesheets' : fields.boolean("Invoice On Timesheets"),
438 'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
439 'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
440 'template_id':fields.many2one('account.analytic.account', 'Template of Contract'),
441 'hours_qtt_est': fields.float('Estimation of Hours to Invoice'),
442 'est_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
443 'invoiced_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
444 'remaining_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
445 'toinvoice_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
448 def on_change_template(self, cr, uid, ids, template_id, context=None):
452 template = self.browse(cr, uid, template_id, context=context)
453 if template.date_start:
454 res['value']['date_start'] = str(template.date_start)
456 res['value']['date'] = str(template.date)
457 res['value']['fix_price_invoices'] = template.fix_price_invoices
458 res['value']['invoice_on_timesheets'] = template.invoice_on_timesheets
459 res['value']['quantity_max'] = template.quantity_max
460 res['value']['amount_max'] = template.amount_max
461 res['value']['to_invoice'] = template.to_invoice.id
462 res['value']['pricelist_id'] = template.pricelist_id.id
463 res['value']['description'] = template.description
466 account_analytic_account()
468 class account_analytic_account_summary_user(osv.osv):
469 _name = "account_analytic_analysis.summary.user"
470 _description = "Hours Summary by User"
475 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
477 account_obj = self.pool.get('account.analytic.account')
478 cr.execute('SELECT MAX(id) FROM res_users')
479 max_user = cr.fetchone()[0]
480 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
481 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
482 parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
484 cr.execute('SELECT id, unit_amount ' \
485 'FROM account_analytic_analysis_summary_user ' \
486 'WHERE account_id IN %s ' \
487 'AND "user" IN %s',(parent_ids, tuple(user_ids),))
488 for sum_id, unit_amount in cr.fetchall():
489 res[sum_id] = unit_amount
491 res[id] = round(res.get(id, 0.0), 2)
495 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
496 'unit_amount': fields.float('Total Time'),
497 'user': fields.many2one('res.users', 'User'),
501 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
502 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
504 '(u.account_id * u.max_user) + u."user" AS id, ' \
505 'u.account_id AS account_id, ' \
506 'u."user" AS "user", ' \
507 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
510 'a.id AS account_id, ' \
511 'u1.id AS "user", ' \
512 'MAX(u2.id) AS max_user ' \
514 'res_users AS u1, ' \
515 'res_users AS u2, ' \
516 'account_analytic_account AS a ' \
517 'GROUP BY u1.id, a.id ' \
521 'l.account_id AS account_id, ' \
522 'l.user_id AS "user", ' \
523 'SUM(l.unit_amount) AS unit_amount ' \
524 'FROM account_analytic_line AS l, ' \
525 'account_analytic_journal AS j ' \
526 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
527 'GROUP BY l.account_id, l.user_id ' \
530 'u.account_id = l.account_id ' \
531 'AND u."user" = l."user"' \
533 'GROUP BY u."user", u.account_id, u.max_user' \
536 account_analytic_account_summary_user()
538 class account_analytic_account_summary_month(osv.osv):
539 _name = "account_analytic_analysis.summary.month"
540 _description = "Hours summary by month"
545 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
546 'unit_amount': fields.float('Total Time'),
547 'month': fields.char('Month', size=32, readonly=True),
551 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
552 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
554 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000::bigint))::bigint AS id, ' \
555 'd.account_id AS account_id, ' \
556 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
557 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
558 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
565 'a.id AS account_id, ' \
566 'l.month AS month ' \
569 'DATE_TRUNC(\'month\', l.date) AS month ' \
570 'FROM account_analytic_line AS l, ' \
571 'account_analytic_journal AS j ' \
572 'WHERE j.type = \'general\' ' \
573 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
575 'account_analytic_account AS a ' \
576 'GROUP BY l.month, a.id ' \
578 'GROUP BY d2.account_id, d2.month ' \
582 'l.account_id AS account_id, ' \
583 'DATE_TRUNC(\'month\', l.date) AS month, ' \
584 'SUM(l.unit_amount) AS unit_amount ' \
585 'FROM account_analytic_line AS l, ' \
586 'account_analytic_journal AS j ' \
587 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
588 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
591 'd.account_id = l.account_id ' \
592 'AND d.month = l.month' \
594 'GROUP BY d.month, d.account_id ' \
598 account_analytic_account_summary_month()
599 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: