CREATE VIEW train_need_view AS SELECT
row_number () OVER (ORDER BY b.emoployee_name) AS id,
b.emoployee_name,
b.emoployee_id,
b.id_card,
b.course_id,
b.course_name,
b.train_hours,
b.reason,
b.organ_id,
b.organ_name,
b.department_id,
CASE b.department_type
WHEN 4 THEN b.department_name
WHEN 9 THEN b.department_name
when 5 then (select f.department_name from sys_department f where f.id=
(SELECT g.parent_id from sys_department g where g.id=b.department_id))
when 6 then (select e.department_name from sys_department e where e.id=
(SELECT c.parent_id from sys_department c where c.id=
(SELECT d.parent_id from sys_department d where d.id=b.department_id)))
else null
end as top_department_name
FROM
(
SELECT
a.emoployee_name,
a.emoployee_id,
a.id_card,
a.course_id,
a.course_name,
a.train_hours,
a.reason,
a.organ_id,
a.organ_name,
a.department_id,
a.parent_id,
a.department_type,
a.department_name
FROM
(
SELECT
sys_employee.name AS emoployee_name,
sys_employee.id AS emoployee_id,
sys_employee.id_card,
train_need.course_id,
course.course_name,
course.train_hours,
train_need.reason,
sys_organ.id AS organ_id,
sys_organ.organ_name,
sys_employee.department_id,
sys_department.parent_id,
sys_department.type AS department_type,
sys_department.department_name
FROM
train_need,
sys_employee,
sys_organ,
course,
sys_department
WHERE
train_need.employee_id = sys_employee.id
AND sys_employee.organ_id = sys_organ.id
AND course.id = train_need.course_id
AND sys_employee.department_id = sys_department.id
UNION ALL
SELECT
sys_employee.name AS emoployee_name,
sys_employee.id AS emoployee_id,
sys_employee.id_card,
break_rule_education.course_id,
course.course_name,
course.train_hours,
'违章教育' AS reason,
sys_organ.id AS organ_id,
sys_organ.organ_name,
sys_employee.department_id,
sys_department.parent_id,
sys_department.type AS department_type,
sys_department.department_name
FROM
break_rule_education,
sys_employee,
sys_organ,
course,
sys_department
WHERE
break_rule_education.employee_id = sys_employee.id
AND sys_employee.organ_id = sys_organ.id
AND break_rule_education.course_id = course.id
AND sys_employee.department_id = sys_department.id
UNION ALL
SELECT
sys_employee.name AS emoployee_name,
sys_employee.id AS emoployee_id,
sys_employee.id_card,
NULL AS course_id,
NULL AS course_name,
NULL AS train_hours,
'持续培训覆盖率低' AS reason,
sys_organ.id AS organ_id,
sys_organ.organ_name,
sys_employee.department_id,
sys_department.parent_id,
sys_department.type AS department_type,
sys_department.department_name
FROM
sys_employee,
sys_organ,
sys_department
WHERE
sys_employee.organ_id = sys_organ.id
AND sys_employee.continue_train_rate < 0.005
AND sys_employee.department_id = sys_department.id
UNION ALL
SELECT
sys_employee.name AS emoployee_name,
sys_employee.id AS emoployee_id,
sys_employee.id_card,
course.id AS course_id,
course.course_name AS course_name,
course.train_hours,
'培训覆盖率低' AS reason,
sys_organ.id AS organ_id,
sys_organ.organ_name,
sys_employee.department_id,
sys_department.parent_id,
sys_department.type AS department_type,
sys_department.department_name
FROM
sys_employee,
course_position,
sys_organ,
course,
sys_department /*INNER JOIN course_position on sys_employee.position_id=course_position.position_id
INNER JOIN sys_organ on sys_employee.organ_id=sys_organ.id
*/
WHERE
sys_employee.organ_id = sys_organ.id
AND sys_employee.train_rate < 1
AND course_position.course_id = course.id
AND course.disable = 0
AND course_position.disable = 0
AND sys_employee.disable = 0
AND sys_employee.position_id = course_position.position_id
AND sys_employee.department_id = sys_department.id
AND NOT EXISTS (
SELECT
* --train_record.course_id--,train_record.employee_id
FROM
train_record
WHERE
train_record.employee_id = sys_employee.id
AND train_record.course_id = course_position.course_id
)
GROUP BY
course.id,
course.course_name,
course.train_hours,
sys_employee.id,
sys_employee.name,
sys_employee.id_card,
sys_organ.id,
sys_organ.organ_name,
sys_employee.department_id,
sys_department.parent_id,
sys_department.type,
sys_department.department_name
UNION ALL
SELECT
sys_employee.name AS emoployee_name,
sys_employee.id AS emoployee_id,
sys_employee.id_card,
course.id AS course_id,
course.course_name AS course_name,
course.train_hours,
'培训覆盖率低' AS reason,
sys_organ.id AS organ_id,
sys_organ.organ_name,
sys_employee.department_id,
sys_department.parent_id,
sys_department.type AS department_type,
sys_department.department_name
FROM
sys_employee,
course_work_type,
sys_organ,
course,
sys_department
WHERE
sys_employee.organ_id = sys_organ.id
AND sys_employee.train_rate < 1
AND course_work_type.course_id = course.id
AND course.disable = 0
AND course_work_type.disable = 0
AND sys_employee.disable = 0
AND sys_employee.work_type_id = course_work_type.work_type_id
AND sys_employee.department_id = sys_department.id
AND NOT EXISTS (
SELECT
* --train_record.course_id--,train_record.employee_id
FROM
train_record
WHERE
train_record.employee_id = sys_employee.id
AND train_record.course_id = course_work_type.course_id
)
GROUP BY
course.id,
course.course_name,
course.train_hours,
sys_employee.id,
sys_employee.name,
sys_employee.id_card,
sys_organ.id,
sys_organ.organ_name,
sys_employee.department_id,
sys_department.parent_id,
sys_department.type,
sys_department.department_name
) AS a
GROUP BY
a.emoployee_name,
a.emoployee_id,
a.id_card,
a.course_id,
a.course_name,
a.train_hours,
a.reason,
a.organ_id,
a.organ_name,
a.department_id,
a.parent_id,
a.department_type,
a.department_name
) AS b
