DELIMITER $$

DROP TRIGGER IF EXISTS `watch_insert_ohrm_employee_work_week`;$$
DROP PROCEDURE IF EXISTS `audit_insert_ohrm_employee_work_week`;$$

DROP TRIGGER IF EXISTS `watch_update_ohrm_employee_work_week`;$$
DROP PROCEDURE IF EXISTS `audit_update_ohrm_employee_work_week`;$$

DROP TRIGGER IF EXISTS `watch_delete_ohrm_employee_work_week`;$$
DROP PROCEDURE IF EXISTS `audit_delete_ohrm_employee_work_week`;$$



CREATE TRIGGER watch_insert_ohrm_employee_work_week
AFTER INSERT ON ohrm_employee_work_week
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 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 CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM ohrm_employee_work_week LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_employee_work_week.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_insert_ohrm_employee_work_week(@orangehrm_user, orangehrm_user_name, 'ADD WORK WEEK DETAILS', new_version, NEW.emp_number, affected_entity_name, NEW.day_id, NEW.hours_per_day, NEW.shift_start_time, NEW.shift_end_time, NEW.lunch_start_time, NEW.lunch_end_time, NEW.day_type_id );
END;$$

CREATE PROCEDURE audit_insert_ohrm_employee_work_week
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11), 
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_day_id INT(11),  
  IN new_hours_per_day DECIMAL(7,5),  
  IN new_shift_start_time TIME,  
  IN new_shift_end_time TIME,
  IN lunch_start_time TIME,
  IN lunch_end_time TIME,
  IN new_day_type_id INT(11)
  
)
BEGIN
  DECLARE action_description VARCHAR(800) CHARSET UTF8;


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

   SET action_description = IFNULL( CONCAT(action_description, ' ', 'New Work Week Day was added\n'),'');
   SET action_description = IFNULL( CONCAT(action_description, ' ', 'Day: ',(SELECT `name` FROM `ohrm_week_days` WHERE `id` = new_day_id),'\n'),'');
   IF (new_hours_per_day != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Duration: ',new_hours_per_day,'\n'),''); END IF;
   IF (new_shift_start_time != '') THEN SET action_description = CONCAT(action_description, ' ', 'Shift In: ',new_shift_start_time,'\n');END IF;
   IF (new_shift_end_time != '') THEN SET action_description = CONCAT(action_description, ' ', 'Shift Out: ',new_shift_end_time,'\n');END IF;
   IF (lunch_start_time != '') THEN SET action_description = CONCAT(action_description, ' ', 'Lunch In: ',lunch_start_time,'\n');END IF;
   IF (lunch_end_time != '') THEN SET action_description = CONCAT(action_description, ' ', 'Lunch Out: ',lunch_end_time,'\n');END IF;
   SET action_description = CONCAT(action_description, ' ', 'Day Type: ',(SELECT `name` FROM `ohrm_day_type` WHERE `id` = new_day_type_id),'\n');


  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_work_week_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 TRIGGER watch_update_ohrm_employee_work_week
AFTER UPDATE ON ohrm_employee_work_week
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 new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_work_week_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(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 CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM ohrm_employee_work_week LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_employee_work_week.emp_number WHERE hs_hr_employee.emp_number = NEW.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_update_ohrm_employee_work_week(@orangehrm_user,orangehrm_user_name, 'UPDATE WORK WEEK DETAILS', new_version, NEW.emp_number,affected_entity_name, NEW.day_id, OLD.day_id, NEW.hours_per_day, OLD.hours_per_day, NEW.shift_start_time, OLD.shift_start_time, NEW.shift_end_time, OLD.shift_end_time, NEW.lunch_start_time, OLD.lunch_start_time, NEW.lunch_end_time, OLD.lunch_end_time, NEW.day_type_id, OLD.day_type_id);
END;$$

CREATE PROCEDURE audit_update_ohrm_employee_work_week
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11),
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN new_day_id INT(11),
  IN old_day_id INT(11),
  IN new_hours_per_day DECIMAL(7,5),  
  IN old_hours_per_day DECIMAL(7,5),     
  IN new_shift_start_time TIME,  
  IN old_shift_start_time TIME,
  IN new_shift_end_time TIME,
  IN old_shift_end_time TIME,
  IN new_lunch_start_time TIME,
  IN old_lunch_start_time TIME,
  IN new_lunch_end_time TIME,
  IN old_lunch_end_time TIME,
  IN new_day_type_id INT(11),
  IN old_day_type_id INT(11)

)
BEGIN
  DECLARE action_description VARCHAR(600) CHARSET UTF8;

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

  
  IF (old_hours_per_day != new_hours_per_day) THEN SET action_description = CONCAT(action_description, ' ', 'Duration has changed from ', IFNULL(old_hours_per_day, ' '), ' to ', IFNULL(new_hours_per_day, ' '), '\n');END IF;
  IF (old_shift_start_time != new_shift_start_time) THEN SET action_description = CONCAT(action_description, ' ', 'Shift In was changed from ', IFNULL(old_shift_start_time, ' '), ' to ', IFNULL(new_shift_start_time, ' '), '\n');END IF;
  IF (old_shift_end_time != new_shift_end_time) THEN SET action_description = CONCAT(action_description, ' ', 'Shift Out was changed from ', IFNULL(old_shift_end_time, ' '), ' to ', IFNULL(new_shift_end_time, ' '), '\n');END IF;
  IF (old_lunch_start_time != new_lunch_start_time) THEN SET action_description = CONCAT(action_description, ' ', 'Lunch In was changed from ', IFNULL(old_lunch_start_time, ' '), ' to ', IFNULL(new_lunch_start_time, ' '), '\n');END IF;
  IF (old_lunch_end_time != new_lunch_end_time) THEN SET action_description = CONCAT(action_description, ' ', 'Lunch Out was changed from ', IFNULL(old_lunch_end_time, ' '), ' to ', IFNULL(new_lunch_end_time, ' '), '\n');END IF;
  IF (old_day_type_id != new_day_type_id) THEN SET action_description = CONCAT(action_description, ' ', 'Day Type was changed from ', IFNULL((SELECT `name` FROM `ohrm_day_type` WHERE `id` = old_day_type_id), ' '), ' to ', IFNULL((SELECT `name` FROM `ohrm_day_type` WHERE `id` = new_day_type_id), ' '), '\n');END IF;
  -- End generating the action description

  IF (action_description != '') THEN
    SET action_description = IFNULL( CONCAT('(Day: ',(SELECT `name` FROM `ohrm_week_days` WHERE `id` = old_day_id),')\n', action_description),'');
    INSERT INTO `ohrm_audittrail_pim_work_week_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 TRIGGER watch_delete_ohrm_employee_work_week
BEFORE DELETE ON ohrm_employee_work_week
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 new_version = (SELECT MAX(`version_id`) FROM `ohrm_audittrail_pim_work_week_trail` WHERE `affected_entity_id` = OLD.emp_number) + 1;
  SET new_version = IFNULL(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 CONCAT(hs_hr_employee.emp_firstname, ' ' , hs_hr_employee.emp_middle_name ,' ', hs_hr_employee.emp_lastname) FROM ohrm_employee_work_week LEFT JOIN  hs_hr_employee ON hs_hr_employee.emp_number = ohrm_employee_work_week.emp_number WHERE hs_hr_employee.emp_number = OLD.emp_number LIMIT 1);
  SET orangehrm_user_name = IFNULL(orangehrm_user_name, current_user());
  CALL audit_delete_ohrm_employee_work_week(@orangehrm_user, orangehrm_user_name, 'DELETE WORK WEEK DETAILS', new_version, OLD.emp_number, affected_entity_name, OLD.day_id, OLD.hours_per_day, OLD.shift_start_time, OLD.shift_end_time, OLD.lunch_start_time, OLD.lunch_end_time, OLD.day_type_id);
END;$$

CREATE PROCEDURE audit_delete_ohrm_employee_work_week
(
  IN action_owner_id VARCHAR(6) CHARSET UTF8,
  IN action_owner_name VARCHAR(300) CHARSET UTF8,
  IN action_name VARCHAR(255) CHARSET UTF8,
  IN new_version INT,
  IN affected_entity_id INT(11), 
  IN affected_entity_name VARCHAR(300) CHARSET UTF8,
  IN old_day_id INT(11),  
  IN old_hours_per_day DECIMAL(7,5),  
  IN old_shift_start_time TIME,  
  IN old_shift_end_time TIME,
  IN old_lunch_start_time TIME,
  IN old_lunch_end_time TIME,
  IN old_day_type_id INT(11)
)
BEGIN
  DECLARE action_description VARCHAR(600);


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

   SET action_description = IFNULL( CONCAT(action_description, ' ', 'Work Week Day was deleted\n'),'');
   SET action_description = IFNULL( CONCAT(action_description, ' ', 'Day: ',(SELECT `name` FROM `ohrm_week_days` WHERE `id` = old_day_id),'\n'),'');
   IF (old_hours_per_day != '') THEN SET action_description = IFNULL( CONCAT(action_description, ' ', 'Duration: ',old_hours_per_day,'\n'),''); END IF;
   IF (old_shift_start_time != '') THEN SET action_description = CONCAT(action_description, ' ', 'Shift In: ',old_shift_start_time,'\n');END IF;
   IF (old_shift_end_time != '') THEN SET action_description = CONCAT(action_description, ' ', 'Shift Out: ',old_shift_end_time,'\n');END IF;
   IF (old_lunch_start_time != '') THEN SET action_description = CONCAT(action_description, ' ', 'Lunch In: ',old_lunch_start_time,'\n');END IF;
   IF (old_lunch_end_time != '') THEN SET action_description = CONCAT(action_description, ' ', 'Lunch Out: ',old_lunch_end_time,'\n');END IF;
   SET action_description = CONCAT(action_description, ' ', 'Day Type: ',(SELECT `name` FROM `ohrm_day_type` WHERE `id` = old_day_type_id),'\n');


  -- End generating the action description

  IF (action_description != '') THEN
    INSERT INTO `ohrm_audittrail_pim_work_week_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;$$ 

DELIMITER ;