From f2cf6c2db3e126541b10d9c1d9d7458c6766a071 Mon Sep 17 00:00:00 2001 From: Katrin Fischer Date: Mon, 7 Mar 2011 13:50:48 -0500 Subject: [PATCH] Bug 5527: Fix some problems in inactive borrowers report - 'Not checked out since:' date was not added to query - borrowers with only checkouts and no returns since this date were considered inactive Signed-off-by: Nicole C. Engard Signed-off-by: Chris Cormack --- reports/borrowers_out.pl | 16 ++++++++++++---- 1 file changed, 12 insertions(+), 4 deletions(-) diff --git a/reports/borrowers_out.pl b/reports/borrowers_out.pl index 0502dc6676..0a135e1119 100755 --- a/reports/borrowers_out.pl +++ b/reports/borrowers_out.pl @@ -181,7 +181,7 @@ sub calculate { $colorder .= $column; my $strsth2; - $strsth2 .= "select distinctrow $colfield FROM borrowers LEFT JOIN `old_issues` USING(borrowernumber)"; + $strsth2 .= "select distinct $colfield FROM borrowers WHERE 1"; if ($colfilter[0]) { $colfilter[0] =~ s/\*/%/g; $strsth2 .= " and $column LIKE '$colfilter[0]' " ; @@ -229,10 +229,18 @@ sub calculate { $strcalc .= "WHERE 1 "; @$filters[0]=~ s/\*/%/g if (@$filters[0]); $strcalc .= " AND borrowers.categorycode like '" . @$filters[0] ."'" if ( @$filters[0] ); - my $strqueryfilter = "SELECT DISTINCT borrowernumber FROM old_issues WHERE borrowernumber IS NOT NULL "; - if (@$filters[1]){ - my $strqueryfilter .= "AND old_issues.timestamp> @$filters[1] "; + + my $strqueryfilter = "SELECT DISTINCT borrowernumber FROM "; + $strqueryfilter .= "(SELECT borrowernumber from old_issues WHERE borrowernumber IS NOT NULL "; + if ($filters->[1]){ + $strqueryfilter .= "AND old_issues.timestamp> '$filters->[1]' "; + } + $strqueryfilter .= "UNION SELECT borrowernumber FROM issues WHERE 1 "; + if ($filters->[1]){ + $strqueryfilter .= "AND issues.timestamp> '$filters->[1]' "; } + $strqueryfilter .= ") active_borrowers"; + $strcalc .= " AND borrowers.borrowernumber not in ($strqueryfilter)"; $strcalc .= " group by borrowers.borrowernumber"; $strcalc .= ", $colfield" if ($column); -- 2.20.1