X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FOverdues.pm;h=f67f16a4a3c56eda4ce52b89b73773c5d89e7c02;hb=0c1d86c939fc89033cd2a13b8c03fe6cdc284a4d;hp=e735ff9979effb6dfb3295373ca415a87b9298ef;hpb=83a63829463bcb005de9a9f94b2847c9f91cd77b;p=koha.git diff --git a/C4/Overdues.pm b/C4/Overdues.pm index e735ff9979..f67f16a4a3 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; @@ -101,9 +101,7 @@ overdue items. It is primarily used by the 'misc/fines2.pl' script. =head1 FUNCTIONS -=over 2 - -=item Getoverdues +=head2 Getoverdues $overdues = Getoverdues( { minimumdays => 1, maximumdays => 30 } ); @@ -118,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 < now() "; } 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 < now() "; } @@ -156,40 +155,29 @@ 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 } -=item CalcFine +=head2 CalcFine ($amount, $chargename, $daycount, $daycounttotal) = &CalcFine($item, $categorycode, $branch, $days_overdue, $description, $start_date, $end_date ); @@ -239,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'), @@ -255,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 ). @@ -266,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'))); @@ -280,11 +268,13 @@ sub CalcFine { # 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. } -=item GetSpecialHolidays +=head2 GetSpecialHolidays &GetSpecialHolidays($date_dues,$itemnumber); @@ -297,47 +287,52 @@ 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; } -=item GetRepeatableHolidays +=head2 GetRepeatableHolidays &GetRepeatableHolidays($date_dues, $itemnumber, $difference,); @@ -351,31 +346,31 @@ 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); } -=item GetWayFromItemnumber +=head2 GetWayFromItemnumber &Getwdayfromitemnumber($itemnumber); @@ -385,27 +380,25 @@ 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; } -=item GetIssuesIteminfo +=head2 GetIssuesIteminfo &GetIssuesIteminfo($itemnumber); @@ -415,21 +408,21 @@ 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; } -=item UpdateFine +=head2 UpdateFine &UpdateFine($itemnumber, $borrowernumber, $amount, $type, $description); @@ -483,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') @@ -493,9 +486,12 @@ sub UpdateFine { if ( my $data = $sth->fetchrow_hashref ) { - # we're updating an existing fine. + # 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 $query = " UPDATE accountlines @@ -553,7 +549,7 @@ sub UpdateFine { ) if C4::Context->preference("FinesLog"); } -=item BorType +=head2 BorType $borrower = &BorType($borrowernumber); @@ -571,17 +567,15 @@ 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; } -=item ReplacementCost +=head2 ReplacementCost $cost = &ReplacementCost($itemnumber); @@ -597,13 +591,12 @@ 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'} ); } -=item GetFine +=head2 GetFine $data->{'sum(amountoutstanding)'} = &GetFine($itemnum,$borrowernumber); @@ -619,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); @@ -629,7 +622,7 @@ sub GetFine { } -=item GetIssuingRules +=head2 GetIssuingRules FIXME - This sub should be deprecated and removed. It ignores branch and defaults. @@ -651,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=? @@ -666,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 @@ -679,7 +672,7 @@ sub ReplacementCost2 { } -=item GetNextIdNotify +=head2 GetNextIdNotify ($result) = &GetNextIdNotify($reference); @@ -691,38 +684,34 @@ 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; -=item NumberNotifyId + if ( $count < 10 ) { + ( $count = "0" . $count ); + } + $result = $reference . $count; + } + return $result; +} + +=head2 NumberNotifyId (@notify) = &NumberNotifyId($borrowernumber); @@ -740,18 +729,15 @@ 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); - } -=item AmountNotify +=head2 AmountNotify ($totalnotify) = &AmountNotify($notifyid); @@ -778,7 +764,7 @@ sub AmountNotify{ } -=item GetNotifyId +=head2 GetNotifyId ($notify_id) = &GetNotifyId($borrowernumber,$itemnumber); @@ -793,23 +779,22 @@ 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); +} -=item CreateItemAccountLine +=head2 CreateItemAccountLine () = &CreateItemAccountLine($borrowernumber,$itemnumber,$date,$amount,$description,$accounttype,$amountoutstanding,$timestamp,$notify_id,$level); @@ -838,25 +823,31 @@ 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; - } -=item UpdateAccountLines + my $sth = $dbh->prepare($query); + $sth->execute( + $borrowernumber, $nextaccntno, $itemnumber, + $date, $amount, $description, + $accounttype, $amountoutstanding, $timestamp, + $notify_id, $level + ); +} + +=head2 UpdateAccountLines () = &UpdateAccountLines($notify_id,$notify_level,$borrowernumber,$itemnumber); @@ -876,39 +867,30 @@ 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 ); + } } - -=item GetItems +=head2 GetItems ($items) = &GetItems($itemnumber); @@ -921,20 +903,23 @@ 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); } -=item GetOverdueDelays +=head2 GetOverdueDelays (@delays) = &GetOverdueDelays($categorycode); @@ -947,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 @@ -974,13 +957,24 @@ returns a list of branch codes for branches with overdue rules defined. sub GetBranchcodesWithOverdueRules { my $dbh = C4::Context->dbh; my $rqoverduebranches = $dbh->prepare("SELECT DISTINCT branchcode FROM overduerules WHERE delay1 IS NOT NULL AND branchcode <> ''"); + my $availbranches = C4::Branch::GetBranches(); + $rqoverduebranches->execute; my @branches = map { shift @$_ } @{ $rqoverduebranches->fetchall_arrayref }; - $rqoverduebranches->finish; + + my $defaultbranches = $dbh->prepare("SELECT DISTINCT branchcode FROM overduerules WHERE delay1 IS NOT NULL AND branchcode = ''"); + $defaultbranches->execute(); + if($defaultbranches->rows > 0){ + foreach my $branch (keys %$availbranches){ + if(not grep{/^$branch$/} @branches){ + push @branches, $branch; + } + } + } return @branches; } -=item CheckAccountLineLevelInfo +=head2 CheckAccountLineLevelInfo ($exist) = &CheckAccountLineLevelInfo($borrowernumber,$itemnumber,$accounttype,notify_level); @@ -1001,21 +995,20 @@ 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); } -=item GetOverduerules +=head2 GetOverduerules ($overduerules) = &GetOverduerules($categorycode); @@ -1026,24 +1019,23 @@ 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) = @_; - 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); } -=item CheckBorrowerDebarred +=head2 CheckBorrowerDebarred ($debarredstatus) = &CheckBorrowerDebarred($borrowernumber); @@ -1055,26 +1047,22 @@ 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 ); } -=item UpdateBorrowerDebarred +=head2 UpdateBorrowerDebarred ($borrowerstatut) = &UpdateBorrowerDebarred($borrowernumber); @@ -1097,7 +1085,7 @@ sub UpdateBorrowerDebarred{ return 1; } -=item CheckExistantNotifyid +=head2 CheckExistantNotifyid ($exist) = &CheckExistantNotifyid($borrowernumber,$itemnumber,$accounttype,$notify_id); @@ -1113,26 +1101,18 @@ 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; } -=item CheckAccountLineItemInfo +=head2 CheckAccountLineItemInfo ($exist) = &CheckAccountLineItemInfo($borrowernumber,$itemnumber,$accounttype,$notify_id); @@ -1152,19 +1132,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 @@ -1174,17 +1153,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 @@ -1193,112 +1172,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); } @@ -1312,25 +1247,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; } @@ -1354,15 +1286,12 @@ sub RemoveNotifyLine { AND notify_date=?" ); $sth->execute( $borrowernumber, $itemnumber, $notify_date ); - $sth->finish; return 1; } 1; __END__ -=back - =head1 AUTHOR Koha Developement team