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, 'head_count', 'SELECT selectCondition
FROM hs_hr_employee e

LEFT JOIN ohrm_subunit s
ON whereCondition1 

LEFT JOIN ohrm_subunit c
ON e.work_station = c.id

LEFT JOIN ohrm_subunit p
ON get_parant_id(p.id) = s.id AND p.lft <= c.lft and p.rgt >= c.rgt

WHERE 

whereCondition2

AND

whereCondition3

AND 

((s.id = 1)
OR
(p.id is not null or s.id = c.id))

GROUP BY sub_unit
;');

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, 'Head Count 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, 'CASE WHEN p.id IS NULL THEN get_sub_unit_title(s.id) ELSE p.name END', 'Sub Unit', 'sub_unit', 'false', null, null, 'treeLink', CONCAT('<xml><labelGetter>sub_unit</labelGetter><nodeTypeGetter>NodeType</nodeTypeGetter><placeholderGetters><id>SubUnitId</id><empStatus>empStatus</empStatus><include>include</include></placeholderGetters><urlPattern>../../displayHeadCountReport/reportId/', @report_id, '?sub_unit={id}#employment_status={request:time.employment_status,0}#include={request:time.include,1}</urlPattern></xml>'), 200, '0', 'left', false, null, '---', false, true),
   (@display_field_id + 1, @group_id, 'COUNT(emp_number)', 'Number of Employees', 'emp_count', 'false', null, null, 'label', '<xml><getter>emp_count</getter></xml>', 150, '0', 'right', false, null, '---', false, true),
   (@display_field_id + 2, @group_id, 'CASE WHEN p.id IS NULL THEN s.id ELSE p.id END', 'SubUnitId', 'SubUnitId', 'false', null, null, 'label', '<xml><getter>SubUnitId</getter></xml>', 200, '0', 'left', false, null, '---', false, true),
   (@display_field_id + 3, @group_id, 'IF(e.emp_status is NULL ,0,e.emp_status)', 'empStatus', 'empStatus', 'false', null, null, 'label', '<xml><getter>empStatus</getter></xml>', 200, '0', 'left', false, null, '---', false, true),
   (@display_field_id + 4, @group_id, 'IF(is_sub_unit_leaf_node(p.id), \'leaf-node\', \'inner-node\')', 'NodeType', 'NodeType', 'false', null, null, 'label', '<xml><getter>NodeType</getter></xml>', 0, '0', 'left', false, null, '---', false, true);
   

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

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, 'employment_status', 'e.emp_status', 'ohrmWidgetEmploymentStatusList', 2, null),
    (@filter_field_id + 1, @group_id, 'sub_unit', 's.id', 'ohrmWidgetSubDivisionListWithParent', 1, null),
    (@filter_field_id + 2, @group_id, 'include', 'e.termination_id', 'ohrmWidgetTerminatedFilter', 3, null);

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

SELECT (
  SELECT `f`.`name`
  FROM ohrm_subunit f 
  WHERE get_parant_id(f.id) = '1'
    AND f.lft <= c.lft and f.rgt >= c.rgt
) AS `sub_unit`, 
COUNT(emp_number) AS `emp_count`
FROM hs_hr_employee e
LEFT JOIN ohrm_subunit c 
ON e.work_station = c.id
WHERE (e.termination_id IS NULL)
GROUP BY sub_unit;