X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FOverdues.pm;h=04520b255db4bef370d8038ca45a25c1f6f8d3a8;hb=f6f80e1b8bd6302b17468286d868431d634ef9f8;hp=bacd80a722d89cfacabb3ce7a130839766855127;hpb=b22044aa97c35d8538b6084c914847dcea64ad8b;p=koha.git diff --git a/C4/Overdues.pm b/C4/Overdues.pm index bacd80a722..04520b255d 100644 --- a/C4/Overdues.pm +++ b/C4/Overdues.pm @@ -19,7 +19,7 @@ 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; @@ -116,21 +116,22 @@ Koha database. #' sub Getoverdues { my $params = shift; - my $dbh = C4::Context->dbh; my $statement; if ( C4::Context->preference('item-level_itypes') ) { $statement = " -SELECT issues.*,items.itype as itemtype, items.homebranch FROM issues -LEFT JOIN items USING (itemnumber) -WHERE date_due < now() + SELECT issues.*, items.itype as itemtype, items.homebranch, items.barcode + FROM issues +LEFT JOIN items USING (itemnumber) + WHERE date_due < CURDATE() "; } else { $statement = " -SELECT issues.*,biblioitems.itemtype,items.itype, items.homebranch FROM issues - LEFT JOIN items USING (itemnumber) - LEFT JOIN biblioitems USING (biblioitemnumber) - WHERE date_due < now() + 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 < CURDATE() "; } @@ -154,37 +155,26 @@ SELECT issues.*,biblioitems.itemtype,items.itype, items.homebranch FROM issues =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 < CURDATE()" ); - $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 } =head2 CalcFine @@ -237,11 +227,10 @@ or "Final Notice". But CalcFine never defined any value. =cut -#' sub CalcFine { 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'), + ($item ? '{item}' : 'UNDEF'), ($bortype || 'UNDEF'), ($branchcode || 'UNDEF'), ($difference || 'UNDEF'), @@ -253,7 +242,8 @@ sub CalcFine { my $amount = 0; my $daystocharge; # get issuingrules (fines part will be used) - my $data = C4::Circulation::GetIssuingRule($bortype, $item->{'itemtype'},$branchcode); + $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 ). @@ -264,7 +254,7 @@ sub CalcFine { } 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 ); + 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'))); @@ -273,12 +263,14 @@ sub CalcFine { # 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'}; + $amount = int($daystocharge / $data->{'chargeperiod'}) * $data->{'fine'}; } else { # a zero (or null) chargeperiod means no charge. } $amount = C4::Context->preference('maxFine') if(C4::Context->preference('maxFine') && ( $amount > C4::Context->preference('maxFine'))); - return ( $amount, $data->{'chargename'}, $days_minus_grace, $daystocharge); + $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. } @@ -295,44 +287,49 @@ C<$itemnumber> is the book's item number. =cut sub GetSpecialHolidays { -my ($date_dues,$itemnumber) = @_; -# calcul the today date -my $today = join "-", &Today(); - -# return the holdingbranch -my $iteminfo=GetIssuesIteminfo($itemnumber); -# use sql request to find all date between date_due and today -my $dbh = C4::Context->dbh; -my $query=qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d')as date + my ( $date_dues, $itemnumber ) = @_; + + # calcul the today date + my $today = join "-", &Today(); + + # return the holdingbranch + my $iteminfo = GetIssuesIteminfo($itemnumber); + + # use sql request to find all date between date_due and today + my $dbh = C4::Context->dbh; + my $query = + qq|SELECT DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') as date FROM `special_holidays` WHERE DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') >= ? AND DATE_FORMAT(concat(year,'-',month,'-',day),'%Y-%m-%d') <= ? AND branchcode=? |; -my @result=GetWdayFromItemnumber($itemnumber); -my @result_date; -my $wday; -my $dateinsec; -my $sth = $dbh->prepare($query); -$sth->execute($date_dues,$today,$iteminfo->{'branchcode'}); - -while ( my $special_date=$sth->fetchrow_hashref){ - push (@result_date,$special_date); -} + my @result = GetWdayFromItemnumber($itemnumber); + my @result_date; + my $wday; + my $dateinsec; + my $sth = $dbh->prepare($query); + $sth->execute( $date_dues, $today, $iteminfo->{'branchcode'} ) + ; # FIXME: just use NOW() in SQL instead of passing in $today -my $specialdaycount=scalar(@result_date); + while ( my $special_date = $sth->fetchrow_hashref ) { + push( @result_date, $special_date ); + } - for (my $i=0;$i{'date'},"%o"); - (undef,undef,undef,undef,undef,undef,$wday,undef,undef) =localtime($dateinsec); - for (my $j=0;$j{'weekday'})){ - $specialdaycount --; + my $specialdaycount = scalar(@result_date); + + for ( my $i = 0 ; $i < scalar(@result_date) ; $i++ ) { + $dateinsec = UnixDate( $result_date[$i]->{'date'}, "%o" ); + ( undef, undef, undef, undef, undef, undef, $wday, undef, undef ) = + localtime($dateinsec); + for ( my $j = 0 ; $j < scalar(@result) ; $j++ ) { + if ( $wday == ( $result[$j]->{'weekday'} ) ) { + $specialdaycount--; } } } -return $specialdaycount; + return $specialdaycount; } =head2 GetRepeatableHolidays @@ -349,27 +346,27 @@ C<$difference> numbers of between day date of the day and date due =cut -sub GetRepeatableHolidays{ -my ($date_dues,$itemnumber,$difference) = @_; -my $dateinsec=UnixDate($date_dues,"%o"); -my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =localtime($dateinsec); -my @result=GetWdayFromItemnumber($itemnumber); -my @dayclosedcount; -my $j; - -for (my $i=0;$i{'weekday'} == $k) - { - push ( @dayclosedcount ,$k); +sub GetRepeatableHolidays { + my ( $date_dues, $itemnumber, $difference ) = @_; + my $dateinsec = UnixDate( $date_dues, "%o" ); + my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) = + localtime($dateinsec); + my @result = GetWdayFromItemnumber($itemnumber); + my @dayclosedcount; + my $j; + + for ( my $i = 0 ; $i < scalar(@result) ; $i++ ) { + my $k = $wday; + + for ( $j = 0 ; $j < $difference ; $j++ ) { + if ( $result[$i]->{'weekday'} == $k ) { + push( @dayclosedcount, $k ); } - $k++; - ($k=0) if($k eq 7); + $k++; + ( $k = 0 ) if ( $k eq 7 ); } } -return scalar(@dayclosedcount); + return scalar(@dayclosedcount); } @@ -383,23 +380,21 @@ C<$itemnumber> is item number. =cut -sub GetWdayFromItemnumber{ -my($itemnumber)=@_; -my $iteminfo=GetIssuesIteminfo($itemnumber); -my @result; -my $dbh = C4::Context->dbh; -my $query = qq|SELECT weekday +sub GetWdayFromItemnumber { + my ($itemnumber) = @_; + my $iteminfo = GetIssuesIteminfo($itemnumber); + my @result; + my $query = qq|SELECT weekday FROM repeatable_holidays WHERE branchcode=? |; -my $sth = $dbh->prepare($query); - # print $query; + my $sth = C4::Context->dbh->prepare($query); -$sth->execute($iteminfo->{'branchcode'}); -while ( my $weekday=$sth->fetchrow_hashref){ - push (@result,$weekday); + $sth->execute( $iteminfo->{'branchcode'} ); + while ( my $weekday = $sth->fetchrow_hashref ) { + push( @result, $weekday ); } -return @result; + return @result; } @@ -413,17 +408,17 @@ C<$itemnumber> is item number. =cut -sub GetIssuesIteminfo{ -my($itemnumber)=@_; -my $dbh = C4::Context->dbh; -my $query = qq|SELECT * +sub GetIssuesIteminfo { + my ($itemnumber) = @_; + my $dbh = C4::Context->dbh; + my $query = qq|SELECT * FROM issues WHERE itemnumber=? -|; -my $sth = $dbh->prepare($query); -$sth->execute($itemnumber); -my ($issuesinfo)=$sth->fetchrow_hashref; -return $issuesinfo; + |; + my $sth = $dbh->prepare($query); + $sth->execute($itemnumber); + my ($issuesinfo) = $sth->fetchrow_hashref; + return $issuesinfo; } @@ -481,7 +476,7 @@ sub UpdateFine { # "Pay" is Payment # "REF" is Cash Refund my $sth = $dbh->prepare( - "SELECT * FROM accountlines + "SELECT * FROM accountlines WHERE itemnumber=? AND borrowernumber=? AND accounttype IN ('FU','O','F','M') @@ -572,14 +567,12 @@ sub BorType { my ($borrowernumber) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( - "SELECT * from borrowers + "SELECT * from borrowers LEFT JOIN categories ON borrowers.categorycode=categories.categorycode WHERE borrowernumber=?" ); $sth->execute($borrowernumber); - my $data = $sth->fetchrow_hashref; - $sth->finish; - return ($data); + return $sth->fetchrow_hashref; } =head2 ReplacementCost @@ -598,9 +591,8 @@ sub ReplacementCost { $dbh->prepare("Select replacementprice from items where itemnumber=?"); $sth->execute($itemnum); - # FIXME - Use fetchrow_array or something. + # FIXME - Use fetchrow_array or a slice. my $data = $sth->fetchrow_hashref; - $sth->finish; return ( $data->{'replacementprice'} ); } @@ -620,7 +612,7 @@ C<$borrowernumber> is the borrowernumber sub GetFine { my ( $itemnum, $borrowernumber ) = @_; my $dbh = C4::Context->dbh(); - my $query = "SELECT sum(amountoutstanding) FROM accountlines + my $query = "SELECT sum(amountoutstanding) FROM accountlines where accounttype like 'F%' AND amountoutstanding > 0 AND itemnumber = ? AND borrowernumber=?"; my $sth = $dbh->prepare($query); @@ -652,7 +644,7 @@ sub GetIssuingRules { warn "GetIssuingRules is deprecated: use GetIssuingRule from C4::Circulation instead."; my ($itemtype,$categorycode)=@_; my $dbh = C4::Context->dbh(); - my $query=qq|SELECT * + my $query=qq|SELECT * FROM issuingrules WHERE issuingrules.itemtype=? AND issuingrules.categorycode=? @@ -667,7 +659,7 @@ sub GetIssuingRules { sub ReplacementCost2 { my ( $itemnum, $borrowernumber ) = @_; my $dbh = C4::Context->dbh(); - my $query = "SELECT amountoutstanding + my $query = "SELECT amountoutstanding FROM accountlines WHERE accounttype like 'L' AND amountoutstanding > 0 @@ -692,36 +684,32 @@ C<$reference> contains the beggining of file number =cut - - sub GetNextIdNotify { -my ($reference)=@_; -my $query=qq|SELECT max(notify_id) + my ($reference) = @_; + my $query = qq|SELECT max(notify_id) FROM accountlines WHERE notify_id like \"$reference%\" |; -# AND borrowernumber=?|; -my $dbh = C4::Context->dbh; -my $sth=$dbh->prepare($query); -$sth->execute(); -my $result=$sth->fetchrow; -$sth->finish; -my $count; - if ($result eq '') - { - ($result=$reference."01") ; - }else - { - $count=substr($result,6)+1; - - if($count<10){ - ($count = "0".$count); - } - $result=$reference.$count; - } -return $result; -} + # AND borrowernumber=?|; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute(); + my $result = $sth->fetchrow; + my $count; + if ( $result eq '' ) { + ( $result = $reference . "01" ); + } + else { + $count = substr( $result, 6 ) + 1; + + if ( $count < 10 ) { + ( $count = "0" . $count ); + } + $result = $reference . $count; + } + return $result; +} =head2 NumberNotifyId @@ -741,15 +729,12 @@ sub NumberNotifyId{ FROM accountlines WHERE borrowernumber=?|; my @notify; - my $sth=$dbh->prepare($query); - $sth->execute($borrowernumber); - while ( my ($numberofnotify)=$sth->fetchrow){ - push (@notify,$numberofnotify); + my $sth = $dbh->prepare($query); + $sth->execute($borrowernumber); + while ( my ($numberofnotify) = $sth->fetchrow ) { + push( @notify, $numberofnotify ); } - $sth->finish; - return (@notify); - } =head2 AmountNotify @@ -794,21 +779,20 @@ C<$notify_id> contains the file number for the borrower number nad item number =cut - sub GetNotifyId { - my ($borrowernumber,$itemnumber)=@_; - my $query=qq|SELECT notify_id +sub GetNotifyId { + my ( $borrowernumber, $itemnumber ) = @_; + my $query = qq|SELECT notify_id FROM accountlines WHERE borrowernumber=? AND itemnumber=? AND (accounttype='FU' or accounttype='O')|; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare($query); - $sth->execute($borrowernumber,$itemnumber); - my ($notify_id)=$sth->fetchrow; - $sth->finish; - return ($notify_id); - - } + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute( $borrowernumber, $itemnumber ); + my ($notify_id) = $sth->fetchrow; + $sth->finish; + return ($notify_id); +} =head2 CreateItemAccountLine @@ -839,23 +823,29 @@ C<$notify_id> contains the file number C<$level> contains the file level - =cut - sub CreateItemAccountLine { - my ($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level)=@_; - my $dbh = C4::Context->dbh; - my $nextaccntno = C4::Accounts::getnextacctno($borrowernumber); - my $query= "INSERT into accountlines +sub CreateItemAccountLine { + my ( + $borrowernumber, $itemnumber, $date, $amount, + $description, $accounttype, $amountoutstanding, $timestamp, + $notify_id, $level + ) = @_; + my $dbh = C4::Context->dbh; + 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 (?,?,?,?,?,?,?,?,?,?,?)"; - - - my $sth=$dbh->prepare($query); - $sth->execute($borrowernumber,$nextaccntno,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level); - $sth->finish; - } + + my $sth = $dbh->prepare($query); + $sth->execute( + $borrowernumber, $nextaccntno, $itemnumber, + $date, $amount, $description, + $accounttype, $amountoutstanding, $timestamp, + $notify_id, $level + ); +} =head2 UpdateAccountLines @@ -877,38 +867,29 @@ C<$borrowernumber> contains the borrowernumber =cut sub UpdateAccountLines { -my ($notify_id,$notify_level,$borrowernumber,$itemnumber)=@_; -my $query; -if ($notify_id eq '') -{ - - $query=qq|UPDATE accountlines + my ( $notify_id, $notify_level, $borrowernumber, $itemnumber ) = @_; + my $query; + if ( $notify_id eq '' ) { + $query = qq|UPDATE accountlines SET notify_level=? WHERE borrowernumber=? AND itemnumber=? AND (accounttype='FU' or accounttype='O')|; -}else -{ - $query=qq|UPDATE accountlines + } else { + $query = qq|UPDATE accountlines SET notify_id=?, notify_level=? - WHERE borrowernumber=? + WHERE borrowernumber=? AND itemnumber=? - AND (accounttype='FU' or accounttype='O')|; -} - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare($query); - -if ($notify_id eq '') -{ - $sth->execute($notify_level,$borrowernumber,$itemnumber); -}else -{ - $sth->execute($notify_id,$notify_level,$borrowernumber,$itemnumber); -} - $sth->finish; + AND (accounttype='FU' or accounttype='O')|; + } + my $sth = C4::Context->dbh->prepare($query); + if ( $notify_id eq '' ) { + $sth->execute( $notify_level, $borrowernumber, $itemnumber ); + } else { + $sth->execute( $notify_id, $notify_level, $borrowernumber, $itemnumber ); + } } - =head2 GetItems ($items) = &GetItems($itemnumber); @@ -922,17 +903,20 @@ C<$itemnumber> contains the borrower categorycode =cut +# FIXME: This is a bad function to have here. +# Shouldn't it be in C4::Items? +# Shouldn't it be called GetItem since you only get 1 row? +# Shouldn't it be called GetItem since you give it only 1 itemnumber? + sub GetItems { - my($itemnumber) = @_; - my $query=qq|SELECT * + my $itemnumber = shift or return; + my $query = qq|SELECT * FROM items WHERE itemnumber=?|; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare($query); - $sth->execute($itemnumber); - my ($items)=$sth->fetchrow_hashref; - $sth->finish; - return($items); + my $sth = C4::Context->dbh->prepare($query); + $sth->execute($itemnumber); + my ($items) = $sth->fetchrow_hashref; + return ($items); } =head2 GetOverdueDelays @@ -948,16 +932,14 @@ C<$categorycode> contains the borrower categorycode =cut sub GetOverdueDelays { - my($category) = @_; - my $dbh = C4::Context->dbh; - my $query=qq|SELECT delay1,delay2,delay3 + my ($category) = @_; + my $query = qq|SELECT delay1,delay2,delay3 FROM overduerules WHERE categorycode=?|; - my $sth=$dbh->prepare($query); - $sth->execute($category); - my (@delays)=$sth->fetchrow_array; - $sth->finish; - return(@delays); + my $sth = C4::Context->dbh->prepare($query); + $sth->execute($category); + my (@delays) = $sth->fetchrow_array; + return (@delays); } =head2 GetBranchcodesWithOverdueRules @@ -977,7 +959,6 @@ sub GetBranchcodesWithOverdueRules { 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; } @@ -1002,18 +983,17 @@ C<$notify_level> contains the accountline level =cut sub CheckAccountLineLevelInfo { - my($borrowernumber,$itemnumber,$level) = @_; - my $dbh = C4::Context->dbh; - my $query= qq|SELECT count(*) + my ( $borrowernumber, $itemnumber, $level ) = @_; + my $dbh = C4::Context->dbh; + my $query = qq|SELECT count(*) FROM accountlines WHERE borrowernumber =? AND itemnumber = ? AND notify_level=?|; - my $sth=$dbh->prepare($query); - $sth->execute($borrowernumber,$itemnumber,$level); - my ($exist)=$sth->fetchrow; - $sth->finish; - return($exist); + my $sth = $dbh->prepare($query); + $sth->execute( $borrowernumber, $itemnumber, $level ); + my ($exist) = $sth->fetchrow; + return ($exist); } =head2 GetOverduerules @@ -1030,17 +1010,16 @@ C<$notify_level> contains the notify level =cut -sub GetOverduerules{ - my($category,$notify_level) = @_; - my $dbh = C4::Context->dbh; - my $query=qq|SELECT debarred$notify_level - FROM overduerules - WHERE categorycode=?|; - my $sth=$dbh->prepare($query); - $sth->execute($category); - my ($overduerules)=$sth->fetchrow; - $sth->finish; - return($overduerules); +sub GetOverduerules { + my ( $category, $notify_level ) = @_; + my $dbh = C4::Context->dbh; + my $query = qq|SELECT debarred$notify_level + FROM overduerules + WHERE categorycode=?|; + my $sth = $dbh->prepare($query); + $sth->execute($category); + my ($overduerules) = $sth->fetchrow; + return ($overduerules); } @@ -1056,23 +1035,19 @@ C<$borrowernumber> contains the borrower number =cut - -sub CheckBorrowerDebarred{ - my($borrowernumber) = @_; - my $dbh = C4::Context->dbh; - my $query=qq|SELECT debarred - FROM borrowers - WHERE borrowernumber=? - |; - my $sth=$dbh->prepare($query); - $sth->execute($borrowernumber); - my ($debarredstatus)=$sth->fetchrow; - $sth->finish; - if ($debarredstatus eq '1'){ - return(1);} - else{ - return(0); - } +# FIXME: Shouldn't this be in C4::Members? +sub CheckBorrowerDebarred { + my ($borrowernumber) = @_; + my $dbh = C4::Context->dbh; + my $query = qq| + SELECT debarred + FROM borrowers + WHERE borrowernumber=? + |; + my $sth = $dbh->prepare($query); + $sth->execute($borrowernumber); + my ($debarredstatus) = $sth->fetchrow; + return ( $debarredstatus eq '1' ? 1 : 0 ); } =head2 UpdateBorrowerDebarred @@ -1114,23 +1089,15 @@ C<$date_due> contains the date of item return =cut sub CheckExistantNotifyid { - my($borrowernumber,$date_due) = @_; - my $dbh = C4::Context->dbh; - my $query = qq|SELECT notify_id FROM accountlines + my ( $borrowernumber, $date_due ) = @_; + my $dbh = C4::Context->dbh; + my $query = qq|SELECT notify_id FROM accountlines LEFT JOIN issues ON issues.itemnumber= accountlines.itemnumber WHERE accountlines.borrowernumber =? AND date_due = ?|; - my $sth=$dbh->prepare($query); - $sth->execute($borrowernumber,$date_due); - my ($exist)=$sth->fetchrow; - $sth->finish; - if ($exist eq '') - { - return(0); - }else - { - return($exist); - } + my $sth = $dbh->prepare($query); + $sth->execute( $borrowernumber, $date_due ); + return $sth->fetchrow || 0; } =head2 CheckAccountLineItemInfo @@ -1153,19 +1120,18 @@ C<$notify_id> contains the file number =cut sub CheckAccountLineItemInfo { - my($borrowernumber,$itemnumber,$accounttype,$notify_id) = @_; - my $dbh = C4::Context->dbh; - my $query = qq|SELECT count(*) FROM accountlines + my ( $borrowernumber, $itemnumber, $accounttype, $notify_id ) = @_; + my $dbh = C4::Context->dbh; + my $query = qq|SELECT count(*) FROM accountlines WHERE borrowernumber =? AND itemnumber = ? AND accounttype= ? AND notify_id = ?|; - my $sth=$dbh->prepare($query); - $sth->execute($borrowernumber,$itemnumber,$accounttype,$notify_id); - my ($exist)=$sth->fetchrow; - $sth->finish; - return($exist); - } + my $sth = $dbh->prepare($query); + $sth->execute( $borrowernumber, $itemnumber, $accounttype, $notify_id ); + my ($exist) = $sth->fetchrow; + return ($exist); +} =head2 CheckItemNotify @@ -1175,17 +1141,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 @@ -1194,112 +1160,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 < CURDATE()) + "; + 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); } @@ -1313,25 +1235,22 @@ Creat a line into notify, if the method is phone, the notification_send_date is sub AddNotifyLine { my ( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ) = @_; + my $dbh = C4::Context->dbh; if ( $method eq "phone" ) { - my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_send_date,notify_level,method,notify_id) VALUES (?,?,now(),now(),?,?,?)" ); $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ); - $sth->finish; } else { - my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( "INSERT INTO notifys (borrowernumber,itemnumber,notify_date,notify_level,method,notify_id) VALUES (?,?,now(),?,?,?)" ); $sth->execute( $borrowernumber, $itemnumber, $overduelevel, $method, $notifyId ); - $sth->finish; } return 1; } @@ -1355,7 +1274,6 @@ sub RemoveNotifyLine { AND notify_date=?" ); $sth->execute( $borrowernumber, $itemnumber, $notify_date ); - $sth->finish; return 1; }