samedi 25 avril 2015

print departments hierarchy to a table


I have a relationship table for departments as below:

+---------------+----------------+
|     Dept.     | superior Dept. |
+---------------+----------------+
| "00-01"       | "00"           |
| "00-02"       | "00"           |
| "00-01-01"    | "00-01"        |
| "00-01-02"    | "00-01"        |
| "00-01-03"    | "00-01"        |
| "00-02-01"    | "00-02"        |
| "00-02-03"    | "00-02"        |
| "00-02-03-01" | "00-02-03"     |
+---------------+----------------+

Now I want to list them according to their tiers like this:

+-----------+--------------+--------------+--------------+
| Top Dept. | 2-tier Dept. | 3-tire Dept. | 4-tier Dept. |
+-----------+--------------+--------------+--------------+
|        00 |              |              |              |
|           | 00-01        |              |              |
|           |              | 00-01-01     |              |
|           |              | 00-01-02     |              |
|           | 00-02        |              |              |
|           |              | 00-02-01     |              |
|           |              | 00-02-03     |              |
|           |              |              | 00-02-03-01  |
+-----------+--------------+--------------+--------------+

I can construct the relationship tree using the code below:
TreeNode.java

import java.util.LinkedList;
import java.util.List;

public class TreeNode {
  public String value;
  public List children = new LinkedList();

  public TreeNode(String rootValue) {
    value = rootValue;
  }

}

PairsToTree.java

import java.util.*;

public class PairsToTree {

  public static void main(String[] args) throws Exception {
    // Create the child to parent hash map
    Map <String, String> childParentMap = new HashMap<String, String>(8);
    childParentMap.put("00-01", "00");
    childParentMap.put("00-02", "00");
    childParentMap.put("00-01-01", "00-01");
    childParentMap.put("00-01-02", "00-01");
    childParentMap.put("00-01-03", "00-01");
    childParentMap.put("00-02-01", "00-02");
    childParentMap.put("00-02-03", "00-02");
    childParentMap.put("00-02-03-01", "00-02-03");

    // All children in the tree
    Collection<String> children = childParentMap.keySet();

    // All parents in the tree
    Collection<String> values = childParentMap.values();

    // Using extra space here as any changes made to values will
    // directly affect the map
    Collection<String> clonedValues = new HashSet();
    for (String value : values) {
      clonedValues.add(value);
    }

    // Find parent which is not a child to any node. It is the
    // root node
    clonedValues.removeAll(children);

    // Some error handling
    if (clonedValues.size() != 1) {
      throw new Exception("More than one root found or no roots found");
    }

    String rootValue = clonedValues.iterator().next();
    TreeNode root = new TreeNode(rootValue);

    HashMap<String, TreeNode> valueNodeMap = new HashMap();
    // Put the root node into value map as it will not be present
    // in the list of children
    valueNodeMap.put(root.value, root);

    // Populate all children into valueNode map
    for (String child : children) {
      TreeNode valueNode = new TreeNode(child);
      valueNodeMap.put(child, valueNode);
    }

    // Now the map contains all nodes in the tree. Iterate through
    // all the children and
    // associate them with their parents
    for (String child : children) {
      TreeNode childNode = valueNodeMap.get(child);
      String parent = childParentMap.get(child);
      TreeNode parentNode = valueNodeMap.get(parent);
      parentNode.children.add(childNode);
    }

    // Traverse tree in level order to see the output. Pretty
    // printing the tree would be very
    // long to fit here.
    Queue q1 = new ArrayDeque();
    Queue q2 = new ArrayDeque();
    q1.add(root);
    Queue<TreeNode> toEmpty = null;
    Queue toFill = null;
    while (true) {
      if (false == q1.isEmpty()) {
        toEmpty = q1;
        toFill = q2;
      } else if (false == q2.isEmpty()) {
        toEmpty = q2;
        toFill = q1;
      } else {
        break;
      }
      while (false == toEmpty.isEmpty()) {
        TreeNode node = toEmpty.poll();
        System.out.print(node.value + ", ");
        toFill.addAll(node.children);
      }
      System.out.println("");
    }
  }

}

but can't figure it out how to format the output to resemble a table. Or is there a sql statement/stored procedure(like this question) to do this?

EDIT: the Dept. name is just a sample for the sake of convenience, it can be arbitrary string.


Aucun commentaire:

Enregistrer un commentaire