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);
510 C4::Context->userenv->{'number'},
514 "due=".$due." amount=".$amount." itemnumber=".$itemnum
515 ) if C4::Context->preference("FinesLog");
522 $borrower = &BorType($borrowernumber);
524 Looks up a patron by borrower number.
526 C<$borrower> is a reference-to-hash whose keys are all of the fields
527 from the borrowers and categories tables of the Koha database. Thus,
528 C<$borrower> contains all information about both the borrower and
529 category he or she belongs to.
535 my ($borrowernumber) = @_;
536 my $dbh = C4::Context->dbh;
537 my $sth = $dbh->prepare(
538 "SELECT * from borrowers
539 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
540 WHERE borrowernumber=?"
542 $sth->execute($borrowernumber);
543 my $data = $sth->fetchrow_hashref;
548 =item ReplacementCost
550 $cost = &ReplacementCost($itemnumber);
552 Returns the replacement cost of the item with the given item number.
557 sub ReplacementCost {
559 my $dbh = C4::Context->dbh;
561 $dbh->prepare("Select replacementprice from items where itemnumber=?");
562 $sth->execute($itemnum);
564 # FIXME - Use fetchrow_array or something.
565 my $data = $sth->fetchrow_hashref;
567 return ( $data->{'replacementprice'} );
572 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
574 return the total of fine
576 C<$itemnum> is item number
578 C<$borrowernumber> is the borrowernumber
584 my ( $itemnum, $borrowernumber ) = @_;
585 my $dbh = C4::Context->dbh();
586 my $query = "SELECT sum(amountoutstanding) FROM accountlines
587 where accounttype like 'F%'
588 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
589 my $sth = $dbh->prepare($query);
590 $sth->execute( $itemnum, $borrowernumber );
591 my $data = $sth->fetchrow_hashref();
594 return ( $data->{'sum(amountoutstanding)'} );
600 =item GetIssuingRules
602 $data = &GetIssuingRules($itemtype,$categorycode);
604 Looks up for all issuingrules an item info
606 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
607 from the borrowers and categories tables of the Koha database. Thus,
609 C<$categorycode> contains information about borrowers category
611 C<$data> contains all information about both the borrower and
612 category he or she belongs to.
615 sub GetIssuingRules {
616 my ($itemtype,$categorycode)=@_;
617 my $dbh = C4::Context->dbh();
618 my $query=qq|SELECT *
620 WHERE issuingrules.itemtype=?
621 AND issuingrules.categorycode=?
623 my $sth = $dbh->prepare($query);
625 $sth->execute($itemtype,$categorycode);
626 my ($data) = $sth->fetchrow_hashref;
633 sub ReplacementCost2 {
634 my ( $itemnum, $borrowernumber ) = @_;
635 my $dbh = C4::Context->dbh();
636 my $query = "SELECT amountoutstanding
638 WHERE accounttype like 'L'
639 AND amountoutstanding > 0
641 AND borrowernumber= ?";
642 my $sth = $dbh->prepare($query);
643 $sth->execute( $itemnum, $borrowernumber );
644 my $data = $sth->fetchrow_hashref();
647 return ( $data->{'amountoutstanding'} );
651 =item GetNextIdNotify
653 ($result) = &GetNextIdNotify($reference);
655 Returns the new file number
657 C<$result> contains the next file number
659 C<$reference> contains the beggining of file number
665 sub GetNextIdNotify {
667 my $query=qq|SELECT max(notify_id)
669 WHERE notify_id like \"$reference%\"
671 # AND borrowernumber=?|;
672 my $dbh = C4::Context->dbh;
673 my $sth=$dbh->prepare($query);
675 my $result=$sth->fetchrow;
680 ($result=$reference."01") ;
683 $count=substr($result,6)+1;
686 ($count = "0".$count);
688 $result=$reference.$count;
696 (@notify) = &NumberNotifyId($borrowernumber);
698 Returns amount for all file per borrowers
699 C<@notify> array contains all file per borrowers
701 C<$notify_id> contains the file number for the borrower number nad item number
706 my ($borrowernumber)=@_;
707 my $dbh = C4::Context->dbh;
708 my $query=qq| SELECT distinct(notify_id)
710 WHERE borrowernumber=?|;
712 my $sth=$dbh->prepare($query);
713 $sth->execute($borrowernumber);
714 while ( my ($numberofnotify)=$sth->fetchrow){
715 push (@notify,$numberofnotify);
725 ($totalnotify) = &AmountNotify($notifyid);
727 Returns amount for all file per borrowers
728 C<$notifyid> is the file number
730 C<$totalnotify> contains amount of a file
732 C<$notify_id> contains the file number for the borrower number and item number
737 my ($notifyid,$borrowernumber)=@_;
738 my $dbh = C4::Context->dbh;
739 my $query=qq| SELECT sum(amountoutstanding)
741 WHERE notify_id=? AND borrowernumber = ?|;
742 my $sth=$dbh->prepare($query);
743 $sth->execute($notifyid,$borrowernumber);
744 my $totalnotify=$sth->fetchrow;
746 return ($totalnotify);
752 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
754 Returns the file number per borrower and itemnumber
756 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
757 from the items tables of the Koha database. Thus,
759 C<$itemnumber> contains the borrower categorycode
761 C<$notify_id> contains the file number for the borrower number nad item number
766 my ($borrowernumber,$itemnumber)=@_;
767 my $query=qq|SELECT notify_id
769 WHERE borrowernumber=?
771 AND (accounttype='FU' or accounttype='O')|;
772 my $dbh = C4::Context->dbh;
773 my $sth=$dbh->prepare($query);
774 $sth->execute($borrowernumber,$itemnumber);
775 my ($notify_id)=$sth->fetchrow;
781 =item CreateItemAccountLine
783 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
785 update the account lines with file number or with file level
787 C<$items> is a reference-to-hash whose keys are all of the fields
788 from the items tables of the Koha database. Thus,
790 C<$itemnumber> contains the item number
792 C<$borrowernumber> contains the borrower number
794 C<$date> contains the date of the day
796 C<$amount> contains item price
798 C<$description> contains the descritpion of accounttype
800 C<$accounttype> contains the account type
802 C<$amountoutstanding> contains the $amountoutstanding
804 C<$timestamp> contains the timestamp with time and the date of the day
806 C<$notify_id> contains the file number
808 C<$level> contains the file level
813 sub CreateItemAccountLine {
814 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
815 my $dbh = C4::Context->dbh;
816 my $nextaccntno = getnextacctno($borrowernumber);
817 my $query= "INSERT into accountlines
818 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
820 (?,?,?,?,?,?,?,?,?,?,?)";
823 my $sth=$dbh->prepare($query);
824 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
828 =item UpdateAccountLines
830 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
832 update the account lines with file number or with file level
834 C<$items> is a reference-to-hash whose keys are all of the fields
835 from the items tables of the Koha database. Thus,
837 C<$itemnumber> contains the item number
839 C<$notify_id> contains the file number
841 C<$notify_level> contains the file level
843 C<$borrowernumber> contains the borrowernumber
847 sub UpdateAccountLines {
848 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
850 if ($notify_id eq '')
853 $query=qq|UPDATE accountlines
855 WHERE borrowernumber=? AND itemnumber=?
856 AND (accounttype='FU' or accounttype='O')|;
859 $query=qq|UPDATE accountlines
860 SET notify_id=?, notify_level=?
861 WHERE borrowernumber=?
863 AND (accounttype='FU' or accounttype='O')|;
865 my $dbh = C4::Context->dbh;
866 my $sth=$dbh->prepare($query);
868 if ($notify_id eq '')
870 $sth->execute($notify_level,$borrowernumber,$itemnumber);
873 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
882 ($items) = &GetItems($itemnumber);
884 Returns the list of all delays from overduerules.
886 C<$items> is a reference-to-hash whose keys are all of the fields
887 from the items tables of the Koha database. Thus,
889 C<$itemnumber> contains the borrower categorycode
894 my($itemnumber) = @_;
895 my $query=qq|SELECT *
898 my $dbh = C4::Context->dbh;
899 my $sth=$dbh->prepare($query);
900 $sth->execute($itemnumber);
901 my ($items)=$sth->fetchrow_hashref;
906 =item GetOverdueDelays
908 (@delays) = &GetOverdueDelays($categorycode);
910 Returns the list of all delays from overduerules.
912 C<@delays> it's an array contains the three delays from overduerules table
914 C<$categorycode> contains the borrower categorycode
918 sub GetOverdueDelays {
920 my $dbh = C4::Context->dbh;
921 my $query=qq|SELECT delay1,delay2,delay3
923 WHERE categorycode=?|;
924 my $sth=$dbh->prepare($query);
925 $sth->execute($category);
926 my (@delays)=$sth->fetchrow_array;
931 =item CheckAccountLineLevelInfo
933 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
935 Check and Returns the list of all overdue books.
937 C<$exist> contains number of line in accounlines
938 with the same .biblionumber,itemnumber,accounttype,and notify_level
940 C<$borrowernumber> contains the borrower number
942 C<$itemnumber> contains item number
944 C<$accounttype> contains account type
946 C<$notify_level> contains the accountline level
951 sub CheckAccountLineLevelInfo {
952 my($borrowernumber,$itemnumber,$level) = @_;
953 my $dbh = C4::Context->dbh;
954 my $query= qq|SELECT count(*)
956 WHERE borrowernumber =?
959 my $sth=$dbh->prepare($query);
960 $sth->execute($borrowernumber,$itemnumber,$level);
961 my ($exist)=$sth->fetchrow;
966 =item GetOverduerules
968 ($overduerules) = &GetOverduerules($categorycode);
970 Returns the value of borrowers (debarred or not) with notify level
972 C<$overduerules> return value of debbraed field in overduerules table
974 C<$category> contains the borrower categorycode
976 C<$notify_level> contains the notify level
981 my($category,$notify_level) = @_;
982 my $dbh = C4::Context->dbh;
983 my $query=qq|SELECT debarred$notify_level
985 WHERE categorycode=?|;
986 my $sth=$dbh->prepare($query);
987 $sth->execute($category);
988 my ($overduerules)=$sth->fetchrow;
990 return($overduerules);
994 =item CheckBorrowerDebarred
996 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
998 Check if the borrowers is already debarred
1000 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
1002 C<$borrowernumber> contains the borrower number
1007 sub CheckBorrowerDebarred{
1008 my($borrowernumber) = @_;
1009 my $dbh = C4::Context->dbh;
1010 my $query=qq|SELECT debarred
1012 WHERE borrowernumber=?
1014 my $sth=$dbh->prepare($query);
1015 $sth->execute($borrowernumber);
1016 my ($debarredstatus)=$sth->fetchrow;
1018 if ($debarredstatus eq '1'){
1025 =item UpdateBorrowerDebarred
1027 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1029 update status of borrowers in borrowers table (field debarred)
1031 C<$borrowernumber> borrower number
1035 sub UpdateBorrowerDebarred{
1036 my($borrowernumber) = @_;
1037 my $dbh = C4::Context->dbh;
1038 my $query=qq|UPDATE borrowers
1040 WHERE borrowernumber=?
1042 my $sth=$dbh->prepare($query);
1043 $sth->execute($borrowernumber);
1048 =item CheckExistantNotifyid
1050 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1052 Check and Returns the notify id if exist else return 0.
1054 C<$exist> contains a notify_id
1056 C<$borrowernumber> contains the borrower number
1058 C<$date_due> contains the date of item return
1063 sub CheckExistantNotifyid {
1064 my($borrowernumber,$date_due) = @_;
1065 my $dbh = C4::Context->dbh;
1066 my $query = qq|SELECT notify_id FROM accountlines
1067 LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1068 WHERE accountlines.borrowernumber =?
1070 my $sth=$dbh->prepare($query);
1071 $sth->execute($borrowernumber,$date_due);
1072 my ($exist)=$sth->fetchrow;
1083 =item CheckAccountLineItemInfo
1085 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1087 Check and Returns the list of all overdue items from the same file number(notify_id).
1089 C<$exist> contains number of line in accounlines
1090 with the same .biblionumber,itemnumber,accounttype,notify_id
1092 C<$borrowernumber> contains the borrower number
1094 C<$itemnumber> contains item number
1096 C<$accounttype> contains account type
1098 C<$notify_id> contains the file number
1102 sub CheckAccountLineItemInfo {
1103 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1104 my $dbh = C4::Context->dbh;
1105 my $query = qq|SELECT count(*) FROM accountlines
1106 WHERE borrowernumber =?
1110 my $sth=$dbh->prepare($query);
1111 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1112 my ($exist)=$sth->fetchrow;
1117 =head2 CheckItemNotify
1119 Sql request to check if the document has alreday been notified
1120 this function is not exported, only used with GetOverduesForBranch
1124 sub CheckItemNotify {
1125 my ($notify_id,$notify_level,$itemnumber) = @_;
1126 my $dbh = C4::Context->dbh;
1127 my $sth = $dbh->prepare("
1128 SELECT COUNT(*) FROM notifys
1130 AND notify_level = ?
1131 AND itemnumber = ? ");
1132 $sth->execute($notify_id,$notify_level,$itemnumber);
1133 my $notified = $sth->fetchrow;
1138 =head2 GetOverduesForBranch
1140 Sql request for display all information for branchoverdues.pl
1141 2 possibilities : with or without location .
1142 display is filtered by branch
1146 sub GetOverduesForBranch {
1147 my ( $branch, $location) = @_;
1148 my $itype_link = (C4::Context->preference('item-level_itypes')) ? " items.itype " : " biblioitems.itemtype ";
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 = $itype_link
1179 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1180 WHERE ( accountlines.amountoutstanding != '0.000000')
1181 AND ( accountlines.accounttype = 'FU')
1182 AND (issues.branchcode = ?)
1183 AND (issues.date_due <= NOW())
1184 ORDER BY borrowers.surname
1186 $sth->execute($branch);
1189 while ( my $data = $sth->fetchrow_hashref ) {
1190 #check if the document has already been notified
1191 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1192 if ($countnotify eq '0'){
1193 $getoverdues[$i] = $data;
1197 return (@getoverdues);
1201 my $dbh = C4::Context->dbh;
1202 my $sth = $dbh->prepare( "
1203 SELECT borrowers.surname,
1204 borrowers.firstname,
1206 itemtypes.description,
1209 branches.branchname,
1213 items.itemcallnumber,
1214 borrowers.borrowernumber,
1216 biblio.biblionumber,
1218 accountlines.notify_id,
1219 accountlines.notify_level,
1221 accountlines.amountoutstanding
1223 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1224 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1225 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1226 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1227 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1228 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1229 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1230 WHERE ( accountlines.amountoutstanding != '0.000000')
1231 AND ( accountlines.accounttype = 'FU')
1232 AND (issues.branchcode = ? AND items.location = ?)
1233 AND (issues.date_due <= NOW())
1234 ORDER BY borrowers.surname
1236 $sth->execute( $branch, $location);
1239 while ( my $data = $sth->fetchrow_hashref ) {
1240 #check if the document has already been notified
1241 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1242 if ($countnotify eq '0'){
1243 $getoverdues[$i] = $data;
1248 return (@getoverdues);
1253 =head2 AddNotifyLine
1255 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1257 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1262 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1263 if ( $method eq "phone" ) {
1264 my $dbh = C4::Context->dbh;
1265 my $sth = $dbh->prepare(
1266 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1267 VALUES (?,?,now(),now(),?,?,?)"
1269 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1274 my $dbh = C4::Context->dbh;
1275 my $sth = $dbh->prepare(
1276 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1277 VALUES (?,?,now(),?,?,?)"
1279 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1286 =head2 RemoveNotifyLine
1288 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1290 Cancel a notification
1294 sub RemoveNotifyLine {
1295 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1296 my $dbh = C4::Context->dbh;
1297 my $sth = $dbh->prepare(
1298 "DELETE FROM notifys
1304 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1316 Koha Developement team <info@koha.org>