From 9bb031170a5d2c6db1dc075efab779742c21c553 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] Bug 5338: Serials Report

---
 C4/Reports/Guided.pm                               | 31 ++++++++++++++++++----
 koha-tmpl/intranet-tmpl/prog/en/columns.def        |  6 +++++
 .../en/modules/reports/guided_reports_start.tt     |  2 +-
 3 files changed, 33 insertions(+), 6 deletions(-)

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index a4c7c01..0fa2ec9 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
@@ -371,10 +375,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();
 
@@ -385,7 +393,6 @@ sub get_criteria {
 
             push @criteria_array, \%temp;
         }
-
     }
     return ( \@criteria_array );
 }
@@ -1017,6 +1024,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 2b130b7..55aa358 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 225ee83..757b1ff 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
@@ -551,7 +551,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>
-- 
2.0.0.rc2