Sonntag, 7. März 2010

Refresh template based extjs tree dynamically...

In the last few posts i talked about how to use apex templates to create extjs trees and property grids. In this post i will show you how to refresh these regions dynamically....
Our report statement needs a special result set...
<br />select  dname as CATEGORY<br />      , empno as ID<br />      , ename as NAME<br />      , rank() over (partition by emp.deptno order by empno asc) as ROW_ASC<br />from emp, dept<br />where emp.deptno=dept.deptno<br />

Next thing we need is a template... I called mine "ExtJS_Tree_Report_With_Refresh".... It's basically the same as before....
Row Template 1
<br />{"text":"#CATEGORY#","expanded":true,"leaf":false,"children":[<br />{"text":"#NAME#","id":"#ID#","leaf":true}<br />

Row Template 1 Expression
<br />#ROW_ASC#=1 and #ROWNUM#=1<br />

Row Template 2
<br />]},{"text":"#CATEGORY#","expanded":true,"leaf":false,"children":[<br />{"text":"#NAME#","id":"#ID#","leaf":true}<br />

Row Template 2 Expression
<br />#ROW_ASC#=1 and #ROWNUM#!=1<br />

Row Template 3
<br />,{"text":"#NAME#","id":"#ID#","leaf":true}<br />

Row Template 3 Condition
<br />#ROW_ASC#!=1<br />

Before Rows
<br /><div id="tree-div#REGION_ID#">[<br />

After Rows
<br />]}]</div><br /><script type="text/javascript"><br />  Ext.onReady(function(){<br />  initTreeRegion('#REGION_ID#');<br />  })<br /></script><br />

The function "initTreeRegion" looks like that....
<br />function initTreeRegion(pId) {<br />    var myDiv = $x('tree-div'+pId);<br />    var myData = Ext.util.JSON.decode(myDiv.innerHTML);    <br />        myDiv.innerHTML = '';<br />        new Ext.tree.TreePanel({<br />          renderTo: 'tree-div'+pId,<br />          border:false,<br />          id: 'REGION_'+pId+'_tree',<br />          cls: 'myTreePanel',<br />          margins: '2 2 0 2',<br />          autoScroll: true,<br />          rootVisible: false,<br />          root:{ "text": "rootnode",<br />                 "expanded": true,<br />                 "children": myData <br />                }<br />        });<br />}<br />

It just recreates the extjs tree using the json object generated by refreshing the report region via $a_report()....
You can now easily refresh every tree region like that...
<br />$a_report('9159609248948926','1','100');<br />initTreeRegion('R9159609248948926');<br />

You only need to know the ID of the region...
Have fun...
Post only available in english!

Freitag, 12. Februar 2010

ExtJS Property Grids in APEX...

I found this example for creating an extjs property grid, which made me think how nice it would be, to have this in apex.... so i played around and came up with another report template....
Let's look at the example....

var propsGrid = new Ext.grid.PropertyGrid({
renderTo: 'prop-grid',
width: 300,
autoHeight: true,
source: {
'(name)': 'Properties Grid',
grouping: false,
autoFitColumns: true,
productionQuality: false,
created: new Date(Date.parse('10/15/2006')),
tested: false,
version: 0.01,
borderWidth: 1
},
viewConfig : {
forceFit: true,
scrollOffset: 2 // the grid will never have scrollbars
}
});

As you can see, what we basically have to do is to create the source part.... pretty much usual value pairs....
So, i created a new report template of type "Generic Columns (column template)" ... I called it ExtJS_PropertyGrid_Report...
Before Rows

<div id="prop-grid#REGION_STATIC_ID#"></div>
<script type="text/javascript">
Ext.override(Ext.grid.PropertyGrid, {
removeProperty: function(property){
delete this.source[property];
var r = this.store.getById(property);
if(r){
// remove property
this.store.remove(r);
}
}
});

function initRegion#REGION_STATIC_ID#() {
var propsGrid = new Ext.grid.PropertyGrid({
applyTo: 'prop-grid#REGION_STATIC_ID#',
id: 'myPropertyGrid#REGION_STATIC_ID#',
width: 300,
autoHeight: true,
source: {

I copied more or less the example from above, added #REGION_STATIC_ID# to make the names and ids unique.... Then i just add a value pair for every column in the report.... (Reports, that use this template should only have one row!!!)

Column Template 1

'#COLUMN_HEADER#':'#COLUMN_VALUE#',

I have a comma to much, so i add a dummy entry, which i instantly remove when the grid is created.....
After Rows

'MYDUMMY123':''},
viewConfig : {
forceFit: true,
scrollOffset: 2 // the grid will never have scrollbars
}
});
propsGrid.removeProperty('MYDUMMY123'); // remove the dummy entry
}

Ext.onReady(function(){
initRegion#REGION_STATIC_ID#();
});
</script>

I put the whole magic inside a function, so i can call it again, wenn i refresh the report via $a_report()....
Now we can create a report region with any select, that generates a one row result set.... the column names have to be unique....
For example:

select '01.06.1980' as "Birthday"
, 'Anja' as "Firstname"
, 'Hildebrandt' as "Name"
, 'Developer' as "Profession"
from dual

Select your new created template...
The result shoul look something like this....

That's it....
Have fun...
Anja


Dienstag, 9. Februar 2010

ExtJS tree via Apex report template....


This morning i needed to show some data in a simple ExtJS tree... Just a view categories with a few children each.... I could have created the needed json string with the pl/sql function i described in an earlier post, but i finally decided to build a little report template to use for this easy stuff...
That's what i came up with....

Create a new report template of type "Named Column (row template)"... I called mine "ExtJS_Tree_Report"...

Define the Row Templates as follows...

Row Template 1
<br />{  "text"     : "#CATEGORY#"<br /> , "expanded" : true<br /> , "leaf"     : false<br /> , "children" : [<br />                  {  "text" : "#NAME#"<br />                   , "id"   : "#ID#"<br />                   , "leaf" : true<br />                  }<br />

Use Based on PL/SQL Expression:
<br />#ROW_ASC#=1 and #ROWNUM#=1<br />

Row Template 2
<br />]},{  "text"     : "#CATEGORY#"<br />    , "expanded" : true<br />    , "leaf"     : false<br />    , "children" : [<br />                    {  "text" : "#NAME#"<br />                     , "id"   : "#ID#"<br />                     , "leaf" : true<br />                    }<br />

Use Based on PL/SQL Expression:
<br />#ROW_ASC#=1 and #ROWNUM#!=1<br />

Row Template 3
<br />,{  "text" : "#NAME#"<br />  , "id"   : "#ID#"<br />  , "leaf" : true<br /> }<br />

Use Based on PL/SQL Expression
<br />#ROW_ASC#!=1<br />


Before Rows
<br /><div id="tree-div#REGION_STATIC_ID#"></div><br /><script type="text/javascript"><br />Ext.onReady(function(){<br />  new Ext.tree.TreePanel({<br />            renderTo    : 'tree-div#REGION_STATIC_ID#',<br />            border      : false,<br />            id          : 'tree-panel',<br />            margins     : '2 2 0 2',<br />            autoScroll  : true,<br />            rootVisible : false,<br />            root:{ <br />                 "text"     : "rootnode",<br />                 "expanded" : true,<br />                 "children" : [<br />


After Rows
<br />     ]} <br />   ]}<br />  }); // new Ext.tree.TreePanel...  <br />}); //Ext.onReady...<br /><br /></script><br />


Now, create a new report region in your page with the following statement...
<br />select  dname as CATEGORY<br />      , empno as ID<br />      , ename as NAME<br />      , rank() over (partition by emp.deptno order by empno asc) as ROW_ASC<br />from emp, dept<br />where emp.deptno=dept.deptno<br />

These are the minimum columns my report template needs...
In the report attributes select your created report template and disable pagination...
That's it... Your result should look like this....






Montag, 8. Februar 2010

APEX navigation bar in sexy extJS toolbar...

It's been a while ;).... But i have a lot of new APEX and ExtJS stuff to blog about. I've been working on a complete extJs based template for my latest APEX project... So here is my first part of the upcoming series.... It's about how to get the usual apex navigation bar to show up in a sexy extJS toolbar...
There are 3 options i always have in my toolbar - logout, help, home... I usually use image buttons, so my definition looks something like this...


And in more detail....

Now we have to build up a little JSON object via navigation bar template.... This is what i put in my page template....

navigation bar
[{
xtype: 'box',
el: 'my_reg_pos_08'
},'->'#BAR_BODY#]
navigation bar entry
,{
xtype: 'box',
autoEl: {
tag: 'a',
href: '#LINK#',
cn: '<img src="#IMAGE#" alt="#TEXT#" />',
title : '#TEXT#'
}
},{xtype: 'tbspacer', width: 10}
As you can see, I build the complete json string to define my toolbar items.... I also include one of my region-position-divs (defined in the body below), because i need a bit of form-magic inside my toolbar....
The body looks something like this...

body

<div id="my_nav_bar"></div>
<div id="myTabs"></div>
<div id="my_messages">#GLOBAL_NOTIFICATION##SUCCESS_MESSAGE##NOTIFICATION_MESSAGE#</div>
<div id="my_box_body" width="100%">#BOX_BODY#</div>
<div id="my_reg_pos_01">#REGION_POSITION_01#</div>
<div id="my_reg_pos_02">#REGION_POSITION_02#</div>
<div id="my_reg_pos_03">#REGION_POSITION_03#</div>
<div id="my_reg_pos_04">#REGION_POSITION_04#</div>
<div id="my_reg_pos_05">#REGION_POSITION_05#</div>
<div id="my_reg_pos_06">#REGION_POSITION_06#</div>
<div id="my_reg_pos_07">#REGION_POSITION_07#</div>
<div id="my_reg_pos_08">#REGION_POSITION_08#</div>

<script type="text/javascript">
Ext.onReady(function(){
var nb = #NAVIGATION_BAR#;
var tb = new Ext.Toolbar({renderTo: 'my_nav_bar', items: nb});

var myform = new Ext.ux.FormViewport({
layout:'border',
items:[new Ext.Panel({
region:'north',
id: 'my_toolbar',
height: 40,
collapsible: false,
margins:'0 0 0 0',
allowDomMove:false,
tbar: tb
}),new Ext.Panel({
region:'south',
id: 'footer',
height: 22,
collapsible: false,
margins:'0 0 0 0',
html:'<div id="DeveloperToolbar"></div>'
}),
new Ext.Panel({
region:'west',
id:'my_info',
title:'Info',
split:true,
width: 220,
minSize: 175,
maxSize: 400,
collapsible: true,
margins:'40 0 5 5',
cmargins:'40 0 5 5',
contentEl: 'my_reg_pos_01',
layoutConfig:{
animate:true
}
}),new Ext.Panel({region:'center',
margins:'40 5 5 5',
id: 'my_workarea',
items: [tabs],
layout : "fit"
})
]
});
});
</script>
I don't use any usual html table layout. I only define a bunch of div regions according to the region positions, messages, navigation bar etc...
In the onReady-function i use the #NAVIGATION_BAR# substitution string to put my created json string inside a variable.
var nb = #NAVIGATION_BAR#;
Then i use nb as item parameter for my toolbar object ...
var tb = new Ext.Toolbar({renderTo: 'my_nav_bar', items: nb});
After that i am able to use tb in my actual page layout.... If everything works, it shoul look something like this....



In case your wondering... I used the customized viewport from Mark Lancaster to make sure, that my elements stay inside my apex form...
To get my developer toolbar inside the footer i used another tip from Mark, which you find here...






Freitag, 20. November 2009

Regions as tabs using ExtJS... (Regionen als Tabs anzeigen mit ExtJS)

Title: Regions as tabs using ExtJS...
I'm working on an ExtJS-Theme right now. One of the first features i wanted to integrate was to show regions as tabs, but i wanted it to be defined only by the position of the region and its template. To do that i created the following region template.
Ich arbeite momentan an einem ExtJS-Theme. Eine der ersten Funktionalitäten, die ich hierbei vorgesehen habe, ist das anzeigen von Regionen als Tabs. Dabei möchte ich keine großartigen Einstellungen in den einzelnen Regionen selbst vornehmen, sondern nur über die Positionierung definieren, welche Regionen als Tab angezeigt werden. Hierfür hab ich zunächst ein Regionstemplate wie folgt angelegt.
The attribute class=”x-tab” marks the regions that should be displayed as tabs. The title attribute defines the name of the tab (title=”#TITLE#”). Now we have to change a few things in our page template. For my little example i want all regions in the BOX_BODY area to be displayed as tabs, so i place the #BOX_BODY# inside a div-tag.
Das Attribut class=”x-tab” kennzeichnet die umzuwandelnden Regionen. Als Tabbezeichnung wird der Titel der Region angezeigt (title=”#TITLE#”). Als nächstes sind ein paar Änderungen am Seitentemplate notwendig. Für mein kleines Beispiel möchte ich, das alle Regionen im BOX_BODY als Tabs angezeigt werden. Dazu schließe ich diesen in eine benannte DIV-Region ein.
#BOX_BODY#

Next thing to do is to put the following javascript inside the page-template too. It uses the autoTabs-Option of ExtJS, which creates tabs based on existing markup.

Nun nutze ich die autoTabs-Option von ExtJS, um die vorhandenen Regionen im BOX_BODY automatisch in Tabs umzuwandeln. Dazu folgendes Skript ebenfalls ins Seitentemplate kopieren.



In this script two parameters are important. First one is the “autoTabs:true”, which triggers the ExtJS feature we're using to replace our normal regions with tab-regions. Second one is “applyTo: ‘box_body’”, which defines the div-region this feature should be used on. Now we are ready to display regions as tabs by setting our created region template and positioning the region inside the BOX_BODY.
Wichtig hierbei sind die Parameter “autoTabs:true” und “applyTo: ‘box_body’”, die angeben auf welchen Bereich die autoTab-Funktion angewendet werden soll. Nun genügt es beim Erstellen einer neuen Region das neue Regionstemplate anzugeben und die Region im BOX_BODY zu platzieren.


image

The result should look something like this.
Das Ergebnis sollte etwa so aussehen.


image

By the way, the usual conditions to show or hide the regions still work.
Übrigens funktioniert das bedingte ein- und ausblenden der Regionen immer noch.

Freitag, 2. Oktober 2009

Nice trees with ExtJS (Erstellung eines ExtJS-Baums…)

image Bekannterweise ist die in APEX standardmäßig verfügbare Baumstruktur suboptimal und nicht wirklich sexy. Abhilfe kann hier die ExtJS-Bibliothek schaffen, die eine einfach zu definierende Baumstruktur anbietet. Links ist ein Beispiel einer solchen sexy Baumstruktur…
In diesem Beitrag möcht ich kurz zeigen, wie sich ein solcher Baum einfach in eine APEX-Anwendung einbinden und mit Daten aus der Datenbank füttern lässt.
Meistens wird beim Austausch von Standardelementen der Weg bevorzugt, das Element erst von APEX laden zu lassen und es dann entsprechend zu verändern. Ich denke bei einem Baum ist das nicht unbedingt notwendig, so das ich diesen von Grund auf durch ExtJS generieren werde.

Zunächst mal ein paar Worte zu ExtJS. ExtJS ist ein clientseitiges Javascript bzw. AJAX-Framework, das browserunabhängige Funktionalitäten für die DOM-Manipulation, Event-Handling oder auch asynchrone Serverkommunikation zur Verfügung stellt. Die API von ExtJS ist leicht erlernbar und wirklich intuitiv nutzbar, da sie fast vollständig JSON gestützt ist.

Für das folgende Beispiel wurde eine APEX-Anwendung mit einer Seite erstellt. Innerhalb dieser Seite gibt es eine einfache Region, die als Inhalt eine DIV-Region mit der Id “tree” definiert.

Folgende Stylesheets und Javascript-Dateien aus dem ExtJS-Package sollten im Headerbereich eingebunden sein.

<link rel="stylesheet" type="text/css" href="#IMAGE_PREFIX#javascript/ext-2.2.1/resources/css/ext-all.css" />

<script type="text/javascript" src="#IMAGE_PREFIX#javascript/ext-2.2.1/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="#IMAGE_PREFIX#javascript/ext-2.2.1/ext-all.js"></script>


Zusätzlich wurde folgende JS-Funktion im Footer der Seite hinterlegt.


    Ext.onReady(function(){
new Ext.Panel({
title: 'My first tree',
renderTo: 'tree',
layout: 'border',
width: 500,
height: 500,
items: [{
xtype: 'treepanel',
id: 'tree-panel',
region: 'center',
margins: '2 2 0 2',
autoScroll: true,
rootVisible: false,
root: { text: "rootnode",
expanded: true,
children: [{ text:"node 1",
id:1,
leaf:true
},
{ text: "node 2",
id: 2,
leaf:false,
expanded: true,
children:[{ text:"node 1",
id:3,
leaf:true
}
]
}
]
},

}]
});
});

Was passiert hier? Ganz einfach…

Wenn die Seite vollständig geladen ist, wird die onReady-Funktion ausgeführt. Innerhalb dieser Funktion wird ein neues ExtJS-Panel mit dem Titel “My first tree” generiert. Die Option renderTo gibt an, in welchem Bereich der Seite das neu erstellte Panel eingefügt wird. In diesem Beispiel die in unserer Seite definierte DIV-Region.

Innerhalb dieses Panels wird ein TreePanel generiert, was einen nicht sichtbaren (rootVisible:false) Root-Knoten bekommt, der zwei Unterknoten (children) “node 1” und “node 2” enthält. “node 2” wiederum hat einen weiteren Unterknoten.


Die entscheidenden Werte zur Definition einen Knoten sind:

text: Der Text der für den betreffenden Knoten im Baum angezeigt werden soll.

id: Eine eindeutige Bezeichnung für den Knoten, z.B. die tatsächliche numerische ID einer Kategorie oder ähnliches.

leaf: Diese Option gibt an, ob der betreffende Knoten ein Blatt ist oder ob er weiter Unterknoten enthält.

children: Ist ein Array der Unterknoten, sofern vorhanden.

href: Muss gesetzt werden, falls der Knoten als Link funktionieren soll.

Nun hat man natürlich eher selten eine feste Baumstruktur abzubilden. Normalerweise wollen wir eine dynamische Struktur aus der Datenbank laden.

Hierzu erstellen wir eine Datenbank-Funktion, die das JSON-Objekt als Text zurückgibt. Da es relativ mühselig wäre die dafür benötigte Struktur zusammenzubauen, bin ich mal auf die Suche nach einer fertigen Lösung gegangen. Und siehe da, es gibt bereits ein JSON-Datentyp für Oracle. Vielen Dank an Lewis Cunningham dafür.

Ok, bereiten wir also erstmal ein paar Testdaten und die benötigte Funktion auf der Datenbank vor.


create table tbl_mon_category (cat_id number, cat_par_id number, cat_label varchar2(100));

insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (1,0,'Node 1');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (2,0,'Node 2');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (3,1,'Childnode A for Node 1');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (4,2,'Childnode A for Node 2');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (5,2,'Childnode B for Node 2');
insert into tbl_mon_category (cat_id,cat_par_id,cat_label) values (6,2,'Childnode C for Node 2');
commit;

create or replace package PCK_EXTJS_JSON_EXAMPLE is

-- Author : AHILDEBRANDT
-- Created : 10.08.2009

-- Purpose : Prüfen, ob eine Kategorie und Unterkategorien hat
function hasChildren(i_cat_id in number) return boolean;

-- Purpose : anhand einer übergebenen Kategorie-ID das
-- vollständige JSON-Objekt generieren (nutzt
-- Datentyp PL/JSON; arbeitet rekursiv!)
function getJsonObject(i_cat_id in number default 0) return json;

-- Purpose : Aufruf der Funktion zum Generieren des
-- JSON-Objekts und Rückgabe des Ergebnisses
-- als String
function getTreeDataDynamic return varchar2;

end PCK_EXTJS_JSON_EXAMPLE;
/
create or replace package body PCK_EXTJS_JSON_EXAMPLE is
/*
-- Author : AHILDEBRANDT
-- Created : 10.08.2009
-- Purpose : Prüfen, ob eine Kategorie und Unterkategorien hat
*/
function hasChildren(i_cat_id in number) return boolean is
v_count number:=0;
begin
select nvl(count(*),0) into v_count from tbl_mon_category where cat_par_id=i_cat_id;
if v_count>0 then
return true;
else
return false;
end if;
end;

/*
-- Author : AHILDEBRANDT
-- Created : 10.08.2009
-- Purpose : anhand einer übergebenen Kategorie-ID das vollständige JSON-Objekt generieren
-- (nutzt Datentyp PL/JSON)
-- ACHTUNG! REKURSIV!
*/
function getJsonObject(i_cat_id in number default 0) return json is
v_json json:=json();
v_arr_id NUMBER;
v_ele_id NUMBER;
begin
if i_cat_id = 0 then -- wenn Kategorie 0 dann wird das der Root-Knoten mit festen Werten
v_json.add_member(p_name => 'id', p_value => i_cat_id);
v_json.add_member(p_name => 'text', p_value => 'root');
else -- sonst regulärer Knoten; Werte für die Kategorie benutzen
for cat in (select * from tbl_mon_category where cat_id=i_cat_id) loop
v_json.add_member(p_name => 'id', p_value => i_cat_id);
v_json.add_member(p_name => 'text', p_value => cat.cat_label);
end loop;
end if;

if not hasChildren(i_cat_id) then -- wenn Knoten keine Child-Knoten hat
v_json.add_member(p_name => 'leaf', p_value => true); -- als Blatt markieren und fertig
v_json.add_member(p_name => 'href', p_value => 'f?p=108:2:'||v('APP_SESSION')); -- einen Link setzen
else -- sonst
v_json.add_member(p_name => 'leaf', p_value => false); -- als Knoten mit Unterknoten kennzeichnen
v_json.add_array(p_name => 'children', p_array_id => v_arr_id ); -- Array für Unterknoten hinzufügen
for child in (select * from tbl_mon_category where cat_par_id=i_cat_id) loop -- Schleife über alle Unterkategorien
-- Unterknoten über rekursiven Aufruf generieren und ins Array hängen
v_json.add_array_element(p_array_id => v_arr_id, p_value => getJsonObject(i_cat_id => child.cat_id), p_element_id => v_ele_id);
end loop;
end if;

return v_json;
end;

/*
-- Author : AHILDEBRANDT
-- Created : 10.08.2009
-- Purpose : Aufruf der Funktion zum Generieren des JSON-Objekts und Rückgabe des Ergebnisses als String
*/
function getTreeDataDynamic return varchar2 is
v_json json:=json();
begin
v_json:=getJsonObject(i_cat_id => 0);
return(v_json.getString);
end;

end PCK_EXTJS_JSON_EXAMPLE;


Als nächstes benötigen wir einen bedarfsgesteuerten Anwendungsprozess "getTreeData", der die Package-Funktion aufruft.
declare

v_json varchar2(32767);

begin

v_json:=PCK_EXTJS_JSON_EXAMPLE.getTreeDataDynamic;

htp.prn(v_json);

end;


Als letzten Schritt muss nun noch die vorhandene Javascript-Funktion angepasst werden.
Ext.onReady(
function(){

var TreeRequest = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=getTreeData',0);
var TreeResponse = TreeRequest.get();
if (TreeResponse) {
var v_extjs_tree_data= Ext.util.JSON.decode(TreeResponse);
}

new Ext.Panel({
title: 'My first tree',
renderTo: 'tree',
layout: 'border',
width: 500,
height: 500,
items: [{
xtype: 'treepanel',
id: 'tree-panel',
region: 'center',
margins: '2 2 0 2',
autoScroll: true,
rootVisible: false,
root: v_extjs_tree_data,
}]
});
}
);


Das Ergebnis sollte in etwa wie folgt aussehen.

image

Und das war’s auch schon….


Title: How to create an ExtJS-Tree...
image
You probably all now, that the apex trees aren't really sexy. Therefore i decided to give ExtJS a shot. On the left you can see a tree build with ExtJS. Nice, isn't it?
This post shows how you can integrate an ExtJS tree like this in your apex application.


Let's start with a few words about ExtJS. ExtJS is a clientside javascript and ajax framework, that provides browser independent functionality for DOM-manipulation, event-handling or asynchronous server communication. The api is really easy to learn and to use, because it is completely based on JSON.

For the example in this post i created an application with one page. This page contains a region in which a div-region with id "tree" is defined.

The following stylesheets and javascript-files have to be included in the header of the page.
<link rel="stylesheet" type="text/css" href="#IMAGE_PREFIX#javascript/ext-2.2.1/resources/css/ext-all.css" />

<script type="text/javascript" src="#IMAGE_PREFIX#javascript/ext-2.2.1/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="#IMAGE_PREFIX#javascript/ext-2.2.1/ext-all.js"></script>


Now we put the following function in the page footer.
    Ext.onReady(function(){
new Ext.Panel({
title: 'My first tree',
renderTo: 'tree',
layout: 'border',
width: 500,
height: 500,
items: [{
xtype: 'treepanel',
id: 'tree-panel',
region: 'center',
margins: '2 2 0 2',
autoScroll: true,
rootVisible: false,
root: { text: "rootnode",
expanded: true,
children: [{ text:"node 1",
id:1,
leaf:true
},
{ text: "node 2",
id: 2,
leaf:false,
expanded: true,
children:[{ text:"node 1",
id:3,
leaf:true
}
]
}
]
},

}]
});
});


After the page is completely loaded the onReady-Function creates an ExtJS-Panel with the title "My first tree", which is rendered to our defined div-region.

Inside this panel the treepanel is created with an invisible root-node and two childnodes "node 1" and "node 2". "node 2" has another childnode.

The most important options to define a node are:

text: text to be displayed in the tree

id: id for the node, which should be unique in the tree

leaf: this option shows wether the node is a leaf or not

children: an array of childnodes if there are any

href: has to be set for the node to work as a link (line 68 next scriptblock)



Usually we need to load dynamic data from the database.

To do that, we will create a database function, that gives us a generated JSON-Object as a string.

After a bit of research i found that Lewis Cunningham already build a JSON-Datatype for Oracle. Thanks for that, because that makes our task really easy.

The following script creates a table, some test data and a package with needed functions.
create table tbl_category (cat_id number, cat_par_id number, cat_label varchar2(100));

insert into tbl_category (cat_id,cat_par_id,cat_label) values (1,0,'Node 1');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (2,0,'Node 2');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (3,1,'Childnode A for Node 1');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (4,2,'Childnode A for Node 2');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (5,2,'Childnode B for Node 2');
insert into tbl_category (cat_id,cat_par_id,cat_label) values (6,2,'Childnode C for Node 2');
commit;

create or replace package PCK_EXTJS_JSON_EXAMPLE is

-- Author : AHILDEBRANDT
-- Created : 10.08.2009

-- Purpose : check if category has sub-categories
function hasChildren(i_cat_id in number) return boolean;

-- Purpose : create JSON-Object for category;
-- recursive<br/> function getJsonObject(i_cat_id in number default 0) return json;

-- Purpose : function that calls getJsonObject and returns the result as string
function getTreeDataDynamic return varchar2;

end PCK_EXTJS_JSON_EXAMPLE;
/
create or replace package body PCK_EXTJS_JSON_EXAMPLE is
/*
-- Author : AHILDEBRANDT
-- Created : 10.08.2009
-- Purpose : check if category has sub-categories
*/
function hasChildren(i_cat_id in number) return boolean is
v_count number:=0;
begin
select nvl(count(*),0) into v_count from tbl_category where cat_par_id=i_cat_id;
if v_count>0 then
return true;
else
return false;
end if;
end;

/*
-- Author : AHILDEBRANDT
-- Created : 10.08.2009
-- Purpose : create JSON-Object for category;
-- recursive
*/
function getJsonObject(i_cat_id in number default 0) return json is<br/> v_json json:=json();
v_arr_id NUMBER;
v_ele_id NUMBER;
begin
if i_cat_id = 0 then -- if category is root then set root-node
v_json.add_member(p_name => 'id', p_value => i_cat_id);
v_json.add_member(p_name => 'text', p_value => 'root');
else -- else --> usual node; use category infos
for cat in (select * from tbl_category where cat_id=i_cat_id) loop
v_json.add_member(p_name => 'id', p_value => i_cat_id);
v_json.add_member(p_name => 'text', p_value => cat.cat_label);
end loop;
end if;

if not hasChildren(i_cat_id) then -- if node has no children
v_json.add_member(p_name => 'leaf', p_value => true); -- mark as leaf
v_json.add_member(p_name => 'href', p_value => 'f?p=108:2:'||v('APP_SESSION')); -- set a link if needed
else -- sonst
v_json.add_member(p_name => 'leaf', p_value => false); -- mark as node with children
v_json.add_array(p_name => 'children', p_array_id => v_arr_id ); -- add array for subcategories
for child in (select * from tbl_category where cat_par_id=i_cat_id) loop -- loop through all sub-categories
-- create JSON-object for sub-category using recursive call and the append to array
v_json.add_array_element(p_array_id => v_arr_id, p_value => getJsonObject(i_cat_id => child.cat_id), p_element_id => v_ele_id);
end loop;
end if;

return v_json;
end;

/*
-- Author : AHILDEBRANDT
-- Created : 10.08.2009
-- Purpose : function that calls getJsonObject and returns the result as string
*/
function getTreeDataDynamic return varchar2 is
v_json json:=json();
begin
v_json:=getJsonObject(i_cat_id => 0);
return(v_json.getString);
end;

end PCK_EXTJS_JSON_EXAMPLE;
/


Next thing we need is an on-demand application process "getTreeData", that will call our package-function.
declare

v_json varchar2(32767);

begin

v_json:=PCK_EXTJS_JSON_EXAMPLE.getTreeDataDynamic;

htp.prn(v_json);

end;


Only thing left to do is changing our javascript-function...
Ext.onReady(
function(){

var TreeRequest = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=getTreeData',0);
var TreeResponse = TreeRequest.get();
if (TreeResponse) {
var v_extjs_tree_data= Ext.util.JSON.decode(TreeResponse);
}

new Ext.Panel({
title: 'My first tree',
renderTo: 'tree',
layout: 'border',
width: 500,
height: 500,
items: [{
xtype: 'treepanel',
id: 'tree-panel',
region: 'center',
margins: '2 2 0 2',
autoScroll: true,
rootVisible: false,
root: v_extjs_tree_data,
}]
});
}
);


The result should look like this...

image

And that's it....




Dienstag, 30. Juni 2009

Apex Developer Competition 2009

Just got news, that there will be an apex competition this year...

http://www.oracle.com/technology/products/database/application_express/html/competition.html

You can win tickets for this years Oracle Open World, so better go now and submit your application ;) ...

Good luck...

Anja

Habe gerade einen Link zum diesjährigen internationalen APEX Wettbewerb bekommen.

http://www.oracle.com/technology/products/database/application_express/html/competition.html

Es gibt Tickets für die Open World zu gewinnen, also am besten gleich registrieren und Applikation hochladen…

Viel Erfolg

Anja