Bugzilla – Attachment 3720 Details for
Bug 6033
Add Filters to SQL Saved Reports
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
patch
0001-wr77488-bug-6033-report-selection-filter.patch (text/plain), 6.46 KB, created by
Srdjan Jankovic
on 2011-04-06 03:40:43 UTC
(
hide
)
Description:
patch
Filename:
MIME Type:
Creator:
Srdjan Jankovic
Created:
2011-04-06 03:40:43 UTC
Size:
6.46 KB
patch
obsolete
>From 9fa918878097b7c40554def633bccbd8b4c8055f Mon Sep 17 00:00:00 2001 >From: Srdjan Jankovic <srdjan@catalyst.net.nz> >Date: Wed, 6 Apr 2011 14:56:45 +1200 >Subject: [PATCH] wr77488 (bug 6033): report selection filter > >--- > 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,@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 >
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 6033
: 3720