# 4. Make the Management Tree

l  Problem

The following is the Employee table of an enterprise, inclusive of the employee ID and name:

 EMPNO ENAME 7902 Ford 7788 Scott 7900 James 7844 Turner 7654 Martin 7521 Ward 7499 Allen 7934 Miller 7876 Adams 7782 Clark 7698 Blake 7566 Jones 7369 Smith 7839 King

The superior-subordinate relationships between employees are given in the table below. For example, the first record indicates that the manager of Employee 7902 is the Employee 7566. The 14th record indicates the Employee 7839 doesn’t have a manager because he is the highest manager.

 EMPNO MGR 7902 7566 7788 7566 7900 7698 7844 7698 7654 7698 7521 7698 7499 7698 7934 7782 7876 7788 7782 7839 7698 7839 7566 7839 7369 7902 7839

Please return a result set to describe the hierarchical relationships of the data in the table, according to the following structure:

 EMP_TREE King King – Blake King – Blake – Allen … King – Clark King – Clark – Miller …

l  Tip

General steps: First, select out an employee A, and then find out its line manager B, and then the C that is line manager of B. After finding all these persons, run the loop until reaching the line manager that is the highest leader. Then, jump out of the inner loop, and begin a new loop to search for the managers for the next employee.

Finally, sort the resulting set and you will get the desired result.

l  Code

 A B C D 1 =file("C:\\txt\\Employees2.txt").import@t() Find the employee information 2 =file("C:\\txt\\Relationships.txt").import@t() Find the relation between employees 3 =[] Store the subordinates relation tree having been found 4 for A1 Loop against the employee table 5 =A4.EMPNO Get the number of the current employee 6 =A4.ENAME Get name of the current employee 7 for 8 =A2.select(EMPNO==B5).MGR Get the upper class of the current employee 9 if C8==null break If there is no line manager of the current employee, then put an end to the inner loop, and jump to the next employee 10 =A1.select(EMPNO==C8).ENAME Get name of the line manager of the current employee 11 >B6=C10+"-"+B6 Add the present line manager of the employee to B6 12 >B5=C8 Proceed to select out the line manager’s line manager 13 next 14 >A3=A3|B6 Store the found hierarchy tree in the A3 15 =A3.sort() Sort the cell A3 and display the desired result as required in the problem

l  Result