1027

Miscellaneous - Google - Visualization Orgchart

20

Orgchart

PL/SQL

PL/SQL

begin

  htp.p(ted_google_visual_orgchart.orgchart_test);

end;

the_div

style="overflow-x: scroll;"

TED_GOOGLE_VISUAL_ORGCHART

package ted_google_visual_orgchart is

​

​

​

type t_orgchart_record is record

  ( name    varchar2(4000)

  , manager varchar2(4000)

  , tooltip varchar2(4000)

  )

;

type t_orgchart_table is table of t_orgchart_record;

​

​

​

function orgchart_test

  return clob

;

​

​

end ted_google_visual_orgchart;

​

TED_GOOGLE_VISUAL_ORGCHART

package body ted_google_visual_orgchart is

​

​

​

function orgchart

  ( p_rows            in t_orgchart_table

  , p_html_element_id in varchar2

  )

  return clob

is

​

  v_lf            varchar2(2) := chr(10);

​

  v_c1_count      pls_integer := 0;

  v_name          varchar2(4000);

  v_result        clob;

​

begin

​

  dbms_lob.createtemporary(v_result , true, dbms_lob.call);

​

  -- init v_result

  dbms_lob.append(v_result, v_lf);

  dbms_lob.append(v_result, '  <script type="text/javascript" src="https://www.google.com/jsapi"></script>' || v_lf);

  dbms_lob.append(v_result, '  <script type="text/javascript">' || v_lf);

  dbms_lob.append(v_result, '    google.load("visualization", "1", {packages:["orgchart"]});' || v_lf);

  dbms_lob.append(v_result, '    google.setOnLoadCallback(drawChart);' || v_lf);

  dbms_lob.append(v_result, '    function drawChart() {' || v_lf);

  dbms_lob.append(v_result, '      var data = new google.visualization.DataTable();' || v_lf);

  dbms_lob.append(v_result, '      data.addColumn("string", "Name");' || v_lf);

  dbms_lob.append(v_result, '      data.addColumn("string", "Manager");' || v_lf);

  dbms_lob.append(v_result, '      data.addColumn("string", "ToolTip");' || v_lf);

​

  -- open rows

  dbms_lob.append(v_result, '      data.addRows([' || v_lf);

  if p_rows.count > 0 then

    for i in p_rows.first .. p_rows.last loop

​

      v_c1_count := v_c1_count + 1;

      -- build v_name

      v_name := p_rows(i).name;

      if v_name not like '{v%}' then

        v_name := '"' || v_name || '"';

      end if;

      -- build row

      dbms_lob.append(v_result, '        ');

      if v_c1_count > 1 then

        dbms_lob.append(v_result, ', ');

      else

        dbms_lob.append(v_result, '  ');

      end if;

      dbms_lob.append

        ( v_result

        ,    '['   || v_name

          || ', "' || p_rows(i).manager || '"'

          || ', "' || p_rows(i).tooltip || '"'

          || ']'

          || v_lf

        )

      ;

    end loop;

  end if;

  -- close rows

  dbms_lob.append(v_result, '      ]);' || v_lf);

​

  -- close v_result

  dbms_lob.append(v_result, '      var chart = new google.visualization.OrgChart(document.getElementById("' || p_html_element_id || '"));' || v_lf);

  dbms_lob.append(v_result, '      chart.draw(data, {allowHtml:true});' || v_lf);

  dbms_lob.append(v_result, '    }' || v_lf);

  dbms_lob.append(v_result, '  </script>' || v_lf);

​

​

  -- only return a result if there is anything to return

  if v_c1_count > 0 then

    return v_result;

  else

    return null;

  end if;

​

end orgchart;

​

​

​

function orgchart_test

  return clob

is

​

  v_result         clob;

  v_rows           t_orgchart_table := t_orgchart_table();

  v_rows_n         pls_integer := 0;

  cursor c1 is

    select a.entry_text as name

    ,      a.parent_entry_text as manager

    ,      a.entry_text as tooltip

    from apex_application_list_entries a

    where a.application_id = v('APP_ID')

    and   a.list_name      = 'Navigation Menu'

  ;

​

begin

​

  dbms_lob.createtemporary(v_result , true, dbms_lob.call);

  for c1_rec in c1 loop

    v_rows_n := v_rows_n + 1;

    v_rows.extend;

    v_rows(v_rows_n).name    := c1_rec.name;

    v_rows(v_rows_n).manager := c1_rec.manager;

    v_rows(v_rows_n).tooltip := c1_rec.tooltip;

  end loop;

​

  v_result := orgchart

    ( p_rows            => v_rows

    , p_html_element_id => 'the_div'

    )

  ;

  return v_result;

​

end orgchart_test;

​

​

​

end ted_google_visual_orgchart;

​