-- Reports
DELETE FROM ohrm_advanced_report WHERE unique_name = 'Leave_Entitlements_and_Usage_Report_By_Employee'
or unique_name = 'Leave_Entitlements_and_Usage_Report';

INSERT INTO ohrm_advanced_report (name, unique_name, definition) VALUES
('Leave Entitlements and Usage Report', 'Leave_Entitlements_and_Usage_Report_By_Employee', '<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>
        <input_field type="text" name="period_offset" label="Leave Period Offset"></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>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,
            sum(IF(validOnAsOfDateForLeavePeriod(ohrm_leave_entitlement.from_date, ohrm_leave_entitlement.to_date, $X{VAR,empNumber}, ohrm_leave_entitlement.leave_type_id, $X{VAR,period_offset}), ohrm_leave_entitlement.no_of_days, ohrm_leave_entitlement.days_used)) AS available

            FROM ohrm_leave_entitlement
            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
            overlapsWithLeavePeriod(ohrm_leave_entitlement.from_date, ohrm_leave_entitlement.to_date, $X{VAR,empNumber}, ohrm_leave_entitlement.leave_type_id, $X{VAR,period_offset})


            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="false">
                        <field_name>count(id)</field_name>
                        <field_alias>entitlement_count</field_alias>
                        <display_name>Entitlement Count</display_name>
                        <width>1</width>
                    </field>
                    <field display="true">
                        <field_name>sum(A.available)</field_name>
                        <field_alias>entitlement_total</field_alias>
                        <display_name>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
            overlapsWithLeavePeriod(ohrm_leave_adjustment.from_date, ohrm_leave_adjustment.to_date, $X{VAR,empNumber}, ohrm_leave_adjustment.leave_type_id, $X{VAR,period_offset})

            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>
                    <field display="false">
                        <field_name>count(ohrm_leave_adjustment.id)</field_name>
                        <field_alias>adjustment_count</field_alias>
                        <display_name>Adjustment Count</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
            withinLeavePeriod(ohrm_leave.date, $X{VAR,empNumber}, ohrm_leave.leave_type_id, $X{VAR,period_offset})
            LEFT JOIN ohrm_leave_leave_entitlement ON ohrm_leave_leave_entitlement.leave_id = ohrm_leave.id

            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(IFNULL(ohrm_leave_leave_entitlement.length_days,0))</field_name>
                        <field_alias>pending</field_alias>
                        <display_name>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&amp;hide_search_form=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
            withinLeavePeriod(ohrm_leave.date, $X{VAR,empNumber}, ohrm_leave.leave_type_id, $X{VAR,period_offset})
            LEFT JOIN ohrm_leave_leave_entitlement ON ohrm_leave_leave_entitlement.leave_id = ohrm_leave.id

            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(IFNULL(ohrm_leave_leave_entitlement.length_days,0))</field_name>
                        <field_alias>scheduled</field_alias>
                        <display_name>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&amp;hide_search_form=1</link>
                    </field>
                </fields>
            </display_group>
        </display_groups>
    </sub_report>

    <sub_report type="sql" name="takenQuery">
        <query>
            FROM ohrm_leave

            LEFT JOIN ohrm_leave_leave_entitlement ON ohrm_leave_leave_entitlement.leave_id = ohrm_leave.id

            WHERE $X{=,emp_number,empNumber} AND
            status = 3 AND
            $X{IN,ohrm_leave.leave_type_id,leaveTypeId} AND
            withinLeavePeriod(ohrm_leave.date, $X{VAR,empNumber}, ohrm_leave.leave_type_id, $X{VAR,period_offset})

            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(IFNULL(ohrm_leave_leave_entitlement.length_days,0))</field_name>
                        <field_alias>taken</field_alias>
                        <display_name>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&amp;hide_search_form=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>Available Balance (Days)</display_name>
                        <width>160</width>
                        <align>right</align>
                    </field>
                </fields>
            </display_group>
        </display_groups>
    </sub_report>
    <sub_report type="sql" name="overdrawn">
        <query>
            FROM (
            SELECT

            ohrm_leave.id as leave_id,
            ohrm_leave.leave_request_id as leave_request_id,
            ohrm_leave.leave_type_id as leave_type_id,
            ohrm_leave.length_days as leave_length_days,
            sum(IFNULL(ohrm_leave_leave_entitlement.length_days,0)) AS entitlement_length_days

            FROM ohrm_leave

            LEFT JOIN ohrm_leave_leave_entitlement ON ohrm_leave_leave_entitlement.leave_id = ohrm_leave.id
            WHERE $X{=,emp_number,empNumber} AND
            $X{IN,ohrm_leave.leave_type_id,leaveTypeId} AND
            status NOT IN(-1, 0, 4, 5) AND
            withinLeavePeriod(ohrm_leave.date, $X{VAR,empNumber}, ohrm_leave.leave_type_id, $X{VAR,period_offset})

            GROUP BY ohrm_leave.id

            ) AS OD

            GROUP BY leave_type_id
            ORDER BY leave_type_id
        </query>
        <id_field>leaveTypeId</id_field>
        <display_groups>
            <display_group name="g7" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>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>GROUP_CONCAT(DISTINCT IF(leave_length_days > IFNULL(entitlement_length_days,0),leave_request_id, NULL))</field_name>
                        <field_alias>overdrawn_leave_requests</field_alias>
                        <display_name>Overdrawn Leave Requests</display_name>
                        <width>1</width>
                    </field>
                    <field display="true">
                        <field_name>sum(leave_length_days) - sum(IFNULL(entitlement_length_days,0))</field_name>
                        <field_alias>overdrawn</field_alias>
                        <display_name>Total Overdrawn (Days)</display_name>
                        <description>Leave without entitlements</description>
                        <width>120</width>
                        <align>right</align>
                        <link>leave/viewLeaveList?hide_search_form=1&amp;leave_request_ids=$P{overdrawn_leave_requests}</link>
                    </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="overdrawn" id="leaveTypeId"></join_by>
        <join_by sub_report="timeline" id="leaveTypeId"></join_by>
    </join>
    <page_limit>100</page_limit>
</report>');

DELETE FROM ohrm_advanced_report WHERE unique_name = 'Leave_Entitlements_and_Usage_Report_By_LeaveType'
or unique_name = 'Leave_Entitlements_and_Usage_Report_1';

INSERT INTO ohrm_advanced_report (name, unique_name, definition) VALUES
('Leave Entitlements and Usage Report', 'Leave_Entitlements_and_Usage_Report_By_LeaveType', '<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>
        <input_field type="text" name="period_offset" label="Leave Period Offset"></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
            LEFT JOIN ohrm_subunit ON ohrm_subunit.id = hs_hr_employee.work_station
            LEFT JOIN ohrm_job_title ON ohrm_job_title.id = hs_hr_employee.job_title_code
            LEFT JOIN ohrm_leave_type ON $X{=,ohrm_leave_type.id,leaveType}
            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="false">
                        <field_name>ohrm_leave_type.name</field_name>
                        <field_alias>leaveTypeName</field_alias>
                        <display_name>Leave Type</display_name>
                        <width>1</width>
                    </field>
                    <field display="false">
                        <field_name>hs_hr_employee.employee_id</field_name>
                        <field_alias>employeeId</field_alias>
                        <display_name>ID</display_name>
                        <width>1</width>
                    </field>
                    <field display="false">
                        <field_name>ohrm_job_title.job_title</field_name>
                        <field_alias>jobTitle</field_alias>
                        <display_name>Job Title</display_name>
                        <width>1</width>
                    </field>
                    <field display="false">
                        <field_name>ohrm_subunit.name</field_name>
                        <field_alias>subunit</field_alias>
                        <display_name>Sub Unit</display_name>
                        <width>1</width>
                    </field>
                    <field display="false">
                        <field_name>ohrm_location.name</field_name>
                        <field_alias>location</field_alias>
                        <display_name>Location</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>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,
            sum(IF(validOnAsOfDateForLeavePeriod(ohrm_leave_entitlement.from_date, ohrm_leave_entitlement.to_date, ohrm_leave_entitlement.emp_number, $X{VAR,leaveType}, $X{VAR,period_offset}), ohrm_leave_entitlement.no_of_days, ohrm_leave_entitlement.days_used)) AS available
            FROM ohrm_leave_entitlement
            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
            overlapsWithLeavePeriod(ohrm_leave_entitlement.from_date, ohrm_leave_entitlement.to_date, ohrm_leave_entitlement.emp_number, $X{VAR,leaveType}, $X{VAR,period_offset})

            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="false">
                        <field_name>count(id)</field_name>
                        <field_alias>entitlement_count</field_alias>
                        <display_name>Entitlement Count</display_name>
                        <width>1</width>
                    </field>
                    <field display="true">
                        <field_name>sum(A.available)</field_name>
                        <field_alias>entitlement_total</field_alias>
                        <display_name>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
            overlapsWithLeavePeriod(ohrm_leave_adjustment.from_date, ohrm_leave_adjustment.to_date, ohrm_leave_adjustment.emp_number, $X{VAR,leaveType}, $X{VAR,period_offset})

            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>
                    <field display="false">
                        <field_name>count(ohrm_leave_adjustment.id)</field_name>
                        <field_alias>adjustment_count</field_alias>
                        <display_name>Adjustment Count</display_name>
                        <width>1</width>
                    </field>
                </fields>
            </display_group>
        </display_groups>
    </sub_report>

    <sub_report type="sql" name="pendingQuery">
        <query>
            FROM ohrm_leave
            LEFT JOIN ohrm_leave_leave_entitlement ON ohrm_leave_leave_entitlement.leave_id = ohrm_leave.id
            WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
            status IN (SELECT s.status FROM ohrm_leave_status s WHERE s.name LIKE ''%PENDING APPROVAL%'') AND
            $X{IN,ohrm_leave.emp_number,empNumber} AND
            withinLeavePeriod(ohrm_leave.date, ohrm_leave.emp_number, $X{VAR,leaveType}, $X{VAR,period_offset})

            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(IFNULL(ohrm_leave_leave_entitlement.length_days,0))</field_name>
                        <field_alias>pending</field_alias>
                        <display_name>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&amp;hide_search_form=1</link>
                    </field>
                </fields>
            </display_group>
        </display_groups>
    </sub_report>


    <sub_report type="sql" name="scheduledQuery">
        <query>
            FROM ohrm_leave
            LEFT JOIN ohrm_leave_leave_entitlement ON ohrm_leave_leave_entitlement.leave_id = ohrm_leave.id
            WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
            status = 2 AND
            $X{IN,ohrm_leave.emp_number,empNumber} AND
            withinLeavePeriod(ohrm_leave.date, ohrm_leave.emp_number, $X{VAR,leaveType}, $X{VAR,period_offset})

            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(IFNULL(ohrm_leave_leave_entitlement.length_days,0))</field_name>
                        <field_alias>scheduled</field_alias>
                        <display_name>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&amp;hide_search_form=1</link>
                    </field>
                </fields>
            </display_group>
        </display_groups>
    </sub_report>

    <sub_report type="sql" name="takenQuery">
        <query>
            FROM ohrm_leave
            LEFT JOIN ohrm_leave_leave_entitlement ON ohrm_leave_leave_entitlement.leave_id = ohrm_leave.id
            WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
            status = 3 AND
            $X{IN,ohrm_leave.emp_number,empNumber} AND
            withinLeavePeriod(ohrm_leave.date, ohrm_leave.emp_number, $X{VAR,leaveType}, $X{VAR,period_offset})

            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(IFNULL(ohrm_leave_leave_entitlement.length_days,0))</field_name>
                        <field_alias>taken</field_alias>
                        <display_name>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&amp;hide_search_form=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>Available Balance (Days)</display_name>
                        <width>150</width>
                        <align>right</align>
                    </field>

                </fields>
            </display_group>
        </display_groups>
    </sub_report>
    <sub_report type="sql" name="overdrawn">
        <query>
            FROM (
            SELECT

            ohrm_leave.emp_number as emp_number,
            ohrm_leave.id as leave_id,
            ohrm_leave.leave_request_id as leave_request_id,
            ohrm_leave.leave_type_id as leave_type_id,
            ohrm_leave.length_days as leave_length_days,
            sum(IFNULL(ohrm_leave_leave_entitlement.length_days,0)) AS entitlement_length_days

            FROM ohrm_leave
            LEFT JOIN ohrm_leave_leave_entitlement ON ohrm_leave_leave_entitlement.leave_id = ohrm_leave.id
            WHERE $X{=,ohrm_leave.leave_type_id,leaveType} AND
            $X{IN,ohrm_leave.emp_number,empNumber} AND
            status NOT IN(-1, 0, 4, 5) AND
            withinLeavePeriod(ohrm_leave.date, ohrm_leave.emp_number, $X{VAR,leaveType}, $X{VAR,period_offset})

            GROUP BY ohrm_leave.id

            ) AS OD

            GROUP BY emp_number
            ORDER BY emp_number
        </query>
        <id_field>empNumber</id_field>
        <display_groups>
            <display_group name="g7" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="false">
                        <field_name>emp_number</field_name>
                        <field_alias>empNumber</field_alias>
                        <display_name>Emp Number</display_name>
                        <width>1</width>
                    </field>
                    <field display="false">
                        <field_name>GROUP_CONCAT(DISTINCT IF(leave_length_days > IFNULL(entitlement_length_days,0),leave_request_id, NULL))</field_name>
                        <field_alias>overdrawn_leave_requests</field_alias>
                        <display_name>Overdrawn Leave Requests</display_name>
                        <width>1</width>
                    </field>
                    <field display="true">
                        <field_name>sum(leave_length_days) - sum(IFNULL(entitlement_length_days,0))</field_name>
                        <field_alias>overdrawn</field_alias>
                        <display_name>Total Overdrawn (Days)</display_name>
                        <description>Leave without entitlements</description>
                        <width>120</width>
                        <align>right</align>
                        <link>leave/viewLeaveList?hide_search_form=1&amp;leave_request_ids=$P{overdrawn_leave_requests}</link>
                    </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="overdrawn" id="empNumber"></join_by>
        <join_by sub_report="timeline" id="empNumber"></join_by>
    </join>
    <page_limit>20</page_limit>
</report>');