1 # -*- coding: 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, COALESCE(sum(amount),0.0) \
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 COALESCE(sum (product_template.list_price * \
66 account_analytic_line.unit_amount * \
67 ((100-hr_timesheet_invoice_factor.factor)/100)),0.0) \
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, COALESCE(sum(unit_amount),0.0) \
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,COALESCE(SUM(unit_amount),0.0) \
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)
151 for account_id, sum in ff:
152 res[account_id] = round(sum,2)
154 res.setdefault(obj_id, 0.0)
155 for child_id in self.search(cr, uid,
156 [('parent_id', 'child_of', [obj_id])]):
157 if child_id != obj_id:
158 res[obj_id] += res.get(child_id, 0.0)
160 res[id] = round(res.get(id, 0.0),2)
163 def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
165 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
167 acc_set = ",".join(map(str, ids2))
168 cr.execute("""select account_analytic_line.account_id,COALESCE(sum(amount),0.0) \
169 from account_analytic_line \
170 join account_analytic_journal \
171 on account_analytic_line.journal_id = account_analytic_journal.id \
172 where account_analytic_line.account_id IN (%s) \
174 GROUP BY account_analytic_line.account_id"""%acc_set)
175 for account_id, sum in cr.fetchall():
176 res[account_id] = round(sum,2)
178 res.setdefault(obj_id, 0.0)
179 for child_id in self.search(cr, uid,
180 [('parent_id', 'child_of', [obj_id])]):
181 if child_id != obj_id:
182 res[obj_id] += res.get(child_id, 0.0)
184 res[id] = round(res.get(id, 0.0),2)
187 def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
190 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
192 acc_set = ",".join(map(str, ids2))
193 cr.execute("""select account_analytic_line.account_id as account_id, \
194 COALESCE(sum((account_analytic_line.unit_amount * pt.list_price) \
195 - (account_analytic_line.unit_amount * pt.list_price \
196 * hr.factor)),0.0) as somme
197 from account_analytic_line \
198 left join account_analytic_journal \
199 on (account_analytic_line.journal_id = account_analytic_journal.id) \
200 join product_product pp \
201 on (account_analytic_line.product_id = pp.id) \
202 join product_template pt \
203 on (pp.product_tmpl_id = pt.id) \
204 join account_analytic_account a \
205 on (a.id=account_analytic_line.account_id) \
206 join hr_timesheet_invoice_factor hr \
207 on (hr.id=a.to_invoice) \
208 where account_analytic_line.account_id IN (%s) \
209 and a.to_invoice IS NOT NULL \
210 and account_analytic_journal.type in ('purchase','general')
211 GROUP BY account_analytic_line.account_id"""%acc_set)
212 for account_id, sum in cr.fetchall():
213 res2[account_id] = round(sum,2)
216 res.setdefault(obj_id, 0.0)
217 res2.setdefault(obj_id, 0.0)
218 for child_id in self.search(cr, uid,
219 [('parent_id', 'child_of', [obj_id])]):
220 if child_id != obj_id:
221 res[obj_id] += res.get(child_id, 0.0)
222 res[obj_id] += res2.get(child_id, 0.0)
224 # sum both result on account_id
226 res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
229 def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
231 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
233 acc_set = ",".join(map(str, ids2))
234 cr.execute("select account_analytic_line.account_id, max(date) \
235 from account_analytic_line \
236 where account_id IN (%s) \
237 and invoice_id is null \
238 GROUP BY account_analytic_line.account_id" % acc_set)
239 for account_id, sum in cr.fetchall():
240 res[account_id] = sum
242 res.setdefault(obj_id, '')
243 for child_id in self.search(cr, uid,
244 [('parent_id', 'child_of', [obj_id])]):
245 if res[obj_id] < res.get(child_id, ''):
246 res[obj_id] = res.get(child_id, '')
248 res[id] = res.get(id, '')
251 def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
253 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
255 acc_set = ",".join(map(str, ids2))
256 cr.execute ("select account_analytic_line.account_id, \
257 date(max(account_invoice.date_invoice)) \
258 from account_analytic_line \
259 join account_invoice \
260 on account_analytic_line.invoice_id = account_invoice.id \
261 where account_analytic_line.account_id IN (%s) \
262 and account_analytic_line.invoice_id is not null \
263 GROUP BY account_analytic_line.account_id"%acc_set)
264 for account_id, sum in cr.fetchall():
265 res[account_id] = sum
267 res.setdefault(obj_id, '')
268 for child_id in self.search(cr, uid,
269 [('parent_id', 'child_of', [obj_id])]):
270 if res[obj_id] < res.get(child_id, ''):
271 res[obj_id] = res.get(child_id, '')
273 res[id] = res.get(id, '')
276 def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
278 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
280 acc_set = ",".join(map(str, ids2))
281 cr.execute("select account_analytic_line.account_id, max(date) \
282 from account_analytic_line \
283 where account_id IN (%s) \
284 and invoice_id is not null \
285 GROUP BY account_analytic_line.account_id;"%acc_set)
286 for account_id, sum in cr.fetchall():
287 res[account_id] = sum
289 res.setdefault(obj_id, '')
290 for child_id in self.search(cr, uid,
291 [('parent_id', 'child_of', [obj_id])]):
292 if res[obj_id] < res.get(child_id, ''):
293 res[obj_id] = res.get(child_id, '')
295 res[id] = res.get(id, '')
298 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
300 for account in self.browse(cr, uid, ids):
301 if account.quantity_max <> 0:
302 res[account.id] = account.quantity_max - account.hours_quantity
306 res[id] = round(res.get(id, 0.0),2)
309 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
311 for account in self.browse(cr, uid, ids):
312 res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
313 if res[account.id] < 0:
316 res[id] = round(res.get(id, 0.0),2)
319 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
321 for account in self.browse(cr, uid, ids):
322 if account.hours_qtt_invoiced == 0:
325 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
327 res[id] = round(res.get(id, 0.0),2)
330 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
332 for account in self.browse(cr, uid, ids):
333 if account.ca_invoiced == 0:
335 elif account.total_cost <> 0.0:
336 res[account.id] = -(account.real_margin / account.total_cost) * 100
338 res[account.id] = 0.0
340 res[id] = round(res.get(id, 0.0),2)
343 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
345 for account in self.browse(cr, uid, ids):
346 if account.amount_max <> 0:
347 res[account.id] = account.amount_max - account.ca_invoiced
351 res[id] = round(res.get(id, 0.0),2)
354 def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
356 for account in self.browse(cr, uid, ids):
357 res[account.id] = account.ca_invoiced + account.total_cost
359 res[id] = round(res.get(id, 0.0),2)
362 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context={}):
364 for account in self.browse(cr, uid, ids):
365 res[account.id] = account.ca_theorical + account.total_cost
367 res[id] = round(res.get(id, 0.0),2)
370 def _month(self, cr, uid, ids, name, arg, context=None):
373 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
374 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
375 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
376 'AND unit_amount <> 0.0')
377 res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
380 def _user(self, cr, uid, ids, name, arg, context=None):
382 cr.execute('SELECT MAX(id) FROM res_users')
383 max_user = cr.fetchone()[0]
385 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
386 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
387 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
388 'AND unit_amount <> 0.0')
389 res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
393 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced Amount', help="Total customer invoiced amount for this account."),
394 '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."),
395 '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."),
396 '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."),
397 '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'."),
398 '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."),
399 '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."),
400 '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."),
401 '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."),
402 '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."),
403 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining Hours', help="Computed using the formula: Maximum Quantity - Hours Tot."),
404 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining Revenue', help="Computed using the formula: Max Invoice Price - Invoiced Amount."),
405 '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."),
406 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real Margin', help="Computed using the formula: Invoiced Amount - Total Costs."),
407 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical Margin', help="Computed using the formula: Theorial Revenue - Total Costs"),
408 '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."),
409 'month_ids': fields.function(_month, method=True, type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
410 'user_ids': fields.function(_user, method=True, type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
412 account_analytic_account()
414 class account_analytic_account_summary_user(osv.osv):
415 _name = "account_analytic_analysis.summary.user"
416 _description = "Hours summary by user"
421 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
423 account_obj = self.pool.get('account.analytic.account')
424 cr.execute('SELECT MAX(id) FROM res_users')
425 max_user = cr.fetchone()[0]
426 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
427 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
428 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
429 user_set = ','.join([str(x) for x in user_ids])
431 acc_set = ','.join([str(x) for x in account_ids2])
432 cr.execute('SELECT id, unit_amount ' \
433 'FROM account_analytic_analysis_summary_user ' \
434 'WHERE account_id in (%s) ' \
435 'AND "user" in (%s) ' % (acc_set, user_set))
436 for sum_id, unit_amount in cr.fetchall():
437 res[sum_id] = unit_amount
439 res.setdefault(obj_id, 0.0)
440 for child_id in account_obj.search(cr, uid,
441 [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
442 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
443 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)
445 res[id] = round(res.get(id, 0.0), 2)
449 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
450 'unit_amount': fields.function(_unit_amount, method=True, type='float',
451 string='Total Time'),
452 'user' : fields.many2one('res.users', 'User'),
455 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
456 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
458 '(u.account_id * u.max_user) + u."user" AS id, ' \
459 'u.account_id AS account_id, ' \
460 'u."user" AS "user", ' \
461 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
464 'a.id AS account_id, ' \
465 'u1.id AS "user", ' \
466 'MAX(u2.id) AS max_user ' \
468 'res_users AS u1, ' \
469 'res_users AS u2, ' \
470 'account_analytic_account AS a ' \
471 'GROUP BY u1.id, a.id ' \
475 'l.account_id AS account_id, ' \
476 'l.user_id AS "user", ' \
477 'SUM(l.unit_amount) AS unit_amount ' \
478 'FROM account_analytic_line AS l, ' \
479 'account_analytic_journal AS j ' \
480 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
481 'GROUP BY l.account_id, l.user_id ' \
484 'u.account_id = l.account_id ' \
485 'AND u."user" = l."user"' \
487 'GROUP BY u."user", u.account_id, u.max_user' \
490 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
497 fields = self._columns.keys()
499 # construct a clause for the rules :
500 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
502 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
503 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
506 cr.execute('SELECT MAX(id) FROM res_users')
507 max_user = cr.fetchone()[0]
509 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
510 for i in range(0, len(ids), cr.IN_MAX):
511 sub_ids = ids[i:i+cr.IN_MAX]
513 cr.execute('select %s from \"%s\" where id in (%s) ' \
514 'and account_id in (%s) ' \
515 'and "user" in (%s) and %s order by %s' % \
516 (','.join(fields_pre2 + ['id']), self._table,
517 ','.join([str(x) for x in sub_ids]),
518 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
519 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
521 if not cr.rowcount == len({}.fromkeys(sub_ids)):
522 raise except_orm(_('AccessError'),
523 _('You try to bypass an access rule (Document type: %s).') % self._description)
525 cr.execute('select %s from \"%s\" where id in (%s) ' \
526 'and account_id in (%s) ' \
527 'and "user" in (%s) order by %s' % \
528 (','.join(fields_pre2 + ['id']), self._table,
529 ','.join([str(x) for x in sub_ids]),
530 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
531 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
533 res.extend(cr.dictfetchall())
535 res = map(lambda x: {'id': x}, ids)
538 if self._columns[f].translate:
539 ids = map(lambda x: x['id'], res)
540 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
542 r[f] = res_trans.get(r['id'], False) or r[f]
544 for table in self._inherits:
545 col = self._inherits[table]
546 cols = intersect(self._inherit_fields.keys(), fields)
549 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
557 record.update(res3[record[col]])
558 if col not in fields:
561 # all fields which need to be post-processed by a simple function (symbol_get)
562 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
564 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
565 # to get the _symbol_get in each occurence
567 for f in fields_post:
568 r[f] = self.columns[f]._symbol_get(r[f])
569 ids = map(lambda x: x['id'], res)
571 # all non inherited fields for which the attribute whose name is in load is False
572 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
573 for f in fields_post:
574 # get the value of that field for all records/ids
575 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
577 record[f] = res2[record['id']]
581 account_analytic_account_summary_user()
583 class account_analytic_account_summary_month(osv.osv):
584 _name = "account_analytic_analysis.summary.month"
585 _description = "Hours summary by month"
590 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
592 account_obj = self.pool.get('account.analytic.account')
593 account_ids = [int(str(int(x))[:-6]) for x in ids]
594 month_ids = [int(str(int(x))[-6:]) for x in ids]
595 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
596 month_set = ','.join([str(x) for x in month_ids])
598 acc_set = ','.join([str(x) for x in account_ids2])
599 cr.execute('SELECT id, unit_amount ' \
600 'FROM account_analytic_analysis_summary_month ' \
601 'WHERE account_id in (%s) ' \
602 'AND month_id in (%s) ' % \
603 (acc_set, month_set))
604 for sum_id, unit_amount in cr.fetchall():
605 res[sum_id] = unit_amount
607 res.setdefault(obj_id, 0.0)
608 for child_id in account_obj.search(cr, uid,
609 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
610 if child_id != int(str(int(obj_id))[:-6]):
611 res[obj_id] += res.get(int(child_id * 1000000 + int(obj_id)), 0.0)
613 res[id] = round(res.get(id, 0.0), 2)
617 'account_id': fields.many2one('account.analytic.account', 'Analytic Account',
619 'unit_amount': fields.function(_unit_amount, method=True, type='float',
620 string='Total Time'),
621 'month': fields.char('Month', size=25, readonly=True),
625 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
626 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
628 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
629 'd.account_id AS account_id, ' \
630 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
631 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
632 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
639 'a.id AS account_id, ' \
640 'l.month AS month ' \
643 'DATE_TRUNC(\'month\', l.date) AS month ' \
644 'FROM account_analytic_line AS l, ' \
645 'account_analytic_journal AS j ' \
646 'WHERE j.type = \'general\' ' \
647 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
649 'account_analytic_account AS a ' \
650 'GROUP BY l.month, a.id ' \
652 'GROUP BY d2.account_id, d2.month ' \
656 'l.account_id AS account_id, ' \
657 'DATE_TRUNC(\'month\', l.date) AS month, ' \
658 'SUM(l.unit_amount) AS unit_amount ' \
659 'FROM account_analytic_line AS l, ' \
660 'account_analytic_journal AS j ' \
661 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
662 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
665 'd.account_id = l.account_id ' \
666 'AND d.month = l.month' \
668 'GROUP BY d.month, d.account_id ' \
671 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
678 fields = self._columns.keys()
680 # construct a clause for the rules :
681 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
683 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
684 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
688 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
689 for i in range(0, len(ids), cr.IN_MAX):
690 sub_ids = ids[i:i+cr.IN_MAX]
692 cr.execute('select %s from \"%s\" where id in (%s) ' \
693 'and account_id in (%s) ' \
694 'and month_id in (%s) and %s order by %s' % \
695 (','.join(fields_pre2 + ['id']), self._table,
696 ','.join([str(x) for x in sub_ids]),
697 ','.join([str(x)[:-6] for x in sub_ids]),
698 ','.join([str(x)[-6:] for x in sub_ids]), d1,
700 if not cr.rowcount == len({}.fromkeys(sub_ids)):
701 raise except_orm(_('AccessError'),
702 _('You try to bypass an access rule (Document type: %s).') % self._description)
704 cr.execute('select %s from \"%s\" where id in (%s) ' \
705 'and account_id in (%s) ' \
706 'and month_id in (%s) order by %s' % \
707 (','.join(fields_pre2 + ['id']), self._table,
708 ','.join([str(x) for x in sub_ids]),
709 ','.join([str(x)[:-6] for x in sub_ids]),
710 ','.join([str(x)[-6:] for x in sub_ids]),
712 res.extend(cr.dictfetchall())
714 res = map(lambda x: {'id': x}, ids)
717 if self._columns[f].translate:
718 ids = map(lambda x: x['id'], res)
719 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
721 r[f] = res_trans.get(r['id'], False) or r[f]
723 for table in self._inherits:
724 col = self._inherits[table]
725 cols = intersect(self._inherit_fields.keys(), fields)
728 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
736 record.update(res3[record[col]])
737 if col not in fields:
740 # all fields which need to be post-processed by a simple function (symbol_get)
741 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
743 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
744 # to get the _symbol_get in each occurence
746 for f in fields_post:
747 r[f] = self.columns[f]._symbol_get(r[f])
748 ids = map(lambda x: x['id'], res)
750 # all non inherited fields for which the attribute whose name is in load is False
751 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
752 for f in fields_post:
753 # get the value of that field for all records/ids
754 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
756 record[f] = res2[record['id']]
760 account_analytic_account_summary_month()
763 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: