From 5d563b3ccf3ee797346d2288b41f3b15f7e775f3 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 5 Apr 2012 15:44:32 +0200 Subject: [PATCH 1/1] Bug 5338: Serials Report --- C4/Reports/Guided.pm | 133 ++++++++++++-------- koha-tmpl/intranet-tmpl/prog/en/columns.def | 6 + .../en/modules/reports/guided_reports_start.tt | 2 +- 3 files changed, 86 insertions(+), 55 deletions(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index fbc25d8..a98a38a 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; @@ -54,6 +53,8 @@ $table_areas{'2'} = [ 'items', 'biblioitems', 'biblio' ]; # catalogue $table_areas{'3'} = [ 'borrowers' ]; # patrons $table_areas{'4'} = ['aqorders', 'biblio', 'items']; # acquisitions $table_areas{'5'} = [ 'borrowers', 'accountlines' ]; # accounts +$table_areas{'6'} = [ 'biblio', 'biblioitems', 'serial', 'serialitems', 'subscription', 'subscriptionhistory', 'subscriptionroutinglists', 'aqbooksellers' ]; # serial + our %keys; $keys{'1'} = [ 'statistics.borrowernumber=borrowers.borrowernumber', @@ -70,6 +71,7 @@ $keys{'4'} = [ 'biblio.biblionumber=items.biblionumber' ]; $keys{'5'} = ['borrowers.borrowernumber=accountlines.borrowernumber']; +$keys{'6'} = [ 'biblio.biblionumber=biblioitems.biblionumber', 'biblioitems.biblionumber=serial.biblionumber', 'serial.serialid=serialitems.serialid', 'serial.subscriptionid=subscription.subscriptionid', 'serial.subscriptionid=subscriptionhistory.subscriptionid', 'serial.subscriptionid=subscriptionroutinglists.subscriptionid', 'subscription.aqbooksellerid=aqbooksellers.id']; # have to do someting here to know if its dropdown, free text, date etc @@ -96,6 +98,9 @@ $criteria{'4'} = ['aqorders.datereceived|date']; # reports on accounting $criteria{'5'} = ['borrowers.branchcode', 'borrowers.categorycode']; +# reports on serial +$criteria{'6'} = ['subscription.startdate|date', 'subscription.enddate|date', 'subscription.periodicity', 'subscription.callnumber', 'subscription.location', 'subscription.branchcode']; + # Adds itemtypes to criteria, according to the syspref if (C4::Context->preference('item-level_itypes')) { unshift @{ $criteria{'1'} }, 'items.itype'; @@ -155,9 +160,9 @@ sub get_report_areas { my $dbh = C4::Context->dbh(); # FIXME these should be in the database - my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions', 'Accounts'); + my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions', 'Accounts', 'Serials' ); my @reports2; - for ( my $i = 0 ; $i < 5 ; $i++ ) { + for my $i ( 0 .. $#reports ) { my %hashrep; $hashrep{id} = $i + 1; $hashrep{name} = $reports[$i]; @@ -316,59 +321,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 }; + given ( $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; + } + when ( 'date' ) { + my %temp; + $temp{'name'} = $value; + $temp{'date'} = 1; + $temp{'description'} = $column_defs->{$value}; + push @criteria_array, \%temp; } - $sth->finish(); + 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 ); } @@ -736,7 +746,7 @@ sub get_from_dictionary { $sth->execute(); } my @loop; - my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions', 'Accounts'); + my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions', 'Accounts', 'Serial' ); while (my $data = $sth->fetchrow_hashref()){ $data->{'areaname'}=$reports[$data->{'area'}-1]; push @loop,$data; @@ -782,6 +792,21 @@ sub _get_column_defs { close COLUMNS; return \%columns; } + +sub _get_display_value { + my ( $original_value, $column ) = @_; + given ( $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 726ecc6..080ca66 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/columns.def +++ b/koha-tmpl/intranet-tmpl/prog/en/columns.def @@ -138,3 +138,9 @@ biblioitems.place Place of publication biblioitems.lccn LCCN biblioitems.marcxml MARC blob biblioitems.url URL +subscription.startdate Start date +subscription.enddate End Date +subscription.periodicity Periodicity +subscription.callnumber Call Number +subscription.location Location +subscription.branchcode Branch Code 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 3d9a7ab..419242d 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 @@ -371,7 +371,7 @@ canned reports and writing custom SQL reports.

-- 1.7.7.3