10 use DateTime::Format::ISO8601;
14 use Spreadsheet::WriteExcel::Big;
15 use OpenSRF::EX qw/:try/;
16 use OpenSRF::Utils qw/:daemon/;
17 #use OpenSRF::Utils::Logger qw/:level/;
19 use OpenSRF::AppSession;
20 use OpenSRF::Utils::SettingsClient;
21 use OpenILS::Reporter::SQLBuilder;
24 use GD::Graph::bars3d;
25 use GD::Graph::lines3d;
31 my ($count, $config, $sleep_interval, $lockfile, $daemon) = (1, '/openils/conf/bootstrap.conf', 10, '/tmp/reporter-LOCK');
35 "sleep" => \$sleep_interval,
36 "concurrency=i" => \$count,
37 "boostrap=s" => \$config,
38 "lockfile=s" => \$lockfile,
42 die "I seem to be running already. If not remove $lockfile, try again\n";
45 OpenSRF::System->bootstrap_client( config_file => $config );
47 # XXX Get this stuff from the settings server
48 my $sc = OpenSRF::Utils::SettingsClient->new;
49 my $db_driver = $sc->config_value( reporter => setup => database => 'driver' );
50 my $db_host = $sc->config_value( reporter => setup => database => 'host' );
51 my $db_port = $sc->config_value( reporter => setup => database => 'port' );
52 my $db_name = $sc->config_value( reporter => setup => database => 'name' );
53 my $db_user = $sc->config_value( reporter => setup => database => 'user' );
54 my $db_pw = $sc->config_value( reporter => setup => database => 'password' );
56 my $output_base = $sc->config_value( reporter => setup => files => 'output_base' );
58 my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host . ';port=' . $db_port;
60 my ($dbh,$running,$sth,@reports,$run, $current_time);
63 open(F, ">$lockfile");
66 daemonize("Clark Kent, waiting for trouble");
72 $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1});
74 $current_time = DateTime->from_epoch( epoch => time() )->strftime('%FT%T%z');
76 # make sure we're not already running $count reports
77 ($running) = $dbh->selectrow_array(<<SQL);
79 FROM reporter.schedule
80 WHERE start_time IS NOT NULL AND complete_time IS NULL;
83 if ($count <= $running) {
87 POSIX::waitpid( -1, POSIX::WNOHANG );
88 sleep $sleep_interval;
91 print "Already running maximum ($running) concurrent reports\n";
95 # if we have some open slots then generate the sql
96 $run = $count - $running;
98 $sth = $dbh->prepare(<<SQL);
100 FROM reporter.schedule
101 WHERE start_time IS NULL AND run_time < NOW()
109 while (my $r = $sth->fetchrow_hashref) {
110 my $s3 = $dbh->selectrow_hashref(<<" SQL", {}, $r->{report});
111 SELECT * FROM reporter.report WHERE id = ?;
114 my $s2 = $dbh->selectrow_hashref(<<" SQL", {}, $s3->{template});
115 SELECT * FROM reporter.template WHERE id = ?;
118 $s3->{template} = $s2;
121 my $b = OpenILS::Reporter::SQLBuilder->new;
122 $b->register_params( JSON->JSON2perl( $r->{report}->{data} ) );
124 $r->{resultset} = $b->parse_report( JSON->JSON2perl( $r->{report}->{template}->{data} ) );
125 $r->{resultset}->relative_time($r->{run_time});
133 # Now we spaun the report runners
135 for my $r ( @reports ) {
136 next if (safe_fork());
138 # This is the child (runner) process;
139 daemonize("Clark Kent reporting: $r->{report}->{name}");
141 $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1});
144 $dbh->do(<<' SQL',{}, $r->{id});
145 UPDATE reporter.schedule
146 SET start_time = now()
150 $sth = $dbh->prepare($r->{resultset}->toSQL);
153 $r->{data} = $sth->fetchall_arrayref;
155 $r->{column_labels} = [$r->{resultset}->column_label_list];
157 if ($r->{resultset}->pivot_data && $r->{resultset}->pivot_label) {
158 my @labels = $r->{resultset}->column_label_list;
159 my $newdata = pivot_data(
160 { columns => $r->{column_labels}, data => $r->{data}},
161 $r->{resultset}->pivot_label,
162 $r->{resultset}->pivot_data,
163 $r->{resultset}->pivot_default
166 $r->{column_labels} = $newdata->{columns};
167 $r->{data} = $newdata->{data};
170 my $s2 = $r->{report}->{template}->{id};
171 my $s3 = $r->{report}->{id};
172 my $output = $r->{id};
175 mkdir("$output_base/$s2");
176 mkdir("$output_base/$s2/$s3");
177 mkdir("$output_base/$s2/$s3/$output");
179 my $output_dir = "$output_base/$s2/$s3/$output";
181 if ( $r->{csv_format} ) {
182 build_csv("$output_dir/report-data.csv", $r);
185 if ( $r->{excel_format} ) {
186 build_excel("$output_dir/report-data.xls", $r);
189 if ( $r->{html_format} ) {
190 mkdir("$output_dir/html");
191 build_html("$output_dir/report-data.html", $r);
196 if ($r->{report}->{recur} ) {
198 INSERT INTO reporter.schedule (
210 VALUES ( ?, ?, ?, ?::TIMESTAMPTZ + ?, ?, ?, ?, ?, ?, ?, ? );
220 $r->{report}->{recurance},
231 $dbh->do(<<' SQL',{}, $r->{id});
232 UPDATE reporter.schedule
233 SET complete_time = now()
243 $dbh->do(<<' SQL',{}, $e, $r->{id});
244 UPDATE reporter.schedule
246 complete_time = now(),
254 exit; # leave the child
259 POSIX::waitpid( -1, POSIX::WNOHANG );
260 sleep $sleep_interval;
264 #-------------------------------------------------------------------
270 my $csv = Text::CSV_XS->new({ always_quote => 1, eol => "\015\012" });
272 return unless ($csv);
274 my $f = new FileHandle (">$file");
276 $csv->print($f, $r->{column_labels});
277 $csv->print($f, $_) for (@{$r->{data}});
284 my $xls = Spreadsheet::WriteExcel::Big->new($file);
286 my $sheetname = substr($r->{report}->{name},1,31);
287 $sheetname =~ s/\W/_/gos;
289 my $sheet = $xls->add_worksheet($sheetname);
291 $sheet->write_row('A1', $r->{column_labels});
293 $sheet->write_col('A2', $r->{data});
302 my $index = new FileHandle (">$file");
303 my $raw = new FileHandle (">$file.raw.html");
306 print $index <<" HEADER";
309 <title>$$r{report}{name}</title>
311 table { border-collapse: collapse; }
312 th { background-color: lightgray; }
313 td,th { border: solid black 1px; }
314 * { font-family: sans-serif; font-size: 10px; }
319 <h2><u>$$r{report}{name}</u></h2>
320 $$r{report}{description}<br/><br/><br/>
324 # add a link to the raw output html
325 print $index "<a href='report-data.html.raw.html'>Tabular Output</a>";
327 # add a link to the CSV output
328 print $index " -- <a href='report-data.csv'>CSV Output</a>" if ($r->{csv_format});
330 # add a link to the CSV output
331 print $index " -- <a href='report-data.xls'>Excel Output</a>" if ($r->{excel_format});
333 print $index "<br/><br/><br/><br/></center>";
335 # create the raw output html file
336 print $raw "<html><head><title>$$r{report}{name}</title>";
340 table { border-collapse: collapse; }
341 th { background-color: lightgray; }
342 td,th { border: solid black 1px; }
343 * { font-family: sans-serif; font-size: 10px; }
347 print $raw "</head><body><table>";
350 print $raw "<tr><th>".join('</th><th>',@{$r->{column_labels}}).'</th></tr>';
351 print $raw "<tr><td>".join('</td><td>',@$_ ).'</td></tr>' for (@{$r->{data}});
354 print $raw '</table></body></html>';
358 # Time for a pie chart
359 if ($r->{chart_pie}) {
360 my $pics = draw_pie($r, $file);
361 for my $pic (@$pics) {
362 print $index "<img src='report-data.html.$pic->{file}' alt='$pic->{name}'/><br/><br/><br/><br/>";
366 print $index '<br/><br/><br/><br/>';
367 # Time for a bar chart
368 if ($r->{chart_bar}) {
369 my $pics = draw_bars($r, $file);
370 for my $pic (@$pics) {
371 print $index "<img src='report-data.html.$pic->{file}' alt='$pic->{name}'/><br/><br/><br/><br/>";
375 print $index '<br/><br/><br/><br/>';
376 # Time for a bar chart
377 if ($r->{chart_line}) {
378 my $pics = draw_lines($r, $file);
379 for my $pic (@$pics) {
380 print $index "<img src='report-data.html.$pic->{file}' alt='$pic->{name}'/><br/><br/><br/><br/>";
385 print $index '</body></html>';
394 my $data = $r->{data};
396 my @groups = $r->{resultset}->group_by_list(0);
398 my @values = (0 .. (scalar(@{$r->{column_labels}}) - 1));
399 delete @values[@groups];
401 #my $logo = $doc->findvalue('/reporter/setup/files/chart_logo');
404 for my $vcol (@values) {
405 next unless (defined $vcol);
407 my @pic_data = ([],[]);
408 for my $row (@$data) {
409 next if (!defined($$row[$vcol]) || $$row[$vcol] == 0);
410 my $val = $$row[$vcol];
411 push @{$pic_data[0]}, join(" -- ", @$row[@groups])." ($val)";
412 push @{$pic_data[1]}, $val;
415 next unless (@{$pic_data[0]});
418 my $split = int(scalar(@{$pic_data[0]}) / $size);
419 my $last = scalar(@{$pic_data[0]}) % $size;
421 for my $sub_graph (0 .. $split) {
423 if ($sub_graph == $split) {
428 for my $set (@pic_data) {
429 push @sub_data, [ splice(@$set,0,$size) ];
432 my $pic = new GD::Graph::pie;
435 label => $r->{column_labels}->[$vcol],
437 legend_placement => 'R',
439 #logo_position => 'TL',
444 my $format = $pic->export_format;
446 open(IMG, ">$file.pie.$vcol.$sub_graph.$format");
451 $pic->plot(\@sub_data) or die $pic->error;
452 print IMG $pic->gd->$format;
455 warn "Couldn't draw $file.pie.$vcol.$sub_graph.$format : $e";
463 { file => "pie.$vcol.$sub_graph.$format",
464 name => $r->{column_labels}->[$vcol].' (Pie)',
465 } unless ($forgetit);
467 last if ($sub_graph == $split);
478 my $data = $r->{data};
480 #my $logo = $doc->findvalue('/reporter/setup/files/chart_logo');
482 my @groups = $r->{resultset}->group_by_list(0);
485 my @values = (0 .. (scalar(@{$r->{column_labels}}) - 1));
486 splice(@values,$_,1) for (reverse @groups);
490 for my $row (@$data) {
491 push @{$pic_data[0]}, join(' -- ', @$row[@groups]);
501 for my $vcol (@values) {
502 next unless (defined $vcol);
506 for my $row (@$data) {
507 my $val = $$row[$vcol] ? $$row[$vcol] : 0;
508 push @{$pic_data[$set]}, $val;
509 $max_y = $val if ($val > $max_y);
510 $trim_candidates{$pos}++ if ($val == 0);
516 my $set_count = scalar(@pic_data) - 1;
517 my @trim_cols = grep { $trim_candidates{$_} == $set_count } keys %trim_candidates;
523 for my $dataset (@pic_data) {
524 splice(@$dataset,$_,1) for (sort { $b <=> $a } @trim_cols);
526 if (grep { $_ } @$dataset) {
527 push @new_data, $dataset;
528 push @use_me, $set_index;
530 push @no_use, $set_index;
536 return [] unless ($new_data[0] && @{$new_data[0]});
538 for my $col (@use_me) {
539 push @leg, $r->{column_labels}->[$col + @groups - 1] if (map { 1 } grep { $col == $_ } @values);
542 my $w = 100 + 10 * scalar(@{$new_data[0]});
543 $w = 400 if ($w < 400);
545 my $h = 10 * (scalar(@new_data) / 2);
549 my $pic = new GD::Graph::bars3d ($w + 250, $h + 500);
552 title => $r->{report}{name},
553 x_labels_vertical => 1,
557 y_max_value => $max_y,
558 legend_placement => 'TR',
561 #logo_position => 'R',
566 $pic->set_legend(@leg);
568 my $format = $pic->export_format;
570 open(IMG, ">$file.bar.$format");
574 $pic->plot(\@new_data) or die $pic->error;
575 print IMG $pic->gd->$format;
578 warn "Couldn't draw $file.bar.$format : $e";
583 return [{ file => "bar.$format",
584 name => $r->{report}{name}.' (Bar)',
592 my $data = $r->{data};
594 #my $logo = $doc->findvalue('/reporter/setup/files/chart_logo');
596 my @groups = $r->{resultset}->group_by_list(0);
598 my @values = (0 .. (scalar(@{$r->{column_labels}}) - 1));
599 splice(@values,$_,1) for (reverse @groups);
603 for my $row (@$data) {
604 push @{$pic_data[0]}, join(' -- ', @$row[@groups]);
612 for my $vcol (@values) {
613 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);
624 my $set_count = scalar(@pic_data) - 1;
630 for my $dataset (@pic_data) {
632 if (grep { $_ } @$dataset) {
633 push @new_data, $dataset;
634 push @use_me, $set_index;
636 push @no_use, $set_index;
642 for my $col (@use_me) {
643 push @leg, $r->{column_labels}->[$col + @groups - 1] if (map { 1 } grep { $col == $_ } @values);
646 my $w = 100 + 10 * scalar(@{$new_data[0]});
647 $w = 400 if ($w < 400);
649 my $h = 10 * (scalar(@new_data) / 2);
653 my $pic = new GD::Graph::lines3d ($w + 250, $h + 500);
656 title => $r->{report}{name},
657 x_labels_vertical => 1,
660 y_max_value => $max_y,
661 legend_placement => 'TR',
664 #logo_position => 'R',
669 $pic->set_legend(@leg);
671 my $format = $pic->export_format;
673 open(IMG, ">$file.line.$format");
677 $pic->plot(\@new_data) or die $pic->error;
678 print IMG $pic->gd->$format;
681 warn "Couldn't draw $file.line.$format : $e";
686 return [{ file => "line.$format",
687 name => $r->{report}{name}.' (Bar)',
695 my $pivot_label = shift;
696 my $pivot_data = shift;
698 $default = 0 unless (defined $default);
700 my $data = $$blob{data};
701 my $cols = $$blob{columns};
703 my @keep_labels = @$cols;
704 splice(@keep_labels, $_ - 1, 1) for (reverse sort ($pivot_label, $pivot_data));
706 my @keep_cols = (0 .. @$cols - 1);
707 splice(@keep_cols, $_ - 1, 1) for (reverse sort ($pivot_label, $pivot_data));
709 #first, find the unique list of pivot values
711 for my $row (@$data) {
712 $tmp{ $$row[$pivot_label - 1] } = 1;
714 my @new_cols = sort keys %tmp;
716 tie my %split_data, 'Tie::IxHash';
717 for my $row (@$data) {
719 my $row_fp = ''. join('', map { defined($$row[$_]) ? $$row[$_] : '' } @keep_cols);
720 $split_data{$row_fp} ||= [];
722 push @{ $split_data{$row_fp} }, $row;
726 #now loop over the data, building a new result set
727 tie my %new_data, 'Tie::IxHash';
729 for my $fp ( keys %split_data ) {
733 for my $col (@keep_cols) {
734 push @{ $new_data{$fp} }, $split_data{$fp}[0][$col];
737 for my $col (@new_cols) {
739 my ($datum) = map { $_->[$pivot_data - 1] } grep { $_->[$pivot_label - 1] eq $col } @{ $split_data{$fp} };
741 push @{ $new_data{$fp} }, $datum;
745 push @keep_labels, @new_cols;
747 return { columns => \@keep_labels, data => [ values %new_data ] };