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):
36 for obj in obj.browse(cr, user, ids, context=context):
39 children = obj.pool.get('report_account_analytic.planning')._child_compute(cr, user, [obj.user_id.id], '', [])
40 for u_id in children.get(obj.user_id.id, []):
42 list_ids.append(obj.user_id.id)
43 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)
44 for r in obj.pool.get(self._obj)._read_flat(cr, user, ids2, [self._fields_id], context=context, load='_classic_write'):
45 if r[self._fields_id] not in res:
46 res[r[self._fields_id]] = []
47 res[r[self._fields_id]].append( r['id'] )
50 class report_account_analytic_planning(osv.osv):
51 _name = "report_account_analytic.planning"
52 _description = "Planning"
54 def _child_compute(self, cr, uid, ids, name, args, context=None):
57 obj_dept = self.pool.get('hr.department')
58 obj_user = self.pool.get('res.users')
62 cr.execute('SELECT dept.id FROM hr_department AS dept \
63 LEFT JOIN hr_employee AS emp ON dept.manager_id = emp.id \
65 (SELECT emp.id FROM hr_employee \
66 JOIN resource_resource r ON r.id = emp.resource_id WHERE r.user_id=' + str(user_id) + ') ')
67 mgnt_dept_ids = [x[0] for x in cr.fetchall()]
68 ids_dept = obj_dept.search(cr, uid, [('id', 'child_of', mgnt_dept_ids)], context=context)
70 data_dept = obj_dept.read(cr, uid, ids_dept, ['member_ids'], context=context)
71 childs = map(lambda x: x['member_ids'], data_dept)
72 childs = tools.flatten(childs)
73 childs = obj_user.search(cr, uid, [('id', 'in', childs),('active', '=', True)], context=context)
75 childs.remove(user_id)
76 child_ids.extend(tools.flatten(childs))
78 map(set.__setitem__, child_ids, [])
79 child_ids = set.keys()
80 result[user_id] = child_ids
83 def _get_total_planned(self, cr, uid, ids, name, args, context=None):
85 for plan in self.browse(cr, uid, ids, context):
87 for p in plan.planning_user_ids:
88 if not p.plan_open : p.plan_open = 0.0
89 if not p.plan_tasks : p.plan_tasks = 0.0
90 plan_hrs = plan_hrs + p.plan_open + p.plan_tasks
91 result[plan.id] = plan_hrs
94 def _get_total_free(self, cr, uid, ids, name, args, context=None):
96 for plan in self.browse(cr, uid, ids, context):
98 for p in plan.planning_user_ids:
100 total_free = total_free + p.free
101 result[plan.id] = total_free
104 def _check_planning_responsible(self, cr, uid, ids, context=None):
107 for obj_plan in self.browse(cr, uid, ids, context=context):
109 SELECT id FROM report_account_analytic_planning plan
110 WHERE ( (%(date_from)s BETWEEN date_from AND date_to)
111 OR (%(date_to)s BETWEEN date_from AND date_to)
112 OR (%(date_from)s < date_from AND %(date_to)s > date_to)
113 ) AND user_id = %(uid)s AND id <> %(id)s""",
114 {"date_from": obj_plan.date_from,
115 "date_to": obj_plan.date_to,
116 "uid": obj_plan.user_id.id,
126 'name': fields.char('Planning Name', required=True, size=32, states={'done':[('readonly', True)]}),
127 'code': fields.char('Code', size=32, states={'done':[('readonly', True)]}),
128 'user_id': fields.many2one('res.users', 'Responsible', required=True, states={'done':[('readonly', True)]}),
129 'date_from': fields.date('Start Date', required=True, states={'done':[('readonly', True)]}),
130 'date_to':fields.date('End Date', required=True, states={'done':[('readonly', True)]}),
131 'line_ids': fields.one2many('report_account_analytic.planning.line', 'planning_id', 'Planning lines', states={'done':[('readonly', True)]}),
132 'stat_ids': fields.one2many('report_account_analytic.planning.stat', 'planning_id', 'Planning analysis', readonly=True),
133 'state': fields.selection([('draft', 'Draft'), ('open', 'Open'), ('done', 'Done'), ('cancel', 'Cancelled')], 'Status', required=True),
134 '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'),
135 'planning_user_ids': one2many_mod3('report_account_analytic.planning.user', 'planning_id', 'Planning By User'),
136 'planning_account': fields.one2many('report_account_analytic.planning.account', 'planning_id', 'Planning By Account'),
137 'total_planned': fields.function(_get_total_planned, method=True, string='Total Planned'),
138 'total_free': fields.function(_get_total_free, method=True, string='Total Free'),
141 'date_from': time.strftime('%Y-%m-01'),
142 'date_to': (datetime.now()+relativedelta(months=1, day=1, days=-1)).strftime('%Y-%m-%d'),
143 'user_id': lambda self, cr, uid, c: uid,
147 _order = 'date_from desc'
150 (_check_planning_responsible, 'Invalid planning ! Planning dates can\'t overlap for the same responsible. ', ['user_id'])
153 def action_open(self, cr, uid, id, context=None):
156 self.write(cr, uid, id, {'state' : 'open'}, context=context)
159 def action_cancel(self, cr, uid, id, context=None):
162 self.write(cr, uid, id, {'state' : 'cancel'}, context=context)
165 def action_draft(self, cr, uid, id, context=None):
168 self.write(cr, uid, id, {'state' : 'draft'}, context=context)
171 def action_done(self, cr, uid, id, context=None):
174 self.write(cr, uid, id, {'state' : 'done'}, context=context)
177 report_account_analytic_planning()
179 class report_account_analytic_planning_line(osv.osv):
180 _name = "report_account_analytic.planning.line"
181 _description = "Planning Line"
182 _rec_name = 'user_id'
184 def name_get(self, cr, uid, ids, context=None):
189 reads = self.read(cr, uid, ids, ['user_id', 'planning_id', 'note'], context)
192 name = '['+record['planning_id'][1]
193 if record['user_id']:
194 name += " - " +record['user_id'][1]+'] '
198 name += record['note']
199 res.append((record['id'], name))
202 def _amount_base_uom(self, cr, uid, ids, name, args, context=None):
205 users_obj = self.pool.get('res.users')
207 tm = users_obj.browse(cr, uid, uid, context).company_id.planning_time_mode_id
212 for line in self.browse(cr, uid, ids, context):
213 result[line.id] = line.amount / line.amount_unit.factor * div
217 'account_id': fields.many2one('account.analytic.account', 'Analytic account'),
218 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning', required=True, ondelete='cascade'),
219 'user_id': fields.many2one('res.users', 'User'),
220 'amount': fields.float('Quantity', required=True),
221 'amount_unit': fields.many2one('product.uom', 'Qty UoM', required=True),
222 'note': fields.text('Note', size=64),
223 'amount_in_base_uom': fields.function(_amount_base_uom, method=True, string='Quantity in base uom', store=True),
224 'task_ids': fields.one2many('project.task', 'planning_line_id', 'Planning Tasks'),
226 _order = 'user_id, account_id'
228 report_account_analytic_planning_line()
230 class account_analytic_account(osv.osv):
231 _name = 'account.analytic.account'
232 _inherit = 'account.analytic.account'
234 'planning_ids': fields.one2many('report_account_analytic.planning.line', 'account_id', 'Plannings'),
237 account_analytic_account()
239 class project_task(osv.osv):
240 _name = "project.task"
241 _inherit = "project.task"
243 'planning_line_id': fields.many2one('report_account_analytic.planning.line', 'Planning', ondelete='cascade'),
246 def search(self, cr, user, args, offset=0, limit=None, order=None, context=None, count=False):
249 if not context.get('planning', False):
250 return super(project_task,self).search(cr, user, args, offset, limit, order, context)
251 cr.execute(" SELECT t.id, t.name \
252 from project_task t \
253 join report_account_analytic_planning_line l on (l.id = t.planning_line_id )\
254 where l.planning_id=%s",(context.get('planning'),))
255 ids = map(lambda x: x[0], cr.fetchall())
260 class report_account_analytic_planning_user(osv.osv):
261 _name = "report_account_analytic.planning.user"
262 _description = "Planning by User"
263 _rec_name = 'user_id'
266 def _get_tasks(self, cr, uid, ids, name, args, context=None):
269 users_obj = self.pool.get('res.users')
271 tm = users_obj.browse(cr, uid, uid, context=context).company_id.project_time_mode_id
276 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
277 if tm2 and tm2.factor:
281 for line in self.browse(cr, uid, ids, context):
283 cr.execute("""select COALESCE(sum(tasks.remaining_hours),0) from project_task tasks \
284 where tasks.planning_line_id IN (select id from report_account_analytic_planning_line\
285 where planning_id = %s and user_id=%s)""", (line.planning_id.id, line.user_id.id,))
287 result[line.id] = cr.fetchall()[0][0] / div * div2
292 def _get_free(self, cr, uid, ids, name, args, context=None):
296 for line in self.browse(cr, uid, ids, context):
298 result[line.id] = line.planning_id.business_days - line.plan_tasks - line.plan_open - line.holiday
300 result[line.id] = 0.0
303 def _get_timesheets(self, cr, uid, ids, name, args, context=None):
306 users_obj = self.pool.get('res.users')
308 tm2 = users_obj.browse(cr, uid, uid, context).company_id.planning_time_mode_id
309 if tm2 and tm2.factor:
313 for line in self.browse(cr, uid, ids, context):
316 SELECT sum(unit_amount/uom.factor) FROM account_analytic_line acc
317 LEFT JOIN product_uom uom ON (uom.id = acc.product_uom_id)
318 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 ))
320 result[line.id] = res[0][0] and res[0][0] * div2 or False
326 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning'),
327 'user_id': fields.many2one('res.users', 'User', readonly=True),
328 'tasks': fields.function(_get_tasks, method=True, 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.'),
329 '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.'),
330 'free': fields.function(_get_free, method=True, string='Unallocated Time', readonly=True, help='Computed as \
331 Business Days - (Time Allocation of Tasks + Time Allocation without Tasks + Holiday Leaves)'),
332 '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.'),
333 '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.'),
334 'timesheet': fields.function(_get_timesheets, method=True, 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.'),
338 cr.execute(""" CREATE OR REPLACE VIEW report_account_analytic_planning_user AS (
340 planning.id AS planning_id,
341 (1000*(planning.id) + users.id)::integer AS id,
342 planning.business_days,
344 (SELECT sum(line1.amount_in_base_uom)
345 FROM report_account_analytic_planning_line line1
348 FROM project_task task
349 WHERE task.planning_line_id = line1.id
351 AND line1.user_id = users.id
352 AND line1.planning_id = planning.id
354 (SELECT SUM(line1.amount_in_base_uom)
355 FROM report_account_analytic_planning_line line1
358 FROM project_task task
359 WHERE task.planning_line_id = line1.id
361 AND line1.user_id = users.id
362 AND line1.planning_id = planning.id
364 (SELECT -(SUM(holidays.number_of_days))
365 FROM hr_holidays holidays
366 WHERE holidays.employee_id IN
369 FROM hr_employee emp, resource_resource res WHERE emp.resource_id = res.id and res.user_id = users.id
371 AND holidays.state IN ('validate')
372 AND holidays.type = 'remove'
373 AND holidays.date_from >= planning.date_from
374 AND holidays.date_to <= planning.date_to
377 FROM report_account_analytic_planning planning
378 LEFT JOIN report_account_analytic_planning_line line ON (line.planning_id = planning.id), res_users users
379 GROUP BY planning.id, planning.business_days, users.id, planning.date_from, planning.date_to
384 planning.id AS planning_id,
385 (1000*(planning.id) - 1)::integer AS id,
386 planning.business_days,
388 (SELECT SUM(line1.amount_in_base_uom)
389 FROM report_account_analytic_planning_line line1
390 WHERE (SELECT COUNT(1) FROM project_task task WHERE task.planning_line_id = line1.id) > 0
391 AND line1.user_id IS NULL
393 (SELECT SUM(line1.amount_in_base_uom)
394 FROM report_account_analytic_planning_line line1
395 WHERE (SELECT COUNT(1) FROM project_task task WHERE task.planning_line_id = line1.id) = 0
396 AND line1.user_id IS NULL
399 FROM report_account_analytic_planning planning
400 INNER JOIN report_account_analytic_planning_line line ON line.planning_id = planning.id
401 AND line.user_id IS NULL
402 GROUP BY planning.id, planning.business_days, line.user_id, planning.date_from, planning.date_to
406 report_account_analytic_planning_user()
408 class report_account_analytic_planning_account(osv.osv):
409 _name = "report_account_analytic.planning.account"
410 _description = "Planning by Account"
411 _rec_name = 'account_id'
414 def _get_tasks(self, cr, uid, ids, name, args, context=None):
417 users_obj = self.pool.get('res.users')
419 tm = users_obj.browse(cr, uid, uid, context).company_id.project_time_mode_id
424 tm2 = users_obj.browse(cr, uid, uid, context).company_id.planning_time_mode_id
425 if tm2 and tm2.factor:
429 for line in self.browse(cr, uid, ids, context):
431 SELECT COALESCE(sum(tasks.remaining_hours),0)
432 FROM project_task tasks
433 WHERE tasks.planning_line_id IN (
435 FROM report_account_analytic_planning_line
436 WHERE planning_id = %s AND account_id=%s
437 )""", (line.planning_id.id,line.account_id.id ))
438 result[line.id] = cr.fetchall()[0][0] / div * div2
441 def _get_timesheets(self, cr, uid, ids, name, args, context=None):
444 users_obj = self.pool.get('res.users')
446 tm2 = users_obj.browse(cr, uid, uid, context).company_id.planning_time_mode_id
447 if tm2 and tm2.factor:
451 for line in self.browse(cr, uid, ids, context):
453 SELECT SUM(unit_amount/uom.factor) FROM account_analytic_line acc
454 LEFT JOIN product_uom uom ON (uom.id = acc.product_uom_id)
455 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.id, ))
456 res = cr.fetchall()[0][0]
458 result[line.id] = res * div2
464 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning'),
465 'account_id': fields.many2one('account.analytic.account', 'Analytic account', readonly=True),
466 'tasks': fields.function(_get_tasks, method=True, 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.'),
467 '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.'),
468 '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.'),
469 'timesheet': fields.function(_get_timesheets, method=True, 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.'),
473 cr.execute(""" CREATE OR REPLACE VIEW report_account_analytic_planning_account AS (
476 l.account_id AS account_id,
477 SUM(l.amount) AS quantity,
478 l.planning_id AS planning_id,
479 ( SELECT SUM(line1.amount_in_base_uom)
480 FROM report_account_analytic_planning_line line1
483 FROM project_task task
484 WHERE task.planning_line_id = line1.id
486 AND l.account_id = line1.account_id
487 AND l.planning_id = line1.planning_id
489 ( SELECT SUM(line1.amount_in_base_uom)
490 FROM report_account_analytic_planning_line line1
493 FROM project_task task
494 WHERE task.planning_line_id = line1.id
496 AND l.account_id = line1.account_id
497 AND planning.id = line1.planning_id
499 FROM report_account_analytic_planning_line l
500 INNER JOIN report_account_analytic_planning planning ON planning.id=l.planning_id
501 GROUP BY l.account_id, l.planning_id, planning.date_from, planning.date_to, planning.id
505 report_account_analytic_planning_account()
507 class report_account_analytic_planning_stat(osv.osv):
508 _name = "report_account_analytic.planning.stat"
509 _description = "Planning stat"
510 _rec_name = 'user_id'
513 _order = 'planning_id,user_id'
515 def _sum_amount_real(self, cr, uid, ids, name, args, context=None):
518 users_obj = self.pool.get('res.users')
520 tm2 = users_obj.browse(cr, uid, uid, context).company_id.planning_time_mode_id
521 if tm2 and tm2.factor:
525 for line in self.browse(cr, uid, ids, context):
527 cr.execute('''SELECT sum(acc.unit_amount/uom.factor) FROM account_analytic_line acc
528 LEFT JOIN product_uom uom ON (uom.id = acc.product_uom_id)
529 WHERE user_id=%s and account_id=%s and date>=%s and date<=%s''', (line.user_id.id, line.account_id.id, line.planning_id.date_from, line.planning_id.date_to))
531 cr.execute('SELECT sum(unit_amount) FROM account_analytic_line WHERE account_id=%s AND date>=%s AND date<=%s', (line.account_id.id, line.planning_id.date_from, line.planning_id.date_to))
535 result[line.id] = sum[0] * div2
538 def _sum_amount_tasks(self, cr, uid, ids, name, args, context=None):
541 users_obj = self.pool.get('res.users')
543 tm = users_obj.browse(cr, uid, uid, context).company_id.project_time_mode_id
548 tm2 = users_obj.browse(cr, uid, uid, context).company_id.planning_time_mode_id
549 if tm2 and tm2.factor:
553 for line in self.browse(cr, uid, ids, context):
556 where = 'user_id=' + str(line.user_id.id) + ' and '
563 project_id IN (select id from project_project where analytic_account_id=%s) AND
567 line.planning_id.date_from,
568 line.planning_id.date_to)
572 result[line.id] = sum[0] /div * div2
576 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning'),
577 'user_id': fields.many2one('res.users', 'User'),
578 'manager_id': fields.many2one('res.users', 'Manager'),
579 'account_id': fields.many2one('account.analytic.account', 'Account'),
580 'sum_amount': fields.float('Planned Days', required=True),
581 'sum_amount_real': fields.function(_sum_amount_real, method=True, string='Timesheet'),
582 'sum_amount_tasks': fields.function(_sum_amount_tasks, method=True, string='Tasks'),
587 create or replace view report_account_analytic_planning_stat as (
590 l.user_id as user_id,
591 a.user_id as manager_id,
592 l.account_id as account_id,
593 sum(l.amount/u.factor) as sum_amount,
596 report_account_analytic_planning_line l
598 report_account_analytic_planning a on (a.id = l.planning_id)
600 product_uom u on (l.amount_unit = u.id)
602 l.planning_id, l.user_id, l.account_id, a.user_id
606 report_account_analytic_planning_stat()
608 class res_company(osv.osv):
609 _inherit = 'res.company'
611 'planning_time_mode_id': fields.many2one('product.uom', 'Planning Time Unit',
612 help='This will set the unit of measure used in plannings.',
617 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: