Lines 26-31
use C4::Auth;
Link Here
|
26 |
use C4::Output; |
26 |
use C4::Output; |
27 |
use C4::Dates; |
27 |
use C4::Dates; |
28 |
use C4::Debug; |
28 |
use C4::Debug; |
|
|
29 |
use C4::Branch; # XXX subfield_is_koha_internal_p |
29 |
|
30 |
|
30 |
=head1 NAME |
31 |
=head1 NAME |
31 |
|
32 |
|
Lines 226-232
elsif ( $phase eq 'Choose these criteria' ) {
Link Here
|
226 |
if ($value =~ C4::Dates->regexp('syspref')) { |
227 |
if ($value =~ C4::Dates->regexp('syspref')) { |
227 |
$value = C4::Dates->new($value)->output("iso"); |
228 |
$value = C4::Dates->new($value)->output("iso"); |
228 |
} |
229 |
} |
229 |
$query_criteria .= " AND $crit='$value'"; |
230 |
# don't escape runtime parameters, they'll be at runtime |
|
|
231 |
if ($value =~ /<<.*>>/) { |
232 |
$query_criteria .= " AND $crit=$value"; |
233 |
} else { |
234 |
$query_criteria .= " AND $crit='$value'"; |
235 |
} |
230 |
} |
236 |
} |
231 |
warn $query_criteria; |
237 |
warn $query_criteria; |
232 |
} |
238 |
} |
Lines 304-311
elsif ( $phase eq 'Build Report' ) {
Link Here
|
304 |
my $column = $input->param('column'); |
310 |
my $column = $input->param('column'); |
305 |
my $crit = $input->param('criteria'); |
311 |
my $crit = $input->param('criteria'); |
306 |
my $totals = $input->param('totals'); |
312 |
my $totals = $input->param('totals'); |
307 |
my $definition = $input->param('definition'); |
313 |
my $definition = $input->param('definition'); |
308 |
# my @criteria = split( ',', $crit ); |
|
|
309 |
my $query_criteria=$crit; |
314 |
my $query_criteria=$crit; |
310 |
# split the columns up by , |
315 |
# split the columns up by , |
311 |
my @columns = split( ',', $column ); |
316 |
my @columns = split( ',', $column ); |
Lines 377-382
elsif ($phase eq 'Run this report'){
Link Here
|
377 |
my $limit = 20; # page size. # TODO: move to DB or syspref? |
382 |
my $limit = 20; # page size. # TODO: move to DB or syspref? |
378 |
my $offset = 0; |
383 |
my $offset = 0; |
379 |
my $report = $input->param('reports'); |
384 |
my $report = $input->param('reports'); |
|
|
385 |
my @sql_params = $input->param('sql_params'); |
380 |
# offset algorithm |
386 |
# offset algorithm |
381 |
if ($input->param('page')) { |
387 |
if ($input->param('page')) { |
382 |
$offset = ($input->param('page') - 1) * $limit; |
388 |
$offset = ($input->param('page') - 1) * $limit; |
Lines 386-418
elsif ($phase eq 'Run this report'){
Link Here
|
386 |
push @errors, {no_sql_for_id=>$report}; |
392 |
push @errors, {no_sql_for_id=>$report}; |
387 |
} |
393 |
} |
388 |
my @rows = (); |
394 |
my @rows = (); |
389 |
my ($sth, $errors) = execute_query($sql, $offset, $limit); |
395 |
# if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters |
390 |
my $total = select_2_select_count_value($sql) || 0; |
396 |
if ($sql =~ /<</ && !@sql_params) { |
391 |
unless ($sth) { |
397 |
# split on ??. Each odd (2,4,6,...) entry should be a parameter to fill |
392 |
die "execute_query failed to return sth for report $report: $sql"; |
398 |
my @split = split /<<|>>/,$sql; |
|
|
399 |
my @tmpl_parameters; |
400 |
for(my $i=0;$i<($#split/2);$i++) { |
401 |
my ($text,$authorised_value) = split /\|/,$split[$i*2+1]; |
402 |
my $input; |
403 |
if ($authorised_value) { |
404 |
my $dbh=C4::Context->dbh; |
405 |
my @authorised_values; |
406 |
my %authorised_lib; |
407 |
# builds list, depending on authorised value... |
408 |
if ( $authorised_value eq "branches" ) { |
409 |
my $branches = GetBranchesLoop(); |
410 |
foreach my $thisbranch (@$branches) { |
411 |
push @authorised_values, $thisbranch->{value}; |
412 |
$authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname}; |
413 |
} |
414 |
} |
415 |
elsif ( $authorised_value eq "itemtypes" ) { |
416 |
my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description"); |
417 |
$sth->execute; |
418 |
while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) { |
419 |
push @authorised_values, $itemtype; |
420 |
$authorised_lib{$itemtype} = $description; |
421 |
} |
422 |
} |
423 |
elsif ( $authorised_value eq "cn_source" ) { |
424 |
my $class_sources = GetClassSources(); |
425 |
my $default_source = C4::Context->preference("DefaultClassificationSource"); |
426 |
foreach my $class_source (sort keys %$class_sources) { |
427 |
next unless $class_sources->{$class_source}->{'used'} or |
428 |
($class_source eq $default_source); |
429 |
push @authorised_values, $class_source; |
430 |
$authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'}; |
431 |
} |
432 |
} |
433 |
elsif ( $authorised_value eq "categorycode" ) { |
434 |
my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description"); |
435 |
$sth->execute; |
436 |
while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) { |
437 |
push @authorised_values, $categorycode; |
438 |
$authorised_lib{$categorycode} = $description; |
439 |
} |
440 |
|
441 |
#---- "true" authorised value |
442 |
} |
443 |
else { |
444 |
my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib"); |
445 |
|
446 |
$authorised_values_sth->execute( $authorised_value); |
447 |
|
448 |
while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { |
449 |
push @authorised_values, $value; |
450 |
$authorised_lib{$value} = $lib; |
451 |
# For item location, we show the code and the libelle |
452 |
$authorised_lib{$value} = $lib; |
453 |
} |
454 |
} |
455 |
$input =CGI::scrolling_list( # FIXME: factor out scrolling_list |
456 |
-name => "sql_params", |
457 |
-values => \@authorised_values, |
458 |
# -default => $value, |
459 |
-labels => \%authorised_lib, |
460 |
-override => 1, |
461 |
-size => 1, |
462 |
-multiple => 0, |
463 |
-tabindex => 1, |
464 |
); |
465 |
|
466 |
} else { |
467 |
$input = "<input type='text' name='sql_params'/>"; |
468 |
} |
469 |
push @tmpl_parameters, {'entry' => $text, 'input' => $input }; |
470 |
} |
471 |
$template->param('sql' => $sql, |
472 |
'name' => $name, |
473 |
'sql_params' => \@tmpl_parameters, |
474 |
'enter_params' => 1, |
475 |
'reports' => $report, |
476 |
); |
393 |
} else { |
477 |
} else { |
394 |
my $headref = $sth->{NAME} || []; |
478 |
# OK, we have parameters, or there are none, we run the report |
395 |
my @headers = map { +{ cell => $_ } } @$headref; |
479 |
# if there were parameters, replace before running |
396 |
$template->param(header_row => \@headers); |
480 |
# split on ??. Each odd (2,4,6,...) entry should be a parameter to fill |
397 |
while (my $row = $sth->fetchrow_arrayref()) { |
481 |
my @split = split /<<|>>/,$sql; |
398 |
my @cells = map { +{ cell => $_ } } @$row; |
482 |
my @tmpl_parameters; |
399 |
push @rows, { cells => \@cells }; |
483 |
for(my $i=0;$i<$#split/2;$i++) { |
|
|
484 |
my $quoted = C4::Context->dbh->quote($sql_params[$i]); |
485 |
# if there are special regexp chars, we must \ them |
486 |
$split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; |
487 |
$sql =~ s/<<$split[$i*2+1]>>/$quoted/; |
488 |
} |
489 |
my ($sth, $errors) = execute_query($sql, $offset, $limit); |
490 |
my $total = select_2_select_count_value($sql) || 0; |
491 |
unless ($sth) { |
492 |
die "execute_query failed to return sth for report $report: $sql"; |
493 |
} else { |
494 |
my $headref = $sth->{NAME} || []; |
495 |
my @headers = map { +{ cell => $_ } } @$headref; |
496 |
$template->param(header_row => \@headers); |
497 |
while (my $row = $sth->fetchrow_arrayref()) { |
498 |
my @cells = map { +{ cell => $_ } } @$row; |
499 |
push @rows, { cells => \@cells }; |
500 |
} |
400 |
} |
501 |
} |
401 |
} |
|
|
402 |
|
502 |
|
403 |
my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); |
503 |
my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); |
404 |
my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report"; |
504 |
my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report"; |
405 |
$template->param( |
505 |
$template->param( |
406 |
'results' => \@rows, |
506 |
'results' => \@rows, |
407 |
'sql' => $sql, |
507 |
'sql' => $sql, |
408 |
'execute' => 1, |
508 |
'execute' => 1, |
409 |
'name' => $name, |
509 |
'name' => $name, |
410 |
'notes' => $notes, |
510 |
'notes' => $notes, |
411 |
'errors' => $errors, |
511 |
'errors' => $errors, |
412 |
'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), |
512 |
'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), |
413 |
'unlimited_total' => $total, |
513 |
'unlimited_total' => $total, |
414 |
); |
514 |
); |
415 |
} |
515 |
} |
|
|
516 |
} |
416 |
|
517 |
|
417 |
elsif ($phase eq 'Export'){ |
518 |
elsif ($phase eq 'Export'){ |
418 |
binmode STDOUT, ':utf8'; |
519 |
binmode STDOUT, ':utf8'; |
419 |
- |
|
|