INSERT INTO `ohrm_config` (property, `value`) VALUES ('leave.country_based', 'off')
ON DUPLICATE KEY UPDATE `value` = `value`;
INSERT INTO `ohrm_config` (property, `value`) VALUES ('leave.apply_restrictions', 'on');
INSERT INTO `ohrm_config` (property, `value`) VALUES ('leave.enable_leave_type_leave_period', 'on');

INSERT INTO `ohrm_config` (property, `value`) VALUES
  ('leave.hour_based.minute_decimals', '2'),
  ('leave.hour_based.hour_decimals', '2'),
  ('leave.hour_based.day_decimals', '2'),
  ('leave.hour_based.edit_mode_format', 'decimal');

INSERT INTO ohrm_leave_entitlement_type(`id`,`name`,`is_editable`) VALUES
    (2,'Accrual',1),
    (3,'Carried Forward',1),
    (4,'Brought Forward',1),
    (5,'Added with Expiry',1);

alter table ohrm_leave_type add column leave_rules LONGTEXT default null;

-- Add the default leave rule to any existing leave types
UPDATE ohrm_leave_type SET leave_rules = 
'<?xml version="1.0"?><xml><flow><empapply>1</empapply><adminassign>1</adminassign></flow><applyrestriction/><elgibility><empstatus><include/><exclude/></empstatus><empjobtitles><include/><exclude/></empjobtitles><yearofservice><from/><to/></yearofservice><restriction><moreThanCurrent>0</moreThanCurrent></restriction></elgibility><entitlement><adminAdjust>1</adminAdjust><accure>0</accure><accrualRules><frequency><type/><dayOfCredit/><repeatAfter/><accrualStart/></frequency><accrualGroups/></accrualRules></entitlement><rollover><allow/><expireAfterUnit/><expireAfterUnitValue/><maxAmount/><amount/></rollover></xml>';

CREATE TABLE IF NOT EXISTS `ohrm_emp_leave_accrual` (
      `id` int(11) not null auto_increment,
      `leave_type_id` varchar(13) NOT NULL,
      `entitlement_id` int(7) NOT NULL,
      `emp_number` int(7) NOT NULL,
      `acc_time` date NOT NULL,
      `accrual_amount` decimal(19,9) default NULL,
      PRIMARY KEY (`id`),
      key  `lentitlement_id` (`entitlement_id`),
      key  `employee_id` (`emp_number`),
      key  `leave_type_id` (`leave_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_holiday_location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `holiday_id` int(10) unsigned NOT NULL,
  `location_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `holiday_id` (`holiday_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_leave_accrual_time_schedule` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `country_code` char(2) NOT NULL,
  `location_code` int(11) DEFAULT NULL,
  `accrual_time` time DEFAULT NULL,
  `carry_forward_time` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


--
-- Table structure for table `ohrm_apply_restriction_filter`
--

CREATE TABLE IF NOT EXISTS `ohrm_apply_restriction_filter` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `reference_class_name` varchar(100) DEFAULT NULL,
  `display_text` mediumtext,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------

--
-- Table structure for table `ohrm_apply_restriction_filter_params`
--

CREATE TABLE IF NOT EXISTS `ohrm_apply_restriction_filter_params` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filter_id` int(11) DEFAULT NULL,
  `widget` varchar(100) DEFAULT NULL,
  `lable` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `filter_id_idx` (`filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `ohrm_leave_type_leave_period`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leave_type_id` int(10) UNSIGNED NOT NULL,
  `leave_period_type` int(7) DEFAULT NULL,
  `leave_period_duration` int DEFAULT NULL,
  `start_month` int DEFAULT NULL,
  `start_day` int DEFAULT NULL,
  `default_entitlement_validity` int DEFAULT NULL,
  `created_at` date NOT NULL,
   PRIMARY KEY (`id`),
   FOREIGN KEY (leave_type_id) REFERENCES ohrm_leave_type(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Add leave type leave period for any existing leave types
INSERT INTO ohrm_leave_type_leave_period(leave_type_id, leave_period_type, leave_period_duration, start_month, start_day, created_at)
SELECT lt.id, 1, NULL, NULL, NULL, lp.created_at FROM ohrm_leave_type lt, 
(SELECT created_at FROM ohrm_leave_period_history h ORDER BY id DESC LIMIT 1) as lp;

CREATE TABLE IF NOT EXISTS `ohrm_employee_leave_type_leave_period_history`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_number` int(7) NOT NULL,
  leave_type_id int unsigned not null,
  `leave_period_start_date` date NOT NULL,
  `leave_period_end_date` date NOT NULL,
  `created_at` date NOT NULL,
   PRIMARY KEY (`id`),
   KEY `leave_type_id`(`leave_type_id`),
   KEY  `employee_id` (`emp_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_first_accrual_option` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `implementation_class` varchar(255) DEFAULT NULL,
  `enabled` tinyint unsigned NOT NULL DEFAULT 0,
  `display_order` int unsigned NOT NULL,
  `params` text,  
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

INSERT INTO `ohrm_first_accrual_option` 
(`name`, `implementation_class`, `enabled`, `display_order`, `params`) 
VALUES 	('full', 'FullyAccrueFirstAccrualOption', 1, 1, NULL),
        ('skip', 'SkipFirstAccrualOption', 1, 2, NULL),
        ('joined_date_based', 'JoinedDateBasedFirstAccrualOption', 1, 3, '{"M1":{"interval":"14D","before":"full","after":"skip","text":"Accrue full amount if joined before 15th of month"},"Y1":{"interval":"6M","before":"full","after":"skip","text":"Accrue full amount if joined in first half of year"}}'),
        ('prorate', 'ProrateFirstAccrualOption', 1, 4, NULL);

CREATE TABLE IF NOT EXISTS `ohrm_proration_method` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `display_name` varchar(100) DEFAULT NULL,
  `implementation_class` varchar(255) DEFAULT NULL,
  `enabled` tinyint unsigned NOT NULL DEFAULT 0,
  `display_order` int unsigned NOT NULL,
  `params` text,  
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_proration_method` 
(`name`, `display_name`, `implementation_class`, `enabled`, `display_order`, `params`) 
VALUES 	('default', 'Default', 'DefaultProrationMethod', 1, 1, '{"remaining_working":{"enable":true,"class":"RemainingWorkingDaysProrationVariable"},"total_working":{"enable":true,"class":"TotalWorkingDaysProrationVariable"},"remaining_calendar":{"enable":true,"class":"RemainingCalendarDaysProrationVariable"},"total_calendar":{"enable":true,"class":"TotalCalendarDaysProrationVariable"},"fixed":{"enable":true,"class":"FixedNumberProrationVariable"}}'),
        ('table', 'Table Based', 'TableBasedProrationMethod', 1, 2, NULL);

CREATE TABLE ohrm_emp_leave_accrual_proration (
    id BIGINT AUTO_INCREMENT, 
    leave_type_id int(10) unsigned NULL, 
    emp_number int(7) NOT NULL, 
    joined_date DATE NOT NULL, 
    prorate_on DATE, 
    accrued TINYINT(1) DEFAULT '0', 
    entitlement_id int unsigned, 
    PRIMARY KEY(id),
    UNIQUE KEY (`leave_type_id`, `emp_number`),
    FOREIGN KEY (`leave_type_id`) REFERENCES `ohrm_leave_type` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE,
    FOREIGN KEY (`entitlement_id`) REFERENCES `ohrm_leave_entitlement` (`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;


--
-- Constraints for table `ohrm_apply_restriction_filter_params`
--
ALTER TABLE `ohrm_apply_restriction_filter_params`
  ADD CONSTRAINT `ofoi` FOREIGN KEY (`filter_id`) REFERENCES `ohrm_apply_restriction_filter` (`id`);

INSERT INTO `ohrm_apply_restriction_filter` 
(`id`, `name`, `status`, `priority`, `reference_class_name`, `display_text`) 
VALUES 	('1', 'Dont Allow More Than Current', '1', '1', 'MoreThanCurrentRestrictionFilter', 'Don''t allow more than leave balance.'), 
        ('2', 'Dont Allow Half Day Applied Restriction Filter', '1', '2', 'HalfDayRestrictionFilter', 'Don''t allow half day leave.'),
        ('3', 'Dont Allow Specific Time Applied Restriction Filter', '1', '3', 'SpecificTimeRestrictionFilter', 'Don''t allow specific time leave.'),
        ('4', 'Confirm Restriction Filter', '1', '4', 'ConfirmRestrictionFilter', 'Don''t allow if employee answers NO to below question. Show the error message that comes afterwards.'),
        ('5', 'Dont allow Service Period less than X month Filter', '1', '5', 'ServicePeriodLessThanRestrictionFilter', 'Don''t allow if Service Period (from joined date) is less than'),
        ('6', 'Dont Allow Max Consecutive Day Exceed X days  Filter', '1', '7', 'MaxNumOfConsecutiveRestrictionFilter', 'Don''t allow if number of consecutive leave days exceed (Does not apply to employees with Flexible Work Hours (Weekly))'),
        ('7', 'Dont Allow Child found aged leass than X month Filter', '1', '8', 'EligibleChildRestrictionFilter', 'Don''t allow if no child found aged less than'),
        ('8', 'Dont allow Total Service Period less than X month Filter', '1', '6', 'TotalServicePeriodLessThanRestrictionFilter', 'Don''t allow if Total Service Period (from initial joined date: excluding termination periods) is less than');

INSERT INTO `ohrm_apply_restriction_filter_params` 
(`id`, `filter_id`, `widget`, `lable`) 
VALUES 	('1', '4', 'ohrmWidgetRestrictionFilterQuestionFormInput', 'Question :'),
        ('2', '4', 'ohrmWidgetRestrictionFilterErrorMessageFormInput', 'Error Message :'),
        ('3', '5', 'ohrmWidgetRestrictionFilterMonthsFormInput', NULL),
        ('4', '6', 'ohrmWidgetRestrictionFilterMaxNumOfConsecutiveFormInput', NULL),
        ('5', '7', 'ohrmWidgetRestrictionFilterMonthsFormInput', NULL),
        ('6', '8', 'ohrmWidgetRestrictionFilterMonthsFormInput', NULL),
        ('7', '6', 'ohrmWidgetRestrictionFilterMaxNumOfConsecutiveFormCheckbox', 'Exclude non-working days for consecutive leave');

alter table `ohrm_emp_leave_accrual`
add constraint foreign key (emp_number)
references `hs_hr_employee` (`emp_number`) on delete cascade;

alter table `ohrm_employee_leave_type_leave_period_history`
add constraint foreign key (emp_number)
references `hs_hr_employee` (`emp_number`) on delete cascade;

alter table `ohrm_employee_leave_type_leave_period_history`
add foreign key (leave_type_id)
        references ohrm_leave_type(id) on delete cascade;

 


ALTER TABLE `ohrm_holiday_location`
  ADD CONSTRAINT `ohrm_holiday_location_ibfk_1` FOREIGN KEY (`holiday_id`) REFERENCES `ohrm_holiday` (`id`) ON DELETE CASCADE;

ALTER TABLE `ohrm_holiday_location`
  ADD CONSTRAINT `ohrm_holiday_location_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `ohrm_location` (`id`) ON DELETE CASCADE;

SET @leave_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'leave');
-- Insert screens
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Leave Type List Advanced', @leave_module_id, 'leaveTypeListAdvanced'),
('Define Holiday Advanced', @leave_module_id, 'defineHolidayAdvanced'),
('View Holiday List Advanced', @leave_module_id, 'viewHolidayListAdvanced'),
('Assign Leave Advanced', @leave_module_id, 'assignLeaveAdvanced'),
('Leave List Advanced', @leave_module_id, 'viewLeaveListAdvanced'),
('Add Leave Entitlement Advanced',@leave_module_id,'addLeaveEntitlementAdvanced'),
('Edit Leave Entitlement Advanced',@leave_module_id,'editLeaveEntitlementAdvanced'),
('Leave Type Rule General',@leave_module_id,'defineLeaveTypeRule'),
('Leave Type Rule Who can apply',@leave_module_id,'defineLeaveTypeRuleWhoCanApply'),
('Leave Type Rule apply restriction',@leave_module_id,'defineLeaveTypeRuleApplyRestriction'),
('Leave Type Rule Accrue Rule',@leave_module_id,'defineLeaveTypeRuleAccrualRule'),
('Leave Type Rule Carry Forward',@leave_module_id,'defineLeaveTypeCarryForwardRule')
;

-- Give admin access to leave config screens
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 1 FROM ohrm_screen s, ohrm_user_role r WHERE 
    s.action_url in ('defineHolidayAdvanced', 
        'viewHolidayListAdvanced','addLeaveEntitlementAdvanced',
        'editLeaveEntitlementAdvanced')
    AND r.`name` = 'Admin';

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 0, 0, 0 FROM ohrm_screen s, ohrm_user_role r WHERE 
    s.action_url in ('leaveTypeListAdvanced', 'defineLeaveTypeRule','defineLeaveTypeRuleWhoCanApply','defineLeaveTypeRuleApplyRestriction',
        'defineLeaveTypeRuleAccrualRule','defineLeaveTypeCarryForwardRule')
    AND r.`name` = 'Admin';

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 1 FROM ohrm_screen s, ohrm_user_role r WHERE 
    s.action_url in ('leaveTypeListAdvanced', 'defineLeaveTypeRule','defineLeaveTypeRuleWhoCanApply','defineLeaveTypeRuleApplyRestriction',
        'defineLeaveTypeRuleAccrualRule','defineLeaveTypeCarryForwardRule')
    AND r.`name` = 'ConfigAdmin';

-- assign leave, leave list
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 0 FROM ohrm_screen s, ohrm_user_role r WHERE 
    s.action_url in ('assignLeaveAdvanced', 'viewLeaveListAdvanced','saveLeaveEntitlementsAdvanced')
    AND r.`name` in ('Admin', 'Supervisor');

-- data group screen permissions for screens which are routed 
-- viewLeaveList => viewLeaveListAdvanced
INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'viewLeaveList' and ns.action_url = 'viewLeaveListAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'viewHolidayList' and ns.action_url = 'viewHolidayListAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'defineHoliday' and ns.action_url = 'defineHolidayAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'leaveTypeList' and ns.action_url = 'leaveTypeListAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'viewLeaveBalanceReport' and ns.action_url = 'viewLeaveBalanceReportAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'addLeaveEntitlement' and ns.action_url = 'addLeaveEntitlementAdvanced';

INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'editLeaveEntitlement' and ns.action_url = 'editLeaveEntitlementAdvanced';

-- screens used to update leave rules (give access if user has read/update permissions for leave types)
INSERT INTO ohrm_data_group_screen(`data_group_id`, `screen_id`, `permission`)
SELECT dgs.data_group_id, ns.id, dgs.permission 
FROM ohrm_data_group_screen dgs LEFT JOIN ohrm_screen s on s.id = dgs.screen_id 
JOIN ohrm_screen ns WHERE s.action_url = 'defineLeaveType' and dgs.permission in (1, 3) and
ns.action_url in ('defineLeaveTypeRule', 'defineLeaveTypeRuleWhoCanApply', 
'defineLeaveTypeRuleApplyRestriction', 'defineLeaveTypeRuleAccrualRule', 'defineLeaveTypeCarryForwardRule');

UPDATE `ohrm_config` set `value` = 'AdvancedLeaveEntitlementConsumptionStrategy' WHERE 
`property` =  'leave.entitlement_consumption_algorithm';

-- HOD user role permission ----
SET @hod_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'HeadOfDepartment');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete)
SELECT r.id, s.id, 1, 1, 1, 0 FROM ohrm_screen s, ohrm_user_role r WHERE
    s.action_url IN ('assignLeaveAdvanced', 'viewLeaveListAdvanced','saveLeaveEntitlementsAdvanced')
    AND r.`name` IN ('HeadOfDepartment');


CREATE TABLE IF NOT EXISTS `ohrm_emp_leave_bfcf` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `leave_type_id` varchar(13) NOT NULL,
    `bf_id` int(7) NOT NULL,
    `cf_id` int(7) NOT NULL,
    `emp_number` int(7) NOT NULL,
    `cf_time` date NOT NULL,
    `cf` decimal(19,9) DEFAULT NULL,
    `bf` decimal(19,9) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `lbf_id` (`bf_id`),
    KEY `lcf_id` (`cf_id`),
    KEY `employee_id` (`emp_number`),
    KEY `leave_type_id` (`leave_type_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


ALTER TABLE `ohrm_emp_leave_bfcf`
  ADD CONSTRAINT `ohrm_emp_leave_bfcf_ibfk_1` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE;

UPDATE `ohrm_menu_item` SET `uisref`='vueleave.holidays' WHERE `menu_title`='Holidays';

DROP VIEW IF EXISTS  ohrm_stle_entitlements ;

CREATE VIEW ohrm_stle_entitlements AS
SELECT CONCAT('e_',GROUP_CONCAT(id SEPARATOR '_')) as id, emp_number, leave_type_id,
       SUM(length) as length, SUM(used) as used, from_date, to_date,
       SUM(pending) as pending,
       GROUP_CONCAT(pendingLeaveRequests) as pendingLeaveRequests,
       SUM(scheduled) as scheduled,
       GROUP_CONCAT(scheduledLeaveRequests) as scheduledLeaveRequests,
       SUM(taken) as taken,
       GROUP_CONCAT(takenLeaveRequests) as takenLeaveRequests
FROM (
         SELECT le.id as id, le.emp_number, le.leave_type_id, le.length, le.used, DATE(from_date) as from_date, DATE(to_date) as to_date,
                SUM(IF(l.status IN (SELECT s.status FROM ohrm_leave_status s WHERE s.name LIKE '%PENDING APPROVAL%'), lle.length, 0)) as pending,
                GROUP_CONCAT(DISTINCT IF(l.status IN (SELECT s.status FROM ohrm_leave_status s WHERE s.name LIKE '%PENDING APPROVAL%'), l.leave_request_id, NULL)) as pendingLeaveRequests,
                SUM(IF(l.status = 2, lle.length, 0)) as scheduled,
                GROUP_CONCAT(DISTINCT IF(l.status = 2, l.leave_request_id, NULL)) as scheduledLeaveRequests,
                SUM(IF(l.status = 3, lle.length, 0)) as taken,
                GROUP_CONCAT(DISTINCT IF(l.status = 3, l.leave_request_id, NULL)) as takenLeaveRequests
         FROM ohrm_leave_entitlement le
                  LEFT JOIN ohrm_leave_leave_entitlement lle ON (lle.entitlement_id = le.id)
                  LEFT JOIN ohrm_leave l ON (lle.leave_id = l.id)
                  LEFT JOIN ohrm_leave_type t ON (le.leave_type_id = t.id)
         WHERE le.deleted = 0 AND le.leave_type_id IN (SELECT id FROM ohrm_leave_type WHERE is_short_term = 1)
         GROUP BY le.id
) t
GROUP BY emp_number, leave_type_id, from_date, to_date;


--
-- Table structure for table `ohrm_leave_type_sort_order_option`
--

CREATE TABLE `ohrm_leave_type_sort_order_option` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) UNIQUE,
  `processor` VARCHAR(100),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- insert default sort order options to ohrm_leave_type_sort_order_option table
--

INSERT INTO ohrm_leave_type_sort_order_option (`name`, `processor`) VALUES
('Leave Type Balance High to Low', 'LeaveTypeBalanceDescSortingProcessor'),
('Leave Type Balance Low to High', 'LeaveTypeBalanceAscSortingProcessor'),
('Leave Type Alphabetical A to Z', 'LeaveTypeNameAscSortingProcessor'),
('Leave Type Alphabetical Z to A', 'LeaveTypeNameDescSortingProcessor'),
('Most Used Leave Type (Employee-wise)', 'MostUsedLeaveTypeEmployeeWiseSortingProcessor'),
('Leave Type Manual Ordering', 'LeaveTypeManualOrderSortingProcessor');

--
-- Table structure for table `ohrm_leave_type_sort_order`
--

CREATE TABLE `ohrm_leave_type_sort_order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `operational_country_id` INT(10) UNSIGNED NOT NULL,
  `sort_order` INT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_leave_type_sort_order_operational_country_id` FOREIGN KEY (`operational_country_id`) REFERENCES `ohrm_operational_country` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `fk_leave_type_sort_order_sort_order_option` FOREIGN KEY (`sort_order`) REFERENCES `ohrm_leave_type_sort_order_option` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Table structure for table `ohrm_leave_type_manual_sort_order`
--

CREATE TABLE ohrm_leave_type_manual_sort_order (
    leave_type_id INT(10) UNSIGNED NOT NULL,
    sort_order INT NOT NULL,
    PRIMARY KEY(leave_type_id),
    CONSTRAINT `fk_leave_type_manual_sort_order_leave_type` FOREIGN KEY (`leave_type_id`) REFERENCES `ohrm_leave_type` (`id`) ON UPDATE CASCADE
) ENGINE = INNODB;
