DROP VIEW IF EXISTS vm_not_matched_employee_leave_condition_attend_site;
CREATE VIEW vm_not_matched_employee_leave_condition_attend_site AS
SELECT e.id as employeeId, CONCAT(u.`name`,'[',c.number,']为【',group_concat(t1.ITEM_ATTENDANCESITENAME),'】驻勤点负责人，请先将该驻勤点负责人进行调整后再办理离职') as info
	FROM tenant_employees e
INNER JOIN tenant_users u on e.tenant_user_id = u.id
INNER JOIN tenant_user_credentials c on u.selected_credential_id =c .id
INNER JOIN baibaodunflow.tlk_attendance_site_base_info t1 on t1.item_principal = e.id AND t1.DOMAINID = e.organization_id and t1.ITEM_ATTENDANCEENDDATE >= CURDATE()
group by e.id;


-- DROP VIEW IF EXISTS vm_not_matched_employee_leave_condition_attend_site_person;
-- CREATE VIEW vm_not_matched_employee_leave_condition_attend_site_person AS
-- SELECT e.id as employeeId, CONCAT(u.`name`,'[',c.number,']在【',t2.ITEM_ATTENDANCESITENAME,'】驻勤点驻勤，请先从驻勤点中移除，再办理离职') as info
-- 	FROM tenant_employees e
-- INNER JOIN tenant_users u on e.tenant_user_id = u.id
-- INNER JOIN tenant_user_credentials c on u.selected_credential_id =c .id
-- INNER JOIN baibaodunflow.tlk_attendance_site_person_info t1 ON t1.ITEM_SECURITYID = e.id AND t1.DOMAINID = e.organization_id
-- INNER JOIN baibaodunflow.tlk_attendance_site_base_info t2 ON t1.ITEM_ATTENDANCESITEID = t2.id and t2.ITEM_ATTENDANCEENDDATE >= CURDATE();

DROP VIEW IF EXISTS vm_not_matched_employee_leave_condition;
CREATE VIEW vm_not_matched_employee_leave_condition AS
SELECT e.id, CONCAT(u.`name`,'[',c.number,']') as info
	FROM tenant_employees e
INNER JOIN tenant_users u on e.tenant_user_id = u.id
INNER JOIN tenant_user_credentials c on u.selected_credential_id =c .id
where EXISTS(
select t1.ITEM_SECURITYID from tms.tlk_qualify_person_apply t1 where t1.ITEM_SECURITYID = e.id and t1.DOMAINID = e.organization_id and t1.ITEM_APPLYSTATUS ='0'
union select t2.ITEM_securityId from tms.tlk_grade_registration t2,tms.tlk_level_class_student t3
where t2.ITEM_securityId = e.id and t2.DOMAINID = e.organization_id and t2.ITEM_applyStatus ='0'
and t2.ITEM_idCardNo = t3.ITEM_IDCARDNO
and t3.ITEM_SCORESTATE ='0'
);