package body ted_p1068 is
function color_random_light
return varchar2
is
begin
return '"rgb(' || trim(trunc(155 + (dbms_random.value * 100))) || ','
|| trim(trunc(155 + (dbms_random.value * 100))) || ','
|| trim(trunc(155 + (dbms_random.value * 100)))
|| ')"'
;
end color_random_light;
procedure write_javascript
( p_id in varchar2
, p_data in varchar2
, p_labels in varchar2
, p_pie_colors in varchar2 := null
, p_label_font in varchar2 := '10px sans-serif'
, p_label_fillstyle in varchar2 := '#000000'
)
is
v_pie_colors varchar2(32767);
v_vc_arr2 apex_application_global.vc_arr2;
v_data_count pls_integer;
begin
v_vc_arr2 := apex_string.string_to_table(p_str => p_data, p_sep => ',');
v_data_count := v_vc_arr2.count;
v_vc_arr2 := apex_string.string_to_table(p_str => p_pie_colors);
for i in v_vc_arr2.count+1 .. v_data_count loop
v_vc_arr2(i) := color_random_light;
end loop;
v_pie_colors := apex_string.table_to_string(p_table => v_vc_arr2, p_sep => ',');
htp.p('<script type="text/javascript">
/* thanks to http://www.phpied.com/canvas-pie/ */
function draw_pie_' || p_id || '() {
var canvas = document.getElementById("' || p_id || '"); var ctx = canvas.getContext("2d"); var canvas_size = [canvas.width, canvas.height];
var data = [' || p_data || '], value = 0, total = 0;
var labels = [' || p_labels || '];
var pie_colors = [' || v_pie_colors || '];
var radius = Math.min(canvas_size[0], canvas_size[1]) / 2;
var center = [canvas_size[0]/2, canvas_size[1]/2];
var label_width;
var sofar; // keep track of progress
// set font styles
ctx.font = "' || p_label_font || '";
// calculate total
for (var piece in data) { total = total + data[piece];
}
// draw the pie pieces
sofar = 0;
for (var piece in data) {
var thisvalue = data[piece] / total;
ctx.beginPath();
ctx.arc
( center[0]
, center[1]
, radius
, Math.PI * (- 0.5 + 2 * sofar) // -0.5 sets the start to the 12 o''clock position
, Math.PI * (- 0.5 + 2 * (sofar + thisvalue)) // should eliminate white lines between pie pieces, but doesn''t look very nice: -0.5 + 2 * Math.PI
, false
)
;
ctx.lineTo(center[0], center[1]); // line back to the center
ctx.closePath();
ctx.fillStyle = pie_colors[piece]; // set fill color
ctx.fill();
sofar += thisvalue; // increment progress tracker
}
// draw the labels
sofar = 0;
for (var piece in data) {
var thisvalue = data[piece] / total;
if (labels[piece]) { // only draw a label if it is not null label_width = ctx.measureText(labels[piece]).width;
ctx.fillStyle = "' || p_label_fillstyle || '";
ctx.fillText( labels[piece]
, center[0] -label_width/2 + 0.75*radius * Math.cos(Math.PI * (- 0.5 + 2 * (sofar+0.5 * thisvalue)))
, center[1] + 0.75*radius * Math.sin(Math.PI * (- 0.5 + 2 * (sofar+0.5 * thisvalue)))
)
}
sofar += thisvalue; // increment progress tracker
}
}
</script>');
end write_javascript;
procedure departments
( p_id in varchar2
)
is
v_data varchar2(4000);
v_labels varchar2(4000);
begin
select listagg( '"' || dname || '"', ',') within group (order by sumsal desc) as data
, listagg ( nvl(sumsal,0), ',') within group (order by sumsal desc) as labels
into v_labels
, v_data
from
( select dname
, sum(sal) as sumsal
from emp
right outer join dept
using (deptno)
group by dname
having sum(sal) is not null
)
;
write_javascript
( p_id => p_id
, p_data => v_data
, p_labels => v_labels
)
;
end departments;
procedure employees
( p_id in varchar2
)
is
v_data varchar2(4000);
v_labels varchar2(4000);
begin
select listagg( '"' || ename || '"', ',') within group (order by ename) as data
, listagg ( nvl(sal,0), ',') within group (order by ename) as labels
into v_labels
, v_data
from
( select ename
, sal
from emp
order by ename
)
;
write_javascript
( p_id => p_id
, p_data => v_data
, p_labels => v_labels
, p_pie_colors => '"#E3E3E2","#60C3AD","#337D81","#004A60"'
)
;
end employees;
end ted_p1068;