1 # -*- encoding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2009 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
26 from tools.translate import _
29 class account_analytic_account(osv.osv):
30 _name = "account.analytic.account"
31 _inherit = "account.analytic.account"
33 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
35 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
37 acc_set = ",".join(map(str, ids2))
38 cr.execute("select account_analytic_line.account_id, sum(amount) \
39 from account_analytic_line \
40 join account_analytic_journal \
41 on account_analytic_line.journal_id = account_analytic_journal.id \
42 where account_analytic_line.account_id IN (%s) \
43 and account_analytic_journal.type = 'sale' \
44 group by account_analytic_line.account_id" % acc_set)
45 for account_id, sum in cr.fetchall():
46 res[account_id] = round(sum,2)
48 res.setdefault(obj_id, 0.0)
49 for child_id in self.search(cr, uid,
50 [('parent_id', 'child_of', [obj_id])]):
51 if child_id != obj_id:
52 res[obj_id] += res.get(child_id, 0.0)
54 res[id] = round(res.get(id, 0.0),2)
57 def _ca_to_invoice_calc(self, cr, uid, ids, name, arg, context={}):
60 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
62 # Amount uninvoiced hours to invoice at sale price
63 acc_set = ",".join(map(str, ids2))
64 cr.execute("""SELECT account_analytic_account.id, \
65 sum (product_template.list_price * \
66 account_analytic_line.unit_amount * \
67 ((100-hr_timesheet_invoice_factor.factor)/100)) \
69 FROM product_template \
70 join product_product \
71 on product_template.id = product_product.product_tmpl_id \
72 JOIN account_analytic_line \
73 on account_analytic_line.product_id = product_product.id \
74 JOIN account_analytic_journal \
75 on account_analytic_line.journal_id = account_analytic_journal.id \
76 JOIN account_analytic_account \
77 on account_analytic_account.id = account_analytic_line.account_id \
78 JOIN hr_timesheet_invoice_factor \
79 on hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
80 WHERE account_analytic_account.id IN (%s) \
81 AND account_analytic_line.invoice_id is null \
82 AND account_analytic_line.to_invoice IS NOT NULL \
83 and account_analytic_journal.type in ('purchase','general') \
84 GROUP BY account_analytic_account.id;"""%acc_set)
85 for account_id, sum in cr.fetchall():
86 res[account_id] = round(sum,2)
88 # Expense amount and purchase invoice
89 #acc_set = ",".join(map(str, ids2))
90 #cr.execute ("select account_analytic_line.account_id, sum(amount) \
91 # from account_analytic_line \
92 # join account_analytic_journal \
93 # on account_analytic_line.journal_id = account_analytic_journal.id \
94 # where account_analytic_line.account_id IN (%s) \
95 # and account_analytic_journal.type = 'purchase' \
96 # GROUP BY account_analytic_line.account_id;"%acc_set)
97 #for account_id, sum in cr.fetchall():
98 # res2[account_id] = round(sum,2)
100 res.setdefault(obj_id, 0.0)
101 res2.setdefault(obj_id, 0.0)
102 for child_id in self.search(cr, uid,
103 [('parent_id', 'child_of', [obj_id])]):
104 if child_id != obj_id:
105 res[obj_id] += res.get(child_id, 0.0)
106 res2[obj_id] += res2.get(child_id, 0.0)
107 # sum both result on account_id
109 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
112 def _hours_qtt_non_invoiced_calc (self, cr, uid, ids, name, arg, context={}):
114 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
116 acc_set = ",".join(map(str, ids2))
117 cr.execute("select account_analytic_line.account_id, sum(unit_amount) \
118 from account_analytic_line \
119 join account_analytic_journal \
120 on account_analytic_line.journal_id = account_analytic_journal.id \
121 where account_analytic_line.account_id IN (%s) \
122 and account_analytic_journal.type='general' \
123 and invoice_id is null \
124 AND to_invoice IS NOT NULL \
125 GROUP BY account_analytic_line.account_id;"%acc_set)
126 for account_id, sum in cr.fetchall():
127 res[account_id] = round(sum,2)
129 res.setdefault(obj_id, 0.0)
130 for child_id in self.search(cr, uid,
131 [('parent_id', 'child_of', [obj_id])]):
132 if child_id != obj_id:
133 res[obj_id] += res.get(child_id, 0.0)
135 res[id] = round(res.get(id, 0.0),2)
138 def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
140 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
142 acc_set = ",".join(map(str, ids2))
143 cr.execute("select account_analytic_line.account_id,sum(unit_amount) \
144 from account_analytic_line \
145 join account_analytic_journal \
146 on account_analytic_line.journal_id = account_analytic_journal.id \
147 where account_analytic_line.account_id IN (%s) \
148 and account_analytic_journal.type='general' \
149 GROUP BY account_analytic_line.account_id"%acc_set)
150 for account_id, sum in cr.fetchall():
151 res[account_id] = round(sum,2)
153 res.setdefault(obj_id, 0.0)
154 for child_id in self.search(cr, uid,
155 [('parent_id', 'child_of', [obj_id])]):
156 if child_id != obj_id:
157 res[obj_id] += res.get(child_id, 0.0)
159 res[id] = round(res.get(id, 0.0),2)
162 def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
164 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
166 acc_set = ",".join(map(str, ids2))
167 cr.execute("""select account_analytic_line.account_id,sum(amount) \
168 from account_analytic_line \
169 join account_analytic_journal \
170 on account_analytic_line.journal_id = account_analytic_journal.id \
171 where account_analytic_line.account_id IN (%s) \
173 GROUP BY account_analytic_line.account_id"""%acc_set)
174 for account_id, sum in cr.fetchall():
175 res[account_id] = round(sum,2)
177 res.setdefault(obj_id, 0.0)
178 for child_id in self.search(cr, uid,
179 [('parent_id', 'child_of', [obj_id])]):
180 if child_id != obj_id:
181 res[obj_id] += res.get(child_id, 0.0)
183 res[id] = round(res.get(id, 0.0),2)
186 def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
189 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
191 acc_set = ",".join(map(str, ids2))
192 cr.execute("""select account_analytic_line.account_id as account_id, \
193 sum((account_analytic_line.unit_amount * pt.list_price) \
194 - (account_analytic_line.unit_amount * pt.list_price \
195 * hr.factor)) as somme
196 from account_analytic_line \
197 left join account_analytic_journal \
198 on (account_analytic_line.journal_id = account_analytic_journal.id) \
199 join product_product pp \
200 on (account_analytic_line.product_id = pp.id) \
201 join product_template pt \
202 on (pp.product_tmpl_id = pt.id) \
203 join account_analytic_account a \
204 on (a.id=account_analytic_line.account_id) \
205 join hr_timesheet_invoice_factor hr \
206 on (hr.id=a.to_invoice) \
207 where account_analytic_line.account_id IN (%s) \
208 and a.to_invoice IS NOT NULL \
209 and account_analytic_journal.type in ('purchase','general')
210 GROUP BY account_analytic_line.account_id"""%acc_set)
211 for account_id, sum in cr.fetchall():
212 res2[account_id] = round(sum,2)
215 res.setdefault(obj_id, 0.0)
216 res2.setdefault(obj_id, 0.0)
217 for child_id in self.search(cr, uid,
218 [('parent_id', 'child_of', [obj_id])]):
219 if child_id != obj_id:
220 res[obj_id] += res.get(child_id, 0.0)
221 res[obj_id] += res2.get(child_id, 0.0)
223 # sum both result on account_id
225 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
228 def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
230 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
232 acc_set = ",".join(map(str, ids2))
233 cr.execute("select account_analytic_line.account_id, max(date) \
234 from account_analytic_line \
235 where account_id IN (%s) \
236 and invoice_id is null \
237 GROUP BY account_analytic_line.account_id" % acc_set)
238 for account_id, sum in cr.fetchall():
239 res[account_id] = sum
241 res.setdefault(obj_id, '')
242 for child_id in self.search(cr, uid,
243 [('parent_id', 'child_of', [obj_id])]):
244 if res[obj_id] < res.get(child_id, ''):
245 res[obj_id] = res.get(child_id, '')
247 res[id] = res.get(id, '')
250 def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
252 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
254 acc_set = ",".join(map(str, ids2))
255 cr.execute ("select account_analytic_line.account_id, \
256 date(max(account_invoice.date_invoice)) \
257 from account_analytic_line \
258 join account_invoice \
259 on account_analytic_line.invoice_id = account_invoice.id \
260 where account_analytic_line.account_id IN (%s) \
261 and account_analytic_line.invoice_id is not null \
262 GROUP BY account_analytic_line.account_id"%acc_set)
263 for account_id, sum in cr.fetchall():
264 res[account_id] = sum
266 res.setdefault(obj_id, '')
267 for child_id in self.search(cr, uid,
268 [('parent_id', 'child_of', [obj_id])]):
269 if res[obj_id] < res.get(child_id, ''):
270 res[obj_id] = res.get(child_id, '')
272 res[id] = res.get(id, '')
275 def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
277 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
279 acc_set = ",".join(map(str, ids2))
280 cr.execute("select account_analytic_line.account_id, max(date) \
281 from account_analytic_line \
282 where account_id IN (%s) \
283 and invoice_id is not null \
284 GROUP BY account_analytic_line.account_id;"%acc_set)
285 for account_id, sum in cr.fetchall():
286 res[account_id] = sum
288 res.setdefault(obj_id, '')
289 for child_id in self.search(cr, uid,
290 [('parent_id', 'child_of', [obj_id])]):
291 if res[obj_id] < res.get(child_id, ''):
292 res[obj_id] = res.get(child_id, '')
294 res[id] = res.get(id, '')
297 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
299 for account in self.browse(cr, uid, ids):
300 if account.quantity_max <> 0:
301 res[account.id] = account.quantity_max - account.hours_quantity
305 res[id] = round(res.get(id, 0.0),2)
308 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
310 for account in self.browse(cr, uid, ids):
311 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
312 if res[account.id] < 0:
315 res[id] = round(res.get(id, 0.0),2)
318 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
320 for account in self.browse(cr, uid, ids):
321 if account.hours_qtt_invoiced == 0:
324 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
326 res[id] = round(res.get(id, 0.0),2)
329 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
331 for account in self.browse(cr, uid, ids):
332 if account.ca_invoiced == 0:
334 elif account.total_cost <> 0.0:
335 res[account.id] = -(account.real_margin / account.total_cost) * 100
337 res[account.id] = 0.0
339 res[id] = round(res.get(id, 0.0),2)
342 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
344 for account in self.browse(cr, uid, ids):
345 if account.amount_max <> 0:
346 res[account.id] = account.amount_max - account.ca_invoiced
350 res[id] = round(res.get(id, 0.0),2)
353 def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
355 for account in self.browse(cr, uid, ids):
356 res[account.id] = account.ca_invoiced + account.total_cost
358 res[id] = round(res.get(id, 0.0),2)
361 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context={}):
363 for account in self.browse(cr, uid, ids):
364 res[account.id] = account.ca_theorical + account.total_cost
366 res[id] = round(res.get(id, 0.0),2)
369 def _month(self, cr, uid, ids, name, arg, context=None):
372 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
373 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
374 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
375 'AND unit_amount <> 0.0')
376 res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
379 def _user(self, cr, uid, ids, name, arg, context=None):
381 cr.execute('SELECT MAX(id) FROM res_users')
382 max_user = cr.fetchone()[0]
384 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
385 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
386 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
387 'AND unit_amount <> 0.0')
388 res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
392 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced Amount', help="Total customer invoiced amount for this account."),
393 '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."),
394 '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."),
395 '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."),
396 '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'."),
397 '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."),
398 '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."),
399 '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."),
400 '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."),
401 '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."),
402 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining Hours', help="Computed using the formula: Maximum Quantity - Hours Tot."),
403 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining Revenue', help="Computed using the formula: Max Invoice Price - Invoiced Amount."),
404 '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."),
405 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real Margin', help="Computed using the formula: Invoiced Amount - Total Costs."),
406 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical Margin', help="Computed using the formula: Theorial Revenue - Total Costs"),
407 '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."),
408 'month_ids': fields.function(_month, method=True, type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
409 'user_ids': fields.function(_user, method=True, type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
411 account_analytic_account()
413 class account_analytic_account_summary_user(osv.osv):
414 _name = "account_analytic_analysis.summary.user"
415 _description = "Hours summary by user"
420 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
422 account_obj = self.pool.get('account.analytic.account')
423 cr.execute('SELECT MAX(id) FROM res_users')
424 max_user = cr.fetchone()[0]
425 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
426 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
427 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
428 user_set = ','.join([str(x) for x in user_ids])
430 acc_set = ','.join([str(x) for x in account_ids2])
431 cr.execute('SELECT id, unit_amount ' \
432 'FROM account_analytic_analysis_summary_user ' \
433 'WHERE account_id in (%s) ' \
434 'AND "user" in (%s) ' % (acc_set, user_set))
435 for sum_id, unit_amount in cr.fetchall():
436 res[sum_id] = unit_amount
438 res.setdefault(obj_id, 0.0)
439 for child_id in account_obj.search(cr, uid,
440 [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
441 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
442 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)
444 res[id] = round(res.get(id, 0.0), 2)
448 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
449 'unit_amount': fields.function(_unit_amount, method=True, type='float',
450 string='Total Time'),
451 'user' : fields.many2one('res.users', 'User'),
454 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
455 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
457 '(u.account_id * u.max_user) + u."user" AS id, ' \
458 'u.account_id AS account_id, ' \
459 'u."user" AS "user", ' \
460 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
463 'a.id AS account_id, ' \
464 'u1.id AS "user", ' \
465 'MAX(u2.id) AS max_user ' \
467 'res_users AS u1, ' \
468 'res_users AS u2, ' \
469 'account_analytic_account AS a ' \
470 'GROUP BY u1.id, a.id ' \
474 'l.account_id AS account_id, ' \
475 'l.user_id AS "user", ' \
476 'SUM(l.unit_amount) AS unit_amount ' \
477 'FROM account_analytic_line AS l, ' \
478 'account_analytic_journal AS j ' \
479 'WHERE j.type = \'general\' ' \
480 'GROUP BY l.account_id, l.user_id ' \
483 'u.account_id = l.account_id ' \
484 'AND u."user" = l."user"' \
486 'GROUP BY u."user", u.account_id, u.max_user' \
489 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
496 fields = self._columns.keys()
498 # construct a clause for the rules :
499 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
501 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
502 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
505 cr.execute('SELECT MAX(id) FROM res_users')
506 max_user = cr.fetchone()[0]
508 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
509 for i in range(0, len(ids), cr.IN_MAX):
510 sub_ids = ids[i:i+cr.IN_MAX]
512 cr.execute('select %s from \"%s\" where id in (%s) ' \
513 'and account_id in (%s) ' \
514 'and "user" in (%s) and %s order by %s' % \
515 (','.join(fields_pre2 + ['id']), self._table,
516 ','.join([str(x) for x in sub_ids]),
517 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
518 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
520 if not cr.rowcount == len({}.fromkeys(sub_ids)):
521 raise except_orm(_('AccessError'),
522 _('You try to bypass an access rule (Document type: %s).') % self._description)
524 cr.execute('select %s from \"%s\" where id in (%s) ' \
525 'and account_id in (%s) ' \
526 'and "user" in (%s) order by %s' % \
527 (','.join(fields_pre2 + ['id']), self._table,
528 ','.join([str(x) for x in sub_ids]),
529 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
530 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
532 res.extend(cr.dictfetchall())
534 res = map(lambda x: {'id': x}, ids)
537 if self._columns[f].translate:
538 ids = map(lambda x: x['id'], res)
539 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
541 r[f] = res_trans.get(r['id'], False) or r[f]
543 for table in self._inherits:
544 col = self._inherits[table]
545 cols = intersect(self._inherit_fields.keys(), fields)
548 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
556 record.update(res3[record[col]])
557 if col not in fields:
560 # all fields which need to be post-processed by a simple function (symbol_get)
561 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
563 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
564 # to get the _symbol_get in each occurence
566 for f in fields_post:
567 r[f] = self.columns[f]._symbol_get(r[f])
568 ids = map(lambda x: x['id'], res)
570 # all non inherited fields for which the attribute whose name is in load is False
571 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
572 for f in fields_post:
573 # get the value of that field for all records/ids
574 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
576 record[f] = res2[record['id']]
580 account_analytic_account_summary_user()
582 class account_analytic_account_summary_month(osv.osv):
583 _name = "account_analytic_analysis.summary.month"
584 _description = "Hours summary by month"
589 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
591 account_obj = self.pool.get('account.analytic.account')
592 account_ids = [int(str(int(x))[:-6]) for x in ids]
593 month_ids = [int(str(int(x))[-6:]) for x in ids]
594 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
595 month_set = ','.join([str(x) for x in month_ids])
597 acc_set = ','.join([str(x) for x in account_ids2])
598 cr.execute('SELECT id, unit_amount ' \
599 'FROM account_analytic_analysis_summary_month ' \
600 'WHERE account_id in (%s) ' \
601 'AND month_id in (%s) ' % \
602 (acc_set, month_set))
603 for sum_id, unit_amount in cr.fetchall():
604 res[sum_id] = unit_amount
606 res.setdefault(obj_id, 0.0)
607 for child_id in account_obj.search(cr, uid,
608 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
609 if child_id != int(str(int(obj_id))[:-6]):
610 res[obj_id] += res.get(int(child_id * 1000000 + int(obj_id)), 0.0)
612 res[id] = round(res.get(id, 0.0), 2)
616 'account_id': fields.many2one('account.analytic.account', 'Analytic Account',
618 'unit_amount': fields.function(_unit_amount, method=True, type='float',
619 string='Total Time'),
620 'month': fields.char('Month', size=25, readonly=True),
624 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
625 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
627 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
628 'd.account_id AS account_id, ' \
629 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
630 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
631 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
638 'a.id AS account_id, ' \
639 'l.month AS month ' \
642 'DATE_TRUNC(\'month\', l.date) AS month ' \
643 'FROM account_analytic_line AS l, ' \
644 'account_analytic_journal AS j ' \
645 'WHERE j.type = \'general\' ' \
646 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
648 'account_analytic_account AS a ' \
649 'GROUP BY l.month, a.id ' \
651 'GROUP BY d2.account_id, d2.month ' \
655 'l.account_id AS account_id, ' \
656 'DATE_TRUNC(\'month\', l.date) AS month, ' \
657 'SUM(l.unit_amount) AS unit_amount ' \
658 'FROM account_analytic_line AS l, ' \
659 'account_analytic_journal AS j ' \
660 'WHERE j.type = \'general\' ' \
661 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
664 'd.account_id = l.account_id ' \
665 'AND d.month = l.month' \
667 'GROUP BY d.month, d.account_id ' \
670 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
677 fields = self._columns.keys()
679 # construct a clause for the rules :
680 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
682 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
683 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
687 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
688 for i in range(0, len(ids), cr.IN_MAX):
689 sub_ids = ids[i:i+cr.IN_MAX]
691 cr.execute('select %s from \"%s\" where id in (%s) ' \
692 'and account_id in (%s) ' \
693 'and month_id in (%s) and %s order by %s' % \
694 (','.join(fields_pre2 + ['id']), self._table,
695 ','.join([str(x) for x in sub_ids]),
696 ','.join([str(x)[:-6] for x in sub_ids]),
697 ','.join([str(x)[-6:] for x in sub_ids]), d1,
699 if not cr.rowcount == len({}.fromkeys(sub_ids)):
700 raise except_orm(_('AccessError'),
701 _('You try to bypass an access rule (Document type: %s).') % self._description)
703 cr.execute('select %s from \"%s\" where id in (%s) ' \
704 'and account_id in (%s) ' \
705 'and month_id in (%s) order by %s' % \
706 (','.join(fields_pre2 + ['id']), self._table,
707 ','.join([str(x) for x in sub_ids]),
708 ','.join([str(x)[:-6] for x in sub_ids]),
709 ','.join([str(x)[-6:] for x in sub_ids]),
711 res.extend(cr.dictfetchall())
713 res = map(lambda x: {'id': x}, ids)
716 if self._columns[f].translate:
717 ids = map(lambda x: x['id'], res)
718 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
720 r[f] = res_trans.get(r['id'], False) or r[f]
722 for table in self._inherits:
723 col = self._inherits[table]
724 cols = intersect(self._inherit_fields.keys(), fields)
727 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
735 record.update(res3[record[col]])
736 if col not in fields:
739 # all fields which need to be post-processed by a simple function (symbol_get)
740 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
742 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
743 # to get the _symbol_get in each occurence
745 for f in fields_post:
746 r[f] = self.columns[f]._symbol_get(r[f])
747 ids = map(lambda x: x['id'], res)
749 # all non inherited fields for which the attribute whose name is in load is False
750 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
751 for f in fields_post:
752 # get the value of that field for all records/ids
753 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
755 record[f] = res2[record['id']]
759 account_analytic_account_summary_month()
762 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: