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