2 #-----------------------------------
3 # Script Name: overduenotices.pl
6 # Author: Stephen Hedges (shedges@skemotah.com)
7 # modified by Paul Poulain (paul@koha-fr.org)
9 # This script runs send a mail with an attached file of all overdues
10 # that can be used for overdues claims, with your preffered word processor
11 # (OpenOffice.org hopefully ;-) )
14 # 1.0 2003/9/7: original version
15 #-----------------------------------
16 # Copyright 2003 Skemotah Solutions
19 # This file is part of Koha.
21 # Koha is free software; you can redistribute it and/or modify it under the
22 # terms of the GNU General Public License as published by the Free Software
23 # Foundation; either version 2 of the License, or (at your option) any later
26 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
27 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
28 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
30 # You should have received a copy of the GNU General Public License along with
31 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
32 # Suite 330, Boston, MA 02111-1307 USA
38 use Mail::Sendmail; # comment out if not doing e-mail notices
40 use MIME::QuotedPrint;
43 my ($confirm, $nomail,$branch);
51 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.
52 You MUST edit this script for your library BEFORE you run it for the first time!
53 See the comments in the script for directions on changing the script.
54 This script has 2 parameters :
55 -c to confirm and remove this help & warning
56 -n to avoid sending any mail. Instead, all mail messages are printed on screen. Usefull for testing purposes.
58 Do you wish to continue? (y/n)
61 exit unless (/^y/i); # comment these lines out once you've made the changes
65 # BEGINNING OF PARAMETERS
67 my $mindays = 7; # the notice will be sent after mindays days (grace period)
68 my $maxdays = 30; # issues being more than maxdays late are managed somewhere else. (borrower probably suspended)
69 my $smtpserver = 'smtp.laposte.net'; # your smtp server (the server who sent mails)
70 my $from = 'fromadress@toto'; # all the mails sent to the borrowers will appear coming from here.
71 my $mailtitle = 'Relances'; # the title of the mails
72 my $librarymail = 'tonadress@email'; # all notices without mail are sent (in 1 mail) to this mail address. They must then be managed manua lly.
73 # this parameter (the last) is the text of the mail that is sent.
74 # this text contains fields that are replaced by their value. Those fields must be written between brackets
75 # The following fields are available :
76 # <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode>
77 my $mailtext = "<firstname>;<lastname>;<address>;<address2>;<postcode>;<city>;<email>;<itemcount>;<titles>\n";
81 open OUTFILE, ">:utf8","overdues.csv" or die "impossible d'ouvrir le fichier de relances";
82 print OUTFILE "Date;Name;Surname;Adress1;Adress2;zipcode;city;Mail;Nbitems;1title;1author;1barcode;1issuedate;1returndate;";
83 print OUTFILE "2title;2author;2barcode;2issue_date;2return_date;3title;3author;3barcode;3issue_date;3return_date;4title;4author;4barcode;4issue_date;4return_date;5title;5author;5barcode;5issue_date;5return_date;6title;6author;6barcode;6issue_date;6return_date;7title;7author;7barcode;7issue_date;7return_date;8title;8author;8barcode;8issue_date;8return_date;9title;9author;9barcode;9issue_date;9return_date;10title;10author;10barcode;10issue_date;10return_date;\n";
84 # set the e-mail server -- comment out if not doing e-mail notices
85 # unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , $smtpserver;
86 # set your own mail server name here
88 my $dbh = C4::Context->dbh;
89 my $query = "SELECT COUNT(*), issues.borrowernumber,firstname,surname,address,address2,city,zipcode,email FROM issues,borrowers ,categories WHERE TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500 AND issues.borrowernumber=borrowers.borrowernumber and borrowers.categorycode=categories.categorycode and (categories.overduenoticerequired = 1)";
90 $query .= " AND borrowers.branchcode=".$dbh->quote($branch) if $branch;
91 $query .=" GROUP BY issues.borrowernumber";
92 my $sth = $dbh->prepare ($query);
95 my $sth2 = $dbh->prepare("SELECT biblio.title,biblio.author,items.barcode, issues.timestamp, issues.date_due FROM issues,items,biblio WHERE items.itemnumber=issues.itemnumber and biblio.biblionumber=items.biblionumber AND issues.borrowernumber=? AND TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN 0 and 500");
101 my $count = 0; # to keep track of how many notices are printed
102 my $e_count = 0; # and e-mailed
103 my ($itemcount,$borrnum,$firstname,$lastname,$address1,$address2,$city,$postcode,$email);
105 while (($itemcount,$borrnum,$firstname,$lastname,$address1,$address2,$city,$postcode,$email) = $sth->fetchrow) {
106 my $notice = $mailtext;
107 $notice =~ s/\<firstname\>/$firstname/g if $firstname;
108 $notice =~ s/\<lastname\>/$lastname/g if $lastname;
109 $notice =~ s/\<address1\>/$address1/g if $address1;
110 $notice =~ s/\<address2\>/$address2/g if $address2;
111 $notice =~ s/\<email\>/$email/g if $email;
112 $notice =~ s/\<postcode\>/$postcode/g if $postcode;
113 $notice =~ s/\<city\>/$city/g if $city;
114 $notice =~ s/\<itemcount\>/$itemcount/g;
116 $sth2->execute($borrnum);
118 my ($title, $author, $barcode,$timestamp,$date_due);
119 while (($title, $author, $barcode,$timestamp,$date_due) = $sth2->fetchrow){
120 $titles .= ($title?$title:"").";".($author?$author:"").";".($barcode?$barcode:"").";" ;
121 $titles .= ($timestamp?format_date(substr($timestamp,0,10)):"").";".($date_due?format_date($date_due):"").";" ;
123 $notice =~ s/\<titles\>/$titles/g;
124 $notice =~ s/(\<.*?\>)//g;
126 print OUTFILE $notice;
133 open(OD,"overdues.csv");
136 my %mail = ( To => 'mailto@mail.com',
137 From => 'mailfrom@mail.com',
138 Subject => 'Koha overdues',
140 my $boundary = "====" . time() . "====";
141 $mail{'content-type'} = "multipart/mixed; boundary=\"$boundary\"";
143 # open FILE, "Relances.csv";
144 my $message = encode_qp("The file");
146 my $file = "overdues.csv";
148 open (F, $file) or die "Cannot read $ $!";
151 $mail{body} = encode_base64(<F>);
154 $boundary = '--'.$boundary;
155 $mail{body} = <<END_OF_BODY;
157 Content-Type: text/plain; charset="iso-8859-1"
158 Content-Transfer-Encoding: quoted-printable
162 Content-Type: application/octet-stream; name="$^X"
163 Content-Transfer-Encoding: base64
164 Content-Disposition: attachment; filename="Overdues.csv"
170 sendmail(%mail) || print "Error: $Mail::Sendmail::error\n";