1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2010 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 Affero General Public License as
10 # published by the Free Software Foundation, either version 3 of the
11 # License, or (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 Affero General Public License for more details.
18 # You should have received a copy of the GNU Affero General Public License
19 # along with this program. If not, see <http://www.gnu.org/licenses/>.
21 ##############################################################################
24 from datetime import datetime
25 from dateutil.relativedelta import relativedelta
27 from osv import fields, osv
31 class one2many_mod3(fields.one2many):
32 def get(self, cr, obj, ids, name, user=None, offset=0, context=None, values=None):
34 for obj in obj.browse(cr, user, ids, context=context):
37 children = obj.pool.get('report_account_analytic.planning')._child_compute(cr, user, [obj.user_id.id], '', [])
38 for u_id in children.get(obj.user_id.id, []):
40 list_ids.append(obj.user_id.id)
41 ids2 = obj.pool.get(self._obj).search(cr, user, ['&',(self._fields_id,'=',obj.id),'|',('user_id','in',list_ids),('user_id','=',False)], limit=self._limit)
42 for r in obj.pool.get(self._obj)._read_flat(cr, user, ids2, [self._fields_id], context=context, load='_classic_write'):
43 if r[self._fields_id] not in res:
44 res[r[self._fields_id]] = []
45 res[r[self._fields_id]].append( r['id'] )
48 class report_account_analytic_planning(osv.osv):
49 _name = "report_account_analytic.planning"
50 _description = "Planning"
52 def _child_compute(self, cr, uid, ids, name, args, context=None):
53 obj_dept = self.pool.get('hr.department')
54 obj_user = self.pool.get('res.users')
58 cr.execute('SELECT dept.id FROM hr_department AS dept \
59 LEFT JOIN hr_employee AS emp ON dept.manager_id = emp.id \
61 (SELECT emp.id FROM hr_employee \
62 JOIN resource_resource r ON r.id = emp.resource_id WHERE r.user_id=' + str(user_id) + ') ')
63 mgnt_dept_ids = [x[0] for x in cr.fetchall()]
64 ids_dept = obj_dept.search(cr, uid, [('id', 'child_of', mgnt_dept_ids)], context=context)
66 data_dept = obj_dept.read(cr, uid, ids_dept, ['member_ids'], context=context)
67 children = map(lambda x: x['member_ids'], data_dept)
68 children = tools.flatten(children)
69 children = obj_user.search(cr, uid, [('id', 'in', children),('active', '=', True)], context=context)
70 if user_id in children:
71 children.remove(user_id)
72 child_ids.extend(tools.flatten(children))
74 map(set.__setitem__, child_ids, [])
75 child_ids = set.keys()
76 result[user_id] = child_ids
79 def _get_total_planned(self, cr, uid, ids, name, args, context=None):
81 for plan in self.browse(cr, uid, ids, context=context):
83 for p in plan.planning_user_ids:
84 if not p.plan_open : p.plan_open = 0.0
85 if not p.plan_tasks : p.plan_tasks = 0.0
86 plan_hrs = plan_hrs + p.plan_open + p.plan_tasks
87 result[plan.id] = plan_hrs
90 def _get_total_free(self, cr, uid, ids, name, args, context=None):
92 for plan in self.browse(cr, uid, ids, context=context):
94 for p in plan.planning_user_ids:
96 total_free = total_free + p.free
97 result[plan.id] = total_free
100 def _check_planning_responsible(self, cr, uid, ids, context=None):
101 for obj_plan in self.browse(cr, uid, ids, context=context):
103 SELECT id FROM report_account_analytic_planning plan
104 WHERE ( (%(date_from)s BETWEEN date_from AND date_to)
105 OR (%(date_to)s BETWEEN date_from AND date_to)
106 OR (%(date_from)s < date_from AND %(date_to)s > date_to)
107 ) AND user_id = %(uid)s AND id <> %(id)s""",
108 {"date_from": obj_plan.date_from,
109 "date_to": obj_plan.date_to,
110 "uid": obj_plan.user_id.id,
120 'name': fields.char('Planning Name', required=True, size=32, states={'done':[('readonly', True)]}),
121 'code': fields.char('Code', size=32, states={'done':[('readonly', True)]}),
122 'user_id': fields.many2one('res.users', 'Responsible', required=True, states={'done':[('readonly', True)]}),
123 'date_from': fields.date('Start Date', required=True, states={'done':[('readonly', True)]}),
124 'date_to':fields.date('End Date', required=True, states={'done':[('readonly', True)]}),
125 'line_ids': fields.one2many('report_account_analytic.planning.line', 'planning_id', 'Planning lines', states={'done':[('readonly', True)]}),
126 'stat_ids': fields.one2many('report_account_analytic.planning.stat', 'planning_id', 'Planning analysis', readonly=True),
127 'state': fields.selection([('draft', 'Draft'), ('open', 'Open'), ('done', 'Done'), ('cancel', 'Cancelled')], 'Status', required=True),
128 'business_days': fields.integer('Business Days', required=True, states={'done':[('readonly', True)]}, help='Set here the number of working days within this planning for one person full time'),
129 'planning_user_ids': one2many_mod3('report_account_analytic.planning.user', 'planning_id', 'Planning By User'),
130 'planning_account': fields.one2many('report_account_analytic.planning.account', 'planning_id', 'Planning By Account'),
131 'total_planned': fields.function(_get_total_planned, string='Total Planned'),
132 'total_free': fields.function(_get_total_free, string='Total Free'),
135 'date_from': lambda *a: time.strftime('%Y-%m-01'),
136 'date_to': lambda *a: (datetime.now()+relativedelta(months=1, day=1, days=-1)).strftime('%Y-%m-%d'),
137 'user_id': lambda self, cr, uid, c: uid,
141 _order = 'date_from desc'
144 (_check_planning_responsible, 'Invalid planning ! Planning dates can\'t overlap for the same responsible. ', ['user_id'])
147 def action_open(self, cr, uid, id, context=None):
148 self.write(cr, uid, id, {'state' : 'open'}, context=context)
151 def action_cancel(self, cr, uid, id, context=None):
152 self.write(cr, uid, id, {'state' : 'cancel'}, context=context)
155 def action_draft(self, cr, uid, id, context=None):
156 self.write(cr, uid, id, {'state' : 'draft'}, context=context)
159 def action_done(self, cr, uid, id, context=None):
160 self.write(cr, uid, id, {'state' : 'done'}, context=context)
163 report_account_analytic_planning()
165 class report_account_analytic_planning_line(osv.osv):
166 _name = "report_account_analytic.planning.line"
167 _description = "Planning Line"
168 _rec_name = 'user_id'
170 def name_get(self, cr, uid, ids, context=None):
173 reads = self.read(cr, uid, ids, ['user_id', 'planning_id', 'note'], context=context)
176 name = '['+record['planning_id'][1]
177 if record['user_id']:
178 name += " - " +record['user_id'][1]+'] '
182 name += record['note']
183 res.append((record['id'], name))
186 def _amount_base_uom(self, cr, uid, ids, name, args, context=None):
187 users_obj = self.pool.get('res.users')
189 tm = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
194 for line in self.browse(cr, uid, ids, context=context):
195 result[line.id] = line.amount / line.amount_unit.factor * div
199 'account_id': fields.many2one('account.analytic.account', 'Analytic account', select=True),
200 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning', required=True, ondelete='cascade'),
201 'user_id': fields.many2one('res.users', 'User', select=True),
202 'amount': fields.float('Quantity', required=True),
203 'amount_unit': fields.many2one('product.uom', 'Qty UoM', required=True),
204 'note': fields.text('Note', size=64),
205 'amount_in_base_uom': fields.function(_amount_base_uom, string='Quantity in base uom', store=True),
206 'task_ids': fields.one2many('project.task', 'planning_line_id', 'Planning Tasks'),
208 _order = 'user_id, account_id'
210 report_account_analytic_planning_line()
212 class account_analytic_account(osv.osv):
213 _name = 'account.analytic.account'
214 _inherit = 'account.analytic.account'
216 'planning_ids': fields.one2many('report_account_analytic.planning.line', 'account_id', 'Plannings'),
219 account_analytic_account()
221 class project_task(osv.osv):
222 _name = "project.task"
223 _inherit = "project.task"
225 'planning_line_id': fields.many2one('report_account_analytic.planning.line', 'Planning', ondelete='cascade'),
228 def search(self, cr, user, args, offset=0, limit=None, order=None, context=None, count=False):
231 if not context.get('planning', False):
232 return super(project_task,self).search(cr, user, args, offset, limit, order, context)
233 cr.execute(" SELECT t.id, t.name \
234 from project_task t \
235 join report_account_analytic_planning_line l on (l.id = t.planning_line_id )\
236 where l.planning_id=%s",(context.get('planning'),))
237 ids = map(lambda x: x[0], cr.fetchall())
242 class report_account_analytic_planning_user(osv.osv):
243 _name = "report_account_analytic.planning.user"
244 _description = "Planning by User"
245 _rec_name = 'user_id'
248 def _get_tasks(self, cr, uid, ids, name, args, context=None):
249 users_obj = self.pool.get('res.users')
251 tm = users_obj.browse(cr, uid, uid, context=context).company_id.project_time_mode_id
256 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
257 if tm2 and tm2.factor:
261 for line in self.browse(cr, uid, ids, context=context):
263 cr.execute("""select COALESCE(sum(tasks.remaining_hours),0) from project_task tasks \
264 where tasks.planning_line_id IN (select id from report_account_analytic_planning_line\
265 where planning_id = %s and user_id=%s)""", (line.planning_id.id, line.user_id.id,))
267 result[line.id] = cr.fetchall()[0][0] / div * div2
272 def _get_free(self, cr, uid, ids, name, args, context=None):
274 for line in self.browse(cr, uid, ids, context=context):
276 result[line.id] = line.planning_id.business_days - line.plan_tasks - line.plan_open - line.holiday
278 result[line.id] = 0.0
281 def _get_timesheets(self, cr, uid, ids, name, args, context=None):
282 users_obj = self.pool.get('res.users')
284 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
285 if tm2 and tm2.factor:
289 for line in self.browse(cr, uid, ids, context=context):
292 SELECT sum(unit_amount/uom.factor) FROM account_analytic_line acc
293 LEFT JOIN product_uom uom ON (uom.id = acc.product_uom_id)
294 WHERE acc.date>=%s and acc.date<=%s and acc.user_id=%s""", (line.planning_id.date_from, line.planning_id.date_to, line.user_id.id ))
296 result[line.id] = res[0][0] and res[0][0] * div2 or False
302 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning'),
303 'user_id': fields.many2one('res.users', 'User', readonly=True),
304 'tasks': fields.function(_get_tasks, string='Remaining Tasks', help='This value is given by the sum of work remaining to do on the task for this planning, expressed in days.'),
305 'plan_tasks': fields.float('Time Planned on Tasks', readonly=True, help='This value is given by the sum of time allocation with task(s) linked, expressed in days.'),
306 'free': fields.function(_get_free, string='Unallocated Time', readonly=True, help='Computed as \
307 Business Days - (Time Allocation of Tasks + Time Allocation without Tasks + Holiday Leaves)'),
308 'plan_open': fields.float('Time Allocation without Tasks', readonly=True,help='This value is given by the sum of time allocation without task(s) linked, expressed in days.'),
309 'holiday': fields.float('Leaves',help='This value is given by the total of validated leaves into the \'Date From\' and \'Date To\' of the planning.'),
310 'timesheet': fields.function(_get_timesheets, string='Timesheet', help='This value is given by the sum of all work encoded in the timesheet(s) between the \'Date From\' and \'Date To\' of the planning.'),
314 cr.execute(""" CREATE OR REPLACE VIEW report_account_analytic_planning_user AS (
316 planning.id AS planning_id,
317 (1000*(planning.id) + users.id)::integer AS id,
318 planning.business_days,
320 (SELECT sum(line1.amount_in_base_uom)
321 FROM report_account_analytic_planning_line line1
324 FROM project_task task
325 WHERE task.planning_line_id = line1.id
327 AND line1.user_id = users.id
328 AND line1.planning_id = planning.id
330 (SELECT SUM(line1.amount_in_base_uom)
331 FROM report_account_analytic_planning_line line1
334 FROM project_task task
335 WHERE task.planning_line_id = line1.id
337 AND line1.user_id = users.id
338 AND line1.planning_id = planning.id
340 (SELECT -(SUM(holidays.number_of_days))
341 FROM hr_holidays holidays
342 WHERE holidays.employee_id IN
345 FROM hr_employee emp, resource_resource res WHERE emp.resource_id = res.id and res.user_id = users.id
347 AND holidays.state IN ('validate')
348 AND holidays.type = 'remove'
349 AND holidays.date_from >= planning.date_from
350 AND holidays.date_to <= planning.date_to
353 FROM report_account_analytic_planning planning
354 LEFT JOIN report_account_analytic_planning_line line ON (line.planning_id = planning.id), res_users users
355 GROUP BY planning.id, planning.business_days, users.id, planning.date_from, planning.date_to
360 planning.id AS planning_id,
361 (1000*(planning.id) - 1)::integer AS id,
362 planning.business_days,
364 (SELECT SUM(line1.amount_in_base_uom)
365 FROM report_account_analytic_planning_line line1
366 WHERE (SELECT COUNT(1) FROM project_task task WHERE task.planning_line_id = line1.id) > 0
367 AND line1.user_id IS NULL
369 (SELECT SUM(line1.amount_in_base_uom)
370 FROM report_account_analytic_planning_line line1
371 WHERE (SELECT COUNT(1) FROM project_task task WHERE task.planning_line_id = line1.id) = 0
372 AND line1.user_id IS NULL
375 FROM report_account_analytic_planning planning
376 INNER JOIN report_account_analytic_planning_line line ON line.planning_id = planning.id
377 AND line.user_id IS NULL
378 GROUP BY planning.id, planning.business_days, line.user_id, planning.date_from, planning.date_to
382 report_account_analytic_planning_user()
384 class report_account_analytic_planning_account(osv.osv):
385 _name = "report_account_analytic.planning.account"
386 _description = "Planning by Account"
387 _rec_name = 'account_id'
390 def _get_tasks(self, cr, uid, ids, name, args, context=None):
391 users_obj = self.pool.get('res.users')
393 tm = users_obj.browse(cr, uid, uid, context=context).company_id.project_time_mode_id
398 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
399 if tm2 and tm2.factor:
403 for line in self.browse(cr, uid, ids, context=context):
405 SELECT COALESCE(sum(tasks.remaining_hours),0)
406 FROM project_task tasks
407 WHERE tasks.planning_line_id IN (
409 FROM report_account_analytic_planning_line
410 WHERE planning_id = %s AND account_id=%s
411 )""", (line.planning_id.id, line.account_id and line.account_id.id or None))
412 result[line.id] = cr.fetchall()[0][0] / div * div2
415 def _get_timesheets(self, cr, uid, ids, name, args, context=None):
416 users_obj = self.pool.get('res.users')
418 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
419 if tm2 and tm2.factor:
423 for line in self.browse(cr, uid, ids, context=context):
425 SELECT SUM(unit_amount/uom.factor) FROM account_analytic_line acc
426 LEFT JOIN product_uom uom ON (uom.id = acc.product_uom_id)
427 WHERE acc.date>=%s and acc.date<=%s and acc.account_id=%s""", (line.planning_id.date_from, line.planning_id.date_to, line.account_id and line.account_id.id or None))
428 res = cr.fetchall()[0][0]
430 result[line.id] = res * div2
436 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning'),
437 'account_id': fields.many2one('account.analytic.account', 'Analytic account', readonly=True),
438 'tasks': fields.function(_get_tasks, string='Remaining Tasks', help='This value is given by the sum of work remaining to do on the task for this planning, expressed in days.'),
439 'plan_tasks': fields.float('Time Allocation of Tasks', readonly=True, help='This value is given by the sum of time allocation with the checkbox \'Assigned in Taks\' set to TRUE expressed in days.'),
440 'plan_open': fields.float('Time Allocation without Tasks', readonly=True, help='This value is given by the sum of time allocation with the checkbox \'Assigned in Taks\' set to FALSE, expressed in days.'),
441 'timesheet': fields.function(_get_timesheets, string='Timesheet', help='This value is given by the sum of all work encoded in the timesheet(s) between the \'Date From\' and \'Date To\' of the planning.'),
445 cr.execute(""" CREATE OR REPLACE VIEW report_account_analytic_planning_account AS (
448 l.account_id AS account_id,
449 SUM(l.amount) AS quantity,
450 l.planning_id AS planning_id,
451 ( SELECT SUM(line1.amount_in_base_uom)
452 FROM report_account_analytic_planning_line line1
455 FROM project_task task
456 WHERE task.planning_line_id = line1.id
458 AND l.account_id = line1.account_id
459 AND l.planning_id = line1.planning_id
461 ( SELECT SUM(line1.amount_in_base_uom)
462 FROM report_account_analytic_planning_line line1
465 FROM project_task task
466 WHERE task.planning_line_id = line1.id
468 AND l.account_id = line1.account_id
469 AND planning.id = line1.planning_id
471 FROM report_account_analytic_planning_line l
472 INNER JOIN report_account_analytic_planning planning ON planning.id=l.planning_id
473 GROUP BY l.account_id, l.planning_id, planning.date_from, planning.date_to, planning.id
477 report_account_analytic_planning_account()
479 class report_account_analytic_planning_stat(osv.osv):
480 _name = "report_account_analytic.planning.stat"
481 _description = "Planning stat"
482 _rec_name = 'user_id'
485 _order = 'planning_id,user_id'
487 def _sum_amount_real(self, cr, uid, ids, name, args, context=None):
488 users_obj = self.pool.get('res.users')
490 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
491 if tm2 and tm2.factor:
495 for line in self.browse(cr, uid, ids, context=context):
497 cr.execute('''SELECT sum(acc.unit_amount/uom.factor) FROM account_analytic_line acc
498 LEFT JOIN product_uom uom ON (uom.id = acc.product_uom_id)
499 WHERE user_id=%s and account_id=%s and date>=%s and date<=%s''', (line.user_id.id, line.account_id and line.account_id.id or None, line.planning_id.date_from, line.planning_id.date_to))
501 cr.execute('SELECT sum(unit_amount) FROM account_analytic_line WHERE account_id=%s AND date>=%s AND date<=%s', (line.account_id and line.account_id.id or None, line.planning_id.date_from, line.planning_id.date_to))
505 result[line.id] = sum[0] * div2
508 def _sum_amount_tasks(self, cr, uid, ids, name, args, context=None):
509 users_obj = self.pool.get('res.users')
511 tm = users_obj.browse(cr, uid, uid, context=context).company_id.project_time_mode_id
516 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
517 if tm2 and tm2.factor:
521 for line in self.browse(cr, uid, ids, context=context):
524 where = 'user_id=' + str(line.user_id.id) + ' and '
531 project_id IN (select id from project_project where analytic_account_id=%s) AND
534 line.account_id and line.account_id.id or None,
535 line.planning_id.date_from,
536 line.planning_id.date_to)
540 result[line.id] = sum[0] /div * div2
544 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning', select=True),
545 'user_id': fields.many2one('res.users', 'User', select=True),
546 'manager_id': fields.many2one('res.users', 'Manager'),
547 'account_id': fields.many2one('account.analytic.account', 'Account'),
548 'sum_amount': fields.float('Planned Days', required=True),
549 'sum_amount_real': fields.function(_sum_amount_real, string='Timesheet'),
550 'sum_amount_tasks': fields.function(_sum_amount_tasks, string='Tasks'),
555 create or replace view report_account_analytic_planning_stat as (
558 l.user_id as user_id,
559 a.user_id as manager_id,
560 l.account_id as account_id,
561 sum(l.amount/u.factor) as sum_amount,
564 report_account_analytic_planning_line l
566 report_account_analytic_planning a on (a.id = l.planning_id)
568 product_uom u on (l.amount_unit = u.id)
570 l.planning_id, l.user_id, l.account_id, a.user_id
574 report_account_analytic_planning_stat()
576 class res_company(osv.osv):
577 _inherit = 'res.company'
579 'planning_time_mode_id': fields.many2one('product.uom', 'Planning Time Unit',
580 help='This will set the unit of measure used in plannings.',
585 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: