View | Details | Raw Unified | Return to bug 26669
Collapse All | Expand All

(-)a/C4/Reports/Guided.pm (-6 / +8 lines)
Lines 541-557 sub strip_limit { Link Here
541
541
542
sub execute_query {
542
sub execute_query {
543
543
544
    my ( $sql, $offset, $limit, $sql_params, $report_id ) = @_;
544
    my $params     = shift;
545
545
    my $sql        = $params->{sql};
546
    $sql_params = [] unless defined $sql_params;
546
    my $offset     = $params->{offset} || 0;
547
    my $limit      = $params->{limit}  || 999999;
548
    my $sql_params = defined $params->{sql_params} ? $params->{sql_params} : [];
549
    my $report_id  = $params->{report_id};
547
550
548
    # check parameters
551
    # check parameters
549
    unless ($sql) {
552
    unless ($sql) {
550
        carp "execute_query() called without SQL argument";
553
        carp "execute_query() called without SQL argument";
551
        return;
554
        return;
552
    }
555
    }
553
    $offset = 0    unless $offset;
554
    $limit  = 999999 unless $limit;
555
556
556
    Koha::Logger->get->debug("Report - execute_query($sql, $offset, $limit)");
557
    Koha::Logger->get->debug("Report - execute_query($sql, $offset, $limit)");
557
558
Lines 1053-1059 sub EmailReport { Link Here
1053
    my $sql = $report->savedsql;
1054
    my $sql = $report->savedsql;
1054
    return ( { FATAL => "NO_REPORT" } ) unless $sql;
1055
    return ( { FATAL => "NO_REPORT" } ) unless $sql;
1055
1056
1056
    my ( $sth, $errors ) = execute_query( $sql ); #don't pass offset or limit, hardcoded limit of 999,999 will be used
1057
    #don't pass offset or limit, hardcoded limit of 999,999 will be used
1058
    my ( $sth, $errors ) = execute_query( { sql => $sql, report_id => $report_id } );
1057
    return ( undef, [{ FATAL => "REPORT_FAIL" }] ) if $errors;
1059
    return ( undef, [{ FATAL => "REPORT_FAIL" }] ) if $errors;
1058
1060
1059
    my $counter = 1;
1061
    my $counter = 1;
(-)a/misc/cronjobs/runreport.pl (-1 / +7 lines)
Lines 262-268 foreach my $report_id (@ARGV) { Link Here
262
    my $params_needed = ( $sql =~ s/(<<[^>]+>>)/\?/g );
262
    my $params_needed = ( $sql =~ s/(<<[^>]+>>)/\?/g );
263
    die("You supplied ". scalar @params . " parameter(s) and $params_needed are required by the report") if scalar @params != $params_needed;
263
    die("You supplied ". scalar @params . " parameter(s) and $params_needed are required by the report") if scalar @params != $params_needed;
264
264
265
    my ($sth) = execute_query( $sql, undef, undef, \@params, $report_id );
265
    my ($sth) = execute_query(
266
        {
267
            sql        => $sql,
268
            sql_params => \@params,
269
            report_id  => $report_id,
270
        }
271
    );
266
    my $count = scalar($sth->rows);
272
    my $count = scalar($sth->rows);
267
    unless ($count) {
273
    unless ($count) {
268
        print "NO OUTPUT: 0 results from execute_query\n";
274
        print "NO OUTPUT: 0 results from execute_query\n";
(-)a/opac/svc/report (-3 / +8 lines)
Lines 58-70 if ($cache_active) { Link Here
58
}
58
}
59
59
60
unless ($json_text) {
60
unless ($json_text) {
61
    my $offset = 0;
62
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
61
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
63
62
64
    my ( $sql, undef ) = $report_rec->prep_report( \@param_names, \@sql_params );
63
    my ( $sql, undef ) = $report_rec->prep_report( \@param_names, \@sql_params );
65
64
66
    my ( $sth, $errors ) =
65
    my ( $sth, $errors ) = execute_query(
67
      execute_query( $sql, $offset, $limit, undef, $report_id );
66
        {
67
            sql        => $sql,
68
            offset     => 0,
69
            limit      => $limit,
70
            report_id  => $report_id,
71
        }
72
    );
68
    if ($sth) {
73
    if ($sth) {
69
        my $lines;
74
        my $lines;
70
        if ($report_annotation) {
75
        if ($report_annotation) {
(-)a/reports/guided_reports.pl (-3 / +10 lines)
Lines 845-851 elsif ($phase eq 'Run this report'){ Link Here
845
        } else {
845
        } else {
846
            my ($sql,$header_types) = $report->prep_report( \@param_names, \@sql_params );
846
            my ($sql,$header_types) = $report->prep_report( \@param_names, \@sql_params );
847
            $template->param(header_types => $header_types);
847
            $template->param(header_types => $header_types);
848
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
848
            my ( $sth, $errors ) = execute_query(
849
                {
850
                    sql        => $sql,
851
                    offset     => $offset,
852
                    limit      => $limit,
853
                    report_id  => $report_id,
854
                }
855
            );
849
            my $total;
856
            my $total;
850
            if (!$sth) {
857
            if (!$sth) {
851
                die "execute_query failed to return sth for report $report_id: $sql";
858
                die "execute_query failed to return sth for report $report_id: $sql";
Lines 858-864 elsif ($phase eq 'Run this report'){ Link Here
858
                    push @rows, { cells => \@cells };
865
                    push @rows, { cells => \@cells };
859
                }
866
                }
860
                if( $want_full_chart ){
867
                if( $want_full_chart ){
861
                    my ($sth2, $errors2) = execute_query($sql);
868
                    my ( $sth2, $errors2 ) = execute_query( { sql => $sql, report_id => $report_id } );
862
                    while (my $row = $sth2->fetchrow_arrayref()) {
869
                    while (my $row = $sth2->fetchrow_arrayref()) {
863
                        my @cells = map { +{ cell => $_ } } @$row;
870
                        my @cells = map { +{ cell => $_ } } @$row;
864
                        push @allrows, { cells => \@cells };
871
                        push @allrows, { cells => \@cells };
Lines 912-918 elsif ($phase eq 'Export'){ Link Here
912
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
919
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
913
920
914
    ($sql, undef) = $report->prep_report( \@param_names, \@sql_params );
921
    ($sql, undef) = $report->prep_report( \@param_names, \@sql_params );
915
	my ($sth, $q_errors) = execute_query($sql);
922
    my ( $sth, $q_errors ) = execute_query( { sql => $sql, report_id => $report_id } );
916
    unless ($q_errors and @$q_errors) {
923
    unless ($q_errors and @$q_errors) {
917
        my ( $type, $content );
924
        my ( $type, $content );
918
        if ($format eq 'tab') {
925
        if ($format eq 'tab') {
(-)a/svc/report (-2 / +8 lines)
Lines 61-72 if ($cache_active) { Link Here
61
}
61
}
62
62
63
unless ($json_text) {
63
unless ($json_text) {
64
    my $offset = 0;
65
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
64
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
66
65
67
    my ( $sql, undef ) = $report_rec->prep_report( \@param_names, \@sql_params );
66
    my ( $sql, undef ) = $report_rec->prep_report( \@param_names, \@sql_params );
68
67
69
    my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
68
    my ( $sth, $errors ) = execute_query(
69
        {
70
            sql        => $sql,
71
            offset     => 0,
72
            limit      => $limit,
73
            report_id  => $report_id,
74
        }
75
    );
70
    if ($sth) {
76
    if ($sth) {
71
        my $lines;
77
        my $lines;
72
        if ($report_annotation) {
78
        if ($report_annotation) {
(-)a/t/db_dependent/Reports/Guided.t (-13 / +27 lines)
Lines 241-256 subtest 'get_saved_reports' => sub { Link Here
241
    is( scalar @{ get_saved_reports() },
241
    is( scalar @{ get_saved_reports() },
242
        $count, "Report2 and report3 have been deleted" );
242
        $count, "Report2 and report3 have been deleted" );
243
243
244
    my $sth = execute_query('SELECT COUNT(*) FROM systempreferences', 0, 10);
244
    my $sth = execute_query(
245
        {
246
            sql    => 'SELECT COUNT(*) FROM systempreferences',
247
            offset => 0,
248
            limit  => 10,
249
        }
250
    );
245
    my $results = $sth->fetchall_arrayref;
251
    my $results = $sth->fetchall_arrayref;
246
    is(scalar @$results, 1, 'running a query returned a result');
252
    is(scalar @$results, 1, 'running a query returned a result');
247
253
248
    my $version = C4::Context->preference('Version');
254
    my $version = C4::Context->preference('Version');
249
    $sth = execute_query(
255
    $sth = execute_query(
250
        'SELECT value FROM systempreferences WHERE variable = ?',
256
        {
251
        0,
257
            sql        => 'SELECT value FROM systempreferences WHERE variable = ?',
252
        10,
258
            offset     => 0,
253
        [ 'Version' ],
259
            limit      => 10,
260
            sql_params => ['Version'],
261
        }
254
    );
262
    );
255
    $results = $sth->fetchall_arrayref;
263
    $results = $sth->fetchall_arrayref;
256
    is_deeply(
264
    is_deeply(
Lines 261-271 subtest 'get_saved_reports' => sub { Link Here
261
269
262
    # for next test, we want to let execute_query capture any SQL errors
270
    # for next test, we want to let execute_query capture any SQL errors
263
    my $errors;
271
    my $errors;
264
    warning_like {local $dbh->{RaiseError} = 0; ($sth, $errors) = execute_query(
272
    warning_like {
265
            'SELECT surname FRM borrowers',  # error in the query is intentional
273
        local $dbh->{RaiseError} = 0;
266
            0, 10 ) }
274
        ( $sth, $errors ) = execute_query(
267
            qr/DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
275
            {
268
            "Wrong SQL syntax raises warning";
276
                sql    => 'SELECT surname FRM borrowers',    # error in the query is intentional
277
                offset => 0,
278
                limit  => 10,
279
            }
280
        )
281
    }
282
    qr/DBD::mysql::st execute failed: You have an error in your SQL syntax;/,
283
      "Wrong SQL syntax raises warning";
269
    ok(
284
    ok(
270
        defined($errors) && exists($errors->{queryerr}),
285
        defined($errors) && exists($errors->{queryerr}),
271
        'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
286
        'attempting to run a report with an SQL syntax error returns error message (Bug 12214)'
Lines 290-303 subtest 'Ensure last_run is populated' => sub { Link Here
290
305
291
    is( $report->last_run, undef, 'Newly created report has null last_run ' );
306
    is( $report->last_run, undef, 'Newly created report has null last_run ' );
292
307
293
    execute_query( $report->savedsql, undef, undef, undef, $report->id );
308
    execute_query( { sql => $report->savedsql, report_id => $report->id } );
294
    $report->discard_changes();
309
    $report->discard_changes();
295
310
296
    isnt( $report->last_run, undef, 'First run of report populates last_run' );
311
    isnt( $report->last_run, undef, 'First run of report populates last_run' );
297
312
298
    my $previous_last_run = $report->last_run;
313
    my $previous_last_run = $report->last_run;
299
    sleep(1); # last_run is stored to the second, so we need to ensure at least one second has passed between runs
314
    sleep(1); # last_run is stored to the second, so we need to ensure at least one second has passed between runs
300
    execute_query( $report->savedsql, undef, undef, undef, $report->id );
315
    execute_query( { sql => $report->savedsql, report_id => $report->id } );
301
    $report->discard_changes();
316
    $report->discard_changes();
302
317
303
    isnt( $report->last_run, $previous_last_run, 'Second run of report updates last_run' );
318
    isnt( $report->last_run, $previous_last_run, 'Second run of report updates last_run' );
304
- 

Return to bug 26669