Super Shuttle Item

In this post I’ll show you how to create a Super Shuttle Item.

Here's an Demo application: Super Shuttle Item

Features:

Filter on the Shuttle Item
Show the Count Rows on the Left/Right panel
Separate rows on the right panel by color (Selected rows or saved at the table)
Maximum rows on the right panel for manipulating is (32765) Characters
Insert/Delete approximately 4000 records at the same time

Firstly thanks to blog post from Christopher and awesome blog post from JOHN SNYDERS about APEX AND ASYNCHRONOUS AJAX.

Before Start

1.Create table SHUTTLE_TABLE this table has more than 14k records in order to load in the Shuttle Item

CREATE TABLE  "SHUTTLE_TEST" 
   ( "ID" NUMBER NOT NULL ENABLE, 
 "NAME" VARCHAR2(500 CHAR), 
 "COUNTRY" VARCHAR2(100 CHAR), 
  CONSTRAINT "SHUTTLE_TEST_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   )
/

2.Create table MYTABLE_TEST this table maintain some records of SHUTTLE_TABLE and maybe it’s detail

CREATE TABLE  "MYTABLE_TEST" 
   ( "ID" NUMBER NOT NULL ENABLE, 
 "CITY_ID" NUMBER, 
 "FLAG" CHAR(1 CHAR), 
  CONSTRAINT "MYTABLE_TEST_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   )
/

Create a Blank page and setting rendering tab

1. Create a simple form through wizard, e.g. : page 13 that name is SUPER-SHUTTLE

2. Create a region e.g. : Super Shuttle Item and I'll create three items and a button

Static ID => supershuttle-region

3. Create a classic report on MYTABLE_TEST table

Static ID => mycityregion

4. First item in the Super Shuttle Item region is P13_COUNTRY. This item 'll use in where clause of Shuttle Item Query

Required

Name => P13_COUNTRY
Type => Popup LOV
List of Value => SQL Query

select DISTINCT COUNTRY as d,
       COUNTRY as r
  from SHUTTLE_TEST
 order by 1

Display Null Value => YES
Post Text =>

5. Next item is P13_FILTER. With this item can be filter rows in Shuttle Item

Name => P13_COUNTRY
Type => Popup LOV
Value Placeholder => Filter text: City or ID
Post Text =>

6. Last item is P13_CITIES. This is a Shuttle Item

Name => P13_COUNTRY
Type => Shuttle
Show Control => All
List of Value => SQL Query

select s1.NAME||'('||ID||')' as CITY , s1.ID
  from SHUTTLE_TEST s1
where COUNTRY = COALESCE(:P13_COUNTRY,s1.COUNTRY)
and   not exists (Select 'x' 
                  From   MYTABLE_TEST m1
                  Where  m1.CITY_ID = s1.ID)
order by 1

7. Now create a button INSERT-DELETE for submit so I'll explain about Dynamic Action later.

8. Set Function and Global Variable Declaration on SUPER-SHUTTLE page. it has four parts, first set a variable that I ‘ll use later in Dynamic Action for controlling on shuttle item, second makes a function setShuttleLabel to show count rows on both panels, third makes a function fillShuttleRight for filling right panel of shuttle item, last enable Enter Key on Filter input text.

/*--------SHUTTLE CONTROL--------*/
var countsaved = 0;
var left = $x("P13_CITIES_LEFT");
var right = $x("P13_CITIES_RIGHT");

//makes shuttle labels
function setShuttleLabel() {
 
    //remove labels
    $( ".shuttleControl-count" ).remove();
    $( ".shuttleControl1-count" ).remove();
    $( ".shuttleControl2-count" ).remove();

    //create labels
    $( ".shuttleControl" ).prepend( " " );
    $( ".shuttleSort2" ).prepend( " " );
    $( ".shuttleSelect1" ).prepend( "Count: "+left.length+"" );
    $( ".shuttleSelect2" ).prepend( "Saved: "+countsaved+"" );
    $( ".shuttleSelect2" ).prepend( "Selected count: "+right.length+"" );
    
}

//fill right panel of shuttle item
function fillShuttleRight(){
    var largevalues = '';
    // invoke a PL/SQL process on the server by making an ajax request
    var shuttleData = apex.server.process("FILL_SHUTTLE_RIGHT",{} , 
        {
            loadingIndicator: function( pLoadingIndicator ) {
                                                            return pLoadingIndicator.prependTo (
                                                            apex.jQuery(
                                                            "td.shuttleControl", "#P13_CITIES_CONTAINER" ))
                                                            },
            loadingIndicatorPosition: "before"
        });

    shuttleData.done(function(data) {
        var i, item;
        // data could be anything you like but in this example do what a DA would do
        // this handles the page items to return set by the server process
        // data result is object with property item which is an array of page item id, value pairs
        right.length=0;
        if (data && data.item) {
            for (i = 0; i < data.item.length-1; i++) {
                item = data.item[i];
                right.options[i] = new Option(item.value, item.id);
                $(right.options[i]).addClass('color-saved');
            }

            //get lastElement
            countsaved = data.item.slice(-1)[0].value;

            //makes shuttle labels
            setShuttleLabel();
        }


    });
    
}

//fire to click on P13_FILTER item
$("#P13_FILTER").keyup(function(event){
    if(event.keyCode == 13){
        $("#filter-shuttle-button").click();
    }
});

9. Set CSS Inline on SUPER-SHUTTLE page

.count-shuttle-label {
    color: #ff0000;
}

.color-saved {
    color: #4696fc;
}

Set Dynamic Action

I'll create six Events on rendering tab that five events on Click and a event on Double Click.

1.Create a Dynamic Action Clear Filter in order to clear text in filter item

a. Name => Clear Filter
b. Event => Click
c. Selection Type => jQuery Selector
d. jQuery Selector => .filter-clear
e. create a true action Set Value
f. Set Type => Static Assignment
g. Value=> (you should set empty)
h. Fire On Page Load => No

2.Create a Dynamic Action Filter-Reset Shuttle when clicked on the rest/filter button

a. Name => Filter-Reset Shuttle
b. Event => Click
c. Selection Type => jQuery Selector
d. jQuery Selector => #filter-shuttle-button, #reset-shuttle-button
e. Create a true action Execute JavaScript Code
f. code:

var largevalues = '';

//set shuttle right
for ( var i=0; i < right.length; i++ ){
    right.options[i].selected = true;
    largevalues = largevalues +','+ right.options[i].value;
}
//remove first character ','
largevalues = largevalues.substr(1);

//You must select items less than 32767 characters
if (largevalues.length > 32767){
    alert('Your selection is more than 32767 characters');
    return;
}

var shuttleData = apex.server.process("FILL_SHUTTLE_LEFT", {
        x01: apex.item( "P13_COUNTRY" ).getValue(), x02: apex.item( "P13_FILTER" ).getValue(),
        x03: largevalues,
        pageItems:["P13_COUNTRY", "P13_CITIES"] // these are the page items to submit
    }, 
    {
        loadingIndicator: function( pLoadingIndicator ) {
                             return pLoadingIndicator.prependTo (
                            apex.jQuery(
                            "td.shuttleControl", "#P13_CITIES_CONTAINER" ))
                            },
        loadingIndicatorPosition: "before"
    });

shuttleData.done(function(data) {
    var i, item;
    left.length=0;
    if (data && data.item) {
        for (i = 0; i < data.item.length-1; i++) {
            item = data.item[i];
            left.options[i] = new Option(item.value, item.id);
            
        }
        //get lastElement
        countsaved = data.item.slice(-1)[0].value;
       
        //makes shuttle labels
        setShuttleLabel();
        
    }

})

h. Fire On Page Load => No

3.Create a Dynamic Action Change Shuttle Labels for setting count rows on shuttle item

a. Name => Change Shuttle Labels
b. Event => Click
c. Selection Type => jQuery Selector
d. jQuery Selector => .a-Button--shuttle
e. Create a true action Execute JavaScript Code
f. code:

//makes shuttle labels
setShuttleLabel();

h. Fire On Page Load => No

4.Create a Dynamic Action Reset Shuttle when clicked on Rest button after rest shuttle left this event will enable and reset shuttle right. In this event I'll create two true actions.

a. Name => Reset Shuttle
b. Event => Click
c. Selection Type => jQuery Selector
d. jQuery Selector => #P13_CITIES_RESET (find this name is simple "SHUTTL_ITEM_NAME+_RESET")
e. Create a true action Execute PL/SQL Code
f. code:

:P13_COUNTRY := null;
:P13_CITIES := null;
:P13_FILTER := null;

g. Page Items to Return => P13_COUNTRY,P13_CITIES,P13_FILTER

h. Fire On Page Load => No
i. Wait For Result => Yes (In run-time after click on reset button this warning will be appear in console Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience ) I'll try to find new way to prevent this warning.

j. Create a true action Execute JavaScript Code
k. code:

//fill the right panel of shuttle item
fillShuttleRight();

l. Fire On Page Load => Yes (Don't forget set this)

5.Create a Dynamic Action Double Click on Shuttle for setting count rows on shuttle item

a. Name => Double Click on Shuttle
b. Event => Double Click
c. Selection Type => jQuery Selector
d. jQuery Selector => .shuttle_left, .shuttle_right
e. Create a true action Execute JavaScript Code
f. code:

//makes shuttle labels
setShuttleLabel();

h. Fire On Page Load => No

6.Create a Dynamic Action Insert-Delete-Shuttle call a Ajax process in order to DML on shuttle item

a. Name => Insert-Delete-Shuttle
b. Event => Click
c. Selection Type => Button
d. Button => INSERT-DELETE
e. Create a true action Execute JavaScript Code
f. code:

var largevalues = '';
    
//set shuttle right
for ( var i=0; i < right.length; i++ ){
    largevalues = largevalues +','+ right.options[i].value;
}
//remove first charecter ','
if (largevalues.length > 0)
    largevalues = largevalues.substr(1);

//You must select items less than 32767 characters
if (largevalues.length > 32767){
    alert('Your selection is more than 32767 chracters');
    return;
}

apex.item( "P13_COUNTRY" ).setValue( "" );
apex.item( "P13_FILTER" ).setValue( "" );

// invoke a PL/SQL process on the server by making an ajax request
var shuttleData = apex.server.process("INSERT_DELETE_SHUTTLE", 
                    {x01: largevalues
                    }, 
    {
        loadingIndicator: "#supershuttle-region",
        loadingIndicatorPosition: "centered"
    });

shuttleData.done(function(data) {
    //submit this page and call a process for send "command Successful" message after reload page
    apex.submit('Successful');
    
})

h. Fire On Page Load => No

Set Processing

I'll create two Process on processing tab and three AJAX Callback.

1.Create a Process Command successful after submit this page(From Dynamic Action) and reload so if you want to show success message can be use this simple trick.

a. Name => Command successful
b. Type => PL/SQL Code
c. code => null;
d. Success Message => Command successful!

2.Create a Process Clear Session State It's optional it depends to you.

a. Name => Clear Session State
b. Type => Clear Session State

3.Create first AJAX Callback FILL_SHUTTLE_LEFT I think to be clear is for filling left panel of shuttle item

a. Name => FILL_SHUTTLE_LEFT
b. Type => PL/SQL Code
c. code =>

Declare
  l_country  long  := apex_application.g_x01; 
  l_filter   long  := apex_application.g_x02;
  l_selected clob ;
  l_count    number;
  
Begin

    apex_json.open_object;
    apex_json.open_array('item');
    
    l_selected := nvl(apex_application.g_x03,-1);

    for rec in ( 
              Select ID, NAME||'('||ID||')' as NAME
              From   SHUTTLE_TEST
              Where (regexp_like(NAME, COALESCE(l_filter,NAME), 'i' ) Or
                     regexp_like(ID, COALESCE(l_filter,To_Char(ID))))
              And  DBMS_LOB.INSTR(','||l_selected||',',','||ID||',')=0
              And  COUNTRY = COALESCE(l_country,COUNTRY) 
            )
            
    loop
        apex_json.open_object;
        apex_json.write('id', rec.ID);
        apex_json.write('value', rec.name); 
        apex_json.close_object;
    end loop;
    
    --records were saved
    Select Count(*)
    Into   l_count
    From   MYTABLE_TEST;
  
    
    apex_json.open_object;
    apex_json.write('id', 99999999);
    apex_json.write('value', l_count); 
    apex_json.close_object;
    
    
    apex_json.close_array;
    apex_json.close_object;
End;

4.Create second AJAX Callback FILL_SHUTTLE_RIGHT

a. Name => FILL_SHUTTLE_RIGHT
b. Type => PL/SQL Code
c. code =>

Declare
    l_count     number;
Begin

    apex_json.open_object;
    apex_json.open_array('item');
    
    for rec in ( 
                Select m.CITY_ID,(Select s.NAME||'('||s.ID||')'||'-'||s.COUNTRY as NAME FROM SHUTTLE_TEST s where s.ID = m.CITY_ID) as NAME
                From   MYTABLE_TEST m
                order by 2
                )
      
    loop
        apex_json.open_object;
        apex_json.write('id', rec.CITY_ID);
        apex_json.write('value', rec.name); 
        apex_json.close_object;
    end loop;
    
    --The records were saved
    Select Count(*)
    Into   l_count
    From   MYTABLE_TEST;
    
    
    apex_json.open_object;
    apex_json.write('id', 99999999);
    apex_json.write('value', l_count); 
    apex_json.close_object;

    apex_json.close_array;
    apex_json.close_object;
  
End;

5.Create third AJAX Callback INSERT_DELETE_SHUTTLE

a. Name => INSERT_DELETE_SHUTTLE
b. Type => PL/SQL Code
c. code =>

declare
    l_cities apex_application_global.vc_arr2;
    l_selected clob;
begin
   
    l_cities := apex_util.string_to_table (apex_application.g_x01,',');
    l_selected := apex_application.g_x01;
    
    --Delete all data
    if l_cities.count = 0 Then
        DELETE MYTABLE_TEST 
        WHERE  FLAG = 'N';
    
    else
        --Delete
        for rec in ( 
                   Select ID
                   From   MYTABLE_TEST
                   Where  DBMS_LOB.INSTR(','||l_selected||',',','||CITY_ID||',')=0
                   )
        loop
            DELETE MYTABLE_TEST where ID = rec.ID;
        end loop;
    
    end if;
    
    --Insert
    for idx in 1..l_cities.count loop
        MERGE INTO MYTABLE_TEST m
        USING (
              SELECT l_cities(idx) as city_id
                    ,'N' as flag
              FROM Dual
              ) s
        ON (m.CITY_ID = to_number(s.city_id))
        WHEN NOT MATCHED THEN
          INSERT (m.CITY_ID, m.FLAG)
          VALUES (s.city_id, s.flag);
      
    end loop;
   
    
    :P13_COUNTRY :='';
    :P13_FILTER  :='';
    
   
    apex_json.open_object;
    apex_json.close_object;
    
  
end;

Tips

1- At least use an item like P13_COUNTRY for setting in Shuttle where clause

2- Maximum characters that can be to save or filter in default Shuttle Item is (4000) However by this blog you can do that with (32765) Characters.

3- Speed transfer rows between left and right panel depends to count rows in shuttle item
4- If after submit you get this error then you should change the value of java -Xmx1024m -Xms256m. link1, link2

Finish this task and enjoy it.

Oracle APEX Capabilities - (OAC)

Sunday, November 20, 2016

Super Shuttle Item

4 comments :