[Koha-cvs] CVS: koha/C4 SearchMarc.pm,1.7,1.8

[Jerome Vizcaino]

Update of /cvsroot/koha/koha/C4
In directory sc8-pr-cvs1:/tmp/cvs-serv24475

Modified Files:
        SearchMarc.pm 
Log Message:
Searches with NOT are now fully functionnal
Can create SQL request for any number of statements.
FIXED: splits entry if operator is "contains" and the value is like "foo bar 
boo"


Index: SearchMarc.pm
===================================================================
RCS file: /cvsroot/koha/koha/C4/SearchMarc.pm,v
retrieving revision 1.7
retrieving revision 1.8
diff -C2 -r1.7 -r1.8
*** SearchMarc.pm       7 Jul 2003 14:43:58 -0000       1.7
--- SearchMarc.pm       8 Jul 2003 11:59:08 -0000       1.8
***************
*** 66,72 ****
        #               (m1.subfieldvalue like "Des%" and m2.subfieldvalue like 
"27%")
  
        my $sql_tables; # will contain marc_subfield_table as m1,...
        my $sql_where1; # will contain the "true" where
!       my $sql_where2; # will contain m1.bibid=m2.bibid
        my $nb_active=0; # will contain the number of "active" entries. and 
entry is active is a value is provided.
        my $nb_table=1; # will contain the number of table. ++ on each entry 
EXCEPT when an OR  is provided.
--- 66,224 ----
        #               (m1.subfieldvalue like "Des%" and m2.subfieldvalue like 
"27%")
  
+       # "Normal" statements
+       my @normal_tags = ();
+       my @normal_subfields = ();
+       my @normal_and_or = ();
+       my @normal_operator = ();
+       my @normal_value = ();
+ 
+       # Extracts the NOT statements from the list of statements
+       my @not_tags = ();
+       my @not_subfields = ();
+       my @not_and_or = ();
+       my @not_operator = ();
+       my @not_value = ();
+       my $any_not = 0;
+ 
+       for(my $i = 0 ; $i <= $#{$value} ; $i++)
+       {
+               if(@$excluding[$i])     # NOT statements
+               {
+                       $any_not = 1;
+                       if(@$operator[$i] eq "contains")
+                       {
+                               foreach my $word (split(/ /, @$value[$i]))      
# if operator is contains, splits the words in separate requests
+                               {
+                                       push @not_tags, @$tags[$i];
+                                       push @not_subfields, @$subfields[$i];
+                                       push @not_and_or, "or"; # as request is 
negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
+                                       push @not_operator, @$operator[$i];
+                                       push @not_value, $word;
+                               }
+                       }
+                       else
+                       {
+                               push @not_tags, @$tags[$i];
+                               push @not_subfields, @$subfields[$i];
+                               push @not_and_or, "or"; # as request is 
negated, finds "foo" or "bar" if final request is NOT "foo" and "bar"
+                               push @not_operator, @$operator[$i];
+                               push @not_value, @$value[$i];
+                       }
+               }
+               else    # NORMAL statements
+               {
+                       if(@$operator[$i] eq "contains") # if operator is 
contains, splits the words in separate requests
+                       {
+                               foreach my $word (split(/ /, @$value[$i]))
+                               {
+                                       push @normal_tags, @$tags[$i];
+                                       push @normal_subfields, @$subfields[$i];
+                                       push @normal_and_or, "and";     # 
assumes "foo" and "bar" if "foo bar" is entered
+                                       push @normal_operator, @$operator[$i];
+                                       push @normal_value, $word;
+                               }
+                       }
+                       else
+                       {
+                               push @normal_tags, @$tags[$i];
+                               push @normal_subfields, @$subfields[$i];
+                               push @normal_and_or, @$and_or[$i];
+                               push @normal_operator, @$operator[$i];
+                               push @normal_value, @$value[$i];
+                       }
+               }
+       }
+ 
+       # Finds the basic results without the NOT requests
+       my ($sql_tables, $sql_where1, $sql_where2) = 
create_request(address@hidden, address@hidden, address@hidden, address@hidden, 
address@hidden);
+ 
+       my $sth;
+ #     warn "HERE (NORMAL)";
+       if ($sql_where2) {
+               $sth = $dbh->prepare("select distinct m1.bibid from $sql_tables 
where $sql_where2 and ($sql_where1)");
+ #             warn("-->select m1.bibid from $sql_tables where $sql_where2 and 
($sql_where1)");
+       } else {
+               $sth = $dbh->prepare("select distinct m1.bibid from $sql_tables 
where $sql_where1");
+ #             warn("==>select m1.bibid from $sql_tables where $sql_where1");
+       }
+ 
+       $sth->execute();
+       my @result = ();
+ 
+       # Processes the NOT if any and there are results
+       my ($not_sql_tables, $not_sql_where1, $not_sql_where2);
+ 
+       if( ($sth->rows) && $any_not )  # some results to tune up and some NOT 
statements
+       {
+               ($not_sql_tables, $not_sql_where1, $not_sql_where2) = 
create_request(address@hidden, address@hidden, address@hidden, address@hidden, 
address@hidden);
+ 
+               my @tmpresult;
+ 
+               while (my ($bibid) = $sth->fetchrow) {
+                       push @tmpresult,$bibid;
+               }
+               my $sth_not;
+ #             warn "HERE (NOT)";
+               if ($not_sql_where2) {
+                       $sth_not = $dbh->prepare("select distinct m1.bibid from 
$not_sql_tables where $not_sql_where2 and ($not_sql_where1)");
+ #                     warn("-->select m1.bibid from $not_sql_tables where 
$not_sql_where2 and ($not_sql_where1)");
+               } else {
+                       $sth_not = $dbh->prepare("select distinct m1.bibid from 
$not_sql_tables where $not_sql_where1");
+ #                     warn("==>select m1.bibid from $not_sql_tables where 
$not_sql_where1");
+               }
+ 
+               $sth_not->execute();
+ 
+               if($sth_not->rows)
+               {
+                       my %not_bibids = ();
+                       while(my $bibid = $sth_not->fetchrow()) {
+                               $not_bibids{$bibid} = 1;        # populates the 
hashtable with the bibids matching the NOT statement
+                       }
+ 
+                       foreach my $bibid (@tmpresult)
+                       {
+                               if(!$not_bibids{$bibid})
+                               {
+                                       push @result, $bibid;
+                               }
+                       }
+               }
+               $sth_not->finish();
+       }
+       else    # no NOT statements
+       {
+               while (my ($bibid) = $sth->fetchrow) {
+                       push @result,$bibid;
+               }
+       }
+ 
+       # we have bibid list. Now, loads title and author from [offset] to 
[offset]+[length]
+       my $counter = $offset;
+       $sth = $dbh->prepare("select author,title from biblio,marc_biblio where 
biblio.biblionumber=marc_biblio.biblionumber and bibid=?");
+       my @finalresult = ();
+       while (($counter <= $#result) && ($counter <= ($offset + $length))) {
+               $sth->execute($result[$counter]);
+               my ($author,$title) = $sth->fetchrow;
+               my %line;
+               $line{bibid}=$result[$counter];
+               $line{author}=$author;
+               $line{title}=$title;
+               push @finalresult, \%line;
+               $counter++;
+       }
+ 
+       my $nbresults = $#result + 1;
+       return (address@hidden, $nbresults);
+ }
+ 
+ # Creates the SQL Request
+ 
+ sub create_request {
+       my ($tags, $subfields, $and_or, $operator, $value) = @_;
+ 
        my $sql_tables; # will contain marc_subfield_table as m1,...
        my $sql_where1; # will contain the "true" where
!       my $sql_where2 = "("; # will contain m1.bibid=m2.bibid
        my $nb_active=0; # will contain the number of "active" entries. and 
entry is active is a value is provided.
        my $nb_table=1; # will contain the number of table. ++ on each entry 
EXCEPT when an OR  is provided.
***************
*** 78,82 ****
                                if (@$operator[$i] eq "start") {
                                        $sql_tables .= "marc_subfield_table as 
m$nb_table,";
!                                       $sql_where1 .= 
"@$excluding[$i](m1.subfieldvalue like '@$value[$i]%'";
                                        if (@$tags[$i]) {
                                                $sql_where1 .=" and 
address@hidden and m1.subfieldcode='@$subfields[$i]'";
--- 230,234 ----
                                if (@$operator[$i] eq "start") {
                                        $sql_tables .= "marc_subfield_table as 
m$nb_table,";
!                                       $sql_where1 .= "(m1.subfieldvalue like 
'@$value[$i]%'";
                                        if (@$tags[$i]) {
                                                $sql_where1 .=" and 
address@hidden and m1.subfieldcode='@$subfields[$i]'";
***************
*** 85,89 ****
                                } elsif (@$operator[$i] eq "contains") {
                                        $sql_tables .= "marc_word as 
m$nb_table,";
!                                       $sql_where1 .= "@$excluding[$i](m1.word 
 like '@$value[$i]%'";
                                        if (@$tags[$i]) {
                                                 $sql_where1 .=" and 
address@hidden and m1.subfieldid='@$subfields[$i]'";
--- 237,241 ----
                                } elsif (@$operator[$i] eq "contains") {
                                        $sql_tables .= "marc_word as 
m$nb_table,";
!                                       $sql_where1 .= "(m1.word  like 
'@$value[$i]%'";
                                        if (@$tags[$i]) {
                                                 $sql_where1 .=" and 
address@hidden and m1.subfieldid='@$subfields[$i]'";
***************
*** 92,96 ****
                                } else {
                                        $sql_tables .= "marc_subfield_table as 
m$nb_table,";
!                                       $sql_where1 .= 
"@$excluding[$i](m1.subfieldvalue @$operator[$i] '@$value[$i]' ";
                                        if (@$tags[$i]) {
                                                 $sql_where1 .=" and 
address@hidden and m1.subfieldcode='@$subfields[$i]'";
--- 244,248 ----
                                } else {
                                        $sql_tables .= "marc_subfield_table as 
m$nb_table,";
!                                       $sql_where1 .= "(m1.subfieldvalue 
@$operator[$i] '@$value[$i]' ";
                                        if (@$tags[$i]) {
                                                 $sql_where1 .=" and 
address@hidden and m1.subfieldcode='@$subfields[$i]'";
***************
*** 102,137 ****
                                        $nb_table++;
                                        $sql_tables .= "marc_subfield_table as 
m$nb_table,";
!                                       $sql_where1 .= "@$and_or[$i] 
@$excluding[$i](m$nb_table.subfieldvalue like '@$value[$i]%'";
                                        if (@$tags[$i]) {
!                                                $sql_where1 .=" and 
address@hidden and m$nb_table.subfieldcode='@$subfields[$i])";
                                        }
                                        $sql_where1.=")";
!                                       $sql_where2 .= 
"m1.bibid=m$nb_table.bibid";
                                } elsif (@$operator[$i] eq "contains") {
                                        if (@$and_or[$i] eq 'and') {
                                                $nb_table++;
                                                $sql_tables .= "marc_word as 
m$nb_table,";
!                                               $sql_where1 .= "@$and_or[$i] 
@$excluding[$i](m$nb_table.word like '@$value[$i]%'";
                                                if (@$tags[$i]) {
!                                                       $sql_where1 .="  and 
address@hidden and m$nb_table.subfieldid='@$subfields[$i]'";
                                                }
                                                $sql_where1.=")";
!                                               $sql_where2 .= 
"m1.bibid=m$nb_table.bibid";
                                        } else {
!                                               $sql_where1 .= "@$and_or[$i] 
@$excluding[$i](m$nb_table.word like '@$value[$i]%'";
                                                if (@$tags[$i]) {
                                                        $sql_where1 .="  and 
address@hidden and m$nb_table.subfieldid='@$subfields[$i]'";
                                                }
                                                $sql_where1.=")";
!                                               $sql_where2 .= 
"m1.bibid=m$nb_table.bibid";
                                        }
                                } else {
                                        $nb_table++;
                                        $sql_tables .= "marc_subfield_table as 
m$nb_table,";
!                                       $sql_where1 .= "@$and_or[$i] 
@$excluding[$i](m$nb_table.subfieldvalue @$operator[$i] '@$value[$i]'";
                                        if (@$tags[$i]) {
!                                                $sql_where1 .="  and 
address@hidden and m$nb_table.subfieldcode='@$subfields[$i]'";
                                        }
!                                       $sql_where2 .= 
"m1.bibid=m$nb_table.bibid";
                                        $sql_where1.=")";
                                }
--- 254,289 ----
                                        $nb_table++;
                                        $sql_tables .= "marc_subfield_table as 
m$nb_table,";
!                                       $sql_where1 .= "@$and_or[$i] 
(m$nb_table.subfieldvalue like '@$value[$i]%'";
                                        if (@$tags[$i]) {
!                                               $sql_where1 .=" and 
address@hidden and m$nb_table.subfieldcode='@$subfields[$i]'";
                                        }
                                        $sql_where1.=")";
!                                       $sql_where2 .= 
"m1.bibid=m$nb_table.bibid and ";
                                } elsif (@$operator[$i] eq "contains") {
                                        if (@$and_or[$i] eq 'and') {
                                                $nb_table++;
                                                $sql_tables .= "marc_word as 
m$nb_table,";
!                                               $sql_where1 .= "@$and_or[$i] 
(m$nb_table.word like '@$value[$i]%'";
                                                if (@$tags[$i]) {
!                                                       $sql_where1 .=" and 
address@hidden and m$nb_table.subfieldid='@$subfields[$i]'";
                                                }
                                                $sql_where1.=")";
!                                               $sql_where2 .= 
"m1.bibid=m$nb_table.bibid and ";
                                        } else {
!                                               $sql_where1 .= "@$and_or[$i] 
(m$nb_table.word like '@$value[$i]%'";
                                                if (@$tags[$i]) {
                                                        $sql_where1 .="  and 
address@hidden and m$nb_table.subfieldid='@$subfields[$i]'";
                                                }
                                                $sql_where1.=")";
!                                               $sql_where2 .= 
"m1.bibid=m$nb_table.bibid and ";
                                        }
                                } else {
                                        $nb_table++;
                                        $sql_tables .= "marc_subfield_table as 
m$nb_table,";
!                                       $sql_where1 .= "@$and_or[$i] 
(m$nb_table.subfieldvalue @$operator[$i] '@$value[$i]'";
                                        if (@$tags[$i]) {
!                                               $sql_where1 .="  and 
address@hidden and m$nb_table.subfieldcode='@$subfields[$i]'";
                                        }
!                                       $sql_where2 .= 
"m1.bibid=m$nb_table.bibid and ";
                                        $sql_where1.=")";
                                }
***************
*** 139,170 ****
                }
        }
!       chop $sql_tables;
!       my $sth;
!       if ($sql_where2) {
!               $sth = $dbh->prepare("select distinct m1.bibid from $sql_tables 
where $sql_where2 and ($sql_where1)");
!       } else {
!               $sth = $dbh->prepare("select distinct m1.bibid from $sql_tables 
where $sql_where1");
!       }
!       $sth->execute;
!       my @result;
!       while (my ($bibid) = $sth->fetchrow) {
!               push @result,$bibid;
        }
!       # we have bibid list. Now, loads title and author from [offset] to 
[offset]+[length]
!       my $counter = $offset;
!       $sth = $dbh->prepare("select author,title from biblio,marc_biblio where 
biblio.biblionumber=marc_biblio.biblionumber and bibid=?");
!       my @finalresult = ();
!       while ($counter <= ($offset + $length)) {
!               $sth->execute($result[$counter]);
!               my ($author,$title) = $sth->fetchrow;
!               my %line;
!               $line{bibid}=$result[$counter];
!               $line{author}=$author;
!               $line{title}=$title;
!               push @finalresult, \%line;
!               $counter++;
        }
!       return @finalresult;
  }
  
  END { }       # module clean-up code here (global destructor)
--- 291,308 ----
                }
        }
! 
!       if($sql_where2 ne "(")  # some datas added to sql_where2, processing
!       {
!               $sql_where2 = substr($sql_where2, 0, (length($sql_where2)-5)); 
# deletes the trailing ' and '
!               $sql_where2 .= ")";
        }
!       else    # no sql_where2 statement, deleting '('
!       {
!               $sql_where2 = "";
        }
!       chop $sql_tables;       # deletes the trailing ','
!       return ($sql_tables, $sql_where1, $sql_where2);
  }
+ 
  
  END { }       # module clean-up code here (global destructor)