CREATE TABLE IF NOT EXISTS `ohrm_cron_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,
  `observer_class` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=UTF8 ;

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

CREATE TABLE IF NOT EXISTS `ohrm_cron_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_cron_inputs_ohrm_cron_task FOREIGN KEY (task_id) REFERENCES ohrm_cron_task(id) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=UTF8;

--
-- 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) NOT NULL,
  `task_id` int(11) NOT NULL,
  `command` text NOT NULL,
  `time_stamp` datetime NOT NULL,
  `success` BOOLEAN NOT NULL,
  `output` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=UTF8;

--
-- Dumping data for table `ohrm_cron_task`
--

INSERT INTO `ohrm_cron_task` (`id`, `name`, `short_description`, `long_description`, `command`, `observer_class`, `status`) 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', 'EmailObserver', 1),
(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', 'ProcessNotificationObserver', 1),
(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', '', 1),
(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', 'LeaveAccrualObserver', 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', '', 1),
(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', '', 1),
(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', '', 1),
(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', '', 1);


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

INSERT INTO `ohrm_cron_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');


SET @sys_admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ConfigAdmin');

INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('managementtool_notship', 1);

SET @module_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('TestEmulator', @module_id, 'viewCronTasks');

SET @TestEmulator_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('TestEmulatorExecute', @module_id, 'execute');

SET @TestEmulatorExecute_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('TestEmulatorHistory', @module_id, 'cronHistory');

SET @TestEmulatorHistory_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('TestEmulatorOutput', @module_id, 'cronOutput');

SET @TestEmulatorOutput_screen_id := (SELECT LAST_INSERT_ID());

SET @admin_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Admin' AND `level` = 1);


INSERT INTO `ohrm_menu_item` (`id`, `menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`, `has_multiple_records`) VALUES (NULL, 'Management Tools', NULL, NULL, @admin_menu_id, '2', '900', NULL, '1', NULL);
SET @tools_menu_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Executable Tasks', @TestEmulator_screen_id, @tools_menu_id, 3, 1000, '', 1);

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@sys_admin_role_id, @TestEmulator_screen_id, 1, 0, 0, 0),
(@sys_admin_role_id, @TestEmulatorExecute_screen_id, 1, 0, 0, 0),
(@sys_admin_role_id, @TestEmulatorHistory_screen_id, 1, 0, 0, 0),
(@sys_admin_role_id, @TestEmulatorOutput_screen_id, 1, 0, 0, 0);


INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('test_emulator', 'Admin - Test Emulator', 1, 0, 0, 0);
SET @test_emulator_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@sys_admin_role_id , @test_emulator_data_group_id, 1, 0, 0, 0, 0);

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@test_emulator_data_group_id,@TestEmulator_screen_id,1),
(@test_emulator_data_group_id,@TestEmulator_screen_id,2),
(@test_emulator_data_group_id,@TestEmulator_screen_id,3),
(@test_emulator_data_group_id,@TestEmulator_screen_id,4),
(@test_emulator_data_group_id,@TestEmulatorExecute_screen_id,1),
(@test_emulator_data_group_id,@TestEmulatorExecute_screen_id,2),
(@test_emulator_data_group_id,@TestEmulatorExecute_screen_id,3),
(@test_emulator_data_group_id,@TestEmulatorExecute_screen_id,4),
(@test_emulator_data_group_id,@TestEmulatorHistory_screen_id,1),
(@test_emulator_data_group_id,@TestEmulatorHistory_screen_id,2),
(@test_emulator_data_group_id,@TestEmulatorHistory_screen_id,3),
(@test_emulator_data_group_id,@TestEmulatorHistory_screen_id,4),
(@test_emulator_data_group_id,@TestEmulatorOutput_screen_id,1),
(@test_emulator_data_group_id,@TestEmulatorOutput_screen_id,2),
(@test_emulator_data_group_id,@TestEmulatorOutput_screen_id,3),
(@test_emulator_data_group_id,@TestEmulatorOutput_screen_id,4);



INSERT INTO ohrm_module_default_page (`module_id`, `user_role_id`, `action`, `enable_class`, `priority`) VALUES
(@module_id, @sys_admin_role_id, 'managementtool_notship/viewCronTasks', NULL, 100);

UPDATE `ohrm_menu_item` SET `parent_id`= @tools_menu_id,`level`= 3 WHERE `menu_title` = 'Bulk Data Upload';

SET @parent_id := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Management Tools');
UPDATE `ohrm_menu_item` SET `parent_id` = @parent_id , `level` = '3' WHERE `ohrm_menu_item`.`menu_title` = 'License Update';
