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/;
27 use C4::Log; # logaction
29 use vars qw($VERSION @ISA @EXPORT);
32 # set the version for version checking
36 # subs to rename (and maybe merge some...)
41 &CheckAccountLineLevelInfo
42 &CheckAccountLineItemInfo
43 &CheckExistantNotifyid
53 &CreateItemAccountLine
66 # check that an equivalent don't exist already before moving
68 # subs to move to Circulation.pm
73 # &GetIssuingRules - delete.
74 # use C4::Circulation::GetIssuingRule instead.
76 # subs to move to Members.pm
78 &CheckBorrowerDebarred
79 &UpdateBorrowerDebarred
81 # subs to move to Biblio.pm
90 C4::Circulation::Fines - Koha module dealing with fines
98 This module contains several functions for dealing with fines for
99 overdue items. It is primarily used by the 'misc/fines2.pl' script.
107 ($overdues) = &Getoverdues();
109 Returns the list of all overdue books, with their itemtype.
111 C<$overdues> is a reference-to-array. Each element is a
112 reference-to-hash whose keys are the fields of the issues table in the
119 my $dbh = C4::Context->dbh;
120 my $sth = (C4::Context->preference('item-level_itypes')) ?
122 "SELECT issues.*,items.itype as itemtype FROM issues
123 LEFT JOIN items USING (itemnumber)
124 WHERE date_due < now()
125 ORDER BY borrowernumber " )
128 "SELECT issues.*,biblioitems.itemtype,items.itype FROM issues
129 LEFT JOIN items USING (itemnumber)
130 LEFT JOIN biblioitems USING (biblioitemnumber)
131 WHERE date_due < now()
132 ORDER BY borrowernumber " );
136 while ( my $data = $sth->fetchrow_hashref ) {
137 push @results, $data;
146 ( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh );
154 # From Main.pm, modified to return a list of overdueitems, in addition to a count
155 #checks whether a borrower has overdue items
156 my ( $borrowernumber, $dbh ) = @_;
157 my @datearr = localtime;
159 ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3];
162 my $sth = $dbh->prepare(
163 "SELECT * FROM issues
164 LEFT JOIN items ON issues.itemnumber = items.itemnumber
165 LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber
166 LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
167 WHERE issues.borrowernumber = ?
168 AND issues.date_due < ?"
170 $sth->execute( $borrowernumber, $today );
171 while ( my $data = $sth->fetchrow_hashref ) {
172 push( @overdueitems, $data );
176 return ( $count, \@overdueitems );
181 ($amount, $chargename, $message, $daycounttotal, $daycount) =
182 &CalcFine($itemnumber, $categorycode, $branch, $days_overdue, $description);
184 Calculates the fine for a book.
186 The issuingrules table in the Koha database is a fine matrix, listing
187 the penalties for each type of patron for each type of item and each branch (e.g., the
188 standard fine for books might be $0.50, but $1.50 for DVDs, or staff
189 members might get a longer grace period between the first and second
190 reminders that a book is overdue).
193 C<$itemnumber> is the book's item number.
195 C<$categorycode> is the category code of the patron who currently has
198 C<$branchcode> is the library whose issuingrules govern this transaction.
200 C<$days_overdue> is the number of days elapsed since the book's due
204 C<&CalcFine> returns a list of three values:
206 C<$amount> is the fine owed by the patron (see above).
208 C<$chargename> is the chargename field from the applicable record in
209 the categoryitem table, whatever that is.
211 C<$message> is a text message, either "First Notice", "Second Notice",
218 my ( $item, $bortype, $branchcode, $difference , $dues ) = @_;
219 my $dbh = C4::Context->dbh;
222 # calculate how many days the patron is late
223 my $countspecialday=&GetSpecialHolidays($dues,$item->{itemnumber});
224 my $countrepeatableday=&GetRepeatableHolidays($dues,$item->{itemnumber},$difference);
225 my $countalldayclosed = $countspecialday + $countrepeatableday;
226 my $daycount = $difference - $countalldayclosed;
227 # get issuingrules (fines part will be used)
228 my $data = C4::Circulation::GetIssuingRule($item->{'itemtype'},$bortype,$branchcode);
229 my $daycounttotal = $daycount - $data->{'firstremind'};
230 if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype
231 if ($data->{'firstremind'} < $daycount)
233 $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'};
236 # a zero (or null) chargeperiod means no charge.
240 # warn "Calc Fine: " . join(", ", ($item->{'itemnumber'}, $bortype, $difference , $data->{'fine'} . " * " . $daycount . " days = \$ " . $amount , "desc: $dues")) ;
241 return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount );
245 =item GetSpecialHolidays
247 &GetSpecialHolidays($date_dues,$itemnumber);
249 return number of special days between date of the day and date due
251 C<$date_dues> is the envisaged date of book return.
253 C<$itemnumber> is the book's item number.
257 sub GetSpecialHolidays {
258 my ($date_dues,$itemnumber) = @_;
259 # calcul the today date
260 my $today = join "-", &Today();
262 # return the holdingbranch
263 my $iteminfo=GetIssuesIteminfo($itemnumber);
264 # use sql request to find all date between date_due and today
265 my $dbh = C4::Context->dbh;
266 my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date
267 FROM `special_holidays`
268 WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ?
269 AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ?
272 my @result=GetWdayFromItemnumber($itemnumber);
276 my $sth = $dbh->prepare($query);
277 $sth->execute($date_dues,$today,$iteminfo->{'branchcode'});
279 while ( my $special_date=$sth->fetchrow_hashref){
280 push (@result_date,$special_date);
283 my $specialdaycount=scalar(@result_date);
285 for (my $i=0;$i<scalar(@result_date);$i++){
286 $dateinsec=UnixDate($result_date[$i]->{'date'},"%o");
287 (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec);
288 for (my $j=0;$j<scalar(@result);$j++){
289 if ($wday == ($result[$j]->{'weekday'})){
295 return $specialdaycount;
298 =item GetRepeatableHolidays
300 &GetRepeatableHolidays($date_dues, $itemnumber, $difference,);
302 return number of day closed between date of the day and date due
304 C<$date_dues> is the envisaged date of book return.
306 C<$itemnumber> is item number.
308 C<$difference> numbers of between day date of the day and date due
312 sub GetRepeatableHolidays{
313 my ($date_dues,$itemnumber,$difference) = @_;
314 my $dateinsec=UnixDate($date_dues,"%o");
315 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec);
316 my @result=GetWdayFromItemnumber($itemnumber);
320 for (my $i=0;$i<scalar(@result);$i++){
323 for ( $j=0;$j<$difference;$j++){
324 if ($result[$i]->{'weekday'} == $k)
326 push ( @dayclosedcount ,$k);
332 return scalar(@dayclosedcount);
336 =item GetWayFromItemnumber
338 &Getwdayfromitemnumber($itemnumber);
340 return the different week day from repeatable_holidays table
342 C<$itemnumber> is item number.
346 sub GetWdayFromItemnumber{
348 my $iteminfo=GetIssuesIteminfo($itemnumber);
350 my $dbh = C4::Context->dbh;
351 my $query = qq|SELECT weekday
352 FROM repeatable_holidays
355 my $sth = $dbh->prepare($query);
358 $sth->execute($iteminfo->{'branchcode'});
359 while ( my $weekday=$sth->fetchrow_hashref){
360 push (@result,$weekday);
366 =item GetIssuesIteminfo
368 &GetIssuesIteminfo($itemnumber);
370 return all data from issues about item
372 C<$itemnumber> is item number.
376 sub GetIssuesIteminfo{
378 my $dbh = C4::Context->dbh;
379 my $query = qq|SELECT *
383 my $sth = $dbh->prepare($query);
384 $sth->execute($itemnumber);
385 my ($issuesinfo)=$sth->fetchrow_hashref;
392 &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description);
394 (Note: the following is mostly conjecture and guesswork.)
396 Updates the fine owed on an overdue book.
398 C<$itemnumber> is the book's item number.
400 C<$borrowernumber> is the borrower number of the patron who currently
401 has the book on loan.
403 C<$amount> is the current amount owed by the patron.
405 C<$type> will be used in the description of the fine.
407 C<$description> is a string that must be present in the description of
408 the fine. I think this is expected to be a date in DD/MM/YYYY format.
410 C<&UpdateFine> looks up the amount currently owed on the given item
411 and sets it to C<$amount>, creating, if necessary, a new entry in the
412 accountlines table of the Koha database.
417 # FIXME - This API doesn't look right: why should the caller have to
418 # specify both the item number and the borrower number? A book can't
419 # be on loan to two different people, so the item number should be
422 my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_;
423 my $dbh = C4::Context->dbh;
424 # FIXME - What exactly is this query supposed to do? It looks up an
425 # entry in accountlines that matches the given item and borrower
426 # numbers, where the description contains $due, and where the
427 # account type has one of several values, but what does this _mean_?
428 # Does it look up existing fines for this item?
429 # FIXME - What are these various account types? ("FU", "O", "F", "M")
430 my $sth = $dbh->prepare(
431 "Select * from accountlines where itemnumber=? and
432 borrowernumber=? and (accounttype='FU' or accounttype='O' or
433 accounttype='F' or accounttype='M') and description like ?"
435 $sth->execute( $itemnum, $borrowernumber, "%$due%" );
437 if ( my $data = $sth->fetchrow_hashref ) {
439 # I think this if-clause deals with the case where we're updating
441 # print "in accounts ...";
442 if ( $data->{'amount'} != $amount ) {
445 my $diff = $amount - $data->{'amount'};
446 my $out = $data->{'amountoutstanding'} + $diff;
447 my $sth2 = $dbh->prepare(
448 "UPDATE accountlines SET date=now(), amount=?,
449 amountoutstanding=?,accounttype='FU' WHERE
450 borrowernumber=? AND itemnumber=?
451 AND (accounttype='FU' OR accounttype='O') AND description LIKE ?"
453 $sth2->execute( $amount, $out, $data->{'borrowernumber'},
454 $data->{'itemnumber'}, "%$due%" );
459 # print "no update needed $data->{'amount'}"
464 # I think this else-clause deals with the case where we're adding
466 my $sth4 = $dbh->prepare(
467 "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?"
469 $sth4->execute($itemnum);
470 my $title = $sth4->fetchrow_hashref;
473 # # print "not in account";
474 # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines");
477 # # FIXME - Make $accountno a scalar.
478 # my @accountno = $sth3->fetchrow_array;
482 my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber);
483 my $sth2 = $dbh->prepare(
484 "INSERT INTO accountlines
485 (borrowernumber,itemnumber,date,amount,
486 description,accounttype,amountoutstanding,accountno) VALUES
487 (?,?,now(),?,?,'FU',?,?)"
489 $sth2->execute( $borrowernumber, $itemnum, $amount,
490 "$type $title->{'title'} $due",
491 $amount, $nextaccntno);
499 "due=".$due." amount=".$amount." itemnumber=".$itemnum
500 ) if C4::Context->preference("FinesLog");
507 $borrower = &BorType($borrowernumber);
509 Looks up a patron by borrower number.
511 C<$borrower> is a reference-to-hash whose keys are all of the fields
512 from the borrowers and categories tables of the Koha database. Thus,
513 C<$borrower> contains all information about both the borrower and
514 category he or she belongs to.
520 my ($borrowernumber) = @_;
521 my $dbh = C4::Context->dbh;
522 my $sth = $dbh->prepare(
523 "SELECT * from borrowers
524 LEFT JOIN categories ON borrowers.categorycode=categories.categorycode
525 WHERE borrowernumber=?"
527 $sth->execute($borrowernumber);
528 my $data = $sth->fetchrow_hashref;
533 =item ReplacementCost
535 $cost = &ReplacementCost($itemnumber);
537 Returns the replacement cost of the item with the given item number.
542 sub ReplacementCost {
544 my $dbh = C4::Context->dbh;
546 $dbh->prepare("Select replacementprice from items where itemnumber=?");
547 $sth->execute($itemnum);
549 # FIXME - Use fetchrow_array or something.
550 my $data = $sth->fetchrow_hashref;
552 return ( $data->{'replacementprice'} );
557 $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber);
559 return the total of fine
561 C<$itemnum> is item number
563 C<$borrowernumber> is the borrowernumber
569 my ( $itemnum, $borrowernumber ) = @_;
570 my $dbh = C4::Context->dbh();
571 my $query = "SELECT sum(amountoutstanding) FROM accountlines
572 where accounttype like 'F%'
573 AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?";
574 my $sth = $dbh->prepare($query);
575 $sth->execute( $itemnum, $borrowernumber );
576 my $data = $sth->fetchrow_hashref();
579 return ( $data->{'sum(amountoutstanding)'} );
585 =item GetIssuingRules
587 FIXME - This sub should be deprecated and removed.
588 It ignores branch and defaults.
590 $data = &GetIssuingRules($itemtype,$categorycode);
592 Looks up for all issuingrules an item info
594 C<$itemnumber> is a reference-to-hash whose keys are all of the fields
595 from the borrowers and categories tables of the Koha database. Thus,
597 C<$categorycode> contains information about borrowers category
599 C<$data> contains all information about both the borrower and
600 category he or she belongs to.
603 sub GetIssuingRules {
604 my ($itemtype,$categorycode)=@_;
605 my $dbh = C4::Context->dbh();
606 my $query=qq|SELECT *
608 WHERE issuingrules.itemtype=?
609 AND issuingrules.categorycode=?
611 my $sth = $dbh->prepare($query);
613 $sth->execute($itemtype,$categorycode);
614 my ($data) = $sth->fetchrow_hashref;
621 sub ReplacementCost2 {
622 my ( $itemnum, $borrowernumber ) = @_;
623 my $dbh = C4::Context->dbh();
624 my $query = "SELECT amountoutstanding
626 WHERE accounttype like 'L'
627 AND amountoutstanding > 0
629 AND borrowernumber= ?";
630 my $sth = $dbh->prepare($query);
631 $sth->execute( $itemnum, $borrowernumber );
632 my $data = $sth->fetchrow_hashref();
635 return ( $data->{'amountoutstanding'} );
639 =item GetNextIdNotify
641 ($result) = &GetNextIdNotify($reference);
643 Returns the new file number
645 C<$result> contains the next file number
647 C<$reference> contains the beggining of file number
653 sub GetNextIdNotify {
655 my $query=qq|SELECT max(notify_id)
657 WHERE notify_id like \"$reference%\"
659 # AND borrowernumber=?|;
660 my $dbh = C4::Context->dbh;
661 my $sth=$dbh->prepare($query);
663 my $result=$sth->fetchrow;
668 ($result=$reference."01") ;
671 $count=substr($result,6)+1;
674 ($count = "0".$count);
676 $result=$reference.$count;
684 (@notify) = &NumberNotifyId($borrowernumber);
686 Returns amount for all file per borrowers
687 C<@notify> array contains all file per borrowers
689 C<$notify_id> contains the file number for the borrower number nad item number
694 my ($borrowernumber)=@_;
695 my $dbh = C4::Context->dbh;
696 my $query=qq| SELECT distinct(notify_id)
698 WHERE borrowernumber=?|;
700 my $sth=$dbh->prepare($query);
701 $sth->execute($borrowernumber);
702 while ( my ($numberofnotify)=$sth->fetchrow){
703 push (@notify,$numberofnotify);
713 ($totalnotify) = &AmountNotify($notifyid);
715 Returns amount for all file per borrowers
716 C<$notifyid> is the file number
718 C<$totalnotify> contains amount of a file
720 C<$notify_id> contains the file number for the borrower number and item number
725 my ($notifyid,$borrowernumber)=@_;
726 my $dbh = C4::Context->dbh;
727 my $query=qq| SELECT sum(amountoutstanding)
729 WHERE notify_id=? AND borrowernumber = ?|;
730 my $sth=$dbh->prepare($query);
731 $sth->execute($notifyid,$borrowernumber);
732 my $totalnotify=$sth->fetchrow;
734 return ($totalnotify);
740 ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber);
742 Returns the file number per borrower and itemnumber
744 C<$borrowernumber> is a reference-to-hash whose keys are all of the fields
745 from the items tables of the Koha database. Thus,
747 C<$itemnumber> contains the borrower categorycode
749 C<$notify_id> contains the file number for the borrower number nad item number
754 my ($borrowernumber,$itemnumber)=@_;
755 my $query=qq|SELECT notify_id
757 WHERE borrowernumber=?
759 AND (accounttype='FU' or accounttype='O')|;
760 my $dbh = C4::Context->dbh;
761 my $sth=$dbh->prepare($query);
762 $sth->execute($borrowernumber,$itemnumber);
763 my ($notify_id)=$sth->fetchrow;
769 =item CreateItemAccountLine
771 () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
773 update the account lines with file number or with file level
775 C<$items> is a reference-to-hash whose keys are all of the fields
776 from the items tables of the Koha database. Thus,
778 C<$itemnumber> contains the item number
780 C<$borrowernumber> contains the borrower number
782 C<$date> contains the date of the day
784 C<$amount> contains item price
786 C<$description> contains the descritpion of accounttype
788 C<$accounttype> contains the account type
790 C<$amountoutstanding> contains the $amountoutstanding
792 C<$timestamp> contains the timestamp with time and the date of the day
794 C<$notify_id> contains the file number
796 C<$level> contains the file level
801 sub CreateItemAccountLine {
802 my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_;
803 my $dbh = C4::Context->dbh;
804 my $nextaccntno = getnextacctno($borrowernumber);
805 my $query= "INSERT into accountlines
806 (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level)
808 (?,?,?,?,?,?,?,?,?,?,?)";
811 my $sth=$dbh->prepare($query);
812 $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level);
816 =item UpdateAccountLines
818 () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber);
820 update the account lines with file number or with file level
822 C<$items> is a reference-to-hash whose keys are all of the fields
823 from the items tables of the Koha database. Thus,
825 C<$itemnumber> contains the item number
827 C<$notify_id> contains the file number
829 C<$notify_level> contains the file level
831 C<$borrowernumber> contains the borrowernumber
835 sub UpdateAccountLines {
836 my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_;
838 if ($notify_id eq '')
841 $query=qq|UPDATE accountlines
843 WHERE borrowernumber=? AND itemnumber=?
844 AND (accounttype='FU' or accounttype='O')|;
847 $query=qq|UPDATE accountlines
848 SET notify_id=?, notify_level=?
849 WHERE borrowernumber=?
851 AND (accounttype='FU' or accounttype='O')|;
853 my $dbh = C4::Context->dbh;
854 my $sth=$dbh->prepare($query);
856 if ($notify_id eq '')
858 $sth->execute($notify_level,$borrowernumber,$itemnumber);
861 $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber);
870 ($items) = &GetItems($itemnumber);
872 Returns the list of all delays from overduerules.
874 C<$items> is a reference-to-hash whose keys are all of the fields
875 from the items tables of the Koha database. Thus,
877 C<$itemnumber> contains the borrower categorycode
882 my($itemnumber) = @_;
883 my $query=qq|SELECT *
886 my $dbh = C4::Context->dbh;
887 my $sth=$dbh->prepare($query);
888 $sth->execute($itemnumber);
889 my ($items)=$sth->fetchrow_hashref;
894 =item GetOverdueDelays
896 (@delays) = &GetOverdueDelays($categorycode);
898 Returns the list of all delays from overduerules.
900 C<@delays> it's an array contains the three delays from overduerules table
902 C<$categorycode> contains the borrower categorycode
906 sub GetOverdueDelays {
908 my $dbh = C4::Context->dbh;
909 my $query=qq|SELECT delay1,delay2,delay3
911 WHERE categorycode=?|;
912 my $sth=$dbh->prepare($query);
913 $sth->execute($category);
914 my (@delays)=$sth->fetchrow_array;
919 =item CheckAccountLineLevelInfo
921 ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level);
923 Check and Returns the list of all overdue books.
925 C<$exist> contains number of line in accounlines
926 with the same .biblionumber,itemnumber,accounttype,and notify_level
928 C<$borrowernumber> contains the borrower number
930 C<$itemnumber> contains item number
932 C<$accounttype> contains account type
934 C<$notify_level> contains the accountline level
939 sub CheckAccountLineLevelInfo {
940 my($borrowernumber,$itemnumber,$level) = @_;
941 my $dbh = C4::Context->dbh;
942 my $query= qq|SELECT count(*)
944 WHERE borrowernumber =?
947 my $sth=$dbh->prepare($query);
948 $sth->execute($borrowernumber,$itemnumber,$level);
949 my ($exist)=$sth->fetchrow;
954 =item GetOverduerules
956 ($overduerules) = &GetOverduerules($categorycode);
958 Returns the value of borrowers (debarred or not) with notify level
960 C<$overduerules> return value of debbraed field in overduerules table
962 C<$category> contains the borrower categorycode
964 C<$notify_level> contains the notify level
969 my($category,$notify_level) = @_;
970 my $dbh = C4::Context->dbh;
971 my $query=qq|SELECT debarred$notify_level
973 WHERE categorycode=?|;
974 my $sth=$dbh->prepare($query);
975 $sth->execute($category);
976 my ($overduerules)=$sth->fetchrow;
978 return($overduerules);
982 =item CheckBorrowerDebarred
984 ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber);
986 Check if the borrowers is already debarred
988 C<$debarredstatus> return 0 for not debarred and return 1 for debarred
990 C<$borrowernumber> contains the borrower number
995 sub CheckBorrowerDebarred{
996 my($borrowernumber) = @_;
997 my $dbh = C4::Context->dbh;
998 my $query=qq|SELECT debarred
1000 WHERE borrowernumber=?
1002 my $sth=$dbh->prepare($query);
1003 $sth->execute($borrowernumber);
1004 my ($debarredstatus)=$sth->fetchrow;
1006 if ($debarredstatus eq '1'){
1013 =item UpdateBorrowerDebarred
1015 ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber);
1017 update status of borrowers in borrowers table (field debarred)
1019 C<$borrowernumber> borrower number
1023 sub UpdateBorrowerDebarred{
1024 my($borrowernumber) = @_;
1025 my $dbh = C4::Context->dbh;
1026 my $query=qq|UPDATE borrowers
1028 WHERE borrowernumber=?
1030 my $sth=$dbh->prepare($query);
1031 $sth->execute($borrowernumber);
1036 =item CheckExistantNotifyid
1038 ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id);
1040 Check and Returns the notify id if exist else return 0.
1042 C<$exist> contains a notify_id
1044 C<$borrowernumber> contains the borrower number
1046 C<$date_due> contains the date of item return
1051 sub CheckExistantNotifyid {
1052 my($borrowernumber,$date_due) = @_;
1053 my $dbh = C4::Context->dbh;
1054 my $query = qq|SELECT notify_id FROM accountlines
1055 LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber
1056 WHERE accountlines.borrowernumber =?
1058 my $sth=$dbh->prepare($query);
1059 $sth->execute($borrowernumber,$date_due);
1060 my ($exist)=$sth->fetchrow;
1071 =item CheckAccountLineItemInfo
1073 ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id);
1075 Check and Returns the list of all overdue items from the same file number(notify_id).
1077 C<$exist> contains number of line in accounlines
1078 with the same .biblionumber,itemnumber,accounttype,notify_id
1080 C<$borrowernumber> contains the borrower number
1082 C<$itemnumber> contains item number
1084 C<$accounttype> contains account type
1086 C<$notify_id> contains the file number
1090 sub CheckAccountLineItemInfo {
1091 my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_;
1092 my $dbh = C4::Context->dbh;
1093 my $query = qq|SELECT count(*) FROM accountlines
1094 WHERE borrowernumber =?
1098 my $sth=$dbh->prepare($query);
1099 $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id);
1100 my ($exist)=$sth->fetchrow;
1105 =head2 CheckItemNotify
1107 Sql request to check if the document has alreday been notified
1108 this function is not exported, only used with GetOverduesForBranch
1112 sub CheckItemNotify {
1113 my ($notify_id,$notify_level,$itemnumber) = @_;
1114 my $dbh = C4::Context->dbh;
1115 my $sth = $dbh->prepare("
1116 SELECT COUNT(*) FROM notifys
1118 AND notify_level = ?
1119 AND itemnumber = ? ");
1120 $sth->execute($notify_id,$notify_level,$itemnumber);
1121 my $notified = $sth->fetchrow;
1126 =head2 GetOverduesForBranch
1128 Sql request for display all information for branchoverdues.pl
1129 2 possibilities : with or without location .
1130 display is filtered by branch
1134 sub GetOverduesForBranch {
1135 my ( $branch, $location) = @_;
1136 my $itype_link = (C4::Context->preference('item-level_itypes')) ? " items.itype " : " biblioitems.itemtype ";
1137 if ( not $location ) {
1138 my $dbh = C4::Context->dbh;
1139 my $sth = $dbh->prepare("
1142 borrowers.firstname,
1144 itemtypes.description,
1147 branches.branchname,
1151 items.itemcallnumber,
1152 borrowers.borrowernumber,
1154 biblio.biblionumber,
1156 accountlines.notify_id,
1157 accountlines.notify_level,
1159 accountlines.amountoutstanding
1161 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1162 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1163 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1164 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1165 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1166 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1167 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1168 WHERE ( accountlines.amountoutstanding != '0.000000')
1169 AND ( accountlines.accounttype = 'FU')
1170 AND (issues.branchcode = ?)
1171 AND (issues.date_due <= NOW())
1172 ORDER BY borrowers.surname
1174 $sth->execute($branch);
1177 while ( my $data = $sth->fetchrow_hashref ) {
1178 #check if the document has already been notified
1179 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1180 if ($countnotify eq '0'){
1181 $getoverdues[$i] = $data;
1185 return (@getoverdues);
1189 my $dbh = C4::Context->dbh;
1190 my $sth = $dbh->prepare( "
1191 SELECT borrowers.surname,
1192 borrowers.firstname,
1194 itemtypes.description,
1197 branches.branchname,
1201 items.itemcallnumber,
1202 borrowers.borrowernumber,
1204 biblio.biblionumber,
1206 accountlines.notify_id,
1207 accountlines.notify_level,
1209 accountlines.amountoutstanding
1211 LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber
1212 LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber
1213 LEFT JOIN items ON items.itemnumber = issues.itemnumber
1214 LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber
1215 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber
1216 LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link
1217 LEFT JOIN branches ON branches.branchcode = issues.branchcode
1218 WHERE ( accountlines.amountoutstanding != '0.000000')
1219 AND ( accountlines.accounttype = 'FU')
1220 AND (issues.branchcode = ? AND items.location = ?)
1221 AND (issues.date_due <= NOW())
1222 ORDER BY borrowers.surname
1224 $sth->execute( $branch, $location);
1227 while ( my $data = $sth->fetchrow_hashref ) {
1228 #check if the document has already been notified
1229 my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'});
1230 if ($countnotify eq '0'){
1231 $getoverdues[$i] = $data;
1236 return (@getoverdues);
1241 =head2 AddNotifyLine
1243 &AddNotifyLine($borrowernumber, $itemnumber, $overduelevel, $method, $notifyId)
1245 Creat a line into notify, if the method is phone, the notification_send_date is implemented to
1250 my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_;
1251 if ( $method eq "phone" ) {
1252 my $dbh = C4::Context->dbh;
1253 my $sth = $dbh->prepare(
1254 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id)
1255 VALUES (?,?,now(),now(),?,?,?)"
1257 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1262 my $dbh = C4::Context->dbh;
1263 my $sth = $dbh->prepare(
1264 "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id)
1265 VALUES (?,?,now(),?,?,?)"
1267 $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method,
1274 =head2 RemoveNotifyLine
1276 &RemoveNotifyLine( $borrowernumber, $itemnumber, $notify_date );
1278 Cancel a notification
1282 sub RemoveNotifyLine {
1283 my ( $borrowernumber, $itemnumber, $notify_date ) = @_;
1284 my $dbh = C4::Context->dbh;
1285 my $sth = $dbh->prepare(
1286 "DELETE FROM notifys
1292 $sth->execute( $borrowernumber, $itemnumber, $notify_date );
1304 Koha Developement team <info@koha.org>