From a44ca166cd309aa6bde7e4b4401bca72c9ffb534 Mon Sep 17 00:00:00 2001 From: Paul Poulain Date: Tue, 15 Dec 2009 19:47:12 +0100 Subject: [PATCH] Improved guided reports Content-Type: text/plain; charset="utf-8" added runtime parameters for guided reports. Online help: Can I have run-time defined parameters? Yes, you can: there is a specific syntax that Koha will understand as 'ask for values when running the report'. The syntax is <<Question to ask|authorised_value>>. The << and >> are just delimiters. You must put << at the beginning and >> at the end of your parameter The Question to ask will be displayed on the left of the string to enter. The authorised_value can be omitted if not applicable. If it contains an authorised value category, or branches or itemtype or categorycode, a list with the Koha authorised values will be displayed instead of a free field Note that you can have more than one parameter in a given SQL 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=<<Enter title>>" will display results with title='' (no title). If you want to have to have something not mandatory, use "title like <<Enter title>>" and enter a % at run time instead of nothing Sample : SELECT surname,firstname FROM borrowers WHERE branchcode=<<Enter patrons library|branches>> AND surname like <<Enter filter for patron surname (% if none)>> --- C4/Reports/Guided.pm | 25 +++- .../en/modules/help/reports/guided_reports.tmpl | 14 ++- .../en/modules/reports/guided_reports_start.tmpl | 20 ++- reports/guided_reports.pl | 155 ++++++++++++++++---- 4 files changed, 179 insertions(+), 35 deletions(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index a7d6f48..57c2958 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -75,17 +75,27 @@ $keys{'5'} = ['borrowers.borrowernumber=accountlines.borrowernumber']; # have to do someting here to know if its dropdown, free text, date etc our %criteria; +# reports on circulation $criteria{'1'} = [ 'statistics.type', 'borrowers.categorycode', 'statistics.branch', 'biblioitems.publicationyear|date', 'items.dateaccessioned|date' ]; +# reports on catalogue $criteria{'2'} = - [ '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' ]; -$criteria{'3'} = ['borrowers.branchcode']; + [ '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' ]; +# reports on borrowers +$criteria{'3'} = ['borrowers.branchcode', 'borrowers.categorycode']; +# reports on acquisition $criteria{'4'} = ['aqorders.datereceived|date']; -$criteria{'5'} = ['borrowers.branchcode']; + +# reports on accounting +$criteria{'5'} = ['borrowers.branchcode', 'borrowers.categorycode']; if (C4::Context->preference('item-level_itypes')) { unshift @{ $criteria{'1'} }, 'items.itype'; @@ -244,7 +254,6 @@ sub _build_query { my $dbh = C4::Context->dbh(); my $joinedtables = join( ',', @$tables ); my $joinedcolumns = join( ',', @$columns ); - my $joinedkeys = join( ' AND ', @$keys ); my $query = "SELECT $totals $joinedcolumns FROM $tables->[0] "; for (my $i=1;$i<@$tables;$i++){ @@ -341,6 +350,14 @@ sub get_criteria { my $sth = $dbh->prepare($query); $sth->execute(); my @values; + # push the runtime choosing option + my $list; + $list='branches' if $column eq 'branchcode' or $column eq 'holdingbranch' or $column eq 'homebranch'; + $list='categorycode' if $column eq 'categorycode'; + $list='itemtype' if $column eq 'itype'; + $list='ccode' if $column eq 'ccode'; + # TODO : improve to let the librarian choose the description at runtime + push @values, { availablevalues => "<<$column".($list?"|$list":'').">>" }; while ( my $row = $sth->fetchrow_hashref() ) { push @values, $row; if ($row->{'availablevalues'} eq '') { $row->{'default'} = 1 }; diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/help/reports/guided_reports.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/help/reports/guided_reports.tmpl index 55dc498..61a954d 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/help/reports/guided_reports.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/help/reports/guided_reports.tmpl @@ -20,7 +20,7 @@

Step 2: Pick a Report Type

-
  • IMPORTANT: Only Tabular should be chosen with this option

    +
  • IMPORTANT: Only Tabular should be chosen with this option

  • Step 3: Select Columns for Display

    Step 4: Select Criteria to Limit

    This is optional, if you don't want to limit results by any one field, just leave this section blank.

    @@ -53,5 +53,17 @@

    NEKLS also has a great page (http://www.nexpresslibrary.org/training/reports-training/) with a list of reports they have written.

    +

    Can I have run-time defined parameters?

    + +

    Yes, you can: there is a specific syntax that Koha will understand as 'ask for values when running the report'. The syntax is <<Question to ask|authorised_value>>.

    + +

    Note that you can have more than one parameter in a given SQL

    +

    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=<<Enter title>>" will display results with title='' (no title). If you want to have to have something not mandatory, use "title like <<Enter title>>" and enter a % at run time instead of nothing

    +

    Sample :

    + \ No newline at end of file diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl index 70fa0bf..731b866 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl @@ -294,7 +294,7 @@ canned reports and writing custom SQL reports.

    @@ -390,11 +390,11 @@ NAME="name" -->">

    Confirm Custom Report

    Your report will be generated with the following SQL statement.

    - +

    -" /> +" /> " />

    You will need to save the report before you can execute it

    @@ -417,10 +417,24 @@ NAME="name" -->"> + +
    + + +

    Enter parameters for report :

    +

    + +

    :

    + + +
    + +

    Total number of rows matching the (unlimited) query is .

    + diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index e42974d..24b7878 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -26,6 +26,7 @@ use C4::Auth; use C4::Output; use C4::Dates; use C4::Debug; +use C4::Branch; # XXX subfield_is_koha_internal_p =head1 NAME @@ -226,7 +227,12 @@ elsif ( $phase eq 'Choose these criteria' ) { if ($value =~ C4::Dates->regexp('syspref')) { $value = C4::Dates->new($value)->output("iso"); } - $query_criteria .= " AND $crit='$value'"; + # don't escape runtime parameters, they'll be at runtime + if ($value =~ /<<.*>>/) { + $query_criteria .= " AND $crit=$value"; + } else { + $query_criteria .= " AND $crit='$value'"; + } } warn $query_criteria; } @@ -304,8 +310,7 @@ elsif ( $phase eq 'Build Report' ) { my $column = $input->param('column'); my $crit = $input->param('criteria'); my $totals = $input->param('totals'); - my $definition = $input->param('definition'); -# my @criteria = split( ',', $crit ); + my $definition = $input->param('definition'); my $query_criteria=$crit; # split the columns up by , my @columns = split( ',', $column ); @@ -377,6 +382,7 @@ elsif ($phase eq 'Run this report'){ my $limit = 20; # page size. # TODO: move to DB or syspref? my $offset = 0; my $report = $input->param('reports'); + my @sql_params = $input->param('sql_params'); # offset algorithm if ($input->param('page')) { $offset = ($input->param('page') - 1) * $limit; @@ -386,33 +392,128 @@ elsif ($phase eq 'Run this report'){ push @errors, {no_sql_for_id=>$report}; } my @rows = (); - my ($sth, $errors) = execute_query($sql, $offset, $limit); - my $total = select_2_select_count_value($sql) || 0; - unless ($sth) { - die "execute_query failed to return sth for report $report: $sql"; + # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters + if ($sql =~ /<>/,$sql; + my @tmpl_parameters; + for(my $i=0;$i<($#split/2);$i++) { + my ($text,$authorised_value) = split /\|/,$split[$i*2+1]; + my $input; + if ($authorised_value) { + my $dbh=C4::Context->dbh; + my @authorised_values; + my %authorised_lib; + # builds list, depending on authorised value... + if ( $authorised_value eq "branches" ) { + my $branches = GetBranchesLoop(); + foreach my $thisbranch (@$branches) { + push @authorised_values, $thisbranch->{value}; + $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname}; + } + } + elsif ( $authorised_value eq "itemtypes" ) { + my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description"); + $sth->execute; + while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) { + push @authorised_values, $itemtype; + $authorised_lib{$itemtype} = $description; + } + } + elsif ( $authorised_value eq "cn_source" ) { + my $class_sources = GetClassSources(); + my $default_source = C4::Context->preference("DefaultClassificationSource"); + foreach my $class_source (sort keys %$class_sources) { + next unless $class_sources->{$class_source}->{'used'} or + ($class_source eq $default_source); + push @authorised_values, $class_source; + $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'}; + } + } + elsif ( $authorised_value eq "categorycode" ) { + my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description"); + $sth->execute; + while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) { + push @authorised_values, $categorycode; + $authorised_lib{$categorycode} = $description; + } + + #---- "true" authorised value + } + else { + my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib"); + + $authorised_values_sth->execute( $authorised_value); + + while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { + push @authorised_values, $value; + $authorised_lib{$value} = $lib; + # For item location, we show the code and the libelle + $authorised_lib{$value} = $lib; + } + } + $input =CGI::scrolling_list( # FIXME: factor out scrolling_list + -name => "sql_params", + -values => \@authorised_values, +# -default => $value, + -labels => \%authorised_lib, + -override => 1, + -size => 1, + -multiple => 0, + -tabindex => 1, + ); + + } else { + $input = ""; + } + push @tmpl_parameters, {'entry' => $text, 'input' => $input }; + } + $template->param('sql' => $sql, + 'name' => $name, + 'sql_params' => \@tmpl_parameters, + 'enter_params' => 1, + 'reports' => $report, + ); } else { - my $headref = $sth->{NAME} || []; - my @headers = map { +{ cell => $_ } } @$headref; - $template->param(header_row => \@headers); - while (my $row = $sth->fetchrow_arrayref()) { - my @cells = map { +{ cell => $_ } } @$row; - push @rows, { cells => \@cells }; + # OK, we have parameters, or there are none, we run the report + # if there were parameters, replace before running + # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill + my @split = split /<<|>>/,$sql; + my @tmpl_parameters; + for(my $i=0;$i<$#split/2;$i++) { + my $quoted = C4::Context->dbh->quote($sql_params[$i]); + # if there are special regexp chars, we must \ them + $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; + $sql =~ s/<<$split[$i*2+1]>>/$quoted/; + } + my ($sth, $errors) = execute_query($sql, $offset, $limit); + my $total = select_2_select_count_value($sql) || 0; + unless ($sth) { + die "execute_query failed to return sth for report $report: $sql"; + } else { + my $headref = $sth->{NAME} || []; + my @headers = map { +{ cell => $_ } } @$headref; + $template->param(header_row => \@headers); + while (my $row = $sth->fetchrow_arrayref()) { + my @cells = map { +{ cell => $_ } } @$row; + push @rows, { cells => \@cells }; + } } - } - my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); - my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report"; - $template->param( - 'results' => \@rows, - 'sql' => $sql, - 'execute' => 1, - 'name' => $name, - 'notes' => $notes, - 'errors' => $errors, - 'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), - 'unlimited_total' => $total, - ); -} + my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); + my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report"; + $template->param( + 'results' => \@rows, + 'sql' => $sql, + 'execute' => 1, + 'name' => $name, + 'notes' => $notes, + 'errors' => $errors, + 'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), + 'unlimited_total' => $total, + ); + } +} elsif ($phase eq 'Export'){ binmode STDOUT, ':utf8'; -- 1.6.3.3