SELECT @group_id := max(report_group_id) + 1 from ohrm_report_group;

INSERT INTO `ohrm_report_group` (`report_group_id`, `name`, `core_sql`) VALUES 
        (@group_id, 'employee_leave', 'SELECT  selectCondition
FROM (
SELECT CONVERT( CONCAT( date(le.from_date), \'to\', date(le.to_date) ) USING latin1 ) AS leave_period , lt.name AS `type` , ROUND(le.no_of_days,2) AS entitlement , 
SUM(IF(l.date between le.from_date and le.to_date, IF( l.status =3, ROUND(l.length_days,2), 0), 0)) AS taken_days,
SUM(IF(l.date between le.from_date and le.to_date, IF(l.status = 2, ROUND(l.length_days,2), 0), 0)) AS scheduled_days,
IF(let.name = \'Brought Forward\', ROUND(le.no_of_days,2),0) AS `brought_forward`,
(IF(let.name = \'Added\', ROUND(le.no_of_days,2),0) + IF(let.name = \'Accrual\', ROUND(le.no_of_days,2),0) + IF(let.name = \'Brought Forward\', ROUND(le.no_of_days,2),0)) AS `total`
FROM ohrm_leave l
LEFT JOIN ohrm_leave_entitlement le ON l.emp_number = le.emp_number and le.leave_type_id = l.leave_type_id
LEFT JOIN ohrm_leave_type lt ON lt.id = le.leave_type_id
LEFT JOIN ohrm_leave_entitlement_type let ON let.id =   le.entitlement_type 
WHERE  whereCondition1
GROUP BY lt.id,`leave_period`
ORDER BY `leave_period` DESC
) AS `report_records`');

SELECT @report_id := max(report_id) + 1 from ohrm_report;
INSERT INTO `ohrm_report` (`report_id`, `name`, `report_group_id`, `use_filter_field`, `type`) VALUES 
(@report_id, 'Employee Leave Report', @group_id, 1, 'ADVANCED_REPORTS');


SELECT @display_field_id := max(display_field_id) + 1 from ohrm_display_field;
INSERT INTO `ohrm_display_field` (`display_field_id`, `report_group_id`, `name`, `label`, `field_alias`, `is_sortable`, `sort_order`, `sort_field`, `element_type`, `element_property`, `width`, `is_exportable`, `text_alignment_style`, `is_value_list`, `display_field_group_id`, `default_value`, `is_encrypted`, `is_meta`) VALUES
    (@display_field_id + 0, @group_id, 'leave_period', 'Leave Period', null, 'false', null, null, 'labelDate', '<xml><getter>leave_period</getter><dateSplit> to </dateSplit></xml>', 200, '0', 'left', false, null, '---', false, false),
    (@display_field_id + 1, @group_id, 'type', 'Type', null, 'false', null, null, 'label', '<xml><getter>type</getter></xml>', 165, '0', 'left', false, null, '---', false, false),
    (@display_field_id + 2, @group_id, 'entitlement', 'Entitlement', null, 'false', null, null, 'label', '<xml><getter>entitlement</getter></xml>', 120, '0', 'right', false, null, '---', false, false),
    (@display_field_id + 3, @group_id, 'taken_days', 'Taken', null, 'false', null, null, 'label', '<xml><getter>taken_days</getter></xml>', 120, '0', 'right', false, null, '---', false, false),
    (@display_field_id + 4, @group_id, 'brought_forward', 'Brought Forward', null, 'false', null, null, 'label', '<xml><getter>brought_forward</getter></xml>', 120, '0', 'right', false, null, '---', false, false),
    (@display_field_id + 5, @group_id, '(total - taken_days - scheduled_days)', 'Remaining', 'remaining', 'false', null, null, 'label', '<xml><getter>remaining</getter></xml>', 120, '0', 'right', false, null, '---', false, false),
    (@display_field_id + 6, @group_id, 'total', 'Total', null, 'false', null, null, 'label', '<xml><getter>total</getter></xml>', 120, '0', 'right', false, null, '---', false, false);


SELECT @selected_display_id := max(`id`) + 1 from ohrm_selected_display_field;
INSERT INTO `ohrm_selected_display_field` (`id`, `display_field_id`, `report_id`) VALUES
	(@selected_display_id + 0, @display_field_id + 0, @report_id),
	(@selected_display_id + 1, @display_field_id + 1, @report_id),
	(@selected_display_id + 2, @display_field_id + 2, @report_id),
	(@selected_display_id + 3, @display_field_id + 3, @report_id),
	(@selected_display_id + 4, @display_field_id + 4, @report_id),
	(@selected_display_id + 5, @display_field_id + 5, @report_id),
	(@selected_display_id + 6, @display_field_id + 6, @report_id);

SELECT @filter_field_id := max(filter_field_id) + 1 from ohrm_filter_field;

INSERT INTO `ohrm_filter_field` (`filter_field_id`, `report_group_id`, `name`, `where_clause_part`, `filter_field_widget`, `condition_no`, `required`) VALUES 
    (@filter_field_id + 0, @group_id, 'employee', 'l.emp_number', 'ohrmReportWidgetEmployeeListAutoFill', 1, 'true'),
    (@filter_field_id + 1, @group_id, 'leave_period', 'l.date', 'ohrmWidgetLeavePeriodList', 1, 'false'),
    (@filter_field_id + 2, @group_id, 'leave_type', 'lt.id', 'ohrmWidgetLeaveTypeCheckboxGroup', 1, 'true');

INSERT INTO `ohrm_selected_filter_field` (`report_id`, `filter_field_id`, `filter_field_order`, `value1`, `value2`, `where_condition`, `type`) VALUES
    (@report_id, @filter_field_id + 0, 1, null, null, null, 'Runtime'),
    (@report_id, @filter_field_id + 1, 2, null, null, null, 'Runtime'),
    (@report_id, @filter_field_id + 2, 3, null, null, null, 'Runtime');