-- noinspection SqlNoDataSourceInspectionForFile

DELIMITER $$

DROP TRIGGER IF EXISTS `watch_insert_ohrm_job_vacancy`;$$
DROP PROCEDURE IF EXISTS `audit_insert_ohrm_job_vacancy`;$$

DROP TRIGGER IF EXISTS `watch_insert_ohrm_hiring_managers`;$$
DROP PROCEDURE IF EXISTS `audit_insert_ohrm_hiring_managers`;$$
DROP PROCEDURE IF EXISTS `audit_insert_ohrm_hiring_managers_for_vacancy_template`;$$

DROP TRIGGER IF EXISTS `watch_delete_ohrm_hiring_managers`;$$
DROP PROCEDURE IF EXISTS `audit_delete_ohrm_hiring_managers`;$$
DROP PROCEDURE IF EXISTS `audit_delete_ohrm_hiring_managers_for_vacancy_template`;$$

DROP TRIGGER IF EXISTS `watch_update_ohrm_job_vacancy`;$$
DROP PROCEDURE IF EXISTS `audit_update_ohrm_job_vacancy`;$$

DROP TRIGGER IF EXISTS `watch_delete_ohrm_job_vacancy`;$$
DROP PROCEDURE IF EXISTS `audit_delete_ohrm_job_vacancy`;$$

DROP TRIGGER IF EXISTS `watch_insert_ohrm_job_vacancy_template`;$$
DROP PROCEDURE IF EXISTS `audit_insert_ohrm_job_vacancy_template`;$$

DROP TRIGGER IF EXISTS `watch_update_ohrm_job_vacancy_template`;$$
DROP PROCEDURE IF EXISTS `audit_update_ohrm_job_vacancy_template_from_job_vacancy_update`;$$
DROP PROCEDURE IF EXISTS `audit_update_ohrm_job_vacancy_template`;$$

DROP PROCEDURE IF EXISTS `audit_delete_ohrm_job_vacancy_template_from_job_vacancy`;$$

CREATE TRIGGER watch_insert_ohrm_job_vacancy
AFTER INSERT ON ohrm_job_vacancy
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  IF(NEW.is_template IS NULL ) THEN
    SET new_version = 1;
    SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
    SET affected_entity_name=(SELECT name FROM ohrm_job_vacancy WHERE id = NEW.id LIMIT 1);
    SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
    CALL audit_insert_ohrm_job_vacancy(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, NEW.id, affected_entity_name, NEW.job_title_code, NEW.name , NEW.no_of_positions , NEW.location_id , NEW.sub_unit_id , NEW.status ,  NEW.description, NEW.request_consent, NEW.is_resume_required, NEW.include_archived_candidate_on_dynamic_rule_filter);
  END IF ;
END;$$

CREATE PROCEDURE audit_insert_ohrm_job_vacancy
(
  IN action_owner_id VARCHAR(6),
  IN action_owner_name VARCHAR(300),
  IN action_name VARCHAR(40),
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300),
  IN new_job_title_code INT(4),
  IN new_name VARCHAR(100),
  IN new_no_of_positions INT(13),
  IN new_location_id INT(4),
  IN new_sub_unit_id INT(4),
  IN new_status INT(4),
  IN new_description TEXT CHARSET UTF8,
  IN new_request_consent TINYINT(1),
  IN new_is_resume_required TINYINT(1),
  IN new_include_archived_candidate_on_dynamic_rule_filter TINYINT(1)
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;

  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;

  SET action_description = '';
  -- Start generating the action description

   SET action_description = 'New Job Vacancy was Added\n';
   IF(IFNULL(new_job_title_code,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '),'\n');
   END IF;
   IF(IFNULL(new_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(new_name,' '),'\n');END IF;
   IF(IFNULL(new_no_of_positions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions: ',IFNULL(new_no_of_positions,' '),'\n');END IF;
   IF(IFNULL(new_location_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_id),' '),'\n');
   END IF;
   IF(IFNULL(new_sub_unit_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Sub Unit: ',IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = new_sub_unit_id),' '),'\n');
   END IF;
   IF (IFNULL(new_status,'') != '') THEN
   SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);
   SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@new_status_text, ' '), '\n');
   END IF;
   SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Request Consent option: ', IFNULL(@new_request_consent_text, ' '), '\n');
   SET @new_is_resume_required_text = (SELECT IF(IFNULL(new_is_resume_required,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Resume Required Option: ', IFNULL(@new_is_resume_required_text, ' '), '\n');
   SET @new_include_archived_candidate_on_dynamic_rule_filter_text = (SELECT IF(IFNULL(new_include_archived_candidate_on_dynamic_rule_filter,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Include Archived Candidate Option for the Best Match Rules: ', IFNULL(@new_include_archived_candidate_on_dynamic_rule_filter_text, ' '), '\n');

   IF(IFNULL(new_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(new_description,' '),'\n');END IF;

   -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description,
      app_id,
      app_name,
      screen_name
    );
  END IF;
END;$$

CREATE TRIGGER watch_insert_ohrm_hiring_managers
AFTER INSERT ON ohrm_hiring_managers
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_is_template TINYINT(1);
  DECLARE affected_template_entity_id VARCHAR(20);

  SET affected_entity_is_template=(SELECT is_template FROM ohrm_job_vacancy WHERE id = NEW.vacancy_id LIMIT 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());

  IF (affected_entity_is_template IS NULL) THEN
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_trail` WHERE `affected_entity_id` = NEW.vacancy_id) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET affected_entity_name=(SELECT name FROM ohrm_job_vacancy WHERE id = NEW.vacancy_id LIMIT 1);
    CALL audit_insert_ohrm_hiring_managers(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, affected_entity_name, NEW.vacancy_id, NEW.employee_id);
  ELSEIF (affected_entity_is_template = 1) THEN
    SET affected_template_entity_id=(SELECT id FROM ohrm_vacancy_template_details WHERE vacancy_id = NEW.vacancy_id LIMIT 1);
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_template_trail` WHERE `affected_entity_id` = affected_template_entity_id) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET affected_entity_name=(SELECT name FROM ohrm_vacancy_template_details WHERE vacancy_id = NEW.vacancy_id LIMIT 1);
    CALL audit_insert_ohrm_hiring_managers_for_vacancy_template(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, affected_entity_name, affected_template_entity_id, NEW.employee_id);
  END IF;

END;$$

CREATE PROCEDURE audit_insert_ohrm_hiring_managers
(
  IN action_owner_id VARCHAR(6),
  IN action_owner_name VARCHAR(300),
  IN action_name VARCHAR(40),
  IN new_version INT,
  IN affected_entity_name VARCHAR(300),
  IN new_vacancy_id INT(11),
  IN new_employee_id INT(11)
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;

  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;

  SET action_description = '';
  -- Start generating the action description
  SET action_description = 'New Hiring Manager was Added for Job Vacancy\n';
  IF(IFNULL(affected_entity_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(affected_entity_name,' '),'\n');END IF;
  IF(IFNULL(new_employee_id,'') != '') THEN
  SET action_description = CONCAT(action_description, ' ', 'Hiring Manager Name : ',IFNULL((SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number =  new_employee_id),' '),'\n');
  END IF;
  -- End generating the action description
  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'CHANGE JOB VACANCY',
      new_vacancy_id,
      affected_entity_name,
      action_description,
      app_id,
      app_name,
      screen_name
    );
  END IF;
END;$$

CREATE PROCEDURE audit_insert_ohrm_hiring_managers_for_vacancy_template
  (
    IN action_owner_id VARCHAR(6),
    IN action_owner_name VARCHAR(300),
    IN action_name VARCHAR(40),
    IN new_version INT,
    IN affected_entity_name VARCHAR(300),
    IN new_vacancy_template_id INT(11),
    IN new_employee_id INT(11)
  )
  BEGIN
    DECLARE action_description TEXT CHARSET UTF8;

    DECLARE app_id INT;
    DECLARE app_name VARCHAR(1000) CHARSET UTF8;
    DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

    SET app_id = @provenience_app_id;
    SET app_name = @provenience_app_name;
    SET screen_name = @provenience_screen_name;

    SET action_description = '';
    -- Start generating the action description
    SET action_description = 'New Hiring Manager was Added for Job Vacancy Template\n';
    IF(IFNULL(affected_entity_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Template Name: ',IFNULL(affected_entity_name,' '),'\n');END IF;
    IF(IFNULL(new_employee_id,'') != '') THEN
      SET action_description = CONCAT(action_description, ' ', 'Hiring Manager Name : ',IFNULL((SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number =  new_employee_id),' '),'\n');
    END IF;
    -- End generating the action description
    IF (action_description != '') THEN
      INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
        CURRENT_TIMESTAMP,
        action_owner_id,
        action_owner_name,
        new_version,
        'UPDATE VACANCY TEMPLATE',
        new_vacancy_template_id,
        affected_entity_name,
        action_description,
        app_id,
        app_name,
        screen_name

      );
    END IF;
  END;$$


CREATE TRIGGER watch_delete_ohrm_hiring_managers
BEFORE DELETE ON ohrm_hiring_managers
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_is_template TINYINT(1);
  DECLARE affected_template_entity_id VARCHAR(20);

  SET affected_entity_is_template=(SELECT is_template FROM ohrm_job_vacancy WHERE id = OLD.vacancy_id LIMIT 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());

  IF (affected_entity_is_template IS NULL) THEN
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_trail` WHERE `affected_entity_id` = OLD.vacancy_id) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET affected_entity_name=(SELECT name FROM ohrm_job_vacancy WHERE id = OLD.vacancy_id LIMIT 1);
    CALL audit_delete_ohrm_hiring_managers(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, OLD.vacancy_id, affected_entity_name, OLD.employee_id);
  ELSEIF (affected_entity_is_template = 1) THEN
    SET affected_template_entity_id=(SELECT id FROM ohrm_vacancy_template_details WHERE vacancy_id = OLD.vacancy_id LIMIT 1);
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_template_trail` WHERE `affected_entity_id` = affected_template_entity_id) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET affected_entity_name=(SELECT name FROM ohrm_vacancy_template_details WHERE vacancy_id = OLD.vacancy_id LIMIT 1);
    CALL audit_delete_ohrm_hiring_managers_for_vacancy_template(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, affected_entity_name, affected_template_entity_id, OLD.employee_id);
  END IF;
END;$$

CREATE PROCEDURE audit_delete_ohrm_hiring_managers
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_employee_id INT (11)
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;

  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;

  SET action_description = '';
  -- Start generating the action description
   SET action_description = 'Hiring Manager was Deleted for Job Vacancy\n';
   IF(IFNULL(affected_entity_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(affected_entity_name,' '),'\n');END IF;
   IF(IFNULL(old_employee_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Hiring Manager Name : ',IFNULL((SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number =  old_employee_id),' '),'\n');
   END IF;
  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'CHANGE JOB VACANCY',
      affected_entity_id,
      affected_entity_name,
      action_description,
      app_id,
      app_name,
      screen_name
    );
  END IF;
END;$$

CREATE PROCEDURE audit_delete_ohrm_hiring_managers_for_vacancy_template
  (
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name VARCHAR(300) CHARSET UTF8,
    IN action_name VARCHAR(40) CHARSET UTF8,
    IN new_version INT,
    IN affected_entity_id INT(11),
    IN affected_entity_name VARCHAR(300) CHARSET UTF8,
    IN old_employee_id INT (11)
  )
  BEGIN
    DECLARE action_description TEXT CHARSET UTF8;

    DECLARE app_id INT;
    DECLARE app_name VARCHAR(1000) CHARSET UTF8;
    DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

    SET app_id = @provenience_app_id;
    SET app_name = @provenience_app_name;
    SET screen_name = @provenience_screen_name;

    SET action_description = '';
    -- Start generating the action description
    SET action_description = 'Hiring Manager was Deleted for Job Vacancy Template\n';
    IF(IFNULL(affected_entity_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Template Name: ',IFNULL(affected_entity_name,' '),'\n');END IF;
    IF(IFNULL(old_employee_id,'') != '') THEN
      SET action_description = CONCAT(action_description, ' ', 'Hiring Manager Name : ',IFNULL((SELECT CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM hs_hr_employee WHERE hs_hr_employee.emp_number =  old_employee_id),' '),'\n');
    END IF;
    -- End generating the action description

    IF (action_description != '') THEN
      INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
        CURRENT_TIMESTAMP,
        action_owner_id,
        action_owner_name,
        new_version,
        'UPDATE VACANCY TEMPLATE',
        affected_entity_id,
        affected_entity_name,
        action_description,
        app_id,
        app_name,
        screen_name
      );
    END IF;
  END;$$


CREATE TRIGGER watch_update_ohrm_job_vacancy
AFTER UPDATE ON ohrm_job_vacancy
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  IF (NEW.is_template IS NULL ) THEN
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_trail` WHERE `affected_entity_id` = OLD.id) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET affected_entity_name=(SELECT name FROM ohrm_job_vacancy WHERE id = OLD.id LIMIT 1);
    CALL audit_update_ohrm_job_vacancy(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, OLD.id, affected_entity_name, NEW.job_title_code, OLD.job_title_code, NEW.name, OLD.name, NEW.no_of_positions, OLD.no_of_positions, NEW.location_id, OLD.location_id, NEW.sub_unit_id, OLD.sub_unit_id, NEW.status, OLD.status,  NEW.description, OLD.description, NEW.request_consent, OLD.request_consent, NEW.is_resume_required, OLD.is_resume_required, NEW.include_archived_candidate_on_dynamic_rule_filter, OLD.include_archived_candidate_on_dynamic_rule_filter);
  ELSEIF (NEW.is_template =1) THEN
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_template_trail` WHERE `affected_entity_id` = OLD.id) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET affected_entity_name=(SELECT name FROM ohrm_vacancy_template_details WHERE vacancy_id = OLD.id LIMIT 1);
    CALL audit_update_ohrm_job_vacancy_template_from_job_vacancy_update(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, OLD.id, affected_entity_name, NEW.job_title_code, OLD.job_title_code, NEW.name, OLD.name, NEW.no_of_positions, OLD.no_of_positions, NEW.location_id, OLD.location_id, NEW.sub_unit_id, OLD.sub_unit_id, NEW.status, OLD.status,  NEW.description, OLD.description, NEW.request_consent, OLD.request_consent, NEW.is_resume_required, OLD.is_resume_required);
  END IF ;
  END;$$

CREATE PROCEDURE audit_update_ohrm_job_vacancy
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_job_title_code INT(4),
  IN old_job_title_code INT(4),
  IN new_name VARCHAR(100) CHARSET UTF8,
  IN old_name VARCHAR(100) CHARSET UTF8,
  IN new_no_of_positions INT(13),
  IN old_no_of_positions INT(13),
  IN new_location_id INT(4),
  IN old_location_id INT(4),
  IN new_sub_unit_id INT(4),
  IN old_sub_unit_id INT(4),
  IN new_status INT(4),
  IN old_status INT(4),
  IN new_description TEXT CHARSET UTF8,
  IN old_description TEXT CHARSET UTF8,
  IN new_request_consent TINYINT(1),
  IN old_request_consent TINYINT(1),
  IN new_is_resume_required TINYINT(1),
  IN old_is_resume_required TINYINT(1),
  IN new_include_archived_candidate_on_dynamic_rule_filter TINYINT(1),
  IN old_include_archived_candidate_on_dynamic_rule_filter TINYINT(1)
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;

  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;

  SET action_description = '';
  -- Start generating the action description

  IF (IFNULL(old_job_title_code,'') != IFNULL(new_job_title_code,'')) THEN
      SET action_description = CONCAT(action_description, ' ', 'Job Title was changed from ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '), ' to ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '), '\n');
  END IF;
  IF (IFNULL(old_name,'') != IFNULL(new_name,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name was changed from ', IFNULL(old_name, ' '), ' to ', IFNULL(new_name, ' '), '\n');END IF;
  IF (IFNULL(old_no_of_positions,'') != IFNULL(new_no_of_positions,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions was changed from ', IFNULL(old_no_of_positions, ' '), ' to ', IFNULL(new_no_of_positions, ' '), '\n');END IF;
  IF (IFNULL(old_location_id,'') != IFNULL(new_location_id,'')) THEN
      SET action_description = CONCAT(action_description, ' ', 'Location was changed from ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_id),' '), '\n');
  END IF;
  IF (IFNULL(old_sub_unit_id,'') != IFNULL(new_sub_unit_id,'')) THEN
      SET action_description = CONCAT(action_description, ' ', 'Sub Unit was changed from ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = new_sub_unit_id),' '), '\n');
  END IF;
  IF (IFNULL(old_status,'') != IFNULL(new_status,'')) THEN
      SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);
      SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);
      SET action_description = CONCAT(action_description, ' ', 'Status was changed from ', IFNULL(@old_status_text, ' '), ' to ', IFNULL(@new_status_text, ' '), '\n');
  END IF;
  IF (old_request_consent != new_request_consent) THEN
      SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));
      SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));
      SET action_description = CONCAT(action_description, ' ', 'Request Consent option was changed from ', IFNULL(@old_request_consent_text, ' '), ' to ', IFNULL(@new_request_consent_text, ' '), '\n');
  END IF;
  IF (old_is_resume_required != new_is_resume_required) THEN
      SET @old_is_resume_required_text = (SELECT IF(IFNULL(old_is_resume_required,0)=1,'Enabled','Disabled'));
      SET @new_is_resume_required_text = (SELECT IF(IFNULL(new_is_resume_required,0)=1,'Enabled','Disabled'));
      SET action_description = CONCAT(action_description, ' ', 'Resume Required Option was changed from ', IFNULL(@old_is_resume_required_text, ' '), ' to ', IFNULL(@new_is_resume_required_text, ' '), '\n');
  END IF;

  IF (old_include_archived_candidate_on_dynamic_rule_filter != new_include_archived_candidate_on_dynamic_rule_filter) THEN
      SET @old_include_archived_candidate_on_dynamic_rule_filter_text = (SELECT IF(IFNULL(old_include_archived_candidate_on_dynamic_rule_filter,0)=1,'Enabled','Disabled'));
      SET @new_include_archived_candidate_on_dynamic_rule_filter_text = (SELECT IF(IFNULL(new_include_archived_candidate_on_dynamic_rule_filter,0)=1,'Enabled','Disabled'));
      SET action_description = CONCAT(action_description, ' ', 'Include Archived Candidate Option was changed from ', IFNULL(@old_include_archived_candidate_on_dynamic_rule_filter_text, ' '), ' to ', IFNULL(@new_include_archived_candidate_on_dynamic_rule_filter_text, ' '), ' for the Best Match Rules\n');
  END IF;

  IF (IFNULL(old_description,'') != IFNULL(new_description,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Description was changed from ', resolve_empty(old_description), ' to ', resolve_empty(new_description), '\n');END IF;

-- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'CHANGE JOB VACANCY',
      affected_entity_id,
      affected_entity_name,
      action_description,
      app_id,
      app_name,
      screen_name

    );
  END IF;
END;$$

CREATE PROCEDURE audit_update_ohrm_job_vacancy_template_from_job_vacancy_update
  (
    IN action_owner_id VARCHAR(6) CHARSET UTF8,
    IN action_owner_name VARCHAR(300) CHARSET UTF8,
    IN action_name VARCHAR(40) CHARSET UTF8,
    IN new_version INT,
    IN affected_entity_id INT(11),
    IN affected_entity_name VARCHAR(300) CHARSET UTF8,
    IN new_job_title_code INT(4),
    IN old_job_title_code INT(4),
    IN new_name VARCHAR(100) CHARSET UTF8,
    IN old_name VARCHAR(100) CHARSET UTF8,
    IN new_no_of_positions INT(13),
    IN old_no_of_positions INT(13),
    IN new_location_id INT(4),
    IN old_location_id INT(4),
    IN new_sub_unit_id INT(4),
    IN old_sub_unit_id INT(4),
    IN new_status INT(4),
    IN old_status INT(4),
    IN new_description TEXT CHARSET UTF8,
    IN old_description TEXT CHARSET UTF8,
    IN new_request_consent TINYINT(1),
    IN old_request_consent TINYINT(1),
    IN new_is_resume_required TINYINT(1),
    IN old_is_resume_required TINYINT(1)
  )
  BEGIN
    DECLARE action_description TEXT CHARSET UTF8;
    DECLARE app_id INT;
    DECLARE app_name VARCHAR(1000) CHARSET UTF8;
    DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

    SET app_id = @provenience_app_id;
    SET app_name = @provenience_app_name;
    SET screen_name = @provenience_screen_name;

    SET action_description = '';
    -- Start generating the action description

    SET action_description = CONCAT(action_description, ' ', 'Vacancy Template was Changed ', '\n');
    IF (IFNULL(old_job_title_code,'') != IFNULL(new_job_title_code,'')) THEN
      SET action_description = CONCAT(action_description, ' ', 'Job Title was changed from ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '), ' to ', IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '), '\n');
    END IF;
    IF (IFNULL(old_name,'') != IFNULL(new_name,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name was changed from ', IFNULL(old_name, ' '), ' to ', IFNULL(new_name, ' '), '\n');END IF;
    IF (IFNULL(old_no_of_positions,'') != IFNULL(new_no_of_positions,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions was changed from ', IFNULL(old_no_of_positions, ' '), ' to ', IFNULL(new_no_of_positions, ' '), '\n');END IF;
    IF (IFNULL(old_location_id,'') != IFNULL(new_location_id,'')) THEN
      SET action_description = CONCAT(action_description, ' ', 'Location was changed from ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_id),' '), '\n');
    END IF;
    IF (IFNULL(old_sub_unit_id,'') != IFNULL(new_sub_unit_id,'')) THEN
      SET action_description = CONCAT(action_description, ' ', 'Sub Unit was changed from ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = new_sub_unit_id),' '), '\n');
    END IF;
    IF (IFNULL(old_status,'') != IFNULL(new_status,'')) THEN
      SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);
      SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);
      SET action_description = CONCAT(action_description, ' ', 'Status was changed from ', IFNULL(@old_status_text, ' '), ' to ', IFNULL(@new_status_text, ' '), '\n');
    END IF;
    IF (old_request_consent != new_request_consent) THEN
      SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));
      SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));
      SET action_description = CONCAT(action_description, ' ', 'Request Consent option was changed from ', IFNULL(@old_request_consent_text, ' '), ' to ', IFNULL(@new_request_consent_text, ' '), '\n');
    END IF;
    IF (old_is_resume_required != new_is_resume_required) THEN
      SET @old_is_resume_required_text = (SELECT IF(IFNULL(old_is_resume_required,0)=1,'Enabled','Disabled'));
      SET @new_is_resume_required_text = (SELECT IF(IFNULL(new_is_resume_required,0)=1,'Enabled','Disabled'));
      SET action_description = CONCAT(action_description, ' ', 'Resume Required Option was changed from ', IFNULL(@old_is_resume_required_text, ' '), ' to ', IFNULL(@new_is_resume_required_text, ' '), '\n');
    END IF;

    IF (IFNULL(old_description,'') != IFNULL(new_description,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Description was changed from ', resolve_empty(old_description), ' to ', resolve_empty(new_description), '\n');END IF;

    -- End generating the action description

    IF (action_description != '') THEN
      INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
        CURRENT_TIMESTAMP,
        action_owner_id,
        action_owner_name,
        new_version,
        'UPDATE VACANCY TEMPLATE',
        affected_entity_id,
        affected_entity_name,
        action_description,
        app_id,
        app_name,
        screen_name
      );
    END IF;
  END;$$


CREATE TRIGGER watch_delete_ohrm_job_vacancy
BEFORE DELETE ON ohrm_job_vacancy
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE template_name VARCHAR(100) CHARSET UTF8;
  DECLARE template_description TEXT CHARSET UTF8;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  IF (OLD.is_template IS NULL ) THEN
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_trail` WHERE `affected_entity_id` = OLD.id) + 1;
    SET new_version = IFNULL(new_version, 1);
    SET affected_entity_name=(SELECT name FROM ohrm_job_vacancy WHERE id = OLD.id LIMIT 1);
    CALL audit_delete_ohrm_job_vacancy(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, OLD.id, affected_entity_name, OLD.job_title_code, OLD.name , OLD.no_of_positions , OLD.location_id , OLD.sub_unit_id , OLD.status ,  OLD.description, OLD.request_consent, OLD.is_resume_required, OLD.include_archived_candidate_on_dynamic_rule_filter);
    CALL archive_reference('ohrm_job_vacancy', OLD.id, CONCAT(OLD.name, ' - ', (IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = OLD.job_title_code), 'NULL'))));
  ELSEIF (OLD.is_template =1) THEN
    SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_template_trail` WHERE `affected_entity_id` = OLD.id) + 1;
    SET template_name = (SELECT name FROM `ohrm_vacancy_template_details` WHERE `vacancy_id` = OLD.id);
    SET template_description = (SELECT description FROM `ohrm_vacancy_template_details` WHERE `vacancy_id` = OLD.id);
    SET new_version = IFNULL(new_version, 1);
    SET affected_entity_name=(SELECT name FROM ohrm_vacancy_template_details WHERE vacancy_id = OLD.id LIMIT 1);
    CALL audit_delete_ohrm_job_vacancy_template_from_job_vacancy(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, OLD.id, affected_entity_name, OLD.job_title_code, OLD.name , OLD.no_of_positions , OLD.location_id , OLD.sub_unit_id , OLD.status ,  OLD.description, OLD.request_consent, OLD.is_resume_required, template_name, template_description);
  END IF ;
END;$$

CREATE PROCEDURE audit_delete_ohrm_job_vacancy
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_job_title_code INT(4),
  IN old_name VARCHAR(100),
  IN old_no_of_positions INT(13),
  IN old_location_id INT(4),
  IN old_sub_unit_id INT(4),
  IN old_status INT(4),
  IN old_description TEXT CHARSET UTF8,
  IN old_request_consent TINYINT(1),
  IN old_is_resume_required TINYINT(1),
  IN old_include_archived_candidate_on_dynamic_rule_filter TINYINT(1)
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;

  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;

  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Job Vacancy was Deleted\n');
   IF(IFNULL(old_job_title_code,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '),'\n');
   END IF;
   IF(IFNULL(old_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(old_name,' '),'\n');END IF;
   IF(IFNULL(old_no_of_positions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions: ',IFNULL(old_no_of_positions,' '),'\n');END IF;
   IF(IFNULL(old_location_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '),'\n');
   END IF;
   IF(IFNULL(old_sub_unit_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Sub Unit: ',IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '),'\n');
   END IF;
   IF (IFNULL(old_status,'') != '') THEN
   SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);
   SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@old_status_text, ' '), '\n');
   END IF;
   SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Request Consent was: ', IFNULL(@old_request_consent_text, ' '), '\n');
   SET @old_is_resume_required_text = (SELECT IF(IFNULL(old_is_resume_required,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Resume Required Option was: ', IFNULL(@old_is_resume_required_text, ' '), '\n');
   SET @old_include_archived_candidate_on_dynamic_rule_filter_text = (SELECT IF(IFNULL(old_include_archived_candidate_on_dynamic_rule_filter,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Include Archived Candidate Option for the Best Match Rules was: ', IFNULL(@old_include_archived_candidate_on_dynamic_rule_filter_text, ' '), '\n');

   IF(IFNULL(old_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(old_description,' '),'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description,
      app_id,
      app_name,
      screen_name
    );
  END IF;
END;$$

CREATE PROCEDURE audit_delete_ohrm_job_vacancy_template_from_job_vacancy
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(40) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_job_title_code INT(4),
  IN old_name VARCHAR(100),
  IN old_no_of_positions INT(13),
  IN old_location_id INT(4),
  IN old_sub_unit_id INT(4),
  IN old_status INT(4),
  IN old_description TEXT CHARSET UTF8,
  IN old_request_consent TINYINT(1),
  IN old_is_resume_required TINYINT(1),
  IN template_name VARCHAR(100),
  IN template_description TEXT CHARSET UTF8
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;

  DECLARE app_id INT;
  DECLARE app_name VARCHAR(1000) CHARSET UTF8;
  DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

  SET app_id = @provenience_app_id;
  SET app_name = @provenience_app_name;
  SET screen_name = @provenience_screen_name;

  SET action_description = '';
  -- Start generating the action description

   SET action_description = CONCAT(action_description, ' ', 'Job Vacancy Template was Deleted\n');
  IF(IFNULL(old_job_title_code,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = old_job_title_code),' '),'\n');
   END IF;
   IF(IFNULL(old_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ',IFNULL(old_name,' '),'\n');END IF;
   IF(IFNULL(old_no_of_positions,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Number of Positions: ',IFNULL(old_no_of_positions,' '),'\n');END IF;
   IF(IFNULL(old_location_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = old_location_id),' '),'\n');
   END IF;
   IF(IFNULL(old_sub_unit_id,'') != '') THEN
   SET action_description = CONCAT(action_description, ' ', 'Sub Unit: ',IFNULL((SELECT `name` FROM `ohrm_subunit` WHERE `id` = old_sub_unit_id),' '),'\n');
   END IF;
   IF (IFNULL(old_status,'') != '') THEN
   SET @old_status_text = (select name from ohrm_job_vacancy_status where status = old_status);
   SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@old_status_text, ' '), '\n');
   END IF;
   SET @old_request_consent_text = (SELECT IF(IFNULL(old_request_consent,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Request Consent was: ', IFNULL(@old_request_consent_text, ' '), '\n');
   SET @old_is_resume_required_text = (SELECT IF(IFNULL(old_is_resume_required,0)=1,'Enabled','Disabled'));
   SET action_description = CONCAT(action_description, ' ', 'Resume Required Option was: ', IFNULL(@old_is_resume_required_text, ' '), '\n');
   IF(IFNULL(old_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Job Posting Description: ',IFNULL(old_description,' '),'\n');END IF;
   IF(IFNULL(template_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Template Name: ',IFNULL(template_name,' '),'\n');END IF;
   IF(IFNULL(template_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(template_description,' '),'\n');END IF;

  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'DELETE VACANCY TEMPLATE',
      affected_entity_id,
      affected_entity_name,
      action_description,
      app_id,
      app_name,
      screen_name
    );
  END IF;
END;$$

CREATE TRIGGER watch_insert_ohrm_job_vacancy_template
AFTER INSERT ON ohrm_vacancy_template_details
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE job_title INT(4);
  DECLARE location INT(4);
  DECLARE status INT(4);
  DECLARE vacancy_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE request_consent INT(4);
  DECLARE is_resume_required INT(4);
  SET new_version = 1;
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT name FROM ohrm_vacancy_template_details WHERE id = NEW.id LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  SET job_title = (SELECT ohrm_job_vacancy.job_title_code FROM ohrm_job_vacancy WHERE ohrm_job_vacancy.id=NEW.vacancy_id LIMIT 1);
  SET location = (SELECT ohrm_job_vacancy.location_id FROM ohrm_job_vacancy WHERE ohrm_job_vacancy.id=NEW.vacancy_id LIMIT 1);
  SET status = (SELECT ohrm_job_vacancy.status FROM ohrm_job_vacancy WHERE ohrm_job_vacancy.id=NEW.vacancy_id LIMIT 1);
  SET vacancy_name = (SELECT ohrm_job_vacancy.name FROM ohrm_job_vacancy WHERE ohrm_job_vacancy.id=NEW.vacancy_id LIMIT 1);
  SET request_consent = (SELECT ohrm_job_vacancy.request_consent FROM ohrm_job_vacancy WHERE ohrm_job_vacancy.id=NEW.vacancy_id LIMIT 1);
  SET is_resume_required = (SELECT ohrm_job_vacancy.is_resume_required FROM ohrm_job_vacancy WHERE ohrm_job_vacancy.id=NEW.vacancy_id LIMIT 1);
  CALL audit_insert_ohrm_job_vacancy_template(@orangehrm_user, orangehrm_user_name, @orangehrm_action_name, new_version, job_title,location,status,vacancy_name,request_consent,NEW.id, affected_entity_name, NEW.name ,  NEW.description, is_resume_required);
END;$$

CREATE PROCEDURE audit_insert_ohrm_job_vacancy_template
  (
    IN action_owner_id VARCHAR(6),
    IN action_owner_name VARCHAR(300),
    IN action_name VARCHAR(40),
    IN new_version INT,
    IN new_job_title_code INT(4),
    IN new_location_code INT(4),
    IN new_status INT(4),
    IN new_vacancy_name VARCHAR(100),
    IN new_request_consent INT(4),
    IN affected_entity_id INT(11),
    IN affected_entity_name VARCHAR(300),
    IN new_name VARCHAR(100),
    IN new_description TEXT CHARSET UTF8,
    IN new_is_resume_required TINYINT(1)
  )
  BEGIN
    DECLARE action_description TEXT CHARSET UTF8;

    DECLARE app_id INT;
    DECLARE app_name VARCHAR(1000) CHARSET UTF8;
    DECLARE screen_name VARCHAR(1000) CHARSET UTF8;

    SET app_id = @provenience_app_id;
    SET app_name = @provenience_app_name;
    SET screen_name = @provenience_screen_name;

    SET action_description = '';
    -- Start generating the action description

    SET action_description = 'New Job Vacancy Template was Added\n';
    IF(IFNULL(new_name,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Template Name: ',IFNULL(new_name,' '),'\n');
    END IF;
    IF(IFNULL(new_vacancy_name,'') != '') THEN
      SET action_description = CONCAT(action_description, ' ', 'Vacancy Name: ', IFNULL(new_vacancy_name,' '),'\n');
    END IF;
    IF(IFNULL(new_job_title_code,'') != '') THEN
      SET action_description = CONCAT(action_description, ' ', 'Job Title: ',IFNULL((SELECT `job_title` FROM `ohrm_job_title` WHERE `id` = new_job_title_code),' '),'\n');
    END IF;
    IF(IFNULL(new_location_code,'') != '') THEN
      SET action_description = CONCAT(action_description, ' ', 'Location: ',IFNULL((SELECT `name` FROM `ohrm_location` WHERE `id` = new_location_code),' '),'\n');
    END IF;
    IF (IFNULL(new_status,'') != '') THEN
      SET @new_status_text = (select name from ohrm_job_vacancy_status where status = new_status);
      SET action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(@new_status_text, ' '), '\n');
    END IF;
    SET @new_request_consent_text = (SELECT IF(IFNULL(new_request_consent,0)=1,'Enabled','Disabled'));
    SET action_description = CONCAT(action_description, ' ', 'Request Consent option: ', IFNULL(@new_request_consent_text, ' '), '\n');
    SET @new_is_resume_required_text = (SELECT IF(IFNULL(new_is_resume_required,0)=1,'Enabled','Disabled'));
    SET action_description = CONCAT(action_description, ' ', 'Resume Required Option: ', IFNULL(@new_is_resume_required_text, ' '), '\n');


    IF(IFNULL(new_description,'') != '') THEN SET action_description = CONCAT(action_description, ' ', 'Description: ',IFNULL(new_description,' '),'\n');END IF;

    -- End generating the action description

    IF (action_description != '') THEN
      INSERT INTO `ohrm_audittrail_recruitment_job_vacancy_template_trail` (action_time,action_owner_id,action_owner_name,version_id,action,affected_entity_id,affected_entity_name,action_description,app_id,app_name,screen_name) VALUES (
        CURRENT_TIMESTAMP,
        action_owner_id,
        action_owner_name,
        new_version,
        'ADD VACANCY TEMPLATE',
        affected_entity_id,
        affected_entity_name,
        action_description,
        app_id,
        app_name,
        screen_name
      );
    END IF;
  END;$$

CREATE TRIGGER watch_update_ohrm_job_vacancy_template
AFTER UPDATE ON ohrm_vacancy_template_details
FOR EACH ROW BEGIN
  DECLARE new_version INT;
  DECLARE orangehrm_user_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE affected_entity_name VARCHAR(300 ) CHARSET UTF8;
  DECLARE current_action_description TEXT CHARSET UTF8;

  SET new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_recruitment_job_vacancy_template_trail` WHERE `affected_entity_id` = OLD.id);
  SET current_action_description = (SELECT action_description FROM ohrm_audittrail_recruitment_job_vacancy_template_trail WHERE affected_entity_id = NEW.id AND version_id= new_version LIMIT 1);
  SET orangehrm_user_name=(SELECT IFNULL(CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname),ohrm_user.user_name) FROM ohrm_user LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_user.emp_number WHERE ohrm_user.id = @orangehrm_user );
  SET affected_entity_name=(SELECT name FROM ohrm_vacancy_template_details WHERE id = NEW.id LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_ohrm_job_vacancy_template(new_version,NEW.id, NEW.name ,OLD.name,  NEW.description, OLD.description, current_action_description);
END;$$

CREATE PROCEDURE audit_update_ohrm_job_vacancy_template
  (
    IN new_version INT,
    IN affected_entity_id INT(11),
    IN new_name VARCHAR(100),
    IN old_name VARCHAR(100),
    IN new_description TEXT CHARSET UTF8,
    IN old_description TEXT CHARSET UTF8,
    IN current_action_description TEXT CHARSET UTF8
  )
BEGIN
    DECLARE action_description TEXT CHARSET UTF8;

    SET action_description = '';
#     Start generating the action description

  SET action_description = CONCAT(action_description, ' ',current_action_description);


  IF (IFNULL(old_name,'') != IFNULL(new_name,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Vacancy Template Name was changed from ', IFNULL(old_name, ' '), ' to ', IFNULL(new_name, ' '), '\n');END IF;

  IF (IFNULL(old_description,'') != IFNULL(new_description,'')) THEN SET action_description = CONCAT(action_description, ' ', 'Description was changed from ', resolve_empty(old_description), ' to ', resolve_empty(new_description), '\n');END IF;

#   End generating the action description

    IF (action_description != '') THEN
      UPDATE `ohrm_audittrail_recruitment_job_vacancy_template_trail` SET action_description = action_description WHERE affected_entity_id= affected_entity_id AND version_id = new_version;
    END IF;
  END;$$

DELIMITER ;