From 50c3985b0f4ccebf9e732db850cd09dd31158f64 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 5 Apr 2012 15:44:32 +0200 Subject: [PATCH] Bug 5338: Serials Report --- C4/Reports/Guided.pm | 125 ++++++++++++-------- koha-tmpl/intranet-tmpl/prog/en/columns.def | 6 + .../en/modules/reports/guided_reports_start.tt | 2 +- 3 files changed, 81 insertions(+), 52 deletions(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 22a23dd..3262832 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -17,8 +17,7 @@ package C4::Reports::Guided; # with Koha; if not, write to the Free Software Foundation, Inc., # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. -use strict; -#use warnings; FIXME - Bug 2505 this module needs a lot of repair to run clean under warnings +use Modern::Perl; use CGI; use Carp; @@ -49,6 +48,7 @@ BEGIN { GetParametersFromSQL IsAuthorisedValueValid ValidateSQLParameters + nb_rows update_sql ); } @@ -78,6 +78,7 @@ my @REPORT_AREA = ( [PAT => "Patrons"], [ACQ => "Acquisition"], [ACC => "Accounts"], + [SER => "Serials"], ); my $AREA_NAME_SQL_SNIPPET = "CASE report_area " . @@ -93,6 +94,7 @@ my %table_areas = ( PAT => ['borrowers'], ACQ => [ 'aqorders', 'biblio', 'items' ], ACC => [ 'borrowers', 'accountlines' ], + SER => [ 'serial', 'serialitems', 'subscription', 'subscriptionhistory', 'subscriptionroutinglist', 'biblioitems', 'biblio', 'aqbooksellers' ], ); my %keys = ( CIRC => [ 'statistics.borrowernumber=borrowers.borrowernumber', @@ -104,6 +106,7 @@ my %keys = ( ACQ => [ 'aqorders.biblionumber=biblio.biblionumber', 'biblio.biblionumber=items.biblionumber' ], ACC => ['borrowers.borrowernumber=accountlines.borrowernumber'], + SER => [ 'serial.serialid=serialitems.serialid', 'serial.subscriptionid=subscription.subscriptionid', 'serial.subscriptionid=subscriptionhistory.subscriptionid', 'serial.subscriptionid=subscriptionroutinglist.subscriptionid', 'biblioitems.biblionumber=serial.biblionumber', 'biblio.biblionumber=biblioitems.biblionumber', 'subscription.aqbooksellerid=aqbooksellers.id'], ); # have to do someting here to know if its dropdown, free text, date etc @@ -120,6 +123,7 @@ my %criteria = ( PAT => [ 'borrowers.branchcode', 'borrowers.categorycode' ], ACQ => ['aqorders.datereceived|date'], ACC => [ 'borrowers.branchcode', 'borrowers.categorycode' ], + SER => ['subscription.startdate|date', 'subscription.enddate|date', 'subscription.periodicity', 'subscription.callnumber', 'subscription.location', 'subscription.branchcode'], ); # Adds itemtypes to criteria, according to the syspref @@ -333,59 +337,64 @@ sub get_criteria { foreach my $localcrit (@$crit) { my ( $value, $type ) = split( /\|/, $localcrit ); my ( $table, $column ) = split( /\./, $value ); - if ($type eq 'textrange') { - my %temp; - $temp{'name'} = $value; - $temp{'from'} = "from_" . $value; - $temp{'to'} = "to_" . $value; - $temp{'textrange'} = 1; - $temp{'description'} = $column_defs->{$value}; - push @criteria_array, \%temp; - } - elsif ($type eq 'date') { - my %temp; - $temp{'name'} = $value; - $temp{'date'} = 1; - $temp{'description'} = $column_defs->{$value}; - push @criteria_array, \%temp; - } - elsif ($type eq 'daterange') { - my %temp; - $temp{'name'} = $value; - $temp{'from'} = "from_" . $value; - $temp{'to'} = "to_" . $value; - $temp{'daterange'} = 1; - $temp{'description'} = $column_defs->{$value}; - push @criteria_array, \%temp; - } - else { - my $query = - "SELECT distinct($column) as availablevalues FROM $table"; - 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 }; + for ( $type ) { + when ( 'textrange' ) { + my %temp; + $temp{'name'} = $value; + $temp{'from'} = "from_" . $value; + $temp{'to'} = "to_" . $value; + $temp{'textrange'} = 1; + $temp{'description'} = $column_defs->{$value}; + push @criteria_array, \%temp; } - $sth->finish(); + when ( 'date' ) { + my %temp; + $temp{'name'} = $value; + $temp{'date'} = 1; + $temp{'description'} = $column_defs->{$value}; + push @criteria_array, \%temp; + } + when ( 'daterange' ) { + my %temp; + $temp{'name'} = $value; + $temp{'from'} = "from_" . $value; + $temp{'to'} = "to_" . $value; + $temp{'daterange'} = 1; + $temp{'description'} = $column_defs->{$value}; + push @criteria_array, \%temp; + } + default { + my $query = + "SELECT distinct($column) as availablevalues FROM $table"; + 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" : '' ) . ">>", + display_value => "<<$column" . ( $list ? "|$list" : '' ) . ">>", + }; + while ( my $row = $sth->fetchrow_hashref() ) { + if ($row->{'availablevalues'} eq '') { $row->{'default'} = 1 } + else { $row->{display_value} = _get_display_value( $row->{'availablevalues'}, $column ); } + push @values, $row; + } + $sth->finish(); - my %temp; - $temp{'name'} = $value; - $temp{'description'} = $column_defs->{$value}; - $temp{'values'} = \@values; + my %temp; + $temp{'name'} = $value; + $temp{'description'} = $column_defs->{$value}; + $temp{'values'} = \@values; - push @criteria_array, \%temp; + push @criteria_array, \%temp; + } } - } return ( \@criteria_array ); } @@ -1008,6 +1017,20 @@ sub ValidateSQLParameters { return \@problematic_parameters; } +sub _get_display_value { + my ( $original_value, $column ) = @_; + for ( $column ) { + when ("periodicity") { + my $dbh = C4::Context->dbh(); + my $query = "SELECT description FROM subscription_frequencies WHERE id = ?"; + my $sth = $dbh->prepare($query); + $sth->execute($original_value); + return $sth->fetchrow; + } + } + return $original_value; +} + 1; __END__ diff --git a/koha-tmpl/intranet-tmpl/prog/en/columns.def b/koha-tmpl/intranet-tmpl/prog/en/columns.def index dc863e4..23a54d6 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/columns.def +++ b/koha-tmpl/intranet-tmpl/prog/en/columns.def @@ -139,3 +139,9 @@ biblioitems.lccn LCCN biblioitems.marcxml MARC blob biblioitems.url URL biblioitems.title Title +subscription.startdate Start date +subscription.enddate End date +subscription.periodicity Periodicity +subscription.callnumber Call number +subscription.location Location +subscription.branchcode Library diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt index 2ac91d6..a834242 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt @@ -557,7 +557,7 @@ canned reports and writing custom SQL reports.

-- 1.7.10.4