4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
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
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.
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
24 use Date::Calc qw/Today/;
25 use vars qw($VERSION @ISA @EXPORT);
27 use Date::Manip qw/UnixDate/;
28 use C4::Log; # logaction
30 # set the version for version checking
35 C4::Circulation::Fines - Koha module dealing with fines
43 This module contains several functions for dealing with fines for
44 overdue items. It is primarily used by the 'misc/fines2.pl' script.
53 # subs to rename (and maybe merge some...)
58 &CheckAccountLineLevelInfo
59 &CheckAccountLineItemInfo
60 &CheckExistantNotifyid
70 &CreateItemAccountLine
84 # All subs to move : check that an equivalent don't exist already before moving
87 # subs to move to Circulation.pm
92 # subs to move to Members.pm
94 &CheckBorrowerDebarred
95 &UpdateBorrowerDebarred
97 # subs to move to Biblio.pm
105 ($overdues) = &Getoverdues();
107 Returns the list of all overdue books, with their itemtype.
109 C<$overdues> is a reference-to-array. Each element is a
110 reference-to-hash whose keys are the fields of the issues table in the
117 my $dbh = C4::Context->dbh;
118 my $sth = (C4::context->preference('item-level_itypes')) ?
120 "SELECT issues.*,items.itype as itemtype FROM issues
121 LEFT JOIN items USING (itemnumber)
122 WHERE date_due < now()
123 AND returndate IS NULL ORDER BY borrowernumber " )
126 "SELECT issues.*,biblioitems.itemtype,items.itype FROM issues
127 LEFT JOIN items USING (itemnumber)
128 LEFT JOIN biblioitems USING (biblioitemnumber)
129 WHERE date_due < now()
131 NULL ORDER BY borrowernumber " );
135 while ( my $data = $sth->fetchrow_hashref ) {
136 push @results, $data;
145 ( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh );
153 # From Main.pm, modified to return a list of overdueitems, in addition to a count
154 #checks whether a borrower has overdue items
155 my ( $borrowernumber, $dbh ) = @_;
156 my @datearr = localtime;
158 ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
161 my $sth = $dbh->prepare(
162 "SELECT * FROM issues
163 LEFT JOIN items ON issues.itemnumber = items.itemnumber
164 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
165 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
166 WHERE issues.borrowernumber = ?
167 AND issues.returndate is NULL
168 AND issues.date_due < ?"
170 $sth->execute( $borrowernumber, $today );
171 while ( my $data = $sth->fetchrow_hashref ) {
172 push( @overdueitems, $data );
176 return ( $count, \@overdueitems );
181 ($amount, $chargename, $message) =
182 &CalcFine($itemnumber, $borrowercode, $days_overdue);
184 Calculates the fine for a book.
186 The issuingrules table in the Koha database is a fine matrix, listing
187 the penalties for each type of patron for each type of item and each branch (e.g., the
188 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
189 members might get a longer grace period between the first and second
190 reminders that a book is overdue).
192 The fine is calculated as follows: if it is time for the first
193 reminder, the fine is the value listed for the given (branch, item type,
194 borrower code) combination. If it is time for the second reminder, the
195 fine is doubled. Finally, if it is time to send the account to a
196 collection agency, the fine is set to 5 local monetary units (a really
197 good deal for the patron if the library is in Italy). Otherwise, the
200 Note that the way this function is currently implemented, it only
201 returns a nonzero value on the notable days listed above. That is, if
202 the categoryitems entry says to send a first reminder 7 days after the
203 book is due, then if you call C<&CalcFine> 7 days after the book is
204 due, it will give a nonzero fine. If you call C<&CalcFine> the next
205 day, however, it will say that the fine is 0.
207 C<$itemnumber> is the book's item number.
209 C<$borrowercode> is the borrower code of the patron who currently has
212 C<$days_overdue> is the number of days elapsed since the book's due
215 C<&CalcFine> returns a list of three values:
217 C<$amount> is the fine owed by the patron (see above).
219 C<$chargename> is the chargename field from the applicable record in
220 the categoryitem table, whatever that is.
222 C<$message> is a text message, either "First Notice", "Second Notice",
229 my ( $item, $bortype, $difference , $dues ) = @_;
230 my $dbh = C4::Context->dbh;
233 # calculate how many days the patron is late
234 my $countspecialday=&GetSpecialHolidays($dues,$item->{itemnumber});
235 my $countrepeatableday=&GetRepeatableHolidays($dues,$item->{itemnumber},$difference);
236 my $countalldayclosed = $countspecialday + $countrepeatableday;
237 my $daycount = $difference - $countalldayclosed;
238 # get issuingrules (fines part will be used)
239 my $data = GetIssuingRules($item->{'itemtype'},$bortype);
240 my $daycounttotal = $daycount - $data->{'firstremind'};
241 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
242 if ($data->{'firstremind'} < $daycount)
244 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
247 # get fines default rules
248 my $data = GetIssuingRules($item->{'itemtype'},'*');
249 $daycounttotal = $daycount - $data->{'firstremind'};
250 if ($data->{'firstremind'} < $daycount)
252 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
253 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
258 warn "Calc Fine for $item->{'itemnumber'}, $bortype, $difference , $dues = $amount / $daycount";
259 return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
263 =item GetSpecialHolidays
265 &GetSpecialHolidays($date_dues,$itemnumber);
267 return number of special days between date of the day and date due
269 C<$date_dues> is the envisaged date of book return.
271 C<$itemnumber> is the book's item number.
275 sub GetSpecialHolidays {
276 my ($date_dues,$itemnumber) = @_;
277 # calcul the today date
278 my $today = join "-", &Today();
280 # return the holdingbranch
281 my $iteminfo=GetIssuesIteminfo($itemnumber);
282 # use sql request to find all date between date_due and today
283 my $dbh = C4::Context->dbh;
284 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
285 FROM `special_holidays`
286 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
287 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
290 my @result=GetWdayFromItemnumber($itemnumber);
294 my $sth = $dbh->prepare($query);
295 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
297 while ( my $special_date=$sth->fetchrow_hashref){
298 push (@result_date,$special_date);
301 my $specialdaycount=scalar(@result_date);
303 for (my $i=0;$i<scalar(@result_date);$i++){
304 $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
305 (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
306 for (my $j=0;$j<scalar(@result);$j++){
307 if ($wday == ($result[$j]->{'weekday'})){
313 return $specialdaycount;
316 =item GetRepeatableHolidays
318 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
320 return number of day closed between date of the day and date due
322 C<$date_dues> is the envisaged date of book return.
324 C<$itemnumber> is item number.
326 C<$difference> numbers of between day date of the day and date due
330 sub GetRepeatableHolidays{
331 my ($date_dues,$itemnumber,$difference) = @_;
332 my $dateinsec=UnixDate($date_dues,"%o");
333 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
334 my @result=GetWdayFromItemnumber($itemnumber);
338 for (my $i=0;$i<scalar(@result);$i++){
341 for ( $j=0;$j<$difference;$j++){
342 if ($result[$i]->{'weekday'} == $k)
344 push ( @dayclosedcount ,$k);
350 return scalar(@dayclosedcount);
354 =item GetWayFromItemnumber
356 &Getwdayfromitemnumber($itemnumber);
358 return the different week day from repeatable_holidays table
360 C<$itemnumber> is item number.
364 sub GetWdayFromItemnumber{
366 my $iteminfo=GetIssuesIteminfo($itemnumber);
368 my $dbh = C4::Context->dbh;
369 my $query = qq|SELECT weekday
370 FROM repeatable_holidays
373 my $sth = $dbh->prepare($query);
376 $sth->execute($iteminfo->{'branchcode'});
377 while ( my $weekday=$sth->fetchrow_hashref){
378 push (@result,$weekday);
384 =item GetIssuesIteminfo
386 &GetIssuesIteminfo($itemnumber);
388 return all data from issues about item
390 C<$itemnumber> is item number.
394 sub GetIssuesIteminfo{
396 my $dbh = C4::Context->dbh;
397 my $query = qq|SELECT *
401 my $sth = $dbh->prepare($query);
402 $sth->execute($itemnumber);
403 my ($issuesinfo)=$sth->fetchrow_hashref;
410 &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
412 (Note: the following is mostly conjecture and guesswork.)
414 Updates the fine owed on an overdue book.
416 C<$itemnumber> is the book's item number.
418 C<$borrowernumber> is the borrower number of the patron who currently
419 has the book on loan.
421 C<$amount> is the current amount owed by the patron.
423 C<$type> will be used in the description of the fine.
425 C<$description> is a string that must be present in the description of
426 the fine. I think this is expected to be a date in DD/MM/YYYY format.
428 C<&UpdateFine> looks up the amount currently owed on the given item
429 and sets it to C<$amount>, creating, if necessary, a new entry in the
430 accountlines table of the Koha database.
435 # FIXME - This API doesn't look right: why should the caller have to
436 # specify both the item number and the borrower number? A book can't
437 # be on loan to two different people, so the item number should be
440 my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
441 my $dbh = C4::Context->dbh;
442 # FIXME - What exactly is this query supposed to do? It looks up an
443 # entry in accountlines that matches the given item and borrower
444 # numbers, where the description contains $due, and where the
445 # account type has one of several values, but what does this _mean_?
446 # Does it look up existing fines for this item?
447 # FIXME - What are these various account types? ("FU", "O", "F", "M")
448 my $sth = $dbh->prepare(
449 "Select * from accountlines where itemnumber=? and
450 borrowernumber=? and (accounttype='FU' or accounttype='O' or
451 accounttype='F' or accounttype='M') and description like ?"
453 $sth->execute( $itemnum, $borrowernumber, "%$due%" );
455 if ( my $data = $sth->fetchrow_hashref ) {
457 # I think this if-clause deals with the case where we're updating
459 # print "in accounts ...";
460 if ( $data->{'amount'} != $amount ) {
463 my $diff = $amount - $data->{'amount'};
464 my $out = $data->{'amountoutstanding'} + $diff;
465 my $sth2 = $dbh->prepare(
466 "UPDATE accountlines SET date=now(), amount=?,
467 amountoutstanding=?,accounttype='FU' WHERE
468 borrowernumber=? AND itemnumber=?
469 AND (accounttype='FU' OR accounttype='O') AND description LIKE ?"
471 $sth2->execute( $amount, $out, $data->{'borrowernumber'},
472 $data->{'itemnumber'}, "%$due%" );
477 # print "no update needed $data->{'amount'}"
482 # I think this else-clause deals with the case where we're adding
484 my $sth4 = $dbh->prepare(
485 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
487 $sth4->execute($itemnum);
488 my $title = $sth4->fetchrow_hashref;
491 # # print "not in account";
492 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
495 # # FIXME - Make $accountno a scalar.
496 # my @accountno = $sth3->fetchrow_array;
500 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
501 my $sth2 = $dbh->prepare(
502 "INSERT INTO accountlines
503 (borrowernumber,itemnumber,date,amount,
504 description,accounttype,amountoutstanding,accountno) VALUES
505 (?,?,now(),?,?,'FU',?,?)"
507 $sth2->execute( $borrowernumber, $itemnum, $amount,
508 "$type $title->{'title'} $due",
509 $amount, $nextaccntno);
514 C4::Context->userenv->{'number'},
518 "due=".$due." amount=".$amount." itemnumber=".$itemnum
519 ) if C4::Context->preference("FinesLog");
526 $borrower = &BorType($borrowernumber);
528 Looks up a patron by borrower number.
530 C<$borrower> is a reference-to-hash whose keys are all of the fields
531 from the borrowers and categories tables of the Koha database. Thus,
532 C<$borrower> contains all information about both the borrower and
533 category he or she belongs to.
539 my ($borrowernumber) = @_;
540 my $dbh = C4::Context->dbh;
541 my $sth = $dbh->prepare(
542 "SELECT * from borrowers
543 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
544 WHERE borrowernumber=?"
546 $sth->execute($borrowernumber);
547 my $data = $sth->fetchrow_hashref;
552 =item ReplacementCost
554 $cost = &ReplacementCost($itemnumber);
556 Returns the replacement cost of the item with the given item number.
561 sub ReplacementCost {
563 my $dbh = C4::Context->dbh;
565 $dbh->prepare("Select replacementprice from items where itemnumber=?");
566 $sth->execute($itemnum);
568 # FIXME - Use fetchrow_array or something.
569 my $data = $sth->fetchrow_hashref;
571 return ( $data->{'replacementprice'} );
576 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
578 return the total of fine
580 C<$itemnum> is item number
582 C<$borrowernumber> is the borrowernumber
588 my ( $itemnum, $borrowernumber ) = @_;
589 my $dbh = C4::Context->dbh();
590 my $query = "SELECT sum(amountoutstanding) FROM accountlines
591 where accounttype like 'F%'
592 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
593 my $sth = $dbh->prepare($query);
594 $sth->execute( $itemnum, $borrowernumber );
595 my $data = $sth->fetchrow_hashref();
598 return ( $data->{'sum(amountoutstanding)'} );
604 =item GetIssuingRules
606 $data = &GetIssuingRules($itemtype,$categorycode);
608 Looks up for all issuingrules an item info
610 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
611 from the borrowers and categories tables of the Koha database. Thus,
613 C<$categorycode> contains information about borrowers category
615 C<$data> contains all information about both the borrower and
616 category he or she belongs to.
619 sub GetIssuingRules {
620 my ($itemtype,$categorycode)=@_;
621 my $dbh = C4::Context->dbh();
622 my $query=qq|SELECT *
624 WHERE issuingrules.itemtype=?
625 AND issuingrules.categorycode=?
627 my $sth = $dbh->prepare($query);
629 $sth->execute($itemtype,$categorycode);
630 my ($data) = $sth->fetchrow_hashref;
637 sub ReplacementCost2 {
638 my ( $itemnum, $borrowernumber ) = @_;
639 my $dbh = C4::Context->dbh();
640 my $query = "SELECT amountoutstanding
642 WHERE accounttype like 'L'
643 AND amountoutstanding > 0
645 AND borrowernumber= ?";
646 my $sth = $dbh->prepare($query);
647 $sth->execute( $itemnum, $borrowernumber );
648 my $data = $sth->fetchrow_hashref();
651 return ( $data->{'amountoutstanding'} );
655 =item GetNextIdNotify
657 ($result) = &GetNextIdNotify($reference);
659 Returns the new file number
661 C<$result> contains the next file number
663 C<$reference> contains the beggining of file number
669 sub GetNextIdNotify {
671 my $query=qq|SELECT max(notify_id)
673 WHERE notify_id like \"$reference%\"
675 # AND borrowernumber=?|;
676 my $dbh = C4::Context->dbh;
677 my $sth=$dbh->prepare($query);
679 my $result=$sth->fetchrow;
684 ($result=$reference."01") ;
687 $count=substr($result,6)+1;
690 ($count = "0".$count);
692 $result=$reference.$count;
700 (@notify) = &AmountNotify($borrowernumber);
702 Returns amount for all file per borrowers
703 C<@notify> array contains all file per borrowers
705 C<$notify_id> contains the file number for the borrower number nad item number
710 my ($borrowernumber)=@_;
711 my $dbh = C4::Context->dbh;
712 my $query=qq| SELECT distinct(notify_id)
714 WHERE borrowernumber=?|;
716 my $sth=$dbh->prepare($query);
717 $sth->execute($borrowernumber);
718 while ( my ($numberofnotify)=$sth->fetchrow){
719 push (@notify,$numberofnotify);
729 ($totalnotify) = &AmountNotify($notifyid);
731 Returns amount for all file per borrowers
732 C<$notifyid> is the file number
734 C<$totalnotify> contains amount of a file
736 C<$notify_id> contains the file number for the borrower number nad item number
742 my $dbh = C4::Context->dbh;
743 my $query=qq| SELECT sum(amountoutstanding)
746 my $sth=$dbh->prepare($query);
747 $sth->execute($notifyid);
748 my $totalnotify=$sth->fetchrow;
750 return ($totalnotify);
756 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
758 Returns the file number per borrower and itemnumber
760 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
761 from the items tables of the Koha database. Thus,
763 C<$itemnumber> contains the borrower categorycode
765 C<$notify_id> contains the file number for the borrower number nad item number
770 my ($borrowernumber,$itemnumber)=@_;
771 my $query=qq|SELECT notify_id
773 WHERE borrowernumber=?
775 AND (accounttype='FU' or accounttype='O')|;
776 my $dbh = C4::Context->dbh;
777 my $sth=$dbh->prepare($query);
778 $sth->execute($borrowernumber,$itemnumber);
779 my ($notify_id)=$sth->fetchrow;
785 =item CreateItemAccountLine
787 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
789 update the account lines with file number or with file level
791 C<$items> is a reference-to-hash whose keys are all of the fields
792 from the items tables of the Koha database. Thus,
794 C<$itemnumber> contains the item number
796 C<$borrowernumber> contains the borrower number
798 C<$date> contains the date of the day
800 C<$amount> contains item price
802 C<$description> contains the descritpion of accounttype
804 C<$accounttype> contains the account type
806 C<$amountoutstanding> contains the $amountoutstanding
808 C<$timestamp> contains the timestamp with time and the date of the day
810 C<$notify_id> contains the file number
812 C<$level> contains the file level
817 sub CreateItemAccountLine {
818 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
819 my $dbh = C4::Context->dbh;
820 my $nextaccntno = getnextacctno($borrowernumber);
821 my $query= "INSERT into accountlines
822 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
824 (?,?,?,?,?,?,?,?,?,?,?)";
827 my $sth=$dbh->prepare($query);
828 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
832 =item UpdateAccountLines
834 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
836 update the account lines with file number or with file level
838 C<$items> is a reference-to-hash whose keys are all of the fields
839 from the items tables of the Koha database. Thus,
841 C<$itemnumber> contains the item number
843 C<$notify_id> contains the file number
845 C<$notify_level> contains the file level
847 C<$borrowernumber> contains the borrowernumber
851 sub UpdateAccountLines {
852 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
854 if ($notify_id eq '')
857 $query=qq|UPDATE accountlines
859 WHERE borrowernumber=? AND itemnumber=?
860 AND (accounttype='FU' or accounttype='O')|;
863 $query=qq|UPDATE accountlines
864 SET notify_id=?, notify_level=?
865 WHERE borrowernumber=?
867 AND (accounttype='FU' or accounttype='O')|;
869 my $dbh = C4::Context->dbh;
870 my $sth=$dbh->prepare($query);
872 if ($notify_id eq '')
874 $sth->execute($notify_level,$borrowernumber,$itemnumber);
877 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
886 ($items) = &GetItems($itemnumber);
888 Returns the list of all delays from overduerules.
890 C<$items> is a reference-to-hash whose keys are all of the fields
891 from the items tables of the Koha database. Thus,
893 C<$itemnumber> contains the borrower categorycode
898 my($itemnumber) = @_;
899 my $query=qq|SELECT *
902 my $dbh = C4::Context->dbh;
903 my $sth=$dbh->prepare($query);
904 $sth->execute($itemnumber);
905 my ($items)=$sth->fetchrow_hashref;
910 =item GetOverdueDelays
912 (@delays) = &GetOverdueDelays($categorycode);
914 Returns the list of all delays from overduerules.
916 C<@delays> it's an array contains the three delays from overduerules table
918 C<$categorycode> contains the borrower categorycode
922 sub GetOverdueDelays {
924 my $dbh = C4::Context->dbh;
925 my $query=qq|SELECT delay1,delay2,delay3
927 WHERE categorycode=?|;
928 my $sth=$dbh->prepare($query);
929 $sth->execute($category);
930 my (@delays)=$sth->fetchrow_array;
935 =item CheckAccountLineLevelInfo
937 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
939 Check and Returns the list of all overdue books.
941 C<$exist> contains number of line in accounlines
942 with the same .biblionumber,itemnumber,accounttype,and notify_level
944 C<$borrowernumber> contains the borrower number
946 C<$itemnumber> contains item number
948 C<$accounttype> contains account type
950 C<$notify_level> contains the accountline level
955 sub CheckAccountLineLevelInfo {
956 my($borrowernumber,$itemnumber,$level) = @_;
957 my $dbh = C4::Context->dbh;
958 my $query= qq|SELECT count(*)
960 WHERE borrowernumber =?
963 my $sth=$dbh->prepare($query);
964 $sth->execute($borrowernumber,$itemnumber,$level);
965 my ($exist)=$sth->fetchrow;
970 =item GetOverduerules
972 ($overduerules) = &GetOverduerules($categorycode);
974 Returns the value of borrowers (debarred or not) with notify level
976 C<$overduerules> return value of debbraed field in overduerules table
978 C<$category> contains the borrower categorycode
980 C<$notify_level> contains the notify level
985 my($category,$notify_level) = @_;
986 my $dbh = C4::Context->dbh;
987 my $query=qq|SELECT debarred$notify_level
989 WHERE categorycode=?|;
990 my $sth=$dbh->prepare($query);
991 $sth->execute($category);
992 my ($overduerules)=$sth->fetchrow;
994 return($overduerules);
998 =item CheckBorrowerDebarred
1000 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
1002 Check if the borrowers is already debarred
1004 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
1006 C<$borrowernumber> contains the borrower number
1011 sub CheckBorrowerDebarred{
1012 my($borrowernumber) = @_;
1013 my $dbh = C4::Context->dbh;
1014 my $query=qq|SELECT debarred
1016 WHERE borrowernumber=?
1018 my $sth=$dbh->prepare($query);
1019 $sth->execute($borrowernumber);
1020 my ($debarredstatus)=$sth->fetchrow;
1022 if ($debarredstatus eq '1'){
1029 =item UpdateBorrowerDebarred
1031 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1033 update status of borrowers in borrowers table (field debarred)
1035 C<$borrowernumber> borrower number
1039 sub UpdateBorrowerDebarred{
1040 my($borrowernumber) = @_;
1041 my $dbh = C4::Context->dbh;
1042 my $query=qq|UPDATE borrowers
1044 WHERE borrowernumber=?
1046 my $sth=$dbh->prepare($query);
1047 $sth->execute($borrowernumber);
1052 =item CheckExistantNotifyid
1054 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1056 Check and Returns the notify id if exist else return 0.
1058 C<$exist> contains a notify_id
1060 C<$borrowernumber> contains the borrower number
1062 C<$date_due> contains the date of item return
1067 sub CheckExistantNotifyid {
1068 my($borrowernumber,$date_due) = @_;
1069 my $dbh = C4::Context->dbh;
1070 my $query = qq|SELECT notify_id FROM accountlines
1071 LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1072 WHERE accountlines.borrowernumber =?
1074 my $sth=$dbh->prepare($query);
1075 $sth->execute($borrowernumber,$date_due);
1076 my ($exist)=$sth->fetchrow;
1087 =item CheckAccountLineItemInfo
1089 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1091 Check and Returns the list of all overdue items from the same file number(notify_id).
1093 C<$exist> contains number of line in accounlines
1094 with the same .biblionumber,itemnumber,accounttype,notify_id
1096 C<$borrowernumber> contains the borrower number
1098 C<$itemnumber> contains item number
1100 C<$accounttype> contains account type
1102 C<$notify_id> contains the file number
1106 sub CheckAccountLineItemInfo {
1107 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1108 my $dbh = C4::Context->dbh;
1109 my $query = qq|SELECT count(*) FROM accountlines
1110 WHERE borrowernumber =?
1114 my $sth=$dbh->prepare($query);
1115 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1116 my ($exist)=$sth->fetchrow;
1121 =head2 CheckItemNotify
1123 Sql request to check if the document has alreday been notified
1124 this function is not exported, only used with GetOverduesForBranch
1128 sub CheckItemNotify {
1129 my ($notify_id,$notify_level,$itemnumber) = @_;
1130 my $dbh = C4::Context->dbh;
1131 my $sth = $dbh->prepare("
1132 SELECT COUNT(*) FROM notifys
1134 AND notify_level = ?
1135 AND itemnumber = ? ");
1136 $sth->execute($notify_id,$notify_level,$itemnumber);
1137 my $notified = $sth->fetchrow;
1142 =head2 GetOverduesForBranch
1144 Sql request for display all information for branchoverdues.pl
1145 2 possibilities : with or without location .
1146 display is filtered by branch
1150 sub GetOverduesForBranch {
1151 my ( $branch, $location) = @_;
1152 my $itype_link = (C4::context->preference('item-level_itypes')) ? " items.itype " : " biblioitems.itemtype ";
1153 if ( not $location ) {
1154 my $dbh = C4::Context->dbh;
1155 my $sth = $dbh->prepare("
1158 borrowers.firstname,
1160 itemtypes.description,
1163 branches.branchname,
1167 items.itemcallnumber,
1168 borrowers.borrowernumber,
1170 biblio.biblionumber,
1172 accountlines.notify_id,
1173 accountlines.notify_level,
1175 accountlines.amountoutstanding
1177 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1178 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1179 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1180 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1181 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1182 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1183 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1184 WHERE ( issues.returndate is null)
1185 AND ( accountlines.amountoutstanding != '0.000000')
1186 AND ( accountlines.accounttype = 'FU')
1187 AND (issues.branchcode = ?)
1188 AND (issues.date_due <= NOW())
1189 ORDER BY borrowers.surname
1191 $sth->execute($branch);
1194 while ( my $data = $sth->fetchrow_hashref ) {
1195 #check if the document has already been notified
1196 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1197 if ($countnotify eq '0'){
1198 $getoverdues[$i] = $data;
1202 return (@getoverdues);
1206 my $dbh = C4::Context->dbh;
1207 my $sth = $dbh->prepare( "
1208 SELECT borrowers.surname,
1209 borrowers.firstname,
1211 itemtypes.description,
1214 branches.branchname,
1218 items.itemcallnumber,
1219 borrowers.borrowernumber,
1221 biblio.biblionumber,
1223 accountlines.notify_id,
1224 accountlines.notify_level,
1226 accountlines.amountoutstanding
1228 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1229 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1230 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1231 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1232 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1233 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1234 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1235 WHERE ( issues.returndate is null )
1236 AND ( accountlines.amountoutstanding != '0.000000')
1237 AND ( accountlines.accounttype = 'FU')
1238 AND (issues.branchcode = ? AND items.location = ?)
1239 AND (issues.date_due <= NOW())
1240 ORDER BY borrowers.surname
1242 $sth->execute( $branch, $location);
1245 while ( my $data = $sth->fetchrow_hashref ) {
1246 #check if the document has already been notified
1247 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1248 if ($countnotify eq '0'){
1249 $getoverdues[$i] = $data;
1254 return (@getoverdues);
1259 =head2 AddNotifyLine
1261 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1263 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1268 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1269 if ( $method eq "phone" ) {
1270 my $dbh = C4::Context->dbh;
1271 my $sth = $dbh->prepare(
1272 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1273 VALUES (?,?,now(),now(),?,?,?)"
1275 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1280 my $dbh = C4::Context->dbh;
1281 my $sth = $dbh->prepare(
1282 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1283 VALUES (?,?,now(),?,?,?)"
1285 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1292 =head2 RemoveNotifyLine
1294 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1296 Cancel a notification
1300 sub RemoveNotifyLine {
1301 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1302 my $dbh = C4::Context->dbh;
1303 my $sth = $dbh->prepare(
1304 "DELETE FROM notifys
1310 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1322 Koha Developement team <info@koha.org>