From 441341427a59ba2431f22549a445fd851ba03fea Mon Sep 17 00:00:00 2001 From: Fridolyn SOMERS Date: Tue, 9 Jul 2013 17:38:04 +0200 Subject: [PATCH] Bug 9362 - Wrong query in GetUpcomingDueIssues C4:Circulation:GetUpcomingDueIssues is used in the advance_notices.pl script. This script waits for a "maxdays" parameter, which is used in the following request. We have in GetUpcomingDueIssues the following query: SELECT issues.*, items.itype as itemtype, items.homebranch, TO_DAYS( date_due )-TO_DAYS( NOW() ) as days_until_due, branches.branchemail FROM issues LEFT JOIN items USING (itemnumber) LEFT OUTER JOIN branches USING (branchcode) WhERE returndate is NULL AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) < ? The last line should be with date_due before NOW. The date_due is supposed to be "bigger" (farther) than the NOW date. Also, this request gives the upcoming due issues, but also the overdues. This patch corrects this by using HAVING in query. Test plan : - Create an issue with a date due in the paste - Create an issue with a date due in two days - Launch advance notices with due date in max 2 days : perl misc/cronjobs/advance_notices.pl -c -n -v -m=2 => You get a warn "found 0 issues" - Launch advance notices with due date in max 3 days : perl misc/cronjobs/advance_notices.pl -c -n -v -m=3 => You get a warn "found 1 issues" --- C4/Circulation.pm | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/C4/Circulation.pm b/C4/Circulation.pm index 30cbe2f..1762f3a 100644 --- a/C4/Circulation.pm +++ b/C4/Circulation.pm @@ -2419,8 +2419,8 @@ SELECT issues.*, items.itype as itemtype, items.homebranch, TO_DAYS( date_due )- FROM issues LEFT JOIN items USING (itemnumber) LEFT OUTER JOIN branches USING (branchcode) -WhERE returndate is NULL -AND ( TO_DAYS( NOW() )-TO_DAYS( date_due ) ) < ? +WHERE returndate is NULL +HAVING days_until_due > 0 AND days_until_due < ? END_SQL my @bind_parameters = ( $params->{'days_in_advance'} ); -- 1.7.10.4