1 # -*- encoding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2008 Tiny SPRL (<http://tiny.be>). All Rights Reserved
8 # This program is free software: you can redistribute it and/or modify
9 # it under the terms of the GNU General Public License as published by
10 # the Free Software Foundation, either version 3 of the License, or
11 # (at your option) any later version.
13 # This program is distributed in the hope that it will be useful,
14 # but WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with this program. If not, see <http://www.gnu.org/licenses/>.
21 ##############################################################################
23 from osv import osv, fields
24 from osv.orm import intersect
25 from tools.translate import _
28 class account_analytic_account(osv.osv):
29 _name = "account.analytic.account"
30 _inherit = "account.analytic.account"
32 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
34 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
36 acc_set = ",".join(map(str, ids2))
37 cr.execute("select account_analytic_line.account_id, sum(amount) \
38 from account_analytic_line \
39 join account_analytic_journal \
40 on account_analytic_line.journal_id = account_analytic_journal.id \
41 where account_analytic_line.account_id IN (%s) \
42 and account_analytic_journal.type = 'sale' \
43 group by account_analytic_line.account_id" % acc_set)
44 for account_id, sum in cr.fetchall():
45 res[account_id] = round(sum,2)
47 res.setdefault(obj_id, 0.0)
48 for child_id in self.search(cr, uid,
49 [('parent_id', 'child_of', [obj_id])]):
50 if child_id != obj_id:
51 res[obj_id] += res.get(child_id, 0.0)
53 res[id] = round(res.get(id, 0.0),2)
56 def _ca_to_invoice_calc(self, cr, uid, ids, name, arg, context={}):
59 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
61 # Amount uninvoiced hours to invoice at sale price
62 acc_set = ",".join(map(str, ids2))
63 cr.execute("""SELECT account_analytic_account.id, \
64 sum (product_template.list_price * \
65 account_analytic_line.unit_amount * \
66 ((100-hr_timesheet_invoice_factor.factor)/100)) \
68 FROM product_template \
69 join product_product \
70 on product_template.id = product_product.product_tmpl_id \
71 JOIN account_analytic_line \
72 on account_analytic_line.product_id = product_product.id \
73 JOIN account_analytic_journal \
74 on account_analytic_line.journal_id = account_analytic_journal.id \
75 JOIN account_analytic_account \
76 on account_analytic_account.id = account_analytic_line.account_id \
77 JOIN hr_timesheet_invoice_factor \
78 on hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
79 WHERE account_analytic_account.id IN (%s) \
80 AND account_analytic_line.invoice_id is null \
81 AND account_analytic_line.to_invoice IS NOT NULL \
82 and account_analytic_journal.type in ('purchase','general') \
83 GROUP BY account_analytic_account.id;"""%acc_set)
84 for account_id, sum in cr.fetchall():
85 res[account_id] = round(sum,2)
87 # Expense amount and purchase invoice
88 #acc_set = ",".join(map(str, ids2))
89 #cr.execute ("select account_analytic_line.account_id, sum(amount) \
90 # from account_analytic_line \
91 # join account_analytic_journal \
92 # on account_analytic_line.journal_id = account_analytic_journal.id \
93 # where account_analytic_line.account_id IN (%s) \
94 # and account_analytic_journal.type = 'purchase' \
95 # GROUP BY account_analytic_line.account_id;"%acc_set)
96 #for account_id, sum in cr.fetchall():
97 # res2[account_id] = round(sum,2)
99 res.setdefault(obj_id, 0.0)
100 res2.setdefault(obj_id, 0.0)
101 for child_id in self.search(cr, uid,
102 [('parent_id', 'child_of', [obj_id])]):
103 if child_id != obj_id:
104 res[obj_id] += res.get(child_id, 0.0)
105 res2[obj_id] += res2.get(child_id, 0.0)
106 # sum both result on account_id
108 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
111 def _hours_qtt_non_invoiced_calc (self, cr, uid, ids, name, arg, context={}):
113 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
115 acc_set = ",".join(map(str, ids2))
116 cr.execute("select account_analytic_line.account_id, sum(unit_amount) \
117 from account_analytic_line \
118 join account_analytic_journal \
119 on account_analytic_line.journal_id = account_analytic_journal.id \
120 where account_analytic_line.account_id IN (%s) \
121 and account_analytic_journal.type='general' \
122 and invoice_id is null \
123 AND to_invoice IS NOT NULL \
124 GROUP BY account_analytic_line.account_id;"%acc_set)
125 for account_id, sum in cr.fetchall():
126 res[account_id] = round(sum,2)
128 res.setdefault(obj_id, 0.0)
129 for child_id in self.search(cr, uid,
130 [('parent_id', 'child_of', [obj_id])]):
131 if child_id != obj_id:
132 res[obj_id] += res.get(child_id, 0.0)
134 res[id] = round(res.get(id, 0.0),2)
137 def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
139 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
141 acc_set = ",".join(map(str, ids2))
142 cr.execute("select account_analytic_line.account_id,sum(unit_amount) \
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 GROUP BY account_analytic_line.account_id"%acc_set)
149 for account_id, sum in cr.fetchall():
150 res[account_id] = round(sum,2)
152 res.setdefault(obj_id, 0.0)
153 for child_id in self.search(cr, uid,
154 [('parent_id', 'child_of', [obj_id])]):
155 if child_id != obj_id:
156 res[obj_id] += res.get(child_id, 0.0)
158 res[id] = round(res.get(id, 0.0),2)
161 def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
163 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
165 acc_set = ",".join(map(str, ids2))
166 cr.execute("""select account_analytic_line.account_id,sum(amount) \
167 from account_analytic_line \
168 join account_analytic_journal \
169 on account_analytic_line.journal_id = account_analytic_journal.id \
170 where account_analytic_line.account_id IN (%s) \
172 GROUP BY account_analytic_line.account_id"""%acc_set)
173 for account_id, sum in cr.fetchall():
174 res[account_id] = round(sum,2)
176 res.setdefault(obj_id, 0.0)
177 for child_id in self.search(cr, uid,
178 [('parent_id', 'child_of', [obj_id])]):
179 if child_id != obj_id:
180 res[obj_id] += res.get(child_id, 0.0)
182 res[id] = round(res.get(id, 0.0),2)
185 def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
188 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
190 acc_set = ",".join(map(str, ids2))
191 cr.execute("""select account_analytic_line.account_id as account_id, \
192 sum((account_analytic_line.unit_amount * pt.list_price) \
193 - (account_analytic_line.unit_amount * pt.list_price \
194 * hr.factor)) as somme
195 from account_analytic_line \
196 left join account_analytic_journal \
197 on (account_analytic_line.journal_id = account_analytic_journal.id) \
198 join product_product pp \
199 on (account_analytic_line.product_id = pp.id) \
200 join product_template pt \
201 on (pp.product_tmpl_id = pt.id) \
202 join account_analytic_account a \
203 on (a.id=account_analytic_line.account_id) \
204 join hr_timesheet_invoice_factor hr \
205 on (hr.id=a.to_invoice) \
206 where account_analytic_line.account_id IN (%s) \
207 and a.to_invoice IS NOT NULL \
208 and account_analytic_journal.type in ('purchase','general')
209 GROUP BY account_analytic_line.account_id"""%acc_set)
210 for account_id, sum in cr.fetchall():
211 res2[account_id] = round(sum,2)
214 res.setdefault(obj_id, 0.0)
215 res2.setdefault(obj_id, 0.0)
216 for child_id in self.search(cr, uid,
217 [('parent_id', 'child_of', [obj_id])]):
218 if child_id != obj_id:
219 res[obj_id] += res.get(child_id, 0.0)
220 res[obj_id] += res2.get(child_id, 0.0)
222 # sum both result on account_id
224 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
227 def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
229 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
231 acc_set = ",".join(map(str, ids2))
232 cr.execute("select account_analytic_line.account_id, max(date) \
233 from account_analytic_line \
234 where account_id IN (%s) \
235 and invoice_id is null \
236 GROUP BY account_analytic_line.account_id" % acc_set)
237 for account_id, sum in cr.fetchall():
238 res[account_id] = sum
240 res.setdefault(obj_id, '')
241 for child_id in self.search(cr, uid,
242 [('parent_id', 'child_of', [obj_id])]):
243 if res[obj_id] < res.get(child_id, ''):
244 res[obj_id] = res.get(child_id, '')
246 res[id] = res.get(id, '')
249 def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
251 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
253 acc_set = ",".join(map(str, ids2))
254 cr.execute ("select account_analytic_line.account_id, \
255 date(max(account_invoice.date_invoice)) \
256 from account_analytic_line \
257 join account_invoice \
258 on account_analytic_line.invoice_id = account_invoice.id \
259 where account_analytic_line.account_id IN (%s) \
260 and account_analytic_line.invoice_id is not null \
261 GROUP BY account_analytic_line.account_id"%acc_set)
262 for account_id, sum in cr.fetchall():
263 res[account_id] = sum
265 res.setdefault(obj_id, '')
266 for child_id in self.search(cr, uid,
267 [('parent_id', 'child_of', [obj_id])]):
268 if res[obj_id] < res.get(child_id, ''):
269 res[obj_id] = res.get(child_id, '')
271 res[id] = res.get(id, '')
274 def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
276 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
278 acc_set = ",".join(map(str, ids2))
279 cr.execute("select account_analytic_line.account_id, max(date) \
280 from account_analytic_line \
281 where account_id IN (%s) \
282 and invoice_id is not null \
283 GROUP BY account_analytic_line.account_id;"%acc_set)
284 for account_id, sum in cr.fetchall():
285 res[account_id] = sum
287 res.setdefault(obj_id, '')
288 for child_id in self.search(cr, uid,
289 [('parent_id', 'child_of', [obj_id])]):
290 if res[obj_id] < res.get(child_id, ''):
291 res[obj_id] = res.get(child_id, '')
293 res[id] = res.get(id, '')
296 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
298 for account in self.browse(cr, uid, ids):
299 if account.quantity_max <> 0:
300 res[account.id] = account.quantity_max - account.hours_quantity
304 res[id] = round(res.get(id, 0.0),2)
307 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
309 for account in self.browse(cr, uid, ids):
310 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
311 if res[account.id] < 0:
314 res[id] = round(res.get(id, 0.0),2)
317 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
319 for account in self.browse(cr, uid, ids):
320 if account.hours_qtt_invoiced == 0:
323 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
325 res[id] = round(res.get(id, 0.0),2)
328 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
330 for account in self.browse(cr, uid, ids):
331 if account.ca_invoiced == 0:
333 elif account.total_cost <> 0.0:
334 res[account.id] = -(account.real_margin / account.total_cost) * 100
336 res[account.id] = 0.0
338 res[id] = round(res.get(id, 0.0),2)
341 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
343 for account in self.browse(cr, uid, ids):
344 if account.amount_max <> 0:
345 res[account.id] = account.amount_max - account.ca_invoiced
349 res[id] = round(res.get(id, 0.0),2)
352 def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
354 for account in self.browse(cr, uid, ids):
355 res[account.id] = account.ca_invoiced + account.total_cost
357 res[id] = round(res.get(id, 0.0),2)
360 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context={}):
362 for account in self.browse(cr, uid, ids):
363 res[account.id] = account.ca_theorical + account.total_cost
365 res[id] = round(res.get(id, 0.0),2)
368 def _month(self, cr, uid, ids, name, arg, context=None):
371 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
372 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
373 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
374 'AND unit_amount <> 0.0')
375 res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
378 def _user(self, cr, uid, ids, name, arg, context=None):
380 cr.execute('SELECT MAX(id) FROM res_users')
381 max_user = cr.fetchone()[0]
383 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
384 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
385 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
386 'AND unit_amount <> 0.0')
387 res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
391 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced Amount', help="Total customer invoiced amount for this account."),
392 'total_cost': fields.function(_total_cost_calc, method=True, type='float', string='Total Costs', help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets."),
393 'ca_to_invoice': fields.function(_ca_to_invoice_calc, method=True, type='float', string='Uninvoiced Amount', help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs."),
394 'ca_theorical': fields.function(_ca_theorical_calc, method=True, type='float', string='Theorical Revenue', 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."),
395 'hours_quantity': fields.function(_hours_quantity_calc, method=True, type='float', string='Hours Tot', help="Number of hours you spent on the analytic account (from timesheet). It computes on all journal of type 'general'."),
396 'last_invoice_date': fields.function(_last_invoice_date_calc, method=True, type='date', string='Last Invoice Date', help="Date of the last invoice created for this analytic account."),
397 'last_worked_invoiced_date': fields.function(_last_worked_invoiced_date_calc, method=True, type='date', string='Date of Last Invoiced Cost', help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
398 'last_worked_date': fields.function(_last_worked_date_calc, method=True, type='date', string='Date of Last Cost/Work', help="Date of the latest work done on this account."),
399 'hours_qtt_non_invoiced': fields.function(_hours_qtt_non_invoiced_calc, method=True, type='float', string='Uninvoiced Hours', 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, method=True, type='float', string='Invoiced Hours', help="Number of hours that can be invoiced plus those that already have been invoiced."),
401 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining Hours', help="Computed using the formula: Maximum Quantity - Hours Tot."),
402 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining Revenue', help="Computed using the formula: Max Invoice Price - Invoiced Amount."),
403 'revenue_per_hour': fields.function(_revenue_per_hour_calc, method=True, type='float', string='Revenue per Hours (real)', help="Computed using the formula: Invoiced Amount / Hours Tot."),
404 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real Margin', help="Computed using the formula: Invoiced Amount - Total Costs."),
405 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical Margin', help="Computed using the formula: Theorial Revenue - Total Costs"),
406 'real_margin_rate': fields.function(_real_margin_rate_calc, method=True, type='float', string='Real Margin Rate (%)', help="Computes using the formula: (Real Margin / Total Costs) * 100."),
407 'month_ids': fields.function(_month, method=True, type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
408 'user_ids': fields.function(_user, method=True, type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
410 account_analytic_account()
412 class account_analytic_account_summary_user(osv.osv):
413 _name = "account_analytic_analysis.summary.user"
414 _description = "Hours summary by user"
419 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
421 account_obj = self.pool.get('account.analytic.account')
422 cr.execute('SELECT MAX(id) FROM res_users')
423 max_user = cr.fetchone()[0]
424 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
425 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
426 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
427 user_set = ','.join([str(x) for x in user_ids])
429 acc_set = ','.join([str(x) for x in account_ids2])
430 cr.execute('SELECT id, unit_amount ' \
431 'FROM account_analytic_analysis_summary_user ' \
432 'WHERE account_id in (%s) ' \
433 'AND "user" in (%s) ' % (acc_set, user_set))
434 for sum_id, unit_amount in cr.fetchall():
435 res[sum_id] = unit_amount
437 res.setdefault(obj_id, 0.0)
438 for child_id in account_obj.search(cr, uid,
439 [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
440 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
441 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)
443 res[id] = round(res.get(id, 0.0), 2)
447 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
448 'unit_amount': fields.function(_unit_amount, method=True, type='float',
449 string='Total Time'),
450 'user' : fields.many2one('res.users', 'User'),
453 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
455 '(u.account_id * u.max_user) + u."user" AS id, ' \
456 'u.account_id AS account_id, ' \
457 'u."user" AS "user", ' \
458 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
461 'a.id AS account_id, ' \
462 'u1.id AS "user", ' \
463 'MAX(u2.id) AS max_user ' \
465 'res_users AS u1, ' \
466 'res_users AS u2, ' \
467 'account_analytic_account AS a ' \
468 'GROUP BY u1.id, a.id ' \
472 'l.account_id AS account_id, ' \
473 'l.user_id AS "user", ' \
474 'SUM(l.unit_amount) AS unit_amount ' \
475 'FROM account_analytic_line AS l, ' \
476 'account_analytic_journal AS j ' \
477 'WHERE j.type = \'general\' ' \
478 'GROUP BY l.account_id, l.user_id ' \
481 'u.account_id = l.account_id ' \
482 'AND u."user" = l."user"' \
484 'GROUP BY u."user", u.account_id, u.max_user' \
487 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
494 fields = self._columns.keys()
496 # construct a clause for the rules :
497 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
499 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
500 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
503 cr.execute('SELECT MAX(id) FROM res_users')
504 max_user = cr.fetchone()[0]
506 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
507 for i in range(0, len(ids), cr.IN_MAX):
508 sub_ids = ids[i:i+cr.IN_MAX]
510 cr.execute('select %s from \"%s\" where id in (%s) ' \
511 'and account_id in (%s) ' \
512 'and "user" in (%s) and %s order by %s' % \
513 (','.join(fields_pre2 + ['id']), self._table,
514 ','.join([str(x) for x in sub_ids]),
515 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
516 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
518 if not cr.rowcount == len({}.fromkeys(sub_ids)):
519 raise except_orm(_('AccessError'),
520 _('You try to bypass an access rule (Document type: %s).') % self._description)
522 cr.execute('select %s from \"%s\" where id in (%s) ' \
523 'and account_id in (%s) ' \
524 'and "user" in (%s) order by %s' % \
525 (','.join(fields_pre2 + ['id']), self._table,
526 ','.join([str(x) for x in sub_ids]),
527 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
528 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
530 res.extend(cr.dictfetchall())
532 res = map(lambda x: {'id': x}, ids)
535 if self._columns[f].translate:
536 ids = map(lambda x: x['id'], res)
537 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
539 r[f] = res_trans.get(r['id'], False) or r[f]
541 for table in self._inherits:
542 col = self._inherits[table]
543 cols = intersect(self._inherit_fields.keys(), fields)
546 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
554 record.update(res3[record[col]])
555 if col not in fields:
558 # all fields which need to be post-processed by a simple function (symbol_get)
559 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
561 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
562 # to get the _symbol_get in each occurence
564 for f in fields_post:
565 r[f] = self.columns[f]._symbol_get(r[f])
566 ids = map(lambda x: x['id'], res)
568 # all non inherited fields for which the attribute whose name is in load is False
569 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
570 for f in fields_post:
571 # get the value of that field for all records/ids
572 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
574 record[f] = res2[record['id']]
578 account_analytic_account_summary_user()
580 class account_analytic_account_summary_month(osv.osv):
581 _name = "account_analytic_analysis.summary.month"
582 _description = "Hours summary by month"
587 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
589 account_obj = self.pool.get('account.analytic.account')
590 account_ids = [int(str(int(x))[:-6]) for x in ids]
591 month_ids = [int(str(int(x))[-6:]) for x in ids]
592 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
593 month_set = ','.join([str(x) for x in month_ids])
595 acc_set = ','.join([str(x) for x in account_ids2])
596 cr.execute('SELECT id, unit_amount ' \
597 'FROM account_analytic_analysis_summary_month ' \
598 'WHERE account_id in (%s) ' \
599 'AND month_id in (%s) ' % \
600 (acc_set, month_set))
601 for sum_id, unit_amount in cr.fetchall():
602 res[sum_id] = unit_amount
604 res.setdefault(obj_id, 0.0)
605 for child_id in account_obj.search(cr, uid,
606 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
607 if child_id != int(str(int(obj_id))[:-6]):
608 res[obj_id] += res.get(int(child_id * 1000000 + int(obj_id)), 0.0)
610 res[id] = round(res.get(id, 0.0), 2)
614 'account_id': fields.many2one('account.analytic.account', 'Analytic Account',
616 'unit_amount': fields.function(_unit_amount, method=True, type='float',
617 string='Total Time'),
618 'month': fields.char('Month', size=25, readonly=True),
621 cr.execute('DROP VIEW IF EXISTS account_analytic_analysis_summary_month')
622 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
624 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
625 'd.account_id AS account_id, ' \
626 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
627 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
628 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
635 'a.id AS account_id, ' \
636 'l.month AS month ' \
639 'DATE_TRUNC(\'month\', l.date) AS month ' \
640 'FROM account_analytic_line AS l, ' \
641 'account_analytic_journal AS j ' \
642 'WHERE j.type = \'general\' ' \
643 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
645 'account_analytic_account AS a ' \
646 'GROUP BY l.month, a.id ' \
648 'GROUP BY d2.account_id, d2.month ' \
652 'l.account_id AS account_id, ' \
653 'DATE_TRUNC(\'month\', l.date) AS month, ' \
654 'SUM(l.unit_amount) AS unit_amount ' \
655 'FROM account_analytic_line AS l, ' \
656 'account_analytic_journal AS j ' \
657 'WHERE j.type = \'general\' ' \
658 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
661 'd.account_id = l.account_id ' \
662 'AND d.month = l.month' \
664 'GROUP BY d.month, d.account_id ' \
667 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
674 fields = self._columns.keys()
676 # construct a clause for the rules :
677 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
679 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
680 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
684 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
685 for i in range(0, len(ids), cr.IN_MAX):
686 sub_ids = ids[i:i+cr.IN_MAX]
688 cr.execute('select %s from \"%s\" where id in (%s) ' \
689 'and account_id in (%s) ' \
690 'and month_id in (%s) and %s order by %s' % \
691 (','.join(fields_pre2 + ['id']), self._table,
692 ','.join([str(x) for x in sub_ids]),
693 ','.join([str(x)[:-6] for x in sub_ids]),
694 ','.join([str(x)[-6:] for x in sub_ids]), d1,
696 if not cr.rowcount == len({}.fromkeys(sub_ids)):
697 raise except_orm(_('AccessError'),
698 _('You try to bypass an access rule (Document type: %s).') % self._description)
700 cr.execute('select %s from \"%s\" where id in (%s) ' \
701 'and account_id in (%s) ' \
702 'and month_id in (%s) order by %s' % \
703 (','.join(fields_pre2 + ['id']), self._table,
704 ','.join([str(x) for x in sub_ids]),
705 ','.join([str(x)[:-6] for x in sub_ids]),
706 ','.join([str(x)[-6:] for x in sub_ids]),
708 res.extend(cr.dictfetchall())
710 res = map(lambda x: {'id': x}, ids)
713 if self._columns[f].translate:
714 ids = map(lambda x: x['id'], res)
715 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
717 r[f] = res_trans.get(r['id'], False) or r[f]
719 for table in self._inherits:
720 col = self._inherits[table]
721 cols = intersect(self._inherit_fields.keys(), fields)
724 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
732 record.update(res3[record[col]])
733 if col not in fields:
736 # all fields which need to be post-processed by a simple function (symbol_get)
737 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
739 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
740 # to get the _symbol_get in each occurence
742 for f in fields_post:
743 r[f] = self.columns[f]._symbol_get(r[f])
744 ids = map(lambda x: x['id'], res)
746 # all non inherited fields for which the attribute whose name is in load is False
747 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
748 for f in fields_post:
749 # get the value of that field for all records/ids
750 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
752 record[f] = res2[record['id']]
756 account_analytic_account_summary_month()
759 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: