INSERT INTO `ohrm_advanced_report` (`name`, `unique_name`, `definition`) VALUES ('Leave Nominee Information Extraction For Data Warehouse', 'Leave_Nominee_Information_Extraction_For_Data_Warehouse',
'<?xml version="1.0"?>
<report>
    <settings></settings>
    <filter_fields>
        <input_field type="date_comparison" name="date" label="Date" allow-multiple="false"></input_field>
    </filter_fields>
    <sub_report type="sql" name="subReportForTotalLeave">
        <id_field>total_leave_requests</id_field>
        <query>FROM (
            SELECT DISTINCT ohrm_leave_request.* FROM ohrm_leave_request LEFT JOIN ohrm_leave ON ohrm_leave_request.id = ohrm_leave.leave_request_id WHERE $X{FROM_WIDGET,ohrm_leave_request.date_applied,date} AND ohrm_leave.status NOT IN (-1, 0, 4, 5)
            ) as leave_request
            LEFT JOIN ohrm_leave_type ON(leave_request.leave_type_id = ohrm_leave_type.id)
            LEFT JOIN ohrm_leave_request_employee_nominee ON(leave_request.id = ohrm_leave_request_employee_nominee.leave_request_id)</query>
        <display_groups>
            <display_group id="1" name="TotalLeaves" type="one" display="true" show_header="false">
                <group_header></group_header>
                <fields>
                    <field display="true">
                        <field_name>COUNT(DISTINCT leave_request.id)</field_name>
                        <field_alias>total_leave_requests</field_alias>
                        <display_name>Total Leave Requests</display_name>
                        <width>50</width>
                    </field>
                    <field display="true">
                        <field_name>COUNT(DISTINCT IF(ohrm_leave_type.employee_nominee_enabled = 1, leave_request.id, NULL))</field_name>
                        <field_alias>leave_requests_from_nominee_enabled_types</field_alias>
                        <display_name>Leave Requests From Nominee Enabled Types</display_name>
                        <width>50</width>
                    </field>
                    <field display="true">
                        <field_name>COUNT(DISTINCT IF(ohrm_leave_request_employee_nominee.state IS NOT NULL, leave_request.id, NULL))</field_name>
                        <field_alias>leave_requests_with_nominees</field_alias>
                        <display_name>Leave Requests With Nominees</display_name>
                        <width>50</width>
                    </field>
                </fields>
            </display_group>
        </display_groups>
    </sub_report>
    <page_limit>50</page_limit>
</report>');
