|
Listing 1: Ajax_MEMORY_TREE Package Body
create or replace package body ajax_memory_tree
as
-------------------------------------------------------------
-- Ajax Memory Tree Example --
-- Scott Spendolini, Sumner Technologies, LLC --
-- http://sumnertech.com --
-- info@sumnertech.com --
-- Thanks to Carl Backstrom for the original Ajax Tree --
-------------------------------------------------------------
-------------------- I N I T
procedure init
is
l_count number;
l_id number;
begin
-- This procedure should only be called one time per session
-- immediately after the user logs in
-- Remove all old session info from tree_temp
delete from tree_temp where app_user = v('APP_USER') and app_session != v('APP_SESSION');
-- Ensure that there is only one top level node
select count(*) into l_count from tree_view where pid is null;
if l_count = 1 then
-- Get the Top Level of the tree
select id into l_id from tree_view where pid is null;
-- Seed the tree_temp table with the top level node
for x in (select * from tree_view where id = l_id)
loop
insert into tree_temp values (null, v('APP_USER'), v('APP_SESSION'), x.pid, x.id, x.name, 'Y', 'plus');
end loop;
-- Set P1_TREE_ROOT to the value of the top node
htmldb_util.set_session_state('P1_TREE_ROOT', l_id);
-- Set the initially selected node to 1 as well
htmldb_util.set_session_state('P1_SELECTED_NODE', l_id);
else
-- The data in the table will not work with this tree
htp.p('<b>ERROR: You have more than 1 row in your table with a NULL Parent ID.</b>');
end if;
end init;
-------------------- P L U S _ M I N U S
procedure plus_minus
is
l_icon varchar2(100);
l_app_user varchar2(255) := v('APP_USER');
l_app_session number := v('APP_SESSION');
l_branch_id number := v('P1_BRANCH_ID');
begin
-- Get and change the icon from + to - or from - to plus, based on what the previous value was
select icon into l_icon from tree_temp
where id = l_branch_id and app_user = l_app_user and app_session = l_app_session;
if l_icon = 'plus' then
update tree_temp set icon = 'minus'
where id = l_branch_id and app_user = l_app_user and app_session = l_app_session;
else
update tree_temp set icon = 'plus'
where id = l_branch_id and app_user = l_app_user and app_session = l_app_session;
end if;
end plus_minus;
-------------------- R E N D E R _ B R A N C H
procedure render_branch
(p_type in varchar2, p_icon in varchar2, p_class in varchar2, p_id in number, p_pid in number, p_name in varchar2)
is
begin
-- If the branch has sub nodes, render it with a +/- sign
if p_type = 'exp' then
htp.prn('<li style="vertical-align:middle;"><img style="cursor:pointer;margin-right:5px;"
src="/i/' || p_icon || '.gif" onClick="getTreeNode(this,''' || p_id || ''')" />
<a class="' || p_class || '" href="f?p=' ||
v('APP_ID') || ':1:' || v('APP_SESSION') || '::::P1_SELECTED_NODE:' || p_id || '">' || p_name ||
'</a></span></li>');
-- Otherwise, just render the item
else
htp.prn('<li style="vertical-align:middle;margin-left:5px;"><img src="/i/wwv_bullet.gif" style="padding-right:5px;"/><a
class="' || p_class || '" href="f?p=' || v('APP_ID') || ':1:' || v('APP_SESSION') ||
'::::P1_SELECTED_NODE:' || p_id || '">' || p_name || '</a></span></li>');
end if;
end render_branch;
-------------------- R E N D E R
procedure render
is
l_level number := 1;
l_class varchar2(2000);
begin
-- Render the Tree
htp.prn('<ul id="treeTrunk" style="list-style:none;">');
for x in
(select pid, id, name, exp, icon, level from
(select * from tree_temp where app_user = v('APP_USER'))
connect by prior id = pid
start with id = v('P1_TREE_ROOT'))
loop
-- Determine if the node being rendered is the Selected Node
if v('P1_SELECTED_NODE') = x.id then
l_class := 'selected_node';
else
l_class := 'unselected_node';
end if;
-- If this node is a new level, then start a new UL
if x.level > l_level then
htp.prn('<ul id="branch' || x.pid || '" style="list-style:none;">');
elsif x.level < l_level then
-- Otherwise, close the existing UL, accounting for multiple skipped levels
for y in 1..(l_level - x.level)
loop
htp.prn('</ul>');
end loop;
end if;
-- If this node has children, then print out the + or - control
if x.exp = 'Y' or x.pid is null then
render_branch('exp', x.icon, l_class, x.id, x.pid, x.name);
else
-- Otherwise, just print the node name
render_branch('no', x.icon, l_class, x.id, x.pid, x.name);
end if;
l_level := x.level;
end loop;
htp.prn('</ul>');
end render;
-------------------- F I N D _ N O D E
procedure find_node (p_id in varchar2)
is
l_app_user varchar2(255) := v('APP_USER');
l_exp char(1);
l_icon varchar2(10);
l_count number;
l_fy varchar2(10) := v('P1_FY');
cursor tree_cur is
select pid, id, name
from tree_view
group by pid, id, name
connect by prior pid = id
start with id = p_id;
tree_rec tree_cur%rowtype;
begin
-- Clear out the tree entries for a user and see it with the initial node
delete from tree_temp where app_user = l_app_user;
for x in (select * from tree_view where pid is null)
loop
insert into tree_temp values (null, l_app_user, v('APP_SESSION'), x.pid, x.id, x.name, 'Y', 'minus');
end loop;
-- Loop through the cursor and build the tree for the selected node
open tree_cur;
loop
fetch tree_cur into tree_rec;
exit when tree_cur%notfound;
for y in (select pid, id, name
from tree_view
where pid = tree_rec.pid)
loop
select count(*) into l_count from tree_view where pid = y.id;
-- Determine whether or not a node has leaves, and if so, assign the appropriate icon
if l_count > 0 then
l_exp := 'Y';
if tree_rec.id = y.id and tree_rec.id != v('P1_SELECTED_NODE') then
-- This node should be expanded
l_icon := 'minus';
else
-- This node should not be expanded, yet has leaves
l_icon := 'plus';
end if;
else
-- This node is an end-leaf
l_exp := 'N';
l_icon := null;
end if;
insert into tree_temp values(null, v('APP_USER'), v('APP_SESSION'), y.pid, y.id, y.name, l_exp, l_icon);
l_icon := null;
end loop;
end loop;
close tree_cur;
end find_node;
-------------------- C O N T R A C T
procedure contract
is
begin
-- If the user clicks the "-" icon, delete any nodes at or below that level from c_tfsms_nm_all_nodes
plus_minus();
for x in
(select * from tree_temp
Where app_user = v('APP_USER')
connect by pid = prior id
start with pid = v('P1_BRANCH_ID'))
loop
delete from tree_temp where temp_id = x.temp_id;
end loop;
end contract;
-------------------- E X P A N D
procedure expand
is
begin
-- If the user clicks the "+" icon, add any nodes at that level to c_tfsms_nm_all_nodes
plus_minus();
for x in (select a.pid, a.id, a.name,
(select count(*) from tree_view b where
b.pid = a.id) branch_count
from tree_view a
where a.pid = v('P1_BRANCH_ID'))
loop
if(x.branch_count > 0) then
-- There are sub-nodes - render with a +/- icon
insert into tree_temp values(null, v('APP_USER'), v('APP_SESSION'), x.pid, x.id, x.name, 'Y', 'plus');
else
-- This is an end-node - no need for a +/- icon
insert into tree_temp values(null, v('APP_USER'), v('APP_SESSION'), x.pid, x.id, x.name, 'N', null);
end if;
end loop;
end expand;
-------------------- P R O C E S S _ C L I C K
procedure process_click
as
l_counter number;
l_class varchar2(2000);
l_icon varchar2(10);
l_branch_id number := v('P1_BRANCH_ID');
begin
htp.prn('<ul id="branch'|| l_branch_id ||'" style="list-style:none;">');
for x in (select a.pid, a.id, a.name,
(select count(*) from tree_view b where
b.pid = a.id ) branch_count
from tree_view a
where a.pid = l_branch_id)
loop
if(x.branch_count > 0) then
if v('P1_SELECTED_FD_ID') = x.id then
-- This node is the selected node - set the class accordingly
l_class := 'selected_node';
else
-- This node is NOT the selected node
l_class := 'unselected_node';
end if;
render_branch('exp', 'plus', l_class, x.id, x.pid, x.name);
else
render_branch('no', null, l_class, x.id, x.pid, x.name);
end if;
end loop;
htp.prn('</ul>');
end process_click;
end ajax_memory_tree;
/
|
