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::Search;
   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=itemnodata($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 = itemnodata($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         }