11 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::JSON;
18 #use OpenSRF::Utils::Logger qw/:level/;
20 use OpenSRF::AppSession;
21 use OpenSRF::Utils::SettingsClient;
22 use OpenILS::Reporter::SQLBuilder;
25 use GD::Graph::bars3d;
26 use GD::Graph::lines3d;
33 my ($count, $config, $sleep_interval, $lockfile, $daemon) = (1, '/openils/conf/opensrf_core.xml', 10, '/tmp/reporter-LOCK');
37 "sleep=i" => \$sleep_interval,
38 "concurrency=i" => \$count,
39 "boostrap=s" => \$config,
40 "lockfile=s" => \$lockfile,
44 die "I seem to be running already. If not, remove $lockfile and try again\n";
47 OpenSRF::System->bootstrap_client( config_file => $config );
49 # XXX Get this stuff from the settings server
50 my $sc = OpenSRF::Utils::SettingsClient->new;
51 my $db_driver = $sc->config_value( reporter => setup => database => 'driver' );
52 my $db_host = $sc->config_value( reporter => setup => database => 'host' );
53 my $db_port = $sc->config_value( reporter => setup => database => 'port' );
54 my $db_name = $sc->config_value( reporter => setup => database => 'db' );
56 $db_name = $sc->config_value( reporter => setup => database => 'name' );
57 print STDERR "WARN: <database><name> is a deprecated setting for database name. For future compatibility, you should use <database><db> instead." if $db_name;
59 my $db_user = $sc->config_value( reporter => setup => database => 'user' );
60 my $db_pw = $sc->config_value( reporter => setup => database => 'pw' );
62 die "Unable to retrieve database connection information from the settings server" unless ($db_driver && $db_host && $db_port && $db_name && $db_user);
64 my $email_server = $sc->config_value( email_notify => 'smtp_server' );
65 my $email_sender = $sc->config_value( email_notify => 'sender_address' );
66 my $success_template = $sc->config_value( reporter => setup => files => 'success_template' );
67 my $fail_template = $sc->config_value( reporter => setup => files => 'fail_template' );
69 my $output_base = $sc->config_value( reporter => setup => files => 'output_base' );
71 my $base_uri = $sc->config_value( reporter => setup => 'base_uri' );
73 my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host . ';port=' . $db_port;
75 my ($dbh,$running,$sth,@reports,$run, $current_time);
78 open(F, ">$lockfile");
81 daemonize("Clark Kent, waiting for trouble");
87 $dbh = DBI->connect($dsn,$db_user,$db_pw, {AutoCommit => 1, pg_enable_utf8 => 1, RaiseError => 1});
89 $current_time = DateTime->from_epoch( epoch => time() )->strftime('%FT%T%z');
91 # make sure we're not already running $count reports
92 ($running) = $dbh->selectrow_array(<<SQL);
94 FROM reporter.schedule
95 WHERE start_time IS NOT NULL AND complete_time IS NULL;
98 if ($count <= $running) {
102 POSIX::waitpid( -1, POSIX::WNOHANG );
103 sleep $sleep_interval;
106 print "Already running maximum ($running) concurrent reports\n";
110 # if we have some open slots then generate the sql
111 $run = $count - $running;
113 $sth = $dbh->prepare(<<SQL);
115 FROM reporter.schedule
116 WHERE start_time IS NULL AND run_time < NOW()
124 while (my $r = $sth->fetchrow_hashref) {
125 my $s3 = $dbh->selectrow_hashref(<<" SQL", {}, $r->{report});
126 SELECT * FROM reporter.report WHERE id = ?;
129 my $s2 = $dbh->selectrow_hashref(<<" SQL", {}, $s3->{template});
130 SELECT * FROM reporter.template WHERE id = ?;
133 $s3->{template} = $s2;
136 my $b = OpenILS::Reporter::SQLBuilder->new;
137 $b->register_params( OpenSRF::Utils::JSON->JSON2perl( $r->{report}->{data} ) );
139 $r->{resultset} = $b->parse_report( OpenSRF::Utils::JSON->JSON2perl( $r->{report}->{template}->{data} ) );
140 $r->{resultset}->relative_time($r->{run_time});
148 # Now we spawn the report runners
150 for my $r ( @reports ) {
151 next if (safe_fork());
153 # This is the child (runner) process;
154 daemonize("Clark Kent reporting: $r->{report}->{name}");
156 $dbh = DBI->connect($dsn,$db_user,$db_pw, {AutoCommit => 1, pg_enable_utf8 => 1, RaiseError => 1});
159 $dbh->do(<<' SQL',{}, $r->{id});
160 UPDATE reporter.schedule
161 SET start_time = now()
165 $sth = $dbh->prepare($r->{resultset}->toSQL);
168 $r->{data} = $sth->fetchall_arrayref;
170 $r->{column_labels} = [$r->{resultset}->column_label_list];
172 if ($r->{resultset}->pivot_data && $r->{resultset}->pivot_label) {
173 my @labels = $r->{resultset}->column_label_list;
174 my $newdata = pivot_data(
175 { columns => $r->{column_labels}, data => $r->{data}},
176 $r->{resultset}->pivot_label,
177 $r->{resultset}->pivot_data,
178 $r->{resultset}->pivot_default
181 $r->{column_labels} = $newdata->{columns};
182 $r->{data} = $newdata->{data};
183 $r->{group_by_list} = $newdata->{group_by_list};
185 $r->{group_by_list} = [$r->{resultset}->group_by_list(0)];
188 my $s2 = $r->{report}->{template}->{id};
189 my $s3 = $r->{report}->{id};
190 my $output = $r->{id};
193 mkdir("$output_base/$s2");
194 mkdir("$output_base/$s2/$s3");
195 mkdir("$output_base/$s2/$s3/$output");
197 my $output_dir = "$output_base/$s2/$s3/$output";
199 if ( $r->{csv_format} ) {
200 build_csv("$output_dir/report-data.csv", $r);
203 if ( $r->{excel_format} ) {
204 build_excel("$output_dir/report-data.xls", $r);
207 build_html("$output_dir/report-data.html", $r);
211 if ($r->{report}->{recur} ) {
213 INSERT INTO reporter.schedule (
225 VALUES ( ?, ?, ?, ?::TIMESTAMPTZ + ?, ?, ?, ?, ?, ?, ?, ? );
235 $r->{report}->{recurance},
246 $dbh->do(<<' SQL',{}, $r->{id});
247 UPDATE reporter.schedule
248 SET complete_time = now()
254 my $new_r = $dbh->selectrow_hashref(<<" SQL", {}, $r->{id});
255 SELECT * FROM reporter.schedule WHERE id = ?;
258 $r->{start_time} = $new_r->{start_time};
259 $r->{complete_time} = $new_r->{complete_time};
267 $r->{error_text} = ''.$e;
268 if (!$dbh->{AutoCommit}) {
271 $dbh->do(<<' SQL',{}, $e, $r->{id});
272 UPDATE reporter.schedule
274 complete_time = now(),
279 my $new_r = $dbh->selectrow_hashref(<<" SQL", {}, $r->{id});
280 SELECT * FROM reporter.schedule WHERE id = ?;
283 $r->{error_text} = $new_r->{error_text};
284 $r->{complete_time} = $new_r->{complete_time};
294 exit; # leave the child
299 POSIX::waitpid( -1, POSIX::WNOHANG );
300 sleep $sleep_interval;
304 #-------------------------------------------------------------------
308 open F, $success_template;
309 my $tmpl = join('',<F>);
312 my $url = $base_uri . '/' .
313 $r->{report}->{template}->{id} . '/' .
314 $r->{report}->{id} . '/' .
315 $r->{id} . '/report-data.html';
317 $tmpl =~ s/{TO}/$r->{email}/smog;
318 $tmpl =~ s/{FROM}/$email_sender/smog;
319 $tmpl =~ s/{REPLY_TO}/$email_sender/smog;
320 $tmpl =~ s/{REPORT_NAME}/$r->{report}->{template}->{name} -- $r->{report}->{name}/smog;
321 $tmpl =~ s/{RUN_TIME}/$r->{run_time}/smog;
322 $tmpl =~ s/{COMPLETE_TIME}/$r->{complete_time}/smog;
323 $tmpl =~ s/{OUTPUT_URL}/$url/smog;
325 my $sender = Email::Send->new({mailer => 'SMTP'});
326 $sender->mailer_args([Host => $email_server]);
327 $sender->send($tmpl);
332 open F, $fail_template;
333 my $tmpl = join('',<F>);
336 my $sql = $r->{resultset}->toSQL;
338 $tmpl =~ s/{TO}/$r->{email}/smog;
339 $tmpl =~ s/{FROM}/$email_sender/smog;
340 $tmpl =~ s/{REPLY_TO}/$email_sender/smog;
341 $tmpl =~ s/{REPORT_NAME}/$r->{report}->{template}->{name} -- $r->{report}->{name}/smog;
342 $tmpl =~ s/{RUN_TIME}/$r->{run_time}/smog;
343 $tmpl =~ s/{ERROR_TEXT}/$r->{error_text}/smog;
344 $tmpl =~ s/{SQL}/$sql/smog;
346 my $sender = Email::Send->new({mailer => 'SMTP'});
347 $sender->mailer_args([Host => $email_server]);
348 $sender->send($tmpl);
355 my $csv = Text::CSV_XS->new({ always_quote => 1, eol => "\015\012" });
357 return unless ($csv);
359 my $f = new FileHandle (">$file");
361 $csv->print($f, $r->{column_labels});
362 $csv->print($f, $_) for (@{$r->{data}});
369 my $xls = Spreadsheet::WriteExcel::Big->new($file);
371 my $sheetname = substr($r->{report}->{name},1,31);
372 $sheetname =~ s/\W/_/gos;
374 my $sheet = $xls->add_worksheet($sheetname);
376 $sheet->write_row('A1', $r->{column_labels});
378 $sheet->write_col('A2', $r->{data});
387 my $index = new FileHandle (">$file");
390 print $index <<" HEADER";
393 <title>$$r{report}{name}</title>
395 table { border-collapse: collapse; }
396 th { background-color: lightgray; }
397 td,th { border: solid black 1px; }
398 * { font-family: sans-serif; font-size: 10px; }
403 <h2><u>$$r{report}{name}</u></h2>
404 $$r{report}{description}<br/><br/><br/>
409 # add a link to the raw output html
410 push @links, "<a href='report-data.html.raw.html'>Tabular Output</a>" if ($r->{html_format});
412 # add a link to the CSV output
413 push @links, "<a href='report-data.xls'>Excel Output</a>" if ($r->{excel_format});
415 # add a link to the CSV output
416 push @links, "<a href='report-data.csv'>CSV Output</a>" if ($r->{csv_format});
418 print $index join(' -- ', @links);
419 print $index "<br/><br/><br/><br/></center>";
421 if ($r->{html_format}) {
422 # create the raw output html file
423 my $raw = new FileHandle (">$file.raw.html");
424 print $raw "<html><head><title>$$r{report}{name}</title>";
428 table { border-collapse: collapse; }
429 th { background-color: lightgray; }
430 td,th { border: solid black 1px; }
431 * { font-family: sans-serif; font-size: 10px; }
435 print $raw "</head><body><table>";
438 print $raw "<tr><th>".join('</th><th>',@{$r->{column_labels}}).'</th></tr>';
439 print $raw "<tr><td>".join('</td><td>',@$_ ).'</td></tr>' for (@{$r->{data}});
442 print $raw '</table></body></html>';
447 # Time for a pie chart
448 if ($r->{chart_pie}) {
449 my $pics = draw_pie($r, $file);
450 for my $pic (@$pics) {
451 print $index "<img src='report-data.html.$pic->{file}' alt='$pic->{name}'/><br/><br/><br/><br/>";
455 print $index '<br/><br/><br/><br/>';
456 # Time for a bar chart
457 if ($r->{chart_bar}) {
458 my $pics = draw_bars($r, $file);
459 for my $pic (@$pics) {
460 print $index "<img src='report-data.html.$pic->{file}' alt='$pic->{name}'/><br/><br/><br/><br/>";
464 print $index '<br/><br/><br/><br/>';
465 # Time for a bar chart
466 if ($r->{chart_line}) {
467 my $pics = draw_lines($r, $file);
468 for my $pic (@$pics) {
469 print $index "<img src='report-data.html.$pic->{file}' alt='$pic->{name}'/><br/><br/><br/><br/>";
474 print $index '</body></html>';
483 my $data = $r->{data};
485 my @groups = @{ $r->{group_by_list} };
487 my @values = (0 .. (scalar(@{$r->{column_labels}}) - 1));
488 delete @values[@groups];
490 #my $logo = $doc->findvalue('/reporter/setup/files/chart_logo');
493 for my $vcol (@values) {
494 next unless (defined $vcol);
496 my @pic_data = ([],[]);
497 for my $row (@$data) {
498 next if (!defined($$row[$vcol]) || $$row[$vcol] == 0);
499 my $val = $$row[$vcol];
500 push @{$pic_data[0]}, join(" -- ", @$row[@groups])." ($val)";
501 push @{$pic_data[1]}, $val;
504 next unless (@{$pic_data[0]});
507 my $split = int(scalar(@{$pic_data[0]}) / $size);
508 my $last = scalar(@{$pic_data[0]}) % $size;
510 for my $sub_graph (0 .. $split) {
512 if ($sub_graph == $split) {
517 for my $set (@pic_data) {
518 push @sub_data, [ splice(@$set,0,$size) ];
521 my $pic = new GD::Graph::pie;
524 label => $r->{column_labels}->[$vcol],
526 legend_placement => 'R',
528 #logo_position => 'TL',
533 my $format = $pic->export_format;
535 open(IMG, ">$file.pie.$vcol.$sub_graph.$format");
540 $pic->plot(\@sub_data) or die $pic->error;
541 print IMG $pic->gd->$format;
544 warn "Couldn't draw $file.pie.$vcol.$sub_graph.$format : $e";
552 { file => "pie.$vcol.$sub_graph.$format",
553 name => $r->{column_labels}->[$vcol].' (Pie)',
554 } unless ($forgetit);
556 last if ($sub_graph == $split);
567 my $data = $r->{data};
569 #my $logo = $doc->findvalue('/reporter/setup/files/chart_logo');
571 my @groups = @{ $r->{group_by_list} };
574 my @values = (0 .. (scalar(@{$r->{column_labels}}) - 1));
575 splice(@values,$_,1) for (reverse @groups);
579 for my $row (@$data) {
580 push @{$pic_data[0]}, join(' -- ', @$row[@groups]);
590 for my $vcol (@values) {
591 next unless (defined $vcol);
592 $pic_data[$set] ||= [];
595 for my $row (@$data) {
596 my $val = $$row[$vcol] ? $$row[$vcol] : 0;
597 push @{$pic_data[$set]}, $val;
598 $max_y = $val if ($val > $max_y);
599 $trim_candidates{$pos}++ if ($val == 0);
605 my $set_count = scalar(@pic_data) - 1;
606 my @trim_cols = grep { $trim_candidates{$_} == $set_count } keys %trim_candidates;
612 for my $dataset (@pic_data) {
613 splice(@$dataset,$_,1) for (reverse sort @trim_cols);
615 if (grep { $_ } @$dataset) {
616 push @new_data, $dataset;
617 push @use_me, $set_index if ($set_index > 0);
619 push @no_use, $set_index;
625 return [] unless ($new_data[0] && @{$new_data[0]});
627 for my $col (@use_me) {
628 push @leg, $r->{column_labels}->[$values[$col - 1]];
631 my $w = 100 + 10 * scalar(@{$new_data[0]});
632 $w = 400 if ($w < 400);
634 my $h = 10 * (scalar(@new_data) / 2);
638 my $pic = new GD::Graph::bars3d ($w + 250, $h + 500);
641 title => $r->{report}{name},
642 x_labels_vertical => 1,
646 y_max_value => $max_y,
647 legend_placement => 'TR',
650 #logo_position => 'R',
655 $pic->set_legend(@leg);
657 my $format = $pic->export_format;
659 open(IMG, ">$file.bar.$format");
663 $pic->plot(\@new_data) or die $pic->error;
664 print IMG $pic->gd->$format;
667 warn "Couldn't draw $file.bar.$format : $e";
672 return [{ file => "bar.$format",
673 name => $r->{report}{name}.' (Bar)',
681 my $data = $r->{data};
683 #my $logo = $doc->findvalue('/reporter/setup/files/chart_logo');
685 my @groups = @{ $r->{group_by_list} };
687 my @values = (0 .. (scalar(@{$r->{column_labels}}) - 1));
688 splice(@values,$_,1) for (reverse @groups);
692 for my $row (@$data) {
693 push @{$pic_data[0]}, join(' -- ', @$row[@groups]);
701 for my $vcol (@values) {
702 next unless (defined $vcol);
703 $pic_data[$set] ||= [];
706 for my $row (@$data) {
707 my $val = $$row[$vcol] ? $$row[$vcol] : 0;
708 push @{$pic_data[$set]}, $val;
709 $max_y = $val if ($val > $max_y);
714 my $set_count = scalar(@pic_data) - 1;
720 for my $dataset (@pic_data) {
722 if (grep { $_ } @$dataset) {
723 push @new_data, $dataset;
724 push @use_me, $set_index if ($set_index > 0);
726 push @no_use, $set_index;
732 for my $col (@use_me) {
733 push @leg, $r->{column_labels}->[$values[$col - 1]];
736 my $w = 100 + 10 * scalar(@{$new_data[0]});
737 $w = 400 if ($w < 400);
739 my $h = 10 * (scalar(@new_data) / 2);
743 my $pic = new GD::Graph::lines3d ($w + 250, $h + 500);
746 title => $r->{report}{name},
747 x_labels_vertical => 1,
750 y_max_value => $max_y,
751 legend_placement => 'TR',
754 #logo_position => 'R',
759 $pic->set_legend(@leg);
761 my $format = $pic->export_format;
763 open(IMG, ">$file.line.$format");
767 $pic->plot(\@new_data) or die $pic->error;
768 print IMG $pic->gd->$format;
771 warn "Couldn't draw $file.line.$format : $e";
776 return [{ file => "line.$format",
777 name => $r->{report}{name}.' (Bar)',
785 my $pivot_label = shift;
786 my $pivot_data = shift;
788 $default = 0 unless (defined $default);
790 my $data = $$blob{data};
791 my $cols = $$blob{columns};
793 my @keep_labels = @$cols;
794 splice(@keep_labels, $_ - 1, 1) for (reverse sort ($pivot_label, $pivot_data));
796 my @keep_cols = (0 .. @$cols - 1);
797 splice(@keep_cols, $_ - 1, 1) for (reverse sort ($pivot_label, $pivot_data));
799 my @gb = ( 0 .. @keep_cols - 1);
801 #first, find the unique list of pivot values
803 for my $row (@$data) {
804 $tmp{ $$row[$pivot_label - 1] } = 1;
806 my @new_cols = sort keys %tmp;
808 tie my %split_data, 'Tie::IxHash';
809 for my $row (@$data) {
811 my $row_fp = ''. join('', map { defined($$row[$_]) ? $$row[$_] : '' } @keep_cols);
812 $split_data{$row_fp} ||= [];
814 push @{ $split_data{$row_fp} }, $row;
818 #now loop over the data, building a new result set
819 tie my %new_data, 'Tie::IxHash';
821 for my $fp ( keys %split_data ) {
825 for my $col (@keep_cols) {
826 push @{ $new_data{$fp} }, $split_data{$fp}[0][$col];
829 for my $col (@new_cols) {
831 my ($datum) = map { $_->[$pivot_data - 1] } grep { $_->[$pivot_label - 1] eq $col } @{ $split_data{$fp} };
833 push @{ $new_data{$fp} }, $datum;
837 push @keep_labels, @new_cols;
839 return { columns => \@keep_labels, data => [ values %new_data ], group_by_list => \@gb };