Implementasi jQuery Sortable pada APEX Classic Report (AJAX)

Sebelumnya silahkan pelajari UI dokumentasi disini dan demonya disini.
Contoh kasus kita terapkan pada tabel EMP.
Kita bisa lakukan dengan memakai konsep AJAX atau Dynamic Action.

Langkah implementasi dalam APEX untuk AJAX sbb:

• Tambahkan kolom ORDER_BY number(3);
• Buatlah report dengan memakai collection contoh nama collection : 'R_SORT'
1. Buat process pl/sql before header

declare
 l_col_name varchar2(100):='R_SORT';
 l_query varchar2(4000);
begin
  l_query:='select a.EMPNO,a.ENAME,a.JOB,a.MGR,a.HIREDATE,a.SAL,a.COMM,a.DEPTNO,a.ORDER_BY from emp a order by a.ORDER_BY';
 if not APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => l_col_name) then
  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
          p_collection_name => l_col_name
        , p_query => l_query
      );
 end if;
end;
      

2. Buat calssic report dengan query

SELECT coll.seq_id No
     , coll.c001 empno
     , coll.c002 ename
     , coll.c003 job
     , coll.c004 mgr
     , coll.c005 hiredate
     , coll.c006 comm
     , coll.c007 sal
     , coll.c008 deptno
FROM APEX_collections coll
WHERE coll.collection_name = 'R_SORT'
ORDER BY to_number(coll.seq_id)
    

• Buatlah Application Process : REORDER dengan process point : on demand

declare
  -- ajax call to move a seq_id collection
 l_seq     number(5);
 l_seq_id     number(5); 
 l_old        number(5); 
 l_new        number(5); 
 l_counter    number(5);
 l_abscounter    number(5);
begin
 l_seq_id    := to_number(wwv_flow.g_x01);
 l_counter   := to_number(wwv_flow.g_x02);
 l_abscounter  :=abs(l_counter);
      if l_counter < 0 then
      -- Move UP
        for i in 1 .. l_abscounter loop
        apex_collection.move_member_down(
            p_collection_name => 'R_SORT'
          , p_seq             => l_seq_id
        );     
          l_seq_id := l_seq_id - 1;
        end loop;
      elsif l_counter > 0 then
      -- Move DOWN
        for i in 1 .. l_abscounter loop
          apex_collection.move_member_up(
              p_collection_name => 'R_SORT'
            , p_seq             => l_seq_id
          );        
          l_seq_id := l_seq_id + 1;        
        end loop;      
      end if;    
htp.prn('success');
end;
  

• Buatlah sebuah function jQuery untuk setup preparation Sortable dan AJAX memanggil application process REORDER:

function setSortable() {
    var vStart = 0;
    var vEnd = 0;
    var vCounter = 0;
    var vSeqId = 0;
    var sortRegion = '#report_RSORT_1 .report-standard tbody:first'; // selector tergantung report yg anda pakai
    $(sortRegion).sortable({
       cursor      : 'wait',
       distance    : 15,
       revert      : 500,
       start       : function (event, ui) {
        vStart = Number(ui.item.index()) + 1;
        vSeqId = Number($(this).find('td[headers="NO"]')[ui.item.index()].innerHTML);},
       stop        : function (event, ui) {
        vEnd = Number(ui.item.index()) + 1;
        vCounter = vEnd - vStart;
        apex.server.process("REORDER"
              , { x01: vSeqId, x02: vCounter }
              , {
                  dataType: 'text'
                  , success: function (pData) {
                  if (pData != 'success') {
                           alert(pData);
                   }
                  else {
                     // refresh report id tergantung report yg anda pakai
                    $('#report_28558823308938595811_catch').trigger('apexrefresh');
                  }
        }
       }
    );
    }
   });
   $(sortRegion).disableSelection();
}

• tambahkan function setup preparation Sortable pada  bagian on load : setSortable();
• Buatlah DA after refresh pada report sortable execute javascript : setSortable();

Liat demo hasilnya disini.