CREATE OR REPLACE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `employee_details` AS select `employee_history`.`employee_id` AS `employee_id`,`user`.`id` AS `user_id`,`employee_history`.`hrm_id` AS `hrm_id`,`employee_history`.`prefix` AS `prefix`,`employee_history`.`first_name` AS `first_name`,`employee_history`.`middle_name` AS `middle_name`,`employee_history`.`last_name` AS `last_name`,concat_ws(' ',`employee_history`.`first_name`,`employee_history`.`last_name`) AS `full_name`,`employee_history`.`status` AS `status`,`employee_history`.`pos_id` AS `pos_id`,`employee_history`.`email` AS `email`,`employee_history`.`phone` AS `phone`,`employee_history`.`alter_phone` AS `alter_phone`,`employee_history`.`present_address` AS `present_address`,`employee_history`.`parmanent_address` AS `permanent_address`,`employee_history`.`father_name` AS `father_name`,`employee_history`.`picture` AS `picture`,`employee_history`.`degree_name` AS `degree_name`,`employee_history`.`institute_name` AS `institute_name`,`employee_history`.`cgp` AS `grade`,`employee_history`.`passing_year` AS `passing_year`,`employee_history`.`dept_id` AS `dept_id`,`employee_history`.`division_id` AS `division_id`,`employee_history`.`state` AS `state`,`employee_history`.`city` AS `city`,`employee_history`.`hire_date` AS `hire_date`,timestampdiff(YEAR,`employee_history`.`hire_date`,if((dayofmonth(`employee_history`.`termination_date`) > 0),`employee_history`.`termination_date`,last_day(curdate()))) AS `tenure`,timestampdiff(MONTH,`employee_history`.`hire_date`,if((dayofmonth(`employee_history`.`termination_date`) > 0),`employee_history`.`termination_date`,last_day(curdate()))) AS `tenure_in_months`,timestampdiff(DAY,`employee_history`.`hire_date`,if((dayofmonth(`employee_history`.`termination_date`) > 0),`employee_history`.`termination_date`,last_day(curdate()))) AS `tenure_in_days`,`employee_history`.`original_hire_date` AS `original_hire_date`,`employee_history`.`termination_date` AS `termination_date`,`employee_history`.`termination_reason` AS `termination_reason`,`employee_history`.`exit_interview` AS `exit_interview`,`employee_history`.`exit_interview_date` AS `exit_interview_date`,`employee_history`.`exit_interview_details` AS `exit_interview_details`,`employee_history`.`voluntary_termination` AS `voluntary_termination`,`employee_history`.`rehire_date` AS `rehire_date`,if(((`employee_history`.`termination_date` >= `employee_history`.`hire_date`) and (isnull(`employee_history`.`rehire_date`) or (`employee_history`.`termination_date` > `employee_history`.`rehire_date`))),1,0) AS `is_resigned`,`employee_history`.`rate_type` AS `rate_type_id`,`employee_history`.`rate` AS `rate`,`employee_history`.`is_super_visor` AS `is_super_visor`,`employee_history`.`super_visor_id` AS `super_visor_id`,`employee_history`.`indirect_super_visor_id` AS `indirect_super_visor_id`,`employee_history`.`dob` AS `dob`,`employee_history`.`ethnic_group` AS `religion`,`employee_history`.`home_email` AS `personal_email`,`employee_history`.`home_phone` AS `personal_phone`,`employee_history`.`business_email` AS `business_email`,`employee_history`.`business_phone` AS `businesss_phone`,`employee_history`.`cell_phone` AS `cell_phone`,`employee_history`.`emerg_contct` AS `emergency_contact`,`employee_history`.`probation_period` AS `probation_period`,(`employee_history`.`hire_date` + interval `employee_history`.`probation_period` month) AS `probation_end`,`employee_history`.`probation_notified` AS `probation_notified`,`employee_history`.`contract_period` AS `contract_period`,`employee_history`.`contract_notified` AS `contract_notified`,`employee_history`.`performance_duration` AS `performance_duration`,`employee_history`.`cnic` AS `cnic`,`employee_history`.`current_salary` AS `current_salary`,`employee_history`.`hired_salary` AS `hired_salary`,`employee_history`.`eobi` AS `eobi`,`employee_history`.`eobi_check` AS `eobi_check`,`employee_history`.`provident_fund_check` AS `provident_fund_check`,`employee_history`.`work_from_home` AS `work_from_home`,`employee_history`.`transportation_required` AS `transportation_required`,`employee_history`.`primary_project` AS `primary_project`,`employee_history`.`team` AS `team`,`employee_history`.`campaign` AS `campaign`,`employee_history`.`pseudo` AS `pseudo`,`employee_history`.`medflow_id` AS `medflow_id`,`employee_history`.`skype_id` AS `skype_id`,`employee_history`.`replacement` AS `replacement`,`employee_history`.`last_employer` AS `last_employer`,`employee_history`.`last_employer_address` AS `last_employer_address`,`employee_history`.`account_title` AS `account_title`,`employee_history`.`account_number` AS `account_number`,`employee_history`.`branch_code` AS `branch_code`,`employee_history`.`branch_address` AS `branch_address`,`employee_history`.`joining_notified` AS `joining_notified`,`employee_history`.`c_covid_dose1` AS `c_covid_dose1`,`employee_history`.`c_covid_dose2` AS `c_covid_dose2`,`employee_history`.`shift` AS `shift_id`,`employee_history`.`duty_type` AS `duty_type_id`,`employee_history`.`gender` AS `gender_id`,`employee_history`.`marital_status` AS `marital_status_id`,`employee_history`.`domain_name` AS `domain_name`,`employee_history`.`domain_password` AS `domain_password`,`department`.`department_name` AS `department`,`divisions`.`name` AS `division`,`position`.`position_name` AS `position`,`shift`.`name` AS `shift`,`duty_type`.`type_name` AS `duty_type`,`gender`.`gender_name` AS `gender`,`marital_info`.`marital_sta` AS `martial_status`,concat_ws(' ',`direct_manager`.`prefix`,`direct_manager`.`first_name`,`direct_manager`.`last_name`) AS `direct_manager_name`,concat_ws(' ',`indirect_manager`.`prefix`,`indirect_manager`.`first_name`,`indirect_manager`.`last_name`) AS `indirect_manager_name` from ((((((((((`employee_history` left join `department` on((`employee_history`.`dept_id` = `department`.`dept_id`))) left join `divisions` on((`employee_history`.`division_id` = `divisions`.`id`))) left join `position` on((`position`.`pos_id` = `employee_history`.`pos_id`))) left join `shift` on((`employee_history`.`shift` = `shift`.`id`))) left join `duty_type` on((`employee_history`.`duty_type` = `duty_type`.`id`))) left join `gender` on((`employee_history`.`gender` = `gender`.`id`))) left join `marital_info` on((`employee_history`.`marital_status` = `marital_info`.`id`))) left join `user` on((`employee_history`.`email` = `user`.`email`))) left join `employee_history` `direct_manager` on((`employee_history`.`super_visor_id` = `direct_manager`.`employee_id`))) left join `employee_history` `indirect_manager` on((`employee_history`.`super_visor_id` = `indirect_manager`.`employee_id`)));
