X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FOverdues.pm;h=0815386d7b4119c70635efda65bd99b5b6f9945f;hb=0053486cfaae95549c93c00f33841779b12e8914;hp=1e4fd698686750d5253abb55afcb572636aba349;hpb=600d479f4e47041a6465797150ac6e16f03d0b32;p=koha.git diff --git a/C4/Overdues.pm b/C4/Overdues.pm index 1e4fd69868..0815386d7b 100644 --- a/C4/Overdues.pm +++ b/C4/Overdues.pm @@ -19,12 +19,13 @@ package C4::Overdues; # Suite 330, Boston, MA 02111-1307 USA use strict; -use Date::Calc qw/Today/; +use Date::Calc qw/Today Date_to_Days/; use Date::Manip qw/UnixDate/; use C4::Circulation; use C4::Context; use C4::Accounts; use C4::Log; # logaction +use C4::Debug; use vars qw($VERSION @ISA @EXPORT); @@ -100,11 +101,9 @@ overdue items. It is primarily used by the 'misc/fines2.pl' script. =head1 FUNCTIONS -=over 2 +=head2 Getoverdues -=item Getoverdues - - ($overdues) = &Getoverdues(); + $overdues = Getoverdues( { minimumdays => 1, maximumdays => 30 } ); Returns the list of all overdue books, with their itemtype. @@ -116,70 +115,72 @@ Koha database. #' sub Getoverdues { + my $params = shift; my $dbh = C4::Context->dbh; - my $sth = (C4::Context->preference('item-level_itypes')) ? - $dbh->prepare( - "SELECT issues.*,items.itype as itemtype FROM issues - LEFT JOIN items USING (itemnumber) - WHERE date_due < now() - ORDER BY borrowernumber " ) - : - $dbh->prepare( - "SELECT issues.*,biblioitems.itemtype,items.itype FROM issues - LEFT JOIN items USING (itemnumber) - LEFT JOIN biblioitems USING (biblioitemnumber) - WHERE date_due < now() - ORDER BY borrowernumber " ); - $sth->execute; - - my @results; - while ( my $data = $sth->fetchrow_hashref ) { - push @results, $data; + my $statement; + if ( C4::Context->preference('item-level_itypes') ) { + $statement = " + SELECT issues.*, items.itype as itemtype, items.homebranch, items.barcode + FROM issues +LEFT JOIN items USING (itemnumber) + WHERE date_due < now() +"; + } else { + $statement = " + SELECT issues.*, biblioitems.itemtype, items.itype, items.homebranch, items.barcode + FROM issues +LEFT JOIN items USING (itemnumber) +LEFT JOIN biblioitems USING (biblioitemnumber) + WHERE date_due < now() +"; } - $sth->finish; - return \@results; + my @bind_parameters; + if ( exists $params->{'minimumdays'} and exists $params->{'maximumdays'} ) { + $statement .= ' AND TO_DAYS( NOW() )-TO_DAYS( date_due ) BETWEEN ? and ? '; + push @bind_parameters, $params->{'minimumdays'}, $params->{'maximumdays'}; + } elsif ( exists $params->{'minimumdays'} ) { + $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) > ? '; + push @bind_parameters, $params->{'minimumdays'}; + } elsif ( exists $params->{'maximumdays'} ) { + $statement .= ' AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) < ? '; + push @bind_parameters, $params->{'maximumdays'}; + } + $statement .= 'ORDER BY borrowernumber'; + my $sth = $dbh->prepare( $statement ); + $sth->execute( @bind_parameters ); + return $sth->fetchall_arrayref({}); } + =head2 checkoverdues -( $count, $overdueitems )=checkoverdues( $borrowernumber, $dbh ); +($count, $overdueitems) = checkoverdues($borrowernumber); -Not exported +Returns a count and a list of overdueitems for a given borrowernumber =cut sub checkoverdues { - -# From Main.pm, modified to return a list of overdueitems, in addition to a count -#checks whether a borrower has overdue items - my ( $borrowernumber, $dbh ) = @_; - my @datearr = localtime; - my $today = - ( $datearr[5] + 1900 ) . "-" . ( $datearr[4] + 1 ) . "-" . $datearr[3]; - my @overdueitems; - my $count = 0; - my $sth = $dbh->prepare( + my $borrowernumber = shift or return; + my $sth = C4::Context->dbh->prepare( "SELECT * FROM issues - LEFT JOIN items ON issues.itemnumber = items.itemnumber - LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber + LEFT JOIN items ON issues.itemnumber = items.itemnumber + LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber WHERE issues.borrowernumber = ? - AND issues.date_due < ?" + AND issues.date_due < NOW()" ); - $sth->execute( $borrowernumber, $today ); - while ( my $data = $sth->fetchrow_hashref ) { - push( @overdueitems, $data ); - $count++; - } - $sth->finish; - return ( $count, \@overdueitems ); + # FIXME: SELECT * across 4 tables? do we really need the marc AND marcxml blobs?? + $sth->execute($borrowernumber); + my $results = $sth->fetchall_arrayref({}); + return ( scalar(@$results), $results); # returning the count and the results is silly } -=item CalcFine +=head2 CalcFine - ($amount, $chargename, $message, $daycounttotal, $daycount) = - &CalcFine($itemnumber, $categorycode, $branch, $days_overdue, $description); + ($amount, $chargename, $daycount, $daycounttotal) = + &CalcFine($item, $categorycode, $branch, $days_overdue, $description, $start_date, $end_date ); Calculates the fine for a book. @@ -190,59 +191,90 @@ members might get a longer grace period between the first and second reminders that a book is overdue). -C<$itemnumber> is the book's item number. +C<$item> is an item object (hashref). -C<$categorycode> is the category code of the patron who currently has +C<$categorycode> is the category code (string) of the patron who currently has the book. -C<$branchcode> is the library whose issuingrules govern this transaction. +C<$branchcode> is the library (string) whose issuingrules govern this transaction. + +C<$days_overdue> is the number of days elapsed since the book's due date. + NOTE: supplying days_overdue is deprecated. -C<$days_overdue> is the number of days elapsed since the book's due -date. +C<$start_date> & C<$end_date> are C4::Dates objects +defining the date range over which to determine the fine. +Note that if these are defined, we ignore C<$difference> and C<$dues> , +but retain these for backwards-comptibility with extant fines scripts. +Fines scripts should just supply the date range over which to calculate the fine. -C<&CalcFine> returns a list of three values: +C<&CalcFine> returns four values: C<$amount> is the fine owed by the patron (see above). C<$chargename> is the chargename field from the applicable record in the categoryitem table, whatever that is. -C<$message> is a text message, either "First Notice", "Second Notice", -or "Final Notice". +C<$daycount> is the number of days between start and end dates, Calendar adjusted (where needed), +minus any applicable grace period. + +C<$daycounttotal> is C<$daycount> without consideration of grace period. + +FIXME - What is chargename supposed to be ? + +FIXME: previously attempted to return C<$message> as a text message, either "First Notice", "Second Notice", +or "Final Notice". But CalcFine never defined any value. =cut -#' sub CalcFine { - my ( $item, $bortype, $branchcode, $difference , $dues ) = @_; + my ( $item, $bortype, $branchcode, $difference ,$dues , $start_date, $end_date ) = @_; + $debug and warn sprintf("CalcFine(%s, %s, %s, %s, %s, %s, %s)", + ($item ? '{item}' : 'UNDEF'), + ($bortype || 'UNDEF'), + ($branchcode || 'UNDEF'), + ($difference || 'UNDEF'), + ($dues || 'UNDEF'), + ($start_date ? ($start_date->output('iso') || 'Not a C4::Dates object') : 'UNDEF'), + ( $end_date ? ( $end_date->output('iso') || 'Not a C4::Dates object') : 'UNDEF') + ); my $dbh = C4::Context->dbh; my $amount = 0; - my $printout; - # calculate how many days the patron is late - my $countspecialday=&GetSpecialHolidays($dues,$item->{itemnumber}); - my $countrepeatableday=&GetRepeatableHolidays($dues,$item->{itemnumber},$difference); - my $countalldayclosed = $countspecialday + $countrepeatableday; - my $daycount = $difference - $countalldayclosed; - # get issuingrules (fines part will be used) - my $data = C4::Circulation::GetIssuingRule($item->{'itemtype'},$bortype,$branchcode); - my $daycounttotal = $daycount - $data->{'firstremind'}; - if ($data->{'chargeperiod'} >0) { # if there is a rule for this bortype - if ($data->{'firstremind'} < $daycount) - { - $amount = int($daycounttotal/$data->{'chargeperiod'})*$data->{'fine'}; - } + my $daystocharge; + # get issuingrules (fines part will be used) + $debug and warn sprintf("CalcFine calling GetIssuingRule(%s, %s, %s)", $bortype, $item->{'itemtype'}, $branchcode); + my $data = C4::Circulation::GetIssuingRule($bortype, $item->{'itemtype'}, $branchcode); + if($difference) { + # if $difference is supplied, the difference has already been calculated, but we still need to adjust for the calendar. + # use copy-pasted functions from calendar module. (deprecated -- these functions will be removed from C4::Overdues ). + my $countspecialday = &GetSpecialHolidays($dues,$item->{itemnumber}); + my $countrepeatableday = &GetRepeatableHolidays($dues,$item->{itemnumber},$difference); + my $countalldayclosed = $countspecialday + $countrepeatableday; + $daystocharge = $difference - $countalldayclosed; + } else { + # if $difference is not supplied, we have C4::Dates objects giving us the date range, and we use the calendar module. + if(C4::Context->preference('finesCalendar') eq 'noFinesWhenClosed') { + my $calendar = C4::Calendar->new( branchcode => $branchcode ); + $daystocharge = $calendar->daysBetween( $start_date, $end_date ); + } else { + $daystocharge = Date_to_Days(split('-',$end_date->output('iso'))) - Date_to_Days(split('-',$start_date->output('iso'))); + } + } + # correct for grace period. + my $days_minus_grace = $daystocharge - $data->{'firstremind'}; + if ($data->{'chargeperiod'} > 0 && $days_minus_grace > 0 ) { + $amount = int($days_minus_grace / $data->{'chargeperiod'}) * $data->{'fine'}; } else { # a zero (or null) chargeperiod means no charge. - # } - -# warn "Calc Fine: " . join(", ", ($item->{'itemnumber'}, $bortype, $difference , $data->{'fine'} . " * " . $daycount . " days = \$ " . $amount , "desc: $dues")) ; - return ( $amount, $data->{'chargename'}, $printout ,$daycounttotal ,$daycount ); + $amount = C4::Context->preference('maxFine') if(C4::Context->preference('maxFine') && ( $amount > C4::Context->preference('maxFine'))); + $debug and warn sprintf("CalcFine returning (%s, %s, %s, %s)", $amount, $data->{'chargename'}, $days_minus_grace, $daystocharge); + return ($amount, $data->{'chargename'}, $days_minus_grace, $daystocharge); + # FIXME: chargename is NEVER populated anywhere. } -=item GetSpecialHolidays +=head2 GetSpecialHolidays &GetSpecialHolidays($date_dues,$itemnumber); @@ -295,7 +327,7 @@ my $specialdaycount=scalar(@result_date); return $specialdaycount; } -=item GetRepeatableHolidays +=head2 GetRepeatableHolidays &GetRepeatableHolidays($date_dues, $itemnumber, $difference,); @@ -333,7 +365,7 @@ return scalar(@dayclosedcount); } -=item GetWayFromItemnumber +=head2 GetWayFromItemnumber &Getwdayfromitemnumber($itemnumber); @@ -363,7 +395,7 @@ return @result; } -=item GetIssuesIteminfo +=head2 GetIssuesIteminfo &GetIssuesIteminfo($itemnumber); @@ -387,7 +419,7 @@ return $issuesinfo; } -=item UpdateFine +=head2 UpdateFine &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description); @@ -413,13 +445,17 @@ accountlines table of the Koha database. =cut -#' -# FIXME - This API doesn't look right: why should the caller have to +# +# Question: Why should the caller have to # specify both the item number and the borrower number? A book can't # be on loan to two different people, so the item number should be # sufficient. +# +# Possible Answer: You might update a fine for a damaged item, *after* it is returned. +# sub UpdateFine { my ( $itemnum, $borrowernumber, $amount, $type, $due ) = @_; + $debug and warn "UpdateFine($itemnum, $borrowernumber, $amount, " . ($type||'""') . ", $due) called"; my $dbh = C4::Context->dbh; # FIXME - What exactly is this query supposed to do? It looks up an # entry in accountlines that matches the given item and borrower @@ -427,48 +463,61 @@ sub UpdateFine { # account type has one of several values, but what does this _mean_? # Does it look up existing fines for this item? # FIXME - What are these various account types? ("FU", "O", "F", "M") + # "L" is LOST item + # "A" is Account Management Fee + # "N" is New Card + # "M" is Sundry + # "O" is Overdue ?? + # "F" is Fine ?? + # "FU" is Fine UPDATE?? + # "Pay" is Payment + # "REF" is Cash Refund my $sth = $dbh->prepare( - "Select * from accountlines where itemnumber=? and - borrowernumber=? and (accounttype='FU' or accounttype='O' or - accounttype='F' or accounttype='M') and description like ?" + "SELECT * FROM accountlines + WHERE itemnumber=? + AND borrowernumber=? + AND accounttype IN ('FU','O','F','M') + AND description like ? " ); $sth->execute( $itemnum, $borrowernumber, "%$due%" ); if ( my $data = $sth->fetchrow_hashref ) { - # I think this if-clause deals with the case where we're updating - # an existing fine. - # print "in accounts ..."; - if ( $data->{'amount'} != $amount ) { - - # print "updating"; + # we're updating an existing fine. Only modify if we're adding to the charge. + # Note that in the current implementation, you cannot pay against an accruing fine + # (i.e. , of accounttype 'FU'). Doing so will break accrual. + if ( $data->{'amount'} != $amount ) { my $diff = $amount - $data->{'amount'}; + $diff = 0 if ( $data->{amount} > $amount); my $out = $data->{'amountoutstanding'} + $diff; - my $sth2 = $dbh->prepare( - "UPDATE accountlines SET date=now(), amount=?, - amountoutstanding=?,accounttype='FU' WHERE - borrowernumber=? AND itemnumber=? - AND (accounttype='FU' OR accounttype='O') AND description LIKE ?" - ); - $sth2->execute( $amount, $out, $data->{'borrowernumber'}, - $data->{'itemnumber'}, "%$due%" ); - $sth2->finish; - } - else { - + my $query = " + UPDATE accountlines + SET date=now(), amount=?, amountoutstanding=?, + lastincrement=?, accounttype='FU' + WHERE borrowernumber=? + AND itemnumber=? + AND accounttype IN ('FU','O') + AND description LIKE ? + LIMIT 1 "; + my $sth2 = $dbh->prepare($query); + # FIXME: BOGUS query cannot ensure uniqueness w/ LIKE %x% !!! + # LIMIT 1 added to prevent multiple affected lines + # FIXME: accountlines table needs unique key!! Possibly a combo of borrowernumber and accountline. + # But actually, we should just have a regular autoincrementing PK and forget accountline, + # including the bogus getnextaccountno function (doesn't prevent conflict on simultaneous ops). + # FIXME: Why only 2 account types here? + $debug and print STDERR "UpdateFine query: $query\n" . + "w/ args: $amount, $out, $diff, $data->{'borrowernumber'}, $data->{'itemnumber'}, \"\%$due\%\"\n"; + $sth2->execute($amount, $out, $diff, $data->{'borrowernumber'}, $data->{'itemnumber'}, "%$due%"); + } else { # print "no update needed $data->{'amount'}" } - } - else { - - # I think this else-clause deals with the case where we're adding - # a new fine. + } else { my $sth4 = $dbh->prepare( "SELECT title FROM biblio LEFT JOIN items ON biblio.biblionumber=items.biblionumber WHERE items.itemnumber=?" ); $sth4->execute($itemnum); - my $title = $sth4->fetchrow_hashref; - $sth4->finish; + my $title = $sth4->fetchrow; # # print "not in account"; # my $sth3 = $dbh->prepare("Select max(accountno) from accountlines"); @@ -479,17 +528,14 @@ sub UpdateFine { # $sth3->finish; # $accountno[0]++; # begin transaction - my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber); - my $sth2 = $dbh->prepare( - "INSERT INTO accountlines - (borrowernumber,itemnumber,date,amount, - description,accounttype,amountoutstanding,accountno) VALUES - (?,?,now(),?,?,'FU',?,?)" - ); - $sth2->execute( $borrowernumber, $itemnum, $amount, - "$type $title->{'title'} $due", - $amount, $nextaccntno); - $sth2->finish; + my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber); + my $desc = ($type ? "$type " : '') . "$title $due"; # FIXEDME, avoid whitespace prefix on empty $type + my $query = "INSERT INTO accountlines + (borrowernumber,itemnumber,date,amount,description,accounttype,amountoutstanding,lastincrement,accountno) + VALUES (?,?,now(),?,?,'FU',?,?,?)"; + my $sth2 = $dbh->prepare($query); + $debug and print STDERR "UpdateFine query: $query\nw/ args: $borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno\n"; + $sth2->execute($borrowernumber, $itemnum, $amount, $desc, $amount, $amount, $nextaccntno); } # logging action &logaction( @@ -498,11 +544,9 @@ sub UpdateFine { $borrowernumber, "due=".$due." amount=".$amount." itemnumber=".$itemnum ) if C4::Context->preference("FinesLog"); - - $sth->finish; } -=item BorType +=head2 BorType $borrower = &BorType($borrowernumber); @@ -530,7 +574,7 @@ sub BorType { return ($data); } -=item ReplacementCost +=head2 ReplacementCost $cost = &ReplacementCost($itemnumber); @@ -552,7 +596,7 @@ sub ReplacementCost { return ( $data->{'replacementprice'} ); } -=item GetFine +=head2 GetFine $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber); @@ -574,15 +618,11 @@ sub GetFine { my $sth = $dbh->prepare($query); $sth->execute( $itemnum, $borrowernumber ); my $data = $sth->fetchrow_hashref(); - $sth->finish(); - $dbh->disconnect(); return ( $data->{'sum(amountoutstanding)'} ); } - - -=item GetIssuingRules +=head2 GetIssuingRules FIXME - This sub should be deprecated and removed. It ignores branch and defaults. @@ -601,6 +641,7 @@ category he or she belongs to. =cut sub GetIssuingRules { + warn "GetIssuingRules is deprecated: use GetIssuingRule from C4::Circulation instead."; my ($itemtype,$categorycode)=@_; my $dbh = C4::Context->dbh(); my $query=qq|SELECT * @@ -611,10 +652,7 @@ sub GetIssuingRules { my $sth = $dbh->prepare($query); # print $query; $sth->execute($itemtype,$categorycode); - my ($data) = $sth->fetchrow_hashref; - $sth->finish; -return ($data); - + return $sth->fetchrow_hashref; } @@ -630,13 +668,11 @@ sub ReplacementCost2 { my $sth = $dbh->prepare($query); $sth->execute( $itemnum, $borrowernumber ); my $data = $sth->fetchrow_hashref(); - $sth->finish(); - $dbh->disconnect(); return ( $data->{'amountoutstanding'} ); } -=item GetNextIdNotify +=head2 GetNextIdNotify ($result) = &GetNextIdNotify($reference); @@ -679,7 +715,7 @@ return $result; } -=item NumberNotifyId +=head2 NumberNotifyId (@notify) = &NumberNotifyId($borrowernumber); @@ -708,7 +744,7 @@ sub NumberNotifyId{ } -=item AmountNotify +=head2 AmountNotify ($totalnotify) = &AmountNotify($notifyid); @@ -735,7 +771,7 @@ sub AmountNotify{ } -=item GetNotifyId +=head2 GetNotifyId ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber); @@ -766,7 +802,7 @@ C<$notify_id> contains the file number for the borrower number nad item number } -=item CreateItemAccountLine +=head2 CreateItemAccountLine () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level); @@ -801,7 +837,7 @@ C<$level> contains the file level sub CreateItemAccountLine { my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_; my $dbh = C4::Context->dbh; - my $nextaccntno = getnextacctno($borrowernumber); + my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber); my $query= "INSERT into accountlines (borrowernumber,accountno,itemnumber,date,amount,description,accounttype,amountoutstanding,timestamp,notify_id,notify_level) VALUES @@ -813,7 +849,7 @@ C<$level> contains the file level $sth->finish; } -=item UpdateAccountLines +=head2 UpdateAccountLines () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber); @@ -865,7 +901,7 @@ if ($notify_id eq '') } -=item GetItems +=head2 GetItems ($items) = &GetItems($itemnumber); @@ -891,7 +927,7 @@ sub GetItems { return($items); } -=item GetOverdueDelays +=head2 GetOverdueDelays (@delays) = &GetOverdueDelays($categorycode); @@ -916,7 +952,28 @@ sub GetOverdueDelays { return(@delays); } -=item CheckAccountLineLevelInfo +=head2 GetBranchcodesWithOverdueRules + +=over 4 + +my @branchcodes = C4::Overdues::GetBranchcodesWithOverdueRules() + +returns a list of branch codes for branches with overdue rules defined. + +=back + +=cut + +sub GetBranchcodesWithOverdueRules { + my $dbh = C4::Context->dbh; + my $rqoverduebranches = $dbh->prepare("SELECT DISTINCT branchcode FROM overduerules WHERE delay1 IS NOT NULL AND branchcode <> ''"); + $rqoverduebranches->execute; + my @branches = map { shift @$_ } @{ $rqoverduebranches->fetchall_arrayref }; + $rqoverduebranches->finish; + return @branches; +} + +=head2 CheckAccountLineLevelInfo ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level); @@ -951,7 +1008,7 @@ sub CheckAccountLineLevelInfo { return($exist); } -=item GetOverduerules +=head2 GetOverduerules ($overduerules) = &GetOverduerules($categorycode); @@ -962,8 +1019,8 @@ C<$overduerules> return value of debbraed field in overduerules table C<$category> contains the borrower categorycode C<$notify_level> contains the notify level -=cut +=cut sub GetOverduerules{ my($category,$notify_level) = @_; @@ -979,7 +1036,7 @@ sub GetOverduerules{ } -=item CheckBorrowerDebarred +=head2 CheckBorrowerDebarred ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber); @@ -1010,7 +1067,7 @@ sub CheckBorrowerDebarred{ } } -=item UpdateBorrowerDebarred +=head2 UpdateBorrowerDebarred ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber); @@ -1033,7 +1090,7 @@ sub UpdateBorrowerDebarred{ return 1; } -=item CheckExistantNotifyid +=head2 CheckExistantNotifyid ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id); @@ -1068,7 +1125,7 @@ sub CheckExistantNotifyid { } } -=item CheckAccountLineItemInfo +=head2 CheckAccountLineItemInfo ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id); @@ -1110,17 +1167,17 @@ this function is not exported, only used with GetOverduesForBranch =cut sub CheckItemNotify { - my ($notify_id,$notify_level,$itemnumber) = @_; - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare(" - SELECT COUNT(*) FROM notifys - WHERE notify_id = ? - AND notify_level = ? - AND itemnumber = ? "); - $sth->execute($notify_id,$notify_level,$itemnumber); - my $notified = $sth->fetchrow; -$sth->finish; -return ($notified); + my ($notify_id,$notify_level,$itemnumber) = @_; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare(" + SELECT COUNT(*) + FROM notifys + WHERE notify_id = ? + AND notify_level = ? + AND itemnumber = ? "); + $sth->execute($notify_id,$notify_level,$itemnumber); + my $notified = $sth->fetchrow; + return ($notified); } =head2 GetOverduesForBranch @@ -1129,112 +1186,68 @@ Sql request for display all information for branchoverdues.pl 2 possibilities : with or without location . display is filtered by branch +FIXME: This function should be renamed. + =cut sub GetOverduesForBranch { my ( $branch, $location) = @_; my $itype_link = (C4::Context->preference('item-level_itypes')) ? " items.itype " : " biblioitems.itemtype "; - if ( not $location ) { - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare(" - SELECT - borrowers.surname, - borrowers.firstname, - biblio.title, - itemtypes.description, - issues.date_due, - issues.returndate, - branches.branchname, + my $dbh = C4::Context->dbh; + my $select = " + SELECT + borrowers.borrowernumber, + borrowers.surname, + borrowers.firstname, + borrowers.phone, + borrowers.email, + biblio.title, + biblio.biblionumber, + issues.date_due, + issues.returndate, + issues.branchcode, + branches.branchname, items.barcode, - borrowers.phone, - borrowers.email, items.itemcallnumber, - borrowers.borrowernumber, - items.itemnumber, - biblio.biblionumber, - issues.branchcode, - accountlines.notify_id, - accountlines.notify_level, items.location, - accountlines.amountoutstanding - FROM accountlines - LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber - LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber - LEFT JOIN items ON items.itemnumber = issues.itemnumber - LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber - LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber - LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link - LEFT JOIN branches ON branches.branchcode = issues.branchcode - WHERE ( accountlines.amountoutstanding != '0.000000') - AND ( accountlines.accounttype = 'FU') - AND (issues.branchcode = ?) - AND (issues.date_due <= NOW()) - ORDER BY borrowers.surname - "); - $sth->execute($branch); - my @getoverdues; - my $i = 0; - while ( my $data = $sth->fetchrow_hashref ) { - #check if the document has already been notified - my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'}); - if ($countnotify eq '0'){ + items.itemnumber, + itemtypes.description, + accountlines.notify_id, + accountlines.notify_level, + accountlines.amountoutstanding + FROM accountlines + LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber + AND issues.borrowernumber = accountlines.borrowernumber + LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber + LEFT JOIN items ON items.itemnumber = issues.itemnumber + LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber + LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link + LEFT JOIN branches ON branches.branchcode = issues.branchcode + WHERE (accountlines.amountoutstanding != '0.000000') + AND (accountlines.accounttype = 'FU' ) + AND (issues.branchcode = ? ) + AND (issues.date_due <= NOW()) + "; + my @getoverdues; + my $i = 0; + my $sth; + if ($location) { + $sth = $dbh->prepare("$select AND items.location = ? ORDER BY borrowers.surname, borrowers.firstname"); + $sth->execute($branch, $location); + } else { + $sth = $dbh->prepare("$select ORDER BY borrowers.surname, borrowers.firstname"); + $sth->execute($branch); + } + while ( my $data = $sth->fetchrow_hashref ) { + #check if the document has already been notified + my $countnotify = CheckItemNotify($data->{'notify_id'}, $data->{'notify_level'}, $data->{'itemnumber'}); + if ($countnotify eq '0') { $getoverdues[$i] = $data; $i++; - } - } - return (@getoverdues); - $sth->finish; - } - else { - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( " - SELECT borrowers.surname, - borrowers.firstname, - biblio.title, - itemtypes.description, - issues.date_due, - issues.returndate, - branches.branchname, - items.barcode, - borrowers.phone, - borrowers.email, - items.itemcallnumber, - borrowers.borrowernumber, - items.itemnumber, - biblio.biblionumber, - issues.branchcode, - accountlines.notify_id, - accountlines.notify_level, - items.location, - accountlines.amountoutstanding - FROM accountlines - LEFT JOIN issues ON issues.itemnumber = accountlines.itemnumber AND issues.borrowernumber = accountlines.borrowernumber - LEFT JOIN borrowers ON borrowers.borrowernumber = accountlines.borrowernumber - LEFT JOIN items ON items.itemnumber = issues.itemnumber - LEFT JOIN biblio ON biblio.biblionumber = items.biblionumber - LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber - LEFT JOIN itemtypes ON itemtypes.itemtype = $itype_link - LEFT JOIN branches ON branches.branchcode = issues.branchcode - WHERE ( accountlines.amountoutstanding != '0.000000') - AND ( accountlines.accounttype = 'FU') - AND (issues.branchcode = ? AND items.location = ?) - AND (issues.date_due <= NOW()) - ORDER BY borrowers.surname - " ); - $sth->execute( $branch, $location); - my @getoverdues; - my $i = 0; - while ( my $data = $sth->fetchrow_hashref ) { - #check if the document has already been notified - my $countnotify = CheckItemNotify($data->{'notify_id'},$data->{'notify_level'},$data->{'itemnumber'}); - if ($countnotify eq '0'){ - $getoverdues[$i] = $data; - $i++; - } } - $sth->finish; - return (@getoverdues); } + return (@getoverdues); } @@ -1297,8 +1310,6 @@ sub RemoveNotifyLine { 1; __END__ -=back - =head1 AUTHOR Koha Developement team