INSERT INTO `ohrm_advanced_report` (`id`, `name`, `definition`) VALUES (3, 'Employee Leave 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,
       get_leave_period_start_date(ohrm_leave_entitlement.emp_number, ohrm_leave_entitlement.leave_type_id, ohrm_leave_entitlement.from_date) AS fromDate,
       ohrm_leave_entitlement.to_date as toDate,
       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

WHERE ohrm_leave_entitlement.deleted=0 AND $X{=,ohrm_leave_entitlement.emp_number,empNumber} AND 
    $X{IN,ohrm_leave_entitlement.leave_type_id,leaveTypeId}

    
GROUP BY ohrm_leave_entitlement.id
) AS A

GROUP BY A.leave_type_id, A.fromDate
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="false">
                        <field_name>A.fromDate</field_name>
                        <field_alias>fromDate</field_alias>
                        <display_name>From Date</display_name>
                        <width>1</width>	
                    </field> 
                    <field display="false">
                        <field_name>A.toDate</field_name>
                        <field_alias>toDate</field_alias>
                        <display_name>To Date</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 (
     SELECT ohrm_leave_adjustment.leave_type_id as leave_type_id,
     ohrm_leave_adjustment.no_of_days as no_of_days,
     get_leave_period_start_date(ohrm_leave_adjustment.emp_number, ohrm_leave_adjustment.leave_type_id, ohrm_leave_adjustment.from_date) AS fromDate,
     get_leave_period_end_date(ohrm_leave_adjustment.emp_number, ohrm_leave_adjustment.leave_type_id, ohrm_leave_adjustment.from_date) AS toDate

     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}

) AS A

GROUP BY A.leave_type_id, A.fromDate
ORDER BY A.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>A.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>A.fromDate</field_name>
                        <field_alias>fromDate</field_alias>
                        <display_name>From Date</display_name>
                        <width>1</width>	
                    </field> 
                    <field display="false">
                        <field_name>A.toDate</field_name>
                        <field_alias>toDate</field_alias>
                        <display_name>To Date</display_name>
                        <width>1</width>	
                    </field>                         
                    <field display="false">
                        <field_name>sum(A.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 (
     SELECT ohrm_leave_type.id as leave_type_id,
     ohrm_leave.length_days as length_days,
     ohrm_leave.status as status,
     ohrm_leave.emp_number as emp_number,
     get_leave_period_start_date(ohrm_leave.emp_number, ohrm_leave.leave_type_id, ohrm_leave.date) AS fromDate,
     get_leave_period_end_date(ohrm_leave.emp_number, ohrm_leave.leave_type_id, ohrm_leave.date) AS toDate

     FROM ohrm_leave_type RIGHT JOIN 
     ohrm_leave ON ohrm_leave_type.id = ohrm_leave.leave_type_id

     WHERE ohrm_leave_type.deleted = 0 AND $X{=,ohrm_leave.emp_number,empNumber} AND 
     $X{IN,ohrm_leave_type.id,leaveTypeId} AND 
     ohrm_leave.status IN (SELECT s.status FROM ohrm_leave_status s WHERE s.name LIKE "%PENDING APPROVAL%")

) AS A

GROUP BY A.leave_type_id, A.fromDate
ORDER BY A.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>A.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>A.fromDate</field_name>
                        <field_alias>fromDate</field_alias>
                        <display_name>From Date</display_name>
                        <width>1</width>	
                    </field> 
                    <field display="false">
                        <field_name>A.toDate</field_name>
                        <field_alias>toDate</field_alias>
                        <display_name>To Date</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 (
     SELECT ohrm_leave_type.id as leave_type_id,
     ohrm_leave.length_days as length_days,
     ohrm_leave.status as status,
     ohrm_leave.emp_number as emp_number,
     get_leave_period_start_date(ohrm_leave.emp_number, ohrm_leave.leave_type_id, ohrm_leave.date) AS fromDate,
     get_leave_period_end_date(ohrm_leave.emp_number, ohrm_leave.leave_type_id, ohrm_leave.date) AS toDate

     FROM ohrm_leave_type RIGHT JOIN 
     ohrm_leave ON ohrm_leave_type.id = ohrm_leave.leave_type_id

     WHERE ohrm_leave_type.deleted = 0 AND $X{IN,ohrm_leave_type.id,leaveTypeId} AND 
     $X{=,ohrm_leave.emp_number,empNumber} AND status = 2
) AS A

GROUP BY A.leave_type_id, A.fromDate
ORDER BY A.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>A.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>A.fromDate</field_name>
                        <field_alias>fromDate</field_alias>
                        <display_name>From Date</display_name>
                        <width>1</width>	
                    </field> 
                    <field display="false">
                        <field_name>A.toDate</field_name>
                        <field_alias>toDate</field_alias>
                        <display_name>To Date</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 (
    SELECT ohrm_leave.date as date,
    ohrm_leave.length_hours as length_hours,
    ohrm_leave.length_days as length_days,
    ohrm_leave.status as status,
    ohrm_leave.leave_type_id as leave_type_id,
    ohrm_leave.emp_number as emp_number,
    get_leave_period_start_date(ohrm_leave.emp_number, ohrm_leave.leave_type_id, ohrm_leave.date) AS fromDate,
    get_leave_period_end_date(ohrm_leave.emp_number, ohrm_leave.leave_type_id, ohrm_leave.date) AS toDate
    
    FROM ohrm_leave WHERE $X{=,emp_number,empNumber} AND
    status = 3 AND $X{IN,ohrm_leave.leave_type_id,leaveTypeId}
) AS A

GROUP BY A.leave_type_id, A.fromDate
ORDER BY A.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>A.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>A.fromDate</field_name>
                        <field_alias>fromDate</field_alias>
                        <display_name>From Date</display_name>
                        <width>1</width>	
                    </field> 
                    <field display="false">
                        <field_name>A.toDate</field_name>
                        <field_alias>toDate</field_alias>
                        <display_name>To Date</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>');
