@@ -, +, @@ --- 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(-) --- a/C4/Reports/Guided.pm +++ a/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; } --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl @@ -125,6 +125,27 @@ canned reports and writing custom SQL reports.

Saved Reports

Choose the report to run from the list

+
+
+ + + + + + + + + + + + + + + + +

Filter


Date:" />
Author:" />
Keyword:" />
+
+
 page(s) : /prog/img/first.png" class="first"/> --- a/reports/guided_reports.pl +++ a/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') { --