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 tools.translate import _
27 class account_analytic_account(osv.osv):
28 _name = "account.analytic.account"
29 _inherit = "account.analytic.account"
31 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
33 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
35 acc_set = ",".join(map(str, ids2))
36 cr.execute("select account_analytic_line.account_id, sum(amount) \
37 from account_analytic_line \
38 join account_analytic_journal \
39 on account_analytic_line.journal_id = account_analytic_journal.id \
40 where account_analytic_line.account_id IN (%s) \
41 and account_analytic_journal.type = 'sale' \
42 group by account_analytic_line.account_id" % acc_set)
43 for account_id, sum in cr.fetchall():
44 res[account_id] = round(sum,2)
46 res.setdefault(obj_id, 0.0)
47 for child_id in self.search(cr, uid,
48 [('parent_id', 'child_of', [obj_id])]):
49 if child_id != obj_id:
50 res[obj_id] += res.get(child_id, 0.0)
52 res[id] = round(res.get(id, 0.0),2)
55 def _ca_to_invoice_calc(self, cr, uid, ids, name, arg, context={}):
58 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
60 # Amount uninvoiced hours to invoice at sale price
61 acc_set = ",".join(map(str, ids2))
62 cr.execute("""SELECT account_analytic_account.id, \
63 sum (product_template.list_price * \
64 account_analytic_line.unit_amount * \
65 ((100-hr_timesheet_invoice_factor.factor)/100)) \
67 FROM product_template \
68 join product_product \
69 on product_template.id = product_product.product_tmpl_id \
70 JOIN account_analytic_line \
71 on account_analytic_line.product_id = product_product.id \
72 JOIN account_analytic_journal \
73 on account_analytic_line.journal_id = account_analytic_journal.id \
74 JOIN account_analytic_account \
75 on account_analytic_account.id = account_analytic_line.account_id \
76 JOIN hr_timesheet_invoice_factor \
77 on hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
78 WHERE account_analytic_account.id IN (%s) \
79 AND account_analytic_line.invoice_id is null \
80 AND account_analytic_line.to_invoice IS NOT NULL \
81 and account_analytic_journal.type in ('purchase','general') \
82 GROUP BY account_analytic_account.id;"""%acc_set)
83 for account_id, sum in cr.fetchall():
84 res[account_id] = round(sum,2)
86 # Expense amount and purchase invoice
87 #acc_set = ",".join(map(str, ids2))
88 #cr.execute ("select account_analytic_line.account_id, sum(amount) \
89 # from account_analytic_line \
90 # join account_analytic_journal \
91 # on account_analytic_line.journal_id = account_analytic_journal.id \
92 # where account_analytic_line.account_id IN (%s) \
93 # and account_analytic_journal.type = 'purchase' \
94 # GROUP BY account_analytic_line.account_id;"%acc_set)
95 #for account_id, sum in cr.fetchall():
96 # res2[account_id] = round(sum,2)
98 res.setdefault(obj_id, 0.0)
99 res2.setdefault(obj_id, 0.0)
100 for child_id in self.search(cr, uid,
101 [('parent_id', 'child_of', [obj_id])]):
102 if child_id != obj_id:
103 res[obj_id] += res.get(child_id, 0.0)
104 res2[obj_id] += res2.get(child_id, 0.0)
105 # sum both result on account_id
107 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
110 def _hours_qtt_non_invoiced_calc (self, cr, uid, ids, name, arg, context={}):
112 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
114 acc_set = ",".join(map(str, ids2))
115 cr.execute("select account_analytic_line.account_id, sum(unit_amount) \
116 from account_analytic_line \
117 join account_analytic_journal \
118 on account_analytic_line.journal_id = account_analytic_journal.id \
119 where account_analytic_line.account_id IN (%s) \
120 and account_analytic_journal.type='general' \
121 and invoice_id is null \
122 AND to_invoice IS NOT NULL \
123 GROUP BY account_analytic_line.account_id;"%acc_set)
124 for account_id, sum in cr.fetchall():
125 res[account_id] = round(sum,2)
127 res.setdefault(obj_id, 0.0)
128 for child_id in self.search(cr, uid,
129 [('parent_id', 'child_of', [obj_id])]):
130 if child_id != obj_id:
131 res[obj_id] += res.get(child_id, 0.0)
133 res[id] = round(res.get(id, 0.0),2)
136 def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
138 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
140 acc_set = ",".join(map(str, ids2))
141 cr.execute("select account_analytic_line.account_id,sum(unit_amount) \
142 from account_analytic_line \
143 join account_analytic_journal \
144 on account_analytic_line.journal_id = account_analytic_journal.id \
145 where account_analytic_line.account_id IN (%s) \
146 and account_analytic_journal.type='general' \
147 GROUP BY account_analytic_line.account_id"%acc_set)
148 for account_id, sum in cr.fetchall():
149 res[account_id] = round(sum,2)
151 res.setdefault(obj_id, 0.0)
152 for child_id in self.search(cr, uid,
153 [('parent_id', 'child_of', [obj_id])]):
154 if child_id != obj_id:
155 res[obj_id] += res.get(child_id, 0.0)
157 res[id] = round(res.get(id, 0.0),2)
160 def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
162 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
164 acc_set = ",".join(map(str, ids2))
165 cr.execute("""select account_analytic_line.account_id,sum(amount) \
166 from account_analytic_line \
167 join account_analytic_journal \
168 on account_analytic_line.journal_id = account_analytic_journal.id \
169 where account_analytic_line.account_id IN (%s) \
171 GROUP BY account_analytic_line.account_id"""%acc_set)
172 for account_id, sum in cr.fetchall():
173 res[account_id] = round(sum,2)
175 res.setdefault(obj_id, 0.0)
176 for child_id in self.search(cr, uid,
177 [('parent_id', 'child_of', [obj_id])]):
178 if child_id != obj_id:
179 res[obj_id] += res.get(child_id, 0.0)
181 res[id] = round(res.get(id, 0.0),2)
184 def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
187 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
189 acc_set = ",".join(map(str, ids2))
190 cr.execute("""select account_analytic_line.account_id as account_id, \
191 sum((account_analytic_line.unit_amount * pt.list_price) \
192 - (account_analytic_line.unit_amount * pt.list_price \
193 * hr.factor)) as somme
194 from account_analytic_line \
195 left join account_analytic_journal \
196 on (account_analytic_line.journal_id = account_analytic_journal.id) \
197 join product_product pp \
198 on (account_analytic_line.product_id = pp.id) \
199 join product_template pt \
200 on (pp.product_tmpl_id = pt.id) \
201 join account_analytic_account a \
202 on (a.id=account_analytic_line.account_id) \
203 join hr_timesheet_invoice_factor hr \
204 on (hr.id=a.to_invoice) \
205 where account_analytic_line.account_id IN (%s) \
206 and a.to_invoice IS NOT NULL \
207 and account_analytic_journal.type in ('purchase','general')
208 GROUP BY account_analytic_line.account_id"""%acc_set)
209 for account_id, sum in cr.fetchall():
210 res2[account_id] = round(sum,2)
213 res.setdefault(obj_id, 0.0)
214 res2.setdefault(obj_id, 0.0)
215 for child_id in self.search(cr, uid,
216 [('parent_id', 'child_of', [obj_id])]):
217 if child_id != obj_id:
218 res[obj_id] += res.get(child_id, 0.0)
219 res[obj_id] += res2.get(child_id, 0.0)
221 # sum both result on account_id
223 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
226 def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
228 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
230 acc_set = ",".join(map(str, ids2))
231 cr.execute("select account_analytic_line.account_id, max(date) \
232 from account_analytic_line \
233 where account_id IN (%s) \
234 and invoice_id is null \
235 GROUP BY account_analytic_line.account_id" % acc_set)
236 for account_id, sum in cr.fetchall():
237 res[account_id] = sum
239 res.setdefault(obj_id, '')
240 for child_id in self.search(cr, uid,
241 [('parent_id', 'child_of', [obj_id])]):
242 if res[obj_id] < res.get(child_id, ''):
243 res[obj_id] = res.get(child_id, '')
245 res[id] = res.get(id, '')
248 def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
250 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
252 acc_set = ",".join(map(str, ids2))
253 cr.execute ("select account_analytic_line.account_id, \
254 date(max(account_invoice.date_invoice)) \
255 from account_analytic_line \
256 join account_invoice \
257 on account_analytic_line.invoice_id = account_invoice.id \
258 where account_analytic_line.account_id IN (%s) \
259 and account_analytic_line.invoice_id is not null \
260 GROUP BY account_analytic_line.account_id"%acc_set)
261 for account_id, sum in cr.fetchall():
262 res[account_id] = sum
264 res.setdefault(obj_id, '')
265 for child_id in self.search(cr, uid,
266 [('parent_id', 'child_of', [obj_id])]):
267 if res[obj_id] < res.get(child_id, ''):
268 res[obj_id] = res.get(child_id, '')
270 res[id] = res.get(id, '')
273 def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
275 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
277 acc_set = ",".join(map(str, ids2))
278 cr.execute("select account_analytic_line.account_id, max(date) \
279 from account_analytic_line \
280 where account_id IN (%s) \
281 and invoice_id is not null \
282 GROUP BY account_analytic_line.account_id;"%acc_set)
283 for account_id, sum in cr.fetchall():
284 res[account_id] = sum
286 res.setdefault(obj_id, '')
287 for child_id in self.search(cr, uid,
288 [('parent_id', 'child_of', [obj_id])]):
289 if res[obj_id] < res.get(child_id, ''):
290 res[obj_id] = res.get(child_id, '')
292 res[id] = res.get(id, '')
295 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
297 for account in self.browse(cr, uid, ids):
298 if account.quantity_max <> 0:
299 res[account.id] = account.quantity_max - account.hours_quantity
303 res[id] = round(res.get(id, 0.0),2)
306 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
308 for account in self.browse(cr, uid, ids):
309 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
310 if res[account.id] < 0:
313 res[id] = round(res.get(id, 0.0),2)
316 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
318 for account in self.browse(cr, uid, ids):
319 if account.hours_qtt_invoiced == 0:
322 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
324 res[id] = round(res.get(id, 0.0),2)
327 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
329 for account in self.browse(cr, uid, ids):
330 if account.ca_invoiced == 0:
332 elif account.total_cost <> 0.0:
333 res[account.id] = -(account.real_margin / account.total_cost) * 100
335 res[account.id] = 0.0
337 res[id] = round(res.get(id, 0.0),2)
340 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
342 for account in self.browse(cr, uid, ids):
343 if account.amount_max <> 0:
344 res[account.id] = account.amount_max - account.ca_invoiced
348 res[id] = round(res.get(id, 0.0),2)
351 def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
353 for account in self.browse(cr, uid, ids):
354 res[account.id] = account.ca_invoiced + account.total_cost
356 res[id] = round(res.get(id, 0.0),2)
359 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context={}):
361 for account in self.browse(cr, uid, ids):
362 res[account.id] = account.ca_theorical + account.total_cost
364 res[id] = round(res.get(id, 0.0),2)
367 def _month(self, cr, uid, ids, name, arg, context=None):
370 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
371 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
372 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
373 'AND unit_amount <> 0.0')
374 res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
377 def _user(self, cr, uid, ids, name, arg, context=None):
379 cr.execute('SELECT MAX(id) FROM res_users')
380 max_user = cr.fetchone()[0]
382 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
383 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
384 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
385 'AND unit_amount <> 0.0')
386 res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
390 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced Amount', help="Total customer invoiced amount for this account."),
391 '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."),
392 '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."),
393 '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."),
394 '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'."),
395 '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."),
396 '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."),
397 '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."),
398 '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."),
399 '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."),
400 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining Hours', help="Computed using the formula: Maximum Quantity - Hours Tot."),
401 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining Revenue', help="Computed using the formula: Max Invoice Price - Invoiced Amount."),
402 '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."),
403 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real Margin', help="Computed using the formula: Invoiced Amount - Total Costs."),
404 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical Margin', help="Computed using the formula: Theorial Revenue - Total Costs"),
405 '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."),
406 'month_ids': fields.function(_month, method=True, type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
407 'user_ids': fields.function(_user, method=True, type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
409 account_analytic_account()
411 class account_analytic_account_summary_user(osv.osv):
412 _name = "account_analytic_analysis.summary.user"
413 _description = "Hours summary by user"
418 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
420 account_obj = self.pool.get('account.analytic.account')
421 cr.execute('SELECT MAX(id) FROM res_users')
422 max_user = cr.fetchone()[0]
423 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
424 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
425 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
426 user_set = ','.join([str(x) for x in user_ids])
428 acc_set = ','.join([str(x) for x in account_ids2])
429 cr.execute('SELECT id, unit_amount ' \
430 'FROM account_analytic_analysis_summary_user ' \
431 'WHERE account_id in (%s) ' \
432 'AND "user" in (%s) ' % (acc_set, user_set))
433 for sum_id, unit_amount in cr.fetchall():
434 res[sum_id] = unit_amount
436 res.setdefault(obj_id, 0.0)
437 for child_id in account_obj.search(cr, uid,
438 [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
439 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
440 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)
442 res[id] = round(res.get(id, 0.0), 2)
446 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
447 'unit_amount': fields.function(_unit_amount, method=True, type='float',
448 string='Total Time'),
449 'user' : fields.many2one('res.users', 'User'),
452 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
454 '(u.account_id * u.max_user) + u."user" AS id, ' \
455 'u.account_id AS account_id, ' \
456 'u."user" AS "user", ' \
457 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
460 'a.id AS account_id, ' \
461 'u1.id AS "user", ' \
462 'MAX(u2.id) AS max_user ' \
464 'res_users AS u1, ' \
465 'res_users AS u2, ' \
466 'account_analytic_account AS a ' \
467 'GROUP BY u1.id, a.id ' \
471 'l.account_id AS account_id, ' \
472 'l.user_id AS "user", ' \
473 'SUM(l.unit_amount) AS unit_amount ' \
474 'FROM account_analytic_line AS l, ' \
475 'account_analytic_journal AS j ' \
476 'WHERE j.type = \'general\' ' \
477 'GROUP BY l.account_id, l.user_id ' \
480 'u.account_id = l.account_id ' \
481 'AND u."user" = l."user"' \
483 'GROUP BY u."user", u.account_id, u.max_user' \
486 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
493 fields = self._columns.keys()
495 # construct a clause for the rules :
496 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
498 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
499 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
502 cr.execute('SELECT MAX(id) FROM res_users')
503 max_user = cr.fetchone()[0]
505 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
506 for i in range(0, len(ids), cr.IN_MAX):
507 sub_ids = ids[i:i+cr.IN_MAX]
509 cr.execute('select %s from \"%s\" where id in (%s) ' \
510 'and account_id in (%s) ' \
511 'and "user" in (%s) and %s order by %s' % \
512 (','.join(fields_pre2 + ['id']), self._table,
513 ','.join([str(x) for x in sub_ids]),
514 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
515 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
517 if not cr.rowcount == len({}.fromkeys(sub_ids)):
518 raise except_orm(_('AccessError'),
519 _('You try to bypass an access rule (Document type: %s).') % self._description)
521 cr.execute('select %s from \"%s\" where id in (%s) ' \
522 'and account_id in (%s) ' \
523 'and "user" in (%s) order by %s' % \
524 (','.join(fields_pre2 + ['id']), self._table,
525 ','.join([str(x) for x in sub_ids]),
526 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
527 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
529 res.extend(cr.dictfetchall())
531 res = map(lambda x: {'id': x}, ids)
534 if self._columns[f].translate:
535 ids = map(lambda x: x['id'], res)
536 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
538 r[f] = res_trans.get(r['id'], False) or r[f]
540 for table in self._inherits:
541 col = self._inherits[table]
542 cols = intersect(self._inherit_fields.keys(), fields)
545 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
553 record.update(res3[record[col]])
554 if col not in fields:
557 # all fields which need to be post-processed by a simple function (symbol_get)
558 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
560 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
561 # to get the _symbol_get in each occurence
563 for f in fields_post:
564 r[f] = self.columns[f]._symbol_get(r[f])
565 ids = map(lambda x: x['id'], res)
567 # all non inherited fields for which the attribute whose name is in load is False
568 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
569 for f in fields_post:
570 # get the value of that field for all records/ids
571 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
573 record[f] = res2[record['id']]
577 account_analytic_account_summary_user()
579 class account_analytic_account_summary_month(osv.osv):
580 _name = "account_analytic_analysis.summary.month"
581 _description = "Hours summary by month"
586 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
588 account_obj = self.pool.get('account.analytic.account')
589 account_ids = [int(str(int(x))[:-6]) for x in ids]
590 month_ids = [int(str(int(x))[-6:]) for x in ids]
591 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
592 month_set = ','.join([str(x) for x in month_ids])
594 acc_set = ','.join([str(x) for x in account_ids2])
595 cr.execute('SELECT id, unit_amount ' \
596 'FROM account_analytic_analysis_summary_month ' \
597 'WHERE account_id in (%s) ' \
598 'AND month_id in (%s) ' % \
599 (acc_set, month_set))
600 for sum_id, unit_amount in cr.fetchall():
601 res[sum_id] = unit_amount
603 res.setdefault(obj_id, 0.0)
604 for child_id in account_obj.search(cr, uid,
605 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
606 if child_id != int(str(int(obj_id))[:-6]):
607 res[obj_id] += res.get(int(child_id * 1000000 + int(obj_id)), 0.0)
609 res[id] = round(res.get(id, 0.0), 2)
613 'account_id': fields.many2one('account.analytic.account', 'Analytic Account',
615 'unit_amount': fields.function(_unit_amount, method=True, type='float',
616 string='Total Time'),
617 'month': fields.char('Month', size=25, readonly=True),
620 cr.execute('DROP VIEW IF EXISTS account_analytic_analysis_summary_month')
621 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
623 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
624 'd.account_id AS account_id, ' \
625 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
626 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
627 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
634 'a.id AS account_id, ' \
635 'l.month AS month ' \
638 'DATE_TRUNC(\'month\', l.date) AS month ' \
639 'FROM account_analytic_line AS l, ' \
640 'account_analytic_journal AS j ' \
641 'WHERE j.type = \'general\' ' \
642 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
644 'account_analytic_account AS a ' \
645 'GROUP BY l.month, a.id ' \
647 'GROUP BY d2.account_id, d2.month ' \
651 'l.account_id AS account_id, ' \
652 'DATE_TRUNC(\'month\', l.date) AS month, ' \
653 'SUM(l.unit_amount) AS unit_amount ' \
654 'FROM account_analytic_line AS l, ' \
655 'account_analytic_journal AS j ' \
656 'WHERE j.type = \'general\' ' \
657 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
660 'd.account_id = l.account_id ' \
661 'AND d.month = l.month' \
663 'GROUP BY d.month, d.account_id ' \
666 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
673 fields = self._columns.keys()
675 # construct a clause for the rules :
676 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
678 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
679 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
683 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
684 for i in range(0, len(ids), cr.IN_MAX):
685 sub_ids = ids[i:i+cr.IN_MAX]
687 cr.execute('select %s from \"%s\" where id in (%s) ' \
688 'and account_id in (%s) ' \
689 'and month_id in (%s) and %s order by %s' % \
690 (','.join(fields_pre2 + ['id']), self._table,
691 ','.join([str(x) for x in sub_ids]),
692 ','.join([str(x)[:-6] for x in sub_ids]),
693 ','.join([str(x)[-6:] for x in sub_ids]), d1,
695 if not cr.rowcount == len({}.fromkeys(sub_ids)):
696 raise except_orm(_('AccessError'),
697 _('You try to bypass an access rule (Document type: %s).') % self._description)
699 cr.execute('select %s from \"%s\" where id in (%s) ' \
700 'and account_id in (%s) ' \
701 'and month_id in (%s) order by %s' % \
702 (','.join(fields_pre2 + ['id']), self._table,
703 ','.join([str(x) for x in sub_ids]),
704 ','.join([str(x)[:-6] for x in sub_ids]),
705 ','.join([str(x)[-6:] for x in sub_ids]),
707 res.extend(cr.dictfetchall())
709 res = map(lambda x: {'id': x}, ids)
712 if self._columns[f].translate:
713 ids = map(lambda x: x['id'], res)
714 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
716 r[f] = res_trans.get(r['id'], False) or r[f]
718 for table in self._inherits:
719 col = self._inherits[table]
720 cols = intersect(self._inherit_fields.keys(), fields)
723 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
731 record.update(res3[record[col]])
732 if col not in fields:
735 # all fields which need to be post-processed by a simple function (symbol_get)
736 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
738 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
739 # to get the _symbol_get in each occurence
741 for f in fields_post:
742 r[f] = self.columns[f]._symbol_get(r[f])
743 ids = map(lambda x: x['id'], res)
745 # all non inherited fields for which the attribute whose name is in load is False
746 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
747 for f in fields_post:
748 # get the value of that field for all records/ids
749 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
751 record[f] = res2[record['id']]
755 account_analytic_account_summary_month()
758 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: