ALTER TABLE main_users
ADD COLUMN `firstname` varchar(255) NULL AFTER `userstatus`,
ADD COLUMN `lastname` VARCHAR(255)  NULL AFTER `userstatus`;

ALTER TABLE main_employees_summary
ADD COLUMN `firstname` varchar(255) NULL AFTER `emprole_name`,
ADD COLUMN `lastname` VARCHAR(255)  NULL AFTER `firstname`;

ALTER TABLE main_candidatedetails
ADD COLUMN `candidate_firstname` varchar(50) NULL AFTER `requisition_id`,
ADD COLUMN `candidate_lastname` VARCHAR(50)  NULL AFTER `candidate_firstname`;

alter table `main_empsalarydetails` 
add column `salarytype` tinyint(1) DEFAULT '1' NULL COMMENT '1=yearly,2=monthly' after `currencyid`, 
add column `salary` bigint(40) UNSIGNED NULL after `salarytype`;

DELIMITER $$

DROP TRIGGER `main_identitycodes_aft_upd`$$

CREATE
    
    TRIGGER `main_identitycodes_aft_upd` AFTER UPDATE ON `main_identitycodes` 
    FOR EACH ROW BEGIN
    if old.employee_code != new.employee_code then 
    begin
	update main_users set employeeId = replace(employeeId,SUBSTRING(employeeId,1,CHAR_LENGTH(old.employee_code)),new.employee_code),modifieddate = utc_timestamp() where SUBSTRING(employeeId,1,CHAR_LENGTH(old.employee_code)) = old.employee_code;
    end;
    end if;
    if old.backgroundagency_code != new.backgroundagency_code then 
    begin
	update main_users set employeeId = replace(employeeId,SUBSTRING(employeeId,1,CHAR_LENGTH(old.backgroundagency_code)),new.backgroundagency_code),modifieddate = utc_timestamp() where SUBSTRING(employeeId,1,CHAR_LENGTH(old.backgroundagency_code)) = old.backgroundagency_code;
    end;
    end if;
    if old.users_code != new.users_code then 
    begin
	update main_users set employeeId = replace(employeeId,SUBSTRING(employeeId,1,CHAR_LENGTH(old.users_code)),new.users_code),modifieddate = utc_timestamp() where SUBSTRING(employeeId,1,CHAR_LENGTH(old.users_code)) = old.users_code;
    end;
    end if;	
    if old.requisition_code != new.requisition_code then 
    begin
	update main_requisition r set r.requisition_code = replace(r.requisition_code,left(r.requisition_code,LOCATE('/',r.requisition_code)),CONCAT(new.requisition_code,'/')),r.modifiedon = utc_timestamp() where left(r.requisition_code,LOCATE('/',r.requisition_code)) = CONCAT(old.requisition_code,'/');
    end;
    end if;
    END;
$$

DELIMITER ;


DELIMITER $$

DROP TRIGGER `main_employees_aft_ins`$$

CREATE
    
    TRIGGER `main_employees_aft_ins` AFTER INSERT ON `main_employees` 
    FOR EACH ROW BEGIN
	declare user_id,fname,lname,username,role_name,rep_name,emp_status,bunit_name,dept_name,job_name,pos_name,prefix_name,
		createdbyname,holidaygrp,modifiedbyname,emailid,cnumber,bgstatus,empid,mode_entry,omode_entry,sel_date,
                ref_by_name,img_src
		varchar(250);
	declare ref_by_id,role_id int(11);
	select firstname,lastname,userfullname,emailaddress,contactnumber,backgroundchk_status,employeeId,modeofentry,other_modeofentry,selecteddate,candidatereferredby,
               profileimg,emprole  
		into fname,lname,username,emailid,cnumber,bgstatus,empid,mode_entry,omode_entry,sel_date,ref_by_id,img_src,role_id 
	from main_users where id = new.user_id;
	select userfullname into rep_name from main_users where id = new.reporting_manager;
/*
	select employemnt_status into emp_status from tbl_employmentstatus where id = (select workcodename 
	from main_employmentstatus where id = new.emp_status_id);*/
	select employemnt_status into emp_status from tbl_employmentstatus where id = new.emp_status_id	;
	set user_id = new.user_id;
	set bunit_name = null;
	if new.businessunit_id is not null then
		select unitname into bunit_name from main_businessunits where id = new.businessunit_id;
	end if;
	set holidaygrp = null;
	if new.holiday_group is not null then
		select groupname into holidaygrp from main_holidaygroups where id = new.holiday_group;
	end if;
	select deptname into dept_name from main_departments where id = new.department_id;
	select jobtitlename into job_name from main_jobtitles where id = new.jobtitle_id;
	select positionname into pos_name from main_positions where id = new.position_id;
	select prefix into prefix_name from main_prefix where id = new.prefix_id;
	select userfullname into createdbyname from main_users where id = new.createdby;
	select rolename into role_name from main_roles where id = role_id;
	if (ref_by_id != '' and ref_by_id > 0) then 
        begin 
	    select userfullname into ref_by_name from main_users where id = ref_by_id;
        end;
        end if;
insert into main_employees_summary ( 
	user_id, date_of_joining, date_of_leaving, reporting_manager, reporting_manager_name, emp_status_id, 
	emp_status_name, businessunit_id, businessunit_name, department_id, department_name, jobtitle_id, 
	jobtitle_name, position_id, position_name, years_exp, holiday_group, holiday_group_name, 
	prefix_id, prefix_name, extension_number, office_number, office_faxnumber, emprole, 
	emprole_name, firstname,lastname,userfullname, emailaddress, contactnumber, backgroundchk_status, 	employeeId, 
	modeofentry, other_modeofentry, selecteddate, candidatereferredby, referer_name, profileimg, 
	createdby, createdby_name, modifiedby, createddate, modifieddate, isactive)
	values	(	
	new.user_id, new.date_of_joining, new.date_of_leaving,new.reporting_manager,rep_name,new.emp_status_id, 
	emp_status,new.businessunit_id,	bunit_name,new.department_id,dept_name,new.jobtitle_id, 
	job_name, new.position_id, pos_name,new.years_exp, new.holiday_group, holidaygrp, 
	new.prefix_id, 	prefix_name, new.extension_number, new.office_number, new.office_faxnumber,role_id, 
	role_name,fname,lname,username, emailid,cnumber,bgstatus,empid, 
	mode_entry,omode_entry,	sel_date, ref_by_id, ref_by_name,img_src, 
	new.createdby, 	createdbyname, new.modifiedby,new.createddate, new.modifieddate, new.isactive
	);
    END;
$$

DELIMITER ;

DELIMITER $$

DROP TRIGGER `main_employees_aft_upd`$$

CREATE
    
    TRIGGER `main_employees_aft_upd` AFTER UPDATE ON `main_employees` 
    FOR EACH ROW BEGIN
	declare fname,lname,username,role_name,rep_name,emp_status,bunit_name,dept_name,job_name,pos_name,prefixname,
		createdbyname,holidaygrp,modifiedbyname,emailid,cnumber,bgstatus,empid,mode_entry,omode_entry,sel_date,
                ref_by_name,img_src
		varchar(250);
	declare ref_by_id,role_id int(11);
	select firstname,lastname,userfullname,emailaddress,contactnumber,backgroundchk_status,employeeId,modeofentry,other_modeofentry,selecteddate,candidatereferredby,
               profileimg,emprole  
		into fname,lname,username,emailid,cnumber,bgstatus,empid,mode_entry,omode_entry,sel_date,ref_by_id,img_src,role_id 
	from main_users where id = new.user_id;
	select userfullname into rep_name from main_users where id = new.reporting_manager;
	/*select employemnt_status into emp_status from tbl_employmentstatus where id = (select workcodename 
	from main_employmentstatus where id = new.emp_status_id);*/
	select employemnt_status into emp_status from tbl_employmentstatus where id = new.emp_status_id	;
	set bunit_name = null;
	if new.businessunit_id is not null then
		select unitname into bunit_name from main_businessunits where id = new.businessunit_id;
	end if;
	set holidaygrp = null;
	if new.holiday_group is not null then
		select groupname into holidaygrp from main_holidaygroups where id = new.holiday_group;
	end if;
	select deptname into dept_name from main_departments where id = new.department_id;
	select jobtitlename into job_name from main_jobtitles where id = new.jobtitle_id;
	select positionname into pos_name from main_positions where id = new.position_id;
	select prefix into prefixname from main_prefix where id = new.prefix_id;
	select userfullname into createdbyname from main_users where id = new.createdby;
	select rolename into role_name from main_roles where id = role_id;
	if (ref_by_id != '' and ref_by_id > 0) then 
        begin 
	    select userfullname into ref_by_name from main_users where id = ref_by_id;
        end;
        end if;
        update main_employees_summary set  
	 date_of_joining = new.date_of_joining, date_of_leaving = new.date_of_leaving, reporting_manager = new.reporting_manager, 
         reporting_manager_name = rep_name, emp_status_id = new.emp_status_id, 	emp_status_name = emp_status, 
	businessunit_id = new.businessunit_id, businessunit_name = bunit_name, department_id = new.department_id, 
        department_name = dept_name, jobtitle_id = new.jobtitle_id,jobtitle_name = job_name, position_id = new.position_id, 
        position_name = pos_name, years_exp = new.years_exp, holiday_group = new.holiday_group, holiday_group_name = holidaygrp, 
	prefix_id = new.prefix_id, prefix_name = prefixname, extension_number = new.extension_number, office_number = new.office_number, 
	office_faxnumber = new.office_faxnumber, emprole = role_id, emprole_name = role_name, firstname=fname, lastname=lname,userfullname = username, 
	emailaddress = emailid, contactnumber = cnumber, backgroundchk_status = bgstatus,employeeId = empid, 
	modeofentry = mode_entry, other_modeofentry = omode_entry, selecteddate = sel_date, candidatereferredby = ref_by_id,
	referer_name = ref_by_name, profileimg = img_src,  modifiedby = new.modifiedby, modifieddate = new.modifieddate, isactive = new.isactive
	
	 where user_id = new.user_id;
    END;
$$

DELIMITER ;