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, 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])])
375 cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
376 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
377 'AND unit_amount <> 0.0')
378 res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
383 def _user(self, cr, uid, ids, name, arg, context=None):
385 cr.execute('SELECT MAX(id) FROM res_users')
386 max_user = cr.fetchone()[0]
388 ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
390 cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
391 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
392 'AND unit_amount <> 0.0')
393 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 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
462 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
464 '(u.account_id * u.max_user) + u."user" AS id, ' \
465 'u.account_id AS account_id, ' \
466 'u."user" AS "user", ' \
467 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
470 'a.id AS account_id, ' \
471 'u1.id AS "user", ' \
472 'MAX(u2.id) AS max_user ' \
474 'res_users AS u1, ' \
475 'res_users AS u2, ' \
476 'account_analytic_account AS a ' \
477 'GROUP BY u1.id, a.id ' \
481 'l.account_id AS account_id, ' \
482 'l.user_id AS "user", ' \
483 'SUM(l.unit_amount) AS unit_amount ' \
484 'FROM account_analytic_line AS l, ' \
485 'account_analytic_journal AS j ' \
486 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
487 'GROUP BY l.account_id, l.user_id ' \
490 'u.account_id = l.account_id ' \
491 'AND u."user" = l."user"' \
493 'GROUP BY u."user", u.account_id, u.max_user' \
496 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
503 fields = self._columns.keys()
505 # construct a clause for the rules :
506 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
508 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
509 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
512 cr.execute('SELECT MAX(id) FROM res_users')
513 max_user = cr.fetchone()[0]
515 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
516 for i in range(0, len(ids), cr.IN_MAX):
517 sub_ids = ids[i:i+cr.IN_MAX]
519 cr.execute('select %s from \"%s\" where id in (%s) ' \
520 'and account_id in (%s) ' \
521 'and "user" in (%s) and %s order by %s' % \
522 (','.join(fields_pre2 + ['id']), self._table,
523 ','.join([str(x) for x in sub_ids]),
524 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
525 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
527 if not cr.rowcount == len({}.fromkeys(sub_ids)):
528 raise except_orm(_('AccessError'),
529 _('You try to bypass an access rule (Document type: %s).') % self._description)
531 cr.execute('select %s from \"%s\" where id in (%s) ' \
532 'and account_id in (%s) ' \
533 'and "user" in (%s) order by %s' % \
534 (','.join(fields_pre2 + ['id']), self._table,
535 ','.join([str(x) for x in sub_ids]),
536 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
537 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
539 res.extend(cr.dictfetchall())
541 res = map(lambda x: {'id': x}, ids)
544 if self._columns[f].translate:
545 ids = map(lambda x: x['id'], res)
546 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
548 r[f] = res_trans.get(r['id'], False) or r[f]
550 for table in self._inherits:
551 col = self._inherits[table]
552 cols = intersect(self._inherit_fields.keys(), fields)
555 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
563 record.update(res3[record[col]])
564 if col not in fields:
567 # all fields which need to be post-processed by a simple function (symbol_get)
568 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
570 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
571 # to get the _symbol_get in each occurence
573 for f in fields_post:
574 r[f] = self.columns[f]._symbol_get(r[f])
575 ids = map(lambda x: x['id'], res)
577 # all non inherited fields for which the attribute whose name is in load is False
578 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
579 for f in fields_post:
580 # get the value of that field for all records/ids
581 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
583 record[f] = res2[record['id']]
587 account_analytic_account_summary_user()
589 class account_analytic_account_summary_month(osv.osv):
590 _name = "account_analytic_analysis.summary.month"
591 _description = "Hours summary by month"
596 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
598 account_obj = self.pool.get('account.analytic.account')
599 account_ids = [int(str(int(x))[:-6]) for x in ids]
600 month_ids = [int(str(int(x))[-6:]) for x in ids]
601 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
602 month_set = ','.join([str(x) for x in month_ids])
604 acc_set = ','.join([str(x) for x in account_ids2])
605 cr.execute('SELECT id, unit_amount ' \
606 'FROM account_analytic_analysis_summary_month ' \
607 'WHERE account_id in (%s) ' \
608 'AND month_id in (%s) ' % \
609 (acc_set, month_set))
610 for sum_id, unit_amount in cr.fetchall():
611 res[sum_id] = unit_amount
613 res.setdefault(obj_id, 0.0)
614 for child_id in account_obj.search(cr, uid,
615 [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
616 if child_id != int(str(int(obj_id))[:-6]):
617 res[obj_id] += res.get(int(child_id * 1000000 + int(str(int(obj_id))[-6:])), 0.0)
619 res[id] = round(res.get(id, 0.0), 2)
623 'account_id': fields.many2one('account.analytic.account', 'Analytic Account',
625 'unit_amount': fields.function(_unit_amount, method=True, type='float',
626 string='Total Time'),
627 'month': fields.char('Month', size=25, readonly=True),
631 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
632 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
634 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
635 'd.account_id AS account_id, ' \
636 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
637 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
638 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
645 'a.id AS account_id, ' \
646 'l.month AS month ' \
649 'DATE_TRUNC(\'month\', l.date) AS month ' \
650 'FROM account_analytic_line AS l, ' \
651 'account_analytic_journal AS j ' \
652 'WHERE j.type = \'general\' ' \
653 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
655 'account_analytic_account AS a ' \
656 'GROUP BY l.month, a.id ' \
658 'GROUP BY d2.account_id, d2.month ' \
662 'l.account_id AS account_id, ' \
663 'DATE_TRUNC(\'month\', l.date) AS month, ' \
664 'SUM(l.unit_amount) AS unit_amount ' \
665 'FROM account_analytic_line AS l, ' \
666 'account_analytic_journal AS j ' \
667 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
668 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
671 'd.account_id = l.account_id ' \
672 'AND d.month = l.month' \
674 'GROUP BY d.month, d.account_id ' \
677 def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
684 fields = self._columns.keys()
686 # construct a clause for the rules :
687 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
689 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
690 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
694 fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
695 for i in range(0, len(ids), cr.IN_MAX):
696 sub_ids = ids[i:i+cr.IN_MAX]
698 cr.execute('select %s from \"%s\" where id in (%s) ' \
699 'and account_id in (%s) ' \
700 'and month_id in (%s) and %s order by %s' % \
701 (','.join(fields_pre2 + ['id']), self._table,
702 ','.join([str(x) for x in sub_ids]),
703 ','.join([str(x)[:-6] for x in sub_ids]),
704 ','.join([str(x)[-6:] for x in sub_ids]), d1,
706 if not cr.rowcount == len({}.fromkeys(sub_ids)):
707 raise except_orm(_('AccessError'),
708 _('You try to bypass an access rule (Document type: %s).') % self._description)
710 cr.execute('select %s from \"%s\" where id in (%s) ' \
711 'and account_id in (%s) ' \
712 'and month_id in (%s) order by %s' % \
713 (','.join(fields_pre2 + ['id']), self._table,
714 ','.join([str(x) for x in sub_ids]),
715 ','.join([str(x)[:-6] for x in sub_ids]),
716 ','.join([str(x)[-6:] for x in sub_ids]),
718 res.extend(cr.dictfetchall())
720 res = map(lambda x: {'id': x}, ids)
723 if self._columns[f].translate:
724 ids = map(lambda x: x['id'], res)
725 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
727 r[f] = res_trans.get(r['id'], False) or r[f]
729 for table in self._inherits:
730 col = self._inherits[table]
731 cols = intersect(self._inherit_fields.keys(), fields)
734 res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
742 record.update(res3[record[col]])
743 if col not in fields:
746 # all fields which need to be post-processed by a simple function (symbol_get)
747 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
749 # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
750 # to get the _symbol_get in each occurence
752 for f in fields_post:
753 r[f] = self.columns[f]._symbol_get(r[f])
754 ids = map(lambda x: x['id'], res)
756 # all non inherited fields for which the attribute whose name is in load is False
757 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
758 for f in fields_post:
759 # get the value of that field for all records/ids
760 res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
762 record[f] = res2[record['id']]
766 account_analytic_account_summary_month()
769 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: