From 0adc3cd3d3eab161222d198bff70275c07aa513b Mon Sep 17 00:00:00 2001
From: Julian Maurice <julian.maurice@biblibre.com>
Date: Fri, 22 Jun 2012 17:25:54 +0200
Subject: [PATCH] Bug 8296: Add descriptive (text) published date field for serials

Add a new field serial.publisheddatetext for displaying published date
in another format that dateformat syspref permit.
---
 C4/Serials.pm                                      |   40 +++++++++++++------
 installer/data/mysql/kohastructure.sql             |    1 +
 installer/data/mysql/updatedatabase.pl             |   12 ++++++
 .../prog/en/modules/serials/serials-collection.tt  |    4 ++
 .../prog/en/modules/serials/serials-edit.tt        |    7 +++
 .../prog/en/modules/serials/subscription-detail.tt |    4 ++
 .../prog/en/modules/opac-full-serial-issues.tt     |    2 +
 misc/cronjobs/serialsUpdate.pl                     |    2 +-
 serials/serials-collection.pl                      |   10 +++-
 serials/serials-edit.pl                            |    4 ++
 10 files changed, 69 insertions(+), 17 deletions(-)

diff --git a/C4/Serials.pm b/C4/Serials.pm
index 0b31a2b..670e5ac 100644
--- a/C4/Serials.pm
+++ b/C4/Serials.pm
@@ -362,6 +362,7 @@ sub GetFullSubscription {
             serial.serialseq,
             serial.planneddate, 
             serial.publisheddate, 
+            serial.publisheddatetext,
             serial.status, 
             serial.notes as notes,
             year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
@@ -523,6 +524,7 @@ sub GetFullSubscriptionsFromBiblionumber {
             serial.serialseq,
             serial.planneddate, 
             serial.publisheddate, 
+            serial.publisheddatetext,
             serial.status, 
             serial.notes as notes,
             year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year,
@@ -738,7 +740,8 @@ sub GetSerials {
     my $counter = 0;
     $count = 5 unless ($count);
     my @serials;
-    my $query = "SELECT serialid,serialseq, status, publisheddate, planneddate,notes, routingnotes
+    my $query = "SELECT serialid,serialseq, status, publisheddate,
+        publisheddatetext, planneddate,notes, routingnotes
                         FROM   serial
                         WHERE  subscriptionid = ? AND status NOT IN (2,4,5) 
                         ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
@@ -758,7 +761,8 @@ sub GetSerials {
     }
 
     # OK, now add the last 5 issues arrives/missing
-    $query = "SELECT   serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
+    $query = "SELECT   serialid,serialseq, status, planneddate, publisheddate,
+        publisheddatetext, notes, routingnotes
        FROM     serial
        WHERE    subscriptionid = ?
        AND      (status in (2,4,5))
@@ -800,7 +804,8 @@ sub GetSerials2 {
     my ( $subscription, $status ) = @_;
     my $dbh   = C4::Context->dbh;
     my $query = qq|
-                 SELECT   serialid,serialseq, status, planneddate, publisheddate,notes, routingnotes
+                 SELECT serialid,serialseq, status, planneddate, publisheddate,
+                    publisheddatetext, notes, routingnotes
                  FROM     serial 
                  WHERE    subscriptionid=$subscription AND status IN ($status)
                  ORDER BY publisheddate,serialid DESC
@@ -1095,7 +1100,8 @@ sub ModSubscriptionHistory {
 
 =head2 ModSerialStatus
 
-ModSerialStatus($serialid,$serialseq, $planneddate,$publisheddate,$status,$notes)
+    ModSerialStatus($serialid, $serialseq, $planneddate, $publisheddate,
+        $publisheddatetext, $status, $notes);
 
 This function modify the serial status. Serial status is a number.(eg 2 is "arrived")
 Note : if we change from "waited" to something else,then we will have to create a new "waited" entry
@@ -1103,7 +1109,8 @@ Note : if we change from "waited" to something else,then we will have to create
 =cut
 
 sub ModSerialStatus {
-    my ( $serialid, $serialseq, $planneddate, $publisheddate, $status, $notes ) = @_;
+    my ($serialid, $serialseq, $planneddate, $publisheddate, $publisheddatetext,
+        $status, $notes) = @_;
 
     #It is a usual serial
     # 1st, get previous status :
@@ -1119,10 +1126,15 @@ sub ModSerialStatus {
         DelIssue( {'serialid'=>$serialid, 'subscriptionid'=>$subscriptionid,'serialseq'=>$serialseq} );
     }
     else {
-        my $query =
-'UPDATE serial SET serialseq=?,publisheddate=?,planneddate=?,status=?,notes=? WHERE  serialid = ?';
+        my $query = '
+            UPDATE serial
+            SET serialseq = ?, publisheddate = ?, publisheddatetext = ?,
+                planneddate = ?, status = ?, notes = ?
+            WHERE  serialid = ?
+        ';
         $sth = $dbh->prepare($query);
-        $sth->execute( $serialseq, $publisheddate, $planneddate, $status, $notes, $serialid );
+        $sth->execute( $serialseq, $publisheddate, $publisheddatetext,
+            $planneddate, $status, $notes, $serialid );
         $query = "SELECT * FROM   subscription WHERE  subscriptionid = ?";
         $sth   = $dbh->prepare($query);
         $sth->execute($subscriptionid);
@@ -1478,17 +1490,19 @@ returns the serial id
 =cut
 
 sub NewIssue {
-    my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate, $publisheddate, $notes ) = @_;
+    my ( $serialseq, $subscriptionid, $biblionumber, $status, $planneddate,
+        $publisheddate, $publisheddatetext, $notes ) = @_;
     ### FIXME biblionumber CAN be provided by subscriptionid. So Do we STILL NEED IT ?
 
     my $dbh   = C4::Context->dbh;
     my $query = qq|
-        INSERT INTO serial
-            (serialseq,subscriptionid,biblionumber,status,publisheddate,planneddate,notes)
-        VALUES (?,?,?,?,?,?,?)
+        INSERT INTO serial (serialseq, subscriptionid, biblionumber, status,
+            publisheddate, publisheddatetext, planneddate, notes)
+        VALUES (?,?,?,?,?,?,?,?)
     |;
     my $sth = $dbh->prepare($query);
-    $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status, $publisheddate, $planneddate, $notes );
+    $sth->execute( $serialseq, $subscriptionid, $biblionumber, $status,
+        $publisheddate, $publisheddatetext, $planneddate, $notes );
     my $serialid = $dbh->{'mysql_insertid'};
     $query = qq|
         SELECT missinglist,recievedlist
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 221c4e4..52b10f3 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1800,6 +1800,7 @@ CREATE TABLE `serial` (
   `planneddate` date default NULL,
   `notes` text,
   `publisheddate` date default NULL,
+  publisheddatetext varchar(100) default NULL,
   `itemnumber` text default NULL,
   `claimdate` date default NULL,
   `routingnotes` text,
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index f0a9269..28dde99 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -6532,6 +6532,18 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     SetVersion ($DBversion);
 }
 
+$DBversion = "XXX";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+    $dbh->do("
+        ALTER TABLE serial
+        ADD COLUMN publisheddatetext VARCHAR(100) DEFAULT NULL
+          AFTER publisheddate
+    ");
+
+    print "Upgrade to $DBversion done (Add serial.publisheddatetext)\n";
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-collection.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-collection.tt
index eeb9262..866bfce 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-collection.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-collection.tt
@@ -251,6 +251,7 @@ $(document).ready(function() {
 [% END %]
                 <th>Date published
                 </th>
+                <th>Date published (text)</th>
                 <th>Date received
                 </th>
                 <th>Number
@@ -291,6 +292,9 @@ $(document).ready(function() {
                     [% serial.publisheddate %]
                 </td>
                 <td>
+                    [% serial.publisheddatetext || serial.publisheddate %]
+                </td>
+                <td>
                     [% serial.planneddate %]
                 </td>
                 <td>
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-edit.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-edit.tt
index 3a64b11..ce28417 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-edit.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-edit.tt
@@ -161,6 +161,7 @@ $(document).ready(function() {
 	<tr>
 		<th>Numbered</th>
 		<th>Published on</th>
+        <th>Published on (text)</th>
 		<th>Expected on</th>
 		<th>Status</th>
 		<th>Notes</th>
@@ -182,6 +183,9 @@ $(document).ready(function() {
             <input type="text" name="publisheddate" value="[% serialslis.publisheddate %]" size="10" maxlength="15" />
         </td>
         <td>
+            <input type="text" name="publisheddatetext" value="[% serialslis.publisheddatetext || serialslis.publisheddate %]" size="10" maxlength="15" />
+        </td>
+        <td>
             <input type="text" id="expecteddate" name="planneddate" value="[% serialslis.planneddate %]" size="10" maxlength="15" />
         </td>
         <td>
@@ -299,6 +303,9 @@ $(document).ready(function() {
             <input type="text" name="publisheddate" value="[% newserialloo.publisheddate %]" size="10" maxlength="15" />
         </td>
         <td>
+            <input type="text" name="publisheddatetext" value="[% newserialloo.publisheddatetext || newserialloo.publisheddate %]" size="10" maxlength="15" />
+        </td>
+        <td>
             <input type="text" name="planneddate" id="supexpecteddate" value="[% newserialloo.planneddate %]" size="10" maxlength="15" />
         </td>
         <td>
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/subscription-detail.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/subscription-detail.tt
index e6a7074..01a9522 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/subscription-detail.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/subscription-detail.tt
@@ -365,6 +365,7 @@ $(document).ready(function() {
             <th>Issue number</th>
             <th>Planned date</th>
             <th>Published date</th>
+            <th>Published date (text)</th>
             <th>Status</th>
         </tr>
         [% FOREACH serialslis IN serialslist %]
@@ -379,6 +380,9 @@ $(document).ready(function() {
                     [% serialslis.publisheddate %]
                 </td>
                 <td>
+                    [% serialslis.publisheddatetext || serialslis.publisheddate %]
+                </td>
+                <td>
                     [% IF ( serialslis.status1 ) %]Expected[% END %]
                     [% IF ( serialslis.status2 ) %]Arrived[% END %]
                     [% IF ( serialslis.status3 ) %]Late[% END %]
diff --git a/koha-tmpl/opac-tmpl/prog/en/modules/opac-full-serial-issues.tt b/koha-tmpl/opac-tmpl/prog/en/modules/opac-full-serial-issues.tt
index c37a6ef..bad1688 100644
--- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-full-serial-issues.tt
+++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-full-serial-issues.tt
@@ -179,6 +179,7 @@ function showlayer(numlayer){
 	    <thead>
 		<tr>
 		    <th>Date</th>
+		    <th>Date (text)</th>
 		    <th>Library</th>
 		    <th>Notes</th>
 		    <th>Date received</th>
@@ -192,6 +193,7 @@ function showlayer(numlayer){
 		<tr>
 		    <td>    [% serial.publisheddate %]
 		    </td>
+            <td>[% serial.publisheddatetext || serial.publisheddate %]</td>
             <td class="libraryfilterclass">    [% serial.branchcode %]
 		    </td>
 		    <td>    [% serial.notes %]
diff --git a/misc/cronjobs/serialsUpdate.pl b/misc/cronjobs/serialsUpdate.pl
index a5a376f..22ecf7d 100755
--- a/misc/cronjobs/serialsUpdate.pl
+++ b/misc/cronjobs/serialsUpdate.pl
@@ -83,7 +83,7 @@ while ( my $issue = $sth->fetchrow_hashref ) {
             {
         
             ModSerialStatus( $issue->{serialid}, $issue->{serialseq},
-                $issue->{planneddate}, $issue->{publisheddate},
+                $issue->{planneddate}, $issue->{publisheddate}, $issue->{publisheddatetext},
                 3, "Automatically set to late" );
             print $issue->{serialid}." update\n";
             }
diff --git a/serials/serials-collection.pl b/serials/serials-collection.pl
index ad57e07..46f9e80 100755
--- a/serials/serials-collection.pl
+++ b/serials/serials-collection.pl
@@ -56,8 +56,12 @@ my $subscriptions;
 
 if($op eq 'gennext' && @subscriptionid){
     my $subscriptionid = $subscriptionid[0];
-    my $sth = $dbh->prepare("SELECT publisheddate, serialid, serialseq, planneddate
-							FROM serial WHERE status = 1 AND subscriptionid = ?");
+    my $sth = $dbh->prepare("
+        SELECT publisheddate, publisheddatetext, serialid, serialseq,
+            planneddate
+        FROM serial
+        WHERE status = 1 AND subscriptionid = ?
+    ");
     my $status = defined( $nbissues ) ? 2 : 3;
     $nbissues ||= 1;
     for ( my $i = 0; $i < $nbissues; $i++ ){
@@ -66,7 +70,7 @@ if($op eq 'gennext' && @subscriptionid){
 	if ( my $issue = $sth->fetchrow_hashref ) {
 		ModSerialStatus( $issue->{serialid}, $issue->{serialseq},
                 $issue->{planneddate}, $issue->{publisheddate},
-                $status, "" );
+                $issue->{publisheddatetext}, $status, "" );
 	}else{
             my $subscription = GetSubscription($subscriptionid);
             my $expected = GetNextExpected($subscriptionid);
diff --git a/serials/serials-edit.pl b/serials/serials-edit.pl
index 66d3a4e..1379dd9 100755
--- a/serials/serials-edit.pl
+++ b/serials/serials-edit.pl
@@ -81,6 +81,8 @@ my @serialids       = $query->param('serialid');
 my @serialseqs      = $query->param('serialseq');
 my @planneddates    = $query->param('planneddate');
 my @publisheddates  = $query->param('publisheddate');
+my @publisheddates  = $query->param('publisheddate');
+my @publisheddatetexts = $query->param('publisheddatetext');
 my @status          = $query->param('status');
 my @notes           = $query->param('notes');
 my @subscriptionids = $query->param('subscriptionid');
@@ -227,6 +229,7 @@ if ( $op and $op eq 'serialchangestatus' ) {
                     $status[$i],
                     $plan_date,
                     $pub_date,
+                    $publisheddatetexts[$i],
                     $notes[$i]
                 );
             }
@@ -237,6 +240,7 @@ if ( $op and $op eq 'serialchangestatus' ) {
                 $serialseqs[$i],
                 $plan_date,
                 $pub_date,
+                $publisheddatetexts[$i],
                 $status[$i],
                 $notes[$i]
             );
-- 
1.7.2.5