From 6deac5f50f1dc6f7e07ea5a1be8b7bb80e4a6bd5 Mon Sep 17 00:00:00 2001 From: Chris Cormack Date: Tue, 3 May 2011 12:13:28 +1200 Subject: [PATCH] Bug 6292 : Overdue notices not being generated when borrower had an overdue older than the max value of the notice triggers Squashed commit of the following: commit 5f81773013c133b2ccc56e73d6ca69195304665c Author: Chris Cormack Date: Tue May 3 12:03:11 2011 +1200 Bug 6292 continuing fix commit 0e1895deea4647d6675781c4676623447713f1fa Author: Chris Cormack Date: Tue May 3 11:48:11 2011 +1200 First attempt to fix overdues --- misc/cronjobs/overdue_notices.pl | 13 ++++++------- 1 files changed, 6 insertions(+), 7 deletions(-) diff --git a/misc/cronjobs/overdue_notices.pl b/misc/cronjobs/overdue_notices.pl index bc97ecb..42ff03b 100755 --- a/misc/cronjobs/overdue_notices.pl +++ b/misc/cronjobs/overdue_notices.pl @@ -425,7 +425,7 @@ END_SQL # my $borrower_sql = <<'END_SQL'; -SELECT COUNT(*), issues.borrowernumber, firstname, surname, address, address2, city, zipcode, country, email, MIN(date_due) as longest_issue +SELECT distinct(issues.borrowernumber), firstname, surname, address, address2, city, zipcode, country, email, date_due FROM issues,borrowers,categories WHERE issues.borrowernumber=borrowers.borrowernumber AND borrowers.categorycode=categories.categorycode @@ -439,13 +439,12 @@ END_SQL $borrower_sql .= ' AND borrowers.categorycode=? '; push @borrower_parameters, $overdue_rules->{categorycode}; } - $borrower_sql .= ' AND categories.overduenoticerequired=1 - GROUP BY issues.borrowernumber '; + $borrower_sql .= ' AND categories.overduenoticerequired=1 '; if($triggered) { - $borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) = ?'; + $borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(date_due) = ?'; push @borrower_parameters, $mindays; } else { - $borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(longest_issue) BETWEEN ? and ? ' ; + $borrower_sql .= ' HAVING TO_DAYS(NOW())-TO_DAYS(date_due) BETWEEN ? and ? ' ; push @borrower_parameters, $mindays, $maxdays; } @@ -454,9 +453,9 @@ END_SQL $sth->execute(@borrower_parameters); $verbose and warn $borrower_sql . "\n $branchcode | " . $overdue_rules->{'categorycode'} . "\n ($mindays, $maxdays)\nreturns " . $sth->rows . " rows"; - while ( my ($itemcount, $borrowernumber, $firstname, $lastname, + while ( my ( $borrowernumber, $firstname, $lastname, $address1, $address2, $city, $postcode, $country, $email, - $longest_issue ) = $sth->fetchrow ) + $date_due ) = $sth->fetchrow ) { $verbose and warn "borrower $firstname, $lastname ($borrowernumber) has $itemcount items triggering level $i."; -- 1.7.4.1