create or replace procedure show_umfrage_svg
is
  v_svg_width           number := 500;
  v_svg_height          number := 400;
  v_svg_margin          number := 15;
  v_svg_textbar_left    number := 30;
  v_svg_textbar_bottom  number := 15;

  v_svg_bgcolor         varchar2(6) := 'EEEEEE';

  v_x_label             varchar2(5) := 'ALTER';
  v_y_label             varchar2(9) := 'EINKOMMEN';

  v_y_min               number := 18;
  v_y_max               number := 100;
  v_x_min               number := 500;
  v_x_max               number := 10000;

  v_svg_x_null          number;
  v_svg_y_null          number;
  v_svg_x_step          number;
  v_svg_y_step          number;

  v_circle_width        number := 5;
begin
  owa_util.mime_header('image/svg+xml', false);
  owa_util.http_header_close;

  htp.p('<?xml version="1.0" standalone="yes"?>');
  htp.p('<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">');
  htp.p('<svg width="'||v_svg_width||'px" ');
  htp.p('     height="'||v_svg_height||'px" ');
  htp.p('     version="1.1" ');
  htp.p('     xmlns="http://www.w3.org/2000/svg">');
  --
  -- Hier wird das "Koordinatensystem dargestellt"
  -- 1. Y-Achse ...
  --
  htp.p('<line stroke="black" ');
  htp.p('      width="1pt"');
  htp.p('      x1="'||(v_svg_textbar_left + v_svg_margin)||'" ');
  htp.p('      y1="'||(v_svg_margin)||'" ');
  htp.p('      x2="'||(v_svg_margin + v_svg_textbar_left)||'" ');
  htp.p('      y2="'||(v_svg_height - v_svg_textbar_bottom - v_svg_margin)||'"/>');
  --
  -- 2. X-Achse ...
  --
  htp.p('<line stroke="black" ');
  htp.p('      width="1pt" ');
  htp.p('      x1="'||(v_svg_margin + v_svg_textbar_left)||'" ');
  htp.p('      y1="'||(v_svg_height - v_svg_margin - v_svg_textbar_bottom)||'" ');
  htp.p('      x2="'||(v_svg_width  - v_svg_margin)||'" ');
  htp.p('      y2="'||(v_svg_height - v_svg_margin - v_svg_textbar_bottom)||'" />');
  -- 
  -- 3. Diagrammfläche grau hinterlegen
  --
  htp.p('<rect x="'||(v_svg_textbar_left + v_svg_margin)||'" ');
  htp.p('      y="'||(v_svg_margin)||'" ');
  htp.p('      width="' ||(v_svg_width  - (2 * v_svg_margin) - v_svg_textbar_left)||'" ');
  htp.p('      height="'||(v_svg_height - (2 * v_svg_margin) - v_svg_textbar_bottom)||'" ');
  htp.p('      fill="#' ||(v_svg_bgcolor)||'"/>');

  --
  -- Maximum der Y-Achse
  --
  htp.p('<text text-anchor="end" ');
  htp.p('      dominant-baseline="hanging" ');
  htp.p('      x="'||(v_svg_margin+v_svg_textbar_left)||'" ');
  htp.p('      y="'||(v_svg_margin)||'">');
  htp.p(v_y_max);
  htp.p('</text>');

  --
  -- Minimum der Y-Achse
  --
  htp.p('<text text-anchor="end" ');
  htp.p('      x="'||(v_svg_margin+v_svg_textbar_left)||'" ');
  htp.p('      y="'||(v_svg_height - v_svg_margin - v_svg_textbar_bottom)||'">');
  htp.p(v_y_min);
  htp.p('</text>');

  --
  -- Maximum der X-Achse
  --
  htp.p('<text text-anchor="end" ');
  htp.p('      x="'||(v_svg_width - v_svg_margin)||'" ');
  htp.p('      y="'||(v_svg_height - v_svg_margin)||'">');
  htp.p(v_x_max);
  htp.p('</text>');

  --
  -- Minimum der X-Achse
  --
  htp.p('<text x="'||(v_svg_margin + v_svg_textbar_left)||'" ');
  htp.p('      y="'||(v_svg_height - v_svg_margin)||'">');
  htp.p(v_x_min);
  htp.p('</text>');

  --
  -- Beschriftung der X-Achse (Mitte der Diagrammfläche)
  --
  htp.p('<text text-anchor="middle" ');
  htp.p('      x="'||(
   (v_svg_margin + v_svg_textbar_left) +
   (v_svg_width - (2 * v_svg_margin) - v_svg_textbar_left) / 2
  )||'" ');
  htp.p('      y="'||(v_svg_height-v_svg_margin)||'">');
  htp.p(v_x_label);
  htp.p('</text>');

  --
  -- Beschriftung der Y-Achse (Mitte der Diagrammfläche)
  --
  htp.p('<text writing-mode="tb-rl" ');
  htp.p('      dominant-baseline="hanging" ');
  htp.p('      text-anchor="middle" ');
  htp.p('      x="'||(v_svg_margin + v_svg_textbar_left)||'" ');
  htp.p('      y="'||round(
    (v_svg_margin +
      (v_svg_height - (2 * v_svg_margin) - v_svg_textbar_bottom) / 2
    ),0
  )||'">');
  htp.p(v_y_label);
  htp.p('</text>');

  -- 
  -- Datenpunkte: Position der Null-linie bestimmen.
  --
  v_svg_y_null := v_svg_height - v_svg_margin - v_svg_textbar_bottom;
  v_svg_x_null := v_svg_margin + v_svg_textbar_left;

  -- 
  -- Datenpunkte: "Schrittweite" bestimmen 
  --
  v_svg_x_step := (v_svg_width - (2 * v_svg_margin) - v_svg_textbar_left) /
                  (v_x_max - v_x_min);
  v_svg_y_step := -( (v_svg_height - (2 * v_svg_margin) - v_svg_textbar_bottom) /
                     (v_y_max - v_y_min)
                   );

  for werte in (
    select
      "ALTER"         as y,
      "MTL_EINKOMMEN" as x,
      case
        when "STIMME_ZU" = 'Y' then '00FF00'
        when "STIMME_ZU" = 'N' then 'FF0000'
        when "STIMME_ZU" is null then 'CCCCCC'
      end as "WERT"
    from umfrage
  ) loop
    htp.p('<circle cy="'||(v_svg_y_null + ((werte."Y" - v_y_min) * v_svg_y_step))||'" ');
    htp.p('        cx="'||(v_svg_x_null + ((werte."X" - v_x_min) * v_svg_x_step))||'" ');
    htp.p('        r="'||v_circle_width||'" ');
    htp.p('        stroke="black" ');
    htp.p('        fill="#'||werte."WERT"||'"/>');
  end loop;

  htp.p('</svg>');
end;
/