|
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 |
|
|
