[Koha-patches] [PATCH 84/92] Improved guided reports (#3929)
Henri-Damien LAURENT
henridamien.laurent at biblibre.com
Tue Dec 22 01:21:17 CET 2009
From: Paul Poulain <paul.poulain at biblibre.com>
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 <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
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 0c969e3..d8277b7 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 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 @@
</ul>
<p><strong>Step 2: Pick a Report Type</strong></p>
-<li style="color: #990000">IMPORTANT: Only Tabular should be chosen with this option</p>
+<li style="color: #990000">IMPORTANT: Only Tabular should be chosen with this option</p></li>
<p><strong>Step 3: Select Columns for Display</strong></p>
<p><strong>Step 4: Select Criteria to Limit</strong></p>
<p>This is optional, if you don't want to limit results by any one field, just leave this section blank.</p>
@@ -53,5 +53,17 @@
<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>
+<p><strong>Can I have run-time defined parameters?</strong></p>
+
+<p>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>>.</p>
+<ul>
+<li>The << and >> are just delimiters. You must put << at the beginning and >> at the end of your parameter</li>
+<li>The <strong>Question to ask</strong> will be displayed on the left of the string to enter.
+<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>
+</ul>
+<p>Note that you can have more than one parameter in a given SQL</p>
+<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=<<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</p>
+<p>Sample :</p>
+<ul><li>SELECT surname,firstname FROM borrowers WHERE branchcode=<<Enter patrons library|branches>> AND surname like <<Enter filter for patron surname (% if none)>></li></ul>
<!-- TMPL_INCLUDE NAME="help-bottom.inc" -->
\ 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.</p>
<td>
<select name="<!-- TMPL_VAR NAME="name" -->_value">
<!-- TMPL_LOOP NAME="values" -->
- <option value="<!-- TMPL_VAR NAME="availablevalues"-->"><!-- TMPL_IF name="default" -->Default<!-- TMPL_ELSE --><!-- TMPL_VAR NAME="availablevalues" --><!-- /TMPL_IF --></option>
+ <option value="<!-- TMPL_VAR NAME="availablevalues"-->"><!-- TMPL_IF name="default" -->Default<!-- TMPL_ELSE --><!-- TMPL_VAR NAME="availablevalues" ESCAPE="HTML"--><!-- /TMPL_IF --></option>
<!-- /TMPL_LOOP -->
</select>
</td>
@@ -390,11 +390,11 @@ NAME="name" -->"><!-- TMPL_VAR NAME="name"--></label></td><td>
<h1>Confirm Custom Report</h1>
<p>Your report will be generated with the following SQL statement.</p>
<p>
-<!-- TMPL_VAR NAME="sql" -->
+<!-- TMPL_VAR NAME="sql" ESCAPE="HTML" -->
</p>
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
-<input type="hidden" name="sql" value="<!-- TMPL_VAR NAME="sql" ESCAPE="html" -->" />
+<input type="hidden" name="sql" value="<!-- TMPL_VAR NAME="sql" -->" />
<input type="hidden" name="type" value="<!-- TMPL_VAR NAME="type" -->" />
<p>You will need to save the report before you can execute it</p>
<fieldset class="action"><input type="hidden" name="phase" value="Save" />
@@ -417,10 +417,24 @@ NAME="name" -->"><!-- TMPL_VAR NAME="name"--></label></td><td>
</form>
<!-- /TMPL_IF -->
+<!-- TMPL_IF name="enter_params" -->
+ <form action='/cgi-bin/koha/reports/guided_reports.pl'>
+ <input type='hidden' name='phase' value='Run this report' />
+ <input type='hidden' name='reports' value="<!--TMPL_VAR name='reports' -->" />
+ <h1>Enter parameters for report <!-- TMPL_VAR name="name" -->:</h1>
+ <!-- TMPL_IF NAME="notes" --><p><!-- TMPL_VAR NAME="notes" --></p><!-- /TMPL_IF -->
+ <!-- TMPL_LOOP name="sql_params" -->
+ <p><!-- TMPL_VAR name="entry" -->: <!-- TMPL_VAR name='input' --></p>
+ <!-- /TMPL_LOOP -->
+ <input type='submit' value='Run the report'/>
+ </form>
+<!-- /TMPL_IF -->
+
<!-- TMPL_IF NAME="execute" -->
<h1><!-- TMPL_VAR NAME="name" --></h1>
<!-- TMPL_IF NAME="notes" --><p><!-- TMPL_VAR NAME="notes" --></p><!-- /TMPL_IF -->
<!-- TMPL_IF NAME="unlimited_total" --><p>Total number of rows matching the (unlimited) query is <!-- TMPL_VAR NAME="unlimited_total" -->.</p><!-- /TMPL_IF -->
+<!-- TMPL_VAR name="sql" -->
<!-- TMPL_VAR NAME='pagination_bar' DEFAULT="" -->
<!-- TMPL_UNLESS name="errors" -->
<table>
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_params) {
+ # 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 ($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 = "<input type='text' name='sql_params'/>";
+ }
+ 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
More information about the Koha-patches
mailing list