From e15198fc3004dd4c358936204b1a0cca9e18dbe6 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@biblibre.com>
Date: Thu, 5 Apr 2012 15:44:32 +0200
Subject: [PATCH 1/2] Bug 5338: Serials Report


Signed-off-by: Paola Rossi <paola.rossi@cineca.it>
---
 C4/Reports/Guided.pm                               |   31 ++++++++++++++++----
 koha-tmpl/intranet-tmpl/prog/en/columns.def        |    6 ++++
 .../en/modules/reports/guided_reports_start.tt     |    3 +-
 3 files changed, 33 insertions(+), 7 deletions(-)

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index aaa1ce2..1d9e76e 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 qw ( -utf8 );
 use Carp;
 
@@ -49,6 +48,7 @@ BEGIN {
       GetParametersFromSQL
       IsAuthorisedValueValid
       ValidateSQLParameters
+      nb_rows update_sql
     );
 }
 
@@ -79,6 +79,7 @@ sub get_area_name_sql_snippet {
         [PAT  => "Patrons"],
         [ACQ  => "Acquisition"],
         [ACC  => "Accounts"],
+        [SER  => "Serials"],
     );
 
     return "CASE report_area " .
@@ -88,7 +89,7 @@ sub get_area_name_sql_snippet {
 
 sub get_report_areas {
 
-    my $report_areas = [ 'CIRC', 'CAT', 'PAT', 'ACQ', 'ACC' ];
+    my $report_areas = [ 'CIRC', 'CAT', 'PAT', 'ACQ', 'ACC', 'SER' ];
 
     return $report_areas;
 }
@@ -100,6 +101,7 @@ sub get_table_areas {
     PAT  => ['borrowers'],
     ACQ  => [ 'aqorders', 'biblio', 'items' ],
     ACC  => [ 'borrowers', 'accountlines' ],
+    SER  => [ 'serial', 'serialitems', 'subscription', 'subscriptionhistory', 'subscriptionroutinglist', 'biblioitems', 'biblio', 'aqbooksellers' ],
     );
 }
 
@@ -240,6 +242,7 @@ sub build_query {
         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'],
     );
 
 
@@ -330,6 +333,7 @@ sub get_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
@@ -386,10 +390,14 @@ sub get_criteria {
             $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":'').">>" };
+            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;
-                if ($row->{'availablevalues'} eq '') { $row->{'default'} = 1 };
             }
             $sth->finish();
 
@@ -400,7 +408,6 @@ sub get_criteria {
 
             push @criteria_array, \%temp;
         }
-
     }
     return ( \@criteria_array );
 }
@@ -1038,6 +1045,18 @@ sub ValidateSQLParameters {
     return \@problematic_parameters;
 }
 
+sub _get_display_value {
+    my ( $original_value, $column ) = @_;
+    if ( $column eq '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 ec0fb56..92b6473 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/columns.def
+++ b/koha-tmpl/intranet-tmpl/prog/en/columns.def
@@ -139,3 +139,9 @@
 <field name="biblioitems.marcxml">MARC blob</field>
 <field name="biblioitems.url">URL</field>
 <field name="biblioitems.title">Title</field>
+<field name="subscription.startdate">Start date</field>
+<field name="subscription.enddate">End date</field>
+<field name="subscription.periodicity">Periodicity</field>
+<field name="subscription.callnumber">Call number</field>
+<field name="subscription.location">Location</field>
+<field name="subscription.branchcode">Library</field>
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 24b7f78..175cb51 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
@@ -7,6 +7,7 @@
         [%- CASE 'PAT'  -%]Patrons
         [%- CASE 'ACQ'  -%]Acquisitions
         [%- CASE 'ACC'  -%]Accounts
+        [%- CASE 'SER'  -%]Serials
     [%- END -%]
 [%- END -%]
 
@@ -573,7 +574,7 @@ canned reports and writing custom SQL reports.</p>
             <td>
                 <select name="[% criteri.name %]_value">
                 [% FOREACH value IN criteri.values %]
-                <option value="[% value.availablevalues %]">[% IF ( value.default ) %]Default[% ELSE %][% value.availablevalues |html %][% END %]</option>
+                <option value="[% value.availablevalues %]">[% IF ( value.default ) %]Default[% ELSE %][% value.display_value |html %][% END %]</option>
                 [% END %]
                 </select>
             </td>
-- 
1.7.10.4