Building a treetable-like form (part 2)

My last post was about "building a treetable-like form", it ended with a functional version which didn't look very sophisticated. Lets change that.

I'll use the same example as in part 1, the EMPLOYEES-table from hr.

The problem is not to read the hierarchical data, but to build a nice user interface to display the data. The main difference between a tree and a treetable is, that in a treetable you have a "standard" datarow with separate fields for each column, which, if needed, may also be changed in the treetable. If you scroll through the data, the table scrolls with the data. To achieve this, the component to display the tree must be part of the formsblock, so that for each row of the data there is a piece of tree rendered. So the tree-structure has to be splitted into record-based pieces, each of which has informastion about the current node, but also about preceding and following node. But what different types of renderings are possible in a tree?

Each "record" in the hierarchy exists of a number of square blocks, one for each level of the tree-depth the current row is located in. For each of this "squares" there are different possible renderings, depending on the row itself, but also depending on the preceding and following rows. In detail there are 4 possibilities, i colored different in the example.


Red - The rendering in the red square indicates that the current row is the last one at the specific level (the parent of the row has no other childs).
Green - The green square rendering indicates that there is another row at the same level as the current row (the parent of the row has another child).
Yellow/Blue - These rendering doesn't indicate a state for the current row, but for rows at a higher level. For each higher level there is one square, an yellow square means that the preceding row at the specific levels has no following rows at the same level, the blue one indicates that it has.
So, the treelines are build up of 4 different graphical objects, followed by the node-state, icon and label we also had so far.

If we can provide this information to a javabean-item, its quite simple to implement a treelike rendering. We would provide 1-for the red square, 3-for the yellow square, 2 for the green square, 4 for the blue square
In the example, for the different rows, we would have to provide
 

Row 1 is the top-level node, so there is only one level of tree-rendering and this will be 1 for the red-square-style. Row 2 is a row at tree-level 2, so it has two "squares" to be rendered first a 3 for an empty square, followed by a 2 for the green one. This information is provided for every row, so the complete tree gets rendered like a puzzle of square-blocks.

So, how to get that data in a single query? After some playing around i came up to this query:

SELECT MASTER,
       DETAIL,
       NEXTDETAIL,
       REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(PREV, '#'), 1,LENGTH(SYS_CONNECT_BY_PATH(PREV, '#'))-1),'#', '') ||
       AKT    || '|' ||
       STATUS || '|' ||
       ICON   || '|' ||
       LABEL            NODESTATE,
       EMPLOYEE_ID,
       LAST_NAME,
       FIRST_NAME,
       EMAIL,
       HIRE_DATE
-- -------------------------------------------------------------------------------------
FROM (SELECT MASTER,
             DETAIL,
             NEXTDETAIL,
             CASE WHEN NO_OF_SAME_LEVEL=COUNT_OF_SAME_LEVEL THEN
               '1'
             ELSE
               '2'
             END AKT,
             CASE WHEN DETAIL IN (SELECT COLUMN_VALUE
                                    FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
                                 )
             THEN
               'E'
             ELSE
               'C'
             END STATUS,
             CASE WHEN NO_OF_SAME_LEVEL=COUNT_OF_SAME_LEVEL THEN
               '3'
             ELSE
               '4'
             END PREV,
             ICON,
             LABEL,
             EMPLOYEE_ID,
             LAST_NAME,
             FIRST_NAME,
             EMAIL,
             HIRE_DATE
-- -------------------------------------------------------------------------------------
      FROM (SELECT MASTER,
                   DETAIL,
                   LEAD(MASTER,1) OVER (ORDER BY ROWNUM ASC)               NEXTDETAIL,
                   COUNT(*) OVER (PARTITION BY MASTER ORDER BY ROWNUM ASC) NO_OF_SAME_LEVEL,
                   COUNT(*) OVER (PARTITION BY MASTER)                     COUNT_OF_SAME_LEVEL,
                   ICON,
                   LABEL,
                   EMPLOYEE_ID,
                   LAST_NAME,
                   FIRST_NAME,
                   EMAIL,
                   HIRE_DATE
-- -------------------------------------------------------------------------------------
              FROM (SELECT NVL(MANAGER_ID,-1)             MASTER,
                           EMPLOYEE_ID                    DETAIL,
                           'db_user.gif'                  ICON,
                           LAST_NAME || ',' || FIRST_NAME LABEL,
                           EMPLOYEE_ID,
                           LAST_NAME,
                           FIRST_NAME,
                           EMAIL,
                           HIRE_DATE
                      FROM EMPLOYEES
                    START WITH MANAGER_ID IS NULL
                    CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
                    ORDER SIBLINGS BY LAST_NAME
                   )
             WHERE MASTER IN (SELECT COLUMN_VALUE
                                FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
                             )
            ORDER BY ROWNUM
           )
       )           
  START WITH MASTER=-1
  CONNECT BY PRIOR DETAIL=MASTER
 ;

 
The query looks quite complicated at first glance, i'll try to explain it a little beginning form the inner query.
The inner query does a hierarchical query on the employees-table and queries the pseudo-columns

in addition to the "real" table-column. These pseudo-columns are used in the outer queries to determine the node-states and "squares" for the tree-rendering-part.
The second query restricts the data to the "expanded" data hold in the pl/sql-table in the db-package and does some analytical functions on the data, which is needed for the determination of the tree-states.

The third query now takes the data provided and transforms it as required.

The last query does again a hierarchical query over the result and concatenates the needed values for the rendering bean (the SYS_CONNECT_BY_PATH-part).

The task for the javabean now is quite simple. My simple overrides the standard-textitem, so that the value is always passed correctly to the bean when the record-navigation in forms takes place (if i would took a Bean-Area i had to do theis synchronization by myself). The data the bean gets consists of 3 parts, separated by pipe:

<square-numbers(1,2,3,4)>|<state (C,E)>|<icon>

The bean now renders one of the four squares for each level given in the bean-value, followed by the appropiate node-state-icon (collapsed or expanded), followed by the custom icon. Here the prototype code

package forms.client;
import java.awt.Graphics;
import java.awt.Image;
import java.awt.Rectangle;
import java.awt.Toolkit;
import java.net.URL;
import java.util.StringTokenizer;
import javax.swing.ImageIcon;
import oracle.forms.handler.IHandler;
import oracle.forms.properties.ID;
import oracle.forms.ui.VTextField;

public class TreeStateItem extends VTextField
{
  private ImageIcon m_collapsed=new ImageIcon(this.getClass().getResource("collapsed.png"));
  private ImageIcon m_expanded=new ImageIcon(this.getClass().getResource("expanded.png"));  
  private Image     m_image;
  private String    m_lastImage="";
  private URL       m_codeBase;  
  private String    m_status;
  private String    m_nodestates;
  private String    m_label;
  private String    m_value="";

 
  public void init(IHandler handler)
  {
    // Remember Codebase
    m_codeBase = handler.getCodeBase();
    super.init(handler);
  }
 
  public void paint(Graphics g)
  {
    Rectangle r=this.getBounds();
    g.setColor(getBackground());
    g.fillRect(0, 0, (int)r.getWidth(), (int)r.getHeight());
    g.setColor(getForeground());
    // Draw lines for nodestates
    if (m_nodestates!=null)
    {
      char[] c=m_nodestates.toCharArray();
      for (int i=0;i<c.length;i++)
      {
        switch (c[i])
        {
          case '1' : g.drawLine(i*16+8, 0, i*16+8, (int)(r.getHeight()/2));
                     g.drawLine(i*16+8, (int)(r.getHeight()/2), i*16+15, (int)(r.getHeight()/2));
                     break;
          case '2' : g.drawLine(i*16+8, 0, i*16+8, (int)r.getHeight());
                     g.drawLine(i*16+8, (int)(r.getHeight()/2), i*16+15, (int)(r.getHeight()/2));
                     break;
          case '3' : break;
          case '4' : g.drawLine(i*16+8, 0, i*16+8, (int)r.getHeight());
                     break;
        }
      }
      if ("E".equals(m_status))
      {
        g.drawImage(m_expanded.getImage(), (c.length)*16+1, 3, 16, 16, m_expanded.getImageObserver());
      } else if ("C".equals(m_status))
      {
        g.drawImage(m_collapsed.getImage(), (c.length)*16+1, 3, 16, 16, m_collapsed.getImageObserver());
      }
      if (m_image!=null)
      {
        g.drawImage(m_image, (c.length+1)*16+1, 1, 16, 16, null);
      }
    }
  }
 
  private void loadImage(String imageName)
  {
    // LoadImage, taken from oracle-demo RolloverButton
    URL imageURL = null;
    boolean loadSuccess = false;
    if (!(imageName==null || "".equals(imageName)))
    {
      if (!imageName.equals(m_lastImage))
      {
        //JAR
        imageURL = getClass().getResource("/"+imageName);
        if (imageURL != null)
        {
          try
          {
            m_image = Toolkit.getDefaultToolkit().getImage(imageURL);
            loadSuccess = true;
          }
          catch (Exception ilex)
          {
          }
        }
        //DOCBASE
        if (!loadSuccess)
        {
          try
          {
            if (imageName.toLowerCase().startsWith("http://")||imageName.toLowerCase().startsWith("https://"))
            {
              imageURL = new URL(imageName);
            }
            else
            {
              imageURL = new URL(m_codeBase.getProtocol() + "://" + m_codeBase.getHost() + ":" + m_codeBase.getPort() + imageName);
            }
            try
            {
              m_image = createImage((java.awt.image.ImageProducer) imageURL.getContent());
              loadSuccess = true;
            }
            catch (Exception ilex)
            {
            }
          }
          catch (java.net.MalformedURLException urlex)
          {
          }
        }
        //CODEBASE
        if (!loadSuccess)
        {
          try
          {
            imageURL = new URL(m_codeBase, imageName);
            try
            {
              m_image = createImage((java.awt.image.ImageProducer) imageURL.getContent());
              loadSuccess = true;
            }
            catch (Exception ilex)
            {
            }
          }
          catch (java.net.MalformedURLException urlex)
          {
          }
        }
        // remember last image
        m_lastImage=imageName;
      }
    }
  }
 
  public boolean setProperty(ID property, Object value)
  {
    if (property==ID.VALUE)
    {
      // "Catch" the value, tokenize it and store it in member vars
      m_value=(String)value;
      StringTokenizer st=new StringTokenizer(m_value, "|");
      m_nodestates="";
      m_status="";
      m_label="";
      if (st.hasMoreTokens())
      {
        m_nodestates=st.nextToken();
      }
      if (st.hasMoreTokens())
      {
        m_status=st.nextToken();
      }
      String imagename=null;
      if (st.hasMoreTokens())
      {
        imagename=st.nextToken();
        loadImage(imagename);
      }
      if (st.hasMoreTokens())
      {
        m_label=st.nextToken();
      }
      if (imagename==null)
      {
        m_image=null;
        m_lastImage=null;
      }
      // pass an empty value to the superclass
      return super.setProperty(property, "");
    } else
    {
      return super.setProperty(property, value);
    }
  }

  public Object getProperty(ID property)
  {
    // TODO:  Override this oracle.forms.ui.VTextField method
    if (property==ID.VALUE)
    {
      // return the locally stored value
      return m_value;
    } else
    {
      return super.getProperty(property);
    }
  }
}

Putting everything together, there is not much logic in the form itself. I created a view based on the formerly discussed query. Next step is to create a tabular block based on that view.
Then, add the triggers as discussed in part 1 of the article. Last, set the implementation class of the item containing the node-state to forms.client.TreeStateItem. And finally, as for every java-bean, wrap the javabean in a jar and put it into the archive or archine_jini-tag in the formsweb.cfg.