From 3431c323af4311b193eff8443b9682359ce19321 Mon Sep 17 00:00:00 2001 From: Katrin Fischer Date: Tue, 21 Dec 2010 07:45:22 +0100 Subject: [PATCH] Bug 5527: Fix some problems in inactive borrowers report Content-Type: text/plain; charset="utf-8" - 'Not checked out since:' date was not added to query - borrowers with only checkouts and no returns since this date were considered inactive --- reports/borrowers_out.pl | 16 ++++++++++++---- 1 files changed, 12 insertions(+), 4 deletions(-) diff --git a/reports/borrowers_out.pl b/reports/borrowers_out.pl index 0502dc6..0a135e1 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); -- 1.7.1