From b3adc673150bb5c47255df1ee55c016af5e7be14 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 22 Nov 2005 01:02:54 +0000 Subject: [PATCH] reporter (nearing) mini-release state git-svn-id: svn://svn.open-ils.org/ILS/trunk@2112 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../OpenILS/WWW/Reporter/transforms.pm | 4 +- Open-ILS/src/reporter/clark-kent.pl | 170 ++++++++++++------ Open-ILS/src/reporter/report_base.example.xml | 2 +- Open-ILS/src/reporter/tables.example.xml | 56 ++++++ Open-ILS/src/reporter/templates/stage3.ttk | 52 ++++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 38 ++++ 6 files changed, 266 insertions(+), 56 deletions(-) diff --git a/Open-ILS/src/perlmods/OpenILS/WWW/Reporter/transforms.pm b/Open-ILS/src/perlmods/OpenILS/WWW/Reporter/transforms.pm index d3b3c2d7e3..c34795e468 100644 --- a/Open-ILS/src/perlmods/OpenILS/WWW/Reporter/transforms.pm +++ b/Open-ILS/src/perlmods/OpenILS/WWW/Reporter/transforms.pm @@ -64,12 +64,12 @@ our $dtype_xforms = { 'group' => 1 }, 'dateformat' => { # see http://www.postgresql.org/docs/8.0/interactive/functions-formatting.html 'label' => 'Format date and time', - 'select' => 'TO_CHAR(?COLNAME?,?PARAM?)', + 'select' => "TO_CHAR(?COLNAME?,'?PARAM?')", 'param' => 1, 'group' => 1 }, 'numformat' => { # see http://www.postgresql.org/docs/8.0/interactive/functions-formatting.html 'label' => 'Format Numeric data', - 'select' => 'TO_CHAR(?COLNAME?,?PARAM?)', + 'select' => "TO_CHAR(?COLNAME?,'?PARAM?')", 'param' => 1, 'group' => 1 }, }; diff --git a/Open-ILS/src/reporter/clark-kent.pl b/Open-ILS/src/reporter/clark-kent.pl index 8f3942a037..25ba18255e 100755 --- a/Open-ILS/src/reporter/clark-kent.pl +++ b/Open-ILS/src/reporter/clark-kent.pl @@ -60,7 +60,11 @@ INSERT INTO reporter.output ( stage3, state ) FROM reporter.stage3 WHERE runtime <= $1 AND ( ( recurrence = '0 seconds'::INTERVAL - AND id NOT IN ( SELECT stage3 FROM reporter.output ) ) + AND ( + id NOT IN ( SELECT stage3 FROM reporter.output ) + OR rerun IS TRUE + ) + ) OR ( recurrence > '0 seconds'::INTERVAL AND id NOT IN ( SELECT stage3 @@ -191,6 +195,11 @@ for my $r ( @reports ) { SET runtime = runtime + recurrence WHERE id = ? AND recurrence > '0 seconds'::INTERVAL; SQL + $dbh->do(<<' SQL',{}, $r->{stage3}->{id}); + UPDATE reporter.stage3 + SET rerun = FALSE + WHERE id = ? AND rerun = TRUE; + SQL $dbh->do(<<' SQL',{}, $r->{id}); UPDATE reporter.output SET state = 'complete', @@ -236,6 +245,8 @@ sub pivot_data { return unless (defined($settings->{pivot})); my @groups = (map { ($_ - 1) } @{ $settings->{groupby} }); + my @values = (0 .. (scalar(@{$settings->{columns}}) - 1)); + splice(@values,$_,1) for (reverse @groups); # remove pivot from group-by my $count = 0; @@ -243,6 +254,9 @@ sub pivot_data { while ($count < scalar(@{$settings->{groupby}})) { if (defined $pivot_groupby) { $settings->{groupby}->[$count] -= 1; + if ($settings->{groupby}->[$count] >= $values[0] + 1) { + $settings->{groupby}->[$count] -= 1; + } } elsif ($settings->{groupby}->[$count] == $settings->{pivot} + 1) { $pivot_groupby = $count; } @@ -251,7 +265,7 @@ sub pivot_data { # grab positions of non-group-bys - my @values = (0 .. (scalar(@{$settings->{columns}}) - 1)); + @values = (0 .. (scalar(@{$settings->{columns}}) - 1)); splice(@values,$_,1) for (reverse @groups); # we're only doing one "value" for now, so grab that and remove from headings @@ -263,10 +277,13 @@ sub pivot_data { my %p_header; for my $row (@$data) { - $p_header{ $$row[$settings->{pivot}] }++; - push @values, $$row[$val_col]; + $p_header{ $$row[$settings->{pivot}] } = [] unless exists($p_header{ $$row[$settings->{pivot}] }); + + push @{ $p_header{ $$row[$settings->{pivot}] } }, $$row[$val_col]; + splice(@$row,$_,1) for (@remove_me); } + push @{ $settings->{columns} }, sort keys %p_header; # remove from headings; @@ -279,12 +296,20 @@ sub pivot_data { $count = scalar(keys %p_header); my %seenit; my @new_data; - for my $row (@$data) { - my $fingerprint = join('',@$row[@groups]); - next if $seenit{$fingerprint}; - $seenit{$fingerprint}++; - push @$row, splice(@values,0,$count); - push @new_data, [@$row]; + { no warnings; + for my $row (@$data) { + + my $fingerprint = join('',@$row[@groups]); + next if $seenit{$fingerprint}; + + $seenit{$fingerprint}++; + + for my $h ( sort keys %p_header ) { + push @$row, shift(@{ $p_header{$h} }); + } + + push @new_data, [@$row]; + } } #replace old data with new @@ -364,9 +389,11 @@ sub build_html { CSS print $raw ""; - print $raw "'; - print $raw "' for (@{$r->{data}}); + { no warnings; + print $raw "'; + print $raw "' for (@{$r->{data}}); + } print $raw '
".join('',@{$r->{sql}->{columns}}).'
".join('',@$_).'
".join('',@{$r->{sql}->{columns}}).'
".join('',@$_ ).'
'; @@ -388,6 +415,7 @@ sub build_html { } } + print $index '



'; # Time for a bar chart if (grep {$_ eq 'bar'} @graphs) { my $pics = draw_bars($r, $p, $file); @@ -421,48 +449,67 @@ sub draw_pie { for my $vcol (@values) { next unless (defined $vcol); - my @pic_data; + my @pic_data = ([],[]); for my $row (@$data) { next if (!defined($$row[$vcol]) || $$row[$vcol] == 0); - push @{$pic_data[0]}, join(' -- ', @$row[@groups]); + push @{$pic_data[0]}, join(" -- ", @$row[@groups]); push @{$pic_data[1]}, $$row[$vcol]; } - my $pic = new GD::Graph::pie; + next unless (@{$pic_data[0]}); - $pic->set( - label => $p->{reportname}." -- ".$settings->{columns}->[$vcol], - start_angle => 180, - legend_placement => 'R', - logo => $logo, - logo_position => 'TL', - logo_resize => 0.5, - show_values => 1, - ); + my $size = 300; + my $split = int(scalar(@{$pic_data[0]}) / $size); + my $last = scalar(@{$pic_data[0]}) % $size; - my $format = $pic->export_format; + for my $sub_graph (0 .. $split) { + + if ($sub_graph == $split) { + $size = $last; + } - open(IMG, ">$file.pie.$vcol.$format"); - binmode IMG; + my @sub_data; + for my $set (@pic_data) { + push @sub_data, [ splice(@$set,0,$size) ]; + } - my $forgetit = 0; - try { - $pic->plot(\@pic_data) or die $pic->error; - print IMG $pic->gd->$format; - } otherwise { - my $e = shift; - warn "Couldn't draw $file.pie.$vcol.$format : $e"; - $forgetit = 1; - }; + my $pic = new GD::Graph::pie; + + $pic->set( + label => $settings->{columns}->[$vcol], + start_angle => 180, + legend_placement => 'R', + logo => $logo, + logo_position => 'TL', + logo_resize => 0.5, + show_values => 1, + ); + + my $format = $pic->export_format; + + open(IMG, ">$file.pie.$vcol.$sub_graph.$format"); + binmode IMG; + + my $forgetit = 0; + try { + $pic->plot(\@sub_data) or die $pic->error; + print IMG $pic->gd->$format; + } otherwise { + my $e = shift; + warn "Couldn't draw $file.pie.$vcol.$sub_graph.$format : $e"; + $forgetit = 1; + }; - close IMG; + close IMG; - next if ($forgetit); - push @pics, - { file => "pie.$vcol.$format", - name => $p->{reportname}." -- ".$settings->{columns}->[$vcol].' (Pie)', - }; + push @pics, + { file => "pie.$vcol.$sub_graph.$format", + name => $settings->{columns}->[$vcol].' (Pie)', + } unless ($forgetit); + + last if ($sub_graph == $split); + } } @@ -479,11 +526,11 @@ sub draw_bars { my $logo = $doc->findvalue('/reporter/setup/files/chart_logo'); my @groups = (map { ($_ - 1) } @{ $settings->{groupby} }); + my @values = (0 .. (scalar(@{$settings->{columns}}) - 1)); - delete @values[@groups]; - @values = grep {defined $_} @values; - + splice(@values,$_,1) for (reverse @groups); + my @pic_data; { no warnings; for my $row (@$data) { @@ -500,7 +547,6 @@ sub draw_bars { for my $vcol (@values) { next unless (defined $vcol); - push @leg, $settings->{columns}->[$vcol]; my $pos = 0; for my $row (@$data) { @@ -516,16 +562,31 @@ sub draw_bars { my $set_count = scalar(@pic_data) - 1; my @trim_cols = grep { $trim_candidates{$_} == $set_count } keys %trim_candidates; + my @new_data; + my @use_me; + my @no_use; + my $set_index = 0; for my $dataset (@pic_data) { - for my $col (reverse sort { $a <=> $b } @trim_cols) { - splice(@$dataset,$col,1); + splice(@$dataset,$_,1) for (sort { $b <=> $a } @trim_cols); + + if (grep { $_ } @$dataset) { + push @new_data, $dataset; + push @use_me, $set_index; + } else { + push @no_use, $set_index; } + $set_index++; + + } + + for my $col (@use_me) { + push @leg, $settings->{columns}->[$col + @groups - 1] if (map { 1 } grep { $col == $_ } @values); } - my $w = 100 + 10 * scalar(@{$pic_data[0]}); + my $w = 100 + 10 * scalar(@{$new_data[0]}); $w = 400 if ($w < 400); - my $h = 10 * (scalar(@pic_data) / 2); + my $h = 10 * (scalar(@new_data) / 2); $h = 0 if ($h < 0); @@ -554,7 +615,7 @@ sub draw_bars { binmode IMG; try { - $pic->plot(\@pic_data) or die $pic->error; + $pic->plot(\@new_data) or die $pic->error; print IMG $pic->gd->$format; } otherwise { my $e = shift; @@ -595,7 +656,7 @@ sub generate_query { } for my $t (keys %{$$p{output}}) { - if ($t ne $core && !grep { $t } @dims ) { + if ($t ne $core && !(grep { $t eq $_ } @dims) ) { push @dims, $t; } } @@ -724,7 +785,10 @@ sub generate_query { } my $t = table_by_id($core)->findvalue('tablename'); - my $from = " FROM $t AS \"$core\" RIGHT JOIN $d_select ON (". join(' AND ', @join).")"; + + my $from = " FROM $t AS \"$core\" "; + $from .= "RIGHT JOIN $d_select ON (". join(' AND ', @join).")" if ( @join ); + my $select = "SELECT ".join(',', @output). $from; diff --git a/Open-ILS/src/reporter/report_base.example.xml b/Open-ILS/src/reporter/report_base.example.xml index abdbdf99bb..77b4f38d77 100644 --- a/Open-ILS/src/reporter/report_base.example.xml +++ b/Open-ILS/src/reporter/report_base.example.xml @@ -4,7 +4,7 @@ /openils/var/web/reporter/ - /openils/var/web/opac/images/small_logo.png + Pg diff --git a/Open-ILS/src/reporter/tables.example.xml b/Open-ILS/src/reporter/tables.example.xml index 86bb88416b..a721354c75 100644 --- a/Open-ILS/src/reporter/tables.example.xml +++ b/Open-ILS/src/reporter/tables.example.xml @@ -82,6 +82,18 @@ id="copy_language" key="code" type="has_a"/> + + + + + Table mapping MARC three character codes to Item Form names + config.item_form_map + + + + + MARC Item Form Code + + + + Name associated with the MARC code + + +
+ + + + Table mapping MARC three character codes to Item Type names + config.item_type_map + + + + + MARC Item Type Code + + + + Name associated with the MARC code + + +
+ Base table for creating reports on silly words diff --git a/Open-ILS/src/reporter/templates/stage3.ttk b/Open-ILS/src/reporter/templates/stage3.ttk index 955fe4fcc3..eeb8c0c33a 100644 --- a/Open-ILS/src/reporter/templates/stage3.ttk +++ b/Open-ILS/src/reporter/templates/stage3.ttk @@ -8,10 +8,22 @@ INCLUDE logout.ttk; templates = DBI.tie('reporter.stage2', 'id') reports = DBI.tie('reporter.stage3', 'id') +output = DBI.tie('reporter.output', 'id') rpt = {}; tmpl = {}; +IF CGI.param('output'); + oid = CGI.param('output'); + out = output.$oid; + + rid = out.stage3; + rpt = reports.$rid; + + tid = rpt.stage2; + tmpl = templates.$tid; +END; + IF CGI.param('id'); rid = CGI.param('id'); rpt = reports.$rid; @@ -27,6 +39,12 @@ WRAPPER html/html; PROCESS schedule_stage3; ELSIF CGI.param('action') == 'Save'; PROCESS save_stage3; + ELSIF CGI.param('action') == 'delete_stage3'; + PROCESS delete_stage3; + ELSIF CGI.param('action') == 'delete_output'; + PROCESS delete_output; + ELSIF CGI.param('action') == 'rerun'; + PROCESS rerun; ELSIF CGI.param('action') == 'Run Now'; PROCESS save_stage3; ELSIF CGI.param('id'); @@ -179,8 +197,22 @@ BLOCK save_stage3; END; END; +BLOCK delete_stage3; + q = DBI.prepare( 'DELETE FROM reporter.stage3 WHERE id = ?' ); + CALL q.execute(rpt.id); +END; +BLOCK delete_output; + q = DBI.prepare( 'DELETE FROM reporter.output WHERE id = ?' ); + CALL q.execute(out.id); + INCLUDE view_stage3; +END; +BLOCK rerun; + q = DBI.prepare( 'UPDATE reporter.stage3 SET rerun = TRUE WHERE id = ?' ); + CALL q.execute(rpt.id); + INCLUDE view_stage3; +END; BLOCK view_stage3; logme(rpt.params); @@ -210,6 +242,17 @@ BLOCK view_stage3; INCLUDE html/cell content="Recurrence Interval" style="border:solid gray 1px;"; INCLUDE html/cell content=rpt.recurrence style="border:solid gray 1px;"; END; + WRAPPER html/row; + INCLUDE html/cell content="Actions" style="border:solid gray 1px;"; + WRAPPER html/cell style="border:solid gray 1px;"; + INCLUDE anchor content='Refresh' href=CGI.url('-path', 1) _ '?id=' _ rpt.id; + '   '; + INCLUDE anchor content='Run again' href=CGI.url('-path', 1) _ '?action=rerun&id=' _ rpt.id; + '   '; + INCLUDE anchor content='Delete' href=CGI.url('-path', 1) _ '?action=delete_stage3&id=' _ rpt.id; + '   '; + END; + END; END; END; @@ -274,6 +317,15 @@ BLOCK view_stage3; INCLUDE html/cell content=run.error_time style="border:solid gray 1px;"; END; END; + IF run.complete_time || run.error_time; + WRAPPER html/row; + INCLUDE html/cell content="Actions" style="border:solid gray 1px;"; + WRAPPER html/cell style="border:solid gray 1px;"; + INCLUDE anchor content='Delete' href=CGI.url('-path', 1) _ '?action=delete_output&output=' _ run.id; + '   '; + END; + END; + END; END; '

'; END; diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 45b740fa98..e56a4c94bb 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -910,4 +910,42 @@ zul Zulu zun Zuni \. +CREATE TABLE config.item_form_map ( + code TEXT PRIMARY KEY, + value TEXT NOT NULL +); + +COPY config.item_form_map FROM STDIN; +a Microfilm +b Microfiche +c Microopaque +d Large print +f Braille +r Regular print reproduction +s Electronic +\. + +CREATE TABLE config.item_type_map ( + code TEXT PRIMARY KEY, + value TEXT NOT NULL +); + +COPY config.item_type_map FROM STDIN; +a Language material +t Manuscript language material +g Projected medium +k Two-dimensional nonprojectable graphic +r Three-dimensional artifact or naturally occurring object +o Kit +p Mixed materials +e Cartographic material +f Manuscript cartographic material +c Notated music +d Manuscript notated music +i Nonmusical sound recording +j Musical sound recording +m Computer file +\. + COMMIT; + -- 2.43.2