SET @max_report_id = (SELECT MAX(`id`) FROM ohrm_advanced_report);
INSERT INTO ohrm_advanced_report (id, name, definition) VALUES
(@max_report_id + 1, "Leave Percentage Report", '
<report>
    <settings>
        <csv>
            <include_group_header>1</include_group_header>
            <include_header>1</include_header>
        </csv>
    </settings>
<filter_fields>
    <input_field type="leave_type" name="leave_types" label="Leave Type" required="false" allow-multiple="true">
        <options>
                <choices type="sql">
                        <query>SELECT lt.id, lt.name AS name, c.cou_name as country FROM ohrm_leave_type lt LEFT JOIN ohrm_operational_country oc ON oc.id = lt.operational_country_id LEFT JOIN hs_hr_country c ON c.cou_code = oc.country_code WHERE lt.deleted =0 ORDER BY lt.name</query>
                        <value>id</value>
                        <label>name</label>
                </choices>
        </options>
    </input_field>
    <input_field type="date_range" name="leave_period" label="Leave Date" required="false" allow-multiple="false">        
    </input_field>
</filter_fields> 

<sub_report type="sql" name="mainTable">
<query>
From (SELECT l.leave_type_id, format(100 * (count( DISTINCT l.emp_number ) / ec.empcount), 2) AS percentage
FROM ohrm_leave l, (
SELECT count( * ) AS empcount
FROM hs_hr_employee where termination_id is null
)ec
where $X{IN,l.leave_type_id,leave_types} AND
l.status IN (2, 3) AND
$X{BETWEEN,l.date,leave_period} AND
l.emp_number in (select emp_number from hs_hr_employee where termination_id is null)
GROUP BY l.leave_type_id) as pe
LEFT JOIN ohrm_leave_type lt on lt.id = pe.leave_type_id
LEFT JOIN ohrm_operational_country oc on oc.id = lt.operational_country_id
LEFT JOIN hs_hr_country c on c.cou_code = oc.country_code

</query>
    <display_groups>
            <display_group name="leavePercentageInformation" type="one" display="true">
                <group_header></group_header>
                <fields>
                    <field display="true">
                        <field_name>if((SELECT value FROM hs_hr_config WHERE `key` = "leave.country_based") = "on", concat(c.cou_name, " - ", lt.name), lt.name)</field_name>
                        <field_alias>leaveType</field_alias>
                        <display_name>Leave Type</display_name>
                        <width>200</width>
                    </field> 
                    <field display="true">
                        <field_name>pe.percentage</field_name>
                        <field_alias>percentage</field_alias>
                        <display_name>Percentage (%)</display_name>
                        <width>150</width>
                        <align>right</align>
                    </field> 
                </fields>
            </display_group>
    </display_groups>
</sub_report>
    <page_limit>20</page_limit>       
</report>' );

SET @leave_menu_id := (SELECT id FROM ohrm_menu_item where menu_title = 'Leave');
SET @leave_report_menu_id := (SELECT id FROM ohrm_menu_item where parent_id=@leave_menu_id AND menu_title = 'Reports');
SET @view_report_screen_id := (SELECT id FROM ohrm_screen where `name` = 'View Reports' AND `action_url` = 'viewReport');
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES
('Leave Percentage Report', @view_report_screen_id, @leave_report_menu_id, 3, 1000, concat('/reportId/', @max_report_id + 1), 1);
