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 _expense_invoiced_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.
238 cr.execute("SELECT hel.analytic_account,SUM(hel.unit_amount*hel.unit_quantity)\
239 FROM hr_expense_line AS hel\
240 LEFT JOIN hr_expense_expense AS he \
241 ON he.id = hel.expense_id\
242 WHERE he.state = 'paid' \
243 AND hel.analytic_account IN %s \
244 GROUP BY hel.analytic_account",(child_ids,))
245 for account_id, sum in cr.fetchall():
246 res[account_id] = sum
250 def _expense_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
253 child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
260 cr.execute("SELECT hel.analytic_account, SUM(hel.unit_amount*hel.unit_quantity) \
261 FROM hr_expense_line AS hel\
262 LEFT JOIN hr_expense_expense AS he \
263 ON he.id = hel.expense_id\
264 WHERE he.state = 'invoiced' \
265 AND hel.analytic_account IN %s \
266 GROUP BY hel.analytic_account",(child_ids,))
267 for account_id, sum in cr.fetchall():
268 res[account_id] = sum
272 def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
275 child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
281 cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
282 FROM account_analytic_line \
283 JOIN account_analytic_journal \
284 ON account_analytic_line.journal_id = account_analytic_journal.id \
285 WHERE account_analytic_line.account_id IN %s \
287 GROUP BY account_analytic_line.account_id""",(child_ids,))
288 for account_id, sum in cr.fetchall():
289 res[account_id] = round(sum,2)
293 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
295 for account in self.browse(cr, uid, ids, context=context):
296 if account.quantity_max != 0:
297 res[account.id] = account.quantity_max - account.hours_qtt_invoiced
299 res[account.id] = 0.0
301 res[id] = round(res.get(id, 0.0),2)
304 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
306 for account in self.browse(cr, uid, ids, context=context):
307 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
308 if res[account.id] < 0:
309 res[account.id] = 0.0
311 res[id] = round(res.get(id, 0.0),2)
314 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
316 for account in self.browse(cr, uid, ids, context=context):
317 if account.hours_qtt_invoiced == 0:
320 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
322 res[id] = round(res.get(id, 0.0),2)
325 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
327 for account in self.browse(cr, uid, ids, context=context):
328 if account.ca_invoiced == 0:
330 elif account.total_cost != 0.0:
331 res[account.id] = -(account.real_margin / account.total_cost) * 100
333 res[account.id] = 0.0
335 res[id] = round(res.get(id, 0.0),2)
338 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
340 for account in self.browse(cr, uid, ids, context=context):
341 if account.amount_max != 0:
342 res[account.id] = account.amount_max - account.ca_invoiced
346 res[id] = round(res.get(id, 0.0),2)
349 def _remaining_expnse_calc(self, cr, uid, ids, name, arg, context=None):
351 for account in self.browse(cr, uid, ids, context=context):
352 if account.expense_max != 0:
353 res[account.id] = account.expense_max - account.expense_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, context=context):
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, context=context):
371 res[account.id] = account.ca_theorical + account.total_cost
373 res[id] = round(res.get(id, 0.0),2)
376 def _is_overdue_quantity(self, cr, uid, ids, fieldnames, args, context=None):
377 result = dict.fromkeys(ids, 0)
378 for record in self.browse(cr, uid, ids, context=context):
379 if record.quantity_max > 0.0:
380 result[record.id] = int(record.hours_quantity >= record.quantity_max)
382 result[record.id] = 0
385 def _get_analytic_account(self, cr, uid, ids, context=None):
387 for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
388 result.add(line.account_id.id)
391 def _sum_of_fields(self, cr, uid, ids, name, arg, context=None):
392 res = dict([(i, {}) for i in ids])
395 total_toinvoice = 0.0
396 total_remaining = 0.0
397 for account in self.browse(cr, uid, ids, context=context):
398 if account.fix_price_invoices:
399 total_max += account.amount_max
400 total_invoiced += account.ca_invoiced
401 total_remaining += account.remaining_ca
402 total_toinvoice += account.ca_to_invoice
403 if account.invoice_on_timesheets:
404 total_max += account.quantity_max
405 total_invoiced += account.hours_qtt_invoiced
406 total_remaining += account.remaining_hours
407 total_toinvoice += account.hours_qtt_non_invoiced
408 if account.charge_expenses:
409 total_max += account.expense_max
410 total_invoiced += account.expense_invoiced
411 total_remaining += account.remaining_expense
412 total_toinvoice += account.expense_to_invoice
413 res[account.id]['est_total'] = total_max or 0.0
414 res[account.id]['invoiced_total'] = total_invoiced or 0.0
415 res[account.id]['remaining_total'] = total_remaining or 0.0
416 res[account.id]['toinvoice_total'] = total_toinvoice or 0.0
420 'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
422 'account.analytic.line' : (_get_analytic_account, None, 20),
424 'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
425 help="Total customer invoiced amount for this account.",
426 digits_compute=dp.get_precision('Account')),
427 'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
428 help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
429 digits_compute=dp.get_precision('Account')),
430 'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
431 help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
432 digits_compute=dp.get_precision('Account')),
433 'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
434 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.",
435 digits_compute=dp.get_precision('Account')),
436 'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Time',
437 help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
438 'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
439 help="If invoice from the costs, this is the date of the latest invoiced."),
440 'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
441 help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
442 'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
443 help="Date of the latest work done on this account."),
444 'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
445 help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
446 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
447 help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
448 'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
449 help="Computed using the formula: Maximum Time - Total Time"),
450 'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
451 help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
452 digits_compute=dp.get_precision('Account')),
453 'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
454 help="Computed using the formula: Invoiced Amount / Total Time",
455 digits_compute=dp.get_precision('Account')),
456 'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
457 help="Computed using the formula: Invoiced Amount - Total Costs.",
458 digits_compute=dp.get_precision('Account')),
459 'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
460 help="Computed using the formula: Theorial Revenue - Total Costs",
461 digits_compute=dp.get_precision('Account')),
462 'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
463 help="Computes using the formula: (Real Margin / Total Costs) * 100.",
464 digits_compute=dp.get_precision('Account')),
465 'fix_price_invoices' : fields.boolean('Fix Price Invoices'),
466 'invoice_on_timesheets' : fields.boolean("Invoice On Timesheets"),
467 'charge_expenses' : fields.boolean('Charge Expenses'),
468 'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
469 'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
470 'template_id':fields.many2one('account.analytic.account', 'Template of Contract'),
471 'expense_invoiced' : fields.function(_expense_invoiced_calc, type="float"),
472 'expense_to_invoice' : fields.function(_expense_to_invoice_calc, type='float'),
473 'remaining_expense' : fields.function(_remaining_expnse_calc, type="float"),
474 #'fix_exp_max' : fields.float('Max. amt'),
475 #'timesheet_max': fields.float('max_timesheet'),
476 'expense_max': fields.float('expenses'),
477 'est_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
478 'invoiced_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
479 'remaining_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
480 'toinvoice_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
482 def on_change_template(self, cr, uid, id, template_id):
486 template = self.browse(cr, uid, template_id)
487 if template.date_start:
488 res['value']['date_start'] = str(template.date_start)
490 res['value']['date'] = str(template.date)
491 res['value']['quantity_max'] = template.quantity_max
492 res['value']['remaining_hours'] = template.remaining_hours
493 res['value']['amount_max'] = template.amount_max
494 res['value']['expense_max'] = template.expense_max
495 res['value']['to_invoice'] = template.to_invoice.id
496 res['value']['pricelist_id'] = template.pricelist_id.id
497 res['value']['description'] = template.description
500 def open_hr_expense(self, cr, uid, ids, context=None):
501 account = self.browse(cr, uid, ids[0], context)
502 data_obj = self.pool.get('ir.model.data')
504 journal_id = data_obj.get_object(cr, uid, 'hr_timesheet', 'analytic_journal').id
507 line_ids = self.pool.get('hr.expense.line').search(cr,uid,[('analytic_account','=',account.id)])
508 id2 = data_obj._get_id(cr, uid, 'hr_expense', 'view_expenses_form')
509 id3 = data_obj._get_id(cr, uid, 'hr_expense', 'view_expenses_tree')
511 id2 = data_obj.browse(cr, uid, id2, context=context).res_id
513 id3 = data_obj.browse(cr, uid, id3, context=context).res_id
514 domain = [('line_ids','in',line_ids)]
516 'type': 'ir.actions.act_window',
517 'name': _('Expenses'),
519 'view_mode': 'tree,form',
520 'views': [(id3,'tree'),(id2,'form')],
522 'res_model': 'hr.expense.expense',
526 def hr_to_invoiced_expense(self, cr, uid, ids, context=None):
527 res = self.open_hr_expense(cr,uid,ids,context)
528 account = self.browse(cr, uid, ids[0], context)
529 line_ids = self.pool.get('hr.expense.line').search(cr,uid,[('analytic_account','=',account.id)])
530 res['domain'] = [('line_ids','in',line_ids),('state','=','invoiced')]
533 account_analytic_account()
535 class account_analytic_account_summary_user(osv.osv):
536 _name = "account_analytic_analysis.summary.user"
537 _description = "Hours Summary by User"
542 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
544 account_obj = self.pool.get('account.analytic.account')
545 cr.execute('SELECT MAX(id) FROM res_users')
546 max_user = cr.fetchone()[0]
547 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
548 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
549 parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
551 cr.execute('SELECT id, unit_amount ' \
552 'FROM account_analytic_analysis_summary_user ' \
553 'WHERE account_id IN %s ' \
554 'AND "user" IN %s',(parent_ids, tuple(user_ids),))
555 for sum_id, unit_amount in cr.fetchall():
556 res[sum_id] = unit_amount
558 res[id] = round(res.get(id, 0.0), 2)
562 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
563 'unit_amount': fields.float('Total Time'),
564 'user': fields.many2one('res.users', 'User'),
568 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
569 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
571 '(u.account_id * u.max_user) + u."user" AS id, ' \
572 'u.account_id AS account_id, ' \
573 'u."user" AS "user", ' \
574 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
577 'a.id AS account_id, ' \
578 'u1.id AS "user", ' \
579 'MAX(u2.id) AS max_user ' \
581 'res_users AS u1, ' \
582 'res_users AS u2, ' \
583 'account_analytic_account AS a ' \
584 'GROUP BY u1.id, a.id ' \
588 'l.account_id AS account_id, ' \
589 'l.user_id AS "user", ' \
590 'SUM(l.unit_amount) AS unit_amount ' \
591 'FROM account_analytic_line AS l, ' \
592 'account_analytic_journal AS j ' \
593 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
594 'GROUP BY l.account_id, l.user_id ' \
597 'u.account_id = l.account_id ' \
598 'AND u."user" = l."user"' \
600 'GROUP BY u."user", u.account_id, u.max_user' \
603 account_analytic_account_summary_user()
605 class account_analytic_account_summary_month(osv.osv):
606 _name = "account_analytic_analysis.summary.month"
607 _description = "Hours summary by month"
612 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
613 'unit_amount': fields.float('Total Time'),
614 'month': fields.char('Month', size=32, readonly=True),
618 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
619 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
621 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000::bigint))::bigint AS id, ' \
622 'd.account_id AS account_id, ' \
623 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
624 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
625 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
632 'a.id AS account_id, ' \
633 'l.month AS month ' \
636 'DATE_TRUNC(\'month\', l.date) AS month ' \
637 'FROM account_analytic_line AS l, ' \
638 'account_analytic_journal AS j ' \
639 'WHERE j.type = \'general\' ' \
640 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
642 'account_analytic_account AS a ' \
643 'GROUP BY l.month, a.id ' \
645 'GROUP BY d2.account_id, d2.month ' \
649 'l.account_id AS account_id, ' \
650 'DATE_TRUNC(\'month\', l.date) AS month, ' \
651 'SUM(l.unit_amount) AS unit_amount ' \
652 'FROM account_analytic_line AS l, ' \
653 'account_analytic_journal AS j ' \
654 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
655 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
658 'd.account_id = l.account_id ' \
659 'AND d.month = l.month' \
661 'GROUP BY d.month, d.account_id ' \
665 account_analytic_account_summary_month()
666 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: