DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `staff_turnover`(in oldStaffId varchar(50), in newStaffId varchar(50), in turnoverDate datetime)
BEGIN

select id INTO @sid from staff where login_id = oldStaffId and id > 0;
if @sid is null then
insert into staff (login_id) values (oldStaffId);
end if;


update circular_receiver set student_id = newStaffId where student_id = oldStaffId and circular_id != null;
update hr_staff set staff_code = newStaffId, term_date = turnoverDate where staff_code = oldStaffId and id > 0;
update observation set teacher_id = newStaffId where teacher_id = oldStaffId and id > 0;
update parent_message set student_id = newStaffId where student_id = oldStaffId and id > 0;
update staff set login_id = newStaffId, turnover_date = turnoverDate where login_id = oldStaffId and id > 0;
update staff_certificate set staff_id = newStaffId where staff_id = oldStaffId and id > 0;
update staff_educational set staff_id = newStaffId where staff_id = oldStaffId and id > 0;
update staff_executive set staff_id = newStaffId where staff_id = oldStaffId and id > 0;
update staff_experience set staff_id = newStaffId where staff_id = oldStaffId and id > 0;
update staff_responsibility set staff_id = newStaffId where staff_id = oldStaffId and id > 0;
update staff_spouse set staff_id = newStaffId where staff_id = oldStaffId and id > 0;
update staff_training set staff_id = newStaffId where staff_id = oldStaffId and id > 0;
update user_group set login_id = newStaffId where login_id = oldStaffId;
update users set login_id = newStaffId where login_id = oldStaffId;
/* */




END$$
DELIMITER ;
