<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>