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

(-)a/C4/Reports/Guided.pm (-24 / +10 lines)
Lines 43-49 BEGIN { Link Here
43
	    save_report get_saved_reports execute_query get_saved_report create_compound run_compound
43
	    save_report get_saved_reports execute_query get_saved_report create_compound run_compound
44
		get_column_type get_distinct_values save_dictionary get_from_dictionary
44
		get_column_type get_distinct_values save_dictionary get_from_dictionary
45
		delete_definition delete_report format_results get_sql
45
		delete_definition delete_report format_results get_sql
46
        select_2_select_count_value update_sql
46
        nb_rows update_sql
47
	);
47
	);
48
}
48
}
49
49
Lines 373-378 sub get_criteria { Link Here
373
    return ( \@criteria_array );
373
    return ( \@criteria_array );
374
}
374
}
375
375
376
sub nb_rows($) {
377
    my $sql = shift or return;
378
    my $sth = C4::Context->dbh->prepare($sql);
379
    $sth->execute();
380
    while ($sth->fetchrow()) {
381
    }
382
    return $sth->rows;
383
}
384
376
=item execute_query
385
=item execute_query
377
386
378
  ($results, $total, $error) = execute_query($sql, $offset, $limit)
387
  ($results, $total, $error) = execute_query($sql, $offset, $limit)
Lines 399-427 the user in a user-supplied SQL query WILL apply in any case. Link Here
399
#  ~ remove any LIMIT clause
408
#  ~ remove any LIMIT clause
400
#  ~ repace SELECT clause w/ SELECT count(*)
409
#  ~ repace SELECT clause w/ SELECT count(*)
401
410
402
sub select_2_select_count_value ($) {
403
    my $sql = shift or return;
404
    my $countsql = select_2_select_count($sql);
405
    $debug and warn "original query: $sql\ncount query: $countsql\n";
406
    my $sth1 = C4::Context->dbh->prepare($countsql);
407
    $sth1->execute();
408
    my $total = $sth1->fetchrow();
409
    $debug and warn "total records for this query: $total\n";
410
    return $total;
411
}
412
sub select_2_select_count ($) {
413
    # Modify the query passed in to create a count query... (I think this covers all cases -crn)
414
    my ($sql) = strip_limit(shift) or return;
415
    $sql =~ s/\bSELECT\W+(?:\w+\W+){1,}?FROM\b|\bSELECT\W\*\WFROM\b/SELECT count(*) FROM /ig;
416
    return $sql;
417
}
418
sub strip_limit ($) {
419
    my $sql = shift or return;
420
    ($sql =~ /\bLIMIT\b/i) or return ($sql, 0, undef);
421
    $sql =~ s/\bLIMIT\b\s*(\d+)(\s*\,\s*(\d+))?\s*/ /ig;
422
    return ($sql, (defined $2 ? $1 : 0), (defined $3 ? $3 : $1));   # offset can default to 0, LIMIT cannot!
423
}
424
425
sub execute_query ($;$$$) {
411
sub execute_query ($;$$$) {
426
412
427
    my ( $sql, $offset, $limit, $no_count ) = @_;
413
    my ( $sql, $offset, $limit, $no_count ) = @_;
(-)a/reports/guided_reports.pl (-2 / +1 lines)
Lines 484-490 elsif ($phase eq 'Run this report'){ Link Here
484
            $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
484
            $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
485
        }
485
        }
486
        my ($sth, $errors) = execute_query($sql, $offset, $limit);
486
        my ($sth, $errors) = execute_query($sql, $offset, $limit);
487
        my $total = select_2_select_count_value($sql) || 0;
487
        my $total = nb_rows($sql) || 0;
488
        unless ($sth) {
488
        unless ($sth) {
489
            die "execute_query failed to return sth for report $report: $sql";
489
            die "execute_query failed to return sth for report $report: $sql";
490
        } else {
490
        } else {
491
- 

Return to bug 6099