In this tutorial, you will learn another use of drag-n-drop in your Oracle APEX application by creating a Kanban Board that will allow you to drag and drop cards between different system development lifecycle tasks.
A Kanban board is an agile project management tool designed to help visualize work, limit work-in-progress, and maximize efficiency or flow. Kanban boards visually depict work at various stages of a process using cards to represent work items and columns to represent each stage of the process.
We are not using any plug-ins for this module. Here you will be using HTML5 Drag n Drop features to accomplish the task that we will also use in the next topic titled Shopping Cart Drag n Drop.
The images of humans used on the cards will be fetched from the DEMO USERS database table that was created in this video titled Upload and Display Images. In addition to the DEMO USERS table, you will use DEMO SDLC, and DEMO TASK tables in this tutorial. A script for these tables can be downloaded from here. The script creates two tables along with some seed data.
When
you drag a card from one SDLC step and drop it on another, an Ajax Callback
process comes into action and immediately updates the shuffling in a backend
table. Each task is provided as a link. When you click a task, a modal page pops
up to update task details. Similarly, the plus sign that appears under each
SDLC pane allows you to create new tasks.
Let's see how this module is created.
Execute the downloaded script named Table Data.sql in SQL Scripts under SQL Workshop to create the required tables. Note that you also need the DEMO USERS table for this exercise, so make sure you have that table as well in your schema. Also, make sure that the USERID column values in the DEMO TASK table map to the USERID column values in the DEMO USERS table. That is, modify the USERID column values in the script file according to the USERID values in the DEMO USERS table.
Next, create a Blank page. I created page 60 for this module.
Create a new region on
the page and set the following properties for the region. The dynamic PL SQL content
defined here uses two cursors (c_sdlc and c_task) to fetch SDLC steps and
project tasks along with users' information from relevant tables. The cursors'
SELECT statements use multiple HTML tags and CSS classes to generate the page.
The first loop in the BEGIN block is used to display SDLC steps (3 per row) in
separate panes using <tr> and <td> HTML table tags. The second loop
displays task details in the relevant SDLC pane. The hypertext procedures (htp)
package generates HTML tags. It translates PL/SQL into HTML that is understood
by a Web browser. Another important thing used in this code is the APEX WEB SERVICE
API, which enables you to integrate other systems with Application Express by
allowing you to interact with Web services in your application. The API contains
procedures and functions to call both SOAP and RESTful-style Web services. It
contains functions to parse the responses from Web services and to encode or decode
into SOAP-friendly base64 encoding. The BLOB2CLOBBASE64 function of this API is
used to convert a BLOB datatype into a CLOB that is base64 encoded. In this
example, the function gets the user image file from the DEMO USERS table and
converts the BLOB into a CLOB that is base64 encoded. One important point to
note here is that the htp.print procedure takes varchar2 as an input parameter
which means that if you are going to store images bigger than 32k, you'll have
to serve them in chunks.
|
Property |
Value |
|
Title |
Drag n Drop |
|
Type |
PL/SQL Dynamic
Content |
|
PL/SQL
Code |
Declare Vloop number := 1; cursor c_sdlc is select '<div
class="dropdiv" id="'||sdlcno||'" ondrop="drop_handle(this,event)" ondragover="drop_over_handle(event)"> <h1>' ||sdlcname||
'</h1><br> <div
class="dropdiv-new-item-region" style="cursor:
default;"> <a
href="'||apex_util.prepare_url('f?p=&APP_ID.:61:' || :app_session || '::::P61_SDLC:'
||sdlcno|| '-' ||sdlcname)||'"> <div
class="dropdiv-new-card" style="cursor: pointer;"> <i class="fa
fa-plus"></i> </div> </a> </div>' dropsdlc, sdlcno from demo_sdlc order by sdlcno; cursor c_task (b_sdlcno
in number) is select '<div
id="'||t.id||'"
class="dnddiv" draggable="true" ondragstart="drag_handle(event)">
<div
class="card-header"> <i class=" '||
t.project_icon||'"
style="color:black;background:#F7DA1B;"></i> </div> <div class="card-title"> <p>'||t.project_id||'</p> </div> <div
class="card-content"> <div> <div class="img_lnk"> <a href="' || apex_util.prepare_url('f?p=&APP_ID.:61:'
|| :app_session ||
'::::P61_SDLC,P61_ID:' || to_number(b_sdlcno) || ',' || to_number(t.id))
|| '"> <div style="cursor:
pointer;">
<img
src="data:image/png;base64, ' ||
apex_web_service.blob2clobbase64(u.userimage)
|| '"/>'
||u.username ||' - '||t.task_name|| ' </div> </a> </div>
<div>
<p>'||t.task_description||'</p>
</div>
</div> </div> </div>'
dragline from demo_task t,
demo_users u where t.sdlcno =
b_sdlcno and t.userid=u.userid; begin --Use a HTML table to format the output
sys.htp.print('<table>');
sys.htp.print('<tr>'); for r_sdlc in c_sdlc loop
sys.htp.print('<td>');
sys.htp.print(r_sdlc.dropsdlc); for r_task in
c_task(r_sdlc.sdlcno) loop
sys.htp.print(r_task.dragline); end loop;
sys.htp.print('</td>'); if Vloop = 3
then -- Print three columns per row Vloop := 1;
sys.htp.print('</tr>'); else Vloop := Vloop +
1; end if; end loop;
sys.htp.print('</tr>');
sys.htp.print('</table>'); end; |
Save and run the page.
Enter the following CSS
code in the Inline text area to provide a better look.
|
/* CSS code for the div where
the task can be dropped */ .dropdiv {
border: 2px solid #838383;
box-shadow: 3px 3px 3px #BDBEBD;
border-radius: 5px 5px 5px 5px;
display: block;
min-height: 300px;
padding: 16px;
text-decoration: none;
margin-right: 10px;
width: 100%; } .dropdiv:hover {
-webkit-transform: translateY(-6px);
-ms-transform: translateY(-6px);
transform: translateY(-6px); } .dropdiv h1 {
font: bold 18px/18px Helvetica, Arial, Sans-serif;
text-shadow: 0px -1px 2px rgba(0, 0, 0, 0.5);
margin: 0px;
text-align: center;
line-height: 35px;
border-radius: 20px;
background-color: #f1f1f1;
overflow: hidden;
text-overflow: ellipsis;
white-space: nowrap; } /* CSS code for the div to be
dragged */ .dnddiv {
display: inline-block;
width: 100%;
box-shadow: 0 1px 4px 0 rgba(0, 0, 0, 0.44);
border-radius: 1px;
color: rgba(0, 0, 0, 0.87);
transition: all 0.4s ease;
background: #fafafa;
position: relative;
overflow: hidden;
cursor: grab;
margin-bottom: 8px; } .dnddiv:hover {
background: #fff;
box-shadow: 0 10px 20px rgba(0, 0, 0, 0.19), 0 6px 6px rgba(0, 0, 0,
0.23); } .dnddiv .card-content {
margin: 0 20px 6px;
padding-top: 3px;
border-top: 1px solid #eeeeee;
color: #999999; } .dnddiv .card-content .img_lnk {
height: 40px;
line-height: 40px;
border-radius: 20px;
background-color: #f1f1f1;
overflow: hidden;
text-overflow: ellipsis;
white-space: nowrap;
padding-right: 4px; } |
.dropdiv-new-item-region {
clear: both; } .dropdiv-new-card {
float: left;
text-align: center;
margin: 3px 10px 10px 10px;
transition: all 0.3s cubic-bezier(.25, .8,
.25, 1);
box-shadow: 0 1px 3px rgba(0, 0, 0, 0.12), 0 1px 2px
rgba(0, 0, 0, 0.24);
background: #55c555; } .dropdiv-new-card:hover {
box-shadow: 0 10px 20px rgba(0, 0, 0, 0.19), 0 6px 6px
rgba(0, 0, 0, 0.23); } .dropdiv-new-card i {
font-size: 18px;
line-height: 28px;
width: 28px;
height: 28px;
color: white; } .dnddiv .card-content .img_lnk img{
float: left;
height: 40px;
width: 40px;
padding: 0px 4px;
border-radius: 50%;
margin-right: 10px;
background: #f1f1f1; } .dnddiv .card-header {
float: left;
text-align: center;
margin: 6px 0px 6px 6px;
box-shadow: 0 12px 20px -10px rgba(230, 230, 230, 0.28), 0 4px 20px
0px rgba(0, 0, 0, 0.12), 0 7px 8px -5px rgba(230, 230, 230, 0.2);
transition: all 1.0s ease; } .dnddiv .card-header i {
font-size: 18px;
line-height: 28px;
width: 28px;
height: 28px;
/*color: white;*/
transition: all 1.0s ease; } .dnddiv .card-title {
text-align: left;
padding: 0 6px;
overflow: hidden;
min-height: 40px;
display: table;
margin-top: 10px; } |
Add two text field
items to the region to display dragged task id and the system development life
cycle ID where the dragged task will be dropped.
|
Property |
Value – Text Item 1 |
Value – Text Item 2 |
|
Name |
P60_ID |
P60_SDLCNO |
|
Type |
Text
Field |
Text
Field |
|
Label |
Task
ID: |
SDLC
ID: |
|
Start New Row |
Yes |
No |
|
Template |
Optional
Floating |
Optional
Floating |
Add the following
dynamic action to get and then set values for the Task ID and SDLC ID text
field items.
|
Property |
Value |
|
Name |
Get
and Set Task ID and SDLC No |
|
Event |
Mouse
Button Release |
|
Selection Type |
jQuery
Selector |
|
jQuery Selector |
.dnddiv |
|
True Action |
Execute
JavaScript Code |
|
Code |
drop_handle() |
Finally, add the following Ajax Callback process to submit card shuffling.
|
Property |
Value |
|
Name |
SAVE_RESHUFFLING |
|
Type |
PL/SQL Code |
|
PL/SQL
Code |
begin update demo_task set
sdlcno = :P60_SDLCNO where id = :P60_ID; --
Eliminate "Error: SyntaxError: Unexpected end of JSON input"
apex_json.open_object;
apex_json.write('success', true);
apex_json.close_object; end; |
|
Point |
Ajax Callback |
The two SQL statements used in the Drag n Drop PL SQL dynamic content comprise a couple of links to call Page 62 that can be used to create a new task as well as to modify details of an existing task. In order to keep this tutorial small, in the upcoming steps I will just show you how that page displays the primary key information from the calling page (Page 60). You can use this information to enhance this module and apply task addition and modification functionalities yourself.
Click the root node of
the page, and enter three functions in the Function and Global Variable Declaration text area. Note the item's prefix that should match your page number.
|
|
function
drop_over_handle(evt) { evt.preventDefault(); } function
drag_handle(evt) { evt.dataTransfer.setData("Text",evt.target.id); } function drop_handle(obj,evt)
{ evt.preventDefault(evt); var
data=evt.dataTransfer.getData("Text");
evt.target.appendChild(document.getElementById(data)); $x("P60_ID").value = data; $x("P60_SDLCNO").value =
obj.id; apex.server.process (
"SAVE_RESHUFFLING", { pageItems: "#P60_ID, #P60_SDLCNO" }
); } |
To make an element draggable, you have to set the draggable attribute of that element to true and that is what you set in the PL SQL dynamic content earlier. Then, you specify what should happen when the element is dragged. In the current scenario, the on drag start attribute calls a function named drag handle that specifies what data is to be dragged. The dataTransfer.setData() method sets the data type and the value of the dragged data. In this case, the data type is "text" and the value is the id of the draggable element that is, task id denoted by t.id.
The on drag over event specifies where the dragged data can be dropped. By default, data and elements cannot be dropped in other elements. To allow a drop, you must prevent the default handling of the element. This is done by calling the event.preventDefault() method for the on drag over event.
When the dragged data is dropped, a drop event occurs. In this example, the ondrop attribute calls a function drop handle, which performs three tasks:
One: Call prevent Default() to prevent the browser
default handling of the data
Secondly: Get the
dragged data with the dataTransfer.getData() method. This method
will return any data that was set to the same type in the setData() method
And finally: Append the dragged element into the drop element. The appendChild() method appends the dragged task at the bottom.
We used $x Apex Java Script API function to set values of the two-page items (P61_SDLC and P61_ID). These items and the corresponding page will be created shortly to evaluate values of task id (dragged element) and SDLC id (target element).
Finally, we used the apex server process function, which calls a PL SQL on-demand Ajax callback process named SAVE-RESHUFFLING, and sets values for page items P60_ID (TASK ID) and P60_SDLCNO in session state using jQuery selector syntax.
Create another Blank page that can be used to add and modify tasks.
|
Property |
Value |
|
Name |
Project
Tasks |
|
Page Mode |
Modal
Page |
|
Dialog
Template |
Wizard
Modal Dialog |
Create a new static
content region on this page. Set the title of this region to Add and Modify
Task, and then add the following page items to show SDLC and TASK IDs.
|
Property |
Value – Display Only Item 1 |
Value – Display Only Item 2 |
|
Name |
P61_SDLC |
P61_ID |
|
Type |
Display
Only |
Display
Only |
|
Label |
SDLC |
Task
ID |
|
Template |
Optional
Floating |
Optional
Floating |
Add a button named CANCEL to the region.
Add a Cancel Dialog
dynamic action:
|
Property |
Value |
|
Name |
Cancel
Dialog |
|
Event |
Click |
|
Selection Type |
Button |
|
Button |
CANCEL |
|
Action |
Cancel
Dialog |
Save the page and test the Kanban board.
That's it. Hope you enjoyed this tutorial and learned some new techniques for your own Oracle APEX applications.
For Visual Instruction Watch This Video
