[Koha-patches] [PATCH] wr77488 (bug 6033): report selection filter
Srdjan Jankovic
srdjan at catalyst.net.nz
Wed Apr 6 05:41:58 CEST 2011
---
C4/Reports/Guided.pm | 58 +++++++++++++++-----
.../en/modules/reports/guided_reports_start.tmpl | 21 +++++++
reports/guided_reports.pl | 27 ++++++++--
3 files changed, 88 insertions(+), 18 deletions(-)
diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 7231457..24a9dc1 100644
--- a/C4/Reports/Guided.pm
+++ b/C4/Reports/Guided.pm
@@ -24,7 +24,7 @@ use Carp;
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
use C4::Context;
-use C4::Dates qw/format_date/;
+use C4::Dates qw/format_date format_date_in_iso/;
use C4::Output;
use C4::Dates;
use XML::Simple;
@@ -546,22 +546,52 @@ sub delete_report {
$sth->execute($id);
}
+# $filter is either { date => $d, author => $a, keyword => $kw }
+# or $keyword. Optional.
+my $DATE_FORMAT = "%d/%m/%Y";
sub get_saved_reports {
+ my ($filter) = @_;
+ $filter = { keyword => $filter } if $filter && !ref( $filter );
+
my $dbh = C4::Context->dbh();
- my $query = "SELECT *,saved_sql.id AS id FROM saved_sql
- LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
- ORDER by date_created";
- my $sth = $dbh->prepare($query);
- $sth->execute();
-
- my $result = $sth->fetchall_arrayref({});
- foreach (@$result){
- $_->{date_created} = format_date($_->{date_created});
-
- my $member = C4::Members::GetMember(borrowernumber=>$_->{borrowernumber});
- $_->{borrowerfirstname} = $member->{firstname};
- $_->{borrowersurname} = $member->{surname};
+ my (@cond, at args);
+ my $query = "SELECT saved_sql.id, report_id, report,
+ date_run, date_created, last_modified, savedsql, last_run,
+ report_name, type, notes,
+ borrowernumber, surname as borrowersurname, firstname as borrowerfirstname
+ FROM saved_sql
+ LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
+ LEFT OUTER JOIN borrowers USING (borrowernumber)";
+ if ($filter) {
+ if (my $date = $filter->{date}) {
+ $date = format_date_in_iso($date);
+ push @cond, "DATE(date_run) = ? OR
+ DATE(date_created) = ? OR
+ DATE(last_modified) = ? OR
+ DATE(last_run) = ?";
+ push @args, $date, $date, $date, $date;
+ }
+ if (my $author = $filter->{author}) {
+ $author = "%$author%";
+ push @cond, "surname LIKE ? OR
+ firstname LIKE ?";
+ push @args, $author, $author;
+ }
+ if (my $keyword = $filter->{keyword}) {
+ $keyword = "%$keyword%";
+ push @cond, "report LIKE ? OR
+ report_name LIKE ? OR
+ notes LIKE ? OR
+ savedsql LIKE ?";
+ push @args, $keyword, $keyword, $keyword, $keyword;
+ }
}
+ $query .= " WHERE ".join( " AND ", map "($_)", @cond ) if @cond;
+ $query .= " ORDER by date_created";
+
+ my $result = $dbh->selectall_arrayref($query, {Slice => {}}, @args);
+ $_->{date_created} = format_date($_->{date_created}) foreach @$result;
+
return $result;
}
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 eb8d4fb..228b426 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
@@ -125,6 +125,27 @@ canned reports and writing custom SQL reports.</p>
<!-- TMPL_IF NAME="savedreports" --><h1>Saved Reports</h1>
<p>Choose the report to run from the list</p>
+<div style="margin: 5px">
+<form action="?">
+ <input type="hidden" name="phase" value="Use saved" />
+ <input type="hidden" name="filter_set" value="1" />
+ <table>
+ <tr>
+ <td rowspan="3"><h3>Filter</h3><br/><input type="submit" value="Go" /></td>
+ <td align="right">Date:</td>
+ <td><input type="text" name="filter_date" size="10" value="<!-- TMPL_VAR NAME="filter_date" ESCAPE="HTML"-->" /></td>
+ </tr>
+ <tr>
+ <td align="right">Author:</td>
+ <td><input type="text" name="filter_author" value="<!-- TMPL_VAR NAME="filter_author" ESCAPE="HTML"-->" /></td>
+ </tr>
+ <tr>
+ <td align="right">Keyword:</td>
+ <td><input type="text" name="filter_keyword" value="<!-- TMPL_VAR NAME="filter_keyword" ESCAPE="HTML"-->" /></td>
+ </tr>
+ </table>
+</form>
+</div>
<span id="pagertable_reports" class="pager">
<form class="formpager"> <strong>page(s)</strong> :
<img src="<!-- TMPL_VAR name="interface" -->/prog/img/first.png" class="first"/>
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index a53712e..5a50ea8 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -22,7 +22,7 @@ use strict;
use CGI;
use Text::CSV;
use C4::Reports::Guided;
-use C4::Auth;
+use C4::Auth qw/:DEFAULT get_session/;
use C4::Output;
use C4::Dates;
use C4::Debug;
@@ -61,8 +61,20 @@ my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
debug => 1,
}
);
+my $session = $cookie ? get_session($cookie->value) : undef;
- my @errors = ();
+my $filter;
+if ( $input->param("filter_set") ) {
+ $filter = {};
+ $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword/;
+ $session->param('report_filter', $filter) if $session;
+}
+elsif ($session) {
+ $filter = $session->param('report_filter');
+}
+
+
+my @errors = ();
if ( !$phase ) {
$template->param( 'start' => 1 );
# show welcome page
@@ -75,8 +87,15 @@ elsif ( $phase eq 'Build new' ) {
elsif ( $phase eq 'Use saved' ) {
# use a saved report
# get list of reports and display them
- $template->param( 'saved1' => 1 );
- $template->param( 'savedreports' => get_saved_reports() );
+ $template->param(
+ 'saved1' => 1,
+ 'savedreports' => get_saved_reports($filter),
+ );
+ if ($filter) {
+ while ( my ($k, $v) = each %$filter ) {
+ $template->param( "filter_$k" => $v ) if $v;
+ }
+ }
}
elsif ( $phase eq 'Delete Saved') {
--
1.6.5
More information about the Koha-patches
mailing list