CREATE TABLE IF NOT EXISTS `ohrm_symfony_task` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `short_description` text NOT NULL,
  `long_description` text NOT NULL,
  `command` text NOT NULL,
  `schedule` varchar(100) DEFAULT NULL,
  `observer_class` varchar(255) NOT NULL,
  `prevent_overlap` tinyint(1) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL,
  `user_interface` enum('executableTask','esbTask') default 'executableTask',
  `initial_time_for_task_execution_verification` datetime DEFAULT NULL,
  `task_failure_notification` TINYINT(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=UTF8 ;

--
-- Dumping data for table `ohrm_symfony_task`
--

INSERT INTO `ohrm_symfony_task` (`id`, `name`, `short_description`, `long_description`, `command`, `schedule`, `observer_class`, `prevent_overlap`,`user_interface`,`status`,`initial_time_for_task_execution_verification`,`task_failure_notification`) VALUES
(1, 'Send Emails', 'This task will send all queued emails which are in pending status.', 'This task is executed on a daily basis on production systems. The frequency is set to once every minute or once every 15 minutes depending on the client\'s requirement.\n\nRunning this task via this screen task will send all queued emails which are in pending status.\n\nNote: This task will only send email notifications that are queued. As such, the "Queue email notifications to be sent" task must be executed prior to this.', 'php symfony orangehrm:send-email', '*/15 * * * *', 'EmailObserver', 1,'executableTask', 0, null, 0),
(2, 'Queue Email Notifications To Be Sent', 'Process notifications and add emails to the queue for sending.', 'This will process all enabled "date based" and "scheduled" email notifications in the system against the specified date or current date, create email notification accordingly and add them to the email queue.\nIf date is not specified current system date will be used.', 'php symfony orangehrm:ProcessNotification', '0 5 * * *', 'ProcessNotificationObserver', 0,'executableTask', 0, null, 0),
(3, 'Fix Leave Balances: Single Leave Type', 'This task will correct leave balances by updating links between leave requests and entitlements.', 'This task is only executed when fixes are required to leave balances.\n\nRunning this task via this screen will correct leave balances by updating links between leave requests and entitlements.\n\nExamples/Possible scenarios:\n\n1. Fix leave balances of a given leave type (leave type id: 2) for the current leave period for all employees.\n[php symfony orangehrm:leave-balance-fix 2]\n\n2. Fix the leave balance of a given leave type (leave type id: 2) of a given employee (emp_number: 5) for the current leave period.\n[php symfony orangehrm:leave-balance-fix 2 5 ]\n\n3. Fix the leave balance of a given leave type (leave type id: 2) of a given employee (emp_number: 5) for the leave period applicable as at a given date (date: 2013-06-12).\n[php symfony orangehrm:leave-balance-fix 2 5 --date "2013-06-12"]\n\nIf the required date is not specified, the current date of the server will be considered.', 'php symfony orangehrm:leave-balance-fix', null, '', 0,'executableTask', 1, null, 0),
(4, 'Process Leave Accrual and Carry Forward', 'This process will process leave accrual and carry over calculations based on a given date.', 'This task is executed on a daily basis on production systems.It processes leave accrual and carry over calculations. The time of day will depend on the location/country and server time.\n\nRunning this task via this screen will process leave accrual and carry over calculations based on a given date.\nIf the required date is not specified, the current date of the server will be considered. Default time: 00:00:00.\n\nIf Operational Countries are enabled a drop down will appear to select the country so that the scheduled time will get appended.\n\nWarning: Please refrain from running this task more than once per day as it could duplicate carry over calculations.\n', 'php symfony LeaveAccrualAndCarryForward', '*/15 * * * *', 'LeaveAccrualObserver', 0,'executableTask', 0, null, 1),
(5, 'Fix Leave Balances: Bulk', 'Fix leave balances of all leave types for all leave periods.', 'Fix leave balances of all leave types for all leave periods (up to the current date of next year) for all employees.\n[php symfony orangehrm:leave-balance-fix --bulk]', 'php symfony orangehrm:leave-balance-fix --bulk', null, '', 0,'executableTask', 1, null, 0),
(6, 'Clear Email Queue', 'This will remove all the sent/pending/started emails from the email queue.', 'Once this process is executed, all the emails in the email queue will get deleted.', 'php symfony orangehrm:ClearEmailQueue', null, '', 0,'executableTask', 1, null, 0),
(7, 'Clear Leave Entitlements', 'Clear all leave Entitlements in the system.', 'This task will clear all leave Entitlements in the system for all employees and all leave types.\n\nIt is preferred to run Clear leave requests task before running this task. ', 'php symfony orangehrm:ClearLeaveEntitlements', null, '', 0,'executableTask', 1, null, 0),
(8, 'Clear Leave Requests', 'Clear all leave requests in the system.', 'This task will clear all leave requests in the system for all employees and all leave types.', 'php symfony orangehrm:ClearLeaveRequests', null, '', 0,'executableTask', 1, null, 0),
(9, 'Terminate Employment & Update Employee Job Details', 'This process terminates or reactivates employees who have been marked for termination or reactivation on given date and time, and updates job information according to the effective date of the job record.', 'This task is executed every half an hour on production systems.\n\n-It will update employee records with the job information effective as at the current date.\n-It terminates employees who have been marked for termination on given date.\n-It reactivates employees who have been marked for reactivation on given date.', 'php symfony orangehrm:update-employee-job-information', '*/30 * * * *', '', 0,'executableTask', 1, null, 1),
(10, 'Process Leave Notification', 'Process Leave Notification', 'Process Leave Notification', 'php symfony orangehrm:ProcessLeaveNotification', '0 5 * * *', '', 0,'executableTask', 0, null, 0),
(11, 'Attendance Process', 'Attendance Process', 'Attendance Process', 'php symfony orangehrm:AttendanceProcess', '0 5 * * *', '', 0,'executableTask', 0, null, 1),
(12, 'Clean Logs Data and Tables', 'Remove and Archives logs and clean tables', 'Archives logs to encrypted zip files and removed old logs based on a threshold date and clean tables', 'php symfony orangehrm:clean-logs-dataAndTables', '0 1 * * *', '', 0,'executableTask', 1,CURRENT_TIMESTAMP, 1),
(13, 'RabbitMQ Queue Publishing', 'Publishes messages to Queue Service', 'Get Messages Stored from Database and Publishes them to the RabbitMQ Queue Service', 'php symfony orangehrm:RabbitMQQueuePublisher', '*/5 * * * *', '', 0,'executableTask', 0, null, 0),
(14, 'RabbitMQ Queue Subscribing', 'Subscribing to RabbitMQ queues to consume messages', 'Start consuming messages from RabbitMQ queue service', 'php symfony orangehrm:SubscribeQueueConsumers', '*/5 * * * *', '', 1,'executableTask', 0, null, 0),
(15, 'RabbitMQ Configuration', 'Configures RabbitMQ for the instance with rabbitmq.yml', 'Configures RabbitMQ for the instance with rabbitmq.yml. This creates a vhost and a new user for the instance.','php symfony orangehrm:ConfigureRabbitMQ',null, '', 0,'esbTask', 0, null, 0),
(16, 'Install OrangeHRM Plugin', 'Install OrangeHRM Plugin', 'Install OrangeHRM Plugin.','php symfony orangehrm:install-plugin',null, '', 0,'employeeDataExportConfiguration', 0, null, 0),
(17, 'Clear Symfony Cache', 'Clear Symfony Cache', 'Clear Symfony Cache','php symfony cc',null, '', 0,'employeeDataExportConfiguration', 0, null, 0),
(18, 'Diagnose Scheduled Tasks Execution Failures', 'Diagnose Scheduled Tasks Execution Failures', 'Diagnose Scheduled Tasks Execution Failures','php symfony orangehrm:ScheduledTasksFailureDiagnostic','0 2 * * *', '', 0,'executableTask', 1, null, 0),
(19, 'Configure Goal Workflow', 'This task is used to enable/disable the Goal Workflow and related email notifications', 'Executing the task with the below check-box checked will enable the goal workflow. Executing it with the check-box unchecked will disable the goal workflow.', 'php symfony orangehrm:configure-goal-workflow', NULL, '', 0, 'executableTask', 0, null, 0),
(20, 'Configure Password Expiration', 'This task is used to enable/disable password expiration', 'Executing the task with the below check-box checked will enable the password expiration. Executing it with the check-box unchecked will disable the password expiration.\n\nImportant -: If any, admin reset user passwords are already in the system, which user have not reset the password yet, Admin has to redo the resetting for those users again.', 'php symfony orangehrm:configure-password-expiration', NULL, '', 0, 'executableTask', 0, null, 0),
(21, 'Configure Final Rating Source', 'This task is used for configuring final rating source for performance appraisals', "Executing this task will,\n- change the final rating source in ohrm_config table\n- add the new final rating source as an evaluator for all the competencies of existing competency profiles\n- update workflow dependencies, for appraisals to complete upon the new final rating source's review submission\n- potential question mappings for new appraisals will be changed to the new final rating source\n- if new final rating source is changed to a evaluator group other than main evaluator, main evaluator group is deactivated\n\nAny workflow changes or other configurations need to be done manually.", 'php symfony orangehrm:configure-final-rating-source', NULL, '', 0, 'executableTask', 0, null, 0),
(22, 'Process Attendance Sheets', 'This task is used to process Attendance Sheets', "Executing this task will,\n- Create Attendance Sheets for Active Employees\n- Auto-submit the past attendance sheets (if enabled)", 'php symfony orangehrm:process-attendance-sheets', '0 1 * * *', '', 0, 'executableTask', 0, null, 1),
(23, 'Update Missing Address', 'Updates the attendance extra details address', 'Updates the addresses for punch in address and punch out address when they are missing. This should be executed after updating geolocation configuration.', 'php symfony orangehrm:attendance-fillAddress', '0 1 * * *', '', 0,'executableTask', 1, null, 0),
(24, 'Update Geolocation configurations', 'Updates the geolocation API key and URL', 'Updates the geolocation API key and URL', 'php symfony orangehrm:update-geoLocationConfig', NULL, '', 0,'executableTask', 1, null, 0),
(25, 'Configure Data Warehouse', 'Updates Data Warehouse Api key and url', 'Upon enabling configuration for the first time ESB related config will also get populate.', 'php symfony orangehrm:update-elasticAPIKey', NULL, '', 0,'executableTask', 1, null, 0),
(26, 'Update Secure Configurations', 'Updates the secure configuration key and value', 'This task will update the secure configurations in the ohrm_secure_configuration table\n\nNote - Updating configuration settings on a live system may cause integration failures', 'php symfony orangehrm:update-secureConfig', NULL, '', 0,'executableTask', 1, null, 0),
(27, 'Notify Job/Salary Records Reaching Effective Date', 'Notify Job/Salary Records Reaching Effective Date', 'This process will notify future employee job and salary records on reaching the effective date.\n\nThis task is scheduled to run once a day in production systems.', 'php symfony orangehrm:notify-future-effective-changes', '15 0 * * *', '', 0,'executableTask', 1,CURRENT_TIMESTAMP, 1),
(28, 'Configure Candidate Document E-sign', 'This task is used to enable/disable the e-sign for recruitment candidate documents.', 'Executing the task with the below check-box checked will enable the e-sign feature in candidate documents. Executing it with the check-box unchecked will disable the e-sign feature in candidate documents.', 'php symfony orangehrm:configure-digital-sign-for-candidate-documents', NULL, '', 0, 'executableTask', 0, null, 0),
(29, 'Auto Punch Out', "This task automatically punches out employees who have punch-in records within the specified past duration but have not punched out.", "This task automatically punches out employees who have punch-in records within the specified past duration but have not punched out.", 'php symfony orangehrm:attendance-auto-punch-out', '17 * * * *', '', 0, 'executableTask', 0, null, 1),
(30, 'Enable E-Sign for Employee Management Documents', 'Enables/disables e-signature for Employee Management documents', 'Enabling this configuration will activate the e-signature for Employee Management documents. If the check-box is unchecked, the e-signature will be disabled for these documents.', 'php symfony orangehrm:configure-digital-sign-for-pim-documents', NULL, '', 0, 'executableTask', 0, null, 0);

--
-- Table structure for table `ohrm_cron_inputs`
--

CREATE TABLE IF NOT EXISTS `ohrm_symfony_task_inputs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `label` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `active` tinyint(1) NOT NULL,
  `required` tinyint(1) NOT NULL,
  `position` int(11) NOT NULL,
  `widget_creator` varchar(200) NOT NULL,
  `type` varchar(100) NOT NULL,
  `data_type` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT fk_ohrm_symfony_task_inputs_ohrm_symfony_task FOREIGN KEY (task_id) REFERENCES ohrm_symfony_task(id) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=UTF8;

--
-- Dumping data for table `ohrm_cron_inputs`
--

INSERT INTO `ohrm_symfony_task_inputs` (`id`, `task_id`, `name`, `label`, `description`, `active`, `required`, `position`, `widget_creator`, `type`, `data_type`) VALUES
(1, 3, 'leave_type_id', 'Leave Type', 'leave type id', 1, 0, 1, 'LeaveTypeWidgetCreator', 'arguement', 'leaveType'),
(2, 3, 'emp_number', 'Employee', 'Employee Number', 1, 0, 2, 'EmployeeIdWidgetCreator', 'arguement', 'employeeId'),
(3, 3, '--date', 'Date', 'Run fix for leave period as on given date', 1, 0, 3, 'DateWidgetCreator', 'parameter', 'date'),
(4, 2, '--systemDate', 'Date To Run', 'User defined system date', 1, 0, 1, 'DateWidgetCreator', 'parameter', 'date'),
(5, 4, '--systemDate', 'Date To Run', 'Date and time to run the leave accrual for', 1, 0, 1, 'LeaveAccrualWidgetCreator', 'parameter', 'datetime'),
(6, 9, '--systemDate', 'Date To Run', 'User defined system date', 1, 0, 1, 'DateWidgetCreator', 'parameter', 'date'),
(7, 15, '--host', 'RabbitMQ Host', 'RabbitMQ Host', 1, 1, 100, 'InputFieldWidgetCreator', 'parameter', ''),
(8, 15, '--amqpPort', 'AMQP Port', 'User defined system date', 0, 0, 200, 'InputFieldWidgetCreator', 'parameter', ''),
(9, 15, '--apiUrl', 'API URL', 'API URL', 1, 1, 300, 'InputFieldWidgetCreator', 'parameter',''),
(10, 15, '--vhostName', 'Virtual Host Name Prefix', 'Virtual Host Name Prefix', 1, 1, 350, 'InputFieldWidgetCreator', 'parameter',''),
(11, 15, '--tls', 'Enable TLS', '', 1, 0, 700, 'InputCheckboxWidgetCreator', 'parameter', ''),
(12, 15, '--sslPeerVerification', 'Enable SSL Peer Verification', 'User defined system date', 1, 0, 800, 'InputCheckboxWidgetCreator', 'parameter', ''),
(13, 15, '--username', 'RabbitMQ User Name', 'RabbitMQ User Name', 1, 1, 400, 'InputFieldWidgetCreator', 'parameter', ''),
(14, 15, '--password', 'RabbitMQ Password', 'RabbitMQ Password', 1, 1, 500, 'InputPasswordWidgetCreator', 'parameter', ''),
(15, 16, '--plugin', 'Plugin Name', 'Plugin Name', 1, 1, 500, '', 'parameter', ''),
(16, 16, '--installFromYml', 'YML file name for the installation', 'YML file name for the installation', 1, 1, 600, '', 'parameter', ''),
(17, 14, '--timeout', 'Timeout in Seconds', 'Task Timeouts after the Specified Number of Seconds', 1, 1, 350, 'InputFieldWidgetCreator', 'parameter', ''),
(18, 19, '--workflow-status', 'Enable Goal Workflow Status', 'Goal Workflow - Enable or Disable', 1, 0, 1, 'InputCheckboxWidgetCreator', 'parameter', ''),
(19, 20, '--status', 'Enable Password Expiration', 'Password Expiration - Enable or Disable', 1, 0, 1, 'InputCheckboxWidgetCreator', 'parameter', ''),
(20, 21, '--finalRatingSourceId', 'Final Rating Source', 'Final Rating Source Change', 1, 0, 1, 'FinalRatingSourceWidgetCreator', 'parameter', ''),
(21, 22, '--systemDate', 'Date To Run', 'User defined system date', 1, 0, 1, 'DateWidgetCreator', 'parameter', 'date'),
(22, 24, '--apiKey', 'Geolocation API Key', 'Geolocation API Key', 1, 1, 1, 'GeoLocationPasswordWidgetCreator', 'parameter', ''),
(23, 24, '--apiUrl', 'Geolocation API URL', 'Geolocation API URL', 1, 1, 1, 'GeoLocationInputFieldWidgetCreator', 'parameter', ''),
(24, 25, '--apiKey', 'Elasticsearch apiKey', 'Elasticsearch apiKey', 1, 1, 1, 'ElasticSearchPasswordWidgetCreator', 'parameter', ''),
(25, 25, '--apiUrl', 'Elasticsearch apiUrl', 'Elasticsearch apiUrl', 1, 1, 1, 'ElasticSearchInputFieldWidgetCreator', 'parameter', ''),
(26, 26, '--configurationKey', 'Configuration Key', 'Configuration Key', 1, 1, 1, 'ConfigurationKeyInputFieldWidgetCreator', 'parameter', ''),
(27, 26, '--configurationValue', 'Configuration Value', 'Configuration Value', 1, 1, 1, 'ConfigurationValuePasswordWidgetCreator', 'parameter', ''),
(28, 9, '--systemTime', 'Time To Run', 'User defined system time', 1, 0, 2, 'TimeWidgetCreator', 'parameter', 'time'),
(29, 9, '--systemTimezone', 'Time Zone', 'User defined system timezone', 1, 0, 3, 'TimezoneWidgetCreator', 'parameter', ''),
(30, 27, '--systemDate', 'Date To Run', 'User defined system date', 1, 0, 1, 'DateWidgetCreator', 'parameter', 'date'),
(31, 28, '--status', 'Enable Candidate Document E-sign', 'Candidate Document E-sign - Enable or Disable', 1, 0, 1, 'InputCheckboxWidgetCreator', 'parameter', ''),
(32, 29, '--systemDate', 'Date To Run', 'User defined system date', 1, 0, 1, 'UserDefaultDateWidgetCreator', 'parameter', 'date'),
(33, 29, '--systemTime', 'Time To Run', 'User defined system time', 1, 0, 2, 'UserDefaultTimeWidgetCreator', 'parameter', 'time'),
(34, 29, '--durationToRunHours', 'Duration to Run (Hours)', 'Duration to Run (Hours)', 1, 0, 3, 'AutoPunchOutDurationToRunInputFieldWidgetCreator', 'parameter', 'time'),
(35, 29, '--systemTimezone', 'Time Zone', 'User defined system timezone', 1, 0, 4, 'UserDefaultTimezoneWidgetCreator', 'parameter', ''),
(36, 30, '--status', 'Enable E-Sign for Employee Management Documents', 'Enables/disables e-signature for Employee Management documents', 1, 0, 1, 'InputCheckboxWidgetCreator', 'parameter', '');



--
-- Table structure for table `ohrm_cron_job_audittrail`
--

CREATE TABLE IF NOT EXISTS `ohrm_cron_job_audittrail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `task_id` int(11) NOT NULL,
  `command` text NOT NULL,
  `start_time_stamp` datetime DEFAULT NULL,
  `end_time_stamp` datetime DEFAULT NULL,
  `intended_start_time_stamp` datetime DEFAULT NULL,
  `success` BOOLEAN DEFAULT NULL,
  `output` text DEFAULT NULL,
  `unique_id` varchar(50) DEFAULT NULL,
  `execution_verified` boolean DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX task_id (`task_id`),
  INDEX execution_verified (`execution_verified`),
  INDEX cron_job_audittrail_intended_start_time_stamp_idx (`intended_start_time_stamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=UTF8;

CREATE TABLE ohrm_cron_error_log (
  id int(11) NOT NULL AUTO_INCREMENT,
  error_level int(11),
  cron_audit_id int(4) DEFAULT NULL,
  symfony_task_id int(11),
  description text,
  expected_start_time datetime DEFAULT NULL,
  PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`) VALUES
('maintenance.symfony_task_error_event', 'MaintenanceSymfonyTaskErrorDBLogListener', '1', '1'),
('maintenance.symfony_task_error_event', 'MaintenanceSymfonyTaskErrorEmailListener', '1', '2'),
('test_emulator.verify_scheduled_task_execution', 'ScheduleTaskExecutionVerificationListener', '1', '3');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES
('schedule_task_failure_notification_email', 'productteam@orangehrm.com');
