RaqForum 25 No.
2 Reply  •  346 View  • 
     ...

Make the Management Tree

 
sample program(31)

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
…

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.

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

Result

imagepng

sample program(31)
 
 •  346 View  •