
DROP PROCEDURE IF EXISTS `insert_update_employee_info`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_update_employee_info`()
BEGIN

TRUNCATE proll_employee_detail;
SET @query = CONCAT("INSERT INTO proll_employee_detail (empid) SELECT emp_id FROM proll_employee_info");
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @query = CONCAT("Update proll_employee_detail ped 
inner join proll_employee_info pei on pei.emp_id = ped.empid 
SET ped.sap_id = pei.emp_third_party_id,
		ped.empcode = (SELECT e.empcode from proll_employee e WHERE e.id = pei.emp_id),
	   ped.employeeno = (SELECT e.employeeno from proll_employee e WHERE e.id = pei.emp_id),	
		ped.personalEmail = (SELECT e.loginname from proll_employee e WHERE e.id = pei.emp_id),
		ped.previous_appraisal_rating = pei.previous_appraisal_rating,
		ped.PPRRating = pei.previous_prior_rating ,
		ped.PPRLastYear = pei.previous_appraisal_rating,
		ped.lastPromotionDate = pei.lastpromotiondate,
		ped.emgContactPerson = (SELECT emergency_contact_person_name FROM proll_employee_contact ec WHERE ec.emp_id = pei.emp_id AND ec.address_type='current'),
		ped.emgContactNo = (SELECT emergency_contact_number FROM proll_employee_contact ec WHERE ec.emp_id = pei.emp_id AND ec.address_type='current'),
		ped.bloodGroup  = (SELECT pr.description FROM proll_reference_data pr WHERE pr.id = pei.blood_group_id),
		ped.gender= (SELECT pr.description FROM proll_reference_data pr WHERE pr.id = pei.gender),
		ped.bank_name = (SELECT b.bank_name FROM bank_accounts ba inner join banks b ON b.bank_id = ba.bank_id where ba.employee_id = pei.emp_id),
		ped.bank_account = (SELECT ba.account_number FROM bank_accounts ba  where ba.employee_id = pei.emp_id),
		ped.lastworkingdate = pei.lastworkingdate,
		ped.dateoflastincrement = pei.lastpromotiondate,
		ped.managername = (SELECT pdm.line_manager from proll_employee e INNER JOIN proll_department_managers pdm ON pdm.id = e.dept_id WHERE e.id = pei.emp_id),
		ped.manageremail = (SELECT pdm.email from proll_employee e INNER JOIN proll_department_managers pdm ON pdm.id = e.dept_id WHERE e.id = pei.emp_id),
		ped.departmentname = (SELECT dh.department_name from proll_employee e INNER JOIN department_hierarchy dh ON dh.id = e.department_id WHERE e.id = pei.emp_id),
		ped.branch  = (SELECT pcl.loc_desc from proll_employee e inner join proll_client_location pcl on pcl.loc_id = e.loc_id where e.id = pei.emp_id),
		ped.employeementRegionCurrent  = (SELECT e.country from proll_employee e where e.id = pei.emp_id)
");
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

DROP TRIGGER IF EXISTS `after_employee_info_update`;
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE DEFINER=`root`@`localhost` TRIGGER `after_employee_info_update` AFTER UPDATE ON `proll_employee_info` FOR EACH ROW Update proll_employee_detail
 SET 
 	`empid` = NEW.emp_id,
	`sap_id`= NEW.emp_third_party_id,
	`empcode` = (SELECT e.empcode from proll_employee e WHERE e.id = NEW.emp_id),
   `employeeno` = (SELECT e.employeeno from proll_employee e WHERE e.id = NEW.emp_id),	
	`personalEmail` = (SELECT e.loginname from proll_employee e WHERE e.id = NEW.emp_id),
	`previous_appraisal_rating` = NEW.previous_appraisal_rating,
	`PPRRating` = NEW.previous_prior_rating ,
	`PPRLastYear` = NEW.previous_appraisal_rating,
	`lastPromotionDate` = NEW.lastpromotiondate,
	`emgContactPerson` = (SELECT emergency_contact_person_name FROM proll_employee_contact ec WHERE ec.emp_id = NEW.emp_id AND ec.address_type="current"),
	`emgContactNo` = (SELECT emergency_contact_number FROM proll_employee_contact ec WHERE ec.emp_id = NEW.emp_id AND ec.address_type="current"),
	`bloodGroup`  = (SELECT pr.description FROM proll_reference_data pr WHERE pr.id = NEW.blood_group_id),
	`gender`= (SELECT pr.description FROM proll_reference_data pr WHERE pr.id = NEW.gender),
	`bank_name` = (SELECT b.bank_name FROM bank_accounts ba inner join banks b ON b.bank_id = ba.bank_id where ba.employee_id = NEW.emp_id),
	`bank_account` = (SELECT ba.account_number FROM bank_accounts ba  where ba.employee_id = NEW.emp_id),
	`branch`  = (SELECT pcl.loc_desc from proll_employee e inner join proll_client_location pcl on pcl.loc_id = e.loc_id where e.id = NEW.emp_id),
	`lastworkingdate` = NEW.lastworkingdate,
	`dateoflastincrement` = NEW.lastpromotiondate ,
	`managername` = (SELECT pdm.line_manager from proll_employee e INNER JOIN proll_department_managers pdm ON pdm.id = e.dept_id WHERE e.id = NEW.emp_id),
	`manageremail` = (SELECT pdm.email from proll_employee e INNER JOIN proll_department_managers pdm ON pdm.id = e.dept_id WHERE e.id = NEW.emp_id),
	`departmentname` = (SELECT dh.department_name from proll_employee e INNER JOIN department_hierarchy dh ON dh.id = e.department_id WHERE e.id = NEW.emp_id)
 WHERE empid = NEW.emp_id//
DELIMITER ;

DROP TRIGGER IF EXISTS `after_employee_update`;
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE DEFINER=`root`@`localhost` TRIGGER `after_employee_update` AFTER UPDATE ON `proll_employee` FOR EACH ROW Update proll_employee_detail
 SET 
 	`empid` = NEW.id,
	`branch`  =  (SELECT pcl.loc_desc FROM proll_client_location pcl WHERE pcl.loc_id = NEW.loc_id),
	`employeementRegionCurrent` = NEW.country
 WHERE empid = NEW.id//
DELIMITER ;


