From 441341427a59ba2431f22549a445fd851ba03fea Mon Sep 17 00:00:00 2001 From: Fridolyn SOMERS Date: Tue, 9 Jul 2013 17:38:04 +0200 Subject: [PATCH][SIGNED OFF] 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" Signed-off-by: Mathieu Saby I did the following test : - 1 book to check in 2 days - 2 books to check in in the past before applying the patch : $perl ../misc/cronjobs/advance_notices.pl -c -n -v -m=2 getting upcoming due issues at ../misc/cronjobs/advance_notices.pl line 203. found 1 issues at ../misc/cronjobs/advance_notices.pl line 205. I changed the value of "-m" : 0, 1, 2, 3, 4 => always 1 issue found (the book to check in in 2 days) after applying the patch : $perl ../misc/cronjobs/advance_notices.pl -c -n -v -m=2 found 0 issues for m = 0, 1, 2 => 0 issues $perl ../misc/cronjobs/advance_notices.pl -c -n -v -m=3 found 1 issues for m = 3,4,5 => 1 issues (the book to check in in 2 days) --- 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