-- Custom Date Based Notifications
SET @custom_field_name := '<<Custom Field Name>>';
SET @notification_name :=  '<<Notification Name>>';
SET @notification_subject := '<<Email Subject>>';
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');

SET @date_based_notification_type := (SELECT id FROM ohrm_notification_type where `name`= 'Date Based');
SET @custom_field_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` = 'pim');
SET @custom_field_id := '<<Custom Field Id>>';

INSERT INTO `ohrm_notification`
(`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`, `module_id`, `parameters`) VALUES
    (@notification_name, 1, @date_based_notification_type, 'CustomDateEventNotification', NULL, NULL, @custom_field_module, CONCAT('{"entity": "CustomFieldValue" ,"attribute": "date_value","employeeJoinAttribute": "emp_number","dateFormat" : "YYYY-MM-DD","employeeReference": "foreign", "conditions": ["custom_field_id = ', @custom_field_id, '"], "auxiliaryTokens": [ { "id": "RecipientName", "name": "RecipientName", "display_name": "Recipient Name", "section": "", "tab_name": "', @notification_name, '", "value_getters": "" }, { "id": "EventDate", "name": "EventDate", "display_name": "Event Date", "section": "", "tab_name": "', @notification_name, '", "value_getters": "" }, { "id": "DaysLeft", "name": "DaysLeft", "display_name": "Days Left", "section": "", "tab_name": "', @notification_name, '", "value_getters": "" } ] }'));
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     CONCAT(@notification_name, ' sent to Affected Employee'),
     @notification_subject,
     CONCAT('<p>Dear %RecipientName%,<br/><br/>We hope this email finds you well.<br/>This is a friendly reminder that your ',@custom_field_name,' (%EventDate%) is fast approaching. As of today, you have %DaysLeft% left until the expiration date.<br/><br/>If you have any questions please do not hesitate to contact us.<br/><br/>Best regards,</p>'));

INSERT INTO `ohrm_notification_subscriber_subscription_template`
(`notification_id`,`media_id`,`locale`,`name`,`subject`,`body`) VALUES
    (@notification_id, @email_media, 'en_US',
     CONCAT(@notification_name, ' sent to Subscriber'),
     @notification_subject,
     CONCAT('<p>Dear %RecipientName%,<br/><br/>We hope this email finds you well.<br/>This is a friendly reminder that %EmpFirstName% %EmpLastName%''s ',@custom_field_name,' (%EventDate%) is fast approaching and %DaysLeft% left until the expiration date.<br/><br/>If you have any questions please do not hesitate to contact us.<br/><br/>Best regards,</p>'));

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`) VALUES
    (@notification_id, @email_media);

