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 emp_to_users(self, cr, uid, ids, context=None):
53 employees = self.pool.get('hr.employee').browse(cr, uid, ids, context=context)
54 user_ids = [e.user_id.id for e in employees if e.user_id]
57 def _child_compute(self, cr, uid, ids, name, args, context=None):
58 obj_dept = self.pool.get('hr.department')
59 obj_user = self.pool.get('res.users')
63 cr.execute("""SELECT dept.id FROM hr_department AS dept
64 LEFT JOIN hr_employee AS emp ON dept.manager_id = emp.id
66 (SELECT emp.id FROM hr_employee
67 JOIN resource_resource r ON r.id = emp.resource_id WHERE r.user_id = %s)
69 mgnt_dept_ids = [x[0] for x in cr.fetchall()]
70 ids_dept = obj_dept.search(cr, uid, [('id', 'child_of', mgnt_dept_ids)], context=context)
72 data_dept = obj_dept.read(cr, uid, ids_dept, ['member_ids'], context=context)
73 emp_children = map(lambda x: x['member_ids'], data_dept)
74 emp_children = tools.flatten(emp_children)
75 children = self.emp_to_users(cr, uid, emp_children, context=context)
76 children = obj_user.search(cr, uid, [('id', 'in', children),('active', '=', True)], context=context)
77 if user_id in children:
78 children.remove(user_id)
79 child_ids = list(set(child_ids + children))
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=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=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):
105 for obj_plan in self.browse(cr, uid, ids, context=context):
107 SELECT id FROM report_account_analytic_planning plan
108 WHERE ( (%(date_from)s BETWEEN date_from AND date_to)
109 OR (%(date_to)s BETWEEN date_from AND date_to)
110 OR (%(date_from)s < date_from AND %(date_to)s > date_to)
111 ) AND user_id = %(uid)s AND id <> %(id)s""",
112 {"date_from": obj_plan.date_from,
113 "date_to": obj_plan.date_to,
114 "uid": obj_plan.user_id.id,
124 'name': fields.char('Planning Name', required=True, size=32, states={'done':[('readonly', True)]}),
125 'code': fields.char('Code', size=32, states={'done':[('readonly', True)]}),
126 'user_id': fields.many2one('res.users', 'Responsible', required=True, states={'done':[('readonly', True)]}),
127 'date_from': fields.date('Start Date', required=True, states={'done':[('readonly', True)]}),
128 'date_to':fields.date('End Date', required=True, states={'done':[('readonly', True)]}),
129 'line_ids': fields.one2many('report_account_analytic.planning.line', 'planning_id', 'Planning lines', states={'done':[('readonly', True)]}),
130 'stat_ids': fields.one2many('report_account_analytic.planning.stat', 'planning_id', 'Planning analysis', readonly=True),
131 'state': fields.selection([('draft', 'Draft'), ('open', 'Open'), ('done', 'Done'), ('cancel', 'Cancelled')], 'Status', required=True),
132 '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'),
133 'planning_user_ids': one2many_mod3('report_account_analytic.planning.user', 'planning_id', 'Planning By User'),
134 'planning_account': fields.one2many('report_account_analytic.planning.account', 'planning_id', 'Planning By Account'),
135 'total_planned': fields.function(_get_total_planned, string='Total Planned'),
136 'total_free': fields.function(_get_total_free, string='Total Free'),
139 'date_from': lambda self,cr,uid,ctx: fields.date.context_today(self,cr,uid,timestamp=(datetime.now()+relativedelta(day=1)),context=ctx),
140 'date_to': lambda self,cr,uid,ctx: fields.date.context_today(self,cr,uid,timestamp=(datetime.now()+relativedelta(months=1, day=1, days=-1)),context=ctx),
141 'user_id': lambda self, cr, uid, c: uid,
145 _order = 'date_from desc'
148 (_check_planning_responsible, 'Invalid planning ! Planning dates can\'t overlap for the same responsible. ', ['user_id'])
151 def action_open(self, cr, uid, id, context=None):
152 self.write(cr, uid, id, {'state' : 'open'}, context=context)
155 def action_cancel(self, cr, uid, id, context=None):
156 self.write(cr, uid, id, {'state' : 'cancel'}, context=context)
159 def action_draft(self, cr, uid, id, context=None):
160 self.write(cr, uid, id, {'state' : 'draft'}, context=context)
163 def action_done(self, cr, uid, id, context=None):
164 self.write(cr, uid, id, {'state' : 'done'}, context=context)
167 report_account_analytic_planning()
169 class report_account_analytic_planning_line(osv.osv):
170 _name = "report_account_analytic.planning.line"
171 _description = "Planning Line"
172 _rec_name = 'user_id'
174 def name_get(self, cr, uid, ids, context=None):
177 reads = self.read(cr, uid, ids, ['user_id', 'planning_id', 'note'], context=context)
180 name = '['+record['planning_id'][1]
181 if record['user_id']:
182 name += " - " +record['user_id'][1]+'] '
186 name += record['note']
187 res.append((record['id'], name))
190 def _amount_base_uom(self, cr, uid, ids, name, args, context=None):
191 users_obj = self.pool.get('res.users')
193 tm = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
198 for line in self.browse(cr, uid, ids, context=context):
199 result[line.id] = line.amount / line.amount_unit.factor * div
203 'account_id': fields.many2one('account.analytic.account', 'Analytic account', select=True),
204 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning', required=True, ondelete='cascade'),
205 'user_id': fields.many2one('res.users', 'User', select=True),
206 'amount': fields.float('Quantity', required=True),
207 'amount_unit': fields.many2one('product.uom', 'Qty UoM', required=True),
208 'note': fields.text('Note', size=64),
209 'amount_in_base_uom': fields.function(_amount_base_uom, string='Quantity in base uom', store=True),
210 'task_ids': fields.one2many('project.task', 'planning_line_id', 'Planning Tasks'),
212 _order = 'user_id, account_id'
214 report_account_analytic_planning_line()
216 class account_analytic_account(osv.osv):
217 _name = 'account.analytic.account'
218 _inherit = 'account.analytic.account'
220 'planning_ids': fields.one2many('report_account_analytic.planning.line', 'account_id', 'Plannings'),
223 account_analytic_account()
225 class project_task(osv.osv):
226 _name = "project.task"
227 _inherit = "project.task"
229 'planning_line_id': fields.many2one('report_account_analytic.planning.line', 'Planning', ondelete='cascade'),
232 def search(self, cr, user, args, offset=0, limit=None, order=None, context=None, count=False):
235 if not context.get('planning', False):
236 return super(project_task,self).search(cr, user, args, offset, limit, order, context)
237 cr.execute(" SELECT t.id, t.name \
238 from project_task t \
239 join report_account_analytic_planning_line l on (l.id = t.planning_line_id )\
240 where l.planning_id=%s",(context.get('planning'),))
241 ids = map(lambda x: x[0], cr.fetchall())
246 class report_account_analytic_planning_user(osv.osv):
247 _name = "report_account_analytic.planning.user"
248 _description = "Planning by User"
249 _rec_name = 'user_id'
252 def _get_tasks(self, cr, uid, ids, name, args, context=None):
253 users_obj = self.pool.get('res.users')
255 tm = users_obj.browse(cr, uid, uid, context=context).company_id.project_time_mode_id
260 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
261 if tm2 and tm2.factor:
265 for line in self.browse(cr, uid, ids, context=context):
267 cr.execute("""select COALESCE(sum(tasks.remaining_hours),0) from project_task tasks \
268 where tasks.planning_line_id IN (select id from report_account_analytic_planning_line\
269 where planning_id = %s and user_id=%s)""", (line.planning_id.id, line.user_id.id,))
271 result[line.id] = cr.fetchall()[0][0] / div * div2
276 def _get_free(self, cr, uid, ids, name, args, context=None):
278 for line in self.browse(cr, uid, ids, context=context):
280 result[line.id] = line.planning_id.business_days - line.plan_tasks - line.plan_open - line.holiday
282 result[line.id] = 0.0
285 def _get_timesheets(self, cr, uid, ids, name, args, context=None):
286 users_obj = self.pool.get('res.users')
288 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
289 if tm2 and tm2.factor:
293 for line in self.browse(cr, uid, ids, context=context):
296 SELECT sum(unit_amount/uom.factor) FROM account_analytic_line acc
297 LEFT JOIN product_uom uom ON (uom.id = acc.product_uom_id)
298 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 ))
300 result[line.id] = res[0][0] and res[0][0] * div2 or False
306 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning'),
307 'user_id': fields.many2one('res.users', 'User', readonly=True),
308 '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.'),
309 '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.'),
310 'free': fields.function(_get_free, string='Unallocated Time', readonly=True, help='Computed as \
311 Business Days - (Time Allocation of Tasks + Time Allocation without Tasks + Holiday Leaves)'),
312 '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.'),
313 '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.'),
314 '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.'),
318 cr.execute(""" CREATE OR REPLACE VIEW report_account_analytic_planning_user AS (
320 planning.id AS planning_id,
321 (1000*(planning.id) + users.id)::integer AS id,
322 planning.business_days,
324 (SELECT sum(line1.amount_in_base_uom)
325 FROM report_account_analytic_planning_line line1
328 FROM project_task task
329 WHERE task.planning_line_id = line1.id
331 AND line1.user_id = users.id
332 AND line1.planning_id = planning.id
334 (SELECT SUM(line1.amount_in_base_uom)
335 FROM report_account_analytic_planning_line line1
338 FROM project_task task
339 WHERE task.planning_line_id = line1.id
341 AND line1.user_id = users.id
342 AND line1.planning_id = planning.id
344 (SELECT -(SUM(holidays.number_of_days))
345 FROM hr_holidays holidays
346 WHERE holidays.employee_id IN
349 FROM hr_employee emp, resource_resource res WHERE emp.resource_id = res.id and res.user_id = users.id
351 AND holidays.state IN ('validate')
352 AND holidays.type = 'remove'
353 AND holidays.date_from >= planning.date_from
354 AND holidays.date_to <= planning.date_to
357 FROM report_account_analytic_planning planning
358 LEFT JOIN report_account_analytic_planning_line line ON (line.planning_id = planning.id), res_users users
359 GROUP BY planning.id, planning.business_days, users.id, planning.date_from, planning.date_to
364 planning.id AS planning_id,
365 (1000*(planning.id) - 1)::integer AS id,
366 planning.business_days,
368 (SELECT SUM(line1.amount_in_base_uom)
369 FROM report_account_analytic_planning_line line1
370 WHERE (SELECT COUNT(1) FROM project_task task WHERE task.planning_line_id = line1.id) > 0
371 AND line1.user_id IS NULL
373 (SELECT SUM(line1.amount_in_base_uom)
374 FROM report_account_analytic_planning_line line1
375 WHERE (SELECT COUNT(1) FROM project_task task WHERE task.planning_line_id = line1.id) = 0
376 AND line1.user_id IS NULL
379 FROM report_account_analytic_planning planning
380 INNER JOIN report_account_analytic_planning_line line ON line.planning_id = planning.id
381 AND line.user_id IS NULL
382 GROUP BY planning.id, planning.business_days, line.user_id, planning.date_from, planning.date_to
386 report_account_analytic_planning_user()
388 class report_account_analytic_planning_account(osv.osv):
389 _name = "report_account_analytic.planning.account"
390 _description = "Planning by Account"
391 _rec_name = 'account_id'
394 def _get_tasks(self, cr, uid, ids, name, args, context=None):
395 users_obj = self.pool.get('res.users')
397 tm = users_obj.browse(cr, uid, uid, context=context).company_id.project_time_mode_id
402 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
403 if tm2 and tm2.factor:
407 for line in self.browse(cr, uid, ids, context=context):
409 SELECT COALESCE(sum(tasks.remaining_hours),0)
410 FROM project_task tasks
411 WHERE tasks.planning_line_id IN (
413 FROM report_account_analytic_planning_line
414 WHERE planning_id = %s AND account_id=%s
415 )""", (line.planning_id.id, line.account_id and line.account_id.id or None))
416 result[line.id] = cr.fetchall()[0][0] / div * div2
419 def _get_timesheets(self, cr, uid, ids, name, args, context=None):
420 users_obj = self.pool.get('res.users')
422 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
423 if tm2 and tm2.factor:
427 for line in self.browse(cr, uid, ids, context=context):
429 SELECT SUM(unit_amount/uom.factor) FROM account_analytic_line acc
430 LEFT JOIN product_uom uom ON (uom.id = acc.product_uom_id)
431 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))
432 res = cr.fetchall()[0][0]
434 result[line.id] = res * div2
440 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning'),
441 'account_id': fields.many2one('account.analytic.account', 'Analytic account', readonly=True),
442 '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.'),
443 '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.'),
444 '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.'),
445 '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.'),
449 cr.execute(""" CREATE OR REPLACE VIEW report_account_analytic_planning_account AS (
452 l.account_id AS account_id,
453 SUM(l.amount) AS quantity,
454 l.planning_id AS planning_id,
455 ( SELECT SUM(line1.amount_in_base_uom)
456 FROM report_account_analytic_planning_line line1
459 FROM project_task task
460 WHERE task.planning_line_id = line1.id
462 AND l.account_id = line1.account_id
463 AND l.planning_id = line1.planning_id
465 ( SELECT SUM(line1.amount_in_base_uom)
466 FROM report_account_analytic_planning_line line1
469 FROM project_task task
470 WHERE task.planning_line_id = line1.id
472 AND l.account_id = line1.account_id
473 AND planning.id = line1.planning_id
475 FROM report_account_analytic_planning_line l
476 INNER JOIN report_account_analytic_planning planning ON planning.id=l.planning_id
477 GROUP BY l.account_id, l.planning_id, planning.date_from, planning.date_to, planning.id
481 report_account_analytic_planning_account()
483 class report_account_analytic_planning_stat(osv.osv):
484 _name = "report_account_analytic.planning.stat"
485 _description = "Planning stat"
486 _rec_name = 'user_id'
489 _order = 'planning_id,user_id'
491 def _sum_amount_real(self, cr, uid, ids, name, args, context=None):
492 users_obj = self.pool.get('res.users')
494 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
495 if tm2 and tm2.factor:
499 for line in self.browse(cr, uid, ids, context=context):
501 cr.execute('''SELECT sum(acc.unit_amount/uom.factor) FROM account_analytic_line acc
502 LEFT JOIN product_uom uom ON (uom.id = acc.product_uom_id)
503 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))
505 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))
509 result[line.id] = sum[0] * div2
512 def _sum_amount_tasks(self, cr, uid, ids, name, args, context=None):
513 users_obj = self.pool.get('res.users')
515 tm = users_obj.browse(cr, uid, uid, context=context).company_id.project_time_mode_id
520 tm2 = users_obj.browse(cr, uid, uid, context=context).company_id.planning_time_mode_id
521 if tm2 and tm2.factor:
525 for line in self.browse(cr, uid, ids, context=context):
528 where = 'user_id=' + str(line.user_id.id) + ' and '
535 project_id IN (select id from project_project where analytic_account_id=%s) AND
538 line.account_id and line.account_id.id or None,
539 line.planning_id.date_from,
540 line.planning_id.date_to)
544 result[line.id] = sum[0] /div * div2
548 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning', select=True),
549 'user_id': fields.many2one('res.users', 'User', select=True),
550 'manager_id': fields.many2one('res.users', 'Manager'),
551 'account_id': fields.many2one('account.analytic.account', 'Account'),
552 'sum_amount': fields.float('Planned Days', required=True),
553 'sum_amount_real': fields.function(_sum_amount_real, string='Timesheet'),
554 'sum_amount_tasks': fields.function(_sum_amount_tasks, string='Tasks'),
559 create or replace view report_account_analytic_planning_stat as (
562 l.user_id as user_id,
563 a.user_id as manager_id,
564 l.account_id as account_id,
565 sum(l.amount/u.factor) as sum_amount,
568 report_account_analytic_planning_line l
570 report_account_analytic_planning a on (a.id = l.planning_id)
572 product_uom u on (l.amount_unit = u.id)
574 l.planning_id, l.user_id, l.account_id, a.user_id
578 report_account_analytic_planning_stat()
580 class res_company(osv.osv):
581 _inherit = 'res.company'
583 'planning_time_mode_id': fields.many2one('product.uom', 'Planning Time Unit',
584 help='This will set the unit of measure used in plannings.',
589 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: