fixing permissions on scripts
[koha.git] / misc / overduenotices-30.pl
1 #!/usr/bin/perl -w
2 #-----------------------------------
3 # Script Name: overduenotices.pl
4 # Script Version: 1.0
5 # Date:  2003/9/7
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)
9 # Description: 
10 #       This script runs a Koha report of items using overduerules tables and letters tool management.
11 # Revision History:
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
16 #
17 # This file is part of Koha.
18 #
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
22 # version.
23 #
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.
27 #
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
31
32 use strict;
33 use C4::Context;
34 use C4::Date;
35 use Mail::Sendmail;  # comment out if not doing e-mail notices
36 use Getopt::Long;
37
38 my ($confirm, $nomail, $mybranch, $myborcat,$myborcatout, $letter, $MAX, $choice);
39 GetOptions(
40     'c'    => \$confirm,
41         'n'     => \$nomail,
42         'max=s' => \$MAX,
43         'all'   => \$choice,
44 );
45 unless ($confirm) {
46         print qq|
47 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.
48 You MUST edit this script for your library BEFORE you run it for the first time!
49 See the comments in the script for directions on changing the script.
50 This script has 2 parameters :
51         -c to confirm and remove this help & warning
52         -n to avoid sending any mail. Instead, all mail messages are printed on screen. Usefull for testing purposes.
53         -branch <branchcode> to select overdues for ONE specific branch.
54         -borcat <borcatcode> to select overdues for one borrower category,
55         -borcatout <borcatcode> to exclude this borrower category from overdunotices,
56         -max <MAX> MAXIMUM day count before stopping to send overdue notice,
57         -file <filename> to enter a specific filename to be read for message.
58         -all to include ALL the items that reader borrowed.
59
60 Do you wish to continue? (y/n)
61 |;
62         chomp($_ = <STDIN>);
63         exit unless (/^(y|Y|o|O)/i);  # comment these lines out once you've made the changes
64         
65 }
66 #warn 'site '.$mybranch.' text '.$letter;
67 my $dbh = C4::Context->dbh;
68 my $rqoverduebranches=$dbh->prepare("SELECT DISTINCT branchcode from overduerules where delay1>0");
69 $rqoverduebranches->execute;
70 while (my ($branchcode)=$rqoverduebranches->fetchrow){
71         warn "branchcode : $branchcode";
72         my $branchname;
73         my $emailaddress;
74         if ($branchcode){
75                 my $rqbranch=$dbh->prepare("SELECT * from branches where branchcode = ?");
76                 $rqbranch->execute($branchcode);
77                 my $data = $rqbranch->fetchrow_hashref;
78                 $emailaddress = $data->{branchemail};
79                 $branchname = $data->{branchname};
80         }
81         $emailaddress=C4::Context->preference('KohaAdminEmailAddress') unless ($emailaddress);
82
83         #print STDERR "$emailaddress\n";
84         #
85         # BEGINNING OF PARAMETERS
86         #
87         my $rqoverduerules=$dbh->prepare("SELECT * from overduerules where delay1>0 and branchcode = ?");
88         $rqoverduerules->execute($branchcode);
89         while (my $data=$rqoverduerules->fetchrow_hashref){
90                 for (my $i=1; $i<=3;$i++){
91                         #Two actions :
92                         # A- Send a letter
93                         # B- Debar
94                         my $mindays = $data->{"delay$i"}; # the notice will be sent after mindays days (grace period)
95                         my $rqdebarring=$dbh->prepare("UPDATE borrowers SET debarred=1 WHERE borrowernumber=?") if $data->{"debarred$i"};
96                         my $maxdays = ($data->{"delay".($i+1)}?
97                                                                 $data->{"delay".($i+1)}
98                                                                 :($MAX?$MAX:365)); # issues being more than maxdays late are managed somewhere else. (borrower probably suspended)
99                         #LETTER parameters
100                         my $smtpserver = 'smtp.wanadoo.fr'; # your smtp server (the server who sent mails)
101                         my $from = $emailaddress; # all the mails sent to the borrowers will appear coming from here.
102                         my $mailtitle = 'Overdue'; # the title of the mails
103                         $mailtitle = 'Issue status' if ($choice); # the title of the mails
104                         my $librarymail = $emailaddress; # all notices without mail are sent (in 1 mail) to this mail address. They must then be managed manually.
105                         my $letter = $data->{"letter$i"} if $data->{"letter$i"};
106                         # this parameter (the last) is the text of the mail that is sent.
107                         # this text contains fields that are replaced by their value. Those fields must be written between brackets
108                         # The following fields are available :
109                         # <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode>
110                         my $mailtext=$letter;
111                         #
112                         # END OF PARAMETERS
113                         #
114                         open OUTFILE, ">overdues" or die "Cannot open file overdues: $!";
115                         
116                         # set the e-mail server -- comment out if not doing e-mail notices
117                         unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , $smtpserver;
118                         #                                         set your own mail server name here
119                         
120                         my $strsth = "SELECT COUNT(*), issues.borrowernumber,firstname,surname,streetaddress,physstreet,city,zipcode, emailaddress, MIN(date_due) as longest_issue FROM issues,borrowers,categories WHERE returndate IS NULL AND issues.borrowernumber=borrowers.borrowernumber and borrowers.categorycode=categories.categorycode ";
121                         $strsth .= " and issues.branchcode='".$branchcode."' " if ($branchcode);
122                         $strsth .= " and borrowers.categorycode='".$data->{categorycode}."' " if ($data->{categorycode});
123                         $strsth .= " and categories.overduenoticerequired=1 group by issues.borrowernumber HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) BETWEEN $mindays and $maxdays ";
124                         my $sth = $dbh->prepare ($strsth);
125                         warn "".$strsth;
126                         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");
127
128                         $sth->execute;
129                         # 
130                         # my $itemcount = 0;
131                         # my $row;
132                         my $count = 0;   # to keep track of how many notices are printed
133                         my $e_count = 0;   # and e-mailed
134                         my $date=format_date(localtime);
135                         my ($itemcount,$borrowernumber,$firstname,$lastname,$address1,$address2,$city,$postcode,$email);
136                         
137                         while (($itemcount,$borrowernumber,$firstname,$lastname,$address1,$address2,$city,$postcode,$email) = $sth->fetchrow) {
138                                 if ($data->{"debarred$i"}){
139                                         #action taken is debarring
140                                         $rqdebarring->execute($borrowernumber);
141                                         warn "debarring $borrowernumber $firstname $lastname";
142                                 }
143                 #               print STDERR "$itemcount,$borrowernumber,$firstname,$lastname,$address1,$address2,$city,$postcode,$email\n"; 
144                                 if ($letter){
145                                         my $notice .= $mailtext;
146                         #               print STDERR "$notice\n";
147                                         $notice =~ s/\<itemcount\>/$itemcount/g if ($itemcount);
148                                         $notice =~ s/\<firstname\>/$firstname/g if ($firstname);
149                                         $notice =~ s/\<lastname\>/$lastname/g if ($lastname);
150                                         $notice =~ s/\<address1\>/$address1/g if ($address1);
151                                         $notice =~ s/\<address2\>/$address2/g if ($address2);
152                                         $notice =~ s/\<city\>/$city/g if ($city);
153                                         $notice =~ s/\<postcode\>/$postcode/g if ($postcode);
154                                         $notice =~ s/\<date\>/$date/g if ($date);
155                                         $notice =~ s/\<bib\>/$branchname/g if ($branchname);
156                         
157                                         $sth2->execute($borrowernumber);
158                                         my $titles="";
159                                         my ($title, $author, $barcode, $issuedate);
160                                         while (($title, $author, $barcode,$issuedate) = $sth2->fetchrow){
161                                                 $titles .= "    ".format_date($issuedate)."     ".($barcode?$barcode:"")."      ".($title?$title:"")."  ".($author?$author:"")."\n";
162                                         }
163                         #                       print STDERR "$titles";
164                                         $notice =~ s/\<titles\>/$titles/g;
165                                         $sth2->finish;
166                                 # if not using e-mail notices, comment out the following lines
167                                         if ($email) {   # or you might check for borrowers.preferredcont 
168                                                 if ($nomail) {
169                                                         print "TO => $email\n";
170                                                         print "FROM => $from\n";
171                                                         print "SUBJECT => $mailtitle\n";
172                                                         print "MESSAGE => $notice\n";
173                                                 } else {
174                                                         my %mail = ( To      => $email,
175                                                                                         From    => $from,
176                                                                                         Subject => $mailtitle,
177                                                                                         Message => $notice,
178                                                                 );
179                                                         sendmail(%mail);
180                                                 }
181                                                 $e_count++
182                                         } else {
183                                                 print OUTFILE $notice;
184                                                 $count++;
185                                         }    # and comment this one out, too, if not using e-mail
186                                 }
187                         }
188                         $sth->finish;
189                         close OUTFILE;
190                         # if some notices have to be printed & managed by the library, send them to library mail address.
191                         if ($count) {
192                                 open ODUES, "overdues" or die "Cannot open file overdues: $!";
193                                 my $notice = "$e_count overdue notices e-mailed\n";
194                                 $notice .= "$count overdue notices in file for printing\n\n";
195                 
196                                 $notice .= <ODUES>;
197                                 if ($nomail) {
198                                         print "TO => $email\n" if $email;
199                                         print "FROM => $from\n";
200                                         print "SUBJECT => Koha overdue\n";
201                                         print "MESSAGE => $notice\n";
202                                 } else {
203                                         my %mail = ( To      => $email,
204                                                                         From    => $from,
205                                                                         Subject => 'Koha overdues',
206                                                                         Message => $notice,
207                                                 );
208                                         sendmail(%mail);
209                                 }
210                         }
211                 }
212         }
213 }