4 # Copyright 2000-2002 Katipo Communications
6 # This file is part of Koha.
8 # Koha is free software; you can redistribute it and/or modify it under the
9 # terms of the GNU General Public License as published by the Free Software
10 # Foundation; either version 2 of the License, or (at your option) any later
13 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
14 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
15 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License along with
18 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
19 # Suite 330, Boston, MA 02111-1307 USA
24 use Date::Calc qw/Today/;
25 use vars qw($VERSION @ISA @EXPORT);
27 use Date::Manip qw/UnixDate/;
28 use C4::Log; # logaction
30 # set the version for version checking
35 C4::Circulation::Fines - Koha module dealing with fines
43 This module contains several functions for dealing with fines for
44 overdue items. It is primarily used by the 'misc/fines2.pl' script.
53 # subs to rename (and maybe merge some...)
58 &CheckAccountLineLevelInfo
59 &CheckAccountLineItemInfo
60 &CheckExistantNotifyid
70 &CreateItemAccountLine
84 # All subs to move : check that an equivalent don't exist already before moving
87 # subs to move to Circulation.pm
92 # subs to move to Members.pm
94 &CheckBorrowerDebarred
95 &UpdateBorrowerDebarred
97 # subs to move to Biblio.pm
105 ($overdues) = &Getoverdues();
107 Returns the list of all overdue books, with their itemtype.
109 C<$overdues> is a reference-to-array. Each element is a
110 reference-to-hash whose keys are the fields of the issues table in the
117 my $dbh = C4::Context->dbh;
118 my $itype_q = (C4::context->preference('item-level_itype')) ? " items.ccode as itemtype "
119 my $sth = (C4::context->preference('item-level_itype')) ?
121 "SELECT issues.*,items.ccode as itemtype FROM issues
122 LEFT JOIN items USING (itemnumber)
123 WHERE date_due < now()
124 AND returndate IS NULL ORDER BY borrowernumber " )
127 "SELECT issues.*,biblioitems.itemtype,items.ccode FROM issues
128 LEFT JOIN items USING (itemnumber)
129 LEFT JOIN biblioitems USING (biblioitemnumber)
130 WHERE date_due < now()
132 NULL ORDER BY borrowernumber " );
136 while ( my $data = $sth->fetchrow_hashref ) {
137 push @results, $data;
146 ( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh );
154 # From Main.pm, modified to return a list of overdueitems, in addition to a count
155 #checks whether a borrower has overdue items
156 my ( $borrowernumber, $dbh ) = @_;
157 my @datearr = localtime;
159 ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
162 my $sth = $dbh->prepare(
163 "SELECT * FROM issues
164 LEFT JOIN items ON issues.itemnumber = items.itemnumber
165 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
166 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
167 WHERE issues.borrowernumber = ?
168 AND issues.returndate is NULL
169 AND issues.date_due < ?"
171 $sth->execute( $borrowernumber, $today );
172 while ( my $data = $sth->fetchrow_hashref ) {
173 push( @overdueitems, $data );
177 return ( $count, \@overdueitems );
182 ($amount, $chargename, $message) =
183 &CalcFine($itemnumber, $borrowercode, $days_overdue);
185 Calculates the fine for a book.
187 The issuingrules table in the Koha database is a fine matrix, listing
188 the penalties for each type of patron for each type of item and each branch (e.g., the
189 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
190 members might get a longer grace period between the first and second
191 reminders that a book is overdue).
193 The fine is calculated as follows: if it is time for the first
194 reminder, the fine is the value listed for the given (branch, item type,
195 borrower code) combination. If it is time for the second reminder, the
196 fine is doubled. Finally, if it is time to send the account to a
197 collection agency, the fine is set to 5 local monetary units (a really
198 good deal for the patron if the library is in Italy). Otherwise, the
201 Note that the way this function is currently implemented, it only
202 returns a nonzero value on the notable days listed above. That is, if
203 the categoryitems entry says to send a first reminder 7 days after the
204 book is due, then if you call C<&CalcFine> 7 days after the book is
205 due, it will give a nonzero fine. If you call C<&CalcFine> the next
206 day, however, it will say that the fine is 0.
208 C<$itemnumber> is the book's item number.
210 C<$borrowercode> is the borrower code of the patron who currently has
213 C<$days_overdue> is the number of days elapsed since the book's due
216 C<&CalcFine> returns a list of three values:
218 C<$amount> is the fine owed by the patron (see above).
220 C<$chargename> is the chargename field from the applicable record in
221 the categoryitem table, whatever that is.
223 C<$message> is a text message, either "First Notice", "Second Notice",
230 my ( $item, $bortype, $difference , $dues ) = @_;
231 my $dbh = C4::Context->dbh;
234 # calculate how many days the patron is late
235 my $countspecialday=&GetSpecialHolidays($dues,$item->{itemnumber});
236 my $countrepeatableday=&GetRepeatableHolidays($dues,$item->{itemnumber},$difference);
237 my $countalldayclosed = $countspecialday + $countrepeatableday;
238 my $daycount = $difference - $countalldayclosed;
239 # get issuingrules (fines part will be used)
240 my $data = GetIssuingRules($item->{'itemtype'},$bortype);
241 my $daycounttotal = $daycount - $data->{'firstremind'};
242 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
243 if ($data->{'firstremind'} < $daycount)
245 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
248 # get fines default rules
249 my $data = GetIssuingRules($item->{'itemtype'},'*');
250 $daycounttotal = $daycount - $data->{'firstremind'};
251 if ($data->{'firstremind'} < $daycount)
253 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
254 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
259 warn "Calc Fine for $item->{'itemnumber'}, $bortype, $difference , $dues = $amount / $daycount";
260 return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
264 =item GetSpecialHolidays
266 &GetSpecialHolidays($date_dues,$itemnumber);
268 return number of special days between date of the day and date due
270 C<$date_dues> is the envisaged date of book return.
272 C<$itemnumber> is the book's item number.
276 sub GetSpecialHolidays {
277 my ($date_dues,$itemnumber) = @_;
278 # calcul the today date
279 my $today = join "-", &Today();
281 # return the holdingbranch
282 my $iteminfo=GetIssuesIteminfo($itemnumber);
283 # use sql request to find all date between date_due and today
284 my $dbh = C4::Context->dbh;
285 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
286 FROM `special_holidays`
287 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
288 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
291 my @result=GetWdayFromItemnumber($itemnumber);
295 my $sth = $dbh->prepare($query);
296 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
298 while ( my $special_date=$sth->fetchrow_hashref){
299 push (@result_date,$special_date);
302 my $specialdaycount=scalar(@result_date);
304 for (my $i=0;$i<scalar(@result_date);$i++){
305 $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
306 (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
307 for (my $j=0;$j<scalar(@result);$j++){
308 if ($wday == ($result[$j]->{'weekday'})){
314 return $specialdaycount;
317 =item GetRepeatableHolidays
319 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
321 return number of day closed between date of the day and date due
323 C<$date_dues> is the envisaged date of book return.
325 C<$itemnumber> is item number.
327 C<$difference> numbers of between day date of the day and date due
331 sub GetRepeatableHolidays{
332 my ($date_dues,$itemnumber,$difference) = @_;
333 my $dateinsec=UnixDate($date_dues,"%o");
334 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
335 my @result=GetWdayFromItemnumber($itemnumber);
339 for (my $i=0;$i<scalar(@result);$i++){
342 for ( $j=0;$j<$difference;$j++){
343 if ($result[$i]->{'weekday'} == $k)
345 push ( @dayclosedcount ,$k);
351 return scalar(@dayclosedcount);
355 =item GetWayFromItemnumber
357 &Getwdayfromitemnumber($itemnumber);
359 return the different week day from repeatable_holidays table
361 C<$itemnumber> is item number.
365 sub GetWdayFromItemnumber{
367 my $iteminfo=GetIssuesIteminfo($itemnumber);
369 my $dbh = C4::Context->dbh;
370 my $query = qq|SELECT weekday
371 FROM repeatable_holidays
374 my $sth = $dbh->prepare($query);
377 $sth->execute($iteminfo->{'branchcode'});
378 while ( my $weekday=$sth->fetchrow_hashref){
379 push (@result,$weekday);
385 =item GetIssuesIteminfo
387 &GetIssuesIteminfo($itemnumber);
389 return all data from issues about item
391 C<$itemnumber> is item number.
395 sub GetIssuesIteminfo{
397 my $dbh = C4::Context->dbh;
398 my $query = qq|SELECT *
402 my $sth = $dbh->prepare($query);
403 $sth->execute($itemnumber);
404 my ($issuesinfo)=$sth->fetchrow_hashref;
411 &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
413 (Note: the following is mostly conjecture and guesswork.)
415 Updates the fine owed on an overdue book.
417 C<$itemnumber> is the book's item number.
419 C<$borrowernumber> is the borrower number of the patron who currently
420 has the book on loan.
422 C<$amount> is the current amount owed by the patron.
424 C<$type> will be used in the description of the fine.
426 C<$description> is a string that must be present in the description of
427 the fine. I think this is expected to be a date in DD/MM/YYYY format.
429 C<&UpdateFine> looks up the amount currently owed on the given item
430 and sets it to C<$amount>, creating, if necessary, a new entry in the
431 accountlines table of the Koha database.
436 # FIXME - This API doesn't look right: why should the caller have to
437 # specify both the item number and the borrower number? A book can't
438 # be on loan to two different people, so the item number should be
441 my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
442 my $dbh = C4::Context->dbh;
443 # FIXME - What exactly is this query supposed to do? It looks up an
444 # entry in accountlines that matches the given item and borrower
445 # numbers, where the description contains $due, and where the
446 # account type has one of several values, but what does this _mean_?
447 # Does it look up existing fines for this item?
448 # FIXME - What are these various account types? ("FU", "O", "F", "M")
449 my $sth = $dbh->prepare(
450 "Select * from accountlines where itemnumber=? and
451 borrowernumber=? and (accounttype='FU' or accounttype='O' or
452 accounttype='F' or accounttype='M') and description like ?"
454 $sth->execute( $itemnum, $borrowernumber, "%$due%" );
456 if ( my $data = $sth->fetchrow_hashref ) {
458 # I think this if-clause deals with the case where we're updating
460 # print "in accounts ...";
461 if ( $data->{'amount'} != $amount ) {
464 my $diff = $amount - $data->{'amount'};
465 my $out = $data->{'amountoutstanding'} + $diff;
466 my $sth2 = $dbh->prepare(
467 "UPDATE accountlines SET date=now(), amount=?,
468 amountoutstanding=?,accounttype='FU' WHERE
469 borrowernumber=? AND itemnumber=?
470 AND (accounttype='FU' OR accounttype='O') AND description LIKE ?"
472 $sth2->execute( $amount, $out, $data->{'borrowernumber'},
473 $data->{'itemnumber'}, "%$due%" );
478 # print "no update needed $data->{'amount'}"
483 # I think this else-clause deals with the case where we're adding
485 my $sth4 = $dbh->prepare(
486 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
488 $sth4->execute($itemnum);
489 my $title = $sth4->fetchrow_hashref;
492 # # print "not in account";
493 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
496 # # FIXME - Make $accountno a scalar.
497 # my @accountno = $sth3->fetchrow_array;
501 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
502 my $sth2 = $dbh->prepare(
503 "INSERT INTO accountlines
504 (borrowernumber,itemnumber,date,amount,
505 description,accounttype,amountoutstanding,accountno) VALUES
506 (?,?,now(),?,?,'FU',?,?)"
508 $sth2->execute( $borrowernumber, $itemnum, $amount,
509 "$type $title->{'title'} $due",
510 $amount, $nextaccntno);
515 C4::Context->userenv->{'number'},
519 "due=".$due." amount=".$amount." itemnumber=".$itemnum
520 ) if C4::Context->preference("FinesLog");
527 $borrower = &BorType($borrowernumber);
529 Looks up a patron by borrower number.
531 C<$borrower> is a reference-to-hash whose keys are all of the fields
532 from the borrowers and categories tables of the Koha database. Thus,
533 C<$borrower> contains all information about both the borrower and
534 category he or she belongs to.
540 my ($borrowernumber) = @_;
541 my $dbh = C4::Context->dbh;
542 my $sth = $dbh->prepare(
543 "SELECT * from borrowers
544 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
545 WHERE borrowernumber=?"
547 $sth->execute($borrowernumber);
548 my $data = $sth->fetchrow_hashref;
553 =item ReplacementCost
555 $cost = &ReplacementCost($itemnumber);
557 Returns the replacement cost of the item with the given item number.
562 sub ReplacementCost {
564 my $dbh = C4::Context->dbh;
566 $dbh->prepare("Select replacementprice from items where itemnumber=?");
567 $sth->execute($itemnum);
569 # FIXME - Use fetchrow_array or something.
570 my $data = $sth->fetchrow_hashref;
572 return ( $data->{'replacementprice'} );
577 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
579 return the total of fine
581 C<$itemnum> is item number
583 C<$borrowernumber> is the borrowernumber
589 my ( $itemnum, $borrowernumber ) = @_;
590 my $dbh = C4::Context->dbh();
591 my $query = "SELECT sum(amountoutstanding) FROM accountlines
592 where accounttype like 'F%'
593 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
594 my $sth = $dbh->prepare($query);
595 $sth->execute( $itemnum, $borrowernumber );
596 my $data = $sth->fetchrow_hashref();
599 return ( $data->{'sum(amountoutstanding)'} );
605 =item GetIssuingRules
607 $data = &GetIssuingRules($itemtype,$categorycode);
609 Looks up for all issuingrules an item info
611 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
612 from the borrowers and categories tables of the Koha database. Thus,
614 C<$categorycode> contains information about borrowers category
616 C<$data> contains all information about both the borrower and
617 category he or she belongs to.
620 sub GetIssuingRules {
621 my ($itemtype,$categorycode)=@_;
622 my $dbh = C4::Context->dbh();
623 my $query=qq|SELECT *
625 WHERE issuingrules.itemtype=?
626 AND issuingrules.categorycode=?
628 my $sth = $dbh->prepare($query);
630 $sth->execute($itemtype,$categorycode);
631 my ($data) = $sth->fetchrow_hashref;
638 sub ReplacementCost2 {
639 my ( $itemnum, $borrowernumber ) = @_;
640 my $dbh = C4::Context->dbh();
641 my $query = "SELECT amountoutstanding
643 WHERE accounttype like 'L'
644 AND amountoutstanding > 0
646 AND borrowernumber= ?";
647 my $sth = $dbh->prepare($query);
648 $sth->execute( $itemnum, $borrowernumber );
649 my $data = $sth->fetchrow_hashref();
652 return ( $data->{'amountoutstanding'} );
656 =item GetNextIdNotify
658 ($result) = &GetNextIdNotify($reference);
660 Returns the new file number
662 C<$result> contains the next file number
664 C<$reference> contains the beggining of file number
670 sub GetNextIdNotify {
672 my $query=qq|SELECT max(notify_id)
674 WHERE notify_id like \"$reference%\"
676 # AND borrowernumber=?|;
677 my $dbh = C4::Context->dbh;
678 my $sth=$dbh->prepare($query);
680 my $result=$sth->fetchrow;
685 ($result=$reference."01") ;
688 $count=substr($result,6)+1;
691 ($count = "0".$count);
693 $result=$reference.$count;
701 (@notify) = &AmountNotify($borrowernumber);
703 Returns amount for all file per borrowers
704 C<@notify> array contains all file per borrowers
706 C<$notify_id> contains the file number for the borrower number nad item number
711 my ($borrowernumber)=@_;
712 my $dbh = C4::Context->dbh;
713 my $query=qq| SELECT distinct(notify_id)
715 WHERE borrowernumber=?|;
717 my $sth=$dbh->prepare($query);
718 $sth->execute($borrowernumber);
719 while ( my ($numberofnotify)=$sth->fetchrow){
720 push (@notify,$numberofnotify);
730 ($totalnotify) = &AmountNotify($notifyid);
732 Returns amount for all file per borrowers
733 C<$notifyid> is the file number
735 C<$totalnotify> contains amount of a file
737 C<$notify_id> contains the file number for the borrower number nad item number
743 my $dbh = C4::Context->dbh;
744 my $query=qq| SELECT sum(amountoutstanding)
747 my $sth=$dbh->prepare($query);
748 $sth->execute($notifyid);
749 my $totalnotify=$sth->fetchrow;
751 return ($totalnotify);
757 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
759 Returns the file number per borrower and itemnumber
761 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
762 from the items tables of the Koha database. Thus,
764 C<$itemnumber> contains the borrower categorycode
766 C<$notify_id> contains the file number for the borrower number nad item number
771 my ($borrowernumber,$itemnumber)=@_;
772 my $query=qq|SELECT notify_id
774 WHERE borrowernumber=?
776 AND (accounttype='FU' or accounttype='O')|;
777 my $dbh = C4::Context->dbh;
778 my $sth=$dbh->prepare($query);
779 $sth->execute($borrowernumber,$itemnumber);
780 my ($notify_id)=$sth->fetchrow;
786 =item CreateItemAccountLine
788 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
790 update the account lines with file number or with file level
792 C<$items> is a reference-to-hash whose keys are all of the fields
793 from the items tables of the Koha database. Thus,
795 C<$itemnumber> contains the item number
797 C<$borrowernumber> contains the borrower number
799 C<$date> contains the date of the day
801 C<$amount> contains item price
803 C<$description> contains the descritpion of accounttype
805 C<$accounttype> contains the account type
807 C<$amountoutstanding> contains the $amountoutstanding
809 C<$timestamp> contains the timestamp with time and the date of the day
811 C<$notify_id> contains the file number
813 C<$level> contains the file level
818 sub CreateItemAccountLine {
819 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
820 my $dbh = C4::Context->dbh;
821 my $nextaccntno = getnextacctno($borrowernumber);
822 my $query= "INSERT into accountlines
823 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
825 (?,?,?,?,?,?,?,?,?,?,?)";
828 my $sth=$dbh->prepare($query);
829 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
833 =item UpdateAccountLines
835 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
837 update the account lines with file number or with file level
839 C<$items> is a reference-to-hash whose keys are all of the fields
840 from the items tables of the Koha database. Thus,
842 C<$itemnumber> contains the item number
844 C<$notify_id> contains the file number
846 C<$notify_level> contains the file level
848 C<$borrowernumber> contains the borrowernumber
852 sub UpdateAccountLines {
853 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
855 if ($notify_id eq '')
858 $query=qq|UPDATE accountlines
860 WHERE borrowernumber=? AND itemnumber=?
861 AND (accounttype='FU' or accounttype='O')|;
864 $query=qq|UPDATE accountlines
865 SET notify_id=?, notify_level=?
866 WHERE borrowernumber=?
868 AND (accounttype='FU' or accounttype='O')|;
870 my $dbh = C4::Context->dbh;
871 my $sth=$dbh->prepare($query);
873 if ($notify_id eq '')
875 $sth->execute($notify_level,$borrowernumber,$itemnumber);
878 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
887 ($items) = &GetItems($itemnumber);
889 Returns the list of all delays from overduerules.
891 C<$items> is a reference-to-hash whose keys are all of the fields
892 from the items tables of the Koha database. Thus,
894 C<$itemnumber> contains the borrower categorycode
899 my($itemnumber) = @_;
900 my $query=qq|SELECT *
903 my $dbh = C4::Context->dbh;
904 my $sth=$dbh->prepare($query);
905 $sth->execute($itemnumber);
906 my ($items)=$sth->fetchrow_hashref;
911 =item GetOverdueDelays
913 (@delays) = &GetOverdueDelays($categorycode);
915 Returns the list of all delays from overduerules.
917 C<@delays> it's an array contains the three delays from overduerules table
919 C<$categorycode> contains the borrower categorycode
923 sub GetOverdueDelays {
925 my $dbh = C4::Context->dbh;
926 my $query=qq|SELECT delay1,delay2,delay3
928 WHERE categorycode=?|;
929 my $sth=$dbh->prepare($query);
930 $sth->execute($category);
931 my (@delays)=$sth->fetchrow_array;
936 =item CheckAccountLineLevelInfo
938 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
940 Check and Returns the list of all overdue books.
942 C<$exist> contains number of line in accounlines
943 with the same .biblionumber,itemnumber,accounttype,and notify_level
945 C<$borrowernumber> contains the borrower number
947 C<$itemnumber> contains item number
949 C<$accounttype> contains account type
951 C<$notify_level> contains the accountline level
956 sub CheckAccountLineLevelInfo {
957 my($borrowernumber,$itemnumber,$level) = @_;
958 my $dbh = C4::Context->dbh;
959 my $query= qq|SELECT count(*)
961 WHERE borrowernumber =?
964 my $sth=$dbh->prepare($query);
965 $sth->execute($borrowernumber,$itemnumber,$level);
966 my ($exist)=$sth->fetchrow;
971 =item GetOverduerules
973 ($overduerules) = &GetOverduerules($categorycode);
975 Returns the value of borrowers (debarred or not) with notify level
977 C<$overduerules> return value of debbraed field in overduerules table
979 C<$category> contains the borrower categorycode
981 C<$notify_level> contains the notify level
986 my($category,$notify_level) = @_;
987 my $dbh = C4::Context->dbh;
988 my $query=qq|SELECT debarred$notify_level
990 WHERE categorycode=?|;
991 my $sth=$dbh->prepare($query);
992 $sth->execute($category);
993 my ($overduerules)=$sth->fetchrow;
995 return($overduerules);
999 =item CheckBorrowerDebarred
1001 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
1003 Check if the borrowers is already debarred
1005 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
1007 C<$borrowernumber> contains the borrower number
1012 sub CheckBorrowerDebarred{
1013 my($borrowernumber) = @_;
1014 my $dbh = C4::Context->dbh;
1015 my $query=qq|SELECT debarred
1017 WHERE borrowernumber=?
1019 my $sth=$dbh->prepare($query);
1020 $sth->execute($borrowernumber);
1021 my ($debarredstatus)=$sth->fetchrow;
1023 if ($debarredstatus eq '1'){
1030 =item UpdateBorrowerDebarred
1032 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1034 update status of borrowers in borrowers table (field debarred)
1036 C<$borrowernumber> borrower number
1040 sub UpdateBorrowerDebarred{
1041 my($borrowernumber) = @_;
1042 my $dbh = C4::Context->dbh;
1043 my $query=qq|UPDATE borrowers
1045 WHERE borrowernumber=?
1047 my $sth=$dbh->prepare($query);
1048 $sth->execute($borrowernumber);
1053 =item CheckExistantNotifyid
1055 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1057 Check and Returns the notify id if exist else return 0.
1059 C<$exist> contains a notify_id
1061 C<$borrowernumber> contains the borrower number
1063 C<$date_due> contains the date of item return
1068 sub CheckExistantNotifyid {
1069 my($borrowernumber,$date_due) = @_;
1070 my $dbh = C4::Context->dbh;
1071 my $query = qq|SELECT notify_id FROM accountlines
1072 LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1073 WHERE accountlines.borrowernumber =?
1075 my $sth=$dbh->prepare($query);
1076 $sth->execute($borrowernumber,$date_due);
1077 my ($exist)=$sth->fetchrow;
1088 =item CheckAccountLineItemInfo
1090 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1092 Check and Returns the list of all overdue items from the same file number(notify_id).
1094 C<$exist> contains number of line in accounlines
1095 with the same .biblionumber,itemnumber,accounttype,notify_id
1097 C<$borrowernumber> contains the borrower number
1099 C<$itemnumber> contains item number
1101 C<$accounttype> contains account type
1103 C<$notify_id> contains the file number
1107 sub CheckAccountLineItemInfo {
1108 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1109 my $dbh = C4::Context->dbh;
1110 my $query = qq|SELECT count(*) FROM accountlines
1111 WHERE borrowernumber =?
1115 my $sth=$dbh->prepare($query);
1116 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1117 my ($exist)=$sth->fetchrow;
1122 =head2 CheckItemNotify
1124 Sql request to check if the document has alreday been notified
1125 this function is not exported, only used with GetOverduesForBranch
1129 sub CheckItemNotify {
1130 my ($notify_id,$notify_level,$itemnumber) = @_;
1131 my $dbh = C4::Context->dbh;
1132 my $sth = $dbh->prepare("
1133 SELECT COUNT(*) FROM notifys
1135 AND notify_level = ?
1136 AND itemnumber = ? ");
1137 $sth->execute($notify_id,$notify_level,$itemnumber);
1138 my $notified = $sth->fetchrow;
1143 =head2 GetOverduesForBranch
1145 Sql request for display all information for branchoverdues.pl
1146 2 possibilities : with or without location .
1147 display is filtered by branch
1151 sub GetOverduesForBranch {
1152 my ( $branch, $location) = @_;
1153 my $itype_link = (C4::context->preference('item-level_itype')) ? " items.ccode " : " biblioitems.itemtype ";
1154 if ( not $location ) {
1155 my $dbh = C4::Context->dbh;
1156 my $sth = $dbh->prepare("
1159 borrowers.firstname,
1161 itemtypes.description,
1164 branches.branchname,
1168 items.itemcallnumber,
1169 borrowers.borrowernumber,
1171 biblio.biblionumber,
1173 accountlines.notify_id,
1174 accountlines.notify_level,
1176 accountlines.amountoutstanding
1178 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1179 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1180 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1181 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1182 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1183 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1184 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1185 WHERE ( issues.returndate is null)
1186 AND ( accountlines.amountoutstanding != '0.000000')
1187 AND ( accountlines.accounttype = 'FU')
1188 AND (issues.branchcode = ?)
1189 AND (issues.date_due <= NOW())
1190 ORDER BY borrowers.surname
1192 $sth->execute($branch);
1195 while ( my $data = $sth->fetchrow_hashref ) {
1196 #check if the document has already been notified
1197 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1198 if ($countnotify eq '0'){
1199 $getoverdues[$i] = $data;
1203 return (@getoverdues);
1207 my $dbh = C4::Context->dbh;
1208 my $sth = $dbh->prepare( "
1209 SELECT borrowers.surname,
1210 borrowers.firstname,
1212 itemtypes.description,
1215 branches.branchname,
1219 items.itemcallnumber,
1220 borrowers.borrowernumber,
1222 biblio.biblionumber,
1224 accountlines.notify_id,
1225 accountlines.notify_level,
1227 accountlines.amountoutstanding
1229 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1230 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1231 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1232 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1233 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1234 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1235 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1236 WHERE ( issues.returndate is null )
1237 AND ( accountlines.amountoutstanding != '0.000000')
1238 AND ( accountlines.accounttype = 'FU')
1239 AND (issues.branchcode = ? AND items.location = ?)
1240 AND (issues.date_due <= NOW())
1241 ORDER BY borrowers.surname
1243 $sth->execute( $branch, $location);
1246 while ( my $data = $sth->fetchrow_hashref ) {
1247 #check if the document has already been notified
1248 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1249 if ($countnotify eq '0'){
1250 $getoverdues[$i] = $data;
1255 return (@getoverdues);
1260 =head2 AddNotifyLine
1262 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1264 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1269 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1270 if ( $method eq "phone" ) {
1271 my $dbh = C4::Context->dbh;
1272 my $sth = $dbh->prepare(
1273 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1274 VALUES (?,?,now(),now(),?,?,?)"
1276 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1281 my $dbh = C4::Context->dbh;
1282 my $sth = $dbh->prepare(
1283 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1284 VALUES (?,?,now(),?,?,?)"
1286 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1293 =head2 RemoveNotifyLine
1295 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1297 Cancel a notification
1301 sub RemoveNotifyLine {
1302 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1303 my $dbh = C4::Context->dbh;
1304 my $sth = $dbh->prepare(
1305 "DELETE FROM notifys
1311 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1323 Koha Developement team <info@koha.org>