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

(-)a/C4/Reports/Guided.pm (-4 / +21 lines)
Lines 75-91 $keys{'5'} = ['borrowers.borrowernumber=accountlines.borrowernumber']; Link Here
75
# have to do someting here to know if its dropdown, free text, date etc
75
# have to do someting here to know if its dropdown, free text, date etc
76
76
77
our %criteria;
77
our %criteria;
78
# reports on circulation
78
$criteria{'1'} = [
79
$criteria{'1'} = [
79
    'statistics.type',   'borrowers.categorycode',
80
    'statistics.type',   'borrowers.categorycode',
80
    'statistics.branch',
81
    'statistics.branch',
81
    'biblioitems.publicationyear|date',
82
    'biblioitems.publicationyear|date',
82
    'items.dateaccessioned|date'
83
    'items.dateaccessioned|date'
83
];
84
];
85
# reports on catalogue
84
$criteria{'2'} =
86
$criteria{'2'} =
85
  [ 'items.itemnumber|textrange', 'items.biblionumber|textrange', 'items.barcode|textrange', 'biblio.frameworkcode', 'items.holdingbranch', 'items.homebranch', 'biblio.datecreated|daterange', 'biblio.timestamp|daterange', 'items.onloan|daterange', 'items.ccode', 'items.itemcallnumber|textrange', 'items.itype', 'items.itemlost', 'items.location' ];
87
  [ 'items.itemnumber|textrange',   'items.biblionumber|textrange',   'items.barcode|textrange', 
86
$criteria{'3'} = ['borrowers.branchcode'];
88
    'biblio.frameworkcode',         'items.holdingbranch',            'items.homebranch', 
89
  'biblio.datecreated|daterange',   'biblio.timestamp|daterange',     'items.onloan|daterange', 
90
  'items.ccode',                    'items.itemcallnumber|textrange', 'items.itype', 
91
  'items.itemlost',                 'items.location' ];
92
# reports on borrowers
93
$criteria{'3'} = ['borrowers.branchcode', 'borrowers.categorycode'];
94
# reports on acquisition
87
$criteria{'4'} = ['aqorders.datereceived|date'];
95
$criteria{'4'} = ['aqorders.datereceived|date'];
88
$criteria{'5'} = ['borrowers.branchcode'];
96
97
# reports on accounting
98
$criteria{'5'} = ['borrowers.branchcode', 'borrowers.categorycode'];
89
99
90
if (C4::Context->preference('item-level_itypes')) {
100
if (C4::Context->preference('item-level_itypes')) {
91
    unshift @{ $criteria{'1'} }, 'items.itype';
101
    unshift @{ $criteria{'1'} }, 'items.itype';
Lines 244-250 sub _build_query { Link Here
244
    my $dbh           = C4::Context->dbh();
254
    my $dbh           = C4::Context->dbh();
245
    my $joinedtables  = join( ',', @$tables );
255
    my $joinedtables  = join( ',', @$tables );
246
    my $joinedcolumns = join( ',', @$columns );
256
    my $joinedcolumns = join( ',', @$columns );
247
    my $joinedkeys    = join( ' AND ', @$keys );
248
    my $query =
257
    my $query =
249
      "SELECT $totals $joinedcolumns FROM $tables->[0] ";
258
      "SELECT $totals $joinedcolumns FROM $tables->[0] ";
250
	for (my $i=1;$i<@$tables;$i++){
259
	for (my $i=1;$i<@$tables;$i++){
Lines 341-346 sub get_criteria { Link Here
341
		my $sth = $dbh->prepare($query);
350
		my $sth = $dbh->prepare($query);
342
		$sth->execute();
351
		$sth->execute();
343
		my @values;
352
		my @values;
353
        # push the runtime choosing option
354
        my $list;
355
        $list='branches' if $column eq 'branchcode' or $column eq 'holdingbranch' or $column eq 'homebranch';
356
        $list='categorycode' if $column eq 'categorycode';
357
        $list='itemtype' if $column eq 'itype';
358
        $list='ccode' if $column eq 'ccode';
359
        # TODO : improve to let the librarian choose the description at runtime
360
        push @values, { availablevalues => "<<$column".($list?"|$list":'').">>" };
344
		while ( my $row = $sth->fetchrow_hashref() ) {
361
		while ( my $row = $sth->fetchrow_hashref() ) {
345
		    push @values, $row;
362
		    push @values, $row;
346
		    if ($row->{'availablevalues'} eq '') { $row->{'default'} = 1 };
363
		    if ($row->{'availablevalues'} eq '') { $row->{'default'} = 1 };
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/help/reports/guided_reports.tmpl (-1 / +13 lines)
Lines 20-26 Link Here
20
</ul>
20
</ul>
21
21
22
<p><strong>Step 2: Pick a Report Type</strong></p>
22
<p><strong>Step 2: Pick a Report Type</strong></p>
23
<li style="color: #990000">IMPORTANT: Only Tabular should be chosen with this option</p>
23
<li style="color: #990000">IMPORTANT: Only Tabular should be chosen with this option</p></li>
24
<p><strong>Step 3: Select Columns for Display</strong></p>
24
<p><strong>Step 3: Select Columns for Display</strong></p>
25
<p><strong>Step 4: Select Criteria to Limit</strong></p>
25
<p><strong>Step 4: Select Criteria to Limit</strong></p>
26
<p>This is optional, if you don't want to limit results by any one field, just leave this section blank.</p>
26
<p>This is optional, if you don't want to limit results by any one field, just leave this section blank.</p>
Lines 53-57 Link Here
53
53
54
<p>NEKLS also has a great page (<a href="http://www.nexpresslibrary.org/training/reports-training/" target="_new">http://www.nexpresslibrary.org/training/reports-training/</a>) with a list of reports they have written.</p>
54
<p>NEKLS also has a great page (<a href="http://www.nexpresslibrary.org/training/reports-training/" target="_new">http://www.nexpresslibrary.org/training/reports-training/</a>) with a list of reports they have written.</p>
55
55
56
<p><strong>Can I have run-time defined parameters?</strong></p>
57
58
<p>Yes, you can: there is a specific syntax that Koha will understand as 'ask for values when running the report'. The syntax is &lt;&lt;Question to ask|authorised_value&gt;&gt;.</p>
59
<ul>
60
<li>The &lt;&lt; and &gt;&gt; are just delimiters. You must put &lt;&lt; at the beginning and &gt;&gt; at the end of your parameter</li>
61
<li>The <strong>Question to ask</strong> will be displayed on the left of the string to enter. 
62
<li>The <strong>authorised_value</strong> can be omitted if not applicable. If it contains an authorised value category, or <strong>branches</strong> or <strong>itemtype</strong> or <strong>categorycode</strong>, a list with the Koha authorised values will be displayed instead of a free field</li>
63
</ul>
64
<p>Note that you can have more than one parameter in a given SQL</p>
65
<p>Note that entering nothing at run time won't probably work as you expect. It will be considered as "value empty" not as "ignore this parameter". For example entering nothing for : "title=&lt;&lt;Enter title&gt;&gt;" will display results with title='' (no title). If you want to have to have something not mandatory, use "title like &lt;&lt;Enter title&gt;&gt;" and enter a % at run time instead of nothing</p>
66
<p>Sample :</p>
67
<ul><li>SELECT surname,firstname FROM borrowers WHERE branchcode=&lt;&lt;Enter patrons library|branches&gt;&gt; AND surname like &lt;&lt;Enter filter for patron surname (% if none)&gt;&gt;</li></ul>
56
68
57
<!-- TMPL_INCLUDE NAME="help-bottom.inc" -->
69
<!-- TMPL_INCLUDE NAME="help-bottom.inc" -->
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl (-3 / +17 lines)
Lines 294-300 canned reports and writing custom SQL reports.</p> Link Here
294
            <td>
294
            <td>
295
                <select name="<!-- TMPL_VAR NAME="name" -->_value">
295
                <select name="<!-- TMPL_VAR NAME="name" -->_value">
296
                <!-- TMPL_LOOP NAME="values" -->
296
                <!-- TMPL_LOOP NAME="values" -->
297
                <option value="<!-- TMPL_VAR NAME="availablevalues"-->"><!-- TMPL_IF name="default" -->Default<!-- TMPL_ELSE --><!-- TMPL_VAR NAME="availablevalues" --><!-- /TMPL_IF --></option>
297
                <option value="<!-- TMPL_VAR NAME="availablevalues"-->"><!-- TMPL_IF name="default" -->Default<!-- TMPL_ELSE --><!-- TMPL_VAR NAME="availablevalues" ESCAPE="HTML"--><!-- /TMPL_IF --></option>
298
                <!-- /TMPL_LOOP -->
298
                <!-- /TMPL_LOOP -->
299
                </select>
299
                </select>
300
            </td>
300
            </td>
Lines 390-400 NAME="name" -->"><!-- TMPL_VAR NAME="name"--></label></td><td> Link Here
390
<h1>Confirm Custom Report</h1>
390
<h1>Confirm Custom Report</h1>
391
<p>Your report will be generated with the following SQL statement.</p>
391
<p>Your report will be generated with the following SQL statement.</p>
392
<p> 
392
<p> 
393
<!-- TMPL_VAR NAME="sql" -->
393
<!-- TMPL_VAR NAME="sql" ESCAPE="HTML" -->
394
</p>
394
</p>
395
395
396
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
396
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
397
<input type="hidden" name="sql" value="<!-- TMPL_VAR NAME="sql" ESCAPE="html" -->" />
397
<input type="hidden" name="sql" value="<!-- TMPL_VAR NAME="sql" -->" />
398
<input type="hidden" name="type" value="<!-- TMPL_VAR NAME="type" -->" />
398
<input type="hidden" name="type" value="<!-- TMPL_VAR NAME="type" -->" />
399
<p>You will need to save the report before you can execute it</p>
399
<p>You will need to save the report before you can execute it</p>
400
<fieldset class="action"><input type="hidden" name="phase" value="Save" />  
400
<fieldset class="action"><input type="hidden" name="phase" value="Save" />  
Lines 417-426 NAME="name" -->"><!-- TMPL_VAR NAME="name"--></label></td><td> Link Here
417
</form>
417
</form>
418
<!-- /TMPL_IF -->
418
<!-- /TMPL_IF -->
419
419
420
<!-- TMPL_IF name="enter_params" -->
421
    <form action='/cgi-bin/koha/reports/guided_reports.pl'>
422
        <input type='hidden' name='phase' value='Run this report' />
423
        <input type='hidden' name='reports' value="<!--TMPL_VAR name='reports' -->" />
424
        <h1>Enter parameters for report <!-- TMPL_VAR name="name" -->:</h1>
425
        <!-- TMPL_IF NAME="notes" --><p><!-- TMPL_VAR NAME="notes" --></p><!-- /TMPL_IF -->
426
        <!-- TMPL_LOOP name="sql_params" -->
427
            <p><!-- TMPL_VAR name="entry" -->: <!-- TMPL_VAR name='input' --></p>
428
        <!-- /TMPL_LOOP -->
429
        <input type='submit' value='Run the report'/>
430
    </form>
431
<!-- /TMPL_IF -->
432
420
<!-- TMPL_IF NAME="execute" -->
433
<!-- TMPL_IF NAME="execute" -->
421
<h1><!-- TMPL_VAR NAME="name" --></h1>
434
<h1><!-- TMPL_VAR NAME="name" --></h1>
422
<!-- TMPL_IF NAME="notes" --><p><!-- TMPL_VAR NAME="notes" --></p><!-- /TMPL_IF -->
435
<!-- TMPL_IF NAME="notes" --><p><!-- TMPL_VAR NAME="notes" --></p><!-- /TMPL_IF -->
423
<!-- TMPL_IF NAME="unlimited_total" --><p>Total number of rows matching the (unlimited) query is <!-- TMPL_VAR NAME="unlimited_total" -->.</p><!-- /TMPL_IF -->
436
<!-- TMPL_IF NAME="unlimited_total" --><p>Total number of rows matching the (unlimited) query is <!-- TMPL_VAR NAME="unlimited_total" -->.</p><!-- /TMPL_IF -->
437
<!-- TMPL_VAR name="sql" -->
424
<!-- TMPL_VAR NAME='pagination_bar' DEFAULT="" -->
438
<!-- TMPL_VAR NAME='pagination_bar' DEFAULT="" -->
425
<!-- TMPL_UNLESS name="errors" -->
439
<!-- TMPL_UNLESS name="errors" -->
426
<table>
440
<table>
(-)a/reports/guided_reports.pl (-28 / +128 lines)
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
- 

Return to bug 3929