From 9b2d65b86e7c3908215211d9e40609fa18e8c5c6 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. Run updatedatabase.pl and update_dbix_class_files.pl 2. Create a letter in Tools › Notices & slips for module "Serials". Fill the Email template. 3. 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 4. Run `misc/cronjobs/serialsUpdate.pl -v -c`. 5. 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 Sponsored-by: Écoles Nationales Supérieures d'Architecture --- C4/Serials.pm | 172 +++++++++--------- 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 | 19 ++ serials/subscription-add.pl | 23 ++- .../Acquisition/OrderFromSubscription.t | 12 +- t/db_dependent/Koha/Acquisition/Booksellers.t | 4 +- t/db_dependent/Letters.t | 12 +- t/db_dependent/Serials.t | 20 +- t/db_dependent/Serials/Claims.t | 48 ++--- t/db_dependent/Serials_2.t | 24 +-- 14 files changed, 270 insertions(+), 160 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug-29997.pl diff --git a/C4/Serials.pm b/C4/Serials.pm index 18f7318e50..85a4dcf9e4 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -1344,7 +1344,7 @@ sub ModSubscription { $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, $ccode, $published_on_template @@ -1353,45 +1353,46 @@ sub ModSubscription { my $subscription = Koha::Subscriptions->find($subscriptionid); $subscription->set( { - librarian => $auser, - branchcode => $branchcode, - aqbooksellerid => $aqbooksellerid, - cost => $cost, - aqbudgetid => $aqbudgetid, - biblionumber => $biblionumber, - startdate => $startdate, - periodicity => $periodicity, - numberlength => $numberlength, - weeklength => $weeklength, - monthlength => $monthlength, - lastvalue1 => $lastvalue1, - innerloop1 => $innerloop1, - lastvalue2 => $lastvalue2, - innerloop2 => $innerloop2, - lastvalue3 => $lastvalue3, - innerloop3 => $innerloop3, - status => $status, - notes => $notes, - letter => $letter, - firstacquidate => $firstacquidate, - irregularity => $irregularity, - numberpattern => $numberpattern, - locale => $locale, - callnumber => $callnumber, - manualhistory => $manualhistory, - internalnotes => $internalnotes, - serialsadditems => $serialsadditems, - staffdisplaycount => $staffdisplaycount, - opacdisplaycount => $opacdisplaycount, - graceperiod => $graceperiod, - location => $location, - enddate => $enddate, - skip_serialseq => $skip_serialseq, - itemtype => $itemtype, - previousitemtype => $previousitemtype, - mana_id => $mana_id, - ccode => $ccode, - published_on_template => $published_on_template, + librarian => $auser, + branchcode => $branchcode, + aqbooksellerid => $aqbooksellerid, + cost => $cost, + aqbudgetid => $aqbudgetid, + biblionumber => $biblionumber, + startdate => $startdate, + periodicity => $periodicity, + numberlength => $numberlength, + weeklength => $weeklength, + monthlength => $monthlength, + lastvalue1 => $lastvalue1, + innerloop1 => $innerloop1, + lastvalue2 => $lastvalue2, + innerloop2 => $innerloop2, + lastvalue3 => $lastvalue3, + innerloop3 => $innerloop3, + status => $status, + notes => $notes, + letter => $letter, + late_issue_letter_code => $late_issue_letter_code, + firstacquidate => $firstacquidate, + irregularity => $irregularity, + numberpattern => $numberpattern, + locale => $locale, + callnumber => $callnumber, + manualhistory => $manualhistory, + internalnotes => $internalnotes, + serialsadditems => $serialsadditems, + staffdisplaycount => $staffdisplaycount, + opacdisplaycount => $opacdisplaycount, + graceperiod => $graceperiod, + location => $location, + enddate => $enddate, + skip_serialseq => $skip_serialseq, + itemtype => $itemtype, + previousitemtype => $previousitemtype, + mana_id => $mana_id, + ccode => $ccode, + published_on_template => $published_on_template, } )->store; @@ -1407,10 +1408,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); @@ -1424,10 +1425,10 @@ the id of this new subscription sub NewSubscription { my ( - $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber, - $startdate, $periodicity, $numberlength, $weeklength, $monthlength, - $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3, - $innerloop3, $status, $notes, $letter, $firstacquidate, $irregularity, + $auser, $branchcode, $aqbooksellerid, $cost, $aqbudgetid, $biblionumber, + $startdate, $periodicity, $numberlength, $weeklength, $monthlength, + $lastvalue1, $innerloop1, $lastvalue2, $innerloop2, $lastvalue3, + $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, $ccode, @@ -1437,45 +1438,46 @@ sub NewSubscription { my $subscription = Koha::Subscription->new( { - librarian => $auser, - branchcode => $branchcode, - aqbooksellerid => $aqbooksellerid, - cost => $cost, - aqbudgetid => $aqbudgetid, - biblionumber => $biblionumber, - startdate => $startdate, - periodicity => $periodicity, - numberlength => $numberlength, - weeklength => $weeklength, - monthlength => $monthlength, - lastvalue1 => $lastvalue1, - innerloop1 => $innerloop1, - lastvalue2 => $lastvalue2, - innerloop2 => $innerloop2, - lastvalue3 => $lastvalue3, - innerloop3 => $innerloop3, - status => $status, - notes => $notes, - letter => $letter, - firstacquidate => $firstacquidate, - irregularity => $irregularity, - numberpattern => $numberpattern, - locale => $locale, - callnumber => $callnumber, - manualhistory => $manualhistory, - internalnotes => $internalnotes, - serialsadditems => $serialsadditems, - staffdisplaycount => $staffdisplaycount, - opacdisplaycount => $opacdisplaycount, - graceperiod => $graceperiod, - location => $location, - enddate => $enddate, - skip_serialseq => $skip_serialseq, - itemtype => $itemtype, - previousitemtype => $previousitemtype, - mana_id => $mana_id, - ccode => $ccode, - published_on_template => $published_on_template, + librarian => $auser, + branchcode => $branchcode, + aqbooksellerid => $aqbooksellerid, + cost => $cost, + aqbudgetid => $aqbudgetid, + biblionumber => $biblionumber, + startdate => $startdate, + periodicity => $periodicity, + numberlength => $numberlength, + weeklength => $weeklength, + monthlength => $monthlength, + lastvalue1 => $lastvalue1, + innerloop1 => $innerloop1, + lastvalue2 => $lastvalue2, + innerloop2 => $innerloop2, + lastvalue3 => $lastvalue3, + innerloop3 => $innerloop3, + status => $status, + notes => $notes, + letter => $letter, + late_issue_letter_code => $late_issue_letter_code, + firstacquidate => $firstacquidate, + irregularity => $irregularity, + numberpattern => $numberpattern, + locale => $locale, + callnumber => $callnumber, + manualhistory => $manualhistory, + internalnotes => $internalnotes, + serialsadditems => $serialsadditems, + staffdisplaycount => $staffdisplaycount, + opacdisplaycount => $opacdisplaycount, + graceperiod => $graceperiod, + location => $location, + enddate => $enddate, + skip_serialseq => $skip_serialseq, + itemtype => $itemtype, + previousitemtype => $previousitemtype, + mana_id => $mana_id, + ccode => $ccode, + published_on_template => $published_on_template, } )->store; $subscription->discard_changes; diff --git a/Koha/Subscription.pm b/Koha/Subscription.pm index c401cb89a2..6a3fb0c889 100644 --- a/Koha/Subscription.pm +++ b/Koha/Subscription.pm @@ -124,6 +124,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 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 5d7139383c..f3666be6a5 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -6144,7 +6144,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) 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 NULL COMMENT 'foreign key for aqbooksellers.id to link to the vendor', `cost` int(11) DEFAULT 0, @@ -6167,6 +6167,7 @@ CREATE TABLE `subscription` ( `irregularity` mediumtext DEFAULT NULL COMMENT 'any irregularities in the subscription', `skip_serialseq` tinyint(1) NOT NULL DEFAULT 0, `letter` varchar(20) 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) DEFAULT NULL COMMENT 'for foreign language subscriptions to display months, seasons, etc correctly', `distributedto` mediumtext DEFAULT NULL, @@ -6195,7 +6196,8 @@ CREATE TABLE `subscription` ( 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_4` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `subscription_ibfk_4` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL 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 1e62a1108e..f8695ee3d6 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 @@ -86,7 +86,6 @@ [% ELSE %] [% END %] -
Subscription details @@ -204,6 +203,25 @@
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 %] +