Monday, July 7, 2008

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

10 comments:

Stew said...

Sorry that I don't speak German.

This is very cool stuff you're showing. It looks like your application doesn't have so many grid points that creating an item for each point would be too tedious (just a little :-)).

I noticed in your demo that saving doesn't exactly work? That is, items return to their original location. I'm guessing that's so the demo doesn't get updated? Or am I moving things incorrectly.

I also noticed if I cancel the change that I get the message "Geen order opslaan". I've no clue what that might mean! :-)

Sorry I can't make it to OpenWorld to hear you speak about it. Great job!

Rutger said...

Hi Stew,

Well which orders don't stick after saving it? It should work, tested it a minute ago, because the demo does update. And it creates a new open action when you drag an open action order in a truck.

Also, the grid points are fixed only for the day that the order is in. Because the order has a date, not the truck :)

And as for the untranslated bit, I can tell you as much that it isn't german :) But Dutch, changed it to : "Didn't save an order" !

As for the job, Thanks ;)

Patrick Wolf said...

Hi,

is it possible that the demo is not working anymore? FF 3 shows a lot of javascript errors, also tried it with IE 6 and it didn't work too.

Patrick
PS: You should change your blog language in Blogger to English, otherwise a lot of people will have problems to identify where/how to comment if everything is in Dutch :-)

Francis said...

Hi Rutger,

Is it possible that the example application is not working ?

I tried in ie and firefox 3 and I get the cursor for drag and drop but I am not able to drag it.

Thanks

Francis Mignault.
blog :

P.S. I will be at Openworld and looking forward to see more...

P.S. 2 : Patrick is right. I don't know German and it is kind of hard for me to figure out the links in google blog for commenting.

Rutger said...

@Patrick/Francis, yes you're right, switched something off accidentally yesterday, but it's working again now. Try it again ;)

I changed the board language, so everyone should be able to read everything :) Thanks for the tip.

Stew said...

Rutger,

Thanks for changing your blog language, though I managed before, even though I obviously don't speak Dutch, which, given that my family's from there, I'd have had a generational clue? :-) But I'm 10th generation American, so it's pretty faint now!

Anyway, I was able to get the demo to work if I was very careful about dropping exactly in an open spot by waiting until I saw it blink in the new spot. I also realized that I couldn't drag an order to a different day and have it stick because the order would not blink in an open spot on a different day.

Stew said...

Hey I just realized you told Patrick and Francis they were right that it wasn't working, but I was the first to point that out and yet I'm just a dumb American who can't figure it out so don't get any credit??? ;-)

Rutger said...

Hi Stew,

I wouldn't dare to deny you some credits :) But the problem that Patrick and Francis had, didn't exist yet when you did your first post.
The errors they talked about were caused by the message I inserted translating "Geen order opslaan" :)

But also thanks for pointing that out :D

To comment on the grid, with the exception of the upper border of a container, an item should blink in a container when it is 51% in the container (right, left, bottom).

And, I'll be the last to say that (all) Americans are dumb.

Stew said...

Oh, so it was just me specifically that was dumb? ;-)

And yet... I didn't have that much trouble with the Dutch Blogger links so I guess I'm just dumb about some things! :)

I'm not sure how my repeated attempts didn't manage to get the order in the container properly but it's either that or I was always trying to move orders to different days.

BTW, is there any chance you could wrap this up as a complete export (with table installs) for us to download? I have a slightly similar need in mind that would just take some minor changes to make a nice demo for my management.

Mark Lancaster said...

Excellent work Rutger.

Nice demo and explanation.

Look forward to seeing your presentation at OpenWorld.

Mark