Drag&Drop Planboard

Creating out of the box functionality for web apps with APEX is as fun as it is challenging. Some time ago Roel Hartman and me started working on a transport and logistics application in APEX. (Roel allready blogged about it once here)
One specific part of this application was a planboard which used dragging and dropping with orders in sort of a truck calendar. It was build in Java and had all the swingy features you'd expect.
The client wanted to know if this was possible with APEX. Well, sure it is. We'll just use the full pallete available for us with the web browser! CLICK HERE TO SEE IT.
Obviously their are many paths to take when considering the design for this sort of functionality, especially when you have APEX at your disposal to create it.

Should we use the available calendar?
Could we use a tabular form?
Do we use standard APEX DML report/forms?

Well, this is what we did ;)

The design


1. Collect the data for the planboard view, it will show the truck number (equipment number) and the orders in the days of the week:

Datamodel

Viewquery

SELECT trucks.truck_id
,      to_char(to_date(load_date), 'YYYYIW') load_week
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'maandag'  , order_id, null )) mon
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'dinsdag'  , order_id, null )) tue
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'woensdag' , order_id, null )) wed
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'donderdag', order_id, null )) thu
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'vrijdag'  , order_id, null )) fri
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'zaterdag' , order_id, null )) sat
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'zondag'   , order_id, null )) sun
FROM   trucks,
     orders
WHERE  orders.truck_id (+) = trucks.truck_id
GROUP BY trucks.truck_id,
to_char(to_date(load_date), 'YYYYIW')
ORDER BY trucks.truck_id

2. The report region has the planboard view as source. For every column box (value or not) a unique id is created . These id’s make sure that a column box is a container where you can drop items in. The column boxes with a value, should show the item with its value.

Report buildup

The column formatting for every day column

3. The report region must also call a javascript function CreateDragContainer, for every container in the region. While doing this, all items inside a container (or just the values selected with the view) become draggable. With the unique id’s, movement can be tracked, and when an order item is dropped in a container, the rest of the handling can start. This is all done by Javascript, where credit must go out for a part of the dragging & dropping to Mark Kahn at webreference.
The most important part is that the id's are filtered, and passed to application items, and that an AJAX call is made to an application process. Here's the AJAX call code:

var ajaxRequest = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=updatePlanbord',null);
if (curTarget.id.substr(0,3) == 'new'){
  ajaxRequest.add('P_TARGET', curTarget.id.substring(13, curTarget.id.length));
  ajaxRequest.add('P_ADDACTION', 'true');
  var cDay = curTarget.id.substring(9,12);
}else{
  ajaxRequest.add('P_TARGET', curTarget.id.substring(9, curTarget.id.length));
  ajaxRequest.add('P_ADDACTION', 'false');

  var cDay = curTarget.id.substring(5,8);
}
          var cDayDate
          if (cDay == 'mon'){
           cDayDate = html_GetElement('P1_MON_DAT').value;
          }else if (cDay == 'tue'){
           cDayDate = html_GetElement('P1_TUE_DATE').value;
          }else if (cDay == 'wed'){

           cDayDate = html_GetElement('P1_WED_DATE').value;
          }else if (cDay == 'thu'){
           cDayDate = html_GetElement('P1_THU_DATE').value;
          }else if (cDay == 'fri'){
           cDayDate = html_GetElement('P1_FRI_DATE').value;
          }else if (cDay == 'sat'){
          cDayDate = html_GetElement('P1_SAT_DATE').value;
          }else if (cDay == 'sun'){
           cDayDate = html_GetElement('P1_SUN_DATE').value;
          }

ajaxRequest.add('P_PARENT', rootContainer.id.substring(9, rootContainer.id.length));
ajaxRequest.add('P_LOADDATE', cDayDate);

ajaxRequest.GetAsync(f_AsyncReturn);

4. The application process can do two things, UPDATE an existing order when it is dropped in another truck, or UPDATE an existing order when it had no truck (open action) and was dropped in another truck. Here's the code:

DECLARE
type_action varchar2(10);

BEGIN
type_action := ''||:P_ADDACTION||'';

IF type_action= 'false' THEN

UPDATE orders SET truck_id = :P_PARENT WHERE order_id = :P_TARGET AND to_date(load_date, 'dd-mm-yyyy') = to_date(''||:P_LOADDATE||'', 'dd-mm-yyyy');

ELSIF type_action = 'true' THEN

UPDATE orders SET truck_id = :P_PARENT WHERE order_id = :P_TARGET;

INSERT INTO orders (ORDER_ID, DESTINATION, LOAD_DATE, TRUCK_ID) VALUES (ODERS_SEQ.nextval, NULL, to_date(''||:P_LOADDATE||'', 'dd-mm-yyyy'), NULL);

END IF;
COMMIT;
EXCEPTION WHEN OTHERS THEN
htp.p(SQLERRM);
END;

After some CSS tricks and custom theming, you get the result you see in the browser. Needless to say, the client was quite impressed with the result, and I think it is another example of the flexibility APEX has to offer being in a web browser!

If you'd like to know more on this application and you're at the Oracle Open World this year, vote for this presentation on Oracle Mix by clicking HERE