From ca8aa006556d7cd65db23fe929289744584028f8 Mon Sep 17 00:00:00 2001
From: Katrin Fischer <katrin.fischer.83@web.de>
Date: Thu, 27 Aug 2020 10:38:25 +0000
Subject: [PATCH] Bug 25776: Add a 'last updated' column for notices

This add an 'udated_on' column to the letter table and displays
the information in 'slips and notices'.

To test:
- Apply patch
- Run updatedatabase
  Note: the existing letters will all be set to the
  currnet date).
- Go to Tools > Notices and slips
- Verify there is a new column "Last updated" in the table
  Note: The table will always display the newest change date
  of the different message transport types.
- Edit any notice
- Verify the "Last updated" is shown for any transport type
  with an existing notice definition
- Edit and add a notice
- Verify the dates are correctly stored
- Test the column configuration for the new column works
  correctly
---
 admin/columns_settings.yml                              | 2 ++
 installer/data/mysql/kohastructure.sql                  | 1 +
 koha-tmpl/intranet-tmpl/prog/en/modules/tools/letter.tt | 7 +++++++
 tools/letter.pl                                         | 3 ++-
 4 files changed, 12 insertions(+), 1 deletion(-)

diff --git a/admin/columns_settings.yml b/admin/columns_settings.yml
index 96b3a31a68..19e81bbce5 100644
--- a/admin/columns_settings.yml
+++ b/admin/columns_settings.yml
@@ -1437,6 +1437,8 @@ modules:
             -
               columnname: name
             -
+              columnname: updated_on
+            -
               columnname: copy_notice
             -
               columnname: actions
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 9700ee04ab..d3cfcc7782 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2487,6 +2487,7 @@ CREATE TABLE `letter` ( -- table for all notice templates in Koha
   `content` MEDIUMTEXT, -- body text for the notice or slip
   `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice
   `lang` varchar(25) NOT NULL DEFAULT 'default', -- lang of the notice
+  `updated_on` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- last modification
   PRIMARY KEY  (`module`,`code`, `branchcode`, `message_transport_type`, `lang`),
   CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`)
   REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/letter.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/letter.tt
index 4bfed8b44c..7c5349297f 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/letter.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/letter.tt
@@ -1,6 +1,7 @@
 [% USE raw %]
 [% USE Asset %]
 [% USE Koha %]
+[% USE KohaDates %]
 [% USE Branches %]
 [% USE TablesSettings %]
 [% SET footerjs = 1 %]
@@ -142,6 +143,7 @@
                                     <th>Module</th>
                                     <th>Code</th>
                                     <th>Name</th>
+                                    <th class="title-string">Last updated</th>
                                     <th class="nosort">Copy notice</th>
                                     <th class="nosort">Actions</th>
                                 </tr>
@@ -175,6 +177,7 @@
                                         </td>
                                         <td>[% lette.code | html %]</td>
                                         <td>[% lette.name | html %]</td>
+                                        <td>[% lette.updated_on | $KohaDates with_hours = 1 %]</td>
                                         <td class="actions">
                                             [% IF !independant_branch || !lette.branchcode %]
                                                 <form method="post" action="/cgi-bin/koha/tools/letter.pl">
@@ -418,6 +421,10 @@
                                     [% END %]
                                         <ol>
                                             <li>
+                                                <label for="updated_on_[% letter.message_transport_type | html %]_[% lang | html %]">Last updated:</label>
+                                                [% letter.updated_on | $KohaDates with_hours = 1 %]
+                                            </li>
+                                            <li>
                                                 <input type="hidden" name="message_transport_type" value="[% letter.message_transport_type | html %]" />
                                                 <input type="hidden" name="lang" value="[% lang | html %]" />
                                                 <label for="is_html_[% letter.message_transport_type | html %]_[% lang | html %]">HTML message:</label>
diff --git a/tools/letter.pl b/tools/letter.pl
index a0e2d373e4..b1d9eb9624 100755
--- a/tools/letter.pl
+++ b/tools/letter.pl
@@ -208,6 +208,7 @@ sub add_form {
             $letters{ $lang }{templates}{$mtt} = {
                 message_transport_type => $letter->{message_transport_type},
                 is_html    => $letter->{is_html},
+                updated_on => $letter->{updated_on},
                 title      => $letter->{title},
                 content    => $letter->{content} // '',
             };
@@ -368,7 +369,7 @@ sub retrieve_letters {
 
     my $dbh = C4::Context->dbh;
     my ($sql, @where, @args);
-    $sql = "SELECT branchcode, module, code, name, branchname
+    $sql = "SELECT branchcode, module, code, name, branchname, MAX(updated_on) as updated_on
             FROM letter
             LEFT OUTER JOIN branches USING (branchcode)
     ";
-- 
2.11.0