From 35a8c9dbc9d2ec2e0cfb3805963241885d4889b9 Mon Sep 17 00:00:00 2001 From: Amit Gupta Date: Thu, 24 May 2012 10:12:33 +0530 Subject: [PATCH] Bug_6810 Send membership expiry reminder notices. A new crontab based perl script to send membership expiry reminders. A system preference controls the number of days in advance of membership expiry that the notices will be sent on. To Test: 1) Create a new Patron and set membership expiry date 14 days from the date of registration. 2) Check your systemprefence ( MemExpDayNotice to 14 days) 3) Manual testing Run ( perl membership_expiry.pl -h) It would give you various option: This script prepares for membership expiry reminders to be sent to patrons. It queues them in the message queue, which is processed by the process_message_queue.pl cronjob. See the comments in the script for directions on changing the script. This script has the following parameters : -c Confirm and remove this help & warning -n send No mail. Instead, all mail messages are printed on screen. Usefull for testing purposes. -v verbose Do you wish to continue? (y/n) 4) Choose option for ex: perl membership_expiry.pl -c 5) Go to your koha database and check message_queue table you see some results. 6) Run (perl process_message_queue.pl) it will send email to those patron whose membership after 14 days from today. 7) Cron testing: (10 1 * * * $KOHA_CRON_PATH/membership_expiry.pl -c) 8) Set your 15 * * * * $KOHA_CRON_PATH/process_message_queue.pl 9) After running membership_expiry.pl, (process_message_queue.pl will send emails to those patron whose membership after 14 days from today). --- C4/Letters.pm | 12 ++ C4/Members.pm | 21 +++ .../data/mysql/de-DE/mandatory/sample_notices.sql | 3 +- .../data/mysql/en/mandatory/sample_notices.sql | 3 +- .../data/mysql/es-ES/mandatory/sample_notices.sql | 3 +- .../mysql/fr-FR/1-Obligatoire/sample_notices.sql | 3 +- installer/data/mysql/it-IT/necessari/notices.sql | 3 +- .../mysql/nb-NO/1-Obligatorisk/sample_notices.sql | 3 +- .../data/mysql/pl-PL/mandatory/sample_notices.sql | 3 +- .../data/mysql/ru-RU/mandatory/sample_notices.sql | 3 +- installer/data/mysql/sysprefs.sql | 1 + .../data/mysql/uk-UA/mandatory/sample_notices.sql | 3 +- installer/data/mysql/updatedatabase.pl | 8 ++ .../prog/en/modules/admin/preferences/patrons.pref | 5 + misc/cronjobs/membership_expiry.pl | 133 ++++++++++++++++++++ 15 files changed, 198 insertions(+), 9 deletions(-) create mode 100755 misc/cronjobs/membership_expiry.pl diff --git a/C4/Letters.pm b/C4/Letters.pm index 89213fe..2e4b76d 100644 --- a/C4/Letters.pm +++ b/C4/Letters.pm @@ -590,6 +590,18 @@ my %columns = (); sub _parseletter { my ( $letter, $table, $values ) = @_; + if ( $table eq 'borrowers' && $values->{'dateexpiry'} ){ + my @dateexpiry = split /-/, $values->{'dateexpiry'}; + + $values->{'dateexpiry'} = C4::Dates->new( + sprintf( + '%04d-%02d-%02d', + Add_Delta_Days( @dateexpiry,0) + ), + 'iso' + )->output(); + } + # TEMPORARY hack until the expirationdate column is added to reserves if ( $table eq 'reserves' && $values->{'waitingdate'} ) { my @waitingdate = split /-/, $values->{'waitingdate'}; diff --git a/C4/Members.pm b/C4/Members.pm index f5be65e..e4c8d73 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -92,6 +92,7 @@ BEGIN { &GetBorrowersWithIssuesHistoryOlderThan &GetExpiryDate + &GetUpcomingMembershipExpires &AddMessage &DeleteMessage @@ -2237,7 +2238,27 @@ sub GetMessagesCount { return $count; } +=head2 GetUpcomingMembershipExpires + + my $upcoming_mem_expires = GetUpcomingMembershipExpires(); +=cut + +sub GetUpcomingMembershipExpires { + my $dbh = C4::Context->dbh; + my $days = C4::Context->preference("MemExpDaysNotice"); + my $query = " + SELECT borrowers.*, categories.description, + branches.branchname, branches.branchemail FROM borrowers + LEFT JOIN branches on borrowers.branchcode = branches.branchcode + LEFT JOIN categories on borrowers.categorycode = categories.categorycode + WHERE dateexpiry = DATE_ADD(CURDATE(),INTERVAL $days DAY); + "; + my $sth = $dbh->prepare($query); + $sth->execute; + my $results = $sth->fetchall_arrayref({}); + return $results; +} =head2 DeleteMessage diff --git a/installer/data/mysql/de-DE/mandatory/sample_notices.sql b/installer/data/mysql/de-DE/mandatory/sample_notices.sql index f668d99..9372a3d 100644 --- a/installer/data/mysql/de-DE/mandatory/sample_notices.sql +++ b/installer/data/mysql/de-DE/mandatory/sample_notices.sql @@ -15,4 +15,5 @@ VALUES ('circulation','ODUE','Mahnung','Mahnung','Liebe/r < ('suggestions','ACCEPTED','Anschaffungsvorschlag wurde angenommen', 'Ihr Anschaffungsvorschlag wurde angenommen','Liebe(r) <> <>,\n\nSie haben der Bibliothek folgendes Medium zur Anschaffung vorgeschlagen: <> by <>.\n\nDie Bibliothek hat diesen Titel heute recherchiert und wird Ihn sobald wie möglich im Buchhandel bestellen. Sie erhalten Nachricht, sobald die Bestellung abgeschlossen ist und sobald der Titel in der Bibliotek verfügbar ist.\n\nWenn Sie Fragen haben, richten Sie Ihre Mail bitte an: <>.\n\nVielen Dank,\n\n<>'), ('suggestions','AVAILABLE','Vorgeschlagenes Medium verfügbar', 'Das vorgeschlagene Medium ist jetzt verfügbar','Liebe(r) <> <>,\n\nSie haben der Bibliothek folgendes Medium zur Anschaffung vorgeschlagen: <> von <>.\n\nWir freuen uns Ihnen mitteilen zu können, dass dieser Titel jetzt im Bestand der Bibliothek verfügbar ist.\n\nWenn Sie Fragen haben, richten Sie Ihre Mail bitte an: <>.\n\nVielen Dank,\n\n<>'), ('suggestions','ORDERED','Vorgeschlagenes Medium bestellt', 'Das vorgeschlagene Medium wurde im Buchhandel bestellt','Liebe(r) <> <>,\n\nSie haben der Bibliothek folgendes Medium zur Anschaffung vorgeschlaten: <> von <>.\n\nWir freuen uns Ihnen mitteilen zu können, dass dieser Titel jetzt im Buchhandel bestellt wurde. Nach Eintreffen wird er in unseren Bestand eingearbeitet.\n\nSie erhalten Nachricht, sobald das Medium verfügbar ist.\n\nBei Nachfragen erreichen Sie uns unter der Emailadresse <>.\n\nVielen Dank,\n\n<>'), -('suggestions','REJECTED','Anschaffungsvorschlag nicht angenommen', 'Ihr Anschaffungsvorschlag wurde nicht angenommen','Liebe(r) <> <>,\n\nSie haven der Bibliothek folgendes Medium zur Anschaffung vorgeschlagen: <> von <>.\n\nDie Bibliothek hat diesen Titel heute recherchiert und sich gegen eine Anschaffung entschieden.\n\nBegründung: <>\n\nWenn Sie Fragen haben, richten Sie Ihre Mail bitte an: <>.\n\nVielen Dank,\n\n<>'); +('suggestions','REJECTED','Anschaffungsvorschlag nicht angenommen', 'Ihr Anschaffungsvorschlag wurde nicht angenommen','Liebe(r) <> <>,\n\nSie haven der Bibliothek folgendes Medium zur Anschaffung vorgeschlagen: <> von <>.\n\nDie Bibliothek hat diesen Titel heute recherchiert und sich gegen eine Anschaffung entschieden.\n\nBegründung: <>\n\nWenn Sie Fragen haben, richten Sie Ihre Mail bitte an: <>.\n\nVielen Dank,\n\n<>'), +('members','MEMEXP','Membership Expiry Notice','Membership Expiry Notice','Dear <> <> <>,.\r\n\r\nYour membership expires soon on:\r\n\r\n<>\r\n\r\nThank you,\r\n\r\n<>'); diff --git a/installer/data/mysql/en/mandatory/sample_notices.sql b/installer/data/mysql/en/mandatory/sample_notices.sql index 6fa0a54..c065df2 100644 --- a/installer/data/mysql/en/mandatory/sample_notices.sql +++ b/installer/data/mysql/en/mandatory/sample_notices.sql @@ -104,4 +104,5 @@ Date due: <>
  • <>
  • <>
  • -
', 1); +', 1), +('members','MEMEXP','Membership Expiry Notice','Membership Expiry Notice','Dear <> <> <>,.\r\n\r\nYour membership expires soon on:\r\n\r\n<>\r\n\r\nThank you,\r\n\r\n<>'); diff --git a/installer/data/mysql/es-ES/mandatory/sample_notices.sql b/installer/data/mysql/es-ES/mandatory/sample_notices.sql index 0450bd0..8a0e50f 100644 --- a/installer/data/mysql/es-ES/mandatory/sample_notices.sql +++ b/installer/data/mysql/es-ES/mandatory/sample_notices.sql @@ -15,4 +15,5 @@ VALUES ('circulation','ODUE','Overdue Notice','Item Overdue','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nThe library has reviewed your suggestion today. The item will be ordered as soon as possible. You will be notified by mail when the order is completed, and again when the item arrives at the library.\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'), ('suggestions','AVAILABLE','Suggestion available', 'Suggested purchase available','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nWe are pleased to inform you that the item you requested is now part of the collection.\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'), ('suggestions','ORDERED','Suggestion ordered', 'Suggested item ordered','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nWe are pleased to inform you that the item you requested has now been ordered. It should arrive soon, at which time it will be processed for addition into the collection.\n\nYou will be notified again when the book is available.\n\nIf you have any questions, please email us at <>\n\nThank you,\n\n<>'), -('suggestions','REJECTED','Suggestion rejected', 'Purchase suggestion declined','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nThe library has reviewed your request today, and has decided not to accept the suggestion at this time.\n\nThe reason given is: <>\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'); +('suggestions','REJECTED','Suggestion rejected', 'Purchase suggestion declined','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nThe library has reviewed your request today, and has decided not to accept the suggestion at this time.\n\nThe reason given is: <>\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'), +('members','MEMEXP','Membership Expiry Notice','Membership Expiry Notice','Dear <> <> <>,.\r\n\r\nYour membership expires soon on:\r\n\r\n<>\r\n\r\nThank you,\r\n\r\n<>'); diff --git a/installer/data/mysql/fr-FR/1-Obligatoire/sample_notices.sql b/installer/data/mysql/fr-FR/1-Obligatoire/sample_notices.sql index 1056d14..7a4aedc 100644 --- a/installer/data/mysql/fr-FR/1-Obligatoire/sample_notices.sql +++ b/installer/data/mysql/fr-FR/1-Obligatoire/sample_notices.sql @@ -17,4 +17,5 @@ VALUES ('suggestions','ACCEPTED','Suggestion accceptée', 'Suggestion acceptée','Cher(e) <> <>,\n\nVous avez crée une suggestion d\'achat au sujet du document <> de <>.\n\nLa Bibliothèque a reçu votre demande ce jour. Nous donnerons suite à votre demande aussi vite que possible. Vous serez averti par courriel dès que la commande sera envoyée,et quand les documents seront arrivés à la Bibliothèque.\n\nSi vous avez des questions, merci de nous contacter à l\'adresse suivante <>.\n\nMerci,\n\n<>'), ('suggestions','AVAILABLE','Suggestion disponible', 'Suggestion d\'achat disponible','cher(e) <> <>,\n\nVous avez effectué une suggestion d\'achat pour le docuement <> de <>.\n\nNous sommes heureux de vous informer que le document que vous aviez demandé est maintenant disponible dans nos collections.\n\nSi vous avez des questions, merci de nous contacter par courriel à l\'adresse <>.\n\nMerci,\n\n<>'), ('suggestions','ORDERED','Suggestion commandée', 'Suggestion commandée','Cher(e) <> <>,\n\nVous avez effectué une demande de suggestion d\'achat sur le docuement <> de <>.\n\nNous sommes heureux de vous informer que le document que vous avez demandé est maintenant en commande. Le document devrait arriver rapidement dans nos collections.\n\nVous serez averti quand le docuement sera disponible.\n\nSi vous avez des questions, merci de nous contacter à l\'adresse <>\n\nMerci,\n\n<>'), -('suggestions','REJECTED','Suggestion rejetée', 'Suggestion d\'achat rejeté','Cher(e) <> <>,\n\nVous avez fait la demande du document <> de <>.\n\nla Bibliothèque a examiné votre demande ce jour, et a décidé de ne pas retenir la suggestion pour l\'instant.\n\nLa raison est la suivante: <>\n\nSi vous avez des questions, merci de nous contacter à l\'adresse <>.\n\nMerci,\n\n<>'); +('suggestions','REJECTED','Suggestion rejetée', 'Suggestion d\'achat rejeté','Cher(e) <> <>,\n\nVous avez fait la demande du document <> de <>.\n\nla Bibliothèque a examiné votre demande ce jour, et a décidé de ne pas retenir la suggestion pour l\'instant.\n\nLa raison est la suivante: <>\n\nSi vous avez des questions, merci de nous contacter à l\'adresse <>.\n\nMerci,\n\n<>'), +('members','MEMEXP','Membership Expiry Notice','Membership Expiry Notice','Dear <> <> <>,.\r\n\r\nYour membership expires soon on:\r\n\r\n<>\r\n\r\nThank you,\r\n\r\n<>'); diff --git a/installer/data/mysql/it-IT/necessari/notices.sql b/installer/data/mysql/it-IT/necessari/notices.sql index 5b45d32..2e3b516 100644 --- a/installer/data/mysql/it-IT/necessari/notices.sql +++ b/installer/data/mysql/it-IT/necessari/notices.sql @@ -104,4 +104,5 @@ Date due: <>
  • <>
  • <>
  • -
', 1); \ No newline at end of file +', 1), +('members','MEMEXP','Membership Expiry Notice','Membership Expiry Notice','Dear <> <> <>,.\r\n\r\nYour membership expires soon on:\r\n\r\n<>\r\n\r\nThank you,\r\n\r\n<>'); diff --git a/installer/data/mysql/nb-NO/1-Obligatorisk/sample_notices.sql b/installer/data/mysql/nb-NO/1-Obligatorisk/sample_notices.sql index f40da36..2a0ff5c 100644 --- a/installer/data/mysql/nb-NO/1-Obligatorisk/sample_notices.sql +++ b/installer/data/mysql/nb-NO/1-Obligatorisk/sample_notices.sql @@ -36,4 +36,5 @@ VALUES ('circulation','ODUE','Purring','Purring på dokument','<> <>,\n\nDu har foreslått at biblioteket kjøper inn <> av <>.\n\nBiblioteket har vurdert forslaget i dag. Dokumentet vil bli bestilt så fort det lar seg gjøre. Du vil få en ny melding når bestillingen er gjort, og når dokumentet ankommer biblioteket.\n\nEr det noe du lurer på, vennligst kontakt oss på <>.\n\nVennlig hilsen,\n\n<>'), ('suggestions','AVAILABLE','Foreslått dokument tilgjengelig', 'Foreslått dokument tilgjengelig','<> <>,\n\nDu har foreslått at biblioteket kjøper inn <> av <>.\n\nVi har gleden av å informere deg om at dokumentet nå er innlemmet i samlingen.\n\nEr det noe du lurer på, vennligst kontakt oss på <>.\n\nVennlig hilsen,\n\n<>'), ('suggestions','ORDERED','Innkjøpsforslag i bestilling', 'Innkjøpsforslag i bestilling','Dear <> <>,\n\nDu har foreslått at biblioteket kjøper inn <> av <>.\n\nVi har gleden av å informere deg om at dokumentet du foreslo nå er i bestilling.\n\nDu vil få en ny melding når dokumentet er tilgjengelig.\n\nEr det noe du lurer på, vennligst kontakt oss på <>.\n\nVennlig hilsen,\n\n<>'), -('suggestions','REJECTED','Innkjøpsforslag avslått', 'Innkjøpsforslag avslått','<> <>,\n\nDu har foreslått at biblioteket kjøper inn <> av <>.\n\nBiblioteket har vurdert innkjøpsforslaget ditt i dag, og bestemt seg for å ikke ta det til følge.\n\nBegrunnelse: <>\n\nEr det noe du lurer på, vennligst kontakt oss på <>.\n\nVennlig hilsen,\n\n<>'); +('suggestions','REJECTED','Innkjøpsforslag avslått', 'Innkjøpsforslag avslått','<> <>,\n\nDu har foreslått at biblioteket kjøper inn <> av <>.\n\nBiblioteket har vurdert innkjøpsforslaget ditt i dag, og bestemt seg for å ikke ta det til følge.\n\nBegrunnelse: <>\n\nEr det noe du lurer på, vennligst kontakt oss på <>.\n\nVennlig hilsen,\n\n<>'), +('members','MEMEXP','Membership Expiry Notice','Membership Expiry Notice','Dear <> <> <>,.\r\n\r\nYour membership expires soon on:\r\n\r\n<>\r\n\r\nThank you,\r\n\r\n<>'); diff --git a/installer/data/mysql/pl-PL/mandatory/sample_notices.sql b/installer/data/mysql/pl-PL/mandatory/sample_notices.sql index 73102af..6c477c8 100644 --- a/installer/data/mysql/pl-PL/mandatory/sample_notices.sql +++ b/installer/data/mysql/pl-PL/mandatory/sample_notices.sql @@ -17,4 +17,5 @@ VALUES ('suggestions','ACCEPTED','Suggestion accepted', 'Purchase suggestion accepted','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nThe library has reviewed your suggestion today. The item will be ordered as soon as possible. You will be notified by mail when the order is completed, and again when the item arrives at the library.\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'), ('suggestions','AVAILABLE','Suggestion available', 'Suggested purchase available','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nWe are pleased to inform you that the item you requested is now part of the collection.\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'), ('suggestions','ORDERED','Suggestion ordered', 'Suggested item ordered','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nWe are pleased to inform you that the item you requested has now been ordered. It should arrive soon, at which time it will be processed for addition into the collection.\n\nYou will be notified again when the book is available.\n\nIf you have any questions, please email us at <>\n\nThank you,\n\n<>'), -('suggestions','REJECTED','Suggestion rejected', 'Purchase suggestion declined','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nThe library has reviewed your request today, and has decided not to accept the suggestion at this time.\n\nThe reason given is: <>\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'); +('suggestions','REJECTED','Suggestion rejected', 'Purchase suggestion declined','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nThe library has reviewed your request today, and has decided not to accept the suggestion at this time.\n\nThe reason given is: <>\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'), +('members','MEMEXP','Membership Expiry Notice','Membership Expiry Notice','Dear <> <> <>,.\r\n\r\nYour membership expires soon on:\r\n\r\n<>\r\n\r\nThank you,\r\n\r\n<>'); diff --git a/installer/data/mysql/ru-RU/mandatory/sample_notices.sql b/installer/data/mysql/ru-RU/mandatory/sample_notices.sql index e13782a..e594c54 100644 --- a/installer/data/mysql/ru-RU/mandatory/sample_notices.sql +++ b/installer/data/mysql/ru-RU/mandatory/sample_notices.sql @@ -15,4 +15,5 @@ VALUES ('circulation','ODUE','Overdue Notice','Item Overdue','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nThe library has reviewed your suggestion today. The item will be ordered as soon as possible. You will be notified by mail when the order is completed, and again when the item arrives at the library.\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'), ('suggestions','AVAILABLE','Suggestion available', 'Suggested purchase available','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nWe are pleased to inform you that the item you requested is now part of the collection.\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'), ('suggestions','ORDERED','Suggestion ordered', 'Suggested item ordered','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nWe are pleased to inform you that the item you requested has now been ordered. It should arrive soon, at which time it will be processed for addition into the collection.\n\nYou will be notified again when the book is available.\n\nIf you have any questions, please email us at <>\n\nThank you,\n\n<>'), -('suggestions','REJECTED','Suggestion rejected', 'Purchase suggestion declined','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nThe library has reviewed your request today, and has decided not to accept the suggestion at this time.\n\nThe reason given is: <>\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'); +('suggestions','REJECTED','Suggestion rejected', 'Purchase suggestion declined','Dear <> <>,\n\nYou have suggested that the library acquire <> by <>.\n\nThe library has reviewed your request today, and has decided not to accept the suggestion at this time.\n\nThe reason given is: <>\n\nIf you have any questions, please email us at <>.\n\nThank you,\n\n<>'), +('members','MEMEXP','Membership Expiry Notice','Membership Expiry Notice','Dear <> <> <>,.\r\n\r\nYour membership expires soon on:\r\n\r\n<>\r\n\r\nThank you,\r\n\r\n<>'); diff --git a/installer/data/mysql/sysprefs.sql b/installer/data/mysql/sysprefs.sql index 1edf08e..41959b0 100644 --- a/installer/data/mysql/sysprefs.sql +++ b/installer/data/mysql/sysprefs.sql @@ -48,6 +48,7 @@ INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('IntranetUserCSS','','Add CSS to be included in the intranet in an embedded