SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');

-- new approve notifications
SET @approve_notification_id :=(SELECT id FROM  `ohrm_notification` WHERE `name` ='Timesheet Approval');

INSERT INTO `ohrm_notification_template`
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
  (@approve_notification_id, @email_media, 'en_US', NULL, 'Affected Employee',
   '%ApproverName% approved your timesheet for the period %StartDate% - %EndDate%',
   '<p>Hi %recipientFirstName%,</p>
</br>
<p>%ApproverName% has approved your timesheet for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Use the following direct link if you wish to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>This is an auto-generated email.</p>');

INSERT INTO `ohrm_notification_template`
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
  (@approve_notification_id, @email_media, 'en_US', NULL, 'Supervisor',
   '%ApproverName% approved timesheet of %EmployeeName% for the period %StartDate% - %EndDate%',
   '<p>Hi %recipientFirstName%,</p>
</br>
<p>%ApproverName% has approved the timesheet of %EmployeeName% for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Use the following direct link if you wish to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>This is an auto-generated email.</p>');

-- new rejection notifications
SET @rejection_notification_id :=(SELECT id FROM  `ohrm_notification` WHERE `name` ='Timesheet Rejection');

INSERT INTO `ohrm_notification_template`
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
  (@rejection_notification_id, @email_media, 'en_US', NULL, 'Affected Employee',
   '%RejectorName% rejected your timesheet for the period %StartDate% - %EndDate%',
   '<p>Hi %recipientFirstName%,</p>
</br>
<p>%RejectorName% has rejected your timesheet for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Please amend the timesheet as per the comment provided by %RejectorName% and submit for approval.</p>
<p>Use the following direct link to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>This is an auto-generated email.</p>');

INSERT INTO `ohrm_notification_template`
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
  (@rejection_notification_id, @email_media, 'en_US', NULL, 'Supervisor',
   '%RejectorName% rejected timesheet of %EmployeeName% for the period %StartDate% - %EndDate%',
   '<p>Hi %recipientFirstName%,</p>
</br>
<p>%RejectorName% has rejected the timesheet of %EmployeeName% for the period of %StartDate% - %EndDate% with the comment "%Comment%".</p>
<p>Please amend the timesheet as per the comment provided by %RejectorName% and submit for approval.</p>
<p>Use the following direct link to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>This is an auto-generated email.</p>');

-- new submission notifications
SET @submission_notification_id :=(SELECT id FROM  `ohrm_notification` WHERE `name` ='Timesheet Submission');
INSERT INTO `ohrm_notification_template`
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
  (@submission_notification_id, @email_media, 'en_US', NULL, 'Supervisor',
    '%SubmitterName% submitted timesheet for the period %StartDate% - %EndDate%',
    '<p>Hi %recipientFirstName%,</p>
</br>
<p>%SubmitterName% has submitted the timesheet for the period of %StartDate% - %EndDate%.</p>
<p>Please view the timesheet and take the necessary action to Approve/Reject as you wish.
Use the following direct link to access the timesheet.</p>
<a href="%URLToTimesheet%">View Timesheet</a>
</br>
<p>Thank You.</p>
<p>This is an auto-generated email.</p>');