1 # -*- encoding: utf-8 -*-
2 # -*- coding: utf-8 -*-
3 ##############################################################################
5 # Copyright (c) 2004-2008 TINY SPRL. (http://tiny.be) All Rights Reserved.
9 # WARNING: This program as such is intended to be used by professional
10 # programmers who take the whole responsability of assessing all potential
11 # consequences resulting from its eventual inadequacies and bugs
12 # End users who are looking for a ready-to-use solution with commercial
13 # garantees and support are strongly adviced to contract a Free Software
16 # This program is Free Software; you can redistribute it and/or
17 # modify it under the terms of the GNU General Public License
18 # as published by the Free Software Foundation; either version 2
19 # of the License, or (at your option) any later version.
21 # This program is distributed in the hope that it will be useful,
22 # but WITHOUT ANY WARRANTY; without even the implied warranty of
23 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
30 ##############################################################################
32 from osv import osv, fields
33 from tools.translate import _
36 class account_analytic_account(osv.osv):
37 _name = "account.analytic.account"
38 _inherit = "account.analytic.account"
40 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
42 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
44 acc_set = ",".join(map(str, ids2))
45 cr.execute("select account_analytic_line.account_id, sum(amount) \
46 from account_analytic_line \
47 join account_analytic_journal \
48 on account_analytic_line.journal_id = account_analytic_journal.id \
49 where account_analytic_line.account_id IN (%s) \
50 and account_analytic_journal.type = 'sale' \
51 group by account_analytic_line.account_id" % acc_set)
52 for account_id, sum in cr.fetchall():
53 res[account_id] = round(sum,2)
55 res.setdefault(obj_id, 0.0)
56 for child_id in self.search(cr, uid,
57 [('parent_id', 'child_of', [obj_id])]):
58 if child_id != obj_id:
59 res[obj_id] += res.get(child_id, 0.0)
61 res[id] = round(res.get(id, 0.0),2)
64 def _ca_to_invoice_calc(self, cr, uid, ids, name, arg, context={}):
67 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
69 # Amount uninvoiced hours to invoice at sale price
70 acc_set = ",".join(map(str, ids2))
71 cr.execute("""SELECT account_analytic_account.id, \
72 sum (product_template.list_price * \
73 account_analytic_line.unit_amount * \
74 ((100-hr_timesheet_invoice_factor.factor)/100)) \
76 FROM product_template \
77 join product_product \
78 on product_template.id = product_product.product_tmpl_id \
79 JOIN account_analytic_line \
80 on account_analytic_line.product_id = product_product.id \
81 JOIN account_analytic_journal \
82 on account_analytic_line.journal_id = account_analytic_journal.id \
83 JOIN account_analytic_account \
84 on account_analytic_account.id = account_analytic_line.account_id \
85 JOIN hr_timesheet_invoice_factor \
86 on hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
87 WHERE account_analytic_account.id IN (%s) \
88 AND account_analytic_line.invoice_id is null \
89 AND account_analytic_line.to_invoice IS NOT NULL \
90 and account_analytic_journal.type in ('purchase','general') \
91 GROUP BY account_analytic_account.id;"""%acc_set)
92 for account_id, sum in cr.fetchall():
93 res[account_id] = round(sum,2)
95 # Expense amount and purchase invoice
96 #acc_set = ",".join(map(str, ids2))
97 #cr.execute ("select account_analytic_line.account_id, sum(amount) \
98 # from account_analytic_line \
99 # join account_analytic_journal \
100 # on account_analytic_line.journal_id = account_analytic_journal.id \
101 # where account_analytic_line.account_id IN (%s) \
102 # and account_analytic_journal.type = 'purchase' \
103 # GROUP BY account_analytic_line.account_id;"%acc_set)
104 #for account_id, sum in cr.fetchall():
105 # res2[account_id] = round(sum,2)
107 res.setdefault(obj_id, 0.0)
108 res2.setdefault(obj_id, 0.0)
109 for child_id in self.search(cr, uid,
110 [('parent_id', 'child_of', [obj_id])]):
111 if child_id != obj_id:
112 res[obj_id] += res.get(child_id, 0.0)
113 res2[obj_id] += res2.get(child_id, 0.0)
114 # sum both result on account_id
116 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
119 def _hours_qtt_non_invoiced_calc (self, cr, uid, ids, name, arg, context={}):
121 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
123 acc_set = ",".join(map(str, ids2))
124 cr.execute("select account_analytic_line.account_id, sum(unit_amount) \
125 from account_analytic_line \
126 join account_analytic_journal \
127 on account_analytic_line.journal_id = account_analytic_journal.id \
128 where account_analytic_line.account_id IN (%s) \
129 and account_analytic_journal.type='general' \
130 and invoice_id is null \
131 AND to_invoice IS NOT NULL \
132 GROUP BY account_analytic_line.account_id;"%acc_set)
133 for account_id, sum in cr.fetchall():
134 res[account_id] = round(sum,2)
136 res.setdefault(obj_id, 0.0)
137 for child_id in self.search(cr, uid,
138 [('parent_id', 'child_of', [obj_id])]):
139 if child_id != obj_id:
140 res[obj_id] += res.get(child_id, 0.0)
142 res[id] = round(res.get(id, 0.0),2)
145 def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
147 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
149 acc_set = ",".join(map(str, ids2))
150 cr.execute("select account_analytic_line.account_id,sum(unit_amount) \
151 from account_analytic_line \
152 join account_analytic_journal \
153 on account_analytic_line.journal_id = account_analytic_journal.id \
154 where account_analytic_line.account_id IN (%s) \
155 and account_analytic_journal.type='general' \
156 GROUP BY account_analytic_line.account_id"%acc_set)
157 for account_id, sum in cr.fetchall():
158 res[account_id] = round(sum,2)
160 res.setdefault(obj_id, 0.0)
161 for child_id in self.search(cr, uid,
162 [('parent_id', 'child_of', [obj_id])]):
163 if child_id != obj_id:
164 res[obj_id] += res.get(child_id, 0.0)
166 res[id] = round(res.get(id, 0.0),2)
169 def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
171 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
173 acc_set = ",".join(map(str, ids2))
174 cr.execute("""select account_analytic_line.account_id,sum(amount) \
175 from account_analytic_line \
176 join account_analytic_journal \
177 on account_analytic_line.journal_id = account_analytic_journal.id \
178 where account_analytic_line.account_id IN (%s) \
180 GROUP BY account_analytic_line.account_id"""%acc_set)
181 for account_id, sum in cr.fetchall():
182 res[account_id] = round(sum,2)
184 res.setdefault(obj_id, 0.0)
185 for child_id in self.search(cr, uid,
186 [('parent_id', 'child_of', [obj_id])]):
187 if child_id != obj_id:
188 res[obj_id] += res.get(child_id, 0.0)
190 res[id] = round(res.get(id, 0.0),2)
193 def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
196 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
198 acc_set = ",".join(map(str, ids2))
199 cr.execute("""select account_analytic_line.account_id as account_id, \
200 sum((account_analytic_line.unit_amount * pt.list_price) \
201 - (account_analytic_line.unit_amount * pt.list_price \
202 * hr.factor)) as somme
203 from account_analytic_line \
204 left join account_analytic_journal \
205 on (account_analytic_line.journal_id = account_analytic_journal.id) \
206 join product_product pp \
207 on (account_analytic_line.product_id = pp.id) \
208 join product_template pt \
209 on (pp.product_tmpl_id = pt.id) \
210 join account_analytic_account a \
211 on (a.id=account_analytic_line.account_id) \
212 join hr_timesheet_invoice_factor hr \
213 on (hr.id=a.to_invoice) \
214 where account_analytic_line.account_id IN (%s) \
215 and a.to_invoice IS NOT NULL \
216 and account_analytic_journal.type in ('purchase','general')
217 GROUP BY account_analytic_line.account_id"""%acc_set)
218 for account_id, sum in cr.fetchall():
219 res2[account_id] = round(sum,2)
222 res.setdefault(obj_id, 0.0)
223 res2.setdefault(obj_id, 0.0)
224 for child_id in self.search(cr, uid,
225 [('parent_id', 'child_of', [obj_id])]):
226 if child_id != obj_id:
227 res[obj_id] += res.get(child_id, 0.0)
228 res[obj_id] += res2.get(child_id, 0.0)
230 # sum both result on account_id
232 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
235 def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
237 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
239 acc_set = ",".join(map(str, ids2))
240 cr.execute("select account_analytic_line.account_id, max(date) \
241 from account_analytic_line \
242 where account_id IN (%s) \
243 and invoice_id is null \
244 GROUP BY account_analytic_line.account_id" % acc_set)
245 for account_id, sum in cr.fetchall():
246 res[account_id] = sum
248 res.setdefault(obj_id, '')
249 for child_id in self.search(cr, uid,
250 [('parent_id', 'child_of', [obj_id])]):
251 if res[obj_id] < res.get(child_id, ''):
252 res[obj_id] = res.get(child_id, '')
254 res[id] = res.get(id, '')
257 def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
259 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
261 acc_set = ",".join(map(str, ids2))
262 cr.execute ("select account_analytic_line.account_id, \
263 date(max(account_invoice.date_invoice)) \
264 from account_analytic_line \
265 join account_invoice \
266 on account_analytic_line.invoice_id = account_invoice.id \
267 where account_analytic_line.account_id IN (%s) \
268 and account_analytic_line.invoice_id is not null \
269 GROUP BY account_analytic_line.account_id"%acc_set)
270 for account_id, sum in cr.fetchall():
271 res[account_id] = sum
273 res.setdefault(obj_id, '')
274 for child_id in self.search(cr, uid,
275 [('parent_id', 'child_of', [obj_id])]):
276 if res[obj_id] < res.get(child_id, ''):
277 res[obj_id] = res.get(child_id, '')
279 res[id] = res.get(id, '')
282 def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
284 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
286 acc_set = ",".join(map(str, ids2))
287 cr.execute("select account_analytic_line.account_id, max(date) \
288 from account_analytic_line \
289 where account_id IN (%s) \
290 and invoice_id is not null \
291 GROUP BY account_analytic_line.account_id;"%acc_set)
292 for account_id, sum in cr.fetchall():
293 res[account_id] = sum
295 res.setdefault(obj_id, '')
296 for child_id in self.search(cr, uid,
297 [('parent_id', 'child_of', [obj_id])]):
298 if res[obj_id] < res.get(child_id, ''):
299 res[obj_id] = res.get(child_id, '')
301 res[id] = res.get(id, '')
304 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
306 for account in self.browse(cr, uid, ids):
307 if account.quantity_max <> 0:
308 res[account.id] = account.quantity_max - account.hours_quantity
312 res[id] = round(res.get(id, 0.0),2)
315 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
317 for account in self.browse(cr, uid, ids):
318 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
319 if res[account.id] < 0:
322 res[id] = round(res.get(id, 0.0),2)
325 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
327 for account in self.browse(cr, uid, ids):
328 if account.hours_qtt_invoiced == 0:
331 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
333 res[id] = round(res.get(id, 0.0),2)
336 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
338 for account in self.browse(cr, uid, ids):
339 if account.ca_invoiced == 0:
341 elif account.real_margin <> 0.0:
342 res[account.id] = -(account.real_margin / account.total_cost) * 100
344 res[account.id] = 0.0
346 res[id] = round(res.get(id, 0.0),2)
349 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
351 for account in self.browse(cr, uid, ids):
352 if account.amount_max <> 0:
353 res[account.id] = account.amount_max - account.ca_invoiced
357 res[id] = round(res.get(id, 0.0),2)
360 def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
362 for account in self.browse(cr, uid, ids):
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={}):
370 for account in self.browse(cr, uid, ids):
371 res[account.id] = account.ca_theorical + account.total_cost
373 res[id] = round(res.get(id, 0.0),2)
376 def _month(self, cr, uid, ids, name, arg, context=None):
379 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
380 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
381 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
382 'AND unit_amount <> 0.0')
383 res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
386 def _user(self, cr, uid, ids, name, arg, context=None):
388 cr.execute('SELECT MAX(id) FROM res_users')
389 max_user = cr.fetchone()[0]
391 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
392 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
393 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
394 'AND unit_amount <> 0.0')
395 res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
399 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced Amount', help="Total customer invoiced amount for this account."),
400 '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."),
401 '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."),
402 '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."),
403 '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'."),
404 '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."),
405 '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."),
406 '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."),
407 '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."),
408 '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."),
409 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining Hours', help="Computed using the formula: Maximum Quantity - Hours Tot."),
410 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining Revenue', help="Computed using the formula: Max Invoice Price - Invoiced Amount."),
411 '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."),
412 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real Margin', help="Computed using the formula: Invoiced Amount - Total Costs."),
413 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical Margin', help="Computed using the formula: Theorial Revenue - Total Costs"),
414 '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."),
415 'month_ids': fields.function(_month, method=True, type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
416 'user_ids': fields.function(_user, method=True, type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
418 account_analytic_account()
420 class account_analytic_account_summary_user(osv.osv):
421 _name = "account_analytic_analysis.summary.user"
422 _description = "Hours summary by user"
427 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
429 account_obj = self.pool.get('account.analytic.account')
430 cr.execute('SELECT MAX(id) FROM res_users')
431 max_user = cr.fetchone()[0]
432 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
433 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
434 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
435 user_set = ','.join([str(x) for x in user_ids])
437 acc_set = ','.join([str(x) for x in account_ids2])
438 cr.execute('SELECT id, unit_amount ' \
439 'FROM account_analytic_analysis_summary_user ' \
440 'WHERE account_id in (%s) ' \
441 'AND "user" in (%s) ' % (acc_set, user_set))
442 for sum_id, unit_amount in cr.fetchall():
443 res[sum_id] = unit_amount
445 res.setdefault(obj_id, 0.0)
446 for child_id in account_obj.search(cr, uid,
447 [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
448 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
449 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)
451 res[id] = round(res.get(id, 0.0), 2)
455 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
456 'unit_amount': fields.function(_unit_amount, method=True, type='float',
457 string='Total Time'),
458 'user' : fields.many2one('res.users', 'User'),
461 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
463 '(u.account_id * u.max_user) + u."user" AS id, ' \
464 'u.account_id AS account_id, ' \
465 'u."user" AS "user", ' \
466 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
469 'a.id AS account_id, ' \
470 'u1.id AS "user", ' \
471 'MAX(u2.id) AS max_user ' \
473 'res_users AS u1, ' \
474 'res_users AS u2, ' \
475 'account_analytic_account AS a ' \
476 'GROUP BY u1.id, a.id ' \
480 'l.account_id AS account_id, ' \
481 'l.user_id AS "user", ' \
482 'SUM(l.unit_amount) AS unit_amount ' \
483 'FROM account_analytic_line AS l, ' \
484 'account_analytic_journal AS j ' \
485 'WHERE j.type = \'general\' ' \
486 'GROUP BY l.account_id, l.user_id ' \
489 'u.account_id = l.account_id ' \
490 'AND u."user" = l."user"' \
492 'GROUP BY u."user", u.account_id, u.max_user' \
495 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
502 fields = self._columns.keys()
504 # construct a clause for the rules :
505 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
507 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
508 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
511 cr.execute('SELECT MAX(id) FROM res_users')
512 max_user = cr.fetchone()[0]
514 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
515 for i in range(0, len(ids), cr.IN_MAX):
516 sub_ids = ids[i:i+cr.IN_MAX]
518 cr.execute('select %s from \"%s\" where id in (%s) ' \
519 'and account_id in (%s) ' \
520 'and "user" in (%s) and %s order by %s' % \
521 (','.join(fields_pre2 + ['id']), self._table,
522 ','.join([str(x) for x in sub_ids]),
523 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
524 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
526 if not cr.rowcount == len({}.fromkeys(sub_ids)):
527 raise except_orm(_('AccessError'),
528 _('You try to bypass an access rule (Document type: %s).') % self._description)
530 cr.execute('select %s from \"%s\" where id in (%s) ' \
531 'and account_id in (%s) ' \
532 'and "user" in (%s) order by %s' % \
533 (','.join(fields_pre2 + ['id']), self._table,
534 ','.join([str(x) for x in sub_ids]),
535 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
536 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
538 res.extend(cr.dictfetchall())
540 res = map(lambda x: {'id': x}, ids)
543 if self._columns[f].translate:
544 ids = map(lambda x: x['id'], res)
545 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
547 r[f] = res_trans.get(r['id'], False) or r[f]
549 for table in self._inherits:
550 col = self._inherits[table]
551 cols = intersect(self._inherit_fields.keys(), fields)
554 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
562 record.update(res3[record[col]])
563 if col not in fields:
566 # all fields which need to be post-processed by a simple function (symbol_get)
567 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
569 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
570 # to get the _symbol_get in each occurence
572 for f in fields_post:
573 r[f] = self.columns[f]._symbol_get(r[f])
574 ids = map(lambda x: x['id'], res)
576 # all non inherited fields for which the attribute whose name is in load is False
577 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
578 for f in fields_post:
579 # get the value of that field for all records/ids
580 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
582 record[f] = res2[record['id']]
586 account_analytic_account_summary_user()
588 class account_analytic_account_summary_month(osv.osv):
589 _name = "account_analytic_analysis.summary.month"
590 _description = "Hours summary by month"
595 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
597 account_obj = self.pool.get('account.analytic.account')
598 account_ids = [int(str(int(x))[:-6]) for x in ids]
599 month_ids = [int(str(int(x))[-6:]) for x in ids]
600 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
601 month_set = ','.join([str(x) for x in month_ids])
603 acc_set = ','.join([str(x) for x in account_ids2])
604 cr.execute('SELECT id, unit_amount ' \
605 'FROM account_analytic_analysis_summary_month ' \
606 'WHERE account_id in (%s) ' \
607 'AND month_id in (%s) ' % \
608 (acc_set, month_set))
609 for sum_id, unit_amount in cr.fetchall():
610 res[sum_id] = unit_amount
612 res.setdefault(obj_id, 0.0)
613 for child_id in account_obj.search(cr, uid,
614 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
615 if child_id != int(str(int(obj_id))[:-6]):
616 res[obj_id] += res.get(int(child_id * 1000000 + int(obj_id)), 0.0)
618 res[id] = round(res.get(id, 0.0), 2)
622 'account_id': fields.many2one('account.analytic.account', 'Analytic Account',
624 'unit_amount': fields.function(_unit_amount, method=True, type='float',
625 string='Total Time'),
626 'month': fields.char('Month', size=25, readonly=True),
629 cr.execute('DROP VIEW IF EXISTS account_analytic_analysis_summary_month')
630 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
632 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
633 'd.account_id AS account_id, ' \
634 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
635 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
636 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
643 'a.id AS account_id, ' \
644 'l.month AS month ' \
647 'DATE_TRUNC(\'month\', l.date) AS month ' \
648 'FROM account_analytic_line AS l, ' \
649 'account_analytic_journal AS j ' \
650 'WHERE j.type = \'general\' ' \
651 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
653 'account_analytic_account AS a ' \
654 'GROUP BY l.month, a.id ' \
656 'GROUP BY d2.account_id, d2.month ' \
660 'l.account_id AS account_id, ' \
661 'DATE_TRUNC(\'month\', l.date) AS month, ' \
662 'SUM(l.unit_amount) AS unit_amount ' \
663 'FROM account_analytic_line AS l, ' \
664 'account_analytic_journal AS j ' \
665 'WHERE j.type = \'general\' ' \
666 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
669 'd.account_id = l.account_id ' \
670 'AND d.month = l.month' \
672 'GROUP BY d.month, d.account_id ' \
675 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
682 fields = self._columns.keys()
684 # construct a clause for the rules :
685 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
687 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
688 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
692 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
693 for i in range(0, len(ids), cr.IN_MAX):
694 sub_ids = ids[i:i+cr.IN_MAX]
696 cr.execute('select %s from \"%s\" where id in (%s) ' \
697 'and account_id in (%s) ' \
698 'and month_id in (%s) and %s order by %s' % \
699 (','.join(fields_pre2 + ['id']), self._table,
700 ','.join([str(x) for x in sub_ids]),
701 ','.join([str(x)[:-6] for x in sub_ids]),
702 ','.join([str(x)[-6:] for x in sub_ids]), d1,
704 if not cr.rowcount == len({}.fromkeys(sub_ids)):
705 raise except_orm(_('AccessError'),
706 _('You try to bypass an access rule (Document type: %s).') % self._description)
708 cr.execute('select %s from \"%s\" where id in (%s) ' \
709 'and account_id in (%s) ' \
710 'and month_id in (%s) order by %s' % \
711 (','.join(fields_pre2 + ['id']), self._table,
712 ','.join([str(x) for x in sub_ids]),
713 ','.join([str(x)[:-6] for x in sub_ids]),
714 ','.join([str(x)[-6:] for x in sub_ids]),
716 res.extend(cr.dictfetchall())
718 res = map(lambda x: {'id': x}, ids)
721 if self._columns[f].translate:
722 ids = map(lambda x: x['id'], res)
723 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
725 r[f] = res_trans.get(r['id'], False) or r[f]
727 for table in self._inherits:
728 col = self._inherits[table]
729 cols = intersect(self._inherit_fields.keys(), fields)
732 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
740 record.update(res3[record[col]])
741 if col not in fields:
744 # all fields which need to be post-processed by a simple function (symbol_get)
745 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
747 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
748 # to get the _symbol_get in each occurence
750 for f in fields_post:
751 r[f] = self.columns[f]._symbol_get(r[f])
752 ids = map(lambda x: x['id'], res)
754 # all non inherited fields for which the attribute whose name is in load is False
755 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
756 for f in fields_post:
757 # get the value of that field for all records/ids
758 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
760 record[f] = res2[record['id']]
764 account_analytic_account_summary_month()
767 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: