set linesize 200
set pagesize 500



create or replace type string_table_collection
as table of varchar2(4000)
/

create or replace 
function merge_string_table_to_string
( p_tbl in string_table_collection
) return varchar2
is
  l_str varchar2(4000):='';
  l_pos number(4);
begin
  -- the string merge is for the * character
  -- that means that starting with p_tbl(1), all * from the other strings are applied to it
  l_str:= p_tbl(1);
  for i in least(2,p_tbl.count)..p_tbl.count loop
    -- find all * in p_tbl(i) and transfer them to l_str
    for j in 1..length(p_tbl(i)) loop
      if substr(p_tbl(i),j,1) <> ' '
      then
        l_str:= substr(l_str, 1, j -1 )||substr(p_tbl(i),j,1)||substr(l_str, j +1 );
      end if;
    end loop;
  end loop;
  return l_str;
end;
/


with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select ename  category      
  ,      'Employee '||empno||' '||ename category_label
  ,      sal value
  from   emp e
  where  deptno = 20  
)

with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select deptno  category      
  ,      'Department '||deptno||' '||(select dname from dept d where d.deptno = e.deptno) category_label
  ,      sum(sal) value
  from   emp e
  group
  by     deptno
)


with chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select object_type category      
  ,      initcap(object_type) category_label
  ,      count(*) value
  from   user_objects
  group
  by     object_type
)
, gauge_dimensions as
( select 30 radius -- the radius of the gauge - can be changed
  ,      6  marker_count -- the number of markers for the gauge
  ,      0  low_limit
  ,      60 high_limit
  from   dual
)
, categories as 
( select distinct 
         category_label 
  ,      category
  ,      chr(65 -1 + rownum) legend_marker
  from   chart_query
)
, lines as
( SELECT LEVEL rn
  ,      rpad(' ', 2*radius) line
  FROM   gauge_dimensions 
  CONNECT 
  BY     radius+4 > LEVEL
  ORDER 
  BY     LEVEL ASC 
) 
, marker_rows as
( SELECT LEVEL rn
  FROM   gauge_dimensions 
  CONNECT 
  BY     marker_count+1 > LEVEL
  ORDER 
  BY     LEVEL ASC 
) 
, marker_values as
( SELECT rn
  ,      low_limit + (rn-1)* (high_limit - low_limit)/(marker_count-1)  marker_value
  FROM   gauge_dimensions 
  ,      marker_rows
  ORDER 
  BY     rn
) 
, marker_angles as
( select marker_value
  ,      (marker_value - low_limit)/(high_limit - low_limit) * 2* asin(1) alpha -- alpha, angle in gauge in rads
  from   marker_values
  ,      gauge_dimensions
)
, markers as
( select marker_value
  ,      1 + round(radius * (1 - cos(alpha)))   marker_x   -- because we cannot deal with x ==0 
  ,      0 + round(radius * sin (alpha))    marker_y
  from   marker_angles
  ,      gauge_dimensions
)
, dial_angles as
( select value
  ,      category
  ,      (value - low_limit)/(high_limit - low_limit) * 2* asin(1) alpha -- alpha, angle in gauge in rads
  from   chart_query
  ,      gauge_dimensions
)
, dials as
( select value
  ,      d.category
  ,      alpha
  ,      case
         when abs(asin(1) - alpha) < 0.0050 -- tan(alpha) getting too close to infinity
         then radius + 1
         else  radius + 1 - round((radius - lines.rn) / tan (alpha))  
         end marker_x
  ,      radius  - lines.rn     marker_y
  ,      c.legend_marker
  from   dial_angles d
  ,      gauge_dimensions
  ,      lines
  ,      categories c
  where  c.category = d.category
  and    lines.rn <= radius
)
, baselayout as
( select merge_string_table_to_string
         ( cast ( collect
                  ( case 
                    when marker_value is not null
                    then rpad(lpad( ' ', marker_x )||to_char(round(marker_value)) ,10+ 2* radius)
                    else rpad(' ',   1+ 2* radius)
                    end  
                  ) as string_table_collection
                )
         ) line
 ,      lines.rn linern
 from   gauge_dimensions 
        cross join 
        lines
        left outer join
        markers
        on (lines.rn = radius - markers.marker_y + 1
        )
 group 
 by     lines.rn
 order
 by    lines.rn
 )
, gauge_chart as 
( select case
         when d.marker_x is not null
         then substr(b.line, 1, d.marker_x)
              ||case
                when  ( b.linern < round(0.5* radius) 
                        and 
                        lead(b.linern) over (partition by d.category order by b.linern) >= round(0.5* radius) -- the first time we cross half the radius
                      )
                      or 
                      ( abs(d.marker_x - radius) >= round(0.5* radius) 
                        and 
                        lead(abs(d.marker_x - radius)) over (partition by d.category order by b.linern)  < round(0.5* radius) -- the first time we cross half the radius
                       ) 
                then  legend_marker
                else  '*'
                end
              ||substr(b.line, d.marker_x+1)
         else b.line
         end line
  ,      d.category
  ,      b.linern
  from   gauge_dimensions 
         cross join 
         baselayout b
         left outer join
         dials d
         on ( b.linern = radius - d.marker_y +1
              and
              power(marker_x - radius,2) + power( d.marker_y,2) < power(0.9*radius,2)
             )
  order by b.linern     
)
, merged_chart as
( select  merge_string_table_to_string(cast (collect(line) as string_table_collection)) line
  from    gauge_chart
  group
  by     linern
  )
, chart_legend as
( select label
  from   (  select null label
            from   dual
            union all
            select 'Legend:'||chr(13)||chr(10)||'-------'
            from   dual
            union all
            select rpad(legend_marker||': '||c.category_label,30)||' ='||value
            from   categories c
                   join
                   chart_query q
                   on (c.category = q.category)
          )
)
select line "Gauge Chart"
from   merged_chart
union all
select *
from   chart_legend
/