Debian Codegraph @

addons/hr_skills/report/hr_employee_skill_history_report.py

raw
1 # Part of Odoo. See LICENSE file for full copyright and licensing details.
2
3 from odoo import fields, models, tools
4
5
6 class HrEmployeeSkillReport(models.BaseModel):
7 _name = 'hr.employee.skill.history.report'
8 _auto = False
9 _description = 'Employee Skills Report'
10
11 employee_id = fields.Many2one('hr.employee', readonly=True)
12 date = fields.Date()
13 skill_id = fields.Many2one('hr.skill', readonly=True)
14 skill_type_id = fields.Many2one('hr.skill.type', readonly=True)
15 level_progress = fields.Float(readonly=True)
16
17 def init(self):
18 tools.drop_view_if_exists(self.env.cr, self._table)
19
20 self.env.cr.execute("""
21 CREATE OR REPLACE VIEW %s AS (
22 WITH
23 individual_skill AS (
24 SELECT
25 valid_from,
26 valid_to,
27 employee_id,
28 skill_id
29 FROM hr_employee_skill
30 ORDER BY employee_id, skill_id, valid_from ASC
31 ),
32 date_table AS (
33 SELECT
34 date,
35 employee_id
36 FROM (
37 SELECT
38 valid_from as date,
39 employee_id
40 FROM individual_skill AS start
41 UNION
42 SELECT
43 valid_to as date,
44 employee_id
45 FROM individual_skill AS stop
46 WHERE stop.valid_to IS NOT NULL
47 ) AS date_table
48 ORDER BY date ASC
49 )
50 SELECT DISTINCT ON(date_table.date, emp_skill_level.employee_id, emp_skill_level.skill_id)
51 date_table.date AS date,
52 emp_skill_level.employee_id,
53 emp_skill_level.skill_id,
54 emp_skill_level.skill_type_id,
55 emp_skill_level.level_progress
56 FROM date_table
57 CROSS JOIN (
58 SELECT
59 emp_skill.*,
60 level.level_progress
61 FROM hr_employee_skill AS emp_skill
62 INNER JOIN hr_skill_level AS level
63 ON emp_skill.skill_level_id = level.id
64 ) AS emp_skill_level
65 WHERE date_table.date >= emp_skill_level.valid_from AND date_table.employee_id = emp_skill_level.employee_id AND (emp_skill_level.valid_to IS NULL OR date_table.date <= emp_skill_level.valid_to)
66 ORDER BY date_table.date, emp_skill_level.employee_id, emp_skill_level.skill_id, emp_skill_level.valid_from DESC
67 )
68 """ % (self._table, ))