8 use DateTime::Format::ISO8601;
11 use OpenILS::WWW::Reporter::transforms;
13 my $current_time = DateTime->from_epoch( epoch => time() )->strftime('%FT%T%z');
15 my ($base_xml, $count) = ('/openils/conf/reporter.xml', 1);
18 "file=s" => \$base_xml,
19 "concurrency=i" => \$count,
22 my $parser = XML::LibXML->new;
23 $parser->expand_xinclude(1);
25 my $doc = $parser->parse_file($base_xml);
29 my $db_driver = $doc->findvalue('/reporter/setup/database/driver');
30 my $db_host = $doc->findvalue('/reporter/setup/database/host');
31 my $db_name = $doc->findvalue('/reporter/setup/database/name');
32 my $db_user = $doc->findvalue('/reporter/setup/database/user');
33 my $db_pw = $doc->findvalue('/reporter/setup/database/password');
35 my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host;
37 my $dbh = DBI->connect($dsn,$db_user,$db_pw);
39 # make sure we're not already running $count reports
40 my ($running) = $dbh->selectrow_array(<<SQL);
42 FROM reporter.run_queue
43 WHERE state = 'running';
46 if ($count <= $running) {
47 print "Already running maximum ($running) concurrent reports\n";
51 # if we have some open slots then generate the sql
52 my $run = $count - $running;
53 my $sth = $dbh->prepare(<<SQL);
61 $sth->execute($current_time);
64 while (my $r = $sth->fetchrow_hashref) {
65 $r->{sql} = generate_query( $r );
70 for my $r ( @reports ) {
71 my $sql = shift @{ $r->{sql} };
73 $sth = $dbh->prepare($sql);
75 $sth->execute(@{ $r->{sql} });
76 while (my $row = $sth->fetchrow_hashref) {
77 print join(', ', map {"$_\t=> $$row{$_}"} keys %$row)."\n";
82 #-------------------------------------------------------------------
86 my ($node) = $doc->findnodes("//*[\@id='$id']");
87 if ($node && $node->findvalue('@table')) {
88 ($node) = $doc->findnodes("//*[\@id='".$node->getAttribute('table')."']");
97 my $r = JSON->JSON2perl( $s3->{params} );
100 my $s2 = $dbh->selectrow_hashref(<<" SQL", {}, $s3->{stage2});
109 my $core = $s2->{stage1};
112 for my $t (keys %{$$r{filter}}) {
118 for my $t (keys %{$$r{output}}) {
119 if ($t ne $core && !grep { $t } @dims ) {
128 my $t = table_by_id($d);
129 my $t_name = $t->findvalue('tablename');
130 push @dim_from, "$t_name AS \"$d\"";
132 my $k = $doc->findvalue("//*[\@id='$d']/\@key");
133 push @dim_select, "\"$d\".\"$k\" AS \"${d}_${k}\"";
135 for my $c ( keys %{$$r{output}{$d}} ) {
136 push @dim_select, "\"$d\".\"$c\" AS \"${d}_${c}\"";
139 for my $c ( keys %{$$r{filter}{$d}} ) {
140 next if (exists $$r{output}{$d}{$c});
141 push @dim_select, "\"$d\".\"$c\" AS \"${d}_${c}\"";
146 '(SELECT ' . join(',', @dim_select) .
147 ' FROM ' . join(',', @dim_from) . ') AS dims';
149 warn "*** [$d_select]\n";
155 for my $t ( keys %{$$r{output}} ) {
157 $t_name = "dims" if ($t ne $core);
159 my $t_node = table_by_id($t);
161 for my $c ( keys %{$$r{output}{$t}} ) {
162 my $label = $t_node->findvalue("fields/field[\@name='$c']/label");
165 $full_col = "${t}_${c}" if ($t ne $t_name);
166 $full_col = "\"$t_name\".\"$full_col\"";
169 if (my $xform_type = $$r{xform}{type}{$t}{$c}) {
170 my $xform = $$OpenILS::WWW::Reporter::dtype_xforms{$xform_type};
171 if ($xform->{group}) {
174 $label = "$$xform{label} -- $label";
176 my $tmp = $xform->{'select'};
177 $tmp =~ s/\?COLNAME\?/$full_col/gs;
178 $tmp =~ s/\?PARAM\?/$$r{xform}{param}{$t}{$c}/gs;
184 push @output, "$full_col AS \"$label\"";
189 my $k = $doc->findvalue("//*[\@id='$t']/\@key");
190 my $f = $doc->findvalue("//*[\@id='$t']/\@field");
191 push @join, "dims.\"${t}_${k}\" = \"$core\".\"$f\"";
197 for my $t ( keys %{$$r{filter}} ) {
199 $t_name = "dims" if ($t ne $core);
201 my $t_node = table_by_id($t);
203 for my $c ( keys %{$$r{filter}{$t}} ) {
204 my $label = $t_node->findvalue("fields/field[\@name='$c']/label");
207 $full_col = "${t}_${c}" if ($t ne $t_name);
208 $full_col = "\"$t_name\".\"$full_col\"";
210 # XXX make this use widget specific code
212 my ($fam) = keys %{ $$r{filter}{$t}{$c} };
213 my ($w) = keys %{ $$r{filter}{$t}{$c}{$fam} };
214 my $val = $$r{filter}{$t}{$c}{$fam}{$w};
217 push @where, "$full_col IN (".join(",",map {'?'}@$val).")";
220 push @where, "$full_col = ?";
226 my $t = table_by_id($core)->findvalue('tablename');
227 my $from = " FROM $t AS \"$core\" RIGHT JOIN $d_select ON (". join(' AND ', @join).")";
229 "SELECT ".join(',', @output).
231 ' WHERE '.join(' AND ', @where).
232 ' GROUP BY '.join(',',@groupby);
234 warn " !!! [$select]\n";
235 warn " !!! [".join(', ',@bind)."]\n";
237 return [ $select, @bind ];