|
Lines 557-567
and that the number placeholders matches the number of parameters.
Link Here
|
| 557 |
=cut |
557 |
=cut |
| 558 |
|
558 |
|
| 559 |
sub execute_query { |
559 |
sub execute_query { |
| 560 |
|
|
|
| 561 |
my $params = shift; |
560 |
my $params = shift; |
| 562 |
my $sql = $params->{sql}; |
561 |
my $sql = $params->{sql}; |
| 563 |
my $offset = $params->{offset} || 0; |
562 |
my $offset = $params->{offset} || 0; |
| 564 |
my $limit = $params->{limit} || C4::Context->config('report_results_limit') || 999999; |
563 |
my $limit = $params->{limit} || C4::Context->config('report_results_limit') || 999999; |
| 565 |
my $sql_params = defined $params->{sql_params} ? $params->{sql_params} : []; |
564 |
my $sql_params = defined $params->{sql_params} ? $params->{sql_params} : []; |
| 566 |
my $report_id = $params->{report_id}; |
565 |
my $report_id = $params->{report_id}; |
| 567 |
|
566 |
|
|
Lines 573-607
sub execute_query {
Link Here
|
| 573 |
|
572 |
|
| 574 |
Koha::Logger->get->debug("Report - execute_query($sql, $offset, $limit)"); |
573 |
Koha::Logger->get->debug("Report - execute_query($sql, $offset, $limit)"); |
| 575 |
|
574 |
|
| 576 |
my ( $is_sql_valid, $errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid; |
575 |
my ( $is_sql_valid, $errors ) = Koha::Report->new( { savedsql => $sql } )->is_sql_valid; |
| 577 |
return (undef, @{$errors}[0]) unless $is_sql_valid; |
576 |
return ( undef, @{$errors}[0] ) unless $is_sql_valid; |
| 578 |
|
577 |
|
| 579 |
foreach my $sql_param ( @$sql_params ){ |
578 |
foreach my $sql_param (@$sql_params) { |
| 580 |
if ( $sql_param =~ m/\n/ ){ |
579 |
if ( $sql_param =~ m/\n/ ) { |
| 581 |
my @list = split /\n/, $sql_param; |
580 |
my @list = split /\n/, $sql_param; |
| 582 |
my @quoted_list; |
581 |
my @quoted_list; |
| 583 |
foreach my $item ( @list ){ |
582 |
foreach my $item (@list) { |
| 584 |
$item =~ s/\r//; |
583 |
$item =~ s/\r//; |
| 585 |
push @quoted_list, C4::Context->dbh->quote($item); |
584 |
push @quoted_list, C4::Context->dbh->quote($item); |
| 586 |
} |
585 |
} |
| 587 |
$sql_param = "(".join(",",@quoted_list).")"; |
586 |
$sql_param = "(" . join( ",", @quoted_list ) . ")"; |
| 588 |
} |
587 |
} |
| 589 |
} |
588 |
} |
| 590 |
|
589 |
|
| 591 |
my ($useroffset, $userlimit); |
590 |
my ( $useroffset, $userlimit ); |
| 592 |
|
591 |
|
| 593 |
# Grab offset/limit from user supplied LIMIT and drop the LIMIT so we can control pagination |
592 |
# Grab offset/limit from user supplied LIMIT and drop the LIMIT so we can control pagination |
| 594 |
($sql, $useroffset, $userlimit) = strip_limit($sql); |
593 |
( $sql, $useroffset, $userlimit ) = strip_limit($sql); |
| 595 |
|
594 |
|
| 596 |
Koha::Logger->get->debug( |
595 |
Koha::Logger->get->debug( |
| 597 |
sprintf "User has supplied (OFFSET,) LIMIT = %s, %s", |
596 |
sprintf "User has supplied (OFFSET,) LIMIT = %s, %s", |
| 598 |
$useroffset, ( defined($userlimit) ? $userlimit : 'UNDEF' ) ); |
597 |
$useroffset, ( defined($userlimit) ? $userlimit : 'UNDEF' ) |
|
|
598 |
); |
| 599 |
|
599 |
|
| 600 |
$offset += $useroffset; |
600 |
$offset += $useroffset; |
| 601 |
if (defined($userlimit)) { |
601 |
if ( defined($userlimit) ) { |
| 602 |
if ($offset + $limit > $userlimit ) { |
602 |
if ( $offset + $limit > $userlimit ) { |
| 603 |
$limit = $userlimit - $offset; |
603 |
$limit = $userlimit - $offset; |
| 604 |
} elsif ( ! $offset && $limit < $userlimit ) { |
604 |
} elsif ( !$offset && $limit < $userlimit ) { |
| 605 |
$limit = $userlimit; |
605 |
$limit = $userlimit; |
| 606 |
} |
606 |
} |
| 607 |
} |
607 |
} |
|
Lines 611-629
sub execute_query {
Link Here
|
| 611 |
|
611 |
|
| 612 |
$dbh->do( 'UPDATE saved_sql SET last_run = NOW() WHERE id = ?', undef, $report_id ) if $report_id; |
612 |
$dbh->do( 'UPDATE saved_sql SET last_run = NOW() WHERE id = ?', undef, $report_id ) if $report_id; |
| 613 |
|
613 |
|
| 614 |
Koha::Logger->get({ prefix => 0, interface => 'reports', category => 'execute.query'})->info("Report $report_id : $sql, $offset, $limit"); |
614 |
Koha::Logger->get( { prefix => 0, interface => 'reports', category => 'execute.query' } ) |
| 615 |
Koha::Logger->get({ prefix => 0, interface => 'reports', category => 'execute.time.start'})->info("Report starting: $report_id"); |
615 |
->info("Report $report_id : $sql, $offset, $limit"); |
|
|
616 |
Koha::Logger->get( { prefix => 0, interface => 'reports', category => 'execute.time.start' } ) |
| 617 |
->info("Report starting: $report_id"); |
| 616 |
|
618 |
|
| 617 |
my $sth = $dbh->prepare($sql); |
619 |
my $sth = $dbh->prepare($sql); |
| 618 |
eval { |
620 |
eval { $sth->execute( @$sql_params, $offset, $limit ); }; |
| 619 |
$sth->execute(@$sql_params, $offset, $limit); |
|
|
| 620 |
}; |
| 621 |
warn $@ if $@; |
621 |
warn $@ if $@; |
| 622 |
|
622 |
|
| 623 |
Koha::Logger->get({ prefix => 0, interface => 'reports', category => 'execute.time.end'})->info("Report finished: $report_id"); |
623 |
Koha::Logger->get( { prefix => 0, interface => 'reports', category => 'execute.time.end' } ) |
|
|
624 |
->info("Report finished: $report_id"); |
| 624 |
|
625 |
|
| 625 |
return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err); |
626 |
return ( $sth, { queryerr => $sth->errstr } ) if ( $sth->err ); |
| 626 |
return ( $sth ); |
627 |
return ($sth); |
| 627 |
} |
628 |
} |
| 628 |
|
629 |
|
| 629 |
=head2 save_report($sql,$name,$type,$notes) |
630 |
=head2 save_report($sql,$name,$type,$notes) |
| 630 |
- |
|
|