2 #-----------------------------------
3 # Script Name: overduenotices.pl
6 # Author: Stephen Hedges (shedges@skemotah.com)
7 # modified by Paul Poulain (paul@koha-fr.org)
8 # modified by Henri-Damien LAURENT (henridamien@koha-fr.org)
10 # This script runs a Koha report of items using overduerules tables and letters tool management.
12 # 1.0 2003/9/7: original version
13 # 1.5 2006/2/28: Modifications for managing Letters and overduerules
14 #-----------------------------------
15 # Copyright 2003 Skemotah Solutions
17 # This file is part of Koha.
19 # Koha is free software; you can redistribute it and/or modify it under the
20 # terms of the GNU General Public License as published by the Free Software
21 # Foundation; either version 2 of the License, or (at your option) any later
24 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
25 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
26 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
28 # You should have received a copy of the GNU General Public License along with
29 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
30 # Suite 330, Boston, MA 02111-1307 USA
34 # find Koha's Perl modules
35 # test carefully before changing this
37 eval { require "$FindBin::Bin/../kohalib.pl" };
40 use C4::Dates qw/format_date/;
41 use Mail::Sendmail; # comment out if not doing e-mail notices
44 my ($confirm, $nomail, $mybranch, $myborcat,$myborcatout, $letter, $MAX, $choice);
53 This script will send overdue notices by e-mail and prepare a file of\nnotices for printing if the borrower does not have e-mail.
54 You MUST edit this script for your library BEFORE you run it for the first time!
55 See the comments in the script for directions on changing the script.
56 This script has 2 parameters :
57 -c to confirm and remove this help & warning
58 -n to avoid sending any mail. Instead, all mail messages are printed on screen. Usefull for testing purposes.
59 -branch <branchcode> to select overdues for ONE specific branch.
60 -borcat <borcatcode> to select overdues for one borrower category,
61 -borcatout <borcatcode> to exclude this borrower category from overdunotices,
62 -max <MAX> MAXIMUM day count before stopping to send overdue notice,
63 -file <filename> to enter a specific filename to be read for message.
64 -all to include ALL the items that reader borrowed.
66 Do you wish to continue? (y/n)
69 exit unless (/^(y|Y|o|O)/i); # comment these lines out once you've made the changes
72 my $dbh = C4::Context->dbh;
73 my $rqoverduebranches=$dbh->prepare("SELECT DISTINCT branchcode FROM overduerules WHERE delay1>0");
74 $rqoverduebranches->execute;
75 while (my ($branchcode)=$rqoverduebranches->fetchrow){
76 warn "branchcode : $branchcode";
80 my $rqbranch=$dbh->prepare("SELECT * FROM branches WHERE branchcode = ?");
81 $rqbranch->execute($branchcode);
82 my $data = $rqbranch->fetchrow_hashref;
83 $emailaddress = $data->{branchemail};
84 $branchname = $data->{branchname};
86 $emailaddress=C4::Context->preference('KohaAdminEmailAddress') unless ($emailaddress);
88 #print STDERR "$emailaddress\n";
90 # BEGINNING OF PARAMETERS
92 my $rqoverduerules=$dbh->prepare("SELECT * FROM overduerules WHERE delay1>0 and branchcode = ?");
93 $rqoverduerules->execute($branchcode);
94 while (my $data=$rqoverduerules->fetchrow_hashref){
95 for (my $i=1; $i<=3;$i++){
99 my $mindays = $data->{"delay$i"}; # the notice will be sent after mindays days (grace period)
100 my $rqdebarring=$dbh->prepare("UPDATE borrowers SET debarred=1 WHERE borrowernumber=?") if $data->{"debarred$i"};
101 my $maxdays = ($data->{"delay".($i+1)}?
102 $data->{"delay".($i+1)}
103 :($MAX?$MAX:365)); # issues being more than maxdays late are managed somewhere else. (borrower probably suspended)
105 my $smtpserver = 'smtp.wanadoo.fr'; # your smtp server (the server who sent mails)
106 my $from = $emailaddress; # all the mails sent to the borrowers will appear coming from here.
107 my $mailtitle = 'Overdue'; # the title of the mails
108 $mailtitle = 'Issue status' if ($choice); # the title of the mails
109 my $librarymail = $emailaddress; # all notices without mail are sent (in 1 mail) to this mail address. They must then be managed manually.
110 my $letter = $data->{"letter$i"} if $data->{"letter$i"};
111 # this parameter (the last) is the text of the mail that is sent.
112 # this text contains fields that are replaced by their value. Those fields must be written between brackets
113 # The following fields are available :
114 # <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode>
115 my $mailtext=$letter;
119 open OUTFILE, ">overdues" or die "Cannot open file overdues: $!";
121 # set the e-mail server -- comment out if not doing e-mail notices
122 unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , $smtpserver;
123 # set your own mail server name here
125 my $strsth = "SELECT COUNT(*), issues.borrowernumber,firstname,surname,address,address2,city,zipcode, email, MIN(date_due) as longest_issue FROM issues,borrowers,categories WHERE returndate IS NULL AND issues.borrowernumber=borrowers.borrowernumber AND borrowers.categorycode=categories.categorycode ";
126 $strsth .= " AND issues.branchcode='".$branchcode."' " if ($branchcode);
127 $strsth .= " AND borrowers.categorycode='".$data->{categorycode}."' " if ($data->{categorycode});
128 $strsth .= " AND categories.overduenoticerequired=1 GROUP BY issues.borrowernumber HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) BETWEEN $mindays and $maxdays ";
129 my $sth = $dbh->prepare ($strsth);
131 my $sth2 = $dbh->prepare("SELECT biblio.title,biblio.author,items.barcode, issues.timestamp FROM issues,items,biblio WHERE items.itemnumber=issues.itemnumber and biblio.biblionumber=items.biblionumber AND issues.borrowernumber=? AND returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN $mindays and $maxdays");
137 my $count = 0; # to keep track of how many notices are printed
138 my $e_count = 0; # and e-mailed
139 my $date=format_date(localtime);
140 my ($itemcount,$borrowernumber,$firstname,$lastname,$address1,$address2,$city,$postcode,$email);
142 while (($itemcount, $borrowernumber, $firstname, $lastname, $address1, $address2, $city, $postcode, $email) = $sth->fetchrow) {
143 if ($data->{"debarred$i"}){
144 #action taken is debarring
145 $rqdebarring->execute($borrowernumber);
146 warn "debarring $borrowernumber $firstname $lastname";
149 my $notice .= $mailtext;
150 $notice =~ s/\<itemcount\>/$itemcount/g if ($itemcount);
151 $notice =~ s/\<firstname\>/$firstname/g if ($firstname);
152 $notice =~ s/\<lastname\>/$lastname/g if ($lastname);
153 $notice =~ s/\<address1\>/$address1/g if ($address1);
154 $notice =~ s/\<address2\>/$address2/g if ($address2);
155 $notice =~ s/\<city\>/$city/g if ($city);
156 $notice =~ s/\<postcode\>/$postcode/g if ($postcode);
157 $notice =~ s/\<date\>/$date/g if ($date);
158 $notice =~ s/\<bib\>/$branchname/g if ($branchname);
160 $sth2->execute($borrowernumber);
162 my ($title, $author, $barcode, $issuedate);
163 while (($title, $author, $barcode,$issuedate) = $sth2->fetchrow){
164 $titles .= " ".format_date($issuedate)." ".($barcode?$barcode:"")." ".($title?$title:"")." ".($author?$author:"")."\n";
166 $notice =~ s/\<titles\>/$titles/g;
168 # if not using e-mail notices, comment out the following lines
169 if ($email) { # or you might check for borrowers.preferredcont
171 print "TO => $email\n";
172 print "FROM => $from\n";
173 print "SUBJECT => $mailtitle\n";
174 print "MESSAGE => $notice\n";
176 my %mail = ( To => $email,
178 Subject => $mailtitle,
185 print OUTFILE $notice;
187 } # and comment this one out, too, if not using e-mail
192 # if some notices have to be printed & managed by the library, send them to library mail address.
194 open ODUES, "overdues" or die "Cannot open file overdues: $!";
195 my $notice = "$e_count overdue notices e-mailed\n";
196 $notice .= "$count overdue notices in file for printing\n\n";
200 print "TO => $email\n" if $email;
201 print "FROM => $from\n";
202 print "SUBJECT => Koha overdue\n";
203 print "MESSAGE => $notice\n";
205 my %mail = (To => $email,
207 Subject => 'Koha overdues',