5 # Copyright 2000-2002 Katipo Communications
7 # This file is part of Koha.
9 # Koha is free software; you can redistribute it and/or modify it under the
10 # terms of the GNU General Public License as published by the Free Software
11 # Foundation; either version 2 of the License, or (at your option) any later
14 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
15 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
16 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
18 # You should have received a copy of the GNU General Public License along with
19 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
20 # Suite 330, Boston, MA 02111-1307 USA
25 use Date::Calc qw/Today/;
26 use vars qw($VERSION @ISA @EXPORT);
28 use Date::Manip qw/UnixDate/;
29 use C4::Log; # logaction
31 # set the version for version checking
32 $VERSION = do { my @v = '$Revision$' =~ /\d+/g;
33 shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
37 C4::Circulation::Fines - Koha module dealing with fines
45 This module contains several functions for dealing with fines for
46 overdue items. It is primarily used by the 'misc/fines2.pl' script.
55 # subs to rename (and maybe merge some...)
60 &CheckAccountLineLevelInfo
61 &CheckAccountLineItemInfo
62 &CheckExistantNotifyid
72 &CreateItemAccountLine
86 # All subs to move : check that an equivalent don't exist already before moving
89 # subs to move to Circulation.pm
94 # subs to move to Members.pm
96 &CheckBorrowerDebarred
97 &UpdateBorrowerDebarred
99 # subs to move to Biblio.pm
107 ($count, $overdues) = &Getoverdues();
109 Returns the list of all overdue books.
111 C<$count> is the number of elements in C<@{$overdues}>.
113 C<$overdues> is a reference-to-array. Each element is a
114 reference-to-hash whose keys are the fields of the issues table in the
121 my $dbh = C4::Context->dbh;
122 my $sth = $dbh->prepare(
123 "Select * from issues where date_due < now() and returndate is
124 NULL order by borrowernumber "
128 # FIXME - Use push @results
131 while ( my $data = $sth->fetchrow_hashref ) {
132 $results[$i] = $data;
139 return ( $i, \@results );
144 ( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh );
152 # From Main.pm, modified to return a list of overdueitems, in addition to a count
153 #checks whether a borrower has overdue items
154 my ( $borrowernumber, $dbh ) = @_;
155 my @datearr = localtime;
157 ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
160 my $sth = $dbh->prepare(
161 "SELECT * FROM issues,biblio,biblioitems,items
162 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
163 AND items.biblionumber = biblio.biblionumber
164 AND issues.itemnumber = items.itemnumber
165 AND issues.borrowernumber = ?
166 AND issues.returndate is NULL
167 AND issues.date_due < ?"
169 $sth->execute( $borrowernumber, $today );
170 while ( my $data = $sth->fetchrow_hashref ) {
171 push( @overdueitems, $data );
175 return ( $count, \@overdueitems );
180 ($amount, $chargename, $message) =
181 &CalcFine($itemnumber, $borrowercode, $days_overdue);
183 Calculates the fine for a book.
185 The issuingrules table in the Koha database is a fine matrix, listing
186 the penalties for each type of patron for each type of item and each branch (e.g., the
187 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
188 members might get a longer grace period between the first and second
189 reminders that a book is overdue).
191 The fine is calculated as follows: if it is time for the first
192 reminder, the fine is the value listed for the given (branch, item type,
193 borrower code) combination. If it is time for the second reminder, the
194 fine is doubled. Finally, if it is time to send the account to a
195 collection agency, the fine is set to 5 local monetary units (a really
196 good deal for the patron if the library is in Italy). Otherwise, the
199 Note that the way this function is currently implemented, it only
200 returns a nonzero value on the notable days listed above. That is, if
201 the categoryitems entry says to send a first reminder 7 days after the
202 book is due, then if you call C<&CalcFine> 7 days after the book is
203 due, it will give a nonzero fine. If you call C<&CalcFine> the next
204 day, however, it will say that the fine is 0.
206 C<$itemnumber> is the book's item number.
208 C<$borrowercode> is the borrower code of the patron who currently has
211 C<$days_overdue> is the number of days elapsed since the book's due
214 C<&CalcFine> returns a list of three values:
216 C<$amount> is the fine owed by the patron (see above).
218 C<$chargename> is the chargename field from the applicable record in
219 the categoryitem table, whatever that is.
221 C<$message> is a text message, either "First Notice", "Second Notice",
228 my ( $itemnumber, $bortype, $difference , $dues ) = @_;
229 my $dbh = C4::Context->dbh;
230 my $data = GetIssuingRules($itemnumber,$bortype);
233 my $countspecialday=&GetSpecialHolidays($dues,$itemnumber);
234 my $countrepeatableday=&GetRepeatableHolidays($dues,$itemnumber,$difference);
235 my $countalldayclosed = $countspecialday + $countrepeatableday;
236 my $daycount = $difference - $countalldayclosed;
237 my $daycounttotal = $daycount - $data->{'firstremind'};
238 if ($data->{'firstremind'} < $daycount)
240 $amount = $daycounttotal*$data->{'fine'};
242 return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
246 =item GetSpecialHolidays
248 &GetSpecialHolidays($date_dues,$itemnumber);
250 return number of special days between date of the day and date due
252 C<$date_dues> is the envisaged date of book return.
254 C<$itemnumber> is the book's item number.
258 sub GetSpecialHolidays {
259 my ($date_dues,$itemnumber) = @_;
260 # calcul the today date
261 my $today = join "-", &Today();
263 # return the holdingbranch
264 my $iteminfo=GetIssuesIteminfo($itemnumber);
265 # use sql request to find all date between date_due and today
266 my $dbh = C4::Context->dbh;
267 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
268 FROM `special_holidays`
269 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
270 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
273 my @result=GetWdayFromItemnumber($itemnumber);
277 my $sth = $dbh->prepare($query);
278 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
280 while ( my $special_date=$sth->fetchrow_hashref){
281 push (@result_date,$special_date);
284 my $specialdaycount=scalar(@result_date);
286 for (my $i=0;$i<scalar(@result_date);$i++){
287 $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
288 (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
289 for (my $j=0;$j<scalar(@result);$j++){
290 if ($wday == ($result[$j]->{'weekday'})){
296 return $specialdaycount;
299 =item GetRepeatableHolidays
301 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
303 return number of day closed between date of the day and date due
305 C<$date_dues> is the envisaged date of book return.
307 C<$itemnumber> is item number.
309 C<$difference> numbers of between day date of the day and date due
313 sub GetRepeatableHolidays{
314 my ($date_dues,$itemnumber,$difference) = @_;
315 my $dateinsec=UnixDate($date_dues,"%o");
316 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
317 my @result=GetWdayFromItemnumber($itemnumber);
321 for (my $i=0;$i<scalar(@result);$i++){
324 for ( $j=0;$j<$difference;$j++){
325 if ($result[$i]->{'weekday'} == $k)
327 push ( @dayclosedcount ,$k);
333 return scalar(@dayclosedcount);
337 =item GetWayFromItemnumber
339 &Getwdayfromitemnumber($itemnumber);
341 return the different week day from repeatable_holidays table
343 C<$itemnumber> is item number.
347 sub GetWdayFromItemnumber{
349 my $iteminfo=GetIssuesIteminfo($itemnumber);
351 my $dbh = C4::Context->dbh;
352 my $query = qq|SELECT weekday
353 FROM repeatable_holidays
356 my $sth = $dbh->prepare($query);
359 $sth->execute($iteminfo->{'branchcode'});
360 while ( my $weekday=$sth->fetchrow_hashref){
361 push (@result,$weekday);
367 =item GetIssuesIteminfo
369 &GetIssuesIteminfo($itemnumber);
371 return all data from issues about item
373 C<$itemnumber> is item number.
377 sub GetIssuesIteminfo{
379 my $dbh = C4::Context->dbh;
380 my $query = qq|SELECT *
384 my $sth = $dbh->prepare($query);
385 $sth->execute($itemnumber);
386 my ($issuesinfo)=$sth->fetchrow_hashref;
393 &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
395 (Note: the following is mostly conjecture and guesswork.)
397 Updates the fine owed on an overdue book.
399 C<$itemnumber> is the book's item number.
401 C<$borrowernumber> is the borrower number of the patron who currently
402 has the book on loan.
404 C<$amount> is the current amount owed by the patron.
406 C<$type> will be used in the description of the fine.
408 C<$description> is a string that must be present in the description of
409 the fine. I think this is expected to be a date in DD/MM/YYYY format.
411 C<&UpdateFine> looks up the amount currently owed on the given item
412 and sets it to C<$amount>, creating, if necessary, a new entry in the
413 accountlines table of the Koha database.
418 # FIXME - This API doesn't look right: why should the caller have to
419 # specify both the item number and the borrower number? A book can't
420 # be on loan to two different people, so the item number should be
423 my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
424 my $dbh = C4::Context->dbh;
425 # FIXME - What exactly is this query supposed to do? It looks up an
426 # entry in accountlines that matches the given item and borrower
427 # numbers, where the description contains $due, and where the
428 # account type has one of several values, but what does this _mean_?
429 # Does it look up existing fines for this item?
430 # FIXME - What are these various account types? ("FU", "O", "F", "M")
431 my $sth = $dbh->prepare(
432 "Select * from accountlines where itemnumber=? and
433 borrowernumber=? and (accounttype='FU' or accounttype='O' or
434 accounttype='F' or accounttype='M') and description like ?"
436 $sth->execute( $itemnum, $borrowernumber, "%$due%" );
438 if ( my $data = $sth->fetchrow_hashref ) {
440 # I think this if-clause deals with the case where we're updating
442 # print "in accounts ...";
443 if ( $data->{'amount'} != $amount ) {
446 my $diff = $amount - $data->{'amount'};
447 my $out = $data->{'amountoutstanding'} + $diff;
448 my $sth2 = $dbh->prepare(
449 "update accountlines set date=now(), amount=?,
450 amountoutstanding=?,accounttype='FU' where
451 borrowernumber=? and itemnumber=?
452 and (accounttype='FU' or accounttype='O') and description like ?"
454 $sth2->execute( $amount, $out, $data->{'borrowernumber'},
455 $data->{'itemnumber'}, "%$due%" );
460 # print "no update needed $data->{'amount'}"
465 # I think this else-clause deals with the case where we're adding
467 my $sth4 = $dbh->prepare(
468 "select title from biblio,items where items.itemnumber=?
469 and biblio.biblionumber=items.biblionumber"
471 $sth4->execute($itemnum);
472 my $title = $sth4->fetchrow_hashref;
475 # # print "not in account";
476 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
479 # # FIXME - Make $accountno a scalar.
480 # my @accountno = $sth3->fetchrow_array;
484 my $nextaccntno = getnextacctno($borrowernumber);
485 my $sth2 = $dbh->prepare(
486 "Insert into accountlines
487 (borrowernumber,itemnumber,date,amount,
488 description,accounttype,amountoutstanding,accountno) values
489 (?,?,now(),?,?,'FU',?,?)"
491 $sth2->execute( $borrowernumber, $itemnum, $amount,
492 "$type $title->{'title'} $due",
493 $amount, $nextaccntno);
498 C4::Context->userenv->{'number'},
502 "due=".$due." amount=".$amount." itemnumber=".$itemnum
503 ) if C4::Context->preference("FinesLog");
510 $borrower = &BorType($borrowernumber);
512 Looks up a patron by borrower number.
514 C<$borrower> is a reference-to-hash whose keys are all of the fields
515 from the borrowers and categories tables of the Koha database. Thus,
516 C<$borrower> contains all information about both the borrower and
517 category he or she belongs to.
523 my ($borrowernumber) = @_;
524 my $dbh = C4::Context->dbh;
525 my $sth = $dbh->prepare(
526 "Select * from borrowers,categories where
528 borrowers.categorycode=categories.categorycode"
530 $sth->execute($borrowernumber);
531 my $data = $sth->fetchrow_hashref;
536 =item ReplacementCost
538 $cost = &ReplacementCost($itemnumber);
540 Returns the replacement cost of the item with the given item number.
545 sub ReplacementCost {
547 my $dbh = C4::Context->dbh;
549 $dbh->prepare("Select replacementprice from items where itemnumber=?");
550 $sth->execute($itemnum);
552 # FIXME - Use fetchrow_array or something.
553 my $data = $sth->fetchrow_hashref;
555 return ( $data->{'replacementprice'} );
560 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
562 return the total of fine
564 C<$itemnum> is item number
566 C<$borrowernumber> is the borrowernumber
572 my ( $itemnum, $borrowernumber ) = @_;
573 my $dbh = C4::Context->dbh();
574 my $query = "SELECT sum(amountoutstanding) FROM accountlines
575 where accounttype like 'F%'
576 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
577 my $sth = $dbh->prepare($query);
578 $sth->execute( $itemnum, $borrowernumber );
579 my $data = $sth->fetchrow_hashref();
582 return ( $data->{'sum(amountoutstanding)'} );
588 =item GetIssuingRules
590 $data = &GetIssuingRules($itemnumber,$categorycode);
592 Looks up for all issuingrules an item info
594 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
595 from the borrowers and categories tables of the Koha database. Thus,
597 C<$categorycode> contains information about borrowers category
599 C<$data> contains all information about both the borrower and
600 category he or she belongs to.
603 sub GetIssuingRules {
604 my ($itemnumber,$categorycode)=@_;
605 my $dbh = C4::Context->dbh();
606 my $query=qq|SELECT *
607 FROM items,biblioitems,itemtypes,issuingrules
608 WHERE items.itemnumber=?
609 AND items.biblioitemnumber=biblioitems.biblioitemnumber
610 AND biblioitems.itemtype=itemtypes.itemtype
611 AND issuingrules.itemtype=itemtypes.itemtype
612 AND issuingrules.categorycode=?
613 AND (items.itemlost <> 1
614 OR items.itemlost is NULL)|;
615 my $sth = $dbh->prepare($query);
617 $sth->execute($itemnumber,$categorycode);
618 my ($data) = $sth->fetchrow_hashref;
625 sub ReplacementCost2 {
626 my ( $itemnum, $borrowernumber ) = @_;
627 my $dbh = C4::Context->dbh();
628 my $query = "SELECT amountoutstanding
630 WHERE accounttype like 'L'
631 AND amountoutstanding > 0
633 AND borrowernumber= ?";
634 my $sth = $dbh->prepare($query);
635 $sth->execute( $itemnum, $borrowernumber );
636 my $data = $sth->fetchrow_hashref();
639 return ( $data->{'amountoutstanding'} );
643 =item GetNextIdNotify
645 ($result) = &GetNextIdNotify($reference);
647 Returns the new file number
649 C<$result> contains the next file number
651 C<$reference> contains the beggining of file number
657 sub GetNextIdNotify {
659 my $query=qq|SELECT max(notify_id)
661 WHERE notify_id like \"$reference%\"
663 # AND borrowernumber=?|;
664 my $dbh = C4::Context->dbh;
665 my $sth=$dbh->prepare($query);
667 my $result=$sth->fetchrow;
672 ($result=$reference."01") ;
675 $count=substr($result,6)+1;
678 ($count = "0".$count);
680 $result=$reference.$count;
688 (@notify) = &AmountNotify($borrowernumber);
690 Returns amount for all file per borrowers
691 C<@notify> array contains all file per borrowers
693 C<$notify_id> contains the file number for the borrower number nad item number
698 my ($borrowernumber)=@_;
699 my $dbh = C4::Context->dbh;
701 my $query=qq| SELECT distinct(notify_id)
703 WHERE borrowernumber=?|;
705 my $sth=$dbh->prepare($query);
706 $sth->execute($borrowernumber);
707 while ( my $numberofotify=$sth->fetchrow_array){
708 push (@notify,$numberofotify);
718 ($totalnotify) = &AmountNotify($notifyid);
720 Returns amount for all file per borrowers
721 C<$notifyid> is the file number
723 C<$totalnotify> contains amount of a file
725 C<$notify_id> contains the file number for the borrower number nad item number
731 my $dbh = C4::Context->dbh;
732 my $query=qq| SELECT sum(amountoutstanding)
735 my $sth=$dbh->prepare($query);
736 $sth->execute($notifyid);
737 my $totalnotify=$sth->fetchrow;
739 return ($totalnotify);
745 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
747 Returns the file number per borrower and itemnumber
749 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
750 from the items tables of the Koha database. Thus,
752 C<$itemnumber> contains the borrower categorycode
754 C<$notify_id> contains the file number for the borrower number nad item number
759 my ($borrowernumber,$itemnumber)=@_;
760 my $query=qq|SELECT notify_id
762 WHERE borrowernumber=?
764 AND (accounttype='FU' or accounttype='O')|;
765 my $dbh = C4::Context->dbh;
766 my $sth=$dbh->prepare($query);
767 $sth->execute($borrowernumber,$itemnumber);
768 my ($notify_id)=$sth->fetchrow;
774 =item CreateItemAccountLine
776 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
778 update the account lines with file number or with file level
780 C<$items> is a reference-to-hash whose keys are all of the fields
781 from the items tables of the Koha database. Thus,
783 C<$itemnumber> contains the item number
785 C<$borrowernumber> contains the borrower number
787 C<$date> contains the date of the day
789 C<$amount> contains item price
791 C<$description> contains the descritpion of accounttype
793 C<$accounttype> contains the account type
795 C<$amountoutstanding> contains the $amountoutstanding
797 C<$timestamp> contains the timestamp with time and the date of the day
799 C<$notify_id> contains the file number
801 C<$level> contains the file level
806 sub CreateItemAccountLine {
807 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
808 my $dbh = C4::Context->dbh;
809 my $nextaccntno = getnextacctno($borrowernumber);
810 my $query= "INSERT into accountlines
811 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
813 (?,?,?,?,?,?,?,?,?,?,?)";
816 my $sth=$dbh->prepare($query);
817 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
821 =item UpdateAccountLines
823 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
825 update the account lines with file number or with file level
827 C<$items> is a reference-to-hash whose keys are all of the fields
828 from the items tables of the Koha database. Thus,
830 C<$itemnumber> contains the item number
832 C<$notify_id> contains the file number
834 C<$notify_level> contains the file level
836 C<$borrowernumber> contains the borrowernumber
840 sub UpdateAccountLines {
841 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
843 if ($notify_id eq '')
846 $query=qq|UPDATE accountlines
848 WHERE borrowernumber=? AND itemnumber=?
849 AND (accounttype='FU' or accounttype='O')|;
852 $query=qq|UPDATE accountlines
853 SET notify_id=?, notify_level=?
854 WHERE borrowernumber=?
856 AND (accounttype='FU' or accounttype='O')|;
858 my $dbh = C4::Context->dbh;
859 my $sth=$dbh->prepare($query);
861 if ($notify_id eq '')
863 $sth->execute($notify_level,$borrowernumber,$itemnumber);
866 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
875 ($items) = &GetItems($itemnumber);
877 Returns the list of all delays from overduerules.
879 C<$items> is a reference-to-hash whose keys are all of the fields
880 from the items tables of the Koha database. Thus,
882 C<$itemnumber> contains the borrower categorycode
887 my($itemnumber) = @_;
888 my $query=qq|SELECT *
891 my $dbh = C4::Context->dbh;
892 my $sth=$dbh->prepare($query);
893 $sth->execute($itemnumber);
894 my ($items)=$sth->fetchrow_hashref;
899 =item GetOverdueDelays
901 (@delays) = &GetOverdueDelays($categorycode);
903 Returns the list of all delays from overduerules.
905 C<@delays> it's an array contains the three delays from overduerules table
907 C<$categorycode> contains the borrower categorycode
911 sub GetOverdueDelays {
913 my $dbh = C4::Context->dbh;
914 my $query=qq|SELECT delay1,delay2,delay3
916 WHERE categorycode=?|;
917 my $sth=$dbh->prepare($query);
918 $sth->execute($category);
919 my (@delays)=$sth->fetchrow_array;
924 =item CheckAccountLineLevelInfo
926 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
928 Check and Returns the list of all overdue books.
930 C<$exist> contains number of line in accounlines
931 with the same .biblionumber,itemnumber,accounttype,and notify_level
933 C<$borrowernumber> contains the borrower number
935 C<$itemnumber> contains item number
937 C<$accounttype> contains account type
939 C<$notify_level> contains the accountline level
944 sub CheckAccountLineLevelInfo {
945 my($borrowernumber,$itemnumber,$level) = @_;
946 my $dbh = C4::Context->dbh;
947 my $query= qq|SELECT count(*)
949 WHERE borrowernumber =?
952 my $sth=$dbh->prepare($query);
953 $sth->execute($borrowernumber,$itemnumber,$level);
954 my ($exist)=$sth->fetchrow;
959 =item GetOverduerules
961 ($overduerules) = &GetOverduerules($categorycode);
963 Returns the value of borrowers (debarred or not) with notify level
965 C<$overduerules> return value of debbraed field in overduerules table
967 C<$category> contains the borrower categorycode
969 C<$notify_level> contains the notify level
974 my($category,$notify_level) = @_;
975 my $dbh = C4::Context->dbh;
976 my $query=qq|SELECT debarred$notify_level
978 WHERE categorycode=?|;
979 my $sth=$dbh->prepare($query);
980 $sth->execute($category);
981 my ($overduerules)=$sth->fetchrow;
983 return($overduerules);
987 =item CheckBorrowerDebarred
989 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
991 Check if the borrowers is already debarred
993 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
995 C<$borrowernumber> contains the borrower number
1000 sub CheckBorrowerDebarred{
1001 my($borrowernumber) = @_;
1002 my $dbh = C4::Context->dbh;
1003 my $query=qq|SELECT debarred
1005 WHERE borrowernumber=?
1007 my $sth=$dbh->prepare($query);
1008 $sth->execute($borrowernumber);
1009 my ($debarredstatus)=$sth->fetchrow;
1011 if ($debarredstatus eq '1'){
1018 =item UpdateBorrowerDebarred
1020 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1022 update status of borrowers in borrowers table (field debarred)
1024 C<$borrowernumber> borrower number
1028 sub UpdateBorrowerDebarred{
1029 my($borrowernumber) = @_;
1030 my $dbh = C4::Context->dbh;
1031 my $query=qq|UPDATE borrowers
1033 WHERE borrowernumber=?
1035 my $sth=$dbh->prepare($query);
1036 $sth->execute($borrowernumber);
1041 =item CheckExistantNotifyid
1043 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1045 Check and Returns the notify id if exist else return 0.
1047 C<$exist> contains a notify_id
1049 C<$borrowernumber> contains the borrower number
1051 C<$date_due> contains the date of item return
1056 sub CheckExistantNotifyid {
1057 my($borrowernumber,$date_due) = @_;
1058 my $dbh = C4::Context->dbh;
1059 my $query = qq|SELECT notify_id FROM issues,accountlines
1060 WHERE accountlines.borrowernumber =?
1061 AND issues.itemnumber= accountlines.itemnumber
1063 my $sth=$dbh->prepare($query);
1064 $sth->execute($borrowernumber,$date_due);
1065 my ($exist)=$sth->fetchrow;
1076 =item CheckAccountLineItemInfo
1078 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1080 Check and Returns the list of all overdue items from the same file number(notify_id).
1082 C<$exist> contains number of line in accounlines
1083 with the same .biblionumber,itemnumber,accounttype,notify_id
1085 C<$borrowernumber> contains the borrower number
1087 C<$itemnumber> contains item number
1089 C<$accounttype> contains account type
1091 C<$notify_id> contains the file number
1095 sub CheckAccountLineItemInfo {
1096 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1097 my $dbh = C4::Context->dbh;
1098 my $query = qq|SELECT count(*) FROM accountlines
1099 WHERE borrowernumber =?
1103 my $sth=$dbh->prepare($query);
1104 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1105 my ($exist)=$sth->fetchrow;
1110 =head2 CheckItemNotify
1112 Sql request to check if the document has alreday been notified
1113 this function is not exported, only used with GetOverduesForBranch
1117 sub CheckItemNotify {
1118 my ($notify_id,$notify_level,$itemnumber) = @_;
1119 my $dbh = C4::Context->dbh;
1120 my $sth = $dbh->prepare("
1121 SELECT COUNT(*) FROM notifys
1123 AND notify_level = ?
1124 AND itemnumber = ? ");
1125 $sth->execute($notify_id,$notify_level,$itemnumber);
1126 my $notified = $sth->fetchrow;
1131 =head2 GetOverduesForBranch
1133 Sql request for display all information for branchoverdues.pl
1134 2 possibilities : with or without location .
1135 display is filtered by branch
1139 sub GetOverduesForBranch {
1140 my ( $branch, $location) = @_;
1141 if ( not $location ) {
1142 my $dbh = C4::Context->dbh;
1143 my $sth = $dbh->prepare("
1146 borrowers.firstname,
1148 itemtypes.description,
1151 branches.branchname,
1155 items.itemcallnumber,
1156 borrowers.borrowernumber,
1158 biblio.biblionumber,
1160 accountlines.notify_id,
1161 accountlines.notify_level,
1163 accountlines.amountoutstanding
1164 FROM issues,borrowers,biblio,biblioitems,itemtypes,items,branches,accountlines
1165 WHERE ( issues.returndate is null)
1166 AND ( accountlines.amountoutstanding != '0.000000')
1167 AND ( accountlines.accounttype = 'FU')
1168 AND ( issues.borrowernumber = accountlines.borrowernumber )
1169 AND ( issues.itemnumber = accountlines.itemnumber )
1170 AND ( borrowers.borrowernumber = issues.borrowernumber )
1171 AND ( biblio.biblionumber = biblioitems.biblionumber )
1172 AND ( biblioitems.biblionumber = items.biblionumber )
1173 AND ( itemtypes.itemtype = biblioitems.itemtype )
1174 AND ( items.itemnumber = issues.itemnumber )
1175 AND ( branches.branchcode = issues.branchcode )
1176 AND (issues.branchcode = ?)
1177 AND (issues.date_due <= NOW())
1178 ORDER BY borrowers.surname
1180 $sth->execute($branch);
1183 while ( my $data = $sth->fetchrow_hashref ) {
1184 #check if the document has already been notified
1185 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1186 if ($countnotify eq '0'){
1187 $getoverdues[$i] = $data;
1191 return (@getoverdues);
1195 my $dbh = C4::Context->dbh;
1196 my $sth = $dbh->prepare( "
1197 SELECT borrowers.surname,
1198 borrowers.firstname,
1200 itemtypes.description,
1203 branches.branchname,
1207 items.itemcallnumber,
1208 borrowers.borrowernumber,
1210 biblio.biblionumber,
1212 accountlines.notify_id,
1213 accountlines.notify_level,
1215 accountlines.amountoutstanding
1216 FROM issues,borrowers,biblio,biblioitems,itemtypes,items,branches,accountlines
1217 WHERE ( issues.returndate is null )
1218 AND ( accountlines.amountoutstanding != '0.000000')
1219 AND ( accountlines.accounttype = 'FU')
1220 AND ( issues.borrowernumber = accountlines.borrowernumber )
1221 AND ( issues.itemnumber = accountlines.itemnumber )
1222 AND ( borrowers.borrowernumber = issues.borrowernumber )
1223 AND ( biblio.biblionumber = biblioitems.biblionumber )
1224 AND ( biblioitems.biblionumber = items.biblionumber )
1225 AND ( itemtypes.itemtype = biblioitems.itemtype )
1226 AND ( items.itemnumber = issues.itemnumber )
1227 AND ( branches.branchcode = issues.branchcode )
1228 AND (issues.branchcode = ? AND items.location = ?)
1229 AND (issues.date_due <= NOW())
1230 ORDER BY borrowers.surname
1232 $sth->execute( $branch, $location);
1235 while ( my $data = $sth->fetchrow_hashref ) {
1236 #check if the document has already been notified
1237 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1238 if ($countnotify eq '0'){
1239 $getoverdues[$i] = $data;
1244 return (@getoverdues);
1249 =head2 AddNotifyLine
1251 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1253 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1258 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1259 if ( $method eq "phone" ) {
1260 my $dbh = C4::Context->dbh;
1261 my $sth = $dbh->prepare(
1262 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1263 VALUES (?,?,now(),now(),?,?,?)"
1265 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1270 my $dbh = C4::Context->dbh;
1271 my $sth = $dbh->prepare(
1272 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1273 VALUES (?,?,now(),?,?,?)"
1275 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1282 =head2 RemoveNotifyLine
1284 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1286 Cancel a notification
1290 sub RemoveNotifyLine {
1291 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1292 my $dbh = C4::Context->dbh;
1293 my $sth = $dbh->prepare(
1294 "DELETE FROM notifys
1300 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1312 Koha Developement team <info@koha.org>