bug fix:
[koha.git] / misc / notifys / fines.pl
1 #!/usr/bin/perl
2
3 # use lib ('/usr/local/koha/intranet/modules');
4
5
6 use C4::Database;
7 use C4::Members;
8 use C4::Circulation::Circ2;
9 use C4::Circulation::Fines;
10 use Date::Manip;
11 use Data::Dumper;
12 use HTML::Template;
13 use Mail::Sendmail;
14 use Mail::RFC822::Address;
15 use C4::Biblio;
16 use strict;
17
18
19 #levyFines();   # Do not levy real fines in testing situation.
20 notifyOverdues();
21
22
23
24 # Todo
25 #       - Need to calculate the fine on each book; no idea how to get this information from Koha
26 #       - Need to diffentricate between the total_fines including replacement costs, 
27 #       and the total fines if the books are returned in the day 29 notices (see above).
28 #       - clean up the %actions hash creation code.
29
30 #Done
31 #       - preferedcont field in borrowers hash; does this do anything?
32 #       - logging 
33 #       - which 'address' to send sms to?
34 #       - senders returning success or fail
35
36
37
38 sub levyFines {
39         # Look at the current overdues, and levy fines on the offenders. 
40         # arguments:
41         #       $date
42         #       $maxfine
43         
44         # Work out what today is as an integer value.
45         my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime(time);
46         $mon++; $year=$year+1900;
47         my $date=Date_DaysSince1BC($mon,$mday,$year);
48         my $maxfine =5;
49
50
51         # Retrieve an array of overdues.
52         my ($count, $overduesReference) = Getoverdues();
53         print "$count overdue items where found.\n\n";
54         my @overdues=@$overduesReference;
55
56         foreach my $overdue (@overdues) {
57                 my @dates=split('-',$overdue->{'date_due'});
58                 my $due_day=Date_DaysSince1BC($dates[1],$dates[2],$dates[0]);    
59
60
61                 # Check that the item is really overdue. The output of Getoverdues() will normally 
62                 # always be overdue items. However, if you are running this script with a value of $date other than the current time, this check is needed.
63                 if ($due_day <= $date) {
64                         my $difference=$date-$due_day;
65                         print "Itemnumber ".$overdue->{'itemnumber'}." is issued to ".$overdue->{'borrowernumber'}." is overdue by $difference days.\n";                
66
67                         # Calculate the cost of this overdue.
68                         # Fines vary according to borrower type, but cannot exceed the maximum fine. 
69                         print $overdue->{'borrowernumber'};
70                         my $borrower=BorType($overdue->{'borrowernumber'});
71
72                         my ($amount,$type,$printout)=CalcFine($overdue->{'itemnumber'}, $borrower->{'categorycode'}, $difference);      
73                         if ($amount > $maxfine){
74                                 $amount=$maxfine;
75                                 }
76
77                         if ($amount > 0){
78                                 my $due="$dates[2]/$dates[1]/$dates[0]";
79                                 UpdateFine($overdue->{'itemnumber'}, $overdue->{'borrowernumber'}, $amount, $type, $due);
80                                 print $overdue->{'borrowernumber'}." has been fined $amount for itemnumber ".$overdue->{'itemnumber'}." overdue for $difference days.\n";               
81                                 }
82
83                         
84
85                         # After 28 days, the item is marked lost and the replacement charge is added as a fine
86                         if ($difference >= 28) { 
87                                 my $borrower=BorType($overdue->{'borrowernumber'});
88                                 if ($borrower->{'cardnumber'} ne ''){
89                                         my $cost=ReplacementCost($overdue->{'itemnumber'});  
90                                         my $dbh=C4Connect();
91                                         my $env;
92
93                                         my $accountno=C4::Circulation::Circ2::getnextacctno($env,$overdue->{'borrowernumber'},$dbh);
94                                         my $item=getbibliofromitemnumber($env,$dbh,$overdue->{'itemnumber'});
95                                         if ($item->{'itemlost'} ne '1' && $item->{'itemlost'} ne '2' ){
96                                                 $item->{'title'}=~ s/\'/\\'/g;
97                                                 my $query="Insert into accountlines (borrowernumber,itemnumber,accountno,date,amount, description,accounttype,amountoutstanding) 
98                                                                 values ($overdue->{'borrowernumber'}, $overdue->{'itemnumber'},
99                                                                         '$accountno',now(),'$cost','Lost item $item->{'title'} $item->{'barcode'}','L','$cost')";
100
101                                                 my $sth=$dbh->prepare($query);
102                                                 $sth->execute();
103                                                 $sth->finish();
104                   
105                                                 $query="update items set itemlost=2 where itemnumber='$overdue->{'itemnumber'}'";
106                                                 $sth=$dbh->prepare($query);
107                                                 $sth->execute();
108                                                 $sth->finish();
109                                                 }
110                                         } 
111                                 }
112                         }
113                 }
114
115         return 1;
116         }
117
118
119
120
121
122
123 sub     notifyOverdues {
124         # Look up the overdues for today. 
125         # Capture overdues which fall on our dates of interest.
126
127
128
129
130 ####################################################################################################
131 # Creating a big hash of available templates
132 my %email;
133 %email->{'template'}='email-8.txt';
134 my %sms; 
135 %sms->{'template'}='sms-8.txt';
136
137 my %fax1;
138 %fax1->{'template'}='fax-8.html';
139
140 my %firstReminder->{'email'} = \%email;
141 %firstReminder->{'sms'} = \%sms;
142 %firstReminder->{'fax'} = \%fax1;
143         
144 my %email2;
145 %email2->{'template'}='email-15.txt';
146
147 my %fax2;
148 %fax2->{'template'}='fax-15.html';
149     
150 my %letter2;
151 %letter2->{'template'}='fax-15.html';
152     
153 my %sms2->{'template'}='sms-15.txt';
154 my %secondReminder->{'email'} = \%email2;
155 %secondReminder->{'sms'} = \%sms2;
156 %secondReminder->{'fax'} = \%fax2;
157 %secondReminder->{'letter'} = \%letter2;    
158
159
160 my %email3;
161 %email3->{'template'}='email-29.txt';
162 my %fax3;
163 %fax3->{'template'}='fax-29.html';
164 my %letter3;
165 %letter3->{'template'}='letter-29.html';
166
167 my %finalReminder->{'email'} = \%email3;
168 %finalReminder->{'fax'} = \%fax3;
169 %finalReminder->{'letter'} = \%letter3;
170
171 my $fines;
172 my %actions;
173 %actions->{'8'}=\%firstReminder;
174 %actions->{'15'}=\%secondReminder;
175 %actions->{'29'}=\%finalReminder;
176
177 ##################################################################################################################
178
179
180         # Work out what today is as an integer value.
181         my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime(time);
182         $mon++; $year=$year+1900;
183         my $date=Date_DaysSince1BC($mon,$mday,$year);
184
185
186         # Retrieve an array of overdues.
187         my ($count, $overduesReference) = Getoverdues();
188         print "$count overdue items where found.\n\n";
189         my @overdues=@$overduesReference;
190
191
192         # We're going to build a hash of arrays, containing the items requiring action.
193         # ->borrowernumber, date, @overdues
194         my %actionItems;
195         foreach my $actionday (keys(%actions)) {
196                 my @items=();
197                 %actionItems->{$actionday} = \@items;
198                 }
199         
200
201
202         foreach my $overdue (@overdues) {
203                 my @dates=split('-',$overdue->{'date_due'});
204                 my $due_day=Date_DaysSince1BC($dates[1],$dates[2],$dates[0]);
205
206                 my $difference=$date-$due_day;
207 #               $overdue->{'fine'}=GetFine($overdue->{'itemnumber'});
208                 # If does this item fall on a day of interest?
209                                                 $overdue->{'difference'}=$difference;
210                 foreach my $actiondate (keys(%actions)) {
211                         if ($actiondate == $difference) {
212                                 my @items = @{%actionItems->{$actiondate}};
213
214                                 my %o = %$overdue;
215                                 push (@items, \%o); 
216                                 %actionItems->{$actiondate} = \@items;
217                                 }
218                         }
219                 }
220
221
222
223
224         # We now have a hash containing overdues which need actioning,  we can step through each set. 
225         # Work from earilest to latest. We only wish to send the most urgent message.
226         my %messages; 
227         my %borritem;
228
229         foreach my $actiondate (sort {$a <=> $b} (keys(%actions))) {
230                 print "\n\nThe following items are $actiondate days overdue.\n";
231                 my @items = @{%actionItems->{$actiondate}};
232         
233                         
234                 foreach my $overdue (@items) {          
235                         if ($overdue->{'difference'} eq $actiondate) {  
236                                 # Detemine which borrower is responsible for this overdue;
237                                 # if the offender is a child, then the garentor is the person to notify
238                                 my $borrower=responsibleBorrower($overdue);
239
240
241                                 my ($method, $address) = preferedContactMethod($borrower);
242                                 if ($method) {  
243                                 
244                                         # Do we have to send something, using this method on this day?
245                                         if (%actions->{$actiondate}->{$method}->{'template'}) {
246                                                 # If this user has one overdue, then they may have offers as well.
247                                                 # No point in sending a notice without mentioning all of the items.
248                                                 my @alloverdues; 
249                                                 foreach my $over (@overdues) {
250                                                         my $responisble= responsibleBorrower($over);
251                                                         if ($responisble->{'borrowernumber'} eq $borrower->{'borrowernumber'}) {
252                                                                 $over->{'borrowernumber'}=$responisble->{'borrowernumber'};
253                                                                 my %o = %$over;
254                                                                 push (@alloverdues, \%o);
255                                                                 }
256                                                         }
257                         
258                                                 my $dbh=C4Connect();    # FIXME disconnect this
259
260                                                 # Template the message
261                                                 my $template = HTML::Template->new(filename => 'templates/'.%actions->{$actiondate}->{$method}->{'template'}, die_on_bad_params => 0);
262
263                                                 my @bookdetails;
264                                                 my $total_fines = 0;
265                                                 foreach my $over (@alloverdues) {
266                                                         my %row_data;
267                                                         my $env;        #FIXME what is this varible for?
268                                                       
269                                                         if ( my $item = getbibliofromitemnumber($env, $dbh, $over->{'itemnumber'})){
270                                                             print "getting fine ($over->{'itemnumber'} $overdue->{'borrowernumber'} $over->{'borrowernumber'}\n";
271                                                         my $fine = GetFine($over->{'itemnumber'},$overdue->{'borrowernumber'}); 
272                                                             
273                                                             
274                                                             print "fine=$fine  ";
275
276                                                           my $rep = ReplacementCost2($over->{'itemnumber'},$overdue->{'borrowernumber'});
277
278                                                         if ($rep){
279                                                              $rep+=0.00;
280                                                             }
281                                                         if ($fine){
282                                                             $fine+=0.00;
283                                                              $borritem{"$over->{'itemnumber'} $over->{'borrowernumber'}"}=$fine;
284                                                             } else {
285                                                                 $borritem{"$over->{'itemnumber'} $over->{'borrowernumber'}"}+=$fine;
286                                                                 }
287                                                             print $borritem{"$over->{'itemnumber'} $over->{'borrowernumber'}"},"\n";
288                                                         $total_fines +=  $borritem{"$over->{'itemnumber'} $over->{'borrowernumber'}"};
289                                                         $item->{'title'}=substr($item->{'title'},0,25);
290                                                         my $len=length($item->{'title'});
291                                                         if ($len < 25){
292                                                             my $diff=25-$len;
293                                                             $item->{'title'}.=" " x $diff;
294                                                             }
295
296                                                         $row_data{'BARCODE'}=$item->{'barcode'};
297                                                         $row_data{'TITLE'}=$item->{'title'};
298                                                         $row_data{'DATE_DUE'}=$over->{'date_due'};
299                                                         $row_data{'FINE'}=$borritem{"$over->{'itemnumber'} $over->{'borrowernumber'}"};
300                                                     $row_data{'REP'}=$rep;
301
302                                                         push(@bookdetails, \%row_data);
303                                                             } else {
304                                                                 print "Missing item  $over->{'itemnumber'}\n";
305                                                                 }
306                                                         }
307
308                                                 $template->param(BOOKDETAILS => \@bookdetails);         
309                                                 
310                                                 my $env;
311                                                 my %params;
312                                                 %params->{'borrowernumber'} = $overdue->{'borrowernumber'};
313                                                 my ($count, $acctlines, $total) = &getboracctrecord($env, \%params);
314                                                 $template->param(FINES_TOTAL => $total_fines);
315                                                 $template->param(OWING => $total);
316                                                 my $name= "$borrower->{'firstname'} $borrower->{'surname'}"; 
317                                                 $template->param(NAME=> $name);
318         
319                                                 %messages->{$borrower->{'borrowernumber'}} = $template->output();
320                                                 }
321                                         else    {
322                                                 print "No $method needs to be sent at $overdue->{'difference'} days; not sending\n";
323                                                 }
324         
325                                         }
326                                 else    {
327                                         print "This borrower has an overdue item, but no means of contact\n";
328                                         }               
329
330                                 } #end of 'if this overdue falls on an action date'
331
332                         } #end of 'foreach overdue'
333
334                 } # end of foreach actiondate
335
336
337         # How that all of the messsages to be sent have been composed, send them.
338         foreach my $borrowernumber (keys(%messages)) {
339                 print "$borrowernumber\n";
340
341                 my $borrower=BorType($borrowernumber);
342                 my ($method, $address) = preferedContactMethod($borrower);
343
344                 my $result=0;
345                 if ($method eq 'email') {
346                         $result = sendEmail($address, 'lep@library.org.nz', 'Overdue Library Items', %messages->{$borrowernumber});
347                         }
348                 elsif ($method eq 'sms') {
349                         $result = sendSMS($address, %messages->{$borrowernumber});
350                         }
351                 elsif ($method eq 'fax') {
352                         $result = sendFax($address, %messages->{$borrowernumber});
353                         }
354                 elsif ($method eq 'letter') {
355                         $result = printLetter($address, %messages->{$borrowernumber});
356                         }       
357
358
359                 #print %messages->{$borrowernumber};    # debug
360
361
362                 # Log the outcome of this attempt
363                 logContact($borrowernumber, $method, $address, $result, %messages->{$borrowernumber});
364                 }
365
366
367
368         return 1;
369         }
370
371
372
373
374
375
376
377
378
379
380 sub     responsibleBorrower {   
381         # Given an overdue item, return the details of the borrower responible as a hash of database columns.
382         my $overdue=$_[0];
383
384         if ($overdue->{'borrowernumber'}) {
385                 my $borrower=BorType($overdue->{'borrowernumber'});
386
387
388                 # Overdue books assigned to children have notices sent to the guarantor.        
389                 if ($borrower->{'categorycode'} eq 'C') {
390                         my $dbh=C4Connect();
391                         my $query="Select       borrowernumber from borrowers 
392                                                 where borrowernumber=?";
393
394                         my $sth=$dbh->prepare($query);
395                         $sth->execute($borrower->{'guarantor'});
396
397                         my $tdata=$sth->fetchrow_hashref();
398                         $sth->finish();
399                         $dbh->disconnect();
400                         
401                         my $guarantor=BorType($tdata->{'borrowernumber'});
402                         $borrower = $guarantor;
403                         }
404         
405                 return $borrower;
406                 }
407
408         }
409
410
411
412
413
414
415
416
417
418 sub     preferedContactMethod {
419         # Given a reference to borrower details, in the format
420         # returned by BorType(), determine the prefered contact method, and address to use.
421         my $borrower=$_[0];
422 #                   print "finding borrower method $borrower->{'preferredcont'} $borrower->{'emailaddress'} $borrower->{'streetaddress'}\n";
423
424         # Possible contact methods, in order of preference are:
425         my @methods = ('email', 'sms', 'fax', 'letter');
426
427         my $method='';  
428         my $address=''; 
429
430
431         # Does this borrower have a borrower.preferredcont set?
432         # If so, push it to the head of our array of methods to try.
433         # If it's a method unheard of by this system, then we'll drop though to the prefined methods above.
434         # Note use of unshift to push onto the front of the array.
435         if ($borrower->{'preferredcont'}) {
436                 unshift(@methods, $borrower->{'preferredcont'});
437                 }
438
439
440         # Cycle through the possible methods until one is accepted
441         while ((@methods) and (!$address)) {
442                 $method=shift(@methods);
443
444
445                 if ($method eq 'email') {
446                         if (($borrower->{'emailaddress'}) and (Mail::RFC822::Address::valid($borrower->{'emailaddress'}))) {
447                                 $address = $borrower->{'emailaddress'};
448                                 }
449                         }       
450                 elsif ($method eq 'fax') {
451                         if ($borrower->{'faxnumber'}) {
452                                 $address = $borrower->{'faxnumber'};
453                                 }
454                         }
455                 elsif ($method eq 'sms') {
456                         if ($borrower->{'textmessaging'}) {
457                                 $address = $borrower->{'textmessaging'};
458                                 }
459                         }
460                 elsif ($method eq 'letter') {
461                         if ($borrower->{'streetaddress'}) {
462                                 $address =  mailingAddress($borrower);
463                                 }
464                         }
465                 }
466 print "$method, $address\n";
467         return ($method, $address);
468         }
469
470
471
472
473
474
475
476
477 sub     logContact {
478         # Given the details of an attempt to contact a borrower, 
479         # log them in the attempted_contacts table of the koha database.
480         my ($borrowernumber, $method, $address, $result, $message) = @_;
481
482         my $dbh=C4Connect();    # FIXME - disconnect me
483         my $querystring = "     insert into     attempted_contacts 
484                                                 (borrowernumber, method, address, result, message, date) 
485                                                 values (?, ?, ?, ?, ?, now())";
486         my $sth= $dbh->prepare($querystring);
487         $sth->execute($borrowernumber, $method, $address, $result, $message);
488         $sth->finish();
489         }
490
491
492
493
494
495
496
497
498 sub     mailingAddress {
499         # Given a hash of borrower information, such as that returned by BorType, 
500         # return a mailing address. 
501         my $borrower=$_[0];
502
503         my $address =   $borrower->{'firstname'}."\n". 
504                         $borrower->{'streetaddress'}."\n".
505                         $borrower->{'streetcity'};
506
507         return $address;
508         }
509
510
511
512
513
514
515
516 sub itemFine {
517         # Given an overdue item, return the current fines on it
518         my $overdue=$_[0];
519         # FIXME
520         return 1;
521         }
522
523
524
525
526
527
528
529
530
531 sub     sendEmail {
532         # Given an email address, and a subject and message, attempt to send email.
533         my $to=$_[0];
534         my $from=$_[1];
535         my $subject=$_[2];
536         my $message=$_[3];
537         
538 #    print "in email area";
539
540 #       print "\nSending Email To: $to\n$message\n";
541
542         my      %mail = (               To      => $to,
543 #                                        CC => 'rosalie@library.org.nz', 
544                                         From    => $from,
545                                         Subject => $subject,
546                                         Message => $message);
547
548                 
549         if (not(sendmail %mail)) {       
550                 warn "sendEmail to $to failed.";
551                 return 0;       
552                 }
553         
554         return 1;
555 #    die "got to here";
556         }
557
558
559 sub     sendSMS {
560         # Given a cell number and a message, attempt to send an SMS message.
561         # FIXME - needs information about how to do this at HLT
562         return 1;
563         }
564
565
566 sub     sendFax {
567     print "in fax \n";
568         # Given a fax number, and a message, attempt to send a fax.
569         # FIXME - needs information about how to do this at HLT
570         # This is fairly easy. 
571         # We will be past the body of the fax as HTML.
572         # We can pass this through html2ps to generate Postscript suitable
573         # for passing to the fax server. 
574         return 1;
575         }
576
577
578 sub     printLetter {
579         # Print a letter
580         # FIXME - needs information about how to do this at HLT
581         return 1;
582         }