Bugzilla – Attachment 1691 Details for
Bug 3929
parameters in guided reports
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
patch
0001-Improved-guided-reports.patch (text/plain), 19.03 KB, created by
Chris Cormack
on 2009-12-15 18:55:00 UTC
(
hide
)
Description:
patch
Filename:
MIME Type:
Creator:
Chris Cormack
Created:
2009-12-15 18:55:00 UTC
Size:
19.03 KB
patch
obsolete
>From a44ca166cd309aa6bde7e4b4401bca72c9ffb534 Mon Sep 17 00:00:00 2001 >From: Paul Poulain <paul.poulain@biblibre.com> >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 <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 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 @@ > </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 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 3929
: 1691