DROP PROCEDURE IF EXISTS `audit_insert_ohrm_job_vacancy`;
DROP PROCEDURE IF EXISTS `audit_update_ohrm_job_vacancy`;
DROP PROCEDURE IF EXISTS `audit_delete_ohrm_job_vacancy`;
DROP PROCEDURE IF EXISTS `audit_insert_ohrm_hiring_managers`;
DROP PROCEDURE IF EXISTS `audit_delete_ohrm_hiring_managers`;

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
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;

  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 action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(get_static_reference_value('ohrm_job_vacancy', 'status', new_status), ' '), '\n');END IF;

   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) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  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
)
BEGIN
  DECLARE action_description TEXT CHARSET UTF8;


  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 action_description = CONCAT(action_description, ' ', 'Status was changed from ', IFNULL(get_static_reference_value('ohrm_job_vacancy', 'status', old_status), ' '), ' to ', IFNULL(get_static_reference_value('ohrm_job_vacancy', 'status', new_status), ' '), '\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) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'CHANGE JOB VACANCY',
      affected_entity_id,
      affected_entity_name,
      action_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
)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;

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

   SET action_description = CONCAT(action_description, ' ', 'Job Vacancy was Deleted');
   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 action_description = CONCAT(action_description, ' ', 'Status: ', IFNULL(get_static_reference_value('ohrm_job_vacancy', 'status', old_status), ' '), '\n');END IF;

   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) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      action_name,
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  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 VARCHAR(600) CHARSET UTF8;
  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) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'CHANGE JOB VACANCY',
      new_vacancy_id,
      affected_entity_name,
      action_description
    );
  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 VARCHAR(600) CHARSET UTF8;

  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) VALUES (
      CURRENT_TIMESTAMP,
      action_owner_id,
      action_owner_name,
      new_version,
      'CHANGE JOB VACANCY',
      affected_entity_id,
      affected_entity_name,
      action_description
    );
  END IF;

END;