Sunday, November 20, 2016

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:

  1. Filter on the Shuttle Item
  2. Show the Count Rows on the Left/Right panel
  3. Separate rows on the right panel by color (Selected rows or saved at the table)
  4. Maximum rows on the right panel for manipulating is (32765) Characters
  5. 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_FILTERWith 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_CITIESThis 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 itemlast 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: " );
    $( ".shuttleSelect2" ).prepend( "Saved: " );
    $( ".shuttleSelect2" ).prepend( "Selected count: " );
    
}

//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.


4 comments :

  1. Hi Saeed,

    Very nice. Why not make it a plug-in?

    Cheers,
    Christian

    ReplyDelete
    Replies
    1. Hi Christian,

      Thank you for your comment. I'll think about your suggestion, however the main purpose of this post was to explain step by step so some parts might be useful for someone one day.

      Delete
  2. Very Very Nice! Thanks for sharing.

    ReplyDelete