yay! the reporter spits out csv and excel now ... html tomorrow (with any luck)
authormiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Fri, 18 Nov 2005 09:29:07 +0000 (09:29 +0000)
committermiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Fri, 18 Nov 2005 09:29:07 +0000 (09:29 +0000)
git-svn-id: svn://svn.open-ils.org/ILS/trunk@2066 dcc99617-32d9-48b4-a31d-7c20da2025e4

13 files changed:
Open-ILS/src/perlmods/OpenILS/WWW/Reporter.pm
Open-ILS/src/reporter/clark-kent.pl
Open-ILS/src/reporter/config.sql
Open-ILS/src/reporter/templates/dashboard.ttk
Open-ILS/src/reporter/templates/header.ttk
Open-ILS/src/reporter/templates/html/cell
Open-ILS/src/reporter/templates/html/center [new file with mode: 0644]
Open-ILS/src/reporter/templates/html/table
Open-ILS/src/reporter/templates/inputs
Open-ILS/src/reporter/templates/select_sorter.js [new file with mode: 0644]
Open-ILS/src/reporter/templates/stage1.ttk
Open-ILS/src/reporter/templates/stage2.ttk
Open-ILS/src/reporter/templates/stage3.ttk

index ecbb273..d9d4ecc 100644 (file)
@@ -55,12 +55,18 @@ sub child_init {
 sub handler {
 
        my $apache = shift;
+       return Apache2::Const::DECLINED if (-e $apache->filename);
+
        my $cgi = CGI->new;
 
        my $path = $apache->path_info;
        (my $ttk = $path) =~ s{^/?([a-zA-Z0-9_]+).*?$}{$1}o;
 
-       $ttk = "s1" unless $ttk;
+       $ttk = $apache->filename unless $ttk;
+       $ttk = "dashboard" unless $ttk;
+
+       $ttk = (split '/', $ttk)[-1];
+       
        my $user;
 
        # if the user is not logged in via cookie, route them to the login page
@@ -68,6 +74,7 @@ sub handler {
                $ttk = "login";
        }
 
+
        print "Content-type: text/html; charset=utf-8\n\n";
 
        _process_template(
@@ -91,7 +98,7 @@ sub _process_template {
        my $apache                      = $params{apache}                       || undef;
        my $param_hash          = $params{params}                       || {};
        $$param_hash{dtype_xform_map} = $OpenILS::WWW::Reporter::dtype_xform_map;
-       $$param_hash{dtype_xform} = $OpenILS::WWW::Reporter::dtype_xform;
+       $$param_hash{dtype_xforms} = $OpenILS::WWW::Reporter::dtype_xforms;
 
        my $template;
 
index 438efb6..9a5a50b 100755 (executable)
@@ -2,6 +2,7 @@
 
 use strict;
 use DBI;
+use FileHandle;
 use XML::LibXML;
 use Getopt::Long;
 use DateTime;
@@ -9,13 +10,19 @@ use DateTime::Format::ISO8601;
 use JSON;
 use Data::Dumper;
 use OpenILS::WWW::Reporter::transforms;
+use Text::CSV_XS;
+use Spreadsheet::WriteExcel;
+use OpenSRF::EX qw/:try/;
+use OpenSRF::Utils qw/:daemon/;
+use OpenSRF::Utils::Logger qw/:level/;
 
 my $current_time = DateTime->from_epoch( epoch => time() )->strftime('%FT%T%z');
 
-my ($base_xml, $count) = ('/openils/conf/reporter.xml', 1);
+my ($base_xml, $count, $daemon) = ('/openils/conf/reporter.xml', 1);
 
 GetOptions(
        "file=s"        => \$base_xml,
+       "daemon"        => \$daemon,
        "concurrency=i" => \$count,
 );
 
@@ -24,8 +31,6 @@ $parser->expand_xinclude(1);
 
 my $doc = $parser->parse_file($base_xml);
 
-warn $doc->toString;
-
 my $db_driver = $doc->findvalue('/reporter/setup/database/driver');
 my $db_host = $doc->findvalue('/reporter/setup/database/host');
 my $db_name = $doc->findvalue('/reporter/setup/database/name');
@@ -34,12 +39,36 @@ my $db_pw = $doc->findvalue('/reporter/setup/database/password');
 
 my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host;
 
-my $dbh = DBI->connect($dsn,$db_user,$db_pw);
+my $dbh;
+
+daemonize("Clark Kent, waiting for trouble") if ($daemon);
+
+DAEMON:
+
+$dbh = DBI->connect($dsn,$db_user,$db_pw);
+
+# 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 (   (       recurrence = '0 seconds'::INTERVAL
+                               AND id NOT IN ( SELECT stage3 FROM reporter.output ) )
+                       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
 my ($running) = $dbh->selectrow_array(<<SQL);
 SELECT count(*)
-  FROM reporter.run_queue
+  FROM reporter.output
   WHERE        state = 'running';
 SQL
 
@@ -50,37 +79,167 @@ if ($count <= $running) {
 
 # if we have some open slots then generate the sql
 my $run = $count - $running;
+
 my $sth = $dbh->prepare(<<SQL);
 SELECT *
-  FROM reporter.stage3
-  WHERE        runtime <= ?
-  ORDER BY runtime
-  LIMIT $run
+  FROM reporter.output
+  WHERE        state = 'wait'
+  ORDER BY queue_time
+  LIMIT $run;
 SQL
 
-$sth->execute($current_time);
+$sth->execute;
 
 my @reports;
 while (my $r = $sth->fetchrow_hashref) {
-       $r->{sql} = generate_query( $r );
+       my $s3 = $dbh->selectrow_hashref(<<"    SQL", {}, $r->{stage3});
+               SELECT * FROM reporter.stage3 WHERE id = ?;
+       SQL
+
+       my $s2 = $dbh->selectrow_hashref(<<"    SQL", {}, $s3->{stage2});
+               SELECT * FROM reporter.stage2 WHERE id = ?;
+       SQL
+
+       $s3->{stage2} = $s2;
+       $r->{stage3} = $s3;
+
+       generate_query( $r );
        push @reports, $r;
 }
+
 $sth->finish;
 
+$dbh->disconnect;
+
+# Now we spaun the report runners
+
 for my $r ( @reports ) {
-       my $sql = shift @{ $r->{sql} };
+       next if (safe_fork());
 
-       $sth = $dbh->prepare($sql);
+       # This is the child (runner) process;
+       my $p = JSON->JSON2perl( $r->{stage3}->{params} );
+       daemonize("Clark Kent reporting: $p->{reportname}");
 
-       $sth->execute(@{ $r->{sql} });
-       while (my $row = $sth->fetchrow_hashref) {
-               print join(', ', map {"$_\t=> $$row{$_}"} keys %$row)."\n";
-       }
+       $dbh = DBI->connect($dsn,$db_user,$db_pw);
+
+       try {
+
+               $dbh->do(<<'            SQL',{}, $r->{sql}->{'select'}, $$, $r->{id});
+                       UPDATE  reporter.output
+                         SET   state = 'running',
+                               run_time = 'now',
+                               query = ?,
+                               run_pid = ?
+                         WHERE id = ?;
+               SQL
+
+               $sth = $dbh->prepare($r->{sql}->{'select'});
+
+               $sth->execute(@{ $r->{sql}->{'bind'} });
+               $r->{data} = $sth->fetchall_arrayref;
+
+               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};
+
+               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} );
+               }
+       
+               if ( grep { $_ eq 'csv' } @formats ) {
+                       build_csv("$base/$s1/$s2/$s3/$output/report-data.csv", $r);
+               }
+               
+               if ( grep { $_ eq 'excel' } @formats ) {
+                       build_excel("$base/$s1/$s2/$s3/$output/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);
+               }
+
+
+               $dbh->begin_work;
+               $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->{id});
+                       UPDATE  reporter.output
+                         SET   state = 'complete',
+                               complete_time = 'now'
+                         WHERE id = ?;
+               SQL
+               $dbh->commit;
+
+
+       } otherwise {
+               my $e = shift;
+               $dbh->rollback;
+               $dbh->do(<<'            SQL',{}, $e, $r->{id});
+                       UPDATE  reporter.output
+                         SET   state = 'error',
+                               error_time = 'now',
+                               error = ?,
+                               run_pid = NULL
+                         WHERE id = ?;
+               SQL
+       };
+
+       $dbh->disconnect;
+
+       exit; # leave the child
 }
 
+if ($daemon) {
+       sleep 60;
+       goto DAEMON;
+}
 
 #-------------------------------------------------------------------
 
+sub build_csv {
+       my $file = shift;
+       my $r = shift;
+
+       my $csv = Text::CSV_XS->new({ always_quote => 1, eol => "\015\012" });
+       my $f = new FileHandle (">$file");
+
+       $csv->print($f, $r->{sql}->{columns});
+       $csv->print($f, $_) for (@{$r->{data}});
+
+       $f->close;
+}
+sub build_excel {
+       my $file = shift;
+       my $r = shift;
+       my $p = JSON->JSON2perl( $r->{stage3}->{params} );
+
+       my $xls = Spreadsheet::WriteExcel->new($file);
+       my $sheet = $xls->add_worksheet($p->{reportname});
+
+       $sheet->write_row('A1', $r->{sql}->{columns});
+
+       $sheet->write_col('A2', $r->{data});
+
+       $xls->close;
+}
+
+sub build_html {}
+
 sub table_by_id {
        my $id = shift;
        my ($node) = $doc->findnodes("//*[\@id='$id']");
@@ -91,36 +250,26 @@ sub table_by_id {
 }
 
 sub generate_query {
-       my $s3 = shift;
-       warn Dumper($s3);
-
-       my $r = JSON->JSON2perl( $s3->{params} );
-       warn Dumper($r);
+       my $r = shift;
 
-       my $s2 = $dbh->selectrow_hashref(<<"    SQL", {}, $s3->{stage2});
-               SELECT  *
-                 FROM  reporter.stage2
-                 WHERE id = ?
-       SQL
-       warn Dumper($s2);
+       my $p = JSON->JSON2perl( $r->{stage3}->{params} );
 
        my @group_by;
        my @aggs;
-       my $core = $s2->{stage1};
+       my $core = $r->{stage3}->{stage2}->{stage1};
        my @dims;
 
-       for my $t (keys %{$$r{filter}}) {
+       for my $t (keys %{$$p{filter}}) {
                if ($t ne $core) {
                        push @dims, $t;
                }
        }
 
-       for my $t (keys %{$$r{output}}) {
+       for my $t (keys %{$$p{output}}) {
                if ($t ne $core && !grep { $t } @dims ) {
                        push @dims, $t;
                }
        }
-       warn Dumper(\@dims);
 
        my @dim_select;
        my @dim_from;
@@ -132,12 +281,12 @@ sub generate_query {
                my $k = $doc->findvalue("//*[\@id='$d']/\@key");
                push @dim_select, "\"$d\".\"$k\" AS \"${d}_${k}\"";
 
-               for my $c ( keys %{$$r{output}{$d}} ) {
+               for my $c ( keys %{$$p{output}{$d}} ) {
                        push @dim_select, "\"$d\".\"$c\" AS \"${d}_${c}\"";
                }
 
-               for my $c ( keys %{$$r{filter}{$d}} ) {
-                       next if (exists $$r{output}{$d}{$c});
+               for my $c ( keys %{$$p{filter}{$d}} ) {
+                       next if (exists $$p{output}{$d}{$c});
                        push @dim_select, "\"$d\".\"$c\" AS \"${d}_${c}\"";
                }
        }
@@ -146,19 +295,23 @@ sub generate_query {
                '(SELECT ' . join(',', @dim_select) .
                '  FROM ' . join(',', @dim_from) . ') AS dims';
        
-       warn "*** [$d_select]\n";
-
+       my @output_order = map { { (split ':')[1] => (split ':')[2] } } @{ $$p{output_order} };
+       
        my $col = 1;
        my @groupby;
        my @output;
+       my @columns;
        my @join;
-       for my $t ( keys %{$$r{output}} ) {
-               my $t_name = $t;
+       my @join_base;
+       for my $pair (@output_order) {
+               my ($t_name) = keys %$pair;
+               my $t = $t_name;
+
                $t_name = "dims" if ($t ne $core);
 
                my $t_node = table_by_id($t);
 
-               for my $c ( keys %{$$r{output}{$t}} ) {
+               for my $c ( values %$pair ) {
                        my $label = $t_node->findvalue("fields/field[\@name='$c']/label");
 
                        my $full_col = $c;
@@ -166,7 +319,7 @@ sub generate_query {
                        $full_col = "\"$t_name\".\"$full_col\"";
 
                        
-                       if (my $xform_type = $$r{xform}{type}{$t}{$c}) {
+                       if (my $xform_type = $$p{xform}{type}{$t}{$c}) {
                                my $xform = $$OpenILS::WWW::Reporter::dtype_xforms{$xform_type};
                                if ($xform->{group}) {
                                        push @groupby, $col;
@@ -175,32 +328,34 @@ sub generate_query {
 
                                my $tmp = $xform->{'select'};
                                $tmp =~ s/\?COLNAME\?/$full_col/gs;
-                               $tmp =~ s/\?PARAM\?/$$r{xform}{param}{$t}{$c}/gs;
+                               $tmp =~ s/\?PARAM\?/$$p{xform}{param}{$t}{$c}/gs;
                                $full_col = $tmp;
                        } else {
                                push @groupby, $col;
                        }
 
                        push @output, "$full_col AS \"$label\"";
+                       push @columns, $label;
                        $col++;
                }
 
-               if ($t ne $t_name) {
+               if ($t ne $t_name && (!@join_base || !grep{$t eq $_}@join_base)) {
                        my $k = $doc->findvalue("//*[\@id='$t']/\@key");
                        my $f = $doc->findvalue("//*[\@id='$t']/\@field");
                        push @join, "dims.\"${t}_${k}\" = \"$core\".\"$f\"";
+                       push @join_base, $t;
                }
        }
 
        my @where;
        my @bind;
-       for my $t ( keys %{$$r{filter}} ) {
+       for my $t ( keys %{$$p{filter}} ) {
                my $t_name = $t;
                $t_name = "dims" if ($t ne $core);
 
                my $t_node = table_by_id($t);
 
-               for my $c ( keys %{$$r{filter}{$t}} ) {
+               for my $c ( keys %{$$p{filter}{$t}} ) {
                        my $label = $t_node->findvalue("fields/field[\@name='$c']/label");
 
                        my $full_col = $c;
@@ -209,9 +364,9 @@ sub generate_query {
 
                        # XXX make this use widget specific code
 
-                       my ($fam) = keys %{ $$r{filter}{$t}{$c} };
-                       my ($w) = keys %{ $$r{filter}{$t}{$c}{$fam} };
-                       my $val = $$r{filter}{$t}{$c}{$fam}{$w};
+                       my ($fam) = keys %{ $$p{filter}{$t}{$c} };
+                       my ($w) = keys %{ $$p{filter}{$t}{$c}{$fam} };
+                       my $val = $$p{filter}{$t}{$c}{$fam}{$w};
 
                        if (ref $val) {
                                push @where, "$full_col IN (".join(",",map {'?'}@$val).")";
@@ -221,6 +376,13 @@ sub generate_query {
                                push @bind, $val;
                        }
                }
+
+               if ($t ne $t_name && (!@join_base || !grep{$t eq $_}@join_base)) {
+                       my $k = $doc->findvalue("//*[\@id='$t']/\@key");
+                       my $f = $doc->findvalue("//*[\@id='$t']/\@field");
+                       push @join, "dims.\"${t}_${k}\" = \"$core\".\"$f\"";
+                       push @join_base, $t;
+               }
        }
 
        my $t = table_by_id($core)->findvalue('tablename');
@@ -231,10 +393,10 @@ sub generate_query {
                  ' WHERE '.join(' AND ', @where).
                  ' GROUP BY '.join(',',@groupby);
 
-       warn " !!! [$select]\n";
-       warn " !!! [".join(', ',@bind)."]\n";
-
-       return [ $select, @bind ];
+       $r->{sql}->{'select'}   = $select;
+       $r->{sql}->{'bind'}     = \@bind;
+       $r->{sql}->{columns}    = \@columns;
+       
 }
 
 
index 9cc8bd8..a8237e8 100644 (file)
@@ -46,7 +46,7 @@ CREATE TABLE reporter.stage3 (
        recurrence      interval
 );
 
-CREATE TABLE reporter.run_queue (
+CREATE TABLE reporter.output (
        id              serial                          primary key,
        stage3          int                             not null
                                                        references reporter.stage3 (id)
@@ -55,20 +55,12 @@ CREATE TABLE reporter.run_queue (
                                                                initially deferred,
        queue_time      timestamp with time zone        not null default now(),
        run_time        timestamp with time zone,
+       run_pid         int,
+       query           text,
+       error           text,
+       error_time      timestamp with time zone,
        complete_time   timestamp with time zone,
-       state           text                            check (state in ('wait','running','complete'))
-);
-
-CREATE TABLE reporter.output (
-       id              int             primary key,
-       stage3          int             not null
-                                       references reporter.stage3 (id)
-                                               on delete restrict
-                                               deferrable
-                                               initially deferred,
-       queue_time      timestamp with time zone        not null default now(),
-       run_time        timestamp with time zone        not null,
-       complete_time   timestamp with time zone        not null
+       state           text                            check (state in ('wait','running','complete','error'))
 );
 
 COMMIT;
index e5a6f2b..8cbfc1d 100644 (file)
@@ -122,6 +122,7 @@ BLOCK show_reports;
                        INCLUDE html/cell content='Recurrence' nowrap='nowrap' col='lightgray' style='border:solid gray 1px;';
                END;
                FOR report = DBI.query(q);
+                       rid = report.id;
                        bg='lightblue';
                        IF loop.count % 2;
                                bg='white';
@@ -129,7 +130,7 @@ BLOCK show_reports;
                        WRAPPER html/row;
                                p = utils.JSON2perl( report.params );
                                INCLUDE html/cell content=(report.pub ? 'Y' : 'N') col=bg style='border:solid gray 1px;' align='center';
-                               INCLUDE html/cell content=p.reportname col=bg style='border:solid gray 1px;';
+                               INCLUDE html/cell content=link(content=p.reportname,href="stage3?id=$rid") col=bg style='border:solid gray 1px;';
                                INCLUDE html/cell content=report.create_date.chunk(10).0 col=bg style='border:solid gray 1px;';
                                INCLUDE html/cell content=report.runtime.chunk(10).0 col=bg style='border:solid gray 1px;';
                                INCLUDE html/cell content=report.recurrence col=bg style='border:solid gray 1px;';
index 7cce23f..28e7de9 100644 (file)
@@ -13,5 +13,7 @@
        <style>
                .navbar { border-bottom: 1px solid gray; padding-right: 20px; padding-left: 5px; }
        </style>
+
+       [% content %]
 </head>
 
index 890ab80..8a9d680 100644 (file)
@@ -14,6 +14,7 @@
     [%- IF colspan %] colspan="[% colspan %]"[% END %]
     [%- IF rowspan %] rowspan="[% rowspan %]"[% END %]
     [%- IF  align %] align="[% align %]"[% END %]
+    [%- IF  padding %] padding="[% padding %]"[% END %]
     [%- IF  class %] class="[% class %]"[% END %]
     [%- IF  id %] id="[% id %]"[% END %]
     [%- IF  valign %] valign="[% valign %]"[% END %]
diff --git a/Open-ILS/src/reporter/templates/html/center b/Open-ILS/src/reporter/templates/html/center
new file mode 100644 (file)
index 0000000..c72e721
--- /dev/null
@@ -0,0 +1 @@
+<center>[% content %]</center>
index 7b00959..1a2a83a 100644 (file)
@@ -12,6 +12,7 @@
        [%- IF width %] width="[% width %]"[% END %]
        [%- IF pad.defined %] cellpadding="[% pad %]"[% END %]
        [%- IF class %] class="[% class %]"[% END %]
+       [%- IF style %] style="[% style %]"[% END %]
        [%- IF id %] id="[% id %]"[% END %]
        [%- IF cellspace.defined %] cellspacing="[% cellspace %]"[% END %]>
 [%- content -%]
index db967ac..5f86ad2 100644 (file)
@@ -42,7 +42,7 @@ BLOCK textarea;
 END;
 
 BLOCK select;
-       %]<select name="[% name %]" size="[% size %]"[% IF multi %] multiple="multiple" [% END %]>[%
+       %]<select id="[% id %]" name="[% name %]" size="[% size %]"[% IF multi %] multiple="multiple" [% END %]>[%
                content;
        %]</select>[%
 END;
@@ -66,11 +66,15 @@ BLOCK radio;
 END;
 
 BLOCK checkbox;
-       %]<INPUT TYPE="checkbox" NAME="[% name %]" VALUE="[% value %]" [% IF checked %]CHECKED[% END %]>[%
+       %]<INPUT TYPE="checkbox" NAME="[% name %]" VALUE="[% value %]" [% IF onclick %]onclick="[% onclick %]"[% END %] [% IF checked %]CHECKED[% END %]>[%
 END;
 
 BLOCK submit;
-       %]<INPUT TYPE="submit" NAME="[% name %]" VALUE="[% value %]">[%
+       %]<INPUT TYPE="submit" onclick="[% onclick %]" NAME="[% name %]" VALUE="[% value %]">[%
+END;
+
+BLOCK button;
+       %]<button NAME="[% name %]" VALUE="[% value %]" onclick="[% onclick %]">[% content %]</button>[%
 END;
 
 MACRO link INCLUDE anchor;
diff --git a/Open-ILS/src/reporter/templates/select_sorter.js b/Open-ILS/src/reporter/templates/select_sorter.js
new file mode 100644 (file)
index 0000000..868408f
--- /dev/null
@@ -0,0 +1,147 @@
+<script language="javascript">
+
+function field_add_remove (x) {
+       var field = x.name;
+       if (x.checked)
+               Widget.Select.addOption('output_order',field, outputs[field]);
+       else
+               Widget.Select.removeOption('output_order',field);
+       return true;
+}
+
+
+if ( typeof Widget == "undefined" ) Widget = {};
+
+if ( typeof Widget.Select == "undefined" ) Widget.Select = {};
+
+Widget.Select.VERSION = '0.01';
+
+
+Widget.Select.selectAll = function (source){
+       if (typeof(source) != 'object') source = document.getElementById(source);
+       var l = source.options.length;
+    for (var j=0; j<l; j++){
+       source.options[j].selected = true;
+    }
+}
+
+
+Widget.Select.selectNone = function(source){
+       if (typeof(source) != 'object') source = document.getElementById(source);
+       var l = source.options.length;
+    for (var j=0; j<l; j++){
+       source.options[j].selected = false;
+    }
+}
+
+
+Widget.Select.invertSelection = function(source){
+       if (typeof(source) != 'object') source = document.getElementById(source);
+       var l = source.options.length;
+    for (var j=0; j<l; j++){
+       source.options[j].selected = ! source.options[j].selected;
+    }
+}
+
+Widget.Select._moveOption = function(e, source, s_idx, target){
+                       var opt = new Option(
+                               e.text, e.value);
+                       opt.selected = e.selected;
+                       target.options[target.options.length] = opt;
+                       source.options[s_idx] = null;
+}
+
+
+Widget.Select.moveSelectedOptionsUp = function(source){
+       if (typeof(source) != 'object') source = document.getElementById(source);
+       var l = source.options.length;
+    for (var j=0; j<l; j++){
+       
+               var e = source.options[0];
+               if (e.selected){
+                       Widget.Select._moveOption(e, source, 0, source, l);
+                       continue;
+               }
+               
+       
+               while (j<l-1){
+                       var f= source.options[1];
+                       if (!f.selected) break;
+                       Widget.Select._moveOption(f, source, 1, source, l);
+                       j++;
+               }
+       
+               Widget.Select._moveOption(e, source, 0, source, l);
+       }
+                       
+}
+
+
+Widget.Select.moveSelectedOptionsDown = function(source){
+       if (typeof(source) != 'object') source = document.getElementById(source);
+       var l = source.options.length;
+    var skip=0;
+    for (var j=0; j<l; j++){
+               var e = source.options[0];
+               if (skip == 0){
+                       if (e.selected){
+                               for (var i=1;i<l-j; i++){
+                                       var f = source.options[i];
+                                       if (! f.selected){
+                                               Widget.Select._moveOption(f, source, i, source, l);
+                                               j++;
+                                               break;
+                                       }
+                                       skip++;
+                                       
+                               }
+                               
+                       }
+               }else{
+                       skip--;
+               }       
+               
+               Widget.Select._moveOption(e, source, 0, source, l);
+               
+       }
+                       
+}
+
+
+
+Widget.Select.moveSelectedOptionsTo = function(source, target){
+       if (typeof(source) != 'object') source = document.getElementById(source);
+       if (typeof(target) != 'object') target = document.getElementById(target);
+       for (var i=0; i<source.options.length; i++){
+               var e = source.options[i];
+               if(e.selected){
+                       Widget.Select._moveOption(e,source, i, target, target.options.length);
+                       i--;
+               }
+       }
+}
+
+
+
+
+
+
+Widget.Select.addOption = function (target,val,l) {
+       if (typeof(target) != 'object') target = document.getElementById(target);
+       target.options[target.options.length] = new Option( l, val );
+}
+
+Widget.Select.removeOption = function (target, val) {
+       if (typeof(target) != 'object') target = document.getElementById(target);
+       var l = target.options.length;
+       for ( var i = 0; i<l; i++) {
+               if (target.options[i].value == val) {
+                       target.options[i] = null;
+                       break;
+               }
+       }
+}
+
+
+
+</script>
index 8aed7c2..45b5670 100644 (file)
@@ -13,11 +13,6 @@ WRAPPER html/html;
                                                                "document.getElementById('templatename').focus();" _
                                                                "document.getElementById('templatename').select();}";
                IF !CGI.param('id');
-                       IF CGI.param('detail');
-                               %]<a href="?detail=0">No Details (All)</a>[%
-                       ELSE;
-                               %]<a href="?detail=1">Details (All)</a>[%
-                       END;
                        INCLUDE all_stage1;
                ELSE;
                        INCLUDE one_stage1;
@@ -33,12 +28,6 @@ END;
 
 
 BLOCK one_stage1;
-       %]<a href="[%
-       CGI.url('-absolute',1,'-path_info',1,'-query',1)
-               .replace('[&\?;]?id=[\w\.]+','')
-               .replace('[&\?;]?create_stage2=1','');
-       %]">Show all</a>[%
-
        table_xpath =
                '/reporter/tables/table[@id="' _
                CGI.param('id') _ '"]';
@@ -85,27 +74,16 @@ BLOCK stage2_new;
 
        %]<br/><form id="stage2_new" name="stage2_new" action="stage2" method="POST">
                <input type="hidden" name="action" value="save">
-                       <input type="hidden" name="stage1" value="[% CGI.param('id') %]">
-                       <input type="text" name="templatename" id="templatename"
-                               size="40" value="Template name (required)"><br/>
-                       <input type="checkbox" name="publictemplate" value="t">
-                               Public template<br><br>[%
+               <input type="hidden" name="stage1" value="[% CGI.param('id') %]">
+               <center>
+                       <b>Template name (required):</b> <input type="text" name="templatename" id="templatename" size="40" ><br/><br><br>
+               </center>
+               <b>Select Filter Widgets</b><br>[%
 
        
        WRAPPER html/table width="100%" style='border-top: 1px solid black';
                WRAPPER html/row;
-                       WRAPPER html/cell;
-                               WRAPPER html/table + html/row width='100%';
-                                       INCLUDE html/cell content='<b>Report Base</b>';
-                               END;
-                               WRAPPER html/table + html/row width='100%';
-                                       INCLUDE html/cell content='<b>Attribute</b>' align='right';
-                               END;
-                       END;
-                       INCLUDE html/cell content='<b>Widget</b>' align='center';
-               END;
-               WRAPPER html/row;
-                       WRAPPER html/cell colspan=2;
+                       WRAPPER html/cell colspan=2  align='center' style="border: solid black 1px; background: lightgray;";
                                # hidden input here ...
                                table = config.findnodes("/reporter/tables/table[@id='$fact_table']");
                                INCLUDE hidden name="filter" value=fact_table;
@@ -113,6 +91,10 @@ BLOCK stage2_new;
                                ' Core Columns';
                        END;
                END;
+               #WRAPPER html/row;
+               #       INCLUDE html/cell content='<b>Attribute</b>' align='right';
+               #       INCLUDE html/cell content='<b>Widget</b>' align='center';
+               #END;
                INCLUDE widget_selector tableid=fact_table columns=config.findnodes(col_xpath) force=1;
        END;
 
@@ -126,18 +108,7 @@ BLOCK stage2_new;
 
                WRAPPER html/table width="100%" style='border-top: 1px solid black';
                        WRAPPER html/row;
-                               WRAPPER html/cell;
-                                       WRAPPER html/table + html/row width='100%';
-                                               INCLUDE html/cell content='<b>Dimension</b>';
-                                       END;
-                                       WRAPPER html/table + html/row width='100%';
-                                               INCLUDE html/cell content='<b>Attribute</b>' align='right';
-                                       END;
-                               END;
-                               INCLUDE html/cell content='<b>Widget</b>' align='center';
-                       END;
-                       WRAPPER html/row;
-                               WRAPPER html/cell colspan=2;
+                               WRAPPER html/cell colspan=2 align='center' style="border: solid black 1px; background: lightgray;";
                                        # checkbox input here ...
                                        INCLUDE checkbox name="filter" value=link_id checked=1;
 
@@ -153,9 +124,12 @@ BLOCK stage2_new;
                END;
        END;
 
+       %]
+               <input type="checkbox" name="publictemplate" value="t">Public template<br><br>
+       [%
        INCLUDE submit name='button' value='Save';
 
-       %]</form>[%
+       '</form>';
 
 END;
 
index 0bd913e..cb3d107 100644 (file)
@@ -1,24 +1,27 @@
 [%
 
-PROCESS inputs;
-PROCESS class_manip;
-PROCESS widget_manip;
-PROCESS logic_header.ttk;
-INCLUDE logout.ttk;
+WRAPPER html/html;
+   WRAPPER header.ttk + navbar.ttk title="Report Templates";
+       PROCESS inputs;
+       PROCESS class_manip;
+       PROCESS widget_manip;
+       PROCESS logic_header.ttk;
+       INCLUDE logout.ttk;
+   END;
 
-templates = DBI.tie('reporter.stage2', 'id')
-tmpl = {};
+   WRAPPER html/body;
 
+       PROCESS select_sorter.js;
 
-IF CGI.param('id');
-       tid = CGI.param('id');
-       tmpl = templates.$tid;
-END;
+       templates = DBI.tie('reporter.stage2', 'id')
+       tmpl = {};
 
 
-WRAPPER html/html;
-   INCLUDE header.ttk + navbar.ttk title="Report Templates";
-   WRAPPER html/body;
+       IF CGI.param('id');
+               tid = CGI.param('id');
+               tmpl = templates.$tid;
+       END;
+
       IF CGI.param('action') == 'save';
          PROCESS save_stage2;
       ELSIF CGI.param('action') == 'edit';
@@ -232,40 +235,90 @@ BLOCK run_stage2;
        logme(tmpl.params);
        params = utils.JSON2perl( tmpl.params );
 
-   '<br/>';
+       %]
+               <script language="javascript">
+                       var outputs = {};
+               </script>
+               <br/>
+       [%
+
+       WRAPPER html/table width="100%";
+               WRAPPER html/row;
+                       WRAPPER html/cell align='center';
+                               s1_name_xpath = '/reporter/tables/table[@id="' _ params.stage1 _ '"]/label';
+                               INCLUDE anchor
+                                       content=config.findvalue( s1_name_xpath )
+                                       href="stage1?id=" _ params.stage1;
+                               ' :: ' _ params.templatename;
+                       END;
+               END;
+       END;
+
+       '<br/><br/>';
+
        WRAPPER form id="stage3_new" name="stage3_new" action="stage3" method="POST";
                INCLUDE hidden name='stage2' value=CGI.param('id');
 
-               '<br>Report Name:';
-               INCLUDE text name='reportname';
+               '<br/><center><b>Report Name:</b>';
+               INCLUDE text name='reportname' size="50";
+               '</center><br/><br/>';
 
-               WRAPPER html/table border=0 width='100%';
+               WRAPPER html/table width="100%";
+                       WRAPPER html/row  style="border-top: solid black 2px;";
+                               INCLUDE html/cell colspan=2 content='<b>Output and Sort Order</b>' style="border-top: solid black 2px;";
+                               INCLUDE html/cell colspan=2 content='<b>Output Formats</b>' align="center" style="border-top: solid black 2px;";
+                       END;
                        WRAPPER html/row;
-                               WRAPPER html/cell colspan=4 align='center';
-                                       s1_name_xpath = '/reporter/tables/table[@id="' _ params.stage1 _ '"]/label';
-                                       INCLUDE anchor
-                                               content=config.findvalue( s1_name_xpath )
-                                               href="stage1?id=" _ params.stage1;
-                                       ' :: ' _ params.templatename;
+                               WRAPPER html/cell rowspan=3 width="25%";
+                                       INCLUDE select multi=1 size=5 id='output_order' name='output_order';
+                               END;
+                               WRAPPER html/cell;
+                                       INCLUDE anchor href='javascript:void(0);' content='Up' onclick="Widget.Select.moveSelectedOptionsUp('output_order')";
                                END;
+
+                               WRAPPER html/cell align="right";
+                                       INCLUDE checkbox name='output_format' value='csv';
+                               END;
+                               INCLUDE html/cell content="CSV" align="left";
                        END;
+                       WRAPPER html/row;
+                               WRAPPER html/cell;
+                                       INCLUDE anchor href='javascript:void(0);' content='Down' onclick="Widget.Select.moveSelectedOptionsDown('output_order')";
+                               END;
 
+                               WRAPPER html/cell align="right";
+                                       INCLUDE checkbox name='output_format' value='excel';
+                               END;
+                               INCLUDE html/cell content="Excel" align="left";
+                       END;
                        WRAPPER html/row;
-                               INCLUDE html/cell colspan=4 content="<b>Filterable Attributes:</b>";
+                               INCLUDE html/cell;
+                               WRAPPER html/cell align="right";
+                                       INCLUDE checkbox name='output_format' value='html';
+                               END;
+                               INCLUDE html/cell content="HTML" align="left";
+                       END;
+               END;
+
+               '<br/><br/>';
+
+               WRAPPER html/table border=0 width='100%';
+                       WRAPPER html/row;
+                               INCLUDE html/cell colspan=4 content="<b>Filterable Attributes</b><br/><br/>" style="border-top: solid black 2px;";
                        END;
 
                        core_table = params.stage1;
 
                        tables = params.filter.keys;
-                       tables.unshift(core_table);
+                       tables.unshift(core_table) IF tables.grep("^$core_table$");
                        tables = tables.unique;
 
                        FOR t = tables;
+                               NEXT UNLESS params.filter.$t.keys;
                                f_table = INCLUDE find_table_id id=t;
-                               logme([f_table]);
                                table = config.findnodes( "/reporter/tables/table[@id='$f_table']");
                                WRAPPER html/row;
-                                       WRAPPER html/cell colspan=3 align='left';
+                                       WRAPPER html/cell colspan=3 align='center' style="border: solid black 1px; background: lightgray;";
                                                IF t != f_table;
                                                        link_xpath = '//*[@id="' _ t _ '"]';
                                                        dim_link = config.findnodes(link_xpath);
@@ -321,7 +374,7 @@ BLOCK run_stage2;
                        END;
 
                        WRAPPER html/row;
-                               INCLUDE html/cell colspan=4 content="<b>Attributes Available for Output</b>";
+                               INCLUDE html/cell colspan=4 content="<b>Attributes Available for Output</b><br/><br/>" style="border-top: solid black 2px;";
                        END;
 
                        table = config.findnodes( "/reporter/tables/table[@id='$core_table']" );
@@ -330,7 +383,7 @@ BLOCK run_stage2;
                        tdesc = table.findvalue( "desription" );
 
                        WRAPPER html/row;
-                               INCLUDE html/cell colspan=4 align='left' content=tname;
+                               INCLUDE html/cell colspan=4 align='center' content=tname style="border: solid black 1px; background: lightgray;";
                        END;
 
                        pkey_field = table.findnodes('fields/field[@primary = "true"]');
@@ -339,8 +392,14 @@ BLOCK run_stage2;
                
                        WRAPPER html/row;
                                WRAPPER html/cell align='right';
-                                       INCLUDE checkbox checked='checked' name="output:$classname:$pid" value="include";
+                                       INCLUDE checkbox checked='checked' name="output:$classname:$pid" value="include" onclick="field_add_remove(this)";
                                        INCLUDE hidden name="xform:type:$classname:$pid" value="count_dist";
+                                       %]
+                                               <script language="javascript">
+                                                       outputs['[% "output:$classname:$pid" %]'] = '[% "Per group count of $plabel" %]';
+                                                       Widget.Select.addOption('output_order','[% "output:$classname:$pid" %]', '[% "Per group count of $plabel" %]');
+                                               </script>
+                                       [%
                                END;
                                INCLUDE html/cell align='right' content="Per group count of $plabel";
                                INCLUDE html/cell align='center';
@@ -357,13 +416,17 @@ BLOCK run_stage2;
                                noheader = 0;
                                noheader = 1 IF f_table == core_table;
                                INCLUDE show_filter_atts;
+                               WRAPPER html/row;
+                                       INCLUDE html/cell colspan=4 content="<br/><br/>";
+                               END;
                        END;
                END;
        
+
                INCLUDE checkbox name="publicreport" value="t";
                'Public Report<br>';
-               INCLUDE submit name="action" value="Run Now";
-               INCLUDE submit name="action" value="Schedule";
+               INCLUDE submit name="action" value="Run Now" onclick="Widget.Select.selectAll('output_order');this.form.submit();";
+               INCLUDE submit name="action" value="Schedule" onclick="Widget.Select.selectAll('output_order');this.form.submit();";
 
        END;
 END;
@@ -378,7 +441,7 @@ BLOCK show_filter_atts;
 
        IF ! noheader;
                WRAPPER html/row;
-                       WRAPPER html/cell colspan=4 align='left';
+                       WRAPPER html/cell colspan=4 align='center' style="border: solid black 1px; background: lightgray;";
                                IF t != f_table;
                                        link_xpath = '//*[@id="' _ t _ '"]';
                                        dim_link = config.findnodes(link_xpath);
@@ -397,6 +460,9 @@ BLOCK show_filter_atts;
 
        fields_xpath = 'fields/field[not(@primary) or @primary != "true"]';
 
+
+       count = 1;
+
        FOR field = table.findnodes(fields_xpath);
                link_test = '../../links/link[@field="' _ field.findvalue('@name') _ '"]';
                logme(['link_test',link_test]);
@@ -405,13 +471,24 @@ BLOCK show_filter_atts;
                fname = field.findvalue( "label" );
                fid = field.findvalue( "@name" );
 
+               bg_color = 'white';
+               IF count % 2;
+                       bg_color = 'lightcyan';
+               END;
+
                WRAPPER html/row;
 
-                       WRAPPER html/cell align='right';
-                               INCLUDE checkbox name="output:$t:$fid" value="include";
+                       WRAPPER html/cell align='right' col=bg_color;
+                               INCLUDE checkbox name="output:$t:$fid" value="include" onclick="field_add_remove(this)";
+                               %]
+                                       <script language="javascript">
+                                               outputs['[% "output:$t:$fid" %]'] = '[% fname %]';
+                                       </script>
+                               [%
+
                        END;
-                       INCLUDE html/cell align='right' content=fname;
-                       WRAPPER html/cell align='center';
+                       INCLUDE html/cell align='right' content=fname col=bg_color;
+                       WRAPPER html/cell align='right' col=bg_color;
                                'Output Transformation: ';
                                dtype = field.findvalue('@datatype');
                                WRAPPER select name="xform:type:$t:$fid";
@@ -429,6 +506,7 @@ BLOCK show_filter_atts;
                                INCLUDE text name="xform:param:$t:$fid";
                        END;
                END;
+               count = count + 1;
        END;
 END;
 %]
index 06a2c8a..0edafb1 100644 (file)
@@ -13,10 +13,10 @@ tmpl = {};
 
 
 IF CGI.param('id');
-       tid = CGI.param('stage2');
-       tmpl = templates.$tid;
        rid = CGI.param('id');
        rpt = reports.$rid;
+       tid = rpt.stage2;
+       tmpl = templates.$tid;
 END;
 
 
@@ -182,9 +182,99 @@ END;
 BLOCK view_stage3;
        logme(rpt.params);
        params = utils.JSON2perl( rpt.params );
+       s2_params = utils.JSON2perl( tmpl.params );
+
 
-       # stuff goes here ...
+       WRAPPER html/center;
+               WRAPPER html/table style="border-collapse:collapse";
+                       WRAPPER html/row;
+                               INCLUDE html/cell content="<b>Report Name</b>" style="border:solid gray 1px;";
+                               INCLUDE html/cell content=params.reportname style="border:solid gray 1px;";
+                       END;
+                       WRAPPER html/row;
+                               INCLUDE html/cell content="<b>Based on Template</b>" style="border:solid gray 1px;";
+                               INCLUDE html/cell content=s2_params.templatename style="border:solid gray 1px;";
+                       END;
+                       WRAPPER html/row;
+                               INCLUDE html/cell content="<b>Creation Time</b>" style="border:solid gray 1px;";
+                               INCLUDE html/cell content=rpt.create_date style="border:solid gray 1px;";
+                       END;
+                       WRAPPER html/row;
+                               INCLUDE html/cell content="<b>Scheduled Run Time</b>" style="border:solid gray 1px;";
+                               INCLUDE html/cell content=rpt.runtime style="border:solid gray 1px;";
+                       END;
+                       WRAPPER html/row;
+                               INCLUDE html/cell content="<b>Recurrence Interval</b>" style="border:solid gray 1px;";
+                               INCLUDE html/cell content=rpt.recurrence style="border:solid gray 1px;";
+                       END;
+               END;
+       END;
 
+       '<br><br>';
+
+       type_map = { 'csv' => 'csv', 'excel' => 'xls', 'html' => 'html' };
+
+       WRAPPER html/center;
+               '<b>Report Runs:</b>';
+               run_q = DBI.prepare("SELECT * FROM reporter.output WHERE stage3 = ? ORDER BY queue_time DESC");
+               FOR run = run_q.execute(rpt.id);
+                       WRAPPER html/table style="border-collapse:collapse";
+                               WRAPPER html/row;
+                                       INCLUDE html/cell content="<b>Current State</b>" style="border:solid gray 1px;" width="20%";
+                                       WRAPPER html/cell style="border:solid gray 1px;";
+                                               run.state;
+                                               IF run.state == 'complete';
+                                                       '<br>';
+                                                       FOR f = params.output_format.list;
+                                                               h = tmpl.stage1 _ '/' _ tmpl.id _
+                                                                       '/' _ rpt.id _ '/' _ run.id _
+                                                                       '/report-data.' _ type_map.$f;
+                                                               link(content=f, href=h);
+                                                               IF !loop.last;
+                                                                       '  |  ';
+                                                               END;
+                                                       END;
+                                               END;
+                                       END;
+                               END;
+                               WRAPPER html/row;
+                                       INCLUDE html/cell content="<b>Queue Time</b>" style="border:solid gray 1px;";
+                                       INCLUDE html/cell content=run.queue_time style="border:solid gray 1px;";
+                               END;
+                               IF run.run_time;
+                                       WRAPPER html/row;
+                                               INCLUDE html/cell content="<b>Run Time</b>" style="border:solid gray 1px;";
+                                               INCLUDE html/cell content=run.run_time style="border:solid gray 1px;";
+                                       END;
+                               END;
+                               IF run.complete_time;
+                                       WRAPPER html/row;
+                                               INCLUDE html/cell content="<b>Complete Time</b>" style="border:solid gray 1px;";
+                                               INCLUDE html/cell content=run.complete_time style="border:solid gray 1px;";
+                                       END;
+                               END;
+                               IF run.query;
+                                       WRAPPER html/row;
+                                               INCLUDE html/cell content="<b>Query</b>" style="border:solid gray 1px;";
+                                               INCLUDE html/cell content=run.query style="border:solid gray 1px;";
+                                       END;
+                               END;
+                               IF run.error;
+                                       WRAPPER html/row;
+                                               INCLUDE html/cell content="<b>Error</b>" style="border:solid gray 1px;";
+                                               INCLUDE html/cell content=run.error style="border:solid gray 1px;";
+                                       END;
+                               END;
+                               IF run.error_time;
+                                       WRAPPER html/row;
+                                               INCLUDE html/cell content="<b>Error Time</b>" style="border:solid gray 1px;";
+                                               INCLUDE html/cell content=run.error_time style="border:solid gray 1px;";
+                                       END;
+                               END;
+                       END;
+                       '<br><br>';
+               END;
+       END;
 END;
 
 %]