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';


CREATE TABLE `ohrm_data_group_restriction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `map_xml` varchar(2000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

ALTER TABLE  `ohrm_user_role_data_group` ADD  `restriction_id` INT( 11 )  NULL AFTER  `self` ;
ALTER TABLE  `ohrm_user_role_data_group` ADD INDEX (  `restriction_id` ) ;
ALTER TABLE ohrm_user_role_data_group ADD FOREIGN KEY (restriction_id) REFERENCES ohrm_data_group_restriction(id);

ALTER TABLE  `hs_hr_emp_work_experience`
ADD COLUMN `eexp_creditable` INT(1) NULL DEFAULT NULL AFTER `eexp_internal`;

CREATE TABLE IF NOT EXISTS `ohrm_wizard_section_processor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(500) NOT NULL,
  `topic` varchar(200) NOT NULL,
  `add_label` varchar(200) NOT NULL,
  `list_label` varchar(200) NOT NULL,
  `menu_item_id` int(11) DEFAULT NULL,
  `has_list` tinyint(2) NOT NULL,
  `datagroup_name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `menu_item_id` (`menu_item_id`),
  KEY `menu_item_id_2` (`menu_item_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

ALTER TABLE  `ohrm_wizard_section_processor` ADD  `actionUrl` VARCHAR( 200 ) NOT NULL ;

SET @personal_informationDataGroupId := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'personal_information');
SET @social_mediaDataGroupId := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'social_media');
SET @contact_detailsDataGroupId := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'contact_details');
SET @job_history_detailsDataGroupId := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'job_history_details');
SET @supervisorDataGroupId := (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'supervisor');

SET @personal_deitails_menuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Personal Details' AND `menu_category`='pim_left_menu');
SET @contact_detalsMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Contact Details' AND `menu_category`='pim_left_menu');
SET @emergencyContactDetalsMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Emergency Contacts' AND `menu_category`='pim_left_menu');
SET @jobDetalsMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Job' AND `menu_category`='pim_left_menu');
SET @socialMediaMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Social Media Details' AND `menu_category`='pim_left_menu');
SET @reportToMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Report-to' AND `menu_category`='pim_left_menu');
SET @dependentMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Dependents' AND `menu_category`='pim_left_menu');
SET @salaryoMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Salary' AND `menu_category`='pim_left_menu');
SET @qualificationMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Qualifications' AND `menu_category`='pim_left_menu');
SET @membershipMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Memberships' AND `menu_category`='pim_left_menu');
SET @directDepositMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Direct Deposit' AND `menu_category`='pim_left_menu');
SET @immigrationMenuId := (SELECT `id` FROM `ohrm_menu_item` WHERE `menu_title` = 'Immigration' AND `menu_category`='pim_left_menu');


INSERT INTO `ohrm_wizard_section_processor` (`name`, `topic`, `add_label`, `list_label`, `menu_item_id`, `has_list`, `datagroup_name`, `actionUrl`) VALUES
('PimPersonalDetailFormFactory', 'Personal Details', 'Personal Details', 'Employee Personal Details', @personal_deitails_menuId, 0, NULL, ''),
('PimContactDetailsFormFactory', 'Contact Details', 'Contact Details', '', @contact_detalsMenuId, 0, NULL, ''),
('PimSocialMediaFormFactory', 'Social Media Details', 'Social Media Details', 'EmployeeSocial Media Details', @socialMediaMenuId, 1, NULL, ''),
('PimEmergencyContactsFormFactory', 'Emergency Contacts', 'Emergency Contacts', '',  @emergencyContactDetalsMenuId, 1, NULL, ''),
('PimJobFormFactory', 'Job', 'Add Job', '',@jobDetalsMenuId , 0, NULL, ''),
('PimSupervisorFormFactory', 'Supervisor', 'Supervisor', '', @reportToMenuId, 1, NULL, ''),
('PimSubordinateFormFactory', 'Subordinate', 'Subordinate', '', @reportToMenuId, 1, NULL, ''),
('PimDependentFormFactory', 'Dependent', 'Dependent', 'Assigned Dependents', @dependentMenuId, 1, NULL, ''),
('PimImmigrationDetailsFormFactory', 'Immigration', 'Immigration', 'Assigned Immigration Records', @immigrationMenuId, 1, NULL, ''),
('PimDirectDepositFormFactory', 'Direct Deposit', 'Direct Deposits', 'Assigned Direct Deposit Records', @directDepositMenuId, 1, NULL, ''),
('PimMembershipFormFactory', 'Memberships', 'Membership', 'Assigned Memberships', @membershipMenuId, 1, NULL, ''),
('PimWorkExperienceFormFactory', 'Work Experience', 'Work Experience', 'Work Experience', @qualificationMenuId, 1, NULL, ''),
('PimEducationFormFactory', 'Education', 'Education', 'Education', @qualificationMenuId, 1, NULL, ''),
('PimSkillFormFactory', 'Skill', 'Skill', 'Skill', @qualificationMenuId, 1, NULL, ''),
('PimLanguageFormFactory', 'Language', 'Work Experience', 'Work Experience', @qualificationMenuId, 1, NULL, ''),
('PimLicenseFormFactory', 'License', 'License', 'License', @qualificationMenuId, 1, NULL, ''),
('PimSalaryFormFactory', 'Salary', 'Salary Payment Details', 'Salary History', @salaryoMenuId, 0, NULL, '');

ALTER TABLE `ohrm_wizard_section_processor`
  ADD CONSTRAINT `ohrm_wizard_section_processor_ibfk_1` FOREIGN KEY (`menu_item_id`) REFERENCES `ohrm_menu_item` (`id`);

CREATE TABLE IF NOT EXISTS `ohrm_form_input_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field_name` varchar(150) NOT NULL,
  `validator` varchar(1000) NOT NULL,
  `data_source` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

--
-- Dumping data for table `ohrm_form_input_type`
--

INSERT INTO `ohrm_form_input_type` (`field_name`) VALUES
  ('sfWidgetFormInputText'),
  ( 'sfWidgetFormSelect'),
  ('ohrmWidgetDatePicker'),
  ('sfWidgetFormChoice'),
  ('ohrmWidgetEmployeeNameAutoFill'),
  ('sfWidgetFormTextarea'),
  ('sfWidgetFormInputCheckbox');

CREATE TABLE IF NOT EXISTS `ohrm_pim_field` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field_name` varchar(100) NOT NULL,
  `description` varchar(500) NOT NULL,
  `is_mandatory` tinyint(2) NOT NULL,
  `is_visible` tinyint(2) NOT NULL,
  `order` int(11) NOT NULL,
  `field_type` int(11) NOT NULL,
  `getter` varchar(500) NOT NULL,
  `setter` varchar(500) NOT NULL,
  `validator` varchar(500) NOT NULL,
  `processor_id` int(11) NOT NULL,
  `label` varchar(500) NOT NULL,
  `is_enable` tinyint(2) NOT NULL,
  `data_service` varchar(1000) NOT NULL,
  `service_type` enum('SERVICE','ATTR','EXSERVICE') DEFAULT NULL,
  `location` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `processor_id` (`processor_id`),
  KEY `field_type` (`field_type`),
  KEY `location` (`location`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;


SET @sfWidgetFormInputText := (SELECT `id` FROM `ohrm_form_input_type` WHERE `field_name` = 'sfWidgetFormInputText');
SET @sfWidgetFormSelect := (SELECT `id` FROM `ohrm_form_input_type` WHERE `field_name` = 'sfWidgetFormSelect');
SET @ohrmWidgetDatePicker := (SELECT `id` FROM `ohrm_form_input_type` WHERE `field_name` = 'ohrmWidgetDatePicker');
SET @sfWidgetFormChoice := (SELECT `id` FROM `ohrm_form_input_type` WHERE `field_name` = 'sfWidgetFormChoice');
SET @ohrmWidgetEmployeeNameAutoFill := (SELECT `id` FROM `ohrm_form_input_type` WHERE `field_name` = 'ohrmWidgetEmployeeNameAutoFill');


SET @pimPersonalDetailFormFactoryId := (SELECT `id` FROM `ohrm_wizard_section_processor` WHERE `name` = 'PimPersonalDetailFormFactory');
SET @pimContactDetailsFormFactoryId := (SELECT `id` FROM `ohrm_wizard_section_processor` WHERE `name` = 'PimContactDetailsFormFactory');
SET @pimSubordinateFormFactoryId := (SELECT `id` FROM `ohrm_wizard_section_processor` WHERE `name` = 'PimSocialMediaFormFactory');
SET @pimEmergencyContactsFormFactoryId := (SELECT `id` FROM `ohrm_wizard_section_processor` WHERE `name` = 'PimEmergencyContactsFormFactory');
SET @pimSupervisorFormFactoryId := (SELECT `id` FROM `ohrm_wizard_section_processor` WHERE `name` = 'PimSupervisorFormFactory');
SET @pimSubordinateFormFactoryId := (SELECT `id` FROM `ohrm_wizard_section_processor` WHERE `name` = 'PimSubordinateFormFactory');

INSERT INTO `ohrm_pim_field` (`field_name`, `description`, `is_mandatory`, `is_visible`, `order`, `field_type`, `getter`, `setter`, `validator`, `processor_id`, `label`, `is_enable`, `data_service`, `service_type`, `location`) VALUES
('nationality', '', 0, 1, 8, 2, 'getNationCode', 'setNationCode', '', 1, 'Nationality', 1, '{\n  "service": "NationalityService",\n  "method": "getNationalityList",\n  "key": "getId",\n  "value": "getName"\n}', 'SERVICE', NULL),
('txtLicExpDate', 'License Expiry Date', 0, 1, 3, 3, 'getEmpDriLiceExpDate', 'setEmpDriLiceExpDate', '', 1, 'License Expiry Date', 1, '{"choices":[\n    {"key":"Single", "value":"__(''Single'')"},\n    {"key":"Married", "value":"__(''Married'')"},\n    {"key":"Other", "value":"__(''Other'')"}\n]}', NULL, NULL),
('maritalStatus', '', 0, 1, 4, 2, 'getEmpMaritalStatus', 'setEmpMaritalStatus', '', 1, 'Marital Status', 1, '{"choices":[\n    {"key":"Single", "value":"Single"},\n    {"key":"Married", "value":"Married"},\n    {"key":"Other", "value":"Other"}\n]}', 'ATTR', NULL),
('gender', '', 0, 1, 5, 4, 'getEmpGender', 'setEmpGender', '', 1, 'Gender', 1, '{"choices":[\n    {"key":"1", "value":"Male"},\n    {"key":"2", "value":"Female"}\n]}', 'ATTR', NULL),
('dateOfBirth', 'Date of Birth', 0, 1, 6, 3, 'getEmpBirthday', 'setEmpBirthday', '', 1, 'Date of Birth', 1, '', NULL, NULL),
('txtLicenNo', '', 0, 1, 7, 1, 'getLicenseNo', 'setLicenseNo', '', 1, 'Driver''s License Number', 1, '', NULL, NULL),
('employeeId', '', 0, 1, 1, 1, 'getEmployeeId', 'setEmployeeId', '', 1, 'Employee Id', 1, '', NULL, NULL),
('mainId', '', 0, 1, 1, 1, 'getSsn', 'setSsn', '', 1, 'Main Id', 1, '', NULL, NULL),
('addressStreet1', '', 0, 1, 1, 1, 'getStreet1', 'setStreet1', '', 2, 'Address Street 1', 1, '', NULL, NULL),
( 'addressStreet2', '', 0, 1, 1, 1, 'getStreet2', 'setStreet2', '', 2, 'Address Street 2', 1, '', NULL, NULL),
('city', '', 0, 1, 1, 1, 'getCity', 'setCity', '', 2, 'City', 1, '', NULL, NULL),
('stateProvince', '', 0, 1, 1, 1, 'getProvince', 'setProvince', '', 2, 'State/Province', 1, '', NULL, NULL),
('zipPostalCode', '', 0, 1, 1, 1, 'getEmpZipcode', 'setEmpZipcode', '{"maxlength":"10"}', 2, 'Zip/PostalCode', 1, '', NULL, NULL),
('country', '', 0, 1, 5, 2, 'getCountry', 'setCountry', '', 2, 'Country', 1, '{\n  "service": "CountryService",\n  "method": "getCountryList",\n  "key": "getCouCode",\n  "value": "getName"\n}', 'SERVICE', NULL),
('homeTelephone', '', 0, 1, 6, 1, 'getEmpHmTelephone', 'setEmpHmTelephone', '{"phone": "true","maxlength":"25"}', 2, 'Home Telephone', 1, '', NULL, NULL),
('mobile', '', 0, 1, 6, 1, 'getEmpMobile', 'setEmpMobile', '{"phone": "true","maxlength":"25"}', 2, 'Mobile', 1, '', NULL, NULL),
('workTelephone', '', 0, 1, 6, 1, 'getEmpWorkTelephone', 'setEmpWorkTelephone', '{"phone": "true","maxlength":"25"}', 2, 'Work Telephone', 1, '', NULL, NULL),
('workEmail', '', 0, 1, 6, 1, 'getEmpWorkEmail', 'setEmpWorkEmail', '', 2, 'Work Email', 1, '', NULL, NULL),
('otherEmail', '', 0, 1, 6, 1, 'getEmpOthEmail', 'setEmpOthEmail', '', 2, 'Other Email', 1, '', NULL, NULL),
('joinedDate', '', 0, 1, 1, 3, 'getJoinedDate', 'setJoinedDate', '', 5, 'Joined Date', 1, '', NULL, NULL),
('jobTitle', 'getJobTitleId', 0, 1, 1, 2, 'getJobTitleId', 'setJobTitleId', '', 5, 'Job Title', 1, '{\n  "service": "JobTitleService",\n  "method": "getJobTitleList",\n  "key": "getId",\n  "value": "getJobTitleName"\n}', 'SERVICE', NULL),
('employmentStatus', 'getEmploymentStatusId', 0, 1, 3, 2, 'getEmploymentStatusId', 'setEmploymentStatusId', '', 5, 'Employment Status', 1, '{\n  "service": "EmploymentStatusService",\n  "method": "getEmploymentStatusList",\n  "key": "getId",\n  "value": "getName"\n}', 'SERVICE', NULL),
('subUnit', 'getSubunitId', 0, 1, 3, 2, 'getSubunitId', 'setSubunitId', '', 5, 'Sub Unit', 1, 'getSubDivisions', 'EXSERVICE', NULL),
('location', 'getLocationId', 0, 1, 3, 2, 'getLocationId', 'setLocationId', '', 5, 'Location', 1, 'getLocations', 'EXSERVICE', NULL);
SET @module_id = (SELECT `id` FROM `ohrm_module` WHERE `name` = 'pim');


INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`)
VALUES ('Add Employee Wizard', @module_id, 'pimFieldConfiguration');

SET @screenId := (SELECT LAST_INSERT_ID());
SET @parentId := (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'Configuration' AND `parent_id` in (SELECT id FROM `ohrm_menu_item` WHERE `menu_title` = 'PIM'));

INSERT INTO `ohrm_menu_item` (`menu_title`, `menu_category`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`)
VALUES ('Add Employee Wizard', NULL, @screenId, @parentId, '3', 400, NULL, '1');

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

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES ('add_employee_wizard', 'PIM - Configure - Add Employee Wizard', '1', '1', '1', '1');

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

SET @userRoleTypeId := (SELECT `id` FROM  `ohrm_user_role_type` WHERE  `name` ='Admin');


INSERT INTO `ohrm_menu_item_data_group` (`menu_item_id`, `data_group_id`)
VALUES (@menuItemId, @addEmployeeWizardDataGroupId);

INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`, `self`)
VALUES (@addEmployeeWizardDataGroupId, @screenId, '1', '0');

SET @userRoleId := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'Admin');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES (@userRoleId, @screenId, '1', '1', '1', '1');

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`, `restriction_id`)
VALUES (@userRoleId, @addEmployeeWizardDataGroupId, '1', '1', '1', '1', '1', NULL);

INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@userRoleTypeId, @addEmployeeWizardDataGroupId, '1', '1', '1', '1');

 


CREATE TABLE `ohrm_wizard_temp_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_id` int(11) NOT NULL,
  `logged_user`  int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


SET @scheduled_notification_type := (SELECT id FROM ohrm_notification_type where `name`='Scheduled');
SET @email_media := (SELECT id FROM ohrm_notification_media WHERE `name` = 'Email');
SET @pim_module := (SELECT `id` FROM `ohrm_module` WHERE  `name` ='pim');

INSERT INTO `ohrm_notification`
    (`name`, `is_enable`,`type_id`, `generator_class`, `workflow_id`, `workflow_action`,`module_id`) VALUES
    ('Employee Termination', 0, @scheduled_notification_type, 'EmployeeTerminationEmailProcessor', NULL, NULL,@pim_module );
SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_template`
(`notification_id`, `media_id`, `locale`, `performer_role`, `recipient_role`, `subject`, `body`) VALUES
(@notification_id, @email_media, 'en_US', NULL, NULL,
'Employee Termination Notification',
'Hi %subscriberName%,

 %body%

Thank you
System Admin
(This is an auto-generated email, please do not reply!) ');


ALTER TABLE `ohrm_emp_termination`  ADD `flaged` INT(1) NOT NULL DEFAULT '0' AFTER `note`;

ALTER TABLE `ohrm_job_record` ADD COLUMN `creditable_service` VARCHAR(45) NULL AFTER `contracted_hours`;

INSERT INTO `ohrm_cron_task` (  `name`, `short_description`, `long_description`, `command`, `observer_class`, `status`) VALUES
( 'Terminate Employment', 'This process Terminate Employees who has been marked for Terminate on given date.', 'This task is executed on a daily basis on production systems.It processes  Terminate Employees who has been marked for Terminate on given date', 'php symfony orangehrm:terminate-employment', '', 1);

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

INSERT INTO `ohrm_cron_inputs` (  `task_id`, `name`, `label`, `description`, `active`, `required`, `position`, `widget_creator`, `type`, `data_type`) VALUES
 (  @task_id, '--systemDate', 'Date To Run', 'User defined system date', 1, 0, 1, 'DateWidgetCreator', 'parameter', 'date');


 SET @moduleId := (SELECT `id` FROM `ohrm_module` where `name`='admin');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES
('Restricted UserRoles', @moduleId, 'addRestrictedUserRoles');

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

SET @admin_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'Admin');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('data_group_restriction', 'Data Group Restriction', '1', '1', '1', '1');

SET @dataGroupId := (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`,`restriction_id`)
VALUES (@admin_role_id,@dataGroupId, 1, 1, 1, 1, 0, null);

INSERT INTO `ohrm_user_role_screen` (`user_role_id`,`screen_id`,`can_read`,`can_create`,`can_update`,`can_delete`)
VALUES (@admin_role_id,@screenId , 1, 1, 1, 1);

INSERT INTO `ohrm_data_group_screen` (`data_group_id`,`screen_id`,`permission`,`self`)
VALUES(@dataGroupId, @screenId ,1,1);


SET @dig := (SELECT id FROM `ohrm_data_group` WHERE `name` LIKE 'tax_exemptions');
SET @id := (select id from ohrm_menu_item_data_group WHERE  `menu_item_id` in (SELECT id  FROM `ohrm_menu_item` WHERE `menu_title` = 'Tax Exemptions') and `data_group_id`  is NULL) ;
UPDATE `ohrm_menu_item_data_group` SET  `data_group_id`= @dig WHERE id = @id;