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
22 use Date::Calc qw/Today/;
23 use Date::Manip qw/UnixDate/;
26 use C4::Log; # logaction
28 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
35 # subs to rename (and maybe merge some...)
40 &CheckAccountLineLevelInfo
41 &CheckAccountLineItemInfo
42 &CheckExistantNotifyid
52 &CreateItemAccountLine
65 # check that an equivalent don't exist already before moving
67 # subs to move to Circulation.pm
72 # subs to move to Members.pm
74 &CheckBorrowerDebarred
75 &UpdateBorrowerDebarred
77 # subs to move to Biblio.pm
86 C4::Circulation::Fines - Koha module dealing with fines
94 This module contains several functions for dealing with fines for
95 overdue items. It is primarily used by the 'misc/fines2.pl' script.
103 ($overdues) = &Getoverdues();
105 Returns the list of all overdue books, with their itemtype.
107 C<$overdues> is a reference-to-array. Each element is a
108 reference-to-hash whose keys are the fields of the issues table in the
115 my $dbh = C4::Context->dbh;
116 my $sth = (C4::context->preference('item-level_itypes')) ?
118 "SELECT issues.*,items.itype as itemtype FROM issues
119 LEFT JOIN items USING (itemnumber)
120 WHERE date_due < now()
121 AND returndate IS NULL ORDER BY borrowernumber " )
124 "SELECT issues.*,biblioitems.itemtype,items.itype FROM issues
125 LEFT JOIN items USING (itemnumber)
126 LEFT JOIN biblioitems USING (biblioitemnumber)
127 WHERE date_due < now()
129 NULL ORDER BY borrowernumber " );
133 while ( my $data = $sth->fetchrow_hashref ) {
134 push @results, $data;
143 ( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh );
151 # From Main.pm, modified to return a list of overdueitems, in addition to a count
152 #checks whether a borrower has overdue items
153 my ( $borrowernumber, $dbh ) = @_;
154 my @datearr = localtime;
156 ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
159 my $sth = $dbh->prepare(
160 "SELECT * FROM issues
161 LEFT JOIN items ON issues.itemnumber = items.itemnumber
162 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
163 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
164 WHERE issues.borrowernumber = ?
165 AND issues.returndate is NULL
166 AND issues.date_due < ?"
168 $sth->execute( $borrowernumber, $today );
169 while ( my $data = $sth->fetchrow_hashref ) {
170 push( @overdueitems, $data );
174 return ( $count, \@overdueitems );
179 ($amount, $chargename, $message) =
180 &CalcFine($itemnumber, $borrowercode, $days_overdue);
182 Calculates the fine for a book.
184 The issuingrules table in the Koha database is a fine matrix, listing
185 the penalties for each type of patron for each type of item and each branch (e.g., the
186 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
187 members might get a longer grace period between the first and second
188 reminders that a book is overdue).
190 The fine is calculated as follows: if it is time for the first
191 reminder, the fine is the value listed for the given (branch, item type,
192 borrower code) combination. If it is time for the second reminder, the
193 fine is doubled. Finally, if it is time to send the account to a
194 collection agency, the fine is set to 5 local monetary units (a really
195 good deal for the patron if the library is in Italy). Otherwise, the
198 Note that the way this function is currently implemented, it only
199 returns a nonzero value on the notable days listed above. That is, if
200 the categoryitems entry says to send a first reminder 7 days after the
201 book is due, then if you call C<&CalcFine> 7 days after the book is
202 due, it will give a nonzero fine. If you call C<&CalcFine> the next
203 day, however, it will say that the fine is 0.
205 C<$itemnumber> is the book's item number.
207 C<$borrowercode> is the borrower code of the patron who currently has
210 C<$days_overdue> is the number of days elapsed since the book's due
213 C<&CalcFine> returns a list of three values:
215 C<$amount> is the fine owed by the patron (see above).
217 C<$chargename> is the chargename field from the applicable record in
218 the categoryitem table, whatever that is.
220 C<$message> is a text message, either "First Notice", "Second Notice",
227 my ( $item, $bortype, $difference , $dues ) = @_;
228 my $dbh = C4::Context->dbh;
231 # calculate how many days the patron is late
232 my $countspecialday=&GetSpecialHolidays($dues,$item->{itemnumber});
233 my $countrepeatableday=&GetRepeatableHolidays($dues,$item->{itemnumber},$difference);
234 my $countalldayclosed = $countspecialday + $countrepeatableday;
235 my $daycount = $difference - $countalldayclosed;
236 # get issuingrules (fines part will be used)
237 my $data = GetIssuingRules($item->{'itemtype'},$bortype);
238 my $daycounttotal = $daycount - $data->{'firstremind'};
239 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
240 if ($data->{'firstremind'} < $daycount)
242 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
245 # get fines default rules
246 my $data = GetIssuingRules($item->{'itemtype'},'*');
247 $daycounttotal = $daycount - $data->{'firstremind'};
248 if ($data->{'firstremind'} < $daycount)
250 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
251 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
256 warn "Calc Fine for $item->{'itemnumber'}, $bortype, $difference , $dues = $amount / $daycount";
257 return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
261 =item GetSpecialHolidays
263 &GetSpecialHolidays($date_dues,$itemnumber);
265 return number of special days between date of the day and date due
267 C<$date_dues> is the envisaged date of book return.
269 C<$itemnumber> is the book's item number.
273 sub GetSpecialHolidays {
274 my ($date_dues,$itemnumber) = @_;
275 # calcul the today date
276 my $today = join "-", &Today();
278 # return the holdingbranch
279 my $iteminfo=GetIssuesIteminfo($itemnumber);
280 # use sql request to find all date between date_due and today
281 my $dbh = C4::Context->dbh;
282 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
283 FROM `special_holidays`
284 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
285 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
288 my @result=GetWdayFromItemnumber($itemnumber);
292 my $sth = $dbh->prepare($query);
293 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
295 while ( my $special_date=$sth->fetchrow_hashref){
296 push (@result_date,$special_date);
299 my $specialdaycount=scalar(@result_date);
301 for (my $i=0;$i<scalar(@result_date);$i++){
302 $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
303 (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
304 for (my $j=0;$j<scalar(@result);$j++){
305 if ($wday == ($result[$j]->{'weekday'})){
311 return $specialdaycount;
314 =item GetRepeatableHolidays
316 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
318 return number of day closed between date of the day and date due
320 C<$date_dues> is the envisaged date of book return.
322 C<$itemnumber> is item number.
324 C<$difference> numbers of between day date of the day and date due
328 sub GetRepeatableHolidays{
329 my ($date_dues,$itemnumber,$difference) = @_;
330 my $dateinsec=UnixDate($date_dues,"%o");
331 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
332 my @result=GetWdayFromItemnumber($itemnumber);
336 for (my $i=0;$i<scalar(@result);$i++){
339 for ( $j=0;$j<$difference;$j++){
340 if ($result[$i]->{'weekday'} == $k)
342 push ( @dayclosedcount ,$k);
348 return scalar(@dayclosedcount);
352 =item GetWayFromItemnumber
354 &Getwdayfromitemnumber($itemnumber);
356 return the different week day from repeatable_holidays table
358 C<$itemnumber> is item number.
362 sub GetWdayFromItemnumber{
364 my $iteminfo=GetIssuesIteminfo($itemnumber);
366 my $dbh = C4::Context->dbh;
367 my $query = qq|SELECT weekday
368 FROM repeatable_holidays
371 my $sth = $dbh->prepare($query);
374 $sth->execute($iteminfo->{'branchcode'});
375 while ( my $weekday=$sth->fetchrow_hashref){
376 push (@result,$weekday);
382 =item GetIssuesIteminfo
384 &GetIssuesIteminfo($itemnumber);
386 return all data from issues about item
388 C<$itemnumber> is item number.
392 sub GetIssuesIteminfo{
394 my $dbh = C4::Context->dbh;
395 my $query = qq|SELECT *
399 my $sth = $dbh->prepare($query);
400 $sth->execute($itemnumber);
401 my ($issuesinfo)=$sth->fetchrow_hashref;
408 &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
410 (Note: the following is mostly conjecture and guesswork.)
412 Updates the fine owed on an overdue book.
414 C<$itemnumber> is the book's item number.
416 C<$borrowernumber> is the borrower number of the patron who currently
417 has the book on loan.
419 C<$amount> is the current amount owed by the patron.
421 C<$type> will be used in the description of the fine.
423 C<$description> is a string that must be present in the description of
424 the fine. I think this is expected to be a date in DD/MM/YYYY format.
426 C<&UpdateFine> looks up the amount currently owed on the given item
427 and sets it to C<$amount>, creating, if necessary, a new entry in the
428 accountlines table of the Koha database.
433 # FIXME - This API doesn't look right: why should the caller have to
434 # specify both the item number and the borrower number? A book can't
435 # be on loan to two different people, so the item number should be
438 my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
439 my $dbh = C4::Context->dbh;
440 # FIXME - What exactly is this query supposed to do? It looks up an
441 # entry in accountlines that matches the given item and borrower
442 # numbers, where the description contains $due, and where the
443 # account type has one of several values, but what does this _mean_?
444 # Does it look up existing fines for this item?
445 # FIXME - What are these various account types? ("FU", "O", "F", "M")
446 my $sth = $dbh->prepare(
447 "Select * from accountlines where itemnumber=? and
448 borrowernumber=? and (accounttype='FU' or accounttype='O' or
449 accounttype='F' or accounttype='M') and description like ?"
451 $sth->execute( $itemnum, $borrowernumber, "%$due%" );
453 if ( my $data = $sth->fetchrow_hashref ) {
455 # I think this if-clause deals with the case where we're updating
457 # print "in accounts ...";
458 if ( $data->{'amount'} != $amount ) {
461 my $diff = $amount - $data->{'amount'};
462 my $out = $data->{'amountoutstanding'} + $diff;
463 my $sth2 = $dbh->prepare(
464 "UPDATE accountlines SET date=now(), amount=?,
465 amountoutstanding=?,accounttype='FU' WHERE
466 borrowernumber=? AND itemnumber=?
467 AND (accounttype='FU' OR accounttype='O') AND description LIKE ?"
469 $sth2->execute( $amount, $out, $data->{'borrowernumber'},
470 $data->{'itemnumber'}, "%$due%" );
475 # print "no update needed $data->{'amount'}"
480 # I think this else-clause deals with the case where we're adding
482 my $sth4 = $dbh->prepare(
483 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
485 $sth4->execute($itemnum);
486 my $title = $sth4->fetchrow_hashref;
489 # # print "not in account";
490 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
493 # # FIXME - Make $accountno a scalar.
494 # my @accountno = $sth3->fetchrow_array;
498 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
499 my $sth2 = $dbh->prepare(
500 "INSERT INTO accountlines
501 (borrowernumber,itemnumber,date,amount,
502 description,accounttype,amountoutstanding,accountno) VALUES
503 (?,?,now(),?,?,'FU',?,?)"
505 $sth2->execute( $borrowernumber, $itemnum, $amount,
506 "$type $title->{'title'} $due",
507 $amount, $nextaccntno);
512 C4::Context->userenv->{'number'},
516 "due=".$due." amount=".$amount." itemnumber=".$itemnum
517 ) if C4::Context->preference("FinesLog");
524 $borrower = &BorType($borrowernumber);
526 Looks up a patron by borrower number.
528 C<$borrower> is a reference-to-hash whose keys are all of the fields
529 from the borrowers and categories tables of the Koha database. Thus,
530 C<$borrower> contains all information about both the borrower and
531 category he or she belongs to.
537 my ($borrowernumber) = @_;
538 my $dbh = C4::Context->dbh;
539 my $sth = $dbh->prepare(
540 "SELECT * from borrowers
541 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
542 WHERE borrowernumber=?"
544 $sth->execute($borrowernumber);
545 my $data = $sth->fetchrow_hashref;
550 =item ReplacementCost
552 $cost = &ReplacementCost($itemnumber);
554 Returns the replacement cost of the item with the given item number.
559 sub ReplacementCost {
561 my $dbh = C4::Context->dbh;
563 $dbh->prepare("Select replacementprice from items where itemnumber=?");
564 $sth->execute($itemnum);
566 # FIXME - Use fetchrow_array or something.
567 my $data = $sth->fetchrow_hashref;
569 return ( $data->{'replacementprice'} );
574 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
576 return the total of fine
578 C<$itemnum> is item number
580 C<$borrowernumber> is the borrowernumber
586 my ( $itemnum, $borrowernumber ) = @_;
587 my $dbh = C4::Context->dbh();
588 my $query = "SELECT sum(amountoutstanding) FROM accountlines
589 where accounttype like 'F%'
590 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
591 my $sth = $dbh->prepare($query);
592 $sth->execute( $itemnum, $borrowernumber );
593 my $data = $sth->fetchrow_hashref();
596 return ( $data->{'sum(amountoutstanding)'} );
602 =item GetIssuingRules
604 $data = &GetIssuingRules($itemtype,$categorycode);
606 Looks up for all issuingrules an item info
608 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
609 from the borrowers and categories tables of the Koha database. Thus,
611 C<$categorycode> contains information about borrowers category
613 C<$data> contains all information about both the borrower and
614 category he or she belongs to.
617 sub GetIssuingRules {
618 my ($itemtype,$categorycode)=@_;
619 my $dbh = C4::Context->dbh();
620 my $query=qq|SELECT *
622 WHERE issuingrules.itemtype=?
623 AND issuingrules.categorycode=?
625 my $sth = $dbh->prepare($query);
627 $sth->execute($itemtype,$categorycode);
628 my ($data) = $sth->fetchrow_hashref;
635 sub ReplacementCost2 {
636 my ( $itemnum, $borrowernumber ) = @_;
637 my $dbh = C4::Context->dbh();
638 my $query = "SELECT amountoutstanding
640 WHERE accounttype like 'L'
641 AND amountoutstanding > 0
643 AND borrowernumber= ?";
644 my $sth = $dbh->prepare($query);
645 $sth->execute( $itemnum, $borrowernumber );
646 my $data = $sth->fetchrow_hashref();
649 return ( $data->{'amountoutstanding'} );
653 =item GetNextIdNotify
655 ($result) = &GetNextIdNotify($reference);
657 Returns the new file number
659 C<$result> contains the next file number
661 C<$reference> contains the beggining of file number
667 sub GetNextIdNotify {
669 my $query=qq|SELECT max(notify_id)
671 WHERE notify_id like \"$reference%\"
673 # AND borrowernumber=?|;
674 my $dbh = C4::Context->dbh;
675 my $sth=$dbh->prepare($query);
677 my $result=$sth->fetchrow;
682 ($result=$reference."01") ;
685 $count=substr($result,6)+1;
688 ($count = "0".$count);
690 $result=$reference.$count;
698 (@notify) = &NumberNotifyId($borrowernumber);
700 Returns amount for all file per borrowers
701 C<@notify> array contains all file per borrowers
703 C<$notify_id> contains the file number for the borrower number nad item number
708 my ($borrowernumber)=@_;
709 my $dbh = C4::Context->dbh;
710 my $query=qq| SELECT distinct(notify_id)
712 WHERE borrowernumber=?|;
714 my $sth=$dbh->prepare($query);
715 $sth->execute($borrowernumber);
716 while ( my ($numberofnotify)=$sth->fetchrow){
717 push (@notify,$numberofnotify);
727 ($totalnotify) = &AmountNotify($notifyid);
729 Returns amount for all file per borrowers
730 C<$notifyid> is the file number
732 C<$totalnotify> contains amount of a file
734 C<$notify_id> contains the file number for the borrower number and item number
739 my ($notifyid,$borrowernumber)=@_;
740 my $dbh = C4::Context->dbh;
741 my $query=qq| SELECT sum(amountoutstanding)
743 WHERE notify_id=? AND borrowernumber = ?|;
744 my $sth=$dbh->prepare($query);
745 $sth->execute($notifyid,$borrowernumber);
746 my $totalnotify=$sth->fetchrow;
748 return ($totalnotify);
754 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
756 Returns the file number per borrower and itemnumber
758 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
759 from the items tables of the Koha database. Thus,
761 C<$itemnumber> contains the borrower categorycode
763 C<$notify_id> contains the file number for the borrower number nad item number
768 my ($borrowernumber,$itemnumber)=@_;
769 my $query=qq|SELECT notify_id
771 WHERE borrowernumber=?
773 AND (accounttype='FU' or accounttype='O')|;
774 my $dbh = C4::Context->dbh;
775 my $sth=$dbh->prepare($query);
776 $sth->execute($borrowernumber,$itemnumber);
777 my ($notify_id)=$sth->fetchrow;
783 =item CreateItemAccountLine
785 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
787 update the account lines with file number or with file level
789 C<$items> is a reference-to-hash whose keys are all of the fields
790 from the items tables of the Koha database. Thus,
792 C<$itemnumber> contains the item number
794 C<$borrowernumber> contains the borrower number
796 C<$date> contains the date of the day
798 C<$amount> contains item price
800 C<$description> contains the descritpion of accounttype
802 C<$accounttype> contains the account type
804 C<$amountoutstanding> contains the $amountoutstanding
806 C<$timestamp> contains the timestamp with time and the date of the day
808 C<$notify_id> contains the file number
810 C<$level> contains the file level
815 sub CreateItemAccountLine {
816 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
817 my $dbh = C4::Context->dbh;
818 my $nextaccntno = getnextacctno($borrowernumber);
819 my $query= "INSERT into accountlines
820 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
822 (?,?,?,?,?,?,?,?,?,?,?)";
825 my $sth=$dbh->prepare($query);
826 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
830 =item UpdateAccountLines
832 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
834 update the account lines with file number or with file level
836 C<$items> is a reference-to-hash whose keys are all of the fields
837 from the items tables of the Koha database. Thus,
839 C<$itemnumber> contains the item number
841 C<$notify_id> contains the file number
843 C<$notify_level> contains the file level
845 C<$borrowernumber> contains the borrowernumber
849 sub UpdateAccountLines {
850 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
852 if ($notify_id eq '')
855 $query=qq|UPDATE accountlines
857 WHERE borrowernumber=? AND itemnumber=?
858 AND (accounttype='FU' or accounttype='O')|;
861 $query=qq|UPDATE accountlines
862 SET notify_id=?, notify_level=?
863 WHERE borrowernumber=?
865 AND (accounttype='FU' or accounttype='O')|;
867 my $dbh = C4::Context->dbh;
868 my $sth=$dbh->prepare($query);
870 if ($notify_id eq '')
872 $sth->execute($notify_level,$borrowernumber,$itemnumber);
875 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
884 ($items) = &GetItems($itemnumber);
886 Returns the list of all delays from overduerules.
888 C<$items> is a reference-to-hash whose keys are all of the fields
889 from the items tables of the Koha database. Thus,
891 C<$itemnumber> contains the borrower categorycode
896 my($itemnumber) = @_;
897 my $query=qq|SELECT *
900 my $dbh = C4::Context->dbh;
901 my $sth=$dbh->prepare($query);
902 $sth->execute($itemnumber);
903 my ($items)=$sth->fetchrow_hashref;
908 =item GetOverdueDelays
910 (@delays) = &GetOverdueDelays($categorycode);
912 Returns the list of all delays from overduerules.
914 C<@delays> it's an array contains the three delays from overduerules table
916 C<$categorycode> contains the borrower categorycode
920 sub GetOverdueDelays {
922 my $dbh = C4::Context->dbh;
923 my $query=qq|SELECT delay1,delay2,delay3
925 WHERE categorycode=?|;
926 my $sth=$dbh->prepare($query);
927 $sth->execute($category);
928 my (@delays)=$sth->fetchrow_array;
933 =item CheckAccountLineLevelInfo
935 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
937 Check and Returns the list of all overdue books.
939 C<$exist> contains number of line in accounlines
940 with the same .biblionumber,itemnumber,accounttype,and notify_level
942 C<$borrowernumber> contains the borrower number
944 C<$itemnumber> contains item number
946 C<$accounttype> contains account type
948 C<$notify_level> contains the accountline level
953 sub CheckAccountLineLevelInfo {
954 my($borrowernumber,$itemnumber,$level) = @_;
955 my $dbh = C4::Context->dbh;
956 my $query= qq|SELECT count(*)
958 WHERE borrowernumber =?
961 my $sth=$dbh->prepare($query);
962 $sth->execute($borrowernumber,$itemnumber,$level);
963 my ($exist)=$sth->fetchrow;
968 =item GetOverduerules
970 ($overduerules) = &GetOverduerules($categorycode);
972 Returns the value of borrowers (debarred or not) with notify level
974 C<$overduerules> return value of debbraed field in overduerules table
976 C<$category> contains the borrower categorycode
978 C<$notify_level> contains the notify level
983 my($category,$notify_level) = @_;
984 my $dbh = C4::Context->dbh;
985 my $query=qq|SELECT debarred$notify_level
987 WHERE categorycode=?|;
988 my $sth=$dbh->prepare($query);
989 $sth->execute($category);
990 my ($overduerules)=$sth->fetchrow;
992 return($overduerules);
996 =item CheckBorrowerDebarred
998 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
1000 Check if the borrowers is already debarred
1002 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
1004 C<$borrowernumber> contains the borrower number
1009 sub CheckBorrowerDebarred{
1010 my($borrowernumber) = @_;
1011 my $dbh = C4::Context->dbh;
1012 my $query=qq|SELECT debarred
1014 WHERE borrowernumber=?
1016 my $sth=$dbh->prepare($query);
1017 $sth->execute($borrowernumber);
1018 my ($debarredstatus)=$sth->fetchrow;
1020 if ($debarredstatus eq '1'){
1027 =item UpdateBorrowerDebarred
1029 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1031 update status of borrowers in borrowers table (field debarred)
1033 C<$borrowernumber> borrower number
1037 sub UpdateBorrowerDebarred{
1038 my($borrowernumber) = @_;
1039 my $dbh = C4::Context->dbh;
1040 my $query=qq|UPDATE borrowers
1042 WHERE borrowernumber=?
1044 my $sth=$dbh->prepare($query);
1045 $sth->execute($borrowernumber);
1050 =item CheckExistantNotifyid
1052 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1054 Check and Returns the notify id if exist else return 0.
1056 C<$exist> contains a notify_id
1058 C<$borrowernumber> contains the borrower number
1060 C<$date_due> contains the date of item return
1065 sub CheckExistantNotifyid {
1066 my($borrowernumber,$date_due) = @_;
1067 my $dbh = C4::Context->dbh;
1068 my $query = qq|SELECT notify_id FROM accountlines
1069 LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1070 WHERE accountlines.borrowernumber =?
1072 my $sth=$dbh->prepare($query);
1073 $sth->execute($borrowernumber,$date_due);
1074 my ($exist)=$sth->fetchrow;
1085 =item CheckAccountLineItemInfo
1087 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1089 Check and Returns the list of all overdue items from the same file number(notify_id).
1091 C<$exist> contains number of line in accounlines
1092 with the same .biblionumber,itemnumber,accounttype,notify_id
1094 C<$borrowernumber> contains the borrower number
1096 C<$itemnumber> contains item number
1098 C<$accounttype> contains account type
1100 C<$notify_id> contains the file number
1104 sub CheckAccountLineItemInfo {
1105 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1106 my $dbh = C4::Context->dbh;
1107 my $query = qq|SELECT count(*) FROM accountlines
1108 WHERE borrowernumber =?
1112 my $sth=$dbh->prepare($query);
1113 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1114 my ($exist)=$sth->fetchrow;
1119 =head2 CheckItemNotify
1121 Sql request to check if the document has alreday been notified
1122 this function is not exported, only used with GetOverduesForBranch
1126 sub CheckItemNotify {
1127 my ($notify_id,$notify_level,$itemnumber) = @_;
1128 my $dbh = C4::Context->dbh;
1129 my $sth = $dbh->prepare("
1130 SELECT COUNT(*) FROM notifys
1132 AND notify_level = ?
1133 AND itemnumber = ? ");
1134 $sth->execute($notify_id,$notify_level,$itemnumber);
1135 my $notified = $sth->fetchrow;
1140 =head2 GetOverduesForBranch
1142 Sql request for display all information for branchoverdues.pl
1143 2 possibilities : with or without location .
1144 display is filtered by branch
1148 sub GetOverduesForBranch {
1149 my ( $branch, $location) = @_;
1150 my $itype_link = (C4::Context->preference('item-level_itypes')) ? " items.itype " : " biblioitems.itemtype ";
1151 if ( not $location ) {
1152 my $dbh = C4::Context->dbh;
1153 my $sth = $dbh->prepare("
1156 borrowers.firstname,
1158 itemtypes.description,
1161 branches.branchname,
1165 items.itemcallnumber,
1166 borrowers.borrowernumber,
1168 biblio.biblionumber,
1170 accountlines.notify_id,
1171 accountlines.notify_level,
1173 accountlines.amountoutstanding
1175 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1176 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1177 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1178 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1179 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1180 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1181 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1182 WHERE ( issues.returndate is null)
1183 AND ( accountlines.amountoutstanding != '0.000000')
1184 AND ( accountlines.accounttype = 'FU')
1185 AND (issues.branchcode = ?)
1186 AND (issues.date_due <= NOW())
1187 ORDER BY borrowers.surname
1189 $sth->execute($branch);
1192 while ( my $data = $sth->fetchrow_hashref ) {
1193 #check if the document has already been notified
1194 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1195 if ($countnotify eq '0'){
1196 $getoverdues[$i] = $data;
1200 return (@getoverdues);
1204 my $dbh = C4::Context->dbh;
1205 my $sth = $dbh->prepare( "
1206 SELECT borrowers.surname,
1207 borrowers.firstname,
1209 itemtypes.description,
1212 branches.branchname,
1216 items.itemcallnumber,
1217 borrowers.borrowernumber,
1219 biblio.biblionumber,
1221 accountlines.notify_id,
1222 accountlines.notify_level,
1224 accountlines.amountoutstanding
1226 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1227 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1228 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1229 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1230 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1231 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1232 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1233 WHERE ( issues.returndate is null )
1234 AND ( accountlines.amountoutstanding != '0.000000')
1235 AND ( accountlines.accounttype = 'FU')
1236 AND (issues.branchcode = ? AND items.location = ?)
1237 AND (issues.date_due <= NOW())
1238 ORDER BY borrowers.surname
1240 $sth->execute( $branch, $location);
1243 while ( my $data = $sth->fetchrow_hashref ) {
1244 #check if the document has already been notified
1245 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1246 if ($countnotify eq '0'){
1247 $getoverdues[$i] = $data;
1252 return (@getoverdues);
1257 =head2 AddNotifyLine
1259 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1261 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1266 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1267 if ( $method eq "phone" ) {
1268 my $dbh = C4::Context->dbh;
1269 my $sth = $dbh->prepare(
1270 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1271 VALUES (?,?,now(),now(),?,?,?)"
1273 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1278 my $dbh = C4::Context->dbh;
1279 my $sth = $dbh->prepare(
1280 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1281 VALUES (?,?,now(),?,?,?)"
1283 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1290 =head2 RemoveNotifyLine
1292 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1294 Cancel a notification
1298 sub RemoveNotifyLine {
1299 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1300 my $dbh = C4::Context->dbh;
1301 my $sth = $dbh->prepare(
1302 "DELETE FROM notifys
1308 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1320 Koha Developement team <info@koha.org>