CREATE TABLE IF NOT EXISTS `ohrm_salary_component` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(60) NOT NULL UNIQUE,
    `type` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `cost_to_company` TINYINT UNSIGNED NOT NULL DEFAULT 2,
    `value_type` TINYINT UNSIGNED NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INSERT INTO `ohrm_salary_component` VALUES (1, 'Annual Basic Payment', 1, 2, 1);

CREATE TABLE IF NOT EXISTS `ohrm_employee_salary_component` (
    `employee_number` INT(7) NOT NULL,
    `salary_component_id` INT UNSIGNED NOT NULL,
    `value` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
    `is_active` TINYINT DEFAULT 0,
    CONSTRAINT PRIMARY KEY(`employee_number`, `salary_component_id`),
    CONSTRAINT FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
    CONSTRAINT FOREIGN KEY (`salary_component_id`) REFERENCES `ohrm_salary_component` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_job_record` (
    `employee_number` INT(7) PRIMARY KEY,
    `job_title_id` INT(13),
    `employment_status_id` INT,
    `job_category_id` INT,
    `joined_date` DATE,
    `probation_end_date` DATE,
    `date_of_permanency` DATE,
    `effective_date` DATE,
    `subunit_id` INT(6),
    `location_id` INT,
    `comment` VARCHAR(250),
    `has_contract_details` TINYINT UNSIGNED DEFAULT 0,
    `contract_start_date` DATE,
    `contract_end_date` DATE,
    `contract_details` BLOB,
    CONSTRAINT FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
    CONSTRAINT FOREIGN KEY (`job_title_id`) REFERENCES `ohrm_job_title` (`id`) ON DELETE SET NULL,
    CONSTRAINT FOREIGN KEY (`employment_status_id`) REFERENCES `ohrm_employment_status` (`id`) ON DELETE SET NULL,
    CONSTRAINT FOREIGN KEY (`job_category_id`) REFERENCES `ohrm_job_category` (`id`) ON DELETE SET NULL,
    CONSTRAINT FOREIGN KEY (`subunit_id`) REFERENCES `ohrm_subunit` (`id`) ON DELETE SET NULL,
    CONSTRAINT FOREIGN KEY (`location_id`) REFERENCES `ohrm_location` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_job_history_record` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `employee_number` INT(7) NOT NULL,
    `job_title_name` VARCHAR(100),
    `employement_status_name` VARCHAR(60),
    `job_category_name` VARCHAR(50),
    `joined_date` DATE,
    `probation_end_date` DATE,
    `date_of_permanency` DATE,
    `effective_date` DATE,
    `comment` VARCHAR(250),
    `subunit_name` VARCHAR(100),
    `location_name` VARCHAR(110),
    `contract_start_date` DATE,
    `contract_end_date` DATE,
    CONSTRAINT FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_salary_history_record` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `employee_number` INT(7) NOT NULL,
    `pay_grade` VARCHAR(60),
    `currency` VARCHAR(70),
    `basic_payment` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
    `earnings` VARCHAR(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
    `deductions` VARCHAR(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT '',
    `effective_date` DATE,
    `end_date` DATE,
    `comment` VARCHAR(250),
    CONSTRAINT FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_salary_record` (
    `employee_number` INT(7) PRIMARY KEY,
    `pay_grade_id` INT,
    `currency_id` CHAR(3),
    `effective_date` DATE NOT NULL,
    `comment` VARCHAR(250),
    CONSTRAINT FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
    CONSTRAINT FOREIGN KEY (`pay_grade_id`) REFERENCES `ohrm_pay_grade` (`id`) ON DELETE RESTRICT,
    CONSTRAINT FOREIGN KEY (`currency_id`) REFERENCES `hs_hr_currency_type` (`currency_id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_direct_deposit_record` (
    `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `employee_number` INT(7) NOT NULL,
    `amount` VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
    `account_number` VARCHAR(100),
    `institute_name` VARCHAR(255),
    `account_type` VARCHAR(20),
    `branch_location` VARCHAR(255),
    `routing_number` VARCHAR(255),
    CONSTRAINT FOREIGN KEY (`employee_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

ALTER TABLE `ohrm_job_history_record` ADD `end_date` DATE NULL DEFAULT NULL; 
ALTER TABLE `ohrm_employee_salary_component` ADD `effective_date` DATE NULL DEFAULT NULL AFTER `value`; 
ALTER TABLE `ohrm_job_history_record` ADD `contracted_hours` VARCHAR( 10 ) NULL DEFAULT NULL ;
ALTER TABLE `ohrm_job_record` ADD `contracted_hours` VARCHAR( 10 ) NULL DEFAULT NULL;
ALTER TABLE `hs_hr_emp_contract_extend` ADD `contracted_hours` VARCHAR( 10 ) NULL DEFAULT NULL;

INSERT INTO `ohrm_screen` (`name` ,`module_id` ,`action_url`)VALUES 
( 'View Salary Components', '2', 'viewSalaryComponentList');
SET @screen_id := (SELECT LAST_INSERT_ID());
INSERT INTO `ohrm_user_role_screen` 
(`user_role_id` ,`screen_id` ,`can_read` ,`can_create` ,`can_update` ,`can_delete`)VALUES 
( '1', @screen_id, '1', '1', '1', '1');
INSERT INTO `ohrm_menu_item` ( `menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) 
VALUES( 'Salary Components', @screen_id, 6, 3, 100, NULL, 1);

INSERT INTO `ohrm_display_field_group`(`report_group_id`, `name`, `is_list`) VALUES
(3, 'Direct Deposit Details', true);

INSERT INTO `ohrm_display_field` (`report_group_id`, `name`, `label`, `field_alias`, `is_sortable`, `sort_order`, `sort_field`, `element_type`, `element_property`, `width`, `is_exportable`, `text_alignment_style`, `is_value_list`, `display_field_group_id`, `default_value`, `is_encrypted`, `is_meta`) VALUES
    (3, 'ohrm_job_record.probation_end_date', 'Probation End Date', 'getProbationEndDate',  'false', null, null, 'labelDate', '<xml><getter>getProbationEndDate</getter></xml>', 200, '0', null, true, 6, '---', false, false),
    (3, 'ohrm_job_record.date_of_permanency', 'Date of Permanency', 'getDateOfPermanency',  'false', null, null, 'labelDate', '<xml><getter>getDateOfPermanency</getter></xml>', 200, '0', null, true, 6, '---', false, false),
    (3, 'ohrm_job_record.effective_date', 'Effective Date', 'getEffectiveDate',  'false', null, null, 'labelDate', '<xml><getter>getEffectiveDate</getter></xml>', 200, '0', null, true, 6, '---', false, false),
    (3, 'ohrm_job_record.comment', 'Comment', 'getComment',  'false', null, null, 'label', '<xml><getter>getComment</getter></xml>', 200, '0', null, true, 6, '---', false, false);

INSERT INTO `ohrm_data_group` ( `name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
( 'activate_employee', 'PIM - Job - Activate Employee', 1, NULL, 1, NULL);
SET @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 
(1, @data_group_id, 1, NULL, 1, NULL, 0);

INSERT INTO `ohrm_data_group` ( `name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
( 'terminate_employee', 'PIM - Job - Terminate Employee', 1, NULL, 1, NULL);
SET @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 
(1, @data_group_id, 1, NULL, 1, NULL, 0);

INSERT INTO `ohrm_display_field` (`report_group_id`, `name`, `label`, `field_alias`, `is_sortable`, `sort_order`, `sort_field`, `element_type`, `element_property`, `width`, `is_exportable`, `text_alignment_style`, `is_value_list`, `display_field_group_id`, `default_value`, `is_encrypted`, `is_meta`) VALUES
    (3, 'ohrm_salary_record.effective_date', 'Effective Date', 'getEffectiveDate',  'false', null, null, 'labelDate', '<xml><getter>getEffectiveDate</getter></xml>', 200, '0', null, true, 7, '---', false, false),
    (3, 'ohrm_salary_record.comment', 'Comment', 'getComment',  'false', null, null, 'label', '<xml><getter>getComment</getter></xml>', 200, '0', null, true, 7, '---', false, false);

INSERT INTO `ohrm_display_field` (`report_group_id`, `name`, `label`, `field_alias`, `is_sortable`, `sort_order`, `sort_field`, `element_type`, `element_property`, `width`, `is_exportable`, `text_alignment_style`, `is_value_list`, `display_field_group_id`, `default_value`, `is_encrypted`, `is_meta`) VALUES
    (3, "CONCAT(IF( ohrm_salary_component.type = 1,'Earnings :','Deductions :'),' ',ohrm_salary_component.name,' ', {{ohrm_employee_salary_component.value}}, ' / ', IFNULL(ohrm_employee_salary_component.effective_date,''))", 'All Earnings/Deductions', 'getAllEarningDeductions',  'false', null, null, 'label', '<xml><getter>getAllEarningDeductions</getter></xml>', 200, '0', null, true, 7, '---', true, false);

INSERT INTO `ohrm_display_field` (`report_group_id`, `name`, `label`, `field_alias`, `is_sortable`, `sort_order`, `sort_field`, `element_type`, `element_property`, `width`, `is_exportable`, `text_alignment_style`, `is_value_list`, `display_field_group_id`, `default_value`, `is_encrypted`, `is_meta`) VALUES
    (3, 'ohrm_direct_deposit_record.amount', 'Direct Deposit Amount', 'direct_deposit_amount',  'false', null, null, 'label', '<xml><getter>direct_deposit_amount</getter></xml>', 200, '0', null, true, 17, '---', true, false),
    (3, 'ohrm_direct_deposit_record.account_number', 'Account Number', 'direct_deposit_account_number',  'false', null, null, 'label', '<xml><getter>direct_deposit_account_number</getter></xml>', 200, '0', null, true, 17, '---', false, false),
    (3, 'ohrm_direct_deposit_record.institute_name', 'Name of the Financial Institute', 'direct_deposit_institute_name',  'false', null, null, 'label', '<xml><getter>direct_deposit_institute_name</getter></xml>', 200, '0', null, true, 17, '---', false, false),
    (3, 'ohrm_direct_deposit_record.account_type', 'Account Type', 'direct_deposit_account_type',  'false', null, null, 'label', '<xml><getter>direct_deposit_account_type</getter></xml>', 200, '0', null, true, 17, '---', false, false),
    (3, 'ohrm_direct_deposit_record.branch_location', 'Branch Location', 'direct_deposit_branch_location',  'false', null, null, 'label', '<xml><getter>direct_deposit_branch_location</getter></xml>', 200, '0', null, true, 17, '---', false, false),
    (3, 'ohrm_direct_deposit_record.routing_number', 'Routing Number', 'direct_deposit_routing_number',  'false', null, null, 'label', '<xml><getter>direct_deposit_routing_number</getter></xml>', 200, '0', null, true, 17, '---', false, false);

UPDATE `ohrm_report_group` SET `core_sql` = 'SELECT selectCondition FROM hs_hr_employee 
                    LEFT JOIN hs_hr_emp_emergency_contacts ON 
                        (hs_hr_employee.emp_number = hs_hr_emp_emergency_contacts.emp_number) 
                    LEFT JOIN ohrm_subunit ON 
                        (hs_hr_employee.work_station = ohrm_subunit.id) 
                    LEFT JOIN ohrm_employment_status ON 
                        (hs_hr_employee.emp_status = ohrm_employment_status.id) 
                    LEFT JOIN ohrm_job_title ON
                        (hs_hr_employee.job_title_code = ohrm_job_title.id)
                    LEFT JOIN ohrm_job_category ON 
                        (hs_hr_employee.eeo_cat_code = ohrm_job_category.id) 
                    LEFT JOIN ohrm_nationality ON
                        (hs_hr_employee.nation_code = ohrm_nationality.id)
                    LEFT JOIN hs_hr_emp_dependents ON 
                        (hs_hr_employee.emp_number = hs_hr_emp_dependents.emp_number)
                    LEFT JOIN hs_hr_emp_locations AS emp_location ON
                        (hs_hr_employee.emp_number = emp_location.emp_number)
                    LEFT JOIN ohrm_location ON
                        (emp_location.location_id = ohrm_location.id)
                    LEFT JOIN hs_hr_emp_contract_extend ON 
                        (hs_hr_employee.emp_number = hs_hr_emp_contract_extend.emp_number) 
                    LEFT JOIN ohrm_salary_record ON 
                        (hs_hr_employee.emp_number = ohrm_salary_record.employee_number) 
                    LEFT JOIN ohrm_pay_grade ON 
                        (ohrm_salary_record.pay_grade_id = ohrm_pay_grade.id) 
                    LEFT JOIN hs_hr_currency_type ON 
                        (ohrm_salary_record.currency_id = hs_hr_currency_type.currency_id)                    
                    LEFT JOIN hs_hr_emp_passport ON 
                        (hs_hr_employee.emp_number = hs_hr_emp_passport.emp_number) 
                    LEFT JOIN hs_hr_emp_reportto AS subordinate_list ON 
                        (hs_hr_employee.emp_number = subordinate_list.erep_sup_emp_number) 
                    LEFT JOIN hs_hr_employee AS subordinate ON
                        (subordinate.emp_number = subordinate_list.erep_sub_emp_number)
                    LEFT JOIN ohrm_emp_reporting_method AS subordinate_reporting_method ON 
                        (subordinate_list.erep_reporting_mode = subordinate_reporting_method.reporting_method_id) 
                    LEFT JOIN hs_hr_emp_work_experience ON 
                        (hs_hr_employee.emp_number = hs_hr_emp_work_experience.emp_number) 
                    LEFT JOIN ohrm_emp_education ON 
                        (hs_hr_employee.emp_number = ohrm_emp_education.emp_number) 
                    LEFT JOIN ohrm_education ON 
                        (ohrm_emp_education.education_id = ohrm_education.id) 
                    LEFT JOIN hs_hr_emp_skill ON 
                        (hs_hr_employee.emp_number = hs_hr_emp_skill.emp_number) 
                    LEFT JOIN ohrm_skill ON 
                        (hs_hr_emp_skill.skill_id = ohrm_skill.id) 
                    LEFT JOIN hs_hr_emp_language ON 
                        (hs_hr_employee.emp_number = hs_hr_emp_language.emp_number) 
                    LEFT JOIN ohrm_language ON 
                        (hs_hr_emp_language.lang_id = ohrm_language.id) 
                    LEFT JOIN ohrm_emp_license ON 
                        (hs_hr_employee.emp_number = ohrm_emp_license.emp_number) 
                    LEFT JOIN ohrm_license ON 
                        (ohrm_emp_license.license_id = ohrm_license.id) 
                    LEFT JOIN hs_hr_emp_member_detail ON 
                        (hs_hr_employee.emp_number = hs_hr_emp_member_detail.emp_number) 
                    LEFT JOIN ohrm_membership ON
                        (hs_hr_emp_member_detail.membship_code = ohrm_membership.id)
                    LEFT JOIN hs_hr_country ON 
                        (hs_hr_employee.coun_code = hs_hr_country.cou_code)                    
                    LEFT JOIN hs_hr_emp_reportto AS supervisor_list ON 
                        (hs_hr_employee.emp_number = supervisor_list.erep_sub_emp_number) 
                    LEFT JOIN hs_hr_employee AS supervisor ON
                        (supervisor.emp_number = supervisor_list.erep_sup_emp_number)
                    LEFT JOIN ohrm_emp_reporting_method AS supervisor_reporting_method ON 
                        (supervisor_list.erep_reporting_mode = supervisor_reporting_method.reporting_method_id) 
                    LEFT JOIN ohrm_emp_termination ON
                        (hs_hr_employee.termination_id = ohrm_emp_termination.id)
                    LEFT JOIN ohrm_emp_termination_reason ON
                        (ohrm_emp_termination.reason_id = ohrm_emp_termination_reason.id)
                     LEFT JOIN ohrm_job_record ON
                        (ohrm_job_record.employee_number = hs_hr_employee.emp_number)                     
                     LEFT JOIN ohrm_direct_deposit_record ON
                        (ohrm_direct_deposit_record.employee_number = hs_hr_employee.emp_number)
                     LEFT JOIN ohrm_employee_salary_component ON
                        (ohrm_employee_salary_component.employee_number = hs_hr_employee.emp_number AND ohrm_employee_salary_component.is_active = 1)
                     LEFT JOIN ohrm_salary_component ON
                        (ohrm_salary_component.id = ohrm_employee_salary_component.salary_component_id) 
                WHERE hs_hr_employee.emp_number in (
                    SELECT hs_hr_employee.emp_number FROM hs_hr_employee                        
                        LEFT JOIN ohrm_emp_education ON 
                            (hs_hr_employee.emp_number = ohrm_emp_education.emp_number) 
                        LEFT JOIN hs_hr_emp_skill ON 
                            (hs_hr_employee.emp_number = hs_hr_emp_skill.emp_number) 
                        LEFT JOIN hs_hr_emp_language ON 
                            (hs_hr_employee.emp_number = hs_hr_emp_language.emp_number) 
                    WHERE whereCondition1
                )
                
                GROUP BY 
                     hs_hr_employee.emp_number,
                     hs_hr_employee.emp_lastname,
                     hs_hr_employee.emp_firstname,
                     hs_hr_employee.emp_middle_name,
                     hs_hr_employee.emp_birthday,
                     ohrm_nationality.name,
                     hs_hr_employee.emp_gender,
                     hs_hr_employee.emp_marital_status,
                     hs_hr_employee.emp_dri_lice_num,
                     hs_hr_employee.emp_dri_lice_exp_date,
                     hs_hr_employee.emp_street1,
                     hs_hr_employee.emp_street2,
                     hs_hr_employee.city_code,
                     hs_hr_employee.provin_code,
                     hs_hr_employee.emp_zipcode,
                     hs_hr_country.cou_code,
                     hs_hr_employee.emp_hm_telephone,
                     hs_hr_employee.emp_mobile,
                     hs_hr_employee.emp_work_telephone,
                     hs_hr_employee.emp_work_email,
                     hs_hr_employee.emp_oth_email

ORDER BY hs_hr_employee.emp_lastname' WHERE `ohrm_report_group`.`report_group_id` = 3;

DELETE FROM `ohrm_display_field` WHERE `display_field_id` in (71,72,73,74,66,67,68,69,101);

ALTER TABLE `ohrm_salary_record` CHANGE `effective_date` `effective_date` DATE NULL DEFAULT NULL;
-- DELETE FROM `ohrm_display_field` WHERE `ohrm_display_field`.`display_field_id` = 126;

UPDATE `ohrm_data_group` SET `name` = 'job_history_details' WHERE `ohrm_data_group`.`id` = 16;
UPDATE `ohrm_data_group` SET `name` = 'salary_history_details' WHERE `ohrm_data_group`.`id` = 19;

