C4/Overdues.pm

   1 package C4::Overdues;
   2
   3
   4 # Copyright 2000-2002 Katipo Communications
   5 #
   6 # This file is part of Koha.
   7 #
   8 # Koha is free software; you can redistribute it and/or modify it under the
   9 # terms of the GNU General Public License as published by the Free Software
  10 # Foundation; either version 2 of the License, or (at your option) any later
  11 # version.
  12 #
  13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  15 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License along with
  18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
  19 # Suite 330, Boston, MA  02111-1307 USA
  20
  21 use strict;
  22 use Date::Calc qw/Today/;
  23 use Date::Manip qw/UnixDate/;
  24 use C4::Circulation;
  25 use C4::Context;
  26 use C4::Accounts;
  27 use C4::Log; # logaction
  28 use C4::Debug;
  29
  30 use vars qw($VERSION @ISA @EXPORT);
  31
  32 BEGIN {
  33         # set the version for version checking
  34         $VERSION = 3.01;
  35         require Exporter;
  36         @ISA    = qw(Exporter);
  37         # subs to rename (and maybe merge some...)
  38         push @EXPORT, qw(
  39         &CalcFine
  40         &Getoverdues
  41         &checkoverdues
  42         &CheckAccountLineLevelInfo
  43         &CheckAccountLineItemInfo
  44         &CheckExistantNotifyid
  45         &GetNextIdNotify
  46         &GetNotifyId
  47         &NumberNotifyId
  48         &AmountNotify
  49         &UpdateAccountLines
  50         &UpdateFine
  51         &GetOverdueDelays
  52         &GetOverduerules
  53         &GetFine
  54         &CreateItemAccountLine
  55         &ReplacementCost2
  56
  57         &CheckItemNotify
  58         &GetOverduesForBranch
  59         &RemoveNotifyLine
  60         &AddNotifyLine
  61         );
  62         # subs to remove
  63         push @EXPORT, qw(
  64         &BorType
  65         );
  66
  67         # check that an equivalent don't exist already before moving
  68
  69         # subs to move to Circulation.pm
  70         push @EXPORT, qw(
  71         &GetIssuesIteminfo
  72         );
  73     #
  74         # &GetIssuingRules - delete.
  75         # use C4::Circulation::GetIssuingRule instead.
  76
  77         # subs to move to Members.pm
  78         push @EXPORT, qw(
  79         &CheckBorrowerDebarred
  80         &UpdateBorrowerDebarred
  81         );
  82         # subs to move to Biblio.pm
  83         push @EXPORT, qw(
  84         &GetItems
  85         &ReplacementCost
  86         );
  87 }
  88
  89 =head1 NAME
  90
  91 C4::Circulation::Fines - Koha module dealing with fines
  92
  93 =head1 SYNOPSIS
  94
  95   use C4::Overdues;
  96
  97 =head1 DESCRIPTION
  98
  99 This module contains several functions for dealing with fines for
 100 overdue items. It is primarily used by the 'misc/fines2.pl' script.
 101
 102 =head1 FUNCTIONS
 103
 104 =over 2
 105
 106 =item Getoverdues
 107
 108   ($overdues) = &Getoverdues();
 109
 110 Returns the list of all overdue books, with their itemtype.
 111
 112 C<$overdues> is a reference-to-array. Each element is a
 113 reference-to-hash whose keys are the fields of the issues table in the
 114 Koha database.
 115
 116 =cut
 117
 118 #'
 119 sub Getoverdues {
 120     my $dbh = C4::Context->dbh;
 121     my $sth =  (C4::Context->preference('item-level_itypes')) ?
 122                                 $dbh->prepare(
 123                                 "SELECT issues.*,items.itype as itemtype, items.homebranch FROM issues
 124                          LEFT JOIN items USING (itemnumber)
 125                          WHERE date_due < now()
 126                          ORDER BY borrowernumber " )
 127                                 :
 128                                 $dbh->prepare(
 129                     "SELECT issues.*,biblioitems.itemtype,items.itype, items.homebranch  FROM issues
 130                      LEFT JOIN items USING (itemnumber)
 131                      LEFT JOIN biblioitems USING (biblioitemnumber)
 132                      WHERE date_due < now()
 133                      ORDER BY borrowernumber " );
 134     $sth->execute;
 135         return $sth->fetchall_arrayref({});
 136 }
 137
 138 =head2 checkoverdues
 139
 140 ( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh );
 141
 142 Not exported
 143
 144 =cut
 145
 146 sub checkoverdues {
 147
 148 # From Main.pm, modified to return a list of overdueitems, in addition to a count
 149 #checks whether a borrower has overdue items
 150     my ( $borrowernumber, $dbh ) = @_;
 151     my @datearr = localtime;
 152     my $today   =
 153       ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
 154     my @overdueitems;
 155     my $count = 0;
 156     my $sth   = $dbh->prepare(
 157         "SELECT * FROM issues
 158          LEFT JOIN items ON issues.itemnumber      = items.itemnumber
 159          LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
 160          LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
 161             WHERE issues.borrowernumber  = ?
 162                 AND issues.date_due < ?"
 163     );
 164     $sth->execute( $borrowernumber, $today );
 165     while ( my $data = $sth->fetchrow_hashref ) {
 166         push( @overdueitems, $data );
 167         $count++;
 168     }
 169     $sth->finish;
 170     return ( $count, \@overdueitems );
 171 }
 172
 173 =item CalcFine
 174
 175   ($amount, $chargename, $daycount, $daycounttotal) =
 176     &CalcFine($item, $categorycode, $branch, $days_overdue, $description, $start_date, $end_date );
 177
 178 Calculates the fine for a book.
 179
 180 The issuingrules table in the Koha database is a fine matrix, listing
 181 the penalties for each type of patron for each type of item and each branch (e.g., the
 182 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
 183 members might get a longer grace period between the first and second
 184 reminders that a book is overdue).
 185
 186
 187 C<$item> is an item object (hashref).
 188
 189 C<$categorycode> is the category code (string) of the patron who currently has
 190 the book.
 191
 192 C<$branchcode> is the library (string) whose issuingrules govern this transaction.
 193
 194 C<$days_overdue> is the number of days elapsed since the book's due date.
 195   NOTE: supplying days_overdue is deprecated.
 196
 197 C<$start_date> & C<$end_date> are C4::Dates objects
 198 defining the date range over which to determine the fine.
 199 Note that if these are defined, we ignore C<$difference> and C<$dues> ,
 200 but retain these for backwards-comptibility with extant fines scripts.
 201
 202 Fines scripts should just supply the date range over which to calculate the fine.
 203
 204 C<&CalcFine> returns four values:
 205
 206 C<$amount> is the fine owed by the patron (see above).
 207
 208 C<$chargename> is the chargename field from the applicable record in
 209 the categoryitem table, whatever that is.
 210
 211 C<$daycount> is the number of days between start and end dates, Calendar adjusted (where needed),
 212 minus any applicable grace period.
 213
 214 C<$daycounttotal> is C<$daycount> without consideration of grace period.
 215
 216 FIXME - What is chargename supposed to be ?
 217
 218 FIXME: previously attempted to return C<$message> as a text message, either "First Notice", "Second Notice",
 219 or "Final Notice".  But CalcFine never defined any value.
 220
 221 =cut
 222
 223 #'
 224 sub CalcFine {
 225     my ( $item, $bortype, $branchcode, $difference ,$dues , $start_date, $end_date  ) = @_;
 226         $debug and warn sprintf("CalcFine(%s, %s, %s, %s, %s, %s, %s)",
 227                         ($item    ? '{item}' : 'UNDEF'),
 228                         ($bortype    || 'UNDEF'),
 229                         ($branchcode || 'UNDEF'),
 230                         ($difference || 'UNDEF'),
 231                         ($dues       || 'UNDEF'),
 232                         ($start_date ? ($start_date->output('iso') || 'Not a C4::Dates object') : 'UNDEF'),
 233                         (  $end_date ? (  $end_date->output('iso') || 'Not a C4::Dates object') : 'UNDEF')
 234         );
 235     my $dbh = C4::Context->dbh;
 236     my $amount = 0;
 237         my $daystocharge;
 238         # get issuingrules (fines part will be used)
 239     my $data = C4::Circulation::GetIssuingRule($bortype, $item->{'itemtype'},$branchcode);
 240         if($difference) {
 241                 # if $difference is supplied, the difference has already been calculated, but we still need to adjust for the calendar.
 242         # use copy-pasted functions from calendar module.  (deprecated -- these functions will be removed from C4::Overdues ).
 243             my $countspecialday    =    &GetSpecialHolidays($dues,$item->{itemnumber});
 244             my $countrepeatableday = &GetRepeatableHolidays($dues,$item->{itemnumber},$difference);
 245             my $countalldayclosed  = $countspecialday + $countrepeatableday;
 246             $daystocharge = $difference - $countalldayclosed;
 247         } else {
 248                 # if $difference is not supplied, we have C4::Dates objects giving us the date range, and we use the calendar module.
 249                 if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') {
 250                         my $calendar = C4::Calendar->new(  branchcode => $branchcode );
 251                         $daystocharge = $calendar->daysBetween( $start_date, $end_date );
 252                 } else {
 253                         $daystocharge = Date_to_Days(split('-',$end_date->output('iso'))) - Date_to_Days(split('-',$start_date->output('iso')));
 254                 }
 255         }
 256         # correct for grace period.
 257         my $days_minus_grace = $daystocharge - $data->{'firstremind'};
 258     if ($data->{'chargeperiod'} > 0 && $days_minus_grace > 0 ) {
 259         $amount = int($days_minus_grace / $data->{'chargeperiod'}) * $data->{'fine'};
 260     } else {
 261         # a zero (or null)  chargeperiod means no charge.
 262     }
 263     return ( $amount, $data->{'chargename'}, $days_minus_grace, $daystocharge);
 264 }
 265
 266
 267 =item GetSpecialHolidays
 268
 269 &GetSpecialHolidays($date_dues,$itemnumber);
 270
 271 return number of special days  between date of the day and date due
 272
 273 C<$date_dues> is the envisaged date of book return.
 274
 275 C<$itemnumber> is the book's item number.
 276
 277 =cut
 278
 279 sub GetSpecialHolidays {
 280 my ($date_dues,$itemnumber) = @_;
 281 # calcul the today date
 282 my $today = join "-", &Today();
 283
 284 # return the holdingbranch
 285 my $iteminfo=GetIssuesIteminfo($itemnumber);
 286 # use sql request to find all date between date_due and today
 287 my $dbh = C4::Context->dbh;
 288 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
 289 FROM `special_holidays`
 290 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
 291 AND   DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
 292 AND branchcode=?
 293 |;
 294 my @result=GetWdayFromItemnumber($itemnumber);
 295 my @result_date;
 296 my $wday;
 297 my $dateinsec;
 298 my $sth = $dbh->prepare($query);
 299 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
 300
 301 while ( my $special_date=$sth->fetchrow_hashref){
 302     push (@result_date,$special_date);
 303 }
 304
 305 my $specialdaycount=scalar(@result_date);
 306
 307     for (my $i=0;$i<scalar(@result_date);$i++){
 308         $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
 309         (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
 310         for (my $j=0;$j<scalar(@result);$j++){
 311             if ($wday == ($result[$j]->{'weekday'})){
 312             $specialdaycount --;
 313             }
 314         }
 315     }
 316
 317 return $specialdaycount;
 318 }
 319
 320 =item GetRepeatableHolidays
 321
 322 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
 323
 324 return number of day closed between date of the day and date due
 325
 326 C<$date_dues> is the envisaged date of book return.
 327
 328 C<$itemnumber> is item number.
 329
 330 C<$difference> numbers of between day date of the day and date due
 331
 332 =cut
 333
 334 sub GetRepeatableHolidays{
 335 my ($date_dues,$itemnumber,$difference) = @_;
 336 my $dateinsec=UnixDate($date_dues,"%o");
 337 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
 338 my @result=GetWdayFromItemnumber($itemnumber);
 339 my @dayclosedcount;
 340 my $j;
 341
 342 for (my $i=0;$i<scalar(@result);$i++){
 343     my $k=$wday;
 344
 345         for ( $j=0;$j<$difference;$j++){
 346             if ($result[$i]->{'weekday'} == $k)
 347                     {
 348                     push ( @dayclosedcount ,$k);
 349             }
 350         $k++;
 351         ($k=0) if($k eq 7);
 352         }
 353     }
 354 return scalar(@dayclosedcount);
 355 }
 356
 357
 358 =item GetWayFromItemnumber
 359
 360 &Getwdayfromitemnumber($itemnumber);
 361
 362 return the different week day from repeatable_holidays table
 363
 364 C<$itemnumber> is  item number.
 365
 366 =cut
 367
 368 sub GetWdayFromItemnumber{
 369 my($itemnumber)=@_;
 370 my $iteminfo=GetIssuesIteminfo($itemnumber);
 371 my @result;
 372 my $dbh = C4::Context->dbh;
 373 my $query = qq|SELECT weekday
 374     FROM repeatable_holidays
 375     WHERE branchcode=?
 376 |;
 377 my $sth = $dbh->prepare($query);
 378     #  print $query;
 379
 380 $sth->execute($iteminfo->{'branchcode'});
 381 while ( my $weekday=$sth->fetchrow_hashref){
 382     push (@result,$weekday);
 383     }
 384 return @result;
 385 }
 386
 387
 388 =item GetIssuesIteminfo
 389
 390 &GetIssuesIteminfo($itemnumber);
 391
 392 return all data from issues about item
 393
 394 C<$itemnumber> is  item number.
 395
 396 =cut
 397
 398 sub GetIssuesIteminfo{
 399 my($itemnumber)=@_;
 400 my $dbh = C4::Context->dbh;
 401 my $query = qq|SELECT *
 402     FROM issues
 403     WHERE itemnumber=?
 404 |;
 405 my $sth = $dbh->prepare($query);
 406 $sth->execute($itemnumber);
 407 my ($issuesinfo)=$sth->fetchrow_hashref;
 408 return $issuesinfo;
 409 }
 410
 411
 412 =item UpdateFine
 413
 414   &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
 415
 416 (Note: the following is mostly conjecture and guesswork.)
 417
 418 Updates the fine owed on an overdue book.
 419
 420 C<$itemnumber> is the book's item number.
 421
 422 C<$borrowernumber> is the borrower number of the patron who currently
 423 has the book on loan.
 424
 425 C<$amount> is the current amount owed by the patron.
 426
 427 C<$type> will be used in the description of the fine.
 428
 429 C<$description> is a string that must be present in the description of
 430 the fine. I think this is expected to be a date in DD/MM/YYYY format.
 431
 432 C<&UpdateFine> looks up the amount currently owed on the given item
 433 and sets it to C<$amount>, creating, if necessary, a new entry in the
 434 accountlines table of the Koha database.
 435
 436 =cut
 437
 438 #
 439 # Question: Why should the caller have to
 440 # specify both the item number and the borrower number? A book can't
 441 # be on loan to two different people, so the item number should be
 442 # sufficient.
 443 #
 444 # Possible Answer: You might update a fine for a damaged item, *after* it is returned.
 445 #
 446 sub UpdateFine {
 447     my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
 448         $debug and warn "UpdateFine($itemnum, $borrowernumber, $amount, " . ($type||'""') . ", $due) called";
 449     my $dbh = C4::Context->dbh;
 450     # FIXME - What exactly is this query supposed to do? It looks up an
 451     # entry in accountlines that matches the given item and borrower
 452     # numbers, where the description contains $due, and where the
 453     # account type has one of several values, but what does this _mean_?
 454     # Does it look up existing fines for this item?
 455     # FIXME - What are these various account types? ("FU", "O", "F", "M")
 456         #       "L"   is LOST item
 457         #   "A"   is Account Management Fee
 458         #   "N"   is New Card
 459         #   "M"   is Sundry
 460         #   "O"   is Overdue ??
 461         #   "F"   is Fine ??
 462         #   "FU"  is Fine UPDATE??
 463         #       "Pay" is Payment
 464         #   "REF" is Cash Refund
 465     my $sth = $dbh->prepare(
 466         "SELECT * FROM accountlines
 467                 WHERE itemnumber=?
 468                 AND   borrowernumber=?
 469                 AND   accounttype IN ('FU','O','F','M')
 470                 AND   description like ? "
 471     );
 472     $sth->execute( $itemnum, $borrowernumber, "%$due%" );
 473
 474     if ( my $data = $sth->fetchrow_hashref ) {
 475
 476                 # we're updating an existing fine.
 477         if ( $data->{'amount'} != $amount ) {
 478             my $diff = $amount - $data->{'amount'};
 479             my $out  = $data->{'amountoutstanding'} + $diff;
 480             my $query = "
 481                 UPDATE accountlines
 482                                 SET date=now(), amount=?, amountoutstanding=?,
 483                                         lastincrement=?, accounttype='FU'
 484                                 WHERE borrowernumber=?
 485                                 AND   itemnumber=?
 486                                 AND   accounttype IN ('FU','O')
 487                                 AND   description LIKE ?
 488                                 LIMIT 1 ";
 489             my $sth2 = $dbh->prepare($query);
 490                         # FIXME: BOGUS query cannot ensure uniqueness w/ LIKE %x% !!!
 491                         #               LIMIT 1 added to prevent multiple affected lines
 492                         # FIXME: accountlines table needs unique key!! Possibly a combo of borrowernumber and accountline.
 493                         #               But actually, we should just have a regular autoincrementing PK and forget accountline,
 494                         #               including the bogus getnextaccountno function (doesn't prevent conflict on simultaneous ops).
 495                         # FIXME: Why only 2 account types here?
 496                         $debug and print STDERR "UpdateFine query: $query\n" .
 497                                 "w/ args: $amount, $out, $diff, $data->{'borrowernumber'}, $data->{'itemnumber'}, \"\%$due\%\"\n";
 498             $sth2->execute($amount, $out, $diff, $data->{'borrowernumber'}, $data->{'itemnumber'}, "%$due%");
 499         } else {
 500             #      print "no update needed $data->{'amount'}"
 501         }
 502     } else {
 503         my $sth4 = $dbh->prepare(
 504             "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
 505         );
 506         $sth4->execute($itemnum);
 507         my $title = $sth4->fetchrow;
 508
 509 #         #   print "not in account";
 510 #         my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
 511 #         $sth3->execute;
 512 #
 513 #         # FIXME - Make $accountno a scalar.
 514 #         my @accountno = $sth3->fetchrow_array;
 515 #         $sth3->finish;
 516 #         $accountno[0]++;
 517 # begin transaction
 518                 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
 519                 my $desc = ($type ? "$type " : '') . "$title $due";     # FIXEDME, avoid whitespace prefix on empty $type
 520                 my $query = "INSERT INTO accountlines
 521                     (borrowernumber,itemnumber,date,amount,description,accounttype,amountoutstanding,lastincrement,accountno)
 522                             VALUES (?,?,now(),?,?,'FU',?,?,?)";
 523                 my $sth2 = $dbh->prepare($query);
 524                 $debug and print STDERR "UpdateFine query: $query\nw/ args: $borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno\n";
 525         $sth2->execute($borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno);
 526     }
 527     # logging action
 528     &logaction(
 529         "FINES",
 530         $type,
 531         $borrowernumber,
 532         "due=".$due."  amount=".$amount." itemnumber=".$itemnum
 533         ) if C4::Context->preference("FinesLog");
 534 }
 535
 536 =item BorType
 537
 538   $borrower = &BorType($borrowernumber);
 539
 540 Looks up a patron by borrower number.
 541
 542 C<$borrower> is a reference-to-hash whose keys are all of the fields
 543 from the borrowers and categories tables of the Koha database. Thus,
 544 C<$borrower> contains all information about both the borrower and
 545 category he or she belongs to.
 546
 547 =cut
 548
 549 #'
 550 sub BorType {
 551     my ($borrowernumber) = @_;
 552     my $dbh              = C4::Context->dbh;
 553     my $sth              = $dbh->prepare(
 554         "SELECT * from borrowers
 555       LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
 556       WHERE borrowernumber=?"
 557     );
 558     $sth->execute($borrowernumber);
 559     my $data = $sth->fetchrow_hashref;
 560     $sth->finish;
 561     return ($data);
 562 }
 563
 564 =item ReplacementCost
 565
 566   $cost = &ReplacementCost($itemnumber);
 567
 568 Returns the replacement cost of the item with the given item number.
 569
 570 =cut
 571
 572 #'
 573 sub ReplacementCost {
 574     my ($itemnum) = @_;
 575     my $dbh       = C4::Context->dbh;
 576     my $sth       =
 577       $dbh->prepare("Select replacementprice from items where itemnumber=?");
 578     $sth->execute($itemnum);
 579
 580     # FIXME - Use fetchrow_array or something.
 581     my $data = $sth->fetchrow_hashref;
 582     $sth->finish;
 583     return ( $data->{'replacementprice'} );
 584 }
 585
 586 =item GetFine
 587
 588 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
 589
 590 return the total of fine
 591
 592 C<$itemnum> is item number
 593
 594 C<$borrowernumber> is the borrowernumber
 595
 596 =cut
 597
 598
 599 sub GetFine {
 600     my ( $itemnum, $borrowernumber ) = @_;
 601     my $dbh   = C4::Context->dbh();
 602     my $query = "SELECT sum(amountoutstanding) FROM accountlines
 603     where accounttype like 'F%'
 604   AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
 605     my $sth = $dbh->prepare($query);
 606     $sth->execute( $itemnum, $borrowernumber );
 607     my $data = $sth->fetchrow_hashref();
 608     return ( $data->{'sum(amountoutstanding)'} );
 609 }
 610
 611
 612 =item GetIssuingRules
 613
 614 FIXME - This sub should be deprecated and removed.
 615 It ignores branch and defaults.
 616
 617 $data = &GetIssuingRules($itemtype,$categorycode);
 618
 619 Looks up for all issuingrules an item info
 620
 621 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
 622 from the borrowers and categories tables of the Koha database. Thus,
 623
 624 C<$categorycode> contains  information about borrowers category
 625
 626 C<$data> contains all information about both the borrower and
 627 category he or she belongs to.
 628 =cut
 629
 630 sub GetIssuingRules {
 631         warn "GetIssuingRules is deprecated: use GetIssuingRule from C4::Circulation instead.";
 632    my ($itemtype,$categorycode)=@_;
 633    my $dbh   = C4::Context->dbh();
 634    my $query=qq|SELECT *
 635         FROM issuingrules
 636         WHERE issuingrules.itemtype=?
 637             AND issuingrules.categorycode=?
 638         |;
 639     my $sth = $dbh->prepare($query);
 640     #  print $query;
 641     $sth->execute($itemtype,$categorycode);
 642     return $sth->fetchrow_hashref;
 643 }
 644
 645
 646 sub ReplacementCost2 {
 647     my ( $itemnum, $borrowernumber ) = @_;
 648     my $dbh   = C4::Context->dbh();
 649     my $query = "SELECT amountoutstanding
 650          FROM accountlines
 651              WHERE accounttype like 'L'
 652          AND amountoutstanding > 0
 653          AND itemnumber = ?
 654          AND borrowernumber= ?";
 655     my $sth = $dbh->prepare($query);
 656     $sth->execute( $itemnum, $borrowernumber );
 657     my $data = $sth->fetchrow_hashref();
 658     return ( $data->{'amountoutstanding'} );
 659 }
 660
 661
 662 =item GetNextIdNotify
 663
 664 ($result) = &GetNextIdNotify($reference);
 665
 666 Returns the new file number
 667
 668 C<$result> contains the next file number
 669
 670 C<$reference> contains the beggining of file number
 671
 672 =cut
 673
 674
 675
 676 sub GetNextIdNotify {
 677 my ($reference)=@_;
 678 my $query=qq|SELECT max(notify_id)
 679          FROM accountlines
 680          WHERE notify_id  like \"$reference%\"
 681          |;
 682 # AND borrowernumber=?|;
 683 my $dbh = C4::Context->dbh;
 684 my $sth=$dbh->prepare($query);
 685 $sth->execute();
 686 my $result=$sth->fetchrow;
 687 $sth->finish;
 688 my $count;
 689     if ($result eq '')
 690     {
 691     ($result=$reference."01")  ;
 692     }else
 693     {
 694     $count=substr($result,6)+1;
 695
 696     if($count<10){
 697      ($count = "0".$count);
 698      }
 699      $result=$reference.$count;
 700      }
 701 return $result;
 702 }
 703
 704
 705 =item NumberNotifyId
 706
 707 (@notify) = &NumberNotifyId($borrowernumber);
 708
 709 Returns amount for all file per borrowers
 710 C<@notify> array contains all file per borrowers
 711
 712 C<$notify_id> contains the file number for the borrower number nad item number
 713
 714 =cut
 715
 716 sub NumberNotifyId{
 717     my ($borrowernumber)=@_;
 718     my $dbh = C4::Context->dbh;
 719     my $query=qq|    SELECT distinct(notify_id)
 720             FROM accountlines
 721             WHERE borrowernumber=?|;
 722     my @notify;
 723     my $sth=$dbh->prepare($query);
 724         $sth->execute($borrowernumber);
 725           while ( my ($numberofnotify)=$sth->fetchrow){
 726     push (@notify,$numberofnotify);
 727     }
 728     $sth->finish;
 729
 730     return (@notify);
 731
 732 }
 733
 734 =item AmountNotify
 735
 736 ($totalnotify) = &AmountNotify($notifyid);
 737
 738 Returns amount for all file per borrowers
 739 C<$notifyid> is the file number
 740
 741 C<$totalnotify> contains amount of a file
 742
 743 C<$notify_id> contains the file number for the borrower number and item number
 744
 745 =cut
 746
 747 sub AmountNotify{
 748     my ($notifyid,$borrowernumber)=@_;
 749     my $dbh = C4::Context->dbh;
 750     my $query=qq|    SELECT sum(amountoutstanding)
 751             FROM accountlines
 752             WHERE notify_id=? AND borrowernumber = ?|;
 753     my $sth=$dbh->prepare($query);
 754         $sth->execute($notifyid,$borrowernumber);
 755         my $totalnotify=$sth->fetchrow;
 756     $sth->finish;
 757     return ($totalnotify);
 758 }
 759
 760
 761 =item GetNotifyId
 762
 763 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
 764
 765 Returns the file number per borrower and itemnumber
 766
 767 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
 768 from the items tables of the Koha database. Thus,
 769
 770 C<$itemnumber> contains the borrower categorycode
 771
 772 C<$notify_id> contains the file number for the borrower number nad item number
 773
 774 =cut
 775
 776  sub GetNotifyId {
 777  my ($borrowernumber,$itemnumber)=@_;
 778  my $query=qq|SELECT notify_id
 779            FROM accountlines
 780            WHERE borrowernumber=?
 781           AND itemnumber=?
 782            AND (accounttype='FU' or accounttype='O')|;
 783  my $dbh = C4::Context->dbh;
 784  my $sth=$dbh->prepare($query);
 785  $sth->execute($borrowernumber,$itemnumber);
 786  my ($notify_id)=$sth->fetchrow;
 787  $sth->finish;
 788  return ($notify_id);
 789
 790  }
 791
 792 =item CreateItemAccountLine
 793
 794 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
 795
 796 update the account lines with file number or with file level
 797
 798 C<$items> is a reference-to-hash whose keys are all of the fields
 799 from the items tables of the Koha database. Thus,
 800
 801 C<$itemnumber> contains the item number
 802
 803 C<$borrowernumber> contains the borrower number
 804
 805 C<$date> contains the date of the day
 806
 807 C<$amount> contains item price
 808
 809 C<$description> contains the descritpion of accounttype
 810
 811 C<$accounttype> contains the account type
 812
 813 C<$amountoutstanding> contains the $amountoutstanding
 814
 815 C<$timestamp> contains the timestamp with time and the date of the day
 816
 817 C<$notify_id> contains the file number
 818
 819 C<$level> contains the file level
 820
 821
 822 =cut
 823
 824  sub CreateItemAccountLine {
 825   my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
 826   my $dbh = C4::Context->dbh;
 827   my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
 828    my $query= "INSERT into accountlines
 829          (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
 830           VALUES
 831              (?,?,?,?,?,?,?,?,?,?,?)";
 832
 833
 834   my $sth=$dbh->prepare($query);
 835   $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
 836   $sth->finish;
 837  }
 838
 839 =item UpdateAccountLines
 840
 841 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
 842
 843 update the account lines with file number or with file level
 844
 845 C<$items> is a reference-to-hash whose keys are all of the fields
 846 from the items tables of the Koha database. Thus,
 847
 848 C<$itemnumber> contains the item number
 849
 850 C<$notify_id> contains the file number
 851
 852 C<$notify_level> contains the file level
 853
 854 C<$borrowernumber> contains the borrowernumber
 855
 856 =cut
 857
 858 sub UpdateAccountLines {
 859 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
 860 my $query;
 861 if ($notify_id eq '')
 862 {
 863
 864     $query=qq|UPDATE accountlines
 865     SET  notify_level=?
 866     WHERE borrowernumber=? AND itemnumber=?
 867     AND (accounttype='FU' or accounttype='O')|;
 868 }else
 869 {
 870     $query=qq|UPDATE accountlines
 871      SET notify_id=?, notify_level=?
 872            WHERE borrowernumber=?
 873     AND itemnumber=?
 874         AND (accounttype='FU' or accounttype='O')|;
 875 }
 876  my $dbh = C4::Context->dbh;
 877  my $sth=$dbh->prepare($query);
 878
 879 if ($notify_id eq '')
 880 {
 881     $sth->execute($notify_level,$borrowernumber,$itemnumber);
 882 }else
 883 {
 884     $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
 885 }
 886  $sth->finish;
 887
 888 }
 889
 890
 891 =item GetItems
 892
 893 ($items) = &GetItems($itemnumber);
 894
 895 Returns the list of all delays from overduerules.
 896
 897 C<$items> is a reference-to-hash whose keys are all of the fields
 898 from the items tables of the Koha database. Thus,
 899
 900 C<$itemnumber> contains the borrower categorycode
 901
 902 =cut
 903
 904 sub GetItems {
 905     my($itemnumber) = @_;
 906     my $query=qq|SELECT *
 907              FROM items
 908               WHERE itemnumber=?|;
 909         my $dbh = C4::Context->dbh;
 910         my $sth=$dbh->prepare($query);
 911         $sth->execute($itemnumber);
 912         my ($items)=$sth->fetchrow_hashref;
 913         $sth->finish;
 914     return($items);
 915 }
 916
 917 =item GetOverdueDelays
 918
 919 (@delays) = &GetOverdueDelays($categorycode);
 920
 921 Returns the list of all delays from overduerules.
 922
 923 C<@delays> it's an array contains the three delays from overduerules table
 924
 925 C<$categorycode> contains the borrower categorycode
 926
 927 =cut
 928
 929 sub GetOverdueDelays {
 930     my($category) = @_;
 931     my $dbh = C4::Context->dbh;
 932         my $query=qq|SELECT delay1,delay2,delay3
 933                 FROM overduerules
 934                 WHERE categorycode=?|;
 935     my $sth=$dbh->prepare($query);
 936         $sth->execute($category);
 937         my (@delays)=$sth->fetchrow_array;
 938         $sth->finish;
 939         return(@delays);
 940 }
 941
 942 =item CheckAccountLineLevelInfo
 943
 944 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
 945
 946 Check and Returns the list of all overdue books.
 947
 948 C<$exist> contains number of line in accounlines
 949 with the same .biblionumber,itemnumber,accounttype,and notify_level
 950
 951 C<$borrowernumber> contains the borrower number
 952
 953 C<$itemnumber> contains item number
 954
 955 C<$accounttype> contains account type
 956
 957 C<$notify_level> contains the accountline level
 958
 959
 960 =cut
 961
 962 sub CheckAccountLineLevelInfo {
 963     my($borrowernumber,$itemnumber,$level) = @_;
 964     my $dbh = C4::Context->dbh;
 965         my $query=    qq|SELECT count(*)
 966             FROM accountlines
 967             WHERE borrowernumber =?
 968             AND itemnumber = ?
 969             AND notify_level=?|;
 970     my $sth=$dbh->prepare($query);
 971         $sth->execute($borrowernumber,$itemnumber,$level);
 972         my ($exist)=$sth->fetchrow;
 973         $sth->finish;
 974         return($exist);
 975 }
 976
 977 =item GetOverduerules
 978
 979 ($overduerules) = &GetOverduerules($categorycode);
 980
 981 Returns the value of borrowers (debarred or not) with notify level
 982
 983 C<$overduerules> return value of debbraed field in overduerules table
 984
 985 C<$category> contains the borrower categorycode
 986
 987 C<$notify_level> contains the notify level
 988 =cut
 989
 990
 991 sub GetOverduerules{
 992     my($category,$notify_level) = @_;
 993     my $dbh = C4::Context->dbh;
 994         my $query=qq|SELECT debarred$notify_level
 995              FROM overduerules
 996              WHERE categorycode=?|;
 997     my $sth=$dbh->prepare($query);
 998         $sth->execute($category);
 999         my ($overduerules)=$sth->fetchrow;
1000         $sth->finish;
1001         return($overduerules);
1002 }
1003
1004
1005 =item CheckBorrowerDebarred
1006
1007 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
1008
1009 Check if the borrowers is already debarred
1010
1011 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
1012
1013 C<$borrowernumber> contains the borrower number
1014
1015 =cut
1016
1017
1018 sub CheckBorrowerDebarred{
1019     my($borrowernumber) = @_;
1020     my $dbh = C4::Context->dbh;
1021         my $query=qq|SELECT debarred
1022               FROM borrowers
1023              WHERE borrowernumber=?
1024             |;
1025     my $sth=$dbh->prepare($query);
1026         $sth->execute($borrowernumber);
1027         my ($debarredstatus)=$sth->fetchrow;
1028         $sth->finish;
1029         if ($debarredstatus eq '1'){
1030     return(1);}
1031     else{
1032     return(0);
1033     }
1034 }
1035
1036 =item UpdateBorrowerDebarred
1037
1038 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1039
1040 update status of borrowers in borrowers table (field debarred)
1041
1042 C<$borrowernumber> borrower number
1043
1044 =cut
1045
1046 sub UpdateBorrowerDebarred{
1047     my($borrowernumber) = @_;
1048     my $dbh = C4::Context->dbh;
1049         my $query=qq|UPDATE borrowers
1050              SET debarred='1'
1051                      WHERE borrowernumber=?
1052             |;
1053     my $sth=$dbh->prepare($query);
1054         $sth->execute($borrowernumber);
1055         $sth->finish;
1056         return 1;
1057 }
1058
1059 =item CheckExistantNotifyid
1060
1061   ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1062
1063 Check and Returns the notify id if exist else return 0.
1064
1065 C<$exist> contains a notify_id
1066
1067 C<$borrowernumber> contains the borrower number
1068
1069 C<$date_due> contains the date of item return
1070
1071
1072 =cut
1073
1074 sub CheckExistantNotifyid {
1075      my($borrowernumber,$date_due) = @_;
1076      my $dbh = C4::Context->dbh;
1077          my $query =  qq|SELECT notify_id FROM accountlines
1078              LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1079              WHERE accountlines.borrowernumber =?
1080               AND date_due = ?|;
1081     my $sth=$dbh->prepare($query);
1082          $sth->execute($borrowernumber,$date_due);
1083          my ($exist)=$sth->fetchrow;
1084          $sth->finish;
1085          if ($exist eq '')
1086     {
1087     return(0);
1088     }else
1089         {
1090     return($exist);
1091     }
1092 }
1093
1094 =item CheckAccountLineItemInfo
1095
1096   ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1097
1098 Check and Returns the list of all overdue items from the same file number(notify_id).
1099
1100 C<$exist> contains number of line in accounlines
1101 with the same .biblionumber,itemnumber,accounttype,notify_id
1102
1103 C<$borrowernumber> contains the borrower number
1104
1105 C<$itemnumber> contains item number
1106
1107 C<$accounttype> contains account type
1108
1109 C<$notify_id> contains the file number
1110
1111 =cut
1112
1113 sub CheckAccountLineItemInfo {
1114      my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1115      my $dbh = C4::Context->dbh;
1116          my $query =  qq|SELECT count(*) FROM accountlines
1117              WHERE borrowernumber =?
1118              AND itemnumber = ?
1119               AND accounttype= ?
1120             AND notify_id = ?|;
1121     my $sth=$dbh->prepare($query);
1122          $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1123          my ($exist)=$sth->fetchrow;
1124          $sth->finish;
1125          return($exist);
1126  }
1127
1128 =head2 CheckItemNotify
1129
1130 Sql request to check if the document has alreday been notified
1131 this function is not exported, only used with GetOverduesForBranch
1132
1133 =cut
1134
1135 sub CheckItemNotify {
1136         my ($notify_id,$notify_level,$itemnumber) = @_;
1137         my $dbh = C4::Context->dbh;
1138         my $sth = $dbh->prepare("
1139           SELECT COUNT(*) FROM notifys
1140  WHERE notify_id  = ?
1141  AND notify_level  = ?
1142   AND  itemnumber  =  ? ");
1143  $sth->execute($notify_id,$notify_level,$itemnumber);
1144         my $notified = $sth->fetchrow;
1145 $sth->finish;
1146 return ($notified);
1147 }
1148
1149 =head2 GetOverduesForBranch
1150
1151 Sql request for display all information for branchoverdues.pl
1152 2 possibilities : with or without location .
1153 display is filtered by branch
1154
1155 =cut
1156
1157 sub GetOverduesForBranch {
1158     my ( $branch, $location) = @_;
1159         my $itype_link =  (C4::Context->preference('item-level_itypes')) ?  " items.itype " :  " biblioitems.itemtype ";
1160     if ( not $location ) {
1161         my $dbh = C4::Context->dbh;
1162         my $sth = $dbh->prepare("
1163             SELECT
1164                 borrowers.surname,
1165                 borrowers.firstname,
1166                 biblio.title,
1167                 itemtypes.description,
1168                 issues.date_due,
1169                 issues.returndate,
1170                 branches.branchname,
1171                 items.barcode,
1172                 borrowers.phone,
1173                 borrowers.email,
1174                 items.itemcallnumber,
1175                 borrowers.borrowernumber,
1176                 items.itemnumber,
1177                 biblio.biblionumber,
1178                 issues.branchcode,
1179                 accountlines.notify_id,
1180                 accountlines.notify_level,
1181                 items.location,
1182                 accountlines.amountoutstanding
1183             FROM  accountlines
1184             LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1185             LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1186             LEFT JOIN items ON items.itemnumber = issues.itemnumber
1187             LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1188             LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1189             LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1190             LEFT JOIN branches ON branches.branchcode = issues.branchcode
1191             WHERE ( accountlines.amountoutstanding  != '0.000000')
1192               AND ( accountlines.accounttype  = 'FU')
1193               AND (issues.branchcode = ?)
1194               AND (issues.date_due <= NOW())
1195             ORDER BY  borrowers.surname
1196         ");
1197         $sth->execute($branch);
1198         my @getoverdues;
1199         my $i = 0;
1200         while ( my $data = $sth->fetchrow_hashref ) {
1201         #check if the document has already been notified
1202         my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1203         if ($countnotify eq '0'){
1204             $getoverdues[$i] = $data;
1205             $i++;
1206          }
1207         }
1208         return (@getoverdues);
1209         $sth->finish;
1210     }
1211     else {
1212         my $dbh = C4::Context->dbh;
1213         my $sth = $dbh->prepare( "
1214             SELECT  borrowers.surname,
1215                     borrowers.firstname,
1216                     biblio.title,
1217                     itemtypes.description,
1218                     issues.date_due,
1219                     issues.returndate,
1220                     branches.branchname,
1221                     items.barcode,
1222                     borrowers.phone,
1223                     borrowers.email,
1224                     items.itemcallnumber,
1225                     borrowers.borrowernumber,
1226                     items.itemnumber,
1227                     biblio.biblionumber,
1228                     issues.branchcode,
1229                     accountlines.notify_id,
1230                     accountlines.notify_level,
1231                     items.location,
1232                     accountlines.amountoutstanding
1233             FROM  accountlines
1234             LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1235             LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1236             LEFT JOIN items ON items.itemnumber = issues.itemnumber
1237             LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1238             LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1239             LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1240             LEFT JOIN branches ON branches.branchcode = issues.branchcode
1241            WHERE ( accountlines.amountoutstanding  != '0.000000')
1242              AND ( accountlines.accounttype  = 'FU')
1243              AND (issues.branchcode = ? AND items.location = ?)
1244              AND (issues.date_due <= NOW())
1245            ORDER BY  borrowers.surname
1246         " );
1247         $sth->execute( $branch, $location);
1248         my @getoverdues;
1249         my $i = 0;
1250         while ( my $data = $sth->fetchrow_hashref ) {
1251         #check if the document has already been notified
1252           my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1253           if ($countnotify eq '0'){
1254                 $getoverdues[$i] = $data;
1255                  $i++;
1256          }
1257         }
1258         $sth->finish;
1259         return (@getoverdues);
1260     }
1261 }
1262
1263
1264 =head2 AddNotifyLine
1265
1266 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1267
1268 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1269
1270 =cut
1271
1272 sub AddNotifyLine {
1273     my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1274     if ( $method eq "phone" ) {
1275         my $dbh = C4::Context->dbh;
1276         my $sth = $dbh->prepare(
1277             "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1278         VALUES (?,?,now(),now(),?,?,?)"
1279         );
1280         $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1281             $notifyId );
1282         $sth->finish;
1283     }
1284     else {
1285         my $dbh = C4::Context->dbh;
1286         my $sth = $dbh->prepare(
1287             "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1288         VALUES (?,?,now(),?,?,?)"
1289         );
1290         $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1291             $notifyId );
1292         $sth->finish;
1293     }
1294     return 1;
1295 }
1296
1297 =head2 RemoveNotifyLine
1298
1299 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1300
1301 Cancel a notification
1302
1303 =cut
1304
1305 sub RemoveNotifyLine {
1306     my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1307     my $dbh = C4::Context->dbh;
1308     my $sth = $dbh->prepare(
1309         "DELETE FROM notifys
1310             WHERE
1311             borrowernumber=?
1312             AND itemnumber=?
1313             AND notify_date=?"
1314     );
1315     $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1316     $sth->finish;
1317     return 1;
1318 }
1319
1320 1;
1321 __END__
1322
1323 =back
1324
1325 =head1 AUTHOR
1326
1327 Koha Developement team <info@koha.org>
1328
1329 =cut