I am using Oracle Application Express 18.2.0.00.12 and I have a question regarding the use of lists to create an organization chart for my department.
In my department, there is only one department manager, three section managers, and 30 team members. Each section manager manages 10 team members. The org chart needs to be formatted so that the sole department manager is at top, the three section managers in the middle, and the 30 team members at the bottom.
The problem: My requirement is to limit the number of employees at the team members level to 9 employees (3 for each section manager). However, if a fourth section manager is added, I would want this manager to be added to the second level with the other section managers, and then have 12 team members in each row below level two (three team members for each of the four section managers). Regardless of the number of section managers in level 2, I will always want three team members for each section manager in levels 3, 4, 5, etc.
I have created a table using the following code:
create table employee_table(employee_number, name, manager, department) as
select 1001, 'Big Boss', null, 100 from dual union all
select 1100, 'Beth Mgr', 1001, 100 from dual union all
select 1101, 'Jim' , 1100, 100 from dual union all
select 1102, 'Jackie' , 1100, 100 from dual union all
select 1103, 'Helen' , 1100, 100 from dual union all
select 1104, 'Tom' , 1100, 100 from dual union all
select 1105, 'Vance' , 1100, 100 from dual union all
select 1106, 'Rosa' , 1100, 100 from dual union all
select 1107, 'Chuck' , 1100, 100 from dual union all
select 1200, 'Duck Mgr', 1001, 200 from dual union all
select 1201, 'Danny' , 1200, 200 from dual union all
select 1202, 'Henry' , 1200, 200 from dual union all
select 1203, 'Mac' , 1200, 200 from dual union all
select 1204, 'Hassan' , 1200, 200 from dual union all
select 1205, 'Ann' , 1200, 200 from dual union all
select 1300, 'Adam Mgr', 1001, 300 from dual union all
select 1301, 'Wendy' , 1300, 300 from dual;
I have created a list in APEX using the following query:
select case lvl when 3 then lvl + ceil(rn/3) - 1 else lvl end as lvl,
employee_number, name, manager, department
from (
select level as lvl, employee_number, name, manager, department,
rownum as ord,
row_number() over
(partition by manager order by employee_number) as rn
from employee_table2
start with manager is null
connect by prior employee_number = manager
)
order by ord;
I then used Christian Rokitta's instruction for how to create a basic Org chart using a list and CSS in APEX (http://rokitta.blogspot.com/2013/12/pure-css3-org-tree-with-apex-list.html).
For the list template:
Template class: Hierarchical Expanded
Before List Entry:
<div class="tree">
<ul>
Template Definition
List Template Current
<li><a href="#LINK#">#TEXT#</a></li>
List Template Noncurrent
<li><a href="#LINK#">#TEXT#</a></li>
Before Sublist Entry
<ul>
Sublist Entry
Sublist Template Current
<li><a href="#LINK#">#TEXT#</a></li>
Sublist Template Noncurrent
<li><a href="#LINK#">#TEXT#</a></li>
After Sublist Entry
</ul>
After List Entry
</ul>
</div>
In the Inline CSS section of the page attribute:
.tree {
overflow-x: auto;
}
.tree ul {
padding-top: 20px;
position: relative;
white-space: nowrap;
}
.tree li {
display: inline-block;
white-space: nowrap;
vertical-align: top;
margin: 0 -2px;
text-align: center;
list-style-type: none;
position: relative;
padding: 20px 5px 0;
transition: all .5s;
-webkit-transition: all .5s;
-moz-transition: all .5s;
}
/*We will use ::before and ::after to draw the connectors*/
.tree li::before,.tree li::after {
content: '';
position: absolute;
top: 0;
right: 50%;
border-top: 1px solid #ccc;
width: 50%;
height: 20px;
}
.tree li::after {
right: auto;
left: 50%;
border-left: 1px solid #ccc;
}
/*We need to remove left-right connectors from elements without any siblings*/
.tree li:only-child::after,.tree li:only-child::before {
display: none;
}
/*Remove space from the top of single children*/
.tree li:only-child {
padding-top: 0;
}
/*Remove left connector from first child and
right connector from last child*/
.tree li:first-child::before,.tree li:last-child::after {
border: 0 none;
}
/*Adding back the vertical connector to the last nodes*/
.tree li:last-child::before {
border-right: 1px solid #ccc;
border-radius: 0 5px 0 0;
}
.tree li:first-child::after {
border-radius: 5px 0 0 0;
}
/*Time to add downward connectors from parents*/
.tree ul ul::before {
content: '';
position: absolute;
top: 0;
left: 50%;
border-left: 1px solid #ccc;
width: 0;
height: 20px;
}
.tree li a {
border: 1px solid #ccc;
padding: 5px 10px;
text-decoration: none;
color: #666;
font-family: arial, verdana, tahoma;
font-size: 11px;
display: inline-block;
-webkit-border-radius: 5px;
-moz-border-radius: 5px;
border-radius: 5px;
}
/*Time for some hover effects*/
/*We will apply the hover effect the the lineage of the element also*/
.tree li a:hover,.tree li a:hover+ul li a {
background: #c8e4f8;
color: #000;
border: 1px solid #94a0b4;
}
/*Connector styles on hover*/
.tree li a:hover+ul li::after,.tree li a:hover+ul li::before,.tree li a:hover+ul::before {
border-color: #94a0b4;
}
The above code produces the following Org Chart in APEX:
This is very close to the visual I am trying to produce. The reason behind the "9 team members per level" is to try to prevent the chart from being excessively large horizontally. However, if you look to the left, for example, Employees 1104, 1105, and 1106 are underneath 1103 and employee 1107 is underneath 1106. It would be best if employee 1104 were underneath 1101, employee 1105 underneath 1102, employee 1106 underneath 1103, and employee 1107 underneath 1104. Is there any way to edit the code so that it produces this result visually?
Thank you in advance.