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,biblio,biblioitems,items
157 WHERE items.biblioitemnumber = biblioitems.biblioitemnumber
158 AND items.biblionumber = biblio.biblionumber
159 AND issues.itemnumber = items.itemnumber
160 AND 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,items where items.itemnumber=?
464 and biblio.biblionumber=items.biblionumber"
466 $sth4->execute($itemnum);
467 my $title = $sth4->fetchrow_hashref;
470 # # print "not in account";
471 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
474 # # FIXME - Make $accountno a scalar.
475 # my @accountno = $sth3->fetchrow_array;
479 my $nextaccntno = getnextacctno($borrowernumber);
480 my $sth2 = $dbh->prepare(
481 "Insert into accountlines
482 (borrowernumber,itemnumber,date,amount,
483 description,accounttype,amountoutstanding,accountno) values
484 (?,?,now(),?,?,'FU',?,?)"
486 $sth2->execute( $borrowernumber, $itemnum, $amount,
487 "$type $title->{'title'} $due",
488 $amount, $nextaccntno);
493 C4::Context->userenv->{'number'},
497 "due=".$due." amount=".$amount." itemnumber=".$itemnum
498 ) if C4::Context->preference("FinesLog");
505 $borrower = &BorType($borrowernumber);
507 Looks up a patron by borrower number.
509 C<$borrower> is a reference-to-hash whose keys are all of the fields
510 from the borrowers and categories tables of the Koha database. Thus,
511 C<$borrower> contains all information about both the borrower and
512 category he or she belongs to.
518 my ($borrowernumber) = @_;
519 my $dbh = C4::Context->dbh;
520 my $sth = $dbh->prepare(
521 "Select * from borrowers,categories where
523 borrowers.categorycode=categories.categorycode"
525 $sth->execute($borrowernumber);
526 my $data = $sth->fetchrow_hashref;
531 =item ReplacementCost
533 $cost = &ReplacementCost($itemnumber);
535 Returns the replacement cost of the item with the given item number.
540 sub ReplacementCost {
542 my $dbh = C4::Context->dbh;
544 $dbh->prepare("Select replacementprice from items where itemnumber=?");
545 $sth->execute($itemnum);
547 # FIXME - Use fetchrow_array or something.
548 my $data = $sth->fetchrow_hashref;
550 return ( $data->{'replacementprice'} );
555 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
557 return the total of fine
559 C<$itemnum> is item number
561 C<$borrowernumber> is the borrowernumber
567 my ( $itemnum, $borrowernumber ) = @_;
568 my $dbh = C4::Context->dbh();
569 my $query = "SELECT sum(amountoutstanding) FROM accountlines
570 where accounttype like 'F%'
571 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
572 my $sth = $dbh->prepare($query);
573 $sth->execute( $itemnum, $borrowernumber );
574 my $data = $sth->fetchrow_hashref();
577 return ( $data->{'sum(amountoutstanding)'} );
583 =item GetIssuingRules
585 $data = &GetIssuingRules($itemnumber,$categorycode);
587 Looks up for all issuingrules an item info
589 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
590 from the borrowers and categories tables of the Koha database. Thus,
592 C<$categorycode> contains information about borrowers category
594 C<$data> contains all information about both the borrower and
595 category he or she belongs to.
598 sub GetIssuingRules {
599 my ($itemnumber,$categorycode)=@_;
600 my $dbh = C4::Context->dbh();
601 my $query=qq|SELECT *
602 FROM items,biblioitems,itemtypes,issuingrules
603 WHERE items.itemnumber=?
604 AND items.biblioitemnumber=biblioitems.biblioitemnumber
605 AND biblioitems.itemtype=itemtypes.itemtype
606 AND issuingrules.itemtype=itemtypes.itemtype
607 AND issuingrules.categorycode=?
608 AND (items.itemlost <> 1
609 OR items.itemlost is NULL)|;
610 my $sth = $dbh->prepare($query);
612 $sth->execute($itemnumber,$categorycode);
613 my ($data) = $sth->fetchrow_hashref;
620 sub ReplacementCost2 {
621 my ( $itemnum, $borrowernumber ) = @_;
622 my $dbh = C4::Context->dbh();
623 my $query = "SELECT amountoutstanding
625 WHERE accounttype like 'L'
626 AND amountoutstanding > 0
628 AND borrowernumber= ?";
629 my $sth = $dbh->prepare($query);
630 $sth->execute( $itemnum, $borrowernumber );
631 my $data = $sth->fetchrow_hashref();
634 return ( $data->{'amountoutstanding'} );
638 =item GetNextIdNotify
640 ($result) = &GetNextIdNotify($reference);
642 Returns the new file number
644 C<$result> contains the next file number
646 C<$reference> contains the beggining of file number
652 sub GetNextIdNotify {
654 my $query=qq|SELECT max(notify_id)
656 WHERE notify_id like \"$reference%\"
658 # AND borrowernumber=?|;
659 my $dbh = C4::Context->dbh;
660 my $sth=$dbh->prepare($query);
662 my $result=$sth->fetchrow;
667 ($result=$reference."01") ;
670 $count=substr($result,6)+1;
673 ($count = "0".$count);
675 $result=$reference.$count;
683 (@notify) = &AmountNotify($borrowernumber);
685 Returns amount for all file per borrowers
686 C<@notify> array contains all file per borrowers
688 C<$notify_id> contains the file number for the borrower number nad item number
693 my ($borrowernumber)=@_;
694 my $dbh = C4::Context->dbh;
695 my $query=qq| SELECT distinct(notify_id)
697 WHERE borrowernumber=?|;
699 my $sth=$dbh->prepare($query);
700 $sth->execute($borrowernumber);
701 while ( my $numberofotify=$sth->fetchrow_array){
702 push (@notify,$numberofotify);
712 ($totalnotify) = &AmountNotify($notifyid);
714 Returns amount for all file per borrowers
715 C<$notifyid> is the file number
717 C<$totalnotify> contains amount of a file
719 C<$notify_id> contains the file number for the borrower number nad item number
725 my $dbh = C4::Context->dbh;
726 my $query=qq| SELECT sum(amountoutstanding)
729 my $sth=$dbh->prepare($query);
730 $sth->execute($notifyid);
731 my $totalnotify=$sth->fetchrow;
733 return ($totalnotify);
739 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
741 Returns the file number per borrower and itemnumber
743 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
744 from the items tables of the Koha database. Thus,
746 C<$itemnumber> contains the borrower categorycode
748 C<$notify_id> contains the file number for the borrower number nad item number
753 my ($borrowernumber,$itemnumber)=@_;
754 my $query=qq|SELECT notify_id
756 WHERE borrowernumber=?
758 AND (accounttype='FU' or accounttype='O')|;
759 my $dbh = C4::Context->dbh;
760 my $sth=$dbh->prepare($query);
761 $sth->execute($borrowernumber,$itemnumber);
762 my ($notify_id)=$sth->fetchrow;
768 =item CreateItemAccountLine
770 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
772 update the account lines with file number or with file level
774 C<$items> is a reference-to-hash whose keys are all of the fields
775 from the items tables of the Koha database. Thus,
777 C<$itemnumber> contains the item number
779 C<$borrowernumber> contains the borrower number
781 C<$date> contains the date of the day
783 C<$amount> contains item price
785 C<$description> contains the descritpion of accounttype
787 C<$accounttype> contains the account type
789 C<$amountoutstanding> contains the $amountoutstanding
791 C<$timestamp> contains the timestamp with time and the date of the day
793 C<$notify_id> contains the file number
795 C<$level> contains the file level
800 sub CreateItemAccountLine {
801 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
802 my $dbh = C4::Context->dbh;
803 my $nextaccntno = getnextacctno($borrowernumber);
804 my $query= "INSERT into accountlines
805 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
807 (?,?,?,?,?,?,?,?,?,?,?)";
810 my $sth=$dbh->prepare($query);
811 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
815 =item UpdateAccountLines
817 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
819 update the account lines with file number or with file level
821 C<$items> is a reference-to-hash whose keys are all of the fields
822 from the items tables of the Koha database. Thus,
824 C<$itemnumber> contains the item number
826 C<$notify_id> contains the file number
828 C<$notify_level> contains the file level
830 C<$borrowernumber> contains the borrowernumber
834 sub UpdateAccountLines {
835 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
837 if ($notify_id eq '')
840 $query=qq|UPDATE accountlines
842 WHERE borrowernumber=? AND itemnumber=?
843 AND (accounttype='FU' or accounttype='O')|;
846 $query=qq|UPDATE accountlines
847 SET notify_id=?, notify_level=?
848 WHERE borrowernumber=?
850 AND (accounttype='FU' or accounttype='O')|;
852 my $dbh = C4::Context->dbh;
853 my $sth=$dbh->prepare($query);
855 if ($notify_id eq '')
857 $sth->execute($notify_level,$borrowernumber,$itemnumber);
860 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
869 ($items) = &GetItems($itemnumber);
871 Returns the list of all delays from overduerules.
873 C<$items> is a reference-to-hash whose keys are all of the fields
874 from the items tables of the Koha database. Thus,
876 C<$itemnumber> contains the borrower categorycode
881 my($itemnumber) = @_;
882 my $query=qq|SELECT *
885 my $dbh = C4::Context->dbh;
886 my $sth=$dbh->prepare($query);
887 $sth->execute($itemnumber);
888 my ($items)=$sth->fetchrow_hashref;
893 =item GetOverdueDelays
895 (@delays) = &GetOverdueDelays($categorycode);
897 Returns the list of all delays from overduerules.
899 C<@delays> it's an array contains the three delays from overduerules table
901 C<$categorycode> contains the borrower categorycode
905 sub GetOverdueDelays {
907 my $dbh = C4::Context->dbh;
908 my $query=qq|SELECT delay1,delay2,delay3
910 WHERE categorycode=?|;
911 my $sth=$dbh->prepare($query);
912 $sth->execute($category);
913 my (@delays)=$sth->fetchrow_array;
918 =item CheckAccountLineLevelInfo
920 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
922 Check and Returns the list of all overdue books.
924 C<$exist> contains number of line in accounlines
925 with the same .biblionumber,itemnumber,accounttype,and notify_level
927 C<$borrowernumber> contains the borrower number
929 C<$itemnumber> contains item number
931 C<$accounttype> contains account type
933 C<$notify_level> contains the accountline level
938 sub CheckAccountLineLevelInfo {
939 my($borrowernumber,$itemnumber,$level) = @_;
940 my $dbh = C4::Context->dbh;
941 my $query= qq|SELECT count(*)
943 WHERE borrowernumber =?
946 my $sth=$dbh->prepare($query);
947 $sth->execute($borrowernumber,$itemnumber,$level);
948 my ($exist)=$sth->fetchrow;
953 =item GetOverduerules
955 ($overduerules) = &GetOverduerules($categorycode);
957 Returns the value of borrowers (debarred or not) with notify level
959 C<$overduerules> return value of debbraed field in overduerules table
961 C<$category> contains the borrower categorycode
963 C<$notify_level> contains the notify level
968 my($category,$notify_level) = @_;
969 my $dbh = C4::Context->dbh;
970 my $query=qq|SELECT debarred$notify_level
972 WHERE categorycode=?|;
973 my $sth=$dbh->prepare($query);
974 $sth->execute($category);
975 my ($overduerules)=$sth->fetchrow;
977 return($overduerules);
981 =item CheckBorrowerDebarred
983 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
985 Check if the borrowers is already debarred
987 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
989 C<$borrowernumber> contains the borrower number
994 sub CheckBorrowerDebarred{
995 my($borrowernumber) = @_;
996 my $dbh = C4::Context->dbh;
997 my $query=qq|SELECT debarred
999 WHERE borrowernumber=?
1001 my $sth=$dbh->prepare($query);
1002 $sth->execute($borrowernumber);
1003 my ($debarredstatus)=$sth->fetchrow;
1005 if ($debarredstatus eq '1'){
1012 =item UpdateBorrowerDebarred
1014 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1016 update status of borrowers in borrowers table (field debarred)
1018 C<$borrowernumber> borrower number
1022 sub UpdateBorrowerDebarred{
1023 my($borrowernumber) = @_;
1024 my $dbh = C4::Context->dbh;
1025 my $query=qq|UPDATE borrowers
1027 WHERE borrowernumber=?
1029 my $sth=$dbh->prepare($query);
1030 $sth->execute($borrowernumber);
1035 =item CheckExistantNotifyid
1037 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1039 Check and Returns the notify id if exist else return 0.
1041 C<$exist> contains a notify_id
1043 C<$borrowernumber> contains the borrower number
1045 C<$date_due> contains the date of item return
1050 sub CheckExistantNotifyid {
1051 my($borrowernumber,$date_due) = @_;
1052 my $dbh = C4::Context->dbh;
1053 my $query = qq|SELECT notify_id FROM issues,accountlines
1054 WHERE accountlines.borrowernumber =?
1055 AND issues.itemnumber= accountlines.itemnumber
1057 my $sth=$dbh->prepare($query);
1058 $sth->execute($borrowernumber,$date_due);
1059 my ($exist)=$sth->fetchrow;
1070 =item CheckAccountLineItemInfo
1072 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1074 Check and Returns the list of all overdue items from the same file number(notify_id).
1076 C<$exist> contains number of line in accounlines
1077 with the same .biblionumber,itemnumber,accounttype,notify_id
1079 C<$borrowernumber> contains the borrower number
1081 C<$itemnumber> contains item number
1083 C<$accounttype> contains account type
1085 C<$notify_id> contains the file number
1089 sub CheckAccountLineItemInfo {
1090 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1091 my $dbh = C4::Context->dbh;
1092 my $query = qq|SELECT count(*) FROM accountlines
1093 WHERE borrowernumber =?
1097 my $sth=$dbh->prepare($query);
1098 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1099 my ($exist)=$sth->fetchrow;
1104 =head2 CheckItemNotify
1106 Sql request to check if the document has alreday been notified
1107 this function is not exported, only used with GetOverduesForBranch
1111 sub CheckItemNotify {
1112 my ($notify_id,$notify_level,$itemnumber) = @_;
1113 my $dbh = C4::Context->dbh;
1114 my $sth = $dbh->prepare("
1115 SELECT COUNT(*) FROM notifys
1117 AND notify_level = ?
1118 AND itemnumber = ? ");
1119 $sth->execute($notify_id,$notify_level,$itemnumber);
1120 my $notified = $sth->fetchrow;
1125 =head2 GetOverduesForBranch
1127 Sql request for display all information for branchoverdues.pl
1128 2 possibilities : with or without location .
1129 display is filtered by branch
1133 sub GetOverduesForBranch {
1134 my ( $branch, $location) = @_;
1135 if ( not $location ) {
1136 my $dbh = C4::Context->dbh;
1137 my $sth = $dbh->prepare("
1140 borrowers.firstname,
1142 itemtypes.description,
1145 branches.branchname,
1149 items.itemcallnumber,
1150 borrowers.borrowernumber,
1152 biblio.biblionumber,
1154 accountlines.notify_id,
1155 accountlines.notify_level,
1157 accountlines.amountoutstanding
1158 FROM issues,borrowers,biblio,biblioitems,itemtypes,items,branches,accountlines
1159 WHERE ( issues.returndate is null)
1160 AND ( accountlines.amountoutstanding != '0.000000')
1161 AND ( accountlines.accounttype = 'FU')
1162 AND ( issues.borrowernumber = accountlines.borrowernumber )
1163 AND ( issues.itemnumber = accountlines.itemnumber )
1164 AND ( borrowers.borrowernumber = issues.borrowernumber )
1165 AND ( biblio.biblionumber = biblioitems.biblionumber )
1166 AND ( biblioitems.biblionumber = items.biblionumber )
1167 AND ( itemtypes.itemtype = biblioitems.itemtype )
1168 AND ( items.itemnumber = issues.itemnumber )
1169 AND ( branches.branchcode = issues.branchcode )
1170 AND (issues.branchcode = ?)
1171 AND (issues.date_due <= NOW())
1172 ORDER BY borrowers.surname
1174 $sth->execute($branch);
1177 while ( my $data = $sth->fetchrow_hashref ) {
1178 #check if the document has already been notified
1179 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1180 if ($countnotify eq '0'){
1181 $getoverdues[$i] = $data;
1185 return (@getoverdues);
1189 my $dbh = C4::Context->dbh;
1190 my $sth = $dbh->prepare( "
1191 SELECT borrowers.surname,
1192 borrowers.firstname,
1194 itemtypes.description,
1197 branches.branchname,
1201 items.itemcallnumber,
1202 borrowers.borrowernumber,
1204 biblio.biblionumber,
1206 accountlines.notify_id,
1207 accountlines.notify_level,
1209 accountlines.amountoutstanding
1210 FROM issues,borrowers,biblio,biblioitems,itemtypes,items,branches,accountlines
1211 WHERE ( issues.returndate is null )
1212 AND ( accountlines.amountoutstanding != '0.000000')
1213 AND ( accountlines.accounttype = 'FU')
1214 AND ( issues.borrowernumber = accountlines.borrowernumber )
1215 AND ( issues.itemnumber = accountlines.itemnumber )
1216 AND ( borrowers.borrowernumber = issues.borrowernumber )
1217 AND ( biblio.biblionumber = biblioitems.biblionumber )
1218 AND ( biblioitems.biblionumber = items.biblionumber )
1219 AND ( itemtypes.itemtype = biblioitems.itemtype )
1220 AND ( items.itemnumber = issues.itemnumber )
1221 AND ( branches.branchcode = issues.branchcode )
1222 AND (issues.branchcode = ? AND items.location = ?)
1223 AND (issues.date_due <= NOW())
1224 ORDER BY borrowers.surname
1226 $sth->execute( $branch, $location);
1229 while ( my $data = $sth->fetchrow_hashref ) {
1230 #check if the document has already been notified
1231 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1232 if ($countnotify eq '0'){
1233 $getoverdues[$i] = $data;
1238 return (@getoverdues);
1243 =head2 AddNotifyLine
1245 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1247 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1252 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1253 if ( $method eq "phone" ) {
1254 my $dbh = C4::Context->dbh;
1255 my $sth = $dbh->prepare(
1256 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1257 VALUES (?,?,now(),now(),?,?,?)"
1259 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1264 my $dbh = C4::Context->dbh;
1265 my $sth = $dbh->prepare(
1266 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1267 VALUES (?,?,now(),?,?,?)"
1269 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1276 =head2 RemoveNotifyLine
1278 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1280 Cancel a notification
1284 sub RemoveNotifyLine {
1285 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1286 my $dbh = C4::Context->dbh;
1287 my $sth = $dbh->prepare(
1288 "DELETE FROM notifys
1294 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1306 Koha Developement team <info@koha.org>