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 ($overdues) = &Getoverdues();
109 Returns the list of all overdue books, with their itemtype.
111 C<$overdues> is a reference-to-array. Each element is a
112 reference-to-hash whose keys are the fields of the issues table in the
119 my $dbh = C4::Context->dbh;
120 my $sth = $dbh->prepare(
121 "SELECT issues.*,biblioitems.itemtype FROM issues
122 LEFT JOIN items USING (itemnumber)
123 LEFT JOIN biblioitems USING (biblioitemnumber)
124 WHERE date_due < now()
126 NULL ORDER BY borrowernumber
132 while ( my $data = $sth->fetchrow_hashref ) {
133 push @results, $data;
142 ( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh );
150 # From Main.pm, modified to return a list of overdueitems, in addition to a count
151 #checks whether a borrower has overdue items
152 my ( $borrowernumber, $dbh ) = @_;
153 my @datearr = localtime;
155 ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
158 my $sth = $dbh->prepare(
159 "SELECT * FROM issues
160 LEFT JOIN items ON issues.itemnumber = items.itemnumber
161 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
162 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
163 WHERE issues.borrowernumber = ?
164 AND issues.returndate is NULL
165 AND issues.date_due < ?"
167 $sth->execute( $borrowernumber, $today );
168 while ( my $data = $sth->fetchrow_hashref ) {
169 push( @overdueitems, $data );
173 return ( $count, \@overdueitems );
178 ($amount, $chargename, $message) =
179 &CalcFine($itemnumber, $borrowercode, $days_overdue);
181 Calculates the fine for a book.
183 The issuingrules table in the Koha database is a fine matrix, listing
184 the penalties for each type of patron for each type of item and each branch (e.g., the
185 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
186 members might get a longer grace period between the first and second
187 reminders that a book is overdue).
189 The fine is calculated as follows: if it is time for the first
190 reminder, the fine is the value listed for the given (branch, item type,
191 borrower code) combination. If it is time for the second reminder, the
192 fine is doubled. Finally, if it is time to send the account to a
193 collection agency, the fine is set to 5 local monetary units (a really
194 good deal for the patron if the library is in Italy). Otherwise, the
197 Note that the way this function is currently implemented, it only
198 returns a nonzero value on the notable days listed above. That is, if
199 the categoryitems entry says to send a first reminder 7 days after the
200 book is due, then if you call C<&CalcFine> 7 days after the book is
201 due, it will give a nonzero fine. If you call C<&CalcFine> the next
202 day, however, it will say that the fine is 0.
204 C<$itemnumber> is the book's item number.
206 C<$borrowercode> is the borrower code of the patron who currently has
209 C<$days_overdue> is the number of days elapsed since the book's due
212 C<&CalcFine> returns a list of three values:
214 C<$amount> is the fine owed by the patron (see above).
216 C<$chargename> is the chargename field from the applicable record in
217 the categoryitem table, whatever that is.
219 C<$message> is a text message, either "First Notice", "Second Notice",
226 my ( $item, $bortype, $difference , $dues ) = @_;
227 my $dbh = C4::Context->dbh;
230 # calculate how many days the patron is late
231 my $countspecialday=&GetSpecialHolidays($dues,$item->{itemnumber});
232 my $countrepeatableday=&GetRepeatableHolidays($dues,$item->{itemnumber},$difference);
233 my $countalldayclosed = $countspecialday + $countrepeatableday;
234 my $daycount = $difference - $countalldayclosed;
235 # get issuingrules (fines part will be used)
236 my $data = GetIssuingRules($item->{'itemtype'},$bortype);
237 my $daycounttotal = $daycount - $data->{'firstremind'};
238 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
239 if ($data->{'firstremind'} < $daycount)
241 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
244 # get fines default rules
245 my $data = GetIssuingRules($item->{'itemtype'},'*');
246 $daycounttotal = $daycount - $data->{'firstremind'};
247 if ($data->{'firstremind'} < $daycount)
249 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
250 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
255 warn "Calc Fine for $item->{'itemnumber'}, $bortype, $difference , $dues = $amount / $daycount";
256 return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
260 =item GetSpecialHolidays
262 &GetSpecialHolidays($date_dues,$itemnumber);
264 return number of special days between date of the day and date due
266 C<$date_dues> is the envisaged date of book return.
268 C<$itemnumber> is the book's item number.
272 sub GetSpecialHolidays {
273 my ($date_dues,$itemnumber) = @_;
274 # calcul the today date
275 my $today = join "-", &Today();
277 # return the holdingbranch
278 my $iteminfo=GetIssuesIteminfo($itemnumber);
279 # use sql request to find all date between date_due and today
280 my $dbh = C4::Context->dbh;
281 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
282 FROM `special_holidays`
283 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
284 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
287 my @result=GetWdayFromItemnumber($itemnumber);
291 my $sth = $dbh->prepare($query);
292 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
294 while ( my $special_date=$sth->fetchrow_hashref){
295 push (@result_date,$special_date);
298 my $specialdaycount=scalar(@result_date);
300 for (my $i=0;$i<scalar(@result_date);$i++){
301 $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
302 (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
303 for (my $j=0;$j<scalar(@result);$j++){
304 if ($wday == ($result[$j]->{'weekday'})){
310 return $specialdaycount;
313 =item GetRepeatableHolidays
315 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
317 return number of day closed between date of the day and date due
319 C<$date_dues> is the envisaged date of book return.
321 C<$itemnumber> is item number.
323 C<$difference> numbers of between day date of the day and date due
327 sub GetRepeatableHolidays{
328 my ($date_dues,$itemnumber,$difference) = @_;
329 my $dateinsec=UnixDate($date_dues,"%o");
330 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
331 my @result=GetWdayFromItemnumber($itemnumber);
335 for (my $i=0;$i<scalar(@result);$i++){
338 for ( $j=0;$j<$difference;$j++){
339 if ($result[$i]->{'weekday'} == $k)
341 push ( @dayclosedcount ,$k);
347 return scalar(@dayclosedcount);
351 =item GetWayFromItemnumber
353 &Getwdayfromitemnumber($itemnumber);
355 return the different week day from repeatable_holidays table
357 C<$itemnumber> is item number.
361 sub GetWdayFromItemnumber{
363 my $iteminfo=GetIssuesIteminfo($itemnumber);
365 my $dbh = C4::Context->dbh;
366 my $query = qq|SELECT weekday
367 FROM repeatable_holidays
370 my $sth = $dbh->prepare($query);
373 $sth->execute($iteminfo->{'branchcode'});
374 while ( my $weekday=$sth->fetchrow_hashref){
375 push (@result,$weekday);
381 =item GetIssuesIteminfo
383 &GetIssuesIteminfo($itemnumber);
385 return all data from issues about item
387 C<$itemnumber> is item number.
391 sub GetIssuesIteminfo{
393 my $dbh = C4::Context->dbh;
394 my $query = qq|SELECT *
398 my $sth = $dbh->prepare($query);
399 $sth->execute($itemnumber);
400 my ($issuesinfo)=$sth->fetchrow_hashref;
407 &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
409 (Note: the following is mostly conjecture and guesswork.)
411 Updates the fine owed on an overdue book.
413 C<$itemnumber> is the book's item number.
415 C<$borrowernumber> is the borrower number of the patron who currently
416 has the book on loan.
418 C<$amount> is the current amount owed by the patron.
420 C<$type> will be used in the description of the fine.
422 C<$description> is a string that must be present in the description of
423 the fine. I think this is expected to be a date in DD/MM/YYYY format.
425 C<&UpdateFine> looks up the amount currently owed on the given item
426 and sets it to C<$amount>, creating, if necessary, a new entry in the
427 accountlines table of the Koha database.
432 # FIXME - This API doesn't look right: why should the caller have to
433 # specify both the item number and the borrower number? A book can't
434 # be on loan to two different people, so the item number should be
437 my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
438 my $dbh = C4::Context->dbh;
439 # FIXME - What exactly is this query supposed to do? It looks up an
440 # entry in accountlines that matches the given item and borrower
441 # numbers, where the description contains $due, and where the
442 # account type has one of several values, but what does this _mean_?
443 # Does it look up existing fines for this item?
444 # FIXME - What are these various account types? ("FU", "O", "F", "M")
445 my $sth = $dbh->prepare(
446 "Select * from accountlines where itemnumber=? and
447 borrowernumber=? and (accounttype='FU' or accounttype='O' or
448 accounttype='F' or accounttype='M') and description like ?"
450 $sth->execute( $itemnum, $borrowernumber, "%$due%" );
452 if ( my $data = $sth->fetchrow_hashref ) {
454 # I think this if-clause deals with the case where we're updating
456 # print "in accounts ...";
457 if ( $data->{'amount'} != $amount ) {
460 my $diff = $amount - $data->{'amount'};
461 my $out = $data->{'amountoutstanding'} + $diff;
462 my $sth2 = $dbh->prepare(
463 "UPDATE accountlines SET date=now(), amount=?,
464 amountoutstanding=?,accounttype='FU' WHERE
465 borrowernumber=? AND itemnumber=?
466 AND (accounttype='FU' OR accounttype='O') AND description LIKE ?"
468 $sth2->execute( $amount, $out, $data->{'borrowernumber'},
469 $data->{'itemnumber'}, "%$due%" );
474 # print "no update needed $data->{'amount'}"
479 # I think this else-clause deals with the case where we're adding
481 my $sth4 = $dbh->prepare(
482 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
484 $sth4->execute($itemnum);
485 my $title = $sth4->fetchrow_hashref;
488 # # print "not in account";
489 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
492 # # FIXME - Make $accountno a scalar.
493 # my @accountno = $sth3->fetchrow_array;
497 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
498 my $sth2 = $dbh->prepare(
499 "INSERT INTO accountlines
500 (borrowernumber,itemnumber,date,amount,
501 description,accounttype,amountoutstanding,accountno) VALUES
502 (?,?,now(),?,?,'FU',?,?)"
504 $sth2->execute( $borrowernumber, $itemnum, $amount,
505 "$type $title->{'title'} $due",
506 $amount, $nextaccntno);
511 C4::Context->userenv->{'number'},
515 "due=".$due." amount=".$amount." itemnumber=".$itemnum
516 ) if C4::Context->preference("FinesLog");
523 $borrower = &BorType($borrowernumber);
525 Looks up a patron by borrower number.
527 C<$borrower> is a reference-to-hash whose keys are all of the fields
528 from the borrowers and categories tables of the Koha database. Thus,
529 C<$borrower> contains all information about both the borrower and
530 category he or she belongs to.
536 my ($borrowernumber) = @_;
537 my $dbh = C4::Context->dbh;
538 my $sth = $dbh->prepare(
539 "SELECT * from borrowers
540 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
541 WHERE borrowernumber=?"
543 $sth->execute($borrowernumber);
544 my $data = $sth->fetchrow_hashref;
549 =item ReplacementCost
551 $cost = &ReplacementCost($itemnumber);
553 Returns the replacement cost of the item with the given item number.
558 sub ReplacementCost {
560 my $dbh = C4::Context->dbh;
562 $dbh->prepare("Select replacementprice from items where itemnumber=?");
563 $sth->execute($itemnum);
565 # FIXME - Use fetchrow_array or something.
566 my $data = $sth->fetchrow_hashref;
568 return ( $data->{'replacementprice'} );
573 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
575 return the total of fine
577 C<$itemnum> is item number
579 C<$borrowernumber> is the borrowernumber
585 my ( $itemnum, $borrowernumber ) = @_;
586 my $dbh = C4::Context->dbh();
587 my $query = "SELECT sum(amountoutstanding) FROM accountlines
588 where accounttype like 'F%'
589 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
590 my $sth = $dbh->prepare($query);
591 $sth->execute( $itemnum, $borrowernumber );
592 my $data = $sth->fetchrow_hashref();
595 return ( $data->{'sum(amountoutstanding)'} );
601 =item GetIssuingRules
603 $data = &GetIssuingRules($itemtype,$categorycode);
605 Looks up for all issuingrules an item info
607 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
608 from the borrowers and categories tables of the Koha database. Thus,
610 C<$categorycode> contains information about borrowers category
612 C<$data> contains all information about both the borrower and
613 category he or she belongs to.
616 sub GetIssuingRules {
617 my ($itemtype,$categorycode)=@_;
618 my $dbh = C4::Context->dbh();
619 my $query=qq|SELECT *
621 WHERE issuingrules.itemtype=?
622 AND issuingrules.categorycode=?
624 my $sth = $dbh->prepare($query);
626 $sth->execute($itemtype,$categorycode);
627 my ($data) = $sth->fetchrow_hashref;
634 sub ReplacementCost2 {
635 my ( $itemnum, $borrowernumber ) = @_;
636 my $dbh = C4::Context->dbh();
637 my $query = "SELECT amountoutstanding
639 WHERE accounttype like 'L'
640 AND amountoutstanding > 0
642 AND borrowernumber= ?";
643 my $sth = $dbh->prepare($query);
644 $sth->execute( $itemnum, $borrowernumber );
645 my $data = $sth->fetchrow_hashref();
648 return ( $data->{'amountoutstanding'} );
652 =item GetNextIdNotify
654 ($result) = &GetNextIdNotify($reference);
656 Returns the new file number
658 C<$result> contains the next file number
660 C<$reference> contains the beggining of file number
666 sub GetNextIdNotify {
668 my $query=qq|SELECT max(notify_id)
670 WHERE notify_id like \"$reference%\"
672 # AND borrowernumber=?|;
673 my $dbh = C4::Context->dbh;
674 my $sth=$dbh->prepare($query);
676 my $result=$sth->fetchrow;
681 ($result=$reference."01") ;
684 $count=substr($result,6)+1;
687 ($count = "0".$count);
689 $result=$reference.$count;
697 (@notify) = &AmountNotify($borrowernumber);
699 Returns amount for all file per borrowers
700 C<@notify> array contains all file per borrowers
702 C<$notify_id> contains the file number for the borrower number nad item number
707 my ($borrowernumber)=@_;
708 my $dbh = C4::Context->dbh;
709 my $query=qq| SELECT distinct(notify_id)
711 WHERE borrowernumber=?|;
713 my $sth=$dbh->prepare($query);
714 $sth->execute($borrowernumber);
715 while ( my ($numberofnotify)=$sth->fetchrow){
716 push (@notify,$numberofnotify);
726 ($totalnotify) = &AmountNotify($notifyid);
728 Returns amount for all file per borrowers
729 C<$notifyid> is the file number
731 C<$totalnotify> contains amount of a file
733 C<$notify_id> contains the file number for the borrower number nad item number
739 my $dbh = C4::Context->dbh;
740 my $query=qq| SELECT sum(amountoutstanding)
743 my $sth=$dbh->prepare($query);
744 $sth->execute($notifyid);
745 my $totalnotify=$sth->fetchrow;
747 return ($totalnotify);
753 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
755 Returns the file number per borrower and itemnumber
757 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
758 from the items tables of the Koha database. Thus,
760 C<$itemnumber> contains the borrower categorycode
762 C<$notify_id> contains the file number for the borrower number nad item number
767 my ($borrowernumber,$itemnumber)=@_;
768 my $query=qq|SELECT notify_id
770 WHERE borrowernumber=?
772 AND (accounttype='FU' or accounttype='O')|;
773 my $dbh = C4::Context->dbh;
774 my $sth=$dbh->prepare($query);
775 $sth->execute($borrowernumber,$itemnumber);
776 my ($notify_id)=$sth->fetchrow;
782 =item CreateItemAccountLine
784 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
786 update the account lines with file number or with file level
788 C<$items> is a reference-to-hash whose keys are all of the fields
789 from the items tables of the Koha database. Thus,
791 C<$itemnumber> contains the item number
793 C<$borrowernumber> contains the borrower number
795 C<$date> contains the date of the day
797 C<$amount> contains item price
799 C<$description> contains the descritpion of accounttype
801 C<$accounttype> contains the account type
803 C<$amountoutstanding> contains the $amountoutstanding
805 C<$timestamp> contains the timestamp with time and the date of the day
807 C<$notify_id> contains the file number
809 C<$level> contains the file level
814 sub CreateItemAccountLine {
815 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
816 my $dbh = C4::Context->dbh;
817 my $nextaccntno = getnextacctno($borrowernumber);
818 my $query= "INSERT into accountlines
819 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
821 (?,?,?,?,?,?,?,?,?,?,?)";
824 my $sth=$dbh->prepare($query);
825 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
829 =item UpdateAccountLines
831 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
833 update the account lines with file number or with file level
835 C<$items> is a reference-to-hash whose keys are all of the fields
836 from the items tables of the Koha database. Thus,
838 C<$itemnumber> contains the item number
840 C<$notify_id> contains the file number
842 C<$notify_level> contains the file level
844 C<$borrowernumber> contains the borrowernumber
848 sub UpdateAccountLines {
849 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
851 if ($notify_id eq '')
854 $query=qq|UPDATE accountlines
856 WHERE borrowernumber=? AND itemnumber=?
857 AND (accounttype='FU' or accounttype='O')|;
860 $query=qq|UPDATE accountlines
861 SET notify_id=?, notify_level=?
862 WHERE borrowernumber=?
864 AND (accounttype='FU' or accounttype='O')|;
866 my $dbh = C4::Context->dbh;
867 my $sth=$dbh->prepare($query);
869 if ($notify_id eq '')
871 $sth->execute($notify_level,$borrowernumber,$itemnumber);
874 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
883 ($items) = &GetItems($itemnumber);
885 Returns the list of all delays from overduerules.
887 C<$items> is a reference-to-hash whose keys are all of the fields
888 from the items tables of the Koha database. Thus,
890 C<$itemnumber> contains the borrower categorycode
895 my($itemnumber) = @_;
896 my $query=qq|SELECT *
899 my $dbh = C4::Context->dbh;
900 my $sth=$dbh->prepare($query);
901 $sth->execute($itemnumber);
902 my ($items)=$sth->fetchrow_hashref;
907 =item GetOverdueDelays
909 (@delays) = &GetOverdueDelays($categorycode);
911 Returns the list of all delays from overduerules.
913 C<@delays> it's an array contains the three delays from overduerules table
915 C<$categorycode> contains the borrower categorycode
919 sub GetOverdueDelays {
921 my $dbh = C4::Context->dbh;
922 my $query=qq|SELECT delay1,delay2,delay3
924 WHERE categorycode=?|;
925 my $sth=$dbh->prepare($query);
926 $sth->execute($category);
927 my (@delays)=$sth->fetchrow_array;
932 =item CheckAccountLineLevelInfo
934 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
936 Check and Returns the list of all overdue books.
938 C<$exist> contains number of line in accounlines
939 with the same .biblionumber,itemnumber,accounttype,and notify_level
941 C<$borrowernumber> contains the borrower number
943 C<$itemnumber> contains item number
945 C<$accounttype> contains account type
947 C<$notify_level> contains the accountline level
952 sub CheckAccountLineLevelInfo {
953 my($borrowernumber,$itemnumber,$level) = @_;
954 my $dbh = C4::Context->dbh;
955 my $query= qq|SELECT count(*)
957 WHERE borrowernumber =?
960 my $sth=$dbh->prepare($query);
961 $sth->execute($borrowernumber,$itemnumber,$level);
962 my ($exist)=$sth->fetchrow;
967 =item GetOverduerules
969 ($overduerules) = &GetOverduerules($categorycode);
971 Returns the value of borrowers (debarred or not) with notify level
973 C<$overduerules> return value of debbraed field in overduerules table
975 C<$category> contains the borrower categorycode
977 C<$notify_level> contains the notify level
982 my($category,$notify_level) = @_;
983 my $dbh = C4::Context->dbh;
984 my $query=qq|SELECT debarred$notify_level
986 WHERE categorycode=?|;
987 my $sth=$dbh->prepare($query);
988 $sth->execute($category);
989 my ($overduerules)=$sth->fetchrow;
991 return($overduerules);
995 =item CheckBorrowerDebarred
997 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
999 Check if the borrowers is already debarred
1001 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
1003 C<$borrowernumber> contains the borrower number
1008 sub CheckBorrowerDebarred{
1009 my($borrowernumber) = @_;
1010 my $dbh = C4::Context->dbh;
1011 my $query=qq|SELECT debarred
1013 WHERE borrowernumber=?
1015 my $sth=$dbh->prepare($query);
1016 $sth->execute($borrowernumber);
1017 my ($debarredstatus)=$sth->fetchrow;
1019 if ($debarredstatus eq '1'){
1026 =item UpdateBorrowerDebarred
1028 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1030 update status of borrowers in borrowers table (field debarred)
1032 C<$borrowernumber> borrower number
1036 sub UpdateBorrowerDebarred{
1037 my($borrowernumber) = @_;
1038 my $dbh = C4::Context->dbh;
1039 my $query=qq|UPDATE borrowers
1041 WHERE borrowernumber=?
1043 my $sth=$dbh->prepare($query);
1044 $sth->execute($borrowernumber);
1049 =item CheckExistantNotifyid
1051 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1053 Check and Returns the notify id if exist else return 0.
1055 C<$exist> contains a notify_id
1057 C<$borrowernumber> contains the borrower number
1059 C<$date_due> contains the date of item return
1064 sub CheckExistantNotifyid {
1065 my($borrowernumber,$date_due) = @_;
1066 my $dbh = C4::Context->dbh;
1067 my $query = qq|SELECT notify_id FROM accountlines
1068 LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1069 WHERE accountlines.borrowernumber =?
1071 my $sth=$dbh->prepare($query);
1072 $sth->execute($borrowernumber,$date_due);
1073 my ($exist)=$sth->fetchrow;
1084 =item CheckAccountLineItemInfo
1086 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1088 Check and Returns the list of all overdue items from the same file number(notify_id).
1090 C<$exist> contains number of line in accounlines
1091 with the same .biblionumber,itemnumber,accounttype,notify_id
1093 C<$borrowernumber> contains the borrower number
1095 C<$itemnumber> contains item number
1097 C<$accounttype> contains account type
1099 C<$notify_id> contains the file number
1103 sub CheckAccountLineItemInfo {
1104 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1105 my $dbh = C4::Context->dbh;
1106 my $query = qq|SELECT count(*) FROM accountlines
1107 WHERE borrowernumber =?
1111 my $sth=$dbh->prepare($query);
1112 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1113 my ($exist)=$sth->fetchrow;
1118 =head2 CheckItemNotify
1120 Sql request to check if the document has alreday been notified
1121 this function is not exported, only used with GetOverduesForBranch
1125 sub CheckItemNotify {
1126 my ($notify_id,$notify_level,$itemnumber) = @_;
1127 my $dbh = C4::Context->dbh;
1128 my $sth = $dbh->prepare("
1129 SELECT COUNT(*) FROM notifys
1131 AND notify_level = ?
1132 AND itemnumber = ? ");
1133 $sth->execute($notify_id,$notify_level,$itemnumber);
1134 my $notified = $sth->fetchrow;
1139 =head2 GetOverduesForBranch
1141 Sql request for display all information for branchoverdues.pl
1142 2 possibilities : with or without location .
1143 display is filtered by branch
1147 sub GetOverduesForBranch {
1148 my ( $branch, $location) = @_;
1149 if ( not $location ) {
1150 my $dbh = C4::Context->dbh;
1151 my $sth = $dbh->prepare("
1154 borrowers.firstname,
1156 itemtypes.description,
1159 branches.branchname,
1163 items.itemcallnumber,
1164 borrowers.borrowernumber,
1166 biblio.biblionumber,
1168 accountlines.notify_id,
1169 accountlines.notify_level,
1171 accountlines.amountoutstanding
1173 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1174 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1175 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1176 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1177 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1178 LEFT JOIN itemtypes ON itemtypes.itemtype = biblioitems.itemtype
1179 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1180 WHERE ( issues.returndate is null)
1181 AND ( accountlines.amountoutstanding != '0.000000')
1182 AND ( accountlines.accounttype = 'FU')
1183 AND (issues.branchcode = ?)
1184 AND (issues.date_due <= NOW())
1185 ORDER BY borrowers.surname
1187 $sth->execute($branch);
1190 while ( my $data = $sth->fetchrow_hashref ) {
1191 #check if the document has already been notified
1192 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1193 if ($countnotify eq '0'){
1194 $getoverdues[$i] = $data;
1198 return (@getoverdues);
1202 my $dbh = C4::Context->dbh;
1203 my $sth = $dbh->prepare( "
1204 SELECT borrowers.surname,
1205 borrowers.firstname,
1207 itemtypes.description,
1210 branches.branchname,
1214 items.itemcallnumber,
1215 borrowers.borrowernumber,
1217 biblio.biblionumber,
1219 accountlines.notify_id,
1220 accountlines.notify_level,
1222 accountlines.amountoutstanding
1224 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1225 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1226 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1227 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1228 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1229 LEFT JOIN itemtypes ON itemtypes.itemtype = biblioitems.itemtype
1230 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1231 WHERE ( issues.returndate is null )
1232 AND ( accountlines.amountoutstanding != '0.000000')
1233 AND ( accountlines.accounttype = 'FU')
1234 AND (issues.branchcode = ? AND items.location = ?)
1235 AND (issues.date_due <= NOW())
1236 ORDER BY borrowers.surname
1238 $sth->execute( $branch, $location);
1241 while ( my $data = $sth->fetchrow_hashref ) {
1242 #check if the document has already been notified
1243 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1244 if ($countnotify eq '0'){
1245 $getoverdues[$i] = $data;
1250 return (@getoverdues);
1255 =head2 AddNotifyLine
1257 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1259 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1264 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1265 if ( $method eq "phone" ) {
1266 my $dbh = C4::Context->dbh;
1267 my $sth = $dbh->prepare(
1268 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1269 VALUES (?,?,now(),now(),?,?,?)"
1271 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1276 my $dbh = C4::Context->dbh;
1277 my $sth = $dbh->prepare(
1278 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1279 VALUES (?,?,now(),?,?,?)"
1281 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1288 =head2 RemoveNotifyLine
1290 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1292 Cancel a notification
1296 sub RemoveNotifyLine {
1297 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1298 my $dbh = C4::Context->dbh;
1299 my $sth = $dbh->prepare(
1300 "DELETE FROM notifys
1306 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1318 Koha Developement team <info@koha.org>