Wednesday Feb 22, 2012

Dynamic Pie Graph Generation by Drag And Drop Rows from ADF Table

In normal scenarios, we display the graph and chart to get graphical representation of data. In this article, we'll see how to create graph dynamically by dragging the contents from ADF table and dropping on the graph.

Environment : JDeveloper PS5 (11.1.1.6.0)

Assuming that we have a table that displays the Department's details. We'll build a dynamic pie graph to display the Employee details for the departments that are dragged from the Department's table.

DeptEmpChart.jpg



For our usecase, we need to drag the Departments from Table and Drop it on the Pie Chart to get the Employees details corresponding to the Depts selected.

For this, we need to add DragSource in the Dept Table and DropTarget in Emp Chart.

<af:table value="#{bindings.DeptView1.collectionModel}" var="row"
                 rows="#{bindings.DeptView1.rangeSize}"
                 emptyText="#{bindings.DeptView1.viewable ? 'No data to display.' : 'Access Denied.'}"
                 fetchSize="#{bindings.DeptView1.rangeSize}"
                 rowBandingInterval="0"
                 selectionListener="#{bindings.DeptView1.collectionModel.makeCurrent}"
                 rowSelection="multiple" id="t1">
            <af:dragSource discriminant="Dept" 
                           defaultAction="COPY"/>           
            ....
            ....
                  <dvt:pieGraph id="pieGraph1" subType="PIE_MULTI"
                                customLayout="CL_NONE">
                    <af:dropTarget>
                      <af:dataFlavor flavorClass="org.apache.myfaces.trinidad.model.RowKeySet"
                                     discriminant="Dept"/>
                    </af:dropTarget>
          ....
          ....

Now that we've added the drag source and drop target, we need to have a method in our AM/VO's impl class, that take the department number as input and return the list of employees under it and their details as ArrayList.

    public ArrayList getEmpDetsForDept(int Deptno) {
        ArrayList empDetsAL = new ArrayList();
        getEmpView1().setWhereClause("Deptno=" + Deptno);
        getEmpView1().executeQuery();
        
        
        getEmpView1().first();
                
        if(getEmpView1().getRowCount()>0) {

            empDetsAL.add(new Object[]{""+Deptno,
                                        getEmpView1().getCurrentRow().getAttribute("Ename").toString(),
                                        new Double(getEmpView1().getCurrentRow().getAttribute("Sal").toString())}
                          );

            while (getEmpView1().hasNext()){
                empDetsAL.add(new Object[]{""+Deptno,
                                            getEmpView1().getCurrentRow().getAttribute("Ename").toString(),
                                            new Double(getEmpView1().getCurrentRow().getAttribute("Sal").toString())}
                              );
                getEmpView1().next();
                
            }

            
        }
                                              
        return empDetsAL;
    }

Now, the main part comes. Pie Graphs can be based on ArrayList. So, in our backing bean, we'll have an attribute of type ArrayList. Also, we'll have a variable to to bind the graph for triggering PPR.

    private List chartData=new ArrayList();
    private UIGraph empChart;

    public void setChartData(List chartData) {
        this.chartData = chartData;
    }

    public List getChartData() {
        return chartData;
    }

    public void setEmpChart(UIGraph empChart) {
        this.empChart = empChart;
    }

    public UIGraph getEmpChart() {
        return empChart;
    }

As we need to find out the Departments that are dragged from table, we'll have a method in the backing bean to get the list of dragged Departments, iterate through them, pass each to the AM / VO method created to get Emp details and then construct the ArrayList.


    public DnDAction dropDeptInPie(DropEvent dropEvent) {
        
        RichTable table = (RichTable) dropEvent.getDragComponent(); 
        Transferable t = dropEvent.getTransferable(); 
        DataFlavor<RowKeySet> df = DataFlavor.getDataFlavor(RowKeySet.class, "Dept"); 
        RowKeySet rks = t.getData(df); 
        Iterator iter = rks.iterator();
        if(getChartData()!=null) getChartData().clear();
        while (iter.hasNext()) { 
            List key = (List)iter.next(); 
            table.setRowKey(key); 
            JUCtrlHierNodeBinding rowBinding = (JUCtrlHierNodeBinding) table.getRowData(); 
            Row row = (Row) rowBinding.getRow();
            String Deptno = row.getAttribute("Deptno").toString();
            BindingContainer bindings = getBindings();
            OperationBinding operationBinding = bindings.getOperationBinding("getEmpDetsForDept");
            operationBinding.getParamsMap().put("Deptno", Deptno);
            ArrayList result = (ArrayList)operationBinding.execute();
        

            if (operationBinding.getErrors().isEmpty()) {
                if(getChartData()!=null)getChartData().addAll(result);
                else  setChartData(result);

            }

        }  
        
        
        AdfFacesContext.getCurrentInstance().addPartialTarget(empChart);

        return DnDAction.NONE;

        
    }

    public BindingContainer getBindings() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();
    }


In order to make the Pie Graph use the ArrayList as source, we need to bind this to the tabularData Property of the pie graph. Let us also bind the chart to the variable we created in backing bean and set its dropListener to the method created above. Now, our pieGraph's source in jspx page would look like

                  <dvt:pieGraph id="pieGraph1" subType="PIE_MULTI"
                                customLayout="CL_NONE"
                                binding="#{pageFlowScope.DnDBean.empChart}"
                                tabularData="#{pageFlowScope.DnDBean.chartData}">
                    <af:dropTarget dropListener="#{pageFlowScope.DnDBean.dropDeptInPie}">
                      <af:dataFlavor flavorClass="org.apache.myfaces.trinidad.model.RowKeySet"
                                     discriminant="Dept"/>
                    </af:dropTarget>


Now, let us run the page, select couple of Departments and Drop them on the Pie chart.


DeptEmpChartData.jpg


Here, we've dragged the departments 10 & 30, and dropped them on the chart to get the Salary of the employees belonging to those departments as slices.

Wednesday Feb 08, 2012

Passing comma separated string as bind variable for VO query's IN operator

Quite often, we want to pass a parameter to the bind variable in the VO's query with a comma separated value, for the where clause with an IN operator. However, normal SQL query that the VO contain interpret that whole comma separated value as a single String and our usecase fail to get fulfilled.

Ex. question in OTN thread : https://forums.oracle.com/forums/thread.jspa?messageID=10125366

To overcome this, Mohammad Jabr has written a blog entry with the help of  Steve Muench's example #126. This has been achieved by using a TYPE and CASTing it to get the list.

In this article, we'll see another option to alter the query using regexp_substr, without having to use a TYPE and CAST.

Let us take an example of a VO created using EMP table with the following query.

SELECT Emp.EMPNO, 
       Emp.ENAME, 
       Emp.JOB, 
       Emp.MGR, 
       Emp.HIREDATE, 
       Emp.SAL, 
       Emp.COMM, 
       Emp.DEPTNO
FROM EMP Emp

EmpViewDefault.jpg


We'll add a where clause to the VO's query with a bind variable that takes comma separated string as input. Here, we would be including regexp_substr function (Oracle DB >=10g), to split the comma separated string and return them as rows.

Now, our Where clause of the VO's query would be looking like

WHERE Emp.ENAME in 
  (select regexp_substr(:Bind_Ename_Comma_Sep_List,'[^,]+', 1, level) 
   from dual 
    connect by 
        regexp_substr(:Bind_Ename_Comma_Sep_List, '[^,]+', 1, level) 
            is not null)
Also, we'll add a Bind Variable Bind_Ename_Comma_Sep_List of String data type. Once after modifying the Where Cluase and after adding the bind variable, our VO would be looking like

EmpView.jpg


To validate our query, let us run the AM tester to check the result

AMTester.jpg


Validate the result by entering a comma separated ENAME list to the bind variable (SMITH,ALLEN,JONES).


AMTesterBindVar.jpg


Check out the query result

AMTesterQueryRes.jpg


As it can be seen, we've passed 3 comma separated Enames to the bind variable, which in turn fetched only those records with the matching 3 Enames.

Thursday Feb 02, 2012

Drag ADF table record & drop on bar graph in Jdeveloper 11.1.2.0.0

In Jdeveloper 11.1.2.0.0,we can drag any component & drop on graph.It also supports drag/drop between graphs.

Use Case Description

Let us consider a use case wherein we will drag table row & drop that on the bar graph.

Implementation steps

Assume that we have table with Emp records & bar graph that displays Empno & Salary of Employees.

Insert drag component inside table:  <af:dragSource discriminant="tab"/>


Now insert dropTarget inside barGraph:

<af:dropTarget dropListener="#{test.handleTableDrop}">
      <af:dataFlavor
       flavorClass="org.apache.myfaces.trinidad.model.RowKeySet"    discriminant="tab"/>
</af:dropTarget>


public DnDAction handleTableDrop(DropEvent dropEvent)

{
        // Add event code here...
        System.out.println("Dropped id is"+dropEvent.getDragClientId());
        return DnDAction.NONE;
 }

Run the above page .Drag any table row & drop that on the bar Graph.
Note that handleTableDrop method gets executed & it prints dragged client id(i.e table id).

Wednesday Feb 01, 2012

Custom Upload options in ADF Desktop Integration

About

     In ADFdi enabled excel workbook while uploading the changes performed in ADF Table,standard upload options dialog appears like below.

uploadoptions_dialog.PNG

    In this article,we will see how to customize these options in a dialog.

Usecase

     Let us take a simple example of Employee table.

Implementation

Assumption: An ADF Web Application with ADF Desktop Integration enabled workbook having Employee table as below screenshot is available.

adfdi1_table.PNG


Now let's create a new jspx page titled 'UploadOptions.jspx'.
The below code adds two checkboxes and buttons.Let's copy this into the Source view of jspx page.

<!-- Check box for AbortUploadOnFailure -->
<af:selectBooleanCheckbox text="Abort Upload On Failure" id="sbc1"
  value="#{requestScope.abortUploadOnFailure}" autoSubmit="true" selected="true"/>

<!--Check box for DownloadAfterUpload -->
<af:selectBooleanCheckbox text="Download After Upload" id="sbc2" autoSubmit="true"
  value="#{requestScope.downloadAfterUpload}"/>

<!--Command button for Continue action -->
<af:button text="Continue" id="b1" inlineStyle="width:100px;" partialSubmit="false">
<af:setActionListener from="continue" to="#{requestScope.action}"/>
</af:button>

<!-- Command button for Abort action -->
<af:button text="Abort" id="b2" inlineStyle="width:100px;" partialSubmit="false">
<af:setActionListener from="abort" to="#{requestScope.action}" />
</af:button>


The UploadOptions.jspx page now looks like below:

custom_upload_jspx.PNG

Below Span elements have to be used to perform custom upload actions in ADFdi Table.

ADFdiCloseWindow - When a web page has to be closed,this span element can be set to
Continue  - to close the web page and invoke next action in actions set or
Abort       - to close the web page and terminate the action set.
ADFdiAbortUploadOnFailure - If this element is set to True, the action set stops uploading if it encounters a failure. If the element references False, the action set attempts to upload all rows and indicates if each row succeeded or failed to upload.
ADFdiDownloadAfterUpload - If this element is set to True, the action set downloads data into the ADFdi Table component after the action set uploads modified data.

Let us add the below code at the end of tag Form in source view of jspx,to include the above mentioned span elements.

<!-- Closes the Upload Options dialog and then invokes next action in the action set-->
<f:verbatim rendered="#{requestScope.action eq 'continue'}">
<span id="ADFdiCloseWindow">Continue</span>
</f:verbatim>


<!-- Closes the
Upload Options dialog but aborts next actions in action set-->
<f:verbatim rendered="#{requestScope eq 'abort'}">
<span id="ADFdi
CloseWindow">Abort</span>
</f:verbatim>


<!-- If the span element is set to
  True, the action set stops uploading if it encounters a failure.
  False, the action set attempts to upload all rows and indicates if each row succeeded or failed to upload. -->
<f:verbatim>
<span id="ADFdiAbortUploadOnFailure">${requestScope.abortUploadOnFailure}</span> 
</f:verbatim> 


<!--If the span element is set to
True, the action set downloads data from the Fusion web application to the ADF Table after the action set uploads modified data -->
<f:verbatim>
<span id="ADFdi
DownloadAfterUpload">${requestScope.downloadAfterUpload}</span>
</f:verbatim>


In ADFdi excel workbook,let us add this new jspx file as dialog before Table.Upload action in Ribbon Commands of Worksheet Properties.

worksheet_properties.PNG

Now we run the excel workbook and download data into ADF Table as shown above.Let's update a row by changing the Sal=4000 of Martin.

update2_ADFdiTable.PNG

On clicking Upload Ribbon command like below:

Uplaod_ribbon.PNG

the custom upload actions dialog gets opened with 'Abort Upload On Failure' check box selected by default.

custom1_upload_workbook.PNG
We can select 'Download After Upload' check box and click on Continue button.We can see data updated and downloaded again with new value like below:

updated2_table.PNG

This is a simple example of how upload options can be customized. Using this, we can add more custom options to the dialog, which is not available in the standard upload options dialog. We will see such options sooner in my next blog.

Thursday Dec 29, 2011

Business Rules Editor for View Objects in JDeveloper 11.1.2.0.0

JDeveloper 11.1.2.0.0 has a new tab in the View Objects Editor - Business Rules.
This easily tend to make people misunderstand that this Editor can be used for adding validation rules for the attributes in the View Object. However, it is not true.

Lets check out what this editor is all about. As the online help for this page states
Use to create and maintain business rules based on Groovy Language expressions and declarative validation rules for this view object including:

  • Validators for transient attributes
  • Bind variable default value expressions for SQL queries
  • Bind variable value mappings for view accessors
  • Transient attribute value expressions
  • Transient attribute value expression recalculation conditions
  • Attribute default value expressions

We'll see how we can use this editor to edit different types :

Transient Attributes : 

We can use this editor for a. adding validation rules, b. edit default value expression

a. As like the Entity Attributes, we can add many validation rules for the transient attributes in VO as well.

Ex.




In the above image, EmpType is a transient attribute on Emp VO.We can see there are two rules added. First one is a validation rule (List Validator), restricting the user to enter either one of the value specified for the attribute. Second one is the default value expression for the attribute. Upon selecting the node, the default value groovy expression would become editable in the Script Expression field below, wherein we can modify it and test the syntax as well.

Note : We can add the validation rule only for the transient attributes which are Updatable. Also, we can edit only the default values which are script expressions only. Not the literal values.

Bind Variables :

Similar to the transient attributes, we can use this editor for modifying and testing the default value expression for the Bind Variables of the VO as well.

Ex :



In the above example image, we can see a Bind Variable (Bind_Hiredate) added to the Query, which has the default value expression as adf.currentDate.

Using this editor, we can modify this expression and test the syntax.

View Accessor :

If we have a bind variable to the VO and use it as List Data Source for any of the attributes in the VO, we can use this editor to change the value of the bind variable in the View Accessor.

Ex :


In the above example image, we have couple of Bind Variables in the View Accessors (they are used as Lists and Dependent Lists for this View Object). Using this editor, we can edit the values of those bind variables.

We can also use this editor for modifying and testing the default value expression on the VO attributes and the re-calculation conditions.



Wednesday Nov 30, 2011

Project Gantt chart using ADF BC

This article describes simple example of using Project Gantt chart using ADF Business components.

Use Case Description

Let us create a simple Project Gantt chart using ADF Business components & try to get the selected tasks details.
Implementation steps

A project Gantt chart is used for project management. The chart lists
tasks vertically and shows the duration of each task as a bar on a
horizontal time line.

To create a basic project gantt chart,we first need to define  2 tables as below:
1)task_table with taskid,task_type,start_date & end_date
2)subtask_table with subtaskid,subtask_type,start_date, end_date &  taskid


Now we can create Business components for the above 2 tables .Then we will create new jspx page -projectGantt.jspx


Drop TaskView1 as Gantt->Project:


gantt2.JPG












Select all required columns under tasks & subtasks tabs of 'create Project Gantt chart' dialog.

We have created Project Gantt chart that lists tasks & its subtasks.Now if we need to get all task details selected by the user then define taskSelectionListener for the dvt:projectGantt in jspx source page:

taskSelectionListener="#{test.taskSelectlistener}"


public void taskListener(TaskSelectionEvent taskSelectionEvent) {
// This codes gives all the tasks selected by user

System.out.println("Selected task details +taskSelectionEvent.getTask());

}

Run the above page & note that it shows all details of tasks nodes & expanding these tasks nodes shows its corresponding subtasks details.Now if user selects 2 tasks,we can see that it prints the complete task details for the selected tasks.




Simulating Date Picker in ADF Desktop Integration

About

ADF Desktop Integration doesn't have built-in Date-Picker like component to choose a date from the calendar instead of entering it manually as in ADF Faces.

In this Article, I would like to discuss how an ADF Input Text component can be made use as a Calendar component using the power of excel macros.

Usecase

Let us take a simple case of Employee form with Hiredate field as date-picker

Solution

Assumption: An ADF Web Application with ADF Desktop Integration enabled workbook having Employee form as in below screenshot is readily available

Form_DT.PNG
Now, Our goal is to make the InputText component bound to Hiredate as Date-Picker

We can achieve this either by using Microsoft's ActiveX Calendar Control components or by building our own calendar component using macros.

The disadvantage of using Microsoft's ActiveX Calendar Control component is that we need to  register MSCAL.OCX or MSCOMCT2.OCX with every machine we are going to access this workbook and in real-time it would be difficult to maintain the control everywhere.

Here, I discuss the second approach and for which I made use of the macro code given by VBA Express to build calendar.

Procedure

-  Open Microsoft Visual Basic Editor by clicking on 'View Code' button under 'Developer' Tab

-  Insert a new user form using Insert -> UserForm option and change the name of the form as CalendarFrm

Insert_Form.png

- Design the form as in below screenshot and name First two combo boxes as CB_Mth and CB_Yr, Sun to Sat from Label2 to Label8 and remaining Day cells from D1 to D42 (Easiest way of designing the form is to download the workbook attached at the end and copy the form to your excel workbook so that the layout and names everything gets copied)

Calendar_Form.png
- Right click on the Form in Project Explorer and choose ViewCode option

Calendar_Form_ViewCode.png
- Copy below code to form to add calendar functionality

Option Explicit
Dim ThisDay As Date
Dim ThisYear, ThisMth As Date
Dim CreateCal As Boolean
Dim i As Integer

Private Sub UserForm_Initialize()
    Application.EnableEvents = False
     'starts the form on todays date
    ThisDay = Date
    
    ThisMth = Format(ThisDay, "mm")
    ThisYear = Format(ThisDay, "yyyy")
    For i = 1 To 12
        CB_Mth.AddItem Format(DateSerial(Year(Date), Month(Date) + i, 0), "mmmm")
    Next
    CB_Mth.ListIndex = Format(Date, "mm") - Format(Date, "mm")
    For i = -20 To 50
        If i = 1 Then CB_Yr.AddItem Format((ThisDay), "yyyy") Else CB_Yr.AddItem _
        Format((DateAdd("yyyy", (i - 1), ThisDay)), "yyyy")
    Next
    CB_Yr.ListIndex = 21
     'Builds the calendar with todays date
    CreateCal = True
    Call Build_Calendar
    Application.EnableEvents = True
End Sub

Private Sub CB_Mth_Change()
     'rebuilds the calendar when the month is changed by the user
    Build_Calendar
End Sub
Private Sub CB_Yr_Change()
     'rebuilds the calendar when the year is changed by the user
    Build_Calendar
End Sub
Private Sub Build_Calendar()
     'the routine that actually builds the calendar each time
    If CreateCal = True Then
        CalendarFrm.Caption = " " & CB_Mth.Value & " " & CB_Yr.Value
        For i = 1 To 42
            If i < Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
                Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
                Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
            ElseIf i >= Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
                Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) _
                & "/1/" & (CB_Yr.Value))), ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
                Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
            End If
            If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
            ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "mmmm") = ((CB_Mth.Value)) Then
                If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H80000018 '&H80000010
                Controls("D" & (i)).Font.Bold = True
                If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy") = Format(ThisDay, "m/d/yy") Then Controls("D" & (i)).SetFocus
            Else
                If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H8000000F
                Controls("D" & (i)).Font.Bold = False
            End If
        Next
    End If
End Sub
Private Sub D1_Click()
     'this sub and the ones following represent the buttons for days on the form
     'retrieves the current value of the individual controltiptext and
     'places it in the active cell
    ActiveCell.Value = D1.ControlTipText
    Unload Me
     'after unload you can call a different userform to continue data entry
     'uncomment this line and add a userform named UserForm2
     'Userform2.Show
     
End Sub
Private Sub D2_Click()
    ActiveCell.Value = D2.ControlTipText
    Unload Me
     
End Sub
Private Sub D3_Click()
    ActiveCell.Value = D3.ControlTipText
    Unload Me
     
End Sub
Private Sub D4_Click()
    ActiveCell.Value = D4.ControlTipText
    Unload Me
     
End Sub
Private Sub D5_Click()
    ActiveCell.Value = D5.ControlTipText
    Unload Me
     
End Sub
Private Sub D6_Click()
    ActiveCell.Value = D6.ControlTipText
    Unload Me
     
End Sub
Private Sub D7_Click()
    ActiveCell.Value = D7.ControlTipText
    Unload Me
     
End Sub
Private Sub D8_Click()
    ActiveCell.Value = D8.ControlTipText
    Unload Me
     
End Sub
Private Sub D9_Click()
    ActiveCell.Value = D9.ControlTipText
    Unload Me
     
End Sub
Private Sub D10_Click()
    ActiveCell.Value = D10.ControlTipText
    Unload Me
     
End Sub
Private Sub D11_Click()
    ActiveCell.Value = D11.ControlTipText
    Unload Me
     
End Sub
Private Sub D12_Click()
    ActiveCell.Value = D12.ControlTipText
    Unload Me
     
End Sub
Private Sub D13_Click()
    ActiveCell.Value = D13.ControlTipText
    Unload Me
     
End Sub
Private Sub D14_Click()
    ActiveCell.Value = D14.ControlTipText
    Unload Me
     
End Sub
Private Sub D15_Click()
    ActiveCell.Value = D15.ControlTipText
    Unload Me
     
End Sub
Private Sub D16_Click()
    ActiveCell.Value = D16.ControlTipText
    Unload Me
     
End Sub
Private Sub D17_Click()
    ActiveCell.Value = D17.ControlTipText
    Unload Me
     
End Sub
Private Sub D18_Click()
    ActiveCell.Value = D18.ControlTipText
    Unload Me
     
End Sub
Private Sub D19_Click()
    ActiveCell.Value = D19.ControlTipText
    Unload Me
     
End Sub
Private Sub D20_Click()
    ActiveCell.Value = D20.ControlTipText
    Unload Me
     
End Sub
Private Sub D21_Click()
    ActiveCell.Value = D21.ControlTipText
    Unload Me
     
End Sub
Private Sub D22_Click()
    ActiveCell.Value = D22.ControlTipText
    Unload Me
     
End Sub
Private Sub D23_Click()
    ActiveCell.Value = D23.ControlTipText
    Unload Me
     
End Sub
Private Sub D24_Click()
    ActiveCell.Value = D24.ControlTipText
    Unload Me
     
End Sub
Private Sub D25_Click()
    ActiveCell.Value = D25.ControlTipText
    Unload Me
     
End Sub
Private Sub D26_Click()
    ActiveCell.Value = D26.ControlTipText
    Unload Me
     
End Sub
Private Sub D27_Click()
    ActiveCell.Value = D27.ControlTipText
    Unload Me
     
End Sub
Private Sub D28_Click()
    ActiveCell.Value = D28.ControlTipText
    Unload Me
     
End Sub
Private Sub D29_Click()
    ActiveCell.Value = D29.ControlTipText
    Unload Me
     
End Sub
Private Sub D30_Click()
    ActiveCell.Value = D30.ControlTipText
    Unload Me
     
End Sub
Private Sub D31_Click()
    ActiveCell.Value = D31.ControlTipText
    Unload Me
     
End Sub
Private Sub D32_Click()
    ActiveCell.Value = D32.ControlTipText
    Unload Me
     
End Sub
Private Sub D33_Click()
    ActiveCell.Value = D33.ControlTipText
    Unload Me
     
End Sub
Private Sub D34_Click()
    ActiveCell.Value = D34.ControlTipText
    Unload Me
     
End Sub
Private Sub D35_Click()
    ActiveCell.Value = D35.ControlTipText
    Unload Me
     
End Sub
Private Sub D36_Click()
    ActiveCell.Value = D36.ControlTipText
    Unload Me
     
End Sub
Private Sub D37_Click()
    ActiveCell.Value = D37.ControlTipText
    Unload Me
     
End Sub
Private Sub D38_Click()
    ActiveCell.Value = D38.ControlTipText
    Unload Me
     
End Sub
Private Sub D39_Click()
    ActiveCell.Value = D39.ControlTipText
    Unload Me
     
End Sub
Private Sub D40_Click()
    ActiveCell.Value = D40.ControlTipText
    Unload Me
     
End Sub
Private Sub D41_Click()
    ActiveCell.Value = D41.ControlTipText
    Unload Me
     
End Sub
Private Sub D42_Click()
    ActiveCell.Value = D42.ControlTipText
    Unload Me
     
End Sub
Please note that this step doesn't have anything specific to ADF Desktop Integration it is purely excel macro so not explaining much on the code part

- Finally, add below code to the Worksheet _BeforeDoubleClick event of the sheet having employee form to invoke Calendar on double-clicking on Hiredate

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        ' $D$7 is the cell containing Hiredate
        If ActiveCell.Address = "$D$7" Then
            CalendarFrm.Show
        End If
End Sub
- Save the changes and Run the design time of workbook

Run_DT.png
- Navigate to first record, double-click on Hiredate field and notice date-picker dialog and date selected will be shown back to Hiredate field


Form_Calendar_RT.png


Sample design-time workbook developed in 11.1.2.0.0 can be downloaded Here (Right-Click on the link and choose "Save Link As..." option to download the workbook)


Monday Nov 14, 2011

Combo LOV - How to display Description for Selected Value instead of ID

Problem

The common problem that any one working with Combo LOV in a faces page faces is that the list of values component shows Id for selected value inspite of the dropdown showing Description for Options.
Unfortunately, this is a known issue with ADF faces for which there is already an enhancement request which we would expect to see in future releases.

Solution

In this article, I discuss a way to overcome this and display Description rather than Id.
In simple words, The workaround is to define LOV on a transient attribute instead of a View Attribute(Probably a Foreign Key attribute) and set the selected value back to View attribute on change of the LOV and make use of this transient attribute instead of View attribute wherever Combo LOV is needed.

Usecase

I take a simple usecase with well known Emp and Dept tables to display an Emp form with Deptno Combo box List of Values with selected value of LOV being the Department name instead of Deptno
Combo_LOV.png

Here are the detailed steps for the same:

- Add a transient attribute to EmpView with the name "Dname"
Transient_Dname_Attr.png

- Define Combo LOV matching Dname and Deptno both (Deptno matching helps in setting value back to Deptno view attribute on change of Dname LOV value)
- Keep a note of List Data Source name(i.e, DeptViewAccessor in screenshot)
Dname_Combo_LOV.png

- Finally, Set the Department name of respective Deptno as default value to transient attribute(To show current department as selected) using below groovy expression:
Dname_Groovy.png



Now, when you run the page you should be able to see the combo lov showing Department name for the selected value and change in LOV automatically updates Deptno attribute.

Incase, anyone don't like to have groovy, they can overwrite getDname method in RowImpl and add this code there.

Friday Sep 16, 2011

Contextual events with EJB DC

This article describes contextual events with EJB Datacontrol

Use Case Description

Let us consider a scenario wherein the user wants to display deptno list & commandButton in region1 & region2 displays the Emp records corresponding to selected deptno in region1.We will try to achieve this using contextual events.

When the user selects deptno & clicks on the commandButton ,a contextual event with payLoad parameter is broadcasted by taskflow1.This event is then consumed by the  taskflow2 and the handleEvent() handler passes payLoad parameter(i.e deptno) to sessionBean.SessionBean has findByDeptno() method that returns the Emp records & taskflow2 displays the respective Emp records .

Implementation steps

Let us suppose that we have created Java EE Web Application with Entities from Emp & Dept table
.Also add 'ADF Faces Components 11' under ViewController->project properties-> JSP Tag Libraries.

Edit Emp.java & create a custom named query that returns Emp Object for the respective deptno :


 @NamedQuery(name = "findEmpByDeptno", query = "select o from Emp o
 where o.dept.deptno = :deptNo")
Let us create Stateful Session Bean and expose the Named Query through the Session Facade.Create a contextual handle event that passes deptno as payload parameter:   

 public void handleEvent(Object payload) 
    {
           this.setDepartNo((BigDecimal)payload);
    }
Modify findEmpByDeptno namedQuery as below:

 public List<Emp> findEmpByDeptno() 
{
 
 return em.createNamedQuery("findEmpByDeptno").setParameter
 ("deptNo",  this.getDepartNo()).getResultList();
}

Generate Data control for the above Session Bean & expose the above method through the Session Facade.

Create a ADF taskflow1 with page Fragments & drop  view activity(DeptView) on that.In the same taskflow drop Execute operation((findByDeptno()->Emp->Operations->Execute).Define control flow case 'execute' from DeptView to Execute operation in taskflow.

Drop deptno as selectOneChoice & commandButton 'showEmpRecords' on the DeptView page Fragment page.Set showEmpRecords action property  as 'execute'

In its page definition create deptno attribute value that points to deptFindAllIterator &  handler event method action binding to handle the event:

handleEvtAction.JPG
              
Select commandButton 'showEmpRecords' & create new contextual event on that:


contextEvent.JPG

Now we will create new Event subscriber in its contextual events binding editor tab:

subscribeEvt.JPG

Create taskflow2 that displays Emp table(findByDeptno()->Emp) & create main.jspx page,drop both the taskflows as region

Run the page & now if user selects deptno,table below shows Emp records corresponding to the selected deptno.


Wednesday Sep 14, 2011

Paritally restricting user entry in the ADF input text - using Java Script

Recently came across a forum post in which the OP wanted to let the users edit the content of the text field partially.

https://forums.oracle.com/forums/thread.jspa?forumID=83&threadID=2259832

Here is an example. Let us assume the input text contains the following text. "You can edit the content inside { this }". In this, users should be able to edit only the content inside { }. I.e Only "this" should be editable.

To achieve this, we can use a java script method, that tracks the cursor position and ignore the user edits if the cursor position is not between the curly braces. After which, the method would be used in the client listener for the input text.

Example code snippet.



<af:inputText label="Partial Editable Text Item" 
id="it1" value="You can edit the content inside { this }" clientComponent="true" >
<af:clientListener  type="keyPress" method="validateValue"  /> 
           
</af:inputText>
<af:resource type="javascript">
               function validateValue(evt){
                       var inputTxt=document.getElementById('it1::content');
                       var startPos = inputTxt.value.indexOf("{");
                       var endPos = inputTxt.value.indexOf("}");
                       var cursorPos = inputTxt.selectionStart;
                          if (cursorPos &lt; startPos+2 || cursorPos > endPos-1) { 
                                   alert("Cannot Edit");
                                   evt.cancel();
                          }
 }
</af:resource>

More complex example by Frank Nimphius http://blogs.oracle.com/jdevotnharvest/entry/get_social_security_numbers_right
Note : Tested the above snippet successfully in Mozilla Firefox and IE 9.

Wednesday Aug 24, 2011

Creating Matrix Report using ADF Pivot Table

Matrix Report can be achieved using ADF Pivot Table, we have to set drill down parameters to have hierarchical view in the report. Data Aggregation option allows us to use mathematical operations like Sum, Average, Count, Maximum, Minimum, Standard Deviation, and Variance on data.
This article shows how to create a Drill down Matrix Report with Department wise Total Salary using ADF Pivot Table.

  • Create Business Components from DEPARTMENTS and EMPLOYEES tables of HR schema.
  • Drop EmployeesView1 from Data Control Palette to jspx page as ADF Pivot Table.
PivotTable_DataControl_ADFPivotTable.JPG


  • In Select display attributes panel first drop DepartmentId from 'Available Attributes' list to 'Row and Columns edges', drop EmployeeId below DepartmentId in 'Row and Columns edges'.
PivotTable_SelectDispAttributes1.JPG
 
  • From 'Available Attributes' list drop FirstName, LastName, Salary, JobId to 'Data Labels' section.
PivotTable_SelectDispAttributes2.JPG
 
  • In 'Configure drilling' panel select 'Insert Drilling' option, Set 'Insert Parent Row' as 'After Children'. Check enable Drill Path for Drill Down report. Drilling option gives a Tree structure for the selected Attributes. Drill Paths are derived from the Attributes dropped in 'Row and Column Edges' In Select display attributes panel.
PivotTable_ConfigureDrilling.JPG


  • Add 'Salary' attribute to configure Data Aggregation.
PivotTable_ConfigureDataAggregation.JPG


  • Select 'Category Totals' tab and add Categories Attribute as 'DepartmentId' and 'Insert Total' as 'After'.
PivotTable_ConfigureCategoryTotals1.JPG


  • Configure sorting categories based on DepartmentId attribute in ascending order.
PivotTable_ConfigureSorting.JPG


  • Finish the wizard and run the page.
  • Expand DepartmentId to find the Employees, also note the Department wise Salary and Total Salary.

PivotTable_Runtime.JPG













Wednesday Aug 03, 2011

Passing parameter to EJB Method in taskflow

This article describes on passing parameter to EJB Method in taskflow.

Use Case Description

Suppose we have a table displaying Dept records.Table also has column link & clicking on this link displays a region in popup.This region accepts deptno parameter,executes EJB Method & displays respective Emp details in a popup.

Implementation steps

Let us suppose that we have created Java EE Web Application with Entities from Emp & Dept table .Also add 'ADF Faces Components 11' under ViewController->project properties-> JSP Tag Libraries.

Edit Emp.java & create a custom named query that returns Emp Object for the respective deptno :


 @NamedQuery(name = "findEmpByDeptno", query = "select o from Emp o
 where o.dept.deptno = :deptNo")
Let us create Stateful Session Bean and expose the Named Query through the Session Facade.Our use case deals with passing parameters to EJB method in taskflow.So we will define simple EJB method that just accepts deptno parameter & stores its value in private variable:
   
 public void passDeptno(BigDecimal deptNo) 
 {
 this.setDepartNo(deptNo);
 }

Now that we have separate method to accept parameter we will modify findEmpByDeptno namedQuery as below:

 public List<Emp> findEmpByDeptno() 
{
 
 return em.createNamedQuery("findEmpByDeptno").setParameter
 ("deptNo", this.getDepartNo()).getResultList();
}

Generate Data control for the above Session Bean & expose the above method through the Session Facade.

Create a ADF taskflow with page Fragments & create an inputParameter 'inputParameter1' in its overview parameters tab:

adf_taskflow.JPG










taskflow_param.JPG
Drop passDeptno(BigDecimal) as Method Call activity with value as
#{pageFlowScope.inputParameter1}.Drop findEmpByDeptno()->Operations->execute operation as Method Call activity & then drop  emp view activity on the taskflow.Define all control flows.

taskflow2.jpeg
Create emp.jsff page fragment & drop findEmpByDeptno()->Emp as ADF Table with single row selection

emp_tab.JPG


Now we will create main.jspx page & drop deptFindAll as ADF table with single row selection on this.We will also create deptno attribute bindings in page definition file:

attribute.JPG
Insert a new column inside the table that displays Emp Details.Drop the above created taskflow as region inside the column.Pass '#{sessionScope.deptno}' value for inputParameter1 & set the taskflow's ifNedded property to 'Refresh'.Here we are using setPropertyListener to pass parameter to taskflow method:

<af:column id="c4" headerText="Emp details">
<af:commandLink text="Emp details" id="cl1">
<af:showPopupBehavior popupId="p1"/>
</af:commandLink>
<af:popup id="p1" contentDelivery="lazyUncached">
<af:dialog id="d2" title="Emp Details" type="none">
<af:region
 value="#{bindings.taskflowdefinition1.regionModel}"
id="r1"/>
</af:dialog>
<af:setPropertyListener type="popupFetch"
from="#{bindings.deptno.inputValue}"
to="#{sessionScope.deptno}"/>
</af:popup>
</af:column>

Run the page,it shows Dept records.Select any dept record & click on 'Emp Details' link.It shows popup with its respective Emp records


Tuesday Jul 26, 2011

Using Message-Driven Bean and JMS Queue with ADF

EJB3.0 Message-Driven Bean asynchronously listens to JMS Queue/Topic and processes the messages from its clients.
In this article we will see how Message-Driven Bean listens to JMS Queue and process the message sent to Queue by a client, here I will be using ADF jspx page as our client.
This article will be split into two Sections. First Section, is for creating JMS Queue from WLS console, this is to help those who are not familiar in managing JMS from WLS console. Second Section, shows how to send a message from ADF client (jspx page) to JMS Queue and how to make Message-Driven Bean listen to the JMS Queue.

Section 1: Creating JMS Queue from WLS console

  • You need to have Integrated or Standalone WLS instance up and running.
  • FileSystem type Persistence Store has to be created to store JMS messages.
MessageDrivenBean-JMSQueue_CreatePersistenceStore1.JPG


MessageDrivenBean-JMSQueue_CreatePersistenceStore2.JPG


  • JMS Server is required to manage JMS Queues targeted at it.
MessageDrivenBean-JMSQueue_CreateJMSServer1.JPG


  • Use File Store create above as Persistence Store.
MessageDrivenBean-JMSQueue_CreateJMSServer2.JPG


  • Set 'DefaultServer' (for Integrated WLS) or 'AdminServer' (for Standalone WLS) as its Target Server.
MessageDrivenBean-JMSQueue_CreateJMSServer_TargetServer.JPG


  • JMS System Module is required to hold the queues.

MessageDrivenBean-JMSQueue_CreateJMSModule1.JPG


MessageDrivenBean-JMSQueue_CreateJMSModule2.JPG

  • Target JMS Module to server on which JMS Server is target.
MessageDrivenBean-JMSQueue_CreateJMSModule3.JPG
MessageDrivenBean-JMSQueue_CreateJMSModule3.JPG


MessageDrivenBean-JMSQueue_CreateJMSModule4.JPG

  • Check 'Would you like to add resources to this JMS system module' to include JMS queue to this newly created JMS Module.
MessageDrivenBean-JMSQueue_CreateJMSModule4.JPG


  • Create Sub Deployment and target it to JMS Server.

MessageDrivenBean-JMSQueue_SubDeployment1.JPG


MessageDrivenBean-JMSQueue_SubDeployment2.JPG


MessageDrivenBean-JMSQueue_SubDeployment3.JPG


  • Under Configurations tab click New button to create Connection Factories, Queues resources.
MessageDrivenBean-JMSQueue_CreateJMSModule_Config.JPG


  • Create JMS Connection Factory.
MessageDrivenBean-JMSQueue_ConnectionFactory1.JPG


MessageDrivenBean-JMSQueue_ConnectionFactory2.JPG


  • Now Target the Connection Factory to Sub Deployment created above.
MessageDrivenBean-JMSQueue_ConnectionFactory3.JPG


MessageDrivenBean-JMSQueue_ConnectionFactory4.JPG


  • Similarly create a Queue and target it to Sub Deployment.

MessageDrivenBean-JMSQueue_CreateJMSModule_Queue.JPG


MessageDrivenBean-JMSQueue_Queue1.JPG


MessageDrivenBean-JMSQueue_Queue2.JPG


  • With this we have configured WLS to handle JMS Queue.

Section 2: Send message from ADF client to JMS Queue and use Message-Driven Bean listen to the JMS Queue.

  • In a Custom Application Create Message-Driven Bean
MessageDrivenBean-JMSQueue_CreateMDBWizard.JPG


  • onMessage() of Message-Driven Bean is called by container when it receives message from a client.
  • onMessage() has to be modified accordingly to process the received message.

public void onMessage(Message message) {
  try{
    String sub= message.getStringProperty("subject");
    String mess= message.getStringProperty("message");
    System.out.println("Subject = " + sub);
    System.out.println("Message = " + mess);
   } catch(Exception e){
      e.printStackTrace();
  }
}

  • Create jspx page in View Controller project.
  • Drop two Input Texts from component palette to jspx page for Subject and Message.
  • Drop Command Button from component palette to jspx page.
MessageDrivenBean-JMSQueue_sendMessagePage.JPG


  • Bind Subject, Message input text to backing bean.
  • Create Action binding for send command button.
  • Add below code to send message to JMS queue.


QueueConnectionFactory connectionFactory;
try {
  connectionFactory = (QueueConnectionFactory)new InitialContext().lookup("jms.MDBConnectionFactory1");
  QueueConnection connection = connectionFactory.createQueueConnection();
  connection.start();
  QueueSession queueSession = connection.createQueueSession(false, Session.AUTO_ACKNOWLEDGE);
  Queue queue = (Queue)new InitialContext().lookup("weblogic.wsee.DefaultQueue");
  QueueSender queueSender = queueSession.createSender(queue);
  Message message = queueSession.createMessage();
  message.setJMSType("logMessage");
  message.setLongProperty("time", System.currentTimeMillis());
  message.setStringProperty("subject", this.getIt1().getValue().toString());
  message.setStringProperty("message", this.getIt2().getValue().toString());
  queueSender.send(message);
  queueSession.close();
  connection.close();
} catch (Exception e) {
  e.printStackTrace();
}

  • Run jspx page
  • Give values to  Subject, Message and hit Send button.
  • onMessage() reads this message prints as Server Log.
MessageDrivenBean-JMSQueue_IntegratedWLSMsg.JPG

About

Tips and Tricks from Oracle's JDeveloper & ADF QA

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today