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 "
129 while ( my $data = $sth->fetchrow_hashref ) {
130 push @results, $data;
139 ( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh );
147 # From Main.pm, modified to return a list of overdueitems, in addition to a count
148 #checks whether a borrower has overdue items
149 my ( $borrowernumber, $dbh ) = @_;
150 my @datearr = localtime;
152 ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
155 my $sth = $dbh->prepare(
156 "SELECT * FROM issues
157 LEFT JOIN items ON issues.itemnumber = items.itemnumber
158 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
159 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
160 WHERE issues.borrowernumber = ?
161 AND issues.returndate is NULL
162 AND issues.date_due < ?"
164 $sth->execute( $borrowernumber, $today );
165 while ( my $data = $sth->fetchrow_hashref ) {
166 push( @overdueitems, $data );
170 return ( $count, \@overdueitems );
175 ($amount, $chargename, $message) =
176 &CalcFine($itemnumber, $borrowercode, $days_overdue);
178 Calculates the fine for a book.
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).
186 The fine is calculated as follows: if it is time for the first
187 reminder, the fine is the value listed for the given (branch, item type,
188 borrower code) combination. If it is time for the second reminder, the
189 fine is doubled. Finally, if it is time to send the account to a
190 collection agency, the fine is set to 5 local monetary units (a really
191 good deal for the patron if the library is in Italy). Otherwise, the
194 Note that the way this function is currently implemented, it only
195 returns a nonzero value on the notable days listed above. That is, if
196 the categoryitems entry says to send a first reminder 7 days after the
197 book is due, then if you call C<&CalcFine> 7 days after the book is
198 due, it will give a nonzero fine. If you call C<&CalcFine> the next
199 day, however, it will say that the fine is 0.
201 C<$itemnumber> is the book's item number.
203 C<$borrowercode> is the borrower code of the patron who currently has
206 C<$days_overdue> is the number of days elapsed since the book's due
209 C<&CalcFine> returns a list of three values:
211 C<$amount> is the fine owed by the patron (see above).
213 C<$chargename> is the chargename field from the applicable record in
214 the categoryitem table, whatever that is.
216 C<$message> is a text message, either "First Notice", "Second Notice",
223 my ( $itemnumber, $bortype, $difference , $dues ) = @_;
224 my $dbh = C4::Context->dbh;
225 my $data = GetIssuingRules($itemnumber,$bortype);
228 my $countspecialday=&GetSpecialHolidays($dues,$itemnumber);
229 my $countrepeatableday=&GetRepeatableHolidays($dues,$itemnumber,$difference);
230 my $countalldayclosed = $countspecialday + $countrepeatableday;
231 my $daycount = $difference - $countalldayclosed;
232 my $daycounttotal = $daycount - $data->{'firstremind'};
233 if ($data->{'firstremind'} < $daycount)
235 $amount = $daycounttotal*$data->{'fine'};
237 return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
241 =item GetSpecialHolidays
243 &GetSpecialHolidays($date_dues,$itemnumber);
245 return number of special days between date of the day and date due
247 C<$date_dues> is the envisaged date of book return.
249 C<$itemnumber> is the book's item number.
253 sub GetSpecialHolidays {
254 my ($date_dues,$itemnumber) = @_;
255 # calcul the today date
256 my $today = join "-", &Today();
258 # return the holdingbranch
259 my $iteminfo=GetIssuesIteminfo($itemnumber);
260 # use sql request to find all date between date_due and today
261 my $dbh = C4::Context->dbh;
262 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
263 FROM `special_holidays`
264 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
265 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
268 my @result=GetWdayFromItemnumber($itemnumber);
272 my $sth = $dbh->prepare($query);
273 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
275 while ( my $special_date=$sth->fetchrow_hashref){
276 push (@result_date,$special_date);
279 my $specialdaycount=scalar(@result_date);
281 for (my $i=0;$i<scalar(@result_date);$i++){
282 $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
283 (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
284 for (my $j=0;$j<scalar(@result);$j++){
285 if ($wday == ($result[$j]->{'weekday'})){
291 return $specialdaycount;
294 =item GetRepeatableHolidays
296 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
298 return number of day closed between date of the day and date due
300 C<$date_dues> is the envisaged date of book return.
302 C<$itemnumber> is item number.
304 C<$difference> numbers of between day date of the day and date due
308 sub GetRepeatableHolidays{
309 my ($date_dues,$itemnumber,$difference) = @_;
310 my $dateinsec=UnixDate($date_dues,"%o");
311 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
312 my @result=GetWdayFromItemnumber($itemnumber);
316 for (my $i=0;$i<scalar(@result);$i++){
319 for ( $j=0;$j<$difference;$j++){
320 if ($result[$i]->{'weekday'} == $k)
322 push ( @dayclosedcount ,$k);
328 return scalar(@dayclosedcount);
332 =item GetWayFromItemnumber
334 &Getwdayfromitemnumber($itemnumber);
336 return the different week day from repeatable_holidays table
338 C<$itemnumber> is item number.
342 sub GetWdayFromItemnumber{
344 my $iteminfo=GetIssuesIteminfo($itemnumber);
346 my $dbh = C4::Context->dbh;
347 my $query = qq|SELECT weekday
348 FROM repeatable_holidays
351 my $sth = $dbh->prepare($query);
354 $sth->execute($iteminfo->{'branchcode'});
355 while ( my $weekday=$sth->fetchrow_hashref){
356 push (@result,$weekday);
362 =item GetIssuesIteminfo
364 &GetIssuesIteminfo($itemnumber);
366 return all data from issues about item
368 C<$itemnumber> is item number.
372 sub GetIssuesIteminfo{
374 my $dbh = C4::Context->dbh;
375 my $query = qq|SELECT *
379 my $sth = $dbh->prepare($query);
380 $sth->execute($itemnumber);
381 my ($issuesinfo)=$sth->fetchrow_hashref;
388 &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
390 (Note: the following is mostly conjecture and guesswork.)
392 Updates the fine owed on an overdue book.
394 C<$itemnumber> is the book's item number.
396 C<$borrowernumber> is the borrower number of the patron who currently
397 has the book on loan.
399 C<$amount> is the current amount owed by the patron.
401 C<$type> will be used in the description of the fine.
403 C<$description> is a string that must be present in the description of
404 the fine. I think this is expected to be a date in DD/MM/YYYY format.
406 C<&UpdateFine> looks up the amount currently owed on the given item
407 and sets it to C<$amount>, creating, if necessary, a new entry in the
408 accountlines table of the Koha database.
413 # FIXME - This API doesn't look right: why should the caller have to
414 # specify both the item number and the borrower number? A book can't
415 # be on loan to two different people, so the item number should be
418 my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
419 my $dbh = C4::Context->dbh;
420 # FIXME - What exactly is this query supposed to do? It looks up an
421 # entry in accountlines that matches the given item and borrower
422 # numbers, where the description contains $due, and where the
423 # account type has one of several values, but what does this _mean_?
424 # Does it look up existing fines for this item?
425 # FIXME - What are these various account types? ("FU", "O", "F", "M")
426 my $sth = $dbh->prepare(
427 "Select * from accountlines where itemnumber=? and
428 borrowernumber=? and (accounttype='FU' or accounttype='O' or
429 accounttype='F' or accounttype='M') and description like ?"
431 $sth->execute( $itemnum, $borrowernumber, "%$due%" );
433 if ( my $data = $sth->fetchrow_hashref ) {
435 # I think this if-clause deals with the case where we're updating
437 # print "in accounts ...";
438 if ( $data->{'amount'} != $amount ) {
441 my $diff = $amount - $data->{'amount'};
442 my $out = $data->{'amountoutstanding'} + $diff;
443 my $sth2 = $dbh->prepare(
444 "update accountlines set date=now(), amount=?,
445 amountoutstanding=?,accounttype='FU' where
446 borrowernumber=? and itemnumber=?
447 and (accounttype='FU' or accounttype='O') and description like ?"
449 $sth2->execute( $amount, $out, $data->{'borrowernumber'},
450 $data->{'itemnumber'}, "%$due%" );
455 # print "no update needed $data->{'amount'}"
460 # I think this else-clause deals with the case where we're adding
462 my $sth4 = $dbh->prepare(
463 "select title from biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber where items.itemnumber=?"
465 $sth4->execute($itemnum);
466 my $title = $sth4->fetchrow_hashref;
469 # # print "not in account";
470 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
473 # # FIXME - Make $accountno a scalar.
474 # my @accountno = $sth3->fetchrow_array;
478 my $nextaccntno = getnextacctno($borrowernumber);
479 my $sth2 = $dbh->prepare(
480 "Insert into accountlines
481 (borrowernumber,itemnumber,date,amount,
482 description,accounttype,amountoutstanding,accountno) values
483 (?,?,now(),?,?,'FU',?,?)"
485 $sth2->execute( $borrowernumber, $itemnum, $amount,
486 "$type $title->{'title'} $due",
487 $amount, $nextaccntno);
492 C4::Context->userenv->{'number'},
496 "due=".$due." amount=".$amount." itemnumber=".$itemnum
497 ) if C4::Context->preference("FinesLog");
504 $borrower = &BorType($borrowernumber);
506 Looks up a patron by borrower number.
508 C<$borrower> is a reference-to-hash whose keys are all of the fields
509 from the borrowers and categories tables of the Koha database. Thus,
510 C<$borrower> contains all information about both the borrower and
511 category he or she belongs to.
517 my ($borrowernumber) = @_;
518 my $dbh = C4::Context->dbh;
519 my $sth = $dbh->prepare(
520 "SELECT * from borrowers
521 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
522 WHERE borrowernumber=?"
524 $sth->execute($borrowernumber);
525 my $data = $sth->fetchrow_hashref;
530 =item ReplacementCost
532 $cost = &ReplacementCost($itemnumber);
534 Returns the replacement cost of the item with the given item number.
539 sub ReplacementCost {
541 my $dbh = C4::Context->dbh;
543 $dbh->prepare("Select replacementprice from items where itemnumber=?");
544 $sth->execute($itemnum);
546 # FIXME - Use fetchrow_array or something.
547 my $data = $sth->fetchrow_hashref;
549 return ( $data->{'replacementprice'} );
554 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
556 return the total of fine
558 C<$itemnum> is item number
560 C<$borrowernumber> is the borrowernumber
566 my ( $itemnum, $borrowernumber ) = @_;
567 my $dbh = C4::Context->dbh();
568 my $query = "SELECT sum(amountoutstanding) FROM accountlines
569 where accounttype like 'F%'
570 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
571 my $sth = $dbh->prepare($query);
572 $sth->execute( $itemnum, $borrowernumber );
573 my $data = $sth->fetchrow_hashref();
576 return ( $data->{'sum(amountoutstanding)'} );
582 =item GetIssuingRules
584 $data = &GetIssuingRules($itemnumber,$categorycode);
586 Looks up for all issuingrules an item info
588 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
589 from the borrowers and categories tables of the Koha database. Thus,
591 C<$categorycode> contains information about borrowers category
593 C<$data> contains all information about both the borrower and
594 category he or she belongs to.
597 sub GetIssuingRules {
598 my ($itemnumber,$categorycode)=@_;
599 my $dbh = C4::Context->dbh();
600 my $query=qq|SELECT *
601 FROM items,biblioitems,itemtypes,issuingrules
602 WHERE items.itemnumber=?
603 AND items.biblioitemnumber=biblioitems.biblioitemnumber
604 AND biblioitems.itemtype=itemtypes.itemtype
605 AND issuingrules.itemtype=itemtypes.itemtype
606 AND issuingrules.categorycode=?
607 AND (items.itemlost <> 1
608 OR items.itemlost is NULL)|;
609 my $sth = $dbh->prepare($query);
611 $sth->execute($itemnumber,$categorycode);
612 my ($data) = $sth->fetchrow_hashref;
619 sub ReplacementCost2 {
620 my ( $itemnum, $borrowernumber ) = @_;
621 my $dbh = C4::Context->dbh();
622 my $query = "SELECT amountoutstanding
624 WHERE accounttype like 'L'
625 AND amountoutstanding > 0
627 AND borrowernumber= ?";
628 my $sth = $dbh->prepare($query);
629 $sth->execute( $itemnum, $borrowernumber );
630 my $data = $sth->fetchrow_hashref();
633 return ( $data->{'amountoutstanding'} );
637 =item GetNextIdNotify
639 ($result) = &GetNextIdNotify($reference);
641 Returns the new file number
643 C<$result> contains the next file number
645 C<$reference> contains the beggining of file number
651 sub GetNextIdNotify {
653 my $query=qq|SELECT max(notify_id)
655 WHERE notify_id like \"$reference%\"
657 # AND borrowernumber=?|;
658 my $dbh = C4::Context->dbh;
659 my $sth=$dbh->prepare($query);
661 my $result=$sth->fetchrow;
666 ($result=$reference."01") ;
669 $count=substr($result,6)+1;
672 ($count = "0".$count);
674 $result=$reference.$count;
682 (@notify) = &AmountNotify($borrowernumber);
684 Returns amount for all file per borrowers
685 C<@notify> array contains all file per borrowers
687 C<$notify_id> contains the file number for the borrower number nad item number
692 my ($borrowernumber)=@_;
693 my $dbh = C4::Context->dbh;
694 my $query=qq| SELECT distinct(notify_id)
696 WHERE borrowernumber=?|;
698 my $sth=$dbh->prepare($query);
699 $sth->execute($borrowernumber);
700 while ( my $numberofotify=$sth->fetchrow_array){
701 push (@notify,$numberofotify);
711 ($totalnotify) = &AmountNotify($notifyid);
713 Returns amount for all file per borrowers
714 C<$notifyid> is the file number
716 C<$totalnotify> contains amount of a file
718 C<$notify_id> contains the file number for the borrower number nad item number
724 my $dbh = C4::Context->dbh;
725 my $query=qq| SELECT sum(amountoutstanding)
728 my $sth=$dbh->prepare($query);
729 $sth->execute($notifyid);
730 my $totalnotify=$sth->fetchrow;
732 return ($totalnotify);
738 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
740 Returns the file number per borrower and itemnumber
742 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
743 from the items tables of the Koha database. Thus,
745 C<$itemnumber> contains the borrower categorycode
747 C<$notify_id> contains the file number for the borrower number nad item number
752 my ($borrowernumber,$itemnumber)=@_;
753 my $query=qq|SELECT notify_id
755 WHERE borrowernumber=?
757 AND (accounttype='FU' or accounttype='O')|;
758 my $dbh = C4::Context->dbh;
759 my $sth=$dbh->prepare($query);
760 $sth->execute($borrowernumber,$itemnumber);
761 my ($notify_id)=$sth->fetchrow;
767 =item CreateItemAccountLine
769 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
771 update the account lines with file number or with file level
773 C<$items> is a reference-to-hash whose keys are all of the fields
774 from the items tables of the Koha database. Thus,
776 C<$itemnumber> contains the item number
778 C<$borrowernumber> contains the borrower number
780 C<$date> contains the date of the day
782 C<$amount> contains item price
784 C<$description> contains the descritpion of accounttype
786 C<$accounttype> contains the account type
788 C<$amountoutstanding> contains the $amountoutstanding
790 C<$timestamp> contains the timestamp with time and the date of the day
792 C<$notify_id> contains the file number
794 C<$level> contains the file level
799 sub CreateItemAccountLine {
800 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
801 my $dbh = C4::Context->dbh;
802 my $nextaccntno = getnextacctno($borrowernumber);
803 my $query= "INSERT into accountlines
804 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
806 (?,?,?,?,?,?,?,?,?,?,?)";
809 my $sth=$dbh->prepare($query);
810 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
814 =item UpdateAccountLines
816 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
818 update the account lines with file number or with file level
820 C<$items> is a reference-to-hash whose keys are all of the fields
821 from the items tables of the Koha database. Thus,
823 C<$itemnumber> contains the item number
825 C<$notify_id> contains the file number
827 C<$notify_level> contains the file level
829 C<$borrowernumber> contains the borrowernumber
833 sub UpdateAccountLines {
834 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
836 if ($notify_id eq '')
839 $query=qq|UPDATE accountlines
841 WHERE borrowernumber=? AND itemnumber=?
842 AND (accounttype='FU' or accounttype='O')|;
845 $query=qq|UPDATE accountlines
846 SET notify_id=?, notify_level=?
847 WHERE borrowernumber=?
849 AND (accounttype='FU' or accounttype='O')|;
851 my $dbh = C4::Context->dbh;
852 my $sth=$dbh->prepare($query);
854 if ($notify_id eq '')
856 $sth->execute($notify_level,$borrowernumber,$itemnumber);
859 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
868 ($items) = &GetItems($itemnumber);
870 Returns the list of all delays from overduerules.
872 C<$items> is a reference-to-hash whose keys are all of the fields
873 from the items tables of the Koha database. Thus,
875 C<$itemnumber> contains the borrower categorycode
880 my($itemnumber) = @_;
881 my $query=qq|SELECT *
884 my $dbh = C4::Context->dbh;
885 my $sth=$dbh->prepare($query);
886 $sth->execute($itemnumber);
887 my ($items)=$sth->fetchrow_hashref;
892 =item GetOverdueDelays
894 (@delays) = &GetOverdueDelays($categorycode);
896 Returns the list of all delays from overduerules.
898 C<@delays> it's an array contains the three delays from overduerules table
900 C<$categorycode> contains the borrower categorycode
904 sub GetOverdueDelays {
906 my $dbh = C4::Context->dbh;
907 my $query=qq|SELECT delay1,delay2,delay3
909 WHERE categorycode=?|;
910 my $sth=$dbh->prepare($query);
911 $sth->execute($category);
912 my (@delays)=$sth->fetchrow_array;
917 =item CheckAccountLineLevelInfo
919 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
921 Check and Returns the list of all overdue books.
923 C<$exist> contains number of line in accounlines
924 with the same .biblionumber,itemnumber,accounttype,and notify_level
926 C<$borrowernumber> contains the borrower number
928 C<$itemnumber> contains item number
930 C<$accounttype> contains account type
932 C<$notify_level> contains the accountline level
937 sub CheckAccountLineLevelInfo {
938 my($borrowernumber,$itemnumber,$level) = @_;
939 my $dbh = C4::Context->dbh;
940 my $query= qq|SELECT count(*)
942 WHERE borrowernumber =?
945 my $sth=$dbh->prepare($query);
946 $sth->execute($borrowernumber,$itemnumber,$level);
947 my ($exist)=$sth->fetchrow;
952 =item GetOverduerules
954 ($overduerules) = &GetOverduerules($categorycode);
956 Returns the value of borrowers (debarred or not) with notify level
958 C<$overduerules> return value of debbraed field in overduerules table
960 C<$category> contains the borrower categorycode
962 C<$notify_level> contains the notify level
967 my($category,$notify_level) = @_;
968 my $dbh = C4::Context->dbh;
969 my $query=qq|SELECT debarred$notify_level
971 WHERE categorycode=?|;
972 my $sth=$dbh->prepare($query);
973 $sth->execute($category);
974 my ($overduerules)=$sth->fetchrow;
976 return($overduerules);
980 =item CheckBorrowerDebarred
982 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
984 Check if the borrowers is already debarred
986 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
988 C<$borrowernumber> contains the borrower number
993 sub CheckBorrowerDebarred{
994 my($borrowernumber) = @_;
995 my $dbh = C4::Context->dbh;
996 my $query=qq|SELECT debarred
998 WHERE borrowernumber=?
1000 my $sth=$dbh->prepare($query);
1001 $sth->execute($borrowernumber);
1002 my ($debarredstatus)=$sth->fetchrow;
1004 if ($debarredstatus eq '1'){
1011 =item UpdateBorrowerDebarred
1013 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1015 update status of borrowers in borrowers table (field debarred)
1017 C<$borrowernumber> borrower number
1021 sub UpdateBorrowerDebarred{
1022 my($borrowernumber) = @_;
1023 my $dbh = C4::Context->dbh;
1024 my $query=qq|UPDATE borrowers
1026 WHERE borrowernumber=?
1028 my $sth=$dbh->prepare($query);
1029 $sth->execute($borrowernumber);
1034 =item CheckExistantNotifyid
1036 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1038 Check and Returns the notify id if exist else return 0.
1040 C<$exist> contains a notify_id
1042 C<$borrowernumber> contains the borrower number
1044 C<$date_due> contains the date of item return
1049 sub CheckExistantNotifyid {
1050 my($borrowernumber,$date_due) = @_;
1051 my $dbh = C4::Context->dbh;
1052 my $query = qq|SELECT notify_id FROM accountlines
1053 LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1054 WHERE accountlines.borrowernumber =?
1056 my $sth=$dbh->prepare($query);
1057 $sth->execute($borrowernumber,$date_due);
1058 my ($exist)=$sth->fetchrow;
1069 =item CheckAccountLineItemInfo
1071 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1073 Check and Returns the list of all overdue items from the same file number(notify_id).
1075 C<$exist> contains number of line in accounlines
1076 with the same .biblionumber,itemnumber,accounttype,notify_id
1078 C<$borrowernumber> contains the borrower number
1080 C<$itemnumber> contains item number
1082 C<$accounttype> contains account type
1084 C<$notify_id> contains the file number
1088 sub CheckAccountLineItemInfo {
1089 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1090 my $dbh = C4::Context->dbh;
1091 my $query = qq|SELECT count(*) FROM accountlines
1092 WHERE borrowernumber =?
1096 my $sth=$dbh->prepare($query);
1097 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1098 my ($exist)=$sth->fetchrow;
1103 =head2 CheckItemNotify
1105 Sql request to check if the document has alreday been notified
1106 this function is not exported, only used with GetOverduesForBranch
1110 sub CheckItemNotify {
1111 my ($notify_id,$notify_level,$itemnumber) = @_;
1112 my $dbh = C4::Context->dbh;
1113 my $sth = $dbh->prepare("
1114 SELECT COUNT(*) FROM notifys
1116 AND notify_level = ?
1117 AND itemnumber = ? ");
1118 $sth->execute($notify_id,$notify_level,$itemnumber);
1119 my $notified = $sth->fetchrow;
1124 =head2 GetOverduesForBranch
1126 Sql request for display all information for branchoverdues.pl
1127 2 possibilities : with or without location .
1128 display is filtered by branch
1132 sub GetOverduesForBranch {
1133 my ( $branch, $location) = @_;
1134 if ( not $location ) {
1135 my $dbh = C4::Context->dbh;
1136 my $sth = $dbh->prepare("
1139 borrowers.firstname,
1141 itemtypes.description,
1144 branches.branchname,
1148 items.itemcallnumber,
1149 borrowers.borrowernumber,
1151 biblio.biblionumber,
1153 accountlines.notify_id,
1154 accountlines.notify_level,
1156 accountlines.amountoutstanding
1158 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1159 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1160 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1161 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1162 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1163 LEFT JOIN itemtypes ON itemtypes.itemtype = biblioitems.itemtype
1164 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1165 WHERE ( issues.returndate is null)
1166 AND ( accountlines.amountoutstanding != '0.000000')
1167 AND ( accountlines.accounttype = 'FU')
1168 AND (issues.branchcode = ?)
1169 AND (issues.date_due <= NOW())
1170 ORDER BY borrowers.surname
1172 $sth->execute($branch);
1175 while ( my $data = $sth->fetchrow_hashref ) {
1176 #check if the document has already been notified
1177 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1178 if ($countnotify eq '0'){
1179 $getoverdues[$i] = $data;
1183 return (@getoverdues);
1187 my $dbh = C4::Context->dbh;
1188 my $sth = $dbh->prepare( "
1189 SELECT borrowers.surname,
1190 borrowers.firstname,
1192 itemtypes.description,
1195 branches.branchname,
1199 items.itemcallnumber,
1200 borrowers.borrowernumber,
1202 biblio.biblionumber,
1204 accountlines.notify_id,
1205 accountlines.notify_level,
1207 accountlines.amountoutstanding
1209 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1210 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1211 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1212 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1213 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1214 LEFT JOIN itemtypes ON itemtypes.itemtype = biblioitems.itemtype
1215 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1216 WHERE ( issues.returndate is null )
1217 AND ( accountlines.amountoutstanding != '0.000000')
1218 AND ( accountlines.accounttype = 'FU')
1219 AND (issues.branchcode = ? AND items.location = ?)
1220 AND (issues.date_due <= NOW())
1221 ORDER BY borrowers.surname
1223 $sth->execute( $branch, $location);
1226 while ( my $data = $sth->fetchrow_hashref ) {
1227 #check if the document has already been notified
1228 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1229 if ($countnotify eq '0'){
1230 $getoverdues[$i] = $data;
1235 return (@getoverdues);
1240 =head2 AddNotifyLine
1242 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1244 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1249 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1250 if ( $method eq "phone" ) {
1251 my $dbh = C4::Context->dbh;
1252 my $sth = $dbh->prepare(
1253 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1254 VALUES (?,?,now(),now(),?,?,?)"
1256 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1261 my $dbh = C4::Context->dbh;
1262 my $sth = $dbh->prepare(
1263 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1264 VALUES (?,?,now(),?,?,?)"
1266 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1273 =head2 RemoveNotifyLine
1275 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1277 Cancel a notification
1281 sub RemoveNotifyLine {
1282 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1283 my $dbh = C4::Context->dbh;
1284 my $sth = $dbh->prepare(
1285 "DELETE FROM notifys
1291 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1303 Koha Developement team <info@koha.org>