[Koha-cvs] CVS: koha/C4 SearchMarc.pm,1.7,1.8
Jerome Vizcaino
plugz at users.sourceforge.net
Tue Jul 8 13:59:10 CEST 2003
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(\@normal_tags, \@normal_subfields, \@normal_and_or, \@normal_operator, \@normal_value);
+
+ 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(\@not_tags, \@not_subfields, \@not_and_or, \@not_operator, \@not_value);
+
+ 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 (\@finalresult, $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 m1.tag=@$tags[$i] 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 m1.tag=@$tags[$i] 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 m1.tag=@$tags[$i] 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 m1.tag=@$tags[$i] 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 m1.tag=@$tags[$i] 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 m1.tag=@$tags[$i] 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 m$nb_table.tag=@$tags[$i] 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 m$nb_table.tag=@$tags[$i] 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 m$nb_table.tag=@$tags[$i] 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 m$nb_table.tag=@$tags[$i] 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 m$nb_table.tag=@$tags[$i] 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 m$nb_table.tag=@$tags[$i] 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 m$nb_table.tag=@$tags[$i] 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 m$nb_table.tag=@$tags[$i] 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)
More information about the Koha-cvs
mailing list