2 #-----------------------------------
3 # Script Name: overduenotices.pl
6 # Author: Stephen Hedges (shedges@skemotah.com)
8 # This script runs a Koha report of items that
9 # are between 7 and 30 days overdue and generates
10 # a file that may be dumped to a printer. The time period
11 # may be changed by editing the SQL statement handle
12 # prepared in line 52. The actual wording of the overdue
13 # notices may be changed by editing the $notice variable
14 # in line 101. The current notice text is formatted to
15 # fit the standard 34-line 'Speedimailer' form.
17 # 1.0 2003/9/7: original version
18 #-----------------------------------
19 # Copyright 2003 Skemotah Solutions
21 # This file is part of Koha.
23 # Koha is free software; you can redistribute it and/or modify it under the
24 # terms of the GNU General Public License as published by the Free Software
25 # Foundation; either version 2 of the License, or (at your option) any later
28 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
29 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
30 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
32 # You should have received a copy of the GNU General Public License along with
33 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
34 # Suite 330, Boston, MA 02111-1307 USA
38 print "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.\nYou MUST edit this script for your library BEFORE you run it for the first time!\nSee the comments in the script for directions on changing the script.\n\nDo you wish to continue? (y/n) ";
40 die unless (/^y/i); # comment these lines out once you've made the changes
42 open OUTFILE, ">overdues" or die "Cannot open file overdues: $!";
45 use Mail::Sendmail; # comment out if not doing e-mail notices
47 # set the e-mail server -- comment out if not doing e-mail notices
48 unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'alma.athenscounty.lib.oh.us';
49 # set your own mail server name here
51 my $dbh = C4::Context->dbh;
52 my $sth = $dbh->prepare ("SELECT issues.borrowernumber,firstname,surname,streetaddress,physstreet,city,zipcode,emailaddress FROM issues,borrowers WHERE returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 7 and 30 AND issues.borrowernumber=borrowers.borrowernumber ORDER BY issues.borrowernumber");
53 my $first_borrno = $dbh->prepare ("SELECT borrowernumber FROM issues WHERE returndate IS NULL AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 7 and 30 ORDER BY borrowernumber");
54 my $get_date = $dbh->prepare ("SELECT CURDATE()");
57 my $daydate = $get_date->fetchrow_arrayref;
58 my $rawdate = $daydate->[0];
59 my @dates = split /-/, $rawdate; # split and reformat date
60 my $date = "$dates[1]/$dates[2]/$dates[0]";
63 $first_borrno->execute; # get first borrowernumber
64 my $firstborr = $first_borrno->fetchrow_arrayref;
65 my $borrowernumber = $firstborr->[0];
66 $first_borrno->finish;
72 my $count = 0; # to keep track of how many notices are printed
73 my $e_count = 0; # and e-mailed
74 my ($firstname,$lastname,$address1,$address2,$city,$postcode,$email);
76 while ($row = $sth->fetchrow_arrayref) {
77 my $borrno = $row->[0];
78 if ($itemcount==0) { # store values for first borrower
79 $firstname = $row->[1];
80 $lastname = $row->[2];
81 $address1 = $row->[3];
82 $address2 = $row->[4];
90 $postcode = $row->[6];
96 if ($borrno == $borrowernumber) { # next borrower yet?
100 $borrowernumber = $borrno;
101 my $notice = "\n\n\n Athens County Library Services\n 95 W. Washington Street\n Nelsonville, OH 45674\n\n\n $date\n\n According to our records, you have $itemcount items that are at\n least a week overdue for return to the library or renewal.\n If you have registered a password with the library, you may use it\n and your library card to login at http://koha.athenscounty.lib.oh.us\n to check the status of your account, or you may call any of the\n Athens County public libraries. (Athens: 592-4272;\n Nelsonville: 753-2118; The Plains: 797-4579; Albany: 698-3059;\n Glouster: 767-3670; Coolville: 667-3354; and Chauncey: 797-2512)\n Please be advised that all library services will be blocked\n if items are allowed to go more than 30 days overdue.\n\n Thank you for using your public libraries.\n\n\n $firstname $lastname\n $address1\n $address2\n $city $postcode\n\n\n\n\n\n";
103 # if not using e-mail notices, comment out the following lines
104 if ($email) { # or you might check for borrowers.preferredcont
105 my %mail = ( To => $email,
106 From => 'nelpl@athenscounty.lib.oh.us',
107 Subject => 'Overdue library items',
113 # if not using e-mail notices, comment out the above lines
116 print OUTFILE $notice;
118 } # and comment this one out, too, if not using e-mail
120 $itemcount = 1; #start the count for next notice
121 $firstname = $row->[1]; # and store the new values
122 $lastname = $row->[2];
123 $address1 = $row->[3];
124 $address2 = $row->[4];
132 $postcode = $row->[6];
142 print "$e_count overdue notices e-mailed\n";
143 print "$count overdue notices in file for printing\n\n";