From 7d8b369ba4066d485e5aa7739b1b073f0db80b45 Mon Sep 17 00:00:00 2001 From: Paul Poulain Date: Tue, 15 Dec 2009 19:55:58 +0100 Subject: [PATCH] Improved guided reports (#3929) 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 ++- .../modules/help/reports/guided_reports.tmpl | 14 +- .../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 0c969e3253..d8277b7e31 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.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']; # Adds itemtypes to criteria, according to the syspref if (C4::Context->preference('item-level_itypes')) { @@ -245,7 +255,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++){ @@ -342,6 +351,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 63bf22e868..b6c7b3e1fb 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 @@ -52,5 +52,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?

- \ No newline at end of file +

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 :

+ + + 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 70fa0bfcd9..731b8664c6 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 e42974da00..24b7878fa6 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'; -- 2.39.5