14

APEX Plugin – Body Chart

Recently I had an inquiry via my website asking whether I was able to create a selectable body chart APEX plugin for a physiotherapy practice so they could easily highlight joint pain and record information against these selected areas. One of the challenging requirements was to allow fine grained selectivity of 16 areas on each hand, whilst working within a very limited budget.

Initially I was hesitant, since I had never done this, but I recalled reading a number of blogs in the past which described using Raphael.js, an open source SVG drawing library which easily allowed you to hook in javascript events on mousenter, mouseout etc. to obtain the details of the selected area and add colour fills. So armed with this knowledge I thought I would take a risk and give it a go as I could see the potential for other uses, and to my surprise it was actually a lot easier than I expected it to be.

Below is a Youtube video to give you an understanding of how the plugin works and here’s where you can demo the plugin in action.

The following blog post should provide you the necessary javascript code example for you to build the map. I used it as my starting point for the item plugin and I found it really easy to follow. Since the post covers most of the javascript side the rest of the post will focus on creating the actual supporting JSON and SVG outline image as I found this to be the most troublesome and time intensive part of the task.

These are the high level steps which I performed.

  • Hand draw an outline on a piece of paper
  • Scan the outline into a jpeg or other image format
  • Open in Adobe Illustrator and use the “Live Trace” feature, ensuring that your divided sections remain intact e.g.
  • Save the file as SVG and select the “Convert to Outline” option
  • Open in Notepad++ or an equivalent editor
  • Extract the “d” value from the Paths which should look something like the following:
    
    
  • Each of these paths need to defined in a single JSON object which Raphael.js will draw e.g.
    apex.svgMaps = apex.jQuery.extend({}, {
       BodyMapPaths: {
          "part1": {
             name: "Frontal Region",
             "path": "M145.044,18c0,16,0,32,0,48c-10.667,0-21.333,0-32,0c0-16.333,0-32.667,0-49c4.292-2.042,8.513-4.153,14-5C134.863,12.181,140.377,14.667,145.044,18z"
          },
          "part2": {
             name: "Occipital Region",
             "path": "M406.044,48c-0.716,2.715,4.716-0.715,4,2c0.92,5.92-1.711,8.289-2,13c-2.35,1.65-5.688,2.313-6,6c-14.333,0-28.667,0-43,0c-0.512-2.822-2.178-4.488-5-5c-1.203-3.796-2.128-7.872-3-12c-1.053-3.719,3.372-1.961,4-4c-2.062-22.062,6.979-33.021,24-36C398.786,12.745,409.515,27.321,406.044,48z"
          },
          .....
    

    Note: in order to work out what body part each path was I opened the svg file in notepad++ and replaced the path XML with insert statements and loaded the data into a database table. I then performed an update post load to give each path a name/id the rownum sequence so I could identify what it was on hover to then perform the name translation. e.g.

    CREATE
      TABLE SVG_BODYMAP_PATHS
      (
        "SEQ"             NUMBER,
        "ID"              VARCHAR2(4000 BYTE),
        "NAME"            VARCHAR2(4000 BYTE),
        "DESCRIPTION"     VARCHAR2(4000 BYTE),
        "B_PATH"          VARCHAR2(4000 BYTE),
        "SELECTABLE"      VARCHAR2(1 BYTE) DEFAULT 'Y'
      );
    --
    -- Load the data before updating the null columns with the row numbers e.g.
    --
    INSERT INTO svg_bodymap_parts(b_path) VALUES('M111.044,19c2.331,13.669,0.335,31.665,1,47c-4.021,0.688-5.708-0.958-9-1c-2.524-3.143-2.982-8.351-4-13c-1.053-3.719,3.373-1.961,4-4C101.946,34.569,104.587,24.876,111.044,19z');
    INSERT INTO svg_bodymap_parts(b_path) VALUES('M146.044,20c7.416,4.326,10.279,17.154,8,28c-0.715,2.715,4.715-0.715,4,2c0.36,6.693-1.135,11.532-4,15c-3.22-0.22-4.231,1.769-8,1C146.044,50.667,146.044,35.333,146.044,20z');
    --
    -- Set a unique identifier for each path
    --
    UPDATE SVG_BODYMAP_PATHS
    SET seq = rownum-1
    ,   name = 'part'||rownum
    ,   id = 'part'||rownum
    ;
    --
    -- Extract data in JSON format since we will store it in
    -- a static javascript file since the image will never change
    --
    SELECT '"'||id||'":{name: "'||name||'", "path":"'||b_path||'"},' json
    FROM   SVG_BODYMAP_PATHS
    ORDER BY seq
    ;
    

I did have some issues getting all the selectable areas traced properly in Adobe Illustrator and it’s not the prettiest of outlines but it provided the customer everything they needed and they were quite satisfied. You could always outsource this part to an experienced graphic designer to get a higher quality image produced.

Also one of the labour intensive tasks was performing an English translation of the paths one by one, and there were 190 paths (a path is a selectable area/shape). Add to this a review process which meant I had to repeat this step all over again after having to add more selectable areas to the body.

An SVG image is outputted top to bottom left to right, which means a change at the top of the image will change the sequence of all paths below. I’d strongly recommend that if you are going to build a similar plugin that you get the image outline correct the first time especially if you have outsourced the map to a graphic designer. As it will reduce your costs and associated headaches.

To give you an idea of the amount of time required to build the plugin, I was able to produce the entire solution in just under 4 days which included the initial prototype, drawing the image and converting to SVG, client meetings, a review process, creating the jQuery plugin, creating the APEX plugin, testing across IE6-9 Firefox Google Chrome, and producing documentation.

Of course the amount of time may vary depending upon how familiar you are with javascript and creating APEX plugins but the good news is that once you’ve done the process once it will be much easier second time around.

7

APEX Plugin – Interactive Report Dynamic Action Package

A couple of months ago I had a request to create a “Freeze Columns” dynamic action plugin for an interactive report which behaved like the following demo. Whilst I wasn’t sure whether it would be possible I knew that in APEX 4.0 we had some event hooks such as “After Refresh” which we could bind a dynamic action to. We could use this event to manipulate the Interactive Report HTML after every refresh to produce the required layout. Whilst the initial prototype was quick to implement, mainly thanks to jQuery, there were a number of challenges which I faced:

  1. Cross browser and version support since different flavours of IE handled the positioning of scrollbars differently. Achieving equal row heights when splitting the report into two HTML tables was tricky, each browser seemed to behave a little different.
  2. The header drop down menu’s would be placed incorrectly when horizontal scrolling occurred on the unfrozen set of columns, which unfortunately wasn’t as simple as the usual workaround for relative positioning.
  3. After resolving the above and the addition of several enhancement requests i.e. adding in additional vertical scrolling whilst freezing the header (see this demo) and making it work with Clarifit’s existing column grouping plugin (demo), I’m proud to announce the release of publicly available commercial package to perform all 3 cosmetic changes in combination or independently of each other. Here’s a screenshot to give you a visual of the plugins in action:

    Oracle APEX - Interactive Report Dynamic Actions

    Here’s a YouTube video which walks you through how to combine all 3 dynamic actions on the same interactive report.

    Lastly here’s the demo application to see them in action, and download page. The added extra is that, thanks to the release of APEX 4.1 on apex.oracle.com last Friday, I have been able to certify the package against this new release of APEX :)