CREATE TABLE ohrm_bulkdata_uploadprocess (
  `id` INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `data_entity_mapping` VARCHAR(50000),
  `status` VARCHAR (20) NOT NULL,
  `status_map` VARCHAR (50000),
  `settings` VARCHAR (5000),
  `name` VARCHAR (100) NOT NULL,
  `description` VARCHAR (500),
  `file_id` INT(4),
  `completed_phase_count` INT(2) DEFAULT 0,
  `total_phase_count` INT(2) DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ended_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

CREATE TABLE ohrm_bulkdata_uploadfile (
  `id` INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `file` MEDIUMBLOB NOT NULL ,
  `template_id` INT(4) NOT NULL,
  `type` VARCHAR(200) NOT NULL ,
  `size` INT(10) NOT NULL,
  `path` VARCHAR (200) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE TABLE ohrm_bulkdata_uploadtemplate(
  `id` INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR (100) NOT NULL UNIQUE,
  `description` VARCHAR (5000) NOT NULL,
  `setting_map` VARCHAR (500000) NOT NULL,
  `enabled` TINYINT NOT NULL DEFAULT 0
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE TABLE ohrm_bulkdata_uploadsheet(
  `id` INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `sheet_name` VARCHAR (100) NOT NULL,
  `mandatory` INT(1) NOT NULL,
  `template_id` INT(4) NOT NULL,
  `setting_map` VARCHAR (500000) NOT NULL,
  `description` VARCHAR (5000) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;


ALTER TABLE `ohrm_bulkdata_uploadprocess`
  ADD CONSTRAINT `ohrm_bulkdata_uploadprocess_file` FOREIGN KEY (`file_id`) REFERENCES `ohrm_bulkdata_uploadfile` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_bulkdata_uploadfile`
  ADD CONSTRAINT `ohrm_bulkdata_uploadfile_template` FOREIGN KEY (`template_id`) REFERENCES `ohrm_bulkdata_uploadtemplate` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_bulkdata_uploadsheet`
  ADD CONSTRAINT `ohrm_bulkdata_uploadsheet_template` FOREIGN KEY (`template_id`) REFERENCES `ohrm_bulkdata_uploadtemplate` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO `ohrm_bulkdata_uploadtemplate` (`name`, `description`,`enabled`,`setting_map`) VALUES ('Admin','Admin template can be used to upload bulk data about Admin configuration details....',1,'{
   "download":{
    "input": {
      "numberOfRecords": {
        "displayText": "Number of Records",
        "placeholder" : "total number of records",
        "type": "number",
        "max": "2500",
        "min": "1"
      }
    }
  },
   "upload":{
    "message" : "Please make sure the detail you provide are correct as this will be used to configure the system.",
    "input": {
      "checkCompanyStructure": {
        "displayText": "I understand the consequences of inserting invalid information",
        "type": "checkbox"
      }
    }
  }
}');

SET @bulkdata_admin_template_id = (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_bulkdata_uploadtemplate` (`name`, `description`,`enabled`,`setting_map`) VALUES ('PIM','PIM template can be used to upload bulk data about Employee details....',1,'{
  "download":{
    "input": {
      "numberOfEmployees": {
        "displayText": "Number of Employees",
        "placeholder" : "total number of employees",
        "type": "number",
        "max": "2500",
        "min": "1"
      }
    }
  },
  "upload":{
    "message" : "Please note that before continuing the bulk data upload you need to make sure following admin data are configured.",
    "input": {
      "checkCompanyStructure": {
        "displayText": "I have completed the Company Structure",
        "type": "checkbox"
      }
    }
  }
  }');

SET @bulkdata_pim_template_id = (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_bulkdata_uploadtemplate` (`name`, `description`,`enabled`,`setting_map`) VALUES ('Leave','Leave template can be used to upload bulk data about Leave details....',1,'{
  "download":{
    "input": {
      "numberOfRecords": {
        "displayText": "Number of Records",
        "placeholder" : "total number of records",
        "type": "number",
        "max": "2500",
        "min": "1"
      }
    }
  },
  "upload":{
    "message" : "Please note that before continuing the bulk data upload you need to make sure following leave data are configured.",
    "input": {
      "checkLeavePeriod": {
        "displayText": "I have defined the Leave Period",
        "type": "checkbox"
      }
    }
  }

  }');

SET @bulkdata_leave_template_id = (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_bulkdata_uploadtemplate` (`name`, `description`,`setting_map`) VALUES ('Time Sheet','TimeSheet template can be used to upload bulk data about Time sheet details....','{}');

SET @bulkdata_timesheet_template_id = (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_bulkdata_uploadtemplate` (`name`, `description`,`enabled`,`setting_map`) VALUES ('Performance','Performance template can be used to upload bulk data about Performance details....',1,'{
   "download":{
    "input": {
      "numberOfRecords": {
        "displayText": "Number of Records",
        "placeholder" : "total number of records",
        "type": "number",
        "max": "2500",
        "min": "1"
      }
    }
  },
   "upload":{
    "message" : "Please make sure the detail you provide are correct as this will be used to configure the system.",
    "input": {
      "checkCompanyStructure": {
        "displayText": "I understand the consequences of inserting invalid information",
        "type": "checkbox"
      }
    }
  }
}');

SET @bulkdata_performance_template_id = (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Custom Tab','This sheet will configure new tabs for PIM module',@bulkdata_admin_template_id,'{"phases": ["Column Mapping", "Custom Tab"],"input": {
   "mode": {
      "displayText": "Select Upload Mode",
      "type": "dropdown",
      "values": [
        "Add new tab"
      ]
    }
  }}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Custom Section ','This sheet will configure new sections for PIM module',@bulkdata_admin_template_id,'{"phases":["Column Mapping","Validation","Verification","Save Custom Field Section Data"],"input":{"mode":{"displayText":"Select Upload Mode","type":"dropdown","values":["Add Custom Field Section"]}}}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Custom Field','This sheet will configure new fields for PIM module',@bulkdata_admin_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Custom Field Data"]}',0);

INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Salary Component','This sheet will configure salary component details',@bulkdata_admin_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Salary Components"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Location','This sheet will configure location details',@bulkdata_admin_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Location Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Job Title','This sheet will configure job titles',@bulkdata_admin_template_id, '{"phases": ["Column Mapping", "Validation", "Verification", "Save Job Title"]}', 0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Skill','This sheet will configure qualification skills',@bulkdata_admin_template_id, '{"phases": ["Column Mapping", "Validation", "Verification", "Save Data"]}', 0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Education','This sheet will configure qualification education',@bulkdata_admin_template_id, '{"phases": ["Column Mapping", "Validation", "Verification", "Save Data"]}', 0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('License','This sheet will configure qualification license',@bulkdata_admin_template_id, '{"phases": ["Column Mapping", "Validation", "Verification", "Save Data"]}', 0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Language','This sheet will configure qualification language',@bulkdata_admin_template_id, '{"phases": ["Column Mapping", "Validation", "Verification", "Save Data"]}', 0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Membership','This sheet will configure qualification Membership',@bulkdata_admin_template_id, '{"phases": ["Column Mapping", "Validation", "Verification", "Save Data"]}', 0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Competency', 'This sheet will configure Competency details', @bulkdata_admin_template_id, '{"phases":["Column Mapping","Validate Competency Data","Verification","Save Competency Data"],"input":{"mode":{"displayText":"Select Upload Mode","type":"dropdown","values":["Add new Competency"]}}}', 0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Work Schedule','This sheet will configure work schedule details',@bulkdata_admin_template_id, '{"phases": ["Column Mapping", "Validate Work Schedule Data", "Verification", "Save Work Schedule Data"]}', 0);

INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Information','This sheet will upload personal information of each employee',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validate Employee", "Verification", "Save Employee Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Termination','This sheet will upload termination information of employees',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validate Employee Termination", "Verification", "Save Employee Termination", "Termination"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Job Record','This sheet will upload job record details of employees',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validate Job Data", "Verification", "Save Job Data", "Job Record"],"input": {"mode": {"displayText": "Select Upload Mode","type": "dropdown","values" : {"2": "Insert new records + Update existing records with the given data","4": "ONLY update existing records with the given data"}}}}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Salary Record','This sheet will upload salary record details of employees',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Salary Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Salary Component','This sheet will upload salary component details of employees',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validate Salary Component", "Verification", "Save Salary Component"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Supervisor Reporting','This sheet will upload supervisor and subordinate relationship',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validate Supervisor Reporting", "Verification", "Save Supervisor Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Direct Deposit Record','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Direct Deposit Record"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Dependent','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Dependent Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Emergency Contact','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validate Emergency Contact", "Verification", "Save Emergency Contact Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Education','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validate Employee Education", "Verification", "Save Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Immigration Record ','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validate Employee Immigration", "Verification", "Save Immigration Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Language','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee License','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Membership','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Skill','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Employee Work Experience','',@bulkdata_pim_template_id,'{"phases": ["Column Mapping", "Validation", "Verification", "Save Work Experience Data"]}',0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('System User','',@bulkdata_pim_template_id,'{"phases": ["System User Mapping", "Column Mapping", "Validate System User", "Verification", "Save User Data" ], "input": { "checkUserRole":{ "displayText" : "System user roles created", "type" : "checkbox" }, "userNamePattern": { "displayText": "Select pattern to create usernames", "type": "dropdown", "values": { "UpdateFromSheet" : "Update from Sheet", "firstname-lastname" : "firstname-lastname", "firstname.lastname" : "firstname.lastname", "firstnamelastname" : "firstnamelastname", "firstname" : "firstname", "lastname" : "lastname" }}}}',0);

INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('bulkdata', 1);
SET @bulkdata_module_id = (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`, `route_name`) VALUES ('Bulk Data',@bulkdata_module_id,'main', 'bulkdata_main');
SET @bulkdata_screen_id = (SELECT LAST_INSERT_ID());

SET @system_administration_menu_id = (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'System Administration' AND parent_id IS NULL AND level = 1);
SET @management_tools_menu_id = (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Management Tools' AND `parent_id` = @system_administration_menu_id);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
    ('Bulk Data Upload', @bulkdata_screen_id, @management_tools_menu_id, 3, 300, NULL, 1);

INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES
(4, @bulkdata_screen_id);


SET @admin_user_role_id := 1;
SET @ess_user_role_id := 2;
SET @supervisor_user_role_id := 3;
SET @config_admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ConfigAdmin');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_user_role_id, @bulkdata_screen_id, 0, 0, 0, 0),
(@ess_user_role_id, @bulkdata_screen_id, 0, 0, 0, 0),
(@supervisor_user_role_id, @bulkdata_screen_id, 0, 0, 0, 0),
(@config_admin_role_id, @bulkdata_screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Public Holiday','This sheet will add public holidays',@bulkdata_leave_template_id,'{"phases": ["Column Mapping", "Validate Public Holiday Data", "Verification", "Save Holiday Data"]}',0);

INSERT INTO `ohrm_bulkdata_uploadsheet` (`id`, `sheet_name`, `mandatory`, `template_id`, `setting_map`, `description`) VALUES (NULL, 'Entitlement', '0', @bulkdata_leave_template_id, '{"phases": ["Column Mapping", "Validate Entitlement Data", "Verification", "Save Entitlement Data"], "input": {"mode": {"displayText": "Select Upload Mode", "type": "dropdown", "values": {"1": "Delete existing records and add new"}}}}', 'This sheet will add entitlements');

INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Question', 'This sheet will configure Question details', @bulkdata_performance_template_id, '{"phases":["Column Mapping","Validate Question Data","Verification","Save Question Data"],"input":{"mode":{"displayText":"Select Upload Mode","type":"dropdown","values":["Add new Question"]}}}', 0);
INSERT INTO `ohrm_bulkdata_uploadsheet` (`sheet_name`,`description`,`template_id`,`setting_map`,`mandatory`) VALUES ('Goal', 'This sheet will configure Goal details', @bulkdata_performance_template_id, '{"phases":["Column Mapping","Validate Goal Data","Verification","Save Goal Data"],"input":{"mode":{"displayText":"Select Upload Mode","type":"dropdown","values":["Add new Goal"]}}}', 0);

CREATE TABLE `ohrm_bulkdata_upload_entity` (
  `id` INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `entity_name` VARCHAR(300) NOT NULL ,
  `sheet_id` INT(4) NOT NULL ,
  `is_primary_entity` TINYINT NOT NULL ,
  `is_custom_entity` TINYINT NOT NULL,
  `setting_map` VARCHAR (500000),
  CONSTRAINT `fk_ohrm_bulkdata_upload_entity_sheet_id` FOREIGN KEY (`sheet_id`) REFERENCES `ohrm_bulkdata_uploadsheet` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'CustomFieldScreen', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Custom Tab';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'CustomFieldSection', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Custom Section ';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'CustomField', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Custom Field';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'SalaryComponent', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Salary Component';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'Location', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Location';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'JobTitle', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Job Title';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'Skill', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Skill';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'Education', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Education';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'License', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'License';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'Language', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Language';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'Membership', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Membership';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'Employee', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Information';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeTerminationRecord', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Termination';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'JobRecordHistory', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Job Record';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'SalaryRecordHistory', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Salary Record';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`, `setting_map`) SELECT 'EmployeeSalaryComponentHistory', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0, '{"Additional Columns": [{"fieldName": "employee_id", "type": "string", "bulkdataupload": {"displayname": "Employee ID", "valueFormatter": "EmployeeIdValueFormatter", "valueCustomValidator": "EmployeeIdValueValidator"}, "length": 50}, {"fieldName": "effective_date", "type": "date", "bulkdataupload": {"displayname": "Effective Date", "valueFormatter": "DateValueFormatter", "valueCustomValidator": "NotNullValidator"}}]}' FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Salary Component';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'ReportTo', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Supervisor Reporting';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'DirectDepositRecord', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Direct Deposit Record';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmpDependent', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Dependent';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmpEmergencyContact', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Emergency Contact';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeEducation', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Education';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeImmigrationRecord', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Immigration Record ';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeLanguage', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Language';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeLicense', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee License';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeMembership', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Membership';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeSkill', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Skill';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmpWorkExperience', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Work Experience';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`, `setting_map`) SELECT 'SystemUser', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0, '{"Additional Columns":[{"fieldName":"setPasswordOnFirstLogin","type":"boolean","bulkdataupload":{"displayname":"Set password on first login","valueRenderer":"YesNoNullValueRenderer","valueFormatter":"TrueFalseFormatter","valueCustomValidator":"PasswordOnFirstLoginValidator"},"length":25}]}' FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'System User';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'Holiday', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Public Holiday';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'LeaveEntitlement', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Entitlement';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'Job ~ Other Job Details', `ohrm_bulkdata_uploadsheet`.`id`, 0, 1 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Employee Job Record';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeCompetency', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Competency';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'Question', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Question';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EvalGroupQuestion', `ohrm_bulkdata_uploadsheet`.`id`, 0, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Question';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'JobTitleQuestion', `ohrm_bulkdata_uploadsheet`.`id`, 0, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Question';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeGoal', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Goal';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`, `setting_map`) SELECT 'GoalMetric', `ohrm_bulkdata_uploadsheet`.`id`, 0, 0, '{"Additional Columns": [{"fieldName": "add_to_library", "type": "boolean", "bulkdataupload": {"displayname": "Add to Library", "valueRenderer": "YesNoNullValueRenderer", "valueFormatter": "TrueFalseFormatter", "valueCustomValidator": "GoalAddToLibraryValidator"}, "length": 25}, {"fieldName": "parent_goal_name", "type": "string", "bulkdataupload": {"displayname": "Parent Goal/Objective Name", "valueFormatter": "ValueTrimmer", "valueCustomValidator": {"ParentGoalValidator": {"properties": {"field": "parent_goal_name"}}}}, "length": 240}, {"fieldName": "parent_goal_owner_id", "type": "string", "bulkdataupload": {"displayname": "Parent Goal/Objective Owner ID", "valueFormatter": "EmployeeIdValueFormatter", "valueCustomValidator": {"ParentGoalValidator": {"properties": {"field": "parent_goal_owner_id"}}}}, "length": 50}]}' FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Goal';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`) SELECT 'EmployeeGoalEvaluatorGroup', `ohrm_bulkdata_uploadsheet`.`id`, 0, 0 FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Goal';
INSERT INTO `ohrm_bulkdata_upload_entity` (`entity_name`, `sheet_id`, `is_primary_entity`, `is_custom_entity`, `setting_map`) SELECT 'WorkSchedule', `ohrm_bulkdata_uploadsheet`.`id`, 1, 0, '{ "Additional Columns": [ { "fieldName": "day_id", "type": "string", "bulkdataupload": { "displayname": "Day", "valueRenderer": "WeekDayRenderer", "valueFormatter": "WeekDayFormatter" } }, { "fieldName": "day_type", "type": "string", "bulkdataupload": { "displayname": "Day Type", "customValues": ["Working Day", "Half Day", "Non-working Day"] } }, { "fieldName": "start_time", "type": "time", "bulkdataupload": { "displayname": "Shift In", "valueFormatter": "TimeFormatter", "valueCustomValidator": "TimeValidator" } }, { "fieldName": "end_time", "type": "time", "bulkdataupload": { "displayname": "Shift Out", "valueFormatter": "TimeFormatter", "valueCustomValidator": "TimeValidator" } }, { "fieldName": "lunch_in", "type": "time", "bulkdataupload": { "displayname": "Break Start", "valueFormatter": "TimeFormatter", "valueCustomValidator": "TimeValidator" } }, { "fieldName": "lunch_out", "type": "time", "bulkdataupload": { "displayname": "Break End", "valueFormatter": "TimeFormatter", "valueCustomValidator": "TimeValidator" } }, { "fieldName": "exclude_break", "type": "boolean", "bulkdataupload": { "displayname": "Exclude break from duration", "customValues": ["Yes", "No"], "valueFormatter": "TrueFalseFormatter" } } ] }' FROM `ohrm_bulkdata_uploadsheet` WHERE  `ohrm_bulkdata_uploadsheet`.`sheet_name` = 'Work Schedule';
