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 $sth = $dbh->prepare(
119 "SELECT issues.*,biblioitems.itemtype FROM issues
120 LEFT JOIN items USING (itemnumber)
121 LEFT JOIN biblioitems USING (biblioitemnumber)
122 WHERE date_due < now()
124 NULL ORDER BY borrowernumber
130 while ( my $data = $sth->fetchrow_hashref ) {
131 push @results, $data;
140 ( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh );
148 # From Main.pm, modified to return a list of overdueitems, in addition to a count
149 #checks whether a borrower has overdue items
150 my ( $borrowernumber, $dbh ) = @_;
151 my @datearr = localtime;
153 ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
156 my $sth = $dbh->prepare(
157 "SELECT * FROM issues
158 LEFT JOIN items ON issues.itemnumber = items.itemnumber
159 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
160 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
161 WHERE issues.borrowernumber = ?
162 AND issues.returndate is NULL
163 AND issues.date_due < ?"
165 $sth->execute( $borrowernumber, $today );
166 while ( my $data = $sth->fetchrow_hashref ) {
167 push( @overdueitems, $data );
171 return ( $count, \@overdueitems );
176 ($amount, $chargename, $message) =
177 &CalcFine($itemnumber, $borrowercode, $days_overdue);
179 Calculates the fine for a book.
181 The issuingrules table in the Koha database is a fine matrix, listing
182 the penalties for each type of patron for each type of item and each branch (e.g., the
183 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
184 members might get a longer grace period between the first and second
185 reminders that a book is overdue).
187 The fine is calculated as follows: if it is time for the first
188 reminder, the fine is the value listed for the given (branch, item type,
189 borrower code) combination. If it is time for the second reminder, the
190 fine is doubled. Finally, if it is time to send the account to a
191 collection agency, the fine is set to 5 local monetary units (a really
192 good deal for the patron if the library is in Italy). Otherwise, the
195 Note that the way this function is currently implemented, it only
196 returns a nonzero value on the notable days listed above. That is, if
197 the categoryitems entry says to send a first reminder 7 days after the
198 book is due, then if you call C<&CalcFine> 7 days after the book is
199 due, it will give a nonzero fine. If you call C<&CalcFine> the next
200 day, however, it will say that the fine is 0.
202 C<$itemnumber> is the book's item number.
204 C<$borrowercode> is the borrower code of the patron who currently has
207 C<$days_overdue> is the number of days elapsed since the book's due
210 C<&CalcFine> returns a list of three values:
212 C<$amount> is the fine owed by the patron (see above).
214 C<$chargename> is the chargename field from the applicable record in
215 the categoryitem table, whatever that is.
217 C<$message> is a text message, either "First Notice", "Second Notice",
224 my ( $item, $bortype, $difference , $dues ) = @_;
225 my $dbh = C4::Context->dbh;
228 # calculate how many days the patron is late
229 my $countspecialday=&GetSpecialHolidays($dues,$item->{itemnumber});
230 my $countrepeatableday=&GetRepeatableHolidays($dues,$item->{itemnumber},$difference);
231 my $countalldayclosed = $countspecialday + $countrepeatableday;
232 my $daycount = $difference - $countalldayclosed;
233 # get issuingrules (fines part will be used)
234 my $data = GetIssuingRules($item->{'itemtype'},$bortype);
235 my $daycounttotal = $daycount - $data->{'firstremind'};
236 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
237 if ($data->{'firstremind'} < $daycount)
239 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
242 # get fines default rules
243 my $data = GetIssuingRules($item->{'itemtype'},'*');
244 $daycounttotal = $daycount - $data->{'firstremind'};
245 if ($data->{'firstremind'} < $daycount)
247 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
248 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
253 warn "Calc Fine for $item->{'itemnumber'}, $bortype, $difference , $dues = $amount / $daycount";
254 return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
258 =item GetSpecialHolidays
260 &GetSpecialHolidays($date_dues,$itemnumber);
262 return number of special days between date of the day and date due
264 C<$date_dues> is the envisaged date of book return.
266 C<$itemnumber> is the book's item number.
270 sub GetSpecialHolidays {
271 my ($date_dues,$itemnumber) = @_;
272 # calcul the today date
273 my $today = join "-", &Today();
275 # return the holdingbranch
276 my $iteminfo=GetIssuesIteminfo($itemnumber);
277 # use sql request to find all date between date_due and today
278 my $dbh = C4::Context->dbh;
279 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
280 FROM `special_holidays`
281 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
282 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
285 my @result=GetWdayFromItemnumber($itemnumber);
289 my $sth = $dbh->prepare($query);
290 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
292 while ( my $special_date=$sth->fetchrow_hashref){
293 push (@result_date,$special_date);
296 my $specialdaycount=scalar(@result_date);
298 for (my $i=0;$i<scalar(@result_date);$i++){
299 $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
300 (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
301 for (my $j=0;$j<scalar(@result);$j++){
302 if ($wday == ($result[$j]->{'weekday'})){
308 return $specialdaycount;
311 =item GetRepeatableHolidays
313 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
315 return number of day closed between date of the day and date due
317 C<$date_dues> is the envisaged date of book return.
319 C<$itemnumber> is item number.
321 C<$difference> numbers of between day date of the day and date due
325 sub GetRepeatableHolidays{
326 my ($date_dues,$itemnumber,$difference) = @_;
327 my $dateinsec=UnixDate($date_dues,"%o");
328 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
329 my @result=GetWdayFromItemnumber($itemnumber);
333 for (my $i=0;$i<scalar(@result);$i++){
336 for ( $j=0;$j<$difference;$j++){
337 if ($result[$i]->{'weekday'} == $k)
339 push ( @dayclosedcount ,$k);
345 return scalar(@dayclosedcount);
349 =item GetWayFromItemnumber
351 &Getwdayfromitemnumber($itemnumber);
353 return the different week day from repeatable_holidays table
355 C<$itemnumber> is item number.
359 sub GetWdayFromItemnumber{
361 my $iteminfo=GetIssuesIteminfo($itemnumber);
363 my $dbh = C4::Context->dbh;
364 my $query = qq|SELECT weekday
365 FROM repeatable_holidays
368 my $sth = $dbh->prepare($query);
371 $sth->execute($iteminfo->{'branchcode'});
372 while ( my $weekday=$sth->fetchrow_hashref){
373 push (@result,$weekday);
379 =item GetIssuesIteminfo
381 &GetIssuesIteminfo($itemnumber);
383 return all data from issues about item
385 C<$itemnumber> is item number.
389 sub GetIssuesIteminfo{
391 my $dbh = C4::Context->dbh;
392 my $query = qq|SELECT *
396 my $sth = $dbh->prepare($query);
397 $sth->execute($itemnumber);
398 my ($issuesinfo)=$sth->fetchrow_hashref;
405 &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
407 (Note: the following is mostly conjecture and guesswork.)
409 Updates the fine owed on an overdue book.
411 C<$itemnumber> is the book's item number.
413 C<$borrowernumber> is the borrower number of the patron who currently
414 has the book on loan.
416 C<$amount> is the current amount owed by the patron.
418 C<$type> will be used in the description of the fine.
420 C<$description> is a string that must be present in the description of
421 the fine. I think this is expected to be a date in DD/MM/YYYY format.
423 C<&UpdateFine> looks up the amount currently owed on the given item
424 and sets it to C<$amount>, creating, if necessary, a new entry in the
425 accountlines table of the Koha database.
430 # FIXME - This API doesn't look right: why should the caller have to
431 # specify both the item number and the borrower number? A book can't
432 # be on loan to two different people, so the item number should be
435 my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
436 my $dbh = C4::Context->dbh;
437 # FIXME - What exactly is this query supposed to do? It looks up an
438 # entry in accountlines that matches the given item and borrower
439 # numbers, where the description contains $due, and where the
440 # account type has one of several values, but what does this _mean_?
441 # Does it look up existing fines for this item?
442 # FIXME - What are these various account types? ("FU", "O", "F", "M")
443 my $sth = $dbh->prepare(
444 "Select * from accountlines where itemnumber=? and
445 borrowernumber=? and (accounttype='FU' or accounttype='O' or
446 accounttype='F' or accounttype='M') and description like ?"
448 $sth->execute( $itemnum, $borrowernumber, "%$due%" );
450 if ( my $data = $sth->fetchrow_hashref ) {
452 # I think this if-clause deals with the case where we're updating
454 # print "in accounts ...";
455 if ( $data->{'amount'} != $amount ) {
458 my $diff = $amount - $data->{'amount'};
459 my $out = $data->{'amountoutstanding'} + $diff;
460 my $sth2 = $dbh->prepare(
461 "UPDATE accountlines SET date=now(), amount=?,
462 amountoutstanding=?,accounttype='FU' WHERE
463 borrowernumber=? AND itemnumber=?
464 AND (accounttype='FU' OR accounttype='O') AND description LIKE ?"
466 $sth2->execute( $amount, $out, $data->{'borrowernumber'},
467 $data->{'itemnumber'}, "%$due%" );
472 # print "no update needed $data->{'amount'}"
477 # I think this else-clause deals with the case where we're adding
479 my $sth4 = $dbh->prepare(
480 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
482 $sth4->execute($itemnum);
483 my $title = $sth4->fetchrow_hashref;
486 # # print "not in account";
487 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
490 # # FIXME - Make $accountno a scalar.
491 # my @accountno = $sth3->fetchrow_array;
495 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
496 my $sth2 = $dbh->prepare(
497 "INSERT INTO accountlines
498 (borrowernumber,itemnumber,date,amount,
499 description,accounttype,amountoutstanding,accountno) VALUES
500 (?,?,now(),?,?,'FU',?,?)"
502 $sth2->execute( $borrowernumber, $itemnum, $amount,
503 "$type $title->{'title'} $due",
504 $amount, $nextaccntno);
509 C4::Context->userenv->{'number'},
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) = &AmountNotify($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 nad item number
737 my $dbh = C4::Context->dbh;
738 my $query=qq| SELECT sum(amountoutstanding)
741 my $sth=$dbh->prepare($query);
742 $sth->execute($notifyid);
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 if ( not $location ) {
1148 my $dbh = C4::Context->dbh;
1149 my $sth = $dbh->prepare("
1152 borrowers.firstname,
1154 itemtypes.description,
1157 branches.branchname,
1161 items.itemcallnumber,
1162 borrowers.borrowernumber,
1164 biblio.biblionumber,
1166 accountlines.notify_id,
1167 accountlines.notify_level,
1169 accountlines.amountoutstanding
1171 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1172 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1173 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1174 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1175 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1176 LEFT JOIN itemtypes ON itemtypes.itemtype = biblioitems.itemtype
1177 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1178 WHERE ( issues.returndate is null)
1179 AND ( 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 = biblioitems.itemtype
1228 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1229 WHERE ( issues.returndate is null )
1230 AND ( 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>