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 _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
262 for account in self.browse(cr, uid, ids, context=context):
263 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
264 if res[account.id] < 0:
265 res[account.id] = 0.0
267 res[id] = round(res.get(id, 0.0),2)
270 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
272 for account in self.browse(cr, uid, ids, context=context):
273 if account.hours_qtt_invoiced == 0:
276 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
278 res[id] = round(res.get(id, 0.0),2)
281 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
283 for account in self.browse(cr, uid, ids, context=context):
284 if account.ca_invoiced == 0:
286 elif account.total_cost != 0.0:
287 res[account.id] = -(account.real_margin / account.total_cost) * 100
289 res[account.id] = 0.0
291 res[id] = round(res.get(id, 0.0),2)
294 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
296 for account in self.browse(cr, uid, ids, context=context):
297 if account.amount_max != 0:
298 res[account.id] = account.amount_max - account.ca_invoiced
302 res[id] = round(res.get(id, 0.0),2)
305 def _real_margin_calc(self, cr, uid, ids, name, arg, context=None):
307 for account in self.browse(cr, uid, ids, context=context):
308 res[account.id] = account.ca_invoiced + account.total_cost
310 res[id] = round(res.get(id, 0.0),2)
313 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
315 for account in self.browse(cr, uid, ids, context=context):
316 res[account.id] = account.ca_theorical + account.total_cost
318 res[id] = round(res.get(id, 0.0),2)
321 def _is_overdue_quantity(self, cr, uid, ids, fieldnames, args, context=None):
322 result = dict.fromkeys(ids, 0)
323 for record in self.browse(cr, uid, ids, context=context):
324 if record.quantity_max > 0.0:
325 result[record.id] = int(record.hours_quantity >= record.quantity_max)
327 result[record.id] = 0
330 def _get_analytic_account(self, cr, uid, ids, context=None):
332 for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
333 result.add(line.account_id.id)
337 'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
339 'account.analytic.line' : (_get_analytic_account, None, 20),
341 'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
342 help="Total customer invoiced amount for this account.",
343 digits_compute=dp.get_precision('Account')),
344 'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
345 help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
346 digits_compute=dp.get_precision('Account')),
347 'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
348 help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
349 digits_compute=dp.get_precision('Account')),
350 'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
351 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.",
352 digits_compute=dp.get_precision('Account')),
353 'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Time',
354 help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
355 'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
356 help="If invoice from the costs, this is the date of the latest invoiced."),
357 'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
358 help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
359 'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
360 help="Date of the latest work done on this account."),
361 'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
362 help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
363 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
364 help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
365 'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
366 help="Computed using the formula: Maximum Time - Total Time"),
367 'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
368 help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
369 digits_compute=dp.get_precision('Account')),
370 'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
371 help="Computed using the formula: Invoiced Amount / Total Time",
372 digits_compute=dp.get_precision('Account')),
373 'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
374 help="Computed using the formula: Invoiced Amount - Total Costs.",
375 digits_compute=dp.get_precision('Account')),
376 'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
377 help="Computed using the formula: Theorial Revenue - Total Costs",
378 digits_compute=dp.get_precision('Account')),
379 'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
380 help="Computes using the formula: (Real Margin / Total Costs) * 100.",
381 digits_compute=dp.get_precision('Account')),
382 'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
383 'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
386 account_analytic_account()
388 class account_analytic_account_summary_user(osv.osv):
389 _name = "account_analytic_analysis.summary.user"
390 _description = "Hours Summary by User"
395 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
397 account_obj = self.pool.get('account.analytic.account')
398 cr.execute('SELECT MAX(id) FROM res_users')
399 max_user = cr.fetchone()[0]
400 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
401 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
402 parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
404 cr.execute('SELECT id, unit_amount ' \
405 'FROM account_analytic_analysis_summary_user ' \
406 'WHERE account_id IN %s ' \
407 'AND "user" IN %s',(parent_ids, tuple(user_ids),))
408 for sum_id, unit_amount in cr.fetchall():
409 res[sum_id] = unit_amount
411 res[id] = round(res.get(id, 0.0), 2)
415 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
416 'unit_amount': fields.float('Total Time'),
417 'user': fields.many2one('res.users', 'User'),
421 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
422 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
424 '(u.account_id * u.max_user) + u."user" AS id, ' \
425 'u.account_id AS account_id, ' \
426 'u."user" AS "user", ' \
427 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
430 'a.id AS account_id, ' \
431 'u1.id AS "user", ' \
432 'MAX(u2.id) AS max_user ' \
434 'res_users AS u1, ' \
435 'res_users AS u2, ' \
436 'account_analytic_account AS a ' \
437 'GROUP BY u1.id, a.id ' \
441 'l.account_id AS account_id, ' \
442 'l.user_id AS "user", ' \
443 'SUM(l.unit_amount) AS unit_amount ' \
444 'FROM account_analytic_line AS l, ' \
445 'account_analytic_journal AS j ' \
446 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
447 'GROUP BY l.account_id, l.user_id ' \
450 'u.account_id = l.account_id ' \
451 'AND u."user" = l."user"' \
453 'GROUP BY u."user", u.account_id, u.max_user' \
456 account_analytic_account_summary_user()
458 class account_analytic_account_summary_month(osv.osv):
459 _name = "account_analytic_analysis.summary.month"
460 _description = "Hours summary by month"
465 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
466 'unit_amount': fields.float('Total Time'),
467 'month': fields.char('Month', size=32, readonly=True),
471 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
472 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
474 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000::bigint))::bigint AS id, ' \
475 'd.account_id AS account_id, ' \
476 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
477 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
478 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
485 'a.id AS account_id, ' \
486 'l.month AS month ' \
489 'DATE_TRUNC(\'month\', l.date) AS month ' \
490 'FROM account_analytic_line AS l, ' \
491 'account_analytic_journal AS j ' \
492 'WHERE j.type = \'general\' ' \
493 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
495 'account_analytic_account AS a ' \
496 'GROUP BY l.month, a.id ' \
498 'GROUP BY d2.account_id, d2.month ' \
502 'l.account_id AS account_id, ' \
503 'DATE_TRUNC(\'month\', l.date) AS month, ' \
504 'SUM(l.unit_amount) AS unit_amount ' \
505 'FROM account_analytic_line AS l, ' \
506 'account_analytic_journal AS j ' \
507 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
508 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
511 'd.account_id = l.account_id ' \
512 'AND d.month = l.month' \
514 'GROUP BY d.month, d.account_id ' \
518 account_analytic_account_summary_month()
519 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: