SET @notification_type_id = (SELECT `id` FROM `ohrm_notification_type` WHERE `name`='Other');
SET @module_id = (SELECT `id` FROM `ohrm_module` WHERE `name`='report');

INSERT INTO `ohrm_notification`(`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`,
                                `workflow_action`, `module_id`, `hidden`)
VALUES ('Scheduled Report Notification', 1, @notification_type_id, NULL, NULL, NULL, NULL, @module_id, 1);

SET @email_media = (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');
SET @notification_id = (SELECT `id` FROM `ohrm_notification` WHERE `name` = 'Scheduled Report Notification');

INSERT INTO `ohrm_notification_subscriber_subscription_template`(`name`, `notification_id`, `media_id`, `locale`, `subject`, `body`)
VALUES ('Scheduled Report Notification Template For Successful Execution',
        @notification_id, @email_media,
        'en_US',
        'Report schedule “%ScheduleName%” was executed on %ExecutedDate% at %ExecutedTime%',
        '<p>Hi%EmployeeFirstNameSeparator%%EmployeeFirstName%,</p><p> The report schedule, “%ScheduleName%” was successfully executed on %ExecutedDate% at %ExecutedTime%.<br><br>The generated report “%ReportName%” is attached.</p><p>Thank you.<br>This is an automated email.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`(`name`, `notification_id`, `media_id`, `locale`, `subject`, `body`)
VALUES ('Scheduled Report Notification Template For Partially Successful Execution Due To Exceeded Limit',
        @notification_id, @email_media,
        'en_US',
        'Report schedule “%ScheduleName%” was executed on %ExecutedDate% at %ExecutedTime%',
        '<p>Hi%EmployeeFirstNameSeparator%%EmployeeFirstName%,</p><p> The report schedule, “%ScheduleName%” was successfully executed on %ExecutedDate% at %ExecutedTime%.<br><br>Note that the generated report “%ReportName%” has exceeded the maximum allowed file size of %MaximumFileSizeLimit%MB or the maximum limit of %MaximumRecordCount% rows. </p><p> Therefore, the attachment only contains %GeneratedRecordCount% rows.</p><p>Thank you.<br>This is an automated email.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`(`name`, `notification_id`, `media_id`, `locale`, `subject`, `body`)
VALUES ('Scheduled Report Notification Template For Execution Failure',
        @notification_id, @email_media,
        'en_US',
        'Report schedule “%ScheduleName%” failed to execute on %ExecutedDate% at %ExecutedTime%',
        '<p>Hi %EmployeeFirstName%,</p><p> The report schedule “%ScheduleName%” failed to execute on %ExecutedDate% at %ExecutedTime% due to %ErrorMessage%.<br></p><p>Thank you.<br>This is an automated email.</p>');


INSERT INTO `ohrm_notification_subscriber_subscription_template`(`name`, `notification_id`, `media_id`, `locale`, `subject`, `body`)
VALUES ('Scheduled Report Notification Template For FTP Upload Success',
        @notification_id, @email_media,
        'en_US',
        'Report schedule “%ScheduleName%” was executed on %ExecutedDate% at %ExecutedTime%',
        '<p>Hi %EmployeeFirstName%,</p><p> The “%ScheduleName%” report schedule was successfully executed on %ExecutedDate% at %ExecutedTime%.</p><p> The generated report “%ReportName%” is uploaded to the given location.<br></p><p>Thank you.<br>This is an automated email.</p>');


INSERT INTO `ohrm_notification_subscriber_subscription_template`(`name`, `notification_id`, `media_id`, `locale`, `subject`, `body`)
VALUES ('Scheduled Report Notification Template For FTP Upload Failure',
        @notification_id, @email_media,
        'en_US',
        'Report schedule “%ScheduleName%” failed to execute  %ExecutedDate% at %ExecutedTime%',
        '<p>Hi %EmployeeFirstName%,</p><p> The “%ScheduleName%” report schedule failed to execute on %ExecutedDate% at %ExecutedTime% due to the %ErrorMessage%.</p><p>Thank you.<br>This is an automated email.</p>');

INSERT INTO `ohrm_notification_subscriber_subscription_template`(`name`, `notification_id`, `media_id`, `locale`, `subject`, `body`)
VALUES ('Scheduled Report Notification Template For FTP Partially Successful Execution Due To Exceeded Limit',
        @notification_id, @email_media,
        'en_US',
        'Report schedule “%ScheduleName%” was executed on %ExecutedDate% at %ExecutedTime%',
        '<p>Hi %EmployeeFirstName%,</p><p> The “%ScheduleName%” report schedule was successfully executed on %ExecutedDate% at %ExecutedTime%.</p><p> The generated report “%ReportName%” is uploaded to the given location.</p><p>Note that the generated report “%ReportName%” has exceeded the maximum allowed file size of %MaximumFileSizeLimit%MB or the maximum limit of %MaximumRecordCount% rows. </p><p> Therefore, the uploaded file only contains %GeneratedRecordCount% rows.</p><p>Thank you.<br>This is an automated email.</p>');
