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_turnover_hiring', 'SELECT selectCondition
FROM `hs_hr_employee` AS e
LEFT JOIN `ohrm_subunit` AS cs
  ON e.work_station = cs.id
LEFT JOIN `ohrm_job_title` AS jt
  ON e.job_title_code = jt.id
WHERE e.joined_date IS NOT NULL
  AND whereCondition1
GROUP BY e.emp_number
;');

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 Turnover Hiring 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, 'CONCAT (e.emp_firstname, \' \', e.emp_lastname)', 'Employee Name', 'employee_name', 'false', null, null, 'label', '<xml><getter>employee_name</getter></xml>', 200, '0', 'left', false, null, '---', false, false),
    (@display_field_id + 1, @group_id, 'get_sub_unit_path(cs.id)', 'Sub Unit', 'empSubUnit', 'false', null, null, 'label', '<xml><getter>empSubUnit</getter></xml>', 300, '0', 'left', true, null, '---', false, false),
    (@display_field_id + 2, @group_id, 'e.joined_date', 'Hired Date', 'hired_date', 'false', null, null, 'labelDate', '<xml><getter>hired_date</getter></xml>', 100, '0', 'left', false, null, '---', false, false),
    (@display_field_id + 3, @group_id, 'jt.job_title', 'Job Title', 'job_title', 'false', null, null, 'label', '<xml><getter>job_title</getter></xml>', 300, '0', 'left', 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);

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, 'date_range', 'e.joined_date', 'ohrmWidgetDateRange', 1, 'false'),
    (@filter_field_id + 1, @group_id, 'sub_unit', 'e.work_station', 'ohrmWidgetSubDivisionList', 1, 'false');

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');