From b61e0833011c2dcab81c38b44e5b81b427b795f7 Mon Sep 17 00:00:00 2001
From: Nahuel Angelinetti <nahuel.angelinetti@biblibre.com>
Date: Tue, 9 Dec 2008 17:28:41 +0100
Subject: [PATCH] (bug #2854) add the possibility to make a fuzzy search on the
 barcode

This patch add a field that permit to make a "like" and "not like" filter on barcode in the catalog report.

Signed-off-by: Galen Charlton <galen.charlton@liblime.com>
---
 .../en/modules/reports/catalogue_stats.tmpl   | 11 ++++++
 reports/catalogue_stats.pl                    | 38 +++++++++++++------
 2 files changed, 38 insertions(+), 11 deletions(-)

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tmpl
index e4ea2d3a54..fa9668514f 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tmpl
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_stats.tmpl
@@ -185,6 +185,17 @@
 					</select>
 				</td>
 			</tr>
+            <tr>
+                <td colspan="3">Filter barcode</td>
+                <td>
+                    <select name="Filter" id="like">
+                        <option value="1">like</option>
+                        <option value="0">not like</option>
+                    </select>
+                    <input type="text" name="Filter" id="barcode" />
+                    (use * to do a fuzzy search)
+                </td>
+            </tr>
 		</tbody>
 	</table><br /></fieldset>
 	
diff --git a/reports/catalogue_stats.pl b/reports/catalogue_stats.pl
index d624bdcc1c..c5d5ce0198 100755
--- a/reports/catalogue_stats.pl
+++ b/reports/catalogue_stats.pl
@@ -192,9 +192,25 @@ sub calculate {
 	my @looprow;
 	my %globalline;
 	my $grantotal =0;
+    my $barcodelike   = @$filters[13];
+    my $barcodefilter = @$filters[14];
+    my $not;
+    
 # extract parameters
 	my $dbh = C4::Context->dbh;
 
+# if barcodefilter is empty set as %
+if($barcodefilter){
+    # Check if barcodefilter is "like" or "not like"
+    if(!$barcodelike){
+        $not = "not";
+    }
+    # Change * to %
+    $barcodefilter =~ s/\*/%/g;
+}else{
+    $barcodefilter = "%";
+}
+
 # Filters
 # Checking filters
 #
@@ -269,7 +285,7 @@ sub calculate {
 	}
 
 	my $strsth;
-	$strsth .= "select distinctrow $linefield from biblioitems left join items on (items.biblioitemnumber = biblioitems.biblioitemnumber) where $line is not null ";
+	$strsth .= "select distinctrow $linefield from biblioitems left join items on (items.biblioitemnumber = biblioitems.biblioitemnumber) where barcode $not LIKE ? AND $line is not null ";
 	if ( @linefilter ) {
 		if ($linefilter[1]){
 			$strsth .= " and $line >= ? " ;
@@ -284,11 +300,11 @@ sub calculate {
 	
 	my $sth = $dbh->prepare( $strsth );
 	if (( @linefilter ) and ($linefilter[1])){
-		$sth->execute($linefilter[0],$linefilter[1]);
-	} elsif ($linefilter[0]) {
-		$sth->execute($linefilter[0]);
+		$sth->execute($barcodefilter,$linefilter[0],$linefilter[1]);
+	} elsif ($barcodefilter,$linefilter[0]) {
+		$sth->execute($barcodefilter,$linefilter[0]);
 	} else {
-		$sth->execute;
+		$sth->execute($barcodefilter);
 	}
  	while ( my ($celvalue) = $sth->fetchrow) {
  		my %cell;
@@ -318,7 +334,7 @@ sub calculate {
 	FROM   biblioitems
 	LEFT JOIN items
 		ON (items.biblioitemnumber = biblioitems.biblioitemnumber)
-	WHERE  $column IS NOT NULL ";
+	WHERE  barcode $not LIKE ? AND $column IS NOT NULL ";
 	if (( @colfilter ) and ($colfilter[1])) {
 		$strsth2 .= " and $column> ? and $column< ?";
 	}elsif ($colfilter[0]){
@@ -329,11 +345,11 @@ sub calculate {
 	$debug and print STDERR "SQL: $strsth2";
 	my $sth2 = $dbh->prepare( $strsth2 );
 	if ((@colfilter) and ($colfilter[1])) {
-		$sth2->execute($colfilter[0],$colfilter[1]);
+		$sth2->execute($barcodefilter,$colfilter[0],$colfilter[1]);
 	} elsif ($colfilter[0]){
-		$sth2->execute($colfilter[0]);
+		$sth2->execute($barcodefilter,$colfilter[0]);
 	} else {
-		$sth2->execute;
+		$sth2->execute($barcodefilter);
 	}
  	while (my ($celvalue) = $sth2->fetchrow) {
  		my %cell;
@@ -363,7 +379,7 @@ sub calculate {
 	}
 
 # preparing calculation
-	my $strcalc .= "SELECT $linefield, $colfield, count(*) FROM biblioitems LEFT JOIN  items ON (items.biblioitemnumber = biblioitems.biblioitemnumber) WHERE 1";
+	my $strcalc .= "SELECT $linefield, $colfield, count(*) FROM biblioitems LEFT JOIN  items ON (items.biblioitemnumber = biblioitems.biblioitemnumber) WHERE 1 AND barcode $not like ? ";
 	if (@$filters[0]){
 		@$filters[0]=~ s/\*/%/g;
 		$strcalc .= " AND dewey >" . @$filters[0];
@@ -426,7 +442,7 @@ sub calculate {
 	$strcalc .= " group by $linefield, $colfield order by $linefield,$colfield";
 	$debug and warn "SQL: $strcalc";
 	my $dbcalc = $dbh->prepare($strcalc);
-	$dbcalc->execute;
+	$dbcalc->execute($barcodefilter);
 #	warn "filling table";
 	
 	my $emptycol; 
-- 
2.20.1