From 73356e54838eb4a640a39a1241c1def7770be212 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@biblibre.com>
Date: Mon, 8 Dec 2014 16:31:03 +0100
Subject: [PATCH] Bug 13421: Add the creation time for virtual shelves

Test plan:
1/ Execute the updatedb entry
2/ Create a list
3/ Go on the shelve list and confirm that the creation and last
modification time are now displayed.
4/ Confirm that you are able to sort the list by creation/modification
time.
---
 C4/Utils/DataTables/VirtualShelves.pm                   |  4 +---
 C4/VirtualShelves.pm                                    | 11 ++++++-----
 installer/data/mysql/kohastructure.sql                  |  1 +
 installer/data/mysql/updatedatabase.pl                  | 17 +++++++++++++++++
 .../prog/en/modules/virtualshelves/shelves.tt           |  6 ++++++
 .../en/modules/virtualshelves/tables/shelves_results.tt |  5 +++++
 6 files changed, 36 insertions(+), 8 deletions(-)

diff --git a/C4/Utils/DataTables/VirtualShelves.pm b/C4/Utils/DataTables/VirtualShelves.pm
index 6b51cef..f6531c8 100644
--- a/C4/Utils/DataTables/VirtualShelves.pm
+++ b/C4/Utils/DataTables/VirtualShelves.pm
@@ -31,6 +31,7 @@ sub search {
     # But the code is too dirty to refactor...
     my $select = q|
         SELECT vs.shelfnumber, vs.shelfname, vs.owner, vs.category AS type,
+        vs.creation_time, vs.lastmodified as modification_time,
         bo.surname, bo.firstname, vs.sortfield as sortby,
         count(vc.biblionumber) as count
     |;
@@ -64,9 +65,6 @@ sub search {
         push @args, "%$shelfname%";
     }
     if ( defined $owner and $owner ne '' ) {
-        #push @where_strs, 'owner LIKE ?';
-        #push @args, "$owner%";
-        # WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?);
         push @where_strs, '( bo.firstname LIKE ? OR bo.surname LIKE ? )';
         push @args, "%$owner%", "%$owner%";
     }
diff --git a/C4/VirtualShelves.pm b/C4/VirtualShelves.pm
index ed46a83..8ea4a80 100644
--- a/C4/VirtualShelves.pm
+++ b/C4/VirtualShelves.pm
@@ -323,9 +323,10 @@ sub AddShelf {
 
     return -1 unless _CheckShelfName($hashref->{shelfname}, $hashref->{category}, $owner, 0);
 
-    my $query = qq(INSERT INTO virtualshelves
-        (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other)
-        VALUES (?,?,?,?,?,?,?));
+    my $query = q|INSERT INTO virtualshelves
+        (shelfname,owner,category,sortfield,allow_add,allow_delete_own,allow_delete_other, creation_time)
+        VALUES (?,?,?,?,?,?,?, NOW())
+    |;
 
     my $sth = $dbh->prepare($query);
     $sth->execute(
@@ -475,13 +476,13 @@ sub ShelfPossibleAction {
     }
 
     my $dbh = C4::Context->dbh;
-    my $query = qq/
+    my $query = q{
         SELECT COALESCE(owner,0) AS owner, category, allow_add, allow_delete_own, allow_delete_other, COALESCE(sh.borrowernumber,0) AS borrowernumber
         FROM virtualshelves vs
         LEFT JOIN virtualshelfshares sh ON sh.shelfnumber=vs.shelfnumber
         AND sh.borrowernumber=?
         WHERE vs.shelfnumber=?
-    /;
+        };
     my $sth = $dbh->prepare($query);
     $sth->execute($user, $shelfnumber);
     my $shelf= $sth->fetchrow_hashref;
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 4ffbbf2..479a431 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2320,6 +2320,7 @@ CREATE TABLE `virtualshelves` ( -- information about lists (or virtual shelves)
   `category` varchar(1) default NULL, -- type of list (private [1], public [2])
   `sortfield` varchar(16) default NULL, -- the field this list is sorted on
   `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- date and time the list was last modified
+  `creation_time` TIMESTAMP NOT NULL, -- creation time
   `allow_add` tinyint(1) default 0, -- permission for adding entries to list
   `allow_delete_own` tinyint(1) default 1, -- permission for deleting entries frm list that you added yourself
   `allow_delete_other` tinyint(1) default 0, -- permission for deleting entries from list that another person added
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index f267b00..9001473 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -9599,6 +9599,23 @@ if ( CheckVersion($DBversion) ) {
     SetVersion ($DBversion);
 }
 
+$DBversion = "3.19.00.XXX";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do(q|
+        ALTER TABLE virtualshelves
+        ADD COLUMN creation_time TIMESTAMP NOT NULL AFTER lastmodified
+    |);
+    # Set creation_time = lastmodified
+    # I would say it's better than 0000-00-00
+    # Set modified to the existing value (do not get the current ts!)
+    $dbh->do(q|
+        UPDATE virtualshelves
+        SET creation_time = lastmodified, lastmodified = lastmodified
+    |);
+    print "Upgrade to $DBversion done (Bug XXXXX: Add DB field virtualshelves.creation_time)\n";
+    SetVersion ($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/shelves.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/shelves.tt
index a51f6b2..59953b9 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/shelves.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/shelves.tt
@@ -54,6 +54,8 @@ $(document).ready(function(){
             { 'mDataProp': 'dt_count' },
             { 'mDataProp': 'dt_owner' },
             { 'mDataProp': 'dt_sortby' },
+            { 'mDataProp': 'dt_creation_time' },
+            { 'mDataProp': 'dt_modification_time' },
             { 'mDataProp': 'dt_action', 'bSortable': false }
         ],
         "aoColumnDefs": [
@@ -603,6 +605,8 @@ function placeHold () {
                         <th>Contents</th>
                         <th>Owner</th>
                         <th>Sort by</th>
+                        <th>Creation date</th>
+                        <th>Modification date</th>
                         <th>Actions</th>
                     </tr>
                     <tr class="filters_row">
@@ -620,6 +624,8 @@ function placeHold () {
                             </select>
                         </th>
                         <th></th>
+                        <th></th>
+                        <th></th>
                     </tr>
                 </thead>
                 <tbody></tbody>
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/tables/shelves_results.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/tables/shelves_results.tt
index f29be39..0144ebc 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/tables/shelves_results.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/tables/shelves_results.tt
@@ -1,3 +1,4 @@
+[% USE KohaDates %]
 {
     "sEcho": [% sEcho %],
     "iTotalRecords": [% iTotalRecords %],
@@ -15,6 +16,10 @@
                     "<a href='/cgi-bin/koha/members/moremember.pl?borrowernumber=[% data.owner %]'>[% data.firstname %] [% data.surname %]</a>",
                 "dt_sortby":
                     [% IF data.sortby == "author" %]"Author"[% ELSIF data.sortby == "copyrightdate" %]"Year"[% ELSIF data.sortby == "itemcallnumber" %]"Call number"[% ELSE %]"Title"[% END %],
+                "dt_creation_time":
+                    "[% data.creation_time | $KohaDates %]",
+                "dt_modification_time":
+                    "[% data.modification_time | $KohaDates %]",
                 "dt_action":
                     "<a style=\"cursor:pointer\">[% PROCESS action_form shelfnumber=data.shelfnumber can_manage_shelf=data.can_manage_shelf %]</a>"
             }[% UNLESS loop.last %],[% END %]
-- 
2.1.0