10 use DateTime::Format::ISO8601;
13 use OpenILS::WWW::Reporter::transforms;
15 use Spreadsheet::WriteExcel::Big;
16 use OpenSRF::EX qw/:try/;
17 use OpenSRF::Utils qw/:daemon/;
18 use OpenSRF::Utils::Logger qw/:level/;
21 use GD::Graph::bars3d;
22 use GD::Graph::lines3d;
27 my ($base_xml, $count, $daemon) = ('/openils/conf/reporter.xml', 1);
30 "file=s" => \$base_xml,
32 "concurrency=i" => \$count,
35 my $parser = XML::LibXML->new;
36 $parser->expand_xinclude(1);
38 my $doc = $parser->parse_file($base_xml);
40 my $db_driver = $doc->findvalue('/reporter/setup/database/driver');
41 my $db_host = $doc->findvalue('/reporter/setup/database/host');
42 my $db_port = $doc->findvalue('/reporter/setup/database/port') || '5432';
43 my $db_name = $doc->findvalue('/reporter/setup/database/name');
44 my $db_user = $doc->findvalue('/reporter/setup/database/user');
45 my $db_pw = $doc->findvalue('/reporter/setup/database/password');
47 my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host . ';port=' . $db_port;
49 my ($dbh,$running,$sth,@reports,$run, $current_time);
51 daemonize("Clark Kent, waiting for trouble") if ($daemon);
55 $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1});
57 $current_time = DateTime->from_epoch( epoch => time() )->strftime('%FT%T%z');
58 # Move new reports into the run queue
59 $dbh->do(<<'SQL', {}, $current_time);
60 INSERT INTO reporter.output ( stage3, state )
65 AND ( ( recurrence = '0 seconds'::INTERVAL
67 id NOT IN ( SELECT stage3 FROM reporter.output )
71 OR ( recurrence > '0 seconds'::INTERVAL
75 WHERE state <> 'complete')
81 # make sure we're not already running $count reports
82 ($running) = $dbh->selectrow_array(<<SQL);
85 WHERE state = 'running';
88 if ($count <= $running) {
92 POSIX::waitpid( -1, POSIX::WNOHANG );
96 print "Already running maximum ($running) concurrent reports\n";
100 # if we have some open slots then generate the sql
101 $run = $count - $running;
103 $sth = $dbh->prepare(<<SQL);
114 while (my $r = $sth->fetchrow_hashref) {
115 my $s3 = $dbh->selectrow_hashref(<<" SQL", {}, $r->{stage3});
116 SELECT * FROM reporter.stage3 WHERE id = ?;
119 my $s2 = $dbh->selectrow_hashref(<<" SQL", {}, $s3->{stage2});
120 SELECT * FROM reporter.stage2 WHERE id = ?;
126 generate_query( $r );
134 # Now we spaun the report runners
136 for my $r ( @reports ) {
137 next if (safe_fork());
139 # This is the child (runner) process;
140 my $p = JSON->JSON2perl( $r->{stage3}->{params} );
141 daemonize("Clark Kent reporting: $p->{reportname}");
143 $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1});
146 $dbh->do(<<' SQL',{}, $r->{sql}->{'select'}, $$, $r->{id});
147 UPDATE reporter.output
148 SET state = 'running',
155 my ($runtime) = $dbh->selectrow_array("SELECT run_time FROM reporter.output WHERE id = ?",{},$r->{id});
156 $r->{run_time} = $runtime;
158 $sth = $dbh->prepare($r->{sql}->{'select'});
160 $sth->execute(@{ $r->{sql}->{'bind'} });
161 $r->{data} = $sth->fetchall_arrayref;
165 my $base = $doc->findvalue('/reporter/setup/files/output_base');
166 my $s1 = $r->{stage3}->{stage2}->{stage1};
167 my $s2 = $r->{stage3}->{stage2}->{id};
168 my $s3 = $r->{stage3}->{id};
169 my $output = $r->{id};
173 mkdir("$base/$s1/$s2");
174 mkdir("$base/$s1/$s2/$s3");
175 mkdir("$base/$s1/$s2/$s3/$output");
178 if (ref $p->{output_format}) {
179 @formats = @{ $p->{output_format} };
181 @formats = ( $p->{output_format} );
184 if ( grep { $_ eq 'csv' } @formats ) {
185 build_csv("$base/$s1/$s2/$s3/$output/report-data.csv", $r);
188 if ( grep { $_ eq 'excel' } @formats ) {
189 build_excel("$base/$s1/$s2/$s3/$output/report-data.xls", $r);
192 if ( grep { $_ eq 'html' } @formats ) {
193 mkdir("$base/$s1/$s2/$s3/$output/html");
194 build_html("$base/$s1/$s2/$s3/$output/report-data.html", $r);
199 #$dbh->do(<<' SQL',{}, $r->{run_time}, $r->{stage3}->{id});
200 # UPDATE reporter.stage3
201 # SET runtime = CAST(? AS TIMESTAMP WITH TIME ZONE) + recurrence
202 # WHERE id = ? AND recurrence > '0 seconds'::INTERVAL;
204 $dbh->do(<<' SQL',{}, $r->{stage3}->{id});
205 UPDATE reporter.stage3
206 SET runtime = runtime + recurrence
207 WHERE id = ? AND recurrence > '0 seconds'::INTERVAL;
209 $dbh->do(<<' SQL',{}, $r->{stage3}->{id});
210 UPDATE reporter.stage3
212 WHERE id = ? AND rerun = TRUE;
214 $dbh->do(<<' SQL',{}, $r->{id});
215 UPDATE reporter.output
216 SET state = 'complete',
217 complete_time = 'now'
226 $dbh->do(<<' SQL',{}, $e, $r->{id});
227 UPDATE reporter.output
238 exit; # leave the child
243 POSIX::waitpid( -1, POSIX::WNOHANG );
248 #-------------------------------------------------------------------
252 my $p = JSON->JSON2perl( $r->{stage3}->{params} );
253 my $settings = $r->{sql};
254 my $data = $r->{data};
256 return unless (defined($settings->{pivot}));
258 my @groups = (map { ($_ - 1) } @{ $settings->{groupby} });
259 my @values = (0 .. (scalar(@{$settings->{columns}}) - 1));
260 splice(@values,$_,1) for (reverse @groups);
262 # remove pivot from group-by
265 while ($count < scalar(@{$settings->{groupby}})) {
266 if (defined $pivot_groupby) {
267 $settings->{groupby}->[$count] -= 1;
268 if ($settings->{groupby}->[$count] >= $values[0] + 1) {
269 $settings->{groupby}->[$count] -= 1;
271 } elsif ($settings->{groupby}->[$count] == $settings->{pivot} + 1) {
272 $pivot_groupby = $count;
278 # grab positions of non-group-bys
279 @values = (0 .. (scalar(@{$settings->{columns}}) - 1));
280 splice(@values,$_,1) for (reverse @groups);
282 # we're only doing one "value" for now, so grab that and remove from headings
283 my ($val_col) = @values;
287 ($val_col, $settings->{groupby}->[$pivot_groupby] - 1);
289 # get the groups-to-be
290 my @temp_groupby = @groups;
291 splice(@temp_groupby, $pivot_groupby, 1);
293 @groups = map { ($_ - 1) } @{ $settings->{groupby} };
296 for my $row (@$data) {
297 $p_header{ $$row[$settings->{pivot}] } = [] unless exists($p_header{ $$row[$settings->{pivot}] });
299 # add the header from this row's pivot
300 push @{ $p_header{ $$row[$settings->{pivot}] } },
301 { val => $$row[$val_col], fp => join('', map { defined($_) ? $_ : '' } @$row[@temp_groupby]) };
303 splice(@$row,$_,1) for (@remove_me);
306 push @{ $settings->{columns} }, sort keys %p_header;
308 # remove from headings;
309 splice(@{$settings->{columns}},$_,1) for (@remove_me);
311 # remove pivot from groupby
312 splice(@{$settings->{groupby}}, $pivot_groupby, 1);
313 @groups = (map { ($_ - 1) } @{ $settings->{groupby} });
318 for my $row (@$data) {
320 my $fingerprint = join('',@$row[@groups]);
321 next if $seenit{$fingerprint};
323 $seenit{$fingerprint}++;
325 for my $h ( sort keys %p_header ) {
328 for my $blob (@{ $p_header{$h} }) {
329 $fingerprint = join('', map { defined($_) ? $_ : '' } @$row[@groups]);
331 if ($blob->{fp} eq $fingerprint ) {
332 push @$row, $blob->{val};
334 splice(@{ $p_header{$h} }, $bcount, 1);
339 push @$row, 0 if (!$found);
342 push @new_data, [@$row];
346 @new_data = sort { data_sorter($a,$b,\@groups) } @new_data;
348 #replace old data with new
349 $r->{data} = \@new_data;
358 my $sort_cols = shift;
360 for my $col (@$sort_cols) {
361 return -1 if (!defined($$_a[$col]));
362 return 1 if (!defined($$_b[$col]));
364 return -1 if ($$_a[$col] lt $$_b[$col]);
365 return 1 if ($$_a[$col] gt $$_b[$col]);
374 my $csv = Text::CSV_XS->new({ always_quote => 1, eol => "\015\012" });
376 return unless ($csv);
378 my $f = new FileHandle (">$file");
380 $csv->print($f, $r->{sql}->{columns});
381 $csv->print($f, $_) for (@{$r->{data}});
388 my $p = JSON->JSON2perl( $r->{stage3}->{params} );
390 my $xls = Spreadsheet::WriteExcel::Big->new($file);
392 my $sheetname = substr($p->{reportname},1,31);
393 $sheetname =~ s/\W/_/gos;
395 my $sheet = $xls->add_worksheet($sheetname);
397 $sheet->write_row('A1', $r->{sql}->{columns});
399 $sheet->write_col('A2', $r->{data});
407 my $p = JSON->JSON2perl( $r->{stage3}->{params} );
409 my $index = new FileHandle (">$file");
410 my $raw = new FileHandle (">$file.raw.html");
413 print $index <<" HEADER";
416 <title>$$p{reportname}</title>
418 table { border-collapse: collapse; }
419 th { background-color: lightgray; }
420 td,th { border: solid black 1px; }
421 * { font-family: sans-serif; font-size: 10px; }
425 <h2><u>$$p{reportname}</u></h2>
429 # add a link to the raw output html
430 print $index "<a href='report-data.html.raw.html'>Raw output data</a><br/><br/><br/><br/>";
432 # create the raw output html file
433 print $raw "<html><head><title>$$p{reportname}</title>";
437 table { border-collapse: collapse; }
438 th { background-color: lightgray; }
439 td,th { border: solid black 1px; }
440 * { font-family: sans-serif; font-size: 10px; }
444 print $raw "</head><body><table>";
447 print $raw "<tr><th>".join('</th><th>',@{$r->{sql}->{columns}}).'</th></tr>';
448 print $raw "<tr><td>".join('</td><td>',@$_ ).'</td></tr>' for (@{$r->{data}});
451 print $raw '</table></body></html>';
455 # get the graph types
457 if (ref $$p{html_graph_type}) {
458 @graphs = @{ $$p{html_graph_type} };
460 @graphs = ( $$p{html_graph_type} );
464 # Time for a pie chart
465 if (grep {$_ eq 'pie'} @graphs) {
466 my $pics = draw_pie($r, $p, $file);
467 for my $pic (@$pics) {
468 print $index "<img src='report-data.html.$pic->{file}' alt='$pic->{name}'/><br/><br/><br/><br/>";
472 print $index '<br/><br/><br/><br/>';
473 # Time for a bar chart
474 if (grep {$_ eq 'bar'} @graphs) {
475 my $pics = draw_bars($r, $p, $file);
476 for my $pic (@$pics) {
477 print $index "<img src='report-data.html.$pic->{file}' alt='$pic->{name}'/><br/><br/><br/><br/>";
481 print $index '<br/><br/><br/><br/>';
482 # Time for a bar chart
483 if (grep {$_ eq 'line'} @graphs) {
484 my $pics = draw_lines($r, $p, $file);
485 for my $pic (@$pics) {
486 print $index "<img src='report-data.html.$pic->{file}' alt='$pic->{name}'/><br/><br/><br/><br/>";
492 print $index '</body></html>';
501 my $data = $r->{data};
502 my $settings = $r->{sql};
504 my @groups = (map { ($_ - 1) } @{ $settings->{groupby} });
506 my @values = (0 .. (scalar(@{$settings->{columns}}) - 1));
507 delete @values[@groups];
509 my $logo = $doc->findvalue('/reporter/setup/files/chart_logo');
512 for my $vcol (@values) {
513 next unless (defined $vcol);
515 my @pic_data = ([],[]);
516 for my $row (@$data) {
517 next if (!defined($$row[$vcol]) || $$row[$vcol] == 0);
518 my $val = $$row[$vcol];
519 push @{$pic_data[0]}, join(" -- ", @$row[@groups])." ($val)";
520 push @{$pic_data[1]}, $val;
523 next unless (@{$pic_data[0]});
526 my $split = int(scalar(@{$pic_data[0]}) / $size);
527 my $last = scalar(@{$pic_data[0]}) % $size;
529 for my $sub_graph (0 .. $split) {
531 if ($sub_graph == $split) {
536 for my $set (@pic_data) {
537 push @sub_data, [ splice(@$set,0,$size) ];
540 my $pic = new GD::Graph::pie;
543 label => $settings->{columns}->[$vcol],
545 legend_placement => 'R',
547 logo_position => 'TL',
552 my $format = $pic->export_format;
554 open(IMG, ">$file.pie.$vcol.$sub_graph.$format");
559 $pic->plot(\@sub_data) or die $pic->error;
560 print IMG $pic->gd->$format;
563 warn "Couldn't draw $file.pie.$vcol.$sub_graph.$format : $e";
571 { file => "pie.$vcol.$sub_graph.$format",
572 name => $settings->{columns}->[$vcol].' (Pie)',
573 } unless ($forgetit);
575 last if ($sub_graph == $split);
587 my $data = $r->{data};
588 my $settings = $r->{sql};
590 my $logo = $doc->findvalue('/reporter/setup/files/chart_logo');
592 my @groups = (map { ($_ - 1) } @{ $settings->{groupby} });
595 my @values = (0 .. (scalar(@{$settings->{columns}}) - 1));
596 splice(@values,$_,1) for (reverse @groups);
600 for my $row (@$data) {
601 push @{$pic_data[0]}, join(' -- ', @$row[@groups]);
611 for my $vcol (@values) {
612 next unless (defined $vcol);
616 for my $row (@$data) {
617 my $val = $$row[$vcol] ? $$row[$vcol] : 0;
618 push @{$pic_data[$set]}, $val;
619 $max_y = $val if ($val > $max_y);
620 $trim_candidates{$pos}++ if ($val == 0);
626 my $set_count = scalar(@pic_data) - 1;
627 my @trim_cols = grep { $trim_candidates{$_} == $set_count } keys %trim_candidates;
633 for my $dataset (@pic_data) {
634 splice(@$dataset,$_,1) for (sort { $b <=> $a } @trim_cols);
636 if (grep { $_ } @$dataset) {
637 push @new_data, $dataset;
638 push @use_me, $set_index;
640 push @no_use, $set_index;
646 return [] unless ($new_data[0] && @{$new_data[0]});
648 for my $col (@use_me) {
649 push @leg, $settings->{columns}->[$col + @groups - 1] if (map { 1 } grep { $col == $_ } @values);
652 my $w = 100 + 10 * scalar(@{$new_data[0]});
653 $w = 400 if ($w < 400);
655 my $h = 10 * (scalar(@new_data) / 2);
659 my $pic = new GD::Graph::bars3d ($w + 250, $h + 500);
662 title => $p->{reportname},
663 x_labels_vertical => 1,
667 y_max_value => $max_y,
668 legend_placement => 'TR',
671 logo_position => 'R',
676 $pic->set_legend(@leg);
678 my $format = $pic->export_format;
680 open(IMG, ">$file.bar.$format");
684 $pic->plot(\@new_data) or die $pic->error;
685 print IMG $pic->gd->$format;
688 warn "Couldn't draw $file.bar.$format : $e";
693 return [{ file => "bar.$format",
694 name => $p->{reportname}.' (Bar)',
703 my $data = $r->{data};
704 my $settings = $r->{sql};
706 my $logo = $doc->findvalue('/reporter/setup/files/chart_logo');
708 my @groups = (map { ($_ - 1) } @{ $settings->{groupby} });
711 my @values = (0 .. (scalar(@{$settings->{columns}}) - 1));
712 splice(@values,$_,1) for (reverse @groups);
716 for my $row (@$data) {
717 push @{$pic_data[0]}, join(' -- ', @$row[@groups]);
725 for my $vcol (@values) {
726 next unless (defined $vcol);
729 for my $row (@$data) {
730 my $val = $$row[$vcol] ? $$row[$vcol] : 0;
731 push @{$pic_data[$set]}, $val;
732 $max_y = $val if ($val > $max_y);
737 my $set_count = scalar(@pic_data) - 1;
743 for my $dataset (@pic_data) {
745 if (grep { $_ } @$dataset) {
746 push @new_data, $dataset;
747 push @use_me, $set_index;
749 push @no_use, $set_index;
755 for my $col (@use_me) {
756 push @leg, $settings->{columns}->[$col + @groups - 1] if (map { 1 } grep { $col == $_ } @values);
759 my $w = 100 + 10 * scalar(@{$new_data[0]});
760 $w = 400 if ($w < 400);
762 my $h = 10 * (scalar(@new_data) / 2);
766 my $pic = new GD::Graph::lines3d ($w + 250, $h + 500);
769 title => $p->{reportname},
770 x_labels_vertical => 1,
773 y_max_value => $max_y,
774 legend_placement => 'TR',
777 logo_position => 'R',
782 $pic->set_legend(@leg);
784 my $format = $pic->export_format;
786 open(IMG, ">$file.line.$format");
790 $pic->plot(\@new_data) or die $pic->error;
791 print IMG $pic->gd->$format;
794 warn "Couldn't draw $file.line.$format : $e";
799 return [{ file => "line.$format",
800 name => $p->{reportname}.' (Bar)',
807 my ($node) = $doc->findnodes("//*[\@id='$id']");
808 if ($node && $node->findvalue('@table')) {
809 ($node) = $doc->findnodes("//*[\@id='".$node->getAttribute('table')."']");
817 my $p = JSON->JSON2perl( $r->{stage3}->{params} );
821 my $core = $r->{stage3}->{stage2}->{stage1};
824 for my $t (keys %{$$p{filter}}) {
830 for my $t (keys %{$$p{output}}) {
831 if ($t ne $core && !(grep { $t eq $_ } @dims) ) {
839 my $t = table_by_id($d);
840 my $t_name = $t->findvalue('tablename');
841 push @dim_from, "$t_name AS \"$d\""
842 unless ( grep {$_ eq "$t_name AS \"$d\""} @dim_from );
844 my $k = $doc->findvalue("//*[\@id='$d']/\@key");
845 push @dim_select, "\"$d\".\"$k\" AS \"${d}_${k}\""
846 unless ( grep {$_ eq "\"$d\".\"$k\" AS \"${d}_${k}\""} @dim_select );
848 for my $c ( keys %{$$p{output}{$d}} ) {
849 push @dim_select, "\"$d\".\"$c\" AS \"${d}_${c}\""
850 unless ( grep {$_ eq "\"$d\".\"$c\" AS \"${d}_${c}\""} @dim_select );
853 for my $c ( keys %{$$p{filter}{$d}} ) {
854 next if (exists $$p{output}{$d}{$c});
855 push @dim_select, "\"$d\".\"$c\" AS \"${d}_${c}\""
856 unless ( grep {$_ eq "\"$d\".\"$c\" AS \"${d}_${c}\""} @dim_select );
861 '(SELECT ' . join(',', @dim_select) .
862 ' FROM ' . join(',', @dim_from) . ') AS dims';
865 if (ref $$p{output_order}) {
866 @opord = @{ $$p{output_order} };
868 @opord = ( $$p{output_order} );
870 my @output_order = map { { (split ':')[1] => (split ':')[2] } } @opord;
871 my @p_col = split(':',$p->{pivot_col}) if $p->{pivot_col};
877 my @dim_col_names = ();
881 for my $pair (@output_order) {
882 my ($t_name) = keys %$pair;
885 $t_name = "dims" if ($t ne $core);
887 my $t_node = table_by_id($t);
889 for my $c ( values %$pair ) {
890 my $label = $t_node->findvalue("fields/field[\@name='$c']/label");
893 $full_col = "${t}_${c}" if ($t ne $t_name);
894 $full_col = "\"$t_name\".\"$full_col\"";
896 if (my $xform_type = $$p{xform}{type}{$t}{$c}) {
897 my $xform = $$OpenILS::WWW::Reporter::dtype_xforms{$xform_type};
898 if ($xform->{group}) {
901 $label = "$$xform{label} -- $label";
903 my $tmp = $xform->{'select'};
904 $tmp =~ s/\?COLNAME\?/$full_col/gs;
905 $tmp =~ s/\?PARAM\?/$$p{xform}{param}{$t}{$c}/gs;
911 push @output, "$full_col AS \"$label\"";
912 push @columns, $label;
913 $pivot = scalar(@columns) - 1 if (@p_col && $t eq $p_col[1] && $c eq $p_col[2]);
917 if ($t ne $t_name && (!@join_base || !grep{$t eq $_}@join_base)) {
918 my $k = $doc->findvalue("//*[\@id='$t']/\@key");
919 my $f = $doc->findvalue("//*[\@id='$t']/\@field");
920 push @join, "dims.\"${t}_${k}\" = \"$core\".\"$f\"";
927 for my $t ( keys %{$$p{filter}} ) {
929 $t_name = "dims" if ($t ne $core);
931 my $t_node = table_by_id($t);
933 for my $c ( keys %{$$p{filter}{$t}} ) {
934 my $label = $t_node->findvalue("fields/field[\@name='$c']/label");
937 $full_col = "${t}_${c}" if ($t ne $t_name);
938 $full_col = "\"$t_name\".\"$full_col\"";
940 my ($fam) = keys %{ $$p{filter}{$t}{$c} };
941 my ($w) = keys %{ $$p{filter}{$t}{$c}{$fam} };
942 my $val = $$p{filter}{$t}{$c}{$fam}{$w};
944 my $filter_code_xpath = "/reporter/widgets/widget-family[\@name='$fam']/widget[\@name='$w']/filter-code[\@type='perl']";
945 if (my $widget_code = $doc->findvalue($filter_code_xpath)) { # widget supplys it's own filter code
946 my ($where_clause, $bind_list) = ('',[]);
950 die "$@\n\n$widget_code" if ($@);
952 push @where, $where_clause;
953 push @bind, @$bind_list;
956 push @where, "$full_col IN (".join(",",map {'?'}@$val).")";
959 push @where, "$full_col = ?";
964 if ($t ne $t_name && (!@join_base || !grep{$t eq $_}@join_base)) {
965 my $k = $doc->findvalue("//*[\@id='$t']/\@key");
966 my $f = $doc->findvalue("//*[\@id='$t']/\@field");
967 push @join, "dims.\"${t}_${k}\" = \"$core\".\"$f\"";
972 my $t = table_by_id($core)->findvalue('tablename');
974 my $from = " FROM $t AS \"$core\" ";
975 $from .= "RIGHT JOIN $d_select ON (". join(' AND ', @join).")" if ( @join );
978 "SELECT ".join(',', @output). $from;
980 $select .= ' WHERE '.join(' AND ', @where) if (@where);
981 $select .= ' GROUP BY '.join(',',@groupby) if (@groupby);
983 $r->{sql}->{'pivot'} = $pivot;
984 $r->{sql}->{'select'} = $select;
985 $r->{sql}->{'bind'} = \@bind;
986 $r->{sql}->{columns} = \@columns;
987 $r->{sql}->{groupby} = \@groupby;