|
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 |
- |
|
|