From 20e23aa05af06d497932884059c5dade6970e455 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.

Signed-off-by: Cedric Vita <cedric.vita@dracenie.com>
---
 C4/Serials.pm                                      | 39 +++++++++++++++-------
 installer/data/mysql/atomicupdate/bug_8296.sql     |  2 ++
 installer/data/mysql/kohastructure.sql             |  1 +
 .../prog/en/modules/serials/serials-collection.tt  | 14 ++++++++
 .../prog/en/modules/serials/serials-edit.tt        |  7 ++++
 .../prog/en/modules/serials/subscription-detail.tt |  4 +++
 misc/cronjobs/serialsUpdate.pl                     |  2 +-
 serials/serials-collection.pl                      | 10 ++++--
 serials/serials-edit.pl                            |  4 +++
 9 files changed, 67 insertions(+), 16 deletions(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug_8296.sql

diff --git a/C4/Serials.pm b/C4/Serials.pm
index 3fcc4a3..190f1b4 100644
--- a/C4/Serials.pm
+++ b/C4/Serials.pm
@@ -338,6 +338,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,
@@ -494,6 +495,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,
@@ -662,7 +664,8 @@ sub GetSerials {
     $count = 5 unless ($count);
     my @serials;
     my $statuses = join( ',', ( ARRIVED, MISSING_STATUSES, NOT_ISSUED ) );
-    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 ( $statuses )
                         ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC";
@@ -682,7 +685,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 ( $statuses )
@@ -731,7 +735,8 @@ sub GetSerials2 {
 
     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 ($statuses_string)
                  ORDER BY publisheddate,serialid DESC
@@ -1059,7 +1064,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
@@ -1067,7 +1073,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) = @_;
 
     return unless ($serialid);
 
@@ -1089,9 +1096,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);
@@ -1495,19 +1508,21 @@ 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 ?
 
     return unless ($subscriptionid);
 
     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/atomicupdate/bug_8296.sql b/installer/data/mysql/atomicupdate/bug_8296.sql
new file mode 100644
index 0000000..6c742dd
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_8296.sql
@@ -0,0 +1,2 @@
+ALTER TABLE serial
+ADD COLUMN publisheddatetext VARCHAR(100) DEFAULT NULL AFTER publisheddate
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 13b6a6a..8c588f2 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1957,6 +1957,7 @@ CREATE TABLE `serial` ( -- issues related to subscriptions
   `planneddate` date default NULL, -- date expected
   `notes` text, -- notes
   `publisheddate` date default NULL, -- date published
+  publisheddatetext varchar(100) default NULL, -- date published (descriptive)
   `claimdate` date default NULL, -- date claimed
   claims_count int(11) default 0, -- number of claims made related to this issue
   `routingnotes` text, -- notes from the routing list
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 4418baf..149f5e5 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
@@ -205,6 +205,8 @@ $(document).ready(function() {
 [% END %]
                 <th class="title-string">Date published
                 </th>
+                <th class="title-string">Date published (text)
+                </th>
                 <th class="title-string">Date received
                 </th>
                 <th>Number
@@ -251,6 +253,18 @@ $(document).ready(function() {
                     </span>
                 </td>
                 <td>
+                    [% publisheddatetext = serial.publisheddatetext || serial.publisheddate %]
+                    <span title="[% publisheddatetext %]">
+                      [% IF serial.publisheddatetext %]
+                        [% publisheddatetext %]
+                      [% ELSIF serial.publisheddate %]
+                        [% serial.publisheddate | $KohaDates %]
+                      [% ELSE %]
+                        Unknown
+                      [% END %]
+                    </span>
+                </td>
+                <td>
                     <span title="[% serial.planneddate %]">
                       [% IF serial.planneddate %]
                         [% serial.planneddate | $KohaDates %]
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 3f270e0..d5bdac7 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
@@ -114,6 +114,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>
@@ -135,6 +136,9 @@ $(document).ready(function() {
             <input type="text" name="publisheddate" value="[% serialslis.publisheddate %]" size="10" maxlength="15" class="datepicker" />
         </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" class="datepicker" />
         </td>
         <td>
@@ -289,6 +293,9 @@ $(document).ready(function() {
             <input type="text" name="publisheddate" value="[% newserialloo.publisheddate %]" size="10" maxlength="15"  class="datepicker" />
         </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" class="datepicker" />
         </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 b684e5e..8764c30 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
@@ -251,6 +251,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 %]
@@ -273,6 +274,9 @@ $(document).ready(function() {
                     [% END %]
                 </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/misc/cronjobs/serialsUpdate.pl b/misc/cronjobs/serialsUpdate.pl
index fedf064..9cc3014 100755
--- a/misc/cronjobs/serialsUpdate.pl
+++ b/misc/cronjobs/serialsUpdate.pl
@@ -131,7 +131,7 @@ while ( my $issue = $sth->fetchrow_hashref ) {
             {
                 $confirm
                   and ModSerialStatus( $issue->{serialid}, $issue->{serialseq},
-                    $issue->{planneddate}, $issue->{publisheddate},
+                    $issue->{planneddate}, $issue->{publisheddate}, $issue->{publisheddatetext},
                     3, $note );
                 $verbose
                   and print "Serial issue with id=" . $issue->{serialid} . " updated\n";
diff --git a/serials/serials-collection.pl b/serials/serials-collection.pl
index 900064a..aef4604 100755
--- a/serials/serials-collection.pl
+++ b/serials/serials-collection.pl
@@ -55,8 +55,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++ ){
@@ -65,7 +69,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 {
             require C4::Serials::Numberpattern;
             my $subscription = GetSubscription($subscriptionid);
diff --git a/serials/serials-edit.pl b/serials/serials-edit.pl
index 8df489d..1a8339f 100755
--- a/serials/serials-edit.pl
+++ b/serials/serials-edit.pl
@@ -82,6 +82,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');
@@ -231,6 +233,7 @@ if ( $op and $op eq 'serialchangestatus' ) {
                     $status[$i],
                     $plan_date,
                     $pub_date,
+                    $publisheddatetexts[$i],
                     $notes[$i]
                 );
             }
@@ -241,6 +244,7 @@ if ( $op and $op eq 'serialchangestatus' ) {
                 $serialseqs[$i],
                 $plan_date,
                 $pub_date,
+                $publisheddatetexts[$i],
                 $status[$i],
                 $notes[$i]
             );
-- 
1.9.1