Fix date filter on issues_stats.

[srvgit] / reports / catalogue_out.pl

diff --git a/reports/catalogue_out.pl b/reports/catalogue_out.pl
index e1d4814..2beb62b 100755 (executable)
--- a/reports/catalogue_out.pl
+++ b/reports/catalogue_out.pl
@@ -209,13 +209,19 @@ 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]' " ;
+            $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 );
@@ -256,15 +262,19 @@ sub calculate {
 # Processing average loanperiods
     $strcalc .= "SELECT items.barcode, biblio.title, biblio.biblionumber, biblio.author";
     $strcalc .= " , $colfield " if ($colfield);
-    $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";
+    $strcalc .= " FROM (items 
+                        LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber  
+                        LEFT JOIN biblio ON biblio.biblionumber=items.biblionumber) 
+                  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]);
     $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;