[Koha-devel] CVS: koha/C4 Search.pm,1.11,1.12

[Chris Cormack]

Update of /cvsroot/koha/koha/C4
In directory usw-pr-cvs1:/tmp/cvs-serv28698/C4

Modified Files:
        Search.pm 
Log Message:
Fixing bug in subject searching that was causing subjects with ' to break
the search


Index: Search.pm
===================================================================
RCS file: /cvsroot/koha/koha/C4/Search.pm,v
retrieving revision 1.11
retrieving revision 1.12
diff -C2 -r1.11 -r1.12
*** Search.pm   12 Dec 2001 02:31:28 -0000      1.11
--- Search.pm   12 Feb 2002 21:31:41 -0000      1.12
***************
*** 9,12 ****
--- 9,13 ----
  use C4::Database;
  use C4::Reserves2;
+ use Set::Scalar;
  
  use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
***************
*** 261,269 ****
    my $i=1;
    my @results;
    my $query ="Select * from biblio,bibliosubtitle,biblioitems where
    biblio.biblionumber=biblioitems.biblionumber and
    biblio.biblionumber=bibliosubtitle.biblionumber and
    (((title like '$key[0]%' or title like '% $key[0]%')";
- 
    while ($i < $count){
      $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
--- 262,399 ----
    my $i=1;
    my @results;
+   my $query="Select biblionumber from biblio
+   where ((title like '$key[0]%' or title like '% $key[0]%')";
+   while ($i < $count){                                                  
+       $query=$query." and (title like '$key[$i]%' or title like '% 
$key[$i]%')";                                                   
+       $i++;                                                  
+   }
+   $query.= ") or ((biblio.notes like '$key[0]%' or biblio.notes like '% 
$key[0]%')";                                             
+   for ($i=1;$i<$count;$i++){                                                  
+       $query.=" and (biblio.notes like '$key[$i]%' or biblio.notes like '% 
$key[$i]%')";                                           
+   }
+    $query.= ") or ((seriestitle like '$key[0]%' or seriestitle like '% 
$key[0]%')";                                               
+   for ($i=1;$i<$count;$i++){                                                  
+       $query.=" and (seriestitle like '$key[$i]%' or seriestitle like '% 
$key[$i]%')";                                             
+   }
+   $query.=" )";
+ #  print $query;
+   my $sth=$dbh->prepare($query);
+   $sth->execute;
+   my $i=0;
+   while (my @res=$sth->fetchrow_array){
+     $results[$i]=$res[0];
+     $i++;
+   }
+   $sth->finish;
+   my $set1=Set::Scalar->new(@results);
+   $query="Select biblionumber from bibliosubtitle where
+   ((subtitle like '$key[0]%' or subtitle like '% $key[0]%')";                 
+   for ($i=1;$i<$count;$i++){   
+         $query.= " and (subtitle like '$key[$i]%' or subtitle like '% 
$key[$i]%')";                                                  
+   }                   
+   $query.=" )";
+ #  print $query;
+   $sth=$dbh->prepare($query);
+   $sth->execute;
+   $i=0;
+   while (my @res=$sth->fetchrow_array){
+     $results[$i]=$res[0];
+     $i++;
+   }
+   $sth->finish;
+   my $set2=Set::Scalar->new(@results);
+   if ($i > 0){
+     $set1=$set1+$set2;
+   }
+   $query ="Select biblionumber from biblioitems where
+   ((biblioitems.notes like '$key[0]%' or biblioitems.notes like '% 
$key[0]%')";                                   
+   for ($i=1;$i<$count;$i++){                                                  
+       $query.=" and (biblioitems.notes like '$key[$i]%' or biblioitems.notes 
like '% $key[$i]%')";                                 
+   }            
+   $query.=" )";
+ #  print $query;
+   $sth=$dbh->prepare($query);
+   $sth->execute;
+   $i=0;
+   while (my @res=$sth->fetchrow_array){
+     $results[$i]=$res[0];
+     $i++;
+   }
+   $sth->finish;
+   my $set3=Set::Scalar->new(@results);    
+   if ($i > 0){
+     $set1=$set1+$set3;
+   }
+   $sth=$dbh->prepare("Select biblionumber from bibliosubject where subject
+   like '%$search->{'keyword'}%' group by biblionumber");
+   $sth->execute;
+   $i=0;
+   while (my @res=$sth->fetchrow_array){
+     $results[$i]=$res[0];
+     $i++;
+   }
+   $sth->finish;
+   my $set4=Set::Scalar->new(@results);    
+   if ($i > 0){
+     $set1=$set1+$set4;
+   }
+   my $i2=0;
+   my @res2;
+   my @res = $set1->members;
+   address@hidden;
+ #  print $set1;
+   $i=0;
+ #  print "count $count";
+   while ($i2 < $num && $i2 < $count){
+     my $query="select * from biblio,biblioitems where
+     biblio.biblionumber='$res[$i2+$offset]' and        
+     biblio.biblionumber=biblioitems.biblionumber";
+     if ($search->{'class'} ne ''){
+       my @temp=split(/\|/,$search->{'class'});
+       my address@hidden;
+       $query.= "and ( itemtype='$temp[0]'";
+       for (my $i=1;$i<$count;$i++){
+         $query.=" or itemtype='$temp[$i]'";
+       }
+       $query.=")"; 
+     }
+     if ($search->{'dewey'} ne ''){
+       $query.= "and (dewey like '$search->{'dewey'}%') ";
+     }
+ 
+     my $sth=$dbh->prepare($query);
+ #    print $query;
+     $sth->execute;
+     if (my $data2=$sth->fetchrow_hashref){
+         my $dewey= $data2->{'dewey'};               
+         my $subclass=$data2->{'subclass'};                   
+       $dewey=~s/\.*0*$//;     
+         ($dewey == 0) && ($dewey='');               
+         ($dewey) && ($dewey.=" $subclass") ;                      
+         $sth->finish;                                                        
+       
$res2[$i]="$data2->{'author'}\t$data2->{'title'}\t$data2->{'biblionumber'}\t$data2->{'copyrightdate'}\t$dewey";
+         $i++;
+     }
+     $i2++;
+   }
+   $dbh->disconnect;
+ 
+ #  $count=$i;
+   return($count,@res2);
+ }
+ 
+ sub KeywordSearch2 {
+   my ($env,$type,$search,$num,$offset)address@hidden;
+   my $dbh = &C4Connect;
+   $search->{'keyword'}=~ s/ +$//;
+   $search->{'keyword'}=~ s/'/\\'/;
+   my @key=split(' ',$search->{'keyword'});
+   my address@hidden;
+   my $i=1;
+   my @results;
    my $query ="Select * from biblio,bibliosubtitle,biblioitems where
    biblio.biblionumber=biblioitems.biblionumber and
    biblio.biblionumber=bibliosubtitle.biblionumber and
    (((title like '$key[0]%' or title like '% $key[0]%')";
    while ($i < $count){
      $query=$query." and (title like '$key[$i]%' or title like '% $key[$i]%')";
***************
*** 377,381 ****
    my @res;
    my address@hidden;
!   $i=0;
    if ($count > 0){
      $res[0]=$results[0];
--- 507,511 ----
    my @res;
    my address@hidden;
!   $i=1;
    if ($count > 0){
      $res[0]=$results[0];
***************
*** 399,402 ****
--- 529,533 ----
    $dbh->disconnect;
  #  $i--;
+ #  $i++;
    return($i,@res2);
  }
***************
*** 436,441 ****
         $query=$query.")";
           if ($search->{'title'} ne ''){ 
!          $query=$query. " and (title like '%$search->{'title'}%' 
!          or seriestitle like '%$search->{'title'}%')";
         }
         if ($search->{'class'} ne ''){
--- 567,593 ----
         $query=$query.")";
           if ($search->{'title'} ne ''){ 
!          my @key=split(' ',$search->{'title'});
!          my address@hidden;
!            my $i=0;
!          $query.= " and (((title like '$key[0]%' or title like '% $key[0]%' 
or title like '% $key[0]')";
!             while ($i<$count){            
!             $query=$query." and (title like '$key[$i]%' or title like '% 
$key[$i]%' or title like '% $key[$i]')";
!               $i++; 
!           }                       
! #         $query.=") or ((subtitle like '$key[0]%' or subtitle like '% 
$key[0] %' or subtitle like '% $key[0]')"; 
! #            for ($i=1;$i<$count;$i++){
! #           $query.=" and (subtitle like '$key[$i]%' or subtitle like '% 
$key[$i] %' or subtitle like '% $key[$i]')";   
! #            }
!           $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% 
$key[0]%' or seriestitle like '% $key[0]')";  
!             for ($i=1;$i<$count;$i++){                    
!               $query.=" and (seriestitle like '$key[$i]%' or seriestitle like 
'% $key[$i]%')";
!             }                                                             
!           $query.=") or ((unititle like '$key[0]%' or unititle like '% 
$key[0]%' or unititle like '% $key[0]')";                         
!             for ($i=1;$i<$count;$i++){                    
!               $query.=" and (unititle like '$key[$i]%' or unititle like '% 
$key[$i]%')";   
!             }                                                             
!           $query=$query."))"; 
!          #$query=$query. " and (title like '%$search->{'title'}%' 
!          #or seriestitle like '%$search->{'title'}%')";
         }
         if ($search->{'class'} ne ''){
***************
*** 483,497 ****
              $i++;
            }
!           $query.=") or ((subtitle like '$key[0]%' or subtitle like '% 
$key[0] %' or subtitle like '% $key[0]')";
            for ($i=1;$i<$count;$i++){
!             $query.=" and (subtitle like '$key[$i]%' or subtitle like '% 
$key[$i] %' or subtitle like '% $key[$i]')";
            }
!           $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% 
$key[0] %' or seriestitle like '% $key[0]')";
            for ($i=1;$i<$count;$i++){
!             $query.=" and (seriestitle like '$key[$i]%' or seriestitle like 
'% $key[$i] %')";
            }
!           $query.=") or ((unititle like '$key[0]%' or unititle like '% 
$key[0] %' or unititle like '% $key[0]')";
            for ($i=1;$i<$count;$i++){
!             $query.=" and (unititle like '$key[$i]%' or unititle like '% 
$key[$i] %')";
            }
            $query=$query."))";
--- 635,649 ----
              $i++;
            }
!           $query.=") or ((subtitle like '$key[0]%' or subtitle like '% 
$key[0]%' or subtitle like '% $key[0]')";
            for ($i=1;$i<$count;$i++){
!             $query.=" and (subtitle like '$key[$i]%' or subtitle like '% 
$key[$i]%' or subtitle like '% $key[$i]')";
            }
!           $query.=") or ((seriestitle like '$key[0]%' or seriestitle like '% 
$key[0]%' or seriestitle like '% $key[0]')";
            for ($i=1;$i<$count;$i++){
!             $query.=" and (seriestitle like '$key[$i]%' or seriestitle like 
'% $key[$i]%')";
            }
!           $query.=") or ((unititle like '$key[0]%' or unititle like '% 
$key[0]%' or unititle like '% $key[0]')";
            for ($i=1;$i<$count;$i++){
!             $query.=" and (unititle like '$key[$i]%' or unititle like '% 
$key[$i]%')";
            }
            $query=$query."))";
***************
*** 537,541 ****
             and biblioitems.illus like '%".$search->{'illustrator'}."%'";
          }
!         }
            $query .=" group by biblio.biblionumber";    
        }
--- 689,693 ----
             and biblioitems.illus like '%".$search->{'illustrator'}."%'";
          }
!       }
            $query .=" group by biblio.biblionumber";    
        }
***************
*** 607,610 ****
--- 759,763 ----
    }
  }
+ #print $query;
  my $sth=$dbh->prepare($query);
  $sth->execute;
***************
*** 648,654 ****
    my ($env,$subject)address@hidden;
    my $dbh=C4Connect();
    my $query="Select * from biblio,bibliosubject where
    biblio.biblionumber=bibliosubject.biblionumber and
!   bibliosubject.subject='$subject' group by biblio.biblionumber
    order by biblio.title";
    my $sth=$dbh->prepare($query);
--- 801,808 ----
    my ($env,$subject)address@hidden;
    my $dbh=C4Connect();
+   $subject=$dbh->quote($subject);
    my $query="Select * from biblio,bibliosubject where
    biblio.biblionumber=bibliosubject.biblionumber and
!   bibliosubject.subject=$subject group by biblio.biblionumber
    order by biblio.title";
    my $sth=$dbh->prepare($query);
***************
*** 721,725 ****
      $dewey=~ s/\.$//;
      $class = $class.$dewey;
!     $class = $class.$data->{'subclass'};
   #   
$results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
      my @temp=split('-',$data->{'datelastseen'});
--- 875,881 ----
      $dewey=~ s/\.$//;
      $class = $class.$dewey;
!     if ($dewey ne ''){
!       $class = $class.$data->{'subclass'};
!     }
   #   
$results[$i]="$data->{'title'}\t$data->{'barcode'}\t$datedue\t$data->{'branchname'}\t$data->{'dewey'}";
      my @temp=split('-',$data->{'datelastseen'});
***************
*** 791,795 ****
    my ($bibnum,$type)address@hidden;
    my $dbh=C4Connect;
!   my $query="Select *,biblio.notes from biblio,biblioitems,bibliosubtitle 
where biblio.biblionumber=$bibnum
    and biblioitems.biblionumber=$bibnum and 
  (bibliosubtitle.biblionumber=$bibnum)"; 
--- 947,951 ----
    my ($bibnum,$type)address@hidden;
    my $dbh=C4Connect;
!   my $query="Select *,biblio.notes  from biblio,biblioitems,bibliosubtitle 
where biblio.biblionumber=$bibnum
    and biblioitems.biblionumber=$bibnum and 
  (bibliosubtitle.biblionumber=$bibnum)"; 
***************
*** 815,819 ****
    my ($bibitem)address@hidden;
    my $dbh=C4Connect;
!   my $query="Select * from biblio,biblioitems,itemtypes where 
biblio.biblionumber=
    biblioitems.biblionumber and biblioitemnumber=$bibitem and
    biblioitems.itemtype=itemtypes.itemtype";
--- 971,975 ----
    my ($bibitem)address@hidden;
    my $dbh=C4Connect;
!   my $query="Select *,biblioitems.notes as bnotes from 
biblio,biblioitems,itemtypes where biblio.biblionumber=
    biblioitems.biblionumber and biblioitemnumber=$bibitem and
    biblioitems.itemtype=itemtypes.itemtype";