From 7a93211b864686fee9f8c34d3f0f0c3f530b40e7 Mon Sep 17 00:00:00 2001 From: miker Date: Thu, 28 Sep 2006 08:09:38 +0000 Subject: [PATCH] first cut of the new clark git-svn-id: svn://svn.open-ils.org/ILS/trunk@6246 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/reporter/clark-kent.pl | 662 +++++++++++------------------------- 1 file changed, 195 insertions(+), 467 deletions(-) diff --git a/Open-ILS/src/reporter/clark-kent.pl b/Open-ILS/src/reporter/clark-kent.pl index fb6f0f1bc6..233e24a488 100755 --- a/Open-ILS/src/reporter/clark-kent.pl +++ b/Open-ILS/src/reporter/clark-kent.pl @@ -10,39 +10,53 @@ use DateTime; use DateTime::Format::ISO8601; use JSON; use Data::Dumper; -use OpenILS::WWW::Reporter::transforms; use Text::CSV_XS; use Spreadsheet::WriteExcel::Big; use OpenSRF::EX qw/:try/; use OpenSRF::Utils qw/:daemon/; use OpenSRF::Utils::Logger qw/:level/; +use OpenSRF::System; +use OpenSRF::AppSession; +use OpenSRF::Utils::SettingsClient; +use OpenILS::Reporter::SQLBuilder; use POSIX; use GD::Graph::pie; use GD::Graph::bars3d; use GD::Graph::lines3d; +use Tie::IxHash; use open ':utf8'; -my ($base_xml, $count, $daemon) = ('/openils/conf/reporter.xml', 1); +my ($count, $config, $lockfile, $daemon) = (1, '/openils/conf/bootstrap.conf', '/tmp/reporter-LOCK'); GetOptions( - "file=s" => \$base_xml, "daemon" => \$daemon, "concurrency=i" => \$count, + "boostrap=s" => \$config, + "lockfile=s" => \$lockfile, ); -my $parser = XML::LibXML->new; -$parser->expand_xinclude(1); +if (-e $lockfile) { + die "I seem to be running already. If not remove $lockfile, try again\n"; +} + +open(F, ">$lockfile"); +print F $$; +close F; -my $doc = $parser->parse_file($base_xml); +OpenSRF::System->bootstrap_client( config_file => $config ); -my $db_driver = $doc->findvalue('/reporter/setup/database/driver'); -my $db_host = $doc->findvalue('/reporter/setup/database/host'); -my $db_port = $doc->findvalue('/reporter/setup/database/port') || '5432'; -my $db_name = $doc->findvalue('/reporter/setup/database/name'); -my $db_user = $doc->findvalue('/reporter/setup/database/user'); -my $db_pw = $doc->findvalue('/reporter/setup/database/password'); +# XXX Get this stuff from the settings server +my $sc = OpenSRF::Utils::SettingsClient->new; +my $db_driver = $sc->config_value( reporter => setup => database => 'driver' ); +my $db_host = $sc->config_value( reporter => setup => database => 'host' ); +my $db_port = $sc->config_value( reporter => setup => database => 'port' ); +my $db_name = $sc->config_value( reporter => setup => database => 'name' ); +my $db_user = $sc->config_value( reporter => setup => database => 'user' ); +my $db_pw = $sc->config_value( reporter => setup => database => 'password' ); + +my $output_base = $sc->config_value( reporter => setup => files => 'output_base' ); my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host . ';port=' . $db_port; @@ -55,34 +69,12 @@ DAEMON: $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1}); $current_time = DateTime->from_epoch( epoch => time() )->strftime('%FT%T%z'); -# Move new reports into the run queue -$dbh->do(<<'SQL', {}, $current_time); -INSERT INTO reporter.output ( stage3, state ) - SELECT id, 'wait' - FROM reporter.stage3 - WHERE runtime <= $1 - AND NOT disable - AND ( ( recurrence = '0 seconds'::INTERVAL - AND ( - id NOT IN ( SELECT stage3 FROM reporter.output ) - OR rerun IS TRUE - ) - ) - OR ( recurrence > '0 seconds'::INTERVAL - AND id NOT IN ( - SELECT stage3 - FROM reporter.output - WHERE state <> 'complete') - ) - ) - ORDER BY runtime; -SQL # make sure we're not already running $count reports ($running) = $dbh->selectrow_array(<prepare(<execute; @reports = (); while (my $r = $sth->fetchrow_hashref) { - my $s3 = $dbh->selectrow_hashref(<<" SQL", {}, $r->{stage3}); - SELECT * FROM reporter.stage3 WHERE id = ?; + my $s3 = $dbh->selectrow_hashref(<<" SQL", {}, $r->{report}); + SELECT * FROM reporter.report WHERE id = ?; SQL - my $s2 = $dbh->selectrow_hashref(<<" SQL", {}, $s3->{stage2}); - SELECT * FROM reporter.stage2 WHERE id = ?; + my $s2 = $dbh->selectrow_hashref(<<" SQL", {}, $s3->{template}); + SELECT * FROM reporter.template WHERE id = ?; SQL - $s3->{stage2} = $s2; - $r->{stage3} = $s3; + $s3->{template} = $s2; + $r->{report} = $s3; - generate_query( $r ); + my $b = OpenILS::Reporter::SQLBuilder->new; + $b->register_params( JSON->JSON2perl( $r->{report}->{data} ) ); + + $r->{resultset} = $b->parse_report( JSON->JSON2perl( $r->{report}->{template}->{data} ) ); push @reports, $r; } @@ -137,86 +132,90 @@ for my $r ( @reports ) { next if (safe_fork()); # This is the child (runner) process; - my $p = JSON->JSON2perl( $r->{stage3}->{params} ); - daemonize("Clark Kent reporting: $p->{reportname}"); + daemonize("Clark Kent reporting: $r->{report}->{name}"); $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1}); try { - $dbh->do(<<' SQL',{}, $r->{sql}->{'select'}, $$, $r->{id}); - UPDATE reporter.output - SET state = 'running', - run_time = 'now', - query = ?, - run_pid = ? + $dbh->do(<<' SQL',{}, $r->{id}); + UPDATE reporter.schedule + SET start_time = 'now', WHERE id = ?; SQL - my ($runtime) = $dbh->selectrow_array("SELECT run_time FROM reporter.output WHERE id = ?",{},$r->{id}); - $r->{run_time} = $runtime; - - $sth = $dbh->prepare($r->{sql}->{'select'}); + $sth = $dbh->prepare($r->{resultset}->toSQL); - $sth->execute(@{ $r->{sql}->{'bind'} }); + $sth->execute; $r->{data} = $sth->fetchall_arrayref; - pivot_data($r); + $r->{column_labels} = [$r->{resultset}->column_label_list]; - my $base = $doc->findvalue('/reporter/setup/files/output_base'); - my $s1 = $r->{stage3}->{stage2}->{stage1}; - my $s2 = $r->{stage3}->{stage2}->{id}; - my $s3 = $r->{stage3}->{id}; - my $output = $r->{id}; + if ($r->{resultset}->pivot_data && $r->{resultset}->pivot_label) { + my @labels = $r->{resultset}->column_label_list; + my $newdata = pivot_data( + { columns => $r->{column_labels}, data => $r->{data}}, + $r->{resultset}->pivot_label, + $r->{resultset}->pivot_data, + $r->{resultset}->pivot_default + ); - mkdir($base); - mkdir("$base/$s1"); - mkdir("$base/$s1/$s2"); - mkdir("$base/$s1/$s2/$s3"); - mkdir("$base/$s1/$s2/$s3/$output"); - - my @formats; - if (ref $p->{output_format}) { - @formats = @{ $p->{output_format} }; - } else { - @formats = ( $p->{output_format} ); + $r->{column_labels} = $newdata->{columns}; + $r->{data} = $newdata->{data}; } + + my $s2 = $r->{report}->{template}->{id}; + my $s3 = $r->{report}->{id}; + my $output = $r->{id}; + + mkdir($output_base); + mkdir("$output_base/$s2"); + mkdir("$output_base/$s2/$s3"); + mkdir("$output_base/$s2/$s3/$output"); - if ( grep { $_ eq 'csv' } @formats ) { - build_csv("$base/$s1/$s2/$s3/$output/report-data.csv", $r); + my $output_dir = "$output_base/$s2/$s3/$output"; + + if ( $r->{csv_format} eq 't') { + build_csv("$output_dir/report-data.csv", $r); } - if ( grep { $_ eq 'excel' } @formats ) { - build_excel("$base/$s1/$s2/$s3/$output/report-data.xls", $r); + if ( $r->{excel_format} eq 't') { + build_excel("$output_dir/report-data.xls", $r); } - if ( grep { $_ eq 'html' } @formats ) { - mkdir("$base/$s1/$s2/$s3/$output/html"); - build_html("$base/$s1/$s2/$s3/$output/report-data.html", $r); + if ( $r->{html_format} eq 't') { + mkdir("$output_dir/html"); + build_html("$output_dir/report-data.html", $r); } $dbh->begin_work; - #$dbh->do(<<' SQL',{}, $r->{run_time}, $r->{stage3}->{id}); - # UPDATE reporter.stage3 - # SET runtime = CAST(? AS TIMESTAMP WITH TIME ZONE) + recurrence - # WHERE id = ? AND recurrence > '0 seconds'::INTERVAL; - #SQL - $dbh->do(<<' SQL',{}, $r->{stage3}->{id}); - UPDATE reporter.stage3 - 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 + + if ($r->{report}->{recur} eq 't') { + my $sql = <<' SQL'; + INSERT INTO reporter.schedule ( report, folder, runner, run_time, email, csv_format, excel_format, html_format) + VALUES ( ?, ?, ?, NOW() + ?, ?, ?, ?, ? ); + SQL + + $dbh->do( + $sql, + {}, + $r->{report}->{id}, + $r->{folder}, + $r->{runner}, + $r->{report}->{recurance}, + $r->{email}, + $r->{csv_format}, + $r->{excel_format}, + $r->{html_format} + ); + } + $dbh->do(<<' SQL',{}, $r->{id}); - UPDATE reporter.output - SET state = 'complete', - complete_time = 'now' + UPDATE reporter.schedule + SET complete_time = 'now' WHERE id = ?; SQL + $dbh->commit; @@ -224,11 +223,10 @@ for my $r ( @reports ) { my $e = shift; $dbh->rollback; $dbh->do(<<' SQL',{}, $e, $r->{id}); - UPDATE reporter.output - SET state = 'error', - error_time = 'now', - error = ?, - run_pid = NULL + UPDATE reporter.schedule + SET error_text = ?, + complete_time = 'now', + error_code = 1, WHERE id = ?; SQL }; @@ -247,126 +245,6 @@ if ($daemon) { #------------------------------------------------------------------- -sub pivot_data { - my $r = shift; - my $p = JSON->JSON2perl( $r->{stage3}->{params} ); - my $settings = $r->{sql}; - my $data = $r->{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; - my $pivot_groupby; - 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; - } - $count++; - } - - - # grab positions of non-group-bys - @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 - my ($val_col) = @values; - - my @remove_me = sort - { $b <=> $a } - ($val_col, $settings->{groupby}->[$pivot_groupby] - 1); - - # get the groups-to-be - my @temp_groupby = @groups; - splice(@temp_groupby, $pivot_groupby, 1); - - @groups = map { ($_ - 1) } @{ $settings->{groupby} }; - - my %p_header; - for my $row (@$data) { - $p_header{ $$row[$settings->{pivot}] } = [] unless exists($p_header{ $$row[$settings->{pivot}] }); - - # add the header from this row's pivot - push @{ $p_header{ $$row[$settings->{pivot}] } }, - { val => $$row[$val_col], fp => join('', map { defined($_) ? $_ : '' } @$row[@temp_groupby]) }; - - splice(@$row,$_,1) for (@remove_me); - } - - push @{ $settings->{columns} }, sort keys %p_header; - - # remove from headings; - splice(@{$settings->{columns}},$_,1) for (@remove_me); - - # remove pivot from groupby - splice(@{$settings->{groupby}}, $pivot_groupby, 1); - @groups = (map { ($_ - 1) } @{ $settings->{groupby} }); - - my %seenit; - my @new_data; - { no warnings; - for my $row (@$data) { - - my $fingerprint = join('',@$row[@groups]); - next if $seenit{$fingerprint}; - - $seenit{$fingerprint}++; - - for my $h ( sort keys %p_header ) { - my $found = 0; - my $bcount = 0; - for my $blob (@{ $p_header{$h} }) { - $fingerprint = join('', map { defined($_) ? $_ : '' } @$row[@groups]); - - if ($blob->{fp} eq $fingerprint ) { - push @$row, $blob->{val}; - $found++; - splice(@{ $p_header{$h} }, $bcount, 1); - last; - } - $bcount++; - } - push @$row, 0 if (!$found); - } - - push @new_data, [@$row]; - } - } - - @new_data = sort { data_sorter($a,$b,\@groups) } @new_data; - - #replace old data with new - $r->{data} = \@new_data; - -} - -sub data_sorter { - no warnings; - - my $_a = shift; - my $_b = shift; - my $sort_cols = shift; - - for my $col (@$sort_cols) { - return -1 if (!defined($$_a[$col])); - return 1 if (!defined($$_b[$col])); - - return -1 if ($$_a[$col] lt $$_b[$col]); - return 1 if ($$_a[$col] gt $$_b[$col]); - } - return 0; -} - sub build_csv { my $file = shift; my $r = shift; @@ -377,7 +255,7 @@ sub build_csv { my $f = new FileHandle (">$file"); - $csv->print($f, $r->{sql}->{columns}); + $csv->print($f, $r->{column_labels}); $csv->print($f, $_) for (@{$r->{data}}); $f->close; @@ -385,16 +263,14 @@ sub build_csv { sub build_excel { my $file = shift; my $r = shift; - my $p = JSON->JSON2perl( $r->{stage3}->{params} ); - my $xls = Spreadsheet::WriteExcel::Big->new($file); - my $sheetname = substr($p->{reportname},1,31); + my $sheetname = substr($r->{report}->{name},1,31); $sheetname =~ s/\W/_/gos; my $sheet = $xls->add_worksheet($sheetname); - $sheet->write_row('A1', $r->{sql}->{columns}); + $sheet->write_row('A1', $r->{column_labels}); $sheet->write_col('A2', $r->{data}); @@ -404,7 +280,6 @@ sub build_excel { sub build_html { my $file = shift; my $r = shift; - my $p = JSON->JSON2perl( $r->{stage3}->{params} ); my $index = new FileHandle (">$file"); my $raw = new FileHandle (">$file.raw.html"); @@ -413,7 +288,7 @@ sub build_html { print $index <<" HEADER"; - $$p{reportname} + $$r{report}{name} -

$$p{reportname}

+

$$r{report}{name}

+ $$r{report}{description}


HEADER # add a link to the raw output html - print $index "Raw output data



"; + print $index "Tabular Output



"; # create the raw output html file - print $raw "$$p{reportname}"; + print $raw "$$r{report}{name}"; print $raw <<' CSS';