From 295cf6d96c2ea338acf0850ef237e561dd772251 Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Mon, 22 Nov 2021 15:14:53 +0000 Subject: [PATCH] Bug 29997: Allow to send email notification when a serial issue is late MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch adds the ability to define, for each subscription, a letter that will be sent whenever an issue is automatically marked as late by the script misc/cronjobs/serialsUpdate.pl. If no letter is defined for a subscription, then no letter will be sent. The letter will be sent to the subscription's manager (the staff member who created or last modified the subscription). Test plan: 1. Create a letter in Tools › Notices & slips for module "Serials". Fill the Email template. 2. Create a new subscription, with a grace period of 1 day, and set the new field "Late issue notification" to the letter you just created. Set the first issue publication date and subscription start date to two days ago 3. Run `misc/cronjobs/serialsUpdate.pl -v -c`. 4. The first serial issue of your subscription should have been automatically marked as late. Now verify that the table message_queue contains a new message corresponding to this serial issue. The message's contents should be what you defined in step 1. The message should be addressed to the subscription's manager (you) Technical note: The "subscription" table contained a column named "librarian", which contained the userid of the patron who created or last modified the subscription, but there was no foreign key constraint referencing the borrowers table. So this patch also adds a new column "manager_id" which contains the borrowernumber of the patron who created or last modified the subscription, and it also adds the foreign key constraint. The atomicupdate script tries to fill the new "manager_id" column by searching patrons by their "userid", which might fail, so it's normal to have some NULL values in "manager_id" at the end. The "librarian" column is removed at the end of the script --- C4/Serials.pm | 18 ++++---- Koha/Subscription.pm | 17 +++++++ .../data/mysql/atomicupdate/bug-29997.pl | 46 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 6 ++- .../en/modules/serials/subscription-add.tt | 20 +++++++- .../en/modules/serials/subscription-detail.tt | 7 ++- misc/cronjobs/serialsUpdate.pl | 33 +++++++++++-- serials/subscription-add.pl | 13 +++--- serials/subscription-detail.pl | 1 + .../Acquisition/OrderFromSubscription.t | 2 +- t/db_dependent/Koha/Acquisition/Booksellers.t | 4 +- t/db_dependent/Letters.t | 4 +- t/db_dependent/Serials.t | 20 ++++---- t/db_dependent/Serials/Claims.t | 8 ++-- t/db_dependent/Serials_2.t | 4 +- 15 files changed, 159 insertions(+), 44 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug-29997.pl diff --git a/C4/Serials.pm b/C4/Serials.pm index acc387f4f7..4ef07ce02b 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -1312,11 +1312,11 @@ returns the number of rows affected sub ModSubscription { my ( - $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $startdate, + $manager_id, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $startdate, $periodicity, $firstacquidate, $irregularity, $numberpattern, $locale, $numberlength, $weeklength, $monthlength, $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3, $innerloop3, $status, - $biblionumber, $callnumber, $notes, $letter, $manualhistory, + $biblionumber, $callnumber, $notes, $letter, $late_issue_letter_code, $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $subscriptionid, $skip_serialseq, $itemtype, $previousitemtype, $mana_id @@ -1325,7 +1325,7 @@ sub ModSubscription { my $subscription = Koha::Subscriptions->find($subscriptionid); $subscription->set( { - librarian => $auser, + manager_id => $manager_id, branchcode => $branchcode, aqbooksellerid => $aqbooksellerid, cost => $cost, @@ -1345,6 +1345,7 @@ sub ModSubscription { status => $status, notes => $notes, letter => $letter, + late_issue_letter_code => $late_issue_letter_code, firstacquidate => $firstacquidate, irregularity => $irregularity, numberpattern => $numberpattern, @@ -1376,10 +1377,10 @@ sub ModSubscription { =head2 NewSubscription -$subscriptionid = &NewSubscription($auser,branchcode,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber, +$subscriptionid = &NewSubscription($manager_id,branchcode,$aqbooksellerid,$cost,$aqbudgetid,$biblionumber, $startdate,$periodicity,$numberlength,$weeklength,$monthlength, $lastvalue1,$innerloop1,$lastvalue2,$innerloop2,$lastvalue3,$innerloop3, - $status, $notes, $letter, $firstacquidate, $irregularity, $numberpattern, + $status, $notes, $letter, $late_issue_letter_code, $firstacquidate, $irregularity, $numberpattern, $locale, $callnumber, $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq, $itemtype, $previousitemtype); @@ -1393,10 +1394,10 @@ the id of this new subscription sub NewSubscription { my ( - $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber, + $manager_id, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber, $startdate, $periodicity, $numberlength, $weeklength, $monthlength, $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3, - $innerloop3, $status, $notes, $letter, $firstacquidate, $irregularity, + $innerloop3, $status, $notes, $letter, $late_issue_letter_code, $firstacquidate, $irregularity, $numberpattern, $locale, $callnumber, $manualhistory, $internalnotes, $serialsadditems, $staffdisplaycount, $opacdisplaycount, $graceperiod, $location, $enddate, $skip_serialseq, $itemtype, $previousitemtype, $mana_id @@ -1405,7 +1406,7 @@ sub NewSubscription { my $subscription = Koha::Subscription->new( { - librarian => $auser, + manager_id => $manager_id, branchcode => $branchcode, aqbooksellerid => $aqbooksellerid, cost => $cost, @@ -1425,6 +1426,7 @@ sub NewSubscription { status => $status, notes => $notes, letter => $letter, + late_issue_letter_code => $late_issue_letter_code, firstacquidate => $firstacquidate, irregularity => $irregularity, numberpattern => $numberpattern, diff --git a/Koha/Subscription.pm b/Koha/Subscription.pm index 45d1c09fa0..702da53963 100644 --- a/Koha/Subscription.pm +++ b/Koha/Subscription.pm @@ -123,6 +123,23 @@ sub frequency { return Koha::Subscription::Frequency->_new_from_dbic($frequency_rs); } +=head3 manager + +my $patron = $subscription->manager + +Return the subscription manager (a Koha::Patron object) + +=cut + +sub manager { + my ($self) = @_; + + my $manager = $self->_result->manager; + return undef unless $manager; + + return Koha::Patron->_new_from_dbic($manager); +} + =head3 get_search_info =cut diff --git a/installer/data/mysql/atomicupdate/bug-29997.pl b/installer/data/mysql/atomicupdate/bug-29997.pl new file mode 100644 index 0000000000..f479c5927e --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug-29997.pl @@ -0,0 +1,46 @@ +use Modern::Perl; + +return { + bug_number => "29997", + description => "Replace subscription.librarian by subscription.manager_id, add a foreign key constraint, and add subscription.late_issue_letter_code", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + + unless (column_exists('subscription', 'manager_id')) { + $dbh->do(q{ + ALTER TABLE subscription + ADD COLUMN manager_id INT NULL DEFAULT NULL AFTER librarian + }); + } + + unless (foreign_key_exists('subscription', 'subscription_manager_id_fk')) { + $dbh->do(q{ + ALTER TABLE subscription + ADD CONSTRAINT subscription_manager_id_fk + FOREIGN KEY (manager_id) REFERENCES borrowers (borrowernumber) + ON DELETE SET NULL ON UPDATE CASCADE + }); + } + + if (column_exists('subscription', 'librarian')) { + $dbh->do(q{ + UPDATE subscription LEFT JOIN borrowers ON (borrowers.userid = subscription.librarian) + SET subscription.manager_id = borrowers.borrowernumber + WHERE subscription.manager_id IS NULL + }); + + $dbh->do(q{ + ALTER TABLE subscription + DROP COLUMN librarian + }); + } + + unless (column_exists('subscription', 'late_issue_letter_code')) { + $dbh->do(q{ + ALTER TABLE subscription + ADD COLUMN late_issue_letter_code VARCHAR(20) NULL DEFAULT NULL AFTER letter + }); + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 0aab2ff94e..8b8cad5d34 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4786,7 +4786,7 @@ DROP TABLE IF EXISTS `subscription`; CREATE TABLE `subscription` ( `biblionumber` int(11) NOT NULL COMMENT 'foreign key for biblio.biblionumber that this subscription is attached to', `subscriptionid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for this subscription', - `librarian` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'the librarian''s username from borrowers.userid', + `manager_id` INT NULL DEFAULT NULL COMMENT 'the borrowernumber of the patron that last modified the subscription', `startdate` date DEFAULT NULL COMMENT 'start date for this subscription', `aqbooksellerid` int(11) DEFAULT 0 COMMENT 'foreign key for aqbooksellers.id to link to the vendor', `cost` int(11) DEFAULT 0, @@ -4809,6 +4809,7 @@ CREATE TABLE `subscription` ( `irregularity` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'any irregularities in the subscription', `skip_serialseq` tinyint(1) NOT NULL DEFAULT 0, `letter` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `late_issue_letter_code` VARCHAR(20) NULL DEFAULT NULL COMMENT 'letter code used for late issue alerts', `numberpattern` int(11) DEFAULT NULL COMMENT 'the numbering pattern used links to subscription_numberpatterns.id', `locale` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'for foreign language subscriptions to display months, seasons, etc correctly', `distributedto` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL, @@ -4833,7 +4834,8 @@ CREATE TABLE `subscription` ( KEY `subscription_ibfk_3` (`biblionumber`), CONSTRAINT `subscription_ibfk_1` FOREIGN KEY (`periodicity`) REFERENCES `subscription_frequencies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `subscription_ibfk_2` FOREIGN KEY (`numberpattern`) REFERENCES `subscription_numberpatterns` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `subscription_ibfk_3` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `subscription_ibfk_3` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `subscription_manager_id_fk` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/subscription-add.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/subscription-add.tt index 6c22b7cdd1..630da6efde 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/subscription-add.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/subscription-add.tt @@ -75,7 +75,6 @@ fieldset.rows table { clear: none; margin: 0; } [% ELSE %] [% END %] -
Subscription details @@ -186,6 +185,25 @@ fieldset.rows table { clear: none; margin: 0; }
To notify patrons of new serial issues, you must define a notice.
[% END %] +
  • + [% IF ( letterloop ) %] + + +
    Selecting a notice will allow you to receive a notification when a serial issue is late.
    + [% ELSE %] + Late issue notification: +
    To receive notifications of late serial issues, you must define a notice.
    + [% END %] +