From 3eb7a607e5c2ada2b2351d3f289841a510820e0e Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 19 Mar 2008 10:11:17 -0500 Subject: [PATCH] more old_issues SQL cleanup * no need to test for 'returndate is null' in queries on old_issues * added more stylistic corrections Signed-off-by: Joshua Ferraro --- reports/bor_issues_top.pl | 22 +++++++++++----------- reports/catalogue_out.pl | 10 +++++----- reports/issues_avg_stats.pl | 36 ++++++++++++++++++------------------ 3 files changed, 34 insertions(+), 34 deletions(-) diff --git a/reports/bor_issues_top.pl b/reports/bor_issues_top.pl index 7ef1d73b0a..b6cc4b7a53 100755 --- a/reports/bor_issues_top.pl +++ b/reports/bor_issues_top.pl @@ -273,21 +273,21 @@ sub calculate { LEFT JOIN borrowers ON old_issues.borrowernumber=borrowers.borrowernumber LEFT JOIN items ON old_issues.itemnumber=items.itemnumber LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) - WHERE returndate is not null"; + WHERE 1"; if (($column=~/timestamp/) or ($column=~/returndate/)){ if ($colfilter[1] and ($colfilter[0])){ - $strsth2 .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ; + $strsth2 .= " AND $column between '$colfilter[0]' AND '$colfilter[1]' " ; } elsif ($colfilter[1]) { - $strsth2 .= " and $column < '$colfilter[1]' " ; + $strsth2 .= " AND $column < '$colfilter[1]' " ; } elsif ($colfilter[0]) { - $strsth2 .= " and $column > '$colfilter[0]' " ; + $strsth2 .= " AND $column > '$colfilter[0]' " ; } } elsif ($colfilter[0]) { $colfilter[0] =~ s/\*/%/g; - $strsth2 .= " and $column LIKE '$colfilter[0]' " ; + $strsth2 .= " AND $column LIKE '$colfilter[0]' " ; } - $strsth2 .=" group by $colfield"; - $strsth2 .=" order by $colorder"; + $strsth2 .=" GROUP BY $colfield"; + $strsth2 .=" ORDER BY $colorder"; warn "". $strsth2; my $sth2 = $dbh->prepare( $strsth2 ); @@ -326,8 +326,8 @@ sub calculate { $strcalc .= " FROM `old_issues` LEFT JOIN borrowers ON borrowers.borrowernumber LEFT JOIN items ON items.itemnumber=old_issues.itemnumber - LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) - WHERE returndate is not null"; + LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber=items.biblioitemnumber) + WHERE 1"; @$filters[0]=~ s/\*/%/g if (@$filters[0]); $strcalc .= " AND old_issues.timestamp > '" . @$filters[0] ."'" if ( @$filters[0] ); @@ -350,9 +350,9 @@ sub calculate { @$filters[9]=~ s/\*/%/g if (@$filters[9]); $strcalc .= " AND year(old_issues.timestamp) like '" . @$filters[9] ."'" if ( @$filters[9] ); - $strcalc .= " group by borrowers.borrowernumber"; + $strcalc .= " GROUP BY borrowers.borrowernumber"; $strcalc .= ", $colfield" if ($column); - $strcalc .= " order by RANK DESC"; + $strcalc .= " ORDER BY RANK DESC"; $strcalc .= ",$colfield " if ($colfield); # my $max; # if (@loopcol) { diff --git a/reports/catalogue_out.pl b/reports/catalogue_out.pl index 34f7b18f41..2beb62b070 100755 --- a/reports/catalogue_out.pl +++ b/reports/catalogue_out.pl @@ -218,10 +218,10 @@ sub calculate { AND old_issues.borrowernumber=borrowers.borrowernumber"; if ($colfilter[0]) { $colfilter[0] =~ s/\*/%/g; - $strsth2 .= " and $column LIKE '$colfilter[0]' " ; + $strsth2 .= " AND $column LIKE '$colfilter[0]' " ; } - $strsth2 .=" group by $colfield"; - $strsth2 .=" order by $colorder"; + $strsth2 .=" GROUP BY $colfield"; + $strsth2 .=" ORDER BY $colorder"; warn "". $strsth2; my $sth2 = $dbh->prepare( $strsth2 ); @@ -272,9 +272,9 @@ sub calculate { @$filters[1]=~ s/\*/%/g if (@$filters[1]); $strcalc .= " AND biblioitems.itemtype like '" . @$filters[1] ."'" if ( @$filters[1] ); - $strcalc .= " group by items.itemnumber"; + $strcalc .= " GROUP BY items.itemnumber"; $strcalc .= ", $colfield" if ($column); - $strcalc .= " order by $colfield " if ($colfield); + $strcalc .= " ORDER BY $colfield " if ($colfield); my $max = (@loopcol) ? $line*@loopcol : $line ; $strcalc .= " LIMIT 0,$max" if ($line); warn "SQL :". $strcalc; diff --git a/reports/issues_avg_stats.pl b/reports/issues_avg_stats.pl index 71e0b3064d..8d9d70ddb1 100755 --- a/reports/issues_avg_stats.pl +++ b/reports/issues_avg_stats.pl @@ -363,28 +363,28 @@ sub calculate { (issuingrules.branchcode=old_issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) - WHERE returndate is not null"; + WHERE 1"; if (($line=~/timestamp/) or ($line=~/returndate/)){ if ($linefilter[1] and ($linefilter[0])){ - $strsth .= " and $line between '$linefilter[0]' and '$linefilter[1]' " ; + $strsth .= " AND $line BETWEEN '$linefilter[0]' AND '$linefilter[1]' " ; } elsif ($linefilter[1]) { - $strsth .= " and $line < \'$linefilter[1]\' " ; + $strsth .= " AND $line < \'$linefilter[1]\' " ; } elsif ($linefilter[0]) { - $strsth .= " and $line > \'$linefilter[0]\' " ; + $strsth .= " AND $line > \'$linefilter[0]\' " ; } if ($linefilter[2]){ - $strsth .= " and dayname($line) = '$linefilter[2]' " ; + $strsth .= " AND dayname($line) = '$linefilter[2]' " ; } if ($linefilter[3]){ - $strsth .= " and monthname($line) = '$linefilter[3]' " ; + $strsth .= " AND monthname($line) = '$linefilter[3]' " ; } } elsif ($linefilter[0]) { $linefilter[0] =~ s/\*/%/g; - $strsth .= " and $line LIKE '$linefilter[0]' " ; + $strsth .= " AND $line LIKE '$linefilter[0]' " ; } - $strsth .=" group by $linefield"; - $strsth .=" order by $lineorder"; + $strsth .=" GROUP BY $linefield"; + $strsth .=" ORDER BY $lineorder"; my $sth = $dbh->prepare( $strsth ); $sth->execute; @@ -434,28 +434,28 @@ sub calculate { (issuingrules.branchcode=old_issues.branchcode AND issuingrules.itemtype=biblioitems.itemtype AND issuingrules.categorycode=borrowers.categorycode) - WHERE returndate is not null"; + WHERE 1"; if (($column=~/timestamp/) or ($column=~/returndate/)){ if ($colfilter[1] and ($colfilter[0])){ - $strsth2 .= " and $column between '$colfilter[0]' and '$colfilter[1]' " ; + $strsth2 .= " AND $column BETWEEN '$colfilter[0]' AND '$colfilter[1]' " ; } elsif ($colfilter[1]) { - $strsth2 .= " and $column < '$colfilter[1]' " ; + $strsth2 .= " AND $column < '$colfilter[1]' " ; } elsif ($colfilter[0]) { - $strsth2 .= " and $column > '$colfilter[0]' " ; + $strsth2 .= " AND $column > '$colfilter[0]' " ; } if ($colfilter[2]){ - $strsth2 .= " and dayname($column) = '$colfilter[2]' " ; + $strsth2 .= " AND dayname($column) = '$colfilter[2]' " ; } if ($colfilter[3]){ - $strsth2 .= " and monthname($column) = '$colfilter[3]' " ; + $strsth2 .= " AND monthname($column) = '$colfilter[3]' " ; } } elsif ($colfilter[0]) { $colfilter[0] =~ s/\*/%/g; - $strsth2 .= " and $column LIKE '$colfilter[0]' " ; + $strsth2 .= " AND $column LIKE '$colfilter[0]' " ; } - $strsth2 .=" group by $colfield"; - $strsth2 .=" order by $colorder"; + $strsth2 .=" GROUP BY $colfield"; + $strsth2 .=" ORDER BY $colorder"; warn "". $strsth2; my $sth2 = $dbh->prepare( $strsth2 ); -- 2.39.2