[Koha-patches] [PATCH] Bug 2660: Prevent duplication of overdue notices.

[Ryan Higgins]

This limits generation of notices to days that an item triggers
the overdue action.  Previously if a patron had any item overdue
within the range of dates between two overdue triggers, a notice
would be sent.
---
 misc/cronjobs/overdue_notices.pl |   24 ++++++++++++++----------
 1 files changed, 14 insertions(+), 10 deletions(-)

diff --git a/misc/cronjobs/overdue_notices.pl b/misc/cronjobs/overdue_notices.pl
index 225a41f..c937a4e 100755
--- a/misc/cronjobs/overdue_notices.pl
+++ b/misc/cronjobs/overdue_notices.pl
@@ -285,7 +285,7 @@ foreach my $branchcode (@branches) {
     $verbose and warn sprintf "branchcode : '%s' using %s\n", $branchcode, $admin_email_address;
 
     my $sth2 = $dbh->prepare( <<'END_SQL' );
-SELECT biblio.*, items.*, issues.*
+SELECT biblio.*, items.*, issues.*, TO_DAYS(NOW())-TO_DAYS(date_due) AS days_overdue
   FROM issues,items,biblio
   WHERE items.itemnumber=issues.itemnumber
     AND biblio.biblionumber   = items.biblionumber
@@ -315,10 +315,11 @@ END_SQL
             # $letter->{'content'} is the text of the mail that is sent.
             # this text contains fields that are replaced by their value. Those fields must be written between brackets
             # The following fields are available :
+	    # itemcount is interpreted here as the number of items in the overdue range defined by the current notice.
             # <date> <itemcount> <firstname> <lastname> <address1> <address2> <address3> <city> <postcode>
 
             my $borrower_sql = <<'END_SQL';
-SELECT COUNT(*), issues.borrowernumber, firstname, surname, address, address2, city, zipcode, email, MIN(date_due) as longest_issue
+SELECT issues.borrowernumber, firstname, surname, address, address2, city, zipcode, email
 FROM   issues,borrowers,categories
 WHERE  issues.borrowernumber=borrowers.borrowernumber
 AND    borrowers.categorycode=categories.categorycode
@@ -333,17 +334,18 @@ END_SQL
                 push @borrower_parameters, $overdue_rules->{categorycode};
             }
             $borrower_sql .= <<'END_SQL';
-AND    categories.overduenoticerequired=1
-GROUP BY issues.borrowernumber
-HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) BETWEEN ? and ?
+AND categories.overduenoticerequired=1
+AND TO_DAYS(NOW())-TO_DAYS(date_due) = ?
 END_SQL
-            push @borrower_parameters, $mindays, $maxdays;
-            my $sth = $dbh->prepare($borrower_sql);
+
+            push @borrower_parameters, $mindays;
+            # $sth gets borrower info iff at least one overdue item has triggered the overdue action.
+	        my $sth = $dbh->prepare($borrower_sql);
             $sth->execute(@borrower_parameters);
-            $verbose and warn $borrower_sql . "\n\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows";
+            $verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows";
 
-            while ( my ( $itemcount, $borrowernumber, $firstname, $lastname, $address1, $address2, $city, $postcode, $email ) = $sth->fetchrow ) {
-                warn "borrower $firstname, $lastname ($borrowernumber) has $itemcount items overdue." if $verbose;
+            while ( my ( $borrowernumber, $firstname, $lastname, $address1, $address2, $city, $postcode, $email ) = $sth->fetchrow ) {
+                warn "borrower $firstname, $lastname ($borrowernumber) has an item triggering level $i." if $verbose;
 
                 my $letter = C4::Letters::getletter( 'circulation', $overdue_rules->{"letter$i"} );
                 unless ($letter) {
@@ -361,10 +363,12 @@ END_SQL
                 }
 
                 $sth2->execute( $borrowernumber, $mindays, $maxdays );
+		my $itemcount = 0;
                 my $titles = "";
                 while ( my $item_info = $sth2->fetchrow_hashref() ) {
                     my @item_info = map { $_ =~ /date$/ ? format_date( $item_info->{$_} ) : $item_info->{$_} || '' } @item_content_fields;
                     $titles .= join("\t", @item_info) . "\n";
+		    $itemcount++;
                 }
                 $sth2->finish;
 
-- 
1.5.5.GIT