-- Workaround for issue in OS upgrader where My Leave Entitlements and Usage report is not enabled for ESS
UPDATE  ohrm_user_role_screen urs
SET urs.user_role_id = (SELECT r.id FROM ohrm_user_role r WHERE r.name = 'ESS') 
WHERE urs.user_role_id = 3 AND 
urs.screen_id = (select s.id from ohrm_screen s where s.action_url = 'viewMyLeaveBalanceReport');


INSERT INTO `hs_hr_config` (`key`, `value`) VALUES ('leave.enable_leave_type_leave_period', 'on');

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

alter table ohrm_leave_type add column leave_rules text default null;

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,
      `acc_days` decimal(4,2) 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;

DROP VIEW ohrm_adv_leave_country_based_employee_leave_types;

CREATE TABLE IF NOT EXISTS `ohrm_leave_type_leave_period`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leave_type_id` int(11) 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,
  `created_at` date NOT NULL,
   PRIMARY KEY (`id`),
   KEY `leave_type_id`(`leave_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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;

UPDATE `ohrm_apply_restriction_filter` SET 
display_text = replace(display_text, '<br /> ', '')
WHERE `reference_class_name` = 'MoreThanCurrentRestrictionFilter';

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;

-- Insert screens
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Add Leave Entitlement Advanced',4,'addLeaveEntitlementAdvanced'),
('Search Leave Entitlement Advanced',4,'viewLeaveEntitlementsAdvanced'),
('Search My Leave Entitlement Advanced',4,'viewMyLeaveEntitlementsAdvanced'),
('Leave Type Rule General',4,'defineLeaveTypeRule'),
('Leave Type Rule Who can apply',4,'defineLeaveTypeRuleWhoCanApply'),
('Leave Type Rule apply restriction',4,'defineLeaveTypeRuleApplyRestriction'),
('Leave Type Rule Accrue Rule',4,'defineLeaveTypeRuleAccrualRule'),
('Leave Type Rule Carry Forward',4,'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 ('viewLeaveEntitlementsAdvanced','defineLeaveTypeRule','defineLeaveTypeRule','defineLeaveTypeRuleWhoCanApply','defineLeaveTypeRuleApplyRestriction','defineLeaveTypeRuleAccrualRule','defineLeaveTypeCarryForwardRule')
    AND r.`name` = 'Admin';

-- 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 ('addLeaveEntitlementAdvanced','saveLeaveEntitlementsAdvanced')
    AND r.`name` in ('Admin', 'Supervisor');

-- Give supervisor access to leave entitlements
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 ('viewLeaveEntitlementsAdvanced')
    AND r.`name` = 'Supervisor';
-- Give ESS user access to my leave entitlement
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 ('viewMyLeaveEntitlementsAdvanced')
    AND r.`name` = 'ESS';


DELETE FROM ohrm_advanced_report;
INSERT INTO ohrm_advanced_report (id, name, definition) VALUES
(1, 'Leave Entitlements and Usage Report', '
<report>
    <settings>
        <csv>
            <include_group_header>1</include_group_header>
            <include_header>1</include_header>
        </csv>
    </settings>
<filter_fields>
	<input_field type="text" name="empNumber" label="Employee Number"></input_field>
	<input_field type="text" name="fromDate" label="From"></input_field>
        <input_field type="text" name="toDate" label="To"></input_field>
        <input_field type="text" name="asOfDate" label="AsOf"></input_field>
        <input_field type="text" name="eligible_leave_types" label="leave types"></input_field>
</filter_fields> 

<sub_report type="sql" name="mainTable">       
    <query>FROM ohrm_leave_type 
LEFT JOIN ohrm_operational_country o ON ohrm_leave_type.operational_country_id = o.id 
LEFT JOIN hs_hr_country c on c.cou_code = o.country_code
WHERE 
(($X{IN,ohrm_leave_type.id,eligible_leave_types}) || 
    ((SELECT count(le.id) FROM ohrm_leave_entitlement le WHERE le.leave_type_id = ohrm_leave_type.id 
        AND $X{=,le.emp_number,empNumber} AND le.deleted = 0) > 0) ||
    ((SELECT count(adj.id) FROM ohrm_leave_adjustment adj WHERE adj.leave_type_id = ohrm_leave_type.id 
        AND $X{=,adj.emp_number,empNumber} AND adj.deleted = 0) > 0) ||
    ((SELECT count(ll.id) FROM ohrm_leave ll WHERE ll.leave_type_id = ohrm_leave_type.id 
        AND $X{=,ll.emp_number,empNumber} AND ll.status NOT IN (-1, 0, 4, 5)) > 0)
) AND
((deleted = 0) OR (SELECT count(l.id) FROM ohrm_leave l WHERE l.status = 3 AND l.leave_type_id = ohrm_leave_type.id) > 0)
ORDER BY ohrm_leave_type.id
</query>
    <id_field>leaveTypeId</id_field>
    <display_groups>
        <display_group name="leavetype" type="one" display="true">
            <group_header></group_header>
            <fields>
                <field display="false">
                    <field_name>ohrm_leave_type.id</field_name>
                    <field_alias>leaveTypeId</field_alias>
                    <display_name>Leave Type ID</display_name>
                    <width>1</width>	
                </field>   
                <field display="false">
                    <field_name>ohrm_leave_type.exclude_in_reports_if_no_entitlement</field_name>
                    <field_alias>exclude_if_no_entitlement</field_alias>
                    <display_name>Exclude</display_name>
                    <width>1</width>	
                </field>  
                <field display="false">
                    <field_name>ohrm_leave_type.deleted</field_name>
                    <field_alias>leave_type_deleted</field_alias>
                    <display_name>Leave Type Deleted</display_name>
                    <width>1</width>	
                </field>
                <field display="false">
                    <field_name>c.cou_name</field_name>
                    <field_alias>country_name</field_alias>
                    <display_name>Operational Country Name</display_name>
                    <width>1</width>	
                </field> 
                <field display="true">
                    <field_name>ohrm_leave_type.name</field_name>
                    <field_alias>leaveType</field_alias>
                    <display_name>Leave Type</display_name>
                    <width>160</width>	
                </field>s                                                                                                     
            </fields>
        </display_group>
    </display_groups> 
</sub_report>
<sub_report type="sql" name="current_period">       
    <query>FROM ohrm_leave_type WHERE deleted = 0 ORDER BY ohrm_leave_type.id</query>
    <id_field>leaveTypeId</id_field>
    <display_groups>
        <display_group name="current" type="one" display="true">
            <group_header></group_header>
            <fields>
                <field display="false">
                    <field_name>ohrm_leave_type.id</field_name>
                    <field_alias>leaveTypeId</field_alias>
                    <display_name>Leave Type ID</display_name>
                    <width>1</width>	
                </field>   
                <field display="false">
                    <field_name>ohrm_leave_type.id</field_name>
                    <field_alias>fromDate</field_alias>
                    <display_name>From Date</display_name>
                    <width>1</width>	
                </field> 
                <field display="false">
                    <field_name>ohrm_leave_type.id</field_name>
                    <field_alias>toDate</field_alias>
                    <display_name>To Date</display_name>
                    <width>1</width>	
                </field> 
                <field display="true">
                    <field_name>ohrm_leave_type.name</field_name>
                    <field_alias>current</field_alias>
                    <display_name>Current Leave Period</display_name>
                    <width>160</width>	
                </field>                                                                                                     
            </fields>
        </display_group>
    </display_groups> 
</sub_report>
<sub_report type="sql" name="entitlementsTotal">
                    <query>

FROM (
SELECT ohrm_leave_entitlement.id as id, 
       ohrm_leave_entitlement.leave_type_id as leave_type_id,
       ohrm_leave_entitlement.no_of_days as no_of_days,
       sum(IF(ohrm_leave.status = 2, ohrm_leave_leave_entitlement.length_days, 0)) AS scheduled,
       sum(IF(ohrm_leave.status = 3, ohrm_leave_leave_entitlement.length_days, 0)) AS taken
       
FROM ohrm_leave_entitlement LEFT JOIN ohrm_leave_leave_entitlement ON
    ohrm_leave_entitlement.id = ohrm_leave_leave_entitlement.entitlement_id
    LEFT JOIN ohrm_leave ON ohrm_leave.id = ohrm_leave_leave_entitlement.leave_id AND 
    ( $X{&gt;,ohrm_leave.date,toDate} OR $X{&lt;,ohrm_leave.date,fromDate} )

WHERE ohrm_leave_entitlement.deleted=0 AND $X{=,ohrm_leave_entitlement.emp_number,empNumber} AND 
    $X{IN,ohrm_leave_entitlement.leave_type_id,leaveTypeId} AND
    (
      ( $X{&lt;=,ohrm_leave_entitlement.from_date,fromDate} AND $X{&gt;=,ohrm_leave_entitlement.to_date,fromDate} ) OR
      ( $X{&lt;=,ohrm_leave_entitlement.from_date,toDate} AND $X{&gt;=,ohrm_leave_entitlement.to_date,toDate} ) OR 
      ( $X{&gt;=,ohrm_leave_entitlement.from_date,fromDate} AND $X{&lt;=,ohrm_leave_entitlement.to_date,toDate} ) 
    )
    
GROUP BY ohrm_leave_entitlement.id
) AS A

GROUP BY A.leave_type_id
ORDER BY A.leave_type_id

</query>
    <id_field>leaveTypeId</id_field>
    <display_groups>
            <display_group name="g2" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>A.leave_type_id</field_name>
                        <field_alias>leaveTypeId</field_alias>
                        <display_name>Leave Type ID</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="true">
                        <field_name>sum(A.no_of_days)</field_name>
                        <field_alias>entitlement_total</field_alias>
                        <display_name>Leave Entitlements (Days)</display_name>
                        <width>120</width>
                        <align>right</align>
                        <link>leave/viewLeaveEntitlements?empNumber=$P{empNumber}&amp;fromDate=$P{fromDate}&amp;toDate=$P{toDate}&amp;leaveTypeId=$P{leaveTypeId}&amp;stddate=1</link>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
</sub_report>

<sub_report type="sql" name="adjustments">
<query>
FROM ohrm_leave_adjustment 

WHERE ohrm_leave_adjustment.deleted=0 AND $X{=,ohrm_leave_adjustment.emp_number,empNumber} AND 
    $X{IN,ohrm_leave_adjustment.leave_type_id,leaveTypeId} AND
    (
      ( $X{&lt;=,ohrm_leave_adjustment.from_date,fromDate} AND $X{&gt;=,ohrm_leave_adjustment.to_date,fromDate} ) OR
      ( $X{&lt;=,ohrm_leave_adjustment.from_date,toDate} AND $X{&gt;=,ohrm_leave_adjustment.to_date,toDate} ) OR 
      ( $X{&gt;=,ohrm_leave_adjustment.from_date,fromDate} AND $X{&lt;=,ohrm_leave_adjustment.to_date,toDate} ) 
    )
GROUP BY ohrm_leave_adjustment.leave_type_id
ORDER BY ohrm_leave_adjustment.leave_type_id
</query>
    <id_field>leaveTypeId</id_field>
    <display_groups>
            <display_group name="g9" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>ohrm_leave_adjustment.leave_type_id</field_name>
                        <field_alias>leaveTypeId</field_alias>
                        <display_name>Leave Type ID</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="false">
                        <field_name>sum(ohrm_leave_adjustment.no_of_days)</field_name>
                        <field_alias>adjustments</field_alias>
                        <display_name>Leave Adjustments (Days)</display_name>
                        <width>1</width>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
</sub_report>

<sub_report type="sql" name="pendingQuery">
<query>
FROM ohrm_leave_type LEFT JOIN 
ohrm_leave ON ohrm_leave_type.id = ohrm_leave.leave_type_id AND
$X{=,ohrm_leave.emp_number,empNumber} AND
ohrm_leave.status IN (SELECT s.status FROM ohrm_leave_status s WHERE s.name LIKE \'%PENDING APPROVAL%\') AND
$X{&gt;=,ohrm_leave.date,fromDate} AND $X{&lt;=,ohrm_leave.date,toDate}
WHERE
ohrm_leave_type.deleted = 0 AND
$X{IN,ohrm_leave_type.id,leaveTypeId}

GROUP BY ohrm_leave_type.id
ORDER BY ohrm_leave_type.id
</query>
    <id_field>leaveTypeId</id_field>
    <display_groups>
            <display_group name="g6" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>ohrm_leave_type.id</field_name>
                        <field_alias>leaveTypeId</field_alias>
                        <display_name>Leave Type ID</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="true">
                        <field_name>sum(length_days)</field_name>
                        <field_alias>pending</field_alias>
                        <display_name>Leave Pending Approval (Days)</display_name>
                        <width>120</width>
                        <align>right</align>
                        <link>leave/viewLeaveList?empNumber=$P{empNumber}&amp;fromDate=$P{fromDate}&amp;toDate=$P{toDate}&amp;leaveTypeId=$P{leaveTypeId}&amp;status=1&amp;stddate=1</link>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
    </sub_report>

<sub_report type="sql" name="scheduledQuery">
<query>
FROM ohrm_leave_type LEFT JOIN 
ohrm_leave ON ohrm_leave_type.id = ohrm_leave.leave_type_id AND
$X{=,ohrm_leave.emp_number,empNumber} AND
ohrm_leave.status = 2 AND
$X{&gt;=,ohrm_leave.date,fromDate} AND $X{&lt;=,ohrm_leave.date,toDate}
WHERE
ohrm_leave_type.deleted = 0 AND
$X{IN,ohrm_leave_type.id,leaveTypeId}

GROUP BY ohrm_leave_type.id
ORDER BY ohrm_leave_type.id
</query>
    <id_field>leaveTypeId</id_field>
    <display_groups>
            <display_group name="g5" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>ohrm_leave_type.id</field_name>
                        <field_alias>leaveTypeId</field_alias>
                        <display_name>Leave Type ID</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="true">
                        <field_name>sum(length_days)</field_name>
                        <field_alias>scheduled</field_alias>
                        <display_name>Leave Scheduled (Days)</display_name>
                        <width>120</width>
                        <align>right</align>
                        <link>leave/viewLeaveList?empNumber=$P{empNumber}&amp;fromDate=$P{fromDate}&amp;toDate=$P{toDate}&amp;leaveTypeId=$P{leaveTypeId}&amp;status=2&amp;stddate=1</link>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
    </sub_report>

<sub_report type="sql" name="takenQuery">
<query>
FROM ohrm_leave WHERE $X{=,emp_number,empNumber} AND
status = 3 AND
$X{IN,ohrm_leave.leave_type_id,leaveTypeId} AND
$X{&gt;=,ohrm_leave.date,fromDate} AND $X{&lt;=,ohrm_leave.date,toDate}
GROUP BY leave_type_id
ORDER BY ohrm_leave.leave_type_id
</query>
    <id_field>leaveTypeId</id_field>
    <display_groups>
            <display_group name="g4" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>ohrm_leave.leave_type_id</field_name>
                        <field_alias>leaveTypeId</field_alias>
                        <display_name>Leave Type ID</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="true">
                        <field_name>sum(length_days)</field_name>
                        <field_alias>taken</field_alias>
                        <display_name>Leave Taken (Days)</display_name>
                        <width>120</width>
                        <align>right</align>
                        <link>leave/viewLeaveList?empNumber=$P{empNumber}&amp;fromDate=$P{fromDate}&amp;toDate=$P{toDate}&amp;leaveTypeId=$P{leaveTypeId}&amp;status=3&amp;stddate=1</link>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
    </sub_report>

<sub_report type="sql" name="unused">       
    <query>FROM ohrm_leave_type WHERE deleted = 0 AND $X{IN,ohrm_leave_type.id,leaveTypeId} ORDER BY ohrm_leave_type.id</query>
    <id_field>leaveTypeId</id_field>
    <display_groups>
        <display_group name="unused" type="one" display="true">
            <group_header></group_header>
            <fields>
                <field display="false">
                    <field_name>ohrm_leave_type.id</field_name>
                    <field_alias>leaveTypeId</field_alias>
                    <display_name>Leave Type ID</display_name>
                    <width>1</width>	
                </field>   
                <field display="true">
                    <field_name>ohrm_leave_type.name</field_name>
                    <field_alias>unused</field_alias>
                    <display_name>Leave Balance (Days)</display_name>
                    <width>160</width>
                    <align>right</align>	
                </field>                                                                                                     
            </fields>
        </display_group>
    </display_groups> 
</sub_report>
<sub_report type="sql" name="timeline">       
    <query>FROM ohrm_leave_type WHERE deleted = 0 ORDER BY ohrm_leave_type.id</query>
    <id_field>leaveTypeId</id_field>
    <display_groups>
        <display_group name="timeline" type="one" display="true">
            <group_header></group_header>
            <fields>
                <field display="false">
                    <field_name>ohrm_leave_type.id</field_name>
                    <field_alias>leaveTypeId</field_alias>
                    <display_name>Leave Type ID</display_name>
                    <width>1</width>	
                </field>   
                <field display="true">
                    <field_name>ohrm_leave_type.name</field_name>
                    <field_alias>timeline</field_alias>
                    <display_name> </display_name>
                    <width>160</width>	
                    <link>leave/viewTimeline?empNumber=$P{empNumber}&amp;leaveTypeId=$P{leaveTypeId}</link>
                </field>                                                                                                     
            </fields>
        </display_group>
    </display_groups> 
</sub_report>

    <join>             
        <join_by sub_report="mainTable" id="leaveTypeId"></join_by>
        <join_by sub_report="current_period" id="leaveTypeId"></join_by>              
        <join_by sub_report="entitlementsTotal" id="leaveTypeId"></join_by>
        <join_by sub_report="adjustments" id="leaveTypeId"></join_by> 
        <join_by sub_report="pendingQuery" id="leaveTypeId"></join_by>  
        <join_by sub_report="scheduledQuery" id="leaveTypeId"></join_by>  
        <join_by sub_report="takenQuery" id="leaveTypeId"></join_by>  
        <join_by sub_report="unused" id="leaveTypeId"></join_by>  
        <join_by sub_report="timeline" id="leaveTypeId"></join_by>
    </join>
    <page_limit>100</page_limit>        
</report>'); 

INSERT INTO ohrm_advanced_report (id, name, definition) VALUES
(2, 'Leave Entitlements and Usage Report', '
<report>
    <settings>
        <csv>
            <include_group_header>1</include_group_header>
            <include_header>1</include_header>
        </csv>
    </settings>
<filter_fields>
	<input_field type="text" name="leaveType" label="Leave Type"></input_field>
	<input_field type="text" name="fromDate" label="From"></input_field>
        <input_field type="text" name="toDate" label="To"></input_field>
        <input_field type="text" name="asOfDate" label="AsOf"></input_field>
        <input_field type="text" name="emp_numbers" label="employees"></input_field>
        <input_field type="text" name="job_title" label="Job Title"></input_field>
        <input_field type="text" name="location" label="Location"></input_field>
        <input_field type="text" name="sub_unit" label="Sub Unit"></input_field>
        <input_field type="text" name="terminated" label="Terminated"></input_field>
        <input_field type="text" name="country_code" label="Country Code"></input_field>
</filter_fields> 

<sub_report type="sql" name="mainTable">       
    <query>FROM hs_hr_employee 
    LEFT JOIN hs_hr_emp_locations ON hs_hr_employee.emp_number = hs_hr_emp_locations.emp_number
    LEFT JOIN ohrm_location ON ohrm_location.id = hs_hr_emp_locations.location_id
    WHERE $X{IN,hs_hr_employee.emp_number,emp_numbers} 
    AND $X{=,hs_hr_employee.job_title_code,job_title}
    AND $X{IN,hs_hr_employee.work_station,sub_unit}
    AND $X{IN,hs_hr_emp_locations.location_id,location}
    AND $X{=,ohrm_location.country_code,country_code}
    AND $X{IS NULL,hs_hr_employee.termination_id,terminated}
    ORDER BY hs_hr_employee.emp_lastname</query>
    <id_field>empNumber</id_field>
    <display_groups>
        <display_group name="personalDetails" type="one" display="true">
            <group_header></group_header>
            <fields>
                <field display="false">
                    <field_name>hs_hr_employee.emp_number</field_name>
                    <field_alias>empNumber</field_alias>
                    <display_name>Employee Number</display_name>
                    <width>1</width>	
                </field>                
                <field display="false">
                    <field_name>hs_hr_employee.termination_id</field_name>
                    <field_alias>termination_id</field_alias>
                    <display_name>Termination ID</display_name>
                    <width>1</width>	
                </field>   
                <field display="true">
                    <field_name>CONCAT(hs_hr_employee.emp_firstname, \' \', hs_hr_employee.emp_lastname)</field_name>
                    <field_alias>employeeName</field_alias>
                    <display_name>Employee</display_name>
                    <width>150</width>
                </field>                                                                                               
            </fields>
        </display_group>
    </display_groups> 
</sub_report>
<sub_report type="sql" name="current_period">       
    <query>FROM hs_hr_employee ORDER BY hs_hr_employee.emp_number</query>
    <id_field>empNumber</id_field>
    <display_groups>
        <display_group name="current" type="one" display="true">
            <group_header></group_header>
            <fields>    
                <field display="false">
                    <field_name>hs_hr_employee.emp_number</field_name>
                    <field_alias>empNumber</field_alias>
                    <display_name>Employee Number</display_name>
                    <width>1</width>	
                </field>      
                <field display="false">
                    <field_name>hs_hr_employee.emp_number</field_name>
                    <field_alias>fromDate</field_alias>
                    <display_name>From Date</display_name>
                    <width>1</width>	
                </field> 
                <field display="false">
                    <field_name>hs_hr_employee.emp_number</field_name>
                    <field_alias>toDate</field_alias>
                    <display_name>To Date</display_name>
                    <width>1</width>	
                </field>           
                <field display="true">
                    <field_name>hs_hr_employee.emp_firstname</field_name>
                    <field_alias>current</field_alias>
                    <display_name>Current Leave Period</display_name>
                    <width>150</width>
                </field> 
                                                                                               
            </fields>
        </display_group>
    </display_groups> 
</sub_report>
<sub_report type="sql" name="entitlementsTotal">
                    <query>

FROM (
SELECT ohrm_leave_entitlement.id as id, 
       ohrm_leave_entitlement.emp_number as emp_number,
       ohrm_leave_entitlement.no_of_days as no_of_days,
       sum(IF(ohrm_leave.status = 2, ohrm_leave_leave_entitlement.length_days, 0)) AS scheduled,
       sum(IF(ohrm_leave.status = 3, ohrm_leave_leave_entitlement.length_days, 0)) AS taken
       
FROM ohrm_leave_entitlement LEFT JOIN ohrm_leave_leave_entitlement ON
    ohrm_leave_entitlement.id = ohrm_leave_leave_entitlement.entitlement_id
    LEFT JOIN ohrm_leave ON ohrm_leave.id = ohrm_leave_leave_entitlement.leave_id AND 
    ( $X{&gt;,ohrm_leave.date,toDate} OR $X{&lt;,ohrm_leave.date,fromDate} )

WHERE ohrm_leave_entitlement.deleted=0 AND $X{=,ohrm_leave_entitlement.leave_type_id,leaveType}
    AND $X{IN,ohrm_leave_entitlement.emp_number,empNumber} AND
    (
      ( $X{&lt;=,ohrm_leave_entitlement.from_date,fromDate} AND $X{&gt;=,ohrm_leave_entitlement.to_date,fromDate} ) OR
      ( $X{&lt;=,ohrm_leave_entitlement.from_date,toDate} AND $X{&gt;=,ohrm_leave_entitlement.to_date,toDate} ) OR 
      ( $X{&gt;=,ohrm_leave_entitlement.from_date,fromDate} AND $X{&lt;=,ohrm_leave_entitlement.to_date,toDate} ) 
    )
    
GROUP BY ohrm_leave_entitlement.id
) AS A

GROUP BY A.emp_number
ORDER BY A.emp_number

</query>
    <id_field>empNumber</id_field>
    <display_groups>
            <display_group name="g2" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>A.emp_number</field_name>
                        <field_alias>empNumber</field_alias>
                        <display_name>Emp Number</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="true">
                        <field_name>sum(A.no_of_days)</field_name>
                        <field_alias>entitlement_total</field_alias>
                        <display_name>Leave Entitlements (Days)</display_name>
                        <width>120</width>
                        <align>right</align>
                        <link>leave/viewLeaveEntitlements?empNumber=$P{empNumber}&amp;fromDate=$P{fromDate}&amp;toDate=$P{toDate}&amp;leaveTypeId=$P{leaveTypeId}&amp;stddate=1</link>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
</sub_report>

<sub_report type="sql" name="adjustments">
    <query>
FROM ohrm_leave_adjustment

WHERE ohrm_leave_adjustment.deleted=0 AND $X{=,ohrm_leave_adjustment.leave_type_id,leaveType}
    AND $X{IN,ohrm_leave_adjustment.emp_number,empNumber} AND
    (
      ( $X{&lt;=,ohrm_leave_adjustment.from_date,fromDate} AND $X{&gt;=,ohrm_leave_adjustment.to_date,fromDate} ) OR
      ( $X{&lt;=,ohrm_leave_adjustment.from_date,toDate} AND $X{&gt;=,ohrm_leave_adjustment.to_date,toDate} ) OR 
      ( $X{&gt;=,ohrm_leave_adjustment.from_date,fromDate} AND $X{&lt;=,ohrm_leave_adjustment.to_date,toDate} ) 
    )
GROUP BY emp_number
ORDER BY ohrm_leave_adjustment.emp_number

</query>
    <id_field>empNumber</id_field>
    <display_groups>
            <display_group name="g9" type="one" display="false">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>ohrm_leave_adjustment.emp_number</field_name>
                        <field_alias>empNumber</field_alias>
                        <display_name>Emp Number</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="false">
                        <field_name>sum(ohrm_leave_adjustment.no_of_days)</field_name>
                        <field_alias>adjustments</field_alias>
                        <display_name>Leave Adjustments (Days)</display_name>
                        <width>1</width>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
</sub_report>

<sub_report type="sql" name="pendingQuery">
<query>
FROM ohrm_leave WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
status = 1 AND
$X{IN,ohrm_leave.emp_number,empNumber} AND
$X{&gt;=,ohrm_leave.date,fromDate} AND $X{&lt;=,ohrm_leave.date,toDate}
GROUP BY emp_number
ORDER BY ohrm_leave.emp_number
</query>
    <id_field>empNumber</id_field>
    <display_groups>
            <display_group name="g6" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>ohrm_leave.emp_number</field_name>
                        <field_alias>empNumber</field_alias>
                        <display_name>Emp Number</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="true">
                        <field_name>sum(length_days)</field_name>
                        <field_alias>pending</field_alias>
                        <display_name>Leave Pending Approval (Days)</display_name>
                        <width>121</width>
                        <align>right</align>
                        <link>leave/viewLeaveList?empNumber=$P{empNumber}&amp;fromDate=$P{fromDate}&amp;toDate=$P{toDate}&amp;leaveTypeId=$P{leaveTypeId}&amp;status=1&amp;stddate=1</link>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
</sub_report>


<sub_report type="sql" name="scheduledQuery">
<query>
FROM ohrm_leave WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
status = 2 AND
$X{IN,ohrm_leave.emp_number,empNumber} AND
$X{&gt;=,ohrm_leave.date,fromDate} AND $X{&lt;=,ohrm_leave.date,toDate}
GROUP BY emp_number
ORDER BY ohrm_leave.emp_number
</query>
    <id_field>empNumber</id_field>
    <display_groups>
            <display_group name="g5" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>ohrm_leave.emp_number</field_name>
                        <field_alias>empNumber</field_alias>
                        <display_name>Emp Number</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="true">
                        <field_name>sum(length_days)</field_name>
                        <field_alias>scheduled</field_alias>
                        <display_name>Leave Scheduled (Days)</display_name>
                        <width>121</width>
                        <align>right</align>
                        <link>leave/viewLeaveList?empNumber=$P{empNumber}&amp;fromDate=$P{fromDate}&amp;toDate=$P{toDate}&amp;leaveTypeId=$P{leaveTypeId}&amp;status=2&amp;stddate=1</link>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
</sub_report>

<sub_report type="sql" name="takenQuery">
<query>
FROM ohrm_leave WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
status = 3 AND
$X{IN,ohrm_leave.emp_number,empNumber} AND
$X{&gt;=,ohrm_leave.date,fromDate} AND $X{&lt;=,ohrm_leave.date,toDate}
GROUP BY emp_number
ORDER BY ohrm_leave.emp_number
</query>
    <id_field>empNumber</id_field>
    <display_groups>
            <display_group name="g4" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>ohrm_leave.emp_number</field_name>
                        <field_alias>empNumber</field_alias>
                        <display_name>Emp Number</display_name>
                        <width>1</width>
                    </field>                                
                    <field display="true">
                        <field_name>sum(length_days)</field_name>
                        <field_alias>taken</field_alias>
                        <display_name>Leave Taken (Days)</display_name>
                        <width>120</width>
                        <align>right</align>
                        <link>leave/viewLeaveList?empNumber=$P{empNumber}&amp;fromDate=$P{fromDate}&amp;toDate=$P{toDate}&amp;leaveTypeId=$P{leaveTypeId}&amp;status=3&amp;stddate=1</link>
                    </field>                                
                </fields>
            </display_group>
    </display_groups>
</sub_report>
<sub_report type="sql" name="unused">       
    <query>FROM hs_hr_employee WHERE $X{IN,hs_hr_employee.emp_number,empNumber} ORDER BY hs_hr_employee.emp_number</query>
    <id_field>empNumber</id_field>
    <display_groups>
        <display_group name="unused" type="one" display="true">
            <group_header></group_header>
            <fields>    
                <field display="false">
                    <field_name>hs_hr_employee.emp_number</field_name>
                    <field_alias>empNumber</field_alias>
                    <display_name>Employee Number</display_name>
                    <width>1</width>	
                </field>                
                <field display="true">
                    <field_name>hs_hr_employee.emp_firstname</field_name>
                    <field_alias>unused</field_alias>
                    <display_name>Leave Balance (Days)</display_name>
                    <width>150</width>
                    <align>right</align>
                </field> 
                                                                                               
            </fields>
        </display_group>
    </display_groups> 
</sub_report>
<sub_report type="sql" name="timeline">       
    <query>FROM hs_hr_employee ORDER BY hs_hr_employee.emp_number</query>
    <id_field>empNumber</id_field>
    <display_groups>
        <display_group name="timeline" type="one" display="true">
            <group_header></group_header>
            <fields>    
                <field display="false">
                    <field_name>hs_hr_employee.emp_number</field_name>
                    <field_alias>empNumber</field_alias>
                    <display_name>Employee Number</display_name>
                    <width>1</width>	
                </field>                
                <field display="true">
                    <field_name>hs_hr_employee.emp_firstname</field_name>
                    <field_alias>timeline</field_alias>
                    <display_name> </display_name>
                    <width>150</width>
                    <link>leave/viewTimeline?empNumber=$P{empNumber}&amp;leaveTypeId=$P{leaveTypeId}</link>
                </field> 
                                                                                               
            </fields>
        </display_group>
    </display_groups> 
</sub_report>
    <join>             
        <join_by sub_report="mainTable" id="empNumber"></join_by>
        <join_by sub_report="current_period" id="empNumber"></join_by>            
        <join_by sub_report="entitlementsTotal" id="empNumber"></join_by>
        <join_by sub_report="adjustments" id="empNumber"></join_by> 
        <join_by sub_report="pendingQuery" id="empNumber"></join_by>
        <join_by sub_report="scheduledQuery" id="empNumber"></join_by>
        <join_by sub_report="takenQuery" id="empNumber"></join_by> 
        <join_by sub_report="unused" id="empNumber"></join_by> 
        <join_by sub_report="timeline" id="empNumber"></join_by> 
    </join>
    <page_limit>20</page_limit>       
</report>'); 


UPDATE `hs_hr_config` set `value` = 'AdvancedLeaveEntitlementConsumptionStrategy' WHERE 
`key` =  'leave.entitlement_consumption_algorithm';

