From 82349a67cf9bd07ac8404a170fd3c72179423780 Mon Sep 17 00:00:00 2001 From: Henri-Damien LAURENT Date: Wed, 19 Mar 2008 10:11:13 -0500 Subject: [PATCH] Using old_issues table need some testing on data since I donot have valid data in my fresh created database. Note : Shouldnot updatedatabase also cope with data "moving" when a table is created and that some data should go from one table to another ? Signed-off-by: Galen Charlton Signed-off-by: Joshua Ferraro --- reports/borrowers_out.pl | 10 +++++----- reports/borrowers_stats.pl | 4 ++-- reports/catalogue_out.pl | 12 +++++++++--- 3 files changed, 16 insertions(+), 10 deletions(-) diff --git a/reports/borrowers_out.pl b/reports/borrowers_out.pl index c969cc8c61..e836017acf 100755 --- a/reports/borrowers_out.pl +++ b/reports/borrowers_out.pl @@ -189,7 +189,7 @@ sub calculate { $colorder .= $column; my $strsth2; - $strsth2 .= "select distinctrow $colfield FROM borrowers LEFT JOIN `issues` ON issues.borrowernumber=borrowers.borrowernumber"; + $strsth2 .= "select distinctrow $colfield FROM borrowers LEFT JOIN `old_issues` ON issues.borrowernumber=borrowers.borrowernumber"; if ($colfilter[0]) { $colfilter[0] =~ s/\*/%/g; $strsth2 .= " and $column LIKE '$colfilter[0]' " ; @@ -239,8 +239,8 @@ sub calculate { @$filters[0]=~ s/\*/%/g if (@$filters[0]); $strcalc .= " AND borrowers.categorycode like '" . @$filters[0] ."'" if ( @$filters[0] ); if (@$filters[1]){ - my $strqueryfilter="SELECT DISTINCT borrowernumber FROM issues where issues.timestamp> @$filters[1] "; - my $queryfilter = $dbh->prepare("SELECT DISTINCT borrowernumber FROM issues where issues.timestamp> ".format_date_in_iso(@$filters[1])); + my $strqueryfilter="SELECT DISTINCT borrowernumber FROM old_issues where old_issues.timestamp> @$filters[1] "; + my $queryfilter = $dbh->prepare("SELECT DISTINCT borrowernumber FROM old_issues where old_issues.timestamp> ".format_date_in_iso(@$filters[1])); $strcalc .= " AND borrowers.borrowernumber not in ($strqueryfilter)"; # $queryfilter->execute(@$filters[1]); @@ -248,8 +248,8 @@ sub calculate { # $strcalc .= " AND borrowers.borrowernumber <> $borrowernumber "; # } } else { - my $strqueryfilter="SELECT DISTINCT borrowernumber FROM issues "; - my $queryfilter = $dbh->prepare("SELECT DISTINCT borrowernumber FROM issues "); + my $strqueryfilter="SELECT DISTINCT borrowernumber FROM old_issues "; + my $queryfilter = $dbh->prepare("SELECT DISTINCT borrowernumber FROM old_issues "); $queryfilter->execute; $strcalc .= " AND borrowers.borrowernumber not in ($strqueryfilter)"; # while (my ($borrowernumber)=$queryfilter->fetchrow){ diff --git a/reports/borrowers_stats.pl b/reports/borrowers_stats.pl index 0124dd78af..45e6bc777d 100755 --- a/reports/borrowers_stats.pl +++ b/reports/borrowers_stats.pl @@ -396,8 +396,8 @@ sub calculate { $strcalc .= " AND sort1 like '" . @$filters[5] ."'" if ( @$filters[5] ); @$filters[6]=~ s/\*/%/g if (@$filters[6]); $strcalc .= " AND sort2 like '" . @$filters[6] ."'" if ( @$filters[6] ); - $strcalc .= " AND borrowernumber in (select distinct(borrowernumber) from issues where issuedate > '" . $newperioddate . "')" if ($activity eq 'active'); - $strcalc .= " AND borrowernumber not in (select distinct(borrowernumber) from issues where issuedate > '" . $newperioddate . "')" if ($activity eq 'nonactive'); + $strcalc .= " AND borrowernumber in (select distinct(borrowernumber) from old_issues where issuedate > '" . $newperioddate . "')" if ($activity eq 'active'); + $strcalc .= " AND borrowernumber not in (select distinct(borrowernumber) from old_issues where issuedate > '" . $newperioddate . "')" if ($activity eq 'nonactive'); $strcalc .= " AND $status='1' " if ($status); $strcalc .= " group by $linefield, $colfield"; # warn "". $strcalc; diff --git a/reports/catalogue_out.pl b/reports/catalogue_out.pl index 4424aed88c..34f7b18f41 100755 --- a/reports/catalogue_out.pl +++ b/reports/catalogue_out.pl @@ -209,7 +209,13 @@ sub calculate { $colorder .= $column; my $strsth2; - $strsth2 .= "select distinctrow $colfield FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null"; + $strsth2 .= "select distinctrow $colfield + FROM `old_issues` + LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber + LEFT JOIN items ON old_issues.itemnumber=items.itemnumber + LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=items.biblioitemnumber + WHERE old_issues.itemnumber=items.itemnumber + AND old_issues.borrowernumber=borrowers.borrowernumber"; if ($colfilter[0]) { $colfilter[0] =~ s/\*/%/g; $strsth2 .= " and $column LIKE '$colfilter[0]' " ; @@ -259,8 +265,8 @@ sub calculate { $strcalc .= " FROM (items LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber LEFT JOIN biblio ON biblio.biblionumber=items.biblionumber) - LEFT JOIN issues ON issues.itemnumber=items.itemnumber - WHERE issues.itemnumber is null"; + LEFT JOIN old_issues ON old_issues.itemnumber=items.itemnumber + WHERE old_issues.itemnumber is null"; @$filters[0]=~ s/\*/%/g if (@$filters[0]); $strcalc .= " AND items.homebranch like '" . @$filters[0] ."'" if ( @$filters[0] ); @$filters[1]=~ s/\*/%/g if (@$filters[1]); -- 2.20.1