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 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()
128 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.date_due < ?"
166 $sth->execute( $borrowernumber, $today );
167 while ( my $data = $sth->fetchrow_hashref ) {
168 push( @overdueitems, $data );
172 return ( $count, \@overdueitems );
177 ($amount, $chargename, $message) =
178 &CalcFine($itemnumber, $borrowercode, $days_overdue);
180 Calculates the fine for a book.
182 The issuingrules table in the Koha database is a fine matrix, listing
183 the penalties for each type of patron for each type of item and each branch (e.g., the
184 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
185 members might get a longer grace period between the first and second
186 reminders that a book is overdue).
188 The fine is calculated as follows: if it is time for the first
189 reminder, the fine is the value listed for the given (branch, item type,
190 borrower code) combination. If it is time for the second reminder, the
191 fine is doubled. Finally, if it is time to send the account to a
192 collection agency, the fine is set to 5 local monetary units (a really
193 good deal for the patron if the library is in Italy). Otherwise, the
196 Note that the way this function is currently implemented, it only
197 returns a nonzero value on the notable days listed above. That is, if
198 the categoryitems entry says to send a first reminder 7 days after the
199 book is due, then if you call C<&CalcFine> 7 days after the book is
200 due, it will give a nonzero fine. If you call C<&CalcFine> the next
201 day, however, it will say that the fine is 0.
203 C<$itemnumber> is the book's item number.
205 C<$borrowercode> is the borrower code of the patron who currently has
208 C<$days_overdue> is the number of days elapsed since the book's due
211 C<&CalcFine> returns a list of three values:
213 C<$amount> is the fine owed by the patron (see above).
215 C<$chargename> is the chargename field from the applicable record in
216 the categoryitem table, whatever that is.
218 C<$message> is a text message, either "First Notice", "Second Notice",
225 my ( $item, $bortype, $difference , $dues ) = @_;
226 my $dbh = C4::Context->dbh;
229 # calculate how many days the patron is late
230 my $countspecialday=&GetSpecialHolidays($dues,$item->{itemnumber});
231 my $countrepeatableday=&GetRepeatableHolidays($dues,$item->{itemnumber},$difference);
232 my $countalldayclosed = $countspecialday + $countrepeatableday;
233 my $daycount = $difference - $countalldayclosed;
234 # get issuingrules (fines part will be used)
235 my $data = GetIssuingRules($item->{'itemtype'},$bortype);
236 my $daycounttotal = $daycount - $data->{'firstremind'};
237 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
238 if ($data->{'firstremind'} < $daycount)
240 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
243 # get fines default rules
244 my $data = GetIssuingRules($item->{'itemtype'},'*');
245 $daycounttotal = $daycount - $data->{'firstremind'};
246 if ($data->{'firstremind'} < $daycount)
248 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
249 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
254 warn "Calc Fine for $item->{'itemnumber'}, $bortype, $difference , $dues = $amount / $daycount";
255 return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
259 =item GetSpecialHolidays
261 &GetSpecialHolidays($date_dues,$itemnumber);
263 return number of special days between date of the day and date due
265 C<$date_dues> is the envisaged date of book return.
267 C<$itemnumber> is the book's item number.
271 sub GetSpecialHolidays {
272 my ($date_dues,$itemnumber) = @_;
273 # calcul the today date
274 my $today = join "-", &Today();
276 # return the holdingbranch
277 my $iteminfo=GetIssuesIteminfo($itemnumber);
278 # use sql request to find all date between date_due and today
279 my $dbh = C4::Context->dbh;
280 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
281 FROM `special_holidays`
282 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
283 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
286 my @result=GetWdayFromItemnumber($itemnumber);
290 my $sth = $dbh->prepare($query);
291 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
293 while ( my $special_date=$sth->fetchrow_hashref){
294 push (@result_date,$special_date);
297 my $specialdaycount=scalar(@result_date);
299 for (my $i=0;$i<scalar(@result_date);$i++){
300 $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
301 (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
302 for (my $j=0;$j<scalar(@result);$j++){
303 if ($wday == ($result[$j]->{'weekday'})){
309 return $specialdaycount;
312 =item GetRepeatableHolidays
314 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
316 return number of day closed between date of the day and date due
318 C<$date_dues> is the envisaged date of book return.
320 C<$itemnumber> is item number.
322 C<$difference> numbers of between day date of the day and date due
326 sub GetRepeatableHolidays{
327 my ($date_dues,$itemnumber,$difference) = @_;
328 my $dateinsec=UnixDate($date_dues,"%o");
329 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
330 my @result=GetWdayFromItemnumber($itemnumber);
334 for (my $i=0;$i<scalar(@result);$i++){
337 for ( $j=0;$j<$difference;$j++){
338 if ($result[$i]->{'weekday'} == $k)
340 push ( @dayclosedcount ,$k);
346 return scalar(@dayclosedcount);
350 =item GetWayFromItemnumber
352 &Getwdayfromitemnumber($itemnumber);
354 return the different week day from repeatable_holidays table
356 C<$itemnumber> is item number.
360 sub GetWdayFromItemnumber{
362 my $iteminfo=GetIssuesIteminfo($itemnumber);
364 my $dbh = C4::Context->dbh;
365 my $query = qq|SELECT weekday
366 FROM repeatable_holidays
369 my $sth = $dbh->prepare($query);
372 $sth->execute($iteminfo->{'branchcode'});
373 while ( my $weekday=$sth->fetchrow_hashref){
374 push (@result,$weekday);
380 =item GetIssuesIteminfo
382 &GetIssuesIteminfo($itemnumber);
384 return all data from issues about item
386 C<$itemnumber> is item number.
390 sub GetIssuesIteminfo{
392 my $dbh = C4::Context->dbh;
393 my $query = qq|SELECT *
397 my $sth = $dbh->prepare($query);
398 $sth->execute($itemnumber);
399 my ($issuesinfo)=$sth->fetchrow_hashref;
406 &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
408 (Note: the following is mostly conjecture and guesswork.)
410 Updates the fine owed on an overdue book.
412 C<$itemnumber> is the book's item number.
414 C<$borrowernumber> is the borrower number of the patron who currently
415 has the book on loan.
417 C<$amount> is the current amount owed by the patron.
419 C<$type> will be used in the description of the fine.
421 C<$description> is a string that must be present in the description of
422 the fine. I think this is expected to be a date in DD/MM/YYYY format.
424 C<&UpdateFine> looks up the amount currently owed on the given item
425 and sets it to C<$amount>, creating, if necessary, a new entry in the
426 accountlines table of the Koha database.
431 # FIXME - This API doesn't look right: why should the caller have to
432 # specify both the item number and the borrower number? A book can't
433 # be on loan to two different people, so the item number should be
436 my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
437 my $dbh = C4::Context->dbh;
438 # FIXME - What exactly is this query supposed to do? It looks up an
439 # entry in accountlines that matches the given item and borrower
440 # numbers, where the description contains $due, and where the
441 # account type has one of several values, but what does this _mean_?
442 # Does it look up existing fines for this item?
443 # FIXME - What are these various account types? ("FU", "O", "F", "M")
444 my $sth = $dbh->prepare(
445 "Select * from accountlines where itemnumber=? and
446 borrowernumber=? and (accounttype='FU' or accounttype='O' or
447 accounttype='F' or accounttype='M') and description like ?"
449 $sth->execute( $itemnum, $borrowernumber, "%$due%" );
451 if ( my $data = $sth->fetchrow_hashref ) {
453 # I think this if-clause deals with the case where we're updating
455 # print "in accounts ...";
456 if ( $data->{'amount'} != $amount ) {
459 my $diff = $amount - $data->{'amount'};
460 my $out = $data->{'amountoutstanding'} + $diff;
461 my $sth2 = $dbh->prepare(
462 "UPDATE accountlines SET date=now(), amount=?,
463 amountoutstanding=?,accounttype='FU' WHERE
464 borrowernumber=? AND itemnumber=?
465 AND (accounttype='FU' OR accounttype='O') AND description LIKE ?"
467 $sth2->execute( $amount, $out, $data->{'borrowernumber'},
468 $data->{'itemnumber'}, "%$due%" );
473 # print "no update needed $data->{'amount'}"
478 # I think this else-clause deals with the case where we're adding
480 my $sth4 = $dbh->prepare(
481 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
483 $sth4->execute($itemnum);
484 my $title = $sth4->fetchrow_hashref;
487 # # print "not in account";
488 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
491 # # FIXME - Make $accountno a scalar.
492 # my @accountno = $sth3->fetchrow_array;
496 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
497 my $sth2 = $dbh->prepare(
498 "INSERT INTO accountlines
499 (borrowernumber,itemnumber,date,amount,
500 description,accounttype,amountoutstanding,accountno) VALUES
501 (?,?,now(),?,?,'FU',?,?)"
503 $sth2->execute( $borrowernumber, $itemnum, $amount,
504 "$type $title->{'title'} $due",
505 $amount, $nextaccntno);
513 "due=".$due." amount=".$amount." itemnumber=".$itemnum
514 ) if C4::Context->preference("FinesLog");
521 $borrower = &BorType($borrowernumber);
523 Looks up a patron by borrower number.
525 C<$borrower> is a reference-to-hash whose keys are all of the fields
526 from the borrowers and categories tables of the Koha database. Thus,
527 C<$borrower> contains all information about both the borrower and
528 category he or she belongs to.
534 my ($borrowernumber) = @_;
535 my $dbh = C4::Context->dbh;
536 my $sth = $dbh->prepare(
537 "SELECT * from borrowers
538 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
539 WHERE borrowernumber=?"
541 $sth->execute($borrowernumber);
542 my $data = $sth->fetchrow_hashref;
547 =item ReplacementCost
549 $cost = &ReplacementCost($itemnumber);
551 Returns the replacement cost of the item with the given item number.
556 sub ReplacementCost {
558 my $dbh = C4::Context->dbh;
560 $dbh->prepare("Select replacementprice from items where itemnumber=?");
561 $sth->execute($itemnum);
563 # FIXME - Use fetchrow_array or something.
564 my $data = $sth->fetchrow_hashref;
566 return ( $data->{'replacementprice'} );
571 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
573 return the total of fine
575 C<$itemnum> is item number
577 C<$borrowernumber> is the borrowernumber
583 my ( $itemnum, $borrowernumber ) = @_;
584 my $dbh = C4::Context->dbh();
585 my $query = "SELECT sum(amountoutstanding) FROM accountlines
586 where accounttype like 'F%'
587 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
588 my $sth = $dbh->prepare($query);
589 $sth->execute( $itemnum, $borrowernumber );
590 my $data = $sth->fetchrow_hashref();
593 return ( $data->{'sum(amountoutstanding)'} );
599 =item GetIssuingRules
601 $data = &GetIssuingRules($itemtype,$categorycode);
603 Looks up for all issuingrules an item info
605 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
606 from the borrowers and categories tables of the Koha database. Thus,
608 C<$categorycode> contains information about borrowers category
610 C<$data> contains all information about both the borrower and
611 category he or she belongs to.
614 sub GetIssuingRules {
615 my ($itemtype,$categorycode)=@_;
616 my $dbh = C4::Context->dbh();
617 my $query=qq|SELECT *
619 WHERE issuingrules.itemtype=?
620 AND issuingrules.categorycode=?
622 my $sth = $dbh->prepare($query);
624 $sth->execute($itemtype,$categorycode);
625 my ($data) = $sth->fetchrow_hashref;
632 sub ReplacementCost2 {
633 my ( $itemnum, $borrowernumber ) = @_;
634 my $dbh = C4::Context->dbh();
635 my $query = "SELECT amountoutstanding
637 WHERE accounttype like 'L'
638 AND amountoutstanding > 0
640 AND borrowernumber= ?";
641 my $sth = $dbh->prepare($query);
642 $sth->execute( $itemnum, $borrowernumber );
643 my $data = $sth->fetchrow_hashref();
646 return ( $data->{'amountoutstanding'} );
650 =item GetNextIdNotify
652 ($result) = &GetNextIdNotify($reference);
654 Returns the new file number
656 C<$result> contains the next file number
658 C<$reference> contains the beggining of file number
664 sub GetNextIdNotify {
666 my $query=qq|SELECT max(notify_id)
668 WHERE notify_id like \"$reference%\"
670 # AND borrowernumber=?|;
671 my $dbh = C4::Context->dbh;
672 my $sth=$dbh->prepare($query);
674 my $result=$sth->fetchrow;
679 ($result=$reference."01") ;
682 $count=substr($result,6)+1;
685 ($count = "0".$count);
687 $result=$reference.$count;
695 (@notify) = &NumberNotifyId($borrowernumber);
697 Returns amount for all file per borrowers
698 C<@notify> array contains all file per borrowers
700 C<$notify_id> contains the file number for the borrower number nad item number
705 my ($borrowernumber)=@_;
706 my $dbh = C4::Context->dbh;
707 my $query=qq| SELECT distinct(notify_id)
709 WHERE borrowernumber=?|;
711 my $sth=$dbh->prepare($query);
712 $sth->execute($borrowernumber);
713 while ( my ($numberofnotify)=$sth->fetchrow){
714 push (@notify,$numberofnotify);
724 ($totalnotify) = &AmountNotify($notifyid);
726 Returns amount for all file per borrowers
727 C<$notifyid> is the file number
729 C<$totalnotify> contains amount of a file
731 C<$notify_id> contains the file number for the borrower number and item number
736 my ($notifyid,$borrowernumber)=@_;
737 my $dbh = C4::Context->dbh;
738 my $query=qq| SELECT sum(amountoutstanding)
740 WHERE notify_id=? AND borrowernumber = ?|;
741 my $sth=$dbh->prepare($query);
742 $sth->execute($notifyid,$borrowernumber);
743 my $totalnotify=$sth->fetchrow;
745 return ($totalnotify);
751 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
753 Returns the file number per borrower and itemnumber
755 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
756 from the items tables of the Koha database. Thus,
758 C<$itemnumber> contains the borrower categorycode
760 C<$notify_id> contains the file number for the borrower number nad item number
765 my ($borrowernumber,$itemnumber)=@_;
766 my $query=qq|SELECT notify_id
768 WHERE borrowernumber=?
770 AND (accounttype='FU' or accounttype='O')|;
771 my $dbh = C4::Context->dbh;
772 my $sth=$dbh->prepare($query);
773 $sth->execute($borrowernumber,$itemnumber);
774 my ($notify_id)=$sth->fetchrow;
780 =item CreateItemAccountLine
782 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
784 update the account lines with file number or with file level
786 C<$items> is a reference-to-hash whose keys are all of the fields
787 from the items tables of the Koha database. Thus,
789 C<$itemnumber> contains the item number
791 C<$borrowernumber> contains the borrower number
793 C<$date> contains the date of the day
795 C<$amount> contains item price
797 C<$description> contains the descritpion of accounttype
799 C<$accounttype> contains the account type
801 C<$amountoutstanding> contains the $amountoutstanding
803 C<$timestamp> contains the timestamp with time and the date of the day
805 C<$notify_id> contains the file number
807 C<$level> contains the file level
812 sub CreateItemAccountLine {
813 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
814 my $dbh = C4::Context->dbh;
815 my $nextaccntno = getnextacctno($borrowernumber);
816 my $query= "INSERT into accountlines
817 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
819 (?,?,?,?,?,?,?,?,?,?,?)";
822 my $sth=$dbh->prepare($query);
823 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
827 =item UpdateAccountLines
829 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
831 update the account lines with file number or with file level
833 C<$items> is a reference-to-hash whose keys are all of the fields
834 from the items tables of the Koha database. Thus,
836 C<$itemnumber> contains the item number
838 C<$notify_id> contains the file number
840 C<$notify_level> contains the file level
842 C<$borrowernumber> contains the borrowernumber
846 sub UpdateAccountLines {
847 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
849 if ($notify_id eq '')
852 $query=qq|UPDATE accountlines
854 WHERE borrowernumber=? AND itemnumber=?
855 AND (accounttype='FU' or accounttype='O')|;
858 $query=qq|UPDATE accountlines
859 SET notify_id=?, notify_level=?
860 WHERE borrowernumber=?
862 AND (accounttype='FU' or accounttype='O')|;
864 my $dbh = C4::Context->dbh;
865 my $sth=$dbh->prepare($query);
867 if ($notify_id eq '')
869 $sth->execute($notify_level,$borrowernumber,$itemnumber);
872 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
881 ($items) = &GetItems($itemnumber);
883 Returns the list of all delays from overduerules.
885 C<$items> is a reference-to-hash whose keys are all of the fields
886 from the items tables of the Koha database. Thus,
888 C<$itemnumber> contains the borrower categorycode
893 my($itemnumber) = @_;
894 my $query=qq|SELECT *
897 my $dbh = C4::Context->dbh;
898 my $sth=$dbh->prepare($query);
899 $sth->execute($itemnumber);
900 my ($items)=$sth->fetchrow_hashref;
905 =item GetOverdueDelays
907 (@delays) = &GetOverdueDelays($categorycode);
909 Returns the list of all delays from overduerules.
911 C<@delays> it's an array contains the three delays from overduerules table
913 C<$categorycode> contains the borrower categorycode
917 sub GetOverdueDelays {
919 my $dbh = C4::Context->dbh;
920 my $query=qq|SELECT delay1,delay2,delay3
922 WHERE categorycode=?|;
923 my $sth=$dbh->prepare($query);
924 $sth->execute($category);
925 my (@delays)=$sth->fetchrow_array;
930 =item CheckAccountLineLevelInfo
932 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
934 Check and Returns the list of all overdue books.
936 C<$exist> contains number of line in accounlines
937 with the same .biblionumber,itemnumber,accounttype,and notify_level
939 C<$borrowernumber> contains the borrower number
941 C<$itemnumber> contains item number
943 C<$accounttype> contains account type
945 C<$notify_level> contains the accountline level
950 sub CheckAccountLineLevelInfo {
951 my($borrowernumber,$itemnumber,$level) = @_;
952 my $dbh = C4::Context->dbh;
953 my $query= qq|SELECT count(*)
955 WHERE borrowernumber =?
958 my $sth=$dbh->prepare($query);
959 $sth->execute($borrowernumber,$itemnumber,$level);
960 my ($exist)=$sth->fetchrow;
965 =item GetOverduerules
967 ($overduerules) = &GetOverduerules($categorycode);
969 Returns the value of borrowers (debarred or not) with notify level
971 C<$overduerules> return value of debbraed field in overduerules table
973 C<$category> contains the borrower categorycode
975 C<$notify_level> contains the notify level
980 my($category,$notify_level) = @_;
981 my $dbh = C4::Context->dbh;
982 my $query=qq|SELECT debarred$notify_level
984 WHERE categorycode=?|;
985 my $sth=$dbh->prepare($query);
986 $sth->execute($category);
987 my ($overduerules)=$sth->fetchrow;
989 return($overduerules);
993 =item CheckBorrowerDebarred
995 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
997 Check if the borrowers is already debarred
999 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
1001 C<$borrowernumber> contains the borrower number
1006 sub CheckBorrowerDebarred{
1007 my($borrowernumber) = @_;
1008 my $dbh = C4::Context->dbh;
1009 my $query=qq|SELECT debarred
1011 WHERE borrowernumber=?
1013 my $sth=$dbh->prepare($query);
1014 $sth->execute($borrowernumber);
1015 my ($debarredstatus)=$sth->fetchrow;
1017 if ($debarredstatus eq '1'){
1024 =item UpdateBorrowerDebarred
1026 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1028 update status of borrowers in borrowers table (field debarred)
1030 C<$borrowernumber> borrower number
1034 sub UpdateBorrowerDebarred{
1035 my($borrowernumber) = @_;
1036 my $dbh = C4::Context->dbh;
1037 my $query=qq|UPDATE borrowers
1039 WHERE borrowernumber=?
1041 my $sth=$dbh->prepare($query);
1042 $sth->execute($borrowernumber);
1047 =item CheckExistantNotifyid
1049 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1051 Check and Returns the notify id if exist else return 0.
1053 C<$exist> contains a notify_id
1055 C<$borrowernumber> contains the borrower number
1057 C<$date_due> contains the date of item return
1062 sub CheckExistantNotifyid {
1063 my($borrowernumber,$date_due) = @_;
1064 my $dbh = C4::Context->dbh;
1065 my $query = qq|SELECT notify_id FROM accountlines
1066 LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1067 WHERE accountlines.borrowernumber =?
1069 my $sth=$dbh->prepare($query);
1070 $sth->execute($borrowernumber,$date_due);
1071 my ($exist)=$sth->fetchrow;
1082 =item CheckAccountLineItemInfo
1084 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1086 Check and Returns the list of all overdue items from the same file number(notify_id).
1088 C<$exist> contains number of line in accounlines
1089 with the same .biblionumber,itemnumber,accounttype,notify_id
1091 C<$borrowernumber> contains the borrower number
1093 C<$itemnumber> contains item number
1095 C<$accounttype> contains account type
1097 C<$notify_id> contains the file number
1101 sub CheckAccountLineItemInfo {
1102 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1103 my $dbh = C4::Context->dbh;
1104 my $query = qq|SELECT count(*) FROM accountlines
1105 WHERE borrowernumber =?
1109 my $sth=$dbh->prepare($query);
1110 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1111 my ($exist)=$sth->fetchrow;
1116 =head2 CheckItemNotify
1118 Sql request to check if the document has alreday been notified
1119 this function is not exported, only used with GetOverduesForBranch
1123 sub CheckItemNotify {
1124 my ($notify_id,$notify_level,$itemnumber) = @_;
1125 my $dbh = C4::Context->dbh;
1126 my $sth = $dbh->prepare("
1127 SELECT COUNT(*) FROM notifys
1129 AND notify_level = ?
1130 AND itemnumber = ? ");
1131 $sth->execute($notify_id,$notify_level,$itemnumber);
1132 my $notified = $sth->fetchrow;
1137 =head2 GetOverduesForBranch
1139 Sql request for display all information for branchoverdues.pl
1140 2 possibilities : with or without location .
1141 display is filtered by branch
1145 sub GetOverduesForBranch {
1146 my ( $branch, $location) = @_;
1147 my $itype_link = (C4::Context->preference('item-level_itypes')) ? " items.itype " : " biblioitems.itemtype ";
1148 if ( not $location ) {
1149 my $dbh = C4::Context->dbh;
1150 my $sth = $dbh->prepare("
1153 borrowers.firstname,
1155 itemtypes.description,
1158 branches.branchname,
1162 items.itemcallnumber,
1163 borrowers.borrowernumber,
1165 biblio.biblionumber,
1167 accountlines.notify_id,
1168 accountlines.notify_level,
1170 accountlines.amountoutstanding
1172 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1173 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1174 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1175 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1176 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1177 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1178 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1179 WHERE ( accountlines.amountoutstanding != '0.000000')
1180 AND ( accountlines.accounttype = 'FU')
1181 AND (issues.branchcode = ?)
1182 AND (issues.date_due <= NOW())
1183 ORDER BY borrowers.surname
1185 $sth->execute($branch);
1188 while ( my $data = $sth->fetchrow_hashref ) {
1189 #check if the document has already been notified
1190 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1191 if ($countnotify eq '0'){
1192 $getoverdues[$i] = $data;
1196 return (@getoverdues);
1200 my $dbh = C4::Context->dbh;
1201 my $sth = $dbh->prepare( "
1202 SELECT borrowers.surname,
1203 borrowers.firstname,
1205 itemtypes.description,
1208 branches.branchname,
1212 items.itemcallnumber,
1213 borrowers.borrowernumber,
1215 biblio.biblionumber,
1217 accountlines.notify_id,
1218 accountlines.notify_level,
1220 accountlines.amountoutstanding
1222 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1223 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1224 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1225 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1226 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1227 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1228 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1229 WHERE ( accountlines.amountoutstanding != '0.000000')
1230 AND ( accountlines.accounttype = 'FU')
1231 AND (issues.branchcode = ? AND items.location = ?)
1232 AND (issues.date_due <= NOW())
1233 ORDER BY borrowers.surname
1235 $sth->execute( $branch, $location);
1238 while ( my $data = $sth->fetchrow_hashref ) {
1239 #check if the document has already been notified
1240 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1241 if ($countnotify eq '0'){
1242 $getoverdues[$i] = $data;
1247 return (@getoverdues);
1252 =head2 AddNotifyLine
1254 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1256 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1261 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1262 if ( $method eq "phone" ) {
1263 my $dbh = C4::Context->dbh;
1264 my $sth = $dbh->prepare(
1265 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1266 VALUES (?,?,now(),now(),?,?,?)"
1268 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1273 my $dbh = C4::Context->dbh;
1274 my $sth = $dbh->prepare(
1275 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1276 VALUES (?,?,now(),?,?,?)"
1278 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1285 =head2 RemoveNotifyLine
1287 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1289 Cancel a notification
1293 sub RemoveNotifyLine {
1294 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1295 my $dbh = C4::Context->dbh;
1296 my $sth = $dbh->prepare(
1297 "DELETE FROM notifys
1303 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1315 Koha Developement team <info@koha.org>