Overblog
Folge diesem Blog Administration + Create my blog
Neueste Posts

jrXml to PDF - a declarative, pure PL/SQL reporting engine

16. April 2012 , Geschrieben von Andreas Weiden Veröffentlicht in #APEX

The task

I'm working on APEX since half a year now and i really like it. I also think its quite "complete" regarding its functionality, besides... well, besides the reporting possibilities.

There are several available solutions, but either they require additional "middleware" (as BI Publisher or Apache FOP), or they are database driven, but not declarative (like PL/PDF or AS_PDF3 by Anton Scheffer).

I don't want that "middleware"-overhead, so i somehow have to deal with the database-driven pdf-generation as a starting point. But what i want is a graphical editor to define my report and then run and render it inside the database.

And there are graphical editors which are very powerful and easy to use, my favorite one is iReport for JasperReports.

The approach

So my approach is quite easy. I want to design my report in a graphical way, then transfer the resulting report-definition to the database, and then render the report using a PL/SQL-package to PDF.And of course i do not want to reinvent the wheel but use already existing code, if possible.

  • There already is a tool i would like to use to design my report, that's iReport.
  • There already is a tool which is capable of creating PDF-files from inside the database, the one i choose here is AS_PDF3 from Anton Scheffler ( i like it for its simplicity).

There is only one part missing, that's the "thing in the middle" which is capable of reading my report-definition and translate the single pieces to statement-call's to the procedures inside the AS_PDF3-package.

The good news is that reading the report-definition is quite simple, as iReport stores the definition as xml-file.

Here's a simple Report-definition which just renders a thin blue line.

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="simple" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
    <queryString language="SQL">
        <![CDATA[SELECt 1 X FROM DUAL]]>
    </queryString>
    <field name="X" class="java.math.BigDecimal"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <line>
                <reportElement x="202" y="38" width="100" height="1" forecolor="#0000CC"/>
            </line>
        </band>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band splitType="Stretch"/>
    </detail>
    <columnFooter>
        <band splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band splitType="Stretch"/>
    </summary>
</jasperReport>

The result looks as simple.

jrxml2pdf simple

A "real" report in iReport basically exists of

  • a title-region rendered once for the report
  • a pageheader-region rendered ony for each page at the top of the page
  • a columnheader-region rendered once on each page before the data
  • a detail-region rendered once for each record of data
  • a columnheader-region rendered once on each page after the data
  • a pagefooter-region rendered ony for each page at the bottom of the page
  • a summary-region once for the report

each of this region maybe there or maybe not.

Each region consists of graphical objects, like

  • lines with different colors and line-widths
  • rectangles with different colors and line-widths
  • static text, with or without border
  • textfields, which take the data from specific fields of a record from the report-query and show them
  • images, either static or taken from the database (a blob-column)
  • subreports, which can be used to implement master-detail-reports

There is more, but for a first version of my package this is enough to implement.

The implementation

My package consists of three parts

  • the logic for recursively reading the xml-structure of the report-definition.
  • the rendering part which works out the logic of the different objects of the report-definition and calls the render-procedures for the different objects.
  • the render-methods, which "translate" the logical object (e.g. a line) to its physical implementation.

The reading logic

I created a report-table with an CLOB-column in which the reports xml is stored. I concentrated on the, in my opinion, are the most important objects and attributes of the report, details will be added later. I won't show the complete code, as an example here's the code of the procedure reading the main report-definition.

  FUNCTION FK_LOAD_REPORT (i_vcReportName IN VARCHAR2)
  RETURN tReport IS
    CURSOR crReport IS
      SELECT EXTRACTVALUE(XML, '/jasperReport/@pageWidth'   ) PAGE_WIDTH,
             EXTRACTVALUE(XML, '/jasperReport/@pageHeight'  ) PAGE_HEIGHT,
             EXTRACTVALUE(XML, '/jasperReport/@leftMargin'  ) LEFT_MARGIN,
             EXTRACTVALUE(XML, '/jasperReport/@rightMargin' ) RIGHT_MARGIN,
             EXTRACTVALUE(XML, '/jasperReport/@topMargin'   ) TOP_MARGIN,
             EXTRACTVALUE(XML, '/jasperReport/@bottomMargin') BOTTOM_MARGIN,
             EXTRACT     (XML, '/jasperReport/title'        ) TITLE_XML,
             EXTRACT     (XML, '/jasperReport/pageHeader'   ) PAGEHEADER_XML,
             EXTRACT     (XML, '/jasperReport/columnHeader' ) COLUMNHEADER_XML,
             EXTRACT     (XML, '/jasperReport/detail'       ) DETAIL_XML,
             EXTRACT     (XML, '/jasperReport/columnFooter' ) COLUMNFOOTER_XML,
             EXTRACT     (XML, '/jasperReport/pageFooter'   ) PAGEFOOTER_XML,
             EXTRACT     (XML, '/jasperReport/summary'      ) SUMMARY_XML,
             EXTRACTVALUE(XML, '/jasperReport/queryString'  ) QUERY_STRING
        FROM (SELECT XMLTYPE(REPORT_XML) XML
                FROM REPORTS
               WHERE REPORT_NAME =i_vcReportName
             );
    recReport crReport%ROWTYPE;
    rReport tReport;
  BEGIN
    OPEN crReport;
    FETCh crReport INTO recReport;
    IF crReport%FOUND THEN
      rReport.nPageWidth     :=recReport.PAGE_WIDTH;
      rReport.nPageHeight    :=recReport.PAGE_HEIGHT;
      rReport.nLeftMargin    :=recReport.LEFT_MARGIN;
      rReport.nRightMargin   :=recReport.RIGHT_MARGIN;
      rReport.nTopMargin     :=recReport.TOP_MARGIN;
      rReport.nBottomMargin  :=recReport.BOTTOM_MARGIN;
      rReport.xmlTitle       :=recReport.TITLE_XML;
      rReport.xmlPageHeader  :=recReport.PAGEHEADER_XML;
      rReport.xmlColumnHeader:=recReport.COLUMNHEADER_XML;
      rReport.xmlDetail      :=recReport.DETAIL_XML;
      rReport.xmlColumnFooter:=recReport.COLUMNFOOTER_XML;
      rReport.xmlPageFooter  :=recReport.PAGEFOOTER_XML;
      rReport.xmlSummary     :=recReport.SUMMARY_XML;
      rReport.vcQuery        :=recReport.QUERY_STRING;
    END IF;
    CLOSE crReport;
    RETURN rReport;
  END;

For other objects the code looks similar. So its basically translating xml to "flat" record-structures.

The rendering part

This is the most complex part, as i has to deal with all the rules which apply to a report, based on its structure and attributes. As an example here is the main-procedure (shortened)

  PROCEDURE PR_RENDER_REPORT(i_rReport IN tReport) IS

    ..

  BEGIN
    -- Execute the Query and check if there are results
    lResult:=FK_EXECUTE_QUERY(i_rReport.vcQuery, i_rReport.lParams);
   
    IF lResult.COUNT>0 THEn
      -- There are records, start
      PR_INIT_PDF;
   
      PR_SETUP_PAGE(i_nWidth       =>i_rReport.nPageWidth,
                    i_nHeight      =>i_rReport.nPageHeight,
                    i_nLeftMargin  =>i_rReport.nLeftMargin,
                    i_nRightMargin =>i_rReport.nRightMargin,
                    i_nTopMargin   =>i_rReport.nTopMargin,
                    i_nBottomMargin=>i_rReport.nBottomMargin
                   );


      rArea:=FK_RENDER_REGION(i_rReport.xmlTitle, 'title', rArea, lResult, NO_RECORD, TRUE, FALSE, FALSE);
      rArea:=FK_RENDER_REGION(i_rReport.xmlPageHeader, 'pageHeader', rArea, lResult,NO_RECORD, FALSE, FALSE, FALSE);
      rArea:=FK_RENDER_REGION(i_rReport.xmlColumnHeader, 'columnHeader', rArea, lResult, NO_RECORD, TRUE, nPageHeaderheight>0, nPageFooterheight>0);


      FOR i IN 1..lResult.COUNT LOOP
        -- Check if region fits on page
        IF FK_FITS_IN_PAGE(rArea.nY, nDetailHeight, nColumnFooterHeight, nPageFooterheight) THEN
          rArea:=FK_RENDER_REGION(i_rReport.xmlDetail, 'detail', rArea, lResult, i, TRUE, nPageHeaderheight>0, nPageFooterheight>0);
        ELSE
          -- Finish page
          rArea:=FK_RENDER_REGION(i_rReport.xmlColumnFooter, 'columnFooter', rArea, lResult, i,TRUE, nPageHeaderheight>0, nPageFooterheight>0);
          -- psoition to bottom for page-footer
          rArea:=FK_RENDER_REGION(i_rReport.xmlPageFooter, 'pageFooter', rArea, lResult, i, FALSE, FALSE, FALSE);
          PR_NEW_PAGE;
          rArea:=FK_RENDER_REGION(i_rReport.xmlPageHeader, 'pageHeader', rArea, lResult,i, FALSE, FALSE, FALSE);
          rArea:=FK_RENDER_REGION(i_rReport.xmlColumnHeader, 'columnHeader', rArea, lResult, i, TRUE, nPageHeaderheight>0, nPageFooterheight>0);
          rArea:=FK_RENDER_REGION(i_rReport.xmlDetail, 'detail', rArea, lResult, i, TRUE, nPageHeaderheight>0, nPageFooterheight>0);
        END IF; 
      END LOOP;
      rArea:=FK_RENDER_REGION(i_rReport.xmlColumnFooter, 'columnFooter', rArea, lResult, NO_RECORD, TRUE, nPageHeaderheight>0, nPageFooterheight>0);
      rArea:=FK_RENDER_REGION(i_rReport.xmlSummary, 'summary', rArea, lResult, NO_RECORD, TRUE, FALSE, FALSE);

      -- psoition to bottom for page-footer
      rArea.nY:=rPageSetup.nPageHeight-rPageSetup.nBottomMargin-rPageSetup.nTopMargin-nPageFooterheight;
      rArea:=FK_RENDER_REGION(i_rReport.xmlPageFooter, 'pageFooter', rArea, lResult, NO_RECORD, FALSE, FALSE, FALSE);
      PR_FINISH_PDF;
    END IF;
  END;

The render-methods

The render-methods are used to translate a logical object from the report to the implementation of the AS_PDF3-package by Anton Scheffer. Also, here is an example-procedure, this one's for rendering a line.

  PROCEDURE PR_RENDER_LINE(i_nX          IN NUMBER,
                           i_nY          IN NUMBER,
                           i_nWidth      IN NUMBER,
                           i_nHeight     IN NUMBER,
                           i_nLineWidth  IN NUMBER,
                           i_vcLineColor IN VARCHAR2
                          ) IS
  BEGIN
    IF i_nWidth=1 THEN
      -- vertical line
      AS_PDF3.vertical_line(p_x          =>rPageSetup.nLeftMargin+i_nX,
                                p_y          =>rPageSetup.nPageHeight-i_nY-rPageSetup.nTopMargin,
                                p_height     =>-i_nHeight,
                                p_line_width =>i_nLineWidth,
                                p_line_color =>REPLACE(i_vcLineColor, '#', '')
                                );
    ELSIF i_nHeight=1 THEN
      -- horizontal line
      AS_PDF3.horizontal_line(p_x          =>rPageSetup.nLeftMargin+i_nX,
                                  p_y          =>rPageSetup.nPageHeight-rPageSetup.nTopMargin-i_nY,
                                  p_width      =>i_nWidth,
                                  p_line_width =>i_nLineWidth,
                                  p_line_color =>REPLACE(i_vcLineColor, '#', '')
                                );
    END IF;
  END;
Some examples

The following are some examples which can be generated with the current version of my package

Tabular report on DEMO_PRODUCT_INFO with images

jrxml2pdf image

tabular report on EMP built with the wizard using one of iReports predefined template (cherry).

jrxml2pdf cherry layout

Master-detail-report on DEPT and EMP

jrxml2pdf dept emp

 

Whats next

 

The package is currently in a beta-state, i hope to be able to publish a first stable version in maybe a month.

Weiterlesen

A generic search-functionality

10. April 2012 , Geschrieben von Andreas Weiden Veröffentlicht in #APEX

The task

APEX has a nice search-functionality to search all objects in the application by just entering one search-condition. From the result-page of the search you can then directly navigate to the pages where you can edit the found objects. My idea was to create something similar for a self-build APEX-application.

The approach

Not all fields in an application are worth to be searched on. So my approach is to create a table with all "searchable column" in the application along with some additional information.

  • Tablename - The name of the table/view to be searched
  • Columnname - The name of the column to be searched
  • ID-Column1 - The first column of the primary key (used later in the page-url to set Page-Item-Values)
  • ID-Column2 - The first column of the primary key (used later in the page-url to set Page-Item-Values)
  • Object-description - a speaking name for the object represented by the table
  • Column-description - a speaking name of whats inside the column
  • Ident-Column - a column to select the rows identifying column (this is in general a unique key to be shown to the enduser)
  • Page-URL - URL to link to the associated APEX-Page. This must be of the format f?p=&APP_ID.:9001:&SESSION_ID.::NO::IDCOLUMN1,IDCOLUMN2:&IDVALUE1.,&IDVALUE2. The variable in that url (&APP_ID. , &SESSION_ID. , &IDVALUE1. , &IDVALUE2. ) are replaced at runtime with the appropaite value

The table-definition looks like this

 

CREATE TABLE SEARCHABLE_FIELDS (  
ID                  NUMBER(38)    NOT NULL,  
TABLE_NAME          VARCHAR2(30)  NOT NULL,  
COLUMN_NAME         VARCHAR2(30)  NOT NULL,   
IDCOLUMN1           VARCHAR2(30)  NOT NULL,  
IDCOLUMN2           VARCHAR2(30)          ,  
OBJECT_DESCRIPTION  VARCHAR2(255) NOT NULL,  
COLUMN_DESCRIPTION  VARCHAR2(255) NOT NULL,  
IDENT_COLUMN        VARCHAR2(30)  NOT NULL,  
PAGE_URL            VARCHAR2(255) NOT NULL);

 

The implementation - db-side

The search-functionality is implemented as a pipelined function in the db, so i need an object type and a collection.

The columns contain the ones from the search-definition-table along with the "found" data for one record.

CREATE OR REPLACE TYPE CO_APX_SEARCH_RESULT AS OBJECT (
  TABLE_NAME         VARCHAR2(30),
  COLUMN_NAME        VARCHAR2(30),
  OBJECT_DESCRIPTION VARCHAR2(4000),
  COLUMN_DESCRIPTION VARCHAR2(4000),
  IDCOLUMN1          VARCHAR2(30),
  IDCOLUMN2          VARCHAR2(30),
  IDVALUE1           VARCHAR2(4000),
  IDVALUE2           VARCHAR2(4000),
  OBJECT_IDENTIFIER  VARCHAR2(4000),
  SEARCH_IDENTIFIER  VARCHAR2(4000),
  PAGE_URL           VARCHAR2(4000)
);

And, as needed for the pipelined function an additional collection on that object.type

CREATE OR REPLACE TYPE COT_APX_SEARCH_RESULT AS TABLE OF CO_APX_SEARCH_RESULT;

At last, we need a function which does the real searching. The function accepts the search-strings as parameter and returns a collection of search-results to the caller. The searching itself is done by dynamic sql based on the search-definitions in the table SEARCHABLE_FIELDS. Here's the function:

CREATE OR REPLACE FUNCTION FK_SEARCH_APEX(i_vcSearchString IN VARCHAR2)
RETURN COT_APX_SEARCH_RESULT PIPELINED
AUTHID CURRENT_USER IS
  CURSOR crSearch IS
    SELECT TABLE_NAME,
           COLUMN_NAME,
           IDCOLUMN1,
           IDCOLUMN2,
           OBJECT_DESCRIPTION,
           COLUMN_DESCRIPTION,
           IDENT_COLUMN,
           REPLACE(REPLACE(PAGE_URL, '&APP_ID.', V('APP_ID')), '&SESSION_ID.', V('SESSION')) PAGE_URL
      FROM SEARCHABLE_FIELDS;
  TYPE tSearch IS TABLE OF crSearch%ROWTYPE;
 
  lSearch         tSearch;
  lResult         COT_APX_SEARCH_RESULT;
  vcQuery         VARCHAR2(32000);
  vcSearchString  VARCHAR2(32000);
  exAsserted      EXCEPTION;
  PRAGMA EXCEPTION_INIT(exAsserted, -06502);
BEGIN
  IF i_vcSearchString IS NOT NULL THEN
    -- Searchstring is defined, Assert for SQL-injection
    BEGIN
      vcSearchString:=DBMS_ASSERT.ENQUOTE_LITERAL('%' || UPPER(i_vcSearchString) || '%');
    EXCEPTION
      WHEN exAsserted THEN
        vcSearchString:=NULL;
    END;
  END IF;
  IF vcSearchString IS NOT NULL THEN
    -- Searchstring is defined, load all searches
    OPEN crSearch;
    FETCH crSearch
    BULK COLLECT INTO lSearch;
    CLOSE crSearch;
    -- check for results
    IF lSearch.COUNT>0 THEN
      -- now do each search via dynamic sql
      FOR i IN 1..lSearch.COUNT LOOP
        vcQuery:='select CO_APX_SEARCH_RESULT('''  || lSearch(i).TABLE_NAME                || ''',''' ||
                                                      lSearch(i).COLUMN_NAME               || ''',''' ||
                                                      lSearch(i).OBJECT_DESCRIPTION        || ''',''' ||
                                                      lSearch(i).COLUMN_DESCRIPTION        || ''',''' ||
                                                      lSearch(i).IDCOLUMN1                 || ''','''  ||
                                                      lSearch(i).IDCOLUMN2                 || ''','  ||
                                                      lSearch(i).IDCOLUMN1                 || ',' ||
                                                      NVL(lSearch(i).IDCOLUMN2, 'NULL')    || ',' ||
                                                      lSearch(i).IDENT_COLUMN              || ',' ||
                                                      lSearch(i).COLUMN_NAME               || ',''' ||
                                                      lSearch(i).PAGE_URL                  || ''')' ||
                  '  FROM ' || lSearch(i).table_name         ||
                  ' WHERE UPPER(' || lSearch(i).column_name || ') LIKE ' || vcSearchString;
        EXECUTE IMMEDIATE vcQuery
        BULK COLLECT INTO lResult;
        FOR j IN 1..lResult.COUNT LOOP
          -- Replace ID-Values in url
          lResult(j).PAGE_URL:=REPLACE(lResult(j).PAGE_URL, '&IDVALUE1.', lResult(j).IDVALUE1);
          lResult(j).PAGE_URL:=REPLACE(lResult(j).PAGE_URL, '&IDVALUE2.', lResult(j).IDVALUE2);
          -- Pipe the result
          PIPE ROW (lResult(j));
        END LOOP;
      END LOOP;
    END IF;
  END IF;
  RETURN;
END;

 

If you now want to extend your application with search-cababilities to new objects, you only have to create addtional records in the SEARCHABLE_FIELDS-table.

 

That's it for the db-side, now let's integrate it in the APEX-application.

 

The implementation - APEX-side - The search page

The first thing we have to do is create a search-page. To do so, we create a new report-page with a report-region based on the following query

SELECT OBJECT_DESCRIPTION,
       OBJECT_IDENTIFIER,
       COLUMN_DESCRIPTION,
       SEARCH_IDENTIFIER,
       PAGE_URL
  FROM TABLE(CAST (FK_SEARCH_APEX(:P9300_SEARCH_FIELD) AS COT_APX_SEARCH_RESULT))

The passing of the search-string is done in the last line of the query as argument to the call of the function, name that item according to your requirements. In my case, i added an additional region as a "Report Filter-Single Row"  region with just that item in it.

 

Also make sure that the report-column for PAGE_URL correctly

  • Diplay As - "Standard Report column"
  • Link Text - "Show"
  • Target - "URL"
  • URL - "#PAGE_URL#"

    search column link

Now we have a search-page which is capable of searching for everything defined in the SEARCHABLE_FIELDS-table and branching to the appropiate page for each result.

 

search search page

 

You can include that page in your menu-structure or tab-bar.

The implementation - APEX-side- Page 1

But i wanted also to have the functionality of the little searchfield in upper right of the tab-bar, as you have it in the application-builder. For this i had to trick a little and tweak in the HTML of the application-builder main-page. From there i took the html-fragments, and the css.

 

First, i created my own template for the page and extended the tab-region for the search-field. In the body of the template, i added the following HTML direct behind the #TAB_CELLS#-tag

 

<div id="appl-search"><table border="0">
<tr>
  <td nowrap="nowrap" align="right"></td>
  <td  colspan="1" rowspan="1" align="left" valign="middle">
    <div class="searchbox-plugin">
      <span class="left"></span>
      <input type="text"  id="P9300_SEARCH_FIELD" value="Anwendung durchsuchen" />
      <span class="right"></span>
    </div>
  </td>
</tr>
</table>
</div>

 

Its important that the id-attribute of the input-item matches the itemname of the searchitem on my search-page.

 

search template body

 

To make this look somehow nice i took the images

  • apex-search-left.gif
  • apex-search-center.gif
  • apex-search-right.gif

from the apex-application-builder installfiles and uploaded them into my application.

Additionally the following styles has to be included either in the pages html-header-attribut or in the application's css-file.

 

<style>
#appl-search {
    width: 165px;
    margin: 9px 20px 0 0;
    float: right;
    position: relative;
}

#appl-search .left {
    display: block;
    height: 19px;
    left: 6px;
    top: 0;
    width: 10px;
    position: absolute;
    background: transparent url(#APP_IMAGES#apex-search-left.gif) no-repeat scroll 0 0;
}

#appl-search input {
    background: transparent url(#APP_IMAGES#apex-search-center.gif) repeat-x scroll center top;
    border: 0 none;
    font-size: 12px;
    color: #404040;
    margin: 0;
    padding: 2px;
    right: 18px;
    top: 0;
    width: 127px;
    outline: none;
    position: absolute;

}

#appl-search .right {
    display: block;
    height: 19px;
    right: 0;
    top: 0;
    width: 19px;
    cursor: pointer;
    position: absolute;
    background: transparent url(#APP_IMAGES#apex-search-right.gif) no-repeat scroll 0 0;
}
</style>

 

Here's the result

 

search page1

 

Obviously, this only looks nice with theme 2 "Builder Blue", for other themes you will have to create your own images and style.

 

The next step is to implement two dynamic actions to make up the functionality of the searchfield. The first action is to remove the text shown inside the search field and make it empty. So we have a dynamic action with the following properties:

  • Event - get Focus
  • Selection Type - Item
  • Item - P9300_SEARCH_FIELD (or the name of what you defined in the template)
  • Action - Execute Javascript
  • Code - $s("P9300_SEARCH_FIELD", "");

search action clear 1

search action clear 2

 

The second dynamic action is to submit the page and redirect to the search-page. This dynamic action has actually two actions, first it submits the item-content of P9300_SEARCH_FIELD to session-state, then it redirects to the search-page. The first step is necessary, because the search-item isn't a "real" page-item defined declaratively in the page-definition.

 

Here are the settings

  • Event - Change
  • Selection Type - Item
  • Item - P9300_SEARCH_FIELD (or the name of what you defined in the template)
  • Condition - Is not null

search action search 1

 

First Action

  • Action - Execute PL/SQL
  • PL/SQL-Code - NULL;
  • Items to submit - P9300_SEARCH_FIELD

search action search 2

 

Second action

  • Action - Submit page 
  • Request - SEARCH

search action search 3

 

And lastly you need a branch in the page processing going to your search-page

  • branch type - branch to page
  • Branch point - On submit .- Before computation
  • Target type - Page in the application
  • Page - 9300 (or the number of the page you use)

search action branch

 

That's it. Now upon entering a search-text in the search-field and leaving the field with TAB or ENTER will redirect to the search-page, passing the search-text. The search-page will execute the report-query, which in turn executes our search-function. The results are shown and you have a link to navigate to the appropiate page.

 

You can test out your own on apex.oracle.com

Weiterlesen

APEX

26. März 2012 , Geschrieben von Andreas Weiden Veröffentlicht in #APEX

I recently started digging into APEX. Maybe at some time i'm able to place something useful about it here .

Weiterlesen

A card-like javabean

16. Juni 2011 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

In some application i've seen a layout where the data is presented in a card-like style, having a picture to the left, a title on top and some text containing additional information. I liked the layout, so i decided that this should also be possible in forms (of course using a java-bean)

The idea

So what should such a component be capable of

  • it should be used as a standard textitem
  • the layout should be fully configurable
  • it should allow showing images
  • it should allow hyperlinks
  • it should look somehow "modern"
The implementation

And thats my approach. 

  • I created a javabean overwriting the standard VTextField. That allows usage in a multi-record block without having to deal with synchronizing content when scrolling
  • The layout is done by an HTMLEditorkit placed in a JEditorPane.
  • The layout is defined by creating a html-"template", containing placeholders to be replaced with the real data at runtime
  • The value is set by simply assigning it to the Bean-Item
  • WHEN-MOUSE-CLICKED-events are raised when clicking on the item or on a link, with a special GET_CUSTOM_PROPERTY you can get the link clicked in the bean (if any)
  • For handling images i use a adjusted version from Francois Degrelles "HandleImage3"-Bean
The code

Here's a first version of the java code

package forms;
import java.awt.Color;
import java.awt.Graphics;
import java.awt.Graphics2D;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.awt.image.BufferedImage;
import java.awt.image.DataBufferInt;

import java.net.URL;

import java.util.Dictionary;
import java.util.EventListener;
import java.util.Hashtable;
import java.util.StringTokenizer;

import javax.swing.ImageIcon;
import javax.swing.JEditorPane;
import javax.swing.event.HyperlinkEvent;
import javax.swing.event.HyperlinkListener;
import javax.swing.text.html.HTMLEditorKit;

import oracle.forms.properties.ID;
import oracle.forms.ui.VTextField;

import sun.misc.BASE64Decoder;
/**
    This is just sample code, its free to use.
    It is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

    It is tested against Forms 10.1.2.0.2, but may stop working with any patch or future version of forms
   
    Sample code for a Javabean to implement a carditem
    For usage-notes see the the package PK_CARDITEM, which is the counterpart for this code
    on the forms-side
*/
public class CardTextfield extends VTextField implements MouseListener, HyperlinkListener
{
  /** Constants for edge-style */
  public static final String STYLE_EDGE="E";
  public static final String STYLE_ROUNDED="R";
  /** ID's for forms-events */
  public static final ID TEMPLATE   =ID.registerProperty("TEMPLATE");
  public static final ID CLICKED    =ID.registerProperty("CLICKED");
  public static final ID LINK       =ID.registerProperty("LINK");
  public static final ID RECT_STYLE =ID.registerProperty("RECT_STYLE");
  public static final ID READIMGBASE=ID.registerProperty("READIMGBASE");
  public static final ID SHADOWCOLOR=ID.registerProperty("SHADOWCOLOR");
  public static final ID HOVERCOLOR =ID.registerProperty("HOVERCOLOR");
  /** Imagecache */
  private static Hashtable C_IMAGECACHE=new Hashtable(); 
  /** Constant for opacity */
  private static float     C_OPACITY= 0.99f;
  /** Stringbuffer for uploading images */
  private StringBuffer m_imageBuffer =new StringBuffer();
  /** Size of shadow in pixels */
  private int          m_shadowSize=10;
  /** Shadow-color */
  private Color        m_shadowColor=Color.black; 
  /** Shadow-Color when mouse-focused */
  private Color        m_shadowFocusColor=Color.red; 
  /** The html-editor-pane */
  private JEditorPane  m_htmlEditor=new JEditorPane();
  /** html-template */
  private String       m_template="";
  /** Flag, if mouse is on image */
  private boolean      m_hasFocus=false;
  /** Last "Link" visited in html*/
  private String       m_event=null;
  /** Initialization-flag */
  private boolean      m_first=true; 
  /** Current edge-style */
  private String       m_rectangleStyle=STYLE_EDGE; 
  /** Gridbag-Constraints for Layout */
  private GridBagConstraints m_constraints = new GridBagConstraints();
  /** Gridbag-Layout */
  private GridBagLayout m_layout = new GridBagLayout();
  /** Image for shadow */
  private BufferedImage m_shadowImage=null;       
  /** Image for shadow when mouse-focused */
  private BufferedImage m_hoverImage=null;       

  /**
   * Constructor, set up the layout
   */
  public CardTextfield()
  {
    this.setLayout(m_layout);
    this.addMouseListener(this);
    try
    {
      m_htmlEditor.setEditorKit(new HTMLEditorKit());
      // Seems not available in JInitiator
      //m_htmlEditor.setFocusable(false);
      m_htmlEditor.addHyperlinkListener(this);
      m_htmlEditor.setEditable(false);
       
      m_constraints.fill = GridBagConstraints.BOTH;  
      m_constraints.weightx = 1;                    
      m_constraints.weighty = 1;                    
      m_constraints.insets = new Insets(8,8,12,12);  
      m_constraints.gridx = 0;                       
      m_constraints.gridy = 0;                       
      m_constraints.gridwidth = 0;                   
      m_constraints.gridheight = 0;                  
      m_layout.setConstraints(m_htmlEditor, m_constraints);
      add(m_htmlEditor);
      Dictionary cache=(Dictionary)m_htmlEditor.getDocument().getProperty("imageCache");
      if (cache==null)
      {
        m_htmlEditor.getDocument().putProperty("imageCache",C_IMAGECACHE);
      }

    } catch (Exception e)
    {
    }
  }

  /**
   * Create the rectangle-image and apply the shadow
   * @return Image
   * @param c Color for shadow
   * @param height height of the image
   * @param width width of the image
   */
  private BufferedImage createShadowRectangle(int width, int height, Color c)
  {
    BufferedImage subject = new BufferedImage(width + m_shadowSize * 2,
                                              height + m_shadowSize * 2,
                                              BufferedImage.TYPE_INT_ARGB);

    Graphics2D g2 = subject.createGraphics();
 
    g2.setPaint(Color.black);
    if (STYLE_EDGE.equals(m_rectangleStyle))
    {
      g2.fillRect(1, 1, width, height);
    } else
    {
      g2.fillRoundRect(1, 1, width, height, 25, 25);
    }
    g2.dispose();

    applyShadow(subject, c);
    return subject;
  }

  /**
   * applys a shadow to the given image, taken from
   * Romain Guy's blog at http://jroller.com/gfx/entry/non_rectangular_shadow
   * @param c color for the shadow
   * @param image imgae to apply a shadow on
   */
  private void applyShadow(BufferedImage image, Color c)
  {
    int dstWidth = image.getWidth();
    int dstHeight = image.getHeight();

    int left = (m_shadowSize - 1) >> 1;
    int right = m_shadowSize - left;
    int xStart = left;
    int xStop = dstWidth - right;
    int yStart = left;
    int yStop = dstHeight - right;

    int shadowRgb = c.getRGB() & 0x00FFFFFF;

    int[] aHistory = new int[m_shadowSize];
    int historyIdx = 0;

    int aSum;

    int[] dataBuffer = ((DataBufferInt) image.getRaster().getDataBuffer()).getData();
    int lastPixelOffset = right * dstWidth;
    float sumDivider = C_OPACITY/m_shadowSize;

    // horizontal pass

    for (int y = 0, bufferOffset = 0; y < dstHeight; y++, bufferOffset = y * dstWidth)
    {
      aSum = 0;
      historyIdx = 0;
      for (int x = 0; x < m_shadowSize; x++, bufferOffset++)
      {
        int a = dataBuffer[bufferOffset] >>> 24;
        aHistory[x] = a;
        aSum += a;
      }

      bufferOffset -= right;

      for (int x = xStart; x < xStop; x++, bufferOffset++)
      {
        int a = (int) (aSum * sumDivider);
        dataBuffer[bufferOffset] = a << 24 | shadowRgb;

        // substract the oldest pixel from the sum
        aSum -= aHistory[historyIdx];

        // get the lastest pixel
        a = dataBuffer[bufferOffset + right] >>> 24;
        aHistory[historyIdx] = a;
        aSum += a;

        if (++historyIdx >= m_shadowSize)
        {
          historyIdx -= m_shadowSize;
        }
      }
    }

    // vertical pass
    for (int x = 0, bufferOffset = 0; x < dstWidth; x++, bufferOffset = x)
    {
      aSum = 0;
      historyIdx = 0;
      for (int y = 0; y < m_shadowSize; y++, bufferOffset += dstWidth)
      {
        int a = dataBuffer[bufferOffset] >>> 24;
        aHistory[y] = a;
        aSum += a;
      }

      bufferOffset -= lastPixelOffset;

      for (int y = yStart; y < yStop; y++, bufferOffset += dstWidth)
      {
        int a = (int) (aSum * sumDivider);
        dataBuffer[bufferOffset] = a << 24 | shadowRgb;

        // substract the oldest pixel from the sum
        aSum -= aHistory[historyIdx];

        // get the lastest pixel
        a = dataBuffer[bufferOffset + lastPixelOffset] >>> 24;
        aHistory[historyIdx] = a;
        aSum += a;

        if (++historyIdx >= m_shadowSize)
        {
          historyIdx -= m_shadowSize;
        }
      }
    }
  }

  /**
   * Paint the component. At first call, duplicate mouse-listeners from
   * the item to the thml-editor
   * @param g Graphics-Context
   */
  public void paint(Graphics g)
  {
   
    if (m_first)
    {
      // Move Mouselisteners forward to html-editor
      // Seems not available in JInitiator changed to generic getListeners
      EventListener[] ml=getListeners(MouseListener.class);
      for (int i=0;i<ml.length;i++)
      {
        m_htmlEditor.addMouseListener((MouseListener)ml[i]);
      }
      m_first=false;
    }
   
    Graphics2D g2=(Graphics2D)g;
    g2.setPaint(getBackground());
    g2.fillRect(0, 0, getBounds().width, getBounds().height);
    if (m_hasFocus)
    {
      if (m_hoverImage==null)
      {
        m_hoverImage=createShadowRectangle(getBounds().width-10, getBounds().height-10, m_shadowFocusColor);
      }
      g2.drawImage(m_hoverImage, 3, 3, null);
    } else
    {
      if (m_shadowImage==null)
      {
        m_shadowImage=createShadowRectangle(getBounds().width-10, getBounds().height-10, m_shadowColor);
      }
      g2.drawImage(m_shadowImage, 3, 3, null);
    }
    g2.setPaint(getForeground());
    if (STYLE_EDGE.equals(m_rectangleStyle))
    {
      g2.fillRect(3, 3, getBounds().width-10, getBounds().height-10);
    } else
    {
      g2.fillRoundRect(3, 3, getBounds().width-10, getBounds().height-10, 25, 25);
    }
     // Apply forground to html-editors background
    m_htmlEditor.setBackground(getForeground());
    // Sub-components
    paintComponents(g);
  }
 
  /**
   * Apply the given values to the template
   * @param text values, concatenated
   */
  public void setValue(String text)
  {
    String result="";
    if (text!=null && !"".equals(text))
    {
      result=m_template;
      StringTokenizer st=new StringTokenizer(text, "|");
      while (st.hasMoreTokens())
      {
        String field=null;
        String value=null;
        if (st.hasMoreTokens())
        {
          field=st.nextToken();
        }
        if (st.hasMoreTokens())
        {
          value=st.nextToken();
        }
        if (field!=null && value!=null)
        {
          int pos=result.indexOf("#"+field+"#");
          while (pos>-1)
          {
            result=result.substring(0, pos)+value+result.substring(pos+field.length()+2);
            pos=result.indexOf("#"+field+"#");
          }
        }
      }
    } else
    {
      result="<html>&nbsp;</html>";
    }
    m_htmlEditor.setText(result);
  }
 
  /**
   * Standard Method, overwritten to make the bean-specific properties from forms
   * @return true
   * @param value
   * @param id   
   */
  public boolean setProperty(ID id, Object value)
  {
    if (id==TEMPLATE)
    {
      m_template=(String)value;
      setValue(getText());
      return true;
    } else if (id==RECT_STYLE)
    {
      // Force shadow to be recalculated
      m_shadowImage=null;
      m_hoverImage=null;
      m_rectangleStyle=(String)value;
      if (STYLE_EDGE.equals(m_rectangleStyle))
      {
        m_constraints.insets = new Insets(2,2,6,6);  
        m_layout.setConstraints(m_htmlEditor, m_constraints);
       
      } else
      {
        m_constraints.insets = new Insets(8,8,12,12);  
        m_layout.setConstraints(m_htmlEditor, m_constraints);
      }

      return true;
    } else if (id==SHADOWCOLOR)
    {
      // R,G,B values, divided by |
      StringTokenizer st=new StringTokenizer((String)value, "|");
      int r=Integer.parseInt(st.nextToken());
      int g=Integer.parseInt(st.nextToken());
      int b=Integer.parseInt(st.nextToken());
      m_shadowColor=new Color(r, g, b);
      m_shadowImage=null;
      return true;
    } else if (id==HOVERCOLOR)
    {
      // R,G,B values, divided by |
      StringTokenizer st=new StringTokenizer((String)value, "|");
      int r=Integer.parseInt(st.nextToken());
      int g=Integer.parseInt(st.nextToken());
      int b=Integer.parseInt(st.nextToken());
      m_shadowFocusColor=new Color(r, g, b);
      m_hoverImage=null;
      return true;
    } else if (id==ID.VALUE)
    {
      super.setProperty(id, "");
      setValue((String)value);
      return true;
    } else if (id == READIMGBASE) 
    {       
      String imageData = value.toString();
      if(!imageData.startsWith("[END_IMAGE]"))
      {
        m_imageBuffer.append(imageData);
      }
      else
      {
        // extract name of icon
        String name=imageData.substring(11);
        BASE64Decoder decoder = new BASE64Decoder();
        try
        {
          byte[] decodedStr = decoder.decodeBuffer(m_imageBuffer.toString());
          ImageIcon ii = new ImageIcon(decodedStr);
          URL u=new URL(name);
          C_IMAGECACHE.put(u, ii.getImage());
        } catch(Exception e)
        {
        }
        finally
        {
          m_imageBuffer =new StringBuffer();
        }
      }
      return true ;
    } else if (id == ID.BACKGROUND)
    {
      super.setProperty(id, value);
      this.repaint();
      return true;
    } else if (id == ID.FOREGROUND)
    {
      super.setProperty(id, value);
      this.repaint();
      return true;
    } else
    {
      /** Delegate */
      return super.setProperty(id, value);
    }
  }

  public void mouseClicked(MouseEvent e) {}
 
  public void mousePressed(MouseEvent e) {}
 
  public void mouseReleased(MouseEvent e) {}
 
  /**
   * Mouse-entered event,  remember flag for colored-shadow
   * @param e
   */
  public void mouseEntered(MouseEvent e)
  {
    m_hasFocus=true;
    this.repaint();
  }
 
  /**
   * Mouse-exited event,  reset flag for colored-shadow
   * @param e
   */
  public void mouseExited(MouseEvent e)
  {
    m_hasFocus=false;
    this.repaint();
  }
 
  /**
   * Hyperlink-Listener, remembers last visited link in the document
   * @param hyperlinkEvent
   */
  public void hyperlinkUpdate(HyperlinkEvent hyperlinkEvent)
  {
    HyperlinkEvent.EventType type = hyperlinkEvent.getEventType();
    URL url = hyperlinkEvent.getURL();
    if (type == HyperlinkEvent.EventType.ENTERED)
    {
      m_event=hyperlinkEvent.getURL().toString();
    } else if (type == HyperlinkEvent.EventType.ACTIVATED)
    {
    } else if (type == HyperlinkEvent.EventType.EXITED)
    {
      m_event=null;
    } 
  }

  /**
   * Standard Method, overwritten to return the bean-specific properties to forms
   * @return value
   * @param id   
   */
  public Object getProperty(ID id)
  {
    if (id==LINK)
    {
      // return the last activated link
      return m_event;
    } else
    {
      return super.getProperty(id);
    }
  } 
}

And here the forms-code

PACKAGE PK_CARDITEM IS
/**
   
    This is just sample code, its free to use.
    It is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

    It is tested against Forms 10.1.2.0.2, but may stop working with any patch or future version of forms

    Sample code for a forms-Side Package for the Carditem Java-Bean

    To build up a carditem, follow these steps
   
    -Create a textitem which the appropiate size and set the implementation class to forms.CardItem
    -Create a WHEN-MOUSE-CLICKED-trigger at the item with a call to FK_GET_CLICKED_LINK
    -For some internal reason you have to have another visible item in your block (maybe a dummy-item with size 0x0),
     and you have to have a WHEN-NEW-ITEM-INSTANCE-trigger on your carditem with a NEXTITEM or a PREVIOUSITEM in it.
    -Assign the needed values to the Card-Item in an appropiate trigger, maybe the POST-QUERY-trigger of your block with code like
   
    DECLARE
        lValues PK_CARDITEM.tValueList;
        rValue  PK_CARDITEM.tValue;
    BEGIN
          -- Fill the variables for the html-item
          rValue.vcField:='NAME';
          rValue.vcValue:=:EMP.FIRST_NAME ||' ' || :EMP.LAST_NAME;
          lValues(1):=rValue;
         
          rValue.vcField:='DATE';
          rValue.vcValue:=TO_DATE(:EMP.HIRE_DATE);
          lValues(2):=rValue;

          rValue.vcField:='SALARY';
          rValue.vcValue:=:EMP.SALARY;
          lValues(3):=rValue;

          :EMP.B:=PK_CARDITEM.FK_GET_ITEM_VALUE('EMP.B', lValues);
    END
   
    -make sure the jar is on the archive or achive_jini-tag   
  
   Template
   - The template is built-up using standard-html. Everything which can be rendered by a JEditorPane can be used
     which means, you have to test what works. If you want to put some variable, data-specific content in the html,
     simply put the name of this "field" in the html enclosed by #, e.g. <P><FONT STYLE="font-size: 13pt">#DEPT#</FONT></P></TD>.
     The dept will be replaced at runtime with the value passed via the FK_GET_ITEM_VALUE.
    
   Using images and links you can use images in your
   - You need the Database-package Pkg_Read_Blob_Image from Francois Degrelle's advanced image javabean
   - You can use images and hyperlinks in your html-content
     + Links. You can use any link. If you want to use "virtual links" to call some forms-logic, make sure it starts with http://
     + Images. You can use real urls for images or use PR_READ_IMAGE to load an image to the carditem. You can use any url for the
       image-link, you only have to make sure that the url starts with http://
*/
  -- Type used for value-passing
  TYPE tValue IS RECORD (
    vcField VARCHAR2(30),
    vcValue VARCHAR2(4000)
  );
  -- List-Type used for value-passing
  TYPE tValueList IS TABLE OF tValue INDEX BY BINARY_INTEGER;
 
  /** Initialization-method for a card-item
      i_vcItem           name of the item which represents the carditem
      i_vcTemplate       HTML-template to show the content of the item
      i_vcStyle          Borderstyle, either R for rounded rectangle or E for edged rectangle
  */   
  PROCEDURE PR_INIT(i_vcItem     IN VARCHAR2,
                    i_vcTemplate IN VARCHAR2,
                    i_vcStyle    IN VARCHAR2);
                   
  /** Setter for the template
      i_vcItem           name of the item which represents the carditem
      i_vcTemplate       HTML-template to show the content of the item
  */
  PROCEDURE PR_SET_TEMPLATE(i_vcItem     IN VARCHAR2,
                            i_vcTemplate IN VARCHAR2);
                   
 
  /** Setter for the shadow-Color
      i_vcItem           name of the item which represents the carditem
      i_nRed             Red-part of color
      i_nGreen           Green-part of color
      i_nBlue            Blue-part of color
     
  */
  PROCEDURE PR_SET_SHADOW_COLOR(i_vcItem IN VARCHAR2,
                                i_nRed   IN NUMBER,
                                i_nGreen IN NUMBER,
                                i_nBlue  IN NUMBER);
                               
  /** Setter for the hover-Color, when the mouse moves over the carditem
      i_vcItem           name of the item which represents the carditem
      i_nRed             Red-part of color
      i_nGreen           Green-part of color
      i_nBlue            Blue-part of color
     
  */
  PROCEDURE PR_SET_HOVER_COLOR(i_vcItem IN VARCHAR2,
                               i_nRed   IN NUMBER,
                               i_nGreen IN NUMBER,
        
                               i_nBlue  IN NUMBER);
                              
  /** Computes the value which has to be set to the item
      i_vcItem           name of the item which represents the carditem
      i_lValues          List of Key and Values
     
      RETURN             VARCHAR2-Value which has to be set as the values item
  */
  FUNCTION FK_GET_ITEM_VALUE(i_vcItem  IN VARCHAR2,
                             i_lValues IN tValueList)
  RETURN VARCHAR2;
 
  /** WHEN-MOUSE-CLICKED-trigger for the carditem
      i_vcItem           name of the item which represents the carditem
     
      RETURN             NULL if the item was clicked, name of a href-tag if it was clicked
  */
  FUNCTION FK_GET_CLICKED_LINK(i_vcItem IN VARCHAR2)
  RETURN VARCHAR2;

  /** WHEN-MOUSE-CLICKED-trigger for the carditem
      i_vcItem           name of the item which represents the carditem
      i_vcQueryForBlob   Query which points to the BLOB of the image to be read
      i_vcImageUrl       Name under which the image will be referenced in the carditem
  */
    PROCEDURE PR_READ_IMAGE(i_vcItem         IN VARCHAR2,
                            i_vcQueryForBlob IN VARCHAR2,
                            i_vcImageUrl     IN VARCHAR2);
 
END;

PACKAGE BODY PK_CARDITEM IS

  PROCEDURE PR_INIT(i_vcItem     IN VARCHAR2,
                    i_vcTemplate IN VARCHAR2,
                    i_vcStyle    IN VARCHAR2) IS
  BEGIN
    SET_CUSTOM_ITEM_PROPERTY(i_vcItem, 'TEMPLATE',   i_vcTemplate);
    SET_CUSTOM_ITEM_PROPERTY(i_vcItem, 'RECT_STYLE', i_vcStyle);
  END;   
     
  -- -------------------------------------------------------------------------------     
                   
  PROCEDURE PR_SET_TEMPLATE(i_vcItem     IN VARCHAR2,
                            i_vcTemplate IN VARCHAR2) IS
  BEGIN
    SET_CUSTOM_ITEM_PROPERTY(i_vcItem, 'TEMPLATE',   i_vcTemplate);
  END;                                 
                   
  -- -------------------------------------------------------------------------------     
 
  PROCEDURE PR_SET_SHADOW_COLOR(i_vcItem IN VARCHAR2,
                                i_nRed   IN NUMBER,
                                i_nGreen IN NUMBER,
                                i_nBlue  IN NUMBER) IS
  BEGIN
      SET_CUSTOM_ITEM_PROPERTY(i_vcItem,'SHADOWCOLOR', TO_CHAR(i_nRed)   || '|' ||
                                                       TO_CHAR(i_nGreen) || '|' ||
                                                       TO_CHAR(i_nBlue)  || '|');
  END;                               
  -- -------------------------------------------------------------------------------     

  PROCEDURE PR_SET_HOVER_COLOR(i_vcItem IN VARCHAR2,
                               i_nRed   IN NUMBER,
                               i_nGreen IN NUMBER,
                               i_nBlue  IN NUMBER) IS
  BEGIN
      SET_CUSTOM_ITEM_PROPERTY(i_vcItem,'HOVERCOLOR', TO_CHAR(i_nRed)   || '|' ||
                                                      TO_CHAR(i_nGreen) || '|' ||
                                                      TO_CHAR(i_nBlue)  || '|');
  END;                                                     
                              
  -- -------------------------------------------------------------------------------     

  FUNCTION FK_GET_ITEM_VALUE(i_vcItem  IN VARCHAR2,
                             i_lValues IN tValueList)
  RETURN VARCHAR2 IS
    vcValue VARCHAR2(32000);
  BEGIN
      FOR i IN 1..i_lValues.COUNT LOOP
          vcValue:=vcValue || i_lValues(i).vcField || '|' || REPLACE(i_lValues(i).vcValue, '|', ' ') || '|';
      END LOOP;
      RETURN vcValue;
  END;                               
 
  -- -------------------------------------------------------------------------------     

  FUNCTION FK_GET_CLICKED_LINK(i_vcItem IN VARCHAR2)
  RETURN VARCHAR2 IS
        nTopRecord NUMBER:=GET_BLOCK_PROPERTY(SUBSTR(i_vcItem, 1, INSTR(i_vcItem, '.')-1), TOP_RECORD);
        vcLink     VARCHAR2(2000);
    BEGIN
      IF :SYSTEM.MOUSE_RECORD!=:SYSTEM.CURSOR_RECORD THEN
          GO_RECORD(:SYSTEM.MOUSE_RECORD);
      END IF;
      vcLink:=GET_CUSTOM_PROPERTY(i_vcItem, :SYSTEM.MOUSE_RECORD-nTopRecord+1, 'LINK');
      RETURN vcLink;
    END;

  -- -------------------------------------------------------------------------------     

    PROCEDURE PR_READ_IMAGE(i_vcItem         IN VARCHAR2,
                            i_vcQueryForBlob IN VARCHAR2,
                            i_vcImageUrl     IN VARCHAR2) IS
      LB$Ok      boolean ;
      LC$Image   Varchar2(32767) ;
      LC$Clause  Varchar2(4000) ;
    BEGIN
       
        --
        -- Read an image from the database
        --
        LC$Clause := i_vcQueryForBlob;
        -- Select the Blob column --
        If Pkg_Read_Blob_Image.Select_Blob(LC$Clause) Then
             Loop
               -- Get the image chunks from the database --
               LC$Image := Pkg_Read_Blob_Image.Get_B64_Chunk ;
               If LC$Image Is Not Null Then
                 -- Send the chunks to the Java Bean --
                 Set_Custom_Property( i_vcItem, 1, 'READIMGBASE', LC$Image ) ;
               Else
                    -- End the sending process --
                    Set_Custom_Property( i_vcItem, 1, 'READIMGBASE', '[END_IMAGE]' || i_vcImageUrl) ;
                    Exit ;
               End if ;   
             End loop ;
        End if ; 
    END; 
END;

 

This video shows the usage of the bean with the Oracle demo-tables EMPLOYEES (extended with a column called PHOTO to contain an image for each employee)

A compiled version with a demo-fmb can be found at Francois Degrelle's PJC-site here

Weiterlesen

Pasting Excel-data to a forms-block

8. Mai 2011 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

There was a thread in the Forms-forum these days dealing with Copy&Paste-problems, especially with the question if its possible to copy whole rows into a forms-block. As this is structured
data and. of course, forms doesn't know how do deal with that structured data "automatically". But there is a way to deal with that problem using standard PL/SQL-methods.

Example

Lets say that some manager has entered data about new employees in an excel-sheet. And now the manager wants to transfer that data to the "legay-system"without having to type everything again. So
i use a tabular form based on the EMPLOYEES-table from the demo-schema.

How to do it

When you have a closer look about what gets pasted into the clipboard when you have copied some rows and columns  from excel. The data is pure "textdata", each row is delimited by a CHR(10).
In each row, the different cell-values are separated by a CHR(9). So. if we know the stucture of the excel-data, its quite easy to slice it down to the differents rows and columns and transfer
the data to forms-items.
 

As we have to do some "special work" on pasting, i start the pasting-action using a button.

Here's the code behing the "Paste"-Button

 

DECLARE
  vcBuffer VARCHAR2(32000);
  vcLine   VARCHAR2(32000);
  VARCHAR2(4000);

  FUNCTION FK_SPLIT(io_vcText IN OUT VARCHAR2, i_vcSplitChar IN VARCHAR2)
  RETURN VARCHAR2 IS
    iPos     PLS_INTEGER:=NVL(INSTR(io_vcText, i_vcSplitChar),0);
    vcResult VARCHAR2(4000);
  BEGIN
    IF iPos>0 THEN    
      vcResult:=SUBSTR(io_vcText, 1, iPos-1);     
      io_vcText:=SUBSTR(io_vcText, iPos+1);  
    ELSE     
      vcResult:=io_vcText;     
      io_vcText:=NULL;  
    END IF;   
    RETURN vcResult; 
  END;
 
  BEGIN     
    GO_ITEM('BL_CONTROL.IT_PASTE'); 
    PASTE_REGION; 
    GO_BLOCK('EMPLOYEES'); 
    vcBuffer:=:BL_CONTROL.IT_PASTE; 
    LOOP
      EXIT WHEN vcBuffer IS NULL;   
      -- Next line   
      vcLine:=FK_SPLIT(vcBuffer, CHR(10));   
      IF :SYSTEM.RECORD_STATUS!='NEW' THEN     
        CREATE_RECORD;   
      END IF;   
      :EMPLOYEES.EMPLOYEE_ID:=TO_NUMBER(FK_SPLIT(vcLine, CHR(9)));   
      :EMPLOYEES.FIRST_NAME:=FK_SPLIT(vcLine,CHR(9));   
      :EMPLOYEES.LAST_NAME:=FK_SPLIT(vcLine, CHR(9));   
      :EMPLOYEES.EMAIL:=FK_SPLIT(vcLine, CHR(9));   
      :EMPLOYEES.HIRE_DATE:=TO_DATE(FK_SPLIT(vcLine, CHR(9)), 'DD.MM.YYYY'); 
    END LOOP;
  END;
END;


Of course, this dummy-Item :BL_CONTROL.IT_PASTE on the canvas could be made to have a size of (0,0) in a "real" application.

Weiterlesen

An "Expand/Collapse"-Canvas Java-Bean

22. Januar 2011 , Geschrieben von Andreas Weiden

The task

In one of the posts on the OTN forms forum last week there was a question of if there is something like an expandable/collapsable Component like its known from outlook. It seems there is no component like this at the time, so i had the idea to write a java-bean to implement such a feature.

The idea

So what should such a component be capable of

  • the panel should integrate in the standard-lookandfeeld
  • the panle should be capable of containing anything possible in forms
  • everything on such a panel should be a "standard" forms-object
  • you should be able to configure as many single "Panel"
The implementation

And thats my approach.

  • Several panels can be grouped in one panelgroup, from each panelgroup always one panel is active.
  • For each panel you define one stacked canvas, and you can put on that canvas everything you like (items, tree, whole blocks)
  • On each of these stacked canvases there has to be one button which has my javabean as implementation class
  • The stacked canvases have to be arranged on a content canvas that way, that the viewport form one stacked canvas is fully visible, the others are shrinked to show just the button
  • There is one initialization procedure called in the WHEN-NEW-FORM-INSTANCE-trigger
  • You can react on a WHEN-BUTTON-PRESSED-trigger for the created buttons to do some initialization for the shown "Panel"

 

Thats it. Everything else is handled at the java-side, there is no more interaction between the client and application-server.

The code

I've made up a first version of my bean, heres the java-code

package forms;
import java.awt.Color;
import java.awt.Component;
import java.awt.Container;
import java.awt.Dimension;
import java.awt.Image;
import java.awt.Toolkit;
import java.net.URL;
import java.util.StringTokenizer;
import javax.swing.ImageIcon;
import oracle.forms.handler.IHandler;
import oracle.forms.ui.DrawnPanel;
import oracle.forms.ui.VBean;
import oracle.forms.properties.ID;
import oracle.forms.ui.VButton;
import java.awt.event.MouseEvent;
import java.awt.Event;
import java.awt.event.ActionEvent;
import java.awt.Graphics;

/**
    This is just sample code, its free to use.
    It is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

    The code relies on the internal structure of forms when rendering stacked canvases
    It is tested against Forms 10.1.2.0.2, but may stop working with any patch or future version of forms
    
    Sample code for a Javabean to implement an expandabled/collapseable canvas.
    For usage-notes see the the package PK_ACCORDION, which is the counterpart for this code
    on the forms-side
*/
public class AccordionButton extends VButton implements Runnable
{
  /** Ids for Forms-events */
  public static final ID  INIT_ACCORDION    =ID.registerProperty("INIT_ACCORDION");
  public static final ID  SCALE_ACCORDION   =ID.registerProperty("SCALE_ACCORDION");
  public static final ID  ACTIVATE          =ID.registerProperty("ACTIVATE");

  /** default-images */
  private final ImageIcon IMG_COLLAPSED=new ImageIcon(this.getClass().getResource("collapsed.png"));
  private final ImageIcon IMG_EXPANDED=new ImageIcon(this.getClass().getResource("expanded.png"));  
  /** constants */
  private static final int MODE_SHRINK=0;
  private static final int MODE_EXPAND=1;
  /** class variables */
  private static boolean  c_processing=false;

  /** currently used images */
  private ImageIcon     m_collapsed=null;
  private ImageIcon     m_expanded=null;
  /** codebase */
  private URL           m_codeBase=null;  

  /** other vars */
  private int             m_mode=-1;
  private String          m_buttonName=null;
  private Dimension       m_dimension=null;
  private DrawnPanel      m_masterCanvas=null;
  private String          m_previousItemName=null;
  private String          m_nextItemName=null;
  private boolean         m_active=false;
  private AccordionButton m_previousItem=null;
  private AccordionButton m_nextItem=null;
  private AccordionButton m_toMakeActive=null;
  private int             m_minHeight=-1;
  private int             m_maxHeight=-1;

  /**
   * Initialize the codebase, used for image-loading
   *
   * @param handler
   */
  public void init(IHandler handler)
  {
    // Remember Codebase
    m_codeBase = handler.getCodeBase();
    super.init(handler);
  }

  /**
   *
   * @return Searches for an AccordionButton with the given name,
   *         starting the search at the given Container
   * @param name Name of the accordion button to search
   * @param c    Container to start at
   */
  private AccordionButton rekuFindChild(Container c, String name)
  {
    AccordionButton result=null;
    for (int i=0;i<c.getComponentCount();i++)
    {
      Component comp=c.getComponent(i);
      if (comp instanceof AccordionButton)
      {
        if (name.equals(((AccordionButton)comp).getButtonName()))
        {
          result=(AccordionButton)comp;
          break;
        }
      }
    }
    if (result==null)
    {
      for (int i=0;i<c.getComponentCount();i++)
      {
        if (c.getComponent(i) instanceof Container)
        {
          result=rekuFindChild((Container)c.getComponent(i), name);
          if (result!=null)
          {
            break;
          }
        }
      }
    }
    return result;
  }

  /**
   * finds the "Neighbor"-Accordion-Button by takeing the names given by
   * the initialization property
   */
  private void findNeighbors()
  {
    if (!".".equals(m_previousItemName))
    {
      m_previousItem=rekuFindChild(m_masterCanvas, m_previousItemName);
    }
    if (!".".equals(m_nextItemName))
    {
      m_nextItem=rekuFindChild(m_masterCanvas, m_nextItemName);
    }
  }

  /**
   * Initializationof the AccordionButton. The data is given as a string, the
   * values are concatenated by |.
   * @param data
   */
  private void init(String data)
  {
    StringTokenizer st=new StringTokenizer(data, "|");
    if (st.hasMoreTokens())
    {
      m_buttonName=st.nextToken();
      //System.out.println("My name is " + m_buttonName);
    }
    String active="N";
    if (st.hasMoreTokens())
    {
      active=st.nextToken();
    }
    m_active="J".equals(active);
    if (st.hasMoreTokens())
    {
      m_previousItemName=st.nextToken();
    }
    if (st.hasMoreTokens())
    {
      m_nextItemName=st.nextToken();
    }
    if (st.hasMoreTokens())
    {
      String image=st.nextToken();
      m_expanded=loadImage(image);
    }
    if (st.hasMoreTokens())
    {
      String image=st.nextToken();
      m_collapsed=loadImage(image);
    }


    int dp=0;
    Container c=this;
    while (c.getParent()!=null && dp<2)
    {
      c=c.getParent();
      if (c instanceof DrawnPanel)
      {
        dp++;
      }
    }
    if (c instanceof DrawnPanel)
    {
      m_masterCanvas=(DrawnPanel)c;
    }
    m_dimension=this.getParent().getParent().getParent().getSize();
  }

  /**
   * Delegates the current maximum height to the next AccordionButon in the group
   * @param maxHeight
   */
  protected void scaleNext(int maxHeight)
  {
    // init Neighbours
    findNeighbors();
 
    m_minHeight=this.getX()+this.getHeight();
    m_maxHeight=maxHeight;
    if (m_active)
    {
      m_maxHeight=(int)m_dimension.getHeight()-m_minHeight;
    }
    if (m_nextItem!=null)
    {
      m_nextItem.scaleNext(m_maxHeight);
    } else if (m_previousItem!=null)
    {
      m_previousItem.scalePrev(m_maxHeight);
    }
  }

  /**
   * Delegates the current maximum height to the previous AccordionButon in the group
   * @param maxHeight
   */
  protected void scalePrev(int maxHeight)
  {
    m_maxHeight=maxHeight;
    if (m_previousItem!=null)
    {
      m_previousItem.scalePrev(m_maxHeight);
    }
  }


  /**
   * Searches the previous buttons if one of them is the active one,
   * if found, it will start the logic to deactivate (shrink) it
   *
   * @return true if one of the previous buttons was the active one
   * @param toMakeActive the button which should be activated
   */
  protected boolean closePrevActive(AccordionButton toMakeActive)
  {
    if (m_active)
    {
      m_mode=MODE_SHRINK;
      m_toMakeActive=toMakeActive;
      m_active=false;
      Thread t=new Thread(this);
      t.start();
      return true;
    }
    if (m_previousItem!=null)
    {
      return m_previousItem.closePrevActive(toMakeActive);
    }
    return false;
  }

  /**
   * Searches the following buttons if one of them is the active one,
   * if found, it will start the logic to deactivate (shrink) it
   *
   * @return true if one of the following buttons was the active one
   * @param toMakeActive the button which should be activated
   */
  protected boolean closeNextActive(AccordionButton toMakeActive)
  {
    if (m_active)
    {
      m_mode=MODE_SHRINK;
      m_toMakeActive=toMakeActive;
      m_active=false;
      Thread t=new Thread(this);
      t.start();
      return true;
    }
    if (m_nextItem!=null)
    {
      return m_nextItem.closeNextActive(toMakeActive);
    }
    return false;
  }


  /**
   * closes the active Accordion, either a previos or a following one
   */
  private void closeActive()
  {
    // Try to close to prev-direction
    if (!closePrevActive(this))
    {
      // Not found, close next active
      closeNextActive(this);
    }
  }

  /**
   * Standard Method, overwritten to make the bean-specific properties from forms
   * @return true
   * @param value
   * @param id    
   */
  public boolean setProperty(ID id, Object value)
  {
    if (id==INIT_ACCORDION)
    {
      //System.out.println(INIT_ACCORDION);    
      init((String)value);
      return true;
    } else if (id==SCALE_ACCORDION)
    {
      //System.out.println(SCALE_ACCORDION);    
      // Scale
      scaleNext(-1);
      // Adjust Initial Layout
      if (m_nextItem!=null)
      {
        m_nextItem.adjustPosition(this.getParent().getParent().getParent().getY()+this.getParent().getParent().getParent().getHeight());
      }
      return true;
    } else if (id==ACTIVATE)
    {
      processActionEvent(null);
      return true;
    } else
    {
      return super.setProperty(id, value);
    }
  }

  /**
   * Run-Method of the Runnable-interface, does the animation of
   * slowly shrinking or growing the canvas
   */
  public void run()
  {
    boolean done=false;
    int height=this.getParent().getParent().getHeight();
    //System.out.println("Start mit Höhe" + height);
    int offset=(m_mode==MODE_SHRINK ? -10 : 10);
    while (!done)
    {
      try
      {
        height=height+offset;
        if (m_mode==MODE_SHRINK && height<=m_minHeight)
        {
          height=m_minHeight;
          done=true;
        } else if (m_mode==MODE_EXPAND && height>=m_maxHeight+m_minHeight)
        {
          height=m_maxHeight+m_minHeight;
          done=true;
        }
        this.getParent().getParent().setSize((int)m_dimension.getWidth(), height);
        this.getParent().getParent().getParent().setSize((int)m_dimension.getWidth(), height);
        if (m_nextItem!=null)
        {
          m_nextItem.adjustPosition(this.getParent().getParent().getParent().getY()+this.getParent().getParent().getParent().getHeight());
        }
        Thread.sleep(10);
      } catch (Exception e)
      {
      }
    }
    if (m_mode==MODE_SHRINK && m_toMakeActive!=null)
    {
      // Now make the other one active
      m_toMakeActive.makeActive();
    }
    c_processing=false;
    ActionEvent e=new ActionEvent(this, ActionEvent.ACTION_PERFORMED, null);
    super.processActionEvent(e);
  }

  /**
   * Intercepty the standard WHEN-BUTTON-PRESSED and activates the
   * Accordion instead. The WHEN-BUTTON-PRESSED-event is deferred until
   * the current accordion has become active.
   * @param p0
   */
  protected void processActionEvent(ActionEvent p0)
  {
    // Nothing happens whn already active
    if (!m_active && !c_processing)
    {
      c_processing=true;
      // close the active item
      // This will in turn start the thread to open this accordion
      closeActive();
    }
  }

  /**
   * Name assigned to the button
   * @return Name of the button
   */
  public String getButtonName()
  {
    return m_buttonName;
  }

  /**
   * Set flags and start expanding thread
   */
  public void makeActive()
  {
    m_active=true;
    m_mode=MODE_EXPAND;
    m_toMakeActive=null;
    Thread t=new Thread(this);
    t.start();
  }
 
  /**
   * adjust the position of the stacked canvas and delegate to the following Accordion.
   * @param yPosition
   */
  protected void adjustPosition(int yPosition)
  {
    this.getParent().getParent().getParent().setLocation(this.getParent().getParent().getParent().getX(), yPosition);
    if (m_nextItem!=null)
    {
      m_nextItem.adjustPosition(this.getParent().getParent().getParent().getY()+this.getParent().getParent().getParent().getHeight());
    }
    
  }

  /**
   * Paint the button
   * @param g
   */
  public void paint(Graphics g)
  {
    g.setColor(getBackground());
    g.fillRect(getX()+1, getY()+1, getWidth()-2, getHeight()-2);
    getBorderPainter().paint(getPaintContext(),g, getX(), getY(), getWidth(), getHeight());
    int textpos=4;
    if (m_collapsed!=null || m_expanded!=null)
    {
      if (m_active && m_expanded!=null)
      {
        int yPos=((m_expanded.getIconHeight()<getHeight()) ? ((getHeight()-m_expanded.getIconHeight())/2) : 2);
        int height=((m_expanded.getIconHeight()<getHeight()) ? m_expanded.getIconHeight() : getHeight()-4);
        g.drawImage(m_expanded.getImage(), 4, yPos, m_expanded.getIconWidth(), height, null);
      } else if (!m_active && m_collapsed!=null)
      {
        int yPos=((m_collapsed.getIconHeight()<getHeight()) ? ((getHeight()-m_collapsed.getIconHeight())/2) : 2);
        int height=((m_collapsed.getIconHeight()<getHeight()) ? m_collapsed.getIconHeight() : getHeight()-4);
        g.drawImage(m_collapsed.getImage(), 4, yPos, m_collapsed.getIconWidth(), height, m_collapsed.getImageObserver());
      }
      textpos=textpos+Math.max(m_collapsed.getIconWidth(), m_expanded.getIconWidth());
    }
    g.setFont(getFont());
    g.setColor(getForeground());
    g.drawString(getLabel(), textpos, (getHeight()/2)+(g.getFontMetrics().getHeight()-g.getFontMetrics().getDescent())/2);
  }

  /**
   * Load the image given by name. Code is taken from oracle-demo RolloverButton
   * @return loaded Icon or null
   * @param imageName
   */
  private ImageIcon loadImage(String imageName)
  {
    // LoadImage, taken from oracle-demo RolloverButton
    URL imageURL = null;
    ImageIcon result=null;
    boolean loadSuccess=false;
    if ("DEFAULT_EXPANDED".equals(imageName))
    {
      result=IMG_EXPANDED;
    } else if ("DEFAULT_COLLAPSED".equals(imageName))
    {
      result=IMG_COLLAPSED;
    } else if (!".".equals(imageName))
    {
      //JAR
      imageURL = getClass().getResource("/"+imageName);
      if (imageURL != null)
      {
        try
        {
          result = new ImageIcon(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
            {
              result= new ImageIcon(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
            {
              result= new ImageIcon(createImage((java.awt.image.ImageProducer) imageURL.getContent()));
              loadSuccess = true;
            }
            catch (Exception ilex)
            {
            }
          }
          catch (java.net.MalformedURLException urlex)
          {
          }
        }
      }
    }
    return result;
  }

}

 

And here's the forms-code

 

PACKAGE PK_ACCORDION IS
/**
   
    This is just sample code, its free to use.
    It is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

    The code relies on the internal structure of forms when rendering stacked canvases
    It is tested against Forms 10.1.2.0.2, but may stop working with any patch or future version of forms

    Sample code for a forms-Side Package for the Accordion Java-Bean

    To build up an accordion, follow these steps
   
    -Create as many stacked canvases as you want to have accordion-canvases
    -Create a Button on each canvas at position 0,0 with the full width of the canvas and
     set the Implementation class for that buttons to forms.AccordionButton
    -Arrange these canvases on one content canvas, so that all canvases are placed beneath each other
     +The viewport for one canvas should be so that the canvas is fully shown
     +The viewport for all other canvases should be so that just the button is visible.
    
    -Initialize the Accordion in the WHEN-NEW-FORM-INSTANCE-trigger with code lik
   
        DECLARE
            lAccordionList PK_ACCORDION.tAccordionList;
            rAccordion     PK_ACCORDION.tAccordion;
        BEGIN
            rAccordion.vcCanvas:='CANVAS1';
            rAccordion.vcButton:='BLOCK.BUTTON1';
            rAccordion.vcExpandedImage:=PK_ACCORDION.VCC_DEFAULT_EXPANDED_IMAGE;
            rAccordion.vcCollapsedImage:=PK_ACCORDION.VCC_DEFAULT_COLLAPSED_IMAGE;
            rAccordion.bOpened:=TRUE;
            lAccordionList(1):=rAccordion;
   
                rAccordion.vcCanvas:='CANVAS2';
                rAccordion.vcButton:='BLOCK.BUTTON2';
                rAccordion.vcExpandedImage:=PK_ACCORDION.VCC_DEFAULT_EXPANDED_IMAGE;
                rAccordion.vcCollapsedImage:=PK_ACCORDION.VCC_DEFAULT_COLLAPSED_IMAGE;
                rAccordion.bOpened:=FALSE;
                lAccordionList(2):=rAccordion;
   
          PK_ACCORDION.PR_INIT_ACCORDION('MAIN', lAccordionList);
        END;
   
    -make sure the jar is on the archive or achive_jini-tag   
   
*/
  -- constants used for Default-Images conatined in the jar-file
  VCC_DEFAULT_EXPANDED_IMAGE  CONSTANT VARCHAR2(30):='DEFAULT_EXPANDED';
  VCC_DEFAULT_COLLAPSED_IMAGE CONSTANT VARCHAR2(30):='DEFAULT_COLLAPSED';
 
  -- Type which describes one Accordion
  TYPE tAccordion IS RECORD (
    vcCanvas         VARCHAR2(30), -- the Canvas which represents the Accordion-area
    vcButton         VARCHAR2(61), -- the "Activation"-button on that canvas which is used to activate the Accordion
    vcExpandedImage  VARCHAR2(255),-- image-name for Expanded-state, image must be accessible by forms
    vcCollapsedImage VARCHAR2(255),-- image-name for Collapsed-state, image must be accessible by forms
    bOpened          BOOLEAN       -- Flag, if this accordion is the one that is displayed at startup
                                        -- Must match the canvas. The canvases area beneath the button is taken
                                        -- as the area which is given to other accordions when opened
  );
 
  -- Table-Type of Accordion-records
  TYPE tAccordionList IS TABLE OF tAccordion INDEX BY BINARY_INTEGER;
 
  /** Initialization-method for an accordion group
      i_vcAccordionGroup indicates a logical name. It gives the capability of having several Accordion-groups inside one form.
      i_lAccordionList   is a list of the Accordion-entries which belong to the accordion-group
  */   
  PROCEDURE PR_INIT_ACCORDION(i_vcAccordionGroup IN VARCHAR2, i_lAccordionList IN tAccordionList);
 
  /** method to activate a specific accordion programmatically
      i_vcAccordionGroup indicates a logical name. Must be a name which has been initialized via PR_INIT_ACCORDION before
      i_vcCanvas         is the name of the canvas in one of the accordion-entries in the group
  */   
  PROCEDURE PR_ACTIVATE(i_vcAccordionGroup IN VARCHAR2, i_vcCanvas IN VARCHAR2);

  /** method to go to an item placed on an accordion-canvas.
      i_vcItem Name of the item which should get the focus
     
      Thanks to Francois Degrelle for supplying the code
  */   
  PROCEDURE PR_GO_ITEM(i_vcItem IN VARCHAR2);

  /** method to go to a block the first item of which is placed on an accordion-canvas.
      i_vcBlock Name of the block which should get the focus
     
      Thanks to Francois Degrelle for supplying the code
  */   
  PROCEDURE PR_GO_BLOCK(i_vcBlock IN VARCHAR2);
 
 
END;

PACKAGE BODY PK_ACCORDION IS

  TYPE tList IS TABLE OF tAccordionList INDEX BY VARCHAR2(30);
  lList tList;

  -- --------------------------------------------------------------------------------------
 
  PROCEDURE PR_INIT_ACCORDION(i_vcAccordionGroup IN VARCHAR2, i_lAccordionList IN tAccordionList) IS
    vcPrior VARCHAR2(4000);
    vcNext  VARCHAR2(4000);
  BEGIN
      lList(i_vcAccordionGroup):=i_lAccordionList;
      FOR i IN 1..i_lAccordionList.COUNT LOOP
          -- prior Accordion-entry
          IF i>1 THEN
              vcPrior:=i_lAccordionList(i-1).vcButton;
          ELSE
              vcPrior:='.';
          END IF;
          -- following Accordion-entries
          IF i<i_lAccordionList.COUNT THEN
            vcNext:=i_lAccordionList(i+1).vcButton;
          ELSE
              vcNext:='.';
          END IF;
          SET_CUSTOM_ITEM_PROPERTY(i_lAccordionList(i).vcButton, 'INIT_ACCORDION', i_lAccordionList(i).vcButton || '|' ||
                                                                                             CASE WHEN i_lAccordionList(i).bOpened THEN
                                                                                               'J'
                                                                                             ELSE
                                                                                                 'N'
                                                                                             END     || '|' || 
                                                                                             vcPrior || '|' || 
                                                                                             vcNext  || '|' ||
                                                                                             NVL(i_lAccordionList(i).vcExpandedImage, '.')  || '|' ||
                                                                                             NVL(i_lAccordionList(i).vcCollapsedImage, '.') || '|'
                                            );
    END LOOP;
    SYNCHRONIZE;
    -- Make first item scale, it will delegate to others
    SET_CUSTOM_ITEM_PROPERTY(i_lAccordionList(1).vcButton, 'SCALE_ACCORDION', ' ');
  END;
 
  -- --------------------------------------------------------------------------------------
 
  PROCEDURE PR_ACTIVATE(i_vcAccordionGroup IN VARCHAR2, i_vcCanvas IN VARCHAR2) IS
    lAccordionList tAccordionList;
  BEGIN
    IF lList.EXISTS(i_vcAccordionGroup) THEN
        lAccordionList:=lList(i_vcAccordionGroup);
        FOR i IN 1..lAccordionList.COUNT LOOP
            IF lAccordionList(i).vcCanvas=i_vcCanvas THEN
                SET_CUSTOM_ITEM_PROPERTY(lAccordionList(i).vcButton, 'ACTIVATE', ' ');
                EXIT;
            END IF;
        END LOOP;
    END IF;
  END;
     
  -- --------------------------------------------------------------------------------------
 
  PROCEDURE PR_GO_ITEM(i_vcItem IN VARCHAR2) IS
    lAccordionList tAccordionList;
    vcCanvas       VARCHAR2(100);
    vcGroup        VARCHAR2(30);
    itId           ITEM;
  BEGIN
    -- find the item canvas --
    itId:=FIND_ITEM(i_vcItem);
    IF NOT ID_NULL(itId) THEN
      vcCanvas:=GET_ITEM_PROPERTY(itId, ITEM_CANVAS ) ;
      -- find the accordion group --
      vcGroup:=lList.FIRST ;
      LOOP
        EXIT WHEN vcGroup IS NULL;
        lAccordionList:=lList(vcGroup);
        FOR i IN 1..lAccordionList.COUNT LOOP
          IF UPPER(lAccordionList(i).vcCanvas) = vcCanvas THEN
            PR_ACTIVATE(vcGroup, vcCanvas);
            GO_ITEM(i_vcItem);
            RETURN;
          END IF;
        END LOOP;
        vcGroup:=lList.NEXT(vcGroup) ;
      END LOOP;
    END IF;
  END;
  
  -- --------------------------------------------------------------------------------------
 
  PROCEDURE PR_GO_BLOCK(i_vcBlock IN VARCHAR2) IS
    lAccordionList tAccordionList;
    vcCanvas       VARCHAR2(100);
    vcGroup        VARCHAR2(30);
    vcItem         VARCHAR2(61);
    blId           BLOCK;
    bOk            BOOLEAN:= FALSE ;
  BEGIN
    -- find the canvas --
    blId := FIND_BLOCK(i_vcBlock);
    IF NOT ID_NULL(blId) THEN
      vcItem:= GET_BLOCK_PROPERTY(blId, FIRST_ITEM);
      LOOP
        vcCanvas:= GET_ITEM_PROPERTY(vcItem, ITEM_CANVAS ) ;
        IF vcCanvas IS NOT NULL THEN
          bOk:=TRUE;
          EXIT;
        END IF ;
        vcItem:= GET_ITEM_PROPERTY(vcItem, NEXTITEM ) ;
        EXIT WHEN vcItem IS NULL;
      END LOOP;
      IF bOk THEN
        -- find the accordion group --
        vcGroup:=lList.FIRST;
        LOOP
          EXIT WHEN vcGroup IS NULL;
          lAccordionList:=lList(vcGroup);
          FOR i IN 1..lAccordionList.COUNT LOOP
            If UPPER(lAccordionList(i).vcCanvas)=vcCanvas THEN
              PR_ACTIVATE(vcGroup, vcCanvas);
              GO_BLOCK(i_vcBlock);
              RETURN;
            END IF;
          END LOOP;
          vcGroup:=lList.NEXT(vcGroup);
        END LOOP;
      END IF;
    END IF ;
  END PR_GO_BLOCK; 
END;

 

This video shows the usage of the bean with the Oracle demo-tables EMPLOYEES and DEPARTMENTS

 

And this one is just a show-case which shows the usage of several accordions inside one form, including a short tutorial

A compiled version with a demo-fmb can be found at Francois Degrelle's PJC-site here
Weiterlesen

Making the Statusbar more "visible"

18. September 2010 , Geschrieben von Andreas Weiden

The task

One of the things users always complain about is, that errors which occur or messages which are shown are not recognized. For some reason, the statusbar visible by default at the bottom of a forms-application seems to be invisible or at least somehow encrypted to the enduser. So, inspired by a recently question on the forms-forum at OTN, i tried to find a way to make showing messages to be more "recognizable".

The idea

Best way to get someones attention in an application is to use some visual effect, like coloring. So my approach was to

  • get hand on the statusbar object
  • at some specific event triggered from forms, colorize the statusbar
  • somehow remove the coloreffect again, so that the next time someone should pay attention i can activate the color-effect again.
The implementation

So, i started writing a little javabean. There is already a javabean by Francois Degrelle (Personalize.java) which can identify the Statusbar-object. So, all i had to do was

  • define an event to be sent from forms to my bean, which initializes the coloring of the formerly identified statusbar
  • write a little Thread-class which colors the statusbar and then slowly fades out the color back to its original value.

Heres the code

package forms;
import java.awt.Color;
import java.awt.Component;
import java.awt.Container;
import java.lang.reflect.Method;
import oracle.ewt.statusBar.StatusBar;
import oracle.forms.engine.Main;
import oracle.forms.handler.IHandler;
import oracle.forms.ui.VBean;
import oracle.forms.ui.VTextField;
import java.beans.PropertyChangeEvent;
import java.beans.PropertyChangeListener;
import oracle.forms.properties.ID;


/**
 * Sample-Code for a java-Bean that colors the Statusbar green, yellow or red and then slowly fades out the color
 * back to the original color
 */
public class StatusBarFlasher extends VBean
{
  /** Different Type */
  public static final ID ERROR   =ID.registerProperty("ERROR");
  public static final ID WARNING =ID.registerProperty("WARNING");
  public static final ID MESSAGE =ID.registerProperty("MESSAGE");
  /** The statusbar-object */
  private static StatusBar m_statusBar=null;
  /** Handler-Object */
  private IHandler m_handler=null;
  /** Curent fader-alpha */
  int fadervalue=0;


  /**
   * Store the handler locally
   * @param handler
   */
  public void init(IHandler handler)
  {
    super.init(handler);
    m_handler=handler;
  } 
 
  /**
   * Recursive search for the statusbar-object, inspired by Francois Degrelles Personalize.java
   * @param ct
   */
  private void findStatusBar(Container ct)
  {
    Component cp[] = ct.getComponents() ;
    int i=0;
    while (m_statusBar==null && i<cp.length)
    {
      if(m_statusBar==null && (cp[i] instanceof StatusBar))
      {
        m_statusBar = (oracle.ewt.statusBar.StatusBar)cp[i] ;
      }
      try
      {
        findStatusBar((Container)cp[i]);
      }
      catch (Exception e)
      {
      }
      i++;
    }
  }

  /**
   * Initialize and find the Statusbar, if not already done
   */
  public void init()
  {
    if (m_statusBar==null)
    {
      try
      {
        Method method = m_handler.getClass().getMethod("getApplet", new Class[0]);
        Object applet = method.invoke(m_handler, new Object[0]);
        if (applet instanceof Main)
        {
          findStatusBar(((Main)applet).getFrame());
        }     
      } catch(Exception ex)
      {
      }   
    }
  }

  /**
   * Overwritten method acceptingproeprties from forms
   * @return
   * @param id Properties ID
   * @param p0 value
   */
  public boolean setProperty(ID id, Object value)
  {
    if (id==ERROR)
    {
      init();
      if (m_statusBar!=null)
      {
        if (fadervalue==0)
        {
          StatusBarFlasher.Fader fader=new StatusBarFlasher.Fader(255,0,0);
          fader.start();
        } else
        {
          fadervalue=255;
        }
      }
      return true;
    } else if (id==WARNING)
    {
      init();
      if (m_statusBar!=null)
      {
        if (fadervalue==0)
        {
          StatusBarFlasher.Fader fader=new StatusBarFlasher.Fader(255, 255, 0);
          fader.start();
        } else
        {
          fadervalue=255;
        }
      }
      return true;
    } else if (id==MESSAGE)
    {
      init();
      if (m_statusBar!=null)
      {
        if (fadervalue==0)
        {
          StatusBarFlasher.Fader fader=new StatusBarFlasher.Fader(0, 255, 0);
          fader.start();
        } else
        {
          fadervalue=255;
        }
      }
      return true;
    } else
    {
      /** Delegate */
      return super.setProperty(id, value);
    }
  }
 
  /**
   * Local class to color the Statusbar where messages are shwon and slowly fading
   * out the color by reducing the alpha-value
   */
  class Fader extends Thread
  {
    /** The base-Color */
    private int m_r;
    private int m_g;
    private int m_b;
   
   
    /**
     * Constructor taken the base-color as rgb-color-values
     * @param b
     * @param g
     * @param r
     */
    public Fader(int r, int g, int b)
    {
      m_r=r;
      m_g=g;
      m_b=b;
    }
   
   
    /**
     * The Run-methode
     */
    public void run()
    {
      Color cc=m_statusBar.getBackground();
      fadervalue=255;
      while (fadervalue>0)
      {
        Color c=new Color(m_r, m_g, m_b, fadervalue);
        m_statusBar.setBackground(c);
        fadervalue=fadervalue-5;
        try
        {
          Thread.sleep(50);
        } catch (Exception e)
        {
        }
      }
      m_statusBar.setBackground(cc);
    }
  }
}
The forms side

Using the javabean is as with any java-bean.

  • Compile it, create a jar of it and put that jar in your archive or archice_jni-tag in the formsweb.cfg of your application
  • Create a Bean-object in your form, setting the implementation class to forms.StatusBarFlasher

Finally, you need to invoke the flashing effect. As the bean is used to signal errors, you can put something like the following in your own ON-ERROR-trigger, or add the SET_CUTOM_PROPERTY-part to an existing errorhandler

message(ERROR_TYPE || '-' || ERROR_CODE || ': ' || ERROR_TEXT);
SET_CUSTOM_ITEM_PROPERTY('B.BEAN', 'ERROR', '');

Use the property

  • ERROR for red color,
  • WARNING for yellow color,
  • MESSAGE for green color.

Well, thats it.

 

P.S.

Francois Degrelle build a readmade jar-file that can be used directly, get it from here

 

 

 

Weiterlesen

Building a treetable-like form (part 2)

5. August 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

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.

An example

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

The approach

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
 

1 Expanded King
32 Collapsed Zlotkey
32 Collapsed Weiss
32 Collapsed Vollman
32 Expanded Russel
342 Collapsed Tuvault
...    


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.

Fetching the data

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

 

MASTER ID of the master-record
DETAIL ID of the record
ICON Icon-Name to be rendered in the tree
LABEL additional text next to the icon


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.

 

NEXTDETAIL is the master of the next record
NO_OF_SAME_LEVEL is the number of rows on the same level so far
COUNT_OF_SAME_LEVEL is the total number of rows on the same level


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 javabean

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);
    }
  }
}

 

 

The forms-part

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.
 

 

 

 

Weiterlesen

Building a treetable-like form

10. April 2009 , Geschrieben von Andreas Weiden

The task

I made myself familiar with ADF two years ago. Doing so i also had a look at the different components provided by the ADF-faces-UI. I found one component named TreeTable very nice and useful. A TreeTable component shows hierarchical data in a tabular way, where the first column of the table "contains" the hierarchy and can be used to expand, collapse and drill into the data. The big difference against a simple tree is that the hierarchical part of the data is "synchronized" with the tabular data. So, when scrolling through the data, the tree-part moves along with the table-part.

So, as an "old" forms-developer, i tried to create a similar functionality in forms.

An example

The HR-schema is a good example, for the EMPLOYEES-table provided with the schema contains a hierarchical structure. Each employee has assigned a manager, which in turn also is an employee again. The "top-managers", which are the topmost-level in the hierarchy do not have a manager assigned. So as an example we will build a form which shows all the employees in a tabular view with the possibility to expand and collapse each row so that the "detail"-employees of an employee are shown/hidden in the same block.

The approach

The hierarchical query is not a problem to implement, but how deal with the dynamic loading and hiding of details to an already queried record. I played a while trying to create missing records "by hand" and integrate them into the already queried data in the block, but if found it to be to complicated  (at least, when the block should be updateable). So i tried a different approach:
Whenever a record should be expanded or collapsed, the whole block gets requeried and the cursor gets navigated to the previously select record.
The query has to be adjusted every time the block is requeried so that it contains only the data which should be shown in the block. With each requery, there is either one master expanded, which in turn leads to all detail-employees have to be included in the query, or one master is collapsed, which leads to all detail-employees to be excluded from the query.

To do so, i decided to use an table-type-object containing all id's of the "expanded" employees, and this table-type-object then can be included in the query. To store the instance of the object i use a database-package.

Last part is to include or exclude a master-id to this object from the form.

The implementation

First part is to create an object-type to hold the id's:

CREATE OR REPLACE TYPE T_IDLIST AS TABLE OF NUMBER;

Second part is to create a package to "store" an instance of this object-type, which also includes methods to add an id to the type and to remove it again.

CREATE OR REPLACE PACKAGE PK_TREETABLE IS

  -- Method to reset the list
  PROCEDURE PR_RESET;

  -- Method to retrieve the object
  FUNCTION FK_GET_EXPANDED
  RETURN T_IDLIST;

  -- Method to toggle the given id
  -- If the id is not in the list, include it, else exclude it
  PROCEDURE PR_TOGGLE(i_nId IN NUMBER);

END;

And the body

CREATE OR REPLACE PACKAGE BODY PK_TREETABLE IS

  oIds T_IDLIST:=T_IDLIST(-1);

  PROCEDURE PR_RESET IS
  BEGIN
    -- re-Instantiate the type, given only -1 for the root
    oIds:=T_IDLIST(-1);
  END;

  FUNCTION FK_GET_EXPANDED
  RETURN T_IDLIST IS
  BEGIN
    -- return the internal instance
    RETURN oIds;
  END;

  PROCEDURE PR_TOGGLE(i_nId IN NUMBER) IS
    bFound BOOLEAN:=FALSE;
    iPos PLS_INTEGER;
  BEGIN
    -- check if, the given id is part of the list
      iPos:=oIds.FIRST;
    LOOP
        EXIT WHEN iPos IS NULL;
      IF oIds(iPos)=i_nId THEN
        -- found, delete it
        oIds.DELETE(iPos);
        -- and remember
        bFound:=TRUE;
      END IF;
      iPos:=oIds.NEXT(iPos);
    END LOOP;
    -- If not found
    IF NOT bFound THEN
      -- add it to the list
        oIds.EXTEND(1);
        oIds(oids.COUNT):=i_nId;
    END IF;
  END;
END;

Now we need the view to base our forms-block on

CREATE OR REPLACE VIEW V_EMP
(
  STATUS,
  MASTER,
  EMPLOYEE_ID,
  LAST_NAME,
  FIRST_NAME,
  EMAIL,
  HIRE_DATE
) AS
SELECT CAST('?' AS VARCHAR2(200)) STATUS,
       NVL(MANAGER_ID,-1) MASTER,
       EMPLOYEE_ID,
       LAST_NAME,
       FIRST_NAME,
       EMAIL,
       HIRE_DATE
  FROM EMPLOYEES
 WHERE NVL(MANAGER_ID,-1) IN (SELECT COLUMN_VALUE
                                FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
                             )
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
ORDER SIBLINGS BY LAST_NAME

Now, we create a simple tabular form based on that view and implement a WHEN-MOUSE-DOUBLECLICK-trigger which does the toggling of the id in the package and the requery-part:

PK_TREETABLE.PR_TOGGLE(:V_EMP.EMPLOYEE_ID);
EXECUTE_QUERY;
Showing some treelike status

For now, there is a status-field showing just a question-mark. Lets make it a little nicer by showing a + when a node is collapsed and a minus when it is expanded. We have to adjust the view for that:

CREATE OR REPLACE VIEW V_EMP
(
  STATUS,
  MASTER,
  EMPLOYEE_ID,
  LAST_NAME,
  FIRST_NAME,
  EMAIL,
  HIRE_DATE
) AS
SELECT RPAD(' ', LEVEL-1, ' ') ||
       CASE WHEN SELECTED_EMP IS NULL THEN
         '+'
       ELSE
         '-'
       END STATUS,
       NVL(MANAGER_ID,-1) MASTER,
       EMPLOYEE_ID,
       LAST_NAME,
       FIRST_NAME,
       EMAIL,
       HIRE_DATE
  FROM EMPLOYEES,
       (SELECT COLUMN_VALUE SELECTED_EMP
          FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
       )
 WHERE NVL(MANAGER_ID,-1) IN (SELECT COLUMN_VALUE
                                FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
                             )
   AND EMPLOYEE_ID=SELECTED_EMP(+)
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
ORDER SIBLINGS BY LAST_NAME
The missing parts

The GUI for the tree is not satisfying by now, we have to deal with some additional requirements:

  • Show the expand and collapse nodes in a graphical way

  • Show tree-lines

  • Show icons for each node


I'm curently working on building a java-bean for this, i'll show the results in my next post.

Weiterlesen

Working with trees

21. März 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

Trees are a nice feature to present hierarchical data. And, since forms 5 they are available as standard components in forms. A lot of forum posts are related to trees and the way they are populated. I want to show some different techniques to populate a tree and a way to interact with "standard" data-blocks.

Sample

Lets build a form with a tree showing the employee-hierarchie and a "standard" datablock, showing all the employees which have the selected employee in the tree as manager.

Building the basic form

First, we build a datablock EMP based on the table employees with a tabular layout. Then create another datablock BL_TREE with property "Single Record" set to true and create a tree-item in it, lets name it IT_TREE. In the layout we place the tree at the left of the tabular layout, so that it looks like the explorer.

Populating the tree - using a fixed query

The easiest way to populate a tree is by defining a query with a specific result-set-structure and just put that query in the property "Data Query" of the tree.

The query has to be defined so that it has the following result-columns

column-name

meaning

NODE_STATE

defines, how the node is initially shown in the tree
-1 = collapsed
 0 = leaf-node
 1 = expanded

NODE_DEPTH

The tree-level the row it at. This column defines the hierarchy of the tree-data

NODE_LABEL

The text to be shown in the tree-node

NODE_ICON

The name of an icon-file which should be rendered for the treenode. The icon must be accessible to forms as any icon used, e.g. on a button

NODE_VALUE

The "value" to be stored for the tree-node

 

 

 

 

 

 

 

 

 

 

Our example-table EMPLOYEES already has a hierarchical structure, each employee has a manager assigned via the MANAGER_ID, where the referenced manager is an employee him (or her)self.
So, our query look like

SELECT -1                              NODE_STATE,
       LEVEL                           NODE_DEPTH,
       LAST_NAME || ', ' || FIRST_NAME NODE_LABEL,
       NULL                            NODE_ICON,
       EMPLOYEE_ID                     NODE_VALUE
  FROM EMPLOYEES
CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
START WITH MANAGER_ID IS NULL
ORDER SIBLINGS BY LAST_NAME

One last thing is to do, we have to populate the tree on startup of the form. So we create a WHEN-NEW-FORM-INSTANCE-trigger with the following code

Ftree.POPULATE_TREE('BL_TREE.IT_TREE');
Populating the tree - using a query which may change at runtime

A quite similar approach is to use a record-group as base-data for the tree. The structure of the data has to be the same. But, with a recordgroup you can change the query at runtime, e.g. when some userinput influences the data to be shown in the tree.

For this approach, we create a recordgroup named RG_TREE with the same query as in the last approach and assign it to the tree using the property "Record Group"

The population could also be done in the WHEN-nEW-FORM-INSTANCE-trigger, but because of the idea of re-populating it under certain condition, we put the logic in a procedure and just call that from the trigger. Here's the procedure

PROCEDURE PR_TREE IS
  rgTree  RECORDGROUP;
  vcQuery VARCHAR2(2000);
  nStatus NUMBER;
  itTree  ITEM := FIND_ITEM ('BLOCK.TREE');
BEGIN
  rgTree := FIND_GROUP ('RG_TREE');
  -- Clear Tree
  Ftree.DELETE_TREE_NODE (itTree, Ftree.ROOT_NODE);
  -- Build up a new Query, if desired
  vcQuery := 'SELECT -1                              NODE_STATE,' ||
             '       LEVEL                           NODE_DEPTH,' ||
             '       LAST_NAME || ', ' || FIRST_NAME NODE_LABEL,' ||
             '       NULL                            NODE_ICON,' ||
             '       EMPLOYEE_ID                     NODE_VALUE' ||
             '  FROM EMPLOYEES' ||
             ' CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID' ||
             ' START WITH MANAGER_ID IS NULL' ||
             ' ORDER SIBLINGS BY LAST_NAME';

  -- Repopulate Record-Group
  nStatus := POPULATE_GROUP_WITH_QUERY (rgTree, vcQuery);
  -- Repopulate Tree
  Ftree.POPULATE_TREE(itTree);
END;
Populating the tree - using PL/SQL and FTree

The third way to populate a tree is to do everything "by hand". All functionality to interact with a tree are included in the standard-package Ftree. So, there is also a function to add a new node to a tree.

The logic is encapsulated in the following package. There's a procedure PR_FILL_TREE to add the top-level-nodes to the tree (The managers which do not have managers themselves), and, for every manager, the procedure PR_QUERY_DETAILS to read the employees of this manager. And, because each of these can also be a manager the procedure recursivela calls itself. Here's the code

PACKAGE PK_TREE IS
 
  PROCEDURE PR_FILL_TREE;
 
END;

PACKAGE BODY PK_TREE IS
 
  itTree ITEM:=FIND_ITEM('BL_TREE.IT_TREE');
 
  FUNCTION FK_ADD_NODE(i_ndMaster IN FTree.NODE,
                       i_vcValue  IN VARCHAR2,
                       i_vcLabel  IN VARCHAR2,
                       i_vcIcon   IN VARCHAR2 DEFAULT NULL,
                       i_nState   IN NUMBER DEFAULT Ftree.EXPANDED_NODE
                      )
  RETURN Ftree.NODE IS
  BEGIN
      RETURN Ftree.Add_Tree_Node(itTree,
                                 i_ndMaster,
                                 Ftree.PARENT_OFFSET,
                                 Ftree.LAST_CHILD,
                                 i_nState,
                                 i_vcLabel,
                                 i_vcIcon,
                                 i_vcValue);
  END;
 
  PROCEDURE PR_QUERY_DETAILS(i_ndMaster   IN Ftree.NODE,
                             i_nManagerId IN NUMBER) IS
    CURSOR crDetails IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID=i_nManagerId
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
      FOR rec IN crDetails LOOP
          nd:=FK_ADD_NODE(i_ndMaster, rec.VALUE , rec.LABEL);
          PR_QUERY_DETAILS(nd, rec.VALUE);
      END LOOP;
  END;
 
 
  PROCEDURE PR_FILL_TREE IS
    CURSOR crTop IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID IS NULL
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
      Ftree.DELETE_TREE_NODE(itTree, FTree.ROOT_NODE);
      FOR rec IN crTop LOOP
          nd:=FK_ADD_NODE(Ftree.ROOT_NODE, rec.VALUE , rec.LABEL);
          PR_QUERY_DETAILS(nd, rec.VALUE);
      END LOOP;
  END;
END;

Now, in the WHEN-NEW-FORM-INSTANCE-trigger we just call

PK_TREE.PR_FILL_TREE;
Large trees - using partial tree population

Whats the advantage of doing all this "handwork" if it can be done in a single query?
Sometimes it's not possible to put all the logic into a single query, so you have todo it manually. Another possible situation is that you have a large tree with lots of nodes and the population would last very long if all the data would be read at startup. In this case, a partial population can increase performance. The idea behind this is, just read the top-level at startup and collapse all these node, and, at the moment the user expands a node, query the next level of data into that node.

We already have a procedure to populate the top-level and also one for populating the "children" under a master-node. All we need to do is have some event when to query required data and a marker, if the details already have been read. We do this by simply putting a minus in front of the value for "unpopulated" children and remove that minus at the moment the details are queried. The event to populate the details is the moment when the user expands a node, so we use the appropiate trigger, the WHEN-TREE-NODE-EXPANDED-trigger.

So, lets modify the code a litte bit.

PACKAGE PK_TREE IS
 
  PROCEDURE PR_FILL_TREE;
 
  PROCEDURE PR_WTNE(i_ndNode IN Ftree.NODE);
 
END;

PACKAGE BODY PK_TREE IS
 
  itTree ITEM:=FIND_ITEM('BL_TREE.IT_TREE');
 
  FUNCTION FK_ADD_NODE(i_ndMaster IN FTree.NODE,
                       i_vcValue  IN VARCHAR2,
                       i_vcLabel  IN VARCHAR2,
                       i_vcIcon   IN VARCHAR2 DEFAULT NULL,
                       i_nState   IN NUMBER DEFAULT Ftree.EXPANDED_NODE
                      )
  RETURN Ftree.NODE IS
  BEGIN
    RETURN Ftree.Add_Tree_Node(itTree,
                               i_ndMaster,
                               Ftree.PARENT_OFFSET,
                               Ftree.LAST_CHILD,
                               i_nState,
                               i_vcLabel,
                               i_vcIcon,
                               i_vcValue);
  END;
 
  PROCEDURE PR_QUERY_DETAILS(i_ndMaster   IN Ftree.NODE,
                             i_nManagerId IN NUMBER) IS
    CURSOR crDetails IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID=i_nManagerId
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
    FOR rec IN crDetails LOOP
      -- add the node with a negative id
      nd:=FK_ADD_NODE(i_ndMaster, -rec.VALUE , rec.LABEL);
      -- don't add details here
    END LOOP;
  END;
 
  PROCEDURE PR_FILL_TREE IS
    CURSOR crTop IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID IS NULL
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
    Ftree.DELETE_TREE_NODE(itTree, FTree.ROOT_NODE);
    FOR rec IN crTop LOOP
      -- add the node with a negative id
      nd:=FK_ADD_NODE(Ftree.ROOT_NODE, -rec.VALUE , rec.LABEL, NULL, FTree.COLLAPSED_NODE);
      -- don't add details here
    END LOOP;
  END;
 
  PROCEDURE PR_WTNE(i_ndNode IN Ftree.NODE) IS
    nValue   NUMBER;
    ndDetail FTree.NODE;
  BEGIN
    -- Get the value of the node to be expanded
    nValue:=FTree.GET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_VALUE);
    IF nValue<0 THEN
      -- value is negative, then we have to read the details
      PR_QUERY_DETAILS(i_ndNode, ABS(nValue));
      -- Now set the value of the node to positive, so that at next expansion we won't re-read the details
      FTree.SET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_VALUE, ABS(nValue));
      ndDetail:=FTree.FIND_TREE_NODE(itTree,'',FTREE.FIND_NEXT, FTREE.NODE_LABEL,i_ndNode,i_ndNode); 
      -- if there we're no details, set node as lead,
      IF Ftree.ID_NULL(ndDetail) THEN
        FTree.SET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_STATE, FTree.EXPANDED_NODE);   
      ELSE
        -- otherwise set node to expanded
        FTree.SET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_STATE, FTree.EXPANDED_NODE);   
      END IF;
    END IF;
  END;
 
END;

At the tree-item, we have to create an additional WHEN-TREE-NODE-EXPANDED-trigger which calls our new procedure

PK_TREE.PR_WTNE(:SYSTEM.TRIGGER_NODE);

Important:
To have this working, you must set the property "Allow Empty Branches" at the treeitem to "Yes".

Synchronizing the data block

The last requirement is that whenever the user selects an entry in the tree, all employees who have the selected employee in the tree as manager should be queried in the block at the right.

We enhance the PK_TREE for this with a procedure PR_WTNS and create a WHEN-TREE-NODE-SELECTED-trigger at the tree-item which calls that new procedure for this:
 

  PROCEDURE PR_WTNS(i_ndNode IN Ftree.NODE) IS
    nValue NUMBER; 
  BEGIN
    IF :SYSTEM.TRIGGER_NODE_SELECTED='TRUE' THEN
        -- Get the Employee-id, remeber that we made it negative
        nValue:=ABS(FTree.GET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_VALUE));
        -- Restrict block
        SET_BLOCK_PROPERTY('EMPLOYEES', ONETIME_WHERE, 'MANAGER_ID=' || TO_CHAR(nValue));
        GO_BLOCK('EMPLOYEES');
        -- execute block
        EXECUTE_QUERY;
    END IF;
  END;



 

Weiterlesen
<< < 1 2 3 4 > >>